/*题外话
--更改foreign key约束定义引用行(delete cascade/delete set null/delete no action),默认delete on action
--引用行(当主表条记录被删除时确定何处理字表外部码字段）：
--delete cascade : 删除子表所有相关记录
--delete set null : 所有相关记录外部码字段值设置NULL
--delete no action: 做任何操作
--left 以左表为主，左表中的数据都查询出来
--约束唯一 unique
--多对多
*/
drop table stud;
drop table course;
select * from USER_TABLES;
--创建学生表
create table stud(
id int primary key,
name varchar(30)
);
--课程表
create table course(
id int primary key,
name varchar(30),
hours int
);
--
create table sc (
sid int ,
cid int,
constraint sc_pk primary key(sid,cid),
constraint sc_fk1 FOREIGN key(sid) references stud(id),
constraint sc_fk2 FOREIGN key(cid) references course(id)
);
select * from sc;
--先写入几个学生
insert into stud values(1,'Jack');
insert into stud values(2,'张三');
insert into stud values(3,'李四');
insert into stud values(4,'Rose');
--再写入几个课程
insert into course values(101,'Java',120);
insert into course values(102,'C#',60);
insert into course values(103,'Oracle',75);
insert into course values(104,'.NET',60);
commit;
--开始选课
insert into sc values (1,101);
insert into sc values (1,102);
insert into sc values (2,101);
insert into sc values (3,104);
commit;
-----------------------------------------------
-------------------开始查询---------------------
-----------------------------------------------
--查学生选了什么课
select s.name as 学生,c.name as 成绩
from stud s,course c,sc
where s.id=sc.SID and c.id=sc.CID;
-----inner join
select s.name ,c.name from stud s
inner join sc on s.id=sc.sid
inner join course c on c.id=sc.cid;
--查询没有选课的
select s.name,c.name from stud s
LEFT join sc on s.id=sc.SID
LEFT join COURSE c on c.ID=sc.cid
where c.name is null;
select s.name from course c
right join sc on c.id=sc.cid
right join stud s on s.id=sc.sid
where c.name is null;
--查询那些课没人选
select s.name,c.name as cname
from stud s
right join sc on s.id=sc.sid
right join course c on sc.cid=c.id
where s.name is null;
select s.name,c.name as cname
from course c
left join sc on c.id=sc.cid
left join stud s on sc.sid=s.id
where s.name is null;