Tips for querying statements

1. Group_concat: (To make a field of grouped data appear as a comma)

Group_concat (field), Manual description: This function returns a string result with a non-null value for a connection from a group.
More abstract, difficult to understand.

Popular point understanding, in fact, is this: Group_concat () calculates which rows belong to the same group, and displays the columns that belong to the same group. Which columns to return, by letter

The number of parameters (that is, the field name) determines. The grouping must have a standard, which is to group by the columns specified by the group by.

The Group_concat function should be executing the GROUP BY statement internally, which is my guess.

1. Test statement: SELECT group_concat from ' players ' group by town

The result is to find out which values are the same in the town, and if they are equal, they are all listed, separated by commas, as follows:

Group_concat (town)

BEIJING, Beijing
Changsha


2. Test: SELECT group_concat (town)
From players
Results:
Group_concat (town)
Changsha, Beijing, Beijing,

Can it be proved that Group_concat only works with the group by statement? The following is a practical quiz


3. Test constants for Group_concat () configuration effects:
SET @ @GROUP_CONCAT_MAX_LEN =4
The syntax for setting is mentioned in the manual:
SET [SESSION | GLOBAL] Group_concat_max_len = val;

What's the difference between these two?

SET @ @global. group_concat_max_len=4;
Global can be omitted, then it becomes: SET @ @GROUP_CONCAT_MAX_LEN = 4;


4. Use the statement SELECT Group_concat from ' players '. The results are:
Group_concat (town)
Changsha, Beijing, Changsha, Beijing
Conclusion: the Group_concat () function needs to be used together with the group by statement to get the desired effect.
The reason can be understood as follows: Group_concat () gets all the members that belong to the X group (the column parameter inside the function specifies which fields need to be displayed). Where does the X group come from? Such as

If no group BY is specified, then it is not known that group_concat () displays the members according to which group. So, like there's no GROUP BY clause above

, it shows Changsha and Beijing.


When does this function actually need to be used?
If you need to query the result is this: Display the group name on the left, to the right to display all the member information under the group. With this function, you can save a lot of things.

In addition, if I use this: SELECT group_concat (name, sex) from the ' Players ' town. It doesn't make much sense. Group_concat () specifies a

column is the best case. If multiple columns are specified. Then the results are similar to this:

Group_concat (Name,sex)
Wang Yu, Wang Xiaoming man, Liu Huiju, Shuming

2.

Tips for querying statements