MySQL视图

什么是视图

视图时一个或多个表中导出来的结果，是一种虚拟存在的表，但视图并不在数据库中以存储数据值的形式存在，行和列数据来自定义视图的查所引用的基本表，并且在具体引用视图时动态生成。

视图的特点

• 视图的列可以来自不同的表，时表的抽象的逻辑意义上建立的新关系。
• 视图是由基本表（实表）产生的表（虚表）。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新（添加、删除和修改）直接影响基本表。
• 当视图来自多个基本表时，不允许添加和删除数据。

何时使用视图

有时我们需要查询多张表中的字段，查询和书写很麻烦；或从单个表中提取某些字段，针对某些账号开发；这个时候就可以考虑创建视图。

环境

MySQL： 8.0.16 

创建视图

• 创建视图语句

  CREATE
      [OR REPLACE]
      [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
      [DEFINER = user]
      [SQL SECURITY { DEFINER | INVOKER }]
      VIEW view_name [(column_list)]
      AS select_statement
      [WITH [CASCADED | LOCAL] CHECK OPTION]

   

• 视图参数

  CREATE表示创建视图。
  REPLACE表示替换已创建视图。
  ALGORITHM表示视图算法。取值一共3个，分别是UNDEFINED、MERGE、TEMPLATE。默认是是UNDEFINED，表示自动选择算法；MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPLATE表示将视图的结果存入临时表，然后让临时表来执行语句。
  viewname表示视图名称。
  columnlist表示属性列。
  select_statement表示SELECT语句。
  WITH [CASCADED | LOCAL] CHECK OPTION表示视图在更新时保证在视图的权限范围之内。CASCADED位默认值，表示更新视图时要满足所有的相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可。
  

   

• 单表上创建视图

  MySQL [test]> CREATE VIEW view_user AS SELECT realname,age FROM user;
  Query OK, 0 rows affected (0.03 sec)
  
  MySQL [test]> SELECT * FROM view_user;
  +----------+-----+
  | realname | age |
  +----------+-----+
  | 张三     |  20 |
  | 李四     |  22 |
  | 王二     |  24 |
  +----------+-----+
  3 rows in set (0.00 sec)

   

查看视图

• 使用DESCRIBE|DESC语句查看视图基本信息

  DESCRIBE | DESC view_user;

   

• 使用SHOW TABLES语句查看视图

  SHOW TABLES;

   

• 在views表中查看视图信息

  MySQL [test]> SELECT * FROM information_schema.VIEWS WHERE TABLE_NAME = 'view_user'\G;
  *************************** 1. row ***************************
         TABLE_CATALOG: def
          TABLE_SCHEMA: test
            TABLE_NAME: view_user
       VIEW_DEFINITION: select `test`.`user`.`realname` AS `realname`,`test`.`user`.`age` AS `age` from `test`.`user`
          CHECK_OPTION: NONE
          IS_UPDATABLE: YES
               DEFINER: root@127.0.0.1
         SECURITY_TYPE: DEFINER
  CHARACTER_SET_CLIENT: utf8mb4
  COLLATION_CONNECTION: utf8mb4_0900_ai_ci
  2 rows in set (0.01 sec)
  

   

修改视图

• 修改视图是指修改数据库中存在视图，但基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。MySQL中通过CREATE OR REPLACE VIEW和ALTER语句来修改视图。
• 使用CREATE OR REPLACE VIEW语句修改视图

  MySQL [test]> CREATE OR REPLACE VIEW view_user AS SELECT id,realname,age FROM `user`;
  Query OK, 0 rows affected (0.01 sec)
  
  MySQL [test]> SELECT * FROM view_user;
  +----+----------+-----+
  | id | realname | age |
  +----+----------+-----+
  |  1 | 张三     |  20 |
  |  2 | 李四     |  22 |
  |  3 | 王二     |  24 |
  +----+----------+-----+
  3 rows in set (0.00 sec)

   

•  使用ALTER语句修改视图

  MySQL [test]> ALTER VIEW view_user AS SELECT id,nickname,realname,age FROM `user`;
  Query OK, 0 rows affected (0.03 sec)
  
  MySQL [test]> SELECT * FROM view_user;
  +----+----------+----------+-----+
  | id | nickname | realname | age |
  +----+----------+----------+-----+
  |  1 | 小张     | 张三     |  20 |
  |  2 | 小李     | 李四     |  22 |
  |  3 | 小王     | 王二     |  24 |
  +----+----------+----------+-----+
  3 rows in set (0.00 sec)

   

更新视图

• 更新视图是指通过视图来插入(INSERT)、更新（UPDATE）和删除（DELETE）表中的数据。因为视图时一张虚表，其中没有数据，通过视图更新时都是转换到基本表中更新。更新只能在权限范围内更新才能成功。
• 使用SQL更新视图

  MySQL [test]> UPDATE view_user SET nickname='大张' WHERE id = 1;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  MySQL [test]> SELECT * FROM view_user;
  +----+----------+----------+-----+
  | id | nickname | realname | age |
  +----+----------+----------+-----+
  |  1 | 大张     | 张三     |  20 |
  |  2 | 小李     | 李四     |  22 |
  |  3 | 小王     | 王二     |  24 |
  +----+----------+----------+-----+
  3 rows in set (0.00 sec)

   

•  更新基本表之后视图自动更新

  MySQL [test]> INSERT INTO `user`(nickname,realname,age) VALUES('小麻','麻子',18);
  Query OK, 1 row affected (0.00 sec)
  
  MySQL [test]> SELECT * FROM view_user;
  +----+----------+----------+-----+
  | id | nickname | realname | age |
  +----+----------+----------+-----+
  |  1 | 大张     | 张三     |  20 |
  |  2 | 小李     | 李四     |  22 |
  |  3 | 小王     | 王二     |  24 |
  |  4 | 小麻     | 麻子     |  18 |
  +----+----------+----------+-----+
  4 rows in set (0.00 sec)

   

• 不能更新视图

  • 视图中包含SUM()、COUNT()、MAX()和MIN()等聚合函数。

  •  视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字。

  • 常量视图。

    CREATE VIEW view_constant AS SELECT 'lewis' AS name;

     

  • 包含子查询视图。

  • 由不可更新的视图导出的视图。

  • 创建视图时，ALOGORITHM为TEMPTABLE类型。

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

  • WITH [CASCADED | LOCAL] CHECK OPTION也将决定视图是否更新。参数LOCAL表示更新视图时要满足该视图本身定义的条件即可；参数CASCADED表示更新视图时要满足所有相关视图的表的条件。

删除视图

• 删除视图指删除数据库中已存在的视图，只能删除定义视图，不能删除数据。
• 使用DROP VIEW语句来删除。

  MySQL [test]> DROP VIEW view_user;
  Query OK, 0 rows affected (0.02 sec)
  
  MySQL [test]> SELECT * FROM view_user;
  ERROR 1146 (42S02): Table 'test.view_user' doesn't exist