①. ORDER BY Sorting data

• ①. Use ORDER BY Clause ordering
  ASC(ascend): Ascending 、 The default is ascending
  DESC(descend): Descending

#  If no sort operation is used , By default, the data returned by the query is displayed in the order of adding data .
SELECT * FROM employees;

• ②. Single column sort

#  practice : according to salary Display employee information from high to low 
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

#  practice : according to salary Display employee information from low to high 
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;

#  If in ORDER BY  If there is no explicit naming sort after , Sort in ascending order by default .
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary; 

• ③. You can use the alias of the column to sort , Can't be in where Use in , This is because , We're executing SQL Statement is executed first FROM+WHERE Then query each field

# We can use column aliases , Sort 
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;

# Column aliases can only be used in  ORDER BY  Use in , Can't be in WHERE Use in .
# The following operation reports an error ！
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;

• ④. Emphasis format :WHERE Need to declare in FROM after ,ORDER BY Before

# Emphasis format :WHERE  Need to declare in FROM after ,ORDER BY Before .
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;

• ⑤. Secondary sorting

1. Can be used not in SELECT Sort the columns in the list .
2. When sorting multiple columns , The first column sorted first must have the same column value , Will sort the second column . If all values in the first column of data are unique , The second column will no longer be sorted .

#  Show employee information , according to department_id Descending order of ,salary In ascending order 
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

②. LIMIT Paging query

• ①. background 1: The query returned too many records , It's inconvenient to check , How to implement paging query ？
  background 2: There are 4 Data , We just want to show 2、3 What about this data ？

• ②. MySQL Use in LIMIT Implement paging

1. Format :LIMIT[ Position offset ,] Row number
2. first “ Position offset ” Parameters indicate MySQL Which line to start with , Is an optional parameter , If you don't specify “ Position offset ”, It will start with 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 “ Row number ” Indicates the number of records returned

-- front 10 Bar record : 
SELECT * FROM  Table name  LIMIT 0,10;  perhaps SELECT * FROM  Table name  LIMIT 10;
-- The first 11 to 20 Bar record : 
SELECT * FROM  Table name  LIMIT 10,10;
-- The first 21 to 30 Bar record : 
SELECT * FROM  Table name  LIMIT 20,10;

• ③. MySQL 8.0 Can be used in “LIMIT 3 OFFSET 4”, It means to get from 5 A record starts after 3 Bar record , and “LIMIT 4,3;” The results returned are the same

#  There are 107 Data , We just want to show  32、33  What about this data ？
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

# MySQL8.0 New characteristics :LIMIT ... OFFSET ...
# practice : There are 107 Data , We just want to show  32、33  What about this data ？
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

• ④. Paging explicit formula :( The current number of pages -1)* Number of entries per page , Number of entries per page
  Be careful :LIMIT Clause must be placed throughout SELECT At the end of the sentence ！

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

#  demand 1: Each page shows 20 Bar record , The... Is displayed 1 page 
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;

#  demand 2: Each page shows 20 Bar record , The... Is displayed 2 page 
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;

#  demand 3: Each page shows 20 Bar record , The... Is displayed 3 page 
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;
# practice : 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;

• ⑤. The number of results returned by the constraint can Reduce the network transmission of data tables , It's fine too Improve query efficiency . If we know the result, only 1 strip , You can use LIMIT 1 , tell SELECT Statement only needs to return a record . The good thing is SELECT There is no need to scan the complete table , You only need to retrieve a qualified record to return

• ⑥. In different DBMS The keywords used in may be different . stay MySQL、PostgreSQL、MariaDB and SQLite Use in LIMIT keyword , And it needs to be put in SELECT At the end of the statement ( understand )

#  If it is  SQL Server  and  Access, Need to use  TOP  keyword , such as :
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
#  If it is  DB2, Use  FETCH FIRST 5 ROWS ONLY  Such keywords :
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
#  If it is  Oracle, You need to be based on  ROWNUM  To count the number of lines :
SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;

③. ORDER BY and LIMIT exercises

#1.  Check the employee's name, department number and annual salary , In descending order of annual salary , Display by name in ascending order  
SELECT last_name,department_id,salary * 12 annual_salary
FROM employees
ORDER BY annual_salary DESC,last_name ASC;

#2.  The choice of salary is not in  8000  To  17000  The name and salary of the employee , In descending order of wages , According to the first 21 To 40 Location data  
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;


#3.  Query mailbox contains  e  Employee information , And first by the number of bytes in the mailbox , And then in ascending order by 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;

④. GROUP BY Grouped data

• ①. Use GROUP BY Clause divides the data in a table into groups
  

# demand : Query the average salary of each department , Maximum wage 
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id

# demand : Check each one job_id The average wage of 
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

• ②. Multiple sorting
  

# demand : Check each one department_id,job_id The average wage of 
# The way 1:
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY  department_id,job_id;
# The way 2:
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

• ③. SELECT The fields of non group functions appearing in must be declared in GROUP BY in
  conversely ,GROUP BY Fields declared in can not appear in SELECT in

# FALSE ！ there job_id Not in the group by Inside 
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;

• ④. GROUP BY The statement in FROM Back 、WHERE Back ,ORDER BY front 、LIMIT front

• ⑤. MySQL in GROUP BY Use in WITH ROLLUP

1. When using ROLLUP when , Can't be used at the same time ORDER BY Clause to sort the results , namely ROLLUP and ORDER BY Are mutually exclusive
2. stay MYSQL8.0 No mistake. , stay MYSQL5.47 Will report a mistake

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

#  explain : When using ROLLUP when , Can't be used at the same time ORDER BY Clause to sort the results , namely ROLLUP and ORDER BY Are mutually exclusive .
#  FALSE :
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;