MySQL中几种常见的嵌套查询详解
作者：Grateful_Dead424 发布时间：2024-01-19 07:48:40
几种常见的嵌套查询——以学员成绩为例
嵌套查询，也称为子查询，是实际工作中经常用到的一种查询方式。子查询其实就是在已有的查询语句中的where后面再嵌套一层查询语句，也就是把内层查询结果当做外层查询参照的数据表来使用。
在工作中，经常会遇见4种子查询，即含有比较运算符（>、>=、<、<=、=、!=）、IN关键词、ANY/ALL关键词以及EXISTS关键词的嵌套查询。下面我们以学员考试成绩为例，来学习一下这四种子查询的应用。
# 创建学员信息表
CREATE TABLE stu_info
(
id INT AUTO_INCREMENT PRIMARY KEY,
iname VARCHAR(20),
gender CHAR(1),
department VARCHAR(10),
age TINYINT,
province VARCHAR(10),
email VARCHAR(50),
mobilephone CHAR(11)
);
# 向学员表中插入数据
INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone) VALUES
('张勇','男','数学系',23,'河南','sfddf123dd@163.com','13323564321'),
('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'),
('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'),
('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'),
('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'),
('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'),
('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'),
('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'),
('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'),
('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','13827811311');
# 创建学员成绩表
CREATE TABLE stu_score( id INT , Excel TINYINT, Tableau TINYINT, MySQL TINYINT );
# 向成绩表中插入数据
INSERT INTO stu_score VALUES
(1,87,72,88),
(3,90,66,72),
(2,90,70,86),
(4,88,82,76),
(8,92,67,80),
(10,88,82,89),
(5,79,66,60),
(7,91,78,90),
(6,82,79,88),
(9,85,70,85);
# 1.查询年龄超过所有学员平均年龄的学员信息
SELECT * FROM stu_info
WHERE age >= avg(age);
#需要注意的是Where后面不能使用聚合函数
#应该修改成
SELECT AVG(age) FROM stu_info;
SELECT * FROM stu_info
WHERE age>=23.3
#二合一
# 1.查询年龄超过所有学员平均年龄的学员信息
SELECT * FROM stu_info
WHERE age >= (SELECT AVG(age) FROM stu_info);
# 2.查询年龄不低于所属系平均年龄的学员信息
SELECT * FROM stu_info AS s1
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2
WHERE s1.department = s2.department);
使用含比较运算符的嵌套查询时，需要注意，比较运算符后面的子查询只能返回一个结果。
含ANY或ALL关键词的嵌套查询
对于含比较运算符的嵌套查询来说，嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值，就需要用到ANY或者ALL关键词了。通常，ANY / ALL 关键词经常和比较运算符连用，下面是6种比较运算符与ANY / ALL 关键词的搭配结果：
# 1.查询非管理系中比管理系任意一个学员年龄小的学员信息
SELECT * FROM stu_info
WHERE age < ANY(SELECT DISTINCT age FROM stu_info WHERE department = '管理系')
AND department != '管理系';
这里的查询逻辑是这样的：首先查询管理系中学生的年龄（去重），得到的结果是22和24；然后查询出非管理系中年龄比22或24年龄小的学生信息（也就是年龄小于24的非管理系学生信息）。
# 2.查询非管理系中比管理系所有学员年龄大的学员信息
SELECT * FROM stu_info
WHERE age > ALL (SELECT DISTINCT age FROM stu_info WHERE department = '管理系')
AND department != '管理系';
这里的查询逻辑是这样的：首先查询管理系中学生的年龄（去重），得到的结果是22和24；然后查询出非管理系中年龄比22和24都大的学生信息（也就是年龄大于24的非管理系学生信息）。
含IN关键词的嵌套查询
当查询条件涉及某些已知的可枚举离散值的时候，我们就可以选择IN关键词来完成数据的提取。IN关键词有两种用法：
将可枚举的离散值直接写在值列表中
当离散值是基于其他表的筛选结果时，就可以使用嵌套查询，即把另一个表的查询语句块写在IN关键词后面的括号里。
# 1.查询数学系和计算机系的学员信息
SELECT * FROM stu_info WHERE department IN('数学系','计算机系');
# 2.查询与张勇、刘伟同一个系的学员信息
SELECT * FROM stu_info
WHERE department IN (SELECT department FROM stu_info WHERE iname IN('张勇','刘伟'));
# 3.查询MySQL成绩大于85分的学员信息
SELECT * FROM stu_info
WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);
需要注意的是，在使用IN关键词的嵌套查询的时候，嵌套部分只能返回一个字段的信息（比如上面的department字段或者id字段），如果返回两个及以上字段信息则会出现语法错误。
含EXISTS关键词的嵌套查询
EXISTS 关键词的作用和 IN关键词非常类似，不同的是，通过EXISTS关键词的嵌套查询返回的不是具体的值集合，而是满足条件的逻辑值（也就是True / False）。也就是说，EXISTS的作用就是“判断是否存在满足某种条件的记录”，如果存在这样的记录就返回真（True），如果不存在这样的记录就返回假（False）。
# 查询MySQL成绩大于85分的学员信息
SELECT * FROM stu_info
WHERE EXISTS
(SELECT * FROM stu_score WHERE stu_score.id = stu_info.id AND MySQL > 85);
需要注意的是，使用EXISTS关键词的嵌套语句 WHERE与EXISTS关键词之间没有任何参数，这是因为EXISTS只需要一个参数，通常是在EXISTS右侧加一个子查询语句。此外，EXISTS后面的子查询中SELECT后面可以写表中任何一个字段或者星号或者一个常数，因为EXISTS后面的子查询只关心是否存在满足条件的记录。下面返回的结果都是一样：
【补充】关于IN和EXISTS两个关键词还有两个延伸关键词NOT IN和NOT EXISTS
# 查询数学系和计算机系之外的学员信息
# 方法一
SELECT * FROM stu_info
WHERE department NOT IN('数学系','计算机系');
#方法二
SELECT * FROM stu_info
WHERE NOT EXISTS(SELECT * FROM stu_score WHERE department IN('数学系','计算机系') and stu_score.id = stu_info.id);
# not exists的逻辑比较复杂，需要大家慢慢领会
# 主要看not exists括号中的sql语句是否有结果，无结果：才会继续执行where条件；有结果：视为 where条件不成立。
# 当子查询和主查询有关联条件时，相当于从主查询中去掉子查询的数据。
对于IN和EXISTS两个关键词，大多数情况下都可以相互替换，主要差别是使用效率问题，通常情况下采用EXISTS要比IN效率要高，但也要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
以上我们只是了解了where语句后面的子查询，除此之外，子查询还可以放在select语句、from语句、having语句后面。
附：其他使用方法和注意
除了上面这些还有很多很多，不过就不去细讲了，因为这些跟别的数据库差不多，只是为了给大家一个参考，提提就够了。
SELECT (SELECT s1 FROM t2) FROM t1;
SELECT (SELECT s2 FROM t1);
支持子查询的语法有：SELECT，INSERT，UPDATE，DELETE，SET和DO。
子查询可以使用任何普通查询中使用的关键词：如DINSTINCT，GROUP BY，LIMIT，ORDER BY，UNION，ALL，UNION ALL等。可以使用<,>, <=, >=, =, <>运算符进行比较，也可以使用ANY ，IN和SOME进行集合的匹配。
来源：https://blog.csdn.net/Grateful_Dead424/article/details/122826542
0
投稿猜你喜欢
最近接到一个任务，就是用django后端，前段用vue，做一个普通的简单系统，我就是一搞后端的，听到vue也是比较震惊，之前压根没接触过vu- REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具
- Python list列表添加元素方法实际开发中，经常需要对 Python 列表进行更新，包括向列表中添加元素、修改表中元素以及删除元素。&
- 指令和程序计算机的硬件系统通常由五大部件构成，包括：运算器、控制器、存储器、输入设备和输出设备。其中，运算器和控制器放在一起就是我们通常所说
- 层次化索引是pandas的一项重要功能，它使你能在一个轴上拥有多个（两个以上）索引级别。创建一个Series，并用一个由列表或数组组成的列表
- Yii2 框架 之所以称之为高效快速开发的一款框架，是因为有一个神奇的工具Gii用过Yii1框架的Coder都知道，Gii可以为你快速生成代
- 由于研究关系需要自己手动给卷积层初始化权值，但是好像博客上提到的相关文章比较少（大部分都只提到使用nn.init里的按照一定分布初始化方法）
- 我就废话不多说了，大家还是直接看代码吧~func ReadLine(fileName string) ([]string,error){f,
- 本文实例为大家分享了python实现简易动态时钟的具体代码，供大家参考，具体内容如下from turtle import *from dat
- 1.字符串转二进制数组 string content="这是做个测试！"; System.Text.UnicodeEnc
- 前几天和一个小伙伴交流了一下nodejs中epoll和处理请求的一些知识，今天简单来聊一下nodejs处理请求的逻辑。我们从listen函数
- CSRF全拼为Cross Site Request Forgery，译为跨站请求伪造。CSRF指攻击者盗用了你的身份，以你的名义发送恶意请求
- scikit-learn是python的第三方机器学习库，里面集成了大量机器学习的常用方法。例如：贝叶斯，svm，knn等。scikit-l
- DELETE 语句DELETE 语句用于删除记录，语法如下：(与 “UPDATE” 语法较为相似)D
- Django1.8.2中文文档：Django1.8.2中文文档上传图片配置上传文件保存目录1)新建上传文件保存目录。2)配置上传文件保存目录
- 前言近几年，制造业作为国民经济主体，是国家创造力、竞争力和综合国力的重要体现。作为制造强国建设的主攻方向，可以说，智能制造发展水平关乎我国未
- 前面的深入理解Routing章节，我们讲到了在MVC中，除了使用默认的ASP.NET 5的路由注册方式，还可以使用基于Attribute的特
- 创建临时表，往临时表插入数据的时候报的错误。一开始提示没有打开主键，后来打开主键就提示上述错误异常。从网上查找资料没有找到，然后又到群里问各
- 前言根据《2021年Stackoverflow开发者调查》，SQL是最常用的五种编程语言之一。所以，我们应该多投入时间来学习SQL。由Sto
- 前言前段时间因为需要处理一大堆验收单，都是一些简单的复制粘贴替换工作，于是就想到用python进行处理。接下来进入正题～实现需求我是用的开发
