MySQL 数据库视图的简单使用

---

1. 视图简介

视图是一种简单的数据查询机制。不同于表，视图不涉及数据存储，不用担心视图会占满磁盘空间。

创建视图，就是将一个查询结果保存为一个虚拟表，这个虚拟表可以执行查询操作。创建视图可能出于各种理由，比如对用户隐藏列、简化数据库设计等原因。简单来说视图其实就是保存了创建视图时的SELECT语句，通过视图查询数据其实最终查询的还是表，所以表中的数据变动，通过视图查询的结果也会相应的变化。

2. 创建视图

执行CREATE VIEW语句时，数据库服务器只是简单的存储视图的定义为将来使用。如果不执行视图查询就不会检索或存储任何数据，一旦视图被创建，用户就能把它当做一个表来查询。

创建视图的语法结构：

#AS后面就是普通的SQL查询语句
CREATE VIEW view_name [column1, column2,...] AS SELECT column1, column2,... FROM table_name;

创建视图：

#不指定视图字段名，直接使用的是SELECT的字段名
CREATE VIEW employee_vw AS SELECT emp_id, fname, lname, YEAR(start_date) start_year FROM employee;

#指定视图字段名
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 FROM 8 FOR 4)) fed_id, cust_type_cd, address, city, state, postal_code FROM customer;

视图在查询的时候可以和其他视图或者表做连接查询，可以完全和普通的表一样使用。查询视图：

SELECT * FROM employee_vw;

SELECT cust_id, fed_id, cust_type_cd, zipcode FROM customer_vw;

服务器真正执行的查询不是用户提交的SQL语句，也不是视图创建的SQL语句，而是将两者合并创建的一个新的查询SQL，真正执行的查询SQL：

SELECT emp_id, fname, lname, YEAR(start_date) start_year FROM employee

SELECT cust_id, CONCAT('ends in ',SUBSTR(fed_id FROM 8 FOR 4)) fed_id, cust_type_cd, postal_code AS zipcode  FROM customer;

3. 视图的作用

3.1. 数据安全

如果创建一个表，并允许其他用户查询，那么其他用户将能够访问表中的每行每列数据，但是这个表中包含一些敏感信息，比如身份证号码或信用卡号码之类的列，此时可以选择将该表设置为私有的（不向其他用户授权SELECT许可），然后创建一个或多个视图，这些视图省略或掩盖了敏感信息列或者内容，可以将这些视图提供给其他用户访问，这样就保证了数据安全。

3.2. 数据聚合

报表程序通常需要聚合数据，视图就是实现这一功能的好方法，使数据像是已经被预先聚合并存储在数据库一样。

如果之后想要大幅提高查询效率，决定将数据聚合到一个表中而不是利用视图来聚合，这是可以创建一个新表，直接利用该视图填充新表数据，这样将视图转化为一个表：

#创建一个表，并将数据填充。
CREATE TABLE customer_totals AS SELECT * FROM customer_vw;

然后再修改之前的视图定义：

#创建或替换之前的视图
CREATE OR REPLACE VIEW customer_vw AS SELECT * FROM customer_totals;

这样所有的查询就是通过视图customer_vw从新表customer_totals中提取数据，意味着用户无需修改查询就能得到性能提升。

3.3. 隐藏复杂性

部署视图最常见的理由就是为终端用户屏蔽复杂性。如果一个报表需要通过四个表来查询数据，此时可以把这四个表中的数据查询定义为一个视图，这样终端用户只需要查询这个视图就可以了。

3.4. 连接分区数据

如果对一些数据表进行了分表操作，比如将交易表分为最近6个月的交易表和6个月之前的交易表两张表。如果客户想要查看特定账号的所有交易，此时就需要查询两个表。这时可以通过创建查询两表的视图（通过UNION ALL联合两个表的查询结果），使客户像查询一个单表一样。这样做允许设计者更改基础数据结构而不必强迫所有数据用户修改它们的查询语句。

4. 可更新视图

一般视图都是用作查询使用的，但是有时用户需要通过视图修改表中的数据，MySQL允许用户在遵循特定规则的前提下通过视图修改数据：

• 视图没有使用聚合函数。
• 视图没有使用GROUP BY或HAVING子句。
• SELECT或FROM子句中不存在子查询，并且WHERE子句中的任何子查询都不引用FROM子句中的表。
• 视图没有使用UNION、UNION ALL、DISTINCT。
• FROM子句只包括一个表或可更新视图。
• 如果不止一个表或视图，那么FROM子句只能使用内连接。

4.1. 更新简单视图

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 FROM 8 FOR 4)) fed_id, cust_type_cd, address, city, state, postal_code FROM customer;

上面的视图是一个简单视图，除了fed_id列无法进行UPDATE操作，其它列都可以进行更新。因为fed_id列是由一个表达式进行生成的。

UPDATE customer_vw SET city = 'Wooburn' WHERE city = 'Woburn';

包含导出列的视图无法进行插入数据，fed_id列属于导出列，所以该视图不能进行插入数据。

4.2. 更新复杂视图

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 cst.cust_type_cd = 'B';

可以通过上面这个视图更新customer表和business表，比如可以分别更新属于这两个表的字段postal_code和incorp_date，但是不能在一个更新语句中同时更新这两个字段。

比如postal_code字段是从customer表中查询出来的，所以可通过该字段更新customer表的postal_code字段。

UPDATE business_customer_vw SET postal_code = '99999' WHERE cust_id = 10;

比如incorp_date字段是从business表中查询出来的，所以可通过该字段更新business表的incorp_date字段。

UPDATE business_customer_vw SET incorp_date = '2008-11-17' WHERE cust_id = 10;

执行插入操作的时候，可以插入customer表成功：

INSERT INTO business_customer_vw (fed_id, address, city, state, postal_code) VALUES
('04-4444444', '99 Main St', 'Peabody', 'MA', '01975');

但是business表的插入操作失败了，因为视图中的cust_id列是映射到customer表的cust_id的，而不是business表的cust_id：

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');
1393 - Can not modify more than one base table through a join view 'bank.business_customer_vw'
mysql>