03.视图

3.1 视图的作用

• 视图是一个或多个表的数据的逻辑显示，视图不存储数据，占用很少空间

  • 视图建立在已有表的基础上，称这些依赖的表为基表

  • 视图的创建删除操作只影响自身，不影响基表

  • 对视图增删查改操作对基表会造成影响

  • 视图提供数据内容的语句为查询语句，可以将视图理解为存储起来的SELECT语句

  • 要使视图可以更新，需要让视图中的行和表中数据是一对一的关系，绝大多数的视图都无法更新,即不支持对视图增删改操作

    MySQL

    视图可更新情况可能根据数据库版本发生变化，见MySQL官方文档视图更新部分

    • 如果需要插入数据，需要确保基表中除了有默认值的列都已列出，且默认值不包含AUTO_INCREASE，因为对视图更新不会改变LAST_INSERT_ID()值

    视图具有的特点	INSERT	UPDATE	DELETE
    指定了ALGORITHM=TEMPTABLE	❌	☑️	❌
    有查询列是表达式或子查询	❌	❌	☑️
    有查询列是聚合函数	❌	❌	❌
    查询列全为常量	❌	❌	❌
    查询定义中有GROUP BY	❌	❌	❌
    查询定义中有UNION	❌	❌	❌
    查询定义中有DISTINCT	❌	❌	❌
    查询中嵌套了对基表进行子查询	❌	❌	❌
    由多个基本表JOIN导出	❌	☑️	❌
    在以上情况的视图之上定义的视图	❌	❌	❌

  • 小型项目一般不推荐使用视图，大型项目可以使用视图

  • 优点：视图可以简化查询、控制输出的方式、访问限制

  • 不足：如果修改了表结构，还需要修改对应的视图，增加了维护成本，可读性不好

3.2 基本操作

• 创建一个视图

  MySQL

  -- 简单定义
  -- 使用后,在对视图执行增删改操作时要满足子查询中的条件表达式
  CREATE VIEW 视图名(字段列表)
  AS SELECT语句;
  
  -- 完整定义
  -- CREATE OR REPLACE 表示如果存在则覆盖原有视图
  -- ALGORITHM 是设置视图的创建方式，默认UNDEFINED是自动根据情况选择
  -- MERGE：会将查询视图的语句和创建视图时的查询语句进行优化组合，对基表查询，聚合、UNION和子查询不支持MERGE，会切换并产生警告
  -- TEMPTABLE：首先通过创建视图时定义的查询语句在基表上查询，之后将查询结果存入临时表，之后在临时表上执行查询视图的SQL语句并返回结果
  -- WITH CHECK OPTION 用于限制视图更新操作，只有满足视图定义中的条件的数据才能被插入到视图
  CREATE [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  VIEW 视图名称 [(字段列表)]
  AS [SELECT ...]
  [WITH [CASCADE|LOCAL] CHECK OPTION]

• 查询视图信息

  MySQL

  -- 可以通过查询所有表的方式查看到视图
  SHOW TABLES;
  
  -- 可以通过视图名查看视图中列定义信息
  DESC vu_employees;
  
  -- 可以像查看表状态一样查看视图状态
  SHOW TABLE STATUS LIKE 'vu_employees'\G
  SHOW CREATE TABLE vu_employees;

• 修改视图

  MySQL

  -- 方法1
  CREATE OR REPLACE VIEW vu_employees AS SELECT * FROM employees;
  
  -- 方法2
  ALTER VIEW vu_employees AS SELECT * FROM employees;

• 删除视图

  MySQL

  DROP VIEW 视图名1, 视图名2, ...;
  
  -- 希望不存在不报错
  DROP VIEW IF EXISTS 视图名1, 视图名2, ...;