mysql order by 负数_mysql学习笔记：九.排序和分页（order by、limit）

排序和分页(order by、limit)

排序查询(order by)

排序语法：

select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc];

需要排序的字段跟在 order by 之后；

asc|desc表示排序的规则，asc：升序，desc：降序，默认为asc；

支持多个字段进行排序，多字段排序之间用逗号隔开。

单字段排序

mysql> create table test14(a int,b varchar(10));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test14 values (10,'jack'),(8,'tom'),(5,'ready'),(100,'javacode');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test14;

+------+----------+

| a | b |

+------+----------+

| 10 | jack |

| 8 | tom |

| 5 | ready |

| 100 | javacode |

+------+----------+

4 rows in set (0.00 sec)

mysql> select * from test14 order by a;

+------+----------+

| a | b |

+------+----------+

| 5 | ready |

| 8 | tom |

| 10 | jack |

| 100 | javacode |

+------+----------+

4 rows in set (0.00 sec)

mysql> select * from test14 order by a asc;

+------+----------+

| a | b |

+------+----------+

| 5 | ready |

| 8 | tom |

| 10 | jack |

| 100 | javacode |

+------+----------+

4 rows in set (0.00 sec)

mysql> select * from test14 order by a desc;

+------+----------+

| a | b |

+------+----------+

| 100 | javacode |

| 10 | jack |

| 8 | tom |

| 5 | ready |

+------+----------+

4 rows in set (0.00 sec)

多字段排序

比如学生表，先按学生年龄降序，年龄相同时，再按学号升序，如下：

mysql> create table student(id int not null comment '学号' primary key,age tinyint not null comment '年龄',name varchar(16) comment '姓名');

Query OK, 0 rows affected (0.30 sec)

mysql> insert into student (id,age,name) values (1001,18,'周星驰'),(1005,20,'刘德华'),(1003,18,'张学友'),(1004,20,'张国荣'),(1010,19,'梁朝伟');

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from student;

+------+-----+-----------+

| id | age | name |

+------+-----+-----------+

| 1001 | 18 | 周星驰 |

| 1003 | 18 | 张学友 |

| 1004 | 20 | 张国荣 |

| 1005 | 20 | 刘德华 |

| 1010 | 19 | 梁朝伟 |

+------+-----+-----------+

5 rows in set (0.01 sec)

mysql> select * from student order by age desc,id asc;

+------+-----+-----------+

| id | age | name |

+------+-----+-----------+

| 1004 | 20 | 张国荣 |

| 1005 | 20 | 刘德华 |

| 1010 | 19 | 梁朝伟 |

| 1001 | 18 | 周星驰 |

| 1003 | 18 | 张学友 |

+------+-----+-----------+

5 rows in set (0.00 sec)

按别名排序

mysql> select * from student;

+------+-----+-----------+

| id | age | name |

+------+-----+-----------+

| 1001 | 18 | 周星驰 |

| 1003 | 18 | 张学友 |

| 1004 | 20 | 张国荣 |

| 1005 | 20 | 刘德华 |

| 1010 | 19 | 梁朝伟 |

+------+-----+-----------+

5 rows in set (0.00 sec)

mysql> select age '年龄',id as '学号' from student order by `年龄` asc,`学号` desc;

+--------+--------+

| 年龄 | 学号 |

+--------+--------+

| 18 | 1003 |

| 18 | 1001 |

| 19 | 1010 |

| 20 | 1005 |

| 20 | 1004 |

+--------+--------+

5 rows in set (0.00 sec)

按函数排序

有学生表(id：编号，birth：出生日期，name：姓名)，如下：

mysql> drop table if exists student;

Query OK, 0 rows affected (0.00 sec)

mysql> create table student(

-> id int(11) not null comment '学号',

-> birth date not null comment '出生日期',

-> name varchar(16) default null comment '姓名',

-> primary key(id)

-> );

Query OK, 0 rows affected (0.00 sec)

mysql> insert into student (id,birth,name) values (1001,'1990-10-10','周星驰'),(1005,'1960-03-01','刘德华'),(1003,'1960-08-16','张学友'),(1004,'1968-07-01','张国荣'),(1010,'1962-05-16','梁朝伟');

mysql> select * from student;

+------+------------+-----------+

| id | birth | name |

+------+------------+-----------+

| 1001 | 1990-10-10 | 周星驰 |

| 1003 | 1960-08-16 | 张学友 |

| 1004 | 1968-07-01 | 张国荣 |

| 1005 | 1960-03-01 | 刘德华 |

| 1010 | 1962-05-16 | 梁朝伟 |

+------+------------+-----------+

5 rows in set (0.01 sec)

需求：按照出生年份升序、编号升序，查询出编号、出生日期、出生年份、姓名，2种写法如下：

#用year函数取出年份

mysql> SELECT id 编号,birth 出生日期,year(birth) 出生年份,name 姓名 from student ORDER BY

year(birth) asc,id asc;

+--------+--------------+--------------+-----------+

| 编号 | 出生日期 | 出生年份 | 姓名 |

+--------+--------------+--------------+-----------+

| 1003 | 1960-08-16 | 1960 | 张学友 |

| 1005 | 1960-03-01 | 1960 | 刘德华 |

| 1010 | 1962-05-16 | 1962 | 梁朝伟 |

| 1004 | 1968-07-01 | 1968 | 张国荣 |

| 1001 | 1990-10-10 | 1990 | 周星驰 |

+--------+--------------+--------------+-----------+

5 rows in set (0.05 sec)

mysql> SELECT id 编号,birth 出生日期,year(birth) 出生年份,name 姓名 from student ORDER BY 出生年份 asc,id asc;

+--------+--------------+--------------+-----------+

| 编号 | 出生日期 | 出生年份 | 姓名 |

+--------+--------------+--------------+-----------+

| 1003 | 1960-08-16 | 1960 | 张学友 |

| 1005 | 1960-03-01 | 1960 | 刘德华 |

| 1010 | 1962-05-16 | 1962 | 梁朝伟 |

| 1004 | 1968-07-01 | 1968 | 张国荣 |

| 1001 | 1990-10-10 | 1990 | 周星驰 |

+--------+--------------+--------------+-----------+

5 rows in set (0.00 sec)

说明：

year函数：属于日期函数，可以获取对应日期中的年份

上面使用了2种方式排序，第一种是在order by中使用了函数，第二种是使用了别名排序

where之后排序

某个订单表数据如下：

mysql> create table t_order(

-> id int not null auto_increment comment '订单编号',

-> price decimal(10,2) not null default 0 comment '订单金额',

-> primary key(id)

-> )comment '订单表';

Query OK, 0 rows affected (0.28 sec)

mysql> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from t_order;

+----+--------+

| id | price |

+----+--------+

| 1 | 88.95 |

| 2 | 100.68 |

| 3 | 500.00 |

| 4 | 300.00 |

| 5 | 20.88 |

| 6 | 200.50 |

+----+--------+

6 rows in set (0.00 sec)

需求：查询订单金额>=100的，按照订单金额降序排序，显示2列数据，列头：订单编号、订单金额，

如下：

mysql> select a.id `订单编号`,a.pricee `订单金额` from _order a where a.price>=100 order by a.price desc;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 3 | 500.00 |

| 4 | 300.00 |

| 6 | 200.50 |

| 2 | 100.68 |

+--------------+--------------+

4 rows in set (0.00 sec)

mysql> select a.id `订单编号`,a.price `订单金额` from t_order a where price>=100 order by price desc;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 3 | 500.00 |

| 4 | 300.00 |

| 6 | 200.50 |

| 2 | 100.68 |

+--------------+--------------+

4 rows in set (0.00 sec)

limit

limit用来限制select查询返回的行数，常用于分页等操作。

语法：

select 列 from 表 limit [offset,] count;

说明：

offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)

count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)

limit中offset和count的值不能用表达式

示例：

获取前n行记录

select 列 from 表 limit 0,n;

或者

select 列 from 表 limit n;

示例，获取订单的前2条记录，如下：

mysql> create table t_order(

-> id int not null auto_increment comment '订单编号',

-> price decimal(10,2) not null default 0 comment '订单金额',

-> primary key(id)

-> )comment '订单表';

Query OK, 0 rows affected (0.28 sec)

mysql> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from t_order;

+----+--------+

| id | price |

+----+--------+

| 1 | 88.95 |

| 2 | 100.68 |

| 3 | 500.00 |

| 4 | 300.00 |

| 5 | 20.88 |

| 6 | 200.50 |

+----+--------+

6 rows in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a limit 2;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 1 | 88.95 |

| 2 | 100.68 |

+--------------+--------------+

2 rows in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a limit 1,2;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 2 | 100.68 |

| 3 | 500.00 |

+--------------+--------------+

2 rows in set (0.00 sec)

获取最大或者最小的一条记录

先进行升序或者降序排序，然后去第一条记录

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 3 | 500.00 |

| 4 | 300.00 |

| 6 | 200.50 |

| 2 | 100.68 |

| 1 | 88.95 |

| 5 | 20.88 |

+--------------+--------------+

6 rows in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 1;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 3 | 500.00 |

+--------------+--------------+

1 row in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price limit 1; +--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 5 | 20.88 |

+--------------+--------------+

1 row in set (0.00 sec)

获取第n到第m的记录

如：我们想获取订单金额最高的3到5名的记录，我们需要跳过2条，然后获取3条记录，如下：

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 3 | 500.00 |

| 4 | 300.00 |

| 6 | 200.50 |

| 2 | 100.68 |

| 1 | 88.95 |

| 5 | 20.88 |

+--------------+--------------+

6 rows in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 2,3;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 6 | 200.50 |

| 2 | 100.68 |

| 1 | 88.95 |

+--------------+--------------+

3 rows in set (0.00 sec)

分页查询

在开发过程中我们经常会使用分页，核心技术是使用limit进行数据的读取。

不同数据库分页的方法不同，mysql使用limit

分页一般有2个参数：

page：表示是当前第几页，从1开始，范围[1,+∞)

pageSize：每页显示多少条记录，范围[1,+∞)

如：page = 2，pageSize = 10，表示获取第2页10条数据。

使用limit实现分页，语法如下：

select 列 from 表名 limit (page - 1) * pageSize,pageSize;

比如：

查询第1条到第10条的数据的sql是：select * from table limit 0,10; ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

查询第10条到第20条的数据的sql是：select * from table limit 10,20; ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

查询第20条到第30条的数据的sql是：select * from table limit 20,30; ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

需求：我们按照订单金额降序，每页显示2条，依次获取所有订单数据、第1页、第2页、第3页数据，如下：

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 3 | 500.00 |

| 4 | 300.00 |

| 6 | 200.50 |

| 2 | 100.68 |

| 1 | 88.95 |

| 5 | 20.88 |

+--------------+--------------+

6 rows in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 0,2;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 3 | 500.00 |

| 4 | 300.00 |

+--------------+--------------+

2 rows in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 2,2;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 6 | 200.50 |

| 2 | 100.68 |

+--------------+--------------+

2 rows in set (0.00 sec)

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 4,2;

+--------------+--------------+

| 订单编号 | 订单金额 |

+--------------+--------------+

| 1 | 88.95 |

| 5 | 20.88 |

+--------------+--------------+

2 rows in set (0.00 sec)

注意：

limit后面不能使用表达式

limit后面不能跟负数，因为范围为[0,+∞)

分页排序数据重复问题

mysql> insert into test1 (b) values (1),(2),(3),(4),(2),(2),(2),(2);

Query OK, 8 rows affected (0.02 sec)

Records: 8 Duplicates: 0 Warnings: 0

mysql> select * from test1 order by b asc;

+---+---+

| a | b |

+---+---+

| 1 | 1 |

| 2 | 2 |

| 5 | 2 |

| 6 | 2 |

| 7 | 2 |

| 8 | 2 |

| 3 | 3 |

| 4 | 4 |

+---+---+

8 rows in set (0.00 sec)

mysql>

mysql>

mysql>

mysql> select * from test1 order by b asc limit 0,2;

+---+---+

| a | b |

+---+---+

| 1 | 1 |

| 2 | 2 |

+---+---+

2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc limit 2,2;

+---+---+

| a | b |

+---+---+

| 8 | 2 |

| 6 | 2 |

+---+---+

2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc limit 4,2;

+---+---+

| a | b |

+---+---+

| 6 | 2 |

| 7 | 2 |

+---+---+

2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc limit 6,2;

+---+---+

| a | b |

+---+---+

| 3 | 3 |

| 4 | 4 |

+---+---+

2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc limit 7,2;

+---+---+

| a | b |

+---+---+

| 4 | 4 |

+---+---+

1 row in set (0.00 sec)

解决方案：需要增加唯一索引的排序(比如主键)

例如：

mysql> select * from test1 order by b asc,a asc limit 0,2;

+---+---+

| a | b |

+---+---+

| 1 | 1 |

| 2 | 2 |

+---+---+

2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a asc limit 2,2;

+---+---+

| a | b |

+---+---+

| 5 | 2 |

| 6 | 2 |

+---+---+

2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a asc limit 4,2;

+---+---+

| a | b |

+---+---+

| 7 | 2 |

| 8 | 2 |

+---+---+

2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a asc limit 6,2;

+---+---+

| a | b |

+---+---+

| 3 | 3 |

| 4 | 4 |

+---+---+

2 rows in set (0.00 sec)

总结

order by ... [asc|desc]用于对查询结果排序，asc：升序，desc：降序，asc|desc可以省略，默认为asc

limit用来限制查询结果返回的行数，有2个参数(offset，count)，offset：表示跳过多少行，count：表示跳过offset行之后取count行

limit中offset可以省略，默认值为0

limit中offset 和 count都必须大于等于0

limit中offset和count的值不能用表达式

分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序