MySQL教程（新體系-綜合應(yīng)用實例視頻）（第4版） 課件 第8-10章 查詢、視圖和索引；過程式對象程序設(shè)計；用戶與權(quán)限

第8章

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

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

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

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

數(shù)

名說

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

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

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

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

義特殊字符含

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

查詢條件：邏輯條件【例8.18】查詢名稱中含字符“*”的商品名稱。由于*本身就是正則表達式的特殊字符，需要轉(zhuǎn)義，執(zhí)行語句：SELECT商品名稱FROMcommodityWHERE商品名稱REGEXP'\\*+';顯示結(jié)果如圖8.21所示?！纠?.19】查詢編號以1開頭、01結(jié)尾，且名稱中包含了“蘋果”或“大”字眼的商品。SELECT商品編號,商品名稱 FROMcommodity WHERE商品編號REGEXP'^1.*01$'AND商品名稱REGEXP'[蘋果,大]';顯示結(jié)果如圖8.22所示。查詢條件：邏輯條件3．范圍限定1）BETWEEN…AND限定范圍當(dāng)要查詢的條件是某個值的范圍時，可以使用BETWEEN…AND運算符進行限定。表達式[NOT]BETWEEN表達式1AND表達式2當(dāng)不使用NOT時，若表達式的值在表達式1值與表達式2值（表達式1≤表達式2）之間，返回TRUE，否則返回FALSE；使用NOT時，返回結(jié)果剛好相反?！纠?.20】查詢價格在100～1000元或庫存在10000～20000件之間的商品信息。SELECT商品編號,商品名稱,價格,庫存量 FROMcommodity WHERE價格BETWEEN100AND1000OR庫存量BETWEEN10000AND20000;顯示結(jié)果如圖8.23所示。查詢條件：邏輯條件2）IN…限定范圍使用IN運算符可以指定一個值表，其中列出所有可能的值。表達式[NOT]IN(值,...)當(dāng)不使用NOT時，若表達式的值與值表中的任一個匹配，即返回TRUE，否則返回FALSE；使用NOT時，表達式的值與值表中的沒有一個匹配，即返回TRUE，否則返回FALSE。【例8.21】查詢商品分類表（category）中蘋果、梨和橙以外的類別編號和類別名稱。SELECT類別編號,類別名稱 FROMcategory WHERE類別編號LIKE'1%'AND類別編號NOTIN('1A','1B','1C');顯示結(jié)果如圖8.24所示。查詢條件：邏輯條件4．空值判斷使用ISNULL運算符判定一個表達式的值是否為空。表達式IS[NOT]NULL當(dāng)不使用NOT時，若表達式的值為空，返回TRUE，否則返回FALSE；使用NOT時，結(jié)果剛好相反。例如，查詢commodity表中存儲了圖片的商品記錄：SELECT*FROMcommodityWHERE商品圖片ISNOTNULL;05查詢條件：枚舉、集合、JSON和空間條件1.枚舉類型列查詢條件2.集合類型列查詢條件3.JSON類型列查詢條件4.空間類型列查詢條件查詢條件：枚舉、集合、JSON和空間條件1.枚舉類型列查詢條件1）精確查詢枚舉類型列查詢條件可以用成員序號，也可以用字符串?！纠?.22】在user表中按性別和職業(yè)查詢。user表性別列和職業(yè)列定義如下：性別 enum('男','女') NOTNULLDEFAULT'男',職業(yè) enum('學(xué)生','職工','教師','醫(yī)生','軍人','公務(wù)員','其他')按照性別和職業(yè)查詢：USEemarket;SELECT*FROMuserWHERE性別='男'AND職業(yè)='教師';SELECT*FROMuserWHERE性別=1AND職業(yè)=3;這兩條查詢語句查詢結(jié)果相同，如圖8.25所示。查詢條件：枚舉、集合、JSON和空間條件2）模糊查詢可以將枚舉類型的列存放的內(nèi)容認為是枚舉字符串。【例8.22續(xù)】在user表中按性別和職業(yè)查詢。SELECT*FROMuserWHERE職業(yè)LIKE'職%';查詢結(jié)果如圖8.26所示。查詢條件：枚舉、集合、JSON和空間條件2.集合類型列查詢條件1）精確查詢集合類型列查詢條件可以采用將成員的二進制位序號表示成十進制，也可以采用字符串，多個成員順序必須完全相同，用逗號（,）分隔?！纠?.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)容認為是集合字符串，之間用逗號（,）分隔。【例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類型列使用“列名->>路徑”來指定JSON的某一路徑“鍵”。也可以使用JSON函數(shù)進行查詢。JSON函數(shù)很多，請參考本書第7章或者有關(guān)文檔?！纠?.24】在user表中按常用地址查詢。SELECT姓名,常用地址FROMuser; #（a）SELECT姓名,性別,職業(yè),常用地址->>'$."地址"."位置"'AS城市FROMuser WHERE常用地址->>'$."地址"."市"'LIKE'南京%’; #（b）SELECT姓名,性別,職業(yè),常用地址->>'$."地址"."位置"'城市FROMuser WHEREJSON_CONTAINS(常用地址,'"南京"','$."地址"."市“’); #（b）SELECT姓名,JSON_LENGTH(常用地址)FROMuser; #（c）前兩個查詢結(jié)果相同，全部查詢結(jié)果如圖8.29所示。

查詢條件：枚舉、集合、JSON和空間條件說明：（a）顯示常用地址列信息。（b）JSON_CONTAINS(j,j1,路徑)：判斷j1（值或JSON對象）是否在j指定路徑下。兩條記錄常用地址為南京市。這里j=常用地址，j1='"南京"'，路徑='$."地址"."市"'。注意，南京外面的單引號表示JSON對象，里面的雙引號表示字符串常量。（c）JSON_LENGTH(j)：判斷j（JSON對象）中的元素個數(shù)。第2條記錄常用地址中只有“地址”元素；而其他兩條記錄常用地址中還包含收件人和收件人電話元素。查條件：枚舉、集合、JSON和空間條件詢4.空間類型列查詢條件空間類型列數(shù)據(jù)一般不能直接使用表達查詢條件，而是需要通過空間數(shù)據(jù)處理系統(tǒng)函數(shù)進行處理。MySQL空間數(shù)據(jù)處理系統(tǒng)函數(shù)很多，請參考本書第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投遞地點,TRUNCATE(ST_Distance(投遞位置,@g1)*111195/1000,2)ASkm FROMuser WHEREST_WithIn(投遞位置,@g2); #（a）SELECT姓名,投遞位置 FROMuser WHERE投遞位置=ST_GeomFromText('POINT(118.87909632.125901)’);#（b）查條件：枚舉、集合、JSON和空間條件詢運行查詢結(jié)果如圖8.30所示。

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

SELECT

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

AND

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

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

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

GROUPBY

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

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

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

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