上一篇我们介绍了索引的概念、索引提高查询性能的原理，以及索引需要付出的代价。
今天我们来讨论另一个重要的数据库对象：视图（View），学习如何利用视图简化查询语句、实现业务规则以及提高数据的安全性。
视图不是表
简单来说，视图就是一个预定义的查询语句。视图在许多情况下可以当作表来使用，因此也被称为虚拟表（Virtual Table）。视图与表最大的区别在于它不包含数据，数据库中只存储视图的定义语句。以下是一个视图的示意图：
知道了什么是视图，那为什么需要视图呢？因为它可以给我们带来许多好处：
- 替代复杂查询，减少复杂性。将复杂的查询语句定义为视图，然后使用视图进行查询，可以隐藏具体的实现；
- 提供一致性接口，实现业务规则。在视图的定义中增加业务逻辑，对外提供统一的接口；当底层表结构发生变化时，只需要修改视图接口，而不需要修改外部应用，可以简化代码的维护并减少错误；
- 控制对于表的访问，提高安全性。通过视图为用户提供数据访问，而不是直接访问表；同时可以限制允许访问某些敏感信息，例如身份证号、工资等。
不过，视图也可能带来一些问题：
- 不当的使用可能会导致性能问题。视图的定义中可能包含了复杂的查询，例如嵌套的子查询和多个表的连接查询，可能导致使用视图进行查询时性能不佳；
- 视图通常用于查询操作，可更新视图（Updatable View）需要满足许多限制条件。可更新视图可以支持通过视图对底层表进行增删改的操作。
创建视图
我们可以使用 CREATE VIEW 语句创建一个新的视图：
CREATE VIEW view_name AS select_statement; 复制
其中，view_name 是视图的名称；select_statement 是视图的定义，也就是一个 SELECT 语句。视图可以基于一个或多个表定义，也可以基于其他视图进行定义。
以下语句创建了一个名为 developers 的视图。其中只包含开发部门的员工，同时隐藏了月薪等敏感信息：
CREATE VIEW developers AS SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email FROM employee WHERE dept_id = 4; 复制
创建视图之后，可以像普通表一样将视图作为查询的数据源。以下语句使用视图 developers 进行查询：
SELECT emp_name, sex, hire_date, email FROM developers WHERE sex = ‘女’; 复制
该语句返回了开发部门的女性员工，查询的结果如下：
视图也可以用于复杂的查询操作。以下示例将视图 developers 与 job 表进行连接，然后执行分组统计：
SELECT j.job_title, d.sex, COUNT() FROM developers d JOIN job j ON (j.job_id = d.job_id) GROUP *BY j.job_title, d.sex; 复制
该语句返回了开发部门中，按照职位和性别统计的人数；查询的结果如下：
视图定义中的 SELECT 语句与普通的查询一样，可以包含任意复杂的选项。例如子查询、集合操作、分组聚合等。但有一个需要注意的选项就是 ORDER BY 子句。
视图中的 ORDER BY
许多数据库都支持在视图定义中使用 ORDER BY 子句；但是 SQL 标准并不支持这种写法，因为视图并不存储数据。以下语句创建了一个按照 empid 排序的视图 developers2：
— Oracle、MySQL 以及 PostgreSQL 实现_ CREATE VIEW developers2 AS SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email FROM employee WHERE dept_id = 4 ORDER BY emp_id; 复制
建议最好不要在视图的定义中使用 ORDER BY，因为这种排序并不能保证最终结果的顺序；而且可能由于不必要的排序降低查询的性能。
SQL Server 不允许视图定义中包含 ORDER BY 子句，除非指定了 TOP 、OFFSET 或者 FOR XML 选项。
修改视图
如果需要修改视图的定义，可以删除并重新创建视图。除此之外，各种数据库也提供了直接替换视图定义的命令：
— Oracle、MySQL 以及 PostgreSQL 实现 CREATE OR REPLACE VIEW viewname AS select_statement; — SQL Server 实现 CREATE OR ALTER VIEW view_name AS select_statement; 复制
其中 CREATE OR REPLACE VIEW 表示如果视图不存在，创建视图；如果视图已经存在，替换视图。SQL Server 使用 CREATE OR ALTER VIEW 实现相同的功能。
以下语句修改了视图 developers 的定义，删除了字段 hire_date 和 job_id：
— Oracle 以及 MySQL 实现 CREATE OR REPLACE VIEW developers AS SELECT emp_id, emp_name, sex, manager, email FROM employee WHERE dept_id = 4; — SQL Server 实现_ CREATE OR REPLACE VIEW developers AS SELECT emp_id, emp_name, sex, manager, email FROM employee WHERE dept_id = 4; 复制
执行以上语句将会替换原来的视图 developers。PostgreSQL 只支持在视图定义中追加字段，不支持删除字段；这种情况下只能删除再重建。
MySQL 和 SQL Server 还支持使用 ALTER VIEW view_name AS select_statement; 命令修改视图的定义。 Oracle 和 PostgreSQL 中的 ALTER VIEW 命令用于修改视图的其他属性。
删除视图
使用 DROP VIEW 命令可以删除一个视图：
DROP VIEW [IF EXISTS] view_name; 复制
指定 IF EXISTS 选项后，删除一个不存在的视图时也不会产生错误。Oracle 不支持 IF EXISTS 选项。
以下语句可以删除视图 developers：
DROP VIEW developers; 复制
通常来说，视图主要用于查询数据；但是某些视图也可以用于修改数据，这种视图被称为可更新视图（Updatable View）。
可更新视图
可更新视图是指通过视图更新底层表，对于视图的 INSERT、UPDATE、DELETE 等操作最终会转换为针对底层基础表的相应操作。可更新视图的定义需要满足许多限制条件，包括：
- 不能使用聚合函数或窗口函数，例如 AVG、SUM、COUNT 等；
- 不能使用 DISTINCT、GROUP BY、HAVING 子句；
- 不能使用集合运算符，例如 UNION、INTERSECT 等；
- 修改操作只能涉及单个表中的字段，不能同时修改多个表；
- 不同数据库实现的其他限制条件。
总之，对视图的修改只有在能够映射为对基础表的修改时，数据库才能执行视图的修改操作。
我们创建一个简单的视图 developersupdatable，包含了 employee 中除 bonus 之外的所有字段：
CREATE VIEW developers_updatable AS SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email FROM employee WHERE dept_id = 4; 复制
以下语句通过视图 developers_updatable 修改员工表中的 email：
UPDATE developers_updatable SET email = ‘zhaoshi@shuguo.net’ WHERE emp_name = ‘赵氏’; SELECT emp_name, email FROM employee WHERE emp_name = ‘赵氏’; emp_name|email | ————|—————————|_ 赵氏 |zhaoshi@shuguo.net| 复制
从查询的结果可以看出，针对视图 developers_updatable 的更新最终修改了 employee 中的数据。
需要注意，不在视图定义中的字段不能通过视图进行修改。以下语句尝试通过视图 developers_updatable 修改 employee 中的 bonus 字段：
UPDATE developers_updatable SET bonus = 2000 WHERE emp_name = ‘赵氏’; 复制
执行以上语句将会返回一个错误：bonus 字段不存在或者无效。因为视图 developers_updatable 没有包含员工的奖金字段，这样可以防止敏感数据的误修改。
虽然无法通过视图修改视图定义之外的列，但是有可能修改视图可见范围之外的行。
WITH CHECK OPTION 选项
以下语句尝试通过视图 developersupdatable 为员工表增加一个员工：
— 只有 Oracle 需要执行以下 ALTER 语句 — ALTER SESSION SET NLSDATE_FORMAT = ‘yyyy-mm-dd’; INSERT INTO developersupdatable(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email) VALUES (30, ‘张三’, ‘男’, 5, 18, ‘2019-10-01’, 10, 6000, ‘zhangsan@shuguo.com’); 复制
该员工属于销售部（dept_id = 5），不在视图 developers_updatable 的可见范围之内；但是该语句仍然能够执行成功，并且能够在员工表中找到该条记录：
SELECT emp_id, emp_name, sex FROM employee WHERE emp_name = ‘张三’; emp_id|emp_name|sex| ———|————|—-| 30|张三 |男 | SELECT emp_id, emp_name, sex FROM developers_updatable WHERE emp_name = ‘张三’; emp_id|emp_name|sex| ———|————|—-| 复制
员工表中存在“张三”，但是视图无法看到该员工。
为了防止这种情况的发生，可以使用视图的 WITH CHECK OPTION 选项。该选项用于限制对视图的插入和更新操作，不会产生对视图不可见的数据。我们使用 WITH CHECK OPTION 选项重建视图 developers_updatable：
— Oracle、MySQL 以及 PostgreSQL 实现 CREATE OR REPLACE VIEW developers_updatable AS SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email FROM employee WHERE dept_id = 4 WITH CHECK OPTION; 复制
在 SQL Server 中可以使用 CREATE OR ALTER VIEW 语句重建该视图。
然后再次增加一个销售部的员工：
— 只有 Oracle 需要执行以下 ALTER 语句 — ALTER SESSION SET NLSDATE_FORMAT = ‘yyyy-mm-dd’; INSERT INTO developersupdatable(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email) VALUES (31, ‘李四’, ‘男’, 5, 18, ‘2019-10-01’, 10, 6000, ‘lisi@shuguo.com’); — Oracle SQL Error [1402] [44000]: ORA-01402: view WITH CHECK OPTION where-clause violation — MySQL SQL Error [1369] [HY000]: CHECK OPTION failed ‘hrdb.developers_updatable’ — SQL Server SQL Error [550] [S0001]: The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. — PostgreSQL_ SQL Error [44000]: ERROR: new row violates check option for view “developers_updatable” Detail: Failing row contains (30, 张三, 男, 5, 18, 2019-10-01, 10, 6000.00, null, zhangsan@shuguo.com). 复制
以上是各种数据库返回的错误信息，表示视图的数据检查失败。
小结
视图与子查询和通用表表达式有类似之处，都可以作为查询的数据源；但是视图是存储在数据库中的对象，可以被重复使用。合理的使用视图可以实现底层数据表的隐藏，对外提供一致接口，提高数据访问的安全性。不过，复杂的视图可能导致维护和性能问题；在实际应用之前最好进行相关的性能测试。
练习题 1：创建一个关于员工信息的视图 emp_detail：
CREATE VIEW emp_detail(id, manager, dept_name, job_title, name, sex, email) AS …; 复制
其中 dept_name 来自部门表，job_title 来自职位表，并且为某些字段指定了自定义的名称。
练习题 2：以下语句能不能执行成功？
UPDATE developers_updatable SET dept_id = 5 WHERE emp_name = ‘马岱’;
若有收获，就点个赞吧
0 人点赞
