Mysql 简明教程

MySQL - ORDER BY CLAUSE

MySQL ORDER BY Clause

MySQL ORDER BY 子句用于按提供的顺序对表的 1 个或多个列进行排序，该顺序可以是升序或降序。默认情况下，如果不指定排序顺序，它会按 ascending order 对列进行排序。

The MySQL ORDER BY clause is used to sort one or more columns of a table in provided order that can be either ascending or descending order. By default, it sorts the column(s) in ascending order if the sort order is not specified.

排序通过 2 个关键字指定；ASC 用于升序，DESC 用于降序。

The sort is specified with two keywords; ASC for ascending order and DESC for descending order.

使用 ORDER BY 子句，我们可以对表的多个列进行排序，并为每一列提供不同的排序顺序。例如，我们可以先按一列，然后按另一列对结果集进行排序，以此类推。

Using the ORDER BY clause, we can sort multiple columns of a table and provide different sort orders for each column. For instance, we can sort the result set first by one column, and then by another column to the first column, and so on.

Syntax

以下是 MySQL 中 ORDER BY 子句的语法：

Following is the syntax of ORDER BY clause in MySQL −

SELECT column-list
FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]

在此，

Here,

  1. column-list are the names of the columns that we want to retrieve from the table_name.

  2. column1, column2,…​columnN are the column(s) that we want to order (sort).

  3. ASC will sort the columns in ascending order.

  4. DESC will sort the columns in descending order.

默认情况下，ORDER BY 子句按升序排列提供的列。

By default, the ORDER BY clause sorts the provided column in Ascending order.

Example

首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 −

Firstly, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下查询将 7 条记录插入到上述创建的表中：

The following query inserts 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

执行以下查询以验证 CUSTOMERS 表是否已创建：

Execute the following query to verify whether the CUSTOMERS table is created or not −

Select * from CUSTOMERS;

CUSTOMERS 表已成功创建：

The CUSTOMERS table has been created successfully −

现在，让我们按 NAME 列排序来显示 CUSTOMERS 表中的所有列：

Now, let us display all the columns from the CUSTOMERS table, sorted by the NAME column −

默认情况下，ORDER BY 子句按 Ascending order 排序提供的列。

By default, the ORDER BY clause sorts the provided column in Ascending order.

SELECT * FROM CUSTOMERS
ORDER BY NAME;

Output

正如我们在下面的输出中看到的，NAME 列按升序排序。

As we can see in the output below, the NAME column is sorted in Ascending order.

ORDER BY with DESC

我们可以使用 ORDER BY 子句和 DESC 关键字来按降序对表中的特定列进行排序。让我们通过以下示例了解一下。

We can sort a particular column of a table in descending order by using the ORDER BY clause along with the DESC keyword. Let us understand with the following example.

Example

在以下查询中，我们按降序排列 NAME 列显示 CUSTOMERS 表中的所有列：

In the following query, we are displaying all the columns from the CUSTOMERS table, sorted by the NAME column in descending order −

SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;

Output

正如我们在下面的输出中看到的，NAME 列按降序排列。

As we can see in the output below, the NAME column is sorted in descending order.

ORDER BY with Multiple Columns

我们还可以对 MySQL 表的多个列进行排序。为此，我们需要在 ORDER BY 子句中指定所有列名。

We can also sort multiple columns of a MySQL table. To do so, we need to specify all the column names in the ORDER BY clause.

Example

在此，我们按 ADDRESS 和 NAME 列对 CUSTOMERS 表中的所有列进行排序。

Here, we are selecting all the columns from the CUSTOMERS table, sorted by the ADDRESS and NAME columns.

SELECT * FROM CUSTOMERS
ORDER BY ADDRESS, NAME;

Output

上面的查询首先按升序对 ADDRESS 列进行排序，并且对于具有相同 ADDRESS 值的任何行，它们将按升序对 NAME 列进行排序。

The above query first sorts the ADDRESS column in ascending order, and for any rows that have the same ADDRESS value, they will be sorted by the NAME column in ascending order.

这意味着具有相同 ADDRESS 值的所有行将被分组在一起并按 NAME 排序。

This means, all the rows with the same ADDRESS value will be grouped together and sorted by NAME.

ORDER BY with ASC and DESC

在 MySQL 中，我们可以在同一个查询中按 ASC 和 DESC 对列进行排序。首先提供 ASC 的列将按升序排序，其次提供 DESC 的列将按降序排序。

In MySQL, we can order the columns with ASC and DESC in the same query. The column provided first with ASC will be sorted in Ascending order and the column provided second with DESC will be sorted in descending order.

Example

在此查询中，我们按年龄升序并按工资降序对 CUSTOMERS 表中的所有列进行排序：

In this query, we are selecting all the columns from the CUSTOMERS table, sorted ascending by the AGE and descending by the SALARY column −

SELECT * FROM CUSTOMERS
ORDER BY AGE ASC, SALARY DESC;

Output

执行给定的程序后，输出如下所示：

On executing the given program, the output is displayed as follows −

ORDER BY with LENGTH()

我们可以使用 MySQL 中的 ORDER BY 子句和 LENGTH() 函数来基于长度对特定列中存在的值进行排序。

We can use the LENGTH() function with the ORDER BY clause in MySQL to sort the values present in a particular column based on the length.

Example

使用以下查询，我们按长度对 ADDRESS 列进行排序：

Using the following query, we are sorting the ADDRESS column based on the length −

SELECT * FROM CUSTOMERS
ORDER BY LENGTH(ADDRESS) ASC;

Output

以上程序的输出如下所示：

The output for the program above is produced as given below −

Order By Clause Using a Client Program

除了使用 MySQL ORDER BY 子句对表的列进行排序外，我们还可以使用 Node.js、PHP、Java 和 Python 等客户端程序来实现相同的结果。

Besides using MySQL ORDER BY clause to sort one or more columns of a table, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

以下是此操作在各种编程语言中的语法 −

Following are the syntaxes of this operation in various programming languages −

Example

以下是这些程序 −

Following are the programs −