SQL Where语句实现除法

SQL Where语句实现除法

文章目录


前言

在老师一次考试题中总结出来的感想,而已。

一、SQL代数除法是什么?

‎前提条件:‎
‎在 A/B 中,B 中的属性必须包含在 A 的架构中。此外,结果具有属性 A-B‎

定义:给定关系R(X,Y)和S(Y,Z),其中X,Y,Z为属性组。R中Y与S中的Y可以有不同的属性名,但必须出自相同的域集。R与S的除运算可以得到一个新的关系P(X),P是R中满足下列条件的元组在X 属性列上的投影: 元组在X上的分量值x的像集Y(x)包含S在Y上的投影的集合。

二、从题目进行理解

1.例题

SQL Where语句实现除法

这里以A/B2作为研究对象
可以看出除法的作用相当于找出A表中同时包含B表属性的sno

2.代码解析(Not Exist的原理)

Not Exist是Exists的对立面
Exists很好理解,如果存在则返回真
那么作为对立面Not Exists就是如果不存在则返回真

先来一个简单的应用理解一下

下表是我们使用的三个表
这里省略了一些这里用不上的属性
表名 属性
Student SID
Courses Course_no
Enrollment SID Course_no

SQL Where语句实现除法SQL Where语句实现除法SQL Where语句实现除法

查询选择了课程a的学生
select * from Student where exists 
(select SID from Enrollment
where Courses_no='a' and SID=Student.SID) 
//返回1
学号为2的学生没有选修的课程
select Courses_no from Courses where not exists
(select Courses_no from Enrollment 
where Courses_no=Courses.Courses_no and SID=2) 
//返回c,d

大家可以画表辅助理解,尝试用自己的方式去理解not exist并且在下面的应用中进行验证,就是你对not exist的直观理解。
我的理解可能是有些不对的,但是辅助我理解了not exist并且能够使用。
有问题的地方也麻烦大家指教!

那么我们先尝试用一道题目来感受一下Not Exist的嵌套,关于我用除法的解题灵感也是从这里面来的
SQL Where语句实现除法

由于内层连接涉及到了外层属性,是correlated query,执行顺序是从外到内
从常规逻辑去解释就是:寻找 没有 一门课 没有 选修的同学
select DISTINCT SID from Students s where not exists //
   (select * from Courses c where not exists 
   (select * from Enrollment 
   where SID = s.SID 
   and Course_no = c.Course_no))
   //返回1
   //内层返回了2的结果集,再进行第一层not exist,最终结果是1

至此,我们应该已经对Not Exist有了一定

3.题目(Not Exist嵌套以及用两张表实现除法)

下面终于进入了我们最开始的例题。

SQL Where语句实现除法
我们前面说到除法的作用相当于找出A表中同时包含B表属性的sno。回想一下,是不是和我们寻找选修了所有课程的学生是一个意思,那么我们是不是可以用之前的Not Exist去实现这个算法呢?
但是区别在于我们只有两张表,但是学生课程有三张表。

那我们就要想,怎样用两张表去实现三张表的嵌套呢?
其实很简单,我们复制一张表就好了。

但是,复制那一张表呢?
复制非决定性表格(你也可以尝试一下,每张都尝试一下,这仅是我的经验之谈orz)。你可以理解成最外层的表,他的改变不影响我们最终的结果。

那么我们试一下吧!

SELECT DISTINCT sno FROM A a1 WHERE NOT EXISTS
(SELECT pno FROM B WHERE NOT EXISTS
(SELECT * FROM A a2 WHERE a1.sno=sno AND a2.pno=pno)
//输出P1,P4
//你现在可以尝试套用一下你刚刚发现的理论

实际上Not Exists是使用了相关子查询的一个循环查询语句,查询了很多资料,很多人都说,提取最外层的一条数据+中层的一条数据=?内层的数据,但是在这种情况下是解释不通的,也可能是我理解错误了,希望有理解到底怎么进行循环对比的大佬能够指教一下!!或者小伙伴发现的相关传送门也麻烦告知我一下,非常感谢!!这个问题已经困扰了我半个学期了QQQQQAQQQQQQQQQ

2.考试习题(代数除法与SQL语句互转)

首先我们将上面的 “选择了所有课程的学生” SQL语句先写成代数表达式来看看。
Π 2 ( ( Π ( E n r o l l m e n t ) ÷ Π ( C o u r s e s ) ) ∞ S t u d e n t ) \Pi_2((\Pi(Enrollment)\div\Pi(Courses))\infty Student) Π2​((Π(Enrollment)÷Π(Courses))∞Student)
SQL Where语句实现除法

//Example 3
SELECT SNAME FROM s WHERE NOT EXISTS
(SELECT * FROM p WHERE NOT EXISTS
(SELECT * FROM sp WHERE s.S=S AND P=p.P))
//输出S1
//Example 4
SELECT DISTINCT S FROM sp sp1 WHERE S!='S2' AND NOT EXISTS
(SELECT * FROM sp sp2 WHERE S='S2' AND NOT EXISTS
(SELECT * FROM sp WHERE P=sp2.P AND S=sp1.S))
//输出S1

总结

我个人认为所有的除法都能够用这个嵌套公式去实现,这里展示了一个Not Exist的简单情况,两个Not Exist的嵌套情况,只有两张表实现嵌套Not Exist,以及将嵌套Not Exist与除法代数进行互换。
在Not Exist的总结中应该算是比较全面的了,这篇博文在数据库SQL测试的时候就想写,一直拖到了现在,也算是一个复习吧。
希望能对你有帮助,也希望有大佬能够指点一下 ~ 感谢 ~

上一篇:oracle与sql server大批量存储过程可以替换部分


下一篇:小程序图表绘制库