SQL：子查询
一，子查询简介：
子查询就是嵌套在主查询中的查询。
子查询可以嵌套在主查询中所有位置，包括SELECT、FROM、WHERE、ORDER BY。但并不是每个位置嵌套子查询都是有意义并实用的。
子查询必须“自身就是一个完整的查询”。即，它必须至少包括一个SELECT子句和FROM子句。
子查询的分类：
相关子查询： 执行依赖于外部查询的数据。（就是table1在该select外面，而select里面table1.xx=table2.xx） 外部查询返回一行 ，子查询就执行一次。（对于该select，里面的子select会重复很多次执行） 非相关子查询： 独立于外部查询的子查询。 子查询总共执行一次，执行完毕后后将值传递给外部查询。
二，子查询的返回：
一个子查询会返回一个标量（单一值）、一个行、一个列或一个表（一行或多行及一列或多列）。这些子查询被称为标量、列、行和表子查询
1，单行单列,聚合(标量)：
返回的结果集为单个的子查询，叫做单行子查询。单行比较符有： =、 >、>=、<、<=、!=
2，单行多列：
SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
如果在表t2的一个行中，column1=1并且column2=2，则查询结果均为TRUE。
表达式(1,2)和ROW(1,2)有时被称为行构造符。两者是等同的，在其它的语境中也是合法的。例如，以下两个语句在语义上是等同的（但是目前只有第二个语句可以被优化）：
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
3，单列：
返回的结果集为多个的子查询，为多行子查询，多行子比较符有 IN(等于列中任意一个)、ANY(和子查询返回的某个值比较)，ALL(和子查询返回的所有值比较)
NOT IN 是 <> ALL 的别名
IN 是 ＝ ANY 的别名
4，多行多列的子表：
可以用在where子句中的 exists，not exists的查询条件；或者from子句后面(此时子表必须命名，并且不能为有关联的子查询)
五，在FROM中嵌套
from下的子查询 相当于返回一张表，并且要强制取名。
from子句中的子查询不能为有关联的子查询。
在SELECT语句的FROM子句中，子查询是合法的。实际的语法是：
SELECT ... FROM (subquery) [AS] name ...
[AS] name子句是强制性的，因为FROM子句中的每个表必须有一个名称。在子查询选择列表中的任何列都必须有唯一的名称。假设有如下一个表：
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
下面使用了示例表，解释了在FROM子句中如何使用子查询：
INSERT INTO t1 VALUES (1,'1',1.0); INSERT INTO t1 VALUES (2,'2',2.0); SELECT sb1,sb2,sb3 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1; //结果：2, '2', 4.0。
下面是另一个例子：假设您想了解一个分类后的表的一组和的平均值。采用如下操作：
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
不过，本查询提供所需的信息：
SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;
注意，在子查询中使用的列名称（sum_column1）被整理到外部查询中。
六，在SELECT中嵌套：
select中的子查询如果是相关子查询，一般可以和join关联查询转换
现有表两张：一张学生表、一张班表。id相关联
学生信息和班级名称位于不同的表中，要在同一张表中查出学生的学号、姓名、班级名称：
SELECT s.student_id,s.student_name,(SELECT class_name FROM t_class c WHERE c.class_id=s.class_id) FROM t_student s GROUP BY s.student_id;
七，在WHERE中嵌套：
现要查出C语言成绩最高的学生的信息：
SELECT * FROM t_student WHERE student_subject='C语言' AND student_score>=
ALL (SELECT student_score FROM t_student WHERE student_subject='C语言') ;
这里出现了一个ALL，其为子查询运算符
分类：
–ALL运算符
和子查询的结果逐一比较，必须全部满足时表达式的值才为真。
–ANY运算符
和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。
–EXISTS/NOT EXISTS运算符
EXISTS判断子查询是否存在数据，如果存在则表达式为真，反之为假。NOT EXISTS相反。
在子查询或相关查询中，要求出某个列的最大值，通常都是用ALL来比较，大意为比其他行都要大的值即为最大值。
要查出C语言成绩比李四高的学生的信息：
SELECT * FROM t_student WHERE student_subject='C语言' AND student_score >
(SELECT student_score FROM t_student WHERE student_name='李四' AND student_subject='C语言');
通过上面两例，应该可以明白子查询在WHERE中嵌套的作用。通过子查询中返回的列值来作为比较对象，在WHERE中运用不同的比较运算符来对其进行比较，从而得到结果。
现在我们回到最开始的问题，怎么查出每门课最高成绩的学生的信息：（这个也是相关子查询）
SELECT * FROM t_student s1 WHERE s1.student_score >=
ALL(SELECT s2.student_score FROM t_student s2 WHERE s1.`student_subject`=s2.student_subject);
这里就是上文提到的别名的第二种用法，主、子查询对同一张表操作，区分开位于内外表中相同的列名。
八，order by子查询
参考：https://www.cnblogs.com/gered/p/12696134.html
需求举例：按平均值排序
select 学号,姓名,专业名 from xs where 专业名 = '计算机' order by (select avg() from xs_kc group by xs_kc.学号 having xs_kc.学号=xs.学号);
三，EXISTS和NOT EXISTS
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。
请参考： https://blog.csdn.net/qq_27571221/article/details/53090467
#exists的例子 SELECT s.Sname FROM student s WHERE EXISTS (SELECT * FROM sc, course c WHERE sc.sno = s.sno AND sc.cno = c.cno AND c.cname = '信号与系统') //里面还用到了from多个表的情况，from多个表的时候按条件笛卡尔积 #not exists的例子 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno) );
四，子查询和join转换情况
#使用join
update member set nickname = member_update_store.nickname from member join member_update_store
on (member.id= member_update_store.memberid)
where member.id in(1,2,3) and member_update_store.nickname is not null;
#使用子查询
update member set nickname = (select nickname from member_update_store
where member_update_store.memberid = member.id and member_update_store.nickname is not null)
where id in(1,2,3);
