【MySQL】第09章_子查询

已于 2022-12-09 02:23:43 修改
阅读量434 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库 sql
于 2022-12-09 02:23:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qiuyusy/article/details/128246509
版权

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

1. 需求分析与问题解决

1.1 实际问题

image-20221208232748000

现有解决方式：

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

1.2 子查询的基本使用

image-20221208233008320

image-20221209003316728

1.3 子查询的分类

分类方式1：记录数

我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。

image-20221208233334742

分类方式2：执行次数

我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。
同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

2. 单行子查询

2.1 单行比较操作符

image-20221208233502904

2.2 代码示例

image-20221208233541974

image-20221208233614013

image-20221208233620733

image-20221208233656820

image-20221208233709627

image-20221208233718603

2.3 HAVING 中的子查询

image-20221208235503804

2.4 CASE中的子查询

image-20221208235540007

2.5 子查询中的空值问题

image-20221208235720332

2.6 非法使用子查询

image-20221208235805158

3. 多行子查询

  • 也称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符

3.1 多行比较操作符

image-20221208235945545

体会 ANY 和 ALL 的区别

  • ANY 和查询到的记录比较比成功任何一个就行
  • ALL 和查询到的记录比较需要全部成功

3.2 代码示例

关键词 其他

image-20221209000044072

image-20221209000303340

题目：查询平均工资最低的部门id

#方式1：找平均工资，找最小平均工资，找部门
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
    SELECT MIN(avg_sal)
    FROM (
            SELECT AVG(salary) avg_sal
            FROM employees
            GROUP BY department_id
         ) dept_avg_sal
    )


#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
)

3.3 空值问题

image-20221209000728933

4. 相关子查询

上面那些案例都是不相关子查询，相关子查询需要子查询调用外部查询的列

4.1 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

image-20221209001317553

4.2 代码示例

每个员工的部门不同，每次都需要把当前员工放入子查询中，寻找这么员工的部门的平均工资

image-20221209001339653

from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟的表”来使用。

image-20221209001644592

4.3 EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行

image-20221209002854702

image-20221209002919121

image-20221209003006543

这里的‘X’填什么都行，子查询只是为了获得记录

image-20221209003113259

4.4 相关更新

image-20221209003341689

4.5 相关删除

image-20221209003413953

5. 抛一个思考题

image-20221209003435798

image-20221209003549199

6. 练习

1.查询和Zlotkey相同部门的员工姓名和工资

//自连接
SELECT e2.last_name,e2.salary
FROM employees e1, employees e2
WHERE e1.last_name = "Zlotkey"
AND e2.department_id = e1.department_id

//子查询
SELECT last_name,salary
FROM employees 
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = "Zlotkey"
)

2.查询工资比公司平均工资高

SELECT last_name,salary
FROM employees 
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 
WHERE salary > ALL (
	SELECT salary
	FROM employees
	WHERE job_id = "SA_MAN"
)

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

//我的  可以加上DISTINCT
SELECT employee_id,last_name
FROM employees 
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE "%u%"
)

//答案
SELECT employee_id, last_name
FROM employees
WHERE department_id = ANY(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
)

5.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees 
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id = 1700
)

6.查询管理者是King的员工姓名和工资

SELECT last_name,salary
FROM employees 
WHERE manager_id IN (
	SELECT employee_id
	FROM employees
	WHERE last_name = "King"
)

7.查询工资最低的员工信息: last_name, salary

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 ) <= ALL ( 
		SELECT AVG( salary ) avg_sal 
		FROM employees 
		GROUP BY department_id ) 
	);

9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

SELECT * ,(SELECT AVG(salary) FROM employees WHERE employees.department_id = d.department_id) "avg" 
FROM departments d
WHERE department_id = (
	SELECT department_id 
	FROM employees 
	GROUP BY department_id 
  	HAVING AVG( salary ) <= ALL ( 
		SELECT AVG( salary ) avg_sal 
		FROM employees 
		GROUP BY department_id ) 
	);

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
	)
)

11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary)
	FROM employees
	
)

12.查询出公司中所有 manager 的详细信息

SELECT *
FROM employees
WHERE employee_id IN (
	SELECT DISTINCT manager_id
	FROM employees
)

13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

SELECT MIN(salary)
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING MAX(salary) <= ALL (
		SELECT MAX(salary) max_sal
		FROM employees
		GROUP BY department_id
	)
	
)

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

一层一层往下找

先找 manager

然后 部门

然后 最高

然后 平均工资

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
	SELECT employee_id
	FROM employees
	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 NOT IN(
	SELECT DISTINCT department_id
	FROM employees
	WHERE job_id = "ST_CLERK"
)

16.选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees
WHERE employee_id IN (
	SELECT employee_id
	FROM employees
	WHERE manager_id IS NULL
)


//答案
SELECT last_name
FROM employees e1
WHERE NOT EXISTS (
SELECT *
FROM employees e2
WHERE e1.manager_id = e2.employee_id
);

17. 查询员工号、姓名、雇用时间、工资，其中员工的管理者为 ‘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"
)


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'
);

18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE e1.department_id=e2.department_id
)

19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

SELECT department_name
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`
);
QiuYuSy
关注
专栏目录
【MySQL】练习五 数据库完整性
兔子王
04-10 865
主要内容 1、三类完整性的定义 2、触发器的作用
第09章_子查询
分享思想，留下痕迹。
03-30 1583
✅作者简介：大家好，我是Leo，热爱Java后端开发者，一个想要与大家共同进步的男人😉😉💞当前专栏：✨特色专栏：🥭本文内容：第09章_子查询，欢迎大家访问📚个人知识库：，欢迎大家访问子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
Mysql8.0习题系列（五）：子查询（一篇搞懂子查询做题技巧，超详细）
阿里云专家博主、数据科学领域优质创作者、统计er在读
03-24 1954
Mysql8.0习题系列 软件下载地址 提取码：7v7u 数据下载地址 提取码：e6p9 文章目录Mysql8.0习题系列子查询1.查询和Zlotkey相同部门的员工姓名和工资2.查询工资比公司平均工资高的员工的员工号，姓名和工资。3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名5.查询在部门的location_id为1700的部门工作的员工的员工号6.查询管理者是Ki
练习5:MySQL数据插入、更新与删除
匿名攻城狮
08-29 3000
案例:创建表books，对数据进行插入、更新和删除操作，掌握数据表的基本操作。books表结构以及表中的记录如下表： 案例操作过程： (1)创建数据表books，并按照表8.1所示的结构定义各个字段。 (2)将表8.2中的记录插入books表中。分别使用不同的方法插入记录。 (3)将小说类型(novel)的书的价格都增加5。 (4)将名称为EmmaT的书的价格改为40，并将note说明改为dram...
MySQL入门之课后思考题第八章
MrSuper王的博客
05-05 1070
下面使用 mysqldump 命令备份 chapter08 数据库的语句中，正确的是（） A、 mysqldump –u root –p itcast chapter08>d:/chapter08.sql B、 mysqldump -uroot -pitcast chapter08>d:/chapter08.sql C、 mysqldump –u root –p itcast chap...
深入浅出mysql第8章(字符集)习题答案
yaoguaiba579567的博客
01-18 451
1、
第09章_子查询.docx
最新发布
01-17
子查询是指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。子查询可以大大增强 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个...
第09章_子查询1
08-03
【子查询】是数据库SQL语言中的一个重要概念，它允许在一个查询语句中嵌套另一个查询语句，以增强查询的复杂性和灵活性。这种特性自MySQL 4.1版本开始引入，使得开发者能够处理更复杂的查询需求。 在实际问题中，...
MySQL (九) : 子查询
Guizy
04-09 1082
第09章_子查询 子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。 SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。 1. 需求分析与问题解决 1.1 实际问题 现有解决方式： #方式一： SELECT salary FROM employees WHERE last_name = 'Abel
mysql 分组查询慢_MySQL 慢查询的特征表现及优化方式
weixin_33293848的博客
02-27 1165
我们将超过指定时间的SQL语句查询称为慢查询一、慢查询的体现慢查询主要体现在慢上，通常意义上来讲，只要返回时间大于 >1 sec上的查询都可以称为慢查询。慢查询会导致CPU，内存消耗过高。数据库服务器压力陡然过大，那么大部分情况来讲，肯定是由某些慢查询导致的。查看/设置“慢查询”的时间定义mysql> show variables like "long%";+-------------...
mysql第八章_MySQL_第八章
weixin_39898150的博客
01-19 215
第八章：Dao模式JDBC封装为了提高代码的维护性和扩展性，我们使用JDBC进行封装数据，先定义统一的API，将操作数据的代码抽象到接口中，业务逻辑代码只需要调用这些接口的实现类的对象，就可以实现对数据的访问了，从而隔离实现的细节，采用面向接口编程，可以降低代码间的耦合度，提高代码的扩展性和维护性。DAO(data access Objects) 数据存取对象，位于业务逻辑和持久化之间，实现对...
MySQL 第八章-聚合函数+课后练习
weixin_45865571的博客
04-23 949
#1、常见的几个聚合函数 #1.1 AVG / SUM : 只适用于数值类型的字段 （或变量） SELECT AVG(salary) FROM employees; SELECT SUM(salary) FROM employees; #1.2 MAX / MIN 而这个使用于，数值类型和字符串类型，日期时间类型的字段（或变量） SELECT MAX(salary),MIN(salary) FROM employees; SELECT MAX(last_name),MIN(last_name)
MySQL_第八章
GTS311111111的博客
10-09 293
第八章：Dao模式 JDBC封装 为了提高代码的维护性和扩展性，我们使用JDBC进行封装数据， 先定义统一的API，将操作数据的代码抽象到接口中，业务逻辑代码只需要调用这些接口的实现类的对象，就可以实现对数据的访问了，从而隔离实现的细节，采用面向接口编程，可以降低代码间的耦合度，提高代码的扩展性和维护性。 DAO(data access Objects) 数据存取...
在mysql中为了加快数据表_数据库原理与应用教程 何玉洁 五——八章课后习题部分答案...
weixin_39739234的博客
02-05 1031
第五章一、试说明使用视图的好处。答：1、简化数据查询语句2、使用户能从多角度看待同一数据3、提高了数据的安全性4、提供了一定程度的逻辑独立性二、使用视图可以加快数据的查询速度，这句话对吗？为什么？答：不对。因为数据库管理系统在对视图进行查询时，首先检查要查询的视图是否存在，如果存在，则从数据字典中提取视图的定义，把视图的定义语句对视图语句结合起来，转换成等价的对基本表的查询，然后再执行转换后的查询...
1、查询姓名中包含‘u’字母的员工记录2、同名去重3、字段计算
weixin_43826111的博客
02-23 1510
1、select *from emp where ename like %u%; 2、select distinct ( ename) from emp ; 3、select （comn+sal，0） as money from emp （如果comn+sal等于空就返回零）
MySQL数据库技术 第二版 章末 答案—单元8
qq_40927409的博客
12-20 8810
MySQL数据库技术 第二版 章末 答案—单元8 以下是我个人所尝试过得答案，若有不正确的地方请告知，谢谢！ 由于实在是太多内容了，原谅我省略题目内容 单元8 数据库编程 ---- P166-P168 跳到底部，进行题目筛选 实训8 底部 1、存储过程 -- 创建存储过程 USE yggl; DELIMITER $$ CREATE PROCEDURE eqIncome(IN propler1 CHAR(6),IN propler2 CHAR(6),OUT isHei...
mysql第八章_MySQL必知必会--第五章~第八章
weixin_39612297的博客
02-28 117
第六章--过滤数据1. 在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤， WHERE子句在表名(FROM子句)之后给出例如：select prod_names， prod_id, prod_price from products where prod_price=2.5;WHERE子句的位置 在同时使用ORDERBY和WHERE子句时，应 该让ORDER BY位于WHERE之...
P48 第09章_子查询练习题
weixin_54671087的博客
11-28 2010
第09章_子查询练习题 #1.查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name,salary FROM employees WHERE department_id IN( SELECT department_id FROM employees WHERE last_name='Zlotkey' ) #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 SELECT employee_id,last_name,salary FROM employee
Mysql——查询sql语句练习
qq_63099085的博客
10-17 1115
select student2.s_id,student2.s_name,r.选课数,r.总成绩 from student2,(select score2.s_id,sum(score2.s_score) as 总成绩,count(score2.s_score) as 选课数 from score2 group by score2.s_id) as r where student2.s_id=r.s_id;
MySQL高级教程：子查询详解及应用实例
第09章的子查询部分深入讲解了如何在MySQL中灵活运用子查询来解决实际问题，无论是基本的单行或多行比较，还是高级的关联和非关联查询，以及在HAVING和CASE语句中的应用，都是提升SQL查询能力的重要手段。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值