mysql怎么指定查询一张表的查询结果,如最后5行记录和最前5行记录

  • 内容
  • 评论
  • 相关

mysql如何指定查询一张表的查询结果,如最后5行记录和最前5行记录

 

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) 

欢迎大家阅读《mysql怎么指定查询一张表的查询结果,如最后5行记录和最前5行记录》,跪求各位点评,by 搞代码

原创文章,转载请注明: 转载自搞代码

本文链接地址: mysql怎么指定查询一张表的查询结果,如最后5行记录和最前5行记录

微信支付二维码

微信 赏一包辣条吧~

支付宝支付二维码

支付宝 赏一听可乐吧~

评论

0条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注