欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql查询第n到第m条数据

程序员文章站 2022-06-24 16:49:48
mysql如何指定查询一张表的查询结果,如最后5行记录和最前5行记录我们以student表为例,里面有三个字段:id,name,age,其中id为主健,为自增,里面共有10条记录,如下所示。mysql> select * from student;+----+------+------+| id | name | age |+----+------+------+| 1 | li | 11 || 2 | zh | 12 || 3 | chou | 13 |......

收到客户端{pageNo:1,pagesize:10} 
select * from table limit (pageNo-1)*pageSize, pageSize;

收到客户端{pageNo:5,pageSize:30} 
select * from table limit (pageNo-1)*pageSize,pageSize;

 

mysql如何指定查询一张表的查询结果,如最后5行记录和最前5行记录
我们以student表为例,里面有三个字段:id,name,age,其中id为主健,为自增,里面共有10条记录,如下所示。
mysql> select  * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | li   |   11 |
|  2 | zh   |   12 |
|  3 | chou |   13 |
|  4 | he   |   14 |
|  5 | lin  |   15 |
|  6 | ll   |   16 |
|  7 | chen |   17 |
|  8 | yu   |   18 |
|  9 | wu   |   19 |
| 10 | xie  |   20 |
+----+------+------+
10 rows in set (0.00 sec)
 
1、查询第一行记录
select * from student limit 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | li   |   11 |
+----+------+------+
1 row in set (0.00 sec)
 
2、查询最后一行记录
select * from student order by id desc limit 1;
+----+------+------+
| id | name | age  |
+----+------+------+
| 10 | xie  |   20 |
+----+------+------+
1 row in set (0.00 sec)
 
3、查询前n行记录,如前5行
select * from student limit 5;
select * from student limit 0,5;
select * from student order by id asc limit 5;
上面三条语句的结果都是一样的,如下:
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | li   |   11 |
|  2 | zh   |   12 |
|  3 | chou |   13 |
|  4 | he   |   14 |
|  5 | lin  |   15 |
+----+------+------+
5 rows in set (0.00 sec)
    
4、查询后n行记录,如后5条,注意结果为倒序排序,因为用了desc
select * from student order by id desc limit 5;
+----+------+------+
| id | name | age  |
+----+------+------+
| 10 | xie  |   20 |
|  9 | wu   |   19 |
|  8 | yu   |   18 |
|  7 | chen |   17 |
|  6 | ll   |   16 |
+----+------+------+
5 rows in set (0.00 sec)
 
5、查询第m行到第n行记录,注意表中的记录下标是从0开始的,就像数组一样
select * from student limit m,n;  返回m+1到m+n行记录,m代表开始的下标,n代表查找的结果数,将返回n行结果
select * from student limit 2,8;  返回3到10行记录    
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | chou |   13 |
|  4 | he   |   14 |
|  5 | lin  |   15 |
|  6 | ll   |   16 |
|  7 | chen |   17 |
|  8 | yu   |   18 |
|  9 | wu   |   19 |
| 10 | xie  |   20 |
+----+------+------+
8 rows in set (0.00 sec)
 
select * from student limit 3,1; 返回第4行
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | he   |   14 |
+----+------+------+
1 row in set (0.00 sec)
 
6、查询一条记录($id)的下一条记录
select * from student where id>$id order by id asc limit 1;
如$id=4时将返回第5条记录
select * from student where id>4 order by id asc limit 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | lin  |   15 |
+----+------+------+
1 row in set (0.00 sec)
 
7、查询一条记录($id)的上一条记录
select * from student where id<$id order by id desc limit 1;
如$id=4时将返回第3条记录
select * from student where id<4 order by id desc limit 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | chou |   13 |
+----+------+------+
1 row in set (0.00 sec)    

本文地址:https://blog.csdn.net/liu_yulong/article/details/110948069

相关标签: MySql