Заметки SQL

Как получить полный SQL запрос для копирования таблицы

Выполните запрос:

SHOW CREATE TABLE `My_database`.`my_table`

Для того чтобы развернуть весь код щелкните: +Options -> Choose Full texts -> Click on Go

Как автоматически заполнить строки столбца в БД, в порядке возрастания?

SET @n = 0;
UPDATE dest_table SET id = (@n := @n + 1) ;

Как обновить поля столбца случайными числами:

UPDATE `my_table` SET id=FLOOR(RAND()*10000)

Как в PDO получить тип каждой колонки?

В PDO существует метод $sth->getColumnMeta($column_index).
Но у него есть проблема в том что возвращает в поле native_type всякую ерунду, типа: LONG, VAR_STRING - которые не соответсвуют стандарту MySQL.
Поэтому мы воспользуемся SQL оператором DESCRIBE:

DESCRIBE source_table

Еще один вариант:

SHOW COLUMNS FROM source_table

Как найти строку во всех таблицах SQL?

Для таблиц-вообще легко:

select * into #temp from sys.tables

потом курсором

select * from where m=n

Скопировать из одной таблицы в другую все статьи:

INSERT INTO `my_laravelblog`.`posts` (`title`, 
                                             `slug`, 
                                             `content`, 
                                             `created_at`,
                                             `updated_at`)
SELECT `TITLE`, `CODE`, `DETAIL_TEXT`, `DATE_CREATION`, `LAST_MODIFIED`
FROM `my_bwp`.`articles` ;

Скопировать из одной таблицы в другую все связи статей по категорям(тегам):

INSERT INTO `my_laravelblog`.`post_tags` (`post_id`, 
                                             `tag_id`)
SELECT `article_id`, `category_id`
FROM `my_bwp`.`articles_categories` ;

Починить даты:

UPDATE `my_bwp`.`articles` SET DATE_CREATION = '2018-01-01 00:00:00' 
WHERE DATE_CREATION = '1970-01-01 00:00:00'

Как сменить значения по-умолчанию для нескольких полей сразу:

ALTER TABLE `settings` 
	CHANGE `updated_at` `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CHANGE `created_at` `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

... такой скрипт сменит значения по умолчанию у колонок updated_at и created_at на текущую дату.

Как дать права пользователю MySQL из консоли: Grant access to a database user

mysql -u USERNAME -p

GRANT ALL ON .* TO @ IDENTIFIED BY '';

GRANT ALL PRIVILEGES ON *.* TO root@'localhost';

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'asdfQW()ER3456' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Как открыть доступ к MySQL из внешней сети?

1.
- sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
- Change bind-address from 127.0.0.1 to 0.0.0.0

2.
Create user with access from everywhere:

CREATE USER 'dionisvl'@'%' IDENTIFIED BY 'sdfsdfsdf';
GRANT ALL ON *.* to dionisvl@'%';

3. sudo systemctl restart mysql
(from https://phoenixnap.com/kb/mysql-remote-connection)

Выборка записей за определенную дату в SQL

Вариант 1

SELECT * FROM `mybase`.`mytable` 
	WHERE service LIKE '%nbki%'
	AND `app_start_at` BETWEEN "2019-11-01 00:00:01" AND "2019-11-30 23:59:59"
ORDER BY `app_start_at` DESC;

Вариант 2

SELECT COUNT(*) FROM `mybase`.`mytable` 
	WHERE service LIKE '%nbki%'
	AND `app_start_at` LIKE '2019-10-%'
ORDER BY `app_start_at` DESC;

Поиск и замена в MySQL

START TRANSACTION;
	UPDATE 
		YourTable
	SET 
		column_name = REPLACE(column_name,'text_to_find','text_to_replace')
	WHERE 
		column_name LIKE '%text_to_find%';
COMMIT;

Переменные в SQL

SET @order_id = (SELECT order_id
FROM `order`
WHERE operation_date < NOW()
LIMIT 1);


SELECT *
FROM `order`
WHERE `order`.order_id = @order_id;

Отключить кеширование запросов в MySQL

SQL_NO_CACHE

MySQL Error "Access denied for user 'root'@'172.20.0.3'"

Примерно такая ошибка: SQLSTATE[HY000] [1045] Access denied for user 'root'@'172.20.0.3' (using password: YES)

Скорее всего означает что указанному пользователю (в данном случае root) запрещен доступ из источника 172.20.0.3. В таком случае вам нужно предоставить ему доступ посредством другого админитстратора либо зайти по SSH на сервер и сделать это из консоли.

Поиск таблиц по маске

SHOW TABLES LIKE "%monitor%";

Как сделать группировку по дням месяца, и подсчитать кол-во каждого дня?

SELECT
status, channel, DATE_FORMAT(updated_at, "%d") as day, count(*)
FROM fines_request_log 
WHERE 1=1
    AND updated_at BETWEEN '2022-07-01 00:00:00' AND '2022-08-01 00:00:00'
GROUP BY day