《MySQL數(shù)據(jù)庫(kù)應(yīng)用與管理項(xiàng)目化教程》課件-項(xiàng)目5-2 統(tǒng)計(jì)查詢

項(xiàng)目五-2統(tǒng)計(jì)查詢?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)容:記錄個(gè)數(shù)統(tǒng)計(jì)函數(shù)COUNT()累加和函數(shù)SUM()平均值函數(shù)AVG()最大值函數(shù)MAX()最小值函數(shù)MIN()重點(diǎn)難點(diǎn):各個(gè)函數(shù)的應(yīng)用規(guī)則基本查詢語(yǔ)法查閱語(yǔ)法：官網(wǎng)文檔：/doc/refman/8.0/en/select.html語(yǔ)法：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ù)基本語(yǔ)法：SELECTfunc(*|col_num) FROMtable_references

[WHEREwhere_condition]COUNT()語(yǔ)法：COUNT（[ALL|DISTINCT]表達(dá)式|*）作用：統(tǒng)計(jì)所選字段的記錄個(gè)數(shù)。其中：ALL：對(duì)所選字段的所有非NULL值統(tǒng)計(jì)，為默認(rèn)值。DISTINCT：對(duì)重復(fù)的記錄僅計(jì)算一次，返回所有唯一的非NULL值的記錄個(gè)數(shù)。*：返回所有組中的項(xiàng)數(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)計(jì)結(jié)果*/應(yīng)用聚合函數(shù)例2：

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

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

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

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

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

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

GROUPBYgoods_id

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

主要內(nèi)容:HAVING子句的應(yīng)用規(guī)則；HAVING子句的使用場(chǎng)合重點(diǎn)難點(diǎn):HAVING與WHERE的應(yīng)用區(qū)別應(yīng)用HAVING條件子句語(yǔ)法：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;考考你按部門(mén)分組統(tǒng)計(jì)所有員工人數(shù)，顯示超過(guò)3人的部門(mén)人數(shù)；統(tǒng)計(jì)各商品的銷(xiāo)售總數(shù)量，顯示銷(xiāo)售超過(guò)10件的商品數(shù)量；任務(wù)實(shí)施按下列操作完成db_shopping數(shù)據(jù)庫(kù)表記錄的基本統(tǒng)計(jì)查詢。1.選擇db_shopping數(shù)據(jù)庫(kù)，執(zhí)行語(yǔ)句如下：mysql>USEdb_shopping;2.統(tǒng)計(jì)1號(hào)部門(mén)職員人數(shù)，執(zhí)行語(yǔ)句如下：mysql>SELECTCOUNT(*)FROMstafferWHEREdept_id=1;3.統(tǒng)計(jì)各部門(mén)職員人數(shù)，執(zhí)行語(yǔ)句如下：mysql>SELECTdept_id,COUNT(*)FROMstaffer GROUPBYdept_id;4.統(tǒng)計(jì)各部門(mén)男女職員人數(shù)和總?cè)藬?shù)，執(zhí)行語(yǔ)句如下：mysql>SELECTdept_id,sex,COUNT(*)FROMstafferGROUPBYdept_id,sexWITHROLLUP;5.查詢商品單價(jià)中的最高單價(jià)和最低單價(jià)，執(zhí)行語(yǔ)句如下：mysql>SELECTMAX(unit_price),MIN(unit_price