paging

1. Background:

Background 1: there are too many records returned by query, which is very inconvenient to view. How can we realize paging query?

Background 2: there are four data items in the table. What if you only want to display the second and third data items?

2. Implementation rules

• Paging principle The so-called pagination display refers to the conditions required to display the result set in the database paragraph by paragraph.
• Using LIMIT to realize paging in MySQL
• Format:

LIMIT position offset, number of rows

• The first "position offset" parameter indicates which row MySQL displays from. It is an optional parameter. If "position offset" is not specified, it will start from the first record in the table (the position offset of the first record is 0, the position offset of the second record is 1, and so on); The second parameter "number of rows" indicates the number of records returned.
• give an example

--Top 10 records:
SELECT * FROM Table name LIMIT 0,10;
perhaps
SELECT * FROM Table name LIMIT 10;

--Records 11 to 20:
SELECT * FROM Table name LIMIT 10,10;

--Records 21 to 30: 
SELECT * FROM Table name LIMIT 20,10;

• Use limit to realize pagination display of data
• Requirement 1: 5 records are displayed on each page, and page 1 is displayed at this time

SELECT employee_id,last_name
FROM employees
LIMIT 0,5;

• Requirement 2: 6 records are displayed on each page, and page 2 is displayed at this time

SELECT employee_id,last_name
FROM employees
LIMIT 5,6;

• Requirement 3: 7 records are displayed on each page, and page 3 is displayed at this time

SELECT employee_id,last_name
FROM employees
LIMIT 6,7;

• Requirement 4: pageSize records are displayed on each page, and page pageNo is displayed at this time:
• Formula:

LIMIT (pageNo-1) * pageSize, pageSize;

• Pagination explicit formula: (current number of pages - 1) * number of entries per page, number of entries per page

SELECT * FROM table 
LIMIT(PageNo - 1)*PageSize, PageSize;

• Note: the LIMIT clause must be placed at the end of the entire SELECT statement!
• Benefits of using LIMIT: Constraining the number of returned results can reduce the network transmission of the data table and improve the query efficiency. If we know that there is only one returned result, we can use LIMIT 1 to tell the SELECT statement that only one record needs to be returned. The advantage of this is that SELECT does not need to scan the complete table, but only needs to retrieve a qualified record to return.
• WHERE ... ORDER BY ... The order of limit declaration is as follows:
• LIMIT format: strictly speaking: LIMIT position offset and number of entries
• The structure "LIMIT 0, number of entries" is equivalent to "LIMIT number of entries"

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;

• Exercise: there are 107 data in the table. What if you only want to display the 32nd and 33rd data?

SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

"LIMIT 3 OFFSET 4" can be used in MySQL 8.0, which means to obtain the following three records from the fifth record, and "LIMIT 4,3" The results returned are the same.

• MySQL8.0 new feature: Limit OFFSET ...
• Exercise: there are 107 data in the table. What if you only want to display the 32nd and 33rd data?

SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

• Exercise: query the information of the highest paid employee in the employee table

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;

3. Expansion

• LIMIT can be used in MySQL, PGSQL, MariaDB, SQLite and other databases to represent paging. Cannot be used in SQL Server, DB2, Oracle.
• The keywords used in different DBMS may be different. Use the LIMIT keyword in MySQL, PostgreSQL, MariaDB and SQLite, and put it at the end of the SELECT statement.
• For SQL Server and Access, you need to use the TOP keyword, such as:

SELECT TOP 5 last_name, employee_id 
FROM employees
ORDER BY employee_id DESC

• For DB2, use the keyword FETCH FIRST 5 ROWS ONLY:

SELECT last_name, employee_id 
FROM employees 
ORDER BY employee_id DESC 
FETCH FIRST 5 ROWS ONLY

• If it is Oracle, you need to count the number of rows based on ROWNUM: For example: query the first 10 records

SELECT rownum, employee_id, last_name
FROM employees
where rownum<= 10;

SELECT rownum,last_name,salary 
FROM employees 
WHERE rownum <= 5 ORDER BY salary DESC;

4. Practice

1. Query the name, department number and annual salary of employees, and display them in descending order of annual salary and ascending order of name

SELECT last_name,department_id,salary * 12 annual_salary
FROM employees
ORDER BY annual_salary DESC,last_name ASC;

1. Select the name and salary of employees whose salary is not between 8000 and 17000, and display the data at positions 21 to 40 in descending order of salary

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

1. Query the employee information contained e in the mailbox, and first in descending order according to the number of bytes in the mailbox, and then in ascending order according to the department number

SELECT employee_id,last_name,email,department_id
FROM employees
#where email like '%e%'
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id;