数据库原理与应用（MySQL版） 课件 第6章 索引和视图

第6章索引和视图数据库原理与应用1本章内容6.1索引6.2视图26.1索引6.1.1索引基本概念6.1.2索引的存储结构及分类6.1.3创建和删除索引36.1.1索引基本概念建立索引是为了加快数据的查询速度。可以为表中的单个列建立索引，也可以为一组列建立索引。索引由索引项组成，索引项由来自表中一个或多个列（称为搜索关键字或索引关键字）组成。如：对于一个由A、B、C三个列组成的索引，可以在A以及A、B和A、B、C上对其进行高效搜索。4索引及数据间的对应关系示意5使用索引的代价利用索引提高查询效率是以占用空间和增加数据更改的时间为代价的。索引在数据库中会占用一定的存储空间来存储索引信息。对数据进行插入、更改和删除操作时，需要对索引进行相应维护，需要花费时间的。6数据页在数据库管理系统中，数据一般是按数据页存储的，数据页是一块固定大小的连续存储空间。有的数据库管理系统数据页的大小是固定的MySQL的数据页就固定为16KB；有些数据库管理系统的数据页大小可由用户设定DB2。索引项也按数据页存储，大小与存放数据的数据页相同。7数据页组织方式示意8存放数据的数据页与存放索引项的数据页采用的都是通过指针链接在一起的方式连接各数据页。在页头包含指向下一页及前面页的指针，这样就可以将表中的全部数据或者索引链在一起。6.1.2索引的存储结构及分类索引的存储结构索引的分类聚集索引非聚集索引9索引的B树存储结构示意图10聚集索引聚集索引（也称为聚簇索引）的B树是自下而上建立的，最下层的叶级节点存放的是数据，因此它既是索引页，同时也是数据页。多个数据页生成一个中间层节点的索引页，然后再由数个中间层节点的索引页合成更上层的索引页，如此上推，直到生成顶层的根节点的索引页。11说明在聚集索引的叶节点中，数据按聚集索引关键字的值进行物理排序。当在建有聚集索引的列上查找数据时，系统首先从聚集索引树的入口（根节点）开始逐层向下查找，直到达到B树索引的叶级，最后只在这个数据页中查找所需数据即可。12建有聚集索引的表的存储结构示意13例子:employee表14EnoEnameDeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS在Eno列上建有聚集索引的B树15适合建立聚集索引的情况包含大量非重复值的列。使用下列运算符返回一个范围值的查询：BETWEENAND、>、>=、<和<=。经常被用作连接的列。ORDERBY或GROUPBY子句中指定的列。注意：对于频繁进行更改操作的列则不适合建立聚集索引。16非聚集索引非聚集索引也称为非聚簇索引。非聚集索引就类似于术语表，而数据就类似于一本书的内容。17非聚集索引的B树存储示意图18非聚集索引与聚集索引的差别数据不按非聚集索引关键字值的顺序排序和存储。非聚集索引的叶级节点不是存放数据的数据页。非聚集索引B树的叶级节点是索引行。每个索引行包含非聚集索引关键字值以及一个或多个行定位器（如果索引不唯一，则可能是多行）。19在Eno列上建有非聚集索引的情形20适合建立非聚集索引的情况包含大量非重复值的列。经常作为查询条件使用的列。经常作为连接和分组条件的列。216.1.3创建和删除索引创建索引查看索引删除索引22创建索引在MySQL中，建立索引的三种方法：在已建好的表上建立索引；通过更改表结构建立索引；在建表的同时建立索引。23在已建好的表上建立索引CREATE[UNIQUE]INDEX<索引名>ON<表名>(<列名>[(长度)][ASC|DESC][,...n])UNIQUE：表示要创建的索引是唯一索引。ASC|DESC：指定索引项的排序方式。ASC为升序，DESC为降序默认为ASC24通过更改表结构建立索引ALTERTABLE<表名>ADDINDEX<索引名>

(<列名>[(长度)][ASC|DESC])25在建表的同时建立索引CREATETABLE<表名>(<列名><数据类型>[完整性约束]，

…<列名><数据类型>[完整性约束]，INDEX<索引名>(<列名>[ASC|DESC]))26唯一索引建有唯一索引的列中的数据不允许有重复值。例：若在(LastName,FirstName,MiddleInitial)三个列上创建了一个唯一索引FullName，则表中任何两个人都不可以有完全相同的名字。如果必须要实施唯一性来确保数据的完整性，比较好的做法是在列上创建UNIQUE约束或PRIMARYKEY约束，而不是创建唯一索引。27创建唯一索引和普通索引的例子例6-1为students表的phone列创建唯一索引。CREATEUNIQUEINDEXPhone_indONstudents(phone);例6-2为students表的Sname列创建普通索引。CREATEINDEXSname_indONstudents(Sname);或ALTERTABLEstudentsADDINDEXSname_ind(Sname);28创建多列索引（组合索引）索引可以由多个列组成——组合索引。例6-3创建Employee表，同时在FirstName和LastName列上创建一个多列的普通索引。CREATETABLEEmployee(IdINTPRIMARYKEY,FirstNamevarchar(20),LastNamevarchar(20),INDEXEName_ind(FirstName,LastName));29查看索引在MySQL中，可以使用SHOWINDEX语句查看表中创建的索引。其一般语法格式为：SHOWINDEXFROM<表名>[FROM<数据库名>]或者SHOWINDEXFROM<数据库名>.<表名>例6-4查看students表的索引。SHOWINDEXFROMstudents;30删除索引在MySQL中，删除索引使用DROPINDEX语句或AlTERTABLE语句实现。其一般语法格式为：DROPINDEX<索引名>on<表名>或：AlTERTABLE<表名>DROPINDEX<索引名>例6-5删除students表中的Sname_ind索引。DROPINDEXSname_indonstudents;31本章内容6.1索引6.2视图326.2视图6.2.1视图基本概念6.2.2定义视图6.2.3通过视图查询数据6.2.4修改视图定义6.2.5更新视图数据6.2.6删除视图6.2.7视图的作用336.2.1视图基本概念视图（view）是数据库中的一个对象。视图对应关系数据库中的外模式。视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口，是基本表的部分行和列数据的组合。视图是一个虚表。数据库中只存储视图的定义，而不存储视图所包含的数据。对视图数据的操作最终都会转换为对基本表的操作。34视图机制的好处第一，视图数据始终与基本表数据保持一致。第二，节省存储空间。35视图与基本表的关系示意图366.2.2定义视图MySQL定义视图的一般格式：CREATE[ORREPLACE]VIEW<视图名>[(列名[,...n])]ASSELECT语句37说明定义视图中引用的表或视图必须存在。在视图定义中允许使用ORDERBY子句，但如果从该视图查询数据时也使用了ORDERBY子句，则系统将忽略视图定义中使用的ORDERBY子句。在定义视图时要么指定视图的全部列名，要么全部省略不写，不能只写视图的部分列名。如果与视图相关联的表或视图被删除，则该视图将不能使用。38定义单源表视图单源表的行列子集视图指视图的数据取自一个基本表的部分行和列视图行列与基本表行列对应。一般支持通过视图对数据进行查询和修改操作。39定义单源表视图的例子例6-5建立查询“计算机学院”学生的学号、姓名、性别和所在学院的视图CS_Student。CREATEVIEWCS_StudentAS SELECTSID,sname,gender,college FROMstudents

WHEREcollege='计算机学院';40定义多源表视图多源表视图指定义视图的查询语句涉及多张表。例6-6建立查询“计算机学院”借了“零基础入门学习C语言”图书的学生学号、姓名和借书时间的视图。CREATEVIEWV_CS_S1(SID,sname,borrow_time)AS

SELECTs.SID,sname,borrow_timeFROMstudentssJOINborrowbsONs.SID=bs.SID

JOINBooksbonbs.ISBN=b.ISBNWHEREcollege='计算机学院'ANDbname='零基础入门学习C语言';41在已有视图上定义新视图视图的来源可以是基本表，也可以是已经建立好的视图例6-7利用例6-5建立的视图，建立查询“计算机学院”女生的学号和姓名的视图。CREATEVIEWCS_Student_femaleASSELECTSID,SnameFROMCS_StudentWHEREgender='女';42视图来源是视图和基本表视图的来源还可以是视图和基本表的组合。例6-8利用例6-5，例6-6的视图定义可改为：CREATEVIEWV_CS_S2(SID,sname,borrow_time)ASSELECTs.SID,sname,borrow_time

FROMCS_studentsJOINborrowbsONs.SID=bs.SIDJOINBooksbonbs.ISBN=b.ISBNWHEREbname='零基础入门学习C语言';43定义带表达式的视图在定义基本表时，基本数据经过各种计算派生出的数据一般是不存储的。定义视图时可以根据需要设置一些派生属性列，在这些派生属性列中保存经过计算的值。派生属性由于在基本表中并不实际存在，因此，也称它们为虚拟列。包含虚拟列的视图也称为带表达式的视图。44定义带表达式的视图的例子例6-9定义一个查询图书出版的年数的视图，内容包括ISBN、书名和距今已出版年数。CREATEVIEWBT_P(ISBN,bname,PubYear)ASSELECTISBN,bname,YEAR(NOW())-YEAR(pub_date)

FROMbooks;NOW()：MySQL系统函数，获取系统当前日期和时间；YEAR(日期)：MySQL系统函数，获取日期的年份部分。45含分组统计信息的视图含分组统计信息的视图是指定义视图的查询语句中含有GROUPBY子句和聚合函数这种视图只能用于查询。例6-10定义查询每种图书分类的图书总数量的视图。CREATEVIEWB_SAS

SELECTcategory,SUM(quantity)SumQuantityFROMbooksGROUPBYcategory466.2.3通过视图查询数据通过视图查询数据同通过基本表查询数据一样。例6-11利用例6-5建立的视图，查询计算机学院男生的信息。SELECT*FROMCS_StudentWHEREgender='男'47视图查询过程首先检查要查询的视图是否存在。如果存在，则从数据字典中提取视图的定义。根据定义视图的查询语句，将对视图的查询转换成等价的对基本表的查询，然后再执行转换后的查询操作。例6-11的查询最终转换成的实际查询语句如下：

SELECTSID,Sname,gender,collegeFROMstudentsWHEREcollege='计算机学院'ANDgender='男'48转换不能直接进行的情况有些情况下，通过视图查询数据转换成对基本表查询的这种转换不能直接进行。例6-14利用例6-10建立的视图，查询相同图书分类的图书中，总数量大于等于10的图书分类和总数量。

SELECT*FROMB_SWHERESumQuantity>=1049例6-14转换后的查询语句错误的转换语句：

SELECTcategory,SUM(quantity)FROMbooksWHERESUM(quantity)>=10GROUPBYcategory;正确的转换语句：

SELECTcategory,SUM(quantity)FROMbooksGROUPBYcategoryHAVINGSUM(quantity)>=10;50通过建立视图进行分步骤查询例6-15查询图书分类为“TP”的图书名、出版社和总数量。步骤1：建立统计每种图书分类的总数量的视图。CREATEVIEWB_S_SASSELECTcategoryas图书分类,SUM(quantity)as总数量FROMbooks

GROUPBYcategory;步骤2：利用该视图和books表查询“TP”类图书的书名、出版社和总数量。SELECTbnameAS书名,pressAS出版社,总数量FROMB_S_SJOINbooks

ONB_S_S.图书分类

=books.CategoryWHERE图书分类='TP';516.2.4修改视图定义1.使用CREATEORREPLACEVIEW语句修改视图定义：CREATEORREPLACEVIEW语句的语法格式为：CREATEORREPLACEVIEW<视图名>[(列名[,...n])]ASSELECT语句52CREATEORREPLACEVIEW语句例子例6-16修改视图CS_Student，使视图的列名为：学号、姓名、性别和学院。CREATEORREPLACEVIEW

CS_Student(学号,姓名,性别,学院)ASSELECTSID,sname,gender,collegeFROMstudentsWHEREcollege='计算机学院';532.使用ALTERVIEW语句修改视图定义ALTERVIEW语句的语法格式为：

ALTERVIEW<视图名>[(<列名>[,...n])]