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 mitINSERT INTO
ein Eintrag in der vorher erzeugten Tabellebestand
mit der ID111
, dem NamenReifen
und einem Preis von99
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.