MySQL中的视图

2020年3月26日
| 没有评论
摘要：MySQL中的视图

MySQL支持视图，包括可更新的视图。视图是存在库里的SELECT语句，被调用时即时生成查询结果。可以把视图当虚拟表来使用。

使用CREATE VIEW创建视图，ALTER VIEW修改视图，DROP VIEW删除视图。

root@database-one 23:48:  [gftest]> CREATE TABLE testv (qty INT, price INT);
Query OK, 0 rows affected (0.00 sec)

root@database-one 23:48:  [gftest]> INSERT INTO testv VALUES(3, 50), (5, 60);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@database-one 23:49:  [gftest]> CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM testv;
Query OK, 0 rows affected (0.00 sec)

root@database-one 23:50:  [gftest]> SELECT * FROM testv;
+------+-------+
| qty  | price |
+------+-------+
|    3 |    50 |
|    5 |    60 |
+------+-------+
2 rows in set (0.01 sec)

root@database-one 23:50:  [gftest]> SELECT * FROM v1;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
|    5 |    60 |   300 |
+------+-------+-------+
2 rows in set (0.00 sec)

root@database-one 23:50:  [gftest]> alter view v1 as select qty, price, qty*price AS value,now() as order_date from testv;
Query OK, 0 rows affected (0.00 sec)

root@database-one 23:53:  [gftest]> SELECT * FROM v1;
+------+-------+-------+---------------------+
| qty  | price | value | order_date          |
+------+-------+-------+---------------------+
|    3 |    50 |   150 | 2020-03-25 23:53:36 |
|    5 |    60 |   300 | 2020-03-25 23:53:36 |
+------+-------+-------+---------------------+
2 rows in set (0.00 sec)

root@database-one 23:53:  [gftest]> drop view v1;
Query OK, 0 rows affected (0.01 sec)

 

MySQL 5.7创建视图的完整语法

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

关键字说明见参加官方文档
https://dev.mysql.com/doc/refman/5.7/en/create-view.html

MySQL 5.7修改视图的完整语法

ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

关键字说明见参加官方文档
https://dev.mysql.com/doc/refman/5.7/en/alter-view.html

MySQL 5.7删除视图的完整语法

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

关键字说明见参加官方文档
https://dev.mysql.com/doc/refman/5.7/en/drop-view.html

视图可以基于各种select语句，可以基于join、union、子查询等，可以基于表或者其它视图。

有些视图是可更新的，也就是说，可以在UPDATE、DELETE或INSERT等语句中通过视图来更新基础表的内容。具体如何判断哪些视图可以更新哪些不可以呢？
MySQL 5.7中，视图包含任何一种下面情况就不能更新：

  • 聚合函数，比如SUM()、MIN()、MAX()、COUNT()等等
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION或UNION ALL
  • JOIN
  • FROM包含不可更新视图
  • 关联子查询（WHERE中的子句包含了FROM的表）
  • SELECT中包含子查询
  • 常量视图
  • ALGORITHM = TEMPTABLE
  • 对基表任何列的多个引用（fails for INSERT, okay for UPDATE, DELETE）

要了解视图的元数据，比如视图定义语句等，可以用SHOW CREATE VIEW或者通过INFORMATION_SCHEMA查询。

root@database-one 00:37:  [gftest]> CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM testv;
Query OK, 0 rows affected (0.01 sec)

root@database-one 00:37:  [gftest]> SELECT * FROM v1;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
|    5 |    60 |   300 |
+------+-------+-------+
2 rows in set (0.00 sec)

root@database-one 00:38:  [gftest]> show create view v1 \G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v1` AS select `testv`.`qty` AS `qty`,`testv`.`price` AS `price`,(`testv`.`qty` * `testv`.`price`) AS `value` from `testv`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

root@database-one 00:39:  [gftest]> select * from information_schema.views where table_schema='GFTEST' and table_name='V1' \G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: gftest
          TABLE_NAME: v1
     VIEW_DEFINITION: select `gftest`.`testv`.`qty` AS `qty`,`gftest`.`testv`.`price` AS `price`,(`gftest`.`testv`.`qty` * `gftest`.`testv`.`price`) AS `value` from `gftest`.`testv`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

 

© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

MySQL
| Tags: ,