【MySQL】子查詢（subquery）與視圖（view）

日期：

https://www.flickr.com/photos/8058098@N07/2718571627/

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

此篇內容轉載自本人在 iThome 的文章。



一、測試資料

本文的測試資料如下。

部門資料表。

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


二、子查詢（subquery）

（一）背景

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

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

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

結果如下：

dept_id
1

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

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

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

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

（二）一列一欄

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

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

（三）多列一欄

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

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

（四）一列多欄

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

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

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

（五）多列多欄

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

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

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

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

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

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


三、視圖（View）

（一）介紹

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

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

它帶來的好處有：

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

（二）建立與查詢

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

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

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

SELECT * FROM `view_dept_info`;

結果如下。

dept_iddept_nameheadcounttotal_salarymax_salarymin_salary
1研發21340008400050000
2行政1290002900029000
3資訊2740003900035000

（三）其他操作

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

SHOW CREATE VIEW `view_dept_info`;

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

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

DROP VIEW IF EXISTS `view_dept_info`;

上一篇：【MySQL】使用分組查詢並搭配聚合函數來統計資料

下一篇：【MySQL】設計預存程序以封裝常用操作

留言

張貼留言