关系运算与SQL实例

1、1,关系运算与SQL实例,金秋乐,2,一：关系代数-传统的集合运算,1、并(Union) RS=t|tRt S 2、交(Intersection) RS=t |tRt S 3、差(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。t

2、R表示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为属性

3、组。定义：当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,

4、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 t

5、s S trA tsB A和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|

6、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

7、,一：关系代数-专门的关系运算-例题,例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

8、,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,二：SQ

9、L-数据操纵,1、UPDATE UPDATE SET = ， WHERE 2、INSERT INSERT INTO (列名,) VALUES(表达式或函数,),23,二：SQL-数据操纵,2、INSERT（续） INSERT INTO SQL子查询 3、DELETE DELETE FROM WHERE ,24,二：SQL-实例-查询,例1：查询全体学生的学号和姓名 例2：查询全体学生的姓名、学号、所在系,Student,Couse,SC,SELECT Sno,Sname FROM Student,SELECT Sname, Sno,Sdept FROM Student,25,二：SQL-实例-

10、查询,例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

11、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,SE

12、LECT 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 WH

13、ERE 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

14、 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 Stud

15、ent 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_Design ESCAPE ,SELECT * FROM Course WHERE Cname LIKE DB_%i_ ESCAPE ,34,二：SQL-实例-查询,例

16、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

17、 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

18、,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,SELEC

19、T 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

20、,41,二：SQL-实例-查询,例34:查询每门课程的间接先行课,Couse,SC,Student,Couse FIRST,Couse SECOND,SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno,42,二：SQL-实例-查询,例35:查询选修了2号课程且成绩在90分以上的所有学生的学号和姓名,Couse,SC,Student,SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND

21、SC.Cno=2 AND SC.Grade90,43,二：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,44,二：SQL-实例-查询,例37:查询与“刘晨”在同一个系学习的学生的学号、姓名、所在系,Couse,SC,Student,SELECT Sno,Sname,Sdept FROM Student WHERE Sdep

22、t IN (SELECT Sdept FROM Student WHERE Sname=刘晨),可用连接实现,45,二：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=信息系统),可用连接实现,46,二：SQL-实例-查询,例37改写,Couse,SC,Student,SELECT Sno,Sname,Sdept

23、FROM Student WHERE Sdept= (SELECT Sdept FROM Student WHERE Sname=刘晨),47,二：SQL-实例-查询,例39：查询其他系中比信息系某一学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT Sname,Sage FROM Student WHERE Sage ANY(SELECT Sage FROM Student WHERE Sdept=IS),48,二：SQL-实例-查询,例40：查询其他系中比信息系所有学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT Sname,Sage

24、FROM Student WHERE Sage ALL(SELECT Sage FROM Student WHERE Sdept=IS),49,二：SQL-实例-查询,例41：查询所有选修了1号课程的学生姓名,Couse,SC,Student,SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1),50,二：SQL-实例-查询,例42：查询没有选修了1号课程的学生姓名,Couse,SC,Student,SELECT Sname FROM Student WHERE NOT

25、 EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1),51,二：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),52,二：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),53,二：SQL-实例-数据更新,例1：将一个新学生记录(学号:95020,姓名:陈东,性别:男,所在系:IS,年龄:18)插入到Student