項目八高級數據查詢E-mail:358542298@江蘇海事職業(yè)技術學院

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

任務8.1交叉連接與內連接使用交叉連接或內連接完成對“學生成績管理”數據庫（stuDB）涉及多表數據的查詢操作。（具體任務詳見任務實施部分）【任務描述】交叉連接與內連接8.1【相關知識】21

內連接

交叉連接8.1交叉連接與內連接1.交叉連接又叫笛卡爾連接。表1（M行）與表2（N行）做交叉連接的結果集是兩表所有記錄的任意組合，一共M×N行。語法格式有兩種：(1)語法格式1SELECT…FROM表l,

表2;(2)語法格式2SELECT…FROM表1CROSSJOIN表2;8.1【相關知識】交叉連接與內連接2.內連接：把兩表中滿足條件的記錄組合在一起，相當于是交叉連接的子集。

(1)等值連接：兩表連接條件是對應字段做相等比較。（最常見的內連接）①語法格式1：SELECT…FROM表l,表2

WHERE

表1.列名=表2.列名

...........................連接條件②語法格式2：SELECT…FROM表l[INNER]JOIN表2ON

表1.列名=表2.列名8.1【相關知識】交叉連接與內連接說明：N個表連接成一個表，兩兩連接N-1次完成。若在WHERE子句中給出連接條件，N個表連接有N-1個連接條件，用AND運算符連接起來；若在FROM子句后面指定連接條件，JOIN一個表，ON后面寫一個連接條件。引用的字段被查詢的多個表所共有，則引用時需指定其屬于哪個表，格式：表名.字段名為了簡化連接條件的書寫，可以給表起別名，起別名的表，在該查詢語句中要統一使用別名代替表名。兩個表若沒有關聯，需要找一個和它們都有關聯的第三個表間接地完成兩個表的連接操作。8.1【相關知識】交叉連接與內連接(2)自然連接：特殊的等值連接，基于兩個表中所有同名的字段做相等比較。

語法格式：SELECT…FROM表lNATURALJOIN表28.1【相關知識】交叉連接與內連接優(yōu)點：減少冗余，省略連接條件【任務實施】準備工作：數據庫studb三個數據表的數據如任務1.2中表1-3、表1-4、表1-5所示。8.1交叉連接與內連接【任務實施】1.把stuinfo表和stumarks表進行交叉連接。SELECT*FROMstuinfo,stumarks;或者SELECT*FROMstuinfoCROSSJOINstumarks;8.1交叉連接與內連接【任務實施】8.1交叉連接與內連接聲明：學生信息純屬虛構學生表：8條記錄選課成績表：16條記錄交叉連接結果集：8

16=128條記錄stuinfo、stumarks二表交連接結果【任務實施】2.查詢所有學生的學號、姓名、課程號及成績。SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfo,stumarksWHEREstuinfo.stuno=stumarks.stuno;或者SELECTstuinfo.stuno,stuname,cno,stuscoreFROMstuinfoJOINstumarksON

stuinfo.stuno=stumarks.stuno;8.1交叉連接與內連接【任務實施】8.1交叉連接與內連接【任務實施】8.1交叉連接與內連接由于連接條件是兩表同名字段做相等比較，可以使用自然連接簡化代碼：SELECTstuno,stuname,cno,stuscoreFROMstuinfoNATURALJOINstumarks;【任務實施】3.查詢所有學生的學號、姓名、課程名及成績。8.1交叉連接與內連接（1）用語法格式1

SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfo,stumarks,stucourseWHEREstuinfo.stuno=stumarks.stunoANDo=o;（2）用語法格式2SELECTstuinfo.stuno,stuname,cname,stuscoreFROMstuinfoJOINstumarksONstuinfo.stuno=stumarks.stuno

JOIN

stucourseONo=o;【任務實施】簡化代碼如下：（1）用語法格式1SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoi,stumarksm,stucoursecWHEREi.stuno=m.stunoANDm.cno=c.cno;（1）用語法格式2SELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursec

ONm.cno=c.cno;8.1交叉連接與內連接【任務實施】8.1交叉連接與內連接聲明：學生信息純屬虛構【任務實施】8.1交叉連接與內連接由于連接條件是同名字段做相等比較，還可以使用自然連接簡化代碼：SELECTstuno,stuname,cname,stuscoreFROMstuinfoNATURALJOINstumarksNATURALJOINstucourse;【任務實施】4.查詢選修“李斯文”老師課程的學生的學號及姓名SELECTi.stuno,stunameFROMstuinfoi,stumarksm,stucoursecWHERE(i.stuno=m.stunoANDo=o)AND(cteacher='李斯文');或者SELECTi.stuno,stunameFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=oWHEREcteacher='李斯文';8.1交叉連接與內連接【任務實施】8.1交叉連接與內連接【任務實施】8.1交叉連接與內連接還可以使用自然連接簡化代碼：SELECTstuno,stunameFROMstuinfoNATURALJOINstumarksNATURALJOINstucourseWHEREcteacher='李斯文';重要知識點：1.交叉連接：結果集是兩表所有記錄的任意組合，一共M×N行

。（不常用）2.內連接**：把兩表中滿足條件的記錄組合在一起，等值連接最常見。（常用）（1）兩種語法格式（2）連接后相同字段名的引用格式：表名.字段名（3）可以給表起別名簡化代碼（4）自然連接：特殊的等值連接，可以減少冗余，省略連接條件。（4）兩個沒有關聯的表的連接要通過第三個表（與兩表都有關聯）間接實現。8.1交叉連接與內連接任務8.2外連接與自連接使用外連接或自連接完成對“學生成績管理”數據庫（stuDB）涉及多表數據的查詢操作或復雜的單表查詢操作。具體任務如下：（1）查詢沒有選修課程的學生的基本信息。（2）查找同一課程成績相同的選課記錄。

【任務描述】外連接與自連接8.2【相關知識】21

自連接外連接外連接與自連接8.21.外連接外連接分為左外連接、右外連接和全外連接。MySQL目前支持左外連接和右外連接操作。兩表作連接，JOIN左邊的表叫左表，JOIN右邊的表叫右表。外連接與自連接8.2【相關知識】(1)左外連接結果集：內連接的結果集+左表中沒有參加內連接的記錄（左表這些“剩下來”的記錄在結果集中右表的那些字段值全為NULL）。語法格式如下：SELECT…FROM表1

LEFT[OUTER]JOIN

表2ON

表1.列名=表2.列名外連接與自連接8.2【相關知識】(2)右外連接內連接的結果集+右表中沒有參加內連接的記錄（右表這些“剩下來”的記錄在結果集中左表的那些字段值全為NULL）語法格式如下：SELECT…FROM表1RIGHT[OUTER]JOIN表2ON

表1.列名=表2.列名外連接與自連接8.2【相關知識】2.自連接

特殊的內連接，連接的兩個表完全相同的，為了區(qū)分需要給它們分別起別名。（1）語法格式1SELECT…FROM表名別名1,表名別名2WHERE別名1.列名=別名2.列名（2）語法格式2：SELECT…FROM表名別名1JOIN表名別名2ON別名1.列名=別名2.列名外連接與自連接8.2【相關知識】【任務實施】準備工作：數據庫studb三個數據表的數據如任務1.2中表1-3、表1-4、表1-5所示。8.2外連接與自連接【任務實施】查詢沒有選修課程的學生的基本信息分析：先查看stuinfo與stumarks表做左外連接的結果集。SELECT*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stuno;外連接與自連接8.2【任務實施】執(zhí)行結果：外連接與自連接8.2沒有選課學生所在行，stumarks表的那些字段值全為NULL選課學生所在行，stumarks表的主屬性(stuno與cno)值不為NULL【任務實施】（2）判斷學生沒有選課：左外連接結果集中stumarks表的stuno(或cno)為NULL。 SELECTstuinfo.*FROMstuinfoLEFTJOINstumarksONstuinfo.stuno=stumarks.stunoWHEREstumarks.stunoISNULL;外連接與自連接8.2【任務實施】外連接與自連接8.2聲明：學生信息純屬虛構

沒有選修課程的學生信息：【任務實施】2．查找同一課程成績相同的選課記錄。SELECTa.stuno,b.stuno,a.cno,a.stuscoreFROMstumarksa,stumarksbWHEREa.stuscore=b.stuscoreANDa.stuno<>b.stunoANDa.cno=b.cno;外連接與自連接8.2重要知識點：1.外連接（1）左外~（右外~）:內連接結果集+左（右）表沒有參與連接的記錄（2）應用場景：要篩選出在另一個表中沒有相關數據的記錄。2.自連接（1）特殊的內連接，連接的二個表相同，給它們起不同別名區(qū)分（2）應用場景：一些復雜的單表查詢問題8.2外連接與自連接任務8.3子查詢

使用子查詢完成對“學生成績管理”數據庫（stuDB）涉及多表數據的查詢或者復雜的單表查詢操作，這些多表查詢有個特點：查詢的數據項在同一個表中，而篩選記錄需要通過其他表的數據進行。（具體任務詳見任務實施部分）【任務描述】8.3子查詢【相關知識】51

[NOT]EXISTS子查詢

[NOT]IN子查詢8.3子查詢2

比較子查詢34

ALL子查詢

ANY|SOME子查詢子查詢：是指一個查詢塊嵌套在SELECT、INSERT、UPDATE、DELETE等語句中的WHERE或其他子句中進行查詢。SQL語言允許多層嵌套查詢，即一個子查詢中還可以嵌套其他子查詢。常見的使用形式：嵌套在SELECT語句的WHERE子句中的子查詢。子查詢分為相關子查詢與不相關子查詢兩大類。不相關子查詢是指不依賴于外部查詢的子查詢，反之，則稱為相關子查詢8.3子查詢【相關知識】子查詢返回的值要被外部查詢的[NOT]IN、[NOT]EXISTS、比較運算符、ALL等操作符使用，根據操作符的不同，子查詢主要分為以下幾種：1．[NOT]IN子查詢子查詢的結果是一個集合，用謂詞IN判斷某列值是否在集合中，這是最常用的一種子查詢，IN前面加NOT表示判斷某列值是否不在集合中。IN子查詢一般是不相關子查詢。8.3子查詢【相關知識】2．比較子查詢子查詢返回單個值，與外部查詢用比較運算符(>、<、=、>=、<=、!=或<>)進行連接。比較子查詢可能是不相關子查詢，也可能是相關子查詢。3．[NOT]EXISTS子查詢使用EXISTS

謂詞來判斷子查詢是否返回任何記錄，當子查詢的結果不為空集（即存在匹配行）時，返回邏輯真值。EXISTS前面可以加NOT用來判斷是否不存在匹配行。EXISTS子查詢是相關子查詢。8.3子查詢【相關知識】4．ALL子查詢ALL操作符可以與比較運算符一起使用，表示將一個值與子查詢返回的一組值進行比較，只有所有值都滿足某個條件才返回邏輯真值。5．ANY|SOME子查詢與ALL子查詢類似，表示將一個值與子查詢返回的一組值進行比較，ANY操作符指定任何一個值滿足條件即返回邏輯真值，SOME操作符指定一些值滿足條件即返回邏輯真值。8.3子查詢【相關知識】【任務實施】準備工作：數據庫studb三個數據表的數據如任務1.2中表1-3、表1-4、表1-5所示。8.3子查詢【任務實施】查詢選修了課程的學生的基本信息。

方法一：用IN子查詢8.3子查詢分析：第二步：根據第一步得到的學號集合查這些學生的基本信息解決問題思路：把一個復雜的多表查詢問題轉換為二個簡單的單表查詢問題。第一步：查找所有選修了課程的學生的學號【任務實施】代碼如下：第一步：查找出所有選修了課程的學生的學號第二步：根據前一步得到的學號集合查這些學生的基本信息SELECT*FROMstuinfoWHEREstunoIN(？）;

8.3子查詢【任務實施】方法二：用EXISTS子查詢SELECT*FROMstuinfoWHEREEXISTS(SELECT*FROMstumarksWHEREstuno=stuinfo.stuno);子查詢依賴于外部查詢傳遞進來的值：stuinfo.stuno（該生學號）。8.3子查詢【任務實施】8.3子查詢聲明：學生信息純屬虛構【任務實施】2.查詢沒有選修課程的學生的基本信息。（1）用IN子查詢SELECT*FROMstuinfoWHEREstunoNOTIN(SELECTDISTINCTstunoFROMstumarks);（2）用EXISTS子查詢SELECT*FROMstuinfoWHERENOTEXISTS(SELECT*FROMstumarksWHEREstuno=stuinfo.stuno);8.3子查詢【任務實施】8.3子查詢聲明：學生信息純屬虛構【任務實施】3.查詢選修了“高等數學”這門課的學生姓名。

分析：通過三個表的數據分析手工查詢步驟8.3子查詢三張數據表聲明：學生信息純屬虛構【任務實施】3.查詢選修了“高等數學”這門課的學生姓名。

分析：第一步：查找‘高等數學’這門課的課程號第二步：根據第一步得到的課程號查選修該門課的學生學號第三步：根據第二步得到的學號集合查這些學生的姓名

8.3子查詢三個數據表解決問題思路：把一個復雜的多表查詢問題轉換為三個簡單的單表查詢問題?！救蝿諏嵤康谝徊剑翰檎摇叩葦祵W’這門課的課程號第二步：根據第一步得到的課程號查選修該門課的學生學號

8.3子查詢【任務實施】第三步：根據第二步得到的學號查學生的姓名

8.3子查詢所有選修了”高等數學”的學生的學號思政小貼士【規(guī)范化、標準化的代碼編寫習慣】培養(yǎng)一絲不茍的工匠精神子查詢8.3【任務實施】8.3子查詢聲明：學生信息純屬虛構上機過程演示【任務實施】8.3子查詢類似查詢問題：查詢選修了李斯文老師講授課程的學生信息

大家一起來出題。。。。。。

拓展題：（1）查詢既選修了“高等數學”又選修了“英語”的學生姓名。（2）查詢選修了“高等數學”但沒有選修“英語”的學生姓名。【任務實施】8.3子查詢討論與總結：哪類多表查詢問題可以用子查詢來完成？總結：當查詢的數據項在同一個表中，但是篩選數據的條件涉及到其他表的數據時，可以用子查詢來完成?！救蝿諏嵤?.查詢成績最高的選課記錄第一步：查找學生選課表中的最高成績

SELECTMAX(stuscore)FROMstumarks;第二步：查找成績等于最高成績的選課記錄SELECT*FROMstumarksWHEREstuscore=(SELECTmax(stuscore)FROMstumarks);8.3子查詢【任務實施】8.3子查詢【任務實施】5.查詢年齡最小的學生的基本信息方法一：先找出最大的出生日期（年齡最小）SELECT*FROMstuinfoWHEREstubirthday=(SELECTMAX(stubirthday)FROMstuinfo)方法二：逐一比較SELECT*FROMstuinfoWHEREstubirthday>=ALL(SELECTstubirthdayFROMstuinfo)8.3子查詢【任務實施】6.查詢年齡不是最小的學生的基本信息方法一：先找出最大出生日期（年齡最?。㏒ELECT*FROMstuinfoWHEREstubirthday<(SELECTMAX(stubirthday)FROMstuinfo)方法二：出生日期只要小于某個同學的出生日期即可SELECT*FROMstuinfoWHEREstubirthday<ANY(SELECTstubirthdayFROMstuinfo)8.3子查詢重要知識點1.[NOT]IN子查詢:子查詢結果是集合（常用）2.比較子查詢：子查詢結果是單個值3.[NOT]EXISTS子查詢：判斷子查詢是否返回任何記錄。內外相關~4.一題可以多解(1)內連接、IN子查詢、EXISTS子查詢

(2)外連接、NOTIN子查詢、NOTEXISTS子查詢

8.3子查詢任務8.4子查詢在更新語句中的應用子查詢可以嵌套在INSERT、UPDATE、DELETE語句中使用。

對“學生成績管理”數據庫的數據表進行數據更新時應用子查詢，以實現比項目六更強大的數據更新能力。（具體內容見任務實施）【任務描述】8.4子查詢在更新語句中的應用【相關知識】31UPDATE和DELETE語句的條件子句帶子查詢

從一個表向另一個表復制多行多列數據8.4子查詢在更新語句中的應用2

嵌套修改1．從一個表向另一個表復制多行多列數據語法格式如下：INSERTINTO表名[(字段列表)]SELECT語句;說明：字段列表中字段的個數、數據類型必須和SELECT語句中查詢的數據項個數及數據類型一

一對應。8.4子查詢在更新語句中的應用【相關知識】2．嵌套修改利用子查詢返回的單個值，修改表中某個字段值。語法格式如下：UPDATE表名SET字段名＝(返回單個值的子查詢)[WHERE條件]8.4子查詢在更新語句中的應用【相關知識】3．UPDATE和DELETE語句的條件子句帶子查詢

當UPDATE、DELETE語句修改、刪除數據時的篩選條件比較復雜，甚至需要通過另一個表的數據來判斷，如果在UPDATE、DELETE語句的WHERE子句中使用子查詢，基本可以滿足這種篩選需求。8.4子查詢在更新語句中的應用【相關知識】【任務實施】準備工作：數據庫studb三個數據表的數據如任務1.2中表1-3、表1-4、表1-5所示。8.4子查詢在更新語句中的應用【任務實施】1.創(chuàng)建一個空表stuinfo_2（stuno,stuname,avg_stuscore)，要求用INSERT語句把stuinfo表中stuno,stuname兩個字段的數據導入到stuinfo_2表中相應字段。(1)創(chuàng)建空表stuinfo_2CREATETABLEstuinfo_2(stunoCHAR(4)PRIMARYKEY,stunameCHAR(5),avg_stuscoreDECIMAL(4,1));子查詢在更新語句中的應用8.4【任務實施】(2)stuinfo_2表中導入stuinfo表中stuno,stuname兩個字段的數據INSERTINTOstuinfo_2(stuno,stuname)SELECTstuno,stunameFROMstuinfo;子查詢在更新語句中的應用8.4聲明：學生信息純屬虛構【任務實施】2．修改stuinfo_2表中“S001”同學的平均成績(avg_stuscore)（注：平均分統計根據stumarks表中該生的選課成績）。UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)FROMstumarksWHEREstuno='S001')WHEREstuno='S001';子查詢在更新語句中的應用8.4聲明：學生信息純屬虛構【任務實施】思考題：如果要一次修改所有同學的平均分，前面代碼應該怎么改？

子查詢在更新語句中的應用8.4UPDATEstuinfo_2SETavg_stuscore=(SELECTAVG(stuscore)FROMstumarksWHEREstuno=stuinfo_2.stuno);聲明：學生信息純屬虛構【任務實施】3．把“高等數學”這門課的所有選修成績都加5分。UPDATEstumarksSETstuscore=stuscore+5WHEREcno=(SELECTcnoFROMstucourseWHEREcname='高等數學');子查詢在更新語句中的應用8.4【任務實施】4．刪除“劉衛(wèi)平”同學的所有選課記錄（假設“劉衛(wèi)平”沒有同名）。

DELETEFROMstumarksWHEREstuno=(SELECTstunoFROMstuinfoWHEREstuname='劉衛(wèi)平');子查詢在更新語句中的應用8.4重要知識點1.從一個表向另一個表復制多行多列數據INSERTINTO表名[(字段列表)]SELECT語句;2.嵌套修改

UPDATE表名SET字段名＝(返回單個值的子查詢)[WHERE條件]3.UPDATE和DELETE語句的條件子句帶子查詢

8.4子查詢在更新語句中的應用任務8.5集合查詢用集合查詢完成對“學生成績管理”數據庫的以下查詢任務：(1）查詢選修了“0001”或“0003”號課程的學生的學號，結果保留重復行。(2）查詢選修了“0001”或“0003”號課程的學生的學號，結果去掉重復行

（3）查詢既選修了“0001”又選修了“0003”號課程的學生的學號。

（4）查詢選修了“0001”號，但沒有選修“0003”號課程的學生的學號?！救蝿彰枋觥?.5集合查詢集合查詢可以把一些復雜的查詢問題簡單化，SQL提供了集合查詢的并、交、差操作。（注：MySQL8.0.31版本開始才支持交、差操作）語法格式：

查詢1

集合操作符

查詢2說明：查詢1和查詢2的結果集的字段個數和數據類型要一一對應。

8.5集合查詢【相關知識】1．并集操作（UNION|UNIONALL）:合并兩個查詢結果集

語法格式：

查詢1

UNION|UNIONALL

查詢2說明：UNIONALL是簡單合并，重復行保留，UNION則會合并后去掉重復行