python：mysql之ORDER BY 语句

接下来。装逼开始....

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

更多内容欢迎关注公众号