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:
-
MySQL query optimization: join query sort limit of join order by limit statement introduction
-
MySQL query optimization: a brief introduction to join query sorting
-
mysql Join Query (Left Join Right Join Inner Join)
-
Simple example of MySQL join table query
-
The disctinct group by query using mysql does not duplicate records