第5章索引与视图.pptx

数据库技术与应用数据库技术与应用 SQL-ServerSQL-Server第第5 5章章 索引与视图索引与视图 第第5章章 索引与视图索引与视图5.15.1 索引概述索引概述5.2 5.2 索引的操作索引的操作 5.35.3 视图概述视图概述5.45.4 视图的操作视图的操作5.55.5 视图的应用视图的应用 2数据库技术与应用数据库技术与应用 35.1 索引索引数据库技术与应用数据库技术与应用 45.1.1 索引的基本概念索引的基本概念 索引是对数据库表中一个或多个字段的值进行排序而创索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建的一种分散存储结构。建立索引的目的有以下几点：建立索引的目的有以下几点：(1)加速数据检索加速数据检索 (2)加速连接、加速连接、ORDER BY和和GROUP BY等操作等操作 (3)查询优化器依赖于索引起作用查询优化器依赖于索引起作用 (4)强制实行的唯一性强制实行的唯一性数据库技术与应用数据库技术与应用 什么是索引什么是索引q汉语字典中的汉字按汉语字典中的汉字按页页存放，一般都有汉语拼音目录（存放，一般都有汉语拼音目录（索引索引）、偏旁部首目录等、偏旁部首目录等q我们可以根据拼音或偏旁部首，我们可以根据拼音或偏旁部首，快速快速查找某个字词查找某个字词5.1.1 索引的基本概念索引的基本概念5数据库技术与应用数据库技术与应用 IndexesUseKeyValuestoLocateData（根据索引键查找定位数据行）Data Pages（数据页）Index Pages（索引页）什么是索引什么是索引5.1.1 索引的基本概念索引的基本概念6数据库技术与应用数据库技术与应用 什么是索引什么是索引qSQLServer中的数据也是按页（4KB）存放q索引：是SQLServer编排数据的内部方法。它为SQLServer提供一种方法来编排查询数据。q索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。q索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。5.1.1 索引的基本概念索引的基本概念7数据库技术与应用数据库技术与应用 8 5.1.2 索引的分类索引的分类 根据数据库的功能，在根据数据库的功能，在SQL Server 2012中中可创建可创建3种类型种类型的索引，即唯一性索引、主键索引和聚集索引的索引，即唯一性索引、主键索引和聚集索引。1唯一性索引唯一性索引 在表中建立唯一性索引时，组成该索引的字段或字段组合在表中建立唯一性索引时，组成该索引的字段或字段组合在表中具有唯一值，也就是说，对于表中的任何两行记录来说，在表中具有唯一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。索引键的值都是各不相同。唯一性索引要求组成该索引的字段或字段组合不能在唯一性索引要求组成该索引的字段或字段组合不能在多行多行记录中具有记录中具有NULL值。值。数据库技术与应用数据库技术与应用 92主键主键索引索引 表中通常有一个字段或一些字段组的合，其值用来唯表中通常有一个字段或一些字段组的合，其值用来唯一标识表中的每一行记录，该字段或字段组合称为表的主一标识表中的每一行记录，该字段或字段组合称为表的主键。主键索引是唯一索引的特殊类型。主键索引要求主键键。主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空中的每个值是唯一的，并且不能为空数据库技术与应用数据库技术与应用 10 3聚集索引聚集索引(Clustered)：在聚集索引中，表中各记录的物理顺序与键值的逻辑在聚集索引中，表中各记录的物理顺序与键值的逻辑(索索引引)顺序相同。只有在表中建立了一个聚集索引后，数据才会顺序相同。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数据按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以只能按照一种顺序来存储，所以在一个表中只能建立一个聚在一个表中只能建立一个聚集索引集索引。非非聚集索引聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于指向数据存储位置的指针。可以有多个，小于249个个数据库技术与应用数据库技术与应用 索引分类索引分类【例】在学生 表中查询学号sno为“20021003012”的行（在列sno上已建立聚集索引）索引表数据表11数据库技术与应用数据库技术与应用 索引分类索引分类【例】在学生表中查询学号s_no为“20021003012”的行（在列s_no上已建立非聚集索引）索引表数据表12数据库技术与应用数据库技术与应用 135.1.3 索引的操作索引的操作1.创建索引创建索引使用企业管理器向导建立索引使用企业管理器向导建立索引使用企业管理器直接创建索引使用企业管理器直接创建索引数据库技术与应用数据库技术与应用 创建索引创建索引使用T-SQL创建基本语句格式：基本语句格式：CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEX索引名索引名ON表表|视图视图（列列ASC|DESC,.n）UNIQUECLUSTERED NONCLUSTEREDASC|DESC创建唯一索引创建聚集索引创建非聚集索引创建排序方式，默认为升序(ASC)14数据库技术与应用数据库技术与应用 创建索引创建索引例例5-1 在学院表中，除了学院编号字段是唯一的，学院名称字段也具有唯一值，对学院表中的学院名称字段建立唯一索引。创建唯一索引命令为：CREATEUNIQUEINDEXSCHOOLNAMEON学院(学院名称)例例5-2 学院表中已存在一个主键，现在学院名称字段上再创建聚集索引，看看结果如何？CREATE UNIQUE CLUSTERED INDEX 学院 ON 学院(学院名称)15数据库技术与应用数据库技术与应用 16例例5-3 在学院表中，如果会经常对学院名称、学院地址和学院电话进行查询，可以建立以学院名称、学院地址和学院电话为关键字的索引。CREATENONCLUSTEREDINDEXSCHOOLON学院(学院名称,学院地址,学院电话)创建索引创建索引数据库技术与应用数据库技术与应用 5.1.4 查看索引查看索引使用企业管理器查看索引使用企业管理器查看索引使用系统存储过程查看索引使用系统存储过程查看索引语法格式：语法格式：sp_helpindex name其中其中 name子句为指定当前数据库中的表的名称。子句为指定当前数据库中的表的名称。例例5-4 查看学院表的索引，其操作为：use学生成绩管理系统数据库gosp_helpindex学院17数据库技术与应用数据库技术与应用 5.1.5 删除索引删除索引例例5-5 删除学院表内名为SCHOOL的索引。use学生成绩管理系统数据库goIFEXISTS(SELECTnameFROMsysindexesWHEREname=SCHOOL)DROPINDEXSCHOOLON学院删除索引语句的语法格式如下：DROP INDEX index_name,nONdatabase_name.schema_name.|schema_name.table_or_vlew_name18数据库技术与应用数据库技术与应用 索引的优缺点索引的优缺点优点加快访问速度加快访问速度加强行的唯一性加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新对索引进行更新19数据库技术与应用数据库技术与应用 创建索引创建索引的指导原则的指导原则请按照下列标准选择建立索引的列。该列用于频繁搜索该列用于频繁搜索该列用于对数据进行排序该列用于对数据进行排序请不要使用下面的列创建索引：列中仅包含几个不同的值。列中仅包含几个不同的值。表中仅包含几行。为小型表创建索引可能不太划算表中仅包含几行。为小型表创建索引可能不太划算，因为因为SQL ServerSQL Server在索引中搜索数据所花的时间比在在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长表中逐行搜索所花的时间更长 20数据库技术与应用数据库技术与应用 5.2 5.2 视图视图数据库技术与应用数据库技术与应用 5.2.1 5.2.1 视图的概念视图的概念CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名姓名=stuName,学号学号=stuInfo.stuNo,基于学员信息表和成绩表创建视图教员需要的视图：方便查看学员的成绩班主任需要的视图：方便查看学员的档案数据库技术与应用数据库技术与应用 5.2.1 视图的概念视图的概念1、什么是视图、什么是视图视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图23数据库技术与应用数据库技术与应用 2.视图的作用视图的作用 视图通常用来集中、简化和自定义每个用户对数据库的视图通常用来集中、简化和自定义每个用户对数据库的不同认识。视图可用作安全机制，方法是允许用户通过视图不同认识。视图可用作安全机制，方法是允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。访问数据，而不授予用户直接访问视图基础表的权限。(1)简化简化操作操作 (2)定制数据定制数据 (3)导出导出数据数据 (4)安全性安全性24视图的用途筛选表筛选表中中的行的行防止未经许可的用户访问敏感数据防止未经许可的用户访问敏感数据降低数据库的复杂程度降低数据库的复杂程度将将多个多个物理数据库抽象为一个逻辑数据库物理数据库抽象为一个逻辑数据库数据库技术与应用数据库技术与应用 5.2 视图的操作视图的操作5.2.1 创建视图创建视图1.使用企业管理器创建视图使用企业管理器创建视图2.使用使用Transact-SQL语句建立视图语句建立视图语法结构：语法结构：CREATE VIEW database_name.owner_name.view_name(column,)WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATAAS select_statementWITH CHECK OPTION25数据库技术与应用数据库技术与应用 1、为定制特定数据视图、为定制特定数据视图 例例5-6 如果经常要查询课程的名称、学分、学时和课程性质，表中的其他不需要，就只关注这些数据，为这些特定的数据建立一个视图。CREATEVIEW课程基本信息ASSELECT课程名称,学时数,学分数,课程性质FROM课程;例例5-7 在学生成绩管理系统数据库中创建学分数大于等于3分的相关课程信息视图，该视图选择1个基表(课程表)中的数据来显示学分大于等于3分的虚拟表。CREATEVIEW大于等于3学分课程信息ASSELECT*FROM课程where学分数=326数据库技术与应用数据库技术与应用 5-8 创建创建视图：程序设计成绩信息，要求能显示：“学号,姓名,专业班级,课程名称,成绩”等数据CREATEVIEW程序设计成绩信息ASSELECT学生.学号,姓名,专业班级,课程名称,成绩FROM学生innerjoin选课成绩on学生.学号=选课成绩.学号innerjoin课堂on选课成绩.课堂编号=课堂.课堂编号innerjoin课程on课堂.课程编号=课程.课程编号WHERE课程名称=C+程序设计基础数据库技术与应用数据库技术与应用 2、为简化、为简化SQL语句创建视图语句创建视图 例例5-9 要查询土木工程和工程力学两个专业在2017-2018学年均开过的必修课程，如果不用视图的话,语句为:SELECTdistinct课程.课程编号,课程.课程名称FROM课程INNERJOIN课堂ON课程.课程编号=课堂.课程编号INNERJOIN选课成绩ON课堂.课堂编号=选课成绩.课堂编号INNERJOIN学生ON选课成绩.学号=学生.学号WHERE课堂.开课年份=2017-2018AND课程.课程性质=必修AND学生.专业班级LIKE土木工程%INTERSECTSELECTdistinct课程.课程编号,课程.课程名称FROM课程INNERJOIN课堂ON课程.课程编号=课堂.课程编号INNERJOIN选课成绩ON课堂.课堂编号=选课成绩.课堂编号INNERJOIN学生ON选课成绩.学号=学生.学号WHERE课堂.开课年份=2017-2018AND课程.课程性质=必修AND学生.专业班级LIKE工程力学%ORDERBY课程.课程编号;28数据库技术与应用数据库技术与应用 1)1)可以先创建可以先创建视图视图：createview土木工程和工程力学选课信息asSELECTdistinct课程.课程编号,课堂.开课年份,课程.课程性质,课程.课程名称FROM课程INNERJOIN课堂ON课程.课程编号=课堂.课程编号INNERJOIN选课成绩ON课堂.课堂编号=选课成绩.课堂编号INNERJOIN学生ON选课成绩.学号=学生.学号WHERE学生.专业班级LIKE土木工程%INTERSECTSELECTdistinct课程.课程编号,课堂.开课年份,课程.课程性质,课程.课程名称FROM课程INNERJOIN课堂ON课程.课程编号=课堂.课程编号INNERJOIN选课成绩ON课堂.课堂编号=选课成绩.课堂编号INNERJOIN学生ON选课成绩.学号=学生.学号WHERE学生.专业班级LIKE工程力学%29数据库技术与应用数据库技术与应用 2）再再对对该视图查询并该视图查询并排序排序SELECT*FROM土木工程和工程力学选课信息WHERE开课年份=2017-2018AND课程性质=必修ORDERBY课程编号30数据库技术与应用数据库技术与应用 31 3 3、为数据安全创建视图、为数据安全创建视图例例5-10 如果经常要查询学生的学号、姓名和所在的专业班级，并且不希望学生表中的其他个人隐私信息被一般人查询，可为这些特定的数据建立一个视图。视图创建语句为：CREATEVIEW学生专业班级信息ASSELECT学号,姓名,专业班级FROM学生;数据库技术与应用数据库技术与应用 4 4、用用WITH CHECK OPTION创建视图创建视图例例5-7 建立的视图，建立的视图，如果要对学分数小于3分的课程信息视图插入相关的记录，请思考是否能插入成功？INSERTINTO大于等于3学分课程信息(课程编号,课程名称,学时数,学分数,课程性质,课程介绍,学院编号)VALUES(0016,大数据基础,32,2,选修,本课程意在普及大数据知识，帮助学生理解大数据时代的现实意义，了解大数据的处理流程，以及大数据采集、存储、分析、处理和管理的技术，以积极投身于大数据的应用。,06)32数据库技术与应用数据库技术与应用 4 4、用用WITH CHECK OPTION创建视图创建视图例例5-11在学生成绩管理系统数据库中创建“大于等于3学分课程信息2”视图，该视图选择1个基表(课程表)中的数据来显示学分数大于或等于3分的数据，并选择用WITHCHECKOPTION创建视图CREATEVIEW大于等于3学分课程信息2ASSELECT*FROM课程where学分数=3WITHCHECKOPTION33数据库技术与应用数据库技术与应用 4 4、用用WITH CHECK OPTION创建视图创建视图例例5-11 选择用WITHCHECKOPTION创建视图，如果要对学分数小于3分的课程信息视图插入相关的记录，请思考是否能插入成功？INSERTINTO大于等于3学分课程信息2(课程编号,课程名称,学时数,学分数,课程性质,课程介绍,学院编号)VALUES(0016,大数据基础,32,2,选修,本课程意在普及大数据知识，帮助学生理解大数据时代的现实意义，了解大数据的处理流程，以及大数据采集、存储、分析、处理和管理的技术，以积极投身于大数据的应用。,06)34数据库技术与应用数据库技术与应用 5.2.3 修改视图修改视图1使用企业管理器修改视图使用企业管理器修改视图2使用使用Transact-SQL语句修改视图语句修改视图可以使用可以使用ALTER VIEW语句来修改视图，其语法格式如下：语句来修改视图，其语法格式如下：ALTER VIEW .view_name (column ,.n )WITH ,.n AS select_statement WITH CHECK OPTION :=ENCRYPTION|SCHEMABINDING|VIEW_METADATA 35数据库技术与应用数据库技术与应用 3 3、应用案应用案例例5-12 修改视图（例5-8视图）：程序设计成绩信息，添加新字段：性别。命令为：ALTERVIEW程序设计成绩信息ASSELECT学生.学号,姓名,性别,专业班级,课程名称,成绩FROM学生innerjoin选课成绩on学生.学号=选课成绩.学号innerjoin课堂on选课成绩.课堂编号=课堂.课堂编号innerjoin课程on课堂.课程编号=课程.课程编号WHERE课程名称=C+程序设计基础36数据库技术与应用数据库技术与应用 5.2.4 删除视图删除视图1.使用企业管理器删除视图使用企业管理器删除视图2使用使用Transact-SQL语句删除视图语句删除视图可以使用可以使用DROP VIEW语句来删除视图，其语法格式如下：语句来删除视图，其语法格式如下：DROP VIEW view_name ,.n 其中其中View_name是要删除的视图名称，可以删除多个视图是要删除的视图名称，可以删除多个视图例例5-13删除视图：程序设计成绩信息，可执行下面的SQL语句。DROPVIEW程序设计成绩信息37数据库技术与应用数据库技术与应用 385.2.5 视图管理视图管理1.用企业管理器查看视图属性用企业管理器查看视图属性2.使用系统存储过程使用系统存储过程sp_helptext查看视图查看视图语法格式：语法格式：sp_helptext name例例5-14 查看视图“大于等于3学分课程信息”的文本定义信息，可使用以下语句。sp_helptext大于等于3学分课程信息数据库技术与应用数据库技术与应用 393.使用系统存储过程重命名视图使用系统存储过程重命名视图语法格式：语法格式：sp_rename object_name,new_name ,objtype=object_type 例例5-15 将视图“学生专业班级信息”重命名为“学生班级信息”，可执行如下SQL语句：sp_rename学生专业班级信息,学生班级信息数据库技术与应用数据库技术与应用 405.2.6 视图的应用视图的应用1.通过视图检索表数据通过视图检索表数据 在建立视图后，可以用任一种查询方式检索视图在建立视图后，可以用任一种查询方式检索视图数据，对视图可使用连接、数据，对视图可使用连接、GROUP BY子句、子查询子句、子查询等以及它们的任意组合。等以及它们的任意组合。例例5-16 创建一个大于等于3学分的视图，并通过视图查询相关数据。1）创建视图CREATEVIEW大于等于3学分课程信息ASSELECT*FROM课程where学分数=32）查看已经创建好的大于等于3学分的视图数据。SELECT*FROM大于等于3学分课程信息数据库技术与应用数据库技术与应用 412.通过视图添加表数据通过视图添加表数据语法格式：语法格式：INSERT INTO 视图名视图名 VALUES(列值列值1，列值，列值2，列值，列值3，列值，列值n)3.通过视图修改表数据通过视图修改表数据语法格式：语法格式：UPDATE 视图名视图名SET 列列1=列值列值1 列列2=列值列值2 .列列n=列值列值n WHERE 逻辑表达式逻辑表达式数据库技术与应用数据库技术与应用 42 4.通过视图删除表数据通过视图删除表数据 尽管视图不一定包含基础表的所有列，但可尽管视图不一定包含基础表的所有列，但可以通过视图删除基础表的数据行。以通过视图删除基础表的数据行。语法格式：语法格式：DELETE FROM 视图名视图名 WHERE 逻辑表达式逻辑表达式 数据库技术与应用数据库技术与应用 43 视图的限制视图的限制 在创建或使用视图时，应遵守以下规定：在创建或使用视图时，应遵守以下规定：(1)在一个批事务中，在一个批事务中，CREATE VIEW语句不能与其他语句不能与其他SQL语句结合使用。语句结合使用。(2)不能在视图上建立触发器和索引。不能在视图上建立触发器和索引。(3)一个视图最多只能有一个视图最多只能有250列。列。(4)不能基于临时表建立视图，由不能基于临时表建立视图，由SELECT INTO语句可建立临时表，在语句可建立临时表，在 CREATE VIEW语句中不能使用语句中不能使用INTO关键字。关键字。(5)在在CREATE VIEW语句中不能使用联合操作符语句中不能使用联合操作符UNION。(6)在插入或修改数据时，每个在插入或修改数据时，每个INSERT语句和语句和UPDATE语句不能影响一语句不能影响一个以上的视图基表。个以上的视图基表。(5)对视图中的计算列不允许使用对视图中的计算列不允许使用INSERT语句插入数据。语句插入数据。(8)UPDATE语句不能够修改视图的计算列数据，也不允许它修改包含集语句不能够修改视图的计算列数据，也不允许它修改包含集合的函数和内置函数的视图列。合的函数和内置函数的视图列。(9)在视图的在视图的Text和和Image数据类型的列上不允许使用数据类型的列上不允许使用READTEXT和和WRITETEXT语句。语句。数据库技术与应用数据库技术与应用 44本章小结本章小结 (1)索引是对数据库表中一个或多个字段的值进行排序而创建的一种索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。建立索引的主要目的是加速数据检索和连接、优化查分散存储结构。建立索引的主要目的是加速数据检索和连接、优化查询、强制实行惟一性等操作。主要有询、强制实行惟一性等操作。主要有3种类型的索引，即惟一性索引、种类型的索引，即惟一性索引、主键索引和聚集索引。主键索引和聚集索引。(2)在在SQL Server 2000中对索引的基本操作包括创建索引、查看索引、中对索引的基本操作包括创建索引、查看索引、更改索引、删除索引和全文索引，可以在企业管理器或通过更改索引、删除索引和全文索引，可以在企业管理器或通过Transact-SQL语句实现索引操作。语句实现索引操作。(3)视图是一种数据库对象，是从一个或多个表或视图中导出的虚拟视图是一种数据库对象，是从一个或多个表或视图中导出的虚拟表。表。(4)视图的操作主要包括视图的创建、修改、删除和重命名等，其操视图的操作主要包括视图的创建、修改、删除和重命名等，其操作可以通过作可以通过SQL Server企业管理器和企业管理器和Transact-SQL语句来实现。语句来实现。(5)通过视图可以完成某些和基础表相同的一些数据操作，如数据的通过视图可以完成某些和基础表相同的一些数据操作，如数据的检索、添加、修改和删除。检索、添加、修改和删除。数据库技术与应用数据库技术与应用 SQL-ServerSQL-Server再再 见见 ！