catalogue

1. Grouping function

2.HAVING clause

3.SELECT statement execution process

4. Sub query

1. Grouping function

(1) Why use grouping functions:

Grouping function is to operate on the set of data rows and give a result by group. This result can be output directly or used as judgment conditions.

(2) Difference between grouping function and single line function

Single line function: operates on single line data and returns only one value
Grouping function: processes a group of data and returns a value

(3) Grouping function concept

Grouping function is used to operate a group of records in a table. Each group returns only one result, that is, first group the table records, and then summarize the operations. Each group returns one result. When grouping, the whole table may be divided into one group or multiple groups according to conditions.

(4) Five functions commonly used in grouping functions

• MIN: returns the minimum value in each group
• MAX: returns the maximum value in each group
• SUM: calculate the SUM of each group
• AVG: calculate the average value of each group
• COUNT: calculate the number of records in each group

(4) Grammar

SELECT	[column,] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column]
[HAVING group_function(column)expression 
[ORDER BY column| group_function(column)expression];

• Through the GROUP BY clause, the records in the table that meet the WHERE condition can be divided into several groups according to the specified column; WHERE the GROUP BY clause specifies the columns to group
• The GROUP BY clause can also be grouped by multiple columns

example:

select count(mgr) from emp; 
-- because emp Some employees in the table do not have managers, all of them should sql The total number of employees with managers is queried, not the total number of all employees

select min(sal) from emp; -- Get the minimum wage

select max(sal) from emp; -- Get the highest salary

select sum(comm) from emp; -- Calculate the total bonus of all employees

select avg(sal) from emp group by deptno;
 -- Query the average salary of each department by department grouping
 
 select count(distinct deptno) from emp; -- Calculate how many departments there are

-- Query the total salary of each position in each department.
SELECT deptno, job, sum(sal) 
FROM emp 
GROUP BY deptno, job;

2.HAVING clause

(1) Function

Exclude group results

(2) Difference between HAVING clause and WHERE clause

① The HAVING clause is used to restrict the grouped data

② The WHERE clause is used to restrict the data before grouping

(3) Grammar

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING	group_condition]
[ORDER BY column];

(4) Examples

-- Query the department number of each department whose maximum salary is greater than 2900, and the maximum salary
select deptno Department number, max(sal) 
from emp 
group by deptno 
having max(sal) > 2900;

3.SELECT statement execution process

SELECT statement execution process:

• 1. Find the table to query in the FROM clause;
• 2. Filter and judge non grouping functions through WHERE clause;
• 3. Complete the grouping operation through the GROUP BY clause;
• 4. Complete the group function screening judgment through the HAVING clause;
• 5. SELECT the displayed column or expression and group function through the SELECT clause;
• 6. Sort through the ORDER BY clause.

SELECT    deptno,job,avg(sal)                        -- 5               

FROM      emp                                        -- 1

WHERE      job in ('SALESMAN','MANAGER','CLERK')     -- 2

GROUP BY  deptno,job                                 -- 3

HAVING avg(sal)>1000                                 -- 4

ORDER BY  3 DESC;                                    -- 6
-- Pithy formula (waiter or me)
-- clothes:FROM
-- Affairs:WHERE
-- work:GROUP
-- still:HAVING
-- yes:SELECT
-- I:ORDER

4. Sub query

(1) Definition

Queries in parentheses are called subqueries, also known as internal queries, and are executed before the main query.

(2) Grammar

SELECT select_list
FROM table
WHERE expr operator
(SELECT	select_list FROM table);

Queries in parentheses are called subqueries, also known as internal queries, and are executed before the main query.

The results of the sub query are by the main query (external query)

Use the expr operator to include comparison operators.

• Single line operators: >, =, > =, <, < ><=
• Multiline operators: IN, ANY, ALL

(3) Subqueries can contain nested clauses

• WHERE clause
• HAVING clause
• FROM clause

(4) Attention

• Subqueries should be enclosed in parentheses
• Place the subquery to the right of the comparison operator
• For single line subqueries, use single line operators
• Use the multiline operator for multiline subqueries

(5) Subquery instruction

• Subqueries should be enclosed in parentheses
• Place the subquery to the right of the comparison operator
• For single line subqueries, use single line operators
• Use the multiline operator for multiline subqueries

(6) Sub query classification

• single-row subqueries
• Multiline subquery
• Multi column subquery

(7) null value in subquery

-- Query employee names that are not managers
select ename Employee name
from emp 
where empno not in(select mgr from emp);

The above sql query cannot find any data because there is a null value in the result of the sub query, which leads to no record returned by the main query. This is because all condition and null value comparison results are null values. Therefore, the NOT IN operator cannot be used whenever a null value may become part of the subquery result set.