SQL für komplexe Abfragen
SQL (Structured Query Language) ist die Standard-Sprache für die Verwaltung und Manipulation von relationalen Datenbanken. Während grundlegende SQL-Abfragen wie SELECT, INSERT, UPDATE und DELETE für einfache Datenoperationen ausreichen, erfordern komplexere Abfragen fortgeschrittene SQL-Konzepte. Diese ermöglichen es, umfassende Datenanalysen durchzuführen, die über einfache Datenabfragen hinausgehen.
Joins
Definition: Joins ermöglichen das Verbinden von Daten aus zwei oder mehr Tabellen basierend auf einer verwandten Spalte zwischen ihnen.
Arten von Joins:
1. INNER JOIN:
- Beschreibung: Gibt nur die Datensätze zurück, bei denen in beiden Tabellen übereinstimmende Werte gefunden werden.
- Beispiel
SELECT Kunden.Name, Bestellungen.Bestellnummer
FROM Kunden
INNER JOIN Bestellungen ON Kunden.KundenID = Bestellungen.KundenID;
2. LEFT JOIN (oder LEFT OUTER JOIN):
- Beschreibung: Gibt alle Datensätze aus der linken Tabelle zurück und die übereinstimmenden Datensätze aus der rechten Tabelle. Wenn keine Übereinstimmung vorhanden ist, werden NULL-Werte für die rechte Tabelle angezeigt.
- Beispiel:
SELECT Kunden.Name, Bestellungen.Bestellnummer
FROM Kunden
LEFT JOIN Bestellungen ON Kunden.KundenID = Bestellungen.KundenID;
3. RIGHT JOIN (oder RIGHT OUTER JOIN):
- Beschreibung: Gibt alle Datensätze aus der rechten Tabelle zurück und die übereinstimmenden Datensätze aus der linken Tabelle. Wenn keine Übereinstimmung vorhanden ist, werden NULL-Werte für die linke Tabelle angezeigt.
- Beispiel:
SELECT Kunden.Name, Bestellungen.Bestellnummer
FROM Kunden
RIGHT JOIN Bestellungen ON Kunden.KundenID = Bestellungen.KundenID;
4. FULL OUTER JOIN:
- Beschreibung: Gibt alle Datensätze zurück, bei denen es in einer der Tabellen übereinstimmende Werte gibt. Wenn keine Übereinstimmung vorhanden ist, werden NULL-Werte angezeigt.
- Beispiel:
SELECT Kunden.Name, Bestellungen.Bestellnummer
FROM Kunden
FULL OUTER JOIN Bestellungen ON Kunden.KundenID = Bestellungen.KundenID;
Subqueries
Definition: Subqueries sind Abfragen, die innerhalb einer anderen Abfrage verschachtelt sind. Sie können in SELECT-, INSERT-, UPDATE- oder DELETE-Anweisungen verwendet werden.
Beispiel:
SELECT Name
FROM Kunden
WHERE KundenID IN (
SELECT KundenID
FROM Bestellungen
WHERE Bestelldatum > '2023-01-01'
);
Diese Abfrage gibt die Namen der Kunden zurück, die Bestellungen nach dem 1. Januar 2023 aufgegeben haben.
CTEs (Common Table Expressions)
Definition: CTEs bieten eine Möglichkeit, temporäre Ergebnismengen zu definieren, die in einer SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung verwendet werden können. Sie erhöhen die Lesbarkeit und Wartbarkeit von komplexen Abfragen.
Beispiel:
WITH TopVerkaeufer AS (
SELECT VerkaeuferID, SUM(Umsatz) AS Gesamtumsatz
FROM Verkaeufe
GROUP BY VerkaeuferID
HAVING SUM(Umsatz) > 10000
)
SELECT Verkaeufer.Name, TopVerkaeufer.Gesamtumsatz
FROM TopVerkaeufer
JOIN Verkaeufer ON TopVerkaeufer.VerkaeuferID = Verkaeufer.VerkaeuferID;
Diese Abfrage erstellt eine temporäre Ergebnismenge der Verkäufer mit einem Gesamtumsatz von mehr als 10.000 und gibt deren Namen und Umsatz zurück.
Aggregatfunktionen und Gruppierung
Definition: Aggregatfunktionen werden verwendet, um zusammenfassende Informationen aus Datensätzen zu berechnen. GROUP BY ermöglicht die Gruppierung von Datensätzen basierend auf einem oder mehreren Spaltenwerten.
Beispiele:
1. COUNT:
- Beschreibung: Zählt die Anzahl der Datensätze.
- Beispiel:
SELECT COUNT(*) AS AnzahlBestellungen
FROM Bestellungen;
2. SUM:
- Beschreibung: Summiert numerische Werte.
- Beispiel:
SELECT SUM(Umsatz) AS Gesamtumsatz
FROM Verkaeufe;
3. AVG:
- Beschreibung: Berechnet den Durchschnittswert.
- Beispiel:
SELECT AVG(Umsatz) AS Durchschnittsumsatz
FROM Verkaeufe;
4. MIN und MAX:
- Beschreibung: Finden den kleinsten bzw. größten Wert.
- Beispiel:
SELECT AVG(Umsatz) AS Durchschnittsumsatz
FROM Verkaeufe;
GROUP BY:
- Beschreibung: Gruppiert Datensätze nach einem oder mehreren Attributen und ermöglicht die Anwendung von Aggregatfunktionen auf jede Gruppe.
- Beispiel:
SELECT VerkaeuferID, SUM(Umsatz) AS Gesamtumsatz
FROM Verkaeufe
GROUP BY VerkaeuferID;
Fensterfunktionen
Definition: Fensterfunktionen ermöglichen es, über eine Ergebnismenge zu berechnen, ohne diese zu gruppieren, und sind nützlich für laufende Summen, Rangfolgen und gleitende Durchschnitte.
Beispiele:
1. ROW_NUMBER:
- Beschreibung: Weist jeder Zeile innerhalb eines Fensters eine eindeutige Nummer zu.
- Beispiel:
SELECT Name, Umsatz,
ROW_NUMBER() OVER (ORDER BY Umsatz DESC) AS Rang
FROM Verkaeufe;
2. RANK und DENSE_RANK:
- Beschreibung: Weisen jeder Zeile einen Rang zu, wobei DENSE_RANK keine Lücken in den Rangfolgen zulässt.
- Beispiel:
SELECT Name, Umsatz,
RANK() OVER (ORDER BY Umsatz DESC) AS Rang,
DENSE_RANK() OVER (ORDER BY Umsatz DESC) AS DichtRang
FROM Verkaeufe;
3. LEAD und LAG:
- Beschreibung: Zugriff auf die vorhergehende oder nachfolgende Zeile in einem Fenster.
- Beispiel:
SELECT Name, Umsatz,
LAG(Umsatz, 1) OVER (ORDER BY Umsatz) AS VorherigerUmsatz,
LEAD(Umsatz, 1) OVER (ORDER BY Umsatz) AS NaechsterUmsatz
FROM Verkaeufe;
Transaktionen und Isolation
Definition: Transaktionen sind eine Reihe von Datenbankoperationen, die als eine einzige, atomare Einheit behandelt werden. Die Isolation sorgt dafür, dass parallele Transaktionen korrekt und ohne Konflikte ablaufen.
Eigenschaften von Transaktionen (ACID):
1. Atomicity (Atomarität): Eine Transaktion wird vollständig ausgeführt oder gar nicht. 2. Consistency (Konsistenz): Eine Transaktion führt die Datenbank von einem konsistenten Zustand in einen anderen. 3. Isolation (Isolation): Parallel ausgeführte Transaktionen beeinflussen sich nicht gegenseitig. 4. Durability (Dauerhaftigkeit): Nach dem Abschluss einer Transaktion bleiben die Änderungen dauerhaft bestehen.
Beispiel:
BEGIN TRANSACTION;
UPDATE Konten
SET Guthaben = Guthaben - 100
WHERE KontoID = 1;
UPDATE Konten
SET Guthaben = Guthaben + 100
WHERE KontoID = 2;
COMMIT;
Diese Transaktion überweist 100 Einheiten von Konto 1 zu Konto 2. Beide Updates werden als eine einzige atomare Operation behandelt.