29.MySQL中的子查询

1.什么是子查询

子查询可以理解为，在一个SQL语句A（SELECT、INSERT、UPDATE等）中嵌入一个查询语句B，作为执行的条件或查询的数据源（代替FROM后的数据表），那么B就是子查询语句，它是一条完整的SELECT语句，能够独立的执行。

在含有子查询的语句中，子查询必须书写在圆括号( )内。

SQL语句首先会执行子查询中的语句。

然后再将返回的结果作为外层SQL语句的过滤条件。

当遇到同一个SQL语句中含有多层子查询时，它们执行的顺序是从最里层的子查询开始执行。

2.子查询分类

子查询的划分方式有多种，最常见的是以功能和位置进行划分。

按子查询的功能可以分为标量子查询、列子查询、行子查询和表子查询。

按子查询出现的位置可以分为WHERE子查询和FROM子查询。

标量子查询、列子查询和行子查询都属于WHERE子查询。

表子查询属于FROM子查询。

3.准备数据

CREATE DATABASE mahaiwuji;
USE mahaiwuji;

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

INSERT INTO student VALUES (1,'码海无际1',21);
INSERT INTO student VALUES (2,'码海无际2',22);
INSERT INTO student VALUES (3,'码海无际3',23);
INSERT INTO student VALUES (4,'码海无际4',24);
INSERT INTO student VALUES (5,'码海无际5',25);
INSERT INTO student VALUES (6,'码海无际6',26);
INSERT INTO student VALUES (7,'码海无际7',27);
INSERT INTO student VALUES (8,'码海无际8',28);

4.标量子查询

子查询返回的结果是一个数据，即一行一列。

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

查询比“码海无际5”年龄小的数据

# 1.先获取“码海无际5”的年龄
SELECT age FROM student WHERE name='码海无际5';
# 2.获取所有的数据让年龄小于“码海无际5”的年龄
SELECT * FROM student WHERE age < 
(SELECT age FROM student WHERE name='码海无际5');

5.列子查询

子查询返回的结果是一个字段符合条件的所有数据，即一列多行。

子查询利用比较运算函数IN()或NOT IN()。

判断指定的条件是否在子查询语句返回的结果集中。

查询年龄不是“码海无际5”的年龄和“码海无际6”的年龄的数据

# 1.先获取“码海无际5、码海无际6”的年龄
SELECT age FROM student WHERE name='码海无际5' OR name='码海无际6';
# 2.查询年龄不是“码海无际5”的年龄和“码海无际6”的年龄的数据
SELECT * FROM student WHERE age NOT IN
(SELECT age FROM student WHERE name='码海无际5' OR name='码海无际6');

6.行子查询

当子查询的结果是一条包含多个字段的记录（一行多列）时，称为行子查询。

行子查询返回的一条记录与指定的条件比较，比较的运算符通常使用=。

子查询的结果必须全部与指定的字段相等才满足WHERE指定的条件。

查询名字为“码海无际5“的数据

SELECT * FROM student WHERE (name,age) =
(SELECT name,age FROM student WHERE name='码海无际5');

7.表子查询

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

FROM后的数据源都是表名。

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

查询id小于7并且名字为“码海无际5“的数据

SELECT * FROM 
(SELECT * FROM student WHERE id < 7) AS s
WHERE s.name='码海无际5';