querries.sql

USE Yeti_Kamaev;
/*ДОБАВИТЬ КАТЕГОРИИ*/
INSERT INTO categories(title, uniq_code)
VALUES ('Доски и лыжи', 'boards'),
       ('Крепления','attachment'),
       ('Ботинки','boots'),
       ('Одежда', 'clothing'),
       ('Инструменты','tools'),
       ('Разное','other');
/*ДОБАВИТЬ ПОЛЬЗОВАТЕЛЕЙ*/
INSERT INTO users(registry_date, nickname , e_mail, password)
VALUES (CAST('2022-10-10' AS DateTime), 'Даниил', 'Daniil@mail.ru', '$2y$10$azTPD8NrWOdSm5GIrE5J/OE9Usc5JB4rq4CpR4xYv7Yc2Yb01ODXW'),
       (CAST('2022-10-08' AS DateTime), 'Денис', 'Denis@mail.ru', '$2y$10$azTPD8NrWOdSm5GIrE5J/OE9Usc5JB4rq4CpR4xYv7TEJGJY579GHN');
/*ДОБАВИТЬ ЛОТЫ*/
INSERT INTO lots(id_category, id_user_author , id_user_win,create_date,  good_name ,good_discription ,url_img ,close_date, price,step_price)
VALUES (1,1,1,CAST('2022-10-10' AS DateTime), '2014 Rossignol District Snowboard', '_', 'img/lot-1.jpg', CAST('2022-10-13' AS DateTime),10999,950),
       (2,1,NULL,CAST('2022-10-10' AS DateTime), 'DC Ply Mens 2016/2017 Snowboard', '_', 'img/lot-2.jpg', CAST('2022-12-09 23:59' AS DateTime),159999,15000),
       (3,1,NULL,CAST('2022-10-10' AS DateTime), 'Крепления Union Contact Pro 2015 года размер L/XL', '_', 'img/lot-3.jpg', CAST('2022-12-10 23:59' AS DateTime),8000,500),
       (4,1,NULL,CAST('2022-10-10' AS DateTime), 'Ботинки для сноуборда DC Mutiny Charocal', '_', 'img/lot-4.jpg', CAST('2022-12-11 23:59' AS DateTime),10999,700),
       (5,1,NULL,CAST('2022-10-10' AS DateTime), 'Куртка для сноуборда DC Mutiny Charocal', '_', 'img/lot-5.jpg', CAST('2022-12-12 23:59' AS DateTime),7500,750),
       (6,1,NULL,CAST('2022-10-10' AS DateTime), 'Маска Oakley Canopy', '_', 'img/lot-6.jpg', CAST('2022-10-13 23:59' AS DateTime),5400,500);
/*ДОБАВИТЬ СТАВКИ*/
INSERT INTO bets(id, id_user_made, id_lot, date_time, cost)
VALUES (1,1,1,CAST('2022-10-10' AS DATETIME),8200),
       (2,1,1,CAST('2022-10-09' AS DATETIME),8100),
       (3,2,2,CAST('2022-10-10' AS DATETIME),9000);
CREATE FULLTEXT INDEX lot_search ON lots(good_name,good_discription);
/*ПОЛУЧИТЬ ВСЕ КАТЕГОРИИ*/
SELECT * FROM categories;
/*ПОЛУЧИТЬ 3 ПОСЛЕДНИХ ЛОТА*/
SELECT * FROM lots WHERE id_user_win IS NULL ORDER BY create_date DESC LIMIT 3;
/*ПОЛУЧИТЬ ЛОТ ПО ID*/
SELECT * FROM lots WHERE id = 1;
/*ОБНОВИТЬ НАЗВАНИЕ ЛОТА ПО ID*/
UPDATE lots SET good_name='ПОНЧИК С ШОКОЛАДОМ' WHERE id=1;
/*ПОЛУЧИТЬ СПИСОК СТАВОК ПО ID С СОРТИРОВКОЙ ПО ДАТЕ*/
SELECT * FROM bets WHERE id_lot = 1 ORDER BY date_time DESC;