视图

视图：存储下来的SELECT语句

创建视图

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

mysql> select s.name,c.course,t.name as tname from student as s,courses as c,teacher as t where s.cid=c.cid and c.tid=t.tid;
+-------+------------+------------+
| name  | course     | tname      |
+-------+------------+------------+
| jim   | Chinese    | tangyouyou |
| tom   | English    | tomxu      |
| lucy  | English    | tomxu      |
| jack  | math       | yuehan     |
| neccy | math       | yuehan     |
| suke  | math       | yuehan     |
| mary  | computer   | tangyouyou |
| kaka  | biological | caiyuanpei |
+-------+------------+------------+
8 rows in set (0.01 sec)




mysql> create view sct as select s.name,c.course,t.name as tname from student as s,courses as c,teacher as t where s.cid=c.cid and c.tid=t.tid;
Query OK, 0 rows affected (0.01 sec)



mysql> select * from sct;
+-------+------------+------------+
| name  | course     | tname      |
+-------+------------+------------+
| jim   | Chinese    | tangyouyou |
| tom   | English    | tomxu      |
| lucy  | English    | tomxu      |
| jack  | math       | yuehan     |
| neccy | math       | yuehan     |
| suke  | math       | yuehan     |
| mary  | computer   | tangyouyou |
| kaka  | biological | caiyuanpei |
+-------+------------+------------+
8 rows in set (0.00 sec)

查看视图的状态

mysql> show table status like 'sct'\G
*************************** 1. row ***************************
           Name: sct
         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)

插入数据

一般不建议插入数据，如果在视图中插入数据，需要将基表修改。例如在sct视图中插入数据，会在student、courses、teacher中插入数据。如果在student中有字段不允许为空，如果强行插入数据，就会报错。

删除视图

Syntax:
DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

mysql> drop view sct;
Query OK, 0 rows affected (0.00 sec)

物化视图

视图不会创建索引，查询比较缓慢，由于每一次查询都要针对一个内在的语句进行缓存，如果缓存较大就非常消耗资源。避免此类问题可以将视图保存到本地。

优点：减少内在语句的查询，减少消耗
缺点：基表查询需要更新
使用场景：基表变化比较少的情况。

查看view的创建过程

mysql> show create view sct\G
*************************** 1. row ***************************
                View: sct
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sct` AS select `s`.`name` AS `name`,`c`.`course` AS `course`,`t`.`name` AS `tname` from ((`student` `s` join `courses` `c`) join `teacher` `t`) where ((`s`.`cid` = `c`.`cid`) and (`c`.`tid` = `t`.`tid`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)