SQL语句中的嵌套子查询「建议收藏」
文章目录
一开始在学习的SQL语句的时候,没有感受到嵌套子查询的厉害,尤其是相关子查询。现在发现它的厉害之处,写下来记录!
相关子查询
先抛出一个问题来引出这个话题。查找每个学生超过他自己选修课程平均成绩的课程号。看到这个问题,首先有两点我们是不知道的。第一:每一个学生的到底选了什么课程。(有人可能会说i=,选课表SC不就告诉你了吗?的确,选修表SC是告诉我们了,但是我们也得去查哈。SC又不是已经把每一个学生的选课都变成了一张表,你直接select *就完了。所以,这里我们认为每一个学生到底选了什么课程,还是未知的)。第二:我们不知道学生的选课的平均成绩。
那么,问题出来了。思路也就出来了。我们首先求得每一个学生的选课记录,然后取其平均值。然后要每个学生的每一门选课都和自己的平均成绩去比较,如果高出平均成绩就放入结果集。现在,给出SQL语句:
select Sno,Cno
from tb_SC x
where Grade >(
select AVG(Grade)
from tb_SC y
where x.Sno=y.sno
)
这个是tb_SC表的部分数据
它的执行流程我觉得是这样的: 首先,从x(tb_SC)表中拿出一条记录,例如第一条数据。然后用这条数据和内层查询的y(tb_SC)表中的每一条数据做比较,如果满足x.Sno=y.Sno,就抽出来到tmp表中去(这个tmp表是我自己想出来的,并于理解)。直到把y表的数据比配完后,tmp表中的就是所有20173824001的学生的选课记录了。然后使用内置函数avg得到平均分。返回给上层循环。然后去判断第一条记录的Grade是否大于平均分。之后的每条记录也可使用类似的方法分析。
其实每一个相关子查询就是一个二重for循环。上面的例子使用c语言来描述的话:
static i=0;
for(;i<x.length;i++)
{
for(int j=0;j<y.length;j++)
{
int index=0;
if(x.Sno==y.Sno)
{
tmp[index]=y[j].Grade;
index++;
}
}
//这里的return avg(tmp)按在c语言中可能有点歧义,大家能理解就好
return avg(tmp);
}
写一个我当时觉得正确的SQL语句,也是针对这题的:
select Sno,Cno
from tb_SC
where Grade >(
select AVG(y.Grade)
from tb_SC x,tb_SC y
where x.Sno=y.sno
)
我当时就觉得,为什么一定要使用相关子查询呢?不使用相关子查询也没有问题啊。但是事实告诉我是有问题的。上面的SQL语句计显示出来的结果并不是真正的结果。所以,我就发现了一个规律:什么时候使用相关子查询: 如果你想要使用一个表中的数据逐个和另一个表中的数据比较,这个时候可以使用相关子查询。就相当于二重for循环。
那再来一个高级一点的例子,难度大一点的。求:选修了所有课程的学生的学号和姓名。这里我们再来分析一哈未知数。第一:有多少门选修课程我们不知道(可以使用Course表得到)。第二:学生选了哪几门课我们不知道(可以通过SC表得到)。因为SQL中是没有全称量词的(这里就是“所有”),所有我们只能通过存在量词等价转化为全称量词。那么这里就是:“没有一门课是他不选修的!”代表的就是这个学生选修了所有的课程。给出SQL语句:
select Sno,Sname
from tb_Student
where not exists
(
select *
from tb_Course
where not exists
(
select *
from tb_SC
where Sno=tb_Student.Sno
and Cno=tb_Course.Cno
)
)
这里的意思就是说:
- 从tb_Student中拿出一条数据 1.1 然后从tb_Course表中拿出一条数据
- 然后用这两条数据去tb_SC表中看有没有有这样的记录存在。即Sno=tb_Student.sno的同时,Cno=tb_Course.Cno
- 如果没有这样的数据,说明这个学生没有选修这门课,所有最内存循环为false。导致最内层的not exists返回ture.这样子,最外层的not exists返回false。那么,这条记录就不能放到最终结果集中。
- 如果有这样的一条记录,证明这个学生选过这门课,那么返回到第1.1步,然后取出tb_Course中的第二条数据。
我这里其实是有一个疑问的: 在步骤3中,如果这个学生没有选修这门课,那么这个最佳情况应该直接跳到第1步,然后取出二条tb_Student的数据。但是DBMS内部是不是这样做的,这个我就不知道了。我觉得应该不是这样做的吧。也希望大佬们在下面留言,说说自己的看法。
然后这里给出一种使用除法的思想的SQL语句:
select Sno
from tb_SC as SC_1
where not exists(
select Cno
from tb_Course
except
select Cno
from tb_SC as SC_2
where SC_1.Cno=SC_2.Cno)
自身连接
最后再来说一哈关于自连接的小问题。这个就是为了之后复习的时候,不要再犯这么低级的错误。题目问的是:既选修了0002也选修了0004号课程的学生。我一开始写的SQL是这样的:
select Sno
from tb_SC
where Cno='0002' and Cno='0004';
但是这个明显就有一个问题,怎么可能会有一个Cno在等于0002的同时,也等于0004。所以这样的SQL语句的出来的结果必然是空集。正确的结果是这样的:
select x.Sno
from tb_SC x,tb_SC y
where x.Sno=y.Sno and x.Cno='0002' and y.Cno='0004';
就是自连接的表格可能我一开始没有想像到。例如:
就是这样的,当然我也没有全部弄出来。大概的意思应该可以看懂。这个的缺点就是有一些没有用处的的组合也出来了,当然这个也是无法避免的。
还有一个要注意的问题就是:这里自身连接的条件是x.Sno=y.Sno;不是x.Cno=y.Cno;是因为你是要同一个人既选修0002,也选修0004。只有x.Sno=y.Sno的时候,一条元组才会代表一个人同时选修的课程,如果是x.Cno=y.Cno,代表的是这一门课同时被几个人选修!
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/169438.html原文链接:https://javaforall.cn
相关文章
- 动态sql模糊查询和分页「建议收藏」
- 在SQL Server中查询资料库的TABLE数量与名称的sql语句
- MySQL联表查询:轻松搞定复杂SQL语句(mysql联表查询语句)
- MySQL分页查询:实现快速简单的SQL语句(mysql分页查询sql语句)
- 查询 MySQL查询:从SQL语句中构建你的查询(sql语句mysql)
- MySQL 跟踪SQL痕迹:如何调试查询问题(mysql跟踪sql)
- Mysql中删除记录的SQL语句(mysql删除sql语句)
- 查询MS SQL日志:最佳工具推荐(查询mssql日志的工具)
- MySQL SQL优化,提高查询效率。(mysql中的sql优化)
- 查询使用SQL Server实现完整关联查询(sqlserver全关联)
- 深入探讨MySQL主键与SQL语句使用技巧(mysql主键sql)
- 查询使用MSSQL对多表进行复杂SQL查询(sql mssql 多表)
- 了解Oracle跟踪SQL的必备工具(oracle跟踪sql工具)
- Oracle查询:锁定表的SQL语句(oracle查询锁表sql)
- MSSQL查询SQL日志:深入了解服务器运行情况(mssql查询sql日志)
- MySQL操作如何使用SQL窗口进行数据查询和修改(mysql 中sql窗口)
- 将SQL查询结果存入Redis缓存(sql结果存入redis)
- SQL访问Redis新技术所带来的变革(sql 访问 redis)
- MySQL与SQL不同,你知道这些区别吗(mysql 不等于sql)
- 查询快速精准查询Oracle SQL语句(oracle sql快捷)
- 查询Oracle SQL当天查询实战揭秘不一样的技术(oracle sql当天)
- Oracle SQL实战从初学者到专家(oracle sql实战)
- SQL语言查询基础:连接查询联合查询代码
- 交叉表查询sql语句