Mysql 简明教程
MySQL - Limit
MySQL Limit Clause
MySQL 中的 LIMIT 子句可用于指定要返回的记录数量。该子句通常在处理数千条记录的表时使用。它接受一个或两个参数（偏移或计数）。两个参数的值均应为正整数或零。
The LIMIT clause in MySQL can be used to specify the number of records to return. This clause is mostly used when dealing with tables that have thousands of records. It accepts one or two arguments (offset or count). The values of both arguments should be either be positive integers or zero.
第一行的 offset 从 0 开始，而不是从 1 开始，并且第一行的计数从 1 开始。让我们使用以下图片来更好地理解它：
The offset of the first row starts from 0, not from 1 and the count of the first row starts from 1. Let us understand it better using the following picture:
假设上述表的名称为 students 。如果我们执行上述查询，我们将会得到 Mahika、Aarohi 和 Nikhil 这样的输出。
Assume the name of the above table is students. If we execute the above-mentioned query, we will get the output as Mahika, Aarohi, and Nikhil.
Syntax
以下是 MySQL Limit 子句的通用语法：
Following is the generic syntax of MySQL Limit clause −
SELECT column1, column2, ... FROM table_name
LIMIT number;Example
以下是使用 MySQL Limit 查询的示例。
The following example demonstrates the usage of the MySQL Limit query.
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：
First of all, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);这里，我们使用以下 INSERT INTO 语句向上面创建的表中插入 7 条记录：
Here, we are inserting 7 records into the above-created table using the following INSERT INTO statement −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );执行以下查询以提取 CUSTOMERS 表中显示的所有记录：
Execute the following query to fetch all the records present in the CUSTOMERS table −
SELECT * FROM CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，我们使用 MySQL LIMIT 子句配合 SELECT 语句从 CUSTOMERS 表中选择前四条记录：
Now, we are selecting the first four records from the CUSTOMERS table using the MySQL LIMIT clause in conjunct with SELECT statement −
SELECT * FROM CUSTOMERS LIMIT 4;Output
如同我们从以下输出中看到的，它返回了 CUSTOMERS 表中的前四行：
As we can see the output below, it returned the first four rows from the CUSTOMERS table −
LIMIT with WHERE Clause
在 MySQL 中，我们可以在 SELECT 语句中使用 LIMIT 子句和 WHERE 子句来指定根据条件从查询返回的行数。
In MySQL, we can use the LIMIT clause along with the WHERE clause in a SELECT statement to specify the number of rows returned from the query based on the conditions.
Syntax
以下是通用语法：
Following is the generic syntax −
SELECT column1, column2, ... FROM table_name
WHERE condition
LIMIT number;Example
在以下查询中，我们从 CUSTOMERS 表中选择 AGE 大于 21 的前两行：
In the query below, we are selecting the first two rows from the CUSTOMERS table where the AGE is greater than 21 −
SELECT * FROM CUSTOMERS WHERE AGE > 21 LIMIT 2;LIMIT with ORDER BY clause
ORDER BY 子句将按指定顺序（ASC 或 DESC）对列中的行进行排序。在 MySQL 中，我们可以在 ORDER BY 子句中使用 LIMIT 子句来限制排序结果集中返回的行数。
The ORDER BY clause will sort the rows of a column in the specified order (ASC or DESC). In MySQL, we can use the LIMIT clause along with the ORDER BY clause to limit the number of rows returned in the sorted result set.
Syntax
以下是 MySQL 中 WHERE 子句中 LIMIT 子句的语法：
Following is the syntax of LIMIT clause with WHERE clause in MySQL −
SELECT column1, column2, ... FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number;Example
在以下查询中，我们从 CUSTOMERS 表中提取所有记录，并将 SALARY 列按降序排列。然后我们从排序好的结果集中提取 5 行。
In the below query, we are fetching all the records from the CUSTOMERS table and sorting the SALARY column in descending order. Then we are fetching 5 rows from the sorted result set.
SELECT * FROM CUSTOMERS
ORDER BY SALARY DESC
LIMIT 5;Output
如果我们编译并运行上述查询，则结果将生成如下：
If we compile and run the above query, the result is produced as follows −
Example
这里，我们从表中选择所有记录，并将 SALARY 列按升序排列。然后我们从排序好的结果集中从第二行（偏移量 1）开始提取三行：
Here, we are selecting all the records from the table and sorting the SALARY column in ascending order. Then we are fetching rows from the sorted result set starting from the second row (offset 1) from then three rows −
SELECT * FROM CUSTOMERS
ORDER BY SALARY ASC
LIMIT 1,3;Limit Clause Using a Client Program
除了限制 MySQL 查询从表中返回的记录数之外，我们还可以使用客户端程序来执行 LIMIT 操作。
In addition to limiting the number of records returned from a table with a MySQL query, we can also use a client program to perform the LIMIT operation.