· 6 years ago · Jul 11, 2019, 04:54 PM
1-- MySQL dump 10.13 Distrib 8.0.16, for Win64 (x86_64)
2--
3-- Host: localhost Database: bookshop
4-- ------------------------------------------------------
5-- Server version 8.0.16
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 SET NAMES utf8mb4 ;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Table structure for table `book`
20--
21
22DROP TABLE IF EXISTS `book`;
23/*!40101 SET @saved_cs_client = @@character_set_client */;
24 SET character_set_client = utf8mb4 ;
25CREATE TABLE `book` (
26 `idbook` int(11) NOT NULL AUTO_INCREMENT,
27 `title` varchar(45) NOT NULL,
28 `author` varchar(45) NOT NULL,
29 `pubyear` year(4) DEFAULT NULL,
30 `genre` set('программирование','детектив','классика','фантастика') DEFAULT NULL,
31 `price` decimal(6,2) NOT NULL,
32 `quantity` smallint(6) DEFAULT NULL,
33 PRIMARY KEY (`idbook`)
34) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
35/*!40101 SET character_set_client = @saved_cs_client */;
36
37--
38-- Dumping data for table `book`
39--
40
41LOCK TABLES `book` WRITE;
42/*!40000 ALTER TABLE `book` DISABLE KEYS */;
43INSERT INTO `book` VALUES (1,'PHP','Автор1',2017,'программирование',1320.00,10),(2,'MySQL','Автор1',2018,'программирование',737.55,1),(3,'JavaScript','Автор2',2019,'программирование',1969.00,20),(4,'React и Redux','Автор3',2018,'программирование',2310.00,1),(5,'Восточный экспресс','Агата Кристи',2016,'детектив',115.50,2),(6,'Десят негритят','Агата Кристи',2011,'детектив,классика',802.45,10),(7,'Братья Карамазовы','Достоевский Фёдор Михайлович',1985,'классика',572.00,5),(8,'Игрок','Достоевский Фёдор Михайлович',2014,'классика',495.55,2),(9,'Идиот','Достоевский Фёдор Михайлович',2016,'классика',140.80,2),(10,'Мастодония','Клиффорд Саймак',1993,'фантастика',394.90,1),(11,'Принц и нищий','Марк Твен',2016,'фантастика',152.90,2),(12,'Приключения Тома Сойера','Марк Твен',2017,'классика',179.30,2),(13,'Робинзон Крузо','Даниэль Дефо',1992,'классика',658.90,6);
44/*!40000 ALTER TABLE `book` ENABLE KEYS */;
45UNLOCK TABLES;
46/*!50003 SET @saved_cs_client = @@character_set_client */ ;
47/*!50003 SET @saved_cs_results = @@character_set_results */ ;
48/*!50003 SET @saved_col_connection = @@collation_connection */ ;
49/*!50003 SET character_set_client = cp866 */ ;
50/*!50003 SET character_set_results = cp866 */ ;
51/*!50003 SET collation_connection = cp866_general_ci */ ;
52/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
53/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
54DELIMITER ;;
55/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `changeBook` BEFORE UPDATE ON `book` FOR EACH ROW BEGIN
56 IF NEW.price <> OLD.price THEN
57 INSERT INTO log
58 VALUES (NULL, user(), now(), OLD.idbook, OLD.price, NEW.price, OLD.quantity );
59 END IF;
60 END */;;
61DELIMITER ;
62/*!50003 SET sql_mode = @saved_sql_mode */ ;
63/*!50003 SET character_set_client = @saved_cs_client */ ;
64/*!50003 SET character_set_results = @saved_cs_results */ ;
65/*!50003 SET collation_connection = @saved_col_connection */ ;
66
67--
68-- Table structure for table `customer`
69--
70
71DROP TABLE IF EXISTS `customer`;
72/*!40101 SET @saved_cs_client = @@character_set_client */;
73 SET character_set_client = utf8mb4 ;
74CREATE TABLE `customer` (
75 `idcustomer` int(11) NOT NULL AUTO_INCREMENT,
76 `first_name` varchar(45) NOT NULL,
77 `last_name` varchar(45) DEFAULT NULL,
78 `bd` date DEFAULT NULL,
79 `phone` char(10) NOT NULL,
80 `email` varchar(45) NOT NULL,
81 PRIMARY KEY (`idcustomer`),
82 UNIQUE KEY `phone_UNIQUE` (`phone`),
83 UNIQUE KEY `email_UNIQUE` (`email`)
84) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
85/*!40101 SET character_set_client = @saved_cs_client */;
86
87--
88-- Dumping data for table `customer`
89--
90
91LOCK TABLES `customer` WRITE;
92/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
93INSERT INTO `customer` VALUES (1,'Анна','Иванова','1995-03-19','1234567890','someemail1@mail.ru'),(2,'Петр','Ганев','2003-12-20','2345678901','someemail2@yandex.ru'),(3,'Федор','Бойков','1985-08-10','3456789012','someemail3@gmail.com'),(4,'Иван','Петров','2000-01-01','4567890123','someemail4@hotmail.com'),(5,'Джон','Смит','1965-05-17','5678901234','someemail5@rabmler.ru'),(6,'Екатерина','Великая','1990-03-14','6789012345','someemail6@mail.ru'),(7,'Наталья','Плохая','1975-11-15','7890123456','someemail7@mail.ru'),(8,'Пьер','Моро','1998-02-06','8901234567','someemail8@mail.ru');
94/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
95UNLOCK TABLES;
96
97--
98-- Table structure for table `item`
99--
100
101DROP TABLE IF EXISTS `item`;
102/*!40101 SET @saved_cs_client = @@character_set_client */;
103 SET character_set_client = utf8mb4 ;
104CREATE TABLE `item` (
105 `order_idorder` int(11) NOT NULL,
106 `book_idbook` int(11) NOT NULL,
107 PRIMARY KEY (`order_idorder`,`book_idbook`),
108 KEY `fk_order_has_book_book1_idx` (`book_idbook`),
109 KEY `fk_order_has_book_order1_idx` (`order_idorder`),
110 CONSTRAINT `fk_order_has_book_book1` FOREIGN KEY (`book_idbook`) REFERENCES `book` (`idbook`),
111 CONSTRAINT `fk_order_has_book_order1` FOREIGN KEY (`order_idorder`) REFERENCES `order` (`idorder`)
112) ENGINE=InnoDB DEFAULT CHARSET=utf8;
113/*!40101 SET character_set_client = @saved_cs_client */;
114
115--
116-- Dumping data for table `item`
117--
118
119LOCK TABLES `item` WRITE;
120/*!40000 ALTER TABLE `item` DISABLE KEYS */;
121INSERT INTO `item` VALUES (2,1),(2,2),(8,2),(9,2),(2,3),(9,3),(1,4),(2,4),(3,4),(1,5),(1,6),(6,6),(6,7),(7,7),(7,8),(7,9),(5,10),(4,12),(5,12),(4,13),(5,13);
122/*!40000 ALTER TABLE `item` ENABLE KEYS */;
123UNLOCK TABLES;
124
125--
126-- Table structure for table `log`
127--
128
129DROP TABLE IF EXISTS `log`;
130/*!40101 SET @saved_cs_client = @@character_set_client */;
131 SET character_set_client = utf8mb4 ;
132CREATE TABLE `log` (
133 `idlog` int(11) NOT NULL AUTO_INCREMENT,
134 `user` varchar(34) NOT NULL,
135 `time` timestamp NOT NULL,
136 `idbook` int(11) NOT NULL,
137 `oldprice` decimal(6,2) DEFAULT NULL,
138 `newprice` decimal(6,2) DEFAULT NULL,
139 `quantity` smallint(6) DEFAULT NULL,
140 PRIMARY KEY (`idlog`)
141) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
142/*!40101 SET character_set_client = @saved_cs_client */;
143
144--
145-- Dumping data for table `log`
146--
147
148LOCK TABLES `log` WRITE;
149/*!40000 ALTER TABLE `log` DISABLE KEYS */;
150INSERT INTO `log` VALUES (1,'root@localhost','2019-07-10 18:07:20',1,1320.00,2640.00,10),(2,'root@localhost','2019-07-10 18:07:40',1,2640.00,1320.00,10);
151/*!40000 ALTER TABLE `log` ENABLE KEYS */;
152UNLOCK TABLES;
153
154--
155-- Table structure for table `order`
156--
157
158DROP TABLE IF EXISTS `order`;
159/*!40101 SET @saved_cs_client = @@character_set_client */;
160 SET character_set_client = utf8mb4 ;
161CREATE TABLE `order` (
162 `idorder` int(11) NOT NULL AUTO_INCREMENT,
163 `time` timestamp NOT NULL,
164 `amount` decimal(7,2) DEFAULT NULL,
165 `customer_idcustomer` int(11) NOT NULL,
166 PRIMARY KEY (`idorder`),
167 KEY `fk_order_customer_idx` (`customer_idcustomer`),
168 CONSTRAINT `fk_order_customer` FOREIGN KEY (`customer_idcustomer`) REFERENCES `customer` (`idcustomer`)
169) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
170/*!40101 SET character_set_client = @saved_cs_client */;
171
172--
173-- Dumping data for table `order`
174--
175
176LOCK TABLES `order` WRITE;
177/*!40000 ALTER TABLE `order` DISABLE KEYS */;
178INSERT INTO `order` VALUES (1,'2016-11-01 07:00:01',NULL,1),(2,'2017-11-02 07:00:01',NULL,2),(3,'2017-11-03 07:00:01',NULL,1),(4,'2018-11-24 07:00:01',NULL,3),(5,'2018-11-05 07:00:01',NULL,5),(6,'2018-05-06 07:00:01',NULL,6),(7,'2018-03-07 07:00:01',NULL,8),(8,'2018-01-08 07:00:01',NULL,4),(9,'2018-12-14 07:00:01',NULL,4);
179/*!40000 ALTER TABLE `order` ENABLE KEYS */;
180UNLOCK TABLES;
181
182--
183-- Temporary view structure for view `vtotal_sales`
184--
185
186DROP TABLE IF EXISTS `vtotal_sales`;
187/*!50001 DROP VIEW IF EXISTS `vtotal_sales`*/;
188SET @saved_cs_client = @@character_set_client;
189SET character_set_client = utf8mb4;
190/*!50001 CREATE VIEW `vtotal_sales` AS SELECT
191 1 AS `year`,
192 1 AS `month`,
193 1 AS `sales`*/;
194SET character_set_client = @saved_cs_client;
195
196--
197-- Final view structure for view `vtotal_sales`
198--
199
200/*!50001 DROP VIEW IF EXISTS `vtotal_sales`*/;
201/*!50001 SET @saved_cs_client = @@character_set_client */;
202/*!50001 SET @saved_cs_results = @@character_set_results */;
203/*!50001 SET @saved_col_connection = @@collation_connection */;
204/*!50001 SET character_set_client = cp866 */;
205/*!50001 SET character_set_results = cp866 */;
206/*!50001 SET collation_connection = cp866_general_ci */;
207/*!50001 CREATE ALGORITHM=UNDEFINED */
208/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
209/*!50001 VIEW `vtotal_sales` AS select year(`order`.`time`) AS `year`,month(`order`.`time`) AS `month`,sum(`book`.`price`) AS `sales` from ((`book` join `item`) join `order`) where ((`item`.`order_idorder` = `order`.`idorder`) and (`book`.`idbook` = `item`.`book_idbook`)) group by year(`order`.`time`),month(`order`.`time`) with rollup order by year(`order`.`time`) desc */;
210/*!50001 SET character_set_client = @saved_cs_client */;
211/*!50001 SET character_set_results = @saved_cs_results */;
212/*!50001 SET collation_connection = @saved_col_connection */;
213/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
214
215/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
216/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
217/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
218/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
219/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
220/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
221/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
222
223-- Dump completed on 2019-07-11 19:50:33