命令行操作MySQL - 联合查询 之 子查询

这是命令行操作MySQL数据库系列博客的第二十一篇，今天这篇博客记录联合查询中的子查询。子查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。

---

前言

所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。

通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。

可以简单的写一下他的语句：
select 字段 from 表名 where 条件(select 字段 from 表名 where 条件);

简单来讲就是嵌套查询，一层嵌套着一层，但是建议最多嵌套一层就好，因为嵌套多了，可读性差，运行效率也变差了。

为下面例子做准备：
创建class表和student表，并插入数据：

# 创建class表
create table class(id int AUTO_INCREMENT PRIMARY KEY, class_name varchar(128), teacher varchar(128));
# 插入数据
insert into class values(101, 'C语言班', '红老师'), (102, 'C++班', '绿老师'), (103, 'qt班', '蓝老师');


# 创建student表
create table student(id int AUTO_INCREMENT PRIMARY KEY, student_name varchar(128), sex enum('M', 'F'), class_id int);
# 插入数据
insert into student values(1, '小黄', 'M', 101), (2, '小白', 'M', 103), (3, '小紫', 'F', 101), (4, '小黑', 'M', 103);

---

带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。

例如：查询student 表中“小黄”所在班级班主任的名字。SQL语句如下：

select teacher from class where id = (select class_id from student where student_name = '小黄');

注意：使用比较运算符时，select 子句获得的记录数不能大于1条!!!

例：试试子句获得记录大于1条的情况

select teacher from class where id = (select class_id from student where class_id < 103);

由报错信息可以看出，当返回超过一条数据时，查询就会不通过。

当然，返回0条数据却是可以的！

---

带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，NOT IN的用法与IN相同。

IN在此范围内，NOT IN不在此范围内。

例如：查询student 表中“小花”所在班级班主任的名字。

select teacher from class where id in (select class_id from student where student_name = '小黄');

一样可以进行查询出来，但是，这样查询使用in就是大材小用了，其实使用关系运算符就可以可以的了。

下面讲解另一个例子，更贴切与使用in：
查询姓名以“小”字开头的学生所在班级班主任的姓名
这里我们需要返回多条数据，这时使用关系运行符就达不到要求了。所以得使用in来完成，当然还得配合like模糊查询一起查询。

select teacher from class where id in (select class_id from student where student_name like '小%');

首先查询子语句，查询结果为101和103，然后在与主语句进行查询，最后查询出红老师和蓝老师。

---

带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

只要查询到有结果，EXISTS就返回true，没有就返回false。

例如：如果103班存在学生记录，就查询103班的班级信息的记录。

select * from class where id = 103 and EXISTS (select * from student where class_id = 103);

首先查询子语句，学生表中有字段为103的记录，那么EXISTS就返回true，紧接着查询班级表中id为103的记录，班级表中也有对应记录，最有将查询结果显示出来。

现在来查询学生表中没有的记录：
102班存在学生记录，就查询103班的班级信息的记录。

select * from class where id = 103 and EXISTS (select * from student where class_id = 102);

可以看到，子语句查询没有查询到结果，返回false，即使主语句查询有记录，也不符合查询条件，导致查询结果为空。

---

带关键字ANY的子查询

关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。

“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。

例如：
查询student表中的女生所在class表对应老师的记录

select * from class where id = ANY(select class_id from student where sex = 'F');

学生表中只有一名女生，且是在红老师所带的C语言班级中。

ANY查询出来的所有结果只要有一条符合条件，都可以被查下出来。

或者说，
查询student表中的class_id所在class表>=101的记录。这样就可以将全部记录查询出来。

select * from class where id >= ANY(select class_id from student);

因为子查询中将所有class_id都查询出来，然后主查询中，所有记录都符合条件，所以将class表的所有记录都查询出来。

---

带关键字ALL的子查询

关键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。

例如，“>ALL”表示大于所有值，“<ALL”表示小于所有值。

就是查询出来的结果要符合所有条件。

例：
查询班级表中的所有班级id在满足对应学生表的班级id。

select * from student where class_id >= ALL(select id from class);

为什么这里只显示class_id的两条记录呢，因为查询中我们使用了ALL。查询班级id中，查询到记录101，102，103；但是主查询语句中条件得符合>=101 而且 >= 102 而且 >= 103的记录，所以就只有103是符合的，就将class_id等于103的学生记录查询出来。（这就是得符合所有条件）

反过来也是一样的。

select * from student where class_id <= ALL(select id from class);

符合条件<= 101 而且 <= 102 而且 <= 103 的记录。

---

总结

联合查询子查询，在条件中再进行一次查询，将查询到的结果在于主语句查询条件进行匹配，进行查询出最终记录。子查询可以灵活多变，可以配合其他很多关键字一起查询。子查询中建议使用括号()括起来，可读性更强！