Слава Україні!

MySQL: Полезные запросы

Запросы MySQL облегчающие работу
17-го березня 2019, 21:28

Для примера есть две таблицы - "catalogs" и "products". Установлена связь "один-ко-многим". "Catalog" является родительской таблицей по отношению к таблице "products".

tables

TRUNCATE

Необходимо очистить таблицы. И если таблица "products" чистится без проблем:


TRUNCATE table `products`;
 

то таблицу "catalogs" так просто не очистить:


TRUNCATE table `catalogs`;
/* Ошибка SQL (1701):
    Cannot truncate a table referenced in a foreign key constraint
    (`db`.`products`, CONSTRAINT `FK__catalogs`) */
 

Решение простое - временно отключить проверку внешних ключей


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table `catalogs`;
SET FOREIGN_KEY_CHECKS = 1;
 

Поиск дубликатов

Необходимо проверить таблицы на наличие повторяющихся названий. Тут поможет оператор HAVING


SELECT catalog_name, COUNT(*) amount
FROM `catalogs`
GROUP BY catalog_name
HAVING amount > 1;

+--------------+--------+
| catalog_name | amount |
+--------------+--------+
| Phones       |      2 |
+--------------+--------+
1 row in set (0.00 sec)
 

Удаление связанных данных

Удалить каталоги, в которых отсутствуют товары

Проверим, есть ли такие каталоги


SELECT
    c.id,
    c.catalog_name,
    p.product_name
FROM catalogs c
LEFT JOIN products p ON p.catalog_id = c.id;
+----+--------------+--------------+
| id | catalog_name | product_name |
+----+--------------+--------------+
|  1 | Phones       | Xiaomi       |
|  2 | Phones       | Apple        |
|  2 | Phones       | Samsung      |
|  3 | Notebooks    | NULL         |
+----+--------------+--------------+
4 rows in set (0.00 sec)
 

Удаляем только каталоги:


DELETE `c` FROM catalogs c
LEFT JOIN products p ON p.catalog_id = c.id
WHERE p.id IS NULL;

Удалить каталог "Notebooks" вместе с товарами:


DELETE `c`, `p` FROM catalogs c
LEFT JOIN products p ON p.catalog_id = c.id
WHERE c.id = 3;

Удалить товары из каталога "Notebooks":


DELETE `p` FROM catalogs c
LEFT JOIN products p ON p.catalog_id = c.id
WHERE c.id = 3;

Преобразование дат

Периодически сталкиваюсь с тем, что различные приложения сохраняют дату в различном формате. Например в формате UNIXTIME дата выглядит как число. Например дата 2019-03-17 15:46:55 будет выглядеть как 1552826815. Это очень удобно для операций с датой, но абсолютно нечитаемо. Здесь поможет функция FROM_UNIXTIME()

Отображение даты в читаемом формате


SELECT *,  FROM_UNIXTIME(created_at) date FROM catalogs;
+----+--------------+------------+---------------------+
| id | catalog_name | created_at | date                |
+----+--------------+------------+---------------------+
|  1 | Phones       | 1430816400 | 2015-05-05 12:00:00 |
|  2 | Phones       | 1431268215 | 2015-05-10 17:30:15 |
|  3 | Notebooks    | 1431268215 | 2015-05-10 17:30:15 |
+----+--------------+------------+---------------------+
3 rows in set (0.00 sec)

Преобразование даты в формат unix:


SELECT UNIX_TIMESTAMP(STR_TO_DATE('2019-03-17 15:46:55', '%Y-%m-%d %H:%i:%s')) unix_time;
+------------+
| unix_time  |
+------------+
| 1552826815 |
+------------+
1 row in set (0.00 sec)

Важлива інформація

Міністерство оборони
України
з 24.02 по 07.02
втрати противника
орієнтовно склали:
846650 ( +1340 ) особового складу
9975 ( +10 ) танків
20755 ( +18 ) бойових бронемашин
22785 ( +32 ) артилерійських систем
1271 ( +0 ) РСЗВ
1056 ( +1 ) засоби ППО
369 ( +0 ) літаків
331 ( +0 ) гелікоптерів
36307 ( +96 ) автомобільної техніки
28 ( +0 ) кораблі / катери
3054 ( +0 ) крилаті ракети
24301 ( +116 ) БПЛА
3738 ( +1 ) спец. техніки
4 ( +0 ) установок ОТРК/ТРК
1 ( +0 ) підводні човни