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

09 мая 2017, 16:11

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

  1. UPDATE `content` SET `created_at` = (
  2. 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' )
  3. );

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

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

И после выполнения не забываем у поля created_at поменять тип на datetime. Теперь зальём CSV файл и переименуем его:

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

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

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

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

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

Чтобы удалить пробелы в начале и в конце строк в MySQL, можно использовать функцию TRIM(). Вот пример, как это сделать для поля name в таблице sone_articles базы данных:

  1. UPDATE sone_articles SET `name` = TRIM(`name`);

Этот запрос удалит все пробелы в начале и в конце строк в поле Firma.

Если вам нужно удалить пробелы в начале (LEADING) или в конце (TRAILING) отдельно, вы можете использовать функции TRIM(LEADING) и TRIM(TRAILING) соответственно. Вот примеры:

Удаление пробелов только в начале строки:

  1. UPDATE sone_articles SET `name` = TRIM(LEADING ' ' FROM `name`);

Удаление пробелов только в конце строки:

  1. UPDATE sone_articles SET `name` = TRIM(TRAILING ' ' FROM `name`);

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

  1. 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:

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

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

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

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

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

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

  1. DELETE FROM `book_genre` WHERE `genre` IS NULL

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

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

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

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

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

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

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

  1. SELECT DISTINCT `genre` FROM `book_genre`

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

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

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

  1. UPDATE book_genre t1 JOIN sone_genres t2 ON TRIM(t1.genre) == TRIM(t2.name) SET t1.genre_id = t2.id

Преображаем тип данных datetime в unix time:

  1. UPDATE sone_articles SET publishedon=UNIX_TIMESTAMP(STR_TO_DATE(published_at, '%Y-%m-%d %H:%i:%s'));

Очистка таблицы:

  1. TRUNCATE TABLE table_name;

Сброс AUTO_INCREMENT:

  1. ALTER TABLE table_name AUTO_INCREMENT = 1

Как сбросить auto_increment

SSH

Импорт дампа в базу данных:

  1. mysql -h localhost -P 3306 -u root -p database < dump.sql

Экспорт дампа базы данных:

  1. mysqldump -h localhost -P 3306 -u root -p database --no-tablespaces > dump.sql
MODX.ONE
1    3794    0
+1

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

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