本教程以MySQL8为主版本(内容兼顾MySQL5.7)。
所有MySQL文章的目录为:总目录 https://blog.csdn.net/zyplanke/article/details/102968014
熟悉SQL都知道视图(View) 是一种虚拟表,实际就是预先定义好的select查询SQL语句。通过视图,对使用者来说,就像使用一张表一样简单。视图在数据库内部自身并不保存数据,数据都是来源于它背后的基表。
使用视图,而不直接访问基表,主要的原因是:
视图的创建语法为:
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]
下面以articles为基表,在database(schema)=test下,创建名为view_a的视图。视图的字段同基表:
mysql> create view test.view_a as select * from articles;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test.view_a;
+----+-----------------------+------------------------------------------+
| id | title | body |
+----+-----------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
| 7 | 中文标题 | 中文内容:MySQL教程学习等等 |
+----+-----------------------+------------------------------------------+
7 rows in set (0.00 sec)
还可以这样:
mysql> create view test.to_date as select current_date();
Query OK, 0 rows affected (0.01 sec)
mysql> select * from to_date;
+----------------+
| current_date() |
+----------------+
| 2019-11-15 |
+----------------+
1 row in set (0.00 sec)
WITH [CASCADED | LOCAL] CHECK OPTION 的使用场景是多层视图时。允许update视图时,检查视图定义SQL语句中where条件满足性。在MySQL 5.7.6之前
WHERE是否还满足
,并递归检查基础视图WHERE是否还满足
,但不递归检查基础视图WHERE是否还满足
,也不递归检查基础视图从MySQL 5.7.6开始,后两个的处理方式进行了调整:
WHERE是否还满足
,还要递归检查基础视图。WHERE是否还满足
,但是要递归检查基础视图show tables不仅可以列出表名,还能列出视图名(不存在单独的 show views命令):
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| TB |
| articles |
| autoinc_test |
| cities |
| cities_hash |
| class |
| employee |
| student |
| t1 |
| t2 |
| t3 |
| tab_mem |
| view_a |
| vm_articles |
| vm_articles1 |
| vm_articles2 |
查看视图的定义SQL语句,既可以使用show create table也可以用show create view:
mysql> show create table view_a;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_a | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_a` AS select `articles`.`id` AS `id`,`articles`.`title` AS `title`,`articles`.`body` AS `body` from `articles` | utf8mb4 | utf8mb4_general_ci |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> show create view view_a;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_a | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_a` AS select `articles`.`id` AS `id`,`articles`.`title` AS `title`,`articles`.`body` AS `body` from `articles` | utf8mb4 | utf8mb4_general_ci |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
修改视图的语法,同创建视图的语法,只需要加上OR REPLACE。 既类似于CREATE OR REPLACE.....
删除视图很简单,如下。(虽然可以一次删除多个视图,但不建议。还是一个一个删除比较好):
mysql> drop view test.to_date;
Query OK, 0 rows affected (0.00 sec)