數(shù)據(jù)庫原理與應(yīng)用：第9章 連接查詢

數(shù)據(jù)庫原理與應(yīng)用

第9章查詢

9.1單表查詢

9.2連接查詢

9.3嵌套查詢9.2連接查詢

前面的查詢都是針對一個表進(jìn)行的，當(dāng)查詢同時涉及兩個或兩個以上的表時，稱為連接查詢數(shù)據(jù)表之間的聯(lián)系是通過表的字段值來體現(xiàn)的，這種字段稱為連接字段。連接操作的目的就是通過加在連接字段的條件將多個表連接起來，以便從多個表中查詢數(shù)據(jù)。連接操作的執(zhí)行過程嵌套循環(huán)法首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢表的連接方法有兩種：方法1：表之間滿足一定的條件的行進(jìn)行連接，此時FROM子句中指明進(jìn)行連接的表名，WHERE子句指明連接條件。方法2：利用關(guān)鍵字JOIN進(jìn)行連接具體分為以下幾種：CROSSJOIN：會將一個表的每條記錄和另一表的每條記錄拼接成新的記錄行。INNERJOIN

：顯示符合條件的記錄；LEFT（OUTER）JOIN：顯示符合條件的數(shù)據(jù)行以及左邊表中不符合條件的數(shù)據(jù)行，此時右邊數(shù)據(jù)行會以NULL來顯示，此稱為左外連接；RIGHT（OUTER）JOIN：顯示符合條件的數(shù)據(jù)行以及右邊表中不符合條件的數(shù)據(jù)行，此時左邊數(shù)據(jù)行會以NULL來顯示，此稱為右外連接；FULL（OUTER）JOIN：顯示符合條件的數(shù)據(jù)行以及左邊表和右邊表中不符合條件的數(shù)據(jù)行，此時缺乏數(shù)據(jù)的數(shù)據(jù)行會以NULL來顯示；當(dāng)將JOIN關(guān)鍵詞放于FROM子句中時，應(yīng)有關(guān)鍵詞ON與之相對應(yīng)，以表明連接的條件。連接查詢（續(xù)）連接查詢的主要類型

廣義笛卡爾積 等值連接(含自然連接)與非等值連接自身連接 外連接查詢 復(fù)合條件連接查詢一、廣義笛卡爾積(交叉連接：CROSSJOIN)又稱為非限制連接，不帶連接謂詞的連接，它將兩個表不加任何約束地組合在一起，也就是將第一個表的每條記錄分別與第二個表的每條記錄組成新記錄。交叉連接有兩種語法格式：1、SELECT列名列表FROM表名1,表名22、SELECT列名列表FROM表名1CROSSJOIN表名2例：方法1：

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

SELECTstudent.*,score.*FROMstudentCROSSJOINscore二、等值與非等值連接查詢

（內(nèi)連接：INNERJOIN）

等值連接（自然連接）、非等值連接[例32]查詢每個學(xué)生及其選修課程的情況。方法1：SELECTstudent.*,score.*FROMstudent,score

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

為連接條件，sno為連接字段。連接條件的一般格式為：

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

其中,比較運(yùn)算符主要有：＝、＞、＜、＞＝、＜＝、!＝。當(dāng)比較運(yùn)算符為“＝“時，稱為等值連接，其他情況為非等值連接。等值連接本例中，SELECT子句和WHERE子句中的列名前都加上了表名前綴，是因為兩個表中的列名sno相同，必須用表名前綴來確切說明所指列屬于哪個表，以避免二義性。如果列名在參加連接的各表中是唯一的，就不必加前綴。等值連接方法2：SELECTstudent.*,score.*FROMstudentINNERJOINscoreON

student.sno=score.sno等值連接等值連接結(jié)果自然連接等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。[例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自然連接非等值連接查詢連接運(yùn)算符不是“=”的連接操作

例：假設(shè)使用如下命令建立了一個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')查詢所有學(xué)生的sno,cno和rank列。SELECTsno,cno,degree,rankFROMscore,grade

WHEREdegreeBETWEENlowANDupp三、自身連接當(dāng)一個表與自已進(jìn)行連接操作時，稱為表的自身連接。自身連接就是一張表的兩個副本之間的內(nèi)連接，使用它可以將同一個表的不同行連接起來。使用自身連接時，必須為表指定兩個不同的別名，使之在邏輯上成為兩個表以示區(qū)別。由于所有屬性名都是同名屬性，因此必須使用表名前綴。自身連接（續(xù)）[例34]查詢選修3-105課程的成績高于109課程成績的所有學(xué)生記錄，并按成績從高到低排列。自身連接（續(xù)）

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自身連接（續(xù)）查詢結(jié)果四、外連接（OUTERJOIN）

外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作除輸出滿足連接條件的元組外，還可以使一個表（左外連接，右外連接）或兩個表（全外連接）中的不滿足連接條件的元組也出現(xiàn)在結(jié)果中。1、左外連接：LEFT[OUTER]JOIN將左表的所有記錄分別與右表的每一條記錄進(jìn)行連接組合，結(jié)果集中除顯示符合條件的數(shù)據(jù)行以外，還顯示左邊表中不符合條件的數(shù)據(jù)行，此時右邊數(shù)據(jù)行會以NULL來顯示。例：SELECTstudent.sno,sname,ssex,sbirthday,class,cno,degreeFROMstudentLEFTOUTERJOINscoreONstudent.sno=score.sno1、左外連接：LEFT[OUTER]JOIN2、右外連接：RIGHT

[OUTER]JOIN將右表的所有記錄分別與左表的每一條記錄進(jìn)行連接組合，結(jié)果集中除顯示符合條件的數(shù)據(jù)行以外，還要顯示右邊表中不符合條件的數(shù)據(jù)行，此時左邊數(shù)據(jù)行會以NULL來顯示。例：

SELECTsno,degree,o,cname,tnoFROMscoreRIGHTOUTERJOINcourseONo=o2、右外連接：RIGHT[OUTER]JOIN顯示符合條件的數(shù)據(jù)行以及左邊表和右邊表中不符合條件的數(shù)據(jù)行，此時缺乏數(shù)據(jù)的數(shù)據(jù)行會以NULL來顯示。3、全外連接：FULL[OUTER]JOINsnosname001王輝002程偉剛003薛東清004孫強(qiáng)snodegree00180003900047800587T1T2左外連接T1和T2:SELECTT1.*,T2.*FROMT1LEFTJOINT2ONT1.sno=T2.sno結(jié)果：T1.snosnameT2.snodegree001 王輝 001 80002 程偉剛 NULL NULL003 薛東清 003 90004 孫強(qiáng) 004 78右外連接T1和T2:SELECTT1.*,T2.*FROMT1RIGHTJOINT2ONT1.sno=T2.sno結(jié)果：T1.snosnameT2.snodegree001 王輝 001 80003 薛東清 003 90004 孫強(qiáng) 004 78NULL NULL 005 87全外連接T1和T2:SELECTT1.*,T2.*FROMT1FULLJOINT2ONT1.sno=T2.sno結(jié)果：T1.snosnameT2.snodegree001 王輝 001 80002 程偉剛 NULL NULL003 薛東清 003 90004 孫強(qiáng) 004 78NULL NULL 005 87五、復(fù)合條件連接當(dāng)查詢中涉及多個條件（連接條件、查詢條件）時，稱為復(fù)合條件連接[例35]查詢選修3-105號課程且成績在90分以上的所有學(xué)生的學(xué)號、姓名。方法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>