sql嵌套查询

嵌套查询概述

一个 SELECT-FROM-WHERE 语句称为一个查询块，将一个查询块嵌套在另一个查询块的 WHERE 子句 或 HAVING 短语的条件中的查询称为嵌套查询

不相关子查询

子查询的查询条件不依赖父查询

相关子查询

子查询的查询条件依赖父查询

带有IN谓词的子查询

查询与“张三”在同一个系学习的学生

1. 确定“张三”所在的系

   SELECT Sdept
   FROM student
   WHERE Sname='张三';
   

   结果为"CS"

2. 查询"CS"系的所有学生

   SELECT Sno, Sname, Sdept
   FROM student
   WHERE Sdept='CS';
   

   

3. 将1的查询嵌套进2的查询中

   SELECT Sno, Sname, Sdept
   FROM student
   WHERE Sdept IN (
   	SELECT Sdept
   	FROM student
   	WHERE Sname='张三'
   );
   

   

4. 用自身连接查询实现

   /*查询与“张三”在同一个系学习的学生学号和姓名*/
   SELECT second.Sno, second.Sname, second.`Sdept`
   FROM student FIRST, student SECOND
   WHERE first.`Sname`='张三' AND first.`Sdept`=second.`Sdept`;
   

   

查询选修了课程名为“信息系统”的学生学号和姓名

1. 在Course表中找“信息系统”课程的课程号

   SELECT Cno
   FROM course
   WHERE Cname='信息系统';
   

   结果为3

2. 在SC表中找选修了3号课程的学生的学号

   SELECT Sno 
   FROM sc
   WHERE Cno='3';
   

   结果为200215121和200215122

3. 最后在Student表中取出学号和姓名

   SELECT Sno, Sname
   FROM student
   WHERE Sno='200215121' OR Sno='200215122';
   

   

4. 将上面三个操作嵌套

   /*查询选修了课程名为“信息系统”的学生学号和姓名*/
   SELECT Sno, Sname
   FROM student
   WHERE Sno IN(
   	SELECT Sno
   	FROM sc
   	WHERE Cno IN(
   		SELECT Cno
   		FROM course
   		WHERE Cname='信息系统'
   	)
   );
   

   

5. 用连接查询实现

   /*查询选修了课程名为“信息系统”的学生学号和姓名*/
   SELECT student.`Sno`, student.`Sname`
   FROM student, sc, course
   WHERE Cname='信息系统' AND course.`Cno`=sc.`Cno` AND sc.`Sno`=student.`Sno`;
   

   

带有比较运算的子查询

• 当能确切知道内层查询返回单值时，可用比较运 算符（ > ， < ， = ， >= ， <= ， != 或 < > ）
• 与 ANY 或 ALL 谓词配合使用

假设一个学生只可能在一个系学习，并且必须属于一个系，可以用 = 代替 IN

查询与“张三”在同一个系学习的学生

用 = 替代谓词 IN

/*查询与“张三”在同一个系学习的学生学号和姓名*/
SELECT Sno, Sname, Sdept
FROM student
WHERE Sdept = (
	SELECT Sdept
	FROM student
	WHERE Sname='张三'
);

找出每个学生超过他选修课程平均成绩的课程号

SELECT Sno, Cno
FROM sc X
WHERE Grade > (
	SELECT AVG(Grade)
	FROM sc Y
	WHERE y.`Sno`=x.`Sno`
);

此例为相关子查询，外层查询取出sc的一个元组x，将x.Sno（如：200215121）传递给内层查询，内层查询相当于

SELECT AVG(Grade)
FROM sc
WHERE y.`Sno`='200215121';

因此内层查询计算得学号为200215121学生得平均成绩，并将其返回给外层查询（假定为88），则外层查询相当于

SELECT Sno, Cno
FROM sc X
WHERE Grade > 88;

带有ANY或ALL谓词的子查询

ANY：任意一个值

ALL：所有值

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

关键词：某一

SELECT Sname, Sage
FROM student
WHERE Sdept!='CS' AND Sage<ANY(
	SELECT Sage
	FROM student
	WHERE Sdept='CS'
);

先处理子查询，CS系所有学生的年龄构成一个集合，再处理父查询，找出所有不是CS系且年龄小于20或19的学生，相当于使用聚集函数MAX

SELECT Sname, Sage
FROM student
WHERE Sdept!='CS' AND Sage<(
	SELECT MAX(Sage)
	FROM student
	WHERE Sdept='CS'
);

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

关键词：所有

SELECT Sname, Sage
FROM student
WHERE Sdept!='CS' AND Sage<ALL(
	SELECT Sage
	FROM student
	WHERE Sdept='CS'
);

相当于使用聚集函数MIN

SELECT Sname, Sage
FROM student
WHERE Sdept!='CS' AND Sage<(
	SELECT MIN(Sage)
	FROM student
	WHERE Sdept='CS'
);

带有EXISTS谓词的子查询

带有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真 值“ true” 或逻辑假值“ false”

• 若内层查询结果非空，则外层的 WHERE 子句返回真值

• 若内层查询结果为空，则外层的 WHERE 子句返回假值

查询所有选修了 1 号课程的学生姓名

SELECT Sname
FROM student
WHERE EXISTS (
	SELECT *
	FROM sc
	WHERE sc.`Cno`='1' AND sc.`Sno`=student.`Sno`
);

依次取出student中每个元组的Sno值，用此值去检查sc关系，若sc中存在这样的元组，其Sno为外层查询的Sno，且Cno为1，则取Sname送入结果集

用连接查询实现

/*查询所有选修了 1 号课程的学生姓名*/
SELECT Sname
FROM student, sc
WHERE sc.`Cno`='1' AND student.`Sno`=sc.`Sno`;

查询没有选修 1 号课程的学生姓名

SELECT Sname
FROM student
WHERE NOT EXISTS (
	SELECT *
	FROM sc
	WHERE sc.`Cno`='1' AND sc.`Sno`=student.`Sno`
);