SQL

  1. Примеры
  2. Добавить пользователя, через ssh с правами на просмотр
  3. Сменить менеджера
  4. Очистить все корзины
  5. Что в корзине у пользователя
  6. Выгрузить с старого бота
  7. Большой запрос

Примеры

Каждый раз надоело искать эти примеры.. Благодаря им можно быстрее выполнить SQL задачу. Тут нет объяснений, только кодец.
SELECT us.ID, COUNT(order1.ID) AS ORDERS, SUM(order1.PRICE) AS ORDERS_SUM, el.NAME, us.LOGIN, us.ACTIVE, us.LAST_NAME, us.NAME, us.SECOND_NAME, prop.UF_CALL_COMMENT, us.EMAIL, us.PERSONAL_PHONE, us.DATE_REGISTER FROM b_user AS us LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID LEFT JOIN b_iblock_element AS el ON el.ID = prop.UF_MANAGER WHERE el.IBLOCK_ID = '11' AND us.DATE_REGISTER >= '2021-07-01 00:00:00' AND us.DATE_REGISTER < '2021-07-13 00:00:00' GROUP BY us.ID ORDER BY us.ID DESC SELECT us.ID, COUNT(order1.ID) AS ORDERS, SUM(order1.PRICE) AS ORDERS_SUM, us.LOGIN, us.ACTIVE, us.LAST_NAME, us.NAME, us.SECOND_NAME, prop.UF_CALL_COMMENT, us.EMAIL, us.PERSONAL_PHONE, us.DATE_REGISTER FROM b_user AS us LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID WHERE us.DATE_REGISTER >= '2021-07-01 00:00:00' AND us.DATE_REGISTER < '2021-07-13 00:00:00' GROUP BY us.ID ORDER BY us.ID DESC LIMIT 100 SELECT us.ID, (SELECT COUNT(*) FROM b_sale_order AS order1 WHERE order1.USER_ID = us.ID) AS ORDERS, us.LOGIN, us.ACTIVE, us.LAST_NAME, us.NAME, us.SECOND_NAME, prop.UF_CALL_COMMENT, us.EMAIL, us.PERSONAL_PHONE, us.DATE_REGISTER FROM b_user AS us LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID WHERE us.DATE_REGISTER >= '2021-07-01 00:00:00' AND us.DATE_REGISTER < '2021-07-13 00:00:00' GROUP BY us.ID ORDER BY us.ID DESC LIMIT 10 (SELECT COUNT(*) FROM b_sale_order AS order1 WHERE order1.USER_ID = us.ID) AS orders_kol SELECT us.ID, order1.ID AS ID_order, order1.PRICE, us.LOGIN, us.ACTIVE, us.LAST_NAME, us.NAME, us.SECOND_NAME, prop.UF_CALL_COMMENT, us.EMAIL, us.PERSONAL_PHONE, us.DATE_REGISTER FROM b_user AS us LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID WHERE us.DATE_REGISTER >= '2021-07-01 00:00:00' AND us.DATE_REGISTER < '2021-07-13 00:00:00' GROUP BY us.ID ORDER BY us.ID DESC LIMIT 10 SELECT COUNT(*) TotalCount, us.ID, us.LOGIN, us.ACTIVE, us.LAST_NAME, us.NAME, us.SECOND_NAME, prop.UF_CALL_COMMENT, us.EMAIL, us.PERSONAL_PHONE, us.DATE_REGISTER, order1.ID AS ID_order FROM b_user AS us LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID WHERE us.DATE_REGISTER >= '2021-07-01 00:00:00' AND us.DATE_REGISTER < '2021-07-13 00:00:00' GROUP BY us.ID ORDER BY us.ID DESC LIMIT 10 SELECT us.ID, us.LOGIN, us.ACTIVE, us.LAST_NAME, us.NAME, us.SECOND_NAME, prop.UF_CALL_COMMENT, us.EMAIL, us.PERSONAL_PHONE, us.DATE_REGISTER, order1.ID AS ID_order FROM b_user AS us LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID LEFT JOIN b_sale_order AS order1 ON us.ID = order1.USER_ID WHERE us.DATE_REGISTER >= '2021-07-01 00:00:00' AND us.DATE_REGISTER < '2021-07-13 00:00:00' GROUP BY us.ID ORDER BY us.ID DESC LIMIT 10 SELECT us.ID, us.LOGIN, us.ACTIVE, us.LAST_NAME, us.NAME, us.SECOND_NAME, prop.UF_CALL_COMMENT, us.EMAIL, us.PERSONAL_PHONE, us.DATE_REGISTER FROM b_user AS us LEFT JOIN b_uts_user AS prop ON us.ID = prop.VALUE_ID WHERE us.DATE_REGISTER >= '2021-07-01 00:00:00' AND us.DATE_REGISTER < '2021-07-13 00:00:00' ORDER BY ID DESC LIMIT 3 SELECT t_el.ID, t_el.NAME, t_el.ACTIVE, t_el.CODE, t_pr.PRICE FROM b_iblock_element AS t_el LEFT JOIN b_catalog_price AS t_pr ON t_el.ID = t_pr.PRODUCT_ID WHERE t_el.IBLOCK_ID = '9' AND t_el.ACTIVE = 'Y' AND t_pr.PRICE IS null LIMIT 3 CREATE TABLE `h7bot` ( `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `id_u` char(255) NOT NULL, `type_bot` char(50) NOT NULL, `date_create` datetime NOT NULL, `date_rq` datetime NOT NULL, `agreement` char(1) NOT NULL, `id_site` char(50) NOT NULL, `input` char(50) NOT NULL ) ENGINE='MyISAM' COLLATE 'utf8_general_ci'; Задача У товаров арманго есть свойства На озоне == да Иды товаров, у которых это проставлено, можно вычеслить так SELECT `IBLOCK_ELEMENT_ID` FROM `b_iblock_element_property` WHERE `IBLOCK_PROPERTY_ID` = '709' AND `VALUE` = '158919' Так же в свойстве есть ид 1С Их можно просмотреть так SELECT `VALUE` FROM `b_iblock_element_property` WHERE `IBLOCK_PROPERTY_ID` = '48' AND `DESCRIPTION` = 'Код' Есть ещё табличка со складом, озона, просмотреть что-там есть можно так SELECT * FROM `b_catalog_store_product` WHERE `STORE_ID` = '287' //PRODUCT_ID AMOUNT Каким 1 запросом можно вывести список ид товара, ид1с, количество Вывести ид и ид1с SELECT tb0.IBLOCK_ELEMENT_ID, tb1.VALUE FROM b_iblock_element_property AS tb0 INNER JOIN b_iblock_element_property AS tb1 ON tb0.IBLOCK_PROPERTY_ID = '709' AND tb0.VALUE = '158919' AND tb0.IBLOCK_ELEMENT_ID = tb1.IBLOCK_ELEMENT_ID AND tb1.IBLOCK_PROPERTY_ID = '48' AND tb1.DESCRIPTION = 'Код' LIMIT 50 SELECT tb0.IBLOCK_ELEMENT_ID, tb1.VALUE, tb_store.AMOUNT FROM b_iblock_element_property AS tb0 INNER JOIN b_iblock_element_property AS tb1 INNER JOIN b_catalog_store_product AS tb_store ON tb0.IBLOCK_PROPERTY_ID = '709' AND tb0.VALUE = '158919' AND tb0.IBLOCK_ELEMENT_ID = tb1.IBLOCK_ELEMENT_ID AND tb1.IBLOCK_PROPERTY_ID = '48' AND tb1.DESCRIPTION = 'Код' AND tb_store.PRODUCT_ID = tb0.IBLOCK_ELEMENT_ID AND tb_store.STORE_ID = '287' LIMIT 50

Добавить пользователя, через ssh с правами на просмотр

mysql --user="root" --password="pass0" // Создать пользователя CREATE USER 'user1'@'%' IDENTIFIED BY 'pass1'; // Добавить право GRANT SELECT ON * . * TO 'user1'@'%'; // Сразу несколько GRANT SELECT, CREATE ON * . * TO 'user1'@'%'; // Удалить REVOKE SELECT ON * . * TO 'user1'@'%'; // Готовое mysql --user="root" --password="pass1" CREATE USER 'user1'@'%' IDENTIFIED BY 'pass2'; CREATE DATABASE bd1; USE bd1 GRANT CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE ON bd1 . * TO user1;
Список прав CREATE — Позволяет пользователям создавать базы данных/таблицы SELECT — Разрешает делать выборку данных INSERT — Право добавлять новые записи в таблицы UPDATE — Позволяет изменять существующие записи в таблицах DELETE — Даёт право удалять записи из таблиц DROP — Возможность удалять записи в базе данных/таблицах Подробная инструкция

Сменить менеджера

UPDATE b_uts_user SET UF_MANAGER = '35521' WHERE UF_MANAGER = '34387' LIMIT 714;

Очистить все корзины

SELECT * FROM `b_sale_basket` WHERE `ORDER_ID` IS NULL ORDER BY `ID` DESC LIMIT 50; DELETE FROM `b_sale_basket` WHERE `ORDER_ID` IS NULL LIMIT 100;

Что в корзине у пользователя

SELECT basket.ID AS ID, basket.PRODUCT_ID AS PRODUCT_ID, basket.NAME AS NAME, basket.PRICE AS PRICE, basket.QUANTITY AS QUANTITY, basket.DATE_UPDATE AS DATE_UPDATE, basket.DETAIL_PAGE_URL AS URL FROM b_sale_fuser AS fus INNER JOIN b_sale_basket AS basket ON fus.ID = basket.FUSER_ID WHERE fus.USER_ID = '13570' AND basket.ORDER_ID IS NULL

Выгрузить с старого бота

SELECT b.id, b.id_u, b.date_create, b.id_site, b.city_id, cit.NAME AS city, cit.REGION, b.phone, b.login, b.notif_action, b.memory1, b.memory2, b.memory3, b.memory4, b.memory5, b.memory6, b.memory7 FROM h7bot AS b INNER JOIN ipol_sdekcities AS cit ON b.city_id = cit.ID

Большой запрос

битрикс список людей с неоплаченными заказами, С активностью: с начала этого года имя, телефон, почта, сумма и менеджер Сортированные по убыванию суммы
SELECT us.ID AS ID, us.LAST_NAME, us.NAME, us.SECOND_NAME, us.PERSONAL_PHONE, us.EMAIL, ROUND(SUM(basket.PRICE * basket.QUANTITY)) AS SUMM, man.NAME FROM b_user AS us INNER JOIN b_uts_user AS uts ON uts.VALUE_ID = us.ID INNER JOIN b_iblock_element AS man ON uts.UF_MANAGER = man.ID INNER JOIN b_sale_fuser AS fus ON fus.USER_ID = us.ID INNER JOIN b_sale_basket AS basket ON basket.FUSER_ID = fus.ID WHERE us.LAST_LOGIN >= '2022-01-01' AND man.IBLOCK_ID = '11' AND basket.ORDER_ID IS NULL GROUP BY us.ID ORDER BY SUMM DESC LIMIT 1000;