文章目录
- 1.概述
- 2.聚合函数的使用
- 2.1 AVG( )函数
- 2.1.1 使用AVG( )函数返回所有列的平均值
- 2.1.2 使用AVG( )函数返回特定列的平均值
- 2.1.3 使用AVG( )函数的注意事项
- 2.2 COUNT( )函数
- 2.2.1 使用COUNT( )函数查询表中全部行的数目
- 2.2.2 使用COUNT( )函数查询表中特定行的数目
- 2.2.3 使用COUNT( )函数的效率对比
- 2.3 MAX( )函数
- 2.4 MIN( )函数
- 2.5 SUM( )函数
- 2.6 DISTINCT( )函数
- 2.7 组合聚合函数
1.概述
聚合函数是运行在行组上,计算和返回单个值的函数;
MySql提供了专门的函数,将查询到的数据进行汇总后再通过结果返回给我们,这就是聚合函数。MySql可以使用聚合函数更加方便的分析和生成报表,其提供以下场景的支持:
- 确定表中的行数
- 获得表中行组的和
- 找出表列的最大值,最小值和平均值
这些场景都需要对表中的数据进行汇总。u因此,返回实际的表数据是对时间和处理资源的一种浪费,我们在查询的过程中就将此数据进行汇总,会更加节省时间,提高效率;
2.聚合函数的使用
MySql提供了以下聚合函数的支持:
函数 | 说明 |
AVG( ) | 返回某列的平均值 |
COUNT( ) | 返回某列的行数 |
MAX( ) | 返回某列的最大值 |
MIN( ) | 返回某列的最小值 |
SUM( ) | 返回某列值之和 |
2.1 AVG( )函数
AVG( )通过对表中行数计数并计算特定列值的和,求得该列的平均值。AVG( )可以用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
2.1.1 使用AVG( )函数返回所有列的平均值
假设我们需要求 products 表中所有产品的平均价格,我们需要这样完成SQl语句:
输入:
SELECT AVG(prod_price) AS avg_price
FROM products;
输出:
返回值的列名为我们设置的别名avg_price,列中的值为所有产品(行)的平均价格;
2.1.2 使用AVG( )函数返回特定列的平均值
当使用AVG( ) 来确定特定列或行的平均值时,我们需要这样完成SQL:
输入:
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
输出:
其返回供应商为1003的所有产品的平均价格;
2.1.3 使用AVG( )函数的注意事项
AGV( )只能用来确定特定数值列的平均值,而且列名必须作为参数给出,为了获得多个列的平均值,必须使用多个AGV( )函数;
而且如果表中列值为NULL的话,AGV( )函数会忽略这个行;
2.2 COUNT( )函数
COUNT( )函数是用来计数的函数。可以利用COUNT( )确定表中行的数目或符合特定条件的行的数目;
COUNT( )函数有三种使用方法:
- 使用COUNT(1)对表中行的数目进行计数,不管表列中包含的是空值还是非空值;
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值还是非空值;
- 使用COUNT(column)对特定列中具有值得行进行计数,忽略NULL值;
其实COUNT(1)与COUNT(*)的区别可以忽略不计,count(1),其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段,而是表示一个固定值。
可以理解为每个表中都有一个影藏的看不到的列,列中所有字段的值都是固定值1,count(1),就是计算一共有多少个1。
count(*),执行时会把星号翻译成字段的具体名字,效果也是一样的,不过多了一个翻译的动作,比固定值的方式效率稍微低一些。
就是这一点效率的差别,导致在实际开发中,人们都选择使用count(1)而不是count(*);
2.2.1 使用COUNT( )函数查询表中全部行的数目
我们查询customers表中的客户的总数:
输入:
SELECT COUNT(1)
FROM customers;
输出:
2.2.2 使用COUNT( )函数查询表中特定行的数目
接下来我们查询有预留电子邮箱的客户的总人数:
输入:
SELECT COUNT(cust_email) AS num_email
FROM customers;
输出:
这条SQL中使用COUNT( )函数对cust_email列中有值的的行进行计数,如果指定列名,则会忽略NULL值的行;
2.2.3 使用COUNT( )函数的效率对比
- 如果列为主键,count(列名)效率优于count(1)
- 如果列不为主键,count(1)效率优于count(列名)
- 如果表中存在主键,count(主键列名)效率最优
- 如果表中只有一列,则count(*)效率最优
- 如果表有多列,且不存在主键,则count(1)效率优于count(*)
2.3 MAX( )函数
MAX( )函数返回指定列中的最大值。MAX( )函数要求指定列名,如:
输入:
SELECT MAX(prod_price) AS max_price
FROM products;
输出:
使用MAX( )函数会忽略列值为NULL的行。并且MAX( )函数虽然一般用来找出最大的列值或日期值,但MySql允许它用来返回任意列中的最大值,包括文本列。在用于文本数据时,如果数据按照相应的列排序,那么会返回最后一行。
2.4 MIN( )函数
MIN( )函数正好与MAX( )函数相反,它返回指定列的最小值。其也要求指定列名,如:
输入:
SELECT MIN(prod_price) AS min_price
FROM products;
输出:
与MAX( )函数一样,MIN( )函数也会忽略列值为NULL的行。MIN( )函数允许它用来返回任意列中的最小值,包括文本列。在用于文本数据时,如果数据按照相应的列排序,那么会返回最前面的一行。
2.5 SUM( )函数
SUM( )函数用来返回指定列值得和。
假设orderitems表包含订单中得实际的物品,每个物品有相应的数量,那么我们可以使用下列的SQL语句来查询订购物品的总数:
输入:
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
输出:
SUM( )函数用来返回订单中的所有物品数量之和,WHERE戏剧保证只统计某个物品订单中的物品;
SUM( )函数也可以用来合计计算值。在下面的例子中,合计每项物品的item_price*quantity,得出总的订单金额:
输入:
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
输出:
函数SUM(item_price*quantity)返回订单中所有物品价钱之和,WHERE子句同样保证只统计某个物品订单中的物品。
同样的,SUM( )函数也会忽略列值为NULL的行。并且如上面的例子所示,利用标准的算术操作符,所有的聚集函数都可以用来执行多个列上的计算。
2.6 DISTINCT( )函数
DISTINCT( )函数可以对所有的行执行计算,可以指定ALL参数,或者不给参数,因为默认就是给定了ALL参数;
DISTINCT( )函数用来对结果进行去重,可以对查询到的结果进行去重后展示。
假设我们查询供应商提供的产品的价格:
输入:
SELECT item_price
FROM orderitems;
输出:
这样可以得到所有的价格,如果我们使用了DISTINCT( )函数后:
输入:
SELECT DISTINCT item_price
FROM orderitems;
输出:
此时,只会展示去重后的结果,有相同值的行将只保留一行。同样的,DISTINCT( )函数也可以在其他聚合函数上执行,例如:
输入:
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
输出:
可以看到在使用了DISTINCT( )函数后,平均价格变高了,这是因为此例子中多个物品具有相同的较低价格,排除它们后提升了平均价格。
2.7 组合聚合函数
最后,我们来看一个多个聚合函数组合的例子:
输入:
SELECT COUNT(1) AS num_items,
MIN(prod_price) AS min_price,
MAX(prod_price) AS max_price,
AVG(DISTINCT prod_price) AS avg_distinct_price,
SUM(prod_price) AS sum_price
FROM products;
输出:
这里用一条SQL语句执行了6个聚合函数,返回了6个值:物品的总数,最小价格,最大价格,去重后的平均价格,所有产品价格的总价。