数据库原理第三章课件.ppt

第三章关系数据库语言SQL（续）,概述SQL的数据定义SQL的数据操纵SQL的视图定义SQL的数据控制嵌入式SQL,第三章关系数据库语言SQL,联接查询(1of8),联接查询：涉及至少两个表的查询。SQL中没有专门的JOIN命令，而是通过SELECT语句中的WHERE子句来达到联接运算的目的，因此更加灵活、简便。,联接查询(2of8),联接条件的一般格式为：表名1.列名1比较运算符表名2.列名2其中：比较运算符主要有：=、=、，=，）与ANY或ALL谓词配合使用,带有比较运算符的子查询(2of2),又看例1：查询与A在同一个系学习的学生,SELECT*FROMStudentWHERESdeptIN（SELECTSdeptFROMStudentWHERESname=A）;,SELECT*FROMStudentWHERESdept=（SELECTSdeptFROMStudentWHERESname=A）;,SELECT*FROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=A)=Sdept；,子查询应跟在比较符之后,带有ANY或ALL谓词的子查询(1of5),谓词语义ANY：某一个值ALL：所有值使用ANY或ALL谓词时必须同时使用比较运算符,ANYALL,=,带有ANY或ALL谓词的子查询(2of5),例1：查询其他系中比IS系某一学生年龄小的学生姓名和年龄,SELECTSname,SageFROMStudentWHERESageIS；,其他方法？,带有ANY或ALL谓词的子查询(3of5),例1：查询其他系中比IS系某一学生年龄小的学生姓名和年龄用集函数：,SELECTSname,SageFROMStudentWHERESageIS；,效率更高,带有ANY或ALL谓词的子查询(4of5),例2：查询其他系中比IS系所有学生年龄都小的学生姓名和年龄,SELECTSname,SageFROMStudentWHERESageIS；,SELECTSname,SageFROMStudentWHERESageIS；,带有ANY或ALL谓词的子查询(5of5),ANY，ALL与集函数及IN谓词的对应关系,带有EXISTS谓词的子查询(1of15),EXISTS谓词与NOTEXISTS谓词不同形式的查询间的替换用EXISTS/NOTEXISTS实现全称量词用EXISTS/NOTEXISTS实现逻辑蕴函,带有EXISTS谓词的子查询(2of15),EXISTS谓词存在量词带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值,带有EXISTS谓词的子查询(3of15),例1：查询所有选修了1号课程的学生姓名,SELECTSnameFROMStudentWHEREEXISTS（SELECT*FROMSCWHERESno=Student.SnoANDCno=1）；,相当于一个变量，根据它的值处理内层查询Student中有多少个学号，内层查询就进行多少次,属于相关子查询：查询条件依赖于外层查询中某个值,带有EXISTS谓词的子查询(4of15),例1：查询所有选修了1号课程的学生姓名,SELECTSnameFROMStudentWHEREEXISTS（SELECT*FROMSCWHERESno=Student.SnoANDCno=1）；,由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义,其他方法？,用联结、相关、不相关子查询实现,带有EXISTS谓词的子查询(5of15),例2：查询没有选修1号课程的学生姓名,SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMSCWHERESno=Student.SnoANDCno=1）；,此例用连接运算难于实现,SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDCno1;,带有EXISTS谓词的子查询(6of15),不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。,带有EXISTS谓词的子查询(7of15),例：再看“查询与A在同一个系学习的学生”,SELECT*FROMStudentS1WHEREEXISTSSELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=A；,back,带有EXISTS谓词的子查询(8of15),用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：,带全称量词的谓词,带存在量词的谓词,（x）P（x）,（x（P（x）,带有EXISTS谓词的子查询(9of15),例：查询选修了全部课程的学生姓名,带全称量词的谓词,带存在量词的谓词,（x）P（x）,（x（P（x）,本例变为：选这样的学生姓名，没有一门课程是他不选修的,选这样的学生姓名，没有一门课程是他不选修的,分析:任给一学号Sno,若不存在他不选修的课,则显示他的姓名,(Cno(学号为Sno的学生没修课程Cno),(Cno(在SC中不存在选课单(Sno,Cno,),例：查询选修了全部课程的学生姓名,带有EXISTS谓词的子查询(10of15),SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno);,带有EXISTS谓词的子查询(11of15),带有EXISTS谓词的子查询(12of15),用EXISTS/NOTEXISTS实现逻辑蕴函(难点)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：pqpq,带有EXISTS谓词的子查询(13of15),例、查询至少选修了学生95002选修的全部课程的学生号码分析：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词“学生95002选修了课程y”用q表示谓词“学生x选修了课程y”则上述查询为:(y)pq,带有EXISTS谓词的子查询(14of15),等价变换：(y)pq(y(pq)(y(pq)y(pq)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。,带有EXISTS谓词的子查询(15of15),SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=95002ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)；,不存在这样的课程y，学生95002选修了y，而学生x没有选。,P,q,集合查询(1of6),包括：并操作UNION、交操作INTERSECT、差操作MINUS要求：各数据项数目必须相同，对应项的数据类型也必须相同。标准SQL中无交操作INTERSECT、差操作MINUS，但可以用其它方法来实现。,例（并操作）：查询计算机科学系（CS)的学生及年龄不大于19岁的学生。,SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19；,SELECTDISTINCT*FROMStudentWHERE(Sdept=CS)OR(Sage=19)；,集合查询(2of6),集合查询(3of6),例（交操作）：查询计算机科学系（CS)且年龄不大于19岁的学生。,SELECT*FROMStudentWHERE(Sdept=CS)AND(Sage=19)；,SELECT*FROMStudentWHERE(Sdept=CS)ANDSnoIN(SELECTSnoFROMStudentWHERE(Sage=19)；,例（差操作）：查询计算机科学系（CS)的学生与年龄不大于19岁的学生的差集。,SELECT*FROMStudentWHERE(Sdept=CS)AND(NOT(Sage=19)；,SELECT*FROMStudentWHERE(Sdept=CS)ANDSnoNOTIN(SELECTSnoFROMStudentWHERE(Sage=19)；,集合查询(4of6),集合查询(5of6),对集合操作结果的排序ORDERBY子句只能用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDERBY子句只能出现在最后对集合操作结果排序时，ORDERBY子句中用数字指定排序属性,集合查询(6of6),SELECT*FROMStudentWHERESdept=CSORDERBYSnoUNIONSELECT*FROMStudentWHERESage=19ORDERBYSno；,SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage80；,例3：在关系SC中，把平均成绩大于80分的男学生(Ssex=1）的学号和平均成绩存入到另一个关系S_GRADE(Sno,AVG_GRADE)中。,Example(INSERT语句)(4of5),Example(INSERT语句)(5of5),DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOTNULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内,3.3.2SQL的数据更新-Update语句(1of6),修改UpdateUPDATESET=,=WHERE；,Example(Update语句)(2of6),例1：把学生S1的成绩置0。,UPDATESCSETGrade=0WHERESno=S1;,UPDATEStudentSETSage=Sage+1;,例2：把所有学生的年龄增加1岁。,例3：把计算机科学系全体学生的成绩置零。,Example(Update语句)(3of6),嵌套子查询,UPDATESCSETGrade=0WHERECS=（SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno)；,例4：把课程名为M的成绩提高5%。,Example(Update语句)(4of6),UPDATESCSETGrade=Grade*1.05WHERECnoIN（SELECTCnoFROMCourseWHERECname=M)；,例5：当C4课程的成绩低于该门课程的平均成绩时提高5%。,Example(Update语句)(5of6),UPDATESCSETGrade=Grade*1.05WHERECno=C4ANDGrade（SELECTAVG(Grade)FROMSCWHERECno=C4)；,Example(Update语句)(6of6),DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性主码不允许修改用户定义的完整性NOTNULL约束UNIQUE约束值域约束,3.3.2SQL的数据更新-DELETE语句(1of3),删除DELETEDELETEFROM表名WHERE条件；,无条件时，删除全部记录，但表的结构还在，相当于一个空表,例1：删除学号为S1的学生。,DELETEFROMStudentWHERESno=S1;,例2：把数学系全体学生的选课记录删除。,DELETEFROMSCWHEREMA=（SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno)；,Example(DELETE语句)(2of3),Example(DELETE语句)(3of3),DBMS在执行删除语句时会检查所删元组是否破坏表上已定义的完整性规则参照完整性不允许删除级联删除,更新操作与数据库的一致性,DBMS在执行插入、删除、更新语句时必须保证数据库一致性必须有事务的概念和原子性完整性检查和保证,第三章关系数据库语言SQL（续）,概述SQL的数据定义SQL的数据操纵SQL的视图定义SQL的数据控制嵌入式SQL,第三章关系数据库语言SQL,3.4SQL的视图定义,视图是从一个或几个基本表（或视图）导出的表。用户外模式是由若干基本表和/或若干视图构成的。视图是一个虚表，只存储视图的定义，数据存在所基于的基本表中。视图定义后就可象基本表一样来使用。可创建、删除视图可用来定义新的视图可在视图上查询（SELECT）可更新（INSERT，DELETE，UPDATE）视图，但受限制,1.建立视图基本格式CREATEVIEW视图名（字段名，字段名）AS子查询WITHCHECKOPTION功能：在数据字典中存储视图的定义（但并不执行子查询），此后视图名就可作为一个表来使用。限制表中特定列和行的使用隐藏复杂查询的细节将更新、插入和修改值限制到某个范围,定义视图(1of11),例1：建立计算机系的学生视图。说明:若SELECT语句中有集函数、或字段表达式、或多表联接有同名字段时，则视图中必须指定字段名；视图中字段名可以和基本表中的字段名不同；一般不允许有ORDERBY子句和DISTINCT短语。,定义视图(2of11),CREATEVIEWCS_SASSELECTSno,Sname,SageFROMStudentWHERESdept=CS,定义视图(3of11),2.删除视图DROPVIEW视图名例：DROPVIEWCS_S,例2：建立信息系学生的视图。并要求进行修改和插入操作时仍保证该视图只有信息系的学生。,注意：1、DBMS执行CREATEVIEW的结果只把对视图的定义存入数据字典；并不执行其中的查询语句。2、只有对视图进行查询时，才把视图的定义从数据字典中取出，转换成查询语句，进行查询。,定义视图(4of11),CREATEVIEWIS_SASSELECTSno,Sname,SageFROMStudentWHERESdept=IS,(Sno,Sname,Sage),WITHCHECKOPTION;,例2(续)：对IS_S视图的更新操作修改操作：DBMS自动加上Sdept=IS的条件删除操作：DBMS自动加上Sdept=IS的条件插入操作：DBMS自动检查Sdept属性值是否为IS如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为IS,定义视图(5of11),例3：建立信息系选修了C1课程的学生的视图。,定义视图(6of11),CREATEVIEWIS_S1ASSELECTStudent.Sno,Sname,GradeFROMStudent,SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.Cno=C1;,(Sno,Sname,Grade),CREATEVIEWIS_S2ASSELECTSno,Sname,GradeFROMIS_S1WHEREGrade=A；,例4：建立信息系选修了C1课程且成绩为A的学生的视图。,定义视图(7of11),例5：定义一个反映学生出生年份的视图,定义视图(8of11),CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,2006-SageFROMStudent;,例6：建立学生学号及平均成绩的视图。,定义视图(9of11),CREATEVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno,定义视图(10of11),例7：将Student表中所有女生记录定义为一个视图,CREATEVIEWF_S1(stdnum，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex=女；,缺点：修改基表Student的结构后，Student表与F_S1视图的映象关系被破坏，导致该视图不能正确工作。,以SELECT*方式创建的视图可扩充性差，应尽可能避免,定义视图(11of11),为基表Student增加属性列不会破坏Student表与F_S2视图的映象关系,CREATEVIEWF_S(stdnum，name，sex，age，dept)ASSELECTSno，Sname，Ssex，Sage，SdeptFROMStudentWHERESsex=女；,例7(续),查询视图(1of4),执行过程：首先进行有效性检查（视图、基本表是否存在等）；从数据字典中取出视图的定义；把对视图的查询修改为对基本表的查询；在基本表上执行修改后的查询。这一转换过程称为视图消解。,SELECTSno，SageFROMWHERESage=90GROUPBYSno；,SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)=90；,更新视图(1of3),更新操作包括INSERT，DELETE，UPDATE执行方式：将对视图的更新语句转化为对相应的基本表的更新语句，然后执行。注意：不是所有的对视图的更新语句都可转化为正确的更新语句。,例如：UPDATES_GSETGAVG=BWHERESno=S1;,不能有意义地转化,INSERTINTOStudent(Sno,Sname,Sdept,Sage)VALUES(S12,YanXi,IS,19),例1:INSERTINTOIS_SVALUES(S12,YanXi,19),视图中的子查询SELECTSno,Sname,SageFROMStudentWHERESdept=IS,更新视图(2of3),1、执行方式,更新视图(3of3),2、注意事项,有些视图是可更新的,有些视图是不可更新的。但现在还无判别方法。肯定可以更新的视图是行列子集视图,且各个系统对视图的更新还有进一步的规定。例如，DB2中规定了七条视图更新的原则:书P127128,视图的作用,视图能够简化用户的操作视图使用户能以多种角度看待同一数据视图对重构数据库提供了一定程度的逻辑独立性视图能够对机密数据提供安全保护,