关系数据库标准语言SQL补充.ppt

《关系数据库标准语言SQL补充.ppt》由会员分享，可在线阅读，更多相关《关系数据库标准语言SQL补充.ppt（41页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、视图：视图：从一个或几个基本表（或视图）导出的表。建立视图的优点建立视图的优点1、简化用户的操作2、灵活3、提供一定程度的逻辑独立性4、提供安全保护13.5.13.5.1定义视图定义视图1、建立视图 CREATE VIEW (,)AS WITH CHECK OPTION;2如果CREATEVIEW语句仅指定了视图名，省略了组成视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。但在下列三种情况三种情况下必须明确指定组成视图的所有列名：其中某个目标列不是单纯的属性名，而是集函数或列表达式 多表连接时选出了几个同名列作为视图的字段 需要在视图中为某个列启用新的更合适的名

2、需要说明的是，组成视图的属性列名必须依照上面的原则，或者全部省略或者全部指定，没有第三种选择。3例1建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=IS;实际上，DBMS执行CREATEVIEW语句的结果只是把对视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。4例2建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生CREATEVIEWIS_StudentASSELECTSno,Sname,SageFRO

3、MStudentWHERESdept=ISWITHCHECKOPTION;由于在定义IS_Student视图时加上了WITHCHECKOPTION子句，以后对该视图进行插入、修改和删除操作时，DBMS会自动加上Sdept=IS的条件。5视图不仅可以建立在单个基本表上，也可以建立在多个基本表上。例3建立信息系选修了1号课程的学生的视图CREATEVIEWIS_S1(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudent,SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.Cno=1;6 视图不仅可以建立

4、在一个或多个基本表上也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。例4建立信息系选修了1号课程且成绩在90分以上的学生的视图CREATEVIEWIS_S2ASSELECTSno,Sname,GradeFROMIS_S1WHEREGrade=90;7定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列虚拟列。带虚拟列的视图我们称为带表达式的视图表达式的视图。8例5定义

5、一个反映学生出生年份的视图CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,1996-SageFROMStudent;由于BT_S视图中的出生年份值是通过一个表达式计算得到的，不是单纯的属性名，所以定义视图时必须明确定义该视图的各个属性列名。BT_S视图是一个带表达式的视图。9 还可以用带有集函数和GROUP BY子句的查询来定义视图。这种视图称为分组视图分组视图。例6将学生的学号及他的平均成绩定义为一个视图CREATVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;10例7将Stu

6、dent表中所有女生记录定义为一个视图CREATEVIEWF_Student(stdnum,name,sex,age,dept)ASSELECT*FROMStudentWHERESsex=女;这里视图F_Student是由子查询“SELECT*建立的。由于该视图一旦建立后，Student表就构成了视图定义的一部分，如果以后修改了基本表Student的结构，则Student表与F_Student视图的映像关系受到破坏，因而该视图就不能正确工作了。为避免出现这类问题，可以采用下列两种方法：11建立视图时明确指明属性列名，而不是简单地用SELECT*。即：CREATEVIEWF_Student(st

7、dnum,name,sex,age,dept)ASSELECTSno,Sname,Ssex,Sage,SdeptFROMStudentWHERESsex=女;在修改基本表之后删除原来的视图，然后重建视图。这是最保险的方法。122、删除视图语句的格式为：DROP VIEW;一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用DROP VIEW语句将他们一一删除。13例8删除视图IS_S1DROPVIEWIS_S1;执行此语句后，IS_S1视图的定义将从数据字典中删除。由IS_S1视图导出的IS_S2视图的定义虽仍在数据字典中，但该视图已无法使用了，因此应该同时删除。143.5.2查询视

8、图DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解（ViewResolution）。15例1在信息系学生的视图中找出年龄小于20岁的学生SELECTSno,SageFROMIS_StudentWHERESage20;DBMS执行此查询时，将其与IS_Student视图定义中的子查询SELECTSno,Sname,SageFROMStudentWHERES

9、dept=IS;结合起来，转换成对基本表Student的查询，修正后的查询语句为16SELECTSno,SageFROMStudentWHERESdept=ISANDSage(SELECTAVG(Grade)FROMSC);导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。7.一个不允许更新的视图上定义的视图也不允许更新。27视图的优点视图能够简化用户的操作视图使用户能以多种角度看待同一数据视图对重构数据库提供了一定程度的逻辑独立性视图能够对机密数据提供安全保护28数据控制对象对象类型 操作权限属性列 TABLESELECT,INSERT,

10、UPDATE,DELETE,ALLPRIVILEGES视 图TABLESELECT,INSERT,UPDATE,DELETE,ALLPRIVILEGES基本表 TABLESELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,ALLPRIVILEGES数据库DATABASECREATETAB29一、授权GRANT,ON TO,WITH GRANT OPTION;二、收回权限REVOKE,ON FROM,30例1把查询Student表权限授给用户U1GRANTSELECTONTABLEStudentTOU1;例2把对Student表和Course表的全部权限授予用户U2和

11、U3GRANTALLPRIVILIGESONTABLEStudent,CourseTOU2,U3;例3把对表SC的查询权限授予所有用户GRANTSELECTONTABLESCTOPUBLIC;31例4把查询Student表和修改学生学号的权限授给用户U4这里实际上要授予U4用户的是对基本表Student的SELECT权限和对属性列Sno的UPDATE权限。授予关于属性列的权限时必须明确指出相应属性列名。完成本授权操作的SQL语句为：GRANTUPDATE(Sno),SELECTONTABLEStudentTOU4;32例5把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户

12、GRANTINSERTONTABLESCTOU5WITHGRANTOPTION;执行此SQL语句后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限，即由U5用户发上述GRANT命令给其他用户。例如U5可以将此权限授予U6：GRANTINSERTONTABLESCTOU6WITHGRANTOPTION;同样，U6还可以将此权限授予U7：GRANTINSERTONTABLESCTOU7;因为U6未给U7传播的权限，因此U7不能再传播此权限33例6DBA把在数据库S_C中建立表的权限授予用户U8GRANTCREATETABONDATABASES_CTOU8;由上面的例子可以看到，GRAN

13、T语句可以一次向一个用户授权，如例1所示，这是最简单的一种授权操作；也可以一次向多个用户授权，如例2、例3等所示；还可以一次传播多个同类对象的权限，如例2所示；甚至一次可以完成对基本表、视图和属性列这些不同对象的授权，如例4所示；但授予关于DATABASE的权限必须与授予关于TABLE的权限分开，因为它们使用不同的对象类型关键字。3435例7把用户U4修改学生学号的权限收回REVOKEUPDATE(Sno)ONTABLEStudentFROMU4;例8收回所有用户对表SC的查询权限REVOKESELECTONTABLESCFROMPUBLIC;例9把用户U5对SC表的INSERT权限收回REV

14、OKEINSERTONTABLESCFROMU5;36在例5中，U5又将对SC表的INSERT权限授予了U6，而U6又将其授予了U7，执行此REVOKE语句后，DBMS在收回U5对SC表的INSERT权限的同时，还会自动收回U6和U7对SC表的INSERT权限，即收回权限的操作会级联下去的。但如果U6或U7还从其他用户处获得对SC表的INSERT权限，则他们仍具有此权限，系统只收回直接或间接从U5处获得的权限。37可见，SQL提供了非常灵活的授权机制。用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户。被授权的用户如果有“继续授权”的许可，还可以

15、把获得的权限再授予其他用户。DBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。而所有授予出去的权力在必要时又都可以用REVOKE语句收回。38嵌入式SQL一般形式：一般形式：EXEC SQL ；SQL语句语句可执行语句可执行语句说明性语句说明性语句数据定义数据定义数据控制数据控制数据操纵数据操纵39利用如下关系模式完成要求的操作。利用如下关系模式完成要求的操作。S S（SNOSNO，SNAMESNAME，AGEAGE，SEXSEX，SDEPTSDEPT）C C（CNOCNO，CNAMECNAME，CREDITCREDIT，TEACHER,PRE_CNOTE

16、ACHER,PRE_CNO）S_CS_C（SNO,CNO,GRADESNO,CNO,GRADE）401 1 查询与查询与“王英王英”在同一个系学习的学生学号和姓名，在同一个系学习的学生学号和姓名，请使用自连接查询实现请使用自连接查询实现2 2 查询年龄大于查询年龄大于2323岁的男同学的学号和姓名；岁的男同学的学号和姓名；3 3 查询查询00040004号学生选修课程的课程号、课程名和任课号学生选修课程的课程号、课程名和任课教师的姓名；教师的姓名；4 4 查询查询“张小飞张小飞”同学没有选修的课程的课程号和课同学没有选修的课程的课程号和课程名；程名；5 5 找出最大年龄学生的姓名和年龄找出最大年龄学生的姓名和年龄6 6 查询至少选修了查询至少选修了3 3门课程的学生的学号和姓名；门课程的学生的学号和姓名；7 7 查询全部学生都选修了的课程号和课程名；查询全部学生都选修了的课程号和课程名；8 8 在学习表中删除没有成绩的选课纪录；在学习表中删除没有成绩的选课纪录；9 9 把把“高等数学高等数学”的所有不及格成绩都改为的所有不及格成绩都改为6060分；分；10 10 新建一个未选课的学生信息表新建一个未选课的学生信息表Stu_NoselStu_Nosel，利用子利用子查询的结果将所有记录批量插入到查询的结果将所有记录批量插入到Stu_NoselStu_Nosel中。中。41