Интересные и полезные запросы MySQL

Ни для кого не секрет, что одним из популярных языков запросов для баз данных является MySQL. Это свободная реляционная система управления базами данных, разработанная Oracle. Здесь не буду описывать все преимущества или недостатки данной СУБД (при желании прочтете самостоятельно в сети Интернет).

Сегодня поговорим об интересных и полезных запросах в MySQL.

Получение данных объединенных полей.

SELECT CONCAT(fio,' - ',phone) AS contact FROM `table` WHERE `id`='13'

В результате выполнения запроса мы получим информацию в виде нового поля contact, которое является объединением двух полей: fio и phone. Для удобства эти поля разделены дефисом.

Удаление дубликатов в таблице.

ALTER IGNORE TABLE `table` ADD UNIQUE (`fio`, `email`);

В результате выполнения запроса будут автоматически удалены все дубликаты записей (будет создан уникальный ключ для полей fio и email).

Преобразование строки в число.

SELECT '1abc2d' + '3efg4h';
SELECT '1abc2d' - '3efg4h';
SELECT '1abc2d' * '3efg4h';
SELECT '1abc2d' / '3efg4h';

Здесь приведено сразу 4 математических операции. В результате выполнения любого из этих запросов происходит преобразование строки в число. Фактически, учитывается только первый символ в подстроке. Далее, в результате математической операции строка преобразуется в число. Данные запросы для общего понимания.

А вот и  сами результаты запросов:

4
-2
3
0,3333333333333333

Выбор записей, которые повторяются определенное количество раз.

SELECT `fio`, `country` FROM `table` GROUP BY `country` HAVING COUNT(*) = 2;

В результате выполнения данного запроса будут выведены все записи с полями fio и country, для которых поле country встречается 2 раза. Таким образом легко получить повторяющиеся значения (значения, встречающиеся n-раз).

Событие для удаления записей таблицы каждый час.

SET GLOBAL `event_scheduler` = ON;
CREATE EVENT `hourly_event`
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM `table`;

Здесь самой первой строчкой включаем планировщик событий. Далее — создается событие с именем hourly_event, которое содержит запрос на удаление всех данных из таблицы table. Указанное событие выполняется каждый час.

Просмотр активных событий в планировщике.

SHOW EVENTS WHERE STATUS='ENABLED'

Удалить неактивные учетные записи, которым свыше трех дней.

SET GLOBAL `event_scheduler` = ON;
CREATE EVENT `not_active_users`
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM `users`
WHERE `status` = '0', UNIX_TIMESTAMP(`date`) >= UNIX_TIMESTAMP(NOW()) - (3 * 24 * 3600);

Кстати, если захотите изменить временной интервал для данного события, выполните следующий запрос:

ALTER EVENT `not_active_users` ON SCHEDULE EVERY 8 HOUR;

Этот запрос изменит интервал выполнения события not_active_users на каждые 8 часов.

Архивирование таблицы с помощью планировщика событий.

CREATE PROCEDURE `archive_table`()
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
  SET @tbl_archive=CONCAT('log_', DATE_FORMAT(CURRENT_TIMESTAMP, '%Y%m%d_%H%i'));
  SET @archive_query:=CONCAT('CREATE TABLE ', @tbl_archive, ' ENGINE=ARCHIVE AS (SELECT * FROM `table`)');

  PREPARE archive_query FROM @archive_query;
  EXECUTE archive_query;
  DEALLOCATE PREPARE archive_query;

  DELETE FROM `table`;
END;

CREATE EVENT `archive_event`
  ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP
  ON COMPLETION NOT PRESERVE
  ENABLE
  DO
call archive_table();

В начале создается процедура с именем archive_table. В указанной процедуре создается временная таблица (имя таблицы с определенным форматом), в которую копируются данные. После, исходная таблица table с данными очищается. Затем создается процедура archive_event. Эта процедура будет выполняться 1 раз в неделю, начиная с текущего момента времени.

Возможно, по каким-то причинам у вас не сработают данные запросы. Нужно будет смотреть документацию, если где-то не будет хватать привилегий доступа.

Получить название поля, которое является первичным ключом в таблице.

SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE `TABLE_NAME` = 'table' AND `COLUMN_KEY` = 'PRI';

В моем случае в результате выполнения запроса было возвращено значение: id.

Получить размер базы данных MySQL.

SELECT `table_schema` AS `Db name`,
ROUND(SUM(`data_length` + `index_length`) / 1024 / 1024, 3) AS 'Db size(MB)'
FROM `information_schema`.`tables`
GROUP BY `table_schema` 
HAVING `table_schema`='test';

Получение размера базы данных с именем test.

Определить количество слов в столбце.

SELECT LENGTH(`fio`) - LENGTH(REPLACE(`fio`, ' ', '')) + 1 AS `words_count` FROM `table`;

Копирование таблиц с одинаковой структурой данных.

INSERT IGNORE INTO `Table_New` SELECT * FROM `Table_Old`;

Получить даты создания записей, которым более 30 дней.

SELECT DATE_FORMAT(`Created`, '%d-%m-%Y') FROM `Table` WHERE `Created` <= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) GROUP BY DATE_FORMAT(`Created`, '%d-%m-%Y');

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

Удаление записей, которым более 60 дней.

DELETE FROM `Table` WHERE `Created` <= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY);

Поиск значения в таблице с исключением некоторых символов.

SELECT * FROM `Table` WHERE `name` LIKE 'А-432.10' OR REPLACE(REPLACE(REPLACE(`name`, ' ', ''), '.', ''), '-', '') LIKE '%А43210%';

Возможность поиска через оператор LIKE, а также удаление некоторых символов из искомого значения.

Изменение структуры таблицы.

ALTER TABLE `Table` ADD COLUMN `Phone` VARCHAR(15) AFTER `FIO`; # Добавление нового столбца перед стобцом FIO
ALTER TABLE `Table` ADD INDEX `Phone` (`Phone`); # Добавление обычного индекса для столбца
ALTER TABLE `Table` MODIFY COLUMN `Phone` VARCHAR(15) NOT NULL; # Изменение структуры столбца
ALTER TABLE `Table` DROP `Phone`; # Удаление столбца вместе с индексами

Удаление дубликатов по определенному полю.

DELETE `Table` FROM `Table` LEFT OUTER JOIN (SELECT MIN(`Id`) AS `id`, `UserId` FROM `Table` GROUP BY `UserId`) AS `tmp` ON `Table`.`Id` = `tmp`.`id` WHERE `tmp`.`id` IS NULL;

В примере, поиск дубликатов производится по полю UserId.

Проверка определенных полей на одновременное заполнение значениями (должно быть заполнено только одно поле).

SELECT `id`, `sale`, `new`, `hit`, `discont` FROM `Table` 
WHERE 
`sale`='1' AND `sale` IN(`new`, `hit`, `discont`) OR
`new`='1' AND `new` IN(`sale`, `hit`, `discont`) OR
`hit`='1' AND `hit` IN(`sale`, `new`, `discont`) OR
`discont`='1' AND `discont` IN(`sale`, `new`, `hit`);

Обновление данных, используя другую таблицу в качестве источника.

UPDATE Table1, Table2 SET Table1.`ItemId` = Table2.`ItemId` WHERE Table1.`id` = Table2.`id`;

Сгруппировать по полю и вывести для каждого пользователя его Ip-адрес.

SELECT `UserId`, GROUP_CONCAT(`Ip`) FROM `Table` GROUP BY `UserId`;

Удалить все записи, оставив 20 последних новых записей.

DELETE FROM `Table` WHERE `Created` NOT IN (SELECT `Created` FROM (SELECT `Created` FROM `Table` GROUP BY `Created` ORDER BY `Created` DESC LIMIT 20) AS tbl);

Сгруппировать по двум таблицам и вывести по одному самому первому пользователю (по дате захода).

SELECT m1.`UserId`, u.`clientManagerID`, m1.`IP`, DATE_FORMAT(m1.`Created`, '%d.%m.%Y %H:%i') AS Created
FROM `Table` AS m1 
LEFT JOIN `User` AS u ON m1.`UserId` = u.`UserId` 
WHERE m1.`Created` = (SELECT MIN(m2.`Created`) FROM `Table` AS m2 WHERE m1.`UserId` = m2.`UserId`);

Удалить запятую в конце поля.

UPDATE `Table` SET `field`=TRIM(BOTH ',' FROM `field`) WHERE `UserId`='12'

Пример запроса для обновление аналогов с удалением заданного аналога.

UPDATE `Table` SET `analog`=TRIM(BOTH ',' FROM REPLACE(REPLACE(`analog`, 'A-0014', ''), ',,', ',')) WHERE `id`='1789';

В данном примере аналоги хранятся в поле просто через запятую.

Пример с подзапросом в операторе LIKE.

SELECT `analog` FROM `Table` WHERE `analog` LIKE CONCAT('%', (SELECT `ItemId` FROM `Table` WHERE `id`='112'), '%');

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

SELECT DISTINCT IF(INSTR(a.`Title`, ' ') > 0, LEFT(a.`Title`, INSTR(a.`Title`, ' ') - 1), a.`Title`) AS Title
FROM `Table` AS a
LEFT JOIN `Section` AS s ON a.`id` = s.`id` 
WHERE s.`parentId` = '1518';

Вывести расхождения в двух таблицах.

SELECT Table1.id, Table2.id FROM Table1
LEFT JOIN Table2 ON Table1.id = Table2.id 
WHERE Table2.id IS NULL
UNION ALL
SELECT Table1.id, Table2.id FROM Table2 
LEFT JOIN Table1 ON Table1.id = Table2.id 
WHERE Table1.id IS NULL;

Сгруппировать по имени дочернего раздела и вывести все имена его родителей.

SELECT a.`Title`, GROUP_CONCAT((SELECT `Title` FROM `Section` WHERE `id`=a.`parentId`))
FROM `Section` AS a
WHERE a.`Id1C` IS NOT NULL AND a.`parentId` != '43'
GROUP BY a.`Title`;

Сортировка по приоритету, когда в поле есть null значения.

SELECT `id`, `Title`, `parentId` 
FROM `Table`
WHERE `Id1C` IS NOT NULL
ORDER BY -`Priority` DESC, `Title`;

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

SELECT DISTINCT(SUBSTR(`Title`, 1, 1)) AS letter FROM `Table`;

Вот такой получился сборник некоторых интересных, для кого-то не особо smile запросов. На этом не собираюсь останавливаться. По возможности буду размещать новые запросы MySQL. Кстати, разместил тестовые данные на github.

Спасибо за внимание.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Добавить комментарий

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам: