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

Start nginxssl configuration based on docker

Prerequisites A cloud server (centOS of Alibaba C...

Several methods to clear floating (recommended)

1. Add an empty element of the same type, and the...

The correct way to use Homebrew in Linux

Many people use Linux Homebrew. Here are three ti...

Introduction to the graphic composition and typesetting capabilities of web design

Everything needs a foundation. To build a house, ...

CSS tips for controlling animation playback and pause (very practical)

Today I will introduce a very simple trick to con...

Implementation of removing overlapping time and calculating time difference in MySQL

Table of contents need: drive: Ideas: accomplish:...

Solutions to common problems using Elasticsearch

1. Using it with redis will cause Netty startup c...

MySQL sorting Chinese details and examples

Detailed explanation of MySQL sorting Chinese cha...

Detailed explanation of how to enable HSTS in nginx to force the browser to redirect to HTTPS access

In the previous article, we have implemented loca...

How to deploy the crownblog project to Alibaba Cloud using docker

Front-end project packaging Find .env.production ...

Linux configuration SSH password-free login "ssh-keygen" basic usage

Table of contents 1 What is SSH 2 Configure SSH p...

js implements some functions of the input component in Element and encapsulates it into a component (example code)

Currently implemented are basic usage, clearable,...

Implementation of Node connection to MySQL query transaction processing

Table of contents Enter the topic mysql add, dele...

jQuery implements font size adjustment case

This article shares the specific code of jQuery t...

Detailed explanation of Nodejs array queue and forEach application

This article mainly records the problems and solu...