python:mysql之ORDER BY 语句

数据分析学习社
数据分析学习社
数据分析学习社
348
文章
0
评论
2021-06-1009:47:30 评论 218 4168字
摘要

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语句,查询到结果集后并设置排序为降序

python:mysql之ORDER BY 语句

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
import pymysqlclass 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 _errorif __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为降序

python:mysql之ORDER BY 语句

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
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 语句查询数据完成...

 

End.

作者:一名小测试

转载如果涉及作品、版权其他问题请联系我们第一时间删除微信号:lovedata0520)

更多文章前往首页浏览http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: