MySql 常用的聚合函数

AVG 计算平均值

只适用于数值类型的字段（或变量）

SELECT AVG(salary),AVG(salary) * 107 FROM employees;

SUM 求和

只适用于数值类型的字段（或变量）

SELECT SUM(salary) FROM employees;

MAX 获取一组值中的最大值

适用于数值类型、字符串类型、日期时间类型的字段（或变量）

SELECT MAX(salary) FROM employees;

MIN 获取一组值中的最小值

适用于数值类型、字符串类型、日期时间类型的字段（或变量）

SELECT MIN(salary) FROM employees;

COUNT 计算表中有多少条记录

计算指定字段在查询结构中出现的个数（不包含NULL值的）

SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*) FROM employees ;

#如果计算表中有多少条记录，如何实现？
#方式1：COUNT(*)
#方式2：COUNT(1)
#方式3：COUNT(具体字段) : 不一定对！

#② 注意：计算指定字段出现的个数时，是不计算NULL值的。
SELECT COUNT(commission_pct) FROM employees;

#③ 公式：AVG = SUM / COUNT （都不计算NULL值）
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct) / 107
FROM employees;

#需求：查询公司中平均奖金率
#错误的！（举例AVG会过滤空值，公司有20个人没有参加抽奖，但是也要算）
SELECT AVG(commission_pct)FROM employees;

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

count(*) 与 count(具体字段) 效率
如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)

在 MySQL 中统计数据表的行数，可以使用三种方式：SELECT COUNT(*)、SELECT COUNT(1)和SELECT COUNT(具体字段)，使用这三者之间的查询效率是怎样的？

在 MvSOL InnoDB 存储引警中，COUNT(*)和 COUNT(1)都是对所有结果进行 COUNT。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计；如果没有 WHERE 子句，则是对数据表的数据行数进行统计。因此 COUNT(*)和 COUNT(1)本质上并没有区别，执行的复杂度都是O(N)，也就是采用全表扫描，进行循环+计数的方式进行统计。

如果是 MySQL MyISAM 存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值，而一致性则由表级锁来保证。因为 InnoDB 支持事务，采用行级锁和 MVCC机制，所以无法像 MyISAM 一样，只维护一个row_count 变量，因此需要采用扫描全表，进行循环+计数的方式来完成统计。

需要注意的是，在实际执行中，COUNT(*)和 COUNT(1)的执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的。

另外在 InnoDB 引擎中，如果采用 COUNT(*)和 COUNT(1)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引(非聚簇索引)。对于 COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

GROUP BY 根据一个或多个列对结果集进行分组

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

#需求：查询各个department_id,job_id的平均工资
#方式1：
SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY  department_id,job_id;
#方式2：
SELECT job_id,department_id,AVG(salary) FROM employees GROUP BY job_id,department_id;

#结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。
#      反之，GROUP BY中声明的字段可以不出现在SELECT中。
#结论2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面
#结论3：MySQL中GROUP BY中使用WITH ROLLUP 【计算全体平均工资】
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP;

HAVING 为行分组或聚合组指定过滤条件

#要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
#要求2：HAVING 必须声明在 GROUP BY 的后面。
#要求3：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;


#练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#方式1：推荐，执行效率高于方式2.
SELECT department_id,MAX(salary) FROM employees WHERE department_id IN (10,20,30,40) 
GROUP BY department_id HAVING MAX(salary) > 10000;

#方式2：
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

#结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
#     当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。
# WHERE 与 HAVING 的对比
1. 从适用范围上来讲，HAVING的适用范围更广。 
2. 如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING

ORDER BY 排序

#需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal ASC;

#说明：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
#错误的：
SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id WITH ROLLUP ORDER BY avg_sal ASC;

SQL底层执行原理

SELECT 语句的完整结构

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

#sql99语法：
SELECT ....,....,....(存在聚合函数)
FROM ... 
(LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

SELECT SQL语句的执行过程

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