В этой заметке я буду собирать часто попадающиеся мне в ходе сайтостроительных задач 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):
Уже староватая, но полезная публикация о том, как повысить посещаемость сайта за счет недорогих среднечастотных (СЧ) запросов в Яндекс.Директе. При нищенском бюджете или для собственного блога - это экономичное решение. Мне как раз нужно как-то продвигать Жужум в читательские массы. И хочется оперативно без сеошных линкосистем и сопутствующей неповоротливости:
Можно ли приводить много заинтересованных посетителей из Директа по 30 копеек за посетителя? Можно и нужно, особенно, если ваш ресурс только встает на ноги и нужно быстро и недорого привести на сайт заинтересованных людей.
Это – пошаговое руководство для тех, кто хочет знать, каким боком из Яндекс Директ (на вполне законных, между прочим, основаниях) привлекать недорогой трафик по 0.01 у.е или 30 копеек за переход. Читать далее>>
Apache в плане потребления ресурсов неумолим. Поэтому уже давно практикуется вариант, когда на нем висит обработка динамической части сайтов, а вся статика ложится на плечи экономного и шустрого Nginx. В нашем случае, алгоритм работы будет состоять из приема HTTP-запроса от пользователя средствами Nginx, распознавание - запрашивается ли php-скрипт. Если да, запрос передается на Apache. В противном случае, работу продолжает Nginx. Настроить такую схему можно за пару минут.
Привожу перечень бизнес-идей, которые актуальны для онлайн-сферы. Постарался сделать его кратким и по делу. Для некоторых указал примеры и возможное направление полета фантазии. Читать далее>>
В связи со сменой хостинг-панели на сервере с ISPConfig на ZPanel пришлось заменить и сервер входящей почты с Courier на Dovecot, поскольку Zpanel поддерживает работу только с последним. Раньше почтовая связка работала через вирусный сканер Amavis, который подключал SpamAssassin для определения спамности письма. Тот помечал спамное письмо, модифицируя тему (subject) по шаблону ***SPAM***. Потом письмо направлялось во Входящие. Фильтрация помеченных писем производилась не на сервере, а уже у меня на локальной машине, после получения. Это было удобно по той простой причине, что некорректно распознанные письма могли попасть в спам незаслуженно, но при этом они не терялись и не удалялись. И в случае обнаружения, я их быстро мог восстановить.
Вадим Зеланд в своем многотомнике «Трансерфинг реальности» говорит, что деньги нельзя считать ни главной целью жизни, ни средством ее достижения: они являются лишь сопутствующим атрибутом. Безусловно, мы вправе определить их своими и целью или средством, никто нам не запретит, даже наоборот: подобные амплуа денежной массы — удобный инструмент манипуляции людьми. Но у денег роль однозначная — это инструмент обмена продуктов, производимых собственными талантами на продукты, производимые талантами всего остального человечества или, проще говоря, благ, которые мы можем дать, на блага, которые могут дать нам другие. Читать далее...
Если на сервере Apache работает с PHP через FastCGI, для использования становятся недоступными $_SERVER['PHP_AUTH_USER'] и $_SERVER['PHP_AUTH_PW'] , поскольку PHP просто больше не получает соответствующие значения от сервера. Для того, чтобы все-таки была возможность пользоваться этими переменными в скриптах с авторизацией через модуль Apache, придется кое-что дописать.