Modul 164 - Datenbanken erstellen und Daten einfügen

Heute ging es um die Grundlagen der relationaler Datenbanken. Dabei habe ich gelernt, was eine relationale Datenbank ist.

Eine relationale Datenbank speichert Daten in Tabellen. Jede Tabelle besteht aus Zeilen (Datensätze) und Spalten (Attribute).

In einer Zeile befinden sich Daten zu einer bestimmten Entität (einzelner Datensatz innerhalb einer Tabelle).

Auch wurde wieder erklärt, was Primärschlüssel und Fremdschlüssel sind.

• Primärschlüssel:

Ein Primärschlüssel ist ein Attribut oder eine Kombination von Attributen, das jede Zeile eindeutig identifiziert.

• Fremdschlüssel:

Ein Fremdschlüssel ist ein Attribut, das auf ein Primärschlüssel einer anderen Tabelle verweist, um eine Beziehung zwischen den Tabellen herzustellen.

Als übung habe ich wieder mit meiner im Modul-162 erstellten MariaDB Datenbank abfragen geübt.

Grundsätzlich war heute also wieder vieles reine repetition des Modul-162.

Heute habe ich die verschiedenen Datenbankmodelle gelernt.

Es gibt 5 Datenbankmodelle:

• Hierarchisches Modell:

Daten werden in einer Baumartigen Struktur organisiert.

Jede Datenzeile hat nur eine übergeordnete Zeile.

• Netzwerkmodell:

Ähnlich dem Hierarchischem Modell, aber hier können Daten mehrere übergeordnete Zeilen haben.

• Relationales Modell:

Das am häufigsten verwendete Modell, bei dem Daten in Tabellen gespeichert werden, die durch Schlüssel miteinander verbunden sind.

• Objektorientiertes Modell:

Daten werden als Objekte gespeichert, ähnlich wie bei der Programmierung mit Klassen und Instanzen.

• NoSQL-Datenbanken:

Diese Datenbanken sind nicht relational und speichern Daten in Formaten wie Dokumenten, Key-Value-Pairs oder Graphen.

Geeignet für grosse, verteilte Datenmengen.

Ein DBMS ist eine Software, die hilft, Datenbanken zu erstellen, zu verwalten und zu organisieren.

Übliche relationale DBMS

• MySQL

Weit verbreitet und schnell. Verwendet für Webanwendungen.

• MariaDB

Eine Weiterentwicklung von MySQL.

• PostgreSQL

Robustes und Flexibles Relationales Datenbankmanagementsystem (RDBMS).

Beliebt bei grossen und komplexen Anwendungen.

• SQL Server

Von Microsoft entwickletes RCBMS.

Oft in Unternehmungsumgebungen verwendet.

• SQLite

Ein Leichtgewichtiges DBMS, das in kleinen Anwendungen verwendet wird.

Unterschiede und Gemeinsamkeiten

Feature	MariaDB	MySQL	PostgreSQL	SQL Server
Open Source	Ja	Teilweise	Ja	Nein (kommerziell)
Plattform	Linux, Windows, Mac	Linux, Windows, Mac	Linux, Windows, Mac	Windows, Linux (nur begrenzt)
Leistung	Schnell, effizient	Gut für Web-Apps	Sehr leistungsstark	Extrem leistungsfähig für große Systeme
JSON-Unterstützung	Eingeschränkt	Eingeschränkt	Sehr gut	Sehr gut
Erweiterte SQL-Funktionen	Mittel	Mittel	Sehr umfangreich	Sehr umfangreich
Transaktionen (ACID)	Ja	Ja	Ja	Ja

ACID-Konformität

ACID-konform bedeutet, dass eine Datenbank vier Eigenschaften garantiert:

• Atomicity – Unteilbarkeit von Transaktionen
• Consistency – Daten bleiben konsistent
• Isolation – Gleichzeitige Transaktionen beeinflussen sich nicht
• Durability – Daten bleiben auch nach einem Absturz erhalten

Erstellung eines Datenbankschemas (DDL, Tabellen usw.)

Bevor man mit SQL arbeitet, braucht man ein konzeptionelles Modell der Datenbank. Dazu gehören Tabellen, ihre Beziehungen und Regeln zur Datenintegrität.

Vor diesem Schritt sollte ein ER-Diagramm erstellt werden, um die Tabellen, die Beziehungen und die Regeln grafisch darzustellen.

Vergleich mit einem Bauprojekt:

• Bevor man ein Haus baut, erstellt man einen Bauplan → ER-Diagramm
• Dann baut man Wände und Räume → DDL-Code, der das Gerüst der Datenbank bildet
• Erst wenn man Möbel reinstellt, ist das Haus fertig → DML (Daten einfügen)

Datenbankschema mit DDL erstellen

DDL steht für Data Definition Language. Um das "Gerüst" einer Datenbank zu bauen, verwendet man folgende SQL-Befehle:

SQL-Befehl	Beschreibung
CREATE DATABASE	Erstellt eine neue Datenbank
CREATE TABLE	Erstellt eine Tabelle mit Spalten
ALTER TABLE	Ändert eine bestehende Tabelle (z. B. Spalten hinzufügen)
DROP TABLE	Löscht eine Tabelle vollständig
CONSTRAINT	Definiert Regeln für Werte (z. B. NOT NULL, UNIQUE)
FOREIGN KEY	Erstellt eine Beziehung zwischen Tabellen

Ein Datenbankschema besteht aus:

• Eine Datenbank enthält Tabellen
• Tabellen bestehen aus Spalten (Attribute) und Zeilen (Datensätze)
• Das Schema ist die Struktur der Datenbank

Beispielhafte Datentypen:

Datentyp	Beschreibung	Beispiel
INT	Ganze Zahl	Schuetze_ID INT
VARCHAR(n)	Zeichenkette mit max. Länge n	Name VARCHAR(50)
DATE	Speichert ein Datum	Geburtsdatum DATE
DECIMAL(m, d)	Dezimalzahl mit m Stellen, davon d Nachkommastellen	Ergebnis DECIMAL(5,2)

Beispiel: Datenbank erstellen

CREATE DATABASE schiessstandDB;

USE schiessstandDB;

Tabellen erstellen

1. Tabelle für Schützen

CREATE TABLE Schuetzen (
                SchuetzeID INT PRIMARY KEY AUTO_INCREMENT,
                Name VARCHAR(50) NOT NULL,
                Geburtsdatum DATE,
                Wohnort VARCHAR(50)
                );
                

• PRIMARY KEY (SchuetzeID): Jeder Schütze hat eine eindeutige ID
• AUTO_INCREMENT: ID wird automatisch hochgezählt
• NOT NULL: Name darf nicht leer sein

2. Tabelle für Waffen

CREATE TABLE Waffen (
                WaffeID INT PRIMARY KEY AUTO_INCREMENT,
                Modell VARCHAR(50) NOT NULL,
                Kaliber VARCHAR(50) NOT NULL,
                Hersteller VARCHAR(50)
                );
                

3. Tabelle für Schiessstände

CREATE TABLE Schiessstand (
                SchiessstandID INT PRIMARY KEY AUTO_INCREMENT,
                Standort VARCHAR(50) NOT NULL,
                Max_Entfernung INT
                );
                

4. Ergebnistabelle (mit Fremdschlüsseln)

CREATE TABLE Ergebnisse (
                ErgebnisID INT PRIMARY KEY AUTO_INCREMENT,
                SchuetzeID INT,
                WaffeID INT,
                SchiessstandID INT,
                Punkte DECIMAL(5,2),
                Datum DATE NOT NULL,
                FOREIGN KEY (SchuetzeID) REFERENCES Schuetzen(SchuetzeID),
                FOREIGN KEY (WaffeID) REFERENCES Waffen(WaffeID),
                FOREIGN KEY (SchiessstandID) REFERENCES Schiessstand(SchiessstandID)
                );
                

Constraints und Aufbau

Wichtige Constraints

Constraint	Funktion
NOT NULL	Wert darf nicht leer sein
UNIQUE	Wert muss eindeutig sein
DEFAULT	Standardwert setzen
CHECK	Bedingungen setzen (z. B. Zahlenbereich)

Beispiel: Einschränkungen in Schuetzen-Tabelle

CREATE TABLE Schuetzen (
                SchuetzeID INT PRIMARY KEY AUTO_INCREMENT,
                Name VARCHAR(50) NOT NULL,
                Geburtsdatum DATE CHECK (Geburtsdatum <= CURDATE()),
                Wohnort VARCHAR(50) DEFAULT 'Unbekannt'
                );
                

• NOT NULL: Name darf nicht leer sein
• CHECK: Geburtsdatum darf nicht in der Zukunft liegen
• DEFAULT: Falls kein Wohnort eingetragen wird, wird "Unbekannt" eingetragen

Reihenfolge beim Aufbau einer Datenbank

Schritt	Funktion	Beispiel
ER-Diagramm	Planung, Konzept	Zeigt Tabellen und ihre Beziehungen
DDL (CREATE TABLE)	Struktur erstellen	Tabellen mit Primär- und Fremdschlüsseln
DML (INSERT INTO)	Daten einfügen	Schuetzen, Waffen usw.
SQL-Abfragen (SELECT)	Daten auswerten	Liste aller Schuetzen

Sicherstellung der referenziellen Integrität

Nachdem das Grundgerüst mit Tabellen und Beziehungen steht, muss man sicherstellen, dass Datenintegrität gewahrt bleibt.

Vergleich: Türschloss-System

• Ohne Schlüsselkontrolle kann jeder in beliebige Räume
• Mit Schlüsselmanagement: Nur autorisierte Personen dürfen hinein

Ziel: Fremdschlüssel (FK) dürfen nicht ins Leere zeigen und die Daten sollen konsistent bleiben.

Was ist referenzielle Integrität?

Sie stellt sicher, dass Beziehungen zwischen Tabellen korrekt bleiben. Dazu setzt man Fremdschlüssel und definiert Regeln für das Löschen oder Aktualisieren von Daten:

Beispiel mit ON DELETE und ON UPDATE:

CREATE TABLE Ergebnisse (
            Ergebnis_ID INT PRIMARY KEY AUTO_INCREMENT,
            Schuetze_ID INT,
            Waffe_ID INT,
            Schiessstand_ID INT,
            Punkte DECIMAL(5,2),
            Datum DATE NOT NULL,
            FOREIGN KEY (Schuetze_ID) REFERENCES Schuetzen(Schuetze_ID)
                ON DELETE CASCADE
                ON UPDATE CASCADE,
            FOREIGN KEY (Waffe_ID) REFERENCES Waffen(Waffe_ID)
                ON DELETE SET NULL
                ON UPDATE CASCADE,
            FOREIGN KEY (Schiessstand_ID) REFERENCES Schiessstaende(Schiessstand_ID)
                ON DELETE RESTRICT
                ON UPDATE CASCADE
            );
            

Erklärung der Fremdschlüssel-Optionen:

Option	Bedeutung bei DELETE/UPDATE
CASCADE	Verknüpfte Einträge werden mit gelöscht/geändert
SET NULL	Verknüpfter Wert wird auf NULL gesetzt
RESTRICT	Löschen wird blockiert, wenn noch Verknüpfungen bestehen

Tabelle aktualisieren & MariaDB starten

Tabelle mit ALTER TABLE erweitern

Um z. B. eine neue Spalte oder eine Regel hinzuzufügen, verwendet man ALTER TABLE:

ALTER TABLE Ergebnisse
            ADD CONSTRAINT Punkte CHECK (Punkte BETWEEN 0 AND 200);

            ALTER TABLE Ergebnisse
            ADD COLUMN Test VARCHAR(50) DEFAULT 'Test';
            

Mit DESCRIBE bekommt man eine Übersicht über alle Spalten:

DESCRIBE Ergebnisse;
            

Field	Type	Null	Key	Default	Extra
ErgebnisID	int(11)	NO	PRI	NULL	auto_increment
SchuetzenID	int(11)	YES	MUL	NULL
SchiessstandID	int(11)	YES	MUL	NULL
Punkte	int(11)	YES		NULL
Datum	date	YES		NULL
Test	varchar(50)	YES		Test

MariaDB starten & Datenbank wählen

$ mysql -u root -p

            SHOW DATABASES;
            USE SchiessDB;
            

Damit wechselt man in die gewünschte Datenbank.

Einfügen von Daten in die Datenbank (DML)

Nachdem die Struktur steht , geht es darum, die Datenbank mit echten Werten zu füllen.

DML-Befehle:

• INSERT – Daten hinzufügen
• UPDATE – Daten ändern
• DELETE – Daten löschen
• SELECT – Daten anzeigen

Beispiele:

-- Einzelner Eintrag
            INSERT INTO Schuetzen (Name, Alter, Wohnort)
            VALUES ('Max Müller', 30, 'Arch');

            -- Mehrere Einträge gleichzeitig
            INSERT INTO Waffen (Modell)
            VALUES ('Sig P220'), ('Desert Eagle'), ('Sig P320');
            

Tabellenübersicht anzeigen

SHOW TABLES;
            

Tables_in_SchiessDB
Ergebnisse
Schiessstand
Schuetzen
Schuetzen_Waffen
Waffen

Datensätze anzeigen

SELECT * FROM Schuetzen;
            

SchuetzenID	Name	Alter	Wohnort
1	Max	25	Bern
2	Anna	22	Zürich
3	Marcel	18	Thun
4	Beni	32	Ostermundigen
5	Alf	19	Bümpliz

Abfragen & Datenanalyse (SELECT & JOIN)

Grundlegende Abfragen:

-- Alle Namen anzeigen
            SELECT Name FROM Schuetzen;

            -- Alle Daten von Schuetzen aus Zürich
            SELECT * FROM Schuetzen WHERE Wohnort = 'Zürich';

            -- Schuetzen unter 26 Jahren
            SELECT * FROM Schuetzen WHERE Alter < 26;

            -- Nur bestimmte Kaliber
            SELECT * FROM Waffen WHERE Kaliber IN ('9mm', '10mm');
            

JOINs – Tabellen logisch verbinden

Mit JOINs verknüpft man mehrere Tabellen in relationalen Datenbanken:

JOIN-Typ	Zeigt ALLE aus…	UND nur passende aus…
INNER JOIN	Nur mit Verbindung	Beide Tabellen
LEFT JOIN	Linke Tabelle (immer)	Rechte Tabelle (nur bei Match)
RIGHT JOIN	Rechte Tabelle (immer)	Linke Tabelle (nur bei Match)
FULL OUTER JOIN	Beide Tabellen (alles)	–

Beispiel INNER JOIN:

SELECT s.Name, e.Punkte, e.Datum
            FROM Ergebnisse e
            INNER JOIN Schuetzen s ON e.SchuetzeID = s.SchuetzeID;
            

Nur Schuetzen mit Ergebnis werden angezeigt.

Name	Punkte
Max	92
Tom	88

Beispiel LEFT JOIN:

SELECT * FROM Schuetzen s
            LEFT JOIN Ergebnisse e ON s.SchuetzeID = e.SchuetzeID;
            

Alle Schuetzen werden angezeigt – auch ohne Ergebnis.

Name	Punkte
Max	92
Lisa	NULL
Tom	88

Beispiel FULL OUTER JOIN (Simulation mit UNION):

SELECT * FROM Schuetzen s
            LEFT JOIN Ergebnisse e ON s.SchuetzeID = e.SchuetzeID
            UNION
            SELECT * FROM Schuetzen s
            RIGHT JOIN Ergebnisse e ON s.SchuetzeID = e.SchuetzeID;
            

Damit bekommt man ALLE Schuetzen mit/ohne Ergebnis und ALLE Ergebnisse mit/ohne zugeordneten Schuetzen.

Beispieldaten:

SchuetzenID	Name
1	Max
2	Lisa
3	Tom

ErgebnisID	SchuetzenID	Punkte
101	1	92
102	3	88

Abfragen & Analyse (SELECT mit Bedingungen)

Beispiele mit WHERE:

-- Nur Namen anzeigen
            SELECT Name FROM Schuetzen;

            -- Alle aus Zürich
            SELECT * FROM Schuetzen WHERE Wohnort = 'Zürich';

            -- Nur unter 26 Jahre alt
            SELECT * FROM Schuetzen WHERE Alter < 26;

            -- Nur bestimmte Kaliber
            SELECT * FROM Waffen WHERE Kaliber IN ('9mm', '10mm');
            

JOINS – Tabellen logisch verbinden

JOINS verknüpfen mehrere Tabellen zu sinnvollen Ergebnissen. Es gibt INNER, LEFT, RIGHT und FULL OUTER JOIN.

-- INNER JOIN: Nur Treffer beider Tabellen
            SELECT s.Name, e.Punkte, e.Datum
            FROM Ergebnisse e
            INNER JOIN Schuetzen s ON e.SchuetzenID = s.SchuetzenID;

            -- LEFT JOIN: Alle Schuetzen, auch ohne Ergebnis
            SELECT * FROM Schuetzen s
            LEFT JOIN Ergebnisse e ON s.SchuetzenID = e.SchuetzenID;
            

FULL OUTER JOIN simulieren (nicht direkt in MySQL)

SELECT * FROM Schuetzen s
            LEFT JOIN Ergebnisse e ON s.SchuetzenID = e.SchuetzenID
            UNION
            SELECT * FROM Schuetzen s
            RIGHT JOIN Ergebnisse e ON s.SchuetzenID = e.SchuetzenID;
            

Erweiterte Filter: GROUP BY & HAVING

-- Gruppieren nach Name, Durchschnitt berechnen
            SELECT s.Name, AVG(e.Punkte) AS Schnitt
            FROM Ergebnisse e
            JOIN Schuetzen s ON e.SchuetzenID = s.SchuetzenID
            GROUP BY s.Name;

            -- Bedingung auf Aggregatwert
            SELECT s.Name, AVG(e.Punkte) AS Schnitt
            FROM Ergebnisse e
            JOIN Schuetzen s ON e.SchuetzenID = s.SchuetzenID
            GROUP BY s.Name
            HAVING Schnitt > 180;
            

Aggregieren & Subqueries

COUNT(), SUM(), AVG(), MAX(), MIN() helfen, Werte zusammenzufassen.

-- Beispiel: Wie oft hat jemand geschossen?
            SELECT s.Name, COUNT(e.ErgebnisID) AS Anzahl
            FROM Ergebnisse e
            JOIN Schuetzen s ON s.SchuetzenID = e.SchuetzenID
            GROUP BY s.Name;
            

Subquery Beispiel:

-- Zeige alle Schuetzen über dem Gesamtdurchschnitt
            SELECT s.Name
            FROM Ergebnisse e
            JOIN Schuetzen s ON s.SchuetzenID = e.SchuetzenID
            GROUP BY s.Name
            HAVING AVG(e.Punkte) > (
            SELECT AVG(Punkte) FROM Ergebnisse
            );
            

UNION, LIMIT, OFFSET, Fehlerlogik & SQLi

Union Select

-- Kombiniert zwei Abfragen mit gleichen Spalten
            SELECT name, city FROM customers
            UNION
            SELECT company, city FROM suppliers;
            

SQLi Beispiel (unsicherer Code)

SELECT name FROM users WHERE id = '$id';
            -- Angreifer gibt ein:
            SELECT name FROM users WHERE id = 1 UNION SELECT password FROM users -- ;
            

-- kommentiert den Rest. So wird SQL manipuliert. Wichtig für Sicherheitstests!

Typische Anzeichen für SQL-Injection

• Plötzlich viel mehr Daten oder fremde Spalten erscheinen
• Fehlermeldungen wie Unknown column oder Syntax error

SELECT * FROM users WHERE username = '$user' AND password = '$pass';

            -- Angreifer-Eingabe:
            SELECT * FROM users WHERE username = 'admin' -- ;
            

Durch -- wird der Passwort-Check deaktiviert!

LIMIT & OFFSET zur Datenexfiltration

Angreifer können damit schrittweise Daten abfragen:

SELECT name, email FROM users LIMIT 1 OFFSET 0;
            SELECT name, email FROM users LIMIT 1 OFFSET 1;
            SELECT name, email FROM users LIMIT 1 OFFSET 2;
            

So werden alle Einträge einzeln sichtbar gemacht.

Absicherung gegen UNION & SQLi (Sneak Peek)

• Prepared Statements – SQL-Code und Daten getrennt
• Whitelist – Nur erlaubte Eingaben
• Keine Fehlermeldungen – Nur intern loggen
• LIMIT – Auf sensible Daten begrenzen

Mehr dazu in einem späteren Kapitel.