SQL进阶--CASE 表达式

SQL进阶

-- 创建数据库
create database  sqlAdvanced  charset utf8mb4;
-- use
use sqlAdvanced
-- 创建用户名、密码
create user'sqlAdvanced'@'localhost'identified by'sqlAdvanced';
-- 授权
grant all on sqlAdvanced.*to'sqlAdvanced'@'localhost';
-- 用用户名、密码登录
mysql -usqlAdvanced -psqlAdvanced


-- 创建表
create table user
(
	u_id int not null primary key auto_increment,
	u_name varchar(20),
	u_sex int,
	u_birthday date
);
-- 添加数据
insert into user(u_name,u_sex,u_birthday) 
values
-- u_sex：1 男 2 女
('小向',1,'1980-12-03'),
('小二',2,'1980-09-22');

CASE 表达式

CASE 表达式是从 SQL-92 标准开始被引入的。

可能因为它是相对较新 的技术，所以尽管使用起来非常便利，但其真正的价值却并不怎么为人所 知。

很多人不用它，或者用它的简略版函数，例如 DECODE（Oracle）、IF （MySQL）等

简单 CASE 表达式 （simple case expression）

SELECT u_id AS ID,u_name AS 姓名,
-- 简单 CASE 表达式
CASE u_sex
 WHEN '1' THEN '男'
 WHEN '2' THEN '女'
ELSE '其他' END  AS 性别 ,u_birthday AS 生日 FROM `user`

运行

搜索 CASE 表达式（searched case expression）

SELECT u_id AS ID,u_name AS 姓名,
-- 搜索 CASE 表达式
CASE WHEN u_sex = '1' THEN '男'
 WHEN u_sex = '2' THEN '女'
ELSE '其他' END AS 性别 ,u_birthday AS 生日 FROM `user`

运行

剩余的 WHEN 子句被忽略的写法

SELECT u_id AS ID,u_name AS 姓名,
-- 例如，这样写的话，结果里不会出现“第二”
CASE WHEN LEVEL IN ('a', 'b') THEN '第一'
 WHEN LEVEL IN ('a') THEN '第二'
ELSE '其他' END AS 等级 FROM `user`

SELECT u_id AS ID,u_name AS 姓名,
-- 搜索 CASE 表达式
CASE WHEN u_sex = '1' THEN '男'
 WHEN u_sex = '2' THEN '女'
ELSE '其他' END AS 性别 ,u_birthday AS 生日,score AS 分数,
CASE WHEN LEVEL IN ('a') THEN '优'
 WHEN LEVEL IN ('b') THEN '良'
 WHEN LEVEL IN ('c') THEN '中'
 WHEN LEVEL IN ('d') THEN '差'
ELSE '其他' END AS 等级 FROM `user`

SELECT u_id AS ID,u_name AS 姓名,
CASE WHEN LEVEL IN ('a') THEN '第一'
	WHEN LEVEL IN ('b') THEN '第二'
	WHEN LEVEL IN ('c') THEN '第三'
	WHEN LEVEL IN ('d') THEN '第四'
	ELSE'未知' END  AS 等级 FROM `user`

-- 分数 等级判断
SELECT  u_id AS ID,u_name AS 姓名, score AS 分数,
CASE WHEN LEVEL IN ('a') THEN '优'
 WHEN LEVEL IN ('b') THEN '良'
 WHEN LEVEL IN ('c') THEN '中'
 WHEN LEVEL IN ('d') THEN '差'
ELSE '其他' END AS 等级 FROM `user`

运行

此外，使用 CASE 表达式的时候，还需要注意以下几点。

注意事项 1：统一各分支返回的数据类型

CASE 表 达式里各个分支返回的数据类型是否一致。

某个分支返回字符型，而其他 分支返回数值型的写法是不正确的。

注意事项 2：不要忘了写 END

使用 CASE 表达式的时候，最容易出现的语法错误是忘记写 END。

虽 然忘记写时程序会返回比较容易理解的错误消息，不算多么致命的错误。

但是，感觉自己写得没问题，而执行时却出错的情况大多是由这个原因引 起的，所以请一定注意一下。

注意事项 3：养成写 ELSE 子句的习惯

与 END 不同，ELSE 子句是可选的，不写也不会出错。

不写 ELSE 子句时， CASE 表达式的执行结果是 NULL。

但是不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦，所以最好明确地写上 ELSE 子句（即便 是在结果可以为 NULL 的情况下）。