MySQL 流程控制函数与分组函数完整指南

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL 流程控制函数与分组函数”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

一、流程控制函数

1、IF(expr1,expr2,expr3)

用来查询中进行条件判断的流程控制函数。

expr1：要判断的条件表达式，如果条件为真（非零或非空），则得到 expr2，否则得到 expr3。
expr2：条件为真时得到的值。
expr3：条件为假时得到的值。

SELECT IF(2 < 3,'大','小') AS out_put;

示例：在这个场景下，查询员工的姓和名以及奖金率，如果有奖金率则得到有，没有则得到无，并以备注为列名。

分析：

查询的表：employees
查询的字段：last_name、first_name、commission_pct、备注
查询的条件：无
排序的条件：无

SELECT
    last_name,
    first_name,
    IF(commission_pct IS NULL,'无','有') AS 备注
FROM
    employees;

2、CASE

CASE 是一种流程控制函数，类似编程语言中的 switch 语法。

语法格式 1：

CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

value：需进行比较的表达式或者列。
compare_value：进行比较的表达式或值。
result：当 value 等于 compare_value 时得到的结果。

语法格式 2：

CASE
    WHEN condition THEN result
    [WHEN condition THEN result ...]
    [ELSE result]
END

condition：条件表达式，能够是任何布尔表达式。
result：当条件表达式为真时得到的结果。
ELSE result：如果没有条件表达式为真时，则得到的默认结果。

示例：查询员工的工资，要求如下所示：

部门号=30，显示的工资为 1.1 倍。
部门号=40，显示的工资为 1.2 倍。
部门号=50，显示的工资为 1.3 倍。
其他部门，显示的工资为原工资。

分析：

查询的表：employees
查询的字段：salary、department_id、新工资
查询的条件：无
排序的条件：无

采用语法格式 1：

SELECT
    salary,
    department_id,
    CASE department_id
        WHEN 30 THEN salary * 1.1
        WHEN 40 THEN salary * 1.2
        WHEN 50 THEN salary * 1.3
        ELSE salary
    END AS 新工资
FROM
    employees;

采用语法格式 2：

SELECT
    salary,
    department_id,
    CASE
        WHEN department_id = 30 THEN salary * 1.1
        WHEN department_id = 40 THEN salary * 1.2
        WHEN department_id = 50 THEN salary * 1.3
        ELSE salary
    END AS 新工资
FROM
    employees;

示例：查询员工的工资情况：

若工资大于 20000，显示 A 级别。
若工资大于 15000，显示 B 级别。
若工资大于 10000，显示 C 级别。
否则，显示 D 级别。

SELECT
    salary,
    CASE
        WHEN salary > 20000 THEN 'A'  --工资>20000 → A级别
        WHEN salary > 15000 THEN 'B'  --15000<工资≤20000 → B级别
        WHEN salary > 10000 THEN 'C'  --10000<工资≤15000 → C级别
        ELSE 'D'  --工资≤10000 → D级别
    END AS 工资级别
FROM
    employees;

二、分组函数（聚合函数）

分组函数用作统计采用，又称为聚合函数、统计函数或者组函数。

max()：得到最大值。
min()：得到最小值。
avg()：得到参数的平均值。
count()：得到行数。
sum()：得到总和。

总结：

在这个场景下，sum、avg 一般用来处理数值型，max、min、count 能够处理任何类型。
max、min、avg、count 聚合函数都忽略 null 值。
能够和 distinct 搭配实现去重的运算。
一般采用 count(*) 做统计函数。
和聚合函数一同查询的字段要求是 group by 关键字后面的字段。

1、sum()

SELECT
    SUM(salary) AS sum_sal  --查询所有员工工资总和
FROM
    employees;

2、avg()

SELECT
    ROUND(AVG(salary),2) AS avg_sal  --查询工资平均值，保留2位小数
FROM
    employees;

3、max()

SELECT
    MAX(salary) AS max_sal  --查询工资最大值
FROM
    employees;

4、min()

SELECT
    MIN(salary) AS min_sal  --查询工资最小值
FROM
    employees;

5、count()

SELECT COUNT(salary) AS count_sal FROM employees;  --统计salary不为null的行数
SELECT COUNT(commission_pct) AS count_sal FROM employees;  --忽略null值

示例：查询所有员工的工资总和、平均值、最高值、最低值以及总个数。

SELECT
    SUM(salary),    --工资总和
    AVG(salary),    --工资平均值
    MAX(salary),    --工资最大值
    MIN(salary),    --工资最小值
    COUNT(salary)   --非空工资的记录条数
FROM
    employees;

6、聚合函数采用细节与总结

数值型与字符串、日期的处理差异：

SELECT SUM(last_name), AVG(last_name) FROM employees;  --sum、avg只适合数值，字符串求和无实际意义
SELECT SUM(hiredate), AVG(hiredate) FROM employees;    --sum、avg对日期运算，无业务意义
SELECT MAX(last_name), MIN(last_name) FROM employees;  --max/min可以用于字符串，按字典顺序取最大最小
SELECT MAX(hiredate), MIN(hiredate) FROM employees;    --max/min可以用于日期，取最晚、最早日期

是否忽略 null 值：

SELECT COUNT(commission_pct) FROM employees;  --count统计不为null的佣金字段行数，null会跳过
SELECT COUNT(last_name) FROM employees;
SELECT SUM(commission_pct), AVG(commission_pct), SUM(commission_pct) / 35, SUM(commission_pct) / 107 FROM employees;
SELECT MAX(commission_pct), MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;  --count忽略null值
SELECT COUNT(last_name) FROM employees;

搭配 distinct 实现去重：

SELECT MAX(DISTINCT salary), MIN(salary) FROM employees;
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;

count() 详细说明：

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

7、分组查询

分组查询的基本语法结构如下所示：

SELECT
    聚合函数,
    分组字段       --select中非聚合的列，必须写在group by之后
FROM
    表名
WHERE
    分组之前的筛选条件  --where：原始表数据过滤，不能写聚合函数
GROUP BY
    分组字段列表
HAVING
    分组之后的筛选条件  --having：分组结果过滤，可以写聚合函数
ORDER BY
    排序字段 ASC|DESC
WITH ROLLUP;  --生成汇总行

分组查询中的筛选条件分为两类：

分组前筛选：作用来原始表，位于 group by 子句的前面，采用 where。
分组后筛选：作用来分组后的结果集（虚表），位于 group by 子句的后面，采用 having。

注意：

分组函数做条件时，肯定放在 having 子句中。
能用分组前筛选的，就优先考虑采用分组前筛选。
理解这一步时，group by 子句兼容单字段分组、多字段分组（多个字段之间采用逗号隔开，没有先后顺序）、表达式或者函数。
也能够添加排序，排序放在整个分组查询的最后。

示例：查询每个部门的平均工资。

SELECT
    AVG(salary),
    department_id
FROM
    employees
GROUP BY
    department_id;

示例：查询邮箱中包含 a 字符的每个部门的平均工资。

分析：

查询的表：employees
查询的字段：salary、department_id
查询的条件：email like '%a%'
分组的字段：department_id
排序的条件：无

SELECT
    AVG(salary) AS avg_sal,
    department_id
FROM
    employees
WHERE
    email LIKE '%a%'
GROUP BY
    department_id;

示例：查询有奖金率的每个领导手下员工的最高工资。

分析：

查询的表：employees
查询的字段：salary、manager_id
查询的条件：commission_pct is not null
分组的字段：manager_id
排序的条件：无

SELECT
    MAX(salary) AS max_sal,
    manager_id
FROM
    employees
WHERE
    commission_pct IS NOT NULL
GROUP BY
    manager_id;

示例：添加复杂的筛选条件，查询哪个部门的员工个数大于 2。

分析（1）：查询每个部门的员工个数。

SELECT
    COUNT(*),
    department_id
FROM
    employees
GROUP BY
    department_id;

分析（2）：根据（1）的查询结果进行筛选，员工个数大于 2。

SELECT
    COUNT(*),
    department_id
FROM
    employees
GROUP BY
    department_id
HAVING COUNT(*) > 2;

示例：从实现思路看，查询每个工种有奖金率的员工的最高工资大于 12000 的工种编号和最高工资。

SELECT
    MAX(salary) AS max_sal,  --查询最高工资，起别名max_sal
    job_id                    --工种编号
FROM
    employees                 --员工表
WHERE
    commission_pct IS NOT NULL  --分组前筛选：原始表，过滤有奖金的员工
GROUP BY
    job_id                     --按照工种编号分组
HAVING
    max_sal > 12000;           --分组后筛选：分组结果，最高工资大于12000

示例：理解这一步时，查询领导编号大于 102 的每个领导手下的最低工资大于 5000 的领导编号是哪个，以及其最低工资。

分析（1）：查询每个领导手下的员工最低工资。

SELECT
    MIN(salary),      --查询每个分组下员工的最低工资
    manager_id        --领导编号
FROM
    employees         --员工表
GROUP BY
    manager_id;       --根据领导编号分组

分析（2）：根据（1）的结果继续添加筛选条件：编号大于 102。

SELECT
    MIN(salary),      --最低工资
    manager_id        --领导编号
FROM
    employees         --员工表
WHERE
    manager_id > 102  --分组前筛选：原始表，过滤领导编号>102的数据
GROUP BY
    manager_id;       --按领导编号分组

分析（3）：根据（2）的结果继续筛选，最低工资大于 5000。

SELECT
    MIN(salary),      --最低工资
    manager_id        --领导编号
FROM
    employees         --员工表
WHERE
    manager_id > 102  --WHERE：分组前过滤原始行，领导编号>102
GROUP BY
    manager_id        --按照领导编号分组
HAVING
    MIN(salary) > 5000; --HAVING：分组后过滤聚合结果，最低工资大于5000

示例：在这个场景下，按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于 5 的有哪些。

SELECT
    COUNT(*) AS emp_count,                        --统计每组员工数量，别名emp_count
    LENGTH(CONCAT(last_name, first_name)) AS len_name  --姓名总长度
FROM
    employees
GROUP BY
    LENGTH(CONCAT(last_name, first_name))          --GROUP BY支持函数/表达式分组
HAVING
    COUNT(*) > 5;                                 --分组之后过滤，只保留员工数大于5的组

三、连接查询

需求：查询所有女明星对应的男朋友。

SELECT `name`, boyname FROM beauty, boys;  --笛卡尔积，两张表所有行互相配对，产生错误大量数据
SELECT `name`, boyname FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id;  --等值连接，添加两张表关联条件，消除笛卡尔积

笛卡尔积：是两个或多个表之间的连接操作。

笛卡尔积现象：表 1 有 m 行，表 2 有 n 行，结果为 m * n。

笛卡尔积产生的条件：

省略连接条件。
连接条件无效。
所有表中的所有行互相连接。

注意：为便于避免笛卡尔积产生，能够在 where 子句后面加入有效连接条件。

连接查询：又称为多表查询，当查询的字段来自多个表时，就会用到连接查询。

连接查询分类：

按年代分类：
- sql92 标准（淘汰，了解即可）。
- 实际处理时，sql99 标准（建议采用），兼容内连接 + 外连接（左外、右外）+ 交叉连接。
按功能分类：
- 内连接：等值连接、非等值连接、自连接。
- 外连接：左外连接、右外连接、全外连接。
- 交叉连接。

连接查询语法格式总结：

SELECT (7)
    查询列表
FROM 表1 别名 [连接类型]  (1)
JOIN 表2 别名      (3)
ON 连接条件    (2)
[WHERE 筛选条件] (4)
[GROUP BY 子句] (5)
[HAVING 筛选条件] (6)
[ORDER BY 子句] (8)

连接条件分类：

内连接：INNER。
外连接：
- 左外连接：LEFT [OUTER]。
- 右外连接：RIGHT [OUTER]。
- 全外连接：FULL [OUTER]，MySQL 不兼容全外。
交叉连接：CROSS。

内连接：INNER

-- 语法格式：
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件;

内连接分为：等值、非等值、自连接。

特点：

能够添加排序、分组、筛选。
inner 关键字能够省略。
在这个场景下，筛选条件放在 where 后面，连接条件要放在 on 后面，提高分离性，便于阅读。

（1）等值连接

示例：查询员工名、部门名。

分析：

查询的表：employees、departments
查询的字段：first_name、department_name
查询条件：无

SELECT
    e.first_name,
    d.department_name
FROM
    employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;

示例：查询名字中包含 a 的员工名和工种名。

SELECT
    first_name,
    job_title
FROM
    employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE
    e.first_name LIKE '%a%';

示例：查询部门个数大于 3 的城市名和部门个数（添加分组 + 筛选）。

分析（1）：查询每个部门的个数。

SELECT
    COUNT(*) AS num,
    city
FROM
    departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY
    city;

分析（2）：根据（1）的结果筛选部门个数大于 3。

SELECT
    COUNT(*) AS num,
    city
FROM
    departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY
    city
HAVING
    num > 3;

示例：在这个场景下，查询哪个部门的员工个数大于 3 的部门名和员工个数，同时按个数降序（添加排序条件）。

分析（1）：查询每个部门的员工个数。

SELECT
    COUNT(*) AS num,
    department_name
FROM
    employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY
    department_name;

分析（2）：根据（1）的结果筛选员工个数大于 3 的同时排序。

SELECT
    COUNT(*) AS num,
    department_name
FROM
    employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY
    department_name
HAVING
    num > 3
ORDER BY
    COUNT(*) DESC;

示例：查询员工名、部门名、工种名，并按部门名进行降序。

SELECT
    first_name,        --员工名
    department_name,   --部门名
    job_title          --工种名
FROM
    employees e        --员工表，起别名e
INNER JOIN departments d  --内连接部门表，别名d
ON e.department_id = d.department_id  --员工表部门id = 部门表部门id，表关联条件
INNER JOIN jobs j      --内连接工种表，别名j
ON e.job_id = j.job_id --员工表工种id = 工种表工种id，表关联条件
ORDER BY
    department_name DESC;  --按照部门名降序排序

（2）非等值连接

示例：查询员工的工资级别。

分析：

查询的表：employees、job_grades
查询思路：查询 employees 表中的 salary 字段在 job_grades 的最低工资和最高工资区间内，确定等级。

SELECT
    salary,                --员工工资
    grade_level            --工资等级
FROM
    employees e            --员工表，别名e
INNER JOIN job_grades g    --内连接工资等级表，别名g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;  --非等值连接：员工工资落在等级工资区间

示例：查询工资级别的个数大于 20 的个数，同时且按工资级别降序。

SELECT
    COUNT(*),              --统计每个工资级别下员工数量
    grade_level            --工资级别
FROM
    employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal  --非等值关联条件
GROUP BY
    grade_level            --按照工资级别分组
HAVING
    COUNT(*) > 20          --分组后过滤：只保留员工数大于20的等级
ORDER BY
    grade_level DESC;      --工资级别降序排序

（3）自连接

示例：查询员工的名字、上级的名字。

SELECT
    e.first_name,   --普通员工姓名，别名e代表员工
    m.first_name    --上级管理者姓名，别名m代表管理者
FROM
    employees e     --同一张表起第一个别名：员工
INNER JOIN employees m  --自连接：一张表当成两张表使用，第二个别名：管理者
ON e.manager_id = m.employee_id;  --员工的管理者ID = 管理者自己的员工编号

外连接：

左外连接：LEFT [OUTER]。
右外连接：RIGHT [OUTER]。

应用场景：用来查询一个表中有、另一个表中没有的记录。

特点总结：

结合项目来看，外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的则显示 null。外连接查询结果 = 内连接结果 + 主表中有、从表中没有的记录。
从实现思路看，左外连接，left join 左边是主表，右边是从表；右外连接，right join 右边是主表，左边是从表。
左外和右外交换两个表的顺序，能够完成相同的结果。
从实现思路看，全外连接 = 内连接结果 + 表1中有但表2中没有的 + 表2中有但表1中没有的。

连接类型对比：

连接类型	关键字	保留的记录	未匹配时的处理
左外连接	LEFT JOIN 或 LEFT OUTER JOIN	左表全部记录	右表字段填充 NULL
右外连接	RIGHT JOIN 或 RIGHT OUTER JOIN	右表全部记录	左表字段填充 NULL
全外连接	FULL JOIN 或 FULL OUTER JOIN	两表全部记录	缺失方字段填充 NULL

对比维度：

对比维度	LEFT JOIN	RIGHT JOIN	FULL JOIN
保留表	左表全部	右表全部	两表全部
匹配失败填充	右表字段为 NULL	左表字段为 NULL	对应方字段为 NULL
是否可互换	能够（调换表顺序）	能够（调换表顺序）	不能够（需 UNION 模拟）
MySQL 原生兼容	兼容	兼容	不兼容

结果集范围对比：

查询	包含 A 独有	包含交集	包含 B 独有	结果集范围
LEFT JOIN	是	是	否	A 全部
RIGHT JOIN	否	是	是	B 全部
INNER JOIN	否	是	否	交集
LEFT + WHERE B IS NULL	是	否	否	A 独有（A-B）
FULL + WHERE A IS NULL	否	否	是	B 独有（B-A）
FULL JOIN	是	是	是	并集

引入：查询男朋友不在男神表中的女神名，即查询没有男朋友的女神。

左外连接：

SELECT
    *
FROM
    beauty b
LEFT JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE
    bo.id IS NULL;

右外连接：

SELECT
    *
FROM
    boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE
    bo.id IS NOT NULL;  --筛选男神表id不为null，查询有男朋友的女神

示例：查询哪个部门没有员工。

左外连接：部门表为主表，员工表为从表。

SELECT
    d.*,
    e.employee_id
FROM
    departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE
    e.employee_id IS NULL;  --员工id为null，代表该部门没有匹配到员工，即无员工的部门

等价的右外连接写法：把部门放右边做主表，员工表放左边。

SELECT
    d.*,
    e.employee_id
FROM
    employees e
RIGHT JOIN departments d
ON d.department_id = e.department_id
WHERE
    e.employee_id IS NULL;  --左边员工表主键为null，代表该部门没有员工

全外连接：MySQL 不兼容 FULL JOIN 语法。

-- SELECT
--     *
-- FROM
--     beauty b
-- FULL JOIN boys bo
-- ON b.boyfriend_id = bo.id;

三国

街机

手游

单机

攻略

MySQL 流程控制函数与分组函数完整指南

一、流程控制函数

1、IF(expr1,expr2,expr3)

2、CASE

二、分组函数（聚合函数）

1、sum()

2、avg()

3、max()

4、min()

5、count()

6、聚合函数采用细节与总结

7、分组查询

三、连接查询

相关资讯

精彩合集

推荐专题

最新游戏

游戏排行

关注我们：