MySQL中order by的学习使用
文章发布较早,内容可能过时,阅读注意甄别。
order by 用于对查询出来的数据进行排序操作,用两个例子可以很容易理解这个参数。
# 1,普通查询。
mysql> select * from groups;
+----+---------------------+---------------------+---------------------+----------+------------+
| id | created_at | updated_at | deleted_at | group_id | group_name |
+----+---------------------+---------------------+---------------------+----------+------------+
| 1 | NULL | 2020-06-16 22:07:02 | 2020-06-16 22:14:40 | 1 | 开发组 |
| 2 | 2020-06-15 23:37:25 | 2020-06-15 23:37:25 | NULL | 2 | 测试组 |
| 3 | 2020-06-16 22:17:07 | 2020-06-16 22:17:07 | NULL | 3 | 运营组 |
| 4 | 2020-06-16 22:17:15 | 2020-06-16 22:17:15 | NULL | 4 | 客服组 |
| 5 | 2020-06-16 22:17:26 | 2020-06-16 22:17:26 | NULL | 5 | OPS |
+----+---------------------+---------------------+---------------------+----------+------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
默认情况下,查出来的数据是按照主键 ID 正序排列的。
# 2,基于 ID 进行排序。
mysql> select * from groups order by id desc;
+----+---------------------+---------------------+---------------------+----------+------------+
| id | created_at | updated_at | deleted_at | group_id | group_name |
+----+---------------------+---------------------+---------------------+----------+------------+
| 5 | 2020-06-16 22:17:26 | 2020-06-16 22:17:26 | NULL | 5 | OPS |
| 4 | 2020-06-16 22:17:15 | 2020-06-16 22:17:15 | NULL | 4 | 客服组 |
| 3 | 2020-06-16 22:17:07 | 2020-06-16 22:17:07 | NULL | 3 | 运营组 |
| 2 | 2020-06-15 23:37:25 | 2020-06-15 23:37:25 | NULL | 2 | 测试组 |
| 1 | NULL | 2020-06-16 22:07:02 | 2020-06-16 22:14:40 | 1 | 开发组 |
+----+---------------------+---------------------+---------------------+----------+------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
注意其中的 desc
表示用倒序排列。
如果想要正序排列,则使用asc
:
mysql> select * from groups order by id asc;
+----+---------------------+---------------------+---------------------+----------+------------+
| id | created_at | updated_at | deleted_at | group_id | group_name |
+----+---------------------+---------------------+---------------------+----------+------------+
| 1 | NULL | 2020-06-16 22:07:02 | 2020-06-16 22:14:40 | 1 | 开发组 |
| 2 | 2020-06-15 23:37:25 | 2020-06-15 23:37:25 | NULL | 2 | 测试组 |
| 3 | 2020-06-16 22:17:07 | 2020-06-16 22:17:07 | NULL | 3 | 运营组 |
| 4 | 2020-06-16 22:17:15 | 2020-06-16 22:17:15 | NULL | 4 | 客服组 |
| 5 | 2020-06-16 22:17:26 | 2020-06-16 22:17:26 | NULL | 5 | OPS |
+----+---------------------+---------------------+---------------------+----------+------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
上次更新: 2024/07/04, 22:40:37