SQL 如何使用ORDER BY
在本SQL部分中,您将学习ORDER BY关键字是什么以及如何在SQL中使用它。我们还将讨论如何将ORDER BY子句与WHERE子句一起使用。
ORDER BY是什么?
ORDER BY是SQL中的一个子句,它可以按照升序或降序显示SELECT语句的结果集。
这个子句总是与结构化查询语言中的SELECT查询一起使用。
ORDER BY子句的语法
SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name ORDER BY Column_Name_1, Column_Name_2, ........, Column_Name_N;
我们可以在ORDER BY子句中使用多个表字段。我们必须用逗号分隔多个列的名称。
注意:如果您在查询中未定义升序或降序,请SQL自动按升序显示结果。
如果您想知道如何在SQL表中使用ORDER BY子句,您必须遵循以下几点:
- 创建一个简单的数据库和表。
- 插入数据到表中。
- 查看未使用ORDER BY子句插入的数据。
- 使用ORDER BY子句。
步骤1:创建简单的数据库和表
首先,在SQL中创建一个新的数据库。
以下查询创建了Hospital数据库:
CREATE Database Hospital;
现在,您必须使用以下CREATE TABLE语法创建新表:
CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_1)
);
以下查询在医院数据库中创建了一个名为”Patient”的表:
CREATE TABLE Patient
(
Patient_ID Int PRIMARY KEY,
Patient_Name VARCHAR (100),
Patient_Disease VARCHAR (80),
Patient_Age Int NOT NULL,
Patient_City Int NOT NULL Varchar (70)
) ;
步骤2:将数据插入表格中
现在,您必须使用以下语法将数据插入表格中:
INSERT INTO <Table_Name> (Column_Name_1, Column_Name_2, Column_Name_3, ......., Column_Name_N) VALUES (value_1, value_2, value_3, ...., value_N);
以下查询将患者录入Patient表格的Hospital数据库中:
INSERT INTO Patient (Patient_ID, Patient_Name, Patient_Disease, Patient_Age, Patient_City) VALUES ( 1035, Jones, Malaria, 25, Goa),
(1015, Marry, Diabetes, 44, Mumbai),
(1003, Harry, Fever, 29, Kochi),
(1044, Ella, Cancer, 40, Hyderabad),
(1025, Moria, Corona, 52, Kolkata);
步骤3:在没有ORDER BY子句的情况下查看插入数据的表
以下查询显示患者的记录以无序方式呈现:
SELECT * FROM Patient;
上面SELECT查询的输出如下:
Patient_ID | Patient_Name | Patient_Disease | Patient_Age | Patient_City |
---|---|---|---|---|
1035 | Jones | Malaria | 25 | Goa |
1015 | Marry | Diabetes | 44 | Mumbai |
1003 | Harry | Fever | 29 | Kochi |
1044 | Ella | Cancer | 40 | Hyderabad |
1025 | Moria | Corona | 52 | Kolkata |
步骤4:使用ORDER BY子句
以下查询使用ORDER BY子句按照从最小到最大的Patient_ID显示所有病人的记录:
SELECT * FROM Patient ORDER BY Patient_ID;
上述SELECT WITH ODER BY查询的输出在以下的病人表中显示:
Patient_ID | Patient_Name | Patient_Disease | Patient_Age | Patient_City |
---|---|---|---|---|
1003 | Harry | Fever | 29 | Kochi |
1015 | Marry | Diabetes | 44 | Mumbai |
1025 | Moria | Corona | 52 | Kolkata |
1035 | Jones | Malaria | 25 | Goa |
1044 | Ella | Cancer | 40 | Hyderabad |
下面的查询使用Patient表的多个列与ODDER BY子句:
SELECT Patient_ID, Patient_Name, Patient_Age FROM Patient ORDER BY Patient_Name, Patient_Age;
首先,这个查询按照患者姓名对患者记录进行排序,并且如果患者姓名重复超过一次,也会按照患者年龄对这些行进行排序。
输出:
ORDER BY ASC
ASC是与ORDER BY子句一起使用的关键字,用于按升序对所选行进行排序。
ORDER BY ASC的语法
SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name ORDER BY Column_Name_1 ASC, Column_Name_2 ASC, ........, Column_Name_N ASC;
ORDER BY ASC示例
以下查询在School数据库中创建了新的Department表。
CREATE TABLE Department
(
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR (50),
Department_Leader VARCHAR (70)
) ;
以下的INSERT INTO查询将记录插入到Department表中:
INSERT INTO Department(Department_ID, Department_Name, Department_Leader) VALUES (2211, Teacher, Ramesh),
(2252, Library, Somya),
(2201, Transport, Suresh),
(2224, Office, Aman),
(2248, Account, Bhanu),
(2208, Security, Sonu),
(2221, Peon, Punit ));
以下查询显示上表的记录:
SELECT * FROM Department;
输出:
Department_ID | Department_Name | Department_Leader |
---|---|---|
2211 | Teacher | Ramesh |
2252 | Library | Somya |
2201 | Transport | Suresh |
2224 | Office | Aman |
2248 | Account | Bhanu |
2208 | Security | Sonu |
2221 | Peon | Punit |
以下查询按照Department_ID以降序显示了部门表中的选定记录:
SELECT * FROM Department ORDER BY Department_ID ASC;
输出:
Department_ID | Department_Name | Department_Leader |
---|---|---|
2201 | Transport | Suresh |
2208 | Security | Sonu |
2211 | Teacher | Ramesh |
2221 | Peon | Punit |
2224 | Office | Aman |
2248 | Account | Bhanu |
2252 | Library | Somya |
ORDER BY DESC
DESC是与ORDER BY子句一起使用的关键字,用于将所选行按降序排序,即由高到低排列。
ORDER BY DESC的语法
SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name ORDER BY Column_Name_1 DESC, Column_Name_2 DESC, ........, Column_Name_N DESC;
ORDER BY DESC示例
以下查询在School数据库中创建了一个新的Department表:
CREATE TABLE Department
(
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR (50),
Department_Leader VARCHAR (70)
) ;
以下查询将给定的记录插入到Department表中:
INSERT INTO Department(Department_ID, Department_Name, Department_Leader) VALUES (2211, Teacher, Ramesh),
(2252, Library, Somya),
(2201, Transport, Suresh),
(2224, Office, Aman),
(2248, Account, Bhanu),
(2208, Security, Sonu),
(2221, Peon, Punit ));
以下查询显示了上表的记录:
SELECT * FROM Department;
输出:
Department_ID | Department_Name | Department_Leader |
---|---|---|
2211 | Teacher | Ramesh |
2252 | Library | Somya |
2201 | Transport | Suresh |
2224 | Office | Aman |
2248 | Account | Bhanu |
2208 | Security | Sonu |
2221 | Peon | Punit |
以下查询按照Department_ID降序显示了部门表中的选定记录:
SELECT * FROM Department ORDER BY Department_ID DESC;
输出:
Department_ID | Department_Name | Department_Leader |
---|---|---|
2252 | Library | Somya |
2248 | Account | Bhanu |
2224 | Office | Aman |
2221 | Peon | Punit |
2211 | Teacher | Ramesh |
2208 | Security | Sonu |
2201 | Transport | Suresh |
通过WHERE子句进行ORDER BY排序
ORDER BY关键字也可以与Structured Query Language(SQL)中SELECT查询的WHERE子句一起使用。
ORDER BY与WHERE子句的语法
SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name WHERE [condition] ORDER BY Column_Name [ASC | DESC];
ORDER BY和WHERE子句示例
以下查询在医院数据库中创建Patient_Details表:
CREATE TABLE Patient_Details
(
Patient_ID Int PRIMARY KEY,
Patient_Name VARCHAR (100),
Patient_Disease VARCHAR (80),
Patient_Age Int NOT NULL,
Patient_City Int NOT NULL Varchar (70)
) ;
以下查询将插入患者在医院数据库的Patient_Details表中的记录:
INSERT INTO Patient_Details (Patient_ID, Patient_Name, Patient_Disease, Patient_Age, Patient_City) VALUES ( 1035, Jones, Malaria, 25, Goa),
(1015, Marry, Diabetes, 44, Mumbai),
(1003, Harry, Fever, 29, Kochi),
(1044, Ella, Cancer, 40, Hyderabad),
(1025, Moria, Corona, 52, Kolkata);
以下查询以未排序的方式显示了Patient_Details的记录:
SELECT * FROM Patient_Details;
以上 SELECT 查询的结果如下所示:
Patient_ID | Patient_Name | Patient_Disease | Patient_Age | Patient_City |
---|---|---|---|---|
1035 | Jones | Malaria | 25 | Goa |
1015 | Marry | Diabetes | 44 | Mumbai |
1003 | Harry | Fever | 29 | Kochi |
1044 | Ella | Cancer | 40 | Hyderabad |
1025 | Moria | Corona | 52 | Kolkata |
以下查询按照患者ID大于1020的方式对患者的记录进行排序显示:
SELECT * FROM Patient_Details WHERE Patient_ID >1020 ORDER BY Patient_IDASC;
Patient_ID | Patient_Name | Patient_Disease | Patient_Age | Patient_City |
---|---|---|---|---|
1025 | Moria | Corona | 52 | Kolkata |
1035 | Jones | Malaria | 25 | Goa |
1044 | Ella | Cancer | 40 | Hyderabad |