Category Archives: SQL

Sql и все что с ним связано)

Mysql изменить текущее дату и время

date_add(now(), interval -1 hour) # получим текущее дату и время - 1 час
date_add(now(), interval +1 hour) # получим текущее дату и время + 1 час

date_sub(now(), interval 1 hour) # Получим текущее дату и время - 1 час
date_sub(now(), interval -1 hour) # Получим текущее дату и время + 1 час

date_add(now(), interval -1 hour) # получим текущее дату и время - 1 час

date_add(now(), interval +1 hour) # получим текущее дату и время + 1 час

date_sub(now(), interval 1 hour) # Получим текущее дату и время - 1 час

date_sub(now(), interval -1 hour) # Получим текущее дату и время + 1 час

Подробнее

Изменить кодировку для таблицы/базы данных

Изменить кодировку базы данных

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

1	ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Изменить кодировку для таблицы

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

1	ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Источник

PostgreSQL обновление AutoIncrement

Для обновления индекса в postgre используем следующую команду

ALTER SEQUENCE request_queue_id_seq RESTART WITH 4422;

1	ALTER SEQUENCE request_queue_id_seq RESTART WITH 4422;

SQL select phone

Sql phone sql получить из строки только цифры sql регулярные выражения Sql запрос для получения номера телефона без букв, пробелов, и спец символов

select *, 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '+', ''), '(', ''), ')', '') as 'Телефон' 
from table

select *,

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '+', ''), '(', ''), ')', '') as 'Телефон'

from table

Если версия mysql >= 8

select regexp_replace(phone, '[^[:digit:]]', '') from table

1	select regexp_replace(phone, '[^[:digit:]]', '') from table

mysql, sql

sql find float value

Sql поиск в varchar как float Поиск числа в типе поля string

cast(props.value as decimal(10,4))

1	cast(props.value as decimal(10,4))

Документация: http://www.codenet.ru/db/interbase/ibsql/data.php#DECIMAL https://docs.microsoft.com/ru-ru/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017

Большой страшный sql запрос Интернет магазина для Яндекс маркета

SELECT 
    product.goods_code as 'id',
    'В наличии' as 'Статус товара',
    'Есть' as 'Доставка',
    ' ' as 'Стоимость доставки',
    ' ' as 'Срок доставки',
    'Есть' as 'Самовывоз',
    'Можно' as 'Купить в магазине без заказа',
    
    CASE 
        WHEN price.price = (
            select price 
            FROM shop_goods_base 
            where goods_id = product.id 
            AND base_id NOT IN (select id from `base` where hide_contacts = 1)
            ORDER BY price 
            DESC LIMIT 1
        ) 
            THEN CONCAT('https://site.ru/', category.path,'/', product.path) 
        ELSE 
            CONCAT('https://', (
                select domain from base where id =          
                    (
                        select base_id 
                        FROM shop_goods_base 
                        where goods_id = product.id 
                        AND base_id NOT IN (select id from `base` where hide_contacts = 1)
                        ORDER BY price 
                        DESC LIMIT 1
                    )
                ),
                '.site.ru/', 
                category.path, '/', product.path
            ) 
    END AS 'Ссылка на товар на сайте магазина',
    
    brand.name as 'Производитель',
    product.name as 'Название',  
    category.name as 'Категория',
    
    (
        select price 
        FROM shop_goods_base 
        where goods_id = product.id 
        AND base_id NOT IN (select id from `base` where hide_contacts = 1)
        ORDER BY price 
        DESC LIMIT 1
    ) as 'Цена',
    
    '' as 'Цена без скидки',
    'RUR' as 'Валюта',
    
    CONCAT('https://site.ru/u/shop/', SUBSTRING(product.goods_code, 1, 3), '/orig/', image.file) as 'Ссылка на картинку',
    product.description as 'Описание',
    
        CONCAT_WS(
        "",
        (
            select  GROUP_CONCAT('Цвет|', sc.header, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_color as sc
            ON sfv.int_value = sc.id
            where sfv.filter_set_id = 7 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Длина|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 9 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Ширина|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 35 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Высота|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 32 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Пустотность|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 196 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Тип|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 43 and sfv.goods_id = product.id
            limit 1
        ),
        
        (
            select  GROUP_CONCAT('Материал|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 18 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Диаметр|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 8 and sfv.goods_id = product.id
            limit 1
        )
        
    ) as 'Характеристики товара',
    
    'Необходима предоплата.' as 'Условия продажи',
    'Есть' as 'Гарантия производителя',
    country.xart_header as 'Страна происхождения',
    product.goods_code as 'Штрихкод',
    '80' as 'bid'

FROM shop_goods as product

LEFT JOIN shop_goods_base as price
ON product.id = price.goods_id

LEFT JOIN shop_catalog_goods as cat_table
ON product.id = cat_table.goods_id

LEFT JOIN shop_catalog as category
ON cat_table.catalog_id = category.id

LEFT JOIN shop_brand as brand
ON product.brand_id = brand.id

LEFT JOIN shop_country as country
ON product.country_id = country.id

LEFT JOIN shop_image as image
ON product.id = image.goods_id

WHERE product.goods_code IN (1,2,3,4,5) 
GROUP BY product.goods_code

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

SELECT

product.goods_code as 'id',

'В наличии' as 'Статус товара',

'Есть' as 'Доставка',

' ' as 'Стоимость доставки',

' ' as 'Срок доставки',

'Есть' as 'Самовывоз',

'Можно' as 'Купить в магазине без заказа',

CASE

WHEN price.price = (

select price

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

)

THEN CONCAT('https://site.ru/', category.path,'/', product.path)

ELSE

CONCAT('https://', (

select domain from base where id =

(

select base_id

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

)

'.site.ru/',

category.path, '/', product.path

)

END AS 'Ссылка на товар на сайте магазина',

brand.name as 'Производитель',

product.name as 'Название',

category.name as 'Категория',

(

select price

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

) as 'Цена',

'' as 'Цена без скидки',

'RUR' as 'Валюта',

CONCAT('https://site.ru/u/shop/', SUBSTRING(product.goods_code, 1, 3), '/orig/', image.file) as 'Ссылка на картинку',

product.description as 'Описание',

CONCAT_WS(

"",

(

select GROUP_CONCAT('Цвет|', sc.header, ';')

from shop_filter_value as sfv

LEFT JOIN shop_color as sc

ON sfv.int_value = sc.id

where sfv.filter_set_id = 7 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Длина|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 9 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Ширина|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 35 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Высота|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 32 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Пустотность|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 196 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Тип|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 43 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Материал|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 18 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Диаметр|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 8 and sfv.goods_id = product.id

limit 1

)

) as 'Характеристики товара',

'Необходима предоплата.' as 'Условия продажи',

'Есть' as 'Гарантия производителя',

country.xart_header as 'Страна происхождения',

product.goods_code as 'Штрихкод',

'80' as 'bid'

FROM shop_goods as product

LEFT JOIN shop_goods_base as price

ON product.id = price.goods_id

LEFT JOIN shop_catalog_goods as cat_table

ON product.id = cat_table.goods_id

LEFT JOIN shop_catalog as category

ON cat_table.catalog_id = category.id

LEFT JOIN shop_brand as brand

ON product.brand_id = brand.id

LEFT JOIN shop_country as country

ON product.country_id = country.id

LEFT JOIN shop_image as image

ON product.id = image.goods_id

WHERE product.goods_code IN (1,2,3,4,5)

GROUP BY product.goods_code

Вывести одной строкой несколько значений из таблицы

Есть запрос

select * from shop_product_props
where prop_id in (559, 799)

1 2	select * from shop_product_props where prop_id in (559, 799)

А нужно сгруппировать результат по товару, и получить его значения Запрос должен быть следующим (Запрос занял 0.0118 сек.)

select product_id, 
MAX(if(prop_id=559,value,NULL)) as brand, 
MAX(if(prop_id=799,value,NULL)) as mark 
from shop_product_props
where prop_id in (559, 799) GROUP BY 1

select product_id,

MAX(if(prop_id=559,value,NULL)) as brand,

MAX(if(prop_id=799,value,NULL)) as mark

from shop_product_props

where prop_id in (559, 799) GROUP BY 1

Еще вариант (Запрос занял 0.2479 сек.)

select
    spp.product_id,
    spp1.value as brand,
    spp2.value as mark
from (
    select distinct product_id
    from shop_product_props
    where prop_id in (559, 799)
) as spp
    left join shop_product_props as spp1 on spp.product_id = spp1.product_id and spp1.prop_id = 559
    left join shop_product_props as spp2 on spp.product_id = spp2.product_id and spp2.prop_id = 799

select

spp.product_id,

spp1.value as brand,

spp2.value as mark

from (

select distinct product_id

from shop_product_props

where prop_id in (559, 799)

) as spp

left join shop_product_props as spp1 on spp.product_id = spp1.product_id and spp1.prop_id = 559

left join shop_product_props as spp2 on spp.product_id = spp2.product_id and spp2.prop_id = 799

Datetime to Date and interval

Преобразовать дату с временем (datetime) в дату

date(created_at)

1	date(created_at)

Модифицировать текущую дату на ± дней

date(DATE_ADD(NOW(), INTERVAL -5 DAY)) -- Минус 5 дней
date(DATE_ADD(NOW(), INTERVAL 5 DAY))  -- Плюс 5 дней

1 2	date(DATE_ADD(NOW(), INTERVAL -5 DAY)) -- Минус 5 дней date(DATE_ADD(NOW(), INTERVAL 5 DAY)) -- Плюс 5 дней

Пример #1

SELECT * FROM `shop_cart` 
where ordered = 0 
AND modify_date < date(DATE_ADD(NOW(), INTERVAL -14 DAY)) 
ORDER BY `shop_cart`.`modify_date` DESC

SELECT * FROM `shop_cart`

where ordered = 0

AND modify_date < date(DATE_ADD(NOW(), INTERVAL -14 DAY))

ORDER BY `shop_cart`.`modify_date` DESC

Выбрать заказы за сегодня

SELECT count(*) as 'кол-во заказов', SUM(order_total) as 'Общая сумма' FROM shop_order where date(reg_date) = date(NOW())

1	SELECT count(*) as 'кол-во заказов', SUM(order_total) as 'Общая сумма' FROM shop_order where date(reg_date) = date(NOW())

Выбрать заказы за вчера

SELECT * FROM shop_order where date(reg_date) = date(DATE_ADD(NOW(), INTERVAL -1 DAY))

1	SELECT * FROM shop_order where date(reg_date) = date(DATE_ADD(NOW(), INTERVAL -1 DAY))

Postgres команды

\? Справка по командам \l Просмотр существующих БД \l+ Просмотр БД детально \c db_name Войти в БД \dt Просмотр текущих таблиц в БД Источник : https://kamaok.org.ua/?p=961

postgres drop all tables

Удалим схему и создадим опять ее же)

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

1 2	DROP SCHEMA public CASCADE; CREATE SCHEMA public;

Дадим привелегии

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

1 2	GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;