SQL子查询

1常见子查询

1.1SELECT 语句中的子查询

首先，什么是子查询呢？

当一个查询是另一个查询的条件时，称之为子查询

即在查询语句中的 WHERE 条件子句中，又嵌套了另一个查询语句。

因此，子查询本质上就是一个完整的 SELECT 语句，它可以使一个 SELECT、INSERT INTO 语句、DELETE 语句或 UPDATE 语句嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

语法：

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR (
    SELECT `column_name(s)`
    FROM `table_name`
);

例如：小明想了解 Western Venom 老师所教的所有课程的所有信息，现请你来帮助他查询相关信息。

SELECT *
FROM `courses`
WHERE `teacher_id` = (
	SELECT `id`
	FROM `teachers`
	WHERE `name` = 'Western Venom'
);

1.2INSERT 语句中的子查询

语法：

INSERT INTO `table_name`
	SELECT `colnum_name(s)`
	FROM `table_name`
	[ WHERE VALUE OPERATOR ]

例如：小明在整理数据时发现教师表未备份，为了及时完善数据的备份，现在需要将教师表 teachers 中的全部信息复制到相同表结构的备份表 teachers_bkp 中，请使用相关 SQL 语句完成教师表的备份。

INSERT INTO `teachers_bkp`
SELECT *
FROM `teachers`;

1.3UPDATE 语句中的子查询

语法：

UPDATE `table_name` 
SET `column_name` = `new_value`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`
   [WHERE] )

例如:学校教务处排课时发现教师 Western Venom 创建的课程有误，现紧急需要将该教师创建的课程名称修改为 Java，请你使用相关的 SQL 语句完成。

UPDATE `courses`
SET `name` = 'Java'
WHERE `teacher_id` = (
	SELECT `id`
    FROM `teachers`
    WHERE `name` = 'Western Venom'
);

1.4DELETE 语句中的子查询

语法：

DELETE FROM `table_name`
WHERE `column_name` OPERATOR
   (SELECT `column_name`
   FROM `table_name`
   [WHERE] )

例如：需要删除课程表中所有教师年龄小于 21 岁（不包括 21 岁）的课程，请你使用相关的 SQL 语句实现。

DELETE FROM `courses`
WHERE `teacher_id` IN (
		SELECT `id`
		FROM `teachers`
		WHERE `age` < 21
	);

2子查询进阶

2.1内联视图子查询

内联视图子查询实际上就是将查询的结果集作为一个查询表，继续进行查询操作。

👇 我们可以通过下面的实例来感受一下 内联视图子查询 的用法。

现需要查询国籍为美国（USA），且年龄最大的教师，请使用内联视图子查询实现。

SELECT *
FROM (
  SELECT *
  FROM `teachers`
  WHERE `country` = 'USA'
) `T`
WHERE `age` = (
  SELECT MAX(`age`)
  FROM `teachers`
);

2.2IN 操作符的多行子查询

使用 IN 操作符进行子查询，其实是将子查询返回的集合和外层查询得到的集合进行交集运算，这个结果可以是零个值，也可以是多个值。由此，最后可以查询出与列表中任意一个值匹配的行。

语法：

SELECT `column_name`
FROM `table_name`
WHERE `column_name` IN(
    SELECT `column_name`
    FROM `table_name`
    WHERE `column_name` = VALUE
);

现需要查询国籍为美国（USA）的教师所开的所有课程，请使用 IN 操作符进行多行子查询。

SELECT `name`
FROM `courses`
WHERE `teacher_id` IN (
	SELECT `id`
	FROM `teachers`
	WHERE `country` = 'USA'
);

2.3ANY 操作符的多行子查询

除了使用 IN 操作符，还有什么方法能进行多行子查询呢？

操作符 ANY 属于逻辑运算符的一种，与 IN 运算符不同，ANY 必须和其它的比较运算符共同使用，其表示查询结果中的任意一个。

在子查询中使用 ANY ，表示与子查询返回的任何值比较为真，则返回真。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR
   ANY(SELECT column_name
   FROM table_name)

👇 我们可以通过下面的实例来感受一下 ANY 操作符多行子查询 的用法。

现需要查询学生上课人数超过 “Eastern Heretic” 的任意一门课的学生人数的课程信息，请使用 ANY 操作符实现多行子查询。

第一层的父查询为在课程表 courses 中查询满足条件的全部课程信息，这个条件由子查询来完成，即为，查询学生上课人数超过 ”Eastern Heretic“ 的任意一门课的学生人数。这一部分的子查询中需要结合 ANY 操作符实现。之后，再将子查询进行拆分，形成第二层的嵌套子查询。

第二层的父查询为在课程表 courses 中根据教师 id 查询学生上课人数， 其子查询为在教师表 teachers 中查找教师名 name 为 “Eastern Heretic” 的教师 id。

由于我们最终得到的课程信息中肯定不包含 “Eastern Heretic” 的课程，所以我们要在 WHERE 条件中再设置一项：不为 “Eastern Heretic” 所开的课程 。

SELECT *
FROM `courses`
WHERE `student_count` > ANY (
		SELECT `student_count`
		FROM `courses`
		WHERE `teacher_id` = (
			SELECT `id`
			FROM `teachers`
			WHERE `name` = 'Eastern Heretic'
		)
	)
	AND `teacher_id` <> (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Eastern Heretic'
	);