第8章

查詢、視圖和索引——數(shù)據(jù)庫(kù)查詢MySQL+實(shí)用教程（第4版）數(shù)據(jù)庫(kù)查詢SELECT語(yǔ)句，它的功能非常強(qiáng)大、使用極為靈活，它可以實(shí)現(xiàn)對(duì)表的選擇、投影及連接操作。主干形式如下：SELECT[ALL|DISTINCT|DISTINCTROW]輸出項(xiàng),... [FROM表名|視圖名] /*指定數(shù)據(jù)源*/ [WHERE條件表達(dá)式] /*指定查詢條件*/ [GROUPBY...] /*指定分組項(xiàng)*/ [HAVING條件] /*指定分組后篩選條件*/ [ORDERBY...] /*指定輸出行排列依據(jù)項(xiàng)*/ [LIMIT行數(shù)] /*指定輸出行范圍*/ [UNIONSELECT語(yǔ)句] /*數(shù)據(jù)源聯(lián)合*/ [WINDOWS...] /*窗口定義*/01選擇輸出項(xiàng)1．輸出項(xiàng)為列名2．輸出項(xiàng)為表達(dá)式3.輸出內(nèi)容變換4．消除輸出項(xiàng)的重復(fù)行5．聚合函數(shù)6.JSON類型列的部分內(nèi)容選擇輸出項(xiàng)1．輸出項(xiàng)為列名輸出項(xiàng)為一個(gè)表中的某些列，各列名之間以逗號(hào)分隔。當(dāng)希望使用自己命名的標(biāo)題時(shí)，可以在列名之后加“AS別名”。【例8.1】查詢網(wǎng)上商城數(shù)據(jù)庫(kù)（emarket）的商品表（commodity）中所有的商品編號(hào)、商品名稱、庫(kù)存量和單價(jià)。USEemarket;SELECT商品編號(hào),商品名稱,庫(kù)存量,價(jià)格AS商品單價(jià)FROMcommodity;查詢結(jié)果如圖8.1所示。選擇輸出項(xiàng)2．輸出項(xiàng)為表達(dá)式列名本身就是一個(gè)簡(jiǎn)單表達(dá)式，也可以是包含列名的一般表達(dá)式。例如，對(duì)數(shù)字列進(jìn)行各種計(jì)算。為了避免不太直觀的表達(dá)式作為輸出項(xiàng)標(biāo)題，可以用AS定義一個(gè)別名作為新的計(jì)算結(jié)果列的名稱。【例8.2】將1000元以上的商品打8折出售，并同時(shí)顯示原價(jià)和優(yōu)惠價(jià)。USEemarket;SELECTLEFT(商品編號(hào),1)AS類別編號(hào),商品編號(hào),商品名稱,價(jià)格AS'原

價(jià)',FORMAT(價(jià)格*0.8,2)AS優(yōu)惠價(jià) FROMcommodity WHERE價(jià)格>1000;查詢結(jié)果如圖8.2所示。說(shuō)明：（1）LEFT(商品編號(hào),1)為字符串表達(dá)式，獲得商品編號(hào)列第1個(gè)字符。（2）不允許在WHERE子句中使用列別名。例如：“WHERE原價(jià)>1000”。（3）別名中含有空格時(shí)，須用引號(hào)括起來(lái)，比如AS'原

價(jià)'。（4）價(jià)格包含兩位小數(shù)，價(jià)格*0.8后就會(huì)超過(guò)兩位，為了顯示兩位小數(shù)，采用FORMAT(價(jià)格*0.8,2)函數(shù)。選擇輸出項(xiàng)【例8.3】查詢用戶（user）表姓名、年齡和身份證是否有效。SELECT姓名, YEAR(NOW())-CONVERT(SUBSTR(身份證號(hào),7,4),UNSIGNED)+1AS年齡,IF(有效期>=NOW(),'有效','過(guò)期')AS身份證有效 FROMuser;查詢結(jié)果如圖8.3所示。說(shuō)明：（1）SUBSTR(身份證號(hào),7,4)是出生年份n，CONVERT(n,UNSIGNED)將n轉(zhuǎn)換為無(wú)符號(hào)整數(shù)n1，YEAR(NOW())為獲得當(dāng)前日期中的年份n2，n2-n1+1就是年齡。（2）IF(有效期>=NOW(),'有效','過(guò)期')：有效期（date類型）列與當(dāng)前日期對(duì)比，大于等于為'有效'否則為'過(guò)期'。選擇輸出項(xiàng)3.輸出內(nèi)容變換在對(duì)表進(jìn)行查詢時(shí)，輸出列顯示內(nèi)容可以通過(guò)CASE語(yǔ)句進(jìn)行變換。CASE WHEN條件1THEN表達(dá)式1 ...... ELSE表達(dá)式nEND【例8.4】在emarket數(shù)據(jù)庫(kù)商品分類表（category）中，顯示商品分類記錄對(duì)應(yīng)的大類。USEemarket;SELECT類別編號(hào),類別名稱, CASE WHENLEFT(類別編號(hào),1)='1'THEN'水果' WHENLEFT(類別編號(hào),1)='2'THEN'肉禽' WHENLEFT(類別編號(hào),1)='3'THEN'海鮮水產(chǎn)' ELSE'糧油蛋' ENDAS大類 FROMcategory;選擇輸出項(xiàng)查詢結(jié)果如圖8.4所示。選擇輸出項(xiàng)也可以采用IF()函數(shù)嵌套變換輸出項(xiàng)：IF(邏輯表達(dá)式,表達(dá)式1,表達(dá)式2)功能：如果邏輯表達(dá)式為真，函數(shù)值為表達(dá)式1值，否則為表達(dá)式2值。表達(dá)式2又可以是IF()函數(shù)，以此類推。例如：商品表（commodity）中商品編號(hào)前兩位對(duì)應(yīng)類別編號(hào)，第一位也是大類編號(hào)，輸出項(xiàng)變換商品大類名稱，可以進(jìn)行下列變換：SELECT IF(LEFT(商品編號(hào),1)='1','水果',IF(LEFT(商品編號(hào),1)='2','肉禽',IF(LEFT(商品編號(hào),1)='3','海鮮水產(chǎn)','糧油蛋')))AS大類,商品編號(hào),商品名稱,價(jià)格,庫(kù)存量 FROMcommodity;說(shuō)明：顯示商品表（commodity）所有記錄，輸出項(xiàng)包括大類、商品編號(hào)、商品名稱、價(jià)格、庫(kù)存量。大類通過(guò)商品編號(hào)第一個(gè)字符變換得到。選擇輸出項(xiàng)4．消除輸出項(xiàng)的重復(fù)行查詢結(jié)果顯示“輸出項(xiàng)”可能會(huì)出現(xiàn)重復(fù)行，可以使用DISTINCT或DISTINCTROW關(guān)鍵字消除結(jié)果集中的重復(fù)行。【例8.5】查詢orders表中出現(xiàn)的所有帳戶。對(duì)emarket數(shù)據(jù)庫(kù)的訂單表（orders）只選擇帳戶名，由于同一個(gè)帳戶會(huì)多次購(gòu)物而出現(xiàn)多個(gè)訂單，消除重復(fù)行可以很方便看清當(dāng)前有哪些帳戶購(gòu)物。USEemarket;SELECT帳戶名 FROMorders; #（a）SELECTDISTINCT帳戶名FROMorders; #（b）查詢結(jié)果如圖8.5所示。

選擇輸出項(xiàng)5．聚合函數(shù)輸出項(xiàng)為表達(dá)式中用到的系統(tǒng)函數(shù)僅僅是對(duì)包含的列值進(jìn)行處理，而這里的“聚合函數(shù)”處理的是查詢得到的行和列。下表8.1列出了MySQL常用的聚合函數(shù)。函

數(shù)

名說(shuō)

明COUNT求記錄行數(shù)MAX求最大值MIN求最小值SUM求表達(dá)式列的和AVG求表達(dá)式列的平均值STD或STDDEV求表達(dá)式列中所有值的標(biāo)準(zhǔn)差VARIANCE求表達(dá)式列中所有值的方差GROUP_CONCAT產(chǎn)生由屬于一組的列值連接組合而成的字符串選擇輸出項(xiàng)【例8.6】統(tǒng)計(jì)commodity表中的商品記錄總數(shù)，商品價(jià)格的最高、最低及平均值，并計(jì)算出全部商品的總價(jià)值。USEemarket;SELECTCOUNT(商品編號(hào))AS商品總數(shù), MAX(價(jià)格)AS最高價(jià),MIN(價(jià)格)AS最低價(jià),FORMAT(AVG(價(jià)格),2)AS均價(jià),SUM(價(jià)格*庫(kù)存量)AS總價(jià)值 FROMcommodity;查詢結(jié)果如圖8.6所示。選擇輸出項(xiàng)【例8.7】計(jì)算commodity表中所有商品價(jià)格的方差和標(biāo)準(zhǔn)差。統(tǒng)計(jì)學(xué)上的標(biāo)準(zhǔn)差等于方差的平方根，所以STDDEV(…)和SQRT(VARIANCE(…))這兩個(gè)表達(dá)式是相等的。方差的計(jì)算按以下幾個(gè)步驟進(jìn)行。（1）計(jì)算相關(guān)列的平均值。（2）求列中的每一個(gè)值與平均值之差。（3）計(jì)算差值的平方的總和。（4）用總和除以（列中的）值的個(gè)數(shù)得結(jié)果。USEemarket;SELECTVARIANCE(價(jià)格)AS方差,STDDEV(價(jià)格)AS標(biāo)準(zhǔn)差,SQRT(VARIANCE(價(jià)格))AS方差的平方根FROMcommodity; #（a）SET@avg=(SELECTAVG(價(jià)格)FROMcommodity); #計(jì)算價(jià)格平均值SELECTSUM(POW(價(jià)格-@avg,2))/COUNT(商品編號(hào))AS方差FROMcommodity; #（b）查詢結(jié)果如圖8.7所示。

選擇輸出項(xiàng)MySQL支持一個(gè)特殊的聚合函數(shù)GROUP_CONCAT，它返回一個(gè)組指定列的所有非NULL值的長(zhǎng)字符串，這些值一個(gè)接著一個(gè)放置，中間用逗號(hào)隔開。這個(gè)字符串最大長(zhǎng)度標(biāo)準(zhǔn)值是1024。【例8.8】羅列出商品分類表（category）中水果大類的商品類別名稱。USEemarket;SELECTGROUP_CONCAT(類別名稱)FROMcategoryWHERELEFT(類別編號(hào),1)='1';查詢結(jié)果如圖8.8所示。選擇輸出項(xiàng)6.JSON類型列的部分內(nèi)容JSON類型列內(nèi)容是符合“鍵：值”結(jié)構(gòu)的，輸出項(xiàng)中可通過(guò)“列名->路徑”或者“列名->>路徑”及JSON函數(shù)指定部分內(nèi)容。例如：SELECT姓名,常用地址->'$."地址"."位置"'住址FROMuser;查詢結(jié)果如圖8.9所示。02單數(shù)據(jù)源1.表作為數(shù)據(jù)源2.表分區(qū)作為數(shù)據(jù)源3.查詢作為數(shù)據(jù)源4.視圖作為數(shù)據(jù)源單數(shù)據(jù)源SELECT語(yǔ)句的查詢對(duì)象（即數(shù)據(jù)源）由FROM子句指定：FROM數(shù)據(jù)源[AS別名],...數(shù)據(jù)源:=表名[分區(qū)]|視圖|查詢|連接1.表作為數(shù)據(jù)源當(dāng)表作為數(shù)據(jù)源時(shí)，如果查詢表位于當(dāng)前數(shù)據(jù)庫(kù)，直接寫表名即可；如果查詢表不在當(dāng)前數(shù)據(jù)庫(kù)，表名前需要加數(shù)據(jù)庫(kù)名前綴，或者使用“USE數(shù)據(jù)庫(kù)名”將指定的數(shù)據(jù)庫(kù)變成當(dāng)前數(shù)據(jù)庫(kù)再行查詢。查詢的數(shù)據(jù)庫(kù)可以是用戶創(chuàng)建的，也可以是MySQL系統(tǒng)的。1）查詢用戶數(shù)據(jù)庫(kù)表信息例如：USEemarket;SELECT*FROMmydb.test;SELECT*FROMcommodityWHERELEFT(商品編號(hào),1)='1';單數(shù)據(jù)源2）查詢系統(tǒng)數(shù)據(jù)庫(kù)表信息系統(tǒng)數(shù)據(jù)庫(kù)存放服務(wù)器、數(shù)據(jù)庫(kù)及其對(duì)象的有關(guān)信息，通過(guò)查詢可以獲得用戶關(guān)注的信息。例如：（1）查詢MySQL支持的字符集：SELECT*FROMinformation_schema.character_sets;（2）通過(guò)系統(tǒng)數(shù)據(jù)庫(kù)information_schema字典表columns查詢mydb數(shù)據(jù)庫(kù)mytab表列的字符集和排序規(guī)則：USEmydb;SELECTcolumn_name,character_set_name,collation_nameFROMinformation_schema.columnsWHEREtable_name='mytab';（3）通過(guò)系統(tǒng)數(shù)據(jù)庫(kù)information_schema字典表tables查詢指定表的狀態(tài)信息：SELECT*FROMinformation_schema.tablesWHEREtable_schema='mydb'andtable_name='test';單數(shù)據(jù)源（4）通過(guò)系統(tǒng)數(shù)據(jù)庫(kù)information_schema的PARTITIONS表查詢用戶數(shù)據(jù)庫(kù)表分區(qū)信息：SELECT PARTITION_NAME分區(qū)名稱, PARTITION_ORDINAL_POSITION排序, PARTITION_METHOD分區(qū)類型, PARTITION_EXPRESSION表達(dá)式, PARTITION_DESCRIPTION描述, CREATE_TIME創(chuàng)建時(shí)間, TABLE_ROWSAS記錄數(shù) FROMinformation_schema.PARTITIONS WHERETABLE_SCHEMA=SCHEMA()ANDTABLE_NAME='youth';單數(shù)據(jù)源2.表分區(qū)作為數(shù)據(jù)源不指定分區(qū)，表的所有記錄均為查詢數(shù)據(jù)源；包含分區(qū)，則僅將指定分區(qū)內(nèi)的記錄作為查詢數(shù)據(jù)源。【例8.9】查詢商品表中水果庫(kù)存量大于10000的商品。（1）對(duì)商品表（commodity）查詢。USEemarket;SELECT*FROMcommodityWHERELEFT(商品編號(hào),1)='1'AND庫(kù)存量>10000;單數(shù)據(jù)源（2）對(duì)商品分區(qū)表指定分區(qū)查詢。前面已經(jīng)對(duì)商品表（commodity）的副本商品分區(qū)表（commodity_part）進(jìn)行過(guò)分區(qū)，這里為方便對(duì)比再次列出：ALTERTABLEcommodity_part PARTITIONBYRANGECOLUMNS(商品編號(hào)) ( PARTITION水果VALUESLESSTHAN('2'), PARTITION肉禽VALUESLESSTHAN('3'), PARTITION海鮮水產(chǎn)VALUESLESSTHAN('4'), PARTITION糧油蛋VALUESLESSTHAN(MAXVALUE) );按照分區(qū)查詢，顯示結(jié)果如圖8.10所示：SELECT*FROMcommodity_partPARTITION(水果)WHERE庫(kù)存量>10000;單數(shù)據(jù)源（3）對(duì)商品分區(qū)表不指定分區(qū)查詢。SELECT*FROMcommodity_partWHERE商品編號(hào)<'2'AND庫(kù)存量>10000;因?yàn)閃HERE條件包含商品編號(hào)列，系統(tǒng)會(huì)根據(jù)條件匹配分區(qū)。這里匹配的也會(huì)是水果分區(qū)。同理，對(duì)于表按HASH和KEY分區(qū)的，用戶一般不能簡(jiǎn)單知道記錄分區(qū)存放情況，所以不能顯式指定查詢分區(qū)數(shù)據(jù)源，但系統(tǒng)會(huì)根據(jù)WHERE查詢條件是否包含分區(qū)列，指定匹配對(duì)應(yīng)的分區(qū)進(jìn)行查詢，否則分區(qū)就失去意義。所以我們應(yīng)該將最頻繁查詢的列（表達(dá)式）作為分區(qū)依據(jù)。單數(shù)據(jù)源3.查詢作為數(shù)據(jù)源查詢作為數(shù)據(jù)源：FROM(SELECT語(yǔ)句)名稱,…將FROM后的查詢結(jié)果作為數(shù)據(jù)來(lái)源，然后對(duì)其進(jìn)行查詢。【例8.10】查詢庫(kù)存量10000以上的水果。SELECT*FROMcommodityWHERELEFT(商品編號(hào),1)='1'AND庫(kù)存量>10000; #（a）SELECT*FROM(SELECT*FROMcommodityWHERELEFT(商品編號(hào),1)='1')commodity1WHERE庫(kù)存量>10000; 說(shuō)明：（a）在commodity表篩選出符合LEFT(商品編號(hào),1)='1'AND庫(kù)存量>10000條件的記錄。（b）查詢結(jié)果與（a）查詢語(yǔ)句的結(jié)果相同。這里先從commodity表篩選出符合LEFT(商品編號(hào),1)='1'條件的記錄作為數(shù)據(jù)源（commodity1），然后再在commodity1數(shù)據(jù)源查詢符合“庫(kù)存量>10000”條件的記錄。單數(shù)據(jù)源4.視圖作為數(shù)據(jù)源視圖作為數(shù)據(jù)源進(jìn)行查詢與表一樣，僅僅是視圖中的數(shù)據(jù)記錄是由定義它的查詢語(yǔ)句決定的。USEemarket;CREATEVIEWcommodity_1 ASSELECT*FROMcommodityWHERELEFT(商品編號(hào),1)='1'; #（a）SELECT*FROMcommodity_1WHERE庫(kù)存量>10000; #（b）說(shuō)明：（a）創(chuàng)建視圖，視圖中的數(shù)據(jù)是定義它的查詢結(jié)果，并以視圖名“commodity_1”標(biāo)識(shí)。（b）把commodity_1視圖作為數(shù)據(jù)源，對(duì)其進(jìn)行查詢。03多數(shù)據(jù)源1．全連接2．內(nèi)連接3．外連接4．自然連接5．交叉連接6．徑直連接多數(shù)據(jù)源1．全連接各個(gè)數(shù)據(jù)源之間用逗號(hào)分隔就指定了一個(gè)全連接，又叫“等值連接”。連接后產(chǎn)生的中間結(jié)果是一個(gè)新表，它是每個(gè)數(shù)據(jù)源的每行都與其他數(shù)據(jù)源中的每行交叉產(chǎn)生的所有可能組合，也就是笛卡兒積（每個(gè)數(shù)據(jù)源行數(shù)相乘），列包含了所有數(shù)據(jù)源中出現(xiàn)的列。例如，訂單表（orders）包含4列9行（記錄），訂單項(xiàng)表（orderitems）包含4列13行（記錄），如圖8.11所示。

多數(shù)據(jù)源那么下列語(yǔ)句：SELECT*FROMorders,orderitems;將產(chǎn)生9*13=117行（記錄），4+4=8列作為輸出項(xiàng)。全連接的多表可通過(guò)WHERE指定查詢條件，輸出項(xiàng)前需要以表名作為前綴，表明輸出的內(nèi)容出于哪個(gè)表。如果列名在各表中均不相同，輸出列名前可不加前綴。【例8.11】查找所有購(gòu)買過(guò)商品的帳戶名、訂單編號(hào)和商品編號(hào)。分析：訂單表（orders）中包含帳戶名和訂單編號(hào)，訂單項(xiàng)表（orderitems）中包含訂單編號(hào)和商品編號(hào)，所以需要把這兩個(gè)表通過(guò)訂單編號(hào)連接起來(lái)進(jìn)行查詢。USEemarket;SELECT帳戶名,orderitems.訂單編號(hào),商品編號(hào) FROMorders,orderitems WHEREorders.訂單編號(hào)=orderitems.訂單編號(hào);多數(shù)據(jù)源顯示結(jié)果如圖8.12所示。多數(shù)據(jù)源說(shuō)明：（1）這里是在117行（記錄）中間結(jié)果中查詢符合WHEREorders.訂單編號(hào)=orderitems.訂單編號(hào)條件的13條記錄，輸出項(xiàng)從8列選擇3列。（2）因?yàn)橛唵尉幪?hào)在兩個(gè)表中都存在，所以需要加表名作為前綴指定值從哪一個(gè)表得到，而帳戶名和商品編號(hào)只在一個(gè)表中存在，可以不加前綴。為了把多個(gè)表組成一個(gè)有效的整體進(jìn)行有效查詢，通常使用JOIN關(guān)鍵字指定連接類型，把它們連接起來(lái)：表名[INNER|CROSS]JOIN表名[連接條件]|表名STRAIGHT_JOIN表名|表名STRAIGHT_JOIN表名ON連接條件|表名LEFT|RIGHT[OUTER]JOIN表名連接條件|表名NATURAL[LEFT|RIGHT[OUTER]]JOIN表名多數(shù)據(jù)源2．內(nèi)連接指定了INNER關(guān)鍵字（可省略）的連接是內(nèi)連接，根據(jù)ON關(guān)鍵字后面的連接條件合并起來(lái)產(chǎn)生的中間結(jié)果，行（記錄）數(shù)是ON條件后的笛卡兒積。（1）用內(nèi)連接方法實(shí)現(xiàn)上一個(gè)例的功能。SELECT帳戶名,orderitems.訂單編號(hào),商品編號(hào) FROMordersINNERJOINorderitems ON(orders.訂單編號(hào)=orderitems.訂單編號(hào));【例8.11續(xù)】查找'2020-10-01'前下單并且沒(méi)有發(fā)貨的帳戶名、訂單編號(hào)、商品編號(hào)和下單時(shí)間。SELECT帳戶名,orderitems.訂單編號(hào),商品編號(hào),下單時(shí)間 FROMordersJOINorderitems ONorders.訂單編號(hào)=orderitems.訂單編號(hào) WHERE下單時(shí)間<'2020-10-01'AND!發(fā)貨否;顯示結(jié)果如圖8.13所示。說(shuō)明：使用內(nèi)連接后再用WHERE子句來(lái)指定'2020-10-01'前下單并且沒(méi)有發(fā)貨的篩選條件。“!發(fā)貨否”表示沒(méi)有發(fā)貨，因?yàn)榘l(fā)貨否是bit數(shù)據(jù)類型，!與NOT邏輯運(yùn)算符功能相同。多數(shù)據(jù)源（2）內(nèi)連接還可用于多個(gè)表的連接。【例8.11續(xù)】查找'2020-10-01'前下單并且沒(méi)有發(fā)貨的帳戶名、訂單編號(hào)、商品編號(hào)、商品名稱和下單時(shí)間。分析：輸出項(xiàng)帳戶名、訂單編號(hào)、商品編號(hào)、商品名稱和下單時(shí)間分布在orders、orderitems和commodity三個(gè)表中，所以需要連接這三個(gè)表。連接條件：orders和orderitems是通過(guò)訂單編號(hào)連接，而orderitems和commodity是通過(guò)商品編號(hào)建立連接。SELECT帳戶名,orderitems.訂單編號(hào),orderitems.商品編號(hào),商品名稱,下單時(shí)間 FROMorderitems JOINordersONorderitems.訂單編號(hào)=orders.訂單編號(hào) JOINcommodityONorderitems.商品編號(hào)=commodity.商品編號(hào) WHERE下單時(shí)間<'2020-10-01'AND!發(fā)貨否;顯示結(jié)果如圖8.14所示。多數(shù)據(jù)源如果要連接的表中有列名相同，并且連接的條件就是列名相等，那么ON條件也可以換成USING(列名表)子句。下列語(yǔ)句修改與上面語(yǔ)句等效。SELECTB.帳戶名,A.訂單編號(hào),A.商品編號(hào),C.商品名稱,B.下單時(shí)間 FROMorderitemsASA JOINordersASBUSING(訂單編號(hào)) JOINcommodityASCUSING(商品編號(hào)) WHEREB.下單時(shí)間<'2020-10-01'AND!A.發(fā)貨否;多數(shù)據(jù)源3．外連接指定了OUTER關(guān)鍵字（可省略）的連接為外連接，包括：左外連接（LEFTOUTERJOIN）：結(jié)果表中除了匹配行外，還包括左表有但右表中不匹配的行，對(duì)于這樣的行，從右表被選擇的列設(shè)置為NULL。右外連接（RIGHTOUTERJOIN）：結(jié)果表中除了匹配行外，還包括右表有但左表中不匹配的行，對(duì)于這樣的行，從左表被選擇的列設(shè)置為NULL。【例8.12】用外連接查找所有商品名稱對(duì)應(yīng)的訂單編號(hào)、訂貨數(shù)量，未被訂購(gòu)的商品也要列出。SELECTcommodity.商品名稱,訂單編號(hào),訂貨數(shù)量 FROMcommodityLEFTOUTERJOINorderitems ONcommodity.商品編號(hào)=orderitems.商品編號(hào);多數(shù)據(jù)源顯示結(jié)果如圖8.15所示。可以看到，未被訂購(gòu)的商品也一并列出來(lái)了，且相應(yīng)的列被置為NULL。若本例不使用LEFTOUTERJOIN，則結(jié)果中就不會(huì)包含未被訂購(gòu)的商品信息。下面的右連接的效果與上面語(yǔ)句效果相同：SELECTcommodity.商品名稱,訂單編號(hào),訂貨數(shù)量 FROMorderitemsRIGHTJOINcommodity ONorderitems.商品編號(hào)=commodity.商品編號(hào);多數(shù)據(jù)源4．自然連接自然連接用NATURAL關(guān)鍵字定義，它在語(yǔ)義上與使用了ON條件的內(nèi)連接相同，又分為自然左外連接（NATURALLEFTOUTERJOIN）和自然右外連接（NATURALRIGHTOUTERJOIN）。【例8.13】查詢所有被訂購(gòu)過(guò)的商品編號(hào)和商品名稱。SELECT商品編號(hào),商品名稱 FROMcommodity WHERE商品編號(hào)IN ( SELECTDISTINCT商品編號(hào) FROMcommodityNATURALRIGHTOUTERJOINorderitems );顯示結(jié)果如圖8.16所示。多數(shù)據(jù)源5．交叉連接指定了CROSS關(guān)鍵字的連接是交叉連接。在不包含連接條件時(shí)，交叉連接實(shí)際上就是將兩個(gè)表進(jìn)行笛卡兒積運(yùn)算，結(jié)果表是由第一個(gè)表的每行與第二個(gè)表的每一行拼接后形成的表，因此結(jié)果表的行數(shù)等于兩個(gè)表行數(shù)之積，故在MySQL中，交叉連接從語(yǔ)法上來(lái)說(shuō)與內(nèi)連接是等同的，兩者可以互換。6．徑直連接徑直連接就是JOIN語(yǔ)法中以STRAIGHT_JOIN聲明的連接，它的功能同JOIN類似，但能讓左邊的表來(lái)驅(qū)動(dòng)右邊的表，能人為強(qiáng)制改變表優(yōu)化器對(duì)于聯(lián)表查詢的執(zhí)行順序，從而在某些應(yīng)用場(chǎng)合極大地提高多表連接查詢的性能。徑直連接的用法與內(nèi)連接基本相同，不同的是，STRAIGHT_JOIN后不可以使用USING子句替代ON條件。另外，它只適用于內(nèi)連接而不能用于外連接，這是因?yàn)椋膺B接中無(wú)論是左外連接（LEFTOUTERJOIN）還是右外連接（RIGHTOUTERJOIN）都已經(jīng)指定了表的執(zhí)行順序。04查詢條件：邏輯條件1．比較運(yùn)算2．模式匹配3．范圍限定4．空值判斷查詢條件：邏輯條件WHERE子句指定查詢條件。WHERE查詢條件查詢條件是各種運(yùn)算符和表達(dá)式的組合，其最后運(yùn)算結(jié)果只能是邏輯值TRUE（真）、FALSE（假）或UNKNOWN。查詢條件可以包含下列形式：表達(dá)式<比較運(yùn)算符>表達(dá)式 /*比較運(yùn)算*/|匹配列[NOT]LIKE表達(dá)式[ESCAPE'轉(zhuǎn)義字符'] /*模式匹配*/|匹配列[NOT][REGEXP|RLIKE]正則表達(dá)式 /*模式匹配*/|表達(dá)式[NOT]BETWEEN表達(dá)式AND表達(dá)式 /*范圍限定*/|表達(dá)式[NOT]IN(值,...) /*范圍限定*/|表達(dá)式IS[NOT]NULL /*空值判斷*/|表達(dá)式[NOT]IN(SELECT語(yǔ)句) /*IN子查詢*/|表達(dá)式比較運(yùn)算符ALL|SOME|ANY(SELECT語(yǔ)句) /*比較子查詢*/|[NOT]EXISTS(SELECT語(yǔ)句) /*EXISTS子查詢*/|邏輯值查詢條件：邏輯條件1．比較運(yùn)算用比較運(yùn)算符將兩個(gè)或多個(gè)表達(dá)式連接起來(lái)，基本格式為：表達(dá)式=|<|<=|>|>=|<=>|<>|!=表達(dá)式說(shuō)明：（1）表達(dá)式是除TEXT和BLOB外類型的表達(dá)式。（2）當(dāng)兩個(gè)表達(dá)式的值均不為空（NULL）時(shí)，除了“<=>”運(yùn)算符，其他比較運(yùn)算返回邏輯值TRUE或FALSE；而當(dāng)兩個(gè)表達(dá)式值中有一個(gè)為空或都為空時(shí)，則返回UNKNOWN。【例8.14】查詢commodity表中編號(hào)大于“'2B1702'”且價(jià)格低于100元的商品信息。SELECT商品編號(hào),商品名稱,價(jià)格,庫(kù)存量 FROMcommodity WHERE商品編號(hào)>'2B1702'AND價(jià)格<100;顯示結(jié)果如圖8.17所示。查詢條件：邏輯條件【例8.15】查詢commodity表中總價(jià)值（價(jià)格×庫(kù)存量）大于100萬(wàn)元但庫(kù)存少于2000；或者總價(jià)值小于10萬(wàn)元，但庫(kù)存大于500的商品名稱、價(jià)格、庫(kù)存量及總價(jià)值信息。SELECT商品名稱,價(jià)格,庫(kù)存量,價(jià)格*庫(kù)存量AS總價(jià)值 FROMcommodity WHERE價(jià)格*庫(kù)存量>1000000AND庫(kù)存量<2000OR價(jià)格*庫(kù)存量<100000AND庫(kù)存量>500;顯示結(jié)果如圖8.18所示。查詢條件：邏輯條件說(shuō)明：1）可以將多個(gè)判定條件通過(guò)邏輯運(yùn)算符（如AND、OR、XOR或NOT等）組成更為復(fù)雜的查詢條件。2）本例WHERE條件的計(jì)算順序如下：（1）價(jià)格*庫(kù)存量→x1，價(jià)格*庫(kù)存量→x2。（2）x1>1000000→x3，庫(kù)存量<2000→x4，x2<100000→x5，庫(kù)存量>500→x6。（3）x3ANDx4→x7，x5ANDx6→x8。（4）x7ORx8→x9。因?yàn)橄人阈g(shù)運(yùn)算，然后比較運(yùn)算，再邏輯運(yùn)算；邏輯運(yùn)算AND的優(yōu)先級(jí)要高于OR。實(shí)際的部署根據(jù)中間計(jì)算結(jié)果進(jìn)行優(yōu)化。3）如果用戶需要明確指定計(jì)算順序，可以加上括號(hào)，系統(tǒng)會(huì)先計(jì)算最里層的括號(hào)。例如，本例WHERE條件與下列加上括號(hào)后的計(jì)算順序相同：((價(jià)格*庫(kù)存量>1000000)AND(庫(kù)存量<2000))OR((價(jià)格*庫(kù)存量<100000)AND(庫(kù)存量>500))查詢條件：邏輯條件2．模式匹配模式匹配包括兩種形式：使用LIKE進(jìn)行簡(jiǎn)單模式匹配和用REGEXP實(shí)現(xiàn)正則表達(dá)式匹配。1）LIKE簡(jiǎn)單模式匹配LIKE運(yùn)算符用于指出一個(gè)字符串是否與指定的字符串相匹配，其運(yùn)算對(duì)象可以是char、varchar、text、datetime等類型的數(shù)據(jù)，返回邏輯值TRUE或FALSE。匹配列[NOT]LIKE表達(dá)式[ESCAPE'轉(zhuǎn)義字符']在使用LIKE將匹配列與表達(dá)式進(jìn)行模式匹配時(shí)，常使用特殊符號(hào)_和%，它們可用來(lái)進(jìn)行模糊查詢。其中，“%”代表0個(gè)以上字符，“_”則代表單個(gè)字符。【例8.16】查詢commodity表中所有進(jìn)口商品的信息。按照commodity表采用商品編號(hào)第3位為A打頭的商品為進(jìn)口商品，執(zhí)行語(yǔ)句：SELECT商品編號(hào),商品名稱,價(jià)格,庫(kù)存量 FROMcommodity WHERE商品編號(hào)LIKE'__A%'; //2個(gè)'_'連字符顯示結(jié)果如圖8.19所示。查詢條件：邏輯條件【例8.17】查詢commodity表中所有10斤箱裝的水果類商品編號(hào)和名稱。因?yàn)樗箢惖纳唐肪幪?hào)是以1打頭的，故執(zhí)行語(yǔ)句：SELECT商品編號(hào)AS水果類編號(hào),商品名稱AS'名

稱' FROMcommodity WHERE商品編號(hào)LIKE'1%'AND商品名稱LIKE'%10斤%';顯示結(jié)果如圖8.20所示。當(dāng)要匹配的列值中本身也含有符號(hào)_和%時(shí)，就要使用轉(zhuǎn)義字符進(jìn)行特殊的轉(zhuǎn)義匹配。MySQL支持用戶以ESCAPE關(guān)鍵字自定義轉(zhuǎn)義字符，轉(zhuǎn)義字符必須為單個(gè)字符。例如，查詢commodity表中名稱包含下劃線（_）的商品信息，語(yǔ)句寫為：SELECT商品編號(hào),商品名稱,價(jià)格,庫(kù)存量 FROMcommodity WHERE商品名稱LIKE'%#_%'ESCAPE'#';查詢條件：邏輯條件2）REGEXP正則表達(dá)式匹配REGEXP運(yùn)算符通過(guò)正則表達(dá)式來(lái)執(zhí)行更復(fù)雜的字符串匹配運(yùn)算，它是MySQL對(duì)SQL標(biāo)準(zhǔn)的一種擴(kuò)展，功能極為強(qiáng)大，另外，REGEXP還有一個(gè)同義詞是RLIKE。匹配列[NOT][REGEXP|RLIKE]正則表達(dá)式不同于LIKE運(yùn)算符僅有“_”和“%”兩個(gè)匹配符，REGEXP擁有更多具有特殊含義的符號(hào)，參見(jiàn)表8.2。特殊字符含

義特殊字符含

義^匹配字符串的開始部分[abc]匹配方括號(hào)里出現(xiàn)的字符串a(chǎn)bc$匹配字符串的結(jié)束部分[a-z]匹配方括號(hào)里出現(xiàn)的a～z之間的1個(gè)字符.匹配任何一個(gè)字符（包括回車和新行）[^a-z]匹配方括號(hào)里出現(xiàn)的不在a～z之間的1個(gè)字符*匹配星號(hào)之前的0個(gè)或多個(gè)字符的任何序列|匹配符號(hào)左邊或右邊出現(xiàn)的字符串+匹配加號(hào)之前的1個(gè)或多個(gè)字符的任何序列[[..]]匹配方括號(hào)里出現(xiàn)的符號(hào)（如空格、換行、括號(hào)、句號(hào)、冒號(hào)、加號(hào)、連字符等）?匹配問(wèn)號(hào)之前0個(gè)或多個(gè)字符[[:<:]和[[:>:]]匹配一個(gè)單詞的開始和結(jié)束{n}匹配括號(hào)前的內(nèi)容出現(xiàn)n次的序列[[::]匹配方括號(hào)里出現(xiàn)的字符中的任意一個(gè)字符()匹配括號(hào)里的內(nèi)容

查詢條件：邏輯條件【例8.18】查詢名稱中含字符“*”的商品名稱。由于*本身就是正則表達(dá)式的特殊字符，需要轉(zhuǎn)義，執(zhí)行語(yǔ)句：SELECT商品名稱FROMcommodityWHERE商品名稱REGEXP'\\*+';顯示結(jié)果如圖8.21所示。【例8.19】查詢編號(hào)以1開頭、01結(jié)尾，且名稱中包含了“蘋果”或“大”字眼的商品。SELECT商品編號(hào),商品名稱 FROMcommodity WHERE商品編號(hào)REGEXP'^1.*01$'AND商品名稱REGEXP'[蘋果,大]';顯示結(jié)果如圖8.22所示。查詢條件：邏輯條件3．范圍限定1）BETWEEN…AND限定范圍當(dāng)要查詢的條件是某個(gè)值的范圍時(shí)，可以使用BETWEEN…AND運(yùn)算符進(jìn)行限定。表達(dá)式[NOT]BETWEEN表達(dá)式1AND表達(dá)式2當(dāng)不使用NOT時(shí)，若表達(dá)式的值在表達(dá)式1值與表達(dá)式2值（表達(dá)式1≤表達(dá)式2）之間，返回TRUE，否則返回FALSE；使用NOT時(shí)，返回結(jié)果剛好相反。【例8.20】查詢價(jià)格在100～1000元或庫(kù)存在10000～20000件之間的商品信息。SELECT商品編號(hào),商品名稱,價(jià)格,庫(kù)存量 FROMcommodity WHERE價(jià)格BETWEEN100AND1000OR庫(kù)存量BETWEEN10000AND20000;顯示結(jié)果如圖8.23所示。查詢條件：邏輯條件2）IN…限定范圍使用IN運(yùn)算符可以指定一個(gè)值表，其中列出所有可能的值。表達(dá)式[NOT]IN(值,...)當(dāng)不使用NOT時(shí)，若表達(dá)式的值與值表中的任一個(gè)匹配，即返回TRUE，否則返回FALSE；使用NOT時(shí)，表達(dá)式的值與值表中的沒(méi)有一個(gè)匹配，即返回TRUE，否則返回FALSE。【例8.21】查詢商品分類表（category）中蘋果、梨和橙以外的類別編號(hào)和類別名稱。SELECT類別編號(hào),類別名稱 FROMcategory WHERE類別編號(hào)LIKE'1%'AND類別編號(hào)NOTIN('1A','1B','1C');顯示結(jié)果如圖8.24所示。查詢條件：邏輯條件4．空值判斷使用ISNULL運(yùn)算符判定一個(gè)表達(dá)式的值是否為空。表達(dá)式IS[NOT]NULL當(dāng)不使用NOT時(shí)，若表達(dá)式的值為空，返回TRUE，否則返回FALSE；使用NOT時(shí)，結(jié)果剛好相反。例如，查詢commodity表中存儲(chǔ)了圖片的商品記錄：SELECT*FROMcommodityWHERE商品圖片ISNOTNULL;05查詢條件：枚舉、集合、JSON和空間條件1.枚舉類型列查詢條件2.集合類型列查詢條件3.JSON類型列查詢條件4.空間類型列查詢條件查詢條件：枚舉、集合、JSON和空間條件1.枚舉類型列查詢條件1）精確查詢枚舉類型列查詢條件可以用成員序號(hào)，也可以用字符串。【例8.22】在user表中按性別和職業(yè)查詢。user表性別列和職業(yè)列定義如下：性別 enum('男','女') NOTNULLDEFAULT'男',職業(yè) enum('學(xué)生','職工','教師','醫(yī)生','軍人','公務(wù)員','其他')按照性別和職業(yè)查詢：USEemarket;SELECT*FROMuserWHERE性別='男'AND職業(yè)='教師';SELECT*FROMuserWHERE性別=1AND職業(yè)=3;這兩條查詢語(yǔ)句查詢結(jié)果相同，如圖8.25所示。查詢條件：枚舉、集合、JSON和空間條件2）模糊查詢可以將枚舉類型的列存放的內(nèi)容認(rèn)為是枚舉字符串。【例8.22續(xù)】在user表中按性別和職業(yè)查詢。SELECT*FROMuserWHERE職業(yè)LIKE'職%';查詢結(jié)果如圖8.26所示。查詢條件：枚舉、集合、JSON和空間條件2.集合類型列查詢條件1）精確查詢集合類型列查詢條件可以采用將成員的二進(jìn)制位序號(hào)表示成十進(jìn)制，也可以采用字符串，多個(gè)成員順序必須完全相同，用逗號(hào)（,）分隔。【例8.23】在user表中按關(guān)注內(nèi)容查詢。user表關(guān)注列定義如下：關(guān)注 set('水果','肉禽','海鮮水產(chǎn)','糧油蛋')按照關(guān)注內(nèi)容查詢：SELECT帳戶名,姓名,關(guān)注FROMuserWHERE關(guān)注='水果,海鮮水產(chǎn)’; #（a）SELECT帳戶名,姓名,關(guān)注FROMuserWHERE關(guān)注='海鮮水產(chǎn)’; #（b）SELECT帳戶名,姓名,關(guān)注FROMuserWHERE關(guān)注=5; #（c）SELECT帳戶名,姓名,關(guān)注FROMuserWHERE關(guān)注=1; #（d）查詢結(jié)果如圖8.27所示。

查詢條件：枚舉、集合、JSON和空間條件2）模糊查詢可以將集合類型的列存放的內(nèi)容認(rèn)為是集合字符串，之間用逗號(hào)（,）分隔。【例8.23續(xù)】在user表中按關(guān)注內(nèi)容查詢。SELECT帳戶名,姓名,關(guān)注FROMuserWHERE關(guān)注LIKE'水果%'; #（a）SELECT帳戶名,姓名,關(guān)注FROMuserWHERE關(guān)注LIKE'%海鮮水產(chǎn)%’; #（b）SELECT帳戶名,姓名,關(guān)注FROMuserWHERE關(guān)注LIKE'%海鮮%'; #（b）SELECT帳戶名,姓名,關(guān)注FROMuserWHEREFIND_IN_SET('海鮮水產(chǎn)',關(guān)注)>0;SELECT帳戶名,姓名,關(guān)注FROMuserWHEREFIND_IN_SET('海鮮水產(chǎn)',關(guān)注);SELECT帳戶名,姓名,關(guān)注FROMuserWHEREFIND_IN_SET('海鮮',關(guān)注)>0; #（c）查詢結(jié)果如圖8.28所示。

查詢條件：枚舉、集合、JSON和空間條件3.JSON類型列查詢條件JSON類型列使用“列名->>路徑”來(lái)指定JSON的某一路徑“鍵”。也可以使用JSON函數(shù)進(jìn)行查詢。JSON函數(shù)很多，請(qǐng)參考本書第7章或者有關(guān)文檔。【例8.24】在user表中按常用地址查詢。SELECT姓名,常用地址FROMuser; #（a）SELECT姓名,性別,職業(yè),常用地址->>'$."地址"."位置"'AS城市FROMuser WHERE常用地址->>'$."地址"."市"'LIKE'南京%’; #（b）SELECT姓名,性別,職業(yè),常用地址->>'$."地址"."位置"'城市FROMuser WHEREJSON_CONTAINS(常用地址,'"南京"','$."地址"."市“’); #（b）SELECT姓名,JSON_LENGTH(常用地址)FROMuser; #（c）前兩個(gè)查詢結(jié)果相同，全部查詢結(jié)果如圖8.29所示。

查詢條件：枚舉、集合、JSON和空間條件說(shuō)明：（a）顯示常用地址列信息。（b）JSON_CONTAINS(j,j1,路徑)：判斷j1（值或JSON對(duì)象）是否在j指定路徑下。兩條記錄常用地址為南京市。這里j=常用地址，j1='"南京"'，路徑='$."地址"."市"'。注意，南京外面的單引號(hào)表示JSON對(duì)象，里面的雙引號(hào)表示字符串常量。（c）JSON_LENGTH(j)：判斷j（JSON對(duì)象）中的元素個(gè)數(shù)。第2條記錄常用地址中只有“地址”元素；而其他兩條記錄常用地址中還包含收件人和收件人電話元素。查條件：枚舉、集合、JSON和空間條件詢4.空間類型列查詢條件空間類型列數(shù)據(jù)一般不能直接使用表達(dá)查詢條件，而是需要通過(guò)空間數(shù)據(jù)處理系統(tǒng)函數(shù)進(jìn)行處理。MySQL空間數(shù)據(jù)處理系統(tǒng)函數(shù)很多，請(qǐng)參考本書第7章或者查看有關(guān)文檔。【例8.25】查詢南京仙堯投遞站范圍內(nèi)的用戶、位置及其投遞距離。SET@g1=ST_GeomFromText('POINT(118.8832.11)'); #南京仙堯投遞站位置SET@s2= 'Polygon((11832, 11833, 11933, 11932, 11832))';SET@g2=ST_GeomFromText(@s2); #南京仙堯投遞站投遞區(qū)域SELECT姓名,常用地址->>'$."地址"."位置"'AS投遞地點(diǎn),TRUNCATE(ST_Distance(投遞位置,@g1)*111195/1000,2)ASkm FROMuser WHEREST_WithIn(投遞位置,@g2); #（a）SELECT姓名,投遞位置 FROMuser WHERE投遞位置=ST_GeomFromText('POINT(118.87909632.125901)’);#（b）查條件：枚舉、集合、JSON和空間條件詢運(yùn)行查詢結(jié)果如圖8.30所示。

說(shuō)明：（1）MySQL8.0內(nèi)置的ST_Distance()函數(shù)計(jì)算的結(jié)果單位是度，需要乘111195（地球半徑6371000*PI/180）將值轉(zhuǎn)化為米，再除以1000換算為km。（2）ST_WithIn(投遞位置,@g2)：投遞位置在投遞區(qū)域（@g2），函數(shù)返回1，即條件為真。黑龍江的用戶就不在投遞區(qū)域，所以沒(méi)有顯示。這里采用ST_Contains(@g2,投遞位置)也可以達(dá)到同樣效果。06查詢條件：子查詢條件1.子查詢類型及其功能2.子查詢返回結(jié)果查詢條件：子查詢條件1.子查詢類型及其功能1）IN子查詢IN子查詢使用IN運(yùn)算符對(duì)一個(gè)給定值是否在子查詢結(jié)果表中進(jìn)行判斷：表達(dá)式[NOT]IN(SELECT語(yǔ)句)當(dāng)表達(dá)式與子查詢SELECT語(yǔ)句結(jié)果表中的某個(gè)值相等時(shí)，返回TRUE，否則返回FALSE。若使用NOT，返回值剛好相反。【例8.26】查詢編號(hào)為7的訂單中的商品名稱及編號(hào)。SELECT商品名稱,商品編號(hào) #（b） FROMcommodity WHERE商品編號(hào)IN (

SELECT

商品編號(hào) #（a） FROMorderitems WHERE訂單編號(hào)=7 );查詢條件：子查詢條件查詢結(jié)果如圖8.31所示。說(shuō)明：（a）系統(tǒng)先執(zhí)行對(duì)orderitems表的子查詢，產(chǎn)生一個(gè)只含“商品編號(hào)”列的結(jié)果表。訂單編號(hào)為7的訂單項(xiàng)有3條記錄。（b）執(zhí)行外查詢，若commodity表中某行的“商品編號(hào)”列值等于子查詢結(jié)果表中的3個(gè)中任一個(gè)值，WHERE條件成立，該行“商品名稱,商品編號(hào)”列被輸出。查詢條件：子查詢條件【例8.27】查詢帳戶名為的用戶所訂購(gòu)商品的名稱和編號(hào)。SELECT商品名稱,商品編號(hào) #（c） FROMcommodity WHERE商品編號(hào)IN ( SELECT商品編號(hào) #（b） FROMorderitems WHERE訂單編號(hào)IN ( SELECT訂單編號(hào) #（a） FROMorders WHERE帳戶名='' ) );查詢條件：子查詢條件查詢結(jié)果如圖8.32所示。說(shuō)明：（a）查詢orders表中帳戶名=''的用戶的所有訂單編號(hào)（x）。（b）查詢orderitems表訂單編號(hào)IN(x)的記錄的商品編號(hào)（y）。（c）查詢commodity表商品編號(hào)IN(y)的記錄的商品名稱和商品編號(hào)。查詢條件：子查詢條件2）比較子查詢比較子查詢可以認(rèn)為是IN子查詢的擴(kuò)展：表達(dá)式比較運(yùn)算符ALL|SOME|ANY(SELECT語(yǔ)句)它將表達(dá)式的值與（SELECT語(yǔ)句）子查詢的結(jié)果按照ALL、SOME和ANY限制進(jìn)行比較運(yùn)算。ALL指定表達(dá)式要與子查詢結(jié)果表中的每個(gè)值都進(jìn)行比較，只有在表達(dá)式與每個(gè)值都滿足比較關(guān)系時(shí)，才返回TRUE，否則返回FALSE。SOME和ANY是同義詞，表示表達(dá)式只要與子查詢結(jié)果表中的某個(gè)值滿足比較關(guān)系就返回TRUE，否則返回FALSE。如果子查詢的結(jié)果表只返回一行數(shù)據(jù)，就通過(guò)比較運(yùn)算符直接比較。查詢條件：子查詢條件【例8.28】查詢比編號(hào)為7的訂單中的商品價(jià)格都高的商品編號(hào)、名稱和價(jià)格。SELECT商品編號(hào),商品名稱,價(jià)格 #（c） FROMcommodity WHERE價(jià)格>ALL ( SELECT價(jià)格 #（b） FROMcommodity WHERE商品編號(hào)IN ( SELECT商品編號(hào) #（a） FROMorderitems WHERE訂單編號(hào)=7 ) );查詢條件：子查詢條件查詢結(jié)果如圖8.33所示。說(shuō)明：（a）查詢orderitems表符合“訂單編號(hào)=7”條件的記錄有3條，獲得商品編號(hào)（x1,x2,x3）。（b）在commodity表中找出商品編號(hào)(x1,x2,x3)對(duì)應(yīng)的價(jià)格(y1,y2,y3)=(69.80,118.00,99.00)，max(y1,y2,y3)=118.00。（c）在commodity表中找出價(jià)格大于max（y1,y2,y3）的所有商品編號(hào)、商品名稱和價(jià)格。查詢條件：子查詢條件【例8.29】查詢編號(hào)3訂單中價(jià)格不低于編號(hào)7訂單中最低價(jià)商品的商品編號(hào)、名稱和價(jià)格。SELECT商品編號(hào),商品名稱,價(jià)格 FROMcommodity WHERE價(jià)格>SOME #（a） ( SELECT價(jià)格 FROMcommodity WHERE商品編號(hào)IN ( SELECT商品編號(hào) FROMorderitems WHERE訂單編號(hào)=7 ) )

AND

商品編號(hào)IN #（b） ( SELECT商品編號(hào) FROMorderitems WHERE訂單編號(hào)=3 );查詢條件：子查詢條件查詢結(jié)果如圖8.34所示。說(shuō)明：（a）這里就是上例將“價(jià)格>ALL”改成“價(jià)格>SOME”，因?yàn)閙in(y1,y2,y3)=69.80，那么，如果沒(méi)有（b）條件子查詢，就會(huì)輸出commodity表中符合“價(jià)格>69.80”條件的6條記錄（x1,x2，…x6）的商品編號(hào)、商品名稱和價(jià)格。（b）AND商品編號(hào)IN：在orderitems表中符合“訂單編號(hào)=3”條件有2條記錄（商品編號(hào)=1GA101，4C2402）。在上面的6條記錄中只有（商品編號(hào)=4C2402）出現(xiàn)在其中。查詢條件：子查詢條件3）EXISTS子查詢EXISTS謂詞用于測(cè)試子查詢的結(jié)果是否為空表。[NOT]EXISTS(SELECT語(yǔ)句)若子查詢的結(jié)果表不為空，EXISTS(…)返回TRUE，否則返回FALSE。若與NOT結(jié)合使用，即NOTEXISTS(…)，其返回值剛好相反。【例8.30】查詢訂單編號(hào)7訂單中的商品名稱。SELECT商品名稱 FROMcommodity WHEREEXISTS ( SELECT*FROMorderitems WHERE商品編號(hào)=commodity.商品編號(hào)AND訂單編號(hào)=7 );查詢結(jié)果如圖8.35所示。查詢條件：子查詢條件【例8.31】查詢訂單編號(hào)1中包含而訂單編號(hào)7卻沒(méi)有的商品名稱。SELECT商品名稱 FROMcommodity WHEREEXISTS ( SELECT*FROMorderitems WHERE商品編號(hào)=commodity.商品編號(hào)AND訂單編號(hào)=1 ANDNOTEXISTS ( SELECT*FROMorderitems WHERE商品編號(hào)=commodity.商品編號(hào)AND訂單編號(hào)=7 ) );查詢結(jié)果如圖8.36所示。查詢條件：子查詢條件2.子查詢返回結(jié)果1）行子查詢顧名思義，行子查詢就是返回帶有多個(gè)值的一行或者多行的子查詢。在WHERE子句中用于將指定的行數(shù)據(jù)與子查詢中的結(jié)果行數(shù)據(jù)通過(guò)比較運(yùn)算符進(jìn)行比較。【例8.32】查找與指定商品編號(hào)（例如：1A0201）的商品類別相同，且價(jià)格也相同的商品編號(hào)、名稱和價(jià)格。SET@s='1A0201';SELECT商品編號(hào),商品名稱,價(jià)格 FROMcommodity WHERE(LEFT(商品編號(hào),2),價(jià)格)= ( SELECTLEFT(商品編號(hào),2),價(jià)格 FROMcommodity WHERE商品編號(hào)=@s ) AND商品編號(hào)!=@s;查詢條件：子查詢條件查詢結(jié)果如圖8.37所示。說(shuō)明：（a）“WHERE(LEFT(商品編號(hào),2),價(jià)格)=(SELECTLEFT(商品編號(hào),2),價(jià)格…)”相當(dāng)于WHERE(x1,x2)=(y1,y2)的條件，功能等同“x1=y1ANDx2=y2”條件。（b）在commodity表中，商品編號(hào)='1A0201'中前2位類別編號(hào)'1A'相同，價(jià)格也相同的商品記錄有2條（包含它自己），而“AND商品編號(hào)!=@s”條件又把自己排除，所以只有一條記錄。（c）“商品編號(hào)”左起的頭兩位為類別編號(hào)，字符串函數(shù)LEFT從商品編號(hào)中截取類別編號(hào)，類別編號(hào)=LEFT(商品編號(hào),2)='1A'。查詢條件：子查詢條件2）表子查詢表子查詢返回的是一個(gè)表，該表可以用在FROM子句中，作為產(chǎn)生的中間表需要定義一個(gè)別名。【例8.33】查找商品庫(kù)存低于1000的肉禽類別的商品名稱、編號(hào)和庫(kù)存量。SELECT商品名稱,商品編號(hào),庫(kù)存量 #（b） FROM( SELECT*FROMcommodity #（a） WHERELEFT(商品編號(hào),1)='2' )ASmeat WHERE庫(kù)存量<1000;查詢結(jié)果如圖8.38所示。查詢條件：子查詢條件說(shuō)明：（a）首先處理FROM子句中的子查詢，查出所有的肉禽大類別商品記錄（商品編號(hào)第1位='2'為肉禽大類別），將其存放到一個(gè)中間表中，并為表定義一個(gè)別名meat，得到的中間表如圖8.39所示。（b）對(duì)meat中間表再根據(jù)查詢條件（庫(kù)存量<1000）從中找出庫(kù)存低于1000的記錄。查詢條件：子查詢條件3）標(biāo)量子查詢標(biāo)量子查詢就是只返回一個(gè)值的子查詢。它甚至可以直接定義在SELECT關(guān)鍵字后面，作為一個(gè)值來(lái)使用。【例8.34】求每一個(gè)商品的價(jià)格與所有商品均價(jià)之間的差價(jià)。SELECT商品編號(hào),商品名稱, #（b） FORMAT( #（c）

價(jià)格-( SELECTAVG(價(jià)格)FROMcommodity #（a） ),2)AS差價(jià) FROMcommodity;查詢結(jié)果如圖8.40所示。查詢條件：子查詢條件說(shuō)明：（a）SELECTAVG(價(jià)格)FROMcommodity：獲得所有商品的平均價(jià)格（c），該子查詢完成后得到的就是一個(gè)值。（b）相當(dāng)于SELECT商品編號(hào),商品名稱,價(jià)格-cFROMcommodity，就是一個(gè)簡(jiǎn)單查詢，其中“價(jià)格-c”項(xiàng)是算術(shù)表達(dá)式。（c）按實(shí)際應(yīng)用意義，格式化取兩位小數(shù)。07分組1．基本分組2．多表連接分組3．分組匯總分組GROUPBY子句主要用于對(duì)查詢結(jié)果按行分組：GROUPBY列名|表達(dá)式,...[WITHROLLUP]說(shuō)明：（1）列名或表達(dá)式就是分組依據(jù)，可以是一個(gè)或多個(gè)，列名或表達(dá)式相同的為同一組，作為統(tǒng)計(jì)匯總的依據(jù)。（2）WITHROLLUP指定在結(jié)果集內(nèi)組后還包含匯總行。（3）在系統(tǒng)默認(rèn)狀態(tài)下，SQL_MODE設(shè)置包含sql_mode=only_full_group_by，包含“GROUPBYx”的SELECT輸出項(xiàng)，除了x項(xiàng)，其他只能是采用聚合函數(shù)的項(xiàng)。否則需要在SQL_MODE設(shè)置中不能包含sql_mode=only_full_group_by。分組1．基本分組【例8.35】對(duì)訂單表（orders）中記錄根據(jù)帳戶名進(jìn)行分組，并統(tǒng)計(jì)每個(gè)帳戶的支付金額總額。SELECT帳戶名,SUM(支付金額)FROMorders GROUPBY帳戶名; #（a）SELECT帳戶名,SUM(支付金額)FROMorders GROUPBY帳戶名WITHROLLUP; #（b）查詢結(jié)果如圖8.41所示。

說(shuō)明：SUM(支付金額)就是將同一組的成員支付金額累加起來(lái)，加WITHROLLUP項(xiàng)，最后還包含所有帳戶的支付金額總計(jì)。分組【例8.36】求每個(gè)訂單中所含訂單項(xiàng)數(shù)和總商品個(gè)數(shù)。SELECT訂單編號(hào),COUNT(商品編號(hào))AS訂單項(xiàng)數(shù),SUM(訂貨數(shù)量)AS商品個(gè)數(shù) FROMorderitems

GROUPBY

訂單編號(hào);查詢結(jié)果如圖8.42所示。說(shuō)明：訂單項(xiàng)數(shù)需要統(tǒng)計(jì)COUNT(商品編號(hào))，因?yàn)橥粋€(gè)訂單編號(hào)不同的訂單項(xiàng)，商品編號(hào)是不同的。分組2．多表連接分組多表連接與GROUPBY分組功能相結(jié)合，可實(shí)現(xiàn)多表聯(lián)合分類統(tǒng)計(jì)匯總，十分實(shí)用。【例8.37】列出每個(gè)供貨商所提供的商品，并統(tǒng)計(jì)各供貨商提供商品的種數(shù)及庫(kù)存總量。SELECT供貨商編號(hào),供貨商名稱, GROUP_CONCAT(商品編號(hào))AS商品編號(hào),COUNT(商品編號(hào))AS種數(shù),SUM(庫(kù)存量)AS庫(kù)存總量 FROMcommodityRIGHTJOINsupplier ONSUBSTRING(commodity.商品編號(hào),3,2)=supplier.供貨商編號(hào) GROUPBY供貨商編號(hào);查詢結(jié)果如圖8.43所示。分組3．分組匯總GROUPBY后的列或者表達(dá)式包含一個(gè)以上，此時(shí)的分組就出現(xiàn)了多個(gè)層次。【例8.38】按商品大類別和商品類別計(jì)算它們的均價(jià)和庫(kù)存總量。SELECT大類號(hào),類別名稱,FORMAT(AVG(價(jià)格),2)AS均價(jià),SUM(庫(kù)存量)AS總量 FROM ( SELECTLEFT(類別編號(hào),1)AS大類號(hào),類別名稱,價(jià)格,庫(kù)存量 FROMcategoryRIGHTJOINcommodity ONcategory.類別編號(hào)=LEFT(commodity.商品編號(hào),2) )AScategory_info GROUPBY大類號(hào),類別名稱WITHROLLUP;分組查詢結(jié)果如圖8.44所示。說(shuō)明：（1）先將category表與commodity表進(jìn)行右連接，這樣連接后的中間表才能不但包含類別，還包含所有商品的價(jià)格和庫(kù)存量信息。（2）因?yàn)橐凑丈唐反箢悇e和商品類別分組，所以需要在category表取LEFT(類別編號(hào),1)得到商品大類別號(hào)。如果想將大類別號(hào)變成大類別名稱，可以參考本章前面“選擇輸出項(xiàng)”使用IF(…)或者CASE(…)函數(shù)。大類1對(duì)應(yīng)水果、2對(duì)應(yīng)肉禽、3對(duì)應(yīng)海鮮水產(chǎn)、4對(duì)應(yīng)糧油蛋。（3）GROUPBY大類號(hào),類別名稱WITHROLLUP：先按中間表的大類號(hào)分組，大類號(hào)相同再按類別名稱分組。08分組后篩選分組后篩選HAVING子句的目的與WHERE子句一樣均為定義篩選條件，不同的是WHERE子句是用來(lái)在FROM子句之后選擇行，而HAVING子句則是用來(lái)在GROUPBY子句后選擇行。不過(guò)HAVING子句中的條件可以包含聚合函數(shù)，而WHERE子句則不可以。SELECT ... GROUPBY... HAVING條件【例8.39】查找訂貨總量大于等于8的商品的編號(hào)和訂貨量。SELECT商品編號(hào),SUM(訂貨數(shù)量)AS'訂貨總量' FROMorderitems GROUPBY商品編號(hào); #（a）SELECT商品編號(hào),SUM(訂貨數(shù)量)AS'訂貨總量' FROMorderitems GROUPBY商品編號(hào) HAVINGSUM(訂貨數(shù)量)>=8; #（b）分組后篩選查詢結(jié)果如圖8.45所示。

說(shuō)明：（a）匯總出每種商品的訂貨量。（b）篩選出訂貨總量在8以上的商品訂貨量。分組后篩選【例8.40】查找僅被訂購(gòu)過(guò)1次但訂購(gòu)量在5以上的商品編號(hào)及訂貨數(shù)量。SET@@SQL_MODE='';SELECT商品編號(hào),訂貨數(shù)量 FROMorderitems GROUPBY商品編號(hào) HAVINGCOUNT(*)=1AND訂貨數(shù)量>5; #（a）SELECT商品編號(hào),訂貨數(shù)量 FROMorderitems

WHERE訂貨數(shù)量>5 GROUPBY商品編號(hào) HAVINGCOUNT(*)=1; #（b）查詢結(jié)果如圖8.46所示。

分組后篩選說(shuō)明：（a）SQL標(biāo)準(zhǔn)要求HAVING必須引用GROUPBY子句中的列或用于聚合函數(shù)中的列。不過(guò)，MySQL對(duì)其進(jìn)行了擴(kuò)展，允許HAVING引用SELECT清單中的列，比如，本例就引用了SELECT清單中的“訂貨數(shù)量”列。但如果不對(duì)SQL_MODE進(jìn)行設(shè)置，默認(rèn)狀態(tài)下包含sql_mode=only_full_group_by項(xiàng)，要求GROUPBY遵循SQL標(biāo)準(zhǔn)，所以需要設(shè)置取消其默認(rèn)值。（b）要根據(jù)查詢的具體要求來(lái)分析確定條件處于WHERE子句還是HAVING子句，又或者是進(jìn)行分工配合。它先按照WHERE條件將orderitems表中訂購(gòu)量大于5的記錄找出來(lái)，再按商品編號(hào)分組并對(duì)每組計(jì)數(shù)選出記錄數(shù)等于1的組的商品編號(hào)和訂貨數(shù)量，查到的將是至少有1次（而非僅被訂購(gòu)過(guò)1次）訂購(gòu)量在5以上的商品，如圖8.46（b）所示。分組后篩選【例8.41】查