sql_c = "INSERT INTO " + name + " (`date`,`time`,`timestamp`,`name`,`price`,`chng_1`,`chng_2`,`chng_3`,`chng_4`,`chng_5`,`chng_10`,`chng_15`,`chng_30`,`chng_60`,`chng_day`) VALUES %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
CREATE TABLE users (
id int primary key auto_increment,
name varchar(64),
status enum('single', 'married', 'divorced')
);
INSERT INTO users (name, status) VALUES
('Peter', 'single'),
('Basil', 'single'),
('Peter', 'married'),
('Basil', 'married'),
('Basil', 'divorced');
SELECT * FROM (
SELECT
name,
status,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) rn
FROM users) user_status
WHERE rn = 1;
select
:id as person,
IF (person_1 = :id, person_2, person_1) person_contact
from persons
where person_1 = :id or person_2 = :id
;
SELECT
device_name,
COUNT(*) cnt
FROM users
WHERE
YEAR(created_date) = 2020 AND
year_of_birth < DATE_SUB(CURDATE(), INTERVAL 25 YEAR)
GROUP BY device_name;
SELECT *
FROM comment
WHERE (id = 1 AND reply_id IS NULL) OR reply_id = 1;
UPDATE
`products`
SET `price` = ROUND((`price_z` / 100) * (
CASE
WHEN `price_z` BETWEEN 1 AND 100000 THEN 110
WHEN `price_z` BETWEEN 100001 AND 200000 THEN 109
WHEN `price_z` BETWEEN 200001 AND 300000 THEN 108
ELSE 107
END
))
WHERE `date_price` = CURDATE();
select
oi.id,
(oi.price * coalesce(wi.quantity, 0)) wi_sum,
(oi.price * coalesce(ai.quantity, 0)) ai_sum,
(oi.price * coalesce(least(wi.quantity, ai.quantity), 0)) both_sum
from order_items oi
left join waybills_items wi on wi.order_item_id = oi.id
left join acts_items ai on ai.order_item_id = oi.id
order by oi.id;
UPDATE tbl
SET link = CONCAT(
'<p>[playerjs file:\"',
MID(LEFT(link, LOCATE('?', link, LOCATE('src="', link))-1), LOCATE('src="', link)+5),
'\"]</p>'
);
в языке SQL версии СУБД MySQL предпочтительно заключение названий таблиц и их полей в косые кавычки, поскольку это предотвращает путаницу с зарезервированными именами. Например, SELECT * FROM WHERE вызовет ошибку, а SELECT * FROM `WHERE` — нет (при условии, что существует таблица WHERE).
mysqli_query($mysqli, "CREATE TABLE `$game_name` (
`game_id` INT(11) DEFAULT $output[id],
`name` VARCHAR(40) NOT NULL,
`image` VARCHAR(100),
`descr` VARCHAR(100) NOT NULL,
`percents` VARCHAR(6),
`making` TEXT(1000),
`vid_link` VARCHAR(100)
)");
Зачем нормализовать базу данных?
У Вас может возникнуть вопрос – а зачем вообще нормализовать базу данных и бороться с этой избыточностью?
Дело в том, что избыточность данных создает предпосылки для появления различных аномалий, снижает производительность, и делает управление данными не гибким и не очень удобным. Отсюда можно сделать вывод, что нормализация нужна для:
Устранения аномалий
Повышения производительности
Повышения удобства управления данными
select *
from messages
join (
select
-- создаем JSON ARRAY из id и берем 2 последних элемента
IF (
JSON_LENGTH(json_arrayagg(id))>2,
JSON_EXTRACT(json_arrayagg(id), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-2,"]"), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-1,"]")),
json_arrayagg(id)
) ids,
group_id
from
messages
group by group_id
) last_messages on json_contains(ids, CAST(messages.id AS JSON))
;
DELETE
FROM cl_purchases
WHERE id IN (
SELECT id
FROM cl_purchases
WHERE point = 600258
AND date BETWEEN '2020-03-01 00:00:00' AND '2020-03-31 23:59:59'
AND order_type = 'delivery'
AND provider <> 'iikodelivery'
GROUP BY order_id
HAVING COUNT( id ) > 1
)
create table types (
id int auto_increment primary key,
title varchar(255)
);
insert into types (title) values ('Одежда');
create table categories (
id int auto_increment primary key,
type_id int,
title varchar(255),
foreign key (type_id) references types(id)
);
insert into categories (type_id, title) values (1, 'Брюки'), (1, 'Рубашки');
create table goods (
id int auto_increment primary key,
category_id int,
title varchar(255),
foreign key goods_category (category_id) references categories(id)
);
insert into goods (category_id, title) values (1, 'Брюки мужские'), (2, 'Рубашка поло спорт');
select
goods.id,
types.title as type,
categories.title as category,
goods.title
from goods
join categories on goods.category_id = categories.id
join types on categories.type_id = types.id
;
SELECT
user_id,
MIN(current_status) current_status,
MIN(dateadd) dateadd,
status_changed
FROM
(
SELECT
tt.*,
MIN(tt1.dateadd) as status_changed
FROM
`test_table` tt
LEFT JOIN `test_table` tt1 ON tt1.user_id = tt.user_id
AND tt1.dateadd > tt.dateadd
AND tt1.current_status <> tt.current_status
GROUP BY
tt.id,
tt.current_status,
tt.user_id
) aggregated
GROUP BY
user_id,
status_changed
ORDER BY
dateadd;
+=========+================+=====================+=====================+
| user_id | current_status | dateadd | status_changed |
+=========+================+=====================+=====================+
| 3 | new | 2020-10-01 03:00:59 | 2020-10-11 02:00:59 |
+---------+----------------+---------------------+---------------------+
| 3 | old | 2020-10-11 02:00:59 | 2020-10-11 05:00:59 |
+---------+----------------+---------------------+---------------------+
| 3 | new | 2020-10-11 05:00:59 | 2020-11-01 03:00:59 |
+---------+----------------+---------------------+---------------------+
| 3 | old | 2020-11-01 03:00:59 | 2020-11-12 03:00:59 |
+---------+----------------+---------------------+---------------------+
| 3 | new | 2020-11-12 03:00:59 | 2020-11-15 03:00:59 |
+---------+----------------+---------------------+---------------------+
| 3 | old | 2020-11-15 03:00:59 | (null) |
+---------+----------------+---------------------+---------------------+
create table `calendar` (
`event` varchar(64),
`year` smallint unsigned,
`month` tinyint unsigned,
`day` tinyint unsigned,
`hour` tinyint unsigned,
`minute` tinyint unsigned
);
insert into calendar values
('December 31 every year at 23:55', null, 12, 31, 23, 55),
('every minute in december 2020', 2020, 12, null, null, null),
('every hour at first of month', null, null, 1, null, 0)
;
select `event`
from `calendar`
where
(`year` is null or `year` = year(now())) and
(`month` is null or `month` = month(now())) and
(`day` is null or `day` = day(now())) and
(`hour` is null or `hour` = hour(now())) and
(`minute` is null or `minute` = day(now()))
;
select *
from myproducts
join (
select distinct product_id
from myproducts
order by product_id
limit 20 -- количество уникальных товаров
) unique_products on unique_products.product_id = myproducts.product_id;