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);