MySQL 公共表表达式 (CTEs) 教程

介绍

在本实验中，你将学习如何在 MySQL 中使用公共表表达式 (Common Table Expressions, CTEs)。CTEs 通过将复杂查询分解为逻辑清晰、易于阅读的步骤来简化它们。CTE 创建一个临时的、命名的结果集，你可以在单个 SQL 语句中引用它。

你将首先设置一个数据库，并创建一个简单的 CTE 来过滤员工数据。然后，你将探索递归 CTEs 来处理层级数据，例如组织结构图。最后，你将学习如何将 CTE 与表连接起来执行更高级的数据分析。

这是一个实验（Guided Lab），提供逐步指导来帮助你学习和实践。请仔细按照说明完成每个步骤，获得实际操作经验。根据历史数据，这是一个初级级别的实验，完成率为 100%。获得了学习者 92% 的好评率。

设置数据库和简单的 CTE

在第一步中，你将设置数据库环境并定义一个简单的公共表表达式 (CTE)。CTEs 使用 WITH 子句定义，并充当一个临时视图，仅在单个查询的持续时间内存在。这使得复杂查询更易于阅读和管理。

首先，从你的桌面打开终端。

以 root 用户连接到 MySQL 服务器。在此实验环境中，你可以使用 sudo 连接而无需密码。

sudo mysql -u root

连接成功后，你将看到 MySQL 提示符 (mysql>)。现在，创建一个名为 labex_db 的数据库并切换到它。

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;

接下来，创建一个 employees 表并插入一些示例数据。此表将存储员工的基本信息，包括他们的部门和薪资。

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);

现在表已准备就绪，让我们定义一个简单的 CTE 来仅选择 'Sales' 部门的员工。

WITH SalesEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;

让我们分解一下这个查询：

WITH SalesEmployees AS (...): 这定义了一个名为 SalesEmployees 的 CTE。
括号内的查询从 employees 表中选择 department 为 'Sales' 的员工。
SELECT * FROM SalesEmployees;: 这是从我们临时的 SalesEmployees 结果集中检索所有数据的主查询。

你应该看到以下输出，仅列出了来自 Sales 部门的员工：

+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|           1 | John       | Doe       | 60000.00 |
|           3 | Robert     | Jones     | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)

这证实了你的第一个 CTE 工作正常。请保持在 MySQL shell 中进行下一步。

为层级数据创建递归 CTE

递归 CTEs 是查询层级数据（如组织结构图或嵌套类别）的强大功能。递归 CTE 通过自身引用来逐级处理树状结构。它包含一个“锚定成员”（起始点）和一个“递归成员”（迭代）。

在这一步中，你将创建一个表示员工层级的表，并使用递归 CTE 来显示它。

首先，在 MySQL shell 中，创建 employee_hierarchy 表。此表包含一个指向另一个员工 employee_id 的 manager_id，从而创建了层级结构。

CREATE TABLE IF NOT EXISTS employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);

在此结构中，'David' 是最高级别的经理，因为他的 manager_id 是 NULL。

现在，编写一个递归 CTE 来遍历此层级结构。需要使用 RECURSIVE 关键字。

WITH RECURSIVE EmployeeHierarchyCTE AS (
    -- Anchor member: selects the top-level manager
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: joins with itself to find subordinates
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employee_hierarchy e
    INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;

让我们分析一下这个查询：

锚定成员 (Anchor Member)：第一个 SELECT 语句查找层级的根（manager_id 为 NULL 的地方），并为其分配 level 0。
递归成员 (Recursive Member)：第二个 SELECT 语句将 employee_hierarchy 与 CTE 本身 (EmployeeHierarchyCTE) 连接起来。它查找所有 manager_id 与 CTE 中已有的 employee_id 匹配的员工，并递增 level。
UNION ALL: 此运算符将锚定成员和递归成员的结果合并。

查询将产生以下输出，显示完整的组织结构图及其层级：

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | David         |       NULL |     0 |
|           2 | Emily         |          1 |     1 |
|           3 | Frank         |          1 |     1 |
|           4 | Grace         |          2 |     2 |
|           5 | Henry         |          2 |     2 |
|           6 | Ivy           |          3 |     2 |
|           7 | Jack          |          3 |     2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)

你已成功使用递归 CTE 查询了层级数据。请保持在 MySQL shell 中进行最后一步。

将 CTE 与表进行 JOIN 操作

CTEs 可以像普通表一样与其他表进行连接。这对于将 CTE 的聚合数据与另一个表的详细数据结合起来非常有用。在这一步中，你将创建一个 CTE 来计算每个部门的平均薪资，然后将其连接回 employees 表。

在 MySQL shell 中，执行以下查询。它定义了一个名为 AvgSalaryByDepartment 的 CTE，然后将其与 employees 表连接。

WITH AvgSalaryByDepartment AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.employee_id,
    e.first_name,
    e.department,
    e.salary,
    a.avg_salary
FROM
    employees e
JOIN
    AvgSalaryByDepartment a ON e.department = a.department;

以下是详细说明：

AvgSalaryByDepartment CTE 计算每个部门的平均薪资。
主查询然后将 employees 表（别名为 e）与此 CTE（别名为 a）在 department 列上进行连接。
这使你能够显示每个员工的薪资及其部门的平均薪资。

预期的输出将是：

+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary   | avg_salary   |
+-------------+------------+------------+----------+--------------+
|           1 | John       | Sales      | 60000.00 | 57500.000000 |
|           2 | Jane       | Marketing  | 75000.00 | 72500.000000 |
|           3 | Robert     | Sales      | 55000.00 | 57500.000000 |
|           4 | Emily      | IT         | 90000.00 | 90000.000000 |
|           5 | Michael    | Marketing  | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)

你可以手动验证结果。例如，'Sales' 部门的平均薪资是 (60000 + 55000) / 2 = 57500，这与输出相符。这证实了你的查询工作正常。

你已成功将 CTE 与表连接。现在可以退出 MySQL shell。

exit;

总结

在此次实验中，你学习了如何在 MySQL 中有效地使用公共表表达式 (CTEs)。你从设置数据库和表开始，然后逐步创建了不同类型的 CTE。

你已学会：

使用 WITH 子句定义一个简单的 CTE，用于过滤数据并提高查询的可读性。
创建一个递归 CTE 来导航和显示表中的层级数据。
将 CTE 与表连接，以将聚合结果与详细的行级数据结合起来，进行更复杂的分析。

这些技能对于编写清晰、可维护且强大的 SQL 查询至关重要，尤其是在处理复杂逻辑或数据结构时。