Интересные и полезные запросы 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';