Kapitel 13: Basis-Praxisprojekte ⭐⭐

In diesem Kapitel wirst du dein Wissen aus den vorherigen Kapiteln in 3 Praxisprojekten anwenden!

Praxis 1: Studentenverwaltung (Basis-Operationen)

13.1 Anforderungsanalyse

Szenario: Erstelle eine Datenbank für eine Schule, um Studentendaten zu verwalten.

Anforderungen:

1. Studentendaten speichern (ID, Name, Alter, Stadt)
2. Studenten hinzufügen, ändern, löschen
3. Studenten abfragen (alle, nach Stadt, nach Alter)
4. Statistiken erstellen (Anzahl pro Stadt, Durchschnittsalter)

13.2 Kern-Implementierung

Schritt 1: Datenbank und Tabelle erstellen (DDL)

-- Datenbank erstellen
CREATE DATABASE IF NOT EXISTS schule_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE schule_db;

-- Tabelle "students" erstellen
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    vorname VARCHAR(50) NOT NULL,
    nachname VARCHAR(50) NOT NULL,
    alter INT,
    stadt VARCHAR(50),
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Schritt 2: Daten einfügen (DML)

INSERT INTO students (vorname, nachname, alter, stadt)
VALUES 
    ('Max', 'Mustermann', 20, 'Berlin'),
    ('Lisa', 'Schmidt', 19, 'Hamburg'),
    ('John', 'Doe', 22, 'Berlin'),
    ('Jane', 'Smith', 21, 'München'),
    ('Paul', 'Müller', 20, 'Hamburg');

Schritt 3: Daten abfragen (DQL)

-- 1. Alle Studenten abfragen
SELECT * FROM students;

-- 2. Nur Namen und Stadt abfragen
SELECT CONCAT(vorname, ' ', nachname) AS voller_name, stadt 
FROM students;

-- 3. Studenten aus Berlin abfragen
SELECT * FROM students 
WHERE stadt = 'Berlin';

-- 4. Studenten über 20 Jahre abfragen
SELECT * FROM students 
WHERE alter > 20;

-- 5. Studenten nach Alter sortieren (absteigend)
SELECT * FROM students 
ORDER BY alter DESC;

Schritt 4: Daten ändern und löschen (DML)

-- 1. Alter von Max aktualisieren
UPDATE students 
SET alter = 21 
WHERE vorname = 'Max';

-- 2. Studenten aus München löschen
DELETE FROM students 
WHERE stadt = 'München';

-- 3. Stadt von John auf 'Köln' ändern
UPDATE students 
SET stadt = 'Köln' 
WHERE vorname = 'John';

13.3 Vollständige SQL-Anweisungen schreiben und testen

Aufgabe 1: Statistiken erstellen

-- Anzahl der Studenten pro Stadt
SELECT stadt, COUNT(*) AS anzahl 
FROM students 
GROUP BY stadt;

-- Durchschnittsalter berechnen
SELECT AVG(alter) AS durchschnittsalter 
FROM students;

-- Ältester und jüngster Student
SELECT 
    MAX(alter) AS aeltester,
    MIN(alter) AS juengster 
FROM students;

Aufgabe 2: Komplexe Abfrage

-- Studenten abfragen, deren Name mit 'M' beginnt
SELECT * FROM students 
WHERE vorname LIKE 'M%';

-- Die 2 ältesten Studenten abfragen
SELECT * FROM students 
ORDER BY alter DESC 
LIMIT 2;

---

Praxis 2: Waren und Bestellungen (Mehrere Tabellen)

13.4 Anforderungsanalyse

Szenario: Erstelle eine Datenbank für einen Online-Shop.

Anforderungen:

1. Produktdaten speichern (ID, Name, Preis, Bestand)
2. Bestellungen speichern (ID, Studenten-ID, Produkt-ID, Menge)
3. Mehrere Tabellen verknüpfen (JOIN)
4. Statistiken erstellen (Umsatz pro Student, beliebteste Produkte)

13.5 Kern-Implementierung

Schritt 1: Tabellen erstellen (DDL)

-- Tabelle "products" erstellen
CREATE TABLE products (
    produkt_id INT PRIMARY KEY AUTO_INCREMENT,
    produkt_name VARCHAR(200) NOT NULL,
    preis DECIMAL(10,2) NOT NULL,
    bestand INT DEFAULT 0,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle "orders" erstellen
CREATE TABLE orders (
    bestell_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    produkt_id INT,
    menge INT NOT NULL,
    bestell_datum DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (produkt_id) REFERENCES products(produkt_id)
);

Schritt 2: Daten einfügen (DML)

-- Produkte einfügen
INSERT INTO products (produkt_name, preis, bestand)
VALUES 
    ('T-Shirt', 19.99, 100),
    ('Jeans', 59.99, 50),
    ('Sneaker', 89.99, 30);

-- Bestellungen einfügen
INSERT INTO orders (student_id, produkt_id, menge)
VALUES 
    (1, 1, 2),  -- Max kauft 2 T-Shirts
    (1, 2, 1),  -- Max kauft 1 Jeans
    (2, 3, 1);  -- Lisa kauft 1 Sneaker

13.6 Mehrere Tabellen abfragen, Statistiken erstellen

Schritt 3: Tabellenverbindungen (JOIN)

-- 1. Alle Bestellungen mit Studenten- und Produktnamen abfragen
SELECT 
    s.vorname,
    p.produkt_name,
    o.menge,
    p.preis,
    (o.menge * p.preis) AS gesamtpreis
FROM orders o
INNER JOIN students s ON o.student_id = s.student_id
INNER JOIN products p ON o.produkt_id = p.produkt_id;

-- 2. Umsatz pro Student berechnen
SELECT 
    s.vorname,
    SUM(o.menge * p.preis) AS umsatz
FROM orders o
INNER JOIN students s ON o.student_id = s.student_id
INNER JOIN products p ON o.produkt_id = p.produkt_id
GROUP BY s.student_id, s.vorname;

-- 3. Beliebteste Produkte (nach Verkaufsmenge)
SELECT 
    p.produkt_name,
    SUM(o.menge) AS verkaufte_menge
FROM orders o
INNER JOIN products p ON o.produkt_id = p.produkt_id
GROUP BY p.produkt_id, p.produkt_name
ORDER BY verkaufte_menge DESC;

---

Praxis 3: Benutzer-Login (Transaktionen)

13.7 Anforderungsanalyse

Szenario: Erstelle eine Benutzerverwaltung mit Login-Funktion.

Anforderungen:

1. Benutzerdaten speichern (ID, Benutzername, E-Mail, Passwort)
2. Registrierung (Daten einfügen)
3. Login (Daten abfragen)
4. Transaktionen verwenden (für Datensicherheit)

13.8 Kern-Implementierung

Schritt 1: Tabelle erstellen (DDL)

CREATE DATABASE IF NOT EXISTS auth_db;
USE auth_db;

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    benutzername VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    passwort_hash VARCHAR(255) NOT NULL,
    ist_aktiv BOOLEAN DEFAULT TRUE,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP
);

Schritt 2: Registrierung (INSERT mit Transaktion)

-- Transaktion starten
START TRANSACTION;

-- Benutzer einfügen
INSERT INTO users (benutzername, email, passwort_hash)
VALUES ('max_mustermann', 'max@email.de', SHA2('mein_passwort', 256));

-- Erfolg prüfen
SELECT * FROM users WHERE benutzername = 'max_mustermann';

-- Wenn erfolgreich: COMMIT
COMMIT;

-- Wenn Fehler: ROLLBACK
-- ROLLBACK;

Schritt 3: Login (SELECT)

-- Login-Überprüfung
SELECT * FROM users 
WHERE benutzername = 'max_mustermann' 
  AND passwort_hash = SHA2('mein_passwort', 256)
  AND ist_aktiv = TRUE;

Schritt 4: Datensicherheit mit Transaktionen

-- Beispiel: Passwort ändern (mit Transaktion)
START TRANSACTION;

-- Altes Passwort überprüfen
SELECT * FROM users 
WHERE user_id = 1 
  AND passwort_hash = SHA2('altes_passwort', 256);

-- Wenn korrekt: Passwort aktualisieren
UPDATE users 
SET passwort_hash = SHA2('neues_passwort', 256) 
WHERE user_id = 1;

-- Bestätigen
COMMIT;

13.9 Vollständige SQL-Anweisungen schreiben

Aufgabe: Schreibe SQL für:

1. Neuen Benutzer registrieren
2. Benutzer-Login überprüfen
3. Benutzer-Passwort ändern
4. Benutzer-Konto deaktivieren

-- 1. Registrierung
START TRANSACTION;
INSERT INTO users (benutzername, email, passwort_hash)
VALUES ('lisa_schmidt', 'lisa@email.de', SHA2('lisa123', 256));
COMMIT;

-- 2. Login
SELECT user_id, benutzername, email 
FROM users 
WHERE benutzername = 'lisa_schmidt' 
  AND passwort_hash = SHA2('lisa123', 256);

-- 3. Passwort ändern
START TRANSACTION;
UPDATE users 
SET passwort_hash = SHA2('neues_passwort', 256) 
WHERE benutzername = 'lisa_schmidt';
COMMIT;

-- 4. Konto deaktivieren
UPDATE users 
SET ist_aktiv = FALSE 
WHERE user_id = 2;

---

Zusammenfassung

In diesem Kapitel hast du gelernt:

• ✅ Eine Studentenverwaltung zu erstellen (DDL, DML, DQL)
• ✅ Eine Shop-Datenbank mit mehreren Tabellen zu erstellen (JOIN)
• ✅ Eine Benutzer-Login-Funktion mit Transaktionen zu erstellen
• ✅ Dein Wissen aus den vorherigen Kapiteln in der Praxis anzuwenden

Übungen

1. Studentenverwaltung: Füge ein Feld klasse zur Tabelle students hinzu
2. Shop: Erstelle eine Tabelle categories und verknüpfe sie mit products
3. Login: Füge eine Tabelle login_logs hinzu, um Login-Versuche zu protokollieren

Nächstes Kapitel

Im nächsten Kapitel lernen wir MySQL-Sicherheit - wie man SQL-Injection verhindert, Berechtigungen verwaltet und Backups erstellt!