关于集合除法的理解（MySQL实现）_集合的除法

作者写这篇博客的目的是为了记录一些心得体会，不会之处多担待

这里我不想赘述集合除法是什么，直接引述集合除法的实现和对实现集合除法过程的理解

我这里通过一个例子进行解释

现在我有一个存储学生信息的表格 如下图

 
# 创建学生的表格
create table Stu(
	SNO char(4) primary key not null, -- 学号 
	SNAME char(8) not null, -- 姓名 
	SEX tinyint, -- 性别 对这题实际上没用
	MNO char(2), -- 对应专业的编号 如软件工程的编号01 对这题实际上没用
	BIRDATE datetime, -- 出生日期 对这题实际上没用
	MEMO text -- 评论 对这题实际上没用
);
 
#学生信息的插入部分
insert into stu(sno,sname,sex,mno,birdate,memo,email) values
('S001','张三',1,'01','2001-10-01 00:00:00','二年级，荣获学院三好学生','1234@1234.com'),
('S002','李四',0,'02','2001-05-13 00:00:00',NULL,'1234@1234.com'),
('S003','李芳',0,'01','2000-05-11 00:00:00',NULL,'1234@1234.com'),
('S004','张明',1,'02','2002-05-31 00:00:00','一年级，荣获优秀学生干部','1234@1234.com'),
('S005','张强',1,'02','2001-06-01 00:00:00',NULL,'1234@1234.com'),
('S006','吴玲',0,'02','2000-06-11 00:00:00',NULL,'1234@1234.com'),
('S007','郑奇',1,'01','2000-06-11 00:00:00',NULL,'1234@1234.com'),
('S008','吴丽丽',0,'03','2002-06-17 00:00:00',NULL,'1234@1234.com'),
('S009','林森',1,'03','2003-01-10 00:00:00',NULL,'1234@1234.com'),
('S010','徐小宜',1,'04','2003-05-11 00:00:00',NULL,'1234@1234.com');

有一个记录课程的表

创建cou表的sql语句

create table Cou(
	CNO char(4) primary key not null, -- 课程编号 如：C001
	CNAME varchar(20) not null, -- 课程的名称 如：软件工程
	CREDIT smallint, -- 学分 实际没有用
	PTIME char(5), -- 学时 实际没有用
	TEACHER char(10) -- 教师 这个很有用
);
 
insert into cou(cno,cname,credit,ptime,teacher) values
('C001','高等数学',8,'1','孙老师'),
('C002','C语言',5,'2','张老师'),
('C003','数据结构',4,'3','张老师'),
('C004','操作系统',3,'4','罗老师'),
('C005','组成原理',4,'3','陈老师'),
('C006','高等数学',3,'5','吴老师'),
('C007','JAVA程序设计',3,'3','李老师');
select *from cou;

有一个记录学生选课的表

 

 

对应的SQL语句

create table Sc(
	SNO char(4) not null, -- 学生的学号
	CNO char(4) not null, -- 课程的编号
	GRADE decimal(4,1), -- 学生在该课程的得分 实际没有用
	primary key(SNO,CNO)
);
 
insert into sc(sno,cno,grade) values
('S001','C001',68.6),
('S001','C002',95.5),
('S001','C003',74.5),
('S002','C001',70),
('S002','C002',86.1),
('S003','C001',89.3),
('S003','C005',78.8),
('S003','C006',67.6),
('S004','C002',67.6),
('S004','C004',50.2),
('S005','C002',80.9);

至于我为什么要写那么多的没用的 因为懒 有现成的 哈哈哈哈哈哈哈哈哈 

现在我们题目的要求是这样的，从学生这张表中选出至少选修了张老师所有课程的学生

也就是说某个学生一旦选修了如下所有课程 那么我们认为这位学生满足我们的要求

1.取巧的集合除法实现：

题目的要求是选出所有至少选修了张老师所有课程的学生，那么我们先选出张老师的所有课程再说

select * from cou where teacher='张老师';

 

我们再看看有多少学生有选修张老师的课程（注意：我们这里只是有选修，而不是选修了全部）

select * from sc where sc.cno in
(select cno from cou where teacher='张老师');

   

 

 

那么直观的理解上看 如果我有选修了张老师的科目，并且我选修张老师的科目数与张老师所教授的课程数一致 那么我就是选修了张老师的所有科目。（当然 这是建立在数据正确的情况 即不存在我连续选修了张老师同一课程这一种情况）

select sno from  -- 选中学号
 
	(select * from sc where sc.cno in (select cno from cou where teacher='张老师')) a 
    -- 有选修张老师课程的表
 
group by sno -- 以学号为分组条件 分学号计算不同学号选修张老师的课程数
 
having count(a.cno)=(select count(cno) from cou where teacher='张老师'); 
-- 判断条件为选修张老师的科目数与张老师所教授的课程数一致

 

2.我所理解的集合除法：

上边的方法是先找选修了张老师科目的学生 然后统计个数找到答案

而下面的方法有点反着来

我们先找没有选修张老师科目的学生，然后从学生中剔除这些学生

(⊙﹏⊙) 这怎么做到“找出所有没有选修张老师科目的学生” 我是这么理解：

从特殊到一般：

先取一个具体的学生，如果这个学生有某个张老师的科目没有选修，那么这个学生满足我们的要求，抽出来保留在我们的剔除名里

如：我们取S001，S002这两名学生举例

select cno from cou where teacher='张老师'
-- 选出张老师的课程
and -- 并且
-- 选出S001这名学生缺的张老师课程
cno not in (select cno from sc where sc.sno='S001');

输出的结果

这说明S001这名学生选修的课程中没有缺失张老师的任意一节课，即S001这名学生选修了张老师所有的课程 那么这么学生不保留在我们的剔除名单里

对于S002 来说

select cno from cou where teacher='张老师'
-- 选出张老师的课程
and -- 并且
-- 选出S002这名学生缺的张老师课程
cno not in (select cno from sc where sc.sno='S002');

输出的结果

 这说明S002这名学生选修的课程中缺失张老师的一节课 并且课程号为"C003"，即S003这名学生并没有选修了张老师所有的课程 那么这么学生就应当保留在我们的剔除名单里

从一般的角度来看：

如果我们能让上面的SQL语句中的 sc.sno部分依次等于学生表中的sno 并将满足条件的sno保留在我们的剔除名单里，那么我们的剔除名单就完成了

 那么我们必须要有的几个模块

1.    select sno from stu  这个模块是依次引用学生表中的学号 

2.  where exists 这个是链接上下两个模块的

3. 这个模块是为了判断当前的stu.sno是否有少选修张老师的科目

select cno from cou where teacher='张老师'
and 
cno not in (select cno from sc where sc.sno=stu.sno);

 总的来看 （以下就是剔除名单）

select sno from stu 
where exists 
(select cno from cou where teacher='张老师'
and 
cno not in (select cno from sc where sc.sno=stu.sno));

 

解释一下过程：（我个人的理解）

假如从stu表中取出的sno为’S001‘ 此时

select cno from cou where teacher='张老师'
and 
cno not in (select cno from sc where sc.sno='S001')

由于上面的结果是空集（也就是说上面的查询为空），那么此时的

查询可以理解为 

尝试取出 'S001' where exists '空'--------》

尝试取出 'S001' where false -----》

从stu取出的'S001不满足条件，所有最终的剔除名单里没有S001

假如从stu表中取出的sno为’S002‘ 此时

 select cno from cou where teacher='张老师'
and 
cno not in (select cno from sc where sc.sno='S002')

由于上面的结果不是空集 那么此时可以理解为

尝试取出 'S002' where exists '集合非空'--------》

尝试取出 'S002' where true -----》

从stu取出的'S002满足条件，所有最终的剔除名单里就有S002

但此时我们得到的只是剔除名单 我们要的不是剔除名单啊！！！

简单的做法是

select sno from stu where
not exists  -- 这里加个not 就可以了
(select cno from cou where teacher='张老师'
and 
cno not in (select cno from sc where sc.sno=stu.sno));

复杂一点 但是跟我们的初始想法一致

select sno from stu -- 从学生表出发
where sno not in -- 查找学号不在剔除名单里的
 
-- 学生剔除名单
(select sno from stu where
    exists 
(select cno from cou where teacher='张老师'
and 
cno not in (select cno from sc where sc.sno=stu.sno)));

 

 以上就是方某对于集合除法过程（以上例题）的理解 如有更好的解释方法 欢迎欢迎