Detailed description of MySQL grouping query and connection query statements, detailed description of mysql

Detailed description of MySQL grouping query and connection query statements, detailed description of 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:

Copy codeThe 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:

Copy codeThe 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.

Articles you may be interested in:

• Simple optimization tutorial for table connection query in MySQL
• MySQL basic multi-Table connection query tutorial
• Typical mysql connection query example
• Detailed description of MySql basic queries, connection queries, subqueries, and regular expression queries
• Connection query for MySQL notes
• MySQL query optimization: Connection query sorting limit (join, order by, limit statements)
• MySQL query optimization: Use subqueries instead of non-primary key connections to query instances
• MySQL query optimization: Connection query sorting
• Comparison and usage of distinct and group by statements in MySQL
• How to Use Distinct and Group By statements in MySQL