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

How to achieve paging query in the database, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1. Paging query of MySQL database

MySQL database is relatively simple to implement paging and provides LIMIT functions. Generally, you just need to write directly after the SQL statement.

The LIMIT clause can be used to limit the amount of data returned by the SELECT statement. It has one or two parameters. If two parameters are given, the first parameter specifies the position of the first row returned in all data, starting with 0 (note that it is not 1), and the second parameter specifies the maximum number of rows returned. For example:

Select * from table WHERE... LIMIT 10; # returns the first 10 lines

Select * from table WHERE... LIMIT 0d10; # returns the first 10 lines

Select * from table WHERE... LIMIT 10d20; # returns 10-20 rows of data

II. Paging query of SQLServer database

SQLServer database is divided into SQLServer2000 and SQLServer2005. Generally speaking, a relatively simple method is achieved through the TOP function. As follows:

SELECT TOP 10 * FROM sql WHERE (

Code NOT IN (SELECT TOP 20 code FROM TestTable ORDER BY id))

ORDER BY ID

In theory, the execution time of the whole sentence should be longer than that of the clause, but the opposite is true. Because 20 records are returned after the clause is executed, while only 10 statements are returned by the whole statement, the biggest factor that affects the database response time is the physical Ibig O operation. One of the most effective ways to restrict physical IDUBO operations here is to use the TOP keyword. The TOP keyword is a systematically optimized word in SQL SERVER that is used to extract the first few items or percentages of data.

A fatal disadvantage of the above statement is that it contains the word NOT IN. To replace not in with not exists, there is actually no difference in execution efficiency.

In the above paging algorithm, there are two key factors that affect our query speed: TOP and NOT IN. TOP can improve our query speed, while NOT IN will slow down our query speed, so to improve the speed of our entire paging algorithm, we need to completely reinvent NOT IN and replace it with other methods.

We know that almost any field, we can use max (field) or min (field) to extract the maximum or minimum value of a field, so if this field is not repeated, then we can use the max or min of these non-repetitive fields as a watershed to make it a reference for separating each page in the paging algorithm. Here, we can use the operator ">" or "

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.