MySQL 视图与虚拟表

简介

在这个实验中，你将学习如何使用 MySQL 视图（view），视图是基于 SQL 语句结果集的虚拟表。本实验涵盖了从 SELECT 查询创建视图、使用 ALTER VIEW 更新视图定义、通过视图查询数据，以及最后删除视图以进行清理。

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/AdvancedFeaturesGroup(["Advanced Features"]) mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_database("Database Deletion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_table("Table Removal") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/AdvancedFeaturesGroup -.-> mysql/views("View Management") subgraph Lab Skills mysql/use_database -.-> lab-550920{{"MySQL 视图与虚拟表"}} mysql/create_database -.-> lab-550920{{"MySQL 视图与虚拟表"}} mysql/drop_database -.-> lab-550920{{"MySQL 视图与虚拟表"}} mysql/create_table -.-> lab-550920{{"MySQL 视图与虚拟表"}} mysql/drop_table -.-> lab-550920{{"MySQL 视图与虚拟表"}} mysql/select -.-> lab-550920{{"MySQL 视图与虚拟表"}} mysql/insert -.-> lab-550920{{"MySQL 视图与虚拟表"}} mysql/views -.-> lab-550920{{"MySQL 视图与虚拟表"}} end

从 SELECT 查询创建视图

在这一步中，你将学习如何在 MySQL 中从 SELECT 查询创建视图。视图是基于 SQL 语句结果集的虚拟表。视图对于简化复杂查询、隐藏数据复杂性以及为数据访问提供一定程度的抽象非常有用。

首先，让我们连接到 MySQL 服务器。在 LabEx 虚拟机中打开一个终端。你应该已经位于 ~/project 目录下。

mysql -u root -p

当提示输入密码时，输入 root 密码。如果你还没有设置 root 密码，直接按回车键即可。

现在，让我们创建一个名为 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)
);

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');

现在，让我们创建一个名为 sales_employees 的视图，该视图仅显示来自“销售”部门的员工。

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

此语句创建了一个名为 sales_employees 的视图。该视图从 employees 表中选择 id、first_name、last_name 和 salary 列，但仅选择 department 为 'Sales' 的行。

要验证视图是否已创建，你可以描述它：

DESCRIBE sales_employees;

此命令将显示 sales_employees 视图的结构，类似于描述表的结构。

你还可以像查询常规表一样查询该视图：

SELECT * FROM sales_employees;

这将根据 employees 表中的数据显示销售部门的所有员工。

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

你现在已成功从 SELECT 查询创建了一个视图。

使用 ALTER VIEW 更新视图定义

在这一步中，你将学习如何使用 MySQL 中的 ALTER VIEW 语句更新现有视图的定义。当你需要修改所选列、过滤条件或视图底层查询的任何其他方面时，这非常有用。

从上一步继续，你应该仍然连接到 MySQL 服务器并使用 company 数据库。如果没有，请使用以下命令重新连接：

mysql -u root -p

如果提示输入密码，请输入密码，然后执行：

USE company;

回顾一下，我们创建了一个名为 sales_employees 的视图，该视图显示来自“销售”部门的员工。假设我们想将 department 列添加到这个视图中。我们可以使用 ALTER VIEW 语句来修改视图定义。

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

此语句修改 sales_employees 视图，使其在结果集中包含 department 列。ALTER VIEW 语句与 CREATE VIEW 语句非常相似；你只需将 CREATE 替换为 ALTER。

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

DESCRIBE sales_employees;

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

你还可以查询更新后的视图：

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)

让我们再次修改视图，使其包含销售部门中工资超过 55000 的员工。

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

现在，查询更新后的视图：

SELECT * FROM sales_employees;

现在，这将显示销售部门中工资超过 55000 的员工。

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

你现在已成功使用 ALTER VIEW 语句更新了视图的定义。

使用视图查询数据

在这一步中，你将学习如何使用在前几步中创建和修改的视图来查询数据。视图可以像常规表一样进行查询，让你根据视图的定义检索特定的数据。

从上一步继续，你应该仍然连接到 MySQL 服务器并使用 company 数据库。如果没有，请使用以下命令重新连接：

mysql -u root -p

如果提示输入密码，请输入密码，然后执行：

USE company;

回顾一下，我们有一个名为 sales_employees 的视图，目前该视图显示销售部门中工资超过 55000 的员工。

要从 sales_employees 视图中检索所有数据，你可以使用一个简单的 SELECT 语句：

SELECT * FROM sales_employees;

这将显示满足视图定义的所有列和行。根据上一步的操作，你应该只会看到销售部门中工资超过 55000 的员工。

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

你还可以使用 WHERE 子句进一步过滤从视图中检索的数据。例如，要从 sales_employees 视图中查找 id = 1 的员工：

SELECT * FROM sales_employees WHERE id = 1;

这将仅返回 id 为 1 的行，但前提是该员工也要满足视图的定义（销售部门且工资超过 55000）。

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

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

SELECT first_name, last_name FROM sales_employees;

这将仅显示 sales_employees 视图中员工的 first_name 和 last_name 列。

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

你也可以对视图使用聚合函数。例如，要查找 sales_employees 视图中员工的平均工资：

SELECT AVG(salary) FROM sales_employees;

这将计算并显示平均工资。

+-------------+
| AVG(salary) |
+-------------+
|  60000.0000 |
+-------------+
1 row in set (0.00 sec)

通过查询视图，你实际上是在查询底层的 employees 表，但带有视图中定义的约束条件。这简化了复杂的查询，并提供了一定程度的抽象。

删除视图以清理资源

在这一步中，你将学习如何删除在前几步中创建的视图。删除视图会将其从数据库中移除。当不再需要某些资源时，清理它们是很重要的。

从上一步继续，你应该仍然连接到 MySQL 服务器并使用 company 数据库。如果没有，请使用以下命令重新连接：

mysql -u root -p

如果提示输入密码，请输入密码，然后执行：

USE company;

我们有一个名为 sales_employees 的视图，它是我们创建并修改过的。要删除这个视图，请使用 DROP VIEW 语句：

DROP VIEW sales_employees;

此语句会将 sales_employees 视图从 company 数据库中移除。

要验证视图是否已被删除，你可以尝试描述它：

DESCRIBE sales_employees;

这应该会返回一条错误消息，表明该表（或视图）不存在。

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

或者，你可以尝试从该视图中进行查询：

SELECT * FROM sales_employees;

这也会返回一条错误消息，表明该表（或视图）不存在。

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

最后，让我们删除 employees 表和 company 数据库以彻底清理。

DROP TABLE employees;
DROP DATABASE company;

你现在已成功删除视图并清理了数据库。

总结

在这个实验中，你学习了如何通过 SELECT 查询在 MySQL 中创建视图。这包括连接到 MySQL 服务器、创建一个数据库和一个名为 employees 的表，并插入示例数据。然后，你创建了一个名为 sales_employees 的视图，该视图对 employees 表进行过滤，只显示销售部门的员工。

本实验展示了如何使用 CREATE VIEW 语句定义视图，指定要包含的列和过滤条件。你还学习了如何使用 DESCRIBE 验证视图是否创建成功，以及如何像查询常规表一样使用 SELECT 查询视图。