case 函数
MySQL 的 case 函数的语法有种:
1.简单函数
CASE [column_name] WHEN [value1] THEN [result1]... ELSE [default] END
多表查询的时候需要注意下。
CASE hero_name
WHEN '盖伦' THEN '上单'
WHEN '拉克丝' THEN '中单'
WHEN '金克斯' THEN 'ADC'
WHEN '琴女' THEN '辅助'
WHEN '盲僧' THEN '打野'
ELSE '混子' END
2.搜索函数
CASE WHEN [expr] THEN [result1]... ELSE [default] END
搜索函数可以使用表达式 expr 判断,并且返回第一个符合条件的值。
(CASE
WHEN age >= 18 THEN '成年'
WHEN age < 18 THEN '未成年'
ELSE '保密' END) AS '是否成年'
两种都可以实现功能,具体看实现业务使用。
注意:case 只返回第一个符合条件的值,剩下的 case 部分会被忽略。
下面我们就来练习下吧。
1.简单函数
查询学生id、姓名、年龄、各科成绩,总分,按总分排序
注意:这里使用了多表查询需要加上 聚合函数 sum 或者聚合函数 max 或者聚合函数 min,具体看情况使用(这里 else 0 ,所以使用了sum)。
1.1没有加聚合函数(错误使用)
SELECT s.`s#`,s.`s_name`,s.`s_age`,
(CASE c.`c_name` WHEN '语文' THEN cs.`score` ELSE 0 END) AS '语文',
(CASE c.`c_name` WHEN '数学' THEN cs.`score` ELSE 0 END) AS '数学',
(CASE c.`c_name` WHEN '英语' THEN cs.`score` ELSE 0 END) AS '英语',
SUM(cs.`score`) AS '总分'
FROM students s LEFT JOIN cs ON s.`s#`=cs.`s#` LEFT JOIN course c ON c.`c#`=cs.`c#`
GROUP BY s.`s#`,s.`s_name`,s.`s_age`
ORDER BY SUM(cs.`score`) DESC;
注意:这里数据显现出现了问题。
1.2使用了聚合函数(正确使用)
SELECT s.`s#`,s.`s_name`,s.`s_age`,
SUM(CASE c.`c_name` WHEN '语文' THEN cs.`score` ELSE 0 END) AS '语文',
SUM(CASE c.`c_name` WHEN '数学' THEN cs.`score` ELSE 0 END) AS '数学',
SUM(CASE c.`c_name` WHEN '英语' THEN cs.`score` ELSE 0 END) AS '英语',
SUM(cs.`score`) AS '总分'
FROM students s LEFT JOIN cs ON s.`s#`=cs.`s#` LEFT JOIN course c ON c.`c#`=cs.`c#`
GROUP BY s.`s#`,s.`s_name`,s.`s_age`
ORDER BY SUM(cs.`score`) DESC;
这里数据正确的显现出来了。
这里正确的使用方法,需要多多练习。
2.搜索函数
查询学生名称、学生年龄、是否成年。
2.1错误的用法
加了列(错误)
SELECT s.`s_name`,s.`s_age`,
(CASE s.`s_age` WHEN s.`s_age` >= 18 THEN '成年' WHEN s.`s_age` < 18 THEN '未成年' ELSE '保密' END) AS '是否成年'
FROM students s;
2.2正确的用法
没有加列(正确)
SELECT s.`s_name`,s.`s_age`,
(CASE WHEN s.`s_age` >= 18 THEN '成年' WHEN s.`s_age` < 18 THEN '未成年' ELSE '保密' END) AS '是否成年'
FROM students s;
要用的到数据
-- 学生表(students)
CREATE TABLE `students`(
`s#` INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生学号(主键)',
`s_name` VARCHAR(20) COMMENT '学生名字',
`s_age` CHAR(3) COMMENT '学生年龄',
`s_sex` CHAR(1) COMMENT '学生性别'
);
-- 课程表(course)
CREATE TABLE `course`(
`c#` INT AUTO_INCREMENT PRIMARY KEY COMMENT '课程号(主键)',
`c_name` VARCHAR(20) COMMENT '课程名称'
);
-- 成绩表(cs)
CREATE TABLE `cs`(
`s#` INT COMMENT '学生学号',
`c#` INT COMMENT '课程号',
`score` INT COMMENT '成绩'
);
-- 给已经存在的表添加外键,语法如下
-- alter table 表名 add constraint [外键名字] foreign key (外键字段) references 父表(主键字段);
ALTER TABLE cs ADD CONSTRAINT students_fk FOREIGN KEY (`s#`) REFERENCES students (`s#`);
ALTER TABLE cs ADD CONSTRAINT course_fk FOREIGN KEY (`c#`) REFERENCES course (`c#`);
-- 插入数据
-- 学生表
INSERT INTO students VALUES
(100001,'迪丽热巴','17','女'),(100002,'小鹏','18','男'),(100003,'古力娜扎','19','女');
-- 课程表
INSERT INTO course VALUES
(200001,'语文'),(200002,'数学'),(200003,'英语');
-- 成绩表
INSERT INTO cs VALUES
(100001,200001,99),(100001,200002,88),(100001,200003,77),
(100002,200001,100),(100002,200002,100),(100002,200003,100),
(100003,200001,98),(100003,200002,66),(100003,200003,87);
学生表数据
课程表数据
成绩表数据
END…