MariaDB限制返回记录

精华

小牛编辑

115浏览

2023-03-14

在MariaDB数据库中，使用SELECT语句和LIMIT子句从表中检索一个或多个记录。

语法：

SELECT expressions  
FROM tables  
[WHERE conditions]  
[ORDER BY expression [ ASC | DESC ]]  
LIMIT row_count;

示例1：

以降序检索记录：

使用SELECT语句并带有LIMIT子句查询students表中的数据。结果student_id列的值按降序显示，LIMIT为3。参考如下语句 -

SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id <= 7  
ORDER BY student_id DESC  
LIMIT 3;

执行上面查询语句，得到以下结果 -

MariaDB [testdb]> SELECT student_id, student_name, student_address
    -> FROM Students
    -> WHERE student_id <= 7
    -> ORDER BY student_id DESC
    -> LIMIT 3;
+------------+--------------+-----------------+
| student_id | student_name | student_address |
+------------+--------------+-----------------+
|          6 | Blaba        | Shengzheng      |
|          5 | Kobe         | Shanghai        |
|          4 | Mahesh       | Guangzhou       |
+------------+--------------+-----------------+
3 rows in set (0.00 sec)

示例2：

按student_id列的值升序检索记录：

SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id <= 7  
ORDER BY student_id ASC  
LIMIT 3;

执行上面查询语句，得到以下结果 -

MariaDB [testdb]> SELECT student_id, student_name, student_address
    -> FROM Students
    -> WHERE student_id <= 7
    -> ORDER BY student_id ASC
    -> LIMIT 3;
+------------+--------------+-----------------+
| student_id | student_name | student_address |
+------------+--------------+-----------------+
|          1 | Maxsu        | Haikou          |
|          3 | JMaster      | Beijing         |
|          4 | Mahesh       | Guangzhou       |
+------------+--------------+-----------------+
3 rows in set (0.00 sec)

示例3：分页

在应用程序中，由于数据记录太多，不能全在一个页面中全部显示，我们经常要使用分页来显示。假设每页显示3条记录，参考以下语句 -

-- 第1页数据
SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id > 0 
ORDER BY student_id ASC  
LIMIT 0,3; 

-- 第2页数据
SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id > 0 
ORDER BY student_id ASC  
LIMIT 3,3; 

-- 第3页数据
SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id > 0 
ORDER BY student_id ASC  
LIMIT 6,3;

执行上面查询语句，得到以下结果 -

MariaDB [testdb]> SELECT student_id, student_name, student_address
    -> FROM Students
    -> WHERE student_id > 0
    -> ORDER BY student_id ASC
    -> LIMIT 0,3;
+------------+--------------+-----------------+
| student_id | student_name | student_address |
+------------+--------------+-----------------+
|          1 | Maxsu        | Haikou          |
|          3 | JMaster      | Beijing         |
|          4 | Mahesh       | Guangzhou       |
+------------+--------------+-----------------+
3 rows in set (0.05 sec)

MariaDB [testdb]> SELECT student_id, student_name, student_address
    -> FROM Students
    -> WHERE student_id > 0
    -> ORDER BY student_id ASC
    -> LIMIT 3,3;
+------------+--------------+-----------------+
| student_id | student_name | student_address |
+------------+--------------+-----------------+
|          5 | Kobe         | Shanghai        |
|          6 | Blaba        | Shengzheng      |
+------------+--------------+-----------------+
2 rows in set (0.00 sec)

MariaDB [testdb]>