第20章 创建和使用视图及异名

第21章 使用系统目录

第20章 创建和使用视图及异名

本章的重点包括：

什么是视图

如何使用视图

视图和安全

视图的存储

创建视图

结合视图

视图里的数据操作

什么是异名

管理异名

创建异名

删除异名

本章将介绍关于性能的一些知识，以及如何创建和删除视图，如何把视图用于安全管理，如何简化终端用户和报告的数据获取，最后还会讨论异名。

20.1 什么是视图

视图是一个虚拟表。也就是说，对于用户来说，视图在外观和行为上都类似表，但它不需要实际的物理存储。视图实际上是由预定义查询形式的表所组成的。举例来说，从表EMPLOYEE_TBL里创建一个视图，它只包含雇员的姓名和地址，而不是表里的全部字段。视图可以包含表的全部或部分记录，可以由一个表或多个表创建。

当创建一个视图时，实际上是在数据库里执行了一个SELECT语句，它定义了这个视图。这个SELECT语句可能只包含表里的字段名称，也可以包含各种函数和运算来操作或汇总给用户显示的数据。图20.1展示了视图的概念。

图20.1 视图

虽然视图并不占据实际的存储空间，但也被看作是一个数据库对象。视图与表之间的主要区别在于，表占据物理空间，而视图不需要物理空间，它只是从表里引用数据。

在数据库里，视图的使用方式与表是一样的，意味着我们可以像操作表一样从视图里获取数据。另外，我们还可以对视图里的数据进行操作，但存在着一定的限制。下面的小节将介绍视图的一些常见应用，以及视图在数据库里的保存方式。

警告：删除用于创建视图的表

如果用于创建视图的表被删除了，那么这个视图就不可访问了。如果对这个视图做查询，就会收到错误信息。

20.1.1 使用视图来简化数据访问

在有些情况下，通过数据库的归一化，或是数据库设计的过程，数据在表里的格式可能并不适合终端用户进行查询。这时，我们可以创建一系列的视图，让终端用户能够更简单地进行查询。举例来说，用户可能需要从数据库 learnsql 里查询雇员的薪水信息，但并不完全理解如何创建表EMPLOYEE_TBL和EMPLOYEE_PAY_TBL之间的结合。为了解决这个问题，我们可以创建一个视图来包含表的结合，让用户可以从这个视图获取数据。

20.1.2 使用视图作为一种安全角式

提示：视图可以被用作一种安全角式

使用视图可以限制用户只访问表里的特定字段或满足一定条件的记录（在定义视图的WHERE子句里指定）。

视图可以作为数据库里的一种安全角式。假设我们有一个表EMPLOYEE_TBL，它包含雇员姓名、地址、电话号码、紧急联系人、部门、职位、薪水或小时工资。在编写一些报告时，可能会有一些临时需求，要获取雇员的姓名、地址和电话号码。如果允许访问整个表，别人就可以看到每个雇员的收入是多少，这是我们所不允许的。为了防止这种情况发生，我们可以创建一个视图，让它只包含必要的信息：雇员姓名、地址和电话号码，并且让写报告的人可以使用这个视图，这样就可以避免他们访问表的其他敏感数据。

20.1.3 使用视图维护摘要数据

假如摘要数据报告所基于的表经常更新，或是报告经常被创建，使用视图来包含摘要数据就是个很好的选择。

举例来说，有一个表包含个人信息，比如居住的城市、性别、薪水和年龄。我们可以基于这个表来创建一个视图，统计每个城市的人员情况，比如平均年龄、平均薪水、男性总数、女性总数。在创建了视图之后，如果想获得这些信息，我们只需要对视图进行查询，而不需要使用复杂的SELECT语句。

利用摘要数据创建视图与从一个表或多个表创建视图的唯一区别就是使用了汇总函数。关于汇总函数的介绍请见第9章。

视图只保存在内存里，而且只需要保存其定义本身，这一点与其他数据库对象不同。视图由创建者或规划所有者所拥有。视图所有者自动拥有视图的全部权限，并且可以把视图的权限授予其他用户。对于视图来说，GRANT命令的GRANT OPTION权限的工作方式与表一样。关于权限的详细信息请见第19章。

20.2 创建视图

视图是通过CREATE VIEW语句创建的。我们可以从一个表、多个表或另一个视图来创建视图。为了创建视图，用户必须拥有适当的系统权限。

基本的CREATE VIEW语法如下所示：

下面的几个小节分别介绍使用CREATE VIEW语句创建视图的不同方法。

注意：ANSI SQL不包含ALTER VIEW语句

大多数数据库实现里提供了ALTER VIEW语句，但ANSI SQL里没有。举例来说，在老版本的MySQL里，我们可以使用REPLACE VIEW语句修改当前视图。但是，最新版本的 MySQL以及 SQL Server和 Oracle都支持ALTER VIEW语句。详细情况请参见具体实现的文档。

20.2.1 从一个表创建视图

我们可以从一个表创建视图。

语法如下所示：

创建视图的最简单方式是基於单个表的全部内容，范例如下：

下面的范例从基表里只选择指定的字段，从而减少了视图里的内容：

下面的范例展示了如何利用基表里的多个字段组合成视图里的一个字段。利用SELECT子句里的别名，我们把视图字段命名为NAME。

现在可以从视图NAMES里选择全部数据：

下面的范例展示如何基于一个或多个表创建包含摘要数据的视图：

现在，从这个摘要视图里选择数据：

通过使用包含摘要数据的视图，针对基表的SELECT语句可以得到简化。

20.2.2 从多个表创建视图

通过在SELECT语句里使用JOIN，我们可以从多个表创建视图。其语法如下：

下面是从多个表创建视图的范例：

在从多个表创建视图时，这些表必须在WHERE子句里通过共同字段实现结合。视图本身不过是一个SELECT语句而已，因此表在视图定义里的结合与在普通SELECT语句里是一样的。回忆一下，使用表的别名可以简化多表查询的可读性。

视图可以与表或其他视图相结合，其规则与表之间的结合一样。更多相关内容请见第13章。

20.2.3 从视图创建视图

使用下面的语法可以从一个视图创建另一个视图：

视图创建视图可以具有多个层次（视图的视图的视图，以此类推），允许的层次取决于具体实现。基于视图创建视图的唯一问题在于它们的可管理性。举例来说，基于 VIEW1 创建了VIEW2，又基于VIEW2创建了VIEW3。如果VIEW1被删除了，VIEW2和VIEW3也就不可用了，因为支持这些视图的底层数据不存在了。因此，我们需要始终很好地理解数据库里的视图，以及它们依赖于什么数据库对象（参见图20.2）。

图20.2展示了视图基于表和其他视图的情况。VIEW1和VIEW2基于TABLE，VIEW3依赖于VIEW1，VIEW4依赖于VIEW1和VIEW2，VIEW5依赖于VIEW2。根据它们的关系， 我们可以得出以下结论：

如果VIEW1被删除了，VIEW3和VIEW4就无效了；

如果VIEW2被删除了，VIEW4和VIEW5就无效了；

如果TABLE被删除了，这些视图就都无效了。

图20.2 视图依赖

注意：谨慎选择创建视图的方式

如果从基表和从另一个视图创建视图具有一样的难度和效率，那么我们首选从基表创建视图。

20.3 WITH CHECK OPTION

这是CREATE VIEW语句里的一个选项，其目的是确保全部的UPDATE和 INSERT语句满足视图定义里的条件。如果它们不满足条件，UPDATE或INSERT语句就会返回错误。WITH CHECK OPTION实际上通过查看视图定义是否被破坏来确保引用完整性。

下面是使用WITH CHECK OPTION创建视图的范例：

在这个范例里，WITH CHECK OPTION会确保视图的PAGER字段里不包含NULL值，因为视图定义所依赖的数据里不允许在PAGER字段里包含NULL值。

尝试在PAGER字段里插入一个NULL值：

在基于视图创建另一个视图时，WITH CHECK OPTION有两个选项：CASCADED和LOCAL，其中CASCADED是默认选项。CASCADED是ANSI标准语法。但是，Microsoft SQL Server和Oracle使用稍有不同的CASCADE关键字。在对基表进行更新时，CASCADED选项会检查所有底层视图、所有完整性约束，以及新视图的定义条件。LOCAL 选项只检查两个视图的完整性约束和新视图的定义条件，不检查底层的表。因此，使用CASCADED选项创建视图是更安全的作法，基表的引用完整性也得到了保护。

20.4 从视图创建表

我们可以从视图创建一个表，就像从一个表创建另一个表（或从一个视图创建另一个视图）一样。

语法如下：

注意：表与视图的细微差别

表与视图的主要区别在于表包含实际的数据、占据物理存储空间，而视图不包含数据，而且只需要保存视图定义（查询语句）。

首先，基于两个表创建一个视图：

接下来，基于前面这个视图创建一个表：

注意：在查询视图时使用ORDER BY子句

与在 CREATE VIEW 语句里使用 GROUP BY 子句相比，在查询视图的SELECT语句里使用ORDER BY子句更简单、效果更好。

最后，从这个表里选择数据：

20.5 视图与ORDER BY子句

CREATE VIEW语句里不能包含ORDER BY子句，但是GROUP BY子句用于CREATE VIEW语句时，可以起到类似ORDER BY子句的作用。

下面是在CREATE VIEW语句里使用GROUP BY子句的范例：

如果从这个视图里选择全部数据，它们是以字母顺序排列的（因为根据NAME进行了分组）。

20.6 通过视图更新数据

在一定条件下，视图的底层数据可以进行更新：

视图不包含结合；

视图不包含GROUP BY子句；

视图不包含UNION语句；

视图不包含对伪字段ROWNUM的任何引用；

视图不包含任何组函数；

不能使用DISTINCT子句；

WHERE子句包含的嵌套的表表达式不能与FROM子句引用同一个表。

视图可以执行INSERT、UPDATE和DELETE等语句，

关于UPDATE命令的语法请见第14章。

20.7 删除视图

DROP VIEW命令用于从数据库里删除视图，它有两个选项：RESTRICT和CASCADE。如果使用了RESTRICT选项进行删除操作，而其他视图在约束里有所引用，删除操作就会出错。如果使用了CASCADE选项，而且其他视图或约束被引用了，DROP VIEW也会成功， 而且底层的视图或约束也会被删除。范例如下：

20.8 嵌套视图对性能的影响

在查询中使用视图，与使用表有着相同的性能特性。因此，用户必须意识到，在视图中隐藏复杂的逻辑会导致系统需要查询底层表来分析并组合数据。在进行性能调整的时候，视图需要和其他SQL语句一样被调整。如果构成视图的查询没有经过事先设计，那么视图本身就会对性能产生影响。

此外，有些用户将查询分解为各种视图构成的多个单元，以便简化复杂的查询。这种方法在简化复杂逻辑方面看起来是个好办法，但却会降低性能。因为搜索发动机需要分析每一层的视图，来确定为了完成搜索需要进行哪些工作。

嵌套的层数越多，搜索发动机为了获得一个执行计划而需要进行的分析工作就越多。实际上，大多数搜索发动机无法确保获得一个完美的执行计划，而只能保证执行一个耗时最短的计划。因此，最好的方法就是，尽量减少代码中的嵌套层数，并且测试并调整创建视图所用到的语句。

注意：异名不属于ANSI SQL标准

异名并不属于ANSI SQL标准，但由于多个主流实现都在使用它，我们最好还是在此讨论一下。关于异名的使用请查看具体实现的文档。MySQL不支持异名，但我们可以使用视图来实现同样的功能。

20.9 什么是异名

异名就是表或视图的另一个名称。我们创建别名通常是为了在访问其他用户的表或视图时不必使用完整限制名。异名可以创建为PUBLIC或PRIVATE，PUBLIC的异名可以被数据库里的其他用户使用，而PRIVATE异名只能被所有者和拥有权限的用户使用。

异名由数据库管理员（或某个指定的人员）或个人用户管理。由于异名有两种类型：PUBLIC和PRIVATE，在创建异名时可能需要不同的系统级权限。一般来说，全部用户都可以创建PRIVATE异名，而只有数据库管理员（DBA）或被授权的用户可以创建PUBLIC异名，详细情况请参见具体实现的文档。

20.9.1 创建异名

创建异名的一般语法如下所示：

下面的范例为表CUSTOMER_TBL创建一个异名：CUST，之后我们再引用这个表就不用输入完整的表名了。

异名的另一个常见应用是，表的所有者给表创建一个异名，这样其他有权限访问这个表的用户不必在表的名称前面添加所有者名称也可以引用这个表了。

20.9.2 删除异名

删除异名与删除其他数据库对象很类似，一般语法如下所示：

范例如下：

20.10 小结

本章讨论了SQL里两个重要特性：视图和异名。在很多情况下，这些能够对关系型数据库用户有所帮助的特性并没有得到充分应用。视图就是一个虚拟表——外观与行为都像表，但不像表那样占据物理空间。视图实际上是由对表和其他视图的查询所定义的，其主要用于限制用户能够查看的数据、简化数据和进行数据摘要。视图可以基于视图创建，但要注意不要嵌套太多的层次，以避免失去对它们的管理控制。创建视图时有多个选项，有些实现还具有自己特殊的选项。

异名也是数据库里的对象，它代表着其他对象。我们可以创建一个短的异名来代表名称较长的对象，或是代表被其他用户所拥有的对象，从而简化数据库里对象名称的使用。异名有两种类型：PUBLIC 和 PRIVATE。PUBLIC 异名可以被数据库里的全部用户访问，而PRIVATE异名只被单个用户访问。DBA通常负责创建PUBLIC异名，而个人用户通常创建自己的PRIVATE异名。

20.11 问与答

问：视图怎么能包含数据而又不占据存储空间呢？

答：视图不包含数据，它是一个虚拟表，或是一个存储的查询。视图所需的空间只是定义语句所需要的。

问：如果视图所基于的视图被删除了，它会怎么样？

答：这个视图就无效了，因为底层的数据已经不存在了。

问：在创建异名时，其名称有什么限制？

答：这取决于具体的实现。在大多数主流实现里，异名的命名规则与数据库里表和其他对象的命名规则一样。

20.12 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习有助于把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

20.12.1 测验

1．在一个基于多个表创建的视图里，我们可以删除记录吗？

2．在创建一个表时，所有者会自动被授予适当的权限。在创建视图时也是这样吗？

3．在创建视图时，使用什么子句对数据进行排序？

4．在基于视图创建视图时，使用什么选项检查完整性约束？

5．在尝试删除视图时，由于存在着多个底层视图，操作出现了错误。这时怎样做才能删除视图？

20.12.2 练习

1．编写一个语句，基于表EMPLOYEE_TBL的全部内容创建一个视图。

2．编写一个语句创建一个包含摘要数据的视图，显示表 EMPLOYEE_TBL 里每个城市的平均小时工资和平均薪水。

3．再次创建练习2中的摘要数据视图，但不要使用表EMPLOYEE_TBL，而是使用练习1中所创建的视图。比较两个结果。

4．使用练习2中创建的视图来创建一个名为EMPLOYEE_PAY_SUMMARIZED的表。想办法确定视图和表拥有相同的数据。

5．编写SQL语句来删除表和刚刚创建的视图。

第21章 使用系统目录

本章的重点包括：

什么是系统目录

如何创建系统目录

系统目录里包含什么数据

系统目录表的范例

查询系统目录

更新系统目录

本章介绍系统目录，在某些关系型数据库的实现里，这也被称为数据目录。本章将介绍系统目录的作用与内容，以及如何对它进行查询来获得数据库的信息。每种主流实现都具有某种形式的系统目录，保存了关于数据库本身的信息。本章中将展示书中所涉及的不同实现里系统目录所包含的元素。

21.1 什么是系统目录

系统目录是一些表和视图的集合，它们包含了关于数据库的信息。每个数据库都有系统目录，其中定义了数据库的结构，还有数据库所包含数据的信息。举例来说，用于数据库里所有表的数据目录语言（DDL）就保存在系统目录里。图21.1展示了数据库的系统目录。

从图21.1可以看出，系统目录实际上是数据库的组成部分。数据库里包含的是对象，比如表、索引和视图。系统目录基本上就是一组对象，包含了定义数据库里其他对象的信息、数据库本身的结构以及其他各种重要信息。

在具体实现里，系统目录的内容会被划分为对象的逻辑组，以表的形式供数据库管理员（DBA）和其他数据库用户访问。举例来说，某个用户可能需要查看自己具有的数据库权限，但不需要知道数据库内部结构或进程。普通用户通常可以对系统目录进行查询来获得关于所拥有对象和权限的信息，而 DBA 需要能够获取数据库里任何结构或事件的信息。在某些实现里，有些系统目录的对象只能由DBA访问。

图21.1 系统目录

系统目录对于 DBA 或其他需要了解数据库结构和特征的用户来说都是非常重要的。在数据库用户没有使用GUI界面时，它尤为重要。系统目录不仅允许DBA和用户，而且允许数据库服务程序本身对其进行维护。

注意：不同实现的系统目录有所不同

每个实现对系统目录的表和视图都具有自己的命名规则。名称本身并不重要，重要的是功能，以及它包含什么内容、如何检索这些内容。

21.2 如何创建系统目录

系统目录或者是在数据库创建时自动创建的，或是由 DBA 在数据库创建之后立即创建的。举例来说，Oracle数据库会运行一系列由厂商提供的预定义SQL脚本，创建系统目录里全部的表格和视图。系统目录的表格和视图是由系统所拥有的，不属于任何规划。举例来说，在 Oracle 里，系统目录的所有者是一个名为 SYS 的用户，它对数据库具有完全的权限。在Microsoft SQL Server里，SQL服务程序的系统目录位于master数据库里。在MySQL里，系统目录位于mysql数据库里。系统目录保存的实际位置请查看具体实现的文档。

21.3 系统目录里包含什么内容

系统目录里包含多种信息，可以被很多用户访问，但有时不同用户的使用目的并不一样。

系统目录包含的内容有：

用户账户和默认设置；

权限和其他安全信息；

性能统计；

对象大小估计；

对象变化；

表结构和存储；

索引结构和存储；

数据库其他对象的信息，比如视图、异名、触发器和存储过程；

表约束和引用完整性信息；

用户会话；

审计信息；

内部数据库设置；

数据库文件的位置。

系统目录由数据库服务程序维护。举例来说，当一个表被创建时，数据库服务程序把数据插入到系统目录里适当的表或视图。当表的结构被修改时，系统目录里相应的对象也被更新。下面这些小节分别介绍系统目录里所包含的信息。

21.3.1 用户数据

关于个人用户的全部信息都保存在系统目录里：用户具有的系统和对象权限、用户拥有的对象、用户不拥有但能够访问的对象。用户可以通过查询访问用户表或视图。关于系统目录对象的详细情况请参见具体实现的文档。

21.3.2 安全信息

系统目录也保存安全信息，比如用户标识、加密的密码、各种权限和权限组。有些实现里包含审计表，可以跟踪数据库发生的事件，包括由谁引发、何时等信息。在很多实现里，利用系统目录还可以密切监视数据库用户会话。

21.3.3 数据库设计信息

系统目录包含关于数据库的信息，包括数据库的创建日期、名称、对象大小估计、数据文件的大小和位置、引用完整性信息、索引、每个表的字段信息和属性。

21.3.4 性能统计

性能统计一般也在系统目录里，包括关于 SQL 语句性能的信息，比如优化器执行 SQL语句的时间和方法。其他性能信息还有内存分配和使用、数据库里剩余空间、控制表格和索引碎片的信息。利用这些信息可以调整数据库，重新安排SQL查询，重新设计访问数据的方法，从而得到更好的整体性能和更快的SQL查询响应。

21.4 不同实现里的系统目录表格

每个实现都有一些表格和视图来构成系统目录，有些还分为用户级、系统级和DBA级。

关于系统目录表格的详细情况请参见具体实现的文档，表21.1列出了主流实现的范例。

表21.1 主流实现的系统目录对象

上面只是列出了书中涉及的一些关系型数据库实现里的部分系统目录对象，主要是比较类似的对象。每个实现在系统目录内容的组织方面都是很独特的。

21.5 查询系统目录

我们可以像对待数据库里的其他表格和视图一样使用 SQL 查询系统目录里的表格和视图。用户通常只能查询与用户相关的表，不能访问系统表，后者通常只能由被授权的用户访问，比如DBA。

创建查询从系统目录里获取数据与对数据库的其他表格进行操作是一样的。举例来说，下面的查询从Microsoft SQL Server的表SYS.TABLES里返回全部记录：

下面的查询返回数据库里的全部用户账户，它运行于MySQL系统数据库上：

注意：有关下面的范例

下面的范例使用MySQL的系统目录。在此选择使用MySQL没有特别的意图，只是选择了本书所涉的一种数据库实现而已。

下面的范例列出我们的learnsql规划里的全部表格，它运行于information_schema上：

警告：不要手动修改系统目录中的表

不要以任何方式直接操作系统目录里的表（只有DBA能够操作系统目录的表），否则可能会破坏数据库的完整性。与数据库结构有关的信息，以及数据库的全部对象都保存在系统目录中，它通常是与数据库里的其他数据隔离的。有些实现，例如Microsoft SQL Server，不允许用户手动修改系统目录中的表，以确保系统的完整性。

下面的查询返回数据库用户BRANDON的全部系统权限：

注意：本小节所涉信息有限

本小节范例中返回的信息与实际系统目录相比简直是九牛一毛。在实际工作中，最好把系统目录返回的信息输出到文件，打印出来作为参考。关于系统目录里表格以及表格内字段的详细情况请参见具体实现的文档。

21.6 更新系统目录对象

系统目录只能执行查询操作——DBA也是如此。系统目录的更新是由数据库服务程序自动完成的。举例来说，当用户发出CREATE TABLE命令时，数据库里会创建一个表，数据库服务程序就会把创建这个表的DDL放到系统目录里适当的表里。

系统目录里的表从不需要进行手工更新，即使用户有这个能力也不需要。数据库服务程序会根据数据库里发生的行为对系统目录进行相应的更新，如图21.2所示。

图21.2 更新系统目录

21.7 小结

本章介绍了关系型数据库的系统目录。从某种意义来说，系统目录是数据库里的数据库，包含了与其所在数据库相关的全部信息，用于维护数据库的整体结构、跟踪数据库里发生的事件与改变、为数据库整体管理提供各种信息。系统目录只能执行查询操作，没有用户应该对系统目录进行直接修改。然而，在数据库结构本身发生任何一次变化时，比如创建表，数据库服务程序都会自动对系统目录进行更新。

21.8 问与答

问：作为一名数据库用户，我知道可以获取我的对象的信息，但如何才能得到其他用户的对象的信息呢？

答：用户可以利用一组表或视图对系统目录的大部分信息进行查询，其中就包括所访问对象的信息。为了了解其他用户的情况，我们需要查看包含相应内容的系统目录。举例来说，在Oracle里，我们可以查看系统目录DBA_TABLES和DBA_USERS。

问：如果用户忘记了密码，DBA是否可以通过查询某个表而获得这个密码呢？

答：是，也不是。密码被保存在一个系统表格里，通常是加密的，所以即使 DBA 也不能读取这个密码。如果用户忘记了密码，密码就必须被重置，而这是DBA能够轻易完成的。

问：如何了解系统目录表格里包含了什么字段？

答：系统目录表格可以像其他表格一样被查询，所以只要查询适当的表就可以获得这个信息。

21.9 实践

下面的内容包含一些测试问题和实战练习。这些测试问题的目的在于检验对学习内容的理解程度。实战练习有助于把学习的内容应用于实践，并且巩固对知识的掌握。在继续学习之前请先完成测试与练习，答案请见附录C。

21.9.1 测验

1．在某些实现里，系统目录也被称为什么？

2．普通用户能够更新系统目录吗？

3．在Microsoft SQL Server里，哪个系统表格包含了数据库里视图的信息？

4．谁拥有系统目录？

5．Oracle数据对象ALL_TABLES和DBA_TABLES之间的区别是什么？

6．谁修改系统表格？

21.9.2 练习

1．在第19章中，我们查看了mysql里的系统表格。现在查看一些本章中讨论过的系统表，复习一下这些表格。

2．在提示符下，输入命令来获取以下信息：

所有表的信息。

所有视图的信息。

数据库中所有的用户名。

3．使用多个系统表来编写查询，获得learnsql数据库中的所有用户及其权限。