Datenbanken mit  SQL Einführung in die relationale Algebra PostgreSQL, MariaDB und SQLite V 21.07.30

 

 

Einführung

Begriffe

Daten

Digitalisierte, formalisierte und kodierte Informationen, welche durch Interpretieren in einem Bedeutungskontext wiedergewonnen werden können. Mittels Verknüpfungen zwischen Daten kann zusätzliches Wissen geschöpft werden.

Metadaten

Strukturierte Daten, welche Informationen über Merkmale anderer Daten enthalten.

Datensatz

Gruppe von inhaltlich zusammenhängenden Daten (Datenfeldern).

Persistenz

Fähigkeit, Daten oder logische Verbindungen über lange Zeit – insbesondere über einen Programmabbruch hinaus – bereitzuhalten. Zum Erhalt der ~ wird ein nichtflüchtiges Speichermedium benötigt; wobei auch Dateisysteme sowie durch Protokolle gesicherte bidirektionale und transaktionsorientierte Datenübertragungen als nichtflüchtige Medien betrachtet werden können.

Redundanz

Eine Informationseinheit ist dann redundant, wenn diese ohne Informationsverlust weggelassen werden kann. Durch das Vermeiden redundanter Information ist die Eindeutigkeit (Konsistenz) der Datensätze gewährleistet. Jedoch können bewusst eingeführte Redundanzen, um weniger Tabellen miteinander verknüpfen zu müssen (Kartesisches Produkt), zu sichtlich schnelleren Abfragen führen.

Konsistenz

Widerspruchsfreiheit von Daten.

Inkonsistenz einer DB kann entweder durch fehlerhafte Dateneingaben bzw Transaktionen, oder aufgrund unplanmäßiger Redundanzen entstehen.

Datenbank

Formalisierte, strukturierte und kodierte Darstellung logisch zusammenhängender Daten.

Sammlung von Daten und zugehörigen Beschreibungsinformationen (Metadaten), welche einen Ausschnitt aus der realen Welt (Miniwelt) beschreiben, in einem logischen Zusammenhang stehen und persistent sowie formal strukturiert sind. ~ werden für einen bestimmten Zweck entworfen und implementiert.

Datenbankverwaltung (Datenbankmanagementsystem – DBMS)

Sammlung von anwendungsunabhängigen Programmen (Softwaresystem) zum Erstellen und Verwalten einer Datenbank. Darüber hinaus werden Datenverwaltungssysteme genutzt, um durch Verknüpfen von Daten neues Wissen zu erschließen.

Die ~ muss ein effizientes, zuverlässiges, aber auch sicheres System zum Speichern von Datenbeständen und den darauf – zumeist parallel – stattfindenden Zugriffen bieten.

Die ~ muss Mechanismen zur Datenintegrität (Konsistenz), zum Schutz vor unerwünschten Zugriffen (Authentifizierung / Autorisierung), sowie vorbeugend gegen Datenverlust und Inkonsistenz bei Fehlern (Ausfallsicherheit) bereithalten.

Referenzielle Integrität

Beziehungen zwischen den Tabellen, verwirklicht über Primär- und Fremdschlüssel, um die Konsistenz und Integrität der Datenbank sicherzustellen.

Konkret wird die ~ mittels Regeln – entweder in Form von Constraints oder Triggers – gewährleistet, welche die Bedingungen vorgeben, unter denen ein Datensatz eingefügt, verändert oder gelöscht werden darf.

Transaktion – Durchführung

Folge von Operationen auf einen Datenbestand, welche nach fehlerfreier und vollständiger Ausführung einen konsistenten Datenbankzustand hinterlassen.

Datenarten

Strukturierte Daten

Datensatz ist begründet auf einer Definition der einzelnen Datenfelder (formalisierte Struktur – Datenmodell); zB Arbeitsblätter, Tabellen.

Halbstrukturierte Daten

Enthalten implizite Strukturinformationen; zB CSV, XML, Graphen.

Unstrukturierte Daten

Ohne bewusst geschaffene Struktur; zB Texte, Bilder, Videos

Coddsche Regeln

Das relationale Datenbankmodell beruht auf dem mathematischen Konzept der Relation und wurde von dem Mathematiker Edgar F. Codd entwickelt. Alle relevanten Informationen einer Datenbank werden darin in Relationen abgelegt. Edgar F. Codd definierte neun Anforderungen, die sog Coddschen Regeln, welche für einen zuverlässigen und effizienten Datenbankbetrieb notwendig sowie hinreichend sind:

1. Integration

   Daten müssen in einer einheitlichen Struktur ohne Redundanz abgelegt werden.

2. Operationen

   In einer Datenbank müssen Daten gespeichert, geändert und gesucht werden können.

3. Katalog

   Über eine spezielle Struktur (Verzeichnis) müssen Informationen bereitgestellt werden, welche die Daten näher beschreiben (Metadaten).

4. Benutzersichten

   Unterschiedliche Anwendungen erfordern unterschiedliche Sichten auf den Datenbestand.

5. Integritätssicherung

   Die Korrektheit des Datenbankinhalts muss durch Regeln gewährleistet werden, welche jeder Datenbankeintrag zu erfüllen hat.

6. Datenschutz

   Nur berechtigte Benutzer und Programme dürfen Zugriff auf die Datenbank haben.

7. Transaktionskontrolle

   Mehrere Anweisungen sind zu einer einzigen Transaktion zu bündeln, damit diese als eine funktionale Einheit vollständig und fehlerfrei ausgeführt werden können.

8. Synchronisation

   Parallel ausgeführte Transaktionen müssen den gleichen Datenbankzustand hervorrufen wie irgendeine serielle Ausführung der Transaktionen.

9. Datensicherung

   Die Datenbankverwaltung muss nach einem Systemfehler in der Lage sein, den letzten konsistenten Datenbankzustand mittels automatischer Verfahren wiederherzustellen.

 

Konsistenzmodell: AKID (en ACID)

Eigenschaften von Transaktionen

Atomarität – Abgeschlossenheit (en Atomicity)

Eine Transaktion ist eine Folge von Datenbank-Operationen, welche entweder vollständig und fehlerfrei als Ganzes oder gar nicht auszuführen sind. Sollte eine Transaktion nicht vollständig abschließbar sein, erfolgt ein Zurücknehmen aller ausgeführten Anweisungen bis zum ursprünglich gültigen Zustand (en roll back – Zurückrollen).

Konsistenzerhaltung (en Consistency)

Eine Transaktion muss nach Abschluss einen widerspruchsfreien Datenbank­zustand hint­er­lassen, indem alle Integritäts­be­dingungen eingehalten sind. Ist das nicht möglich, oder tritt ein Fehler auf, wird die gesamte Transaktion zurückgenommen und der ursprünglich Zustand wiederhergestellt.

Isolation – Abgrenzung

Die Datenbankverwaltung muss gewährleisten, dass sich Transaktionen verschiedener Anwender oder Prozesse nicht gegenseitig beeinflussen. In der Regel werden benötigte Daten für eine Transaktion vor­über­geh­end gesperrt, damit andere nebenläufige Transaktionen keinen Einfluss ausüben können.

Dauerhaftigkeit (en Durability)

Nach Abschluss einer Transaktion liegen die Daten dauerhaft gespeichert vor. Auch darf nach einem Systemfehler – bspw bei Ausfall des Hauptspeichers – kein Datenverlust auftreten. Dauerhaftigkeit kann durch das Schreiben eines Transaktionslogs sichergestellt werden, um nach einem Systemausfall fehlende oder unvollständige Operationen nachzuholen.

Sperrverfahren schränken die Nebenläufigkeit ein und begünstigen Blockierungen, weshalb in vielen Datenbanksystemen das verwendete Isolationsverfahren einstellbar ist, um bestimmte eigentlich unerwünschte Effekte für eine höhere Nebenläufigkeit zuzulassen.

Verarbeitungsarten

Datenbanken arbeiten entweder transaktional (überweisenden) oder analytisch (untersuchend).

Online Transaction Processing Data Base / OLTP-DB

Online-Transaktionsverarbeitung / Echtzeit-Transaktionsverarbeitung

Benutzungsparadigma von Datenbanksystemen und Geschäftsanwendungen, bei dem die Verarbeitung von Transaktionen unmittelbar sowie prompt, also ohne nennenswerte Zeitverzögerung, stattfindet. Gegenstück ist die Stapelerarbeitung, bei welcher alle Geschäftsvorfälle gesammelt und zu einem späteren Zeitpunkt durchlaufen werden. Neben dem schnellen Verarbeiten kurzer Abfragen liegt das Hauptaugenmerk auf der Wahrung von Datenintegrität in Umgebungen mit mehreren – zumeist parallelen – Zugriffen, sowie auf eine an der Anzahl der Transaktionen pro Sekunde gemessenen Effektivität. Demnach speichern OLTP-Datenbanken insbesondere detaillierte und aktuelle Daten nach dem Entitätsmodell (in der Regel 3NF).

Architektur und Arbeitsweise ist darauf ausgelegt, je Sekunde tausende Operationen (Einfügen, Ändern, Löschen von Daten) umzusetzen.

Online Analytical Processing / OLAP-DB

analytische Datenverarbeitung / Echtzeitdatenanalyse

Benutzungsparadigma zum Speichern von aggregierten historischen Daten, auf welche zumeist nur lesend zugegriffen wird. OLAP-Datenbanken zeichnet sich durch ein verhältnismäßig geringes Transaktionsvolumen aus und kommen vor allem im Bereich der Datenförderung (en data mining) zum Einsatz, um insbesondere komplexe Anfragen (Aggregate, Verknüpfungen, analytische Funktionen, statistische Berechnungen) in Sekunden bzw Minuten zu realisieren.

Im Gegensatz zu OLTP-Datenbanken steht die Durchführung komplexer Analysevorhaben im Vordergrund, welche ein sehr hohes Datenaufkommen verursachen. Das Ziel ist, durch multidimensionale Betrachtung dieser Daten (OLAP-Würfel) ein entscheidungsunterstützendes Analyseergebnis zu gewinnen.

Gegenüberstellung von OLTP und OLAP

Merkmal	OLTP	OLAP
Grundschema	transaktionsorientiert	analyse- / themenorientiert
Zweck	operative Systeme (Administrations- und Dispositionssysteme)	Data-Warehouse-Systeme
Nutzer	Administration, Sachbearbeiter	Entscheidungs- / Führungskräfte
Datenstruktur	zweidimensional, anwendungsbezogen	multidimensional, subjektbezogen
Dateninhalt	detaillierte und unverdichtete Einzeldaten	verdichtete und abgeleitete Daten
Zweck	transaktionale Konsistenzerhaltung	zeitbasierte Versionierung
Datenaktualität	aktuelle Geschäftsdaten	historische Verlaufsdaten
Aktualisierung	durch laufende Geschäftsvorfälle	periodische Datenaktualisierung (Schnappschuss)
Zugriffsform	lesend / schreibend / löschend	lesend / verdichtend
Zugriffsmuster	vorhersehbar, repetitiv	ad hoc, heuristisch
Zugriffshäufigkeit	hoch	mittel bis niedrig
Transaktionsdauer	kurze Lese- / Schreiboperationen	lange Lesetransaktionen

Drei-Schichten-Architektur

Die ~, auch Drei-Schema-Architektur oder Drei-Ebenen-Architektur genannt, beschreibt die grundlegende Trennung verschiedener Bereiche einer Datenbank mit dem Ziel, den Benutzer einer Datenbank vor nachteiligen Auswirkungen bei Änderungen in der Datenbankstruktur zu schützen.

Jede Schicht verfolgt einen bestimmten Zweck, um die zu speichernden Daten entgegenzunehmen, strukturiert abzulegen und dem Anwender oder dem Anwendungssystems wieder zur Verfügung zu stellen.

Für den Informationsaustausch zwischen den Schichten werden Transformationsregeln definiert. Dieser Aufbau ist entscheidend für das Einhalten der Anforderungen eines Datenbanksystems.

Externe Ebene

Für Benutzer und Anwendungen individuell bereitgestellte Sichten auf den Datenbestand wie Formulare, Suchmasken, Listen, aber auch Programmierschnittstellen.

Konzeptionelle Ebene

Beschreibt, welche Daten von der Datenbank erfasst werden, sowie deren Beziehungen zueinander (Nachbilden von Realwelt-Zusammenhängen). Entwurfsziel ist eine vollständige und redundanzfreie Darstellung aller zu speichernden Informationen, einhergehend mit einer Normalisierung des relationalen Datenbankschemas.

Interne Ebene

Auch physische Ebene, welche die Speicherung der Daten und Zugriffspfade umfasst. Entwurfsziel ist ein effizienter Zugriff auf die gespeicherten Informationen.

Die Vorteile des Drei-Ebenen-Modells liegen in der

• physischen Datenunabhängigkeit, da die interne von der konzeptionellen und externen Ebene getrennt ist. Physische Änderungen, zB des Speichermediums oder des Datenbankprodukts, wirken sich nicht auf die konzeptionelle oder externe Ebene aus.
• logischen Datenunabhängigkeit, da die konzeptionelle und die externe Ebene getrennt sind, wodurch Änderungen an der Datenbankstruktur (konzeptionelle Ebene) keinen Einfluss auf die externe Ebene ausüben.

Relationenalgebra

Die Relationenalgebra – auch relationale Algebra – bildet das mathematisch-theoretische Fundament von Abfragesprachen wie SQL. Das Ziel ist eine Logik, in welcher sich Abfragen formulieren lassen, ohne die Art und Weise vorgeben zu müssen (→ deklarative Sprache).

Operationen

Gegenstand der relationalen Algebra sind auf Relationen angewandte Operationen wie das Filtern, Verknüpfen und Aggregieren, wobei im Ergebnisse neue Relationen entstehen.

Anmerkungen

• Eine Algebra ist eine Menge zusammen mit Operationen auf dieser Menge.

• Eine Relation R ist eine Menge von n-Tupeln (→ Teilmenge des kartesischen Produkts):

  $$A⨯B⨯\dots =\{(a,b,\dots )|a\in A,b\in B,\dots \}$$

Selektion

Die Operation σ_X wählt jene Teilmenge einer Relation aus, welche die Bedingung X erfüllt:

Projektion

Die Projektion π entfernt Attribute (Spalten) aus der Eingaberelation:

Der Mengencharakter hat zur folge, dass gleiche Tupeln nicht vorkommen:

Auch sind Umbenennungen des Tabellenkopfs ausdrückbar:

Kartesisches Produkt

Das kartesische Produkt A × B zweier Mengen A und B ist definiert als die Menge aller geordneten Paare (a , b), mit a als Element aus A und b als Element aus B:

Verbund / Join

Ein Join ⋈ ist die Kombination aus kartesischem Produkt und Selektion:

Der Verbund kombiniert Tupel, wobei Datensätze ohne Verbundpartner entfernt werden.

SQL

Zweck	Abfragesprache
Grundparadigma	deklarativ / logisch
Nebenparadigma	prozedural
Syntax	ALGOL-artig
Speicherverwaltung	automatisch
Typsystem	stark, statisch
Übersetzungsmodell	interpretierend
Dateiendungen	SQL
Ersterscheinungsjahr	1974
Erfinder	IBM: Donald D. Chamberlin, Raymond F. Boyce
Herausgeber	ISO/IEC
Vorläufer	Prolog > Datalog
Abkömmlinge	LINQ, OQL, …
Standardisierungen	ANSI / FIPS: SQL-{86, 89, 92} ISO: SQL {1999, 2003, 2006, 2008, 2011, 2016, 2019}
freie Umsetzungen	mariaDB, MySQL, PostgreSQL, SQLite
proprietäre —	IBM Db2, MS SQL Server, Oracle
freie DBMS	DBeaver, Emma, Firebird, MySQL Workbench, phpMyAdmin
proprietäre —	IBM Db2, Microsoft SQL Server, Oracle Database Server

Die nachfolgende Zusammenfassung von SQL – ausgeschrieben Structured Query Language – beschränkt sich auf die Implementierungen PostgreSQL, MySQL bzw. MariaDB sowie SQLite. Da aber PostgreSQL deutlich über die zuletzt genannten Datenbanksysteme hinausgeht, ist dem ein eigenes Kapitel gewidmet.

Teilsprachen

• Data Query Language (DQL)

  Abfragen und Aufbereitung der gesuchten Informationen

  select … from … where … group by … having … order by …

• Data Manipulation Language (DML)

  Ändern, Einfügen und Löschen von Datensätzen

  insert • update • delete • merge • call • explain plan • lock table

• Data Definition Language (DDL)

  Erzeugen, Ändern und Löschen von Tabellen und Schlüsseln (→ Datenbankschemen)

  create • alter • drop • truncate • rename

• Data Control Language (DCL)

  Rechteverwaltung

  grant • revoke

• Transaction Control Language (TCL)

  Transaktionskontrolle

  commit • rollback • savepoint • set transaction

Die Data Query Language wird häufig auch als Teil der DML gezählt.

Literale & Datentypen

SQL kennt vier Arten von literalen Repräsentationen:

character string	'Hello, World!'
bit string	b'1011000' x'5FA7CA1'
exact numeric	58 +31 3.14159 -3234
hexadecimal numeric	0x2AB85F
approximate numeric	6E6 +0.725E 36.86E-13 -323E-3

Manche Datenbanken erlauben auch doppelte Anführungszeichen. Von deren Gebrauch ist aber abzuraten, um möglichst kompatibel zum Standard zu bleiben.

MySQL / MariaDB

Bezüglich der Datentypen sind MySQL und MariaDB noch weitestgehend kompatibel zueinander.

Numerische Datentypen

tinyint* / int1*	[-128, 127]
boolean	{0, false, 1 true}
smallint* / int2*	[-32768, 32768]
mediumint* / int3*	[-8388608, 8388607]
integer* / int* / int4*	[-2147483648, 2147483647]
bigint* / int8*	[-1 · 263, 263 – 1]
decimal(N[,DECIMALS])*	präzise Festkommazahl
float(N[,DECIMALS])*	Gleitkommazahl mit einfacherer Genauigkeit
real / double(N[,DECIMALS])*	— doppelte Genauigkeit
bit	Bitfeld

Für decimal sind noch die Synonyme dec, numeric und fixed bekannt.

Alle mit einem Sternchen * gekennzeichneten Zahlentypen sind zusätzlich spezifizierbar:

DATATYPE [signed | unsigned | zerofill]

DATATYPE [unsigned zerofill | zerofill unsigned]

Zeichentypen

char(n)	fixierte Länge im Bereich [0, 255] → rechtsseitig mit Leerzeichen aufgefüllt
varchar(n)	variabel mit [0, 65532] als mögliche Obergrenzen
tiny text	maximal 255
text	maximal 65535
mediumtext	maximal 16777215
json / longtext	maximal 4294967295 / 4GB
inet6	IPv6 Adressen
enum('VALUE', …)	beschränkt Auswahl auf einen der vorgegebenen Werte maximal 65535 Vorgaben zulässig
set('VALUE', …)	erlaubt Teilmenge der aufgelisteten Werte, einschließlich '' Trennung von mehreren Werten mittels Komma , maximal 64 Werte zulässig
tinyblob	binäres Objekt mit maximal 255 Bytes
blob	binäres Objekt mit maximal 65535 Bytes
mediumblob	binäres Objekt mit maximal 16777215 Bytes
longblob	binäres Objekt mit maximal 4294967295 Bytes / 4GB

SQL unterscheidet nicht zwischen Zeichenketten und einem einzelnen Symbol. Folglich darf char nicht mit dem gleichnamigen numerischen Datentyp aus C oder anderen Sprachen verwechselt werden.

Bei Mengen ist zu beachten, dass deren Werte kommagetrennt in einer Zeichenkette übergeben und angezeigt werden (einschließlich Leerzeichen), wobei die Reihenfolge der zulässigen Elemente – festgehalten in der Typdefinition – stets eingehalten wird:

mysql> create table myset (col set('a', 'b', 'c', 'd'));
mysql> insert into myset (col) values
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
mysql> select col from myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+

Empfehlung

• Wenn die Länge völlig unbekannt ist und kein Richtwert besteht, verwende text. 
• Wenn die Länge variabel bleiben soll, aber bekannt ist, von wie vielen Zeichen höchstens ausgegangen werden darf, verwende varchar.
• Wenn Text stets in exakt gleicher Größe vorkommt, greife auf char zurück. 

Datum- und Zeitangaben

date	YYYY-MM-DD
time	HH-MM-SS[.PRECISION]
datetime	YYYY-MM-DD HH-MM-SS[.PRECISION]
timestamp	YYYY-MM-DD HH-MM-SS[.PRECISION] + Zeitzone
year	YYYY

Im Gegensatz zu datetime erfasst timestamp automatisch den Zeitpunkt des Tabelleneintrags.

SQLite

Im Gegensatz zu anderen Implementierungen weist SQLite keine strenge, statische Typisierung vor. Stattdessen gehören Daten einem der folgenden Speicherklassen (en storage classes) an:

null	ungültiger Wert
integer	vorzeichenbehaftete Ganzzahl
real	Gleitkommazahl
text	Zeichenkette
blob	Binärobjekt

Speicherklassen verallgemeinern gleichartige Datentypen. So werden Zahlen im Format des passenden bitmäßig beschränkten Typen abgelegt, aber beim Verarbeiten als eine vorzeichenbehaftete 64-Bit Ganzzahl gelesen.

Abfragen

Eine Abfrage umfasst zumindest eine Auswahl von Spalten oder einen verarbeitbaren Ausdruck, ggf ergänzt um zusätzlich Klauseln:

xxxxxxxxxx
select COLUMN/EXPRESSION
    -- Auflistung aller benötigter Spalten oder Ausdrücke wie Berechnungen
    -- Umsetzung der Operation PROJEKTION
    
from TABLENAME/VIEV [as ALIAS]
    -- alle benötigten Tabellen oder Sichten
    
-- wahlfreie / zusätzliche Befehle:
where CONDITION
    -- Bedingungen zur Auswahl bestimmter Zeilen
    -- Umsetzung der Operation RESTRIKTION
group by COLUMN/EXPRESSION
    -- Gruppierung (Aggregation) 
    -- Spalten oder Ausdrücke, welche zusammengefasst werden sollen
having GROUPCONDITION
    -- Bedingungen zur Auswahl bestimmter Gruppen
    -- nur bei vorheriger Gruppierung sinnvoll
order by COLUMN/EXPRESSION;
    -- Spalten / Ausdrücke, nach denen sortiert werden soll
    -- Semikolon ist unverzichtbar als Befehlsabschluss

Aliase und Redundanzen

​x
select * from …
    -- Auswahl aller Tabellenspalten nach deren Reihenfolge

select COLUMN as ALIAS from …
    -- Aliasnamen

select distinct … from …
    -- Unterdrücken von Mehrfachnennungen in der Ausgabe

/*
    Beispiel: Tabelle „Baukasten“:
    Nr | Modell | Preis
    1  | A      | 64.99
    2  | B      | 35.49
    4  | A      | 86.99 → Redundanz
    5  | D      | 14.49
*/

select distinct Modell from Baukasten
    -- Modell„A“ wird nur einmal angezeigt

Aggregatfunktionen

Aggregatfunktionen f(COLUMN) sind deterministische Funktionen, welche aus einer Menge von Spaltenwerten einen neuen Einzelwert berechnen. Zusammen mit group by COLUMN ist eine Aggregatfunktion auf jede Gruppe von Datensätzen anwendbar.

Bedingungen in Abhängigkeit von Aggregatfunktionen können nicht in der WHERE-Klausel stehen, sondern müssen im HAVING-Block erfolgen.

Mit Ausnahme von count() ignorieren Aggregatfunktionen nichtige Werte (NULL).

	Erläuterung
avg	Durchschnittswert einer numerischen Spalte
count	Anzahl aller ausgewählten Datensätzen, deren Wert nicht NULL ist
max	höchster Wert
min	niedrigster Wert
sum	Summe einer numerischen Spalte

Unterabfragen

en subquery

Unterabfragen sind innere Abfragen (Verschachtelungen), welche in den Klauseln select, from und where erscheinen können, sowie in den Anweisungen select, insert, delete und update.

xxxxxxxxxx
select … from … where EXPR OPERATOR (select … from …)

Eine Unterabfrage dient dem Zweck, Daten zurückzugeben, welche in der Hauptabfrage zum Formulieren von Bedingungen benötigt werden.

Weitere Anmerkungen zu Unterabfragen

• Unterabfragen müssen von Rundklammern umschlossen sein.
• Auf order by kann innerhalb einer Abfrage nicht zurückgegriffen werden.
• Rückgabe von mehreren Zeilen (Ergebnissen) können nur zusammen mit Operatoren wie in verwendet werden, welche eine ganze Menge von Werten verarbeiten.
• Die SELECT-Liste kann keine Referenzen zu Werten umfassen, welche als BLOB, ARRAY, CLOB oder NCLOB ausgewertet werden.
• Unterabfragen können nicht unmittelbar von einer Mengenfunktion umschlossen sein oder mit dem Operator between verwendet werden.

Korrelierte Unterabfrage

en correlated subquery

Unterabfrage, welche auf Werte einer äußeren Abfrage zurückgreift:

xxxxxxxxxx
select * from T1 where
    EXPR OP (select * from T2 where T1.COLUMN = T2.COLUMN)

Derartige Verschachtelungen sollten vermieden werden, da die Unterabfrage jeweils für jeden Datensatz der äußeren Abfrage ausgewertet wird und damit die Geschwindigkeit übermäßig beeinträchtigt.

Abgleichen und Sortieren

Muster

xxxxxxxxxx
select * from TABLENAME where EXPR like PATTERN;

Muster (en pattern) sind Zeichenketten mit den folgenden Platzhaltern:

	Bedeutung	Beispiel
%	kein, ein oder beliebig viele Einzelzeichen	'a%n'
_	einzelnes Zeichen	'_n%'

Für komplexere Muster stehen in MySQL / Mariadb reguläre Ausdrücke bereit:

xxxxxxxxxx
EXPR regexp '^[A-ZÄÖÜ]+$'

Suchen in Mengen

Die Funktion find_in_set liefert die Position eines Elements zurück bzw. den tatsächlich gespeicherten Wert:

xxxxxxxxxx
select find_in_set('b', 'a,b,c,d') -- → 2

Zu beachten ist, dass alles zwischen den Kommas zum Namen gehören und mit abgeglichen wird, auch Leerzeichen!

Bei Spalten vom Typ einer Menge

Sortieren

xxxxxxxxxx
select * from TABLENAME order by EXPR [asc | desc]

Liegt keine Angabe zur Reihenfolge vor, wird asc (aufsteigend) angenommen.

Redundanzvermeidung

xxxxxxxxxx
select distinct COLUMN from TABLENAME

Mit distinct werden nur die ersten Datensätze des jeweiligen Spaltenwerts angezeigt.

Sichten

Eine Sicht (en view) ist eine unter einem eigenen Namen gespeicherte und wie eine gewöhnliche Tabelle verwendbare SQL-Abfrage (→ virtuelle Tabelle):

xxxxxxxxxx
create or replace view NAME as select …;

Sichten vereinfachen vor allem den Zugriff auf stark normalisierte Datenbanken.

Operatoren

	Symbol	Bedeutung
Arithmetik	+ - * / %	Plus, Minus, …, Modulo
Bitmanipulation	& | ^	UND, ODER, XODER
Vergleiche	= > < >= <= <>	gleich, größer, …, ungleich
Zuweisungen	+= -= /= %= &= ^= |=	Kombinierungen
Konkatenation	||	nur in SQLite und PostgreSQL

Logische Operatoren

Die logische Operatoren not, and und or (nicht ausschließendes Oder) liefern entweder true oder false zurück und werden in der WHERE oder HAVING-Klausel verwendet:

xxxxxxxxxx
select * from TABLENAME where EXPR and EXPR …;

in

xxxxxxxxxx
select COLUMN from TABLENAME where COLUMN in (VALUE1, VALUE2, …);
    -- das gleiche wie „ SPALTENWERT ist Element von {Wert1, Wert2, …}“

select COLUMN from TABLENAME where COLUMN in (select …);
    -- Unterabfrage

Kurzform für mehrfache ODER-Verknüpfungen

between

xxxxxxxxxx
select * from TABLENAME where EXPR between EXPR and EXPR;

Als Grenzen sind Ausdrücke wie Zahlen, Text oder Datumsangaben einsetzbar.

any /some

xxxxxxxxxx
select … from … where EXPR COMPARISON any (SUBQUERY)

gibt true zurück, wenn einer der Unterabfragewerte die Bedingung erfüllt.

Der Ausdruck … = any (…) ist gleichwertig zu … in (…).

all

xxxxxxxxxx
select … from … where EXPR COMPARISON all (SUBQUERY)

gibt true zurück, wenn alle Unterabfragewerte die Bedingung erfüllen

exists

xxxxxxxxxx
select … from TABLENAME where exists (select …)

gibt true zurück, wenn mindestens ein Datensatz in der Unterabfrage existiert

Mengenoperationen

Mit Mengenoperationen lassen sich die Ergebnisse von zwei oder mehr Abfragen bzw Ausdrücken zusammenfassen: EXPR OP EXPR

Operator	Ergebnis
union	Vereinigung
union all	Vereinigung zur Multimenge
intersect	Schnittmenge
except	Rest / Differenzmenge

Relationale Verbünde / Joins

en joins

Ein relationaler Verbund verknüpft mehrere Tabellen zu einer Ergebnistabelle entsprechend etwaiger Vorbedingungen.

Übersicht

Schlüsselwörter		Erläuterung
[cross] join	kartesische Produkt	Jeder Datensatz der einen Tabelle wird mit jedem Datensatz der anderen kombiniert.
[inner] join	innerer Verbund	Kombination von Datensätzen unter Verbundbedingungen wie Gleichheit – oder andere Vergleichsoperationen.
left [outer] join	linksseitiger Verbund	Umfasst alle Datensätze der linken Tabelle (links von join), auch wenn die rechte Tabelle keinen korrespondierenden Datensatz bietet; die fehlenden Werte werden mit null aufgefüllt.
right [outer] join	rechtsseitiger Verbund	Wie linksseitiger Verbund, nur mit dem Unterschied, dass die Datensätze der rechten Tabelle ggf. ergänzt werden.
full [outer] join	äußerer Vollverbund	Vereinigungsmenge der Ergebnisse des links- und rechtsseitigen Verbundes.

Syntax

x
select
    t1.c1, t2.c1, …
from
    t1 [left | right | full] join t2 on t1.c3 = t2.c4;

Innerer Verbund

Ein innerer Verbund kann auf unterschiedliche Weise geschehen:

Syntax
natural join	Natürlicher Verbund: Verknüpfung der Datensätze, deren Werte bei gleichem Attributnamen übereinstimmen.
join … using (COLUMNS)	Ausdrückliche Angabe der Attribute, über welche die Verknüpfung erfolgen soll.
join … on T1.C COMPARE T2.C	Verbund von Tabellen, bei denen die Bezeichnungen der zu vergleichenden Attribute nicht übereinstimmen oder bei dem ein anderer Operator als = verwendet wird.

Natürliche Verbünde sollten vermieden werden, da unter Umständen ungewollte bzw falsche Verknüpfungen entstehen.

Verbund mit sich selbst

Ein Self-Join ist ein Verbund, bei dem eine Tabelle ihre Zeilen mit sich selbst verknüpft, was vor allem dann der Fall ist, wenn ein Fremdschlüssel vorliegt, welcher auf den Primärschlüssel der eigenen Tabelle verweist.

Im Gegensatz zu den anderen Verbundarten muss immer mit Aliassen gearbeitet werden, um trotz des gleichen Tabellennamens unterscheiden zu können:

xxxxxxxxxx

Datenbankerstellung

Die wichtigsten Befehle zum Erstellen oder Verwerfen von Datenbankobjekten werden mit create bzw. drop eingeleitet:

xxxxxxxxxx
create database NAME; -- not in SQLite 

-- PostgreSQL
    create [global | local] table [if not exists] NAME.TABLENAME (
        COLUMN1 TYPE,
        COLUMN1 TYPE,
        …
    ) [inherits (T1, T2, …)];


-- MariaDB
    create [or replace] [temporary] table [if not exists] NAME.TABLENAME (
        COLUMN1 TYPE,
        COLUMN1 TYPE,
        …
    );


-- SQLite
    create table [if not exists] NAME.TABLENAME (
        COLUMN1 TYPE,
        COLUMN1 TYPE,
        …
    ) [without rowid];


drop table NAME.TABLENAME [cascade];

drop database NAME; -- not in SQLite 

Beschränkungen

en constraints

Beschränkungen werden vorgenommen, um einen bestimmten Wertebereich von Daten festzulegen. Dies stellt die Genauigkeit und Zuverlässigkeit der Daten in der Tabelle sicher. Wenn eine Verletzung zwischen der Einschränkung und einer Datenmanipulation vorliegt, wird die Aktion abgebrochen.

Einschränkungen können auf Spalten- oder Tabellenebene erfolgen. Einschränkungen auf Spaltenebene gelten für eine Spalte, während Einschränkungen auf Tabellenebene für die gesamte Tabelle verbindlich sind.

Folgende Einschränkungen werden häufig in SQL verwendet:

	stell sicher, dass
auto_increment	eine ganzzahlige Spalte autonom u. fortlaufend Schlüssel erzeugt
not null	eine Spalte keine NULL-Werte vorweist
unique	alle Werte in einer Spalte unterschiedlich (einzigartig) sind
primary key	jeder Datensatz (Zeile) einer Tabelle eindeutig identifiziert werden kann: Kombination aus not null und unique
foreign key	der Wert mit dem eines aus einer anderen Tabelle übereinstimmt: referenzierter Wert muss unique oder primary key sein
check	alle Werte in einer Spalte eine bestimmte Bedingung erfüllen
default	zumindest ein Standardwert für eine Spalte vorliegt
index	Datensätze besonders schnell abgerufen werden können

In Spalten mit auto_increment ist 0 als Primärschlüssel nicht zulässig und wird bei einem INSERT genauso wie NULL übergangen. Ferner darf eine Tabelle das Attribut auto_increment nur einmal vorweisen.

Die Beschränkung auto_increment ist nur in MySQL / MariaDB verfügbar. Jedoch erfüllt in PostgreSQL der Datentyp serial den gleichen Zweck.

Check-Constraints schlagen nicht fehl, wenn ein Wert null ist, sondern werten zu unbekannt aus, womit eine Zeile dennoch akzeptiert wird.

Syntax

Empfohlen wird, Einschränkungen stets einen innerhalb der Datenbank eindeutigen Namen mittels constraint NAME zu geben:

xxxxxxxxxx
create table T1 (
    C1 TYPE not null,
    C2 TYPE null,
    C3 TYPE not null default LITERAL,
    …
    constraint T1_PK primary key (C1),
    constraint T1_UQ unique (C2, C3),
    constraint T1_FK foreign key (C2) references T2(C),
    …
    constraint T1_CK check (…)
);

Gibt der Programmierer keinen Namen vor, autogeneriert die Datenbank bei Auftreten von Verletzungen eigene Bezeichnungen, welcher aber nicht verständlich sind. Zudem erleichtert die Benennung von Constraints ganz erheblich spätere Tabellenänderungen.

Namenskonvention

Constraint	Affix
Primary Key	PK
Alternate Key	AK
Foreign Key	FK
IndeX	IX
ChecK	CK
DeFault	DF
UniQue	UQ

Nachträgliches Ändern von Tabellen

Mit dem ALTER-Befehl lassen sich Tabellen nachträglich abändern:

xxxxxxxxxx
rename table t1 to t2;

alter table t change c1 c2 TYPE;
    -- Rename a column or change its definition, or both.

alter table t modify c TYPE;
    -- Change a column definition but not its name.

alter table t add column c TYPE;

alter table t add constraint x foreign key (c) references t2(c);

alter table t drop column c;

alter table category auto_increment = 1
    -- Reset counter for the automatic assignment of a primary key

Es sind beliebige Constraints nachträglich hinzufügbar. Eine praktische Anwendung sind wechselseitige (en mutual) Beziehungen zwischen Tabellen, wo erst nach Erstellung der zweiten Tabelle eine Referenz auf diese in der ersten hinzugefügt werden kann.

Einfügen, Ändern und Löschen von Daten

SQL umfasst drei fundamentale Befehle für die Manipulation von Datenbankeinträgen:

xxxxxxxxxx
-- Einfügen
insert into TABLENAME (COLUMN1, COLUMN2, COLUMN3, …) values
    (VALUE1, VALUE2, VALUE3, …),
    …
    (VALUE1, VALUE2, VALUE3, …);

-- Verändern
update TABLENAME set COLUMN1 = VALUE1, COLUMN2 = VALUE2, … where …;

-- Löschen
delete from TABLENAME where …;

Sollen alle Datensätze einer Tabelle gelöscht werden, bietet sich truncate an:

xxxxxxxxxx
truncate table TABLENAME;

Trigger

Ein Trigger ist eine gespeicherte Prozedur in der Datenbank, welche automatisch aufgerufen wird, wenn ein bestimmtes Ereignis (en event) wie insert, update oder delete auftritt.

MySQL / MariaDB

Für Trigger gelten die folgenden Einschränkungen.

• Bis MariaDB 10.2.3 kann eine Tabelle nur einen Trigger für jede Timing / Event-Kombination vorweisen: {before, after} ⨉ {insert, update, delete} ⨉ table
• Trigger werden immer für jede Zeile ausgeführt.
• Die Ergebnismenge kann nicht zurückgegeben werden (→ Prozedur).
• Der Trigger kann mit leave sofort beendet werden.

xxxxxxxxxx
create trigger NAME
    before insert on TABLENAME for each row
begin
    if new.COLUMN not like … then
        signal sqlstate '45000' set message_text = 'Value is not valid';
    end if;
end;

SQLite

SQLite unterstützt die folgenden Trigger:

• before insert
• after insert
• before update
• after update
• before delete
• after delete
• instead of insert
• instead of delete
• instead of update

PostgreSQL

 

Mit seinen zahlreichen Features und langjährigen Stabilität zeichnet sich PostgreSQL als ernstzunehmende sowie professionelle Alternative aus.

Datentypen

Im Gegensatz zu anderen relationalen Datenbanksystemen hält sich PostgreSQL streng an den Standard, sodass bestimmte Datentypen wie tinyint nicht existieren. Dafür trumpft PostgreSQL umso mehr mit einem deutlich leistungsstärkeren Typsystem auf, welches den Programmierer befähigt, eigene Datentypen zu definieren, sodass die Integrität der Datensätze nicht nur durch Relationen gewährleisten werden kann.

Offizielle Dokumentation: https://www.postgresql.org/docs/13/datatype.html

Die offizielle Dokumentation offenbart, dass PostgreSQL noch viele weitere Datentypen unterstützt, u. A. für geometrische Objekte, Netzwerkadressen und Dateiformate wie JSON und XML.

Umwandeln von Typen

PostgreSQL unterstützt den Standard-SQL-Typ boolean mit mehreren literalen Repräsentationen,

true	't'	'yes'	'on'	1
false	'f'	'no'	'off'	0

 

wobei ohne Kontxt und abgesehen von true bzw. false stets der Typ mit angegeben werden muss:

select 'on' :: boolean;

Der zweifache Doppelpunkt :: wird als Cast-Operator bezeichnet und stellt lediglich eine  Kurzschreibweise für cast(EXPR as TYPE) dar.

Zu Beachten ist, dass boolean bis zu drei Zustände abbilden kann: true, false und null.

Numerische Datentypen

Name	Byte	Wertebereich
smallint	2	[-32768, 32767]
integer / int	4	[-214748364, 2147483647]
bigint	8	[263, 263 – 1]
decimal / numeric	var	max. 131072 Vorkommastellen u. 16383 Nach—
money	8	[-92233720368547758.08, 92233720368547758.07]
real	4	Genauigkeit von 6 Nachkommastellen
double precision	8	Genauigkeit von 15 Nachkommastellen
smallserial	2	[1, 32767]
serial	4	[1, 2147483647]
bigserial	8	[1, 9223372036854775807]

Zwischen decimal und numeric besteht faktisch kein Unterschied. Ferner unterscheidet PostgreSQL nicht zwischen vorzeichenlose und vorzeichenbehaftete Ganzzahlen, da dies der SQL-Standard auch nicht verlangt.

Die seriellen Typen sind für künstliche Schlüssel vorgesehen, wobei serial gleichwertig zu integer NOT NULL DEFAULT nextval('table_name_id_seq') ist. Jedoch vorkonfiguriert PostgreSQL serielle Typen nicht automatisch als unique.

Zeichentypen

Name	Beschreibung
text	beliebig lang
character varying(n) / varchar(n)	variable Größe mit Maximum
character(n) / char(n)	fixiert und mit Leerzeichen aufgefüllt

Die Größe n beschreibt die Zahl an Einzelzeichen und nicht die Höchstgrenze in Byte.

Alle drei Zeichentypen sind intern als variable length array implementiert, was zur Folge hat, dass char – im Gegensatz zu anderen Datenbanksystemen – sogar weniger effizient ist, da aufgrund des Auffüllens mit Leerzeichen unnötig mehr Speicherplatz belegt wird.

Bedingt durch den Wegwahl von Extraprüfungen ist text am performantesten und zu bevorzugen.

Zu beachten ist, dass Indexe auf maximal 2712 Byte je Zeile beschränkt sind, sodass bei indexierten Textspalten ohne Höchstgrenzen – ob über varchar oder einen Constraint realisiert – die Gefahr von Fehlern besteht.

Datum- und Zeitangaben

Name	Byte	Beschreibung	Res.
timestamp [(p)]	8	Tag + Uhrzeit	1 µs
timestamp [(p)] with time zone	8	Tag + Uhrzeit + Zeitzone	1 µs
date	4	Tag	1 d
time [(p)]	8	Uhrzeit	1 µs
time [(p)] with time zone	12	Uhrzeit + Zeitzone	1 µs
interval [FIELDS] [(p)]	16	Zeitraum	1 µs

Über den Wert p lässt sich die Genauigkeit der Sekunden von 6 (voreingestellt) auf 0 reduzieren.

Der Intervalltyp hat eine zusätzliche Option, um mittels

• year / month / day / hour / minute / second
• year to month
• day to [hour | minute | second]
• hour to [minute | second]
• minute to second

die Menge der gespeicherten Felder einzuschränken.

Datums- und Zeitangaben werden in jeglichem gültigen Format akzeptiert:

• '1999-01-08'
• '1/8/1999'
• 'Jan-08-1999'
• …

Verarbeiten von Datumsangaben

Neben der dem SQL-Standard entsprechenden Syntax mittels extract stellt Postgres auch eine Funktion date_part() bereit, um einzelne Werte aus einer Datumsangabe herauszulösen:

xxxxxxxxxx
select date_part('week', now());

select extract (week from now());

/*
    other options:
        century decade year quarter month day dow doy
        hour minute second milliseconds microseconds epoch
*/

select date_part('week', date '2021-08-26');

select date_part('week', '2021-08-26'::date);

select extract (week from date '2021-08-26');

select extract (week from '2021-08-26'::date);

-- → 34

Tatsächlich wird im Hintergrund der standardkonforme Ausdruck nach date_part() übersetzt, sodass beide Varianten völlig gleichwertig sind.

Datenfelder / Arrays

In PostgreSQL kann eine Spalte nicht nur einelementig sein, sondern mehrere Werte enthalten, ohne die erste Normalform zu verletzten:

xxxxxxxxxx
create table t (id serial primary key, matrix int[][]);

insert into t (matrix) values
    ('{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'),
    ('{{9, 8, 7}, {6, 5, 4}, {3, 2, 1}}');

insert into t (matrix) values (array [[1, 2, 3], [4, 5, 6], [7, 8, 9]]);

select matrix[2][2] from t where id = 1

select matrix[2:3][1:2] from t where id = 2
    -- Access arbitrary slices of an array.

Darüber hinaus sind sogar mehrdimensionale Datenfelder definierbar, ohne die Länge im Voraus fixieren zu müssen.

Literale Datenfelder lassen sich auf zwei Arten angeben: Entweder als speziell formatierte Zeichenketten oder über die array constructor syntax, eingeleitet mit dem Schlüsselwort array und umschlossen von eckigen Klammern.

Ferner stehen zahlreiche Funktionen und Operatoren für die verarbeitung von Arrays bereit:

	Bedeutung	Beispiel	Ergebnis
=	gleich	[1.1]::int[] = [1]	t
<>	ungleich	[1,3] <> [1,4]	t
<	kleiner als	[1,2,3] < [1,4]	t
>	größer als	[1,4,3] > [1,2,4]	t
<=	kleiner als oder gleich	[1,2,3] <= [1,2,3]	t
>=	größer als oder gleich	[1,4,3] >= [1,4,3]	t
@>	enthält	[1,4,3] @> [3,1,3]	t
<@	ist enthalten in	[2,7] <@ [2,5,7]	t
&&	überlappend	[1,4,3] && [2,1]	t
||	Konkatenation	[1,2] || [[4,6]]	{{1,2},{4,6}}
||	einfügen in	3 || [4,5,6]	{3,4,5,6}
||	einfügen von	[4,5,6] || 7	{4,5,6,7}

Aus Platzgründen ist in den Beispielen das Schlüsselwort array weggelassen.

In der offiziellen Dokumentation sind noch zahlreiche vordefinierte Funktionen aufgelistet:

www.postgresql.org/docs/13/functions-array.html

Verarbeitung von Arrays

xxxxxxxxxx
do $$ declare
    x int[] = array [0, 1, 2, 3, 4, 5, 6];
    i int = 0;
begin
    for i in array_lower(x, 1) .. array_upper(x, 1) loop
        raise notice 'x[%] = %', i, x[i];
    end loop;

    raise notice '';
    
    x := x[:1] || 9 || x[2:];

    for i in array_lower(x, 1) .. array_upper(x, 1) loop
        raise notice 'x[%] = %', i, x[i];
    end loop;

    raise notice '';
end $$;

Aufzählungen / Enums

Aufzählungstypen geben einen geordneten Satz von zulässigen Zeichenketten vor:

xxxxxxxxxx
create type country_code as enum (
    'AT', 'BE', 'CH', 'CZ', 'DE', 'DK', 'FR', 'IT', 'LI', 'LU', 'NL', 'PL'
);

alter type country_code add value 'GB';
    -- Nachträgliches Hinzufügen weiterer Werte.
/*
alter type <enum name> add value <new value>;
    -- Appends to list.

alter type <enum name> add value <new value> before <old value>;

alter type <enum name> add value <new value> after <old value>;

alter type name rename value <old label> to <new label>;
*/

select enum_range(null::country_code);
    -- Ausgabe aller zulässigen Werte.

select 'DE'::country_code > 'AT'::country_code;
    -- Vergleichen nach der erfolgten Definitionsreihenfolge.

Die Groß- und Kleinschreibung ist relevant, sodass in diesem Beispiel 'de' nicht als gültiger Wert gilt. Zudem sind Enums typsicher. Folglich lassen sich Werte von unterschiedlichen Aufzählungen nicht miteinander vergleichen.

Trotz der Repräsentation als Zeichenkette beansprucht ein Spaltenwert stets nur 4 Byte.

Verbünde / Composite Types

Ein zusammengesetzter Typ bildet die Struktur einer Zeile bzw. eines Datensatzes ab:

xxxxxxxxxx
create type __street_address__ as (
    name varchar(50),
    house_nr int,
    house_nr_letter varchar(1),
    house_nr_fraction int,
    apartment_nr int
);

create domain street_address as __street_address__ check (
    (value).street is not null and (value).house_nr is not null
);

Beschränkungen wie not null bleiben nur Tabellenspalten vorbehalten. Jedoch kann mit domain ein Untertyp definiert werden, welcher derartige Prüfungen vornimmt.

Beim Zugriff auf einzelne Felder eines zusammengesetzten Wertes muss der Spaltenname in Rundklammern stehen, andernfalls geht PostgeSQL von einer Tabelle aus:

xxxxxxxxxx
create table address (street street_address not null, …);

select (street).name from address where …;

In gleicher Weise müssen Ausdrücke, welche einen zusammengesetzten Wert zum Ergebnis haben, von Rundklammern umschlossen sein:

select (f(PARAM)).field;

Darstellung zusammengesetzte Werte

Werte von zusammengesetzten Typen werden als Tupel dargestellt, entweder in Gestalt einer wohlformatierten Zeichenkette oder repräsentiert als eigenständiges literales Objekt:

xxxxxxxxxx
select '(Birkenweg,16,b,,)'::street_address
    -- Only missing values are interpreted as NULL!
    
select row ('Birkenweg', 16, 'b', null, null)::street_address
    -- More pleasant presentation of composite values.
    
select ('Birkenweg', 16, 'b', null, null)::street_address
    -- Or without a keyword, as long as there is more than one field.

Das Schlüsselwort row gestattet es, zusammengesetzte Werte (en composite values) außerhalb einer Zeichenkette als Tupel zu schreiben. Solange mehr als ein Feld vorliegt, kann auf das Schlüsselwort row sogar verzichtet werden.

Untertypen / Domain Types

Eine Domain in PostgreSQL ist ein benutzerdefinierter Datentyp, welcher auf einem anderen bestehenden Typen beruht:

xxxxxxxxxx
create domain booking_year as smallint check (
    value >= (date_part('year', current_date) - 6) and
    value <= (date_part('year', current_date) + 2)
);

select 2014::booking_year;

Sofern keine Einschränkungen definiert werden, verhält sich eine Domain wie die zugrunde liegende Datenart

Domains sind ein ausgezeichnetes Mittel, um die Datenkonsistenz auf systematische und tabellenübergreifende Weise zu gewährleisten.

Pseudotypen

Pseudotypen (en pseudo-types) abstrahieren über konkrete Datenarten von Parametern und Funktionsergebnissen hinweg und ermöglichen damit im beschränkten Umfang – aber auf typischere Weise – generische Programmierung.

Name	Datentyp verweist auf
any	… ein beliebiges Eingabeobjekt
anyelement	… ein beliebiges Objekt
anyarray	… ein beliebiges Datenfeld
anynonarray	… ein beliebiges Nichtdatenfeld
anyenum	… ein beliebiges Aufzählungsobjekt
anyrange	… einen beliebigen Bereich (en <range type)
anycompatible	… ein automatisch umwandelbares Objekt
anycompatiblearray	… ein automatisch umwandelbares Datenfeld
anycompatiblenonarray	… ein automatisch umwandelbares Nichtdatenfeld
anycompatiblerange	… einen automatisch umwandelbaren Bereich
record	… eine einzelne unspezifische Tabellenzeile
trigger	… eine Funktion mit einem Trigger als Rückgabe
event_trigger	… eine Funktion mit einem Event-Trigger als Rückgabe
void	… eine Funktion ohne Rückgabe
unknown	… eine noch unbekannte Objektart (keine Typauflösung)

WITH-Klausel

Bei komplexen Operationen bietet es sich an, Unterfragen getrennt zu behandeln:

xxxxxxxxxx
with
    a as (select …),
    b as (select …),
    …
select …

Solche Hilfsanweisungen mit with, auch als Common Table Expressions – CTE bezeichnet, stellen nichts anderes als temporäre Tabellen dar, welche nur für die darauffolgeden Datenbankoperation existieren (→ lokale Namensbindung).

Rollen und Rechteverwaltung

PostgresSQL verwaltet den Zugriff anhand von Rollen (en roles). Eine Rolle ist entweder ein einzelner Nutzer (en user) oder eine ganze Nutzerschaft. Demnach vereinigt PostgresSQL Nutzer und Gruppen, wie diese andere DB-Systeme voneinander unterscheiden, zu einem einzigen Konzept: Eine Rolle besitzt die Fähigkeiten sich einzuloggen, andere Rollen zu beerben, sowie Zugang zu ganz bestimmten Datenbankobjekten zu erhalten.

• Aus Kompatibilitätsgründen stellen create user und create group einen Alias für create role dar.
• Eine neu aufgesetzte PostgresSQL-Datenbank enthält immer die vordefinierte Rolle postgres, vergleichbar mit einem Superuser, der alles machen darf.
• Der Befehl drop role <name> ist nur ausführbar, wenn die zu löschende Rolle nicht Eigentümer von Objekten ist.
• Mit select rolname from pg_roles; sind alle bestehenden Rollen anzeigbar, wobei anzumerken ist, dass Rollen global, also Datenbank-übergreifend vorliegen.

Privilegien

auch role attributes

create role <name> <attribute> …;

Attribut	Nutzer
login	… darf sich über seinen Namen einloggen
superuser	… wird zum Superusers
createdb	… darf Datenbanken erstellen
createrole	… darf Rollen erstellen
replication	… darf Daten zu einem anderen Server kopieren
password '<string>'	… erhält ein eigenes Passwort

• Das Recht auf Replikation setzt voraus, dass sich ein Nutzer einloggen darf:

  alter role <name> replication login;

• Bei Verwendung von user anstelle von role gilt implizit login, sodass

  create user <name> with password 'Pa$$w0rd';

  völlig gleichwertig ist zu

  create role <name> with login password 'Pa$$w0rd';

• Mit dem Schlüsselwort revoke werden Privilegien entfernt.

Häufige Befehle

xxxxxxxxxx
grant connect on database DATABASE_NAME to USER_NAME;
revoke connect on database DATABASE_NAME from USER_NAME;

Mitgliedschaften

Zum leichteren Verwalten von Zugriffsberechtigungen können mehrere Rolle von ein und derselben Rolle erben, sodass diese nichts anderes als eine Nutzergruppe mit gemeinsamen Rechten darstellt. Die Befehle grant und revoke erteilen oder entfernen Mitgliedschaften:

grant <user group> to <user> [, …]

revoke <user group> from <user> [, …]

Eigentümerschaften

Jedes Datenbankobjekt weist immer einen Eigentümer (en owner) vor; in der Regel jene Rolle, welche das Objekt erstellt hat:

xxxxxxxxxx
dbname=# create role max;
CREATE ROLE
dbname=# create table x (id serial);
CREATE TABLE
dbname=# set role max;
SET
dbname=> create table y (id serial);
CREATE TABLE
dbname=> \d
            List of relations
 Schema |   Name   |   Type   |  Owner   
--------+----------+----------+----------
 public | x        | table    | postgres
 public | x_id_seq | sequence | postgres
 public | y        | table    | max
 public | y_id_seq | sequence | max
(4 rows)

Mit set role <name>; wird die gegenwärtig aktive Rolle gewechselt, sodass alle nachfolgend erstellten Datenbankobjekte dieser Rolle angehören. Die Eigentümerschaft kann auch nachträglich geändert werden:

alter table <name> owner to <role>;

Zugriffsberechtigungen

Auf ein Datenbankobjekt darf nur über eine Rolle zugegriffen werden, die zur Eigentümerschaft gehört. Jedoch lassen sich spezifische Zugriffsberechtigungen für Nichteigentümer definieren:

Art	Rolle erhält das Recht
select	… abzufragen
insert / update / delete / truncate	… Daten zu manipulieren
references	… Fremdschlüsselbeziehung zu erstellen

Zugriffsberechtigungen werden mit grant erteilt

grant [select, insert, … | all] on <table> to <role> [, <other role>, …];

und mit revoke aufgehoben,

revoke [select, insert, … | all] on <table> from <role> [, …];

ausgehend von der Rolle eines Eigentümers, wozu auch Superuser gehört.

Es ist auch möglich, über die spezielle Rolle public Berechtigungen an alle im System bestehenden Rollen zu erteilen:

grant … on … to public;

Zeilenschutz

Ergänzend zum Zugriffsberechtigungssystem von SQL führt PostgresSQL unter dem Begriff row security policies auch Sicherheitsrichtlinien auf Zeilenebene ein, um Tabellenoperationen zeilenweise zu beschränken.

Als Erstes muss der Zeilenschutz aktiviert werden:

alter table <table> enable row level security

Die Deaktivierung erfolgt mittels disable.

Durch das Aktivieren des Zeilenschutzes findet die Verweigerungsrichtlinie Anwendung, sodass auch Rollen, die eigentlich zugriffsberechtigt sind, keine Zeilen für Abfragen oder Manipulationen angezeigt bekommen. Jedoch erfasst die Verweigerungsrichtlinie nur zeilenweise Operationen; berechtigte Rollen können dennoch mittels truncate die Tabelle als Ganzes entleeren.

Ausgenommen vom Zeilenschutz sind der Eigentümer sowie die Rolle Superuser. Jedoch kann der Eigentümer mit force ebenfalls in das Sicherheitssystem einbezogen werden:

alter table <table> force row level security

Die entsprechende Deaktivierung erfolgt mittels no force.

Sicherheitsrichtlinien

Sicherheitsrichtlinien auf Zeilenebene werden mit create policy erstellt, mit alter policy verändert und mit drop policy gelöscht, vorausgesetzt der Zeilenschutz ist aktiviert, andernfalls sind die Befehle wirkungslos:

xxxxxxxxxx
create table bsp (id serial, pin smallint, created_by varchar(30));

alter table bsp enable row level security;

insert into bsp (pin, created_by) values
    (43, 'erik'), (43, 'erik'), (62, 'olaf'), (88, 'olaf')
    
create policy bsp_policy on bsp for all to public using (
    created_by = current_user
);

alter table bsp enable row level security;

Die Police (en policy) ist vergleichbar mit einem benannten Check-Constraint: Die angegebene Bedingung muss true zurückliefern, andernfalls besteht kein Zugriff auf den betreffenden Datensatz. Im weiteren Sinne filtert eine Police alle ein- und ausgehenden Daten wie eine Where-Klausel. 

Schemen

Die folgenden Datenbankobjekte bilden jeweils voneinander unabhängige Namensräume: 

• Schemen
• Tabellen und benutzerdefinierte Datentypen
• Trigger
• Funktionen und Prozeduren

Es ist ersichtlich, dass Namenskollisionen, bspw. zwischen Tabellen und benutzerdefinierten Datentypen, kaum vermeidbar sind. Abhilfe können Schemen schaffen, welche über einen eigenen Namensraum verfügen:

create schema <name>;

alter schema <name> owner to <role>;

drop schema <name> [cascade];

Der Zugriff auf Datenbankobjekte eines anderen Schemas erfolgt in gewohnter Punktnotation:

select * from <schema>.<table>;

Ist kein Schema spezifiziert, sucht PostgreSQL in jenen Schemen nach dem genannten Datenbankobjekt, welche in der Variable search_path aufgelistet sind, beginnend von links:

show search_path;

set search_path to <schema> [, <another schema>];

alter database <db> set search_path to …;

alter role <role> set search_path to …;

Rechte an Schemen

Als vollwertige Datenbankobjekte lassen sich Schemen für bestimmte Nutzer freigeben oder sperren:

grant usage on schema <name> to <role>;

grant all privileges on all tables in schema <nem> to <role>;

Systeminformationen

Funktion	Rückgabe	Beschreibung
current_catalog	name	name of current database (called "catalog" in the SQL standard)
current_database()	name	name of current database
current_query()	text	text of the currently executing query, as submitted by the client (might contain more than one statement)
current_role	name	equivalent to current_user
current_schema[()]	name	name of current schema
current_schemas(boolean)	name[]	names of schemas in search path, optionally including implicit schemas
current_user	name	user name of current execution context
inet_client_addr()	inet	address of the remote connection
inet_client_port()	int	port of the remote connection
inet_server_addr()	inet	address of the local connection
inet_server_port()	int	port of the local connection
pg_backend_pid()	int	Process ID of the server process attached to the current session
pg_column_size()	regtype	Size in bytes of the passed value
pg_listening_channels()	setof text	channel names that the session is currently listening on
pg_my_temp_schema()	oid	OID of session's temporary schema, or 0 if none
pg_trigger_depth()	int	current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)
pg_typeof()	regtype	Data type of the passed value
session_user	name	session user name
user	name	equivalent to current_user
version()	text	PostgreSQL version information

Anzahl an Einträgen in allen Tabellen

xxxxxxxxxx
select schemaname, relname, n_live_tup as estimatedcount
from pg_stat_user_tables
order by n_live_tup desc;

Prozedurale Erweiterungen

PL/pgSQL

PL/pgSQL – ausgeschrieben Procedural Language/PostgreSQL Structured Query Language – ist die prozedurale Sprache der objektrelationalen Datenbank PostgreSQL.

Variablen und Blöcke

PL/pgSQ ist eine strukturierte Sprache, wo jede Anweisung von einem Block umschlossen sein muss. Als Konsequenz sind Variablen – im Gegensatz zu anderen prozeduralen Erweiterungen – nicht wahllos deklarierbar, sondern dürfen nur im Kopf eines Anweisungsblocks bekannt gemacht werden:

xxxxxxxxxx
create table test (id serial, pin int);

do $$ <<inserting_block>>
    declare
        initial integer = 1;
        pin integer = 3;
    begin
       for x in initial .. 100 loop
            insert into test (pin) values (pin);
            pin := pin + x;
        end loop;
    end inserting_block; $$;

Das Schlüsselwort do kündigt einen anonymen Block an, welcher anders als Funktionsblöcke nicht über einen eigenen aufrufenden Namen verfügt (→ callable unit), sondern stattdessen an Ort und Stelle ausgeführt wird.

Blöcke sind beliebig ineiander verschachtelbar, einschließlich derer von Funktionen (→ closure).

Kontrollstrukturen

Verzweigungen

xxxxxxxxxx
-- If Statement
if … then … end if …
if … then … else … end if …
if … then … elsif … then … end if … -- or 'elseif'

-- Case Statement
case … when … then … else … end case
case when … then … else … end case

-- Conditional Expressions
case EXPR when … then … [when …] [else …] end
    -- Example
    select case 5 when 4 then 'four' when 3 then 'three' else 'dunno' end;
    
case when COND then … [when …] [else …] end;

Die folgenden Funktionen können als Kontrollstrukturen aufgefasst werden, da deren Argumente nur nach Bedarf ausgewertet werden (→ lazy evaluation):

xxxxxxxxxx
coalesce(VAL, …)
    -- Return first argument that is not null.
nullif(VAL1, VAL2)
    -- Return NULL if VAL1 equals VAL2; otherwise VAL1 is returned.
greatest(VAL, …)
    -- Return largest value from a list of expressions.
least(VAL, …)
    -- Return smallest value from a list of expressions.

Schleifen und Abbruchbedingungen

x
-- General Loop
[<<LABEL>>]
    loop
        …
        [continue | exit] [LABEL] [when COND]
        …
    end loop [LABEL];

-- While Statement
[<<LABEL>>]
    while COND loop
        …
    end loop [LABEL];
    
-- For Statement
[<<LABEL>>]
    for x in [reverse] EXPR .. EXPR [by EXPR] loop
        …
    end loop [LABEL];

PostgreSQL unterstützt sogar Mengenschleifen:

xxxxxxxxxx
-- Looping Through Query Results
[<<LABEL>>]
    for r in QUERY loop
        …
    end loop [LABEL];
    
-- Looping Through Arrays
[<<LABEL>>]
    foreach e [slice NUMBER] in array EXPR loop
        …
    end loop [LABEL];

Ausnahmebehandlungen

xxxxxxxxxx
-- Exception Handling
[<<LABEL>>]
    begin
        …
    exception
        when cond then
            …
        when …
    end [LABEL];

Funktionen

Funktionsblöcke werden in Form von Stringliteralen definiert:

xxxxxxxxxx
create function NAME([PARAM] TYPE, …) returns TYPE
    as 'FUNCTION BODY'
language plpgsql;

Um nicht weitere Hochkommas von Zeichenketten oder auch Namen innerhalb des Funktionsrumpfs maskieren zu müssen, besteht die Schreibweise mit doppeltem Dollarzeichen als alternatives Trennzeichen (→ dollar-quoted literal),

xxxxxxxxxx
create function NAME([PARAM] TYPE, …) returns TYPE as $$
    BODY
$$ language plpgsql;

was nicht zuletzt auch eine Syntaxhervorhebung in Texteditoren ermöglicht.

Anmerkung

In PostgreSQL sind Zeichenkettenliterale allgemein mit doppelten Dollarzeichen erlaubt:

select $$ Hallo Welt! $$​

Jedoch sollte davon kein Gebrauch gemacht werden.

Sprachauswahl

Die Sprachangabe lässt vermuten, dass nicht nur plpgsql zur Auswahl steht:

xxxxxxxxxx
create function test_trigger() returns trigger
    as '/usr/lib/postgresql/13/lib/trgr.so'
language c;

Die Schreibweise als Zeichenkettenliteral ist darauf zurückzuführen, dass Funktionsrümpfe in verschiedenen Sprachen definiert werden können. So ist beispielsweise Python über eine Erweiterung nachrüstbar. PL/pgSQL stellt lediglich die native und – abgesehen von direktem C – effektivste Lösung dar, wobei sich Funktionen auch in reinem SQL ohne prozedurale Erweiterung schreiben lassen:

xxxxxxxxxx
create function NAME([PARAM] TYPE, …) returns TYPE as $$
    select EXPR;
$$ language sql;

Als Fausregel gilt, reines SQL gegenüber PL/pgSQL zu bevorzugen, solange keine Variablen oder komplexe Kontrollstrukturen benötigt werden.

Parameter

Parameter müssen nicht zwangsweise einen Namen vorweisen:

xxxxxxxxxx
create or replace function custom_sum(int, int) returns int immutable
    language sql as $$ select $1 + $2; $$;

create or replace function custom_sum(int) returns int immutable
    language sql as $$ select 2 * $1; $$;

select custom_sum(3, 9);

select custom_sum(5);

PostgreSQL erlaubt das Überladen von Funktionen, solange sich deren Parameter in Anzahl oder Typ voneinander unterscheiden.

Zusammen mit Arrays sind sogar variadische Funktion definierbar:

xxxxxxxxxx
create or replace function custom_sum(variadic int[]) returns int
immutable as $$
declare 
    _sum int = 0;
    x int;
begin
    foreach x in array $1 loop
        _sum := _sum + x;
    end loop;
    return _sum;
end $$ language plpgsql;

select custom_sum(3, 9, 5, 3, 5);

Rückgabe anonymer Verbünde

Eine weitere Möglichkeit, den Rückgabetypen einer Funktion zu fixieren, besteht in der Festlegung von Ausgabeparametern:

xxxxxxxxxx
create or replace function sum_n_concat(
    in int, in int, out sum int, out concatenation text)
as $$
    select $1 + $2, $1::text || $2::text
$$ language sql;

select * from sum_n_concat(11, 42);
/*
    sum|concatenation|
    ---+-------------+
     53|1142         |
*/

select sum_n_concat(11, 42);
/*
    sum_n_concat|
    ------------+
    (53,1142)   |
*/

Der Vorteil gegenüber einer RETURNS-Klausel liegt darin, dass mehr als ein Wert gleichzeitig zurückgeliefert werden kann, ohne erst einen Verbundtypen mit create type … definieren zu müssen.

Bei fehlender Angabe eines Modifikators wird in implizit angenommen.

Volatilitätskategorien

Jede Funktion weist eine Volatilitätsklassifizierung (en function volatility categories) auf, womit nichts anderes als ein Versprechen an den Optimierer gemeint ist:

Modifikator	Versprechen
volatile	keine (voreingestellt)
stable	Funktion verändert keine bestehenden Datensätze
immutable	Funktionsergebnis hängt ausschließlich von den Parametern ab

Die Verantwortung über die Korrektheit einer solcher Angabe obliegt dem Programmierer. Als Faustregel gilt, immutable gegenüber stable, und stable gegenüber volatile zu bevorzugen. Jedoch sollten Funktionen nur dann als stable gekennzeichnet werden, wenn Daten lediglich mittels SELECT abgefragt werden, ohne diese zu manipulieren oder gar zu löschen. Der Modifikator immutable ist allenfalls bei reinen Funktionen (→ referentielle Transparenz) zu empfehlen.

Überladen von Operatoren

Bestehende Operatoren lassen sich überladen:

xxxxxxxxxx
create type calendar_week as (
    y smallint,
    week_number smallint
);

create or replace function is_less_than_cw(
    calendar_week, calendar_week) returns boolean
as $$
    select case when
        ($1).y < ($2).y or (
        ($1).y = ($2).y and ($1).week_number < ($2).week_number)
            then true else false end;
$$ language sql immutable;

create operator < (
    leftarg = calendar_week,
    rightarg = calendar_week,
    function = is_less_than_cw,
    commutator = >,
        -- since (x < y) = (y > x)
    negator = >
        -- since (x < y) = not (x > y) 
);

select row(2019, 18)::calendar_week < row (2019, 19)::calendar_week

Die wahlfreie Angabe eines Kommutators oder Negators dient lediglich zu etwaigen Optimierungen.

PostgreSQL erlaubt unter gewissen Einschränkungen auch das Erstellen von neuen Operator mit benutzereigenen Symbolen.

Nullproblematik

Null ist Fluch und Segen zugleich: Einerseits erscheint die Handhabung in SQL, das Fehlen eines Wertes universell als NULL auszudrücken, sehr einfach und effizient; andererseits bleibt – aufgrund der fehlenden Widerspieglung im Typsystem – ausnahmslos die ganze Arbeit beim Programmierer hängen, stets dafür zu sorgen, dass Operationen trotz Nullwerte wie gewünscht ablaufen. Funktionen als strict zu kennzeichnen, kann eine mögliche Abhilfe schaffen:

xxxxxxxxxx
create or replace function strict_const(anyelement) returns int strict as $$
    select 1;
$$ language sql immutable;

select strict_const('Hallo Welt!'::text) -- → 1

select strict_const(null::text) -- → NULL   

Eine stricte Funktion gibt automatisch NULL zurück, sobald auch nur ein einziges Argument zu NULL evaluiert. Somit kann der Programmierer den Rückgabewert auf NULL prüfen und etwaige Sonderbehandlungen vorgeben, ohne auf Exceptions zurückgreifen zu müssen. Jedoch ist zu beachten, dass als strict gekennzeichnete Funktionen meist wegen unmöglicher Inline-Ersetzung nicht optimiert werden. Daher ist zu empfehlen, selbst etwaige Prüfungen zu programmieren:

xxxxxxxxxx
create or replace function if_anything_is_null(anyelement, anyelement)
returns boolean immutable as $$
    select $1 is null or $2 is null;
$$ language sql;

create operator ? ( -- drop operator ? (anyelement, anyelement)
    leftarg = anyelement,
    rightarg = anyelement,
    function = if_anything_is_null
);


create or replace function if_anything_is_null(boolean, anyelement)
returns boolean immutable as $$
    select $1 or $2 is null;
$$ language sql;

create operator ? ( -- drop operator ? (boolean, anyelement)
    leftarg = boolean,
    rightarg = anyelement,
    function = if_anything_is_null
);


create or replace function ccat(text, text, int) returns text as $$
    select case when $1 ? $2 ? $3 then null else $1 || $2 || $3::text end;
$$ language sql immutable;

Gespeicherte Prozeduren

 

xxxxxxxxxx

 

Nachrichten und Fehler

raise <level> <format>;

mit <level> gleich debug | log | notice | info | warning | exception

Beispiel

xxxxxxxxxx
do $$ begin
    raise notice 'Aktuelle Uhrzeit: %', now();
end $$;

Das Prozentzeichen fungiert als Platzhalter für die nachfolgend aufgelisteten Ausdrücke.

Trigger

 

xxxxxxxxxx

 

PL/SQL

MySQL bzw. MariaDB implementieren zumindest eine Teilmenge von PL/SQL.

Anweisungsblöcke

Mehrere Anweisungen lassen sich zu einem Block zusammenfassen: 

xxxxxxxxxx
begin not atomic -- anonymous block / nameless procedure
    STATEMENTS
end

Anweisungen erfordern stets ein Semikolon ; als Befehlsabschluss.

Variablen

Vom Benutzer angelegte Variablen gelten nur für eine Sitzung (en session), können aber zwischen mehreren Abfragen und abgelegten Unterprogrammen geteilt werden.

xxxxxxxxxx
declare NAME TYPE [default EXPR] -- Deklarierung

set @NAME = EXPR                 -- Initialisierung / Zuweisung

select @NAME := EXPR

select @a, @b, … := EXPR

Beim Aufruf einer Variable muss das Adresszeichen @ vorangestellt werden. Fehlt das Adresszeichen, liegt hingegen nur eine lokal sichtbare Variable vor, beschränkt auf den Scope des umgebenden Blocks.

Systemvariablen sind vordefinierte Werte zum Konfigurieren von Datenbankoperationen  und unterscheiden sich durch zwei vorangestellte Adresszeichen @@ von den übrigen Variablen.

Kontrollstrukturen

MariaDB unterstützt die wesentlichen Konstrukte zum Steuern des Programmablaufs:

xxxxxxxxxx
-- If Statement
if SEARCH_COND then STATEMENTS
elseif SEARCH_COND then STATEMENTS
else STATEMENTS
end if;

-- If Function
select if(1 < 2, 'yes', 'no');
    
-- Case Operator
select case 9 when 1 then 'eins' when 9 then 'neun' end;

-- General Loop
LABEL: loop
    STATEMENTS
    if COND then iterate LABEL; end if; -- Continue the loop.
    leave LABEL; -- Break the loop.
end loop

-- For Loop
for i in 1..3 do
    STATEMENTS
end for;

Funktionen

Benutzerdefinierte Funktionen

• werden abgespeichert als Datenbankobjekte im Schema des Eigentümers.
• sind nutzbar für andere Anwender bei entsprechender Berechtigung.
• verursachen keine Änderungen in der Datenbank (→ DML, DDL).
• können bei passendem Rückgabedatentyp in allen Klauseln aufgerufen werden, wo auch Systemfunktionen vorkommen.
• können Werte aus der aufrufenden Umgebung entgegen nehmen.
• müssen entweder einen einzelnen Wert oder eine ganze Tabelle an die aufrufende Umgebung zurückgeben.

xxxxxxxxxx
create [or replace] [aggregate] function [if not exists] NAME(
    PARAM TYPE, …
)
returns TYPE begin
    STATEMENTS
    return EXPRESSION;
end

Prozeduren

Gleichermaßen als Datenbankobjekte abgespeichert fassen benutzereigene Prozeduren ein oder mehr Anweisungen zusammen:

xxxxxxxxxx
use DATABASE_NAME;

create or replace procedure NAME(MODIFIER PARAM TYPE, …) begin
    STATEMENTS …
end;

call NAME(…);

Im Gegensatz zu Funktionen geben Prozeduren keinen Wert zurück, sondern verändern über Referenzen bestehende Daten. Wie weitreichend über einen Parameter eingegriffen werden darf, ist durch einen von drei möglichen Zugriffsmodifikatoren festzulegen:

Attribut	Zugriffsrecht	Auswirkung
in	nur lesend	→ Bei Veränderung wird eine lokale Kopie angelegt.
out	nur schreibend	→ Der Parameter ist uninitialisiert (NULL).
inout	lesend / schreibend	→ Statt einer Kopie wird die äußere Variable überschrieben.

Bei einem als out gekennzeichneten Parameter verläuft der Datenfluss nur aus der Prozedur heraus. Der Anfangswert der übergebenen Variable ist nicht lesbar. Stattdessen bleibt der Parameter auf NULL gesetzt, solange nicht innerhalb der Prozedur eine Wertzuweisung erfolgt. 

Persönliche Konventionen

SQL im Allgemeinen und Implementierungen wie PostgreSQL weisen extrem viele Schlüsselwörter auf. Der Rückgriff auf angefügte Unterstriche kann hier für gewisse Erleichterung sorgen und gleichzeitig Namenskollisionen von vornherein ausschließen:

Datenbankobjekt	Kennzeichen	Beispiele
Schema	lediglich kleingeschrieben	logistics
Tabelle, Datentyp	lediglich kleingeschrieben	booking_year
Einwegtabelle	umschlossen von _	_person_
Constraint	Text in Anführungszeichen	"either x or y"
Trigger	systematisch	before_insert_job
Prozedur, Funktion	lediglich kleingeschrieben	build_tables
bloße Implementierungen	umschlossen von __	__check_cw__
— boolean als Rückgabetyp	gepräfixt mit is_	is_valid_cw
— spezifische Spaltenabfrage	systematisch	location_name
Parameter, Variable	gepräfixt mit _	_txt

Auf CamelCase sollte verzichten werden, da SQL standardgemäß nicht zwischen Groß- und Kleinbuchstaben unterscheidet. Stattdessen sollten die Bestandteile von zusammengesetzten Begriffen mittels Unterstriche voneinander getrennt werden. Der Rückgriff auf Hochkammas bei Namen – vor allem von Tabellen, Spalten oder auch Funktionen – ist absolut nicht zu empfehlen. Jedoch kann diese Möglichkeit bezüglich Constraints, dessen Bezeichner nur bei Fehlermeldungen angezeigt werden, durchaus sinnvoll sein, um kurze Erläuterungen statt wenig sagende Namen wie tabelle_ck_spalte auszugeben.

Aus Gründen der Einfachheit ist zu empfehlen, sogar Schlüsselwörter durchgängig kleinzuschreiben, da moderne Texteditoren problemlos die Syntax hervorheben können! Heutzutage besteht absolut kein Grund mehr, noch die Tastaturakrobatik zu vollführen, zwanghaft KEYWORDS zu UPPERCASEN. Eine Ausnahme stellen lediglich Abfragen in Form von Zeichenketten über eine Hostsprache dar, um mittels durchgehender Großschreibung die fehlende Syntaxhervorhebung zu kompensieren.

Handhabung

PostgreSQL

In PostgreSQL wird ein Benutzerkonto als Rolle bezeichnet. Standardmäßig verwendet PostgreSQL die Ident-Authentifizierung, sodass Rollen den Systemkonten von Linux zugeordnet sind.

Bei der Installation wird automatisch ein Benutzerkonto namens postgres erstellt, welches der Standardrolle postgres angehört (→ database superuser). Um eine Verbindung herzustellen, ist zum Postgres-Konto zu wechseln:

> sudo -i -u postgres

> psql

Oder ohne Umweg:

> sudo -u postgres psql

In der interaktiven Umgebung von PostgreSQL (en interactive command prompt) sollte als erstes ein neues Passwort festgelegt werden:

postgres=# alter user postgres password 'Pa$$w0rd';

Prompt

Aufgabe	Befehl	Langform
Prompt beenden	\q	`\quit
Übersicht aller Befehle	\? (Beendigung mit q)
Liste aller Datenbanken	\l	\list
Verbindung aufbauen	\c <db name> [<role name>]	\connect
Passwort ändern		\password
Liste aller Relationen	\d
Liste aller Tabellen	\dt
Tabellenbeschreibung	\d <table name>
Liste aller Schemen	\dn
Liste aller Funktionen	\df
Liste aller Sichten	\dv
Liste aller Rollen	\du
Übersicht aller erteilter Rechte	\dp
aktuelle Version von pgSQL	SELECT version();
letzten Befehl wiederholen	\g
Anzeige der Befehlshistorie	\s
Sichern der Befehlshistorie	\s <file name>
Wechsel zur HTML-Ausgabe	\H

Hinzufügen von Nutzern und Rechten

xxxxxxxxxx
create user USERNAME with password 'PASSWORD'; 

alter user USERNAME with encrypted password 'PASSWORD';

grant all privileges on database DB to USERNAME;

Frontends zum Verwalten von Postgres-Datenbanken

• DBeaver
• phpPgAdmin
• pgAdmin

Interaktion mit Python

Empfohlen wird der Datenbanktreiber psycopg in Version 2 – Stand 2021:

pip install psycopg2

Unter Linux sind noch zuvor folgende Abhängigkeiten zu installieren:

sudo apt install python3-dev libpq-dev

Quelle: www.psycopg.org/install/

MariaDB

> sudo mysql -u root -p # einloggen als root

Neues Passwort einrichten

xxxxxxxxxx
GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY 'Pa$$w0rd';

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
FLUSH PRIVILEGES;

Anschließend beenden exit und Neustarten mit sudo service mysql restart.

Zu Entwurfszwecken ist ein einfaches Passwort wie Pa$$w0rd zu empfehlen.

Spezifische Befehle

Zum Verwalten des Servers bietet MariaDB eine Reihe von Anweisungen (→ Semikolon als Abschlusszeichen), welche in die Syntax von SQL eingebettet sind:

Aufgabe	Anweisung
Anzeigen aller Datenbanken / Schemen	show {database| schemas}
— mit Filterung	… [like <pattern> | where <expr>]

SQLite

Nach Installation ist die interaktive Umgebung direkt zugänglich:

xxxxxxxxxx
sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .help

SQLite-eigene Befehle werden mit einem vorangestellten Unterpunkt aufgerufen (→ SQLite dot commands) und ohne Semikolon als Abschlusszeichen.

Im Gegensatz zu MariaDB oder anderen Datenbanken sind ebenso keine besonderen Rechte erforderlich, um eine neue Datenbank zu erzeugen:

> sqlite3 DatabaseName

Falls die angegebene Datenbank noch nicht existiert, wird diese automatisch angelegt.

Aufgabe	Befehl
Anzeige aller verbundener Datenbanken	.databases
Auflisten aller Tabellen	.tables
Einlesen und Ausführen einer SQL-Datei	.read x.sql

Datenbankverbindungen

Bei Verbindung zu einer Datenbankdatei lädt SQLite diese unter dem Namen main, unabhängig vom tatsächlichen Dateinamen. Ein anderer Bezeichner ist nicht wählbar. Über temp kann auf die zwischengespeicherten Tabellen und anderen Datenbankobjekte einer Verbindung zugegriffen werden.

Neben der primären Datenbank main erlaubt SQLite das Einbinden von weiteren Nebendatenbanken:

attach database 'FILENAME' as 'ALIAS';

detach database 'ALIAS';

Daten aus CSV-Dateien

Zuerst muss eingestellt werden, dass die einzulesende Datei als CSV zu interpretieren ist:

sqlite> .mode csv

Erst dann lassen sich die Datensätze in eine Tabelle importieren:

sqlite> .import <path> <table>

Und zur abschließenden Kontrolle der neuen Datensätze:  

sqlite> .schema <table>

Anhang

PostgreSQL vs MariaDB vs SQLite

Welches Datenbanksystem ist das richtige? Die Antwort hängt vom jeweiligen Anwendungsfall ab:

• Soll die Datenbank lediglich Programmdaten speichern ohne mit anderen Nutzern zu interagieren, ist SQLite die erste Wahl, da hier kein Datenbankserver installiert werden muss. Im Vergleich zu allen anderen Datenbanksystemen ist SQLite völlig schmerzfrei und geradezu simpel.
• Bei Webseiten erübrigt sich die Frage, da in aller Regel schon MySQL bzw. MariaDB vorinstalliert ist. Zudem interagiert die Datenbank ohnehin nur mit der hiesigen Webseite auf selbigem Server, sodass wie bei SQLite die Regeln zur Wahrung der Datenintegrität größtenteils oder weitestgehend auf Ebene in der jeweils eingesetzten Programmiersprache erfolgen (bei Webseiten / Webapps zumeist PHP oder JavaScript).
• Soll die Datenbank hingegen für mehrere Nutzer oder gar für unterschiedliche Anwendungen zugänglich sein, genügen die Möglichkeiten von MySQL / MariaDB nicht mehr, um die Datenintegrität gänzlich unabhängig von der Applikationsebene zu gewährleisten.