《MySQL數(shù)據(jù)庫(kù)應(yīng)用與管理（第3版）》 課件 5.3 高級(jí)查詢

數(shù)據(jù)查詢

高級(jí)查詢

本節(jié)內(nèi)容1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢2.使用外連接（OUTERJOIN）進(jìn)行多表查詢3.使用統(tǒng)計(jì)函數(shù)對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)匯總4.使用GROUPBY子句對(duì)數(shù)據(jù)進(jìn)行分組匯總本節(jié)內(nèi)容5.使用HAVING子句對(duì)分組匯總結(jié)果進(jìn)行篩選6.子查詢的返回值為單列單值的嵌套查詢7.子查詢的返回值為單列多值的嵌套查詢8.使用EXISTS關(guān)鍵字創(chuàng)建子查詢高級(jí)查詢關(guān)系數(shù)據(jù)庫(kù)在進(jìn)行數(shù)據(jù)表設(shè)計(jì)時(shí)，為了減少冗余，確保數(shù)據(jù)一致性、完整性，要求數(shù)據(jù)表的設(shè)計(jì)符合規(guī)范（比如3NF），為了遵循這些規(guī)范，往往需要將數(shù)據(jù)分離到多張表中。然而在實(shí)際應(yīng)用中，又往往需要將多張表的相關(guān)數(shù)據(jù)提取、聚合后一起提供給用戶，即需要多表查詢。多表查詢的本質(zhì)是多張表通過(guò)關(guān)聯(lián)的列的連接，所以多表查詢也稱為連接查詢。1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢高級(jí)查詢多表（連接）查詢的語(yǔ)法格式有如下兩種：1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢第一種語(yǔ)法格式： SELECT*|字段列表

FROM表名1 [連接類型]JOIN表名2ON連接條件 [[連接類型]JOIN表名3ON連接條件][…] WHERE查詢條件;第二種語(yǔ)法格式： SELECT*|字段列表 FROM表名1,表名2[,表名3,…表名n] WHERE連接條件

AND查詢條件;高級(jí)查詢說(shuō)明：查詢時(shí)所有的字段都必須要明確，為了區(qū)分多張表中出現(xiàn)的重復(fù)字段名，可以在字段列表中使用“表名.字段名”的形式；星號(hào)（*）表示的是多張表中的所有字段，如果要指定某一張表中是所有字段，可以使用“表名.*”的形式。連接類型主要包括：內(nèi)連接（INNER）、左外連接（LEFTOUTER）、右外連接（RIGHTOUTER）等。為了增加可讀性，可以對(duì)數(shù)據(jù)表使用別名進(jìn)行引用。表的別名的使用方法是在表名的后面直接加上一個(gè)別名，原名與別名之間用空格隔開(kāi)；一旦使用了別名代替某個(gè)表，則在連接時(shí)必須用表的別名，不能再用表的原名。1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢高級(jí)查詢內(nèi)連接（INNERJOIN）查詢是最常用的多表查詢形式。內(nèi)連接（INNERJOIN）是指多個(gè)表通過(guò)連接條件中共享列的值進(jìn)行的比較連接，INNER關(guān)鍵字可以省略，當(dāng)未指明連接類型時(shí)，默認(rèn)為內(nèi)連接。內(nèi)連接值顯示兩個(gè)表中所有匹配數(shù)據(jù)的行。1.使用內(nèi)連接（INNERJOIN）進(jìn)行多表查詢操作案例知識(shí)點(diǎn)概述示例5-23運(yùn)行結(jié)果如下：使用兩種語(yǔ)法格式查詢所有女生的學(xué)號(hào)、姓名、性別、課程ID和成績(jī)。操作案例知識(shí)點(diǎn)概述示例5-24運(yùn)行結(jié)果如下：使用兩種語(yǔ)法格式查詢學(xué)號(hào)（sNo）為“1308013101”學(xué)生的學(xué)號(hào)、姓名、性別、班級(jí)、課程名稱和成績(jī)。高級(jí)查詢外連接顯示包含來(lái)自一個(gè)表中所有行和來(lái)自另一個(gè)表中匹配行的結(jié)果集。2.使用外連接（OUTERJOIN）進(jìn)行多表查詢高級(jí)查詢外連接主要又分為左外連接和右外連接，說(shuō)明如下：左外連接（LEFTOUTERJOIN）返回LEFTOUTERJOIN關(guān)鍵字左側(cè)指定的表（左表）的所有行和右側(cè)指定的表（右表）的匹配的行。對(duì)于來(lái)自左表中的行，在右表中沒(méi)有發(fā)現(xiàn)匹配的行，那么在來(lái)自右表中獲得數(shù)據(jù)的列中將顯示NULL值。OUTER關(guān)鍵字可以省略。右外連接（RIGHTOUTERJOIN）即在連接兩表時(shí)，結(jié)果集包含RIGHTOUTERJOIN關(guān)鍵字右側(cè)指定的表（右表）的所有行以及左表匹配的行；對(duì)于來(lái)自右表的行，如果左表無(wú)匹配，則左表的數(shù)據(jù)列將顯示NULL。OUTER關(guān)鍵字可以省略。2.使用外連接（OUTERJOIN）進(jìn)行多表查詢操作案例知識(shí)點(diǎn)概述示例5-25運(yùn)行結(jié)果如下：使用兩種方式查詢“網(wǎng)絡(luò)131”班學(xué)生的學(xué)號(hào)、姓名、性別、班級(jí)、課程ID和成績(jī)（包括沒(méi)有選修課程的學(xué)生信息）。高級(jí)查詢MySQL不僅可以查詢返回滿足條件的記錄，還可以對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)匯總。3.使用統(tǒng)計(jì)函數(shù)對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)匯總操作案例知識(shí)點(diǎn)概述示例5-26運(yùn)行結(jié)果如下：統(tǒng)計(jì)student表中的男生人數(shù)。操作案例知識(shí)點(diǎn)概述示例5-27運(yùn)行結(jié)果如下：統(tǒng)計(jì)course表中的課程總數(shù)、以及備注（remark）不為空的課程總數(shù)。操作案例知識(shí)點(diǎn)概述示例5-28運(yùn)行結(jié)果如下：通過(guò)score表統(tǒng)計(jì)已選修課程的學(xué)生人數(shù)。操作案例知識(shí)點(diǎn)概述示例5-29運(yùn)行結(jié)果如下：統(tǒng)計(jì)學(xué)號(hào)（sNo）為“1308013101”的學(xué)生選修課程的門數(shù)、最高分、最低分、平均分和總分。高級(jí)查詢使用GROUPBY子句，可以顯示分組的匯總數(shù)據(jù)。該子句的功能是按照指定的字段，先將數(shù)據(jù)分成多個(gè)組（相同字段的值為一組），然后對(duì)每個(gè)組匯總出一個(gè)數(shù)據(jù)。結(jié)果集中每個(gè)組都有一行匯總數(shù)據(jù)。其語(yǔ)法格式為： SELECT字段名1[,字段名2,…],統(tǒng)計(jì)函數(shù) FROM表名 [WHERE查詢條件]

GROUPBY字段名1[,字段名2,…]說(shuō)明：GROUPBY子句用來(lái)指定分組的字段，這些字段還必須要全部包含在SELECT子句中。4.使用GROUPBY子句對(duì)數(shù)據(jù)進(jìn)行分組匯總操作案例知識(shí)點(diǎn)概述示例5-30運(yùn)行結(jié)果如下：分組統(tǒng)計(jì)男、女學(xué)生的人數(shù)。高級(jí)查詢使用HAVING子句，可以用來(lái)指定結(jié)果集的組需要滿足的條件，即對(duì)結(jié)果集的組進(jìn)行篩選，僅顯示滿足條件的分組統(tǒng)計(jì)結(jié)果。其語(yǔ)法格式為： SELECT字段名1[,字段名2,…],統(tǒng)計(jì)函數(shù) FROM表名 [WHERE查詢條件] GROUPBY字段名1[,字段名2,…] [HAVING分組條件]5.使用HAVING子句對(duì)分組匯總結(jié)果進(jìn)行篩選高級(jí)查詢說(shuō)明：如果同時(shí)具有WHERE子句、GROUPBY子句、HAVING子句時(shí)，其執(zhí)行順序是：首先執(zhí)行WHERE子句，然后執(zhí)行GROUPBY子句，最后再執(zhí)行HAVING子句。即先使用WHERE查詢出滿足條件的記錄；然后使用GROUPBY對(duì)這些滿足條件的數(shù)據(jù)按照指定的字段分組匯總；最后再使用HAVING子句篩選出符合條件的組。5.使用HAVING子句對(duì)分組匯總結(jié)果進(jìn)行篩選操作案例知識(shí)點(diǎn)概述示例5-31運(yùn)行結(jié)果如下：分組統(tǒng)計(jì)被選修過(guò)1次以上的課程編號(hào)、選修次數(shù)和平均分。說(shuō)明：語(yǔ)句中的“HAVING選修次數(shù)>1”也可以更改為“HAVINGCOUNT(*)>1”。操作案例知識(shí)點(diǎn)概述示例5-32運(yùn)行結(jié)果如下：分組統(tǒng)計(jì)“軟件131”班級(jí)中選修超過(guò)2門課程、且平均成績(jī)高于60分的學(xué)生學(xué)號(hào)、姓名、選修門數(shù)和平均分，并按照平均分降序排列。高級(jí)查詢?cè)陉P(guān)系型數(shù)據(jù)庫(kù)的應(yīng)用中，也經(jīng)常會(huì)涉及到嵌套查詢的使用。嵌套查詢是指一個(gè)SELECT語(yǔ)句的WHERW子句中還包含另外一個(gè)SELECT語(yǔ)句，外層的SELECT語(yǔ)句稱為外部查詢或父查詢，內(nèi)層的SELECT語(yǔ)句稱為內(nèi)部查詢或子查詢，子查詢需要使用圓括號(hào)“（）”括起來(lái)。SQL語(yǔ)言允許多層嵌套查詢，即一個(gè)子查詢中還可以有其他子查詢。嵌套查詢的求解方法是由里向外處理，即每個(gè)子查詢都是在上一級(jí)查詢之前求解，子查詢的結(jié)果用于建立其父查詢的查詢條件。如果子查詢的返回值為單列單值，可以通過(guò)使用“=”、“!=”、“>”、“<”等比較運(yùn)算符直接與父查詢的字段值進(jìn)行比較。6.子查詢的返回值為單列單值的嵌套查詢操作案例知識(shí)點(diǎn)概述示例5-33運(yùn)行結(jié)果如下：查詢與學(xué)號(hào)（sNo）為“1308013101”的同學(xué)在同一個(gè)班級(jí)的學(xué)生名單。操作案例知識(shí)點(diǎn)概述示例5-34運(yùn)行結(jié)果如下：查詢選修課程編號(hào)（cNo）為“01001”的課程、且成績(jī)超過(guò)該課程平均分的學(xué)生的學(xué)號(hào)、姓名、班級(jí)、課程名稱和成績(jī)。高級(jí)查詢?nèi)绻硬樵兊姆祷刂禐閱瘟卸嘀?，可以使用IN或NOTIN關(guān)鍵字，即表示在或者不在子查詢的結(jié)果集中。7.子查詢的返回值為單列多值的嵌套查詢操作案例知識(shí)點(diǎn)概述示例5-35運(yùn)行結(jié)果如下：查詢選修課程編號(hào)（cNo）為“01001”的課程的學(xué)生名單。操作案例知識(shí)點(diǎn)概述示例5-36運(yùn)行結(jié)果如下：查詢學(xué)號(hào)為“1308013101”的學(xué)生選修的課程信息。操作案例知識(shí)點(diǎn)概述示例5-37運(yùn)行結(jié)果如下：查詢沒(méi)有選修課程的女生名單。高級(jí)查詢?nèi)绻硬樵兊姆祷刂禐槎嗔袛?shù)據(jù)，可以使用EXISTS或NOTEXISTS關(guān)鍵字。在WHERE子句中使用EXISTS關(guān)鍵字，表示判斷子查詢的結(jié)果集是否為空，如果子查詢至少返回一行時(shí)，WHERE子句的條件為真，返回TRUE；否則條件為假，返回FALSE。加上關(guān)