【MySQL - 6】多表關係是什麼？如何進行多表查詢？內連線、外連線、子查詢（含超多練習）

1. 多表關係

多表關係，是指表與表之間的關係，在上一篇文章中，我們說到了表與表之間的關係是靠外來鍵來建立的，下面 下麪來詳細說說具體有哪些多表關係

1.1 一對多關係

一對多關係，一個很簡單的例子，一個部門可以對應多個員工，其實現方式也很簡單，在「多」的一方建立外來鍵指向「一」的主鍵即可，下面 下麪就來演示一下這個例子

# 一對多關係 一個部門多個員工
CREATE TABLE onemore1(
	id INT,
	NAME VARCHAR(8), 
	dept INT, 
	CONSTRAINT 1om2 FOREIGN KEY (dept) REFERENCES onemore2(id)
);
CREATE TABLE onemore2(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8)
);

來看看他的架構圖

1.2 多對多關係

多對多關係，可能大家一下子反應不過來有什麼是多對多的，其實也有一個很簡單的例子，一個學生可以選擇多門課程，一門課程也可以被多個學生所選擇，因此學生與課程之間就是多對多的關係，那怎麼來實現多對多關係呢？這裏就注意了，多對多關係需要建立一張新表作爲中間表來建立多對多關係，新表上的兩列分別建立外來鍵指向學生表與課程表，有點抽象？沒關係，我們來演示一下

# 多對多關係 學生與課程
CREATE TABLE moremore1(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8),
	lect INT
);
CREATE TABLE moremore2(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8)
);
-- 中間表
CREATE TABLE moremore3(
	stu INT, 
	luct INT, 
	CONSTRAINT 1mm3 FOREIGN KEY (stu) REFERENCES moremore1(id), 
	CONSTRAINT 2mm3 FOREIGN KEY (luct) REFERENCES moremore2(id)
);

不太清楚中間關係？來看看架構圖

1.3 一對一關係

最後是一對一關係，一對一是啥？很簡單，學號和姓名對吧，那怎麼實現一對一關係呢？別被我繞進去了，一對一關係直接寫在同一張表上不就好了嘛

1.4 小案例

關係介紹完了，但是似乎上面的例子不夠複雜對吧，印象一定不夠深刻，這裏來做一個四個表之間的多表關係

以下是情境引入：例如某某旅遊網站，有很多的旅遊線路分類，點進某個分類後你可以看見許多旅遊線路，若你對這個旅遊線路感興趣你可以加入你自己的購物車，同樣道理，這個旅遊線路也可以被加進你朋友的購物車

接下來分析一下剛剛的情境：一個分類下有多個線路，一對多關係；一個人可以收藏多個旅遊線路，一條旅遊線路也可以被多個人收藏，多對多關係；所涉及的表有分類表、線路表、個人表、中間表

那麼我們來演示一下這個栗子

# 多表關係 旅遊網
CREATE TABLE catalog(
	cid INT PRIMARY KEY, 
	NAME VARCHAR(16)
);
CREATE TABLE route(
	rid INT PRIMARY KEY,
	NAME VARCHAR(16), 
	cata INT, 
	CONSTRAINT rc FOREIGN KEY(cata) REFERENCES catalog(cid)
);
CREATE TABLE customer(
	id INT PRIMARY KEY, 
	NAME VARCHAR(8)
);
-- 中間表
CREATE TABLE r_and_c(
	cuid INT, 
	rid INT, 
	CONSTRAINT cc FOREIGN KEY(cuid) REFERENCES customer(id), 
	CONSTRAINT rr FOREIGN KEY(rid) REFERENCES route(rid)
); 

給你們直觀看看這個架構圖

2. 多表查詢

多表關係就介紹完了，其實多表關係理解都很簡單，不難；而多表查詢就沒那麼簡單了，下面 下麪來舉個例子說明一下什麼是多表查詢，這裏有一個員工表和部門表

-- 部門表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUES("研發部"),("市場部"),("財務部");
-- 員工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8), 
	gender VARCHAR(8), 
	salary INT, 
	joindate DATE, 
	dept INT, 
	FOREIGN KEY(dept) REFERENCES dept(id)
);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("孫悟空","男",7200,"2013-02-24",1);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("豬八戒","男",3600,"2010-12-02",2);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("唐僧","男",9000,"2008-08-08",2);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("白骨精","女",5000,"2015-10-07",3);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("蜘蛛精","女",4500,"2011-03-14",1);

倘若我要查詢每位員工的個人資訊以及對應的部門資訊怎麼辦呢？分別SELECT一下？


不行呀，檢視不在同一個表，看起來不方便不直觀，那這時候就需要用到多表查詢了，先來說說格式
SELECT 列名1, 列名2, ... , 列名n FROM 表名1, 表名2, ... , 表名n;

那麼我們來試一下利用多表查詢查一下這兩個表，看一下可不可以得出每位員工的個人資訊與部門資訊

SELECT * FROM dept,emp;

Emmm…綜合表是查出來了，但是裏面有些不符合的數據呀，爲什麼會這樣子呢？其原因是在進行多表查詢時是預設把所提供的表進行笛卡爾積運算得出結果（不曉得啥是笛卡爾積的同學自行百度）因此我們需要運用一些手段來得出我們所想要的結果，下面 下麪來介紹一下具體有哪些手段可以使用

2.1 內連線

內連線分爲隱式內連線與顯式內連線，內連線查詢的邏輯是：從哪些表中查詢數據、查詢條件是什麼、查詢什麼東西，下面 下麪來分別介紹一下

隱式內連線：

隱式內連線的思想是使用WHERE來消除無用條件，怎麼消除呢？例如剛剛所查詢出來的例子而言，認真觀察一下你會發現每個人的部門都不完全對應的，因此在篩選時我們可以以部門爲條件進行篩選

SELECT * FROM emp,dept WHERE emp.`dept`=dept.`id`;

顯然這個結果已經是我們想要的了

顯式內連線：

顯式內連線的思想其實同樣是消除無用欄位，但他的方式並不是使用WHERE，而是使用... INNER JOIN ... ON ...，其詳細的使用格式如下
SELECT 列名1, 列名2, ... , 列名n FROM 表1 INNER JOIN 表2 ON 條件;

另外，INNER關鍵字可以省略，同樣剛纔的例子，我們來演示一下

select * from emp join dept on emp.`dept`=dept.id;

同樣也是可以得到我們所期望的結果

2.2 外連線

外連線分爲左外連線與右外連線，其功能是查詢某表的所有數據及其與其他表的交集部分，有點點抽象？別急，先繼續看下去

左外連線：

語法：SELECT 列名1, 列名2, ... , 列名n FROM 表1 LEFT JOIN 表2 ON 條件;，其含義是查詢出表1的所有數據以及表2中符合條件的數據

下面 下麪來演示一下前面的例子

SELECT * FROM emp LEFT JOIN dept ON emp.`dept`=dept.`id`;

右外連線：

右外連線其實是跟左外連線相對的，語法：SELECT 列名1, 列名2, ... , 列名n FROM 表1 RIGHT JOIN 表2 ON 條件;，其含義是查詢出表2的所有數據以及表1中符合條件的數據

下面 下麪來演示一下前面的例子

SELECT * FROM emp RIGHT JOIN dept ON emp.`dept`=dept.`id`;

大家有沒有發現外連線中的左右是什麼意思呢？在左外連線的語法中，處於左邊（前面）的表所有數據都會顯示；在右外連線的語法中，處於右邊（後面）的表所有數據都會顯示，因此其實兩種方式的都可以達到一模一樣的效果，平常使用上我們比較習慣使用左外鏈接

2.3 子查詢

子查詢，其實通俗一點的意思是巢狀查詢，同樣是不懂啥意思對吧？來直接看例子，還是前面用前面所建立的員工表，現在我需要查詢工資最高的員工資訊，怎麼做呢？我們可以拆成兩步

第一步，找出最高工資是多少

SELECT MAX(salary) FROM emp;

第二步，根據該最高工資查詢員工資訊

SELECT * FROM emp WHERE emp.`salary`=9000;

顯然，結果是拿到了，可是分兩步做是不是太麻煩了？有沒有方法可以一步完成的？那肯定是有的，我們可以利用子查詢來幹這個事情

SELECT * FROM emp WHERE emp.`salary`=(SELECT MAX(salary) FROM emp);

這樣一條語句就可以得到我們想要的結果了，這種寫法其實是子查詢的第一種情況，子查詢一共有三種不同的情況，下面 下麪來詳細介紹一下：

情況1，子查詢的結果是單行單列的：

這是什麼意思呢？這裏結合一個栗子來講一下，現在我的需求是查詢員工工資小於平均工資的人，看到這個需求後我們要解決的第一件事情是找出平均工資究竟是多少

SELECT AVG(salary) FROM emp;

第二件事情是根據這個平均工資來找出符合條件的人

SELECT * FROM emp WHERE emp.`salary`< 5860;

有沒有注意到我們剛剛的第一件事情所查出來的表是單行單列的，這就是子查詢的情況1，這個第一件事情的查詢語句可以作爲一個子查詢放到第二件事情的查詢語句的條件當中

SELECT * FROM emp WHERE emp.`salary`< (SELECT AVG(salary) FROM emp);

情況2，子查詢的結果是多行單列的：

同樣結合一個栗子來講一下，現在我的需求是查詢財務部和研發部所有的員工資訊，看到這個需求後我們要解決的第一件事情是找出財務部和研發部對應的部門代號是啥

SELECT id FROM dept WHERE NAME IN("財務部","研發部");

第二件事情是根據這個部門代號來找出符合條件的人

SELECT * FROM emp WHERE dept IN(1,3);

有沒有注意到我們剛剛的第一件事情所查出來的表是多行單列的，這就是子查詢的情況2，這個第一件事情的查詢語句同樣也可以作爲一個子查詢放到第二件事情的查詢語句的條件當中，使用運算子IN來判斷

SELECT * FROM emp WHERE dept IN(SELECT id FROM dept WHERE NAME IN("財務部","研發部"));

情況3，子查詢的結果是多行多列的：

同樣結合一個栗子來講一下，現在我的需求是查詢入職日期是2011-11-11之後的員工資訊和部門資訊，看到這個需求後我們要解決的第一件事情是找出職日期是2011-11-11之後的員工資訊

SELECT * FROM emp WHERE emp.`joindate`>"2011-11-11";

第二件事情是找出這些員工對應的部門資訊一併顯示

哎呀，是不是好像不知道怎麼實現這個東西呢？好像需要兩個表纔可以做到呀，對，有注意到剛剛第一件事情查出來的表是多行多列的嗎，這就是我們將要介紹的情況3，這個第一件事情的查詢結果可以作爲一張虛擬表放到第二件事情的查詢語句的表列表當中

SELECT * FROM (SELECT * FROM emp WHERE emp.`joindate`>"2011-11-11") t1,dept t2 WHERE t1.`dept`=t2.`id`;

除此以外，其實該需求還可以使用前面所介紹的內連線來完成

SELECT * FROM emp,dept WHERE emp.`dept`=dept.`id` AND emp.`joindate`>"2011-11-11"; -- 內連線

2.4 綜合練習

相關內容介紹完了，爲了鞏固一下相關語法的使用，這裏來給一個綜合練習，一共有六個小題，這裏給出幾個表建立的原始程式碼，以方便大家練習

部門表

-- 部門表
CREATE TABLE pdept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	city VARCHAR(8)
);
INSERT INTO 
	pdept(id,NAME,city) 
VALUES
	(10,"教研部","北京"),
	(20,"學工部","上海"),
	(30,"銷售部","廣州"),
	(40,"財務部","深圳");

職務表

-- 職務表
CREATE TABLE pjob(
	id INT PRIMARY KEY,
	NAME VARCHAR(20),
	description VARCHAR(50)
);
INSERT INTO
	pjob(id,NAME,description)
VALUES
	(1,"董事長","管理公司"),
	(2,"經理","管理部門"),
	(3,"銷售","銷售產品"),
	(4,"文員","處理事務");

員工表

-- 員工表
CREATE TABLE pemp(
	id INT PRIMARY KEY,
	NAME VARCHAR(8),
	job INT,
	manager INT,
	jobdate DATE,
	salary DOUBLE(7,2),
	bonus DOUBLE(7,2),
	dept INT,
	CONSTRAINT jobkey FOREIGN KEY(job) REFERENCES pjob(id),
	CONSTRAINT managerkey FOREIGN KEY(manager) REFERENCES pemp(id),
	CONSTRAINT deptkey FOREIGN KEY(dept) REFERENCES pdept(id)
);
INSERT INTO 
	pemp(id,NAME,job,manager,jobdate,salary,bonus,dept)
VALUES
	(1001,"孫悟空",4,1004,"2000-12-17",8000.00,NULL,20),
	(1002,"盧俊義",3,1006,"2001-02-20",16000.00,3000.00,30),
	(1003,"林沖",3,1006,"2001-02-22",12500.00,5000.00,30),
	(1004,"唐僧",2,1009,"2001-04-02",29750.00,NULL,20),
	(1005,"李逵",4,1006,"2001-09-28",12500.00,14000.00,30),
	(1006,"宋江",2,1009,"2001-05-01",28500.00,NULL,30),
	(1007,"劉備",2,1009,"2001-09-01",24500.00,NULL,10),
	(1008,"豬八戒",4,1004,"2007-04-19",30000.00,NULL,20),
	(1009,"羅貫中",1,NULL,"2001-11-17",50000.00,NULL,10),
	(1010,"吳用",3,1006,"2001-09-08",15000.00,0.00,30),
	(1011,"沙僧",4,1004,"2007-05-23",11000.00,NULL,20),
	(1012,"李逵",4,1006,"2001-12-03",9500.00,NULL,30),
	(1013,"小白龍",4,1004,"2001-12-03",30000.00,NULL,20),
	(1014,"關羽",4,1007,"2002-01-23",13000.00,NULL,10);

工資等級表

-- 工資等級表
CREATE TABLE psalary(
	grade INT PRIMARY KEY,
	low INT,
	high INT
);
INSERT INTO 
	psalary(grade,low,high) 
VALUES
	(1,7000,12000),
	(2,12001,14000),
	(3,14001,20000),
	(4,20001,30000),
	(5,30001,99999);

所有原始表都已經給出了，下面 下麪就來看一下這六道小題目是啥

大家可以自己先做一下，做不出來再來看看我一下的參考程式碼以及結果

需求一

-- 需求一：查詢所有員工資訊，查詢員工編號、員工姓名、工作、職務名稱、職務描述
SELECT
	t1.`id`,
	t1.`name`,
	t2.*
FROM
	pemp t1,pjob t2
WHERE
	t1.`job`=t2.`id`;

需求二

-- 需求二：查詢員工編號、員工姓名、工資、職務名稱、職務描述、部門名稱、部門位置
SELECT
	t1.`id`,
	t1.`name`,
	t1.`salary`,
	t2.`name`,
	t2.`description`,
	t3.`name`,
	t3.`city`
FROM
	pemp t1,pjob t2,pdept t3
WHERE
	t1.`job`=t2.`id`
	AND t1.`dept`=t3.`id`;

需求三

-- 需求三：查詢員工姓名、工資、工資等級
SELECT
	t1.`name`,
	t1.`salary`,
	t2.`grade`
FROM
	pemp t1,psalary t2
WHERE
	t1.`salary` BETWEEN t2.`low` AND t2.`high`;

需求四

-- 需求四：查詢員工姓名、工資、職務名稱、職務描述、部門名稱、部門位置、工資等級
SELECT
	t1.`name`,
	t1.`salary`,
	t2.`name`,
	t2.`description`,
	t3.`name`,
	t3.`city`,
	t4.`grade`
FROM
	pemp t1,pjob t2,pdept t3,psalary t4
WHERE
	t1.`job`=t2.`id`
	AND t1.`dept`=t3.`id`
	AND t1.`salary` BETWEEN t4.`low` AND t4.`high`;

需求五

-- 需求五：查詢部門編號、部門名稱、部門位置、部門人數
		-- 方法一
SELECT
	t1.*,
	IFNULL(t3.`num`,0) num
FROM
	pdept t1 LEFT JOIN (SELECT t2.`dept` id,COUNT(t2.`dept`) num FROM pemp t2 GROUP BY t2.`dept`) t3
ON 
	t1.`id`=t3.`id`;
		-- 方法二
SELECT
	t1.*,
	COUNT(t2.`dept`) num
FROM
	pdept t1 LEFT JOIN pemp t2
ON
	t1.`id`=t2.`dept`
GROUP BY
	t2.`dept`;

需求六

-- 需求六：查詢所有員工的姓名及其直接上級的姓名，沒有領導的員工也需要查詢
SELECT
	t1.`name`,
	t1.`manager`,
	t2.`name` superior
FROM
	pemp t1 LEFT JOIN pemp t2
ON
	t1.`manager`=t2.`id`;

The end~該內容可能理解上會比較繞，需要好好理解多加練習