MySQL汇总数据

向右看齐 2024-04-18 14:46 66阅读 0赞

## 聚集函数 ##

**聚集函数（aggregate function）** ：运行在行组上，计算和返回单 个值的函数。

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提 供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分 析和报表生成。  
这种类型的检索例子有以下几种。

1.  确定表中行数（或者满足某个条件或包含某个特定值的行数）。
2.  获得表中行组的和。
3.  找出表列（或所有行或某些特定的行）的最大值、最小值和平均 值。

**为方便这种类型的检索，MySQL给出了5个聚集函数**  
![在这里插入图片描述][20190908130550540.png]  
**例子**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70]  
**1、AVG（）函数**  
AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均 值。

1.  AVG()可用来返回所有列的平均值，  
    例如：返回买入价格的平均值
    
        SELECT AVG(buy_price) AS avgbuy_price
         FROM goods;
2.  也可以用来返回特定列或行的平均值，  
    例如：返回买入价格的，数量为 100 的平均值
    
        SELECT AVG(buy_price) AS avgbuy_price
         FROM goods
         WHERE number=100;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 1]  
**注意**  
**只用于单个列**  
AVG()只能用来确定特定数值列的平均值，而 且列名必须作为函数参数给出。为了获得多个列的平均值， 必须使用多个AVG()函数。  
**NULL值**  
AVG()函数忽略列值为NULL的行。

**2、 COUNT()函数**  
COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特 定条件的行的数目。 COUNT()函数有两种使用方式。

1.  使用COUNT(\*)对表中行的数目进行计数，不管表列中包含的是空 值（NULL）还是非空值。  
    例如：
    
        SELECT COUNT(*) AS goods_all
         FROM goods;
2.  使用COUNT(num)对特定列中具有值的行进行计数，忽略 NULL值。  
    例如：
    
        SELECT COUNT(remarks) AS goods_remarks
        FROM goods;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 2]  
**3、 MAX()函数**  
MAX()返回指定列中的最大值。  
MAX()要求指定列名  
例如：

SELECT MAX(buy_price) AS maxbuy_price
        FROM goods;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 3]

**注意**

1.  对非数值数据使用MAX()  
    虽然MAX()一般用来找出最大的 数值或日期值，但MySQL允许将它用来返回任意列中的最大 值，包括返回文本列中的最大值。在用于文本数据时，如果数 据按相应的列排序，则MAX()返回最后一行。
2.  NULL值  
    MAX()函数忽略列值为NULL的行。

**4、MIN()函数**  
MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。  
MIN()要求指定列名  
例如：

SELECT MIN(buy_price) AS minbuy_price
        FROM goods;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 4]

**注意**

1.  对非数值数据使用MIN()  
    MIN()函数与MAX()函数类似， MySQL允许将它用来返回任意列中的最小值，包括返回文本 列中的最小值。在用于文本数据时，如果数据按相应的列排序， 则MIN()返回最前面的行。
2.  NULL值  
    MIN()函数忽略列值为NULL的行。

**5、 SUM()函数**  
SUM()用来返回指定列值的和（总计）。  
例如：查询物品的所有数量

SELECT SUM(number) AS goods_number
    FROM goods;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 5]

## 聚集不同值 ##

以上５个聚集函数都可以如下使用：

1.  对所有的行执行计算：指定ALL参数或不给参数（因为ALL是默认行为）；
2.  只包含不同的值：指定DIATINCT参数。

下面的例子使用AVG()函数返回产品的平均价格，但它使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

SELECT AVG(DISTINCT buy_price) AS avgbuy_price
    FROM goods;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 6]  
可以看到，在使用了DISTINCT后，计算所得的平均值avgbuy\_price比较高，因为有多个物品具有相同的较低价格，排除它们提升了平均价格。

## 组合聚集函数 ##

目前为止所有聚集函数荔枝都只涉及单个函数。但实际上SELECT语句可以根据需要包含多个聚集函数。**中间用逗号隔开。**  
例如：

SELECT 	COUNT(*) AS num_all,
    		MIN(buy_price) AS minbuy_price,
    		MAX(buy_price) AS maxbuy_price,
    		AVG(buy_price) AS avgbuy_price
    FROM goods;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 7]

[20190908130550540.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/9e5c80c035bc42c388936d366a7eb794.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/80269ac7943f42afb42d78d146941f00.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7588f92c408a43e99cb8c5f03ff83510.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/f075c7a2cdd342ad8b585af296eb8c47.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/3b6f05759c724beb86905de585a733ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/5667ab739c7c425593aff0ceb38edf6d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/90ba9eaf36be49cf9fd7eb7646f43215.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/1f41719aab5c4abb8b8ab146f6aa5717.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEwOTAxMg_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/a3e7fc4387d8419dbda6413f30d8ce4e.png