mysql, sql server, oracle, DB2, Access paging technology

mysql

Remember that limit in mysql starts from scratch

The main format is as follows:

select   *   from   tablename   limit   m,n

For example, look up lines 6 to 20

select   *   from   tablename   limit   5,15

The two right-hand writings are equivalent:

select   *   from   table  limit  10
select   *   from   table   limit  0,  10 

It's the top ten lines of the query.

limit [m,] n

• m: As the starting line (i.e. from the row of the result set), start at 0 and can be omitted, i.e., write limit n directly, which is the default starting at line 0.

• n: Number of queries, not negative

Examples are as follows:

select * fromtablename limit 2,20

Represents taking 20 pieces of data from line 2

SELECT * FROM table   LIMIT [offset,] rows | rows OFFSET offset

The LIMIT clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numerical parameters. The parameter must be an integer constant. If two parameters are given, the first parameter is specified

The first returns the offset of the record row, and the second parameter specifies the maximum number of rows returned. The offset of the initial record line is 0 (not 1): MySQL also supports syntax in order to be compatible with PostgreSQL:

LIMIT # OFFSET #. 

mysql> SELECT * FROM table LIMIT 5,10; // Retrieval rows 6-15

To retrieve all rows from an offset to the end of the recordset, the second parameter can be specified as -1:

mysql> SELECT * FROM table LIMIT 95,-1; // Retrieval record line 96-last.

If only one parameter is given, it represents the maximum number of rows returned:

mysql> SELECT * FROM table LIMIT 5;     //Retrieving the first five rows of records

In other words, LIMIT n is equivalent to LIMIT 0,n.

sql server

Suppose id is the primary key field of the corresponding table

After sorting, select the third to fifth rows

select * from ( selecttop 3 * from ( select  top 5 * from wen3order by id asc ) a order by id desc ) b order by id asc ;

The following is also true, and more intuitive and concise, but it is not supported in SQL Server 2000, but it is supported in SQL Server 2005 or above. By the way, there is no except ion in oracle, and minus is replaced by minus.

select top 5 * fromwen3 order by id
except
select top 2 * fromwen3 order by id ;

Now let me change another way because there is no support for except in SQL Server 2000. It can be replaced by notexists, not in, <> any, etc. In relational algebra, except denotes the difference of sets.

select top 5 a.* from ( select  top 5 * from wen3 order by id asc ) a whereid not in( select top 2 id  from wen3order by id ) order by id;

oracle

Method 1 (This performance is the best)

select * from (selecta.*, rownum rn from (select * from table1 t order by id ) a where rownum <=8) where rn > 4;

Or

select * from (selecta.*, rownum rn from (select * from table1 t order by id ) a where rownum <=8) b where b.rn > 4;

Method two

select a.*,rownum from(select * from table1 t order by id ) a   --Remember to sort first.
where rownum <=8
minus
select a.*,rownum from(select * from table1 t order by id ) a
where  rownum <=4 ;

The above method is also very good, and the general players can not think of it!

Method 3 (This performance is the worst)

Select * from (selecta. *, rownum RN from (select * from table1 t order by id) a) b where B. RN between 5 and 8; -- of course, you can use and here

There are many traps. Be careful to distinguish them.

When using ROWNUM, only when the field of Order By is the primary key will the query result be first

Sort and recalculate ROWNUM

DB2

The first 15 records were taken.

select * from Table name order by XXX [desc] FETCH FIRST 15 ROWS ONLY

Take line m-n in db2

select * from (selectaid,aname,row_number() over(Support orderby clause) as id from Table name) as twhere t.id between m and n

Following are three methods of recording first-out items m to n in Oracle/Sqlserver/MySQL databases:

Gets rows from m to n sorted in ascending order by field F1

Note that F1 must be indexed or primary key, preferably primary key.

SqlServer

select t2.* from (Select top n-m t1.* from (Select top n * from Tablename as torder by t.F1) as t1 order by t1.F1 desc) as t2 order by t2.F1

select t2.* from
(
select top 20 t1.* from
(
select top 500020 * from dbgen as t order by t.F1
)
as t1 order by t1.F1 desc
)
as t2 order by t2.F1

Oracle

select * from (select rownum r, t.* from Tablename t where r<n) t1 wheret1.r > m and t1.r <= m

select * from (select rownum r, t.* from DBTEST t where rownum<300020) sswhere ss.r > 300000 and ss.r <= 300020

MySQL

The simplest

select * from Tablename t order by t.F1 limit n,m-n+1

select * from DBtest t order by t.F1 limit 300000,20

Performance comparison:

There are almost few of them! uuuuuuuuuuuu
The statements in the example above select about 50,000 records from the table of 500,000 records, which are no more than 1 second.

Oracle, DB2, SQL SERVER, Mysql, Access Paging SQL Statements

Recently, the Database Paging sql commonly used in the project is summarized. You can post paged sql statements that are more efficient.

SQL Server paging

The first paging method

Required parameters:

pageSize How many pieces of data are displayed per page
pageNumber Pages come from the client
totalRecouds Total number of records in the table select count (*) from Table name
totalPages PageCount
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
pages Before calculation pages Bar data
pages= pageSize*(pageNumber-1)

SQL statement:

select top pageSize * from Table name where id not in (selecttop pages id from Table name order by id) order by id

Second Paging Method

Required parameters:

pageSize How many pieces of data are displayed per page
pageNumber Pages come from the client
pages=pageSize*(pageNumber-1)+1

SQL statement:

select top pageSize * from Table name where id>=(selectmax(id) from (select top pages id from Table name order by idasc ) t )

mysql paging

Required parameters:

pageSize How many pieces of data are displayed per page
pageNumber Pages come from the client
totalRecouds Total number of records in the table select count (*) from Table name
totalPages PageCount
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
pages Starting position
pages= pageSize*(pageNumber-1)

SQL statement:

select * from table name limit pages, pageSize;

mysql paging depends on the keyword limit, which requires two parameters: the starting position and pageSize

Starting position = page size * (page number - 1)
Starting position = pageSize*(pageNumber -1)

oracle paging

Required parameters:

pageSize How many pieces of data are displayed per page
pageNumber Pages come from the client
totalRecouds Total number of records in the table select count (*) from Table name
totalPages PageCount
totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
startPage Starting position
startPage= pageSize*(pageNumber-1)+1
endPage=startPage+pageSize

SQL statement:

select a.* from
(
   select rownum num ,t.* from Table name t where A column=Certain value order by id asc
)a
where a.num>=startPage and a.num<endPage

db2 paging

Required parameters:

int startPage=1 //home page
int endPage;     //Termination page
int pageSize=5; //Page size
int pageNumber=1 //Request page

startPage=(pageNumber-1)*pageSize+1
endPage=(startPage+pageSize);

SQL statement:

select * from (select field 1, field 2, field 3, field 4, field 5,rownumber()over(order by sort field asc) as ROWID from table name) as a where a.rowid >= startPage and a.rowid < endPage.

access paging

Required parameters:

pageSize How many pieces of data are displayed per page
pageNumber Pages come from the client
pages=pageSize*(pageNumber-1)+1

SQL statement:

select top pageSize * from Table name where id>=(selectmax(id) from (select top pages id from Table name order by idasc ) t )

Change from: Database Paging Technology