mysql视图的使用

一、视图的含义：

　　　　视图是一张虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

二、为什么使用视图：

        1）重用SQL语句    

                    简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节

        2）使用表的组成部分而不是整个表。

        3）保护数据    

                    可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

        4）更改数据格式和表示

                    视图可返回与底层表的表示和格式不同的数据。

注意：

• 重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。
• 与表一样，视图必须唯一命名唯一，还需要保证自己有足够的权限支持创建视图，这些规则基本是由数据库管理员授予的，视图可以嵌套，即    可以利用从其他视图中检索的数据的查询构造一个视图。
• order by可以用在视图中，但如果从该视图检索数据select中也含有order by，那么该视图的order by将被覆盖。
• 视图不支持索引，也不能有关联的触发器或默认值。
• 视图可以和表一起使用。例如：编写一个联结表和视图的select语句。

创建视图：

查询最近30天的成交金额，按已支付和未支付展示

语法：

        CREATE ALGORITHM = UNDEFINED | MERGE | TEMPTABLE

         VIEW   视图名   ( 属性清单 )

         AS   SELECT   语句

         WITH   CASCADED | LOCAL   CHECK   OPTION;

　　ALGORITHM是可选参数，表示视图选择的算法。

　　　　UNDEFINED：表示mysql将自动选择所使用的算法；

　　　　MERGE：表示将使用的视图语句与视图定义结合起来，使得视图定义的某一部分取代语句的对应部分；

　　　　TEMPTABLE：表示将视图的结果存入临时表，然后使用临时表执行语句。

　　属性清单是可选参数，指定视图中各个属性的名词。默认情况下与SELECT语句中查询的属性相同。

　　SELECT语句是一个完整的查询语句，表示从某个表查询满足条件的记录。

　　WITH CHECK OPTION是可选参数，表示更新视图时要保证在该视图的权限范围内。

　　　　CASCADED：表示更新视图时要满足所有相关视图和表的条件；

　　　　LOCAL：表示更新视图时要满足视图本身的定义条件即可。

　　注意：创建视图分为：1）从单表创建视图；2）从多表创建视图。

实例：

create VIEW aff_order AS
SELECT case pay_status 
when 1 THEN '已支付' 
WHEN 2 THEN '未支付' 
END AS 状态, 
FROM_UNIXTIME(ctime, '%Y-%m-%d') AS order_day, 
sum(order_amount) / 100 AS 合计 
FROM `aff_order` 
GROUP BY pay_status,order_day 
HAVING order_day < FROM_UNIXTIME( (UNIX_TIMESTAMP() - 86400 *30 ), '%Y-%m-%d' ) 
ORDER BY ctime

查看视图

DESCRIBE   视图名;

SHOW  TABLE  STATUS  LIKE   '视图名';

SHOW  CREATE  VIEW   视图名;
SELECT * FROM  information_schema.views;

　　前两种为查看视图的基本信息，后两种为查看视图的详细信息。

 

修改视图

　　修改视图指修改视图中包含的表的字段。

　　1）CREATE OR REPLACE VIEW修改视图

                      CREATE   OR  REPLACE  ALGORITHM = UNDEFINED | MERGE | TEMPTABLE

                      VIEW   视图名   ( 属性清单 )

                      AS   SELECT   语句

                      WITH   CASCADED | LOCAL   CHECK   OPTION;

　　2）ALTER语句

                    ALTER   ALGORITHM = UNDEFINED | MERGE | TEMPTABLE

                    VIEW   视图名   ( 属性清单 )

                    AS   SELECT   语句

                    WITH   CASCADED | LOCAL   CHECK   OPTION;

　　注意：

　　　　CREATE OR REPLACE VIEW可以修改现有的视图，也可以创建视图。而ALTER只能修改视图。

 

更新视图

　　更新视图指通过视图来插入、更新和删除表中的数据。

　　因为视图是一个虚拟的表，其中没有数据。通过视图更新都是切换到基本表来更新。并且只能更新权限范围内的数据，超出范围则不能更新。

　　可以通过INSERT、UPDATE、DELETE更新。

　　注意：

　　　　以下这几种情况不能更新视图：

　　　　（1）视图中保护SUM()、COUNT()、MAX()、MIN()等函数；

　　　　（2）视图中包含UNION、UNION ALL、DISTINCT、GROUP BY、HAVING等关键字；

　　　　（3）常量视图；

　　　　（4）视图中的SELECT包含子查询；

　　　　（5）由不可更新的视图导出的视图；

　　　　（6）创建视图时，ALGORITHM为TEMPTABLE类型；

　　　　（7）视图对应的表上存在没有默认值的列，而且该列没有包含在视图中。

 

删除视图

              DROP  VIEW  [IF EXISTS]   视图名列表   [RESTRICT | CASCADE];

 

视图的缺点：

（1） 视图不能创建索引

（2）性能差　　 
     sql server必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，sql server也要把它变成一个复杂的结合体，需要花费一定的时间。 
（3）修改限制　　 
     当用户试图修改试图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的试图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的

 

其他：

mysql的视图不是一种物化视图，它相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查出来的。这带来的问题是使用视图并不能将常用数据分离出来，优化查询速度。
且操作视图的很多命令都与普通表一样，这会导致在业务代码中无法通过sql区分表和视图，使代码变得复杂。
      实现视图的方式有两种，分别为合并算法和临时表算法，合并算法是指查询视图时将视图定义的sql合并到查询sql中，比如create view v1 as select * from user where sex=m;当我们要查询视图时，mysql会将select id,name from v1;合并成select id,name from user where sex=m……

临时表算法是先将视图查出来的数据保存到一个临时表中，查询的时候查这个临时表。不管是合并算法和临时表算法都会带来额外的开销，;且如果使用临时表后会使mysql的优化变得很困难，比如索引。
而且视图还引入了一些其他的问题，使得其背后的逻辑非常复杂。
当然，视图在某些情况下可以帮助提升性能，但视图的性能很难预测。且在mysql的优化器中，视图的代码执行路径也完全不同，无法直观的预测其执行性能。