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

Example code for implementing 3D Rubik's Cube with CSS

Let's make a simple 3D Rubik's Cube today...

How to configure redis sentinel mode in Docker (on multiple servers)

Table of contents Preface condition Install Docke...

The difference between key and index in MySQL

Let's look at the code first: ALTER TABLE rep...

CSS3 Bezier Curve Example: Creating Link Hover Animation Effects

We will use CSS3 animated transitions to create a...

Detailed explanation of various HTTP return status codes

When a request is sent to your server to display ...

WeChat Mini Program uses simultaneous interpretation to implement speech recognition

I use the simultaneous interpretation voice recog...

Solution to 1067 when Mysql starts in Windows

I just started working a few days ago and install...

Example code for text origami effect using CSS3

Preface This article mainly shares with you an ex...

Detailed explanation of the implementation method and usage of CSS3 border-radius rounded corners

In the past, it was quite troublesome to achieve ...

Element table header row height problem solution

Table of contents Preface 1. Cause of the problem...

Detailed explanation of how to install mysql5.7.16 from source code in centos7 environment

This article describes how to install mysql5.7.16...

Summary of Common Terms in CSS (Cascading Style Sheet)

If you use CSS don't forget to write DOCTYPE, ...

How to restore a database and a table from a MySQL full database backup

In the official MySQL dump tool, how can I restor...

Specific use of MySQL global locks and table-level locks

Table of contents Preface Global Lock Table lock ...

Front-end development general manual (including tools, websites, experience, etc.)

Today I have nothing to do, so I listed some tool...