在MariaDB數(shù)據(jù)庫(kù)中，使用SELECT語(yǔ)句和LIMIT子句從表中檢索一個(gè)或多個(gè)記錄。

語(yǔ)法：

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

示例1：

以降序檢索記錄：

使用SELECT語(yǔ)句并帶有LIMIT子句查詢students表中的數(shù)據(jù)。結(jié)果student_id列的值按降序顯示，LIMIT為3。參考如下語(yǔ)句 -

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

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

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;

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

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：分頁(yè)

在應(yīng)用程序中，由于數(shù)據(jù)記錄太多，不能全在一個(gè)頁(yè)面中全部顯示，我們經(jīng)常要使用分頁(yè)來(lái)顯示。假設(shè)每頁(yè)顯示3條記錄，參考以下語(yǔ)句 -

-- 第1頁(yè)數(shù)據(jù)
SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id > 0 
ORDER BY student_id ASC  
LIMIT 0,3; 

-- 第2頁(yè)數(shù)據(jù)
SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id > 0 
ORDER BY student_id ASC  
LIMIT 3,3; 

-- 第3頁(yè)數(shù)據(jù)
SELECT student_id, student_name, student_address  
FROM Students  
WHERE student_id > 0 
ORDER BY student_id ASC  
LIMIT 6,3;

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

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