程序员经验分享-hwhale

Mysql基础篇笔记-第09章_子查询

2023-11-19

仅作个人笔记使用

本篇的学习来源是：尚硅谷Mysql基础教程-宋红康。教学视频来自B站【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！】 。感谢老师的教学！

第09章_子查询

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

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

1. 需求分析与问题解决

1.1 实际问题

在这里插入图片描述

现有解决方式：

#方式一：
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 子查询的基本使用

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

在这里插入图片描述

  • 子查询（内查询）在主查询之前一次执行完成。
  • 子查询的结果被主查询（外查询）使用 。
  • 注意事项
    • 子查询要包含在括号内
    • 将子查询放在比较条件的右侧
    • 单行操作符对应单行子查询，多行操作符对应多行子查询

1.3 子查询的分类

分类方式1：

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

  • 单行子查询

在这里插入图片描述

  • 多行子查询

在这里插入图片描述

分类方式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号员工工资的员工的信息

在这里插入图片描述
在这里插入图片描述

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

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

在这里插入图片描述

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

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

在这里插入图片描述

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

实现方式1：不成对比较

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN
		  (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND     department_id IN 
		  (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND	employee_id NOT IN(174,141);

实现方式2：成对比较

SELECT	employee_id, manager_id, department_id
FROM	employees
WHERE  (manager_id, department_id) IN
                      (SELECT manager_id, department_id
                       FROM   employees
                       WHERE  employee_id IN (141,174))
AND	employee_id NOT IN (141,174);

2.3 HAVING 中的子查询

  • 首先执行子查询。
  • 向主查询中的HAVING 子句返回结果。

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

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

2.4 CASE中的子查询

在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;

2.5 子查询中的空值问题

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

在这里插入图片描述

子查询不返回任何行

2.5 非法使用子查询

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

在这里插入图片描述

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

3. 多行子查询

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

3.1 多行比较操作符

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

体会 ANY 和 ALL 的区别

3.2 代码示例

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

在这里插入图片描述

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

在这里插入图片描述
在这里插入图片描述

题目：查询平均工资最低的部门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 空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			);

在这里插入图片描述

4. 相关子查询

4.1 相关子查询执行流程

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

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

在这里插入图片描述
在这里插入图片描述

说明：子查询中使用主查询中的列

4.2 代码示例

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

方式一：相关子查询

在这里插入图片描述

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

SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

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

在ORDER BY 中使用子查询：

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

SELECT employee_id,salary
FROM employees e
ORDER BY (
	  SELECT department_name
	  FROM departments d
	  WHERE e.`department_id` = d.`department_id`
	);

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

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

4.3 EXISTS 与 NOT EXISTS关键字

  • 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行：
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • 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 e2
                 WHERE  e2.manager_id = 
                        e1.employee_id);

方式二：自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM   employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

方式三：

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
		     SELECT DISTINCT manager_id
		     FROM employees
		     
		     );

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

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM   employees
                  WHERE  department_id = d.department_id);

在这里插入图片描述

4.4 相关更新

UPDATE table1 alias1
SET    column = (SELECT expression
                 FROM   table2 alias2
                 WHERE  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 d
	                       WHERE  e.department_id = d.department_id);

4.4 相关删除

 DELETE FROM table1 alias1
 WHERE column operator (SELECT expression
                        FROM   table2 alias2
                        WHERE  alias1.column = alias2.column);

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

题目：删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in  
           (SELECT employee_id
            FROM   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 salary
		FROM employees
		WHERE last_name = 'Abel'
		);

**问题：**以上两种方式有好坏之分吗？

**解答：**自连接方式好！

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

MySQL基础学习篇

mysql

笔记

数据库

Mysql基础篇笔记-第09章_子查询 的相关文章

  • Hibernate 可以使用 MySQL 的“ON DUPLICATE KEY UPDATE”语法吗？

    MySQL 支持 INSERT ON DUPLICATE KEY UPDATE 语法允许您 盲目 插入数据库 并回退到更新现有记录 如果存在 当您想要快速事务隔离并且想要更新的值取决于数据库中已有的值时 这非常有用 作为一个人为的示例 假设

  • 如何在不超时的情况下解析大型 CSV 文件？

    我正在尝试解析 50 MB 的 csv 文件 文件本身很好 但我正在尝试解决所涉及的大量超时问题 每个设置上传明智 我可以轻松上传并重新打开文件 但浏览器超时后 我收到 500 内部错误 我的猜测是我可以将文件保存到服务器上 打开它并保留我

  • MySQL有两个不同的密码？

    我确信它们是不同事物的密码 但我不确定是什么 当在终端连接到 MySQL 时 我输入 usr LOCAL mysql BIN mysql h host u username p然后系统会提示我输入密码 密码是 但是当使用 PHP 连接到 M

  • MYSQL中收盘价的简单移动平均线计算和更新表

    我可以使用一些帮助 最好是虚拟指南 来更新下表 CREATE TABLE SYMBOL day date NOT NULL open decimal 8 3 DEFAULT NULL high decimal 8 3 DEFAULT NUL

  • MySQL 按主键排序

    某些 SQL 服务器允许使用通用语句 例如ORDER BY PRIMARY KEY 我不相信这适用于 MySQL 是否有任何此类解决方法可以允许跨多个表自动选择 或者是否需要查找查询来确定主键 我一直在研究的解决方法包括调用SHOW COL

  • mysql语句中的*星号是什么意思？

    Ex mysql query SELECT FROM members WHERE id id 这意味着选择表中的所有列

  • MySQL 服务器未启动

    当我做 mysql u root p并输入my password这就是我得到的 错误 2002 HY000 无法通过套接字 var run mysqld mysqld sock 连接到本地 MySQL 服务器 2 所以我输入 systemc

  • PHP PDO相关：更新SQL语句未更新数据库内容

    我正在尝试使用准备好的语句来实现更新语句PHP http en wikipedia org wiki PHP脚本 但它似乎没有更新数据库中的记录 我不确定为什么 所以如果您能分享一些见解 我将不胜感激 Code query UPDATE D

  • 使用按位函数查询 BIT 字段时，MySQL 不使用索引

    我的 MySQL 表中有一个 BIT 类型的字段 我想使用位值存储记录的状态 例如 1 status1 2 status2 4 status3 8 status4 每条记录可以同时具有多种状态 对于 status1 和 status3 该值

  • PHP 选择后立即删除

    我有一个 PHP 服务器脚本 它从 MySQL 数据库中选择一些数据 一旦我将 mysql query 和 mysql fetch assoc 的结果存储在我自己的局部变量中 我就想删除我刚刚选择的行 这种方法的问题在于 PHP 似乎对我的

  • 将记录分成两列

    我的数据库中有一个 学生 表 其中包含大约 5 000 条记录 我想将这些记录显示在two分区 如何在不执行查询两次的情况下做到这一点 仅使用单个查询 显示示例http www freeimagehosting net uploads f1

  • MYSQL - 查找最近的前一天

    我可以以某种方式 不使用 PHP 找到一周中最近的前一天日期吗 Like 最近的上一个星期二的日期是哪一天 CURDATE INTERVAL WEEKDAY CURDATE wday IF WEEKDAY CURDATE gt wday 0

  • java mysql 准备好的语句

    我正在尝试使用 java 向数据库中进行简单的插入 它告诉我我的 sql 语法已关闭 但是 当我复制打印出来的字符串并将其放入 phpmyadmin 中的 sql 命令中时 它会正确执行该命令 并且我似乎无法弄清楚 java 中的字符串查询

  • 如何使用 MySQL 的 LOAD DATA LOCAL INFILE 在导入 CSV 时将字符串日期更改为 MySQL 日期格式

    我正在使用 MySQL 的 LOAD DATA LOCAL INFILE SQL 语句将数据从 CSV 文件加载到现有数据库表中 下面是一个 SQL 语句示例 LOAD DATA LOCAL INFILE file csv INTO TAB

  • 如何在 Laravel 查询中使用多个 OR,AND 条件

    我需要 Laravel 查询帮助 我的自定义查询 返回正确结果 Select FROM events WHERE status 0 AND type public or type private 如何写这个查询Laravel Event w

  • 如何在Mysql中仅将不同的值从一个表复制到另一个表？

    我有一个大约 2 5GB 的 MySql 数据库 表 A 具有以下列 anoid query date item rank url 我刚刚创建了另一个仅包含列的表 b query and date 我想在查询列中插入所有不同的记录 及其各自

  • 为什么我的 if 语句没有按我预期的方式工作？

    我正在尝试实现以下目标 我向我的 SQL 数据库询问使用SELECT FROM subjects 这样做之后我要求使用数组mysqli fetch assoc 在那之前一切都很好 现在的问题是 当我尝试在每个循环中修改 genero 的值

  • Mysql 中 UNION 子句的替代方案

    我有两张桌子 表 a 表 b table a ID 1 2 3 4 5 7 table b ID 2 3 4 5 6 我必须得到这样的输出而无需UNION命令 ID 1 2 3 4 5 6 7 注意 我有一个联合解决方案 select fr

  • 在 MySQL 中使用 COUNT 时如何返回 0 而不是 null

    我使用此查询返回存储在 sTable 中的歌曲列表以及存储在 sTable2 中的总项目数 SQL queries Get data to display sQuery SELECT SQL CALC FOUND ROWS str repl

  • MySQL 查询计算上个月

    我想计算上个月的订单总额 我收到了从当前日期获取当月数据的查询 SELECT SUM goods total AS Total Amount FROM orders WHERE order placed date gt date sub c

随机推荐

  • iscsi多路径配置方式

    学习一个服务的过程 1 此服务器的概述 名字 功能 特点 端口号 2 安装 3 配置文件的位置 4 服务启动关闭脚本 查看端口 5 此服务的使用方法 6 修改配置文件 实战举例 7 排错 从下到上 从内到外 内容 实战 配置IP SAN多路

  • Linux升级命令yum update

    Linux升级命令有两个分别是yum upgrade和yum update 这个两个命令是有区别的 代码如下 yum y update 升级所有包同时也升级软件和系统内核 代码如下 yum y upgrade 只升级所有包 不升级软件和系统

  • Memcach基础使用

    memcache 基础课程 使用场景 memcache 服务器端的安装 推荐使用memcached memcached是memchache的升级版本 sudo su apt get install memcached usr bin mem

  • 02-12306验证码预处理（分割、转存dat、解析dat文件）

    import cv2 as cv import numpy as np import os import binascii temp path r F python StockAnalyzer test test avi img path

  • 决策分类树算法之ID3，C4.5算法系列

    一 引言 在最开始的时候 我本来准备学习的是C4 5算法 后来发现C4 5算法的核心还是ID3算法 所以又辗转回到学习ID3算法了 因为C4 5是他的一个改进 至于是什么改进 在后面的描述中我会提到 二 ID3算法 ID3算法是一种分类决策

  • 从TCP协议的原理来谈谈rst复位攻击

    在谈RST攻击前 必须先了解TCP 如何通过三次握手建立TCP连接 四次握手怎样把全双工的连接关闭掉 滑动窗口是怎么传输数据的 TCP的flag标志位里RST在哪些情况下出现 下面我会画一些尽量简化的图来表达清楚上述几点 之后再了解下RST

  • svn软件常用命令

    下载代码 命令 svn co 代码路径 查看工程中被修改的文件的内容 命令 svn diff 查看工程中文件的状态 命令 svn status 备注 状态是 M 就是被修改过 M是modify的缩写 回退被修改的文件 命令 svn reve

  • 2020中科院sci分区查询_查询SCI分区有几种方法

    查询SCI分区有几种方法 SCI分区目前有两种方法和标准 一个是中科院分区 一个是JCR分区 SCI期刊的分区有着重要意义 SCI期刊的影响因子都是浮动变化的 如果以一个影响因子的固定值来区分期刊是不合理的 不同领域内的期刊影响因子也没有可

  • Java单链表反转 详细过程

    https blog csdn net guyuealian article details 51119499 一 单链表的结点结构 data域 存储数据元素信息的域称为数据域 next域 存储直接后继位置的域称为指针域 它是存放结点的直接

  • Microsoft Dynamics CRM 安装注意事项(请朋友们补充)

    最近安装Microsoft Dynamics CRM 遇到的了很多烦人的小问题 特此记录下需要注意事项 仅供参考 服务器 Windows Server 2012 R2 Datacenter 安装及顺序 IIS gt SQLServer gt

  • 高质量、高并发的实时通信架构设计与探索

    中国互联网络信息中心 CNNIC 近日发布的第 47 次 中国互联网络发展状况统计报告 显示 截至 2020 年 12 月 我国网民规模达 9 89 亿 随着社会信息化水平持续提升及电子设备加速普及 手机网民规模持续增长 基本实现对全体网民

  • D - Robots Easy （脑残题）

    D Robots Easyhttps vjudge csgrandeur cn problem Gym 102267D 题意 对于给出的 12 12 的图 有 l 组查询 每组给出一个坐标 要求从这个坐标开始行走 遇到黑色或在边界不能走 直

  • AGPBI: {“kind“:“error“,“text“:“Program type already present: android.support.v4.os.ResultReceiver$1“

    使用环境 遇见 解决方法 第一步 object下的build gradle文件中build gradle版本号修改 第二步 object下的Gradle版本号修改 具体对应版本 没有一个固定的对应关系 取决于创建项目时创建者当时的AS环境

  • cpp: Observer Pattern

    Gold h 此文件包含 Gold 类 Observer Pattern 观察者模式 C 14 Jewelry Observer Pattern 观察者模式 2023年5月10日 涂聚文 Geovin Du Visual Studio 20

  • 数据结构 严薇敏 顺序表的实现(增 删 改)及其使用方法详解

    时间复杂度 数据结构 时间复杂度和空间复杂度 目录 1 线性表 2 顺序表 2 1概念及结构 2 2 接口实现 SeqList h SeqList c 2 2 1初始化链表以及销毁链表的实现 初始化顺序表 销毁顺序表 2 2 2查找元素前驱

  • 如何使用FolderSync在Android和PC之间同步文件

    On the desktop apps like Dropbox and Google Drive sync folders between your devices But on your phone it only gives you

  • Windows下命令行及Java+Tesseract-OCR对图像进行（字母+数字+中文）识别，亲测可行

    Windows下Java Tesseract OCR对图像进行字符识别 亲测可行 1 下载tesseract ocr 中文语言包并安装 2 命令行对图片进行识别及效果图 3 Java调用Tesseart OCR 3 1 效果图 3 2 源码

  • SlideLive：免费的PPT模板网站

    简介 在平时的学习和工作中 我们经常需要使用到PPT模板 包括课程报告 团体活动等 本文给大家介绍一款免费的PPT模板的网站 SlideLive 官网地址 https www slidelive cn SlideLive

  • 完美解决Echarts X坐标轴下方文字最后一个字体加粗颜色加深的问题

    之前用Echarts画图的时候 X坐标轴最后一个字存在自动加粗的问题 也是在网上找过解决办法没有找到 后面自己研究明白了后 在某篇文章下评论了如何解决 但是好像大家没有看评论的习惯 所以单独拿出来一篇文章 希望能给大家解决下问题 下面是有问

  • Mysql基础篇笔记-第09章_子查询

    仅作个人笔记使用 本篇的学习来源是 尚硅谷Mysql基础教程 宋红康 教学视频来自B站 MySQL数据库教程天花板 mysql安装到mysql高级 强 硬 感谢老师的教学 第09章 子查询 子查询指一个查询语句嵌套在另一个查询语句内部的查询

热门标签