MySQL query sorting and paging related

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:
  • Implementation of MySQL large page query optimization for millions of data
  • How to implement paging query in MySQL
  • How to query data from multiple unrelated tables and paging in Mysql
  • MySQL paging query optimization techniques
  • MySQL optimization tutorial: large paging query
  • How to implement paging query using MySQL

<<:  Detailed explanation of Docker container network port configuration process

>>:  el-table in vue realizes automatic ceiling effect (supports fixed)

Recommend

How to build and deploy Node project with Docker

Table of contents What is Docker Client-side Dock...

Details on using JS array methods some, every and find

Table of contents 1. some 2. every 3. find 1. som...

A brief summary of all encapsulation methods in Vue

Table of contents 1. Encapsulation API 2. Registe...

Detailed explanation of Object.create instance usage in js

1. Create a new object using the Object.create() ...

The use of anchor points in HTML_PowerNode Java Academy

Now let's summarize several situations of con...

A method of hiding processes under Linux and the pitfalls encountered

Preface 1. The tools used in this article can be ...

The difference between Decimal type and Float Double in MySQL (detailed explanation)

MySQL has non-standard data types such as float a...

Summary of the characteristics of SQL mode in MySQL

Preface The SQL mode affects the SQL syntax that ...

An article to help you understand Js inheritance and prototype chain

Table of contents Inheritance and prototype chain...

Detailed explanation of docker-machine usage

Docker-machine is a Docker management tool offici...

Implementation of Single Div drawing techniques in CSS

You can often see articles about CSS drawing, suc...

In-depth understanding of umask in new linux file permission settings

Preface The origin is a question 1: If your umask...

Html comments Symbols for marking text comments in Html

HTML comments, we often need to make some HTML co...

Vue implements an example of pulling down and scrolling to load data

Table of contents Step 1: Installation Step 2: Ci...

How to write CSS elegantly with react

Table of contents 1. Inline styles 2. Use import ...