《索引和视图》PPT课件.ppt

第6章 索引和视图,6.1 索引 6.2 视图,6.1 索引,6.1.1 索引基本概念 6.1.2 索引的存储结构及分类 6.1.3 创建和删除索引,2020年7月29日3时6分,2,6.1 索引基本概念,索引与书籍中的目录类似。 索引使对数据的查找不需要对整个表进行扫描，就可以在其中找到所需数据。 可以为表中的单个列建立索引，也可以为一组列（索引项）建立索引。 索引一般采用B树结构。,2020年7月29日3时6分,3,索引及数据间的对应关系示意图,索引的组织方式,索引项按数据页(一块固定大小的连续存储空间)存储。 表中的全部索引连在一起。,6.1.2 索引的存储结构及分类,聚集索引（Clustered Index，也称为聚簇索引） 将数据按照索引项的顺序进行物理排序。 非聚集索引（Non-clustered Index，也称为非聚簇索引）。 不对数据进行物理排序。,两类索引相同点,聚集索引和非聚集索引一般都使用B-树结构来存储索引项， 都包含数据页和索引页， 索引页用来存放索引项和指向下一层的指针， 数据页用来存放数据。,B-树结构,聚集索引,聚集索引的B-树按自下而上建立，最下层的叶级节点存放数据，它同时也是数据页。 多个数据页生成一个中间层节点的索引页，然后再由数个中间层的节点的索引页合成更上层的索引页， 如此上推，直到生成顶层的根节点的索引页。,9,建有聚集索引的表的存储结构示意图,数据示例,聚集索引,非聚集索引,聚集索引示例,12,数据示例,查找过程,当在建有聚集索引的列上查找数据时 首先从聚集索引树的入口（根节点）开始逐层向下查找， 直到达到B-树索引的叶级，也就是达到了要找的数据所在的数据页， 最后只在这个数据页中查找所需数据,查找示例,SELECT * FROM employee WHERE eno=E08,说明,在聚集索引的叶节点中，数据按聚集索引项的值进行物理排序。 因此，聚集索引很类似于电话号码簿。 一个表只能包含一个聚集索引。 但一个索引可以由多个列（组合索引）组成。,下列情况可考虑创建聚集索,包含大量非重复值的列。 使用下列运算符返回一个范围值的查询：BETWEEN AND、=、 和 =。 被连续访问的列。 不返回大型结果集的查询。 经常被用作连接的列。 ORDER BY或GROUP BY子句中指定的列。,下列情况不适于建立聚集索引,频繁更改的列。 字节长的列。因为聚集索引的索引项的值将被所有非聚集索引作为查找关键字使用，并被存储在每个非聚集索引的B树的叶级索引项中。,非聚集索引,非聚集索引与图书后边的术语表类似。数据存储在一个地方，术语表存储在另一个地方。而且数据并不按术语表的顺序存放，但术语表中的每个词在书中都有确切的位置。 非聚集索引就类似于术语表，而数据就类似于一本书的内容。,非聚集索引的存储示意图,非聚集索引与聚集索引的差别,数据不按非聚集索引关键字值的顺序排序和存储。 叶级节点不是存放数据的数据页。 非聚集索引B树的叶级节点是索引行。每个索引行包含非聚集索引关键字值以及一个或多个行定位器，这些行定位器指向该关键字值对应的数据行（如果索引不唯一，则可能是多行）,在eno列上建有非聚集索引的情形,数据示例,下述情况可考虑建立非聚集索引,包含大量非重复值的列。 不返回大型结果集的查询。 经常作为查询条件使用的列。 经常作为连接和分组条件的列。,唯一索引,确保索引列不包含重复值。 在组合唯一索引的情况下，可以确保索引列中每个值的组合都是唯一的。 例如，如果在last_name、first_name和middle_initial列的组合上创建了唯一索引full_name，则该表中任何两个人都不可以具有完全相同的名字。 聚集索引和非聚集索引都可以是唯一的。,说明,如果必须要实施唯一性来确保数据的完整性，则应在列上创建UNIQUE约束或PRIMARY KEY约束，而不要创建唯一索引。 例如，如果限制身份证号码（sid）列的取值不重复，则可在sid列上创建UNIQUE约束。 实际上，当在表上创建PRIMARY KEY约束或UNIQUE约束时，系统会自动在这些列上创建唯一索引。,创建索引,CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名(列名 ,.n) UNIQUE：创建唯一索引。 CLUSTERED：创建聚集索引。 NONCLUSTERED：创建非聚集索引。 如果没有指定索引类型，则默认是创建非聚集索引。,示例,例1 为Student表的Sname列创建非聚集索引。 CREATE INDEX Sname_ind ON Stuent (Sname) 例2 为Student表的Sid列创建唯一聚集索引。 CREATE UNIQUE CLUSTERED INDEX Sid_ind ON Stuent (Sid ),示例,例3 为Employee表的FirstName和LastName列创建一个聚集索引。 CREATE CLUSTERED INDEX EName_ind ON Employee(FirstName,LastName),删除索引,删除索引的基本语法格式为： DROP INDEX 例4 删除Student表的Sname_ind索引 DROP INDEX Sname_ind,6.2 视图,6.2.1 概念概念 6.2.2 定义视图 6.2.3 通过视图查询数据 6.2.4 修改和删除视图 6.2.5 视图的作用,6.2.1 基本概念,视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口， 是基本表的部分行和列数据的组合。 视图是一个虚表。 数据库中只存储视图的定义，而不存储视图所包含的数据。,视图与基本表关系,6.2.2 定义视图,CREATE VIEW (视图列名表) AS 查询语句 查询语句中通常不包含ORDER BY和DISTINCT子句。 在定义视图时要么指定视图的全部列名，要么全部省略不写。,说明,如果省略了视图的“列名表”部分，则视图的列名与查询语句结果显示的列名相同。 如下三种情况下必须明确指定视图列名： 某个目标列是函数或表达式，并且没有为这样的列起别名。 多表连接时选出了几个同名列作为视图的字段。 需要在视图中为列选用新的更合适的列名。,定义单源表视图,视图取自一个基本表的部分行、列，视图行列与基本表行列对应， 一般可看可改。,示例,CREATE VIEW IS_Student AS SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = 信息系,例1建立信息系学生的的学号、姓名、性别和年龄视图。,定义多源表视图,子查询源表多于一个， 一般可看不可改。,36,示例,CREATE VIEW V_IS_S1（Sno, Sname, Grade） AS SELECT Student.Sno, Sname, Sage FROM Student, SC WHERE Sdept = 信息系 AND Student.Sno = SC.Sno AND SC.Cno = c01,例2建立信息系选修了c01课程的学生的学号、姓名和成绩的视图。,在已有视图上定义新视图,视图的数据源可以来自其它的视图。,示例,CREATE VIEW IS_Student_Sage AS SELECT Sno, Sname, Sage FROM IS_Student WHERE Sage 20,例3利用例1建立的视图，建立查询信息管理系年龄小于20的学生的学号、姓名和年龄的视图 。,示例,例4 在例1所建的视图基础上，例2的视图定义可改为： CREATE VIEW V_IS_S2(Sno,Sname,Grade) AS SELECT SC.Sno, Sname, Grade FROM IS_Student JOIN SC ON IS_Student.Sno = SC.Sno WHERE Cno = c01,2020年7月29日3时6分,40,定义带表达式的视图,定义基本表时，为减少数据冗余，表中只存放基本数据。 由基本数据经过各种计算派生出的数据一般不存储。 由于视图中的数据并不实际存储，因此，可以在视图中设置一些附加列来保存这些派生的数据。 由于这些附加列在基本表中并不实际存在，因此称这些列为虚拟列。 称包含虚拟列的视图为带表达式的视图。,示例,例5.定义一个查询学生出生年份的视图，内容包括学号，姓名和出生年份。 CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno, Sname, 2010 - Sage FROM Student,含分组统计信息的视图,子查询中含GROUP BY子句，视图行列由基本表行列得到， 数据只可看不可改。,示例,CREATE VIEW S_G AS SELECT Sno,AVG(Grade) AverageGrade FROM SC GROUP BY Sno,例6定义一个查询每个学生的学号及平均成绩的视图。,6.2.3 通过视图查询数据,视图定义好后，可以对其进行查询， 通过视图查询数据同基本表一样。,示例,例7：利用例1建立的视图，查询信息系男生的信息。 SELECT * FROM IS_Student WHERE Ssex = 男,转换成相关基本表的等价查询,SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = 信息系 AND Ssex = 男,示例,例8 查询信息系选修了“cC01”号课程且成绩大于等于60的学生的学号、姓名和成绩。 这个查询可以利用例2的视图实现。 SELECT * FROM V_IS_S1 WHERE Grade = 60 此查询转换成的对最终基本表的查询： SELECT S.Sno, Sname, Grade FROM SC JOIN Student S ON S.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c01 AND Grade = 60,示例,例9.查询信息系学生的学号、姓名、所选课程名 SELECT v.Sno, Sname, Cname FROM IS_Student v JOIN SC ON v.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno 此查询转换成的对最终基本表的查询： SELECT S.Sno, Sname, Cname FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Sdept = 信息系,示例,例10 利用例6建立的视图，查询平均成绩大于等于80分的学生的学号和平均成绩。 SELECT * FROM S_G WHERE AverageGrade = 80 此查询转换成的对最终基本表的查询： SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) = 80,注意,若将例10的查询转换为如下形式： SELECT Sno, AVG(Grade) FROM SC WHERE AVG(Grade) 80 GROUP BY Sno,x,视图到基本表的转换有些并不是直接进行的。 目前大多数DBMS对这种含有统计函数的视图的查询均能进行正确的转换。,通过视图修改数据,也可以通过视图修改基本表中的数据， 但并不是所有的视图都可以用于修改数据。如经过统计或表达式计算得到的视图。 能否通过视图修改数据的基本原则： 如果这个操作能够最终落实到基本表上，并成为对基本表的正确操作，则可以 否则不行。,6.2.4 修改和删除视图,1.修改视图 ALTER VIEW 视图名( 列名 ,.n ) AS 查询语句,示例,例11.修改S_G视图，使其统计每个学生的考试平均成绩和修课总门数。 ALTER VIEW S_G(Sno, AverageGrade,Count_Cno) AS SELECT Sno, AVG(Grade), Count(*) FROM SC GROUP BY Sno,2. 删除视图,格式： DROP VIEW 例删除IS_Student视图。 DROP VIEW IS_Student,6.2.5 视图的作用,简化数据查询语句 使用户能从多角度看待同一数据 提高了数据的安全性 提供了一定程度的逻辑独立性,