在这里主要讲解一下MySQL、SQLServer2000(及SQLServer2005)和ORCALE三种数据库实现分页查询的方法。可能会有人说这些网上都有,但我的主要目的是把这些知识通过我实际的应用总结归纳一下,以方便大家查询使用。
下面就分别给大家介绍、讲解一下三种数据库实现分页查询的方法。
一、MySQL 数据库 分页查询
MySQL数据库实现分页比较简单,提供了 LIMIT函数。一般只需要直接写到sql语句后面就行了。LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数。例如:
select * from table WHERE … LIMIT 10; #返回前10行
select * from table WHERE … LIMIT 0,10; #返回前10行
select * from table WHERE … LIMIT 10,20; #返回第10-20行数据
二、SQLServer数 据库分页查询
SQLServer数据库又分为 SQLServer2000和SQLServer2005。一般比较简单的方法是通过TOP函数来实现。如下:
SELECT TOP 10 * FROM sql WHERE (
code NOT IN (SELECT TOP 20 code FROM TestTable ORDER BY id))
ORDER BY ID
这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时
间长,但事实相反。因为,子句执行后返回的是20条记录,而整条语句仅返回10条语句,所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I
/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL
SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。
以上语句的有一个致命的缺点,就是它含有NOT IN字样,要换成用not exists来代替not in,二者的执行效率实际上是没有区别的。
在以上分页算法中,影响我们查 询速度的关键因素有两点:TOP和NOT IN。TOP可以提高我们的查询速度,而NOT IN会减慢我们的查询速度,所以要提高我们整个分页算法的速度,就要彻底改造NOT IN,同其他方法来替代它。
我们知道,几乎任何字段,我们都可以通过max(字段)或min(字段)来提取某个字段中的最大
或最小值,所以如果这个字段不重复,那么就可以利用这些不重复的字段的max或min作为分水岭,使其成为分页算法中分开每页的参照物。在这里,我们可以
用操作符“>”或“<”号来完成这个使命。如:
Select top 10 * from table1 where id>200
于是就有了如下分页方案:
select top 页大小 *
from table1
where id>
(select max (id) from
(select top ((页码-1)*页大小) id from table1 order by id) as T
)
order by id
这种方法执行 多少始终没有大的降势,后劲仍然很足。尤其对于数据量大的时候,该方法执行速度一点也不会降低。
使用TOP要求主键必须唯一,不能是联合主键。如果是联合主键,则查询出的结果会乱序的。
目前SQLServer2005提供了一个row_number()函数。ROW_NUMBER()
就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID),其中ReportID可
以是联合主键。下面,我们看看怎么具体应用这个RowNo进行分页.
SELECT TOP 10 * FROM
(
SELECT top 10 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM TABLE
) AS A
WHERE RowNo > " + pageIndex*10
pageIndex就是我们需要数据的页数.
但对于SQLServer2000的话,如果是联合主键,我还没有解决办法,如果大家有可跟我联 系。谢谢大家了。
三、ORCALE数据库 分页查询
ORCALE数据库实现分页查询可以使用 row_number()函数或者使用rownum 虚列两种方法。
第一 种:利用分析函数row_number() 方法
select * from(
select t.*,row_number() over (order by t1.id) rowno from TABLE1
)
where rowno between 21 and 40;
第二种:直接 使用rownum 虚列
select * from
(select t.*,rownum as rowno from TABLE1 )
where rowno between 10 and 20
这两种方法比较,显然第二种方法比较好。因为不用order by语句,会提高检索数据的速度的,尤其数据量越大时,第二种方法快速检索数据越明显。
最后提醒大家:oracle中慎用带有order by的分页。尤其是在oracle10g中,会出现会引起混乱,即相同记录会出现在不同页中。
转自:http://blog.csdn.net/q809204304/article/details/17607699
查看更多关于【数据库其他】的文章
去重复的sql(Oracle) 去重复的英文
1.利用group by 去重复2.可以利用下面的sql去重复,如下 1) select id,name,sex from (select a.*,row_number() over(partition by a.id,a.set order by name) su from test a ) where su=1 2)select id,name,sex from (select a.*,row_number() over(p
0评论2023-02-10893
Oracle SQL七次提速技巧
以下SQL执行时间按序号递减。1,动态SQL,没有绑定变量,每次执行都做硬解析操作,占用较大的共享池空间,若共享池空间不足,会导致其他SQL语句的解析信息被挤出共享池。create or replace procedure proc1as beginfor i in 1..100000 loop execute imme
0评论2023-02-10755
SQL ORACLE case when函数用法
case when 用法(1)简单case函数:格式: case 列名 when 条件值1 then 选项1 when 条件值1 then 选项2...... else 默认值 end例如: select case job_level when '1' then '1111' when '2' then '2222' when '3' then '3333
0评论2023-02-10564
mysql下如何执行sql脚本 执行SQL脚本
1.编写sql脚本,假设内容如下: create database dearabao; use dearabao; create table niuzi (name varchar(20)); 保存脚本文件,假设我把它保存在F盘的hello world目录下,于是该文件的路径为:F:\hello world\niuzi.sql2.执行sql脚本,可以有2种方法:
0评论2023-02-10699
MySQL 5.7版本sql_mode=only_full_group_by问题
用到GROUP BY 语句查询时com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on colu
0评论2023-02-10973
ORACLE中通过SQL语句(alter table)来增加、删除、修改字段
1.添加字段:alter table 表名 add (字段 字段类型) [ default '输入默认值'] [null/not null] ;2.添加备注:comment on column 库名.表名.字段名 is '输入的备注'; 如: 我要在ers_data库中 test表 document_type字段添加备注 comm
0评论2023-02-10584
MySQL与Oracle 差异比较之六触发器
触发器编号类别ORACLEMYSQL注释1创建触发器语句不同create or replace trigger TG_ES_FAC_UNIT before insert or update or delete on ES_FAC_UNIT for each rowcreate trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_u
0评论2023-02-10914
mysql where条件:某时间字段为今天的sql语句
1.查询:注册时间为今天的所有用户数:select count(*) from customer where TO_DAYS(createtime) = TO_DAYS(NOW())2.获取当前时间到凌晨24点还有多长时间:(Java中可用于判断某时间是否为今天)final Calendar cal = Calendar.getInstance(); ca
0评论2023-02-10717
mysql中的sql
变量用户变量: 在用户变量前加@系统变量: 在系统变量前加@@运算符算术运算符有: +(加), -(减), * (乘), / (除) 和% (求模) 五中运算位运算符有:(位于), | (位或), ^ (位异或), ~ (位取反),(位右移),(位左移)比较运算符有: = (等于),(大于),(小于), = (大
0评论2023-02-10936
Oracle的HINT可以强制指定SQL的执行计划,比如选择索引、表的连接顺序以及表的连接方式等等。(转)
在Oracle中查看所有的表: select * from tab/dba_tables/dba_objects/cat; 看用户建立的表 : select table_name from user_tables; //当前用户的表 select table_name from all_tables; //所有用户的表 select table_name from dba_tables; //包
0评论2023-02-10857
Oracle sql 子字符串长度判断
Oracle sql 子字符串长度判断 select t.* from d_table t WHEREsubstr(t.col,1,1)='8' and instr(t.col,'/')0 and length(substr(t.col,1,instr(t.col,'/')))5; 字符串的前两位都是数字:select * from d_table t WHERE regexp_like(substr(t.col,1,2), '^[
0评论2023-02-10759
Oracle、MySql、Sql Server比对
MySql:廉价(部分免费):当前,MySQL採用双重授权(DualLicensed),他们是GPL和MySQLAB制定的商业许可协议。假设你在一个遵循GPL的***(开源)项目中使用MySQL,那么你能够遵循GPL协议免费使用MySQL。否则,你须要购买MySQLAB制定的那个商业许可协议。Windows $
0评论2023-02-10441
Oracle 存储过程,临时表,动态SQL测试
--创建事务级别的结果临时表create global temporary table tmp_yshy( c1 varchar2(100), c2 varchar2(100))on commit delete rows;--创建事务级别的存储sql语句的临时表create global temporary table tmp_sql( c1 varchar2(4000))on commit delete rows;测
0评论2023-02-10508