mysql中的视图

1、介绍

视图是一种虚拟表，从一张或者多张表中导出来的表，所以视图也有由表的行和列属性，区别在于，数据库只存放了视图的定义（查询的sql 语句），并没有存放视图中的数据，数据还存放在原来的表中。示例图如下：

图片来源网络

2、视图的原理

2.1 视图的作用

1、提高了重用性，就像一个函数。将一些复杂繁琐的查询结果作为视图，可简化查询。
2、提供安全性，可以屏蔽原始表中不暴露的字段。
3、在数据库表结构调整重构时候，视图使得调用程序不会应为调整原始表而报错。

2.1 视图的优缺点

• 优点

1、视图
2、视图提供额外的安全层。

• 缺点

1、性能问题，从数据库视图查询数据可能会很慢，尤其如果视图是基于其他视图创建的。

3、视图实战

3.1 创建视图的语法如下：

create  [algorithm = {undefined | merge | temptable }] view  视图名 [ (属性清单)]  
as select 语句 [with [cascade | local] check option]

algorithm 可选参数，表示视图的算法

3.2、查看视图

（1） describe 方式

describe  视图名称;

mysql> desc class_view;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(10)     | NO   |     | NULL    |       |
| class_name  | varchar(20) | NO   |     | NULL    |       |
| class_num   | varchar(10) | NO   |     | NULL    |       |
| create_time | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

（2） show table status语句查看视图

该方式查看视图很少使用，原因只能通过Comment查看其他属性都为NULL没什么实质意义。

show  table  status  like  '视图'；

mysql> show table status like 'class_view' \G;
*************************** 1. row ***************************
           Name: class_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

ERROR: 
No query specified

其中，Comment:VIEW说明该表为视图，其他信息(存储引擎，创建时间等)为NULL，说明是虚拟表。

（3）show create view 查看视图

show create view '视图名'

mysql> show create view class_view \G;
*************************** 1. row ***************************
                View: class_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `class_view` AS select `class`.`id` AS `id`,`class`.`class_name` AS `class_name`,`class`.`class_num` AS `class_num`,`class`.`create_time` AS `create_time` from `class`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

该方式可以查看到所有视图信息，包括编码：character_set_client:utf8。

（4）通过 views视图表查看
information_schema 中views表中存放了所有视图信息。

mysql> select * from information_schema.views \G;
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: mysel_finance
          TABLE_NAME: class_view
     VIEW_DEFINITION: select `mysel_finance`.`class`.`id` AS `id`,`mysel_finance`.`class`.`class_name` AS `class_name`,`mysel_finance`.`class`.`class_num` AS `class_num`,`mysel_finance`.`class`.`create_time` AS `create_time` from `mysel_finance`.`class`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*************************** 2. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sys
          TABLE_NAME: host_summary
     VIEW_DEFINITION: select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) AS `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,sum(`stmt`.`full_scans`) AS `table_scans`,sum(`io`.`ios`) AS `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) AS `file_io_latency`,sum(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,sum(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,count(distinct `performance_schema`.`accounts`.`USER`) AS `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) AS `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) AS `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`HOST` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`HOST` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: mysql.sys@localhost
       SECURITY_TYPE: INVOKER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci

其中，1.row下的内容 就是我们最新建立的视图信息。

3.3 修改视图语法

当基本表字段修改之后，需要对已经存在的视图可以进行修改。
（1）Create or replace方式
如果视图存在则修改，不存在则新增视图，和创建视图格式一致。这里是修改视图内容不修改视图名称

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];

3.4、 删除视图

删除视图，只会删除视图在数据库中存在的定义，不会删除原表数据。

drop view  [if exists] 视图名列表 [ restrict| cascade ]

其中 if exists 判断视图是否存在，如果存在则执行，不存在则不执行删除。视图名列表，要被删除的视图名的列别，多个视图名之间用逗号隔开。

4、更新视图数据

更新视图，指的是通过视图来更新（update）、插入（insert）和删除（delete）原表中的数据。其语法和正常表格操作一致，只是更新视图数据更新的对应的原始表数据。而更新表数据直接更新对应数据。
以下情况是无法操作更新视图数据的：
（1）视图中包含sum(),count(),max()和min()等函数。
（2）视图中包含union、union all、distinct、group by、having等关键字。
（3）常量视图。
（4）视图中的select 语句包含子查询。
（5）由不可更新的视图导出的视图。
（6）创建视图时，algorithm = temptable
（7）视图对应的表存在没有默认值的列，而且该列没有在视图里。