数据库原理第11讲

3.3.5集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS).1．并操作形式

<查询块> UNION[ALL] <查询块>参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同.并操作（续）[例45]查询计算机科学系的学生及年龄不大于19岁的学生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；.并操作（续）方法二：

SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；.并操作（续）[例46]查询选修了课程1或者选修了课程2的学生。方法一：

SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；.并操作（续）方法二：

SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；.并操作（续）[例47]设数据库中有一教师表Teacher(Tno,Tname,...)。查询学校中所有师生的姓名。

SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;new.2．交操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。.2．交操作[例48]查询计算机科学系的学生与年龄不大于19岁的学生的交集本例实际上就是查询计算机科学系中年龄不大于19岁的学生

SELECT*FROMStudentWHERESdept='CS'ANDSage<=19；.交操作（续）[例49]查询选修课程1的学生集合与选修课程2的学生集合的交集本例实际上是查询既选修了课程1又选修了课程2的学生SelectsnoFromscWherecno=‘1’IntersectSelectsnoFromscWherecno=‘2’;.SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2')；.交操作（续）[例50]查询学生姓名与教师姓名的交集本例实际上是查询学校中与教师同名的学生姓名

SELECTDISTINCTSnameFROMStudentWHERESnameIN (SELECTTnameFROMTeacher);new.3．差操作

[例51]查询计算机科学系的学生与年龄不大于19岁的学生的差集。

Select*fromstudentwheresdept=‘CS’MinusSelect*fromstudentwhereage<=19;.3．差操作[例51]查询计算机科学系的学生与年龄不大于19岁的学生的差集。本例实际上是查询计算机科学系中年龄大于19岁的学生

SELECT*FROMStudentWHERESdept='CS'ANDSage>19；.差操作（续）[例52]查询学生姓名与教师姓名的差集本例实际上是查询学校中未与教师同名的学生姓名

SELECTDISTINCTSnameFROMStudentWHERESnameNOTIN (SELECTTname FROMTeacher);new.4.对集合操作结果的排序ORDERBY子句只能用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDERBY子句只能出现在最后对集合操作结果排序时，ORDERBY子句中用数字指定排序属性new.对集合操作结果的排序（续）[例53]错误写法

SELECT*FROMStudentWHERESdept='CS'ORDERBYSnoUNIONSELECT*FROMStudentWHERESage<=19ORDERBYSno；new.对集合操作结果的排序（续）正确写法

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19ORDERBY1；new.3.3.6SELECT语句的一般格式SELECT[ALL|DISTINCT]<目标列表达式>[别名][，<目标列表达式>[别名]]…FROM

<表名或视图名>[别名][，<表名或视图名>[别名]]…[WHERE<条件表达式>][GROUPBY<列名1>[，<列名1’>]...[HAVING

<条件表达式>]][ORDERBY<列名2>[ASC|DESC][，<列名2’>[ASC|DESC]]…]；.3.4数据更新3.4.1插入数据3.4.2修改数据3.4.3删除数据.3.4.1插入数据两种插入数据方式插入单个元组插入子查询结果.1.插入单个元组语句格式INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]VALUES(<常量1>[，<常量2>]…)功能将新元组插入指定表中。.插入单个元组（续）[例1]将一个新学生记录

INSERTINTOStudentVALUES(‘95020’，‘陈冬’，‘男’，null，18)；或

INSERTINTOStudent(sname,sno,ssex,sage)VALUES(‘陈冬’，‘

95020’，‘男’，18)；.AdddatevalueConsiderParty(name,birthday)Insertintopartyvalues(‘Newborn’,sysdate);Insertintopartyvalues(‘Rose’,‘11-Jan-84’);Insertintopartyvalues(‘Jack’,todate(’11/01/1984’,’MM/DD/YYYY’));.UsesubstitutionvariablesConsiderSC(sno,cno,grade)Insertintoscvalues(‘&sno’,’&cno’,&grade);Entervalueforsno:95001Entervalueforcno:c01Entervalueforgrade:90old1:insertintoscvalues(‘&sno’,’&cno’,&grade);new1:insertintoscvalues(‘95001’,’c01’,90)1rowcreated..2.插入子查询结果语句格式

INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]

子查询；功能将子查询结果插入指定表中.插入子查询结果（续）[例3]对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表

CREATETABLEDeptage(SdeptCHAR(15)/*系名*/AvgageSMALLINT)；/*学生平均年龄*/

.插入子查询结果（续）第二步：插入数据

INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；.CreateTableRe-visitedOraclesupportscreatingtableandinsertingtuplesfromaquerycombined.Example:CreatetalbeCSStudent asselectsno,sname,ssex,sage fromstudent wheresdept=‘CS’;.3.4.2修改数据语句格式

UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；功能修改指定表中满足WHERE子句条件的元组.修改数据（续）三种修改方式修改某一个元组的值修改多个元组的值带子查询的修改语句.1.修改某一个元组的值[例4]将学生95001的年龄改为22岁。

UPDATEStudentSETSage=22WHERESno='95001'；.2.修改多个元组的值[例5]将所有学生的年龄增加1岁。

UPDATEStudentSETSage=Sage+1；.修改多个元组的值(续)[例6]将信息系所有学生的年龄增加1岁。

UPDATEStudentSETSage=Sage+1WHERESdept='IS'；.3.带子查询的修改语句[例7]将计算机科学系全体学生的成绩置零。

UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；.修改数据（续）DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性主码不允许修改用户定义的完整性

NOTNULL约束

UNIQUE约束值域约束.3.4.3删除数据

DELETEFROM<表名>[WHERE<条件>]；功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要修改表中的所有元组.删除数据（续）三种删除方式删除某一个元组的值删除多个元组的值带子查询的删除语句.1.删除某一个元组的值[例8]删除学号为95019的学生记录。

DELETEFROMStudentWHERESno='95019'；.2.删除多个元组的值[例9]删除2号课程的所有选课记录。

DELETEFROMSC；

WHERECno='2'；[例10]删除所有的学生选课记录。

DELETEFROMSC；.3.带子查询的删除语句[例11]删除计算机科学系所有学生的选课记录。

DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；.删除数据(续)DBMS在执行删除语句时会检查所插元组是否破坏表上已定义的完整性规则参照完整性不允许删除级联删除.更新数据与数据一致性DBMS在执行插入、删除、更新语句时必须保证数据库一致性必须有事务的概念和原子性完整性检查和保证.Considerthefollowingtabledefinitions: createtableDepartments (Namevarchar2(20)notnull,

…); createtableStudents (… Sdeptvarchar2(20), foreignkey(Sdept)references Departments(Name));更新数据与数据一致性.SnoSnameSsexSageSdept95001

李勇

男20CS95002

刘晨

女19IS95003

王敏

女18MA95004

张立

男19ISStudents:NameDeanLocCSJacksonF1ISLincoinF2MAParksF3Departments:.InsertanewtupletintoStudentsAllowedift[Sdept]iscurrentlyunderDepartments.NameRejectedotherwise更新数据与数据一致性.DeleteanexistingtupletfromDepartments Options:Rejectedift[Name]isreferencedbysometupleinStudents.AllowedbutalsodeletealltuplesofStudentswhichreferencetAllowedbutalsochanget1[Sdept]tonullforeveryt1inStudentswhichreferencest更新数据与数据一致性.Updatet[Name]ofatupletinDepartments: Options:Rejectedift[Name]isreferencedbysometupleinStudentsAllowedbutalsoupdatealltuplesofStudentswhichreferencetAllowedbutalsochanget1[Sdept]tonullforeveryt1inStudentswhichreferencet更新数据与数据一致性.SpecifyoptionsusingSQLFormat: foreignkey(Sdept)references Departments(Name) [ondelete{noaction|cascade| setdefault|setnull}]

更新数据与数据一致性.RedefinetheStudentstable. createtableStudents (… foreignkey(Sdept)references Departments(Name) ondeletenoactiononupdatecascade);更新数据与数据一致性.视图（Views）Atablescreatedbycreatetable…isabasetable.Abasetableisarealtable.Theconceptualschemaofadatabaseisthesetofallbasetables.Aviewisavirtualtablethatdoesnotnecessarilyexistinphysicalform.Eachviewisdefined,directlyorindirectly,intermsofbasetables..3.5视图视图的特点虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数据也随之改变.3.5视图基于视图的操作

查询删除受限更新定义基于该视图的新视图.1.建立视图语句格式

CREATEVIEW

<视图名>[(<列名>[，<列名>]…)]AS<