数据库系统概论(王珊第五版)第十讲课件

1、数据库原理 第三章：关系数据库标准语言SQL,授课教师：姜姗,1,PPT学习交流,1.自身连接,自身连接：连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。 由于所有属性名都是同名属性，因此必须使用别名前缀。需要给表起别名以示区别。,2,PPT学习交流,例1：查询每一门课的间接先修课（即先修课的先修课） first表（course表） second表（course表）,3,PPT学习交流,完成上述查询的sql语句为： SELECT o, second.cpno FROM course first,course second WHERE first.cpno =

2、 o,4,PPT学习交流,例2：查询和“李勇”一个院系的其他学生的基本情况 a表（student表） b表（student表）,5,PPT学习交流,例2：查询和“李勇”一个院系的学生的基本情况 SELECT b.* FROM Student a,student b WHERE a.Sname=李勇 and a.Sdept= b.Sdept and b.Sname李勇,6,PPT学习交流,使用JOIN关键字实现表的连接,在SELECT语句的FROM子句中，通过指定不同类型的JOIN关键字可以实现不同的表的连接方式，而在ON关键字后指定连接条件。,7,PPT学习交流,基本连接语法如下：SELECT

3、 column_list FROM join_table JOIN_TYPE join_tableON ( join_condition )说明如下。join_table：指出参与连接操作的表名。JOIN_TYPE为连接类型，可分为3种：内部连接、外部连接和交叉连接。,8,PPT学习交流,1.内部连接,内部连接INNER JOIN内部连接是使用比较运算符比较要连接列中的值的连接，下面通过实例说明其使用。例：从student和sc表中查询每个学生的各门选修课成绩信息，要求返回的结果中包含学生的学号、姓名、性别、选修课程序号及成绩。 Select a.sno,sname,ssex,cno,grad

4、e From student a inner join sc b On a.sno=b.sno,9,PPT学习交流,练习：查询所有考试成绩及格的学生的成绩信息，结果中包含学生的学号、姓名、性别、选修课程编号、成绩，并按照成绩进行降序排列。,Select a.sno,sname,ssex,o,grade From student a inner join sc b On a.sno=b.sno and Grade=60 order by b.Grade,10,PPT学习交流,2.外连接,在通常的连接操作中，只有满足连接条件的行才能作为结果输出，但有些情况下，也需要输出其他相关选项，这就用到了外连

5、接。 例3：查询每个学生及其选修课程的情况。,11,PPT学习交流,例3：查询每个学生及其选修课程的情况。 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno 参见P82，查询结果中没有200215123和200215125这两个学生的信息。,12,PPT学习交流,SQL扩展了以JOIN关键字指定连接的表达式，使表的连接运算能力有了增强（关键字Outer 可省略）。 外连接可分为左连接（LEFT）、右连接（RIGHT）和全连接（FULL）三种：,13,PPT学

6、习交流,R,S,外连接,左外连接,右外连接,14,PPT学习交流,左外连接格式： SELECT , FROM LEFT OUTER JOIN ON 右外连接格式： SELECT , FROM RIGHT OUTER JOIN ON 外连接格式： SELECT , FROM FULL OUTER JOIN ON ,15,PPT学习交流,例1：查询所有学生基本情况及他们选修课程的成绩（包括没选修任何课的学生）。 Select student.*,grade From student left outer join sc On （student.sno = sc.sno）,Select studen

7、t.*,grade From student left join sc On （student.sno = sc.sno）,16,PPT学习交流,例2：查询所有选修课程的学生的学号、姓名、课程号及成绩。 Select student.sno,sname,cno,grade From student right outer join sc On （student.sno = sc.sno）,Select student.sno,sname,cno,grade From student right join sc On （student.sno = sc.sno）,17,PPT学习交流,4. 多表

8、连接,多表连接：两个以上的表进行连接 例3.54查询每个学生的学号、姓名、选修的课程名及成绩 SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course /*多表连接*/ WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;,18,PPT学习交流,3.4.3 嵌套查询,SQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌套子查询，用来表示复杂的查询，从而增强SQL的查询能力。 以这种层层嵌套的方式来构造查询语句正是SQL中“结构化”的含义所在。,19,PPT学习交流,在

9、where子句或having子句所表示的条件中，可以使用另一个查询的结果（即一个查询块：一个SELECT- FROM- WHERE 语句称为一个查询块）作为条件的一部分，这种将一个查询块嵌套在另一个查询块的where子句或having子句的条件中的查询称为嵌套查询。 例如： SELECT sname FROM Student 上层模块：父查询，外层查询 WHERE Sno in （ SELECT sno FROM sc 下层模块：子查询，内层查询 WHERE cno=2 ）;,20,PPT学习交流,Sql语言允许有多层嵌套查询。即一个子查询中还允许嵌套其他子查询。需要特别指出的是子查询的sel

10、ect 语句中不能使用order by 子句，order by子句只能对最终查询结果排序。 子查询通常与IN 、比较运算符及EXISTS谓词结合使用。,21,PPT学习交流,1、带有In谓词的子查询,在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最常用的谓词。 IN子查询用于进行一个给定值是否在子查询结果集中的判断。,22,PPT学习交流,例1：查询与“刘晨”在一个系学习的学生。,SELECT b.* FROM Student a,student b WHERE a.Sname=刘晨 and a.Sdept= b.Sdept and b.Sname刘晨,SELECT * F

11、ROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE sname= 刘晨) and Sname刘晨,步骤：先找出刘晨对应的院系，再通过院系找其余学生。,23,PPT学习交流,例2：查询选修了课程名为“信息系统”的学生学号和姓名。 SELECT sno,sname FROM Student WHERE sno IN (SELECT sno FROM sc WHERE cno in （ SELECT cno FROM course WHERE cname=信息系统） ） ),24,PPT学习交流,上述查询可以用连接查询实现： SEL

12、ECT student.sno,sname FROM Student, sc, course WHERE Student .sno=sc.sno and o=o and ame=信息系统,25,PPT学习交流,练习： 1、查询选修课程号为2的学生姓名； 2、查询没有选修课程的学生的基本情况； 3、查询至少有一个成绩在80分以上的学生的基本情况； 4、查询没有选修“数据库”课程的学生的基本情况。,26,PPT学习交流,1、查询选修课程号为2的学生姓名；,SELECT sname FROM Student,sc WHERE Student.Sno = SC.Sno and o=2,SELECT S

13、name FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno= 2),步骤：先找出2号课程对应的学号，再通过学号找学生姓名。,27,PPT学习交流,2、查询没有选修课程的学生的基本情况；,SELECT * FROM Student WHERE Sno not in (SELECT sno FROM sc),3、查询至少有一个成绩在80分以上的学生的基本情况；,SELECT * FROM Student WHERE Sno in (SELECT sno FROM sc WHERE grade=80),28,PPT学习交流,4、查询没有选

14、修“数据库”课程的学生的基本情况。,SELECT * FROM Student WHERE Sno not in (SELECT sno FROM sc WHERE cno in (SELECT cno from course WHERE cname=数据库),29,PPT学习交流,2、带有比较运算符的子查询,带有比较运算符的子查询是指父查询与子查询之间用比较运算符连接。当用户确切知道内层查询返回的是单值时，可以用， =， !=或 等比较运算符。 例如39：查询与“刘晨”在一个系学习的学生。由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用=代替in SELECT * F

15、ROM Student WHERE Sdept IN（或者用 =） (SELECT Sdept FROM Student WHERE sname= 刘晨) and Sname刘晨,30,PPT学习交流,例1：找出一个学生超过他选修课程平均成绩的课程号。 x表 （sc表） y表（sc表）,31,PPT学习交流,例1：找出一个学生超过他选修课程平均成绩的课程号。 SELECT sno,cno FROM sc x WHERE grade= (select avg(grade) from sc y where y.sno=x.sno),32,PPT学习交流,练习 1、查询和“李勇”不在一个院系的学生的

16、基本情况； 2、查询年龄高于平均年龄的学生的基本信息。,33,PPT学习交流,1、查询和“李勇”不在一个院系的学生的基本情况；,SELECT * FROM Student WHERE Sage (SELECT avg(sage) FROM student),2、查询年龄高于平均年龄的学生的基本信息；,SELECT * FROM Student WHERE Sdept (SELECT Sdept FROM Student WHERE sname= 李勇),34,PPT学习交流,3、带有ANY或ALL谓词的子查询,子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL谓词修饰。 而使用A

17、NY或ALL谓词时必须同时使用比较运算符。,35,PPT学习交流,需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 = ANY 大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值 ）ANY 不等于子查询结果中的某个值 !=（或）ALL不等于子查询结果中的任何一个值,36,PPT学习交流,例：查询其他系中比计算机科学系（CS）某一学生年龄小的学生姓名和年龄；,37,PPT学习交流,例1：查询其他系中比计算机科学系（CS）某一学生年龄小的学生姓名和年龄； SELECT Sname,Sage FROM Student WHERE Sage CS

18、,SELECT Sname,Sage FROM Student WHERE Sage CS,38,PPT学习交流,例2：查询其他系中比计算机科学系（CS）所有学生年龄都小的学生姓名和年龄； SELECT Sname,Sage FROM Student WHERE Sage CS,SELECT Sname,Sage FROM Student WHERE Sage CS,39,PPT学习交流,4、带有EXISTS谓词的子查询,EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则外层的WHERE子句返回真值

19、 若内层查询结果为空，则外层的WHERE子句返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。,40,PPT学习交流,例1：查询参加选修的学生信息。 SELECT * FROM student WHERE EXISTS (SELECT * FROM sc WHERE sc.sno =student.sno),SELECT * FROM Student WHERE Sno IN (SELECT Sno FROM SC ),41,PPT学习交流,例2：查询未参加选修的学生信息。 SELECT * FROM studen

20、t WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.sno =student.sno),SELECT * FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC ),42,PPT学习交流,例3：查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1 ),SELECT Sname FROM Student WHERE Sno NOT IN (SEL

21、ECT Sno FROM SC WHERE Cno= 1),43,PPT学习交流,三、集合查询,SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。 集合操作主要包括： 并操作 UNION 交操作 INTERSECT 差操作 EXCEPT 注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。,44,PPT学习交流,例1：查询计算机科学系（CS）的学生及年龄不大于19岁的学生。 SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19,SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19,UNION：将多个查询结果合并起来时，系统自动去掉重复元组。 UNION ALL：将多个查询结果合并起来时，保留重复元组，包含空值。,45,PPT学习交流,例2：查询选修了课程1或者选修了课程2的学生。 SELECT * FROM SC WHERE Cno=1 UNION SELECT * FROM SC WHERE Cno= 2,46,PPT学习交流,例3：查询计算机科学系（CS）