mysql命令行 - 计数

mysql提供5种聚合函数(aggregate functions)：

• MIN(): 最小数
• MAX(): 最大数
• SUM(): 和数
• AVG(): 平均值
• COUNT():计算出现次数

在前面的文章里我们已经介绍了MIN()、MAX()、SUM()、AVG()的使用方法，在这一节里我们学习最后一个，

如何计算出现次数？

mysql使用COUNT()函数来计算某个查询结果的总数，例如：

select COUNT(*) from employee_data;
+----------+
| COUNT(*) |
+----------+
|       21 |
+----------+
1 row in set (0.00 sec)

上述例子计算出employee_data这个表里总共有多少条记录。COUNT(*)，星号这里代表所有数据。我们还可以给查询语句加入条件，然后计算出满足条件的结果的总数，例如：

select COUNT(*) from employee_data where title = 'Programmer';
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

上述例子计算出职务为Programmer的员工总数。

此外，COUNT还经常和GROUP BY 这个子句合用，用来求出每一组的记录。GROUP BY子句把同一组的数据归为一类，例如:

select title from employee_data GROUP BY title;
+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Customer Service Manager   |
| Finance Manager            |
| Marketing Executive        |
| Multimedia Programmer      |
| Programmer                 |
| Senior Marketing Executive |
| Senior Programmer          |
| Senior Web Designer        |
| System Administrator       |
| Web Designer               |
+----------------------------+
11 rows in set (0.01 sec)

上述例子把employee_data这里表里的所有职务类别显示出来。

如果我们在上述查询语句里加入COUNT，就可以把每一种职务的员工人数计算出来，例如：

select title, count(*) from employee_data GROUP BY title;
+----------------------------+----------+
| title                      | count(*) |
+----------------------------+----------+
| CEO                        |        1 |
| Customer Service Manager   |        1 |
| Finance Manager            |        1 |
| Marketing Executive        |        3 |
| Multimedia Programmer      |        3 |
| Programmer                 |        4 |
| Senior Marketing Executive |        1 |
| Senior Programmer          |        2 |
| Senior Web Designer        |        1 |
| System Administrator       |        2 |
| Web Designer               |        2 |
+----------------------------+----------+
11 rows in set (0.00 sec)

最后，如果我们想对上述查询结果进行排列，我们还可以加入以前学到的ORDER BY，如下：

select title, count(*) AS Number
from employee_data
GROUP BY title 
ORDER BY Number;
+----------------------------+--------+
| title                      | Number |
+----------------------------+--------+
| CEO                        |      1 |
| Customer Service Manager   |      1 |
| Finance Manager            |      1 |
| Senior Marketing Executive |      1 |
| Senior Web Designer        |      1 |
| Senior Programmer          |      2 |
| System Administrator       |      2 |
| Web Designer               |      2 |
| Marketing Executive        |      3 |
| Multimedia Programmer      |      3 |
| Programmer                 |      4 |
+----------------------------+--------+
11 rows in set (0.00 sec)