mysql聚合函数

#8.0 聚合函数

#8.1 常见的聚合函数
#1.1 avg / sum 只使用于数值类型的字段（或变量）
SELECT AVG(salary),SUM(salary)
FROM employees;
#1.2 max /min 适用于字符串 数值类型 日期类型
SELECT MAX(salary),MIN(salary)
FROM employees;
SELECT MAX(last_name),MIN(last_name)
FROM employees;
#1.3 count 求个数
SELECT COUNT(employee_id),COUNT(salary),COUNT(2*salary),COUNT(1)
FROM employees;
SELECT *
FROM employees;
#如果计算表中有多少条记录，如何实现？
#方式一： count(*)
#方式二：count(1)
#方式三：count(具体字段)

#0.2 注意计算字段出现的个数是，是不计算null值
#0.3
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct)/107,
FROM employees;
SELECT COUNT(commission_pct)
FROM employees
WHERE commission_pct IS NOT NULL;

#如何需要统计表中的记录数，适用count(*) ,count(1),count()
#如果使用的myism引擎 则三者的效率是一致的
#如果使用的是innodb引擎的话，则三者效率 count(*) = count(1) > count(字段)
#方差、标准差、中位数

#group by 的使用
#前提提示： order by 排序
#需求：查询每个部门的平均工资，最高的工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
# need: 查询每个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 department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;
#结论：select中出现的非组成函数的字段必须声明在gruop by 中，反之，gruop by 声明的字段可以不出现 在select 中
# 结论：group by 声明在from 后面 ，orderby 前面 limit 前面
#结论： 使用 wint rollup
# 正常的结构 按照平均工资从小到大排序
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
SELECT VERSION();
#having的使用
#练习：查询各个部门的最高工资 比 10000高的部门信息
#1.1 错误的写法
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary ) > 10000
GROUP BY department_id;
#正确的写法：如果过滤条件中出现了聚合函数，则限制条件必须使用having 来替换 where，否则;报错
#要求二：having 必须声明在group by 之后
SELECT department_id,MAX(salary)
FROM employees
#WHERE MAX(salary ) > 10000 error
GROUP BY department_id
HAVING MAX(salary) > 10000;
#思考： 没有group by 是否还可以使用having ？
#要求3： 无报错！ 但是在工作中，我们使用having的前提就是sql语句中使用了聚合函数 group by
SELECT department_id,MAX(salary)
FROM employees
#WHERE MAX(salary ) > 10000 error
#GROUP BY department_id
HAVING MAX(salary) > 10000;
#练习： 查询部门id为 10 20 30 40 这四个部门的最高工资比10000高的部门信息
#练习答案：方式一 执行效率高于 方式二
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
#方拾贰
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的效率高
/**
where vs having
1.从适用性上看 having的适用性比较高，where不能使用在含有聚合函数中使用
2. where 的效率 > having 的效率

*/
#4.sql底层的执行原理
#4.1 SQL 底层的执行原理
/**
#SQL92语法：
select ------（存在聚合函数）
from -----
where 多表的连接条件 .... AND 过滤条件(不包含聚合函数的过滤条件)
GROUP BY 包含聚合函数的过滤条件
ORDER BY ----- (asc / desc)
limit -----

#sql99 语法：
select ------（存在聚合函数）
from --（left/ right）join --- on 多表的连接条件
where 过滤条件(不包含聚合函数的过滤条件)
GROUP BY 包含聚合函数的过滤条件
ORDER BY ----- (asc / desc)
limit -----
*/
#4.2
#FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

 练习;

#聚合函数的练习;
#1.where子句可否使用组函数进行过滤?
/*
不可以，含有组函数的sql语句必须使用having语句进行过滤

*/
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary)
FROM employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT 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;
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary),MIN(salary), MAX(salary)-MIN(salary) AS 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;
SELECT manager_id, MIN(salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) > 6000;
DESC employees;
# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT d.department_name,d.location_id,COUNT(e.`employee_id`),AVG(e.`salary`) AS "avg_sal"
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_name`,d.`location_id`
ORDER BY avg_sal DESC ;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT e.employee_id,d.department_name,e.job_id,MIN(salary) AS "max_sal"
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY e.`employee_id`,d.`department_id`;