Organize the third training materials (Summary of Several SQL pages) of the online studio after the summer vacation

In the actual development process, if the number of records is very large, it would be terrible to query and fill the data table with SQL statements directly. In addition, the website performance and server performance are greatly consumed.

Two common mistakes:

1) When querying real data, all the data that meets the conditions will be filled in the DataTable, and a part of the data will be displayed in the program according to the conditions.

2) in statistical data, when obtaining the number of records that meet the condition, it also fills all the data that meets the condition into the DataTable, and then uses the DataTable instance Rows. count attribute to obtain the number of records.

The result is extremely low efficiency. If the data size is too large, the data you need may not be displayed for a long time. Therefore, you should use paging query to display the data. Querying by PAGE means that only the required data is returned each time, instead of submitting all the data from the database every time. This reduces the data transfer between the program and the database, it can also improve the program performance.

In general, when the data volume is large, it needs to be displayed by page. In this way, two parameters are returned for the query result set: the currently displayed page number pageIndex and the number of records per page.

The data table used. For example, the table id is the primary key.

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/12360JG5-0.png "/>

The following describes how to query the information of all users in the forward order of IDs. The SQL statement is as follows:

 
 

  
  
Select * from T_Person orderby id ASC;
 
 
Now we paging the data. The paging rule is that the page size is five pieces of data, so we can use the SQL statement to complete the data query on the 1st page:
  
  

   
   
Selecttop 5 * from T_Person orderby id asc;
  
  
In this way, we can retrieve the 5 data entries to be displayed on the 1st page. But how can we compile SQL statements to display data on pages 2, 3, 4?
If we use 1-5 records for the data retrieved on page 6, the data on page 6 should be 6-10 records. How should we do it? There are two methods: the first is to extract all the data and fill it in the able, and then start from 5 through I in the for loop, and the method I is less than 10 shows data. The disadvantages of this method have been described earlier. The second method is to filter the data in the database. in this case, the not in SQL statements can be used in a good way.
So how to use not in to display the data on the 2nd page? The SQL statement is as follows:
   
   

    
    
Selecttop 5 * from T_Person where id notin
    
    
(
    
    
Selecttop 5 id from T_Person orderby id asc
    
    
) Orderby id asc;
   
   
Here, a subquery is used to first display the data number on the 1st page, and then use not in to discharge records 1-5 from the data, showing 6-10 data records.
Because the data id starts from 1, records with IDs 1-5 are displayed on page 1, records with IDs 6-10 are displayed on page 1, and records with IDs 11-15 are displayed on page 1st, the SQL statement for pushing data on page n is: n is the page on which data is displayed,
    
    

     
     
selecttop 5 * from T_Person where id notin
     
     
( 
     
     
selecttop (n-1)5 id from T_Person orderby id asc
     
     
)orderby id asc;
    
    
In this way, the page data is displayed based on parameter n.
Another important knowledge point is how to calculate the total number of pages on the data page. If there are 20 data entries, if there are 5 data entries on each page, it is obviously divided into 4 pages. However, if the number of records is 21, it is obvious that there should be 5 pages. There is a formula. If there are m pieces of data in total and n pieces of data are displayed on each page, m and n are all greater than 0), the page number of all records must be displayed: page = m % n) = 0? M/n): m/n + 1 );
The second paging method:
The ROW_NUMBER function in SQL is used to mark each record in the returned record set.
Because we need to delete the database, the IDs in the database table may be discontinuous. In the above method, we mainly use IDs for sorting and do not require too many operations. However, the following method requires a continuous id value to query data.
First look at an SQL statement:
    
    

     
     
Select * from
     
     
(
     
     
Select * from T_Person
     
     
) As
     
     
Where id> 5 and id <= 10
    
    
In this way, we can still retrieve the data of the corresponding number of pages as required. The SQL statement is as follows: n is the page number of the displayed data.
     
     

      
      
select * from
      
      
( 
      
      
select * from T_Person 
      
      
)as a 
      
      
where id>(n-1)*5 and id<=n*5
     
     
At this time, we can select the data of the corresponding page number based on the parameter n, but we can quickly find that this page is highly dependent on the id consecutively. Therefore, when we delete data, the IDs in the database may be discontinuous. Therefore, when we perform paging in the database, we need to number the data, in this case, we need to use the ROW_NUMBER function in SQL,
The SQL statement queried using the ROW_NUMBER function and the display result are as follows:
650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/12360L308-1.png "/>
We can easily find that there is a rl column in the forward order of IDs.
Therefore, the complete SQL paging statement is: n is the page number of the pages we pass in. By default, each page displays 5 data bits.
    
    

     
     
Select * from
     
     
(
     
     
Select * from T_Person
     
     
) As
     
     
Where id> (n-1) * 5 and id <= n * 5
    
    
In this way, we can create a paging storage process, paging the data in the database, and then call the program.
Finally, I will share a better paging stored procedure on the Internet:

     
     

      
      
CreatePROCEDURE GetPageData
      
      
(
      
      
@ TableName varchar (30), -- table name
      
      
@ IDName varchar (20), -- table primary key name
      
      
@ PageIndex int, -- current page number
      
      
@ PageSize int -- size of each page
      
      
)
      
      
AS
      
      
IF @ PageIndex> 0
      
      
BEGIN
      
      
Set nocount on
      
      
DECLARE @ PageLowerBound int, @ StartID int, @ SQL nvarchar (225)
      
      
SET @ PageLowerBound = @ PageSize * (@ PageIndex-1)
      
      
IF @ PageLowerBound <1
      
      
SET @ PageLowerBound = 1
      
      
Set rowcount @ PageLowerBound
      
      
SET @ SQL = n' SELECT @ StartID = ['+ @ IDName +'] from' + @ TableName + 'ORDER BY' + @ IDName
      
      
Exec sp_executesql @ SQL, n' @ StartID int output', @ StartID output
      
      
Set rowcount 0
      
      
SET @ SQL = 'select top '+ str (@ PageSize) + '* from' + @ TableName + 'where [' + @ IDName + ']> =' + str (@ StartID) + 'order BY ['+ @ IDName +']'
      
      
EXEC (@ SQL)
      
      
Set nocount off
      
      
END
     
     
  
Original works can be reprinted. During reprinting, you must mark the original source, author information, and this statement in hyperlink form. Otherwise, legal liability will be held.
650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/12360IK2-2.png "/>

This article is from the blog of "Hadar column", please be sure to keep this source http://yisuowushinian.blog.51cto.com/4241271/1034882