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–databaseszt>/data/ok1.sql
cd/usr/local/mysql/bin/
mysql-uroot-pueoadirzt</backdata/kb.sql
mysqldump-uroot-pueoadir–databaseszt>/backdata/back1210.sql
su-mysql-c“mysqld_safe–skip-name-resolve–open-files-limit=4096&”
cd/usr/local/mysql/bin/
mysql-uroot-pueoadirzt4</backdata/new.sql
mysql-uroot-pueoadirzt</data/ok.sql
UE处理下负区导入的表
mysqlFLServer</data/FLServer.sql
mysqlGMTool</data/GMTool.sql
mysqlLoginServer</data/LoginServer.sql
mysqlroleChangeServer</data/roleChangeServer.sql
mysqlroleregServer</data/roleregServer.sql
mysqlunify00</data/unify00.sql
mysqlFLServer</data/FLServer.sql
mysqlGMTool</data/GMTool.sql
mysqlLoginServer</data/LoginServer.sql
mysqlroleChangeServer</data/roleChangeServer.sql
mysqlroleregServer</data/roleregServer.sql
mysqlunify00</data/unify00.sql
mysqlFLServer</data/FLServer.sql
mysqlzt</data/zt501.sql
mysql-uroot-pueoadirzt</backdata/kb.sql
查询
select*fromzt4.CHARBASEwherenamein(selectnamefromzt.zHARBASE)
select*fromCHARBASElimit100
selectUNIONIDfromCHARBASElimit100
更新
updatezt4.CHARBASEsetname=CONCAT('一区',rtrim(name))wherenamein(selectnamefromzt.CHARBASE)‘处理重名
updateCHARBASEsetname=CONCAT(rtrim(name),'1q')
updateCHARBASEsetCHARID=CHARID+10
插入
insertintozt.CHARBASESELECT*FROMzt4.CHARBASE
insertintogame.user(name,pass)selectname,passfromgame2.user2
导入
mysql-uroot-pueoadirzt</backdata/zt2q.sql
mysql-uroot-pueoadirzt</backdata/1q.sql
删除
deletefromCHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-1900:00:00′
deletefromCHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-1900:00:00′
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
deletefromzt.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-12-1000:00:00′
deletefromzt.CHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
2.去除GMCHARID编号重复
SELECTmax(CHARID)FROM`CHARBASE`
updatezt4.CHARBASEsetCHARID=CHARID+10000whereCHARID<100LIMIT50
updatezt4.CHARBASEsetCHARID=CHARID+20000whereCHARIDin(selectCHARIDfromzt.CHARBASE)
3.去除人物重名
updatezt4.CHARBASEsetname=CONCAT(rtrim(name),'o')wherenamein(selectnamefromzt.CHARBASE)
4.插入表2数据到表1
insertintozt.CHARBASESELECT*FROMzt4.CHARBASE
________________________________________________
deletefromzt4.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt4.CHARBASEWHEREROUND=0ANDLEVEL<96and`LASTACTIVEDATE`<‘2007-12-1000:00:00′
SELECTmax(CHARID)FROMzt4.CHARBASE
select*fromzt4.CHARBASElimit100
updatezt4.CHARBASEsetCHARID=CHARID+10000whereCHARIDin(selectCHARIDfromzt.CHARBASE)
updatezt4.CHARBASEsetname=CONCAT(rtrim(name),'oo')wherenamein(selectnamefromzt.CHARBASE)
insertintozt.CHARBASESELECT*FROMzt4.CHARBASE
___________________________________________________________________________________
1.删除玩家离线时间超过五天且在线时间小于1小时的玩家！
deletefromzt.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt4.CHARBASEWHEREROUND=0ANDONLINETIME<4962and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt.CHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
deletefromzt4.CHARBASEWHEREROUND=0ANDLEVEL<80and`LASTACTIVEDATE`<‘2007-11-2200:00:00′
2.CHARBASE表里CHARID处理重复重复ID前加10000
updatezt4.CHARBASEsetzt4.CHARBASE.CHARID=zt4.CHARBASE.CHARID+10000wherezt4.CHARBASE.CHARIDin(selectCHARIDfromzt.CHARBASE)
CARTOONPET表里CARTOONID与主库CARTOONPET重复的处理
updatezt4.CARTOONPETsetzt4.CARTOONPET.CARTOONID=zt4.CARTOONPET.CARTOONID+10000wherezt4.CARTOONPET.CARTOONIDin(SELECTzt4.CARTOONPET.CARTOONIDfromzt.CARTOONPET)
3.SEPT表里SEPTID处理重复，重复ID前加10000
updatezt4.SEPT,zt.SEPTsetzt4.SEPT.SEPTID=zt4.SEPT.SEPTID+10000wherezt4.SEPT.SEPTIDin(selectSEPTIDfromzt.SEPT)
4.UNIONMEMBER表里UNIONID处理重复前加10000
updatezt4.UNIONMEMBERsetzt4.UNIONMEMBER.UNIONID=zt4.UNIONMEMBER.UNIONID+10000wherezt4.UNIONMEMBER.UNIONIDin(selectUNIONIDfromzt.UNIONMEMBER)
5.UNIONMEMBER表里SEPTID与SEPT表里的同步
updatezt4.UNIONMEMBER,zt4.SEPTsetzt4.UNIONMEMBER.SEPTID=zt4.SEPT.SEPTIDWHEREzt4.UNIONMEMBER.NAME=zt4.SEPT.MASTER
6.UNION表里UNIONID与UNIONMEMBER表里的同步
updatezt4.UNION,zt4.UNIONMEMBERsetzt4.UNION.UNIONID=zt4.UNIONMEMBER.UNIONIDWHEREzt4.UNION.MASTER=zt4.UNIONMEMBER.NAME
7.SEPT表里UNIONID与UNIONMEMBER表里的同步
updatezt4.SEPT,zt4.UNIONMEMBERsetzt4.SEPT.UNIONID=zt4.UNIONMEMBER.UNIONIDWHEREzt4.SEPT.MASTER=zt4.UNIONMEMBER.NAME
8.CHARBASE表里SEPTID处理与SEPT表一致
updatezt4.CHARBASE,zt4.SEPTsetzt4.CHARBASE.SEPTID=zt4.SEPT.SEPTIDWHEREzt4.CHARBASE.NAME=zt4.SEPT.NAME
9.CHARBASE表里UNIONID处理与UNIONMEMBER的UNIONID表一致
updatezt4.CHARBASE,zt4.UNIONMEMBERsetzt4.CHARBASE.UNIONID=zt4.UNIONMEMBER.UNIONIDWHEREzt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME
10.SEPT表里CHARID与CHARBASE里的CHARID一致
updatezt4.CHARBASE,zt4.SEPTsetzt4.SEPT.CHARID=zt4.CHARBASE.CHARIDWHEREzt4.CHARBASE.NAME=zt4.SEPT.MASTER
CARTOONPET表里MASTERID与CHARBASE里的CHARID一致
updatezt4.CARTOONPET,zt4.CHARBASEsetzt4.CARTOONPET.MASTERID=zt4.CHARBASE.CHARIDwherezt4.CHARBASE.NAME=zt4.CARTOONPET.MASTERNAME
12.UNIONMEMBER表里CHARID与CHARBASE里的CHARID一致
updatezt4.CHARBASE,zt4.UNIONMEMBERsetzt4.UNIONMEMBER.CHARID=zt4.CHARBASE.CHARIDWHEREzt4.CHARBASE.NAME=zt4.UNIONMEMBER.NAME
13.SCHOOLMEMBER表里CHARID与CHARBASE里的CHARID一致
updatezt4.CHARBASE,zt4.SCHOOLMEMBERsetzt4.SCHOOLMEMBER.CHARID=zt4.CHARBASE.CHARIDWHEREzt4.CHARBASE.NAME=zt4.SCHOOLMEMBER.NAME
14.CHARBASE表里NAME与主库CHARBASE名字重复的处理
updatezt4.CHARBASEsetname=CONCAT(rtrim(name),'oo')wherenamein(selectnamefromzt.CHARBASE)
15.SEPT表里NAME与主库SEPT名字重复的处理
updatezt4.SEPTsetzt4.SEPT.NAME=CONCAT(rtrim(zt4.SEPT.NAME),'oo')wherezt4.SEPT.NAMEin(selectNAMEfromzt.SEPT)
16.UNION表里NAME与主库UNION名字重复的处理
updatezt4.UNIONsetzt4.UNION.NAME=CONCAT(rtrim(zt4.UNION.NAME),'oo')wherezt4.UNION.NAMEin(selectNAMEfromzt.UNION)
17.SCHOOLMEMBER表里SERIALID与主库SCHOOLMEMBER名字重复的处理
updatezt4.SCHOOLMEMBERsetzt4.SCHOOLMEMBER.SERIALID=zt4.SCHOOLMEMBER.SERIALID+10000wherezt4.SCHOOLMEMBER.SERIALIDin(selectSERIALIDfromzt.SCHOOLMEMBER)
18.SEPT表里MASTER人物名与CHAREBASE里的人物名同步
updatezt4.CHARBASE,zt4.SEPTsetzt4.SEPT.MASTER=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.SEPT.CHARID
19.SCHOOLMEMBER表里NAME与CHARBASE人物名同步
updatezt4.CHARBASE,zt4.SCHOOLMEMBERsetzt4.SCHOOLMEMBER.NAME=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.SCHOOLMEMBER.CHARID
20.UNION表里MASTER人物名与CHAREBASE里的人物名同步
updatezt4.CHARBASE,zt4.UNIONsetzt4.UNION.NAME=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.UNION.CHARID
22.CARTOONPET表里MASTERNAME与CHAREBASE里的人物名同步
updatezt4.CHARBASE,zt4.CARTOONPETsetzt4.CARTOONPET.MASTERNAME=zt4.CHARBASE.NAMEWHEREzt4.CHARBASE.CHARID=zt4.CARTOONPET.MASTERID
21.合并CHARBASE数据库
insertintozt.CHARBASESELECT*FROMzt4.CHARBASE
22.合并SCHOOLMEMBER数据库
insertintozt.SCHOOLMEMBERSELECT*FROMzt4.SCHOOLMEMBER
23.合并SEPT数据库
insertintozt.SEPTSELECT*FROMzt4.SEPT
24.合并UNIONMEMBER数据库
insertintozt.UNIONMEMBERSELECT*FROMzt4.UNIONMEMBER
25.合并UNION
insertintozt.UNIONSELECT*FROMzt4.UNION
26.合并CARTOONPET
insertintozt.CARTOONPETSELECT*FROMzt4.CARTOONPET
26.合并BALANCE
insertintozt.BALANCESELECT*FROMzt4.BALANCE
【以前架征途时的合区的SQL语句代码备份】相关文章：
★ 在PostgreSQL的基础上创建一个MongoDB的副本的教程
★ 海量数据库的查询优化及分页算法方案 2 之 改良SQL语句