MySQL - 子查询

MySQL：掌握子查询及其替代方案

子查询（subquery），也称为嵌套查询或内部查询，是嵌入在另一个 SQL 查询内部的 SELECT 查询。它们是执行复杂数据检索的强大工具，通过使用一个查询的结果来过滤或为另一个查询提供数据。子查询可以在 SELECT、FROM、WHERE、INSERT、UPDATE 和 DELETE 语句中使用。

场景设置

在我们的示例中，假设我们有两个表：employees（员工）和 departments（部门）。

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');

INSERT INTO employees VALUES
(101, 'Alice', 90000, 1),
(102, 'Bob', 85000, 1),
(103, 'Charlie', 70000, 2),
(104, 'Diana', 72000, 2),
(105, 'Eve', 60000, 3);

WHERE 子句中的子查询

子查询最常见的用途是在 WHERE 子句中过滤结果。例如，让我们查找所有在“Engineering”（工程）部门工作的员工。

使用 IN 关键字的子查询

内部查询查找“Engineering”部门的 department_id，外部查询查找所有匹配该 ID 的员工。

SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE department_name = 'Engineering'
);

现代替代方案：使用 JOIN

对于这类问题，使用 JOIN（连接）通常更具可读性，并且性能可能更好，因为数据库优化器对连接进行了高度优化。它更直接地表达了表之间的关系。

SELECT e.name, e.salary
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

这两个查询产生相同的结果，但通常更推荐使用 JOIN 版本。

name	salary
Alice	90000.00
Bob	85000.00

带有比较运算符的子查询

你还可以使用返回单个值（标量子查询，scalar subquery）的子查询与 =、>、< 等比较运算符一起使用。让我们找出收入高于公司平均薪资的员工。

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

现代替代方案：公共表表达式（CTE）

对于更复杂的多步逻辑，嵌套子查询可能变得难以阅读。MySQL 8.0 引入了公共表表达式（Common Table Expressions，CTEs），它允许你定义命名的、临时的结果集。让我们找出每个部门中收入高于其部门平均薪资的员工。

WITH DepartmentAvg AS (
    SELECT
        department_id,
        AVG(salary) as avg_dept_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.name,
    e.salary,
    d.department_name
FROM employees AS e
JOIN DepartmentAvg AS da ON e.department_id = da.department_id
JOIN departments AS d ON e.department_id = d.department_id
WHERE e.salary > da.avg_dept_salary;

CTE 通过将其分解为逻辑的、可重用的步骤，使逻辑更加清晰。

带有 INSERT 语句的子查询

子查询可以用于从其他表填充数据到表中。这对于创建备份或汇总表非常有用。让我们为工程部门的员工创建一个备份。

CREATE TABLE engineering_archive LIKE employees;

INSERT INTO engineering_archive (employee_id, name, salary, department_id)
SELECT employee_id, name, salary, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Engineering');

通过客户端程序使用子查询

从应用程序代码执行子查询时，关键是使用预处理语句 (prepared statements) 来安全地将任何变量传递到查询中，从而防止 SQL 注入（SQL injection）。

Python：查找特定部门的员工

import mysql.connector

def get_employees_by_dept(department_name):
    results = []
    query = """
        SELECT name, salary
        FROM employees
        WHERE department_id IN (
            SELECT department_id FROM departments WHERE department_name = %s
        )
    """
    try:
        conn = mysql.connector.connect(user='root', password='my-secret-pw', database='your_db')
        cursor = conn.cursor(dictionary=True)

        cursor.execute(query, (department_name,))
        results = cursor.fetchall()

        print(f"Employees in {department_name}:")
        for row in results:
            print(f"- {row['name']}, Salary: {row['salary']}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
    return results

if __name__ == "__main__":
    get_employees_by_dept('Sales')

Node.js：查找高于特定薪资阈值的员工

const mysql = require('mysql2/promise');

async function findHighEarners(minSalary) {
  let connection;
  try {
    connection = await mysql.createConnection({ host: 'localhost', user: 'root', password: 'my-secret-pw', database: 'your_db' });

    // Using a JOIN is better here, but this shows a parameterized subquery.
    const query = 'SELECT name, salary FROM employees WHERE salary > ? ORDER BY salary DESC';
    const [rows] = await connection.execute(query, [minSalary]);

    console.log(`Employees earning more than ${minSalary}:`);
    rows.forEach(row => {
        console.log(`- ${row.name}, Salary: ${row.salary}`);
    });

  } catch (error) {
    console.error('Database query failed:', error);
  } finally {
    if (connection) await connection.end();
  }
}

findHighEarners(80000);