数据库MySQL 聚合函数、子查询知识点总结P39-P48

已于 2022-12-16 20:07:31 修改
阅读量148 收藏
点赞数
分类专栏： mysql 文章标签： 数据库 mysql
于 2022-12-15 23:30:49 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43367551/article/details/128318501
版权

目录

聚合函数

常用聚合函数

GROUP-BY使用

HAVING使用

 WHERE与HAVING的对比

SQL底层执行原理 

 SELECT执行顺序

 子查询

子查询分类

单行子查询

​编辑 多行子查询​编辑

 空值问题

 相关子查询

 EXIST和NOT EXIST

 子查询课后练习

聚合函数

常用聚合函数

AVG/SUM

MAX/MIN

COUNT,

select 1,'aa'#此时表中没有此选项，它会扩展至每一行

可通过count（1）计算表有多少项。

count（）只会记录不为空的项的个数，空的它会跳过。

因此计算表中记录数要使用

count（*）、count（1）、count（不含null的某一字段）

GROUP-BY使用

 

 SELECT中出现非组函数的字段必须声明在GROUP BY中

 

HAVING使用

 HAVING和GROUP BY一定要联合使用

 WHERE与HAVING的对比

SQL底层执行原理 

 SELECT执行顺序

 练习

 子查询

子查询分类

单行、多行子查询

 相关、不相关子查询

 

单行子查询

 多行子查询

 空值问题

 相关子查询

 EXIST和NOT EXIST

使用exist时注意，里面的select不能使用count（*），count（*）没有检索到 的话就是0，仍会返回true，需要使用具体的列。

 子查询课后练习

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees e1 
WHERE e1.department_id = (
												SELECT e.department_id
												FROM employees e 
												WHERE e.last_name = 'Zlotkey'
)
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT employee_id,last_name,salary
FROM employees e
WHERE salary > (
								SELECT AVG(salary)
								FROM employees
								)
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name,job_id, salary
SELECT last_name,job_id, salary
FROM employees e 
WHERE salary > ALL(
										SELECT salary
										FROM employees
										WHERE job_id = 'SA_MAN'

)
#4.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees e 
WHERE e.department_id IN (
													SELECT department_id
													FROM departments 
													WHERE location_id = 1700
)
-- 5.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
												SELECT department_id
												FROM employees
												WHERE last_name LIKE '%u%'
)

-- 6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
										SELECT employee_id,first_name,last_name
										FROM employees
										WHERE last_name = 'King'
)
-- 7. 查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary <= ALL(
										SELECT salary
										FROM employees
)

SELECT last_name,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);

-- 8. 查询平均工资最低的部门信息

SELECT*
FROM departments
WHERE department_id = (
											SELECT department_id
											FROM employees
											GROUP BY department_id
											HAVING AVG(salary) = (
																						SELECT MIN(av_sa)
																						FROM  (
																											SELECT department_id, avg(salary) av_sa
																											FROM employees
																											GROUP BY department_id
																						) aa 
												
											)
)
**********************************************
SELECT*
FROM departments
WHERE department_id = (
											SELECT department_id
											FROM employees
											GROUP BY department_id
											HAVING AVG(salary) <= ALL (
																								SELECT avg(salary) av_sa
																								FROM employees
																								GROUP BY department_id
											)
)
**********************************************
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id

-- 9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT d.*,(SELECT AVG(salary) FROM employees e WHERE e.department_id = d.department_id ) avg_sal
-- SELECT d.*,avg(SELECT salary FROM employees e WHERE e.department_id = d.department_id ) avg_sal
-- AVG要对表的某一列数据进行求平均，对于单列的表，直接写avg（表）也是错误的。
-- select中也可以子查询，此处为啥是相关子查询，执行顺序是from ——>WHERE——>SELECT,
-- 所以前面select中调用是在d表的里面
FROM departments d
WHERE department_id = (
											SELECT department_id
											FROM employees
											GROUP BY department_id
											HAVING AVG(salary) = (
																						SELECT MIN(av_sa)
																						FROM  (
																											SELECT department_id, avg(salary) av_sa
																											FROM employees
																											GROUP BY department_id
																						) aa 
												
											)
)
AVG()SELECT salary FROM employees e WHERE e.department_id = 50

-- 10. 查询平均工资最高的 job 信息
SELECT *
FROM jobs
WHERE job_id = (
								SELECT job_id
								FROM employees
								GROUP BY job_id
								HAVING AVG(salary) >= ALL(
																				SELECT AVG(salary)
																				FROM employees
																				GROUP BY job_id
								) 

)
**********************************************
SELECT *
FROM jobs
WHERE job_id = (
								SELECT job_id
								FROM employees
								GROUP BY job_id
								HAVING AVG(salary) = (
																				SELECT MAX(avg_sla)
																				FROM (
																						SELECT AVG(salary) avg_sla
																						FROM employees
																						GROUP BY job_id
																						) tablea 
																			)

								)
**********************************************
SELECT *
FROM jobs
WHERE job_id = (
									SELECT job_id
									FROM employees
									GROUP BY job_id
									HAVING AVG(salary) = (
																				SELECT AVG(salary)
																				FROM employees
																				GROUP BY job_id
																				ORDER BY AVG(salary) DESC
																				LIMIT 0,1
																				)
								)
**********************************************
SELECT j.*
FROM jobs j,(
							SELECT job_id,AVG(salary) avg_sla
							FROM employees
							GROUP BY job_id
							ORDER BY AVG(salary) DESC
							LIMIT 0, 1
						) jid_avgsla
WHERE j.job_id = jid_avgsla.job_id				
-- 11. 查询平均工资高于公司平均工资的部门有哪些?
SELECT department_name
FROM departments
WHERE department_id IN (#错误使用了=，注意用=时必须是只有一个值
												SELECT department_id
												FROM employees
												WHERE department_id is not NULL#可以从此处先排除那些id为空的部门
												GROUP BY department_id
												HAVING AVG(salary) > (
																							SELECT AVG(salary)
																							FROM employees
																							)
												)

-- 12. 查询出公司中所有 manager 的详细信息
SELECT *
FROM employees e 
WHERE e.employee_id IN ( #IN的地方通常可以改写为exist
												SELECT DISTINCT manager_id
												FROM employees
												WHERE manager_id is not NULL#此句多余
												)
**********************************************												
SELECT employee_id, last_name, salary
FROM employees e1
WHERE EXISTS (
							SELECT *
							FROM employees e2
							WHERE e2.manager_id = e1.employee_id
							);
**********************************************
SELECT DISTINCT e1.employee_id, e1.last_name, e1.salary
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

-- 13. 各个部门中 最高工资之中  最低的那个部门  的最低工资是多少?
SELECT MIN(salary)
FROM employees
WHERE department_id = (
												SELECT department_id
												FROM employees
												WHERE department_id IS NOT NULL
												GROUP BY department_id
												ORDER BY MAX(salary)
												LIMIT 0,1
											)
**********************************************
SELECT MIN(salary)
FROM employees
WHERE department_id = (
											SELECT department_id
											FROM employees
											GROUP BY department_id
											HAVING MAX(salary) = (
																						SELECT MIN(max_sal)
																						FROM (
																									SELECT MAX(salary) max_sal
																									FROM employees
																									GROUP BY department_id
																									) dept_max_sal
																						)
											);

-- 14. 查询平均工资最高的部门的 manager 的详细信息: last_name,department_id, email, salary

SELECT last_name,department_id, email, salary
FROM employees
WHERE employee_id = (
											SELECT manager_id
											FROM departments
											WHERE department_id = (
																							SELECT department_id
																							FROM employees
																							GROUP BY department_id
																							HAVING AVG(salary) >= all(
																																				SELECT AVG(salary)
																																				FROM employees
																																				GROUP BY department_id
																																				)
																						)
																					
										);

-- 15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT department_id
FROM departments
WHERE department_id != ( 
													SELECT DISTINCT department_id
													FROM employees
													WHERE job_id = 'ST_CLERK'
												)
**********************************************
SELECT department_id
FROM departments d
WHERE department_id NOT IN (
														SELECT DISTINCT department_id
														FROM employees
														WHERE job_id = 'ST_CLERK'
														);

16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees
WHERE manager_id is NULL

**********************************************
SELECT last_name
FROM employees e1
WHERE NOT EXISTS(#先写成exists的逻辑，在加not
							SELECT *
							FROM employees e2 
							WHERE e1.manager_id = e2.employee_id
							)
-- 17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT employee_id,last_name,hire_date,salary
FROM employees e1
WHERE employee_id IN(
				SELECT employee_id
				FROM employees e2 
				WHERE e1.manager_id = (
																SELECT employee_id
																FROM employees
																WHERE last_name = 'De Haan'
															)
				)
**********************************************
SELECT employee_id, last_name, hire_date, salary
FROM employees e1
WHERE EXISTS (
							SELECT *
							FROM employees e2
							WHERE e2.`employee_id` = e1.manager_id
							AND e2.last_name = 'De Haan'
							);

**********************************************
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE manager_id = (
										SELECT employee_id
										FROM employees
										WHERE last_name = 'De Haan'
										)

-- 18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难
SELECT employee_id,last_name,salary
FROM employees e1 
WHERE EXISTS (
									SELECT count(*) #此处写count*，即便他检索到0个，但是仍返回true，所以应该用具体的某一列
									SELECT department_id
									FROM
									(SELECT avg(salary) avg_sla,department_id
									FROM employees
									WHERE department_id is not null
									GROUP BY department_id
-- 									HAVING avg_sla > 1000000
									) avg_did
									WHERE e1.department_id = avg_did.department_id  AND e1.salary > avg_did.avg_sla
												
							)

**********************************************				
	SELECT employee_id,last_name,salary
	FROM employees e1 
	WHERE salary > (
										SELECT avg(salary)
										FROM employees e2 
										WHERE e2.department_id = e1.department_id
									)
**********************************************	
SELECT employee_id,last_name,salary
FROM employees e1,
								(SELECT department_id,AVG(salary) avg_sal
									FROM employees e2
								 GROUP BY department_id
								) dept_avg_sal
WHERE e1.`department_id` = dept_avg_sal.department_id
AND e1.`salary` > dept_avg_sal.avg_sal;


-- 19.查询每个部门下的部门人数大于 5 的部门名称
SELECT department_name,department_id
FROM departments
WHERE department_id IN (
												SELECT department_id
												FROM employees
												GROUP BY department_id
												HAVING count(department_id) > 5
												)
												
**********************************************
SELECT department_name,department_id
FROM departments d
WHERE 5 < (
					SELECT COUNT(*)
					FROM employees e
					WHERE d.`department_id` = e.`department_id`
					);
-- 20.查询每个国家下的部门个数大于 2 的国家编号
SELECT country_id
FROM locations l  
WHERE 2 < (
						SELECT COUNT(*)
						FROM departments d 
						WHERE l.location_id = d.location_id
					)

老霉儿
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL-数据库的高级查询-聚合函数、分组查询、多表连接、UNION关键字和子查询-学习笔记06
baidu_41924187的博客
05-14 554
文章目录0. 语法总结1. 数据统计分析1.1 聚合函数（AVG，SUM等）1.2 一个容易犯错的地方：聚合函数不能出现在WHERE中1.2 分组查询以及对SELECT子句的要求1.2.1 基础用法（GROUP BY）1.2.2 按照数字进行分组1.2.2 逐级分组1.2.3 对SELECT子句的要求1.2.4 对分组统计的结果再次做汇总运算（GROUP BY * WITH ROLLUP）1.2.5 GROUP_CONCAT函数1.3 HAVING子句2. 多表连接查询2.1 内连接2.2 外连接以及UNI
MySQL小节练习二
无
09-18 1911
四：分组函数练习 1.where子句可否使用组函数进行过滤? 不可以 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
mysql子查询、聚合函数
程序三两行
07-19 860
mysql子查询
mysql聚合函数查询，子查询，关联查询
weixin_42467346的博客
01-25 507
聚合函数 MAX() MIN() COUNT() SUM() AVG() 求最高成绩 from django.db.models import Max Stu.objects.aggregate(Max('score')) {'score__max': 99} showsql() SELECT MAX(`t_stu`.`score`) AS `score__max` FROM `t_stu`...
MySQL基础（三）聚合函数、子查询
qq_54517101的博客
03-15 920
HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。例如下面的写法就是错误的，分组之后，每一个department_id会有一个AVG(salary)值，但是无法确定对应哪个job_id值。通过一个例子引入子查询的使用场景：查询工资比Abel高的人的名字和工资。
mysql数据库基础知识点与操作小结
09-08
总结，MySQL的基础知识点和操作涵盖了数据表结构的管理、数据的增删改查以及复杂查询中的子查询，同时提供了强大的函数支持，使得数据库操作更加便捷高效。对于初学者和开发者来说，理解和掌握这些基础知识点是构建...
MySQL数据库考试试题及答案-34页
06-01
在本文中，我们将对 MySQL 数据库考试试题及答案进行详细的解析，并总结出相关的知识点。 1．数据库聚合函数 在 MySQL 中，聚合函数用于对数据进行汇总和计算。 SUM 函数用于计算数据的总和，MAX 函数用于取得最大...
MySQL面试题知识点总结
04-15
MySQL是世界上最受欢迎的关系型数据库管理系统（RDBMS）之一，尤其在Web应用...熟悉以上知识点，将有助于在MySQL相关的面试中展现出扎实的技术基础。随着技术的发展，不断学习和掌握新的特性和最佳实践也是必不可少的。
数据库系统及原理及MYSQL应用教程多表连接和子查询实验报告
04-15
实验报告主要涵盖了数据库系统的基本操作，特别是MySQL中的分组查询、多表连接和子查询的运用。以下是对这些知识点的详细解释： 1. **GROUP BY 语句与聚合函数**： GROUP BY 语句用于将数据分组，通常与聚合函数如...
高级软件人才培训专家-day07-数据库-MySQL(2)
最新发布
03-10
根据给定的信息，我们将深入探讨有关 MySQL 数据库中数据查询语言 (DQL) 的关键知识点。这主要包括如何使用 DQL 进行基本查询、条件查询、分组查询、排序查询和分页查询。 ### 一、DQL简介 DQL（Data Query ...
【MySQL】聚合查询
程序猿教你打篮球的博客
03-17 3797
本期主要讲解： 1、前言 2、插入查询结果 3、聚合查询 3.1 聚合函数 3.1.1 count 3.1.2 sum 3.1.3 avg 3.1.4 max 和 min 4、GROUP BY 子句 5、HAVING 关键字
【数据库】MySql复杂查询的用法：聚合查询、分组查询、子查询、多表查询的用法及示例
zhongqingtian的博客
05-02 1043
【数据库】MySql复杂查询的用法：聚合查询、分组查询、子查询、多表查询的用法及示例
Oracle——操作题测试——试卷题
girls的博客
12-30 1754
Oracle——操作题测试一、用户和权限操作（每小题4分）1. 创建表空间myuser，数据文件为d1.dbf，表空间大小为20M2. 创建一个用户，用户名为rose，密码为r123，默认表空间为myuser3. 为该用户授予connect角色和create table权限4. 以该用户连接oracle数据库二、表操作（每小题4分）1. 使用rose用户创建表mytab，表结构如下：2. 删除ZY字段3. XB字段重命名为SEX4. 向表中插入如下记录5. .创建一个视图v_mytab，视图包含xh、xm、
进阶5、MySQL基础_分组查询+测试题
qq_39044046的博客
09-13 903
语法： select 分组函数,列(要求出现在group by的后面) from 表 【where 筛选条件】 group by 分组的列表 【order by子句】 注意：查询列表必须特殊,要求是分组函数和group by后出现的字段 一、分组查询的简单使用： 查询每个部门的平均工资 SELECT AVG(salary) FROM employees G...
MySQL 56课后习题
weixin_48687138的博客
07-25 2989
1.显示系统时间（注 ：日期+时间） SELECT now() AS 系统时间 ; 2.查询员工号，姓名，工资以及工资提高百分之20后的结果（new_salary) SELECT employee_id,last_name,salary,salary*1.2 AS ‘new_salary’ FROM employees; 3.将员工的姓名按照首字母排序，并写出姓名的长度 SELECT last_name,LENGTH(last_name) AS 姓名长度 FROM employees ORDER BY SU
MySQL专题五——子查询
qq_24292711的博客
02-06 656
子查询 举例 查找出工资比Abel高的员工名字和工资 方式一 多次查询 SELECT salary `salary_abel` FROM employees WHERE last_name = 'Abel'; SELECT last_name, salary FROM employees WHERE salary > salary_abel; 方式二 自连接 SELECT e2.last_name, e2.salary FROM employees e1 , employees e2 WHERE e
mysql中的方差函数_请问关于SQL中的方差函数VAR（）相关问题？
weixin_42099633的博客
01-19 2177
聚合函数是对一组值执行计算并返回单一的值的函数，它经常与SELECT语句的GROUP BY子句一同使用，SQL SERVER 中具体有哪些聚合函数呢？我们来一一看一下：1. AVG 返回指定组中的平均值，空值被忽略。例：select prd_no,avg(qty) from sales group by prd_no2. COUNT 返回指定组中项目的数量。例：select count(prd_n...
Oracle查询练习01
天上白玉京 十二楼五城
07-24 1638
1 . 查询工资大于12000的员工姓名和工资select last_name, salary from employees where salary > 120002 . 查询员工号为176的员工的姓名和部门号select last_name, department_id from employees where employee_id = 1763 . 选择工资不在5000到12000的员工的姓
MySQL 68课后习题
weixin_48687138的博客
07-25 2931
1.查询各job_id的员工工资的最大值，最小值，平均值，总和并按照job_id升序 SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id ORDER BY job_id ASC; 2.查询员工最高工资和最低工资的差距（DIFFERENCE） SELECT MAX(salary)-MIN(salary) AS DIFFERENCE FROM employees; 3.查询各个管
mysql数据库 - 使用聚合函数查询
06-28
MySQL 数据库中可以使用聚合函数来对查询结果进行统计分析。常用的聚合函数包括：COUNT（）、SUM（）、AVG（）、MIN（）、MAX（）。 例如，在一个订单表中，想要统计订单总数，可以使用如下语句: ``` SELECT COUNT...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值