在MariaDB数据库中如何对检索记录做降序升序操作-群英

上一篇：在MariaDB数据库中如何更改现有表的结构下一篇：MariaDB中删除数据库的表怎么样做

关于“在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]>据

以上就是关于“在MariaDB数据库中如何对检索记录做降序升序操作”的相关知识，感谢各位的阅读，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注群英网络，小编每天都会为大家更新不同的知识。