《MySQL数据库应用与管理项目化教程》课件-项目5-2 统计查询

项目五-2统计查询任务目标任务一应用聚合函数任务二应用GOUOUPBY任务三应用HAVING条件子句任务一应用聚合函数应用聚合函数

主要内容:记录个数统计函数COUNT()累加和函数SUM()平均值函数AVG()最大值函数MAX()最小值函数MIN()重点难点:各个函数的应用规则基本查询语法查阅语法：官网文档：/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}]应用聚合函数基本语法：SELECTfunc(*|col_num) FROMtable_references

[WHEREwhere_condition]COUNT()语法：COUNT（[ALL|DISTINCT]表达式|*）作用：统计所选字段的记录个数。其中：ALL：对所选字段的所有非NULL值统计，为默认值。DISTINCT：对重复的记录仅计算一次，返回所有唯一的非NULL值的记录个数。*：返回所有组中的项数，包括NULL值和重复值在内。应用聚合函数例1：

查询顾客表的人员数量。mysql>USEdb_shop;mysql>SELECT*FROMcustomer;mysql>SELECTCOUNT(ALLemail)FROMcustomer;mysql>SELECTCOUNT(email)FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer;

mysql>SELECTCOUNT(*)FROMcustomer WHEREsex='F';/*观察上面的统计结果*/应用聚合函数例2：

查询订单表处理的人员数量。mysql>SELECT*FROMorders;mysql>SELECTCOUNT(ALLcustomer_id)FROMorders;mysql>SELECTCOUNT(customer_id)FROMorders;mysql>SELECTCOUNT(DISTINCTcustomer_id)FROMorders;/*不写DISTINCT时，默认是ALL*/应用聚合函数SUM()语法：SUM（[ALL|DISTINCT]表达式）作用：统计数值型字段的总和，忽略NULL值。其中：ALL：对所选字段的所有值求和，为默认值。DISTINCT：对重复的记录仅计算一次。

例：mysql>USEdb_shop;mysql>SELECTSUM(quantity)FROMitem;mysql>SELECTSUM(quantity)FROMitem;WHERERgoods_id=‘1’应用聚合函数AVG()语法：AVG（[ALL|DISTINCT]表达式）作用：统计数值型字段的平均值，忽略NULL值。其中：ALL：对所选字段的所有值求平均值，为默认值。DISTINCT：对重复的记录仅计算一次。例：mysql>USEdb_shop;mysql>SELECTAVG(quantity)FROMitem;mysql>SELECTAVG(quantity)FROMitem;WHERERgoods_id=‘1’应用聚合函数MAX()MAX（[ALL|DISTINCT]表达式）作用：统计数值型、日期时间型字段的最大值，忽略NULL值。其中：ALL：对所选字段的所有值求最大值，为默认值。DISTINCT：对重复的记录仅计算一次。例：mysql>USEdb_shop;mysql>SELECTMAX(quantity)FROMitem;mysql>SELECTMAX(quantity)FROMitem;

WHERERgoods_id=‘1’应用聚合函数MIN()语法：MIN（[ALL|DISTINCT]表达式）作用：统计数值型、日期时间型字段的最小值，忽略NULL值。其中：ALL：对所选字段的所有值求最小值，为默认值。DISTINCT：对重复的记录仅计算一次。例：mysql>USEdb_shop;mysql>SELECTMIN(quantity)FROMitem;mysql>SELECTMIN(quantity)FROMitem;

WHERERgoods_id=‘1’考考你统计所有员工人数；统计'001'部门的员工人数；查询最贵商品的价格；查询最便宜商品的价格；统计饮用水商品的均价；统计“1”号商品的销售总数量；任务二应用GOUOUPBY应用GOUOUPBY

主要内容:GOUOUPBY子句与聚合函数结合应用；重点难点:GOUOUPBY的分组表达式对SELECT字段的影响应用GOUOUPBY语法：GROUPBY分组表达式[,…n]功能：使用GROUPBY子句时，结果集被分为若干个组，可以对结果集内的记录进行分组统计，每个组在结果集内有一条记录，有几个组就有几条记录。应用GOUOUPBY基本查询分组例1：按性别分类查询顾客表的人员数量。mysql>USEdb_shop;mysql>SELECT*FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer WHEREsex='F';mysql>SELECTsex,COUNT(*)FROMcustomerGROUPBYsex;应用GOUOUPBY例2：按部门查询职员表的人员数量。mysql>USEdb_shop;mysql>SELECT*FROMcustomer;mysql>SELECTCOUNT(*)FROMcustomer;mysql>SELECTdept_id,COUNT(*)FROMstaffer

GROUPBYdept_id;应用GOUOUPBY用WHERE过滤的记录进行分组统计例1：查询'1'号供应商的各商品销售数量；mysql>USEdb_shop；mysql>SELECTgoods_id,SUM(quantity)FROMitemWHEREsupplier_id=1GROUPBYgoods_id;应用GOUOUPBY对分组后的结果进行ORDERBY排序例1：查询各商品销售数量,并按降序排序；mysql>USEdb_shop；mysql>SELECTgoods_id,SUM(quantity)FROMitem

GROUPBYgoods_id

ORDERBYSUM(quantity)DESC;应用GOUOUPBY多个分组表达式例1：按商品和供应商，查询各商品的各供应商提供产品的销售数量；mysql>USEdb_shop；mysql>SELECTgoods_id,supplier_id,SUM(quantity)FROMitemGROUPBYgoods_id,supplier_id;例2：按商品和供应商分组，查询各商品的各供应商提供产品的销售数量和总数量；mysql>USEdb_shop；mysql>SELECTgoods_id,supplier_id,SUM(quantity)FROMitemGROUPBYgoods_id,supplier_idWITHROLLUP;考考你按部门分组统计所有员工人数；统计'001'部门的男女员工人数；统计“1”号商品的销售总数量；统计各部门男女人数和总人数；任务三应用HAVING条件子句应用HAVING条件子句

主要内容:HAVING子句的应用规则；HAVING子句的使用场合重点难点:HAVING与WHERE的应用区别应用HAVING条件子句语法：GROUPBY分组表达式[,…n] HAVING条件表达式；例1:mysql>USEdb_shop；mysql>SELECTdept_id,COUNT(*)FROMstaffer GROUPBYdept_id HAVINGCOUNT(*)<1;应用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;考考你按部门分组统计所有员工人数，显示超过3人的部门人数；统计各商品的销售总数量，显示销售超过10件的商品数量；任务实施按下列操作完成db_shopping数据库表记录的基本统计查询。1.选择db_shopping数据库，执行语句如下：mysql>USEdb_shopping;2.统计1号部门职员人数，执行语句如下：mysql>SELECTCOUNT(*)FROMstafferWHEREdept_id=1;3.统计各部门职员人数，执行语句如下：mysql>SELECTdept_id,COUNT(*)FROMstaffer GROUPBYdept_id;4.统计各部门男女职员人数和总人数，执行语句如下：mysql>SELECTdept_id,sex,COUNT(*)FROMstafferGROUPBYdept_id,sexWITHROLLUP;5.查询商品单价中的最高单价和最低单价，执行语句如下：mysql>SELECTMAX(unit_price),MIN(unit_price