一、什么是子查询

      子查询（subquery）是指，嵌套在查询内部，出现在其他SQL语句内的select子句。例如：

SELECT * FROM table1 WHERE col1 = (SELECT col2 FROM table2);

      其中，SELECT col2 FROM table2就是子查询。

• 子查询必须出现在圆括号内
• 子查询可以包括多个关键字或条件
• 子查询的外层查询可以是SELECT,INSERT,DELETE,UPDATE,SET,DO.
• 子查询可以返回标量、一行、一列，甚至是子查询

二、子查询的分类

1、使用比较运算符的子查询

      例如，查询分数大于等于平均数的学生。

SELECT name FROM students WHERE score >= (SELECT ROUND(AVG(score),2) FROM students);

      当使用比较运算符的子查询中，返回的结果大于一个时，要使用ANY,SOME或ALL来修饰子查询，否则会出现错误。其中ANY和SOME是等价的，表示符合其中一个结果即可，ALL表示要符合全部结果。例如，查询分数大于A班分数的学生：

SELECT name FROM students WHERE score > ANY (SELECT score FROM students WHERE klass = 'A');

2、使用[NOT] IN的子查询

      与比较运算符的子查询类似，IN与=ANY等效，NOT IN与!=ALL等效。

3、使用[NOT] EXISTS的子查询

      如果子查询返回任何内容，则EXIST返回TRUE，否则返回FALSE。

三、子查询的应用

1、将查询到的结果写入数据表

      例如，从students表中，查询所有的班级，写入classes表中

INSERT classes(class_name) SELECT klass FROM students GROUP BY klass;

2、多表更新(参照另外的表来更新本表中的记录)

      例如，将students表中存储的班级klass，改为classes表中对应的class_id

UPDATE students INNER JOIN classes ON klass = class_name SET klass = class_id;

      若两个表中对应的字段名相同，需要指定是哪个表中的字段，可以通过起别名的方式实现，例如

UPDATE students AS s INNER JOIN classes AS c ON s.klass = c.class_name SET s.klass = c.class_id;

3、创建数据表并把查询的结果写入数据表

      例如，创建一个省份表provinces，并把students表中所有省份province写入到省份表中

CREATE TABLE provinces
(
province_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
province_name VARCHAR(20) NOT NULL
)
SELECT province FROM students GROUP BY province;

4、多表删除

      例如，删除students表中，姓名重复的记录。

DELETE s1 FROM students AS s1 LEFT JOIN (SELECT id,name FROM students GROUP BY name HAVING count(name)>=2) AS s2 ON s1.name = s2.name WHERE s1.id>t2.id;