系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

mysql 使用技巧 分页limit

时间:2020-04-19来源:电脑系统城作者:电脑系统城

mysql 分页使用 limit关键字,limit x,y (x代表从哪条数据开始,y代表页面大小。mysql第一条数据在limit计算时索引为0)
复制代码
limit 10
  前10条
limit 0,10
  从第1条开始的10条
limit 10,10
  从第 11 条开始的 10 条
limit 100,10
  从第101条开始的10条
  数据量大时(>千万),效率低

oracal 分页,使用 oracle的特殊列 rownum
select * from
(select *,rownum R from
(select * from a)
复制代码

实例:查找入职员工时间排名倒数第三的员工所有信息。limit 2,1代表选择从第3条数据开始的1条数据,即第3页数据,页面大小为1

select * from employees order by hire_date desc limit 2,1

 

前n条数据,即从索引0开始计算:limit n 或者 limit 0,n

复制代码
mysql> select * from employees order by hire_date desc limit 5;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|         173 | Sundita    | Kumar     | SKUMAR  | 011.44.1343.329268 | 2000-04-21 | SA_REP   | 6100.00 |           0.10 |        148 |            80 |
|         167 | Amit       | Banda     | ABANDA  | 011.44.1346.729268 | 2000-04-21 | SA_REP   | 6200.00 |           0.10 |        147 |            80 |
|         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
|         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
|         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from employees order by hire_date desc limit 0,5;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|         173 | Sundita    | Kumar     | SKUMAR  | 011.44.1343.329268 | 2000-04-21 | SA_REP   | 6100.00 |           0.10 |        148 |            80 |
|         167 | Amit       | Banda     | ABANDA  | 011.44.1346.729268 | 2000-04-21 | SA_REP   | 6200.00 |           0.10 |        147 |            80 |
|         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
|         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
|         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
5 rows in set (0.00 sec)
复制代码

 

更多例子 

limit 5     前5条数据
limit 2,1   第3页数据,页面大小为1
limit 2,2   第2页数据,页面大小为2
limit 2,3   第3条数据起,共3条数据,此数据不符合分页数据显示格式
分页显示格式:limit startIndex pageSize
startIndex = (需要查询的页码数 - 1) * pageSize
复制代码
mysql> select * from employees order by hire_date desc limit 5;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|         173 | Sundita    | Kumar     | SKUMAR  | 011.44.1343.329268 | 2000-04-21 | SA_REP   | 6100.00 |           0.10 |        148 |            80 |
|         167 | Amit       | Banda     | ABANDA  | 011.44.1346.729268 | 2000-04-21 | SA_REP   | 6200.00 |           0.10 |        147 |            80 |
|         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
|         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
|         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from employees order by hire_date desc limit 2,1;
+-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email | phone_number       | hire_date  | job_id | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
|         166 | Sundar     | Ande      | SANDE | 011.44.1346.629268 | 2000-03-24 | SA_REP | 6400.00 |           0.10 |        147 |            80 |
+-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
1 row in set (0.00 sec)

mysql> select * from employees order by hire_date desc limit 2,2;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
|         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from employees order by hire_date desc limit 2,3;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|         166 | Sundar     | Ande      | SANDE   | 011.44.1346.629268 | 2000-03-24 | SA_REP   | 6400.00 |           0.10 |        147 |            80 |
|         128 | Steven     | Markle    | SMARKLE | 650.124.1434       | 2000-03-08 | ST_CLERK | 2200.00 |           NULL |        120 |            50 |
|         165 | David      | Lee       | DLEE    | 011.44.1346.529268 | 2000-02-23 | SA_REP   | 6800.00 |           0.10 |        147 |            80 |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
3 rows in set (0.00 sec)
复制代码
分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载