📋Spickzettel

Grundgerüst

SELECT spalte, AGG(x) AS alias
FROM tabelle t
  JOIN andere a ON a.id = t.a_id
WHERE bedingung
GROUP BY spalte
HAVING AGG(x) > 0
ORDER BY alias DESC
LIMIT 10 OFFSET 20;

Ausgeführt: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Filter-Operatoren

WHERE preis BETWEEN 10 AND 20
  AND genre IN ('Krimi', 'Roman')
  AND titel LIKE 'D%'      -- % beliebig, _ genau ein Zeichen
  AND autor_id IS NOT NULL

CASE

CASE WHEN preis < 15 THEN 'günstig'
     WHEN preis < 25 THEN 'mittel'
     ELSE 'teuer' END

Mengenoperationen

SELECT stadt FROM kunden
UNION        -- ohne Duplikate (UNION ALL: mit)
SELECT land FROM autoren;
-- INTERSECT: Schnittmenge, EXCEPT: Differenz (Oracle: MINUS)

JOIN-Arten

FROM a INNER JOIN b ON …   -- nur Paare
FROM a LEFT JOIN b ON …    -- alle aus a
FROM a RIGHT JOIN b ON …   -- alle aus b
FROM a FULL JOIN b ON …    -- alle aus beiden
FROM a CROSS JOIN b        -- jede mit jeder

Semi- und Anti-Join

WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)

NOT EXISTS statt NOT IN, wenn NULLs möglich sind

Aggregatfunktionen

COUNT(*)  COUNT(x)  COUNT(DISTINCT x)
SUM(x)  AVG(x)  MIN(x)  MAX(x)

Alle außer COUNT(*) ignorieren NULL

Window Function

RANK() OVER (PARTITION BY genre ORDER BY preis DESC)
SUM(x) OVER (ORDER BY datum
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
LAG(x) OVER (ORDER BY datum)

CTE

WITH t AS (SELECT …),
     u AS (SELECT … FROM t)
SELECT * FROM u;

Rekursive CTE

WITH RECURSIVE r (id, ebene) AS (
  SELECT id, 0 FROM knoten WHERE eltern IS NULL
  UNION ALL
  SELECT k.id, r.ebene + 1
  FROM knoten k JOIN r ON k.eltern = r.id
)
SELECT * FROM r;

INSERT / UPDATE / DELETE

INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');
INSERT INTO t (a, b) SELECT … FROM …;
UPDATE t SET b = 'z' WHERE a = 1;
DELETE FROM t WHERE a = 2;

UPDATE/DELETE immer erst als SELECT mit derselben WHERE-Klausel testen

Transaktion

BEGIN;           -- Standard: START TRANSACTION
  …
SAVEPOINT s1;
  …
ROLLBACK TO s1;
COMMIT;          -- oder ROLLBACK;

CREATE TABLE

CREATE TABLE t (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(100) NOT NULL UNIQUE,
  x_id  INTEGER REFERENCES x (id) ON DELETE CASCADE,
  menge INTEGER DEFAULT 1 CHECK (menge > 0)
);

ALTER / INDEX / VIEW

ALTER TABLE t ADD COLUMN notiz TEXT;
CREATE INDEX idx_t_name ON t (name);
CREATE VIEW v AS SELECT id, name FROM t;
DROP TABLE IF EXISTS alt;