如何在SQL中使用嵌套查询

教程
MySQL

介绍

结构化查询语言（SQL）用于管理关系数据库管理系统（RDBMS）中的数据。 SQL 中一个有用的功能是创建查询内的查询，也称为子查询或嵌套查询。 嵌套查询是一个SELECT语句，通常被括在括号中，并嵌入在主要的SELECT、INSERT或DELETE操作中。

在本教程中，您将使用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 的方法进行设置。本指南假定您已按照本指南的第 3 步中的说明设置了一个非根 MySQL 用户。

注意:请注意，许多关系数据库管理系统使用它们自己独特的 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表。

另外，通过在嵌套查询语句（membership_cost * 0.85）中包含乘法数学运算*，将15%的折扣应用于任何“居民”会员。然后使用WHERE子句对membership_type列的值进行排序。您可以进一步缩小范围，只获取“居民”会员的结果，使用LIKE子句，并在单引号中在“居民”一词前后放置百分比%符号，以选择符合相同模式或相同措辞的任何会员。您的查询将写成如下形式：

  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表中。为了确认您请求的数据已成功从您创建的空upgrade_guests表中的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表的与“居民”相关的客户会员信息。另外，已重新计算了带有15%折扣的新membership_cost。因此，此操作已有助于分段并针对适当的受众，并且已准备好与这些潜在新成员共享折扣价格。

使用嵌套查询与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