今天和大家聊聊数据查询中常使用到的两个查询,排序和分页查询。
| 声明:本文中被[]包含的表示可选项,|符号分开的表示可选其一。
一、详解排序查询(order by)
电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成。
排序语法:
select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc]...;
说明:
- 需要排序的字段跟在 order by 之后;
- asc | desc 表示排序的规则。asc:升序;desc:降序。默认为desc降序
- 支持多个字段进行排序,多字段之间用 逗号 隔开。
1、单字段排序
2、多字段排序
3、按别名排序
4、按函数排序
有个学生表(id:学号,birth:出生日期,name:姓名),如下:
需求:按照出生年份升序、编号升序,查询出编号、出生日期、出生年份、姓名,2种写法,如下:
说明:
- year()函数:输入日期函数,可以获取对应日期中的年份;
- 上面使用了两种方式排序,第一张是在order by后面使用了函数;第二种使用了别名排序
5、where之后进行排序
有订单数据如下:
需求:查询订单金额 >= 100的,按照订单金额降序排序,显示2列数据,列头:订单编号、订单金额,如下:
二、详解分页查询(limit)
limit用来限制select查询返回的行数,常用于分页等操作!
limit语法:
select 列 from 表名 limit [offset,] count;
说明:
- offset:表示 偏移量 ,通俗点讲就是跳过多少行,offset可以省略,默认为0,表示跳过多少行;范围:[0,+无穷大);
- count:跳过offset行之后开始取数据,取count行记录;范围:[0,+无穷大);
- limit中offset和count的值不能用表达式。
下面我们列一些常用的示例来加深理解。
1、获取前n行记录
select 列 from 表 limit 0,n;
或者
select 列 from 表 limit n;
示例:获取订单前两条记录
2、获取最大的一条记录
我们需要获取订单金额最大的一条记录,可以这么做:先按照金额降序,然后取第一条记录即可,如下:
3、获取排名第n到m的记录
我们需要先跳过n-1条记录,然后取m-n+1条记录,如下:
select 列 from 表 limit n-1,m-n+1;
如:我们想获取订单金额最高的3到5名的记录,我们需要跳过2条,然后获取3条记录,如下:
4、分页查询
开发过程中,分页是业务一个基础功能。所以我们经常会使用的到,分页一般有两个参数:
- page :表示第几页,从1开始,范围:[1,+无穷大);
- pageSize:每页显示多少条记录,范围:[1,+无穷大);
如:page=2,pageSize=10,表示获取第2页10条数据。
我们使用limit实现分页,语法如下:
select 列 from 表 limit (page - 1) * pageSize,pageSize;
需求:我们按照订单金额降序,每页显示2条,以此获取所有订单数据、第1页、第2页、第3页数据,如下:
三、常见的坑
1、limit中不能使用表达式
结论:limit后面只能跟明确的数字。
2、limit后面的2个数字不能为负数
3、排序分页存储的坑
(1)准备数据
(2)按照b升序,每页2条数据
下面的SQL以此为第1页、第2页、第3页、第4页和第5页的数据,如下:
上面有两个问题:
- 问题1:看一下第2个SQL和第3个SQL,分别是第2页和第3页的数据,结果出现了相同的数据,是不是懵逼了!!
- 整个表只有8条数据,怎么会出现第5页的数据呢?是不是又懵逼了!?
分析:主要是b字段存在相同的值,当排序过程中存在相同的值时,没有其他排序规则时,这下换成Mysql懵逼了,不知道如何排序了。
就像我们上学站队一样,按照身高排序,那身高一样的如何排序呢?身高一样的就乱排序了。
建议:排序中存在相同的值时,需要再指定一个排序规则,通过这种排序规则不存在二义性,比如上面可以再加上a降序,如下:
看上面的结果,分页数据都正常了,第五页也没有数据了。
总结:
- order by … [asc | desc] 用于对查询结果排序,asc:升序;desc:降序,asc|desc可以省略,默认为desc;
- limit用来限制查询结果返回的行数,有2个参数(offset,count),offset:表示跳过多少行,count:表示跳过offset行后去count行数据;
- limit中offset可以省略,默认值为0;
- limit中offset和count都必须大于等于0,offset范围:[0, +无穷大];count范围:[0, +无穷大];
- limit中offset和count的值不能用表达式;
- 分页排序时,排序不要有二义性,二义性情况下会导致分页结果乱序,可以在后面追加一个主键排序。