第9章视图、索引和游标

第9章视图、索引和游标河北工程大学信电学院崔冬数据库原理与SQLServer2005应用SQLServer2005第9章视图、索引和游标视图1索引2游标341小结SQLServer20059.1视图基本表1基本表2视图图9-1视图概念示意图SQLServer20051.视图的优点1)为用户集中数据，简化用户的数据查询和处理。使得分散在多个表中的数据，通过视图定义在一起。2)简化操作，屏蔽了数据库的复杂性。3)重新定制数据，使得数据便于共享。4)合并分割数据，有利于数据输出到应用程序中。5)简化了用户权限的管理，增加了安全性。9.1.1视图概述SQLServer2005视图通常用来集中、简化和自定义每个用户对数据库的不同认识。通常在以下情况下使用视图：1)着重于特定数据。2)简化数据操作。3)自定义数据。4)数据的导入与导出。5)跨服务器组合分区数据库2.视图的使用范围SQLServer2005在SQLServer2005中，视图可以分为标准视图、索引视图和分区视图。标准视图组合了一个或多个表中的数据，用户可以使用标准视图对数据库进行查询、修改、删除等基本操作。索引视图是被具体化了的视图，即它已经过计算并存储。可以为视图创建索引，即对视图创建一个惟一的聚集索引。分区视图在一台或多台服务器间水平连接一组成员表中的分区数据。9.1.2视图的类型SQLServer2005在创建视图前应考虑如下准则：1)只能在当前数据库中创建视图。2)视图名必须遵循标识符的规则，且对每个架构都必须惟一。3)用户可以对其他视图创建视图。4)不能将规则或DEFAULT定义与视图相关联.5)不能将AFTER触发器与视图相关联，只有INSTEADOF触发器可以与之相关联。6)定义视图的查询不能包含COMPUTE子句、COMPUTEBY子句或INTO关键字。7)定义视图的查询不能包含ORDERBY子句，除非在SELECT语句的选择列表中还有一个TOP子句。8)定义视图的查询不能包含指定查询提示的OPTION子句。9)定义视图的查询不能包含TABLESAMPLE子句。9.1.3创建视图SQLServer200510)不能为视图定义全文索引定义。11)不能创建临时视图，也不能对临时表创建视图。12)不能删除参与到使用SCHEMABINDING子句创建的视图中的视图、表或函数，除非该视图已被删除或更改而不再具有架构绑定。另外，如果对参与具有架构绑定的视图的表执行ALTERTABLE语句，而这些语句又会影响该视图的定义，则这些语句将会失败。13)下列情况下必须指定视图中每列的名称：①视图中的任何列都是从算术表达式、内置函数或常量派生而来。②视图中有两列或多列原应具有相同名称。③希望为视图中的列指定一个与其源列不同的名称。SQLServer2005主要有两种方式1、在SQLServerManagementStudio中使用向导创建视图，演示。2、通过在查询窗口中执行T-SQL语句创建视图。其语法格式如下：CREATEVIEW视图名[（视图列名表）]AS子查询语句在SQLServer2005中创建标准视图SQLServer2005定义视图注意：子查询中通常不包含ORDERBY和DISTINCT子句。在定义视图时要么指定全部视图列，要么全部省略不写。如果省略了视图的属性列名，则视图的列名与子查询列名相同。但在如下三种情况下必须明确指定组成视图的所有列名：某个目标列是计算函数或列表达式；多表连接时选出了几个同名列作为视图的字段；需要在视图中为某个列选用新的更合适的列名。SQLServer20051.定义单源表视图单源表视图指的是数据取自一个基本表的部分行、列。这样定义的视图可以进行查询和修改数据操作。例1．建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='信息系'DBMS执行CREATEVIEW语句的结果只是保存视图的定义，只有在对视图执行查询时，才按视图的定义从相应基本表中查询数据。SQLServer20052．定义多源表视图多源表视图指的是定义视图的子查询的源表可以有多个，这样定义的视图一般只用于查询，不用于修改数据。例2．建立信息系选修了‘c01’号课程的学生的视图。CREATEVIEWV_IS_S1(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,Sage,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='信息系'ANDSC.Cno='c01'SQLServer20053．在已有视图上定义新视图在视图上建立视图表示视图的数据源中有视图。作为数据源的视图必须是已经建立好的。例3．建立信息系选修了‘c01’号课程且成绩在90分以上的学生的视图。CREATEVIEWV_IS_S2ASSELECTSno,Sname,GradeFROMV_IS_S1WHEREGrade=90SQLServer20054．定义带表达式的视图在定义基本表时，为减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据需要设置一些派生属性列，在这些派生属性列中保存经过计算的值。例4．定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,2002-SageFROMStudentSQLServer20055．含分组统计信息的视图含分组统计信息的视图是指视图的子查询中含有GROUPBY子句，这样的视图只能用于查询，不能用于修改数据。例5．定义一个存放每个学生的学号及平均成绩的视图。CREATEVIEWS_G(Sno,AverageGrade)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno注意：如果子查询的选择列表包含表达式或统计函数，而且在子查询中也没有为这样的列指定列标题，则在定义视图的语句中必须要指定视图属性列的名字。SQLServer2005【例6】查询学生_课程_分数视图，统计“数据库”课程的总分和平均分。CREATEVIEWS_C_SC(Sname,Cname,Grade)ASSELECTSname,Cname,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoJOINCourseONCourse.Cno=SC.CnoSELECT总分=SUM(Grade),平均分=AVG(Grade)FROMS_C_SCWHERECname='数据库'GO9.1.4查询视图SQLServer2005只要满足下列条件，即可通过视图修改基础基表的数据：1)任何修改（包括UPDATE、INSERT和DELETE语句）都只能引用一个基表的列。2)视图中被修改的列必须直接引用表列中的基础数据。不能通过任何其他方式对这些列进行派生，如通过聚合函数、计算（如表达式计算）、集合运算等。3)被修改的列不受GROUPBY、HAVING、DISTINCT或TOP子句的影响。注意：即使是可更新视图，也不能随意更新数据。如果视图所依赖的基本表有多个时，不能向该视图添加数据。修改数据时，若视图依赖于多个基本表，那么一次修改只能修改一个基本表中的数据。删除数据时，若视图依赖于多个基本表，那么不能通过视图删除数据。9.1.5更新视图SQLServer2005常见错误例1：INSERTINTOV_IS_S1(Sno,Sname,Grade)VALUES(9512107,‘张星’,18)例2：SQLServer2005修改视图定义，与修改基本表结构不一样。修改基本表结构是指重新定义列名、属性、约束等，而修改视图定义是指修改视图的指定列的列名、别名、表名、是否输出、顺序类型等属性。1、在SQLServerManagementStudio修改的视图。演示2、执行T-SQL语句修改视图定义。其语法格式如下：ALTERVIEW[schema_name.]view_name[(column[,...n])][WITHview_attribute[,...n]]ASselect_statement[;][WITHCHECKOPTION]9.1.6修改视图定义SQLServer2005例1．建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='信息系'例7ALTERVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=‘信息系’ANDssex=‘男'【例7】修改【例1】创建的信息系学生的视图，包括信息系的男生的学号、姓名、性别、年龄SQLServer20051、在SQLServerManagementStudio中选中要删除的视图并删除。演示。2、执行T-SQL语句删除视图。其语法格式如下：DROPVIEWview_name例8．删除IS_Student视图。DROPVIEWIS_Student注意:如果被删除的视图是作为其他视图的数据源，则导出视图将无法再使用了。同样，如果作为视图的基本表被删除了，则视图也将无法使用。9.1.7删除视图SQLServer2005索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。在数据库系统中建立索引主要有以下作用：1)快速存取数据。2)保证数据的一致性。3)实现表与表之间的参照完整性。4)在使用GROUPBY、ORDERBY子句进行查询时，利用索引可以减少排序和分组的时间。9.2索引SQLServer2005如果一个表没有创建索引，则数据行不按任何特定顺序存储，这种结构称为堆集。SQLServer2005支持在表中任何列（包括计算列）上定义索引。索引可以是惟一的，即索引列不会有两行记录相同，这样的索引称为惟一索引。索引也可以是不惟一的。如果索引是根据单列创建的，这样的索引称为单列索引。根据多列组合创建的索引称为复合索引。按索引的组织方式的不同，可以将索引分为聚集索引和非聚集索引。9.2.1索引的分类SQLServer2005创建索引时，首先必须考虑以下设计准则：1.设计索引时应考虑的准则(1)创建索引之前应考虑的准则1)了解数据库本身的特征。2)了解最常用的查询的特征。3)了解查询中使用的列的特征。4)确定哪些索引选项可在创建或维护索引时提高性能。5)确定索引的最佳存储位置。9.2.2创建索引SQLServer20051、在SSMS中使用向导创建索，演示。2、执行T-SQL语句创建索引。其语法格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameONobject(column[ASC|DESC][,...n])[INCLUDE(column_name[,...n])][WITH(relational_index_option[,...n])][ON{partition_scheme_name(column_name)|filegroup_name|default}]创建索引SQLServer2005如果在查询中频繁地引用视图，可通过对视图创建惟一聚集索引来提高性能。【例9-10】根据工作人员表创建的视图，包括人员编号、姓名和职务列。再使用T-SQL语句根据姓名列创建索引视图。创建视图：CREATEVIEW职工视图WITHSCHEMABINDINGASSELECT人员编号,姓名,职务FROMdbo.工作人员表GO创建索引：CREATEUNIQUECLUSTEREDINDEX职工视图索引ON职工视图(人员编号)GO9.2.3创建索引视图SQLServer20051、SQLServerManagementStudio中删除索