MySQL(29)如何使用LIMIT限制查询结果数量？

在SQL查询中，LIMIT 子句用于限制查询结果的数量。这对于分页显示、减少响应时间以及控制数据量非常有用。LIMIT 子句可以单独使用，也可以与 OFFSET 子句结合使用，以实现更灵活的分页控制。

基本语法

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET start_position;

示例数据库和表结构

假设我们有一个公司数据库，其中有两个表：employees 和 departments。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建表 departments
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- 创建表 employees
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 插入示例数据到 departments 表
INSERT INTO departments (dept_name)
VALUES 
('HR'),
('Engineering'),
('Marketing');

-- 插入示例数据到 employees 表
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date, dept_id)
VALUES 
('John Doe', 'Manager', 75000.00, '2023-10-01', 1),
('Jane Smith', 'Developer', 60000.00, '2023-09-01', 2),
('Alice Johnson', 'Analyst', 50000.00, '2023-08-01', 1),
('Bob Brown', 'Developer', 70000.00, '2023-07-01', 2),
('Charlie Brown', 'Tester', 55000.00, '2023-11-01', 3);

使用 LIMIT 子句的示例

1. 简单使用 LIMIT 限制查询结果数量

获取前3名员工的信息：

SELECT * FROM employees
LIMIT 3;

结果：

emp_id | emp_name      | emp_position | emp_salary | hire_date  | dept_id
-------|---------------|--------------|------------|------------|--------
1      | John Doe      | Manager      | 75000.00   | 2023-10-01 | 1
2      | Jane Smith    | Developer    | 60000.00   | 2023-09-01 | 2
3      | Alice Johnson | Analyst      | 50000.00   | 2023-08-01 | 1

2. 使用 LIMIT 和 OFFSET 进行分页查询

获取第二页的员工信息，每页显示2条记录：

SELECT * FROM employees
LIMIT 2 OFFSET 2;

结果：

emp_id | emp_name      | emp_position | emp_salary | hire_date  | dept_id
-------|---------------|--------------|------------|------------|--------
3      | Alice Johnson | Analyst      | 50000.00   | 2023-08-01 | 1
4      | Bob Brown     | Developer    | 70000.00   | 2023-07-01 | 2

使用 LIMIT 进行排序和分页

3. 按薪水降序排序并获取前2名员工

SELECT * FROM employees
ORDER BY emp_salary DESC
LIMIT 2;

结果：

emp_id | emp_name | emp_position | emp_salary | hire_date  | dept_id
-------|----------|--------------|------------|------------|--------
1      | John Doe | Manager      | 75000.00   | 2023-10-01 | 1
4      | Bob Brown| Developer    | 70000.00   | 2023-07-01 | 2

4. 按入职日期升序排序并获取第二页的员工信息，每页显示2条记录

SELECT * FROM employees
ORDER BY hire_date ASC
LIMIT 2 OFFSET 2;

结果：

emp_id | emp_name      | emp_position | emp_salary | hire_date  | dept_id
-------|---------------|--------------|------------|------------|--------
2      | Jane Smith    | Developer    | 60000.00   | 2023-09-01 | 2
1      | John Doe      | Manager      | 75000.00   | 2023-10-01 | 1

结合 JOIN 使用 LIMIT

5. 结合 JOIN 查询并限制结果数量

获取每个部门的名称以及对应的员工信息，并限制结果为前3条记录：

SELECT d.dept_name, e.emp_name, e.emp_position, e.emp_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LIMIT 3;

结果：

dept_name   | emp_name      | emp_position | emp_salary
------------|---------------|--------------|-----------
HR         | John Doe      | Manager      | 75000.00
Engineering| Jane Smith    | Developer    | 60000.00
HR         | Alice Johnson | Analyst      | 50000.00

小结

通过使用 LIMIT 子句，您可以轻松地控制查询结果的数量。LIMIT 子句可以与 OFFSET 结合使用，以实现分页显示。通过结合 ORDER BY 子句，您还可以对结果进行排序并分页显示。上述示例展示了不同情况下如何使用 LIMIT 子句进行限制查询结果，这在数据展示、分页和性能优化方面非常有用。