学习与管饭的我的笔记 (4)

系统中视图的应用•任务1：选择employee表中的雇员ID、姓名、性别、和出生日期四个字段，以及product表中的产品ID、产品名和类别ID三个字段创建名为v_employee的视图（要求用企业管理器）•任务2：利用employee表中的全部字段和记录创建名为view1_employee的视图（要求用SQL语句实现）•任务3：选择employee表中的雇员ID、姓名、性别、出生年月和雇佣日期字段，以及所有女职工的记录创建名view2_employee的视图（要求用SQL语句实现）知识点：•视图是一种数据库的对象，是从一个或多个数据表（基本表）或视图导出的虚表，是关系数据库系统提供给用户以多种角度观察数据中数据的重要机制。•虚表是指视图只存储了它的定义(select语句)，而没有储存视图对应的数据，这些数据仍存放在原来的数据表中，对视图的数据操作与对表的操作一样，可以对其进行改变，若基表的数据发生变化，这种变化也会自动反映到视图中。•视图可以是一个表的部分，也可以是多个表的联合。定义视图查询视图更新视图视图的作用snosnamesagesdeptstudentsnoscogradesco定义视图•建立视图•删除视图一、建立视图•语句格式CREATEVIEW视图名[(列名[，列名]…)]AS子查询[WITHCHECKOPTION]；•组成视图的属性列名：全部省略或全部指定•子查询不允许含有ORDERBY子句和DISTINCT短语建立视图（续）•RDBMS执行CREATEVIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。•在对视图查询时，按视图的定义从基本表中将数据查出。建立视图（续）[例1]建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；建立视图（续）[例2]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；withcheckoption可以这么解释：通过视图进行的修改，必须也能通过该视图看到修改后的结果。比如你insert，那么加的这条记录在刷新视图后必须可以看到；如果修改，修改完的结果也必须能通过该视图看到；如果删除，当然只能删除视图里有显示的记录。我们来看下面的例子：createorreplaceviewtestviewasselectempno,enamefromempwhereenamelike‘M%’withcheckoption;这里我们创建了一个视图，并使用了withcheckoption来限制了视图。然后我们来看一下视图包含的结果：select*fromtestview得到：EMPNOENAME———-———–7654MARTIN7934MILLER这两条记录然后我们在试图将其中一条更新：updatetestviewsetename=‘Mike’whereempno=7654;OK,这条更新语句可以执行，并没有什么问题，但是当我们执行另一条更新时：updatetestviewsetename=‘Robin’whereempno=‘7654′;就会出现ORA-01402:视图WITHCHECKOPTIDN违反where子句的错误。建立视图（续）对IS_Student视图的更新操作：•修改操作：自动加上Sdept='IS'的条件•删除操作：自动加上Sdept='IS'的条件•插入操作：自动检查Sdept属性值是否为'IS'–如果不是，则拒绝该插入操作–如果没有提供Sdept属性值，则自动定义Sdept为'IS'建立视图（续）•基于多个基表的视图[例3]建立信息系选修了1号课程的学生视图。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；建立视图（续）•基于视图的视图[例4]建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade=90；建立视图（续）•分组视图[例6]将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；建立视图（续）•不指定属性列[例7]将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student(F_Sno，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex=‘女’；缺点：修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。二、删除视图•语句的格式：DROPVIEW视图名；–该语句从数据字典中删除指定的视图定义–如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除–删除基表时，由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除删除视图(续）［例8］删除视图BT_S：DROPVIEWBT_S；删除视图IS_S1：DROPVIEWIS_S1；级联删除：DROPVIEWIS_S1CASCADE;查询视图•用户角度：查询视图与查询基本表相同•RDBMS实现视图查询的方法–视图消解法（ViewResolution）•进行有效性检查•转换成等价的对基本表的查询•执行修正后的查询查询视图（续）[例9]在信息系学生的视图中找出年龄小于20岁的学生。SELECTSno，SageFROMIS_StudentWHERESage20；IS_Student视图的定义(参见视图定义例1)查询视图（续）视图消解转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage20；查询视图（续）[例10]查询选修了1号课程的信息系学生SELECTIS_Student.Sno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'；查询视图（续）•视图消解法的局限–有些情况下，视图消解法不能生成正确查询。查询视图（续）[例11]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩SELECT*FROMS_GWHEREGavg=90；S_G视图的子查询定义：CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；查询转换错误：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)=90GROUPBYSno；正确：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)=90；更新视图（续）[例12]将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。UPDATEIS_StudentSETSname='刘辰'WHERESno='200215122'；转换后的语句：UPDATEStudentSETSname='刘辰'WHERESno='200215122'ANDSdept='IS'；更新视图（续）[例13]向信息系学生视图IS_S中插入一个新的学生记录：200215129，赵新，20岁INSERTINTOIS_StudentVALUES(‘95029’，‘赵新’，20)；转换为对基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(‘200215129'，'赵新'，20，'IS')；更新视图（续）[例14]删除信息系学生视图IS_Student中学号为200215129的记录DELETEFROMIS_StudentWHERESno='200215129'；转换为对基本表的更新：DELETEFROMStudentWHERESno='200215129'ANDSdept='IS'；更新视图（续）•更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新例：视图S_G为不可更新视图。UPDATES_GSETGavg=90WHERESno=‘200215121’；这个对视图的更新无法转换成对基本表SC的更新更新视图（续）•允许对行列子集视图进行更新•对其他类型视图的更新不同系统有不同限制视图的作用•1.视图能够简化用户的操作•2.视图使用户能以多种角度看待同一数据•3.视图对重构数据库提供了一定程度的逻辑独立性•4.视图能够对机密数据提供安全保护•5.适当的利用视图可以更清晰的表达查询•插入数据记录例：利用已创建的视图view1_employee，向employee表中添加一条新的数据记录，该记录对应的值为:(1000,’李立’,’男’,’1980-9-9’,’2003-3-9’,’乒乓球’,2000)。•更改数据记录•例：利用已创建的视图view1_employee,修改employee表中姓名为“章宏明”的为“张宏明”。•修改数据记录•例：利用视图view1_employee删除表employee中的姓名为李立三的记录。系统中索引的应用•任务1：为employee表创建一个姓名的非聚集索引，取名为i_xm_employee（要求使用企业管理器）•任务2：为employee表创建一个姓名和出生日期的复合索引，取名为i_xmrq_employee(要求用SQL语句创建)•任务3：为employee表创建一个雇员ID的唯一聚集索引，取名为i_gy_employee(要求用SQL语句创建)