Shulou(Shulou.com)05/31 Report--

This article mainly explains the specific usage of "mysql paging query sentence". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the specific usage of mysql paging query sentence.

SQL Server

There is a lot of information about paging SQL, some using stored procedures and some using cursors. I do not like to use cursors, I think it is expensive and inefficient; using stored procedures is a good choice, because stored procedures are precompiled, efficient and more flexible. Let's first look at the paging SQL of a single SQL statement.

Method 1:

Suitable for SQL Server 2000 Universe 2005

SELECT TOP page size * FROM table1 WHERE id NOT IN (SELECT TOP page size * (number of pages-1) id FROM table1 ORDER BY id) ORDER BY id

Method 2:

Suitable for SQL Server 2000 Universe 2005

SELECT TOP page size * FROM table1 WHERE id > (SELECT ISNULL (MAX (id), 0) FROM (SELECT TOP page size * (number of pages-1) id FROM table1 ORDER BY id) A) ORDER BY id

Method 3:

For SQL Server 2005

SELECT TOP page size * FROM (SELECT ROW_NUMBER () OVER (ORDER BY id) AS RowNumber,* FROM table1) A WHERE RowNumber > page size * (number of pages-1)

Description, page size: the number of rows per page; number of pages: which page. When using, please replace "page size" and "page size * (number of pages-1)" with numbers.

MYSQL

SELECT * FROM TT LIMIT 1 and 20

SELECT * FROM TT LIMIT 21

/ *

If you are tens of thousands of data, directly use the common usage of mysql's own function limit on ok, if it is more than 1 million of the data, may be about to talk about the method, let's do a million-level data paging query statement.

Mysql > select * from news where id > = (select id from news limit 4900 sec 1) limit 10; / / 0.18 sec / / obviously, this method wins.

Mysql > select * from news limit 490000Ji 10 / / 0.22 sec

, /

The following article mainly introduces the actual operation scheme of MySQL paging. In fact, the easiest way to realize MySQL paging is to use mysql's LIMIT function. The statement that LIMIT [offset,] rows can retrieve N records from the M record in the MySQL database table is as follows:

SELECT * FROM table name LIMIT Mpenn

For example, 20 records are retrieved from the 10th record from the table Sys_option (the primary key is sys_id). The statement is as follows:

Select * from sys_option limit 10 and 20

Select * from table [query conditions] order by id limit?

The paging query statement of Oracle can basically follow this article, and the next article will explain it through examples. Let's briefly discuss the situation of multi-table federation. For the most common equivalent table join queries, CBO may generally use two join methods: NESTED LOOP and HASH JOIN (MERGE JOIN is less efficient than HASH JOIN, and generally CBO will not consider it). Here, because paging is used, a maximum number of records is specified, and NESTED LOOP can immediately contain and return the results to the central layer when the number of records crosses the maximum, while HASH JOIN must process all sets (as does MERGE JOIN). Then in most cases, it is more efficient to choose NESTED LOOP as the join method for the paging query (the vast majority of the paging query is to query the data of the first few pages, the more the number of pages behind, the smaller the access probability).

Therefore, if you don't mind using HINT in the system, you can rewrite the paged query statement as follows:

SELECT / * + FIRST_ROWS * / * FROM

(

SELECT A.V., ROWNUM RN

FROM (SELECT * FROM TABLE_NAME) A

WHERE ROWNUM = 21

Thank you for your reading, the above is the content of "specific usage of mysql paging query sentence". After the study of this article, I believe you have a deeper understanding of the specific usage of mysql paging query sentence, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.