mysql下聚合函数count、avg、max、min

1.count()

count()函数统计数据表中的行数，或根据查询结果统计列中包含的行数

使用方法：

• （1）count(*) 计算表中总的行数，不管表中某列有值或是空值
• （2）count(字段名) 计算指定列下总的行数，计算的时候将忽略空值

mysql> select * from customers;
+-------+----------+---------------------+---------+--------+-----------+-------------------+
| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email           |
+-------+----------+---------------------+---------+--------+-----------+-------------------+
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LMing@163.com     |
| 10002 | Stars    | 333 Fromage Lane    | Dalian  | 116000 | Zhangbo   | Jerry@hotmail.com |
| 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL              |
| 10004 | JOTO     | 829 Riverside Drive | Haikou  | 570000 | YangShan  | sam@hotmail.com   |
+-------+----------+---------------------+---------+--------+-----------+-------------------+

计算customers表中总的行数：

mysql> select count(*) as total from customers;
+-------+
| total |
+-------+
|     4 |
+-------+

查询多少人有电子邮箱

mysql> select count(c_email) as c_mail_num from customers;
+------------+
| c_mail_num |
+------------+
|          3 |
+------------+

#########################################################################

mysql> select * from orderitems;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 |      1 | a1   |       10 |       5.20 |
| 30001 |      2 | b2   |        3 |       7.60 |
| 30001 |      3 | bs1  |        5 |      11.20 |
| 30001 |      4 | bs2  |       15 |       9.20 |
| 30002 |      1 | b3   |        2 |      20.00 |
| 30003 |      1 | c0   |      100 |      10.00 |
| 30004 |      1 | o2   |       50 |       2.50 |
| 30005 |      1 | c0   |        5 |      10.00 |
| 30005 |      2 | b1   |       10 |       8.99 |
| 30005 |      3 | a2   |