[Информация] Полезный SQL запросы

[n1cekkk]

Удалить левый дроп кроме адены
Код:

DELETE FROM `droplist` WHERE (`itemId`!='57') AND (`category`='-1')

Меняем 70% дроп аден на 100%

Код:

UPDATE `droplist` SET `droplist`.`chance`=1000000 WHERE `droplist`.`itemId`=57;

Удаление всех вещей с определённым ID у определнного персонажа.
Код:

DELETE * FROM items WHERE item_id=(ид того что удалить) AND owner_id=(ИД перса, у когорого удалить)

Снижение цен во всех магазинах до 1 адены:
Код:

update etcitem set price=1 where price > 1;
update weapon set price=1 where price > 1;
update armor set price=1 where price > 1;

Убирает вес:
Код:

update etcitem set weight=1 where weight> 1;
update weapon set weight=1 where weight> 1;
update armor set weight=1 where weight > 1;

Скрипт для очистки базы ява сервера:
суть простая, удаляются чары и все вещи и прочее ниже определенного времени или не заходившие с какого то времени.
Код:

UPDATE characters SET online=0;
DELETE FROM characters WHERE lastAccess < 1191211200;
DELETE FROM accounts WHERE lastactive < 1191211200;
DELETE FROM characters WHERE level < 75;
DELETE FROM characters WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM accounts WHERE login NOT IN (SELECT account_name FROM characters);
DELETE FROM clan_data WHERE leader_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM clan_privs WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_subpledges WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_wars WHERE clan1 NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_wars WHERE clan2 NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM auction_bid WHERE bidderId NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clanhall_functions WHERE hall_id NOT IN (SELECT ID FROM clanhall WHERE ownerId <> '0');
UPDATE clanhall SET paidUntil='0' WHERE ownerId NOT IN (SELECT clan_id FROM clan_data);
UPDATE clanhall SET ownerId='0' WHERE ownerId NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM account_data WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM account_data WHERE account_name NOT IN (SELECT account_name FROM characters);
DELETE FROM account_data WHERE value NOT IN (SELECT obj_Id FROM characters);
DELETE FROM items WHERE loc <> 'clanwh' and owner_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM items WHERE loc = 'clanwh' and owner_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM character_skills WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_skills_save WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_shortcuts WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_recipebook WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_quests WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_macroses WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_hennas WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_friends WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_subclasses WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM couples WHERE player1Id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM couples WHERE player2Id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM pets where item_obj_id not in (SELECT object_id FROM items);
DELETE FROM seven_signs WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM forums WHERE forum_owner_id <> '0' AND forum_owner_id NOT IN (SELECT clan_id FROM clan_data);

Продажа всего по 0 аден

Код:

UPDATE `armor` SET price=0 where price > 1;
UPDATE `etcitem` SET price=0 where price > 1;
UPDATE `weapon` SET price=0 where price > 1;

авто рестарт каждые 12 чесов
Код:

INSERT INTO `global_tasks` VALUES ("5", "restart", "TYPE_SHEDULED", "2147483647", "43200000", "43200000", "360");

Устанавливаем вес всех вещей 0
Код:

UPDATE `etcitem` SET `weight`=0 WHERE `weight`>0;
UPDATE `armor` SET `weight`=0 WHERE `weight`>0;
UPDATE `weapon` SET `weight`=0 WHERE `weight`>0;

Изменяем имя персонажу
Код:

UPDATE characters SET char_name='новое_имя' WHERE char_name='старое_имя'

Полное удаление Нпса из БД
Код:

DELETE FROM spawnlist WHERE npc_templateid = 50000;
DELETE FROM npc WHERE id = 50000;

Удаление всех вещей с определённым ID у определнного персонажа.
Код:

DELETE * FROM items WHERE item_id=(ид того что удалить) AND owner_id=(ИД перса, у которого удалить)

Снижение цен продажи вещей в магазины до 1 адены:
Код:

update etcitem set price=1 where price > 1;
update weapon set price=1 where price > 1;
update armor set price=1 where price > 1;

Убираем весь дроп кроме адены
Код:

DELETE FROM `droplist` WHERE (`itemId`!='57') AND (`category`='-1')

Устанавливаем шанс дропа аден 100% вместо 70%
Код: [

UPDATE `droplist` SET `droplist`.`chance`=1000000 WHERE `droplist`.`itemId`=57;

Удаляем из дропа всё, кроме вещей с ID 57, 77, 107

Код:

DELETE FROM droplist WHERE itemId NOT IN (57,77,107);

Удаление дропа:

Ресурсы
Код:

DELETE FROM droplist WHERE itemId='1864';
DELETE FROM droplist WHERE itemId='1865';
DELETE FROM droplist WHERE itemId='1866';
DELETE FROM droplist WHERE itemId='1868';
DELETE FROM droplist WHERE itemId='1869';
DELETE FROM droplist WHERE itemId='1870';
DELETE FROM droplist WHERE itemId='1871';
DELETE FROM droplist WHERE itemId='1872';
DELETE FROM droplist WHERE itemId='1873';
DELETE FROM droplist WHERE itemId='1874';
DELETE FROM droplist WHERE itemId='1875';
DELETE FROM droplist WHERE itemId='1876';
DELETE FROM droplist WHERE itemId='1877';
DELETE FROM droplist WHERE itemId='1878';
DELETE FROM droplist WHERE itemId='1879';
DELETE FROM droplist WHERE itemId='1880';
DELETE FROM droplist WHERE itemId='1881';
DELETE FROM droplist WHERE itemId='1882';
DELETE FROM droplist WHERE itemId='1884';
DELETE FROM droplist WHERE itemId='1885';
DELETE FROM droplist WHERE itemId='1895';
DELETE FROM droplist WHERE itemId='5549';
DELETE FROM droplist WHERE itemId='1886';
DELETE FROM droplist WHERE itemId='1887';
DELETE FROM droplist WHERE itemId='1888';
DELETE FROM droplist WHERE itemId='1890';
DELETE FROM droplist WHERE itemId='1891';
DELETE FROM droplist WHERE itemId='1892';
DELETE FROM droplist WHERE itemId='1893';
DELETE FROM droplist WHERE itemId='1889';
DELETE FROM droplist WHERE itemId='1894';
DELETE FROM droplist WHERE itemId='4039';
DELETE FROM droplist WHERE itemId='4040';
DELETE FROM droplist WHERE itemId='4041';
DELETE FROM droplist WHERE itemId='4042';
DELETE FROM droplist WHERE itemId='4043';
DELETE FROM droplist WHERE itemId='4044';
DELETE FROM droplist WHERE itemId='4046';
DELETE FROM droplist WHERE itemId='4047';
DELETE FROM droplist WHERE itemId='4045';
DELETE FROM droplist WHERE itemId='5550';
DELETE FROM droplist WHERE itemId='4048';

Стрелы
Код:

DELETE FROM droplist WHERE itemId='17';
DELETE FROM droplist WHERE itemId='1341';
DELETE FROM droplist WHERE itemId='1342';
DELETE FROM droplist WHERE itemId='1343';
DELETE FROM droplist WHERE itemId='1344';
DELETE FROM droplist WHERE itemId='1345';
DELETE FROM droplist WHERE itemId='4048';

Potions
Код:

DELETE FROM droplist WHERE itemId='1539';
DELETE FROM droplist WHERE itemId='5591';
DELETE FROM droplist WHERE itemId='5592';

Cursed Bone

[sql]DELETE FROM droplist WHERE itemId='2508';

SoE
Код:

DELETE FROM droplist WHERE itemId='736';
DELETE FROM droplist WHERE itemId='1829';
DELETE FROM droplist WHERE itemId='1830';
DELETE FROM droplist WHERE itemId='737';
DELETE FROM droplist WHERE itemId='1538';

Возвращаем РБ бижу Боссам
Код:

INSERT INTO droplist VALUES ('29001', '6660', '1', '1', '12', '300000');
INSERT INTO droplist VALUES ('29006', '6662', '1', '1', '8', '300000');
INSERT INTO droplist VALUES ('29014', '6661', '1', '1', '9', '300000');
INSERT INTO droplist VALUES ('29022', '6659', '1', '1', '13', '1000000');
INSERT INTO droplist VALUES ('29020', '6658', '1', '1', '2', '1000000');
INSERT INTO droplist VALUES ('29019', '6656', '1', '1', '31', '1000000');
INSERT INTO droplist VALUES ('29028', '6657', '1', '1', '34', '1000000');

Добавляем скил Weight Limit всем персонажам.
Код:

INSERT INTO skill_trees VALUES (0,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (10,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (18,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (25,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (31,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (38,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (44,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (49,150,2,'Weight Limit',0,1);
INSERT INTO skill_trees VALUES (53,150,2,'Weight Limit',0,1);

Добавляем скил Noblesse Blessingt всем персонажам
Код:

INSERT INTO skill_trees VALUES (0,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (10,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (18,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (25,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (31,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (38,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (44,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (49,1323,2,'Noblesse Blessing',0,1);
INSERT INTO skill_trees VALUES (53,1323,2,'Noblesse Blessing',0,1);

Умножает Шанс Дропа на 10 у всех РБ и Боссов, и ставит 100% где получилось больше 100%:
Код:

UPDATE droplist
SET chance =chance*10
WHERE mobId IN (SELECT id FROM npc WHERE type IN ("L2RaidBoss", "L2Boss"));

UPDATE droplist
SET chance=1000000
WHERE chance > 1000000;

UPDATE droplist
SET chance =chance*10
WHERE mobId IN (SELECT id FROM npc WHERE type IN ("L2RaidBoss", "L2Boss"));

UPDATE droplist
SET chance=1000000
WHERE chance > 1000000;

Увеличиваем М.Деф на Дино острове всем динозаврам:

Код:

 UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22196');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22197');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22198');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22199');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22200');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22201');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22202');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22203');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22204');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22205');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22208');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22209');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22210');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22211');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22212');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22213');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22214');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22215');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22216');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22217');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22218');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22219');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22220');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22221');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22222');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22223');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22224');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22225');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22226');
    UPDATE `npc` SET `mdef`='2000' WHERE (`id`='22227');

Эти зaпpocы пoнижaют физ.aтк. и м.aтк в 3 paзa, физ.зaщ. и м.зaщ. в 1.2 paз y Mиниoнoв:

Код:

UPDATE `npc` SET `patk` = `patk`*0.33 WHERE `type` = 'L2Minion';
UPDATE `npc` SET `matk` = `matk`*0.33 WHERE `type` = 'L2Minion';
UPDATE `npc` SET `pdef` = `pdef`*0.8 WHERE `type` = 'L2Minion';
UPDATE `npc` SET `mdef` = `mdef`*0.8 WHERE `type` = 'L2Minion';

UPDATE `npc` SET `patk` = `patk`*0.2 WHERE `type` = 'L2GrandBoss';
UPDATE `npc` SET `matk` = `matk`*0.2 WHERE `type` = 'L2GrandBoss';
UPDATE `npc` SET `pdef` = `pdef`*0.5 WHERE `type` = 'L2GrandBoss';
UPDATE `npc` SET `mdef` = `mdef`*0.5 WHERE `type` = 'L2GrandBoss';

Это запрос позволит вам изменить начальную точку появления всех чаров на нужную вам:

(это центр Диона)
Код:

UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='0');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='10');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='18');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='25');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='31');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='38');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='44');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='49');
UPDATE `char_templates` SET `x`='18821',`y`='145176',`z`='-3135' WHERE (`ClassId`='53');

(это центр Годдарда)

Код:

UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='0');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='10');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='18');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='25');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='31');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='38');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='44');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='49');
UPDATE `char_templates` SET `x`='147730',`y`='-56295',`z`='-2786' WHERE (`ClassId`='53');

Удаление всех аккаунтов с уровнем доступа 100
Код:

DELETE FROM `accounts` WHERE (`access_level`='100');

Шанс дропа аден 100% вместо 70%.

Код:

UPDATE `droplist` SET `droplist`.`chance`=1000000 WHERE `droplist`.`itemId`=57;

Делаем шанс дропа клановых яиц 100%

UPDATE droplist SET chance='1000000' WHERE ItemId in (8158, 8159, 8160, 8161, 8162, 8163, 8164, 8165, 8166, 8167, 8168, 8169, 8170, 8171, 8172, 8173, 8174, 8175, 8176);

Добавить скилл Return всем персонажам
Код:

INSERT INTO skill_trees VALUES (0, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (10, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (18, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (25, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (31, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (38, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (44, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (49, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (53, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (123, 1050, 2, "Return", 0, 1);
INSERT INTO skill_trees VALUES (124, 1050, 2, "Return", 0, 1);

Добавить все клановые скиллы определенному клану

Код:

INSERT INTO `clan_skills` VALUES
('ид_клана','370','3','Clan Vitality'),
('ид_клана','371','3','Clan Spirituality'),
('ид_клана','372','3','Clan Essence'),
('ид_клана','373','3','Clan Lifeblood'),
('ид_клана','374','3','Clan Morale'),
('ид_клана','375','3','Clan  Clarity'),
('ид_клана','376','3','Clan  Might'),
('ид_клана','377','3','Clan Aegis'),
('ид_клана','378','3','Clan Empowerment'),
('ид_клана','379','3','Clan Magic Protection'),
('ид_клана','380','3','Clan Guidance'),
('ид_клана','381','3','Clan Agility'),
('ид_клана','382','3','Clan Withstand-Attack'),
('ид_клана','383','3','Clan Shield Boost'),
('ид_клана','384','3','Clan Cyclonic Resistance'),
('ид_клана','385','3','Clan Magmatic Resistance'),
('ид_клана','386','3','Clan Fortitude'),
('ид_клана','387','3','Clan Freedom'),
('ид_клана','388','3','Clan Vigilance'),
('ид_клана','389','3','Clan March'),
('ид_клана','390','3','Clan Luck'),
('ид_клана','391','1','Clan Imperium');

Сделать телепорт бесплатным

UPDATE teleport SET price=0;

Добавить все клановые скиллы определенному клану

INSERT INTO `clan_skills` VALUES
('ид_клана','370','3','Clan Vitality'),
('ид_клана','371','3','Clan Spirituality'),
('ид_клана','372','3','Clan Essence'),
('ид_клана','373','3','Clan Lifeblood'),
('ид_клана','374','3','Clan Morale'),
('ид_клана','375','3','Clan  Clarity'),
('ид_клана','376','3','Clan  Might'),
('ид_клана','377','3','Clan Aegis'),
('ид_клана','378','3','Clan Empowerment'),
('ид_клана','379','3','Clan Magic Protection'),
('ид_клана','380','3','Clan Guidance'),
('ид_клана','381','3','Clan Agility'),
('ид_клана','382','3','Clan Withstand-Attack'),
('ид_клана','383','3','Clan Shield Boost'),
('ид_клана','384','3','Clan Cyclonic Resistance'),
('ид_клана','385','3','Clan Magmatic Resistance'),
('ид_клана','386','3','Clan Fortitude'),
('ид_клана','387','3','Clan Freedom'),
('ид_клана','388','3','Clan Vigilance'),
('ид_клана','389','3','Clan March'),
('ид_клана','390','3','Clan Luck'),
('ид_клана','391','1','Clan Imperium');