Полезные Sql Запросы

Собери свой Сервак!

Fuck Your Brains :)

[ Новые сообщения · Участники · Правила форума · Поиск · RSS ]

Страница 1 из 1
1

создатель

Дата: Воскресенье, 12.06.2011, 12:48 | Сообщение # 1

Рядовой

Группа: Администраторы

Сообщений: 14

Репутация: 1

Статус: Offline

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

Code

DELETE * FROM items WHERE item_id=(ид того что удалить) AND owner_id=

(ИД перса, у когорого удалить)

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

Code

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

Показ всех вещей определённого чара:
Код:

Code

mysql> select a.name,"armor",a.crystal_type,i.count,i.enchant_level from armor a, items i where a.item_id=i.item_id and i.owner_id=идчара union select a.name,"weapon",a.crystal_type,i.count,i.enchant_level from weapon a, items i where a.item_id=i.item_id and i.owner_id=идчара union select a.name,"some",a.crystal_type,i.count,i.enchant_level from etcitem a, items i where a.item_id=i.item_id and i.owner_id=идчара;

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

Code

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

Дропаются только адены:
Код:

Code

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

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

Code

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

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

Code

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

удаляем нпц (в том числе мобов) выше 51 лвл с респа (не удаляя их с сервака). ЛВЛ моба выставляет в конце запросов.
Код:

Code

DELETE spawnlist FROM spawnlist, npc WHERE spawnlist.npc_templateid=npc.idTemplate AND npc.level<51;

удаляем с респа только мобов свыше 51 лвл:
Код:

Code

DELETE spawnlist FROM spawnlist, npc WHERE spawnlist.npc_templateid=npc.idTemplate AND npc.level<51 AND npc.type="L2Monster";

удалить с респа и удалить с сервака мобов свыше 51 лвл:
Код:

Code

DELETE spawnlist, npc FROM spawnlist, npc WHERE spawnlist.npc_templateid=npc.idTemplate AND npc.level<51 AND npc.type="L2Monster";

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

Code

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

Удаление дропа ресурсов со всех мобов. Он безопасен, удаляет дроп варнишей, молдов, скинов и прочего
Код:

Code

DELETE FROM droplist WHERE itemId='1864';
DELETE FROM droplist WHERE itemId='1865';
DELETE FROM droplist WHERE itemId='1866';
DELETE FROM droplist WHERE itemId='1867';
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='1883';
DELETE FROM droplist WHERE itemId='1884';
DELETE FROM droplist WHERE itemId='1885';
DELETE FROM droplist WHERE itemId='1886';
DELETE FROM droplist WHERE itemId='1887';
DELETE FROM droplist WHERE itemId='1888';
DELETE FROM droplist WHERE itemId='1889';
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='1894';
DELETE FROM droplist WHERE itemId='1895';

Все наши статьи о создании серверов проверены и работают.Если у вас , что-то не получилось, или не запускается какой-то сервер , вылезают ошибки - пишите на форум в раздел. "Моя Криворукость" , Мы Вам ОТВЕТИМ И ПОМОЖЕМ :)

Полезные Sql Запросы

Страница 1 из 1
1