本文中的题目 课件等均来自于B站尚硅谷,想要更加详细解释请移步b站
本文中的最终的练习的表也是来自于尚硅谷
聚合函数
聚合函数作用于 一组数据,并对一组数据返回一个值
1.常用的聚合函数
AVG/SUM(用于数值类型)
就是求平均值和总和的函数
🌰:查询所有员工的平均共资和老板应发的总工资(107个人)
SELECT AVG(salary), SUM(salary) ,AVG(salary) * 107 "总工资"
FROM employees;
MAX/MIN
能够用于字符串、时间类型、数值类型 (能排序就能有最值)
🌰:查询工资最高和工资最低的薪资
SELECT AVG(salary), SUM(salary) ,AVG(salary) * 107 "总工资"
FROM employees;
COUNT
作用:计算指定的字段在查询结构中出现的个数(不包含NULL值的)
🌰:有多少人有id和薪水(不是特指薪水是啥),就是数拥有查询的字段
的个数有多少个
SELECT AVG(salary), SUM(salary) ,AVG(salary) * 107 "总工资"
FROM employees;
🌰:计算表中有多少条记录
COUNT(*)
COUNT(1)
COUNT(具体字段):不一定靠谱
AVG = SUM / COUNT :都是会自动过滤null值的
🌰: 查询公司中的平均奖金率(应将所有员工都算进去而不是有奖金的部分)
#下面两个查询是相同的
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct, 0)),
AVG(IFNULL(commission_pct,0))
FROM employees
统计表中的记录数,使用COUNT(*),COUNT(1),COUNT(具体字段)效率?
2.GROUP BY 的使用
🌰:查询各个部门的平均工资,最高工资
很多部门,所以要进行分组
SELECT department_id, AVG(salary),MAX(salary)
FROM employees
GROUP BY department_id
使用多个列进行分组
比如说已经是按照部门进行分组了,组内我还想分组就可以多个列分组
🌰:查询各个部门id,job_id的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id
🌰:查询各个部门的平均工资, 按照平均工资进行升序排列
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
3.HAVING 的使用
用于过滤数据,要求格式:
- 如果过滤条件中使用了聚合函数,则必须使用Having替换WHERE
- Having必须声明在GROUP BY的后面
- 开发中:使用HAVING的前提是使用了GROUP BY(建议)
🌰:查询各个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary)
FROM employees
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对比
4.SQL的底层执行原理
执行顺序以及SQL99的语法
#第二执行部分
SELECT .......(存在聚合函数)
#第一执行部分(从上向下顺序执行)
FROM .....(LEFT / RIGHT) JOIN ..ON 多表的连接条件
(LEFT / RIGHT) JOIN .. ON ..
WHERE 不包含聚合函数的过滤条件
GROUP BY .......
HAVING 包含聚合函数的过滤条件
#第三执行部分
ORDER BY ....(ASC, DESC)
LIMIT .....,....
选表、连接过滤、条件过滤(WHERE)、分组、分组过滤(HAVING)、选择、去重、排序、取区间
为什么WHERE效率高呢?
因为执行WHERE进行过滤后才进行分组,然后是分组过滤,如果写在Having中就会有很多 无用的数据进行分组,总的来说就是WHERE先执行,先过滤肯定是快啊
聚合函数练习题
- WHERE子句是否可以用组合函数过滤?不可以🙅♂️
- 查询公司员工工资的最大值、最小值、平均值、总和
SELECT MAX(salary), MIN(salary),AVG(salary),SUM(salary)
FROM employees
- 查询各job_id的员工工资的最大值、最小值、平均值、总和
SELECT job_id, MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
- 选择具有各个job_id的员工人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id
- 查询员工最高工资和最低工资的差距
SELECT MAX(salary) - MIN(salary) "difference"
FROM employees
- 查询各个管理者手下员工的最低工资,其中最低工资不能够低于6000,没有管理者的员工不计算在内
#首先根据管理者进行分组,其中manager_id为null的用WHERE过滤
#将薪资是否大于6000的过滤条件用HAVING 进行过滤
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000
- 查询 所有部门的名字,location_id,员工数量和平均工资,按照平均工资进行降序
SELECT d.department_name, d.location_id, COUNT(employee_id), AVG(salary)
FROM departments d LEFT JOIN employees e #部门表中的比较多,ALTER
ON d.department_id = e.department_id
GROUP BY department_name,location_id
ORDER BY AVG(salary) DESC
- 查询每个工种、每个部门的部门名、工种名和最低工资
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