🇩🇪 Datenmanipulation und Abfragen mit SQL

SQL (Structured Query Langauge) ist die die Basis für Datenbank Manipulationen und Abfragen. In diesem Beitrag schauen wir uns die fortgeschrittenen Konzepte der Datenmanipulation und komplexere Abfragen genauer an.

Daten aktualisieren und löschen

Im letzten Beitrag haben wir gelernt wie man Einträge speichert und ausließt. Da sich Daten aber natürlich ändern können, ist die Fähigkeiten diese zu aktualisieren und zu löschen entscheidend bei der Datenbankverwaltung.

UPDATE

Der UPDATE-Befehl ermöglicht es einem die bestehenden Datensätze einer Tabelle zu aktualisieren. Die allgemeine Syntax ist dabei wie folgt:

UPDATE tabelle
SET spalte1 = wert1, spalte2 = wert2, ...
WHERE bedingung;

Beispiel

UPDATE Kunden
SET email = '[e-mail]', telefon = '0123456789'
WHERE kunden_id = 1001;

Hier wird sowohl die E-Mail Adresse als auch die Telefonnummer eines bestehenden Kunden mit der ID 1001 aktualisiert.

Achtung!
Die WHERE-Bedingung ist wichtig, da wir nur gewünschten Datensatz ändern möchten. Ohne WHERE-Bedingung würden wir alle Datensätze der Tabelle aktualisieren!

DELETE

Mit dem DELETE-Befehl können Datensätze aus einer Tabelle gelöscht werden. Die grundlegende Syntax ist dabei wie folgt:

DELETE FROM tabelle
WHERE bedingung;

Beispiel

DELETE FROM Bestellungen
WHERE bestelldatum < '2024-01-01' AND status = 'storniert';

In diesem Beispiel werden alle stornierten Bestellungen entfernt, die vor 2024 erstellt wurden.

Achtung!
Auch hier gilt: Die WHERE-Bedingung ist wichtig, da wir nur gewünschten Datensatz löschen möchten. Ohne WHERE-Bedingung würden wir alle Datensätze der Tabelle löschen!

Sicherheitsaspekte bei Datenmanipulation

Bei der Arbeit mit UPDATE und DELETE gibt es ein paar Dinge die man beachten sollte

1. WHERE-Bedingung: Man sollte immer eine präzise WHERE-Bedingung verwenden, um nicht aus Versehen Datensätze zu ändern oder zu löschen, welche nicht geändert werden sollten.
2. Backups: Es ist immer gut Backups seiner Datenbank zu haben, besonders dann wenn man größere Änderungen vornimmt.
3. Transaktionen: Transaktionen ermöglichen es einem, mehrere Abfragen eine Einheit auszuführen und bei Bedarf rückgängig zu machen - Perfekt für komplexere Operationen.

Beispiel für eine Transaktion:

BEGIN TRANSACTION;

UPDATE Konten SET guthaben = guthaben - 1000 WHERE konto_id = 1;
UPDATE Konten SET guthaben = guthaben + 1000 WHERE konto_id = 2;

COMMIT;

Erweiterte Abfragen

JOIN-Operationen

JOIN-Operationen sind essentiell bei der Verwendung von relationalen Datenbanken, da Sie es einem ermöglichen, Daten aus mehreren Tabellen zu verknüpfen. Die Verknüpfung der Daten geschieht dabei über Primär- und Fremdschlüssel (Primary Key und Foreign Keys). Die gängisten Arten von Join-Typen sind: Inner Join, Left und der Right Join.

Die Grundlegende Syntax ist wie folgt:

SELECT * FROM [Tabelle]
[LEFT|RIGHT|INNER] JOIN [Tabelle2] ON [Bedingung];

Hinweis: JOINs sind, gerade bei Anfängern, oft ein missverstandenes Thema. Zum besseren Verständnis nutzen wir die folgenden Tabellen zur Erklärungen

Tabelle: Bestellungen

id	bestell_nr	kunden_id	produkt_id	gesamtbetrag
1	1000	1	2	500
2	1001	2	1	200
3	1002	3	1	200
4	1003	1	3	70

Tabelle: Produkte

id	produkt_name	preis
1	Uhr	200
2	PlayStation 5	500
3	Final Fantasy	70
4	Skull and Bones	70

Tabelle: Kunden

id	name
1	John Doe
2	Jane Doe
3	Max Mustermann
4	Maria Musterfrau

INNER JOIN

Der INNER JOIN gibt nur die Daten zurück, die in beiden Tabellen übereinstimmende Werte haben, also die Schnittmenge beider Tabellen.

INNER JOIN: Die Schnittmenge aus den verknüpften Tabellen

Angenommen du möchtest alle Bestellungen samt Kundennamen und Produktnamen auflisten, dann kannst du die 3 Tabellen mit INNER JOINs wie folgt verbinden:

SELECT Bestellungen.bestell_nr, Bestellungen.gesamtbetrag, Kunden.name, Produkte.produkt_name
FROM Bestellungen
INNER JOIN Kunden ON Bestellungen.kunden_id = Kunden.id
INNER JOIN Produkte ON Bestellungen.produkt_id = Produkte.id;

Das Ergebnis der obigen Abfrage ist dann:

bestell_nr	gesamtbetrag	name	produkt_name
1000	500	John Doe	PlayStation 5
1001	200	Jane Doe	Uhr
1002	200	Max Mustermann	Uhr
1003	70	John Doe	Final Fantasy

LEFT JOIN und RIGHT JOIN

LEFT/RIGHT JOIN: Die Schnittmenge und die Datensätze aus der linken bzw. rechten Tabelle

Ein LEFT JOIN gibt alle Datensätze der linken (ersten) Tabelle zurück, auch wenn keine Übereinstimmungen mit der rechten Tabellen gefunden werden. Wenn in der Tabelle rechts keine Übereinstimmungen gefunden werden, wird für diese Werte NULL zurückgegeben.

SELECT Kunden.name, Bestellungen.bestell_nr
FROM Kunden
LEFT JOIN Bestellungen ON Kunden.id = Bestellungen.kunden_id;

Diese Abfrage zeigt alle Kunden an, auch solche ohne Bestellungen. Das Ergebnis der obigen Abfrage ist dann:

name	bestell_nr
John Doe	1003
John Doe	1000
Jane Doe	1001
Max Mustermann	1002
Maria Musterfrau	NULL

Ein RIGHT JOIN funktioniert ähnlich wie ein LEFT JOIN, es ist praktisch das Gegenteil von einem LEFT JOIN. Der RIGHT JOIN behält alle Zeilen der rechten Tabelle, auch wenn in der linken Tabelle keine passenden Zeilen gefunden werden. Für nicht gefundene Übereinstimmungen wird auch hier NULL zurückgegeben.

SELECT Bestellungen.bestell_nr, Produkte.produkt_name
FROM Bestellungen
RIGHT JOIN Produkte ON Bestellungen.produkt_id = Produkte.id;

Die Abfrage zeigt alle Produkte an, auch solche die nie bestellt wurden. Das Ergebnis der obigen Abfrage ist dann:

bestell_nr	produkt_name
1002	Uhr
1001	Uhr
1000	PlayStation 5
1003	Final Fantasy
NULL	Skull and Bones

FULL OUTER JOIN

Der FULL OUTER JOIN gibt alle Datensätze aus beiden Tabellen zurück. Auch alle NULL-Werte werden dabei berücksichtigt, wodurch dieser JOIN die größte Zeilenzahl zurückgibt.

FULL OUTER JOIN: Alle Datensätze aus beiden Tabellen

Da der FULL OUTER JOIN nicht Teil von MySQL ist, gehen wir hier nicht weiter darauf ein. Bedenke aber, dass andere relationale Datenbanksysteme durchaus diese Art von JOIN unterstützen können!

Warum sollten JOINs verwendet werden

JOINs sollten aus den folgenden Gründen verwendet werden:

• anstatt mehrerer einzelner Abfragen, welche "per Hand" kombiniert werden müssen, benötigt es nur eine einzelne Abfrage
• bessere Performance, da Datenbanksysteme wie MySQL auf solche Abfragen optimiert sind + die Nutzung von Indizes erhöht die Performance beim Kombinieren der Ergebnisse
• mehrere Abfragen auszuführen, führt zu einem höheren Overhead, da mehr Daten zwischen dem Datenbankserver und der Anwendung (Client) übertragen werden müssen

Aggregate Funktionen

Aggregate Funktionen führen Berechnungen auf einer Menge von Werten durch und geben einen einzelnen Wert zurück. Sie sind besonders nützlich in Verbindung mit GROUP BY.

COUNT

Die COUNT-Funktion zählt die Anzahl der Zeilen oder nicht-NULL-Werte in einer Spalte. Beispiel: Berechnen der Mitarbeiter-Anzahl pro Abteilung (die GROUP BY Anweisung wird hier verwendet um die Ergebnissmenge anhand der Abteilung zu gruppieren).

SELECT abteilung, COUNT(*) AS mitarbeiteranzahl
FROM Mitarbeiter
GROUP BY abteilung;

SUM

Die SUM-Funktion summiert die numerischen Werte einer Spalte. Beispiel: Berechnen wie oft jedes Produkt bisher bestellt wurde.

SELECT produkt_id, SUM(menge) AS gesamtmenge
FROM Bestellungen
GROUP BY produkt_id;

AVG

Die AVG-Funktion ermittelt den Durschsnitt der numerischen Werte einer Spalte. Beispiel: Berechnen des Durschschnittgehalts der Mitarbeiter pro Abteilung, sofern das Durschnittsgehalt über 50.000 liegt.

SELECT abteilung, AVG(gehalt) AS durchschnittsgehalt
FROM Mitarbeiter
GROUP BY abteilung
HAVING AVG(gehalt) > 50000;

In diesem Beispiel benutzen wir die HAVING-Klausel. HAVING wird verwendet, um Bedingungen auf aggregierte Daten anzuwenden, während WHERE für Bedingungen auf einzelne Zeilen verwendet wird.

Unterabfragen

Bei Unterabfragen (Subqueries), auch verschachtelte Abfragen genannt, handelt es sich um SQL Abfragen innerhalb anderer Abfragen. Dies ermöglicht komplexe Datenmanipulationen und vereinfacht Abfragen.

SELECT produktname, preis
FROM produkte
WHERE preis > (SELECT AVG(preis) FROM produkte);

In diesem Beispiel werden alle Produkte selektiert, deren Preis über dem Durchschnittspreis liegt. Die Unterabfrage (SELECT AVG(preis) FROM produkte) wird von SQL zuerst ausgewertet und liefert einen einzelnen Wert, welcher dann in der Hauptabfrage verwendet wird. Diese Art von Subquery nennt man "skalare Subquery", da sie einen einzelnen Wert zurückgibt.

Verschachtelte Abfragen können in verschiedenen Teilen einer SQL-Anweisung verwendet werden:

• In der WHERE-Klausel (wie im obigen Beispiel)
• In der FROM-Klausel als abgeleitete Tabelle
• In der SELECT-Klausel für berechnete Spalten

Beispiel einer Unterabfrage in der FROM-Klausel:

SELECT abteilung, durchschnittsgehalt
FROM (
    SELECT abteilung, AVG(gehalt) AS durchschnittsgehalt
    FROM mitarbeiter
    GROUP BY abteilung
) AS abteilungsdurchschnitte
WHERE durchschnittsgehalt > 50000;

In diesem Beispiel berechnet die Unterabfrage zuerst das Durchschnittsgehalt für jede Abteilung. Sie wird als eigenständige Tabelle behandelt und erhält den Alias abteilungsdurchschnitte. Die Hauptabfrage selektiert dann aus dieser "virtuellen Tabelle" die Abteilungen aus, deren Durchschnittsgehalt über 50.000 liegt. Diese Art der Unterabfrage nennt man auch "abgeleitete Tabelle oder "inline view, da sie wie eine temporäre Tabelle in der FROM-Klausel verwendet wird.

Übungen

• Übung 1:
  • Angenommen, du hast eine Tabelle "produkte" mit den Spalten id, produkt_name und preis. Die Geschäftsleitung hat beschlossen, die Preise für die "PlayStation 5" um 10% zu erhöhen.
  • Aufgabe: Schreibe eine SQL-Anweisung, die diese Preiserhöhung umsetzt
• Übung 2:
  • Angenommen, du hast eine Tabelle "bestellungen" mit den Spalten id, kunden_id, bestelldatum und status. Das Unternehmen möchte alte, stornierte Bestellungen aus der Datenbank entfernen, um Speicherplatz zu sparen.
  • Aufgabe: Schreibe eine SQL-Anweisung, die alle stornierten Bestellungen löscht. die älter als ein Jahr sind.
• Übung 3: Schreibe eine SQL Abfrage, die alle Kunden mit ihren Bestellungen anzeigt, auch wenn sie keine Bestellungen haben. Sortieren Sie das Ergebnis nach dem Kundennamen.
• Übung 4: Berechne den Gesamtumsatz pro Kunde und sortiere das Ergebnis nach dem höchsten Umsatz. Zeige nur Kunden mit einem Gesamtumsatz von mehr als 10.000 an.
• Übung 5: Finde alle Produkte, die teurer als der Durchschnittspreis sind. Zeige die Produktname, den Preis und um wie viel Prozent der Preis über dem Durchschnitt liegt.
• Übung 6:
  • Angenommen es gibt eine Tabelle mitarbeiter mit den Spalten abteilung, name und gehalt.
  • Aufgabe: Erstelle eine Abfrage, die für jede Abteilung den Mitarbeiter mit dem höchsten Gehalt anzeigt.
• Übung 7:
  • Angenommen, du hast eine Tabelle "bestellungen" mit den Spalten id, produkt_id, bestelldatum, und eine Tabelle "produkte" mit den Spalten id, produkt_name und preis.
  • Schreibe eine Abfrage, die alle Produkte auflistet, die in den letzten 30 Tagen nicht bestellt wurden.

Diese Übungen decken die wichtigsten Konzepte ab, die wir in diesem Tutorial behandelt haben. Versuche sie selbstständig zu lösen, bevor du nach Lösungen suchst. Die praktische Anwendung ist der beste Weg, um SQL zu lernen und zu beherrschen.

Die Lösungen zu den Übungen findest du in unserem GitHub Repository.

Fazit

SQL bietet eine leistungsstarke Möglichkeit zur Datenmanipulation und -abfrage. Durch die Verwendung von UPDATE, DELETE, JOINs, Unterabfragen und Aggregatfunktionen können komplexe Datenbankoperationen effizient durchgeführt werden.

Um das hier gelernte zu festigen und die eigenen SQL-Fähigkeiten zu verbessern, hilft nur kontinuierliches Lernen und Üben. Die hier vorgestellten Übungen sind ein guter Anfang, du solltest aber auch weitere Online-Ressourcen, Fachbücher und praktische Projekte nutzen, um dein Wissen zu erweitern und auf dem neuesten Stand zu bleiben. Eine kleine Auswahl findest du im folgenden Punkt "Referenzen".

Referenzen

• W3Schools SQL Tutorial
• PostgreSQL Dokumentation
• Markus Winand (2012), "SQL Performance Explained: Everything developers need to know about SQL performance" *
• Anthony Molinaro und Robert de Graaf (2020), "SQL Cookbook: Query Solutions and Techniques for All SQL Users" *
• Alan Beaulieu (2020), "Learning SQL: Generate, Manipulate, and Retrieve Data" *

* Affiliatelinks/Werbelinks: Wenn du auf einen Affiliate-Link klickst und über diesen Link einkaufst, bekomme ich von dem betreffenden Online-Shop oder Anbieter eine Provision. Du unterstützt den Blog und für dich ändert sich nichts am Preis.