目录本文内容概要一、认识聚合查询二、聚合函数2.1 COUNT2.2 SUM2.3 AVG2.4 MAX 和 MIN三、分组查询GROUP BY3.1 GROUP BY 基本语法3.2 单字段分组3.3 多字段分组3.4 GROUP BY 中 SELECT 字段的注意事项3.5 GROUP BY 与 WHERE 配合使用四、分组结果筛选HAVING4.1 HAVING 基本语法4.2 WHERE 与 HAVING 的区别五、综合查询样例六、SELECT 语句的逻辑执行顺序本文内容概要本文主要介绍 MySQL 中的聚合查询与分组查询。通过本文的学习需要掌握 COUNT、SUM、AVG、MAX、MIN 等常用聚合函数的使用理解GROUP BY分组查询的基本语法以及单字段分组、多字段分组的使用方式掌握 GROUP BY 中 SELECT 字段的使用规则以及 WHERE 与 GROUP BY 的配合使用理解HAVING对分组结果进行筛选的作用及其与 WHERE 的区别。同时通过综合查询案例将 WHERE、GROUP BY、HAVING、ORDER BY、LIMIT 等语法进行串联并对SELECT 语句的逻辑执行顺序进行总结为后续学习多表查询、子查询等进阶 SQL 内容打下基础。一、认识聚合查询在MySQL 基础篇五数据操作基础 —— CRUD文章中学习的 SELECT 查询主要是对数据表中的记录进行筛选和获取。但在实际开发中我们有时并不关系每一条具体数据而是希望对一组数据进行统计和计算。例如查询学生总人数查询所有学生的平均成绩查询最高成绩和最低成绩统计每个专业分别有多少名学生计算每个专业学生的平均成绩。对于这类需求MySQL 提供了聚合查询来完成此类操作。聚合查询将多条数据作为一个整体进行统计或计算并得到一个汇总结果。MySQL 提供了一组专门用于统计和计算的函数这类函数通常被称为聚合函数。聚合函数作用COUNT()统计数据条数SUM()计算总和AVG()计算平均值MAX()获取最大值MIN()获取最小值二、聚合函数2.1 COUNTCOUNT统计数据条数NULL 值不参与统计select * from student; ------------------------- | id | name | age | score | ------------------------- | 1 | 张三 | 18 | 80 | | 2 | 李四 | 20 | 90 | | 3 | 王五 | 16 | 75 | | 4 | 赵六 | 22 | 58 | | 5 | 田七 | 20 | NULL | ------------------------- 5 rows in set (0.00 sec) 统计学生表中有多少名学生: select count(*) from student; ---------- | count(*) | ---------- | 5 | ---------- 1 row in set (0.00 sec) 还可以这样写: select count(1) from student; ---------- | count(1) | ---------- | 5 | ---------- 1 row in set (0.00 sec) 统计学生表中有多少名学生的成绩已经出来了: select count(score) from student; -------------- | count(score) | -------------- | 4 | -------------- 1 row in set (0.00 sec)2.2 SUMsum计算数据总和NULL值不参与统计select * from student; ------------------------- | id | name | age | score | ------------------------- | 1 | 张三 | 18 | 80 | | 2 | 李四 | 20 | 90 | | 3 | 王五 | 16 | 75 | | 4 | 赵六 | 22 | 58 | | 5 | 田七 | 20 | NULL | ------------------------- 5 rows in set (0.00 sec) 统计学生的成绩之和: select sum(score) from student; ------------ | sum(score) | ------------ | 303 | ------------ 1 row in set (0.00 sec)2.3 AVGavg计算数据平均值NULL 值不参与统计select * from student; ------------------------- | id | name | age | score | ------------------------- | 1 | 张三 | 18 | 80 | | 2 | 李四 | 20 | 90 | | 3 | 王五 | 16 | 75 | | 4 | 赵六 | 22 | 58 | | 5 | 田七 | 20 | NULL | ------------------------- 5 rows in set (0.00 sec) 统计学生成绩的平均值: select avg(score) from student; ------------ | avg(score) | ------------ | 75.7500 | ------------ 1 row in set (0.00 sec)2.4 MAX 和 MINmax 和 min获取表中数据的最大值和最小值NULL 值不参与统计select * from student; ------------------------- | id | name | age | score | ------------------------- | 1 | 张三 | 18 | 80 | | 2 | 李四 | 20 | 90 | | 3 | 王五 | 16 | 75 | | 4 | 赵六 | 22 | 58 | | 5 | 田七 | 20 | NULL | ------------------------- 5 rows in set (0.01 sec) select max(score) 最大值, min(score) 最小值 from student; ---------------------- | 最大值 | 最小值 | ---------------------- | 90 | 58 | ---------------------- 1 row in set (0.02 sec)三、分组查询GROUP BY前面学习的聚合函数默认会将满足条件的所有数据作为一个整体进行统计。例如select avg(score) from student;它会计算所有学生的平均成绩。但在实际业务中我们经常需要按照某个字段将数据划分为多个组然后分别对每个组进行统计。例如统计每个专业有多少名学生计算每个专业学生的平均成绩统计不同班级的最高成绩统计每个部门的员工数量。此时可以使用 GROUP BY 对查询结果进行分组。3.1 GROUP BY 基本语法GROUP BY 用于按照指定字段对数据进行分组。基本语法SELECT 分组字段, 聚合函数 FROM 表名 GROUP BY 分组字段;3.2 单字段分组----------------------------- | id | name | major | score | ----------------------------- | 1 | 张三 | 计算机 | 90 | | 2 | 李四 | 计算机 | 80 | | 3 | 王五 | 软件工程 | 95 | | 4 | 赵六 | 软件工程 | 85 | | 5 | 小明 | 人工智能 | 88 | ----------------------------- 统计每个专业的学生人数: select major, count(*) from student group by major; -------------------- | major | count(*) | -------------------- | 计算机 | 2 | | 软件工程 | 2 | | 人工智能 | 1 | --------------------因此GROUP BY 的作用是将具有相同分组字段值的数据划分到同一个组中。3.3 多字段分组GROUP BY 也可以同时按照多个字段进行分组基本语法GROUP BY 字段1, 字段2, ...;------------------------------------ | id | name | major | class | score | ------------------------------------ | 1 | 张三 | 计算机 | 1班 | 90 | | 2 | 李四 | 计算机 | 1班 | 80 | | 3 | 王五 | 计算机 | 2班 | 95 | | 4 | 赵六 | 软件工程 | 1班 | 85 | | 5 | 小明 | 软件工程 | 2班 | 88 | ------------------------------------ 统计每个专业中每个班级的最高分: select major, class, max(score) from student group by major, class;3.4 GROUP BY 中 SELECT 字段的注意事项在使用 GROUP BY 时一个非常重要的点SELECT 中出现的字段通常应该是 GROUP BY 中的分组字段或者聚合函数。下面这种写法看似想要得到每个专业成绩最高的姓名但是会存在问题MySQL 直接报错select name, major, max(score) from student group by major;因为对于 name 字段来说它并不是分组字段也没有参与聚合计算因此可以将其理解为一个不可压缩字段。而对于 major 字段数据正是按照 major 进行分组的。同一个分组中的 major 值一定相同因此可以将多个相同的 major 值“压缩”为一个值也可以将其理解为当前分组的标识。但是 name 不同分组之后一个组最终只对应一条查询结果此时 MySQL 无法确定 name 应该选择。因此 name 无法像 major 一样被压缩成为一个确定的值。3.5 GROUP BY 与 WHERE 配合使用示例只统计成绩合格的学生并计算每个专业的平均成绩select major, avg(score) from student where score 60 group by major;SELECT 的执行顺序SELECT 分组字段 | 聚合函数 FROM 表名 WHERE 条件 GROUP BY 分组字段 FROM 表名 - WHERE 条件 - GROUP BY 分组字段 - SELECT 分组字段 | 聚合函数因此WHERE 负责在分组前筛选数据GROUP BY 再对筛选后的数据进行分组。四、分组结果筛选HAVING4.1 HAVING 基本语法HAVING 用于对 GROUP BY 分组之后的结果进行筛选。基本语法SELECT 分组字段, 聚合函数 FROM 表名 GROUP BY 分组字段 HAVING 条件----------------------------- | id | name | major | score | ----------------------------- | 1 | 张三 | 计算机 | 90 | | 2 | 李四 | 计算机 | 80 | | 3 | 王五 | 软件工程 | 95 | | 4 | 赵六 | 软件工程 | 90 | | 5 | 小明 | 人工智能 | 88 | | 6 | 小红 | 人工智能 | 92 | ----------------------------- 查询平均成绩大于 85 分的专业: select major, avg(score) from student group by major having avg(score) 85; select major, avg(score) as avg_score from student group by major having avg_score 85;4.2 WHERE 与 HAVING 的区别初学 HAVING 时最容易产生的问题就是已经有了 WHERE为什么还需要 HAVING关键在于二者工作的阶段不同假设执行: select major, avg(score) from student where score 60 group by major having avg(score) 80; 可以理解为: 从 student 表中先筛选 score 80 的学生将筛选出来的学生通过专业 进行分组然后计算每组的平均成绩在通过筛选 avg(score) 80 得到最终分组结果对比WHEREHAVING筛选对象原始记录分组后的结果执行阶段GROUP BY之前GROUP BY之后是否常用于聚合结果否是典型条件score 60AVG(score) 80五、综合查询样例当我们把聚合函数、GROUP BY 分组以及 HAVING 分组结果筛选学完之后我们才真正把 MySQL 的基本查询学完。在实际查询中我们通常不会只使用其中某一个语句而是会根据需求将WHERE GROUP BY HAVING ORDER BY LIMIT组合起来使用从而完成更加复杂的统计查询。需求查询每个专业的平均成绩并按照平均成绩从高到低排序select major, avg(score) avg_score from student group by major order by avg_score desc;需求查询平均成绩大于 85 分的专业并按照平均成绩降序排列select major, avg(score) avg_score from student group by major having avg_score 85 order by avg_score desc;需求查询平均成绩最高的两个专业select major, avg(score) avg_score from student group by major order by avg_score desc limit 2;需求只统计成绩及格的学生按照专业进行分组查询每个专业的学生人数和平均成绩只保留人数不少于 2 人的专业并按照平均成绩从高到低排列最终只显示前 3 个专业。select major, count(*) as student_count, avg(score) as avg_score from student where score 60 group by major having student_count 2 order by avg_score desc limit 3;在聚合查询中WHERE 用于筛选分组前的原始数据GROUP BY 用于对数据进行分组HAVING 用于筛选分组后的结果ORDER BY 用于对最终结果排序而 LIMIT 用于限制最终返回的数据数量。六、SELECT 语句的逻辑执行顺序理解 SELECT 语句的逻辑执行顺序至关重要它不仅能帮助我们解决别名是否可用的问题还能帮助我们梳理一条完整的查询语句是如何根据需要来写出来的。一条较为完整的查询语句SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段 HAVING 分组筛选条件 ORDER BY 排序字段 LIMIT 数量;顺序子句作用1FROM确定数据来源2WHERE筛选原始数据3GROUP BY对数据进行分组4HAVING筛选分组后的结果5SELECT确定最终需要查询的字段6DISTINCT对查询结果去重7ORDER BY对结果进行排序8LIMIT限制最终返回的数据数量这里所说的是 SELECT 语句的逻辑执行顺序主要用于帮助我们理解 SQL 的语义。MySQL 优化器在真正执行 SQL 时可能会根据索引、数据量等情况对实际执行过程进行优化并不一定严格按照该流程进行物理执行。