Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use concat and group_concat in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The function of the concat () function: concatenates multiple strings into a single string. Syntax: concat (str1, str2,...) The result is the string produced by the connection parameter, and if any of the parameters is null, the return value is null.

After group_concat grouping, the values of multiple rows are changed into one line, and the values of the original rows are separated by commas.

Description:

The examples used in this article are performed under the following database table tt2:

1. Concat () function

1. Function: concatenate multiple strings into a single string.

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

The result is the string produced by the connection parameter, and if any of the parameters is null, the return value is null.

3. For example:

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

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

Example 2: in the result of example 1, there is no delimiter for the combination of the three fields id,name,score. We can add a comma as the delimiter:

This seems to be pleasing to the eye.

But entering sql statements is a lot of trouble, three fields need to enter commas twice, if 10 fields, enter commas nine times. I'm in so much trouble. Is there any easy way? -- so concat_ws () that can specify the delimiter between parameters comes!

Second, concat_ws () function

1. Function: like concat (), concatenate multiple strings into a string, but you can specify the delimiter ~ (concat_ws is concat with separator) at one time.

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

Description: the first parameter specifies the delimiter. It is important to note that the delimiter cannot be null. If it is null, the return result is null.

3. For example:

Example 3: we use concat_ws () to specify the delimiter as a comma to achieve the same effect as example 2:

Example 4: specify the delimiter as null, and the result is all null:

Third, group_concat () function

In the query statement with group by, the fields specified by select are either included after the group by statement as the basis for grouping, or in the aggregate function. Please stamp the knowledge about group by: analyze 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 id of people with the same name?

Of course, we can inquire like this:

Example 6:

But it seems very unintuitive that the same name appears many times in this way. Is there a more intuitive way to make each name appear only once and display the id of all people with the same name? -- use group_concat ()

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

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

Note: duplicate values can be excluded by using distinct; if you want to sort the values in the results, you can use the order by clause; separator is a string value and defaults to a comma.

3. For example:

Example 7: use group_concat () and group by to display the id number of a person with the same name:

Example 8: sort the above id number from large to small, and use'_'as the delimiter:

Example 9: the above query shows all the id in each group grouped in name. Next we will query the id and score of all groups grouped in name:

The above is all the content of the article "how to use concat and group_concat in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.