06 第六章 关联检索

第六章关联检索

本章主要内容6.1前言了解关联查询6.2笛卡尔关联描述概念。6.3相等关联描述概念以及应用。6.4自我关联了解概念以及应用方式。6.5外部关联了解概念以及应用方式。本章主要内容6.6集合运算掌握运算概念和应用。6.7总结6.1前言

本章主要介绍表关联查询的方式。由于在实际开发过程中，关联查询是占很大比重的。同时我们需要注意表关联中，传统写法和JOIN写法的区别。两种方式在开发中的应用都比较广泛。6.2笛卡尔关联

当检索时,使用多个表时,如果条件不完全,输出的结果就会有规则的出现重复数据。而这种重复结果集就是笛卡尔乘积,我们把这种关联方式叫笛卡尔关联。通常笛卡尔关联是一种错误的关联方式。（参看例6.1）

SELECTA.stuname,B.scoreFROMtbl_studentinfoA,tbl_scoreinfoB6.3相等关联

最常用的联接类型是对一个公共列中存储了相同数据的几个表创建的，这种联接称为相等关联。相等关联分为传统方法和JOIN方式。（参看例6.2和例6.3）例6.2(查询学生名和成绩)SELECTA.stuname,B.scoreFROMtbl_studentinfoA,tbl_scoreinfoBWHEREA.stuno=B.stunoSELECTA.stuname,B.scoreFROMtbl_studentinfoAJOINtbl_scoreinfoBONA.stuno=B.stuno6.3相等关联

例6.3(查询学生名和成绩已经课程名)SELECTA.stuname,B.score,C.classnameFROMtbl_studentinfoA,tbl_scoreinfoB,tbl_classinfoCWHEREA.stuno=B.stunoANDB.classno=C.classnoSELECTA.stuname,B.score,C.classnameFROMtbl_scoreinfoBJOINtbl_studentinfoAONA.stuno=B.stunoJOINtbl_classinfoCONB.classno=C.classno6.3相等关联

注意:多表关联中,追加的条件应该是可以唯一确定关联表的数据.如果不能唯一,或出现笛卡尔集合的话,肯定是有条件没有追加完全。6.4自我关联

自我关联的两个表,其实就是一个表,只是根据业务的需要,看成两个不同的表

。根据表6.1修改学生表数据，然后查询每个学生的上级组长的姓名（参看例6.4）6.4自我关联

例6.4(查询学号和学生名以及上级组长名)SELECTA.stuno,A.stuname,B.stunameFROMtbl_studentinfoA,tbl_studentinfoBWHEREA.upstuno=B.stunoSELECTA.stuno,A.stuname,B.stunameFROMtbl_studentinfoAINNERJOINtbl_studentinfoBONA.upstuno=B.stuno6.5外部关联

当用户需要查询结果中包括存在于一个表中但是在另一个表中没有对应的记录的时候，就需要使用外部关联查询了。查询所有学生日语课成绩（参看例6.6，例6.7，例6.8）6.5外部关联

例6.6(查询所有学生日语的成绩)SELECTA.stuno,A.stuname,B.scoreFROMtbl_studentinfoA,tbl_scoreinfoBWHEREA.stuno=B.stuno(+)ANDB.classno='002';SELECTA.stuno,A.stuname,B.scoreFROMtbl_studentinfoALEFTOUTERJOINtbl_scoreinfoBONA.stuno=B.stunoANDB.classno='002'6.5外部关联

例6.7(查询所有学生日语的成绩和课程名，学生名)SELECTA.stuno,A.stuname,B.score,C.classnameFROMtbl_studentinfoA,tbl_scoreinfoB,tbl_classinfoCWHEREA.stuno=B.stuno(+)ANDB.classno='002'ANDB.classno=C.classno(+)SELECTA.stuno,A.stuname,B.score,C.classnameFROMtbl_scoreinfoBRIGHTOUTERJOINtbl_studentinfoAONA.stuno=B.stunoANDB.classno='002'LEFTOUTERJOINtbl_classinfoCONB.classno=C.classno6.5外部关联

例6.8(查询所有学生日语的成绩和课程名，学生名，没成绩的为0)SELECTA.stuno,A.stuname,NVL(B.score,0)score,C.classnameFROMtbl_studentinfoA,tbl_scoreinfoB,tbl_classinfoCWHEREA.stuno=B.stuno(+)ANDB.classno='002';ANDB.classno=C.classno(+)SELECTA.stuno,A.stuname,NVL(B.score,0),C.classnameFROMtbl_scoreinfoBRIGHTOUTERJOINtbl_studentinfoAONA.stuno=B.stunoANDB.classno='002'LEFTOUTERJOINtbl_classinfoCONB.classno=C.classno6.6集合运算

在数据库查询中我们有时需要将数据库的检索结果集进行合并，则这个时候需要用到集合运算（UNION与UNIONALL）。查询参加日语和英语考试的学生成绩（参看例6.9）。方式1：

SELECTA.stuname,B.classname,C.scoreFROMtbl_studentinfoA,tbl_classinfoB,tbl_scoreinfoCWHEREA.stuno=C.stunoANDB.classno=C.classnoANDB.classnamein('英语','日语');6.6集合运算

方式2：

SELECTA.stuname,B.classname,C.scoreFROMtbl_studentinfoA,tbl_classinfoB,tbl_scoreinfoCWHEREA.stuno=C.stunoANDB.classno=C.classnoAND(B.classname='英语'ORB.classname='日语')6.6集合运算

方式3：SELECTA.stuname,B.classname,C.scoreFROMtbl_studentinfoA,tbl_classinfoB,tbl_scoreinfoCWHEREA.stuno=C.stunoANDB.classno=C.classnoANDB.classname='英语'UNIONALLSELECTA.stuname,B.classname,C.scoreFROMtbl_studentinfoA,tbl_classinfoB,tbl_scoreinfoCWHEREA.stuno=C.stunoANDB.classno=C.classnoANDB.classname='日语'6.6集合运算

查询参加日语和英语考试的学生成绩，结果按照课程与成绩降序排序（参看例6.10）。

SELECTstuname,classname,scoreFROM(SELECTA.stuname,B.classname,C.scoreFROMtbl_studentinfoA,tbl_classinfoB,tbl_scoreinfoCWHEREA.stuno=C.stunoANDB.classno=C.classnoANDB.classno='002'UNIONALLSELECTA.stuname,B.classname,C.scoreFROMtbl_studentinfoA,tbl_classinfoB,tbl_scoreinfoCWHEREA.stuno=C.stunoANDB.classno=C.classnoANDB.classno='003')ORDERBYclassname,scoreDESC;6.6集合运算

注意：

1.UNION，UNIONALL集合运算很多情况下可以和OR，IN语句互换。

2.UNION与UNIONAL