本文共 4606 字,大约阅读时间需要 15 分钟。
我们使用SELECT SQL语句返回某个数据库表的单个列没有特定的顺序,如果我们想对获得的数据进行一个排序,就可以加上ORDER BY 这个参数。
子句(clause) SQL语句由子句
构成,有些子句是必需的,而有的是可选的。
ORDER BY
子句。ORDER BY子句取一个或多个
列的名字,据此对输出进行排序。 MariaDB [test]> select * from linux;+-------+--------+------+| user | passwd | sex |+-------+--------+------+| user1 | 111 | boy || user2 | 222 | girl || user3 | 333 | boy || user4 | 444 | boy || user5 | 555 | boy || a | passa | boy || 1 | pass1 | girl |+-------+--------+------+7 rows in set (0.00 sec)MariaDB [test]> select * from linux order by user;+-------+--------+------+| user | passwd | sex |+-------+--------+------+| 1 | pass1 | girl || a | passa | boy || user1 | 111 | boy || user2 | 222 | girl | /按照user排序,数字在前,字母在后| user3 | 333 | boy || user4 | 444 | boy || user5 | 555 | boy |+-------+--------+------+7 rows in set (0.00 sec)MariaDB [test]> select * from linux order by user,sex;+-------+--------+------+| user | passwd | sex |+-------+--------+------+| 1 | pass1 | girl || a | passa | boy || user1 | 111 | boy | /按多个列排序| user2 | 222 | girl || user3 | 333 | boy || user4 | 444 | boy || user5 | 555 | boy |+-------+--------+------+7 rows in set (0.00 sec)MariaDB [test]> insert into linux value ('user1','pass1','girl');Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into linux value ('user1','pass1','girl');Query OK, 1 row affected (0.00 sec)MariaDB [test]> select * from linux order by user,sex;+-------+--------+------+| user | passwd | sex |+-------+--------+------+| 1 | pass1 | girl || a | passa | boy || user1 | 111 | boy || user1 | pass1 | girl || user2 | 222 | girl | /按多个列排序时,首先按user排序,然后在每个user中再按sex排序,适用于有相同的情况| user3 | 333 | boy || user4 | 444 | boy || user5 | 555 | boy |+-------+--------+------+8 rows in set (0.01 sec)
排序方向
DESC关
键字。 MariaDB [test]> select * from linux order by user DESC,sex;+-------+--------+------+| user | passwd | sex |+-------+--------+------+| user5 | 555 | boy || user4 | 444 | boy || user3 | 333 | boy || user2 | 222 | girl || user1 | 111 | boy || user1 | pass1 | girl || a | passa | boy || 1 | pass1 | girl |+-------+--------+------+8 rows in set (0.00 sec)MariaDB [test]> select * from linux order by user,sex DESC;+-------+--------+------+| user | passwd | sex |+-------+--------+------+| 1 | pass1 | girl || a | passa | boy || user1 | pass1 | girl || user1 | 111 | boy || user2 | 222 | girl || user3 | 333 | boy || user4 | 444 | boy || user5 | 555 | boy |+-------+--------+------+8 rows in set (0.00 sec)MariaDB [test]> select * from linux order by user DESC,sex DESC;+-------+--------+------+| user | passwd | sex |+-------+--------+------+| user5 | 555 | boy || user4 | 444 | boy || user3 | 333 | boy || user2 | 222 | girl || user1 | pass1 | girl || user1 | 111 | boy || a | passa | boy || 1 | pass1 | girl |+-------+--------+------+8 rows in set (0.00 sec)
可见 DESC 关键字在那个条件上,就会对那一列倒叙排列,其他的列仍按照默认排列。
如果想在多个列上都进行降序排序,必须对每个列指定DESC关键字。
与DESC相反的关键字是ASC(ASCENDING)
,在升序排序时可以指定它。但实际上升序是默认的
在字典(dictionary)排序顺序中,A被视为与a相同,这是MySQL(和大多数数据库管理系统)的默认行为。但是,许多数据库管理员能够在需要时改变这种行为(如果你的数据库包含大量外语字符,可能必须这样做)。
ORDER BY和LIMIT的组合
MariaDB [test]> select * from linux;+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| user1 | 111 | boy | 18 || user2 | 222 | girl | 23 || user3 | 333 | boy | 12 || user4 | 444 | boy | 22 || user5 | 555 | boy | 30 || a | passa | boy | 22 || 1 | pass1 | girl | 30 || user1 | pass1 | girl | 18 |+-------+--------+------+------+8 rows in set (0.00 sec)MariaDB [test]> select * from linux order by sex,age DESC;+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| user5 | 555 | boy | 30 || user4 | 444 | boy | 22 || a | passa | boy | 22 || user1 | 111 | boy | 18 || user3 | 333 | boy | 12 || 1 | pass1 | girl | 30 || user2 | 222 | girl | 23 || user1 | pass1 | girl | 18 |+-------+--------+------+------+8 rows in set (0.00 sec)MariaDB [test]> select * from linux order by sex,age DESC limit 1;+-------+--------+-----+------+| user | passwd | sex | age |+-------+--------+-----+------+| user5 | 555 | boy | 30 |+-------+--------+-----+------+1 row in set (0.00 sec)
就可以通过这种方式找出男生中年龄最大的了。
TIP:
转载地址:http://quxli.baihongyu.com/