关系运算与SQL实例.ppt

1,关系运算与SQL实例,金秋乐,2,一：关系代数-传统的集合运算,1、并(Union)RS=t|tRt S2、交(Intersection)RS=t|tRt S3、差(Difference)R-S=t|tRt S,3,一：关系代数-传统的集合运算,4、广义笛卡儿积(Extended Cartesian Product)RS=trts|trRts S,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上的象集(Image Set)为：Zx=tZ|t R,tX=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岁的学生Sage20(Student)420(Student),9,一：关系代数-专门的关系运算-例,Student,Couse,SC,10,一：关系代数-专门的关系运算-例,Student,Couse,SC,11,一：关系代数-专门的关系运算,专门的关系运算2、投影(Projection)从R中选择若干个属性列组成新的关系。记作：A(R)=tA|t R,A是R中的属性列例3：查询学生的姓名和所在的系Sname,Sdept(Student)2,5(Student)例4：查询学生关系Student中有哪些系Sdept(Student),12,一：关系代数-专门的关系运算,专门的关系运算3、连接(Join)又称连接。从两个关系的笛卡儿积中选取属性间满足一定条件的元组。记作；R S=trts|tr R ts S trA tsBA和B分别为R和S上度数相等且可比的属性组.为比较运算符特殊的连接:等值连接:R S=trts|tr R ts S trA=tsB自然连接:将等值连接中重复的属性列去掉,记作:R S=trts|tr R ts S trA=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|tr R y(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)SC Sno,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概述Structured Query Language:通用的、功能极强的关系数据库语言特点：(1).综合统一(2).高度非过程化(3).面向集合的操作方式(4).以同一种语法结构提供两种使用方式(5).语言简捷、易学易用,20,二：SQL,数据查询SELECT数据定义CREATE、DROP数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE,21,二：SQL-数据查询,SELECT FROM WHERE GROUP BY HAVING 内部函数表达式ORDER BY ASC 或 DESC,22,二：SQL-数据操纵,1、UPDATEUPDATE SET=，WHERE 2、INSERTINSERT INTO(列名,)VALUES(表达式或函数,),23,二：SQL-数据操纵,2、INSERT（续）INSERT INTO SQL子查询3、DELETEDELETE FROM WHERE,24,二：SQL-实例-查询,例1：查询全体学生的学号和姓名例2：查询全体学生的姓名、学号、所在系,Student,Couse,SC,SELECT Sno,Sname FROM Student,SELECT Sname,Sno,Sdept FROM Student,25,二：SQL-实例-查询,例3：查询全体学生的详细记录例4：查询全体学生的姓名及其出生年份,Student,Couse,SC,SELECT*FROM Student,SELECT Sname,2004-Sage FROM Student,26,二：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,27,二：SQL-实例-查询,例7：查询计算机系全体学生的名单例8：查询所有年龄在20岁以下的学生姓名及其年龄,Couse,SC,Student,SELECT Sname FROM Student WHERE Sdept=CS,SELECT Sname,Sage FROM Student WHERE Sage20,SELECT Sname,Sage FROM Student WHERE NOT Sage=20,28,二：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,29,二：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),30,二：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,31,二：SQL-实例-查询,例15：查询所有姓刘的学生的姓名、学号和性别例16：查询姓欧阳且全名为三个汉字的学生的姓名,Couse,SC,Student,SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘%,SELECT Sname FROM Student WHERE Sname LIKE 欧阳_,32,二：SQL-实例-查询,例17：查询名字中第2个字为阳字的学生的姓名和学号例18：查询所有不姓刘的学生的姓名,Couse,SC,Student,SELECT Sname,Sno FROM Student WHERE Sname LIKE _阳%,SELECT Sname FROM Student WHERE Sname NOT LIKE 刘%,33,二：SQL-实例-查询,例19：查询DB_Design课程的课程号和学分例20：查询以DB_开头，且倒数第3个字符为i 的课程的详细情况,Couse,SC,Student,SELECT Cno,Ccredit FROM Course WHERE Cname LIKE DB_DesignESCAPE,SELECT*FROM Course WHERE Cname LIKE DB_%i_ESCAPE,34,二：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,35,二：SQL-实例-查询,例23：查询计算机系年龄在20岁以下的学生姓名例12改写：,Couse,SC,Student,SELECT Sname FROM Student WHERE Sdept=is AND Sage20,SELECT Sname,Ssex FROM Student WHERE Sdept=IS OR Sdept=MA OR Sdept=CS,36,二：SQL-实例-查询,例24：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列例25：查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列,Couse,SC,Student,SELECT Sno,Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC,SELECT*FROM Student ORDER BY Sdept,Sage DESC,37,二：SQL-实例-查询,例26：查询学生总人数例27：查询选修了课程的学生人数,Couse,SC,Student,SELECT COUNT(*)FROM Student,SELECT COUNT(DISTINCT Sno)FROM SC,38,二：SQL-实例-查询,例28：计算1号课程的学生平均成绩例29：查询选修了1号课程的学生最高分数,Couse,SC,Student,SELECT AVG(Grade)FROM SC WHERE Cno=1,SELECT MAX(Grade)FROM SC WHERE Cno=1,39,二：SQL-实例-查询,例30：求各个课程号及相应的选课人数例31：查询选修了3门以上课程的学生学号,Couse,SC,Student,SELECT Cno,COUNT(Sno)FROM SC GROUP BY Cno,SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)3,40,二：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,41,二：SQL-实例-查询,例34:查询每门课程的间接先行课,Couse,SC,Student,Couse FIRST,Couse SECOND,SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECONDWHERE FIRST.Cpno=SECOND.Cno,42,二：SQL-实例-查询,例35:查询选修了2号课程且成绩在90分以上的所有学生的学号和姓名,Couse,SC,Student,SELECT Student.Sno,Sname FROM Student,SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90,43,二：SQL-实例-查询,例36:查询每个学生的学号、姓名、选修的课程名及成绩,Couse,SC,Student,SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno,44,二：SQL-实例-查询,例37:查询与“刘晨”在同一个系学习的学生的学号、姓名、所在系,Couse,SC,Student,SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname=刘晨),可用连接实现,45,二：SQL-实例-查询,例38:查询选修了课程名为信息系统的学生的学号和姓名,Couse,SC,Student,SELECT Sno,SnameFROM StudentWHERE Cno IN(SELECT Cno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname=信息系统),可用连接实现,46,二：SQL-实例-查询,例37改写,Couse,SC,Student,SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept=(SELECT Sdept FROM Student WHERE Sname=刘晨),47,二：SQL-实例-查询,例39：查询其他系中比信息系某一学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT Sname,SageFROM StudentWHERE Sage ANY(SELECT Sage FROM Student WHERE Sdept=IS),48,二：SQL-实例-查询,例40：查询其他系中比信息系所有学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT Sname,SageFROM StudentWHERE Sage ALL(SELECT Sage FROM Student WHERE Sdept=IS),49,二：SQL-实例-查询,例41：查询所有选修了1号课程的学生姓名,Couse,SC,Student,SELECT Sname FROM StudentWHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=1),50,二：SQL-实例-查询,例42：查询没有选修了1号课程的学生姓名,Couse,SC,Student,SELECT Sname FROM StudentWHERE NOT EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=1),51,二：SQL-实例-查询,例43：查询选修了全部课程的学生姓名,Couse,SC,Student,SELECT Sname FROM StudentWHERE NOT EXISTS(SELECT*FROM Course WHERE NOT EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno),52,二：SQL-实例-查询,例44：查询至少选修了学生95002选修的全部课程的学生号码,Couse,SC,Student,SELECT DISTINCT Sno FROM SC SCXWHERE 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),53,二：SQL-实例-数据更新,例1：将一个新学生记录(学号:95020,姓名:陈东,性别:男,所在系:IS,年龄:18)插入到Student表中例2:插入一条选课记录(95020,1),Couse,SC,Student,INSERT INTO StudentVALUES(95020,陈东,男,IS,18),INSERT INTO SC(Sno,Cno)VALUES(95020,1),54,二：SQL-实例-数据更新,例3：对每一个系,求学生的平均年龄,并把结果存入数据库,Couse,SC,Student,CREATE TABLE Deptage(Sdept CHAR(15),Avgage SMALLINT),INSERT INTO Deptage(Sdept,Avgage)SELECT Sdept,AVG(Sage)FROM StudentGROUP BY Sdept,55,二：SQL-实例-数据更新,例4：将学生95001的年龄改为22岁例5:将所有的学生年龄增加1岁,Couse,SC,Student,UPDATE Student SET Sage=22 WHERE Sno=95001,UPDATE Student SET Sage=Sage+1,56,二：SQL-实例-数据更新,例6：将计算机科学系全体学生的成绩置零,Couse,SC,Student,UPDATE SCSET Grade=0WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno),57,二：SQL-实例-数据更新,例7：删除学号为95019的学生记录例8:删除所有学生的选课记录,Couse,SC,Student,DELETE FROM Student WHERE Sno=95019,DELETE FROM SC,58,二：SQL-实例-数据更新,例9：删除计算机科学系所有学生的选课记录,Couse,SC,Student,DELETE FROM SCWHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno),