SQL

Einleitung

SQL steht für Structured Query Language (Strukturierte Abfrage-Sprache) und ist eine Datenbanksprache, mit der Datenstrukturen in relationalen Datenbanken abgelegt und abgerufen werden können. SQL wurde in den 1970er Jahren von Edgar F. Codd (IBM), Donald D. Chamberlin und Raymond F. Boyce entworfen.

Von SQL gibt es viele verschiedene Implementationen, wesentliche Varianten werden z.B. hier vorgestellt. Die Implementationen unterscheiden sich in Faktoren wie kostenpflichtigkeit, Geschwindigkeit und Nutzerfreundlichkeit. Eine sehr populäre Implementation von SQL ist PostgreSQL. Diese ist kostenfrei, schnell und vergleichsweise einfach für Neueinsteiger, weshalb in diesem Tutorial PostgreSQL verwendet wird. PostgreSQL wurde ab 1982 von Michael Stonebraker an der University of California in Berkeley entwickelt.

SQL kann entweder durch ein Terminalprogramm, eine graphische Schnittstelle oder durch Steuerungsbibliotheken mit Programmiersprachen bedient werden. Hier wird PostgreSQL mit Python-Bindings und einem einfachen Wrapper-Script gesteuert, die SQL-Befehle könnten aber auch im Terminalprogramm oder einer GUI verwendet werden.

Installation

Windows

Ein Installer für Windows wird auf der Webseite von EDB zur Verfügung gestellt. Wähle den Installer für Version 12.X, um die Beispiele auf dieser Webseite nachvollziehen zu können.
Wähle bei der Installation ein Passwort und merke dir dieses; bestätige alle weiteren Anfragen des Installers ausser der Installation der Zusatzsoftware StackBuilder - diese ist nicht erforderlich.
Falls du Python nicht bereits installiert hast, lade den Windows-Installer herunter und führe ihn aus.
Dabei sollten folgende Optionen aktiviert werden: "pip", "py launcher" und "Associate files with Python".
Du kannst jetzt Python oder Pip von der Kommandozeile aus ausführen.

Wie du PostgresSQL unter Windows installierst findest du schrittweise auf auf dieser Seite

Mac-OSX

Die Installation auf Mac/OSX erfolgt am einfachsten über Homebrew. Installiere Homebrew mit dieser Terminal-Eingabe auf deinen Mac:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Installiere die Version 12.X von PostgreSQL mit dem Befehl:

brew install postgresql@12

Wähle bei der Installation ein Passwort und merke dir dieses.
Zusätzlich für Mac/OSX ist die Installation pip notwendig. Installiere pip durch die Eingabe von

curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py

Ubuntu Linux

Auf Ubuntu Linux wird PostgreSQL mit folgenden Eingaben installiert:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-12 -y
sudo apt install python3-dev libpq-dev -y

Konfiguration

Python Bindings

Weiterhin müssen noch die Python Bindings für PostgreSQL (hier die populärsten Bindings Psycopg) für Windows, MacOS oder Linux mit pip installiert werden:

pip3 install psycopg2

Jetzt muss PostgreSQL noch für trusted-Zugriff konfiguriert werden, damit lokal gearbeitet werden kann. Dazu muss die Datei pg_hba.conf angepasst werden. Unter Windows findest du diese Datei in C:\Progam Files\PostgreSQL\12\data\pg_hba.conf, unter MacOS und Linux in /etc/postgresql/12/main/pg_hba.conf. Öffne die Datei mit Administrator-Rechten, finde die Zeile

local   all             postgres                                peer

und ersetze sie durch

local   all             postgres                                trust

Erste Schritte

Nachdem PostgreSQL erfolgreich installiert und konfiguriert wurde, kann es nun gestartet werden. Dabei beinhaltet eine PostgreSQL-Session immer zwei Komponenten: Einen im Hintergrund laufenden Datenbankserver sowie eine Clientanwendung, mit der auf den Server zugegriffen wird.

Windows Start

Starte PostgreSQL im Hintergrund mit folgender Eingabe auf der CMD:

pg_ctl.exe restart -D  "C:\Program Files\PostgreSQL\12\data"

Mac Start

Starte PostgreSQL im Hintergrund mit folgender Terminal-Eingabe:

brew services start postgresql

Linux Start

Starte PostgreSQL im Hintergrund mit folgender Eingabe:

sudo pg_ctlcluster 12 main start

Datenbank erzeugen

Zuerst gilt es, eine neue Datenbank für den Nutzer postgres anzulegen:

sudo -u postgres createdb testdb

DRAGONS: it's probably better to remove this part to avoid confusion,
since the Python examples need the database!

Datenbank löschen

Mit der folgenden Eingabe könnte die vorher erzeugte Datenbank wieder gelöscht werden.
ACHTUNG: Führe diesen Schritt nicht aus, da die Datenbank in folgenden Kapiteln noch gebraucht wird!

dropdb testdb

Grundlagen

Zum Ausführen von SQL-Befehlen in Python ist ein Wrapper-Script notwendig, welches mittels des zuvor installierten Modules psycopg2 eine Verbindung (conn) zur Datenbank aufbaut und dann die SQL-Befehle mit dem cursor absendet.
Achtung: Erzeuge vorher die Datenbank testdb wie im vorherigen Kapitel gezeigt!

Wrapper-Script

Speichere den folgenden Python-Code in einer Datei wrapper.py:

def execute_sql_commands(commands):

    conn = psycopg2.connect("dbname=testdb user=postgres")
    
    cur = conn.cursor()

    for command in commands:
        cur.execute(command);

    try:
        rows = cur.fetchall()

        for row in rows:
            print(row)  
    except:
        print("Keine Ergebnisse!")

Mit dem obigen Python-Script können alle folgenden SQL-Beispiele direkt ausgeführt werden. Dazu müssen lediglich die entsprechenden SQL-Befehle als eine Liste von Strings namens commands in der Ausführungsreihenfolge gespeichert werden.

Tabelle erzeugen

Definiere die Liste commands als

commands = [        
        """ CREATE TABLE bestand (
                id INT PRIMARY KEY,
                name VARCHAR NOT NULL,
                preis INT DEFAULT 0
        );
        """

In diesem Beispiel wird eine neue Tabelle bestand mit den Spalten id, name und preis angelegt.

Wrapper ausführen

Das Script wrapper.py kann jetzt durch die Eingabe von

python3 wrapper.py

ausgeführt werden. Ist PostgreSQL korrekt installiert, so läuft das Script ohne Fehlermeldung oder Ausgabe durch und die Tabelle bestand wurde erzeugt.

Änderungen speichern

Ergänze die im vorherigen Abschnitt, Daten einfügen, gezeigte Liste commands um das Listenelement

"COMMIT;"

um die Erzeugung der Tabelle und den Eintrag in die Tabelle dauerhaft zu speichern.
ACHTUNG: Der zusätzliche Befehl "COMMIT;" muss für jede dauerhaften Datenbankänderung den commandos in den folgenden Beispielen hinzugefügt werden!

Daten einfügen

commands = [        
        """ INSERT INTO bestand (id, name, preis)
                VALUES (111, 'Reifen', 99); 
        """,]

   Hier wird mit INSERT INTO ein Eintrag in der vorher erzeugten Tabelle bestand mit der ID 111, dem Namen Reifen und einem Preis von 99 angelegt.

ACHTUNG: Einzelne und doppelte Anführungszeichen sind hier nicht äquivalent! Strings, die in die Datenbank eingefügt werden sollen, müssen in einfachen Anführungszeichen gesetzt sein!

Daten auslesen

Folgendes Kommando sucht alle Einträge in der Tabelle bestand:

commands = [        
        """SELECT * FROM bestand;""",
        ]

Wird das Kommando mit dem Wrapper-Script ausgeführt, so erfolgt als Ausgabe der vorher erzeugte, einzelne Eintrag Reifen:

(111, 'Reifen', 99)

Tabellenoperationen

Die Werte in einer bestehenden Tabelle werden mit UPDATE aktualisiert:

commands = [        
        """UPDATE bestand SET preis = preis + 10 WHERE name='Motor'; """, 
        """COMMIT;""",
        ]

Wird jetzt die Tabelle mit

commands = [        
        """SELECT * FROM bestand;""",
        ]

abgefragt so erfolgt als Ausgabe:

(111, 'Reifen', 99)
(211, 'Felgen', 15)
(312, 'Vergaser', 99)
(311, 'Motor', 1510)

Der Preis des Motors wurde um 10 erhöht. Datensätze können folgendermaßen gelöscht werden (persistent wiederum nur mit COMMIT;):

commands = [        
        """DELETE FROM bestand WHERE name = 'Felgen';""", """SELECT * FROM bestand;""",
        ]

Wie in einem vorherigen Beispiel gezeigt können dabei auch Wildcards verwendet werden.
Neue Spalten werden so der Tabelle hinzugefügt:

commands = [        
        """ALTER TABLE bestand ADD COLUMN anzahl INT DEFAULT 0;""",
        ]

Mit diesem Befehl wird eine neue Spalte anzahl des Typs INT mit einem Defaultwert von 0 angelegt.
Um eine Spalte zu löschen wird DROP verwendet:

commands = [        
        """ALTER TABLE bestand DROP COLUMN preis;""",
        ]

Eine Tabelle wird mit der Operation RENAME TO umbenannt:

commands = [        
        """ALTER TABLE bestand RENAME TO lager;""",
        ]

Durch TRUNCATE können alle Datensätze also Zeilen einer Tabelle gelöscht werden:

commands = [        
        """ALTER TABLE bestand RENAME TO lager;""",
        ]

Glossar

Tabelle

Grundeinheit einer SQL-Datenbank. Die Spalten entsprechen hierbei den Parametern, die Zeilen bilden die Datenpunkte. Jede Zeile hat für jede Spalte einen Wert.

Spalte

In einer Spalte wird ein bestimmer Parameter einer Tabelle abgelegt. Alle Einträge einer Spalte haben den gleichen Datentyp.

Zeile

Eine Spalte enthält für jede Zeile einer Tabelle genau einen Wert und ist die kleinste Einheit, die in einer Tabelle abgelegt werden kann.

Event

Ein Event (Ereignis) wird ausgeführt, wenn die Datenbank einen bestimmten Zustand erreicht hat, zum Beispiel wenn ein Fehler aufgetreten ist.

Trigger

Ein Trigger (Auslöser) ist eine Folge von Datenbankanweisungen, die nach dem Auftreten eines bestimmten Events ausgeführt werden, beispielsweise wenn bestimmte Daten in einer Tabelle geändert wurden.

Primärschlüssel

Jede Tabelle einer Datenbank verfügt über einen Primärschlüssel bestehend aus einer oder mehreren Spalten. Diese haben einen eindeutigen Wert für jede Zeile der Tabelle.

Fremdschlüssel

Fremdschlüssel bezeichnet die Übereinstimmung der Werte einer Spalte einer Tabelle (oder einer Kombination von Spalte) mit der Primärschlüsselspalte einer anderen Tabelle.

Index

Der Index (auch Sekundärschlüssel genannt) dient der Beschleunigung der Suche in einer Tabelle. Eine indizierte Spalte verweist auf verschiedene Primärschlüssel, die auf den selben Spaltenwert verweisen.

Join

Mit einem Join-Befehl werden mehrere Tabellen zu einer neuen Tabelle verknüpft.

Cross Join

Bei einem Cross Join werden alle Einträge einer Tabelle mit allen Einträgen einer anderen verknüpft.

Privileg

Privileg bezeichnet die für ein SQL-Datenbankobjekt erlaubten Operationen und kann für verschiedene Benutzer verschieden definiert sein.

Query Tool

Ein Query Tool ist ein Programm, mit dem SQL-Anweisungen auf eine Datenbank angewendet werden können.

Programmierschnittstelle

Populäre Programmiersprachen bieten SQL-Schnittstellen an, die das Bearbeiten einer SQL-Datenbank vereinfachen.

Schema

Mit einem Schema werden zusammengehörige Datenbankobjekte gruppiert.

Snapshot

Ein Snapshot fixiert den aktuellen Zustand der Datenbank in einer Sicherungsdatei.

View

Ein View ist eine reduzierte, virtuelle Repräsentation einer oder mehrerer Tabellen.

Aggregation

Werden partitiell Daten aus einer oder mehreren Tabellen extrahiert, so ist dies eine Aggregation.

Commit

Mit einem Commit werden alle vorgenommenen SQL-Operationen (Transaktionen) dauerhaft auf die Datenbank durchgeführt.

SQL