· 7 years ago · Dec 23, 2018, 08:34 AM
11. Create tables
2CREATE TABLE IF NOT EXISTS `user` (
3 `user_id` int(11) NOT NULL,
4 `firstname` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
5 `lastname` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
6 `prefecture` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
7 `city` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
8 `other_address` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
9 `tel` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
10 `gender` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
11 `birthday` date NOT NULL,
12 `mail` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
13 `in_date` date NOT NULL,
14 `upd_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
15) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
16
17--
18-- Dumping data for table `user`
19--
20
21INSERT INTO `user` (`user_id`, `firstname`, `lastname`, `prefecture`, `city`, `other_address`, `tel`, `gender`, `birthday`, `mail`, `in_date`, `upd_date`) VALUES
22(1, 'Akihabara', 'Akihabara manab', 'Tokyo', 'Chiyoda Ward', 'Akihabara 1-2-1\r\n', '03-1111-2222', 'Mal', '1980-01-01', 'manabu@akihabara.ne.jp\r\n', '2018-12-22', '2018-12-21 17:30:00'),
23(2, 'Kanda Akira', 'Kanda Kira', 'Tokyo', 'Chiyoda Ward', 'Kanda 2-1\r\n', '03-2222-3333', 'Mal', '1970-02-02', 'akira@kannda.ne.jp\r\n', '2018-12-22', '2018-12-21 17:30:00'),
24(3, 'Ueno', 'Wenohiroshi', 'Tokyo', 'Taito', 'Ueno 6 - chome 2\r\n', '03-3333-4444', 'Mal', '1975-03-03', 'hiroshi@ueno.ne.jp\r\n', '2018-12-22', '2018-12-21 17:30:00'),
25(4, 'Iruma Green', 'Il Mondri', 'Tokyo', 'Iruma-shi', 'Iruma 400\r\n', '04-2222-3333', 'Fem', '1985-04-04', 'midori@iruma.ne.jp\r\n', '2018-12-22', '2018-12-21 17:30:00'),
26(5, 'Taro Nihei', 'Ryou gok Taro', 'Tokyo', 'Sumida-ku', 'Both countries 555\r\n', '03-4444-5555', 'Mal', '1965-05-05', 'taro@ryogoku.ne.jp\r\n', '2018-12-22', '2018-12-21 17:30:00');
27
28--
29-- Indexes for dumped tables
30--
31
32--
33-- Indexes for table `user`
34--
35ALTER TABLE `user`
36 ADD PRIMARY KEY (`user_id`);
37
38 --
39-- Table structure for table `category`
40--
41
42CREATE TABLE IF NOT EXISTS `category` (
43`category_id` int(11) NOT NULL,
44 `category_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
45 `in_date` date NOT NULL,
46 `upd_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
47) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
48
49--
50-- Dumping data for table `category`
51--
52
53INSERT INTO `category` (`category_id`, `category_name`, `in_date`, `upd_date`) VALUES
54(1, 'Java', '2018-12-22', '2018-12-21 17:30:00'),
55(2, 'Database', '2018-12-22', '2018-12-21 17:30:00'),
56(3, 'PHP', '2018-12-22', '2018-12-21 17:30:00'),
57(4, 'no category', '2018-12-22', '2018-12-21 17:30:00');
58
59--
60-- Indexes for dumped tables
61--
62
63--
64-- Indexes for table `category`
65--
66ALTER TABLE `category`
67 ADD PRIMARY KEY (`category_id`);
68
69 --
70-- Table structure for table `order_i`
71--
72
73CREATE TABLE IF NOT EXISTS `order_i` (
74`order_item_id` int(11) NOT NULL,
75 `order_id` int(11) NOT NULL,
76 `product_id` int(11) NOT NULL,
77 `order_quantity` int(11) NOT NULL,
78 `in_date` date NOT NULL,
79 `upd_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
80) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
81
82--
83-- Dumping data for table `order_i`
84--
85
86INSERT INTO `order_i` (`order_item_id`, `order_id`, `product_id`, `order_quantity`, `in_date`, `upd_date`) VALUES
87(1, 1, 1, 1, '2018-12-22', '2018-12-21 17:30:00'),
88(2, 2, 1, 1, '2018-12-22', '2018-12-21 17:30:00'),
89(3, 2, 2, 1, '2018-12-22', '2018-12-21 17:30:00'),
90(4, 3, 3, 2, '2018-12-22', '2018-12-21 17:30:00');
91
92--
93-- Indexes for dumped tables
94--
95
96--
97-- Indexes for table `order_i`
98--
99ALTER TABLE `order_i`
100 ADD PRIMARY KEY (`order_item_id`), ADD KEY `order_id` (`order_id`), ADD KEY `product_id` (`product_id`);
101
102--
103-- AUTO_INCREMENT for dumped tables
104--
105
106--
107-- AUTO_INCREMENT for table `order_i`
108--
109ALTER TABLE `order_i`
110MODIFY `order_item_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
111--
112-- Constraints for dumped tables
113--
114
115--
116-- Constraints for table `order_i`
117--
118ALTER TABLE `order_i`
119ADD CONSTRAINT `order_i_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order_h` (`order_id`),
120ADD CONSTRAINT `order_i_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `book_info` (`book_id`);
121
122--
123-- Table structure for table `order_h`
124--
125
126CREATE TABLE IF NOT EXISTS `order_h` (
127`order_id` int(11) NOT NULL,
128 `user_id` int(11) NOT NULL,
129 `order_date` date NOT NULL,
130 `delivery_date` date NOT NULL,
131 `totalprice` int(11) NOT NULL,
132 `in_date` date NOT NULL,
133 `upd_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
134) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
135
136--
137-- Dumping data for table `order_h`
138--
139
140INSERT INTO `order_h` (`order_id`, `user_id`, `order_date`, `delivery_date`, `totalprice`, `in_date`, `upd_date`) VALUES
141(1, 1, '2018-12-22', '2018-12-24', 2500, '2018-12-22', '2018-12-21 17:30:00'),
142(2, 2, '2018-12-22', '2018-12-24', 6500, '2018-12-22', '2018-12-21 17:30:00'),
143(3, 4, '2018-12-22', '2018-12-24', 7000, '2018-12-22', '2018-12-21 17:30:00');
144
145--
146-- Indexes for dumped tables
147--
148
149--
150-- Indexes for table `order_h`
151--
152ALTER TABLE `order_h`
153 ADD PRIMARY KEY (`order_id`), ADD KEY `user_id` (`user_id`);
154
155--
156-- AUTO_INCREMENT for dumped tables
157--
158
159--
160-- AUTO_INCREMENT for table `order_h`
161--
162ALTER TABLE `order_h`
163MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
164--
165-- Constraints for dumped tables
166--
167
168--
169-- Constraints for table `order_h`
170--
171ALTER TABLE `order_h`
172ADD CONSTRAINT `order_h_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`);
173
174--
175-- Table structure for table `book_info`
176--
177
178CREATE TABLE IF NOT EXISTS `book_info` (
179`book_id` int(11) NOT NULL,
180 `isbn` char(20) COLLATE utf8_unicode_ci NOT NULL,
181 `book_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
182 `price` int(11) NOT NULL,
183 `quantity` int(11) NOT NULL,
184 `category_id` int(11) NOT NULL,
185 `in_date` date NOT NULL,
186 `upd_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
187) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
188
189--
190-- Dumping data for table `book_info`
191--
192
193INSERT INTO `book_info` (`book_id`, `isbn`, `book_name`, `price`, `quantity`, `category_id`, `in_date`, `upd_date`) VALUES
194(1, '4-0010-1111-1', 'One day of a cat\r\n', 2500, 10, 4, '2018-12-22', '2018-12-21 17:30:00'),
195(2, '4-5312-0202-2', 'Even monkeys and dogs may understand Java\r\n', 4000, 6, 1, '2018-12-22', '2018-12-21 17:30:00'),
196(3, '4-5555-4444-5', 'Basics of Database Basics\r\n', 3500, 5, 2, '2018-12-22', '2018-12-21 17:30:00'),
197(4, '4-8899-6667-3', 'One hour a day PHP question collection\r\n', 3000, 13, 3, '2018-12-22', '2018-12-21 17:30:00'),
198(5, '4-5677-0222-1', 'Akihabara gourmet spot feature\r\n', 1500, 23, 4, '2018-12-22', '2018-12-21 17:30:00');
199
200--
201-- Indexes for dumped tables
202--
203
204--
205-- Indexes for table `book_info`
206--
207ALTER TABLE `book_info`
208 ADD PRIMARY KEY (`book_id`), ADD KEY `category_id` (`category_id`);
209
210--
211-- AUTO_INCREMENT for dumped tables
212--
213
214--
215-- AUTO_INCREMENT for table `book_info`
216--
217ALTER TABLE `book_info`
218MODIFY `book_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
219--
220-- Constraints for dumped tables
221--
222
223--
224-- Constraints for table `book_info`
225--
226ALTER TABLE `book_info`
227ADD CONSTRAINT `book_info_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`);
228
2292-2
230UPDATE user SET `other_address`= "XXXX" WHERE `user_id`=1;
231UPDATE book_info SET `quantity`=12 WHERE `book_id`= 1;
232
2332-3
234DELETE FROM category WHERE category_id = 3;
235UPDATE book_info SET category_id = 4 WHERE book_id = 4;
236
2373-1
238SELECT COUNT(*) FROM book_info GROUP BY category_id;
239
2403-2
241SELECT AVG(price) as average_price FROM book_info GROUP BY category_id;
242
2433-3
244SELECT c.category_name, b.book_name as product_name FROM book_info as b INNER JOIN category as c ON b.category_id = c.category_id;
245
2463-4
247SELECT SUM(oi.order_quantity) as order_count, oh.user_id FROM order_h as oh LEFT JOIN order_i as oi ON oi.order_id = oh.order_id GROUP BY oi.order_id;
248
2493-5
250SELECT SUM(oi.order_quantity*b. price) as totalprice, oh.user_id, b.book_name, oi.order_id FROM order_h as oh LEFT JOIN order_i as oi ON oi.order_id = oh.order_id LEFT JOIN book_info as b ON b.book_id = oi.product_id GROUP BY oh.user_id;