Ru-MaNGOS - Sql запросы

Ru-MaNGOS (http://mangos.ytdb.ru/index.php)

- Языки программирования (http://mangos.ytdb.ru/forumdisplay.php?f=34)

- - Sql запросы (http://mangos.ytdb.ru/showthread.php?t=262)

Sql запросы

Делимся 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

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

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

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

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

поправьте ошибку:
('entry'');

'id_вещи';

Выводит людей, сделавших определённый квест

PHP код:


		
			
SELECT 

    cc.name, cc.level, a.username 

FROM 

    characters.`character_queststatus` cq 

INNER JOIN 

    characters.characters cc 

ON 

    cc.guid = cq.guid 

INNER JOIN 

    realmd.account a 

ON 

    a.id = cc.account 

WHERE 

    quest = xxx;

Когда выполняли квест

PHP код:


		
			
SELECT 

    cc.name, cc.level, a.username, from_unixtime(cq.time)

FROM 

    characters.`character_queststatus_daily` cq

INNER JOIN 

    characters.characters cc ON cc.guid = cq.guid

INNER JOIN 

    realmd.account a ON a.id = cc.account

WHERE 

    cq.quest = xxx

ORDER BY 

    from_unixtime(cq.time) DESC

LIMIT 0 , 300;

Выводит гм онлайн

PHP код:


		
			
SELECT 

    c.name, c.class, c.race, a.gmlevel 

FROM 

    characters.characters c 

INNER JOIN 

    realmd.account a 

ON 

    a.id = c.account 

WHERE 

    a.gmlevel != 0 && c.online = 1;

Убедительная просьба:
Если выкладываете участок кода (как пример) пожалуйста не пишите его 1 строкой,
тот кому он понадобится, должен в первую очередь понимать что и к чему, а не тупо его копировать (во избежания пустых вопросов) .
Желательно делать комментарии в самом коде, поверьте толку от такого примера будет куда больше.

Нужны запросы:

1)Удаление персонажей которые не привязаны к аккаунту.
2)удаление всех лишних записей после удаления персонажей.

PHP код:


		
			
DELETE FROM 

    `characters`.`characters` 

WHERE 

    `account` NOT IN (

    SELECT id 

        FROM `realmd`.`account`

    );

2. а вообще вот

Код:

ERROR 1206 : The total number of locks exceeds the lock table size

как избавиться от такой ошибки...? может можно сделать количество запросов чтоб не превышало..?

Вот на этих запросах выдает:

Код:

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;

Как решение нашел увеличить размер innodb_buffer_pool_size в /etc/my.cnf

Тип обоих таблиц InnoDB, и если вы прочли статью на утдб, то там написано:

Цитата:

запросы пройдут только на mysql 5.1 и выше, и актуальны только для MyISAM

Так что решайте сами, что делать.

Просьба к автору первого поста - собирать всё в первый пост и, желательно, под спойлер (на этом форуме его функцию выполняет тэг оффтопа), над спойлером краткое описание - что там.

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

Код:

#Выборка

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

Старое помойму

Новое:

Код:

#Выборка

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

Спасибо, исправил.

подскажите, как запросом сделать все логины(аккаунты) в базе большим регистром? Например был plauer, после запроса станет PLAUER.

Код:

UPDATE realmd.account SET username = UPPER(username)

p.s. чтобы остальные не задавали подобных вопросов: изменяет регистр всех записей в выбранном поле.

Не коректное! Много мусора останется!

Код:

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

Коректное!

Код:

DELETE FROM `item_instance` WHERE `guid` in (SELECT item FROM `character_inventory` WHERE item_template IN ('entry');

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

вайпаем шмот, с инвенторя, гб, банка, почты итд

Код:

DELETE FROM item_instance WHERE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 4), ' ', -1) AS UNSIGNED)=номер шмотки;

DELETE FROM item_instance WHERE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 4), ' ', -1) AS UNSIGNED)=номер шмотки;

DELETE FROM `auctionhouse` where `itemguid` not in (SELECT `guid` FROM `item_instance`);

DELETE FROM `character_inventory` where `item` not in (SELECT `guid` FROM `item_instance`);

DELETE FROM `guild_bank_item` where `item_guid` not in (SELECT `guid` FROM `item_instance`);

DELETE FROM `mail_items` where `item_guid` not in (SELECT `guid` FROM `item_instance`);

DELETE FROM `character_gifts` where `item_guid` not in (SELECT `guid` FROM `item_instance`);

удаляем мусор от удалённых чаров

Код:

DELETE FROM `item_instance` WHERE `guid` NOT IN (SELECT `itemguid` FROM `auctionhouse`) AND `guid` NOT IN (SELECT `item_guid` FROM `character_gifts`) AND `guid` NOT IN (SELECT `item` FROM `character_inventory`) AND `guid` NOT IN (SELECT `item_guid` FROM `guild_bank_item`) AND `guid` NOT IN (SELECT `item_guid` FROM `mail_items`);

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

выполнил я его и как по волшебной палочке сервер крашиться каждые 4-5 минут ,ну в общем постоянно.
хотя посмотрел и выглядит он на отлично.

а догадаться мы сами должны почему, так видимо?
логи где?
крашдампы где?

Есть некая таблица

id | sessionID | somethingID | time | ip

Данные заносятся при каждом посещении пользователем определенной страницы. Простой insert.
Раз в N мне необходимо считывать и обрабатывать информацию.
Случаются вот такие ситуации:
1 session123 666 time 127.0.0.1
2 session222 666 time 127.0.0.1

Можно ли как-то удалить подобные дубликаты? Из этой строки, я бы хотел оставить либо первый, либо второй вариант.

Т.е. убрать дубли по sessionID-somethingID и ip-somethingID.

Цитата:

Сообщение от DeusModus (Сообщение 3387)

может имеет смысл создать в таблице уникальные сложные ключи (sessionID, somethingID) и/или (ip, somethingID) - в этом случае вставить одинаковые значения пары (sessionID, somethingID) и/или (ip, somethingID) не получится, а значит не придется удалять эти дубликаты.
или требуется удалить уже существующие дубликаты?

судя по всему - это статистика. В статистике невозможно поставить уникальные значения на поля.

Цитата:

Сообщение от ghostpast (Сообщение 3394)

Да, в таблице уже 20-30 тысяч записей.

Создать временную таблицу, скопировать distinct записи и потом сделать truncate нашей таблице и переписать данные с временной таблицы.

Цитата:

Сообщение от Konctantin (Сообщение 3427)

id | sessionID | somethingID | time | ip
Ни по одному из этих столбцов нельзя выбрать только DISTINCT сохраняя корректность данных.
Да и смысл в том, что для каждого пользователя присутствует несколько записей. Удалить же нужно только дублирующие по смыслу.
Или я что-то не понял?

помогите из этого запроса

Код:

SELECT * FROM `character_inventory` WHERE (`item_template`=xxx);

сделать чтоб выводил ник персонажа.

во, сделал, только не знаю правильно или нет, но работает=)))

Код:

SELECT

    `characters`.`name`

  FROM `characters`, `character_inventory`

  WHERE `characters`.`guid` = `character_inventory`.`guid` AND `character_inventory`.`item_template` = 'xxx';

Цитата:

Сообщение от FreeBSD (Сообщение 3580)

во, сделал, только не знаю правильно или нет, но работает=)))

Код:

SELECT

    `characters`.`name`

  FROM `characters`, `character_inventory`

  WHERE `characters`.`guid` = `character_inventory`.`guid` AND `character_inventory`.`item_template` = 'xxx';

ну тогда уж лучше так

Код:

select c.name

from characters c

        join character_inventory ci on ci.guid = c.guid

where ci.item_template = 23345;

Цитата:

Сообщение от DeusModus (Сообщение 3387)

Получить последние записи из дублей можно так:

если считать что запись в таблице можно определить по полям somethingID, ip

Код:

select max(id)

from sometab t

group by somethingID, ip

having count(id) > 1

Народ, извиняюсь, может быть не там вопрос задаю.

Работаю с Личным Кабинетом. Необходимо создать чара в базе. Но нет возможности "завести" сервер.

Если это возможно, можете привести sql запрос на создание в базе персонажа 1 лвл? Благодарю заранее.

Персонажей лучше создовать через игру, в противном случая надо строго копировать методику создания и писать какой-то скрипт или нечто похожее. Иначе ничего не получиться.

ЗЫ А какой смысл создавать перса, если вы не можете поднять сервер?

У меня есть вопрос. Вот например есть такой запрос

SELECT * FROM `honor` WHERE `kills` >0

А как сделать так чтоб сначала проверялось наличие таблицы honor, и только потом осуществлялась выборка из неё. Пробовал что-то вроде

IF EXISTS (SELECT * FROM `honor` WHERE `kills` >0)

но так не работает.

Цитата:

Сообщение от VinD (Сообщение 5970)

У меня есть вопрос. Вот например есть такой запрос

SELECT * FROM `honor` WHERE `kills` >0

IF EXISTS (SELECT * FROM `honor` WHERE `kills` >0)

но так не работает.

Код:

SELECT COUNT(*)

FROM information_schema.tables 

WHERE table_schema = 'mangos' 

AND table_name = 'creature';

если результат 0, таблицы creature нет =)))

Как сделать ограничение на ввод цифр? типа от 2 до 5, от 1 до 3 и т.п.?

Цитата:

Сообщение от Incognito (Сообщение 6072)

Как сделать ограничение на ввод цифр? типа от 2 до 5, от 1 до 3 и т.п.?

тип SET или ENUM вполне подходит...хотя все зависит от тз. Желательно такое до попадания в базу учитывать....:mda:

Суть проблемы: есть необходимость с сайта отправить почту игроку 123 с итемом, ну допустим,ИД 123456 и количество 15 штук..... ну и текстом... "здраствуй вася"
чет еще смутно понимаю как почта работает :(
подсобите плз

запросом в базу это не как не сделать, если ток через telnet... есть голосовалки для пабликов, отдуда повыдирай нужные коды и будет тебе счастье)

хм.. телнет чтож и на тм спс. щя бу чет думать.

Цитата:

Сообщение от FreeBSD (Сообщение 6447)

Разве? Насколько помню это было реализовано в MMFPM (мини менеджер фор чего то там мангос), так что это сделать можно, только запрос будет не маленький и придется заполнять 2 таблицы и не 1 раз.

Сделать через запрос возможно, но слишком много возни, большой риск уронить сервер. ИМХО telnet в Мангосе есть, по этому юзай telnet. Тем более там буквально несколько строк когда нужно чтобы отправить самую заковыристую команду. Веселее всего шутдаун через сайт ставить :)

С телнетом через сторонние либы не возился, и думаю там много подводных камней.
Но есть же SOAP специально прикрученный для этого дела.
Для Php рабочий пример лежит в сорцах мангоса mangos/contrib/soap/example.php
нужно только поставить\включить библиотеку.
Для питона пример работы был описан тут в блоге.

Выводит список вещей, которые не привязаны к письму.

PHP код:


		
			
SELECT * FROM `mail_items` where `mail_id` not in (select `id` from `mail`);

Отправляет вещи игроку, которые не привязаны к письму.

PHP код:


		
			
INSERT 

INTO `mail` 

SELECT `mail_id`, '0', '61', '0', '0', `receiver`, 'System repaired!', 'Message generate System!', '1', unix_timestamp() + 3600 * 24 * 30, unix_timestamp(), '0', '0', '0'

FROM `mail_items` 

WHERE `mail_id` not in (SELECT `id` FROM `mail`)

GROUP BY `mail_id`

Подскажите пожалуйста запрос который выводил бы список персонажей и их ip