第5章视图和索引

1、数据库原理与应用（第2版）人民邮电出版社第第5 5章章 视图和索引视图和索引v5.1 5.1 视图视图v5.2 5.2 索引索引5.1 5.1 视图视图 v5.1.1 5.1.1 基本概念基本概念v5.1.2 5.1.2 定义视图定义视图v5.1.3 5.1.3 通过视图查询数据通过视图查询数据v5.1.4 5.1.4 修改和删除视图修改和删除视图 v5.1.5 5.1.5 视图的作用视图的作用 5.1.1 5.1.1 基本概念基本概念v视图是由从数据库的基本表中选取出来视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口，是基本表的部的数据组成的逻辑窗口，是基本表的部分行和列数据的组合。分

2、行和列数据的组合。v视图是一个虚表。数据库中只存放视图视图是一个虚表。数据库中只存放视图的定义，而不存放视图包含的数据，这的定义，而不存放视图包含的数据，这些数据仍存放在原来的基本表中。些数据仍存放在原来的基本表中。v基本表中的数据如果发生变化，从视图基本表中的数据如果发生变化，从视图中查询出的数据也会随之变化。中查询出的数据也会随之变化。 基本概念（续）基本概念（续）v视图可以从一个基本表中提取数据，视图可以从一个基本表中提取数据，也可以从多个基本表中提取数据，甚也可以从多个基本表中提取数据，甚至还可以从其他视图中提取数据，构至还可以从其他视图中提取数据，构成新的视图。成新的视图。v对视图数

3、据的操作最终都会转换为对对视图数据的操作最终都会转换为对基本表的操作。基本表的操作。 基本概念（续）基本概念（续）5.1.2 5.1.2 定义视图定义视图v定义视图的格式如下：定义视图的格式如下：CREATE VIEW CREATE VIEW ( (列名列名 ,.n ) ,.n )AS AS 查询语句查询语句v查询语句中通常不包含查询语句中通常不包含ORDER BYORDER BY和和DISTINCTDISTINCT子句。子句。v在定义视图时要么指定视图的全部列名，要在定义视图时要么指定视图的全部列名，要么全部省略不写，不能只写视图的部分列名。么全部省略不写，不能只写视图的部分列名。 必须明确

4、指定视图所有列名的情况必须明确指定视图所有列名的情况 v某个目标列不是简单的列名，而是函数某个目标列不是简单的列名，而是函数或表达式。或表达式。v多表连接时选出了几个同名列作为视图多表连接时选出了几个同名列作为视图的字段。的字段。v需要在视图中为某个列选用新的更合适需要在视图中为某个列选用新的更合适的列名。的列名。1 1定义单源表视图定义单源表视图v视图的数据取自一个表的部分行和列。视图的数据取自一个表的部分行和列。v例例1 1 建立查询信息管理系学生的学号、建立查询信息管理系学生的学号、姓名、性别和年龄的视图。姓名、性别和年龄的视图。CREATE VIEW IS_StudentCREATE

5、VIEW IS_StudentASAS SELECT Sno, Sname, Ssex, Sage SELECT Sno, Sname, Ssex, Sage FROM Student FROM Student WHERE Sdept = WHERE Sdept = 信息管理系信息管理系 2 2定义多源表视图定义多源表视图v例例2 2 建立信息管理系选修了建立信息管理系选修了C001C001课程的学课程的学生的学号、姓名和成绩的视图。生的学号、姓名和成绩的视图。CREATE VIEW V_IS_S1(Sno, Sname, Grade)CREATE VIEW V_IS_S1(Sno, Snam

6、e, Grade)ASAS SELECT Student.Sno, Sname, Grade SELECT Student.Sno, Sname, Grade FROM Student JOIN SC FROM Student JOIN SC ON Student.Sno = SC.Sno ON Student.Sno = SC.Sno WHERE Sdept = WHERE Sdept = 信息管理系信息管理系 AND SC.Cno = C001 AND SC.Cno = C001 例例83 3在已有视图上定义新视图在已有视图上定义新视图v例例3 3 利用例利用例1 1建立的视图，建立查询信

7、建立的视图，建立查询信息管理系年龄小于息管理系年龄小于2020的学生的学号、姓的学生的学号、姓名和年龄的视图。名和年龄的视图。CREATE VIEW IS_Student_SageCREATE VIEW IS_Student_SageASAS SELECT Sno, Sname, Sage SELECT Sno, Sname, Sage FROM IS_Student WHERE Sage 20 FROM IS_Student WHERE Sage = 60 WHERE Grade = 60v转换成的对最终基本表的查询：转换成的对最终基本表的查询：SELECT S.Sno, Sname, Gr

8、ade FROM SCSELECT S.Sno, Sname, Grade FROM SC JOIN Student S ON S.Sno = SC.Sno JOIN Student S ON S.Sno = SC.Sno WHERE Sdept = WHERE Sdept = 信息管理系信息管理系 AND SC.Cno = C001 AND SC.Cno = C001 AND Grade = 60 AND Grade = 60 通过视图查询数据（续）通过视图查询数据（续）v例例9 9 查询信息管理系学生的学号、姓名、所选课查询信息管理系学生的学号、姓名、所选课程的课程名。程的课程名。SELE

9、CT v.Sno, Sname, Cname SELECT v.Sno, Sname, Cname FROM IS_Student v JOIN SC ON v.Sno = SC.Sno FROM IS_Student v JOIN SC ON v.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno JOIN Course C ON C.Cno = SC.Cnov转换成的对最终基本表的查询：转换成的对最终基本表的查询：SELECT S.Sno, Sname, Cname SELECT S.Sno, Sname, Cname FROM Student S J

10、OIN SC ON S.Sno = SC.Sno FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno JOIN Course C ON C.Cno = SC.Cno WHERE Sdept = WHERE Sdept = 信息管理系信息管理系 通过视图查询数据（续）通过视图查询数据（续）v例例10 10 利用例利用例6 6建立的视图，查询平均成建立的视图，查询平均成绩大于等于绩大于等于8080分的学生的学号和平均成分的学生的学号和平均成绩。绩。SELECT SELECT * * FROM S_G FR

11、OM S_G WHERE AverageGrade = 80 WHERE AverageGrade = 80v正确的转换语句是：正确的转换语句是：SELECT Sno, AVG(Grade) FROM SCSELECT Sno, AVG(Grade) FROM SC GROUP BY Sno GROUP BY Sno HAVING AVG(Grade) = 80 HAVING AVG(Grade) = 805.1.4 5.1.4 修改和删除视图修改和删除视图 v1 1修改视图修改视图v语法格式：语法格式：ALTER VIEW ALTER VIEW 视图名视图名 ( ( 列名列名 ,.n ) ,

12、.n ) ASAS 查询语句查询语句 v修改视图的修改视图的SQLSQL语句与定义视图的语句基语句与定义视图的语句基本是一样的，只是将本是一样的，只是将CREATE VIEWCREATE VIEW改成了改成了ALTER VIEWALTER VIEW。 示例示例v例例11 11 修改例修改例6 6定义的视图，使其统计每个定义的视图，使其统计每个学生的考试平均成绩和修课总门数。学生的考试平均成绩和修课总门数。ALTER VIEW S_G(Sno, ALTER VIEW S_G(Sno, AverageGrade,Count_Cno)AverageGrade,Count_Cno)AS AS SELE

13、CT Sno, AVG(Grade), Count( SELECT Sno, AVG(Grade), Count(* *) ) FROM SC FROM SC GROUP BY Sno GROUP BY Sno5.1.4 5.1.4 修改和删除视图（续）修改和删除视图（续）v2 2删除视图删除视图v语法格式：语法格式：DROP VIEW DROP VIEW v例例12 12 删除例删除例1 1定义的定义的IS_StudentIS_Student视图。视图。DROP VIEW IS_Student DROP VIEW IS_Student 注意注意v如果被删除的视图是其他视图的数据源，如果被删除

14、的视图是其他视图的数据源，那么删除该视图，其导出视图将无法再那么删除该视图，其导出视图将无法再使用。使用。v同样，如果视图的基本表被删除了，视同样，如果视图的基本表被删除了，视图也将无法使用。图也将无法使用。v因此，在删除基本表和视图时一定要注因此，在删除基本表和视图时一定要注意是否存在引用被删除对象的视图，如意是否存在引用被删除对象的视图，如果有应同时删除。果有应同时删除。5.1.5 5.1.5 视图的作用视图的作用 v简化数据查询语句简化数据查询语句 v使用户能从多角度看待同一数据使用户能从多角度看待同一数据 v提高了数据的安全性提高了数据的安全性 v提供了一定程度的逻辑独立性提供了一定程

15、度的逻辑独立性 5.2 5.2 索引索引v5.2.1 5.2.1 基本概念基本概念v5.2.2 5.2.2 索引分类索引分类v5.2.3 5.2.3 创建和删除索引创建和删除索引5.2.1 5.2.1 基本概念基本概念v索引与图书中的目录类似。索引与图书中的目录类似。v在数据库中，索引使对数据的查找不需要对整个在数据库中，索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。表进行扫描，就可以在其中找到所需数据。v图书的目录注明了各部分内容所对应的页码，而图书的目录注明了各部分内容所对应的页码，而数据库中的索引是一个表中所包含的值的列表，数据库中的索引是一个表中所包含的值的列表，

16、其中注明了表中的各行数据所在的存储位置。其中注明了表中的各行数据所在的存储位置。v可以为表中的单个列建立索引，也可以为一组列可以为表中的单个列建立索引，也可以为一组列建立索引。建立索引。v索引由索引项组成，索引项由来自表中每一行的索引由索引项组成，索引项由来自表中每一行的一个或多个列（称为索引关键字）组成。一个或多个列（称为索引关键字）组成。v当在多个列上建立索引时，系统按索引列出现的当在多个列上建立索引时，系统按索引列出现的先后顺序对索引列进行排序。先后顺序对索引列进行排序。 索引及数据间对应关系索引及数据间对应关系 索引利弊索引利弊v合适的索引可以提高查询效率。合适的索引可以提高查询效率。

17、v索引为查找所带来的性能好处是有代价索引为查找所带来的性能好处是有代价的：的： 索引在数据库中会占用一定的存储空间。索引在数据库中会占用一定的存储空间。 在对数据进行插入、更改和删除操作时，在对数据进行插入、更改和删除操作时，为使索引与数据保持一致，还需要对索引为使索引与数据保持一致，还需要对索引进行相应维护。对索引的维护是需要花费进行相应维护。对索引的维护是需要花费时间。时间。5.2.2 5.2.2 索引存储结构及分类索引存储结构及分类v存储结构：存储结构： B B树树v分类：分类： 聚集索引聚集索引 非聚集索引非聚集索引索引的存储结构索引的存储结构v索引一般都采用索引一般都采用B B树结构

18、来存储索引项。树结构来存储索引项。构建过程查找过程聚集索引聚集索引 v数据按索引列进行物理排序数据按索引列进行物理排序v类似于电话号码簿类似于电话号码簿v特点：叶级节点即特点：叶级节点即是索引页，同时也是是索引页，同时也是数据页。数据页。数据示例数据示例enoenamedeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS聚集索引示例聚集索引示例查找过程查找过程v当在建有聚集索引的列上查找数据时当在建有聚集索引的列上查找数据时v首先从聚集索引树的入口（根节点）开首先从聚集索

19、引树的入口（根节点）开始逐层向下查找，始逐层向下查找，v直到达到直到达到B-B-树索引的叶级，也就是达到树索引的叶级，也就是达到了要找的数据所在的数据页，了要找的数据所在的数据页，v最后只在这个数据页中查找所需数据最后只在这个数据页中查找所需数据查找示例查找示例SELECT SELECT * * FROM employee WHERE eno=E08 FROM employee WHERE eno=E08说明说明v在聚集索引的叶节点中，数据按聚集索在聚集索引的叶节点中，数据按聚集索引项的值进行物理排序。引项的值进行物理排序。v因此，聚集索引很类似于电话号码簿。因此，聚集索引很类似于电话号码簿。

20、v一个表只能包含一个聚集索引。一个表只能包含一个聚集索引。v但但一个一个索引可以由多个列（组合索引）索引可以由多个列（组合索引）组成组成。下列情况可考虑创建聚集索下列情况可考虑创建聚集索 v包含大量非重复值的列。包含大量非重复值的列。v使用下列运算符返回一个范围值的查询：使用下列运算符返回一个范围值的查询：BETWEEN ANDBETWEEN AND、 、=、 和和 =。v返回大型结果集的查询。返回大型结果集的查询。v经常被用作连接的列。经常被用作连接的列。vORDER BYORDER BY或或GROUP BYGROUP BY子句中指定的列。子句中指定的列。下列情况不适于建立聚集索引下列情况不

21、适于建立聚集索引v频繁更改的列。频繁更改的列。v字节长的列。字节长的列。因为聚集索引的因为聚集索引的索引项的值将被所有非聚集索引索引项的值将被所有非聚集索引作为查找关键字使用，并被存储作为查找关键字使用，并被存储在每个非聚集索引的在每个非聚集索引的B B树的叶级树的叶级索引项中。索引项中。非聚集索引非聚集索引v非聚集索引与图书后边的术语表类似。数非聚集索引与图书后边的术语表类似。数据存储在一个地方，术语表存储在另一个据存储在一个地方，术语表存储在另一个地方。而且数据并不按术语表的顺序存放，地方。而且数据并不按术语表的顺序存放，但术语表中的每个词在书中都有确切的位但术语表中的每个词在书中都有确切

22、的位置。置。v非聚集索引就类似于术语表，而数据就类非聚集索引就类似于术语表，而数据就类似于一本书的内容。似于一本书的内容。非聚集索引的存储示意图非聚集索引的存储示意图 *非聚集索引与聚集索引的差别非聚集索引与聚集索引的差别v数据不按非聚集索引关键字值的顺序排序和数据不按非聚集索引关键字值的顺序排序和存储存储。v叶级节点不是存放数据的数据页叶级节点不是存放数据的数据页。v非聚集索引非聚集索引B B树的叶级节点是索引行。每个索树的叶级节点是索引行。每个索引行包含非聚集索引关键字值以及一个或多引行包含非聚集索引关键字值以及一个或多个行定位器，这些行定位器指向该关键字值个行定位器，这些行定位器指向该关

23、键字值对应的数据行（如果索引不唯一，则可能是对应的数据行（如果索引不唯一，则可能是多行）多行）在在enoeno列上建有非聚集索引的情形列上建有非聚集索引的情形 数数据示例据示例下述情况可考虑建立非聚集索引下述情况可考虑建立非聚集索引v包含大量非重复值的列。包含大量非重复值的列。v不返回大型结果集的查询。不返回大型结果集的查询。v经常作为查询条件使用的列。经常作为查询条件使用的列。v经常作为连接和分组条件的列。经常作为连接和分组条件的列。唯一索引唯一索引v确保索引列不包含重复值。确保索引列不包含重复值。v在组合唯一索引的情况下，可以确保索引在组合唯一索引的情况下，可以确保索引列中每个值的组合都是

24、唯一的。列中每个值的组合都是唯一的。v例如，如果在例如，如果在last_namelast_name、first_namefirst_name和和middle_initialmiddle_initial列的组合上创建了唯一索列的组合上创建了唯一索引引full_namefull_name，则该表中任何两个人都不，则该表中任何两个人都不可以具有完全相同的名字。可以具有完全相同的名字。v聚集索引和非聚集索引都可以是唯一的。聚集索引和非聚集索引都可以是唯一的。 说明说明v如果必须要实施唯一性来确保数据的完整性，如果必须要实施唯一性来确保数据的完整性，则应在列上创建则应在列上创建UNIQUEUNIQUE约

25、束或约束或PRIMARY KEYPRIMARY KEY约约束，而不要创建唯一索引。束，而不要创建唯一索引。v例如，如果限制身份证号码（例如，如果限制身份证号码（sidsid）列的取值）列的取值不重复，则可在不重复，则可在sidsid列上创建列上创建UNIQUEUNIQUE约束。约束。v实际上，当在表上创建实际上，当在表上创建PRIMARY KEYPRIMARY KEY约束或约束或UNIQUEUNIQUE约束时，系统会自动在这些列上创建约束时，系统会自动在这些列上创建唯一索引。唯一索引。 5.2.3 5.2.3 创建索引创建索引 CREATE UNIQUE CLUSTERED | NONCLUS

26、TEREDCREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX INDEX ON ON ( ( , , vUNIQUEUNIQUE：创建唯一索引。：创建唯一索引。vCLUSTEREDCLUSTERED：创建聚集索引。：创建聚集索引。vNONCLUSTEREDNONCLUSTERED：创建非聚集索引。：创建非聚集索引。v ASC | DESC ASC | DESC ：指定索引列的升序或降：指定索引列的升序或降序排序方式。默认值为序排序方式。默认值为ASCASC。v默认是创建非聚集索引。默认是创建非聚集索引。 示例示例v例例1 1 在在StudentStudent表的表的SnameSname列上创建非聚集索列上创建非聚集索引。