SQL数据分析系列之视图

[[407941]]

本文转载自微信公众号「数据与智能」，作者Alan Beaulieu。转载本文请联系数据与智能公众号。

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

1. 什么是视图

视图其实就是一种数据查询机制。与表不同，视图不涉及数据存储，所以不必担心视图会占用磁盘空间。可以通过命名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美元或更多金额的客户。

2. 为何要用视图

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

2.1 数据安全

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

对于这些情况，最好的方法是保持表的私有性(即不向任何用户授予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表的所有查询中。

2.2 数据聚合

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

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表中检索数据，这意味着用户无需修改查询就可以提高性能。

2.3 隐藏复杂性

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

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表中提取描述性信息。

2.4 连接分区数据

一些数据库设计将大型表分解为多个小块以提高性能。例如，如果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; 

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

3. 可更新的视图

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

• 不使用聚合函数(max()、min()、avg()等);
• 视图不使用group by或having子句;
• select或from子句中不存在子查询，且where子句中的任何子查询都不引用from子句中的表;
• 视图不使用union、union all或distinct;
• from子句至少包含一个表或可更新视图;
• 如果有多个表或视图，那么from子句只使用内部连接。

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

3.1 更新简单视图

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

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 

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

3.2 更新复杂视图

虽然单表视图确实很常见，但你遇到的许多视图都会在基础查询的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语句，并编写自定义代码将这些更改包括在内。如果没有这种类型的功能，通常会有太多的限制，使得通过视图进行更新成为非平凡应用程序中的可行策略。