一.视图
什么是视图?
在数据库中,表中存放的是实际数据,视图中存放的是sql查询语句,使用视图时,会运行视图里的sql查询语句创建出一张临时表。(在客户端与数据库断开连接时,临时表会自动删除,并不是保存在数据库中,所以是临时表)。
如何创建视图?
create view 视图名称(,,...)
as
;
----create view 按性别汇总(性别,人数)
as
select 性别,count(*) #查询语句中列的名称和视图列名可以不一样,但是关系是一一对应的。
from student
group by 性别;
我们可以将频繁使用的sql语句保存为视图,之后直接在from字句中使用视图名称代替表名称,而且在表更新数据后,视图的数据也会自动更新,因为视图保存的并不是数据,而是查询语句。
因为视图不需要保存数据,所以可以节省存放数据的空间。
select 性别,人数
from 按性别汇总;
使用视图注意事项:
1.避免在视图的基础上再创建视图,多重视图会降低sql的性能和效率。
2.往视图里插入数据,不然会报错。
二.子查询
子查询可以理解为一次性的视图,在一个select查询中再嵌套一个查询。
select 性别,人数
from(
select 性别,count(*) as 人数
from student
group by 性别
) as 按性别汇总;
#sql语句中有子查询时,会先运行子查询,得到一个临时表,再运行外部的查询语句从临时表查找数据。
除了from字句,子查询也可以放到where字句里,与运算符in,any,all一起使用,从而构建出复杂的查询条件。
练习:找出每个课程号成绩最低的学号(topN问题)
select 学号,课程号,成绩
from score s1
where 成绩=(
select min(成绩)
from score s2
where s1.课程号=s2.课程号
group by 课程号);
any,all关键词与子查询一起使用:
any(子查询)与some(子查询)相同
哪些学生的成绩比课程0002的全部成绩里的任意一个高呢?
select 学号,成绩
from score
where 成绩>any (
select 成绩
from score
where 课程号='0002');
哪些学生的成绩比课程0002的全部成绩都高呢?
select 学号,成绩
from score
where 成绩>all (
select 成绩
from score
where 课程号='0002');
注意两个查询的不同。
什么情况用子查询,视图对比:在一个查询语句只是偶尔使用时,就没必要保存为视图,视图适用于频繁使用的查询语句。
使用子查询的注意事项:
注意事项1:
a>3*all(b) × a/3>all(b) √
如果需要两个数字进行比较,由于在mysql中,all得到的不是一个数据,而是一个集合,是N行数据,所以不能用第一种方式比较。
注意事项2:
避免使用多层嵌套子查询,随着子查询嵌套越来越多,会让整个查询变得难以理解,且影响性能。
注意事项3:
尽量不要省略子查询的as取别名,方便看懂。
三.标量子查询
什么是标量子查询?
由于在where字句中,不能使用汇总函数,在想对结果用汇总函数进行限制时,需要使用返回单一值的标量子查询,这样才能与比较运算符一起使用。
select 学号,成绩
from score
where 成绩>(
select avg(成绩)
from score);
四.关联子查询
查询找出每个课程中大于对应课程平均成绩的学生
select 学号,课程号,成绩
from score s1
where 成绩>(
select avg(成绩)
from score s2
where s1.课程号=s2.课程号 #关联条件
group by 课程号);
对关联子查询的理解:当从表1中查询数据时,会根据where字句后的比较运算符筛选,子查询中关联条件限制了,子查询只会返回与s1课程号同一组的平均成绩,所以子查询每次返回的不是每个组的平均成绩,而是只返回一行结果。
注意:关联条件一定要写在子查询中。
各种函数:
总结:视图可以储存使用频繁的查询语句,便于工作效率;子查询可以理解为一次性的视图。
除了from字句,子查询也可以放到where字句里,与运算符in,any,all一起使用,构建出复杂的查询条件。
标量子查询是返回单一值的查询,在where字句中,不能使用汇总函数,所以通过标量子查询,与比较运算符一起对结果进行限制。
在需要在每一组里进行比较时,我们可以使用关联子查询。