Concat syntax of MySQL

Time:2021-2-25

1、 Concat() function

effect

Concatenates multiple strings into a single string

The test data are as follows

 

#There is no separator
  

 select concat (id,user_name,pwd) as temp from t_user;

 

 

#Add separator

 

 select concat (id, ',', user_name, ',', pwd) as temp from t_user;

 

 

To query multiple fields, you need to enter the separator repeatedly, which will cause trouble

 

  

2、 Concat_ Ws() function

effectLike concat(), concatenates multiple strings into a single string, but you can specify the separator at one time

(!! — note that the first parameter specifies the separator. It should be noted that the separator cannot be null. If it is null, the return result will be null. )

 

 select concat_ws(',', id,user_name,pwd) as temp from t_user;

 

 

 

3、 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 of grouping or in the aggregate function.

 

select gender, id, user_name from t_user group by gender;

 

  

 

It’s true that they are grouped by gender, but how to view the data in each group after grouping?

Use – group_ concat()

 

select 
       gender, group_concat(id) as ids, group_concat(user_name) as names
from 
     t_user
group by 
         gender;