How to limit the number of queries in mysql

In mysql, you can use the "LIMIT" clause to limit the number of queries. This clause can limit the number of items returned by the SELECT query results. The syntax "SELECT column1, column2,... FROM table LIMIT offset , count;”.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

When there are tens of thousands of data in the data table, querying all the data in the table at one time will reduce the speed of data return and put a lot of pressure on the database server. At this time, you can use the LIMIT clause to limit the number of items returned by the query results.

LIMIT is a special keyword in MySQL, which is used to specify which record the query results start to be displayed and how many records are displayed in total.

1. Introduction to MySQL LIMIT clause

Use the LIMIT clause in the SELECT statement to constrain the results in the result set Rows. LIMITThe clause accepts one or two parameters. The value of both parameters must be zero or a positive integer.

The LIMIT clause syntax for the two parameters is explained below:

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;

Let’s take a look at the LIMIT clause parameters:

• offsetThe parameter specifies the offset of the first row to be returned. The offset of the first row is 0, not 1.
• countSpecifies the maximum number of rows to return.

When you use the LIMIT clause with one parameter, this parameter is used to determine what is returned from the beginning of the result set Maximum number of rows.

SELECT 
    column1,column2,...
FROM
    table
LIMIT count;

The above query is equivalent to the following query with a LIMIT clause that accepts two parameters:

SELECT 
    column1,column2,...
FROM
    table
LIMIT 0 , count;

2. Use MySQL LIMIT to get the first N rows

You can use the LIMIT clause to select the first N row records in the table, as follows:

SELECT 
    column1,column2,...
FROM
    table
LIMIT N;

For example, to query employeesFor the first 5 customers in the table, please use the following query:

SELECT customernumber, customername, creditlimit FROM customers LIMIT 5;

or -

SELECT customernumber, customername, creditlimit FROM customers LIMIT 0,5;

Execute the above statement and get the following results-

mysql> SELECT customernumber, customername, creditlimit FROM customers LIMIT 5;
+----------------+----------------------------+-------------+
| customernumber | customername               | creditlimit |
+----------------+----------------------------+-------------+
|            103 | Atelier graphique          | 21000       |
|            112 | Signal Gift Stores         | 71800       |
|            114 | Australian Collectors, Co. | 117300      |
|            119 | La Rochelle Gifts          | 118200      |
|            121 | Baane Mini Imports         | 81700       |
+----------------+----------------------------+-------------+
5 rows in set

3. Use MySQL LIMIT to get the highest and lowest values. The

LIMIT clause is often used together with the ORDER BY clause. First, use the ORDER BY clause to sort the result set based on specific criteria, and then use the LIMIT clause to find the minimum or maximum value.

Note: The ORDER BY clause is used to sort by the specified field.

Please refer to the following customers table in the sample database (yiibaidb). Its table structure is as follows -

mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber         | int(11)       | NO   | PRI | NULL    |       |
| customerName           | varchar(50)   | NO   |     | NULL    |       |
| contactLastName        | varchar(50)   | NO   |     | NULL    |       |
| contactFirstName       | varchar(50)   | NO   |     | NULL    |       |
| phone                  | varchar(50)   | NO   |     | NULL    |       |
| addressLine1           | varchar(50)   | NO   |     | NULL    |       |
| addressLine2           | varchar(50)   | YES  |     | NULL    |       |
| city                   | varchar(50)   | NO   |     | NULL    |       |
| state                  | varchar(50)   | YES  |     | NULL    |       |
| postalCode             | varchar(15)   | YES  |     | NULL    |       |
| country                | varchar(50)   | NO   |     | NULL    |       |
| salesRepEmployeeNumber | int(11)       | YES  | MUL | NULL    |       |
| creditLimit            | decimal(10,2) | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set

For example, to query the one with the highest credit limit For the top five customers, please use the following query:

SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;

Execute the above query statement and get the following results -

mysql> SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;
+----------------+------------------------------+-------------+
| customernumber | customername                 | creditlimit |
+----------------+------------------------------+-------------+
|            141 | Euro+ Shopping Channel       | 227600      |
|            124 | Mini Gifts Distributors Ltd. | 210500      |
|            298 | Vida Sport, Ltd              | 141300      |
|            151 | Muscle Machine Inc           | 138500      |
|            187 | AV Stores, Co.               | 136800      |
+----------------+------------------------------+-------------+
5 rows in set

The following query will return the five customers with the lowest credit limit:

SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY
 creditlimit ASC
LIMIT 5;

4. Use MySQL LIMIT to get the nth highest value

One of the most difficult problems in MySQL is: how to get the nth highest value in the result set Values, such as querying which is the second (or n) most expensive product, obviously cannot be obtained using functions such as MAX or MIN. However, we can use MySQL LIMIT to solve such problems.

• First , sort the result set in descending order.
• The second step, use the LIMIT clause to obtain the nth most expensive product.

The general query is as follows:

SELECT 
    column1, column2,...
FROM
    table
ORDER BY column1 DESC
LIMIT nth-1, count;

Let’s take a look at an example below, which will be done using the products (products) table in the sample database (yiibaidb) Demo. productsThe structure of the table is as follows -

mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode        | varchar(15)   | NO   | PRI | NULL    |       |
| productName        | varchar(70)   | NO   |     | NULL    |       |
| productLine        | varchar(50)   | NO   | MUL | NULL    |       |
| productScale       | varchar(10)   | NO   |     | NULL    |       |
| productVendor      | varchar(50)   | NO   |     | NULL    |       |
| productDescription | text          | NO   |     | NULL    |       |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |       |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |       |
| MSRP               | decimal(10,2) | NO   |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
9 rows in set

View the row records in the following products table:

mysql> SELECT productCode, productName, buyprice
FROM products
ORDER BY
 buyprice DESC;
+-------------+--------------------------------------+----------+
| productCode | productName                          | buyprice |
+-------------+--------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V               | 103.42   |
| S18_2238    | 1998 Chrysler Plymouth Prowler       | 101.51   |
| S10_1949    | 1952 Alpine Renault 1300             | 98.58    |
| S24_3856    | 1956 Porsche 356A Coupe              | 98.3     |
| S12_1108    | 2001 Ferrari Enzo                    | 95.59    |
| S12_1099    | 1968 Ford Mustang                    | 95.34    |
... ....
+-------------+--------------------------------------+----------+
110 rows in set

Our task is to find the second highest priced product in the result set . You can use the LIMIT clause to select the second row, such as the following query (note: the offset starts from 0, so specify starting from 1, and then Take a row of records):

SELECT productCode, productName, buyprice FROM  products
ORDER BY buyprice DESC
LIMIT 1, 1;

Execute the above query statement and get the following results-

mysql> SELECT productCode, productName, buyprice FROM  products
ORDER BY buyprice DESC
LIMIT 1, 1;
+-------------+--------------------------------+----------+
| productCode | productName                    | buyprice |
+-------------+--------------------------------+----------+
| S18_2238    | 1998 Chrysler Plymouth Prowler | 101.51   |
+-------------+--------------------------------+----------+
1 row in set

Similarly, get the product information with the third and fourth highest selling prices: LIMIT 2, 1 and LIMIT 3, 1.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to limit the number of queries in mysql. For more information, please follow other related articles on the PHP Chinese website!