MySQL: A brief introduction to the use of concat and group_concat

The function of the concat() function: concatenate multiple strings into one string. Syntax: concat(str1, str2,...) returns the string generated by the connection parameters. If any parameter is null, the return value is null.

group_concat turns multiple rows of values ​​into one row after grouping. The original values ​​in each row are separated by commas

Note:

The examples used in this article are all below Execute under database table tt2:

1. concat() function

1. Function: Concatenate multiple strings into one string.

2. Syntax: concat(str1, str2,...)

The return result is the string generated by the connection parameters. If any parameter is null, the return value is null.

3. Example:

Example 1: select concat (id, name, score) as info from tt2;

There is one in the middle The behavior is null because there is a row in the tt2 table with a score value of null.

Example 2: In the result of Example 1, the combination of the three fields id, name, and score has no separator. We can add a comma as the separator:

This seems to be a lot more pleasing to the eye~~

But entering the sql statement is a lot of trouble. Three fields need to enter commas twice. If there are 10 fields, commas have to be entered nine times...It's so troublesome Okay, is there any easy way? ——Then concat_ws(), which can specify the separator between parameters, is here! ! !

2. concat_ws() function

1. Function: Same as concat(), concatenate multiple strings into A string, but you can specify the separator at once ~ (concat_ws is concat with separator)

2. Syntax: concat_ws(separator, str1, str2, ...)

Description: Chapter One parameter specifies the delimiter. It should be noted that the separator cannot be null. If it is null, the return result will be null.

3. Example:

Example 3: We use concat_ws() to specify the separator as a comma to achieve the same effect as Example 2:

Example 4: Specify the separator as null, and all the results become null:

3. group_concat() function

Preface: In the query statement with group by, the field specified by select is either included after the group by statement as the basis for grouping, or is included in the aggregate function. (For knowledge about group by, please click: A brief analysis of the use of Group By in SQL).

Example 5:

This example queries the smallest ID among people with the same name. What if we want to query all the IDs of people with the same name?

Of course we can query like this:

Example 6:

But if the same name appears multiple times, it seems very unintuitive. . Is there a more intuitive way that allows each name to appear only once, while also displaying the IDs of all people with the same name? ——Use group_concat()

1. Function: Connect the values ​​in the same group generated by group by and return a string result.

2. Syntax: group_concat( [distinct] Field to be connected [order by sorting field asc/desc ] [separator 'separator'] )

Description: Duplicates can be excluded by using distinct Value; if you want to sort the values ​​in the result, you can use the order by clause; separator is a string value, which defaults to a comma.

3. Example:

Example 7: Use group_concat() and group by to display the ID numbers of people with the same name:

Example 8: Sort the above ID numbers from large to small, and use '_' as the separator:

Example 9: The above query shows grouping by name All ids in each group. Next we need to query the id and score of all groups grouped by name:

Related articles:

The difference between concat and concat_ws() and group_concat(),repeat() string function

Using GROUP BY combined with GROUP_CONCAT in mysql

Related videos:

Introduction to installation of Apache and introduction to installation and use of MySQL

The above is the detailed content of MySQL: A brief introduction to the use of concat and group_concat. For more information, please follow other related articles on the PHP Chinese website!