展开

文章目录

修改历史

历史修改版本

  1. 2021-03-11 18:00:35
  2. 2021-03-06 11:35:02

MySQL查询之排序分页

2021-03-06 11:10:51 MySQL 167

简介

对于每一个CRUD程序员来说,在使用MySQL数据库获取数据的过程中,肯定是避免不了排序以及分页操作,同时在实际应用过程中中也会遇到一些坑,本文说明记录一下在MySQL中排序、分页的基本操作以及可能会遇到的坑。

1.排序order by的基本用法

在MySQL中排序的基本语法如下所示

# 单字段
SELECT column1, column2 from table order by column1 desc;

# 多字段
SELECT column1, column2 from table order by column1, column2 desc;

通过下面的语句新建一张student表,并插入一些数据

CREATE TABLE student(
    id int primary key auto_increment,
    name varchar(128) default '' not null ,
    score integer default 0 not null ,
    birth date not null 
)

插入一些测试数据

INSERT INTO student (name, score, birth) VALUES
('张三', 89, '2005-08-12'),
('李四', 78, '2002-11-03'),
('王五', 89, '2004-09-11'),
('赵六', 68, '2005-08-24'),
('赵七', 59, '2001-04-22'),
('钱八', 95, '2008-08-31');

a.单字段排序

通过分数对查询结果进行排序,升序排列

select name, score, birth from student order by score;

查询结果

分数单字段

通过分数对查询结果进行排序,降序排列

select name, score, birth from student order by score desc ;

查询结果

分数降序

b. 多字段排序

上面的数据中我们可以看到分数有相同的情况,如果某个字段存在相同的数据,那么可以用其他字段作为第二个、第三个等次要条件来排序

使用scorebirth字段来排序

select name, score, birth from student order by score desc , birth desc;

 查询结果

多字段排序

多字段我们可以指定每个字段的排序方式(升序或者降序)!

 c.按函数排序

除了字段之外,我们还可以使用内置的函数来排序,比如通过birth字段的年份来排序

select name, score, year(birth), birth from student order by year(birth);

查询结果

2.分页limit的用法

limit顾名思义,是用来显示查询返回的长度,其语法如下所示

SELECT column1, column2 from table limit offset, count;
  • offset:表示偏移量,就是跳过多少行数据,可以省略默认为0
  • count:表示去多少行数据,如果设置了offset则跳过offset行数据后开始取数据

a.获取前n行记录

我们想获取成绩排名前面的学生

首先我们需要对数据进行降序排列,然后通过limit来获取前3排名的数据

select name, score from student order by score desc limit 3;

查询结果

limit前三

b.获取n-m行记录

如果想获取成绩排名2-4名的学生,通过limit怎么实现呢?

首先我们要跳过第一名就是将offset设置为1,同时要取2-4名count设为3;

  • offset: n-1
  • count: m-n+1
select name, score from student order by score desc limit 1, 3;

查询结果

3.分页查询

在实际开发过程中我们经常会遇到分页查询的功能,一般的都通过limit来实现,当然还有一些邪道,这里就不说。

上面插入的数据量太小了,我们需要重新插入一下测试数据

insert into student (name, score, birth) values
('李明', FLOOR(RAND()*100), '2006-06-06'),
('李红', FLOOR(RAND()*100), '2001-01-23'),
('王宝静', FLOOR(RAND()*100), '2006-04-18'),
('李耀武', FLOOR(RAND()*100), '2003-11-11'),
('宋青书', FLOOR(RAND()*100), '2006-12-08'),
('任我行', FLOOR(RAND()*100), '2006-08-22'),
('裘千仞', FLOOR(RAND()*100), '2006-01-24'),
('小龙女', FLOOR(RAND()*100), '2006-07-26')

上面使用了生成随机数的函数,大家可以自行百度一下。

执行select语句,看看数据是否插入成功

比如我们按照成绩降序,需要查询第2页,每页5条数据,怎么实现呢?

  • 第1页 5条数据 1-5,offset=0 count=5
  • 第2页 5条数据 6-10 offset=5 count=5
  • 第3页 5条数据 11-15 offse=10 count =5
  • ...
  • 第n页 5条数据 (n-1)*5 - 5n offset=(n-1)*5 count=5
select name, score, birth from student order by score desc limit 5, 5;

查询结果

 

4.避免踩坑

  1. limit后面不能跟表达式
    # 报错,limit后面不能跟表达式
    select * from student where limit 1,4+1;

    这种写法是会报错,limit后面只能跟具体的数字

  2. limit后面不能为负数

    # 报错,limit后面不能跟负数
    select * from student where limit -1,-4;
  3. 排序分页问题
    排序最好用两个字段进行排序,如果一个字段中可能出现同等的值,那么就会造成排序分页冗余的问题,可以指定多个字段进行排序,保证其排序结果的唯一性!
     

 

当前共有0条评论