十、mysql子查询

最新推荐文章于 2025-07-09 11:58:30 发布

转载最新推荐文章于 2025-07-09 11:58:30 发布 · 1.1k 阅读

8 ·

CC 4.0 BY-SA版权

原文链接：https://mp.youkuaiyun.com

文章标签：

#mysql子查询

mysql 专栏收录该内容

24 篇文章

订阅专栏

MySQL子查询是嵌套在另一个查询(如SELECT，INSERT，UPDATE或DELETE)中的查询。MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在使用表达式的任何地方使用，并且必须在括号中关闭。
如果一个查询语句需要用到多张表，可以在一个子查询中继续嵌套另一个子查询。
嵌套查询的工作方式是：先处理内查询，由内向外处理；外层查询利用内层查询的结果。
嵌套查询不仅仅可以用于父查询select语句使用，还可以用于insert、update、delete语句或其他子查询中。
eg：INSERT INTO table1(column1,column2,column3) SELECT value_column1,value_column2,value_column23 FROM table2；

`一、数据准备`

准备两张表，一张存储学生信息，一张存储学生成绩
表1 学生信息表：

CREATE TABLE student_info(
  number INT PRIMARY KEY COMMENT '学号',
  NAME VARCHAR(20) COMMENT '姓名',
  sex ENUM('男','女'), 
  id_number CHAR(18) COMMENT '身份证号',
  department VARCHAR(30) COMMENT '学院',
  major VARCHAR(30) COMMENT '专业',
  enrollment_time DATE COMMENT '入学时间',
 UNIQUE KEY(id_number) 
);

INSERT INTO student_info VALUES(20180101,'杜子腾','男','158177199010447921','计算机学院','计算机科学与工程','2018-09-01'),
(20180102,'杜琦燕','女','158177199011785921','计算机学院','计算机科学与工程','2018-09-01'),
(20180103,'范统','男','178177199011169590','计算机学院','软件工程','2018-09-01'),
(20180104,'史珍香','女','168477199111169602','计算机学院','软件工程','2018-09-01'),
(20180105,'范剑','男','189177199102169893','航天学院','飞行器设计','2018-09-01'),
(20180106,'朱意群','男','197777199012169785','航天学院','电子信息','2018-09-01');

在这里插入图片描述

表2 学生成绩表

CREATE TABLE student_score(
  number INT COMMENT '学号',
  SUBJECT VARCHAR(30) COMMENT '学科',
  score TINYINT COMMENT '分数',
  PRIMARY KEY(number,SUBJECT),
  CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

INSERT INTO student_score VALUES(20180101,'母猪的产后护理',78),
(20180101,'资本论',88),
(20180102,'母猪的产后护理',100),
(20180102,'资本论',98),
(20180103,'母猪的产后护理',59),
(20180103,'资本论',61),
(20180104,'母猪的产后护理',55),
(20180104,'资本论',46),
(20180105,'母猪的产后护理',85),
(20180105,'资本论',90),
(20180106,'母猪的产后护理',66),
(20180106,'资本论',75);

在这里插入图片描述
`

`二、子查询的分类`

按照期望值的数量可以将子查询分为：标量子查询、多值子查询

按照对外部查询的依赖可以将子查询分为：独立子查询、相关子查询

它们之间的关系是标量子查询和多值子查询可以是独立子查询也可以是相关子查询

标量子查询返回的是单个值，若标量子查询返回多个值，则MySQL会抛出错误提示“该子查询返回多行数据”，可以使用limit 1来保证只有一条结果；多值子查询返回的是多值集合。

独立子查询是不依赖于外部查询而运行的子查询。怎么理解呢，就是这个子查询可以单独运行，而不需要和外部查询有任何交互。

MySQL对于in子句的优化，如果不是显式的列表定义，如in (1,2,3,4,5)，那么in子句一般都会被转换为exists子句，这一过程就是把独立子查询转换为了相关子查询。

相关子查询是指引用了外部查询列的子查询，例如下述这个SQL，

select id_temp from t_user_collect uc where exists (select * from t_commodity c where c.id=uc.commodity_id);

子查询中的“c.id=uc.commodity_id”就是引用了外部查询的commodity_id列，通过explain可以看到这个子查询的select_type是dependent subquery（相关子查询），

`三、标量子查询`

SELECT * FROM student_score WHERE number=(SELECT number FROM student_info WHERE NAME=‘杜琦燕’);
在这里插入图片描述
小括号中的查询语句也被称为子查询或者内层查询，使用子查询的结果作为搜索条件的查询称为外层查询。如果你在一个查询语句中需要用到更多的表的话，那么在一个子查询中可以继续嵌套另一个子查询，在执行查询时，将按照从内到外的顺序依次执行这些查询。

在这个例子中的子查询的结果只有一个值(也就是’杜琦燕’的学号)，这种子查询称之为标量子查询。正因为标量子查询单纯的代表一个值，所以它可以作为表达式的操作数来参与运算，它除了用在外层查询的搜索条件以外，也可以作为查询对象放在查询列表处，比如这样：

mysql> SELECT (SELECT number FROM student_info WHERE name = '杜琦燕') AS 学号;
+----------+
| 学号     |
+----------+
| 20180102 |
+----------+
1 row in set (0.00 sec)
mysql>

因为标量子查询的结果只有一个值，所以外层查询的搜索条件想怎么写就怎么写喽，只要符合布尔表达式的语法就可以，比方说我们来查询学号大于’杜琦燕’的学号的学生成绩，可以这么写：
SELECT * FROM student_score WHERE number>(SELECT number FROM student_info WHERE NAME=‘杜琦燕’);
在这里插入图片描述

`四、多值子查询`

有时候子查询的结果并不是单纯的一个值，而是多个值，称为多值子查询。

`4、1 单列子查询`

【关键字IN】
查询‘计算机科学与工程’专业的学生的成绩
SELECT * FROM student_score WHERE number IN(SELECT number FROM student_info WHERE major=‘计算机科学与工程’);
在这里插入图片描述

【关键字 NOT IN】
关键字 NOT IN和IN的使用类似，就不再赘述。
【关键字ANY】
any关键字必须与一个比较操作符一起使用，它的意思是“与子查询中返回列的数值进行逐一对比，只要其中一个数值比较为true，则返回true”，我们来看一个查询实例
SELECT NAME FROM STUDENT_INFO WHERE number> ANY (SELECT number FROM student_score);
在这里插入图片描述
首先我们已经明确了any关键字的含义，STUDENT_INFO 表中number列会与student_score表中number列的数值进行逐一对比，只要number大于student_score表中的一个number，则STUDENT_INFO 中number列值所在行的name列值就会被查询出来。

in关键字的含义是“只返回包含这些值的记录”，它等价于“= any”，因此以下两个SQL是等价的：

select id_temp from t_user_collect where commodity_id = any (select id from t_commodity);

select id_temp from t_user_collect where commodity_id in (select id from t_commodity);

为什么说“in”和“= any”是等价的呢，我们来分析一下，“= any”的含义是“只要等于指定值中的任何一个值就返回true”。

some关键字和any关键字是等价的，之所以在有些条件下使用some，是为了表达“有部分不相等”的意思，因此以下SQL是等价的，
SELECT NAME FROM STUDENT_INFO WHERE number<> ANY (SELECT number FROM student_score);
SELECT NAME FROM STUDENT_INFO WHERE number<> some (SELECT number FROM student_score);
在这里插入图片描述

这里的“<> any”含义是“只要不等于指定值中的任何一个值就返回true”。

这里的“<> some”含义是“只要不等于指定值中的部分值就返回true”。

【关键字ALL】
all关键字，也需要与比较操作符一起使用，其含义是“与子查询返回列中的值进行逐一对比，所有比较都为true，才返回true”，来看一个实例，

SELECT NAME FROM STUDENT_INFO WHERE number> ALL (SELECT number FROM student_score);
在这里插入图片描述
结果为Null
当我们对all使用<>符号时，是想得到那些外部查询中未包含在子查询中的数据，来看一个实例，
<>和！=等价，不等于
其实<> all还有一个别名，就是not in，后者明显更易于理解，即不包含、不在里面的意思，因此以下两个SQL是等价的，

SELECT NAME FROM STUDENT_INFO WHERE number<> ALL (SELECT number FROM student_score);
SELECT NAME FROM STUDENT_INFO WHERE number not in (SELECT number FROM student_score);
在这里插入图片描述
因此查询出来的结果为Null。

`4、2 多列子查询`

上边例子中出现的子查询的结果集里只有一个列，其实子查询的结果集也可以有多个列的，只不过在子查询的查询列表和外层查询WHERE子句的搜索条件要匹配！比如我们可以这么写：
SELECT * FROM student_score WHERE (number,SUBJECT) IN(SELECT number, ‘母猪的产后护理’ FROM student_info WHERE major=‘计算机科学与工程’);
在这里插入图片描述
在这个例子中的子查询的查询列表是number, ‘母猪的产后护理’，number是列名，'母猪的产后护理’是一个常数，所以在外层查询的WHERE子句的搜索条件里也需要两个表达式，例子中使用的是(number, subject)。需要注意的是，WHERE子句的搜索条件中多个表达式需要用小括号()括住。

`五、EXISTS和NOT EXISTS子查询`

有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果是不是为空。
EXISTS: 示例EXISTS(selcect …) 当子查询有结果时表达式为真
NOT EXISTS:示例 NOT EXISTS(selcect …) 当子查询没有结果时表达式为真

SELECT * FROM student_score WHERE EXISTS(SELECT * FROM student_info WHERE number=20180108);
在这里插入图片描述
这个例子的子查询的意思是在student_info表中查找学号为20180108的学生信息，很显然并没有学号为20180108的学生，所以子查询没有结果，EXISTS表达式的结果为FALSE，所以外层查询也就不查了，直接返回了一个Empty set，表示没有结果。
SELECT * FROM student_score WHERE NOT EXISTS(SELECT * FROM student_info WHERE number=20180108);
在这里插入图片描述

`六、相关子查询`

前边介绍的子查询和外层查询都没有依赖关系，也就是说可以独立运行完子查询得到结果之后，再拿结果作为外层查询的搜索条件去执行外层查询，这种子查询称为不相关子查询或独立子查询，比如下边这个查询：
SELECT * FROM student_score WHERE number=(SELECT number FROM student_info WHERE NAME=‘杜琦燕’);
在这里插入图片描述
子查询中只用到了student_info表而没有使用到student_score表，这就是一种典型的不相关子查询。

而有时候我们需要在子查询的语句中引用到外层查询的值，这样的话子查询就不能当作一个独立的语句去执行，这种子查询方式称为相关子查询。比方说我们想查看一些学生的基本信息，但是前提是这些学生有成绩记录，那可以这么写：
SELECT number,id_number,major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_score.number=student_info.number);
在这里插入图片描述
首先需要注意的是，student_info和student_score表里都有number列，所以在子查询的WHERE语句中书写number = number会造成二义性，也就是让服务器懵逼，不知道这个number列到底是哪个表的，所以为了区分，在列名前边加上了表名，并用点.连接起来，这种显式的将列所属的表名书写出来的名称称为该列的全限定名。所以上边子查询的WHERE语句中用了列的全限定名：student_score.number = student_info.number。

相关子查询的处理逻辑：内部查询的执行依赖于外部查询的数据，外部查询每执行一次，内部查询也会执行一次。每一次都是外部查询先执行，取出外部查询表中的一个元组，将当前元组中的数据传递给内部查询，然后执行内部查询。根据内部查询执行的结果，判断当前元组是否满足外部查询中的where条件，若满足则当前元组是符合要求的记录，否则不符合要求。然后，外部查询继续取出下一个元组数据，执行上述的操作，直到全部元组均被处理完毕。

这条查询语句可以分成这么两部分来理解
我们要查询学生的一些基本信息。
这些学生必须符合这样的条件：必须在student_score表中有记录。
所以这个例子中的相关子查询的查询过程是这样的：

先执行外层查询获得到student_info表的第一条记录，发现它的number值是20180101。把20180101当作参数传入到它的子查询，此时子查询判断student_score表的number字段是否有20180101这个值存在，子查询的结果是该值存在，所以整个EXISTS表达式的值为TRUE，那么student_info表的第一条记录可以被加入到结果集。

再执行外层查询获得到student_info表的第二条记录，发现它的number值是20180102，与上边的步骤相同，student_info表的第二条记录也可以被加入到结果集。

与上边类似，student_info表的第三条记录也可以被加入到结果集。

与上边类似，student_info表的第四条记录也可以被加入到结果集。

…
student_info表没有更多的记录了，结束查询。

所以最后的查询结果是上边的6条记录。如果你觉得相关子查询还是有点儿绕的话，那就返回去再重新看几遍这个查询过程。
需要注意的是，相关子查询通常都用在EXISTS和NOT EXISTS子查询里，用于匹配在某个表里查找在另一个表里有匹配行或者没有匹配行的行。但是也没有限制只能使用EXISTS和NOT EXISTS，还可以如下方式使用：
select * from score as a where a.score>(select avg(b.score) from score as b where a.cou_id=b.cou_id);

`七、对同一个表的子查询`

其实不只是在查询多表的时候会用到子查询，在单个表中有时也会用到子查询。比方说我们想看看在student_score表的’母猪的产后护理’这门课的成绩中，有哪些超过了平均分的记录，脑子中第一印象是这么写：
SELECT * FROM student_score WHERE subject = ‘母猪的产后护理’ AND score > AVG(score);
ERROR 1111 (HY000): Invalid use of group function
需要特别特别特别注意的是：聚合函数不能放到WHERE子句中！！！因为WHERE子句是针对每一条记录来进行过滤的，而聚集函数只能用于过滤分组。所以如果需要实现上边的需求，就需要搞一个student_score表的副本，相当于两个student_score表，在一个表上用聚合函数统计，统计完后使用统计结果到另一个表中进行过滤
SELECT * FROM student_score WHERE SUBJECT = ‘母猪的产后护理’ AND score > (SELECT AVG(score) FROM student_score WHERE SUBJECT = ‘母猪的产后护理’ );
在这里插入图片描述