The old rule goes first:

1, Function introduction

An aggregate function acts on a set of data and returns a value to a set of data

1. Aggregate function type
   ●AVG()
   ●SUM()
   ●MAX()
   ●MIN()
   ●COUNT()
2. Aggregate function syntax
   

🔥 AVG and SUM functions

AVG and SUM functions are used for numerical data

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

🔥 MIN and MAX functions

To use the MIN and MAX functions for any data type

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

🔥 COUNT function

COUNT(*) returns the total number of records in the table, applicable to any data type

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

2, GROUP BY

You can use the GROUP BY clause to divide the data in a table into groups

SELECT ...,....,...
FROM ...,...,....
WHERE Connection conditions of multiple tables
AND Filter conditions without group functions
GROUP BY ...,...
HAVING Filter conditions containing group functions
ORDER BY ... ASC/DESC
LIMIT ...,...

1. Group using multiple columns
   

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

🔥 Use WITH ROLLUP in GROUP BY

After using the WITH ROLLUP keyword, add a record after all the queried grouping records. The record calculates the sum of all the queried records, that is, the number of statistical records

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

🎁 Note: when using ROLLUP, you cannot use ORDER BY to sort the results at the same time, that is, ROLLUP and ORDER BY are mutually exclusive

3, HAVING

Filter grouping

1. Rows have been grouped
2. Aggregate function used
3. Groups that meet the conditions in the HAVING clause are displayed
4. HAVING cannot be used alone. It must be used together with GROUP BY

Aggregate functions cannot be used in a WHERE clause

🔥 Comparison between WHERE and HAVING

1. Difference one
   WHERE can directly use the fields in the table as the filter criteria, but cannot use the calculation function in the grouping as the filter criteria;
   HAVING must be used in combination with GROUP BY. Grouping calculation functions and grouping fields can be used as filtering conditions, which determines that HAVING can complete tasks that cannot be completed by WHERE when grouping statistics of data is required
2. Difference 2
   If you need to obtain the required data from the association table through connection, WHERE is to filter before connection, and HAVING is to connect first
   Post filtering determines that WHERE is more efficient than HAVING in association query

	advantage	shortcoming
WHERE	Filter the data first and then associate it, which has high execution efficiency	You cannot filter using a calculated function in a group
HAVING	You can use the calculation function in the group	It is inefficient to filter in the final result set

4, SELECT execution process

🔥 Query structure

Mode 1

SELECT ...,....,...
FROM ...,...,....
WHERE Connection conditions of multiple tables
AND Filter conditions without group functions
GROUP BY ...,...
HAVING Filter conditions containing group functions
ORDER BY ... ASC/DESC
LIMIT ...,...

Mode 2

SELECT ...,....,...
FROM ... JOIN ...
ON Connection conditions of multiple tables
JOIN ...
ON ...
WHERE Filter conditions without group functions
AND/OR Filter conditions without group functions
GROUP BY ...,...
HAVING Filter conditions containing group functions
ORDER BY ... ASC/DESC
LIMIT ...,...

#Of which:
#(1) From: which tables to filter from
#(2) on: Descartes product is removed when associating multi table queries
#(3) where: criteria to filter from the table
#(4) group by: grouping basis
#(5) having: filter again in the statistical results
#(6) order by: sort
#(7) limit: paging

🔥 Execution order of SELECT

1. Keyword order:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

1. The execution order of the SELECT statement

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT Field of -> DISTINCT -> ORDER BY -> LIMIT

In MySQL and Oracle, the execution order of SELECT is basically the same

5, Execution principle of SQL

SELECT executes the FROM step first. At this stage, if you want to perform an associated query on multiple tables
You will also go through the following steps:

1. First, find the Cartesian product through CROSS JOIN, which is equivalent to getting the virtual table vt (virtual table) 1-1;
2. Filter through ON, filter ON the basis of virtual table vt1-1, and get virtual table vt1-2;
3. Add an external row. If we use left link, right link or full link, it will involve external rows, that is, in virtual
   Add external rows on the basis of table vt1-2 to obtain virtual table vt1-3

When we get the original data of the query data table, that is, the final virtual table vt1, we can carry out WHERE order on this basis
Paragraph. In this stage, the virtual table vt2 will be obtained by filtering according to the results of vt1 table.
Then enter the third and fourth steps, that is, the GROUP and HAVING stages. At this stage, it is actually in the virtual table vt2
On the basis of grouping and grouping filtering, the intermediate virtual tables vt3 and vt4 are obtained.
After we complete the condition filtering part, we can filter the fields extracted from the table, that is, enter SELECT and DISTINCT
Phase.
First, the desired fields will be extracted in the SELECT phase, and then the duplicate rows will be filtered out in the DISTINCT phase to obtain the intermediate virtual tables respectively
vt5-1 and vt5-2.
After we extract the desired field data, we can sort according to the specified field, that is, the ORDER BY stage, and get the
Virtual table vt6.
Finally, on the basis of vt6, take out the records of the specified row, that is, the LIMIT stage, and get the final result, which corresponds to the virtual table vt7