简单介绍
我们有两个实体,分别是学生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`
)
)
通过以上几道题的练习,不难发现,其实套路都是一样的。
最后用一张图草草了事