sql 基础--mysql 5 （5）

10.汇总数据

　　聚集函数 avg平均值，count行数，max最大值，min最小值，sum之和

mysql> select msg,avg(msg) from pw_luck;
+-----+----------+
| msg | avg(msg) |
+-----+----------+
| 100 | 920.2000 |
+-----+----------+
1 row in set (0.00 sec)

mysql> select msg,sum(msg) from pw_luck;
+-----+----------+
| msg | sum(msg) |
+-----+----------+
| 100 |     9202 |
+-----+----------+
1 row in set (0.00 sec)

mysql> select sum(msg) from pw_luck;
+----------+
| sum(msg) |
+----------+
|     9202 |
+----------+
1 row in set (0.00 sec)

mysql> select count(msg) from pw_luck;
+------------+
| count(msg) |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)

　　聚集不同值

mysql> select * from pw_luck;
+-----+-----------+------+
| uid | name      | msg  |
+-----+-----------+------+
|   0 | Wilson    |  100 |
|   1 | zhangsan  |  100 |
|   2 | lisi      | 1001 |
|   3 | wang5     | 1001 |
|   7 | zhangsan7 | 1000 |
|   8 | zhangsan8 | 1000 |
|   9 | zhangsan9 | 1000 |
|  10 | wang5     | 1000 |
|  11 | wang5%    | 2000 |
|  12 | WANG5     | 1000 |
+-----+-----------+------+
10 rows in set (0.00 sec)

mysql> select avg(distinct msg) from pw_luck;
+-------------------+
| avg(distinct msg) |
+-------------------+
|         1025.2500 |
+-------------------+
1 row in set (0.02 sec)

mysql> select avg(msg) from pw_luck;
+----------+
| avg(msg) |
+----------+
| 920.2000 |
+----------+
1 row in set (0.00 sec)

　　组合聚集函数

mysql> select avg(msg),sum(msg) from pw_luck;
+----------+----------+
| avg(msg) | sum(msg) |
+----------+----------+
| 920.2000 |     9202 |
+----------+----------+
1 row in set (0.00 sec)

11.分组数据

　　group by column

mysql> select name,max(msg) from pw_luck group by name;
+-----------+----------+
| name      | max(msg) |
+-----------+----------+
| lisi      |     1001 |
| wang5     |     1001 |
| wang5%    |     2000 |
| Wilson    |      100 |
| zhangsan  |      100 |
| zhangsan7 |     1002 |
| zhangsan8 |     1000 |
| zhangsan9 |     1000 |
+-----------+----------+
8 rows in set (0.00 sec)

　　having 类似where 过滤分组

mysql> select name,max(msg) from pw_luck group by name having count(*)>2;
+-----------+----------+
| name      | max(msg) |
+-----------+----------+
| wang5     |     1001 |
| zhangsan7 |     1002 |
+-----------+----------+
2 rows in set (0.00 sec)

　　

mysql> select uid,name,max(msg) from pw_luck where uid>3 group by name having count(*)>2;
+-----+-----------+----------+
| uid | name      | max(msg) |
+-----+-----------+----------+
|  10 | wang5     |     1000 |
|   7 | zhangsan7 |     1002 |
+-----+-----------+----------+
2 rows in set (0.00 sec)

　　分组和排序

mysql> select uid,name,max(msg) from pw_luck where uid>3 group by name having count(*)>2 order by na
me desc;
+-----+-----------+----------+
| uid | name      | max(msg) |
+-----+-----------+----------+
|   7 | zhangsan7 |     1002 |
|  10 | wang5     |     1000 |
+-----+-----------+----------+
2 rows in set (0.00 sec)

　　select 语句的顺序  

　　　　select

　　　　from

　　　　where

　　　　group by

　　　　having

　　　　order by

　　　　limit

mysql> select uid,max(msg) from pw_luck where uid>2 group by name having count(*)>2 order by name li
mit 5;
+-----+----------+
| uid | max(msg) |
+-----+----------+
|   3 |     1001 |
|   7 |     1002 |
+-----+----------+
2 rows in set (0.00 sec)