How to implement query and sorting in mysql

In mysql, you can use the SELECT statement and the "ORDER BY" keyword to implement the query sorting function. The syntax is "SELECT * FROM data table name ORDER BY field name [ASC|DESC]"; if you set "ASC ” can be sorted in ascending order, and “DESC” can be sorted 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.

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

Grammar:

SELECT * FROM 数据表名 ORDER 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 means the fields are sorted in ascending order; DESC means the fields are sorted in descending order. Among them, ASC is the default value.

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

• ORDER BY Keywords can be followed by subqueries.

• 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 query and sorting in mysql. For more information, please follow other related articles on the PHP Chinese website!