关系运算与SQL实例.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,关系运算与SQL实例,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,关系运算与SQL实例,*,关系运算与,SQL,实例,1,关系运算与SQL实例,一：关系代数,-,传统的集合运算,1,、并,(Union),RS=t|tRt S,2,、交,(Intersection),RS=t|tRt S,3,、差,(Difference),R-S=t|tRt S,2,关系运算与SQL实例,一：关系代数,-,传统的集合运算,4,、广义笛卡儿积,(Extended Cartesian Product),RS=t,r,t,s,|t,r,Rt,s,S,R,S,3,关系运算与SQL实例,一：关系代数,-,传统的集合运算,运算结果,RS,RS,R-S,4,关系运算与SQL实例,一：关系代数,-,传统的集合运算,运算结果,RS,5,关系运算与SQL实例,一：关系代数,-,专门的关系运算,专业标记,1,、设关系模式为,R(A,1,A,2,A,n,).,它的一个关系设为,R,。,tR,表示,t,是,R,的一个元组。,tA,i,表示元组,t,中相应于属性,Ai,的一个分量,2,、若,A=A,i1,A,i2,A,ik,其中,A,i1,A,i2,A,ik,是,A,1,A,2,A,n,中的一部分，则,A,称为属性列或域列。,tA=(tA,i1,tA,i2,tA,ik,),表示元组,t,在属性列,A,上诸分量的集合。,A,则表示,A,1,A,2,A,n,中去掉,A,i1,A,i2,A,ik,后剩余的属性组,6,关系运算与SQL实例,一：关系代数,-,专门的关系运算,专业标记,3,、,R,为,n,目关系。,S,为,m,目关系。,t,r,R,，,t,s,S,t,r,t,s,称为元组的连接,(Concatenation).,它是一个,n+m,列的元组，前,n,个分量为,R,中的一个,n,元组，后,m,个分量为,S,中的一个,m,元组,4,、给定一个关系,R(X,Z),，,X,和,Z,为属性组。定义：当,tX=x,时，,x,在,R,上的象集,(Image Set),为：,Zx=tZ|t R,tX=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,岁的学生,Sage20,(Student),420,(Student),8,关系运算与SQL实例,一：关系代数,-,专门的关系运算,-,例,Student,Couse,SC,9,关系运算与SQL实例,一：关系代数,-,专门的关系运算,-,例,Student,Couse,SC,10,关系运算与SQL实例,一：关系代数,-,专门的关系运算,专门的关系运算,2,、投影,(Projection),从,R,中选择若干个属性列组成新的关系。记作：,A,(R)=tA|t R,A,是,R,中的属性列,例,3,：查询学生的姓名和所在的系,Sname,Sdept,(Student),2,5,(Student),例,4,：查询学生关系,Student,中有哪些系,Sdept,(Student),11,关系运算与SQL实例,一：关系代数,-,专门的关系运算,专门的关系运算,3,、连接,(Join),又称,连接。从两个关系的笛卡儿积中选取属性间满足一定条件的元组。记作；,R S=t,r,t,s,|t,r,R,t,s,S,t,r,A t,s,B,A,和,B,分别为,R,和,S,上度数相等且可比的属性组,.,为比较运算符,特殊的连接,:,等值连接,:R S=t,r,t,s,|t,r,R,t,s,S,t,r,A=t,s,B,自然连接,:,将等值连接中重复的属性列去掉,记作,:,R S=t,r,t,s,|t,r,R,t,s,S,t,r,A=t,s,B,AB,A=B,12,关系运算与SQL实例,一：关系代数,-,专门的关系运算,-,连接例题,R,S,R S,CE,R S,R.B=S.B,R S,13,关系运算与SQL实例,一：关系代数,-,专门的关系运算,专门的关系运算,4,、除,(Division),R(X,Y),与,S(Y,Z),的除运算得到一个新的关系,P(X),P,是,R,中满足下列条件的元组在,X,属性列上的投影,:,元组在,X,上分量值,x,的象集,Yx,包含,S,在,Y,上投影的集合,记作,:,RS=t,r,X|t,r,R,y,(S)Yx,其中,Yx,为,x,在,R,中的象集,x=t,r,X,14,关系运算与SQL实例,一：关系代数,-,专门的关系运算,-,除例题,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),15,关系运算与SQL实例,一：关系代数,-,专门的关系运算,-,例题,例,7:,查询至少选修了,1,号课程和,3,号课程的学生号码,先建立临时关系,K:,Sno,Cno,(SC)K,Student,Couse,SC,16,关系运算与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),Student,Couse,SC,17,关系运算与SQL实例,一：关系代数,-,专门的关系运算,-,例题,例,10:,查询选修了全部课程的学生号码和姓名,Sno,Cno,(SC),Cno,(Course),Sno,Sname,(Student),Couse,SC,Student,18,关系运算与SQL实例,二：,SQL,1,、,SQL,概述,Structured Query Language:,通用的、功能极强的关系数据库语言,特点：,(1).,综合统一,(2).,高度非过程化,(3).,面向集合的操作方式,(4).,以同一种语法结构提供两种使用方式,(5).,语言简捷、易学易用,19,关系运算与SQL实例,二：,SQL,数据查询,SELECT,数据定义,CREATE,、,DROP,数据操纵,INSERT,、,UPDATE,、,DELETE,数据控制,GRANT,、,REVOKE,20,关系运算与SQL实例,二：,SQL-,数据查询,SELECT ,FROM,WHERE ,GROUP BY HAVING,内部函数表达式,ORDER BY ASC,或,DESC,21,关系运算与SQL实例,二：,SQL-,数据操纵,1,、,UPDATE,UPDATE,SET=,，,WHERE,2,、,INSERT,INSERT INTO(,列名,),VALUES(,表达式或函数,),22,关系运算与SQL实例,二：,SQL-,数据操纵,2,、,INSERT,（续）,INSERT INTO,SQL,子查询,3,、,DELETE,DELETE,FROM,WHERE,23,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,1,：查询全体学生的学号和姓名,例,2,：查询全体学生的姓名、学号、所在系,Student,Couse,SC,SELECT,Sno,Sname,FROM,Student,SELECT,Sname,Sno,Sdept,FROM,Student,24,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,3,：查询全体学生的详细记录,例,4,：查询全体学生的姓名及其出生年份,Student,Couse,SC,SELECT,*,FROM,Student,SELECT,Sname,2004-Sage,FROM,Student,25,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,5,：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名,例,6,：查询选修了课程的学生的学号,Couse,SC,Student,SELECT,Sname,Year of Birth,2004-Sage,LOWER,(Sdept),FROM,Student,SELECT,Sno,FROM,SC,SELECT,DISTINCT,Sno,FROM,SC,26,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,7,：查询计算机系全体学生的名单,例,8,：查询所有年龄在,20,岁以下的学生姓名及其年龄,Couse,SC,Student,SELECT,Sname,FROM,Student,WHERE,Sdept=,CS,SELECT,Sname,Sage,FROM,Student,WHERE,Sage=20,27,关系运算与SQL实例,二：,SQL,-,实例,-,查,询,例,9,：查询考试成绩有不及格的学生的学号,例,10,：查询年龄在,20-23,岁之间的学生的姓名、系别和年龄,Couse,SC,Student,SELECT,DISTINCT,Sno,FROM,SC,WHERE,Grade60,SELECT,Sname,Sdept,Sage,FROM,Student,WHERE,Sage,BETWEEN,20,AND,23,SELECT,Sname,Sdept,Sage,FROM,Student,WHERE,Sage=20,28,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,11,：查询年龄在,20-23,岁之间的学生的姓名、系别和年龄,例,12,：查询信息系,(IS),数学系,(MA),和计算机科学系,(CS),学生的姓名和性别,Couse,SC,Student,SELECT,Sname,Sdept,Sage,FROM,Student,WHERE,Sage,NOT BETWEEN,20,AND,23,SELECT,Sname,Ssex,FROM,Student,WHERE,Sdept,IN,(,IS,MA,CS,),29,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,13,：查询不是信息系,数学系,和计算机科学系学生的姓名和性别,例,14,：查询学号为,95001,的学生的详细情况,Couse,SC,Student,SELECT,Sname,Ssex,FROM,Student,WHERE,Sdept,NOT IN,(,IS,MA,CS,),SELECT,*,FROM,Student,WHERE,Sno,LIKE,95001,SELECT,*,FROM,Student,WHERE,Sno=,95001,30,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,15,：查询所有姓刘的学生的姓名、学号和性别,例,16,：查询姓,欧阳,且全名为三个汉字的学生的姓名,Couse,SC,Student,SELECT,Sname,Sno,Ssex,FROM,Student,WHERE,Sname,LIKE,刘,%,SELECT,Sname,FROM,Student,WHERE,Sname,LIKE,欧阳,_,31,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,17,：查询名字中第,2,个字为,阳,字的学生的姓名和学号,例,18,：查询所有不姓刘的学生的姓名,Couse,SC,Student,SELECT,Sname,Sno,FROM,Student,WHERE,Sname,LIKE,_,阳,%,SELECT,Sname,FROM,Student,WHERE,Sname,NOT LIKE,刘,%,32,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,19,：查询,DB_Design,课程的课程号和学分,例,20,：查询以,DB_,开头，且倒数第,3,个字符为,i,的课程的详细情况,Couse,SC,Student,SELECT,Cno,Ccredit,FROM,Course,WHERE,Cname,LIKE,DB_Design,ESCAPE,SELECT,*,FROM,Course,WHERE,Cname,LIKE,DB_%i_,ESCAPE,33,关系运算与SQL实例,二：,SQL,-,实例,-,查询,例,21,：查询缺少成绩的学生的学号和课程号,例,22,：查询所有有成绩的学生的学号和课程号,Couse,SC,Student,SELECT,Sno,Cno,FROM,SC,WHERE,Grade,IS NULL,SELECT,Sno,Cno,FROM,SC,WHERE,Grade,IS NOT NULL,34,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,23,：查询计算机系年龄在,20,岁以下的学生姓名,例,12,改写：,Couse,SC,Student,SELECT,Sname,FROM,Student,WHERE,Sdept=,is,AND,Sage3,39,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,32,：查询每个学生及其选修课程的情况,例,33,：改写,Couse,SC,Student,SELECT,Student.*,SC.*,FROM,Student,SC,WHERE,Student.Sno=SC.Sno,SELECT,Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade,FROM,Student,SC,WHERE,Student.Sno=SC.Sno,40,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,34:,查询每门课程的间接先行课,Couse,SC,Student,Couse FIRST,Couse SECOND,SELECT,FIRST.Cno,SECOND.Cpno,FROM,Course FIRST,Course SECOND,WHERE,FIRST.Cpno=SECOND.Cno,41,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,35:,查询选修了,2,号课程且成绩在,90,分以上的所有学生的学号和姓名,Couse,SC,Student,SELECT,Student.Sno,Sname,FROM,Student,SC,WHERE,Student.Sno=SC.Sno,AND,SC.Cno=,2,AND,SC.Grade90,42,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,36:,查询每个学生的学号、姓名、选修的课程名及成绩,Couse,SC,Student,SELECT,Student.Sno,Sname,Cname,Grade,FROM,Student,SC,Course,WHERE,Student.Sno=SC.Sno,AND,SC.Cno=Course.Cno,43,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,37:,查询与,“,刘晨,”,在同一个系学习的学生的学号、姓名、所在系,Couse,SC,Student,SELECT,Sno,Sname,Sdept,FROM,Student,WHERE,Sdept,IN,(,SELECT,Sdept,FROM,Student,WHERE,Sname=,刘晨,),可用连接实现,44,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,38:,查询选修了课程名为,信息系统,的学生的学号和姓名,Couse,SC,Student,SELECT,Sno,Sname,FROM,Student,WHERE,Cno,IN,(,SELECT,Cno,FROM,SC,WHERE,Cno,IN,(,SELECT,Cno,FROM,Course,WHERE,Cname=,信息系统,),可用连接实现,45,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,37,改写,Couse,SC,Student,SELECT,Sno,Sname,Sdept,FROM,Student,WHERE,Sdept,=,(,SELECT,Sdept,FROM,Student,WHERE,Sname=,刘晨,),46,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,39,：查询其他系中比信息系某一学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT,Sname,Sage,FROM,Student,WHERE,Sage,ANY,(,SELECT,Sage,FROM,Student,WHERE,Sdept=,IS),47,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,40,：查询其他系中比信息系所有学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT,Sname,Sage,FROM,Student,WHERE,Sage,ALL,(,SELECT,Sage,FROM,Student,WHERE,Sdept=,IS),48,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,41,：查询所有选修了,1,号课程的学生姓名,Couse,SC,Student,SELECT,Sname,FROM,Student,WHERE,EXISTS,(,SELECT,*,FROM,SC,WHERE,Sno=Student.Sno,AND,Cno=,1,),49,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,42,：查询没有选修了,1,号课程的学生姓名,Couse,SC,Student,SELECT,Sname,FROM,Student,WHERE,NOT,EXISTS,(,SELECT,*,FROM,SC,WHERE,Sno=Student.Sno,AND,Cno=,1,),50,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,43,：查询选修了全部课程的学生姓名,Couse,SC,Student,SELECT,Sname,FROM,Student,WHERE,NOT,EXISTS,(,SELECT,*,FROM,Course,WHERE,NOT,EXISTS,(,SELECT,*,FROM SC,WHERE,Sno=Student.Sno,AND,Cno=Course.Cno),51,关系运算与SQL实例,二,：,SQL,-,实例,-,查询,例,44,：查询至少选修了学生,95002,选修的全部课程的学生号码,Couse,SC,Student,SELECT,DISTINCT Sno,FROM,SC SCX,WHERE,NOT,EXISTS,(,SELECT,*,FROM,SC SCY,WHERE,SCY.Sno=,95002,AND NOT EXISTS,(,SELECT,*,FROM SC SCZ,WHERE,SCZ.Sno=SCX.Sno,AND,SCZ.Cno=SCY.Cno),52,关系运算与SQL实例,二,：,SQL,-,实例,-,数据更新,例,1,：将一个新学生记录,(,学号,:95020,姓名,:,陈东,性别,:,男,所在系,:IS,年龄,:18),插入到,Student,表中,例,2:,插入一条选课记录,(,95020,1,),Couse,SC,Student,INSERT INTO,Student,VALUES,(,95020,陈东,男,IS,18),INSERT INTO,SC(Sno,Cno),VALUES,(,95020,1),53,关系运算与SQL实例,二,：,SQL,-,实例,-,数据更新,例,3,：对每一个系,求学生的平均年龄,并把结果存入数据库,Couse,SC,Student,CREATE TABLE,Deptage,(Sdept,CHAR(15),Avgage,SMALLINT,),INSERT INTO,Deptage(Sdept,Avgage),SELECT,Sdept,AVG,(Sage),FROM,Student,GROUP BY,Sdept,54,关系运算与SQL实例,二,：,SQL,-,实例,-,数据更新,例,4,：将学生,95001,的年龄改为,22,岁,例,5:,将所有的学生年龄增加,1,岁,Couse,SC,Student,UPDATE,Student,SET,Sage=22,WHERE,Sno=,95001,UPDATE,Student,SET,Sage=Sage+1,55,关系运算与SQL实例,二,：,SQL,-,实例,-,数据更新,例,6,：将计算机科学系全体学生的成绩置零,Couse,SC,Student,UPDATE,SC,SET,Grade=0,WHERE,CS,=,(,SELECT,Sdept,FROM,Student,WHERE,Student.Sno=SC.Sno),56,关系运算与SQL实例,二,：,SQL,-,实例,-,数据更新,例,7,：删除学号为,95019,的学生记录,例,8:,删除所有学生的选课记录,Couse,SC,Student,DELETE,FROM,Student,WHERE,Sno=,95019,DELETE FROM,SC,57,关系运算与SQL实例,二,：,SQL,-,实例,-,数据更新,例,9,：删除计算机科学系所有学生的选课记录,Couse,SC,Student,DELETE,FROM,SC,WHERE,CS,=,(,SELECT,Sdept,FROM,Student,WHERE,Student.Sno=SC.Sno),58,关系运算与SQL实例,Bye Bye,59,关系运算与SQL实例,