用SQL语句操作Oracle数据库--数据查询详解（下篇）

数据查询是数据库的核心操作。上一篇文章我们介绍了单表查询操作，本文将继续介绍另一种重要的查询类型—[ 多表查询 ]（涉及多个表的数据查询）。

本文我们将使用以下三个表来进行查询操作：

TABLE1:
S(学生基本信息表)

TABLE2:
C(课程表)

TABLE3:
SC(学生选修课程信息表)

---

1.连接查询

连接查询是数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合连接查询等等。

关系代数相关知识引入：

关系代数是一种抽象的查询语言，它用对关系的运算来表达查询。关系代数按运算符的不同可分为 _{传统的集合运算} 和 _{专门的关系运算}两类。专门的关系运算包括 _选择、_投影、_除运算、 连接 等。
连接（join）也称为 θ连接。它是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。连接运算中有两种最为常用的连接，一种是等值连接(equijoin)、另一种是自然连接（natural join）。θ为“=”的连接运算为等值连接。它是从关系R与S的笛卡尔积中选取A、B属性值相等的那些元组。自然连接是特殊的等值连接。它要求两个关系中进行比较的分量必须是同名的属性组，并且在结果中将重复的属性列去掉。
我们可以知道，在数据库中的一张二维表其实也是一个关系，所以上述专门的关系运算概念与法则同样适用于二维表之间。

---

①等值连接查询与非等值连接查询：

连接查询的WHERE子句中用来连接两个表的条件称为 连接条件(连接谓词)，一般格式如下：
TABLE1.COLUMN1 比较运算符 TABLE2.CLOUMN2
其中的比较运算符主要有六种：=、>、<、<=、>=、!=(<>)等等
连接谓词亦可以使用以下形式：
TABLE1.cloumn1 BETWEEN TABLE2.cloumn2 AND TABLE2.cloumn3
当连接运算符为“=”时，就叫做 等值连接，否则使用其它运算符时就叫做 非等值连接连接谓词中的列名就叫做 连接字段。注意：连接条件中的各连接字段类型必须是可比的，名字可以不相同。

---

SELECT S.*, SC
FROM S,SC
WHERE S.sno = SC.sno;

查询结果如下：

上述例子中，我们等值连接查询了每个学生与其选修的课程信息。关系数据库管理系统在执行该连接查询操作时的一种可能的过程是：首先在S表中找到第一个元组，然后从头开始扫描SC表，如果找到与S表中的第一个元组的SNO值相等的SC中的元组，就将其与S表中的第一个元组连接起来，组成结果表中的第一个元组，并以此类推，直到S表中的元组全部处理完为止。这其实 是 嵌套循环链接算法 的基本思想。
在S表与SC表中的sno是连接字段，从输出的结果不难看出表中有相同的两个属性列，过我们可以使用自然连接来将重复的属性列去掉：

SELECT S.*,CNO,GRADE
FROM S, SC
WHERE SC.SNO = S.SNO;

查询结果如下：

---

例1：
查询选修3号课程且成绩在80分以上的学生的姓名和学号

SELECT S.SNO,SNMAE
FROM S,SC
WHERE S.SNO = SC,SNO AND CNO = '2' AND GRADE > 80;

查询结果如下：

对于此类问题，我们还有一种使执行过程更为高效的查询方法，那就是中间表法，我们先从SC表种找出cno=‘2’ 并且grade>80的元组组成一个中间表,取名为SM表，再和S表中满足连接的元组进行连接得到相同的最终结果。

SELECT S.SNO,SNAME
FROM S,(SELECT SNO FROM SC 
		WHERE CNO = '3' AND GRADE>80 ) SM
WHERE S.SNO = SM.SNO;

---

②自身连接查询：
连接操作不仅可以在不同的表间进行，亦可以是一个表与自身进行连接，称为表的 自身连接
例2：
查询一门课的间接先修课（先修课的先修课）

SELECT first.Cno,second.Cpno
FROM C first, C second
WHERE first.Cpno = second.Cno;

查询结果如下：

上述示例中，我们对C表进行了自身连接查询，为了便于区分我们分别为C表取了别名即first、second。由显示的结果我们可以发现有的课程没有间接先修课，但任然显示在了表中，故我们可进一步添加条件对查询结果，进行修改使之不显示；

SELECT first.Cno,second.Cpno
FROM C first, C second
WHERE first.Cpno = second.Cno AND second.Cpno IS NOT NULL;

优化后的查询结果如下：

---

③外连接：
在通常的连接操作中，只有满足连接条件的元组才会作为结果输出，如：在查询选修了课程的学生的学号时，由于S表中有些学生没有选修课程，，在SC表中没有相应的元组，导致S表中这些元组在连接时被舍弃了，这些舍弃的元组称为[ 悬浮元组 ]。外链接可分为<左外连接>和< 右外连接>
经过细细思考，我们不难发现，之所以会出现这种舍弃现象，其本质原因在于，进行查询时，我们选取的【基准表】（参照表）不同，类比于物理学中的【参考系】，当我们选取的参考系不同时，我们对于某一物体运动状态的描述一般是不同的。但选定的参考系始终是处于静止状态的。
外连接操作的一般格式如下（以S表和SC表举例）：
S left/right outer join SC on (join conditions)；
说明：left表示左外连接（以左边的表为基准），right表示右外连接（以右边的表为基准），join conditions表示进行外连接的连接条件。

#S左外连接SC：
SELECT S.*,SC.CNO,GRADE
FROM S LEFT OUTER JOIN SC ON(S.SNO = SC.SNO);
#S右外连接SC：
SELECT S.*,SC.CNO,GRADE
FROM S RIGHT OUTER JOIN SC ON(S.SNO = SC.SNO);

查询结果如下：
由显示结果我们可以看出，进行左连接时（以S表为基准），SC表中没有的元组任然显示出来；
进行右链接查询时（以SC表为基准），SC表中没有的元组被舍弃。

---

2.嵌套查询

在SQL语句中，一个SELECT---FROM---WHERE语句成为一个查询块。讲一个查询块嵌入到另一个查询块的WHERE子句或HAVING短语的条件中的查询称为<嵌套查询（nested query）>。在外层的查询块称为<外层查询（父查询）>，嵌在内层的查询块称为<内层查询（子查询）>。嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层潜逃的方式来构造程序正是SQL中“ 结构化 ”的含义所在。
[注意]：子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序]。

---

①带有谓词IN的子查询：
在嵌套查询中，子查询的结果往往是一个集合（或者说也是一张二维表），因此，谓词IN在嵌套查询中较为常用。
例1：
查询与李勇在同一个系的学生。

SELECT *FROM S
WHERE Sdept IN(
	SELECT Sdept FROM S
	WHERE Sname = '李勇');

查询结果如下：

例2：
查询选修了课程名为“数学”的学生学号与姓名。

#方法一：嵌套查询
SELECT S.SNO, SNAME FROM S
     WHERE SNO IN (
     		SELECT SNO FROM SC
     		WHERE CNO IN (
     			SELECT CNO FROM C
     			WHERE CNAME LIKE '数学'));
#方法二：使用连接查询
 SELECT S.SNO,SNAME
    FROM S,SC,C
    WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND CNAME = '数学';

查询结果如下：


有些嵌套查询可以使用连接运算代替，所以在实际应用中，能够用连接运算表达的查询我们一般尽可能采用连接运算。在上面的两个例中，子查询的查询条件不依赖于父查询，称为 <不相关子查询>。

---

②带有比较运算符的子查询：
带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当确切知道内层查询返回的是单个值时，可用“>”、“<”等比较运算符连接。
事实上，我们所说的含有谓词IN的子查询其实也属于带有比较运算符的子查询的范畴。因为当内查询的结果是单个值时，可以用=代替IN
例1：
查询每个学生的单科分值超过其选修的所有课程的平均分的课程号，课程名。

 SELECT X.SNO, X.CNO
    FROM SC X
    WHERE GRADE>(SELECT AVG(GRADE) FROM SC Y
     			 WHERE X.SNO = Y.SNO);

查询结果如下：

本例中的子查询条件依赖于父查询，所以这类查询叫做 <相关子查询>。求解相关子查询不能向求解相关父查询那样一次将查询求解出来，然后去求解父查询。由于内层查询与外层查询相关联，因此必须反复求值。

---

③带有ANY(有的系统用SOME或ALL谓词的子查询:

子查询返回单值时可以使用比较运算符，但返回多值时要用ANY或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。如：> ANY表示大于查询结果中的某一个值，> ALL则表示大于查询结果中的所有（任意）值。
例1：
查询非计算机院中比计算机院所有学生年龄都小的学生的姓名及年龄。

#方法一：直接法
SELECT SNAME,SAGE
     FROM S
     WHERE SAGE < ALL(
     		SELECT SAGE
     		FROM S
     		WHERE SDEPT LIKE 'CS'
     		)AND SDEPT <>'CS';
 #方法二：（间接法）等价转换
  SELECT SNAME,SAGE
   FROM S
   WHERE SAGE<(SELECT MIN(SAGE) FROM S 
     		   WHERE SDEPT='CS')AND SDEPT <> 'CS';

查询结果如下;


小结：
实际上，博主认为，带有ANY(有的系统用SOME或ALL谓词的子查询，可以说分别对应数学中不等式的存在性与恒成立与问题,因此，可以用聚集函数中的最值函数进行等价转换。事实上，用聚集函数实现子查询的效率通常比用ANY与ALL的查询效率要高。
ANY、ALL等价转换的聚集函数：

	=	<>或!=	<	<=	>	>=
ANY	IN	—	<MAX	<=MAX	>MIN	>=MIN
ALL	—	NOT IN	<MIN	<=MIN	>MAX	>=MAX

---