如何使用SQL系列 之 如何在SQL中使用视图

引言

结构化查询语言(SQL)采用了各种不同的数据结构，其中表是最常用的一种。然而，表有一定的局限性。例如，你不能限制用户只能访问表的一部分。用户必须被授予访问整个表,不只是几列。

再举一个例子，假设你想把多个表中的数据合并到一个新结构中，但又不想删除原来的表。你可以创建另一张表，但这样会在多个地方存储冗余数据。这可能会造成很多不便:如果你的一些数据发生了变化，你必须在多个地方更新它。在这种情况下，视图可以派上用场。

在SQL中，视图是一个虚拟表，它的内容是对一个或多个表(称为基表)的特定查询的结果。本指南概述了什么是SQL视图以及它们为什么有用。它还强调了如何使用标准SQL语法创建、查询、修改和销毁视图。

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。

连接到MySQL并设置一个示例数据库

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器:

ssh sammy@your_server_ip
1

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称:

mysql -u sammy -p
1

创建一个名为views_db的数据库:

CREATE DATABASE views_db;
1

如果数据库成功创建,您将收到这样的输出:

OutputQuery OK, 1 row affected (0.01 sec)
1

要选择views_db数据库，运行以下USE语句:

USE views_db;
1

OutputDatabase changed
1

选择views_db后，在其中创建一些表。

为了遵循本指南中使用的示例，假设您经营一家在家照顾宠物的服务。你决定使用SQL数据库存储注册服务的每只宠物的信息，以及服务雇用的每一位宠物护理专业人员的信息。为了让事情井然有序，你决定需要两张桌子:一张代表员工，另一张代表你的服务所照顾的宠物。代表雇员的表将包含以下列。

• emp_id：你雇佣的每个看护者的识别号，用int数据类型表示。这一列将作为表的主键，这意味着每个值将作为其各自行的唯一标识符。因为主键中的每个值都必须是唯一的，所以这一列也有unique约束。
• emp_name：每个员工的名字，使用varchar数据类型表示，最多20个字符。

运行下面的CREATE TABLE语句来创建一个名为employees的表，它包含这两列：

CREATE TABLE employees (
emp_id int UNIQUE,
emp_name varchar(20),
PRIMARY KEY (emp_id)
);
1
2
3
4
5

另一个表示每个小宠物的表将包含以下6列：

• pet_id：入住酒店的每只宠物的识别号，用int数据类型表示。与employees表中的emp_id列类似，这一列将作为pets表的主键。
• pet_name：每只宠物的名字，使用varchar数据类型表示，不超过20个字符。
• walker：这一列存储了负责照顾每条宠物的员工的员工ID号。
• walk_distance：每只宠物出去锻炼时应该走的距离，使用decimal数据类型表示。在本例中，decimal声明指定精度为3，刻度为2，这意味着这一列中的任何值最多可以有三位数字，其中两位在小数点的右侧。
• meals_perday：宠物酒店每天为每只宠物提供一定数量的食物。这一列保存了每只宠物每天应主人要求获得的餐食数量，它使用的是整数int。
• cups_permeal：这一栏列出了每只宠物每顿饭应该吃多少杯宠物粮。与walk_distance列一样，这一列也用decimal表示。然而，这一列的刻度是3，精度是2，这意味着这一列的值最多可以有3位，其中有两位在小数点的右边。

为了确保walker列只保存代表有效员工ID数字的值，你决定对引用employees表的emp_ID列的walker列应用一个外键约束。外键约束是一种表达两个表之间关系的方式，它要求应用外键的列中的值必须已经存在于它引用的列中。在下面的例子中，FOREIGN KEY约束要求添加到pets表中的walker列中的任何值都必须已经存在于employees表的emp_ID列中。

使用下面的命令创建一个名为pets的表，其中包含这些列：

CREATE TABLE pets (
pet_id int UNIQUE,
pet_name varchar(20),
walker int,
walk_distance decimal(3,2),
meals_perday int,
cups_permeal decimal(3,2),
PRIMARY KEY (pet_id),
FOREIGN KEY (walker)
REFERENCES employees(emp_ID)
);
1
2
3
4
5
6
7
8
9
10
11

现在可以用一些示例数据加载这两个表了。运行以下INSERT INTO操作，将代表你的服务的三个雇员的三行数据添加到employees表:

INSERT INTO employees
VALUES
(1, 'Peter'),
(2, 'Paul'),
(3, 'Mary');
1
2
3
4
5

然后执行以下操作，将7行数据插入到pets表中:

INSERT INTO pets
VALUES
(1, 'Dottie', 1, 5, 3, 1),
(2, 'Bronx', 3, 6.5, 3, 1.25),
(3, 'Harlem', 3, 1.25, 2, 0.25),
(4, 'Link', 2, 2.75, 2, 0.75),
(5, 'Otto', 1, 4.5, 3, 2),
(6, 'Juno', 1, 4.5, 3, 2),
(7, 'Zephyr', 3, 3, 2, 1.5);
1
2
3
4
5
6
7
8
9

有了这些，你就可以开始学习如何在SQL中使用视图了。

理解和创建视图

根据场景的不同，SQL查询可能会变得异常复杂。实际上，SQL的主要好处之一是它包含许多不同的选项和子句，允许您以较高的粒度和特指度过滤数据。如果你有复杂的查询,你需要经常运行,必须不断地写出来很快就变得沮丧。解决这些问题的一种方法是使用视图。

正如引言中提到的，视图是虚拟表。这意味着,尽管一个视图功能类似于一个表,它是一种不同类型的结构自自己的视图不持有任何数据。相反,它把数据从一个或多个_base tables_实际上保存数据。DBMS存储的关于视图的唯一信息是视图的结构。视图有时被称为保存的查询，因为这就是它们的本质:为了方便访问，查询被保存在特定的名称下。

为了更好地理解视图，请考虑以下示例场景。想象一下,你的宠物护理业务做得很好,你需要打印每天的日程安排你所有的员工。时间表应该列出服务机构照顾的每只宠物，分配给照顾它们的员工，每只宠物每天应该走的距离，每只宠物每天应该喂多少顿饭，每只宠物每餐应该吃多少宠物粮。

使用SQL技能，使用上一步中的样例数据创建一个查询，以检索计划的所有这些信息。注意，这个查询包含了1111语法，以便从employees和pets表中获取数据:

SELECT emp_name, pet_name, walk_distance, meals_perday, cups_permeal
FROM employees JOIN pets ON emp_ID = walker;
1
2

Output+----------+----------+---------------+--------------+--------------+
| emp_name | pet_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

假设你需要定期运行这个查询。重复编写查询可能会变得冗长乏味，特别是当涉及到更长的和更复杂的查询语句时，如果您必须对查询进行轻微的调整或扩展，在排除错误时，由于有这么多语法错误的可能性，这可能是令人沮丧的。

视图在这种情况下可能很有用，因为视图本质上是根据查询结果生成的表。

大多数RDBMS使用以下语法创建视图:

CREATE VIEW view_name
AS
SELECT statement;
1
2
3

在CREATE VIEW语句之后，你定义一个视图的名称，稍后用来引用它。在名称之后，输入AS关键字，然后输入SELECT查询，你想保存它的输出。你用来创建视图的查询可以是任何有效的SELECT语句。只要使用正确的语法，所包含的语句可以查询一个或多个基表。

尝试使用前面的示例查询创建一个视图。这个CREATE VIEW操作将视图命名为walking_schedule:

CREATE VIEW walking_schedule
AS
SELECT emp_name, pet_name, walk_distance, meals_perday, cups_permeal 
FROM employees JOIN pets
ON emp_ID = walker;
1
2
3
4
5

接下来，就可以像使用其他表一样使用这个视图并与之交互。例如，你可以执行以下查询来返回视图中保存的所有数据:

SELECT * FROM walking_schedule;
1

Output+----------+----------+---------------+--------------+--------------+
| emp_name | pet_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

虽然这个视图是从另外两个表派生出来的，但你无法从这些表中查询任何数据，除非这些数据已经存在于视图中。下面的查询试图从walking_schedule视图中检索walker列，但此查询将失败，因为视图不包含任何具有该名称的列:

SELECT walker FROM walking_schedule;
1

OutputERROR 1054 (42S22): Unknown column 'walker' in 'field list'
1

这个输出返回一个错误消息，因为walker列是pets表的一部分，但没有包含在你创建的视图中。

您还可以运行查询,包括聚合操作数据的函数在一个视图中。下面的例子使用MAX聚合函数和GROUP BY来找出每个员工在给定的一天中需要走的最长距离:

SELECT emp_name, MAX(walk_distance) AS longest_walks
FROM walking_schedule GROUP BY emp_name;
1
2

Output+----------+---------------+
| emp_name | longest_walks |
+----------+---------------+
| Peter    |          5.00 |
| Paul     |          2.75 |
| Mary     |          6.50 |
+----------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

如前所述，视图有用的另一个原因是，可以使用它们限制数据库用户只能访问一个视图，而不能访问整个表或数据库。

例如，假设你雇佣了一位办公室经理来帮助你管理日程。您希望他们能够访问日程表信息，但不能访问数据库中的任何其他数据。要做到这一点，你可以在数据库中为他们创建一个新的用户帐户:

CREATE USER 'office_mgr'@'localhost' IDENTIFIED BY 'password';
1

然后，您可以授予这个新用户对walking_schedule视图的读取权限，并且只授予该视图，使用GRANT语句，如下所示:

GRANT SELECT ON views_db.walking_schedule to 'office_mgr'@'localhost';
1

接下来，任何访问office_mgr MySQL用户帐户的人都只能在walking_schedule视图上执行SELECT查询。

修改和删除视图

如果您在视图对应表中添加或更改任何数据，相关数据将自动添加或更新到视图中。运行以下INSERT INTO命令向pets表中添加另一行:

INSERT INTO pets VALUES (8, 'Charlie', 2, 3.5, 3, 1);
1

然后再次从walking_schedule视图中获取所有数据:

SELECT * FROM walking_schedule;
1

Output+----------+----------+---------------+--------------+--------------+
| emp_name | pet_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Paul     | Charlie  |          3.50 |            3 |         1.00 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

这一次，查询结果集中有另一行数据反映了你添加到pets表中的数据。

然而，视图仍然从相同的基表中拉取相同的数据，所以这个操作没有改变视图本身。

许多RDBMS允许你在创建视图后使用CREATE OR REPLACE VIEW语法来更新视图的结构:

CREATE OR REPLACE VIEW view_name
AS
new SELECT statement
1
2
3

使用这种语法，如果名称为==view_name==的视图已经存在，数据库系统将更新它，以表示==new SELECT statement==返回的数据。如果该名称的视图还不存在，DBMS将创建一个新视图。

假设你想修改walking_schedule视图，让它不列出每只宠物每顿吃多少杯食物，而是列出每只宠物在一天中总共吃了多少食物。可以使用下面的命令修改视图:

CREATE OR REPLACE VIEW walking_schedule
AS
SELECT emp_name, pet_name, walk_distance, meals_perday, (cups_permeal * meals_perday) AS total_kibble 
FROM employees JOIN pets ON emp_ID = walker;
1
2
3
4

现在，当你查询这个视图时，结果集将反映视图的新数据:

SELECT * FROM walking_schedule;
1

Output+----------+----------+---------------+--------------+--------------+
| emp_name | pet_name | walk_distance | meals_perday | total_kibble |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         3.00 |
| Peter    | Otto     |          4.50 |            3 |         6.00 |
| Peter    | Juno     |          4.50 |            3 |         6.00 |
| Paul     | Link     |          2.75 |            2 |         1.50 |
| Paul     | Charlie  |          3.50 |            3 |         3.00 |
| Mary     | Bronx    |          6.50 |            3 |         3.75 |
| Mary     | Harlem   |          1.25 |            2 |         0.50 |
| Mary     | Zephyr   |          3.00 |            2 |         3.00 |
+----------+----------+---------------+--------------+--------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

Like most other structures one can create in SQL, you can delete views using the DROP syntax:
像大多数其他可以在SQL中创建的结构一样，你可以使用DROP语法删除视图:

DROP VIEW view_name;
1

例如，如果你想删除walking_schedule视图，可以使用以下命令:

DROP VIEW walking_schedule;
1

这从数据库删除“walking_schedule”的观点,但没有一个视图的数据将被删除,除非你从基表中删除它。

总结

通过阅读本指南，您了解了什么是SQL视图，如何从数据库中创建、查询、更改和删除它们。您还了解了为什么视图可以很有用，并创建了一个只能从本指南中创建的示例视图读取数据的MySQL用户。
虽然我们示例中的命令可以在大多数关系型数据库上运行，但请注意，每个SQL数据库都使用自己独特的语言实现。关于每个命令及其所有选项的更完整描述，读者可以查阅相应DBMS的官方文档。