項目五-2統(tǒng)計查詢?nèi)蝿?wù)目標(biāo)任務(wù)一應(yīng)用聚合函數(shù)任務(wù)二應(yīng)用GOUOUPBY任務(wù)三應(yīng)用HAVING條件子句任務(wù)一應(yīng)用聚合函數(shù)應(yīng)用聚合函數(shù)

主要內(nèi)容:記錄個數(shù)統(tǒng)計函數(shù)COUNT()累加和函數(shù)SUM()平均值函數(shù)AVG()最大值函數(shù)MAX()最小值函數(shù)MIN()重點難點:各個函數(shù)的應(yīng)用規(guī)則基本查詢語法查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/select.html語法：SELECT[ALL|DISTINCT]select_expr[,select_expr...][FROMtable_references[WHEREwhere_condition][GROUPBY{col_name|expr|position},...[WITHROLLUP]][HAVINGwhere_condition][ORDERBY{col_name|expr|position}[ASC|DESC][LIMIT{[offset,]row_count|row_countOFFSEToffset}]應(yīng)用聚合函數(shù)基本語法：SELECTfunc(*|col_num) FROMtable_references

[WHEREwhere_condition]COUNT()語法：COUNT（[ALL|DISTINCT]表達(dá)式|*）作用：統(tǒng)計所選字段的記錄個數(shù)。其中：ALL：對所選字段的所有非NULL值統(tǒng)計，為默認(rèn)值。DISTINCT：對重復(fù)的記錄僅計算一次，返回所有唯一的非NULL值的記錄個數(shù)。*：返回所有組中的項數(shù)，包括NULL值和重復(fù)值在內(nèi)。應(yīng)用聚合函數(shù)例1：

查詢顧客表的人員數(shù)量。mysql>USEdb_shop;mysql>SELECT*FROMcustomer;mysql>SELECTCOUNT(ALLemail)FROMcustomer;mysql>SELECTCOUNT(email)FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer;

mysql>SELECTCOUNT(*)FROMcustomer WHEREsex='F';/*觀察上面的統(tǒng)計結(jié)果*/應(yīng)用聚合函數(shù)例2：

查詢訂單表處理的人員數(shù)量。mysql>SELECT*FROMorders;mysql>SELECTCOUNT(ALLcustomer_id)FROMorders;mysql>SELECTCOUNT(customer_id)FROMorders;mysql>SELECTCOUNT(DISTINCTcustomer_id)FROMorders;/*不寫DISTINCT時，默認(rèn)是ALL*/應(yīng)用聚合函數(shù)SUM()語法：SUM（[ALL|DISTINCT]表達(dá)式）作用：統(tǒng)計數(shù)值型字段的總和，忽略NULL值。其中：ALL：對所選字段的所有值求和，為默認(rèn)值。DISTINCT：對重復(fù)的記錄僅計算一次。

例：mysql>USEdb_shop;mysql>SELECTSUM(quantity)FROMitem;mysql>SELECTSUM(quantity)FROMitem;WHERERgoods_id=‘1’應(yīng)用聚合函數(shù)AVG()語法：AVG（[ALL|DISTINCT]表達(dá)式）作用：統(tǒng)計數(shù)值型字段的平均值，忽略NULL值。其中：ALL：對所選字段的所有值求平均值，為默認(rèn)值。DISTINCT：對重復(fù)的記錄僅計算一次。例：mysql>USEdb_shop;mysql>SELECTAVG(quantity)FROMitem;mysql>SELECTAVG(quantity)FROMitem;WHERERgoods_id=‘1’應(yīng)用聚合函數(shù)MAX()MAX（[ALL|DISTINCT]表達(dá)式）作用：統(tǒng)計數(shù)值型、日期時間型字段的最大值，忽略NULL值。其中：ALL：對所選字段的所有值求最大值，為默認(rèn)值。DISTINCT：對重復(fù)的記錄僅計算一次。例：mysql>USEdb_shop;mysql>SELECTMAX(quantity)FROMitem;mysql>SELECTMAX(quantity)FROMitem;

WHERERgoods_id=‘1’應(yīng)用聚合函數(shù)MIN()語法：MIN（[ALL|DISTINCT]表達(dá)式）作用：統(tǒng)計數(shù)值型、日期時間型字段的最小值，忽略NULL值。其中：ALL：對所選字段的所有值求最小值，為默認(rèn)值。DISTINCT：對重復(fù)的記錄僅計算一次。例：mysql>USEdb_shop;mysql>SELECTMIN(quantity)FROMitem;mysql>SELECTMIN(quantity)FROMitem;

WHERERgoods_id=‘1’考考你統(tǒng)計所有員工人數(shù)；統(tǒng)計'001'部門的員工人數(shù)；查詢最貴商品的價格；查詢最便宜商品的價格；統(tǒng)計飲用水商品的均價；統(tǒng)計“1”號商品的銷售總數(shù)量；任務(wù)二應(yīng)用GOUOUPBY應(yīng)用GOUOUPBY

主要內(nèi)容:GOUOUPBY子句與聚合函數(shù)結(jié)合應(yīng)用；重點難點:GOUOUPBY的分組表達(dá)式對SELECT字段的影響應(yīng)用GOUOUPBY語法：GROUPBY分組表達(dá)式[,…n]功能：使用GROUPBY子句時，結(jié)果集被分為若干個組，可以對結(jié)果集內(nèi)的記錄進(jìn)行分組統(tǒng)計，每個組在結(jié)果集內(nèi)有一條記錄，有幾個組就有幾條記錄。應(yīng)用GOUOUPBY基本查詢分組例1：按性別分類查詢顧客表的人員數(shù)量。mysql>USEdb_shop;mysql>SELECT*FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer WHEREsex='F';mysql>SELECTsex,COUNT(*)FROMcustomerGROUPBYsex;應(yīng)用GOUOUPBY例2：按部門查詢職員表的人員數(shù)量。mysql>USEdb_shop;mysql>SELECT*FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer;mysql>SELECTdept_id,COUNT(*)FROMstaffer

GROUPBYdept_id;應(yīng)用GOUOUPBY用WHERE過濾的記錄進(jìn)行分組統(tǒng)計例1：查詢'1'號供應(yīng)商的各商品銷售數(shù)量；mysql>USEdb_shop；mysql>SELECTgoods_id,SUM(quantity)FROMitemWHEREsupplier_id=1GROUPBYgoods_id;應(yīng)用GOUOUPBY對分組后的結(jié)果進(jìn)行ORDERBY排序例1：查詢各商品銷售數(shù)量,并按降序排序；mysql>USEdb_shop；mysql>SELECTgoods_id,SUM(quantity)FROMitem

GROUPBYgoods_id

ORDERBYSUM(quantity)DESC;應(yīng)用GOUOUPBY多個分組表達(dá)式例1：按商品和供應(yīng)商，查詢各商品的各供應(yīng)商提供產(chǎn)品的銷售數(shù)量；mysql>USEdb_shop；mysql>SELECTgoods_id,supplier_id,SUM(quantity)FROMitemGROUPBYgoods_id,supplier_id;例2：按商品和供應(yīng)商分組，查詢各商品的各供應(yīng)商提供產(chǎn)品的銷售數(shù)量和總數(shù)量；mysql>USEdb_shop；mysql>SELECTgoods_id,supplier_id,SUM(quantity)FROMitemGROUPBYgoods_id,supplier_idWITHROLLUP;考考你按部門分組統(tǒng)計所有員工人數(shù)；統(tǒng)計'001'部門的男女員工人數(shù)；統(tǒng)計“1”號商品的銷售總數(shù)量；統(tǒng)計各部門男女人數(shù)和總?cè)藬?shù)；任務(wù)三應(yīng)用HAVING條件子句應(yīng)用HAVING條件子句

主要內(nèi)容:HAVING子句的應(yīng)用規(guī)則；HAVING子句的使用場合重點難點:HAVING與WHERE的應(yīng)用區(qū)別應(yīng)用HAVING條件子句語法：GROUPBY分組表達(dá)式[,…n] HAVING條件表達(dá)式；例1:mysql>USEdb_shop；mysql>SELECTdept_id,COUNT(*)FROMstaffer GROUPBYdept_id HAVINGCOUNT(*)<1;應(yīng)用HAVING條件子句例2:mysql>USEdb_shop；mysql>SELECTgoods_id,SUM(quantity)FROMitemWHEREsupplier_id=1 HAVINGSUM(quantity)>20GROUPBYgoods_id;例3:mysql>USEdb_shop；mysql>SELECTgoods_id,SUM(quantity)FROMitem GROUPBYgoods_id HAVINGSUM(quantity)>20 ORDERBYSUM(quantity)DESC;考考你按部門分組統(tǒng)計所有員工人數(shù)，顯示超過3人的部門人數(shù)；統(tǒng)計各商品的銷售總數(shù)量，顯示銷售超過10件的商品數(shù)量；任務(wù)實施按下列操作完成db_shopping數(shù)據(jù)庫表記錄的基本統(tǒng)計查詢。1.選擇db_shopping數(shù)據(jù)庫，執(zhí)行語句如下：mysql>USEdb_shopping;2.統(tǒng)計1號部門職員人數(shù)，執(zhí)行語句如下：mysql>SELECTCOUNT(*)FROMstafferWHEREdept_id=1;3.統(tǒng)計各部門職員人數(shù)，執(zhí)行語句如下：mysql>SELECTdept_id,COUNT(*)FROMstaffer GROUPBYdept_id;4.統(tǒng)計各部門男女職員人數(shù)和總?cè)藬?shù)，執(zhí)行語句如下：mysql>SELECTdept_id,sex,COUNT(*)FROMstafferGROUPBYdept_id,sexWITHROLLUP;5.查詢商品單價中的最高單價和最低單價，執(zhí)行語句如下：mysql>SELECTMAX(unit_price),MIN(unit_price