SQL

SQL (Structured Query Language, also “strukturierte Abfragesprache”) ist eine standardisierte Abfragesprache (ISO), welche die Definition, Manipulation und das Abfragen von Daten ermöglicht. Des Weiteren dient SQL zur Rechtevergabe in relationalen Datenbanken. Dabei kann in folgende Bereiche untergliedert werden.

DDL (Data Defeinition Language)

Enthält Befehle zum Erstellen und Bearbeiten der Struktur des Datenschemas.

DML (Data Modification Language)

Enthält Befehle für das Anzeigen, Einfügen, Ändern und Löschen von Daten.

DCL (Data Control Language)

Dient zur Rechteverwaltung und zur Zugriffskontrolle.

Bevor Sie Tabellen in einer Datenbank erstellen können, müssen Sie zuerst eine Datenbank anlegen, was mit dem SQL-Befehl CREATE DATABASE funktioniert-

[sql]CREATE DATABASE HighScores;
USE HighScores;[/sql]

Erläuterung zum Code

Der Befehl CREATE DATABASE erstellt eine neue Datenbank mit dem Namen “HighScores”. Der Befehl USE wählt die angegebene Datenbank als aktiv aus. Somit werden alle CREATE TABLE Befehle neue Tabellen in der aktiven Datenbank erstellen.

Um in SQL eine Tabelle zu erstellen, wird der SQL-Befehl CREATE TABLE verwendet. Dieser Befehl kann erst ausgeführt werden, wenn eine neue Datenbank erstellt und ausgewählt wurde.

[sql]CREATE TABLE HighScores
(
Spielername VARCHAR(50),
Punkte INTEGER,

PRIMARY KEY (Spielername)
);[/sql]

Erläuterung zum Code Beispiel

Obiger SQL-Befehl erstellt eine neue Tablle mit dem Namen “HighScores” und mit den zwei Spalten “Spielername“, als Zeichenkette (varchar) und “Punkte” als Integerwert für die Punkte.

Da der Spieler nur einmal in der Tabelle gelistet sein soll, wird der “Spielername” als PRIMARY KEY (Primärschlüssel) definiert. So ist nur ein Eintrag pro Spielername und Punktzahl möglich.

Oft kann es notwendig sein eine bereits existierende Tabelle zu verändern. Dies ist mit dem ALTER TABLE Befehl möglich.

Spalte hinzufügen

[sql]ALTER TABLE HighScores
#Fügt eine neue Spalte Plattform hinzu (iOS, Android, Desktop)
ADD COLUMN Plattform VARCHAR(10) NOT NULL DEFAULT ‘Desktop’
CHECK(Plattform IN (‘iOS’, ‘Android’, ‘Desktop’))
;[/sql]

Erläuterung zum Code

Wir wollen die Tabelle “HighScore” um eine neue Spalte erweitern. Dies ist mit dem Befehl ADD COLUMN möglich. Der Aufbau der Spalte ist wie, wenn man eine neue Tabelle mit CREATE TABLE erstellen würde.  Im obigen Beispiel wird also eine neue Spalte mit den Namen “Plattform” hinzugefügt, welches später zur Filterung der verschiedenen Plattformen dient. Da hier nicht unterschiedliche Daten eingetragen werden können, wird diese Spalte geprüft (CHECK) ob die zu einfügenden Daten mit den drei Angaben (‘iOS’, ‘Android’, ‘Desktop’) übereinstimmen.

Der Zusatz AFTER kann genutzt werden, um die Position der neuen Spalte zu bestimmen. Wird dieser Zusatz nicht angegeben, so wird die Spalte automatisch am Ende eingefügt.

Der Zusatz NOT NULL dient dazu, dass das Feld später nicht leer sein kann. Des Weiteren wird als Standardwert (DEFAULT) ‘Desktop’ angegeben. Werden keine Daten für das Feld Plattform übermittelt, so wird das Feld automatisch mit dem Standardwert gefüllt.

Zum Anzeigen der Tabelle dient unter anderem folgender Befehl.

[sql]#Zeigt die Tabelle an
DESCRIBE HighScores;[/sql]

Das Ergebnis sieht dann wie folgt aus.

Zusätzlich zu den Spalten “Spielername” und “Punkte“, welche wir mit dem CREATE TABLE Befehl erstellt haben, wurde nun auch die Spalte “Plattform” mit dem Standardwert “Desktop” eingebunden.

Spalte ändern

Um jedoch nun eine bereits existierende Spalte zu ändern, da vielleicht ein Schreibfehler im Spaltenname ist, kann folgender Befehl verwendet werdet.

[sql]#Spaltenname ändern
ALTER TABLE HighScores
CHANGE COLUMN Spielername Spieler VARCHAR(50)
;[/sql]

Sieht man sich nun die neue Tabelle erneut an, erhält man folgendes Ergebnis.

Datentyp ändern

Um den Datentyp zu ändern, kann folgender Befehl verwendet werden.

[sql]ALTER TABLE HighScores
DROP PRIMARY KEY,
MODIFY COLUMN Spieler INTEGER,
ADD PRIMARY KEY (Spieler)
;[/sql]

Erläuterung zum Code

Der Spieler soll durch eine ID identifiziert werden, und nicht durch seinen Spielername. Aus diesem Grund wird der Datentyp von VARCHAR auf INTEGER geändert.

Spalte löschen

Manchmal kann es nötig sein, eine Spalte einer Tabelle zu löschen. Dies kann mit folgendem Befehl realisiert werden. Das funktioniert aber nur, wenn die Spalte nicht auf eine andere Tabelle zeigt (Fremdschlüssel).

[sql]ALTER TABLE HighScores DROP COLUMN Spieler;[/sql]

Spalte mit Fremdschlüssel löschen

Handelt es sich jedoch um einen Fremdschlüssel, so muss erst der CONSTRAINT gelöscht werden.

[sql]ALTER TABLE HighScore DROP CONSTRAINT SOME_KEY[/sql]

Dann kann die Tabelle wie oben gelöscht werden.

[sql]ALTER TABLE HighScores DROP COLUMN Spieler;[/sql]

Um eine falsch erstellte Tabelle zu löschen, wird der DROP Befehl verwendet.

[creativ_alertbox icon=”warning-sign” colour=”yellow” custom_colour=””]Alle sich in der Tabelle befindlichen Daten werden gelöscht! Sollten bereits Daten in der Tabelle eingefügt worden sein, dann sollte die Tabelle angepasst (ALTER TABLE) und nicht gelöscht werden.[/creativ_alertbox]

[sql]#Löscht eine Tabelle (hier wegen eines Schreibfehlers)
DROP TABLE HighSocres;[/sql]

Ein Verweis auf einen Primärschlüssel, welcher sich in einer anderen Tabelle befindet, wird Fremdschlüssel (FOREIGN KEY) genannt. Der Fremdschlüssel kann entweder beim Erstellen der Tabelle (CREATE TABLE) zugewiesen werden, oder später via ALTER TABLE.

Für die HighScore-Datenbank erstellen wir deshalb eine neue Tabelle mit dem Namen “Spieler”.

[sql]CREATE TABLE Spieler
(
SID INTEGER AUTO_INCREMENT,
Vorname VARCHAR(30) NOT NULL,
Nachname VARCHAR(40) NOT NULL,
Accountname VARCHAR(50) NOT NULL,
Email VARCHAR(255) NOT NULL,

PRIMARY KEY(SID)
);[/sql]

Nachdem die neue Tabelle erfolgreich hinzugefügt wurde, müssen wir in der HighScore Tabelle noch den neuen Fremdschlüssel hinzufügen.

[sql]ALTER TABLE HighScores
#Fügt einen neuen Fremdschlüssel für die Spalte Spieler in der HighScores Tabelle hinzu
ADD FOREIGN KEY (Spieler)
#Welcher auf die SID des Spielers aus der Spieler Tabelle verweist.
REFERENCES Spieler(SID)
ON DELETE CASCADE
ON UPDATE CASCADE
;[/sql]

Ein Primärschlüssel darf in einer Tabelle, sprich in einem Datensatz, nur einmal vorkommen. Dabei unterscheidet man zwischen sprechenden Primärschlüssel und dem Surrogatschlüssel. Letzterer enthält eine aufsteigende, nichtssagende, Nummer.

Folgendes Beispiel soll den Unterschied erklären.

Wir erstellen zuerst eine neue Datenbank Shop und wählen diese als aktive Datenbank.

[sql]CREATE DATABASE Shop;
USE Shop;[/sql]

Dann erstellen wir in unserer Shop Datenbank eine neue Tabelle Kunde, welche die Kundendaten speichern soll (vereinfachtes Beispiel). Dabei verwenden wir als Primärschlüssel einen INTEGER, welcher automatisch um 1 (AUTO_INCREMENT) pro Datensatz erhöht wird.

[sql]CREATE TABLE Kunde
(
#Surrogatschlüssel
Kundennummer INT AUTO_INCREMENT,
#Alternativ:
#Kundennummer INT AUTO_INCREMENT PRIMARY KEY,
Vorname VARCHAR(30) NOT NULL,
Nachname VARCHAR(50) NOT NULL,
Geburtsdatum DATE NOT NULL,

PRIMARY KEY (Kundennummer)
);[/sql]

Anschließend erstellen wir eine vereinfachte Tabelle Rechnung, welche die Rechnungen für alle Kunden speichern soll. Da eine Rechnungsnummer eindeutig sein muss, eignet sich dieses Attribut sehr gut als Primärschlüssel.

[sql]CREATE TABLE Rechnung
(
Rechnungsnummer VARCHAR(10),
Rechnungsdatum DATE NOT NULL,
#Fremdschlüssel auf einen Kunden
Kundennummer INTEGER NOT NULL,

PRIMARY KEY (Rechnungsnummer),
FOREIGN KEY (Kundennummer)
REFERENCES Kunde(Kundennummer)
ON DELETE RESTRICT
ON UPDATE CASCADE
);[/sql]

Im Gegensatz zu dem Surrogatschlüssel beinhaltet der “normale” Primärschlüssel einen richtigen Wert und keine zufällige nummerische ID.

Nun werden unsere Tabellen mit Daten gefüllt. Dies wird mit dem Befehl INSERT INTO durchgeführt.

Neue Spieler anlegen

[sql]INSERT INTO Spieler
(Vorname, Nachname, Accountname, Email)
VALUES
(‘Hans’, ‘Dampf’, ‘hansdampf842’, ‘hans.d@gmail.com’),
(‘Franz’, ‘Xaver’, ‘xxAver’, ‘f.xaver@gmail.com’),
(‘Simon’, ‘Immerblau’, ‘hackedicht92’, ‘s.immerblau@gmail.com’)
;[/sql]

Erläuterung zum Code

Der Befehl INSERT INTO fügt Daten in die angegebene Tabelle ein. Dabei muss zuerst der Tabellenkopf angegeben werden, sprich welche Spalten befüllt werden sollen. Die Reihenfolge ist nicht relevant, es müssen jedoch die Daten entsprechend der Reihenfolge im Kopf kommen.

(Vorname, Nachname, Accountname, Email)

Wir wollen also nacheinander, mit einem Komma getrennt, erst den Vornamen, dann den Nachnamen, den Accountnamen und schließlich die Email-Adresse einfügen.

Die SID ist ein Surrogatschlüssel, welcher sich automatisch pro Eintrag inkrementiert. Dieser muss nicht übergeben werden.

Um nun einen Blick auf die Daten zu erhalten, muss der SELECT Befehl benutzt werden.

[sql]#Der * ist eine Wildcard, somit wird alles aus der Tabelle angezeigt.
SELECT * FROM Spieler;[/sql]

Das Ergebnis der Abfrage sieht wie folgt aus.

Neue HighScores anlegen

Nun wollen wir auch noch ein paar HighScores einfügen.

[sql]INSERT INTO HighScores
(Spieler, Punkte, Plattform)
VALUES
((SELECT SID FROM Spieler WHERE Accountname = ‘hackedicht92’), 2165321, ‘Android’),
((SELECT SID FROM Spieler WHERE Accountname = ‘xxAver’), 234324, ‘iOS’),
((SELECT SID FROM Spieler WHERE Accountname = ‘hansdampf842’), 23432, ‘Desktop’)
;[/sql]

Erläuterung zum Code

Das Einfügen der Daten unterscheidet sich hier, da wir in der HighScores Tabelle einen Fremdschlüssel auf einen Spieler setzen müssen. Da dies jedoch ein Surrogatschlüssel ist, welche man in der Regel nicht weis, muss dieser über eine Abfrageroutine ermittelt werden. Da jedoch meist der Benutzername im Programm oder auf der Webseite bekannt ist, kann dieser Wert zum Ermitteln der SID des Spielers genutzt werden.

Um bereits eingefügt Daten zu aktualisieren, kann der UPDATE Befehl verwendet werden. Wir wollen zum Beispiel den Vornamen unseres Spielers ‘Franz’ ändern, da wir vergessen haben, dass er einen Doppelnamen hat.

[sql]UPDATE Spieler
SET Vorname = ‘Franz-Huber’
WHERE Vorname = ‘Franz’
;[/sql]

Erläuterung zum Code

Wir wollen einen Datensatz (Tupel) in der Tabelle Spieler aktualisieren, somit verwenden wir den UPDATE Spieler Befehl. Nun wollen wir den Vornamen auf ‘Franz-Huber‘ ändern. Dies wird mit dem SET Befehl ermöglicht. Nun wollen wir aber nicht jeden Vornamen überschreiben, sondern nur den vom Spieler Franz. Das erzielt man mit der WHERE Bedingung.

Das Ergebnis sieht dann wie folgt aus.

Um Daten aus einer Tabelle zu löschen muss der grundsätzlich der komplette Datensatz (Tupel) gelöscht werden. Achtung! Fehlt die WHERE Bedingung werden alle Datensätze aus der Datenbank gelöscht!

DELETE FROM SpielerWHERE Accountname = 'hackedicht92';

Erläuterung zum Code

Wird der Befehl DELETE FROM tbl_name ausgeführt, so werden alle Tupel gelöscht, wo die WHERE Bedingung true ergibt.

Um Daten wie Zeit und Datum in einer Tabelle abzulegen, müssen die Daten erst konvertiert werden. Dies ist mit dem CAST Befehl möglich.

UPDATE TABLESET DATE_TEIME = CAST('2018-06-04 07:15:22' AS DATETIME)WHERE LOG_ID = 1

Ein CAST kann in jeden beliebigen Datentyp durchgeführt werden, solange der Quellwert den Vorgaben des Ziel-Datentypes entspricht.

Um nun die Daten aus einer Tabelle zu erhalten, wird der SELECT Befehl verwendet. Es kann das Format der zurückgegebenen Datensätze angepasst werden, dazu später mehr. In diesem Kapitel werden Anfragen aus einer Tabelle behandelt. Daten aus mehreren Tabellen können nur nach einem JOIN komplett angezeigt und geladen werden. Dazu mehr im Kapitel JOINS.

Einfache Abfrage

Um eine größere Auswahl zu haben, fügen wir zuerst neue Spieler und HighScores hinzu.

[sql]INSERT INTO Spieler
(Vorname, Nachname, Accountname, Email)
VALUES
(‘Kevin’, ‘Allein’, ‘kevkev’, ‘kev.a@gmail.com’),
(‘Done’, ‘Himbeer’, ‘himbeerdone23’, ‘done.hb@gmail.com’),
(‘Patrick’, ‘Praktisch’, ‘pp99’, ‘pp.99@gmail.com’)
;[/sql]

[sql]INSERT INTO HighScores
(Spieler, Punkte, Plattform)
VALUES
((SELECT SID FROM Spieler WHERE Accountname = ‘kevkev’), 25213, ‘Android’),
((SELECT SID FROM Spieler WHERE Accountname = ‘himbeerdone23’), 234324, ‘Android’),
((SELECT SID FROM Spieler WHERE Accountname = ‘pp99’), 931435, ‘Desktop’)
;[/sql]

Wir wollen nun erstmal alle Daten sehen, welche sich in der HighScore und Spieler Tabelle befinden.

[sql]#Der Stern dient als Wildcard und liefert komplette Datensätze zurück
SELECT * FROM Spieler;
SELECT * FROM HighScores;[/sql]

Diese Abfrage liefert uns alle Daten aus den jeweiligen Tabellen.

Gefilterte Abfrage

Um nun jedoch gezielt einen Wert aus einer Spalte zu erhalten, muss der SELECT Befehl wie folgt modifiziert werden.

[sql]SELECT Accountname FROM Spieler
#Diese Zeile ist optional
WHERE Accountname IS NOT NULL;[/sql]

Diese Anfrage liefert nun nur die Daten der Spalte Accountname zurück.

Benutzerdefiniertes Format (CONCAT)

Um nun die Rückgabe der Daten anzupassen, kann der SELECT Befehl mit dem CONCAT Befehl erweitert werden.

SELECT CONCAT(Accountname, ' (', Email, ')') AS 'Benutzer (Email)' FROM Spieler;

Erläuterung zum Code

Der CONCAT Befehl stellt die Daten wie in den Klammern () angegeben dar und setzt den Spaltenkopf wie nach dem AS angegeben.

Es gibt auch die Möglichkeit Einträge zu zählen. Dazu muss der COUNT Befehl verwendet werden.

SELECT COUNT(Spieler) AS 'Gelistete Spieler' FROM HighScores;

Das Abfrageergebnis zählt nun die Spieler.

Nun wollen wir wissen, wie viele Spieler pro Plattform eingetragen sind. Dazu wird der GROUP BY Befehl verwendet.

SELECT COUNT(Spieler) AS 'Gelistete Spieler', Plattform FROM HighScoresGROUP BY Plattform;

Das Ergebnis liefert uns die Anzahl der Spieler pro Plattform.

Um nun Daten aus mehreren Tabellen zu erhalten, wird ein JOIN verwendet. Es gibt vier Arten von JOINS.

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• CROSS JOIN

Illustration

INNER JOIN

Der INNER JOIN sieht alle Daten, welche eine direkte Verbindung haben. Als würde er “von oben herunter schauen”. Sprich es werden nur Datensätze zurückgegeben, für die in beiden Tabellen Einträge existieren.

LEFT / RIGHT (OUTER) JOIN

Wohingegen der LEFT JOIN “von links” auf die Verbindung der beiden Tabellen schaut. Sprich es werden alle Datensätze aus der ersten (am weitesten links stehenden) Tabelle und nur die zugeordneten Datensätze der zweiten Tabelle angezeigt. Das gleiche gilt analog für den RIGHT JOIN, jedoch von der anderen Richtung aus gesehen.

CROSS JOIN

Der CROSS JOIN zeigt als Ergebnis alle möglichen Verknüpfungen der Datensätze der beiden Tabellen an (eher unüblich).

Da wir in unserer Spieler Tabelle einen Surrogatschlüssel verwendet haben, wissen wir  aus dem reinen Datensatz der HighScore Tabelle nicht, welcher Benutzer sich wirklich dahinter befindet. Aus diesem Grund müssen wir zuerst einen INNER JOIN ausführen.

[sql]SELECT * FROM HighScores h
INNER JOIN Spieler s
ON h.Spieler = s.SID
;[/sql]

Erläuterung zum Code

Diese Abfrage von der Tabelle HighScore (h) liefert alle Datensätze (*) zurück, nachdem ein INNER JOIN zwischen der Tabelle Spieler (Referenz) und der Ausgangstabelle HighScore ausgeführt wurde. Dabei werden die beiden Tabellen mit Hilfe des Fremdschlüssels (HighScores.Spieler) auf den Primärschlüssel (Spieler.SID) verbunden.

Das Ergebnis dieser Abfrage liefert die kompletten Datensätze beider verbundenen Tabellen zurück (siehe oben). Da uns jedoch die Spieler ID (SID) nicht interessiert, werden wir die Abfrage mit dem CONCAT Befehl weiter anpassen.

[sql]SELECT h.Punkte, CONCAT(s.Accountname, ‘ (‘, s.Email, ‘)’) AS Spieler, h.Plattform
FROM HighScores h
INNER JOIN Spieler s
ON h.Spieler = s.SID
ORDER BY h.Punkte DESC
;[/sql]

Erläuterung zum Code

Wir wollen folgende Spalten in gegebener Reihenfolge anzeigen.

Punkte | Spieler | Plattform

Um dies zu ermöglichen, werden die Spaltennamen (aus der jeweiligen Tabelle) mit einem Komma getrennt nach dem SELECT Befehl aufgeführt. Da es keine Spalte Spieler im gewünschten Format gibt, muss diese mit dem CONCAT Befehl erstellt werden.

Die verwendeten Kürzel s. und h. dienen zur Identifizierung der Tabelle. Diese sind nur nötig, wenn gleiche Spaltennamen in den Tabellen verwendet wurden.

Nun soll die Ausgabe noch absteigend nach den Punkten sortiert werden, was mit dem ORDER BY Zusatz leicht möglich ist.

• DESC = Absteigend
• ASC = Aufsteigend

Das Ergebnis der Abfrage sieht nun wie folgt aus.

LEFT JOIN

Der LEFT JOIN sieht bildlich gesprochen von links auf die beiden Tabellen. Als Beispiel wird der gleiche Abfragecode wie für den INNER JOIN verwendet, nur hier wird ein LEFT JOIN ausgeführt.

[sql]SELECT CONCAT(s.Accountname, ‘ (‘, s.Email, ‘)’) AS Spieler,
LPAD(CONCAT(h.Punkte, ‘ PKT’), 15, ‘ ‘) AS Score
FROM HighScores h
LEFT JOIN Spieler s
ON s.SID = h.Spieler
;[/sql]

Das Ergebnis der Abfrage unterscheidet sich in diesem Fall nicht vom INNER JOIN, da alle sich in der HighScore befindlichen Datensätze mit welchen aus der Spieler Tabelle, verknüpft sind.

RIGHT JOIN

Der RIGHT JOIN sieht bildlich gesprochen von rechts auf die beiden Tabellen. Als Beispiel wird der gleiche Abfragecode wie für den INNER JOIN verwendet, nur hier wird ein RIGHT JOIN ausgeführt.

[sql]SELECT CONCAT(s.Accountname, ‘ (‘, s.Email, ‘)’) AS Spieler,
LPAD(CONCAT(h.Punkte, ‘ PKT’), 15, ‘ ‘) AS Score
FROM HighScores h
RIGHT JOIN Spieler s
ON s.SID = h.Spieler
;[/sql]

Hier unterscheidet sich das Abfrageergebnis zu dem INNER & LEFT JOIN, da sich in der “rechten” Tabelle, also in der Spieler Tabelle, ein weiterer Datensatz verbirgt, welcher keine Verknüpfung zu einem Datensatz in der HighScore Tabelle hat. Dieser wird durch den RIGHT JOIN trotzdem angezeigt.

Die IF-Funktion liefert entweder den Wert für TRUE oder FALSE zurück, je nach dem wie die angegebene Bedingung ausfällt.

[sql]SELECT CONCAT(IF(Punkte > 30000, Punkte, ‘unterhalb der Grenze’)) AS Punkte
FROM HighScores
ORDER BY Punkte DESC
;[/sql]

Werte summieren

Durch die SUM() Funktion ist es möglich Werte einer Spalte aufsummieren zu lassen.

Integer aufsummieren

[sql]SELECT SUM(DateDiff(minute, 0, DURATION)) As ‘TOTAL TIME’ FROM Taks
GROUP BY ID
;[/sql]

Zeiten aufsummieren

[sql]SELECT SUM(QUANTITY) As ‘TOTAL’ FROM Items
GROUP BY ID
;[/sql]