1, Sort

1.1 sorting rules

Sort using ORDER BY clause

• ASC (ascend): ascending
• DESC (descend): descending order

The ORDER BY clause is at the end of the SELECT statement

1.2 single column sorting

Example 1:

mysql> SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date;
+-------------+------------+---------------+------------+
| last_name   | job_id     | department_id | hire_date  |
+-------------+------------+---------------+------------+
| King        | AD_PRES    |            90 | 1987-06-17 |
| Whalen      | AD_ASST    |            10 | 1987-09-17 |
| Kochhar     | AD_VP      |            90 | 1989-09-21 |
| Markle      | ST_CLERK   |            50 | 2000-03-08 |
| Ande        | SA_REP     |            80 | 2000-03-24 |
| Banda       | SA_REP     |            80 | 2000-04-21 |
| Kumar       | SA_REP     |            80 | 2000-04-21 |
+-------------+------------+---------------+------------+
7 rows in set (0.01 sec)

Example 2:

SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal;

1.3 multi column sorting

SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

1.4 summary

• You can sort using columns that are not in the SELECT list
• When sorting multiple columns, the first column to be sorted must have the same column value before the second column can be sorted. If all values in the first column of data are unique, the second column will no longer be sorted

2, Pagination

2.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 4 pieces of data in the table. We just want to display the second and third data. What should we do?

2.2 implementation rules

• Pagination 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,] rows
  • The first position offset parameter indicates which row MySQL displays from. It is an optional parameter. If the 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 line number indicates the number of records returned
• 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

2.3 examples

--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;

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.

2.4 benefits

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.

2.5 expansion

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 name, hp_max FROM heros ORDER BY hp_max DESC

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

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

If it is Oracle, you need to count the number of rows based on ROWNUM:

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

It should be noted that this statement takes out the first five data rows first, and then follows hp_max is sorted from high to low. However, the result is different from that of the above method. We will talk about sub query later. You can use

SELECT rownum, last_name,salary 
FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC) 
WHERE rownum < 10;

The results are consistent with the above methods.