Some Usefull SQL's for Mangos servers

**Blizardo** · 02-06-2009

delete account with last login more 60 (change your number) days

Code:

DELETE FROM `realmd`.`account` WHERE DATEDIFF(NOW(), `lastlogin`)>60;

delete accounts without characters

Code:

DELETE FROM `realmd`.`account` where `id` not in (select account from `mangos`.`character`);

Search characters with gold more then number in SQL

Code:

select name,SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',1177),' ',-1) 
from mangos.character where SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',1177),' ',-1)>2000000

Search creature with hi gold drop

Code:

select * from creature_template where maxgold>10000 and rank=1

Output creature guids and names on map (Temple of Ahn'Qiraj here). Usefull for check quest npc.

Code:

SELECT 
`creature`.`guid`,  
(select creature_template.name from creature_template  
where creature_template.entry = `creature`.`id`) as name,  
`creature`.`id` 
FROM 
`creature`  
WHERE `map` = '531'

Delete item from player's inventory

Code:

SET @ITEM = 17740;
SET @GUID = 4;
DELETE FROM item_instance WHERE guid IN
(SELECT item FROM character_inventory WHERE item_template=@ITEM AND guid=@GUID);
DELETE FROM character_inventory WHERE item_template=@ITEM AND guid=@GUID;

GM Beer (just for fun)

Code:

DELETE FROM item_template WHERE entry=99999;
INSERT INTO item_template VALUES(99999,0,2,"Beer from GM","Beer from GM","Beer from GM","Beer from GM",7921,6,0,1,2001,301,0,1503,511,30,18,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"0","0",0,"0","0",0,"0","0",0,"0","0",0,"0","0",0,0,0,0,0,0,0,0,0,0,"0",11629,0,-1,0,0,0,6615,0,-1,0,0,0,549,0,-1,0,0,0,19645,0,-1,0,0,0,8212,0,-1,0,0,0,0,"ToJIbKo oT 18 JIET :)",0,0,0,0,0,0,0,0,0,0,0,0,0,0,"internalitemhanler",0);
DELETE FROM page_text WHERE entry=99999;