[mysql基础文档]-18-如何创建View视图
引言
视图View有两个基本功能；其一：简化查询，将一个复杂查询结果集储存为一个视图，能简化查询语句的复杂度；其二：权限控制，开放表中一部分列数据储存为视图，这样可以隐藏一些不想让外部看到的信息。
文章目录
0×1.如何创建视图
View可以储存一条Select查询语句，当我们Select视图的时候，实际上就是调用了这条查询语句：
--创建表t28
mysql> create table t28(
-> id int primary key auto_increment,
-> uname char(30) not null default ''
-> );
--插入三条记录
mysql> insert into t28(uname) values('qingsword');
mysql> insert into t28(uname) values('qingsword.com');
mysql> insert into t28(uname) values('www.qingsword.com');
--完整的t28表内容
mysql> select * from t28;
+----+-------------------+
| id | uname |
+----+-------------------+
| 1 | qingsword |
| 2 | qingsword.com |
| 3 | www.qingsword.com |
+----+-------------------+
--现在我想将下面的这条语句查询到了id=2或3的结果集储存为一个视图，方便以后再查询或筛选
mysql> select * from t28 where id=2 or id=3;
+----+-------------------+
| id | uname |
+----+-------------------+
| 2 | qingsword.com |
| 3 | www.qingsword.com |
+----+-------------------+
--view视图创建语法：
--create view [视图名称] as [原查询语句];
mysql> create view v1 as select * from t28 where id=2 or id=3;
--这样v1就储存了上面那条select语句
mysql> select * from v1;
+----+-------------------+
| id | uname |
+----+-------------------+
| 2 | qingsword.com |
| 3 | www.qingsword.com |
+----+-------------------+
--可以像对待一张表一样来对待这个视图v1，可以对它再进行where筛选查询
mysql> select * from v1 where id=3;
+----+-------------------+
| id | uname |
+----+-------------------+
| 3 | www.qingsword.com |
+----+-------------------+
--在show tables里面也可以看到v1这个名字，就好像一张真是存在的表一样
mysql> show tables;
0×2.视图管理
从第一部分的实例可以看出，既然View视图会出现在show tables里面，那应该怎么分辨这个名字对应的到底只是视图，还是一张完整的表呢？
● 可以通过show table status判断是view还是table
--如果不加where筛选，就是显示本数据库中所有表和视图的结构，本例仅显示v1的结构 mysql> show table status where name='v1' \G ************** 1. row ************** Name: v1 Engine: NULL Version: NULL ............. Comment: VIEW <-这一行是View，说明v1是一个视图，如果是表，Comment后面是空 --知道了v1是视图之后，可以show出它的创建语法，可以看到视图的显示编码utf8，创建语法，使用的算法ALGORITHM=UNDEFINED，在本文第三部分介绍 mysql> show create view v1 \G ************** 1. row ************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t28`.`id` AS `id`,`t28`.`uname` AS `uname` from `t28` where ((`t28`.`id` = 2) or (`t28`.`id` = 3)) character_set_client: utf8 collation_connection: utf8_general_ci
● 修改视图：
修改真实表数据会同时影响视图显示，因为视图只是真实表的一个投影；与此相对，对视图的修改会出现两种情况，如果视图是和真实表一一对应，那么可以通过修改视图来修改真实表数据；但如果视图是真实表的几列通过某些计算得到的结果集，那么不能对这个视图进行任何修改，请看下面的实例；
--现在创建t29，设置3列，第一列储存产品种类id，第二列储存产品名称，第三列储存产品价格 mysql> create table t29( -> product_id int not null default 0, -> product_name varchar(100) not null default '', -> product_price decimal(8,2) not null default 0.00 -> ); --插入三条记录 mysql> insert into t29 values(1,'iPhone4',4333.12); mysql> insert into t29 values(1,'iPhone4s',4999.86); mysql> insert into t29 values(2,'iPhone6',5666.18); mysql> select * from t29; +------------+--------------+---------------+ | product_id | product_name | product_price | +------------+--------------+---------------+ | 1 | iPhone4 | 4333.12 | | 1 | iPhone4s | 4999.86 | | 2 | iPhone6 | 5666.18 | +------------+--------------+---------------+ --创建视图v2，这是一个与t29每条记录数据一一对应的视图 mysql> create view v2 as select * from t29 where product_id=1; mysql> select * from v2; +------------+--------------+---------------+ | product_id | product_name | product_price | +------------+--------------+---------------+ | 1 | iPhone4 | 4333.12 | | 1 | iPhone4s | 4999.86 | +------------+--------------+---------------+ --创建视图v3，以产品种类id分组，对产品价格做avg()运算（求平均值），这种视图是经过运算得到的，不可能和t29数据一一对应 mysql> create view v3 as select product_id,avg(product_price) from t29 group by product_id; mysql> select * from v3; +------------+--------------------+ | product_id | avg(product_price) | +------------+--------------------+ | 2 | 5666.180000 | | 1 | 4666.490000 | +------------+--------------------+ --现在对v3进行修改，报错了 mysql> update v3 set product_id=3 where avg(product_price)=4666.49; ERROR 1288 (HY000): The target table v3 of the UPDATE is not updatable --对v2进行修改，修改被写入t29 mysql> update v2 set product_price=4111 where product_name='iPhone4'; --查看修改的记录 mysql> select * from t29 where product_name='iphone4'; +------------+--------------+---------------+ | product_id | product_name | product_price | +------------+--------------+---------------+ | 1 | iPhone4 | 4111.00 | +------------+--------------+---------------+ --与此同时，v3因为是对原表数据进行计算得到的，当原表数据改变时，运算结果也会改变 mysql> select * from v3; +------------+--------------------+ | product_id | avg(product_price) | +------------+--------------------+ | 2 | 5666.180000 | | 1 | 4555.430000 | +------------+--------------------+
P.S：上面这个实例出现了前面并没有介绍到的一些查询语句以及运算函数，这些会在后面的文章详细介绍，这里只要明白在什么情况下视图是可修改，对可修改视图的修改会被写入原表，明白这些就可以了。
● 删除视图，可以使用drop，如同删除表一样：
mysql> drop view v1;
0×3.视图算法简述t
view实际上只是储存了创建时候的查询语句，当我们对视图再次筛选查询的时候，数据库会根据创建视图时候的复杂度自动选择是使用什么算法来执行你的查询，一般分为两种，merge算法以及temptable算法；
● merge算法
将储存的视图查询语句和第二次对视图本身的查询的语句进行合并（merge）；
--如果创建的是简单的视图，比如第2小节中的v2，语句不是很复杂,也没有对列进行复杂计算，这时数据库将选择使用merge算法查询 mysql> select * from v2 where product_name='iPhone4'; +------------+--------------+---------------+ | product_id | product_name | product_price | +------------+--------------+---------------+ | 1 | iPhone4 | 4111.00 | +------------+--------------+---------------+ --merge算法将创建v2时的查询部分和现在这条对v2的查询语句进行拼接，得到最终查询语句 --（select * from t29 where product_id=1）和（select * from v2 where product_name='iPhone4'）拼接得到 --select * from t29 where product_id=1 and product_name='iPhone4';
● temptable算法
但有时，我们创建视图的时候语句复杂，比如v3(实际可能比这更加复杂)，对这种复杂视图查询时，数据库就可能使用temptable算法查询；
temptable算法将声明v3时的select语句先在内存中运行，得到一个结果集表，再对内存中的这个结果集表进行二次查询；
--对v3的avg列进行asc排序，asc排序将此列结果从小到大排列 mysql> select * from v3 order by product_id asc; +------------+--------------------+ | product_id | avg(product_price) | +------------+--------------------+ | 1 | 4555.430000 | | 2 | 5666.180000 | +------------+--------------------+ --使用temptable算法查询时，数据库先将创建v3时的select语句（select product_id,avg(product_price) from t29 group by product_id;）在内存中运行，得到结果集（一张查询后的结果表），然后对内存中的此表，再使用（select * from 内存中的表 order by product_id asc;）,得到最后的结果。
这就是不同复杂度下查询视图，数据库可能做出的两种查询算法选择。