数据库---数据操作3

数据操作--子查询

1、在SQL语言中，一个SELECT－FROM－WHERE语句称为一个查询块。

2、子查询是一个 SELECT 查询，它嵌套在语句的 WHERE 或 HAVING 子句内，或其它子查询中

3、子查询的 SELECT 查询总用圆括号括起来。

(1)使用子查询进行基于集合的测试

使用子查询进行基于集合的测试的语句的一般格式为： 列名 NOT IN (子查询)

 

 

 

例2、 查询选修了“数据库基础”课程的学生的学号、姓名。

SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = '数据库基础') )

(2)使用子查询进行比较测试

1）带比较运算符的子查询指父查询与子查询之间用比较运算符连接。

2）能确切知道内层查询返回的是单值时，可用>、<、=、>=、<=、<>运算符。

例3：查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。

SELECT Sno , Grade FROM SC WHERE Cno = 'c02‘ and Grade > ( SELECT AVG(Grade) from SC WHERE Cno = 'c02')

(3)带有ANY（SOME）或ALL谓词的子查询

谓词语义 ANY：任意一个值 ALL：所有值

需要配合使用比较运算符

 

例4：查询其他系中比计算机系某一学生年龄小的学生姓名和年龄

SELECT Sname，Sage FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ‘CS ' ;

 用聚集函数实现：查询其他系中比计算机系某一学生年龄小的学生姓名和年龄

SELECT Sname，Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= ‘CS ') AND Sdept <> ' CS ’;

例4：查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

方法一：用ALL谓词 SELECT Sname，Sage FROM Student WHERE Sage < ALL (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ' CS ’;

方法二：用聚集函数 SELECT Sname，Sage FROM Student WHERE Sage < (SELECT MIN(Sage) FROM Student WHERE Sdept= ' CS ') AND Sdept <>' CS ’;

(4)使用子查询进行存在性测试

EXISTS谓词： 带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。

1. EXISTS谓词 存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”,判断有无结果。 若内层查询结果为空，则外层的WHERE子句返回假值 若内层查询结果非空，则外层的WHERE子句返回真值 因带EXISTS的子查询只返回true或false，给出列名无实际意义，通常都用* 。

2. NOT EXISTS谓词 若内层查询结果非空，则外层的WHERE子句返回假值 若内层查询结果为空，则外层的WHERE子句返回真值

例5：查询选修了‘c01’号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno  AND Cno = 'c01')

2点注意

注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。

注2: 由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。

例6：查询没有选修‘c01’号课程的学生姓名和所在系。 SELECT Sname, Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'c01')

不同形式的查询间的替换

• 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换。
• 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

例7：查询选修了全部课程的学生姓名。

SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno） ）；

查询学生X，对于学生X来说，不存在课程y，200215122选修了y，而学生x没有选。用NOT EXISTS谓词表示：

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.Cno=SCY.Cno) )；