使用concat做字符串拼接和数据迁移

作用:

　　解决一开始数据库建立不合理造成的字段冗余，从而提取部分字段，数据迁移、拼接字符串的功能。

格式：

　　concat(字段1,'间隔符',字段2....)

　　concat_ws('间隔符',字段1，字段2)

数据准备：

　　

#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

　　操作示例：

　　只做字符串的拼接：

mysql> select concat('name:',name) as mingzi,concat('sex:',sex) from  emp;
+-----------------+--------------------+
| mingzi          | concat('sex:',sex) |
+-----------------+--------------------+
| name:egon       | sex:male           |
| name:alex       | sex:male           |
| name:wupeiqi    | sex:male           |
| name:yuanhao    | sex:male           |
| name:liwenzhou  | sex:male           |
| name:jingliyang | sex:female         |
| name:jinxin     | sex:male           |
| name:成龙       | sex:male           |
| name:歪歪       | sex:female         |
| name:丫丫       | sex:female         |
| name:丁丁       | sex:female         |
| name:星星       | sex:female         |
| name:格格       | sex:female         |
| name:张野       | sex:male           |
| name:程咬金     | sex:male           |
| name:程咬银     | sex:female         |
| name:程咬铜     | sex:male           |
| name:程咬铁     | sex:female         |
+-----------------+--------------------+
18 rows in set (0.00 sec)

　　数据迁移（同时进行字符串拼接）：

mysql> create table aa select concat('name:',name) as mingzi,concat('sex:',sex) from  emp;
Query OK, 18 rows affected (0.03 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql>
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| aa            |
| big_data      |
| emp           |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from aa ;
+-----------------+--------------------+
| mingzi          | concat('sex:',sex) |
+-----------------+--------------------+
| name:egon       | sex:male           |
| name:alex       | sex:male           |
| name:wupeiqi    | sex:male           |
| name:yuanhao    | sex:male           |
| name:liwenzhou  | sex:male           |
| name:jingliyang | sex:female         |
| name:jinxin     | sex:male           |
| name:成龙       | sex:male           |
| name:歪歪       | sex:female         |
| name:丫丫       | sex:female         |
| name:丁丁       | sex:female         |
| name:星星       | sex:female         |
| name:格格       | sex:female         |
| name:张野       | sex:male           |
| name:程咬金     | sex:male           |
| name:程咬银     | sex:female         |
| name:程咬铜     | sex:male           |
| name:程咬铁     | sex:female         |
+-----------------+--------------------+
18 rows in set (0.00 sec)