欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL必知必会 -- 排序检索数据 ORDER BY

程序员文章站 2022-04-04 07:51:08
我们使用SELECT SQL语句返回某个数据库表的单个列没有特定的顺序,如果我们想对获得的数据进行一个排序,就可以加上ORDER BY 这个参数。子句(clause) SQL语句由子句构成,有些子句是必需的,而有的是可选的。为了明确地排序用SELECT语句检索出的数据,可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字,据此对输出进行排序。MariaDB [test]> select * from linux;+-------+--------+------+| user...

我们使用SELECT SQL语句返回某个数据库表的单个列没有特定的顺序,如果我们想对获得的数据进行一个排序,就可以加上ORDER BY 这个参数。

子句(clause) SQL语句由子句构成,有些子句是必需的,而有的是可选的。
为了明确地排序用SELECT语句检索出的数据,可使用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)

排序方向
数据排序不限于升序排序(从A到Z)。这只是默认的排序顺序,还可以使用ORDER BY子句以降序(从Z到A)顺序排序。为了进行降序排序,必须指定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:
order by 位于 from 关键字之后;
limit 位于 order by 关键字之后。

本文地址:https://blog.csdn.net/thermal_life/article/details/107644733

相关标签: mysql必知必会