Лайфхаки SQL. Команды SQL для автоматизирования работы / MySQL / Программирование

Лайфхаки SQL. Команды SQL для автоматизирования работы

Решил привести в пример пару SQL запросов с помощью которых массово можно менять даты, копировать из одного поля в другое и прочие хитрости. Пишу кратко и по делу.

Для того чтобы нам рандомно поменять даты в выбранном периоде в *nix формате достаточно в нашей таблице `content` добавить поле created_at с типом integer и выставить значение 10. После же выполняем команду:

UPDATE `content` SET `created_at` = (
	RAND( ) * ( UNIX_TIMESTAMP( '2017-05-09 23:59:59' ) - UNIX_TIMESTAMP( '2015-11-07 23:19:23' ) ) + UNIX_TIMESTAMP( '2015-11-04 00:00:00' )
);


Чтобы дату из *nix формата перевести в datetime, во первых нужно тип поля created_at перевести в тип text и потом достаточно выполнить команду:

UPDATE `content` SET `created_at`=DATE_FORMAT( FROM_UNIXTIME(`created_at`) , '%Y-%m-%d %T' )

И после выполнения не забываем у поля `created_at` поменять тип на datetime.

Теперь зальём CSV файл и переименуем его:

RENAME TABLE `table_new`.`TABLE 22` TO `table_new`.`images`;

Теперь давайте рассмотрим как можно из одной таблицы MySQL перенести данные в другую таблицу исходя из общих черт. В нашем случае в двух таблицах у нас имеются одинаковые ключевые слова, а нам нужно из перенести пути картинок. В первую очередь нужно изменить путь с files/8908097097086.jpg на uploads/images/8908097097086.jpg, для этого выполняем команду:

UPDATE `images` SET `preview` = REPLACE(`preview`, "images/", "uploads/images/");

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

UPDATE content b, images a
SET b.image=a.preview
WHERE a.keys=b.keywords;

И следующим шагом мы добавим данные из одной таблицы в другую поле в поле:

INSERT INTO `sone_articles`(`category_id`, `title`, `description`, `keywords`, `slug`, `introtext`, `content`, `image`, `date`, `created_at`, `updated_at`) SELECT '1', `title`, `description`, `keywords`, `alias`, `description`, `content`, `image`, `created_at`, `created_at`, `updated_at` FROM `content`;

Удаляем пустые строки из таблицы импортированной через CSV со значением NULL у столбца COL 3:

DELETE FROM `TABLE 26` WHERE `COL 3` IS NULL;

Рандомное обновление числового поля:

UPDATE `sone_articles` SET `view_count`=RAND( ) * (10000 - 673 ) + 568

Бывают случаи когда при парсинге начинают генерится картинки с названиями типа none_1.png, none_2.png, none_3.png и т.д. Для того чтобы изменить массово нужно использовать команду:

UPDATE `sone_articles` SET image = 'uploads/images/none.png' WHERE image LIKE 'uploads/images/none\_%'

Удаление пустых строк, если у поля стоит значение NULL:

DELETE FROM `book_genre` WHERE `genre` IS NULL

Удаление пустых строк, если не стоит ничего:

DELETE FROM `book_genre` WHERE `genre` = ''

Удаление дублей записей в таблице:

DELETE t1 FROM `sone_books` t1, `sone_books` t2 WHERE t1.genre=t2.genre AND t1.id < t2.id

Обновление таблиц с JOIN:

UPDATE sone_books t1 JOIN sone_authors t2 ON t1.author = t2.author_name SET t1.author_id = t2.id

Выборка всех жанров в единственном экземпляре:

SELECT DISTINCT `genre` FROM `book_genre`

Удаление или добавление чего-то в начале и конце поля:

UPDATE `table` SET `column` = CONCAT( 'чего-то спереди ', `column` )

Сверка строк без пробелов. Используем TRIM:

UPDATE book_genre t1 JOIN sone_genres t2 ON TRIM(t1.genre) == TRIM(t2.name) SET t1.genre_id = t2.id
09 мая 2017, 18:11    Андрей Копп MySQL 1    352 +1

Отзывы ()

    Вы должны авторизоваться, чтобы оставлять комментарии.