MySQL中的拼接Concat,Trim

最新推荐文章于 2024-07-02 17:40:08 发布

sheygshsi

最新推荐文章于 2024-07-02 17:40:08 发布

阅读量1.5k

点赞数 2

分类专栏： 11 文章标签： sql

本文链接：https://blog.csdn.net/m0_55212193/article/details/121687673

版权

11 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

MySQL中的拼接Concat

在MySQL的SELECT语句中,可使用 Concat()函数来拼接两个列.
Concat()拼接串,即把多个串连接起来形成一个较长的串.
Concat()需要一个或多个指定的串, 各个串之间用逗号分隔.
Trim()函数用来去掉 串左右两边的空格
LTrim()函数用来去掉 串左边的空格
RTrim()函数用来去掉 串右边的空格

#注意:' 粉底液 '左右两边有空格
mysql> update product set name=' 粉底液 ' where id='c007';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product;
+------+----------+-------+
| id   | name     | price |
+------+----------+-------+
| c001 | 防晒霜   |    68 |
| c002 | 护手霜   |    23 |
| c003 | 面膜     |    79 |
| c004 | 粉饼     |    58 |
| c005 | 口红     |    59 |
| c006 | 水乳     |    68 |
| c007 |  粉底液  |    68 |
| c003 | 卸妆油   |  NULL |
| c003 | NULL     |  NULL |
+------+----------+-------+
9 rows in set (0.01 sec)

mysql> select Concat(id,' (',name,')') from product;
+--------------------------+
| Concat(id,' (',name,')') |
+--------------------------+
| c001 (防晒霜)            |
| c002 (护手霜)            |
| c003 (面膜)              |
| c004 (粉饼)              |
| c005 (口红)              |
| c006 (水乳)              |
| c007 ( 粉底液 )          |
| c003 (卸妆油)            |
| NULL                     |
+--------------------------+
9 rows in set (0.00 sec)

mysql> select Concat(id,name) from product;
+-----------------+
| Concat(id,name) |
+-----------------+
| c001防晒霜      |
| c002护手霜      |
| c003面膜        |
| c004粉饼        |
| c005口红        |
| c006水乳        |
| c007 粉底液     |
| c003卸妆油      |
| NULL            |
+-----------------+
9 rows in set (0.00 sec)

mysql> select Concat(id,' (',RTrim(name),')') from product;
+---------------------------------+
| Concat(id,' (',RTrim(name),')') |
+---------------------------------+
| c001 (防晒霜)                   |
| c002 (护手霜)                   |
| c003 (面膜)                     |
| c004 (粉饼)                     |
| c005 (口红)                     |
| c006 (水乳)                     |
| c007 ( 粉底液)                  |#看'粉底液'右边的空格去掉了
| c003 (卸妆油)                   |
| NULL                            |
+---------------------------------+
9 rows in set (0.00 sec)

mysql> select Concat(id,' (',LTrim(name),')') from product;
+---------------------------------+
| Concat(id,' (',LTrim(name),')') |
+---------------------------------+
| c001 (防晒霜)                   |
| c002 (护手霜)                   |
| c003 (面膜)                     |
| c004 (粉饼)                     |
| c005 (口红)                     |
| c006 (水乳)                     |
| c007 (粉底液 )                  |#'粉底液'右边的空格去掉了
| c003 (卸妆油)                   |
| NULL                            |
+---------------------------------+
9 rows in set (0.01 sec)

mysql> select Concat(id,' (',Trim(name),')') from product;
+--------------------------------+
| Concat(id,' (',Trim(name),')') |
+--------------------------------+
| c001 (防晒霜)                  |
| c002 (护手霜)                  |
| c003 (面膜)                    |
| c004 (粉饼)                    |
| c005 (口红)                    |
| c006 (水乳)                    |
| c007 (粉底液)                  |#'粉底液'左右两边的空格去掉了
| c003 (卸妆油)                  |
| NULL                           |
+--------------------------------+

SELECT通常用来从表中检索数据,但可以省略from子句以便简单地访问和处理表达式.
如:

mysql> select 3*2;
+-----+
| 3*2 |
+-----+
|   6 |
+-----+
1 row in set (0.00 sec)

mysql> select Trim(' abc ');#利用Trim()函数去掉串左右两边的空格
+---------------+
| Trim(' abc ') |
+---------------+
| abc           |
+---------------+
1 row in set (0.00 sec)

mysql> select Now();#利用Now()函数返回当前的日期和时间
+---------------------+
| Now()               |
+---------------------+
| 2021-12-02 22:15:43 |
+---------------------+
1 row in set (0.01 sec)