Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how MySQL beginners use grouping aggregation query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Catalogue

1. Schematic diagram of grouping query

Detailed explanation of 2.group by keyword syntax

3. A simple case of grouping query

4. Screening before grouping and filtering after grouping

1) the concept of original table and result set

2) Mr. Huang made a big move.

3) case explanation

5. Grouping queries (grouped by function)

6. Grouping queries (grouped by multiple fields)

7.group by and order by, old partners

8. Summary of grouped queries

1. Schematic diagram of grouping query

For the above raw data, the average value of SALARY (salary) is calculated according to DEPARTMENT_ID (employee id).

If the above principles are written in code, how should they be written?

Select department_id,avg (salary) from testgroup by department_id

You can clearly see that after grouping with department_id, the system allocates rows with the same department_id number by default. If you have several different department_id, you will be divided into several groups, and the number of data rows in each group may not be the same.

When the automatic allocation is completed, the intra-group operation will be carried out according to the grouping function you have written.

In other words, if you are using the sum () function, you will find the sum within the group; when you are using the avg () function, you will find the average within the group; when you are using the count () function, you will count within the group; when you are using the max () function, you will seek the maximum value within the group; if you are using the min () function, you will seek the minimum value within the group.

Detailed explanation of 2.group by keyword syntax

Some rookies get stuck in the group by keyword in the process of learning MySQL. So I hope I can use the vernacular picture and text way, so that you can really understand the meaning of this keyword.

Group by is a keyword used for grouping queries and is generally used with sum (), avg (), count (), max (), min () aggregate functions. That is to say, as long as there is a group by in the SQL statement, one or more of the aggregate functions (5 aggregate functions) will appear in the display field after the select. Looking at the figure above, one thing you need to remember is that after you group field An in the table, you generally need to use an aggregate function for other fields in the table, which makes more sense than using an aggregate function for field A. it doesn't make much sense.

Let's think about the following question!

When group by is used in the SQL statement, there must be a field after the select that uses aggregate functions (5 aggregate functions). But besides this aggregate function, are there any other fields that can be added after select?

The answer must be yes! However, this field has certain restrictions, and not all fields can be used. That is, when the group by keyword is used in the SQL statement, the select can only be followed by the fields that appear after the group by except the aggregate function. That is, there can only be fields after the group by after the field in the figure, A _ select.

3. A simple case of grouping query

Case study: grouped according to department number deptno, the average salary of each department is counted.

Select deptno,avg (sal) avgsfrom empgroup by deptno

The results are as follows:

4. Screening before grouping and filtering after grouping

This knowledge point is to take you to understand, what use should be filtered with where? When should I use having filtering? This knowledge point is also a thorny matter for rookies who are learning MySQL. Don't worry, there's nothing you can't learn to learn MySQL from classmate Huang.

1) the concept of original table and result set

The original table refers to the table that really exists in the database, and the original table information is queried using [select * from table name]. The result set refers to adding any other constraint in the SQL statement and finally showing us the table, which is the result set. Add different constraints, the query result set is also different. There is only one original table, but the result set is various.

2) Mr. Huang made a big move.

As long as the aggregate function is involved in the requirement, it must be the screening after grouping. If you can use pre-grouping screening, priority will be given to pre-grouping screening. (considering performance issues)

3) case explanation

The original dataset is as follows:

① pre-packet filtering

Exercise 1: query the sum of the salaries of each department that contains the S character in the name.

Exercise 2: inquire about the average salary of different departments with a salary greater than 2000.

Filter after ② grouping

Exercise 1: inquire about the department number and the number of employees whose number is greater than 3.

Exercise 2: check the department number and maximum wage of each department where the maximum wage is greater than 3000.

The combination of pre-grouping filtering and post-grouping filtering of ③

Exercise: inquire about the department numbers and averages of wages between different departments whose average salary is greater than 2000 when they were hired in 1981.

5. Grouping queries (grouped by function)

Exercise: group according to the length of employees' names, inquire about the number of employees in each group, and screen the number of employees who are more than 3.

Select length (ename) len,count (*) countsfrom empgroup by lenhaving counts > 3

The results are as follows:

6. Grouping queries (grouped by multiple fields)

Exercise: inquire about the average salary of employees of each job in each department.

7.group by and order by, old partners

Exercise 1: inquire about the average salary of employees in each department, according to the descending order of average salary.

Exercise 2: inquire about the average salary of employees in each department, according to the increasing order of average salary.

8. Summary of grouped queries

1) if the grouping function is used as a condition, it must be put in the having clause.

2) if pre-grouping screening can be used, priority should be given to using pre-grouping filtering. (where filtering)

3) the group by clause supports single field grouping, multiple field grouping (there is no order required for multiple fields separated by commas), and function grouping (less used).

This is the end of the content of "how MySQL beginners use grouped aggregate queries". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.