Интересные и полезные запросы MySQL

В моем случае в результате выполнения запроса было возвращено значение: id.

Получить размер базы данных MySQL.

SELECT `table_schema` AS `Db name`,
ROUND(SUM(`data_length` + `index_length`) / 1024 / 1024, 3) AS 'Db size(MB)'
FROM `information_schema`.`tables`
GROUP BY `table_schema` 
HAVING `table_schema`='test';

Получение размера базы данных с именем test.

Определить количество слов в столбце.

SELECT LENGTH(`fio`) - LENGTH(REPLACE(`fio`, ' ', '')) + 1 AS `words_count` FROM `table`;

Копирование таблиц с одинаковой структурой данных.

INSERT IGNORE INTO `Table_New` SELECT * FROM `Table_Old`;

Получить даты создания записей, которым более 30 дней.

SELECT DATE_FORMAT(`Created`, '%d-%m-%Y') FROM `Table` WHERE `Created` <= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) GROUP BY DATE_FORMAT(`Created`, '%d-%m-%Y');

При этом выполнена группировка по полю с датой создания, чтобы исключить повторяющиеся даты.

Удаление записей, которым более 60 дней.

DELETE FROM `Table` WHERE `Created` <= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY);

Поиск значения в таблице с исключением некоторых символов.

SELECT * FROM `Table` WHERE `name` LIKE 'А-432.10' OR REPLACE(REPLACE(REPLACE(`name`, ' ', ''), '.', ''), '-', '') LIKE '%А43210%';

Возможность поиска через оператор LIKE, а также удаление некоторых символов из искомого значения.

Изменение структуры таблицы.

ALTER TABLE `Table` ADD COLUMN `Phone` VARCHAR(15) AFTER `FIO`; # Добавление нового столбца перед стобцом FIO
ALTER TABLE `Table` ADD INDEX `Phone` (`Phone`); # Добавление обычного индекса для столбца
ALTER TABLE `Table` MODIFY COLUMN `Phone` VARCHAR(15) NOT NULL; # Изменение структуры столбца
ALTER TABLE `Table` DROP `Phone`; # Удаление столбца вместе с индексами

Удаление дубликатов по определенному полю.

DELETE `Table` FROM `Table` LEFT OUTER JOIN (SELECT MIN(`Id`) AS `id`, `UserId` FROM `Table` GROUP BY `UserId`) AS `tmp` ON `Table`.`Id` = `tmp`.`id` WHERE `tmp`.`id` IS NULL;

В примере, поиск дубликатов производится по полю UserId.

Проверка определенных полей на одновременное заполнение значениями (должно быть заполнено только одно поле).

SELECT `id`, `sale`, `new`, `hit`, `discont` FROM `Table` 
WHERE 
`sale`='1' AND `sale` IN(`new`, `hit`, `discont`) OR
`new`='1' AND `new` IN(`sale`, `hit`, `discont`) OR
`hit`='1' AND `hit` IN(`sale`, `new`, `discont`) OR
`discont`='1' AND `discont` IN(`sale`, `new`, `hit`);

Обновление данных, используя другую таблицу в качестве источника.

UPDATE Table1, Table2 SET Table1.`ItemId` = Table2.`ItemId` WHERE Table1.`id` = Table2.`id`;

Сгруппировать по полю и вывести для каждого пользователя его Ip-адрес.

SELECT `UserId`, GROUP_CONCAT(`Ip`) FROM `Table` GROUP BY `UserId`;

Удалить все записи, оставив 20 последних новых записей.

DELETE FROM `Table` WHERE `Created` NOT IN (SELECT `Created` FROM (SELECT `Created` FROM `Table` GROUP BY `Created` ORDER BY `Created` DESC LIMIT 20) AS tbl);

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

SELECT m1.`UserId`, u.`clientManagerID`, m1.`IP`, DATE_FORMAT(m1.`Created`, '%d.%m.%Y %H:%i') AS Created
FROM `Table` AS m1 
LEFT JOIN `User` AS u ON m1.`UserId` = u.`UserId` 
WHERE m1.`Created` = (SELECT MIN(m2.`Created`) FROM `Table` AS m2 WHERE m1.`UserId` = m2.`UserId`);

Удалить запятую в конце поля.

UPDATE `Table` SET `field`=TRIM(BOTH ',' FROM `field`) WHERE `UserId`='12'

Пример запроса для обновление аналогов с удалением заданного аналога.

UPDATE `Table` SET `analog`=TRIM(BOTH ',' FROM REPLACE(REPLACE(`analog`, 'A-0014', ''), ',,', ',')) WHERE `id`='1789';

В данном примере аналоги хранятся в поле просто через запятую.

Пример с подзапросом в операторе LIKE.

SELECT `analog` FROM `Table` WHERE `analog` LIKE CONCAT('%', (SELECT `ItemId` FROM `Table` WHERE `id`='112'), '%');

Вывести из определенного раздела только первое слово, являющееся уникальным значением.

SELECT DISTINCT IF(INSTR(a.`Title`, ' ') > 0, LEFT(a.`Title`, INSTR(a.`Title`, ' ') - 1), a.`Title`) AS Title
FROM `Table` AS a
LEFT JOIN `Section` AS s ON a.`id` = s.`id` 
WHERE s.`parentId` = '1518';

Вывести расхождения в двух таблицах.

SELECT Table1.id, Table2.id FROM Table1
LEFT JOIN Table2 ON Table1.id = Table2.id 
WHERE Table2.id IS NULL
UNION ALL
SELECT Table1.id, Table2.id FROM Table2 
LEFT JOIN Table1 ON Table1.id = Table2.id 
WHERE Table1.id IS NULL;

Сгруппировать по имени дочернего раздела и вывести все имена его родителей.

SELECT a.`Title`, GROUP_CONCAT((SELECT `Title` FROM `Section` WHERE `id`=a.`parentId`))
FROM `Section` AS a
WHERE a.`Id1C` IS NOT NULL AND a.`parentId` != '43'
GROUP BY a.`Title`;

Сортировка по приоритету, когда в поле есть null значения.

SELECT `id`, `Title`, `parentId` 
FROM `Table`
WHERE `Id1C` IS NOT NULL
ORDER BY -`Priority` DESC, `Title`;

Вывести только первую букву по определенному полю из таблицы.

SELECT DISTINCT(SUBSTR(`Title`, 1, 1)) AS letter FROM `Table`;