学习目标
是什么?如何用?有什么用?注意事项
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'
);
注意事项
- ALL得到的不是数字,而是一个n行的集合,所以不能做运算, e.g. 3*ALL() 是错误的。
- 避免使用多层嵌套子查询,不好维护且性能差。
- 子查询名称 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. 什么时候使用关联子查询?
在每个组里进行比较的时候,使用关联子查询。