MySQL數(shù)據(jù)庫原理與應(yīng)用項目化教程（微課版） 課件 (含思政) 項目8-高級數(shù)據(jù)查詢

項目八高級數(shù)據(jù)查詢

涉及多表數(shù)據(jù)的查詢或復(fù)雜的單表查詢問題要用高級查詢來完成。高級查詢包括連接查詢、子查詢和集合查詢等操作，連接查詢又分為交叉連接、內(nèi)連接、外連接和自連接，子查詢可以嵌套在查詢語句中使用，也可以在更新語句中使用。本項目將對“學生成績管理”數(shù)據(jù)庫的數(shù)據(jù)表作高級查詢操作，并在更新語句中應(yīng)用子查詢以實現(xiàn)更強大的數(shù)據(jù)更新能力。知識目標：識記連接查詢、子查詢、集合查詢相關(guān)語句的語法。能力目標：能用連接查詢或子查詢解決多表查詢或復(fù)雜的單表查詢問題。能用集合查詢解決一些查詢問題。任務(wù)8.1任務(wù)8.3交叉連接與內(nèi)連接子查詢?nèi)蝿?wù)8.4子查詢在更新語句中的應(yīng)用任務(wù)8.2外連接與自連接任務(wù)8.5集合查詢

任務(wù)8.1交叉連接與內(nèi)連接使用交叉連接或內(nèi)連接完成對“學生成績管理”數(shù)據(jù)庫（stuDB）涉及多表數(shù)據(jù)的查詢操作。具體任務(wù)如下：(1)把stuinfo表和stumarks表進行交叉連接。(2)查詢所有學生的學號、姓名、課程號及成績。(3)查詢所有學生的學號、姓名、課程名及成績。(4)查詢選修“李斯文”老師講授課程的學生的學號及姓名?！救蝿?wù)描述】交叉連接與內(nèi)連接8.1【相關(guān)知識】21

內(nèi)連接

交叉連接8.1交叉連接與內(nèi)連接1.交叉連接交叉連接又叫做笛卡爾連接。表1（M行）與表2（N行）做交叉連接，就是把表1的每一行分別與表2的每一行連接，結(jié)果集是兩表所有記錄的任意組合，一共M×N行。交叉連接語法格式有兩種，分別如下：(1)語法格式1SELECT…FROM表l,表2;(2)語法格式2SELECT…FROM表1CROSSJOIN表2;8.1【相關(guān)知識】交叉連接與內(nèi)連接2.內(nèi)連接內(nèi)連接是把兩表中滿足條件的記錄組合在一起，相當于是交叉連接的子集。(1)語法格式1：SELECT…FROM表l,表2WHERE表1.列名=表2.列名(2)語法格式2：SELECT…FROM表l[INNER]JOIN表2ON表1.列名=表2.列名8.1【相關(guān)知識】交叉連接與內(nèi)連接說明：N個表要連接成一個表，需要兩兩連接N-1次完成。第1種格式是在WHERE子句中給出連接條件，N個表連接有N-1個連接條件，要用AND運算符連接起來；第2種格式是在FROM子句后面指定連接條件，JOIN一個表，ON后面寫一個連接條件。如果所引用的字段被查詢的多個表所共有，則引用該字段時必須指定其屬于哪個表，引用的語法格式：表名.字段名。為了簡化連接條件的書寫，可以給表名起別名，起了別名的表，在該查詢語句中要統(tǒng)一使用別名代替表名。二個表如果沒有共同字段，需要找一個和它們都有共同字段的第三個表間接地完成二個表的連接操作。8.1【相關(guān)知識】交叉連接與內(nèi)連接【任務(wù)實施】1.把stuinfo表和stumarks表進行交叉連接。根據(jù)交叉連接二種語法格式，代碼如下：SELECT*FROMstuinfo,stumarks;或者SELECT*FROMstuinfoCROSSJOINstumarks;8.1交叉連接與內(nèi)連接【任務(wù)實施】8.1圖8.1stuinfo、stumarks二表交叉連接結(jié)果（最前面8條記錄）圖8.2stuinfo、stumarks二表交叉連接結(jié)果（最后面8條記錄）交叉連接與內(nèi)連接【任務(wù)實施】2.查詢所有學生的學號、姓名、課程號及成績。SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfo,stumarksWHEREstuinfo.stuno=stumarks.stuno;或者SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfoJOINstumarksONstuinfo.stuno=stumarks.stuno;8.1交叉連接與內(nèi)連接【任務(wù)實施】8.1圖8.3查詢所有學生的學號、姓名、課程號及成績交叉連接與內(nèi)連接【任務(wù)實施】3.查詢所有學生的學號、姓名、課程名及成績。8.1交叉連接與內(nèi)連接（1）用語法格式1

SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfo,stumarks,stucourseWHEREstuinfo.stuno=stumarks.stunoANDo=o;（2）用語法格式2SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfoJOINstumarksONstuinfo.stuno=stumarks.stunoJOINstucourseONo=o;【任務(wù)實施】簡化代碼如下：（1）用語法格式1SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoi,stumarksm,stucoursecWHEREi.stuno=m.stunoANDo=o;（1）用語法格式2SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=o;8.1交叉連接與內(nèi)連接【任務(wù)實施】8.1圖8.4查詢所有學生的學號、姓名、課程名及成績交叉連接與內(nèi)連接【任務(wù)實施】4.查詢選修“李斯文”老師課程的學生的學號及姓名SELECTi.stuno,stunameFROMstuinfoi,stumarksm,stucoursecWHERE(i.stuno=m.stunoANDo=o)AND(cteacher='李斯文');或者SELECTi.stuno,stunameFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=oWHEREcteacher='李斯文';8.1交叉連接與內(nèi)連接【任務(wù)實施】8.1圖8.5查詢選修李斯文老師課程的學生的學號及姓名交叉連接與內(nèi)連接思政小貼士【課堂實踐分組管理，組長負責協(xié)調(diào)組內(nèi)學習能力較強者指導(dǎo)組內(nèi)較差的同學完成課堂作業(yè)】培養(yǎng)認真負責的工作態(tài)度、一絲不茍的工匠精神、團隊合作意識。8.1交叉連接與內(nèi)連接任務(wù)8.2外連接與自連接使用外連接或自連接完成對“學生成績管理”數(shù)據(jù)庫（stuDB）涉及多表數(shù)據(jù)的查詢操作或復(fù)雜的單表查詢操作。外連接應(yīng)用場景：要篩選出在另一個表中沒有相關(guān)數(shù)據(jù)的記錄。具體任務(wù)如下：（1）查詢沒有選修課程的學生的基本信息。（2）查找同一課程成績相同的選課記錄。

【任務(wù)描述】外連接與自連接8.2【相關(guān)知識】21

自連接

外連接外連接與自連接8.21.外連接外連接分為左外連接、右外連接和全外連接，MySQL目前支持左外連接和右外連接操作。這里先給出左表和右表的概念，兩表作連接，JOIN左邊的表叫左表，JOIN右邊的表叫右表。(1)左外連接左外連接的結(jié)果集是兩表內(nèi)連接的結(jié)果集加上左表中沒有參加內(nèi)連接的記錄，左表這些“剩下來”的記錄在結(jié)果集中右表的那些字段值全為空值（NULL）。語法格式如下：SELECT…FROM表1LEFT[OUTER]JOIN

表2ON表1.列名=表2.列名外連接與自連接8.2【相關(guān)知識】(2)右外連接右外連接的結(jié)果集是兩表內(nèi)連接的結(jié)果集加上右表中沒有參加內(nèi)連接的記錄，右表這些“剩下來”的記錄在結(jié)果集中左表的那些字段值全為空值（NULL）。語法格式如下：SELECT…FROM表1RIGHT[OUTER]JOIN表2ON表1.列名=表2.列名外連接與自連接8.2【相關(guān)知識】2.自連接自連接是一種特殊的內(nèi)連接，特殊在連接的兩個表是完全相同的，可以看作是一張表的兩個副本的連接，為了區(qū)分兩個副本，需要給它們分別起別名。（1）語法格式1SELECT…FROM表名別名1,表名別名2WHERE別名1.列名=別名2.列名（2）語法格式2：SELECT…FROM表名別名1JOIN表名別名2ON別名1.列名=別名2.列名外連接與自連接8.2【相關(guān)知識】【任務(wù)實施】查詢沒有選修課程的學生的基本信息

（1）先查看stuinfo與stumarks表做左外連接的結(jié)果集。代碼如下：SELECT*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stuno;外連接與自連接8.2【任務(wù)實施】執(zhí)行結(jié)果：外連接與自連接8.2圖8.6stuinfo表與stumarks表做左外連接的結(jié)果【任務(wù)實施】（2）結(jié)果集中沒有選課的學生所在的行，對應(yīng)stumarks表中的那些字段值全為NULL。這個特點正好可以用來判斷哪些學生沒有選課，根據(jù)實體完整性規(guī)則，stumarks表中參與內(nèi)連接的那些行，主屬性（構(gòu)成主鍵的字段）不可能為NULL，這里有兩個主屬性stuno與cno，通過判斷它們其中任何一個是否為空值就可以篩選出那些沒有選課的學生的基本信息。 SELECTstuinfo.*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stunoWHEREstumarks.stunoISNULL;外連接與自連接8.2【任務(wù)實施】外連接與自連接8.2圖8.7查詢沒有選修課程的學生的基本信息【任務(wù)實施】2．查找同一課程成績相同的選課記錄。SELECTa.stuno,b.stuno,o,a.stuscoreFROMstumarksa,stumarksbWHEREa.stuscore=b.stuscoreANDa.stuno<>b.stunoANDo=o;外連接與自連接8.2圖8.8查詢同一課程成績相同的選課記錄思政小貼士【課堂實踐分組管理，組長負責協(xié)調(diào)組內(nèi)學習能力較強者指導(dǎo)組內(nèi)較差的同學完成課堂作業(yè)】培養(yǎng)認真負責的工作態(tài)度、一絲不茍的工匠精神、團隊合作意識。8.1交叉連接與內(nèi)連接任務(wù)8.3子查詢

使用子查詢完成對“學生成績管理”數(shù)據(jù)庫（stuDB）涉及多表數(shù)據(jù)的查詢或者復(fù)雜的單表查詢操作，這種多表查詢有個特點，查詢的數(shù)據(jù)項在同一個表中，而篩選記錄需要通過其他表的數(shù)據(jù)進行。具體任務(wù)如下：（1）查詢選修了課程的學生的基本信息。（2）查詢沒有選修課程的學生的基本信息。（3）查詢選修了“高等數(shù)學”這門課的學生的基本信息。（4）查詢成績最高的選課記錄。【任務(wù)描述】8.3子查詢【相關(guān)知識】31[NOT]EXISTS子查詢[NOT]IN子查詢8.3子查詢2

比較子查詢子查詢是指一個查詢塊嵌套在SELECT、INSERT、UPDATE、DELETE等語句中的WHERE或其他子句中進行查詢。SQL語言允許多層嵌套查詢，即一個子查詢中還可以嵌套其他子查詢。根據(jù)子查詢執(zhí)行是否依賴于外部查詢，子查詢可分為相關(guān)子查詢與不相關(guān)子查詢兩大類。不相關(guān)子查詢是指不依賴于外部查詢的子查詢，反之，則稱為相關(guān)子查詢；不相關(guān)子查詢先于外部查詢執(zhí)行，子查詢得到的結(jié)果集不會顯示，而是傳給外部查詢使用，不相關(guān)子查詢總共執(zhí)行一次；相關(guān)子查詢的執(zhí)行依賴于外部查詢，即需要外部查詢給它傳遞值，與外部查詢正在判斷的記錄有關(guān)，外部查詢執(zhí)行一行，相關(guān)子查詢就執(zhí)行一次。8.3子查詢【相關(guān)知識】子查詢返回的值要被外部查詢的[NOT]IN、[NOT]EXISTS、比較運算符、ANY（SOME）、ALL等操作符使用，根據(jù)操作符的不同，子查詢可以分為以下幾種：1．[NOT]IN子查詢在嵌套查詢中，子查詢的結(jié)果往往是一個集合，用謂詞IN判斷某列值是否在集合中，這是最常用的一種子查詢，IN前面加NOT表示判斷某列值是否不在集合中。IN子查詢一般是不相關(guān)子查詢。8.3子查詢【相關(guān)知識】2．比較子查詢帶有比較運算符的子查詢是指外部查詢與子查詢之間用比較運算符進行連接。當用戶確切知道內(nèi)層查詢返回單個值時，可以用>、<、=、>=、<=、!=或<>等比較運算符。比較子查詢可能是不相關(guān)子查詢，也可能是相關(guān)子查詢，要看具體情況。3．[NOT]EXISTS子查詢使用EXISTS謂詞來判斷子查詢是否返回任何記錄，當子查詢的結(jié)果不為空集（即存在匹配行）時，返回邏輯真值。EXISTS前面可以加NOT用來判斷是否不存在匹配行。EXISTS子查詢是相關(guān)子查詢。8.3子查詢【相關(guān)知識】【任務(wù)實施】1.查詢選修了課程的學生的基本信息。（1）用IN子查詢第一步：查找出所有選修了課程的學生的學號SELECTDISTINCTstunoFROMstumarks第二步：根據(jù)前一步得到的學號集合查這些學生的基本信息SELECT*FROMstuinfoWHEREstunoIN(SELECTDISTINCTstunoFROMstumarks);8.3子查詢【任務(wù)實施】（2）用EXISTS子查詢SELECT*FROMstuinfoWHEREEXISTS(SELECT*

FROMstumarks

WHEREstuno=stuinfo.stuno);這里子查詢的查詢條件依賴于外部查詢傳遞進來的值：stuinfo.stuno（該生學號）。8.3子查詢【任務(wù)實施】8.3子查詢圖8.9選修了課程的學生的基本信息【任務(wù)實施】2.查詢沒有選修課程的學生的基本信息。（1）用IN子查詢SELECT*FROMstuinfoWHEREstunoNOTIN(SELECTDISTINCTstuno

FROMstumarks);（2）用EXISTS子查詢SELECT*FROMstuinfoWHERENOTEXISTS(SELECT*

FROMstumarks

WHEREstuno=stuinfo.stuno);8.3子查詢【任務(wù)實施】8.3子查詢圖8.10查詢沒有選修課程的學生的基本信息【任務(wù)實施】3.查詢選修了“高等數(shù)學”這門課的學生的基本信息。第一步：查找‘高等數(shù)學’這門課的課程號SELECTcnoFROMstucourseWHEREcname=‘高等數(shù)學’;第二步：根據(jù)‘高等數(shù)學’的課程號查選修該門課的學生學號SELECTstunoFROMstumarksWHEREcno=(SELECTcno

FROMstucourse

WHEREcname=‘高等數(shù)學’);8.3子查詢【任務(wù)實施】第三步：根據(jù)學號找學生基本信息SELECT*FROMstuinfoWHEREstunoIN(SELECTstuno

FROMstumarks

WHEREcno=(SELECTcno

FROMstucourse

WHEREcname=‘高等數(shù)學’));8.3子查詢【任務(wù)實施】8.3子查詢圖8.11查詢選修了“高等數(shù)學”這門課的學生的基本信息【任務(wù)實施】4.查詢成績最高的選課記錄第一步：查找學生選課表中的最高成績

SELECTMAX(stuscore)FROMstumarks;第二步：查找成績等于最高成績的選課記錄SELECT*FROMstumarksWHEREstuscore=(SELECTmax(stuscore)

FROMstumarks);8.3子查詢【任務(wù)實施】8.3子查詢圖8.12查詢成績最高的選課記錄思政小貼士【課堂實踐分組管理，組長負責協(xié)調(diào)組內(nèi)學習能力較強者指導(dǎo)組內(nèi)較差的同學完成課堂作業(yè)】培養(yǎng)認真負責的工作態(tài)度、一絲不茍的工匠精神、團隊合作意識。8.3子查詢?nèi)蝿?wù)8.4子查詢在更新語句中的應(yīng)用子查詢可以嵌套在INSERT、UPDATE、DELETE語句中使用。

對“學生成績管理”數(shù)據(jù)庫的數(shù)據(jù)表進行數(shù)據(jù)更新時應(yīng)用子查詢，以實現(xiàn)比項目六中更強大的數(shù)據(jù)更新能力?！救蝿?wù)描述】8.4子查詢在更新語句中的應(yīng)用【相關(guān)知識】31UPDATE和DELETE語句的條件子句帶子查詢

從一個表向另一個表復(fù)制多行多列數(shù)據(jù)8.4子查詢在更新語句中的應(yīng)用2

嵌套修改1．從一個表向另一個表復(fù)制多行多列數(shù)據(jù)利用子查詢，可以把查詢結(jié)果（一行或多行數(shù)據(jù)）插入到表中，實現(xiàn)從一個表向另一個表導(dǎo)入數(shù)據(jù)的功能。語法格式如下：INSERTINTO表名[(字段列表)]SELECT語句;說明：字段列表中字段的個數(shù)、數(shù)據(jù)類型必須和SELECT語句中查詢的數(shù)據(jù)項個數(shù)及數(shù)據(jù)類型一一對應(yīng)。8.4子查詢在更新語句中的應(yīng)用【相關(guān)知識】2．嵌套修改利用子查詢返回的單個值，可以實現(xiàn)用查詢結(jié)果修改表中某個字段值的目的。語法格式如下：UPDATE表名SET字段名＝(返回單個值的子查詢)[WHERE條件]8.4子查詢在更新語句中的應(yīng)用【相關(guān)知識】3．UPDATE和DELETE語句的條件子句帶子查詢

有時候，用UPDATE、DELETE語句修改、刪除數(shù)據(jù)時的篩選條件比較復(fù)雜，甚至需要通過另一個表的數(shù)據(jù)來判斷，如果在UPDATE、DELETE語句的條件子句中使用子查詢，基本可以滿足這種篩選需求。8.4子查詢在更新語句中的應(yīng)用【相關(guān)知識】【任務(wù)實施】1.創(chuàng)建一個空表stuinfo_2（stuno,stuname,avg_stuscore)，要求用INSERT語句把stuinfo表中stuno,stuname兩個字段的數(shù)據(jù)導(dǎo)入到stuinfo_2表中相應(yīng)字段。(1)創(chuàng)建空表stuinfo_2CREATETABLEstuinfo_2(stunoCHAR(4)PRIMARYKEY,stunameCHAR(5),avg_stuscoreDECIMAL(4,1));子查詢在更新語句中的應(yīng)用8.4【任務(wù)實施】(2)stuinfo_2表中導(dǎo)入stuinfo表中stuno,stuname兩個字段的數(shù)據(jù)INSERTINTOstuinfo_2(stuno,stuname)SELECTstuno,stunameFROMstuinfo;子查詢在更新語句中的應(yīng)用8.4圖8.13從一個表向另一個表復(fù)制多行多列數(shù)據(jù)【任務(wù)實施】2．修改stuinfo_2表中“S001”同學的平均成績(avg_stuscore)（注：平均分統(tǒng)計根據(jù)stumarks表中該生的選課成績）。UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)

FROMstumarksWHEREstuno='S001')WHEREstuno='S001';子查詢在更新語句中的應(yīng)用8.4圖8.14嵌套修改（使用不相關(guān)子查詢）【任務(wù)實施】思考題：如果要一次修改所有同學的平均分，上面代碼應(yīng)該怎么改進？

子查詢在更新語句中的應(yīng)用8.4UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)

FROMstumarks

WHEREstuno=stuinfo_2.stuno);【任務(wù)實施】3．把“高等數(shù)學”這門課的所有選修成績都加5分。UPDATEstumarksSETstuscore=stuscore+5WHEREcno=(SELECTcno

FROMstucourseWHEREcname='高等數(shù)學');子查詢在更新語句中的應(yīng)用8.4圖8.16U