Let the music play: Auf geht’s in die Datenbank – mit SQL direkt arbeiten

Am Anfang unserer Fallstudie „Let the music play“ stand eine wild zusammengewürfelte Tabelle mit Alben, Artists und Tracks. Inhaltliche und logische Zusammenhänge dargestellt (in einem ERM) und die Tabellen selbst auch aufgeräumt (in die Normalformen). Und jetzt wird das praktisch umgesetzt!

SQL – los geht’s

Mit der Structured Query Language (SQL) kann direkt auf eine Datenbank zugegriffen werden:1

  • SQL erlaubt die Definition (CREATE, ALTER, DROP), Manipulation (INSERT, UPDATE) und Abfrage (SELECT) von Daten – und Löschen (DELETE) geht auch
  • SQL ist bewährte Sprache für eine Vielzahl von Datenbanksystemen
  • SQL unterscheidet im Standard nicht zwischen Groß- und Kleinschreibung
  • SQL „hört“ auf bestimmte Befehlsworte

Die Grundzüge und Aufbau der Befehle sind dabei unabhängig von der verwendeten Datenbank gleich. Aber wie bei der gesprochenen Sprache auch gibt es Dialekte: Verschiedene Software und Datenbanken verstehen die Dinge unterschiedlich oder erwarten etwas andere Formate.

Wo ausführen? Und wo sind die vollständigen SQL-Statements?

Einen Vergleich verschiedner Möglichkeiten zum Ausführen der Befehle findet sich hier. Alle Beispiele auf dieser Seite sind getestet mit dem empfohlenen db-fiddle.com unter Verwendung von MySQL.

Im GitHub-Projekt zu dieser Seite finden sich die vollständigen SQL-Dateien zu allen Beispielen. Und noch eine Datei mit zusätzlichen Datensätzen, um mehr und kompliziertere Abfragen auszuprobieren. Gerne können diese Dateien auch weiterverwendet werden, so lange ein Hinweis auf TabellenDoktor.de enthalten bleibt.

Schritt 1: Datenstruktur definieren – CREATE, ALTER, DROP

Mit drei Befehlen kann die Datenstruktur (als Tabellen) in einer (relationalen) Datenbank definiert werden:

  • DROP TABLE entfernt bereits vorhandene Tabellen – dadurch kann wirklich sauber neu gestartet werden (und mit IF EXIST wird vorher geschaut ob das entsprechende Element auch schon existiert, ansonsten werfen die meisten Datenbanken einen Fehler)
  • CREATE TABLE erzeugt einen Tabelle mit den definierten Spalten. Pro Spalte wird der Name und der Datentyp (bspw. INTEGER für ganze Zahlen) angegeben. Über PRIMARY KEY wird dann auch festgelegt welche Spalte(n) den Primärschlüssel bildet. Weitere Möglichkeit ist der Befehl FOREIGN KEY um die Relationen mit Fremdschlüsseln abzubilden (siehe auch ERMs und Normalformen).
  • ALTER TABLE erlaubt das Hinzufügen und Entfernen von Spalten, genauso wie das Verändern der Datentypen (solange die Inhalte noch passen, sonst stellt sich die Datenbank quer)

Hier ein Beispiel um die Tabelle Artist zu erst zu entfernen (falls schon existent) und dann sauber neu anzulegen. Danach wird noch eine zusätzliche Spalte nachträglich hinzugefügt und auch wieder entfernt:

-- remove table if already existing
DROP TABLE IF EXISTS Artist;

-- create the table Artist
CREATE TABLE Artist (
    Artist_ID INTEGER, 
    Artist VARCHAR(30) NOT NULL, 
    Artist_YOB INTEGER(4),
    PRIMARY KEY (Artist_ID)
);

-- add an additional column
ALTER TABLE Artist ADD placeOfBirth VARCHAR(40);
-- delete an column again
ALTER TABLE Artist DROP placeOfBirth;

Vollständige Fassung mit allen Tabellen: SQL-NormalForm-example/1_create_alter_drop_tables.sql auf GitHub

Schritt 2: Daten einfügen – INSERT (und UPDATE)

Jetzt es die Daten in die Datenstrukturen bzw. Tabellen zu bringen und auch wieder zu ändern:

  • INSERT INTO fügt Daten in eine Tabelle ein.
    • Hierzu wird zuerst angegeben wohin die Daten kommen sollen, also ein Tabellennamen muss angegeben werden.
    • Dahinter sollten auch die Felder bzw. Spalten (innerhalb von runden Klammern durch Kommata getrennt) in der Reihenfolge aufgelistet werden in die die Daten reinsollen. Wenn keine Felder aufgelistet werden gilt die Reihenfolge wie in der Anlage der Tabelle (siehe Schritt 1). Für die Lesbarkeit und für die Fehlersuche ist es praktischer hier immer die Felder sauber und ordentlich anzugeben.
    • Nach dem Schlüsselwort VALUES folgen dann die Daten bzw. Werte als Tupel. Tupel? Immer die Werte für eine „Zeile“ innerhalb von runden Klammern durch Kommata getrennt. Wenn mehrere Tupelo eingefügt werden sollen (siehe auch die Statements auf GitHub) wird zwischen die Tupel auch ein Komma gesetzt. Alternativ könnte anstelle von manuell angegebenen Tupel auch ein SELECT-Befehl die gewünschten Daten liefern.
  • UPDATE aktualisiert bzw. ändert Daten:
    • In einer bestimmten Tabelle als erster Parameter
    • Nach dem Wort SET kommen dann Zuweisungen von Werten. Also in welcher Spalte bzw. welchem Feld soll was stehen
    • Über eine WHERE-Angabe kann das Update auch auf einzelne Zeilen/Datensätze beschränkt werden – ansonsten erfolgt die Zuweisung für die ganze Tabelle. WHERE wird im Schritt 3 beim SELECT auch noch einmal genauer erklärt
INSERT INTO Artist (Artist_ID, Artist, Artist_YOB) VALUES (1, 'P!nk', 1979);

-- Beispiel mit anderer Tabelle (siehe vollständiges Beispiel)
UPDATE Tracks SET Track_name = 'Future Nostalgia (Joe Goddard Remix/Mixed)' WHERE Album_ID = 8982 AND Track = 1;

Die vollständige Fassung mit allen Datensätzen aus dem Normalisierungsbeispiel ist hier: SQL-NormalForm-example/2_insert_update.sql auf GitHub

Schritt 3: Daten abfrage – SELECT

Der SELECT-Befehl ist der wichtigste SQL-Befehl. Warum? Mit diesem können Daten aus der Datenbank ausgelesen werden. Und nicht nur einfach gelesen, sondern auch gefiltert, gezählt, zusammengefasst, berechnet, zusammengeführt, verglichen und und und. Und das schöne an SELECT: Daten können damit nicht verändert werden. Daher geht auch nichts kaputt.
Spätestens bei den komplexeren Dingen gilt: vorher nachdenken, testen und auch einmal nachrechnen anstatt blindes Verlassen auf die Ergebnisse. (Ja ist sogar dem TabellenDoktor schon passiert mal etwas falsches ausgerechnet zu haben… Statement funktionierte 1a – nur der Inhalt nicht so ganz…). Alle Statements aus diesem Block finden sich ebenfalls wieder (zusammen mit weiteren Beispielen) auf Github: SQL-NormalForm-example/3_select.sql auf GitHub

Einfach alles lesen

Fangen wir einfach an: Nach dem SELECT ein * und anschließend FROM und den Tabellennamen. Und schon kommen alle Spalten und alle Datensätze aus der jeweiligen Tabelle. Oder nur die Spaltenüberschriften, wenn noch keine Datensätze in der Tabelle sind. Das sieht dann für die Tabelle Artist unserer Fallstudie so aus:

-- just select all artists (with all columns)
SELECT *
FROM Artist;

Filtern und Berechnen

Das ist auf Dauer natürlich etwas langweilig einfach immer alle Daten einfach so zu lesen. Also fangen wir mit Filtern und Berechnen von Werten an:

  • Berechnungen können mit allen Grundrechenarten (und je nach Datenbank noch viel mehr Funktionen von Quadratwurzeln bis hin zu geographischen Berechnungen) durchgeführt werden. Und das Ergebnis kann dann sogar einen neuen Spaltennamen mittels AS erhalten
  • Fürs Filtern gibt es WHERE gefolgt von einem (oder mehreren auch noch mit AND und OR verschachtelteren) Bedingungen. Auch hier sind Berechnungen und Vergleiche möglich. Während numerische Vergleiche immer recht leicht geschrieben sind (größer, kleiner, gleich, ungleich, usw.), sind Vergleiche auf Text bzw. Springs (siehe den Typ VARCHAR oben beim CREATE) immer etwas „frickeliger“. Hier kommt dann statt einem Gleichheitszeichen der Befehl LIKE zum Einsatz. Dann in Anführungszeichen der Suchbegriff. Mit Hilfe des Prozentzeichens % vor und nach dem Begriff darf der dann auch irgendwo beim Gesuchten stehen.

Praktisch sieht das dann in zwei Beispielen so aus:

-- select artists with a calculated age >= 30
SELECT Artist, 2020 - Artist_YOB AS age 
FROM Artist
WHERE 2020 - Artist_YOB >= 30;

-- select from tracks those with a name containing "physical" at any position
SELECT * FROM Tracks WHERE Track_name LIKE '%Physical%'; 

Im ersten Fall berechnet die Differenz zwischen der Zahl 2020 und dem Wert für Artist_YOB für jeden Datensatz und gibt das in der Spalte age aus. Im WHERE-Statement kommt die gleiche Berechnung. Nur wird dann verglichen ob das Ergebnis größer oder gleich 30 ist. Wenn ja, bleibt der jeweilige Datensatz drin im Ergebnis. Wenn nein wird der nicht ausgegeben als Ergebnis der Anfrage.
Das zweite Statement sucht einfach welche Tracks einen Namen haben bei dem „Physical“ an einer beliebigen Stelle steht und gibt genau die dann aus.

Daten aus mehreren Tabellen zusammenbringen

Bei der Normalisierung wurden die Tabellen sauber aufgeteilt. Bei der Abfrage ist manchmal das Gegenteil gefragt: Daten aus mehreren Tabellen wieder richtig zusammenhängend anzeigen.

Hierzu gibt es JOIN als Befehl in SQL. Das steht zwischen zwei Tabellen und danach folgt mit ON die Definition welche Spalten die Beziehung (siehe das ERM) herstellen. Und das kann auch mehrfach gemacht werden. Wie hier im Beispiel:

-- JOIN tracks with albums with artist
SELECT *
FROM Tracks INNER JOIN Albums ON Tracks.Album_ID = Albums.Album_ID
	INNER JOIN Artist ON Albums.Artist_ID = Artist.Artist_ID;

Das Ergebnis hier ist eine große Tabelle in der alle Spalten aus Tracks, Albums und Artist ausgegeben werden. Und in jeder Zeile steht jeweils alles was zusammengehört. Damit wäre die erste Normalform wieder ausgegeben.

Aber warum steht da INNER JOIN und nicht einfach JOIN? Es gibt mehrere Typen von JOIN und nicht jede Datenbank kann alle verstehen. Alle können aber INNER JOIN. Diese Arten gibt es:

  • INNER JOIN: Alle Datensätze bei denen die Bedingung(en) im ON zutreffen werden ausgegeben – aber auch nur die
  • LEFT JOIN: Alle Datensätze der Tabelle links vom JOIN werden ausgegeben. Wenn ein Datensatz aus der Tabelle rechts passt (gemäß ON), werden die Daten entsprechend ausgegeben. Wenn kein Datensatz von rechts passt, dann stehen dort nur leere Werte (technisch in der Regel NULL – die leere Menge, keine Zahl).
  • RIGHT JOIN: wie LEFT JOIN, nur die Tabelle auf er rechten Seite wird immer vollständig ausgegeben
  • FULL OUTER JOIN: Gib so viel wie möglich als Ergebnis. Alle Datensätze links und rechts kommen im Ergebnis vor. Wo über ON ein Zusammenhang hergestellt wird, stehen die Daten entsprechend drin. Ansonsten stehen auf einer Seite leere Werte. Der FULL OUTER JOIN ist damit eigentlich die Kombination des Ergebnisses von LEFT JOIN und RIGHT JOIN.

Also praktisch heißt das: ausprobieren was die jeweilige Datenbank kann. Und dann auch mal damit spielen.

Aggregate bilden – GROUP BY

Neben dem Abfrage der Daten selbst, gibt uns SQL die Möglichkeit direkt Zusammenfassungen zu bilden. Typische Aggregate sind die Anzahl, das Maximum, das Minimum, der Durchschnitt, und so weiter und so fort.

Für jedes Aggregat muss die Datenbank wissen worauf zusammengefasst werden soll. Hierzu gibt es den GROUP BY-Befehl gefolgt vom Namen einer oder mehrer Spalten. Damit wird der Datenbank gesagt: „Fasse alle Zeilen mit dem gleichen Wert in diesen Spalten zusammen im Ergebnis“. Damit dann etwas sinnvolles als Ergebnis kommt, müssen Aggregatfunktionen auf weitere Spalten angewendet werden.

Hier ein praktisches Beispiel (auf Basis des Befehls oben zum JOIN):

-- Get number of tracks and albums per artist (albums need to be distinct in the count)
SELECT Artist.Artist, COUNT(Tracks.Track) AS numberOfTracks, COUNT(DISTINCT Albums.Album_ID) AS numberOfAlbums
FROM Tracks INNER JOIN Albums ON Tracks.Album_ID = Albums.Album_ID
	INNER JOIN Artist ON Albums.Artist_ID = Artist.Artist_ID
GROUP BY Artist.Artist;

Als Ergebnis bekommen wir jetzt eine wunderbare Auflistung von Artists mit der jeweiligen Anzahl von Tracks pro Artist (zähle wie viele Zeilen sind da in denen der gleiche Artist steht) und wie viele Alben (zähle nur wie viele verschiedene (DISTINCT) Alben stehen pro Artist). Warum das DISTINCT? Weil in jeder Zeile steht auch ein Album. Ohne das DISTINCT würde einfach die gleiche Zahl bei beiden rauskommen, was von der Datenbank richtig gezählt wäre. Aber nicht das gewünschte Ergebnis. Daher nachrechnen und immer schauen was eigentlich das Ziel der Abfrage war.

Weitere Fragestellungen

Daneben ergeben sich noch viele weitere Fragestellungen die mit Hilfe von SELECT beantwortet werden können:

  • Wie können Ergebnisse von zwei Abfragen zusammengeführt werden? (UNION)
  • Wie viele Tracks sind im Durchschnitt auf einem beliebigen Album? (AVG)
  • Wie viele Tracks hat ein Artist im Durchschnitt pro Album veröffentlicht?
  • Gibt es Artists mit mehr als zwei Alben?

Mögliche Lösungen finden sich hier: SQL-NormalForm-example/3_select.sql auf GitHub Dabei ist zu empfehlen auch die weiteren Daten aus Schritt 4 zu verwenden, dann ist mehr „Material“ zum ausprobieren dabei.

Schritt 4: Mehr Daten verwenden – weitere INSERTs

Im Repository auf GitHub finden sich noch weitere Datensätze, um mehr Material zum Ausprobieren und Üben zu haben: SQL-NormalForm-example/blob/master/4_additional_inserts.sql

Dabei werden 4 weitere Alben und insgesamt rund 100 Tracks hinzugefügt, so dass weitere spannende Auswertungen mit SELECT und den anderen Befehlen möglich sind.

Schritt 5: Auch mal was löschen – DELETE

Nach all dem Anlegen von Datenstrukturen, dem Einfügen und Abfragen von Daten, muss manchmal auch ein Datensatz/Tupel eine Tabelle verlassen. Hierfür gibt es den DELETE-Befehl in SQL. Der Aufbau ist einfach:

  • DELETE FROM Tabellenname
  • und dann kommt eine Einschränkung auf was gelöscht werden soll über das bekannte WHERE

Empfehlung: Vor dem Ausführen einmal mit SELECT statt DELETE ausprobieren ob auch die richtigen Datensätze „erwischt“ werden.

Zu dem muss beim Löschen geschaut werden, dass die Verknüpfungen/Relationen (siehe auch das ERM) zwischen Tabellen berücksichtigt werden: In unserer Fallstudie müssten, um einen Eintrag aus der Tabelle Artist zu entfernen, zuerst die Tracks der jeweiligen Alben, dann die Alben des Artist und erst am Ende aus der Tabelle Artist gelöscht werden. Sonst wird das Datenbanksystem (zu Recht!) anmerken, dass Daten zu einem Fremdschlüssel fehlen.

-- This file delets some entries from the database without droping the whole tables

DELETE FROM Tracks WHERE Album_ID = 8980 AND Track > 1; -- delete all tracks above 1 from the album with ID 8980;
DELETE FROM Tracks WHERE Track_name LIKE '%Physical%'; -- deletes all tracks where the name contains "Physical"

Auch auf GitHub (der guten Form halber 😉 ): SQL-NormalForm-example/blob/master/5_delete.sql

Fazit

In diesem Artikel haben wir die wesentlichen SQL-Befehle kennengelernt. Mit den vorgestellten Befehlen werden im praktischen Alltag die meisten Herausforderungen für die Anwendung von SQL lösbar sein.

Daneben gibt es noch eine Vielzahl weiterer Befehle – auch teilweise abhängig davon welche Datenbank bzw. welches Datenbanksystem verwendet wird. Diese sind dann eher für erweiterte Use Cases relevant.

Hier ein paar Beispiele der Schlüsselwörter:

  • VIEW: Bestimmte Ansichten/SELECT-Befehle immer wieder abrufbar und verwendbar (fast) wie eine Tabelle machen
  • GRANT: Rechte auf Datenbankebene verwalten für unterschiedliche Nutzer und Rollen
  • COMMIT: Manuell das Datenbanksystem auffordern Befehle jetzt auf die Datenbank abzusetzen und nicht weiter zu warten oder zu optimieren
  • TRANSACTION: um mehrere Befehle zu einem logischen Block zusammen zu fassen. Dadurch kann ein „alles oder nichts“-Prinzip für die Ausführung erreicht werden. Klassisches Beispiel aus Banken: Eine Überweisung von Konto A auf Konto B darf nur komplett ausgeführt werden. Also der Kontostand auf Konto A wird verringert und auf Konto B erhöht. Und nur wenn beides erfolgt, wird das „festgeschrieben“. Ansonsten wird das rückgängig gemacht.
  • Microsoft bietet über FUNCTION und TYPE die Möglichkeit eigene Funktionen bzw. Typen definieren zur Verwendung in weiteren SQL-Befehlen
  • USE um dem Datenbankmanagementsystem zu sagen in welcher Datenbank gearbeitet werden soll – ein Datenbankserver kann (und wir meist auch) in der Praxis mehrere Datenbanken enthalten

Also das Abenteuer SQL kann auch nach dieser Fallstudie noch weiter gehen.

Anmerkungen und Ergänzungen (bei Interesse)

  1. Bei ganz enger Betrachtung: Die SQL-Anfragen werden durch das jeweilige Datenbankmanagementsystem (DBMS) verarbeitet. Das DBMS greift dann auf die physischen Datenbanken unter Verwendung der jeweiligen Betriebssystemroutinen zu. Details finden sich auch in Standardwerken wie Database System Concepts von Silberschatz, Korth und Sudarshan(Amazon-Affiliate-Link, Preis ist gleich nur wir erhalten Provision).

Dieser Artikel ist Teil der Fallstudie Let the music play.

2 Gedanken zu „Let the music play: Auf geht’s in die Datenbank – mit SQL direkt arbeiten“

Schreibe einen Kommentar