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
- Aggregate function type
●AVG()
●SUM()
●MAX()
●MIN()
●COUNT() - 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 ...,...
- 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
- Rows have been grouped
- Aggregate function used
- Groups that meet the conditions in the HAVING clause are displayed
- 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
- 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 - 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
- Keyword order:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
- 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:
- First, find the Cartesian product through CROSS JOIN, which is equivalent to getting the virtual table vt (virtual table) 1-1;
- Filter through ON, filter ON the basis of virtual table vt1-1, and get virtual table vt1-2;
- 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