关系运算与SQL实例公开课一等奖省优质课大赛获奖课件

1关系运算与SQL实例金秋乐2一：关系代数-传统集合运算1、并(Union)R∪S={t|t∈R∨t∈S}2、交(Intersection)R∩S={t|t∈R∧t∈S}3、差(Difference)R-S={t|t∈R∧t∈S}3一：关系代数-传统集合运算4、广义笛卡儿积(ExtendedCartesianProduct)R×S={trts|tr∈R∧ts∈S}ABCa1b1c1a1b2c2a2b2c1RABCa1b2c2a1b3c2a2b2c1S4一：关系代数-传统集合运算运算结果ABCa1b1c1a1b2c2a2b2c1a1b3c2R∪SABCa1b2c2a2b2c1R∩SABCa1b1c1R-S5一：关系代数-传统集合运算运算结果R×SABCABCa1b1C1a1b2C2a1b1C1a1b3C2a1b1C2a2b2C1a1b2C2a1b2C2a1b2C2a1b3C2a1b2C2a2b2C1a2b2C1a1b2C2a2b2C1a1b3C2a2b2C1a2b2C16一：关系代数-专门关系运算专业标识1、设关系模式为R(A1,A2,…,An).它一个关系设为R。t∈R表示t是R一个元组。t[Ai]表示元组t中对应于属性Ai一个分量2、若A={Ai1,Ai2,…,Aik},其中Ai1,Ai2,…,Aik是A1,A2,…,An中一部分，则A称为属性列或域列。t[A]=(t[Ai1],t[Ai2],…,t[Aik])表示元组t在属性列A上诸分量集合。A则表示{A1,A2,…,An}中去掉{Ai1,Ai2,…,Aik}后剩下属性组7一：关系代数-专门关系运算专业标识3、R为n目关系。S为m目关系。tr∈R，ts∈S,trts称为元组连接(Concatenation).它是一个n+m列元组，前n个分量为R中一个n元组，后m个分量为S中一个m元组4、给定一个关系R(X,Z)，X和Z为属性组。定义：当t[X]=x时，x在R上象集(ImageSet)为：Zx={t[Z]|t∈R,t[X]=x}，它表示R中属性组X上值为x诸元组在Z上分量集合8一：关系代数-专门关系运算专门关系运算1、选择(Selection)又称为限制(Restriction),在关系R中选择满足给定条件诸元组，记作σF(R)={t|t∈R∧F(t)=‘真’}例1、查询信息系(IS系)全体学生σSdept=‘IS’(Student)σ5=‘IS’(Student)例2、查询年纪小于20岁学生σSage<20(Student)σ4<20(Student)9一：关系代数-专门关系运算-例学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王民女18MA95004张力男19ISStudent课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL84Couse学号Sno课程号Cno成绩Grade9500119295001285950013889500229095002380SC10一：关系代数-专门关系运算-例学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeStudentCouseSC11一：关系代数-专门关系运算专门关系运算2、投影(Projection)从R中选择若干个属性列组成新关系。记作：πA(R)={t[A]|t∈R},A是R中属性列例3：查询学生姓名和所在系πSname,Sdept(Student)π2,5(Student)例4：查询学生关系Student中有哪些系πSdept(Student)12一：关系代数-专门关系运算专门关系运算3、连接(Join)又称θ连接。从两个关系笛卡儿积中选取属性间满足一定条件元组。记作；RS={trts|tr∈

R∧ts∈

S∧tr[A]θts[B]}A和B分别为R和S上度数相等且可比属性组.θ为比较运算符特殊连接:等值连接:RS={trts|tr∈

R∧ts∈

S∧tr[A]=ts[B]}自然连接:将等值连接中重复属性列去掉,记作:RS={trts|tr∈

R∧ts∈

S∧tr[A]=ts[B]}AθBA=B13一：关系代数-专门关系运算-连接例题ABCa1b15a1b26a2b38a2b412RBEb13b27b310b32b52SAR.BCS.BEa1b15b27a1b15b310a1b26b27a1b26b310a2b38b310AR.BCS.BEa1b15b13a1b26b27a2b38b310a2b38b32ABCEa1b153a1b267a2b3810a2b382RSC<ERSR.B=S.BRS14一：关系代数-专门关系运算专门关系运算4、除(Division)R(X,Y)与S(Y,Z)除运算得到一个新关系P(X),P是R中满足以下条件元组在X属性列上投影:元组在X上分量值x象集Yx包含S在Y上投影集合,记作:R÷S={tr[X]|tr∈R∧πy(S)Yx}其中Yx为x在R中象集,x=tr[X]15一：关系代数-专门关系运算-除例题ABCa1b1c2a2b3c7a3b4c6a1b2c3a4b6c6a2b2c3a1b2c1RBCDb1c2d1b2c1d1b2c3d2SR÷SAa1分析:在关系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学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeStudentCouseSCCno1317一：关系代数-专门关系运算-例题例8:查询选修了2号课程学生学号πSno(σCno=‘2’(SC))={95001,95002}例9:查询最少选修了一门其直接先行课程为5号课程学生姓名πSname(σCpno=‘5’(Course)SCπSno,Sname(Student)πSno,Cno(SC)÷πCno(Course)πSno,Sname(Student)学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeStudentCouseSC18一：关系代数-专门关系运算-例题例10:查询选修了全部课程学生号码和姓名πSno,Cno(SC)÷πCno(Course)πSno,Sname(Student)学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudent19二：SQL1、SQL概述StructuredQueryLanguage:通用、功效极强关系数据库语言特点：(1).综合统一(2).高度非过程化(3).面向集合操作方式(4).以同一个语法结构提供两种使用方式(5).语言简捷、易学易用20二：SQL数据查询SELECT数据定义CREATE、DROP数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE21二：SQL-数据查询SELECT<列名表>FROM<基本表名或视图名>[WHERE<条件表示式>][GROUPBY<列名1>][HAVING内部函数表示式][ORDERBY<列名2>[ASC或DESC]]22二：SQL-数据操纵1、UPDATE UPDATE<基本表或视图> SET<列名1>=<函数或表示式>[，……] WHERE<条件表示式>2、INSERT INSERTINTO<基本表或视图>(列名,……) VALUES(表示式或函数,……)23二：SQL-数据操纵2、INSERT（续）INSERTINTO<基本表或视图>SQL子查询3、DELETEDELETEFROM<基本表或视图>WHERE<条件表示式>24二：SQL-实例-查询例1：查询全体学生学号和姓名例2：查询全体学生姓名、学号、所在系学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeStudentCouseSCSELECTSno,SnameFROMStudentSELECTSname,Sno,SdeptFROMStudent25二：SQL-实例-查询例3：查询全体学生详细统计例4：查询全体学生姓名及其出生年份学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeStudentCouseSCSELECT*FROMStudentSELECTSname,-SageFROMStudent26二：SQL-实例-查询例5：查询全体学生姓名、出生年份和所在系，要求用小写字母表示所在系名例6：查询选修了课程学生学号学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSname,’YearofBirth’,-Sage,LOWER(Sdept)FROMStudentSELECTSnoFROMSCSELECT

DISTINCTSnoFROMSC27二：SQL-实例-查询例7：查询计算机系全体学生名单例8：查询全部年纪在20岁以下学生姓名及其年纪学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSnameFROMStudentWHERESdept=‘CS’SELECTSname,SageFROMStudentWHERESage<20SELECTSname,SageFROMStudentWHERE

NOTSage>=2028二：SQL-实例-查询例9：查询考试成绩有不及格学生学号例10：查询年纪在20-23岁之间学生姓名、系别和年纪学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECT

DISTINCTSnoFROMSCWHEREGrade<60SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23SELECTSname,Sdept,SageFROMStudentWHERESage<=23ANDSage>=2029二：SQL-实例-查询例11：查询年纪在20-23岁之间学生姓名、系别和年纪例12：查询信息系(IS),数学系(MA),和计算机科学系(CS)学生姓名和性别学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23SELECTSname,SsexFROMStudentWHERESdeptIN(‘IS’,’MA’,’CS’)30二：SQL-实例-查询例13：查询不是信息系,数学系,和计算机科学系学生姓名和性别例14：查询学号为95001学生详细情况学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSname,SsexFROMStudentWHERESdeptNOTIN(‘IS’,’MA’,’CS’)SELECT*FROMStudentWHERESnoLIKE

‘95001’SELECT*FROMStudentWHERESno=‘95001’31二：SQL-实例-查询例15：查询全部姓刘学生姓名、学号和性别例16：查询姓‘欧阳’且全名为三个汉字学生姓名学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSname,Sno,SsexFROMStudentWHERESnameLIKE

‘刘%’SELECTSnameFROMStudentWHERESnameLIKE

‘欧阳__’32二：SQL-实例-查询例17：查询名字中第2个字为‘阳’字学生姓名和学号例18：查询全部不姓刘学生姓名学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSname,SnoFROMStudentWHERESnameLIKE

‘__阳%’SELECTSnameFROMStudentWHERESnameNOTLIKE

‘刘%’33二：SQL-实例-查询例19：查询DB_Design课程课程号和学分例20：查询以‘DB_’开头，且倒数第3个字符为i课程详细情况

学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTCno,CcreditFROMCourseWHERECnameLIKE

‘DB\_Design’ESCAPE

‘\’SELECT*FROMCourseWHERECnameLIKE

‘DB\_%i__’ESCAPE

‘\’34二：SQL-实例-查询例21：查询缺乏成绩学生学号和课程号例22：查询全部有成绩学生学号和课程号学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSno,CnoFROMSCWHEREGradeISNULLSELECTSno,CnoFROMSCWHEREGradeISNOTNULL35二：SQL-实例-查询例23：查询计算机系年纪在20岁以下学生姓名例12改写：学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSnameFROMStudentWHERESdept=‘is’

ANDSage<20SELECTSname,SsexFROMStudentWHERESdept=‘IS’

ORSdept=‘MA’

ORSdept=‘CS’36二：SQL-实例-查询例24：查询选修了3号课程学生学号及其成绩，查询结果按分数降序排列例25：查询全体学生情况，查询结果按所在系系号升序排列，同一系中学生按年纪降序排列学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSno,GradeFROMSCWHERECno=‘3’

ORDERBYGradeDESCSELECT*FROMStudentORDERBYSdept,SageDESC37二：SQL-实例-查询例26：查询学生总人数例27：查询选修了课程学生人数学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECT

COUNT(*)FROMStudentSELECT

COUNT(DISTINCTSno)FROMSC38二：SQL-实例-查询例28：计算1号课程学生平均成绩例29：查询选修了1号课程学生最高分数学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECT

AVG(Grade)FROMSCWHERECno=‘1’SELECT

MAX(Grade)FROMSCWHERECno=‘1’39二：SQL-实例-查询例30：求各个课程号及对应选课人数例31：查询选修了3门以上课程学生学号学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTCno,COUNT(Sno)FROMSCGROUPBYCno SELECTSnoFROMSCGROUPBYSnoHAVING

COUNT(*)>340二：SQL-实例-查询例32：查询每个学生及其选修课程情况例33：改写学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.SnoSELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno41二：SQL-实例-查询例34:查询每门课程间接先行课学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudent课程号Cno课程名Cname先行课Cpno学分Ccredit课程号Cno课程名Cname先行课Cpno学分CcreditCouseFIRSTCouseSECONDSELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno42二：SQL-实例-查询例35:查询选修了2号课程且成绩在90分以上全部学生学号和姓名学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’

ANDSC.Grade>9043二：SQL-实例-查询例36:查询每个学生学号、姓名、选修课程名及成绩学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno44二：SQL-实例-查询例37:查询与“刘晨”在同一个系学习学生学号、姓名、所在系学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdept

FROMStudent

WHERESname=‘刘晨’)可用连接实现45二：SQL-实例-查询例38:查询选修了课程名为‘信息系统’学生学号和姓名学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSno,SnameFROMStudentWHERECnoIN(SELECTCno

FROMSC

WHERECnoIN(SELECTCno

FROMCourse

WHERECname=‘信息系统’)可用连接实现46二：SQL-实例-查询例37改写学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdept

FROMStudent

WHERESname=‘刘晨’)47二：SQL-实例-查询例39：查询其它系中比信息系某一学生年纪小学生姓名和年纪学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSname,SageFROMStudentWHERESage<ANY(SELECTSage

FROMStudent

WHERESdept=‘’IS)48二：SQL-实例-查询例40：查询其它系中比信息系全部学生年纪小学生姓名和年纪学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSname,SageFROMStudentWHERESage<ALL(SELECTSage

FROMStudent

WHERESdept=‘’IS)49二：SQL-实例-查询例41：查询全部选修了1号课程学生姓名学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSnameFROMStudentWHEREEXISTS

(SELECT*

FROMSC

WHERESno=Student.SnoANDCno=‘1’)50二：SQL-实例-查询例42：查询没有选修了1号课程学生姓名学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSnameFROMStudentWHERENOT

EXISTS

(SELECT*

FROMSC

WHERESno=Student.SnoANDCno=‘1’)51二：SQL-实例-查询例43：查询选修了全部课程学生姓名学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTSnameFROMStudentWHERENOT

EXISTS

(SELECT*

FROMCourse

WHERE

NOT

EXISTS

(SELECT*FROMSCWHERESno=Student.Sno

AND

Cno=Course.Cno))52二：SQL-实例-查询例44：查询最少选修了学生95002选修全部课程学生号码学号Sno姓名Sname性别Ssex年纪Sage所在系Sdept课程号Cno课程名Cname先行课Cpno学分Ccredit学号Sno课程号Cno成绩GradeCouseSCStudentSELECTDISTINCTSnoFROMSCSCXWHERENOT

EXISTS

(SELECT*

FROMSCSCY

WHERESCY.Sno=‘95002’

ANDNOTEXISTS

(SELECT*FROMSCSCZ

WHERE

SCZ.Sno=SCX.Sno

AND

SCZ.Cno=SCY.Cno))53二：SQL-实例-数据更新例1：将一个新学生统计(学号:95020,姓名:陈东,性别:男,所在系:IS,年纪:18)插入到Student表中例2:插入一条选课统计(‘95020’,‘1’)学号Sno姓名Sname性别Ss