数据库中的集合操作

create table Student1(
       id int  primary key ,
       stuName varchar(20)
);

insert into Student1 (id,stuName) values(1,'allen');
insert into Student1 (id,stuName) values(2,'vivian');
insert into Student1 (id,stuName) values(3,'joe');
insert into Student1 (id,stuName) values(4,'jason');

create table Course1(
        id int  primary key,
        couName varchar(20)
)
select * from Course1;
insert into Course1 (id,couName) values(1,'math');
insert into Course1 (id,couName) values(2,'chinese');
insert into Course1 (id,couName) values(3,'english');

create table stu_cou(
        id int  primary key,
        stuId int,
        couId int
)
select * from stu_cou;
drop table stu_cou

insert into stu_cou(id,stuId,couId)values(1,1,1);
insert into stu_cou(id,stuId,couId)values(2,1,2);
insert into stu_cou(id,stuId,couId)values(3,1,3);
insert into stu_cou(id,stuId,couId)values(4,2,1);
insert into stu_cou(id,stuId,couId)values(5,2,2);
insert into stu_cou(id,stuId,couId)values(6,2,3);

求Student1中没有上数学课的学生

oracle中集合：

能用集合操作的表的条件：两个表的结构相同，也就是列的个数，列的顺序，列的数据类型一致

合集：union和union all ,其中，union去掉重复，union all不去掉重复，union排序，unoin all 不排序。

交集：intersect

差集：minus

select stuName from Student1 
minus
select stuName from Student1 left join stu_cou on Student1.id = stu_cou.stuid where couId = 1;

数据准确，省略关联了一个表，因为自己写三表关联时出现错误

在网上看三表关联应该是这样写，不用join

select stu.stuName,cou.couName from stu_cou sc ,Student stu,Course1 cou
where  sc.stuId = stu.id
and sc.couId = cou.id

执行可以查询到列，但是数据为空，

用左关联是这样写的：

select stu.stuName,cou.couName from stu_cou sc
left join Student1 stu left join Course1 cou
on sc.stuId = stu.id and sc.couId = cou.id

报 missing key words错误，原因是 一个left jion 配一个on

改为这样：

select stu.stuName,cou.couName from stu_cou sc 
left join Student1 stu on sc.stuId = stu.id
left join Course1 cou on sc.couId = cou.id

就查询出结果了