写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，强哥的畅销书「构建企业级推荐系统：算法、工程实现与案例分析」已经出版，需要提升可以私信我呀。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

2020 Learning SQL-中译

第十四章视图

设计良好的应用程序通常会在保持实现细节私有性的同时公开一个公共接口，从而在不影响终端用户的情况下支持将来的设计变动。在设计数据库时，通过保持表的私有性并允许用户仅通过一组视图访问数据，你可以获得类似的结果。本章致力于定义什么是视图、如何创建它们、何时使用它们以及如何使用它们。

什么是视图

视图其实就是一种数据查询机制。与表不同，视图不涉及数据存储，所以不必担心视图会占用磁盘空间。可以通过命名 select 语句来创建视图，将其保存以供其他人使用。其他用户可以使用该视图访问数据，就像他们直接查询表一样（实际上，他们甚至可能不知道自己正在使用视图）。

举一个简单的例子，假设你希望部分隐藏 customer 表中的电子邮件地址。例如，市场营销部门可能需要访问电子邮件地址才能发布促销广告，但公司的隐私政策有规定必须保证这些数据的安全。因此不允许直接访问 customer 表，而是定义一个名为 customer_vw 的视图，并授权给所有非营销人员使用以访问客户数据。视图定义如下：

CREATE VIEW customer_vw 

(customer_id, 

first_name, 

last_name, 

email  

)

AS

SELECT  

customer_id,

first_name, 

last_name, 

concat(substr(email,1,2), '*****', substr(email, -4)) email

FROM customer;

语句的第一部分列出了视图的列名，这些列名可能与基础表的列名不同。语句的第二部分是 select 语句，它必须为视图中的每一列提供一个表达式。email 列的生成方法是：获取电子邮件地址的前两个字符，与“*****”连接，然后与电子邮件地址的最后四个字符连接。

执行 create view 语句时，数据库服务器只简单地存储视图定义以供将来使用。若不执行查询，也就不会检索或存储任何数据。创建视图后，用户可以像查询表一样使用它进行查询，如下所示：

mysql> SELECT first_name, last_name, email

 -> FROM customer_vw;

+-------------+--------------+-------------+

| first_name | last_name | email |

+-------------+--------------+-------------+

| MARY | SMITH | MA*****.org |

| PATRICIA | JOHNSON | PA*****.org |

| LINDA | WILLIAMS | LI*****.org |

| BARBARA | JONES | BA*****.org |

| ELIZABETH | BROWN | EL*****.org |

...

| ENRIQUE | FORSYTHE | EN*****.org |

| FREDDIE | DUGGAN | FR*****.org |

| WADE | DELVALLE | WA*****.org |

| AUSTIN | CINTRON | AU*****.org |

+-------------+--------------+-------------+

599 rows in set (0.00 sec)

尽管 customer_vw 视图定义包含 customer 表的四列，但前面的查询只检索其中三列。正如你将在本章后面看到的，如果视图中的某些列被附加到函数或子查询，那么这会是一个重要的区别。

从用户的角度来看，视图看起来就像一个表。要想知道视图中有哪些列是可用的，可以使用 MySQL（或 Oracle）的 describe 命令查看：

mysql> describe customer_vw;

+-------------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------------------+------+-----+---------+-------+

| customer_id | smallint(5) unsigned | NO | | 0 | |

| first_name | varchar(45) | NO | | NULL | |

| last_name | varchar(45) | NO | | NULL | |

| email | varchar(11) | YES | | NULL | |

+-------------+----------------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

在通过视图进行查询时，可以自由使用 select 语句中的任何子句，包括 group by、having 和 order by。举个例子：

mysql> SELECT first_name, count(*), min(last_name), max(last_name) 

-> FROM customer_vw 

-> WHERE first_name LIKE 'J%' 

-> GROUP BY first_name 

-> HAVING count(*) > 1 

-> ORDER BY 1;

+------------+----------+----------------+----------------+

| first_name | count(*) | min(last_name) | max(last_name) |

+------------+----------+----------------+----------------+

| JAMIE | 2 | RICE | WAUGH |

| JESSIE | 2 | BANKS | MILAM |

+------------+----------+----------------+----------------+

2 rows in set (0.00 sec)

此外，你还可以在查询中连接视图到其他表（或者甚至视图），如下所示：

mysql> SELECT cv.first_name, cv.last_name, p.amount 

-> FROM customer_vw cv 

-> INNER JOIN payment p 

-> ON cv.customer_id = p.customer_id 

-> WHERE p.amount >= 11;

+------------+-----------+--------+

| first_name | last_name | amount |

+------------+-----------+--------+

| KAREN | JACKSON | 11.99 |

| VICTORIA | GIBSON | 11.99 |

| VANESSA | SIMS | 11.99 |

| ALMA | AUSTIN | 11.99 |

| ROSEMARY | SCHMIDT | 11.99 |

| TANYA | GILBERT | 11.99 |

| RICHARD | MCCRARY | 11.99 |

| NICHOLAS | BARFIELD | 11.99 |

| KENT | ARSENAULT | 11.99 |

| TERRANCE | ROUSH | 11.99 |

+------------+-----------+--------+

10 rows in set (0.01 sec)

此查询将 customer_vw 视图与 payment 表连接，以查找租赁电影花费了 11 美元或更多金额的客户。

为何要用视图

在上一节中，我演示了一个简单的视图，它的目的是掩盖 customer.email 列。虽然视图通常被用于此种目的，但还有更多理由使用视图，如下小节所述。

数据安全

如果你创建一个表并允许用户查询，那么他们将能够访问表中的每一列和每一行数据。但正如我前面提到的，你的表中有些列可能包含敏感信息，比如身份证号或信用卡号码，把包括这些敏感数据在内的表数据公开给用户访问绝对不是一个好主意，而且还可能违反公司的隐私政策，甚至触犯州或联邦法律。

对于这些情况，最好的方法是保持表的私有性（即不向任何用户授予 select 权限），然后创建一个或多个视图省略或者模糊（比如对 customer_vw.email 列采取'*****'替代部分内容）这些敏感信息。你还可以通过向视图定义中添加 where 子句来限制一组用户只能访问哪些行。例如，下面的视图定义将非活跃客户排除在外：

CREATE VIEW active_customer_vw 

(customer_id, 

first_name, 

last_name, email

)

AS

SELECT 

customer_id, 

first_name, 

last_name, 

concat(substr(email,1,2),

'*****',

substr(email, -4))

email

FROM customer

WHERE active = 1;

如果将此视图提供给市场营销部门，他们将能够避免向非活跃客户发送信息，因为视图的 where 子句中的条件将始终包含在查询中。

注意：Oracle Database 用户还可以用另一种方法保证表中行和列的安全：虚拟私有数据库（Virtual Private Database，VPD）。VPD 允许你为表附加策略，然后服务器将根据需要修改用户的查询以执行此策略。例如，如果你制定了策略指定销售和市场营销部门的成员只能看到活跃客户，则条件 active=1 将添加到他们对 customer 表的所有查询中。

数据聚合

报表程序通常需要聚合数据，而视图就是一种实现该功能的很好的方法，可以使数据看起来像是已经被预聚合并存储在数据库中。例如，假设一个应用程序每月生成一个报表，用于显示每类电影的总销售额，这样经理就可以决定将哪些新电影添加到库存中。你可以为他们提供以下视图，而不是允许其直接针对表编写查询：

 Sakila 示例数据库中包含了该视图定义，以及其他六个视图定义，其中几个会在接下来的示例中使用。

CREATE VIEW sales_by_film_category

AS

SELECT 

c.name AS category, 

SUM(p.amount) AS total_sales

FROM payment AS p 

INNER JOIN rental AS r ON p.rental_id = r.rental_id 

INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id 

INNER JOIN film AS f ON i.film_id = f.film_id 

INNER JOIN film_category AS fc ON f.film_id = fc.film_id 

INNER JOIN category AS c ON fc.category_id = c.category_id

GROUP BY c.name

ORDER BY total_sales DESC;

这种方法给数据库设计者提供很大的灵活性。如果将来某个时候你为了提高查询性能，要将数据预聚合到表中而不是使用视图求和，那么你可以创建一个 film_category_sales 表，用聚合数据加载该表，然后修改 sales_by_film_category 视图定义以从此表中检索数据。之后，所有使用 sales_by_film_category 视图的查询都将从新的 film_category_sales 表中检索数据，这意味着用户无需修改查询就可以提高性能。

隐藏复杂性

部署视图最常见的原因之一是为了保护终端用户不受复杂性的影响。例如，假设每个月都会创建一个报表以显示有关所有电影的信息，以及电影类别、电影中出现的演员数量、库存中的总拷贝数以及每部电影的租赁数量。你可以提供如下视图，而不是让他们使用六个不同的表检索数据：

CREATE VIEW film_stats

AS

SELECT f.film_id, f.title, f.description, f.rating, 

(SELECT c.name 

FROM category c 

INNER JOIN film_category fc 

ON c.category_id = fc.category_id

 WHERE fc.film_id = f.film_id) category_name, 

(SELECT count(*) 

FROM film_actor fa 

WHERE fa.film_id = f.film_id ) num_actors,

 (SELECT count(*) 

FROM inventory i 

WHERE i.film_id = f.film_id ) inventory_cnt, 

(SELECT count(*) 

FROM inventory i 

INNER JOIN rental r

 ON i.inventory_id = r.inventory_id 

WHERE i.film_id = f.film_id ) num_rentals

FROM film f;

这个视图定义很有趣，因为即使可以通过视图检索来自六个不同表的数据，查询的 from 子句也只有一个表（film）。来自其他五个表的数据是使用标量子查询生成的。如果有人使用此视图但未引用 category_name、num_actors、inventory_cnt 或 num_rentals 列，则不会执行任何子查询。这种方法允许在不连接其他五个表的情况下使用视图从 film 表中提取描述性信息。

连接分区数据

一些数据库设计将大型表分解为多个小块以提高性能。例如，如果 payment 表变大了，设计者可能会决定将其分为两个表：payment_current（保存最近六个月的数据）和 payment_historical（保存六个月前的所有数据）。如果客户希望查看某个特定客户的所有交易，则需要同时查询这两个表。但是，通过创建一个视图以查询两个表并组合查询结果，可以使其看起来像是所有交易数据都存储在一个表中。视图定义如下：

CREATE VIEW payment_all 

(payment_id, customer_id, 

staff_id, 

rental_id, 

amount, 

payment_date, 

last_update

 )

AS

SELECT

 payment_id,

customer_id,

staff_id,

rental_id, 

amount,

payment_date,

last_update

FROM payment_historic

UNION ALL

SELECT 

payment_id,

customer_id,

staff_id,

rental_id, 

amount,

payment_date,

last_update

FROM payment_current;

在这种情况下使用视图是一个好主意，因为它允许设计人员更改基础数据结构而无需强制所有数据库用户修改其查询。

可更新的视图

如果为用户提供了一组用于数据检索的视图，但如果用户还要修改同一数据，又该怎么办呢？例如，强制用户使用视图检索数据，然后允许用户使用 update 或 insert 语句直接修改基础表——这似乎有点奇怪。为此，MySQL、Oracle Database 和 SQL Server 都允许在遵守某些规则的前提下通过视图修改数据。对于 MySQL，如果满足以下条件，则视图是可更新的：

• 不使用聚合函数（max()、min()、avg()等）；

• 视图不使用 group by 或 having 子句；

• select 或 from 子句中不存在子查询，且 where 子句中的任何子查询都不引用 from 子句中的表；

• 视图不使用 union、union all 或 distinct；

• from 子句至少包含一个表或可更新视图；

• 如果有多个表或视图，那么 from 子句只使用内部连接。

为了演示可更新视图的实用性，我们从简单的视图定义开始，然后逐步深入更复杂的视图。

更新简单视图

本章开头的视图非常简单，让我们看看下面这个例子：

CREATE VIEW customer_vw  

(customer_id,  first_name,  last_name,  email  )

AS

SELECT

 customer_id,  

first_name,

 last_name,  

concat(substr(email,1,2), '*****', substr(email, -4)) email

FROM customer;

视图 customer_vw 查询单个表，其四列中只有一列是通过表达式派生出来的。此视图定义没有违反前面列出的任何限制，因此你可以使用它修改 customer 表中的数据。下面让我们使用该视图更新 Mary Smith 的姓氏（更新为 Smith Allen）：

mysql> UPDATE customer_vw 

-> SET last_name = 'SMITH-ALLEN' 

-> WHERE customer_id = 1;

Query OK, 1 row affected (0.11 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如你所见，该语句修改了一行，但我们还是检查一下基础 customer 表以验证我们的更新：

mysql> SELECT first_name, last_name, email 

-> FROM customer 

-> WHERE customer_id = 1;

+------------+-------------+-------------------------------+

| first_name | last_name | email |

+------------+-------------+-------------------------------+

| MARY | SMITH-ALLEN | MARY.SMITH@sakilacustomer.org |

+------------+-------------+-------------------------------+

1 row in set (0.00 sec)

虽然可以以这种方式修改视图中的大多数列，但却无法修改 email 列，因为它是从表达式派生的：

mysql> UPDATE customer_vw 

-> SET email = 'MARY.SMITH-ALLEN@sakilacustomer.org' 

-> WHERE customer_id = 1;

ERROR 1348 (HY000): Column 'email' is not updatable

这种情况可能并不是一件坏事，因为创建视图的主要原因是为了隐藏电子邮件地址。

如果要使用 customer_vw 视图插入数据，那就不太合适了，因为包含派生列的视图不能用于插入数据，即使派生列未包含在语句中。例如，下一条语句尝试使用 customer_vw 视图填充 customer_id、first_name 和 last_name 列：

mysql> INSERT INTO customer_vw  

-> (customer_id,  

-> first_name,  

-> last_name)  

-> VALUES (99999,'ROBERT','SIMPSON');

ERROR 1471 (HY000): The target table customer_vw of the INSERT is not insertable-into

现在你已经了解了简单视图的局限性，下一节将演示如何使用视图连接多个表。

更新复杂视图

虽然单表视图确实很常见，但你遇到的许多视图都会在基础查询的 from 子句中包含多个表。例如，下一个视图连接 customer、address、city 和 country 表，以便可以方便地查询客户的所有数据：

CREATE VIEW customer_details

AS

SELECT c.customer_id,  

c.store_id,  

c.first_name,

c.last_name,  

c.address_id,  

c.active,  

c.create_date,  

a.address,  

ct.city,  

cn.country,  

a.postal_code

FROM customer c

 INNER JOIN address a

 ON c.address_id = a.address_id

 INNER JOIN city ct  ON a.city_id = ct.city_id

 INNER JOIN country cn  ON ct.country_id = cn.country_id;

你可以使用此视图更新 customer 或 address 表中的数据，如下语句所示：

mysql> UPDATE customer_details  

-> SET last_name = 'SMITH-ALLEN', active = 0  

-> WHERE customer_id = 1;

Query OK, 1 row affected (0.10 sec)

Rows matched: 1 Changed: 1 Warnings: 0 

mysql> UPDATE customer_details  

-> SET address = '999 Mockingbird Lane'  

-> WHERE customer_id = 1;

Query OK,

1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0

第一个语句修改 customer.last_name 以及 customer.active 列，而第二条语句修改 address.address 列。你可能想知道，如果尝试在一条语句中更新两个表中的列，会发生什么情况，下面让我们看看：

mysql> UPDATE customer_details 

-> SET last_name = 'SMITH-ALLEN', 

-> active = 0, 

-> address = '999 Mockingbird Lane' 

-> WHERE customer_id = 1;

ERROR 1393 (HY000): Can not modify more than one base table  

through a join view 'sakila.customer_details'

如你所见，使用单个语句中修改两个基础表是不可行的，只能分别修改。接下来让我们尝试在两个表中插入一些新客户（customer_id=9998 和 9999）的数据：

mysql> INSERT INTO customer_details 

-> (customer_id, store_id, first_name, last_name, 

-> address_id, active, create_date) 

-> VALUES (9998, 1, 'BRIAN', 'SALAZAR', 5, 1, now());

Query OK, 1 row affected (0.23 sec)

此语句只填充 customer 表中的列并且工作正常。下面看一下如果扩展列列表以同时包含 address 表中的列会发生什么：

mysql> INSERT INTO customer_details  

-> (customer_id, store_id, first_name, last_name,  

-> address_id, active, create_date, address)  

-> VALUES (9999, 2, 'THOMAS', 'BISHOP', 7, 1, now(),  

-> '999 Mockingbird Lane'); ERROR 1393 (HY000): Can not modify more than one base table   

through a join view 'sakila.customer_details'

此版本的语句包含跨两个不同表的列，结果抛出异常。为了通过复杂视图插入数据，你需要知道每个列的来源。由于创建视图的目的是向终端用户隐藏复杂性，因此如果用户要熟悉视图定义，那么这似乎就达不到隐藏复杂性的目的了。

注意：Oracle Database 和 SQL Server 也允许通过视图插入和更新数据，但与 MySQL 一样有许多限制。如果你打算愿意编写一些 PL/SQL 或 Transact-SQL 语句，那么可以使用名为替代触发器（instead-of triggers）的功能，它允许你截取视图的 insert、update 和 delete 语句，并编写自定义代码将这些更改包括在内。如果没有这种类型的功能，通常会有太多的限制，使得通过视图进行更新成为非平凡应用程序中的可行策略。