A detailed explanation of the group query and connection query statement in Mysql _mysql

Group Query GROUP BY
Group BY property name [having conditional expression] [with Rollup]
"Property name" means grouped by the field value, the "Having conditional expression" is used to limit the display after grouping, the result of the condition is displayed, and the WITH rollup will add a record at the end of all records, which is the sum of all the records above.

1) Use alone
Group BY is used alone, and the query results show only one record for a group.
Instance:

SELECT * FROM employee GROUP by sex;

Only two records of men and women will be displayed.

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

Select Sex,group_concat (name) from the employee group by sex;

The "female" in the display result shows all sex as "female" name

sex | Group_concat (name)
female | Xiao Hong, xiao Lan
man | John, Harry, Wang Liu

3) used with aggregate functions
Instance:

Select Sex,count (Sex) from the employee group by sex;

Results:

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

Count () is the method of calculating the number.

4) use with the having
Having conditional expression, you can limit the output results. Only the results of a conditional expression are displayed.
Instance:

Select Sex,count (Sex) from the employee group by sex has the having count (sex) >= 3;

Results:

sex | COUNT (Sex)
man | 3

The having conditional expression acts on the records after grouping.

5) grouped by multiple fields

SELECT * FROM employee GROUP by D_id,sex;

Query results are grouped by d_id and then by sex

6) Use with rollup
Using the WITH rollup will add a record at the end of all records, which is the sum of all the records above
Instance:

Select Sex,count (Sex) from the employee GROUP by sex with rollup;

Results:

sex | COUNT (Sex)
female | 1
male | 5
NULL | 6

If it is a string, the name, for example, generates the result of the type "John, Dick, Harry", which is the name sum.

Connection Query
connect two and more than two tables to select the data you want.

1) Internal connection query:
This record is queried when the value of a field with the same meaning in two tables is equal.
Instance:

Copy Code code 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's a good idea to specify which table's fields when d_id field values.

2) External connection query
Select Property Names list from table name 1 left|right join table name 2 on table Name 1. property name 1= table Name 2. property Name 2;
Left JOIN query:
When you make a left-join query, you can detect all the records in the table named in Table 1. Table Name 2 refers to a table in which only matching records can be queried.
Instance:

Copy Code code as follows:

Select Num,name,employee.d_id,age,d_name from employee left JOIN department on employee.d_id = department.d_id;

Right connection query:
Instead of left join, you can query all the records in Table Name 2, and in table name 1, only the matching records are queried.

PS: Using aggregate function query
aggregate functions include count (), SUM (), AVG (), Max (), and Min ().
1) count () function
Number of statistical record bars
Instance:

Select COUNT (*) from employee;

Use with GROUP by

Select D_id,count (*) from the employee group by D_ID;

The above statements are grouped before statistics.

2) sum () function
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 type fields.
3 avg () function
The AVG () function is the average function.
Instance:

Select AVG (age) from employee;

Select Course,avg (Score) from group by course;

4 max (), min () function
Find the maximum and minimum values.
Instance:
Select Max (age) from employee;
Select Num,course,max (Score) from grade group by course;
For the maximum problem of a string, the max () function is computed using the ASCII code corresponding to the character.