MySQL中几种常见的嵌套查询详解

吾爱主题阅读：154 2024-04-01 23:20:53 评论：0

几种常见的嵌套查询——以学员成绩为例

嵌套查询，也称为子查询，是实际工作中经常用到的一种查询方式。子查询其实就是在已有的查询语句中的where后面再嵌套一层查询语句，也就是把内层查询结果当做外层查询参照的数据表来使用。

在工作中，经常会遇见4种子查询，即含有比较运算符（>、>=、<、<=、=、!=）、IN关键词、ANY/ALL关键词以及EXISTS关键词的嵌套查询。下面我们以学员考试成绩为例，来学习一下这四种子查询的应用。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 # 创建学员信息表 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 2 3 4 # 1.查询非管理系中比管理系任意一个学员年龄小的学员信息 SELECT * FROM stu_info WHERE age < ANY ( SELECT DISTINCT age FROM stu_info WHERE department = '管理系' ) AND department != '管理系' ;

这里的查询逻辑是这样的：首先查询管理系中学生的年龄（去重），得到的结果是22和24；然后查询出非管理系中年龄比22或24年龄小的学生信息（也就是年龄小于24的非管理系学生信息）。

1 2 3 4 # 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 2 3 4 5 6 7 8 # 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）。

1 2 3 4 # 查询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

1 2 3 4 5 6 7 8 9 10 # 查询数学系和计算机系之外的学员信息 # 方法一 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 条件不成立。 # 当子查询和主查询有关联条件时，相当于从主查询中去掉子查询的数据。