MySQL视图之：`视图`的`复杂`查询：`视图`中是否可以使用`ORDER BY`、`GROUP BY`。

好的，没问题。

MySQL 视图中的复杂查询：ORDER BY 和 GROUP BY 的使用

大家好，今天我们来深入探讨 MySQL 视图中 ORDER BY 和 GROUP BY 的使用，特别是涉及复杂查询时的一些注意事项。视图是一个虚拟表，它基于 SQL 查询的结果。它可以简化复杂查询、提高安全性、并为用户提供更友好的数据访问方式。但是，在视图中使用 ORDER BY 和 GROUP BY 时有一些限制和最佳实践需要了解。

1. 视图的基本概念

首先，回顾一下视图的基本概念。 视图是存储在数据库中的一个 SQL 查询语句。当用户查询视图时，数据库会执行视图定义的查询，并将结果返回给用户，就像查询普通表一样。

创建视图的语法:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

示例:

假设我们有一个 employees 表，包含员工的 ID、姓名、部门和薪水：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 50000.00),
(2, 'Bob', 'Marketing', 60000.00),
(3, 'Charlie', 'Sales', 55000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00);

我们可以创建一个视图，只显示 Sales 部门的员工信息：

CREATE VIEW sales_employees AS
SELECT employee_id, employee_name, salary
FROM employees
WHERE department = 'Sales';

现在，我们可以像查询普通表一样查询 sales_employees 视图：

SELECT * FROM sales_employees;

2. ORDER BY 在视图中的使用

ORDER BY 子句用于对查询结果进行排序。 在视图的定义中，使用 ORDER BY 需要特别注意。

MySQL 8.0 之前的版本:

在 MySQL 8.0 之前的版本中，视图定义中通常不允许使用 ORDER BY，除非同时使用了 LIMIT 子句。这是因为视图本身不保证任何特定的排序。 排序应该在查询视图时进行，而不是在视图定义中。

示例 (MySQL 8.0 之前):

以下视图定义在 MySQL 8.0 之前的版本中可能会报错：

--  以下语句可能报错
CREATE VIEW ordered_employees AS
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC;

但以下语句在 MySQL 8.0 之前的版本中是允许的，因为它使用了 LIMIT 子句：

CREATE VIEW ordered_employees AS
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1000; -- 必须有 LIMIT

MySQL 8.0 及以后版本:

MySQL 8.0 及以后的版本允许在视图定义中使用 ORDER BY，即使没有 LIMIT 子句。 但是，这种 ORDER BY 仅在视图被直接查询时才有效。如果视图被用作子查询，或者与其他表进行连接，则视图定义中的 ORDER BY 将被忽略。这是因为优化器可能会根据整体查询的需要重新排序结果。

示例 (MySQL 8.0 及以后):

CREATE VIEW ordered_employees AS
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC;

-- 直接查询视图，ORDER BY 生效
SELECT * FROM ordered_employees;

-- 视图作为子查询，ORDER BY 可能被忽略
SELECT * FROM (SELECT * FROM ordered_employees) AS subquery;

-- 视图与其他表连接，ORDER BY 可能被忽略
SELECT e.* FROM ordered_employees e JOIN some_other_table s ON e.employee_id = s.employee_id;

总结:

MySQL 版本	视图中 ORDER BY 的使用	注意事项
8.0 之前	仅在同时使用 LIMIT 时允许	排序最好在查询视图时进行
8.0 及以后	允许，即使没有 LIMIT	仅在直接查询视图时生效，作为子查询或连接时可能被忽略

最佳实践:

为了避免混淆和不可预测的行为，建议在查询视图时显式地使用 ORDER BY，而不是依赖视图定义中的 ORDER BY。 这样可以确保排序按照你的预期进行，并且不受视图使用方式的影响。

3. GROUP BY 在视图中的使用

GROUP BY 子句用于将具有相同值的行分组到一起，通常与聚合函数（如 COUNT, SUM, AVG, MIN, MAX）一起使用。 在视图中使用 GROUP BY 是完全允许的，并且非常有用，可以创建汇总数据的视图。

示例:

假设我们想创建一个视图，显示每个部门的平均薪水：

CREATE VIEW department_avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

现在，我们可以查询 department_avg_salary 视图，获取每个部门的平均薪水：

SELECT * FROM department_avg_salary;

复杂查询中的 GROUP BY:

GROUP BY 在视图中可以用于更复杂的查询，例如，计算每个部门薪水高于全体员工平均薪水的人数：

CREATE VIEW high_earners_per_dept AS
SELECT department, COUNT(*) AS high_earners_count
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
GROUP BY department;

结合 WHERE 子句:

可以在 GROUP BY 之前使用 WHERE 子句来过滤数据，然后再进行分组和聚合。例如，只计算 Sales 和 Marketing 部门的平均薪水：

CREATE VIEW sales_marketing_avg_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE department IN ('Sales', 'Marketing')
GROUP BY department;

注意事项:

• 在视图中使用 GROUP BY 时，必须包含所有未聚合的列在 GROUP BY 子句中。 例如，如果 SELECT 子句中包含了 department 和 AVG(salary)，那么 GROUP BY 子句必须包含 department。
• HAVING 子句可以用于过滤 GROUP BY 后的结果。 例如，只显示平均薪水高于 60000 的部门：

CREATE VIEW high_avg_salary_departments AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

高级示例：使用 WITH ROLLUP

WITH ROLLUP 是一个 GROUP BY 子句的扩展，它可以在结果集中添加额外的行，用于显示分组的汇总值。

CREATE VIEW department_salary_rollup AS
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;

查询这个视图会得到每个部门的总薪水，以及所有部门的总薪水（在 department 列为 NULL 的行中）：

SELECT * FROM department_salary_rollup;

结合多个表:

GROUP BY 也可以用于连接多个表的视图。例如，假设我们有一个 departments 表，包含部门的 ID 和名称：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'IT');

ALTER TABLE employees ADD COLUMN department_id INT;

UPDATE employees SET department_id = 1 WHERE department = 'Sales';
UPDATE employees SET department_id = 2 WHERE department = 'Marketing';
UPDATE employees SET department_id = 3 WHERE department = 'IT';

ALTER TABLE employees DROP COLUMN department; -- 删除旧的 department 列

我们可以创建一个视图，显示每个部门的名称和员工人数：

CREATE VIEW department_employee_count AS
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

4. 复杂查询场景下的最佳实践

在涉及更复杂的查询场景时，以下最佳实践可以帮助你更有效地使用视图中的 ORDER BY 和 GROUP BY：

• 尽量避免在视图定义中使用 ORDER BY，除非确实需要并且了解其局限性。 在查询视图时显式地使用 ORDER BY，以确保排序的正确性和可预测性。
• 在视图中使用 GROUP BY 时，确保包含所有未聚合的列在 GROUP BY 子句中。 使用 HAVING 子句来过滤 GROUP BY 后的结果。
• 对于复杂的聚合查询，考虑使用物化视图 (Materialized View)。 物化视图会将查询结果存储在磁盘上，可以提高查询性能。但是，物化视图需要定期刷新，以保持数据的最新性。MySQL 并没有直接支持物化视图，但可以通过一些技巧来模拟实现，例如使用触发器或定时任务。
• 优化视图的性能。 使用 EXPLAIN 语句来分析视图的查询计划，并根据需要添加索引或重写查询。
• 合理设计视图的结构。 将复杂的查询分解成多个简单的视图，可以提高可读性和可维护性。
• 考虑使用存储过程或函数来封装复杂的逻辑。 存储过程和函数可以接受参数，并返回结果，可以更灵活地处理复杂的数据处理需求。
• 对视图进行适当的权限控制，确保只有授权的用户才能访问敏感数据。
• 编写清晰的文档，描述视图的功能和使用方法。 这可以帮助其他开发人员更好地理解和使用你的视图。
• 测试视图的正确性。 编写单元测试来验证视图返回的结果是否符合预期。

5. 示例：多表连接和分组排序

让我们看一个更复杂的示例，结合多个表、GROUP BY 和 ORDER BY。 假设我们有三个表：employees (员工), departments (部门), 和 salaries (薪水历史)。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department_id INT
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);

CREATE TABLE salaries (
    salary_id INT PRIMARY KEY,
    employee_id INT,
    salary DECIMAL(10, 2),
    start_date DATE,
    end_date DATE
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'IT');

INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 1),
(4, 'David', 3),
(5, 'Eve', 2);

INSERT INTO salaries (salary_id, employee_id, salary, start_date, end_date) VALUES
(1, 1, 50000.00, '2022-01-01', '2022-12-31'),
(2, 1, 55000.00, '2023-01-01', '2023-12-31'),
(3, 2, 60000.00, '2022-01-01', '2022-12-31'),
(4, 2, 62000.00, '2023-01-01', '2023-12-31'),
(5, 3, 55000.00, '2022-01-01', '2022-12-31'),
(6, 3, 60000.00, '2023-01-01', '2023-12-31'),
(7, 4, 70000.00, '2022-01-01', '2022-12-31'),
(8, 4, 75000.00, '2023-01-01', '2023-12-31'),
(9, 5, 62000.00, '2022-01-01', '2022-12-31'),
(10, 5, 65000.00, '2023-01-01', '2023-12-31');

我们创建一个视图，显示每个部门 2023 年的平均薪水，并按平均薪水降序排序：

CREATE VIEW department_avg_salary_2023 AS
SELECT
    d.department_name,
    AVG(s.salary) AS avg_salary
FROM
    departments d
JOIN
    employees e ON d.department_id = e.department_id
JOIN
    salaries s ON e.employee_id = s.employee_id
WHERE
    YEAR(s.start_date) = 2023
GROUP BY
    d.department_name
ORDER BY
    avg_salary DESC;

现在，我们可以查询 department_avg_salary_2023 视图：

SELECT * FROM department_avg_salary_2023;

在这个例子中，GROUP BY 用于按部门分组，ORDER BY 用于按平均薪水排序。 请注意，这里的 ORDER BY 在视图定义中，只会在直接查询该视图时生效。

6. 总结：灵活运用，明确限制

总而言之，ORDER BY 在视图中的使用需要根据 MySQL 的版本和具体场景进行判断。 GROUP BY 在视图中则非常有用，可以创建汇总数据的视图。 理解这些规则和限制，可以帮助我们更有效地利用视图来简化复杂查询，提高数据访问的效率。 建议在查询视图时显式地使用 ORDER BY，而不是依赖视图定义中的 ORDER BY。