MySQL基础之分组函数

分组函数主要用于统计，又称为聚合函数、统计函数或组函数。

常见的分组函数有：

SUM(expr) 求和
AVG([DISTINCT] expr) 求平均值
MAX(expr) 求最大值
MIN(expr) 求最小值
COUNT(DISTINCT expr,[expr...]) 计算个数

先建一个员工表用于测试，表名emp，包含姓名，部门编号，工作，工资，入职日期，奖金等信息。

 CREATE TABLE `emp` (
 `id` int(255) NOT NULL AUTO_INCREMENT,
 `ename` varchar(255) DEFAULT NULL,
 `job` varchar(255) DEFAULT NULL,
 `sal` int(11) DEFAULT NULL,
 `deptNo` int(11) DEFAULT NULL,
 `hiredate` datetime NOT NULL,
 `bonus` int(11) DEFAULT NULL,
 `orderNo` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

简单插入几行数据来测试

简单使用

实例：

#sum
mysql> select sum(sal) as sum from emp;
+-------+
| sum |
+-------+
| 37700 |
+-------+
#avg
mysql> select avg(sal) as avg from emp;
+-----------+
| avg |
+-----------+
| 3770.0000 |
+-----------+
#max
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000 |
+----------+
#min
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 2000 |
+----------+
#count
mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
| 10 |
+------------+

搭配其他函数使用

mysql> select round(avg(sal),2) as avg from emp;
+---------+
| avg |
+---------+
| 3770.00 |
+---------+

参数的类型

同时使用数值、字符型和日期参数来测试

sum和avg

mysql> select sum(sal) ,
 SUM(ename),
 sum(hiredate) from emp;
+----------+------------+------------------------+
| sum(sal) | SUM(ename) | sum(hiredate) |
+----------+------------+------------------------+
| 37700 | 0 | 201785163831138.000000 |
+----------+------------+------------------------+
mysql> select avg(sal) ,
 avg(ename),
 avg(hiredate) from emp;
+-----------+------------+---------------------------+
| avg(sal) | avg(ename) | avg(hiredate) |
+-----------+------------+---------------------------+
| 3770.0000 | 0 | 20178516383113.8010000000 |
+-----------+------------+---------------------------+

结果中看出，sum和avg的参数是字符和日期时，虽然没有语法错误，但却是没有意义的。

max和min

mysql> select max(sal) ,
 max(ename),
 max(hiredate) from emp;
+----------+------------+---------------------+
| max(sal) | max(ename) | max(hiredate) |
+----------+------------+---------------------+
| 5000 | Zara | 2019-06-03 18:30:39 |
+----------+------------+---------------------+
mysql> select min(sal) ,
 min(ename),
 min(hiredate) from emp;
+----------+------------+---------------------+
| min(sal) | min(ename) | min(hiredate) |
+----------+------------+---------------------+
| 2000 | Anette | 2016-02-10 18:32:03 |
+----------+------------+---------------------+

字符和日期也是可以排序的，有可比较性，所以它们作为max和min的参数也能得到有意义的结果。

count

mysql> select count(sal) ,
 count(ename),
 count(hiredate) from emp;
+------------+--------------+-----------------+
| count(sal) | count(ename) | count(hiredate) |
+------------+--------------+-----------------+
| 10 | 10 | 10 |
+------------+--------------+-----------------+

同样，count使用字符型和日期也是有意义的。

MySQL中函数使用的参数，不但要符合语法，并且要有意义。关于它们的参数类型，我们可以得出结论：

1. sub、avg一般用于处理数值型。
2. max、min、count可以处理任意值。

是否忽略null

以emp表中的bonus列来测试。

先看一下数据

其中有5行的值是null,5行是非null。

sum与avg实例：

mysql> select sum(bonus),
 avg(bonus),
 sum(bonus)/5,
 sum(bonus)/10 from emp;
+------------+------------+--------------+---------------+
| sum(bonus) | avg(bonus) | sum(bonus)/5 | sum(bonus)/10 |
+------------+------------+--------------+---------------+
| 1650 | 330.0000 | 330.0000 | 165.0000 |
+------------+------------+--------------+---------------+

首先，和是1650，我们通过计算能够知道，它与所有bonus值非null的行的加起来是一样的。

还有，和除以5的结果与agv计算的平均值相等，也就是说平均值的计算也是在非null的行中计算的。

所以sum和avg是忽略null的。

max和min

mysql> select max(bonus),
 min(bonus) from emp;
+------------+------------+
| max(bonus) | min(bonus) |
+------------+------------+
| 500 | 200 |
+------------+------------+

max和min明显也是是忽略null的，否则在结果中，将会出现max的值或者min的值为null的情况了。

count

mysql> select count(bonus) from emp;
+--------------+
| count(bonus) |
+--------------+
| 5 |
 +--------------+

结果只有5行，显然count也是忽略null的。

所以，以上组合函数都是忽略null值的。我们在使用的时候要考虑字段值是否为null的情况。

和distinct搭配使用达到去重的效果

以sum为例：

mysql> select sum(DISTINCT sal),
 sum(sal) from emp;
+-------------------+----------+
| sum(DISTINCT sal) | sum(sal) |
+-------------------+----------+
| 32700 | 37700 |
+-------------------+----------+
1 row in set (0.00 sec)

表中有两个人的sal是5000，所去重后结果比没去重的少5000没错。

好了，对于分组函数的分享就到此。