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

一、视图概述

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.1、创建格式

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

案例：

创建一个视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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、视图的运算规则

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

注意：

ALGORITHM ： 视图算法

undefined 系统自动选择算法

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

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

案例：

用temptable创建视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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创建视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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 秒)

2.3、视图的权限范围

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

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

注意：

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

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

案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 'test.view_1_1'

mysql> insert into view_1_2 values(6,'lisi','男',20);
Query OK, 1 rows affected (0.01 秒)

2.4、视图记录修改

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

案例：

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)

注意：

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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)

三、视图修改

3.1、修改格式

1
2
3
4
5
6
格式：
alter view 视图名称 as select 语句;
或
alter view 视图名称 as select 视图;
或
create or replace view  视图名  as  select 字段名 from 表名;

3.2、select 语句 修改

案例：

修改我们的s_view视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)

3.3、select 视图 修改

案例：

修改我们的s_view视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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)

3.4、create or replace

案例：

修改我们的s_view视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)

四、视图查看

4.1、显示视图创建情况

1
2
格式：
show create view 视图名;

案例：

1
2
3
4
5
6
7
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)

4.2、查看视图

4.2.1、查看视图结构

1
2
格式：
desc 视图名;

案例：

查看视图s_view结构

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

4.2.2、查看数据库中的视图

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

案例：

查看数据库中所有视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> show tables;
+--------------------+
| Tables_in_test |
+--------------------+
| 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_test (%view%) |
+-----------------------------+
| s_view                      |
| s_view_1                    |
+-----------------------------+
2 rows in set (0.00 sec)

五、视图删除及重命名

5.1、视图删除

1
2
格式：
drop view 视图列表;

案例：

删除视图s_view_1

1
2
3
4
5
6
7
8
9
10
mysql> drop view s_view_1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_test (%view%) |
+-----------------------------+
| s_view                      |
+-----------------------------+
1 row in set (0.00 sec)

5.2、视图重命名

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

案例：

修改视图s_view的名字为view_s

1
2
3
4
5
6
7
8
9
10
mysql> rename table s_view to view_s;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables like '%view%';
+-----------------------------+
| Tables_in_test (%view%) |
+-----------------------------+
| view_s                      |
+-----------------------------+
1 row in set (0.01 sec)
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。