【MySQL】十、分组函数（多行处理函数）和单行处理函数（ifnull）

1. 分组函数（聚合函数，统计函数，多行统计函数）：

作用：用作统计使用，又称为聚合函数或者统计函数或者组函数。

1.1 分组函数的分类

分组函数一共有下面5个：

• sum() 求和
• avg() 求平均值
• max() 求最大值
• min() 求最小值
• count() 计算个数

1.2 分组函数的特点

1. sum() , avg()一般用于处理数值
2. max() , min() , count()可以处理任何类型。
3. 以上分组函数都会自动过滤null。
4. 可以实现与distinct组合使用。
5. 在计算时，统计多行数据，最终输出的结果为1行。

所有的分组函数都是对某一组数据进行操作的。

2. 分组函数案例

2.1 分组函数使用案例1

// 查询所有员工的工资总和
select sum(sal) from emp;
// 查询所有员工的平均薪资
select avg(sal) from emp;
// 查询所有员工的最高薪资
select max(sal) from emp;
// 查询所有员工的最低薪资
select min(sal) from emp;
// 查询员工的总人数
select count(ename) from emp;

2.2 分组函数使用案例2

查询出工资高于平均工资的员工

select ename, sal from emp where sal >  avg(sal);

查询报错：

ERROR 1111 (HY000): Invalid use of group function

思考以上错误信息：无效的使用了分组函数。

原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。

解释： group by（第3步执行）实在where（第2步执行）执行之后才能使用，要先分组才能在使用。

执行顺序：

select ...				5
from ...				1
where ...				2   // 第一层条件过滤
group by..				3	
having ...				4   // 第二层过滤数据
order by ..				6

正确写法：

第一步：找出平均工资；

select avg(sal) from emp;

+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

第二步：找出高于平均工资的员工；

+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

将两个语句合在一起(子查询)：

select ename, sal from emp where sal > (select avg(sal) from emp);

2.3 count(*)和count(字段)的区别

（1）count(*)

统计的是总记录的条数

select count(*) from emp;

查询结果：

+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

（2）count(字段)

表示count当中字段不为空的元素总数

select count(comm) from emp;

查询结果：

+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

总结：
count(*) ：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）

count(comm)：表示统计comm字段中不为NULL的数据总数。

2.4 分组函数的组合使用

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

查询结果：

+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal)    | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+
|       14 | 29025.00 | 2073.214286 |  5000.00 |   800.00 |
+----------+----------+-------------+----------+----------+
1 row in set (0.00 sec)

3. 单行处理函数

输入一行，输出一行

4. 单行处理函数案例

4.1 计算每个员工的年薪

（这里计算年薪时，我们算上每个月的补助）

每个员工的年资 = （月薪 + 补助）* 12 ；

因此：

select ename, (sal  + comm)*12 from emp;

查询结果：

+--------+-----------------+
| ename  | (sal + comm)*12 |
+--------+-----------------+
| SMITH  |            NULL |
| ALLEN  |        22800.00 |
| WARD   |        21000.00 |
| JONES  |            NULL |
| MARTIN |        31800.00 |
| BLAKE  |            NULL |
| CLARK  |            NULL |
| SCOTT  |            NULL |
| KING   |            NULL |
| TURNER |        18000.00 |
| ADAMS  |            NULL |
| JAMES  |            NULL |
| FORD   |            NULL |
| MILLER |            NULL |
+--------+-----------------+
14 rows in set (0.00 sec)

但是，我们发现上面的查询结果中出现了NULL，这是因为有些员工的补助（comm）为NULL，在SQL语句的表达式中 (800+NULL)*12 只要有NULL参与运算，计算结果一定为NULL。所有的数据库都是这样规定！！

这个时候就要用到 ifnull() 空处理函数：

ifnull(可能为NULL的数据，被当作什么处理)

因此，上面正确的查询每个员工的年薪sql语句为：

 select ename ,(sal + ifnull(comm,0)) * 12 as sal_year from emp;

ifnull(comm,0)) 将补助为NULL的值当作0；

查询结果：

+--------+----------+
| ename  | sal_year |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

4.1 计算所有员工的补助

在查询之前，我们先看一下comm中的值

select ename,comm from emp;

查询结果：

+--------+---------+
| ename  | comm    |
+--------+---------+
| SMITH  |    NULL |
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| JONES  |    NULL |
| MARTIN | 1400.00 |
| BLAKE  |    NULL |
| CLARK  |    NULL |
| SCOTT  |    NULL |
| KING   |    NULL |
| TURNER |    0.00 |
| ADAMS  |    NULL |
| JAMES  |    NULL |
| FORD   |    NULL |
| MILLER |    NULL |
+--------+---------+
14 rows in set (0.00 sec)

很简单，使用sum函数就可以完成！

select sum(comm) from emp;

查询结果：

+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+
1 row in set (0.00 sec)

但是，在上面讲单行处理函数时，我们说到

在SQL语句的表达式中，只要有NULL参与运算，计算结果一定为NULL。所有的数据库都是这样规定！！

为什么这里的查询结果不是NULL？其实，在1.2分组函数的特点时，我们就说到分组函数都会自动过滤NULL！ 因此，这里的查询结果不为NULL。