数据库原理与应用：第9章 连接查询

数据库原理与应用

第9章查询

9.1单表查询

9.2连接查询

9.3嵌套查询9.2连接查询

前面的查询都是针对一个表进行的，当查询同时涉及两个或两个以上的表时，称为连接查询数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。连接操作的执行过程嵌套循环法首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕表的连接方法有两种：方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接条件。方法2：利用关键字JOIN进行连接具体分为以下几种：CROSSJOIN：会将一个表的每条记录和另一表的每条记录拼接成新的记录行。INNERJOIN

：显示符合条件的记录；LEFT（OUTER）JOIN：显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示，此称为左外连接；RIGHT（OUTER）JOIN：显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以NULL来显示，此称为右外连接；FULL（OUTER）JOIN：显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以NULL来显示；当将JOIN关键词放于FROM子句中时，应有关键词ON与之相对应，以表明连接的条件。连接查询（续）连接查询的主要类型

广义笛卡尔积 等值连接(含自然连接)与非等值连接自身连接 外连接查询 复合条件连接查询一、广义笛卡尔积(交叉连接：CROSSJOIN)又称为非限制连接，不带连接谓词的连接，它将两个表不加任何约束地组合在一起，也就是将第一个表的每条记录分别与第二个表的每条记录组成新记录。交叉连接有两种语法格式：1、SELECT列名列表FROM表名1,表名22、SELECT列名列表FROM表名1CROSSJOIN表名2例：方法1：

SELECTstudent.*,score.*FROMstudent,score方法2：

SELECTstudent.*,score.*FROMstudentCROSSJOINscore二、等值与非等值连接查询

（内连接：INNERJOIN）

等值连接（自然连接）、非等值连接[例32]查询每个学生及其选修课程的情况。方法1：SELECTstudent.*,score.*FROMstudent,score

WHEREstudent.sno=score.snostudent.sno=score.sno

为连接条件，sno为连接字段。连接条件的一般格式为：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

其中,比较运算符主要有：＝、＞、＜、＞＝、＜＝、!＝。当比较运算符为“＝“时，称为等值连接，其他情况为非等值连接。等值连接本例中，SELECT子句和WHERE子句中的列名前都加上了表名前缀，是因为两个表中的列名sno相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果列名在参加连接的各表中是唯一的，就不必加前缀。等值连接方法2：SELECTstudent.*,score.*FROMstudentINNERJOINscoreON

student.sno=score.sno等值连接等值连接结果自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。[例33]对[例32]用自然连接完成。方法1:SELECTstudent.sno,sname,ssex,sbirthday,class,

cno,degreeFROMstudent,scoreWHEREstudent.sno=score.sno方法2：SELECTstudent.sno,sname,ssex,sbirthday,class,

cno,degreeFROMstudentINNERJOINscoreONstudent.sno=score.sno自然连接非等值连接查询连接运算符不是“=”的连接操作

例：假设使用如下命令建立了一个grade表。CREATETABLEgrade(low

int,upp

int,rankchar(1))INSERTINTOgradevalues(90,100,'A')INSERTINTOgradevalues(80,89,'B')INSERTINTOgradevalues(70,79,'C')INSERTINTOgradevalues(60,69,'D')INSERTINTOgradevalues(0,59,'E')查询所有学生的sno,cno和rank列。SELECTsno,cno,degree,rankFROMscore,grade

WHEREdegreeBETWEENlowANDupp三、自身连接当一个表与自已进行连接操作时，称为表的自身连接。自身连接就是一张表的两个副本之间的内连接，使用它可以将同一个表的不同行连接起来。使用自身连接时，必须为表指定两个不同的别名，使之在逻辑上成为两个表以示区别。由于所有属性名都是同名属性，因此必须使用表名前缀。自身连接（续）[例34]查询选修3-105课程的成绩高于109课程成绩的所有学生记录，并按成绩从高到低排列。自身连接（续）

x表（score表）

y表（score表）

方法1：SELECTx.*,y.*FROMscorex,scoreyWHEREo='3-105'ANDo='3-105'ANDy.sno='109'ANDx.degree>y.degreeORDERBYx.degreeDESC方法2：SELECTx.*,y.*FROMscorexINNERJOINscoreyONo='3-105'ANDo='3-105'ANDy.sno='109'ANDx.degree>y.degreeORDERBYx.degreeDESC自身连接（续）查询结果四、外连接（OUTERJOIN）

外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作除输出满足连接条件的元组外，还可以使一个表（左外连接，右外连接）或两个表（全外连接）中的不满足连接条件的元组也出现在结果中。1、左外连接：LEFT[OUTER]JOIN将左表的所有记录分别与右表的每一条记录进行连接组合，结果集中除显示符合条件的数据行以外，还显示左边表中不符合条件的数据行，此时右边数据行会以NULL来显示。例：SELECTstudent.sno,sname,ssex,sbirthday,class,cno,degreeFROMstudentLEFTOUTERJOINscoreONstudent.sno=score.sno1、左外连接：LEFT[OUTER]JOIN2、右外连接：RIGHT

[OUTER]JOIN将右表的所有记录分别与左表的每一条记录进行连接组合，结果集中除显示符合条件的数据行以外，还要显示右边表中不符合条件的数据行，此时左边数据行会以NULL来显示。例：

SELECTsno,degree,o,cname,tnoFROMscoreRIGHTOUTERJOINcourseONo=o2、右外连接：RIGHT[OUTER]JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以NULL来显示。3、全外连接：FULL[OUTER]JOINsnosname001王辉002程伟刚003薛东清004孙强snodegree00180003900047800587T1T2左外连接T1和T2:SELECTT1.*,T2.*FROMT1LEFTJOINT2ONT1.sno=T2.sno结果：T1.snosnameT2.snodegree001 王辉 001 80002 程伟刚 NULL NULL003 薛东清 003 90004 孙强 004 78右外连接T1和T2:SELECTT1.*,T2.*FROMT1RIGHTJOINT2ONT1.sno=T2.sno结果：T1.snosnameT2.snodegree001 王辉 001 80003 薛东清 003 90004 孙强 004 78NULL NULL 005 87全外连接T1和T2:SELECTT1.*,T2.*FROMT1FULLJOINT2ONT1.sno=T2.sno结果：T1.snosnameT2.snodegree001 王辉 001 80002 程伟刚 NULL NULL003 薛东清 003 90004 孙强 004 78NULL NULL 005 87五、复合条件连接当查询中涉及多个条件（连接条件、查询条件）时，称为复合条件连接[例35]查询选修3-105号课程且成绩在90分以上的所有学生的学号、姓名。方法1：SELECTstudent.sno,student.snameFROMstudent,scoreWHEREstudent.sno=score.snoAND/*连接条件*/

o='3-105'AND/*其他查询条件*/

score.degree>90/*其他查询条件*/方法2：SELECTstudent.sno,student.snameFROMstudentINNERJOINscoreONstudent.sno=score.snoAND

o='3-105'AND

score.degree>