《关系数据库标准语言SQL(续2).ppt》由会员分享，可在线阅读，更多相关《关系数据库标准语言SQL(续2).ppt（40页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1第第3章章关系数据库标准语言关系数据库标准语言SQLStructuredQuerylanguage 数据定义数据定义 数据操纵数据操纵 数据控制数据控制 数据约束数据约束2视图：视图：从一个或几个基本表（或视图）导出的表。一、建立视图的优点一、建立视图的优点1、简化用户的操作2、灵活3、提供一定程度的逻辑独立性4、提供安全保护3二、定义视图二、定义视图1、建立视图 CREATE VIEW (,)AS WITH CHECK OPTION;2、删除视图 DROP VIEW 三、查询视图(同表查询一样）四、更新视图(同表更新一样）4如果CREATE VIEW语句仅指定了视图名，省略了组成视图的各个
2、属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。在下列三种情况三种情况下必须明确指定组成视图的所有列名：其中某个目标列不是单纯的属性名，而是集函数或列表达式 多表连接时选出了几个同名列作为视图的字段 需要在视图中为某个列启用新的更合适的名称5例例1 1 建立信息系学生的视图。建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,ageFROMSWHEREdept=IS;行列子集视图行列子集视图：视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码。6例例2 2 建立信息系学生的视图，并要求进行修改和建立信息
3、系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生插入操作时仍须保证该视图只有信息系的学生CREATEVIEWIS_StudentASSELECTSno,Sname,ageFROMSWHEREdept=ISWITHCHECKOPTION;7视图不仅可以建立在单个基本表上，也可以建立在多个基本表上。例例3 3建立信息系选修了建立信息系选修了1 1号课程的学生的视图号课程的学生的视图CREATEVIEWIS_S1(Sno,Sname,Grade)ASSELECTS.Sno,Sname,GradeFROMS,SCWHEREdept=ISANDS.Sno=SC.SnoANDSC.C
4、no=1;8 视图不仅可以建立在一个或多个基本表上也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。例例4 4建立信息系选修了建立信息系选修了1 1号课程且成绩在号课程且成绩在9090分分以上的学生的视图以上的学生的视图CREATEVIEWIS_S2ASSELECTSno,Sname,GradeFROMIS_S1WHEREGrade=90;9定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派义视图时可以
5、根据应用的需要，设置一些派生属性列。生属性列。这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列虚拟列。带虚拟列的视图我们称为带表达式的视图表达式的视图。10例例5 5定义一个反映学生出生年份的视图定义一个反映学生出生年份的视图CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,2010-ageFROMS;带表达式的视图11还可以用带有集函数和GROUP BY子句的查询来定义视图。这种视图称为分组视图分组视图。例例6 6 将学生的学号及他的平均成绩定义为一将学生的学号及他的平均成绩定义为一个视图个视图CREATVIEWS_G(Sno
6、,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;12例例7 7将将S S表中所有女生记录定义为一个视图表中所有女生记录定义为一个视图CREATEVIEWF_Student(stdnum,name,sex,age,dept)ASSELECT*FROMSWHEREsex=女;这里视图F_Student是由子查询“SELECT*建立的。由于该视图一旦建立后，S表就构成了视图定义的一部分，如果以后修改了基本表S的结构，则S表与F_Student视图的映像关系受到破坏，因而该视图就不能正确工作了。为避免出现这类问题，可以采用下列两种方法：131.建立视图时明确指
7、明属性列名，而不是简单地用SELECT*。即：CREATEVIEWF_Student(stdnum,name,sex,age,dept)ASSELECTSno,Sname,sex,age,deptFROMSWHERESsex=女;2.在修改基本表之后删除原来的视图，然后重建视图。这是最保险的方法。142、删除视图语句的格式为：DROP VIEW;一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用DROP VIEW语句将他们一一删除。15例例8 8删除视图删除视图IS_S1IS_S1DROPVIEWIS_S1;执行此语句后，IS_S1视图的定义将从数据字典中删除。由IS_S1视图导出
8、的IS_S2视图的定义虽仍在数据字典中，但该视图已无法使用了，因此应该同时删除。16三、查询视图DBMSDBMS执行对视图的查询时，首先进行有效执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正对基本表的查询，然后再执行这个经过修正的查询。的查询。将对视图的查询转换为对基本表
9、的查询的过程称为视图的消解视图的消解（View Resolution）。17例1在信息系学生的视图中找出年龄小于20岁的学生SELECTSno,ageFROMIS_StudentWHEREage20;DBMS执行此查询时，将其与IS_Student视图定义中的子查询 SELECTSno,Sname,ageFROMSWHEREdept=IS;结合起来，转换成对基本表S的查询，修正后的查询语句为18SELECTSno,SageFROMStudentWHERESdept=ISANDSage=90S_G的定义为Selectsno,avg(grade)froms_cgroupbysno该转换后得到：22
10、该查询转换后得到SELECTSNO,AVG(GRADE)FROMS_CWHEREAVG(GRADE)=90这是错误的！这是错误的！正确的查询应该是SELECTSNO,AVG(GRADE)FROMS_CGROUPBYSNOHAVINGAVG(GRADE)=90此类查询应该直接对表进行！23四、更新视图更新视图包括插入（INSERT）、删除（DELETE）和修改（UPDATE）三类操作。由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。为防止用户通过视图对数据进行增删改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITH CHECK OPTION子
11、句，这样在视图上增删改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。24例例1 1将信息系学生视图将信息系学生视图IS_StudentIS_Student中学号中学号为为9500295002的学生姓名改为的学生姓名改为“刘辰刘辰”UPDATEIS_StudentSETSname=刘辰WHERESno=95002;一般行列子集视图都是可更新视图一般行列子集视图都是可更新视图25与查询视图类似，DBMS执行此语句时，首先进行有效性检查，检查所涉及的表、视图等是否在数据库中存在如果存在，则从数据字典中取出该语句涉及的视图的定义，把定义中的子查询和用户对视图的更新操作结
12、合起来，转换成对基本表的更新执行经过修正的更新操作。转换后的更新语句为：UPDATESSETSname=刘辰WHERESno=95002ANDdept=IS;26例2向信息系学生视图IS_S中插入一个新的学生记录，其中学号为95029，姓名为赵新，年龄为20岁INSERTINTOIS_StudentVALUES(95029,赵新,20);DBMS将其转换为对基本表的更新：INSERTINTOS(Sno,Sname,age,dept)VALUES(95029,赵新,20,IS);这里系统自动将系名IS放入VALUES子句中。27例3删除计算机系学生视图CS_S中学号为95029的记录DELETE
13、FROMIS_StudentWHERESno=95029;DBMS将其转换为对基本表的更新：DELETEFROMSWHERESno=95029ANDdept=IS;28更新的限制 在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。29DB2规定：1.1.若视图是由两个以上基本表导出的，则此视若视图是由两个以上基本表导出的，则此视图不允许更新。图不允许更新。2.2.若视图的字段来自字段表达式或常数，则不若视图的字段来自字段表达式或常数，则不允许对此视图执行允许对此视图执行INSERTINSERT和和UPDATEUPDATE操作，操作，但允
14、许执行但允许执行DELETEDELETE操作。操作。3.3.若视图的字段来自集函数，则此视图不允许若视图的字段来自集函数，则此视图不允许更新。更新。4.4.若视图定义中含有若视图定义中含有GROUP BYGROUP BY子句，则此视子句，则此视图不允许更新。图不允许更新。5.5.若视图定义中含有若视图定义中含有DISTINCTDISTINCT短语，则此视短语，则此视图不允许更新。图不允许更新。306.6.若视图定义中有嵌套查询，并且内层查询的若视图定义中有嵌套查询，并且内层查询的FROMFROM子句中涉及的表也是导出该视图的基本子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。表，则此
15、视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC：CREATEVIEWGOOD_SCASSELECTSno,Cno,GradeFROMSCWHEREGrade(SELECTAVG(Grade)FROMSC);导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。7.7.一个不允许更新的视图上定义的视图也不允一个不允许更新的视图上定义的视图也不允许更新。许更新。31视图的优点视图的优点视图能够简化用户的操作视图使用户能以多种角度看待同一数据视图对重构数据库提供了一定程度的逻辑独立性视图能够对机密数据提供安全保护32数据控
16、制对象对象类型 操作权限属性列 TABLESELECT,INSERT,UPDATE,DELETE,ALLPRIVILEGES视 图TABLESELECT,INSERT,UPDATE,DELETE,ALLPRIVILEGES基本表 TABLESELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,ALLPRIVILEGES数据库DATABASECREATETABLE，ALTERTABLE33一、授权GRANT,ON TO,WITH GRANT OPTION;二、收回权限REVOKE,ON FROM,34例例1 1把查询把查询S S 表权限授给用户表权限授给用户U1U1GR
17、ANTSELECTONTABLESTOU1;例例2 2把对把对S S表和表和C C表的全部权限授予用户表的全部权限授予用户U2U2和和U3U3GRANTALLPRIVILEGESONTABLES,CTOU2,U3;例例3 3把对表把对表SCSC的查询权限授予所有用户的查询权限授予所有用户GRANTSELECTONTABLESCTOPUBLIC;在sqlserver2000中不需要对象类型table只需对象名sc35例例4 4把查询把查询S S 表和修改学生学号的权限授给表和修改学生学号的权限授给用户用户U4U4这里实际上要授予U4用户的是对基本表S 的SELECT权限和对属性列Sno的UPDA
18、TE权限。授予关于属性列的权限时必须明确指出相应属性列名。完成本授权操作的SQL语句为：GRANTUPDATE(Sno),SELECTONTABLESTOU4;36例例5 5把对表把对表SCSC的的INSERTINSERT权限授予权限授予U5U5用户，并用户，并允许他再将此权限授予其他用户允许他再将此权限授予其他用户GRANTINSERTONTABLESCTOU5WITHGRANTOPTION;U5U5可以将此权限授予可以将此权限授予U6U6：GRANTINSERTONTABLESCTOU6WITHGRANTOPTION;同样，同样，U6U6还可以将此权限授予还可以将此权限授予U7U7：GRA
19、NTINSERTONTABLESCTOU7;37例例6DBA6DBA把在数据库把在数据库S_CS_C中建立表的权限授予中建立表的权限授予用户用户U8U8GRANTCREATETABLEONDATABASESTUTOU8;例例7 7把用户把用户U4U4修改学生学号的权限收回修改学生学号的权限收回REVOKEUPDATE(Sno)ON TABLESFROMU4;例例8 8收回所有用户对表收回所有用户对表SCSC的查询权限的查询权限REVOKESELECTON TABLESCFROMPUBLIC;例例9 9把用户把用户U5U5对对SCSC表的表的INSERTINSERT权限收回权限收回REVOKEI
20、NSERTONTABLESCFROMU5;38在例5中，U5又将对SC表的INSERT权限授予了U6，而U6又将其授予了U7，执行此REVOKE语句后，DBMS在收回U5对SC表的INSERT权限的同时，还会自动收回U6和U7对SC表的INSERT权限，即收回权限的操作会级联下去的。但如果U6或U7还从其他用户处获得对SC表的INSERT权限，则他们仍具有此权限，系统只收回直接或间接从U5处获得的权限。39嵌入式SQL一般形式：一般形式：EXECSQL；SQL语句语句可执行语句可执行语句说明性语句说明性语句数据定义数据定义数据控制数据控制数据操纵数据操纵40例：C语言EXEC SQL DROP TABLE Student;EXECSQLCREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20),SsexCHAR(1),SageINT,SdeptCHAR(15);