※多表操作 (凡是多表，都要用到關聯技術(把多表合并成一個新表): 左關聯、右關聯、内關聯。還有一個外(全)關聯，MySQL不支援，為考慮軟體相容，我們開發一般不用。)
※表與表之間的關系:1對1，1對多，多對多
一、1對1
※第三範式: 1方建主表(id為主鍵字段), 多方建外鍵字段(husband--參考主表的主鍵id，加unique)
CREATE TABLE man(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
CREATE TABLE woman(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
husband VARCHAR(32) UNIQUE,
CONSTRAINT wm_fk FOREIGN KEY(husband) REFERENCES man(id)
);
注：husband這裡要加unique限制，不加則是一對多關系
加unique才是1對1關系
//DROP TABLE woman;
INSERT INTO man VALUES('1','小明');
INSERT INTO man VALUES('2','小聰');
INSERT INTO man VALUES('3','老王');
INSERT INTO woman VALUES('1','小花','2');
INSERT INTO woman VALUES('2','小靜','1');
INSERT INTO woman VALUES('3','小紅','1');//Error:違反1對1
INSERT INTO woman VALUES('3','小紅','10');//Error:違反外鍵--主表必須存在該外鍵值
INSERT INTO man VALUES('10','王六');
INSERT INTO woman VALUES('3','小玉','10');//OK
受unique限制，husband不能重複不然
husband是外鍵，同時受man中的id限制
//◇查詢夫妻資訊(内聯)
SELECT man.name AS 丈夫, woman.name AS 妻子 FROM man
INNER JOIN woman ON man.id=woman.husband;
SELECT m.name AS 丈夫, w.name AS 妻子 FROM man AS m
INNER JOIN woman AS w ON m.id=w.husband;
二、1對多
※第三範式: 1方建主表(id為主鍵字段), 多方建外鍵字段(pid--參考主表的主鍵id，不加unique)
CREATE TABLE person2(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
sex CHAR(1)
);
DROP TABLE car2;
CREATE TABLE car(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30),
price NUMERIC(10,2),
pid VARCHAR(32),
CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person2(id)
);
INSERT INTO person2 VALUES('P01','Jack','1');
INSERT INTO person2 VALUES('P02','Tom','1');
INSERT INTO person2 VALUES('P03','Rose','0');
INSERT INTO car VALUES('C001','BMW',30,'P01');
INSERT INTO car VALUES('C002','BEnZ',40,'P01');
INSERT INTO car VALUES('C003','Audi',40,'P01');
INSERT INTO car VALUES('C004','QQ',5.5,'P02');
//外鍵字段值可以為“NULL”表示該車還未賣出
INSERT INTO car(id,NAME,price) VALUES('C005','ABC',10);
INSERT INTO car(id,NAME,price) VALUES('C006','BCD',10);
//◇查詢哪些人有哪些車
SELECT * FROM car INNER JOIN person2 ON car.pid=person2.id;
SELECT person.NAME,car.name FROM person
INNER JOIN car ON person.id=car.pid;
//◇查詢Jack有哪些車
SELECT car.NAME,car.price,person2.name FROM car
INNER JOIN person2 ON car.pid=person2.id where person2.name='Jack';
//◇查詢哪些人有兩輛或兩輛以上的車
//失敗品:--每組隻顯示第一條
SELECT car.NAME,car.price,person2.name FROM car
INNER JOIN person2 ON car.pid=person2.id GROUP BY person2.id HAVING COUNT(car.pid)>=2;
//過渡版
SELECT car.NAME,car.price,person2.name FROM car
INNER JOIN person2 ON car.pid=person2.id WHERE person2.id IN('P01','P02');
SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2; //把上面寫死的pid寫成活的
//OK版
SELECT car.NAME,car.price,person2.name FROM car
INNER JOIN person2 ON car.pid=person2.id WHERE person2.id IN(
SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2
);
//示範左關聯: SELECT * FROM person2 LEFT JOIN car ON car.pid=person2.id;//由全表可知隻需條件是car.id或NAME或price或pid為空即可查出誰沒有車
//◇查詢哪些人沒有車
SELECT person2.name FROM person2 LEFT JOIN car ON car.pid=person2.id
WHERE car.id IS NULL;
其實右關聯跟左關聯一樣，隻需要把左關聯的表調換一下位置便成了右關聯的結果，是以隻要會了左關聯，右關聯也是一樣的。
//◇補一個外鍵的概念（預設是限制): 删除主鍵資訊時，當該主鍵字段值在外鍵表中存在時，該記錄是不能删除的。---要把外鍵表是的相關資訊删除之後，才能删除。 ---更新同理
DELETE FROM person2 WHERE id='P01';//car表中存在pid='P01'的車，是以主表不能删除'P01'這條記錄
三、多對多（ 3個表= 2個實體表 + 1個關系表 )
※第三範式: 兩個實體都建成獨立的主表, 另外再單獨建一個關系表（采用聯合主鍵）
1、分别建議兩個實體表(沒有外鍵，但有自己的主鍵, 沒有備援資訊)
//DROP TABLE stud;
//學生表
CREATE TABLE stud(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
//課程表
CREATE TABLE ject(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(30)
);
2、另外補建一個關系表
CREATE TABLE sj(
studid VARCHAR(32) NOT NULL,
jectid VARCHAR(32)
);
//注意，要先建聯合主鍵，再添加外鍵。順序不能反了。
ALTER TABLE sj ADD CONSTRAINT sj_pk PRIMARY KEY(studid,jectid);
ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studid) REFERENCES stud(id);
ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectid) REFERENCES ject(id);
//删除外鍵限制
//ALTER TABLE sj DROP FOREIGN KEY sj_fk1;
//ALTER TABLE sj DROP FOREIGN KEY sj_fk2;
3、添加一些示範資料
//實體表1
INSERT INTO stud VALUES('S001','Jack');
INSERT INTO stud VALUES('S002','Rose');
INSERT INTO stud VALUES('S003','Tom');
//實體表2
INSERT INTO ject VALUES('J001','Java');
INSERT INTO ject VALUES('J002','Oracle');
INSERT INTO ject VALUES('J003','XML');
INSERT INTO ject VALUES('J004','JSP');
INSERT INTO ject VALUES('J005','Game');
//關系表
INSERT INTO sj VALUES('S001','J001');
INSERT INTO sj VALUES('S001','J003');
INSERT INTO sj VALUES('S001','J004');
INSERT INTO sj VALUES('S002','J002');
INSERT INTO sj VALUES('S002','J003');
INSERT INTO sj VALUES('S002','J004');
//查詢哪些人選了哪些課
//SQL組織的1992标準，可用，但效率不高
SELECT stud.name, ject.NAME FROM stud,ject,sj WHERE stud.id=sj.studid AND ject.id=sj.jectid;
//SQL組織的1996标準，效率高，推薦使用---關聯
SELECT stud.name, ject.NAME FROM stud INNER JOIN sj ON stud.id=sj.studid
INNER JOIN ject ON ject.id=sj.jectid;
//查詢哪些人沒選課
SELECT stud.name FROM stud LEFT JOIN sj ON stud.id=sj.studid
LEFT JOIN ject ON ject.id=sj.jectid where ject.id is NULL;
//查詢哪些課沒人選
SELECT ject.name FROM stud RIGHT JOIN sj ON stud.id=sj.studid
RIGHT JOIN ject ON ject.id=sj.jectid where stud.id is NULL;