SQL-Einführung

Diese Seite gibt einen Überblick, wie einfache Operationen in SQL ausgeführt werden. Dieses Tutorial soll nur eine Einführung geben und ist keinesfalls ein vollständiges SQL-Tutorial. Es ist an das PostgreSQL-Tutorial angelehnt.

Der SQL-Dialekt von DuckDB folgt weitgehend den Konventionen des PostgreSQL-Dialekts. Die wenigen Ausnahmen sind auf der Seite PostgreSQL-Kompatibilität aufgeführt.

In den folgenden Beispielen gehen wir davon aus, dass Sie die DuckDB-Kommandozeile (CLI) installiert haben. Informationen zur Installation der CLI finden Sie auf der Installationsseite.

Tipp Wenn Sie eine umfassende SQL-Einführung suchen, schauen Sie sich die Folien des Kurses „Tabular Database Systems“ an:

• The Structured Query Language (SQL)
• More SQL (Subqueries + Embedded SQL)
• SQL: Grouping + Aggregation and Functional Dependencies

Konzepte

DuckDB ist ein relationales Datenbankmanagementsystem (RDBMS). Das bedeutet, es verwaltet Daten, die in Relationen gespeichert sind. Eine Relation ist im Wesentlichen der mathematische Begriff für eine Tabelle.

Jede Tabelle ist eine benannte Sammlung von Zeilen. Jede Zeile einer gegebenen Tabelle hat dieselbe Menge benannter Spalten, und jede Spalte hat einen bestimmten Datentyp. Die Tabellen selbst liegen in Schemata, und eine Sammlung von Schemata bildet die gesamte Datenbank, auf die Sie zugreifen können.

Eine neue Tabelle anlegen

Sie können eine neue Tabelle anlegen, indem Sie den Tabellennamen sowie alle Spaltennamen und ihre Typen angeben:

CREATE TABLE weather (
    city    VARCHAR,
    temp_lo INTEGER, -- minimum temperature on a day
    temp_hi INTEGER, -- maximum temperature on a day
    prcp    FLOAT,
    date    DATE
);

Sie können das mit Zeilenumbrüchen in die Shell eingeben. Der Befehl ist erst mit dem Semikolon abgeschlossen.

Leerraum (d. h. Leerzeichen, Tabulatoren und Zeilenumbrüche) darf in SQL-Befehlen frei verwendet werden. Sie können den Befehl also anders ausrichten als oben oder sogar in einer Zeile schreiben. Zwei Bindestriche (--) leiten Kommentare ein. Alles danach bis zum Zeilenende wird ignoriert. SQL ist bei Schlüsselwörtern und Bezeichnern nicht case-sensitiv. Beim Zurückgeben von Bezeichnern wird ihre ursprüngliche Groß-/Kleinschreibung beibehalten.

Im SQL-Befehl geben wir zuerst die Art des Befehls an, den wir ausführen wollen: CREATE TABLE. Danach folgen die Parameter. Zuerst der Tabellenname weather. Dann die Spaltennamen und Spaltentypen.

city VARCHAR legt fest, dass die Tabelle eine Spalte namens city vom Typ VARCHAR hat. VARCHAR speichert Text beliebiger Länge. Die Temperaturfelder liegen im Typ INTEGER, der ganze Zahlen speichert (d. h. Zahlen ohne Dezimalpunkt). FLOAT-Spalten speichern Gleitkommazahlen einfacher Genauigkeit (d. h. Zahlen mit Dezimalpunkt). DATE speichert ein Datum (d. h. eine Kombination aus Jahr, Monat, Tag). DATE speichert nur den konkreten Tag, keine Uhrzeit zu diesem Tag.

DuckDB unterstützt die Standard-SQL-Typen INTEGER, SMALLINT, FLOAT, DOUBLE, DECIMAL, CHAR(n), VARCHAR(n), DATE, TIME und TIMESTAMP.

Das zweite Beispiel speichert Städte und ihre geografische Lage:

CREATE TABLE cities (
    name VARCHAR,
    lat  DECIMAL,
    lon  DECIMAL
);

Schließlich: Wenn Sie eine Tabelle nicht mehr brauchen oder sie anders neu anlegen möchten, können Sie sie mit folgendem Befehl entfernen:

DROP TABLE ⟨tablename⟩;

Eine Tabelle mit Zeilen füllen

Die Insert-Anweisung füllt eine Tabelle mit Zeilen:

INSERT INTO weather
VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

Konstanten, die keine Zahlen sind (z. B. Text und Daten), müssen in einfache Anführungszeichen ('') gesetzt werden, wie im Beispiel. Eingabedaten für den Date-Typ müssen als 'YYYY-MM-DD' formatiert sein.

In die Tabelle cities können Sie auf dieselbe Weise einfügen.

INSERT INTO cities
VALUES ('San Francisco', -194.0, 53.0);

Die bisherige Syntax verlangt, dass Sie sich die Reihenfolge der Spalten merken. Eine alternative Syntax erlaubt, die Spalten explizit aufzulisten:

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

Sie können die Spalten in anderer Reihenfolge auflisten oder sogar einige weglassen, z. B. wenn prcp unbekannt ist:

INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);

Tipp Viele Entwickler halten das explizite Auflisten der Spalten für besseren Stil als das implizite Verlassen auf die Reihenfolge.

Bitte geben Sie alle oben gezeigten Befehle ein, damit Sie in den folgenden Abschnitten Daten zum Arbeiten haben.

Alternativ können Sie die Anweisung COPY verwenden. Das ist bei großen Datenmengen schneller, weil COPY für Massenladen optimiert ist und weniger Flexibilität als INSERT bietet. Ein Beispiel mit weather.csv wäre:

COPY weather
FROM 'weather.csv';

Der Dateiname der Quelldatei muss auf der Maschine verfügbar sein, auf der der Prozess läuft. Es gibt viele weitere Wege, Daten in DuckDB zu laden; siehe den entsprechenden Dokumentationsabschnitt.

Eine Tabelle abfragen

Um Daten aus einer Tabelle zu holen, wird die Tabelle abgefragt. Dazu dient eine SQL-SELECT-Anweisung. Die Anweisung besteht aus einer Select-Liste (die die zurückzugebenden Spalten auflistet), einer Tabellenliste (die die Tabellen auflistet, aus denen die Daten kommen) und einer optionalen Qualifikation (die etwaige Einschränkungen festlegt). Um beispielsweise alle Zeilen der Tabelle weather zu holen, geben Sie ein:

SELECT *
FROM weather;

Hier ist * eine Kurzform für „alle Spalten“. Dasselbe Ergebnis erhielten Sie mit:

SELECT city, temp_lo, temp_hi, prcp, date
FROM weather;

Die Ausgabe sollte sein:

city	temp_lo	temp_hi	prcp	date
San Francisco	46	50	0.25	1994-11-27
San Francisco	43	57	0.0	1994-11-29
Hayward	37	54	NULL	1994-11-29

In der Select-Liste können Sie Ausdrücke schreiben, nicht nur einfache Spaltenverweise. Zum Beispiel:

SELECT city, (temp_hi + temp_lo) / 2 AS temp_avg, date
FROM weather;

Das sollte ergeben:

city	temp_avg	date
San Francisco	48.0	1994-11-27
San Francisco	50.0	1994-11-29
Hayward	45.5	1994-11-29

Beachten Sie, wie die AS-Klausel die Ausgabespalte umbenennt. (Die AS-Klausel ist optional.)

Eine Abfrage kann durch eine WHERE-Klausel „qualifiziert“ werden, die festlegt, welche Zeilen gewünscht sind. Die WHERE-Klausel enthält einen booleschen (Wahrheitswert-)Ausdruck, und nur Zeilen, für die der boolesche Ausdruck wahr ist, werden zurückgegeben. Die üblichen booleschen Operatoren (AND, OR und NOT) sind in der Qualifikation erlaubt. Die folgende Abfrage holt beispielsweise das Wetter von San Francisco an regnerischen Tagen:

SELECT *
FROM weather
WHERE city = 'San Francisco'
  AND prcp > 0.0;

Ergebnis:

city	temp_lo	temp_hi	prcp	date
San Francisco	46	50	0.25	1994-11-27

Sie können verlangen, dass die Ergebnisse einer Abfrage sortiert zurückgegeben werden:

SELECT *
FROM weather
ORDER BY city;

city	temp_lo	temp_hi	prcp	date
Hayward	37	54	NULL	1994-11-29
San Francisco	43	57	0.0	1994-11-29
San Francisco	46	50	0.25	1994-11-27

In diesem Beispiel ist die Sortierreihenfolge nicht vollständig festgelegt, sodass Sie die San-Francisco-Zeilen in beliebiger Reihenfolge erhalten können. Die oben gezeigten Ergebnisse erhalten Sie immer, wenn Sie Folgendes tun:

SELECT *
FROM weather
ORDER BY city, temp_lo;

Sie können verlangen, dass doppelte Zeilen aus dem Ergebnis einer Abfrage entfernt werden:

SELECT DISTINCT city
FROM weather;

city
San Francisco
Hayward

Auch hier kann die Reihenfolge der Ergebniszeilen variieren. Konsistente Ergebnisse sichern Sie, indem Sie DISTINCT und ORDER BY zusammen verwenden:

SELECT DISTINCT city
FROM weather
ORDER BY city;

Joins zwischen Tabellen

Bisher haben unsere Abfragen jeweils nur auf eine Tabelle zugegriffen. Abfragen können auf mehrere Tabellen gleichzeitig zugreifen oder auf dieselbe Tabelle so, dass mehrere Zeilen der Tabelle gleichzeitig verarbeitet werden. Eine Abfrage, die gleichzeitig auf mehrere Zeilen derselben oder unterschiedlicher Tabellen zugreift, heißt Join-Abfrage. Angenommen, Sie möchten alle Wetterdatensätze zusammen mit der Lage der zugehörigen Stadt auflisten. Dazu müssen wir die Spalte city jeder Zeile der Tabelle weather mit der Spalte name aller Zeilen der Tabelle cities vergleichen und die Zeilenpaare auswählen, in denen diese Werte übereinstimmen.

Das leistet die folgende Abfrage:

SELECT *
FROM weather, cities
WHERE city = name;

city	temp_lo	temp_hi	prcp	date	name	lat	lon
San Francisco	46	50	0.25	1994-11-27	San Francisco	-194.000	53.000
San Francisco	43	57	0.0	1994-11-29	San Francisco	-194.000	53.000

Beachten Sie zwei Dinge am Ergebnis:

• Es gibt keine Ergebniszeile für die Stadt Hayward. Das liegt daran, dass es in der Tabelle cities keinen passenden Eintrag für Hayward gibt, sodass der Join die nicht zugeordneten Zeilen in der Tabelle weather ignoriert. Gleich sehen wir, wie sich das beheben lässt.
• Es gibt zwei Spalten mit dem Städtenamen. Das ist korrekt, weil die Spaltenlisten der Tabellen weather und cities aneinandergehängt werden. In der Praxis ist das jedoch unerwünscht, daher sollten Sie die Ausgabespalten lieber explizit auflisten statt * zu verwenden:

SELECT city, temp_lo, temp_hi, prcp, date, lon, lat
FROM weather, cities
WHERE city = name;

city	temp_lo	temp_hi	prcp	date	lon	lat
San Francisco	46	50	0.25	1994-11-27	53.000	-194.000
San Francisco	43	57	0.0	1994-11-29	53.000	-194.000

Da die Spalten alle unterschiedliche Namen hatten, hat der Parser automatisch gefunden, zu welcher Tabelle sie gehören. Gäbe es doppelte Spaltennamen in den beiden Tabellen, müssten Sie die Spaltennamen qualifizieren, um zu zeigen, welche Sie meinen, etwa so:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.lon, cities.lat
FROM weather, cities
WHERE cities.name = weather.city;

Es gilt allgemein als guter Stil, in einer Join-Abfrage alle Spaltennamen zu qualifizieren, damit die Abfrage nicht scheitert, wenn später ein doppelter Spaltenname zu einer der Tabellen hinzugefügt wird.

Join-Abfragen der bisher gesehenen Art können auch in dieser alternativen Form geschrieben werden:

SELECT *
FROM weather
INNER JOIN cities ON weather.city = cities.name;

Diese Syntax ist nicht so verbreitet wie die oben, wir zeigen sie aber, damit Sie die folgenden Themen besser verstehen.

Nun wollen wir die Hayward-Datensätze wieder hereinholen. Die Abfrage soll die Tabelle weather durchlaufen und für jede Zeile die passende(n) cities-Zeile(n) finden. Wird keine passende Zeile gefunden, sollen für die Spalten der Tabelle cities „leere Werte“ eingesetzt werden. Diese Art von Abfrage heißt Outer Join. (Die Joins, die wir bisher gesehen haben, sind Inner Joins.) Der Befehl sieht so aus:

SELECT *
FROM weather
LEFT OUTER JOIN cities ON weather.city = cities.name;

city	temp_lo	temp_hi	prcp	date	name	lat	lon
San Francisco	46	50	0.25	1994-11-27	San Francisco	-194.000	53.000
San Francisco	43	57	0.0	1994-11-29	San Francisco	-194.000	53.000
Hayward	37	54	NULL	1994-11-29	NULL	NULL	NULL

Diese Abfrage heißt Left Outer Join, weil die links vom Join-Operator genannte Tabelle jede ihrer Zeilen mindestens einmal in der Ausgabe hat, während die Tabelle rechts nur die Zeilen ausgibt, die zu einer Zeile der linken Tabelle passen. Wird eine Zeile der linken Tabelle ausgegeben, zu der keine rechte Zeile passt, werden für die Spalten der rechten Tabelle leere (Null-)Werte eingesetzt.

Aggregatfunktionen

Wie die meisten anderen relationalen Datenbankprodukte unterstützt DuckDB Aggregatfunktionen. Eine Aggregatfunktion berechnet ein einzelnes Ergebnis aus mehreren Eingabezeilen. Es gibt beispielsweise Aggregate, um count, sum, avg (Durchschnitt), max (Maximum) und min (Minimum) über eine Menge von Zeilen zu berechnen.

Als Beispiel können wir die höchste Tiefsttemperatur irgendwo finden mit:

SELECT max(temp_lo)
FROM weather;

max(temp_lo)
46

Wenn wir wissen wollten, in welcher Stadt (oder welchen Städten) dieser Wert vorkam, könnten wir versuchen:

SELECT city
FROM weather
WHERE temp_lo = max(temp_lo);

Das funktioniert jedoch nicht, weil das Aggregat max nicht in der WHERE-Klausel verwendet werden kann:

Binder Error:
WHERE clause cannot contain aggregates!

Diese Einschränkung besteht, weil die WHERE-Klausel festlegt, welche Zeilen in die Aggregatberechnung eingehen; sie muss also ausgewertet werden, bevor Aggregatfunktionen berechnet werden. Wie so oft lässt sich die Abfrage jedoch umformulieren, um das gewünschte Ergebnis zu erreichen, hier mit einer Unterabfrage:

SELECT city
FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

city
San Francisco

Das ist in Ordnung, weil die Unterabfrage eine unabhängige Berechnung ist, die ihr eigenes Aggregat getrennt von dem berechnet, was in der äußeren Abfrage geschieht.

Aggregate sind auch sehr nützlich in Kombination mit GROUP BY-Klauseln. Zum Beispiel können wir die maximale Tiefsttemperatur in jeder Stadt holen mit:

SELECT city, max(temp_lo)
FROM weather
GROUP BY city;

city	max(temp_lo)
San Francisco	46
Hayward	37

Das liefert eine Ausgabezeile pro Stadt. Jedes Aggregatergebnis wird über die Tabellenzeilen berechnet, die zu dieser Stadt passen. Diese gruppierten Zeilen können wir mit HAVING filtern:

SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;

city	max(temp_lo)
Hayward	37

Das liefert dieselben Ergebnisse nur für die Städte, deren sämtliche temp_lo-Werte unter 40 liegen. Wenn uns schließlich nur Städte interessieren, deren Namen mit S beginnen, können wir den Operator LIKE verwenden:

SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'            -- (1)
GROUP BY city
HAVING max(temp_lo) < 40;

Weitere Informationen zum Operator LIKE finden Sie auf der Seite zur Mustererkennung.

Es ist wichtig, das Zusammenspiel von Aggregaten und den SQL-Klauseln WHERE und HAVING zu verstehen. Der grundlegende Unterschied zwischen WHERE und HAVING ist: WHERE wählt Eingabezeilen aus, bevor Gruppen und Aggregate berechnet werden (steuert also, welche Zeilen in die Aggregatberechnung eingehen), während HAVING Gruppenzeilen auswählt, nachdem Gruppen und Aggregate berechnet wurden. Die WHERE-Klausel darf daher keine Aggregatfunktionen enthalten; es ergibt keinen Sinn, ein Aggregat zu verwenden, um zu bestimmen, welche Zeilen Eingaben der Aggregate werden. Die HAVING-Klausel enthält dagegen immer Aggregatfunktionen.

Im vorherigen Beispiel können wir die Einschränkung des Städtenamens in WHERE anwenden, weil sie kein Aggregat braucht. Das ist effizienter, als die Einschränkung zu HAVING hinzuzufügen, weil wir Gruppierung und Aggregatberechnungen für alle Zeilen vermeiden, die die WHERE-Prüfung nicht bestehen.

Aktualisierungen

Vorhandene Zeilen können Sie mit dem Befehl UPDATE aktualisieren. Angenommen, Sie stellen fest, dass die Temperaturwerte nach dem 28. November alle um 2 Grad zu hoch sind. Sie können die Daten wie folgt korrigieren:

UPDATE weather
SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
WHERE date > '1994-11-28';

Schauen Sie sich den neuen Zustand der Daten an:

SELECT *
FROM weather;

city	temp_lo	temp_hi	prcp	date
San Francisco	46	50	0.25	1994-11-27
San Francisco	41	55	0.0	1994-11-29
Hayward	35	52	NULL	1994-11-29

Löschungen

Zeilen können mit dem Befehl DELETE aus einer Tabelle entfernt werden. Angenommen, Sie interessieren sich nicht mehr für das Wetter von Hayward. Dann können Sie diese Zeilen wie folgt aus der Tabelle löschen:

DELETE FROM weather
WHERE city = 'Hayward';

Alle Wetterdatensätze zu Hayward werden entfernt.

SELECT *
FROM weather;

city	temp_lo	temp_hi	prcp	date
San Francisco	46	50	0.25	1994-11-27
San Francisco	41	55	0.0	1994-11-29

Bei Anweisungen der folgenden Form sollte man vorsichtig sein:

DELETE FROM ⟨table_name⟩;

Warnung Ohne Qualifikation entfernt DELETE alle Zeilen aus der angegebenen Tabelle und hinterlässt sie leer. Das System fordert vor dem Ausführen keine Bestätigung an.