实验二统计分析.docx

实验二统计分析.docx

《实验二统计分析.docx》由会员分享，可在线阅读，更多相关《实验二统计分析.docx（11页珍藏版）》请在冰豆网上搜索。

实验二统计分析

第十章：

创建计算字段

1、拼接字段（CONCAT（）需要一个或多个用逗号分隔的串）

1）selectconcat（vend_name,'（',vend_country,'）'）as'sss'fromvendorsorderbyvend_name;（sss作为别名）

拼接字段：

vend_name和vend_country的拼接，并按照“名字（城市）”的方式展现，并命名为sss展现

2）selectconcat（rtrim（vend_name）,'（',trim（vend_country）,'）'）as'sss'fromvendorsorderbyvend_name;（rtrim（）,ltrim（）,trim（））

将名称和城市按照如图展现，并删除了名字的右空格，trim是删除指定字符，结果按照供应商名字排序

2、使用别名（别名是一个字段或值的替换名，AS为关键字，任何客户机应用都可以按名引用这个计算的列，就像它是一个实际的表列一样）

selectconcat（rtrim（vend_name）,'（',rtim（vend_country）,'）'）asvend_titlefromvendorsorderbyvend_name;

指示SQL创建一个包含指定计算的名为vend_title的计算字段，别名有时也称为导出列。

3、执行算术计算

Selectprod_id,quantity,item_pricefromorderitemswhereorder_num=20005;

Selectprod_id,quantity,item_price,quantity*item_priceasexpanded_pricefromorderitemswhereorder_num=20005;

选择订单号是20005的item，查询产品id，质量，单价，以及总价，将总价命名为exoanded_price作为别名。

4、测试计算*

mysql>selectquantity*item_priceastotal_pricefromorderitems;

计算所有订单总价格并命名为total_price

第十一章：

使用数据处理函数

1、文本处理函数（P69）

1）Selectvend_name,upper（vend_name）asvend_name_upcasefromvendorsorderbyvend_name;

查询vend_name字段，并将vend_name的结果变成大写字母命名为vend_name_upcase。

结果按照vend_name排序

2）selectcust_name,cust_contactfromcustomerswherecust_contact=’YLie’;

从客户表查询联系人为YLie的客户名字和联系人名字

selectcust_name,cust_contactfromcustomerswhereSoundex（cust_contact）=Soundex（’YLie’）;

从客户表查询联系人发音与YLie相似的客户名字和联系人名字

2、日期和时间处理函数（P71）

selectcust_id,order_num,order_datefromorderswhereorder_date=’2005-09-01’;

从订单表中查询日期为2005-09-01的订单信息

selectcust_id,order_num,order_datefromorderswheredate（order_date）='2005-09-01';

（使用日期比不适用更可靠。

）

如果时间中不是后面的00：

00：

00的时间那么不使用date而直接查询结果将不会被查出来。

使用date可以避免这种情况。

Selectcust_id,order_num,order_datefromorderswheredate（order_date）between'2005-09-01'and'2005-09-30';

查询订单时间在哪2005年9月的所有订单信息

Selectcust_id,order_num,order_datefromorderswhereyear（order_date）=2005andmonth（order_date）=9;

查询订单时间在2005年9月的所有信息

selectyear（curdate（））,month（curdate（））;

查询当前系统的日期（年，月）

3、数值处理函数（仅处理数值数据）

selectsin（pi（）/2）;

查询pi/2的正弦值

第十二章：

汇总数据

1、聚集函数（运行在行组上，计算和返回单个值的函数）

1）AVG（）函数（忽略列值为NULL的行）

selectavg（prod_price）asavg_pricefromproductswherevend_id=1003;

从产品表中查询id是1003的平均价格，命名为avg_price输出

2）COUNT（）函数（计数，count（*）与count（column））

selectcount（*）asnum_custfromcustomers;

从客户表中查询总客户数，命名为num_cust输出

selectcount（cust_email）asnum_custfromcustomers;

从客户表中查询总的客户邮件的总个数

3）MAX（）函数（返回列中的最大值，要求指定别名，可对非数值列使用）

selectmax（prod_price）asmax_pricefromproducts;

从产品表中输出最大价格

4）MIN（）函数（返回列中的最小值，要求指定别名，可对非数值列使用）

selectmin（prod_price）asmin_pricefromproducts;

从产品表中查询最小价格，别名为min_price

5）SUM（）函数（返回列的和，要求指定别名，也可用来合计计算值）

selectsum（quantity）asitems_orderedfromorderitemswhereorder_num=20005;

从订单表中查询订单号为20005的所有订单和，别名为items_ordered

selectsum（item_price*quantity）astotal_pricefromorderitemswhereorder_num=20005;

从订单表中查询订单号为20005的信息，求和每个条目价格和数量的乘积求和最为返回值

2、聚集不同值（distinct）

selectavg（prod_price）asavg_pricefromproductswherevend_id=1003;

selectavg（distinctprod_price）asavg_pricefromproductswherevend_id=1003;

distinct指定为不同的产品价格，去除重复值。

求和产品价格更加合理。

3、组合聚集函数

selectcount（*）asnum_items,

avg（prod_price）asprice_avg,

max（prod_price）asprice_max

fromproducts;

包含多个聚集函数，一次返回最低价格，最高价格，和平均价格

第十三章：

分组数据（对每个组或子集进行聚集）

注意：

1）GROUPBY子句可以包含任意数目的列，从面可以实现嵌套；

2）若嵌套了分组，数据将在最后规定的分组上进行汇总，此时指定的所有的列都一起计算，所以不再能从个别的列取回数据；

3）子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数，如果在SELECT中使用表达式，则必须在GROUPBY子句中指定相同的表达式，不能使用别名。

4）除聚集计算语句外，SELECT语句中的每个列都必须在GROUPBY子句中给出。

5）若分组列中具有NULL值，则将作为一个分组返回，若列中有多行NULL值，它们将分为一组。

6）GROUPBY子句必须出现在WHERE子句之后，ORDEBY子句之前。

1、数据分组（简单分组需要加WHERE子句）

selectcount（*）asnum_prodsfromproductswherevend_id=1003;

统计产品表中id为1003所有条目的数量

2、创建分组（同步自动排序并创建多个分组）

selectvend_id,count（*）asnum_prodsfromproductsgroupbyvend_id;

按照vend_id进行分组，查询每组的id，和总个数

selectvend_id,count（*）asnum_prodsfromproductsgroupbyvend_idorderbyvend_iddesc;（特别指出要降序排序）

*使用WITHROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

（分组对应级别的总计值）

按id分组并降序排列，查询产品表中每个分组的总个数

selectvend_id,count（*）asnum_prodsfromproductsgroupbyvend_idwithrollup;

withrollup是对每个分组的汇总

3、过滤分组（HAVING子句，必须基于完整的分组而不是个别的行进行过滤）

注意：

1）事实上，所有简单类型的WHERE子句都可以用HAVING子句来代替，唯一的差别是WHERE过滤行，而HAVING过滤分组。

2）HAVING支持所有的WHERE操作符，句法相同，而关键字不同。

3）WHERE在数据分组前过滤，而HAVING在数据分组后过滤

selectcust_id,count（*）asorders_sumFROMordersgroupbycust_idhavingcount（*）>=2;

按id分组后的总条目数大于2个的返回

selectvend_id,count（*）asnum_prodsfromproductswhereprod_price>=10groupbyvend_idhavingcount（*）>=2;

查询产品表中按照id分组后总价格大于10的产品,并且剩余条目数大于2返回id和产品的条目数

selectvend_id,count（*）asnum_prodsfromproductsgroupbyvend_idhavingcount（*）>=2;

查询产品表中按id分组后总每个分组中条目数大于2的所有分组

4、分组和排序（GROUPBY与ORDERBY）

selectorder_num,sum（quantity*item_price）asordertotalfromorderitemsgroupbyorder_numhavingsum（quantity*item_price）>=50;

从订单表中查询，并按照订单号码分组，筛选出分组后的ordertotal>50的条目

selectorder_num,sum（quantity*item_price）asordertotalfromorderitemsgroupbyorder_numhavingsum（quantity*item_price）>=50orderbyordertotal;

在上个基础上，按照ordertotal的大小进行排序

注意：

SELECT子句顺序

SELECTFROM[WHERE[GROUPBY[HAVING][ORDERBY]]LIMIT[NUM]

selectorder_num,sum（quantity*item_price）asordertotalfromorderitemsgroupbyorder_numhavingsum（quantity*item_price）>=50orderbyordertotallimit3;

在上面的基础上只选择前三条