第09章_MySQL子查询

news/2024/3/29 5:27:55/文章来源:https://blog.csdn.net/weixin_46655235/article/details/129178969

第09章_子查询

讲师:尚硅谷-宋红康(江湖人称:康师傅)

官网:http://www.atguigu.com


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

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

1. 需求分析与问题解决

1.1 实际问题

image-20230220142204749

现有解决方式:

#方式1:
SELECT salary
FROM employees
WHERE last_name = 'Abel';SELECT last_name, salary
FROM employees
WHERE salary > 11000;#方式2:自连接
SELECT e2.last_name, e2.salary
FROM employees e1, employees e2
WHERE e1.last_name = 'Abel' AND e1.salary < e2.salary;
#方式3:子查询
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel');

1.2 子查询的基本使用

  • 子查询的基本语法结构:

image-20230220144101534

  • 子查询要包含在括号内
  • 子查询的结果被主查询使用

1.3 子查询的分类

分类方式1:

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

  • 单行子查询

image-20230222185333716

  • 多行子查询

image-20230222185350960

  • 单行操作符对应单行子查询,多行操作符对应多行子查询

分类方式2:

我们按子查询是否被执行多次,将子查询划分为相关子查询不相关子查询

  • 相关子查询

    • 如果子查询需要执行多次,即采用循环的方式,先从主查询开始,每次都将主查询的一条记录传入子查询中进行查询,然后再将结果反馈给主查询,这种嵌套的执行方式就称为相关子查询

    • 子查询需要执行多次,而且与主查询穿插进行

  • 不相关子查询

    • 子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,那么这样的子查询叫做不相关子查询
    • 子查询在主查询之前一次执行完成

2. 单行子查询

2.1 单行比较操作符

操作符含义
=equal to
>greater than
>=greater than or equal to
<less than
<=less than or equal to
<>not equal to

2.2 代码示例

题目:查询工资大于149号员工工资的员工的信息

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT salaryFROM employeesWHERE employee_id=149);

image-20230220154454064

题目:返回job_id与141号员工相同,salary比143号员工多的员工的姓名,job_id和工资

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (	SELECT job_idFROM employeesWHERE employee_id = 141)
AND salary > (SELECT salaryFROM employeesWHERE employee_id = 143		)

题目:返回公司工资最少的员工的last_name,job_id和salary

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

image-20230220155042098

题目:查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id

实现方式1:不成对比较

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id = (SELECT manager_idFROM employeesWHERE employee_id = 141)
AND department_id = (SELECT department_idFROM employeesWHERE employee_id = 141)
AND employee_id <> 141;

实现方式2:成对比较

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id,department_id) = (SELECT manager_id, department_idFROM employeesWHERE employee_id = 141)
AND employee_id <> 141;

image-20230220155944723

2.3 HAVING中的子查询

题目:查询最低工资大于110号部门最低工资的部门的id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary)> (SELECT MIN(salary)FROM employeesWHERE department_id = 110);

image-20230220172247547

2.4 CASE中的子查询

题目:显式员工的employee_id,last_name和location。其中,若员工的department_id与location_id为1800的部门的department_id相同,则location为’Canada’,其余则为’USA’。

SELECT employee_id, last_name, CASE department_id 
WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' 
ELSE 'USA' END "location"					
FROM employees;

image-20230220173459731

2.5 子查询中的空值问题

子查询不返回任何行

SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employeesWHERE last_name = 'Haas');

2.6 非法使用子查询

多行子查询使用单行比较符

SELECT employee_id, last_name
FROM employees
WHERE salary = (SELECT MIN(salary)FROM employeesGROUP BY department_id);

3. 多行子查询

3.1 多行比较操作符

操作符含义
IN等于列表中的任意一个
ANY需要和单行比较操作符一起使用,和子查询返回的某一个值比较
ALL需要和单行比较操作符一起使用,和子查询返回的所有值比较
SOME实际上是ANY的别名,作用相同,一般常使用ANY

3.2 代码示例

题目:返回其它job_id的员工中比job_id为‘IT_PROG’的任一员工的工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG');

image-20230222142624915

题目:返回其它job_id的员工中比job_id为‘IT_PROG’的所有员工的工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG');

image-20230222142857330

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

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(SELECT AVG(salary)FROM employeesGROUP BY department_id)

image-20230222143212425

3.3 空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN(SELECT manager_id #筛选出来的记录有nullFROM employees);

image-20230222143631157

4. 相关子查询

4.1 相关子查询执行流程

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

相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。

image-20230222234127522

4.2 代码示例

题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

方式一:相关子查询

SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary)FROM employees e2WHERE department_id = e1.department_id);	

方式二:在 FROM 中使用子查询

要给这个子查询取别名,并且聚合函数字段也需要取别名
把它当成一张“临时的虚拟的表”来使用。

SELECT e.last_name, e.salary, e.department_id
FROM employees e,(SELECT department_id, AVG(salary) avg_salFROM employeesGROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal;

image-20230222160254456

题目:查询员工的id,salary,按照department_name 排序

在ORDER BY 中使用子查询:

SELECT employee_id, salary
FROM employees e
ORDER BY(SELECT department_nameFROM departments dWHERE d.department_id = e.department_id )ASC;

image-20230222161234338

题目:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id

SELECT employee_id, last_name, job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)FROM job_history jWHERE j.employee_id = e.employee_id )

image-20230222161722684

4.3 EXISTS 与 NOT EXISTS关键字

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

  • 在遍历子查询中的行时

    • 如果在子查询中的行满足条件:直接返回 TRUE,不再继续遍历
    • 如果在子查询中的行不存在满足条件:继续遍历,如果全都遍历完都不存在满足条件的行,返回false
  • NOT EXISTS关键字表示如果在子查询中不存在满足条件的行,则返回TRUE,否则返回FALSE

题目:查询公司管理者的employee_id,last_name,job_id,department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS(SELECT *FROM employees e2WHERE e1.employee_id = e2.manager_id);

题目:查询departments表中,不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS(SELECT *FROM employees eWHERE d.department_id = e.department_id);

image-20230222165845693

4.4 相关更新

UPDATE table1 alias1
SET    column = (SELECT expressionFROM   table2 alias2WHERE  alias1.column = alias2.column);

使用相关子查询依据一个表中的数据更新另一个表的数据。

题目:在employees中增加一个department_name字段,数据为员工对应的部门名称

# 1)
ALTER TABLE employees
ADD(department_name VARCHAR2(14));# 2)
UPDATE employees e
SET department_name =  (SELECT department_name FROM   departments dWHERE  e.department_id = d.department_id);

4.4 相关删除

 DELETE FROM table1 alias1WHERE column operator (SELECT expressionFROM   table2 alias2WHERE  alias1.column = alias2.column);

使用相关子查询依据一个表中的数据删除另一个表的数据。

题目:删除表employees中,其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in  (SELECT employee_idFROM   emp_history WHERE  employee_id = e.employee_id);

5. 抛一个思考题

问题:谁的工资比Abel的高?

解答

#方式1:自连接
SELECT e2.last_name, e2.salary
FROM employees e1, employees e2
WHERE e1.last_name = 'Abel' AND e1.salary < e2.salary;
#方式2:子查询
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel');

问题:以上两种方式有好坏之分吗?

解答:自连接方式好!

题目中可以使用子查询,也可以使用自连接。一般情况建议你使用自连接,因为在许多 DBMS 的处理过程中,对于自连接的处理速度要比子查询快得多。
可以这样理解:子查询实际上是通过未知表进行查询后的条件判断,而自连接是通过已知的自身数据表进行条件判断,因此在大部分 DBMS 中都对自连接处理进行了优化

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_72986.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

2023年你应该关注的JavaScript趋势

本文翻译者系360 奇舞团前端开发工程师原文标题&#xff1a;Top JavaScript (JS) Trends You Should Follow in 2023原文作者&#xff1a;yevheniia原文地址&#xff1a;https://www.codica.com/blog/top-javascript-trends/#top-java-script-frameworks-and-libraries-to-watc…

【python】考前复习,python基础语法知识点整理

文章目录1.常量与表达式2.变量和数据类型创建变量数据类型动态类型数据类型的转换3.注释4.字符串字符串的定义方式字符串的拼接字符串的格式化①字符串格式化的精度控制字符串的格式化②对表达式进行格式化5.从控制台输入(input)6.运算符算术运算符赋值运算符布尔类型和比较运算…

顶象APP加固的“蜜罐”技术有什么作用

目录 蜜罐有很多应用模式 蜜罐技术让App加固攻守兼备 顶象端加固的三大功能 为了捕获猎物&#xff0c;猎人会在设置鲜活的诱饵。被诱惑的猎物去吃诱饵时&#xff0c;就会坠入猎人布置好的陷阱&#xff0c;然后被猎人擒获&#xff0c;这是狩猎中常用的一种手段。在业务安全防…

【华为OD机试真题】用 C++ 实现 - 最小施肥机能效

最近更新的博客 华为OD机试 - 入栈出栈(C++) | 附带编码思路 【2023】 华为OD机试 - 箱子之形摆放(C++) | 附带编码思路 【2023】 华为OD机试 - 简易内存池 2(C++) | 附带编码思路 【2023】 华为OD机试 - 第 N 个排列(C++) | 附带编码思路 【2023】 华为OD机试 - 考古…

关于《How to Learn to Code Get a Developer Job in 2023》的经验学习

1. Who is This Book For ? for Anyone who is considering a career in software development. 2. Can Anyone Learn to Code? Any sufficiently motivated person can learn to code. 3.Executive Summary Learning to code is hard.Getting a job as a software devel…

jupyter的安装步骤

1.安装python文件 首先去官网python去下载python的安装包&#xff0c;点击donwload,选择合适的系统。这里我是windown系统&#xff0c;点击进去&#xff0c;如图找到有installer的去下载。不建议下载最新版本的&#xff0c;会有兼容问题。 2.安装python 点击第二个选项是自己配…

JavaScript 进阶--charater3

文章目录前言一、编程思想1.1 面向过程介绍1.2 面向对象编程 (oop)对比二、构造函数三、原型3.1原型3.2 constructor 属性3.3 对象原型3.4 原型继承3.5 原型链总结前言 &#x1f191;学习目标 理解面向对象思想&#xff0c;掌握函数原型对象运用面向对象封装继承特点&#xf…

Docker之路(5.Docker镜像学习、镜像加载原理、镜像分层原理、通过commit提交镜像)

1.镜像是什么 镜像是一种轻量级&#xff0e;可执行的独立软件包&#xff0c;用来打包软件运行环境和基于运行环境开发的软件&#xff0c;它包含运行某个软件所需的所有内客&#xff0c; 包活代码、运行时、库、环境变量和配置文件。 所有的应用&#xff0c;直接打包docker镜像&…

UE4 手把手教你做插件(4)做一个可以拖入场景的小方块插件

0&#xff0c;前言 学习一下如何编写一个有actor的插件&#xff0c;能够将它拖入场景里面。 1&#xff0c;创建插件 &#xff08;1&#xff09;创建一个空白插件 添加之后vs里面就会多出一个目录&#xff1a; &#xff08;2&#xff09;给插件添加一个actor 创建完actor之后&am…

ubuntu本地访问nas

需求 本地磁盘空间太小&#xff0c;本地网络里有个nas&#xff0c;希望将nas作为数据盘挂载到本地使用。 方法1 基于sftp访问nas 首先nas设置时要打开sftp访问功能。 然后用ubuntu桌面访问服务器的功能登录sftp&#xff0c;类似如下指令 sftp://user192.168.0.100 ubuntu下…

DevData Talks | 对谈谷歌云 DORA 布道师,像谷歌一样度量 DevOps 表现

本期 DevData Talks 我们请到来自 Google Cloud 谷歌云的 DORA 研究团队的嘉宾 Nathen Harvey与 Apache DevLake、CNCF DevStream 的海外社区负责人 Maxim 进行对谈。如果您关注 DevOps 的话&#xff0c;也许对这个团队有所耳闻。 DORA 的全称是 DevOps Research and Assessme…

【运动控制】CNC三轴小线段路径规划

CNC三轴小线段路径规划 文章目录CNC三轴小线段路径规划一、项目说明二、具体实现1、速度规划2、小线段插补3、运动学逆解刀轴插补点4、差分处理得到实际的速度和加速度5、加速度滑动平均6、实现的效果如图所示三、Reference写在前面&#xff0c;本文是作为一个练手小项目的总结…

论文浅尝 | Deep Reinforcement Learning for Entity Alignment

笔记整理&#xff1a;陈鹏&#xff0c;天津大学硕士链接&#xff1a;https://aclanthology.org/2022.findings-acl.217.pdf动机实体对齐(Entity Alignment&#xff09;的基本目标在于发现两个知识图谱间指向同一现实对象的实体对&#xff0c;以便将不同知识图谱连接起来&#x…

Interview系列 - 06 Java | ArrayList底层源码分析 | 遍历集合时如何删除集合中的元素

文章目录1. 底层源码分析01. 属性02. 构造方法03. 在数组的末尾添加元素 add(E e)04. 在数组的指定位置添加元素 add(int index, E element)05. 替换指定位置的元素 set(int index, E element)06. 获取指定索引位置处的元素 get(int index)07. 删除指定位置的元素 remove(int i…

华为OD机试真题 用 C++ 实现 - 众数和中位数 | 多看题,提高通过率

最近更新的博客 华为OD机试 - 入栈出栈(C++) | 附带编码思路 【2023】 华为OD机试 - 箱子之形摆放(C++) | 附带编码思路 【2023】 华为OD机试 - 简易内存池 2(C++) | 附带编码思路 【2023】 华为OD机试 - 第 N 个排列(C++) | 附带编码思路 【2023】 华为OD机试 - 考古…

SpringCloud - Feign远程调用

目录 Feign的远程调用 RestTemplate方式调用存在的问题 介绍与初步使用 Feign的自定义配置 Feign运行自定义配置来覆盖默认配置&#xff0c;可以修改的配置如下&#xff1a; 配置Feign日志有两种方式&#xff1a; Feign性能优化 Feign底层的客户端实现&#xff1a; 连…

AI作画—中国画之山水画

山水画&#xff0c;简称“山水”&#xff0c;中国画的一种&#xff0c;描写山川自然景色为主体的绘画。山水画在我国绘画史中占有重要的地位。 山水画形成于魏晋南北朝时期&#xff0c;但尚未从人物画中完全分离。隋唐时始终独立&#xff0c;五代、北宋时趋于成熟&#xff0c;…

Linux多媒体子系统01:从用户空间使用V4L2子系统

1 V4L2应用编程基础1.1 概述V4L2应用编程需要使用如下系统调用&#xff0c;open(): 打开V4L2设备 close(): 关闭V4L2设备 ioctl(): 向V4L2设备驱动程序发送控制命令 mmap(): 将V4L2设备驱动程序分配的缓冲区内存映射到用户空间 read()或write(): 这2个系统调用是否支持取决于流…

领导催我优化SQL语句,我求助了ChatGPT。这是ChatGPT给出的建议,你们觉得靠谱吗

作为一个程序员&#xff0c;无论在面试还是工作中&#xff0c;优化SQL都是绕不过去的难题。 为啥&#xff1f;工作之后才会明白&#xff0c;随着公司的业务量增多&#xff0c;SQL的执行效率对程系统运行效率的影响逐渐增大&#xff0c;相对于改造代码&#xff0c;优化SQL语句是…

LeetCode-93. 复原 IP 地址

目录题目思路回溯法题目来源 93. 复原 IP 地址 题目思路 意识到这是切割问题&#xff0c;切割问题就可以使用回溯搜索法把所有可能性搜出来&#xff0c;和131.分割回文串就十分类似了。 回溯法 1.递归参数 startIndex一定是需要的&#xff0c;因为不能重复分割&#xff0c…