Datenbanken abfragen, bearbeiten und warten

Eine Datenbank ist erst nützlich, wenn man die richtigen Antworten aus ihr herausholt. Du schreibst Abfragen, die filtern, sortieren und mehrere Tabellen mit Joins verbinden, und erstellst Auswertungen mit Gruppierungen und Unterabfragen. Du änderst und löschst Daten so, dass nichts Ungewolltes passiert, und nutzt dafür Transaktionen. Zur Wartung gehören Views für wiederkehrende Abfragen, Benutzerrechte, Indizes für Geschwindigkeit sowie Backup und Restore.

Das sind die Fähigkeiten, die am Ende des Moduls sitzen sollten. Jedes Ziel mit einem Beispiel aus dem Lehrbetrieb.

1. 1

   Daten gezielt auswählen, filtern und sortieren

   Kompetenz A

   Du schreibst SELECT-Abfragen, die nur die gewünschten Spalten und Zeilen liefern. Du kombinierst Bedingungen mit AND und OR, suchst mit LIKE und BETWEEN und behandelst NULL-Werte korrekt.

   Situation

   Die Kundendienstleiterin eines Onlineshops für Outdoor-Ausrüstung will alle Bestellungen aus dem Kanton Graubünden im Oktober über CHF 200 sehen, die noch nicht versendet sind.

   Deine Aufgabe

   Schreibe die Abfrage auf die Tabelle bestellung.

   Gutes Ergebnis

   SELECT auf Bestellnummer, Datum, Total und Kunde mit WHERE kanton = 'GR', Datum zwischen 1. und 31. Oktober, total > 200 und versandt_am IS NULL, sortiert nach Datum aufsteigend. Ergebnis: 17 Bestellungen.

   SELECT / FROM / WHEREORDER BYLIKE, IN, BETWEENIS NULLLIMITAlias
2. 2

   Informationen aus mehreren Tabellen mit Joins verbinden

   Kompetenz B

   Du verknüpfst Tabellen über ihre Schlüssel und weisst, wann ein INNER JOIN und wann ein LEFT JOIN nötig ist. Mit einem LEFT JOIN findest du auch Datensätze, zu denen es keine passenden Einträge gibt.

   Situation

   Der Verkaufsleiter eines Weinhandels in Lavaux möchte wissen, welche Stammkunden im laufenden Jahr noch keine einzige Bestellung aufgegeben haben.

   Deine Aufgabe

   Finde diese Kunden mit einer Abfrage auf kunde und bestellung.

   Gutes Ergebnis

   kunde wird per LEFT JOIN mit bestellung verbunden, wobei die Jahresbedingung im ON-Teil steht. WHERE bestellung.id IS NULL filtert auf Kunden ohne Bestellung. 42 Kunden erhalten daraufhin ein Angebot zur Herbstdegustation.

   INNER JOINLEFT JOINJoin-BedingungTabellenaliasMehrfach-JoinBedingung in ON vs. WHERE
3. 3

   Auswertungen mit Gruppierungen und Unterabfragen erstellen

   Kompetenz C

   Du fasst Daten mit COUNT, SUM, AVG, MIN und MAX zusammen, gruppierst nach Merkmalen und filterst Gruppen mit HAVING. Für komplexere Fragen setzt du Unterabfragen ein, z.B. um Werte mit dem Durchschnitt zu vergleichen.

   Situation

   Die Leiterin einer Kette mit 8 Coiffeursalons will für die Quartalssitzung wissen, welche Filialen mehr als CHF 60'000 Umsatz hatten und wie hoch der durchschnittliche Betrag pro Termin war.

   Deine Aufgabe

   Erstelle eine Auswertung pro Filiale für das 3. Quartal.

   Gutes Ergebnis

   Join von termin und filiale, WHERE auf das Quartal, GROUP BY filiale.name mit SUM(betrag) und AVG(betrag), HAVING SUM(betrag) > 60000, sortiert nach Umsatz absteigend. Fünf Filialen erscheinen, Spitzenreiter ist Zug mit CHF 84'310.

   AggregatfunktionenGROUP BYHAVING vs. WHEREUnterabfrageDatumsfunktionen
4. 4

   Daten sicher ändern und löschen

   Kompetenz D

   Du schreibst UPDATE- und DELETE-Befehle mit präziser WHERE-Bedingung und prüfst vorher mit einem SELECT, welche Zeilen betroffen sind. Grössere Änderungen führst du in einer Transaktion aus, damit du sie bei einem Fehler rückgängig machen kannst.

   Situation

   Ein Lieferant eines Detailhändlers erhöht per 1. Januar die Preise aller Artikel der Kategorie 'Reinigungsmittel' um 4 %.

   Deine Aufgabe

   Passe die Preise in der Tabelle artikel an, ohne andere Artikel zu verändern.

   Gutes Ergebnis

   Zuerst ein SELECT mit derselben WHERE-Bedingung: 63 Artikel. Dann START TRANSACTION, UPDATE mit preis = ROUND(preis * 1.04, 2) und derselben Bedingung, erneute Kontrolle von drei Stichproben und COMMIT. Ein Fehler hätte mit ROLLBACK rückgängig gemacht werden können.

   UPDATEDELETETransaktionCOMMIT / ROLLBACKACIDAutocommit
5. 5

   Views und Berechtigungen für verschiedene Benutzergruppen einrichten

   Kompetenz E

   Wiederkehrende oder komplexe Abfragen speicherst du als View. Mit Views und Rechten steuerst du, dass Benutzergruppen nur die Daten sehen, die sie für ihre Arbeit brauchen.

   Situation

   Im Personalsystem eines Spitals braucht die Einsatzplanung Namen, Abteilung und Pensum der Pflegenden, aber keine Lohndaten.

   Deine Aufgabe

   Gib der Einsatzplanung Zugriff auf die nötigen Daten, ohne die Lohntabelle freizugeben.

   Gutes Ergebnis

   Eine View 'v_personal_planung' mit Name, Abteilung, Pensum und Qualifikation, ohne Lohn und AHV-Nummer. Die Rolle 'planung' erhält nur SELECT auf diese View, nicht auf die Basistabellen. Ein Testlogin bestätigt, dass die Lohntabelle nicht lesbar ist.

   CREATE VIEWGRANT / REVOKERollenDatenschutzLeast Privilege
6. 6

   Die Datenbank sichern, wiederherstellen und beschleunigen

   Kompetenz F

   Du erstellst regelmässige Backups, testest die Wiederherstellung und weisst, wie lange sie dauert. Langsame Abfragen analysierst du mit EXPLAIN und beschleunigst sie mit passenden Indizes.

   Situation

   Bei einer Treuhandfirma dauert die Suche nach Buchungen eines Mandanten in einer Tabelle mit 2.4 Millionen Zeilen 9 Sekunden. Ausserdem wurde noch nie geprüft, ob das nächtliche Backup funktioniert.

   Deine Aufgabe

   Beschleunige die Abfrage und beweise, dass das Backup brauchbar ist.

   Gutes Ergebnis

   EXPLAIN zeigt einen Full Table Scan. Ein Index auf (mandant_id, buchungsdatum) senkt die Laufzeit auf 0.05 Sekunden. Der mysqldump der Nacht wird in einen Docker-Testcontainer eingespielt, die Zeilenzahlen stimmen, die Wiederherstellung dauert 6 Minuten. Das Ergebnis steht im Betriebshandbuch.

   Backup / RestoremysqldumpRestore-TestIndexEXPLAINZusammengesetzter Index

Die Kompetenzmatrix zerlegt das Modul in Themenstränge und drei Stufen. Für den Kompetenznachweis solltest du überall mindestens Stufe 2 erreichen. Dein Stand bleibt in diesem Browser gespeichert und färbt Lernweg und Übungen ein.

Luca ist im 1. Lehrjahr als Informatiker Applikationsentwicklung bei der Bachmann Haustechnik AG in Emmen, einem Sanitär-Grosshändler mit 85 Mitarbeitenden. Der Verkauf arbeitet mit einer MariaDB-Datenbank mit rund 4000 Kunden und 60'000 Bestellungen, aber Auswertungen macht bisher nur ein externer Programmierer. Lucas Berufsbildnerin gibt ihm den Auftrag, die wichtigsten Anfragen der Abteilung selbst mit SQL zu lösen und die Datenbank in Schuss zu halten.

1. Kapitel 1

   Die erste Liste für den Aussendienst

   Der Aussendienstleiter will alle Kunden aus den Kantonen Luzern und Zug, die seit Anfang 2024 nichts mehr bestellt haben. Luca arbeitet in DBeaver auf einer Kopie der Datenbank und baut die Abfrage mit WHERE, IN und ORDER BY auf. Zuerst fehlen 140 Kunden, weil bei ihnen das Feld letzte_bestellung leer ist. Mit einer zusätzlichen Bedingung IS NULL erscheinen sie, und die Liste mit 312 Kunden geht als CSV an den Aussendienst.

   Ziel 1

2. Kapitel 2

   Umsatz, der doppelt so hoch ist

   Für die Jahresplanung will die Geschäftsleitung den Umsatz pro Produktgruppe und Region. Luca verbindet Kunden, Bestellungen, Positionen und Artikel mit drei Joins und gruppiert mit GROUP BY. Der Gesamtumsatz ist plötzlich doppelt so hoch wie in der Buchhaltung, weil er die Artikel über die falsche Spalte verknüpft hat. Nach der Korrektur stimmt die Summe auf den Franken mit dem Abschluss von CHF 18.4 Mio. überein, und mit einer Unterabfrage markiert er alle Regionen unter dem Durchschnitt.

   Ziel 2Ziel 3

3. Kapitel 3

   Die Preiserhöhung

   Ein Lieferant erhöht die Preise aller Armaturen um 4 Prozent. Luca prüft mit einem SELECT, wie viele Artikel betroffen sind, und startet dann das UPDATE in einer Transaktion. Die Kontrollabfrage zeigt, dass auch 80 Ersatzteile mit derselben Kategorie-ID mitgekommen sind. Er macht ein ROLLBACK, verfeinert die Bedingung über die Lieferantennummer und bestätigt erst den zweiten Lauf mit COMMIT.

   Ziel 4

4. Kapitel 4

   Jeder sieht nur, was er braucht

   Das Lager soll Bestellungen sehen, aber keine Preise und Kreditlimiten der Kunden. Luca erstellt eine View v_lager_bestellungen ohne die heiklen Spalten und legt einen Benutzer lager_ro an, der nur SELECT auf diese View erhält. Mit einem Testlogin zeigt er seiner Berufsbildnerin, dass ein Zugriff auf die Kundentabelle mit einer Fehlermeldung abgelehnt wird.

   Ziel 5

5. Kapitel 5

   Sechs Sekunden Wartezeit

   Die Kundenmaske im Verkauf braucht 6 Sekunden, weil sie Dutzende Abfragen absetzt, die jedes Mal alle 60'000 Bestellungen lesen. EXPLAIN zeigt den Full Table Scan: Auf kunden_id fehlt der Index, weil die alte Tabelle keinen Fremdschlüssel hat. Luca legt den Index an, und die Maske ist danach praktisch sofort da. Zum Abschluss richtet er ein nächtliches mysqldump ein und spielt die Sicherung einmal in eine leere Testdatenbank zurück, um zu beweisen, dass sie wirklich funktioniert.

   Ziel 6

1. 1

   Einfache Abfragen sicher beherrschen

   ca. 3 Std.

   Du arbeitest mit einer Übungsdatenbank, z.B. Sakila oder einer eigenen Shop-Datenbank, und übst SELECT bis es sitzt.

   • 20 kurze Fragen mit WHERE, ORDER BY und LIKE beantworten
   • Gezielt Abfragen mit NULL-Werten schreiben und Fehler wie '= NULL' verstehen
   • Spalten mit Alias und berechneten Werten ausgeben, z.B. Preis inkl. 8.1 % MWST

   Trainiert Kompetenz A1A2

   Lernziel1

2. 2

   Joins verstehen

   ca. 4 Std.

   Du verbindest Tabellen und lernst den Unterschied zwischen INNER und LEFT JOIN an konkreten Ergebnissen kennen.

   • Dieselbe Frage mit INNER und LEFT JOIN beantworten und die Zeilenzahl vergleichen
   • Abfragen über drei und vier Tabellen schreiben
   • Auf Papier skizzieren, welche Zeilen ein Join erzeugt

   Trainiert Kompetenz B1B2

   Lernziel2

3. 3

   Auswerten und zusammenfassen

   ca. 4 Std.

   Du erstellst Kennzahlen pro Gruppe und lernst, wann du eine Unterabfrage brauchst.

   • Umsatz pro Monat, pro Kategorie und pro Kunde berechnen
   • Mit HAVING nur Gruppen über einem Schwellenwert anzeigen
   • Artikel finden, die teurer sind als der Durchschnitt ihrer Kategorie

   Trainiert Kompetenz C1C2

   Lernziel3

4. 4

   Daten ändern mit Netz und doppeltem Boden

   ca. 3 Std.

   Du übst UPDATE und DELETE in Transaktionen und lernst, Fehler wieder rückgängig zu machen.

   • Autocommit ausschalten und Änderungen mit ROLLBACK zurücknehmen
   • Vor jedem UPDATE und DELETE das passende SELECT schreiben
   • Datenfehler korrigieren, z.B. falsch geschriebene Ortsnamen vereinheitlichen

   Trainiert Kompetenz D1D2

   Lernziel4

5. 5

   Views, Rechte und Wartung

   ca. 4 Std.

   Du richtest Views und Rollen ein, sicherst die Datenbank und testest die Wiederherstellung. Danach optimierst du eine langsame Abfrage.

   • Zwei Views für unterschiedliche Abteilungen erstellen und Rechte vergeben
   • Ein Backup mit mysqldump erstellen und in eine leere Datenbank zurückspielen
   • Mit EXPLAIN eine Abfrage vor und nach einem neuen Index vergleichen

   Trainiert Kompetenz E1E2F1F2

   Lernziel56

6. 6

   Praxisfall wie im ÜK

   ca. 4 Std.

   Du erhältst eine Datenbank und eine Liste mit Anfragen aus einem fiktiven Betrieb und bearbeitest sie in begrenzter Zeit.

   • 15 Fragen von einfach bis anspruchsvoll beantworten und die Abfragen kommentieren
   • Zwei Datenkorrekturen mit Transaktion durchführen und protokollieren

   Trainiert Kompetenz A3B3C3D3

   Lernziel1234

SELECT

SQL-Befehl zum Lesen von Daten aus einer oder mehreren Tabellen.

JOIN

Verknüpfung von Tabellen über gemeinsame Werte, meist Primär- und Fremdschlüssel.

Aggregatfunktion

Funktion, die mehrere Werte zu einem zusammenfasst, z.B. SUM, COUNT oder AVG.

Unterabfrage

SELECT-Abfrage innerhalb einer anderen Abfrage.

Transaktion

Gruppe von Änderungen, die entweder vollständig oder gar nicht ausgeführt wird.

View

Gespeicherte Abfrage, die wie eine virtuelle Tabelle verwendet werden kann.

Index

Zusätzliche Datenstruktur, die das Suchen in einer Tabelle beschleunigt.

Restore

Wiederherstellen einer Datenbank aus einem Backup.

Passende Module