MySQL 视图与虚拟表

介绍

在本实验中，你将学习如何使用 MySQL 视图。视图是一个基于 SQL 语句结果集的虚拟表。视图有助于简化复杂查询、隐藏数据复杂性以及为数据访问提供抽象层。你将学习如何创建、更新、查询和删除 MySQL 视图。

你将首先连接到 MySQL 服务器，创建一个名为 company 的数据库，并用一个 employees 表填充它。然后，你将创建一个名为 sales_employees 的视图，该视图会过滤 employees 表，只显示来自“Sales”部门的员工。你将学习如何验证视图的结构并像查询普通表一样查询它。

连接 MySQL 并创建数据库/表

在此步骤中，你将连接到 MySQL 服务器并为实验设置必要的数据库和表。这包括创建一个名为 company 的数据库，并在其中创建一个 employees 表，并填充一些示例数据。

首先，在 LabEx VM 中打开一个终端。你应该已经在 ~/project 目录中。

以 root 用户连接到 MySQL 服务器。由于你拥有 sudo 权限，并且 MySQL 中的 root 用户配置为使用 auth_socket 插件，因此你可以使用 sudo 无密码连接。

sudo mysql -u root

你现在已进入 MySQL shell。提示符将变为 mysql>。

在 MySQL shell 中，创建一个名为 company 的数据库：

CREATE DATABASE company;

切换到 company 数据库，以便后续命令在此数据库中执行：

USE company;

现在，创建一个名为 employees 的表来存储员工信息：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

向 employees 表插入一些示例数据：

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

你可以通过选择所有行来验证 employees 表中的数据：

SELECT * FROM employees;

输出应显示已插入的员工数据：

+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department  |
+----+------------+-----------+----------+-------------+
|  1 | John       | Doe       | 60000.00 | Sales       |
|  2 | Jane       | Smith     | 75000.00 | Marketing   |
|  3 | Robert     | Jones     | 50000.00 | Sales       |
|  4 | Emily      | Brown     | 80000.00 | Engineering |
|  5 | Michael    | Davis     | 65000.00 | Marketing   |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)

你已成功连接到 MySQL，创建了一个数据库，并用数据填充了一个表。

创建视图

在此步骤中，你将基于 employees 表创建一个视图。视图是一个存储的 SQL 查询，它像一个虚拟表一样工作。它本身不存储数据，而是呈现一个或多个底层表中的数据。

你应该仍然在 MySQL shell 中，并连接到 company 数据库。如果不是，请使用 sudo mysql -u root 重新连接，然后执行 USE company;。

我们将创建一个名为 sales_employees 的视图，该视图将只显示在“Sales”部门工作的员工。此视图将从 employees 表中选择特定列。

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

此语句定义了一个名为 sales_employees 的视图。视图的定义是 AS 关键字后面的 SELECT 查询。此查询从 employees 表中选择 id、first_name、last_name 和 salary 列，但仅限于 department 列等于 'Sales' 的行。

要确认视图已创建，你可以列出当前数据库中的表和视图：

SHOW TABLES;

你应该会看到 employees 和 sales_employees 都被列出。

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| sales_employees   |
+-------------------+
2 rows in set (0.00 sec)

你也可以像描述表一样描述视图的结构：

DESCRIBE sales_employees;

这将显示视图中包含的列：

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

你已成功创建了一个名为 sales_employees 的视图。

使用视图查询数据

在此步骤中，你将学习如何使用你创建的 sales_employees 视图来查询数据。视图可以像普通表一样进行查询，从而提供一种简化的方式来访问数据的特定子集。

你应该仍然在 MySQL shell 中，并连接到 company 数据库。如果不是，请使用 sudo mysql -u root 重新连接，然后执行 USE company;。

要从 sales_employees 视图中检索所有数据，请使用标准的 SELECT 语句：

SELECT * FROM sales_employees;

此查询执行视图中定义的底层 SELECT 语句并返回结果。你应该只看到来自“Sales”部门的员工：

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

你还可以对从视图检索的数据应用额外的过滤或排序。例如，要查找薪资大于 55000 的销售员工：

SELECT * FROM sales_employees WHERE salary > 55000;

这将仅返回视图中薪资大于 55000 的行：

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
+------+------------+-----------+----------+
1 row in set (0.00 sec)

你也可以从视图中选择特定列：

SELECT first_name, last_name FROM sales_employees;

这将仅显示销售员工的名字和姓氏：

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Robert     | Jones     |
+------------+-----------+
2 rows in set (0.00 sec)

查询视图本质上与查询表相同，但视图提供了来自底层数据源的预定义过滤器和列选择。

使用 ALTER VIEW 更新视图定义

在此步骤中，你将学习如何使用 ALTER VIEW 语句修改现有视图的定义。当你需要更改视图中包含的列或过滤条件时，这将非常有用。

你应该仍然在 MySQL shell 中，并连接到 company 数据库。如果不是，请使用 sudo mysql -u root 重新连接，然后执行 USE company;。

当前，sales_employees 视图包含 id、first_name、last_name 和 salary。让我们修改视图以也包含 department 列。

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

此语句使用 ALTER VIEW，后跟视图名称和定义视图的新 SELECT 查询。新查询现在包含 department 列。

要验证视图是否已更新，请再次描述它：

DESCRIBE sales_employees;

你应该现在在输出中看到 department 列：

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
| department | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

查询更新后的视图以查看新列：

SELECT * FROM sales_employees;

输出现在将包括 department 列：

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

你已成功更新了 sales_employees 视图的定义。

删除视图并清理

在最后一步中，你将学习如何删除（移除）视图，并清理在此实验中创建的数据库和表。

你应该仍然在 MySQL shell 中，并连接到 company 数据库。如果不是，请使用 sudo mysql -u root 重新连接，然后执行 USE company;。

要移除 sales_employees 视图，请使用 DROP VIEW 语句：

DROP VIEW sales_employees;

此命令将永久地从 company 数据库中移除 sales_employees 视图。

要确认视图已被删除，你可以尝试描述它：

DESCRIBE sales_employees;

这应该会产生一个错误消息，表明视图不存在：

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

现在，让我们清理 employees 表和 company 数据库。

删除 employees 表：

DROP TABLE employees;

删除 company 数据库：

DROP DATABASE company;

你可以通过输入以下命令退出 MySQL shell：

exit

你已成功删除了视图、表和数据库，清理了在此实验中使用的资源。

总结

在此实验中，你学习了如何使用 MySQL 视图。你首先连接到 MySQL 服务器并设置了数据库和表。然后，你创建了一个名为 sales_employees 的视图，以提供 employees 表的过滤视图。

你通过 SELECT 语句练习了查询视图，展示了视图如何简化数据访问。你还学习了如何使用 ALTER VIEW 语句修改现有视图的定义以包含其他列。最后，你学习了如何使用 DROP VIEW 语句移除视图，并清理了数据库和表。

你现在已掌握了使用 MySQL 视图的基本概念和操作，视图是管理和访问数据库中数据的宝贵工具。