SQL: Spickzettel

Die Sprache für Datenbanken

Ausführen: Ausprobieren ohne Installation in einem SQL-Playground im Browser, lokal mit SQLite (sqlite3 test.db) oder PostgreSQL (psql). Die Beispiele dieses Kurses laufen in SQLite und fast alle auch in PostgreSQL und MySQL.

← SQL

1. Einführung in SQL und Datenbanken

SQL beschreibt was du willst; die Datenbank findet den Weg
Daten liegen in Tabellen aus Spalten und Zeilen
Befehle gliedern sich in DDL, DML, DQL, TCL, DCL
Schlüsselwörter sind nicht case-sensitiv, Anweisungen enden mit ;
Der Kern ist in allen Systemen gleich, Details unterscheiden sich

SELECT 'Hallo SQL' AS gruss, 6 * 7 AS antwort;

2. Tabellen und Datentypen

CREATE TABLE legt Struktur an, ALTER TABLE ändert sie, DROP TABLE löscht sie
Wichtige Typen: Ganzzahl, Dezimal, Text, Datum, Wahrheitswert (Namen variieren)
Constraints (NOT NULL, UNIQUE, CHECK, DEFAULT) sichern Datenqualität
Geld nie als Gleitkommazahl speichern
Jede Tabelle braucht einen Primärschlüssel

CREATE TABLE produkte (
    id      INTEGER PRIMARY KEY,
    name    TEXT    NOT NULL,
    preis   REAL    NOT NULL CHECK (preis >= 0),
    lager   INTEGER DEFAULT 0,
    aktiv   INTEGER DEFAULT 1
);

INSERT INTO produkte (name, preis) VALUES ('Stift', 1.5);
SELECT * FROM produkte;

3. Daten einfügen, ändern und löschen

INSERT INTO t (spalten) VALUES (...) mit expliziten Spalten
UPDATE ... SET ... WHERE und DELETE ... WHERE: immer an WHERE denken
Upsert: ON CONFLICT DO UPDATE (MySQL: ON DUPLICATE KEY UPDATE)
RETURNING liefert betroffene Zeilen zurück
Mehrere Zeilen mit einem INSERT sind schneller als viele einzelne

CREATE TABLE nutzer (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER DEFAULT 0);

INSERT INTO nutzer (name, ort) VALUES ('Mia', 'Berlin');
INSERT INTO nutzer (name, ort, punkte) VALUES ('Tom', 'Hamburg', 50), ('Zoe', NULL, 80);
INSERT INTO nutzer (id, name) VALUES (10, 'Ben');

SELECT * FROM nutzer;

4. SELECT und WHERE

SELECT spalten FROM tabelle WHERE bedingung ORDER BY ... LIMIT ...
Filtern mit =, <>, BETWEEN, IN, LIKE, AND/OR/NOT
Fehlende Werte nur mit IS NULL / IS NOT NULL prüfen
Ohne ORDER BY keine feste Reihenfolge
Klammere gemischte AND/OR-Bedingungen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT * FROM kunden;
SELECT name, ort FROM kunden;
SELECT name AS kunde, punkte * 2 AS doppelt FROM kunden;

5. Funktionen für Text, Zahlen, Datum und NULL

Text: upper, lower, length, substr, replace, trim, ||
Zahlen: abs, round, %, Ganzzahldivision beachten
CASE WHEN ... THEN ... ELSE ... END ist das "if" von SQL
COALESCE ersetzt NULL, NULLIF erzeugt NULL
Datumsfunktionen unterscheiden sich je nach System am stärksten

SELECT upper('Hallo') AS gross, lower('Hallo') AS klein, length('Hallo') AS laenge,
       substr('Datenbank', 1, 5) AS teil, replace('a-b-c', '-', '+') AS ersetzt,
       trim('  x  ') AS getrimmt, 'Mia' || ' ' || 'Müller' AS verkettet,
       instr('Datenbank', 'ban') AS position;

6. Aggregation und GROUP BY

Aggregate: count, sum, avg, min, max, group_concat
GROUP BY bildet Gruppen, jede Spalte im SELECT gehört in GROUP BY oder eine Aggregatfunktion
WHERE filtert Zeilen, HAVING filtert Gruppen
count(*) zählt Zeilen, count(spalte) nur Nicht-NULL
CASE innerhalb von sum ergibt bedingte Zählungen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT count(*) AS anzahl, count(punkte) AS mit_punkten, sum(punkte) AS summe,
       avg(punkte) AS durchschnitt, min(punkte) AS minimum, max(punkte) AS maximum
FROM kunden;

7. Beziehungen und Fremdschlüssel

1:n über einen Fremdschlüssel, n:m über eine Verbindungstabelle
Fremdschlüssel sichern Datenintegrität (SQLite: PRAGMA foreign_keys = ON)
ON DELETE CASCADE / SET NULL / RESTRICT steuert das Löschverhalten
Jede Tabelle hat einen Primärschlüssel

PRAGMA foreign_keys = ON;
CREATE TABLE autoren (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE buecher (
    id INTEGER PRIMARY KEY,
    titel TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autoren(id)
);
INSERT INTO autoren VALUES (1, 'Kafka'), (2, 'Mann');
INSERT INTO buecher VALUES (1, 'Der Prozess', 1), (2, 'Das Schloss', 1), (3, 'Buddenbrooks', 2);
SELECT * FROM buecher;

8. JOINs

INNER JOIN: nur Treffer; LEFT JOIN: alle links; FULL/CROSS für Sonderfälle
Bedingung mit ON, Aliase für Lesbarkeit
LEFT JOIN + WHERE rechts.id IS NULL findet fehlende Gegenstücke
Bei Zählungen über LEFT JOIN count(spalte_rechts) verwenden
Eine Tabelle kann mit sich selbst verbunden werden

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT k.name, b.produkt, b.preis
FROM kunden AS k
INNER JOIN bestellungen AS b ON b.kunde_id = k.id
ORDER BY k.name, b.id;

9. Unterabfragen, CTEs und Mengenoperationen

Unterabfragen stehen in WHERE, FROM oder SELECT
EXISTS/NOT EXISTS ist robuster als IN/NOT IN (NULL!)
WITH (CTE) macht komplexe Abfragen lesbar, WITH RECURSIVE für Hierarchien
UNION, INTERSECT, EXCEPT kombinieren Ergebnismengen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
-- Kunden mit überdurchschnittlichen Punkten
SELECT name, punkte FROM kunden
WHERE punkte > (SELECT avg(punkte) FROM kunden);

-- Kunden, die mindestens eine Bestellung haben
SELECT name FROM kunden WHERE id IN (SELECT kunde_id FROM bestellungen);

-- Kunden ohne Bestellung
SELECT name FROM kunden k
WHERE NOT EXISTS (SELECT 1 FROM bestellungen b WHERE b.kunde_id = k.id);

10. Fensterfunktionen

Fensterfunktionen berechnen über Gruppen, ohne Zeilen zusammenzufassen
PARTITION BY teilt in Gruppen, ORDER BY bestimmt die Reihenfolge im Fenster
ROW_NUMBER, RANK, DENSE_RANK für Ranglisten
LAG/LEAD für Vergleiche mit Nachbarzeilen, sum() OVER (ORDER BY ...) für laufende Summen
Ergebnisse von Fensterfunktionen filterst du über eine CTE oder Unterabfrage

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT name, ort, punkte,
       sum(punkte) OVER (PARTITION BY ort) AS summe_ort,
       round(avg(punkte) OVER (), 1) AS schnitt_gesamt
FROM kunden
WHERE punkte IS NOT NULL
ORDER BY ort, name;

11. Indizes und Performance

Indizes beschleunigen Lesen, bremsen Schreiben und kosten Speicher
Indiziere Spalten aus WHERE, JOIN und ORDER BY
EXPLAIN zeigt, ob der Index wirklich genutzt wird
Funktionen auf indizierten Spalten verhindern oft die Indexnutzung
Zusammengesetzte Indizes beachten die Reihenfolge der Spalten

CREATE TABLE messungen (id INTEGER PRIMARY KEY, sensor INTEGER, wert REAL);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000)
INSERT INTO messungen (sensor, wert) SELECT i % 50, i * 1.5 FROM n;

CREATE INDEX idx_messungen_sensor ON messungen (sensor);
CREATE UNIQUE INDEX idx_eindeutig ON messungen (id, sensor);

SELECT count(*) AS treffer FROM messungen WHERE sensor = 7;
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' ORDER BY name;

12. Transaktionen, Views und Trigger

Transaktionen: BEGIN ... COMMIT oder ROLLBACK; sie sichern ACID-Eigenschaften
Views sind gespeicherte Abfragen; Trigger reagieren automatisch auf Änderungen
Berechtigungen mit GRANT/REVOKE, Prinzip der geringsten Rechte
Gegen SQL-Injection helfen parametrisierte Abfragen, nie String-Verkettung

CREATE TABLE konto (name TEXT PRIMARY KEY, stand INTEGER CHECK (stand >= 0));
INSERT INTO konto VALUES ('Mia', 100), ('Tom', 50);

BEGIN;
UPDATE konto SET stand = stand - 30 WHERE name = 'Mia';
UPDATE konto SET stand = stand + 30 WHERE name = 'Tom';
COMMIT;
SELECT * FROM konto;

BEGIN;
UPDATE konto SET stand = 0 WHERE name = 'Tom';
SELECT * FROM konto;
ROLLBACK;
SELECT * FROM konto;

13. JSON, Textsuche und moderne SQL-Funktionen

JSON lässt sich mit json_extract, ->/->> und json_each abfragen
FTS5 (SQLite) bzw. tsvector (PostgreSQL) ermöglichen Volltextsuche
Generierte Spalten berechnen Werte automatisch
Funktionsnamen unterscheiden sich je System; die Konzepte bleiben gleich

CREATE TABLE events (id INTEGER PRIMARY KEY, daten TEXT);
INSERT INTO events (daten) VALUES
 ('{"typ":"klick","nutzer":{"name":"Mia","alter":17},"tags":["a","b"]}'),
 ('{"typ":"kauf","nutzer":{"name":"Tom","alter":25},"tags":["b","c","d"],"betrag":19.9}');

SELECT id,
       json_extract(daten, '$.typ') AS typ,
       daten ->> '$.nutzer.name' AS nutzer,
       json_array_length(daten, '$.tags') AS anzahl_tags,
       daten ->> '$.betrag' AS betrag
FROM events;

14. Datenbankentwurf und Normalisierung

Entitäten, Attribute, Beziehungen: erst planen, dann Tabellen bauen
1NF bis 3NF vermeiden Redundanz und Anomalien
Verbindungstabellen lösen n:m-Beziehungen
Gezielte Denormalisierung ist erlaubt, wenn begründet
Konsistente Namensgebung erleichtert die Arbeit im Team

PRAGMA foreign_keys = ON;
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT);
CREATE TABLE produkte (id INTEGER PRIMARY KEY, name TEXT NOT NULL, preis REAL NOT NULL);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER NOT NULL REFERENCES kunden(id), datum TEXT);
CREATE TABLE positionen (
    bestellung_id INTEGER REFERENCES bestellungen(id),
    produkt_id    INTEGER REFERENCES produkte(id),
    menge INTEGER NOT NULL CHECK (menge > 0),
    preis_bei_kauf REAL NOT NULL,
    PRIMARY KEY (bestellung_id, produkt_id)
);

INSERT INTO kunden VALUES (1, 'Mia', 'Berlin');
INSERT INTO produkte VALUES (1, 'Maus', 25.5), (2, 'Laptop', 999);
INSERT INTO bestellungen VALUES (1, 1, '2025-01-10');
INSERT INTO positionen VALUES (1, 1, 2, 25.5), (1, 2, 1, 999);

SELECT k.name, p.name AS produkt, po.menge, po.menge * po.preis_bei_kauf AS summe
FROM positionen po
JOIN bestellungen b ON b.id = po.bestellung_id
JOIN kunden k ON k.id = b.kunde_id
JOIN produkte p ON p.id = po.produkt_id;

15. Referenz und Spickzettel

Die Reihenfolge der Ausführung ist FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
Mit NULL vorsichtig sein: IS NULL, COALESCE, EXISTS
Joins verbinden Tabellen, CTEs strukturieren Abfragen
Indizes, Transaktionen und Normalisierung machen Datenbanken schnell und sicher

SELECT [DISTINCT] spalten / ausdruecke
FROM tabelle [alias]
[JOIN andere ON bedingung]
[WHERE zeilenfilter]
[GROUP BY spalten]
[HAVING gruppenfilter]
[ORDER BY spalte [ASC|DESC]]
[LIMIT n [OFFSET m]];

← SQL

SQL: Spickzettel

Die Sprache für Datenbanken

← SQL

1. Einführung in SQL und Datenbanken

SQL beschreibt was du willst; die Datenbank findet den Weg
Daten liegen in Tabellen aus Spalten und Zeilen
Befehle gliedern sich in DDL, DML, DQL, TCL, DCL
Schlüsselwörter sind nicht case-sensitiv, Anweisungen enden mit ;
Der Kern ist in allen Systemen gleich, Details unterscheiden sich

SELECT 'Hallo SQL' AS gruss, 6 * 7 AS antwort;

2. Tabellen und Datentypen

CREATE TABLE legt Struktur an, ALTER TABLE ändert sie, DROP TABLE löscht sie
Wichtige Typen: Ganzzahl, Dezimal, Text, Datum, Wahrheitswert (Namen variieren)
Constraints (NOT NULL, UNIQUE, CHECK, DEFAULT) sichern Datenqualität
Geld nie als Gleitkommazahl speichern
Jede Tabelle braucht einen Primärschlüssel

CREATE TABLE produkte (
    id      INTEGER PRIMARY KEY,
    name    TEXT    NOT NULL,
    preis   REAL    NOT NULL CHECK (preis >= 0),
    lager   INTEGER DEFAULT 0,
    aktiv   INTEGER DEFAULT 1
);

INSERT INTO produkte (name, preis) VALUES ('Stift', 1.5);
SELECT * FROM produkte;

3. Daten einfügen, ändern und löschen

INSERT INTO t (spalten) VALUES (...) mit expliziten Spalten
UPDATE ... SET ... WHERE und DELETE ... WHERE: immer an WHERE denken
Upsert: ON CONFLICT DO UPDATE (MySQL: ON DUPLICATE KEY UPDATE)
RETURNING liefert betroffene Zeilen zurück
Mehrere Zeilen mit einem INSERT sind schneller als viele einzelne

CREATE TABLE nutzer (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER DEFAULT 0);

INSERT INTO nutzer (name, ort) VALUES ('Mia', 'Berlin');
INSERT INTO nutzer (name, ort, punkte) VALUES ('Tom', 'Hamburg', 50), ('Zoe', NULL, 80);
INSERT INTO nutzer (id, name) VALUES (10, 'Ben');

SELECT * FROM nutzer;

4. SELECT und WHERE

SELECT spalten FROM tabelle WHERE bedingung ORDER BY ... LIMIT ...
Filtern mit =, <>, BETWEEN, IN, LIKE, AND/OR/NOT
Fehlende Werte nur mit IS NULL / IS NOT NULL prüfen
Ohne ORDER BY keine feste Reihenfolge
Klammere gemischte AND/OR-Bedingungen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT * FROM kunden;
SELECT name, ort FROM kunden;
SELECT name AS kunde, punkte * 2 AS doppelt FROM kunden;

5. Funktionen für Text, Zahlen, Datum und NULL

Text: upper, lower, length, substr, replace, trim, ||
Zahlen: abs, round, %, Ganzzahldivision beachten
CASE WHEN ... THEN ... ELSE ... END ist das "if" von SQL
COALESCE ersetzt NULL, NULLIF erzeugt NULL
Datumsfunktionen unterscheiden sich je nach System am stärksten

SELECT upper('Hallo') AS gross, lower('Hallo') AS klein, length('Hallo') AS laenge,
       substr('Datenbank', 1, 5) AS teil, replace('a-b-c', '-', '+') AS ersetzt,
       trim('  x  ') AS getrimmt, 'Mia' || ' ' || 'Müller' AS verkettet,
       instr('Datenbank', 'ban') AS position;

6. Aggregation und GROUP BY

Aggregate: count, sum, avg, min, max, group_concat
GROUP BY bildet Gruppen, jede Spalte im SELECT gehört in GROUP BY oder eine Aggregatfunktion
WHERE filtert Zeilen, HAVING filtert Gruppen
count(*) zählt Zeilen, count(spalte) nur Nicht-NULL
CASE innerhalb von sum ergibt bedingte Zählungen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT count(*) AS anzahl, count(punkte) AS mit_punkten, sum(punkte) AS summe,
       avg(punkte) AS durchschnitt, min(punkte) AS minimum, max(punkte) AS maximum
FROM kunden;

7. Beziehungen und Fremdschlüssel

1:n über einen Fremdschlüssel, n:m über eine Verbindungstabelle
Fremdschlüssel sichern Datenintegrität (SQLite: PRAGMA foreign_keys = ON)
ON DELETE CASCADE / SET NULL / RESTRICT steuert das Löschverhalten
Jede Tabelle hat einen Primärschlüssel

PRAGMA foreign_keys = ON;
CREATE TABLE autoren (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE buecher (
    id INTEGER PRIMARY KEY,
    titel TEXT NOT NULL,
    autor_id INTEGER NOT NULL REFERENCES autoren(id)
);
INSERT INTO autoren VALUES (1, 'Kafka'), (2, 'Mann');
INSERT INTO buecher VALUES (1, 'Der Prozess', 1), (2, 'Das Schloss', 1), (3, 'Buddenbrooks', 2);
SELECT * FROM buecher;

8. JOINs

INNER JOIN: nur Treffer; LEFT JOIN: alle links; FULL/CROSS für Sonderfälle
Bedingung mit ON, Aliase für Lesbarkeit
LEFT JOIN + WHERE rechts.id IS NULL findet fehlende Gegenstücke
Bei Zählungen über LEFT JOIN count(spalte_rechts) verwenden
Eine Tabelle kann mit sich selbst verbunden werden

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT k.name, b.produkt, b.preis
FROM kunden AS k
INNER JOIN bestellungen AS b ON b.kunde_id = k.id
ORDER BY k.name, b.id;

9. Unterabfragen, CTEs und Mengenoperationen

Unterabfragen stehen in WHERE, FROM oder SELECT
EXISTS/NOT EXISTS ist robuster als IN/NOT IN (NULL!)
WITH (CTE) macht komplexe Abfragen lesbar, WITH RECURSIVE für Hierarchien
UNION, INTERSECT, EXCEPT kombinieren Ergebnismengen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
-- Kunden mit überdurchschnittlichen Punkten
SELECT name, punkte FROM kunden
WHERE punkte > (SELECT avg(punkte) FROM kunden);

-- Kunden, die mindestens eine Bestellung haben
SELECT name FROM kunden WHERE id IN (SELECT kunde_id FROM bestellungen);

-- Kunden ohne Bestellung
SELECT name FROM kunden k
WHERE NOT EXISTS (SELECT 1 FROM bestellungen b WHERE b.kunde_id = k.id);

10. Fensterfunktionen

Fensterfunktionen berechnen über Gruppen, ohne Zeilen zusammenzufassen
PARTITION BY teilt in Gruppen, ORDER BY bestimmt die Reihenfolge im Fenster
ROW_NUMBER, RANK, DENSE_RANK für Ranglisten
LAG/LEAD für Vergleiche mit Nachbarzeilen, sum() OVER (ORDER BY ...) für laufende Summen
Ergebnisse von Fensterfunktionen filterst du über eine CTE oder Unterabfrage

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT name, ort, punkte,
       sum(punkte) OVER (PARTITION BY ort) AS summe_ort,
       round(avg(punkte) OVER (), 1) AS schnitt_gesamt
FROM kunden
WHERE punkte IS NOT NULL
ORDER BY ort, name;

11. Indizes und Performance

Indizes beschleunigen Lesen, bremsen Schreiben und kosten Speicher
Indiziere Spalten aus WHERE, JOIN und ORDER BY
EXPLAIN zeigt, ob der Index wirklich genutzt wird
Funktionen auf indizierten Spalten verhindern oft die Indexnutzung
Zusammengesetzte Indizes beachten die Reihenfolge der Spalten

CREATE TABLE messungen (id INTEGER PRIMARY KEY, sensor INTEGER, wert REAL);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000)
INSERT INTO messungen (sensor, wert) SELECT i % 50, i * 1.5 FROM n;

CREATE INDEX idx_messungen_sensor ON messungen (sensor);
CREATE UNIQUE INDEX idx_eindeutig ON messungen (id, sensor);

SELECT count(*) AS treffer FROM messungen WHERE sensor = 7;
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' ORDER BY name;

12. Transaktionen, Views und Trigger

Transaktionen: BEGIN ... COMMIT oder ROLLBACK; sie sichern ACID-Eigenschaften
Views sind gespeicherte Abfragen; Trigger reagieren automatisch auf Änderungen
Berechtigungen mit GRANT/REVOKE, Prinzip der geringsten Rechte
Gegen SQL-Injection helfen parametrisierte Abfragen, nie String-Verkettung

CREATE TABLE konto (name TEXT PRIMARY KEY, stand INTEGER CHECK (stand >= 0));
INSERT INTO konto VALUES ('Mia', 100), ('Tom', 50);

BEGIN;
UPDATE konto SET stand = stand - 30 WHERE name = 'Mia';
UPDATE konto SET stand = stand + 30 WHERE name = 'Tom';
COMMIT;
SELECT * FROM konto;

BEGIN;
UPDATE konto SET stand = 0 WHERE name = 'Tom';
SELECT * FROM konto;
ROLLBACK;
SELECT * FROM konto;

13. JSON, Textsuche und moderne SQL-Funktionen

JSON lässt sich mit json_extract, ->/->> und json_each abfragen
FTS5 (SQLite) bzw. tsvector (PostgreSQL) ermöglichen Volltextsuche
Generierte Spalten berechnen Werte automatisch
Funktionsnamen unterscheiden sich je System; die Konzepte bleiben gleich

CREATE TABLE events (id INTEGER PRIMARY KEY, daten TEXT);
INSERT INTO events (daten) VALUES
 ('{"typ":"klick","nutzer":{"name":"Mia","alter":17},"tags":["a","b"]}'),
 ('{"typ":"kauf","nutzer":{"name":"Tom","alter":25},"tags":["b","c","d"],"betrag":19.9}');

SELECT id,
       json_extract(daten, '$.typ') AS typ,
       daten ->> '$.nutzer.name' AS nutzer,
       json_array_length(daten, '$.tags') AS anzahl_tags,
       daten ->> '$.betrag' AS betrag
FROM events;

14. Datenbankentwurf und Normalisierung

Entitäten, Attribute, Beziehungen: erst planen, dann Tabellen bauen
1NF bis 3NF vermeiden Redundanz und Anomalien
Verbindungstabellen lösen n:m-Beziehungen
Gezielte Denormalisierung ist erlaubt, wenn begründet
Konsistente Namensgebung erleichtert die Arbeit im Team

PRAGMA foreign_keys = ON;
CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT);
CREATE TABLE produkte (id INTEGER PRIMARY KEY, name TEXT NOT NULL, preis REAL NOT NULL);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER NOT NULL REFERENCES kunden(id), datum TEXT);
CREATE TABLE positionen (
    bestellung_id INTEGER REFERENCES bestellungen(id),
    produkt_id    INTEGER REFERENCES produkte(id),
    menge INTEGER NOT NULL CHECK (menge > 0),
    preis_bei_kauf REAL NOT NULL,
    PRIMARY KEY (bestellung_id, produkt_id)
);

INSERT INTO kunden VALUES (1, 'Mia', 'Berlin');
INSERT INTO produkte VALUES (1, 'Maus', 25.5), (2, 'Laptop', 999);
INSERT INTO bestellungen VALUES (1, 1, '2025-01-10');
INSERT INTO positionen VALUES (1, 1, 2, 25.5), (1, 2, 1, 999);

SELECT k.name, p.name AS produkt, po.menge, po.menge * po.preis_bei_kauf AS summe
FROM positionen po
JOIN bestellungen b ON b.id = po.bestellung_id
JOIN kunden k ON k.id = b.kunde_id
JOIN produkte p ON p.id = po.produkt_id;

15. Referenz und Spickzettel

Die Reihenfolge der Ausführung ist FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
Mit NULL vorsichtig sein: IS NULL, COALESCE, EXISTS
Joins verbinden Tabellen, CTEs strukturieren Abfragen
Indizes, Transaktionen und Normalisierung machen Datenbanken schnell und sicher

SELECT [DISTINCT] spalten / ausdruecke
FROM tabelle [alias]
[JOIN andere ON bedingung]
[WHERE zeilenfilter]
[GROUP BY spalten]
[HAVING gruppenfilter]
[ORDER BY spalte [ASC|DESC]]
[LIMIT n [OFFSET m]];