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

This article mainly explains "how to implement query and sort in mysql". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to query and sort by mysql.

In mysql, you can use the select statement and the "ORDER BY" keyword to sort the query. The syntax "SELECT * FROM data table name ORDER BY field name [ASC | DESC]"; if you set "ASC", you can sort in ascending order, and "DESC" can sort in descending order.

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 achieve the query sorting effect.

The SELECT statement can query the data, while the ORDER BY keyword can sort the data in the query results in a certain order

Syntax:

SELECT * FROM datasheet name ORDER 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:

You can follow the subquery after the ORDER BY keyword.

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.

At this point, I believe you have a deeper understanding of "how to achieve query and sorting in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.