LkwQgnC4

1/*
2Navicat MySQL Data Transfer
3
4Source Server         : localhost
5Source Server Version : 50638
6Source Host           : localhost:3306
7Source Database       : lesson
8
9Target Server Type    : MYSQL
10Target Server Version : 50638
11File Encoding         : 65001
12
13Date: 2018-05-18 20:33:06
14*/
15
16SET FOREIGN_KEY_CHECKS=0;
17
18-- ----------------------------
19-- Table structure for `city`
20-- ----------------------------
21DROP TABLE IF EXISTS `city`;
22CREATE TABLE `city` (
23  `id` int(11) NOT NULL AUTO_INCREMENT,
24  `title` varchar(30) DEFAULT NULL,
25  `country_id` int(11) DEFAULT NULL,
26  PRIMARY KEY (`id`),
27  KEY `foregin_key_country_id` (`country_id`),
28  CONSTRAINT `foregin_key_country_id` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
29) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
30
31-- ----------------------------
32-- Records of city
33-- ----------------------------
34INSERT INTO `city` VALUES ('1', 'Город 1', '2');
35INSERT INTO `city` VALUES ('2', 'Город 2', '2');
36INSERT INTO `city` VALUES ('3', 'Город 3', '1');
37INSERT INTO `city` VALUES ('4', 'Город 4', '3');
38
39-- ----------------------------
40-- Table structure for `country`
41-- ----------------------------
42DROP TABLE IF EXISTS `country`;
43CREATE TABLE `country` (
44  `id` int(11) NOT NULL AUTO_INCREMENT,
45  `title` varchar(30) DEFAULT NULL,
46  PRIMARY KEY (`id`)
47) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
48
49-- ----------------------------
50-- Records of country
51-- ----------------------------
52INSERT INTO `country` VALUES ('1', 'Страна 1');
53INSERT INTO `country` VALUES ('2', 'Страна 2');
54INSERT INTO `country` VALUES ('3', 'Страна 3');
55
56-- ----------------------------
57-- Table structure for `orders`
58-- ----------------------------
59DROP TABLE IF EXISTS `orders`;
60CREATE TABLE `orders` (
61  `id` int(11) NOT NULL AUTO_INCREMENT,
62  `status` set('0','1') NOT NULL DEFAULT '0',
63  `user_id` int(11) DEFAULT NULL,
64  `product_id` int(11) DEFAULT NULL,
65  `date` datetime DEFAULT NULL,
66  PRIMARY KEY (`id`),
67  KEY `f_key_user_id` (`user_id`),
68  KEY `f_key_product_id` (`product_id`),
69  CONSTRAINT `f_key_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
70  CONSTRAINT `f_key_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
71) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
72
73-- ----------------------------
74-- Records of orders
75-- ----------------------------
76INSERT INTO `orders` VALUES ('1', '1', '1', '4', '2015-08-04 20:20:01');
77INSERT INTO `orders` VALUES ('2', '0', '1', '4', '2016-06-09 00:00:00');
78INSERT INTO `orders` VALUES ('3', '1', '3', '6', '2016-06-15 00:00:00');
79INSERT INTO `orders` VALUES ('4', '1', '3', '11', '2016-06-28 00:00:00');
80INSERT INTO `orders` VALUES ('5', '1', '3', '10', '2016-06-09 00:00:00');
81INSERT INTO `orders` VALUES ('6', '1', '3', '8', '2016-06-22 00:00:00');
82INSERT INTO `orders` VALUES ('7', '1', '3', '2', '2016-06-09 00:00:00');
83INSERT INTO `orders` VALUES ('8', '1', '3', '9', '2016-06-07 00:00:00');
84INSERT INTO `orders` VALUES ('9', '0', '8', '12', '2016-06-17 00:00:00');
85INSERT INTO `orders` VALUES ('10', '0', '7', '10', '2016-06-16 00:00:00');
86INSERT INTO `orders` VALUES ('11', '0', '4', '12', '2016-06-17 00:00:00');
87
88-- ----------------------------
89-- Table structure for `product`
90-- ----------------------------
91DROP TABLE IF EXISTS `product`;
92CREATE TABLE `product` (
93  `id` int(11) NOT NULL AUTO_INCREMENT,
94  `title` varchar(30) DEFAULT NULL,
95  `description` varchar(50) DEFAULT NULL,
96  `count` int(11) DEFAULT NULL,
97  `price` float DEFAULT NULL,
98  `product_category_id` int(11) DEFAULT NULL,
99  PRIMARY KEY (`id`),
100  KEY `foregin_key_product_category_id` (`product_category_id`),
101  CONSTRAINT `foregin_key_product_category_id` FOREIGN KEY (`product_category_id`) REFERENCES `product_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
102) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
103
104-- ----------------------------
105-- Records of product
106-- ----------------------------
107INSERT INTO `product` VALUES ('1', 'Mercedes', 'Описание1', '10', '123', '1');
108INSERT INTO `product` VALUES ('2', 'Mercedes', 'Описание2', '11', '156.87', '2');
109INSERT INTO `product` VALUES ('3', 'Toyota', 'Описание3', '21', '246.08', '1');
110INSERT INTO `product` VALUES ('4', 'Таврия', 'Описание4', '11', '7', '1');
111INSERT INTO `product` VALUES ('5', 'Запорожец', 'Описание5', '21', '90.09', '1');
112INSERT INTO `product` VALUES ('6', 'Продукт6', 'Описание6', '2', '1.01', '2');
113INSERT INTO `product` VALUES ('7', 'Mercedes', 'Описание7', '128', '2.04', '3');
114INSERT INTO `product` VALUES ('8', 'Mercedes', 'Описание8', '11', '9.9', '1');
115INSERT INTO `product` VALUES ('9', 'Продукт9', 'Описание9', '13', '100', '1');
116INSERT INTO `product` VALUES ('10', 'Продукт10', 'Описание10', '12', '45.32', '2');
117INSERT INTO `product` VALUES ('11', 'Продукт11', 'Описание11', '2', '872.1', '2');
118INSERT INTO `product` VALUES ('12', 'Продукт12', 'Описание12', '12', '6.92', '3');
119
120-- ----------------------------
121-- Table structure for `product_category`
122-- ----------------------------
123DROP TABLE IF EXISTS `product_category`;
124CREATE TABLE `product_category` (
125  `id` int(11) NOT NULL AUTO_INCREMENT,
126  `title` varchar(30) DEFAULT NULL,
127  PRIMARY KEY (`id`)
128) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
129
130-- ----------------------------
131-- Records of product_category
132-- ----------------------------
133INSERT INTO `product_category` VALUES ('1', 'Категория 1');
134INSERT INTO `product_category` VALUES ('2', 'Категория 2');
135INSERT INTO `product_category` VALUES ('3', 'Категория 3');
136INSERT INTO `product_category` VALUES ('4', 'Категория 4');
137INSERT INTO `product_category` VALUES ('5', 'Категория 5');
138INSERT INTO `product_category` VALUES ('6', 'Категория 6');
139
140-- ----------------------------
141-- Table structure for `role`
142-- ----------------------------
143DROP TABLE IF EXISTS `role`;
144CREATE TABLE `role` (
145  `id` int(11) NOT NULL AUTO_INCREMENT,
146  `title` varchar(30) DEFAULT NULL,
147  `description` varchar(30) DEFAULT NULL,
148  PRIMARY KEY (`id`)
149) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
150
151-- ----------------------------
152-- Records of role
153-- ----------------------------
154INSERT INTO `role` VALUES ('1', 'Admin', 'Администратор');
155INSERT INTO `role` VALUES ('2', 'User', 'Пользователь');
156INSERT INTO `role` VALUES ('3', 'Editor', 'Редактор');
157
158-- ----------------------------
159-- Table structure for `user`
160-- ----------------------------
161DROP TABLE IF EXISTS `user`;
162CREATE TABLE `user` (
163  `id` int(11) NOT NULL AUTO_INCREMENT,
164  `firstname` varchar(30) DEFAULT NULL,
165  `lastname` varchar(30) DEFAULT NULL,
166  `login` varchar(30) DEFAULT NULL,
167  `password` varchar(30) DEFAULT NULL,
168  `city_id` int(11) DEFAULT NULL,
169  PRIMARY KEY (`id`),
170  KEY `foregin_key_city_id` (`city_id`),
171  CONSTRAINT `foregin_key_city_id` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
172) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
173
174-- ----------------------------
175-- Records of user
176-- ----------------------------
177INSERT INTO `user` VALUES ('1', 'Иван', 'Иванов', 'Ivan', 'вапрыарен8е8', '1');
178INSERT INTO `user` VALUES ('2', 'Антон', 'Андреенко', 'Anton', ';fdlg4598', '1');
179INSERT INTO `user` VALUES ('3', 'Сергей', 'Муншин', 'login45', 'lasdkjf098', '2');
180INSERT INTO `user` VALUES ('4', 'Коля', 'Сидоренко', 'koliy', 'slk%*', '4');
181INSERT INTO `user` VALUES ('7', 'Люба', 'Сванидзе', 'luba', '0-4okg', '4');
182INSERT INTO `user` VALUES ('8', 'Галя', 'Козлович', 'galiy', '03945dpo', '2');
183
184-- ----------------------------
185-- Table structure for `user_city`
186-- ----------------------------
187DROP TABLE IF EXISTS `user_city`;
188CREATE TABLE `user_city` (
189  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
190  `user_id` int(11) unsigned DEFAULT NULL,
191  `city_id` int(11) unsigned DEFAULT NULL,
192  PRIMARY KEY (`id`)
193) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
194
195-- ----------------------------
196-- Records of user_city
197-- ----------------------------
198INSERT INTO `user_city` VALUES ('1', '1', '1');
199INSERT INTO `user_city` VALUES ('2', '2', '2');
200INSERT INTO `user_city` VALUES ('3', '3', '1');
201INSERT INTO `user_city` VALUES ('4', '4', '2');
202INSERT INTO `user_city` VALUES ('5', '7', '4');
203INSERT INTO `user_city` VALUES ('6', '8', '1');
204INSERT INTO `user_city` VALUES ('7', '3', '2');
205INSERT INTO `user_city` VALUES ('8', '7', '3');
206
207-- ----------------------------
208-- Table structure for `user_role`
209-- ----------------------------
210DROP TABLE IF EXISTS `user_role`;
211CREATE TABLE `user_role` (
212  `user_id` int(11) DEFAULT NULL,
213  `role_id` int(11) DEFAULT NULL,
214  KEY `foregin_key_user_id` (`user_id`),
215  KEY `foregin_key_role_id` (`role_id`),
216  CONSTRAINT `foregin_key_role_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
217  CONSTRAINT `foregin_key_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
218) ENGINE=InnoDB DEFAULT CHARSET=utf8;
219
220-- ----------------------------
221-- Records of user_role
222-- ----------------------------
223INSERT INTO `user_role` VALUES ('1', '2');
224INSERT INTO `user_role` VALUES ('3', '2');
225INSERT INTO `user_role` VALUES ('2', '1');
226INSERT INTO `user_role` VALUES ('2', '3');