如何在 SQL 中使用视图

介绍

结构化查询语言(SQL)使用各种不同的数据结构,表是最常用的语言之一,但表具有某些限制,例如,您不能限制用户只访问表的一部分。

另一个例子是,假设您想将来自多个其他表的数据合并成一个新的结构,但您也不想删除原始表. 您可以只创建另一个表,但然后您将多余的数据存储在多个位置。

在 SQL 中,一个 view 是一个虚拟表,其内容是对一个或多个表的特定查询的结果,称为 _base tables。本指南提供了 SQL 视图的概述,以及它们为什么有用。

前提条件

要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。

运行 Ubuntu 20.04 的服务器,具有非root 用户的sudo管理权限和与 UFW 配置的防火墙,如我们在 Ubuntu 20.04 的初始服务器安装指南中所描述的。

<$>[注] 注:请注意,许多RDBMS使用自己的独特的SQL实现程序,虽然本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。

您还需要一个数据库,其中有一些表中包含示例数据,您可以使用这些数据来练习创建和使用视图. 我们鼓励您通过以下 连接到MySQL和设置示例数据库部分,了解如何连接到MySQL服务器,并创建本指南中示例中使用的测试数据库。

连接到MySQL并设置样本数据库

如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:

1[environment local]
2ssh sammy@your_server_ip

然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:

1mysql -u sammy -p

从提示程序中,创建一个名为views_db的数据库:

1CREATE DATABASE views_db;

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

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

若要選擇「view_db」資料庫,請執行下列「USE」聲明:

1USE views_db;

1[secondary_label Output]
2Database changed

在选择view_db后,在其内创建几个表。

要跟随本指南中使用的示例,想象一下,您正在运行家庭狗护理服务。您决定使用 SQL 数据库来存储有关您注册的每个狗的信息,以及您的服务雇用的每个狗护理专业人员。为了保持事物的组织,您决定需要两个表:一个代表员工和一个代表您的服务照顾的狗。

emp_id:您雇用的每个狗护理员的标识号,用int数据类型表示。这个列将作为表的 primary key,这意味着每个值将作为其相应行的唯一标识符。

运行以下创建表语句,创建一个名为员工的表,其中包含这两个列:

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

代表每个狗的另一张表将包含以下六个列:

" dog-id " :每只留在旅馆的狗的识别号码,用 " int " 数据类型表示。与 " 雇员 " 表中的 " emp_id " 一栏一样,该一栏将成为 " dogs " 一栏的主要关键。 *'dog_name':每条狗名,用"varchar"数据类型表示,最多有20个字符.
" 行走者 " :本栏储存被指派照顾每只相关狗的雇员的身份证号码。
"行走-距离": 每只狗被带出锻炼时的行走距离,用"小数"数据类型表示. 在这种情况下, " 十进制 " 声明指定了3的精度和2的分数,这意味着本栏中的任何值最多可有3个位数,其中2个位数位于小数点右侧。 *饭_每日':狗旅馆每天为每只狗提供一定数量的膳食. 本栏按每只狗的主人的要求,保留每一只狗每天应获得的餐数,并使用 " int " 即整数。 *cups_permeal':本栏列出每只狗每餐应该得到多少杯克布. 与 " 行走 -- -- 距离 " 一栏一样,这一栏以 " 十进制 " 表示。然而,这一个比例尺为3,精度为2,意思是本列中的数值最多可以有3个数字,其中2个数字位于小数点右侧. (英语)

要确保walker列只包含代表有效员工 ID 号码的值,您决定将一个 foreign key 限制应用于walker列,该列引用了员工表的emp_ID列。

创建一个名为狗的表,其中包含以下列,并使用以下命令:

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

现在,您可以用一些样本数据加载两个表,然后运行以下输入操作,将代表服务中的三名员工的三个数据行添加到员工表中:

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

然后执行以下操作,将七行数据插入到狗表中:

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

有了它,您已经准备好跟随本指南的其余部分,并开始学习如何在 SQL 中使用视图。

理解和创造观点

事实上,SQL的主要好处之一是它包含许多不同的选项和条款,允许您以高层次的细微性和具体性过滤数据。如果您有复杂的查询,您需要经常运行,需要不断写出它们可能会很快变得令人沮丧。

正如介绍中提到的,视图是 virtual tables. 这意味着虽然视图在功能上类似于表,但它是一种不同的结构类型,因为视图不包含任何数据。相反,它将从一个或多个 _base 表中提取数据,这些数据实际上包含在数据中。

为了更好地理解观点,考虑下面的示例场景。想象你的狗护理业务做得很好,你需要为所有员工打印每日时间表。日程表应该列出每只被服务所照顾的狗,被分配照顾他们的员工,每个狗每天要走的距离,每只狗每天应喂食的食物数量,每只狗每餐应该得到多少鱼。

使用您的 SQL 技能,您可以创建一个查询,使用上一个步骤中的样本数据来获取所有这些信息,以便为日程安排。

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

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

假设您必须定期运行此查询,这可能会使您不得不重复写出查询,特别是当涉及到更长和更复杂的查询陈述时,如果您不得不对查询进行轻微调整或扩展,则在解决错误时可能会令人沮丧,因为语法错误的可能性如此之多。

在这种情况下,视图可能有用,因为视图基本上是来自查询结果的表。

若要创建视图,大多数 RDBMS 都使用以下语法:

1[label Example CREATE VIEW syntax]
2CREATE VIEW view_name
3AS
4SELECT statement;

在 CREATE VIEW 语句之后,您将为视图定义一个名称,您将用来后来参考它. 之后的名称,您将输入 AS 关键字,然后是SELECT 查询,您想要保存其输出。

尝试使用前面的示例查询创建视图,此CREATE VIEW操作命名视图为walking_schedule:

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

然后,您可以像任何其他表一样使用并与此视图交互。例如,您可以运行以下查询以返回视图中的所有数据:

1SELECT * FROM walking_schedule;

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

虽然此视图源自其他两个表,但除非此视图中已经存在,否则您将无法查询来自这些表中的任何数据的视图。

1SELECT walker FROM walking_schedule;

1[secondary_label Output]
2ERROR 1054 (42S22): Unknown column 'walker' in 'field list'

此输出返回错误消息,因为行人列是狗表的一部分,但未包含在您创建的视图中。

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

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

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

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

例如,假设您雇用了一个办公室管理员来帮助您管理时间表. 您希望他们可以访问时间表信息,而不是数据库中的任何其他数据. 要做到这一点,您可以在数据库中为他们创建一个新的用户帐户:

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

然后,您可以允许该新用户阅读访问walking_schedule视图,并且只允许该视图使用GRANT声明,如下:

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

随后,任何有访问 MySQL 用户帐户的用户只能在walking_schedule视图中运行SELECT查询。

改变和删除观点

如果您添加或更改视图来源表中的任何数据,则相应的数据将在视图中自动添加或更新。

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

然后从walking_schedule视图中重新检索所有数据:

1SELECT * FROM walking_schedule;

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

这次,查询的结果集中还有另一个行,反映了您添加到狗表中的数据。

然而,视图仍然从相同的基表中提取相同的数据,因此此操作并没有改变视图本身。

许多 RDBMS 允许您使用创建或替换视图语法创建视图后更新视图的结构:

1[label Example CREATE OR REPLACE VIEW syntax]
2CREATE OR REPLACE VIEW view_name
3AS
4new SELECT statement

使用此语法,如果已经存在一个名为view_name的视图,数据库系统将更新它,以代表由新 SELECT 语句返回的数据。

假设您想要更改步行_日程表视图,以便不列出每个狗每餐吃多少杯食物,而是列出每个狗在一天中吃了多少总食物。

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

现在,当您查询此视图时,结果集将反映视图的新数据:

1SELECT * FROM walking_schedule;

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

与在 SQL 中创建的大多数其他结构一样,您可以使用DROP语法删除视图:

1[label Example DROP VIEW syntax]
2DROP VIEW view_name;

例如,如果您曾经想放弃walking_schedule视图,则可以使用以下命令:

1DROP VIEW walking_schedule;

这将从数据库中删除walking_schedule视图,但除非您从基表中删除此视图的数据,否则不会删除任何数据。

结论

通过阅读本指南,您了解了什么是 SQL 视图,如何创建、查询、更改和从数据库中删除它们。

虽然我们示例中的命令应该在大多数关系数据库上工作,但请注意,每个 SQL 数据库都使用其独特的语言实现。

如果您想了解有关使用 SQL 的更多信息,我们鼓励您查看本系列中的其他教程在如何使用 SQL。