字段连接

连个表中字段的合并

create table `test3`
as
select distinct name from test1
union
select distinct name from test2

test1 有street和No两个字段 现在想要把Street和No两个字段连在一起 何必成一个完整的地址保存在test2中
mysql> select * from test1
-> ;
+----+---------------+----------+
| id | street | No |
+----+---------------+----------+
| 1 | jian she road | 8 number |
| 2 | bin he road | 9 number |
+----+---------------+----------+

mysql> select * from test2;
+----+---------+-------------------------+
| id | test_id | address |
+----+---------+-------------------------+
| 1 | 1 | null |
| 2 | 2 | null |
+----+---------+-------------------------+
首先通过需要把street和No的值取到并何必再一起
尝试方法1：select (street+No) from test1;
显示效果为
mysql> select (street+No) from test1
+-------------+
| (street+No) |
+-------------+
| 8 |
| 9 |
+-------------+
失败

尝试方法2：select (test1.street+" "+test1.No) from test1;
显示效果为
mysql> select (test1.street+" "+test1.No) from test1;
+-----------------------------+
| (test1.street+" "+test1.No) |
+-----------------------------+
| 8 |
| 9 |
+-----------------------------+
2 rows in set (0.00 sec)失败

尝试方法3：利用concat函数实现
mysql> select concat(street,No) from test1;
+-----------------------+
| concat(street,No) |
+-----------------------+
| jian she road8 number |
| bin he road9 number |
+-----------------------+
结果已经出来，但是显示效果不理想
修改为
mysql> select concat(street," ",No) from test1;
+------------------------+
| concat(street," ",No) |
+------------------------+
| jian she road 8 number |
| bin he road 9 number |
+------------------------+
大功告成！
注意使用cancat函数时需要注意的地方。
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
如
mysql> select * from test1;
+----+-----------------+----------+
| id | street | No |
+----+-----------------+----------+
| 1 | jian she road | 8 number |
| 2 | bin he road | 9 number |
| 6 | shen nan da dao | NULL |
+----+-----------------+----------+

mysql> select concat(street," ",No) from test1;
+------------------------+
| concat(street," ",No) |
+------------------------+
| jian she road 8 number |
| bin he road 9 number |
| NULL |
+------------------------+
遇到这种情况怎么办呢，就需要用到另一个函数
coalesce
mysql> select concat(street," ",coalesce(No,'')) from test1;
+------------------------------------+
| concat(street," ",coalesce(No,'')) |
+------------------------------------+
| jian she road 8 number |
| bin he road 9 number |
| shen nan da dao |
+------------------------------------+
实现了自己想要的结果。
去除重复还可以使用distinct
mysql> select distinct(id) from test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 6 |
+----+