十、MySQL视图
数据库中的视图是一个虚拟表。同真实的表一样，视图包含一系列带有名称的行和列数据。行和列数据来自由定义视图查询所引用的表，并且在引用视图时动态生成。
创新互联服务紧随时代发展步伐，进行技术革新和技术进步，经过10余年的发展和积累，已经汇集了一批资深网站策划师、设计师、专业的网站实施团队以及高素质售后服务人员，并且完全形成了一套成熟的业务流程，能够完全依照客户要求对网站进行做网站、网站制作、建设、维护、更新和改版，实现客户网站对外宣传展示的首要目的，并为客户企业品牌互联网化提供全面的解决方案。
10.1、视图概述
视图是从一个或多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。在视图中用户可以使用SELECT语句查询，以及使用INSERT、UPDATE、DELETE修改记录。
视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。试图还可以从已存在的视图的基础上定义。视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份。通过视图看到的数据只是存在基本表的数据。
视图的主要优点有：
1. 视点集中 视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务。这样通过只允许用户看到视图中所定义的数据而不是视图引用表中的数据而提高了数据的安全性。 2. 简化操作 视图大大简化了用户对数据的操作。因为在定义视图时，若视图本身就是一个复杂查询的结果集，这样在每一次执行相同的查询时，不必重新写这些复杂的查询语句，只要一条简单的查询视图语句即可。可见视图向用户隐藏了表与表之间的复杂的连接操作。 3. 定制数据 视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要。 4. 合并分割数据 在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。 5. 安全性 视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它数据库或表既不可见也不可以访问。如果某一用户想要访问视图的结果集，必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。 |
10.2、创建视图
创建视图的语法
创建视图使用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表示视图选择的算法 UNDEFINED：MySQL自动选择算法 MERGE：将使用的视图语句与视图定义结合起来，使得视图定义的某一部分取代语句对应的部分 TEMPTABLE：将视图的结果存入临时表，然后用临时表来执行语句 |
| view_name为视图的名称，column_list为属性列 |
| select_statement表示SELECT语句 |
WITH [CASCADED | LOCAL] CHECK OPTION参数表示视图在更新时保证在视图的权限范围内 CASCADED：表示更新视图时要满足所有相关视图和表的条件 LOCAL：更新视图时满足该视图本身定义的条件即可 |
在单表上创建视图
在t表格上创建一个名为view_t的视图
mysql> CREATE TABLE t (qty INT, price INT); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t VALUES(3, 50); Query OK, 1 row affected (0.02 sec) mysql> CREATE VIEW view_t AS SELECT qty, price, qty *price FROM t; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM view_t; +------+-------+------------+ | qty | price | qty *price | +------+-------+------------+ | 3 | 50 | 150 | +------+-------+------------+ 1 row in set (0.00 sec)
在t表格上创建一个名为view_t2的视图
mysql> CREATE VIEW view_t2(qty, price, total ) AS SELECT qty, price, qty *price FROM t; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM view_t2; +------+-------+-------+ | qty | price | total | +------+-------+-------+ | 3 | 50 | 150 | +------+-------+-------+ 1 row in set (0.00 sec)
在多表上创建视图
在表student和表stu_info上创建视图stu_glass
mysql> CREATE TABLE student( id INT, name CHAR(11)); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE stu_info( -> id INT, -> name CHAR(11), -> glass CHAR(11) -> ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO student VALUES(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO stu_info VALUES(1, 'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','sh andong'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> CREATE VIEW stu_glass (id,name, glass) AS SELECT student.id,student.name ,stu_info. glass FROM student ,stu_info WHERE student.id=stu_info.id; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM stu_glass; +------+----------+----------+ | id | name | glass | +------+----------+----------+ | 1 | wanglin1 | henan | | 2 | gaoli | hebei | | 3 | zhanghai | shandong | +------+----------+----------+ 3 rows in set (0.00 sec)
10.3、查看视图
查看视图是查看数据库中已存在的视图的定义。查看视图必须有SHOW VIEW 的权限。查看视图的方法有DESCRIBE、SHOW TABLE STATUS、SHOW CREATE VIEW。
通过DESCRIBE语句查看视图view_t的定义
mysql> DESCRIBE view_t; +------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------+------+-----+---------+-------+ | qty | int(11) | YES | | NULL | | | price | int(11) | YES | | NULL | | | qty *price | bigint(21) | YES | | NULL | | +------------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
使用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: 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) ERROR: No query specified mysql> SHOW TABLE STATUS LIKE 't' \G; *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 16384 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 7340032 Auto_increment: NULL Create_time: 2017-08-04 19:38:50 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) ERROR: No query specified
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`.`qty` AS `qty`,`t`.`price` AS `price`,(`t`.`qty` * `t`.`price`) AS `qty *price` from `t` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) ERROR: No query specified
在views表中查看视图的详细定义
mysql> SELECT * FROM information_schema.views \G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: stu_glass VIEW_DEFINITION: select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name`,`test`.`stu_info`.`glass` AS `glass` from `test`.`student` join `test`.`stu_info` where (`test`.`student`.`id` = `test`.`stu_info`.`id`) CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: view_t VIEW_DEFINITION: select `test`.`t`.`qty` AS `qty`,`test`.`t`.`price` AS `price`,(`test`.`t`.`qty` * `test`.`t`.`price`) AS `qty *price` from `test`.`t` CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci *************************** 3. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: view_t2 VIEW_DEFINITION: select `test`.`t`.`qty` AS `qty`,`test`.`t`.`price` AS `price`,(`test`.`t`.`qty` * `test`.`t`.`price`) AS `total` from `test`.`t` CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci 3 rows in set (0.00 sec) ERROR: No query specified
10.4、修改视图
修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。MySQL通过CREATE OR REPLACE VIEW语句和ALTER语句修改视图。
使用CREATE OR REPLACE VIEW的基本语法为：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]修改视图view_t
mysql> DESC view_t; +------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------+------+-----+---------+-------+ | qty | int(11) | YES | | NULL | | | price | int(11) | YES | | NULL | | | qty *price | bigint(21) | YES | | NULL | | +------------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> CREATE OR REPLACE VIEW view_t AS SELECT * FROM t; Query OK, 0 rows affected (0.07 sec) mysql> DESC view_t; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | qty | int(11) | YES | | NULL | | | price | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
ALTER语句是MySQL提供的另一种修改视图的方法，其语法格式为：
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]使用ALTER语句修改视图view_t
mysql> DESC view_t; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | qty | int(11) | YES | | NULL | | | price | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ALTER VIEW view_t AS SELECT qty FROM t; Query OK, 0 rows affected (0.01 sec) mysql> DESC view_t; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | qty | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
10.5、更新视图
更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的。
使用UPDATE语句更新视图view_t
mysql> SELECT * FROM view_t; /*查看更新之前的视图*/ +------+ | qty | +------+ | 3 | +------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t; /*查看更新之前的表*/ +------+-------+ | qty | price | +------+-------+ | 3 | 50 | +------+-------+ 1 row in set (0.00 sec) mysql> UPDATE view_t SET qty=5; /*更新视图*/ Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM t; /*查看更新之后的表*/ +------+-------+ | qty | price | +------+-------+ | 5 | 50 | +------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM view_t; /*查看更新之后的视图*/ +------+ | qty | +------+ | 5 | +------+ 1 row in set (0.00 sec) mysql> SELECT * FROM view_t2; +------+-------+-------+ | qty | price | total | +------+-------+-------+ | 5 | 50 | 250 | +------+-------+-------+ 1 row in set (0.00 sec)
使用INSERT语句在基本表t中插入一条记录
mysql> INSERT INTO t VALUES (3,5); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM t; +------+-------+ | qty | price | +------+-------+ | 5 | 50 | | 3 | 5 | +------+-------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM view_t2; +------+-------+-------+ | qty | price | total | +------+-------+-------+ | 5 | 50 | 250 | | 3 | 5 | 15 | +------+-------+-------+ 2 rows in set (0.00 sec)
当视图中包含以下内容时，视图的更新操作将不能执行：
视图中不包含基表中被定义为非空的列； 在定义视图的SELECT语句后的字段列表中使用了数学表达式； 在定义视图的SELECT语句后的字段列表中使用聚合函数； 在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY或HAVING子句。 |
10.6、删除视图
当视图不再需要时，可以将其删除，其语法格式为：
DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]
删除stu_glass视图
mysql> DROP VIEW IF EXISTS stu_glass; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE VIEW stu_glass; ERROR 1146 (42S02): Table 'test.stu_glass' doesn't exist
新闻标题：十、MySQL视图
URL标题：http://cdiso.cn/article/iicpsg.html
