MySQL_第07章_聚合函数

第07章_聚合函数

1. 聚合函数介绍

• 什么是聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

• 聚合函数类型
  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

2. GROUP BY

2.1 基本使用

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

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

明确：WHERE一定放在FROM后面。在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中，包含在GROUP BY子句中的列不必包含在SELECT列表中。

SELECT depart_id, AVG(salary)
FROM emp
GROUP BY depart_id;

SELECT AVG(salary)
FROM emp
GROUP BY depart_id;

2.2 使用多个列分组

SELECT depart_id, job_id, SUM(salary)
FROM emp
GROUP BY depart_id, job_id;

2.3 GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT depart_id, AVG(salary)
FROM emp
WHERE depart_id > 80
GROUP BY depart_id WITH ROLLUP;

注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

3. HAVING

3.1 基本使用

过滤分组：HAVING子句

SELECT depart_id, MAX(salary)
FROM emp
GROUP BY depart_id
HAVING MAX(salary) > 10000;

SELECT depart_id, MAX(salary)
FROM emp
GROUP BY depart_id
HAVING MAX(salary) > 10000 AND depart_id IN (10, 20, 30);

3.2 WHERE和HAVING的对比

区别1：WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。

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

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

开发中的选择：

WHERE和HAVING也不是互相排斥的，可以在同一个查询里面使用WHERE和HAVING。包含分组统计函数的条件用HAVING，普通条件用WHERE。这样既利用了WHERE条件的高效快捷，又发挥了HAVING可以使用包含分组统计函数的查询条件的优点。

4. SELECT的执行过程

4.1 查询的结构

--方式1，sql92语法:
SELECT ..., ...
FROM ..., ...
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ..., ... 
HAVING 包含组函数的过滤条件
ORDER BY ... ASC / DESC
LIMTI ..., ...

--方式2，sql99语法:
SELECT ..., ...
FROM ... (LEFT / RIGHT)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：分页
'''

4.2 SELECT执行顺序

SELECT 查询时的两个顺序：

1. 关键字的顺序是不能颠倒的：

   SELECT ... FROM ... WHERE ... GROUP BY ... LIMIT ...
   

2. SELECT语句的执行顺序（在MySQL和Oracle中，SELECT执行顺序基本相同）：

   FROM -> on -> (LEFT/RIGHT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT
   

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。这些步骤隐含在SQL的执行过程中，是不可见的。

4.3 SQL的执行原理

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

1. 首先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表vt（virtual table）1-1；
2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3. 添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

如果操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。

当得到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。

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

当完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。

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

最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。