-- C:\Users\andreasalef>c:\xampp\mysql\bin\mysql --local-infile -u root -p
-- C:\Users\andreasalef>c:\xampp\mysql\bin\mysql -u root -p



DROP DATABASE IF EXISTS nbb;
-- DROP DATABASE NBB;
-- DROP DATABASE NBB;
/* DROP DATABASE NBB; */

CREATE DATABASE nbb;

USE nbb;

SET utf8 ;

-----------------------------------------------------
-- Table tblKunde
-- -----------------------------------------------------
CREATE TABLE tblKunde
(
  kid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  vorname VARCHAR(45),
  strasse VARCHAR(45),
  plz CHAR(5),
  stadt VARCHAR(45),
  email VARCHAR(45),
  PRIMARY KEY (kid)
) ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblArtikel
-- -----------------------------------------------------
CREATE TABLE tblArtikel (
  aid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  beschreibung VARCHAR(45),
  PRIMARY KEY (aid))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblVersand
-- -----------------------------------------------------
CREATE TABLE tblVersand (
  vid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  beschreibung VARCHAR(45),
  kosten VARCHAR(45),
  PRIMARY KEY (vid))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblZahlungsart
-- -----------------------------------------------------
CREATE TABLE tblZahlungsart (
  zid INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45),
  PRIMARY KEY (zid))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblRechnung
-- -----------------------------------------------------
CREATE TABLE tblRechnung (
  rid INT NOT NULL AUTO_INCREMENT,
  datum DATE,
  vid INT,
  zid INT,
  kid INT,
  PRIMARY KEY (rid),
  INDEX fk_tblRechnung_tblVersand1_idx (vid ASC),
  INDEX fk_tblRechnung_tblZahlungsart1_idx (zid ASC),
  INDEX fk_tblRechnung_tblKunde1_idx (kid ASC),
  CONSTRAINT fk_tblRechnung_tblVersand1
    FOREIGN KEY (vid)
    REFERENCES tblVersand (vid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_tblRechnung_tblZahlungsart1
    FOREIGN KEY (zid)
    REFERENCES tblZahlungsart (zid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_tblRechnung_tblKunde1
    FOREIGN KEY (kid)
    REFERENCES tblKunde (kid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tblRechnung_has_tblArtikel
-- -----------------------------------------------------
CREATE TABLE tblRechnung_has_tblArtikel (
  a2rid INT NOT NULL AUTO_INCREMENT,
  rid INT NOT NULL,
  aid INT NOT NULL,
  INDEX fk_tblRechnung_has_tblArtikel_tblArtikel1_idx (aid ASC),
  INDEX fk_tblRechnung_has_tblArtikel_tblRechnung1_idx (rid ASC),
  PRIMARY KEY (a2rid),
  CONSTRAINT fk_tblRechnung_has_tblArtikel_tblRechnung1
    FOREIGN KEY (rid)
    REFERENCES tblRechnung (rid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_tblRechnung_has_tblArtikel_tblArtikel1
    FOREIGN KEY (aid)
    REFERENCES tblArtikel (aid)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


--- SQL Datei:  https://pastebin.com/YrXSLpCd
--- mwb-Datei:

Einfügen von Daten:
1. INSERT INTO
2. Per csv-Datei und LOAD DATA LOCAL INFILE
   - csv-Datei erstellen
	- einlesen mit LLI


-- Zilberberg
INSERT INTO tblVersand (name,beschreibung,kosten)
    VALUES
        ('Andreas','Alef','drölf'),
        ('Mark','Blef','drölf'),
        ('Paulo','Clef','drölf'),
        ('Leo','Dlef','drölf'),
        ('Maria','Elef','drölf'),
        ('Ronaldo','Flef','drölf'),
        ('Arnold','Jlef','drölf'),
        ('ACE','Ülef','drölf'),
        ('Klivlend','Älef','drölf'),
        ('Dude','Ölef','drölf'),
        ('Ernesti','Zlef','drölf')
;

-- Gallistl

-- Grautstück
INSERT INTO tblZahlungsart(name)
        VALUES
                ('PayPal'),
                ('giropay'),
                ('Sofortueberweisung'),
                ('EC'),
                ('Visa'),
                ('MasterCard'),
                ('American Express'),
                ('Nachnahme'),
                ('Vorkasse'),
                ('Rechnung')
        ;

-- Jan Savas
INSERT INTO tblArtikel (name) VALUES (‘Acer Laptop’);
INSERT INTO tblArtikel (name) VALUES (‘Lenovo Laptop’);
INSERT INTO tblArtikel (name) VALUES (‘Dell Laptop’);
INSERT INTO tblArtikel (name) VALUES (‘HP Laptop’);
INSERT INTO tblArtikel (name) VALUES (‘Macbook’);
INSERT INTO tblArtikel (name) VALUES (‘Logitech Maus’);
INSERT INTO tblArtikel (name) VALUES (‘Logitech Tastatur’);
INSERT INTO tblArtikel (name) VALUES (‘Logitech Maus Tastatur Set’);
INSERT INTO tblArtikel (name) VALUES (‘Logitech Headset’);
INSERT INTO tblArtikel (name) VALUES (‘24” Monitor’);
INSERT INTO tblArtikel (name) VALUES (‘27” Monitor’);

-- Gallistl
INSERT INTO tblZahlungsart (name) VALUES ('Kreditkarte');
INSERT INTO tblZahlungsart (name) VALUES ('Vorkasse');
INSERT INTO tblZahlungsart (name) VALUES ('Visa');
INSERT INTO tblZahlungsart (name) VALUES ('Mastercard');
INSERT INTO tblZahlungsart (name) VALUES ('EC-Karte');
INSERT INTO tblZahlungsart (name) VALUES ('Leasing');
INSERT INTO tblZahlungsart (name) VALUES ('Bar');
INSERT INTO tblZahlungsart (name) VALUES ('Girocard');

-- Zistler
INSERT INTO tblversand (name, beschreibung, kosten)
VALUES
('Nutzer', 'Test2', '50'),
('TBS', 'Test3', '230'),
('Max', 'Test4', '560'),
('Mustermann', 'Test5', '870'),
('Nut zer', 'Test6', '58'),
('Test', 'Test7', '556'),
('ITF16b', 'Test8', '5023'),
('Bochum', 'Test9', '540'),
('Usr', 'Test10', '80');

-- Nihat
INSERT INTO tblVersand(name)
VALUES
(‘DHL’),
(‘Hermes’),
(‘GLS’),
(‘UPS’),
(‘DPD’);

-- Schülzky
INSERT INTO tblartikel (name,beschreibung)
	VALUES
		('HP00','notebook1'),
		('HP01','notebook2'),
		('HP02','notebook3'),
		('HP03','notebook4'),
		('HP04','notebook5'),
		('HP05','notebook6'),
		('HP06','notebook7'),
		('HP12','notebook8'),
		('HP13','notebook9'),
		('HP55','notebook20'),
		('HP77','notebook12'),
		('HP88','notebook13'),
		('HP99','notebook15'),
		('HP334','notebook18');

-- Florian Pötsch
insert into tblversand (name, beschreibung, kosten)
		values
				('Lucifer', 'Morningstar', '666'),
				('Disco', 'Pogo', 'Dingalingaling'),
				('Alle', 'Atzen', 'Sing'),
				('A', 'B', 'C'),
				('D', 'E', 'F'),
				('Lucifer', 'Morningstar', '666'),
				('Lucifer', 'Morningstar', '666'),
				('Lucifer', 'Morningstar', '666'),
				('Lucifer', 'Morningstar', '666');


LOAD DATA LOCAL INFILE '***'
#REPLACE
INTO TABLE ***
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
# Linux:
# IGNORE 1 LINES
(<spalten>);

INSERT INTO table_name
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...),
(value1, value2, value3, ...),
(value1, value2, value3, ...)
;

----- Bertram et al
INSERT INTO
     tblArtikel (name, beschreibung)
	  VALUES
	  ('Acer Predator Helios 300 (G3-572-79KL)', 'y0y0y0 n1 description'),
	  ('HP Pavilion Power', 'power'),
	  ('MacBook Pro', 'y0'),
	  ('Razer d000ge', 'zerstört sich von selbst nach ablauf der garantie'),
	  ('Razer noch mehr dogshit', 'auch kabutt nach garantie'),
	  ('Roccat Kone', 'stabil')
;

LOAD DATA LOCAL INFILE 'Artikel.csv'
INTO TABLE tblartikel
FIELDS TERMINATED BY '\,'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(name,beschreibung);



--- Galinski, Waide et al
INSERT INTO tblzahlungsart (name) VALUES ('VISA');
INSERT INTO tblzahlungsart (name) VALUES ('Bargeld lacht');
INSERT INTO tblzahlungsart (name) VALUES ('Muscheln');
INSERT INTO tblzahlungsart (name) VALUES ('Überweisung');
INSERT INTO tblzahlungsart (name) VALUES ('Paypal');

#Pfad Anpassen!!!
LOAD DATA LOCAL INFILE 'zahlungsart.csv' INTO TABLE tblzahlungsart COLUMNS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (name);

SELECT * FROM tblzahlungsart;



--- Schöckel et al
INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Fahhrad', 'Platten reifen', '213');
INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Auto', 'Platten reifen', '1');
INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Fuß', 'dauert ewig', '453');
INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('DPD', 'Fast', '453');
INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Post', '3 Tage', '2');
INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('Gar nicht', 'kommt nie an', '312');
INSERT INTO tblVersand (`name`, `beschreibung`, `kosten`) VALUES ('UPS', 'World shipping', '213');


--- Schäfer et al
INSERT INTO `tblkunde` (`name`, `vorname`, `strasse`, `plz`, `stadt`, `email`) VALUES
	('Heise', 'Kevin', 'Wasserstraße, 3', '42448', 'Bochum', 'test.bla@gmail.com'),
	('Soggi', 'Daniel', 'unter der Bruecke, 13', '47892', 'Bochum', 'soggi@gmx.de'),
	('Mustermann', 'Max', 'Musterstrasse ,1', '12345', 'Musterstadt', 'mustermail@musterprovider.muster'),
	('Wasgehtsiedasan', 'Kevin', 'Schneckenstraße, 12', '44544', 'Bikini Bottom', 'wasgehtsiedasan@gmail.com'),
	('Hackfleischhackendezerhasser', 'Der', 'Muschelsand Superhighway', '99999', 'Bikini Bottom', 'zerhacker@bikinibotton.de')
	;

LOAD DATA LOCAL INFILE 'loadfile.csv'
INTO TABLE tblKunde
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
(`name`, `vorname`, `strasse`, `plz`, `stadt`, `email`);

--- Brünenkamp, Carstensen et al
SELECT * FROM mydb.tblartikel;

LOAD DATA LOCAL INFILE 'test.csv'
INTO TABLE tblartikel
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(`name`, `beschreibung`);

INSERT INTO tblArtikel (name, beschreibung)
VALUES
('Maus','Maus'),
('Notebook','Notebook'),
('SSD','SSD 512GB'),
('Schreibtisch','groß'),
('Stift','Kugelschreiber'),
('Monitor','NEC 3D')
;


SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM tblArtikel;
SHOW EXTENDED COLUMNS FROM tblArtikel;
DESCRIBE   tblArtikel;
EXPLAIN    tblArtikel;

SELECT * FROM tblArtikel;
SELECT name, beschreibung FROM tblArtikel;

#Kevin Heise
#09.05.2018
#über Muschelgeld
#zu Fuß

INSERT INTO tblRechnung(datum, vid, zid, kid)
VALUES('2018-05-09',3,3,3);
INSERT INTO tblRechnung(datum, vid, zid, kid)
VALUES('2018-05-10',3,3,3);
INSERT INTO tblRechnung(datum, vid, zid, kid)
VALUES('2018-05-11',3,3,3);
INSERT INTO tblRechnung(datum, vid, zid, kid)
VALUES('2018-05-13',3,3,2);
INSERT INTO tblRechnung(datum, vid, zid, kid)
VALUES('2018-05-14',3,3,2);

INSERT INTO tblRechnung_has_tblArtikel(rid, aid)
VALUES
(1,1),
(1,3),
(1,5),
(1,7),
(1,9),
(1,9),
(1,1),
(1,3),
(1,5),
(1,7),
(1,1)
;


INSERT INTO tblRechnung_has_tblArtikel(rid, aid)
VALUES
(2,3),
(2,4),
(2,6),
(2,9),
(2,2),
(2,5),
(2,5),
(2,5),
(2,5),
(2,5),
(2,5)
;


INSERT INTO tblRechnung_has_tblArtikel(rid, aid)
VALUES
(3,3),
(3,4),
(3,6),
(3,9),
(3,2),
(3,5),
(3,5),
(3,5),
(3,5),
(3,5),
(3,5)
;


INSERT INTO tblRechnung_has_tblArtikel(rid, aid)
VALUES
(4,3),
(4,4),
(4,6),
(4,5),
(4,5)
;
INSERT INTO tblRechnung_has_tblArtikel(rid, aid)
VALUES
(5,5),
(5,5),
(5,5),
(5,5),
(5,5),
(5,5)
;


SELECT * FROM tblKunde;
SELECT name, vorname FROM tblKunde;
SELECT name AS Name, vorname as Vorname FROM tblKunde;
SELECT name, vorname, strasse, stadt, email FROM tblKunde WHERE name = 'soundso';
SELECT name, strasse, stadt, email FROM tblKunde WHERE name LIKE '_u%';

# * alle Spalten
# _ ein einzelnes Zeichen in Verbindung mit LIKE in der WHERE Klausel
# % eine beliebige Zeichenfolge in Verbindung mit LIKE in der WHERE Klausel
















# 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).

Neuer Kunde -> kid
Neue Rechnung -> mit kid und neue rid
Positionen mit bestehenden Artikeln in die Positionstabelle einfügen.

# Neuen Kunden anlegen
INSERT INTO tblKunde (name, vorname) VALUES ('Zilberberg2', 'Nikita');
# neue Rechnung anlegen
SELECT kid FROM tblKunde WHERE name = 'Zilberberg2'; # --> 11
INSERT INTO tblRechnung (datum, vid, zid, kid) VALUES ('2018-06-12', 1, 1, 11);
SELECT rid FROM tblRechnung WHERE kid = 11 AND datum = '2018-06-12'; # ---> rid = 2
# tblArtikel2Rechnung rid, aid = 17 bzw. rid, aid = 18
INSERT INTO tblRechnung_has_tblArtikel (rid, aid) VALUES (3,17);
INSERT INTO tblRechnung_has_tblArtikel (rid, aid) VALUES (3,18);



# Nun möchte ich die Rechnung ausdrucken (mit allen Informationen)
Kunde, Rechnung
JOINS

SELECT * FROM tblRechnung;
SELECT * FROM tblKunde;
Verknüpfung zwischen Rechnung und Kunde über die Schlüssel fehlt
--- > JOIN

# 1. Versuch
SELECT
   name,
	vorname,
	datum
FROM tblKunde
INNER JOIN tblRechnung;

# 2. Versuch
SELECT
   tblKunde.kid,
	tblKunde.name,
	tblKunde.vorname,
	tblRechnung.rid,
	tblRechnung.kid,
	tblRechnung.datum
FROM tblKunde
INNER JOIN tblRechnung
ON tblRechnung.kid = tblKunde.kid;
# ON FK            = PK
--------------------------------------------> ITF16b
# 3. Versuch alternativ	, funktioniert auch nur bei gleichen Bezeichnern PK und FK
SELECT
   tblKunde.kid,
	tblKunde.name,
	tblKunde.vorname,
	tblRechnung.rid,
	tblRechnung.kid,
	tblRechnung.datum
FROM tblKunde
INNER JOIN tblRechnung
USING (kid);

# 3. Versuch beliebter Fehler
SELECT
   tblKunde.kid,
	tblKunde.name,
	tblKunde.vorname,
	tblRechnung.rid,
	tblRechnung.kid,
	tblRechnung.datum
FROM tblKunde
INNER JOIN tblRechnung
ON tblRechnung._r_id = tblKunde.kid;

SELECT
   tblKunde.kid,
	tblKunde.name,
	tblKunde.vorname,
	tblRechnung.rid,
	tblRechnung.kid,
	tblRechnung.datum
FROM tblKunde
INNER JOIN tblRechnung
ON tblRechnung.rid = tblKunde.alterdesbusfahrers;


# LAST_INSERT_ID() liefert die letzt hinzugefügte ID des Systemes
# Die Anzahl der Datensätze einer Tabelle mittels COUNT

SELECT COUNT(kid) FROM tblKunde;

# 2 Tabellen miteinander verbunden über einen INNER JOIN
SELECT
   tabelle1.spalte1,
   tabelle2.spalte3,
   tabelle1.spalte5,
   tabelle2.spalte1
FROM tabelle1
INNER JOIN tabelle2
ON tabelle1.PK = tabelle2.___FK___;

# 2 Tabellen miteinander verbunden über einen INNER JOIN bei gleicher Bezeichnung von PK und FK
SELECT
   tabelle1.spalte1,
   tabelle2.spalte3,
   tabelle1.spalte5,
   tabelle2.spalte1
FROM tabelle1
INNER JOIN tabelle2
USING(PK/FK);


# IHK, aber unschön
SELECT ... FROM 1.Tabelle, 2.Tabelle WHERE PK=FK



############# ITF16b, auch in unterschiedlichen Reihenfolgen
# Name usw, Rechnungsdatum, alle Artikel

SELECT
tblKunde.vorname AS Vorname,
tblKunde.name AS Name,
tblRechnung.rid AS Rechnungsnummer,
tblRechnung.datum AS Datum,
tblArtikel.name AS Artikel
FROM tblKunde
INNER JOIN tblRechnung
ON tblKunde.kid = tblRechnung.kid
INNER JOIN tblRechnung_has_tblArtikel
ON tblRechnung.rid = tblRechnung_has_tblArtikel.rid
INNER JOIN tblArtikel
ON tblRechnung_has_tblArtikel.aid = tblArtikel.aid;

SELECT
k.vorname AS Vorname,
k.name AS Name,
r.rid AS Rechnungsnummer,
r.datum AS Datum,
a.name AS Artikel
FROM tblKunde k
INNER JOIN tblRechnung r
USING(kid)
INNER JOIN tblRechnung_has_tblArtikel r2a
USING(rid)
INNER JOIN tblArtikel a
USING(aid);

SELECT
   tblKunde.kid,
	tblKunde.name,
	tblKunde.vorname,
	tblRechnung.rid,
	tblRechnung.kid,
	tblRechnung.datum
FROM tblKunde
LEFT JOIN tblRechnung
ON tblRechnung.kid = tblKunde.kid;





#### ITF16b





SELECT
   tblKunde.kid,
	tblKunde.name,
	tblKunde.vorname,
	tblRechnung.rid,
	tblRechnung.kid,
	tblRechnung.datum
FROM tblKunde
RIGHT JOIN tblRechnung
ON tblRechnung.kid = tblKunde.kid;








# Wiederholung:
# Neuen Kunden anlegen
INSERT INTO tblKunde (name, vorname) VALUES ('Zilberberg2', 'Nikita');
# neue Rechnung anlegen
SELECT kid FROM tblKunde WHERE name = 'Zilberberg2'; # --> 11
INSERT INTO tblRechnung (datum, vid, zid, kid) VALUES ('2018-06-12', 1, 1, 11);
SELECT rid FROM tblRechnung WHERE kid = 11 AND datum = '2018-06-12'; # ---> rid = 2
# tblArtikel2Rechnung rid, aid = 17 bzw. rid, aid = 18
INSERT INTO tblRechnung_has_tblArtikel (rid, aid) VALUES (2,17), (2,17);

INSERT INTO tblKunde (name, vorname) VALUES ('Zilberberg3', 'Nikita');
INSERT INTO tblRechnung (datum, vid, zid, kid) VALUES ('2018-06-12', 1, 1, LAST_INSERT_ID());

INSERT INTO tblKunde (name, vorname) VALUES ('Zilberberg3', 'Nikita');
INSERT INTO tblRechnung (datum, vid, zid, kid) VALUES ('2018-06-12', 1, 1, (SELECT kid FROM tblKunde WHERE name = 'Zilberberg3'));






SELECT
   tblKunde.kid,
	tblKunde.name,
	tblKunde.vorname,
	tblRechnung.rid,
	tblRechnung.kid,
	tblRechnung.datum
FROM tblRechnung
LEFT JOIN tblKunde
ON tblKunde.kid = tblRechnung.kid;


# Spalte hinzufügen
ALTER TABLE tblArtikel
ADD preis INT NOT NULL;

# Spalte löschen
ALTER TABLE tblArtikel
DROP preis;

# Spalte hinzufügen
ALTER TABLE tblArtikel
ADD preis INT NOT NULL DEFAULT 100;

# Oder Spalte bearbeiten
UPDATE tblArtikel
SET preis = 50;

# einzelne Werte setzen
UPDATE tblArtikel
SET preis = 50000
WHERE aid = 5;

UPDATE tblArtikel
SET preis = tblArtikel.aid*100
WHERE aid = 5;

UPDATE tblArtikel
SET preis = tblArtikel.preis*1.1
WHERE name LIKE '%HP%';


SELECT RAND()*(10-5)+5;


# alle Rechnungen mit Artikel, Kunde und Einzelpreisen
SELECT
k.vorname AS Vorname,
k.name AS Name,
r.rid AS Rechnungsnummer,
r.datum AS Datum,
a.name AS Artikel,
a.preis/100 AS Euro
FROM tblKunde k
INNER JOIN tblRechnung r
USING(kid)
INNER JOIN tblRechnung_has_tblArtikel r2a
USING(rid)
INNER JOIN tblArtikel a
USING(aid);

# Aggregatfunktionen in mysql
# COUNT()
# AVG() = SUM()/COUNT()
# SUM()
# MIN()
# MAX()













# Gesamtsumme aller Rechnungen (Umsatz)

SELECT
SUM(a.preis/100) AS Gesamtsumme
FROM tblRechnung r
INNER JOIN tblRechnung_has_tblArtikel r2a
USING(rid)
INNER JOIN tblArtikel a
USING(aid);