如何在 SQL 中使用嵌套查询

教學
MySQL

介紹

結構化查詢語言（SQL）用於管理關聯式數據庫管理系統（RDBMS）中的數據。SQL 中一個有用的功能是在查詢中創建另一個查詢，也稱為 子查詢 或 嵌套查詢。嵌套查詢是一個通常用括號括起來並嵌套在主要的 SELECT、INSERT 或 DELETE 操作中的 SELECT 語句。

在本教程中，您將使用帶有 SELECT、INSERT 和 DELETE 語句的嵌套查詢。您還將在嵌套查詢中使用聚合函數，以將數據值與您指定的按排序數據值進行比較，使用 WHERE 和 LIKE 子句。

先決條件

要遵循本指南，您需要一台運行某種使用 SQL 的關聯式數據庫管理系統（RDBMS）的計算機。本教程中的指示和示例是在以下環境中驗證的：

  • A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
  • MySQL已安裝並在伺服器上進行了安全設置。按照我們的在Ubuntu 20.04上安裝MySQL的方法指南進行設置。本指南假設您還設置了一個非根MySQL用戶，如本指南的第3步所述。

注意:請注意，許多關聯式數據庫管理系統使用自己獨特的SQL實現。儘管本教程中概述的命令將在大多數RDBMS上運行，但如果您在除MySQL之外的系統上測試它們，確切的語法或輸出可能會有所不同。

為了在本教程中練習使用嵌套查詢，您需要一個包含示例數據的數據庫和表。如果您沒有準備好要插入的數據庫，您可以閱讀以下連接到MySQL並設置示例數據庫部分，了解如何創建數據庫和表。本教程將在整個過程中引用這個示例數據庫和表。

連接到MySQL並設置示例數據庫

如果您的SQL數據庫運行在遠程服務器上，請從本地機器上的終端使用SSH登錄到您的服務器:

  1. ssh sammy@your_server_ip

接下來，打開MySQL提示符，將sammy替換為您的MySQL用戶帳戶信息:

  1. mysql -u sammy -p

創建名為zooDB的數據庫:

  1. CREATE DATABASE zooDB;

如果資料庫建立成功，您將收到以下輸出：

Output
Query OK, 1 row affected (0.01 sec)

要選擇 zooDB 資料庫，執行以下 USE 語句：

  1. USE zooDB;



Output
Database changed

在選擇資料庫後，在其中創建一個表。對於本教程的示例，我們將創建一個表，用於存儲訪問動物園的客人信息。該表將包含以下七個列：

  • guest_id：存儲訪問動物園的客人值，使用 int 數據類型。這也作為表的 主鍵，意味著該列中的每個值將作為其所在行的唯一標識符。
  • first_name：使用 varchar 數據類型保存每個客人的名字，最大長度為 30 個字符。
  • last_name：再次使用 varchar 數據類型，最大長度為 30 個字符，用於存儲每個客人的姓氏。
  • guest_type：包含每位客人的客戶類型（成人或兒童），使用 varchar 數據類型，最大長度為 15 個字符。
  • membership_type：表示每位客人持有的會員類型，使用 varchar 數據類型，最大長度為 30 個字符。
  • membership_cost：存儲各種會員類型的費用。該列使用 decimal 數據類型，精度為五，比例為二，這意味著該列中的值可以有五位數字，小數點右邊有兩位數字。
  • total_visits：使用int数据类型记录每位访客的总访问次数。

通过运行以下CREATE TABLE命令创建名为guests的表，其中包含每个列：

  1. CREATE TABLE guests (

  2. guest_id int, 

  3. first_name varchar(30),

  4. last_name varchar(30),

  5. guest_type varchar(15),

  6. membership_type varchar(30),

  7. membership_cost decimal(5,2), 

  8. total_visits int,

  9. PRIMARY KEY (guest_id)

  10. );

接下来，向空表中插入一些示例数据：

  1. INSERT INTO guests

  2. (guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)

  3. VALUES

  4. (1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),

  5. (2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),

  6. (3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),

  7. (4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),

  8. (5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),

  9. (6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),

  10. (7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),

  11. (8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),

  12. (9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),

  13. (10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),

  14. (11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 

  15. (12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 

  16. (13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 

  17. (14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),

  18. (15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);



Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

插入数据后，您就可以开始在SQL中使用嵌套查询了。

使用嵌套查询和SELECT

在SQL中，查询是从数据库中的表中检索数据的操作，并且始终包括一个SELECT语句。嵌套查询是完整的查询嵌入在另一个操作中。嵌套查询可以具有常规查询中使用的所有元素，并且任何有效的查询都可以嵌入到另一个操作中以成为嵌套查询。例如，嵌套查询可以嵌入到INSERT和DELETE操作中。根据操作，应将嵌套查询嵌入，方法是将语句封装在正确数量的括号中，以遵循特定的操作顺序。在您想要在一个查询语句中执行多个命令而不是编写多个以返回所需结果时，嵌套查询也非常有用。

為了更好地理解嵌套查询，讓我們使用前一步驟中的示例數據來說明它們可以如何有用。例如，假設您想要找到在guests表中訪問動物園頻率高於平均數的所有客人。您可能會認為可以使用以下查詢來找到此信息：

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > AVG(total_visits);

但是，使用此語法的查詢將返回錯誤：

Output
ERROR 1111 (HY000): Invalid use of group function

出現此錯誤的原因是，像AVG()這樣的聚合函數只有在SELECT子句內執行時才能工作。

檢索此信息的一種選擇是首先運行一個查詢以找到客人訪問次數的平均值，然後運行另一個查詢以根據該值找到結果，如以下兩個示例：

  1. SELECT AVG(total_visits) FROM guests;



Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > 57.5333;



Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

但是，您可以通過在第二個查詢中嵌套第一個查詢(SELECT AVG(total_visits) FROM guests;)來獲得相同的結果集。請記住，在嵌套查詢中，使用適當數量的括號是必要的，以完成您想要執行的操作。這是因為嵌套查詢是首先執行的操作：

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > 

  4. (SELECT AVG(total_visits) FROM guests);



Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

根據這個輸出，有五位訪客比平均值更頻繁地參觀。這樣的信息可能會提供有用的見解，幫助我們思考創意方法，確保現有會員經常參觀動物園並每年續訂他們的會籍證。此外，這個例子展示了在一個完整的語句中使用巢狀查詢以獲得所需結果的價值，而不是運行兩個單獨的查詢。

使用巢狀查詢與INSERT

使用巢狀查詢，您不僅限於將其嵌入到其他SELECT語句中。事實上，您還可以使用巢狀查詢將數據插入到現有表中，方法是將您的巢狀查詢嵌入到INSERT操作中。

為了說明，假設一家關聯的動物園請求一些有關您的客戶的信息，因為他們有興趣為購買其地點的“居民”會員購買提供15％的折扣。要做到這一點，請使用CREATE TABLE創建一個名為upgrade_guests的新表，其中包含六列。請密切關注數據類型，例如int和varchar，以及它們可以容納的最大字符數。如果它們與您在設置示例數據庫部分創建的guests表中的原始數據類型不匹配，則在嘗試使用嵌套查詢從guests表中插入數據時將收到錯誤，並且數據將不會正確轉移。使用以下信息創建您的表：

  1. CREATE TABLE upgrade_guests (

  2. guest_id int, 

  3. first_name varchar(30),

  4. last_name varchar(30),

  5. membership_type varchar(30),

  6. membership_cost decimal(5,2),

  7. total_visits int, 

  8. PRIMARY KEY (guest_id)

  9. );

為了保持一致性和準確性，我們將此表中的大部分數據類型信息保持與guests表相同。我們還刪除了新表中不想要的任何額外列。有了這個空表準備就緒，下一步是將所需的數據值插入到表中。

在此操作中，請寫下INSERT INTO和新的upgrade_guests表，以便明確指示數據的插入位置。然後，使用SELECT語句撰寫您的嵌套查詢，以檢索相關的數據值，並使用FROM確保它們來自guests表。

此外，對任何“Resident”成員應用15%的折扣，方法是在嵌套查詢語句中包含乘法數學運算*，以0.85相乘(membership_cost * 0.85)。然後使用WHERE子句按membership_type列的值進行排序。您可以進一步縮小範圍，僅對“Resident”會籍的結果使用LIKE子句，並在單引號中在單詞“Resident”之前和之後放置百分比%符號，以選擇符合相同模式或相同措辭的任何會籍。您的查詢將如下所示：

  1. INSERT INTO upgrade_guests

  2. SELECT guest_id, first_name, last_name, membership_type, 

  3. (membership_cost * 0.85), total_visits

  4. FROM guests

  5. WHERE membership_type LIKE '%resident%';



Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

輸出顯示已向新的upgrade_guests表添加了五條記錄。要確認您從guests表成功轉移到您創建的空upgrade_guests表中的數據，並且滿足您指定的嵌套查詢和WHERE子句的條件，請運行以下操作：

  1. SELECT * FROM upgrade_guests;



Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

根據您的新upgrade_guests表的此輸出，來自guest表的“Resident”相關客戶會籍信息已正確插入。此外，新的membership_cost已重新計算，並應用了15%的折扣。因此，此操作有助於對適當的受眾進行分割和定位，並且具有折扣價格可供與這些潛在新成員分享。

使用嵌套查询与DELETE

为了练习使用DELETE语句进行嵌套查询，假设您想要删除任何频繁访客，因为您只想要将升级的高级通行证折扣推广给目前不经常访问动物园的会员。

从DELETE FROM语句开始操作，以便清楚地知道数据将从哪里删除，即upgrade_guests表。然后，使用WHERE子句来排序任何总访问量total_visits超过嵌套查询中指定数量的记录。在嵌套的嵌套查询中，使用SELECT来查找total_visits的平均值AVG，以便前面的WHERE子句具有适当的数据值进行比较。最后，使用FROM从guests表检索该信息。完整的查询语句将如下所示：

  1. DELETE FROM upgrade_guests

  2. WHERE total_visits >

  3. (SELECT AVG(total_visits) FROM guests);



Output
Query OK, 2 rows affected (0.00 sec)

确认这些记录已成功从upgrade_guests表中删除，并使用ORDER BY按数值和升序对total_visits结果进行排序：

注意：使用DELETE語句從新表中刪除記錄，不會從原始表中刪除它們。您可以運行SELECT * FROM original_table來確認所有原始記錄都已被計算，即使它們已從新表中刪除。

  1. SELECT * FROM upgrade_guests ORDER BY total_visits;



Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

正如此輸出所示，DELETE語句和嵌套查詢在刪除指定的數據值方面運作正常。這個表現在保存了三位來賓的信息，其參觀次數少於平均水平，這對於動物園代表來說是一個很好的起點，可以與他們聯繫，提供以優惠價格升級到高級通行證的機會，並希望他們能更頻繁地去動物園。

結論

嵌套查詢非常有用，因為它們允許您獲得極細粒度的結果，否則您只能通過運行單獨的查詢來獲得。此外，使用嵌套查詢與INSERT和DELETE語句為您提供了另一種在一步中插入或刪除數據的方法。如果您想了解更多關於如何組織您的數據的信息，請查看我們的系列文章：如何使用SQL。

Source:
https://www.digitalocean.com/community/tutorials/how-to-use-nested-queries