聚合函数

聚合函数：

#聚合函数
​
#1. 常见的几个聚合函数
#AVG / SUM：只适用于数值类型的字段（或变量）
SELECT AVG(salary),SUM(salary),AVG(salary)*107#AVG(salary)*107求总工资
FROM employees;
​
​
#MAX / MIN :适用于数值类型，字符串类型，日期类型的字段（或变量）
SELECT MAX(salary),MIN(salary)
FROM employees;
​
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;
​
​
#COUNT
#作用：计算指定字段在查询结构中出现的个数
SELECT COUNT(employee_id),COUNT(salary),COUNT(2*salary),COUNT(1)  #查询结果都一样
FROM employees;
#如果计算表中用多条记录，如何实现
#方式1：COUNT(*)
#方式2：COUNT(1)
#方式3：COUNT(具体字段)   ：不一定对
#注意：计算指定字段出现的个数时，是不计算null值的。
​
# AVG = SUM/COUNT
#需求：查询公司中平均奖金率
#错误的：因为这样会过滤掉空值null
SELECT  AVG(commission_pct)
FROM employees;
#正确的：
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0))
#AVG(IFNULL(commission_pct,0))
FROM employees;
​
​
#GROUP BY 的使用
#查询各个部门平均工资，最高工资
SELECT department_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：myslq中GROUP BY 使用WITH ROLLUP
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
​
​
​
# HAVING的使用(作用：用来过滤数据的)
#查询各个部门最高工资比10000高的部门信息
#要求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
*/
​
​
#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
​
​

#1.where子句可否使用组函数进行过滤? 
#不可以
​
#2.查询公司员工工资的最大值，最小值，平均值，总和 
SELECT MAX(salary),MIN(salary),SUM(salary),AVG(salary)
FROM employees;
​
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和 
SELECT job_id, MAX(salary),MIN(salary),SUM(salary),AVG(salary)
FROM employees
GROUP BY job_id;
​
#4.选择具有各个job_id的员工人数 
SELECT job_id,COUNT( *)
FROM employees
GROUP BY job_id;
​
#5.查询员工最高工资和最低工资的差距（DIFFERENCE） 
SELECT MAX(salary)-MIN(salary) "DIFFERENCE"
FROM employees;
​
#6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;
​
#7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 
SELECT department_name,location_id,COUNT(employee_id),AVG(salary) "avgs"
FROM departments d LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name,location_id
ORDER BY avgs DESC;
​
#8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name ,job_id
​