关系运算与SQL实例

1,关系运算与SQL实例,金秋乐,2,一：关系代数-传统的集合运算,1、并(Union)RS=t|tRtS2、交(Intersection)RS=t|tRtS3、差(Difference)R-S=t|tRtS,3,一：关系代数-传统的集合运算,4、广义笛卡儿积(ExtendedCartesianProduct)RS=trts|trRtsS,R,S,4,一：关系代数-传统的集合运算,运算结果,RS,RS,R-S,5,一：关系代数-传统的集合运算,运算结果,RS,6,一：关系代数-专门的关系运算,专业标记1、设关系模式为R(A1,A2,An).它的一个关系设为R。tR表示t是R的一个元组。tAi表示元组t中相应于属性Ai的一个分量2、若A=Ai1,Ai2,Aik,其中Ai1,Ai2,Aik是A1,A2,An中的一部分，则A称为属性列或域列。tA=(tAi1,tAi2,tAik)表示元组t在属性列A上诸分量的集合。A则表示A1,A2,An中去掉Ai1,Ai2,Aik后剩余的属性组,7,一：关系代数-专门的关系运算,专业标记3、R为n目关系。S为m目关系。trR，tsS,trts称为元组的连接(Concatenation).它是一个n+m列的元组，前n个分量为R中的一个n元组，后m个分量为S中的一个m元组4、给定一个关系R(X,Z)，X和Z为属性组。定义：当tX=x时，x在R上的象集(ImageSet)为：Zx=tZ|tR,tX=x，它表示R中属性组X上值为x的诸元组在Z上分量的集合,8,一：关系代数-专门的关系运算,专门的关系运算1、选择(Selection)又称为限制(Restriction),在关系R中选择满足给定条件的诸元组，记作F(R)=t|tRF(t)=真例1、查询信息系(IS系)全体学生Sdept=IS(Student)5=IS(Student)例2、查询年龄小于20岁的学生Sage20(Student)420(Student),9,一：关系代数-专门的关系运算-例,Student,Couse,SC,10,一：关系代数-专门的关系运算-例,Student,Couse,SC,11,一：关系代数-专门的关系运算,专门的关系运算2、投影(Projection)从R中选择若干个属性列组成新的关系。记作：A(R)=tA|tR,A是R中的属性列例3：查询学生的姓名和所在的系Sname,Sdept(Student)2,5(Student)例4：查询学生关系Student中有哪些系Sdept(Student),12,一：关系代数-专门的关系运算,专门的关系运算3、连接(Join)又称连接。从两个关系的笛卡儿积中选取属性间满足一定条件的元组。记作；RS=trts|trRtsStrAtsBA和B分别为R和S上度数相等且可比的属性组.为比较运算符特殊的连接:等值连接:RS=trts|trRtsStrA=tsB自然连接:将等值连接中重复的属性列去掉,记作:RS=trts|trRtsStrA=tsB,AB,A=B,13,一：关系代数-专门的关系运算-连接例题,R,S,14,一：关系代数-专门的关系运算,专门的关系运算4、除(Division)R(X,Y)与S(Y,Z)的除运算得到一个新的关系P(X),P是R中满足下列条件的元组在X属性列上的投影:元组在X上分量值x的象集Yx包含S在Y上投影的集合,记作:RS=trX|trRy(S)Yx其中Yx为x在R中的象集,x=trX,15,一：关系代数-专门的关系运算-除例题,R,S,RS,分析:在关系R中A可以取4个值a1,a2,a3,a4.其中a1的象集为:(b1,c2),(b2,c3),(b2,c1)a2的象集为:(b3,c7),(b2,c3)a3的象集为:(b4,c6)a4的象集为:(b6,c6)S在(B,C)上的投影为(b1,c2),(b2,c1),(b2,c3),16,一：关系代数-专门的关系运算-例题,例7:查询至少选修了1号课程和3号课程的学生号码先建立临时关系K:Sno,Cno(SC)K,Student,Couse,SC,17,一：关系代数-专门的关系运算-例题,例8:查询选修了2号课程的学生的学号Sno(Cno=2(SC)=95001,95002例9:查询至少选修了一门其直接先行课程为5号课程的学生姓名Sname(Cpno=5(Course)SCSno,Sname(Student)Sno,Cno(SC)Cno(Course)Sno,Sname(Student),Student,Couse,SC,18,一：关系代数-专门的关系运算-例题,例10:查询选修了全部课程的学生号码和姓名Sno,Cno(SC)Cno(Course)Sno,Sname(Student),Couse,SC,Student,19,二：SQL,1、SQL概述StructuredQueryLanguage:通用的、功能极强的关系数据库语言特点：(1).综合统一(2).高度非过程化(3).面向集合的操作方式(4).以同一种语法结构提供两种使用方式(5).语言简捷、易学易用,20,二：SQL,数据查询SELECT数据定义CREATE、DROP数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE,21,二：SQL-数据查询,SELECTFROMWHEREGROUPBYHAVING内部函数表达式ORDERBYASC或DESC,22,二：SQL-数据操纵,1、UPDATEUPDATESET=，WHERE2、INSERTINSERTINTO(列名,)VALUES(表达式或函数,),23,二：SQL-数据操纵,2、INSERT（续）INSERTINTOSQL子查询3、DELETEDELETEFROMWHERE,24,二：SQL-实例-查询,例1：查询全体学生的学号和姓名例2：查询全体学生的姓名、学号、所在系,Student,Couse,SC,SELECTSno,SnameFROMStudent,SELECTSname,Sno,SdeptFROMStudent,25,二：SQL-实例-查询,例3：查询全体学生的详细记录例4：查询全体学生的姓名及其出生年份,Student,Couse,SC,SELECT*FROMStudent,SELECTSname,2004-SageFROMStudent,26,二：SQL-实例-查询,例5：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名例6：查询选修了课程的学生的学号,Couse,SC,Student,SELECTSname,YearofBirth,2004-Sage,LOWER(Sdept)FROMStudent,SELECTSnoFROMSC,SELECTDISTINCTSnoFROMSC,27,二：SQL-实例-查询,例7：查询计算机系全体学生的名单例8：查询所有年龄在20岁以下的学生姓名及其年龄,Couse,SC,Student,SELECTSnameFROMStudentWHERESdept=CS,SELECTSname,SageFROMStudentWHERESage=20,28,二：SQL-实例-查询,例9：查询考试成绩有不及格的学生的学号例10：查询年龄在20-23岁之间的学生的姓名、系别和年龄,Couse,SC,Student,SELECTDISTINCTSnoFROMSCWHEREGrade90,43,二：SQL-实例-查询,例36:查询每个学生的学号、姓名、选修的课程名及成绩,Couse,SC,Student,SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno,44,二：SQL-实例-查询,例37:查询与“刘晨”在同一个系学习的学生的学号、姓名、所在系,Couse,SC,Student,SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨),可用连接实现,45,二：SQL-实例-查询,例38:查询选修了课程名为信息系统的学生的学号和姓名,Couse,SC,Student,SELECTSno,SnameFROMStudentWHERECnoIN(SELECTCnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=信息系统),可用连接实现,46,二：SQL-实例-查询,例37改写,Couse,SC,Student,SELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=刘晨),47,二：SQL-实例-查询,例39：查询其他系中比信息系某一学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECTSname,SageFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept=IS),48,二：SQL-实例-查询,例40：查询其他系中比信息系所有学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECTSname,SageFROMStudentWHERESageALL(SELECTSageFROMStudentWHERESdept=IS),49,二：SQL-实例-查询,例41：查询所有选修了1号课程的学生姓名,Couse,SC,Student,SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1),50,二：SQL-实例-查询,例42：查询没有选修了1号课程的学生姓名,Couse,SC,Student,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1),51,二：SQL-实例-查询,例43：查询选修了全部课程的学生姓名,Couse,SC,Student,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),52,二：SQL-实例-查询,例44：查询至少选修了学生95002选修的全部课程的学生号码,Couse,SC,Student,SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=95002ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno),53,二：SQL-实例-数据更新,例1：将一个新学生记录(学号:95020,姓名:陈东,性别:男,所在系:IS,年龄:18)插入到Student表中例2:插入一条选课记录(95020,1),Couse,SC,Student,INSERTINTOStudentVALUES(95020,陈东,男,IS,18),INSERTINTOSC(Sno,Cno)VALUES(95020,1),54,二：SQL-实例-数据更新,例3：对每一个系,求学生的平均年龄,并把结果存入数据库,Couse,SC,Student,CREATETABLEDeptage(SdeptCHAR(15),AvgageSMALLINT),INSERTINTODeptage(Sdept,Avgage)SELECTSdept,AVG(Sage)FROMStudentGROUPBYSdept,55,二：SQL-实例-数据更新,例4：将学生95001的年龄改为22岁例5:将所有的学生年龄增加1岁,Couse,SC,Student,UPDATEStudentSETSage=22WHERESno=95001,UPDATEStudentSETSage=Sage+1,56,二：SQL-实例-数据更新,例6：将计算机科学系全体学生的成绩置零,Couse,SC,Student,UPDATESCSETGrade=0WHERECS=(SELECTSd