第七章　数据库的高级使用

第七章　数据库的高级使用 ——视图和索引的应用

内容概要 • 本章我们将介绍实现和管理视图、索引的方法与技巧，以及使用视图和索引所带来的好处。

任务的提出 “晓灵呀，你来的正好，我正要给你布置新任务呢，就是让你去创建视图和索引”，郝老师示意晓灵坐下，慢慢说。“咱们下面就要学习视图和索引的创建及使用了，视图确实是你说的那样，它是一张虚表。虚表意味着在视图中并没有实际的数据，数据库中只保存着视图的定义语句，可是视图的功能还是很大的。晓灵咱们先看看，在咱们的管理系统中设计了9张表格，学生、教师、课程、成绩等等信息都分布在不同的表格中，表与表之间通过主外键进行联系。我们如果在这么多的表中去查询一些数据非常困难。而视图就给我们提供了一种重新整合数据的手段，我们可以通过创建视图来将我们经常需要查看的有联系的一些数据放在一起，这样我们再查阅的时候就好向是对一张表进行操作，这样是不是很方便呢？”

7.2 视图的功能 • 视图是一个虚拟表，其结构和数据是建立在对表的查询基础上的。和表一样，视图也是包括几个被定义的数据列和多个数据行，但就本质而言这些数据列和数据行来源于其所引用的基本表。所以视图不是真实存在数据的基本表而是一张虚表，视图所对应的数据并不真实地以视图结构存储在数据库中，而是存储在视图所引用的表中。

视图优点 • （1）视点集中。使用户只关心感兴趣的某些特定数据和他们所负责的特定任务。这样通过只允许用户看到视图中所定义的数据而不是视图引用表中的数据而提高了数据的安全性。 • （2）简化操作。视图大大简化了用户对数据的操作。因为在定义视图时，若视图本身就是一个复杂查询的结果集，这样在每一次执行相同的查询时，不必重新写这些复杂的查询语句，只要一条简单的查询视图语句即可。可见视图向用户隐藏了表与表之间的复杂的连接操作。 • （3)定制数据。视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要。 • （4）合并分割数据。在有些情况下，由于表中数据量太大，故在表的设计时常将表进行水平分割或垂直分割，但表的结构的变化将对应用程序产生不良的影响。如果使用视图就可以重新保持表原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。 • （5）安全性。视图可以作为一种实现安全机制的手段。通过视图用户只能查看和修改（注意是有限制的）他们所能看到数据。其他数据库或表不可见也不能访问。如果某一用户想要访问视图的结果集，必须要授予其访问权限才可以实现。视图所引用基本表与视图的权限设置互不影响。

7.2.1 创建视图 • 在创建视图之前，必须要注意以下几点 • 只能在当前数据库中创建视图，尽管被引用的表或视图可以存在于其他的数据库内。 • 一个视图最多可以引用1024列 • 视图的命名必须符合SQL Server的标识符定义规则，视图的名称必须唯一，不能与表名相同 • 可以在视图的基础上再创建视图 • 不能将规则、默认对象绑定在视图上 • 不能将触发器与视图关联 • 定义视图的查询语句中不能包括ORDER BY、COMPUTER、COMPUTER BY等子句 • 默认情况下，视图中的列继承它们在基表中的名字

视图创建方法 • 1. 使用企业管理器创建视图 • 2. 使用T-SQL语句创建视图 CREATE VIEW view_name [ ( column [ ,...n ] ) ] [ WITH < view_attribute > [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] < view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA } 主要参数说明如下 view_name：是视图的名称。 column：是视图中的列名。只有在下列情况下，才必须命名 CREATE VIEW 中的列：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。还可以在 SELECT 语句中指派列名。如果未指定 column，则视图列将获得与 SELECT 语句中的列相同的名称。 AS：是视图要执行的操作。 select_statement：是定义视图的 SELECT 语句。 WITH CHECK OPTION:强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。通过视图修改行时，WITH CHECK OPTION 可确保提交修改后，仍可通过视图看到修改的数据。 WITH ENCRYPTION:表示 SQL Server 加密包含 CREATE VIEW 语句文本的系统表列

【任务7.1】创建一个视图，包含学生信息表中学生的学号，姓名，班级和学习成绩表中该学生的课程号和成绩，且成绩大于90。【任务7.1】创建一个视图，包含学生信息表中学生的学号，姓名，班级和学习成绩表中该学生的课程号和成绩，且成绩大于90。 • CREATE VIEW V_chengji • AS • SELECT student.sName, student.sID, student.sSex, student.sBanji, grade.kcID, grade.gradeNum • FROM student CROSS JOIN grade • WHERE (grade.gradeNum > 90)

7.2.3 修改视图的定义 使用企业管理器修改视图 • 使用企业管理器修改视图的方法非常简单，视图定义窗口，对视图定义语句进行修改，然后保存即可。 • ☺注意：如果在视图初期定义时设置了加密属性，那么我们无法看到视图的定义语句，只能将视图删除，进行重新定义。

使用T-SQL语句修改视图 ALTER VIEW view_name [ WITH < view_attribute > [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] < view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

【任务7.2】例如修改前面创建的视图，新曾一列专业【任务7.2】例如修改前面创建的视图，新曾一列专业 • ALTER VIEW V_chengji • AS • SELECT student.sName, student.sID, student.sSex, student.sZhuanye, student.sBanji,grade.kcID, grade.gradeNum • FROM student CROSS JOIN grade • WHERE (grade.gradeNum > 90)

7.2.5 删除视图 • 1. 使用企业管理器删除视图 • （1）在企业管理器中，展开服务组，在展开服务器 • （2）展开数据库,在展开视图所在的数据库 • （3）选则视图目录，在右边窗口的视图列表中用鼠标右键单击要删除的视图，在弹出的快捷菜单中选择Delete命令 • （4）如果确认要删除视图，可以单击show Depences……按钮查看数据库中与该视图有依赖关系的其他数据库对象。

2. 使用T-SQL 语句删除视图 • 使用DROP VIEW语句实现对视图的删除，语法为 • DROP VIEW {VIEW}[,…] • 在该语句中，一次可以删除多个视图。

7.3 索引 • 在数据库的管理中，为了迅速地从庞大的数据库中找到所需要的数据，提供了类似书籍目录作用的索引技术。通过在数据库中对表建立索引，则可以大大加快数据的检索速度。在数据查询时，如果表的数据量很大且没有建立索引，SQL server将从第一条记录开始，逐行扫描整个表，直到找到符合条件的数据行。这样，系统在查询上的开销将很大，且效率会很低。如果建立索引，SQL Server将根据索引的有序排列，通过高效的有序查找算法找到索引项，然后通过索引项直接定位数据，从而加快查找速度。

7.3.1索引的基本概念 • 索引是一种树状结构，其中存储了关键字和指向包含关键字所在记录的数据页的指针。它是在通过数据库管理和使用数据时，为了提高数据的访问速度，缩短数据的查找时间而采用的一种数据库技术。索引是基本表的目录，通过这个目录就可以高效地找到需要的数据。一个基本表可以根据需要建立多个索引，以提供多种存取路径，加快数据查询速度。基本表文件和索引文件一起构成了数据库系统的内模式。

创建索引好处 • 第一，通过创建惟一性索引，可以保证每一行数据的唯一性。 • 第二，可以大大加快数据的检索速度，这也是索引的最主要的原因。 • 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 • 第四，在使用ORDER BY和GROUP BY子句进行数据检索时，可以显著减少查询中分组和排序的时间。 • 第五，使用索引可以在查询数据的过程中使用优化隐藏器，提高系统的性能。

创建索引原则： • 在经常需要查询的属性列上创建索引； • 在主键上创建索引； • 在经常用于连接运算的属性列上创建索引； • 在经常需要根据范围进行查询的属性列上创建索引，因为索引己经排序，其指定的范围是连续的； • 在经常需要排序的列上创建索引，因为索引己经排序，这样查询可以利用索引的排序，加快排序查询时间； • 在常用于WHERE子句中的属性列上创建索引。

7.3.1.2 索引的类型 • 根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种基本类型：一种是数据表中数据的物理顺序与索引顺序相同的聚集索引；另一种是数据表中数据的物理顺序与索引顺序不相同的非聚集索引。

7.3.2 实现索引 • 可以通过以下三种方法来实现索引： • 通过主键约束或唯一性约束间接实现索引 • 使用SQL Server企业管理器创建索引 • 使用T-SQL语句创建索引

7.3.2.3 使用T-SQL语句实现索引 • 使用CREATE INDEX语句可以创建各种类型的索引，其语法格式如下： CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON < table_name | view_name > (column [ ASC | DESC ] [ ,...n ] ) [ ON filegroup_name | default ]

参数说明： • UNIQUE：为表或视图创建唯一索引。 • CLUSTERED：为表或视图创建聚集索引。 • NONCLUSTERED：为表或视图创建非聚集索引，系统默认创建的索引为非聚集索引。 • index_name：索引的名称。索引名称在表或视图中必须唯一，但在数据库中不必唯一。索引名必须遵循标识符命名规则。 • table_ name：要为其建立索引的基本表的名称。 • view_ name：要为其建立索引的表或视图的名称。必须使用SCHEMABINDING定义视图，才能为视图创建索引。并且必须先为视图创建唯一的聚集索引，才能为该视图创建非聚集索引。 • column：索引所基于的一或多个数据列。指定两个或多个列名，可为指定列的组合值创建组合索引。在table_or_view_name后的括号中列出组合索引中要包括的列（按排序优先级排列）。一个组合索引键中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中，且其数据类型的长度之和不能超过900字节。 • ASC | DESC：确定特定索引列的排序方向为升序或降序。默认值为ASC。

【任务7.4】将课程信息表中课程编号列创建为聚集索引。【任务7.4】将课程信息表中课程编号列创建为聚集索引。 • CREATE CLUSTERED INDEX IX_ Course • ON Course (kcID) • 【任务7.5】为班级信息表中的班级名称列创建唯一的非聚集索引。该索引将强制插入该数据列中的数据具有唯一性，保证了班级名称不重名。 • CREATE UNIQUE INDEX AK_ Class • ON Classe(ClsName)

7.3.3 删除索引 • 删除索引的语法格式： • DROP INDEX ‘table.index | view.index’ [,…n] • 其中table | view用于指定索引裂所在的表或索引视图；index用于指定要删除的索引名称。需要注意的是DROP INDEX命令不能删除由CREATE TABLE或ALTEX TABLE命令创建的主键或唯一性约束，也不能删除系统表中的索引。

【任务7.7】删除任务7.4创建的索引 • DROP INDEX IX_ Course ON Course

第七章 数据库的高级使用