SQL 子查詢1

1. 定義

慕課解釋：子查詢，又稱嵌套查詢，是一種嵌套在其它 SQL 查詢的 Where 字句中的查詢。

2. 前言

本小節(jié)，我們將一起學習 SQL 子查詢。

SQL 子查詢是一種復雜的查詢方式，一般子查詢語句都可以被分為主查詢部分和子查詢部分。子查詢部分為主查詢部分服務，常用于為主查詢返回其所需數(shù)據(jù)，或者進一步篩選主查詢數(shù)據(jù)。

子查詢的知識點其實不多，學習它的難點在于如何將 SQL 查詢使用熟練，然后靈活搭配，本小節(jié)我們將聚焦在子查詢的基本使用上，而把子查詢中的兩個特殊語法 ANY 和 ALL 放在下一小節(jié)介紹。

本小節(jié)測試數(shù)據(jù)如下，請先在數(shù)據(jù)庫中執(zhí)行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int,
  score int
);
INSERT INTO imooc_user(id,username,age,score) VALUES (1,'peter', 18, 100),
(2,'pedro', 24, 200),(3,'jerry', 28, 500),
(4,'mike', 12, 300),(5,'tom', 27, 1000);

3. 語法

子查詢的語法其實與普通查詢的語法沒有什么區(qū)別，只不過多了相應的子查詢部分。

以 Select 為例，語法如下：

SELECT [col] FROM [table_name]
WHERE [col] [operator]
(
  SELECT [col] FROM [table_name];
  WHERE [col] [operator]
 );

其中table_name表示數(shù)據(jù)表名稱，col表示字段名，operator表示字段操作。

子查詢可靈活用于 Insert、Select、Update 和 Delete 指令中，我們沒有列舉出所有的語法，它們在子查詢的部分其實是一致的，區(qū)別在于主操作部分。

子查詢雖然很靈活，但是也有一定的限制，它必須滿足以下幾個規(guī)則：

1. 子查詢必須在括號（）內(nèi)。
2. 子查詢中不能使用 Order By，主查詢可以使用。
3. 子查詢不能使用在聚合函數(shù)中。
4. Between 指令不能與子查詢一起使用，但可使用在子查詢內(nèi)部。
5. 子查詢?nèi)舴祷匾粭l記錄，則只能使用單值運算符，如 > ，若返回多條記錄需使用多值運算符，如 In。
6. 若子查詢返回多條記錄，且使用 ANY 或 ALL 特殊語法，則可使用單值比較符，我們將在下小節(jié)介紹。

4. Select 中的子查詢

在 Select 中使用子查詢是最為常見的一種子查詢方式，子查詢讓我們可以更加靈活查詢數(shù)據(jù)。

4.1 例1 Select 搭配子查詢

請書寫 SQL 語句，獲取imooc_user表中小于最大年齡的用戶名。

分析：

從題干中得出，需要獲取小于最大年齡的用戶名，所以第一步需要找到最大年齡，然后通過最大年齡比較得出年齡小于它的用戶名；使用子查詢可以快速的辦到這一點，子查詢得到最大年齡，主查詢部分以最大年齡作為篩選條件從而得到結(jié)果。

語句：

整理可得語句如下：

SELECT username FROM imooc_user 
WHERE age <
(SELECT age FROM imooc_user ORDER BY age DESC LIMIT 1);

結(jié)果如下：

+----------+
| username |
+----------+
| peter    |
| pedro    |
| mike     |
| tom      |
+----------+

imooc_user 表中，jerry 年齡最大，28 歲，結(jié)果中顯示了除他以外的所有人， 因此結(jié)果正確。

5. Insert 中的子查詢

子查詢還可以與 Insert 搭配使用，Insert 可將子查詢得到的數(shù)據(jù)插入到其它表中。

5.1 例2 Insert 搭配子查詢

請書寫 SQL 語句，獲取imooc_user表中小于最大年齡的用戶名，并將用戶名插入到 username_copy表中。

分析：

同上，另外的我們還需要新建 username_copy 表，并向其中插入數(shù)據(jù)。

語句：

整理可得語句如下：

CREATE TABLE username_copy(username varchar(20));
INSERT INTO username_copy SELECT username FROM imooc_user 
WHERE age <
(SELECT age FROM imooc_user ORDER BY age DESC LIMIT 1);

username_copy 表信息如下：

+----------+
| username |
+----------+
| peter    |
| pedro    |
| mike     |
| tom      |
+----------+

6. Update 中的子查詢

子查詢可搭配 Update，一次完成多條記錄的更新，當然也可只更新一條記錄。

6.1 例3 Update 搭配子查詢

請書寫 SQL 語句，將imooc_user表中年齡大于 25 歲的用戶積分增加 100 。

分析：

由題干可知，我們可分兩步完成，第一步從子查詢中獲取年齡大于 25 歲的用戶 id，然后在主操作語句部分更新他們的積分。

語句：

整理可得語句如下：

UPDATE imooc_user SET score = score + 100 
WHERE id IN (SELECT id FROM imooc_user WHERE age > 25);

更新后，用戶積分如下：

+----------+-------+
| username | score |
+----------+-------+
| peter    | 100   |
| pedro    | 200   |
| jerry    | 600   |
| mike     | 300   |
| tom      | 1100  |
+----------+-------+

如果你使用 MySQL，那么上面語句會無法執(zhí)行，因為 MySQL 不支持在同一張表中查詢又更新，因此我們可以使用如下的方式來改寫 SQL，使 MySQL 來支持：

UPDATE imooc_user SET score = score + 100 
WHERE id IN ( 
SELECT a.id FROM
(SELECT id FROM imooc_user WHERE age > 25) as a
);

7. Delete 中的子查詢

子查詢可與 Delete 搭配使用來更加方便刪除數(shù)據(jù)。

7.1 例4 Delete 搭配子查詢

請書寫 SQL 語句，刪除imooc_user表中積分大于 500 的用戶 。

分析：

我們?nèi)匀环謨刹酵瓿?，第一步子查詢獲取積分大于 500 的用戶 id，然后在主操作刪除他們。

語句：

整理可得語句如下：

DELETE FROM imooc_user 
WHERE id IN (SELECT id FROM imooc_user WHERE score > 500);

刪除成功后，imooc_user 表信息如下：

+----------+-------+
| username | score |
+----------+-------+
| peter    | 100   |
| pedro    | 200   |
| mike     | 300   |
+----------+-------+

同樣的，MySQL 不支持在一張表中同時刪除和查詢，因此我們改寫一下：

DELETE FROM imooc_user 
WHERE id IN ( 
SELECT a.id FROM
(SELECT id FROM imooc_user WHERE score > 500) as a
);

8. 小結(jié)

• 子查詢的難點在于如何熟練搭配和使用查詢，這是一個積少成多的過程，因此請務必多多練習。
• 子查詢也有諸多限制，而且性能不高，因此如果可以用一次查詢解決的問題盡量不要使用子查詢。