- Подробности
- </программирование>
В этой заметке я буду собирать часто попадающиеся мне в ходе сайтостроительных задач SQL-запросы.
Удаление строк из таблицы на основании подзапроса (subquery), в котором присутствуют поля как из данной таблицы, так и из других:
DELETE ua FROM url_alias ua
JOIN node n ON n.type = 'torgovya_marka'
AND ua.alias LIKE 'content/%'
AND ua.source LIKE CONCAT( 'node/', n.nid )
Описанный запрос удалит из таблицы url_alias все строки, в которых поле alias начинается с 'content/', а поле source при этом содержит результат конкатенации 'node/' и идентификаторы записей nid из таблицы node, у которых поле type содержит значение 'torgovya_marka'. Другими словами, запрос выберет из таблицы node все идентификаторы строк, у которых поле type = 'torgovya_marka' и подставит их в родительский запрос, предварив каждый идентификатор строкой 'node/'. А далее будет произведена проверка полей source и alias редактируемой таблицы url_alias в соответствии с указанными условиями и выполнено удаление строк, которые этим условиям удовлетворяют.
Уникализация таблицы БД или удаление одинаковых строк:
ALTER IGNORE TABLE table ADD UNIQUE INDEX(column);
Импорт большого дампа через консоль:
CREATE DATABASE [DBNAME];
USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;
Сортировка по нескольким полям с условием, что предпочтение отдается НЕ нулевым (не NULL) значениям:
...order by COALESCE(p.products_date_available, p.products_date_added) desc
Поиск одинаковых значений в колонке:
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;
Update и join в одном запросе:
UPDATE t1 LEFT JOIN t2 ON t2.id = t1.id SET t1.col1 = newvalue WHERE t2.id IS NULL
Информация о таблице с индикатором, какое из полей является первичным ключом (Primary Key, pk) для SQLite:
PRAGMA table_info(myTable)
Регулярное выражение в запросе:
SELECT url_alias_id
FROM oc_url_alias
WHERE query REGEXP 'product_id=[0-9]+'
AND CAST(RIGHT(query, LENGTH(query) - 10 -
LOCATE('product_id=', query)) AS SIGNED INTEGER)
NOT IN (select product_id from oc_product)
Тримминг (удаление пробелов в начале и конце) поля:
UPDATE `oc_product_description` SET name=TRIM(name)
DELETE ... SELECT:
DELETE FROM posts WHERE id IN (
SELECT * FROM (
SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
) AS p
)
Чтобы не возникала ошибка при truncate foreign key constrained table:
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;
SET FOREIGN_KEY_CHECKS = 1;
Подсчет корректного кол-ва строк COUNT(*), когда в запросе есть LEFT JOIN:
SELECT COUNT(DISTINCT p.product_id) AS total FROM... LEFT JOIN...
Поиск по числовым выражениям (что-то типа is_numeric , is_number):
... WHERE concat( '', model * 1 ) = model
Полезно иметь в своем арсенале инструментарий, который можно легко применять из приложения в приложение для решения широкого круга задач, не касаясь кода этого инструментария и вне зависимости от характера самих задач. В ходе разработки всевозможных приложений программистами был выделен ряд абстрактных программных решений, которые, как шаблоны или трафареты, можно свободно приложить к конкретно стоящей проблеме и вычертить по ним готовый продукт.