MySQL基础篇聚合函数总结

1. 聚合函数概述

1.1 聚合函数引入

①聚合函数：聚合函数作用于一组数据，并对一组数据返回一个值。
②聚合函数类型 AVG() 、SUM() 、MAX() 、MIN() 、COUNT()
③基本使用规则：

SELECT column, group_function(column) 
FROM table 
[WHERE condition] 
[GROUP BY group_by_expression]
[ORDER BY column];

1.2 常用函数说明

①AVG和SUM函数
在对于AVG使用的时候，有时候需要借助下面介绍的函数COUNT来使用
原因：
      有的时候，计算的字段不一定会有数值，所以在计算平均值的时候不会计算NULL值，然而在正常的情况下这是需要计算的，所以会采用AVG = SUM / COUNT的方法来计算；

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) 
FROM employees；

运行结果：

② MIN和MAX函数
介绍：
      可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date) 
FROM employees;

③ COUNT函数
介绍：
COUNT(*)返回表中记录总数，适用于任意数据类型。

查询部门号是50的人数：

![SELECT COUNT(*)
FROM employees
WHERE department_id = 50;]

对于COUNT的使用没有严格要求的使用COUNT（*） 或者是COUNT(1) 都是可以的

1.3 基本使用

可以使用GROUP BY子句将表中的数据分成若干组

①查询单列进行分组

SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id ;

运行结果：

使用说明：
      在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

备注：
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

②查询多列进行分组

例如：查询每个部门，每个工种的总工资

SELECT department_id dept_id, job_id, SUM(salary) 
FROM employees 
GROUP BY department_id, job_id ;

运行结果：

2. 聚合函数高级应用

2.1 集合函数结合HAVING的使用

HAVING子句作用：

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

总结：HAVING需要结合GROUP BY 一起使用，他的作用也是用来进行过滤条件的。在使用的时候HAVING 后面添加的是包含聚合函数的过滤条件

使用样例（1）：
题目描述：查询各个部门中最高工资比10000高的部门信息

SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id 
HAVING MAX(salary)>10000 ;

运行结果：

使用样例（2）：

题目描述：查询部门id为10，20，30，40这四个部门中最高工资比10000高的部门

SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

运行结果：

2.2 WHERE和HAVING的对比

①WHERE 可以直接使用进行过滤表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
理解性知识点
    这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

②如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

理解性知识点

    这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

总结:

WHERE	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
HAVING	可以使用分组中的计算函数	在最后的结果集中进行筛选，执行效率较低

    在实际的开发中对于WHERE和HAVING的使用是不冲突的，对于有聚合函数的筛选条件就需要使用HAVING （结合GROUP BY一起使用），对于没有聚合条件的函数就使用WHERE进行条件过滤。

2.3 SELECT的执行过程

SQL92版

SELECT ...,....,... 
FROM ...,...,.... 
WHERE 多表的连接条件 
AND 不包含组函数的过滤条件 
GROUP BY ...,... 
HAVING 包含组函数的过滤条件 
ORDER BY ... ASC/DESC 
LIMIT ...,...

SQL99版

SELECT ...,....,... 
FROM ... JOIN ... 
ON 多表的连接条件 
JOIN ... ON ... 
WHERE 不包含组函数的过滤条件 
AND/OR 不包含组函数的过滤条件 
GROUP BY ...,... 
HAVING 包含组函数的过滤条件 
ORDER BY ... ASC/DESC
 LIMIT ...,

提示：

（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页

在书写SELECT语句时：
关键字的顺序不能颠倒

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

2.4 SQL底层实现原理

【了解内容】

（1）SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

①首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
② 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
③添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

（2）然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。

（3）当我们 完成了条件筛选 部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。
①首先在 SELECT 阶段会提取想要的字段；
②然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的 虚拟表vt5-1 和 vt5-2 。

（4）当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6 。

（5）最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表vt7 。
以上就是SELECT语句查询的底层原理实现。