Sorting of data

If sorting operation is not used, the data returned by query is displayed in the order of adding data by default.

Sorting using the ORDER BY clause

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

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

Single column sorting

For example:

# Display employee information in salary order from high to low
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

#Display employee information in salary order from low to high
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary; # If there is no explicit name sorting method after ORDER BY, it will be sorted in ascending ORDER BY default.

be careful:
① We can use the alias of the column to sort.
② Column aliases can only be used in ORDER BY, not WHERE.
③ Emphasize format: WHERE needs to be declared after FROM and before ORDER BY.
For example:

SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;

#The following operations report an error!
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;

Multi column sort (secondary sort)

• 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 sorting the second column. If all values in the first column of data are unique, the second column will no longer be sorted.

For example:

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

result:

The same is true for three-level sorting, four-level sorting and multi-level sorting.

paging

Use background:
Background 1: there are too many records returned by query, which is very inconvenient to view. How can paging query be realized?
Background 2: there are 4 pieces of data in the table. What should we do if we only want to display the second and third pieces of data

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
Note: the LIMIT clause must be placed at the end of the entire SELECT statement!

Benefits of using paging:
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.

Syntax format:

LIMIT [position offset,] 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.

For example:

# Requirement 1: 20 records are displayed on each page, and page 1 is displayed
SELECT employee_id,last_name
FROM employees
LIMIT 0,20

# Requirement 2: 20 records are displayed on each page, and page 2 is displayed at this time
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;

# Requirement 3: 20 records are displayed on each page, and page 3 is displayed
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;

# Demand 4: 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;

# Demand 5: there are 107 pieces of data in the table. We only want to display the 32nd and 33rd pieces of data
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;
#LIMIT 31,2;

be careful:

"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.

LIMIT can be used in MySQL, PGSQL, MariaDB, SQLite and other databases to represent paging. Cannot be used in SQL Server, DB2, Oracle!

Pagination display formula

(current pages - 1) * number of entries per page, number of entries per page

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