How to implement ascending query in mysql

In mysql, you can use the "SELECT" statement and the "ORDER BY" keyword to implement ascending query, the syntax "SELECT {*|field column name} FROM table name [WHERE condition] GROUP BY field name ASC; ".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the "SELECT" statement and the "ORDER BY" keyword to implement ascending query.

The SELECT statement is used to query data, and the ORDER BY keyword is mainly used to sort the data in the query results in a certain order.

Basic syntax:

SELECT {*|字段列名} FROM 表名 [WHERE 条件] GROUP BY 字段名 [ASC|DESC];

The syntax is explained below.

• Field name: Indicates the name of the field that needs to be sorted. If there are multiple fields, separate them with commas.

• ASC|DESC: ASC indicates that the fields are sorted in ascending order; DESC indicates that the fields are sorted in descending order. ASC is the default value.

When using the ORDER BY keyword, you should pay attention to the following aspects:

• The ORDER BY keyword can be followed by a subquery (about the subquery) The tutorial will explain it in detail, just learn it here).

• When there is a null value in the sorted field, ORDER BY will treat the null value as the minimum value.

• ORDER BY When specifying multiple fields for sorting, MySQL will sort from left to right according to the order of the fields.

Single field sorting

The following uses a specific example to illustrate that when ORDER BY specifies a single field, MySQL How to sort query results.

Example 1

The following queries all records in the tb_students_info table and sorts the height field. The SQL statement and running results are as follows.

mysql> SELECT * FROM tb_students_info ORDER BY height;
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.08 sec)

As you can see from the results, MySQL sorts the data in the height field of the query in ascending order according to the size of the value.

Multi-field sorting

The following uses a specific example to illustrate how MySQL performs query results when ORDER BY specifies multiple fields. Sort.

Example 2

Query the name and height fields in the tb_students_info table, sort by height first, and then by name. The SQL statement and running results are as follows.

mysql> SELECT name,height FROM tb_students_info ORDER BY height,name;
+--------+--------+
| name   | height |
+--------+--------+
| Green  |    158 |
| Dany   |    160 |
| Jane   |    162 |
| Lily   |    165 |
| Tom    |    165 |
| Susan  |    170 |
| John   |    172 |
| Jim    |    175 |
| Thomas |    178 |
| Henry  |    185 |
+--------+--------+
10 rows in set (0.09 sec)

Note: When sorting multiple fields, the first field to be sorted must have the same value before the second field is sorted. If all values ​​in the first field's data are unique, MySQL will no longer sort the second field.

By default, the query data is sorted in ascending alphabetical order (A~Z), but the sorting of data is not limited to this. You can also use DESC in ORDER BY to sort the query results in descending order (Z~A ).

Example 3

Query the tb_students_info table, first sort by height in descending order, and then by name in ascending order. The SQL statement and running results are as follows.

mysql> SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC;
+--------+--------+
| name   | height |
+--------+--------+
| Henry  |    185 |
| Thomas |    178 |
| Jim    |    175 |
| John   |    172 |
| Susan  |    170 |
| Lily   |    165 |
| Tom    |    165 |
| Jane   |    162 |
| Dany   |    160 |
| Green  |    158 |
+--------+--------+
10 rows in set (0.00 sec)

DESC keyword only sorts the previous columns in descending order, here only the height field is sorted in descending order. Therefore, height is sorted in descending order, while name is still sorted in ascending order. If you want to sort in descending order on multiple columns, you must specify the DESC keyword for each column.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to implement ascending query in mysql. For more information, please follow other related articles on the PHP Chinese website!