寫在前面：
如果文章對你有幫助，記得點贊關注加收藏一波，利于以后需要的時候復習，多謝支持！

---

【MySQL數據庫學習】系列文章

第一章 《認識與環境搭建》
第二章 《數據類型》
第三章 《數據定義語言DDL》
第四章 《數據操作語言DML》
第五章 《約束》
第六章 《數據查詢語言DQL》
第七章 《多表操作》

---

---

一、多表關系

（一）多表關系概念

在實際的項目中，往往需要進行處理多表數據，而多表的關系通常可以概括為以下幾種。

• 一對一關系：例如一個學生只有一個身份證號，表現為一張表的一行對應另一張表的一行。但這種關系使用較少，因為通常一對一關系可以合成為一張表。
• 一對多關系：例如一個部門有著多個員工，表現為一張表的一行對應另一張表的多行。
• 多對多關系：例如學生和選課之間，一個學生可以選多節課，而一節課也可以被多個學生所選，表現在一張表對應另一張表的多行的同時，另一張表的一行也對應這張表的多行。通常多對多的關系需要中間表將其分割為一對多的關系。

（二）外鍵約束

外鍵約束會在表中建立一種關系，這種關系使得從表（子表）中的列（外鍵）引用主表（父表）中的列（主鍵或唯一鍵）。通過這種方式，可以確保子表中的數據在父表中有對應的條目。

這用于確保數據的一致性和完整性，具體而言，則是用于維護表與表之間的關系，確保在一個表中引用的值在另一個表中存在。

外鍵約束有著以下特點。

• 主表必須已經存在于數據庫，或者是當前正在創建的表。
• 必須為主表定義主鍵。
• 主鍵不能包含空值，但允許在外鍵中出現空值。
• 在主表的表名后面指定列名或列名的組合，而這個列或者列組合必須是主表的主鍵或者候選鍵。
• 外鍵中列的數目必須和主鍵中列的數目相同。
• 外鍵中列的數據類型必須和主鍵中列的數據類型相同。

如果想要創建外鍵約束，有兩種方式。

方式1：在創建表時設置外鍵約束。

在CREATE TABLE語句中，通過FOREIGN KEY關鍵字來指定外鍵，具體的語法格式如下。

CONSTRAINT 外鍵名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主鍵列1,主鍵列2,...

下面是簡單的示例。

USE mydb1;-- 創建主表（部門表）
CREATE TABLE IF NOT EXISTS dept (did VARCHAR(20) PRIMARY KEY, -- 部門編號，設置主鍵name VARCHAR(20) -- 部門名字
);-- 創建從表（員工表）
CREATE TABLE IF NOT EXISTS emp (eid VARCHAR(20) PRIMARY KEY, -- 員工編號，設置主鍵ename VARCHAR(20), -- 員工名字age INT, -- 員工年齡dept_id VARCHAR(20), -- 員工所屬部門編號CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did) -- 外鍵約束
);

創建完外鍵約束后，可以通過模型查看外鍵約束關系。點擊表，選中兩個表，右鍵選擇“逆向表到模型”即可查看。

可以看到，兩張表之間的外鍵約束已經建立。

除此之外，還有另一種創建外鍵約束的方式。

方式2：在修改表時設置外鍵約束。

在ALTER TABLE語句中，通過FOREIGN KEY關鍵字來指定外鍵，具體的語法格式如下。

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主鍵列1,主鍵列2,...

下面是簡單的示例。

-- 創建主表（部門表）
CREATE TABLE IF NOT EXISTS dept (did VARCHAR(20) PRIMARY KEY, -- 部門編號，設置主鍵name VARCHAR(20) -- 部門名字
);-- 創建從表（員工表）
CREATE TABLE IF NOT EXISTS emp (eid VARCHAR(20) PRIMARY KEY, -- 員工編號，設置主鍵ename VARCHAR(20), -- 員工名字age INT, -- 員工年齡dept_id VARCHAR(20), -- 員工所屬部門編號
);-- 創建外鍵約束
ALTER TABLE emp ADD CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did);

1.一對多關系

為了驗證外鍵約束的作用，首先應該將上面創建的兩張空表，進行一對多關系的數據填充。

-- 1.添加主表數據
INSERT INTO dept VALUES ('1001','研發部');
INSERT INTO dept VALUES ('1002','銷售部');
INSERT INTO dept VALUES ('1003','財務部');
INSERT INTO dept VALUES ('1004','人事部');-- 2.添加從表數據
INSERT INTO emp VALUES ('1','劉邦',25,'1001');
INSERT INTO emp VALUES ('2','樊噲',24,'1001');
INSERT INTO emp VALUES ('3','張良',26,'1001');
INSERT INTO emp VALUES ('4','韓信',25,'1002');
INSERT INTO emp VALUES ('5','蕭何',27,'1002');
INSERT INTO emp VALUES ('6','曹參',23,'1003');
INSERT INTO emp VALUES ('7','陳平',26,'1003');
INSERT INTO emp VALUES ('8','周勃',28,'1004');

注意，當刪除數據的時候，有外鍵依賴的主表數據是不能刪除的，除非先清除從表中依賴主表的外鍵，否則會報錯。但反之，從表中的外鍵都是可以隨意刪除的。

而如果希望刪除外鍵約束時，需要在ALTER TABLE語句中使用DROP關鍵字來刪除外鍵約束。具體語法如下所示。

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵約束名

簡單的實現示例則如下所示。

ALTER TABLE emp DROP FOREIGN KEY emp_fk;

這樣就能夠刪除剛才在上面的示例中在從表emp中創建的emp_fk外鍵約束。

2.多對多關系

對于多對多關系，比如之前提到的學生和選課的關系，此時學生表和選課表都是主表，而簡化其關系的中間表則是從表，其中的外鍵列依賴于學生表和選課表兩個主表。

具體的實現示例如下。

-- 創建學生表（主表）
CREATE TABLE IF NOT EXISTS student (sid INT PRIMARY KEY auto_increment, -- 學生編號name VARCHAR(20), -- 學生姓名age INT, -- 學生年齡gender VARCHAR(20) -- 學生性別
);
-- 創建課程表（主表）
CREATE TABLE IF NOT EXISTS course (cid INT PRIMARY KEY auto_increment, -- 課程編號cname VARCHAR(20) -- 課程名
);
-- 創建中間表（從表）
CREATE TABLE IF NOT EXISTS score (sid INT,cid INT,score DOUBLE
);-- 創建外鍵約束
ALTER TABLE score ADD FOREIGN KEY (sid) REFERENCES student(sid);
ALTER TABLE score ADD FOREIGN KEY (cid) REFERENCES course(cid);-- 學生表數據填充
INSERT INTO student VALUES (1,'劉邦',21,'男'),(2,'呂雉',19,'女'),(3,'項羽',20,'男');
-- 課程表數據填充
INSERT INTO course VALUES (1,'語文'),(2,'數學'),(3,'英語');
-- 中間表數據填充
INSERT INTO score VALUES (1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);

查看表的模型即可看到外鍵約束已創建完畢。

二、多表聯合查詢

多表聯合查詢（也稱為聯接查詢）用于從多個表中檢索相關數據，因為在實際項目需要時，可能需要顯示的查詢結果來自于兩個或兩個以上的表。

多表查詢有以下分類。

• 交叉連接查詢
• 內連接查詢
• 外連接查詢
• 子查詢
• 表自關聯

作為使用的數據，仍然主要沿用上面的部門和員工表示例，只不過不加入外鍵約束。

CREATE TABLE IF NOT EXISTS dept (did VARCHAR(20) PRIMARY KEY, -- 部門編號，設置主鍵name VARCHAR(20) -- 部門名字
);
CREATE TABLE IF NOT EXISTS emp (eid VARCHAR(20) PRIMARY KEY, -- 員工編號，設置主鍵ename VARCHAR(20), -- 員工名字age INT, -- 員工年齡dept_id VARCHAR(20) -- 員工所屬部門編號
);
INSERT INTO dept VALUES
('1001','研發部'),
('1002','銷售部'),
('1003','財務部'),
('1004','人事部');
INSERT INTO emp VALUES 
('01','劉邦',25,'1001'),
('02','樊噲',24,'1001'),
('03','張良',26,'1001'),
('04','韓信',25,'1001'),
('05','蕭何',27,'1002'),
('06','曹參',23,'1002'),
('07','陳平',26,'1002'),
('08','周勃',28,'1003'),
('09','彭越',27,'1003'),
('10','呂雉',24,'1005');

（一）交叉連接查詢

交叉連接（Cross Join） 是 SQL 中的一種連接類型，它返回兩個表的笛卡爾積，可以理解為一張表的每一行都和另一張表的任意一行進行匹配（假如A表有m行數據，B表有n行數據，則返回m*n行數據）。笛卡爾積會產生很多冗余的數據，后期的其他查詢可以在該集合的基礎上進行條件篩選。

其語法格式為以下所示。

SELECT * FROM 表1,表2,...

具體實現示例如下所示。

SELECT * FROM dept,emp;

返回結果如下。

（二）內連接查詢

內連接（INNER JOIN） 是 SQL 中最常用的連接類型之一，用于從兩個或多個表中提取符合條件的記錄。內連接只返回滿足連接條件的記錄，實際上是求的兩張表的交集，可以將表中的相關數據組合在一起，從而進行更加復雜的查詢和分析。

其具體語法格式如下所示。

-- 隱式內連接
SELECT * FROM A表,B表 WHERE 條件; -- 可以理解為從笛卡爾積中篩選出符合條件的值
-- 顯式內連接
SELECT * FROM A表 INNER JOIN B表 ON 條件; -- INNER可省略

具體示例如下所示。

-- 查詢每個部門的所屬員工
SELECT * FROM dept,emp WHERE dept.did = emp.dept_id;
SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id;-- 查詢研發部和銷售部的所屬員工
SELECT * FROM dept,emp WHERE dept.did = emp.dept_id AND name IN ('研發部','銷售部');
SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id AND name IN ('研發部','銷售部');-- 查詢每個部門的員工數，并升序排序
SELECT a.name,count(*) FROM dept a JOIN emp b ON a.did = b.dept_id GROUP BY a.did;-- 查詢人數大于3的部門，并按照人數降序排序
SELECT a.name,count(*) AS count FROM dept a JOIN emp b ON a.did = b.dept_id GROUP BY a.did HAVING count >= 3 ORDER BY count DESC;

---

我是EC，一個永遠在學習中的探索者，關注我，讓我們一起進步！