MySQL汇总数据

秒速五厘米 2022-06-06 03:20 169阅读 0赞

说明：本文所使用的数据库样例表创建脚本下载地址：[https://github.com/zhaoxd298/mysql\_scripts][https_github.com_zhaoxd298_mysql_scripts]

### **一、聚集函数** ###

我们经常需要汇总数据而不是把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有一下几种：  
　　1.确定表中行数（或者满足某个条件或包含某个特定值的行数）；  
　　2.获得表中行组的和；  
　　3.找出列表中的最大值、最小值和平均值。

为方便这种类型的检索，MySQL给出了5个聚集函数，如下表：  
![这里写图片描述][20171028170939822]

#### **1.AVG()函数** ####

AVG()可用来返回所有列的平均值，也可用来返回特定列或行的平均值。下面例子使用AVG()返回products表中所有产品的平均价格：

mysql> SELECT AVG(prod_price) AS avg_price
        -> FROM products;

执行结果：  
![这里写图片描述][20171028171431308]

下面例子返回特定供应商提供产品的平均价格：

mysql> SELECT AVG(prod_price) AS avg_price
        -> FROM products
        -> WHERE vend_id = 1003;

执行结果：  
![这里写图片描述][20171028171701616]

**注意：** AVG()只能确定单列的平均值，为了获得多个列的平均值必须使用多个AVG()函数。AVG()函数会忽略列值为NULL的行。

#### **2.COUNT()函数** ####

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。COUNT()函数有两种使用方式：  
　　1.使用COUNT(\*)对表中行的数目进行计数，不管列中包含的是空值（NULL）还是非空值；  
　　2.使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

下面的例子返回customers表中客户的总数：

mysql> SELECT COUNT(*) AS num_cust
        -> FROM customers;

执行结果：  
![这里写图片描述][20171028174147607]

下面例子只对具有电子邮件地址的客户计数：

mysql> SELECT COUNT(cust_email) AS num_cust
        -> FROM customers;

执行结果：  
![这里写图片描述][20171028174523871]

#### **3.MAX()函数** ####

MAX()返回指定列中的最大值。MAX()要求必须指定列名，使用方法如下：

mysql> use crashcourse;
    mysql> SELECT MAX(prod_price) AS max_price
        -> FROM products;

执行结果：  
![这里写图片描述][20171030091247484]

MAX()一般用来找出最大的**数值或日期值**，但MySQL允许它返回任意列中的最大值，包括返回文本列中的最大值。**MAX()函数忽略列值为NULL的行。**

#### **4.MIN()函数** ####

MIN()的功能与MAX()相反，它返回指定列的最小值，MIN()也必须要指定列名，使用方法如下：

mysql> SELECT MIN(prod_price) AS min_price
        -> FROM products;

执行结果：  
![这里写图片描述][20171030091905482]

MIN()与MAX()类似，也允许返回任意列的最小值。MIN()也忽略列值为NULL的行。

#### **5.SUM()函数** ####

SUM()用来返回指定列值的和，使用方法如下：

mysql> SELECT SUM(quantity) AS items_ordered
        -> FROM orderitems
        -> WHERE order_num = 20005;

执行结果：  
![这里写图片描述][20171030092411855]

SUM()也可以用来合计计算值。下面例子中，合计每项物品的item\_price\*quantity，得出总的订单金额：

mysql> SELECT SUM(item_price*quantity) AS total_price
        -> FROM orderitems
        -> WHERE order_num = 20005;

执行结果：  
![这里写图片描述][20171030092824749]

### **二、聚集不同值** ###

以上５个聚集函数都可以如下使用：  
　　１.对所有的行执行计算：指定ALL参数或不给参数（因为ALL是默认行为）；  
　　2.只包含不同的值：指定DIATINCT参数。

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

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
        -> FROM products
        -> WHERE vend_id = 1003;

执行结果：  
![这里写图片描述][20171030094100083]

可以看到，在使用了DISTINCT后，计算所得的平均值avg\_price比较高，因为有多个物品具有相同的较低价格，排除它们提升了平均价格。

### **组合聚集函数** ###

目前为止所有聚集函数荔枝都只涉及单个函数。但实际上SELECT语句可以根据需要包含多个聚集函数，请看下面例子：

mysql> 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;

执行结果：  
![这里写图片描述][20171030094824323]

[https_github.com_zhaoxd298_mysql_scripts]: https://github.com/zhaoxd298/mysql_scripts
[20171028170939822]: /images/20220606/6e281feb03ce433ab085e47e3978cf50.png?
[20171028171431308]: /images/20220606/3e1d6911dc844a179cc7c4b4ac3f6a9d.png?
[20171028171701616]: /images/20220606/1650980d1d5e46969b8d3b4a80cbceab.png?
[20171028174147607]: /images/20220606/7640565f062642de981c6b1e5a6f03d1.png?
[20171028174523871]: /images/20220606/4693c956d5d347b5a8ac3a9dd7a112ed.png?
[20171030091247484]: /images/20220606/d8d957e70c4142d9b3a192857d93531d.png?
[20171030091905482]: /images/20220606/47a899dd34894481ad3d97ace656e061.png?
[20171030092411855]: /images/20220606/c9639d7d3d9f4b10b2cbc6a54ab87857.png?
[20171030092824749]: /images/20220606/95ac30495e7b43acba999619d60d7ddf.png?
[20171030094100083]: /images/20220606/983d09cf9e4c43a4bc78dc861e81269d.png?
[20171030094824323]: /images/20220606/cf2249aba4cf40128c22b81502c67b13.png?