SQL 入门教程

pedrogao · 更新于 2020-03-19

SQL Order By

2. 前言

``````DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
age int
);
INSERT INTO imooc_user(username,age) VALUES('pedro',23), ('pedro',17), ('mike',18), ('jerry',28);
``````

3. 单字段排序

Order By 多用于根据单个字段进行排序，即单字段排序。

``````SELECT [col] FROM [table_name] ORDER BY [col] [DESC|ASC];
``````

3.1 例1 升序排序 age 字段

``````SELECT username,age FROM imooc_user ORDER BY age;
``````

``````+----------+-----+
+----------+-----+
| mike     | 18  |
| pedro    | 23  |
| jerry    | 28  |
+----------+-----+
``````

``````SELECT username,age FROM imooc_user ORDER BY age ASC;
``````

3.2 例2 降序排序 age 字段

``````SELECT username,age FROM imooc_user ORDER BY age DESC;
``````

``````+----------+-----+
+----------+-----+
| jerry    | 28  |
| pedro    | 23  |
| mike     | 18  |
+----------+-----+
``````

4. 多字段排序

Order By 还可用于多字段排序。

``````SELECT [col] FROM [table_name] ORDER BY [col1] [DESC|ASC],[col2] [DESC|ASC];
``````

``````SELECT username,age FROM imooc_user ORDER BY username, age;
``````

``````+----------+-----+
+----------+-----+
| jerry    | 28  |
| mike     | 18  |
| pedro    | 23  |
+----------+-----+
``````

``````SELECT username,age FROM imooc_user ORDER BY username ASC, age ASC;
``````

``````SELECT username,age FROM imooc_user ORDER BY username DESC, age ASC;
``````

``````+----------+-----+
+----------+-----+
| pedro    | 23  |
| mike     | 18  |
| jerry    | 28  |
+----------+-----+
``````

5. 小结

• Order By 可以有效让查询结果有序，这对于数据展示来说至关重要。
• Order By 对于多字段的排序支持虽然强大，但是很消耗性能。
• Order By 对于字符串的排序是按位（每个字符）进行比较排序的，十分耗时，一般情况下不推荐使用。