MySQL 子查询

介绍

在MySQL中，子查询（Subquery）是指嵌套在其他查询中的查询。子查询可以出现在 SELECT、INSERT、UPDATE、DELETE 语句中，也可以出现在 WHERE 或 HAVING 子句中。子查询的结果可以作为外部查询的条件或数据源。

子查询通常用于解决复杂的查询问题，例如从一个表中获取数据，然后将其用作另一个查询的条件。子查询可以返回单个值、单行、多行或多列，具体取决于查询的需求。

备注

子查询必须用括号 () 包裹，并且可以嵌套多层。

子查询的类型

子查询可以分为以下几类：

标量子查询（Scalar Subquery）：返回单个值的子查询。
行子查询（Row Subquery）：返回单行多列的子查询。
列子查询（Column Subquery）：返回单列多行的子查询。
表子查询（Table Subquery）：返回多行多列的子查询。

标量子查询

标量子查询返回单个值，通常用于 WHERE 子句或 SELECT 列表中。

示例

假设我们有一个 employees 表，结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

我们想要查询工资高于平均工资的员工：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

在这个例子中，子查询 (SELECT AVG(salary) FROM employees) 返回一个标量值（平均工资），然后外部查询使用这个值来过滤出工资高于平均工资的员工。

行子查询

行子查询返回单行多列的结果，通常用于 WHERE 子句中与行进行比较。

示例

假设我们有一个 orders 表和一个 customers 表：

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

我们想要查询与某个特定客户（例如 id = 1）在同一城市的所有订单：

SELECT *
FROM orders
WHERE customer_id IN (
    SELECT id
    FROM customers
    WHERE city = (SELECT city FROM customers WHERE id = 1)
);

在这个例子中，子查询 (SELECT city FROM customers WHERE id = 1) 返回一个标量值（城市名称），然后外部查询使用这个值来过滤出与特定客户在同一城市的所有订单。

列子查询

列子查询返回单列多行的结果，通常用于 IN、ANY、ALL 等操作符中。

示例

假设我们想要查询所有工资高于部门平均工资的员工：

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department_id
);

在这个例子中，子查询 (SELECT AVG(salary) FROM employees GROUP BY department_id) 返回每个部门的平均工资，然后外部查询使用 ALL 操作符来过滤出工资高于所有部门平均工资的员工。

表子查询

表子查询返回多行多列的结果，通常用于 FROM 子句中作为临时表。

示例

假设我们想要查询每个部门的最高工资及其对应的员工：

SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) AS dept_max
ON e.department_id = dept_max.department_id AND e.salary = dept_max.max_salary;

在这个例子中，子查询 (SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id) 返回每个部门的最高工资，然后外部查询将其与 employees 表进行连接，以获取每个部门中工资最高的员工。

实际案例

案例1：查询每个部门的最高工资

假设我们有一个 departments 表和一个 employees 表：

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT
);

我们想要查询每个部门的最高工资及其对应的员工：

SELECT d.name AS department_name, e.name AS employee_name, e.salary
FROM departments d
JOIN employees e ON d.id = e.department_id
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = d.id
);

在这个例子中，子查询 (SELECT MAX(salary) FROM employees WHERE department_id = d.id) 返回每个部门的最高工资，然后外部查询将其与 employees 表进行连接，以获取每个部门中工资最高的员工。

案例2：查询没有订单的客户

假设我们有一个 customers 表和一个 orders 表：

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

我们想要查询没有下过订单的客户：

SELECT name
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM orders
);

在这个例子中，子查询 (SELECT customer_id FROM orders) 返回所有下过订单的客户ID，然后外部查询使用 NOT IN 操作符来过滤出没有下过订单的客户。

总结

MySQL子查询是一个强大的工具，可以帮助我们解决复杂的查询问题。通过嵌套查询，我们可以从一个查询中获取数据，然后将其用作另一个查询的条件或数据源。子查询可以返回单个值、单行、多行或多列，具体取决于查询的需求。

在实际应用中，子查询常用于过滤数据、计算聚合值、连接表等场景。掌握子查询的使用方法，可以大大提高我们编写复杂查询的能力。

附加资源

练习

编写一个查询，找出工资高于其所在部门平均工资的员工。
编写一个查询，找出没有下过订单的客户。
编写一个查询，找出每个部门的最高工资及其对应的员工。

通过完成这些练习，你将更好地理解MySQL子查询的使用方法。

介绍​

子查询的类型​

标量子查询​

示例​

行子查询​

示例​

列子查询​

示例​

表子查询​

示例​

实际案例​

案例1：查询每个部门的最高工资​

案例2：查询没有订单的客户​

总结​

附加资源​

练习​

介绍