1、数据库的基本表是按照数据库设计人员的观点设计的，并不一定符合用户的需求。SQL Server 2005可以根据用户需求重新定义表的数据结构，这种数据结构就是视图。 在数据库系统中，为了迅速地从庞大的数据库中找到所需要的数据，SQL Server 2005提供了类似书的目录作用的索引技术。通过对数据库中表设置索引，可以大大加快数据的检索速度。SQL Server 2005还提供了一种称为游标的机制，来处理数据。 本章主要介绍SQL Server 2005数据库系统视图的创建以及使用，索引类型及创建、使用，游标的创建以及使用。,第9章 视图、索引和游标,9.1 视图 9.2 索引 9.3 游标,第
2、9章 视图、索引和游标,9.1.1 视图概述 视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。视图不是真实存在的基础表而是一张虚表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。 1. 视图的优点 使用视图有很多优点，主要表现在： 1) 为用户集中数据，简化用户的数据查询和处理。使得分散在多个表中的数据，通过视图定义在一起。 2) 简化操作，屏蔽了数据库的复杂性。 3) 重新定制数据，使得数据便于共享。 4) 合并分割数据，有利于数据输出到应用程序中。 5) 简化了用户权限的管理，增加了安全性。,9.1 视图,2. 视图的使用范围
3、 视图通常用来集中、简化和自定义每个用户对数据库的不同 认识。通常在以下情况下使用视图： 着重于特定数据。简化数据操作。 3) 自定义数据。 4) 数据的导入与导出。 5) 跨服务器组合分区数据库,9.1 视图,9.1.2 视图的类型 在SQL Server 2005中，视图可以分为标准视图、索引视图和分区视图。 标准视图组合了一个或多个表中的数据，用户可以使用标准视图对数据库进行查询、修改、删除等基本操作。 索引视图是被具体化了的视图，即它已经过计算并存储。可以为视图创建索引，即对视图创建一个惟一的聚集索引。索引视图可以显著提高某些类型查询的性能。索引视图尤其适于聚合许多行的查询。但它们不太
4、适于经常更新的基本数据集。 分区视图在一台或多台服务器间水平连接一组成员表中的分区数据。这样，数据看上去如同来自于一个表。联接同一个SQL Server实例中的成员表的视图是一个本地分区视图。,9.1 视图,9.1.3 创建视图 在创建视图前应考虑如下准则： 1) 只能在当前数据库中创建视图。 2) 视图名称必须遵循标识符的规则，且对每个架构都必须惟一。 3) 用户可以对其他视图创建视图。 5) 不能将AFTER触发器与视图相关联，只有INSTEAD OF触发器可以与之相关联。 6) 定义视图的查询不能包含COMPUTE子句、COMPUTE BY子句或INTO关键字。 7) 定义视图的查询不能
5、包含ORDER BY子句，除非在SELECT语句的选择列表中还有一个TOP子句。 8) 定义视图的查询不能包含指定查询提示的OPTION子句。 9) 定义视图的查询不能包含TABLESAMPLE子句。,9.1 视图,10) 不能为视图定义全文索引定义。 11) 不能创建临时视图，也不能对临时表创建视图。 12) 不能删除参与到使用SCHEMABINDING子句创建的视图中的视图、表或函数，除非该视图已被删除或更改而不再具有架构绑定。另外，如果对参与具有架构绑定的视图的表执行ALTER TABLE语句，而这些语句又会影响该视图的定义，则这些语句将会失败。 13) 下列情况下必须指定视图中每列的名
6、称： 视图中的任何列都是从算术表达式、内置函数或常量派生而来。 视图中有两列或多列原应具有相同名称。 希望为视图中的列指定一个与其源列不同的名称。,9.1 视图,在SQL Server 2005中创建标准视图主要有两种方式：一种 方式是在SQL Server Management Studio中使用向导创 建视图，另一种方式是通过在查询窗口中执行T-SQL语句创 建视图。T-SQL提供了视图创建语句CREATE VIEW。其语 法格式如下： CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statemen
7、t ; WITH CHECK OPTION ,9.1 视图,组成视图的属性列名,全部省略或全部指定，没有第3种选择。 省略: 视图属性列由子查询中SELECT目标列中的诸字段组成 明确指定视图的所有列名: (1) 某个SELECT目标列是集函数或列表达式 (2) 多表连接时选出了几个同名列作为视图的字段（注：在SQL Server 2005中可省略） (3) 需要在视图中为某个列启用新的更合适的名字,【例1】创建学生_课程_分数视图，包括计算机系的学生的学号、姓名，和他们选修的课程名、分数。 CREATE VIEW stu_sc AS SELECT Student.sno,Cname,Grad
8、e FROM Student,SC,Course WHERE student.sno=sc.sno and o=o and Sdept= CS 【例2】带WITH CHECK OPTION的视图 CREATE VIEW CS_student AS SELECT Student.sno,Sname,Sage FROM Student WHERE Sdept= CS WITH CHECK OPTION,9.1 视图,WITH CHECK OPTION 透过视图进行增删改操作时，不得破坏视 图定义中的谓词条件（即子查询中的条件表达式）,带表达式的视图,例3 定义一个反映学生出生年份的视图。CREAT
9、E VIEW BT_S(Sno，Sname，Sbirth)AS SELECT Sno，Sname，2000-SageFROM Student 设置一些派生属性列, 也称为虚拟列-Sbirth 带表达式的视图必须明确定义组成视图的各个属性列名,建立含分组视图,例4 将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型CREAT VIEW S_G(Sno，Gavg)AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；,9.1.4 查询视图 视图创建完毕，就可以如同查询基本表一样查询视图了。可以在SQL Server Managemen
10、t Studio中选中要查询的视图并打开，浏览该视图查询的所有数据。也可以在查询窗口中执行T-SQL语句查询视图。 【例5】查询stu_sc视图，统计“数据库”课程的总分和平均分。 SELECT SUM(Grade) 总分,平均分=AVG(Grade) FROM stu_sc WHERE Cname=数据库 【例6】在S_G视图中查询平均成绩在80分以上的学生学号和平均成绩 SELECT * FROM S_G WHERE Gavg=80；,9.1 视图,9.1.5 更新视图 通过更新视图数据（包括添加、修改和删除）可以修改基本表数据。但并不是所有的视图都可以更新，只有对满足可更新条件的视图才能
11、进行更新。 只要满足下列条件，即可通过视图修改基础基表的数据： 1) 任何修改（包括UPDATE、INSERT和DELETE语句）都只能引用一个基表的列。更新不能涉及多个表。 2) 视图中被修改的列必须直接引用表列中的基础数据。不能通过任何其他方式对这些列进行派生，如通过聚合函数、计算（如表达式计算）、集合运算等。 3) 被修改的列不受GROUP BY、HAVING、DISTINCT或TOP子句的影响。 即使是可更新视图，也不能随意更新数据。如果视图所依赖的基本表有 多个时，不能向该视图添加数据，因为这将影响多个基本表。修改数据 时，若视图依赖于多个基本表，那么一次修改只能修改一个基本表中的
12、数据。删除数据时，若视图依赖于多个基本表，那么不能通过视图删除 数据。,9.1 视图,更新视图（续）,例7 将信息系学生视图stu_sc中学号为95001的学生姓名改为“李永”。 UPDATE stu_sc SET Sname= 李永 WHERE Sno= 95001；,更新视图（续）,例8 向信息系学生视图CS_Student中插入一个新的学生记录：95029，赵新，20岁 INSERT INTO CS_Student VALUES(95029,赵新,20,CS)； Sdept的值必为CS，为什么？,更新视图（续）,例：视图S_G为不可更新视图。 对于如下更新语句： UPDATE S_G S
13、ET Gavg=90 WHERE Sno= 95001；,9.1.6 修改视图定义 修改视图定义，与修改基本表结构不一样。修改基本表结构是指重新定义列名、属性、约束等，而修改视图定义是指修改视图的指定列的列名、别名、表名、是否输出、顺序类型等属性。 可以在SQL Server Management Studio中选中要修改的视图并修改，就能修改视图定义了。也可以在查询窗口中执行T-SQL语句修改视图定义。T-SQL提供了视图创建语句ALTER VIEW。其语法格式如下： ALTER VIEW schema_name . view_name ( column ,.n ) WITH ,.n AS
14、select_statement ; WITH CHECK OPTION ,9.1 视图,【例9】修改【例1】创建的stu_sc视图。 ALTER VIEW stu_sc (sID,Sname,Cname,Grade) AS SELECT Student.sno,Sname,Cname,Grade FROM Student,SC,Course WHERE student.sno=sc.sno and o=o and Sdept= CS,9.1 视图,9.1.7 删除视图 如果不需要视图，则可以将视图删除。可以在SQL Server Management Studio中选中要删除的视图并删除，就
15、能删除视图。也可以在查询窗口中执行T-SQL语句删除视图。T-SQL提供了视图删除语句DROP VIEW。其语法格式如下： DROP VIEW view_name 【例10】删除视图view_1。 DROP VIEW view_1 GO,9.1 视图,含子查询的视图,例11：视图GOOD_SC(修课成绩在平均成绩之上的元组)CREATE VIEW GOOD_SCAS SELECT Sno，Cno，GradeFROM SCWHERE Grade (SELECT AVG(Grade)FROM SC)；,索引包含从表或视图中一个或多个列生成的键，以及映射到 指定数据的存储位置的指针。通过创建设计良好
16、的索引以支 持查询，可以提高查询性能。对于包含SELECT、UPDATE 或DELETE语句的各种查询，索引会很有用。 在数据库系统中建立索引主要有以下作用： 1) 快速存取数据。 2) 保证数据的一致性。 3) 实现表与表之间的参照完整性。 4) 在使用GROUP BY、ORDER BY子句进行查询时，利用索引可以减少排序和分组的时间。,9.2 索引,9.2.1 索引的分类 如果一个表没有创建索引，则数据行不按任何特定顺序存储，这种结构称为堆集。 SQL Server 2005支持在表中任何列（包括计算列）上定义索引。索引可以是惟一的，即索引列不会有两行记录相同，这样的索引称为惟一索引。索引
17、也可以是不惟一的。如果索引是根据单列创建的，这样的索引称为单列索引。根据多列组合创建的索引称为复合索引。 按索引的组织方式的不同，可以将索引分为聚集索引和非聚集索引。 1. 聚集索引 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。 2. 非聚集索引 非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。 聚集索引和非聚集索引都可以是惟一的。,9.2 索引,9.2.2 创建索引 创建索引时，首先必须考虑以下设计准则： 1. 设计索引时应考虑的
18、准则 (1) 创建索引之前应考虑的准则 在创建索引之前应考虑如下准则： 1) 了解数据库本身的特征。 2) 了解最常用的查询的特征。 3) 了解查询中使用的列的特征。 4) 确定哪些索引选项可在创建或维护索引时提高性能。 5) 确定索引的最佳存储位置。,9.2 索引,(2) 设计索引应考虑的数据库准则 1) 一个表如果建有大量索引，会影响INSERT、UPDATE和DELETE语句的性能。 2) 避免对经常更新的表进行过多索引，索引列尽可能少。 3) 使用多个索引可以提高更新少而数据量大的查询的性能。 4) 对小表进行索引可能不会产生优化效果。 5) 视图包含聚集函数、连接或聚集函数和连接的组
19、合时，视图的索引可以显著提升性能。,9.2 索引,在SQL Server 2005中创建索引主要有两种方式：一种方式是在SQL Server Management Studio中使用向导创建索引，另一种方式是通过在查询窗口中执行T-SQL语句创建索引。 T-SQL提供了索引创建语句CREATE INDEX。其语法格式如下： CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON ( column ASC | DESC ,.n ) INCLUDE ( column_name ,.n ) WITH ( ,.n ) ON partitio
20、n_scheme_name ( column_name ) | filegroup_name | default ,9.2 索引,例12 根据学生表的系创建索引 CREATE INDEX index_stu_sdept on student(sdept) 例13 根据选课表的学号、课程号降序创建索引 CREATE INDEX index_SC on SC(sno,cno desc),9.2.3 创建索引视图 如果在查询中频繁地引用视图，可通过对视图创建惟一聚集索引来提高性能。 【例14】根据视图CS_student1 ，创建索引视图。 创建视图： CREATE VIEW CS_student1
21、 WITH SCHEMABINDING AS SELECT Sno, Sname, Sage, Sdept FROM dbo.Student WHERE (Sdept = CS) 创建索引： CREATE UNIQUE CLUSTERED INDEX index_CS_student1 ON CS_student1(sdept,sage) GO,9.2 索引,9.2.4 删除索引 如果不需要索引，则可以将索引删除。在SQL Server 2005中提供了两种删除方式：一种方式是在SQL Server Management Studio中删除索引，另一种方式是通过执行T-SQL语句删除索引。 T
22、-SQL提供了索引删除语句DROP INDEX。其语法格式如下： DROP INDEX table_name . index_name 【例15】删除【例12】创建的索引index_stu_sdept 。 DROP INDEX Student.index_stu_sdept GO,9.2 索引,在SQL Server 2005中，SELECT查询操作会对整个行集起 作用。由SELECT语句返回的行集包括满足该语句的WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果 集。应用程序，特别是交互式联机应用程序，并不总能将整 个结果集作为一个单元来有效地处理。这些应用程序需要一 种机制以
23、便每次处理一行或一部分行。游标就是提供这种机 制的对结果集的一种扩展。,9.3 游标,9.3.1 游标概述 SQL Server 2005通过游标提供了对一个结果集进行逐行处理的能力。 游标也可以看作是一个表中的记录指针，该指针与某个查询结果相联系。在某一时刻，该指针只指向一条记录，即游标是通过移动指向记录的指针来处理数据的。当用户在SQL Server Management Studio中浏览记录时，总有一条记录的前面有一个黑色的三角标识，该标识就好像是一个记录指针。 游标通过以下方式来扩展结果处理： 1) 允许定位在结果集的特定行。 2) 从结果集的当前位置检索一行或一部分行。 3) 支持
24、对结果集中当前位置的行进行数据修改。 4) 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。 5) 提供脚本、存储过程和触发器中用于访问结果集中的数据的T-SQL语句。,9.3 游标,9.3.2 游标的类型 在SQL Server 2005中，根据游标的用途、使用方式等不同，可以将游标分为多种类型。 1. 根据用途分类 根据游标用途的不同，SQL Server 2005将游标分为三种： (1) T-SQL游标 基于DECLARE CURSOR语法，主要用于T-SQL脚本、存储过程和触发器。T-SQL游标在服务器上实现并由从客户端发送到服务器的T-SQL语句管理。它们
25、还可能包含在批处理、存储过程或触发器中。 (2) 应用程序编程接口（API）服务器游标 支持OLE DB和ODBC中的API游标函数。API服务器游标在服务器上实现。每次客户端应用程序调用API游标函数时，SQL Native Client OLE DB访问接口或ODBC驱动程序将把请求传输到服务器，以便对API服务器游标进行操作。 (3) 客户端游标 由SQL Native Client ODBC驱动程序和实现ADO API的DLL在内部实现。客户端游标通过在客户端高速缓存所有结果集行来实现。每次客户端应用程序调用API游标函数时，SQL Native Client ODBC 驱动程序或AD
26、O DLL就对客户端上高速缓存的结果集行执行游标操作。 由于T-SQL游标和API服务器游标都在服务器上实现，所以它们统称为服务器游标。,9.3 游标,2. 根据处理特性分类 根据T-SQL服务器游标的处理特性，SQL Server 2005将游标分为四种： (1) 静态游标 静态游标的完整结果集在打开游标时建立在tempdb中。静态游标总是按照打开游标时的原样显示结果集。SQL Server 2005静态游标始终是只读的。 (2) 动态游标 动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDAT
27、E、INSERT和DELETE语句均通过游标可见。 在SQL Server 2005中，动态游标工作表更新始终可以进行。,9.3 游标,(3) 只进游标 只进游标不支持滚动，它只支持游标从头到尾顺序提取。行只在从数据库中提取出来后才能检索。对所有由当前用户发出或由其他用户提交、并影响结果集中的行的INSERT、UPDATE和DELETE语句，其效果在这些行从游标中提取时是可见的。SQL Server 2005将只进和滚动都作为能应用于静态游标、键集驱动游标和动态游标的选项。T-SQL游标支持只进静态游标、键集驱动游标和动态游标。 (4) 由键集驱动游标 打开由键集驱动的游标时，该游标中各行的成
28、员身份和顺序是固定的。 当用户滚动游标时，对非键集列中的数据值所做的更改（由游标所有者做出或由其他用户提交）是可见的。在游标外对数据库所做的插入在游标内不可见，除非关闭并重新打开游标。,9.3 游标,3. 根据移动方式分类 根据T-SQL服务器游标在结果集中移动方式，SQL Server 2005将游标分为两种： (1) 滚动游标 在游标结果集中，滚动游标可以前后移动。 (2) 前向游标 在游标结果集中，前向游标只能向前移动。 根据T-SQL服务器游标结果集是否允许修改，SQL Server 2005将游标分为两种： (1) 只读游标 只读游标禁止修改游标结果集中的数据。 (2) 只写游标 只
29、写游标可以修改游标结果集中的数据，但分为部分可写和全部可写。部分可写表示只能修改数据行指定的列，而全部可写表示可以修改数据行所有的列。,9.3 游标,9.3.3 声明游标 声明游标是指利用SELECT查询语句创建游标的结构，指明游标的结果集中包括哪些数据。声明游标有两种方式：标准方式和T-SQL扩展方式。 标准方式提供了声明游标语句DECLARE CURSOR。其语法格式如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n
30、,9.3 游标,T-SQL扩展方式也提供了声明游标语句DECLARE CURSOR。其语法格式如下： DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWORD_only | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_list FOR UPDATE OF column_name ,n ,9.3 游标,【例16】利用标准方式声明一个游标。 DECLARE 学生 CURSOR FOR
31、 SELECT sno,sname,ssex,sage FROM student WHERE sdept=IS FOR READ ONLY 【例17】利用T-SQL扩展方式声明一个游标。 DECLARE 学生1 CURSOR DYNAMIC FOR SELECT sno,sname,ssex,sage FROM student WHERE sdept=IS FOR UPDATE OF sname,9.3 游标,9.3.4 打开游标 声明了游标，还必须打开才能使用。T-SQL提供了打开游标语句OPEN。其语法格式如下： OPEN GLOBAL cursor_name 【例16】打开声明的学生游标
32、。 OPEN 学生 GO,9.3 游标,9.3.5 读取游标 打开游标后，就可以从结果集中提取数据了。T-SQL提供了读取游标语句FETCH。其语法格式如下： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nvar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name ,.n ,9.3 游标,9.3.6 关闭游标 如果一个已打开游标暂时不用，就可以关闭。T-SQL提供了关闭游标语句CLOSE。其语法格式如下： CLOSE cu
33、rso _name 【例】关闭学生游标 CLOSE 学生,9.3 游标,9.3.7 删除游标 如果一个游标不需要，就可以删除。T-SQL提供了删除游标语句DEALLOCATE。其语法格式如下： DEALLOCATE curso _name 【例】删除学生游标。 DEALLOCATE学生,9.3 游标,【例18】完整举例。 DECLARE 学生CURSOR FOR SELECT sno,sname-,ssex,sage FROM student WHERE sdept=IS FOR READ ONLY OPEN 学生 declare vSno char(25),vSname char(25) fetch next from 学生into vSno,vSname While (fetch_status = 0)beginPRINT 学生名 = + vSname;PRINT 学生学号= + vSnofetch next from 学生into vSno,vSnameend close 学生 deallocate 学生,9.3 游标,
