2021-04-13

&7、排序、分组、筛选

一、排序

1.升序：order by 排序字段 asc

mysql> select * from t_user order by age asc;
+------+--------+------+--------+
| idd  | name   | age  | tel    |
+------+--------+------+--------+
|    2 | lisi   |    8 | 112222 |
|    1 | xixi   |   20 | 123444 |
|    1 | hihi   |   23 | 111000 |
|    3 | wangwu |   23 | 112332 |
+------+--------+------+--------+

2.降序：order by 排序字段 desc

mysql> select * from t_user order by age desc;
+------+--------+------+--------+
| idd  | name   | age  | tel    |
+------+--------+------+--------+
|    1 | hihi   |   23 | 111000 |
|    3 | wangwu |   23 | 112332 |
|    1 | xixi   |   20 | 123444 |
|    2 | lisi   |    8 | 112222 |
+------+--------+------+--------+

二、分组（将内容归纳为一组）

1.group by:select 分组条件 from 表名 group by；

mysql> select * from t_user;
+------+--------+------+--------+
| idd  | name   | age  | tel    |
+------+--------+------+--------+
|    1 | hihi   |   23 | 111000 |
|    2 | lisi   |    8 | 112222 |
|    3 | wangwu |   23 | 112332 |
|    1 | xixi   |   20 | 123444 |
+------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> select age<22 from t_user group by age;
+--------+
| age<22 |
+--------+
|      1 |
|      1 |
|      0 |
+--------+
3 rows in set (0.00 sec)

三、筛选（分组之后才能筛选）

1.having：select 分组条件 from 表名 where 条件 group by gender having 筛选条件