SQL从入门到出门 第13章 简单视图

• • 创建视图
  • 使用视图
  • 修改视图
  • 删除视图

本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的视图（View）和相关操作，以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

在 SQL 定义中，视图（view）是一个命名的查询语句。它在许多情况下可以当作表来使用，因此视图也被看作一个虚拟的表。视图与表最大的区别在于它自身不包含数据，数据库中存储的只是视图定义时的查询语句。

视图具有以下优点：

• 替代复杂查询，减少复杂性；
• 提供一致性接口，实现业务规则；
• 控制对于表的访问，提高安全性。

但是，使用视图也需要以下问题：

• 不当使用可能会导致查询性能问题；
• 视图通常是只读的，更新视图数据需要满足一定的条件。

无论如何，视图是数据库中非常重要的对象。接下来，我们了解一下如何维护视图。

创建视图

在 SQL 中，使用CREATE VIEW语句创建视图：

CREATE VIEW view_name
    AS select_statement;

其中，view_name 指定了视图的名称；select_statement 是视图的定义，视图可以基于多个表，也可以基于其他视图定义。

以下示例创建了名为 stock_clerks 的视图。它基于 employees 表创建，并且添加了 WHERE 条件，只包含职位为股票专员的员工相关信息。

CREATE VIEW stock_clerks
    AS
SELECT first_name, last_name, email, phone_number, hire_date, salary
  FROM employees
 WHERE job_id = 'ST_CLERK';

也可以为视图中的列指定自定义的名称，即在视图名称后加上新的列名：

CREATE VIEW stock_clerks(fname, lname, email, phone, hire_date, income)
    AS
SELECT first_name, last_name, email, phone_number, hire_date, salary
  FROM employees
 WHERE job_id = 'ST_CLERK';

视图定义中的查询语句可以包含任意复杂的多表连接、子查询、以及集合操作等。以下是一个多表连接的视图示例：

CREATE VIEW emp_info
    AS
SELECT d.department_name,j.job_title, e.first_name, e.last_name, e.email
  FROM employees e
  LEFT JOIN departments d ON (d.department_id = e.department_id)
  JOIN jobs j ON (j.job_id = e.job_id);

视图中的 ORDER BY

在 SQL 标准中，要求视图定义不能包含ORDER BY子句，因为视图自身并不存储数据；但许多数据库并不遵循该规则。

-- Oracle, MySQL, PostgreSQL and SQLite
CREATE VIEW emp_info2
    AS
SELECT d.department_name,j.job_title, e.first_name, e.last_name, e.email
  FROM employees e
  LEFT JOIN departments d ON (d.department_id = e.department_id)
  JOIN jobs j ON (j.job_id = e.job_id)
 ORDER BY d.department_name;

Db2 不允许视图定义中包含 ORDER BY 子句。
SQL Server 不允许视图定义中包含 ORDER BY 子句，除非指定了 TOP 、OFFSET 或者 FOR XML 选项。

使用视图

对于查询语句，视图可以像表一样作为数据源使用。以下查询语句使用了视图 stock_clerks。

SELECT hire_date,
       MAX(salary) AS max_salary
  FROM stock_clerks
 WHERE salary >= 2500
 GROUP BY hire_date
 ORDER BY max_salary DESC;

在数据库执行以上语句时，会使用视图的定义进行查询重写：

SELECT hire_date,
       MAX(salary) AS max_salary
  FROM (
  SELECT first_name, last_name, email, phone_number, hire_date, salary
    FROM employees
   WHERE job_id = 'ST_CLERK'
 )
 WHERE salary >= 2500
 GROUP BY hire_date
 ORDER BY max_salary DESC;

甚至进一步优化成以下形式：

SELECT hire_date,
       MAX(salary) AS max_salary
  FROM employees
 WHERE job_id = 'ST_CLERK' AND salary >= 2500
 GROUP BY hire_date
 ORDER BY max_salary DESC;

视图还有一个特性，称为可更新视图（Updatable View）。也就是可以针对视图执行INSERT、UPDATE、DELETE等数据操作，其结果是针对视图底层的数据表进行相应操作。

通常来说，可更新视图需要满足许多条件，只有基于单表的简单视图支持更新操作。我们将在下个系列文章《SQL 从出门到出师》中介绍高级视图。

SQLite 不支持可更新视图。

修改视图

在 SQL 标准中没有定义如何修改视图，只能删除后重新创建。不过，许多数据库都扩展了修改视图的语法。

这些扩展可以分为两类：扩展的CREATE命令以及单独的ALTER命令。

-- Oracle, MySQL, PostgreSQL and Db2
CREATE OR REPLACE VIEW view_name
    AS select_statement;

-- SQL Server
CREATE OR ALTER VIEW view_name
    AS select_statement;

SQLite 不支持修改视图，只能删除后重建。

以下示例创建名为 stock_clerks 的视图；如果它已经存在，替换原来的视图。

-- Oracle, MySQL, PostgreSQL and Db2
CREATE OR REPLACE VIEW stock_clerks
    AS
SELECT employee_id, first_name, last_name, email, hire_date
  FROM employees
 WHERE job_id = 'ST_CLERK';

-- SQL Server only
CREATE OR ALTER VIEW stock_clerks
    AS
SELECT employee_id, first_name, last_name, email, hire_date
  FROM employees
 WHERE job_id = 'ST_CLERK';

一些数据库还提供了单独的ALTER命令，用于修改视图。

-- MySQL and SQL Server
ALTER VIEW view_name
    AS select_statement;

Oracle、PostgreSQL、Db2 中的 ALTER VIEW 命令只能修改某些视图属性。

删除视图

使用 DROP VIEW 命令删除视图。

DROP VIEW stock_clerks;

某些数据库还支持扩展的删除命令。

-- MySQL, SQL Server, PostgreSQL and SQLite 
DROP VIEW IF EXISTS stock_clerks;

使用了 IF EXISTS 选项之后，即使视图不存在，也不会显示错误。

一些数据库还可以同时删除多个视图：

-- MySQL, SQL Server and PostgreSQL
DROP VIEW IF EXISTS 
emp_max_salary, emp_info, emp_info2;

数据库	简单视图	描述
Oracle	OK
MySQL	OK
SQL Server	OK	SQL Server 不允许视图定义中包含 ORDER BY，除非指定了 TOP 、OFFSET 或者 FOR XML 选项。
PostgreSQL	OK
Db2	OK	Db2 不允许视图定义中包含 ORDER BY。
SQLite	OK