第14章 视图
# 第14章 视图
设计良好的应用程序通常会公开一个公共接口，同时将实现细节隐藏起来，这样在未来进行设计变更时就不会影响终端用户。在设计数据库时，你也可以通过将表隐藏起来，只允许用户通过一组视图（view）来访问数据，从而达到类似的效果。本章旨在定义视图是什么、如何创建视图，以及何时和如何使用视图。
# 什么是视图？
视图只是一种查询数据的机制。与表不同，视图不涉及数据存储，你无需担心视图会占用磁盘空间。创建视图时，你为一条SELECT语句指定一个名称，然后存储该查询以供他人使用。其他用户可以使用你创建的视图来访问数据，就像他们在直接查询表一样（实际上，他们可能甚至不知道自己正在使用视图）。
举个简单的例子，假设你想要部分隐藏客户（customer）表中的联邦身份证号码（社会保障号码和公司标识符）。例如，客服部门可能只需要访问联邦身份证号码的最后几位来核实来电者的身份，但公开完整的号码会违反公司的隐私政策。因此，你可以定义一个名为customer_vw的视图，并要求所有银行员工通过它来访问客户数据，而不是直接允许他们访问customer表。下面是这个视图的定义：
CREATE VIEW customer_vw (
cust_id,
fed_id,
cust_type_cd,
address,
city,
state,
zipcode
) AS
SELECT
cust_id,
concat('ends in ', substr(fed_id, 8, 4)) fed_id,
cust_type_cd,
address,
city,
state,
postal_code
FROM
customer;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
该语句的第一部分列出了视图的列名，这些列名可能与底层表的列名不同（例如，customer_vw视图有一个名为zipcode的列，它映射到customer.postal_code列）。语句的第二部分是一条SELECT语句，它必须为视图中的每一列提供一个表达式。
当执行CREATE VIEW语句时，数据库服务器只是存储视图定义以供将来使用，并不会执行该查询，也不会检索或存储数据。视图创建好后，用户可以像查询表一样查询它，例如：
mysql> SELECT cust_id, fed_id, cust_type_cd
-> FROM customer_vw;
+---------+------------------+--------------+
| cust_id | fed_id | cust_type_cd |
+---------+------------------+--------------+
| 1 | ends in 1111 | I |
| 2 | ends in 2222 | I |
| 3 | ends in 3333 | I |
| 4 | ends in 4444 | I |
| 5 | ends in 5555 | I |
| 6 | ends in 6666 | I |
| 7 | ends in 7777 | I |
| 8 | ends in 8888 | I |
| 9 | ends in 9999 | I |
| 10 | ends in 111 | B |
| 11 | ends in 222 | B |
| 12 | ends in 333 | B |
| 13 | ends in 444 | B |
+---------+------------------+--------------+
13 rows in set (0.02 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
服务器实际执行的查询既不是用户提交的查询，也不是视图定义中附带的查询。相反，服务器会将这两个查询合并，创建出另一条语句，在这个例子中，这条语句如下：
SELECT
cust_id,
concat('ends in ', substr(fed_id, 8, 4)) fed_id,
cust_type_cd
FROM
customer;
2
3
4
5
6
尽管customer_vw视图定义包含了customer表中的七列，但服务器执行的查询只检索了这七列中的三列。正如你将在本章后面看到的，如果视图中的某些列与函数或子查询相关联，这将是一个重要的区别。
从用户的角度来看，视图看起来与表完全一样。如果你想知道视图中可用的列，可以使用MySQL（或Oracle）的DESCRIBE命令来查看：
mysql> describe customer_vw;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| cust_id | int(10) unsigned | NO | | 0 | |
| fed_id | varchar(12) | YES | | NULL | |
| cust_type_cd | enum('I','B') | NO | | NULL | |
| address | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| postal_code | varchar(10) | YES | | NULL | |
+-------------+---------------------+------+-----+---------+-------+
7 rows in set (1.40 sec)
2
3
4
5
6
7
8
9
10
11
12
13
通过视图进行查询时，你可以自由使用SELECT语句的任何子句，包括GROUP BY、HAVING和ORDER BY。例如：
mysql> SELECT cust_type_cd, count(*)
-> FROM customer_vw
-> WHERE state = 'MA'
-> GROUP BY cust_type_cd
-> ORDER BY 1;
+--------------+----------+
| cust_type_cd | count(*) |
+--------------+----------+
| I | 7 |
| B | 2 |
+--------------+----------+
2 rows in set (0.22 sec)
2
3
4
5
6
7
8
9
10
11
12
此外，你可以在查询中将视图与其他表（甚至其他视图）进行连接，例如：
mysql> SELECT cst.cust_id, cst.fed_id, bus.name
-> FROM customer_vw cst
-> INNER JOIN business bus
-> ON cst.cust_id = bus.cust_id;
+---------+------------------+----------------------------------+
| cust_id | fed_id | name |
+---------+------------------+----------------------------------+
| 10 | ends in 111 | Chilton Engineering |
| 11 | ends in 222 | Northeast Cooling Inc. |
| 12 | ends in 333 | Superior Auto Body |
| 13 | ends in 444 | AAA Insurance Inc. |
+---------+------------------+----------------------------------+
4 rows in set (0.24 sec)
2
3
4
5
6
7
8
9
10
11
12
13
这个查询将customer_vw视图与business表进行连接，以仅检索企业客户的数据。
# 为什么使用视图？
在上一节中，我展示了一个简单的视图，其唯一目的是隐藏customer.fed_id列的内容。虽然视图常被用于这个目的，但使用视图还有很多其他原因，我将在以下小节中进行说明。
# 数据安全性
如果你创建了一个表并允许用户查询它，那么用户将能够访问表中的每一列和每一行数据。然而，正如我前面所指出的，你的表中可能包含一些敏感数据列，如身份证号码或信用卡号码。将这些数据暴露给所有用户不仅是个糟糕的主意，还可能违反公司的隐私政策，甚至违反州或联邦法律。
在这些情况下，最好的方法是将表隐藏起来（即不向任何用户授予SELECT权限），然后创建一个或多个视图，这些视图要么省略敏感列，要么对其进行模糊处理（例如对customer_vw.fed_id列采用“以####结尾”的方法）。你还可以通过在视图定义中添加WHERE子句来限制一组用户可以访问的行。例如，下面的视图定义只允许查询企业客户的数据：
CREATE VIEW business_customer_vw (
cust_id,
fed_id,
cust_type_cd,
address,
city,
state,
zipcode
) AS
SELECT
cust_id,
concat('ends in ', substr(fed_id, 8, 4)) fed_id,
cust_type_cd,
address,
city,
state,
postal_code
FROM
customer
WHERE
cust_type_cd = 'B';
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
如果你将这个视图提供给公司的企业银行部门，他们将只能访问企业账户的数据，因为视图WHERE子句中的条件会始终包含在他们的查询中。
Oracle数据库的用户在保护表的行和列方面还有另一种选择：虚拟专用数据库（Virtual Private Database，VPD）。VPD允许你为表附加策略，之后服务器会根据需要修改用户的查询以执行这些策略。例如，如果你制定了一项策略，规定企业银行部门的成员只能查看企业账户，那么cust_type_cd = 'B'这个条件将被添加到他们对customer表的所有查询中。
# 数据聚合
报表应用程序通常需要聚合数据，而视图是一种很好的方式，它能让数据看起来像是预先聚合并存储在数据库中的。例如，假设一个应用程序每月生成一份报表，显示每个客户的账户数量和总存款金额。与其让应用程序开发人员直接针对基础表编写查询，你可以为他们提供以下视图：
CREATE VIEW customer_totals_vw (
cust_id,
cust_type_cd,
cust_name,
num_accounts,
tot_deposits
) AS
SELECT
cst.cust_id,
cst.cust_type_cd,
CASE
WHEN cst.cust_type_cd = 'B' THEN
(SELECT bus.name FROM business bus WHERE bus.cust_id = cst.cust_id)
ELSE
(SELECT concat(ind.fname,' ', ind.lname) FROM individual ind WHERE ind.cust_id = cst.cust_id)
END cust_name,
sum(CASE WHEN act.status = 'ACTIVE' THEN 1 ELSE 0 END) tot_active_accounts,
sum(CASE WHEN act.status = 'ACTIVE' THEN act.avail_balance ELSE 0 END) tot_balance
FROM
customer cst
INNER JOIN
account act
ON
act.cust_id = cst.cust_id
GROUP BY
cst.cust_id, cst.cust_type_cd;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
作为数据库设计者，使用这种方法会让你拥有很大的灵活性。如果你在未来某个时候认为，如果将数据预先聚合到一个表中，而不是使用视图进行求和，查询性能会有显著提升，那么你可以创建一个customer_totals表，并修改customer_totals_vw视图的定义，使其从这个新表中检索数据。在修改视图定义之前，你可以使用该视图来填充新表。以下是针对这种情况所需的SQL语句：
mysql> CREATE TABLE customer_totals
-> AS
-> SELECT * FROM customer_totals_vw;
Query OK, 13 rows affected (3.33 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> CREATE OR REPLACE VIEW customer_totals_vw (
-> cust_id,
-> cust_type_cd,
-> cust_name,
-> num_accounts,
-> tot_deposits
-> )
-> AS
-> SELECT cust_id, cust_type_cd, cust_name, num_accounts, tot_deposits
-> FROM customer_totals;
Query OK, 0 rows affected (0.02 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
从现在开始，所有使用customer_totals_vw视图的查询都将从新的customer_totals表中提取数据，这意味着用户无需修改查询就能看到性能提升。
# 隐藏复杂性
部署视图（views）最常见的原因之一是为了让终端用户避开复杂性。例如，假设每月都要生成一份报告，展示每个分支机构的员工数量、有效账户总数以及交易总数。与其让报告设计者在四个不同的表中查找以收集必要的数据，不如提供一个如下所示的视图：
CREATE VIEW branch_activity_vw
(
branch_name,
city,
state,
num_employees,
num_active_accounts,
tot_transactions
) AS
SELECT
br.name,
br.city,
br.state,
(SELECT count(*)
FROM employee emp
WHERE emp.assigned_branch_id = br.branch_id) num_emps,
(SELECT count(*)
FROM account acnt
WHERE acnt.status = 'ACTIVE' AND acnt.open_branch_id = br.branch_id) num_accounts,
(SELECT count(*)
FROM transaction txn
WHERE txn.execution_branch_id = br.branch_id) num_txns
FROM branch br;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
这个视图定义很有意思，因为六个列值中有三个是使用标量子查询生成的。如果有人使用这个视图，但没有引用num_employees、num_active_accounts或tot_transactions列，那么这些子查询都不会被执行。
# 连接分区数据
有些数据库设计会将大表拆分成多个部分以提高性能。例如，如果交易表（transaction table）变得很大，设计者可能会决定将其拆分为两个表：transaction_current，用于存储最近六个月的数据；以及transaction_historic，用于存储六个月之前的所有数据。如果客户想要查看某个特定账户的所有交易记录，就需要查询这两个表。不过，通过创建一个查询这两个表并将结果合并在一起的视图，就可以让所有交易数据看起来就像存储在一个表中。下面是视图定义：
CREATE VIEW transaction_vw
(
txn_date,
account_id,
txn_type_cd,
amount,
teller_emp_id,
execution_branch_id,
funds_avail_date
) AS
SELECT
txn_date,
account_id,
txn_type_cd,
amount,
teller_emp_id,
execution_branch_id,
funds_avail_date
FROM transaction_historic
UNION ALL
SELECT
txn_date,
account_id,
txn_type_cd,
amount,
teller_emp_id,
execution_branch_id,
funds_avail_date
FROM transaction_current;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
在这种情况下使用视图是个不错的主意，因为它允许设计者更改底层数据的结构，而无需强制所有数据库用户修改他们的查询语句。
# 可更新视图
如果为用户提供了一组用于数据检索的视图，而用户也需要修改相同的数据，这时该怎么办呢？例如，强迫用户使用视图来检索数据，但却允许他们使用update或insert语句直接修改底层表，这可能看起来有点奇怪。为此，MySQL、Oracle数据库（Oracle Database）和SQL Server都允许通过视图修改数据，只要遵守某些限制条件即可。对于MySQL而言，满足以下条件的视图是可更新的：
- 未使用聚合函数（如
max()、min()、avg()等）。 - 视图未使用
group by或having子句。 select或from子句中不存在子查询，并且where子句中的任何子查询都不引用from子句中的表。- 视图未使用
union、union all或distinct。 from子句中至少包含一个表或可更新视图。- 如果
from子句中有多个表或视图，则仅使用内连接（inner join）。
为了展示可更新视图的实用性，最好先从一个简单的视图定义开始，然后再过渡到更复杂的视图。
# 更新简单视图
本章开头的视图非常简单，就从它开始吧：
CREATE VIEW customer_vw
(
cust_id,
fed_id,
cust_type_cd,
address,
city,
state,
zipcode
) AS
SELECT
cust_id,
concat('ends in ', substr(fed_id, 8, 4)) fed_id,
cust_type_cd,
address,
city,
state,
postal_code
FROM customer;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
customer_vw视图查询单个表，七列中只有一列是通过表达式派生出来的。这个视图定义没有违反前面列出的任何限制条件，因此可以使用它来修改customer表中的数据，例如：
mysql> UPDATE customer_vw
-> SET city = 'Woooburn'
-> WHERE city = 'Woburn';
Query OK, 1 row affected (0.34 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2
3
4
5
可以看到，该语句声称修改了一行数据，但为了确保无误，还是检查一下底层的customer表：
mysql> SELECT DISTINCT city FROM customer;
+-------------+
| city |
+-------------+
| Lynnfield |
| Woooburn |
| Quincy |
| Waltham |
| Salem |
| Wilmington |
| Newton |
+-------------+
7 rows in set (0.12 sec)
2
3
4
5
6
7
8
9
10
11
12
13
虽然可以用这种方式修改视图中的大多数列，但无法修改fed_id列，因为它是从表达式派生出来的：
mysql> UPDATE customer_vw
-> SET city = 'Woburn', fed_id = '999999999'
-> WHERE city = 'Woooburn';
ERROR 1348 (HY000): Column 'fed_id' is not updatable
2
3
4
在这种情况下，这可能并非坏事，因为这个视图的目的就是隐藏联邦标识符。
如果想使用customer_vw视图插入数据，那是不行的；包含派生列的视图不能用于插入数据，即使插入语句中不包含这些派生列也不行。例如，下面的语句尝试仅使用customer_vw视图填充cust_id、cust_type_cd和city列：
mysql> INSERT INTO customer_vw(cust_id, cust_type_cd, city)
-> VALUES (9999, 'I', 'Worcester');
ERROR 1471 (HY000): The target table customer_vw of the INSERT is not insertable - into
2
3
既然已经了解了简单视图的局限性，下一节将展示如何使用连接多个表的视图。
# 更新复杂视图
虽然单表视图确实很常见，但遇到的许多视图在底层查询的from子句中会包含多个表。例如，下一个视图连接了business表和customer表，这样就可以轻松查询所有企业客户的数据：
CREATE VIEW business_customer_vw
(
cust_id,
fed_id,
address,
city,
state,
postal_code,
business_name,
state_id,
incorp_date
) AS
SELECT
cst.cust_id,
cst.fed_id,
cst.address,
cst.city,
cst.state,
cst.postal_code,
bsn.name,
bsn.state_id,
bsn.incorp_date
FROM customer cst
INNER JOIN business bsn ON cst.cust_id = bsn.cust_id
WHERE cust_type_cd = 'B';
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
可以使用这个视图更新customer表或business表中的数据，如下列语句所示：
mysql> UPDATE business_customer_vw
-> SET postal_code = '99999'
-> WHERE cust_id = 10;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE business_customer_vw
-> SET incorp_date = '2008-11-17'
-> WHERE cust_id = 10;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2
3
4
5
6
7
8
9
10
11
第一条语句修改了customer.postal_code列，而第二条语句修改了business.incorp_date列。可能会想，如果在一条语句中尝试更新两个表中的列会发生什么，来看看：
mysql> UPDATE business_customer_vw
-> SET postal_code = '88888', incorp_date = '2008-10-31'
-> WHERE cust_id = 10;
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'bank.business_customer_vw'
2
3
4
可以看到，允许修改两个底层表，只要不是在一条语句中进行操作。现在尝试为一个新客户（cust_id = 99）向两个表中插入数据：
mysql> INSERT INTO business_customer_vw
-> (cust_id, fed_id, address, city, state, postal_code)
-> VALUES (99, '04-9999999', '99 Main St.', 'Peabody', 'MA', '01975');
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO business_customer_vw
-> (cust_id, business_name, state_id, incorp_date)
-> VALUES (99, 'Ninety-Nine Restaurant', '99-999-999', '1999-01-01');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'bank.business_customer_vw'
2
3
4
5
6
7
8
9
第一条尝试向customer表插入数据的语句执行正常，但第二条尝试向business表插入一行数据的语句引发了异常。第二条语句失败是因为两个表都包含cust_id列，而视图定义中的cust_id列映射到了customer.cust_id列。因此，使用前面的视图定义无法向business表插入数据。
Oracle数据库和SQL Server也允许通过视图插入和更新数据，但与MySQL一样，也有很多限制。不过，如果愿意编写一些PL/SQL或Transact-SQL代码，就可以使用一种称为替代触发器（instead-of triggers）的功能，它允许拦截针对视图的insert、update和delete语句，并编写自定义代码来合并这些更改。如果没有这种功能，限制就太多了，对于较为复杂的应用程序而言，通过视图进行更新就不是一种可行的策略。
# 知识测验
通过完成以下练习来检验你对视图的理解。完成后，将你的答案与附录C中的答案进行对比。
# 练习14 - 1
创建一个查询employee表的视图，在不使用where子句进行查询时生成以下输出：
+-----------------+------------------+
| supervisor_name | employee_name |
+-----------------+------------------+
| NULL | Michael Smith |
| Michael Smith | Susan Barker |
| Michael Smith | Robert Tyler |
| Robert Tyler | Susan Hawthorne |
| Susan Hawthorne | John Gooding |
| Susan Hawthorne | Helen Fleming |
| Helen Fleming | Chris Tucker |
| Helen Fleming | Sarah Parker |
| Helen Fleming | Jane Grossman |
| Susan Hawthorne | Paula Roberts |
| Paula Roberts | Thomas Ziegler |
| Paula Roberts | Samantha Jameson |
| Susan Hawthorne | John Blake |
| John Blake | Cindy Mason |
| John Blake | Frank Portman |
| Susan Hawthorne | Theresa Markham |
| Theresa Markham | Beth Fowler |
| Theresa Markham | Rick Tulman |
+-----------------+------------------+
18 rows in set (1.47 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 练习14 - 2
银行行长希望生成一份报告，展示每个分支机构的名称和所在城市，以及在该分支机构开设的所有账户的总余额。创建一个视图来生成这些数据。