MySQL SQL character join function Concat Concat_ws

MySQL SQL character join function
1, Concat () function
    1.1 MySQL's Concat function can connect one or more strings, such as
   & nbsp;    mysql> Select concat (' 10 ');
        +--------------+
        | Concat (' 10 ') |
        +--------------+
        | 10           |
        +--------------+
       1 row in Set (0.00 sec)

Mysql> Select concat (' 11 ', ' 22 ', ' 33 ');
+------------------------+
| Concat (' 11 ', ' 22 ', ' 33 ') |
+------------------------+
| 112233 |
+------------------------+

1 row in Set (0.00 sec)

and Oracle's CONCAT function can only connect two strings

Sql> Select Concat (' One ', ' all ') from dual;

1.2 MySQL's concat function when the string is concatenated, as long as one of them is NULL, NULL is returned

Mysql> Select Concat (' One ', ' all ', null);
+------------------------+
| Concat (' One ', ', ', null) |
+------------------------+
| NULL |
+------------------------+
1 row in Set (0.00 sec)

When Oracle's Concat function is connected, as long as there is a string that is not NULL, it does not return

Null

Sql> Select Concat (' One ', NULL) from dual;
Concat
--
11

2, Concat_ws () function, representing concat with separator, that is, a delimited string connection
Separated by commas after connection
Mysql> Select Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ');

+-------------------------------+
| Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ') |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in Set (0.00 sec)

Unlike concat, the CONCAT_WS function does not return NULL because of a null value at the time it is executed.
Mysql> Select Concat_ws (', ', ', ', ', ');
+-------------------------------+
| Concat_ws (', ', ' One ', ' ', ', NULL ') |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in Set (0.00 sec)

3, Group_concat () can be used for row-column, Oracle does not have such a function

The complete syntax is as follows
Group_concat ([DISTINCT] The field to connect to [order by asc/desc sort field]

[Separator ' delimiter '])
The following example
Mysql> select * from AA;

+------+------+
| ID | name |
+------+------+
| 1 | 10 |
| 1 | 20 |
| 1 | 20 |
| 2 | 20 |
| 3 | 200 |
| 3 | 500 |
+------+------+
6 rows in Set (0.00 sec)
3.1 Group by ID, print the value of the Name field on one line, comma separated (default)
Mysql> Select Id,group_concat (name) from AA Group by ID;
+------+--------------------+
| ID | Group_concat (name) |
+------+--------------------+
| 1 | 10,20,20 |
| 2 | 20 |
| 3 | 200,500 |
+------+--------------------+

3 Rows in Set (0.00 sec)

3.2 Grouped by ID, print the value of the Name field on one line, separated by semicolons
Mysql> Select ID,GROUP_CONCAT (name separator '; ') from the AA group by

Id
+------+----------------------------------+
| ID | Group_concat (name separator '; ') |
+------+----------------------------------+
| 1 | 10;20;20 |
| 2 | 20 |
| 3 | 200;500 |
+------+----------------------------------+

3 Rows in Set (0.00 sec)

3.3 Grouped by ID, the value of the Name field to be redundant is printed on one line, separated by commas

Mysql> Select Id,group_concat (distinct name) from the AA group by ID;

        +------+-----------------------------+
         | id   | Group_concat (distinct name) |
        +------+-----------------------------+
         |    1 | 10,20                        |
        |    2 | 20                            |
        |    3 | 200,500                      |
        +------+-----------------------------+

3 Rows in Set (0.00 sec)

3.4 Group by ID, print the value of the Name field on one line, comma delimited, and order by name

Mysql> Select Id,group_concat (name order BY name Desc) from AA Group

by ID;

+------+---------------------------------------+
| ID | Group_concat (name order BY name Desc) |
+------+---------------------------------------+
| 1 | 20,20,10 |
| 2 | 20 |
| 3 | 500,200 |
+------+---------------------------------------+

3 Rows in Set (0.00 sec)

4, repeat () function, to copy the string, the following ' AB ' represents the string to be copied, 2 indicates the number of copies copied

Mysql> Select repeat (' AB ', 2);

+----------------+
| Repeat (' AB ', 2) |
+----------------+
| Abab |
+----------------+

1 row in Set (0.00 sec)

And AS
Mysql> Select repeat (' a ', 2);

+---------------+
| Repeat (' a ', 2) |
+---------------+
| AA |
+---------------+
1 row in Set (0.00 sec)