《数据库》课件第5章-索引与视图

1回顾连接查询子查询2第五章

索引与视图3目标索引的分类创建合适的索引创建视图45.1索引操作用户对数据库最常用的操作就是查询数据。在数据量比较大时，搜索满足条件的数据可能会花费很长时间，从而占用较多的服务器资源。为了提高数据检索的能力，在数据库中引入了索引的概念。

55.1.1索引概述数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含的某列（或某些列）的值以及各个值所对应的记录存储位置。可以为表中的单个列建立索引，也可以为一组列建立索引。6索引的作用可以大大加快数据检索速度。在检索数据过程中自动进行优化，提高系统性能。可以加速表与表之间的连接。可以显著减少查询中分组和排序的时间。7索引缺点1.创建索引需花费时间。2.每建一个索引都需要花费一定的存储空间。3.当数据变化时，需要重新维护索引。8索引类型1.聚集索引（Clusteredindex）2.非聚集索引（Nonclusteredindex）聚集索引：在聚集索引中，表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。表只能包含一个聚集索引，聚集索引通常建在有大量唯一值及不常更新的列上，聚集索引常用于搜索范围值特别有效。非聚集索引：非聚集索引具有完全独立于数据行的结构，非聚集索引每张表可以创建249个。非聚集索常用于：在join，where及groupby子句上出现的列。列的值常常更新。外键对应的列。9索引工作步骤（非）聚集索引是按B+树结构存放的，工作步骤如下：1.SQLSERVER从sysindexes表中获得根页地址。2.将要查找的值与根页上的键值相比较。3.找到页上小于或等于要查找的值的最大键值。4.页指针指向下一页。5.重复步骤3和4，直到叶子页。6.找到要查找的值，如果没有该值，则不返回结果。10索引工作步骤602E005601E001PagePtrEidPage603204E007203E005PagePtrEidPage602202E003201E001PagePtrEidPage601…CalMillerE006…LADavE005…CityNameEidPage203…CalWilsE008…WACarlE007…CityNameEidPage204…BostonDunE002…LAAllenE001…CityNameEidPage201…LAKellyE004…CalColsE003…CityNameEidPage202RootpageIntermediatepageData(Leaf)pages(table)1.SQLSERVER从sysindexes表中获得根页地址。2.将要查找的值与根页上的键值相比较。3.找到页上小于或等于要查找的值的最大键值。4.页指针指向下一页3.找到页上小于或等于要查找的值的最大键值。4.页指针指向下一页聚集11索引工作步骤602Dun601AllenPagePtrNamePage603…CalColsE006…LAAllenE005…CityNameEidPage303…BostonDunE008…LAKellyE007…CityNameEidPage304…CalWilsE002…LADavE001…CityNameEidPage301…WACarlE004…CalMillerE003…CityNameEidPage302RootpageIntermediatepageDatapages(table)Leafpage202Cols201AllenPagePtrNamePage601204Miller203DunPagePtrNamePage602302Carl303AllenPagePtrNamePage201301Dav303ColsPagePtrNamePage202304Kelly304DunPagePtrNamePage203301Wils302MillerPagePtrNamePage2041.SQLSERVER从sysindexes表中获得根页地址。2.将要查找的值与根页上的键值相比较。3.找到页上小于或等于要查找的值的最大键值。4.页指针指向下一页3.找到页上小于或等于要查找的值的最大键值。4.页指针指向下一页非聚集12索引特性1.索引增强了连接表，排序，分组的查询。2.当修改了创建索引的列，相应的索引也会自动更新。3.维护索引需要花费时间，不常用的索引不应创建。4.聚集索引通常创建在非聚集索引之前。5.通常外键上创建非聚集索引，主键创建聚集索引。135.1.2创建索引语法：CREATE[CLUSTERED|NONCLUSTERED]INDEX索引名ON{表名|视图名}(列名[ASC|DESC][,...n])默认为非聚集索引示例1:为“Student”表创建基于“StuName”列的聚集索引“idxStuName”

解决方案：CREATECLUSTEREDINDEXidxStuNameONStudent(StuName)示例2:对“Student”表的“DepID”列创建非聚集索引解决方案：CREATENONCLUSTEREDINDEXidxDepIDONStudent(DepID)145.1.3删除索引语法：DROPINDEX表名.索引名示例:删除“Student”表的索引“idxStuName”解决方案：DROPINDEXStudent.idxStuName15练习：为提升下列查询速度创建索引有下列查询语句，由于数据量较大，希望查询时能提升查询速度，该如何建立适当的索引？SELECTStuNo,StuName,DepNameFROMStudentJOINDepartmentONStudent.DeptID=Department.DeptID解决方案：CREATECLUSTEREDINDEXidxDepartmentONDepartment(DeptID)CREATECLUSTEREDINDEXidxStudentONStudent(StuID)CREATENONCLUSTEREDINDEXnidxStudentONStudent(DeptID)165.2视图管理显示选修了“数据库”课程的学生姓名,所在系,年龄及成绩。SELECTStuName,DepName,StuAge,ScoreFROMStudentsJOINSCONs.StuID=SC.StuIDJOINCourseONSC.CourseID=Course.CourseIDJOINDepartmentdONd.DepID=s.DepIDWHERECourseName=‘数据库’

17视图管理视图是保存在数据库中的SELECT查询，其内容由查询定义，因此，视图不是真实存在的基础表，而是从一个或者多个表中导出的虚拟的表。同真实的表一样，视图包含一系列带有名称的列和行数据，但视图中的行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。因此，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。18视图的特点简单性。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部的条件。

安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。195.2.2创建视图示例：创建视图显示选修了“数据库”课程的学生姓名,所在系,年龄及成绩。语法：

CREATEVIEW视图名ASSELECT语句解决方案：CREATEVIEWvwStudentInfoWithDBASSELECTStuName,DepName,StuAge,ScoreFROMStudentsJOINSCONs.StuID=SC.StuIDJOINCourseONSC.CourseID=Course.CourseIDJOINDepartmentdONd.DepID=s.DepIDWHERECourseName=‘数据库’205.2.3视图更新CREATEVIEWvwStudentInfoWithDBASSELECTStuName,DepName,StuAge,ScoreFROMStudentsJOINSCONs.StuID=SC.StuIDJOINCourseONSC.CourseID=Course.CourseIDJOINDepartmentdONd.DepID=s.DepIDWHERECourseName=‘数据库’如要更新‘1’号学生的年龄和成绩：UPDATEvwStudentInfoWithDBSETStuAge=StuAge+1,Score=Score+10WHEREStuID=1编译器将报错21视图更新视图不能同时修改来自于2张或2张以上基表的数据正确的更新方法：UPDATEvwStudentInfoWithDBSETStuAge=StuAge+1WHEREStuID=1UPDATEvwStudentInfoWithDBSETScore=Score+10WHERE