第7章 视图和索引.ppt

《第7章 视图和索引.ppt》由会员分享，可在线阅读，更多相关《第7章 视图和索引.ppt（38页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、1第第7章章 视图和索引视图和索引7.1 视图及其作用视图及其作用7.2 视图操作视图操作7.3 索引及其作用索引及其作用7.4 索引操作索引操作数据库原理与应用（基于MySQL）27.1 视图及其作用视图及其作用视图(View)是从一个或多个表(或视图)导出的，用来导出视图的表称为基表(BaseTable)或基本表，导出的视图称为虚表。在数据库中，视图通过SELECT查询语句定义，它只存储视图的定义，不存放视图对应的数据，这些数据仍然存放在原来的基表中。视图一经定义以后，就可以像表一样被查询、修改、删除和更新。视图有以下优点：方便用户的查询和处理，简化数据操作。简化用户的权限管理，增加安全性

2、。便于数据共享。屏蔽数据库的复杂性。可以重新组织数据。数据库原理与应用（基于MySQL）37.2 视图操作视图操作7.2.1 创建视图创建视图语法格式：语法格式：CREATEORREPLACEVIEWview_name(column_list)ASSELECT_statementWITHCASCADE|LOCALCHECKOPTION说明：说明：（1）ORREPLACE：为可选项，在创建视图时，如果存在同名视图，则要重新创建。（2）view_name：指定视图名称。（3）column_list：该子句为视图中每个列指定列名，为可选子句。可以自定义视图中包含的列，若使用源表或视图中相同的列名，可

3、不必给出列名。数据库原理与应用（基于MySQL）47.2 视图操作视图操作（4）SELECT_statement：定义视图的SELECT语句，用于创建视图，可查询多个表或视图。（5）WITHCHECKOPTION：指出在视图上进行的修改都要符合SELECT语句所指定的限制条件。【例7.1】在stusys数据库中创建V_StudentScore视图，包括学号、姓名、性别、专业、课程号、成绩，且专业为计算机。mysqlCREATEORREPLACEVIEWV_StudentScore-AS-SELECTa.sno,sname,ssex,speciality,cno,grade-FROMstuden

4、ta,scoreb-WHEREa.sno=b.snoANDspeciality=计算机-WITHCHECKOPTION;QueryOK,0rowsaffected(0.06sec)数据库原理与应用（基于MySQL）57.2 视图操作视图操作【例7.2】在stusys数据库中创建V_StudentCourseScore视图，包括学号、姓名、性别、课程名、成绩，按学号升序排列，且专业为计算机。mysqlCREATEORREPLACEVIEWV_StudentCourseScore-AS-SELECTa.sno,sname,ssex,speciality,cname,grade-FROMstuden

5、ta,courseb,scorec-WHEREa.sno=c.snoANDo=oANDspeciality=计算机-ORDERBYa.sno;QueryOK,0rowsaffected(0.06sec)数据库原理与应用（基于MySQL）67.2 视图操作视图操作7.2.2 查询视图查询视图【例7.3】分别查询V_StudentScore视图、V_StudentCourseScore视图。mysqlSELECT*-FROMV_StudentScore;查询结果：查询结果：+-+-+-+-+-+-+|sno|sname|ssex|speciality|cno|grade|+-+-+-+-+-+-+

6、|191001|刘清泉|男|计算机|1004|95|191001|刘清泉|男|计算机|1201|92|191001|刘清泉|男|计算机|8001|92|191002|张慧玲|女|计算机|1004|87|191002|张慧玲|女|计算机|1201|78|191002|张慧玲|女|计算机|8001|88|191003|冯涛|男|计算机|1004|93|191003|冯涛|男|计算机|1201|93|191003|冯涛|男|计算机|8001|84|+-+-+-+-+-+-+9rowsinset(0.09sec)数据库原理与应用（基于MySQL）77.2 视图操作视图操作mysqlSELECT*-FR

7、OMV_StudentCourseScore;+-+-+-+-+-+-+|sno|sname|ssex|speciality|cname|grade|+-+-+-+-+-+-+|191001|刘清泉|男|计算机|数据库系统|95|191001|刘清泉|男|计算机|英语|92|191001|刘清泉|男|计算机|高等数学|92|191002|张慧玲|女|计算机|数据库系统|87|191002|张慧|女|计算机|英语|78|191002|张慧玲|女|计算机|高等数学|88|191003|冯涛|男|计算机|数据库系统|93|191003|冯涛|男|计算机|英语|93|191003|冯涛|男|计算机|高

8、等数学|84|+-+-+-+-+-+-+9rowsinset(0.01sec)数据库原理与应用（基于MySQL）87.2 视图操作视图操作数据库原理与应用（基于MySQL）【例7.4】查询计算机专业学生的学号、姓名、性别、课程名。mysqlSELECTsno,sname,ssex,cname-FROMV_StudentCourseScore;查询结果：查询结果：+-+-+-+-+|sno|sname|ssex|cname|+-+-+-+-+|191001|刘清泉|男|数据库系统|191001|刘清泉|男|英语|191001|刘清泉|男|高等数学|191002|张慧玲|女|数据库系统|19100

9、2|张慧玲|女|英语|191002|张慧玲|女|高等数学|191003|冯涛|男|数据库系统|191003|冯涛|男|英语|191003|冯涛|男|高等数学|+-+-+-+-+9rowsinset(0.00sec)97.2 视图操作视图操作7.2.3 更新视图更新视图1.可更新视图可更新视图【例7.5】在stusys数据库中，以student为基表，创建专业为通信的可更新视图V_StudentSpecialityComm。mysqlCREATEORREPLACEVIEWV_StudentSpecialityComm-AS-SELECT*-FROMstudent-WHEREspeciality=

10、通信;QueryOK,0rowsaffected(0.06sec)数据库原理与应用（基于MySQL）107.2 视图操作视图操作使用SELECT语句查询V_StudentSpecialityComm视图mysqlSELECT*-FROMV_StudentSpecialityComm;查询结果查询结果:+-+-+-+-+-+-+|sno|sname|ssex|sbirthday|speciality|tc|+-+-+-+-+-+-+|196001|董明霞|女|1999-05-02|通信|50|196002|李茜|女|1998-07-25|通信|48|196004|周俊文|男|1998-03-10

11、|通信|52|+-+-+-+-+-+-+3rowsinset(0.03sec)数据库原理与应用（基于MySQL）117.2 视图操作视图操作2.插入数据插入数据【例7.6】向V_StudentSpecialityComm视图中插入一条记录：(196006,程超,男,1998-04-28,通信,50)。mysqlINSERTINTOV_StudentSpecialityComm-VALUES(196006,程超,男,1998-04-28,通信,50);QueryOK,1rowaffected(0.05sec)使用SELECT语句查询V_StudentSpecialityComm视图的基表stud

12、entmysqlSELECT*-FROMstudent;数据库原理与应用（基于MySQL）127.2 视图操作视图操作上述语句对基表student进行查询，该表已添加记录(196006,程超,男,1998-04-28,通信,50)。查询结果：查询结果：+-+-+-+-+-+-+|sno|sname|ssex|sbirthday|speciality|tc|+-+-+-+-+-+-+|191001|刘清泉|男|1998-06-21|计算机|52|191002|张慧玲|女|1999-11-07|计算机|50|191003|冯涛|男|1999-08-12|计算机|52|196001|董明霞|女|19

13、99-05-02|通信|50|196002|李茜|女|1998-07-25|通信|48|196004|周俊文|男|1998-03-10|通信|52|196006|程超|男|1998-04-28|通信|50|+-+-+-+-+-+-+7rowsinset(0.00sec)数据库原理与应用（基于MySQL）137.2 视图操作视图操作3.修改数据修改数据【例7.7】将V_StudentSpecialityComm视图中学号为196006的学生的总学分增加2分。mysqlUPDATEV_StudentSpecialityCommSETtc=tc+2-WHEREsno=196006;QueryOK,1

14、rowaffected(0.15sec)Rowsmatched:1Changed:1Warnings:0使用SELECT语句查询V_StudentSpecialityComm视图的基表studentmysqlSELECT*-FROMstudent;数据库原理与应用（基于MySQL）147.2 视图操作视图操作上述语句对基表student进行查询，该表已将学号为196006的学生的总学分增加了2分。查询结果：查询结果：+-+-+-+-+-+-+|sno|sname|ssex|sbirthday|speciality|tc|+-+-+-+-+-+-+|191001|刘清泉|男|1998-06-21

15、|计算机|52|191002|张慧玲|女|1999-11-07|计算机|50|191003|冯涛|男|1999-08-12|计算机|52|196001|董明霞|女|1999-05-02|通信|50|196002|李茜|女|1998-07-25|通信|48|196004|周俊文|男|1998-03-10|通信|52|196006|程超|男|1998-04-28|通信|52|+-+-+-+-+-+-+7rowsinset(0.00sec)数据库原理与应用（基于MySQL）157.2 视图操作视图操作4.删除数据删除数据【例7.8】删除V_StudentSpecialityComm视图中学号为196

16、006的记录。mysqlDELETEFROMV_StudentSpecialityComm-WHEREsno=196006;QueryOK,1rowaffected(0.12sec)使用SELECT语句查询V_StudentSpecialityComm视图的基表studentmysqlSELECT*-FROMstudent;数据库原理与应用（基于MySQL）167.2 视图操作视图操作上述语句对基表student进行查询，该表已删除记录(196006,程超,男,1998-04-28,通信,50)。查询结果：查询结果：+-+-+-+-+-+-+|sno|sname|ssex|sbirthday|

17、speciality|tc|+-+-+-+-+-+-+|191001|刘清泉|男|1998-06-21|计算机|52|191002|张慧玲|女|1999-11-07|计算机|50|191003|冯涛|男|1999-08-12|计算机|52|196001|董明霞|女|1999-05-02|通信|50|196002|李茜|女|1998-07-25|通信|48|196004|周俊文|男|1998-03-10|通信|52|+-+-+-+-+-+-+6rowsinset(0.00sec)数据库原理与应用（基于MySQL）177.2 视图操作视图操作7.2.4 修改视图修改视图修改视图定义使用ALTERV

18、IEW语句。语法格式：语法格式：ALTERVIEWview_name(column_list)ASSELECT_statementWITHCASCADE|LOCALCHECKOPTIONALTERVIEW语句的语法与CREATEVIEW类似，此处不再重复。数据库原理与应用（基于MySQL）187.2 视图操作视图操作【例7.9】将例7.1定义的视图V_StudentScore视图进行修改，取消专业为计算机的要求。mysqlALTERVIEWV_StudentScore-AS-SELECTa.sno,sname,ssex,speciality,cno,grade-FROMstudenta,sco

19、reb-WHEREa.sno=b.sno-WITHCHECKOPTION;QueryOK,0rowsaffected(0.39sec)使用SELECT语句对修改后的V_StudentScore视图进行查询，可看出修改后的V_StudentScore视图已取消专业为计算机的要求。mysqlSELECT*-FROMV_StudentScore;数据库原理与应用（基于MySQL）197.2 视图操作视图操作+-+-+-+-+-+-+|sno|sname|ssex|speciality|cno|grade|+-+-+-+-+-+-+|191001|刘清泉|男|计算机|1004|95|191001|刘清

20、泉|男|计算机|1201|92|191001|刘清泉|男|计算机|8001|92|191002|张慧玲|女|计算机|1004|87|191002|张慧玲|女|计算机|1201|78|191002|张慧玲|女|计算机|8001|88|191003|冯涛|男|计算机|1004|93|191003|冯涛|男|计算机|1201|93|191003|冯涛|男|计算机|8001|84|196001|董明霞|女|通信|1201|84|196001|董明霞|女|通信|4002|90|196001|董明霞|女|通信|8001|87|196002|李茜|女|通信|1201|76|196002|李茜|女|通信|40

21、02|79|196002|李茜|女|通信|8001|NULL|196004|周俊文|男|通信|1201|92|196004|周俊文|男|通信|4002|88|196004|周俊文|男|通信|8001|94|+-+-+-+-+-+-+18rowsinset(0.15sec)数据库原理与应用（基于MySQL）207.2 视图操作视图操作【例7.10】修改例7.2创建的视图V_StudentCourseScore，学号以降序排列。mysqlALTERVIEWV_StudentCourseScore-AS-SELECTa.sno,sname,ssex,speciality,cname,grade-FR

22、OMstudenta,courseb,scorec-WHEREa.sno=c.snoANDo=oANDspeciality=计算机-ORDERBYa.snoDESC;QueryOK,0rowsaffected(0.05sec)使用SELECT语句对修改后的V_StudentCourseScore视图进行查询，可看出修改后的V_StudentCourseScore视图学号以降序排列。mysqlSELECT*-FROMV_StudentCourseScore;数据库原理与应用（基于MySQL）217.2 视图操作视图操作查询结果：查询结果：+-+-+-+-+-+-+|sno|sname|ssex|

23、speciality|cname|grade|+-+-+-+-+-+-+|191003|冯涛|男|计算机|数据库系统|93|191003|冯涛|男|计算机|英语|93|191003|冯涛|男|计算机|高等数学|84|191002|张慧玲|女|计算机|数据库系统|87|191002|张慧玲|女|计算机|英语|78|191002|张慧玲|女|计算机|高等数学|88|191001|刘清泉|男|计算机|数据库系统|95|191001|刘清泉|男|计算机|英语|92|191001|刘清泉|男|计算机|高等数学|92|+-+-+-+-+-+-+9rowsinset(0.13sec)数据库原理与应用（基于M

24、ySQL）227.2 视图操作视图操作7.2.5 删除视图删除视图删除视图使用DROPVIEW语句。语法格式：语法格式：DROPVIEWIFEXISTSview_name,view_name.其中，view_name是视图名，声明了IFEXISTS，可防止因视图不存在而出现错误信息。使用DROPVIEW一次可删除多个视图。【例7.11】在stusys数据库中，将视图V_StudentCourseScore删除。mysqlDROPVIEWV_StudentCourseScore;QueryOK,0rowsaffected(0.08sec)数据库原理与应用（基于MySQL）237.3 索引及其作用

25、索引及其作用1索引的概念索引的概念对数据库中的表进行查询操作时，有两种搜索扫描方式：一种是全表扫描，另一种是使用表上建立的索引扫描。索引是按照数据表中一列或多个列进行索引排序，并为其建立指向数据表记录所在位置的指针，如图7.1所示。索引表中的列称为索引字段或索引项，该列的各个值称为索引值。索引访问首先搜索索引值，再通过指针直接找到数据表中对应的记录。数据库原理与应用（基于MySQL）图7.1 索引示意247.3 索引及其作用索引及其作用建立索引的作用如下：提高查询速度。保证列值的唯一性。查询优化依靠索引起作用。提高ORDERBY、GROUPBY执行速度。2索引的分类索引的分类（1）普通索引（I

26、NDEX）这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。（2）唯一性索引（UNIQUE）这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。数据库原理与应用（基于MySQL）257.3 索引及其作用索引及其作用（3）主键（PRIMARYKEY）主键是一种唯一性索引，它必须指定为“PRIMARYKEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。（4）聚簇索引聚簇索引的索引顺序就是数据存储的物理顺序，这样能保证索引值相近的元组所存储的物理位

27、置也相近。一个表只能有一个聚簇索引。（5）全文索引（FULLTEXT）MySQL支持全文检索和全文索引。在MySQL中，全文索引的索引类型为FULLTEXT。索引可以建立在一列上，称为单列索引，一个表可以建立多个单列索引。索引也可以建立在多个列上，称为组合索引、复合索引或多列索引。3索引的使用索引的使用 使用索引可以提高系统的性能，加快数据检索的速度，但是使用索引是要付出一定的代价。数据库原理与应用（基于MySQL）267.4 索引索引操作操作7.4.1 创建索引创建索引1.使用使用CREATE INDEX语句创建索引语句创建索引语法格式：语法格式：CREATEUNIQUEINDEXindex

28、_nameONtbl_name(col_name(length)ASCDESC,.)说明：说明：index_name：指定所建立的索引名称。一个表中可建多个索引，而每个索引名称必须是唯一的。tbl_name：指定需要建立索引的表名。UNIQUE：可选项，指定所创建的索引是唯一性索引。col_name：指定要创建索引的列名。length：可选项，用于指定使用列的前length个字符创建索引。ASCDESC：可选项，指定索引是按升序(ASC)还是降序(DESC)排列，默认为ASC。数据库原理与应用（基于MySQL）277.4 索引索引操作操作【例7.12】在stusys数据库中student表的s

29、name列上，创建一个普通索引I_studentSname。mysqlCREATEINDEXI_studentSnameONstudent(sname);QueryOK,0rowsaffected(0.73sec)Records:0Duplicates:0Warnings:0该语句执行后，在student表的sname列上建立了一个普通索引I_studentSname，普通索引是没有唯一性等约束的索引。该语句没有指明排序方式，因此采用默认方式，即为升序索引。数据库原理与应用（基于MySQL）287.4 索引索引操作操作【例7.13】在stusys数据库中course表的cno列上，创建一个索引

30、I_courseCno，要求按课程号cno字段值前2个字符降序排列。mysqlCREATEINDEXI_courseCnoONcourse(cno(2)DESC);QueryOK,0rowsaffected(0.15sec)Records:0Duplicates:0Warnings:0该语句执行后，在course表的cno列上建立了一个普通索引I_courseCno，按cno字段值前2个字符降序排列。对字符类型排序，如果是英文，按照英文字母顺序排列；如果是中文，按照汉语拼音对应的英文字母顺序排列。数据库原理与应用（基于MySQL）297.4 索引索引操作操作【例7.14】在stusys数据库中

31、student表的tc列(降序)和sname列(升序)，创建一个组合索引I_studentTcSname。mysqlCREATEINDEXI_studentTcSnameONstudent(tcDESC,sname);QueryOK,0rowsaffected(0.13sec)Records:0Duplicates:0Warnings:0该语句执行后，在student表的tc列和sname列上建立了一个组合索引I_courseCno。排序时，先按tc列降序排序；若tc列值相同，再按sname列升序排序。数据库原理与应用（基于MySQL）307.4 索引索引操作操作2.使用使用ALTER TAB

32、LE语句创建索引语句创建索引使用ALTERTABLE语句也可在一个已有的表上创建索引。语法格式语法格式：ALTERTABLEtbl_nameADDUNIQUE|FULLTEXTINDEX|KEYindex_name(col_name(length)ASCDESC,.)上述语句中的tbl_name、UNIQUE、index_name、col_name、length、ASCDESC等选项与CREATEINDEX语句中相关选项类似，此处不再重复解释。【例7.15】在stusys数据库teacher表的tname列，创建一个唯一性索引I_teacherTname，并按降序排列。mysqlALTERTA

33、BLEteacher-ADDUNIQUEINDEXI_teacherTname(tnameDESC);QueryOK,0rowsaffected(0.17sec)Records:0Duplicates:0Warnings:0数据库原理与应用（基于MySQL）317.4 索引索引操作操作3.使用使用CREATE TABLE语句创建索引语句创建索引使用CREATETABLE语句可在创建表的同时创建索引。语法格式：语法格式：CREATETABLEtbl_namecol_namedata_typeCONSTRAINTindex_nameUNIQUE|FULLTEXTINDEX|KEYindex_nam

34、e(col_name(length)ASCDESC,.)上述语句中的tbl_name、index_name、UNIQUE、col_name、length、ASCDESC等选项与CREATEINDEX语句中相关选项类似，此处不再重复解释。数据库原理与应用（基于MySQL）327.4 索引索引操作操作【例7.16】在stusys数据库中，创建新表score1表，主键为sno和cno，同时在grade列上创建普通索引。mysqlCREATETABLEscore1-(-snochar(6)NOTNULL,-cnochar(4)NOTNULL,-gradetinyintNULL,-PRIMARYKEY(

35、sno,cno),-INDEX(grade)-);数据库原理与应用（基于MySQL）337.4 索引索引操作操作7.4.2 查看表上建立的索引查看表上建立的索引查看表上建立的索引使用SHOWINDEX语句。语法格式：语法格式：SHOWINDEX|INDEXES|KEYSFROM|INtbl_nameFROM|INdb_name该语句以二维表的形式显示建立在表上所有索引信息，由于显示的项目较多，不易查看，可使用G参数。数据库原理与应用（基于MySQL）347.4 索引索引操作操作【例7.17】查看例7.5所创建的score1表的索引。mysqlSHOWINDEXFROMscore1G;*1.ro

36、w*Table:score1Non_unique:0Key_name:PRIMARYSeq_in_index:1Column_name:snoCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL数据库原理与应用（基于MySQL）357.4 索引索引操作操作*2.row*Table:score1Non_unique:0Key_name:PRIMARYSeq_in_index:2Column_name:cnoColla

37、tion:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL*3.row*数据库原理与应用（基于MySQL）367.4 索引索引操作操作Table:score1Non_unique:1Key_name:gradeSeq_in_index:1Column_name:gradeCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:I

38、ndex_comment:Visible:YESExpression:NULL3rowsinset(0.08sec)可以看出，在表score1上建立了3个索引：2个主键索引，索引名称是PRIMARY，索引建立在sno和cno列上；1个普通索引，索引名称是grade，索引建立在列grade上。数据库原理与应用（基于MySQL）377.4 索引索引操作操作7.4.3 删除索引删除索引 1.使用使用DROP INDEX语句删除索引语句删除索引语法格式：语法格式：DROPINDEXindex_nameONtable_name其中，index_name是要删除的索引名，table_name是索引所在的表

39、。【例7.18】删除已建索引I_studentTcSname。mysqlDROPINDEXI_studentTcSnameONstudent;QueryOK,0rowsaffected(0.14sec)Records:0Duplicates:0Warnings:0该语句执行后，表student上的索引I_studentTcSname被删除，对表student无影响，也不影响该表上其它索引。数据库原理与应用（基于MySQL）387.4 索引索引操作操作2.使用使用ALTER TABLE语句删除索引语句删除索引ALTERTABLE语句不仅能创建索引，还能删除索引。语法格式：语法格式：ALTERTABLEtbl_nameDROPINDEXindex_name其中，table_name是索引所在的表，index_name是要删除的索引名。【例7.19】删除已建索引I_teacherTname。mysqlALTERTABLEteacher-DROPINDEXI_teacherTname;QueryOK,0rowsaffected(0.10sec)Records:0Duplicates:0Warnings:0Records:0Duplicates:0Warnings:0数据库原理与应用（基于MySQL）