介绍

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

1、视图概述

1.1、什么是视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注意：

数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

1.2、视图的作用

重复利用SQL语句

简化SQL查询，快速取数据

只用知道表的部分结构

保护数据，根据特定授权

更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据

注意：
在视图创建后，可以用与表基本相同的方式使用（查询、过滤、排序数据、与其他视图或连结、（添加、更新））。

视图只是用来查看存储在别处的数据的设施，本身不包含数据，返回的数据也是从其他表检索出来的。

因为视图本身不包含数据，索引多个表连结或嵌套可能存在性能问题，需测试。

1.3、视图规则和限制

与表一样，命名必须是唯一的(不能出现同名视图或表名)。

创建视图数目无限制，但是要考虑复杂查询创建为视图之后的性能影响。

视图不能添加索引，也不能有关联的触发器或者默认值。

视图可以提高安全性，必须具有足够的访问权限。

order by可用在视图中，但是如果从该视图检索数据select中含有order by ，那么该视图中的order by将被覆盖。

视图可以和表一起使用。

1.4、视图的应用

权限控制时使用

如某几个列，允许用户查询，其他列不允许查询

可以通过视图，开放其中几列查询，起到权限控制作用

简化复杂查询时使用

查询每个栏目下商品的平均价格，并按平均价格排序，查询出平均价格前3的栏目

视图能不能更新，删除，添加

如果视图的每一行，是与物理表一一对应的则可以

视图的行是由物理表多行经过计算得到的结果，视图不可以更新的

2、视图创建-上

2.1、创建格式

格式：
create view 视图名 as select 字段名 from 表名;

案例：

创建一个视图
mysql> create view s_view as (select sname,sex,age from students);
Query OK, 0 rows affected (0.02 sec)
mysql> select * from s_view;
+--------+------+------+
| sname | sex | age |
+--------+------+------+
| 张三 | 男 | 19 |
| 李四 | 男 | 20 |
| 张红 | 女 | 19 |
| 张八 | 男 | 18 |
| 三李 | 男 | 19 |
| 王六 | 女 | 20 |
| 刘红 | 女 | 18 |
+--------+------+------+
8 rows in set (0.00 sec)

2.2、视图的运算规则

格式：
create [algorithm = {undefined | merge | temptable}] view 视图名 as select 字段名 from 表名;

注意：

ALGORITHM ： 视图算法

undefined 系统自动选择算法

merge 当使用视图时，会把查询视图的语句和创建视图的语句合并起来，形成一条件一句，最后再从基表中查询

temptable 当使用视图时，会把创建视图的语句的查询结果当成一张临时表，再从临时表中进行筛选

案例：

用temptable创建视图

mysql> create algorithm=temptable view view_t as select sname,sex,english,math from students order by math,english desc;
Query OK, 0 rows affected (0.03 秒)
mysql> select * from view_t;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 王六 | 女 | 50.0 | 70.0 |
| 张红 | 女 | 86.0 | 80.0 |
| 张八 | 男 | 80.0 | 85.0 |
| 张三 | 男 | 98.5 | 88.0 |
| 李四 | 男 | 80.0 | 88.0 |
| 三李 | 男 | 60.0 | 88.0 |
| 刘红 | 女 | 90.0 | 98.0 |
+--------+------+---------+------+
8 行于数据集 (0.01 秒)
mysql> select * from view_t group by sex;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 王六 | 女 | 50.0 | 70.0 |
| 张八 | 男 | 80.0 | 85.0 |
+--------+------+---------+------+
2 行于数据集 (0.01 秒)

用merge创建视图

mysql> create algorithm=merge view view_m as select sname,sex,english,math from students order by math,english desc;
Query OK, 0 rows affected (0.04 秒)
mysql> select * from view_m;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 王六 | 女 | 50.0 | 70.0 |
| 张红 | 女 | 86.0 | 80.0 |
| 张八 | 男 | 80.0 | 85.0 |
| 张三 | 男 | 98.5 | 88.0 |
| 李四 | 男 | 80.0 | 88.0 |
| 三李 | 男 | 60.0 | 88.0 |
| 刘红 | 女 | 90.0 | 98.0 |
+--------+------+---------+------+
8 行于数据集 (0.02 秒)
mysql> select * from view_m group by sex;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 张三 | 男 | 98.5 | 88.0 |
| 张红 | 女 | 86.0 | 80.0 |
+--------+------+---------+------+
2 行于数据集 (0.01 秒)
#查询视图的语句和创建视图的语句合并起来，形成一条件一句，最后再从基表中查询
mysql> select sname,sex,english,math from students group by sex order by math,english desc;
+--------+------+---------+------+
| sname | sex | english | math |
+--------+------+---------+------+
| 张红 | 女 | 86.0 | 80.0 |
| 张三 | 男 | 98.5 | 88.0 |
+--------+------+---------+------+
2 行于数据集 (0.02 秒)

3、视图创建-下

3.1、视图的权限范围

格式：
[with [cascaded | local ] check option]

WITH CHECK OPTION 表示对UPDATE、INSERT和DELETE操作时保持更新，插入或删除的行满足视图定义的条件（即子查询中的条件表达式）

注意：

cascaded 默认值 更新视图时要满足所有相关视图和表的条件。

local 表示更新视图时满足该视图本身定义的条件即可。

案例：

mysql> create view view_1 as select sid,sname,sex,age from students where sid<6;
Query OK, 0 rows affected (0.02 秒)
mysql> create view view_1_1 as select * from view_1 where sid>2 with cascaded check option;
Query OK, 0 rows affected (0.02 秒)
mysql> create view view_1_2 as select * from view_1 where sid>2 with local check option;
Query OK, 0 rows affected (0.02 秒)
mysql> insert into view_1_1 values(6,'lisi','男',20);
CHECK OPTION failed 'tyschool.view_1_1'
mysql> insert into view_1_2 values(6,'lisi','男',20);
Query OK, 1 rows affected (0.01 秒)

3.2、视图记录修改

格式：
update 数据库表名 set 字段名1=字段值1,字段名2=字段值2,...字段名n=字段值n where 条件表达式; #和表的修改一样

案例：

修改视图中王六的性别为‘男’

mysql> update s_view set sex='男' where sname='王六';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from s_view;
+--------+------+------+
| sname | sex | age |
+--------+------+------+
| 张三 | 男 | 19 |
| 李四 | 男 | 20 |
| 张红 | 女 | 19 |
| 张八 | 男 | 18 |
| 三李 | 男 | 19 |
| 王六 | 男 | 20 |
| 刘红 | 女 | 18 |
+--------+------+------+
8 rows in set (0.00 sec)

注意：

修改了视图，对基表数据也有影响

mysql> select * from students;
+------+--------+------+------+---------+------+------------+-----------------+
| sid | sname | sex | age | english | math | entertime | remark |
+------+--------+------+------+---------+------+------------+-----------------+
| 1 | 张三 | 男 | 19 | 98.5 | 88.0 | 2017-09-01 | 他来自四川 |
| 2 | 李四 | 男 | 20 | 80.0 | 88.0 | 2017-09-01 | 他来自重庆 |
| 3 | 张红 | 女 | 19 | 86.0 | 80.0 | 2017-09-01 | 他来自北京 |
| 4 | 张八 | 男 | 18 | 80.0 | 85.0 | 2017-09-01 | 他来自天津 |
| 5 | 三李 | 男 | 19 | 60.0 | 88.0 | 2017-09-01 | 他来自湖北 |
| 6 | 王六 | 男 | 20 | 50.0 | 70.0 | 2017-09-01 | 他来自湖南 |
| 7 | 刘红 | 女 | 18 | 90.0 | 98.0 | 2017-09-01 | 他来自甘肃 |
+------+--------+------+------+---------+------+------------+-----------------+
8 rows in set (0.00 sec)

修改students表中王六的年龄为21

mysql> update students set age=21 where sname='王六';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from s_view;
+--------+------+------+
| sname | sex | age |
+--------+------+------+
| 张三 | 男 | 19 |
| 李四 | 男 | 20 |
| 张红 | 女 | 19 |
| 张八 | 男 | 18 |
| 三李 | 男 | 19 |
| 王六 | 男 | 21 |
| 刘红 | 女 | 18 |
+--------+------+------+
8 rows in set (0.00 sec)

4、视图修改

4.1、修改格式

格式：
alter view 视图名称 as select 语句;
或
alter view 视图名称 as select 视图;
或
create or replace view 视图名  as  select 字段名 from 表名;

4.2、select 语句 修改

案例：

修改我们的s_view视图

mysql> alter view s_view as select sname,sex,age,remark from students;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from s_view;
+--------+------+------+-----------------+
| sname | sex | age | remark |
+--------+------+------+-----------------+
| 张三 | 男 | 19 | 他来自四川 |
| 李四 | 男 | 20 | 他来自重庆 |
| 张红 | 女 | 19 | 他来自北京 |
| 张八 | 男 | 18 | 他来自天津 |
| 三李 | 男 | 19 | 他来自湖北 |
| 王六 | 女 | 20 | 他来自湖南 |
| 刘红 | 女 | 18 | 他来自甘肃 |
+--------+------+------+-----------------+
8 rows in set (0.01 sec)

4.3、select 视图 修改

案例：

修改我们的s_view视图

mysql> create view s_view_1 as(select sname,sex,age,remark from students);
Query OK, 0 rows affected (0.01 sec)
mysql> alter view s_view as select sname,remark from s_view_1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from s_view;
+--------+-----------------+
| sname | remark |
+--------+-----------------+
| 张三 | 他来自四川 |
| 李四 | 他来自重庆 |
| 张红 | 他来自北京 |
| 张八 | 他来自天津 |
| 三李 | 他来自湖北 |
| 王六 | 他来自湖南 |
| 刘红 | 他来自甘肃 |
+--------+-----------------+
8 rows in set (0.00 sec)

4.4、create or replace

案例：

修改我们的s_view视图

mysql> create or replace view s_view as select sname from students;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from s_view;
+--------+
| sname |
+--------+
| 张三 |
| 李四 |
| 张红 |
| 张八 |
| 三李 |
| 王六 |
| 刘红 |
+--------+
8 rows in set (0.00 sec)

5、视图查看

5.1、显示视图创建情况

格式：
show create view 视图名;

案例：

mysql> show create view s_view;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| s_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `s_view` AS select `s_view_1`.`sname` AS `sname`,`s_view_1`.`remark` AS `remark` from `s_view_1` | utf8mb4 | utf8mb4_0900_ai_ci |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

5.2、查看视图

5.2.1、查看视图结构

格式：
desc 视图名;

案例：

查看视图s_view结构

mysql> desc s_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sname | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)

5.2.2、查看数据库中的视图

格式：
show tables [like %字符串%];

案例：

查看数据库中所有视图

mysql> show tables;
+--------------------+
| Tables_in_tyschool |
+--------------------+
| classes |
| new_user |
| s_view |
| s_view_1 |
| stu |
| student |
| students |
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
| t6 |
| teacher |
| user |
+--------------------+
15 rows in set (0.00 sec)
mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_tyschool (%view%) |
+-----------------------------+
| s_view |
| s_view_1 |
+-----------------------------+
2 rows in set (0.00 sec)

6、视图删除及重命名

6.1、视图删除

格式：
drop view 视图列表;

案例：

删除视图s_view_1

mysql> drop view s_view_1;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_tyschool (%view%) |
+-----------------------------+
| s_view |
+-----------------------------+
1 row in set (0.00 sec)

6.2、视图重命名

格式：
rename table 视图名 to 新视图名;

案例：

修改视图s_view的名字为view_s

mysql> rename table s_view to view_s;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_tyschool (%view%) |
+-----------------------------+
| view_s |
+-----------------------------+
1 row in set (0.01 sec)