一、视图的含义
在MySQL数据库中，视图（view）是一个命名的虚拟表，它由一个SQL查询来定义，或者说视图是从数据库中一个或多个表中导出来的表，并且视图还可以从已经存在的视图的基础上定义。视图可以当表使用，对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相对的基本表的数据也要发送变化；同时，若基本表的数据发送变化，则这种变化也可以自动地反应到视图中。但是与持久表不同的是，视图中的数据没有实际的物理存储，通过视图看到的数据只是存放在持久表中的数据。
MySQL从5.0开始可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全。
二、视图的作用
视图在数据库中发挥着重要的作用，视图的主要用途之一是被用做一个抽象装置。
1）简单化
看到的就是需要的，视图不仅可以简化用户对数据的就理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2）安全性
通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也娶不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同字迹上：
- 使用权限可被限制在基表的行的子集上。
- 使用权限可被限制在基表的列的子集上。
- 使用权限可被限制在基表的行和列的子集上。
- 使用权限可被限制在多个基表的连接所限定的行上。
- 使用权限可被限制在基表中的数据的统计汇总上。
- 使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。
3）逻辑数据独立性
视图可帮助用户屏蔽真实表结构变化带来的影响。
三、创建视图
创建视图使用CREATE VIEW语句，基本语法格式如下：
|
1 2 3 4 |
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] |
其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；ALGORITHM表示视图选择的算法；view_name为视图的名称，column_list为属性列；select_statement表示SELECT语句；WITH [CASCADED | LOCAL] CHECK OPTION参数表示视图在更新时保证在视图的权限范围之内。
ALGORITHM的取值有3个，分别是UNDEFINED | MERGE | TEMPTABLE，UNDEFINED表示MySQL将自动选择算法；MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。
CASCADED与LOCAL为可选参数，CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可。
该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。
视图属于数据库，在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建创建视图，创建时应将名称指定为db_name.view_name。
实验环境：创建一个数据库testdb，一个info表一个product表。
|
1 2 3 4 5 6 |
create database testdb; use testdb; create table info(quantity INT,price INT); insert into info values(2,50); create table product(name char(10),price INT); insert into product value('apple',50); |
在单表上创建视图
|
1 |
mysql> CREATE VIEW view_info AS select quantity,price,quantity*price from info; |
语句执行成功后，查看view_info视图中的数据
|
1 2 3 4 5 6 7 |
mysql> select * from view_info; +----------+-------+----------------+ | quantity | price | quantity*price | +----------+-------+----------------+ | 2 | 50 | 100 | +----------+-------+----------------+ 1 row in set (0.00 sec) |
默认情况下创建的视图和基本表的字段是一样的，也可以通过指定视图字段的名称来创建视图，代码如下：
|
1 |
mysql> CREATE VIEW view_info1(quantity,price,total) AS select quantity,price,quantity*price from testdb.info; |
语句执行成功，查看view_info1视图中的数据。
|
1 2 3 4 5 6 7 |
mysql> select * from view_info1; +----------+-------+-------+ | quantity | price | total | +----------+-------+-------+ | 2 | 50 | 100 | +----------+-------+-------+ 1 row in set (0.00 sec) |
可以看到，view_info和view_info1两个视图中字段名称不同，但数据却是相同的。因此，在使用视图的时候，可能用户根本就不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。
在多个表上创建视图
|
1 2 3 4 5 6 7 8 |
mysql> create view view_info_product AS select product.name,info.price FROM info,product WHERE info.price=product.price; mysql> select * from view_info_product; +-------+-------+ | name | price | +-------+-------+ | apple | 50 | +-------+-------+ 1 row in set (0.00 sec) |
四、管理视图
- 查看视图
查看视图是查看数据库中已存在的视图的定义，查看视图必须要有SHOW VIEW的权限，MySQL数据库下的user表中保存着这个信息。查看视图的方法包括：DESCRIBE、SHOW TABLE STATUS和SHOW CREATE VIEW。
查看视图结构
|
1 |
mysql> DESC view_info; |
查看所有视图
|
1 2 |
mysql> SHOW TABLE STATUS\G mysql> SHOW TABLE STATUS LIKE 'info'\G |
查看视图详细定义
|
1 |
mysql> SHOW CREATE VIEW view_info\G |
在MySQL中，information_schema数据库下的views表中存储了所有视图的定义。通过对views表的查询，可以查看数据库中所有视图的详细信息，查询语句如下：
|
1 |
mysql> SELECT * FROM information_schema.views\G |
- 修改视图
修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER语句来修改视图。
CREATE OR REPLACE VIEW语句修改视图
|
1 2 3 4 |
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name[(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] |
可以看到，修改视图的语句和创建视图的语句是完全一样的，当视图已经存在时，修改语句对视图进行修改；当视图不存在时，创建视图。下面通过一个实例来说明。
|
1 |
mysql> CREATE OR REPLACE VIEW view_info AS select quantity,price from testdb.info; |
执行完这条语句之后，新的view_info视图会少一个字段“quantify*price”。
- ALTER语句修改视图
ALTER语句时MySQL提供的另外一种修改视图的方法，语法同CREATE OR REPLACE VIEW一样。下面直接给出修改实例。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> ALTER VIEW view_info AS select quantity FROM testdb.info; 1 rows in set (0.00 sec) mysql> select * from view_info; +----------+ | quantity | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) |
通过LATER语句同样可以达到修改视图view_info的目的，从上面的执行过程来看，视图view_info只剩下1个quantity字段，修改成功。
- 更新视图
更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。下面介绍3种视图更新的方法：INSERT/UPDATE/DELETE。
示例1：使用UPDATE语句更新视图view_info
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> UPDATE view_info SET quantity=10; 1 rows in set (0.00 sec) mysql> select * from view_info; +----------+ | quantity | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> select * from testdb.info; +----------+-------+ | quantity | price | +----------+-------+ | 10 | 50 | +----------+-------+ 1 row in set (0.00 sec) |
对试图view_info更新后，基本表info的内容也更新了，同样当对基本表info更新后，另外一个视图view_info1中的内容也会更新。
示例2：使用INSERT语句插入一条记录到view_info视图
|
1 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 |
mysql> INSERT INTO testdb.info VALUES(5,50); 1 rows in set (0.00 sec) mysql> select * from testdb.info; +----------+-------+ | quantity | price | +----------+-------+ | 10 | 50 | | 5 | 50 | +----------+-------+ 2 rows in set (0.00 sec) mysql> select * from view_info; +----------+ | quantity | +----------+ | 10 | | 5 | +----------+ 2 rows in set (0.00 sec) mysql> select * from view_info1; +----------+-------+-------+ | quantity | price | total | +----------+-------+-------+ | 10 | 50 | 500 | | 5 | 50 | 250 | +----------+-------+-------+ 2 rows in set (0.00 sec) |
使用INSERT语句向info表中插入一条记录，通过SELECT查看info表和视图view_info，可以看到其中的内容也跟着更新，视图更新的不仅仅是数量和单价，总价也会更新。
示例3：使用DELETE语句删除视图view_info中的一条记录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> DELETE FROM view_info WHERE quantity=10; Query OK, 1 row affected (0.00 sec) mysql> select * from view_info; +----------+ | quantity | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select * from testdb.info; +----------+-------+ | quantity | price | +----------+-------+ | 5 | 50 | +----------+-------+ 1 row in set (0.00 sec) |
在视图view_info中删除quantity=10的记录，视图中的删除操作最终是通过删除基本表中相关的记录实现的，查看删除操作之后的表info和视图view_info1，可以看到通过视图删除其所依赖的基本表中的数据。
- 删除视图
当视图不再需要时，可以将其删除，删除一个或多个视图可以使用DROP VIEW语句，如下操作：
|
1 |
mysql> DROP VIEW IF EXISTS view_info1; |
五、物化视图
Oralce数据库支持物化视图，该视图不是基于基表的虚拟表，而是根据基表实际存在的实表，即物化视图的数据存储在易失的存储设备上。物化视图可以用于预先计算并保存多表的链接或聚集等耗时较多的SQL操作结果。这样，在执行复杂查询时，就可以避免进行这些耗时的操作，从而快速得到结果。物化视图的好处是对于一些复杂的统计类查询能直接查出结果。在SQL Server数据库中称这种视图为索引视图。
在Oracle数据库中，物化视图的创建方式包括以下两种：
- BUILD IMMEDIATE：是默认的创建方式，在创建物化视图的时候就生成数据。
- BUILD DEFERRED：则在创建物化视图时不生成数据，以后根据需要再生产数据。
查询重写是指当对物化视图的基表进行查询时，数据库会自动判断能否通过查询物化视图来直接得到最终的结果，如果可以，则避免了聚集或链接等这类较为复杂的SQL操作，直接从已经计算好的物化视图中得到所需的数据。
物化视图的刷新是指当基表发送了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：
- ON DEMAND：意味着物化视图在用户需要的时候进行刷新。
- ON COMMIT：意味着物化视图在对基表的DML操作提交的同时进行刷新。
而刷新的方法有四种：
- FAST：表示采用增量刷新，只刷新自上次刷新以后进行的修改。
- COMPLETE：表示是对整个物化视图进行完全的刷新。
- FORCE：表示数据库在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE方式。
- NEVER：表示指物化视图不进行任何刷新。
MySQL数据库本身并不支持物化视图，换句话说，MySQL数据库中的视图总是虚拟的。但是用户可以通过一些机制来实现物化视图的功能。例如要创建一个ON DEMAND的物化视图还是比较简单的，用户只需定时把数据导入到另一张表。例如有如下的订单表，记录了用户采购电脑设备的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 创建表; create table orders( order_id int unsigned not null auto_increment, product_name varchar(30) not null, price decimal(8,2) not null, amount smallint not null, primary key (order_id) ); # 插入数据; insert into orders values(NULL,'cpu',135.5,1); insert into orders values(NULL,'cpu',123.2,3); insert into orders values(NULL,'cpu',113.5,5); insert into orders values(NULL,'mem',48,3); |
接着建立一张物化视图的基表，用来统计每件物品的信息，如：
|
1 2 3 4 5 6 7 8 |
create table orders_mv( product_name varchar(30) not null, price_sum decimal(8,2) not null, price_avg float not null, amount_sum smallint not null, orders_cnt int not null, unique key (product_name) ); |
执行查询插入：
|
1 |
mysql> insert into orders_mv select product_name,sum(price),avg(price),sum(amount),count(*) from orders group by product_name; |
|
1 2 3 4 5 6 7 8 |
mysql> select * from orders_mv; +--------------+-----------+-----------+------------+------------+ | product_name | price_sum | price_avg | amount_sum | orders_cnt | +--------------+-----------+-----------+------------+------------+ | cpu | 372.20 | 124.067 | 9 | 3 | | mem | 48.00 | 48 | 3 | 1 | +--------------+-----------+-----------+------------+------------+ 2 rows in set (0.00 sec) |
在上面的例子中，把物化视图定义为一张表orders_mv，表名以_mv结尾，以便能让DBA很好地理解这张表的作用。通过上面的方式，用户就拥有了一个统计信息的物化视图。如果是要实现ON DEMAND的物化视图，只需把表清空，重新导入数据即可。当然，这是COMPLETE的刷新方式，要实现FAST的方式，也是可以的，只不过稍微复杂点，需要记录上次统计时order_id的位置。
但是，如果要实现ON COMMMIT的物化视图，就不像上面这么简单了。在oracle数据库中是通过物化视图日志来实现的，很显然MySQL数据库没有这个日志，不过通过触发器同样可以达到这个目的，首先需要对表orders建立一个触发器。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DELIMITER $$ CREATE TRIGGER orders_ins AFTER INSERT ON orders FOR EACH ROW BEGIN SET @old_price_sum = 0; SET @old_amount_sum = 0; SET @old_price_avg = 0; SET @old_orders_cnt = 0; SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0) FROM orders_mv WHERE product_name = NEW.product_name INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_orders_cnt; SET @new_price_sum = @old_price_sum + NEW.price; SET @new_amount_sum = @old_amount_sum + NEW.amount; SET @new_orders_cnt = @old_orders_cnt + 1; SET @new_price_avg = @new_price_sum / @new_orders_cnt; REPLACE INTO orders_mv VALUES(NEW.product_name, @new_price_sum, @new_price_avg, @new_amount_sum, @new_orders_cnt); END; $$ DELIMITER ; |
上述代码创建了一个INSERT的触发器，每个INSERT操作都会重新统计表orders_mv中的数据。接着运行以下插入操作，并观察之后物化视图表orders_mv中的记录。
|
1 2 |
mysql> insert into orders values (null,'ssd',299,3); mysql> insert into orders values (null,'mem',47.9,3); |
可以发现插入两条新的记录后，直接查询order_mv表就能得到统计信息。而不像之前需要重新进行SQL语句得统计，这就实现了ON COMMIT的物化视图功能。需要注意的是，orders表可能还会有UPDATE和DELETE的操作，所以还需要实现DELETE和UPDATE的触发器。
通过触发器，在MySQL数据库中实现了类似物化视图的功能，但是MySQL数据库本身并不支持物化视图，因此对于物化视图支持的查询重写功能就显得无能为力了。
六、总结
在MySQL中，视图和表的区别以及联系是什么呢？
两者的区别
- 视图时以及编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。
- 视图没有实际的物理记录，而基本表有。
- 表是内容，视图时窗口。
- 视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改。
- 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
- 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
- 视图的建立和删除只影响视图本身，不影响对应的基本表。
两者的联系
视图是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有记录）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
