第6章 视图、索引和游标

数据库原理及应用案例教程北京大学出版社2020年2月16日第1页第6章视图、索引和游标SQLServer2005数据库应用与开发教程北京大学出版社教学目标教学重点教学过程数据库原理及应用案例教程北京大学出版社2020年2月16日第2页教学目标掌握视图的创建、删除和维护掌握索引的创建、删除了解游标的类型、声明和使用数据库原理及应用案例教程北京大学出版社2020年2月16日第3页教学重点及难点重点：视图的创建、删除和维护索引的创建、删除难点：游标的类型、声明和使用数据库原理及应用案例教程北京大学出版社2020年2月16日第4页教学过程引例6.1视图6.2索引的定义和维护功能6.3游标6.4本章小结数据库原理及应用案例教程北京大学出版社2020年2月16日第5页引例小王作为技术培训中心的数据库开发人员，负责“学生选课”数据库设计。在数据库中的许多查询经常用到“学生学号、学生姓名、课程名称、分数”这些字段，这些字段涉及到了“学生、选课、选修”三个表；这三个表很少被更新（只有在期末要录入成绩时，才更新选修表中的数据）。小王在做数据查询测试时，发现查询响应时间太慢，而且多数的查询语句都要做三个表的联接。作为一个有经验的数据库开发人员，小王为“学生选课”数据库创建一个“学生课程成绩”视图，该视图定义了查询经常用到的那些字段列。同时，小王还为该视图创建了一个索引，以加快查询响应的时间。当查询姓名为‘李奇’同学所选修课程的课程名称及其分数，只要输入如下语句即可：Select课程名称,分数from学生课程成绩where姓名='李奇'(1)视图可以被当作一个基本表一样看待(2)索引则可以加快数据的查询速度数据库原理及应用案例教程北京大学出版社2020年2月16日第6页6.1视图6.1.1视图概述6.1.2创建视图6.1.3查询视图6.1.4修改视图定义6.1.5删除视图6.1.6更新视图数据库原理及应用案例教程北京大学出版社2020年2月16日第7页6.1.1视图概述视图中的数据可以来源于一个或多个数据库中的一个或多个表，视图中的数据也可能是来自另外的视图。1．视图的优点使用视图有很多优点，主要表现在：(1)为用户聚焦数据。(2)限制访问敏感数据，隐蔽数据库复杂性。(3)简化对用户许可的管理。(4)改进性能，视图可以实现复杂查询的结果。数据库原理及应用案例教程北京大学出版社2020年2月16日第8页6.1.1视图概述2．视图的使用范围下列场合可以考虑使用视图：(1)表的行或列的子集。(2)两个或多个表的联合。(3)两个或多个表的连接。(4)其它视图的子集。(5)视图与表的组合。3.视图的类型标准视图：组合了一个或多个表中的数据。索引视图：是被具体化了的视图，即它已经过计算并存储。分区视图：在一台或多台服务器间水平连接一组成员表中的分区数据。分布式分区视图：视图在服务器间连接表中的数据。数据库原理及应用案例教程北京大学出版社2020年2月16日第9页6.1.2创建视图1．在SQLServerManagementStudio中使用向导创建视图(1)右击【数据库】|【StudentCourse】|【视图】,在弹出的快捷菜单中执行【新建视图(N)…】命令，出现“添加表”对话框，用户可以选择需要添加的表、视图、函数和同义词,如图6-1所示。(2)添加表完毕，单击【关闭】按钮，进入视图设计窗口。设计定义视图所需要的Select语句。(3)视图创建完毕，需要给视图命名，并存盘退出。数据库原理及应用案例教程北京大学出版社2020年2月16日第10页6.1.2创建视图在创建视图要注意以下几点：(1)建立视图时必须遵循标识符命名规则，在数据库范围内视图名称要具有唯一性，不能与用户所拥有的其他数据库对象名称相同。(2)一个视图最多可以引用1024个字段，这些字段可以来自一个表或视图，也可以来自多个表或视图。(3)视图可以在其他视图上建立。SQLServer2005允许视图最多嵌套32层。(4)即使删除了一个视图所依赖的表或视图，这个视图的定义仍然保留在数据库中。(5)不能在视图上定义全文索引。(6)不能在视图上绑定规则、默认值和触发器。(7)不能建立临时视图，也不能在一个临时表上建立视图。(8)只能在当前数据库中创建视图，但是视图所引用的表或视图可以是其他数据库中的，甚至可以是其他服务器上的。数据库原理及应用案例教程北京大学出版社2020年2月16日第11页6.1.2创建视图2.使用CREATEVIEW语句创建视图其语法格式如下：CREATEVIEW视图名[(列名[,列名]…)]AS子查询[WITHCHECKOPTION]；【例6.1】建立计算机系学生的视图。CREATEVIEWvwComputerStu1ASSELECTSno,Sname,Ssex,SbirthdayFROMStudentsWHERESdept='计算机'注意：CREATEVIEW必须是批处理的第一条语句。数据库原理及应用案例教程北京大学出版社2020年2月16日第12页6.1.2创建视图【例6.2】建立计算机系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生。CREATEVIEWvwComputerStu2ASSELECTSno,Sname,Ssex,Sbirthday,SdeptFROMStudentsWHERESdept='计算机'WITHCHECKOPTION【例6.3】建立计算机系选修了C2号课程的学生视图。CREATEVIEWvwComputerStu3ASSELECTSC.Sno,Sname,Ssex,Sbirthday,SdeptFROMSC,vwComputerStu2WHERESC.Sno=vwComputerStu2.SnoANDCno='C2'数据库原理及应用案例教程北京大学出版社2020年2月16日第13页6.1.2创建视图【例6.4】将学生的学号及他的平均成绩定义为一个视图。CREATEVIEWvwStudentGrade(Sno,Savg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno数据库原理及应用案例教程北京大学出版社2020年2月16日第14页6.1.3查询视图1.在SQLServerManagementStudio中查看视图右击要查看的视图，在弹出的快捷菜单中是执行【打开视图】命令，则可查看该视图的数据内容，如图6-4所示。图6-4查看视图数据库原理及应用案例教程北京大学出版社2020年2月16日第15页6.1.3查询视图2．执行T-SQL语句查询视图【例6.5】在计算机系学生视图中查找1986年之后出生的学生。SELECTSno,SnameFROMvwComputerStu1WHEREYEAR(Sbirthday)1986本例转换后的查询语句为：SELECTSno,SnameFROMStudentsWHERESdept='计算机'ANDYEAR(Sbirthday)1985数据库原理及应用案例教程北京大学出版社2020年2月16日第16页6.1.4修改视图定义T-SQL提供了视图修改语句ALTERVIEW【例6.6】把例6.3创建的视图改为选了C1号课程的学生视图。ALTERVIEWvwComputerStu3ASSELECTSC.Sno,Sname,Ssex,Sbirthday,SdeptFROMSC,vwComputerStu2WHERESC.Sno=vwComputerStu2.SnoANDCno='C1'数据库原理及应用案例教程北京大学出版社2020年2月16日第17页6.1.4修改视图定义其语法格式如下：DROPVIEW视图名;注意：视图删除后视图的定义将从数据字典中删除。但是由该视图导出的其他视图定义仍在数据字典中，不过该视图已失效。用户使用时会出错，要用DROPVIEW语句将它们一一删除。例如要删除视图vwComputerStu2，则执行如下SQL命令即可：DROPVIEWvwComputerStu2数据库原理及应用案例教程北京大学出版社2020年2月16日第18页6.1.5更新视图【例6.8】向学生视图vwComputerSt2u中插入—个新的计算机系学生记录，其中学号为0601004，姓名为吴新，出生日期为1987-9-7。INSERTINTOvwComputerStu2VALUES('0601005','吴新','男','1987-9-7','计算机')说明：若把一个不属于计算机系的学生视图通过插入数据到Students表中，则系统提示所插入的数据不符合CHECKOPTION约束。【例6.9】删除计算机系学生视图vwComputerStu1中学号为0601004的记录。DELETEFROMvwComputerStu1WHERESno='0601005'数据库原理及应用案例教程北京大学出版社2020年2月16日第19页6.2索引的定义和维护功能数据库中的索引使用户可以快速找到表或索引视图中韵特定信息1．索引的作用(1)使用索引可以明显地加快数据查询的速度(2)使用索引可保证数据的唯一性(3)使用索引可以加快连接速度2．建立索引的原则(1)索引的建立和维护由DBA和DBMS完成(2)大表应当建索引，小表则不必建索引(3)对于一个基本表，不要建立过多的索引(4)根据查询要求建索引3．索引的分类(1)聚集索引(2)非聚集索引数据库原理及应用案例教程北京大学出版社2020年2月16日第20页6.2.1创建索引1．在SQLServerManagementStudio中使用向导创建索引(1)展开需要设置索引的表节点，右击【索引】选项，从弹出的快捷菜单中执行【新建索引】命令，打开“新建索引”对话框。图6-6选择索引列(2)在该对话框的“常规”选项页中，设置好“索引名称、索引类型”，然后单击【添加】按钮，打开如图6-6所示的对话框，在其中选择要添加到索引中的列，本例中选择“Sname”与“Sbirthday”列。数据库原理及应用案例教程北京大学出版社2020年2月16日第21页6.2.1创建索引(3)单击【确定】按钮返回“新建索引”对话框，单击“选项”页，进入图6-7所示的页面。在此页面中可以设置索引是否可以忽略重复的值，设置填充因子，是否将排序结果存储在tempdb数据库中，是否重新计算统计信息及索引的存储选项等操作。(4)单击【确定】按钮完成索引的创建。图6-7新建索引“选项”页数据库原理及应用案例教程北京大学出版社2020年2月16日第22页6.2.1创建索引2．利用T-SQL语句创建索引其语法格式如下：CREATE[UNIQUE][CLUSTERED][NONCLUSTEREDINDEX索引名ON表|视图(列名[ASC][DESC][,…n])[INCLUDE(包含性列[,…n])][WITH(索引选项)][ON{分区架构|文件组|default}]数据库原理及应用案例教程北京大学出版社2020年2月16日第23页6.2.1创建索引【例6.10】在StudentBak1表的学号Sno列上建立一个聚集索引，而且学生表中的记录将按照姓名值的升序存放。CREATECLUSTERINDEXixSnameONStudentBak1(Sname)【例6.11】根据StudentBak1表的姓名Cname、所在系Sdept创建一个名为ixNameDept索引。CREATEUNIQUENONCLUSTEREDINDEXixNameDeptONStudentBak1(SnameASC,SdeptDESC)数据库原理及应用案例教程北京大学出版社2020年2月16日第24页6.2.2创建索引视图对视图创建唯一聚集索引后视图称为索引视图。索引视图通常不会提高下列查询类型的性能：(1)具有大量更新的数据库。(2)不涉及聚集或连接的查询。(3)使用GROUPBY子句的数据聚集。(4)扩展连接。数据库原理及应用案例教程北京大学出版社2020年2月16