Previous: SQL benutzen Up: Praktischer Teil SQL Next: Tips & Tricks

SQL-Befehle

Sobald man mit dem DBMS verbunden ist, kann man die Befehle eingeben, die auch von PHP aus aufgerufen werden.

Bei der dialogorientierten Benutzung und im Batch-Betrieb müssen die Befehle immer mit einem ; abgeschlossen werden. Man kann ohne Probleme einen Befehl in mehrere Zeilen schreiben, indem man erst am Ende das Semikolon setzt.

Als theoretische Grundlage wird die Beispiel-Datenbank, die in Kapitel 4.6 beschrieben wurde, benutzt.

CREATE DATABASE

Was eine Datenbank theoretisch ist, haben wir im letzten Kapitel 4 gelernt. Wie aber sieht es auf dem Server, also technisch, aus? Im Falle von MySQL entspricht eine Datenbank hier ganz profan einem bestimmten Unterverzeichnis der MySQL-Installation. Relationen dieser Datenbank, die Tabellen, sind als Dateien realisiert, die sich in einem solchen Unterverzeichnis befinden. Diese Dateien bestehen aus Binärcode, d.h. sie können und sollten ausschließlich von MySQL selbst gelesen werden (anders als etwa .sql Dateien).

Im Allgemeinen wird wohl eine Datenbank vom Administrator (beim Jugendnetz: wir) eingerichtet werden. Von diesem bekommst du dann auch die nötigen Zugangsdaten wie Datenbankname, Benutzername und Paßwort. Bist du selbst jedoch der Administrator des Systems (wie z.B. bei der Installation eines Offline-Testwebservers), mußt du das Einrichten der Datenbank auch selbst erledigen; der MySQL-Befehl hierzu lautet CREATE DATABASE, gefolgt von dem gewünschten Namen der Datenbank.

Beim Anlegen wie auch beim späteren Benutzen der Datenbank ist folgendes zu bedenken: Unix/Linux unterscheidet Groß- und Kleinschreibung. Aufgrund o.g. Dateisystem-Abhängigkeit der Datenbank (Verzeichnischarakter) wäre die Datenbank ,,Termine`` nämlich eine andere als ,,termine``.
Als Grundregel solltest du dir einfach angewöhnen, bei jeglichen Eingaben von Datenbank-, Tabellen- und Feldnamen grundsätzlich auf die Groß- und Kleinschreibung zu achten und diese konsistent zu halten, also auch in allen SQL-Befehlen diese Namen genau so zu verwenden, wie sie angelegt wurden. Selbst wenn Windows (ja, auch hier läuft MySQL!) weniger restriktiv ist, vermeidest du durch dieses Vorgehen späteren Ärger z.B. bei Portierungen von Datenbanken nach Unix/Linux.

Nach dem Einrichten der Datenbank ist diese bereits benutzbar, d.h. du kannst jetzt Tabellen mit entsprechenden Feldern anlegen (mehr dazu im nächsten Abschnitt: 6.2). Arbeitest du hingegen auf dem mysql-Prompt, mußt du zuerst die Datenbank wechseln: das geht mittels USE <DB-Name>.

CREATE TABLE

Als erstes müssen wir natürlich die Tabellen erstellen. Dazu dient der Befehl CREATE TABLE. Die Syntax lautet:

CREATE TABLE table_name (create_definition,...)

Für table_name ist selbstverständlich der Name der zu erstellenden Tabelle einzusetzen. Die drei Punkte hinter `create_definition` bedeuten, daß mehrere `create_definitions` durch Kommas getrennt hintereinander stehen können. Die `create_definitions` müssen innerhalb der runden Klammern stehen!

Für `create_definition` kann man folgendes einsetzen:

     feld_name Typ [NOT NULL] [AUTO_INCREMENT] [UNIQUE] [PRIMARY KEY]
oder PRIMARY KEY (feld_name,...)
oder UNIQUE (feld_name,...)
oder FOREIGN KEY (feld_name,...) [reference_definition]

Für `reference_definition` muß man folgendes einsetzen:

REFERENCES table_name[(index_spalte,...)]

Mit NOT NULL wird festgelegt, daß ein Wert (das kann auch ein leerer sein) eingetragen werden muß. NULL ist nicht mit der Zahl 0 zu verwechseln; NULL bedeutet einfach ,,kein Wert``. Wenn bei INSERT kein Wert für dieses Feld angegeben wurde, wird der Standardwert genommen. Es gibt keine Fehlermeldung (nur MySQL)!

Wenn ein Zahlenfeld mit dem Schlüsselwort AUTO_INCREMENT angelegt wurde, wird, solange kein Wert für dieses Feld angeben wurde, der höchste Wert +1 genommen. AUTO_INCREMENT kann nur einmal pro Tabelle in einem Primärschlüsselfeld verwendet werden. AUTO_INCREMENT gibt es nur in MySQL.

Wenn UNIQUE bei einem Feld angegeben wurde, darf ein Wert in maximal einem Tupel vorkommen, d.h. jeder Wert muß eindeutig sein. Bei Primärschlüsseln wird automatisch UNIQUE angenommen.

Mit PRIMARY KEY wird der Primärschlüssel festgelegt. Bei zusammengesetzten Primärschlüsseln sind alle Felder, die Teil des Schlüssels sind, anzugeben. Primärschlüssel müssen eindeutig sein und es muß NOT NULL angegeben werden.

UNIQUE und PRIMARY KEY können entweder direkt hinter einer Spaltendefinition angegeben werden oder getrennt davon. Sobald erst eine Kombination mehrerer Spalten den Primärschlüssel bildet, d.h. eindeutig ist, muß der Primärschlüssel getrennt angegeben werden.

Mit FOREIGN KEY wird die Fremdschlüsselbeziehung angegeben. MySQL unterstützt bis jetzt noch keine Fremdschlüssel und ignoriert folglich die Angaben.

Für Typ ist einer der Datentypen aus Tabelle 6.1 zu verwenden.

**Tabelle 6.1:** Verfügbare Datentypen in SQL
Typ	Beschreibung
TINYINT	-128 .. 127
TINYINT UNSIGNED	0 .. 255
INT	-2.147.483.648 .. 2.147.483.647
INT UNSIGNED	0 .. 4.294.967.295
BIGINT	-3402823e+31 .. 3402823e+31
DECIMAL(length,dec)	Kommazahl der Länge `length` und mit `dec` Dezimalstellen; die Länge beträgt: Stellen vor dem Komma + 1 Stelle für Komma + Stellen nach dem Komma
VARCHAR(NUM) [BINARY]	Zeichenkette mit max `NUM` Stellen ( $1\leq NUM\leq 255$ ). Alle Leerstellen am Ende werden gelöscht. Solange nicht `BINARY` angegeben wurde, wird bei Vergleichen nicht auf Groß-/Kleinschreibung geachtet.
TEXT	Text mit einer max. Länge von 65535 Zeichen
MEDIUMTEXT	Text mit einer max. Länge von 16.777.216 Zeichen
TIME	Zeit; Format: HH:MM:SS, HHMMSS, HHMM oder HH
DATE	Datum; Format: YYYY-MM-DD, wobei `-` jedes nicht numerische Zeichen sein kann
TIMESTAMP	setzt einen Datumswert beim Einfügen/Updaten einzelner Felder automatisch auf das Systemdatum.
	Format: YYYYMMDDHHMMSS. Wenn mehrere Felder den Typ `TIMESTAMP` haben, wird immer nur das erste automatisch geändert!

Die Bedeutung der YMHSDs ist in der Tabelle 6.2 erläutert.

**Tabelle 6.2:** Bedeutung der YMHSDs
D	Tag (engl. day)
H	Stunde (engl. hour)
M	Monat (engl. month) oder Minute (engl. minute)
S	Sekunde (engl. second)
Y	Jahr (engl. year)

Wenn ein Buchstabe mehrmals vorkommt, so bedeutet das, daß es mehrere Stellen gibt.

Ein kleines Beispiel:
Es soll eine Tabelle `Mitarbeiter` erstellt werden. Zu jedem Mitarbeiter sind Name, Telefonnummer und die Mitarbeiter-Nummer (kurz MNr) zu speichern. Die MNr ist Primärschlüssel und soll automatisch um 1 erhöht werden. Der Name ist nicht optional. Daraus ergibt sich folgender Befehl:

CREATE TABLE Mitarbeiter (
   MNr        INT NOT NULL AUTO_INCREMENT,
   VNr        INT,
   AbtNr      INT NOT NULL,
   Name       VARCHAR(30) NOT NULL,
   GebDat     DATE,
   Telefon    VARCHAR(30),
   PRIMARY KEY(MNr),
   FOREIGN KEY(VNr) REFERENCES Mitarbeiter(MNr),
   FOREIGN KEY(AbtNr) REFERENCES Abteilung(AbtNr)
);

Die mehrfachen Leerstellen sind optional. Sie sind hier nur der Übersichtlichkeit halber eingefügt.

Wenn man versucht, die Mitarbeiter-Tabelle bei einem DBMS, das Fremdschlüssel unterstützt, als erstes anzulegen, wird man eine Fehlermeldung erhalten, weil die referenzierte Tabelle noch nicht existiert. In diesem Fall kann man die Tabellenstruktur nachträglich mit ALTER TABLE (6.11) anpassen.

Im Anhang A.1 sind noch einmal sämtliche `CREATE TABLE` -Definitionen aufgeführt, die für unser Beispiel benötigt werden. Es sind der Vollständigkeit halber auch sämtliche Fremdschlüssel mit angegeben, obwohl sie unter MySQL keine Bedeutung haben.

SHOW

Man kann sich die nun erstellten Tabellen und deren Felder auch ansehen. Dazu dient der Befehl SHOW. Die Syntax lautet wie folgt (nur MySQL):

     SHOW TABLES

oder

     SHOW COLUMNS FROM table

SHOW TABLES zeigt alle angelegten Tabellen an. Mit SHOW COLUMNS FROM table lassen sich die Felder in der Tabelle `table` anzeigen.

Nach unserem obigen CREATE TABLE würde sich folgende Ausgabe ergeben:

mysql> SHOW TABLES;
+--------------+
| Tables in cr |
+--------------+
| Mitarbeiter  |
+--------------+
1 row in set (0.01 sec)

mysql> SHOW COLUMNS FROM Mitarbeiter;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| MNr     | int(11)     |      | PRI | 0       | auto_increment |
| VNr     | int(11)     | YES  |     | NULL    |                |
| AbtNr   | int(11)     |      |     | 0       |                |
| Name    | varchar(30) |      |     |         |                |
| GebDat  | date        | YES  |     | NULL    |                |
| Telefon | varchar(30) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

In der unteren Tabelle ist zu erkennen, welche Felder mit welchen Typen und Attributen in der jeweiligen Tabelle vorhanden sind. Nur für `VNr`, `GebDat` und ` Telefon` sind NULL-Werte zugelassen. Der Primärschlüssel ist `MNr`. Wie man sieht, wurden die Fremdschlüssel ignoriert.

DROP TABLE

Da wir jetzt wissen, wie wir Tabellen erstellen, ist es auch wichtig zu wissen, wie wir sie wieder loswerden. Dies geschieht mit dem Befehl DROP TABLE.

Die Syntax lautet:

DROP TABLE table_name

Achtung: Es erfolgt keine Abfrage, ob du dies wirklich tun willst! Mit der Tabelle werden natürlich auch alle Daten der Tabelle unwiderruflich gelöscht!

INSERT INTO

Tabellen ohne Daten haben eigentlich keinen Sinn. Deshalb wollen wir mit dem Befehl INSERT INTO ein paar Daten einfügen. Die Syntax lautet:

INSERT INTO table_name [ (feld_name,...) ] VALUES (werte,...)

Die Feldnamen können weggelassen werden, wenn in alle Felder etwas eingefügt werden soll. In diesem Fall muß man aber die Werte in genau der Reihenfolge eingeben, in der die Felder in der CREATE TABLE Anweisung definiert wurden.

In der Regel, vor allem aber in Programmen, empfiehlt es sich, die Feldnamen anzugeben, weil man sich nie darauf verlassen sollte, daß sich die Reihenfolge der Spalten nicht ändert.

Bei den Werten müssen Zeichenketten und Datum in Hochkommata (Anführungszeichen) stehen, nur für Zahlen gilt das nicht.

In unsere oben erstellte Tabelle sollen folgende Werte eingefügt werden:

Name GebDat Telefon

Christoph Reeg 13.5.1979

junetz.de 5.3.1998 069/764758

Name	GebDat	Telefon
Christoph Reeg	13.5.1979
junetz.de	5.3.1998	069/764758

Damit ergeben sich folgende Befehle und Ausgaben (wie man hier bereits sieht, gibt der MySQL-Prompt automatisch die Zeichenfolge `->` aus, wenn ein mehrzeiliger Befehl eingegeben wird):

mysql> INSERT INTO Mitarbeiter (Name,GebDat)
    ->  VALUES ('Christoph Reeg','1979-5-13');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Mitarbeiter (Name,GebDat,Telefon)
    ->  VALUES ('junetz.de','1998-3-5','069/764758');
Query OK, 1 row affected (0.00 sec)

Wegen fehlender Fremdschlüssel konnten die Mitarbeiter in die DB eingefügt werden, obwohl wir keine Abteilung für sie angegeben haben. Deshalb muß man immer selbst für die Einhaltung der Beziehung(en) sorgen!

Um die Datenbasis für unser Beispiel in die DB einzutragen, wird der Befehl noch ein paarmal benutzt. Diese konkrete Anwendung kann im Anhang A.2 nachgeschlagen werden.

Bei den folgenden Kurzbeispielen gehen wir von der Datenbasis unseres Beispiels aus.

SELECT

Der Befehl SELECT ist der mächtigste Befehl in SQL. Die Grund-Syntax lautet:

SELECT [DISTINCT | ALL] select_expression,... FROM tables ...
[WHERE where_definition]
[GROUP BY feld_name,...]
[ORDER BY feld_name [ASC | DESC] ,...]
[LIMIT [offset,] rows]

Die kürzestmögliche SELECT-Anweisung lautet:

SELECT * FROM table

Es sollen z.B. alle Mitarbeiter ausgegeben werden:

mysql> select * from Mitarbeiter;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   2 |    1 |     1 | junetz.de      | 1998-03-05 | 069/764758   |
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
|   4 |    3 |     1 | JCP            | NULL       | 069/764758   |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
+-----+------+-------+----------------+------------+--------------+
6 rows in set (0.00 sec)

mysql>

DISTINCT und ALL sind exklusive, optionale Parameter; soll heißen, es kann immer nur einer, muß aber keiner benutzt werden. DISTINCT sorgt dafür, daß jede identische Zeile nur einmal ausgegeben wird. Mit ALL werden die sich wiederholenden Werte auch mehrmals ausgegeben. Ohne Parameter verhält sich das DBMS normalerweise, als ob man ALL angeben würde.

Es sollen alle Telefonnummern aus der Mitarbeiter-Tabelle ausgeben werden:

mysql> SELECT Telefon from Mitarbeiter;
+--------------+
| Telefon      |
+--------------+
| NULL         |
| 069/764758   |
| NULL         |
| 069/764758   |
| 06196/671797 |
| 069/97640232 |
+--------------+
6 rows in set (0.01 sec)

mysql> SELECT ALL Telefon from Mitarbeiter;
+--------------+
| Telefon      |
+--------------+
| NULL         |
| 069/764758   |
| NULL         |
| 069/764758   |
| 06196/671797 |
| 069/97640232 |
+--------------+
6 rows in set (0.00 sec)

mysql> SELECT DISTINCT Telefon from Mitarbeiter;
+--------------+
| Telefon      |
+--------------+
| NULL         |
| 06196/671797 |
| 069/764758   |
| 069/97640232 |
+--------------+
4 rows in set (0.05 sec)

mysql>

ORDER BY

Mit ORDER BY wird festgelegt, nach welcher Spalte bzw. welchen Spalten sortiert werden soll. Mit ASC werden die Zeilen aufsteigend, mit DESC absteigend sortiert. Ist nichts angegeben, wird aufsteigend sortiert.

Als Beispiel alle Mitarbeiter, nach Namen sortiert:

mysql> SELECT * from Mitarbeiter ORDER BY Name;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   4 |    3 |     1 | JCP            | NULL       | 069/764758   |
|   2 |    1 |     1 | junetz.de      | 1998-03-05 | 069/764758   |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
+-----+------+-------+----------------+------------+--------------+
6 rows in set (0.00 sec)

mysql> SELECT * from Mitarbeiter ORDER BY Name ASC;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   4 |    3 |     1 | JCP            | NULL       | 069/764758   |
|   2 |    1 |     1 | junetz.de      | 1998-03-05 | 069/764758   |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
+-----+------+-------+----------------+------------+--------------+
6 rows in set (0.01 sec)

mysql> SELECT * from Mitarbeiter ORDER BY Name DESC;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   2 |    1 |     1 | junetz.de      | 1998-03-05 | 069/764758   |
|   4 |    3 |     1 | JCP            | NULL       | 069/764758   |
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
+-----+------+-------+----------------+------------+--------------+
6 rows in set (0.00 sec)

mysql>

Als letztes soll nach dem Geburtsdatum sortiert werden.

mysql>  SELECT * from Mitarbeiter ORDER BY GebDat;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
|   4 |    3 |     1 | JCP            | NULL       | 069/764758   |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   2 |    1 |     1 | junetz.de      | 1998-03-05 | 069/764758   |
+-----+------+-------+----------------+------------+--------------+
6 rows in set (0.01 sec)

mysql>

Die ersten vier Mitarbeiter haben kein Geburtsdatum eingetragen. Um sie dennoch irgendwie zu sortieren, ist bei ihnen ein zweites Sortierkriterium notwendig. Das kann einfach mit einem Komma getrennt hinter ORDER BY geschrieben werden.

Um zum Beispiel nach Geburtsdatum und, wenn das nicht eindeutig ist, dann nach Namen zu sortieren, ist folgende Anweisung notwendig:

mysql>  SELECT * from Mitarbeiter ORDER BY GebDat,Name;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   4 |    3 |     1 | JCP            | NULL       | 069/764758   |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   2 |    1 |     1 | junetz.de      | 1998-03-05 | 069/764758   |
+-----+------+-------+----------------+------------+--------------+
6 rows in set (0.00 sec)

mysql>

GROUP BY

Die GROUP BY-Anweisung wird nur in Verbindung mit den Gruppenfunktionen aus Kapitel 6.7.5 benutzt, um mehrere Tupel mit identischen Attributen zu Gruppen zusammenzufassen.

Ein kleines Beispiel:
Es soll ausgegeben werden, wie viele Mitarbeiter in den jeweiligen Abteilungen arbeiten.

mysql> SELECT count(*), AbtNr from Mitarbeiter GROUP BY AbtNr;
+----------+-------+
| count(*) | AbtNr |
+----------+-------+
|        3 |     1 |
|        2 |     2 |
|        1 |     3 |
+----------+-------+
3 rows in set (0.00 sec)

mysql>

Die sechs Tupel (Datensätze), die wir in der Mitarbeiter-Tabelle haben, werden zu drei Gruppen zusammengefaßt; anschließend wird die Anzahl der Tupel pro Gruppe ausgeben.

Eigentlich dürfen in der select_expression nur Spalten angegeben werden, die in GROUP BY auftauchen, MySQL ignoriert dies allerdings. Folgende Anweisung wäre eigentlich unzulässig, zudem ist sie völlig sinnlos - was besagt schon die Spalte `Name`?

mysql> SELECT Name,count(*),AbtNr from Mitarbeiter GROUP BY AbtNr;
+----------------+----------+-------+
| Name           | count(*) | AbtNr |
+----------------+----------+-------+
| junetz.de      |        3 |     1 |
| Maier          |        2 |     2 |
| Christoph Reeg |        1 |     3 |
+----------------+----------+-------+
3 rows in set (0.00 sec)

mysql>

LIMIT

Mit LIMIT [offset,] rows kann angegeben werden, wie viele Zeilen man maximal von der SELECT-Anweisung zurückgeliefert haben will. Mit `offset` kann man festlegen, ab welcher Zeile angefangen werden soll. Ohne Angaben wird 0 angenommen. Mit `rows` legt man fest, wie viele Zeilen man maximal ausgegeben haben möchte.

mysql> select * from table LIMIT 5,10;  # gibt die Zeilen 6-15 zurück
mysql> select * from table LIMIT 5;     # gibt die ersten 5 Zeilen zurück
mysql> select * from table LIMIT 0,5;   # dasselbe nochmal

select_expression

Für select_expression muß eingesetzt werden, was angezeigt werden soll. Im einfachsten Fall ist das ein `*`. Damit wird einfach alles angezeigt.

Im Normalfall werden die benötigten Spalten durch Kommas getrennt angegeben. Sobald die Spaltennamen nicht mehr eindeutig sind, weil mehrere Tabellen angegeben wurden, muß der Tabellenname, gefolgt von einem Punkt, vorangestellt werden; es können dabei auch sog. Aliase verwendet werden. Mehr dazu weiter unten.

Neben den reinen Spaltenwerten können auch Werte, die aus den Spaltenwerten berechnet oder durch sonstige Funktionen erstellt wurden, ausgegeben werden. In Kapitel 6.7 werden die Funktionen ausführlich beschrieben.

Als Beispiel sollen der Mitarbeitername und die Telefonnummer ausgegeben werden. Die beiden Ausgaben sind äquivalent, wie man unschwer erkennen kann. Solange nur aus einer Tabelle ausgelesen wird, ist die erstere allerdings eher empfehlenswert, da nicht zu umständlich.

mysql> select Name, Telefon from Mitarbeiter;
+----------------+--------------+
| Name           | Telefon      |
+----------------+--------------+
| Christoph Reeg | NULL         |
| junetz.de      | 069/764758   |
| Uli            | NULL         |
| JCP            | 069/764758   |
| Maier          | 06196/671797 |
| Meier          | 069/97640232 |
+----------------+--------------+
6 rows in set (0.02 sec)

mysql> select Mitarbeiter.Name, Mitarbeiter.Telefon from Mitarbeiter;
+----------------+--------------+
| Name           | Telefon      |
+----------------+--------------+
| Christoph Reeg | NULL         |
| junetz.de      | 069/764758   |
| Uli            | NULL         |
| JCP            | 069/764758   |
| Maier          | 06196/671797 |
| Meier          | 069/97640232 |
+----------------+--------------+
6 rows in set (0.00 sec)

mysql>

Alias

Alias bedeutet so viel wie ein ,,anderer Name``. Man kann sowohl für Spalten als auch für Tabellen Aliase definieren.

Tabellen-Alias

Tabellen-Aliase können sowohl bei der select_expression als auch bei der `where_definition` zur eindeutigen Spaltenbeschreibung anstelle des Tabellennamens verwendet werden. Aliase werden verwendet, weil sie in der Regel kürzer sind als der Spaltenname. Aliase werden bei tables mit einer Leerstelle getrennt hinter dem Tabellennamen eingegeben.

Die folgenden zwei Anweisungen sind völlig identisch, abgesehen davon, daß erstere kürzer ist. Der einzige Unterschied liegt in den ersten beiden Zeilen. Im ersten Beispiel wird bei der FROM-Anweisung ein Alias definiert, welches in der ersten Zeile bei der select_expression benutzt wird.

mysql> select M.Name, M.Telefon, M.AbtNr
    -> FROM Mitarbeiter M
    -> WHERE M.AbtNr = 1;
+-----------+------------+-------+
| Name      | Telefon    | AbtNr |
+-----------+------------+-------+
| junetz.de | 069/764758 |     1 |
| Uli       | NULL       |     1 |
| JCP       | 069/764758 |     1 |
+-----------+------------+-------+
3 rows in set (0.00 sec)

mysql> select Mitarbeiter.Name, Mitarbeiter.Telefon,
    ->        Mitarbeiter.AbtNr
    -> FROM Mitarbeiter
    -> WHERE Mitarbeiter.AbtNr = 1;
+-----------+------------+-------+
| Name      | Telefon    | AbtNr |
+-----------+------------+-------+
| junetz.de | 069/764758 |     1 |
| Uli       | NULL       |     1 |
| JCP       | 069/764758 |     1 |
+-----------+------------+-------+
3 rows in set (0.01 sec)

mysql>

Spalten-Alias

Wenn man die Überschrift der Spalten ändern will, braucht man Spalten-Aliase. Auf den ersten Blick mag es vielleicht egal sein, wie die Spalten heißen. Später in Verbindung mit PHP ist das allerdings wichtig und sobald Funktionen in der Abfrage verwendet werden, sind die Spaltenüberschriften auch nicht mehr so schön.

Zum Umbenennen der Spalten wird einfach hinter den Spaltennamen bzw. Ausdruck der Aliasname geschrieben. Alternativ kann auch AS aliasname benutzt werden.

Ein kleines Beispiel:
Es soll die Anzahl der Mitarbeiter ausgegeben werden. Dazu wird eine Funktion benötigt (siehe Kapitel 6.7, das soll uns aber nicht weiter stören).

mysql> SELECT count(*)
    -> FROM Mitarbeiter;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql>

Das einzige störende ist, daß die Spalte ,,count(*)`` heißt. Viel schöner wäre es doch, wenn sie z.B. ,,Anzahl`` heißen würde:

mysql> SELECT count(*) Anzahl
    -> FROM Mitarbeiter;
+--------+
| Anzahl |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT count(*) AS Anzahl
    -> FROM Mitarbeiter;
+--------+
| Anzahl |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

mysql>

Wie man unschwer erkennen kann, ist das AS optional.

where_definition

Da es nicht immer sinnvoll ist, alle Zeilen auszugeben, kann man über die `where_definition` angeben, welche Bedingungen erfüllt sein müssen, damit die Zeile ausgegeben wird.

Die `where_definition` wird auch beim Löschen (DELETE) und Ändern (UPDATE) von ausgewählten Datensätzen gebraucht.

Eine Bedingung kann aus mehreren Teilbedingungen, die mit AND und OR verknüpft werden müssen, bestehen. Eine Teilbedingung besteht aus einem Spaltennamen, einem Operator sowie entweder einer Konstanten, einer weiteren Spalte oder einer Funktion. Die Teilbedingungen können auch mit NOT verneint werden. Schreibfaule können an Stelle von NOT auch einfach ! verwenden; das Ergebnis ist dasselbe. Die Reihenfolge der Teilbedingungen kann durch Klammern beeinflußt werden. Als Operatoren stehen die Vergleichsoperatoren sowie die Operatoren `LIKE`, `BETWEEN` und `IN` zur Auswahl. Alle Vergleichsoperatoren aus Tabelle 6.3 stehen zur Verfügung. Bei Vergleichen mit Strings (=VARCHAR) wird im Normalfall nicht auf die Groß-/Kleinschreibung geachtet. Wenn man jedoch unterscheiden will, so muß beim Anlegen der Tabelle bei VARCHAR die Option BINARY angegeben werden.

**Tabelle 6.3:** Verfügbare Vergleichsoperatoren in SQL
Operator	Bedeutung	verneinender Operator
=	gleich	<> bzw. !=
<> oder !=	ungleich	=
>	größer	<=
<	kleiner	>=
>=	größer gleich	<
<=	kleiner gleich	>

Es sollen alle Mitarbeiter ausgegeben werden, bei denen die Abteilungsnummer größer als 2 ist:

mysql> SELECT Name, AbtNr
    -> FROM Mitarbeiter
    -> WHERE AbtNr > 2;
+----------------+-------+
| Name           | AbtNr |
+----------------+-------+
| Christoph Reeg |     3 |
+----------------+-------+
1 row in set (0.00 sec)

mysql> SELECT Name, AbtNr
    -> FROM Mitarbeiter
    -> WHERE NOT (AbtNr < 3);
+----------------+-------+
| Name           | AbtNr |
+----------------+-------+
| Christoph Reeg |     3 |
+----------------+-------+
1 row in set (0.00 sec)

mysql>

Es sollen alle Mitarbeiter ausgegeben werden, die der Abteilung 1 angehören und als Vorgesetztennummer ebenfalls die 1 haben.

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE AbtNr = 1
    ->   AND VNr = 1;
+-----------+-------+------+
| Name      | AbtNr | VNr  |
+-----------+-------+------+
| junetz.de |     1 |    1 |
| Uli       |     1 |    1 |
+-----------+-------+------+
2 rows in set (0.02 sec)

mysql>

Es sollen alle Mitarbeiter ausgegeben werden, die keinen Vorgesetzten haben oder der Abteilung 1 angehören:

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE AbtNr = 1
    ->    OR VNr IS NULL;
+----------------+-------+------+
| Name           | AbtNr | VNr  |
+----------------+-------+------+
| Christoph Reeg |     3 | NULL |
| junetz.de      |     1 |    1 |
| Uli            |     1 |    1 |
| JCP            |     1 |    3 |
+----------------+-------+------+
4 rows in set (0.01 sec)

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE NOT (AbtNr <> 1)
    ->   OR VNr IS NULL;
+----------------+-------+------+
| Name           | AbtNr | VNr  |
+----------------+-------+------+
| Christoph Reeg |     3 | NULL |
| junetz.de      |     1 |    1 |
| Uli            |     1 |    1 |
| JCP            |     1 |    3 |
+----------------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE NOT (AbtNr <> 1 AND VNr IS NOT NULL);
+----------------+-------+------+
| Name           | AbtNr | VNr  |
+----------------+-------+------+
| Christoph Reeg |     3 | NULL |
| junetz.de      |     1 |    1 |
| Uli            |     1 |    1 |
| JCP            |     1 |    3 |
+----------------+-------+------+
4 rows in set (0.00 sec)

mysql>

Bei der Überprüfung auf keinen Vorgesetzten ist IS NULL verwendet worden, da bei Vergleichen mit NULL-Werten nicht mit den normalen Operatoren gearbeitet werden kann. Statt dessen ist nur IS NULL oder, verneint, IS NOT NULL möglich.

Es gibt mit Sicherheit noch 1001 andere Möglichkeiten, auf diese Lösung zu kommen. Da der erste Korrekturleser mir das nicht glauben wollte, hier noch ein paar Möglichkeiten mehr:

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE NOT (AbtNr < 1 OR AbtNr > 1)
    ->    OR VNr IS NULL;
+----------------+-------+------+
| Name           | AbtNr | VNr  |
+----------------+-------+------+
| Christoph Reeg |     3 | NULL |
| junetz.de      |     1 |    1 |
| Uli            |     1 |    1 |
| JCP            |     1 |    3 |
+----------------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE (AbtNr <= 1 AND AbtNr >= 1)
    ->    OR VNr IS NULL;
+----------------+-------+------+
| Name           | AbtNr | VNr  |
+----------------+-------+------+
| Christoph Reeg |     3 | NULL |
| junetz.de      |     1 |    1 |
| Uli            |     1 |    1 |
| JCP            |     1 |    3 |
+----------------+-------+------+
4 rows in set (0.01 sec)

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE AbtNr & 1 = 1
    ->    OR VNr IS NULL;
+----------------+-------+------+
| Name           | AbtNr | VNr  |
+----------------+-------+------+
| Christoph Reeg |     3 | NULL |
| junetz.de      |     1 |    1 |
| Uli            |     1 |    1 |
| JCP            |     1 |    3 |
+----------------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT Name, AbtNr, VNr
    -> FROM Mitarbeiter
    -> WHERE POW(13,AbtNr) = 13
    ->    OR POW(VNr,42) IS NULL;
+----------------+-------+------+
| Name           | AbtNr | VNr  |
+----------------+-------+------+
| Christoph Reeg |     3 | NULL |
| junetz.de      |     1 |    1 |
| Uli            |     1 |    1 |
| JCP            |     1 |    3 |
+----------------+-------+------+
4 rows in set (0.02 sec)

mysql>

Es sind noch nicht 1001 Möglichkeiten, aber wenn ich vieeel Zeit habe, werde ich daran weiterarbeiten ;-). Wer sich die beiden letzten Lösungen genau angesehen hat, wird feststellen, daß dort unbekannte Befehle verwendet werden; in Kapitel 6.7 werden diese noch genauer beschrieben.

Bei der letzten Möglichkeit sieht man, was passiert, wenn mit NULL-Werten gerechnet wird: Das Ergebnis ist NULL.

LIKE

Immer dann, wenn man in Textfeldern im Suchmuster Platzhalter oder Jokerzeichen (auch reguläre Ausdrücke genannt) verwenden will, können die Vergleichsoperatoren nicht verwendet werden. Ein Beispiel zur Verdeutlichung:

Es sollen alle Mitarbeiter ausgegeben werden, bei denen die Telefon-Vorwahl auf ,, 96`` endet. Falls du eine Möglichkeit findest, das mit Vergleichsoperatoren (und ohne Funktionen) zu lösen, schicke mir bitte eine E-Mail an die Adresse dsp@reeg.net; ich bevorzuge übrigens den Operator LIKE.

mysql> SELECT Name, Telefon
    -> FROM Mitarbeiter
    -> WHERE Telefon LIKE '%96/%';
+-------+--------------+
| Name  | Telefon      |
+-------+--------------+
| Maier | 06196/671797 |
+-------+--------------+
1 row in set (0.00 sec)

mysql>

Es soll ein Mitarbeiter mit Namen ,,Meier`` ausgegeben werden. Allerdings ist unbekannt, ob er sich mit ,,ei`` oder ,,ai`` schreibt. Es sollen also alle Möglichkeiten ausgegeben werden.

mysql> SELECT Name
    -> FROM Mitarbeiter
    -> WHERE Name LIKE 'M_ier';
+-------+
| Name  |
+-------+
| Maier |
| Meier |
+-------+
2 rows in set (0.00 sec)

mysql>

Wie wir sehen, gibt es zwei Jokerzeichen:

% DOS-Pendant: * steht für eine beliebige (auch 0) Anzahl beliebiger Zeichen

_ DOS-Pendant: ? steht für genau ein beliebiges Zeichen

BETWEEN

Neben dem LIKE-Operator gibt es auch noch andere, zum Beispiel den BETWEEN-Operator. Er tut das, was man von ihm erwartet: er wählt alle Spalten aus, die zwischen dem oberen und unteren Wert liegen.

Beispiel: Es sollen alle Mitarbeiter ausgewählt werden, deren Abteilungs-Nummer zwischen 2 und 5 liegt. Mit dem bisherigen Wissen würde man folgende Anweisung nehmen:

mysql> SELECT * FROM Mitarbeiter
    -> WHERE AbtNr >= 2 AND AbtNr <=5;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
+-----+------+-------+----------------+------------+--------------+
3 rows in set (0.01 sec)

mysql>

Man kann es aber noch etwas vereinfachen:

mysql> SELECT * FROM Mitarbeiter
    -> WHERE AbtNr BETWEEN 2 AND 5;
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   5 |    1 |     2 | Maier          | NULL       | 06196/671797 |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
+-----+------+-------+----------------+------------+--------------+
3 rows in set (0.00 sec)

mysql>

BETWEEN kann bei Textspalten, Datumsspalten und numerischen Spalten verwendet werden.

IN

Der Operator IN schließlich wird benutzt, wenn man nicht mit einem einzelnen Wert, sondern mit einer Wertemenge vergleichen will.

Zur Verdeutlichung: Es sollen alle Mitarbeiter ausgegeben werden, deren Telefonnummer ,,06196/671797`` oder ,,069/764758`` ist. Mit den bisherigen Operatoren würde sich folgende Abfrage ergeben:

mysql> SELECT * FROM Mitarbeiter
    -> WHERE Telefon = '06196/671797' OR Telefon = '069/764758';
+-----+------+-------+-----------+------------+--------------+
| MNr | VNr  | AbtNr | Name      | GebDat     | Telefon      |
+-----+------+-------+-----------+------------+--------------+
|   2 |    1 |     1 | junetz.de | 1998-03-05 | 069/764758   |
|   4 |    3 |     1 | JCP       | NULL       | 069/764758   |
|   5 |    1 |     2 | Maier     | NULL       | 06196/671797 |
+-----+------+-------+-----------+------------+--------------+
3 rows in set (0.00 sec)

mysql>

Das funktioniert zwar, aber da diese Möglichkeit bei großen Mengen von Werten sehr umständlich und unübersichtlich wird, hier das ganze nochmal mit dem IN-Operator:

mysql> SELECT * FROM Mitarbeiter
    -> WHERE Telefon IN ('06196/671797','069/764758');
+-----+------+-------+-----------+------------+--------------+
| MNr | VNr  | AbtNr | Name      | GebDat     | Telefon      |
+-----+------+-------+-----------+------------+--------------+
|   2 |    1 |     1 | junetz.de | 1998-03-05 | 069/764758   |
|   4 |    3 |     1 | JCP       | NULL       | 069/764758   |
|   5 |    1 |     2 | Maier     | NULL       | 06196/671797 |
+-----+------+-------+-----------+------------+--------------+
3 rows in set (0.00 sec)

mysql>

Der IN-Operator kann bei Textspalten, Datumsspalten und numerischen Spalten verwendet werden.

Die Verneinung des IN-Operators ist NOT IN. Als Beispiel sollen alle Mitarbeiter ausgegeben werden, deren Telefonnummer nicht ,,06196/671797`` oder ,,069/97640232`` ist. Erst umständlich mit OR und dann elegant...

mysql> SELECT * FROM Mitarbeiter
    -> WHERE NOT (Telefon = '06196/671797' OR Telefon = '069/764758');
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
+-----+------+-------+----------------+------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Mitarbeiter
    -> WHERE Telefon NOT IN ('06196/671797','069/764758');
+-----+------+-------+----------------+------------+--------------+
| MNr | VNr  | AbtNr | Name           | GebDat     | Telefon      |
+-----+------+-------+----------------+------------+--------------+
|   1 | NULL |     3 | Christoph Reeg | 1979-05-13 | NULL         |
|   3 |    1 |     1 | Uli            | NULL       | NULL         |
|   6 |    5 |     2 | Meier          | NULL       | 069/97640232 |
+-----+------+-------+----------------+------------+--------------+
3 rows in set (0.00 sec)

mysql>

Funktionen

Bei select_expression und where_expression können neben Konstanten und Spaltenwerten auch Funktionen verwendet werden. Es gibt zwei Arten von Funktionen, zum einen die sog. ,,singlerow``-Funktionen und zum anderen die Gruppenfunktionen. Singlerow-Funktionen werden auf jede Zeile angewendet, während die Gruppenfunktionen immer auf eine Gruppe von Zeilen angewendet werden.

Es wäre zum Beispiel Schwachsinn, zu versuchen, den Betrag von allen Stellenanzahlen auf einmal zu berechnen; bei der Summe paßt das schon eher.

Mathematische Funktionen

Es können nicht nur Spaltennamen angegeben werden, sondern auch mathematische Rechenoperationen mit Spalten und/oder Konstanten.

Eine Auswahl der mathematischen Funktionen ist in Tabelle 6.4 aufgeführt.

Tabelle 6.4: Mathematische Funktionen in SQL

+ - * /	addieren/subtrahieren/multiplizieren/dividieren
%	modulo (ganzzahliger Rest)
ABS()	Betrag von
COS()	Cosinus in rad
DEGREES()	Umrechnung von rad in deg (Grad)
MOD()	Modulo (ganzzahliger Rest)
PI()	die Zahl Pi
POW(X,Y)	rechnet X hoch Y aus
RAND()	liefert eine Zufallszahl zwischen 0 und 1
ROUND()	rundet Wert
ROUND(x,n)	rundet Wert von x auf n Stellen
SQRT()	Wurzel (2. Grades)
TRUNCATE(x,n)	schneidet nach n Kommastellen von x ab

Logische Operatoren

Die logischen bzw. booleschen Operatoren haben wir bereits im Kapitel 6.6.6 kennengelernt. In Tabelle 6.5 sind sie der Vollständigkeit halber noch einmal aufgeführt.

Tabelle 6.5: Logische Funktionen in SQL

NOT	logisches NOT. Wird genau dann wahr (gibt 1 zurück), wenn
!	das Argument 0 ist (sonst Rückgabe 0). NOT NULL gibt NULL zurück.
AND	logisches UND. Wird genau dann wahr (gibt 1 zurück), wenn
&&	alle Argumente weder 0 noch NULL sind (sonst Rückgabe 0).
OR	logisches ODER. Wird genau dann wahr (gibt 1 zurück), wenn
\|\|	eines der Argumente weder 0 noch NULL ist (sonst Rückgabe 0).

Sonstige Funktionen

In Tabelle 6.6 und 6.7 sind sonstige, teilweise praktische Funktionen aufgeführt. Die Tabellen sind nicht vollständig!

Tabelle 6.6: Bit-Funktionen in SQL

\|	bitweises ODER
&	bitweises UND

Tabelle 6.7: String-Funktionen in SQL

CONCAT(str1, str2, ...)	Gibt den String zurück, der durch Zusammenführen der Argumente entstanden ist. Sobald ein Argument NULL ist, wird NULL zurückgegeben.
LEFT(str,n)	schneidet n Buchstaben von `str` ab und gibt diese zurück
LTRIM(str)	löscht alle Leerzeichen am Anfang von `str`
PASSWORD(str)	verschlüsselt den Klartext `str`
REVERSE(str)	dreht `str` um, d.h. letzter Buchstabe ist dann am Anfang
LCASE(str)	Wandelt `str` in Kleinbuchstaben
LOWER(str)	und gibt das Ergebnis zurück
UCASE(str)	Wandelt `str` in Großbuchstaben
UPPER(str)	und gibt das Ergebnis zurück

DAYOFWEEK(date)	Gibt den Wochentag-Index des Datums zurück (1 = Sonntag, 2 = Montag, ..., 7 = Samstag)
DAYOFMONTH(date)	Gibt den Tag des Monats zurück
DAYOFYEAR(date)	Gibt den Tag im Jahr zurück
WEEK(date)	Gibt die Woche des Datums zurück.
WEEK(date,first)	Wenn `first` nicht angegeben wird bzw. 0 ist, fängt die Woche mit Sonntag an. Ist `first` z.B. 1, fängt die Woche mit Montag an.
MONTH(date)	Gibt den Monat zurück
YEAR(date)	Gibt das Jahr zurück
DATE_FORMAT(date,format)	Formatiert das Datum `date` entsprechend dem übergebenen `format` String.
UNIX_TIMESTAMP(date)	Gibt den Unix-Timestamp (Sekunden seit dem 1.1.1970) des Datums `date` zurück.

Datums-Funktionen

Mit Hilfe von DATE_FORMAT kann man Datumswerte aus Tabellen so formatieren, wie man sie gerne hätte. Die Funktion erwarten zwei Parameter. Zum einen das Datumsfeld, zum anderen den Formatierungs-String. Die Formatierungszeichen (siehe Tabelle 6.9) werden durch die entsprechenden Werte ersetzt. Alle anderen Zeichen werden so wie sie sind ausgegeben.

In PHP gibt es auch eine Datum-Formatierungsfunktion. Ob man nun mit der MySQL-Funktion das Datum formatiert und dann mit PHP ausgibt oder mit Hilfe der PHP-Funktion das Datum formatiert, ist häufig egal. Teilweise ist es praktischer, mit Hilfe der MySQL-Funktion das Datum zu formatieren, weil man dann die Formatierungsanweisung in der SQL-Abfrage hat und mit PHP nur ausgeben muß. Andererseits kann es aber auch praktischer sein, mit PHP zu formatieren, wenn man zum Beispiel dasselbe Datum an mehreren Stellen auf der Seite verschieden formatiert haben will. Wie gesagt, es kommt auf den Einzelfall und die Vorlieben des Programmierers an.

`%W`	Wochentag
`%w`	Tag in der Woche (0 = Sonntag, ..., 6=Samstag)
`%d`	Tag des Monats (00 - 31)
`%e`	Tag des Monats (0 - 31)
`%j`	Tag im Jahr (001 - 366)
`%U`	Woche, mit Sonntag als 1. Tag der Woche (00 - 52)
`%u`	Woche, mit Montag als 1. Tag der Woche (00 - 52)
`%M`	Monatsname
`%m`	Monat, numerisch (01 - 12)
`%c`	Monat, numerisch (1 - 12)
`%Y`	Jahr (4stellig)
`%y`	Jahr (2stellig)
`%T`	Uhrzeit (24 Std.) (hh:mm:ss)
`%S`	Sekunden (00 - 59)
`%s`	Sekunden (00 - 59)
`%i`	Minuten (00 - 59)
`%H`	Stunde (00 - 23)
`%k`	Stunde (0 - 23)
`%h`	Stunde (00 - 12)
`%I`	Stunde (00 - 12)
`%l`	Stunde (0 - 12)
`%%`	%

Gruppenfunktionen

Es können aber auch die sogenannten Gruppenfunktionen verwendet werden. Gruppenfunktionen heißen so, weil sie immer auf eine Gruppe von Tupeln (Datensätzen) angewendet werden.

In Verbindung mit Gruppenfunktionen darf streng genommen kein Spaltenname mehr mit in der select_expression stehen. Die einzige Ausnahme ist dann gegeben, wenn der Spaltenname in der GROUP BY-Anweisung steht. MySQL sieht das etwas lockerer und gibt keine Fehlermeldung bei Verwendung von Spaltennamen in der select_expression aus, allerdings hat dies im Normalfall wenig Sinn.

In Tabelle 6.10 sind ein Teil der verfügbaren Gruppenfunktionen aufgeführt.

Tabelle 6.10: Gruppenfunktionen in SQL

COUNT(expr)	zählt die Zeilen, deren Werte ungleich NULL sind
AVG(expr)	durchschnittlicher Wert
MIN(expr)	kleinster Wert
MAX(expr)	größter Wert
SUM(expr)	Summe

Für `expr` ist immer der Name der Spalte einzusetzen, mit der diese Operation erfolgen soll.

Beispiel: Es soll ausgegeben werden, wie viele Mitarbeiter ihren Geburtstag angegeben haben.

mysql> SELECT count(GebDat)
    -> FROM Mitarbeiter;
+---------------+
| count(GebDat) |
+---------------+
|             2 |
+---------------+
1 row in set (0.27 sec)

mysql>

Joins

Nachdem wir jetzt die Tabellen perfekt abfragen können, wollen wir mal ein paar Tabellen miteinander verbinden. Nach unserer Normalisierung (siehe Kapitel 4.4) befinden sich einige Informationen nämlich in verschiedenen Tabellen, obwohl sie eigentlich zusammengehören. Zum Beispiel würde ich gerne alle Mitarbeiter mit deren Abteilungen sehen. Hierbei aber nicht die Abteilungsnummer, sondern den Namen der Abteilung. Nach dem, was wir bis jetzt wissen, würden wir folgende Abfrage starten:

mysql> SELECT m.Name, m.AbtNr, a.Name, a.AbtNr
    -> FROM Mitarbeiter m, Abteilung a;
+----------------+-------+------------+-------+
| Name           | AbtNr | Name       | AbtNr |
+----------------+-------+------------+-------+
| Christoph Reeg |     3 | EDV        |     1 |
| junetz.de      |     1 | EDV        |     1 |
| Uli            |     1 | EDV        |     1 |
| JCP            |     1 | EDV        |     1 |
| Maier          |     2 | EDV        |     1 |
| Meier          |     2 | EDV        |     1 |
| Christoph Reeg |     3 | Verwaltung |     2 |
| junetz.de      |     1 | Verwaltung |     2 |
| Uli            |     1 | Verwaltung |     2 |
| JCP            |     1 | Verwaltung |     2 |
| Maier          |     2 | Verwaltung |     2 |
| Meier          |     2 | Verwaltung |     2 |
| Christoph Reeg |     3 | Chefetage  |     3 |
| junetz.de      |     1 | Chefetage  |     3 |
| Uli            |     1 | Chefetage  |     3 |
| JCP            |     1 | Chefetage  |     3 |
| Maier          |     2 | Chefetage  |     3 |
| Meier          |     2 | Chefetage  |     3 |
+----------------+-------+------------+-------+
18 rows in set (0.07 sec)

mysql>

Equi-Join

Die obige Abfrage ergibt allerdings nicht ganz das erwünschte Resultat. Bei dieser Art der Abfrage entsteht nämlich das kartesische Produkt, was so viel bedeutet wie ,,jeder mit jedem``. Wie wir oben jedoch unschwer erkennen können, gehören nur die Mitarbeiter und Abteilungen zusammen, deren AbtNr übereinstimmen, deshalb hatten wir diese auch eingefügt. Eine entsprechend modifizierte Abfrage würde demnach folgendermaßen lauten:

mysql> SELECT m.Name, m.AbtNr, a.Name, a.AbtNr
    -> FROM Mitarbeiter m, Abteilung a
    -> WHERE m.AbtNr = a.AbtNr;
+----------------+-------+------------+-------+
| Name           | AbtNr | Name       | AbtNr |
+----------------+-------+------------+-------+
| junetz.de      |     1 | EDV        |     1 |
| Uli            |     1 | EDV        |     1 |
| JCP            |     1 | EDV        |     1 |
| Maier          |     2 | Verwaltung |     2 |
| Meier          |     2 | Verwaltung |     2 |
| Christoph Reeg |     3 | Chefetage  |     3 |
+----------------+-------+------------+-------+
6 rows in set (0.00 sec)

mysql>

Vor den Spaltennamen muß jeweils die entsprechende Tabelle genannt werden, da die Namen nicht eindeutig sind. Um nicht jedes Mal den kompletten Tabellennamen benutzen zu müssen, wurden Aliase verwendet (siehe Kapitel 6.6.5).

Über die AbtNr entsteht die Verbindung zwischen den beiden Tabellen. Falls bei der Verknüpfung von mehreren Tabellen nicht das gewünschte Ergebnis erscheint, fehlt häufig eine WHERE-Bedingung, so daß bei einigen Tabellen das kartesische Produkt entsteht. In solchen Fällen kann man einfach nachzählen: Wenn n Tabellen miteinander verknüpft werden sollen, werden (n-1) WHERE-Bedingungen benötigt.

Diese Art der Verbindung wird Equi-Join genannt.

Self-Join

So, wie man mehrere Tabellen miteinander verbinden kann, ist es auch möglich, eine Tabelle mit sich selbst zu verbinden. Notwendig ist dies in unserem Beispiel beim Vorgesetzten. Es ist zwar schön, daß man zu jedem Mitarbeiter die Mitarbeiter-Nummer seines Vorgesetzten abrufen kann, aber der Name wäre doch noch viel schöner:

mysql> SELECT m.Name, m.VNr, v.Name, v.MNr
    -> FROM Mitarbeiter m, Mitarbeiter v
    -> WHERE m.VNr = v.MNr;
+-----------+------+----------------+-----+
| Name      | VNr  | Name           | MNr |
+-----------+------+----------------+-----+
| junetz.de |    1 | Christoph Reeg |   1 |
| Uli       |    1 | Christoph Reeg |   1 |
| Maier     |    1 | Christoph Reeg |   1 |
| JCP       |    3 | Uli            |   3 |
| Meier     |    5 | Maier          |   5 |
+-----------+------+----------------+-----+
5 rows in set (0.13 sec)

mysql>

Die Tabelle `Mitarbeiter` muß zwei Mal innerhalb der FROM-Anweisung auftauchen. Um nachher die Spalten eindeutig bezeichnen zu können, müssen Tabellen-Aliase vergeben werden.

Der einzige Schönheitsfehler bei der Abfrage ist, daß der Mitarbeiter ,,Christoph Reeg`` nicht aufgelistet wird. Im Prinzip ist das logisch, da er keinen Vorgesetzten hat. Dumm ist es trotzdem, und deshalb kommen wir zur nächsten Form des Joins: dem Outer-Join.

Outer-Join

Um beim Join alle Tupel der Haupttabelle mit den dazu passenden Tupeln der nachgeordneten Tabelle zu bekommen, wenn nicht zu jedem Tupel der Haupttabelle ein passender Tupel existiert, wird der Outer-Join benötigt.

Die Syntax unter MySQL lautet (wo sonst die Tabellennamen stehen):

     haupttabelle LEFT JOIN tabelle2 ON bedingung
oder haupttabelle LEFT JOIN tabelle2 USING (spalte)

Bei der unteren Möglichkeit müssen die Spaltennamen in den beiden Tabellen, über die die Verbindung entsteht, gleich sein. Bei der oberen Möglichkeit muß an die Stelle, wo ` bedingung` steht, das eingesetzt werden, was man beim Equi-Join als ` where_condition` schreiben würde. In beiden Fällen kann auch LEFT OUTER JOIN anstelle von LEFT JOIN geschrieben werden - das OUTER ist optional und nur aus Gründen der SQL-Kompatibilität erlaubt.

Um beim oben gezeigten Beispiel zu bleiben: Es sollen alle Mitarbeiter mit deren Vorgesetzten (sofern vorhanden) angezeigt werden. Da die Spaltennamen ungleich sind (in der Haupttabelle `VNr` und in der nachgeordneten Tabelle `MNr`), muß die obere Syntax benutzt werden.

mysql> SELECT m.Name, m.VNr, v.Name, v.MNr
    -> FROM Mitarbeiter m LEFT JOIN Mitarbeiter v ON m.VNr = v.MNr;
+----------------+------+----------------+------+
| Name           | VNr  | Name           | MNr  |
+----------------+------+----------------+------+
| Christoph Reeg | NULL | NULL           | NULL |
| junetz.de      |    1 | Christoph Reeg |    1 |
| Uli            |    1 | Christoph Reeg |    1 |
| JCP            |    3 | Uli            |    3 |
| Maier          |    1 | Christoph Reeg |    1 |
| Meier          |    5 | Maier          |    5 |
+----------------+------+----------------+------+
6 rows in set (0.03 sec)

mysql>

Die Mitarbeitertabelle mit dem Alias ,,m`` ist in unserem Fall die Haupttabelle.

Wie man unschwer erkennen kann, wird bei den Attributen, bei denen keine Werte existieren (in diesem Beispiel die beiden rechten Spalten in der ersten Zeile), NULL als Wert genommen.

Übrigens: Man muß natürlich nicht sowohl VNr als auch MNr abfragen (also zwischen SELECT und FROM angeben), da deren Werte wegen der ON-Bedingung (in der sie wiederum stehen müssen) immer gleich sind. Wenn man nur wissen möchte, in welcher Beziehung die einzelnen Mitarbeiter zu den Vorgesetzten stehen, kann man sogar ganz auf das Abfragen der Nummern verzichten. Um nicht völlig den Überblick zu verlieren, vergeben wir dabei noch Aliase:

mysql> SELECT m.Name AS Mitarb, v.Name as Vorges
    -> FROM Mitarbeiter m LEFT JOIN Mitarbeiter v ON m.VNr = v.MNr;
+----------------+----------------+
| Mitarb         | Vorges         |
+----------------+----------------+
| Christoph Reeg | NULL           |
| junetz.de      | Christoph Reeg |
| Uli            | Christoph Reeg |
| JCP            | Uli            |
| Maier          | Christoph Reeg |
| Meier          | Maier          |
+----------------+----------------+
6 rows in set (0.03 sec)

mysql>

Ein neues Beispiel:
Ich will alle Abteilungen aufgelistet haben, aber mit deren Autos.

mysql> SELECT a.Name, p.Kennzeichen, p.Typ
    -> FROM Abteilung a, PKW p
    -> WHERE a.AbtNr = p.AbtNr;
+-----------+-------------+---------+
| Name      | Kennzeichen | Typ     |
+-----------+-------------+---------+
| Chefetage | MTK-CR 1    | RR      |
| EDV       | F-JN 1      | VW-Golf |
+-----------+-------------+---------+
2 rows in set (0.04 sec)

mysql>

Wie man sieht, führt der Equi-Join nicht zum gewünschten Ergebnis. Also das ganze nochmal als Outer-Join. Da die Schlüsselspalten denselben Namen haben, können in diesem Fall beide Syntaxvarianten verwendet werden:

mysql> SELECT a.Name, p.Kennzeichen, p.Typ
    -> FROM Abteilung a LEFT JOIN PKW p ON a.AbtNr = p.AbtNr;
+------------+-------------+---------+
| Name       | Kennzeichen | Typ     |
+------------+-------------+---------+
| EDV        | F-JN 1      | VW-Golf |
| Verwaltung | NULL        | NULL    |
| Chefetage  | MTK-CR 1    | RR      |
+------------+-------------+---------+
3 rows in set (0.00 sec)

mysql> SELECT a.Name, p.Kennzeichen, p.Typ
    -> FROM Abteilung a LEFT JOIN PKW p USING (AbtNr);
+------------+-------------+---------+
| Name       | Kennzeichen | Typ     |
+------------+-------------+---------+
| EDV        | F-JN 1      | VW-Golf |
| Verwaltung | NULL        | NULL    |
| Chefetage  | MTK-CR 1    | RR      |
+------------+-------------+---------+
3 rows in set (0.00 sec)

mysql>

Als kleine Herausforderung hätte ich jetzt gerne die Abteilungen, die keinen PKW haben. Die Lösung ist eigentlich einfach. Wenn man sich das obige Ergebnis ansieht, stellt man fest (wie auch etwas weiter oben schon beschrieben), daß bei der Abteilung ohne PKW (nämlich ,,Verwaltung``) `Kennzeichen` und `Typ` NULL sind.

mysql> SELECT a.Name, p.Kennzeichen, p.Typ
    -> FROM Abteilung a LEFT JOIN PKW p USING (AbtNr)
    -> WHERE Typ IS NULL;
+------------+-------------+------+
| Name       | Kennzeichen | Typ  |
+------------+-------------+------+
| Verwaltung | NULL        | NULL |
+------------+-------------+------+
1 row in set (0.03 sec)

mysql>

DELETE FROM

Immer nur Daten einfügen und anzeigen zu lassen, ist zwar ganz praktisch, aber nicht ausreichend. Um Daten wieder löschen zu können, wird der Befehl DELETE FROM benutzt. Die Syntax lautet:

DELETE FROM table_name [WHERE where_definition]

Für `table_name` ist selbstverständlich der Name der Tabelle einzusetzen, in der gelöscht wird. Wenn keine `where_definition` angegeben wird, werden alle Daten ohne Nachfrage gelöscht! Die `where_definition` muß denselben Anforderungen genügen wie bei der SELECT-Anweisung.

Um vor dem Löschen zu testen, ob auch wirklich nur die richtigen Daten gelöscht werden, kann man erst ein SELECT * statt DELETE machen. Wenn nur Daten angezeigt werden, die gelöscht werden sollen, ersetzt man das SELECT * durch DELETE.

UPDATE

Als letztes fehlt noch das Ändern von Daten. Es ist doch etwas unpraktisch, erst die Daten zu löschen, um sie danach wieder geändert einzufügen. Die Syntax des UPDATE-Befehls lautet:

UPDATE table_name SET feld_name=expression,... [WHERE where_definition]

Für `table_name` ist selbstverständlich der Name der Tabelle einzusetzen, in der einzelne Einträge geändert werden sollen. `feld_name` ist durch den Namen des Feldes zu ersetzen, dessen Werte geändert werden sollen und `expression` durch den neuen Wert. Falls Werte mehrerer Felder geändert werden sollen, können diese Zuweisungen einfach durch je ein Komma getrennt hintereinander geschrieben werden. Wenn keine `where_definition` angegeben wird, werden alle Tupel geändert! Die ` where_definition` muß denselben Anforderungen genügen wie bei der SELECT-Anweisung.

ALTER TABLE

Schließlich kommen wir noch zum Ändern von Tabellen. Es ist möglich, bei Tabellen, die Daten enthalten, die Datentypen für einzelne Spalten zu ändern und Spalten hinzuzufügen bzw. zu löschen; beim Löschen gehen natürlich die Daten der gelöschten Spalte verloren! Der Befehl dafür ist ALTER TABLE. Die Syntax lautet:

ALTER TABLE table_name alter_spec

alter_spec:
     ADD [COLUMN] create_definition [AFTER column_name | FIRST]
oder CHANGE alter_feld_name create_definition
oder ADD PRIMARY KEY (index_spalte,...)
oder ADD INDEX (index_spalte,...)
oder ADD UNIQUE (index_spalte,...)
oder DROP feld_name
oder DROP PRIMARY KEY
oder RENAME new_table_name

Für `create_definition` gilt dasselbe wie schon für `CREATE TABLE`.

Um das Ganze etwas zu verdeutlichen, hier ein kleines praktisches Beispiel (der Befehl SHOW COLUMNS wird hierbei jeweils nur zur Verdeutlichung der Ergebnisse benutzt):

Es wird folgende Tabelle angelegt:

mysql> CREATE TABLE temp (
    -> eins     VARCHAR(10) NOT NULL,
    -> zwei     VARCHAR(20),
    -> PRIMARY KEY(eins)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      | PRI |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

Als nächstes soll die Spalte `fuenf` vom Typ VARCHAR(30) eingefügt werden.

mysql> ALTER TABLE temp
    -> ADD fuenf VARCHAR(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      | PRI |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| fuenf | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Nun wird die Spalte `drei` vom Typ VARCHAR(30) nach der Spalte `zwei` eingefügt.

mysql> ALTER TABLE temp
    -> ADD drei VARCHAR(30) AFTER zwei;         
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      | PRI |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| drei  | varchar(30) | YES  |     | NULL    |       |
| fuenf | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

So gefällt uns das aber noch nicht ganz. Also benennen wir einfach die Spalte `fuenf` in `vier` um:

mysql> ALTER TABLE temp
    -> CHANGE fuenf vier VARCHAR(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      | PRI |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| drei  | varchar(30) | YES  |     | NULL    |       |
| vier  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

Jetzt soll die Spalte `eins` nicht mehr Primärschlüssel sein (nicht die erste Spalte der SHOW-Anweisung!):

mysql> ALTER TABLE temp
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      |     |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| drei  | varchar(30) | YES  |     | NULL    |       |
| vier  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

Die Spalte `drei` soll nun Primärschlüssel werden. Primärschlüssel müssen aber als `NOT NULL` definiert werden. Deshalb zuerst einmal eine kleine Änderung des Datentyps:

mysql> ALTER TABLE temp
    -> CHANGE drei drei  VARCHAR(30) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      |     |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| drei  | varchar(30) |      |     |         |       |
| vier  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

Und nun definieren wir den Primärschlüssel neu:

mysql> ALTER TABLE temp
    -> ADD PRIMARY KEY(drei);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      |     |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| drei  | varchar(30) |      | PRI |         |       |
| vier  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

Jetzt, wo wir alles mühsam erstellt haben, fangen wir wieder an, alle Spalten der Reihe nach zu löschen. Als erstes soll die Spalte `drei` gelöscht werden.

mysql> ALTER TABLE temp
    -> DROP drei;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW columns FROM temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      |     |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| vier  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Der Name `temp` klingt aber irgendwie langweilig. Deshalb benennen wir die Tabelle in `test` um:

mysql> ALTER TABLE temp
    -> RENAME test;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW columns FROM temp;
ERROR 1146: Table 'cr.temp' doesn't exist
mysql> SHOW columns FROM test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eins  | varchar(10) |      |     |         |       |
| zwei  | varchar(20) | YES  |     | NULL    |       |
| vier  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Zum Schluß löschen wir wieder die ganze Tabelle:

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW columns FROM test;
ERROR 1146: Table 'cr.test' doesn't exist
mysql>

Die letzte Fehlermeldung besagt, daß die Tabelle nicht existiert. Diese Fehlermeldung bekommt man auch, wenn man sich beim Tabellennamen verschrieben hat (auch Groß-/Kleinschreibung).

Up: Praktischer Teil SQL Previous: SQL benutzen Next: Tips & Tricks

Christoph Reeg

%	DOS-Pendant: *	steht für eine beliebige (auch 0) Anzahl beliebiger Zeichen
_	DOS-Pendant: ?	steht für genau ein beliebiges Zeichen