MYSQL基础之函数:聚合函数，以及GROUP BY HAVINN_mysql group by 合计

其实前面聊的很多函数，其实单行函数。

现在我们开始聊一些聚合函数，所以本章就是聊一些常见的聚合函数。

常用的聚合函数

上面的方法只是常用的，而不是所有的，比如还有什么中位数等方法。

还是用员工表进行演示，员工表如下：

SELECT MAX(sal),MIN(sal),AVG(sal),COUNT(sal),SUM(sal) FROM   test.emp 
1

这个看着和数学的祭祀啊u你一样，但是这个有一个问题，也是数据库中最怕的数据NULL.

正常来说AVG=SUM/COUNT.

所以我们搞以下抽成这个字段：comm

SELECT MAX(comm),MIN(comm),COUNT(comm),SUM(comm),COUNT(1),AVG(comm),SUM(comm)/COUNT(comm),SUM(comm)/15  FROM   test.emp 
1

这个值可以看出，无论AVG,SUM,COUNT,三个函数都会不计算该字段的值为NULL。所以在要求中计算所有用户的平均，要看清楚具体的需求。

min和max在遇见NULL的时候其不为最大也不是最小，如果该字段都为NULL，那么其结果为NULL，如果只有一个值非NULL，其它都是NULL，那么最大值最小值都是这个非NULL的值。

上面还有一个COUNT(1) 其的值为一共多少条数据，为什么会这样呢，这个就像是在

SELECT sal,1 FROM emp
-- 1字段下的数据，sal有多少个，1就补充多少个，所以很多时候 求全部的平均值的时候，是sum（某值）/count(1),当然其它字段也行。
1
2

补充 COUNT的效率

其实求一共多少条，目前有三种写法：

SELECT COUNT(sal),COUNT(*),COUNT(1) FROM emp
-- 默认使用字段中值都不为空，如果字段中有值为NULL ，那么count（字段）有可能不对。
1
2

三者的速度其实速度和引擎有关，后面聊优化的时候，在具体聊引擎，现在记住结果即可。

如果使用的是MyISAM存储引擎，则三者的效率相同，都是O(1).

如果使用的是InnoDB存储引擎，则三者的效率:COUNT(*)=COUNT(1)（只能说约等于）>COUNT(字段)（如果字段中都非空，那几乎和前两者没有什么区别。）

所以无论在那个引擎下，建议用COUNT(*)算多少行。

其实上面的常用的聚合函数，其很重要的是搭配着GROUP BY而使用的。

GROUP BY

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。比如前面计算的最大最小或者平均值都是整体，如果按部门求平均工资呢？

SELECT  deptno,AVG(sal) FROM emp  GROUP BY deptno
1

可见将不同的部分，分别进行求职。

当然其后面可以有多个字段，比如部门，以及部门下的职位的平均工资。

SELECT  deptno,job,AVG(sal)  FROM emp   GROUP BY deptno,job
-- GROUP BY 后面的字段其实前后顺序不一样，不影响结果
1
2

这个地方又涉及到一个规范：

比如这样写：

SELECT  deptno,job,AVG(sal)  FROM emp   GROUP BY deptno
1

在MYSQL中不会报错，如果在ORACL中就会报错，不过这样写job字段又没什么意义，所以一般SELECT中出现的非使用组函数的字段必须声明在GROUP BY 中，反之GROUP BY 中的字段可以不出现在SELECT 中

前面聊ORDER BY的时候说其位置的放置，那么GROUP BY呢？

GROUP BY 声明在FROM 以及 WHERE 后面，ORDER BY 前面，LIMIT 前面

WITH ROLLUP

这个是在分组后计算的数据后面，计算一个全部的。可能这样说有点绕，还是演示以下比较靠谱

SELECT  deptno,AVG(sal)  FROM emp   GROUP BY deptno WITH ROLLUP
1

如果对这个数字不熟悉的话，看下：

SELECT   AVG(sal)  FROM emp
1

可以看出其下面又进行了一个求总的平均值，当然，还有其它聚合函数也是类似，所以不在演示。

不过这个时候就涉及到一个排序问题，所以在使用WIHT ROLLUP的时候，排序可能会让结果有点模糊，比如下面求各个部分的工资，然后排序。

SELECT  deptno,AVG(sal) AS avg_sal FROM emp   GROUP BY deptno  WITH ROLLUP  ORDER BY avg_sal 
-- 这样写就会报错，比较整体的平均值和各个部分平均值数据的性质不一样啊。
1
2

HAVING

这个是过滤条件语句的。

如果这样说是否感觉和WHERE有重复呢？

既然两个不同的筛选关键字，那其自然有区别。

SELECT sal FROM  emp HAVING sal>2000
1

如果这样写的话，感觉就是WHERE 的用法，说实话在MYSQL函数中都有两个不同的函数得到的结果一样。

现在又有一个区别，比如各个部门平均工资大于2500块的有哪些部门？

SELECT  deptno,AVG(sal)  FROM emp WHERE  AVG(sal) >'2500'  GROUP BY deptno 
1

可以看出报错了，那么使用HAVING呢

SELECT  deptno,AVG(sal)  FROM emp HAVING  AVG(sal) >'2500'  GROUP BY deptno 
1

同样是报错啊,不过可以调一下位置。

SELECT  deptno,AVG(sal)  FROM emp  GROUP BY deptno  HAVING  AVG(sal) >'2500' 
1

这样就可以了，WHERE 是不肯能放在GROUP BY 后面的。

现在可以看出：**如果条件语句中使用了聚合函数，则必须使用HAVING替换WHERE，否则报错。 **

如果没有GROUP BY 的时候，HAVING 和WHERE 似乎一样，如果二者只选一个用的话，还是有要求的：一般HAVING 搭配着GROUP BY 搭配着使用，如果没有GROUP BY的时候使用WHERE.

比如下面：

而且WHERE 和HAVING可以一起用。

如果不一起用的话：

-- 第一种
SELECT  deptno,AVG(sal)  FROM emp    GROUP BY deptno  HAVING AVG(sal) >'2500'  AND deptno=10 
1
2

前面说了可以一起用，如下：

-- 第二种
SELECT  deptno,AVG(sal)  FROM emp  WHERE deptno=10 GROUP BY deptno  HAVING AVG(sal) >'2500' 
1
2

这个时候就有问题，为什么用一个就行，还需要用两个，或者说两者执行是否效率有区别？

先说结果：第一种执行的效率低于第二种。

整体总结：

• 当前的过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中
• 当前的过滤条件中没有聚合函数时，则过滤条件声明在WHERE和HAVING中都行，但是建议非聚合函条件放在WHERE 语句后面（其实在开发中之不是建议而是必须）

SQL执行顺序

前面说HAVING执行效率低于WHERE是为什么呢？这个就需要聊一下SQL在执行中具体的执行的顺序是什么？

-- SQL92 语法 语句的完整结构
SELECT .,..,...,(聚合函数)
FROM ....,....
WHERE 多表的连接条件 AND 不包含集合函数的过滤条件
GROUP BY ...,...
HAVING  包含聚合函数的过滤条件
ORDER BY  ...,..(ASC/DESC)
LIMIT ...,..

-- SQL99 语法 语句的完整结构
SELECT .,..,...,(聚合函数)
FROM .... (LEFT/RIGHT) JOIN ... ON 多表连接条件
WHERE   不包含集合函数的过滤条件
GROUP BY ...,...
HAVING  包含聚合函数的过滤条件
ORDER BY  ...,..(ASC/DESC)
LIMIT ...,..
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

上面演示了SQL语句的组成部分。这个就有一个疑问了，比较在很多语言中都是从上而下执行的那么SQL执行的顺序也是如此吗？

其实不是具体的执行是：

所以具体分四大部分，在各自的部分中依次从下而下执行。

• 第一部分：

  • from 表 ：数据的源头必然第一，数据不可能凭空而生。
  • ON 告诉数据库，有表要连接起来。
  • （LEFT/RIGHT）讲明表的具体关联关系是左还是右。
  • WHERE 筛选条件，毕竟如果有运算的话，将不符合条件的语句筛选出来。
  • GROUP BY 这样就可以理解为什么搭配着GROUP BY 出现的聚合函数不能放在WHERE 中了，因为其执行顺序在WHERE 后面

• 第二部分

  • HAVING : 对有聚合函数的数据再一次进行筛选

• 第三部分

  • SELECT 将需要显示的字段选出来，如果使用了DISTINCT,其再SELECT 后面显示。如果有别名的话，也是这个地方生效,所以不能在WHERE 中使用，不过HAVING例外，其可以使用别名（在MYSQL中，如果在SQLserver中也会报错。）。

• 第四部分

  • ORDER BY： 根据条件将其进行排序
  • LIMIT 显示多少数据

补充： 个人觉得其实上面所说的HAVING 在SELECT前面有点不太对，更好的理解是 SELECT -> HAVING -> SELECT 。HAVING 是对SELECT集合再次进行了一次筛选,然后再SELECT。