[SQL]3апросы которые должен знать каждый админ. - Форум

Страница 1 из 2 1 2 »
Модератор форума: staford11

[SQL]3апросы которые должен знать каждый админ. (Добавляем своё, я вставляю в первый потс!)

[SQL]3апросы которые должен знать каждый админ.

zyxel8

Сержант

Репутация: 263

Делимся SQL запросами через навикат
add Pro[100]Hens
[cut]SQL Code add Pro[100]Hens
Снизить дамаг у всех в столько то раз:

Code
update `creature_proto` set `attacktime`=`attacktime`*1,`mindamage`=`mindamage`/1.2,`maxdamage`=`maxdamage`/2,`rangedattacktime`=`rangedattacktime`*1,`rangedmindamage`=`rangedmindamage`/1.2,`rangedmaxdamage`=`rangedmaxdamage`/2

Снизить hp у всех в столько то раз:

Code
update `creature_proto` set
`minhealth` =`minhealth`/2,`maxhealth`=`maxhealth`/2

удалить аккаунты не заходившие в игру с определённого времени(выделено красным цветом)

Quote
DELETE FROM `logon`.`accounts` WHERE `lastlogin` < '2007-04-15 00:00:00';

На арене кладбище

Code
UPDATE `graveyards` SET `position_x` = '-13273.3', `position_y` = '61.8193', `position_z` = '17.8711' WHERE `id` = 37 LIMIT 1

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

Code
#Выборка
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`);

Запрос который показывает количество денег у персонажа

Code
SELECT `name`, SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1177),' ',-1) as `money`
FROM `character` order by 'name'

Удаление объектов/мобов.
#объекты

Code
select * from gameobject where gameobject.id not in ( select entry from gameobject_template);
delete from gameobject where gameobject.id not in ( select entry from gameobject_template);

#мобы

Code
select * from creature where id not in ( select entry from creature_template);
delete from creature where id not in ( select entry from creature_template);

Удаление вещи из инвенторя всех чаров:

Code
DELETE FROM `character_inventory` WHERE `item_template` IN ('entry1','entry2',...);

Запрос удаляет вещь у всех торговцов, запрос по id (entry) вещи:

Quote
delete from npc_vendor where item ='id_вещи'

или

Quote
delete from npc_vendor where item in (select entry from item_template where name ='точное_имя_вещи')

запрос на удаление вещей у чаров, которых нету в item_template :

Code
DELETE FROM `characters`.`character_inventory` where `item_template` not in (SELECT `entry` FROM `mangos`.`item_template`);

сделать вайп денег

Code
update character set SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',1177),' ',-1)=0 where SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',1177),' ',-1)>1

вещи на которых можно навариться

Code
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);

Сделано в виде готового фикса
удаляем старые данные о мобе и луте

Code
DELETE FROM creature_template WHERE entry=30000; # delete mob if exist
DELETE FROM creature_loot_template WHERE entry=30000; # delete loot if exist

создаем нового моба

Code
INSERT INTO creature_template VALUES ('30000','10286','10289','Cursed Scarlet Mage','','55','57','4020','4304','5155','5555','200','67','0','1.25','0','104','148','100','1420','1562','0','0','0','1','0','2','0','0','0'             ,'0','71.2272','97.9374','100','2.21','7','0','524288','21251','0','0','285346306','0','0','2','0','0','30000','0','0','56','21' , ' 2 1 ' , ' 0 ' , '0  ','50','18223','22937','18278','2602','151','1305','','1','generic_creature'); # creating new mob

Добавляем ему лут

Code
INSERT INTO creature_loot_template VALUES ('30000','31241','55','0','1','1','1'); # some loot
INSERT INTO creature_loot_template VALUES ('30000','31240','95','0','1','1','1');

Забыли про вещи которые должны падать?

Code
DELETE FROM item_template WHERE entry IN (31240,31241);
INSERT INTO item_template VALUES ('31241','2','10','Bloodcursed War Staff','Bloodcursed War Staff','Bloodcursed War Staff','Bloodcursed War Staff','20309','3','0','1','519622','111924','17','-1','-1','51','50','0','0','0','0','0','0','0','0','1','0','4','7','3','9','6','13','1','             -1000','0','0','0','0','0','0','0','0','0','0','0','0','59','89','0','5','21','5','0','0','0','0','0','0','0','0','0','200','0', ' 1 5 ' , ' 0 ' , ' -1  5','15','0','2100','0','0','16079','2','0','0','0','0','1112','2','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0           ','0  ','0','0','0','1','This staff is cursed long time ago by nameless insaned mage, using it in evil experiments of fire life.','0','0','0','0','0','2','2','0','0','0','85','0','0','0','internalitemhandler','0');
INSERT INTO item_template VALUES ('31240','2','10','Cursed Magician Staff','Cursed Magician Staff','Cursed Magician Staff','Cursed Magician Staff','28578','2','0','1','25295','5059','17','-1','-1','29','24','0','0','0','0','0','0','0','0','1','0','3','-25','4','-25','5','25','7',             '5','0','0','0','0','0','0','0','0','0','0','0','0','47','71','6','15','35','0','0','0','0','0','0','0','0','0','0','0','0','0', ' 0 ' , ' 0 ' , ' 0 ',  '0','3000','0','0','20883','2','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','2','','0','0','0','0','0','2','2','5221','0','0','85','0','0','0','internalitemhandler','0');

Добавляем самого моба на карту (в игре достаточно посмотреть .gps чтобы знать куда его добавить)

Code
DELETE FROM creature WHERE GUID=61504; # delete&add
INSERT INTO `creature` VALUES ('61504', '', '451','16303.50','-16173.50','40.44', '2.661', '370', '0', '0', '16303.50','-16173.50', '2.661', '2171', '0', '0', '1', '0', '');

моб попадет точно на Designers isle
если движок скриптов отличается - моб кастовать не будет.
Это не фикс а просто образец как его можно сделать
важно помнить что фиксы обычно от ревизии к ревизии надо подправлять, иначе SQL не найдет нужного ему поля и скажет вместо добавления.
Добавлено - в зависимости от вашей ревизии может изменится число или расположение колонок в таблице, не забывайте с ней сверятся.
Проще всего создать новый предмет на основе существующего, просто заменяя параметры на нужные вам, так вы допустите меньше ошибок.
Не все скл запросы смогут подойти.
© mangos.ru
[/cut]

add Sasha_12
[cut]SQL Code add Sasha_12
Тренер на все оружия:

Code
REPLACE INTO `npc_trainer` (entry, spell, spellcost, reqskill, reqskillvalue, reqlevel) VALUES
(11867, 196, 1000, 0, 0, 0),
(11867, 197, 1000, 0, 0, 0),
(11867, 198, 1000, 0, 0, 0),
(11867, 199, 1000, 0, 0, 0),
(11867, 200, 10000, 0, 0, 0),
(11867, 201, 1000, 0, 0, 0),
(11867, 202, 1000, 0, 0, 0),
(11867, 227, 1000, 0, 0, 0),
(11867, 264, 1000, 0, 0, 0),
(11867, 266, 1000, 0, 0, 0),
(11867, 1180, 1000, 0, 0, 0),
(11867, 2567, 1000, 0, 0, 0),
(11867, 5011, 1000, 0, 0, 0),
(11867, 15590, 1000, 0, 0, 0);

Старт в столицах:

Code
REPLACE INTO `playercreateinfo`(`race`,`class`,`map`,`zone`,`position_x`,`position_y`,`position_z`) values
(1,1,0,1519,-8927.46,542.175,94.2933),
(1,2,0,1519,-8927.46,542.175,94.2933),
(1,4,0,1519,-8927.46, 542 .17 5,9 4.2 933 ),
(  1, 5,0,1519,-8927. 46,542.175,94.2933),
(1,8,0,1519,-8927.46,542.175,94.2933),
(1,9,0,1519,-8927.46,542.175,94.2933),
(2,1,1,1 637 ,14 34. 25, -43 98. 33, 25. 4628),
(2,3,1,1637,1434.25,-4398.33,25.4628),
(2,4,1,1637,1434.25,-4398.33,25.4628),
(2,7,1,1637,1434.25,- 439 8.3 3,2 5.4 628 ),
(  2, 9,1,1637,1434.2 5,-4398.33,25.4628),
(3,1,0,1519,-8927.46,542.175,94.2933),
(3,2,0,1519,-8927.46,542.175,94.2933),
(3,3,0,1 519 ,-8 927 .46 ,54 2.1 75, 94. 2933),
(3,4,0,1519,-8927.46,542.175,94.2933),
(3,5,0,1519,-8927.46,542.175,94.2933),
(4,1,0,1519,-8927.46, 542 .17 5,9 4.2 933 ),
(  4, 3,0,1519,-8927. 46,542.175,94.2933),
(4,4,0,1519,-8927.46,542.175,94.2933),
(4,5,0,1519,-8927.46,542.175,94.2933),
(4,11,0, 151 9,- 892 7.4 6,5 42. 175 ,94 .2933) ,
(5,1,1,1637,1434.25,-4398.33,25.4628),
(5,4,1,1637,1434.25,-4398.33,25.4628),
(5,5,1,1637,1434.25,-4398.3 3,2 5.4 628 ),
(   5,8, 1,16 37,1434. 25,-4398.33,25.4628),
(5,9,1,1637,1434.25,-4398.33,25.4628),
(6,1,1,1637,1434.25,-4398.33,25.4628),
(6,3,1, 163 7,1 434 .25 ,-4 398 .33 ,25 .4628) ,
(6,7,1,1637,1434.25,-4398.33,25.4628),
(6,11,1,1637,1434.25,-4398.33,25.4628),
(7,1,0,1519,-8927.46,542.1 75, 94. 293 3),
(  7,9 ,0,1 519,-892 7.46,542.175,94.2933),
(7,8,0,1519,-8927.46,542.175,94.2933),
(7,4,0,1519,-8927.46,542.175,94.2933),
(8,1,1 ,16 37, 143 4.2 5,- 439 8.3 3,2 5.4628 ),
(8,3,1,1637,1434.25,-4398.33,25.4628),
(8,4,1,1637,1434.25,-4398.33,25.4628),
(8,5,1,1637,1434.25,-4398. 33, 25. 462 8),
(  8,7 ,1,1 637,1434 .25,-4398.33,25.4628),
(8,8,1,1637,1434.25,-4398.33,25.4628),
(10,2,1,1637,1434.25,-4398.33,25.4628),
(10,3 ,1, 163 7,1 434 .25 ,-4 398 .33 ,25.46 28),
(10,4,1,1637,1434.25,-4398.33,25.4628),
(10,5,1,1637,1434.25,-4398.33,25.4628),
(10,8,1,1637,1434.25,- 439 8.3 3,2 5.4 628 ),
(  10 ,9,1,163 7,1434.25,-4398.33,25.4628),
(11,1,0,1519,-8927.46,542.175,94.2933),
(11,2,0,1519,-8927.46,542.175,94.2933),
(  11, 3,0 ,15 19, -89 27. 46, 542.17 5,94.2933),
(11,5,0,1519,-8927.46,542.175,94.2933),
(11,7,0,1519,-8927.46,542.175,94.2933),
(11,8,0,1519,-8 927 .46 ,54 2.1 75, 94. 293 3),
(11, 6,0,1519,-8927.46,542.175,94.2933),
(10,6,1,1637,1434.25,-4398.33,25.4628),
(8,6,1,1637,1434.25,-4398.33,25.4628),
(    7,6 ,0, 151 9,- 892 7.46,5 42.175,94.2933),
(6,6,1,1637,1434.25,-4398.33,25.4628),
(5,6,1,1637,1434.25,-4398.33,25.4628),
(4,6,0,1519, -89 27. 46, 542 .17 5,9 4.2 933 ),
(3 ,6,0,1519,-8927.46,542.175,94.2933),
(2,6,1,1637,1434.25,-4398.33,25.4628),
(1,6,0,1519,-8923,545,94);

Квест на 46 талов ДК:

Code
DELETE FROM `creature_questrelation` WHERE `quest` = 555555;
DELETE FROM `gameobject_questrelation` WHERE `quest` = 555555;
UPDATE `item_template` SET `StartQuest`=0 WHERE `StartQuest` = 555555;
REPLACE INTO `creature_questrelation` (`id`, `quest`) VALUES (25462, 555555);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry` = 25462;
DELETE FROM `creature_involvedrelation` WHERE `quest` = 555555;
DELETE FROM `gameobject_involvedrelation` WHERE `quest` = 555555;
REPLACE INTO `creature_involvedrelation` (`id`, `quest`) VALUES (25462, 555555);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry`=25462;
REPLACE INTO `quest_template` (`entry`, `Method`, `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`, `ReqSourceId1`, `ReqSourceId2`, `ReqSourceId3`, `ReqSourceId4`, `ReqSourceCount1`, `ReqSourceCount2`, `ReqSourceCount3`, `ReqSourceCount4`, `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`, `RewHonorableKills`, `RewOrReqMoney`, `RewMoneyMaxLevel`, `RewSpell`, `RewSpellCast`, `RewMailTemplateId`, `RewMailDelaySecs`, `PointMapId`, `PointX`, `PointY`, `PointOpt`, `DetailsEmote1`, `DetailsEmote2`, `DetailsEmote3`, `DetailsEmote4`, `IncompleteEmote`, `CompleteEmote`, `OfferRewardEmote1`, `OfferRewardEmote2`, `OfferRewardEmote3`, `OfferRewardEmote4`, `StartScript`, `CompleteScript`, `BonusTalents`) VALUES (555555, 2, 0, -6, 80, 80, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'Talents for DK', 'Take 46 talents!!!$BMade my BaNDiT!', 'Say me something.', 'Play for enjoy.', 'Talk to me?', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 25462, 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, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 46);

© из патча бандита
Тотемы при старте:

Code
REPLACE INTO playercreateinfo_item  (`race`, `class`, `itemid`, `amount`)  VALUES
              (11, 7, 5178, 1), (11, 7, 5175, 1),  (11, 7, 5176, 1), (11, 7, 5177, 1),
              (2, 7, 5175, 1),  (2, 7, 5176, 1),  (2, 7, 5177, 1),  (2, 7, 5178, 1),
              (6, 7, 5175, 1), (6, 7, 5176, 1), (6, 7, 5177, 1),  (6, 7, 5178, 1),
              (8, 7, 5175, 1),  (8, 7, 5176, 1),  (8, 7, 5177, 1),  (8, 7, 5178, 1);

Вещь не ломается:

Code
UPDATE `item_template` SET `MaxDurability` = 0 WHERE `MaxDurability` > 0;

Игроку не наносится урон:
В мангос.конф

Code
Rate.Damage.Fall = 1

Никаких сек при воскрешении:
В мангос.конф

Code
Death.CorpseReclaimDelay.PvP = 0
Death.CorpseReclaimDelay.PvE = 0
[/cut]

add DeSSower
[cut]SQL Code add DeSSower
Запросы на удаление всего старого от персоонажей который не захадили в игру более 30 дней , Спасает от высокого размера базы после длительной работы сервера

Code
DELETE `arena_team_member`.* FROM `arena_team_member` LEFT JOIN `characters` ON `arena_team_member`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `arena_team_member`.* FROM `arena_team_member` LEFT JOIN `arena_team` ON `arena_team_member`.`arenateamid` = `arena_team`.`arenateamid` WHERE `arena_team`.`arenateamid` IS NULL;
DELETE `arena_team_stats`.* FROM `arena_team_stats` LEFT JOIN `arena_team` ON `arena_team_stats`.`arenateamid` = `arena_team`.`arenateamid` WHERE `arena_team`.`arenateamid` IS NULL;
DELETE `auctionhouse`.* FROM `auctionhouse` LEFT JOIN `characters` ON `auctionhouse`.`itemowner` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_account_data`.* FROM `character_account_data` LEFT JOIN `characters` ON `character_account_data`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_achievement`.* FROM `character_achievement` LEFT JOIN `characters` ON `character_achievement`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_achievement_progress`.* FROM `character_achievement_progress` LEFT JOIN `characters` ON `character_achievement_progress`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_action`.* FROM `character_action` LEFT JOIN `characters` ON `character_action`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_aura`.* FROM `character_aura` LEFT JOIN `characters` ON `character_aura`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_battleground_data`.* FROM `character_battleground_data` LEFT JOIN `characters` ON `character_battleground_data`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_declinedname`.* FROM `character_declinedname` LEFT JOIN `characters` ON `character_declinedname`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_equipmentsets`.* FROM `character_equipmentsets` LEFT JOIN `characters` ON `character_equipmentsets`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_gifts`.* FROM `character_gifts` LEFT JOIN `characters` ON `character_gifts`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_homebind`.* FROM `character_homebind` LEFT JOIN `characters` ON `character_homebind`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_instance`.* FROM `character_instance` LEFT JOIN `characters` ON `character_instance`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_inventory`.* FROM `character_inventory` LEFT JOIN `characters` ON `character_inventory`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_pet`.* FROM `character_pet` LEFT JOIN `characters` ON `character_pet`.`owner` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_pet_declinedname`.* FROM `character_pet_declinedname` LEFT JOIN `characters` ON `character_pet_declinedname`.`owner` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_queststatus`.* FROM `character_queststatus` LEFT JOIN `characters` ON `character_queststatus`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_queststatus_daily`.* FROM `character_queststatus_daily` LEFT JOIN `characters` ON `character_queststatus_daily`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_reputation`.* FROM `character_reputation` LEFT JOIN `characters` ON `character_reputation`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_social`.* FROM `character_social` LEFT JOIN `characters` ON `character_social`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_social`.* FROM `character_social` LEFT JOIN `characters` ON `character_social`.`friend` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_spell`.* FROM `character_spell` LEFT JOIN `characters` ON `character_spell`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_spell_cooldown`.* FROM `character_spell_cooldown` LEFT JOIN `characters` ON `character_spell_cooldown`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_ticket`.* FROM `character_ticket` LEFT JOIN `characters` ON `character_ticket`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_tutorial`.* FROM `character_tutorial` LEFT JOIN `realmd`.`account` ON `character_tutorial`.`account` = `account`.`id` WHERE `account`.`id` IS NULL;
DELETE `corpse`.* FROM `corpse` LEFT JOIN `characters` ON `corpse`.`player` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `groups`.* FROM `groups` LEFT JOIN `characters` ON `groups`.`leaderGuid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `group_instance`.* FROM `group_instance` LEFT JOIN `characters` ON `group_instance`.`leaderGuid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `guild`.* FROM `guild` LEFT JOIN `characters` ON `guild`.`leaderguid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `guild_bank_eventlog`.* FROM `guild_bank_eventlog` LEFT JOIN `guild` ON `guild_bank_eventlog`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_bank_tab`.* FROM `guild_bank_tab` LEFT JOIN `guild` ON `guild_bank_tab`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_bank_item`.* FROM `guild_bank_item` LEFT JOIN `guild` ON `guild_bank_item`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_bank_right`.* FROM `guild_bank_right` LEFT JOIN `guild` ON `guild_bank_right`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_eventlog`.* FROM `guild_eventlog` LEFT JOIN `guild` ON `guild_eventlog`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_member`.* FROM `guild_member` LEFT JOIN `guild` ON `guild_member`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_member`.* FROM `guild_member` LEFT JOIN `characters` ON `guild_member`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `guild_rank`.* FROM `guild_rank` LEFT JOIN `guild` ON `guild_rank`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE FROM `mail` where `sender` not in (select `guid` from `characters`) and `receiver` not in (select `guid` from `characters`);
DELETE `mail_items`.* FROM `mail_items` LEFT JOIN `mail` ON `mail_items`.`mail_id` = `mail`.`id` WHERE `mail`.`id` IS NULL;
DELETE `petition`.* FROM `petition` LEFT JOIN `characters` ON `petition`.`ownerguid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `petition_sign`.* FROM `petition_sign` LEFT JOIN `characters` ON `petition_sign`.`ownerguid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `pet_aura`.* FROM `pet_aura` LEFT JOIN `character_pet` ON `pet_aura`.`guid` = `character_pet`.`id` WHERE `character_pet`.`id` IS NULL;
DELETE `pet_spell`.* FROM `pet_spell` LEFT JOIN `character_pet` ON `pet_spell`.`guid` = `character_pet`.`id` WHERE `character_pet`.`id` IS NULL;
DELETE `pet_spell_cooldown`.* FROM `pet_spell_cooldown` LEFT JOIN `character_pet` ON `pet_spell_cooldown`.`guid` = `character_pet`.`id` WHERE `character_pet`.`id` IS NULL;
CREATE TABLE `item_instance_tmp` (`guid` int(11) NOT NULL, PRIMARY KEY (`guid`)) ENGINE = MYISAM DEFAULT CHARSET = utf8;
INSERT INTO `item_instance_tmp` SELECT `item_instance`.`guid` FROM `item_instance`;
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `character_inventory` ci ON (`ii`.`guid` = `ci`.`item`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `mail_items` ci ON (`ii`.`guid` = `ci`.`item_guid`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `auctionhouse` ci ON (`ii`.`guid` = `ci`.`itemguid`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `character_gifts` ci ON (`ii`.`guid` = `ci`.`item_guid`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `guild_bank_item` ci ON (`ii`.`guid` = `ci`.`item_guid`);
DELETE ii.* FROM `item_instance` ii INNER JOIN `item_instance_tmp` iit ON (`ii`.`guid` = `iit`.`guid`);
DROP TABLE `item_instance_tmp`;
#end

Образец создания портала

Code
REPLACE INTO `areatrigger_teleport` (`id`, `name`, `required_level`, `required_item`, `required_item2`, `heroic_key`, `heroic_key2`, `required_quest_done`, `required_failed_text`, `target_map`, `target_position_x`, `target_position_y`, `target_position_z`, `target_orientation`) VALUES
(6349, 'Portal to Blood Ring', 0, 0, 0, 0, 0, 0, NULL, 530, -1988.192139, 6571.001653, 10.589470, 1.133);

REPLACE INTO `spell_target_position` (`id`, `target_map`, `target_position_x`, `target_position_y`, `target_position_z`, `target_orientation`) VALUES
(6349, 530, -1988.192139, 6571.001653, 10.589470, 1.133);

REPLACE INTO `gameobject_template` (`entry`, `type`, `displayId`, `name`, `castBarCaption`, `faction`, `flags`, `size`, `data0`, `data1`, `data2`, `data3`, `data4`, `data5`, `data6`, `data7`, `data8`, `data9`, `data10`, `data11`, `data12`, `data13`, `data14`, `data15`, `data16`, `data17`, `data18`, `data19`, `data20`, `data21`, `data22`, `data23`, `ScriptName`) VALUES
(402402, 22, 6955, 'Portal in Blood RING', '', 0, 0, 1, 6349, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '');

Моя работа (Квестовые спеллы для тренеров) 2.4.3 и 3.Х.Х (Закреплена)!
Вся информация о моем сервере.

Сообщение # 1 отредактировано zyxel8 - Четверг, 25.02.2010, 16:28

L30m4nc3r

TC User

Репутация: 1610

Пополняйте своими интересными sql запросами!

Совершенно безопасен для людей, обладающих хотя бы некоторыми минимальными зачатками интеллекта, и способными строить причинно-следственные цепочки. ©

Сообщение # 2 написано 02.01.2010 в 18:56

Kaspera

Старший Маршал

Репутация: 584

Pro[100]Hens, очень нужно это всё , а можно эти всё вместе и залить на файлообменник ??

Сообщение # 3 написано 02.01.2010 в 18:59

zyxel8

Сержант

Репутация: 263

Kaspera, щас я sql сделаю, и прекреплю к 1 посту.

Моя работа (Квестовые спеллы для тренеров) 2.4.3 и 3.Х.Х (Закреплена)!
Вся информация о моем сервере.

Сообщение # 4 написано 02.01.2010 в 19:01

glebyz

Центурион

Репутация: 58

zyxel8, молодец лови +!

Сообщение # 5 написано 02.01.2010 в 19:04

zyxel8

Сержант

Репутация: 263

Kaspera, cмысла под весь sql прекреплять мне нету...
всеравно тут даны запросы чтоб ты их изменял под себя... такчто прекреплять не буду

Моя работа (Квестовые спеллы для тренеров) 2.4.3 и 3.Х.Х (Закреплена)!
Вся информация о моем сервере.

Сообщение # 6 написано 02.01.2010 в 19:11

L30m4nc3r

TC User

Репутация: 1610

Помоему 100% лут, еще не пробовал сам.

Code

UPDATE creature_loot_template SET ChanceOrQuestChance=99, groupid=0 WHERE ChanceOrQuestChance<99 AND groupid>0;

далее в mangosd.conf

Code

Rate.Drop.Item.Epic = 100
Rate.Drop.Item.Legendary = 100

Сообщение # 7 написано 02.01.2010 в 19:35

Nichers

Маршал

Репутация: 1319

Не плохо,может пригодиться

Сообщение # 8 написано 02.01.2010 в 19:40

zyxel8

Сержант

Репутация: 263

Nichers, поэтому я и выложил smile

Моя работа (Квестовые спеллы для тренеров) 2.4.3 и 3.Х.Х (Закреплена)!
Вся информация о моем сервере.

Сообщение # 9 написано 02.01.2010 в 19:41

TRACE

Борец за справедливость

Репутация: 1207

На какой ак тебе ставить +?)

Я 80% отсюда не знал, молодец, спс)

Сообщение # 10 написано 02.01.2010 в 20:08

LinyX

Капрал

Репутация: 168

TRACE, аналогично...жесть лови +

Сообщение # 11 написано 02.01.2010 в 20:24

zyxel8

Сержант

Репутация: 263

TRACE, на zyxel8 biggrin

Моя работа (Квестовые спеллы для тренеров) 2.4.3 и 3.Х.Х (Закреплена)!
Вся информация о моем сервере.

Сообщение # 12 написано 02.01.2010 в 20:35

lanc

The Hurt Locker

Репутация: 1524

можна запрос на убрание каста у боссов?)

пример: npc - 10184 spell - 69286

P.S. убрать с Ониксии спелл Взмах хвостом...

Сообщение # 13 написано 10.02.2010 в 13:50

Zeppelin

Сержант

Репутация: 118

спс нужная вещь

Сообщение # 14 написано 10.02.2010 в 17:20

L30m4nc3r

TC User

Репутация: 1610

Zeppelin, нез, пополняйте своими запросами, буду в 1 пост добавлять
[cut]вот так + ваш ник[/cut]

Сообщение # 15 написано 10.02.2010 в 19:06

almazz

Чемпион

Репутация: 59

меняй запросы в code подцветка неработает

помог? поставь +

Сообщение # 16 написано 10.02.2010 в 20:36

L30m4nc3r

TC User

Репутация: 1610

almazz, сек щас поправлю

Сообщение # 17 написано 10.02.2010 в 20:48

Sasha_12

Проверенный торговец

Репутация: 2065

Позже, как время будет добавлю парочку...

Добавлено (25.02.2010, 08:10)
---------------------------------------------
Тренер на все оружия:

Code

REPLACE INTO `npc_trainer` (entry, spell, spellcost, reqskill, reqskillvalue, reqlevel) VALUES
(11867, 196, 1000, 0, 0, 0),
(11867, 197, 1000, 0, 0, 0),
(11867, 198, 1000, 0, 0, 0),
(11867, 199, 1000, 0, 0, 0),
(11867, 200, 10000, 0, 0, 0),
(11867, 201, 1000, 0, 0, 0),
(11867, 202, 1000, 0, 0, 0),
(11867, 227, 1000, 0, 0, 0),
(11867, 264, 1000, 0, 0, 0),
(11867, 266, 1000, 0, 0, 0),
(11867, 1180, 1000, 0, 0, 0),
(11867, 2567, 1000, 0, 0, 0),
(11867, 5011, 1000, 0, 0, 0),
(11867, 15590, 1000, 0, 0, 0);

Старт в столицах:

Code

REPLACE INTO `playercreateinfo`(`race`,`class`,`map`,`zone`,`position_x`,`position_y`,`position_z`) values
(1,1,0,1519,-8927.46,542.175,94.2933),
(1,2,0,1519,-8927.46,542.175,94.2933),
(1,4,0,1519,-8927.46,542.175,94.2933),
(1,5,0,1519,-8927. 46,542.175,94.2933),
(1,8,0,1519,-8927.46,542.175,94.2933),
(1,9,0,1519,-8927.46,542.175,94.2933),
(2,1,1,1637,1434.25,-4398.33,25.4628),
(2,3,1,1637,1434.25,-4398.33,25.4628),
(2,4,1,1637,1434.25,-4398.33,25.4628),
(2,7,1,1637,1434.25,-4398.33,25.4628),
(2,9,1,1637,1434.2 5,-4398.33,25.4628),
(3,1,0,1519,-8927.46,542.175,94.2933),
(3,2,0,1519,-8927.46,542.175,94.2933),
(3,3,0,1519,-8927.46,542.175,94.2933),
(3,4,0,1519,-8927.46,542.175,94.2933),
(3,5,0,1519,-8927.46,542.175,94.2933),
(4,1,0,1519,-8927.46,542.175,94.2933),
(4,3,0,1519,-8927. 46,542.175,94.2933),
(4,4,0,1519,-8927.46,542.175,94.2933),
(4,5,0,1519,-8927.46,542.175,94.2933),
(4,11,0,1519,-8927.46,542.175,94.2933) ,
(5,1,1,1637,1434.25,-4398.33,25.4628),
(5,4,1,1637,1434.25,-4398.33,25.4628),
(5,5,1,1637,1434.25,-4398.33,25.4628),
(5,8,1,1637,1434. 25,-4398.33,25.4628),
(5,9,1,1637,1434.25,-4398.33,25.4628),
(6,1,1,1637,1434.25,-4398.33,25.4628),
(6,3,1,1637,1434.25,-4398.33,25.4628) ,
(6,7,1,1637,1434.25,-4398.33,25.4628),
(6,11,1,1637,1434.25,-4398.33,25.4628),
(7,1,0,1519,-8927.46,542.175,94.2933),
(7,9,0,1519,-892 7.46,542.175,94.2933),
(7,8,0,1519,-8927.46,542.175,94.2933),
(7,4,0,1519,-8927.46,542.175,94.2933),
(8,1,1,1637,1434.25,-4398.33,25.4628 ),
(8,3,1,1637,1434.25,-4398.33,25.4628),
(8,4,1,1637,1434.25,-4398.33,25.4628),
(8,5,1,1637,1434.25,-4398.33,25.4628),
(8,7,1,1637,1434 .25,-4398.33,25.4628),
(8,8,1,1637,1434.25,-4398.33,25.4628),
(10,2,1,1637,1434.25,-4398.33,25.4628),
(10,3,1,1637,1434.25,-4398.33,25.46 28),
(10,4,1,1637,1434.25,-4398.33,25.4628),
(10,5,1,1637,1434.25,-4398.33,25.4628),
(10,8,1,1637,1434.25,-4398.33,25.4628),
(10,9,1,163 7,1434.25,-4398.33,25.4628),
(11,1,0,1519,-8927.46,542.175,94.2933),
(11,2,0,1519,-8927.46,542.175,94.2933),
(11,3,0,1519,-8927.46,542.17 5,94.2933),
(11,5,0,1519,-8927.46,542.175,94.2933),
(11,7,0,1519,-8927.46,542.175,94.2933),
(11,8,0,1519,-8927.46,542.175,94.2933),
(11, 6,0,1519,-8927.46,542.175,94.2933),
(10,6,1,1637,1434.25,-4398.33,25.4628),
(8,6,1,1637,1434.25,-4398.33,25.4628),
(7,6,0,1519,-8927.46,5 42.175,94.2933),
(6,6,1,1637,1434.25,-4398.33,25.4628),
(5,6,1,1637,1434.25,-4398.33,25.4628),
(4,6,0,1519,-8927.46,542.175,94.2933),
(3 ,6,0,1519,-8927.46,542.175,94.2933),
(2,6,1,1637,1434.25,-4398.33,25.4628),
(1,6,0,1519,-8923,545,94);

Квест на 46 талов ДК:

Code

DELETE FROM `creature_questrelation` WHERE `quest` = 555555;
DELETE FROM `gameobject_questrelation` WHERE `quest` = 555555;
UPDATE `item_template` SET `StartQuest`=0 WHERE `StartQuest` = 555555;
REPLACE INTO `creature_questrelation` (`id`, `quest`) VALUES (25462, 555555);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry` = 25462;
DELETE FROM `creature_involvedrelation` WHERE `quest` = 555555;
DELETE FROM `gameobject_involvedrelation` WHERE `quest` = 555555;
REPLACE INTO `creature_involvedrelation` (`id`, `quest`) VALUES (25462, 555555);
UPDATE `creature_template` SET `npcflag`=`npcflag`|2 WHERE `entry`=25462;
REPLACE INTO `quest_template` (`entry`, `Method`, `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`, `ReqSourceId1`, `ReqSourceId2`, `ReqSourceId3`, `ReqSourceId4`, `ReqSourceCount1`, `ReqSourceCount2`, `ReqSourceCount3`, `ReqSourceCount4`, `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`, `RewHonorableKills`, `RewOrReqMoney`, `RewMoneyMaxLevel`, `RewSpell`, `RewSpellCast`, `RewMailTemplateId`, `RewMailDelaySecs`, `PointMapId`, `PointX`, `PointY`, `PointOpt`, `DetailsEmote1`, `DetailsEmote2`, `DetailsEmote3`, `DetailsEmote4`, `IncompleteEmote`, `CompleteEmote`, `OfferRewardEmote1`, `OfferRewardEmote2`, `OfferRewardEmote3`, `OfferRewardEmote4`, `StartScript`, `CompleteScript`, `BonusTalents`) VALUES (555555, 2, 0, -6, 80, 80, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'Talents for DK', 'Take 46 talents!!!$BMade my BaNDiT!', 'Say me something.', 'Play for enjoy.', 'Talk to me?', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 25462, 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, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 46);

Все взято из патча бандита.

Тотемы при старте:

Code

REPLACE INTO playercreateinfo_item  (`race`, `class`, `itemid`, `amount`)  VALUES
    (11, 7, 5178, 1), (11, 7, 5175, 1),  (11, 7, 5176, 1), (11, 7, 5177, 1),
    (2, 7, 5175, 1),  (2, 7, 5176, 1),  (2, 7, 5177, 1),  (2, 7, 5178, 1),
    (6, 7, 5175, 1), (6, 7, 5176, 1), (6, 7, 5177, 1),  (6, 7, 5178, 1),
    (8, 7, 5175, 1),  (8, 7, 5176, 1),  (8, 7, 5177, 1),  (8, 7, 5178, 1);

Вещь не ломается:

Code

UPDATE `item_template` SET `MaxDurability` = 0 WHERE `MaxDurability` > 0;

Игроку не наносится урон:
В мангос.конф

Code

Rate.Damage.Fall = 0

Меняем на

Code

Rate.Damage.Fall = 1

Никаких сек при воскрешении:
В мангос.конф

Code

Adrenaline-WoW, 24.02.2010 0:54:10:
Death.CorpseReclaimDelay.PvP = 1
Death.CorpseReclaimDelay.PvE = 1

Меняем на

Code

Adrenaline-WoW, 24.02.2010 0:54:10:
Death.CorpseReclaimDelay.PvP = 0
Death.CorpseReclaimDelay.PvE = 0

Выступлю гарантом на любых видах сделок, подробности в асю/ЛС. Бесплатно.

Сообщение # 18 написано 25.02.2010 в 08:10

lordinpvp

Капрал

Репутация: 61

zyxel8,источник всего этого mangos.ru и другие форумы.

I'am

Сообщение # 19 написано 25.02.2010 в 09:32

Sasha_12

Проверенный торговец

Репутация: 2065

Автор, добавь мое в первый пост!

Добавлено (25.02.2010, 11:27)
---------------------------------------------
Добавте тему в мануалы, буду добавлять

Добавлено (25.02.2010, 13:29)
---------------------------------------------
Тему АП!

Выступлю гарантом на любых видах сделок, подробности в асю/ЛС. Бесплатно.

Сообщение # 20 написано 25.02.2010 в 13:29

DeSSower

Маршал

Репутация: 1046

Запросы на удаление всего старого от персоонажей который не захадили в игру более 30 дней , Спасает от высокого размера базы после длительной работы сервера

Code

#лишние акки
use realmd;
DELETE FROM `account` WHERE DATEDIFF(NOW(), `last_login`)>30 AND gmlevel = '0';
DELETE FROM `account` where `id` not in (select `account` from `characters`.`characters`) AND gmlevel = '0';
DELETE `account_banned`.* FROM `account_banned` LEFT JOIN `account` ON `account_banned`.`id` = `account`.`id` WHERE `account`.`id` IS NULL;
use characters;
#чары без акков
DELETE `characters`.* FROM `characters` LEFT JOIN `realmd`.`account` ON `characters`.`account` = `account`.`id` WHERE `account`.`id` IS NULL;
#мусор от чаров
DELETE `account_data`.* FROM `account_data` LEFT JOIN `realmd`.`account` ON `account_data`.`account` = `account`.`id` WHERE `account`.`id` IS NULL;
DELETE `arena_team`.* FROM `arena_team` LEFT JOIN `characters` ON `arena_team`.`captainguid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `arena_team_member`.* FROM `arena_team_member` LEFT JOIN `characters` ON `arena_team_member`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `arena_team_member`.* FROM `arena_team_member` LEFT JOIN `arena_team` ON `arena_team_member`.`arenateamid` = `arena_team`.`arenateamid` WHERE `arena_team`.`arenateamid` IS NULL;
DELETE `arena_team_stats`.* FROM `arena_team_stats` LEFT JOIN `arena_team` ON `arena_team_stats`.`arenateamid` = `arena_team`.`arenateamid` WHERE `arena_team`.`arenateamid` IS NULL;
DELETE `auctionhouse`.* FROM `auctionhouse` LEFT JOIN `characters` ON `auctionhouse`.`itemowner` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_account_data`.* FROM `character_account_data` LEFT JOIN `characters` ON `character_account_data`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_achievement`.* FROM `character_achievement` LEFT JOIN `characters` ON `character_achievement`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_achievement_progress`.* FROM `character_achievement_progress` LEFT JOIN `characters` ON `character_achievement_progress`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_action`.* FROM `character_action` LEFT JOIN `characters` ON `character_action`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_aura`.* FROM `character_aura` LEFT JOIN `characters` ON `character_aura`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_battleground_data`.* FROM `character_battleground_data` LEFT JOIN `characters` ON `character_battleground_data`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_declinedname`.* FROM `character_declinedname` LEFT JOIN `characters` ON `character_declinedname`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_equipmentsets`.* FROM `character_equipmentsets` LEFT JOIN `characters` ON `character_equipmentsets`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_gifts`.* FROM `character_gifts` LEFT JOIN `characters` ON `character_gifts`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_homebind`.* FROM `character_homebind` LEFT JOIN `characters` ON `character_homebind`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_instance`.* FROM `character_instance` LEFT JOIN `characters` ON `character_instance`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_inventory`.* FROM `character_inventory` LEFT JOIN `characters` ON `character_inventory`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_pet`.* FROM `character_pet` LEFT JOIN `characters` ON `character_pet`.`owner` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_pet_declinedname`.* FROM `character_pet_declinedname` LEFT JOIN `characters` ON `character_pet_declinedname`.`owner` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_queststatus`.* FROM `character_queststatus` LEFT JOIN `characters` ON `character_queststatus`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_queststatus_daily`.* FROM `character_queststatus_daily` LEFT JOIN `characters` ON `character_queststatus_daily`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_reputation`.* FROM `character_reputation` LEFT JOIN `characters` ON `character_reputation`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_social`.* FROM `character_social` LEFT JOIN `characters` ON `character_social`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_social`.* FROM `character_social` LEFT JOIN `characters` ON `character_social`.`friend` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_spell`.* FROM `character_spell` LEFT JOIN `characters` ON `character_spell`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_spell_cooldown`.* FROM `character_spell_cooldown` LEFT JOIN `characters` ON `character_spell_cooldown`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_ticket`.* FROM `character_ticket` LEFT JOIN `characters` ON `character_ticket`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `character_tutorial`.* FROM `character_tutorial` LEFT JOIN `realmd`.`account` ON `character_tutorial`.`account` = `account`.`id` WHERE `account`.`id` IS NULL;
DELETE `corpse`.* FROM `corpse` LEFT JOIN `characters` ON `corpse`.`player` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `groups`.* FROM `groups` LEFT JOIN `characters` ON `groups`.`leaderGuid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `group_instance`.* FROM `group_instance` LEFT JOIN `characters` ON `group_instance`.`leaderGuid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `guild`.* FROM `guild` LEFT JOIN `characters` ON `guild`.`leaderguid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `guild_bank_eventlog`.* FROM `guild_bank_eventlog` LEFT JOIN `guild` ON `guild_bank_eventlog`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_bank_tab`.* FROM `guild_bank_tab` LEFT JOIN `guild` ON `guild_bank_tab`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_bank_item`.* FROM `guild_bank_item` LEFT JOIN `guild` ON `guild_bank_item`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_bank_right`.* FROM `guild_bank_right` LEFT JOIN `guild` ON `guild_bank_right`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_eventlog`.* FROM `guild_eventlog` LEFT JOIN `guild` ON `guild_eventlog`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_member`.* FROM `guild_member` LEFT JOIN `guild` ON `guild_member`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE `guild_member`.* FROM `guild_member` LEFT JOIN `characters` ON `guild_member`.`guid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `guild_rank`.* FROM `guild_rank` LEFT JOIN `guild` ON `guild_rank`.`guildid` = `guild`.`guildid` WHERE `guild`.`guildid` IS NULL;
DELETE FROM `mail` where `sender` not in (select `guid` from `characters`) and `receiver` not in (select `guid` from `characters`);
DELETE `mail_items`.* FROM `mail_items` LEFT JOIN `mail` ON `mail_items`.`mail_id` = `mail`.`id` WHERE `mail`.`id` IS NULL;
DELETE `petition`.* FROM `petition` LEFT JOIN `characters` ON `petition`.`ownerguid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `petition_sign`.* FROM `petition_sign` LEFT JOIN `characters` ON `petition_sign`.`ownerguid` = `characters`.`guid` WHERE `characters`.`guid` IS NULL;
DELETE `pet_aura`.* FROM `pet_aura` LEFT JOIN `character_pet` ON `pet_aura`.`guid` = `character_pet`.`id` WHERE `character_pet`.`id` IS NULL;
DELETE `pet_spell`.* FROM `pet_spell` LEFT JOIN `character_pet` ON `pet_spell`.`guid` = `character_pet`.`id` WHERE `character_pet`.`id` IS NULL;
DELETE `pet_spell_cooldown`.* FROM `pet_spell_cooldown` LEFT JOIN `character_pet` ON `pet_spell_cooldown`.`guid` = `character_pet`.`id` WHERE `character_pet`.`id` IS NULL;
CREATE TABLE `item_instance_tmp` (`guid` int(11) NOT NULL, PRIMARY KEY (`guid`)) ENGINE = MYISAM DEFAULT CHARSET = utf8;
INSERT INTO `item_instance_tmp` SELECT `item_instance`.`guid` FROM `item_instance`;
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `character_inventory` ci ON (`ii`.`guid` = `ci`.`item`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `mail_items` ci ON (`ii`.`guid` = `ci`.`item_guid`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `auctionhouse` ci ON (`ii`.`guid` = `ci`.`itemguid`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `character_gifts` ci ON (`ii`.`guid` = `ci`.`item_guid`);
DELETE ii.* FROM `item_instance_tmp` ii INNER JOIN `guild_bank_item` ci ON (`ii`.`guid` = `ci`.`item_guid`);
DELETE ii.* FROM `item_instance` ii INNER JOIN `item_instance_tmp` iit ON (`ii`.`guid` = `iit`.`guid`);
DROP TABLE `item_instance_tmp`;
#end

Добавлено (25.02.2010, 13:38)
---------------------------------------------
Образец создания портала

Code

REPLACE INTO `areatrigger_teleport` (`id`, `name`, `required_level`, `required_item`, `required_item2`, `heroic_key`, `heroic_key2`, `required_quest_done`, `required_failed_text`, `target_map`, `target_position_x`, `target_position_y`, `target_position_z`, `target_orientation`) VALUES
(6349, 'Portal to Blood Ring', 0, 0, 0, 0, 0, 0, NULL, 530, -1988.192139, 6571.001653, 10.589470, 1.133);

REPLACE INTO `spell_target_position` (`id`, `target_map`, `target_position_x`, `target_position_y`, `target_position_z`, `target_orientation`) VALUES
(6349, 530, -1988.192139, 6571.001653, 10.589470, 1.133);

REPLACE INTO `gameobject_template` (`entry`, `type`, `displayId`, `name`, `castBarCaption`, `faction`, `flags`, `size`, `data0`, `data1`, `data2`, `data3`, `data4`, `data5`, `data6`, `data7`, `data8`, `data9`, `data10`, `data11`, `data12`, `data13`, `data14`, `data15`, `data16`, `data17`, `data18`, `data19`, `data20`, `data21`, `data22`, `data23`, `ScriptName`) VALUES
(402402, 22, 6955, 'Portal in Blood RING', '', 0, 0, 1, 6349, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '');

Как Стать админом DeSSower WOW 1

Сообщение # 21 написано 25.02.2010 в 13:38

L30m4nc3r

TC User

Репутация: 1610

Sasha_12, и DeSSower, сейчас добавлю спасибо за то что делитесь.

Сообщение # 22 написано 25.02.2010 в 15:43

zyxel8

Сержант

Репутация: 263

Sasha_12, DeSSower, добавил в первый потс! спасибо!

Моя работа (Квестовые спеллы для тренеров) 2.4.3 и 3.Х.Х (Закреплена)!
Вся информация о моем сервере.

Сообщение # 23 написано 25.02.2010 в 16:15

DeSSower

Маршал

Репутация: 1046

Quote (Pro|100|Hens)

Sasha_12, и DeSSower, сейчас добавлю спасибо за то что делитесь.

я как от души оторвал свои секреты happy

Как Стать админом DeSSower WOW 1

Сообщение # 24 написано 25.02.2010 в 20:15

L30m4nc3r

TC User

Репутация: 1610

DeSSower, ну как не как, я незнал этого запроса, и многие. Такчто инфа для всех полезная какой бы она не была.

Сообщение # 25 написано 25.02.2010 в 20:33

Sasha_12

Проверенный торговец

Репутация: 2065

DeSSower, спс за портал, опробую, отпишусь...

Добавлено (27.02.2010, 20:10)
---------------------------------------------
Темку не опускаем, модеры закрепите!

Выступлю гарантом на любых видах сделок, подробности в асю/ЛС. Бесплатно.

Сообщение # 26 написано 27.02.2010 в 20:10

L30m4nc3r

TC User

Репутация: 1610

Sasha_12, в закреплении тема не нуждается, в скоре, если еще парочку sql запросов добавят, в первый пост они уже невлезут, и прийдется листать только страницы с постами.

Сообщение # 27 написано 27.02.2010 в 20:33