MySQL 子查询示例

由： 编辑团队 编辑团队
Hours 更新

⚡ 智能摘要

MySQL 子查询语法将一个 SELECT 语句嵌套在另一个 SELECT 语句中，内部查询的结果会传递给外部查询。本文将解释标量子查询、行子查询和表子查询，以及它们的执行顺序、实际示例，并探讨其与 JOIN 操作的性能权衡。

  • 🔍 核心定义： 子查询是嵌套在另一个查询中的 SELECT 语句，内部查询首先运行，为外部查询提供值。
  • 🧮 标量子查询： 返回一行和一列，因此可以与等于、大于或小于等比较运算符配合使用。
  • 📋 行和表子查询： 行子查询返回一行包含多列的内容，而表子查询返回多行内容，并且与 IN 运算符一起使用。
  • 🧩 嵌套深度： 子查询可以嵌套多层，从而在一个语句中查找诸如最高薪酬成员之类的值。
  • ✍️ 超越 SELECT： INSERT、UPDATE 和 DELETE 语句接受子查询，这使得无需临时表即可进行批量更改。
  • ⚡ 绩效规则： JOIN 通常比等效的子查询运行速度快得多，因此子查询应留给 JOIN 无法表达的逻辑。
阅读更多

MySQL 子查询

SQL中的子查询是什么？

A 子查询 这是一个包含在另一个查询中的 SELECT 查询。内部 SELECT 查询通常用于确定外部 SELECT 查询的结果，因此数据库会先执行内部语句，然后将输出传递给外部查询。

内部查询被称为 内部查询 或者嵌套查询，包含它的语句称为 外部查询让我们来看一下子查询语法。

MySQL 子查询

上图显示了语句的一般结构：外部 SELECT 提供要查看的列，带括号的内部 SELECT 提供 WHERE 子句要与之比较的值或值列表。

为什么要使用子查询？

在了解不同类型的子查询之前，先了解子查询何时适合出现在语句中会很有帮助。

子查询用于回答一个事先不知道筛选值的问题。筛选值必须根据数据本身计算得出。MyFlix 视频库经常被用户抱怨电影数量太少，管理层希望购买电影时优先考虑电影数量最少的类别。在查询数据库之前，没有人知道是哪个类别，因此必须先计算出筛选值，然后再将其用作筛选条件。

子查询位于trac出于以下三个实际原因：

  • 可读性： 逻辑的每个部分都位于其自己的括号块中，因此该语句读起来像是一系列小问题，而不是一个复杂的表达式。
  • 隔离： 可以单独运行内部查询来确认它是否返回预期值，这使得测试和调试变得更加容易。
  • 灵活性： 同样的模式也适用于 WHERE、HAVING、SELECT 和 FROM 子句，以及 INSERT、UPDATE 和 DELETE 语句。

权衡的代价是速度，这一点将在本文后面的 JOIN 比较中进行探讨。

子查询的类型 MySQL

MySQL 支持三种类型的子查询，子查询的类型取决于内部查询返回结果的结构。下面将通过一个针对 myflixdb 数据库的示例来解释每种类型。

1) 标量子查询

A 标量子查询 返回一行一列的结果，这意味着它返回一个单独的值。由于结果是单个值，因此它可以用于任何允许使用字面值的地方。回到上面提到的 MyFlix 问题，您可以使用如下查询：

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

它给出了一个结果：

MySQL 子查询

让我们看看这个查询是如何工作的。

MySQL 子查询

如图所示，执行过程图 MySQL 首次运行 SELECT MIN(category_id) FROM movies它接收一个值，然后才使用该值运行外部查询。由于只返回一个值，因此允许使用的运算符是标准比较运算符集： =, <> （或 !=), >, >=, <和 <=.

💡提示： 如果子查询放在后面 = 返回多行数据， MySQL 引发错误 1242， 子查询返回多行切换操作员 IN或者加强内部 WHERE 子句。

2) 行子查询

A 行子查询 它也返回单行数据，但该行可能包含多列。因此，外部查询是将一行值与行构造函数进行比较，而不是与单个值进行比较。

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

允许使用的运算符与上面列出的比较运算符相同，但会一次性应用于整行。

不要错过：

3) 表子查询

A 表子查询 返回多行数据，通常还有多列数据，因此外部查询必须使用集合运算符，例如： IN, NOT IN, ANY, ALL 或 EXISTS.

假设你想获取已租借电影但尚未归还的会员的姓名和电话号码，以便打电话提醒他们。你可以使用类似这样的查询：

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

MySQL 子查询

让我们看看这个查询是如何工作的。

MySQL 子查询

在这种情况下，内部查询返回多个结果，因此将会员编号列表传递给…… IN 运算符返回所有匹配的成员。

嵌套子查询多层深度

到目前为止，你已经看到了两层结构。一个子查询还可以包含另一个子查询，从而产生三层嵌套语句。

假设管理层想要奖励缴费最高的会员。我们可以运行如下查询：

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

最内层查询查找最大付款金额，中间层查询将该金额转换为会员编号，最外层查询将会员编号转换为姓名。上述查询返回以下结果：

MySQL 子查询

如何在 INSERT、UPDATE 和 DELETE 中使用子查询

子查询不仅限于 SELECT 语句。同样的括号模式也适用于数据修改语句，它允许一次性修改整组行，而无需创建临时表。

使用子查询插入数据。 子查询可以提供要插入的行，从而将数据从一个表复制到另一个表。SELECT 语句的列列表必须与 INSERT 语句的列列表一致。

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

使用子查询进行更新。 内部查询决定要处理哪些行。下面的示例会标记所有租金尚未付清的会员。

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

使用子查询删除。 同样的方法也可以删除满足第二个表中所保存条件的行。

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️警告： MySQL 不允许语句在 FROM 子句的子查询中修改表并从同一表中选择数据。如果出现错误 1093，请将内部查询包装在派生表中，例如： SELECT * FROM (SELECT ...) AS t，让 MySQL 在应用更改之前，先将结果具体化。此外，最好先单独运行内部 SELECT 语句，并确认行数，然后再运行其他语句。 更新 或者 删除 在生产中。

子查询与连接

子查询和 JOIN 都可以合并来自多个表的信息，因此自然而然的问题是应该选择哪一个。

与连接相比，子查询使用起来更简单，也更容易阅读。它们不像连接那样复杂。 加入因此它们经常被……使用 SQL 初学者.

但是子查询存在性能问题。使用连接（JOIN）代替子查询有时可以带来高达 500 倍的性能提升，因为优化器只需一次遍历即可解析连接，而无需重复评估内部语句。

比较点 子查询 注册
可读性 高，因为每个方块回答一个问题 降低，因为所有表格都出现在一个子句中
性能 速度较慢时，内部查询可能需要对每一行外部查询都执行一次。 速度更快，而且往往快很多。
结果列 仅返回外部表的列 可以返回所有连接表中的列。
典型用途 筛选条件是必须先计算出一个值。 合并来自两个或多个表的相关行
学习曲线 温和，适合初学者 更陡峭，需要了解连接类型

如果可以选择，建议使用 JOIN 而不是子查询。 只有当无法使用 JOIN 操作实现上述目标时，才应使用子查询作为备选方案。

子查询与连接

子查询也很容易分解成单个逻辑组件，这在以下情况下非常有用： 测试 并调试查询。

常见问题

相关子查询引用外部查询的某一列，因此对于外部查询的每一行都会执行一次。非相关子查询是独立的，只执行一次。相关子查询功能强大，但在大型表上速度明显较慢。

子查询可以位于 WHERE 子句、HAVING 子句、SELECT 列表或 FROM 子句中，此时它会成为一个派生表，并且需要别名。子查询也可以用于 INSERT、UPDATE 和 DELETE 语句中。

通常是这样。例如，编辑器中内置的人工智能助手。 MySQL 工作台 可以提出一个等效方案 注册. 在信任重写操作之前，务必比较行数并阅读 EXPLAIN 计划，因为 NULL 值处理方式可能有所不同。

是的。文本转 SQL 助手会将诸如“哪个类别中的电影最少”之类的问题转换为嵌套的 SELECT 语句。准确性取决于提供给模型的模式，因此请对照实际的表名检查生成的语句。

当比较运算符后的子查询返回多行数据时，会出现此错误。请将比较运算符替换为 IN、ANY 或 EXISTS，或者优化内部 WHERE 子句，使其仅返回一行数据。

总结一下这篇文章：