Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to limit the number of queries, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

In mysql, you can use the "LIMIT" clause to limit the number of queries, which can limit the number of entries returned by SELECT query results, with 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 once will reduce the speed of data return and cause great pressure on the database server. At this point, you can use the LIMIT clause to limit the number of entries returned by the query results.

LIMIT is a special keyword in MySQL that specifies which record the query results are displayed from and how many records are displayed.

1. Brief introduction of MySQL LIMIT clause

Use the LIMIT clause in the SELECT statement to constrain the number of rows in the result set. The LIMIT clause accepts one or two parameters. The values of both parameters must be zero or positive integers.

The syntax of the LIMIT clause for the two parameters is described below:

SELECT column1,column2,...FROM tableLIMIT offset, count

Let's look at the parameters of the LIMIT clause:

The offset parameter specifies the offset of the first row to be returned. The offset of the first line is 0, not 1.

Count specifies the maximum number of rows to return.

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

SELECT column1,column2,...FROM tableLIMIT count

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

SELECT column1,column2,...FROM tableLIMIT 0, count;2. Use MySQL LIMIT to get the first N rows

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

SELECT column1,column2,...FROM tableLIMIT N

For example, to query the first five customers in the employees table, 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 | +-+-+ -+ | 21000 | Atelier graphique | 21000 | 112 | Signal Gift Stores | 71800 | | 114 | Australian Collectors Co. | 117300 | La Rochelle Gifts | 118200 | | 81700 | Baane Mini Imports | 81700 | +-+ 5 rows in set3. Use MySQL LIMIT to get the highest and lowest values

The LIMIT clause is often used with the ORDER BY clause. First, use the ORDER BY clause to sort the result set according to specific conditions, and then use the LIMIT clause to find the minimum or maximum value.

Note: the use of the ORDER BY clause sorted by the specified field.

See the following customers table in the sample database (yiibaidb), which has the following table structure-

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 (10L2) | YES | | NULL | | +-+ 13 rows in set |

For example, to query the top five customers with the highest credit lines, use the following query:

SELECT customernumber, customername, creditlimitFROM customersORDER BY creditlimit DESCLIMIT 5

Execute the above query and get the following results-

Mysql > SELECT customernumber, customername, creditlimitFROM customersORDER BY creditlimit DESCLIMIT 5 +-+ | customernumber | customername | creditlimit | +-+- -+-+ | 141 | Euro+ Shopping Channel | 227600 | | 227600 | Mini Gifts Distributors Ltd. | 210500 | | 298 | Vida Sport Ltd | 141300 | Muscle Machine Inc | 138500 | 138500 | AV Stores, Co. | 136800 | +-+ 5 rows in set

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

SELECT customernumber, customername, creditlimitFROM customersORDER BY creditlimit ASCLIMIT 5. Use MySQL LIMIT to get the nth highest value

One of the thorniest problems in MySQL is how to get the nth highest value in the result set, such as querying which product is the second (or nth) most expensive product, which obviously cannot be obtained by 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 is to use the LIMIT clause to get the nth expensive product.

The general query is as follows:

SELECT column1, column2,...FROM tableORDER BY column1 DESCLIMIT nth-1, count

Let's take a look at an example that will be demonstrated using the products table in the sample database (yiibaidb). The structure of the products 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 (10L2) | NO | NULL | MSRP | decimal (10L2) | NO | | | NULL | | +-+-+ 9 rows in set |

View the row records in the following product table:

Mysql > SELECT productCode, productName, buypriceFROM productsORDER BY buyprice DESC +-+ | productCode | productName | buyprice | +-+-- -+-+ | S10room4962 | 1962 LanciaA Delta 16V | 103.42 | | S18room2238 | 1998 Chrysler Plymouth Prowler | 101.51 | | S10room1949 | 1952 Alpine Renault 1300 | 98.58 | S24room3856 | 1956 Porsche 356A Coupe | 98.3 | S12room1108 | 2001 Ferrari Enzo | | 95.59 | | S1251099 | 1968 Ford Mustang | 95.34 |. +-+ 110 rows in set

Our task is to find the second most expensive 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 at 0, so specify 1, and then take a row of records):

SELECT productCode, productName, buyprice FROM productsORDER BY buyprice DESCLIMIT 1, 1

Execute the above query and get the following results-

Mysql > SELECT productCode, productName, buyprice FROM productsORDER BY buyprice DESCLIMIT 1,1 +-+ | productCode | productName | buyprice | +-+- +-+ | S18002238 | 1998 Chrysler Plymouth Prowler | 101.51 | +-+ 1 row in set

Similarly, the third and fourth most expensive products are: LIMIT 2, 1 and LIMIT 3, 1.

Thank you for reading this article carefully. I hope the article "how to limit the number of queries in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.