29.MySQL中的子查询

1.子查询

在一个SQL语句A（SELECT、INSERT、UPDATE等）中嵌入一个查询语句B，作为执行的条件或查询的数据源（代替FROM后的数据表），那么B就是子查询语句，它是一条完整的SELECT语句，能够独立执行。
在含有子查询的语句中，子查询必须书写在圆括号（）内。
SQL首先会执行子查询中的语句。然后再将返回的结果作为外层SQL语句的过滤条件。
当遇到一个SQL语句中含有多层子查询时，它们执行的顺序是从最里层的子查询开始执行。

2.子查询分类

子查询的划分方式有多种。
按子查询的功能可以分为标量子查询，列子查询、行子查询和表子查询。
按子查询出现的位置可以分为WHERE子查询和FROM子查询。
标量子查询、列子查询和行子查询都属于WHERE子查询。
表子查询属于FROM子查询。

3.准备数据

CREATE TABLE student(
    id INT(4) PRIMARY KEY,
    name VARCHAR(36),
    age INT(4)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO student VALUES (1,'student1',21);
INSERT INTO student VALUES (2,'student2',22);
INSERT INTO student VALUES (3,'student3',23);
INSERT INTO student VALUES (4,'student4',24);
INSERT INTO student VALUES (5,'student5',25);
INSERT INTO student VALUES (6,'student6',26);
INSERT INTO student VALUES (7,'student7',27);
INSERT INTO student VALUES (8,'student8',28);

4.标量子查询

子查询返回的结果是一个数据，即一行一列。
子查询利用比较运算符>、<、=或!=，判断子查询语句返回的数据是否与指定的条件相等会不等，然后根据比较结果完成相关需求的操作。

查询比student5年龄小的数据：

SELECT * FROM student WHERE age < (SELECT age FROM student WHERE name='student5');

5.列子查询

子查询返回的结果是一个字段符合条件的所有数据，即一列多行。
子查询利用比较运算函数IN()或NOT IN()。
判断指定的条件是否在子查询语句返回的结果集中。

查询年龄不是student5的年龄和student6年龄的数据：

SELECT * FROM student WHERE age NOT IN (SELECT age FROM student WHERE name='student5' OR name='student6'); 

6.行子查询

当子查询的结果是一条包含多个字段的记录（一行多列）时，称为行子查询。
行子查询返回的一条记录与指定的条件比较，比较的运算符通常使用=。
子查询的结果必须全部与指定的字段相等才满足WHERE指定的条件。

查询名字为student5的数据：

SELECT * FROM student WHERE (name,age) =
(SELECT name,age FROM student WHERE name='student5');
OR
 SELECT * FROM student WHERE (name,age) =(SELECT name,age FROM student WHERE name='student5');

# 等价于简单指令：
SELECT * from student WHERE name='student5';

7.表子查询

子查询的返回结果用于FROM数据源，它是一个符合二维表结构的数据，可以是一行一列、一列多行、一行多列或多行多列。

FROM后的数据源都是表名。

当数据源是子查询时必须为其设置别名，同时也是为了将查询结果作为一个表使用时，可以进行条件判断、分组、排序以及限量等操作。

查询id<7并且名字为“student5”的数据：

SELECT * FROM 
(SELECT * FROM student WHERE id<7) AS s
WHERE s.name='student5';

等价于：
SELECT * FROM student WHEREid<7 AND name='student5';