k9mDrbjt

1 Мне кажется, что стоит создать 4 таблицы, так как связь между таблицами `Orders` 
2 и `Products` можно определить как М:М - Каждый заказ может включать в себя несколько продуктов,
3 с другой стороны - каждый из продуктов может содержаться в нескольких заказах.
4 ***************************************************************************************************
51. Создание БД shop:
6CREATE SCHEMA IF NOT EXISTS `shop` DEFAULT CHARACTER SET utf8 ;
7USE `shop` ;   
8///////////////////////////////////////////////////////////////
92. Создание таблиц
10********************************
11CREATE TABLE IF NOT EXISTS `shop`.`products` (
12  `product_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
13  `model` VARCHAR(255) NOT NULL,
14  `cost` DECIMAL(16,5) UNSIGNED NOT NULL,
15  PRIMARY KEY (`product_id`))
16ENGINE = InnoDB;
17************************************
18CREATE TABLE IF NOT EXISTS `shop`.`Users` (
19  `uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
20  `name` VARCHAR(255) NOT NULL,
21  `email` VARCHAR(255) NOT NULL,
22  PRIMARY KEY (`uid`))
23ENGINE = InnoDB;
24********************************
25CREATE TABLE IF NOT EXISTS `shop`.`Orders` (
26  `order_id` INT(10) UNSIGNED NOT NULL,
27  `status` VARCHAR(255) NOT NULL,
28  `Users_uid` INT(10) UNSIGNED NOT NULL,
29  PRIMARY KEY (`order_id`),
30  INDEX `fk_Orders_Users1_idx` (`Users_uid` ASC),
31  CONSTRAINT `fk_Orders_Users1`
32    FOREIGN KEY (`Users_uid`)
33    REFERENCES `shop`.`Users` (`uid`)
34    ON DELETE NO ACTION
35    ON UPDATE NO ACTION)
36ENGINE = InnoDB;
37****************************
38CREATE TABLE IF NOT EXISTS `shop`.`Orders_has_products` (
39  `Orders_order_id` INT(10) UNSIGNED NOT NULL,
40  `products_product_id` INT(10) UNSIGNED NOT NULL,
41  PRIMARY KEY (`Orders_order_id`, `products_product_id`),
42  INDEX `fk_Orders_has_products_products1_idx` (`products_product_id` ASC),
43  INDEX `fk_Orders_has_products_Orders1_idx` (`Orders_order_id` ASC),
44  CONSTRAINT `fk_Orders_has_products_Orders1`
45    FOREIGN KEY (`Orders_order_id`)
46    REFERENCES `shop`.`Orders` (`order_id`)
47    ON DELETE NO ACTION
48    ON UPDATE NO ACTION,
49  CONSTRAINT `fk_Orders_has_products_products1`
50    FOREIGN KEY (`products_product_id`)
51    REFERENCES `shop`.`products` (`product_id`)
52    ON DELETE NO ACTION
53    ON UPDATE NO ACTION)
54ENGINE = InnoDB;
55//////////////////////////////////////////////////////////////////////////////
563. Добавление данных в таблицы
573.1 Вставка данных в таблицу продукты
58START TRANSACTION;
59USE `shop`;
60INSERT INTO `shop`.`products` (`product_id`, `model`, `cost`) VALUES (1025, '16-19-15214-KL_1', 10.35);
61INSERT INTO `shop`.`products` (`product_id`, `model`, `cost`) VALUES (2005, '16-19-15658-KL_5', 20.69);
62INSERT INTO `shop`.`products` (`product_id`, `model`, `cost`) VALUES (1758, '12-10-125D5-MV_3', 15.65);
63INSERT INTO `shop`.`products` (`product_id`, `model`, `cost`) VALUES (1432, '20-85-15214-SL_1', 50.02);
64COMMIT;
653.2 Вставка данных в таблицу пользователи
66START TRANSACTION;
67USE `shop`;
68INSERT INTO `shop`.`Users` (`uid`, `name`, `email`) VALUES (1, 'Dmitriy', 'dmitriy@gmail.com');
69INSERT INTO `shop`.`Users` (`uid`, `name`, `email`) VALUES (2, 'Vladislava', 'vladka@mail.ru');
70INSERT INTO `shop`.`Users` (`uid`, `name`, `email`) VALUES (3, 'Evgeniy', 'jeka112@bk.ru');
71INSERT INTO `shop`.`Users` (`uid`, `name`, `email`) VALUES (4, 'Andrey', 'Andrey@gmail.com');
72COMMIT;
733.3 Вставка данных в таблицу заказы
74START TRANSACTION;
75USE `shop`;
76INSERT INTO `shop`.`orders` (`order_id`, `status`, `Users_uid`) VALUES (1, 'cancel', 2);
77INSERT INTO `shop`.`orders` (`order_id`, `status`, `Users_uid`) VALUES (2, 'in-process', 2);
78INSERT INTO `shop`.`orders` (`order_id`, `status`, `Users_uid`) VALUES (4, 'completed', 3);
79INSERT INTO `shop`.`orders` (`order_id`, `status`, `Users_uid`) VALUES (5, 'completed', 3);
80INSERT INTO `shop`.`orders` (`order_id`, `status`, `Users_uid`) VALUES (6, 'in-process', 3);
81INSERT INTO `shop`.`orders` (`order_id`, `status`, `Users_uid`) VALUES (7, 'in-process', 4);
82COMMIT;
833.4 Вставка данных в смежную таблицу
84START TRANSACTION;
85USE `shop`;
86INSERT INTO `orders_has_products` (`Orders_order_id`, `products_product_id`) VALUES (6, 1432);
87INSERT INTO `orders_has_products` (`Orders_order_id`, `products_product_id`) VALUES (1, 1758);
88INSERT INTO `orders_has_products` (`Orders_order_id`, `products_product_id`) VALUES (6, 1758);
89INSERT INTO `orders_has_products` (`Orders_order_id`, `products_product_id`) VALUES (1, 2005);
90COMMIT;
91///////////////////////////////////////////////////////////////////////////////////////////
924-5.
93/*Orders in-process:*/
94SELECT users.uid, users.name, users.email FROM orders, users WHERE orders.status="in-process" AND orders.Users_uid = users.uid
95/*Update costs:*/
96UPDATE products SET products.cost=products.cost+300