5. SQL 复杂查询

1. 视图

1.1 什么是视图

视图是一张虚拟的表，它本质上是将常用的SQL查询语句保存起来，每次用到该数据，都直接运行视图得到即可。
注意视图保存的不是数据，而是SQL查询语句。

1.2 视图有什么用

将常用的SQL查询语句保存起来，方便后续重复使用。

1.3 使用视图

1.3.1 创建视图

使用如下语句进行视图创建，

CREATE VIEW 视图名称(视图列名1, 视图列名2) AS SELECT <查询语句>;

注意SELECT 查询语句中列的顺序和视图中要保持一致，如下图所示：

1.3.2 使用视图

用视图名称替代表名即可，

SELECT 性别,人数
FROM 性别汇总;

查询结果如下图所示：

1.4 注意事项

1. 避免在视图的基础上再创建视图，嵌套会降低效率。
2. 不能往视图中插入数据，否则会报错。

2. 子查询

返回多行，多列结果的查询，当与in，all, any使用，要返回多行，单列。

1.1 什么是子查询

简言之，子查询是一次性视图。
如果一个SELECT语句中有子查询，则会首先运行子查询生成临时表，然后再运行外部的查询语句。

1.2 子查询作用

根据使用目的，如果偶尔使用某个临时表，可以使用子查询，而没必要保存为视图。所以根本区别在于使用频率。

1.3 如何使用子查询

1.3.1 FROM 子句中使用

在FROM 子句中使用子查询，提供给外部查询作为临时表。
比如直接把上一节中的视图SELECT子句放到FROM中，如下代码：

SELECT 性别,人数 
FROM (SELECT 性别, COUNT(*) FROM student GROUP BY 性别);

1.3.2 在WHERE 中使用

1. in (子查询）
   限定条件在查询结果中的任意一个，相当于等于任意一个。
   选出各科最小成绩，查询成绩最小成绩里的学号。注意这个在知乎LIVE中的意义是错误的，并不能得到各科成绩最小的学号。

SELECT 学号,成绩
FROM score
WHERE 成绩 in (SELECT MIN(成绩) AS 最低成绩
FROM score
GROUP BY 课程号);

2. any(子查询)
   any必须与比较运算符一起使用，表示与任何一个相比，大于等于或者小于，得到一个条件判断。
   这句话和in的那句表达意义是相同的。

SELECT 学号,成绩
FROM score
WHERE 成绩 = any(SELECT MIN(成绩) AS 最低成绩
FROM score
GROUP BY 课程号);

3. all(子查询)
   all同样与比较运算符一起使用，表示比全部结果都大或者小。
   下面语句表示，那个学生的成绩比课程0002的所有成绩都高。

SELECT 学号,成绩
FROM score
WHERE 成绩 > all(SELECT 成绩
FROM score
WHERE 课程号='0002');

1.4 注意事项

1. 子查询不要做加减乘除，因为他是一个集合
2. 子查询最好不要嵌套，性能低，不好维护
3. 子查询可以使用AS进行别名命名，也可以省略

3. 标量子查询

子查询返回多行多列，标量子查询是在子查询的基础上限制返回一行一列结果的查询。
用于解决WHERE语句中不能使用聚合函数的限制，实现如下需求：

SELECT 学号,成绩 FROM score WHERE 成绩>AVG(成绩); ----报错，因为AVG不能出现在WHERE子句中

替代方案如下：

SELECT 学号,成绩 
FROM score 
WHERE 成绩 > (SELECT AVG(成绩) FROM score);

执行结果如下图：

简单说，标量子查询可以看成查询结果得到一个标量，然后可以进行对比，对算等操作。

注意事项：标量子查询，不能返回多行，所以在利用子查询时候，要注意需求是标量子查询还是普通的子查询。

4. 关联子查询

关联子查询是在子查询中使用了关联条件，用于解决组内比较问题。
比如需要查找出每门课程中，成绩大于该课程平均成绩的学生？

1. 首先计算出每门课程平均成绩，很简单。
2. 关键在于怎么在各分组中，与该分组的平均成绩进行对比，这就需要关联子查询来帮忙。

4.1 关联子查询执行顺序

关联子查询很难懂，难懂的原因在于其与SELECT普通查询的执行顺序有很大差异，我们按照如下代码来分析执行顺序，从而怎么得到结果的。

SELECT 学号,课程号,成绩 
FROM score as s1
WHERE 成绩 > (SELECT AVG(成绩) 
FROM score as s2
WHERE s1.课程号=s2.课程号  -----这句话是成功的关键因素
GROUP BY 课程号);

执行顺序：

1. 先执行主查询 （不再先执行子查询了）

SELECT 学号,课程号,成绩 
FROM score as s1; --- 得到一个表

2. 从主查询的 课程号类别 中取第一个0001，通过 WHERE s1.课程号=s2.课程号 传入子查询。
   如下图：
   
   此时子查询语句变成如下代码，得到了 0001 课程的平均成绩为 80。：

SELECT AVG(成绩) 
FROM score as s2
WHERE s2.课程号='0001'  -----这句话是成功的关键因素
GROUP BY 课程号

3. 将 0001课程平均成绩传回给主查询，得到如下语句

SELECT 学号,课程号,成绩 
FROM score as s1
WHERE 成绩 > 80 AND 课程号='0001';

此时得到结果如下图，说明课程0001中没有大于平均成绩的：

4. 继续重复第2步，把第2个类别，0002传入，如下代码：

SELECT AVG(成绩) 
FROM score as s2
WHERE s2.课程号='0002'  -----这句话是成功的关键因素
GROUP BY 课程号

得到平均成绩 76.6667.
然后执行第3步，如下代码：

SELECT 学号,课程号,成绩 
FROM score as s1
WHERE 成绩 > 76.6667 AND 课程号='0002';

得到如图结果,说明0002课程中，0001和0003学生的成绩高于平均值：

5. 以此类推，将所有类别结果求出来，然后组成最终结果展示。

4.2 总结

1. 关联子查询执行逻辑不同于正常过SQL语句
2. 关联子查询用于每个组内进行比较
3. 要熟记上述查询思路，一边迁移应用。

5. 各种函数总结

上一节介绍了汇总函数，下面总结几个比较常会用的其他函数。

6. 本节小结

1. 频繁使用的子查询保存为视图
2. 普通子查询返回多行，与in any all配合使用
3. 标量子查询返回单一值，往往用于WHERE条件比较
4. 关联子查询用于求解分组内部的比较