以前架征途时的合区的SQL语句代码备份
网络编程 2021-07-05 16:49www.168986.cn编程入门
本来以为资料都是丢了的，今天整理移动硬盘时发现found.000这个目录超大，进去一看，我的妈呀，资料都在这里了，这下可把我乐坏了，我赶紧把一些有用的都发上来先
su - mysql -c “mysqld_safe –skip-name-resolve –open-files-limit=4096&”
cd /usr/local/mysql/bin/
mysqldump -u用户名 -p密码 –databases 库名 >/backdata/ddd07-11-15.sql
mysqldump –databases zt >/data/ok1.sql
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt</backdata/kb.sql
mysqldump -uroot -pueoadir –databases zt >/backdata/back1210.sql
su - mysql -c “mysqld_safe –skip-name-resolve –open-files-limit=4096&”
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt4</backdata/new.sql
mysql -uroot -pueoadir zt</data/ok.sql
UE处理下负区导入的表
mysql FLServer</data/FLServer.sql
mysql GMTool</data/GMTool.sql
mysql LoginServer</data/LoginServer.sql
mysql roleChangeServer</data/roleChangeServer.sql
mysql roleregServer</data/roleregServer.sql
mysql unify00</data/unify00.sql
mysql FLServer</data/FLServer.sql
mysql GMTool</data/GMTool.sql
mysql LoginServer</data/LoginServer.sql
mysql roleChangeServer</data/roleChangeServer.sql
mysql roleregServer</data/roleregServer.sql
mysql unify00</data/unify00.sql
mysql FLServer</data/FLServer.sql
mysql zt</data/zt501.sql
mysql -uroot -pueoadir zt</backdata/kb.sql
查询
select from zt4.CHARBASE where name in (select name from zt.zHARBASE)
select from CHARBASE limit 100
select UNIONID from CHARBASE limit 100
更新
update zt4.CHARBASE set name=CONCAT('一区',rtrim(name)) where name in (select name from zt.CHARBASE) ‘处理重名
update CHARBASE set name=CONCAT(rtrim(name),'1q')
update CHARBASE set CHARID=CHARID+10
插入
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
insert into game.user(name,pass) select name,pass from game2.user2
导入
mysql -uroot -pueoadir zt</backdata/zt2q.sql
mysql -uroot -pueoadir zt</backdata/1q.sql
删除
delete from CHARBASE WHERE ROUND=0 AND LEVEL < 80 and `LASTACTIVEDATE`< ‘2007-11-19 00:00:00′
delete from CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-19 00:00:00′
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-12-10 00:00:00′
delete from zt.CHARBASE WHERE ROUND=0 AND LEVEL<80 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
2.去除GM CHARID编号重复
SELECT max(CHARID) FROM `CHARBASE`
update zt4.CHARBASE set CHARID=CHARID+10000 where CHARID < 100 LIMIT 50
update zt4.CHARBASE set CHARID=CHARID+20000 where CHARID in (select CHARID from zt.CHARBASE)
3.去除人物重名
update zt4.CHARBASE set name=CONCAT(rtrim(name),'o') where name in (select name from zt.CHARBASE)
4.插入表2数据到表1
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
________________________________________________
delete from zt4.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt4.CHARBASE WHERE ROUND=0 AND LEVEL<96 and `LASTACTIVEDATE`< ‘2007-12-10 00:00:00′
SELECT max(CHARID) FROM zt4.CHARBASE
select from zt4.CHARBASE limit 100
update zt4.CHARBASE set CHARID=CHARID+10000 where CHARID in (select CHARID from zt.CHARBASE)
update zt4.CHARBASE set name=CONCAT(rtrim(name),'oo') where name in (select name from zt.CHARBASE)
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
___________________________________________________________________________________
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt4.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt.CHARBASE WHERE ROUND=0 AND LEVEL<80 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt4.CHARBASE WHERE ROUND=0 AND LEVEL<80 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
2.CHARBASE表里CHARID处理重复 重复ID 前加10000
update zt4.CHARBASE set zt4.CHARBASE.CHARID=zt4.CHARBASE.CHARID+10000 where zt4.CHARBASE.CHARID in (select CHARID from zt.CHARBASE)
CARTOONPET表里CARTOONID与主库CARTOONPET重复的处理
update zt4.CARTOONPET set zt4.CARTOONPET.CARTOONID=zt4.CARTOONPET.CARTOONID+10000 where zt4.CARTOONPET.CARTOONID in (SELECT zt4.CARTOONPET.CARTOONID from zt.CARTOONPET)
3.SEPT表里SEPTID 处理重复，重复ID前加10000
update zt4.SEPT,zt.SEPT set zt4.SEPT.SEPTID= zt4.SEPT.SEPTID+10000 where zt4.SEPT.SEPTID in (select SEPTID from zt.SEPT)
4.UNIONMEMBER表里UNIONID处理重复前加10000
update zt4.UNIONMEMBER set zt4.UNIONMEMBER.UNIONID= zt4.UNIONMEMBER.UNIONID+10000 where zt4.UNIONMEMBER.UNIONID in (select UNIONID from zt.UNIONMEMBER)
5.UNIONMEMBER表里SEPTID 与SEPT表里的同步
update zt4.UNIONMEMBER,zt4.SEPT set zt4.UNIONMEMBER.SEPTID=zt4.SEPT.SEPTID WHERE zt4.UNIONMEMBER.NAME=zt4.SEPT.MASTER
6.UNION表里UNIONID 与UNIONMEMBER表里的同步
update zt4.UNION,zt4.UNIONMEMBER set zt4.UNION.UNIONID=zt4.UNIONMEMBER.UNIONID WHERE zt4.UNION.MASTER=zt4.UNIONMEMBER.NAME
7.SEPT表里UNIONID 与UNIONMEMBER表里的同步
update zt4.SEPT,zt4.UNIONMEMBER set zt4.SEPT.UNIONID=zt4.UNIONMEMBER.UNIONID WHERE zt4.SEPT.MASTER=zt4.UNIONMEMBER.NAME
8.CHARBASE表里SEPTID处理与SEPT表一致
update zt4.CHARBASE,zt4.SEPT set zt4.CHARBASE.SEPTID=zt4.SEPT.SEPTID WHERE zt4.CHARBASE.NAME=zt4.SEPT.NAME
9.CHARBASE表里UNIONID处理与UNIONMEMBER的UNIONID表一致
update zt4.CHARBASE,zt4.UNIONMEMBER set zt4.CHARBASE.UNIONID=zt4.UNIONMEMBER.UNIONID WHERE zt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME
10.SEPT表里CHARID与CHARBASE里的CHARID一致
update zt4.CHARBASE,zt4.SEPT set zt4.SEPT.CHARID=zt4.CHARBASE.CHARID WHERE zt4.CHARBASE.NAME=zt4.SEPT.MASTER
CARTOONPET表里MASTERID与CHARBASE里的CHARID一致
update zt4.CARTOONPET,zt4.CHARBASE set zt4.CARTOONPET.MASTERID=zt4.CHARBASE.CHARID where zt4.CHARBASE.NAME=zt4.CARTOONPET.MASTERNAME
12.UNIONMEMBER表里CHARID与CHARBASE里的CHARID一致
update zt4.CHARBASE,zt4.UNIONMEMBER set zt4.UNIONMEMBER.CHARID=zt4.CHARBASE.CHARID WHERE zt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME
13.SCHOOLMEMBER表里CHARID与CHARBASE里的CHARID一致
update zt4.CHARBASE,zt4.SCHOOLMEMBER set zt4.SCHOOLMEMBER.CHARID=zt4.CHARBASE.CHARID WHERE zt4.CHARBASE.NAME=zt4.SCHOOLMEMBER.NAME
14.CHARBASE表里NAME与主库CHARBASE名字重复的处理
update zt4.CHARBASE set name=CONCAT(rtrim(name),'oo') where name in (select name from zt.CHARBASE)
15.SEPT表里NAME与主库SEPT名字重复的处理
update zt4.SEPT set zt4.SEPT.NAME=CONCAT(rtrim(zt4.SEPT.NAME),'oo') where zt4.SEPT.NAME in (select NAME from zt.SEPT)
16.UNION表里NAME与主库UNION名字重复的处理
update zt4.UNION set zt4.UNION.NAME=CONCAT(rtrim(zt4.UNION.NAME),'oo') where zt4.UNION.NAME in (select NAME from zt.UNION)
17.SCHOOLMEMBER表里SERIALID与主库SCHOOLMEMBER名字重复的处理
update zt4.SCHOOLMEMBER set zt4.SCHOOLMEMBER.SERIALID=zt4.SCHOOLMEMBER.SERIALID+10000 where zt4.SCHOOLMEMBER.SERIALID in (select SERIALID from zt.SCHOOLMEMBER)
18.SEPT表里MASTER人物名与CHAREBASE里的人物名同步
update zt4.CHARBASE,zt4.SEPT set zt4.SEPT.MASTER=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.SEPT.CHARID
19.SCHOOLMEMBER表里NAME与CHARBASE人物名同步
update zt4.CHARBASE,zt4.SCHOOLMEMBER set zt4.SCHOOLMEMBER.NAME=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.SCHOOLMEMBER.CHARID
20.UNION表里MASTER人物名与CHAREBASE里的人物名同步
update zt4.CHARBASE,zt4.UNION set zt4.UNION.NAME=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.UNION.CHARID
22.CARTOONPET表里MASTERNAME与CHAREBASE里的人物名同步
update zt4.CHARBASE,zt4.CARTOONPET set zt4.CARTOONPET.MASTERNAME=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.CARTOONPET.MASTERID
21.合并CHARBASE数据库
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
22.合并SCHOOLMEMBER数据库
insert into zt.SCHOOLMEMBER SELECT FROM zt4.SCHOOLMEMBER
23.合并SEPT数据库
insert into zt.SEPT SELECT FROM zt4.SEPT
24.合并UNIONMEMBER数据库
insert into zt.UNIONMEMBER SELECT FROM zt4.UNIONMEMBER
25.合并UNION
insert into zt.UNION SELECT FROM zt4.UNION
26.合并CARTOONPET
insert into zt.CARTOONPET SELECT FROM zt4.CARTOONPET
26.合并BALANCE
insert into zt.BALANCE SELECT FROM zt4.BALANCE
cd /usr/local/mysql/bin/
mysqldump -u用户名 -p密码 –databases 库名 >/backdata/ddd07-11-15.sql
mysqldump –databases zt >/data/ok1.sql
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt</backdata/kb.sql
mysqldump -uroot -pueoadir –databases zt >/backdata/back1210.sql
su - mysql -c “mysqld_safe –skip-name-resolve –open-files-limit=4096&”
cd /usr/local/mysql/bin/
mysql -uroot -pueoadir zt4</backdata/new.sql
mysql -uroot -pueoadir zt</data/ok.sql
UE处理下负区导入的表
mysql FLServer</data/FLServer.sql
mysql GMTool</data/GMTool.sql
mysql LoginServer</data/LoginServer.sql
mysql roleChangeServer</data/roleChangeServer.sql
mysql roleregServer</data/roleregServer.sql
mysql unify00</data/unify00.sql
mysql FLServer</data/FLServer.sql
mysql GMTool</data/GMTool.sql
mysql LoginServer</data/LoginServer.sql
mysql roleChangeServer</data/roleChangeServer.sql
mysql roleregServer</data/roleregServer.sql
mysql unify00</data/unify00.sql
mysql FLServer</data/FLServer.sql
mysql zt</data/zt501.sql
mysql -uroot -pueoadir zt</backdata/kb.sql
查询
select from zt4.CHARBASE where name in (select name from zt.zHARBASE)
select from CHARBASE limit 100
select UNIONID from CHARBASE limit 100
更新
update zt4.CHARBASE set name=CONCAT('一区',rtrim(name)) where name in (select name from zt.CHARBASE) ‘处理重名
update CHARBASE set name=CONCAT(rtrim(name),'1q')
update CHARBASE set CHARID=CHARID+10
插入
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
insert into game.user(name,pass) select name,pass from game2.user2
导入
mysql -uroot -pueoadir zt</backdata/zt2q.sql
mysql -uroot -pueoadir zt</backdata/1q.sql
删除
delete from CHARBASE WHERE ROUND=0 AND LEVEL < 80 and `LASTACTIVEDATE`< ‘2007-11-19 00:00:00′
delete from CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-19 00:00:00′
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-12-10 00:00:00′
delete from zt.CHARBASE WHERE ROUND=0 AND LEVEL<80 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
2.去除GM CHARID编号重复
SELECT max(CHARID) FROM `CHARBASE`
update zt4.CHARBASE set CHARID=CHARID+10000 where CHARID < 100 LIMIT 50
update zt4.CHARBASE set CHARID=CHARID+20000 where CHARID in (select CHARID from zt.CHARBASE)
3.去除人物重名
update zt4.CHARBASE set name=CONCAT(rtrim(name),'o') where name in (select name from zt.CHARBASE)
4.插入表2数据到表1
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
________________________________________________
delete from zt4.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt4.CHARBASE WHERE ROUND=0 AND LEVEL<96 and `LASTACTIVEDATE`< ‘2007-12-10 00:00:00′
SELECT max(CHARID) FROM zt4.CHARBASE
select from zt4.CHARBASE limit 100
update zt4.CHARBASE set CHARID=CHARID+10000 where CHARID in (select CHARID from zt.CHARBASE)
update zt4.CHARBASE set name=CONCAT(rtrim(name),'oo') where name in (select name from zt.CHARBASE)
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
___________________________________________________________________________________
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
delete from zt.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt4.CHARBASE WHERE ROUND=0 AND ONLINETIME<4962 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt.CHARBASE WHERE ROUND=0 AND LEVEL<80 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
delete from zt4.CHARBASE WHERE ROUND=0 AND LEVEL<80 and `LASTACTIVEDATE`< ‘2007-11-22 00:00:00′
2.CHARBASE表里CHARID处理重复 重复ID 前加10000
update zt4.CHARBASE set zt4.CHARBASE.CHARID=zt4.CHARBASE.CHARID+10000 where zt4.CHARBASE.CHARID in (select CHARID from zt.CHARBASE)
CARTOONPET表里CARTOONID与主库CARTOONPET重复的处理
update zt4.CARTOONPET set zt4.CARTOONPET.CARTOONID=zt4.CARTOONPET.CARTOONID+10000 where zt4.CARTOONPET.CARTOONID in (SELECT zt4.CARTOONPET.CARTOONID from zt.CARTOONPET)
3.SEPT表里SEPTID 处理重复，重复ID前加10000
update zt4.SEPT,zt.SEPT set zt4.SEPT.SEPTID= zt4.SEPT.SEPTID+10000 where zt4.SEPT.SEPTID in (select SEPTID from zt.SEPT)
4.UNIONMEMBER表里UNIONID处理重复前加10000
update zt4.UNIONMEMBER set zt4.UNIONMEMBER.UNIONID= zt4.UNIONMEMBER.UNIONID+10000 where zt4.UNIONMEMBER.UNIONID in (select UNIONID from zt.UNIONMEMBER)
5.UNIONMEMBER表里SEPTID 与SEPT表里的同步
update zt4.UNIONMEMBER,zt4.SEPT set zt4.UNIONMEMBER.SEPTID=zt4.SEPT.SEPTID WHERE zt4.UNIONMEMBER.NAME=zt4.SEPT.MASTER
6.UNION表里UNIONID 与UNIONMEMBER表里的同步
update zt4.UNION,zt4.UNIONMEMBER set zt4.UNION.UNIONID=zt4.UNIONMEMBER.UNIONID WHERE zt4.UNION.MASTER=zt4.UNIONMEMBER.NAME
7.SEPT表里UNIONID 与UNIONMEMBER表里的同步
update zt4.SEPT,zt4.UNIONMEMBER set zt4.SEPT.UNIONID=zt4.UNIONMEMBER.UNIONID WHERE zt4.SEPT.MASTER=zt4.UNIONMEMBER.NAME
8.CHARBASE表里SEPTID处理与SEPT表一致
update zt4.CHARBASE,zt4.SEPT set zt4.CHARBASE.SEPTID=zt4.SEPT.SEPTID WHERE zt4.CHARBASE.NAME=zt4.SEPT.NAME
9.CHARBASE表里UNIONID处理与UNIONMEMBER的UNIONID表一致
update zt4.CHARBASE,zt4.UNIONMEMBER set zt4.CHARBASE.UNIONID=zt4.UNIONMEMBER.UNIONID WHERE zt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME
10.SEPT表里CHARID与CHARBASE里的CHARID一致
update zt4.CHARBASE,zt4.SEPT set zt4.SEPT.CHARID=zt4.CHARBASE.CHARID WHERE zt4.CHARBASE.NAME=zt4.SEPT.MASTER
CARTOONPET表里MASTERID与CHARBASE里的CHARID一致
update zt4.CARTOONPET,zt4.CHARBASE set zt4.CARTOONPET.MASTERID=zt4.CHARBASE.CHARID where zt4.CHARBASE.NAME=zt4.CARTOONPET.MASTERNAME
12.UNIONMEMBER表里CHARID与CHARBASE里的CHARID一致
update zt4.CHARBASE,zt4.UNIONMEMBER set zt4.UNIONMEMBER.CHARID=zt4.CHARBASE.CHARID WHERE zt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME
13.SCHOOLMEMBER表里CHARID与CHARBASE里的CHARID一致
update zt4.CHARBASE,zt4.SCHOOLMEMBER set zt4.SCHOOLMEMBER.CHARID=zt4.CHARBASE.CHARID WHERE zt4.CHARBASE.NAME=zt4.SCHOOLMEMBER.NAME
14.CHARBASE表里NAME与主库CHARBASE名字重复的处理
update zt4.CHARBASE set name=CONCAT(rtrim(name),'oo') where name in (select name from zt.CHARBASE)
15.SEPT表里NAME与主库SEPT名字重复的处理
update zt4.SEPT set zt4.SEPT.NAME=CONCAT(rtrim(zt4.SEPT.NAME),'oo') where zt4.SEPT.NAME in (select NAME from zt.SEPT)
16.UNION表里NAME与主库UNION名字重复的处理
update zt4.UNION set zt4.UNION.NAME=CONCAT(rtrim(zt4.UNION.NAME),'oo') where zt4.UNION.NAME in (select NAME from zt.UNION)
17.SCHOOLMEMBER表里SERIALID与主库SCHOOLMEMBER名字重复的处理
update zt4.SCHOOLMEMBER set zt4.SCHOOLMEMBER.SERIALID=zt4.SCHOOLMEMBER.SERIALID+10000 where zt4.SCHOOLMEMBER.SERIALID in (select SERIALID from zt.SCHOOLMEMBER)
18.SEPT表里MASTER人物名与CHAREBASE里的人物名同步
update zt4.CHARBASE,zt4.SEPT set zt4.SEPT.MASTER=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.SEPT.CHARID
19.SCHOOLMEMBER表里NAME与CHARBASE人物名同步
update zt4.CHARBASE,zt4.SCHOOLMEMBER set zt4.SCHOOLMEMBER.NAME=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.SCHOOLMEMBER.CHARID
20.UNION表里MASTER人物名与CHAREBASE里的人物名同步
update zt4.CHARBASE,zt4.UNION set zt4.UNION.NAME=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.UNION.CHARID
22.CARTOONPET表里MASTERNAME与CHAREBASE里的人物名同步
update zt4.CHARBASE,zt4.CARTOONPET set zt4.CARTOONPET.MASTERNAME=zt4.CHARBASE.NAME WHERE zt4.CHARBASE.CHARID=zt4.CARTOONPET.MASTERID
21.合并CHARBASE数据库
insert into zt.CHARBASE SELECT FROM zt4.CHARBASE
22.合并SCHOOLMEMBER数据库
insert into zt.SCHOOLMEMBER SELECT FROM zt4.SCHOOLMEMBER
23.合并SEPT数据库
insert into zt.SEPT SELECT FROM zt4.SEPT
24.合并UNIONMEMBER数据库
insert into zt.UNIONMEMBER SELECT FROM zt4.UNIONMEMBER
25.合并UNION
insert into zt.UNION SELECT FROM zt4.UNION
26.合并CARTOONPET
insert into zt.CARTOONPET SELECT FROM zt4.CARTOONPET
26.合并BALANCE
insert into zt.BALANCE SELECT FROM zt4.BALANCE