MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询

MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询

聚合函数又称为统计函数，可以对查询结果进行统计和汇总，对表中某一列的数据值进行计算并返回一个单一值。

使用 GROUP BY 关键字可以将查询结果按照一个或多个列或者表达式进行分组，分组的依据为 GROUP BY 后面的列名或表达式。GROUP BY 通常与聚合函数合用。

一、聚合函数

常用的聚合函数包括 SUM、COUNT、AVG、MAX 和 MIN，实现对表中数据的统计，GROUP_CONCAT 函数的功能与聚合函数类似，可以对某一列中的数据值进行连接操作。聚合函数的语法格式如下：

-- 1、计数函数（count）：使用 count(*) 时不忽略 NULL 值，使用 count(列名|表达式) 时忽略 NULL 值
-- 使用 distinct 可以去除重复数据
COUNT(distinct *|列名|表达式)-- 2、求和函数（sum）：列的类型或表达式返回值的类型必须是数值类型
SUM(列名|表达式)-- 3、求平均值函数：列的类型或表达式返回值的类型必须是数值类型
AVG(列名|表达式)-- 4、求最大值函数：列的类型或表达式返回值的类型可以是任意类型
MAX(列名|表达式)-- 5、求最小值函数：列的类型或表达式返回值的类型可以是任意类型
MIN(列名|表达式)-- 6、连接数据值函数：列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符，如果省略 separator，则默认的分隔符为逗号
-- 使用 distinct 可以去除重复数据
GROUP_CONCAT([distinct] 列名 separator '分隔符')

说明：

（1）如果查询中使用了聚合函数，在没有分组的情况下，查询结果只有一行（只有一个统计结果）。如果使用 GROUP BY 分组，则每一个分组有一个统计结果。

（2）如果查询中使用了聚合函数，在没有分组的情况下，SELECT 后面除了聚合函数之外，一般不能有列名。

1、计数函数（COUNT）

使用 COUNT( ) 函数用于统计记录数量，通常与 GROUP BY 子句合用。

语法格式如下：

COUNT(distinct *|列名|表达式)-- 说明：使用 count(*) 时不忽略 NULL 值，使用 count(字段|表达式) 时忽略 NULL 值

例如：

（1）统计学生人数

mysql> select count(*) stu_cnt from stu;
+---------+
| stu_cnt |
+---------+
|      14 |
+---------+
1 row in set (0.00 sec)

（2）统计学生所在院系的数量

mysql> select count(distinct dept_id) as dept_cnt from stu;
+----------+
| dept_cnt |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

（3）统计女生人数

mysql> select count(1) stu_cnt from stu where gender='女';
+---------+
| stu_cnt |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

2、求和函数（SUM）

使用 SUM( ) 函数可以对表中某一列的数据求和，统计时忽略 NULL 值。如果没有匹配行，则返回 NULL 值。常与 GROUP BY 子句合用。语法格式如下：

SUM(字段|表达式)-- 说明：列的类型或表达式返回值的类型必须是数值类型

例如：

（1）统计学号为【20220124002】的同学选修的所有课程的总分

mysql> select sum(score) total_score from xk where s_id='20220124002';
+-------------+
| total_score |
+-------------+
|      226.00 |
+-------------+
1 row in set (0.00 sec)

（2）统计女生人数

mysql> select sum(1) stu_cnt from stu where gender='女';
+---------+
| stu_cnt |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

（3）统计男生人数

mysql> select sum(if(gender='男',1,0)) stu_cnt from stu;
+---------+
| stu_cnt |
+---------+
|       8 |
+---------+
1 row in set (0.00 sec)

3、求平均值函数（AVG）

使用 AVG( ) 函数可以计算表中某一列数据的平均值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下：

AVG(字段|表达式)-- 说明：列的类型或表达式返回值的类型必须是数值类型

例如：

（1）统计学号为【20220124002】的同学选修的所有课程的平均分

mysql> select avg(score) avg_score from xk where s_id='20220124002';
+-------------+
| avg_score   |
+-------------+
|   75.333333 |
+-------------+
1 row in set (0.00 sec)

（2）统计学生的平均年龄

mysql> select avg(year(now())-year(birth)) avg_age from stu;
+---------+
| avg_age |
+---------+
| 22.0714 |
+---------+
1 row in set (0.00 sec)

4、求最大值函数（MAX）

使用 MAX( ) 函数统计某一列数据的最大值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下：

MAX(字段|表达式)-- 说明：列的类型或表达式返回值的类型可以是任意类型

例如：

（1）查询所有学生的最大年龄

mysql> select max(year(now())-year(birth)) max_age from stu;
+---------+
| max_age |
+---------+
|      24 |
+---------+
1 row in set (0.00 sec)

（2）查询编号为【C01002】的课程的最高分

mysql> select max(score) max_score from xk where c_id='C01002';
+-----------+
| max_score |
+-----------+
|     69.00 |
+-----------+
1 row in set (0.00 sec)

5、求最小值函数（MIN）

使用 MIN( ) 函数统计某一列数据的最小值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下：

MIN(字段|表达式)-- 说明：列的类型或表达式返回值的类型可以是任意类型

例如：

（1）查询所有学生的最小年龄

mysql> select min(year(now())-year(birth)) min_age from stu;
+---------+
| min_age |
+---------+
|      21 |
+---------+
1 row in set (0.00 sec)

（2）查询编号为【C01002】的课程的最低分

mysql> select min(score) min_score from xk where c_id='C01002';
+-----------+
| min_score |
+-----------+
|     68.00 |
+-----------+
1 row in set (0.00 sec)

6、连接数据值函数（GROUP_CONCAT）

使用 GROUP_CONCAT( ) 函数可以把某一列的数据值连接成一个字符串，数据值之间使用指定的分隔符分隔（默认为逗号）。语法格式如下：

GROUP_CONCAT([distinct] 列名 separator '分隔符')-- 说明：列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符，如果省略 separator，则默认的分隔符为逗号

例如：

（1）查询编号为【D01】的学院的学生名单

mysql> select group_concat(s_name) from stu where dept_id='D01';
+-----------------------------+
| group_concat(s_name)        |
+-----------------------------+
| 薛智玲,杨铭华,张从超,孙金航 |
+-----------------------------+
1 row in set (0.00 sec)

（2）查询编号为【D02】的学院的学生名单，学生之间用空格分隔

mysql> select group_concat(s_name separator '  ') from stu where dept_id='D01';
+-------------------------------------+
| group_concat(s_name separator '  ') |
+-------------------------------------+
| 薛智玲  杨铭华  张从超  孙金航      |
+-------------------------------------+
1 row in set (0.00 sec)

二、分组查询

使用 GROUP BY 关键字可以将查询结果按照一个或多个列或者表达式进行分组，分组的依据为 GROUP BY 后面的列名或表达式。GROUP BY 通常与聚合函数合用。

1、GROUP BY 的语法

GROUP BY 子句的语法格式如下：

GROUP BY <列名|表达式>[,...] [HAVING 条件表达式] [WITH ROLLUP]--说明：
（1）使用分组查询时，select 后面的字段列表只能包含 GROUP BY 后面的列名或表达式以及聚合函数，不能包含其他的列或表达式，否则会报错。
（2）列名|表达式：分组依据，按列名或表达式进行分组。
（3）HAVING 条件表达式：对分组进行选择，符合条件表达式的结果才会显示。
（4）WITH ROLLUP：在所有记录的最后加上一条记录，该记录为对所有行的统计结果（求和）。

2、使用列名分组

例如：按照 s_id 分组，统计每个学生所学课程的平均分

mysql> select s_id, avg(score) avg_score from xk group by s_id;
+-------------+-----------+
| s_id        | avg_score |
+-------------+-----------+
| 20220124001 | 73.666667 |
| 20220124002 | 75.333333 |
| 20220124003 | 94.500000 |
| 20220124004 | 88.500000 |
| 20220214001 | 85.666667 |
| 20220214002 | 68.000000 |
| 20220214003 | 87.333333 |
| 20220325101 | 80.000000 |
| 20220325102 | 80.333333 |
| 20220325103 | 69.333333 |
| 20220410101 | 82.666667 |
| 20220410102 | 70.666667 |
| 20220410103 | 69.333333 |
| 20220410104 | 69.500000 |
+-------------+-----------+
14 rows in set (0.00 sec)

3、使用表达式分组

例如：按照学生的姓氏分组，查询每组对应的学生人数

mysql> select left(s_name, 2) surname, count(*) stu_cnt from stu group by surname;
+---------+---------+
| surname | stu_cnt |
+---------+---------+
| 孙      |       1 |
| 夏      |       1 |
| 谭      |       1 |
| 杨      |       1 |
| 薛      |       1 |
| 赵      |       2 |
| 张      |       2 |
| 裴      |       1 |
| 聂      |       1 |
| 周      |       1 |
| 董      |       1 |
| 江      |       1 |
+---------+---------+
12 rows in set (0.00 sec)

4、分组的同时使用 WHERE 子句

分组时如果使用了 WHERE 子句，则先使用 WHERE 对表中的数据进行筛选，然后进行分组和统计。

例如：利用 xk 表统计每门课程考试分数大于 80 分的学生人数

mysql> select c_id, count(*) stu_cnt from xk where score>80 group by c_id;
+--------+---------+
| c_id   | stu_cnt |
+--------+---------+
| C01001 |       3 |
| C01003 |       3 |
| C02102 |       1 |
| C02103 |       2 |
| C03201 |       2 |
| C03202 |       2 |
| C04112 |       2 |
| C04113 |       1 |
+--------+---------+
8 rows in set (0.00 sec)

5、使用 HAVING 对分组进行选择

使用 HAVING 子句可以对分组进行选择。当 HAVING 子句与 WHER 子句同时使用时，查询执行的顺序为：先使用 WHERE 对表中的记录进行筛选，然后对满足条件的记录分组与统计，再使用 HAVING 子句对分组进行选择。

例如：

（1）利用 xk 表查询每门课的选修人数，并且只显示选修人数大于 3 门的课程信息

mysql> select c_id, count(*) stu_cnt from xk group by c_id having stu_cnt>3;
+--------+---------+
| c_id   | stu_cnt |
+--------+---------+
| C01001 |       4 |
| C01003 |       4 |
| C04111 |       4 |
| C04112 |       4 |
+--------+---------+
4 rows in set (0.00 sec)

（2）利用 stu 表查询每个学院的男生人数，并且只显示男生人数大于 3 人的学院信息

mysql> select dept_id, count(*) stu_cnt from stu where gender='男' group by dept_id having stu_cnt>2;
+---------+---------+
| dept_id | stu_cnt |
+---------+---------+
| D03     |       3 |
+---------+---------+
1 row in set (0.00 sec)

6、WITH ROLLUP 参数

在所有记录的最后加上一条记录，该记录为对所有行的统计结果（求和）。

例如：

（1）利用 stu 表查询每个学院的学生人数以及总人数

-- 把 dept_id 为空的学生的 dept_id 修改为 D04
mysql> update stu set dept_id='D04' where dept_id is null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0-- 分组查询
mysql> select dept_id, count(*) stu_cnt from stu group by dept_id with rollup;
+---------+---------+
| dept_id | stu_cnt |
+---------+---------+
| D01     |       4 |
| D02     |       3 |
| D03     |       3 |
| D04     |       4 |
| NULL    |      14 |
+---------+---------+
5 rows in set (0.00 sec)

（2）利用 stu 表查询每个学院的学生人数以及总人数，并把最后一行的 NULL 修改为 stu_total

mysql> select ifnull(dept_id, 'total') dept_id, count(*) stu_cnt from stu group by dept_id with rollup;
+---------+---------+
| dept_id | stu_cnt |
+---------+---------+
| D01     |       4 |
| D02     |       3 |
| D03     |       3 |
| D04     |       4 |
| total   |      14 |
+---------+---------+
5 rows in set, 1 warning (0.00 sec)