MySQL學習筆記2（多表操作）

外鍵：使兩張表之間存在關聯

特點：

1.從表外鍵的值是對主表主鍵的引用

2.從表外鍵類型，必須與主表主鍵類型一致

示例：

創建兩個表並準備數據：

USE mybase;
CREATE TABLE category(
    cid VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100)
);
CREATE TABLE product(
    pid VARCHAR(32) PRIMARY KEY,
    pname VARCHAR(40),
    price DOUBLE,
    category_id VARCHAR(32)
);
INSERT INTO category(cid,cname) VALUES('c001','家電');
INSERT INTO category(cid,cname) VALUES('c002','服飾');
INSERT INTO category(cid,cname) VALUES('c003','化妝品');

INSERT INTO product(pid,pname,price,category_id) VALUES('p001','聯想','5000','c001');
INSERT INTO product(pid,pname,price,category_id) VALUES('p002','海爾','5000','c001');
INSERT INTO product(pid,pname,price,category_id) VALUES('p003','雷神','5000','c001');

INSERT INTO product(pid,pname,price,category_id) VALUES('p004','JACK JONES','800','c002');
INSERT INTO product(pid,pname,price,category_id) VALUES('p005','真維斯','200','c002');
INSERT INTO product(pid,pname,price,category_id) VALUES('p006','花花公子','440','c002');
INSERT INTO product(pid,pname,price,category_id) VALUES('p007','勁霸','2000','c002');

INSERT INTO product(pid,pname,price,category_id) VALUES('p008','香奈兒','800','c003');
INSERT INTO product(pid,pname,price,category_id) VALUES('p009','相宜本草','200','c003');

執行這段代碼沒有問題：

DELETE FROM category WHERE cid='c003';

添加外鍵關係：

ALTER TABLE product ADD FOREIGN KEY(category_id) REFERENCES category(cid);

這時候兩個表中的數據就存在外鍵關係

執行這段代碼就會出錯：

DELETE FROM category WHERE cid='c003';

 如果想成功，需要先刪除和它有關的product表中的兩條數據

如果從表添加數據，不能添加主表中不存在的記錄，例如添加category_id為c004的數據

表與表之間的關係：

一對多：

實例：客戶和訂單，分類和商品

一個分類對應多個商品，一個商品只能屬於某個分類

上邊這個實例，就是一個一對多案例

多對多：

實例：學生選課

多名學生，選擇多門課程，學生隨意選擇

需要註意多對多的建表方法：

創建第三張表作為中間表，至少兩個欄位

這兩個欄位分別作為外鍵指向各自一方的主鍵：

具體代碼：

CREATE TABLE 從表(
    欄位1，
    欄位2，
    ...
)
ALTER TABLE 從表 ADD FOREIGN KEY(欄位1) REFERENCES 主表1(主鍵);
ALTER TABLE 從表 ADD FOREIGN KEY(欄位2) REFERENCES 主表2(主鍵);

一對一：

 實例：公司的地址

一個公司對應一個地址，一對一

實際開發中不使用，通常寫在一張表中即可

多表查詢：

 使用上邊創建的兩張表以及數據：

兩張表的全集
SELECT * FROM category,product;


內連接查詢
SELECT * FROM category INNER JOIN product ON cid=category_id;

SELECT * FROM category c,product p WHERE c.cid=p.category_id;


外連接查詢
SELECT * FROM category LEFT JOIN product ON cid=category_id;

SELECT * FROM category RIGHT JOIN product ON cid=category_id;



子查詢

SELECT cid FROM category WHERE cname='化妝品';

SELECT * FROM product WHERE category_id=(SELECT cid FROM category WHERE cname='化妝品');