· 7 years ago · Sep 10, 2018, 08:06 AM
1-- C:\Users\andreasalef>c:\xampp\mysql\bin\mysql --local-infile -u root -p
2-- C:\Users\andreasalef>c:\xampp\mysql\bin\mysql -u root -p
3
4
5
6DROP DATABASE IF EXISTS nbb;
7-- DROP DATABASE NBB;
8-- DROP DATABASE NBB;
9/* DROP DATABASE NBB; */
10
11CREATE DATABASE nbb;
12
13USE nbb;
14
15SET utf8 ;
16
17-----------------------------------------------------
18-- Table tblKunde
19-- -----------------------------------------------------
20CREATE TABLE tblKunde
21(
22 kid INT NOT NULL AUTO_INCREMENT,
23 name VARCHAR(45),
24 vorname VARCHAR(45),
25 strasse VARCHAR(45),
26 plz CHAR(5),
27 stadt VARCHAR(45),
28 email VARCHAR(45),
29 PRIMARY KEY (kid)
30) ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table tblArtikel
35-- -----------------------------------------------------
36CREATE TABLE tblArtikel (
37 aid INT NOT NULL AUTO_INCREMENT,
38 name VARCHAR(45),
39 beschreibung VARCHAR(45),
40 PRIMARY KEY (aid))
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table tblVersand
46-- -----------------------------------------------------
47CREATE TABLE tblVersand (
48 vid INT NOT NULL AUTO_INCREMENT,
49 name VARCHAR(45),
50 beschreibung VARCHAR(45),
51 kosten VARCHAR(45),
52 PRIMARY KEY (vid))
53ENGINE = InnoDB;
54
55
56-- -----------------------------------------------------
57-- Table tblZahlungsart
58-- -----------------------------------------------------
59CREATE TABLE tblZahlungsart (
60 zid INT NOT NULL AUTO_INCREMENT,
61 name VARCHAR(45),
62 PRIMARY KEY (zid))
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table tblRechnung
68-- -----------------------------------------------------
69CREATE TABLE tblRechnung (
70 rid INT NOT NULL AUTO_INCREMENT,
71 datum DATE,
72 vid INT,
73 zid INT,
74 kid INT,
75 PRIMARY KEY (rid),
76 INDEX fk_tblRechnung_tblVersand1_idx (vid ASC),
77 INDEX fk_tblRechnung_tblZahlungsart1_idx (zid ASC),
78 INDEX fk_tblRechnung_tblKunde1_idx (kid ASC),
79 CONSTRAINT fk_tblRechnung_tblVersand1
80 FOREIGN KEY (vid)
81 REFERENCES tblVersand (vid)
82 ON DELETE NO ACTION
83 ON UPDATE NO ACTION,
84 CONSTRAINT fk_tblRechnung_tblZahlungsart1
85 FOREIGN KEY (zid)
86 REFERENCES tblZahlungsart (zid)
87 ON DELETE NO ACTION
88 ON UPDATE NO ACTION,
89 CONSTRAINT fk_tblRechnung_tblKunde1
90 FOREIGN KEY (kid)
91 REFERENCES tblKunde (kid)
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION)
94ENGINE = InnoDB;
95
96
97-- -----------------------------------------------------
98-- Table tblRechnung_has_tblArtikel
99-- -----------------------------------------------------
100CREATE TABLE tblRechnung_has_tblArtikel (
101 a2rid INT NOT NULL AUTO_INCREMENT,
102 rid INT NOT NULL,
103 aid INT NOT NULL,
104 INDEX fk_tblRechnung_has_tblArtikel_tblArtikel1_idx (aid ASC),
105 INDEX fk_tblRechnung_has_tblArtikel_tblRechnung1_idx (rid ASC),
106 PRIMARY KEY (a2rid),
107 CONSTRAINT fk_tblRechnung_has_tblArtikel_tblRechnung1
108 FOREIGN KEY (rid)
109 REFERENCES tblRechnung (rid)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT fk_tblRechnung_has_tblArtikel_tblArtikel1
113 FOREIGN KEY (aid)
114 REFERENCES tblArtikel (aid)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION)
117ENGINE = InnoDB;
118
119
120--- SQL Datei: https://pastebin.com/YrXSLpCd
121--- mwb-Datei:
122
123Einfügen von Daten:
1241. INSERT INTO
1252. Per csv-Datei und LOAD DATA LOCAL INFILE
126 - csv-Datei erstellen
127 - einlesen mit LLI
128
129
130-- Zilberberg
131INSERT INTO tblVersand (name,beschreibung,kosten)
132 VALUES
133 ('Andreas','Alef','drölf'),
134 ('Mark','Blef','drölf'),
135 ('Paulo','Clef','drölf'),
136 ('Leo','Dlef','drölf'),
137 ('Maria','Elef','drölf'),
138 ('Ronaldo','Flef','drölf'),
139 ('Arnold','Jlef','drölf'),
140 ('ACE','Ülef','drölf'),
141 ('Klivlend','Älef','drölf'),
142 ('Dude','Ölef','drölf'),
143 ('Ernesti','Zlef','drölf')
144;
145
146-- Gallistl
147
148-- Grautstück
149INSERT INTO tblZahlungsart(name)
150 VALUES
151 ('PayPal'),
152 ('giropay'),
153 ('Sofortueberweisung'),
154 ('EC'),
155 ('Visa'),
156 ('MasterCard'),
157 ('American Express'),
158 ('Nachnahme'),
159 ('Vorkasse'),
160 ('Rechnung')
161 ;
162
163-- Jan Savas
164INSERT INTO tblArtikel (name) VALUES (‘Acer Laptop’);
165INSERT INTO tblArtikel (name) VALUES (‘Lenovo Laptop’);
166INSERT INTO tblArtikel (name) VALUES (‘Dell Laptop’);
167INSERT INTO tblArtikel (name) VALUES (‘HP Laptop’);
168INSERT INTO tblArtikel (name) VALUES (‘Macbook’);
169INSERT INTO tblArtikel (name) VALUES (‘Logitech Maus’);
170INSERT INTO tblArtikel (name) VALUES (‘Logitech Tastatur’);
171INSERT INTO tblArtikel (name) VALUES (‘Logitech Maus Tastatur Set’);
172INSERT INTO tblArtikel (name) VALUES (‘Logitech Headset’);
173INSERT INTO tblArtikel (name) VALUES (‘24†Monitor’);
174INSERT INTO tblArtikel (name) VALUES (‘27†Monitor’);
175
176-- Gallistl
177INSERT INTO tblZahlungsart (name) VALUES ('Kreditkarte');
178INSERT INTO tblZahlungsart (name) VALUES ('Vorkasse');
179INSERT INTO tblZahlungsart (name) VALUES ('Visa');
180INSERT INTO tblZahlungsart (name) VALUES ('Mastercard');
181INSERT INTO tblZahlungsart (name) VALUES ('EC-Karte');
182INSERT INTO tblZahlungsart (name) VALUES ('Leasing');
183INSERT INTO tblZahlungsart (name) VALUES ('Bar');
184INSERT INTO tblZahlungsart (name) VALUES ('Girocard');
185
186-- Zistler
187INSERT INTO tblversand (name, beschreibung, kosten)
188VALUES
189('Nutzer', 'Test2', '50'),
190('TBS', 'Test3', '230'),
191('Max', 'Test4', '560'),
192('Mustermann', 'Test5', '870'),
193('Nut zer', 'Test6', '58'),
194('Test', 'Test7', '556'),
195('ITF16b', 'Test8', '5023'),
196('Bochum', 'Test9', '540'),
197('Usr', 'Test10', '80');
198
199-- Nihat
200INSERT INTO tblVersand(name)
201VALUES
202(‘DHL’),
203(‘Hermes’),
204(‘GLS’),
205(‘UPS’),
206(‘DPD’);
207
208-- Schülzky
209INSERT INTO tblartikel (name,beschreibung)
210 VALUES
211 ('HP00','notebook1'),
212 ('HP01','notebook2'),
213 ('HP02','notebook3'),
214 ('HP03','notebook4'),
215 ('HP04','notebook5'),
216 ('HP05','notebook6'),
217 ('HP06','notebook7'),
218 ('HP12','notebook8'),
219 ('HP13','notebook9'),
220 ('HP55','notebook20'),
221 ('HP77','notebook12'),
222 ('HP88','notebook13'),
223 ('HP99','notebook15'),
224 ('HP334','notebook18');
225
226-- Florian Pötsch
227insert into tblversand (name, beschreibung, kosten)
228 values
229 ('Lucifer', 'Morningstar', '666'),
230 ('Disco', 'Pogo', 'Dingalingaling'),
231 ('Alle', 'Atzen', 'Sing'),
232 ('A', 'B', 'C'),
233 ('D', 'E', 'F'),
234 ('Lucifer', 'Morningstar', '666'),
235 ('Lucifer', 'Morningstar', '666'),
236 ('Lucifer', 'Morningstar', '666'),
237 ('Lucifer', 'Morningstar', '666');
238
239
240LOAD DATA LOCAL INFILE '***'
241#REPLACE
242INTO TABLE ***
243CHARACTER SET utf8
244FIELDS TERMINATED BY ';'
245OPTIONALLY ENCLOSED BY '"'
246LINES TERMINATED BY '\r\n'
247# Linux:
248# IGNORE 1 LINES
249(<spalten>);
250
251INSERT INTO table_name
252VALUES
253(value1, value2, value3, ...),
254(value1, value2, value3, ...),
255(value1, value2, value3, ...),
256(value1, value2, value3, ...)
257;
258
259----- Bertram et al
260INSERT INTO
261 tblArtikel (name, beschreibung)
262 VALUES
263 ('Acer Predator Helios 300 (G3-572-79KL)', 'y0y0y0 n1 description'),
264 ('HP Pavilion Power', 'power'),
265 ('MacBook Pro', 'y0'),
266 ('Razer d000ge', 'zerstört sich von selbst nach ablauf der garantie'),
267 ('Razer noch mehr dogshit', 'auch kabutt nach garantie'),
268 ('Roccat Kone', 'stabil')
269;
270
271LOAD DATA LOCAL INFILE 'Artikel.csv'
272INTO TABLE tblartikel
273FIELDS TERMINATED BY '\,'
274OPTIONALLY ENCLOSED BY '"'
275LINES TERMINATED BY '\r\n'
276(name,beschreibung);
277
278
279
280--- Galinski, Waide et al
281INSERT INTO tblzahlungsart (name) VALUES ('VISA');
282INSERT INTO tblzahlungsart (name) VALUES ('Bargeld lacht');
283INSERT INTO tblzahlungsart (name) VALUES ('Muscheln');
284INSERT INTO tblzahlungsart (name) VALUES ('Ãœberweisung');
285INSERT INTO tblzahlungsart (name) VALUES ('Paypal');
286
287#Pfad Anpassen!!!
288LOAD DATA LOCAL INFILE 'zahlungsart.csv' INTO TABLE tblzahlungsart COLUMNS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (name);
289
290SELECT * FROM tblzahlungsart;
291
292
293
294--- Schöckel et al
295INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Fahhrad', 'Platten reifen', '213');
296INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Auto', 'Platten reifen', '1');
297INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Fuß', 'dauert ewig', '453');
298INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('DPD', 'Fast', '453');
299INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Post', '3 Tage', '2');
300INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Gar nicht', 'kommt nie an', '312');
301INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('UPS', 'World shipping', '213');
302
303
304--- Schäfer et al
305INSERT INTO `tblkunde` (`name`, `vorname`, `strasse`, `plz`, `stadt`, `email`) VALUES
306 ('Heise', 'Kevin', 'Wasserstraße, 3', '42448', 'Bochum', 'test.bla@gmail.com'),
307 ('Soggi', 'Daniel', 'unter der Bruecke, 13', '47892', 'Bochum', 'soggi@gmx.de'),
308 ('Mustermann', 'Max', 'Musterstrasse ,1', '12345', 'Musterstadt', 'mustermail@musterprovider.muster'),
309 ('Wasgehtsiedasan', 'Kevin', 'Schneckenstraße, 12', '44544', 'Bikini Bottom', 'wasgehtsiedasan@gmail.com'),
310 ('Hackfleischhackendezerhasser', 'Der', 'Muschelsand Superhighway', '99999', 'Bikini Bottom', 'zerhacker@bikinibotton.de')
311 ;
312
313LOAD DATA LOCAL INFILE 'loadfile.csv'
314INTO TABLE tblKunde
315CHARACTER SET utf8
316FIELDS TERMINATED BY ';'
317LINES TERMINATED BY '\r\n'
318(`name`, `vorname`, `strasse`, `plz`, `stadt`, `email`);
319
320--- Brünenkamp, Carstensen et al
321SELECT * FROM mydb.tblartikel;
322
323LOAD DATA LOCAL INFILE 'test.csv'
324INTO TABLE tblartikel
325FIELDS TERMINATED BY ';'
326OPTIONALLY ENCLOSED BY '"'
327LINES TERMINATED BY '\r\n'
328(`name`, `beschreibung`);
329
330INSERT INTO tblArtikel (name, beschreibung)
331VALUES
332('Maus','Maus'),
333('Notebook','Notebook'),
334('SSD','SSD 512GB'),
335('Schreibtisch','groß'),
336('Stift','Kugelschreiber'),
337('Monitor','NEC 3D')
338;
339
340
341SHOW DATABASES;
342SHOW TABLES;
343SHOW COLUMNS FROM tblArtikel;
344SHOW EXTENDED COLUMNS FROM tblArtikel;
345DESCRIBE tblArtikel;
346EXPLAIN tblArtikel;
347
348SELECT * FROM tblArtikel;
349SELECT name, beschreibung FROM tblArtikel;
350
351#Kevin Heise
352#09.05.2018
353#über Muschelgeld
354#zu Fuß
355
356INSERT INTO tblRechnung(datum, vid, zid, kid)
357VALUES('2018-05-09',3,3,1);
358
359INSERT INTO tblRechnung_has_tblArtikel(rid, aid)
360VALUES
361(1,1),
362(1,3),
363(1,5),
364(1,7),
365(1,9),
366(1,9),
367(1,1),
368(1,3),
369(1,5),
370(1,7),
371(1,1)
372;
373
374SELECT * FROM tblKunde;
375SELECT name, vorname FROM tblKunde;
376SELECT name AS Name, vorname as Vorname FROM tblKunde;
377SELECT name, vorname, strasse, stadt, email FROM tblKunde WHERE name = 'soundso';
378SELECT name, strasse, stadt, email FROM tblKunde WHERE name LIKE '_u%';
379
380# * alle Spalten
381# _ ein einzelnes Zeichen in Verbindung mit LIKE in der WHERE Klausel
382# % eine beliebige Zeichenfolge in Verbindung mit LIKE in der WHERE Klausel
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399# Ein neuer Kunde „Kunde“ kommt am 18.05.2018 und kauft die bereits geführten Artikel „Notebook“ (aid = 17) und „Maus“ (aid=18) ein. Gliedern Sie den Vorgang in Untervorgänge auf. Die DB „nbb“ und ihre Tabellen existieren bereits. Geben Sie auch "Hilfskommandos" usw. an, mit denen Sie z. B. eine id recherchieren (da wir Subselects noch nicht gehabt haben).
400
401Neuer Kunde -> kid
402Neue Rechnung -> mit kid und neue rid
403Positionen mit bestehenden Artikeln in die Positionstabelle einfügen.
404
405# Neuen Kunden anlegen
406INSERT INTO tblKunde (name, vorname) VALUES ('Zilberberg2', 'Nikita');
407# neue Rechnung anlegen
408SELECT kid FROM tblKunde WHERE name = 'Zilberberg2'; # --> 11
409INSERT INTO tblRechnung (datum, vid, zid, kid) VALUES ('2018-06-12', 1, 1, 11);
410SELECT rid FROM tblRechnung WHERE kid = 11 AND datum = '2018-06-12'; # ---> rid = 2
411# tblArtikel2Rechnung rid, aid = 17 bzw. rid, aid = 18
412INSERT INTO tblRechnung_has_tblArtikel (rid, aid) VALUES (3,17);
413INSERT INTO tblRechnung_has_tblArtikel (rid, aid) VALUES (3,18);
414
415
416
417
418
419
420
421
422
423
424
425
426
427# Nun möchte ich die Rechnung ausdrucken (mit allen Informationen)
428Kunde, Rechnung
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456JOINS
457
458SELECT * FROM tblRechnung;
459SELECT * FROM tblKunde;
460Verknüpfung zwischen Rechnung und Kunde über die Schlüssel fehlt
461--- > JOIN
462
463# 1. Versuch
464SELECT
465 name,
466 vorname,
467 datum
468FROM tblKunde
469INNER JOIN tblRechnung;
470
471# 2. Versuch
472SELECT
473 tblKunde.kid,
474 tblKunde.name,
475 tblKunde.vorname,
476 tblRechnung.rid,
477 tblRechnung.kid,
478 tblRechnung.datum
479FROM tblKunde
480INNER JOIN tblRechnung
481ON tblRechnung.kid = tblKunde.kid;
482--------------------------------------------> ITF16b
483# 3. Versuch alternativ , funktioniert auch nur bei gleichen Bezeichnern PK und FK
484SELECT
485 tblKunde.kid,
486 tblKunde.name,
487 tblKunde.vorname,
488 tblRechnung.rid,
489 tblRechnung.kid,
490 tblRechnung.datum
491FROM tblKunde
492INNER JOIN tblRechnung
493USING (kid);
494
495# 3. Versuch beliebter Fehler
496SELECT
497 tblKunde.kid,
498 tblKunde.name,
499 tblKunde.vorname,
500 tblRechnung.rid,
501 tblRechnung.kid,
502 tblRechnung.datum
503FROM tblKunde
504INNER JOIN tblRechnung
505ON tblRechnung.rid = tblKunde.kid;
506
507SELECT
508 tblKunde.kid,
509 tblKunde.name,
510 tblKunde.vorname,
511 tblRechnung.rid,
512 tblRechnung.kid,
513 tblRechnung.datum
514FROM tblKunde
515INNER JOIN tblRechnung
516ON tblRechnung.rid = tblKunde.alterdesbusfahrers;
517
518
519# LAST_INSERT_ID() liefert die letzt hinzugefügte ID des Systemes
520# Die Anzahl der Datensätze einer Tabelle mittels COUNT
521SELECT COUNT(kid) FROM tblKunde;
522# 2 Tabellen miteinander verbunden über einen INNER JOIN
523SELECT
524 tabelle1.spalte1,
525 tabelle2.spalte3,
526 tabelle1.spalte5,
527 tabelle2.spalte1
528FROM tabelle1
529INNER JOIN tabelle2
530ON tabelle1.PK = tabelle2.___FK___;
531# 2 Tabellen miteinander verbunden über einen INNER JOIN bei gleicher Bezeichnung von PK und FK
532SELECT
533 tabelle1.spalte1,
534 tabelle2.spalte3,
535 tabelle1.spalte5,
536 tabelle2.spalte1
537FROM tabelle1
538INNER JOIN tabelle2
539USING(PK/FK);