MySQL Oracle and SQL Server paging query example analysis

Recently, I have done a simple study on the data paging query of Oracle, MySQL, and SQL Server 2005, and posted the query statements of each for everyone to learn...

(I) Paging query of MySQL

MySQL's paging query is the simplest. You can use the keyword limit to query. The general query statement is:

select o.*from(sql) o limit firstIndex,pageSize

As shown in the following screenshot, the number of records displayed per page is 20:

Query (1-20) these 20 records

Query these 20 records (21-40)

MySQL paging query is so simple...

(II) Paging query of sqlserver2005

Before SQL Server 2005, the top keyword was used to implement paging query, but the efficiency was low. In SQL Server 2005 and later versions, the row_number() analytical function is used to complete paging query, which has greatly improved the efficiency. However, the SQL statement is more complicated. The general formula of paging query is given below:

selecttoppageSizeo.*from(selectrow_number()over(orderbyorderColumn)asrownumber,*from(sql)asowhererownumber>firstIndex;

Look at the screenshot below, 20 records are displayed per page:

Query (1-20) these 20 records

Query these 20 records (21-40)

Knowing the row_number function in sqlserver, paging is easy...

(III) Oracle paging query

Next, let's focus on Oracle's paging query. Oracle has relatively more paging query methods, ROWNUM and row_number(). Today we will mainly focus on two paging query statements with slightly better efficiency.

①ROWNUM query paging formula:

select*from(selecta.*,ROWNUMrn from(sql)a whereROWNUM<=(firstIndex+pageSize))wherern>firstIndex

The following screenshot shows a query executed in this way:

Query (1-21) these 20 records***** (there is no record with ID=6, so the maximum ID queried is 21)

Query (22-41) these 20 records***** (there is no record with ID=6, so the first ID to be queried is 22, and the maximum ID is 41)

②row_number() parsing function paging query method:

select*from(select*from(selectt.*,row_number()over(orderbyorderColumn)asrownumberfrom(sql)t) p wherep.rownumber>firstIndex)whererownum<=pageSize

The following screenshot shows the paging query effect using row_number():

Query (1-21) these 20 records***** (there is no record with ID=6, so the maximum ID queried is 21)

Query (22-41) these 20 records***** (there is no record with ID=6, so the first ID to be queried is 22, and the maximum ID is 41)

In the ROWNUM query method, there is a " where ROWNUM<(firstIndex+pageSize)order by idasc in the second-level SQL statement, which queries (firstIndex+pageSize) records from the data table. So if this value is very small, the efficiency will be very good. If this value is thousands for a large data form, for example: select* from wyuse where rownum<(5000) order by idasc , 5000 records will be selected at the beginning, and the efficiency will naturally be much slower....

Summarize

The above is the introduction of MySQL Oracle and SQL Server paging query by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!