5.mysql 基础之排序和分页

数据准备：创建表

create table emploees
(
id int,
name varchar(20),
gender varchar(100),
saraly double(10,2),
department_id int,
manger_man  varchar(10)
)

数据准备 ：插入数据

insert into emploees values(101,'tom','m',5000,20,'zhangsan');
insert into emploees values(102,'tom1','m',6000,30,'zhangsan');
insert into emploees values(103,'tom2','m',7000,40,'zhangsan');
insert into emploees values(104,'tom3','m',8000,50,'zhangsan');
insert into emploees values(105,'tom4','m',9000,60,'zhangsan');
insert into emploees values(106,'tom5','m',10000,70,'zhangsan');
insert into emploees values(107,'kate1','f',5000,20,'lisi');
insert into emploees values(108,'kate2','f',6000,30,'lisi');
insert into emploees values(109,'kate3','f',7000,40,'lisi');
insert into emploees values(107,'kate4','f',8000,50,'lisi');
insert into emploees values(107,'kate5','f',9000,60,'lisi');
insert into emploees values(107,'kate6','f',9000,60,null);

排序

按id 降序排序

select * from emploees order by id desc

按id 升序排序，asc可以省略

select * from emploees order by id asc

按id 降序排序，saraly 升序

select * from emploees order by id desc,saraly asc

按id 降序排序，saraly 降序

select * from emploees order by id desc,saraly desc

分页

按id 降序排序，然后分页显示，limit后面0为偏移量，3 为显示的条数，第一页显示3条

select * from emploees 
order by id desc
limit 0,3

按id 降序排序，然后分页显示第二页显示5条

select * from emploees 
order by id desc
limit 1,5

8.0版本新特性 offset ，5代表显示条数，1为偏移量

select * from emploees 
order by id desc
limit 5 offset 1