MySQL 基础篇（六）：聚合查询与分组查询

目录

本文内容概要

一、认识聚合查询

二、聚合函数

2.1 COUNT

2.2 SUM

2.3 AVG

2.4 MAX 和 MIN

三、分组查询：GROUP BY

3.1 GROUP BY 基本语法

3.2 单字段分组

3.3 多字段分组

3.4 GROUP BY 中 SELECT 字段的注意事项

3.5 GROUP BY 与 WHERE 配合使用

四、分组结果筛选：HAVING

4.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 提供了一组专门用于统计和计算的函数，这类函数通常被称为聚合函数。

二、聚合函数

2.1 COUNT

COUNT：统计数据条数

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 SUM

sum：计算数据总和

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 AVG

avg：计算数据平均值

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 和 MIN

max 和 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 再对筛选后的数据进行分组。

四、分组结果筛选：HAVING

4.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 得到最终分组结果

五、综合查询样例

当我们把聚合函数、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 数量;

这里所说的是 SELECT 语句的逻辑执行顺序，主要用于帮助我们理解 SQL 的语义。MySQL 优化器在真正执行 SQL 时，可能会根据索引、数据量等情况对实际执行过程进行优化，并不一定严格按照该流程进行物理执行。