MySQL--day7--聚合函数

（以下内容全部来自上述课程）

聚合函数

1. 介绍

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

• 聚合函数类型

• AVG（）
• SUM（）
• MAX（）
• MIN（）
• COUNT（）

2. 常见的聚合函数

2.1 AVG / SUM

#1.1 AVG / SUM:只适用于数值类型的字段(或变量)
SELECT AVG(salary),SUM(salary),AVG(salary) * 107 
FROM employees;

#如下的操作没有意义
SELECT SUM(last_name),AVG(last_name),SUM(hire_date) 
FROM employees;

2.2 MAX / MIN

#1.2 MAX / MIN :适用于数值类型、字符串类型、日期时问类型的字段(或变量)
SELECT MAX(salary),MIN(salary)
FROM employees;	

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

2.3 COUNT

#1.3 COUNT:
# （1）作用:计算指定字段在查询结构中出现的个数（不包含NULL）
#                 107             107            107             107        107        107
SELECT COUNT (employee_id),COUNT (salary) ,COUNT(2 * salary),COUNT (1) , COUNT (2), COUNT (*) 
FROM employees ;

SELECT	*	
FROM employees;

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

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

SELECT commission_pct 
FROM employees
WHERE commission_pct IS NOT NULL;

# (3) 公式: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;

其他：方差、标准差、中位数

3. GROUP BY 的使用

1. 结论1:SELECT中出现的非组函数的字段必须声明在GROUP BY 中。
   反之，GROUP BY中声明的字段可以不出现在SELECT中。
2. 结论2:GROUP BY声明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面
3. 结论3:MySQL中GROUPBY中使用WITH ROLLUP（总体）
   说明:当使用ROLLUP时，不能同时使用ORDERBY子句进行结果排序，即ROLLUP和ORDERBY是互相排斥的。

#2. 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 BYdepartment_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中GROUPBY中使用WITH ROLLUP（总体）
SELECT department_id,AVG(salary) 
FROM employees
GROUP BY department_id 
WITH ROLLUP;

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

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

4. HAVING的使用

1. 要求1:如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。

2. 要求2:HAVING必须声明在GROUP BY 的后面。

3. 要求3:开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

4. 结论:当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。

5. 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。

6. 但是，建议大家声明在WHERE

WHERE与AVING 的对比

1. 从适用范围上来讲，HAVING的适用范围更广。
2. 如果过滤条件中没有聚合函数:这种情况下， WHERE的执行效率要高于HAVING

#3.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;

# 要求3:开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

#练习:查询部门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与AVING 的对比
1.从适用范围上来讲，HAVING的适用范围更广。
2.如果过滤条件中没有聚合函数:这种情况下， WHERE的执行效率要高于HAVING
*/

5. SQL底层执行原理

5.1 SELECT 语句的完整结构

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

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

5.2 SQL 语句的执行顺序

#4.2 SQL语句的执行过程:
#FROM.......-> ON ->(LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -># ORDER BY -> LIMIT