Несколько полезных запросов в базу данных
Предыдущая и следующая запись
SELECT * FROM `i_u11_1` WHERE (`WID` = (SELECT MAX(`WID`) FROM `i_u11_1` WHERE `WID` < 17) OR `WID` = (SELECT MIN(`WID`) FROM `i_u11_1` WHERE `WID` > 17))
Предыдущая и следующая запись с дополнительной выборкой
SELECT * FROM `i_u11_1` WHERE (`WID` = (SELECT MAX(`WID`) FROM `i_u11_1`WHERE `WID` < 19 AND `APP`= 'content') OR `WID` = (SELECT MIN(`WID`) FROM `i_u11_1` WHERE `WID` > 19 AND `APP`='content'))
Указать между каким датами показать выборку
SELECT * FROM content WHERE id BETWEEN 40 AND 60 WHERE id >= 40 AND id <= 60 Analog
Диапазон дат
SELECT * FROM order_details WHERE order_date BETWEEN CAST('2017-12-01' AS DATE) AND CAST('2017-12-31' AS DATE);
***
SELECT * FROM order_details WHERE order_date BETWEEN CAST('2017-12-01' AS DATE) AND CAST('2017-12-31' AS DATE);
Слова начинаются с Ber...
SELECT customer_name FROM customers WHERE last_name LIKE 'Ber%';
Нахождение символов
SELECT customer_name FROM customers WHERE last_name LIKE '%ns%';
Подзапрос
Этот подзапрос позволяет находить все значения поля site_name из таблицы address_book, у которого address_book_id менее 50. Подзапрос затем используется для фильтрации результатов основного запроса с использованием условия IN.
SELECT c.contact_id, c.last_name FROM contacts c WHERE c.site_name IN (SELECT a.site_name FROM address_book a WHERE a.address_book_id < 50);
Аналогично этому: Второй запрос будет работать эффективнее!
SELECT c.contact_id, c.last_name FROM contacts c INNER JOIN address_book a ON c.site_name = a.site_name WHERE a.address_book_id < 50;
Этот подзапрос имеет псевдоним с именем subquery1. Это будет имя, используемое для ссылки на этот подзапрос или любое из его полей.
SELECT contacts.last_name, subquery1.total_size
FROM contacts, (SELECT site_name, SUM(file_size) AS total_size
FROM pages GROUP BY site_name) subquery1
WHERE subquery1.site_name = contacts.site_name;
SELECT p1.site_name, (SELECT MAX(file_size) FROM pages p2
WHERE p1.site_id = p2.site_id) subquery2
FROM pages p1;
SELECT products.product_id, inventory.qty_on_hand
FROM products
LEFT OUTER JOIN inventory
ON products.product_id = inventory.product_id
WHERE products.product_name = 'MySQL';
Этот пример MySQL оператора DISTINCT возвращает каждую уникальную комбинацию полей city и state из таблицы customers. В этом случае DISTINCT применяется к каждому столбцу, указанному после ключевого слова DISTINCT, и, поэтому, возвращает отдельные комбинации.
SELECT DISTINCT city, state
FROM customers;
В этом простом примере вы можете использовать оператор IN для возврата всех значений
category_id, которые существуют как в products, так и в таблицах inventory.
SELECT category_id
FROM products
INTERSECT
SELECT category_id
FROM inventory;
Аналог:
SELECT products.category_id
FROM products
WHERE products.category_id IN (SELECT inventory.category_id FROM inventory);
SELECT category_id
FROM products
WHERE category_id < 100
INTERSECT
SELECT category_id
FROM inventory
WHERE quantity > 0;
Аналог:
SELECT products.category_id
FROM products
WHERE products.category_id < 100
AND products.category_id IN
(SELECT inventory.category_id
FROM inventory
WHERE inventory.quantity > 0);
SELECT contact_id, last_name, first_name
FROM contacts
WHERE contact_id < 100
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name <> 'Johnson';
SELECT contacts.contact_id, contacts.last_name, contacts.first_name
FROM contacts
WHERE contacts.contact_id < 100
AND EXISTS (SELECT *
FROM customers
WHERE customers.last_name <> 'Markoski'
AND customers.customer_id = contacts.contact_id
AND customers.last_name = contacts.last_name
AND customers.first_name = contacts.first_name);
SELECT *
FROM suppliers
WHERE (state = 'Nevada' AND supplier_name = 'IBM')
OR (supplier_id > 7000);
В этом примере GROUP BY используется функция COUNT для возврата product и количества заказов
(для этого product), которые находятся в категории product.
SELECT product, COUNT(*) AS "Number of orders"
FROM order_details
WHERE category = 'produce'
GROUP BY product;
В этом примере GROUP BY используется функция MIN, чтобы вернуть department (имя каждого отдела)
и lowest salary (минимальную зарплату в отделе).
SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;
В этом примере GROUP BY используется функция MAX для возврата имени каждого department и максимальной
заработной платы в department.
SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;
В этом примере MySQL GROUP BY используется функция SUM, чтобы вернуть имя product
и общее количество (для product).
SELECT product, SUM(quantity) AS "Total kol"
FROM order_details
GROUP BY product;
******************************** HAVING - Ограничение группировки GROUP BY
Вы также можете использовать функцию SUM, чтобы вернуть имя product
и общее количество (для этого product). MySQL оператор HAVING будет
фильтровать результаты так, чтобы возвращались только product с общим количеством больше 10.
SELECT product, SUM(quantity) AS "Total quantity"
FROM order_details
GROUP BY product
HAVING SUM(quantity) > 10;
Вы можете использовать функцию COUNT, чтобы вернуть имя product
и количество заказов (для этого product), которые находятся
в категории ‘produce’. MySQL оператор HAVING будет фильтровать результаты так,
чтобы возвращались только product с более чем 20 заказами.
SELECT product, COUNT(*) AS "Number of orders"
FROM order_details
WHERE category = 'produce'
GROUP BY product
HAVING COUNT(*) > 20;
Вы также можете использовать функцию MIN, чтобы вернуть имя каждого department
и минимальную зарплату в department. MySQL оператор HAVING будет возвращать
только те department, где минимальная зарплата составляет менее 50000 биткоинов :).
SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) < 50000;
Например, вы также можете использовать функцию MAX для возврата имени каждого department
и максимальной заработной платы в department. Предложение MySQL HAVING будет возвращать
только те department, чья максимальная заработная плата превышает 25000 биткоинов :).
SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) > 25000;
Этот пример MySQL условия EXISTS возвращает все записи из таблицы customers, где есть хотя бы
одна запись в таблице order_details с соответствующим customer_id
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);
Этот MySQL пример EXISTS вернет все записи из таблицы customers, в которых нет записей
в таблице order_details с соответствующим customer_id.
SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);
INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
FROM orders
WHERE suppliers.supplier_id = orders.supplier_id);
UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT *
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);
DELETE FROM suppliers
WHERE EXISTS (SELECT *
FROM orders
WHERE suppliers.supplier_id = orders.supplier_id);
В этом примере MySQL оператор UPDATE обновил значение city в таблице customers значением city
из таблицы suppliers, где customer_id соответствует supplier_id.
UPDATE customers, suppliers
SET customers.city = suppliers.city
WHERE customers.customer_id = suppliers.supplier_id;
В этом примере UPDATE будет обновляться только таблица customers для всех записей,
где customer_id больше 5000. Когда supplier_name из таблицы suppliers совпадает
с именем customer_name из таблицы customers, city из таблицы suppliers будет
скопирован в поле city таблицы customers.
UPDATE customers
SET city = (SELECT city
FROM suppliers
WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id > 5000;
mysql> SELECT ROUND(123.345);
+----------------+
| ROUND(123.345) |
+----------------+
| 123 |
+----------------+
mysql> SELECT ROUND(123.345, 0);
+-------------------+
| ROUND(123.345, 0) |
+-------------------+
| 123 |
+-------------------+
mysql> SELECT ROUND(123.345, 1);
+-------------------+
| ROUND(123.345, 1) |
+-------------------+
| 123.3 |
+-------------------+
mysql> SELECT ROUND(123.345, 2);
+-------------------+
| ROUND(123.345, 2) |
+-------------------+
| 123.35 |
+-------------------+
mysql> SELECT ROUND(123.345, -1);
+--------------------+
| ROUND(123.345, -1) |
+--------------------+
| 120 |
+--------------------+
mysql> SELECT ROUND(123.345, -2);
+--------------------+
| ROUND(123.345, -2) |
+--------------------+
| 100 |
+--------------------+
mysql> SELECT ROUND(-123.345);
+-----------------+
| ROUND(-123.345) |
+-----------------+
| -123 |
SELECT BE.ID as ID
FROM b_iblock B
INNER JOIN b_lang L ON B.LID = L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1 = 1
AND (
(BE.IBLOCK_ID = '14')
AND (EXISTS (
SELECT IBLOCK_ID
FROM b_iblock_site
WHERE IBLOCK_ID = B.ID
AND (SITE_ID = 's1')
))
AND ((
BE.ACTIVE_TO >= now() Время начала показа меньше текущего
OR BE.ACTIVE_TO IS NULL Время начала показа равно NULL
)
AND (
BE.ACTIVE_FROM <= now() Время конца показа больше текущего
OR BE.ACTIVE_FROM IS NULL Время конца показа равно NULL
)
)
AND (BE.ACTIVE = 'Y') Активный
AND (BE.CODE = 'tumba_pristavnaya_a_tp_4_belyy') Поиск по псевдониму URL
)
AND (
BE.WF_STATUS_ID = 1
AND BE.WF_PARENT_ELEMENT_ID IS NULL
)
AND (
B.ID IN (
SELECT
IBLOCK_ID
FROM
b_iblock_group IBG
WHERE
IBG.GROUP_ID IN (2)
AND IBG.PERMISSION >= 'R'
AND (
IBG.PERMISSION = 'X'
OR B.ACTIVE = 'Y'
)
)
OR (
B.RIGHTS_MODE = 'E'
AND EXISTS (
SELECT
ER.ELEMENT_ID
FROM
b_iblock_element_right ER
INNER JOIN b_iblock_right IBR ON
IBR.ID = ER.RIGHT_ID
INNER JOIN b_user_access UA ON
UA.ACCESS_CODE = IBR.GROUP_CODE
AND UA.USER_ID = 0
WHERE
ER.ELEMENT_ID = BE.ID
AND IBR.OP_EREAD = 'Y'
)
)
)
SELECT
BE.ID as ID,
BE.IBLOCK_ID as IBLOCK_ID,
BE.CODE as CODE,
BE.XML_ID as XML_ID,
BE.NAME as NAME,
BE.ACTIVE as ACTIVE,
IF(
EXTRACT(HOUR_SECOND FROM
BE.ACTIVE_FROM)>0,
DATE_FORMAT(
BE.ACTIVE_FROM,
'%d.%m.%Y %H:%i:%s'
),
DATE_FORMAT(
BE.ACTIVE_FROM,
'%d.%m.%Y'
)
) as DATE_ACTIVE_FROM,
IF(
EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_TO)>0,
DATE_FORMAT(
BE.ACTIVE_TO,
'%d.%m.%Y %H:%i:%s'
),
DATE_FORMAT(
BE.ACTIVE_TO,
'%d.%m.%Y'
)
) as DATE_ACTIVE_TO,
BE.SORT as SORT,
BE.PREVIEW_TEXT as PREVIEW_TEXT,
BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,
BE.DETAIL_TEXT as DETAIL_TEXT,
BE.DETAIL_TEXT_TYPE as DETAIL_TEXT_TYPE,
DATE_FORMAT(
BE.DATE_CREATE,
'%d.%m.%Y %H:%i:%s'
) as DATE_CREATE,
BE.CREATED_BY as CREATED_BY,
BE.TAGS as TAGS,
DATE_FORMAT(
BE.TIMESTAMP_X,
'%d.%m.%Y %H:%i:%s'
) as TIMESTAMP_X,
BE.MODIFIED_BY as MODIFIED_BY,
BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,
B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,
BE.DETAIL_PICTURE as DETAIL_PICTURE,
BE.PREVIEW_PICTURE as PREVIEW_PICTURE,
B.LIST_PAGE_URL as LIST_PAGE_URL,
L.DIR as LANG_DIR,
BE.XML_ID as EXTERNAL_ID,
B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
B.LID as LID,
CAT_PR.QUANTITY as CATALOG_QUANTITY,
CAT_PR.QUANTITY_RESERVED as CATALOG_QUANTITY_RESERVED,
IF (
CAT_PR.QUANTITY_TRACE = 'D',
'N',
CAT_PR.QUANTITY_TRACE
) as CATALOG_QUANTITY_TRACE,
CAT_PR.QUANTITY_TRACE as CATALOG_QUANTITY_TRACE_ORIG,
IF (
CAT_PR.CAN_BUY_ZERO = 'D',
'N',
CAT_PR.CAN_BUY_ZERO
) as CATALOG_CAN_BUY_ZERO,
CAT_PR.CAN_BUY_ZERO as CATALOG_CAN_BUY_ZERO_ORIG,
IF (
CAT_PR.NEGATIVE_AMOUNT_TRACE = 'D',
'N',
CAT_PR.NEGATIVE_AMOUNT_TRACE
) as CATALOG_NEGATIVE_AMOUNT_TRACE,
CAT_PR.NEGATIVE_AMOUNT_TRACE as CATALOG_NEGATIVE_AMOUNT_ORIG,
IF (
CAT_PR.SUBSCRIBE = 'D',
'N',
CAT_PR.SUBSCRIBE
) as CATALOG_SUBSCRIBE,
CAT_PR.SUBSCRIBE as CATALOG_SUBSCRIBE_ORIG,
CAT_PR.AVAILABLE as CATALOG_AVAILABLE,
CAT_PR.WEIGHT as CATALOG_WEIGHT,
CAT_PR.WIDTH as CATALOG_WIDTH,
CAT_PR.LENGTH as CATALOG_LENGTH,
CAT_PR.HEIGHT as CATALOG_HEIGHT,
CAT_PR.MEASURE as CATALOG_MEASURE,
CAT_VAT.RATE as CATALOG_VAT,
CAT_PR.VAT_INCLUDED as CATALOG_VAT_INCLUDED,
CAT_PR.PRICE_TYPE as CATALOG_PRICE_TYPE,
CAT_PR.RECUR_SCHEME_TYPE as CATALOG_RECUR_SCHEME_TYPE,
CAT_PR.RECUR_SCHEME_LENGTH as CATALOG_RECUR_SCHEME_LENGTH,
CAT_PR.TRIAL_PRICE_ID as CATALOG_TRIAL_PRICE_ID,
CAT_PR.WITHOUT_ORDER as CATALOG_WITHOUT_ORDER,
CAT_PR.SELECT_BEST_PRICE as CATALOG_SELECT_BEST_PRICE,
CAT_PR.PURCHASING_PRICE as CATALOG_PURCHASING_PRICE,
CAT_PR.PURCHASING_CURRENCY as CATALOG_PURCHASING_CURRENCY,
CAT_PR.TYPE as CATALOG_TYPE,
CAT_PR.BUNDLE as CATALOG_BUNDLE
FROM
b_iblock B
INNER JOIN b_lang L ON
B.LID = L.LID
INNER JOIN b_iblock_element BE ON
BE.IBLOCK_ID = B.ID
LEFT JOIN b_catalog_product CAT_PR ON
(CAT_PR.ID = BE.ID)
LEFT JOIN b_catalog_iblock CAT_IB ON
(
(
CAT_PR.VAT_ID IS NULL
OR CAT_PR.VAT_ID = 0
)
AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID
)
LEFT JOIN b_catalog_vat CAT_VAT ON
(
CAT_VAT.ID = IF(
(
CAT_PR.VAT_ID IS NULL
OR CAT_PR.VAT_ID = 0
),
CAT_IB.VAT_ID,
CAT_PR.VAT_ID
)
)
WHERE
1 = 1
AND (
(
EXISTS (
SELECT
IBLOCK_ID
FROM
b_iblock_site
WHERE
IBLOCK_ID = B.ID
AND (SITE_ID = 's1')
)
)
AND (
(
BE.ACTIVE_TO >= now()
OR BE.ACTIVE_TO IS NULL
)
AND (
BE.ACTIVE_FROM <= now()
OR BE.ACTIVE_FROM IS NULL
)
)
AND (BE.ACTIVE = 'Y')
AND (BE.IBLOCK_ID = '14')
AND (BE.ID = '4368')
)
AND (
BE.WF_STATUS_ID = 1
AND BE.WF_PARENT_ELEMENT_ID IS NULL
)
AND (
B.ID IN (
SELECT
IBLOCK_ID
FROM
b_iblock_group IBG
WHERE
IBG.GROUP_ID IN (2)
AND IBG.PERMISSION >= 'R'
AND (
IBG.PERMISSION = 'X'
OR B.ACTIVE = 'Y'
)
)
OR (
B.RIGHTS_MODE = 'E'
AND EXISTS (
SELECT
ER.ELEMENT_ID
FROM
b_iblock_element_right ER
INNER JOIN b_iblock_right IBR ON
IBR.ID = ER.RIGHT_ID
INNER JOIN b_user_access UA ON
UA.ACCESS_CODE = IBR.GROUP_CODE
AND UA.USER_ID = 0
WHERE
ER.ELEMENT_ID = BE.ID
AND IBR.OP_EREAD = 'Y'
)
)
)
SELECT
MAX(IBG.PERMISSION) as P
FROM
b_iblock_group IBG
WHERE
IBG.IBLOCK_ID = 14
AND IBG.GROUP_ID IN (2)
INSERT INTO b_catalog_viewed_product (
`FUSER_ID`,
`DATE_VISIT`,
`PRODUCT_ID`,
`ELEMENT_ID`,
`SITE_ID`,
`VIEW_COUNT`,
`RECOMMENDATION`
) VALUES (
'50681',
'2017-07-08 13:02:58',
'3826',
'3826',
's1',
'1',
''
)
SELECT
C. *,
C.ID as REFERENCE_ID,
concat(
'[',
C.ID,
'] ',
ifnull(
C.NAME,
''
)
) as REFERENCE
FROM
b_stat_country C
WHERE
(1 = 1)
ORDER BY
C.NAME DESC
SELECT
C. *,
C.ID as REFERENCE_ID,
concat(
'[',
C.ID,
'] ',
ifnull(
C.NAME,
''
)
) as REFERENCE
FROM
b_stat_country C
WHERE
(1 = 1)
ORDER BY
C.NAME DESC
SELECT
CS.ID as ID,
CS.ACTIVE as ACTIVE,
CS.TITLE as TITLE,
CS.PHONE as PHONE,
CS.SCHEDULE as SCHEDULE,
CS.ADDRESS as ADDRESS,
CS.DESCRIPTION as DESCRIPTION,
CS.GPS_N as GPS_N,
CS.GPS_S as GPS_S,
CS.IMAGE_ID as IMAGE_ID,
DATE_FORMAT(CS.DATE_CREATE,'%d.%m.%Y %H:%i:%s') as DATE_CREATE, // Формат вывода времени
DATE_FORMAT(CS.DATE_MODIFY,'%d.%m.%Y %H:%i:%s') as DATE_MODIFY, // Формат вывода времени
CS.USER_ID as USER_ID,
CS.XML_ID as XML_ID,
CS.SORT as SORT,
CS.EMAIL as EMAIL,
CS.ISSUING_CENTER as ISSUING_CENTER,
CS.SHIPPING_CENTER as SHIPPING_CENTER,
CS.SITE_ID as SITE_ID,
CS.CODE as CODE
FROM
b_catalog_store CS
WHERE
(CS.ACTIVE = 'Y')
AND (CS.SITE_ID = 's1')
--------------------- Объединение строк в общую строку Образуя массив: Администраторы [13]
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
SELECT G.ID, G.ACTIVE, G.C_SORT, G.ANONYMOUS, G.NAME, G.DESCRIPTION, G.STRING_ID, G.ID REFERENCE_ID,
concat(G.NAME,' [',G.ID,']') REFERENCE,
DATE_FORMAT(G.TIMESTAMP_X,'%d.%m.%Y %H:%i:%s') TIMESTAMP_X
FROM b_group G
WHERE (1 = 1 AND (G.ACTIVE = 'Y') )
GROUP BY
G.ID,
G.ACTIVE,
G.C_SORT,
G.TIMESTAMP_X,
G.ANONYMOUS,
G.NAME,
G.STRING_ID,
G.DESCRIPTION
HAVING 1 = 1
ORDER BY G.ID ASC
UPDATE b_stat_session_data
SET
`DATE_LAST` = now(),
`GUEST_MD5` = '357ed10121cd501ca3676dc46a12a40d',
`SESS_SESSION_ID` = 0,
`SESSION_DATA` = 'a:26:{s:8:\"referer1\";N;s:8:\"referer2\";N;s:8:\"referer3\";N;s:16:\"SESS_SEARCHER_ID\";i:3;s:18:\"SESS_SEARCHER_NAME\";s:6:\"Yandex\";s:28:\"SESS_SEARCHER_CHECK_ACTIVITY\";s:1:\"N\";s:28:\"SESS_SEARCHER_SAVE_STATISTIC\";s:1:\"Y\";s:27:\"SESS_SEARCHER_HIT_KEEP_DAYS\";N;s:18:\"SESS_LAST_PROTOCOL\";N;s:13:\"SESS_LAST_URI\";N;s:14:\"SESS_LAST_HOST\";N;s:14:\"SESS_LAST_PAGE\";N;s:13:\"SESS_LAST_DIR\";N;s:17:\"SESS_HTTP_REFERER\";N;s:15:\"SESS_COUNTRY_ID\";N;s:12:\"SESS_CITY_ID\";N;s:15:\"SESS_SESSION_ID\";N;s:15:\"SESS_REFERER_ID\";N;s:16:\"FROM_SEARCHER_ID\";N;s:19:\"SESS_FROM_SEARCHERS\";N;s:23:\"SESS_REQUEST_URI_CHANGE\";N;s:16:\"SESS_LAST_DIR_ID\";N;s:17:\"SESS_LAST_PAGE_ID\";N;s:22:\"SESS_GRABBER_STOP_TIME\";N;s:26:\"SESS_GRABBER_DEFENCE_STACK\";a:1:{i:0;i:1499036672;}s:27:\"ACTIVITY_EXCEEDING_NOTIFIED\";N;}'
WHERE
ID = 6268
--------- Добавить +1 к кол-ву просмотров
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
UPDATE b_conv_context_counter_day
SET
`VALUE` = `VALUE` + 1
WHERE
`DAY` = '2017-07-08'
AND `CONTEXT_ID` = '4'
AND `NAME` = 'conversion_visit_day'
SELECT
TAG
FROM
b_cache_tag
WHERE
SITE_ID = 's1'
AND CACHE_SALT = '/59d'
AND RELATIVE_PATH = '/COptimusCache/iblock/CIBlockElement_GetList/stock12/'
SELECT
B.SECTION_PROPERTY,
BP.ID PROPERTY_ID,
BSP.SECTION_ID LINK_ID,
BSP.SMART_FILTER,
BSP.DISPLAY_TYPE,
BSP.DISPLAY_EXPANDED,
BSP.FILTER_HINT,
BP.SORT,
0 LEFT_MARGIN,
B.NAME LINK_TITLE,
BP.PROPERTY_TYPE,
BP.USER_TYPE,
BP.ACTIVE
FROM
b_iblock B
INNER JOIN b_iblock_property BP ON
BP.IBLOCK_ID = B.ID
LEFT JOIN b_iblock_section_property BSP ON
BSP.SECTION_ID = 0
AND BSP.PROPERTY_ID = BP.ID
WHERE
B.ID = 15
ORDER BY
BP.SORT ASC,
BP.ID ASC
SELECT
B.SECTION_PROPERTY,
BP.ID PROPERTY_ID,
BSP.SECTION_ID LINK_ID,
BSP.SMART_FILTER,
BSP.DISPLAY_TYPE,
BSP.DISPLAY_EXPANDED,
BSP.FILTER_HINT,
BP.SORT,
BS.LEFT_MARGIN,
BS.NAME LINK_TITLE,
BP.PROPERTY_TYPE,
BP.USER_TYPE,
BP.ACTIVE
FROM
b_iblock B
INNER JOIN b_iblock_property BP ON
BP.IBLOCK_ID = B.ID
INNER JOIN b_iblock_section M ON
M.ID = 64
INNER JOIN b_iblock_section BS ON
BS.IBLOCK_ID = M.IBLOCK_ID
AND M.LEFT_MARGIN >= BS.LEFT_MARGIN
AND M.RIGHT_MARGIN <= BS.RIGHT_MARGIN
INNER JOIN b_iblock_section_property BSP ON
BSP.IBLOCK_ID = BS.IBLOCK_ID
AND BSP.SECTION_ID = BS.ID
AND BSP.PROPERTY_ID = BP.ID
WHERE
B.ID = 15
ORDER BY
BP.SORT ASC,
BP.ID ASC,
BS.LEFT_MARGIN ASC
SELECT
UG.GROUP_ID,
DATE_FORMAT(
UG.DATE_ACTIVE_FROM,
'%d.%m.%Y %H:%i:%s'
) as DATE_ACTIVE_FROM,
DATE_FORMAT(
UG.DATE_ACTIVE_TO,
'%d.%m.%Y %H:%i:%s'
) as DATE_ACTIVE_TO
FROM
b_user_group UG
WHERE
UG.USER_ID = 0 UNION SELECT
2,
NULL,
NULL
SELECT
`catalog_price`.`ID` AS `ID`,
`catalog_price`.`PRODUCT_ID` AS `PRODUCT_ID`,
`catalog_price`.`EXTRA_ID` AS `EXTRA_ID`,
`catalog_price`.`CATALOG_GROUP_ID` AS `CATALOG_GROUP_ID`,
`catalog_price`.`PRICE` AS `PRICE`,
`catalog_price`.`CURRENCY` AS `CURRENCY`,
`catalog_price`.`TIMESTAMP_X` AS `TIMESTAMP_X`,
`catalog_price`.`QUANTITY_FROM` AS `QUANTITY_FROM`,
`catalog_price`.`QUANTITY_TO` AS `QUANTITY_TO`,
`catalog_price`.`TMP_ID` AS `TMP_ID`,
`catalog_price`.`PRICE_SCALE` AS `PRICE_SCALE`
FROM
`b_catalog_price` `catalog_price`
WHERE
`catalog_price`.`ID` = 3375
SELECT
`catalog_group_access`.`BUY` AS `ACCESS`
FROM
`b_catalog_group2group` `catalog_group_access`
WHERE
`catalog_group_access`.`CATALOG_GROUP_ID` = 1
AND `catalog_group_access`.`GROUP_ID` IN (2)
UPDATE b_iblock_element
SET
TIMESTAMP_X = TIMESTAMP_X,
SHOW_COUNTER_START = ifnull(
SHOW_COUNTER_START,
now()
),
SHOW_COUNTER = ifnull(SHOW_COUNTER, 0) + 1
WHERE
ID = 4368
SELECT
ID
FROM
b_catalog_product_sets
WHERE
OWNER_ID IN(4368)
AND TYPE = 2
LIMIT 1
SELECT
`catalog_catalog_iblock`.`IBLOCK_ID` AS `IBLOCK_ID`,
`catalog_catalog_iblock`.`PRODUCT_IBLOCK_ID` AS `PRODUCT_IBLOCK_ID`,
`catalog_catalog_iblock`.`SKU_PROPERTY_ID` AS `SKU_PROPERTY_ID`
FROM
`b_catalog_iblock` `catalog_catalog_iblock`
WHERE
`catalog_catalog_iblock`.`IBLOCK_ID` IN (14)
AND (
`catalog_catalog_iblock`.`PRODUCT_IBLOCK_ID` IS NOT NULL
AND `catalog_catalog_iblock`.`PRODUCT_IBLOCK_ID` <> 0
)
SELECT
CM.ID as ID,
CM.SYMBOL_RUS as SYMBOL_RUS,
CM.CODE as CODE,
CM.SYMBOL_INTL as SYMBOL_INTL
FROM
b_catalog_measure CM
WHERE
(
CM.ID IN (5)
)
++++++++++++++++++++++++++++++++++++++++++++
SELECT
`catalog_price`.`ID` AS `ID`,
`catalog_price`.`PRODUCT_ID` AS `PRODUCT_ID`,
`catalog_price`.`CATALOG_GROUP_ID` AS `CATALOG_GROUP_ID`,
`catalog_price`.`PRICE` AS `PRICE`,
`catalog_price`.`CURRENCY` AS `CURRENCY`,
`catalog_price`.`QUANTITY_FROM` AS `QUANTITY_FROM`,
`catalog_price`.`QUANTITY_TO` AS `QUANTITY_TO`,
`catalog_price`.`EXTRA_ID` AS `EXTRA_ID`
FROM
`b_catalog_price` `catalog_price`
WHERE
`catalog_price`.`PRODUCT_ID` IN (4368)
AND `catalog_price`.`CATALOG_GROUP_ID` IN (1)
ORDER BY
`catalog_price`.`PRODUCT_ID` ASC,
`catalog_price`.`CATALOG_GROUP_ID` ASC
SELECT
UG.GROUP_ID,
G.STRING_ID,
DATE_FORMAT(
UG.DATE_ACTIVE_FROM,
'%d.%m.%Y %H:%i:%s'
) as DATE_ACTIVE_FROM,
DATE_FORMAT(
UG.DATE_ACTIVE_TO,
'%d.%m.%Y %H:%i:%s'
) as DATE_ACTIVE_TO
FROM
b_user_group UG
INNER JOIN b_group G ON
G.ID = UG.GROUP_ID
WHERE
UG.USER_ID = 0
AND (
(UG.DATE_ACTIVE_FROM IS NULL)
OR (
UG.DATE_ACTIVE_FROM <= now()
)
)
AND (
(UG.DATE_ACTIVE_TO IS NULL)
OR (
UG.DATE_ACTIVE_TO >= now()
)
)
AND G.ACTIVE = 'Y' UNION SELECT
2,
'everyone',
NULL,
NULL
SELECT
`sale_internals_discount_module`.`DISCOUNT_ID` AS `DISCOUNT_ID`,
`sale_internals_discount_module`.`MODULE_ID` AS `MODULE_ID`
FROM
`b_sale_discount_module` `sale_internals_discount_module`
WHERE
`sale_internals_discount_module`.`DISCOUNT_ID` IN (1, 2, 3, 4, 5)
SELECT
`sale_internals_discount`.`ID` AS `ID`,
`sale_internals_discount`.`PRIORITY` AS `PRIORITY`,
`sale_internals_discount`.`SORT` AS `SORT`,
`sale_internals_discount`.`LAST_DISCOUNT` AS `LAST_DISCOUNT`,
`sale_internals_discount`.`LAST_LEVEL_DISCOUNT` AS `LAST_LEVEL_DISCOUNT`,
`sale_internals_discount`.`UNPACK` AS `UNPACK`,
`sale_internals_discount`.`APPLICATION` AS `APPLICATION`,
`sale_internals_discount`.`USE_COUPONS` AS `USE_COUPONS`,
`sale_internals_discount`.`EXECUTE_MODULE` AS `EXECUTE_MODULE`,
`sale_internals_discount`.`NAME` AS `NAME`,
`sale_internals_discount`.`CONDITIONS` AS `CONDITIONS_LIST`,
`sale_internals_discount`.`ACTIONS` AS `ACTIONS_LIST`,
`sale_internals_discount`.`ACTIVE_FROM` AS `ACTIVE_FROM`,
`sale_internals_discount`.`ACTIVE_TO` AS `ACTIVE_TO`,
`sale_internals_discount`.`PREDICTIONS` AS `PREDICTIONS_LIST`,
`sale_internals_discount`.`PREDICTIONS_APP` AS `PREDICTIONS_APP`,
`sale_internals_discount`.`PREDICTION_TEXT` AS `PREDICTION_TEXT`,
`sale_internals_discount`.`PRESET_ID` AS `PRESET_ID`,
`sale_internals_discount`.`CURRENCY` AS `CURRENCY`,
`sale_internals_discount`.`LID` AS `LID`,
`sale_internals_discount`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION`,
`sale_internals_discount`.`SHORT_DESCRIPTION` AS `SHORT_DESCRIPTION_STRUCTURE`
FROM
`b_sale_discount` `sale_internals_discount`
WHERE
`sale_internals_discount`.`ID` IN (1, 2, 3, 4, 5)
AND `sale_internals_discount`.`LID` = 's1'
AND `sale_internals_discount`.`EXECUTE_MODULE` IN (
'all',
'catalog'
)
AND (
(
`sale_internals_discount`.`ACTIVE_FROM` IS NULL
)
OR `sale_internals_discount`.`ACTIVE_FROM` <= '2017-07-08 13:02:56'
)
AND (
(
`sale_internals_discount`.`ACTIVE_TO` IS NULL
)
OR `sale_internals_discount`.`ACTIVE_TO` >= '2017-07-08 13:02:56'
)
AND `sale_internals_discount`.`USE_COUPONS` = 'N'
ORDER BY
`sale_internals_discount`.`PRIORITY` DESC,
`sale_internals_discount`.`SORT` ASC,
`sale_internals_discount`.`ID` ASC
SELECT
`catalog_price`.`ID` AS `ID`,
`catalog_price`.`CATALOG_GROUP_ID` AS `CATALOG_GROUP_ID`,
`catalog_price`.`PRICE` AS `PRICE`,
`catalog_price`.`CURRENCY` AS `CURRENCY`
FROM
`b_catalog_price` `catalog_price`
WHERE
`catalog_price`.`PRODUCT_ID` = 4368
AND `catalog_price`.`CATALOG_GROUP_ID` IN (1)
AND (
`catalog_price`.`QUANTITY_FROM` <= 1
OR (
`catalog_price`.`QUANTITY_FROM` IS NULL
OR `catalog_price`.`QUANTITY_FROM` = 0
)
)
AND (
`catalog_price`.`QUANTITY_TO` >= 1
OR (
`catalog_price`.`QUANTITY_TO` IS NULL
OR `catalog_price`.`QUANTITY_TO` = 0
)
)
SELECT
CAT_VAT. *,
CAT_PR.VAT_INCLUDED
FROM
b_catalog_product CAT_PR
LEFT JOIN b_iblock_element BE ON
(BE.ID = CAT_PR.ID)
LEFT JOIN b_catalog_iblock CAT_IB ON
(
(
CAT_PR.VAT_ID IS NULL
OR CAT_PR.VAT_ID = 0
)
AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID
)
LEFT JOIN b_catalog_vat CAT_VAT ON
(
CAT_VAT.ID = IF(
(
CAT_PR.VAT_ID IS NULL
OR CAT_PR.VAT_ID = 0
),
CAT_IB.VAT_ID,
CAT_PR.VAT_ID
)
)
WHERE
CAT_PR.ID = '4368'
AND CAT_VAT.ACTIVE = 'Y'
SELECT
BP. *
FROM
b_iblock_property BP,
b_iblock B
WHERE
BP.IBLOCK_ID = B.ID
AND B.ID IN (12)
AND UPPER(BP.CODE) = 'LINK'
SELECT DISTINCT
BE.ID as ID,
BE.IBLOCK_ID as IBLOCK_ID,
BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,
BE.NAME as NAME,
BE.PREVIEW_PICTURE as PREVIEW_PICTURE,
BE.PREVIEW_TEXT as PREVIEW_TEXT,
B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,
L.DIR as LANG_DIR,
BE.CODE as CODE,
BE.XML_ID as EXTERNAL_ID,
B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
B.LID as LID,
BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE
FROM
b_iblock B
INNER JOIN b_lang L ON
B.LID = L.LID
INNER JOIN b_iblock_element BE ON
BE.IBLOCK_ID = B.ID
INNER JOIN b_iblock_property FP0 ON
FP0.IBLOCK_ID = B.ID
AND FP0.CODE = 'LINK'
INNER JOIN b_iblock_element_property FPV0 ON
FPV0.IBLOCK_PROPERTY_ID = FP0.ID
AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
WHERE
1 = 1
AND (
(BE.IBLOCK_ID = '12')
AND (BE.ACTIVE = 'Y')
AND (
(
BE.ACTIVE_TO >= now()
OR BE.ACTIVE_TO IS NULL
)
AND (
BE.ACTIVE_FROM <= now()
OR BE.ACTIVE_FROM IS NULL
)
)
AND (FPV0.VALUE_NUM = '4368')
)
AND (
BE.WF_STATUS_ID = 1
AND BE.WF_PARENT_ELEMENT_ID IS NULL
)
SELECT
`iblock_property_enumeration`.`ID` AS `ID`,
`iblock_property_enumeration`.`PROPERTY_ID` AS `PROPERTY_ID`,
`iblock_property_enumeration`.`VALUE` AS `VALUE`,
`iblock_property_enumeration`.`SORT` AS `SORT`,
`iblock_property_enumeration`.`XML_ID` AS `XML_ID`
FROM
`b_iblock_property_enum` `iblock_property_enumeration`
WHERE
(
`iblock_property_enumeration`.`PROPERTY_ID` IN (47, 97, 49, 365, 366, 352, 348, 353, 354, 356, 357, 358, 359, 360, 361, 362, 363, 342, 341, 340, 344, 343, 345, 347, 349, 350, 346)
)
ORDER BY
`iblock_property_enumeration`.`PROPERTY_ID` ASC,
`iblock_property_enumeration`.`SORT` ASC,
`iblock_property_enumeration`.`VALUE` ASC
SELECT
BE.ID IBLOCK_ELEMENT_ID,
BEP.IBLOCK_PROPERTY_ID,
BEP.VALUE,
BEP.VALUE_NUM,
BEP.ID PROPERTY_VALUE_ID,
BEP.DESCRIPTION
FROM
b_iblock B
INNER JOIN b_lang L ON
B.LID = L.LID
INNER JOIN b_iblock_element BE ON
BE.IBLOCK_ID = B.ID
LEFT JOIN b_iblock_element_property BEP ON
BEP.IBLOCK_ELEMENT_ID = BE.ID
AND BEP.IBLOCK_PROPERTY_ID IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 68, 69, 72, 93, 94, 95, 97, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 352, 353, 354, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 374)
WHERE
1 = 1
AND (
(BE.ID = '4368')
AND (BE.IBLOCK_ID = '14')
)
AND (
BE.WF_STATUS_ID = 1
AND BE.WF_PARENT_ELEMENT_ID IS NULL
)
ORDER BY
BEP.IBLOCK_ELEMENT_ID,
BEP.IBLOCK_PROPERTY_ID,
BEP.ID
SELECT
`iblock_property`.`ID` AS `ID`,
`iblock_property`.`IBLOCK_ID` AS `IBLOCK_ID`,
`iblock_property`.`NAME` AS `NAME`,
`iblock_property`.`ACTIVE` AS `ACTIVE`,
`iblock_property`.`SORT` AS `SORT`,
`iblock_property`.`CODE` AS `CODE`,
`iblock_property`.`DEFAULT_VALUE` AS `DEFAULT_VALUE`,
`iblock_property`.`PROPERTY_TYPE` AS `PROPERTY_TYPE`,
`iblock_property`.`MULTIPLE` AS `MULTIPLE`,
`iblock_property`.`LINK_IBLOCK_ID` AS `LINK_IBLOCK_ID`,
`iblock_property`.`VERSION` AS `VERSION`,
`iblock_property`.`USER_TYPE` AS `USER_TYPE`,
`iblock_property`.`USER_TYPE_SETTINGS` AS `USER_TYPE_SETTINGS`
FROM
`b_iblock_property` `iblock_property`
WHERE
(
`iblock_property`.`ID` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 68, 69, 72, 93, 94, 95, 97, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 352, 353, 354, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 374)
)
AND `iblock_property`.`IBLOCK_ID` = 14
ORDER BY
`iblock_property`.`ID` ASC
SELECT DISTINCT
BS. *,
B.LIST_PAGE_URL,
B.SECTION_PAGE_URL,
B.IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
BS.XML_ID as EXTERNAL_ID,
DATE_FORMAT(
BS.TIMESTAMP_X,
'%d.%m.%Y %H:%i:%s'
) as TIMESTAMP_X,
DATE_FORMAT(
BS.DATE_CREATE,
'%d.%m.%Y %H:%i:%s'
) as DATE_CREATE
FROM
b_iblock_section BS
INNER JOIN b_iblock B ON
BS.IBLOCK_ID = B.ID
WHERE
1 = 1
AND (BS.ID = '259')
AND (BS.IBLOCK_ID = '14')
AND (BS.ACTIVE = 'Y')
AND (B.ID = '14')
AND (
B.ID IN (
SELECT
IBLOCK_ID
FROM
b_iblock_group IBG
WHERE
IBG.GROUP_ID IN (2)
AND IBG.PERMISSION >= 'R'
AND (
IBG.PERMISSION = 'X'
OR B.ACTIVE = 'Y'
)
)
OR (
B.RIGHTS_MODE = 'E'
AND EXISTS (
SELECT
SR.SECTION_ID
FROM
b_iblock_section_right SR
INNER JOIN b_iblock_right IBR ON
IBR.ID = SR.RIGHT_ID
INNER JOIN b_user_access UA ON
UA.ACCESS_CODE = IBR.GROUP_CODE
AND UA.USER_ID = 0
WHERE
SR.SECTION_ID = BS.ID
AND IBR.OP_SREAD = 'Y'
)
)
)
SHOW TABLES LIKE 'b_iblock_15_index_val'
SELECT
BP. *,
BEP.ID as PROPERTY_VALUE_ID,
BEP.VALUE,
BEP.DESCRIPTION,
BEPE.VALUE VALUE_ENUM,
BEPE.XML_ID VALUE_XML_ID,
BEPE.SORT VALUE_SORT
FROM
b_iblock B
INNER JOIN b_iblock_property BP ON
B.ID = BP.IBLOCK_ID
LEFT JOIN b_iblock_element_property BEP ON
(
BP.ID = BEP.IBLOCK_PROPERTY_ID
AND BEP.IBLOCK_ELEMENT_ID = 4368
)
LEFT JOIN b_iblock_property_enum BEPE ON
(
BP.PROPERTY_TYPE = 'L'
AND BEPE.ID = BEP.VALUE_ENUM
AND BEPE.PROPERTY_ID = BP.ID
)
WHERE
B.ID = 14
AND BP.CODE LIKE 'ASSOCIATED'
ORDER BY
BP.SORT ASC,
BP.ID ASC,
BEPE.SORT ASC,
BEP.ID ASC
SELECT
TAG
FROM
b_cache_tag
WHERE
SITE_ID = 's1'
AND CACHE_SALT = '/59d'
AND RELATIVE_PATH = '/COptimusCache/iblock/CIBlockElement_GetProperty/aspro_optimus_catalog14/'
SELECT
CM.ID as ID,
CM.CODE as CODE,
CM.MEASURE_TITLE as MEASURE_TITLE,
CM.SYMBOL_RUS as SYMBOL_RUS,
CM.SYMBOL_INTL as SYMBOL_INTL,
CM.SYMBOL_LETTER_INTL as SYMBOL_LETTER_INTL,
CM.IS_DEFAULT as IS_DEFAULT
FROM
b_catalog_measure CM
WHERE
(CM.IS_DEFAULT = 'Y')
Компания Акрасим!..