MYSQL基础之函数:聚合函数，以及GROUP BY HAVINN

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

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

常用的聚合函数

函数	作用
AVG()	求平均值
SUM()	求和
COUNT()	算出有多少个
MIN()	求最小值
MAX()	求最大值

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

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

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

这个看着和数学的祭祀啊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 

这个值可以看出，无论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),当然其它字段也行。

补充 COUNT的效率

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

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

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

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

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

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

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

GROUP BY

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

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

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

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

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

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

比如这样写：

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

在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

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

SELECT   AVG(sal)  FROM emp

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

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

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

HAVING

这个是过滤条件语句的。

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

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

SELECT sal FROM  emp HAVING sal>2000

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

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

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

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

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

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

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

这样就可以了，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 

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

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

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

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

整体总结：

• 当前的过滤条件中有聚合函数时，则此过滤条件必须声明在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 ...,..

上面演示了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。