Ich habe versucht, das Tutorial möglichst locker, einfach und mit wenigen Fachausdrücken zu schreiben. Das Tutorial zeigt, wie sich Aufgaben elegant und mit ein paar Zeilen SQL lösen lassen. Es zeigt aber auch, wann und vor allem warum manche SQLs rumzicken und Datenschrott produzieren.

Inhalt

Vorbereitung:

• Was ist SQL?
• Installation
• Der Query Browser

Tabellen und Daten ändern

• Erstellen einer Tabelle
• Daten einfügen
• Bestehende Daten ändern
• Daten selektieren
• Daten aus anderen Tabellen einfügen
• Datensätze löschen

Abfragen erstellen

• Aggregatfunktionen
• Gruppieren von Daten
• JOIN: Daten aus mehreren Tabellen
• JOIN und NULL
• JOIN mit mehreren Treffern
• JOIN: Ein paar Beispiele
• JOIN: Nichts geht mehr
• Temporäre Tabellen
• Sub SELECT

Anhang

• Tipps

Was ist SQL?

SQL ist eine Abkürzung für Structured Query Language und heisst soviel wir strukturierte Abfragesprache. Ein SQL-Server hat die Aufgabe, Daten zu speichern und zu liefern. Wenn wir einen Datensatz lesen wollen, suchen wir nicht in der Datenbank, wir lassen suchen.
Wir sagen dem SQL-Server: Suche mir den Artikel mit der Nummer sowieso. Der SQL-Server schickt dann die Informationen zurück an unser Programm. Wir brauchen also nicht nur einen Server, sondern auch einen Client. Der Client kann ein selbst geschriebenes Programm sein. Für das Tutorial verwenden wir ein fertiges Programm von MySQL, den MySQL Query Browser. Das Tutorial ist also unabhängig von einer Programmiersprache, hier geht es ausschliesslich um SQL.

Installation

Zuerst müssen wir von www.mysql.com die aktuelle Version von MySQL  und den Query Browser downloaden

MySQL Query Browser installieren

MySQL installieren:
- MySQL setup ausführen
- Typical installation wählen
- Configure auswählen
- Standard configuration auswählen
- Passwort vergeben und möglichst nicht vergessen

Doku:
Eine Dokumentation der MySQL - Befehle findet sich im Unterordner Docs vom MySQL-Server. Wer mit Englisch ein Problem hat, kann auch eine deutschsprachige Doku herunterladen.

Der Query Browser

Einloggen:

Nach dem Login erstellt der Query Browser eine Database, hier Schema genannt. Eine Database ist vergleichbar mit einem Ordner, in dem Tabellen erstellt werden können. Die Database mysql enthält die Zugangsberechtigungen zum Server. Unqualifiziertes rumspielen in dieser database kann MySQL mit Aussperren des users bestrafen, mit dem wir gerade eingeloggt sind.
Die Tabellen, die wir gleich erstellen werden, werden deshalb alle in der Database tutorial gespeichert. Alle Aktionen wie Erstellen und löschen von Tabellen sowie einfügen und lesen von Daten werden über SQL-Kommandos gesteuert. Ein SQL-Kommando gibt man im SQL Eingabefeld ein und klickt auf Execute. Je nach Kommando erscheinen Daten im Ergebnisfenster.

Erstellen einer Tabelle

Unsere Database ist im Moment noch leer. Der SQL "CREATE TABLE" erstellt uns eine neue Tabelle.

Die Syntax:
CREATE TABLE Tabellenname (
Spaltenname1 Spaltentyp1 Default1,
Spaltenname2 Spaltentyp2 Default2
...
Keydefinition
)

Hier eine Übersicht der wichtigsten Spaltentypen:

Eine SQL-Typische Besonderheit sind NULL - Werte. Sie erscheinen, wenn ein Datensatz eingefügt wird und einem Feld kein Wert zugeordnet wird. Wir wollen diese Besonderheit jetzt erst einmal ausser Acht lassen und vermeiden, indem wir einen Defaultwert für jede Spalte angeben. Der Defaultwert muss natürlich zum Spaltentyp passen.
Als Beispiel stellen wir uns vor, wir sollen eine Shopsoftware entwickeln. Zuerst brauchen wir natürlich etwas zum Verkaufen: Die Artikel. Unsere Artikel sollen

• eine Artikelnummer,
• eine Bezeichnung (der Text zum Artikel),
• einen Einkaufspreis und
• einen Verkaufspreis

bekommen.
Zuerst muss man die Spaltentypen definieren. Eine Artikelnummer ist (wie der Name schon sagt) eine Zahl ohne Komma -> INTEGER, die Bezeichnung ist ein Text -> VARCHAR, die Preise sind Kommazahlen -> DOUBLE.
Eine Tabelle kann einen primary key enthalten. Mit einem primary key kann ein Datensatz eindeutig lokalisiert werden. Es wird automatisch verhindert, dass mehrere Datensätze mit dem gleichen Key angelegt werden können. In unserem Fall ist der primary key die Artikelnummer.

SQL:
create table artikel(
artikelnummer integer not null default 0,
bezeichnung varchar(10) not null default '',
einkaufspreis double not null default 0,
verkaufspreis double not null default 0,
primary key(artikelnummer)
)

Die Tabelle "artikel" wurde nun erzeugt. Jetzt wollen wir unsere die Tabelle natürlich noch anschauen.
Dazu wird der mächtige SELECT Befehl verwendet.

Die Syntax:
SELECT Spalte1, Spalte2... FROM Tabellenname

Falls alle Spalten angezeigt werden sollen, kann ein * an Stelle von den Spalten stehen.

Mit dem SQL
SELECT * from artikel
lassen wir uns nun alle Spalten der Tabelle artikel anzeigen:

Ausser Spaltennamen sieht man noch nichts, die Tabelle ist noch leer. Der SELECT - Befehl sieht hier alles andere als mächtig aus, wir werden in jedoch später noch genau kennenlernen.

Daten einfügen

Unsere Artikeltabelle ist im Moment noch leer. Mit dem SQL "INSERT INTO TABLE" können Datensätze hinzugefügt werden.

Die Syntax:
INSERT INTO Tabellenname (
Spaltenname1,
Spaltenname2
...)
VALUES (
Wert1,
Wert2
...)

Folgenden Artikel wollen wir nun aufnehmen:

Alphanumerische Werte (Text) werden in Hochkomma geschrieben.

SQL:
INSERT INTO artikel (artikelnummer,bezeichnung,einkaufspreis) VALUES (1,'Computer',750.35)

Mit SELECT * FROM artikel werfen wir einen Blick in die Datenbank:

Der Artikel wurde angelegt, für den Verkaufspreis wurde der Defaultwert 0 geschrieben. Falls der Artikel noch einmal mit der gleichen Artikelnummer geschrieben werden soll, wird ein Fehler erzeugt:

Mit dem INSERT-SQL können auch mehrere Datensätze in einem SQL geschrieben werden:
INSERT INTO artikel (artikelnummer,bezeichnung,einkaufspreis) VALUES
(2,'Monitor',300.35),
(3,'Maus',10.83),
(4,'Drucker',215.74)

Unsere Tabelle sollte nun folgendermassen aussehen:

Bestehende Daten ändern

Den Verkaufspreis haben wir noch nicht festgelegt. Damit wir nicht schon bei der Eröffnung unseres Shops pleite sind, soll der Verkaufspreis anhand des Einkaufspreises und einer Gewinnspanne berechnet werden.

Syntax:
UPDATE Tabellenname SET Spalte=NeuerWert WHERE Filter

Mit WHERE lassen sich die SQL-Befehle auf bestimmte Zeilen beschränken.

Zu unserem Shop: Als Gewinnspanne legen wir 10% fest, der Verkaufspreis ist daher Einkaufspreis x 1.10

SQL:
UPDATE artikel SET verkaufspreis=einkaufspreis*1.1

Das Ergebnis ist nicht gerundet. Das wollen wir jetzt nachholen:

Syntax:
ROUND( Spalte, Anzahl Kommastellen)

SQL:
UPDATE artikel SET verkaufspreis=ROUND(verkaufspreis,2)

Natürlich lassen sich die beiden Rechenschritte auch zusammenfassen:
UPDATE artikel SET verkaufspreis=ROUND(einkaufspreis*1.1, 2)

Dummerweise haben wir uns von einem windigen Vertreter eine grosse Menge an Computermäusen andrehen lassen. "Die sind voll der Renner, die könnt ihr locker zum doppelten Preis verkaufen" hat der Wurstkopf gesagt. Doch leider will niemand eine Maus mit Baumwollslip im Tigerlook kaufen. Wir müssen die Maus zum halben Preis verkaufen. Mit WHERE werden wir den UPDATE gezielt auf nur einen Datensatz anwenden:

SQL:
UPDATE artikel
SET verkaufspreis=verkaufspreis*0.5
WHERE artikelnummer=3

Wir lassen uns die Artikel anzeigen:

SQL:
SELECT *
FROM artikel

Daten selektieren

Den SELECT Befehl haben wir schon verwendet. Er soll jetzt näher beleuchtet werden.

Die Syntax:
SELECT Spalte oder Funktion1, Spalte oder Funktion2...
FROM Tabellenname
WHERE Spalte=Wert
ORDER BY Sortierspalte [DESC]

Bisher haben wir uns immer alle Datensätze anzeigen lassen. Mit WHERE lassen sich die SQL-Befehle auf bestimmte Zeilen beschränken. Mit ORDER BY kann sortiert werden. Mit DESC kann die Sortierrichtung umgekehrt werden.

Als Beispiel soll aus unserer Tabelle der Artikel mit der Artikelnummer 2 gesucht werden:

SQL:
SELECT *
FROM artikel
WHERE artikelnummer=2

Jetzt wird nur noch ein Artikel angezeigt. In der Statusleiste kann die Anzahl der Datensätze abgelesen werden.

Bei der Selektion über Text sind Platzhalter, sog. Wildcards zulässig. _ bedeutet ein beliebiges Zeichen, % bedeutet beliebig viele Zeichen. Wenn Wildcards verwendet werden, muss LIKE statt dem Gleichheitszeichen verwendet werden.

Der folgende SQL selektiert alle Artikel die in der Bezeichnung mit einem M beginnen:

SQL:
SELECT *
FROM artikel
WHERE bezeichnung LIKE 'M%'

Der folgende SQL selektiert alle Artikel in deren Bezeichnung an der 3. Stelle ein u vorkommt:

SQL:
SELECT *
FROM artikel
WHERE bezeichnung LIKE '__u%'

Nun wollen wir die Spalten gezielt auswählen:

SQL:
SELECT
    bezeichnung,
    einkaufspreis,
    verkaufspreis
FROM artikel

Man kann nicht nur Spalten selektieren, sondern auch Funktionen bzw. Berechnungen benutzen. Als Beispiel wollen wir uns den Gewinn für jeden Artikel berechnen lassen:

SQL:
SELECT
    bezeichnung,
    einkaufspreis,
    verkaufspreis,
    verkaufspreis-einkaufspreis
FROM artikel

Die berechnete Spalte soll jetzt noch einen vernünftigen Spaltennamen bekommen. Dazu kann man hinter jeder Funktion mit AS einen neuen Spaltennamen vergeben:

SQL:
SELECT
    bezeichnung,
    einkaufspreis,
    verkaufspreis,
    verkaufspreis-einkaufspreis AS gewinn
FROM artikel

Nun soll noch das Ergebnis nach Gewinn sortiert werden:

SQL:
SELECT
    bezeichnung,
    einkaufspreis,
    verkaufspreis,
    verkaufspreis-einkaufspreis AS gewinn
FROM artikel
ORDER BY gewinn

Das mit der Maus zum halben Preis sollten wir uns vielleicht doch noch überlegen...

Daten aus anderen Tabellen einfügen

Wenn ein Artikel verkauft wird, müssen die Umsätze gespeichert werden. Gespeichert werden sollen folgende Werte:

• Artikelnummer
• Verkaufspreis
• Verkaufsdatum und Zeit

Da wir Artikel mehrfach verkaufen, eignet sich die Artikelnummer nicht mehr als eindeutiger Key. Eine fortlaufende Nummer (Transaktionsnummer) soll verwendet werden. MySQL bietet die Funktion auto_increment, um eine fortlaufende Nummer zu vergeben. Voraussetzung ist ein primary key. Anders gesagt: Unsere Transaktionsnummer braucht die Eigenschaft auto_increment und einen primary key. Damit wir uns nicht die Finger wund tippen, nennen wir die Transaktionsnummer id.
 

SQL:
CREATE TABLE umsatz (
id integer not null auto_increment,
artikelnummer integer not null,
verkaufspreis double not null default 0,
datum datetime,
primary key(id)
)

Unser Shop läuft an, wir haben einen Computer verkauft! Wir müssen die Artikelnummer, den Preis und das Datum eintragen. Das Datumsformat ist entgegen dem in Deutschland üblichen Format
Jahr-Monat-Tag Stunde:Minute:Sekunde.
Der SQL sieht folgendermassen aus:

SQL:
INSERT INTO umsatz (artikelnummer, verkaufspreis, datum) VALUES (1, 825.39, '2006-01-05 10:30:51')

Dieses Vorgehen kann wesentlich verbessert werden. Die Artikelnummer und der Verkaufspreis ist ja bereits in unserer Tabelle artikel gespeichert, für das Datum kann das aktuelle Datum verwendet werden. Die Lösung ist ein kombinierter INSERT INTO [...] SELECT SQL:

SQL:
INSERT INTO umsatz (artikelnummer, verkaufspreis, datum)
SELECT artikelnummer,verkaufspreis,now()
FROM artikel
WHERE artikelnummer=1

Im oben genannten SQL muss lediglich die Artikelnummer im WHERE eingegeben werden, die restlichen Felder werden so automatisch übernommen.

Datensätze löschen

Um Datensätze zu löschen, benutzt man den SQL DELETE.

Syntax:
DELETE FROM Tabellenname WHERE Spalte=Wert

SQL:
DELETE FROM umsatz WHERE id=2

Vorher:

Nacher:

Ein DELETE ohne WHERE löscht alle Datensätze aus der Datenbank.

SQL:
DELETE FROM umsatz

Jetzt ist die Tabelle wieder leer. Um ganze Tabellen zu löschen benutzt man den SQL DROP TABLE:

Syntax:
DROP TABLE Tabellenname

SQL:
DROP TABLE umsatz

Weg ist sie.

Aggregatfunktionen

Der SELECT kann noch mehr. Jetzt wollen wir Daten zusammenfassen. Da wir soeben unsere Tabelle platt gemacht haben, sind hier noch SQLs zum Erstellen und Füllen:

SQL:
CREATE TABLE umsatz (
id integer not null auto_increment,
artikelnummer integer not null,
einkaufspreis double not null default 0,
verkaufspreis double not null default 0,
datum datetime,
primary key(id)
)

SQL:
INSERT INTO umsatz (artikelnummer,einkaufspreis,verkaufspreis,datum) VALUES
(1,750.35,825.39,'2006-01-05 10:00:00'),
(2,300.35,330.39,'2006-01-05 10:00:00'),
(3, 10.83, 5.955,'2006-01-05 10:00:00'),
(4,215.74,237.31,'2006-01-05 10:00:00'),
(1,750.35,825.39,'2006-01-06 10:00:00'),
(2,300.35,330.39,'2006-01-06 10:00:00')

Zum Zusammenfassen benutzt man sog. Aggregatfunktionen, z.B.:

Wir wollen jetzt sehen, was wir insgesamt an Umsatz gemacht haben. Anders gesagt, wir wollen die Summe des Verkaufspreises.

SQL:
SELECT
    SUM(einkaufspreis) as summe_ek
FROM umsatz

Jetzt interessiert uns noch die Anzahl der verkauften Artikel und unser Gewinn:

SQL:
SELECT
    COUNT(*) as anzahl_artikel,
    SUM(einkaufspreis) as summe_ek,
    SUM(verkaufspreis) as summe_vk,
    SUM(verkaufspreis)-SUM(einkaufspreis) as summe_gewinn
FROM umsatz

Gruppieren von Daten

Nun wollen wir nicht die Gesamtsumme, sondern die Auswertung pro Artikelnummer. Die Artikelnummer wird als Spalte in den SELECT und GROUP BY aufgenommen. So werden alle Summen gruppiert nach Artikelnummer:

SQL:
SELECT
    artikelnummer,
    COUNT(*) as anzahl_artikel,
    SUM(einkaufspreis) as summe_ek,
    SUM(verkaufspreis) as summe_vk,
    SUM(verkaufspreis)-SUM(einkaufspreis) as summe_gewinn
FROM umsatz
GROUP BY artikelnummer

MySQL sortiert intern nach GROUP BY (hier: Artikelnummer) und fasst die Daten mit Aggregatfunktionen SUM zusammen:

Nun erstellen wir eine Auswertung pro Tag, d.h. wir gruppieren nach Datum.

SQL:
SELECT
    datum,
    COUNT(*) as anzahl_artikel,
    SUM(einkaufspreis) as summe_ek,
    SUM(verkaufspreis) as summe_vk,
    SUM(verkaufspreis)-SUM(einkaufspreis) as summe_gewinn
FROM umsatz
GROUP BY datum

Grundsätzlich sollten alle Spalten ohne Aggregatfunktionen im SELECT auch im GROUP BY vorkommen.

Warnung:
Viele SQL-Server vergleichen die Nicht - Aggregatfunktionen im SELECT und GROUP BY weigern sich gegebenenfalls, einen solchen SQL auszuführen. MySQL erzeugt jedoch keinen Fehler. Wenn die Spalten im SELECT und GROUP BY nicht identisch sind, können falsche Werte ausgegeben werden. Man muss also sehr genau unterscheiden, was eine Aggregatfunktion ist und was nicht:

Als Negativbeispiel wählen wir die Artikelnummer im SELECT und das Datum im GROUP BY:

SQL:
SELECT
    artikelnummer,
    COUNT(*) as anzahl_artikel,
    SUM(einkaufspreis) as summe_ek,
    SUM(verkaufspreis) as summe_vk,
    SUM(verkaufspreis)-SUM(einkaufspreis) as summe_gewinn
FROM umsatz
GROUP BY datum

Das Ergebnis ist zu nichts zu gebrauchen. Nur eine einzige Artikelnummer erscheint, sie wird 2x angezeigt, und das mit falschen Werten. Wir haben diesen Artikel nicht 6x verkauft. Was ist passiert? MySQL hat nach datum gruppiert und den ersten Datensatz im Feld Artikelnummer verwendet:

JOIN

Bisher wurden Daten immer nur von einer Tabelle selektiert. Jetzt wollen wir eine Auswertung über beide Tabellen (Artikel und Umsatz) erstellen. Zuerst selektieren wir alle Spalten der Tabelle umsatz:
SELECT *
FROM umsatz

Um eine zweite Tabelle hinzuzufügen, verwenden wir JOIN.

Syntax:

JOIN Tabellenname ON Relation

Um eine weitere Tabelle hinzuzufügen, müssen wir MySQL den Zusammenhang der Tabellen beschreiben, eine sog. Relation. Bei uns ist die Artikelnummer in beiden Tabellen der Zusammenhang: MySQL soll von der Tabelle umsatz die Artikel aus der Tabelle artikel über die Artikelnummer suchen, d.h. umsatz.artikelnummer=artikel.artikelnummer.

SQL:
SELECT *
FROM umsatz
LEFT JOIN artikel ON umsatz.artikelnummer=artikel.artikelnummer

Grafisch gesehen geschieht folgendes:

Spalten selektieren:

Die Spaltennamen sind jetzt nicht mehr eindeutig, z.B. kommt die Artikelnummer in beiden Tabellen vor. Der Versuch, die Artikelnummer wie bisher zu selektieren scheitert:

SQL:
SELECT
    artikelnummer,
    bezeichnung,
    datum
FROM umsatz
LEFT JOIN artikel ON umsatz.artikelnummer=artikel.artikelnummer

Wir müssen MySQL mitteilen, aus welcher Tabelle die Spalte kommt. Dies geschieht, indem wir den Tabellenname vor die Spalte schreiben:

SQL:
SELECT
    umsatz.artikelnummer,
    artikel.bezeichnung,
    umsatz.datum
FROM umsatz
LEFT JOIN artikel ON umsatz.artikelnummer=artikel.artikelnummer

Für Tabellennamen kann auch ein Alias vergeben werden. Dies ist zwingend nötig, wenn dieselbe Tabelle in mehreren JOINS vorkommt. In diesem Fall ist "Tabellenname.Spaltenname" auch nicht mehr eindeutig. Den Alias schreibt man hinter den Tabellennamen im FROM und JOIN, man kann sich bei der Spaltenauswahl darauf beziehen. Der Alias kann auch im ON verwendet werden. Wir vergeben jetzt den Alias ums für die Tabelle umsatz und art für die Tabelle artikel:

SQL:
SELECT
    ums.artikelnummer,
    art.bezeichnung,
    ums.datum
FROM umsatz ums
LEFT JOIN artikel art ON ums.artikelnummer=art.artikelnummer

JOIN und NULL

Bei unserem Beispiel gab es für jeden Datensatz in der Tabelle umsatz einen passenden in der Tabelle artikel und umgekehrt. Jetzt wollen wir untersuchen, was passiert, wenn die Relation stellenweise nicht zutrifft. Der Einfachheit halber erstellen wir uns zwei neue sehr einfache Tabellen:

CREATE TABLE t1 (
id integer not null default 0,
zahl integer not null default 0
)

INSERT INTO t1 (id,zahl) VALUES (1,1),(2,1),(3,1)

CREATE TABLE t2 (
id integer not null default 0,
zahl integer not null default 0
)

INSERT INTO t2 (id,zahl) VALUES (1,2),(2,2),(4,2)

In t1 ist die id 4 nicht vorhanden, in t2 fehlt die id 3. Schauen wir mal was passiert:

SQL:
SELECT
    t1.id as t1_id,
    t1.zahl as t1_zahl,
    t2.id as t2_id,
    t2.zahl as t2_zahl
FROM t1
LEFT JOIN t2 on t1.id=t2.id

Mit diesem SQL werden alle Datensätze von t1 angezeigt, von t2 nur solche, die dazu passen. Wenn ein Wert nicht in der JOIN-Tabelle existiert, werden NULL-Werte zurückgegeben. Wenn ein Wert in der JOIN tabelle existiert, in der FROM jedoch nicht, wird kein Datensatz ausgegeben. Anders gesagt: Von t1 werden alle Datensätze angezeigt, von t2 nur solche die dazu passen.

Wer in der Grundschule Mengenlehre hatte, kann sich jetzt freuen: Es ist tatsächlich noch zu etwas zu gebrauchen:

Wir haben die Tabelle t1 udn t2. Es gibt Datensätze, die nur in t1 vorkommen (rot, id=3), nur in t2 vorkommen (gelb, id=4) und die Schnittmenge (grün, id=1 & id=2). Der SQL SELECT [...] FROM t1 LEFT JOIN t2 gibt Daten von t1 und die Schnittmenge t1&t2  zurück.

Schauen wir uns den umgekehrten Fall an, hier ist t2 in FROM und t1 in JOIN:

SQL:
SELECT
    t2.id as t2_id,
    t2.zahl as t2_zahl,
    t1.id as t1_id,
    t1.zahl as t1_zahl
FROM t2
LEFT JOIN t1 on t1.id=t2.id

Auch hier sehen wir wieder die NULL Werte für Datensätze von t2 ohne passenden Datensatz in t1.

NULL - Werte ausschliessen
Bedingungen im WHERE wirken sich auf beide Tabellen aus. Wenn im WHERE die Relation angegeben wird, erscheint der Datensatz mit NULL - Werten nicht mehr:

SQL:
SELECT
    t1.id as t1_id,
    t1.zahl as t1_zahl,
    t2.id as t2_id,
    t2.zahl as t2_zahl
FROM t1
LEFT JOIN t2 on t1.id=t2.id
WHERE t1.id=t2.id

Warnung: Vorsicht bei Gruppierung
Wenn ein GROUP BY verwendet wird, muss man bedenken, dass sich die Summe nur auf die Daten bezieht, die nach dem JOIN verfügbar sind.

SQL:
SELECT
    SUM(t1.zahl) as t1_zahl,
    SUM(t2.zahl) as t2_zahl
FROM t1
LEFT JOIN t2 on t1.id=t2.id

Das Ergebnis der Spalte zahl in der Tabelle t2 ist nicht das Gesamtergebnis 6 sondern lediglich 4, da nur 2 Datensätze zur Tabelle t1 passen.

JOIN mit mehreren Treffern

Nun untersuchen wir, was passiert, wenn für einen Datensatz nicht einen sondern mehrere passende Daten gefunden werden. Man spricht hier von einer 1:n Relation. Dazu löschen wir die Daten in t2 und fügen neue ein:

DELETE FROM t2

INSERT INTO t2 (id,zahl) VALUES (1,2),(2,2),(2,2)

SQL:
SELECT
    t1.id as t1_id,
    t1.zahl as t1_zahl,
    t2.id as t2_id,
    t2.zahl as t2_zahl
FROM t1
LEFT JOIN t2 on t1.id=t2.id

Wie man sehen kann, wird für jede passende Möglichkeit ein Datensatz angezeigt.

Warnung: Vorsicht auch hier bei Gruppierung.

Wenn hier die Summe über die Zahl in t1 gebildet wird, bekommen wir als Ergebnis nicht 3 sondern 4!

SQL:
SELECT
    SUM(t1.zahl) as t1_zahl,
    SUM(t2.zahl) as t2_zahl
FROM t1
LEFT JOIN t2 on t1.id=t2.id

Ein gutes "Warnsignal" für dieses Problem ist die Anzahl der Datensätze. Wenn der SQL Schritt für Schritt aufgebaut wird, sollte der SELECT gleich viele Datensätze haben wie der SELECT mit JOIN.

Beispiel ohne JOIN:

SELECT
    t1.id as t1_id,
    t1.zahl as t1_zahl
FROM t1

Beispiel mit JOIN:

SELECT
    t1.id as t1_id,
    t1.zahl as t1_zahl,
    t2.id as t2_id,
    t2.zahl as t2_zahl
FROM t1
LEFT JOIN t2 on t1.id=t2.id

Jetzt ein paar Beispiele

Zurück zu unserem Shop. Die theoretischen Grundlagen sollten jetzt vorhanden sein. Jetzt wollen wir eine Aufstellung machen, die uns eine Monatsübersicht über unseren Umsatz gibt. Dazu brauchen wir noch ein paar Daten mehr:

DELETE FROM umsatz

INSERT INTO umsatz (artikelnummer,einkaufspreis,verkaufspreis,datum) VALUES
(1,750.35,825.39,'2006-01-01 10:00:00'),
(2,300.35,330.39,'2006-01-02 10:00:00'),
(3, 10.83, 5.955,'2006-01-05 10:00:00'),
(1,750.35,825.39,'2006-01-06 10:00:00'),
(2,300.35,330.39,'2006-01-06 10:00:00'),
(3, 10.83, 5.955,'2006-01-08 10:00:00'),
(1,750.35,825.39,'2006-01-09 10:00:00'),
(2,300.35,330.39,'2006-01-09 10:00:00'),
(1,750.35,825.39,'2006-02-01 10:00:00'),
(2,300.35,330.39,'2006-02-02 10:00:00'),
(3, 10.83, 5.955,'2006-02-05 10:00:00'),
(4,215.74,237.31,'2006-02-05 10:00:00'),
(1,750.35,825.39,'2006-02-06 10:00:00'),
(2,300.35,330.39,'2006-02-06 10:00:00')

Zuerst wollen wir die verkauften Artikel mit Bezeichnung pro Monat. Dazu wird die Funktion MONTH(datum) verwendet. Eigentlich sollten wir noch das Jahr mit WHERE einschränken, wir kehren das jetzt aber der Einfachheit halber unauffällig unter den Teppich. Den SQL bauen wir jetzt nicht auf einmal sondern Schritt für Schritt auf. Zuerst der SELECT:

SELECT
    MONTH(ums.datum) as monat,
    ums.artikelnummer,
    ums.verkaufspreis
FROM umsatz ums

Jetzt mit JOIN den Artikelstamm dazu:

SELECT
    MONTH(ums.datum) as monat,
    ums.artikelnummer,
    ums.verkaufspreis,
    art.bezeichnung
FROM umsatz ums
LEFT JOIN artikel art on ums.artikelnummer=art.artikelnummer

Jetzt wird zusammengefasst. Der Verkaufspreis wird mit der Aggregatfunktion SUM zusammengerechnet, alles andere muss in den GROUP BY rein. Sortiert werden soll das ganze dann nach Monat und Bezeichnung:

SELECT
    MONTH(ums.datum) as monat,
    ums.artikelnummer,
    SUM(ums.verkaufspreis) AS sum_vk,
    art.bezeichnung
FROM umsatz ums
LEFT JOIN artikel art on ums.artikelnummer=art.artikelnummer
GROUP BY monat,ums.artikelnummer,art.bezeichnung
ORDER BY monat, art.bezeichnung

Fertig.

Und die Summe pro Monat? Dazu müssen wir lediglich die Artikelnummer und Bezeichnung aus dem SQL herausnehmen:

SELECT
    MONTH(ums.datum) as monat,
    SUM(ums.verkaufspreis) AS sum_vk
FROM umsatz ums
LEFT JOIN artikel art on ums.artikelnummer=art.artikelnummer
GROUP BY monat
ORDER BY monat

Jetzt noch ein paar Beispiele:

Es wird Zeit, dass wir uns noch mehr vornehmen. Die Auswertung pro Monat sah so aus:

Nun wollen wir eine Jahresübersicht, in der die Umsätze je Monat in Spalten angeordnet sind.

Das Ganze wird wieder Schritt für Schritt aufgebaut, wir fangen mit dem SELECT an:

SELECT
    artikelnummer,
    verkaufspreis
FROM umsatz
WHERE MONTH(datum)=1

Der JOIN dazu:
SELECT
    ums1.artikelnummer,
    ums1.verkaufspreis,
    ums2.verkaufspreis
FROM umsatz ums1
LEFT JOIN umsatz ums2 on ums1.artikelnummer=ums2.artikelnummer and MONTH(ums2.datum)=2
WHERE MONTH(ums1.datum)=1

Spätestens jetzt sollten die Alarmglocken läuten, die Tabelle wird plötzlich länger und wir wollen sie eigentlich noch gruppieren. Beim JOIN wurde vor zwei Problemen gewarnt. Hier haben wir uns gleich beide eingefangen. Das summieren können wir uns sparen, wir bekommen ja jetzt schon völlig unbrauchbare Werte:

1. Im Februar hatten wir den Artikel mit der Nummer 4 verkauft, er fehlt, da kein passender Artikel im Januar verkauft wurde.
2. Wir haben zwischen den Monaten eine n:n - Beziehung, ein Artikel kann in einem Monat ja mehrmals verkauft werden. Folge: Der Umsatz stimmt nicht mehr.

Das 1. Problem bekommt man in den Griff, wenn man vom Artikelstamm ausgeht (FROM artikelstamm). Dort sind ja alle Artikel vorhanden.
Für das 2. Problem gibt es mit JOIN keine Lösung.

Das ist das Ende von JOIN. Hier geht es nicht mehr weiter.

Was nun?

Temporäre Tabellen

Nach einem Rückschlag wie vorhin gibt ein echter Entwickler natürlich nicht gleich auf. Ein schlauer Mensch sagte einmal: "Der Kopf ist rund damit das Denken die Richtung wechseln kann". Die Lösung: Eine temporäre Tabelle, in die jeder Monat eingefügt wird. Der Einfachheit halber begnügen wir uns mit Januar und Februar.

CREATE TABLE tmp (
    artikelnummer integer not null,
    vk_01 double not null default 0,
    vk_02 double not null default 0
)

Jetzt werden die Januardaten von der Tabelle umsatz in die Spalte vk_01 eingefügt:

INSERT INTO tmp ( artikelnummer, vk_01)
SELECT
    artikelnummer,
    verkaufspreis
FROM umsatz
WHERE MONTH(datum)=1

Jetzt werden die Februardaten von der Tabelle umsatz in die Spalte vk_02 eingefügt:

INSERT INTO tmp ( artikelnummer, vk_02)
SELECT
    artikelnummer,
    verkaufspreis
FROM umsatz
WHERE MONTH(datum)=2

Wenn SQLs in einer Programmiersprache erstellt werden, verwendet man in so einer Situation natürlich nur einen SQL und eine Schleife von 1-12 für die Monate Januar-Dezember.

Jetzt bauen wir die Abfrage auf. Wir fangen mit dem SELECT an:
SELECT
    tmp.artikelnummer,
    tmp.vk_01,
    tmp.vk_02
FROM tmp

Ergebnis: siehe oben rechts.

Eine Artikelbezeichnung wollen wir auch noch. Also JOIN mit artikel:
SELECT
    tmp.artikelnummer,
    art.bezeichnung,
    tmp.vk_01 AS Jan,
    tmp.vk_02 AS Feb
FROM tmp tmp
LEFT JOIN artikel art ON tmp.artikelnummer=art.artikelnummer

Und jetzt gruppieren. Wir wollen die Summe der Verkäufe und benutzen die Aggregatfunktion SUM dafür. Alles andere landet im GROUP BY:

SELECT
    tmp.artikelnummer,
    art.bezeichnung,
    SUM(tmp.vk_01) AS Jan,
    SUM(tmp.vk_02) AS Feb
FROM tmp tmp
LEFT JOIN artikel art ON tmp.artikelnummer=art.artikelnummer
GROUP BY tmp.artikelnummer,art.bezeichnung

Warnung:
Das oben genannte Beispiel ist nicht Multiuser fähig. Wenn zwei Personen gleichzeitig die Auswertung starten, können sie sich gegenseitig beeinflussen, da sie dieselbe Tabelle benutzen. MySQL hat für temporäre Tabellen speziell den Parameter CREATE TEMPORARY TABLE [...]. Die Tabellen werden dann pro connection und user erzeugt und werden beim Disconnect automatisch gelöscht. Eigentlich wollte ich das noch hier vorführen, aber dummerweise funktioniert das nicht mit unserem MySQL Query Browser, da er bei jedem Klick auf Execute eine neue Connection aufbaut.

Sub SELECT

Es gibt noch eine weitere Möglichkeit die Monatsauswertung zu erstellen, man kann sog. Subselects verwenden. Damit ist gemeint, dass an einer Stelle innerhalb eines SQLs ein SELECT auftaucht. Wir bauen den SQL wieder Schritt für Schritt auf. Zuerst selektieren wir alle Daten vom Artikelstamm.

Das Hauptquery: die Artikel selektieren:

SELECT
    art.artikelnummer,
    art.bezeichnung
FROM artikel art

Dann erstellen wir einen SQL für die nächste Spalte, die Werte für Januar. Das Subquery muss für einen Artikel genau ein Feld (einee Spalte und eine Zeile) erzeugen. Zum Aufbauen und Testen nehmen wir den Artikel mit der Nummer1:

SQL: Der Subselect für Januar:
SELECT
    SUM(ums1.verkaufspreis)
FROM umsatz ums1
WHERE MONTH(ums1.datum)=1 AND ums1.artikelnummer=1

Jetzt wird das Subquery in das Hauptquery eingebaut.
Es muss in Klammern geschrieben werden. Die Artikelnummer des Subquerys entspricht der Artikelnummer des Hauptquerys:

SELECT
    art.artikelnummer,
    art.bezeichnung,
    (SELECT SUM(ums1.verkaufspreis)
        FROM umsatz ums1
        WHERE MONTH(ums1.datum)=1 AND ums1.artikelnummer=art.artikelnummer )
FROM artikel art

Der Subselect wird jetzt für jedes Feld ausgeführt. Jetzt fehlt nur noch der Februar und eine vernünftige Spaltenüberschrift:

SELECT
    art.artikelnummer,
    art.bezeichnung,
    (SELECT SUM(ums1.verkaufspreis)
        FROM umsatz ums1
        WHERE MONTH(ums1.datum)=1 AND ums1.artikelnummer=art.artikelnummer ) as Jan,
    (SELECT SUM(ums2.verkaufspreis)
        FROM umsatz ums2
        WHERE MONTH(ums2.datum)=2 AND ums2.artikelnummer=art.artikelnummer ) as Feb
FROM artikel art

Warnung:
Subselects sind langsam, der Server muss für jedes Feld eine ganze SQL-Abfrage ausführen. Wir merken hier noch nichts davon. In unserem Fall wurden für 4 Zeilen und 2 Spalten 8 Abfragen ausgeführt. Eine Umsatztabelle kann in der Realität jedoch richtig gross werden. Bei 100 Artikeln und 12 Monaten entstehen intern 1200 Unterabfragen. Bei nur 1000 Datensätzen in der Umsatztabelle müssen schon 1.2 Millionen Datensätze verarbeitet werden. Subselects sollte man daher nur verwenden, wenn es wirklich keine andere Möglichkeit mehr gibt und wenn man sich sicher ist, dass die Performance mit "echten" Daten noch annehmbar ist.

Tipps

Das Tutorial ist nun zu Ende.
Viele Sachen wollte ich eigentlich noch unterbringen. Ich hatte aber nicht gedacht, dass das Erstellen eines Tutorials so viel Arbeit bedeutet. Ein paar nützliche Dinge möchte ich aber trotzdem noch "auf die Schnelle" loswerden. Zu Details bitte gegebenenfalls die MySQL-Doku dazu lesen.

Datentypen
Wir haben nur die wichtigsten Datentypen beim Erstellen von Tabellen verwendet. Ein Blick in die MySQL - Doku zu den verfügbaren Datentypen ist sicher interessant und sinnvoll.

Datenbankdesign
Die Einkaufspreise und Verkaufspreise sind sowohl in der Tabelle artikel als auch in der Tabelle umsatz vorhanden. Warum kann man sie nicht mit JOIN aus der Tabelle artikel lesen? Preise können sich ändern, Umsatzzahlen nachträglich nicht. Man sollte sich vorher genau überlegen, welche Daten man wie ablegt.

Performance:
Bei der Tabellenerstellung sollte für jede Spalte die im JOIN verwendet wird ein key vergeben werden. Das kann auch nachträglich geschehen:
ALTER TABLE umsatz ADD KEY (artikelnummer)

Tabellen einer Datenbank anzeigen:
SHOW TABLES

Zeitraum selektieren mit BETWEEN:
SELECT *
FROM umsatz
WHERE datum BETWEEN "2006-01-01" AND "2006-01-09"

Manchmal kann man Datensätze nicht mit WHERE einschränken.
Das ist immer dann der Fall, wenn nach Aggregatfunktionen gefiltert werden soll. Als Beispiel wollen wir Ladenhüter herausfinden: Alle Artikel, die wir weniger als 3x verkauft haben. Dazu verwendet man HAVING. WHERE filtert vor einer Gruppierung, HAVING danach. HAVING ist deshalb ein wenig langsamer als WHERE.
SELECT
    artikelnummer,
    COUNT(*) as anzahl
FROM umsatz
GROUP BY artikelnummer
HAVING anzahl<3

Den teuersten Artikel finden:
Es wird absteigend nach Preis sortiert und die Ausgabe auf 1 Zeile beschränkt. Dazu verwenden wir LIMIT:
SELECT *
FROM artikel
ORDER BY verkaufspreis DESC
LIMIT 1

5er Rundung für Schweiz (und Finnland):
Preis nach Rappen umrechnen, durch 5 teilen, runden, mal 5 und in Franken umrechnen:
(round((x*100)/5)*5)/100 = round(x*20)/20

SELECT
    verkaufspreis,
    round(verkaufspreis*20)/20
FROM artikel

Anzahl unterschiedlicher Artikel berechnen mit DISTINCT:
SELECT
    MONTH(datum),
    COUNT(DISTINCT(artikelnummer))
FROM umsatz
GROUP BY MONTH(datum)

Benutzer eindeutig identifizieren:
Manchmal ist es notwendig, mehrere Benutzer eindeutig zu unterscheiden. Bei jedem Einloggen bekommt jede Verbindung eine Nummer:
SELECT CONNECTION_ID()

Passwörter speichern
Ein Passwort speichert man nie im Klartext ab. Trotzdem habe ich das schon viel zu oft gesehen. Falls man so etwas sieht, geht man wie folgt vor:
1. Den Code ausdrucken
2. Das Blatt auf ein Brett nageln
3. Dem Programmierer den Mist um die Ohren hauen
Man speichert Passwörter in codierter Form ab, z.B. mit INSERT INTO [..] VALUES MD5('MeinPasswort')
Geprüft wird es dann z.B. mit WHERE Spalte=MD5('MeinPasswort')
Ist nicht 100% sicher, aber zumindest hat nicht jeder Depp gleich alle Passwörter. MySQL macht's bei der Zugangskontrolle übrigens auch so.
 

Bestehende Daten ändern

Den Verkaufspreis haben wir noch nicht festgelegt. Damit wir nicht schon bei der Eröffnung unseres Shops pleite sind, soll der Verkaufspreis anhand des Einkaufspreises und einer Gewinnspanne berechnet werden.

Syntax:
UPDATE Tabellenname SET Spalte=NeuerWert WHERE Filter

Mit WHERE lassen sich die SQL-Befehle auf bestimmte Zeilen beschränken.

Zu unserem Shop: Als Gewinnspanne legen wir 10% fest, der Verkaufspreis ist daher Einkaufspreis x 1.10

SQL:
UPDATE artikel SET verkaufspreis=einkaufspreis*1.1

Das Ergebnis ist nicht gerundet. Das wollen wir jetzt nachholen:

Syntax:
ROUND( Spalte, Anzahl Kommastellen)

SQL:
UPDATE artikel SET verkaufspreis=ROUND(verkaufspreis,2)

Natürlich lassen sich die beiden Rechenschritte auch zusammenfassen:
UPDATE artikel SET verkaufspreis=ROUND(einkaufspreis*1.1, 2)

Dummerweise haben wir uns von einem windigen Vertreter eine grosse Menge an Computermäusen andrehen lassen. "Die sind voll der Renner, die könnt ihr locker zum doppelten Preis verkaufen" hat der Wurstkopf gesagt. Doch leider will niemand eine Maus mit Baumwollslip im Tigerlook kaufen. Wir müssen die Maus zum halben Preis verkaufen. Mit WHERE werden wir den UPDATE gezielt auf nur einen Datensatz anwenden:

SQL:
UPDATE artikel
SET verkaufspreis=verkaufspreis*0.5
WHERE artikelnummer=3

Wir lassen uns die Artikel anzeigen:

SQL:
SELECT *
FROM artikel