Chapter 08 MySQL: aggregate function

Write it at the front ： Follow B Master Kang at the station mysql Summary of notes and self summary .

1、 What are aggregate functions

Aggregate functions act on a set of data , And return a value for a set of data .

# This code indicates ： A set of data on the salary of the employee table , Returns the maximum value of this set of data 
SELECT MAX(salary) FROM employees;

2、 Aggregate function type ( Commonly used )

• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()

It should be noted that ： Aggregate functions cannot be nested , For example, there can be no similar “AVG(SUM( Field name ))” Call of form ; Can't be in WHERE The aggregate function is used in clause

3、GROUP BY Use

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

above SQL You can learn ：

• have access to GROUP BY Clause divides the data in a table into groups
• stay SELECT All columns in the list that are not included in the group function should be included in GROUP BY clause , What we are talking about here is that we should , It does not contain and will not report an error
• You can use multiple columns to group

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

above SQL You can learn ： Included in GROUP BY Columns in Clause need not be included in SELECT In the list

4、HAVING Use

HAVING It is used to filter the grouped , and WHERE Is to filter before grouping

What should be paid attention to when using ：

• Rows have been grouped .
• The aggregate function is used .
• Satisfy HAVING The grouping of conditions in the clause will be displayed .
• HAVING Not to be used alone , Must follow GROUP BY Use it together .

5、 WHERE and HAVING Comparison of ( Original notes )

difference 1：WHERE You can directly use the fields in the table as filter criteria , However, you cannot use the calculation function in the group as the filter condition ;HAVING Must be with GROUP BY In combination with , You can use the grouping calculation function and grouping field as the filter criteria .

To determine the , When it is necessary to make grouping statistics on data ,HAVING Can finish WHERE Tasks that cannot be completed . This is because , In the query syntax structure ,WHERE stay GROUP BY Before , Therefore, the grouping results cannot be filtered .HAVING stay GROUP BY after , You can use group fields and calculation functions in groups , Filter the grouped result set , This function is WHERE What can't be done . in addition ,WHERE Excluded records are no longer included in the group .

difference 2： If you need to obtain the required data from the associated table through connection ,WHERE Filter first and then connect , and HAVING Connect first and then filter . This point , This determines that in the association query ,WHERE Than HAVING More efficient . because WHERE You can screen first , Join with a smaller filtered dataset and associated tables , This takes up less resources , The implementation efficiency is also relatively high .HAVING You need to prepare the result set first , That is, association with unfiltered data sets , Then filter the large data set , This takes up more resources , Execution efficiency is also low .

Choices in development ：

WHERE and HAVING It's not mutually exclusive , We can use both in one query WHERE and HAVING. The conditions containing grouping statistical functions are HAVING, For ordinary conditions WHERE. such , We're taking advantage of WHERE Efficient and rapid of conditions , Played again HAVING You can use the advantages of query conditions that include grouping statistics functions . When the amount of data is very large , There will be a big difference in operating efficiency .

6、 Strengthen the exercises after class

# Query each job_id It's the maximum wage of employees , minimum value , Average , The sum of the 
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;

# Some results of the query ：
+------------+-------------+-------------+--------------+-------------+
| job_id     | MAX(salary) | MIN(salary) | AVG(salary)  | SUM(salary) |
+------------+-------------+-------------+--------------+-------------+
| AC_ACCOUNT |     8300.00 |     8300.00 |  8300.000000 |     8300.00 |
| AC_MGR     |    12000.00 |    12000.00 | 12000.000000 |    12000.00 |
| AD_ASST    |     4400.00 |     4400.00 |  4400.000000 |     4400.00 |
| AD_PRES    |    24000.00 |    24000.00 | 24000.000000 |    24000.00 |
| AD_VP      |    17000.00 |    17000.00 | 17000.000000 |    34000.00 |
| FI_ACCOUNT |     9000.00 |     6900.00 |  7920.000000 |    39600.00 |
| FI_MGR     |    12000.00 |    12000.00 | 12000.000000 |    12000.00 |
+------------+-------------+-------------+--------------+-------------+

#  Query each type of work 、 The name of each department 、 The name of the type of work and the minimum wage  
# Multi table query and multi column grouping are used here 
SELECT t1.department_name,t2.job_id,MIN(salary)
FROM departments t1 LEFT JOIN employees t2
ON t1.`department_id` = t2.`department_id`
GROUP BY department_name,job_id;

# Some results of the query ：
+----------------------+------------+-------------+
| department_name      | job_id     | MIN(salary) |
+----------------------+------------+-------------+
| Administration       | AD_ASST    |     4400.00 |
| Marketing            | MK_MAN     |    13000.00 |
| Marketing            | MK_REP     |     6000.00 |
| Purchasing           | PU_MAN     |    11000.00 |
| Purchasing           | PU_CLERK   |     2500.00 |
| Human Resources      | HR_REP     |     6500.00 |
| Shipping             | ST_MAN     |     5800.00 |
| Shipping             | ST_CLERK   |     2100.00 |
| Shipping             | SH_CLERK   |     2500.00 |
+----------------------+------------+-------------+

Love is at the end ： You should understand ：1、having and where The difference between ; 2、group by and having Precautions in use .