Oracle、 Mysql 、 SQLserver 分页查询

MYSQL 分页最简单了. SELECT * FROM Account  WHERE (usertype='base' or usertype='home' or usertype='salse')  and logindate is not null order by logindate desc LIMIT 起始行, 每页多少行 LIMIT 接受一个或两个数字参数。 参数必须是一个整数常量。 如果给定两个参数,第一个参数指定第一个返回记录行的偏移量, 第二个参数指定返回记录行的最大数目。 初始记录行的偏移量是 0(而不是 1)

SQLServer 的数据分页: 假设现在有这样的一张表: CREATE TABLE test (  id int primary key not null identity,  names varchar(20) ) 然后向里面插入大约1000条数据,进行分页测试 假设页数是10,现在要拿出第5页的内容,查询语句如下: --10代表分页的大小 select top 10 * from test where id not in (  --40是这么计算出来的:10*(5-1)  select top 40 id from test order by id ) order by id 原理:需要拿出数据库的第5页,就是40-50条记录。首先拿出数据库中的前40条记录的id值,然后再拿出剩余部分的前10条元素 第二种方法: 还是以上面的结果为例,采用另外的一种方法 --数据的意思和上面提及的一样 select top 10 * from test where id > (  select isnull(max(id),0)  from    (    select top 40 id from test order by id   ) A ) order by id 原理:先查询前40条记录,然后获得其最id值,如果id值为null的,那么就返回0 然后查询id值大于前40条记录的最大id值的记录。 这个查询有一个条件,就是id必须是int类型的。 第三种方法: select top 10 * from  (  select row_number() over(order by id) as rownumber,* from test ) A where rownumber > 40 原理:先把表中的所有数据都按照一个rowNumber进行排序,然后查询rownuber大于40的前十条记录 这种方法和oracle中的一种分页方式类似,不过只支持2005版本以上的 第四种: 存储过程查询 创建存储过程 alter procedure pageDemo @pageSize int, @page int AS declare @temp int set @temp=@pageSize*(@page - 1) begin  select top (select @pageSize) * from test where id not in (select top (select @temp) id from test) order by id end 执行存储过程 exec 10,5

Oracle分页查询语句(一) Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。 分页查询格式: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21 其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。 上面给出的这个分页查询语句,在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,将结果尽快的返回。在上面的分页查询语句中,这种考虑主要体现在WHERE ROWNUM <= 40这句上。 选择第21到40条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值,在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句,在查询的最外层控制分页的最小值和最大值。这是,查询语句如下: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40 对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。 这是由于CBO优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。对于第一个查询语句,第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。 而第二个查询语句,由于查询条件BETWEEN 21 AND 40是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。 上面分析的查询不仅仅是针对单表的简单查询,对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。 这里就不对包含排序的查询进行说明了,下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询,CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN(MERGE JOIN效率比HASH JOIN效率低,一般CBO不会考虑)。在这里,由于使用了分页,因此指定了一个返回的最大记录数,NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层,而HASH JOIN必须处理完所有结果集(MERGE JOIN也是)。那么在大部分的情况下,对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率(分页查询的时候绝大部分的情况是查询前几页的数据,越靠后面的页数访问几率越小)。 因此,如果不介意在系统中使用HINT的话,可以将分页的查询语句改写为: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏惨绿少年

MySQL用户管理及SQL语句详解

1.1 MySQL用户管理 1.1.1 用户的定义 用户名+主机域 mysql> select user,host,password from mysql.u...

30800
来自专栏10km的专栏

mysql/jdbc:设置useInformationSchema=true读取表注释信息(table_comment)

问题描述 今天在读取表的注释信息(COMMENT)时,发现返回的REMARKS字段返回居然是null. 以下是代码示例: DatabaseMetaData...

29770
来自专栏琯琯博客

Yii2 学习笔记之数据库篇

46770
来自专栏JMCui

MySQL数据库规约.

一、建表规约 1、表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint(1 表示是, 0 表示否) 。 2、表...

37450
来自专栏技术碎碎念

sql server 2008 数据库的完整性约束

一、数据库完整性概述 1.数据库的完整性: ①数据库的完整性是指数据的正确性和相容性 ②数据库完整性是防止不合语义或不正确的数据进入数据库 ③完整性体现了是否真...

34940
来自专栏Django Scrapy

day2 oracle相关

数据库定义有三: 1 一门计算机学科 2 代表某个软件 3 某一种数据库软件产生的集合 DDL 数据库定义语句 DML 数据库操作语句 创建一张表: ...

32090
来自专栏逸鹏说道

SQL Server 深入解析索引存储(中)

概述 本篇文章是关于堆的存储结构。堆是不含聚集索引的表(所以只有非聚集索引的表也是堆)。堆的 sys.partitions 中具有一行,对于堆使用的每...

37180
来自专栏数据和云

轻轻揭开 b*tree 索引结构的神秘面纱

李翔宇 云和恩墨西区技术专家 大家好,我是云和恩墨的技术专家李翔宇,今天要为大家分享的主题是《轻轻揭开b*tree索引结构的神秘面纱》。 说到索引,大家应该...

37160
来自专栏Java技术栈

去 BAT 面试,总结了这 55 道 MySQL 面试题!

Mysql数据库软件是一个客户端或服务器系统,其中包括:支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理工具。

2.3K20
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle存储过程

Oracle存储过程 oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数...

44450

扫码关注云+社区

领取腾讯云代金券