# Datenaustausch
Wir leben alle unter demselben Himmel, aber wir haben nicht alle den gleichen Horizont.
– Konrad Adenauer, dt. Bundeskanzler 1949–1963
Kommunikation und der Austausch von Informationen kann diesen Horizont jedoch erweitern. Dieses Kapitel wird Sie in den Datenaustausch mit einer MySQL-Datenbank einführen, sodass Sie schon bald Daten aus einer Datenbank auslesen, in eine Datenbank einfügen oder ändern können. Auch der Austausch von Daten mit anderen Datenbanken wird am Ende dieses Kapitels behandelt.
Für die Beispiele in diesem Kapitel sollten Sie zuvor die Datei buecher.sql einbinden. Diese Datei finden Sie auf der dem Buch beiliegenden CD‑ROM im Verzeichnis x:\misc\buecher.sql. Das x muss durch den entsprechenden Buchstaben des CD‑ROM-Laufwerks ersetzt werden, in dem die CD eingelegt ist. Um die Datei einbinden zu können, müssen Sie die MySQL-Konsole aufrufen und den Befehl source x:\misc\buecher.sql eingeben. Wechseln Sie anschließend mit der Anweisung use bspbuecher; die Datenbank.
# SELECT
Um Daten aus einer SQL-kompatiblen Datenbank zu lesen, steht Ihnen nur ein Befehl zur Verfügung: SELECT. Durch seine zahlreichen Optionen für die explizite Wahl von Spalten und die Eingrenzung der Wertebereiche wird dieser Befehl zu einem mächtigen Werkzeug. Die grundlegende Syntax dieses Befehls sieht folgendermaßen aus:
SELECT [DISTINCT]
[COUNT spaltenname]
{*|spaltenname|tabellenname.spaltenname} [,...]
FROM tabellenname [,...]
[WHERE bedingung]
[GROUP BY spaltenname [,...] [HAVING bedingung] ]
[ORDER BY spaltenname [,...] [{ASC|DESC}] ]
[LIMIT offset[,n]]
Auch wenn Sie die Syntax dieses Befehls wahrscheinlich nicht beim ersten Mal erfassen können, sollten Sie sich keine Sorgen machen. Sie werden sich Schritt für Schritt an diesen Befehl herantasten und alle Optionen einzeln durcharbeiten.
Basierend auf der weiter oben dargestellten Syntax sieht die einfachste SELECT-Anweisung wie folgt aus:
SELECT * FROM bspverlage;
Diese SQL-Anweisung würde nun alle Datensätze inklusive aller Felder aus der Tabelle bspverlage auslesen. Das * steht dabei für alle Spalten und somit für alle Datensatzfelder. FROM gibt an, aus welcher Tabelle die Datensätze ausgelesen werden sollen. Dementsprechend muss der Name einer Tabelle folgen. In unserem Beispiel ist dies bspverlage.
Für Testzwecke habe ich in meiner Datenbank bereits zwei Einträge in die Tabelle bspverlage eingefügt. Die Ausgabe, nachdem der Befehl SELECT * FROM bspverlage; ausgeführt worden ist, können Sie in Abbildung 3.1 sehen.
Abbildung 3.1: Ausgabe des Befehls SELECT * FROM bspverlage;
Wie Sie erkennen können, wurden alle Datensätze inklusive aller Spalten bzw. Felder ausgegeben.
Anstelle des Platzhalters * können Sie auch explizit bestimmte Spalten angeben. Die einzelnen Spalten werden dann durch Kommata voneinander getrennt. Die SQL-Anweisung, um nur die Spalten autor und titel der Datensätze zu erhalten, würde folgendermaßen aussehen:
SELECT autor, titel FROM bspbuecher;
Die Ausgabe (diesmal ohne Abbildung):
+---------------------+--------------------------+
| autor | titel |
+---------------------+--------------------------+
| Christian Wenz | JavaScript |
| Till Wortmann | Die Computer-Fibel |
| Sascha Wolter | ActionScript |
| Christian Ullenboom | Java ist auch eine Insel |
| Ulrich Kaiser | C/C++ |
| Andreas Kühnel | VB.NET |
| Arnold Willemer | Wie werde ich UNIX-Guru? |
| Martin Kloss | Lingo objektorientiert |
| Gerhard Koren | Adobe Premiere 6.5 |
| Harald Puchtler | Mac OS X 10.2 |
+---------------------+--------------------------+
Bei allen bisherigen Beispielen wurden immer alle Datensätze ausgegeben. Mit einer kleinen Änderung der Anweisungen ließe sich nun z. B. die Anzahl der Datensätze in einer Tabelle ermitteln. Vor allem dann, wenn Sie nur die Anzahl und nicht die Details der Datensätze ermitteln möchten, ergeben sich so erhebliche Performance-Steigerungen.
SELECT COUNT(autor) FROM bspbuecher;
Diese Anweisung würde alle Datensätze der Tabelle ermitteln und ausgeben. Dabei ist es egal, ob Sie explizit einen Spaltennamen oder das * angeben. MySQL optimiert zuvor die Anweisung und ermittelt nur die Anzahl der Datensätze, liest diese jedoch nicht. Die Ausgabe könnte folgendermaßen aussehen:
+--------------+
| count(autor) |
+--------------+
| 10 |
+--------------+
Wenn Sie bei einer Abfrage zusätzlich die Option LIMIT angeben, können Sie die Anzahl der Datensätze beschränken. Dies ist immer dann sinnvoll, wenn Sie z. B. nur den ersten von 10 Datensätzen oder die ersten 10 von 100.000 Datensätzen benötigen. Alle Datensätze zurückgeben zu lassen wäre reine Zeitvergeudung. Die Anweisung
SELECT autor,titel FROM bspbuecher LIMIT 5;
liefert z.B. nur einen Datensatz aus der Tabelle bspbuecher. Die Ausgabe könnte dann so aussehen:
+---------------------+--------------------------+
| autor | titel |
+---------------------+--------------------------+
| Christian Wenz | JavaScript |
| Till Wortmann | Die Computer-Fibel |
| Sascha Wolter | ActionScript |
| Christian Ullenboom | Java ist auch eine Insel |
| Ulrich Kaiser | C/C++ |
+---------------------+--------------------------+
Mit der Option LIMIT ist aber noch eine weitere Eingrenzung möglich. Wählen Sie z. B. die Anweisung
SELECT autor,titel FROM bspbuecher LIMIT 5,3;
würden Sie lediglich 3 Datensätze erhalten. Der Clou daran ist jedoch, dass die Ausgabe erst ab dem 6. Datensatz erfolgt. Die interne Zählung der Datensätze beginnt wie bei Arrays oder Listen mit 0. Dadurch entspricht 0 dem ersten, 1 dem zweiten, 2 dem dritten Datensatz usw. Die Ausgabe sähe so aus:
+-----------------+--------------------------+
| autor | titel |
+-----------------+--------------------------+
| Andreas Kühnel | VB.NET |
| Arnold Willemer | Wie werde ich UNIX-Guru? |
| Martin Kloss | Lingo objektorientiert |
+-----------------+--------------------------+
# WHERE
Eine Art der Eingrenzung von Datensätzen haben Sie jetzt mit LIMIT kennen gelernt. In den meisten Fällen reicht dies jedoch nicht aus. Die Option WHERE ermöglicht Ihnen hingegen eine äußerst flexible Definition, welche Datensätze aus der Datenbank ausgelesen und zurückgegeben werden sollen. Dafür müssen Sie lediglich eine oder auch mehrere Bedingungen angeben.
SELECT autor, titel FROM bspbuecher WHERE autor > 'S';
Diese Anweisung würde nur die Datensätze ausgeben, bei denen der Wert des Feldes autor größer als S ist. Die Ausgabe dieses Beispiels könnte folgendermaßen aussehen:
+---------------+--------------------+
| autor | titel |
+---------------+--------------------+
| Till Wortmann | Die Computer-Fibel |
| Sascha Wolter | ActionScript |
| Ulrich Kaiser | C/C++ |
+---------------+--------------------+
Der Wert der Namen der Autoren ist lexikalisch größer als der Buchstabe S.
Welche Spalte Sie zum Eingrenzen des Ergebnisses verwenden, bleibt Ihnen überlassen. Der Unterschied liegt lediglich in der Art der Notation. So müssen Sie Zeichen- bzw. Zeichenkettentypen auch immer mit Zeichenketten vergleichen. Zahlenwerte hingegen werden auch als Zahlenwerte verglichen. Ein Beispiel:
SELECT autor, titel, preis FROM bspbuecher WHERE preis < 34.99;
Das Ergebnis dieser Anweisung:
+-----------------+--------------------------+-------+
| autor | titel | preis |
+-----------------+--------------------------+-------+
| Till Wortmann | Die Computer-Fibel | 19.90 |
| Arnold Willemer | Wie werde ich UNIX-Guru? | 34.90 |
| Harald Puchtler | Mac OS X 10.2 | 34.90 |
+-----------------+--------------------------+-------+
Es wurden nur Datensätze ausgegeben, deren Wert in der Spalte preis kleiner als 34.99 ist.
Neben den beiden Operatoren < (kleiner als) und > (größer als) stehen Ihnen auch noch folgende weitere Vergleichsoperatoren bei der Verwendung der WHERE-Option zur Verfügung:
| Operator | Erklärung |
|---|---|
| = | gleich |
| <> | ungleich |
| < | kleiner als |
| > | größer als |
| <= | kleiner gleich |
| >= | größer gleich |
Tabelle 3.1: Vergleichsoperatoren
Diese Operatoren genügen in der Regel für eine einfache Bedingungsprüfung vollkommen. Es gibt jedoch auch logische Operatoren, mit denen eine Verknüpfung oder Negierung von Bedingungen möglich wird.
| Operator | Erklärung |
|---|---|
| AND | logische UND-Verknüpfung |
| OR | logische ODER-Verknüpfung |
| NOT | Negierung einer Bedingung |
Tabelle 3.2: Logische Operatoren
Diese Vielzahl an Operatoren, die man sicherlich in Programmiersprachen, aber nicht in SQL erwarten würde, ermöglicht zahlreiche Kombinationen. Nachfolgend einige Beispiele:
SELECT autor, titel, preis FROM bspbuecher WHERE id > 5 AND id < 8;
Diese Anweisung gibt lediglich die Datensätze aus, deren Wert in der Spalte id größer als 5, aber kleiner als 8 ist; somit also nur die Datensätze mit der id 6 und 7.
SELECT autor, titel, preis FROM bspbuecher WHERE preis <> 49.90;
Es werden nur die Datensätze ausgegeben, in denen preis ungleich 49.90 ist.
SELECT COUNT(*) FROM bspbuecher WHERE preis = 34.90;
Anhand dieses Beispiels können Sie erkennen, dass auch beliebige andere Kombinationen möglich sind. So liefert dieses Beispiel die Anzahl der Datensätze zurück, in denen preis gleich 34.90 ist.
Mit runden Klammern können Sie innerhalb der Verknüpfung von Bedingungen zusätzlich Prioritäten festlegen. Dies soll das nachfolgende Beispiel demonstrieren.
SELECT autor, titel, seiten, preis FROM bspbuecher WHERE seiten > 500 AND (preis < 25.00 OR preis > 34.90);
Die Anweisung würde nur die Datensätze zurückgeben, deren seiten größer als 500 und deren preis entweder kleiner als 25.00 oder größer als 34.90 ist.
SELECT autor, titel FROM bspbuecher WHERE id IN (1,3,5,7,9);
Mit der Option IN können Sie die Option WHERE zusätzlich um eine Mengenprüfung erweitern. Die Menge wird dabei in runden Klammern notiert und umfasst in diesem Beispiel die Zahlen 1,3,5,7 und 9. Zurückgegeben werden dann nur die Datensätze, deren id in der definierten Menge vorkommt (also entweder 1, 3, 5, 7 oder 9 ist).
Zusätzlich zum Mengenoperator IN gibt es noch den Ähnlichkeitsoperator LIKE und den Bereichsoperator BETWEEN. Der Ähnlichkeitsoperator ist für den Vergleich von Zeichenketten gedacht. Während Sie mit den normalen Vergleichsoperatoren nur auf bestimmte Zeichenfolgen oder Werte vergleichen können, ermöglicht der Ähnlichkeitsoperator einen ungenauen Vergleich. Möchten Sie z. B. alle Datensätze nach dem Namen Christian durchsuchen, kommen Sie mit den Vergleichsoperatoren nicht sehr weit. Mit LIKE sieht das jedoch anders aus.
SELECT autor FROM bspbuecher WHERE autor LIKE 'Christian%';
Diese Abfrage würde nun alle Datensätze zurückgeben, in denen die Zeichenkette »Christian« am Anfang des Wertes der Spalte autor steht. Das Prozentzeichen ist ein Platzhalter für eine beliebige Zeichenfolge. Aus diesem Grund muss »Christian« auch am Anfang des Wertes und darf nicht zwischendrin stehen.
SELECT autor FROM bspbuecher WHERE autor LIKE '%ll%';
Das Ergebnis dieser Abfrage enthält nun alle Datensätze, in denen die Zeichenkette »ll« an beliebiger Stelle des Wertes der Spalte autor gefunden wurde. Das Ergebnis der Abfrage sieht so aus:
+---------------------+
| autor |
+---------------------+
| Till Wortmann |
| Christian Ullenboom |
| Arnold Willemer |
+---------------------+
Sie sollten LIKE aber nur selten verwenden und versuchen, eine andere Möglichkeit zu finden, denn Abfragen mit dem LIKE-Operator werden alles andere als schnell ausgeführt.
Für einen Bereichsvergleich, ob z. B. der Wert der Spalte preis zwischen 25 und 35 liegt, sollten Sie den Operator BETWEEN im Zusammenhang mit AND verwenden. Sie ersparen sich damit umständliche Konstrukte wie z. B. WHERE preis >= 25.00 AND preis <= 35.00. Die folgende SQL-Anweisung erfüllt den gleichen Zweck, ist aber wesentlich eleganter:
SELECT autor, titel, preis FROM bspbuecher WHERE preis BETWEEN 25.00 AND 35.00;
# ORDER BY
Mit der Option ORDER BY können Sie die Ergebnisse von Abfragen vor dem Zurückgeben sortieren lassen. Dabei erfolgt die Sortierung standardmäßig aufsteigend, also von klein nach groß. Welche Sortierung verwendet wird, können Sie jedoch festlegen, und zwar mit den Optionen ASC und DESC. ASC steht für »ascending« (aufsteigend) und DESC für »descending« (absteigend). Dabei müssen Sie jedoch noch festlegen, nach welcher Spalte sortiert werden soll.
SELECT * FROM bspbuecher ORDER BY preis DESC;
Dieses Beispiel liefert alle Datensätze absteigend gemäß der Spalte preis sortiert zurück. Manchmal reicht eine Sortierung anhand einer Spalte jedoch nicht aus, da es auch hier zu doppelten Einträgen kommen kann. Notieren Sie stattdessen einfach alle Spalten, nach denen sortiert werden soll, in der Reihenfolge der Priorität.
SELECT titel, preis, seiten FROM bspbuecher
ORDER BY preis, seiten ASC;
Dieses Beispiel würde ebenfalls alle Datensätze ausgeben, sie jedoch zuerst nach der Spalte preis und anschließend nach der Spalte seiten in aufsteigender Reihenfolge sortieren. Die Ausgabe der Anweisung sieht so aus:
+--------------------------+-------+--------+
| titel | preis | seiten |
+--------------------------+-------+--------+
| Die Computer-Fibel | 19.90 | 600 |
| Mac OS X 10.2 | 34.90 | 500 |
| Wie werde ich UNIX-Guru? | 34.90 | 600 |
| JavaScript | 39.90 | 624 |
| C/C++ | 39.90 | 1300 |
| ActionScript | 44.90 | 400 |
| Lingo objektorientiert | 44.90 | 612 |
| Adobe Premiere 6.5 | 49.90 | 416 |
| VB.NET | 49.90 | 960 |
| Java ist auch eine Insel | 49.90 | 1264 |
+--------------------------+-------+--------+
# GROUP BY
Mit der Option GROUP BY können Sie doppelte Einträge in einer Datenbank gruppieren. Die Gruppierung wird dabei nach Spalten vorgenommen. Datensätze, die in einer Spalte die gleichen Werte besitzen, werden dabei jedoch nur insofern zusammengefasst, als dass lediglich einer dieser Datensätze dargestellt wird. Welcher das nun genau ist, hängt vom Zufall ab.
In der Beispiel-Tabelle bspbuecher verfügen die Spalten hauptsächlich in der Spalte preis über gleiche Werte.
SELECT autor, titel, preis FROM bspbuecher GROUP BY preis;
Diese Anweisung gibt insgesamt nur fünf Datensätze aus, die jedoch in der Spalte preis alle einen anderen Wert besitzen. Die Ausgabe dieser Anweisung könnte so aussehen:
+---------------------+--------------------------+-------+
| autor | titel | preis |
+---------------------+--------------------------+-------+
| Till Wortmann | Die Computer-Fibel | 19.90 |
| Arnold Willemer | Wie werde ich UNIX-Guru? | 34.90 |
| Christian Wenz | JavaScript | 39.90 |
| Sascha Wolter | ActionScript | 44.90 |
| Christian Ullenboom | Java ist auch eine Insel | 49.90 |
+---------------------+--------------------------+-------+
Interessant ist dabei jedoch, dass eine Sortierung der Datensätze anhand der Spalte preis erfolgt. In dieser Form macht eine Anweisung mit GROUP BY sicherlich keinen echten Sinn. In Verbindung mit der Option COUNT und AS können Sie MySQL jedoch anweisen, die Anzahl von Datensätzen zu zählen, die einen gleichen Wert in einer beliebigen Spalte besitzen.
SELECT preis, COUNT(preis) AS anzahl FROM bspbuecher GROUP BY preis;
Diese Anweisung liest nun die Spalte preis aller Datensätze ein. Dabei wird mit der Option AS jedoch eine neue Spalte namens anzahl eingefügt. Die Ausgabe dieser Abfrage sieht dann folgendermaßen aus:
+-------+--------+
| preis | anzahl |
+-------+--------+
| 19.90 | 1 |
| 34.90 | 2 |
| 39.90 | 2 |
| 44.90 | 2 |
| 49.90 | 3 |
+-------+--------+
# DISTINCT
Wenn Sie Tabellen mit Primärschlüsseln definieren, ist es eigentlich so gut wie ausgeschlossen, dass ein Datensatz mit einem anderen absolut identisch ist. Immerhin ist der Primärschlüssel bei beiden Datensätzen unterschiedlich. Wenn Sie jedoch nur Teile von Datensätzen auslesen (ohne die Spalte mit dem Primärschlüssel), kann es schon einmal vorkommen, dass sich zwei Datensätze in allen Feldern gleichen. Um die Ausgabe von solchen doppelten Datensätzen zu verhindern, können Sie die Option DISTINCT verwenden.
SELECT DISTINCT preis, verlag FROM bspbuecher;
+-------+--------+
| preis | verlag |
+-------+--------+
| 39.90 | 1 |
| 19.90 | 1 |
| 44.90 | 1 |
| 49.90 | 1 |
| 34.90 | 1 |
+-------+--------+
Ohne die Option DISTINCT würde diese Anweisung 10 Datensätze ausgeben. Einige dieser Datensätze würden sich jedoch gleichen. Mit der Option DISTINCT wurden die gleichen Datensätze jedoch nur einmal ausgegeben.
# Numerische Ausdrücke
Die numerischen Ausdrücke unterteilen sich in Operatoren und Funktionen. Eine der numerischen Funktionen haben Sie bereits kennen gelernt: COUNT. Diese ermittelt die Anzahl von Datensätzen in einer Tabelle. Es gibt jedoch noch weitere Ausdrücke. Die numerischen Operatoren finden Sie in Tabelle 3.3 und die numerischen Funktionen in Tabelle 3.4.
| Operator | Erklärung |
|---|---|
| + | Addition |
| - | Subtraktion |
| * | Multiplikation |
| / | Division |
Tabelle 3.3: Numerische Operatoren
| Funktion | Erklärung |
|---|---|
| AVG | Ermittelt den arithmetischen Mittelwert einer Spalte |
| MAX | Ermittelt den höchsten Wert einer Spalte |
| MIN | Ermittelt den kleinsten Wert einer Spalte |
| SUM | Ermittelt die Summe einer Spalte |
| COUNT | Ermittelt die Anzahl der Datensätze einer Spalte |
Tabelle 3.4: Numerische Funktionen
Obwohl Sie Operatoren und Funktionen auf die Datensätze von Tabellen anwenden können, werden die Datensätze selbst nicht verändert, sondern nur der Wert wird zurückgegeben.
SELECT autor, titel, preis * 0.93 FROM bspbuecher;
Der numerische Operator in dieser Anweisung führt dazu, dass die Preise der Bücher um 7 % reduziert werden (entspricht dem gesetzlichen Steuersatz). Die Werte in der Tabelle bleiben davon jedoch unberührt.
SELECT SUM(preis) FROM bspbuecher;
Diese SQL-Anweisung addiert nun alle Werte der Spalte preis und gibt das Ergebnis zurück. Wie das folgende Beispiel zeigt, lassen sich auch mehrere Funktionen gleichzeitig auf eine Spalte anwenden.
SELECT AVG(preis), MAX(preis), MIN(preis), SUM(preis),
COUNT(preis) FROM bspbuecher;
Bei dieser Abfrage werden der durchschnittliche, der höchste, der niedrigste und der Gesamtpreis ermittelt. Außerdem wird noch die Anzahl der Datensätze festgestellt.
Auch eine Verknüpfung numerischer Operatoren und Funktionen ist möglich.
SELECT SUM(preis) + 7.95 AS summe FROM bspbuecher;
Diese Anweisung liefert die Summe aller Werte der Spalte preis zzgl. 7.95. Das Ergebnis wird in der Spalte summe zurückgegeben.
# INSERT
Auf welche Art und Weise können Sie die Daten überhaupt in die Datenbank einfügen? Dafür benötigen Sie den Befehl INSERT, zu dem Sie nachfolgend zunächst die grundlegende Syntax finden.
INSERT INTO
tabellenname [(spaltenname [,...])]
VALUES (wert[,...])
Bei jeder INSERT-Anweisung ist es zwingend erforderlich, den Namen der Tabelle anzugeben, in die die Werte eingefügt werden sollen, und natürlich die einzufügenden Werte. Das folgende Beispiel stellt die einfachste Variante dar:
INSERT INTO verliehen VALUES (5,'Max Mustermann');
Diese Anweisung setzt die Tabelle verliehen voraus, die zwei Spalten besitzt: eine Spalte, in der die id des verliehenen Buches gespeichert wird, und eine zweite Spalte, in der der Name der Person gespeichert wird, an die das Buch verliehen wurde. Der Datentyp der ersten Spalte ist INTEGER und der der zweiten Spalte VARCHAR. Wenn Sie sich die Anweisung noch einmal genauer ansehen, können Sie erkennen, dass Zeichenketten innerhalb von einfachen Anführungsstrichen notiert werden müssen, damit sie in eine Spalte eingefügt werden können. Dies ist bei Zahlenwerten und auch Datums- bzw. Zeitangaben nicht nötig.
Wenn Sie zusätzlich die Spaltennamen angeben, ist die Reihenfolge, in der Sie die Werte für die Spalten angeben, nicht vom Aufbau der Tabelle abhängig, sondern davon, in welcher Reihenfolge die Spaltennamen in der Anweisung angegeben wurden. Erst dadurch wird es möglich, nicht für jede Spalte einen Wert angeben zu müssen. Dies ist vor allem dann sinnvoll, wenn Sie in einer Tabelle eine Spalte definiert haben, die die Optionen PRIMARY KEY und AUTO_INCREMENT zugewiesen bekommen hat. Die Datensätze werden dadurch automatisch und korrekt durchnummeriert.
INSERT INTO bspbuecher VALUES (3,'Thomas Theis','Einstieg in Python',
450,'3–89842–227–5',34.90,1);
Wenn in der Tabelle bspbuecher bereits ein Datensatz mit der id 3 existiert, quittiert MySQL den Versuch, einen weiteren Datensatz mit der id 3 in diese Tabelle einzufügen, mit einer Fehlermeldung. Damit Sie also zuvor nicht alle id überprüfen müssen, ist folgende Anweisung wesentlich flexibler:
INSERT INTO bspbuecher (autor, titel, seiten, isbn, preis, verlag)
VALUES
('Thomas Theis','Einstieg in Python',450,'3–89842–227–5',34.90,1);
Eine besondere Regelung gibt es in MySQL bezüglich der Syntax von INSERT. Durch Kommata können Sie mehrere Datensätze in einer einzelnen Anweisung in eine Tabelle einfügen:
INSERT INTO bspbuecher (autor, titel, seiten, isbn, preis, verlag)
VALUES
('Axel Schemberg','PC-Netzwerke',500,'3–89842–307–7',29.90,1),
('Ernst Maracke','VoiceXML',500,'3–89842–293–3',59.90,1);
Mit dieser SQL-Anweisung werden zwei Datensätze in einem Rutsch in die Tabelle bspbuecher eingefügt. Der Vorteil ist, dass Sie die Reihenfolge der Spalten nicht jedes Mal aufs Neue notieren müssen. Vorteilhaft ist auch die kürzere Ausführungszeit einer solchen Anweisung im Vergleich zum einzelnen Hinzufügen. Denken Sie aber daran, dies funktioniert nur in MySQL, nicht aber mit anderen SQL-kompatiblen Datenbanken.
# UPDATE
Mit Hilfe der UPDATE-Anweisung können Sie bereits bestehende Datensätze anpassen. Ähnlich wie bei der Anpassung der Struktur von Tabellen müssen Sie dabei nicht jede Spalte einzeln anpassen, sondern nur die Spalten, die einen neuen Wert erhalten sollen. Die Syntax lautet:
UPDATE tabellenname
SET spaltenname=wert [spaltenname=wert,...]
WHERE bedingung
Bei dieser Anweisung sind diesmal alle Optionen Pflichtangaben. Mit der Option SET bestimmen Sie für eine Spalte einen neuen Wert. Damit nun auch der richtige Datensatz geändert wird, müssen Sie außerdem mit der WHERE-Option eine Bedingung angeben.
UPDATE bspverlage SET name='Galileo Computing',
webseite='www.galileo-computing' WHERE name = 'Galileo Press';
Diese SQL-Anweisung ändert die Spalten name und webseite des Datensatzes, der in der Spalte name den Wert Galileo Press hat.
Seien Sie vorsichtig, wenn Sie UPDATE ohne WHERE verwenden. Dies wird dazu führen, dass alle Datensätze geändert werden, ob Sie das nun wollen oder nicht. Notieren Sie sicherheitshalber also immer eine WHERE-Bedingung.
Auch bei der UPDATE-Anweisung können Sie mit gewissen Einschränkungen numerische Operatoren verwenden. So würde z. B. die Anweisung
UPDATE bspbuecher SET preis=preis+10.0;
alle Preise der Bücher in Tabelle bspbuecher um 10 erhöhen.
# DELETE
Mit der Anweisung DELETE können Sie Datensätze löschen. Sie können jedoch nur ganze Datensätze löschen. Um einen einzelnen Wert zu entfernen, müssen Sie die Anweisung UPDATE verwenden. Die Anweisung DELETE besitzt folgende Syntax:
DELETE FROM tabellenname [WHERE bedingung];
Die Angabe der Option WHERE ist zwar nicht zwingend, hat aber entscheidende Auswirkungen auf das Ergebnis. Wenn Sie DELETE ohne WHERE verwenden, werden alle Datensätze der Tabelle gelöscht. Mit Hilfe von WHERE können Sie dies jedoch einschränken.
DELETE FROM bspbuecher;
Diese Anweisung würde alle Datensätze der Tabelle bspbuecher löschen.
# Tabellen importieren und exportieren
Wenn Sie mit Datenbanken arbeiten, kann schon ein kleiner Fehler zu enormen Problemen führen, z. B. wenn Sie vergessen, bei einer DELETE-Anweisung eine Bedingung anzugeben. Deshalb sollten Sie regelmäßig eine Sicherung Ihrer Datenbank(en) durchführen. MySQL ist dafür mit speziellen Bordmitteln ausgestattet, durch die ein Export und auch ein Import von Daten außerordentlich leicht fällt.
# Export
Zum Exportieren der Daten müssen Sie eine erweiterte SELECT-Anweisung verwenden. Die einfachste Methode, einen Export zu realisieren, ist die Anweisung
SELECT * FROM bspbuecher INTO OUTFILE '/bspbuecher.txt';
Diese Anweisung würde nun alle Datensätze inklusive aller Spalten in die Datei bspbuecher.txt exportieren. Durch den Schrägstrich vor dem Dateinamen wird MySQL angewiesen, die Datei im Wurzelverzeichnis des Laufwerks abzulegen, auf dem es installiert ist. Wurde MySQL z. B. im Verzeichnis d:\mysql installiert, lautet der Pfad zur Datei d:\bspbuecher.sql.
Standardmäßig werden die einzelnen Felder durch einen Tabulator \t und Datensätze durch ein \r\n (CR/LF) getrennt. Doch lässt sich dies durch die Angabe zusätzlicher Optionen an die eigenen Wünsche anpassen. Das Zeichen bzw. die Zeichenfolge, mit dem bzw. der die Felder voneinander getrennt werden sollen, können Sie mit FIELDS TERMINATED BY angeben. Die Markierung für das Ende eines Datensatzes wird mit LINES TERMINATED BY angegeben. Achten Sie darauf, dass Sie die Zeichen(folgen) in einfache Anführungsstriche setzen.
SELECT * FROM bspbuecher INTO OUTFILE '/bspbuecher.txt' FIELDS
TERMINATED BY '>>' LINES TERMINATED BY '<<';
Diese Anweisung würde alle Datensätze der Tabelle bspbuecher in die Datei bspbuecher.txt exportieren und die einzelnen Felder durch >> und die Datensätze durch << trennen. Wenn Sie nur bestimmte Spalten oder Datensätze exportieren möchten, können Sie natürlich auch WHERE, GROUP BY und Ähnliches angeben.
# Import
Der Befehl LOAD DATA ermöglicht den Datenimport: Dieser Befehl verfügt über eine Menge Optionen, die ich im Einzelnen besprechen werde. Zunächst die Syntax:
LOAD DATA INFILE dateiname
INTO TABLE tabellenname [importoptionen]
[(spaltenname [,...])]
Die Optionen INFILE und INTO TABLE sind obligatorisch. INFILE gibt die Datei an, aus der die Daten importiert werden, und INTO TABLE gibt die Tabelle an, in die die Daten eingefügt werden sollen. Bei der Angabe des Dateinamens sind jedoch ein paar Regeln einzuhalten. Erstens muss der Dateiname in einfachen Anführungsstrichen notiert werden, und zweitens müssen Sie auf die Schrägstriche achten.
Wenn Sie in UNIX/Linux-Umgebungen eine Datei einbinden möchten, genügt es, wenn Sie den Schrägstrich / einmal angeben. In Windows-Umgebungen, bei denen der Backslash \\als Trennstrich für Verzeichnisse verwendet wird, müssen Sie diesen zuvor entwerten, indem Sie davor einen weiteren Backslash notieren. Schließlich wird der Backslash als Einleitung für spezielle Steuerzeichen verwendet.
LOAD DATA INFILE 'd:\\\\daten\\\\bspbuecher.txt' INTO TABLE bspbuecher;
Diese Anweisung würde den Inhalt der Datei bspbuecher.txt in die Tabelle bspbuecher importieren. In dieser Form erwartet MySQL, dass Felder durch einen Tabulator und Datensätze durch CR\LF getrennt sind. Wenn Sie jedoch Daten zwischen anderen Datenbanken oder ähnlichen Programmen (z. B. Excel) austauschen möchten, werden Sie dabei in der Regel auf das so genannte CSV-Format angewiesen sein. CSV steht für »Comma Separated Values«. In diesem Format werden Felder durch Komma getrennt und Datensätze durch CR\LF. Dies unterscheidet sich also von der Syntax, die MySQL erwartet. Aus diesem Grund können Sie mit FIELDS TERMINATED BY und LINES TERMINATED BY MySQL beibringen, wie es die Daten zu interpretieren hat.
LOAD DATA INFILE 'excel.csv' INTO TABLE excel FIELDS
TERMINATED BY ',' LINES TERMINATED BY '\r\n';
Es kann vorkommen, dass in einer Datei weniger Felder angegeben sind als in der MySQL-Tabelle. Aus diesem Grund können Sie MySQL mitteilen, in welcher Reihenfolge die Felder in der Datei angegeben sind. Sie müssen dafür lediglich die Spaltennamen der MySQL-Tabelle in der Reihenfolge notieren, in der sie in der Datei angeordnet sind.
LOAD DATA INFILE 'excel.csv'
INTO TABLE excel
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(spalte2, spalte4, spalte1, spalte3);
Dies ist jedoch kein Garant dafür, dass der Import auch funktioniert. MySQL erwartet bei Zahlen als Dezimaltrennzeichen den Punkt. Viele Programme (vor allem von Microsoft) richten sich dabei aber nach der landesspezifischen Einstellung. Für Deutschland ist das Dezimaltrennzeichen das Komma. Auch Datumsangaben sind problematisch. Während MySQL ein Datum im Format JJJJTTMM erwartet, liefern viele Programme TT.MM.JJJJ oder auch TT/MM/JJ. Dies sind die häufigsten Fehlerquellen beim Datenaustausch mit anderen Programmen.
# Tabellenverknüpfung
Die bisherigen Beispiele mit SELECT-Anweisungen bezogen sich immer nur auf eine Tabelle. Grundsätzlich wäre es nicht weiter tragisch, wenn ich zu Beginn nicht das Thema Redundanz angesprochen hätte und ich dementsprechend als Verlag in der Tabelle bspbuecher immer nur eine id angegeben hätte. Es wird Zeit, sich damit zu beschäftigen, wie diese Daten miteinander verknüpft werden können.
Hierfür gibt es zwei Methoden. Die einfachste ist, in der SELECT-Anweisung mehrere Tabellennamen anzugeben und mit einer WHERE-Bedingung die entsprechenden Daten zu filtern. Dies könnte folgendermaßen aussehen:
SELECT bspbuecher.titel, bspverlage.name FROM bspbuecher, bspverlage WHERE bspbuecher.verlag = bspverlage.id;
Diese Anweisung würde aus der Tabelle bspbuecher die Spalte titel und aus bspverlage die Spalte name auslesen. Die Gruppierung selbst erfolgt durch die WHERE-Bedingung. Sie definiert, dass nur der Name des Verlags ausgelesen werden soll, dessen id dem Wert in der Spalte verlag der Tabelle bspbuecher entspricht. Die Ausgabe sieht so aus:
+--------------------------+-------------------+
| titel | name |
+--------------------------+-------------------+
| JavaScript | Galileo Computing |
| Die Computer-Fibel | Galileo Computing |
| ActionScript | Galileo Computing |
| Java ist auch eine Insel | Galileo Computing |
| C/C++ | Galileo Computing |
| VB.NET | Galileo Computing |
| Wie werde ich UNIX-Guru? | Galileo Computing |
| Lingo objektorientiert | Galileo Computing |
| Adobe Premiere 6.5 | Galileo Computing |
| Mac OS X 10.2 | Galileo Computing |
| Einstieg in Python | Galileo Computing |
| PC-Netzwerke | Galileo Computing |
| VoiceXML | Galileo Computing |
+--------------------------+-------------------+
# Aliase
Gerade bei solchen Verknüpfungen kann eine SQL-Anweisung manchmal sehr lang und damit unübersichtlich werden. Um dies zu verhindern, können Sie für Tabellen einen Alias definieren. Dieser wird (ohne Komma) hinter dem Tabellennamen notiert.
SELECT b.autor, b.titel, v.name FROM bspbuecher b, bspverlage v
WHERE b.verlag = v.id;
Obwohl in diesem Beispiel eine zusätzliche Spalte angegeben wurde, die ausgelesen werden soll, ist die eigentliche Anweisung jedoch kürzer geworden. Dies liegt daran, dass die langen Namen der Tabellen durch entsprechende sehr kurze Aliase ersetzt wurden. Für die Tabelle bspbuecher wurde der Alias b und für die Tabelle bspverlage der Alias v definiert. Die Angabe der einzelnen Spalten erfolgt dann nur noch mit Hilfe der Aliase. Anstatt also bspbuecher.autor zu schreiben, genügt dann b.autor.
# JOIN
Eine Verknüpfung zweier Tabellen mit einer WHERE-Klausel ist sehr einfach und funktioniert auch in der Regel perfekt. Unübersichtlich wird das Ganze jedoch, wenn Sie zusätzlich zur Verknüpfung auch noch Datensätze filtern und sortieren möchten.
SELECT b.autor, b.titel, b.preis, v.name
FROM bspbuecher b, bspverlage v
WHERE (b.verlag = v.id)
AND (b.preis BETWEEN 20.00 AND 40.00)
ORDER BY preis DESC;
Der Großteil dieser Anweisung besteht aus Bedingungen für die WHERE-Option. Eine moderne und übersichtlichere Variante ist die Verwendung der Option LEFT JOIN.
SELECT b.autor, b.titel, b.preis, v.name
FROM bspbuecher b
LEFT JOIN bspverlage v ON b.verlag = v.id
WHERE b.preis BETWEEN 20.00 AND 40.00
ORDER BY preis DESC;
Der Vorteil der Variante mit LEFT JOIN ist, dass die Verknüpfung unabhängig von der WHERE-Option erfolgt. Beide SQL-Anweisungen sind jedoch nicht gleich übersichtlich; die Variante mit LEFT JOIN hat diesbezüglich die Nase klar vorn. Die Option LEFT JOIN weist MySQL an, die Tabelle bspbuecher mit der Tabelle bspverlage zu verknüpfen. Das Kriterium für die Verknüpfung wird nach ON notiert und lautet hier, dass b.verlag und v.id die gleichen Werte haben sollen.
Es gibt jedoch auch einen Unterschied im Ergebnis der beiden Varianten. Bei Verwendung von LEFT JOIN werden auch Datensätze ausgegeben, zu denen kein Verlag gespeichert wurde. Würden Sie eine WHERE-Bedingung angeben, würden die Datensätze, die kein Pendant in der Tabelle bspverlage besitzen, nicht mit ausgegeben werden. Dabei spielt die Reihenfolge, in der die Tabellen angegeben wurden, eine wichtige Rolle. bspbuecher LEFT JOIN bspverlage würde Bücher ausgeben, denen kein Verlag zugeordnet wurde. Andersherum würde bspverlage LEFT JOIN bspbuecher Verlage ausgeben, die keine Bücher veröffentlicht haben.
# Zusammenfassung
- Abfragen an eine MySQL-Datenbank werden mit dem Befehl
SELECTgestellt. - Mit der Option
WHEREkönnen Sie das Ergebnis einerSELECT-Abfrage durch Bedingungen einschränken. GROUP BYgruppiert Ergebnis-Datensätze einerSELECT-Abfrage.- Mit dem Befehl
INSERTkönnen Sie neue Datensätze in eine Tabelle einfügen. UPDATEhingegen ermöglicht das Verändern eines Datensatzes.- Mit
DELETEkönnen Sie einen Datensatz löschen. Die Angabe einerWHERE-Bedingung ist dann erforderlich, wenn Sie verhindern möchten, dass alle Datensätze der Tabelle gelöscht werden. - Mit
SELECT ... FROM ... INTO OUTFILE ...können Sie Daten aus einer Tabelle in eine Datei exportieren. - Mit
LOAD DATAhingegen können Sie Daten aus einer Datei in eine Tabelle importieren. - Zwei oder mehrere Tabellen können Sie entweder mit einer
WHERE-Bedingung oder derLEFT JOIN-Option miteinander verknüpfen.
# Fragen und Übungen
- Schreiben Sie eine SQL-Anweisung, die aus der Tabelle
tastaturendie Spaltenartikelnummer,bezeichnungundpreisausgibt. - Erweitern Sie die Anweisung aus Aufgabe 9 um die Sortierung der Datensätze anhand der Spalte
preisin absteigender Reihenfolge. - Schreiben Sie eine SQL-Anweisung, die aus der Tabelle
monitoredie Spaltengroesse,preisundanzahlausliest. Der Wert der Spaltepreissoll zwischen149.00und400.00liegen. - Schreiben Sie eine Anweisung, die aus der Tabelle
festplattenden kleinsten Wert, den größten Wert und einen Durchschnittswert ermittelt. Der Wert steht in der Spaltepreis. - Schreiben Sie eine SQL-Anweisung, die Datensätze aus einer Datei namens
prozessoren.datin die Tabelleprozessorenimportiert. Die einzelnen Felder werden in der Datei durch Rauten#und die einzelnen Datensätze durch einen Zeilumbruch getrennt. - Erstellen Sie eine SQL-Anweisung, die die Tabellen
artikelundlagermiteinander verknüpft. Als Verknüpfungskriterium soll aus beiden Tabellen die Spalteartikelnummergenutzt werden. Verwenden Sie sowohl die Variante mitWHEREals auch die Variante mitLEFT JOIN. Beachten Sie, dass die Tabellelageran die Tabelleartikelgeknüpft werden soll. - Schreiben Sie beide Verknüpfungsvarianten aus Aufgabe 14 mit Aliasnamen für die Tabellen.