Hôm nay mu4viet sẽ hướng dẫn các bạn những query thông dụng của admin Mu online. Tuỳ nhu cầu mà admin có thể tự gõ query theo cấu trúc tương tự.
*Lưu ý: Chạy query sẽ trực tiếp thay đổi dữ liệu Data của game. Hãy cẩn thận hoặc backup Database trước khi chạy query!
Query Mu online
– Xem thông tin nhân vật
select * from character where name ='ten nhan vat'
– Tăng giảm 10 Reset và Relife cho nhân vật cho server MU4Viet.net
update character
set ResetCount = ResetCount + 10,Clevel = 400
where name ='ten nhan vat'
update character
set Relifes = Relifes + 10
where name ='ten nhan vat'
– Xóa toàn bộ để lại db trống (Xóa Data để Open game)
Delete AccountCharacter
Delete CashLog
Delete CashShopData
Delete CashShopInventory
Delete CashShopPeriodicItem
Delete character
Delete CustomAttack
Delete CustomJewelBank
Delete CustomGift
Delete CustomQuest
Delete EventLeoTheHelper
Delete EventSantaClaus
Delete ExtWarehouse
Delete Gens_Duprian
Delete Gens_Rank
Delete Gens_Reward
Delete Gens_Varnert
Delete GuildMember
Delete Guild
Delete HelperData
Delete ItemLog
Delete LOG_CREDITOS
Delete LuckyCoin
Delete LuckyItem
Delete Marry
Delete MasterSkillTree
Delete MEMB_STAT
Delete MEMB_INFO
Delete MK_Server
Delete MuCastle_DATA
Delete MuCastle_MONEY_STATISTICS
Delete MuCastle_NPC
Delete MuCastle_REG_SIEGE
Delete MuCastle_SIEGE_GUILDLIST
Delete OptionData
Delete QuestKillCount
Delete QuestWorld
Delete RankingBloodCastle
Delete RankingChaosCastle
Delete RankingDevilSquare
Delete RankingDuel
Delete RankingIllusionTemple
Delete RankingKingGuild
Delete RankingKingPlayer
Delete RankingTvT
Delete T_CGuid
Delete T_FriendList
Delete T_FriendMail
Delete T_FriendMain
Delete T_PetItem_Info
Delete T_WaitFriend
Delete warehouse
Delete WarehouseGuild
Delete WZ_CW_INFO
Delete Log_TienTe
Delete GiftCode
Delete GiftLog
Delete CardPhone
Delete DoanhThu
Delete item_vpoint_changed
Delete ItemLock
Delete Event_TOP_RS
Delete MasterSkillTree
Delete NBB_Award
Delete TopResetScore
Delete TopReset
Delete DauGia_Bid
Delete DauGia_Item
Delete Event_TOP_Card
Delete GiaiTri_LoDe
Delete nbb_toppoint
Delete CardVweb
--Log WEB nếu sử dụng web thuê
Delete from event_doiitem_vweb
Delete from vweb_block
Delete from EVENT_TONGTOP_VWEB
Delete from EVENT_TONGTOP_VWEB_KETQUA
Delete from CardVweb
Delete from Log_ModVweb
Delete from Log_chuyensv
Delete from ShareFB_VWEB
Delete from vweb_warehouse_safe
Delete from vweb_block
Delete from vweb_muathe
Delete from vweb_gift_tichluythe
Delete from vweb_doingoc
Delete from VWEB_Resets_Log
Delete from VWEB_PointEvent_Log
Delete from VWEB_DanhHieuThue_Log
Delete from MEMB_INFO Where memb___id not like 'adm_block'
Update GameServerInfo set Number=0, ItemCount=0, ZenCount=0, AceItemCount=0
– Tăng điểm master cho nhân vật – SERVER EMU
update MasterSkillTree
set MasterLevel = 200,MasterPoint = 200,MasterExperience = 200
where Name = 'tên nhân vật'
– Tăng điểm master cho cả Server – SERVER EMU MU4Viet.net
update MasterSkillTree
set MasterLevel = 200,MasterPoint = 200,MasterExperience = 200
– Chỉnh số điểm cộng cho nhân vật về 0
UPDATE Character
SET LevelUpPoints=0
WHERE LevelUpPoints>0
– Reset số điểm đã cộng và điểm chưa cộng toàn server
UPDATE Character
SET Strength=30,
Dexterity=30,
Vitality=30,
Energy=30,
LevelUpPoint=65000
– Chỉnh tài khoản admin thành tài khoản thường
UPDATE Character
SET CtlCode=0
WHERE CtlCode='32' OR CtlCode='8'
– Mở khóa tài khoản cho tất cả nhân vật
UPDATE Character
SET CtlCode=0
WHERE CtlCode=1
– Chỉnh lại toàn bộ số lần reset về 0
UPDATE Character
SET ResetCount=0
WHERE ResetCount>0
– Xóa thùng đồ
UPDATE warehouse
SET Items=('')
– Xóa thùng đồ cá nhân
UPDATE Character
SET Inventory=('')
– Sửa lỗi hiển thị gamer online trên web MU EMU – MU4Viet.net
UPDATE MEMB_STAT SET ConnectStat=0' WHERE ConnectStat>0
– Sửa lỗi kẹt map
UPDATE Character
SET MapNumber=0, MapPosX=125, MapPosY=125
WHERE MapNumber=5
– Sửa lỗi âm điểm Guild
UPDATE Guild
SET G_Score=0
WHERE G_Score<-1
– Tăng điểm point cho thành viên
UPDATE Character
SET Strength='số point'
WHERE strength<18
UPDATE Character
SET Dexterity='số point'
WHERE Dexterity<16
UPDATE Character
SET Vitality='số point'
WHERE Vitality<16
UPDATE Character
SET Energy='số point'
WHERE Energy<16
– Xóa Pk (Rửa tội)
UPDATE Character
SET PkLevel = 0, PkTime = 0, PkCount = 0
Where PkLevel>2
– Chỉnh tiền cho thành viên
UPDATE Character
SET Money=2000000000
WHERE Money < 0
– Loại bỏ số ID cá nhân
UPDATE MEMB_INFO
SET sno__numb=('')
WHERE sno__numb>1
– Xóa thành viên không có Guild (bang hội chỉ có 1 GM)
alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where EXISTS (
SELECT m.G_Name,count(*) as memb from GuildMember m
where guild.g_name=m.G_name
group by m.G_Name
having count(*) <2
)
– Xóa guildmember với guild không (khi bạn chỉnh sửa sai sau đó làm cho lỗi)
alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guildmember
where NOT EXISTS (
SELECT * from Guild
where guild.g_name=guildmember.G_name
)
– Top 10 Guild (thành viên)
SELECT top 10 g.g_name,count(all m.g_name)as thanhvien from Guild as g join Guildmember as m
on g.g_name like m.g_name
group by g.g_Name
having count(*) > 20
order by count(*) DESC
– Top 5 guild tính theo số lần rs của thành viên trong G
select top 5 g.g_name,sum(c.ResetCount) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
order by reset desc
– Top 5 online
select top 10 c.name,resets,s.onlinehours from character c full join memb_stat s
on c.accountid=s.memb___id
where (c.accountid in ( select top 10 memb___id from memb_stat
order by onlinehours desc ))
and (c.name in ( select top 1 c.name from character c where c.accountid=s.memb___id order by resets desc))
order by onlinehours desc
– Top reset trung bình (mỗi người chơi trong guild). Chỉnh sửa 10 đến giá trị mà bạn muốn. Đó là giá trị của các thành viên của guild
select top 5 g.g_name,sum(c.ResetCount)/count(all g.g_name) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
having count(all g.g_name)>10
order by reset desc
– Đổi tên tài khoản (account)
select * from accountcharacter
where id='9242085'
update accountcharacter
set id='ha1710'
where id='9242085'
select * from accountcharacter
where id='ha1710'
-----------------------
select * from character
where accountid='9242085'
update character
set accountid='ha1710'
where accountid='9242085'
select * from character
where accountid='ha1710'
----------------------
select * from memb_info
where memb___id='9242085'
update memb_info
set memb___id='ha1710'
where memb___id='9242085'
select * from memb_info
where memb___id='ha1710'
----------------------
select * from memb_stat
where memb___id='9242085'
update memb_stat
set memb___id='ha1710'
where memb___id='9242085'
select * from memb_stat
where memb___id='ha1710'
-----------------------
select * from warehouse
where accountid='9242085'
update warehouse
set accountid='ha1710'
where accountid='9242085'
select * from warehouse
where accountid='ha1710'
– Xóa tài khoản không có nhiều kết nối từ 01/06/2010
delete from memb_info
where memb___id in (
select memb___id from memb_stat where disconnecttm<'2010-06-01')
delete from accountcharacter
where id in (
select memb___id from memb_stat where disconnecttm<'2010-06-01')
delete from character
where accountid in (
select memb___id from memb_stat where disconnecttm<'2010-06-01')
delete from warehouse
where accountid in (
select memb___id from memb_stat where disconnecttm<'2010-06-01')
– xóa tài khoản mà không có connect (tài khoản trống) hoặc lâu năm không có connect
delete from memb_info
where memb___id not in (
select memb___id from memb_stat)
delete from accountcharacter
where id not in (
select memb___id from memb_stat)
delete from character
where accountid not in (
select memb___id from memb_stat)
delete from warehouse
where accountid not in (
select memb___id from memb_stat)
– Character không có trong tài khoản
select accountid,character.name from character where accountid in (
select accountid from character
where name not in (select gameid1 from accountcharacter where id=accountid )
and name not in (select gameid2 from accountcharacter where id=accountid )
and name not in (select gameid3 from accountcharacter where id=accountid )
and name not in (select gameid4 from accountcharacter where id=accountid )
and name not in (select gameid5 from accountcharacter where id=accountid )
)
– Back up server
BACKUP LOG muonline WITH NO_LOG
USE [Muonline]
GO
--shrinking
DBCC SHRINKDATABASE(N'Muonline')
Declare @dateBackup Varchar(100)
Set @dateBackup = 'c:\muonline_' +
Convert(varchar, datepart( year , Getdate() )) + '-'+
Convert( varchar , datepart( month , Getdate() ) ) + '-'+
Convert( varchar, datepart( day , Getdate() ) ) + '.bak'
--saving backup with name and date.
BACKUP DATABASE [Muonline] TO DISK = @dateBackup WITH NOFORMAT, NOINIT, NAME = N'Muonline-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
– xóa guild, thành viên guild mà bị sai
alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where g_master not in ( select name from character )
delete from guildmember
where name not in ( select name from character )
delete from guildmember
where G_Name not in ( select G_name from guild )
– Tạo tài khoản
--Tao account
use MuOnline
insert into MEMB_INFO (memb___id,memb__pwd,mail_addr,sno__numb,ctl1_code,bloc_code,memb_name)
VALUES('abc123','matkhau','abc123@yahoo.com',123456789123456789,0,0,'proab')
– Kích 1 tài khoản bi treo
Lưu ý: Bị treo mới chạy query này.
-- Kick 1 tai khoan bi treo acc - hoac 1 tai khoan dang Online
use MuOnline
update MEMB_STAT
set ConnectStat = 0
where memb___id = 'adminpro'
– Up điểm Master cho nhân vật ở Server SCF có tên …
UPDATE Character
SET SCFMasterLevel = 200, SCFMasterPoints = 200
where name='Tên Nhân Vật'
– Tặng điểm master cho tất cả nhân vật ở Server SCF
UPDATE Character
SET SCFMasterLevel = 200, SCFMasterPoints = 200
– DK có combo ko phải làm nv khi lên 220
UPDATE Character set Quest=0xAAFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
WHERE NAME ='tên nv' and class ='18'
– DW có Nova ko phải làm nv khi lên 220
@: nếu là SCF thì thay chữ Quest = SCFCustomQuest
UPDATE Character set Quest=0xEAFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
WHERE NAME ='tên nv' and class ='2'
– Block 1 nhân vật nào đó
UPDATE Character
SET CtlCode=1
WHERE NAME ='tên nv'
– Tìm Đồ Full Trong Server
select * from warehouse
where (charindex (0xbf, items) %16=8)
or (charindex (0xff, items) %16=8)
or (charindex (0x7f, items) %16=8)
or (charindex (0x3f, items) %16=8)
or (charindex (0x9f, items) %16=8)
or (charindex (0x8f, items) %16=8)
or (charindex (0x9e, items) %16=8)
or (charindex (0x9d, items) %16=8)
or (charindex (0xbd, items) %16=8)
or (charindex (0xbc, items) %16=8)
or (charindex (0xba, items) %16=8)
or (charindex (0xbb, items) %16=8)
or (charindex (0xb7, items) %16=8)
or (charindex (0xaf, items) %16=8)
select * from extwarehouse
where (charindex (0xbf, items) %16=8)
or (charindex (0xff, items) %16=8)
or (charindex (0x7f, items) %16=8)
or (charindex (0x3f, items) %16=8)
or (charindex (0x9f, items) %16=8)
or (charindex (0x8f, items) %16=8)
or (charindex (0x9e, items) %16=8)
or (charindex (0x9d, items) %16=8)
or (charindex (0xbd, items) %16=8)
or (charindex (0xbc, items) %16=8)
or (charindex (0xba, items) %16=8)
or (charindex (0xbb, items) %16=8)
or (charindex (0xb7, items) %16=8)
or (charindex (0xaf, items) %16=8)
select * from character
where (charindex (0xbf, inventory) %16=8)
or (charindex (0xff, inventory) %16=8)
or (charindex (0x7f, inventory) %16=8)
or (charindex (0x3f, inventory) %16=8)
or (charindex (0x9f, inventory) %16=8)
or (charindex (0x8f, inventory) %16=8)
or (charindex (0x9e, inventory) %16=8)
or (charindex (0x9d, inventory) %16=8)
or (charindex (0xbd, inventory) %16=8)
or (charindex (0xbc, inventory) %16=8)
or (charindex (0xba, inventory) %16=8)
or (charindex (0xbb, inventory) %16=8)
or (charindex (0xb7, inventory) %16=8)
or (charindex (0xaf, inventory) %16=8)
