不忽略空值null的聚合函数_数据库知识总结——(四)数据处理函数&聚集函数...

一.数据处理函数

SQL支持利用函数处理数据，大多数SQL支持以下类型的函数：

（一）用于处理文本串的文本函数。

（二）用于在数值数据上进行算术操作的数值函数。

（三）用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数。

（四）返回DBMS特殊信息的系统函数。

本节用到的表结构：

orderitems表

vendors表

orders表

products表

customers表

（1）文本处理函数

文本处理函数名及作用

//upper()将文本转换为大写
//在供应商表里检索供应商名字，及其大写形式叫做别名vend_name_upcase，并按vend_name排序显示。
select vend_name,Upper(vend_name) as vend_name_upcase
from vendors
order by vend_name;

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

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取，统计和处理这些值。

日期和时间函数名及作用

用日期进行数据的过滤需要注意一些问题和使用特殊的函数。首先是MySQL的日期格式，必须为yyyy-mm-dd，例如：

//检索订单日期为2005-09-01的订单记录.
select cust_id,order_num
from orders
where order_date='2005-09-01';

若日期中存在时间值该怎么办？这也是再具体实例中经常遇到的情景。解决方案是让MySQL将给出的日期与列中的日期部分进行比较，而不是将整个列值进行比较。因此必须使用Date()函数，

select cust_id,order_num
from orders
where Date(order_date)= '2005-09-01';

//检索9月份所有订单的两种方法
select cust_id,order_num
from orders
where(order_date) between 2005-09-01' and '2005-09-30';

Select cust_id, order_num 
from orders 
where Year(order_date) = 2005 and Month(order_date) = 9;

(3)数值处理函数

数值处理函数及作用

二.聚集函数

我们经常需要汇总数据而不是检索数据，所以MySQL提供专门的聚集函数，聚集函数的定义是运行在行组上，计算和返回单个值的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。下面依次介绍五大聚集函数：

（1）AVG()函数：通过对表中行数计数并计算特定列值之和，求得该列的平均值。

AVG()只用于确定特定数值列的平均值，并且列名必须作为函数参数给出.为了获得多个列的平均值，必须使用多个AVG()函数。

//返回所有列的平均值。
select avg(prod_price) as avg_price 
from products;

//返回特定列或行的平均值。
select avg(prod_price) as avg_price 
from products
where vend_id=1003;

（2）COUNT()函数:进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目,经常有一下两种用法：

（一）使用count(*)对表中行的数目进行计数，不管表列中包含的是空值还是非空值。

（二）使用count(column)对特定列中具有值的行进行计数，忽略null值。

//返回customers中的客户总数。
select count(*) as num_cust
from customers;

//对只有电子邮件地址的客户计数。Null值会被忽略。
select count(cust_email) as num_cust
from customers;

（3）MAX()函数：返回指定列中的最大值。

Max()一般用来找出最大数值或日期值，但MySQL允许它返回任意列中的最大值，包括文本列中的最大值。在用于文本数据时，若数据按相应列排序，则max()返回最后一行。Max()忽略空值行。

//返回最贵的物品价格。
select max(prod_price) as max_price
from products;

（4）MIN()函数：返回指定列中的最小值。

与Max()类似，在用于文本数据时，若数据按相应列排序，则min()返回第一行。Min()忽略空值行。

//返回最便宜的物品价格。
select min(prod_price) as min_price
from products;

（5）SUM()函数:返回指定列值的和。

//Orderitems包含实际的物品，每个物品有相应数量，检索订购的物品总数。
//sum()可以用来计算合计值，忽略空值行。

//计算订单号为20005购买商品的总数量。
select sum(quantity) as item_ordered 
from orderitems 
where order_num = 20005;

//计算订单号为20005购买商品总价
select sum(item_price*quantity) as total_price 
from orderitems 
where order_num=20005;

（6）聚集不同值

聚合函数中有两个情形:

（一）对所有的行执行计算，指定all参数或不给参数。

（二）只包含不同的值，指定distinct参数。Distinct不能用于count(*),只能用于count()，也可以用于min()和max()，但无实际价值。

//这里指定distinct计算供应商1003的商品平均值
Select avg(distinct prod_price) as avg_price 
from products 
where vend_id=1003;

//这里匹配的值有13、10、2.5、50、4.49，其中重复出现的2.5和10被忽略。

（7）组合聚合函数

//执行4个聚集计算，返回4个值：products中物品的数目，产品价格的最高，最低和平均值
select count(*) as num_items,
       min(prod_price) as price_min,
       max(prod_price) as price_max,
       avg(prod_price) as price_avg
from products;

组合聚合函数的输出结果.