In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces "how to use the database paging query sentence to query the database". In the daily operation, I believe many people have doubts about how to use the database paging query sentence to query the database. The editor consulted all kinds of materials and sorted out a simple and easy-to-use operation method. I hope it will be helpful to answer the doubt of "how to use the database paging query sentence to query the database". Next, please follow the editor to study!
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
If you have tens of thousands of data, you can directly use the common usage of the function limit that comes with mysql to ok. If it is more than 1 million data, you may need to talk about the method. Let's do a paging query statement for million-level data. MySQL > select * from news where id > = (select id from news limit 490000Magne1) limit 10 / / 0.18 sec / / obviously, this method wins. MySQL > select * from news limit 4900 sec;*/ 10 / / 0.22
The following article mainly introduces the actual operation scheme of MySQL paging. In fact, the easiest way to realize MySQL paging is to use the LIMIT function of mysql database. 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, 20 select * from table [query conditions] order by id limit?
Oracle
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. times, ROWNUM RNFROM (SELECT * FROM TABLE_NAME) AWHERE ROWNUM = 21 this is the end of the study on "how to use database paging query statements for database query", hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.