数据库系统原理与应用教程（045）—— MySQL 查询（七）：聚合函数

聚合函数又称为统计函数，可以对查询结果进行统计和汇总，对表中某一列的数据值进行计算并返回一个单一值。聚合函数经常与 GROUP BY 子句一同使用。

常用的聚合函数包括 SUM、COUNT、AVG、MAX 和 MIN，实现对表中数据的统计（求和、计数、平均值、最大值和最小值等）。GROUP_CONCAT 函数的功能与聚合函数类似，可以对某一列中的数据值进行连接操作。

一、聚合函数的用法

聚合函数的语法格式如下：

-- 1、计数函数
-- 使用 count(*) 时不忽略 NULL 值，使用 count(列名|表达式) 时忽略 NULL 值
COUNT(distinct *|列名|表达式)

-- 2、求和函数
-- 列的类型或表达式返回值的类型必须是数值类型
SUM(列名|表达式)

-- 3、求平均值函数
-- 列的类型或表达式返回值的类型必须是数值类型
AVG(列名|表达式)

-- 4、求最大值函数
-- 列的类型或表达式返回值的类型可以是任意类型
MAX(列名|表达式)

-- 5、求最小值函数
-- 列的类型或表达式返回值的类型可以是任意类型
MIN(列名|表达式)

-- 6、连接数据值函数
-- 列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符，如果省略 separator，则默认的分隔符为逗号
-- 使用 distinct 可以去除重复数据
GROUP_CONCAT([distinct] 列名 [order by 排序字段 asc|desc] separator '分隔符')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

说明：

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

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

例如：

mysql> select s_name,count(*) from student;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mydb.student.s_name'; this is incompatible with sql_mo
de=only_full_group_by

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

二、计数函数：COUNT

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

语法格式如下：

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

例如：

（1）统计所有学生的人数

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（2）统计地址为【新乡市】的学生人数

mysql> select count(1) from student where addr = '新乡市';
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（3）统计 2000 年以后出生的学生数量

mysql> select count(*) from student where birth >= '2000-1-1';
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（4）统计学生所在的地址数量

mysql> select count(distinct addr) from student;
+----------------------+
| count(distinct addr) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

（5）数据存在 NULL 值的情况

mysql> create table t12(id int primary key,name char(20),salary int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t12 
    -> values(1,'Jack',5200),(2,'Tom',4800),(3,'Black',3700),(4,null,null),(5,null,null);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

-- 统计记录数
mysql> select count(*) from t12;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

-- 统计记录数（忽略 name 为 NULL 的记录）
mysql> select count(name) from t12;
+-------------+
| count(name) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

三、求和函数：SUM

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

语法格式如下：

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

例如：

（1）统计学生 S2011 选修课程的总分。

mysql> select sum(score) from score where s_id = 'S2011';
+------------+
| sum(score) |
+------------+
|        318 |
+------------+
1 row in set (0.04 sec)
1
2
3
4
5
6
7

（2）统计 t12 表中的 salary 列的总和。

mysql> select * from t12;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  1 | Jack  |   5200 |
|  2 | Tom   |   4800 |
|  3 | Black |   3700 |
|  4 | NULL  |   NULL |
|  5 | NULL  |   NULL |
+----+-------+--------+
5 rows in set (0.00 sec)

-- 统计时忽略 NULL 值
mysql> select sum(salary) from t12;
+-------------+
| sum(salary) |
+-------------+
|       13700 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（3）统计 2000 年以后出生的学生人数。

mysql> select sum(1) from student where birth >= '2000-1-1';
+--------+
| sum(1) |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（4）统计地址为【新乡市】的学生人数。

mysql> select sum(if(addr = '新乡市',1,0)) cnt from student;
+------+
| cnt  |
+------+
|    2 |
+------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

四、求平均值函数：AVG

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

语法格式如下：

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

例如：

（1）统计学生 S2011 选修课程的平均分。

mysql> select avg(score) from score where s_id = 'S2011';
+------------+
| avg(score) |
+------------+
|    79.5000 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（2）统计 t12 表中的 salary 列的平均值。

mysql> select * from t12;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  1 | Jack  |   5200 |
|  2 | Tom   |   4800 |
|  3 | Black |   3700 |
|  4 | NULL  |   NULL |
|  5 | NULL  |   NULL |
+----+-------+--------+
5 rows in set (0.00 sec)

-- 统计时忽略 NULL 值
mysql> select avg(salary) from t12;
+-------------+
| avg(salary) |
+-------------+
|   4566.6667 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（3）求所有学生的平均年龄。

-- 统计表达式的平均值
mysql> select avg(year(now())-year(birth)) avg_age from student;
+---------+
| avg_age |
+---------+
| 22.9286 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

五、求最大值函数：MAX

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

语法格式如下：

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

例如：

（1）查询 C102 课程的最高分。

mysql> select max(score) from score where c_id = 'C102';
+------------+
| max(score) |
+------------+
|         97 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（2）查询所有学生中年龄的最大值。

mysql> select max(year(now())-year(birth)) max_age from student;
+---------+
| max_age |
+---------+
|      24 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

六、求最小值函数：MIN

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

语法格式如下：

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

例如：

（1）查询 C102 课程的最低分。

mysql> select min(score) from score where c_id = 'C102';
+------------+
| min(score) |
+------------+
|         52 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（2）查询所有学生中年龄的最小值。

mysql> select min(year(now())-year(birth)) min_age from student;
+---------+
| min_age |
+---------+
|      22 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

七、连接数据值函数：GROUP_CONCAT

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

语法格式如下：

-- 列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符，如果省略 separator，则默认的分隔符为逗号
-- 使用 distinct 可以去除重复数据
GROUP_CONCAT([distinct] 列名 [order by 排序字段 asc|desc] separator '分隔符');
1
2
3
4

例如：

（1）查询地址为【郑州市】的学生名单，姓名之间用逗号分隔。

mysql> select group_concat(s_name) from student where addr = '郑州市';
+----------------------------------------------------------+
| group_concat(s_name)                                     |
+----------------------------------------------------------+
| 曹梦德,刘艳,周华建,张学有,李明博,达芬奇                  |
+----------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（2）查询地址为【郑州市】的学生名单，姓名之间用【||】分隔。

mysql> select group_concat(s_name separator '||') from student where addr = '郑州市';
+---------------------------------------------------------------+
| group_concat(s_name separator '||')                           |
+---------------------------------------------------------------+
| 曹梦德||刘艳||周华建||张学有||李明博||达芬奇                  |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

（3）查询地址为【郑州市】的学生名单，姓名之间用【|】分隔，并且按 birth 进行排序。

mysql> select s_name,birth,addr from student where addr = '郑州市' order by birth;
+-----------+---------------------+-----------+
| s_name    | birth               | addr      |
+-----------+---------------------+-----------+
| 曹梦德    | 1998-02-13 00:00:00 | 郑州市    |
| 刘艳      | 1998-06-24 00:00:00 | 郑州市    |
| 张学有    | 1998-07-06 00:00:00 | 郑州市    |
| 周华建    | 1999-05-25 00:00:00 | 郑州市    |
| 李明博    | 1999-10-26 00:00:00 | 郑州市    |
| 达芬奇    | 1999-12-31 00:00:00 | 郑州市    |
+-----------+---------------------+-----------+
6 rows in set (0.00 sec)

mysql> select group_concat(s_name order by birth separator '|') from student where addr = '郑州市';
+----------------------------------------------------------+
| group_concat(s_name order by birth separator '|')        |
+----------------------------------------------------------+
| 曹梦德|刘艳|张学有|周华建|李明博|达芬奇                  |
+----------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

（4）查询所有学生的 addr 列表。

mysql> select group_concat(addr) from student;
+---------------------------------------------------------------------------------------------+
| group_concat(addr)                                                                          |
+---------------------------------------------------------------------------------------------+
| 信阳市,新乡市,郑州市,郑州市,信阳市,开封市,开封市,郑州市,新乡市,信阳市,开封市,郑州市,郑州市,郑州市   |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 消除重复的地址
mysql> select group_concat(distinct addr) from student;
+-----------------------------------------+
| group_concat(distinct addr)             |
+-----------------------------------------+
| 信阳市,开封市,新乡市,郑州市             |
+-----------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16