08 聚合函数

1 聚合函数

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

● 不能嵌套调用。

1.1 AVG和SUM函数

● 使用对象：数值型数据

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

1.2 MIN和MAX函数

● 使用对象：任意数据类型

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

1.3 COUNT函数

● COUNT(*)返回表中记录总数，适用于任意数据类型。

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

● COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

 

2 GROUP BY

2.1 基本使用

● GROUP BY子句将表中的数据分成若干组。

【案例】

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

2.2 使用多个列分组

【案例】

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

 

2.3 GROUP BY中使用WITH ROLLUP

● WITH ROLLUP关键字，用于统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：

ROLLUP和ORDER BY相互排斥，不可同时使用。

 

3 HAVING

3.1 基本使用

过滤分组：HAVING子句

● 行已经被分组

● 使用了聚合函数

● 满足HAVING子句条件的分组将被显示

● HAVING必须与GROUP BY一起使用

注意：

非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。

【案例】

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

3.2 WHERE和HAVING的对比

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

 

4 SELECT的执行过程

4.1 查询的结构

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

#方式2：
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：分页

顺序记忆：

on—where—group—having—order        （on—w—g—h—or）

4.2 SELECT执行顺序

SELECT查询的两个顺序：

● 关键字的顺序

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

● 执行顺序

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

4.3 SQL的执行原理