数据库 第9章视图、索引和游标

1、第9章 视图、索引和游标,河北工程大学 信电学院 崔冬,数据库原理与SQL Server 2005应用,第9章 视图、索引和游标,视图,1,索引,2,游标,3,4,1,小结,SQL Server 2005,9.1 视图,图9-1 视图概念示意图,SQL Server 2005,1. 视图的优点 1) 为用户集中数据，简化用户的数据查询和处理。使得分散在多个表中的数据，通过视图定义在一起。 2) 简化操作，屏蔽了数据库的复杂性。 3) 重新定制数据，使得数据便于共享。 4) 合并分割数据，有利于数据输出到应用程序中。 5) 简化了用户权限的管理，增加了安全性。,9.1.1 视图概述,SQL Se

2、rver 2005,视图通常用来集中、简化和自定义每个用户对数据库的不同 认识。通常在以下情况下使用视图： 着重于特定数据。 简化数据操作。 3) 自定义数据。 4) 数据的导入与导出。 5) 跨服务器组合分区数据库,2. 视图的使用范围,SQL Server 2005,在SQL Server 2005中，视图可以分为标准视图、索引视图和分区视图。 标准视图组合了一个或多个表中的数据，用户可以使用标准视图对数据库进行查询、修改、删除等基本操作。 索引视图是被具体化了的视图，即它已经过计算并存储。可以为视图创建索引，即对视图创建一个惟一的聚集索引。 分区视图在一台或多台服务器间水平连接一组成员表

3、中的分区数据。,9.1.2 视图的类型,SQL Server 2005,在创建视图前应考虑如下准则： 1) 只能在当前数据库中创建视图。 2) 视图名必须遵循标识符的规则，且对每个架构都必须惟一。 3) 用户可以对其他视图创建视图。 4) 不能将规则或DEFAULT定义与视图相关联. 5) 不能将AFTER触发器与视图相关联，只有INSTEAD OF触发器可以与之相关联。 6) 定义视图的查询不能包含COMPUTE子句、COMPUTE BY子句或INTO关键字。 7) 定义视图的查询不能包含ORDER BY子句，除非在SELECT语句的选择列表中还有一个TOP子句。 8) 定义视图的查询不能包

4、含指定查询提示的OPTION子句。 9) 定义视图的查询不能包含TABLESAMPLE子句。,9.1.3 创建视图,SQL Server 2005,10) 不能为视图定义全文索引定义。 11) 不能创建临时视图，也不能对临时表创建视图。 12) 不能删除参与到使用SCHEMABINDING子句创建的视图中的视图、表或函数，除非该视图已被删除或更改而不再具有架构绑定。另外，如果对参与具有架构绑定的视图的表执行ALTER TABLE语句，而这些语句又会影响该视图的定义，则这些语句将会失败。 13) 下列情况下必须指定视图中每列的名称： 视图中的任何列都是从算术表达式、内置函数或常量派生而来。 视图

5、中有两列或多列原应具有相同名称。 希望为视图中的列指定一个与其源列不同的名称。,SQL Server 2005,主要有两种方式 1、在SQL Server Management Studio中使用向导创建视图，演示。 2、通过在查询窗口中执行T-SQL语句创建视图。 其语法格式如下： CREATE VIEW （视图列名表） AS 子查询语句,在SQL Server 2005中创建标准视图,SQL Server 2005,定义视图,注意： 子查询中通常不包含ORDER BY和DISTINCT子句。 在定义视图时要么指定全部视图列，要么全部省略不写。如果省略了视图的属性列名，则视图的列名与子查询列

6、名相同。但在如下三种情况下必须明确指定组成视图的所有列名： 某个目标列是计算函数或列表达式； 多表连接时选出了几个同名列作为视图的字段； 需要在视图中为某个列选用新的更合适的列名。,SQL Server 2005,1.定义单源表视图,单源表视图指的是数据取自一个基本表的部分行、列。这样定义的视图可以进行查询和修改数据操作。 例1建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 信息系 DBMS执行CREATE VIEW语句的结果只是保存视图的定义，只有在对视图执行查询

7、时，才按视图的定义从相应基本表中查询数据。,SQL Server 2005,2定义多源表视图,多源表视图指的是定义视图的子查询的源表可以有多个，这样定义的视图一般只用于查询，不用于修改数据。 例2建立信息系选修了c01号课程的学生的视图。 CREATE VIEW V_IS_S1(Sno, Sname, Sage, Grade) AS SELECT Student.Sno, Sname, Sage, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c01,SQL Server 20

8、05,3在已有视图上定义新视图,在视图上建立视图表示视图的数据源中有视图。作为数据源的视图必须是已经建立好的。 例3建立信息系选修了c01号课程且成绩在90分以上的学生的视图。 CREATE VIEW V_IS_S2 AS SELECT Sno, Sname, Grade FROM V_IS_S1 WHERE Grade = 90,SQL Server 2005,4定义带表达式的视图,在定义基本表时，为减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据需要设置一些派生属性列，在这些派生属性列中保存

9、经过计算的值。 例4定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno, Sname, Sbirth) AS SELECT Sno, Sname, 2002-Sage FROM Student,SQL Server 2005,5含分组统计信息的视图,含分组统计信息的视图是指视图的子查询中含有GROUP BY子句，这样的视图只能用于查询，不能用于修改数据。 例5定义一个存放每个学生的学号及平均成绩的视图。 CREATE VIEW S_G(Sno, AverageGrade) AS SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno 注意

10、：如果子查询的选择列表包含表达式或统计函数，而且在子查询中也没有为这样的列指定列标题，则在定义视图的语句中必须要指定视图属性列的名字。,SQL Server 2005,【例6】查询学生_课程_分数视图，统计“数据库”课程的总分和平均分。 CREATE VIEW S_C_SC(Sname, Cname, Grade) AS SELECT Sname, Cname, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON Course.Cno = SC.Cno SELECT 总分=SUM(Grade),平均分=AVG(Gr

11、ade) FROM S_C_SC WHERE Cname=数据库 GO,9.1.4 查询视图,SQL Server 2005,只要满足下列条件，即可通过视图修改基础基表的数据： 1) 任何修改（包括UPDATE、INSERT和DELETE语句）都只能引用一个基表的列。 2) 视图中被修改的列必须直接引用表列中的基础数据。不能通过任何其他方式对这些列进行派生，如通过聚合函数、计算（如表达式计算）、集合运算等。 3) 被修改的列不受GROUP BY、HAVING、DISTINCT或TOP子句的影响。 注意：即使是可更新视图，也不能随意更新数据。如果视图所依赖的基本表有多个时，不能向该视图添加数据。

12、修改数据时，若视图依赖于多个基本表，那么一次修改只能修改一个基本表中的数据。删除数据时，若视图依赖于多个基本表，那么不能通过视图删除数据。,9.1.5 更新视图,SQL Server 2005,常见错误,例1： INSERT INTO V_IS_S1 (Sno,Sname,Grade) VALUES (9512107,张星,18) 例2：,SQL Server 2005,修改视图定义，与修改基本表结构不一样。修改基本表结构是指重新定义列名、属性、约束等，而修改视图定义是指修改视图的指定列的列名、别名、表名、是否输出、顺序类型等属性。 1、在SQL Server Management Studi

13、o修改的视图。演示 2、执行T-SQL语句修改视图定义。其语法格式如下： ALTER VIEW schema_name . view_name ( column ,.n ) WITH ,.n AS select_statement ; WITH CHECK OPTION ,9.1.6 修改视图定义,SQL Server 2005,例1建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 信息系 例7 ALTER VIEW IS_Student AS SELECT Sno,

14、 Sname, Sage FROM Student WHERE Sdept=信息系 AND ssex=男,【例7】修改【例1】创建的信息系学生的视图，包括信息系的男生的学号、姓名、性别、年龄,SQL Server 2005,1、在SQL Server Management Studio中选中要删除的视图并删除。演示。 2、执行T-SQL语句删除视图。 其语法格式如下： DROP VIEW view_name 例8删除IS_Student视图。 DROP VIEW IS_Student 注意:如果被删除的视图是作为其他视图的数据源，则导出视图将无法再使用了。同样，如果作为视图的基本表被删除了，则

15、视图也将无法使用。,9.1.7 删除视图,SQL Server 2005,索引包含从表或视图中一个或多个列生成的键，以及映射到 指定数据的存储位置的指针。 在数据库系统中建立索引主要有以下作用： 1) 快速存取数据。 2) 保证数据的一致性。 3) 实现表与表之间的参照完整性。 4) 在使用GROUP BY、ORDER BY子句进行查询时，利用索引可以减少排序和分组的时间。,9.2 索引,SQL Server 2005,如果一个表没有创建索引，则数据行不按任何特定顺序存储，这种结构称为堆集。 SQL Server 2005支持在表中任何列（包括计算列）上定义索引。索引可以是惟一的，即索引列不会

16、有两行记录相同，这样的索引称为惟一索引。索引也可以是不惟一的。如果索引是根据单列创建的，这样的索引称为单列索引。根据多列组合创建的索引称为复合索引。 按索引的组织方式的不同，可以将索引分为聚集索引和非聚集索引。,9.2.1 索引的分类,SQL Server 2005,创建索引时，首先必须考虑以下设计准则： 1. 设计索引时应考虑的准则 (1) 创建索引之前应考虑的准则 1) 了解数据库本身的特征。 2) 了解最常用的查询的特征。 3) 了解查询中使用的列的特征。 4) 确定哪些索引选项可在创建或维护索引时提高性能。 5) 确定索引的最佳存储位置。,9.2.2 创建索引,SQL Server 2

17、005,1、在SSMS中使用向导创建索，演示。 2、执行T-SQL语句创建索引。 其语法格式如下： CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON ( column ASC | DESC ,.n ) INCLUDE ( column_name ,.n ) WITH ( ,.n ) ON partition_scheme_name ( column_name ) | filegroup_name | default ,创建索引,SQL Server 2005,如果在查询中频繁地引用视图，可通过对视图创建惟一聚集索引来提高性能。

18、 【例9-10】根据工作人员表创建的视图，包括人员编号、姓名和职务列。再使用T-SQL语句根据姓名列创建索引视图。 创建视图： CREATE VIEW 职工视图 WITH SCHEMABINDING AS SELECT 人员编号,姓名,职务 FROM dbo.工作人员表 GO 创建索引： CREATE UNIQUE CLUSTERED INDEX 职工视图索引 ON 职工视图(人员编号) GO,9.2.3 创建索引视图,SQL Server 2005,1、SQL Server Management Studio中删除索引，演示。 2、执行T-SQL语句删除索引。 其语法格式如下： DROP I

19、NDEX table_name . index_name 【例9-11】删除【例9-8】创建的索引。 DROP INDEX 教师表.index_教师姓名 GO,9.2.4 删除索引,SQL Server 2005,9.3.1 游标概述 游标通过以下方式来扩展结果处理： 1) 允许定位在结果集的特定行。 2) 从结果集的当前位置检索一行或一部分行。 3) 支持对结果集中当前位置的行进行数据修改。 4) 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。 5) 提供脚本、存储过程和触发器中用于访问结果集中的数据的T-SQL语句。,9.3 游标,SQL Server 200

20、5,1. 根据游标用途的不同，SQL Server 2005将游标分为三种： (1) T-SQL游标 基于DECLARE CURSOR语法，主要用于T-SQL脚本、存储过程和触发器。 (2) 应用程序编程接口（API）服务器游标 支持OLE DB和ODBC中的API游标函数。API服务器游标在服务器上实现。 (3) 客户端游标 由SQL Native Client ODBC驱动程序和实现ADO API的DLL在内部实现。通过在客户端高速缓存所有结果集行来实现。 由于T-SQL游标和API服务器游标都在服务器上实现，所以它们统称为服务器游标。,9.3.2 游标的类型,SQL Server 200

21、5,2.根据T-SQL服务器游标的处理特性，分为四种：,(1) 静态游标 静态游标总是按照打开游标时的原样显示结果集。 (2) 动态游标 动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。 (3) 只进游标 只进游标不支持滚动，它只支持游标从头到尾顺序提取。 (4) 由键集驱动游标 打开由键集驱动的游标时，该游标中各行的成员身份和顺序是固定的。,SQL Server 2005,(1) 滚动游标 在游标结果集中，滚动游标可以前后移动。 (2) 前向游标 在游标结果集中，前向游标只能向前移动。 4.根据T-SQL服务器游标

22、结果集是否允许修改，SQL Server 2005将游标分为两种： (1) 只读游标 只读游标禁止修改游标结果集中的数据。 (2) 只写游标 只写游标可以修改游标结果集中的数据，但分为部分可写和全部可写。部分可写表示只能修改数据行指定的列，而全部可写表示可以修改数据行所有的列。,3. 根据T-SQL服务器游标在结果集中移动方式,SQL Server 2005,声明游标是指利用SELECT查询语句创建游标的结构，指明游标的结果集中包括哪些数据。 声明游标有两种方式：标准方式和T-SQL扩展方式。 标准方式提供了声明游标语句DECLARE CURSOR。其语法格式如下： DECLARE curso

23、r_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n ,9.3.3 声明游标,SQL Server 2005,T-SQL扩展方式也提供了声明游标语句DECLARE CURSOR。其语法格式如下： DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWORD_only | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | O

24、PTIMISTIC TYPE_WARING FOR select_list FOR UPDATE OF column_name ,n ,T-SQL扩展方式,SQL Server 2005,DECLARE 学生 CURSOR FOR SELECT 学号,姓名,性别,出生日期 FROM 学生表 WHERE 院系名称=计算机学院 FOR READ ONLY,【例9-12】利用标准方式声明一个游标。,SQL Server 2005,【例9-13】利用T-SQL扩展方式声明一个游标。,DECLARE 职工CURSOR DYNAMIC FOR SELECT 姓名,职务 FROM 工作人员表 WHERE 性别=男 FOR UPDATE OF 姓名,SQL Server 2005,声明了游标，还必须打开才能使用。T-SQL提供了打开游标语句OPEN。其语法格式如下： OPEN GLOBAL cursor_name 【例9-14】打开【例9-12】声明的游标。 OPEN 学生 GO 【例9-15】打开【例9-13】声明的游标。 OPEN 职工 GO,9.3.4 打开游标,