本来有数据库A 、和数据库B
数据库A里表xuexiao 和数据库B表xuexiao是一样的(包括字段)
ID(自动递增,从1开始) aid(账号ID递增,从101001开始) name(账号可能有重复) pw(密码) std(学生) nl(年龄)我把B表里的xuexiao拷贝到A表里了,并重新命名为 xuexiao_copy。
现在表A里 有表xuexiao 和表xuexiao_copy!
请问我应该怎么将表xuexiao_copy里的内容合并到表xuexiao!表A里xuexiao内容
id aid name pw std nl
1 101001 aaaa 123456 张三 19
2 101002 gggg 11111 张四 18
3 101003 bbb 789 李五 18表A里xuexiao_copy内容
id aid name pw std nl
1 101001 oooo 123456 张思 19
2 101002 gggg 11111 小张 18
3 101003 bbb 789 李五 18
数据库A里表xuexiao 和数据库B表xuexiao是一样的(包括字段)
ID(自动递增,从1开始) aid(账号ID递增,从101001开始) name(账号可能有重复) pw(密码) std(学生) nl(年龄)我把B表里的xuexiao拷贝到A表里了,并重新命名为 xuexiao_copy。
现在表A里 有表xuexiao 和表xuexiao_copy!
请问我应该怎么将表xuexiao_copy里的内容合并到表xuexiao!表A里xuexiao内容
id aid name pw std nl
1 101001 aaaa 123456 张三 19
2 101002 gggg 11111 张四 18
3 101003 bbb 789 李五 18表A里xuexiao_copy内容
id aid name pw std nl
1 101001 oooo 123456 张思 19
2 101002 gggg 11111 小张 18
3 101003 bbb 789 李五 18
现在表A里 有表xuexiao 和表xuexiao_copy!
请问我应该怎么将表xuexiao_copy里的内容合并到表xuexiao!表A里xuexiao内容
id aid name pw std nl
1 101001 aaaa 123456 张三 19
2 101002 gggg 11111 张四 18
3 101003 bbb 789 李五 18表A里xuexiao_copy内容
id aid name pw std nl
1 101001 oooo 123456 张思 19
2 101002 gggg 11111 小张 18
3 101003 bbb 789 李五 18======================================
合并后的结果是什么?
同样主键如aid的记录忽略
insert ignore into xuexiao(aid,name,pw,std,nl) select aid,name,pw,std,nl from xuexiao_copy;
同样主键如aid的记录替换
replace into xuexiao(aid,name,pw,std,nl) select aid,name,pw,std,nl from xuexiao_copy;
id aid name pw std nl
1 101001 aaaa 123456 张三 19
2 101002 gggg 11111 张四 18
3 101003 bbb 789 李五 18 表A里xuexiao_copy内容
id aid name pw std nl
1 101001 oooo 123456 张思 19
2 101002 gggg 11111 小张 18
3 101003 bbb 789 李五 18 合并后新表 xuexiaoid aid name pw std nl
1 101001 aaaa 123456 张三 19
2 101002 gggg 11111 张四 18
3 101003 bbb 789 李五 18
4 101004 oooo 123456 张思 19
5 101006 gggga 11111 小张 18 (这里名字由于是重复所以加个a区分)
6 101006 bbba 789 李五 18 (这里名字由于是重复所以加个a区分)这个就是我预想合并后的结果!
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | aaaa | 123456 | 张三 | 19 |
| 2 | 101002 | gggg | 11111 | 张四 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)mysql> select * from xuexiao_copy;
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | oooo | 123456 | 张思 | 19 |
| 2 | 101002 | gggg | 11111 | 小张 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)mysql> insert into xuexiao (aid,name,pw,std,nl )
-> select aid,name,pw,std,nl
-> from xuexiao_copy
-> where name not in (select name from xuexiao)
-> union all
-> select aid,concat(name,'a'),pw,std,nl
-> from xuexiao_copy
-> where name in (select name from xuexiao);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from xuexiao;
+----+--------+-------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+-------+--------+------+------+
| 1 | 101001 | aaaa | 123456 | 张三 | 19 |
| 2 | 101002 | gggg | 11111 | 张四 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
| 4 | 101001 | oooo | 123456 | 张思 | 19 |
| 5 | 101002 | gggga | 11111 | 小张 | 18 |
| 6 | 101003 | bbba | 789 | 李五 | 18 |
+----+--------+-------+--------+------+------+
6 rows in set (0.02 sec)mysql>
是啊,其实我刚也感觉不好意思,没把表述好!
我在网上找了下,也说不合并重复行的用union all
合并重复行用 union
对loveflea(coolwind)的回答和我从网上找的资料感觉有点偏差所以,我才把我预想结果表示出来!在这里对 loveflea(coolwind)和ACMAIN_CHM(acmain) 表示感谢,同时也对loveflea(coolwind)表示道歉!因为我没说明白!在问下ACMAIN_CHM(acmain),你的这个我是把同名一样的修改了,但是我的aid如何做呢?
1 101001 aaaa 123456 张三 19
2 101002 gggg 11111 张四 18
3 101003 bbb 789 李五 18
4 101004 oooo 123456 张思 19
5 101006 gggga 11111 小张 18 (这里名字由于是重复所以加个a区分)
6 101006 bbba 789 李五 18 (这里名字由于是重复所以加个a区分)能解释一下这三条记录的新号码基于什么算法来的? 为什么 5,6 都更新为 101006?
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | aaaa | 123456 | 张三 | 19 |
| 2 | 101002 | gggg | 11111 | 张四 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)mysql> select * from xuexiao_copy;
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | oooo | 123456 | 张思 | 19 |
| 2 | 101002 | gggg | 11111 | 小张 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)
mysql> select * from cj;
+----+--------+--------+--------+------+------+
| id | aid | shuxue | yuwen | yyu | zh |
+----+--------+--------+--------+------+------+
| 1 | 101001 | 86 | 78 | 90 | 230 |
| 2 | 101002 | 87 | 89 | 92 | 240 |
| 3 | 101003 | 90 | 87 | 85 | 220 |
+----+--------+--------+--------+------+------+
3 rows in set (0.00 sec)
mysql> select * from cj_copy;
+----+--------+--------+--------+------+------+
| id | aid | shuxue | yuwen | yyu | zh |
+----+--------+--------+--------+------+------+
| 1 | 101001 | 80 | 90 | 86 | 270 |
| 2 | 101002 | 87 | 78 | 75 | 212 |
| 3 | 101003 | 98 | 92 | 89 | 260 |
+----+--------+--------+--------+------+------+
3 rows in set (0.00 sec)
本来数据库A里有2个表xuexiao cj她们是互相关联的!
本来数据库b里有2个表xuexiao cj她们是互相关联的!她们都是一样的字段一样的属性!
后来我把数据库B里的2个表拷贝到数据库A里来了,然后重命名 xuexiao_copy cj_copy我现在想将 xuexiao和xuexiao_copy 合并 ,cj和 cj_copy合并!并能正常使用!不敢轻易操作,怕把信息弄丢失了!所以才来请教高手的!
mysql> select * from xuexiao;
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | aaaa | 123456 | 张三 | 19 |
| 2 | 101002 | gggg | 11111 | 张四 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)mysql> select * from xuexiao_copy;
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | oooo | 123456 | 张思 | 19 |
| 2 | 101002 | gggg | 11111 | 小张 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)
mysql> select * from cj;
+----+--------+--------+--------+------+------+
| id | aid | shuxue | yuwen | yyu | zh |
+----+--------+--------+--------+------+------+
| 1 | 101001 | 86 | 78 | 90 | 230 |
| 2 | 101002 | 87 | 89 | 92 | 240 |
| 3 | 101003 | 90 | 87 | 85 | 220 |
+----+--------+--------+--------+------+------+
3 rows in set (0.00 sec)
mysql> select * from cj_copy;
+----+--------+--------+--------+------+------+
| id | aid | shuxue | yuwen | yyu | zh |
+----+--------+--------+--------+------+------+
| 1 | 101001 | 80 | 90 | 86 | 270 |
| 2 | 101002 | 87 | 78 | 75 | 212 |
| 3 | 101003 | 98 | 92 | 89 | 260 |
+----+--------+--------+--------+------+------+
3 rows in set (0.00 sec)下面是我预想合并后的结果!
这里xuexiao 字段AID和 cj字段AID是对应的!mysql> select * from xuexiao;
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | aaaa | 123456 | 张三 | 19 |
| 2 | 101002 | gggg | 11111 | 张四 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
| 4 | 101004 | oooo | 123456 | 张思 | 19 |
| 5 | 101005 |gggga | 11111 | 小张 | 18 |
| 6 | 101006 | bbba | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)mysql> select * from cj;
+----+--------+--------+--------+------+------+
| id | aid | shuxue | yuwen | yyu | zh |
+----+--------+--------+--------+------+------+
| 1 | 101001 | 86 | 78 | 90 | 230 |
| 2 | 101002 | 87 | 89 | 92 | 240 |
| 3 | 101003 | 90 | 87 | 85 | 220 |
| 4 | 101004 | 80 | 90 | 86 | 270 |
| 5 | 101005 | 87 | 78 | 75 | 212 |
| 6 | 101006 | 98 | 92 | 89 | 260 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | aaaa | 123456 | 张三 | 19 |
| 2 | 101002 | gggg | 11111 | 张四 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)mysql> select * from xuexiao_copy;
+----+--------+------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+------+--------+------+------+
| 1 | 101001 | oooo | 123456 | 张思 | 19 |
| 2 | 101002 | gggg | 11111 | 小张 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
+----+--------+------+--------+------+------+
3 rows in set (0.00 sec)mysql> insert into xuexiao (aid,name,pw,std,nl )
-> select aid+3,name,pw,std,nl
-> from xuexiao_copy
-> where name not in (select name from xuexiao)
-> union all
-> select aid+3,concat(name,'a'),pw,std,nl
-> from xuexiao_copy
-> where name in (select name from xuexiao);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from xuexiao;
+----+--------+-------+--------+------+------+
| id | aid | name | pw | std | nl |
+----+--------+-------+--------+------+------+
| 1 | 101001 | aaaa | 123456 | 张三 | 19 |
| 2 | 101002 | gggg | 11111 | 张四 | 18 |
| 3 | 101003 | bbb | 789 | 李五 | 18 |
| 4 | 101004 | oooo | 123456 | 张思 | 19 |
| 5 | 101005 | gggga | 11111 | 小张 | 18 |
| 6 | 101006 | bbba | 789 | 李五 | 18 |
+----+--------+-------+--------+------+------+
6 rows in set (0.00 sec)mysql>
select aid+3,shuxue,yuwen,yyu,zh from cj_copy