视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的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) |
