MySQL count()

简介

COUNT()函数用来统计表的行数，也就是统计记录行数，很好理解

官方的解释：

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value. If there are no matching rows, COUNT() returns 0.

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

COUNT(DISTINCT expr,[expr…])Returns a count of the number of rows with different non-NULL expr values.If there are no matching rows, COUNT(DISTINCT) returns 0.

COUNT(expr)统计并返回参数expr为非NULL值的总行数，COUNT(DISTINCT expr)返回的是参数expr为非NULL值且不相同的总行数

举个例子

CREATE TABLE `users` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `LoginName` varchar(50) DEFAULT NULL,
  `LoginPwd` varchar(16) DEFAULT NULL,
  `Name` varchar(16) DEFAULT NULL,
  `Address` varchar(16) DEFAULT NULL,
  `Phone` varchar(16) DEFAULT NULL,
  `Mail` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

#插入数据
mysql> select * from users;
+----+------------+----------+------+----------+-------------+---------------+
| Id | LoginName  | LoginPwd | Name | Address  | Phone       | Mail          |
+----+------------+----------+------+----------+-------------+---------------+
|  1 | bb1        | 123      | 张三 | 湖北武汉 | 13317190688 | [e-mail] |
|  2 | bb3        | 123      | 李四 | 湖北武汉 | 13317190688 | [e-mail] |
|  3 | jj4        | 123      | 张三 | 湖北武汉 | 13317190688 | [e-mail] |
|  4 | kobeBryant | 123456   | NULL | LA       | NULL        | NULL          |
|  5 | kobe       | 456      | NULL | NULL     | NULL        | NULL          |
|  6 | Jay        | NULL     | NULL | GXI      | NULL        | NULL          |
|  7 | jj4        | NULL     | NULL | NULL     | NULL        | NULL          |
+----+------------+----------+------+----------+-------------+---------------+
7 rows in set

查询，

mysql> SELECT COUNT(*),COUNT(1),COUNT(0),COUNT(-1), COUNT(LoginPwd),COUNT(Phone),COUNT(DISTINCT Phone) FROM users;
+----------+----------+----------+-----------+-----------------+--------------+------------------------+
| COUNT(*) | COUNT(1) | COUNT(0) | COUNT(-1) | COUNT(LoginPwd) | COUNT(Phone) | COUNT(DISTINCT Phone) |
+----------+----------+----------+-----------+-----------------+--------------+------------------------+
|        7 |        7 |        7 |         7 |               5 |            3 |                      1 |
+----------+----------+----------+-----------+-----------------+--------------+------------------------+
1 row in set

具体差异