Detailed description of MySQL group query and connection query statement _ MySQL

This article mainly introduces the grouping query and connection query statements in MySQL, and also introduces the usage of some statistical functions. For more information, see Detailed description of MySQL group query and connection query statement _ MySQL

Group query group
Group by attribute name [having condition expression] [with rollup]
"Attribute name" refers to grouping based on the field value. "having condition expression" is used to restrict the display after grouping. results that meet the conditions will be displayed; with rollup adds a record to the end of all records, which is the sum of all the above records.

1) separate use
Group by IS used independently. the query result only displays one record of a group.
Instance:

select * from employee group by sex;

Only two records of male and female are displayed.

2) used with the group_concat () function
The specified field values in each group are displayed.
Instance:

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

In the result, "female" displays the names of all sex IDs.

Sex | group_concat (name) female | Xiao Hong, Xiao Lan male | Zhang San, Wang Wu, Wang Liu

3) used with a collection function
Instance:

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

Result:

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

Count () is the calculation method of the number.

4) used with having
The "having condition expression" can restrict output results. Only results that meet the condition expression are displayed.
Instance:

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

Result:

Sex | count (sex) male | 3

The having condition expression acts on the record after the group.

5) group by multiple fields

select * from employee group by d_id,sex;

Query results are grouped by d_id and then by sex.

6) used with rollup
Using with rollup will add a record at the end of all records. this record is the sum of all the above records.
Instance:

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

Result:

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

If it is a string, for example, the name will generate a result of the type "zhang san, Li Si, Wang Wu", that is, the total name.

Connection query
Connect two or more tables to select the required data.

1) internal connection query:
When the values of fields with the same meaning in the two tables are equal, this record is queried.
Instance:

The code is as follows:

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 the table fields when the d_id field value is used.

2) query external connections
Select attribute name list from table name 1 left | right join table name 2 on table name 1. attribute name 1 = table name 2. attribute name 2;
Left join query:
When performing a left join query, you can find all the records in the table in Table 1. In table 2, only matching records can be queried.
Instance:

The code is as follows:

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

Right join query:
In contrast to the left join, all records in table 2 can be queried, while only matching records can be queried in the table in Table 1.

PS: query using a set function
Aggregate functions include count (), sum (), avg (), max (), and min ().
1) count () function
Number of statistical records
Instance:

select count(*) from employee;

Used with group

select d_id,count(*) from employee group by d_id;

The preceding statements are grouped before statistics.

2) sum () function
The sum () function is the sum function.
Instance:

select num,sum(score) from grade where num= 1001;select num,sum(score) from grade group by num;

Sum () can only calculate numeric fields.
3) avg () function
Avg () is an average function.
Instance:

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

4) max (), min () functions
Calculate the maximum and minimum values.
Instance:

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 uses the ascii code corresponding to the character for calculation.

The above is a detailed description of MySQL's group query and connection query statement _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!