写一个sql实现以下查询结果_SQL复杂查询—知识点梳理（四）

SQL复杂查询学习大纲

一、视图

1.1 什么是视图

在数据库里有很多表，表里存放的是实际数据，而视图中存放的是SQL查询语句。当我们通过客户端连接到数据库，开始使用视图时，视图会先运行里面的SQL查询语句，从表里查找出数据保存到一张临时表中（当客户端断开与数据库的连接，这张表会自动删除，不是保存在数据库里的表，因此是临时表）。因此视图本身不存放数据，存放的是SQL查询语句。

• • • 创建视图

创建视图的SQL语句：create view ... as ...<select查询语句> ；

create view表示创建视图

查询语句列的顺序要和视图名称括号里列的顺序要一一对应。如下图，新建视图“按性别汇总”，SQL查询语句写在as之后，查询语句的列和视图名称括号里的列名称可以不同，但他们的关系是一一对应的。

• • • • 创建视图步骤

写好创建视图的SQL语句并运行—右击左侧“视图”—刷新

1.2 如何使用视图

用视图名称代替表名。表中的数据更新时，视图也会随之更新。

• • • 删除视图步骤

选中要删除的视图名称右击—“删除视图”

1.3 视图有什么用

作用1：需要频繁使用的SQL语句可以保存成视图，不需要每次都重新输入一遍。特别是在庞大的复杂查询语句中，使用视图可以提高效率。

作用2：视图中的数据会随着原表的变化自动更新，可以保证数据的最新状态

作用3：视图不需要保存数据，可以节省存放数据设备的空间

1.4 注意事项

1）避免在已创建视图的基础上再创建视图，多重视图会降低SQL的性能和效率

2）不能往视图里插入数据，否则会报错

二、子查询

之前学习的查询条件都是具体的数值，如“学习成绩大于60分”，但如果查询条件需要从表中获取，如“成绩大于平均成绩的学生有哪些？”，这就需要用到子查询。

2.1 什么是子查询

子查询就是一次性视图。在SQL查询子句中，直接写定义视图的SQL查询语句，即在一个select查询语句中嵌入另一个select查询语句。下图中的“按性别汇总”就是子查询的名称，由于该名称是一次性的，不会像视图名称一样保存在数据库的硬盘中，在SQL查询语句运行结束后就会消失，所以可以看成临时表。

SQL运行顺序——先运行子查询，将子查询的结果作为外部查询的一部分，再运行外部的查询语句

2.2 如何使用子查询

子查询除了放在form子句里，还可以放在where子句里，与运算符in、any、all一起使用，从而构建出复杂的查询条件。使用方法是在运算符in、any、all的括号里放入子查询。

错误示例——查询每门课程里成绩最低的学号

正确示例——查询每门课程里成绩最低的学号

any和all

any、all关键字必须与一个比较运算符一起使用

any等同于some，用法相同

案例1—any的用法

案例2—all的用法

2.3 子查询有什么用

2.4 注意事项

1）虽然在数学上a>3*all(b)等价于a/3>all(b)，但是在数据库里all代表的不是一个数字而是一个集合即得到的是N行数据，因此不能写成3*all(b)的形式

2）避免使用多层嵌套子查询。因为子查询的层数没有限制，随着子查询层数越来越多，SQL语句越来越复杂，性能变差并且不好维护。

3）as关键字及子查询名称可以省略。尽量不要省略更有利于读懂。

SQL运行顺序

三、标量子查

什么是标量子查询

由于在where子句中不能使用汇总函数，因此报错，此时可以使用标量子查询

子查询可以返回一行或多行数据，标量子查询就是在子查询的基础上做了特殊限制，必须且只能返回一行一列的查询结果，即返回的是单一的值。如下图返回的只有一行一列即平均成绩这个单一值，它是一个标量子查询。或者查询的是表中某一行某一列的值，返回的也是单一的值，因此标量子查询可以和比较运算符一起使用

案例

如何使用标量子查询

标量子查询的书写位置并不仅仅局限于where子句，通常任何使用单一值的地方都可以使用标量子查询

标量子查询有什么用

可以和比较运算符及between、in、or等关键字一起使用实现复杂的查询条件

注意事项

该子查询不能返回多行结果，如果返回多行结果那就不再是标量子查询而是普通子查询，因此也不能用在比较运算符中

四、关联子查询

什么是关联子查询及如何使用关联子查询

可以依据子查询是否执行多次，从而将子查询划分为关联子查询和非关联子查询

按课程号分组后得到每门课程的平均成绩，要查找出每门课程大于对应课程平均成绩的学生，需要在每个组里而不是整个表中进行比较，此时就需要用到关联子查询

这里起到关键作用的就是关联条件。在子查询里有个where子句的条件，意思是按课程号对成绩表进行分组，同一组里的数据和这一组的平均成绩进行比较，由于作为比较对象的都是同一张表score，为了更好区分，此处使用s1、s2两个别名。在使用关联子查询时，需要使用如下图红框中表的别名来表示表的列名。

关联条件一定要写在子查询里。在下图例子中，表别名s2只在子查询中有效，因此表别名s2能看到表别名s1，表别名s1看不到表别名s2

关联子查询有什么用

当每个组里进行比较时使用关联子查询，关联子查询是在子查询里有了一个关联条件

五、用SQL解决业务问题

案例

看懂SQL报错信息

使用排除法逐一查找错误之处。如下图出现报错，可以先选中子查询检查是否出现报错，若子查询出现报错则说明问题出在子查询中；若子查询运行正常则说明问题出在子查询外部。

六、各种函数

七、补充知识

concat函数及round函数

concat函数为字符串连接函数

round函数用于把数值字段四舍五入为指定的小数位数，用法：round(数值,返回的小数位数)

非null值的表现形式

关联子查询（关联子查询里的group by可以省略）

案例一

案例二

案例三

案例四

topN问题

• • • 分组取每组最大值

• • • 分组取每组最小值

• • • 每组最大的N条记录

八、练习

Chestnut-J：SQL复杂查询—练习（四）​zhuanlan.zhihu.com

总结

以上是生活随笔为你收集整理的写一个sql实现以下查询结果_SQL复杂查询—知识点梳理（四）的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。