嵌套查询

Create table student

(sno char(9) primary key, sname char(20) unique, ssex char(2), sage smallint, sdept char(20) );

Course表;

Create table course

(cno char(4) primary key,

Cname char(40),

Cpno char(4), Ccredit smallint );

Sc表：

Create table sc

(sno char(9),

Cno char(4), Grade smallint

);

2） 库表记录

Student表插入数据：

insert into student values('200215121','李勇','男',20,'CS');

insert into student values('200215122','刘晨','女',19,'CS'); insert into student values('200215123','王敏','女',18,'MA'); insert into student values('200215125','张立','男',19,'IS');

如图：

Course表：

insert into course values('1','数据库','5','4');

insert into course values('2','数学',' ','2'); insert into course values('3','信息系统','1','4'); insert into course values('4','操作系统','6','3'); insert into course values('5','数据结构','7','4'); insert into course values('6','数据处理',' ','2'); insert into course values('7','PASCAL语言','6','4');

如图：

Sc表：

insert into sc values('200215121','1',92);

insert into sc values('200215121','2',85); insert into sc values('200215121','3',88); insert into sc values('200215122','2',90); insert into sc values('200215122','3',80);

如图：

4. 带有IN谓词的子查询

1） IN谓词表达的意义

在嵌套查询中，子查询的结果往往是一个集合，所以用谓词IN加上子查询结果作为父查询的条件。

2） 构造嵌套查询的方法

将第一步查询嵌入到第二步查询的条件中，以此来构造嵌套查询。 3） 实例操作

【实例1】查询与“刘晨”在同一个系学习的学生。

先分步来完成查询，再构造嵌套查询，最后用自身连接来完成查询。 ① 确定“刘晨”所在系名。

Select sdept From student

Where sname=‘刘晨’；

执行结果如下：

② 查找所有在CS系学习的学生。

Select sno,sname,sdept From student Where sdept=‘CS’；

执行结果如下：

将第一步查询嵌入到第二步查询的条件中： Select sno,sname,sdept From student Where sdept in ( select sdept From student

Where sname=‘刘晨’); 执行结果如下图：

用自身连接完成本次查询：

Select s1.sno,s1.sname,s1.sdept From student s1,student s2 Where s1.sdept=s2.sdept And s2.sname=‘刘晨’; 执行结果如下：

【实例2】查询选修了课程名为“信息系统”的学生学号和姓名。

Select sno,sname From student Where sno in ( select sno From sc Where cno in

( select cno From course

Where cname=‘信息系统’))； 执行结果如下图：

用连接查询完成查询： Select student.sno,sname From student,sc,course Where student.sno=sc.sno And course.cno=sc.cno

And course.cname=‘信息系统’; 执行结果如下图：

5. 带有比较运算符的子查询

1） 比较运算符的子查询的含义

父查询与子查询之间用比较运算符进行连接。

当用户能确切知道内层查询返回的是单值时，可用>,<,=,>=,<=,!=或<>等比较运算符。 2） 实例操作

【实例1】找出每个学生超过他选修课程平均成绩的课程号。 Select cno From sc x Where grade>= ( select avg(grade) From sc y

Where y.sno=x.sno); 执行结果如下图：

6. 带有ANY或ALL谓词的子查询

1） ANY或ALL谓词的含义

ANY：任意一个值 ALL：所有值

2） ANY或ALL谓词需要配合使用比较运算符

> ANY 大于子查询结果中的某个值 > ALL 大于子查询结果中的所有值

< ANY 小于子查询结果中的某个值 < ALL 小于子查询结果中的所有值 >= ANY 大于等于子查询结果中的某个值 >= ALL 大于等于子查询结果中的所有值 <= ANY 小于等于子查询结果中的某个值 <= ALL 小于等于子查询结果中的所有值 = ANY 等于子查询结果中的某个值 =ALL 等于子查询结果中的所有值（通常没有实际意义） !=（或<>）ANY 不等于子查询结果中的某个值 !=（或<>）ALL 不等于子查询结果中的任何一个值 3） ANY与ALL与集函数的对应关系 ANY ALL 4） 实例操作 【实例1】查询其他系中比计算机科学系任意一个（其中某一个）学生年龄小的学生姓名和年龄。 Select sname,sage From student Where sage‘CS’; 执行结果如下图： = IN -- <>或！= -- NOTIN < >MIN >MAX >= >=MIN >=MAX

【实例2】查询其他系中比计算机科学系所有学生年龄都小的学生姓名和年龄。 Select sname,sage From student Where sage( select sage From student Where sdept=‘CS’) And sdept <>‘CS’;

执行结果如下图：

7. 带有EXISTS谓词的子查询

1） EXISTS谓词

--存在量词

--带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或“false”。 ●若内层查询结果非空，则返回真值 ●若内层查询结果为空，则返回假值

--由EXISTS引出的子查询，其目标表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。 2） NOT EXISTS 谓词

与EXISTS谓词相对应，使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的where子句返回真值，否则返回假值。 3） 实例操作

【实例1】查询所有选修了1号课程的学生姓名。 Select sname From student Where exists

( select * From sc

Where sno=student.sno And cno=‘1’ );

执行结果如下图：

【实例2】查询没有选修1号课程的学生姓名。 Select sname From student Where not exists

( select * From sc

Where sc.sno=student.sno And cno=‘1’ );

执行结果如下图：

【实例3】查询选修了全部课程的学生姓名。 Select sname From student Where not exists ( select * From course Where not exists ( select * From sc

Where sno=student.sno And cno=course.cno) );

执行结果如下图：

【实例4】查询至少选修了学生200215122选修的课程的全部课程的学生学号。 Select distinct sno From sc scx Where not exists

( select * From sc scy

Where scy.sno=‘200215122’ And not exists

( select * From sc scz

Where scz.sno=scx.sno And scz.sno=scy.sno) );

执行结果如下图：

完成情况： 1 理解了嵌套查询的意义和分类。 2 完成了各种不同类型的嵌套查询，全称量词和存在量词的转换。 出现的问题： 在使用exists和not exists量词做查询时，之间转换出错 ，后通过看课本例题的思路讲解理解了之间的转换，查询成功。