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

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

第9章查詢

9.1單表查詢

9.2連接查詢

9.3嵌套查詢9.2連接查詢

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

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

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

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

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

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

等值連接（自然連接）、非等值連接[例32]查詢每個(gè)學(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)算符為“＝“時(shí)，稱為等值連接，其他情況為非等值連接。等值連接本例中，SELECT子句和WHERE子句中的列名前都加上了表名前綴，是因?yàn)閮蓚€(gè)表中的列名sno相同，必須用表名前綴來(lái)確切說(shuō)明所指列屬于哪個(gè)表，以避免二義性。如果列名在參加連接的各表中是唯一的，就不必加前綴。等值連接方法2：SELECTstudent.*,score.*FROMstudentINNERJOINscoreON

student.sno=score.sno等值連接等值連接結(jié)果自然連接等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。[例33]對(duì)[例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è)使用如下命令建立了一個(gè)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)一個(gè)表與自已進(jìn)行連接操作時(shí)，稱為表的自身連接。自身連接就是一張表的兩個(gè)副本之間的內(nèi)連接，使用它可以將同一個(gè)表的不同行連接起來(lái)。使用自身連接時(shí)，必須為表指定兩個(gè)不同的別名，使之在邏輯上成為兩個(gè)表以示區(qū)別。由于所有屬性名都是同名屬性，因此必須使用表名前綴。自身連接（續(xù)）[例34]查詢選修3-105課程的成績(jī)高于109課程成績(jī)的所有學(xué)生記錄，并按成績(jī)從高到低排列。自身連接（續(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ū)別普通連接操作只輸出滿足連接條件的元組外連接操作除輸出滿足連接條件的元組外，還可以使一個(gè)表（左外連接，右外連接）或兩個(gè)表（全外連接）中的不滿足連接條件的元組也出現(xiàn)在結(jié)果中。1、左外連接：LEFT[OUTER]JOIN將左表的所有記錄分別與右表的每一條記錄進(jìn)行連接組合，結(jié)果集中除顯示符合條件的數(shù)據(jù)行以外，還顯示左邊表中不符合條件的數(shù)據(jù)行，此時(shí)右邊數(shù)據(jù)行會(huì)以NULL來(lái)顯示。例：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í)左邊數(shù)據(jù)行會(huì)以NULL來(lái)顯示。例：

SELECTsno,degree,o,cname,tnoFROMscoreRIGHTOUTERJOINcourseONo=o2、右外連接：RIGHT[OUTER]JOIN顯示符合條件的數(shù)據(jù)行以及左邊表和右邊表中不符合條件的數(shù)據(jù)行，此時(shí)缺乏數(shù)據(jù)的數(shù)據(jù)行會(huì)以NULL來(lái)顯示。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)查詢中涉及多個(gè)條件（連接條件、查詢條件）時(shí)，稱為復(fù)合條件連接[例35]查詢選修3-105號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生的學(xué)號(hào)、姓名。方法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>