视图

• 特点
  • 优点
    • 简化查询
    • 提供独立访问
    • 限制访问
  • 查看时，生成sql查看, 有即时性
  • 更新视图表级联
    • 不能更新的视图包含元素
      • 组函数
      • distinct
      • group by
      • rownum
  • grant可授权
  • 不能与表名重名
  • mysql保存在information_schema.views中
  • 删除不放回收站

• CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    # TEMPTABLE是临时表，不能更新
      VIEW view_name [(column_list)]
      AS select_statement
      [WITH [CASCADED | LOCAL] CHECK OPTION]
        # 通过该视图更改的数据，可再通过该视图查看到(满足视图的限制)
   
  o->
  CREATE VIEW test.v AS SELECT * FROM t;
  o->
  CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;                # 可以计算
  o->
  CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));                # 可以联合
  o->
  CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2)
      AS SELECT c1, c2 FROM t1 WHERE c3 > 100
   
      查询解释
          SELECT * FROM v_merge WHERE vc1 < 100; 会执行
          SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
   
   
  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
      VIEW view_name [(column_list)]
      AS select_statement
      [WITH [CASCADED | LOCAL] CHECK OPTION]              # CASCADED 忽略主外键关系
   
  DROP VIEW [IF EXISTS]
      view_name [, view_name] ...
      [RESTRICT | CASCADE]
   
  SHOW CREATE VIEW view_name