mysql系列X-视图

发表于 更新于 分类于 ， Changyan： 本文字数： 3.1k 阅读时长 ≈ 11 分钟

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

创建视图

1
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

查询视图

1
2
3
4
-- 查看创建视图语句：
SHOW CREATE VIEW 视图名称;
-- 查看视图数据：
SELECT * FROM 视图名称 ...... ;

修改视图

1
2
3
4
-- 方式一：
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
-- 方式二：
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

删除视图

1
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

演示示例：

1
2
3
4
5
6
7
8
9
10
11
-- 创建视图
create or replace view emp_view as select id, name, job from emp where id <=20;
-- 查询视图
show create view emp_view;
select * from emp_view;
select * from emp_view where id < 3;
-- 修改视图
create or replace view emp_view as select id, name, age, job from emp where id <= 10;
alter view emp_view as select id, name, job from emp where id <= 10;
-- 删除视图
drop view if exists emp_view;

上述我们演示了，视图应该如何创建、查询、修改、删除，那么我们能不能通过视图来插入、更新数据呢？ 接下来，做一个测试。

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
mysql> create or replace view emp_view as select id, name, job from emp where id <= 10;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp_view;
+----+--------+----------+
| id | name   | job      |
+----+--------+----------+
|  1 | 金庸   | 总裁     |
|  2 | 张无忌 | 项目经理 |
|  3 | 杨逍   | 开发     |
|  4 | 韦一笑 | 开发     |
|  5 | 常遇春 | 开发     |
+----+--------+----------+
5 rows in set (0.00 sec)

mysql> insert into emp_view values(6, '谢逊', '项目经理');
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp_view values(11, '小昭', '客服');
Query OK, 1 row affected (0.02 sec)

mysql> select * from emp_view;
+----+--------+----------+
| id | name   | job      |
+----+--------+----------+
|  1 | 金庸   | 总裁     |
|  2 | 张无忌 | 项目经理 |
|  3 | 杨逍   | 开发     |
|  4 | 韦一笑 | 开发     |
|  5 | 常遇春 | 开发     |
|  6 | 谢逊   | 项目经理 |
+----+--------+----------+
6 rows in set (0.00 sec)

执行上述的SQL，我们会发现，id为6和11的数据都是可以成功插入的。 但是我们执行查询，查询出来的数据，却没有id为11的记录。

我们查看下原始表数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from emp;
+----+--------+------+----------+--------+------------+-----------+---------+
| id | NAME   | age  | job      | salary | entrydate  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       5 |
|  2 | 张无忌 |   22 | 项目经理 |  12500 | 2005-12-05 |         1 |       1 |
|  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       1 |
|  4 | 韦一笑 |   48 | 开发     |  11000 | 2002-02-05 |         2 |       1 |
|  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       1 |
|  6 | 谢逊   | NULL | 项目经理 |   NULL | NULL       |      NULL |    NULL |
| 11 | 小昭   | NULL | 客服     |   NULL | NULL       |      NULL |    NULL |
+----+--------+------+----------+--------+------------+-----------+---------+
7 rows in set (0.00 sec)

可以看到刚才添加的两条数据是成功且存在的。

因为我们在创建视图的时候，指定的条件为 id<=10, id为11的数据，是不符合条件的，所以没有查询出来，但是这条数据确实是已经成功的插入到了基表中。

如果我们定义视图时，如果指定了条件，然后我们在插入、修改、删除数据时，是否可以做到必须满足条件才能操作，否则不能够操作呢？ 答案是可以的，这就需要借助于视图的检查选项了。

检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED 。

CASCADED

级联。

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图
创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

我们先拿案例做演示：

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
-- 查询基表原始数据5条
mysql> select * from emp;
+----+--------+------+----------+--------+------------+-----------+---------+
| id | NAME   | age  | job      | salary | entrydate  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       5 |
|  2 | 张无忌 |   22 | 项目经理 |  12500 | 2005-12-05 |         1 |       1 |
|  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       1 |
|  4 | 韦一笑 |   48 | 开发     |  11000 | 2002-02-05 |         2 |       1 |
|  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       1 |
+----+--------+------+----------+--------+------------+-----------+---------+

-- 创建emp_v1基于emp表
mysql> create view emp_v1 as select id, name from emp where id <= 20;
Query OK, 0 rows affected (0.01 sec)


-- 创建emp_v2基于emp_v1表（添加了with cascaded check option）
mysql> create view emp_v2 as select id, name from emp_v1 where id >= 10 with cascaded check option;
Query OK, 0 rows affected (0.01 sec)


-- 创建emp_v3基于emp_v2表
mysql> create view emp_v3 as select id, name from emp_v2 where id <= 15;
Query OK, 0 rows affected (0.01 sec)


接着我们依次插入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- id为14的记录因为在emp_v3定义范围内；也没有设置范围检查；且符合emp_v3基于emp_v2、emp_v1的定义，所以插入成功
mysql> insert into emp_v3 values(14, "孙悟空");
Query OK, 1 row affected (0.03 sec)

-- emp_v3插入id为16的记录，不在emp_v3定义范围内；也没有设置范围检查；但是符合emp_v3基于emp_v2、emp_v1的定义范围，所以插入成功
mysql> insert into emp_v3 values(16, "猪八戒");
Query OK, 1 row affected (0.00 sec)

-- emp_v3插入id为6的记录，在emp_v3定义范围内；emp_v3也没有设置范围检查；但不在emp_v2定义范围；且emp_v2设置了cascaded check option级联范围检查，所以插入失败
mysql> insert into emp_v3 values(6, "沙悟净");
ERROR 1369 (HY000): CHECK OPTION failed 'emp_v3'

-- emp_v3插入id为21的记录，不在emp_v3定义范围内；emp_v3没有设置范围检查；在emp_v2定义范围；且emp_v2设置了cascaded check option级联范围检查，所以会检查emp_v2的基数视图做范围检查，发现id=21不在emp_v1定义范围内，所以插入失败
mysql> insert into emp_v3 values(21, "唐玄奘");
ERROR 1369 (HY000): CHECK OPTION failed 'emp_v3'

总结来说图示如下：

LOCAL

本地。

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创
建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

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
-- 查询基表原始数据5条
mysql> select * from emp;
+----+--------+------+----------+--------+------------+-----------+---------+
| id | NAME   | age  | job      | salary | entrydate  | managerid | dept_id |
+----+--------+------+----------+--------+------------+-----------+---------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       5 |
|  2 | 张无忌 |   22 | 项目经理 |  12500 | 2005-12-05 |         1 |       1 |
|  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       1 |
|  4 | 韦一笑 |   48 | 开发     |  11000 | 2002-02-05 |         2 |       1 |
|  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       1 |
+----+--------+------+----------+--------+------------+-----------+---------+

-- 创建emp_v1基于emp表
mysql> create view emp_v1 as select id, name from emp where id <= 20;
Query OK, 0 rows affected (0.01 sec)


-- 创建emp_v2基于emp_v1表（添加了with cascaded check option）
mysql> create view emp_v2 as select id, name from emp_v1 where id >= 10 with local check option;
Query OK, 0 rows affected (0.01 sec)


-- 创建emp_v3基于emp_v2表
mysql> create view emp_v3 as select id, name from emp_v2 where id <= 15;
Query OK, 0 rows affected (0.01 sec)

插入数据进行测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- emp_v1没有设置范围检查，所以都可以
mysql> insert into emp_v3 values(14, "孙悟空");
Query OK, 1 row affected (0.02 sec)

-- 测试主键在所有关联视图定义范围内，所以也能插入成功
mysql> insert into emp_v3 values(16, "猪八戒");
Query OK, 1 row affected (0.00 sec)

-- 测试主键6不在emp_v2定义范围内，且emp_v2设置了范围检查，故插入失败
mysql> insert into emp_v3 values(6, "沙悟净");
ERROR 1369 (HY000): CHECK OPTION failed 'studydata.emp_v3'

-- emp_v1和emp_v3都没有设置范围检查，emp_v2设置了范围检查，但是插入主键在emp_v2定义范围内，故插入成功
mysql> insert into emp_v3 values(21, "唐玄奘");
Query OK, 1 row affected (0.00 sec)

总结如图所示：

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

  • 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）

  • DISTINCT

  • GROUP BY

  • HAVING

  • UNION 或者 UNION ALL

示例演示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> create view emp_view_count as select count(*) sums from emp;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from emp_view_count;
+------+
| sums |
+------+
|    8 |
+------+
1 row in set (0.02 sec)


mysql> insert into emp_view_count values(99);
ERROR 1471 (HY000): The target table emp_view_count of the INSERT is not insertable-into

视图作用

简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

数据独立

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

案例

1). 为了保证数据库表的安全性，开发人员在操作emp表时，只能看到的用户的基本字段，屏蔽年龄和薪资两个字段。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create or replace view emp_view as select id, name, job, entrydate from emp;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp_view;
+----+--------+----------+------------+
| id | name   | job      | entrydate  |
+----+--------+----------+------------+
|  1 | 金庸   | 总裁     | 2000-01-01 |
|  2 | 张无忌 | 项目经理 | 2005-12-05 |
|  3 | 杨逍   | 开发     | 2000-11-03 |
|  4 | 韦一笑 | 开发     | 2002-02-05 |
|  5 | 常遇春 | 开发     | 2004-09-07 |
| 14 | 孙悟空 | NULL     | NULL       |
| 16 | 猪八戒 | NULL     | NULL       |
| 21 | 唐玄奘 | NULL     | NULL       |
+----+--------+----------+------------+
8 rows in set (0.00 sec)

2). 查询每个员工所属部门（两张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create or replace view emp_dept_view as select e.*, d.name as dept_name from emp e left join dept d on e.dept_id = d.id;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from emp_dept_view;
+----+--------+------+----------+--------+------------+-----------+---------+-----------+
| id | NAME   | age  | job      | salary | entrydate  | managerid | dept_id | dept_name |
+----+--------+------+----------+--------+------------+-----------+---------+-----------+
|  1 | 金庸   |   66 | 总裁     |  20000 | 2000-01-01 |      NULL |       5 | 总经办    |
|  2 | 张无忌 |   22 | 项目经理 |  12500 | 2005-12-05 |         1 |       1 | 研发部    |
|  3 | 杨逍   |   33 | 开发     |   8400 | 2000-11-03 |         2 |       1 | 研发部    |
|  4 | 韦一笑 |   48 | 开发     |  11000 | 2002-02-05 |         2 |       1 | 研发部    |
|  5 | 常遇春 |   43 | 开发     |  10500 | 2004-09-07 |         3 |       1 | 研发部    |
| 14 | 孙悟空 | NULL | NULL     |   NULL | NULL       |      NULL |    NULL | NULL      |
| 16 | 猪八戒 | NULL | NULL     |   NULL | NULL       |      NULL |    NULL | NULL      |
| 21 | 唐玄奘 | NULL | NULL     |   NULL | NULL       |      NULL |    NULL | NULL      |
+----+--------+------+----------+--------+------------+-----------+---------+-----------+
8 rows in set (0.00 sec)

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁