MySQL必知必会6

汇总数据

聚集函数

我们经常需要汇总数据而不是把他检索出来，例如确定表的行数，获得表的行组的和及求表列的最大值或最小值，这些都需要对表中的数据进行汇总，为此，MySQL中提供了聚集函数来进行汇总数据。

以下是5个常用聚集函数：

以下说明各函数的使用：

MySQL还提供了一系列的标准偏差函数，在此不去叙述。

AVG( )函数

AVG( )可以求出特定列的平均值。

这个例子返回prod_price的平均值。函数的参数为列的名字。

AVG( ) 函数也可以求特定行的平均值，这个时候就要使用WHERE子句来过滤数据。

这个例子就是查找vend_id = 1003的行的数据的平均值。

AVG( )函数只能用来求特定列的平均值，且函数参数是列名，如果想查询多个列的平均值，需要使用多个AVG( )函数。

AVG( )函数忽略值为NULL的行。

COUNT( )函数

COUNT( )函数用来计数，可以确定行的数目，或符合条件的行的数目。

COUNT( )函数有两种使用方式：

1）使用COUNT(*)对表中行的数目进行计算。(包含值为NULL的行)

2）使用COUNT(column)对特定列中具有值得行进行计数，忽略值为NULL得行。

例如在customers表中

假设我们使用COUNT(*)和使用COUNT(cust_email)，其结果是不同的。

主要是因为COUNT(*)查询表中的行数，而COUNT(column)只查询这一列值不为空的行数。

MAX( )函数

MAX( )返回指定列的最大值，参数要指定列名。

假设我们要查找的最大值有限定条件，我们也可以使用WHERE子句：

这个例子就加了限定条件vend_id = 1003。

MAX( )允许对于非数值数据的列进行检索，例如用于文本数据的时候，返回字典排序的最大值。

MAX( )函数忽略值为NULL的行。

MIN( )函数

MIN( )函数返回特定列的最小值，同MAX( )用法相同。

SUM( )函数

SUM( )函数返回指定列的和。

例如下面这个例子返回20005订单中quantity列的和。

聚集不同值

对于上述5各函数，都可以使用ALL和DISTINCT用以修饰，DISTINCT只对行中的不同的值进行操作。

这个返回列中不同行的平均值。

DISTINCT只能用于修饰列名，如果使用COUNT(DISTINCT *)会显示错误。

组合聚集函数

以上的例子都只涉及一个函数，但在实际操作中，可以包含多个函数。

这个例子通过多个函数，返回了列的行数，最小值，最大值，平均值。

分组数据

数据分组

在上一节中我们已经可以计算出某一个vend_id的产品数量，但如果我们想要得到每一个vend_id的数量该怎么做呢？

这个时候就可以使用分组来进行检索。

创建分组

分组是使用SELECT语句中的GROUP BY 子句来创建的。

来看一个例子以更好的理解GROUP BY 子句：

在这个例子中，通过GROUP BY 子句，把vend_id列分成几个不同的组，分组依据是vend_id，即vend_id中的不同值。

在使用GROUP 子句之前，要知道一些重要的规定。

1）GROUP BY 子句可以包含任意数目的列，即对分组进行嵌套，为数据分组进行更好的控制。

2）如果嵌套分组，数据将在最后的分组上进行汇总，即最后得出的分组时依据最后规定的分组条件。

3）GROUP BY 子句中列出的每一个列都是有效的列或表达式，但不能是聚集函数之类的（还记得聚集函数吗？）如果在SELECT语句中使用表达式，那么在GROUP BY 子句中也要使用表达式，不能使用别名。

4）除聚集计算语句以外，SELECT 中的所有列，都要在GROUP BY 中列出。

5）如果分组中有NULL值，则NUL为一个新的分组，所有NULL为一组。

6）GROUP BY 必须在WHERE字句之后，在ORDER子句之前。

过滤分组

除了能使用GROUP BY子句进行分组之外，MySQL还支持过滤分组，什么是过滤分组呢？ 还记得过滤数据吗？过滤数据使用WHERE进行过滤，但分组中WHERE子句并不能完成任务。因为WHERE过滤的是行而不是分组，这个时候我们就要使用HAVING子句来进行过滤分组。

先看一个例子：

这个例子和上一个例子非常相似，只是多了一个过滤分组子句，他过滤了数量小于3的分组，和WHERE子句很相似。

但事实上WHERE 和HAVING 子句有很大的区别，WHERE子句其实是在分组之前进行过滤，而HAVING则是在分组之后进行过滤。因为HAVING子句在GROUP之后，WHERE 在GROUP之前。

那么有没有WHERE和HAVING混用的情况呢？

看一个例子：

这个例子中相较于上一个例子只是多了一个WHERE子句来过滤数据，先是把所有prod_price>=10的先过滤掉在进行分组，这样分组的结果就会变少。

SELECT 子句顺序

下表是SELECT一些子句的用法与顺序。