MySQL表聚合函数

前言

    哈喽，各位小伙伴大家好，本篇文章为大家介绍几个MySQL中常用的聚合函数，什么是聚合函数，相信第一次看到这个名词的小伙伴是比较懵的，举个例子，比如说统计表中数据的个数，就可以使用MySQL中提供的函数COUNT进行统计，下面我们就具体来看看都有哪些聚合函数，以及如何使用聚合函数。

1.聚合函数

首先整体认识一下都有哪些聚合函数，然后再结合具体的案例对每一种聚合函数进行学习：

案例：

1.统计班级共有多少同学

-- 使用 * 做统计，不受 NULL 影响

mysql> select* from student;
+----+--------+----+---------+
| id | name   | sn | qq      |
+----+--------+----+---------+
|  1 | 张三   |  1 | 123456  |
|  2 | 李四   |  2 | 1234578 |
|  3 | 王五   |  3 | 234567  |
|  5 | 张飞   |  6 | 2467764 |
|  6 | 刘备   |  4 | NULL    |
+----+--------+----+---------+
5 rows in set (0.00 sec)
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)
-- 使用表达式做统计
mysql> select count(1) from student;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

2.统计班级收集的 qq 号有多少

-- NULL 不会计入结果
mysql> select count(qq) from student;
+-----------+
| count(qq) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

3.统计本次考试的数学成绩分数个数

mysql> select* from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  8 | 唐三藏    |      67 |   98 |      56 |
|  9 | 孙悟空    |      87 |   78 |      77 |
| 10 | 猪悟能    |      88 |   98 |      90 |
| 11 | 曹孟德    |      82 |   84 |      67 |
| 12 | 刘玄德    |      55 |   85 |      45 |
| 13 | 孙权      |      70 |   73 |      78 |
| 14 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
--count(math)统计的是全部的成绩
mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)
--count(distinct math)统计的是去重的成绩
mysql> select count(distinct math) from exam_result;
+----------------------+
| count(distinct math) |
+----------------------+
|                    6 |
+----------------------+
1 row in set (0.00 sec)

4.统计数学成绩总分

mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       581 |
+-----------+
1 row in set (0.00 sec)

5.统计平均分

mysql> select avg(math+english+chinese) 平均总分 from exam_result;
+--------------------+
| 平均总分           |
+--------------------+
| 221.14285714285714 |
+--------------------+
1 row in set (0.00 sec)

6.返回英语最高分

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.01 sec)

7.返回 > 70 分以上的数学最低分

mysql> select min(math) from exam_result where math > 70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.00 sec)

2.分组统计

上面介绍了聚合函数的相关使用，为什么这里突然出现了一个分组统计，可能会有小伙伴感到很奇怪，不要担心，下面我们来认识一下什么是分组统计。

分组统计的目的是为了实现聚合函数的使用，所以说分组是手段，最终目的是为了实现聚合统计，可能这样说大家还是理解起来不太直观，下面我们通过实际的案例进行说明：

这里有三张表：

EMP员工表
DEPT部门表
SALGRADE工资等级表

emp表：

dept表：

salgrade表:

通过第一张表可以观察出，表中有许多员工的信息，每个员工都具有不同信息，但是有几个员工具有相同的信息，就是员工的部门编号：

此时有一个需求：显示每个部门的平均工资和最后工资

如果直接使用聚合函数进行统计的就会发现实现不了上述的需求，此时就需要先进行分组，然后再使用相应的聚合函数统计，这也就是上面所说的分组是手段，而真正的目的是为了实现聚合统计。

下面我们就具体来看看如何进行分组统计：

语法：

select column1, column2, .. from table group by column;

案例：

显示每个部门的平均工资和最高工资：

mysql> select deptno,max(sal) 最高,avg(sal) 平均 from emp group by deptno;
+--------+---------+-------------+
| deptno | 最高    | 平均        |
+--------+---------+-------------+
|     10 | 5000.00 | 2916.666667 |
|     20 | 3000.00 | 2175.000000 |
|     30 | 2850.00 | 1566.666667 |
+--------+---------+-------------+
3 rows in set (0.00 sec)

显示每个部门的每种岗位的平均工资和最低工资：

mysql> select deptno,job,avg(sal) 平均,min(sal) 最低 from emp group by deptno,job;
+--------+-----------+-------------+---------+
| deptno | job       | 平均        | 最低    |
+--------+-----------+-------------+---------+
|     10 | CLERK     | 1300.000000 | 1300.00 |
|     10 | MANAGER   | 2450.000000 | 2450.00 |
|     10 | PRESIDENT | 5000.000000 | 5000.00 |
|     20 | ANALYST   | 3000.000000 | 3000.00 |
|     20 | CLERK     |  950.000000 |  800.00 |
|     20 | MANAGER   | 2975.000000 | 2975.00 |
|     30 | CLERK     |  950.000000 |  950.00 |
|     30 | MANAGER   | 2850.000000 | 2850.00 |
|     30 | SALESMAN  | 1400.000000 | 1250.00 |
+--------+-----------+-------------+---------+
9 rows in set (0.00 sec)

显示平均工资低于2000的部门和它的平均工资：

实现思路：

a.统计每个部门的平均工资：

mysql> select deptno,avg(sal) 平均工资 from emp group by deptno;
+--------+--------------+
| deptno | 平均工资     |
+--------+--------------+
|     10 |  2916.666667 |
|     20 |  2175.000000 |
|     30 |  1566.666667 |
+--------+--------------+
3 rows in set (0.00 sec)

b.对结果进行过滤：

having和group by配合使用，对group by结果进行过滤

mysql> select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 < 2000;
+--------+--------------+
| deptno | 平均工资     |
+--------+--------------+
|     30 |  1566.666667 |
+--------+--------------+
1 row in set (0.00 sec)

--having经常和group by搭配使用，作用是对分组进行筛选，作用像where。

总结

    本篇文章为大家介绍了什么是聚合函数，聚合函数如何使用以及分组统计，了解了这些使用规则之后，能够在以后对MySQL表数据操作的时候更加便捷和快速。