Praktikum Internet-Werkzeuge
			Aufgaben  Dokus  Links

SQL - Structured Query Language

Mit SQL existiert eine Sprache zur Bearbeitung von Daten in relationalen Datenbanksystemen, die sich an einer natürlichen Beschreibung der Aktionen (in englischer Sprache) anlehnt. Sie ist von der amerikanischen Standardisierungsbehörde ANSI genormt und wird von den meisten Datenbanken unterstützt.

In einem relationalen Datenbankmodell werden Daten in Tabellen gespeichert und diese Tabellen untereinander verknüpft. Eine Zeile einer Tabelle stellt dabei einen Datensatz dar, der über einen Eintrag in einer ausgewählten Spalte eindeutig referenziert werden kann (Primärschlüssel). Daten in anderen Spalten können auf Primärschlüssel anderer Tabellen verweisen (Fremdschlüssel) und so zwei oder mehr Tabellen miteinander verbinden.

Es existieren grundsätzlich vier Befehle, um Daten in einer Tabelle zu lesen oder zu verändern.

SELECT

Abfrage der Daten in Tabellen

INSERT

Einfügen von neuen Datensätzen in eine Tabelle

UPDATE

Datensätze in einer Tabelle verändern

DELETE

Datensätze aus einer Tabelle löschen

Weiterhin gibt es noch Befehle, die das Tabellenschema einer Datenbank verändern, also neue Tabellen erzeugen oder bestehende verändern oder löschen.

Die SQL-Statements im Einzelnen

Die folgenden Beispiele beziehen sich auf diese Beispieltabellen:

Tabelle 1: Personal ID Name Gehalt Abteilung 0 Karl 48.000 3 1 Stefanie 50.000 1 2 Thomas 32.000 2 3 Katrin 24.000 3 4 Erich 30.000 2

Tabelle 2: Abteilungen ID Abteilung 1 Mode 2 Elektro 3 Haushalt

SELECT

Das SELECT-Statement dürfte das am häufigsten benutzte sein. Mit ihm kann man Daten aus einer oder mehreren Tabellen abfragen. Dabei kann man Kriterien festlegen, nach denen Datensätze in die Ausgabetabelle mit einbezogen oder übersprungen werden. Außerdem kann man festlegen, welche Spalten der Datensätze auszugeben sind.

SELECT column,...
	  FROM table_references
	  [WHERE where_definition]
	  [ORDER BY column [ASC|DESC],...] 

Hier nun ein Beispiel, um alle Personen auszugeben. Der Stern (*) wird verwendet, wenn alle Spalten ausgegeben werden sollen.

SELECT * FROM personal

Wenn nur die ID und der Name ausgegeben werden sollen, sieht das folgendermaßen aus:

SELECT ID, name FROM personal

Jetzt sollen nur alle Mitarbeiter ausgegeben werden, die in Abteilung 3 arbeiten. Dazu wird die Auswahl mit einer WHERE-Klausel auf die Datensätze eingeschränkt, die in der Spalte Abteilung den Wert 3 haben.

SELECT ID, name FROM personal WHERE abteilung=3

Wenn auch noch die Personen mit ausgegeben werden sollen, die in Abteilung 2 arbeiten, kann man die beiden Bedingungen miteinander verknüpfen.

SELECT ID, name FROM personal WHERE abteilung=2 OR abteilung=3 oder: SELECT ID, name FROM personal WHERE abteilung IN (2,3) oder: SELECT ID, name FROM personal WHERE NOT abteilung=1

Bedingungen können sich auch auf mehrere Spalten beziehen und miteinander verknüpft werden. Nun sollen alle die Personen ausgegeben werden, die in Abteilung 2 arbeiten und mehr als DM 30.000 verdienen.

SELECT ID, name FROM personal WHERE abteilung=2 AND gehalt > 30000

Bedingungen mit Mustern

Häufig möchte man alle Datensätze angezeigt bekommen, in denen ein Feld einem bestimmten Muster entspricht, z.B. alle Personen, deren Namen mit einem "S" beginnt. SQL kennt dafür die LIKE-Klausel, die in WHERE-Bedingungen verwendet werden kann.

Um zum Beispiel alle Personen anzuzeigen, die mit "K" beginnen:

SELECT ID, name FROM personal WHERE name LIKE 'K%'

Das Prozentzeichen (%) steht dabei für beliebig viele Buchstaben.

Alle Mitarbeiter, deren Name ein "vol" enthält, erhält man mit folgender Abfrage:

SELECT ID, name FROM personal WHERE name LIKE '%vol%' besser: SELECT ID, name FROM personal WHERE LOWER (name) LIKE LOWER ('%vol%')

Der zweite Ausdruck überprüft, ob sich der String "vol" unabhängig von der Groß-und Kleinschreibung im Feld name befindet. Dazu wird die Funktion LOWER() benutzt, die einen String in Kleinbuchstaben umwandelt.

Joins über mehrere Tabellen

Eine SELECT-Abfrage kann sich über mehr als eine Tabelle erstrecken. Um dies zu erreichen, werden mehrere Tabellen in der FROM-Klausel angegeben. Technisch wird dabei ein kartesisches Produkt der Tabellen gebildet, also jeder Datensatz mit allen anderen aus den anderen Tabellen verknüpft. Um nicht eine riesengroße Liste zu erhalten, sollte es mindestens einen Wert in beiden Tabellen geben, der die Datensätze miteinander logisch verknüpft. In dem Beispiel wäre das der Wert von Abteilung in der Personal-Tabelle und der Wert von ID in der Abteilungen-Tabelle. In der WHERE-Klausel werden dann die Tupel des kartesischen Produktes ausgewählt, bei denen der Wert gleich ist, also einer Person nur der Datensatz aus der Abteilungstabelle zugeordnet, der "passt".

Das ganze einmal in SQL-Syntax. Ausgegeben werden alle Personen, diesmal soll jedoch nicht die Abteilungsnummer sondern der Name der Abteilung angezeigt werden.

SELECT personal.ID, personal.name, abteilungen.abteilung    FROM personal, abteilungen    WHERE personal.abteilung = abteilungen.ID

Die Spaltennamen werden jetzt qualifiziert, d.h. ihnen wird der Tabellenname vorangestellt, um für das Datenbanksystem deutlich zu machen, aus welcher Tabelle die Informationen geholt werden sollen. Ist der Spaltenname sowieso eindeutig (d.h. kommt nur in einer der verbundenen Tabellen vor), kann der Tabellenbezeichner auch weggelassen werden.

Da Tabellennamen häufig recht lang sind, ist es unbequem, sie in der Spaltenauswahl immer komplett hinschreiben zu müssen. Daher kann man sogenannte Tabellen-Aliase definieren und diese verwenden. Die Query von eben sähe dann wie folgt aus (die Alias-Definitionen sind fett hervorgehoben.Da die Spalte "name" eindeutig ist, ist sie nicht weiter qualifiziert):

SELECT p.ID, name, a.abteilung    FROM personal p, abteilungen a    WHERE p.abteilung = a.ID

Sortieren der Datensätze

Für die in einer Datenbank gespeicherten Datensätze liegt keine implizite Ordnung vor, d.h. es ist nicht vorhersagbar, in welcher Reihenfolge das Datenbanksystem die Datensätze zurückliefert. In einer SELECT-Query kann jedoch eine Reihenfolge festgelegt werden. Dazu wird die ORDER BY-Klausel verwendet.

Es sollen alle Personen, sortiert nach Namen in aufsteigender Reihenfolge ausgegeben werden. Das Attribut ASC steht dabei für "ascending" (also aufsteigend). Eine absteigende Sortierung würde man mit DESC (für "descending") erreichen.

SELECT ID, name FROM personal ORDER BY name ASC

Es kann auch über mehrere Spalten sortiert werden, wobei die Reihenfolge nach der ORDER BY-Klausel auch die Sortierreihenfolge angibt. Um zum Beispiel alle Personen erst nach den Namen der Abteilungen und dann innerhalb der Abteilungen nach ihren Namen zu sortieren, müsste man folgende Query losschicken:

SELECT a.abteilung, name FROM personal p, abteilungen a    WHERE a.ID=p.abteilung ORDER BY a.abteilung ASC, name ASC

INSERT

Mit dem INSERT-Statement werden neue Datensätze in eine Tabelle eingefügt.

INSERT INTO table VALUES (expression,...)

Die Personal-Tabelle soll um einen neuen Mitarbeiter erweitert werden.

INSERT INTO personal VALUES (5,'Reinhold',27.000,2)

Das Hinzufügen einer neuen Abteilung ist ähnlich einfach:

INSERT INTO abteilungen VALUES (4,'Garten')

UPDATE

Mit UPDATE werden bereits existierende Datensätze in einer Tabelle verändert. Dabei wird über eine WHERE-Klausel festgelegt, welche Datensätze verändert werden sollen, und welche neuen Werte in welchen Spalten eingetragen werden sollen.

UPDATE table SET column=expression,... WHERE where_definition

Den Mitarbeiter Erich von Abteilung 2 in Abteilung 3 zu versetzen, sähe zum Beispiel wie folgt aus:

UPDATE personal SET abteilung=3 WHERE name='Erich'

In einem Update können auch mehrere Spalten auf einmal verändert werden. Zu der Versetzung bekommt Erich auch noch eine Gehaltserhöhung:

UPDATE personal SET abteilung=3, gehalt=35000 WHERE name='Erich'

DELETE

Datensätze können mit DELETE wieder aus einer Tabelle gelöscht werden. Welche Zeilen von der Löschung betroffen sein sollen, wird wieder über eine WHERE-Klausel festgelegt.

DELETE FROM table_name WHERE where_definition 

Der gerade erst eingestellte Reinhold wird nach Ablauf der Probezeit wieder entlassen, sein Datensatz soll aus der Personal-Tabelle gelöscht werden. Da ja theoretisch noch mehrere Mitarbeiter den gleichen Namen haben könnten, ist es nicht sinnvoll, alle die zu löschen, die Reinhold heißen. Besser ist es, die zu löschenden Datensätze über den Primärschlüssel (hier die ID) auszuwählen.

DELETE FROM personal WHERE ID=5

Tips und Tricks

In MySQL werden viele Funktionen definiert, die Ihr z.B. in WHERE-Klauseln benutzen könnt. Details dazu finden sich in der MySQL - Dokumentation in den Kapiteln 12 und 13.

Datumsfunktionen:

NOW() ergibt das aktuelle Datum, und TO_DAYS() wandelt ein Datum in Tage seit Null um. Der folgende Code wählt Zeilen aus der Tabelle table aus, deren Datumsfeld date_col ein Datum nicht älter als 30 Tage enthält:

SELECT * FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30

Auch die folgende Anweisung ist möglich und erhöht alle Preise von Volkerts Anzeigen um 40 Pfennig:

UPDATE anzeigen SET preis=preis+0.4 WHERE verfasser='volkert'