MariaDB Order By子句

在MariaDB數(shù)據(jù)庫(kù)中，ORDER BY子句用于按升序或降序?qū)Y(jié)果集中的記錄進(jìn)行排序。

語(yǔ)法：

SELECT expressions  
FROM tables  
[WHERE conditions]  
ORDER BY expression [ ASC | DESC ];

注意：可以對(duì)結(jié)果進(jìn)行排序而不使用ASC/DESC屬性。 默認(rèn)情況下，結(jié)果將按升序(ASC)排序。

1. ORDER BY子句不使用ASC/DESC屬性

在這個(gè)示例中，使用具有以下數(shù)據(jù)的students表：

MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          1 | Maxsu        | Haikou          | 2017-01-07     |
|          3 | JMaster      | Beijing         | 2016-05-07     |
|          4 | Mahesh       | Guangzhou       | 2016-06-07     |
|          5 | Kobe         | Shanghai        | 2016-02-07     |
|          6 | Blaba        | Shengzhen       | 2016-08-07     |
|          7 | Maxsu        | Sanya           | 2017-08-08     |
+------------+--------------+-----------------+----------------+
6 rows in set (0.00 sec)

示例

SELECT * FROM students  
WHERE student_name LIKE '%Ma%'  
ORDER BY student_id;

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

MariaDB [testdb]> SELECT * FROM students
    -> WHERE student_name LIKE '%Ma%'
    -> ORDER BY student_id;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          1 | Maxsu        | Haikou          | 2017-01-07     |
|          3 | JMaster      | Beijing         | 2016-05-07     |
|          4 | Mahesh       | Guangzhou       | 2016-06-07     |
|          7 | Maxsu        | Sanya           | 2017-08-08     |
+------------+--------------+-----------------+----------------+
4 rows in set (0.00 sec)

在上面結(jié)果集中，可以看到是按student_id字段從小到大(不指定ASC或DESC時(shí)，默認(rèn)使用ASC)來(lái)排序的。

2. ORDER BY子句使用DESC屬性

示例

SELECT * FROM students  
WHERE student_name LIKE '%Ma%'  
ORDER BY student_id DESC;

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

MariaDB [testdb]> SELECT * FROM students
    -> WHERE student_name LIKE '%Ma%'
    -> ORDER BY student_id DESC;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          7 | Maxsu        | Sanya           | 2017-08-08     |
|          4 | Mahesh       | Guangzhou       | 2016-06-07     |
|          3 | JMaster      | Beijing         | 2016-05-07     |
|          1 | Maxsu        | Haikou          | 2017-01-07     |
+------------+--------------+-----------------+----------------+
4 rows in set (0.00 sec)

在上面結(jié)果集中，可以看到是按student_id字段從大到小(指定DESC)來(lái)排序的。

3. 按多列排序

假設(shè)students表中有兩個(gè)人的名字是：Maxsu，我們希望先按student_name升序排序，在列的值相同時(shí)，再按student_id降序排序，參考以下查詢語(yǔ)句 -

SELECT * FROM students  
WHERE student_name LIKE '%Ma%'  
ORDER BY student_name ASC, student_id DESC;

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -