MySQL8

常见的聚合函数

• 平均数AVG()/SUM()求和 :适用于数值类型（或变量）过滤NULL值

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

#如下操作没有意义
SELECT   SUM(last_name)
FROM employees;    //结果:0

• 最大值MAX()/MIN()最小值 过滤NULL值

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

#记住前面讲到过ORDER BY排序，说明字符串有最大值、最小值
SELECT   MAX(last_name),MIN(last_name)
FROM employees;

• COUNT()
• 作用：计算指定字段在查询结果中出现的个数

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

SELECT *,COUNT(*)
FROM employees;

#如果计算表中有多少条记录
#注意：计算知道指定字段在查询结果的个数不包含NULL值的
方式1：COUNT(*)
方式2：COUNT(1)
方式3：COUNT(具体字段)：不一定对

#AVG=SUM/COUNT
SELECT  AVG(salary),SUM(salary)/COUNT(salary)
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct)/107
#前面两个相同
FROM employees;

#需求：查询公司平均的奖金率
错误：
SELECT  AVG(commission_pct)
FROM employees;
正确：
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0))
 AVG(IFNULL(commission_pct,0))
FROM employees;

#需要统计表中的记录数,使用COUNT(*),COUNT(1),COUNT(具体字段)谁效率高？（跟存储引擎有关系）

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

GROUP BY的使用

• 分组操作，将表中的数据分成若干组

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

查询各个job_id的平均工资
SELECT  job_id, AVG(salary)
FROM employees
GROUP BY job_id;

• 使用多个列分组

查询各个department_id,job_id的平均工资
SELECT  department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;#先按部门分
#结果一样，顺序反一下
SELECT  job_id,department_id,AVG(salary)
FROM employees
GROUP BY ,job_id,department_id;#先按工种分

错误形式：
SELECT  department_id,job_id,AVG(salary)
FROM  employees
GROUP BY  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

• 作用：过滤数据

#查询各个部门最高工资比10000高的部门
#错误的写法：
SELECT  department_id,MAX(salary)
FROM  employees
WHERE  MAX(salary)>10000
GROUP BY department_id;

要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE，否则报错
要求2：HAVING必须在GROUP BY后面
#正确的写法：
SELECT  department_id,MAX(salary)
FROM  employees
GROUP BY department_id
HAVING  MAX(salary)>10000;

#开发中使用HAVING的前提是SQL使用了GROUP BY

#查询各个部门10,20,30,40这4个部门最高工资比10000高的部门信息
方式1：（WHERE里面不写聚合函数）
SELECT  department_id,MAX(salary)
FROM employees
WHERE  department_id IN(10,20,30,40)
GROUP BY  department_id
HAVING MAX(salary)>10000;
方式1的执行效率高于方式2
方式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中
当过滤条件中没有聚合函数，则此过滤条件声明在HAVING或WHERE都可以，建议声明在WHERE中

WHERE与HAVING的对比

• 从适用范围来讲，HAVING适用范围更广
• 当过滤条件中没有聚合函数，则此过滤条件声明在WHERE的效率高于HAVING

SQL的执行原理

1.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.....

SQL的执行过程
FROM..,..->ON-> (LEFT/RIGHT JOIN)->WHERE->GROUP BY->HAVING->SELECT->DISTINCT->ORDER BY->LIMIT