一、集合概念

集合概念

二、数据库操作

2.1 制造数据

#创建表class_1
CREATE TABLE `class_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `sname` varchar(64) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
);

#创建表class_2
CREATE TABLE `class_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `sname` varchar(64) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
);

#插入数据到class_1
insert into class_1(sname) values('sname_01'), ('name_02'), ('name_03'), ('name_04'), ('name_05'), ('name_06'), ('name_07'), ('name_08');

#插入数据到class_2
insert into class_2(sname) values('sname_06'), ('name_07'), ('name_08'), ('name_09'), ('name_10');

2.2 并集UNION

子语句SELECT 必须拥有相同数量的列(字段), 且列的数据类型也相同

SELECT sname from class_1 WHERE sname is not NULL
UNION
SELECT sname from class_2 WHERE sname is not NULL;

2.3 交集JOIN

#第一种方式
SELECT s1.sname FROM
(SELECT sname from class_1 WHERE sname is not NULL) as s1
JOIN
(SELECT sname from class_2 WHERE sname is not NULL) as s2
ON s1.sname=s2.sname;
#第二种方式
select class_1.sname from class_1 join class_2 on class_1.sname=class_2.sname;

2.4 差集（LEFT JOIN，RIGHT JOIN）

#class_1对class_2的差集（LEFT JOIN）
#s1对s2的差集, select就可以使用s1.sname
SELECT * FROM 
(SELECT sname from class_1 WHERE sname is not NULL) as s1
LEFT JOIN
(SELECT sname from class_2 WHERE sname is not NULL) as s2
ON s1.sname=s2.sname
WHERE s2.sname is NULL;   // 限定s1有, s2没有的记录
#等价于
select class_1.sname from class_1 left join class_2 on class_1.sname=class_2.sname where class_2.sname is null;

[图片上传中...(image.png-4187e9-1666433934466-0)]

#class_2对class_1的差集（RIGHT JOIN）
SELECT * FROM
(SELECT sname from class_1 WHERE sname is not NULL) as s1
RIGHT JOIN
(SELECT sname from class_2 WHERE sname is not NULL) as s2
ON s1.sname=s2.sname
WHERE s1.sname is NULL;

#等价于
select class_2.sname from class_1 right join class_2 on class_1.sname=class_2.sname where class_1.sname is null;

2.5 补集

class_1与class_2的补集 = class_1对class_2的差集 + class_2对class_1的差集

SELECT s1.sname FROM
(SELECT sname from class_1 WHERE sname is not NULL) as s1
LEFT JOIN
(SELECT sname from class_2 WHERE sname is not NULL) as s2
ON s1.sname=s2.sname
WHERE s2.sname is NULL
UNION
SELECT s2.sname FROM
(SELECT sname from class_1 WHERE sname is not NULL) as s1
RIGHT JOIN
(SELECT sname from class_2 WHERE sname is not NULL) as s2
ON s1.sname=s2.sname
WHERE s1.sname is NULL;

#等价于
select class_1.sname from class_1 left join class_2 on class_1.sname=class_2.sname where class_2.sname is null
union
select class_2.sname from class_1 right join class_2 on class_1.sname=class_2.sname where class_1.sname is null;