Новые сообщения · Участники · Правила форума · Поиск · RSS
Страница 1 из 11
Форум CS-WCS.Ru » » MaNGOS » [SQL]3апросы которые должен знать каждый админ.
[SQL]3апросы которые должен знать каждый админ.
Дата: Пятница, 12.09.2014, 18:14 | Сообщение # 1
Постоянный
Сообщений:
1365
Награды:
1
Репутация:
16
Замечания:
0%
Делимся SQL запросами через навикат add Pro[100]Hens
[cut]SQL Code add Pro[100]Hens
Снизить дамаг у всех в столько то раз: Codeupdate `creature_proto` set
`attacktime`=`attacktime`*1,`mindamage`=`mindamage`/1.2,`maxdamage`=`maxdamage`/2,`rangedattacktime`=`rangedattacktime`*1,`rangedmindamage`=`rangedmindamage`/1.2,`rangedmaxdamage`=`rangedmaxdamage`/2 Снизить hp у всех в столько то раз: Codeupdate `creature_proto` set
`minhealth` =`minhealth`/2,`maxhealth`=`maxhealth`/2 удалить аккаунты не заходившие в игру с определённого времени(выделено красным цветом) DELETE FROM `logon`.`accounts` WHERE `lastlogin` < '2007-04-15 00:00:00'; На арене кладбище CodeUPDATE `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`); Запрос который показывает количество денег у персонажа CodeSELECT `name`, SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1177),' ',-1) as `money`
FROM `character` order by 'name' Удаление объектов/мобов. #объекты Codeselect * 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); #мобы Codeselect * from creature where id not in ( select entry from creature_template);
delete from creature where id not in ( select entry from creature_template); Удаление вещи из инвенторя всех чаров: CodeDELETE FROM `character_inventory` WHERE `item_template` IN ('entry1','entry2',...); Запрос удаляет вещь у всех торговцов, запрос по id (entry) вещи: delete from npc_vendor where item ='id_вещи' или delete from npc_vendor where item in (select entry from item_template where name ='точное_имя_вещи') запрос на удаление вещей у чаров, которых нету в item_template : CodeDELETE FROM `characters`.`character_inventory` where `item_template` not in (SELECT `entry` FROM `mangos`.`item_template`); сделать вайп денег Codeupdate character set SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',1177),' ',-1)=0
where SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',1177),' ',-1)>1 вещи на которых можно навариться CodeSELECT * 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); Сделано в виде готового фикса удаляем старые данные о мобе и луте CodeDELETE FROM creature_template WHERE entry=30000; # delete mob if exist
DELETE FROM creature_loot_template WHERE entry=30000; # delete loot if exist создаем нового моба CodeINSERT 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 Добавляем ему лут CodeINSERT 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'); Забыли про вещи которые должны падать? CodeDELETE 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 чтобы знать куда его добавить) CodeDELETE 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, '');
Форум CS-WCS.Ru » » MaNGOS » [SQL]3апросы которые должен знать каждый админ.
Страница 1 из 11
Поиск: