《数据库系统概论(王珊第五版)第十讲.ppt》由会员分享，可在线阅读，更多相关《数据库系统概论(王珊第五版)第十讲.ppt（51页珍藏版）》请在三一文库上搜索。
1、数据库原理 第三章：关系数据库标准语言SQL,授课教师：姜姗,1.自身连接,自身连接：连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。 由于所有属性名都是同名属性，因此必须使用别名前缀。需要给表起别名以示区别。,例1：查询每一门课的间接先修课（即先修课的先修课） first表（course表） second表（course表）,完成上述查询的sql语句为： SELECT o, second.cpno FROM course first,course second WHERE first.cpno = o,例2：查询和“李勇”一个院系的其他学生的基本情况 a表
2、（student表） b表（student表）,例2：查询和“李勇”一个院系的学生的基本情况 SELECT b.* FROM Student a,student b WHERE a.Sname=李勇 and a.Sdept= b.Sdept and b.Sname李勇,使用JOIN关键字实现表的连接,在SELECT语句的FROM子句中，通过指定不同类型的JOIN关键字可以实现不同的表的连接方式，而在ON关键字后指定连接条件。,基本连接语法如下：SELECT column_list FROM join_table JOIN_TYPE join_tableON ( join_condition )
3、说明如下。join_table：指出参与连接操作的表名。JOIN_TYPE为连接类型，可分为3种：内部连接、外部连接和交叉连接。,1.内部连接,内部连接INNER JOIN内部连接是使用比较运算符比较要连接列中的值的连接，下面通过实例说明其使用。例：从student和sc表中查询每个学生的各门选修课成绩信息，要求返回的结果中包含学生的学号、姓名、性别、选修课程序号及成绩。 Select a.sno,sname,ssex,cno,grade From student a inner join sc b On a.sno=b.sno,练习：查询所有考试成绩及格的学生的成绩信息，结果中包含学生的学号
4、、姓名、性别、选修课程编号、成绩，并按照成绩进行降序排列。,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,2.外连接,在通常的连接操作中，只有满足连接条件的行才能作为结果输出，但有些情况下，也需要输出其他相关选项，这就用到了外连接。 例3：查询每个学生及其选修课程的情况。,例3：查询每个学生及其选修课程的情况。 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM St
5、udent,SC WHERE Student.Sno = SC.Sno 参见P82，查询结果中没有200215123和200215125这两个学生的信息。,SQL扩展了以JOIN关键字指定连接的表达式，使表的连接运算能力有了增强（关键字Outer 可省略）。 外连接可分为左连接（LEFT）、右连接（RIGHT）和全连接（FULL）三种：,R,S,外连接,左外连接,右外连接,左外连接格式： SELECT , FROM LEFT OUTER JOIN ON 右外连接格式： SELECT , FROM RIGHT OUTER JOIN ON 外连接格式： SELECT , FROM FULL OUT
6、ER JOIN ON ,例1：查询所有学生基本情况及他们选修课程的成绩（包括没选修任何课的学生）。 Select student.*,grade From student left outer join sc On （student.sno = sc.sno）,Select student.*,grade From student left join sc On （student.sno = sc.sno）,例2：查询所有选修课程的学生的学号、姓名、课程号及成绩。 Select student.sno,sname,cno,grade From student right outer join
7、sc On （student.sno = sc.sno）,Select student.sno,sname,cno,grade From student right join sc On （student.sno = sc.sno）,4. 多表连接,多表连接：两个以上的表进行连接 例3.54查询每个学生的学号、姓名、选修的课程名及成绩 SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course /*多表连接*/ WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;,3.4.3
8、 嵌套查询,SQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌套子查询，用来表示复杂的查询，从而增强SQL的查询能力。 以这种层层嵌套的方式来构造查询语句正是SQL中“结构化”的含义所在。,在where子句或having子句所表示的条件中，可以使用另一个查询的结果（即一个查询块：一个SELECT- FROM- WHERE 语句称为一个查询块）作为条件的一部分，这种将一个查询块嵌套在另一个查询块的where子句或having子句的条件中的查询称为嵌套查询。 例如： SELECT sname FROM Student 上层模块：父查询，外层查询 WHERE Sno in （ SELECT
9、sno FROM sc 下层模块：子查询，内层查询 WHERE cno=2 ）;,Sql语言允许有多层嵌套查询。即一个子查询中还允许嵌套其他子查询。需要特别指出的是子查询的select 语句中不能使用order by 子句，order by子句只能对最终查询结果排序。 子查询通常与IN 、比较运算符及EXISTS谓词结合使用。,1、带有In谓词的子查询,在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最常用的谓词。 IN子查询用于进行一个给定值是否在子查询结果集中的判断。,例1：查询与“刘晨”在一个系学习的学生。,SELECT b.* FROM Student a,stude
10、nt b WHERE a.Sname=刘晨 and a.Sdept= b.Sdept and b.Sname刘晨,SELECT * FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE sname= 刘晨) and Sname刘晨,步骤：先找出刘晨对应的院系，再通过院系找其余学生。,例2：查询选修了课程名为“信息系统”的学生学号和姓名。 SELECT sno,sname FROM Student WHERE sno IN (SELECT sno FROM sc WHERE cno in （ SELECT cno FROM co
11、urse WHERE cname=信息系统） ） ),上述查询可以用连接查询实现： SELECT student.sno,sname FROM Student, sc, course WHERE Student .sno=sc.sno and o=o and ame=信息系统,练习： 1、查询选修课程号为2的学生姓名； 2、查询没有选修课程的学生的基本情况； 3、查询至少有一个成绩在80分以上的学生的基本情况； 4、查询没有选修“数据库”课程的学生的基本情况。,1、查询选修课程号为2的学生姓名；,SELECT sname FROM Student,sc WHERE Student.Sno =
12、SC.Sno and o=2,SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno= 2),步骤：先找出2号课程对应的学号，再通过学号找学生姓名。,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),4、查询没有
13、选修“数据库”课程的学生的基本情况。,SELECT * FROM Student WHERE Sno not in (SELECT sno FROM sc WHERE cno in (SELECT cno from course WHERE cname=数据库),2、带有比较运算符的子查询,带有比较运算符的子查询是指父查询与子查询之间用比较运算符连接。当用户确切知道内层查询返回的是单值时，可以用， =， !=或 等比较运算符。 例如39：查询与“刘晨”在一个系学习的学生。由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用=代替in SELECT * FROM Studen
14、t WHERE Sdept IN（或者用 =） (SELECT Sdept FROM Student WHERE sname= 刘晨) and Sname刘晨,例1：找出一个学生超过他选修课程平均成绩的课程号。 x表 （sc表） y表（sc表）,例1：找出一个学生超过他选修课程平均成绩的课程号。 SELECT sno,cno FROM sc x WHERE grade= (select avg(grade) from sc y where y.sno=x.sno),练习 1、查询和“李勇”不在一个院系的学生的基本情况； 2、查询年龄高于平均年龄的学生的基本信息。,1、查询和“李勇”不在一个院系
15、的学生的基本情况；,SELECT * FROM Student WHERE Sage (SELECT avg(sage) FROM student),2、查询年龄高于平均年龄的学生的基本信息；,SELECT * FROM Student WHERE Sdept (SELECT Sdept FROM Student WHERE sname= 李勇),3、带有ANY或ALL谓词的子查询,子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL谓词修饰。 而使用ANY或ALL谓词时必须同时使用比较运算符。,需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值
16、 = ANY 大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值 ）ANY 不等于子查询结果中的某个值 !=（或）ALL不等于子查询结果中的任何一个值,例：查询其他系中比计算机科学系（CS）某一学生年龄小的学生姓名和年龄；,例1：查询其他系中比计算机科学系（CS）某一学生年龄小的学生姓名和年龄； SELECT Sname,Sage FROM Student WHERE Sage CS,SELECT Sname,Sage FROM Student WHERE Sage CS,例2：查询其他系中比计算机科学系（CS）所有学生年龄都小的学生姓名和年龄； SELECT Sname,S
17、age FROM Student WHERE Sage CS,SELECT Sname,Sage FROM Student WHERE Sage CS,4、带有EXISTS谓词的子查询,EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则外层的WHERE子句返回真值 若内层查询结果为空，则外层的WHERE子句返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。,例1：查询参加选修的学生信息。 SELECT * FROM st
18、udent WHERE EXISTS (SELECT * FROM sc WHERE sc.sno =student.sno),SELECT * FROM Student WHERE Sno IN (SELECT Sno FROM SC ),例2：查询未参加选修的学生信息。 SELECT * FROM student WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.sno =student.sno),SELECT * FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC ),例3：查询没有选修1号课程的学生姓
19、名。 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 (SELECT Sno FROM SC WHERE Cno= 1),三、集合查询,SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。 集合操作主要包括： 并操作 UNION 交操作 INTERSECT 差操作 EXCEPT 注意：参加集合操作的各查询结果的列数必须相同；对应项的
20、数据类型也必须相同。,例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：将多个查询结果合并起来时，保留重复元组，包含空值。,例2：查询选修了课程1或者选修了课程2的学生。 SELECT * FROM SC WHERE Cno=1
21、 UNION SELECT * FROM SC WHERE Cno= 2,例3：查询计算机科学系（CS）的学生与年龄不大于19岁的学生的交集。 SELECT DISTINCT * FROM Student WHERE Sdept= CS and Sage=19,SELECT * FROM Student WHERE Sdept= CS INTERSECT SELECT * FROM Student WHERE Sage=19,例4：查询选修了课程1又选修了课程2的学生。 SELECT * FROM SC WHERE Cno=1 INTERSECT SELECT * FROM SC WHERE
22、Cno= 2,例5：查询计算机科学系（CS）的学生与年龄不大于19岁的学生的差集。 SELECT * FROM Student WHERE Sdept=CS EXCEPT SELECT * FROM Student WHERE Sage =19 等价于： CS系的年龄大于19岁的学生，sql命令如下所示。,SELECT * FROM Student WHERE Sdept=CS AND Sage 19,SELECT语句的基本格式,SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY , HAVING 条件表达式 ORDER BY ASC|DESC , ASC|DESC ,总结,连接查询 以JOIN关键字指定的外连接 嵌套查询 带有In谓词的子查询 带有比较运算符的子查询 带有EXISTS谓词的子查询 集合查询 并操作 UNION，交操作 INTERSECT，差操作 EXCEPT,