本文介紹子查詢（subquery），這項技巧能讓我們將多組查詢結合在一起，一次得到最後想要的查詢結果。接著進一步認識視圖（view），將常用的查詢儲存起來，供重複利用。

此篇亦轉載到個人部落格。

一、測試資料

本文的測試資料如下。

部門資料表。

CREATE TABLE `dept` (
    `dept_id` INT PRIMARY KEY,
    `dept_name` VARCHAR(20)
);

INSERT INTO `dept` (`dept_id`, `dept_name`)
VALUES (1, "研發"), (2, "行政"), (3, "資訊");

員工資料表，其中包含部門編號欄位。

CREATE TABLE `employee` (
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `salary` INT,
    `dept_id` INT
);

INSERT INTO `employee` (`emp_id`, `name`, `salary`, `dept_id`)
VALUES
(206, "小黃", 50000, 1),
(207, "小綠", 29000, 2),
(208, "小黑", 35000, 3),
(209, "小白", 39000, 3),
(210, "小蘭", 84000, 1);

二、子查詢（subquery）

（一）背景

若要查詢「研發部」的員工，在不知道部門編號的情況下，我們可以拆成兩個步驟。

第一步是在部門表依據名稱找出其編號。

SELECT `dept_id`
FROM `dept`
WHERE `dept_name` = "研發";

結果如下：
|| dept_id ||
|-|
|1|

第二步是用部門編號到員工表查詢。

SELECT *
FROM `employee`
WHERE `dept_id` = 1;

如此便寫出了兩組查詢。雖然是簡單的例子，但隱含著一個問題。那就是為了一項需求（找出某部門的員工），我們得另外先進行一至多次查詢（找出部門編號），才能將最終的查詢條件給拼湊出來。

本文介紹的子查詢，能將多組查詢合併在一起，也就是把查詢結果，當成另一組查詢的輸入。當讀者無法用一個步驟完成查詢的需求時，可考慮搭配子查詢的技巧，以下逐一示範。

（二）一列一欄

延續前面的例子。由於查詢部門編號的步驟，其結果固定是「一列一欄」，因此可以將該組查詢當成一個值，放在另一組查詢的 WHERE 語法作為條件。

SELECT *
FROM `employee`
WHERE `dept_id` = (
    SELECT `dept_id`
    FROM `dept`
    WHERE `dept_name` = "研發"
);

這樣看下來，該組查詢的輸入（input）就是部門名稱，而輸出（output）則為員工資料。我們不必知道部門編號。

（三）多列一欄

這種子查詢，會查出一到多筆資料，但固定只有一個欄位。找出這些資料的目的，是為了將它們的欄位值以 IN 語法包裝起來，作為主查詢的條件。

以下的範例，是查詢和「小黃」或「小白」同部門的員工。

SELECT *
FROM `employee`
WHERE `dept_id` IN (
    SELECT `dept_id`
    FROM `employee`
    WHERE `name` IN ("小黃", "小白")
);

（四）一列多欄

這種子查詢，會固定查出一筆資料，但可以有多個欄位。找出這些欄位的目的，是為了將它們以「AND」邏輯的概念包裝起來，作為主查詢的條件。

以下的範例，是查詢跟「小黃」同部門，且同性別的員工。

SELECT *
FROM `employee`
WHERE (`gender`, `dept_id`) = (
    SELECT `gender`, `dept_id`
    FROM `employee`
    WHERE `name` = "小黃"
);

在主查詢的 WHERE 語法中，用括弧將作為條件的欄位包起來，而後面接上子查詢。該子查詢必須回傳相同數量的欄位，它們要依序對應到主查詢。

（五）多列多欄

這種子查詢並非直接作為主查詢的條件，而是將結果當成主查詢的來源，放在 FROM 語法之後。

以下的範例，是將薪資大於 3 萬的員工當成主查詢的資料來源。再以此為基礎，繼續查詢薪資小於 5 萬的員工。

SELECT *
FROM (
    SELECT *
    FROM `employee`
    WHERE `salary` > 30000
) AS `employee_salary_30k_up`
WHERE `salary` < 50000;

此處需使用 AS 語法，替子查詢的結果取別名（alias）。

然而以上的寫法，不就是找出薪資 3 到 5 萬的員工嗎？如下：

SELECT *
FROM `employee`
WHERE `salary` > 30000 AND `salary` < 50000;

換句話說，子查詢勢必有它的查詢條件。那麼直接寫成一組查詢，反而還比較單純。

三、視圖（View）

以第二節第五段的「多列多欄」子查詢為出發點，我們可以進一步認識「視圖」。

（一）介紹

視圖儲存了一組查詢語法，我們能夠將其當作資料表去查詢。

它帶來的好處有：

• 將常用的查詢保存起來，以重複利用，避免經常撰寫冗長的語法。
• 將資料表的查詢結果進行摘要，一來提供有用的資訊給視圖的使用者，二來也不讓所有的欄位都暴露出去。
• 在資料表上隔了一層介面。視圖的使用者不會直接依賴資料表，他們只要知道視圖有什麼欄位即可。

（二）建立與查詢

以下語法建立了一個名為「view_dept_info」的視圖，將各部門的人數、薪資等資訊摘要出來。

CREATE OR REPLACE VIEW `view_dept_info` AS
SELECT
`dept`.`dept_id`,
`dept`.`dept_name`,
COUNT(*) AS `headcount`,
SUM(`salary`) AS `total_salary`, 
MAX(`salary`) AS `max_salary`,
MIN(`salary`) AS `min_salary`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
GROUP BY `dept`.`dept_id`;

使用 CREATR OR REPLACE VIEW 語法，可建立新的視圖，或修改現有的。而 ALTER VIEW 也是修改，但當視圖不存在時，會執行失敗。

建立好後，可以像一般資料表那樣去查詢它。

SELECT * FROM `view_dept_info`;

結果如下。

（三）其他操作

使用 SHOW CREATE VIEW 語法，能查看當初建立視圖的指令。

SHOW CREATE VIEW `view_dept_info`;

使用 SHOW TABLES 語法，能列出目前資料庫中已有的表格和視圖。

使用 DROP VIEW 語法，能刪除視圖。

DROP VIEW IF EXISTS `view_dept_info`;

今日文章到此結束！
最後宣傳一下自己的部落格，我是「新手工程師的程式教室」的作者，主要發表後端相關文章，請多指教