Mysql常用函数(二)

条件函数

CASE函数

  是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。

  可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。

  分为：简单CASE函数、搜索CASE函数

  

简单 CASE函数

CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
1
2
3
4
5
6

• 计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。

• 如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。

• 如果所有简单表达式的值与测试表达式的值都不相等，若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；若没有指定ELSE子句，则返回NULL。

 

例一 ： 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：
当系号为1时，显示 “计算机系”；
当系号为2时，显示 “软件工程系”；
当系号为3时，显示 “物联网系”。

SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表
1
2
3
4
5
6
7

  

搜索CASE函数

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
1
2
3
4
5
6

• 按从上到下的书写顺序计算每个WHEN子句的布尔表达式。

• 返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。

• 如果没有取值为TRUE的布尔表达式，则当指定了ELSE子句时,返回ELSE子句中指定的结果；如果没有指定ELSE子句，则返回NULL。

  

例一 ： 用搜索CASE来做：

SELECT 班号 ,班名,
CASE
WHEN 系号=1 THEN '软件工程系'
WHEN 系号=2 THEN '计算机系'
WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表
1
2
3
4
5
6
7

  

例二 ： 查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

SELECT 学号,课程号,
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'
1
2
3
4
5
6
7
8
9
10

  

例三 ： 统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。

SELECT 班号,
COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数,
COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数
FROM 学生表 GROUP BY 班号
1
2
3
4

  

例四 ： 判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。

SELECT
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级, COUNT(*) 人数
FROM SC
GROUP BY
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

  

例五 ： 现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

select 

case 
when age >= 25 then '25岁及以上'
else '25岁以下'
end as age_cut, 

count(*) as number

from user_profile
group by age_cut;
1
2
3
4
5
6
7
8
9
10
11

  

例六 ：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

select device_id, gender,
case 
when age is null then '其他'
when age < 20 then '20岁以下'
when age between 20 and 24 then '20-24岁'
else '25岁及以上' 
end as age_cut
from user_profile;
1
2
3
4
5
6
7
8

  

例七 ： 现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

select 
case 
when profile like '%female%' then 'female'
when profile like '%male%' then 'male' 
end as gender,
count(*) as number
from user_submit
group by gender;
1
2
3
4
5
6
7
8

  

例八 ： 现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

select substring_index(substring_index(profile, ',', 3), ',', -1) as age,
count(*) as number
from user_submit
group by age;
1
2
3
4

  

例九 ： 对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

select device_id, substring(blog_url, 11) as user_name
from user_submit;
1
2

  

  

日期函数

现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

select day(date) as day, count(*) as question_cnt 
from question_practice_detail
where month(date) = 8
group by day;
1
2
3
4