Sql запросы

[FreeBSD]

Делимся sql запросами.

Пак запросов от LLIbIcpEP на чистку базы:

#лишние акки
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

Запрос аккаунты на которых нет персонажей:

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

Запрос удаляет вещь у всех персонажей(В место entry ставим ид пердмета):

DELETE FROM `character_inventory` WHERE `item_template` IN ('entry');

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

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

P.s Выкладываем свои запросы в базу которыми вы пользуетесь.