Зачем нормализовать базу данных?
У Вас может возникнуть вопрос – а зачем вообще нормализовать базу данных и бороться с этой избыточностью?
Дело в том, что избыточность данных создает предпосылки для появления различных аномалий, снижает производительность, и делает управление данными не гибким и не очень удобным. Отсюда можно сделать вывод, что нормализация нужна для:
Устранения аномалий
Повышения производительности
Повышения удобства управления данными
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;SELECT title, type
FROM (
SELECT
title,
type,
row_number() over (partition by type order by id) rnum
FROM test) numered
WHERE numered.rnum < 3
;ADD COLUMN IF NOT EXISTSреализован только в MariaDB, но не существует в MySQL 5-8.
SELECT VERSION();
SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи'
FROM `article`
LEFT JOIN `photo` ON `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
WHERE `article_id` IS NULL
GROUP BY `article`.`id`SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи'
FROM `article`
WHERE NOT EXISTS (
SELECT `article_id`
FROM `photo`
WHERE `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
);con.query(`UPDATE promocode SET activated = activated + 1 WHERE promo = ${promo}`)SELECT *
FROM devices
WHERE breaking > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
;ALTER TABLE tbl_publication
ADD INDEX status_ix (status),
ADD INDEX type_ix (type);ALTER TABLE project_list
CHANGE COLUMN proj_id proj_id1 INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (proj_id1);select table1.*
from table1
join table2 on table1.date between table2.date_from and table2.date_to
;WHERE DATE_FORMAT(column_1, '%Y-%m') = '2020-10';