SQL DDL & DML: Eine Einführung

SQL DDL & DML: Eine Einführung#

In diesem Tutorium üben wir die Funktionalitäten der SQL Data Description Language (DDL) und SQL Data Manipulation Language (DML). Die DDL beschreibt, welche Daten in einer Datenbank gespeichert werden. Die DML wird hingegen genutzt, um Instanzen in unserer Datenbank zu erstellen, zu verändern oder auch wieder zu löschen.

Wir benutzen hierfür die eingebettete Datenbank DuckDB, welche auch über das Python-Paket duckdb unterstützt wird. DuckDB wurde ursprünglich als Datei- oder In-Memory-basierte OLAP-Datenbank entwickelt (was OLAP heißt, sehen wir im Data Warehouse-Tutorium).

Hinweis: Aufgaben, welche durch einen Asterisk (*) markiert sind, sind Bonusaufgaben. Diese Aufgaben können im Tutorium behandelt werden, dies ist jedoch von den Übungsleitern nicht geplant.

DuckDB Einführung#

Im Folgenden geben wir einen groben Überblick zu den Befehlen der SQL-DDL und -DML Sprachen.

Öffnen einer Datenbankverbindung#

Als Erstes importieren wir DuckDB selbst und öffnen dann unsere erste Datenbank.

import duckdb

con = duckdb.connect()

Indem wir keinen Namen für die Datenbank angeben, wird diese nur im Arbeitsspeicher angelegt und somit auch nicht persistent gespeichert. Dies bedeutet, dass sämtliche Daten innerhalb der Datenbank verloren gehen, falls die Verbindung auf Wunsch oder aufgrund eines Fehlers geschlossen wird.

In unserem Beispiel schließen wir vorerst unsere Datenbank(-verbindung) wieder und erstellen eine neue Datenbank, in der unsere Änderungen persistent gespeichert werden. Falls Sie dieses Tutorium komplett von vorne beginnen möchten, müssen Sie deshalb diese Datenbank manuell löschen.

con.close()

con = duckdb.connect("intro.duckdb")

Erstellen einer Relation#

Nachdem wir letzte Woche gelernt haben, wie wir ein relationales Modell aus unserem (E)ER-Modell erstellen, setzten wir dies nun in die Praxis um und erstellen unsere erste Tabelle (Person) in unserer Datenbank.

Den dazu notwendigen SQL-Code können wir ausführen, indem wir die sql auf unserem connection/con Objekt aufrufen.

con.sql("CREATE TABLE Person(Name TEXT PRIMARY KEY, Age UTINYINT);")

Der oben stehende Befehl setzt sich wie folgt zusammen:

  • CREATE: Wir geben an, dass wir etwas erstellen wollen. Dies kann eine Tabelle, ein Schema, aber z.B. auch ein View sein.

  • TABLE: Wir geben an, dass wir konkret eine Tabelle erstellen wollen.

  • Person: Wir geben an, dass die Tabelle, die wir erstellen wollen, konkret Person heißen soll.

  • (: Die Menge der Attribute in der Tabelle.

    • Name: Der Name des ersten Attributs.

    • TEXT: Der Datentyp des ersten Attributs.

    • PRIMARY KEY: Dies deklariert das Attribut Name zum Primärschlüssel der Relation.

    • Age: Der Name des zweiten Attributs.

    • UTINYINT: Der Datentyp des zweiten Attributs.

  • )

Als Konvention schreiben wir SQL-Schlüsselwörter (hier CREATE, TABLE, etc.) immer groß, während die von Nutzer:innen gegebenen Parameter wie z. B. der Tabellenname (Person) oder die Attributnamen (Name und Age) im CamelCase oder snake_case geschrieben werden.

Jeden SQL-Befehl sollten Sie mit einem Semikolon beenden, um das Ende des Befehls zu signalisieren und ungewollte Interaktionen zu einem nächsten Befehl zu vermeiden. Wenn Sie nur einen einzigen Befehl haben, kann das Semikolon auch weggelassen werden.

Auf die verschiedenen Datentypen von DuckDB gehen wir weiter unten im Tutorium ein.

Nun versuchen wir erneut eine Tabelle mit dem gleichen Namen zu erstellen, vielleicht sogar mit anderen Attributen, Datentypen oder auch einem anderen Primärschlüssel.

con.sql(
    "CREATE TABLE Person(PersonId UINTEGER PRIMARY KEY, Name TEXT, Age UTINYINT, Birthdate DATE);"
)

---------------------------------------------------------------------------
CatalogException                          Traceback (most recent call last)
Cell In[4], line 1
----> 1 con.sql(
      2     "CREATE TABLE Person(PersonId UINTEGER PRIMARY KEY, Name TEXT, Age UTINYINT, Birthdate DATE);"
      3 )

CatalogException: Catalog Error: Table with name "Person" already exists!

Wir erhalten einen Fehler, da die Tabelle bereits definiert ist, genauso wie Sie in anderen Programmiersprachen Variablen nicht mehrfach definieren dürfen.

Löschen einer Relation#

Da wir die Tabelle nur versehentlich falsch definiert haben, möchten wir sie wieder löschen. Dies klappt mit dem folgenden Statement:

con.sql("DROP TABLE Person;")

Dabei geben wir mit dem DROP Befehl an, dass wir etwas löschen wollen, mit TABLE dass wir konkret eine Relation löschen wollen und zuletzt geben wir den konkreten Namen der Relation an. Nun können wir die Relation neu definieren.

Dieses Mal geben wir der Tabelle unter anderem eine PersonId als eindeutigen Identifier (UINTEGER PRIMARY KEY). Im Vergleich zu Textdatentypen können nummerische Datentypen vom Computer effizienter verarbeitet werden. Alternativ können wir auch mittels ALTER TABLE die Tabelle nachträglich verändern und z. B. Attribute hinzufügen oder entfernen, Bedingungen für Attribute hinzufügen oder auch die Tabelle selbst umbenennen.

con.sql("CREATE TABLE Person(PersonId UINTEGER PRIMARY KEY, Name TEXT, Age UTINYINT);")

Einfügen von Daten#

Als Nächstes befüllen wir unsere Relation mit Daten.

con.sql("INSERT INTO Person (PersonId, Name, Age) VALUES (1, 'Peter Schmidt', 23)")

Dieser Befehl setzt sich wie folgt zusammen:

  • INSERT INTO: Wir geben an, dass wir etwas einfügen wollen.

  • Person: Wir geben den Namen der Tabelle an, in die wir etwas einfügen wollen.

  • (PersonId, Name, Age): Wir geben die Reihenfolge der Attribute an, in der wir die Werte einfügen angeben werden.

  • VALUES: Wir geben an, dass die Werte als Nächstes folgen.

  • (1, 'Peter Schmidt', 23: Die konkreten Werte, die eingefügt werden sollen.

Als Nächstes schauen wir, was passiert, wenn wir die gleiche Instanz hinzufügen.

con.sql("INSERT INTO Person (PersonId, Name, Age) VALUES (1, 'Peter Schmidt', 23)")

Wie die Fehlermeldung schon selber aussagt, versuchen wir den Primärschlüssel mit dem Wert 1 erneut zu vergeben, was die UNIQUE-Bedingung des Primärschlüssels verletzt. Wenn wir alternativ versuchen, Peter Schmidt, der 23 Jahre alt ist, erneut mit einer noch nicht vergebenen PersonId einzufügen, dann ist dies kein Problem. Dabei lassen wir dieses Mal auch die Reihenfolge der Attribute weg, da wir die Werte ohnehin in der gleichen Reihenfolge wie in der Definition der Relation angeben.

con.sql("INSERT INTO Person VALUES (2, 'Peter Schmidt', 23)")

Des Weiteren können wir auch mehrere Instanzen auf einmal hinzufügen, indem wir die Menge an Attributen der unterschiedlichen Instanzen durch Kommata trennen.

con.sql("INSERT INTO Person VALUES (3, 'Sandra Müller', 24), (4, 'Petra Maier', 14)")

Hinweis: Strings werden mit einfahen Anführungsstrichen (') markiert. Doppelte Anführungsstriche (") kennzeichnen Tabellennamen.

Ausgabe einer Relation#

Wir können uns nun unsere Relation ausgeben lassen, um das Zwischenergebnis unserer Operationen zu begutachten.

Hinweis: Das Anfragen bzw. Ausgeben von Daten ist Inhalt von Tutorium 6. Damit wir unsere Ergebnisse in diesem Tutorium aber überprüfen können, stellen wir an dieser Stelle die einfachsten Bausteine von SQL-DQL vor.

con.sql("SELECT * FROM Person")

Der Befehl setzt sich wie folgt zusammen:

  • SELECT: Wir geben an, dass wir im Folgenden die Attribute auflisten, welche wir uns ausgeben lassen wollen.

  • *: Der Asterisk gibt an, dass wir alle Attribute ausgegeben bekommen wollen.

  • FROM: Dies gibt an, dass wir im Folgenden die Relation(en) angeben, von denen wir die Daten beziehen.

  • Person: Wir geben an, dass wir die Tabelle Person verwenden wollen.

Hinweis: Diese Befehle gehören der SQL Data Query Language an und werden vertieft in Tutorium 6 behandelt. Damit wir die Ergebnisse unserer Befehle jedoch überprüfen können, führen wir diesen einfachen Fall in diesem Kontext bereits ein.

Änderungen (Updates) der Datenbank#

Durch die Ausgabe fällt uns auf, dass sich beim letzten Befehl “aus Versehen” ein Typo eingeschlichen hat: Leider haben wir ein falsches Alter bei Petra Maier angegeben (14statt 24), weshalb wir nun ihr Alter anpassen wollen. Dies tun wir mit dem UPDATE Befehl.

con.sql("UPDATE Person SET Age = Age + 10 WHERE PersonId = 4")

Dieser Befehl setzt sich wie folgt zusammen:

  • UPDATE: Wir geben an, dass wir etwas anpassen wollen.

  • Person: Wir geben an, dass wir konkret die Relation Person aktualisieren wollen.

  • SET: Mit dem Set Keyword geben wir an, dass wir etwas auf einen neuen Wert setzen wollen.

  • Age: Das Attribut, welches wir aktualisieren wollen.

  • Age + 10: Die Bedingung oder die Formel, mit der wir das Attribut aktualisieren.

  • WHERE: Mit dem Keyword WHERE geben wir an, dass wir eine Einschränkung für die Aktualisierung im Folgenden angeben.

  • PersonId = 4: Konkret sollen nur die Personen mit der PersonId deren Wert 4, also Petra Maier aktualisiert werden.

con.sql("SELECT * FROM Person")

Uns ist aufgefallen, dass generell keine Personen unter 18 in die Datenbank eingefügt werden sollen. Damit man später hinzugekommene Anforderungen noch umsetzen kann, unterstützen viele andere Datenbankmanagementsysteme das nachträgliche Einfügen eines CHECK-Constraints, z. B. so: ALTER TABLE ADD CONSTRAINT <contraintName> CHECK (<constraint>). Leider ist diese Funktionalität in DuckDB noch nicht implementiert, kann aber nachgeahmt werden, indem wir die Tabelle umbenennen, eine neue Tabelle mit dem vorherigen Namen und dem nachträglichen constraint (CHECK) erstellen und dann die Daten aus der ursprünglichen Tabelle in die neue kopieren.

Überblick über die Datenbank#

Wir können uns nun die Struktur der Datenbank mithilfe des sogenannten Datenbank-Katalogs oder einem grafischen Tool zur Verwaltung von Datenbanken anschauen. In DuckDB nutzen wir hierfür die bereits existierende sqlite_master Tabelle.

con.sql("SELECT * FROM sqlite_master")

con.close()
Contents