多数据库学习之常见数据库分页查询SQL语法

分页查询SQL语法"/>

多数据库学习之常见数据库分页查询SQL语法

多数据库学习之常见数据库分页查询SQL语法

• 前言
• MySQL
• Oracle
• SQL Server
• DB2
• PostgreSQL
• Hana
• 参考连接

前言

• 分页原理

  客户端通过传递 start(页码)，pageSize(每页显示的条数) 两个参数去分页查询数据库表中的数据。一般数据库提供的分页函数用法和实际需求不切合(如：MySQL limit m,n)，所以就需要根据实际情况去改写适合分页的语句。

• 计算公式

  offset = (start-1)*pageSize

  limit = pageSize

• PageBean

  package com.nbspmon.bean;import lombok.Data;import java.io.Serializable;import java.util.List;@Datapublic class PageBean<T> implements Serializable {private int pageNum; //当前页数private int pageSize; //每页显示数private int totalPage; //总页数private int totalRecord; //总的记录数private List<T> data; //当前页面的数据集合private int start;private int end;public PageBean() {}public PageBean(int pageNum, int pageSize, int totalRecord) {this.pageNum = pageNum;this.pageSize = pageSize;//计算总页数this.totalPage=totalRecord%pageSize==0?(totalRecord/pageSize):(totalRecord/pageSize+1);//计算每页的起始下标this.start=(pageNum-1)*pageSize;this.end=this.start+pageSize;}}

MySQL

• 概述

  OceanBase 和 MariaDB 与MySQL类似

• SQL样例

  分页 sql 格式是：

  select * from table limit (start-1)*pageSize,pageSize -- 查询第11条到第20条的数据 select * from table limit 10,10;SELECT * FROM (SELECT t.table_schema, t.table_name AS name, t.table_comment AS comment FROM information_schema.tables t WHERE t.table_type = 'BASE TABLE' AND t.table_schema IN ('xxxx')) tt LIMIT ?, 10000

Oracle

• 概述

  DM 和 Oracle 类似，但分页有区别，DM的分页与MySQL语法一致

• SQL样例

  分页 sql 格式是：

  SELECT * FROM (SELECT TMP.*, ROWNUM ROW_ID FROM ( %s ) TMP WHERE ROWNUM <= limit ) WHERE ROW_ID > offset

  其中：ROWNUM 是从1开始计数

  -- 查询第1条数据到10000条数据SELECT *FROM (SELECT TMP.*, ROWNUM ROW_IDFROM (SELECT t.owner table_schema, t.table_name name, tcments "COMMENT"FROM all_tables tLEFT JOIN all_tab_comments tc ON t.owner = tc.owner AND t.table_name = tc.table_nameWHERE 1 = 1AND t.owner IN ('xxxx')) TMPWHERE ROWNUM <= 10000)WHERE ROW_ID > 0

SQL Server

• 概述

  SQL Server是多DB多Schema

• SQL样例

  分页格式：

  SELECT SCHEMA_NAME(t.schema_id) table_schema, t.name, f.value commentFROM sys.objects tLEFT JOIN sys.extended_properties f ON t.object_id = f.major_id AND f.minor_id = 0WHERE t.type = 'U'AND SCHEMA_NAME(t.schema_id) IN ('xxx') ORDER BY 1 OFFSET ? ROWSFETCH NEXT ? ROWS ONLY;

  其中：

  ORDER BY 是必须的，一般后面跟查询的columns列表，不排序采用 1

  -- 查询第1条到第10000条的数据 SELECT SCHEMA_NAME(t.schema_id) table_schema, t.name, f.value commentFROM sys.objects tLEFT JOIN sys.extended_properties f ON t.object_id = f.major_id AND f.minor_id = 0WHERE t.type = 'U'AND SCHEMA_NAME(t.schema_id) IN ('xxx') ORDER BY 1 OFFSET 0 ROWSFETCH NEXT 10000 ROWS ONLY;

DB2

PostgreSQL

• 概述

Hana

• 概述

  内存型数据库，实测发现分页越往后，查询效率基本一致

• SQL样例

  分页 sql 格式是：

  select * from table limit n offset m -- 查询第1条到第10000条的数据 SELECT *FROM (SELECT t.SCHEMA_NAME AS table_schema,t.TABLE_NAME  AS name,t.COMMENTS    AS comment,t.IS_TEMPORARY,t.HAS_PRIMARY_KEYFROM SYS.TABLES tWHERE 1 = 1AND t.schema_name IN ('xxx')) tt LIMIT 10000OFFSET 0

参考连接

• Java-常用实现分页查询