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条，注意结果为倒序排序，因为用了descselect * 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)
页:
[1]