info@madafa.de

PostgreSQL – Eine Einführung

Einführung

Für den Austausch und die Speicherung von Daten über das Internet sind Datenbanken eine essentielle Komponente und gleichzeitig Kernelemente für viele Websites und Anwendungen. Einer der wichtigsten Aspekte der Datenbankverwaltung ist das Abrufen von Daten aus einer Datenbank, wobei es hierzu verschiedene Möglichkeiten gibt.
Am häufigsten werden queries verwendet, die über die Befehlszeile gesendet werden. Eine query ist ein Befehl, der zum Abrufen von Daten aus einer Tabelle verwendet wird. In SQL (Structured Query Language) werden Abfragen fast immer mit der SELECT-Anweisung durchgeführt.
Wir werden uns sowohl mit der grundlegenden Syntax von SQL-Abfragen und einigen der am häufigsten verwendeten Funktionen und Operatoren beschäftigen, sowie der Durchführung von SQL-Abfragen unter Verwendung einiger Beispieldaten in einer PostgreSQL-Datenbank.

PostgreSQL ist ein objektrelationales Datenbankmanagementsystem. PostgreSQL, oft auch Postgres genannt, orientiert sich sehr eng am SQL-Standard, enthält jedoch auch einige Funktionen, die in anderen relationalen Datenbanksystemen nicht vorhanden sind.

Voraussetzungen

Wir führen die hier beschriebenen Befehle und Konzepte auf einem Docker Container aus. Sie können jedoch auch auf jedem Linux-basierten Betriebssystem, auf dem eine SQL-Datenbanksoftware ausgeführt wird, verwendet werden.

Für die von uns genutzte Einrichtung benötigen Sie Folgendes:

  • Eine Docker-Installation (z.B. hier)
  • Zugriff auf Windows PowerShell

Genauere Informationen dazu, wie sie PostgreSQL auf Docker Containern ausführen, haben wir bereits in diesem Beitrag näher beschrieben. Nachdem Sie eine Verbindung zu Postgres im Docker Container hergestellt haben, können wir mit dem Erstellen der Datenbank fortfahren.

Erstellen einer Beispieldatenbank

Lassen Sie uns zunächst eine Datenbank und einige Tabellen erstellen, die wir mit Beispieldaten füllen. Stellen Sie sich dafür folgende Situation vor:
Sie feiern mit einigen Ihrer Freunde und Freundinnen regelmäßig den jeweiligen Geburtstag und treffen sich auf der örtlichen Bowlingbahn. Nach einem Freundschaftsturnier bereiten Sie gemeinsam das Lieblingsessen der Geburtstagsperson zu.
Sie beschließen die Turnierergebnisse und sämtliche notwendigen Informationen, wie bevorzugte Vorspeisen, Beilagen und Desserts, in einer Datenbank festzuhalten, um die Planung der Geburtstagsmenüs zu vereinfachen.

Wir wollen Ihnen anhand dieses Beispiels verschiedene SQL-Abfragen veranschaulichen.
Erstellen Sie hierzu zunächst mit CREATE DATABASE birthdays; die benötigte Datenbank und wählen Sie dann diese Datenbank mit \c birthdays aus.

Im nächsten Schritt erstellen Sie die erste Tabelle in dieser Datenbank.
Wir wollen in dieser Tabelle die Turnierergebnisse festhalten. Der folgende Befehl erstellt unsere Tabelle und fügt Spalten mit Namen, gewonnenen Turnieren, Gesamtpunktzahl und Größe der Bowlingschuhe ein.

CREATE TABLE tourneys (
name varchar(30),
wins real,
best real,
size real
);

Sobald Sie den Befehl ausgeführt haben, erhalten Sie die Ausgabe Create Table.

Füllen Sie nun die Tabelle mit einigen Daten, z.B.:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Julia', '5', '225', '9'),
('Monika', '6', '198', '7.5'),
('Stephanie', '10', '256', '8'),
('Sandra', '11', '282', '8.5'),
('Svetlana', '3', '263', '7');

Sie sollten nun folgende Ausgabe erhalten: Insert 0 5

Erstellen Sie im Anschluss die zweite Tabelle und erfüllen Sie diese ebenfalls mit Beispieldaten:

CREATE TABLE dinners (
name varchar(30),
birthdate date,
entree varchar(30),
side varchar(30),
dessert varchar(30)
);
INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Julia', '1976-03-12', 'steak', 'salad', 'cake'),
('Monika', '1978-08-04', 'chicken', 'fries', 'ice cream'),
('Stephanie', '1981-10-22', 'tofu', 'fries', 'cake'),
('Sandra', '1978-04-23', 'tofu', 'salad', 'ice cream'),
('Svetlana', '1974-12-06', 'steak', 'fries', 'ice cream');

Wenn sie diese Befehle ausgeführt haben, ist die Datenbankeinrichtung abgeschlossen und wir schauen uns als Nächstes sogenannte SELECT-Abfragen an.

SELECT-Anweisungen

Die SELECT-Abfrage wird verwendet, um anzugeben, welche Spalten aus einer Tabelle zurückgegeben werden sollen. Sie enthält meist eine FROM-Klausel, die angibt, welche Tabelle von der Anweisung abgefragt wird. SQL-Abfragen folgen in der Regel dieser Syntax:

SELECT FROM WHERE ;

Mit folgendem Befehl wird die gesamte Spalte ‘name’ aus der Tabelle ‘dinners’ zurückgegeben:

SELECT name FROM dinners ;

Mit SELECT name, birthdate FROM dinners ; können Sie mehrere Spalten aus der selben Tabelle auswählen. Hierzu trennen Sie einfach die Spaltennamen durch ein Komma.

Wenn Sie dem ‘SELECT’-Operator ein Sternchen ‘*‘ hinzufügen, werden nicht nur eine oder mehrere bestimmte Spalten, sondern alle Spalten einer Tabelle ausgegeben. Der folgende Befehl gibt jede Spalte aus der Tabelle ‘tourneys’ zurück:

SELECT * FROM tourneys ;

Um Datensätze zu filtern, die eine bestimmte Bedingung erfüllen, wird die ‘WHERE’-Klausel in Abfragen verwendet. Alle Zeilen, die die angegebene Bedingung nicht erfüllen, werden aus dem Ergebnis entfernt.

Der Vergleichsoperator in einer ‘WHERE’- Klausel definiert, wie die angegebene Spalte mit dem Wert verglichen werden soll. Hier sind einige gängige SQL-Vergleichsoperatoren:

OperatorBedeutung
=Gleich
>Größer als
<Kleiner als
>=Größer gleich
<=Kleiner gleich
<>Ungleich
!=Ungleich
!<Nicht kleiner als
!>Nicht größer als

Wollen Sie beispielsweise die Schuhgröße von Stephanie ermitteln, können Sie den folgenden Befehl verwenden:

SELECT size FROM tourneys WHERE name = 'Stephanie' ;

SQL erlaubt die Verwendung von Platzhalter-Zeichen. Diese sind vor allem von Vorteil, wenn Sie einen bestimmten Eintrag in einer Tabelle suchen, aber nicht sicher sind, um welchen es sich genau handelt. Zu den Platzhaltern gehören beispielsweise Unterstriche oder Prozentzeichen, wobei das Prozentzeichen ‘%’ für Null oder mehrere unbekannte Zeichen und der Unterstrich ‘_’ für ein einzelnes unbekanntes Zeichen steht.

Nehmen Sie an, Sie erinnern sich nicht mehr an das Lieblingsgericht einiger Ihrer Freundinnen, wissen jedoch, dass es mit einem ‘t‘ beginnt. Mit der folgenden Abfrage können Sie den Namen finden:

SELECT entree FROM dinners WHERE entree LIKE 't%' ;

Anhand unserer Ausgabe, wissen Sie nun, dass die Vorspeise, an die Sie sich nicht erinnern konnten ‘Tofu’ ist.

In manchen Fällen bietet es sich an, für lange oder schwer lesbare Namen Aliasse zu erstellen, um die Lesbarkeit dieser Namen zu verbessern. Dies erreichen Sie temporär und nur für die Dauer der Abfrage, für die sie erstellt werden, mit dem Schlüsselwort ‘AS’.
In dem nächsten Beispiel, wird die Spalte ‘name’ als ‘n’, die Spalte ‘birthdate’ als ‘b’ und die Spalte ‘dessert’ als ‘d’ angezeigt:

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners ;

In unseren bisherigen Beispielen haben Sie nun die häufigsten Klauseln und Schlüsselwörter in SQL-Abfragen kennengelernt. Wollen Sie allerdings Berechnungen durchführen oder basierend auf Ihren Daten einen Skalarwert ableiten, benötigen Sie weitere Funktionen. Hierzu schauen wir uns als Nächstes Aggregatfunktionen an.

Aggregatfunktionen

Aggregatfunktionen führen Berechnungen für verschiedene Werte durch und geben einen einzelnen Wert zurück. Dies ist nützlich, wenn Sie häufig mit Daten arbeiten und bestimmte Informationen über die vorhanden Daten haben möchten, ohne diese Daten explizit einsehen zu müssen.

Mit der ‘COUNT’-Funktion, können Sie sich die Anzahl von Zeilen, die eine bestimmte Bedingung erfüllen, zurückgeben lassen.
Mit folgender Abfrage können Sie sich beispielsweise die Anzahl der Freundinnen, die sich Tofu für Ihre Geburtstagsfeier wünschen, ausgeben lassen.

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu' ;

‘AVG’ gibt den Durchschnittswert einer Spalte zurück. Hier z.B. die durchschnittliche beste Punktzahl:

SELECT AVG(best) FROM tourneys ;

‘SUM’ ermittelt die Gesamtsumme einer Spalte:

SELECT SUM(wins) FROM tourneys ;

Im nächsten Beispiel können Sie sehen, dass ‘AVG’ und ‘SUM’ nur mit numerischen Daten funktionieren. Geben Sie nicht die entsprechenden Daten ein, bekommen Sie folgende Fehlermeldung:

SELECT SUM(entree) FROM dinners ;

Benutzen Sie ‘MIN’, um den kleinsten und ‘MAX’,um den größten Wert innerhalb einer Spalte zu finden:

SELECT MIN(wins) FROM tourneys ;
SELECT MAX(wins) FROM tourneys ;

‘MIN’ und ‘MAX’ können sowohl für numerische, als auch für alphabetische Datentypen verwendet werden.
Die MIN-Funktion zeigt den ersten Wert in alphabetischer Reihenfolge an, die MAX-Funktion den letzten:

SELECT MIN(name) FROM dinners ; und SELECT MAX(name) FROM dinners ;

Wir haben Ihnen hier nur einen kleinen Teil der Möglichkeiten, die Sie mit Aggregatfunktionen haben, beschrieben. Im nächsten Abschnitt befassen wir uns mit Aggregatfunktionen in Kombination mit der ‘GROUP BY’-Klausel, hierbei schauen wir uns Abfrageklauseln an, die sich auf die Sortierung der Ergebnismenge auswirken.

Abfrageausgaben

Zum bearbeiten der SELECT-Abfrage gibt es neben den FROM- und WHERE-Klauseln noch weitere Klauseln, auf die wir in diesem Abschnitt genauer eingehen möchten.

Neben ‘FROM und ‘WHERE ist die ‘GROUP BY‘-Klausel eine der am häufigsten verwendeten Abfrageklauseln. Mit dieser Option können sie eine Ergebnismenge gruppieren.

Im nächsten Beispiel wollen wir wissen, von wie vielen Personen die Hauptgerichte bevorzugt werden:

SELECT COUNT(name), entree FROM dinners GROUP BY entree ;

Zum Sortieren Ihrer Abfrageergebnisse benutzen Sie die ORDER BY’-Klausel. Hierbei werden standardmäßig numerische Werte in aufsteigender Reihenfolge und Textwerte in alphabetischer Reihenfolge sortiert.
Bei folgender Abfrage werden die Spalten ‘name’ und ‘birthdate’ aufgelistet und nach dem Geburtsdatum sortiert:

SELECT name, birthdate FROM dinners ORDER BY birthdate ;

Wollen Sie die Ergebnisse in absteigender Reihenfolge sortieren, schließen Sie die Abfrage mit ‘DESC’:

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC ;

Wie bereits beschrieben, lassen sich Ergebnisse mit der WHERE-Klausel nach bestimmten Bedingungen filtern. Wenn Sie die WHERE-Klausel allerdings mit einer Aggregatfunktion verwenden, wird Ihnen eine Fehlermeldung zurückgegeben. Mit der HAVING-Klausel existiert eine ähnliche Funktion, die mit Aggregatfunktionen kompatibel ist. Bei dieser Klausel muss zusätzlich auch die GROUP BY-Klausel vorhanden sein, wie Sie der folgenden Abfrage entnehmen können:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3 ;

Abfragen mehrerer Tabellen

Oftmals enthalten Datenbanken mehrere Tabellen mit jeweils unterschiedlichen Datensätzen. SQL bietet verschiedene Möglichkeiten, einzelne Abfragen für mehrere Tabellen auszuführen.

Mit der JOIN-Klausel können Zeilen aus zwei oder mehr Tabellen in einem Abfrageergebnis kombiniert werden. Dazu wird eine verwandte Spalte zwischen den Tabellen gefunden und anschließend die Ergebnisse entsprechend sortiert.
SELECT-Anweisungen, die eine JOIN-Klausel enthalten, folgen im Allgemeinen nachstehender Syntax, wobei die Tabellen, aus denen die Spalten ausgewählt werden jeweils mit einem Punkt voneinander getrennt werden:

SELECT ., .
FROM
JOIN  ON .=. ;

Schauen wir uns hierzu unser nächstes Beispiel an:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
JOIN dinners ON tourneys.name=dinners.name ;

Die hier verwendete JOIN-Klausel ist ohne weitere Argumente eine innere JOIN-Klausel. Dies bedeutet, dass alle Datensätze mit übereinstimmenden Werten in beiden Tabellen ausgewählt und in der Ergebnismenge zurückgegeben werden. Nicht übereinstimmende Datensätze werden ausgeschlossen.
Fügen wir zur Veranschaulichung jeder Tabelle eine neue Zeile hinzu, für die in der anderen Tabelle kein entsprechender Eintrag vorhanden ist und führen die vorherige Anweisung erneut aus:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Esra', '2', '197', '6.5') ;
INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Pauline', '1980-05-02', 'steak', 'salad', 'ice cream') ;

Wie Sie sehen, fehlen in der neuen Abfrage die neuen Datensätze, da die beiden Tabellen nicht die entsprechenden Einträge enthalten.

Es ist jedoch möglich, alle Datensätze aus einer der Tabellen mit einer OUTER JOIN– Klausel zurückzugeben. OUTER JOIN-Klauseln werden entweder als ‘LEFT JOIN’, ‘RIGHT JOIN’ oder ‘FULL JOIN’ geschrieben.

Eine LEFT JOIN– Klausel gibt alle Datensätze aus der ‘linken’ Tabelle und nur die übereinstimmenden Datensätze aus der rechten Tabelle zurück. Im Kontext von Outer-Joins ist die linke Tabelle diejenige, auf die durch die FROM-Klausel verwiesen wird und die rechte Tabelle ist jede andere Tabelle, auf die nach der JOIN-Anweisung verwiesen wird.

Führen Sie die vorherige Abfrage erneut aus, verwenden Sie diesmal jedoch die LEFT JOIN-Klausel.
Mit diesem Befehl wird die linke Tabelle (tourneys) zurückgegeben, auch wenn in der rechten Tabelle kein entsprechender Datensatz enthalten ist. Wenn es keinen passenden Datensatz in der rechten Tabelle gibt, wird er als leeren Wert oder ‘NULL zurückgegeben:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
LEFT JOIN dinners ON tourneys.name=dinners.name ;

Wenn Sie nun die Abfrage mit der RIGHT JOIN-Klausel ausführen, werden alle Datensätze der rechten Tabelle zurückgegeben. Das Geburtsdatum von Pauline ist in der rechten Tabelle aufgezeichnet, hat allerdings in der linken Tabelle keine entsprechende Zeile. Somit werden in den Spalten name und size leere Werte in dieser Zeile zurückgegeben:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
RIGHT JOIN dinners ON tourneys.name=dinners.name ;

Left- und Right-Joins können auch als LEFT OUTER JOIN oder RIGHT OUTER JOIN geschrieben werden, obwohl der OUTER-Teil der Klausel impliziert ist. Ebenso führt INNER JOIN zum gleichen Ergebnis wie JOIN.

Für PostgreSQL steht weiterhin eine vierte JOIN-Klausel, FULL JOIN, zur Verfügung. Ein FULL JOIN gibt alle Datensätze, einschließlich aller Nullwerte, zurück:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
FULL JOIN dinners ON tourneys.name=dinners.name ;

Alternativ zur FULL JOIN-Abfrage kann die UNION-Klausel verwendet werden.

Der UNION-Operator unterscheidet sich von der JOIN-Klausel darin, dass UNION die Ergebnisse zweier SELECT-Anweisungen in einer einzigen Spalte kombiniert. Hierbei werden alle doppelten Einträge entfernt:

SELECT name FROM tourneys UNION SELECT name FROM dinners ;

Wollen Sie allerdings, dass alle Einträge zurückgegeben werden, verwenden Sie den Operator UNION ALL:

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners ;

Die Namen und die Anzahl der Spalten, die von der ersten SELECT-Anweisung abgefragt wurden, werden als Namen und Anzahl in den Spalten der Ergebnistabelle zurückgegeben. Bei der Verwendung von UNION ist allerdings zu beachten, dass die entsprechenden Spalten ähnliche Datentypen und die gleiche Reihenfolge haben müssen. Wenn dies nicht der Fall ist und Sie etwa eine unterschiedliche Anzahl von Spalten abfragen, kann es passieren, dass Sie folgende Fehlermeldung bekommen:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys ;

Eine alternative Option zur Abfrage mehrerer Tabellen ist die Verwendung von subqueries. Unterabfragen (auch inner oder nested queries) sind Abfragen, die in einer anderen Abfrage enthalten sind. Dies kann nützlich sein, wenn Sie versuchen, die Ergebnisse einer Abfrage nach dem Ergebnis einer separaten Aggregatfunktion zu filtern.

In unserem folgenden Beispiel wollen wir wissen, wer mehr Spiele als Julia gewonnen hat. Sie könnten an dieser Stelle eine Abfrage ausführen, bei der zunächst zurückgegeben wird, wie viele Spiele Julia gewonnen hat und im nächsten Schritt eine weitere Abfrage ausführen, um herauszufinden, wer mehr Spiele gewonnen hat. Wir wollen dieses Ergebnis mit einer einzigen Abfrage erreichen:

SELECT name, wins FROM tourneys
WHERE wins > (
SELECT wins FROM tourneys WHERE name = 'Julia'
) ;

Unterabfragen können verwendet werden, um Ergebnisse aus mehreren Tabellen abzufragen.
Schauen wir uns hierzu eine Abfrage an, bei der uns die Freundin mit der besten Bowling-Bilanz und ihr jeweiliges Lieblingsgericht zurückgegeben wird:

SELECT name, entree, side, dessert
FROM dinners
WHERE name = (SELECT name FROM tourneys
WHERE wins = (SELECT MAX(wins) FROM tourneys));

Fazit

Wenn Sie mit Datenbanken arbeiten, werden Sie schnell feststellen, dass das Ausgeben von Abfragen eine der am häufigsten ausgeführten Aufgaben im Bereich der Datenbankverwaltung ist. Hierzu gibt es eine Reihe von Datenbankverwaltungstools wie pgAdmin, allerdings ist die Ausgabe von SELECT-Anweisungen über die Befehlszeile jedoch nach wie vor ein weit verbreiteter Arbeitsablauf, der Ihnen auch eine bessere Kontrolle bieten kann.