MySQL知识（十七）——视图

1 视图概述

1.1 视图的含义

　　视图是从一个或者多个表中导出的，也可以从已经存在的视图基础上定义，视图的行为与表非常相似，但视图是一个虚拟表。在视图中用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE修改记录。视图可以使用户操作方便，而且可以保障数据库系统的安全。
　　视图一经定义便存储在数据库中，与其相应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。
　　当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

1.2 视图的作用

　　与直接从数据表中读取相比，视图有以下优点：
　　（1）简单化
　　视图呈现的数据就是需要的。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
　　（2）安全性
　　通过视图用户只能查询和修改他们所能见到的数据。
　　（3）逻辑数据独立性
　　视图可以帮助用户屏蔽真实表结构变化带来的影响。

2 创建视图

2.1 语法形式

创建视图使用CREATE VIEW语句：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW view_name[(column_list)]AS SELECT_statement[WITH [CASCADED|LOCAL] CHECK OPTION]]

• CREATE：创建新的视图
• REPLACE：替换已经创建的视图
• ALGORITHM：视图选择的算法
• view_name：视图名称
• column_list：属性列
• SELECT_statement：SELECT语句

• WITH [CASCADED|LOCAL] CHECK OPTION：视图在更新时保证在视图的权限范围之内

  　　ALGORITHM有3个取值：
  UNDEFINED：MySQL自动选择算法；
  MERGE：将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分
  TEMPTABLE：将视图的结果存入临时表，然后用临时表来执行语句

  　　CASCADED与LOCAL为可选参数：
  CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；
  LOCAL：更新视图满足该视图本省定义的条件即可

2.2 在单表上创建视图

mysql> CREATE TABLE t(quantity INT,price INT);Query OK, 0 rows affectedmysql> INSERT INTO t VALUES(3,50);Query OK, 1 row affectedmysql> CREATE VIEW view_t AS SELECT quantity,price,quantity*price FROM t;Query OK, 0 rows affectedmysql> SELECT * FROM view_t;+----------+-------+----------------+| quantity | price | quantity*price |+----------+-------+----------------+|        3 |    50 |            150 |+----------+-------+----------------+1 row in set

　　可以指定视图字段的名称：

mysql> CREATE VIEW view_t2(qty,price,total) AS SELECT quantity,price,quantity*price FROM t;Query OK, 0 rows affectedmysql> SELECT * FROM view_t2;+-----+-------+-------+| qty | price | total |+-----+-------+-------+|   3 |    50 |   150 |+-----+-------+-------+1 row in set

2.3 在多表上创建视图

（1）创建student表和stu_info表，并插入数据

mysql> CREATE TABLE student(  s_id  INT,  name  VARCHAR(40));Query OK, 0 rows affectedmysql> CREATE TABLE stu_info(  s_id   INT,  glass  VARCHAR(40),  addr   VARCHAR(90));Query OK, 0 rows affectedmysql> INSERT INTO student VALUES(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai'); Query OK, 3 rows affectedRecords: 3  Duplicates: 0  Warnings: 0mysql> INSERT INTO stu_info VALUES(1, 'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');Query OK, 3 rows affectedRecords: 3  Duplicates: 0  Warnings: 0

（2）创建视图

mysql> CREATE VIEW stu_glass(id,name,glass)    -> AS SELECT student.s_id,student.name,stu_info.glass    -> FROM student,stu_info    -> WHERE student.s_id=stu_info.s_id;Query OK, 0 rows affectedmysql> SELECT * FROM stu_glass;+----+----------+--------+| id | name     | glass  |+----+----------+--------+|  1 | wanglin1 | wuban  ||  2 | gaoli    | liuban ||  3 | zhanghai | qiban  |+----+----------+--------+3 rows in set

3 查看视图

3.1 DESCRIBE或DESC

mysql> DESC view_t;+----------------+------------+------+-----+---------+-------+| Field          | Type       | Null | Key | Default | Extra |+----------------+------------+------+-----+---------+-------+| quantity       | int(11)    | YES  |     | NULL    |       || price          | int(11)    | YES  |     | NULL    |       || quantity*price | bigint(21) | YES  |     | NULL    |       |+----------------+------------+------+-----+---------+-------+3 rows in set

3.2 SHOW TABLE STATUS

mysql> SHOW TABLE STATUS LIKE 'view_t' \G;*************************** 1. row ***************************           Name: view_t         Engine: NULL        Version: NULL     Row_format: NULL           Rows: NULL Avg_row_length: NULL    Data_length: NULLMax_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: VIEW1 row in set (0.00 sec)

Comment值为VIEW说明表为视图，其他信息为NULL说明这是一个虚表。

3.3 SHOW CREATE VIEW

mysql> SHOW CREATE VIEW view_t \G;*************************** 1. row ***************************                View: view_t         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t` AS select `t`.`quantity` AS `quantity`,`t`.`price` AS `price`,(`t`.`quantity` * `t`.`price`) AS `quantity*price` from `t`character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)

3.4 在views表中查看

　　在MySQL中，information_schema数据库下的views表中存储了所有视图的定义。

SELECT * FROM information_schema.views;

4 修改视图

　　修改数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。

4.1 CREATE OR REPLACE VIEW语句

修改视图的语句和创建视图的语句是完全一样的。

mysql> DESC view_t;+----------------+------------+------+-----+---------+-------+| Field          | Type       | Null | Key | Default | Extra |+----------------+------------+------+-----+---------+-------+| quantity       | int(11)    | YES  |     | NULL    |       || price          | int(11)    | YES  |     | NULL    |       || quantity*price | bigint(21) | YES  |     | NULL    |       |+----------------+------------+------+-----+---------+-------+3 rows in setmysql> CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;Query OK, 0 rows affectedmysql> DESC view_t;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| quantity | int(11) | YES  |     | NULL    |       || price    | int(11) | YES  |     | NULL    |       |+----------+---------+------+-----+---------+-------+2 rows in set

4.2 ALTER语句

语法跟创建视图的语句一样，只是将”CREATE OR REPLACE VIEW”改为”ALTER”。

mysql> ALTER VIEW view_t AS SELECT quantity FROM t;Query OK, 0 rows affectedmysql> DESC view_t;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| quantity | int(11) | YES  |     | NULL    |       |+----------+---------+------+-----+---------+-------+1 row in set

5 更新视图

　　更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对基本表增加或删除记录。

5.1 UPDATE语句

对视图更新，基本表的内容也会更新。

mysql> SELECT * FROM view_t;+----------+| quantity |+----------+|        3 |+----------+1 row in setmysql> SELECT * FROM t;+----------+-------+| quantity | price |+----------+-------+|        3 |    50 |+----------+-------+1 row in setmysql> UPDATE view_t SET quantity=5;Query OK, 1 row affectedRows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM t;+----------+-------+| quantity | price |+----------+-------+|        5 |    50 |+----------+-------+1 row in setmysql> SELECT * FROM view_t;+----------+| quantity |+----------+|        5 |+----------+1 row in setmysql> SELECT * FROM view_t2;+-----+-------+-------+| qty | price | total |+-----+-------+-------+|   5 |    50 |   250 |+-----+-------+-------+1 row in set

5.2 INSERT语句

对基本表进行插入数据，视图的内容也会跟着更新。（不能对视图进行插入操作）

mysql> INSERT INTO t VALUES(9,9);Query OK, 1 row affectedmysql> SELECT * FROM t;+----------+-------+| quantity | price |+----------+-------+|        5 |    50 ||        9 |     9 |+----------+-------+2 rows in setmysql> SELECT * FROM view_t2;+-----+-------+-------+| qty | price | total |+-----+-------+-------+|   5 |    50 |   250 ||   9 |     9 |    81 |+-----+-------+-------+2 rows in set

5.3 DELETE语句

删除视图的数据，相应的基本表的记录也会删除。

mysql> DELETE FROM view_t2 WHERE price=9;Query OK, 1 row affectedmysql> SELECT * FROM view_t2;+-----+-------+-------+| qty | price | total |+-----+-------+-------+|   5 |    50 |   250 |+-----+-------+-------+1 row in setmysql> SELECT * FROM t;+----------+-------+| quantity | price |+----------+-------+|        5 |    50 |+----------+-------+1 row in set

5.4 不能执行更新的视图

（1）视图中不包含基本表中被定义为非空的列
（2）在定义视图的SELECT语句后的字段列中使用了数学表达式
（3）在定义视图的SELECT语句后的字段列表中使用聚合函数
（4）在定义视图的SELECT语句中使用了DISTINCT，UNION，TOP，GROUP BY或HAVING子句

6 删除视图

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

　　例如，删除stu_glass的视图：

mysql> DROP VIEW IF EXISTS stu_glass;Query OK, 0 rows affectedmysql> SHOW CREATE VIEW stu_glass;1146 - Table 'test.stu_glass' doesn't exist

7 视图和表的区别