Kapitel 14: Fortgeschrittene Praxis ⭐⭐

In diesem Kapitel wirst du zwei umfassende Praxisprojekte umsetzen, die typische Anforderungen aus der Webentwicklung abdecken.

Praxis 4: Persönliche Blog-Datenbank

14.1 Anforderungsanalyse

Szenario: Erstelle eine Datenbank für ein persönliches Blog-System.

Kernanforderungen:

Benutzer können sich registrieren und anmelden
Benutzer können Blog-Artikel veröffentlichen
Artikel können in Kategorien eingeteilt werden
Leser können Kommentare hinterlassen
Statistiken (Anzahl Artikel, Kommentare) müssen erstellt werden

14.2 Datenbankdesign

ER-Diagramm (Entity-Relationship):

[users] 1 ---- n [posts] 1 ---- 1 [categories]
                                n ---- n [comments]

Tabellenstruktur:

Tabelle	Felder	Beschreibung
`users`	user_id, username, email, passwort_hash, erstellt_am	Benutzerkonto
`categories`	kategorie_id, name, beschreibung	Blog-Kategorien
`posts`	post_id, user_id, kategorie_id, titel, inhalt, erstellt_am	Blog-Artikel
`comments`	comment_id, post_id, user_id, inhalt, erstellt_am	Kommentare

14.3 Kern-Implementierung

Schritt 1: Datenbank und Tabellen erstellen (DDL)

sql

CREATE DATABASE IF NOT EXISTS blog_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE blog_db;

-- 1. Tabelle "users" erstellen
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,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tabelle "categories" erstellen
CREATE TABLE categories (
    kategorie_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    beschreibung TEXT
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tabelle "posts" erstellen
CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    kategorie_id INT NOT NULL,
    titel VARCHAR(200) NOT NULL,
    inhalt TEXT NOT NULL,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (kategorie_id) REFERENCES categories(kategorie_id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Tabelle "comments" erstellen
CREATE TABLE comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    inhalt TEXT NOT NULL,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Schritt 2: Testdaten einfügen (DML)

sql

-- Kategorien einfügen
INSERT INTO categories (name, beschreibung)
VALUES 
    ('Technik', 'Technik-Themen'),
    ('Reisen', 'Reiseberichte'),
    ('Kochen', 'Rezepte und Kochtipps');

-- Benutzer einfügen
INSERT INTO users (benutzername, email, passwort_hash)
VALUES 
    ('max_blogger', 'max@blog.de', SHA2('pass123', 256)),
    ('lisa_writer', 'lisa@blog.de', SHA2('pass456', 256));

-- Artikel einfügen
INSERT INTO posts (user_id, kategorie_id, titel, inhalt)
VALUES 
    (1, 1, 'Mein erster Blogpost', 'Hallo Welt! Dies ist mein erster Post.'),
    (1, 2, 'Meine Reise nach Berlin', 'Berlin ist eine tolle Stadt...'),
    (2, 3, 'Einfaches Rezept', 'Hier ist ein einfaches Rezept...');

-- Kommentare einfügen
INSERT INTO comments (post_id, user_id, inhalt)
VALUES 
    (1, 2, 'Toller erster Post!'),
    (1, 1, 'Danke für den Kommentar!'),
    (2, 2, 'Ich möchte auch nach Berlin reisen.');

14.4 Abfragen schreiben (DQL)

Aufgabe 1: Alle Artikel mit Autor-Namen und Kategorienamen anzeigen

sql

SELECT 
    p.titel,
    u.benutzername AS autor,
    c.name AS kategorie,
    p.erstellt_am
FROM posts p
INNER JOIN users u ON p.user_id = u.user_id
INNER JOIN categories c ON p.kategorie_id = c.kategorie_id
ORDER BY p.erstellt_am DESC;

Aufgabe 2: Anzahl der Artikel pro Kategorie zählen

sql

SELECT 
    c.name AS kategorie,
    COUNT(p.post_id) AS anzahl_artikel
FROM categories c
LEFT JOIN posts p ON c.kategorie_id = p.kategorie_id
GROUP BY c.kategorie_id, c.name
ORDER BY anzahl_artikel DESC;

Aufgabe 3: Alle Kommentare zu einem bestimmten Artikel anzeigen

sql

SELECT 
    u.benutzername AS kommentator,
    co.inhalt AS kommentar,
    co.erstellt_am
FROM comments co
INNER JOIN users u ON co.user_id = u.user_id
WHERE co.post_id = 1
ORDER BY co.erstellt_am;

Aufgabe 4: Die 5 neuesten Artikel mit Anzahl der Kommentare anzeigen

sql

SELECT 
    p.titel,
    u.benutzername AS autor,
    COUNT(co.comment_id) AS anzahl_kommentare
FROM posts p
INNER JOIN users u ON p.user_id = u.user_id
LEFT JOIN comments co ON p.post_id = co.post_id
GROUP BY p.post_id, p.titel, u.benutzername
ORDER BY p.erstellt_am DESC
LIMIT 5;

Praxis 5: Einfaches Backend-Verwaltungssystem

14.5 Anforderungsanalyse

Szenario: Erstelle eine Datenbank für ein Backend-Verwaltungssystem.

Kernanforderungen:

Administratoren können sich anmelden
Benutzerkonten verwalten (aktivieren/deaktivieren)
Datenwörterbücher (Lookup-Tabellen) verwalten
CRUD-Operationen für alle Tabellen
Berechtigungsverwaltung (vereinfacht)

14.6 Datenbankdesign

Tabellenstruktur:

Tabelle	Felder	Beschreibung
`admins`	admin_id, benutzername, passwort_hash, letzter_login	Administratoren
`users`	user_id, benutzername, email, ist_aktiv, erstellt_am	Benutzerkonten
`data_dictionary`	dict_id, kategorie, schluessel, wert, beschreibung	Datenwörterbuch
`user_logs`	log_id, user_id, aktion, zeitstempel	Benutzeraktivitäten

14.7 Kern-Implementierung

Schritt 1: Datenbank und Tabellen erstellen (DDL)

sql

CREATE DATABASE IF NOT EXISTS backend_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE backend_db;

-- 1. Tabelle "admins" erstellen
CREATE TABLE admins (
    admin_id INT PRIMARY KEY AUTO_INCREMENT,
    benutzername VARCHAR(50) NOT NULL UNIQUE,
    passwort_hash VARCHAR(255) NOT NULL,
    letzter_login DATETIME
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tabelle "users" erstellen
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    benutzername VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    ist_aktiv BOOLEAN DEFAULT TRUE,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tabelle "data_dictionary" erstellen
CREATE TABLE data_dictionary (
    dict_id INT PRIMARY KEY AUTO_INCREMENT,
    kategorie VARCHAR(50) NOT NULL,
    schluessel VARCHAR(50) NOT NULL,
    wert VARCHAR(100) NOT NULL,
    beschreibung TEXT,
    UNIQUE KEY (kategorie, schluessel)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Tabelle "user_logs" erstellen
CREATE TABLE user_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    aktion VARCHAR(255) NOT NULL,
    zeitstempel DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Schritt 2: Testdaten einfügen (DML)

sql

-- Administrator einfügen
INSERT INTO admins (benutzername, passwort_hash)
VALUES ('admin', SHA2('admin123', 256));

-- Datenwörterbuch-Einträge einfügen
INSERT INTO data_dictionary (kategorie, schluessel, wert, beschreibung)
VALUES 
    ('user_status', 'active', 'Aktiv', 'Benutzer ist aktiv'),
    ('user_status', 'inactive', 'Inaktiv', 'Benutzer ist inaktiv'),
    ('user_role', 'admin', 'Administrator', 'Vollzugriff'),
    ('user_role', 'user', 'Benutzer', 'Eingeschränkter Zugriff');

-- Benutzer einfügen
INSERT INTO users (benutzername, email, ist_aktiv)
VALUES 
    ('user1', 'user1@email.de', TRUE),
    ('user2', 'user2@email.de', TRUE),
    ('user3', 'user3@email.de', FALSE);

-- Benutzer-Logs einfügen
INSERT INTO user_logs (user_id, aktion)
VALUES 
    (1, 'Login'),
    (1, 'Profil aktualisiert'),
    (2, 'Login');

14.8 Praxis: CRUD und Abfragen

Aufgabe 1: Alle aktiven Benutzer anzeigen (CRUD - Read)

sql

SELECT user_id, benutzername, email, erstellt_am
FROM users
WHERE ist_aktiv = TRUE
ORDER BY erstellt_am DESC;

Aufgabe 2: Benutzer deaktivieren (CRUD - Update)

sql

START TRANSACTION;

UPDATE users 
SET ist_aktiv = FALSE 
WHERE user_id = 2;

-- Log-Eintrag erstellen
INSERT INTO user_logs (user_id, aktion)
VALUES (2, 'Deaktiviert');

COMMIT;

Aufgabe 3: Datenwörterbuch-Eintrag abrufen (CRUD - Read)

sql

SELECT schluessel, wert, beschreibung
FROM data_dictionary
WHERE kategorie = 'user_status'
ORDER BY schluessel;

Aufgabe 4: Benutzeraktivitäten protokollieren (CRUD - Create)

sql

START TRANSACTION;

-- Neue Benutzeraktivität protokollieren
INSERT INTO user_logs (user_id, aktion)
VALUES (1, 'Passwort geändert');

COMMIT;

Aufgabe 5: Anzahl der Aktivitäten pro Benutzer zählen (Aggregatfunktion)

sql

SELECT 
    u.benutzername,
    COUNT(l.log_id) AS anzahl_aktivitaeten
FROM users u
LEFT JOIN user_logs l ON u.user_id = l.user_id
GROUP BY u.user_id, u.benutzername
ORDER BY anzahl_aktivitaeten DESC;

Zusammenfassung

In diesem Kapitel hast du gelernt:

✅ Eine Blog-Datenbank von Grund auf zu entwerfen und zu implementieren
✅ Eine Backend-Verwaltungsdatenbank mit CRUD-Operationen zu erstellen
✅ Komplexe Abfragen mit JOIN, GROUP BY und COUNT() zu schreiben
✅ Transaktionen für Datensicherheit zu verwenden

Übungen

Blog: Füge ein Feld bild_url zur Tabelle posts hinzu
Blog: Erstelle eine Abfrage, die die Anzahl der Kommentare pro Artikel zählt
Backend: Füge eine Tabelle roles hinzu und verknüpfe sie mit admins
Backend: Schreibe eine Abfrage, die alle Benutzeraktivitäten der letzten 7 Tage anzeigt

Nächstes Kapitel

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

Kapitel 14: Fortgeschrittene Praxis ⭐⭐ ​

Praxis 4: Persönliche Blog-Datenbank ​

14.1 Anforderungsanalyse ​

14.2 Datenbankdesign ​

14.3 Kern-Implementierung ​

14.4 Abfragen schreiben (DQL) ​

Praxis 5: Einfaches Backend-Verwaltungssystem ​

14.5 Anforderungsanalyse ​

14.6 Datenbankdesign ​

14.7 Kern-Implementierung ​

14.8 Praxis: CRUD und Abfragen ​

Zusammenfassung ​

Übungen ​

Nächstes Kapitel ​