实验二统计分析.docx

1、实验二统计分析第十章：创建计算字段1、拼接字段（CONCAT()需要一个或多个用逗号分隔的串）1）select concat(vend_name, (,vend_country,) as sss from vendors order by vend_name;（sss作为别名）拼接字段：vend_name和vend_country的拼接，并按照“名字（城市）”的方式展现，并命名为sss展现2）select concat(rtrim(vend_name), (,trim(vend_country),) as sss from vendors order by vend_name;（rtrim()

2、,ltrim(),trim()）将名称和城市按照如图展现，并删除了名字的右空格，trim是删除指定字符，结果按照供应商名字排序2、使用别名（别名是一个字段或值的替换名，AS为关键字，任何客户机应用都可以按名引用这个计算的列，就像它是一个实际的表列一样）select concat(rtrim(vend_name), (,rtim(vend_country),) as vend_title from vendors order by vend_name;指示SQL创建一个包含指定计算的名为vend_title的计算字段，别名有时也称为导出列。3、执行算术计算Select prod_id,quant

3、ity,item_price from orderitems where order_num=20005;Select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num=20005;选择订单号是20005的item，查询产品id，质量，单价，以及总价，将总价命名为exoanded_price作为别名。4、测试计算*mysql select quantity*item_price as total_price from orderitems;计算所有订

4、单总价格并命名为total_price第十一章：使用数据处理函数1、文本处理函数(P69)1)Select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name;查询vend_name 字段，并将vend_name的结果变成大写字母命名为vend_name_upcase。结果按照vend_name排序2)select cust_name,cust_contact from customers where cust_contact=Y Lie;从客户表查询联系人为Y Lie的客户名字和联系人名

5、字 select cust_name,cust_contact from customers where Soundex(cust_contact)=Soundex(Y Lie);从客户表查询联系人发音与Y Lie相似的客户名字和联系人名字2、日期和时间处理函数(P71)select cust_id,order_num,order_date from orders where order_date=2005-09-01;从订单表中查询日期为2005-09-01的订单信息select cust_id,order_num,order_date from orders where date(orde

6、r_date)=2005-09-01;（使用日期比不适用更可靠。）如果时间中不是后面的00：00：00的时间那么不使用date而直接查询结果将不会被查出来。使用date可以避免这种情况。Select cust_id,order_num,order_date from orders where date(order_date) between 2005-09-01 and 2005-09-30;查询订单时间在哪2005年9月的所有订单信息Select cust_id,order_num,order_date from orders where year(order_date)=2005 and

7、month(order_date)=9;查询订单时间在2005年9月的所有信息select year(curdate(), month(curdate();查询当前系统的日期（年，月）3、数值处理函数（仅处理数值数据） select sin(pi()/2);查询pi/2的正弦值第十二章：汇总数据1、聚集函数（运行在行组上，计算和返回单个值的函数）1）AVG()函数（忽略列值为NULL的行）select avg(prod_price) as avg_price from products where vend_id=1003;从产品表中查询id是1003的平均价格，命名为avg_price 输出

8、2）COUNT()函数（计数，count(*)与count(column)）select count(*) as num_cust from customers; 从客户表中查询总客户数，命名为num_cust输出select count(cust_email) as num_cust from customers;从客户表中查询总的客户邮件的总个数3）MAX()函数（返回列中的最大值，要求指定别名，可对非数值列使用）select max(prod_price) as max_price from products;从产品表中输出最大价格4）MIN()函数（返回列中的最小值，要求指定别名，可对

9、非数值列使用）select min(prod_price) as min_price from products;从产品表中查询最小价格，别名为min_price5）SUM()函数（返回列的和，要求指定别名，也可用来合计计算值）select sum(quantity) as items_ordered fromorderitems where order_num=20005;从订单表中查询订单号为20005的所有订单和，别名为items_orderedselect sum(item_price*quantity) as total_price from orderitems where ord

10、er_num=20005;从订单表中查询订单号为20005的信息，求和每个条目价格和数量的乘积求和最为返回值2、聚集不同值（distinct）select avg(prod_price) as avg_price from products where vend_id=1003;select avg(distinct prod_price) as avg_price from products where vend_id=1003;distinct指定为不同的产品价格，去除重复值。求和产品价格更加合理。3、组合聚集函数select count(*) as num_items,avg(prod_

11、price) as price_avg ,max(prod_price) as price_maxfrom products;包含多个聚集函数，一次返回最低价格，最高价格，和平均价格第十三章：分组数据（对每个组或子集进行聚集）注意：1）GROUP BY 子句可以包含任意数目的列，从面可以实现嵌套；2）若嵌套了分组，数据将在最后规定的分组上进行汇总，此时指定的所有的列都一起计算，所以不再能从个别的列取回数据；3）子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数，如果在SELECT 中使用表达式，则必须在GROUP BY 子句中指定相同的表达式，不能使用别名。4）除聚集计算语句外，

12、SELECT 语句中的每个列都必须在GROUP BY子句中给出。5）若分组列中具有NULL值，则将作为一个分组返回，若列中有多行NULL值，它们将分为一组。6）GROUP BY 子句必须出现在WHERE子句之后，ORDE BY 子句之前。1、数据分组（简单分组需要加WHERE子句）select count(*) as num_prods from products where vend_id=1003;统计产品表中id为1003所有条目的数量2、创建分组（同步自动排序并创建多个分组）select vend_id,count(*) as num_prods from products group

13、 by vend_id;按照vend_id进行分组，查询每组的id，和总个数select vend_id,count(*) as num_prods from products group by vend_id order by vend_id desc;（特别指出要降序排序）*使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。（分组对应级别的总计值）按id分组并降序排列，查询产品表中每个分组的总个数select vend_id,count(*) as num_prods from products group by vend_id with roll

14、up;with rollup 是对每个分组的汇总3、过滤分组（HAVING 子句，必须基于完整的分组而不是个别的行进行过滤）注意：1）事实上，所有简单类型的WHERE子句都可以用HAVING子句来代替，唯一的差别是WHERE过滤行，而HAVING过滤分组。2）HAVING支持所有的WHERE操作符，句法相同，而关键字不同。3）WHERE在数据分组前过滤，而HAVING在数据分组后过滤select cust_id,count(*) as orders_sum FROM orders group by cust_id having count(*)=2;按id分组后的总条目数大于2个的返回sele

15、ct vend_id,count(*) as num_prods from products where prod_price=10 group by vend_id having count(*)=2;查询产品表中按照id分组后总价格大于10的产品,并且剩余条目数大于2 返回id和产品的条目数select vend_id,count(*) as num_prods from products group by vend_id having count(*)=2;查询产品表中按id分组后总每个分组中条目数大于2的所有分组4、分组和排序（GROUP BY 与ORDER BY ）select or

16、der_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)=50;从订单表中查询，并按照订单号码分组，筛选出分组后的order total50 的条目select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)=50 order by ordertotal;在上个基础上，按照order total的大小进行排序注意：SELECT子句顺序SELECT FROM WHERE GROUP BY HAVINGORDER BY LIMIT NUMselect order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price)=50 order by ordertotal limit 3;在上面的基础上只选择前三条