1. Paging introduction
Why do you use paging? Because sometimes the list content is too much and you need to use paging to display it. If there is too much data, a single page cannot display all the content, so only a part of the data is displayed each time.
Pagination is a technique that displays all data in segments to users. What the user sees each time is not all of the data, but a part of it. If you don't find the content you want in it, you can convert the visible content by specifying the page number or turning the page until you find the content you want. In fact, this is very similar to how we read books.
There are two solutions to achieve classification
- Query all the records in the database at once, and then display the specified records on each page.
- Query the database multiple times, and only get the data on this page each time and display it.
Nowadays, the data in website construction is massive. If you follow the plan 1, it will undoubtedly increase the load of the server memory and reduce the speed of the system; if you follow the plan 2, you may frequently operate the database, which will also affect the response efficiency; Therefore, it is generally used
The credibility of paging is to calculate the number of records per page, the total number of pages, and the number of pages. For each page of data, only the start record and the end record need to be calculated.
oracleto achieve paging, you need to use
rownumnot a real column, it is the number of the row returned from the query, the first row returned is assigned 1, the second row is 2, and so on , This pseudo field can be used to limit the total number of rows returned by the query.
rownumis always the number of the result set,
rownum>1any record that cannot be directly queried because it always starts from 1. 】
Here the following table is an example:
Next, use the
sqlstatement to query
select name,age,math,rownum from student;
Let's see what it
In fact, each query record has its own
rownumnumber, but it is not displayed by default.
Now let's perform the paging operation, each page displays 5 records, query the data on the first page:
select rownum, name,age,math,english from student where rownum<=5;
When this statement is executed, all the records have been queried, but
whereonly the first 5 pieces of data are displayed.
rownumto how much it can only be less than. If the condition is greater than 1, it is impossible to get data, because each piece of data
rownumis 1 when it is placed in the result set , and the second is 2. If the first is 1, the condition is not met, then The second piece of data
rownumis still 1 when it is placed in the result set , so it continues... For
select rownum, name,age,math,english from student where rownum>2;
The limit is
rownumgreater than 2, in fact, no data can be found.
So how to get the data of the second page? 【
5<rownum<=10】. It can
wherebe achieved by using clauses to treat the queried data as the source of the data, so that
rownumit becomes an ordinary field, not a pseudo column.
The table in the above figure is the data source, and
rownumthis column becomes an ordinary column.
select * from (select rownum, name,age,math,english from student) where rownum <=10;
This is the first 10 records of the query. There is a point to note here. The
rownumis the result set,
rownumwhich can only be less than a certain number, and cannot be greater than a certain number:
then how to get the
rownum[ordinary column in the result table] in the table , Instead of the
rownum[pseudo column, can only start from 1] in the result set of this statement .
At this time, you can use an alias to achieve:
select * from (select rownum as rw, name,age,math,english from student) where rw>5 and rw <=10;
Here played an alias
rwto achieve fetch
6到10data between here and there is no query result set
rwthe result set of a query inside the brackets
rownum, this concept can not be mixed up. As shown in the figure below:
aliases can distinguish different sentence result sets
rownum. Note here that if you want to sort,
rownumthe sequence number will be shuffled, because
rownumthe sequence number is determined before the sorting is done first in the result set and then sorting . Let's look at an example:
select rownum,rw2, name,age,math,english from (select rownum as rw2, name,age,math,english from student order by math desc);
Here, the query result set in the brackets is sorted.
As shown in the figure above, the result set in the brackets
rownumhas been out of order, but the outer layer is
rownumstill in order.