Shulou(Shulou.com)06/01 Report--

[PHP]

1. Concat () function

1.1 the concat function of MySQL can concatenate one or more strings, such as

Mysql > select concat ('10')

+-+

| | concat ('10') | |

+-+

| | 10 |

+-+

1 row in set (0.00 sec)

Mysql > select concat ('11, 22, 22, 33)

+-- +

| | concat ('11, 22, 33) | |

+-- +

| | 112233 |

+-- +

1 row in set (0.00 sec)

The concat function of Oracle can only concatenate two strings.

SQL > select concat ('11 years, 22') from dual

1.2 when concatenating a string, the concat function of MySQL will return NULL as long as one of them is NULL

Mysql > select concat ('11, 22, 13, 22, 9, 9, 9, 9, 22, 9, 9, 22, 9, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 9, 9, 6, 6, 6, 6, 6, 6, 9, 6, 6, 6, 6, 6, 6, 9, 9, 6, 6, 6, 6, 6, 6, 6, 6, 9, 9, 9, 9, 9, 9, 9, 9, 6, 6, 9, 9, 6, 9, 9, 6, 6, 6, 9, 6, 6, 6, 9, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6

+-- +

| | concat ('11 years, 22 minutes, 22 months) | |

+-- +

| | NULL |

+-- +

1 row in set (0.00 sec)

When the concat function of Oracle is concatenated, as long as a string is not NULL, it will not return NULL.

SQL > select concat ('11percent last null) from dual

CONCAT

--

eleven

2. The concat_ws () function, which represents concat with separator, that is, a string concatenation with delimiters

If separated by commas after connection

Mysql > select concat_ws (',','11, 22, 22, 33)

+-+

| | concat_ws (',','11), 22 (33) | |

+-+

| | 11pr 22 pr 33 | |

+-+

1 row in set (0.00 sec)

Unlike concat, the concat_ws function does not return NULL because of the NULL value when it is executed

Mysql > select concat_ws (',','11,'11,'11,'11,'11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,','11,')

+-+

| | concat_ws (',','11 minutes, 22 minutes, null) | |

+-+

| | 11pr 22 |

+-+

1 row in set (0.00 sec)

3. Group_concat () can be used for row transfer, but Oracle does not have such a function.

The complete syntax is as follows

Group_concat ([DISTINCT] the field to be connected [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 grouped in id, printing the values of the name field on one line, separated by commas (default)

Mysql > select id,group_concat (name) from aa group by id

+-+ +

| | id | group_concat (name) |

+-+ +

| | 1 | 10, 20, 20, 20 |

| | 2 | 20 |

| | 3 | 200500 | |

+-+ +

3 rows in set (0.00 sec)

3.2 grouped in id, printing the values of the name field on one line, separated by semicolons

Mysql > select id,group_concat (name separator';') from 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.grouping in id, printing the values of the de-redundant name fields on a single line, separated by commas

Mysql > select id,group_concat (distinct name) from aa group by id

+-- +

| | id | group_concat (distinct name) |

+-- +

| | 1 | 100.20 |

| | 2 | 20 |

| | 3 | 200500 | |

+-- +

3 rows in set (0.00 sec)

3.4 grouped in id, printing the values of the name field on a line, separated by commas, and in reverse order in 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 | 500200 | |

+-- +

3 rows in set (0.00 sec)

4. The repeat () function is used to copy the string, as follows: 'ab'' indicates the string to be copied, and 2 represents the number of copies

Mysql > select repeat ('ab',2)

+-+

| | repeat ('ab',2) | |

+-+

| | abab |

+-+

1 row in set (0.00 sec)

Also such as

Mysql > select repeat ('axioma2)

+-+

| | repeat ('astatine 2) | |

+-+

| | aa |

+-+

1 row in set (0.00 sec)

..

[/ PHP]

[@ more@]

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.