MySQL、SqlServer、Oracle三大主流数据库分页查询

MySQL

、

SqlServer Oracle

三大主流数据库分页查询

 

MySQL

、

SqlServer

、

Oracle

三大主流数据库分页查询

 

 

在这里主要讲解一下

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 WH

ERE … 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

中，

会出现会引起混乱，即相同记录会出现在不同页中。