接下来。装逼开始....
ORDER BY 语句用于对结果集进行排序操作
-
ORDER BY 语句可以指定任何字段作为排序条件
-
ORDER BY 语句也可以指定多个字段作为排序条件
-
ORDER BY 语句也可以指定ASC或DESC关键字设置结果集是升序还是降序
-
ORDER BY 语句是跟在SQL语句最后面的
-
ORDER BY 语句默认情况下是升序排序,也就是执行ASC
示例1:终端执行SQL语句,查询到结果集后并默认排序为升序
mysql> select * from test_user_copy;
+----+--------+-------------+----------+-----------+---------------------+
| id | name | mobile | address | country | school |
+----+--------+-------------+----------+-----------+---------------------+
| 1 | JAVA | 18847474541 | Beijing | China | Beijing University |
| 2 | Python | 17748484242 | Nanjing | Singapore | Nanjing University |
| 3 | SQL | 17748484246 | Beijing | China | Beijing University |
| 4 | R | 17748484141 | Shanghai | Singapore | Shanghai University |
+----+--------+-------------+----------+-----------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from test_user_copy order by country;
+----+--------+-------------+----------+-----------+---------------------+
| id | name | mobile | address | country | school |
+----+--------+-------------+----------+-----------+---------------------+
| 1 | JAVA | 18847474541 | Beijing | China | Beijing University |
| 3 | SQL | 17748484246 | Beijing | China | Beijing University |
| 2 | Python | 17748484242 | Nanjing | Singapore | Nanjing University |
| 4 | R | 17748484141 | Shanghai | Singapore | Shanghai University |
+----+--------+-------------+----------+-----------+---------------------+
4 rows in set (0.00 sec)
示例2:python执行SQL语句,查询到结果集后并设置排序为降序
import pymysql
class MysqlData:
def __init__(self, host="localhost", user="root", password="123456", database="testing", port=3306):
# 连接数据库
self.connection = pymysql.connect(host=host, user=user, password=password, database=database, port=port,
charset='utf8', cursorclass=pymysql.cursors.DictCursor)
def modify_data(self, sql, args=None):
"""
Args:
sql: sql语句
args: 接收格式化参数
Returns:
"""
try:
with self.connection:
self.connection.ping(reconnect=True)
with self.connection.cursor() as cursor:
# 执行sql语句
cursor.execute(sql, args=args)
# 提交sql语句
self.connection.commit()
except pymysql.err.MySQLError as _error:
raise _error
def get_data(self, sql, args=None, is_data=False):
"""
Args:
sql: sql语句
args: 接收格式化参数
is_data: 判断获取多条数据还是单个数据,默认获取单个数据
Returns:
"""
try:
with self.connection:
self.connection.ping(reconnect=True)
with self.connection.cursor() as cursor:
# 执行sql语句
cursor.execute(sql, args=args)
# 返回查询出的数据信息
return cursor.fetchall() if is_data else cursor.fetchone()
except pymysql.err.MySQLError as _error:
raise _error
if __name__ == '__main__':
# 初始化类,创建对象
info = MysqlData()
# 调用查询多条数据方法
results = info.get_data("""
SELECT
*
FROM
test_user_copy
ORDER BY
country DESC
""", is_data=True)
# 循环列表,获取数据
for i in results:
print(i)
{'id': 2, 'name': 'Python', 'mobile': '17748484242', 'address': 'Nanjing', 'country': 'Singapore', 'school': 'Nanjing University'}
{'id': 4, 'name': 'R', 'mobile': '17748484141', 'address': 'Shanghai', 'country': 'Singapore', 'school': 'Shanghai University'}
{'id': 5, 'name': 'C++', 'mobile': '17748484141', 'address': 'Shenzhen', 'country': 'France', 'school': 'Shanghai University'}
{'id': 1, 'name': 'JAVA', 'mobile': '18847474541', 'address': 'Beijing', 'country': 'China', 'school': 'Beijing University'}
{'id': 3, 'name': 'SQL', 'mobile': '17748484246', 'address': 'Beijing', 'country': 'China', 'school': 'Beijing University'}
Process finished with exit code 0
从返回的结果集可以看到,country这一列已经做了降序排序...
示例3:python执行SQL语句,查询到结果集后并设置id升序、country为降序
if __name__ == '__main__':
# 初始化类,创建对象
info = MysqlData()
# 调用查询多条数据方法
results = info.get_data("""
SELECT
*
FROM
test_user_copy
ORDER BY
id DESC,
country DESC
""", is_data=True)
# 循环列表,获取数据
for i in results:
print(i)
{'id': 5, 'name': 'C++', 'mobile': '17748484141', 'address': 'Shenzhen', 'country': 'France', 'school': 'Shanghai University'}
{'id': 4, 'name': 'R', 'mobile': '17748484141', 'address': 'Shanghai', 'country': 'Singapore', 'school': 'Shanghai University'}
{'id': 3, 'name': 'SQL', 'mobile': '17748484246', 'address': 'Beijing', 'country': 'China', 'school': 'Beijing University'}
{'id': 2, 'name': 'Python', 'mobile': '17748484242', 'address': 'Nanjing', 'country': 'Singapore', 'school': 'Nanjing University'}
{'id': 1, 'name': 'JAVA', 'mobile': '18847474541', 'address': 'Beijing', 'country': 'China', 'school': 'Beijing University'}
Process finished with exit code 0
从返回的结果集可以看到,优先生效的是id排序...
至此,mysql中使用 ORDER BY 语句查询数据完成...
以上总结或许能帮助到你,或许帮助不到你,但还是希望能帮助到你,如有疑问、歧义,直接私信留言会及时修正发布;感觉还不错记得点赞呦,谢谢!
未完,待续…
一直都在努力,希望您也是!
微信搜索公众号:就用python
![](https://i-blog.csdnimg.cn/blog_migrate/91280400f4e7c4efebfbdb006455e063.png)