#聚合函数的使用
#常见的聚合函数
# AVG/SUM
SELECT AVG(salary)
FROM employees;
SELECT SUM(salary)
FROM employees;
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
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 求总数
#作用1：计算指定字段在查询结构中出现的个数（不包含null值的）
SELECT COUNT(employee_id),COUNT(salary),COUNT(salary*107),COUNT(1),COUNT(2),COUNT(*)#107个1 107 个2 107个*
FROM employees;#107
SELECT COUNT(1)
FROM DUAL;
SELECT *
FROM employees;
#如果计算表中有多少条记录，如何实现？
#方式一：COUNT(*)
#方式二：COUNT(1)
#方式三：COUNT(具体字段)：不一定对！
#COUNT 计算指定字段个数时，是不计算NULL值的
SELECT COUNT(commission_pct)
FROM employees;#结果35
SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;#35行数据和上面的条数一致
#公式 ：AVG = SUM / COUNT
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),#35/35
SUM(commission_pct)/107 #35/107
FROM employees;
#需求：查询公司中平均的奖金率
#错误的!
SELECT AVG(commission_pct)#没有奖金的人数没有加进去
FROM employees;
#正确的是 所有奖金的总和 / 有奖金和没有奖金的人的人数总和
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0))，
AVG(IFNULL(commission_pct,0))#同上一样 不是NULL的值，是NULL就用0来代替
FROM employees;
# 如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
# 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
# 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)
#方差、标准差、中位数
#GROUP BY 的使用
#需求查询各个部门的平均工资，最高工资
SELECT department_id,AVG(salary),SUM(salary)#按照员工id分组 相同id的平均工资和相同id的工资总和
FROM employees
GROUP BY department_id;
#需求 查询各个job_id的平均工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;#按照相同的job_id 的平均工资 进行分组
#需求：查询各个department_id,job_id的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id; #员工id和工作id相同的平均工资
#或 一样的
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
#容易错误的地方
#错误的 一个部门的job_id 应该有多个 这里只显示一个job_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 e
GROUP BY e.`department_id` WITH ROLLUP;#把计算的数值的平均值输出
#需求 查询各个部门的平均工资
SELECT department_id,AVG(salary) avg_sal
FROM employees e
GROUP BY department_id
ORDER BY avg_sal ASC;
SELECT department_id,AVG(salary) avg_sal
FROM employees e
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;#错误的 会把计算的总和加入进去排序
#HAVING的使用(作用：用来过滤数据)
#练习：查询各个部门中最高工资比10000高的部门信息
#错误的写法
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary)>10000
GROUP BY department_id;
#要求：如果过滤条件中出现了聚合函数，则必须使用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。
#练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#4.2 SQL语句的执行过程：
#FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->
# ORDER BY -> LIMIT
#方式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底层的执行原理
#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 ...,....
*/
#4.2 SQL语句的执行过程：
#FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->
# ORDER BY -> LIMIT
#聚合函数之课后习题
#1.where子句可否使用组函数进行过滤?
#NO
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;
#4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)#每条记录的个数
FROM employees
GROUP BY job_id#分组每个不同的员工id
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT employee_id,MAX(salary)-MIN(salary)AS DIFFERENCE
FROM employees;
# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(emp.salary)
FROM employees emp
WHERE emp.manager_id IS NOT NULL
GROUP BY emp.`manager_id`#按照管理者id 分组 应为有管理者的员工都属于管理者手下的员工
HAVING MIN(emp.salary)>=6000;
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT department_name,location_id,COUNT(employee_id),AVG(salary)AS asalary
FROM employees e RIGHT JOIN departments d#count不能填入*
ON e.`department_id`=d.`department_id`
GROUP BY department_name,location_id
ORDER BY asalary DESC;
SELECT AVG(salary),SUM(salary)/107
FROM employees;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT e.job_id,department_name,MIN(salary)
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.department_name,e.`job_id`;
|
