聚合函数

COUNT()：适用于任意的数据类型。（会跳过null）

SELECT COUNT(employee_id),COUNT(salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees;
计算表中有多少条记录
1：COUNT(*)
2：COUNT(1)
3：COUNT(具体字段):不一定正确，当字段值为 null 时

统计表中的数据，使用 COUNT(*)、 COUNT(1)、 COUNT(具体字段)哪个效率高？
使用的是 MyISAM 存储引擎，则三者效率相同、都是O(1)
InnoDB 存储引擎，则 COUNT(*) = COUNT(1) > COUNT(字段)

举例：查询公司中平均奖金率

错误的
SELECT AVG(commission_pct)
FROM employees;
正确的：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0))
FROM employees;
或
SELECT AVG(IFNULL(commission_pct,0))
FROM employees;

2. GROUP BY 的使用

GROUP BY：用于将数据按照某个（或多个）字段进行分组

举例：

需求：查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),MAX(salary)
FROM employees
GROUP BY department_id

2.1 GROUP BY 中使用 WITH ROLLUP

使用 WITH ROLLUP后，在查询所有的分组中会添加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量

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

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

3. HAVING 的使用

HAVING：用于过滤数据

举例：查询各个部门中最高工资比 10000 高的部门信息

错误的：
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;
若过滤条件中使用了聚合函数，则应使用 HAVING 替换 WHERE
正确的：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id;
HAVING MAX(salary) > 10000;

HAVING 应与 GROUP BY 一起使用

举例：查询部门 id 为 10，20，30 这3个部门中最高工资比 10000 高的部门id
推荐使用方式一执行效率高于方式二

方式一:
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30)
GROUP BY department_id
HAVING MAX(salary) > 1000;

方式二：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 1000 AND department_id IN (10,20,30);

结论：
当过滤条件中有聚合函数时，此过滤条件必须声明在 HAVING 中
当过滤条件没有聚合函数时，应声明在 WHERE 中

3.1 WHERE 与 HAVING 的对比

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

区别2：若要通过连接查询的表获取数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

在关联查询中，区别2决定了 WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用筛选后较少的数据和关联表进行连接。HAVING 需要先将未筛选的数据集进行关联，再进行筛选。

1. HAVING 的适用范围更广
2. 若过滤条件没有聚合函数时，则 WHERE 的执行效率高于 HAVING

4. SQL 底层执行原理

4.1 SELECT 语句的完整结构

sql92 语法：
SELECT ....
FROM ...
WHERE ... 多表连接条件 and 不包含聚合函数的过滤条件
GROUP BY ...
HAVING ... (包含聚合函数的过滤条件)
ORDER BY ...(ASC/DESC)
LIMIT ....

sql99语法：
SELECT ....
FROM ...
JOIN ...   ON ...
WHERE ...(不包含聚合函数的过滤条件)
GROUP BY ...
HAVING ... (包含聚合函数的过滤条件)
ORDER BY ...(ASC/DESC)
LIMIT ....

4.2 SELECT 执行顺序

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

WHERE 中无法使用 SELECT 中设置的字段别名 原因在于：WHERE 的执行顺序先于 SELECT