/*題外話
--更改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;
