Shulou(Shulou.com)05/31 Report--

Editor to share with you mysql how to achieve ascending order query, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

In mysql, you can use the "SELECT" statement and the "ORDER BY" keyword to implement an ascending query with 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 an ascending query.

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

Basic syntax:

SELECT {* | Field column name} FROM table name [WHERE condition] GROUP BY field name [ASC | DESC]

The syntax is explained as follows.

Field name: indicates the name of the field that needs to be sorted. Multiple fields are separated by commas.

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

You should pay attention to the following aspects when using the ORDER BY keyword:

The ORDER BY keyword can be followed by a subquery (the subquery will be explained in more detail in later tutorials, which you can learn here).

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

When ORDER BY specifies multiple fields to sort, MySQL sorts them from left to right in the order of the fields.

Single field sorting

Here is a concrete example to illustrate how ORDER BY sorts the query results when MySQL specifies a single field.

Example 1

The following query all the records of the tb_students_info table and sort the height field. The SQL statement and run 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 | 2016 | 10-22 | 1 | Dany | 1 | 25 | F | 2015-09-10 | 4 | Jane | 1 | 22 | F | 2016-12-20 | 7 | Lily | 6 | F | 2016-02-26 | | 10 | | | Tom | 4 | 23 | M | 2016 | 08-05 | 8 | Susan | 4 | 23 | F | 170 | 10-01 | | 6 | John | 2 | 21 | M | 2015-11 | | 5 | Jim | 1 | 24 | M | 2016-01-15 | 9 | Thomas | 3 | 22 | M | 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 of the query's height field in ascending order by numeric value.

Multi-field sorting

Here is a concrete example to illustrate how MySQL sorts the query results when ORDER BY specifies multiple fields.

Example 2

Query the name and height fields in the tb_students_info table, sort by height, and then by name. The SQL statement and run 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 | Susan | 170 | John | Jim | Jim | Thomas | Henry | 185 | +-+-+ 10 rows in set (0.09 sec)

Note: when sorting multiple fields, the first field of the sort must have the same value before the second field is sorted. If all the values in the first field data are unique, MySQL will no longer sort the second field.

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

Example 3

Query the tb_students_info table, first sort by height descending order, and then sort by name ascending order. The SQL statement and run 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 | Susan | 170 | Lily | 165th | Tom | Jane | Jane | Dany | 160 | Green | 158 | +-+-+ 10 rows in set (0.00 sec)

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

The above is all the contents of the article "how to query in ascending order in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.