关系运算中的除操作怎么用SQL语句表示？ 有套路的！（双 NOT EXISTS）

简单介绍

我们有两个实体，分别是学生S、课程C，学生有学号、姓名、性别、年龄、院系等属性。课程有课程编号、课程名、学分等属性。一个学生可以选多门课，当然了，选同一门课的学生也可以有多个，因此这两个实体之间是多对多的关系。

E-R图

建表：

创建课程表C

CREATE TABLE `c` (
  `cno` varchar(5) NOT NULL,
  `cname` varchar(20) DEFAULT NULL,
  `credit` int(11) DEFAULT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建学生表S

CREATE TABLE `s` (
  `sno` varchar(15) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `gender` varchar(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `dept` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建选课表SC

CREATE TABLE `sc` (
  `sno` varchar(15) NOT NULL,
  `cno` varchar(5) NOT NULL,
  `grade` double DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  KEY `cno` (`cno`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `c` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

insert  into `s`(`sno`,`sname`,`gender`,`age`,`dept`) values ('2020001','张一','男',20,'CS'),('2020002','张二','女',18,'CS'),('2020003','张三','男',22,'IS'),('2020004','张四','男',30,'CS'),('2020005','张五','女',25,'IS');
insert  into `c`(`cno`,`cname`,`credit`) values ('C001','数据结构',4),('C002','操作系统',4),('C003','计算机网络',2);
insert  into `sc`(`sno`,`cno`,`grade`) values ('2020001','C001',98),('2020001','C002',100),('2020001','C003',95),('2020002','C001',100),('2020002','C002',100),('2020003','C001',100),('2020003','C002',88),('2020003','C003',96),('2020004','C001',89),('2020005','C001',76);

简单看下数据

SELECT * FROM s;

SELECT * FROM c;

SELECT s.*, c.*, sc.`grade`
FROM s, c, sc
WHERE s.`sno` = sc.`sno` 
AND c.`cno` = sc.`cno`

正式进入主题，怎么用SQL语句表示关系代数的除法操作

question 1: 查找出选修了全部课程的学生
question 2: 查找出所有学生都选修的课程

先来看第一个问题：查找出选修了全部课程的学生
先写出关系代数：
① 全部课程的课程号：∏cno（C）
②除：∏sno, cno(SC) ÷ ∏cno（C）

那如何将关系代数转换成SQL语句呢？SQL里面没有除操作，也没有全程量词，如果有全程量词的话其实也是很容易的。但是有存在量词EXISTS 和 NOT EXISTS 我们可以用两个 NOT EXISTS 实现全程量词。

我总结了下，基本上像这种除操作都可以套用下面的骨架去写，很方便
骨架：查询做了所有(至少)A的B

SELECT * FROM B
WHERE NOT EXISTS(
	SELECT * FROM A
	WHERE NOT EXISTS(
		# 用中间表将A,B连接起来
		SELECT * FROM 中间表
		WHERE 中间表.key1 = A.key AND 中间表.key2 = B.key
	)
)

对于question 1：查找出选修了全部课程的学生 。
我们首先确定A、B和中间表，显然A是课程表C，B是学生表S，中间表是SC, 于是很容易就可以写出部分代码

SELECT * FROM s
WHERE NOT EXISTS(
	SELECT * FROM c
	WHERE NOT EXISTS(
		# 用中间表SC将A,B连接起来
	)
);

又因为中间表是SC，于是我们就可以通过SC表将这S表和C表连接起来

SELECT * FROM s
WHERE NOT EXISTS(
	SELECT * FROM c
	WHERE NOT EXISTS(
		SELECT * FROM sc
		WHERE sc.`cno` = c.`cno` AND sc.`sno` = s.`sno`
	)
);

查询结果：

我们查出所有数据来对比一下

SELECT s.*, c.*, sc.`grade`
FROM s, c, sc
WHERE s.`sno` = sc.`sno` 
AND c.`cno` = sc.`cno`

确实，只有张一和张三选修了全部课程。

我们再看question 2:查询全部学生选修的课程
确定A、B和中间表，显然A是学生， B是课程，中间表是SC

SELECT * FROM C
WHERE NOT EXISTS(
	SELECT * FROM S
	WHERE NOT EXISTS(
		SELECT * FROM SC 
		WHERE SC.`cno` = C.`cno` AND SC.`sno` = S.`sno`
	)
)

查询结果

ok, 完成！

要背一下那个骨架哦

查询做了所有（至少）A的B

SELECT * FROM B
WHERE NOT EXISTS(
	SELECT * FROM A
	WHERE NOT EXISTS(
		# 用中间表将A,B连接起来
		SELECT * FROM 中间表
		WHERE 中间表.key1 = A.key AND 中间表.key2 = B.key
	)
)

扩展问题(1)

查询至少选修了C001号课程和C002号课程的学生信息
其实套路是一样的，我们来分析一下A、B 和中间表是什么
A：C001号课程和C002号课程，说白了还是课程表，只不过加了点条件
B：学生
中间表：SC

所以骨架稍加修改还是照样用：

SELECT * FROM S
WHERE NOT EXISTS(
	SELECT * FROM C WHERE C.`cno` IN ('C001','C002')
	AND NOT EXISTS(
		SELECT * FROM SC
		WHERE SC.`cno` = C.`cno` AND SC.`sno` = S.`sno` 
		
	)
)

查询结果：

扩展问题2（可忽略）

《数据库系统概论 第五版》王珊，萨师煊
第72页： 求至少使用了供应商S1所供应的全部零件的工程号JNO

分析：
A: 供应商S1所供应的全部零件
B: 工程号JNO
中间表：SPJ

写代码

SELECT * FROM J
WHERE NOT EXISTS(
	SELECT * FROM P WHERE P.`pno` IN (SELECT DISTINCT SPJ.`pno` FROM SPJ WHERE SPJ.`sno` = 'S1')
	AND NOT EXISTS(
		SELECT * FROM SPJ 
		WHERE SPJ.`pno` = P.`pno` AND SPJ.`jno` = J.`jno`
	)
)

通过以上几道题的练习，不难发现，其实套路都是一样的。
最后用一张图草草了事