sql join 子查询_SQL 之 视图&子查询

学习目标

是什么？如何用？有什么用？注意事项

1.视图；2.子查询；3.标量子查询；4.关联子查询；5.各种函数

---

一. 视图

1. 什么是视图？

表中存放的是实际数据，视图中存放的是SQL查询语句，不存放数据。使用视图时，会运行视图里的SQL查询语句，创建出一张临时表。当客户端和数据库的连接端掉后，临时表会被自动删除。

2. 如何创建视图？

CREATE VIEW 按性别汇总(性别, 人数)
AS
SELECT 性别, COUNT(*)
FROM student
GROUP BY 性别;

3. 如何使用视图？

SELECT 性别, 人数
FROM 视图名称

4. 视图有什么用?

1）表中内容更新，视图中会跟着更新，因为存放的是SQL查询语句。
2）节省存放数据设备的空间。

5. 注意事项

1）避免在视图的基础上再次创建视图，因为多重视图会降低SQL的性能和效率。
2）不能往视图插入数据。

---

二. 子查询

1. 什么是子查询？

子查询就是一次性的视图。

在FROM子句中直接写定义视图的SQL查询语句，在SELECT查询语句中嵌套了另一个SELECT查询语句。

SELECT 性别, 人数
FROM(
SELECT 性别, COUNT(*) AS 人数
FROM student
GROUP BY 性别
) AS 按性别汇总;

注：AS按性别汇总 是子查询的名称

SQL运行顺序：先运行子查询，得到一个临时表。

2. 如何使用子查询？

子查询可以放在WHERE子句中，与IN(子查询)， ANY(子查询)， ALL(子查询)。

1）IN(子查询)

例：查询每个课程里成绩最低的学号。

第一步：查询每个课程的最低成绩。

SELECT 课程号, MIN(成绩)
FROM score
GROUP BY 课程号;

不能直接添加学号在SELECT语句中，因为在分组过程中，每个组里只有课程号和最低成绩，没有学号。

第二步：

SELECT 学号, 成绩
FROM score
WHERE 成绩 IN(80,60,80);

最终：

SELECT 学号, 成绩
FROM score
WHERE 成绩 IN(
SELECT MIN(成绩)
FROM score
GROUP BY 课程号
);

----------------------------------我是华丽的分割线---------------------------------------

上述的查询结果是错误的！！！

SQL面试题： topN问题

分组取每组最大值，最小值，每组最大的N条记录。

例：查询每个课程里成绩最高的记录。

SELECT *
FROM score AS a
WHERE 成绩 = (
SELECT MAX(成绩)
FROM score AS b
WHERE b.课程号 = a.课程号
);

例：查询每个课程里成绩前两名的记录。

1.每个课程按成绩降序排序并取前两名

SELECT *
FROM score 
WHERE 课程号 = '0001'
ORDER BY 成绩 DESC
LIMIT 2

2.综合所有组

(SELECT *
FROM score 
WHERE 课程号 = '0001'
ORDER BY 成绩 DESC
LIMIT 2) UNION ALL
(SELECT *
FROM score 
WHERE 课程号 = '0002'
ORDER BY 成绩 DESC
LIMIT 2) UNION ALL
(SELECT *
FROM score 
WHERE 课程号 = '0003'
ORDER BY 成绩 DESC
LIMIT 2)

---------------------------------我是华丽的分割线----------------------------------------

2）ANY(子查询)

ANY(子查询)与SOME(子查询)相同。

SELECT <列名1>
FROM <表名1>
WHERE <列名1> > ANY(子查询)；

问：哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？

1.查找出课程0002的全部成绩。

SELECT 成绩
FROM score
WHERE 课程 = '0002';

2.某个学生的成绩 大于 任意一个第一步里的成绩，就符合条件。

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

3）ALL(子查询)

问：哪些学生的成绩比课程0002的全部成绩里的都高呢？

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

注意事项

1. ALL得到的不是数字，而是一个n行的集合，所以不能做运算， e.g. 3*ALL() 是错误的。
2. 避免使用多层嵌套子查询，不好维护且性能差。
3. 子查询名称 AS 子查询名称 可以省略，但尽量不要省略。

---

三. 标量子查询

1. 什么是标量子查询？

上一节的子查询返回多行结果，而标量子查询在子查询上做了特殊的限制，必须且只能返回一行一列的查询结果，返回的是单一的值，所以可以将其与比较运算符连用。

2. 如何使用标量子查询？

例：查询大于平均成绩的学生的学号和成绩。

WHERE子句中不能使用汇总函数，SQL报错。

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

这时可以使用标量子查询：

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

例：查询成绩介于差生 （<= 60）平均成绩和优等生（>= 80）平均成绩之间的学生记录。

SELECT *
FROM score
WHERE 成绩 BETWEEN
(SELECT AVG(成绩)
FROM score
WHERE 成绩 <= 60) AND 
(SELECT AVG(成绩)
FROM score
WHERE 成绩 >= 80);

3. 什么时候使用标量子查询？

任何使用单一值的地方都可以使用标量子查询。

注意事项

该子查询不能返回多行结果。若返回多行结果，则不能用在比较运算中。

---

四. 关联子查询

1. 什么是关联子查询？

关联子查询是在子查询里有了一个关联条件。

例：查找出每个课程大于对应课程平均成绩的学生。

关联条件：WHERE子句中按课程号对成绩表分组，同一组的数据和这一组的平均成绩比较，因为比较的都是同一张score表，所以分别取了别名s1，s2，在关联条件里通过使用表的别名去使用表里的列名（s1.课程号）。

SELECT 学号, 课程号, 成绩
FROM score AS s1
WHERE 成绩 > (
SELECT AVG(成绩)
FROM score AS s2
WHERE s1.课程号 = s2.课程号
GROUP BY 课程号
);

先运行子查询，查找出每个课程的平均成绩。因为子查询中关联条件的存在，所以每次只返回与s1课程号同一组的平均成绩，返回一行结果。

2. 什么时候使用关联子查询？

在每个组里进行比较的时候，使用关联子查询。

---

五. 函数汇总

---