Sql Запросы, Которые Должен Знать Каждый Админ

[DJAKS]

Так как базы постоянно обоновляются, есть необходимость каждый раз проверять на наличие таких ошибок, чтобы состояние баз стремилось к идеальному

Когда запросов накопится достаточно, можно будет написать утилиту проверки.

Вот пока что пришло первым в голову... Выкладывайте и свои запросы, которыми вы часто пользуетесь

Спойлер

# вещи на которых можно навариться
SELECT * FROM `item_template` WHERE (`sellprice` * `buycount` > `buyprice`) and entry in (select item from npc_vendor);
SELECT * FROM `item_template` WHERE (`buycount` = 0) AND (`sellprice` > `buyprice`) and entry in (select item from npc_vendor);
# фикс от ZzZzZzZ - все вопросы к нему %) :
UPDATE `item_template` SET `sellprice` = ((`buyprice` /`buycount`) / 2) WHERE (`sellprice` * `buycount` > `buyprice`) and entry in (select item from npc_vendor);
UPDATE `item_template` SET `sellprice` =  (`buyprice` / 2)  WHERE (`buycount` = 0) AND (`sellprice` > `buyprice`) and entry in (select item from npc_vendor);

#лишние записи по игрокам (все "SELECT *" можно смело менять на "DELETE" )
SELECT * FROM `character_action` where guid not in (select guid from `character`);
SELECT * FROM `character_aura` where guid not in (select guid from `character`);
SELECT * FROM `character_gifts` where guid not in (select guid from `character`);
SELECT * FROM `character_homebind` where guid not in (select guid from `character`);
SELECT * FROM `character_inventory` where guid not in (select guid from `character`);
SELECT * FROM `character_kill` where guid not in (select guid from `character`);
SELECT * FROM `character_pet` where owner not in (select guid from `character`);
SELECT * FROM `character_queststatus` where guid not in (select guid from `character`);
SELECT * FROM `character_reputation` where guid not in (select guid from `character`);
SELECT * FROM `character_social` where guid not in (select guid from `character`);
SELECT * FROM `character_spell` where guid not in (select guid from `character`);
SELECT * FROM `character_spell_cooldown` where guid not in (select guid from `character`);
SELECT * FROM `character_stable` where owner not in (select guid from `character`);
SELECT * FROM `character_ticket` where guid not in (select guid from `character`);
SELECT * FROM `character_tutorial` where guid not in (select guid from `character`);
SELECT * FROM `corpse` where `player` not in (select guid from `character`);
SELECT * FROM `character_inventory` where item not in (SELECT guid FROM `item_instance`);
SELECT * FROM `item_instance` where guid not in (SELECT item FROM `character_inventory`) and guid not in (SELECT item_guid FROM `mail`) and guid not in (SELECT itemguid FROM `auctionhouse`) and guid not in (SELECT item_guid FROM `character_gifts`);
SELECT * FROM `character_queststatus` where quest not in (select `entry` from `quest_template`);
SELECT * FROM `character_social` where `friend` not in (select guid from `character`);

# неверные предметы у продавцов ("SELECT *" -> "DELETE" для очистки)
SELECT * FROM `mangos`.`npc_vendor` WHERE `item` not in (SELECT `entry` FROM `item_template`);

# предметы для продажи есть, однако NPC не может их продать (flag не содержит 4)
# фиксить:
# или изменить флаг (если это действительно продавец),
# или удалить записи из npc_vendor (если не продавец)
SELECT * FROM `creature_template` WHERE (`entry` in (SELECT `entry` from `npc_vendor`)) AND (`npcflag` &  4 <> 4);

# несуществующие продавцы
SELECT * FROM `npc_vendor` WHERE `entry` not in (SELECT `entry` from `creature_template`);

# несуществующие тренеры
SELECT * FROM `npc_trainer` WHERE `entry` not in (SELECT `entry` from `creature_template`);

# простая проверка на квесты (вообще их куча как в ядре, так и в моей программе)
SELECT * FROM `mangos`.`quest_template` where minlevel>questlevel

# аукцион
SELECT * FROM `auctionhouse` where `auctioneerguid` not in (select guid from `character`);
SELECT * FROM `auctionhouse` where `itemowner` not in (select guid from `character`);

# мобы с маленькими / неверными жизнями / уровнями / дамагом
SELECT * FROM `mangos`.`creature` where curhealth < 20;
SELECT * FROM `mangos`.`creature_template` where minhealth>maxhealth;
SELECT * FROM `mangos`.`creature_template` where (minhealth< 20 ) or (maxhealth<20);
SELECT * FROM `mangos`.`creature_template` where minlevel>maxlevel;
SELECT * FROM `mangos`.`creature_template` where (minlevel=0) or (maxlevel=0);
SELECT * FROM `mangos`.`creature_template` where mindmg > maxdmg;
SELECT * FROM `mangos`.`creature_template` where (mindmg=0) or (maxdmg=0);

# несуществующие мобы
SELECT * FROM `creature` c where id  not in (select entry from creature_template);

[свернуть]

Команда на удаление акков, на которые давно не заходили
Внимание: 60 - число дней с последнего захода

Спойлер

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

[свернуть]

Команда на удаление акков, на которых нет прсонажей.
Внимание: базы должны называеться mangos и realmd. В случае, если они так не называюются, откоректируте скрипт.

Спойлер

#Выборка:
SELECT * FROM `realmd`.`account` where `id` not in (select account from `mangos`.`character`);
#Удаление:
DELETE FROM `realmd`.`account` where `id` not in (select account from `mangos`.`character`);

[свернуть]

sFiret: Пользуюсь такими запросами к сожалению комментарии потер давно
indomit: будет время напишу комменты

Спойлер

delete from gameobject where gameobject.id not in ( select entry from gameobject_template);
delete from creature where id not in ( select entry from creature_template);
delete from skinning_loot_template where item not in (select entry from item_template);
delete from creature_loot_template where item not in (select entry from item_template);
update creature_template set lootid=0 where lootid not in (select entry from creature_loot_template);
delete from gameobject_loot_template where entry not in (select entry from gameobject_template);
delete from creature_involvedrelation where quest not in (select entry from quest_template);
delete from gameobject_involvedrelation where quest not in (select entry from quest_template);
delete from creature_questrelation where quest not in (select entry from quest_template);
delete from gameobject_questrelation where quest not in (select entry from quest_template);
delete from creature_questrelation where id not in (select entry from creature_template);
delete from gameobject_questrelation where id not in (select entry from gameobject_template);
delete from creature_involvedrelation where id not in (select entry from creature_template);
DELETE FROM character_inventory where item NOT IN (SELECT guid FROM item_instance) AND guid NOT IN (SELECT owner_guid FROM item_instance);
delete from creature_involvedrelation where id not in (select entry from creature_template);
delete from creature_involvedrelation where quest not in (select entry from quest_template);
delete from gameobject_involvedrelation where id not in (select entry from gameobject_template);
delete from gameobject_involvedrelation where quest not in (select entry from quest_template);
delete from creature_questrelation where id not in (select entry from creature_template);
delete from creature_questrelation where quest not in (select entry from quest_template);
delete from gameobject_questrelation where id not in (select entry from gameobject_template);
delete from gameobject_questrelation where quest not in (select entry from quest_template);
update creature_template set npcflag=npcflag& ~4;
update creature_template set npcflag=npcflag|4 where entry in (select distinct(entry) from npc_vendor);
update creature_template set npcflag=npcflag& ~16;
update creature_template set npcflag=npcflag|16 where entry in (select distinct(entry) from npc_trainer);
update creature_template set npcflag=npcflag& ~2;
update creature_template set npcflag=npcflag|2 where entry in (select distinct(id) from creature_questrelation);
update creature_template set npcflag=npcflag|2 where entry in (select distinct(id) from creature_involvedrelation);
delete from creature_loot_template where item not in (select entry from item_template);
delete from creature_loot_template where entry not in (select lootid from creature_template)
delete from pickpocketing_loot_template where item not in (select entry from item_template);
delete from skinning_loot_template where item not in (select entry from item_template);
delete from skinning_loot_template where entry not in (select skinloot from creature_template);
delete from fishing_loot_template where item not in (select entry from item_template);
delete from item_loot_template where entry not in (select entry from item_template);
delete from item_loot_template where item not in (select entry from item_template);
delete from npc_vendor where item not in (select entry from item_template);
delete from npc_vendor where entry not in (select entry from creature_template);
delete from npc_trainer where entry not in (select entry from creature_template);
delete from gameobject where id not in (select entry from gameobject_template);
delete from creature where id not in (select entry from creature_template);
update item_template set buyprice=sellprice*4 where buyprice<sellprice;
delete from npc_vendor where item in (select entry from item_loot_template);

[свернуть]

Выводит игоков, которые используют Death Touch

Спойлер

select `character`.`account` from `character` Where `character`.`guid` in
(SELECT `character_spell`.`guid` FROM `character_spell` WHERE `spell`='5') group by `character`.`account`;

[свернуть]

Запросы от Chili

Спойлер

/*Установка времени респауна в зависимости от лвл и ранка*/
UPDATE creature, creature_template SET creature.spawntimesecs=(creature_template.maxlevel*60)*(creature_template.rank+0
.8) WHERE creature.id=creature_template.entry;

update gameobject set spawntimesecs=1800 where spawntimesecs>9 and spawntimesecs<1000000;

#Чистка базы
delete FROM mangos_realmd.account WHERE `last_login`<20061201000000 and gmlevel=0;
DELETE FROM mangos_realmd.account WHERE `last_login` = '0000-00-00 00:00:00';
DELETE FROM mangos2.character WHERE totaltime<1000 and online=0;
delete FROM mangos2.character WHERE account NOT IN (SELECT id FROM mangos_realmd.account);

delete FROM mangos2.character_action WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_aura WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_bags WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_homebind WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_inventory WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_kill WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_queststatus WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_reputation WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_social WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_spell WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_spell_cooldown WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_ticket WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_tutorial WHERE guid NOT IN (SELECT guid FROM mangos2.character);
DELETE FROM `mangos2`.`character_inventory` WHERE `character_inventory`.`guid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`mail` WHERE `mail`.`receiver` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_charter_sign` WHERE `guild_charter_sign`.`playerguid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_charter` WHERE `guild_charter`.`ownerguid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_charter_sign` WHERE `guild_charter_sign`.`ownerguid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_member` WHERE `guild_member`.`guid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);

#мое ГМское пиво :) суйте куда хотите- я поставил на продажу в аукционеров- очень эффективно высасывать бабло с игроков :)

DELETE FROM item_template WHERE entry=99999;
INSERT INTO item_template VALUES(99999,0,2,"nuBko oT GMa","nuBko oT GMa","nuBko oT GMa","nuBko oT GMa",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;

#армор
UPDATE creature_template SET armor=round((minlevel*22*(1+(minlevel*0.02))*(1+(rank*0.02)))+(200*rank))/2 WHERE armor<round((maxlevel*22*(1+(maxlevel*0.02))*(1+(rank*0.02)))+(200*rank));

#мин левел макс ХП
update creature_template set maxlevel=minlevel+2 where minlevel>maxlevel;
update creature_template set maxhealth=minhealth where minhealth>maxhealth;

-- удаление у продавцов предметов, из которых есть лут.
delete from npc_vendor where item in (select entry from item_loot_template);

#а теперь внимание! мега запрос -позволяет искать игроков у кого голда больше чем цифра в запросе(я таких начинаю жестоко допрашивать:))

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



#поиск креатюр, у каторых дроп голда НЕРЕАЛЬНЫЙ
select * from creature_template where maxgold>10000 and rank=1

#гибкий запрос на поиск итемов, каторые ВАС интересуют у игроков. с сортировкой и указанием от какого количества от чего искать. данный запрос показывает у кого лошадей или подобный вещей больше 1 штуки

select * from character_inventory where concat(guid,':',item_template) in (
select concat(guid,':',item_template) from (
select inven.guid,inven.item_template,count(*) as kol from character_inventory inven where item_template in (select entry from item_template where RequiredSkill=762)
group by 1,2) a
where a.kol>1)

[свернуть]

Формулки

Спойлер

/*Установка времени респауна в зависимости от лвл и ранка*/
UPDATE creature, creature_template SET creature.spawntimesecs=(creature_template.maxlevel*60)*(creature_template.rank+0
.8) WHERE creature.id=creature_template.entry;

update gameobject set spawntimesecs=1800 where spawntimesecs>9 and spawntimesecs<1000000;

#Чистка базы
delete FROM mangos_realmd.account WHERE `last_login`<20061201000000 and gmlevel=0;
DELETE FROM mangos_realmd.account WHERE `last_login` = '0000-00-00 00:00:00';
DELETE FROM mangos2.character WHERE totaltime<1000 and online=0;
delete FROM mangos2.character WHERE account NOT IN (SELECT id FROM mangos_realmd.account);

delete FROM mangos2.character_action WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_aura WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_bags WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_homebind WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_inventory WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_kill WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_queststatus WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_reputation WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_social WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_spell WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_spell_cooldown WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_ticket WHERE guid NOT IN (SELECT guid FROM mangos2.character);
delete FROM mangos2.character_tutorial WHERE guid NOT IN (SELECT guid FROM mangos2.character);
DELETE FROM `mangos2`.`character_inventory` WHERE `character_inventory`.`guid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`mail` WHERE `mail`.`receiver` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_charter_sign` WHERE `guild_charter_sign`.`playerguid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_charter` WHERE `guild_charter`.`ownerguid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_charter_sign` WHERE `guild_charter_sign`.`ownerguid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);
DELETE FROM `mangos2`.`guild_member` WHERE `guild_member`.`guid` NOT IN ( SELECT `guid` FROM `mangos2`.`character`);

#мое ГМское пиво :) суйте куда хотите- я поставил на продажу в аукционеров- очень эффективно высасывать бабло с игроков :)

DELETE FROM item_template WHERE entry=99999;
INSERT INTO item_template VALUES(99999,0,2,"nuBko oT GMa","nuBko oT GMa","nuBko oT GMa","nuBko oT GMa",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;


#защита от навара при перепродаже итемов. ЮЗАТЬ ТОЛЬКО С ПОНИМАНИЕМ ДЕЛА!!! я 1 раз ошибся, када не прописал стоимость для лошадей!

update item_template set buyprice=1 and sellprice=1 where sellprice=0 ;
update item_template set buyprice=sellprice*4*buycount ;
#лошадки по 100
update item_template set buyprice=1000000 where RequiredSkill=762 and RequiredSkillRank=150;
update item_template set buyprice=600000 where RequiredSkill=762 and RequiredSkillRank=75;
update  item_template set bonding=1 where RequiredSkill=762;

#армор
UPDATE creature_template SET armor=round((minlevel*22*(1+(minlevel*0.02))*(1+(rank*0.02)))+(200*rank))/2 WHERE armor<round((maxlevel*22*(1+(maxlevel*0.02))*(1+(rank*0.02)))+(200*rank));

#мин левел макс ХП
update creature_template set maxlevel=minlevel+2 where minlevel>maxlevel;
update creature_template set maxhealth=minhealth where minhealth>maxhealth;

-- удаление у продавцов предметов, из которых есть лут.
delete from npc_vendor where item in (select entry from item_loot_template);

#а теперь внимание! мега запрос -позволяет искать игроков у кого голда больше чем цифра в запросе(я таких начинаю жестоко допрашивать:))

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



#поиск креатюр, у каторых дроп голда НЕРЕАЛЬНЫЙ
select * from creature_template where maxgold>10000 and rank=1

#гибкий запрос на поиск итемов, каторые ВАС интересуют у игроков. с сортировкой и указанием от какого количества от чего искать. данный запрос показывает у кого лошадей или подобный вещей больше 1 штуки

select * from character_inventory where concat(guid,':',item_template) in (
select concat(guid,':',item_template) from (
select inven.guid,inven.item_template,count(*) as kol from character_inventory inven where item_template in (select entry from item_template where RequiredSkill=762)
group by 1,2) a
where a.kol>1)

[свернуть]

запрос выводит ники персонажей из базы mangos у которых last_ip='127.0.0.1' :

Спойлер

SELECT `name` FROM `mangos`.`character` WHERE account IN (SELECT `id`  FROM `realmd`.`account` WHERE `last_ip` = '127.0.0.1')

[свернуть]

Запрос на удаление вещи у определенного персонажа:

Спойлер

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;

[свернуть]

Когда запросов накопится достаточно, можно будет написать утилиту проверки.

Спойлер

# вещи на которых можно навариться
SELECT * FROM `item_template` WHERE (`sellprice` * `buycount` > `buyprice`) and entry in (select item from npc_vendor);
SELECT * FROM `item_template` WHERE (`buycount` = 0) AND (`sellprice` > `buyprice`) and entry in (select item from npc_vendor);
# фикс от ZzZzZzZ - все вопросы к нему %) :
UPDATE `item_template` SET `sellprice` = ((`buyprice` /`buycount`) / 2) WHERE (`sellprice` * `buycount` > `buyprice`) and entry in (select item from npc_vendor);
UPDATE `item_template` SET `sellprice` =  (`buyprice` / 2)  WHERE (`buycount` = 0) AND (`sellprice` > `buyprice`) and entry in (select item from npc_vendor);

#лишние записи по игрокам (все "SELECT *" можно смело менять на "DELETE" )
SELECT * FROM `character_action` where guid not in (select guid from `character`);
SELECT * FROM `character_aura` where guid not in (select guid from `character`);
SELECT * FROM `character_gifts` where guid not in (select guid from `character`);
SELECT * FROM `character_homebind` where guid not in (select guid from `character`);
SELECT * FROM `character_inventory` where guid not in (select guid from `character`);
SELECT * FROM `character_kill` where guid not in (select guid from `character`);
SELECT * FROM `character_pet` where owner not in (select guid from `character`);
SELECT * FROM `character_queststatus` where guid not in (select guid from `character`);
SELECT * FROM `character_reputation` where guid not in (select guid from `character`);
SELECT * FROM `character_social` where guid not in (select guid from `character`);
SELECT * FROM `character_spell` where guid not in (select guid from `character`);
SELECT * FROM `character_spell_cooldown` where guid not in (select guid from `character`);
SELECT * FROM `character_stable` where owner not in (select guid from `character`);
SELECT * FROM `character_ticket` where guid not in (select guid from `character`);
SELECT * FROM `character_tutorial` where guid not in (select guid from `character`);
SELECT * FROM `corpse` where `player` not in (select guid from `character`);
SELECT * FROM `character_inventory` where item not in (SELECT guid FROM `item_instance`);
SELECT * FROM `item_instance` where guid not in (SELECT item FROM `character_inventory`) and guid not in (SELECT item_guid FROM `mail`) and guid not in (SELECT itemguid FROM `auctionhouse`) and guid not in (SELECT item_guid FROM `character_gifts`);
SELECT * FROM `character_queststatus` where quest not in (select `entry` from `quest_template`);
SELECT * FROM `character_social` where `friend` not in (select guid from `character`);

# неверные предметы у продавцов ("SELECT *" -> "DELETE" для очистки)
SELECT * FROM `mangos`.`npc_vendor` WHERE `item` not in (SELECT `entry` FROM `item_template`);

# предметы для продажи есть, однако NPC не может их продать (flag не содержит 4)
# фиксить:
# или изменить флаг (если это действительно продавец),
# или удалить записи из npc_vendor (если не продавец)
SELECT * FROM `creature_template` WHERE (`entry` in (SELECT `entry` from `npc_vendor`)) AND (`npcflag` &  4 <> 4);

# несуществующие продавцы
SELECT * FROM `npc_vendor` WHERE `entry` not in (SELECT `entry` from `creature_template`);

# несуществующие тренеры
SELECT * FROM `npc_trainer` WHERE `entry` not in (SELECT `entry` from `creature_template`);

# простая проверка на квесты (вообще их куча как в ядре, так и в моей программе)
SELECT * FROM `mangos`.`quest_template` where minlevel>questlevel

# аукцион
SELECT * FROM `auctionhouse` where `auctioneerguid` not in (select guid from `character`);
SELECT * FROM `auctionhouse` where `itemowner` not in (select guid from `character`);

# мобы с маленькими / неверными жизнями / уровнями / дамагом
SELECT * FROM `mangos`.`creature` where curhealth < 20;
SELECT * FROM `mangos`.`creature_template` where minhealth>maxhealth;
SELECT * FROM `mangos`.`creature_template` where (minhealth< 20 ) or (maxhealth<20);
SELECT * FROM `mangos`.`creature_template` where minlevel>maxlevel;
SELECT * FROM `mangos`.`creature_template` where (minlevel=0) or (maxlevel=0);
SELECT * FROM `mangos`.`creature_template` where mindmg > maxdmg;
SELECT * FROM `mangos`.`creature_template` where (mindmg=0) or (maxdmg=0);

# несуществующие мобы
SELECT * FROM `creature` c where id  not in (select entry from creature_template);

[свернуть]

Вот этот запрос обнулит АП (arena points) у всех игроков. Для сервера на 3.1.3

Спойлер

UPDATE `characters` SET `data`=CONCAT(CAST(SUBSTRING_INDEX(`data`,' ', 1247) AS CHAR), ' ', 0, ' ',
CAST(SUBSTRING_INDEX(`data`, ' ', -(1294 - 1247))AS CHAR));

[свернуть]

Скрипт для фан серверов

Спойлер

DELETE FROM `creature_questrelation` WHERE `quest` = 987655;
DELETE FROM `gameobject_questrelation` WHERE `quest` = 987655;
UPDATE `item_template` SET `StartQuest`=0 WHERE `StartQuest` = 987655;
INSERT INTO `creature_questrelation` (`id`, `quest`) VALUES (1001942, 987655);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry` = 1001942;
DELETE FROM `creature_involvedrelation` WHERE `quest` = 987655;
DELETE FROM `gameobject_involvedrelation` WHERE `quest` = 987655;
INSERT INTO `creature_involvedrelation` (`id`, `quest`) VALUES (1001942, 987655);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry`=1001942;
REPLACE INTO `quest_template` (`entry`, `ZoneOrSort`, `SkillOrClass`, `MinLevel`, `QuestLevel`, `Type`, `RequiredRaces`, `RequiredSkillValue`, `RepObjectiveFaction`, `RepObjectiveValue`, `RequiredMinRepFaction`, `RequiredMinRepValue`, `RequiredMaxRepFaction`, `RequiredMaxRepValue`, `SuggestedPlayers`, `LimitTime`, `QuestFlags`, `SpecialFlags`, `CharTitleId`, `PrevQuestId`, `NextQuestId`, `ExclusiveGroup`, `NextQuestInChain`, `SrcItemId`, `SrcItemCount`, `SrcSpell`, `Title`, `Details`, `Objectives`, `OfferRewardText`, `RequestItemsText`, `EndText`, `ObjectiveText1`, `ObjectiveText2`, `ObjectiveText3`, `ObjectiveText4`, `ReqItemId1`, `ReqItemId2`, `ReqItemId3`, `ReqItemId4`, `ReqItemCount1`, `ReqItemCount2`, `ReqItemCount3`, `ReqItemCount4`, `ReqSourceCount1`, `ReqSourceCount2`, `ReqSourceCount3`, `ReqSourceCount4`, `ReqSourceId1`, `ReqSourceId2`, `ReqSourceId3`, `ReqSourceId4`, `ReqSourceRef1`, `ReqSourceRef2`, `ReqSourceRef3`, `ReqSourceRef4`, `ReqCreatureOrGOId1`, `ReqCreatureOrGOId2`, `ReqCreatureOrGOId3`, `ReqCreatureOrGOId4`, `ReqCreatureOrGOCount1`, `ReqCreatureOrGOCount2`, `ReqCreatureOrGOCount3`, `ReqCreatureOrGOCount4`, `ReqSpellCast1`, `ReqSpellCast2`, `ReqSpellCast3`, `ReqSpellCast4`, `RewChoiceItemId1`, `RewChoiceItemId2`, `RewChoiceItemId3`, `RewChoiceItemId4`, `RewChoiceItemId5`, `RewChoiceItemId6`, `RewChoiceItemCount1`, `RewChoiceItemCount2`, `RewChoiceItemCount3`, `RewChoiceItemCount4`, `RewChoiceItemCount5`, `RewChoiceItemCount6`, `RewItemId1`, `RewItemId2`, `RewItemId3`, `RewItemId4`, `RewItemCount1`, `RewItemCount2`, `RewItemCount3`, `RewItemCount4`, `RewRepFaction1`, `RewRepFaction2`, `RewRepFaction3`, `RewRepFaction4`, `RewRepFaction5`, `RewRepValue1`, `RewRepValue2`, `RewRepValue3`, `RewRepValue4`, `RewRepValue5`, `RewOrReqMoney`, `RewMoneyMaxLevel`, `RewSpell`, `RewSpellCast`, `RewMailTemplateId`, `RewMailDelaySecs`, `PointMapId`, `PointX`, `PointY`, `PointOpt`, `DetailsEmote1`, `DetailsEmote2`, `DetailsEmote3`, `DetailsEmote4`, `IncompleteEmote`, `CompleteEmote`, `OfferRewardEmote1`, `OfferRewardEmote2`, `OfferRewardEmote3`, `OfferRewardEmote4`, `StartScript`, `CompleteScript`) VALUES (987655, 0, 0, 1, 70, 0, 690, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'Level and Money [H]', '$N Ты получеш за это задание 200 тысяч голд и 70 лвл', 'Здай мне квест', 'Ты получил задание, $N здай его', 'Отдай мне квест', '', '', '', '', '', 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, 17031, 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, 2147483647, 4294967295, 0, 4996, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

DELETE FROM `creature_questrelation` WHERE `quest` = 987654;
DELETE FROM `gameobject_questrelation` WHERE `quest` = 987654;
UPDATE `item_template` SET `StartQuest`=0 WHERE `StartQuest` = 987654;
INSERT INTO `creature_questrelation` (`id`, `quest`) VALUES (1001942, 987654);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry` = 1001942;
DELETE FROM `creature_involvedrelation` WHERE `quest` = 987654;
DELETE FROM `gameobject_involvedrelation` WHERE `quest` = 987654;
INSERT INTO `creature_involvedrelation` (`id`, `quest`) VALUES (1001942, 987654);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry`=1001942;
REPLACE INTO `quest_template` (`entry`, `ZoneOrSort`, `SkillOrClass`, `MinLevel`, `QuestLevel`, `Type`, `RequiredRaces`, `RequiredSkillValue`, `RepObjectiveFaction`, `RepObjectiveValue`, `RequiredMinRepFaction`, `RequiredMinRepValue`, `RequiredMaxRepFaction`, `RequiredMaxRepValue`, `SuggestedPlayers`, `LimitTime`, `QuestFlags`, `SpecialFlags`, `CharTitleId`, `PrevQuestId`, `NextQuestId`, `ExclusiveGroup`, `NextQuestInChain`, `SrcItemId`, `SrcItemCount`, `SrcSpell`, `Title`, `Details`, `Objectives`, `OfferRewardText`, `RequestItemsText`, `EndText`, `ObjectiveText1`, `ObjectiveText2`, `ObjectiveText3`, `ObjectiveText4`, `ReqItemId1`, `ReqItemId2`, `ReqItemId3`, `ReqItemId4`, `ReqItemCount1`, `ReqItemCount2`, `ReqItemCount3`, `ReqItemCount4`, `ReqSourceCount1`, `ReqSourceCount2`, `ReqSourceCount3`, `ReqSourceCount4`, `ReqSourceId1`, `ReqSourceId2`, `ReqSourceId3`, `ReqSourceId4`, `ReqSourceRef1`, `ReqSourceRef2`, `ReqSourceRef3`, `ReqSourceRef4`, `ReqCreatureOrGOId1`, `ReqCreatureOrGOId2`, `ReqCreatureOrGOId3`, `ReqCreatureOrGOId4`, `ReqCreatureOrGOCount1`, `ReqCreatureOrGOCount2`, `ReqCreatureOrGOCount3`, `ReqCreatureOrGOCount4`, `ReqSpellCast1`, `ReqSpellCast2`, `ReqSpellCast3`, `ReqSpellCast4`, `RewChoiceItemId1`, `RewChoiceItemId2`, `RewChoiceItemId3`, `RewChoiceItemId4`, `RewChoiceItemId5`, `RewChoiceItemId6`, `RewChoiceItemCount1`, `RewChoiceItemCount2`, `RewChoiceItemCount3`, `RewChoiceItemCount4`, `RewChoiceItemCount5`, `RewChoiceItemCount6`, `RewItemId1`, `RewItemId2`, `RewItemId3`, `RewItemId4`, `RewItemCount1`, `RewItemCount2`, `RewItemCount3`, `RewItemCount4`, `RewRepFaction1`, `RewRepFaction2`, `RewRepFaction3`, `RewRepFaction4`, `RewRepFaction5`, `RewRepValue1`, `RewRepValue2`, `RewRepValue3`, `RewRepValue4`, `RewRepValue5`, `RewOrReqMoney`, `RewMoneyMaxLevel`, `RewSpell`, `RewSpellCast`, `RewMailTemplateId`, `RewMailDelaySecs`, `PointMapId`, `PointX`, `PointY`, `PointOpt`, `DetailsEmote1`, `DetailsEmote2`, `DetailsEmote3`, `DetailsEmote4`, `IncompleteEmote`, `CompleteEmote`, `OfferRewardEmote1`, `OfferRewardEmote2`, `OfferRewardEmote3`, `OfferRewardEmote4`, `StartScript`, `CompleteScript`) VALUES (987654, 0, 0, 1, 70, 0, 1101, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'Level and Money [A]', '$N Ты получеш за это задание 200 тысяч голд и 70 лвл', 'Здай мне квест', 'Ты получил задание, $N здай его', 'Отдай мне квест', '', '', '', '', '', 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, 17031, 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, 2147483647, 4294967295, 0, 42710, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);  

[свернуть]

для тех у каво нет нпц

Спойлер

REPLACE INTO `creature_template` (`entry`, `heroic_entry`, `modelid_A`, `modelid_A2`, `modelid_H`, `modelid_H2`, `name`, `subname`, `IconName`, `minlevel`, `maxlevel`, `minhealth`, `maxhealth`, `minmana`, `maxmana`, `armor`, `faction_A`, `faction_H`, `npcflag`, `speed`, `scale`, `rank`, `mindmg`, `maxdmg`, `dmgschool`, `attackpower`, `baseattacktime`, `rangeattacktime`, `flags`, `dynamicflags`, `family`, `trainer_type`, `trainer_spell`, `class`, `race`, `minrangedmg`, `maxrangedmg`, `rangedattackpower`, `type`, `flag1`, `lootid`, `pickpocketloot`, `skinloot`, `resistance1`, `resistance2`, `resistance3`, `resistance4`, `resistance5`, `resistance6`, `spell1`, `spell2`, `spell3`, `spell4`, `PetSpellDataId`, `mingold`, `maxgold`, `AIName`, `MovementType`, `InhabitType`, `RacialLeader`, `RegenHealth`, `equipment_id`, `mechanic_immune_mask`, `flags_extra`, `ScriptName`) VALUES (1001942, 0, 19338, 19338, 19338, 19338, 'МАНИ И ЛВЛЫ', '', '', 70, 80, 5000000, 5000000, 300000, 300000, 0, 36, 36, 6, 1, 1, 3, 263, 513, 5, 2712, 1377, 1618, 0, 0, 0, 0, 0, 0, 0, 1, 3, 100, 3, 64, 18341, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2700, 3700, '', 0, 3, 0, 1, 0, 0, 0, '');  

[свернуть]

Собрал скрипты: indomit