12.笔记 MySQL学习——聚集函数

12.笔记 MySQL学习——聚集函数

           MYSQL能够对大量原始数据进行归纳和统计。

           可以使用DISTINCT关键字清除查询结果里重复出现的行。

mysql> select distinct state from president orderby state;

+-------+

| state |

+-------+

| AR   |

| CA   |

| CT   |

| GA   |

| HI   |

| IA   |

| IL   |

| KY   |

| MA   |

| MO   |

| NC   |

| NE   |

| NH   |

| NJ   |

| NY   |

| OH   |

| PA   |

| SC   |

| TX   |

| VA   |

| VT   |

+-------+

21 rows in set (0.00 sec)

使用COUNT函数计算选取到行数。

mysql> select count(*) from member;

+----------+

| count(*) |

+----------+

|      102 |

+----------+

1 row in set (0.00 sec)

COUNT列名只会统计所有非NULL值的数目,如下:

mysql> selectcount(*),count(email),count(expiration) from member;

+----------+--------------+-------------------+

| count(*) | count(email) |count(expiration) |

+----------+--------------+-------------------+

|     102 |           80 |                96 |

+----------+--------------+-------------------+

1 row in set (0.00 sec)

mysql> select state,count(*) as count frompresident group by state having count > 1 order by count DESC;

+-------+-------+

| state | count |

+-------+-------+

| VA   |     8 |

| OH   |     7 |

| MA   |     4 |

| NY   |     4 |

| NC   |     2 |

| VT   |     2 |

| TX   |     2 |

+-------+-------+

7 rows in set (0.00 sec)

相关文章

相关标签/搜索