Python Data Access 简明教程
Python SQLite - Order By
使用 SELECT 查询获取数据时，您将按照插入记录的顺序获取记录。
While fetching data using SELECT query, you will get the records in the same order in which you have inserted them.
您可以使用 Order By 子句按所需顺序（升序或降序）对结果进行排序。默认情况下，此子句按升序对结果进行排序。如果您需要按降序对结果进行排序，您需要显式使用 “DESC”。
You can sort the results in desired order (ascending or descending) using the Order By 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
以下是 SQLite 中 ORDER BY 子句的语法。
Following is the syntax of the ORDER BY clause in SQLite.
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];Example
假设我们使用以下查询创建了一个名为 CRICKETERS 的表 -
Assume we have created a table with name CRICKETERS using the following query −
sqlite> CREATE TABLE CRICKETERS (
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Age int,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255)
);
sqlite>并且如果我们使用 INSERT 语句向其中插入 5 条记录，如下所示 -
And if we have inserted 5 records in to it using INSERT statements as −
sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>选择语句检索 CRICKETERS 表的行按升序排列它们的年龄—
Following SELECT statement retrieves the rows of the CRICKETERS table in the ascending order of their age −
sqlite> SELECT * FROM CRICKETERS ORDER BY AGE;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Virat Kohli 30 Delhi India
Rohit Sharma 32 Nagpur India
Shikhar Dhawan 33 Delhi India
Jonathan Trott 38 CapeTown SouthAfrica
Kumara Sangakkara 41 Matale Srilanka
sqlite>您可以使用多个列对表的记录进行排序。以下 SELECT 语句基于列 AGE 和 FIRST_NAME 对 CRICKETERS 表的记录进行排序。
You can use more than one column to sort the records of a table. Following SELECT statements sorts the records of the CRICKETERS table based on the columns AGE and FIRST_NAME.
sqlite> SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Virat Kohli 30 Delhi India
Rohit Sharma 32 Nagpur India
Shikhar Dhawan 33 Delhi India
Jonathan Trott 38 CapeTown SouthAfrica
Kumara Sangakkara 41 Matale Srilanka
sqlite>默认情况下， ORDER BY 子句按升序对表的记录进行排序。您可以使用 DESC 按降序排列结果，如下所示：
By default, the ORDER BY clause sorts the records of a table in ascending order you can arrange the results in descending order using DESC as −
sqlite> SELECT * FROM CRICKETERS ORDER BY AGE DESC;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Kumara Sangakkara 41 Matale Srilanka
Jonathan Trott 38 CapeTown SouthAfrica
Shikhar Dhawan 33 Delhi India
Rohit Sharma 32 Nagpur India
Virat Kohli 30 Delhi India
sqlite>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 psycopg2
#establishing the connection
conn = psycopg2.connect(
database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)
#Setting auto commit false
conn.autocommit = True
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Creating a table
sql = '''CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT, SEX CHAR(1),
INCOME INT,
CONTACT INT
)'''
cursor.execute(sql)
#Populating the table
#Populating the table
cursor.execute('''INSERT INTO EMPLOYEE
(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES
('Ramya', 'Rama priya', 27, 'F', 9000),
('Vinay', 'Battacharya', 20, 'M', 6000),
('Sharukh', 'Sheik', 25, 'M', 8300),
('Sarmista', 'Sharma', 26, 'F', 10000),
('Tripthi', 'Mishra', 24, 'F', 6000)''')
conn.commit()
#Retrieving specific records using the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
print(cursor.fetchall())
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()