SQL全方位攻略：5. SQL “方言”大比拼：Oracle、MySQL、PostgreSQL限制检索行数语法详解(分页查询)

news/2024/7/9 20:40:10 标签: oracle, postgresql, sql

系列文章目录

SQL全方位攻略：1.数据库介绍
SQL全方位攻略：2.SQL介绍
SQL全方位攻略：3.SQL标准
SQL全方位攻略：4. 标准SQL和SQL“方言”

文章目录

  • 系列文章目录
  • SQL “方言”大比拼：Oracle、MySQL、PostgreSQL限制检索行数语法详解(分页查询)
    • 1. Oracle中使用ROWNUM或者ROW_NUMBER()函数实现限制检索行数和分页查询
      • 1. 使用ROWNUM实现分页查询
        • 语法1：随机获取表中的前n条数据
        • 语法2：按照某列排序，获取表中的前n条数据（Top-N）
        • 语法3：实现分页查询
      • 2. 使用ROW_NUMBER()实现分页查询
        • 语法1：获取表中的前n条数据
        • 语法2：实现分页查询
    • 2. MySQL使用LIMIT实现限制检索行数和分页查询
        • 语法1：获取表中的前n条数据
        • 语法2：LIMIT实现分页查询
        • 语法3：LIMIT和OFFSET实现分页查询（兼容PostgreSQL）
    • 3. PostgreSQL使用LIMIT和OFFSET实现限制检索行数和分页查询
        • 语法1：获取表中的前n条数据
        • 语法2：LIMIT和OFFSET实现分页查询
    • 总结

【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

SQL “方言”大比拼：Oracle、MySQL、PostgreSQL限制检索行数语法详解(分页查询)

Oracle、MySQL和PostgreSQL等关系型数据库中基本都支持标准SQL，虽然SQL的基本语法在这些数据库中是类似的，但是在一些特定的功能和语法上仍然存在差异。

在MySQL和PostgreSQL中，可以使用LIMIT和OFFSET关键字来实现分页查询（限制SELECT语句返回结果行数）的操作，而Oracle中则需要使用ROWNUM或者ROW_NUMBER()函数实现。
它们的具体用法如下：

1. Oracle中使用ROWNUM或者ROW_NUMBER()函数实现限制检索行数和分页查询

在Oracle中，使用ROWNUM或者ROW_NUMBER()函数都可以实现分页查询，用于限制SELECT语句返回结果行数。具体操作如下：

1. 使用ROWNUM实现分页查询

ROWNUM是Oracle中一个伪列，用于表示返回结果集中的行数。通过使用ROWNUM和WHERE语句，可以实现分页查询的操作。

语法1：随机获取表中的前n条数据

sql">SELECT *
FROM table_name
WHERE ROWNUM <= n;

语法2：按照某列排序，获取表中的前n条数据（Top-N）

sql">SELECT *
  FROM (SELECT * FROM table_name ORDER BY table_column)
  WHERE ROWNUM < n;

语法3：实现分页查询

sql">SELECT * 
FROM (
    SELECT ROWNUM rn, t.* 
    FROM (
        SELECT * 
        FROM table_name 
        WHERE conditions 
        ORDER BY sort_key
    ) t 
    WHERE ROWNUM <= page_size * page_index
) t1 
WHERE rn > page_size * (page_index - 1);

其中，page_size表示每页显示的记录数，page_index表示当前页数。这个查询语句中，先按照sort_key排序，然后取出前page_size * page_index个记录并加上ROWNUM，再通过WHERE语句过滤出第page_size * (page_index - 1) + 1到page_size * page_index行的记录。

2. 使用ROW_NUMBER()实现分页查询

ROW_NUMBER()函数是Oracle 9i及以上版本中引入的一个窗口函数，在排序后的结果中为每条记录分配一个序号。通过在SELECT语句中使用ROW_NUMBER()函数，并结合OVER子句和WHERE子句，也可以实现分页查询的操作，示例代码如下。

语法1：获取表中的前n条数据

sql">SELECT *
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY column1, column2) AS rownum, column1, column2
  FROM table_name
)
WHERE rownum <= :page_size;

其中，table_name为要查询的表名，column1和column2为要排序的列名，:page_size为每页显示的记录数。

上述SQL语句中，ROW_NUMBER()函数会按照column1和column2两个列的升序排列，并为每一行数据分配一个唯一的序号(rownum)。然后通过WHERE子句限制查询结果只返回前page_size条记录，即每页显示的数据。

需要注意的是，ROW_NUMBER()函数只能用于查询语句中，不能直接用于更新语句中。如果需要在更新语句中对数据进行排序，则需要使用ORDER BY子句。

语法2：实现分页查询

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY sort_key) rn, t.* 
    FROM table_name t 
    WHERE conditions 
) WHERE rn > page_size * (page_index - 1) AND rn <= page_size * page_index;

SQL语句先按照sort_key排序，然后使用ROW_NUMBER()函数为每条记录分配一个序号，再通过WHERE语句过滤出第page_size * (page_index - 1) + 1到page_size * page_index行的记录。

总的来说，使用ROWNUM或者ROW_NUMBER()函数都可以实现Oracle中的分页查询，需要根据具体需求选择合适的语法。同时，需要注意在使用ROWNUM时要先排序再进行筛选，而使用ROW_NUMBER()则可以在OVER子句中指定排序方式。

参考：
ROWNUM Pseudocolumn
ROW_NUMBER

2. MySQL使用LIMIT实现限制检索行数和分页查询

MySQL中同样提供了限制检索行数语法，但是和Oracle不同使用LIMIT关键字。
在分页查询中，使用LIMIT还可以将结果集分成多个页面。

LIMIT {[offset,] row_count | row_count OFFSET offset}

语法1：获取表中的前n条数据

MySQL使用LIMIT关键字来限制返回结果集中的行数，语法如下：

sql">SELECT *
FROM table_name
LIMIT n;

其中，n为希望返回行数的值。
例：

sql">mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.03 sec)

mysql> select * from actor limit 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

语法2：LIMIT实现分页查询

为了实现分页查询，需要限制结果集中的行数同时还要跳过一定的行数，使用LIMIT可以将结果集分成多个页面，下面是LIMIT语法的详细说明：

sql">SELECT *
FROM table_name
LIMIT offset, row_count;

语法说明：

  • offset表示查询结果的起始位置，即跳过多少条记录，取下一条记录作为返回记录。
  • row_count表示要返回的记录数，也就是取几条记录。例：如果row_count为5，表示返回5条记录。
  • 如果只给出LIMIT row_count，则默认offset为0，从第1条记录开始返回row_count条记录，即LIMIT row_count 等于 LIMIT 0, row_count。
  • 如果查询结果不足指定的row_count条记录，则返回实际可用的记录数。

例：查询第2到12条记录

sql">mysql> select * from actor limit 2,10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
|       11 | ZERO       | CAGE         | 2006-02-15 04:34:33 |
|       12 | KARL       | BERRY        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

mysql>

语法3：LIMIT和OFFSET实现分页查询（兼容PostgreSQL）

为了兼容PostgreSQL，MySQL也支持LIMIT row_count OFFSET offset语法。
可以使用LIMIT和OFFSET关键字，语法如下：

SELECT *
FROM table_name
LIMIT n OFFSET m;

其中，n为希望返回的行数，m为需要跳过的行数。

对于上面的【查询第2到12条记录】的例子，还可以用LIMIT row_count OFFSET offset语法实现。
例：

sql">mysql> select * from actor limit 10 OFFSET 2;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
|       11 | ZERO       | CAGE         | 2006-02-15 04:34:33 |
|       12 | KARL       | BERRY        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

mysql>

参考：

https://dev.mysql.com/doc/refman/8.0/en/select.html

3. PostgreSQL使用LIMIT和OFFSET实现限制检索行数和分页查询

在 PostgreSQL 中，可以使用 LIMIT 和 OFFSET 子句来限制检索的行数和进行分页查询。

SELECT select_list
FROM table_expression
[ ORDER BY … ]
[ LIMIT { number | ALL } ] [ OFFSET number ]

语法说明：

  • SELECT select_list：从数据库中选取列的列表。您可以指定一个或多个列，使用逗号分隔。如果要检索所有列的数据，也可以使用通配符 *。
  • FROM table_expression：从数据库中选取数据的表名或视图。
  • ORDER BY：按升序或降序排列检索的数据。可以根据一个或多个列进行排序，如果没有指定 ORDER BY 子句，则检索出的数据将以任意顺序返回。
  • LIMIT { number | ALL }：限制要返回的记录数的数量。如果指定为数字，则表示要返回的记录数。如果设置为 ALL，则返回表中的所有记录。
  • OFFSET number：从查询的结果集的开头开始省略前 number 个记录。

语法1：获取表中的前n条数据

要限制要检索的行数，可以仅使用 LIMIT 子句。

sql">SELECT * FROM table_name
LIMIT n;

在 PostgreSQL 的 SELECT 查询语句中，LIMIT 子句指定检索记录的最大数量。如果指定了 LIMIT 子句，将只返回指定数量的记录，即使实际情况下表中存在更多记录。但是，如果查询本身返回的行数少于 LIMIT 子句所指定的数量，则只会返回查询所产生的行数。

例如，假设执行如下查询：

SELECT * FROM my_table LIMIT 10;

如果 my_table 中只有 5 行，则只返回这 5 行，并且 LIMIT 子句将被保留。如果 my_table 中有 20 行，则只返回前 10 行。

语法2：LIMIT和OFFSET实现分页查询

要执行分页查询，需要同时使用 LIMIT 和 OFFSET 子句。
另外，在进行分页查询时，需要确保在表中有某种排序规则，以便在每次查询时都可以得到正确的结果。所以通常需要使用 ORDER BY 子句来指定表中的排序规则。

例如，下面是一个分页查询的示例：

sql">-- 创建表 table1
CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    create_date TIMESTAMP NOT NULL
);

-- 插入一些数据
INSERT INTO table1 (name, create_date) VALUES
('John', '2020-11-01 09:00:00'),
('Mary', '2020-11-02 10:00:00'),
('Tom', '2020-11-03 11:00:00'),
('Kate', '2020-11-04 12:00:00'),
('Bob', '2020-11-05 13:00:00'),
('Lisa', '2020-11-06 14:00:00'),
('Peter', '2020-11-07 15:00:00'),
('Alice', '2020-11-08 16:00:00'),
('David', '2020-11-09 17:00:00'),
('Jane', '2020-11-10 18:00:00'),
('Alex', '2020-11-11 19:00:00'),
('Sarah', '2020-11-12 20:00:00'),
('Mike', '2020-11-13 21:00:00'),
('Lucy', '2020-11-14 22:00:00');

-- 运行查询
SELECT * 
FROM table1 
ORDER BY create_date 
LIMIT 10 
OFFSET 10;

执行结果输出例：

mydb1=# select * from table1;
 id | name  |     create_date
----+-------+---------------------
  1 | John  | 2020-11-01 09:00:00
  2 | Mary  | 2020-11-02 10:00:00
  3 | Tom   | 2020-11-03 11:00:00
  4 | Kate  | 2020-11-04 12:00:00
  5 | Bob   | 2020-11-05 13:00:00
  6 | Lisa  | 2020-11-06 14:00:00
  7 | Peter | 2020-11-07 15:00:00
  8 | Alice | 2020-11-08 16:00:00
  9 | David | 2020-11-09 17:00:00
 10 | Jane  | 2020-11-10 18:00:00
 11 | Alex  | 2020-11-11 19:00:00
 12 | Sarah | 2020-11-12 20:00:00
 13 | Mike  | 2020-11-13 21:00:00
 14 | Lucy  | 2020-11-14 22:00:00
(14 rows)

mydb1=# SELECT *
mydb1-# FROM table1
mydb1-# ORDER BY create_date
mydb1-# LIMIT 10
mydb1-# OFFSET 10;
 id | name  |     create_date
----+-------+---------------------
 11 | Alex  | 2020-11-11 19:00:00
 12 | Sarah | 2020-11-12 20:00:00
 13 | Mike  | 2020-11-13 21:00:00
 14 | Lucy  | 2020-11-14 22:00:00
(4 rows)

mydb1=#

在上面的示例中，我们创建了一个名为 table1 的表，并插入一些数据。然后，我们运行了与原始 SQL 语句相同的查询，检索满足条件的前10行，从第11行开始显示。
我们在 create_date 列上对表进行排序，并检索从第11行开始的10行记录。由于表中仅有14条数据，所以最终的输出为4条数据。

参考：

>7.6. LIMIT and OFFSET

https://www.sql>postgresql.org/docs/14/sql-select.html
https://www.sql>postgresql.org/docs/14/queries-limit.html

总结

本文我们介绍了 Oracle、MySQL 和 PostgreSQL 数据库实现限制检索行数（分页查询）的语法及其不同之处。


http://www.niftyadmin.cn/n/321221.html

相关文章

TCP/IP协议族

TCP/IP协议族

TCP/IP协议族 一系列协议组成的一个网络分层模型 分层 分层原因 网络的不稳定性 具体分层 应用层 决定了向用户提供应用服务时通信的活动&#xff0c;如HTTP、FTP&#xff08;文件传输协议&#xff09;、DNS&#xff08;域名系统&#xff09; 传输层 对上层应用层&…
阅读更多...
【2023华为OD笔试必会25题--C语言版】《24 货币单位换算》——字符串

【2023华为OD笔试必会25题--C语言版】《24 货币单位换算》——字符串

本专栏收录了华为OD 2022 Q4和2023Q1笔试题目，100分类别中的出现频率最高（至少出现100次）的25道，每篇文章包括原始题目 和 我亲自编写并在Visual Studio中运行成功的C语言代码。 仅供参考、启发使用，切不可照搬、照抄，查重倒是可以过，但后面的技术面试还是会暴露的。✨✨…
阅读更多...
数据结构--线段树

数据结构--线段树

写在前面&#xff1a; 学习之前需要知道以下内容&#xff1a; 1. 递归 2. 二叉树 文章目录 线段树介绍用途建树修改单点修改区间修改 查询 代码实现。建树更新lazy传递查询 练习洛谷 P3372 【模板】线段树 1题目描述题解 线段树 介绍 线段树是一种二叉树&#xff0c;也可以…
阅读更多...
css的clip-path学习

css的clip-path学习

文章目录 clip-path的几个值polygon多边形circle圆形ellipse椭圆形inset 矩形round后面是四个角的度数 一个简单的应用&#xff0c;比如画一段曲线 参考博文 clip-path的几个值 自己学习后&#xff0c;先把clip-path理解为在原图上绘制轮廓&#xff0c;显示的内容是轮廓内的内…
阅读更多...
柯里化详解

柯里化详解

柯里化详解 介绍玩具版柯里化真正的柯里化&#xff08;面试必备&#xff09; 介绍 柯里化&#xff08;Currying&#xff09;是一种函数式编程技术&#xff0c;它将一个接受多个参数的函数转换为一系列接受单一参数的函数。柯里化的主要目的是让函数更易于复用和组合。通过柯里…
阅读更多...
基于企业网的信息安全防护系统的设计与实现_kaic

基于企业网的信息安全防护系统的设计与实现_kaic

摘 要 这篇文章提供了一种新的&#xff0c;基于“禁止一切”安全策略和VxD技术的企业网络安全保障方案&#xff0c;旨在帮助企业更好地应对外界的威胁。通过设计一套完善的企业网信息安全防护系统&#xff0c;不仅可以有效地解决企业网络中存在的问题&#xff0c;而且还可以有…
阅读更多...
Testing and fault tolerence考试要点

Testing and fault tolerence考试要点

文章目录 ATPGFault modelScanFunctional testMemory BISTLogic BISTboundary scanATEIddq testingFault tolerant designRisk analysis ATPG ATPG工作流程fault collapsing的原则 Fault model 有哪些fault model以及他们的工作原理 Scan Scan寄存器结构Scan Chain的连接方…
阅读更多...
Cypher的使用说明

Cypher的使用说明

一、简介 1.1 常用的命令和函数 Cypher查询语言也叫做CQL。 常用的命令如下&#xff1a; CQL命令作用用法CREATE创建创建节点&#xff0c;关系和属性MATCH匹配检索有关节点&#xff0c;关系和属性数据RETURN返回返回查询结果WHERE哪里提供条件过滤检索数据DELETE删除删除节…
阅读更多...
最新文章

Copyright @ 2022~2023