The database query returns a specific result, that is, querying by page.

Database Query returns a specific result, that is, querying by page 1 different paging statements for different databases: a mysql 1a) query the first n records 2 select * from table_name limit 0, n3b) query n to m 4 select * from table_name limit n, mb oracle 1a) query the first n records 2 select * from table_name where rownum3b) query records from nth to nth: 4 select * from (select. *,. rownum rn from table_name where rownum <n) where rn> mc sqlserver1a) query the first n records: 2 select top n * from table_name; 3b) query the n to m records: 4 select top n * from (select top m * from Table_name order by column_name) a order by column_name desc2 oracle rownum usage for rownum it is the number of the rows returned from the query in oracle system order, the first row returned is allocated 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, and rownum cannot be prefixed with any table name. (1) rownum: If you want to find the information of the first student in the student table that is equal to a certain value, you can use rownum = 1 as the condition. However, if you want to find the second student information in the student table, you cannot find the data using rownum = 2. Because rownum starts from 1, but the natural numbers above 1 are regarded as false when rownum is equal to or equal to the true value. Therefore, rownum = n (The Natural Number of n> 1) cannot be found ). SQL> select rownum, id, name from student where rownum = 1; (it can be used to limit the number of returned records to ensure no error, for example, implicit cursor) (2) rownum for query conditions greater than a certain value if you want to find records from the second row after the record, when rownum> 2 is used, no records can be found, the reason is that rownum is a pseudo column that always starts from 1. Oracle considers that rownum> n (Natural Number of n> 1) is still not true, so no records can be found. Then how can we find the record after the second row. You can use the following subquery method to solve the problem. Note that the rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column of a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column. SQL> select * from (select rownum no, id, name from student) where no> 2; (3) rownum for query conditions smaller than a certain value if you want to find the record before the third record, when using rownum <3, you can get two records. Obviously, rownum considers the condition of rownum <n (Natural Number of n> 1) as true, so records can be found. SQL> select rownum, id, name from student where rownum <3; In summary, you may need to query the data of rownum in a certain range in some cases, so what should we do? We can see that the rownum query condition for a value smaller than a certain value is true, and rownum is regarded as false for a query condition greater than a certain value, however, it can be converted to true indirectly. Subquery is required. For example, to query the data of rownum between the second row and the third row, including the data of the second row and the third row, we can only write the following statement to first let it return the record rows smaller than or equal to three, then, in the primary query, it is judged that the alias column of the new rownum is greater than or equal to two record rows. However, such operations will affect the speed in the big data set. SQL> select * from (select rownum no, id, name from student where rownum <= 3) where no> = 2; (4) rownum and rownum in Oracle are the sequence numbers generated when data is retrieved. Therefore, you must pay attention to the number of specified rowmun rows of data to be sorted. SQL> select rownum, id, name from student order by name; we can see that rownum is not the serial number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, you must use the subquery SQL> select rownum, id, name from (select * from student order by name); 3. The specific syntax for limit usage in mysql is: 1 SELECT * FROM table LIMIT [offset,] rows | the rows offset limit clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. The parameter must be an integer constant. If two parameters are specified, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The OFFSET of the initial record row is 0 rather than 1. To be compatible with PostgreSQL, MySQL also supports Syntax: LIMIT # OFFSET #. 1 mysql> SELECT * FROM table LIMIT 5, 10; // retrieve record rows 6-152 3 // to retrieve all record rows FROM an offset to the end of the record set, you can specify the second parameter-1: 4 mysql> SELECT * FROM table LIMIT 95,-1; // retrieve the record row 96-last.5 6 // if only one parameter is specified, it indicates the maximum number of record rows returned: 7 mysql> SELECT * FROM table LIMIT 5; // retrieves the first five record rows 8 9 // In other words, LIMIT n is equivalent to LIMIT 0, n. 4 mysql efficient paging method 1 Select. * from (2 select id from table B force index (ind_group_type_time) 3 where B. id = 1111 order by B. update_time desc limit xx, xx4) B, table a where. id = B. id; MySQL limit is used to read n records first, then discard the first n records, and read m records. Therefore, the larger n records, the worse the performance. Before optimization SQL: SELECT * FROM member order by last_active LIMIT 50, 5 after optimization SQL: SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id) the difference is that the SQL statement before optimization requires more I/O waste, because the index is read first, data is read again, and unnecessary rows are discarded. After optimization, the read-only index (Cover index) of the SQL statement (subquery) can be used to read the required columns through member_id. 5. calculate the total number of pages written in paging mode = (total number of records-1)/number of records displayed on each page + 1. To sum up, mysql and oracle paging write methods in the database are inconsistent, each database has its own characteristics. In addition, pay attention to the performance optimization of related SQL statements, especially for Big Data paging queries.