关系运算与SQL实例

关系运算与SQL实例1关系运算与SQL实例一：关系代数-传统的集合运算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}2关系运算与SQL实例一：关系代数-传统的集合运算4、广义笛卡儿积(ExtendedCartesianProduct)R×S={trts|tr∈R∧ts∈S}RS3关系运算与SQL实例一：关系代数-传统的集合运算运算结果R∪SR∩SR-S4关系运算与SQL实例一：关系代数-传统的集合运算运算结果R×S5关系运算与SQL实例一：关系代数-专门的关系运算专业标记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}后剩余的属性组6关系运算与SQL实例一：关系代数-专门的关系运算专业标记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上分量的集合7关系运算与SQL实例一：关系代数-专门的关系运算专门的关系运算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)8关系运算与SQL实例一：关系代数-专门的关系运算-例StudentCouseSC9关系运算与SQL实例一：关系代数-专门的关系运算-例StudentCouseSC10关系运算与SQL实例一：关系代数-专门的关系运算专门的关系运算2、投影(Projection)从R中选择若干个属性列组成新的关系。记作：πA(R)={t[A]|t∈R},A是R中的属性列例3：查询学生的姓名和所在的系πSname,Sdept(Student)π2,5(Student)例4：查询学生关系Student中有哪些系πSdept(Student)11关系运算与SQL实例一：关系代数-专门的关系运算专门的关系运算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=B12关系运算与SQL实例一：关系代数-专门的关系运算-连接例题RSRSC<ERSR.B=S.BRS13关系运算与SQL实例一：关系代数-专门的关系运算专门的关系运算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]14关系运算与SQL实例一：关系代数-专门的关系运算-除例题RSR÷S分析:在关系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)}15关系运算与SQL实例一：关系代数-专门的关系运算-例题例7:查询至少选修了1号课程和3号课程的学生号码先建立临时关系K:πSno,Cno(SC)÷KStudentCouseSC16关系运算与SQL实例一：关系代数-专门的关系运算-例题例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)StudentCouseSC17关系运算与SQL实例一：关系代数-专门的关系运算-例题例10:查询选修了全部课程的学生号码和姓名πSno,Cno(SC)÷πCno(Course)πSno,Sname(Student)CouseSCStudent18关系运算与SQL实例二：SQL1、SQL概述StructuredQueryLanguage:通用的、功能极强的关系数据库语言特点：(1).综合统一(2).高度非过程化(3).面向集合的操作方式(4).以同一种语法结构提供两种使用方式(5).语言简捷、易学易用19关系运算与SQL实例二：SQL数据查询SELECT数据定义CREATE、DROP数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE20关系运算与SQL实例二：SQL-数据查询SELECT<列名表>FROM<基本表名或视图名>[WHERE<条件表达式>][GROUPBY<列名1>][HAVING内部函数表达式][ORDERBY<列名2>[ASC或DESC]]21关系运算与SQL实例二：SQL-数据操纵1、UPDATE UPDATE<基本表或视图> SET<列名1>=<函数或表达式>[，……] WHERE<条件表达式>2、INSERT INSERTINTO<基本表或视图>(列名,……) VALUES(表达式或函数,……)22关系运算与SQL实例二：SQL-数据操纵2、INSERT（续）INSERTINTO<基本表或视图>SQL子查询3、DELETEDELETEFROM<基本表或视图>WHERE<条件表达式>23关系运算与SQL实例二：SQL-实例-查询例1：查询全体学生的学号和姓名例2：查询全体学生的姓名、学号、所在系StudentCouseSCSELECTSno,SnameFROMStudentSELECTSname,Sno,SdeptFROMStudent24关系运算与SQL实例二：SQL-实例-查询例3：查询全体学生的详细记录例4：查询全体学生的姓名及其出生年份StudentCouseSCSELECT*FROMStudentSELECTSname,2004-SageFROMStudent25关系运算与SQL实例二：SQL-实例-查询例5：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名例6：查询选修了课程的学生的学号CouseSCStudentSELECTSname,’YearofBirth’,2004-Sage,LOWER(Sdept)FROMStudentSELECTSnoFROMSCSELECT

DISTINCTSnoFROMSC26关系运算与SQL实例二：SQL-实例-查询例7：查询计算机系全体学生的名单例8：查询所有年龄在20岁以下的学生姓名及其年龄CouseSCStudentSELECTSnameFROMStudentWHERESdept=‘CS’SELECTSname,SageFROMStudentWHERESage<20SELECTSname,SageFROMStudentWHERE

NOTSage>=2027关系运算与SQL实例二：SQL-实例-查询例9：查询考试成绩有不及格的学生的学号例10：查询年龄在20-23岁之间的学生的姓名、系别和年龄CouseSCStudentSELECT

DISTINCTSnoFROMSCWHEREGrade<60SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23SELECTSname,Sdept,SageFROMStudentWHERESage<=23ANDSage>=2028关系运算与SQL实例二：SQL-实例-查询例11：查询年龄在20-23岁之间的学生的姓名、系别和年龄例12：查询信息系(IS),数学系(MA),和计算机科学系(CS)学生的姓名和性别CouseSCStudentSELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23SELECTSname,SsexFROMStudentWHERESdeptIN(‘IS’,’MA’,’CS’)29关系运算与SQL实例二：SQL-实例-查询例13：查询不是信息系,数学系,和计算机科学系学生的姓名和性别例14：查询学号为95001的学生的详细情况CouseSCStudentSELECTSname,SsexFROMStudentWHERESdeptNOTIN(‘IS’,’MA’,’CS’)SELECT*FROMStudentWHERESnoLIKE

‘95001’SELECT*FROMStudentWHERESno=‘95001’30关系运算与SQL实例二：SQL-实例-查询例15：查询所有姓刘的学生的姓名、学号和性别例16：查询姓‘欧阳’且全名为三个汉字的学生的姓名CouseSCStudentSELECTSname,Sno,SsexFROMStudentWHERESnameLIKE

‘刘%’SELECTSnameFROMStudentWHERESnameLIKE

‘欧阳__’31关系运算与SQL实例二：SQL-实例-查询例17：查询名字中第2个字为‘阳’字的学生的姓名和学号例18：查询所有不姓刘的学生的姓名CouseSCStudentSELECTSname,SnoFROMStudentWHERESnameLIKE

‘__阳%’SELECTSnameFROMStudentWHERESnameNOTLIKE

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

CouseSCStudentSELECTCno,CcreditFROMCourseWHERECnameLIKE

‘DB\_Design’ESCAPE

‘\’SELECT*FROMCourseWHERECnameLIKE

‘DB\_%i__’ESCAPE

‘\’33关系运算与SQL实例二：SQL-实例-查询例21：查询缺少成绩的学生的学号和课程号例22：查询所有有成绩的学生的学号和课程号CouseSCStudentSELECTSno,CnoFROMSCWHEREGradeISNULLSELECTSno,CnoFROMSCWHEREGradeISNOTNULL34关系运算与SQL实例二：SQL-实例-查询例23：查询计算机系年龄在20岁以下的学生姓名例12改写：CouseSCStudentSELECTSnameFROMStudentWHERESdept=‘is’

ANDSage<20SELECTSname,SsexFROMStudentWHERESdept=‘IS’

ORSdept=‘MA’

ORSdept=‘CS’35关系运算与SQL实例二：SQL-实例-查询例24：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列例25：查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列CouseSCStudentSELECTSno,GradeFROMSCWHERECno=‘3’

ORDERBYGradeDESCSELECT*FROMStudentORDERBYSdept,SageDESC36关系运算与SQL实例二：SQL-实例-查询例26：查询学生总人数例27：查询选修了课程的学生人数CouseSCStudentSELECT

COUNT(*)FROMStudentSELECT

COUNT(DISTINCTSno)FROMSC37关系运算与SQL实例二：SQL-实例-查询例28：计算1号课程的学生平均成绩例29：查询选修了1号课程的学生最高分数CouseSCStudentSELECT

AVG(Grade)FROMSCWHERECno=‘1’SELECT

MAX(Grade)FROMSCWHERECno=‘1’38关系运算与SQL实例二：SQL-实例-查询例30：求各个课程号及相应的选课人数例31：查询选修了3门以上课程的学生学号CouseSCStudentSELECTCno,COUNT(Sno)FROMSCGROUPBYCno SELECTSnoFROMSCGROUPBYSnoHAVING

COUNT(*)>339关系运算与SQL实例二：SQL-实例-查询例32：查询每个学生及其选修课程的情况例33：改写CouseSCStudentSELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.SnoSELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno40关系运算与SQL实例二：SQL-实例-查询例34:查询每门课程的间接先行课CouseSCStudentCouseFIRSTCouseSECONDSELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno41关系运算与SQL实例二：SQL-实例-查询例35:查询选修了2号课程且成绩在90分以上的所有学生的学号和姓名CouseSCStudentSELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’

ANDSC.Grade>9042关系运算与SQL实例二：SQL-实例-查询例36:查询每个学生的学号、姓名、选修的课程名及成绩CouseSCStudentSELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno43关系运算与SQL实例二：SQL-实例-查询例37:查询与“刘晨”在同一个系学习的学生的学号、姓名、所在系CouseSCStudentSELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdept

FROMStudent

WHERESname=‘刘晨’)可用连接实现44关系运算与SQL实例二：SQL-实例-查询例38:查询选修了课程名为‘信息系统’的学生的学号和姓名CouseSCStudentSELECTSno,SnameFROMStudentWHERECnoIN(SELECTCno

FROMSC

WHERECnoIN(SELECTCno

FROMCourse

WHERECname=‘信息系统’)可用连接实现45关系运算与SQL实例二：SQL-实例-查询例37改写CouseSCStudentSELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdept

FROMStudent

WHERESname=‘刘晨’)46关系运算与SQL实例二：SQL-实例-查询例39：查询其他系中比信息系某一学生年龄小的学生的姓名和年龄CouseSCStudentSELECTSname,SageFROMStudentWHERESage<ANY(SELECTSage

FROMStudent

WHERESdept=‘’IS)47关系运算与SQL实例二：SQL-实例-查询例40：查询其他系中比信息系所有学生年龄小的学生的姓名和年龄CouseSCStudentSELECTSname,SageFROMStudentWHERESage<ALL(SELECTSage

FROMStudent

WHERESdept=‘’IS)48关系运算与SQL实例二：SQL-实例-查询例41：查询所有选修了1号课程的学生姓名CouseSCStudentSELECTSnameFROMStudentWHEREEXISTS

(SELECT*

FROMSC

WHERESno=Student.SnoANDCno=‘1’)49关系运算与SQL实例二：SQL-实例-查询例42：查询没有选修了1号课程的学生姓名CouseSCStudentSELECTSnameFROMStudentWHERENOT

EXISTS

(SELECT*

FROMSC

WHERESno=Student.SnoANDCno=‘1’)50关系运算与SQL实例二：SQL-实例-查询例43：查询选修了全部课程的学生姓名CouseSCStudentSELECTSnameFROMStudentWHERENOT

EXISTS

(SELECT*

FROMCourse

WHERE

NOT

EXISTS

(SELECT*FROMSCWHERESno=Student.Sno

AND

Cno=Course.Cno))51关系运算与SQL实例二：SQL-实例-查询例44：查询至少选修了学生95002选修的全部课程的学生号码CouseSCStudentSELECTDISTINCTSnoFROMSCSCXWHERENOT

EXISTS

(SELECT*

FROMSCSCY

WHERESCY.Sno=‘95002’

ANDNOTEXISTS

(SELECT*FROMSCSCZ

WHERE

SCZ.Sno=SCX.Sno

AND

SCZ.Cno=SCY.Cno))52关系运算与SQL实例二：SQL-实例-数据更新例1：将一个新学生记录(学号:95020,姓名:陈东,性别:男,所在系