LabEx
登录免费加入

MySQL 子查询与嵌套操作

MySQLBeginner
立即练习

介绍

在本实验中，你将探索 MySQL 子查询和嵌套操作的强大功能。主要重点是使用 WHERE 子句中的子查询，根据从其他表或同一表中派生的条件来过滤数据。

你将学习如何连接到 MySQL 服务器，创建数据库和表（customers 和 orders），然后构建利用子查询来识别订单总金额超过特定值的客户的 SQL 查询。本实验还涵盖了使用带子查询的 EXISTS，测试相关子查询以及比较子查询的性能。

在 WHERE 子句中编写子查询

在本步骤中，你将学习如何在 SQL 语句的 WHERE 子句中使用子查询。子查询是嵌套在另一个查询中的查询。它是根据从其他表或同一表中派生的条件检索数据的强大工具。

理解子查询

子查询（或内部查询）是嵌套在较大查询中的 SQL 查询。子查询首先执行，其结果由外部查询使用。子查询可以出现在 WHERE、SELECT、FROM 和 HAVING 子句中。

在 WHERE 子句中，子查询通常用于根据条件过滤外部查询的结果。子查询返回一个单一值或一组值，外部查询使用这些值进行比较。

场景

假设你有两个表：customers 和 orders。customers 表包含客户信息（例如 customer_id、name、city），orders 表包含订单信息（例如 order_id、customer_id、order_date、total_amount）。

你想找出所有至少下过一笔订单且订单总金额大于 100 美元的客户。

步骤

  1. 连接到 MySQL 服务器：

    打开你的终端，并执行以下命令以 root 用户连接到你的 MySQL 服务器：

    sudo mysql -u root

    你应该会看到 MySQL 提示符：mysql>。

  2. 创建数据库和表：

    如果你还没有数据库和表，请立即创建它们。我们来创建一个名为 labdb 的数据库以及 customers 和 orders 表。在 MySQL 提示符中执行以下 SQL 命令：

    CREATE DATABASE IF NOT EXISTS labdb;
USE labdb;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

  3. 插入示例数据：

    向表中插入一些示例数据。在 MySQL 提示符中执行以下 SQL 命令：

    INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice Smith', 'New York'),
(2, 'Bob Johnson', 'Los Angeles'),
(3, 'Charlie Brown', 'Chicago'),
(4, 'David Lee', 'Houston');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-01-15', 120.00),
(2, '2023-02-20', 80.00),
(1, '2023-03-10', 150.00),
(3, '2023-04-05', 200.00),
(2, '2023-05-12', 110.00),
(4, '2023-06-18', 90.00);

  4. 在 WHERE 子句中编写子查询：

    现在，编写查询以查找订单总金额大于 100 美元的客户。在 MySQL 提示符中执行以下 SQL 命令：

    SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 100);

    解释：

    • 子查询 (SELECT customer_id FROM orders WHERE total_amount > 100) 从 orders 表中选择 total_amount 大于 100 的 customer_id。
    • 外部查询 SELECT * FROM customers WHERE customer_id IN (...) 从 customers 表中选择所有列，其中 customer_id 包含在子查询返回的 customer_id 集合中。

  5. 观察输出：

    你应该会看到以下输出，显示下过订单总金额大于 100 美元的客户：

    +-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
+-------------+-------------+-----------+
3 rows in set (0.00 sec)

使用 EXISTS 和子查询

在本步骤中，你将学习如何在 MySQL 中使用 EXISTS 运算符配合子查询。EXISTS 运算符用于测试子查询中是否存在行。如果子查询返回任何行，它将返回 TRUE，否则返回 FALSE。

理解 EXISTS

EXISTS 运算符通常用于 SQL 语句的 WHERE 子句中，以根据另一个表中相关数据的存在情况来过滤结果。它是使用 IN 或 JOIN 操作的强大替代方案，有时效率更高，尤其是在处理大型数据集时。

与 IN 不同，EXISTS 实际上不检索子查询中的数据。它只是检查是否返回了任何行。当你只需要知道匹配项是否存在，而不需要实际值时，这可以使其比 IN 更快。

场景

继续使用上一步中的 customers 和 orders 表，让我们找出所有至少下过一笔订单的客户。

先决条件

请确保你已完成上一步（“在 WHERE 子句中编写子查询”），并且 labdb 数据库、customers 表和 orders 表已填充了数据。

步骤

  1. 使用 EXISTS 编写查询：

    编写查询以查找至少下过一笔订单的客户。在 MySQL 提示符中执行以下 SQL 命令：

    SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

    解释：

    • 外部查询 SELECT * FROM customers c 从 customers 表中选择所有列，并为其设置别名 c。
    • WHERE EXISTS (...) 子句检查子查询是否返回任何行。
    • 子查询 SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id 从 orders 表（别名为 o）中选择值 1（也可以是任何常量值），其中 orders 表中的 customer_id 与 customers 表中的 customer_id 匹配。
    • 如果子查询至少返回一行，EXISTS 运算符将返回 TRUE，表示该客户至少下过一笔订单。

  2. 观察输出：

    你应该会看到以下输出，显示所有至少下过一笔订单的客户：

    +-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
|           4 | David Lee   | Houston   |
+-------------+-------------+-----------+
4 rows in set (0.00 sec)

  3. 修改查询（可选）：

    让我们修改查询以查找从未下过任何订单的客户。你可以通过使用 NOT EXISTS 来实现此目的。在 MySQL 提示符中执行以下 SQL 命令：

    SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

  4. 观察输出：

    由于我们示例数据中的所有客户都下过订单，因此此查询应返回一个空结果集：

    Empty set (0.00 sec)

测试相关子查询

在本步骤中，你将学习 MySQL 中的相关子查询。相关子查询是引用外部查询中列的子查询。这意味着子查询会为外部查询中的每一行执行一次。

理解相关子查询

与仅执行一次的简单子查询不同，相关子查询依赖于外部查询来获取其值。子查询使用外部查询当前行的值来确定其结果。这使得相关子查询对于某些类型的查询功能更强大，但也可能比简单子查询效率低，尤其是在处理大型数据集时。

场景

继续使用 customers 和 orders 表，让我们找出所有下过订单且订单金额大于 所有 订单平均订单金额的客户。

先决条件

请确保你已完成之前的步骤，并且 labdb 数据库、customers 表和 orders 表已填充了数据。

步骤

  1. 编写相关子查询：

    编写查询以查找下过订单且订单金额大于平均订单金额的客户。在 MySQL 提示符中执行以下 SQL 命令：

    SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > (SELECT AVG(total_amount) FROM orders)
);

    解释：

    • 外部查询 SELECT c.customer_id, c.name FROM customers c 从 customers 表中选择 customer_id 和 name，并为其设置别名 c。
    • WHERE EXISTS (...) 子句检查子查询是否返回任何行。
    • 相关子查询 SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > (SELECT AVG(total_amount) FROM orders) 从 orders 表（别名为 o）中选择值 1，其中：
      • o.customer_id = c.customer_id：这是相关性。子查询引用了外部查询 customers 表中的 customer_id。
      • o.total_amount > (SELECT AVG(total_amount) FROM orders)：此条件检查订单金额是否大于 所有 订单的平均订单金额。AVG(total_amount) 子查询是一个非相关子查询，它只执行一次以获取平均订单金额。

  2. 观察输出：

    你应该会看到以下输出，显示下过订单金额大于平均订单金额的客户：

    +-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           1 | Alice Smith |
|           3 | Charlie Brown |
+-------------+-------------+
2 rows in set (0.00 sec)

  3. 另一个示例：查找每个客户的最高订单金额

    在 MySQL 提示符中执行以下 SQL 命令，以检索每个客户的 ID、姓名及其最高订单金额。相关子查询单独计算每个客户的最大订单金额。

    SELECT c.customer_id, c.name, (
    SELECT MAX(o.total_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) AS highest_order_amount
FROM customers c;

  4. 观察输出：

    你应该会看到以下输出：

    +-------------+-------------+-----------------------+
| customer_id | name        | highest_order_amount  |
+-------------+-------------+-----------------------+
|           1 | Alice Smith |                150.00 |
|           2 | Bob Johnson |                110.00 |
|           3 | Charlie Brown |                200.00 |
|           4 | David Lee   |                 90.00 |
+-------------+-------------+-----------------------+
4 rows in set (0.00 sec)

比较子查询性能

在本步骤中，你将学习如何比较 MySQL 中不同类型子查询的性能。理解子查询的性能特点对于编写高效的 SQL 查询至关重要，尤其是在处理大型数据集时。

理解性能考量

子查询的性能会受到几个因素的影响，包括：

  • 数据量： 查询涉及的表的大小。
  • 子查询类型： 子查询是相关的还是非相关的。
  • 索引： 表上是否存在有效的索引。
  • MySQL 版本： 使用的具体 MySQL 版本，因为查询优化技术可能有所不同。

场景

继续使用 customers 和 orders 表，让我们比较使用 IN 的子查询与使用 EXISTS 的子查询的性能，以找出至少下过一笔订单的所有客户。

先决条件

请确保你已完成之前的步骤，并且 labdb 数据库、customers 表和 orders 表已填充了数据。为了使性能比较更有意义，我们将向 orders 表添加更多数据。

步骤

  1. 向 orders 表添加更多数据：

    为了使性能比较更真实，让我们向 orders 表添加大量数据。我们将使用存储过程为每个客户插入 1000 笔订单。在 MySQL 提示符中执行以下 SQL 命令：

    DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000 DO
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;

    解释：

    • 此 SQL 脚本创建了一个名为 insert_many_orders 的存储过程。
    • 该过程将 1000 笔订单插入到 orders 表中，每个客户对应 1000 笔。
    • 插入数据后，该过程将被删除。

  2. 使用 IN 的查询：

    执行以下使用 IN 的查询，以查找至少下过一笔订单的所有客户：

    SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

  3. 使用 EXPLAIN 分析查询执行计划：

    在执行查询之前，使用 EXPLAIN 命令分析查询执行计划。这将让你了解 MySQL 如何计划执行查询，并识别潜在的性能瓶颈。在 MySQL 提示符中执行以下 SQL 命令：

    EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

    EXPLAIN 的输出将显示正在访问的表、正在使用的索引（如果有）以及操作执行的顺序。请注意 type 列，它指示了使用的连接或访问方法的类型。

  4. 使用 EXISTS 的查询：

    执行以下使用 EXISTS 的查询，以查找至少下过一笔订单的所有客户：

    SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

  5. 使用 EXPLAIN 分析查询执行计划：

    使用 EXPLAIN 命令分析 EXISTS 查询的查询执行计划。在 MySQL 提示符中执行以下 SQL 命令：

    EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

    将此执行计划与 IN 查询的执行计划进行比较。在访问的表、使用的索引或访问方法方面是否存在任何差异？

  6. 观察结果：

    总的来说，当子查询返回大量行时，EXISTS 的性能通常优于 IN。这是因为 IN 需要将外部查询的值与子查询返回的所有值进行比较，而 EXISTS 在找到匹配项后就会停止。但是，实际性能可能会因具体查询、数据和数据库系统而异。你可以使用 BENCHMARK() 函数（如原始文档所示）来更精确地测量执行时间，但对于本次实验，分析 EXPLAIN 输出足以了解查询计划。

  7. 清理（可选）：

    如果你想清理数据库和表，可以在 MySQL 提示符中执行以下命令：

    DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;

    完成所有步骤后，你可以通过键入以下命令退出 MySQL 客户端：

    exit

总结

在本实验中，你学习了如何在 SQL 语句的 WHERE 子句中使用子查询，以根据从其他表或同一表中派生的条件来过滤数据。你练习了连接到 MySQL 服务器、创建数据库和表以及插入示例数据。

你探索了使用带有子查询的 IN 操作符，根据 orders 表中的相关数据来查找客户。你还了解了 EXISTS 操作符作为 IN 的替代方案，并练习使用它来检查相关行的存在。

此外，你还接触了相关子查询，它们引用外部查询中的列，并使用相关子查询查找订单金额超过平均订单金额的客户。最后，你通过使用 EXPLAIN 命令分析它们的执行计划，比较了 IN 和 EXISTS 子查询的性能，从而深入了解了 MySQL 如何处理这些查询。

相关 MySQL 课程
MySQL 入门

MySQL 入门

databasemysql
高级 MySQL 实战实验室

高级 MySQL 实战实验室

databasemysql
管理 MySQL 用户权限

管理 MySQL 用户权限

databasemysql