数据库第五章 复杂查询

一、连接查询

1.定义：当一条查询涉及到多个表时，称为连接查询。包括：等值连接查询和非等值连接查询、自然连接查询、自身连接查询、多表查询、外连接查询。

2.前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

3.注意事项：如果我们使用了表的别名， 在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错

4.自然连接查询：是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉，称为自然连接。

SELECT * FROM Students S NATURAL JOIN Reports R ；

注：①将两个关系中所有相同的属性列进行等值比较  ②运行结果中去掉所有重复的属性列

例：请写出下列查询的SQL语句，“用自然连接查询学生的选修课程情况，结果显示学号、姓名、课程号、课程名和成绩”：

SELECT Sno, Sname, Cno, Cname, Grade

FROM students NATURAL JOIN reports 

           NATURAL  JOIN courses;

5.自身连接查询：将同一个表与自己进行连接的查询。其基本方法是在SELECT语句的FROM子句中将同一个表取两个不同的别名。（自己与自己连）

例：查询每一门课的间接先修课

SELECT A.Cno, A.Cname, A.Pre_Cno, B.Pre_Cno

FROM Courses A, Courses B

WHERE A.Pre_Cno = B.Cno

6.多表连接查询：两个以上的表连接完成的查询。

例：查询每个学生的学号、姓名、选修的课程名及成绩

①SELECT S.Sno, Sname, Cname, Grade

FROM Students S, Reports R, Courses C

WHERE S.Sno=R.Sno AND R.Cno=C.Cno;

 ②SELECT S.Sno, Sname, Cname, Grade

FROM Students S INNER JOIN Reports R ON S.Sno=R.Sno

INNER JOIN Courses C ON R.Cno=C.Cno;（内连接）

使用USING完成连接 ：
例：查询每个学生的学号、姓名、选修的课程名及成绩

SELECTS.Sno, Sname, Cname, Grade

FROM Students S INNER JOIN Reports R ON S.Sno=R.Sno

                              INNER JOIN Courses C ON R.Cno=C.Cno;

注意：USING只能和JOIN一起使用，要求两个关联字段在关联表中名称一致，只能表示关联字段值相等。超过三个表禁止JOIN

7.外连接：选定连接操作中涉及的某个表A为基准，即使另一个表B中没有与之匹配的记录，其结果表中也要求包括表A的所有元组。若表 B中没有与之匹配的记录，结果表中涉及表B的属性列全部取空值。外连接分为：左连接和右连接。

例：查询每个学生的基本情况和选课情况

SELECT S.Sno, Sname, Sgender, Sbirth, Dno, Cno, Grade

FROM Students S LEFT JOIN Reports R ON S.Sno=R.Sno;(Students表中的数据记录全部包含）

8.连接图：

二、嵌套查询

1.定义:将一个查询块(SELECT-FROM-WHERE)嵌套在另一个查询块的WHERE子句或HAVING短语条件中的查询。基本格式：

SELECT ... FROM ...   

WHERE ...=

(SELECT... FROM ... WHERE ...)

子查询的结果可以是单个数据值，元组的属性值，或一个新的关系表。

2.常用的嵌套查询:(1)带谓词IN的嵌套查询(2)带有比较运算符的嵌套查询(3)带谓词ANY或ALL的嵌套查询(4)带谓词EXISTS的嵌套查询

3.带谓词IN的嵌套查询:

①例:查询选修了编号为'112p0024'的课程的学生姓名(Sname)和所在学院(Dname)
SELECT Sname, Dname

FROM Students S, Department D

WHERE S.Dno=D.Dno AND    父查询

Sno IN
(SELECT Sno FROM Reports WHERE Cno='112p0024') 子查询，子查询中不能用order by

②例:查询本学期选课超过1门的学生人数，没有成绩的选课表示本学期正在选修的课程。
SELECT COUNT(*)

FROM Students
WHERE Sno IN
（SELECT Sno FROM Reports

WHERE Grade IS NULL
GROUP BY Sno
HAVING COUNT(*)>1)

4.带比较运算符的嵌套查询

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

例:查找高于平均年龄的学生信息

SELECT* FROM Students

WHERE (YEAR(GETDATE())-YEAR(Sbirth))>
（SELECT
AVG(YEAR(GETDATE())-YEAR(Sbirth)) FROM Students )

5.带谓词ANY或ALL的嵌套查询：必须与比较运算符同时使用。ANY表示某个，ALL表示所有

例:查询非计算机与信息工程学院的不超过该学院所有学生的年龄的学生姓名(Sname)和出生日期(Sbirth)

SELECT Sname, Sbirth FROM Students

WHERE Dno<>（不等于）
（SELECT Dno FROM Department

WHERE Dname='计算机与信息工程学院')

AND Sbirth >=ALL
(SELECT Sbirth
FROM Students

WHERE Dno=(

（SELECT Dno FROM Department

WHERE Dname='计算机与信息工程学院'));

6.带谓词EXISTS的嵌套查询:不返回任何数据，只产生逻辑值真(True)或假(False)

：带谓词EXISTS的嵌套查询这类查询称为相关子查询
处理过程:
取外层父查询表中的第1个元组
根据它与内层子查询相关的属性值处理内层子查询若WHERE子句返回值为True，则取此元组放入结果表再取父查询表的下一个元组
重复上述过程，直至外层父查询表的元组全部检查完为止.

：用存在量词表示全称量词(∀x)P=¬(∃x)(┐P)
例:查询选修了所有课程的学生学号(Sno)、姓名(Sname)和所在院号(Dno)提示:查询这样的学生，没有一门课程是他不选修的。

7.带子关系的嵌套查询：连接的数据表是一个查询得到的新的关系

三、集合查询

1.标准SQL提供了并操作运算命令UNION，将多个SELECT 语句的结果进行传统的集合并操作（系统会自动去掉重复的元组）。这个操作要求参加UNION操作的各个结果表的列数必须相同且对应属性的数据类型也相同。  标准SQL中没有直接提供集合的交和差的操作，但可用其它条件查询来实现