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

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

MySQL如何指定查询一张表的查询结果，如最后5行记录和最前5行记录？我们以student表为例，表中包含三个字段：id、name、age，id为主键，为自增，共有10条记录。

MySQL查询语句如下：

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. 查询前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. 查询后5行记录：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行记录：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)

6. 查询一条记录($id)的下一条记录：select * from student where id>$id order by id asc limit 1;，例如当$id=4时，返回第5条记录。

+----+------+------+
| 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条记录。

+----+------+------+
| id | name | age |
+----+------+------+
| 3 | chou | 13 |
+----+------+------+
1 row in set (0.00 sec)2024-12-12