MySQL LIMIT使用介绍-原创手记-慕课网

Summary: in this tutorial, you will learn how to use MySQL LIMIT clause to select records from the beginning, middle and end of a result set.

MySQL LIMIT syntax

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMITclause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.

The following illustrates the LIMIT clause syntax with 2 arguments:

SELECT * FROM tbl LIMIT offset, count

Let’s see what the offset and count mean in the LIMIT clause:

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The count specifies maximum number of rows to return.

When you use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.

SELECT * FROM tbl LIMIT count

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

SELECT * FROM tbl LIMIT 0, count

Using MySQL LIMIT to get the first N rows

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

SELECT * FROM tbl LIMIT N

For example, to select the first 10 customers, you use the following query:

SELECT customernumber,        customername,        creditlimit FROM customers LIMIT 10;

mysql limit first n rows

Using MySQL LIMIT to get the highest and lowest values

The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on a certain criteria, and then you use LIMIT clause to find lowest or highest values.

For example, to select 5 customers who have the highest credit limit, you use the following query:

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

mysql limit get highest values

And the following query returns 5 customers who have the lowest credit limit:

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

mysql limit get lowest values

Using MySQL LIMIT to get the N highest values

One of the toughest questions in MySQL is how to select the N highest values in a result set e.g., select the second most expensive product, which you cannot use MAX or MIN functions to answer. However, you can use MySQL LIMIT to answer those kinds of questions.

Let’s take a look at the products result set of the following query:

SELECT productName,        buyprice FROM products ORDER BY buyprice DESC;

MySQL LIMIT products table

Our task is to get the highlight product, which is the second most expensive product in the products result set. In order to do so, you use LIMIT clause to select 1 row from the second row as the following query: (notice that the offset starts from zero)

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

mysql limit most second expensive product

In this tutorial, you have learned how to use MySQL LIMIT clause to select records from the beginning, the middle and the end of a result set.