MySQL 5.7（七）【视图】

视图

一、视图的特点

（一）视图定义

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。

视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

（二）视图作用

1、简单化

操作简单，查询速度快，更新方便。

2、安全性

通过视图，用户可以被限制在数据的不同子集上：
① 使用权限可以被限制在基表的行的子集上、列的子集上、行和列的子集上；
② 使用权限可以被限制在多个基表的连接所限定的行上；
③ 使用权限可以被限制在基表中的数据的统计汇总上；
④ 使用权限可以被限制在另一个视图的一个子集上，或是一些视图和基表合并后的子集上。

3、逻辑数据独立性

视图可以帮助用户屏蔽真实表结构变化带来的影响。

二、创建视图

语法结构

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 取值分别是 UNDEFINED | MERGE | TEMPTABLE
UNDEFINED：表示MySQL将自动选择算法；
MERGE：表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；
TEMPTABLE：表示将视图的结果存入临时表，然后用临时表来执行语句。

CASCADED 与 LOCAL 为可选参数
CASCADED：默认值，表示更新视图时要满足所有相关视图和表的条件；
LOCAL：表示更新视图时满足该视图本身定义的条件即可。
该语句要求具有针对视图的 CREATE VIEW 权限，以及针对由 SELECT 语句选择的每一列上的某些权限。对于在 SELECT 语句中其他地方使用的列，必须具有 SELECT 权限。如果还有 OR REPLACE 子句，必须在视图上具有DROP权限。

视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时应将名称指定为 db_name.view_name 。

（一）在单表上创建视图

create view view_t_01(quan,pri,total) as select quantity,price,quantity * price from t_01;

结果：

mysql> select * from view_t_01;
+------+------+-------+
| quan | pri  | total |
+------+------+-------+
|    3 |   50 |   150 |
+------+------+-------+
1 row in set (0.00 sec)

（二）在多表上创建视图

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;

mysql> select * from stu_glass;
+------+----------+------------+
| id   | name     | glass      |
+------+----------+------------+
|    1 | xiaowang | five_class |
|    2 | xiaoxin  | six_class  |
|    3 | xiaoqin  | one_class  |
+------+----------+------------+
3 rows in set (0.00 sec)

三、查看视图

（一）使用 describe 查看视图

基本语法：

describe <视图名>;

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 (0.00 sec)

（二）使用 show table status 查看视图

基本语法：

show table status like '视图名';

查看视图结果：

mysql> show table status like 'view_t';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| view_t | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)

查看表结果：

mysql> show table status like 't_01';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t_01 | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2020-06-18 15:30:35 | 2020-06-18 15:32:11 | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

（三）使用 show create view 查看视图

基本语法：

show create view <视图名>;

mysql> show create view view_t;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View                                                                                                                                                                                                                         | character_set_client | collation_connection |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_t | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t` AS select `t_01`.`quantity` AS `quantity`,`t_01`.`price` AS `price`,(`t_01`.`quantity` * `t_01`.`price`) AS `quantity * price` from `t_01` | utf8                 | utf8_general_ci      |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

（四）使用 views 查看所有视图

基本语法：

select * from information_schema.views;

四、修改视图

（一）使用 create or replace view 修改视图

语法结构与创建视图一样，当视图存在时，修改语句对视图修改，不存在时对视图进行创建：

create [or replace] [algorithm = {undefined | merge | temptable}]

view view_name [(column_list)]

as select_statement

[with [cascaded | local] check option]

@example：

create or replace view view_t as select price from t_01;

mysql> desc view_t_03;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| price | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

（二）使用 alter 修改视图

语法结构：

alter [algorithm = {undefined | merge | temptable}]

view view_name [(column_list)]

as select_statement

[with [cascaded | local] check option]

@example：

alter view view_t as select quantity from t_01;

mysql> desc view_t;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

五、更新视图

更新视图是指通过视图来插入、更新、删除表中的数据，视图是一个虚拟表， 其中没有数据。

视图更新的都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

update view_name set <字段> = 值;

insert into table_name values(值1，值2，...);

对一个视图更新后，其基本表内容被更新，其他与本基本表相关的视图也被更新，包括算术操作的视图字段。
 

delete from view_name where <字段> = 值;

视图中删除操作最终是通过删除基本表中的相关记录来实现的。

视图包含下面内容时，更新操作不能执行：
（1）视图中不包含基表中被定义为非空的列。
（2）在定义视图的 SELECT 语句后的字段列表中使用了数学表达式。
（3）在定义视图的 SELECT 语句后的字段列表中使用聚合函数。
（4）在定义视图的 SELECT 语句中使用了 DISTINCT，UNION，TOP，GROUP BY 或 HAVING 子句。

六、删除视图

drop view [if exists]

view_name [,view_name] ...

[restrict | cascade]

@example:

drop view if exists stu_glass;

七、视图与表区别

（1）视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。
（2）视图没有实际的物理记录，而表有。
（3）表是内容，视图是窗口。
（4）表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改。
（5）视图是查看数据表的一种方法， 可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
（6）表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
（7）视图的建立和删除只影响视图本身，不影响对应的基本表。