Detail the group query and join query statements in MySQL

  • 2020-12-18 01:57:16
  • OfStack

Group query group by
group by attribute name [having conditional expression][with rollup]
"Property name" means grouping by the field value; "having conditional expression" is used to restrict the display after grouping. The results that meet the criteria will be displayed. with rollup will add 1 record to the end of all records, which is the sum of all records above.

1) Used alone
group by alone, the query results show only 1 record for 1 group.
Example:


select * from employee group by sex;

Only two records for men and women will be displayed.

2) Use with group_concat () function 1
The values of the fields specified in each group are displayed
Example:


select sex,group_concat(name) from employee group by sex;

The display result for "female" shows all sex's names as "female" name


sex | group_concat(name)
 female  |  Xiao Hong, Xiao LAN 
 male  |  zhang 3 And the king 5 And the king 6

3) And set function 1
Example:


select sex,count(sex) from employee group by sex;

Results:


sex | count ( num ) 
 female  | 1
 male  | 3

count () is the method to calculate the number.

4) Starting from having1
having conditional expression, which limits the output. Only results that satisfy the conditional expression are displayed.
Example:


select sex,count(sex) from employee group by sex having count(sex) >= 3;

Results:


sex | count ( sex ) 
 male  | 3

The having conditional expression applies to the grouped records.

5) Group by multiple fields


select * from employee group by d_id,sex;

The query results are grouped by d_id and then by sex

6) Use with with rollup1
Using with rollup will add 1 record to the end of all records, which is the sum of all records above
Example:


select sex,count(sex) from employee group by sex with rollup;

Results:


sex | count ( sex ) 
 female  | 1
 male  | 5
null | 6

If it is a string, such as a name, it will produce a result of the type "3, 4, 5", which is the name sum.

Join queries
Join two or more tables to select the required data.

1) Internal connection query:
The record is queried when the values of fields with the same meaning in two tables are equal.
Example:


select num,name,employee.d_id,age,d_name from employee,department where employee.d_id = department.d_id

Because the field names are the same, it is best to specify which table's fields when you take the d_id field value.

2) External connection query
select attributes list from table name 1 left|right join table name 2 on table name 1. Property name 1= table name 2.
Left connection query:
When you do a left join query, you can find all the records in the table indicated in table name 1. In table name 2, only matching records can be queried.
Example:


select num,name,employee.d_id,age,d_name from employee left join department on employee.d_id = department.d_id;

Right connection query:
As opposed to a left join, all records in table name 2 can be queried, whereas only matching records can be queried in tables indicated in table name 1.


PS: Query using the collection function
The set functions include count (), sum (), avg (), max () and min ().
1) count () function
Count the number of records
Example:


select sex,group_concat(name) from employee group by sex;
0

Use with group by1


select sex,group_concat(name) from employee group by sex;
1

The above statements are grouped first and then counted.

2) sum () function
The sum () function is the sum function
Example:


select sex,group_concat(name) from employee group by sex;
2

sum () can only compute numeric type fields.
3) avg () function
The avg () function is the average function.
Example:


select avg(age) from employee;

select course,avg(score) from group by course;

4) max (), min () function
Find the maximum and the minimum.
Example:
select max(age) from employee;
select num,course,max(score) from grade group by course;
For the maximum value of a string, the max () function is calculated using the ascii code corresponding to the character.


Related articles: