MariaDB限制返回記錄

在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]>