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

09 мая 2017, 18:11

Решил привести в пример пару 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
Преображаем тип данных datetime в unix time:

UPDATE sone_articles SET publishedon=UNIX_TIMESTAMP(STR_TO_DATE(published_at, '%Y-%m-%d %H:%i:%s'));
Андрей Копп
1    375    0
+1

Комментарии ()

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

    Топики

    Андрей Копп 03 октября 2018, 11:55
    Компоненты MODX Обзор компонента SEO Pro 0
    Андрей Копп 02 октября 2018, 11:18
    MODX Настройка MySQL 5.7 для работы pdoTools 0
    Андрей Копп 27 сентября 2018, 20:49
    MODX MinifyX в Fenom 0
    Андрей Копп 01 июля 2018, 15:13
    Другое Fenom в MODX (шпаргалки) 0

    Комментарии

    Андрей Копп 02 октября 2018, 09:04
    ComboBox с данными из другой таблицы modExtra 1
    Андрей Копп 08 сентября 2017, 20:24
    Всевозможная очистка кэша в Laravel 5 1