数据库技术基础chp5

数据库技术基础第5章数据库管理第5章数据库管理5.1学习目标5.2视图5.3索引5.4安全性5.5完整性5.6系统表5.7小结5.1学习目标学完本章后，读者应具备以下能力：理解视图的概念和作用。能建立视图，会使用视图实现数据的逻辑独立性。理解索引的概念和作用。掌握建立索引的一般规则，能利用索引改善查询性能。理解数据库的操作权限，掌握授权的方法。理解角色的概念，能利用角色简化权限管理。理解参照完整性的概念以及对SQL语句的影响。基本掌握用户定义完整性约束的方法。第5章数据库管理5.1学习目标5.2视图5.3索引5.4安全性5.5完整性5.6系统表5.7小结5.2视图视图是从一个或多个表中导出的表，用户可以像对表一样对它进行查询，在SELECT语句中可以出现表的地方都可以出现视图。视图是一个虚表，在数据库中只存储视图的定义（一个SELECT语句）而不存放视图的数据，这些数据仍存放在导出视图的基本表中，直到用户使用视图时才去执行视图的定义，求出数据。5.2视图（续）5.2.1建立视图5.2.2删除视图5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2.1建立视图SQL语言用CREATEVIEW命令建立视图，其一般格式为：CREATEVIEW视图名[(列名[，列名]…)]AS子查询[WITHCHECKOPTION]；组成视图的列名可以全部省略或者全部指定。5.2.1建立视图（续）下列三种情况下必须明确指定组成视图的所有列名：某个目标列不是单纯的列名，而是聚集函数或列表达式。多表连接时选出了几个同名列作为视图的列。需要在视图中为某个列启用更合适的名字。子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句和DISTINCT短语。WITHCHECKOPTION表示，对视图进行UPDATE和INSERT操作时，要保证更新后的元组和新插入的元组满足视图定义中子查询的WHERE子句中的条件表达式。5.2.1建立视图（续）[例5.1]建立计算机学院学生的视图。CREATEVIEWStudent_CSASSELECT*FROMStudentWHERESdept='计算机学院'例5.1在表Student上建立了视图Student_CS，但是没有明确指出视图Student_CS的列名，则构成视图的列与SELECT子句相同。即Student_CS有Sno、Sname、Sgender、Sage和Sdept共5个列，这5个列分别对应Student的Sno、Sname、Sgender、Sage和Sdept列。CREATEVIEW语句的执行结果是在DBMS的数据字典中保存了视图名和SELECT语句。5.2.1建立视图(续)[例5.2]建立英语课（1156）成绩单的视图。CREATEVIEWEnglish_Grade(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoANDSC.Cno='1156'例2在表Student和SC上建立了视图English_Grade。它有3个列：Sno、Sname和Grade，分别对应表Student的Sno列、Sname列和表SC的Grade列。因为SELECT语句中包含了Student表与SC表的同名列Sno，所以必须在视图名后面明确说明视图的各个列名。5.2.1建立视图（续）[例5.3]定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno,Sname,Sbirthday)ASSELECTSno,Sname,datepart(getdate(),year)－SageFROMStudent由于Student表的Sage列存放了学生的年龄，没有存放其出生年份，例5.3定义的视图由学号、学生姓名和学生出生年份三个列组成。getdate()返回系统日期，datepart函数求出日期中的年份。由于SELECT子句中出现了表达式，则必须指明视图的列名。视图不仅可以建立在一个或多个表上，也可以建立在一个或多个已定义好的视图上，或建立在表与视图上。5.2.1建立视图（续）[例5.4]建立英语课的成绩在80分以上的学生的视图。CREATEVIEWEnglish_Grade_80ASSELECTSno,Sname,GradeFROMEnglish_GradeWHEREGrade=80已经定义过的视图可以和表一样使用。例5.4中的FROM子句中出现了在例5.2定义过的视图English_Grade，因此，视图English_Grade_80是建立在视图English_Grade之上。5.2视图5.2.1建立视图5.2.2删除视图5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2.2删除视图当不再需要一个视图时，可以删除它，语句格式为：DROPVIEW视图名[例5.5]删除视图Student_CS。DROPVIEWStudent_CSDROPVIEW语句执行后，DBMS从数据字典中删除视图Student_CS和定义它的SELECT语句。[例5.6]删除视图English_Grade。DROPVIEWEnglish_Grade5.2视图5.2.1建立视图5.2.2删除视图5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2.3查询视图定义视图以后，就可以像对表一样对视图进行查询。[例5.7]查找计算机学院年龄小于19岁的学生的姓名。视图Student_CS包含有计算机学院全体学生的信息，可以直接对视图进行查询。SELECTSnameFROMStudent_CSWHERESage195.2.3查询视图（续）对视图进行查询时，DBMS要进行视图消解工作，把对视图的查询转换为对基本表（定义视图时涉及的表）的查询，即把对视图查询的一个SQL语句，转换为对基本表查询的SQL语句。视图消解的基本过程分为４个步骤：①３从数据字典中取出定义视图的子查询（SELECT语句）。②用子查询的FROM子句替换要执行的SELECT的FROM子句。③根据定义视图时，视图的列和基本表的列的对应关系，映射要执行的SELECT子句的列到基本表的列。④将定义视图的子查询的WHERE子句的条件表达式合并到要执行的SELECT语句的WHERE子句中，逻辑关系是与关系。5.2.3查询视图（续）SELECTSno,SageFROMStudent_CSWHERESage19SELECT*FROMStudentWHERESdept='计算机学院'SELECTStudent.Sno,Student.SageFROMStudentWHERESage19ANDSdept='计算机学院'①②③④图5.1视图消解基本过程5.2.3查询视图（续）[例5.8]假设定义了一个求每个学生学号和平均成绩的视图：CREATEVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno现在要查询平均成绩在80分以上的学生学号和平均成绩，可以写出以下的语句：SELECT*FROMS_GWHEREGavg=80这时，DBMS无法像前面例5.7那样得到一个等价的SELECT语句。DBMS采用第二种视图消解方法。先执行定义视图S_G的SELECT语句，得到了一个结果，把它作为一个临时表tmp_S_G，然后将上面的查询语句改写为：SELECT*FROMtmp_S_GWHEREGavg=80同样可以得到正确结果。因此，我们可以把视图当作表一样进行查询，而不必关心DBMS如何进行处理。5.2视图5.2.1建立视图5.2.2删除视图5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2.4更新视图更新视图是指向视图中插入（INSERT）、删除（DELETE）和更新（UPDATE）数据。对视图的更新操作也要通过视图消解转换为对表的更新操作。不是所有的视图都是可更新的，因为有些视图的更新不能唯一有意义地转换成对相应表的更新。5.2.4更新视图（续）例如,例5.8定义的视图S_G是由“学号”和“平均成绩”两个属性列组成的，其中“平均成绩”一项是由Student表中对元组分组后计算平均值得来的。如果我们想把视图S_G中学号为2007012的学生的平均成绩改成90分，SQL语句如下：UPDATES_GSETGavg=90WHERESno='2007012'但这个对视图的更新是无法转换成对表SC的更新的，因为系统无法修改各科成绩，以使平均成绩成为90。所以S_G视图是不可更新的。5.2.4更新视图（续）行列子集视图若一个视图是从单个表导出的，并且只是去掉了表的某些行和某些列，但保留了主码，则这类视图称为行列子集视图。应该指出的是，不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已证明其是不可更新的视图。后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。对于行列子集视图的更新，DBMS也要进行视图消解，把对视图的更新转换为对基本表的更新。5.2.4更新视图（续）[例５.9]将计算机学院的学生马翔的姓名改为马飞翔。UPDATEStudent_CSSETSname='马飞翔'WHERESname='马翔'DBMS进行视图消解后，得到下面的语句：UPDATEStudentSETSname='马飞翔'WHERESname='马翔'ANDSdept='计算机学院'5.2.4更新视图（续）[例5.10]计算机学院增加一个新生，学号为2007015，姓名为赵新，年龄为20岁。INSERTINTOStudent_CS(Sno,Sname,Sage)VALUES('2007015','赵新',20)转换后的更新语句为：INSERTINTOStudent(Sno,Sname,Sage)VALUES('2007015','赵新',20)5.2.4更新视图（续）[例5.11]删除计算机学院学生赵新，学号是2007015。DELETEFROMStudent_CSWHERESno='2007015'转换为对表的删除操作：DELETEFROMStudentWHERESno='2007015'ANDSdept='计算机学院'5.2.4更新视图（续）要防止用户通过视图对数据库进行增删改时有意或无意地对不属于视图范围内（不满足子查询的过滤条件）的基本表数据进行操作，则在视图定义时要加上WITHCHECKOPTION子句。WITHCHECKOPTION短语相当于在视图上施加了一个元组级约束条件，更新前后的元组必须满足定义视图的子查询的过滤条件。若操纵的元组不满足条件，则拒绝执行该操作。5.2.4更新视图（续）[例5.12]建立计算机学院学生的视图，要求进行更新操作前后的元组要保证满足视图的过滤条件（即Sdept列上的值是计算机学院）。CREATEVIEWStudent_CSASSELECT*FROMStudentWHERESdept='计算机学院'WITHCHECKOPTION由于在定义Student_CS视图时加上了WITHCHECKOPTION子句，所以以后对该视图进行插入、修改时，DBMS会自动检查插入的元组和修改后的元组在Sdept列上的值是否等于计算机学院。例如，DBMS会拒绝执行下面的对视图进行修改的SQL语句。INSERTINTOStudent_CS(Sno,Sname,Sage)VALUES('2007015','赵新',20)--新插入的元组在Sdept的值不等于计算机学院UPDATEStudent_CSSETSdept='环境学院'--试图将Sdept的值由计算机学院更改为环境学院WHERESno='2007012'5.2视图5.2.1建立视图5.2.2删除视图5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2.5视图的作用视图能够简化用户的操作视图可以减少冗余数据视图对重构数据库