当我们掌握了基本的MySQL数据库操作后,在面对超多的数据库时,我们会考虑mysql数据库中有木有耐心而函数,可以方便我们学习呢?
在本节中,介绍了一些典型的集合函数的用法,包括计数、均值、极值和总和,这些都是SQL语言中非常常用的函数。
到现在为止,你只学习了如何根据特定的条件从表中取出一条或多条记录。但是,假如你想对一个表中的记录进行数据统计。例如,如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计,都需要使用集合函数。你可以统计记录数目,平均值,最小值,最大值,或者求和。当你使用一个集合函数时,它只返回一个数,该数值代表这几个统计值之一。
这些函数的最大特点就是经常和GROUPBY语句配合使用,需要注意的是集合函数不能和非分组的列混合使用。
行列计数
计算查询语句返回的记录行数
直接计算函数COUNT(*)的值,例如,计算pet表中猫的只数:
mysql>SELECTcount(*)FROMpetWHEREspecies=’cat’;
+———-+
|count(*)|
+———-+
|2|
+———-+
统计字段值的数目
例如,计算pet表中species列的数目:
mysql>SELECTcount(species)FROMpet;
+—————-+
|count(species)|
+—————-+
|9|
+—————-+
如果相同的种类出现了不止一次,该种类将会被计算多次。如果你想知道种类为某个特定值的宠物有多少个,你可以使用WHERE子句,如下例所示:
mysql>SELECTCOUNT(species)FROMpetWHEREspecies='cat';
注意这条语句的结果:
+—————-+
|COUNT(species)|
+—————-+
|2|
+—————-+
这个例子返回种类为'cat'的作者的数目。如果这个名字在表pet中出现了两次,则次函数的返回值是2。而且它和上面提到过的语句的结果是一致的:
SELECTcount(*)FROMpetWHEREspecies=’cat’
实际上,这两条语句是等价的。
假如你想知道有多少不同种类的的宠物数目。你可以通过使用关键字DISTINCT来得到该数目。如下例所示:
mysql>SELECTCOUNT(DISTINCTspecies)FROMpet;
+————————-+
|COUNT(DISTINCTspecies)|
+————————-+
|5|
+————————-+
如果种类'cat'出现了不止一次,它将只被计算一次。关键字DISTINCT决定了只有互不相同的值才被计算。
通常,当你使用COUNT()时,字段中的空值将被忽略。
另外,COUNT()函数通常和GROUPBY子句配合使用,例如可以这样返回每种宠物的数目:
mysql>SELECTspecies,count(*)FROMpetGROUPBYspecies;
+———+———-+
|species|count(*)|
+———+———-+
|bird|2|
|cat|2|
|dog|3|
|hamster|1|
|snake|1|
+———+———-+
计算字段的平均值
需要计算这些值的平均值。使用函数AVG(),你可以返回一个字段中所有值的平均值。
假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1到10之间投票,表示他们喜欢你站点的程度。你把投票结果保存在名为vote的INT型字段中。要计算你的用户投票的平均值,你需要使用函数AVG():
SELECTAVG(vote)FROMopinion
这个SELECT语句的返回值代表用户对你站点的平均喜欢程度。函数AVG()只能对数值型字段使用。这个函数在计算平均值时也忽略空值。
再给出一个实际例子,例如我们要计算pet表中每种动物年龄的平均值,那么使用AVG()函数和GROUPBY子句:
mysql>SELECTspecies,AVG(CURDATE()-birth)FROMpetGROUPBYspecies;
返回的结果为:
+———+———————-+
|species|AVG(CURDATE()-birth)|
+———+———————-+
|bird|34160|
|cat|74959.5|
|dog|112829.66666667|
|hamster|19890|
|snake|49791|
+———+———————-+
计算字段值的和
假设你的站点被用来出售某种商品,已经运行了两个月,是该计算赚了多少钱的时候了。假设有一个名为orders的表用来记录所有访问者的定购信息。要计算所有定购量的总和,你可以使用函数SUM():
SELECTSUM(purchase_amount)FROMorders
#p#分页标题#e#
函数SUM()的返回值代表字段purchase_amount中所有值的总和。字段purchase_amount的数据类型也许是DECIMAL类型,但你也可以对其它数值型字段使用函数SUM()。
用一个不太恰当的例子说明,我们计算pet表中同种宠物的年龄的总和:
mysql>SELECTspecies,SUM(CURDATE()-birth)FROMpetGROUPBYspecies;
你可以查看结果,与前一个例子对照:
+———+———————-+
|species|SUM(CURDATE()-birth)|
+———+———————-+
|bird|68320|
|cat|149919|
|dog|338489|
|hamster|19890|
|snake|49791|
+———+———————-+
计算字段值的极值
求字段的极值,涉及两个函数MAX()和MIN()。
例如,还是pet表,你想知道最早的动物出生日期,由于日期最早就是最小,所以可以使用MIN()函数:
mysql>SELECTMIN(birth)FROMpet;
+————+
|MIN(birth)|
+————+
|1989-05-13|
+————+
但是,你只知道了日期,还是无法知道是哪只宠物,你可能想到这样做:
SELECTname,MIN(birth)FROMpet;
但是,这是一个错误的SQL语句,因为集合函数不能和非分组的列混合使用,这里name列是没有分组的。所以,你无法同时得到name列的值和birth的极值。
MIN()函数同样可以与GROUPBY子句配合使用,例如,找出每种宠物中最早的出生日期:
mysql>SELECTspecies,MIN(birth)FROMpetGROUPBYspecies;
下面是令人满意的结果:
+———+————+
|species|MIN(birth)|
+———+————+
|bird|1997-12-09|
|cat|1993-02-04|
|dog|1989-05-13|
|hamster|1999-03-30|
|snake|1996-04-29|
+———+————+
另一方面,如果你想知道最近的出生日期,就是日期的最大值,你可以使用MAX()函数,如下例所示:
mysql>SELECTspecies,MAX(birth)FROMpetGROUPBYspecies;
+———+————+
|species|MAX(birth)|
+———+————+
|bird|1998-09-11|
|cat|1994-03-17|
|dog|1990-08-31|
|hamster|1999-03-30|
|snake|1996-04-29|
+———+————+
总结
在本节中,介绍了一些典型的集合函数的用法,包括计数、均值、极值和总和,这些都是SQL语言中非常常用的函数。
这些函数之所以称之为集合函数,是因为它们应用在多条记录中,所以集合函数最常见的用法就是与GROUPBY子句配合使用,最重要的是集合函数不能同未分组的列混合使用。