## 介绍 在本实验中，你将深入了解 MySQL 子查询与嵌套查询的核心用法。重点内容是如何在 `WHERE` 子句中使用子查询，根据来自其他表或同一张表的条件对数据进行筛选与过滤。 你将学习如何连接 MySQL 服务器、创建数据库和数据表（`customers` 和 `orders`），并编写利用子查询筛选客户的 SQL 语句，例如找出订单总金额超过指定值的客户。本实验还会介绍带子查询的 `EXISTS` 用法、相关子查询的测试方式，以及不同子查询写法之间的性能比较。 ## 在 WHERE 子句中编写子查询 在本步骤中，你将学习如何在 SQL 语句的 `WHERE` 子句中使用子查询。子查询是嵌套在另一个查询中的查询语句，是根据来自其他表或当前表的条件检索数据的重要工具，在 MySQL 查询优化和数据筛选中非常常见。 **理解子查询** 子查询（也叫内部查询）是嵌套在更大 SQL 查询中的查询。通常子查询会先执行，然后其结果再被外层查询使用。子查询可以出现在 `WHERE`、`SELECT`、`FROM` 和 `HA VING` 子句中。 在 `WHERE` 子句中，子查询通常用于按条件过滤外部查询结果。子查询可能返回单个值，也可能返回一组值，而外部查询会基于这些返回值进行比较判断。 **场景** 假设你有两张表：`customers` 和 `orders`。`customers` 表保存客户信息（如 `customer_id`、`name`、`city`），`orders` 表保存订单信息（如 `order_id`、`customer_id`、`order_date`、`total_amount`）。 现在你希望查找所有至少下过一笔订单，并且订单总金额大于 100 美元的客户。 **步骤** 1. **连接到 MySQL 服务器：** 打开终端，并执行以下命令，以 `root` 用户身份连接到 MySQL 服务器： ```bash sudo mysql -u root ``` 连接成功后，你会看到 MySQL 提示符：`mysql>`。 2. **创建数据库和表：** 如果数据库和数据表尚未创建，请先完成创建。下面将创建名为 `labdb` 的数据库，以及 `customers` 和 `orders` 两张表。在 MySQL 提示符中执行以下 SQL 命令： ```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 命令： ```sql INSERT INTO customers (customer_id, name, city) VALUES (1, 'Alice Smith', 'New York'), (2, 'Bob Johnson', 'Los Angeles'), (3, 'Charlie Brown', 'Chicago'), (4, 'Da vid 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 子句中编写子查询：** 现在，编写 SQL 查询，找出订单总金额大于 100 美元的客户。在 MySQL 提示符中执行以下 SQL 命令： ```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` 出现在子查询结果集中的客户。 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` 并不会真正取出子查询中的具体数据，而只是检查是否存在满足条件的记录。当你只关心“是否存在匹配项”，而不需要具体值时，`EXISTS` 往往更加高效。 **场景** 继续使用上一步中的 `customers` 和 `orders` 表，下面我们来查找所有至少下过一笔订单的客户。 **先决条件** 请确保你已经完成上一步“在 WHERE 子句中编写子查询”，并且 `labdb` 数据库、`customers` 表和 `orders` 表中已经插入了示例数据。 **步骤** 1. **使用 EXISTS 编写查询：** 编写查询，查找至少下过一笔订单的客户。在 MySQL 提示符中执行以下 SQL 命令： ```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`）中查找与当前客户 `customer_id` 匹配的订单。 - 如果子查询返回至少一条记录，则 `EXISTS` 的结果为 `TRUE`，说明该客户至少有一笔订单。 2. **观察输出：** 你应该会看到如下输出，显示所有至少下过一笔订单的客户： ``` +-------------+-------------+-----------+ | customer_id | name | city | +-------------+-------------+-----------+ | 1 | Alice Smith | New York | | 2 | Bob Johnson | Los Angeles | | 3 | Charlie Brown | Chicago | | 4 | Da vid Lee | Houston | +-------------+-------------+-----------+ 4 rows in set (0.00 sec) ``` 3. **修改查询（可选）：** 你还可以将查询改为查找从未下过任何订单的客户，这可以通过 `NOT EXISTS` 来实现。在 MySQL 提示符中执行以下 SQL 命令： ```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 中的相关子查询。相关子查询是指在子查询内部引用了外层查询列的写法，这意味着子查询会随着外层查询的每一行分别执行一次。 **理解相关子查询** 与只执行一次的普通子查询不同，相关子查询依赖外层查询当前行的数据来计算结果。也就是说，子查询会根据外层查询当前记录的值动态判断结果。这种写法在某些 SQL 查询场景中非常灵活，但在处理大数据量时，性能通常会比非相关子查询更低。 **场景** 继续使用 `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 A VG(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 A VG(total_amount) FROM orders)` 会从 `orders` 表中筛选满足条件的订单，其中： - `o.customer_id = c.customer_id` 表示将订单表与外层客户表建立关联，也就是只检查当前客户自己的订单。 - `o.total_amount > (SELECT A VG(total_amount) FROM orders)` 表示该订单金额必须高于所有订单的平均金额。这里的 `A VG(total_amount)` 子查询属于非相关子查询，只执行一次，用于先计算整体平均订单金额。 2. **观察输出：** 你会看到类似下面的结果，列出的就是那些存在高于平均订单金额订单的客户： ```n +-------------+-------------+n | customer_id | name |n +-------------+-------------+n | 1 | Alice Smith |n | 3 | Charlie Brown |n +-------------+-------------+n 2 rows in set (0.00 sec)n ``` 3. **另一个示例：查找每个客户的最高订单金额** 在 MySQL 提示符中执行下面这条 SQL 语句，查询每位客户的 ID、姓名以及他们各自的最高订单金额。这里使用的是相关子查询，因为它会针对每位客户分别计算一次最大订单金额。 ```sql 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. **观察输出：** 你应该会看到如下输出： ```n +-------------+-------------+-----------------------+n | customer_id | name | highest_order_amount |n +-------------+-------------+-----------------------+n | 1 | Alice Smith | 150.00 |n | 2 | Bob Johnson | 110.00 |n | 3 | Charlie Brown | 200.00 |n | 4 | Da vid Lee | 90.00 |n +-------------+-------------+-----------------------+n 4 rows in set (0.00 sec)n ``` ## 比较子查询性能 在本步骤中，你将学习如何比较 MySQL 中不同类型子查询的性能表现。理解子查询的性能差异，对于编写高效 SQL 查询和优化数据库检索速度非常重要，尤其是在处理大规模数据时。 **理解性能考量** 子查询性能通常会受到以下几个因素影响： - **数据量：** 查询涉及的数据表大小和记录数量。 - **子查询类型：** 使用的是相关子查询还是非相关子查询。 - **索引：** 表中是否建立了合适且有效的索引。 - **MySQL 版本：** 不同 MySQL 版本的查询优化器实现可能存在差异。 **场景** 继续使用 `customers` 和 `orders` 表，我们将比较使用 `IN` 子查询与使用 `EXISTS` 子查询的性能，以查找所有至少下过一笔订单的客户。 **先决条件** 请确保你已完成前面的步骤，并且 `labdb` 数据库、`customers` 表和 `orders` 表中已有测试数据。为了让性能比较更直观，我们还会向 `orders` 表中插入更多记录。 **步骤** 1. **向 `orders` 表添加更多数据：** 为了让性能测试更接近真实场景，我们向 `orders` 表批量插入更多数据。下面的存储过程会为每位客户插入 1000 笔订单。在 MySQL 提示符中执行以下 SQL 命令： ```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` 的存储过程。 - 该过程会向 `orders` 表中批量插入 1000 轮订单数据，每位客户对应 1000 笔订单。 - 插入完成后，存储过程会被删除，以保持环境整洁。 2. **使用 `IN` 的查询：** 执行以下使用 `IN` 子查询的 SQL，查找所有至少下过一笔订单的客户： ```sql SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders); ``` 3. **使用 `EXPLAIN` 分析查询执行计划：** 在正式评估查询前，先使用 `EXPLAIN` 查看执行计划。这可以帮助你理解 MySQL 会如何执行该查询，并发现可能存在的性能瓶颈。在 MySQL 提示符中执行以下 SQL 命令： ```sql EXPLAIN SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders); ``` `EXPLAIN` 的输出会展示访问了哪些表、是否使用了索引，以及各个操作的执行顺序。请特别关注 `type` 列，它表示使用了哪种访问或连接方式。 4. **使用 `EXISTS` 的查询：** 执行以下使用 `EXISTS` 的查询，查找所有至少下过一笔订单的客户： ```sql SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); ``` 5. **使用 `EXPLAIN` 分析查询执行计划：** 使用 `EXPLAIN` 分析 `EXISTS` 查询的执行计划。在 MySQL 提示符中执行以下 SQL 命令： ```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 提示符中执行以下命令： ```sql DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS customers; DROP DATABASE IF EXISTS labdb; ``` 完成全部步骤后，你可以输入以下命令退出 MySQL 客户端： ```sql exit ``` ## 总结 在本实验中，你学习了如何在 SQL 语句的 `WHERE` 子句中使用子查询，并根据来自其他表或同一张表的条件对数据进行筛选。你还练习了连接 MySQL 服务器、创建数据库和数据表，以及插入示例数据。 你进一步掌握了带有子查询的 `IN` 运算符，学会根据 `orders` 表中的相关数据筛选客户。同时，你也了解了 `EXISTS` 作为 `IN` 的替代方案，并练习使用它判断关联记录是否存在。 此外，你还接触了 MySQL 相关子查询，理解了它如何引用外层查询中的列，并使用这种方式找出订单金额高于平均订单金额的客户。最后，你通过 `EXPLAIN` 分析执行计划，对 `IN` 与 `EXISTS` 两类子查询的性能进行了比较，从而更深入地理解了 MySQL 查询优化与执行机制。