Python Data Access 简明教程
Python MySQL - Order By
在使用 SELECT 查询获取数据时， يمكنك使用 OrderBy 子句以所需的順序（升序或降序）对结果进行排序。默认情况下，此子句按升序对结果进行排序，如果你需要按降序排列结果，则需要明确使用“DESC”。
While fetching data using SELECT query, you can sort the results in desired order (ascending or descending) using the OrderBy clause. By default, this clause sorts results in ascending order, if you need to arrange them in descending order you need to use “DESC” explicitly.
Syntax
以下是 SELECT column-list 的语法
Following is the syntax SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2,.. columnN] [ASC | DESC]; of the ORDER BY clause:Example
假设我们在 MySQL 中使用 EMPLOYEES 作为名称创建了一个表格，如下所示：
Assume we have created a table in MySQL with name EMPLOYEES as −
mysql> CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
Query OK, 0 rows affected (0.36 sec)如果使用 INSERT 语句向其中插入了 4 条记录，如下所示：
And if we have inserted 4 records in to it using INSERT statements as −
mysql> INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);以下语句按升序检索 EMPLOYEE 表的内容。
Following statement retrieves the contents of the EMPLOYEE table in ascending order of the age.
mysql> SELECT * FROM EMPLOYEE ORDER BY AGE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 19 | M | 2000 |
| Raj | Kandukuri | 20 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
| Mac | Mohan | 26 | M | 2000 |
+------------+-----------+------+------+--------+
4 rows in set (0.04 sec)你还可以使用 DESC 按降序检索数据，如下所示：
You can also retrieve data in descending order using DESC as −
mysql> SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME, INCOME DESC;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 19 | M | 2000 |
| Mac | Mohan | 26 | M | 2000 |
| Raj | Kandukuri | 20 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
+------------+-----------+------+------+--------+
4 rows in set (0.00 sec)ORDER BY clause using python
要按特定顺序检索表的某个内容，调用游标对象上的 execute() 方法，并将 SELECT 语句和 ORDER BY 子句作为参数传递给它。
To retrieve contents of a table in specific order, invoke the execute() method on the cursor object and, pass the SELECT statement along with ORDER BY clause, as a parameter to it.
Example
在以下示例中，我们使用 ORDER BY 子句创建了一个名为 Employee 的表，对其进行填充，然后按其年龄的（升序）顺序检索其记录。
In the following example we are creating a table with name and Employee, populating it, and retrieving its records back in the (ascending) order of their age, using the ORDER BY clause.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
)'''
cursor.execute(sql)
#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES (%s, %s, %s, %s, %s)"
data = [('Krishna', 'Sharma', 26, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 29, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)
conn.commit()
#Retrieving specific records using the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
print(cursor.fetchall())
#Closing the connection
conn.close()Output
[('Raj', 'Kandukuri', 20, 'M', 7000.0),
('Krishna', 'Sharma', 26, 'M', 2000.0),
('Mac', 'Mohan', 26, 'M', 2000.0),
('Ramya', 'Ramapriya', 29, 'F', 5000.0)
]同样，你可以使用 ORDER BY 子句按降序从表中检索数据。
In the same way you can retrieve data from a table in descending order using the ORDER BY clause.
Example
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving specific records using the ORDERBY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY INCOME DESC")
print(cursor.fetchall())
#Closing the connection
conn.close()