多表

1.1 多表簡述
實際開發中，一個項目通常需要很多張表才能完成。
例如一個商城項目的數據庫,需要有很多張表：用戶表、分類表、商品表、訂單表…
1.2 單表的缺點
1.2.1 數據準備

1. 創建一個數據庫 db3

CREATE DATABASE db3 CHARACTER SET utf8;

2. 數據庫中 創建一個員工表 emp ,
   包含如下列 eid, ename, age, dep_name, dep_location
   eid 為主鍵并 自動增長, 添加 5 條數據

-- 創建emp表 主鍵自增
CREATE TABLE emp(eid INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(20),age INT ,dep_name VARCHAR(20),dep_location VARCHAR(20)
);-- 添加數據 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('張百萬', 20, '研發部', '廣州'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('趙四', 21, '研發部', '廣州'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('廣坤', 20, '研發部', '廣州'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('小斌', 20, '銷售部', '深圳'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('艷秋', 22, '銷售部', '深圳'); INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('大玲子', 18, '銷售部', '深圳'); 

1.2.2 單表的問題
?冗余, 同一個字段中出現大量的重復數據

1.3 解決方案
1.3.1設計為兩張表
1)多表方式設計
?department 部門表 : id, dep_name, dep_location
?employee 員工表: eid, ename, age, dep_id
2) 刪除emp表, 重新創建兩張表

-- 創建部門表
-- 一方,主表
CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT,   dep_name VARCHAR(30),  dep_location VARCHAR(30)
);-- 創建員工表
-- 多方 ,從表
CREATE TABLE employee(eid INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(20),age INT,dept_id INT
);

3)添加部門表 數據

-- 添加2個部門 
INSERT INTO department VALUES(NULL, '研發部','廣州'),(NULL, '銷售部', '深圳'); SELECT * FROM department; 

4. 添加員工表 數據

-- 添加員工,dep_id表示員工所在的部門 
INSERT INTO employee (ename, age, dept_id) VALUES ('張百萬', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('趙四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('廣坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艷秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); SELECT * FROM employee;

1.3.2 表關系分析
部門表與員工表的關系
1.員工表中有一個字段dept_id 與部門表中的主鍵對應,員工表的這個字段就叫做 外鍵。
2.擁有外鍵的員工表 被稱為從表, 與外鍵對應的主鍵所在的表叫做主表。

1.3.3 多表設計上的問題
當我們在 員工表的 dept_id 里面輸入不存在的部門id ,數據依然可以添加 顯然這是
不合理的。

-- 插入一條 不存在部門的數據
INSERT INTO employee (ename,age,dept_id) VALUES('無名',35,3);

實際上我們應該保證,員工表所添加的 dept_id , 必須在部門表中存在.
解決方案:
?使用外鍵約束,約束 dept_id ,必須是 部門表中存在的id
1.4 外鍵約束
1.4.1 什么是外鍵
?外鍵指的是在 從表中 與主表的主鍵對應的那個字段,比如員工表的 dept_id,就是外鍵。
?使用外鍵約束可以讓兩張表之間產生一個對應關系,從而保證主從表的引用的完整性。

多表關系中的主表和從表
?主表: 主鍵id所在的表, 約束別人的表。
?從表: 外鍵所在的表多, 被約束的表。

1.4.2 創建外鍵約束
語法格式:
1)新建表時添加外鍵

[CONSTRAINT] [外鍵約束名稱] FOREIGN KEY(外鍵字段名) REFERENCES 主表名(主鍵字段名) 

2)已有表添加外鍵

ALTER TABLE 從表 ADD [CONSTRAINT] [外鍵約束名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表(主 鍵字段名);

3. 重新創建employee表, 添加外鍵約束

-- 先刪除 employee表
DROP TABLE employee;-- 重新創建 employee表,添加外鍵約束
CREATE TABLE employee(eid INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(20),age INT,dept_id INT,-- 添加外鍵約束CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

4. 插入數據

-- 正常添加數據 (從表外鍵 對應主表主鍵)
INSERT INTO employee (ename, age, dept_id) VALUES ('張百萬', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('趙四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('廣坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艷秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); -- 插入一條有問題的數據 (部門id不存在)
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO employee (ename, age, dept_id) VALUES ('錯誤', 18, 3); 

4)添加外鍵約束,就會產生強制性的外鍵數據檢查, 從而保證了數據的完整性和一致性

1.4.3 刪除外鍵約束
語法格式

alter table 從表 drop foreign key 外鍵約束名稱

刪除 外鍵約束

-- 刪除employee 表中的外鍵約束,外鍵約束名 emp_dept_fk
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

再將外鍵 添加回來, 語法格式

ALTER TABLE 從表 ADD [CONSTRAINT] [外鍵約束名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表(主 鍵字段名);

SQL示例

-- 可以省略外鍵名稱, 系統會自動生成一個
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

1.4.4外鍵約束的注意事項
1)從表外鍵類型必須與主表主鍵類型一致 否則創建失敗.

2. 添加數據時, 應該先添加主表中的數據.

-- 添加一個新的部門
INSERT INTO department(dep_name,dep_location) VALUES('市場部','北京');-- 添加一個屬于市場部的員工
INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);

3. 刪除數據時,應該先刪除從表中的數據.

-- 刪除數據時 應該先刪除從表中的數據
-- 報錯 Cannot delete or update a parent row: a foreign key constraint fails
-- 報錯原因 不能刪除主表的這條數據,因為在從表中有對這條數據的引用
DELETE FROM department WHERE id = 3;
-- 先刪除從表的關聯數據
DELETE FROM employee WHERE dept_id = 3;-- 再刪除主表的數據
DELETE FROM department WHERE id = 3;

1.5物理外鍵和邏輯外鍵
?物理外鍵
物理外鍵實際通過數據庫語法設置為外鍵.
?邏輯外鍵
邏輯外鍵是指，字段設置時不需要額外通過數據庫語法設置成外鍵關聯.