MySQL学习笔记-聚合函数、SQL底层执行原理

最新推荐文章于 2023-03-25 22:51:00 发布

炸毛疯兔

最新推荐文章于 2023-03-25 22:51:00 发布

阅读量1k

点赞数

分类专栏： MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_52245648/article/details/122858390

版权

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

MySQL所有笔记链接

本文中的题目课件等均来自于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