带你从入门到精通——MySQL（五. 常用函数二）

原创已于 2024-11-25 21:16:44 修改 · 888 阅读

26 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MySQL

于 2024-11-22 22:28:51 首次发布

建议先阅读我之前的博客，掌握一定的MySQL前置知识后再阅读本文，链接如下

带你从入门到精通——MySQL（一. 基础知识）-CSDN博客

带你从入门到精通——MySQL（二. 单表查询）-CSDN博客

带你从入门到精通——MySQL（三. 多表查询）-CSDN博客

带你从入门到精通——MySQL（四. 常用函数一）-CSDN博客

五. 常用函数二

5.1 条件控制函数

5.1.1 CASE...WHEN函数

Case...when函数是一种条件控制函数，其一般格式如下：

CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
....
ELSE valuen
END

该函数表示当满足condition1时，该行的值为value1，当满足condition2时，该行的值为value2，以此类推，当所有条件均不满足时，取else分支的值valuen。该函数一般用于select关键字之后，用于创建一个新的字段。

现在假设我们有如下一张名为score1的分数表，我们需要查询所有学生的成绩信息，并将学生的成绩分成5个等级，查询结果中需要有一个新的表示学生成绩等级的字段。

-- 成绩等级按以下标准区分：
-- 优秀：90分及以上
-- 良好：80-90，包含80
-- 中等：70-80，包含70
-- 及格：60-70，包含60
-- 不及格：60分以下
SELECT *,
       CASE
           WHEN score >= 90 THEN '优秀'
           WHEN score >= 80 THEN '良好'
           WHEN score >= 70 THEN '中等'
           WHEN score >= 60 THEN '及格'
           ELSE '不及格'
           END AS grade
FROM score1;

从以上示例可以看出，case...when函数可以帮助我们创建一个新的字段，并根据查询出来的数据来判断当前行的值应为多少。我们再来看一个需求，现在需要我们统计在score1表中不同科目中，成绩在90分以上（包含90）和90分以下的人数各有多少，如果只需要统计90分以上（包含90）和90分以下的人数各有多少那么这是一个很简单的问题，直接group by分组结合count函数即可解决，但现在需要我们统计不同学科中的90分以上（包含90）和90分以下的人数各有多少，此时就需要使用case...when函数了。

SELECT course,
       SUM(
               CASE WHEN score >= 90 THEN 1 ELSE 0 END
           ) AS more_90,
       SUM(
               CASE WHEN score < 90 THEN 1 ELSE 0 END
           ) AS less_90
FROM score1
GROUP BY course;

5.1.2 IF函数

IF函数同样也是一个条件控制函数，它的格式为IF（条件，值1，值2），如果条件成立，IF的结果就是值1，否则结果就是值2，可以看到if函数有着比case...when函数更加简短的格式，但是if函数只能处理只有两种条件选择的情况，因此如果case...when函数中只有两种条件选择时，可以使用if函数替换，如果条件选择大于两种，我们只能使用case...when函数处理。

在上面的需求2中就是只有两种条件选择的情况，因此我们可以用if函数去替换case...when函数。

SELECT course,
       SUM(IF(score >= 90, 1, 0)) AS more_90,
       SUM(IF(score < 90, 1, 0))  AS less_90
FROM score1
GROUP BY course;

5.2 常用函数补充

5.2.1 CAST函数

Cast函数用于对各种数据类型进行转换，其语法格式为CAST（value AS data_type），其中value表示一个数据值，data_type表示需要转换的目标数据类型，两个参数都是必填内容，具体示例如下：

SELECT CAST(2.205 AS DECIMAL(8, 2));
-- 输出2.21
SELECT CAST(150 AS CHAR);
-- 输出’150‘

5.2.2 GROUP_CONCAT函数

该函数是一种将分组中的多个值连接成一个字符串的聚合函数，可以将同一组内的多个值合并为一个由指定分隔符分隔的字符串，其具体语法格式如下：

GROUP_CONCAT
([DISTINCT] field_name1 [ORDER BY field_name2 ASC/DESC] [SEPARATOR 'separator'])

其中，带有[]的内容表示可以省略，如果分隔符省略，默认按照逗号连接。

5.2.3 FIELD函数

在需要对某一字段中的内容进行更为精细地排序时，可以使用field函数，该函数可以自定义字段的排列顺序，其格式为field（字段名，值1，值2，值3，....），该函数会将你指定的字段按照你所指定的值1，值2，值3，....的顺序进行重排列，其具体语法格式如下：

SELECT *
FROM table_name
ORDER BY FIELD(field_name, valus1, value2, values3);

5.3 经典面试题

行转列和列转行问题是两道十分经典的面试题，也是十分重要的一个知识点，如果是第一次遇到这类题型的话，还是有一定难度的，这里专门用一节的内容来详细介绍这两个问题。

5.3.1 行转列问题

我们还是使用之前的score1表，现在我们有这样一个需求，我们需要将score1表进行相应的转换，最终得到如下一张新表，我们应该怎么做呢？

先观察新表的数据，与score1表只有一个相同的字段name，其他字段全部不同，并且name字段在新表中是唯一不重复的，而在score1表中是有重复name存在的，此外，score1表中course字段中的数据值`语文`、`数学`在新表中变为了两个新的字段，score1表中的score字段则是被去除了，那么我们应该怎么去思考这个问题呢，既然新表中name字段的值是唯一的，首先我们想到的就是根据name去进行group by分组，分组之后每个同学都有两个成绩，分别对应语文和数学，这里我们需要单独提取出两门数据的值，这里就需要使用到条件控制函数，如果我们需要获得语文的成绩，则在course值为语文时保留score的值，否则取null值，由于此时只有两种条件选择，我们使用更为简洁的if函数来实现，此时在该分组中只有语文对应的score值被保留，其余值均为null值，所以我们可以使用除count以外的聚合函数来获取该同学的语文值，同理我们可以获取到每个同学的数学成绩，最后为获得的score值附上一个新的别名即可创建出相应的新字段。

具体代码实现如下：

SELECT name,
       MAX(IF(course = '语文', score, NULL)) AS `语文`,
       MAX(IF(course = '数学', score, NULL)) AS `数学`
FROM score1
GROUP BY name;

5.3.2 列转行问题

我们将上一小节获取到的新表命名为score2，现在我们需要将score2表重新转换为score1表，这就是列转行的问题，我们又应该怎么去实现呢？

对于列转行的问题我们往往需要进行多表的合并，第一步我们先从score2表筛选出各个同学的语文成绩，创建一个新的字段course并将`语文`作为它的一个字段值，同时选取当前的语文成绩作为score，同理获取各个同学的数学成绩，最后将两表通过union函数进行合并，要注意合并后的表的行顺序可能与score1表不同，因此我们需要对score2表进行重新排序，首先根据name字段进行排序，再使用field函数对course字段进行排序即可获得最终结果。

具体代码实现如下：

SELECT name, '语文' AS course, 语文 AS score
FROM score2
UNION
SELECT name, '数学' AS course, 语文 AS score
FROM score2
ORDER BY name, FIELD(course, '语文', '数学');

标签

#数据库 #mysql #sql