網(wǎng)頁(yè)制作基礎(chǔ)教程 （Dreamweaver CC）（第3版）課件 項(xiàng)目9 多表查詢應(yīng)用

數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用授課人：陳翠松項(xiàng)目9多表查詢應(yīng)用很多時(shí)候，查詢的數(shù)據(jù)在同一個(gè)表中，所以查詢比較簡(jiǎn)單。當(dāng)用戶需求比較復(fù)雜時(shí)，一個(gè)表的數(shù)據(jù)解決不了問(wèn)題，因此需要從多個(gè)表中獲取數(shù)據(jù)，此時(shí)可以利用多表查詢和子查詢來(lái)完成特定任務(wù)。工作情境目錄01使用連接查詢02使用子查詢03鞏固與小結(jié)04任務(wù)訓(xùn)練01使用連接查詢一、使用連接查詢?nèi)蝿?wù)分析小王發(fā)現(xiàn)，一個(gè)數(shù)據(jù)表中的數(shù)據(jù)有時(shí)無(wú)法滿足用戶的需求，需要同時(shí)從多個(gè)表中選擇數(shù)據(jù)才能滿足用戶的需求。例如，要查詢各個(gè)地方的名人，但名人表celebrity中只有地區(qū)編號(hào)，沒(méi)有地區(qū)名稱，要確認(rèn)地區(qū)名稱需要使用地區(qū)表area中的數(shù)據(jù)，此時(shí)需要2個(gè)表中的數(shù)據(jù)。小王決定采用連接查詢來(lái)實(shí)現(xiàn)多表查詢。小王對(duì)粵文創(chuàng)進(jìn)行分析后得到的任務(wù)清單如下。任務(wù)編號(hào)任務(wù)內(nèi)容任務(wù)9-1查詢廣州有哪些民俗，并顯示id、chineseName和folkName信息任務(wù)9-2查詢中山有哪些名人，并顯示id、chineseName和celebrityName信息任務(wù)9-3查詢東莞有哪些城市名譽(yù)，并顯示id、chineseName和honoraryTitle信息任務(wù)9-4查詢工作計(jì)劃參與人員表participant，并顯示id、planName、userName、duty、requirement和remarks信息任務(wù)9-5查詢工作計(jì)劃表plan，并顯示planId、planName、planMaker、releaseTime、auditTime、startTime、endTime和planContent信息，其中planMaker用來(lái)顯示姓名而不是工號(hào)任務(wù)9-6查詢還沒(méi)有分配工作的工作人員一、使用連接查詢知識(shí)儲(chǔ)備1、使用WHERE關(guān)鍵字實(shí)現(xiàn)多表查詢使用WHERE關(guān)鍵字實(shí)現(xiàn)多表查詢，語(yǔ)法格式如下：SELECT字段列表FROM表名列表WHERE條件表達(dá)式需要說(shuō)明以下幾點(diǎn)。在表名列表中，允許有多個(gè)表，但各個(gè)表名之間需要用逗號(hào)隔開(kāi)。要把兩個(gè)表連接起來(lái)，需要找到兩個(gè)表中相同意義的字段作為連接條件，如果兩個(gè)字段名相同，那么必須在字段名前加表名和“.”，明確指出該字段來(lái)自哪個(gè)表。多個(gè)表要設(shè)置多個(gè)連接條件。條件表達(dá)式不僅可以包括連接條件，還可以同時(shí)包括其他條件，中間用邏輯運(yùn)算符連接。一、使用連接查詢知識(shí)儲(chǔ)備示例9-1查詢廣東各個(gè)地區(qū)的名人，顯示id、chineseName和celebrityName信息。一、使用連接查詢知識(shí)儲(chǔ)備分析：id和celebrityName來(lái)自名人表celebrity，chineseName來(lái)自地區(qū)表area，兩個(gè)表的連接條件是地區(qū)編號(hào)相同，即areaNumber=fkAreaNumber。查詢的結(jié)果與數(shù)據(jù)源有關(guān)，可能會(huì)查到數(shù)據(jù)，也可能查不到數(shù)據(jù)，但一定要確保查詢命令正確。在操作前，可查詢相關(guān)表中的數(shù)據(jù)，根據(jù)連接條件進(jìn)行分析，估計(jì)有哪些結(jié)果數(shù)據(jù)，并與顯示數(shù)據(jù)進(jìn)行對(duì)比，判斷查詢結(jié)果是否正確。名人表celebrity和地區(qū)表area中都有不少數(shù)據(jù)，結(jié)果數(shù)據(jù)比較多，分析比較麻煩。但在企業(yè)實(shí)際工作中，各個(gè)表中的數(shù)據(jù)都是比較多的，所以應(yīng)習(xí)慣面對(duì)大量的數(shù)據(jù)處理。程序代碼如下：SELECT*FROMcelebrity;SELECT*FROMarea;SELECTid,chineseName,celebrityNameFROMcelebrity,areaWHEREareaNumber=fkAreaNumber;一、使用連接查詢知識(shí)儲(chǔ)備示例9-2查詢惠州有哪些名人，并顯示id、chineseName和celebrityName信息。一、使用連接查詢知識(shí)儲(chǔ)備分析：在示例9-1的基礎(chǔ)上，增加一個(gè)查詢條件，即查詢惠州的名人，只在WHERE中添加這條件即可，兩個(gè)條件要同時(shí)滿足，并用AND連接。程序代碼如下：SELECTid,chineseName,celebrityNameFROMcelebrity,areaWHEREareaNumber=fkAreaNumberANDchineseName="惠州";一、使用連接查詢知識(shí)儲(chǔ)備示例9-3查詢id、planName、userName、duty和requirement信息。一、使用連接查詢知識(shí)儲(chǔ)備分析：planName字段來(lái)自工作計(jì)劃表plan，userName來(lái)自工作人員表user，id、duty和requirement來(lái)自工作計(jì)劃參與人員表participant，因此該查詢需要3個(gè)表的數(shù)據(jù)，要設(shè)置表與表之間的連接條件，工作人員表user與工作計(jì)劃參與人員表participant之間有相同的字段userId，可以作為兩個(gè)表的連接條件；工作計(jì)劃表plan與工作計(jì)劃參與人員表participant之間有相同的字段planId，可以作為兩表連接條件。程序代碼如下：SELECTid,planName,userName,duty,requirementFROMuser,plan,participantWHEREuser.userId=participant.userIdANDplan.planId=participant.planId;一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢1）交叉連接使用WHERE關(guān)鍵字實(shí)現(xiàn)多表查詢時(shí)，可以省略條件表達(dá)式，即沒(méi)有連接條件，此時(shí)又稱為交叉連接。交叉連接是指沒(méi)有設(shè)置連接條件的多表查詢，從每個(gè)表中各取一條記錄組新的記錄，因此交叉連接的結(jié)果數(shù)據(jù)量非常大。交叉連接的語(yǔ)法格式如下：SELECT字段列表FROM表名列表一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢示例9-4新建表T1，該表包含3個(gè)CHAR(2)字段，分別為A、B和C；新建表T2，該表包含3個(gè)CHAR(2)字段，分別為A、E和F，每個(gè)表各輸入3條記錄，交叉連接的信息如圖9-1所示。一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢分析：表T1和表T2各有3個(gè)字段，交叉連接后共有6個(gè)字段。在表T1中取第1條記錄，與表T2中的3條記錄分別組合，可以產(chǎn)生3條新記錄；在表T1中取第2條記錄，與表T2中的3條記錄分別組合，又可以產(chǎn)生3條新記錄；在表T1中取第3條記錄，與表T2中的3條記錄分別組合，又可產(chǎn)生3條新記錄。最終得到9條記錄。程序代碼如下：CREATETABLET1(ACHAR(2),BCHAR(2),CCHAR(2));INSERTINTOT1(A,B,C)VALUES("X","1","1"),("Y","1v,"1"),("Z","1","1");CREATETABLET2(ACHAR(2),ECHAR(2),FCHAR(2));INSERTINTOT2(A,E,F)VALUES("X","2","2"),("Y","2","2"),("T","2","2");SELECT*FROMT1;SELECT*FROMT2;SELECT*FROMT1,T2;一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢示例9-5用交叉連接實(shí)現(xiàn)示例9-1，即用交叉連接顯示id、chineseName和celebrityName信息。一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢分析：當(dāng)名人表celebrity中有228條記錄，地區(qū)表area中有21條記錄時(shí)，交叉連接的結(jié)果為4788（228×21）條記錄，數(shù)據(jù)量比較大，運(yùn)行時(shí)展示數(shù)據(jù)的時(shí)間比較長(zhǎng)，需要耐心等待。程序代碼如下：SELECT*FROMcelebrity;SELECT*FROMarea;SELECTid,chineseName,celebrityNameFROMcelebrity,area;一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢2）內(nèi)連接交叉連接產(chǎn)生的數(shù)據(jù)量非常大，因此需要設(shè)置條件過(guò)濾部分?jǐn)?shù)據(jù)，即連接條件。設(shè)置了連接條件的交叉連接稱為內(nèi)連接。如果連接條件運(yùn)算符是等于號(hào)，那么稱為等值連接，否則稱為非等值連接。在等值連接的基礎(chǔ)上去除重復(fù)字段稱為自然連接。在一般情況下，內(nèi)連接一般是指等值連接。內(nèi)連接的語(yǔ)法格式如下：SELECT字段列表FROM表1[INNER]JOIN表2ON表1.字段名=表2.字段名[JOIN表3ON表1.字段名|表2.字段名=表3.字段名…]一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢示例9-6通過(guò)內(nèi)連接查詢表T1和表T2的信息。分析：在如圖9-1所示的交叉連接的結(jié)果中，查找表T1的字段A和表T2的字段A的相同值，發(fā)現(xiàn)第1行的兩個(gè)值都是X，第5行的兩個(gè)值都是Y，其他都不相同，即結(jié)果為第1行和5行數(shù)據(jù)。內(nèi)連接相當(dāng)于找兩個(gè)表指定字段的交集，如圖9-3所示。程序代碼如下：SELECT*FROMT1JOINT2ONT1.A=T2.A;一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢示例9-7用內(nèi)連接實(shí)現(xiàn)示例9-1，即使用內(nèi)連接顯示id、chineseName和celebrityName信息。分析：將WHERE實(shí)現(xiàn)的多表查詢轉(zhuǎn)換為通過(guò)內(nèi)連接方式實(shí)現(xiàn)，只需要將連接條件換一下位置即可。程序代碼如下：SELECTid,chineseName,celebrityNameFROMcelebrityJOINareaONareaNumber=fkAreaNumber;示例9-8用內(nèi)連接查詢佛山有哪些名人，并顯示id、chineseName和celebrityName信息。程序代碼如下：SELECTid,chineseName,celebrityNameFROMcelebrityJOINareaONareaNumber=fkAreaNumberWHEREchineseName="佛山";示例9-9用內(nèi)連接實(shí)現(xiàn)示例9-3，并查詢id、planName、userName、duty和requirement信息。程序代碼如下：SELECTid,planName,username,duty,requirementFROMparticipantJOINuserONuser.userId=participant.userIdJOINplanONplan.planId=participant.planId;一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢3）外連接在圖9-3中，除了交集，表T1還有1條獨(dú)有記錄，即字段A值為Z對(duì)應(yīng)的記錄；同樣，表T2也有1條獨(dú)有記錄，即字段A值為T對(duì)應(yīng)的記錄。外連接是對(duì)內(nèi)連接的結(jié)果進(jìn)行擴(kuò)展，如果連接時(shí)T1表在左邊而T2表在右邊，那么內(nèi)連接擴(kuò)展表T1的獨(dú)有記錄稱為左外連接，內(nèi)連接擴(kuò)展表T2的獨(dú)有記錄稱為右外連接，內(nèi)連接擴(kuò)展到表T1和表T2的所有獨(dú)有記錄稱為全連接。MySQL暫不支持全連接。左外連接的語(yǔ)法格式如下：SELECT字段列表FROM表1LEFT[JOIN]表2ON表1.字段名=表2.字段名;右外連接的語(yǔ)法格式如下：SELECT字段列表FROM表1RIGHT[JOIN]表2ON表1.字段名=表2.字段名;一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢示例9-10通過(guò)左外連接查詢表T1和表T2的信息。程序代碼如下：SELECT*FROMT1LEFTJOINT2ONT1.A=T2.A;運(yùn)行結(jié)果如圖9-4所示。示例9-11通過(guò)右外連接查詢表T1和表T2的信息。程序代碼如下：SELECT*FROMT1RIGHTJOINT2ONT1.A=T2.A;運(yùn)行結(jié)果如圖9-5所示。一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢示例9-12查詢還沒(méi)輸入名人信息的地區(qū)，并顯示areaNumber和chineseName。一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢分析：先通過(guò)左外連接將地區(qū)表area和名人表celebrity連起來(lái)，得到地區(qū)表area中的所有數(shù)據(jù)，如果某地區(qū)編號(hào)在名人表celebrity中也存在，那么名人表celebrity也會(huì)顯示對(duì)應(yīng)地區(qū)編號(hào)的信息；如果某地區(qū)編號(hào)在名人表celebrity中不存在，那么系統(tǒng)顯示NULL，即該地區(qū)還沒(méi)有輸入名人信息。因此，可以將celebrity.fkAreaNumberISNULL作為查詢條件。程序代碼如下：SELECTareaNumber,chineseNameFROMareaLEFTJOINcelebrityONarea.areaNumber=celebrity.fkAreaNumberWHEREcelebrity.fkAreaNumberISNULL;一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢示例9-13顯示名人表celebrity中地區(qū)編碼錯(cuò)誤的記錄。一、使用連接查詢知識(shí)儲(chǔ)備2、多表連接查詢分析：先通過(guò)右外連接將地區(qū)表area和名人表celebrity連起來(lái)，得到名人表celebrity中的所有數(shù)據(jù)。如果某地區(qū)編號(hào)在地區(qū)表area中也存在，就會(huì)顯示地區(qū)表area中該地區(qū)編號(hào)對(duì)應(yīng)的信息；如果某地區(qū)編號(hào)在地區(qū)表area中不存在，那么系統(tǒng)顯示NULL，表示該地區(qū)在名人表celebrity中存在，但在地區(qū)表area中不存在，即該記錄為名人表celebrity中錯(cuò)誤的地區(qū)編碼。因此，可以將area.areaNumberISNULL作為查詢條件。程序代碼如下：SELECTcelebrity.*FROMareaRIGHTJOINcelebrityONarea.areaNumber=celebrity.fkAreaNumberWHEREarea.areaNumberISNULL;一、使用連接查詢知識(shí)儲(chǔ)備3、設(shè)置表的別名在查詢時(shí)，可以為表設(shè)置別名，特別是當(dāng)表名比較長(zhǎng)時(shí)，設(shè)置簡(jiǎn)短的別名能提高操作效率。在定義了表的別名之后，在需要指定表名的地方都可以使用表的別名。設(shè)置表的別名的語(yǔ)法格式如下：SELECT字段列表FROM表1[AS]表別名1LEFT|RIGHT|JOIN表2[AS]表別名2ON表1.字段名=表2.字段名;示例9-14通過(guò)別名實(shí)現(xiàn)示例9-9，并查詢id、planName、userName、duty和requirement信息。程序代碼如下：SELECTid,planName,userName,duty,requirementFROMparticipantaJOINuserbONa.userId=b.userIdJOINplancONa.planId=c.planId;一、使用連接查詢知識(shí)儲(chǔ)備4、聯(lián)合查詢聯(lián)合查詢是指將兩個(gè)或多個(gè)查詢結(jié)果合并在一起顯示。其語(yǔ)法格式如下：SELECT字段列表1FROM表1WHERE條件1UNIONSELECT字段列表2FROM表2WHERE條件2;示例9-15小王想去深圳（地區(qū)編碼為5840）調(diào)研，打算把深訓(xùn)的民俗、名人和城市榮譽(yù)列在一張清單上。程序代碼如下：SELECTfkAreaNumberAS地區(qū),folkNameAS調(diào)研內(nèi)容FROMfolkWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,celebrityNameFROMcelebrityWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,honoraryTitlefolkNameFROMhonorWHEREfkAreaNumber="5840";示例9-16優(yōu)化示例9-15，增加一列，用來(lái)顯示調(diào)研內(nèi)容的類型，如民俗、名人和城市榮譽(yù)等。程序代碼如下：SELECTfkAreaNumberAS地區(qū),folkNameAS調(diào)研內(nèi)容,"民俗"AS類型FROMfolkWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,celebrityName,"名人"FROMcelebrityWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,honoraryTitle,"城市榮譽(yù)"FROMhonorWHEREfkAreaNumber="5840";一、使用連接查詢?nèi)蝿?wù)實(shí)施任務(wù)9-1查詢廣州有哪些民俗，并顯示id、chineseName和folkName信息。任務(wù)9-2查詢中山有哪些名人，并顯示id、chineseName和celebrityName信息。任務(wù)9-3查詢東莞有哪些城市名譽(yù)，并顯示id、chineseName和honoraryTitle信息。任務(wù)9-4查詢工作計(jì)劃參與人員表participant，并顯示id、planName、userName、duty、requirement和remarks信息。任務(wù)9-5查詢工作計(jì)劃表plan，并顯示planId、planName、planMaker、releaseTime、auditTime、startTime、endTime和planContent信息，其中planMaker用來(lái)顯示姓名而不是工號(hào)。任務(wù)9-6查詢還沒(méi)有分配工作的工作人員。02使用子查詢二、使用子查詢?nèi)蝿?wù)分析除了連接查詢，小王發(fā)現(xiàn)還可以使用子查詢來(lái)實(shí)現(xiàn)連接查詢。盡管子查詢沒(méi)有連接操作，每次只針對(duì)一個(gè)表操作，但子查詢其實(shí)是查詢的嵌套，即把一個(gè)查詢的結(jié)果嵌套到另一個(gè)查詢中，作為查詢條件。嵌套的兩個(gè)或多個(gè)查詢可以針對(duì)不同的表進(jìn)行，從而實(shí)現(xiàn)多表查詢。小王對(duì)粵文創(chuàng)進(jìn)行分析后得到的任務(wù)清單如下。任務(wù)編號(hào)任務(wù)內(nèi)容任務(wù)9-7查詢梅州有哪些城市榮譽(yù)任務(wù)9-8查詢茂名有哪些名人任務(wù)9-9查詢肇慶以外地區(qū)的廣東民俗任務(wù)9-10查詢還沒(méi)有分配工作的工作人員二、使用子查詢知識(shí)儲(chǔ)備1、帶比較運(yùn)算符的子查詢根據(jù)子查詢的嵌入位置一般可以分為WHERE型子查詢、FROM型子查詢和復(fù)制表子查詢等，下面主要介紹WHERE型子查詢。帶比較運(yùn)算符的子查詢的語(yǔ)法格式如下：SELECT字段列表FROM表名WHERE字段名關(guān)系運(yùn)算符(子查詢);需要說(shuō)明以下幾點(diǎn)。帶比較運(yùn)算符的子查詢只能返回單個(gè)值，不能返回值列表，即不能返回多個(gè)值。子查詢中的字段列表一般只有一個(gè)字段，并且這個(gè)字段必須和WHERE后面的字段一致。二、使用子查詢知識(shí)儲(chǔ)備示例9-17查詢清遠(yuǎn)有哪些名人，并顯示id和celebrityName信息。二、使用子查詢知識(shí)儲(chǔ)備分析：id和celebrityName在名人表celebrity中，名人表celebrity中只有fkAreaNumber字段，沒(méi)有chineseName字段，可以先在地區(qū)表area中查詢清遠(yuǎn)的地區(qū)代碼，再在名人表celebrity中查詢?cè)摰貐^(qū)代碼對(duì)應(yīng)的名人。程序代碼如下：SELECTid,celebrityNameFROMcelebrityWHEREfkAreaNumber=(SELECTareaNumberFROMareaWHEREchineseName="清遠(yuǎn)");二、使用子查詢知識(shí)儲(chǔ)備示例9-18查詢潮州以外地區(qū)的廣東民俗，并顯示id、fkAreaNumber和folkName信息。程序代碼如下：SELECTid,fkAreaNumber,folkNameFROMfolkWHEREfkAreaNumber!=(SELECTareaNumberFROMareaWHEREchineseName="潮州");二、使用子查詢知識(shí)儲(chǔ)備2、帶IN關(guān)鍵字的子查詢帶IN關(guān)鍵字的子查詢是比較常用的子查詢。其語(yǔ)法格式如下：SELECT字段列表FROM表名WHERE字段名[NOT]IN(子查詢);示例9-19利用帶IN關(guān)鍵字的子查詢實(shí)現(xiàn)示例9-12，查詢還沒(méi)輸入名人信息的地區(qū)，并顯示areaNumber和chineseName信息。分析：先查詢名人表celebrity中的所有fkAreaNumber，并將其放在IN列表以作為條件，再查詢地區(qū)表area，凡是不在IN列表中的地區(qū)，就是目前還沒(méi)輸入名人信息的地區(qū)。程序代碼如下：SELECTareaNumber,chineseNameFROMareaWHEREareaNumberNOTIN(SELECTfkAreaNumberFROMcelebrity);示例9-20查詢汕頭的城市名譽(yù)，并顯示id和honoraryTitle信息。程序代碼如下：SELECTid,honoraryTitleFROMhonorWHEREfkAreaNumberIN(SELECTareaNumberFROMareaWHEREchineseName="汕頭");二、使用子查詢知識(shí)儲(chǔ)備3、帶EXISTS關(guān)鍵字的子查詢帶EXISTS關(guān)鍵字的子查詢的語(yǔ)法格式如下：SELECT字段列表FROM表名WHERE[NOT]EXISTS(子查詢);說(shuō)明：EXISTS只返回真或假，其本身是一個(gè)邏輯值，可以作為判斷條件，所以在WHERE條件判斷中不需要指定字段名。示例9-21當(dāng)工作計(jì)劃參與人員表participant中的userId有錯(cuò)誤時(shí)，顯示所有工作人員信息。程序代碼如下：SELECT*FROMuserWHEREEXISTS(SELECT*FROMparticipantWHEREuserIdNOTIN(SELECTuserIdFROMuser));二、使用子查詢知識(shí)儲(chǔ)備4、帶ANY、ALL和SOME關(guān)鍵字的子查詢帶ANY、ALL和SOME關(guān)鍵字的子查詢