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. 多字段排序

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

使用score、birth字段来排序

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;

查询结果

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.避免踩坑

limit后面不能跟表达式
# 报错，limit后面不能跟表达式
select * from student where limit 1,4+1;
这种写法是会报错，limit后面只能跟具体的数字
limit后面不能为负数
# 报错，limit后面不能跟负数
select * from student where limit -1,-4;

排序分页问题

排序最好用两个字段进行排序，如果一个字段中可能出现同等的值，那么就会造成排序分页冗余的问题，可以指定多个字段进行排序，保证其排序结果的唯一性！