MySQL query sorting and paging related

Overview

It is usually not what we want to present the data in the database directly, so we demonstrated in the last two sections how to filter the data. In addition to filtering the data,

We may also need to sort the data. For example, if we want to know the highest-cost items in the list, we may need to sort the amount field in descending order. If we want to see the distribution of ages from young to old, we may need to sort the age field in the user table in ascending order.

It may also be necessary to restrict the data. For example, we need to give different gifts to users who make payments 1 to 10, 11 to 20, and 21 to 30 respectively. At this time, data restrictions are very useful.

Note: In the following script, [] indicates optional, and the | separator indicates that one of them can be selected.

Data sorting order by

The syntax format is as follows:

1. The fields to be sorted follow the order by statement;

2. asc and desc indicate the sorting rules, asc: ascending order, desc: descending order, the default is ascending asc;

3. You can specify multiple fields for sorting, and separate multiple fields with commas.

4. In multi-field sorting, the earlier the field is, the higher the priority. In the following, cname1 is sorted first. When cname1 has the same value, cname2 starts to sort until all fields are sorted.

select cname from tname order by cname1 [asc|desc],cname2 [asc|desc]...;

Sort by single field

For example, the sales amount is displayed in descending order according to the transaction order amount:

mysql> select * from t_order;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 8 | brand | 52.2 | 2 |
| 9 | hen | 1752.02 | 7 |
| 10 | helyn | 88.5 | 4 |
| 11 | sol | 1007.9 | 11 |
| 12 | diny | 12 | 1 |
| 13 | weng | 52.2 | 5 |
| 14 | sally | 99.71 | 9 |
+---------+---------+---------+-------+
7 rows in set

mysql> select * from t_order order by amount desc;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 9 | hen | 1752.02 | 7 |
| 11 | sol | 1007.9 | 11 |
| 14 | sally | 99.71 | 9 |
| 10 | helyn | 88.5 | 4 |
| 8 | brand | 52.2 | 2 |
| 13 | weng | 52.2 | 5 |
| 12 | diny | 12 | 1 |
+---------+---------+---------+-------+
7 rows in set

Sorting multiple fields

Multiple fields are sorted by commas, and the priority decreases from left to right. As shown in the following figure, if the amount is the same, they are sorted from most to least according to the number of purchased items:

mysql> select * from t_order order by amount desc,goods desc;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 9 | hen | 1752.02 | 7 |
| 11 | sol | 1007.9 | 11 |
| 14 | sally | 99.71 | 9 |
| 10 | helyn | 88.5 | 4 |
| 13 | weng | 52.2 | 5 |
| 8 | brand | 52.2 | 2 |
| 12 | diny | 12 | 1 |
+---------+---------+---------+-------+
7 rows in set

Sort by alias

The purpose of sorting by alias or conditional query is to simplify the code and facilitate use. The alias can be in English or Chinese:

mysql> select account as ac,amount as am,goods as gd from t_order order by am,gd desc;

+-------+---------+----+
| ac | am | gd |
+-------+---------+----+
| diny | 12 | 1 |
| weng | 52.2 | 5 |
| brand | 52.2 | 2 |
| helyn | 88.5 | 4 |
| sally | 99.71 | 9 |
| sol | 1007.9 | 11 |
| hen | 1752.02 | 7 |
+-------+---------+----+
7 rows in set

Using functions in field sorting

The abs function is used below, so in the descending sort of the am field, -99.99 is ranked above 99.71.

mysql> select * from t_order;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 8 | brand | 52.2 | 2 |
| 9 | hen | 1752.02 | 7 |
| 10 | helyn | 88.5 | 4 |
| 11 | sol | 1007.9 | 11 |
| 12 | diny | 12 | 1 |
| 13 | weng | 52.2 | 5 |
| 14 | sally | 99.71 | 9 |
| 15 | brand1 | -99.99 | 5 |
+---------+---------+---------+-------+
8 rows in set

mysql> select account as ac,amount as am,goods as gd from t_order order by abs(am) desc;

+--------+---------+----+
| ac | am | gd |
+--------+---------+----+
| hen | 1752.02 | 7 |
| sol | 1007.9 | 11 |
| brand1 | -99.99 | 5 |
| sally | 99.71 | 9 |
| helyn | 88.5 | 4 |
| brand | 52.2 | 2 |
| weng | 52.2 | 5 |
| diny | 12 | 1 |
+--------+---------+----+
8 rows in set

Used in conjunction with the Where condition

order comes after the where condition, and sorts the data based on the filtered data in where. The following is the data filtered out with purchase amount > 80 and purchase quantity > 5, and sorted by price in descending order.

mysql> select * from t_order;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 8 | brand | 52.2 | 2 |
| 9 | hen | 1752.02 | 7 |
| 10 | helyn | 88.5 | 4 |
| 11 | sol | 1007.9 | 11 |
| 12 | diny | 12 | 1 |
| 13 | weng | 52.2 | 5 |
| 14 | sally | 99.71 | 9 |
| 15 | brand1 | -99.99 | 5 |
+---------+---------+---------+-------+
8 rows in set

mysql> select * from t_order where amount>80 and goods>5 order by amount desc;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 9 | hen | 1752.02 | 7 |
| 11 | sol | 1007.9 | 11 |
| 14 | sally | 99.71 | 9 |
+---------+---------+---------+-------+

Data limit

Often after filtering out the data that meets the requirements, we still need to get a specific range of these data. For example, we want to give different gifts to the 1st to 10th, 11th to 20th, and 21st to 30th users whose payments exceed 1,000. In this case, we need to use the limit operation.

Limit is used to limit the data returned by a select query and is often used in data ranking or paging.

The syntax format is as follows:

select cname from tname limit [offset,] count;

1. offset indicates the offset, which refers to the number of rows to skip. It can be omitted and the default value is 0, which means skipping 0 rows. For example, limit 8 is equivalent to limit 0,8.

2. count: The number of data rows to be fetched after skipping offset offset, which is count rows.

3. The values of offset and count in limit cannot be expressed using expressions.

Get the first n records

As shown in the following figure, limit n and limit 0,n are consistent:

mysql> select * from t_order;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 8 | brand | 52.2 | 2 |
| 9 | hen | 1752.02 | 7 |
| 10 | helyn | 88.5 | 4 |
| 11 | sol | 1007.9 | 11 |
| 12 | diny | 12 | 1 |
| 13 | weng | 52.2 | 5 |
| 14 | sally | 99.71 | 9 |
| 15 | brand1 | -99.99 | 5 |
+---------+---------+---------+-------+
8 rows in set

mysql> select * from t_order limit 2
;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 8 | brand | 52.2 | 2 |
| 9 | hen | 1752.02 | 7 |
+---------+---------+---------+-------+
2 rows in set

mysql> select * from t_order limit 0,2;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
| 8 | brand | 52.2 | 2 |
| 9 | hen | 1752.02 | 7 |
+---------+---------+---------+-------+
2 rows in set

Limit a single record

Here we get a record of the largest and smallest payment amounts. You can first use the order condition to sort, and then limit the first record:

 mysql> select * from t_order;
 +---------+---------+---------+-------+
 | orderid | account | amount | goods |
 +---------+---------+---------+-------+
 | 8 | brand | 52.2 | 2 |
 | 9 | hen | 1752.02 | 7 |
 | 10 | helyn | 88.5 | 4 |
 | 11 | sol | 1007.9 | 11 |
 | 12 | diny | 12 | 1 |
 | 13 | weng | 52.2 | 5 |
 | 14 | sally | 99.71 | 9 |
 | 15 | brand1 | -99.99 | 5 |
 +---------+---------+---------+-------+
 8 rows in set

 mysql> select * from t_order where amount>0 order by amount desc limit 1;
 +---------+---------+---------+-------+
 | orderid | account | amount | goods |
 +---------+---------+---------+-------+
 | 9 | hen | 1752.02 | 7 |
 +---------+---------+---------+-------+
 1 row in set

 mysql> select * from t_order where amount>0 order by amount asc limit 1;
 +---------+---------+--------+-------+
 | orderid | account | amount | goods |
 +---------+---------+--------+-------+
 | 12 | diny | 12 | 1 |
 +---------+---------+--------+-------+
 1 row in set

The above are the details related to sorting and paging of MySQL queries. For more information about MySQL queries, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: