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 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
6、 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,1
8、9,二：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-
9、数据操纵,1、UPDATEUPDATE SET = ，WHERE 2、INSERTINSERT 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-实例-查询,例3：查
10、询全体学生的详细记录例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,2
11、7,二：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 DISTIN
12、CT 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
13、 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=95
14、001,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
15、 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-实例-查询,例21：查询缺少成绩的学生的学号和
16、课程号例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
17、=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 C
18、OUNT(*) 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
19、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:查询每
20、门课程的间接先行课,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 SC.Cno=2 AND SC.Grade90
21、,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 Sdept IN(SELECT SdeptFROM S
22、tudentWHERE Sname=刘晨),可用连接实现,45,二：SQL-实例-查询,例38:查询选修了课程名为信息系统的学生的学号和姓名,Couse,SC,Student,SELECT Sno,Sname FROM Student WHERE Cno IN(SELECT CnoFROM SCWHERE Cno IN(SELECT CnoFROM CourseWHERE Cname=信息系统),可用连接实现,46,二：SQL-实例-查询,例37改写,Couse,SC,Student,SELECT Sno,Sname,Sdept FROM Student WHERE Sdept=(SELECT
23、 SdeptFROM StudentWHERE Sname=刘晨),47,二：SQL-实例-查询,例39：查询其他系中比信息系某一学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT Sname,Sage FROM Student WHERE Sage ANY(SELECT SageFROM StudentWHERE Sdept=IS),48,二：SQL-实例-查询,例40：查询其他系中比信息系所有学生年龄小的学生的姓名和年龄,Couse,SC,Student,SELECT Sname,Sage FROM Student WHERE Sage ALL(SELECT Sa
24、geFROM StudentWHERE Sdept=IS),49,二：SQL-实例-查询,例41：查询所有选修了1号课程的学生姓名,Couse,SC,Student,SELECT Sname FROM Student WHERE EXISTS(SELECT *FROM SCWHERE Sno=Student.Sno AND Cno=1),50,二：SQL-实例-查询,例42：查询没有选修了1号课程的学生姓名,Couse,SC,Student,SELECT Sname FROM Student WHERE NOT EXISTS(SELECT *FROM SCWHERE Sno=Student.S
25、no AND Cno=1),51,二：SQL-实例-查询,例43：查询选修了全部课程的学生姓名,Couse,SC,Student,SELECT Sname FROM Student WHERE NOT EXISTS(SELECT *FROM CourseWHERE NOT EXISTS (SELECT *FROM SCWHERE Sno=Student.Sno AND Cno=Course.Cno),52,二：SQL-实例-查询,例44：查询至少选修了学生95002选修的全部课程的学生号码,Couse,SC,Student,SELECT DISTINCT Sno FROM SC SCX WHE
26、RE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.Sno=95002 AND NOT EXISTS (SELECT *FROM SC SCZWHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno),53,二：SQL-实例-数据更新,例1：将一个新学生记录(学号:95020,姓名:陈东,性别:男,所在系:IS,年龄:18)插入到Student表中例2:插入一条选课记录(95020,1),Couse,SC,Student,INSERT INTO Student VALUES(95020,陈东,男,IS,18),INSERT INTO
27、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 Student GROUP BY Sdept,55,二：SQL-实例-数据更新,例4：将学生95001的年龄改为22岁例5:将所有的学生年龄增加1岁,Couse,SC,Student,UP
28、DATE Student SET Sage=22 WHERE Sno=95001,UPDATE Student SET Sage=Sage+1,56,二：SQL-实例-数据更新,例6：将计算机科学系全体学生的成绩置零,Couse,SC,Student,UPDATE SC SET Grade=0 WHERE CS=(SELECT Sdept FROM StudentWHERE 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 SC WHERE CS=(SELECT Sdept FROM StudentWHERE Student.Sno=SC.Sno),
