數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（MySQL版） 課件 項(xiàng)目4 使用數(shù)據(jù)查詢

項(xiàng)目4使用數(shù)據(jù)查詢目錄任務(wù)1單表查詢?nèi)蝿?wù)2多表查詢?nèi)蝿?wù)3分類匯總與排序任務(wù)1單表查詢4.1.1SELECT語句定義SQL的SELECT語句可以實(shí)現(xiàn)對(duì)表的選擇、投影及連接操作。即SELECT語句可以從一個(gè)或多個(gè)表中根據(jù)用戶的需要從數(shù)據(jù)庫(kù)中選出匹配的行和列，結(jié)果通常是生成一個(gè)臨時(shí)表。SELECT語句是SQL的核心。語法格式：SELECT[ALLIDISTINCT]輸出列表達(dá)式，…[FROM表名1[,表名2]...]/*FROM子句*/[WHERE條件]/*WHERE子句*/[GROUPBY{列名|表達(dá)式|列編號(hào)｝[ASC|DESC],.../*GROUPBY子句*/[HAVING條件］/*HAVING子句*/[ORDERBY{列名I表達(dá)式I列編號(hào)｝/*ORDERBY子句＊／[ASCIDESC],…］[LIMIT{[偏移量,］行數(shù)|列數(shù)OFFSET偏移量}]/*LIMIT子句＊／SELECT語句功能強(qiáng)大，有很多子句，所有被使用的子句必須按語法說明的順序嚴(yán)格地排序。例如，一個(gè)HAVING子句必須位于GROUPBY子句之后、ORDERBY子句之前。下面將逐一介紹SELECT語句中包含的各個(gè)子句。4.1.2選擇列從SELECT語句基本語法可以看出，最簡(jiǎn)單的SELECT語句是：SELECT表達(dá)式輸出列表達(dá)式可以是MySQL所支持的任何運(yùn)算的表達(dá)式，利用這個(gè)最簡(jiǎn)單的SELECT語句，可以進(jìn)行如“1+1”這樣的運(yùn)算。若SELECT語句的表達(dá)式是表中的字段名變量，則字段名變量名之間要以逗號(hào)分隔。【例4-1】查詢aircompany表中航空公司的名稱、地址。mysql>SELECTcompanyName,addressFROMaircompany;+--------------+---------------------------------+|companyName|address|+--------------+---------------------------------+|兗州航空|山東金鄉(xiāng)擱撈撈278號(hào)||豫州航空|安徽亳州擱撈撈2550號(hào)|……|南中航空|四川擱撈撈四段六號(hào)||青州航空|山東淄博市擱撈撈5746號(hào)|+--------------+---------------------------------+9rowsinset(0.00sec)選擇指定的列4.1.2選擇列對(duì)表只選擇某些列時(shí)，可能會(huì)出現(xiàn)重復(fù)行。例如，若對(duì)Bookstore數(shù)據(jù)庫(kù)的Book表只選擇圖書類別和出版社，則出現(xiàn)多行重復(fù)的情況?？梢允褂肈ISTINCT關(guān)鍵字消除結(jié)果集中的重復(fù)行，其格式為：SELECTDISTINCT列名1[,列名2…]其含義是對(duì)結(jié)果集中的重復(fù)行只選擇一行，保證行的唯一性?！纠?-2】查詢flight表中起飛地點(diǎn)信息，要求返回的信息不重復(fù)。mysql>SELECTDISTINCTfromCityFROMflight;+----------+|fromCity|+----------+|兗州||漢中|……|涼州||冀州|+----------+9rowsinset(0.00sec)消除結(jié)果集中的重復(fù)行4.1.2選擇列當(dāng)希望查詢結(jié)果中的列使用自定義的列標(biāo)題時(shí)，可以在列名之后使用AS子句來更改查詢結(jié)果的列名，其格式為：SELECT列名[AS]別名當(dāng)自定義的列標(biāo)題中含有空格時(shí)，必須使用引號(hào)將標(biāo)題括起來。注意：不允許在WHERE子句中使用列別名。這是因?yàn)樵趫?zhí)行WHERE代碼時(shí)可能尚未確定列值?！纠?-3】查詢aircompany表中的航空公司ID和航空公司，結(jié)果中各列的標(biāo)題分別指定為航空公司ID和航空公司名稱。mysql>SELECTa_idAS'航空公司ID',c_nameAS'航空公司名稱'FROMaircompany;+----------------+--------------------+|航空公司ID|航空公司名稱|+----------------+--------------------+|1|兗州航空||2|豫州航空||3|幽州航空|……|8|南中航空||9|青州航空|+----------------+--------------------+9rowsinset(0.00sec)定義列別名4.1.2選擇列使用SELECT對(duì)列進(jìn)行查詢時(shí)，在結(jié)果中可以輸出對(duì)列值計(jì)算后的值，即SELECT子句可使用表達(dá)式作為結(jié)果。【例4-4】按照往返路程計(jì)算，顯示各個(gè)航班的往返公里數(shù)。mysql>SELECTf_id,fromCity,toCity,mileage*2FROMflight;+--------+----------+--------+-----------+|f_id|fromCity|toCity|mileage*2|+--------+----------+--------+-----------+|3U8962|兗州|豫州|5600||9C6859|漢中|南中|3746||WL3121|夷陵|兗州|3396|……|WS3121|夷陵|兗州|3396|+--------+----------+--------+-----------+26rowsinset(0.00sec)計(jì)算列值4.1.2選擇列在對(duì)表進(jìn)行查詢時(shí)，有時(shí)對(duì)所查詢的某些列希望得到的是一種概念而不是具體的數(shù)據(jù)。例如查詢航空表的庫(kù)存數(shù)量，所希望知道的是庫(kù)存的總體情況而不是庫(kù)存數(shù)量，這時(shí)就可以用庫(kù)存情況來替換具體的庫(kù)存數(shù)。要替換查詢結(jié)果中的數(shù)據(jù)，則使用查詢中的CASE表達(dá)式，其格式如下：CASEWHEN條件1THEN表達(dá)式1WHEN條件2THEN表達(dá)式2……ELSE表達(dá)式nEND語法說明：CASE表達(dá)式以CASE開始，END結(jié)束，MySQL從條件1開始判斷，條件1成立輸出表達(dá)式1，結(jié)束；若條件1不成立，判斷條件2,若條件2成立，輸出表達(dá)式2后結(jié)束，……如果條件都不成立，輸出表達(dá)式n。替換查詢結(jié)果中的數(shù)據(jù)4.1.3聚合函數(shù)SELECT子句的表達(dá)式中可以包含所謂的聚合函數(shù)(aggregationfunction)。聚合函數(shù)常常用于對(duì)一組值進(jìn)行計(jì)算，然后返回單個(gè)值。除COUNT()函數(shù)外，聚合函數(shù)都會(huì)忽略空值。聚合函數(shù)通常與GROUPBY子句一起使用。若SELECT語句中有一個(gè)GROUPBY子句，則該聚合函數(shù)對(duì)所有列起作用；若沒有，則SELECT語句只產(chǎn)生一行作為結(jié)果。表4-1列出了—些常用的聚合函數(shù)：表4-1常用的聚合函數(shù)函數(shù)名 說明COUNT求組中項(xiàng)數(shù)，返回int類型整數(shù)MAX求最大值MIN求最小值SUM返回表達(dá)式中所有值的和AVG求組中值的平均值4.1.3聚合函數(shù)使用聚合函數(shù)查詢COUNT()函數(shù)1聚合函數(shù)中最常使用的是COUNT()函數(shù)，其用于統(tǒng)計(jì)表中滿足條件的行數(shù)或總行數(shù)，返回SELECT語句檢索到的行中非NULL值的數(shù)目；若找不到匹配的行，則返回0。COUNT()函數(shù)的格式如下：COUNT({[ALL|DISTINCT]表達(dá)式}|*)語法說明：?表達(dá)式，可以是常量、列、函數(shù)或表達(dá)式，其數(shù)據(jù)類型是除blob或text之外的任何類型。?ALL|DISTINCT,ALL表示對(duì)所有值進(jìn)行運(yùn)算，DISTINCT表示去除重復(fù)值，默認(rèn)為ALL。?使用COUNT(*)時(shí)將返回檢索行的總數(shù)目，不論其是否包含NULL值?！纠?-5】查詢已售罄的座位數(shù)量。mysql>SELECTCOUNT(s_id)AS'已售罄座位數(shù)'FROMseatWHEREavailableSeatsISNULL;+--------------------------+|已售罄座位數(shù)|+--------------------------+|4|+--------------------------+1rowinset(0.00sec)4.1.3聚合函數(shù)使用聚合函數(shù)查詢SUM()函數(shù)和AVG()函數(shù)2SUM和AVG分別用于求表達(dá)式中所有值項(xiàng)的總和與平均值。SUM和AVG函數(shù)的格式如下：SUM/AVG([ALL|DISTINCT]表達(dá)式）語法說明：表達(dá)式，可以是常量、列、函數(shù)或表達(dá)式，其數(shù)據(jù)類型只能是數(shù)值型數(shù)據(jù)。SUM和AVG函數(shù)的使用語法與COUNT函數(shù)相同。【例4-6】查詢seat表中“機(jī)艙類型”為“經(jīng)濟(jì)艙”的座位價(jià)格總和。mysql>SELECTSUM(fullPrice)AS'價(jià)格總和'FROMseatWHEREgrade='經(jīng)濟(jì)艙';+--------------+|價(jià)格總和|+--------------+|37540.00|+--------------+1rowinset(0.00sec)4.1.3聚合函數(shù)使用聚合函數(shù)查詢SUM()函數(shù)和AVG()函數(shù)2【例4-7】查詢seat表中“機(jī)艙類型”為“經(jīng)濟(jì)艙”的座位價(jià)格平均值。mysql>SELECTAVG(fullPrice)AS'價(jià)格平均值'FROMseatWHEREgrade='經(jīng)濟(jì)艙';+-----------------+|價(jià)格平均值|+-----------------+|1564.166667|+-----------------+1rowinset(0.00sec)4.1.3聚合函數(shù)使用聚合函數(shù)查詢MAX()函數(shù)和MIN()函數(shù)3MAX和MIN分別用于求表達(dá)式中所有值項(xiàng)的最大值與最小值。MAX和MIN函數(shù)的格式如下：MAX|MIN([ALLIDISTINCT]表達(dá)式）語法說明：當(dāng)給定列上只有空值或檢索出的中間結(jié)果為空時(shí)，MAX和MIN函數(shù)的值也為空。MAX和MIN函數(shù)的使用語法與COUNT函數(shù)相同?！纠?-8】查詢seat表中“機(jī)艙類型”為“經(jīng)濟(jì)艙”的座位價(jià)格最大值。mysql>SELECTMAX(fullPrice)AS'最大價(jià)格'FROMseatWHEREgrade='經(jīng)濟(jì)艙';+--------------+|最大價(jià)格|+--------------+|1900.00|+--------------+1rowinset(0.00sec)4.1.3聚合函數(shù)使用聚合函數(shù)查詢MAX()函數(shù)和MIN()函數(shù)2【例4-9】查詢seat表中“機(jī)艙類型”為“經(jīng)濟(jì)艙”的座位價(jià)格最小值。mysql>SELECTMIN(fullPrice)AS'最小價(jià)格'FROMseatWHEREgrade='經(jīng)濟(jì)艙';+--------------+|最小價(jià)格|+--------------+|1080.00|+--------------+1rowinset(0.00sec)4.1.4WHERE子句在了解了WHERE子句的用法后，本節(jié)將詳細(xì)討論WHERE子句中查詢條件的構(gòu)成。WHERE子句必須緊跟在FROM子句之后；在WHERE子句中，使用一個(gè)條件從FROM子句的中間結(jié)果中選取行。WHERE子句的格式為：WHERE<判定運(yùn)算＞判定運(yùn)算：結(jié)果為TRUE、FALSE或UNKNOWN,格式如下：表達(dá)式{=I<I<=I>I>=I<=>I<>I!=}表達(dá)式

／＊比較運(yùn)算＊／|表達(dá)式[NOT]LIKE表達(dá)式/*LIKE運(yùn)算符＊／|表達(dá)式[NOT]BETWEEN表達(dá)式1AND表達(dá)式2/*指定范圍＊／|表達(dá)式IS[NOT]NULL/*是否空值判斷＊／|表達(dá)式[NOT]IN(子查詢|表達(dá)式1[,...表達(dá)式n])/*IN子句＊／WHERE子句會(huì)根據(jù)條件對(duì)FROM子句的中間結(jié)果中的行一行一行地進(jìn)行判斷，當(dāng)條件為TRUE時(shí)，一行就被包含到WHERE子句的中間結(jié)果集中?！纠?-10】查詢passenger表中所有男乘客的信息。4.1.4WHERE子句mysql>SELECT*FROMpassengerWHEREgenger='男';+--------------------+--------+--------+------------+-------------+|p_id|name|genger|birthday|tel|+--------------------+--------+--------+------------+-------------+|123698547852369874|曹操|(zhì)男|2022-10-20|156487953214789632|關(guān)羽|男|2022-10-20|321654987456987123|周瑜|男|2022-10-20|369874125896547123|張飛|男|2022-10-20|456987123654789321|曹丕|男|2022-10-20|785463219874563214|趙云|男|2022-10-20|852369741258963214|王營(yíng)|男|2022-10-20|982536014789632541|劉備|男|2022-10-20|987654321456123789|孫權(quán)|男|2022-10-20+--------------------+--------+--------+------------+-------------+9rowsinset(0.00sec)

提示在SQL中，返回邏輯值(TRUE或FALSE)的運(yùn)算符或關(guān)鍵字都可稱為謂詞，判定運(yùn)算包括比較運(yùn)算、模式匹配、范圍比較、空值比較和子查詢。【例4-11】查詢flight表中公里數(shù)小于2000的航班號(hào)id、航空公司ID和飛機(jī)型號(hào)。4.1.4WHERE子句mysql>SELECTf_id,a_id,mileageFROMflightWHEREmileage<2000;+--------+------+---------+|f_id|a_id|mileage|+--------+------+---------+|9C6859|1|1873||9C7027|1|1100||9C8673|4|1100|……|WL3121|2|1698||WS3121|1|1698|+--------+------+---------+15rowsinset(0.00sec)比較運(yùn)算比較運(yùn)算符用于比較兩個(gè)表達(dá)式值，MySQL支持的比較運(yùn)算符有：＝（等于）、＜（小于）、＜＝（小于等于）、＞（大于）、＞＝（大于等于）、＜＝＞（相等或都等于空）、<>（不等于）、＝?。ú坏扔冢１容^運(yùn)算的格式如下：表達(dá)式{=I<I<=I>I>=I<=>I<>I!=}表達(dá)式表達(dá)式是除TEXT和BLOB外類型的表達(dá)式。當(dāng)兩個(gè)表達(dá)式值均不為空值(NULL)時(shí)，除了"<=>"運(yùn)算符，其他比較運(yùn)算返回邏輯值TRUE(真)或FALSE(假)；而當(dāng)兩個(gè)表達(dá)式值中有一個(gè)為空值或都為空值時(shí)，將返回UNKNOWN?！纠?-12】查詢seat表中座位已售罄的信息（可購(gòu)座位數(shù)為null）4.1.4WHERE子句mysql>SELECT*FROMseatwhereavailableSeatsisNULL;+------+--------+------------+-----------+-------+----------------+-----------+|s_id|f_id|flightDate|grade|seats|availableSeats|fullPrice|+------+--------+------------+-----------+-------+----------------+-----------+|15|NS3507|2022-06-21|商務(wù)艙|16|NULL|1580.00||17|9C7027|2022-06-21|頭等艙|4|NULL|2100.00||18|9C7027|2022-06-21|商務(wù)艙|12|NULL|1620.00||21|9C8975|2022-06-21|商務(wù)艙|16|NULL|1480.00|+------+--------+------------+-----------+-------+----------------+-----------+4rowsinset(0.00sec)空值比較當(dāng)需要判定一個(gè)表達(dá)式的值是否為空值時(shí)，使用ISNULL關(guān)鍵字。其格式如下：表達(dá)式IS[NOT]NULL若表達(dá)式的值為空值，返回TRUE，否則返回FALSE；當(dāng)使用NOT時(shí)，結(jié)果剛好相反?！纠?-13】查詢passenger表中年齡在50-70之間的乘客id、乘客的姓名和年齡。4.1.4WHERE子句mysql>SELECTp_id,name,ageFROMpassengerWHEREage>=50andage<=70;+--------------------+--------+-----+|p_id|name|age|+--------------------+--------+-----+|123698547852369874|曹操|(zhì)65||156487953214789632|關(guān)羽|58||369874125896547123|張飛|54|……|987654321456123789|孫權(quán)|70|+--------------------+--------+-----+7rowsinset(0.00sec)范圍比較用于范圍比較的關(guān)鍵字有兩個(gè)，分別是BETWEEN和IN。當(dāng)要查詢的條件是某個(gè)值的范圍時(shí)，可以使用BETWEEN關(guān)鍵字。BETWEEN關(guān)鍵字指出查詢范圍，格式為：表達(dá)式[NOT]BETWEEN表達(dá)式1AND表達(dá)式2說明：若表達(dá)式expression的值在表達(dá)式1與表達(dá)式2之間（包括這兩個(gè)值），則返回TRUE,否則返回FALSE;使用NOT時(shí)，返回值剛好相反。表達(dá)式1的值不能大于表達(dá)式2的值?！纠?-14】查詢passenger表中年齡在50-70之間的乘客id、乘客的姓名和年齡。4.1.4WHERE子句mysql>SELECTp_id,name,ageFROMpassengerWHEREageBETWEEN50AND70;+--------------------+--------+-----+|p_id|name|age|+--------------------+--------+-----+|123698547852369874|曹操|(zhì)65||156487953214789632|關(guān)羽|58||369874125896547123|張飛|54||785463219874563214|趙云|61||852369741258963214|王營(yíng)|50||982536014789632541|劉備|62||987654321456123789|孫權(quán)|70|+--------------------+--------+-----+7rowsinset(0.00sec)范圍比較【例4-15】查詢flight表中起飛地點(diǎn)是“漢中”或“荊州”的航班號(hào)id、航空公司ID和起飛地點(diǎn)。4.1.4WHERE子句mysql>SELECTf_id,a_id,fromCityFROMflightWHEREfromCityIN('漢中','荊州');+--------+------+----------+|f_id|a_id|fromCity|+--------+------+----------+|9C6859|1|漢中||CA2733|7|荊州||CZ3109|2|荊州||CZ3907|1|荊州||KN2397|4|漢中||NS3267|4|漢中||PN6411|5|漢中|+--------+------+----------+7rowsinset(0.00sec)范圍比較使用IN關(guān)鍵字可以指定一個(gè)值表，值表中列出所有可能的值，當(dāng)與值表中的任一個(gè)匹配時(shí)，即返回TRUE,否則返回FALSE。使用IN關(guān)鍵字指定值表的格式為：表達(dá)式[NOT]IN(子查詢|表達(dá)式1[,...表達(dá)式n]IN關(guān)鍵字應(yīng)用最多的是表達(dá)子查詢，也可以用于OR運(yùn)算?！纠?-16】查詢flight表中以蜀國(guó)開頭的飛機(jī)型號(hào)的航班號(hào)id、航空公司ID和飛機(jī)型號(hào)。4.1.4WHERE子句mysql>SELECTf_id,a_id,modelFROMflightWHEREmodelLIKE'蜀國(guó)%';+--------+------+-----------------+|f_id|a_id|model|+--------+------+-----------------+|3U8962|8|蜀國(guó)空客320||9C7411|8|蜀國(guó)空客320||9C8643|2|蜀國(guó)空客320|……|WS3121|1|蜀國(guó)空客320|+--------+------+-----------------+13rowsinset(0.00sec)模式匹配LIKE運(yùn)算符LIKE運(yùn)算符用于指出一個(gè)字符串是否與指定的字符串相匹配，其運(yùn)算對(duì)象可以是char、varchar、text、datetime等類型的數(shù)據(jù)，返回邏輯值TRUE或FALSE。LIKE謂詞表達(dá)式的語法格式為：表達(dá)式[NOT]LIKE表達(dá)式使用LIKE進(jìn)行模式匹配時(shí)，常使用特殊符號(hào)＿和％進(jìn)行模糊查詢。％代表0個(gè)或多個(gè)字符，＿代表單個(gè)字符。

提示由于MySQL默認(rèn)不區(qū)分大小寫，要區(qū)分大小寫時(shí)需要更換字符集的校對(duì)規(guī)則。【例4-17】查詢passenger表中電話號(hào)碼不是以159開頭的乘客id、姓名和電話。4.1.4WHERE子句mysql>SELECTp_id,name,telFROMpassengerWHEREtelNOTLIKE'159%';+--------------------+--------+-------------+|p_id|name|tel|+--------------------+--------+-------------+|123698547852369874|曹操|(zhì)456987123654789321|曹丕|741258963214785369|朱雋|785463219874563214|趙云|852369741258963214|王營(yíng)|982536014789632541|劉備|987654321456123789|孫權(quán)+--------------------+--------+-------------+7rowsinset(0.00sec)范圍比較

提示若要查找特殊符號(hào)中的一個(gè)或全部(＿和％)，須使用一個(gè)轉(zhuǎn)義字符。如當(dāng)要查找下劃線＿時(shí)，可以使用ESCAPE'#'來定義＃為轉(zhuǎn)義字符，這樣，語句中在＃后面的＿就失去了其原來特殊的意義，被視為正常的下劃線＿。【例4-18】查詢passenger表中姓名包含下劃線的乘客id、姓名和電話。4.1.4WHERE子句mysql>SELECTp_id,name,telFROMpassengerWHEREnameLIKE'%#_%'ESCAPE'#';+--------------------+------+-------------+|p_id|name|tel|+--------------------+------+-------------+|147891236548546321|貂_+--------------------+------+-------------+1rowinset(0.00sec)范圍比較任務(wù)2多表查詢4.2.1FROM子句前面介紹了使用SELECT子句選擇列，以下討論SELECT查詢的對(duì)象（即數(shù)據(jù)源）的構(gòu)成形式。SELECT的查詢對(duì)象由FROM子句指定。FROM子句格式如下：FROM表名1[[AS]別名1][,表名2[[AS]別名2]]…

／＊查詢表＊／|JOIN子句

／＊連接表＊／語法說明：表名1[[AS]別名1]，與列別名一樣，可以使用AS選項(xiàng)為表指定別名。表別名主要用于相關(guān)子查詢及連接查詢中。若FROM子句指定了表別名，該SELECT語句中的其他子句都必須使用表別名來代替原始的表名。當(dāng)同一個(gè)表在SELECT語句中多次被提到時(shí)，就必須要使用表別名來加以區(qū)分。?JOIN子句，將在后面多表連接中討論。FROM子句可以用兩種方式引用一個(gè)表，第1種方式是使用USE語句讓一個(gè)數(shù)據(jù)庫(kù)成為當(dāng)前數(shù)據(jù)庫(kù)，在該情況下，若在FROM子句中指定表名，則該表應(yīng)該屬千當(dāng)前數(shù)據(jù)庫(kù)。第2種方式是指定的時(shí)候在表名前帶上表所屬數(shù)據(jù)庫(kù)的名字。例如，假設(shè)當(dāng)前數(shù)據(jù)庫(kù)是dbl,現(xiàn)在要顯示數(shù)據(jù)庫(kù)db2里的表tb的內(nèi)容，則使用語句：SELECT*FROMdb2.tb;。在SELECT關(guān)鍵字后指定列名的時(shí)候也可以在列名前帶上所屬數(shù)據(jù)庫(kù)和表的名字，但是一般來說，若選擇的字段在各表中是唯一的，就沒有必要去特別指定。【例4-19】查詢乘客已訂購(gòu)的機(jī)票相關(guān)的航空公司、飛機(jī)型號(hào)和倉(cāng)位類型。4.2.1FROM子句mysql>SELECTa.c_nameAS'航空公司',f.modelAS'飛機(jī)型號(hào)',o.gradeAS'倉(cāng)位類型'FROMaircompanyaINNERJOINflightfONa.a_id=f.a_idINNERJOINordersoONo.f_id=f.f_id;+--------------+-----------------+--------------+|航空公司|飛機(jī)型號(hào)|倉(cāng)位類型|+--------------+-----------------+--------------+|南中航空|蜀國(guó)空客320|經(jīng)濟(jì)艙||兗州航空|吳國(guó)波音738|經(jīng)濟(jì)艙||夷陵航空|蜀國(guó)空客320|商務(wù)艙|……|豫州航空|吳國(guó)波音738|經(jīng)濟(jì)艙||涼州航空|吳國(guó)波音738|頭等艙||涼州航空|吳國(guó)波音738|商務(wù)艙|+--------------+-----------------+--------------+26rowsinset(0.00sec)4.2.2多表連接若要在不同表中查詢數(shù)據(jù)，則必須在FROM子句中指定多個(gè)表。將不同列的數(shù)據(jù)組合到一個(gè)表中叫做表的連接。例如，在Bookstore數(shù)據(jù)庫(kù)中需要查找訂購(gòu)了“網(wǎng)頁程序設(shè)計(jì)“圖書的會(huì)員的姓名和訂購(gòu)數(shù)量，就需要將Book、Members和Sell這3個(gè)表進(jìn)行連接，才能查找到結(jié)果。(1)連接方式1)全連接全連接是指將每個(gè)表的每行都與其他表中的每行交叉以產(chǎn)生所有可能的組合，列包含了所有表中出現(xiàn)的列，也就是笛卡兒積。如表4-4有3行，表4-5有2行，表4-4和表4-5全連接后得到6行(3x2=6)的表4-6。表4-4T1T21A6F2B表4-5T3T4T513M20N表4-6表4-4和表4-5全連接后的結(jié)果T1T2T3T4T51A13M6F13M2B13M1A20N6F20N2B20N4.2.2多表連接2)內(nèi)連接從表4-6可以看出，全連接得到的表產(chǎn)生數(shù)量非常多的行，其得到的行數(shù)為每個(gè)表中行數(shù)之積，而且全連接產(chǎn)生的表中數(shù)據(jù)在大多數(shù)情況下都沒有意義。在這樣的情形下，通常要設(shè)定條件來將結(jié)果集減少且有意義的表，這樣的連接即為內(nèi)連接。若設(shè)定的條件是等值條件，也叫等值連接。若表4-4和表4-5進(jìn)行等值連接(T1=T3),則形成表4-7,只有兩行。表4-7表4-4和表4-5等值連接(T1=T3)后的結(jié)果T1T2T3T4T51A13M2B20N4.2.2多表連接(3)外連接外連接包括左外連接(LEFTOUTERJOIN)和右外連接(RIGHTOUTERJOIN)兩種。左外連接：結(jié)果表中除了匹配行外，還包括左表有的但右表中不匹配的行，對(duì)千這樣的行，從右表被選擇的列設(shè)置為NULL。表4-4與表4-5左外連接(T1=T3)后的結(jié)果見表4-8。表4-8表4-4和表4-5左外連接(T1=T3)后的結(jié)果T1T2T3T4T51A13M2B20N6FNULLNULLNULL右外連接：結(jié)果表中除了匹配行外，還包括右表有的但左表中不匹配的行，對(duì)于這樣的行，從左表被選擇的列設(shè)置為NULL。表4-5與表4-4右外連接(T3=T1)后的結(jié)果見表5-9。表4-9表4-5和表4-4右外連接(T3=T1)后的結(jié)果T3T4T5T1T213M1A20N2BNULLNULLNULL6F若FROM子句中將各表用逗號(hào)分隔，就指定了全連接，全連接得到的表產(chǎn)生數(shù)量非常多的行?！纠?-20】使用多表連接查詢每個(gè)航空公司及其航班。mysql>SELECTa.c_nameAS'航空公司',f.f_idAS'航班'FROMaircompanya,flightfWHEREa.a_id=f.a_id;+--------------+--------+|航空公司|航班|+--------------+--------+|兗州航空|9C6859||兗州航空|9C7027||兗州航空|CZ3907||兗州航空|WS3121|……|青州航空|9C8899||青州航空|GX2039|+--------------+--------+26rowsinset(0.00sec)4.2.2多表連接4.2.3聯(lián)合查詢使用JOIN關(guān)鍵字建立多表連接時(shí)，JOIN中定義了如何使用JOIN關(guān)鍵字連接表。JOIN子句格式如下：表名1INNERJOIN表名2|表名1{LEFT|RIGHT}[OUTER]JOIN表名2ON連接條件|USING(列名)使用JOIN關(guān)鍵字的連接主要分為以下3種。1)內(nèi)連接指定INNER關(guān)鍵字的連接是內(nèi)連接。內(nèi)連接是在FROM子句產(chǎn)生的中間結(jié)果中應(yīng)用ON條件后得到的結(jié)果?！纠?-21】使用等值連接查詢每個(gè)航空公司及其航班。mysql>SELECTa.c_nameAS'航空公司',f.f_idAS'航班'FROMaircompanyaINNERJOINflightfONa.a_id=f.a_id;+--------------+--------+|航空公司|航班|+--------------+--------+|兗州航空|9C6859||兗州航空|9C7027||兗州航空|CZ3907||兗州航空|WS3121|……|南中航空|3U8962||南中航空|9C7411||青州航空|9C8899||青州航空|GX2039|+--------------+--------+26rowsinset(0.00sec)4.2.3聯(lián)合查詢【例4-22】查詢航空公司、飛機(jī)類型和起飛時(shí)間。mysql>SELECTa.c_name,f.model,f.departureTimeFROMaircompanyaLEFTJOINflightfONa.a_id=f.a_id;+--------------+-----------------+---------------+|c_name|model|departureTime|+--------------+-----------------+---------------+|兗州航空|吳國(guó)波音738|11:51:00||兗州航空|吳國(guó)波音738|08:06:00||兗州航空|蜀國(guó)空客333|18:05:00||兗州航空|蜀國(guó)空客320|16:30:00||豫州航空|蜀國(guó)空客320|11:15:00|…….|青州航空|吳國(guó)波音738|08:45:00||青州航空|吳國(guó)波音738|20:00:00||洛陽航空|NULL|NULL|+--------------+-----------------+---------------+27rowsinset(0.00sec)4.2.3聯(lián)合查詢2)外連接指定OUTER關(guān)鍵字的連接為外連接?！纠?-23】查詢訂單編號(hào)和下單乘客編號(hào)。mysql>SELECTo.o_idAS'訂單編號(hào)',p.p_idAS'乘客編號(hào)'FROMordersoRIGHTJOINpassengerpONo.p_id=p.p_id;+--------------+--------------------+|訂單編號(hào)|乘客編號(hào)|+--------------+--------------------+|12|123698547852369874||13|123698547852369874||19|123698547852369874||28|123698547852369874|……|32|156487953214789632||21|982536014789632541||24|982536014789632541||27|982536014789632541||31|982536014789632541||NULL|987654321456123789|+--------------+--------------------+34rowsinset(0.00sec)4.2.3聯(lián)合查詢4.2.4子查詢?cè)诓樵儣l件中，可以使用另一個(gè)查詢的結(jié)果作為條件的一部分。例如，判定列值是否與某個(gè)查詢的結(jié)果集中的值相等，作為查詢條件一部分的查詢稱為子查詢。SQL標(biāo)準(zhǔn)允許SELECT多層嵌套使用，用來表示復(fù)雜的查詢。子查詢除了可以用在SELECT語句中，還可以用在INSERT、UPDATE及DELETE語句中。子查詢通常與IN、EXIST謂詞及比較運(yùn)算符結(jié)合使用。1)IN子查詢IN子查詢用千進(jìn)行一個(gè)給定值是否在子查詢結(jié)果集中的判斷。其格式為：表達(dá)式

[NOT]IN(子查竘）語法說明：?當(dāng)表達(dá)式與子查詢的結(jié)果表中的某個(gè)值相等時(shí)，IN謂詞返回TRUE,否則返回FALSE;若使用了NOT,則返回的值剛好相反。?IN(子查詢），只能返回一列數(shù)據(jù)。對(duì)于較復(fù)雜的查詢，可以使用嵌套的子查詢?！纠?-24】查詢航班旅程大于平均路程的航班信息。mysql>SELECT*FROMflightwheremileage>(SELECTAVG(mileage)FROMflight);+--------+------+-----------------+----------+--------+---------+---------------+|f_id|a_id|model|fromCity|toCity|mileage|departureTime|+--------+------+-----------------+----------+--------+---------+---------------+|3U8962|8|蜀國(guó)空客320|兗州|豫州|2800|11:35:00||9C6859|1|吳國(guó)波音738|漢中|南中|1873|11:51:00||9C7411|8|蜀國(guó)空客320|青州|幽州|2080|21:30:00||9C8643|2|蜀國(guó)空客320|兗州|豫州|2800|11:15:00||9C8899|9|吳國(guó)波音738|青州|幽州|2080|08:45:00|……|NS3301|3|魏國(guó)空客340|兗州|豫州|2800|08:03:00||PN6411|5|蜀國(guó)空客320|漢中|南中|1873|17:00:00|+--------+------+-----------------+----------+--------+---------+---------------+15rowsinset(0.00sec)4.2.4子查詢【例4-25】用IN子查詢查詢荊州航空公司的航班信息。mysql>SELECT*FROMflightWHEREa_idIN(SELECTa_idFROMaircompanyWHEREc_name='荊州航空');+--------+------+-----------------+----------+--------+---------+---------------+|f_id|a_id|model|fromCity|toCity|mileage|departureTime|+--------+------+-----------------+----------+--------+---------+---------------+|JD5303|5|蜀國(guó)空客320|徐州|青州|2242|14:55:00||NS3287|5|蜀國(guó)空客320|徐州|青州|2242|16:20:00||PN6411|5|蜀國(guó)空客320|漢中|南中|1873|17:00:00|+--------+------+-----------------+----------+--------+---------+---------------+3rowsinset(0.00sec)4.2.4子查詢【例4-26】用NOTIN子查詢查詢不是荊州航空公司的航班信息。mysql>SELECT*FROMflightWHEREa_idNOTIN(SELECTa_idFROMaircompanyWHEREc_name='荊州航空');+--------+------+-----------------+----------+--------+---------+---------------+|f_id|a_id|model|fromCity|toCity|mileage|departureTime|+--------+------+-----------------+----------+--------+---------+---------------+|3U8962|8|蜀國(guó)空客320|兗州|豫州|2800|11:35:00||9C6859|1|吳國(guó)波音738|漢中|南中|1873|11:51:00||9C7027|1|吳國(guó)波音738|涼州|荊州|1100|08:06:00||9C7411|8|蜀國(guó)空客320|青州|幽州|2080|21:30:00|……|WL3121|2|蜀國(guó)空客520|夷陵|兗州|1698|13:50:00||WS3121|1|蜀國(guó)空客320|夷陵|兗州|1698|16:30:00|+--------+------+-----------------+----------+--------+---------+---------------+23rowsinset(0.00sec)4.2.4子查詢4.2.4子查詢(2)EXISTS子查詢EXISTS謂詞用于測(cè)試子查詢的結(jié)果是否為空表，若子查詢的結(jié)果集不為空，則EXISTS返回TRUE,否則返回FALSE。EXISTS還可與NOT結(jié)合使用，即NOTEXISTS,其返回值與EXIST剛好相反。EXISTS子查詢的格式如下：[NOT]EXISTS(子查詢）【例4-27】用EXISTS子查詢查詢荊州航空公司的航班信息mysql>SELECT*FROMflightfWHEREEXISTS(SELECT*fromaircompanyaWHEREf.a_id=a.a_idANDa.c_name='荊州航空');+--------+------+-----------------+----------+--------+---------+---------------+|f_id|a_id|model|fromCity|toCity|mileage|departureTime|+--------+------+-----------------+----------+--------+---------+---------------+|JD5303|5|蜀國(guó)空客320|徐州|青州|2242|14:55:00||NS3287|5|蜀國(guó)空客320|徐州|青州|2242|16:20:00||PN6411|5|蜀國(guó)空客320|漢中|南中|1873|17:00:00|+--------+------+-----------------+----------+--------+---------+---------------+3rowsinset(0.00sec)4.2.4子查詢2)比較子查詢?cè)撟硬樵兛梢哉J(rèn)為是IN子查詢的擴(kuò)展，其使表達(dá)式的值與子查詢的結(jié)果進(jìn)行比較運(yùn)算，格式如下：裝達(dá)式{<|<=|=|>|>=|!=|<>}{ALL|SOME|ANY}(子查竘）語法說明：?表達(dá)式，為要進(jìn)行比較的表達(dá)式。?ALL|SOME|ANY，說明對(duì)比較運(yùn)算的限制。若子查詢的結(jié)果集只返回一行數(shù)據(jù)時(shí)，可以通過比較運(yùn)算符直接比較。若子查詢的結(jié)果集返回多行數(shù)據(jù)時(shí)，需要用{ALL|SOME|ANY}來限定。ALL指定表達(dá)式要與子查詢結(jié)果集中的每個(gè)值都進(jìn)行比較，當(dāng)表達(dá)式的每個(gè)值都滿足比較關(guān)系時(shí)，才返回TRUE,否則返回FALSE；SOME與ANY是同義詞，表示表達(dá)式只要與子查詢結(jié)果集中的某個(gè)值滿足比較關(guān)系時(shí)，就返回TRUE,否則返回FALSE?！纠?-28】查詢價(jià)格比任一商務(wù)艙價(jià)格都貴的座位信息。mysql>SELECT*FROMseatWHEREfullPrice>ANY(SELECTfullPriceFROMseatWHEREgrade='商務(wù)艙');+------+--------+------------+-----------+-------+----------------+-----------+|s_id|f_id|flightDate|grade|seats|availableSeats|fullPrice|+------+--------+------------+-----------+-------+----------------+-----------+|1|9C8643|2022-06-21|頭等艙|4|4|2600.00||2|9C8643|2022-06-21|商務(wù)艙|8|8|2200.00||3|9C8643|2022-06-21|經(jīng)濟(jì)艙|172|172|1610.00||6|NS3223|2022-06-21|頭等艙|2|2|2900.00||7|NS3223|2022-06-21|商務(wù)艙|6|6|2500.00|……|12|NS3301|2022-06-21|頭等艙|8|8|2750.00||13|NS3301|2022-06-21|商務(wù)艙|12|12|2230.00||14|9C8975|2022-06-21|經(jīng)濟(jì)艙|270|268|1610.00|+------+--------+------------+-----------+-------+----------------+-----------+54rowsinset(0.00sec)4.2.4子查詢【例4-29】查詢價(jià)格比所有商務(wù)艙價(jià)格貴的座位信息mysql>SELECT*FROMseatWHEREfullPrice>ALL(SELECTfullPriceFROMseatWHEREgrade='商務(wù)艙');+------+--------+------------+-----------+-------+----------------+-----------+|s_id|f_id|flightDate|grade|seats|availableSeats|fullPrice|+------+--------+------------+-----------+-------+----------------+-----------+|29|NS3267|2022-06-21|頭等艙|4|4|3830.00||32|9C6859|2022-06-21|頭等艙|6|6|3820.00||43|CZ3132|2022-06-21|頭等艙|6|6|3680.00||53|9C7411|2022-06-21|頭等艙|6|6|3880.00||58|CZ3109|2022-06-21|頭等艙|12|12|3890.00||61|CZ3907|2022-06-21|頭等艙|9|9|3770.00||66|WS3121|2022-06-21|頭等艙|4|4|3890.00|+------+--------+------------+-----------+-------+----------------+-----------+7rowsinset(0.00sec)4.2.4子查詢?nèi)蝿?wù)3分類匯總與排序4.3.1GROUPBY子句GROUPBY子句主要用于根據(jù)字段對(duì)行分組。例如，根據(jù)學(xué)生所學(xué)的專業(yè)對(duì)學(xué)生基本表中的所有行分組，結(jié)果是每個(gè)專業(yè)的學(xué)生成為一組。GROUPBY子句的格式如下：GROUPBY{列名|表達(dá)式

|列編號(hào)[ASC|DESC],...[WITHROLLUP]語法說明：?GROUPBY子句后通常包含列名或表達(dá)式。?MySQL對(duì)GROUPBY子句進(jìn)行了擴(kuò)展，可以在列的后面指定ASC(升序）或DESC(降序）。GROUPBY可以根據(jù)一個(gè)或多個(gè)列進(jìn)行分組，也可以根據(jù)表達(dá)式進(jìn)行分組，經(jīng)常和聚合函數(shù)一起使用?！纠?-30】查詢flight表中按照“起飛地點(diǎn)”統(tǒng)計(jì)各個(gè)航班的數(shù)量。mysql>SELECTfromCityAS'起飛地點(diǎn)',COUNT(f_id)AS'航班數(shù)'FROMflightGROUPBYfromCity;+--------------+-----------+|起飛地點(diǎn)|航班數(shù)|+--------------+-----------+|兗州|4||漢中|4||涼州|4||青州|3||荊州|3||夷陵|3||司州|1||徐州|3||冀州|1|+--------------+-----------+9rowsinset(0.00sec)4.3.1GROUPBY子句4.3.2HAVING子句使用HAVING子句的目的與WHERE子句類似，不同的是WHERE子句是用來在FROM子句之后選擇行，而HAVING子句用來在GROUPBY子句后選擇行。HAVING子句格式如下：HAVING條件語法說明：?條件：定義和W印RE子句中的條件類似，不過HAVING子句中的條件可以包含聚合函數(shù)，而WHERE子句中則不可以。SQL標(biāo)準(zhǔn)要求HAVING必須引用GROUPBY子句中的列或用千聚合函數(shù)中的列。不過，MySQL支持對(duì)此工作性質(zhì)的擴(kuò)展，并允許HAVING引用SELECT清單中的列和外部子查詢中的列。【例4-31】查詢flight表中按照“起飛地點(diǎn)”統(tǒng)計(jì)各個(gè)航班的數(shù)量大于3班。mysql>SELECTfromCityAS'起飛地點(diǎn)',COUNT(f_id)AS'航班數(shù)'FROMflightGROUPBYfromCityHAVINGCOUNT(f_id)>3;+--------------+-----------+|起飛地點(diǎn)|航班數(shù)|+--------------+-----------+|兗州|4||漢中|4||涼州|4|+--------------+-----------+3rowsinset(0.00sec)4.3.2HAVING子句4.3.3ORDERBY子句在一條SELECT語句中，若不使用ORDERBY子句，結(jié)果中行的順序是不可預(yù)料的。使用ORDERBY子句后可以保證結(jié)果中的行按一定順序排列。ORDERBY子句格式如下：ORDERBY{列名|表達(dá)式|列編號(hào)}[ASC|DESC],...語法說明：?ORDERBY子句后可以是一個(gè)列、一個(gè)表達(dá)式或一個(gè)正整數(shù)。列編號(hào)是正整數(shù)表示按結(jié)果表中該位置上的列排序。例如，使用ORDERBY3表示對(duì)SELECT的列清單上的第3列進(jìn)行排序。?關(guān)鍵宇A(yù)SC表示升序排列，DESC表示降序排列，系統(tǒng)默認(rèn)值為ASC?！纠?-32】在flight表中按照公里數(shù)升序排列mysql>SELECT*FROMflight