mysql 复杂sql_SQL：复杂查询

一.视图

什么是视图？

在数据库中，表中存放的是实际数据，视图中存放的是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字句中，不能使用汇总函数，所以通过标量子查询，与比较运算符一起对结果进行限制。

在需要在每一组里进行比较时，我们可以使用关联子查询。