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

first表（course表）
cno 1 2 3 cname 数据库 数学 信息系统 1 cpno ccredit 5 4 2 4
second表（course表）
cno cname cpno ccredit
1
2 3 4 5
数据库
数学 信息系统 操作系统 数据结构
5
4
2
1 6 7
4 3 4
4
5 6 7
操作系统
数据结构 数据处理 Pascal 2
6
7
数据处理
Pascal语言 6
2
4
6
4
完成上述查询的sql语句为： SELECT o, second.cpno FROM course first,course second WHERE first.cpno = o
200215122
200215123 200215125
刘晨
王敏 张立
女
女 男
19
18 19
CS
MA IS
200215122
200215123 200215125
刘晨
王敏 张立
女
女 男
19
18 19
CS
MA IS
例2：查询和“李勇”一个院系的学生的基本情况
SELECT b.*
FROM Student a,student b WHERE a.Sname='李勇' and a.Sdept= b.Sdept and b.Sname<>'李勇'
R
A a1 a1 a2 a2 B b1 b2 b3 b4 C 2 4 6 8 B b1 b2 b4 b5
S
D 5 6 7 8
外连接
A a1 a1 a2 a2 B b1 b2 b3 b4 C 2 4 6 8 D 5 6 null 7 A a1 a1
左外连接
B b1 b2 C 2 4 D 5 6 A a1 a1
WHERE cno=‘2’ ）;
Sql语言允许有多层嵌套查询。即一个子查询中还允 许嵌套其他子查询。需要特别指出的是子查询的 select 语句中不能使用order by 子句，order by子 句只能对最终查询结果排序。 子查询通常与IN 、比较运算符及EXISTS谓词结合使 用。
1、带有In谓词的子查询
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)
参见P82，查询结果中没有200215123和200215125这两 个学生的信息。
SQL扩展了以JOIN关键字指定连接的表达式，使表的连接运算能力 有了增强（关键字Outer 可省略）。 外 连 接 可 分 为 左 连 接 （ LEFT ） 、 右 连 接 （ RIGHT ） 和 全 连 接 （FULL）三种：
1、查询选修课程号为2的学生姓名；
SELECT sname FROM Student,sc WHERE Student.Sno = SC.Sno and o=‘2’ SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno= '2') 步骤：先找出2号课程对应的学号，再通过学号找学生姓名。
2.外连接
在通常的连接操作中，只有满足连接条件的行才能作为结果输出，但 有些情况下，也需要输出其他相关选项，这就用到了外连接。 例3：查询每个学生及其选修课程的情况。 Sno 200215121 Sname 李勇 Ssex 男 Sage 20 Sdept CS Cno 1 Grade 92
使用JOIN关键字实现表的连接
在SELECT语句的FROM子句中，通过指定不同类型
的JOIN关键字可以实现不同的表的连接方式，而在 ON关键字后指定连接条件。
基本连接语法如下：
SELECT column_list FROM join_table JOIN_TYPE join_table ON ( join_condition )
数据库原理
第三章：关系数据库标准语言SQL
授课教师：姜姗
1.自身连接
自身连接：连接操作不仅可以在两个表之间进行，也 可以是一个表与其自己进行连接，称为表的自身连接。 由于所有属性名都是同名属性，因此必须使用别名前 缀。需要给表起别名以示区别。
例1：查询每一门课的间接先修课（即先修课的先修课）

Select a.sno,sname,ssex,cno,grade From student a inner join sc b On a.sno=b.sno
练习：查询所有考试成绩及格的学生的成绩信息， 结果中包含学生的学号、姓名、性别、选修课程编 号、成绩，并按照成绩进行降序排列。
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：查询和“李勇”一个院系的其他学生的基本情况
a表（student表）
学号 sno 200215121 姓名 sname 李勇 性别 ssex 男 年龄 sage 20 所在系 sdept CS
b表（student表）
学号 sno 200215121 姓名 sname 李勇 性别 ssex 男 年龄 sage 20 所在系 sdept CS
右外连接
B b1 b2 C 2 4 D 5 6
a2
a2
b3
b4
6
8
null
7
a2
null
b4
b5
8
null
7
8
null
b5
null
8
左外连接格式： SELECT <目标列表达式>[,<目标列表达式>]…… FROM <表名1> LEFT [OUTER] JOIN <表名2> ON <连接条件> 右外连接格式： SELECT <目标列表达式>[,<目标列表达式>]…… FROM <表名1> RIGHT [OUTER] JOIN <表名2> ON <连接条件> 外连接格式： SELECT <目标列表达式>[,<目标列表达式>]…… FROM <表名1> FULL [OUTER] JOIN <表名2> ON <连接条件>
上述查询可以用连接查询实现： SELECT student.sno,sname FROM Student, sc, course WHERE Student .sno=sc.sno and o=o and ame=‘信息系统’
练习： 1、查询选修课程号为2的学生姓名； 2、查询没有选修课程的学生的基本情况； 3、查询至少有一个成绩在80分以上的学生的基本情况； 4、查询没有选修“数据库”课程的学生的基本情况。
AND o = o;
3.4.3 嵌套查询
SQL允许SELECT多层嵌套使用，即一个子查询中还
可以嵌套子查询，用来表示复杂的查询，从而增强
SQL的查询能力。 以这种层层嵌套的方式来构造查询语句正是SQL中 “结构化”的含义所在。
在where子句或having子句所表示的条件中，可以使用另一个查询的 结果（即一个查询块：一个SELECT- FROM- WHERE 语句称为一个 查询块）作为条件的一部分，这种将一个查询块嵌套在另一个查询块 的where子句或having子句的条件中的查询称为嵌套查询。 例如： SELECT sname FROM Student WHERE Sno in （ SELECT sno FROM sc 下层模块：子查询，内层查询 上层模块：父查询，外层查询
例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）
例2：查询选修了课程名为“信息系统”的学生学号和 姓名。
SELECT sno,sname FROM Student WHERE sno IN
(SELECT sno
FROM sc WHERE cno in （ SELECT cno FROM course WHERE cname=‘信息系统’） ） )
4. 多表连接
多表连接：两个以上的表进行连接
[例3.54]查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course
/*多表连接*/
WHERE Student.Sno = SC.Sno
在嵌套查询中，子查询的结果往往是一个集合， 所以谓词IN是嵌套查询中最常用的谓词。 IN子查询用于进行一个给定值是否在子查询结果 集中的判断。
例1：查询与“刘晨”在一个系学习的学生。
SELECT b.* FROM Student a,student 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<>‘刘晨' 步骤：先找出刘晨对应的院系，再通过院系找其余学生。