mysql memory limit_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 LIMITclause 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 offsetand countmean in the LIMIT clause:The offsetspecifies the offset of the first row to return. The offsetof the first row is 0, not 1.

The countspecifies maximum number of rows to return.

When you use LIMITwith 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 LIMITclause that accepts two arguments:SELECT * FROM tbl LIMIT 0, count

Using MySQL LIMIT to get the first N rows

You can use the LIMITclause 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;

Using MySQL LIMIT to get the highest and lowest values

The LIMITclause 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 LIMITclause 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;

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;

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;

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 LIMITclause 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

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.

Related Tutorials

原文链接：http://outofmemory.cn/mysql/mysql-limit