数据库—DML3(嵌套查询和集合查询)

数据库—DML(嵌套查询)

---

• SQL是一种结构化查询语言，它允许将一个查询作为子查询嵌套在另一个SELECT语句中
• 最常见的嵌套是将子查询嵌套在WHERE子句或HAVING短语的条件中
• 称将一个查询嵌套在另一个查询中的查询称为嵌套查询，并称前者为子查询（内层查询），后者为父查询（外层查询）
• 嵌套查询分为不相关子查询和相关子查询
• 不相关子查询的子查询的条件不依赖于父查询，不相关子查询的查询效率高
• 相关子查询的子查询的条件依赖于父查询

---

IN引出的子查询

• IN引导的查询为不相关子查询
• IN表达式其形式如下： <元组> [NOT] IN <子查询>
• 其中<元组>形如(<值表达式>,…,<值表达式>)，并且当元组只有一个分量时，可以省略圆括号
• 当且仅当<元组>出现在<子查询>的结果中，<元组> IN <子查询>为真，而<元组> NOT IN <子查询>为假

SELECT Sno, Sname
FROM Students
WHERE Sex = ‘女’ AND Speciality IN
   (SELECT Speciality
    FROM Students
    WHERE Sname = ‘林艳’);
//在表Students里选择性别为女并且专业在（表Students中选择出的姓名为林艳的专业）中的学生的学号和姓名，即查询和林艳在同一个专业学习的女同学的学号和姓名

---

集合的比较引出的子查询

• SQL允许将一个元素与子查询的结果集进行比较。这种量化比较表达式的常用形式是：
• <值表达式> & ALL | SOME | ANY <子查询>
• 其中<值表达式>通常是属性，&是比较运算符（=、<>、!＝、<、>、>=、<=）SOME和ANY的含义相同。早期只有ANY，但容易与英语中的any一词在语言上混淆，现在更多地使用SOME
• 当<子查询>的结果为单个值时，ALL、SOME和ANY可以省略

SELECT Sno, Sname, Speciality, year(Birthday)
FROM Students
WHERE Speciality <>‘软件工程’ AND
year(Birthday)> ALL (SELECT year(Birthday)
                      FROM Students
                      WHERE Speciality = ‘软件工程’);
//在表Students里选择专业不是软件工程的，并且出生年份大于所有（从表Students中选择的专业为软件工程的学生的出生年份）的学生，并输出他们的学号，姓名，专业和出生年份
//即：查询比软件工程专业所有学生都小其他专业的学生的学号、姓名、专业和出生日期

以上代码等同于

SELECT Sno, Sname, Speciality, year(Birthday)
FROM Students
WHERE Speciality <>‘软件工程’ AND
year(Birthday)> (SELECT MAX(year(Birthday))
                  FROM Students
                  WHERE Speciality = ‘软件工程’)；
//用聚集函数实现                

PS：用聚集函数实现子查询通常比直接用SOME或ALL查询效率要高

some和all与·聚集函数的对应关系如下表：

	=	！=或<>	<	<=	>	>=
SOME	IN	—	<MAX	<=MAX	>MIN	>=MIN
ALL	—	NOT IN	<MIN	<=MIN	>MAX	>=MAX

SELECT Cno, AVG(Grade)
FROM SC
GROUP BY Cno
HAVING AVG(Grade) >= ALL(SELECT AVG(Grade)
       			 FROM SC
                         GROUP BY Cno);
//在表SC中以Cno分组选择平均成绩大于（所有在表SC中以cno分组的成绩的平均值）的课程的课程号和平均成绩
//即，查询平均成绩最高的课程的课程号和平均成绩

PS：SQL中的聚集函数不允许复合，形如MAX(AVG(…））的写法是不允许的

---

存在量词引出的子查询

• 一般形式如下：EXISTS <子查询>
• 其中<子查询>的SELECT子句的形式为：SELECT *
• EXISTS <子查询>为真，当且仅当<子查询>的结果非空（至少包含一个元组）

SELECT Sno, Sname
FROM Students S
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=S.Sno AND Cno=‘CS403’);
）
//如果在表SC中存在学号出现在Students中，并且课程号为CS403的信息，那么在表Students中输出他的学号和姓名
//即：查询所有选修了CS403课程的学生的学号和姓名

以上代码等同于

SELECT Sno, Sname
FROM Students 
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno=‘CS403’);
//用IN引导的嵌套查询

emmm，下面这两个例子比较难理解，看懂看不懂随缘吧

SELECT Sno, Sname
FROM Students S
WHERE NOT EXISTS 
(SELECT *
FROM Courses C
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE SC.Sno= S.Sno AND SC.Cno= C.Cno));
//查询选修了全部课程的学生的学号和姓名

SELECT Sno, Sname
FROM Students S
WHERE NOT EXISTS 
(SELECT *
FROM SC SC1
WHERE SC1.Sno = ‘201615122’ AND
NOT EXISTS 
(SELECT *
FROM SC SC2
WHERE SC2.Sno=S.Sno AND SC2.Cno=SC1.Cno))；
//查询至少选修了学号为201615122的学生选修的全部课程的学生的学号和姓名

---

集合查询

• SQL语言也支持传统的集合运算，包括并（UNION）、交（INTERSECT）、差（EXCEPT）
• 集合运算的常见形式为：<元组集表达式> <集合运算符> [ALL] <元组集表达式>
• 其中<元组集表达式>产生元组的集合，通常是SELECT查询或集合运算的结果
• <集合运算符>是UNION、INTERSECT或EXCEPT
• 集合运算将自动删除结果中的重复元组。可选的ALL可以用来保留运算结果中的重复元组

SELECT Sno
FROM SC
WHERE Cno= ‘CS301’
EXCEPT
SELECT Sno
FROM SC
WHERE Cno= ‘CS306’;
//从选修了CS301的学生里减去选修了CS306的
//即：查询选修了CS301号课程, 但未选修CS306号课程的学生的学号