· 6 years ago · Jun 11, 2019, 08:14 PM
1-- phpMyAdmin SQL Dump
2-- version 4.8.5
3-- https://www.phpmyadmin.net/
4--
5-- Host: 127.0.0.1
6-- Gegenereerd op: 10 jun 2019 om 16:39
7-- Serverversie: 10.1.40-MariaDB
8-- PHP-versie: 7.3.5
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET AUTOCOMMIT = 0;
12START TRANSACTION;
13SET time_zone = "+00:00";
14
15
16/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
17/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
18/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
19/*!40101 SET NAMES utf8mb4 */;
20
21--
22-- Database: `football`
23--
24CREATE DATABASE IF NOT EXISTS `football` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
25USE `football`;
26
27-- --------------------------------------------------------
28
29--
30-- Tabelstructuur voor tabel `coaches`
31--
32
33CREATE TABLE `coaches` (
34 `id` int(2) NOT NULL,
35 `name` varchar(30) NOT NULL,
36 `firstname` varchar(30) NOT NULL,
37 `national_team` int(4) NOT NULL,
38 `country` int(2) NOT NULL
39) ENGINE=InnoDB DEFAULT CHARSET=utf8;
40
41--
42-- Gegevens worden geëxporteerd voor tabel `coaches`
43--
44
45INSERT INTO `coaches` (`id`, `name`, `firstname`, `national_team`, `country`) VALUES
46(1, 'Moriyasu', 'Hajime', 10, 10),
47(2, 'Martínez', 'Roberto', 5, 2),
48(3, 'Löw', 'Joachim', 1, 1),
49(4, 'Stuart', 'Baxter', 4, 5),
50(5, 'Cherchesov', 'Stanislav', 9, 9),
51(6, 'Deschamps', 'Didier', 2, 4),
52(7, 'Scaloni', 'Lionel', 7, 7),
53(8, 'Foda', 'Franco', 8, 6),
54(9, 'Southgate', 'Gareth', 6, 8),
55(10, 'Bacchi', 'Leonardo', 3, 3);
56
57-- --------------------------------------------------------
58
59--
60-- Tabelstructuur voor tabel `countries`
61--
62
63CREATE TABLE `countries` (
64 `id` int(2) NOT NULL,
65 `name` varchar(20) NOT NULL
66) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67
68--
69-- Gegevens worden geëxporteerd voor tabel `countries`
70--
71
72INSERT INTO `countries` (`id`, `name`) VALUES
73(1, 'Germany'),
74(2, 'Belgium'),
75(3, 'Brazil'),
76(4, 'France'),
77(5, 'South Africa'),
78(6, 'Austria'),
79(7, 'Argentina'),
80(8, 'England'),
81(9, 'Russia'),
82(10, 'Japan'),
83(11, 'Spain'),
84(12, 'Portugal'),
85(13, 'United States');
86
87-- --------------------------------------------------------
88
89--
90-- Tabelstructuur voor tabel `matches`
91--
92
93CREATE TABLE `matches` (
94 `id` int(11) NOT NULL,
95 `start_date` datetime NOT NULL,
96 `end_date` datetime NOT NULL,
97 `location` int(2) NOT NULL
98) ENGINE=InnoDB DEFAULT CHARSET=utf8;
99
100--
101-- Gegevens worden geëxporteerd voor tabel `matches`
102--
103
104INSERT INTO `matches` (`id`, `start_date`, `end_date`, `location`) VALUES
105(1, '2017-06-05 16:15:00', '2017-06-05 18:00:00', 2),
106(2, '2018-11-09 14:15:00', '2018-11-09 16:00:00', 10),
107(3, '2016-02-27 16:15:00', '2016-02-27 18:00:00', 9),
108(4, '2019-03-15 15:00:00', '2019-03-15 16:45:00', 4),
109(5, '2019-04-02 14:15:00', '2019-04-02 16:00:00', 8),
110(6, '2018-06-21 19:15:00', '2018-06-21 21:00:00', 1);
111
112-- --------------------------------------------------------
113
114--
115-- Tabelstructuur voor tabel `match_teams`
116--
117
118CREATE TABLE `match_teams` (
119 `national_team` int(4) NOT NULL,
120 `match` int(11) NOT NULL,
121 `score` int(2) NOT NULL
122) ENGINE=InnoDB DEFAULT CHARSET=utf8;
123
124--
125-- Gegevens worden geëxporteerd voor tabel `match_teams`
126--
127
128INSERT INTO `match_teams` (`national_team`, `match`, `score`) VALUES
129(1, 6, 0),
130(2, 4, 2),
131(4, 2, 3),
132(4, 4, 0),
133(5, 1, 2),
134(5, 3, 1),
135(5, 6, 0),
136(6, 1, 1),
137(7, 5, 2),
138(8, 5, 0),
139(9, 3, 0),
140(10, 2, 3);
141
142-- --------------------------------------------------------
143
144--
145-- Tabelstructuur voor tabel `national_teams`
146--
147
148CREATE TABLE `national_teams` (
149 `id` int(4) NOT NULL,
150 `name` varchar(30) NOT NULL,
151 `country` int(2) NOT NULL
152) ENGINE=InnoDB DEFAULT CHARSET=utf8;
153
154--
155-- Gegevens worden geëxporteerd voor tabel `national_teams`
156--
157
158INSERT INTO `national_teams` (`id`, `name`, `country`) VALUES
159(1, 'Die Mannschaft', 1),
160(2, 'Les Bleus', 4),
161(3, 'A Seleção', 3),
162(4, 'Bafana Bafana', 5),
163(5, 'De rode druiven', 2),
164(6, 'The Three Lions', 8),
165(7, 'Albicelestes', 7),
166(8, 'Das team', 6),
167(9, 'Sbornaja', 9),
168(10, 'Nippon Daihyō', 10);
169
170-- --------------------------------------------------------
171
172--
173-- Tabelstructuur voor tabel `players`
174--
175
176CREATE TABLE `players` (
177 `id` int(4) NOT NULL,
178 `name` varchar(30) NOT NULL,
179 `firstname` varchar(30) NOT NULL,
180 `country` int(2) NOT NULL,
181 `national_team` int(4) NOT NULL
182) ENGINE=InnoDB DEFAULT CHARSET=utf8;
183
184--
185-- Gegevens worden geëxporteerd voor tabel `players`
186--
187
188INSERT INTO `players` (`id`, `name`, `firstname`, `country`, `national_team`) VALUES
189(1, 'Tomiyasu', 'Takehiro', 10, 10),
190(2, 'Courtois', 'Thibaut', 2, 5),
191(3, 'Griezmann', 'Antoine', 4, 2),
192(4, 'Lloris ', 'Hugo', 4, 2),
193(5, 'Shunin', 'Anton', 9, 9),
194(6, 'Gomez', 'Joe', 8, 6),
195(7, 'Marinato', 'Guilherme', 9, 9),
196(8, 'Williams', 'Ronwen ', 5, 4),
197(9, 'Gregoritsch', 'Michael', 6, 8),
198(10, 'Dubois', 'Léo', 4, 2),
199(11, 'Sandro', 'Alex', 3, 3),
200(12, 'Hazard', 'Eden', 2, 5),
201(13, 'Trapp', 'Kevin', 1, 1),
202(14, 'Jesus', 'Gabriel', 3, 3),
203(15, 'Messi', 'Lionel', 7, 7),
204(16, 'Pizarro', 'Guido', 7, 7),
205(17, 'Doan', 'Ritsu', 10, 10),
206(18, 'Heaton', 'Tom', 8, 6),
207(19, 'Mendy', 'Benjamin', 4, 2),
208(20, 'Kimpembe', 'Presnel', 4, 2),
209(21, 'Vertonghen', 'Jan', 2, 5),
210(22, 'De Bruyne', 'Kevin', 2, 5),
211(23, 'Meunier', 'Thomas', 2, 5);
212
213--
214-- Indexen voor geëxporteerde tabellen
215--
216
217--
218-- Indexen voor tabel `coaches`
219--
220ALTER TABLE `coaches`
221 ADD PRIMARY KEY (`id`,`national_team`,`country`),
222 ADD KEY `fk_coaches_national_teams1_idx` (`national_team`),
223 ADD KEY `fk_coaches_countries1_idx` (`country`);
224
225--
226-- Indexen voor tabel `countries`
227--
228ALTER TABLE `countries`
229 ADD PRIMARY KEY (`id`);
230
231--
232-- Indexen voor tabel `matches`
233--
234ALTER TABLE `matches`
235 ADD PRIMARY KEY (`id`,`location`),
236 ADD KEY `fk_toernooien_landen1_idx` (`location`);
237
238--
239-- Indexen voor tabel `match_teams`
240--
241ALTER TABLE `match_teams`
242 ADD PRIMARY KEY (`national_team`,`match`),
243 ADD KEY `fk_nationale_ploegen_has_toernooien_toernooien1_idx` (`match`),
244 ADD KEY `fk_nationale_ploegen_has_toernooien_nationale_ploegen1_idx` (`national_team`);
245
246--
247-- Indexen voor tabel `national_teams`
248--
249ALTER TABLE `national_teams`
250 ADD PRIMARY KEY (`id`,`country`),
251 ADD KEY `fk_national_teams_countries1_idx` (`country`);
252
253--
254-- Indexen voor tabel `players`
255--
256ALTER TABLE `players`
257 ADD PRIMARY KEY (`id`,`country`,`national_team`),
258 ADD KEY `fk_spelers_landen_idx` (`country`),
259 ADD KEY `fk_spelers_nationale_ploegen1_idx` (`national_team`);
260
261--
262-- AUTO_INCREMENT voor geëxporteerde tabellen
263--
264
265--
266-- AUTO_INCREMENT voor een tabel `coaches`
267--
268ALTER TABLE `coaches`
269 MODIFY `id` int(2) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
270
271--
272-- AUTO_INCREMENT voor een tabel `countries`
273--
274ALTER TABLE `countries`
275 MODIFY `id` int(2) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
276
277--
278-- AUTO_INCREMENT voor een tabel `matches`
279--
280ALTER TABLE `matches`
281 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
282
283--
284-- AUTO_INCREMENT voor een tabel `national_teams`
285--
286ALTER TABLE `national_teams`
287 MODIFY `id` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
288
289--
290-- AUTO_INCREMENT voor een tabel `players`
291--
292ALTER TABLE `players`
293 MODIFY `id` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;
294
295--
296-- Beperkingen voor geëxporteerde tabellen
297--
298
299--
300-- Beperkingen voor tabel `coaches`
301--
302ALTER TABLE `coaches`
303 ADD CONSTRAINT `fk_coaches_countries1` FOREIGN KEY (`country`) REFERENCES `countries` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
304 ADD CONSTRAINT `fk_coaches_national_teams1` FOREIGN KEY (`national_team`) REFERENCES `national_teams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
305
306--
307-- Beperkingen voor tabel `matches`
308--
309ALTER TABLE `matches`
310 ADD CONSTRAINT `fk_toernooien_landen1` FOREIGN KEY (`location`) REFERENCES `countries` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
311
312--
313-- Beperkingen voor tabel `match_teams`
314--
315ALTER TABLE `match_teams`
316 ADD CONSTRAINT `fk_nationale_ploegen_has_toernooien_nationale_ploegen1` FOREIGN KEY (`national_team`) REFERENCES `national_teams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
317 ADD CONSTRAINT `fk_nationale_ploegen_has_toernooien_toernooien1` FOREIGN KEY (`match`) REFERENCES `matches` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
318
319--
320-- Beperkingen voor tabel `national_teams`
321--
322ALTER TABLE `national_teams`
323 ADD CONSTRAINT `fk_national_teams_countries1` FOREIGN KEY (`country`) REFERENCES `countries` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
324
325--
326-- Beperkingen voor tabel `players`
327--
328ALTER TABLE `players`
329 ADD CONSTRAINT `fk_spelers_landen` FOREIGN KEY (`country`) REFERENCES `countries` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
330 ADD CONSTRAINT `fk_spelers_nationale_ploegen1` FOREIGN KEY (`national_team`) REFERENCES `national_teams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;