postgresql分页查询，limit的使用

一、表结构

二、插入表数据

三、limit子句的select语句

sql语法：

SELECT * FROM teacher LIMIT 3;

运行结果：

总结：

发现这个跟mysql一样，也是查找前三条数据

四、limit子句与 OFFSET 子句一起使用

sql语法：

SELECT * FROM teacher LIMIT pageSize OFFSET pageNum;

sql测试：

1、SELECT * FROM teacher LIMIT 6 OFFSET 0;

2、SELECT * FROM teacher LIMIT 8 OFFSET 2;

3、SELECT * FROM teacher LIMIT 5 OFFSET 4;

运行结果：

sql1：

sql2：

sql3：

总结：

LIMIT 代表查的条数，OFFSET 代表从下标几开始

实际应用场景：LIMIT " + pageSize+ " OFFSET " +pageSize*(pageNum-1)

pageSize 条目
pageNum 当前页

四、资料

/*
 Navicat PostgreSQL Data Transfer

 Source Server         : benji
 Source Server Type    : PostgreSQL
 Source Server Version : 100019
 Source Host           : localhost:5432
 Source Catalog        : test
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 100019
 File Encoding         : 65001

 Date: 02/12/2021 11:39:14
*/


-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS "public"."teacher";
CREATE TABLE "public"."teacher" (
  "id" int2 NOT NULL,
  "teacher_name" varchar(50) COLLATE "pg_catalog"."default",
  "teacher_age" int2,
  "tea_salary" numeric(10,2)
)
;
COMMENT ON COLUMN "public"."teacher"."id" IS '主键ID';
COMMENT ON COLUMN "public"."teacher"."teacher_name" IS '教师名称';
COMMENT ON COLUMN "public"."teacher"."teacher_age" IS '教师年龄';
COMMENT ON COLUMN "public"."teacher"."tea_salary" IS '教师工资';

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO "public"."teacher" VALUES (1, '钟洋兄', 35, 20000.00);
INSERT INTO "public"."teacher" VALUES (2, '王佳琦', 40, 300000.00);
INSERT INTO "public"."teacher" VALUES (3, '运营小姐姐', 18, 40000.00);
INSERT INTO "public"."teacher" VALUES (4, '李逵', 20, 0.00);
INSERT INTO "public"."teacher" VALUES (5, '张飞', 13, 0.00);

-- ----------------------------
-- Primary Key structure for table teacher
-- ----------------------------
ALTER TABLE "public"."teacher" ADD CONSTRAINT "teacher_pkey" PRIMARY KEY ("id");