表的查询（二）

1.汇总数据

1.AVG(）函数

返回平均数：

如下，计算：

mysql> SELECT AVG(score) AS avg_score FROM students;
+-----------+
| avg_score |
+-----------+
|   41.2500 |
+-----------+
1 row in set (0.00 sec)

2.COUNT()函数

COUNT（）函数进行计数，可以利用该函数确定表中行的数目或符合特定条件的行的数目。

两种使用方式：

• COUNT（*）对表中行的数目进行计数。
• COUNT（列名）对特定列中具有值的行进行计数，忽略NULL值。

如下查询以M开头的记录数目：

mysql> select COUNT(*) AS COUNTS from students where name REGEXP '^M';
+--------+
| COUNTS |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

3.MAX（）和MIN（）函数

如其字面意思，取最大值和最小值的意思。

4.SUM（）

计算总和：
如下，我们计算分数总和：

mysql> select SUM(score) AS he from students;
+------+
| he   |
+------+
|  330 |
+------+
1 row in set (0.00 sec)

5.聚集不同值

上面我们检索统计的都可能包含相同值（比如可能同时存在2个90分），
如果我们想检索不同的值的记录的个数呢？

如下，原表：

mysql> select * from students;
+-------------+-----+-------+
| name        | sex | score |
+-------------+-----+-------+
| xiaoming    | m   |   100 |
| a           | m   |    20 |
| b           | m   |    50 |
| b           | m   |    80 |
| fishab      | m   |    20 |
| fishcd      | m   |    20 |
| Michle      | m   |    20 |
| MichleJason | m   |    20 |
+-------------+-----+-------+
8 rows in set (0.00 sec)

之前我们可以看到总和为：

mysql> select SUM(score) AS he from students;
+------+
| he   |
+------+
|  330 |
+------+
1 row in set (0.00 sec)

我们可以看到有很多20分，现在我们将20分只是计算一次：

mysql> select SUM(DISTINCT score) AS another_sum from students;
+-------------+
| another_sum |
+-------------+
|         250 |
+-------------+
1 row in set (0.06 sec)

上面用到了DISTINCT参数，这里我们也可以体会到，之前我们所有的检索其实是使用的ALL参数，只是我们将其省略，而系统默认采用ALL参数而已。

2.分组数据

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

例如，对于下表：

mysql> select * from students;
+-------------+-----+-------+
| name        | sex | score |
+-------------+-----+-------+
| xiaoming    | m   |   100 |
| a           | m   |    20 |
| b           | m   |    50 |
| b           | m   |    80 |
| fishab      | m   |    20 |
| fishcd      | m   |    20 |
| Michle      | m   |    20 |
| MichleJason | m   |    20 |
| xbox        | f   |   100 |
| tbox        | f   |   100 |
+-------------+-----+-------+
10 rows in set (0.00 sec)

我们按照男女分组统计人数：（男：m，女：f）

mysql> select sex,COUNT(*) from students GROUP BY sex;
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
| f   |        2 |
| m   |        8 |
+-----+----------+
2 rows in set (0.00 sec)

我们需要注意的是，NULL值也可以作为一个单独的分组返回。
另外,GROUP BY 语句必须出现在WHERE 子句之后，ORDER BY子句之前。
如下,统计非满分学生个数：

mysql> select sex,COUNT(*) from students WHERE score<100 GROUP BY sex;
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
| m   |        7 |
+-----+----------+
1 row in set (0.04 sec)

下面我们来讲一下分组过滤问题。

前面我们讲数据过滤的时候，都是用的WHERE子句，然而，WHERE子句只是用来过滤列，对于分组，我们需要使用HAVING。

mysql> select * from students;
+-------------+-----+-------+
| name        | sex | score |
+-------------+-----+-------+
| xiaoming    | m   |   100 |
| a           | m   |    20 |
| b           | m   |    50 |
| b           | m   |    80 |
| fishab      | m   |    20 |
| fishcd      | m   |    20 |
| Michle      | m   |    20 |
| MichleJason | m   |    20 |
| xbox        | f   |   100 |
| tbox        | f   |   100 |
| animal      | a   |   100 |
| atbox       | a   |   100 |
+-------------+-----+-------+
12 rows in set (0.00 sec)

对于上表，我们假定a代表动物，下面我们来检索个数：

mysql> select sex,COUNT(*)  from students GROUP BY sex;
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
| a   |        2 |
| f   |        2 |
| m   |        8 |
+-----+----------+
3 rows in set (0.00 sec)

如果将动物分组去除：

mysql> select sex,COUNT(*)  from students GROUP BY sex HAVING sex<>'a';
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
| f   |        2 |
| m   |        8 |
+-----+----------+
2 rows in set (0.00 sec)

WHERE和HAVING的区别

• WHERE是用来过滤列，而HAVING 是用来过滤分组。

最后我们来总结一下SELECT子句顺序：

子句	说明	是否必须使用
select	要返回的列或者表达式	是
from	后面跟要检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
Having	组级过滤	否
Order By	输出排序顺序	否