Home >Database >Mysql Tutorial >How to sort results using MySQL's ORDER BY function

How to sort results using MySQL's ORDER BY function

PHPz
PHPzOriginal
2023-07-24 18:09:332381browse

How to use MySQL's ORDER BY function to sort the results

Introduction:
When processing data in the database, we often encounter situations where the results need to be sorted. MySQL provides the ORDER BY function, which can easily sort query results according to specified rules. This article will introduce how to use MySQL's ORDER BY function and give code examples.

1. Basic syntax of the ORDER BY function
The ORDER BY statement is used to sort query results. By default, the order is in ascending order. The basic syntax of the ORDER BY statement is as follows:

SELECT column_name1, column_name2, ...
FROM table_name
ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC], ...

Description:

  1. column_name1, column_name2, ...: The column name to be queried, which can be one or more.
  2. table_name: Data table name.
  3. column_name1, column_name2, ...: The column name to be sorted, which can be one or more.
  4. ASC|DESC: Optional parameter, used to specify the sorting method. The default is ascending order (ASC). If you need to sort in descending order, use the DESC keyword.

2. Example
Suppose we have a data table named student, which contains the student’s student number (sid), name (name) and age (age). We want to sort the Output the results in order from small to large.

The sample code is as follows:

-- 创建student数据表
CREATE TABLE student (
  sid INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

-- 插入测试数据
INSERT INTO student (sid, name, age) VALUES (1, '张三', 20);
INSERT INTO student (sid, name, age) VALUES (2, '李四', 18);
INSERT INTO student (sid, name, age) VALUES (3, '王五', 22);
INSERT INTO student (sid, name, age) VALUES (4, '赵六', 19);

-- 查询并按照年龄升序排序
SELECT * FROM student ORDER BY age ASC;

Run the above code, we will get the results sorted by age from small to large:

+-----+------+------+-----+
| sid | name | age  |     |
+-----+------+------+-----+
|   2 | 李四   |   18 |     |
|   4 | 赵六   |   19 |     |
|   1 | 张三   |   20 |     |
|   3 | 王五   |   22 |     |
+-----+------+------+-----+

In addition to sorting by a single column, we can also sort by Sort by multiple columns. Suppose we want to sort by age in ascending order first, and if the ages are the same, then by student number in ascending order.

The sample code is as follows:

-- 查询并按照年龄和学号升序排序
SELECT * FROM student ORDER BY age ASC, sid ASC;

Run the above code, we will get the results sorted by age and student number in ascending order:

+-----+------+------+-----+
| sid | name | age  |     |
+-----+------+------+-----+
|   2 | 李四   |   18 |     |
|   4 | 赵六   |   19 |     |
|   1 | 张三   |   20 |     |
|   3 | 王五   |   22 |     |
+-----+------+------+-----+

If we need to sort by age in descending order, just The DESC keyword needs to be added after the column name.

The sample code is as follows:

-- 查询并按照年龄降序排序
SELECT * FROM student ORDER BY age DESC;

Run the above code, we will get the results sorted in descending order by age:

+-----+------+------+-----+
| sid | name | age  |     |
+-----+------+------+-----+
|   3 | 王五   |   22 |     |
|   1 | 张三   |   20 |     |
|   4 | 赵六   |   19 |     |
|   2 | 李四   |   18 |     |
+-----+------+------+-----+

Summary:
This article introduces how to use MySQL The ORDER BY function sorts query results and provides code examples. By using the ORDER BY function, we can easily sort the query results according to the specified sorting rules to meet actual needs. I hope this article can help readers better understand and use the ORDER BY function.

The above is the detailed content of How to sort results using MySQL's ORDER BY function. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn