Kapitel 3 - Buecher.de

Kapitel 3
Syntax und Verwendung von SQL
in MySQL
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
3.10
MySQL – was es hat und was es nicht hat
Namensregeln in MySQL
Datenbanken anlegen, verwerfen und auswählen
Tabellen anlegen, löschen, indizieren und bearbeiten
Informationen über Datenbanken und Tabellen ermitteln
Datensätze suchen
Kommentare
Lösungsvorschläge
Ausführung einer UNION-Operation
Funktionen, die MySQL nicht unterstützt
192
194
196
198
212
213
219
220
224
228
192
MySQL – was es hat und was es nicht hat
Für die Kommunikation mit dem MySQL-Server sollten Sie SQL fließend
beherrschen. Beispielsweise erlauben Ihnen die Funktionen des mysql-Clients,
SQL-Anweisungen zur Ausführung an den Server zu senden. Aber auch für die
Entwicklung von Programmen, die die von Ihrer Programmiersprache bereitgestellte MySQL-Schnittstelle nutzen, müssen Sie SQL beherrschen, weil Sie auch
hier mit dem Server kommunizieren, indem Sie ihm SQL-Anweisungen übergeben.
In Kapitel 1 finden Sie eine kurze Einführung in die Arbeitsweise von MySQL.
Das vorliegende Kapitel baut auf den dort bereitgestellten Informationen auf und
beschreibt einige der von MySQL implementierten SQL-Bereiche genauer. Hier
wird erklärt, wie man unter Berücksichtigung der Namensregeln und der Einschränkungen bei der Groß-/Kleinschreibung auf Elemente der Datenbank verweist. Außerdem werden einige der wichtigeren SQL-Anweisungen beschrieben,
beispielsweise zum Anlegen und Entfernen von Datenbanken, Tabellen und Indizes. Anweisungen für die Suche von Daten mit Hilfe von Joins sowie Anweisungen, die Informationen über Ihre Datenbanken und Tabellen liefern. Dabei werden insbesondere auch Erweiterungen vorgestellt, die MySQL zusätzlich zum
Standard-SQL unterstützt.
3.1
MySQL – was es hat und was es nicht hat
Die SQL-Anweisungen von MySQL können in mehrere Kategorien eingeordnet
werden, wie in Abbildung 3.1 gezeigt. In diesem Kapitel geht es um die Anweisungen aus den ersten vier der hier gezeigten Kategorien. Bei einigen der
MySQL-Dienstprogramme handelt es sich im Grunde um Kommandozeilenschnittstellen für bestimmte SQL-Anweisungen. Beispielsweise ist mysqlshow
eine Schnittstelle zur Anweisung SHOW COLUMNS. Solche Entsprechungen werden
in diesem Kapitel ebenfalls beschrieben.
Anweisungen, die hier keine Berücksichtigung finden, werden zum Teil in anderen Kapiteln beschrieben. Beispielsweise beschäftigt sich Kapitel 11 mit den
Anweisungen GRANT und REVOKE zur Einrichtung von Benutzerberechtigungen.
Die Aufrufsyntax für alle Anweisungen finden Sie in Anhang D. Darüber hinaus
sollten Sie im MySQL Reference Manual nach weiteren Informationen suchen,
insbesondere nach Änderungen in neueren MySQL-Versionen.
Der letzte Abschnitt des Kapitels beschreibt, was es in MySQL nicht gibt, d.h.,
welche Funktionen ihm fehlen. Dabei handelt es sich um Dinge, die in einigen
anderen Datenbanken unterstützt werden, nicht aber in MySQL. Unter anderem
sind das Sub-Selects, Transaktionen, referenzielle Integrität, Trigger, gespeicherte
Prozeduren und Sichten. Bedeutet das, dass MySQL gar kein »richtiges« Datenbanksystem ist? Einige Leute sind dieser Meinung, aber man sieht ja, dass das
Fehlen dieser Funktionen in MySQL viele Anwender nicht daran hindern konnte,
es einzusetzen. Viele Anwendungen brauchen nämlich diese Funktionalität gar
nicht. In anderen Fällen gibt es Möglichkeiten, fehlende Funktionen durch andere
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
193
zu ersetzen. Weil beispielsweise kein kaskadenförmiges Löschen möglich ist,
führen Sie beim Löschen von Datensätzen aus einer Tabelle eine zusätzliche
Anfrage in Ihrer Anwendung aus. Dass Transaktionen nicht unterstützt werden,
muss nicht unbedingt eine Rolle spielen. Statt dessen könnte es für Ihre Bedürfnisse ausreichen, wenn Sie die von MySQL gebotene Möglichkeit nutzen, Anweisungen durch Einschluss in LOCK TABLES- und UNLOCK TABLES-Anweisungen in
nicht zu unterbrechende Gruppen zusammenzufassen.
(Das eigentliche Problem ist hier nicht das Fehlen von Transaktionen, sondern
vielmehr das Fehlen eines automatischen Rückgängigmachens (Rollback) aller
Anweisungen, falls eine der Transaktionen fehlschlägt. Wenn Sie Anwendungen
einsetzen, in denen beispielsweise komplexe Finanztransaktionen auszuführen
sind, deren Anweisungen entweder vollständig oder überhaupt nicht ausgeführt
werden sollen, sollten Sie besser eine Datenbank mit Commit/Rollback-Fähigkeit
auswählen, wie beispielsweise Progress.) Einige Funktionen fehlen nur, weil sie
noch nicht implementiert worden sind. Beispielsweise gibt es zum Zeitpunkt der
Drucklegung dieses Buches noch keine Sub-Selects, aber für Version 3.24, die
Ihnen möglicherweise bereits vorliegt, sind sie bereits geplant.
Datenbanken anlegen, löschen und auswählen
CREATE DATABASE
DROP DATABASE
USE
Tabellen und Indexe erzeugen, ändern und löschen
ALTER TABLE
CREATE INDEX
CREATE TABLE
DROP INDEX
DROP TABLE
Informationen über Datenbanken, Tabellen und Anfragen erhalten
DESCRIBE
EXPLAIN
SHOW
Informationen aus Tabellen auswählen
SELECT
Informationen in Tabellen modifizieren
DELETE
INSERT
LOAD DATA
OPTIMIZE TABLE
REPLACE
UPDATE
Administrative Anweisungen
FLUSH
GRANT
KILL
REVOKE
Diverse Anweisungen
CREATE FUNCTION
DROP FUNCTION
LOCK TABLES
SET
UNLOCK TABLES
Abb. 3.1: Die in MySQL unterstützten SQL-Anweisungen
194
3.2
Namensregeln in MySQL
Namensregeln in MySQL
Jede SQL-Anweisung verweist in irgendeiner Form auf eine Datenbank oder
einen Datenbankeintrag. Dieser Abschnitt beschreibt die Syntaxregeln für Verweise auf Datenbanken, Tabellen, Spalten, Indizes und Aliase. Namen unterliegen
den Regeln zur Groß-/Kleinschreibung, die hier ebenfalls erklärt werden.
3.2.1
Verweise auf Datenbankelemente
Falls Sie für den Verweis auf Datenbankelemente Namen verwenden, sind Sie auf
die in Namen erlaubten Zeichen und die erlaubte Länge beschränkt. Das Format
der Namen hängt außerdem vom jeweiligen Kontext ab:
•
Erlaubte Zeichen in Namen. Bestehen aus alphanumerischen Zeichen des auf
dem Server verwendeten Zeichensatzes sowie den Zeichen _ und $. Namen
dürfen mit jedem zulässigen Zeichen beginnen, auch mit einer Ziffer. Sie dürfen jedoch nicht ausschließlich aus Ziffern bestehen, weil sie dann nicht mehr
von Zahlen unterschieden werden können. Einen Namen mit einer Ziffer zu beginnen ist eher ungewöhnlich. Falls Sie solche Namen verwenden, seien Sie
insbesondere bei Namen mit E oder e vorsichtig, weil diese Zeichen zu mehrdeutigen Ausdrücken führen können. 23e + 14 ist Spalte 23e plus 14, aber was
ist 23e+14? Ist es dasselbe oder ist es womöglich eine Zahl in wissenschaftlicher Notation?
•
Namenslänge. Namen für Datenbanken, Tabellen, Spalten und Indizes dürfen
bis zu 64 Zeichen umfassen. Aliasnamen können bis zu 256 Zeichen umfassen.
•
Namensqualifikationen. Um auf eine Datenbank zu verweisen, geben Sie einfach nur ihren Namen an:
USE db_name
SHOW TABLES FROM db_name
Um auf eine Tabelle zu verweisen, gibt es zwei Möglichkeiten. Ein voll qualifizierter Tabellenname besteht aus einem Datenbanknamen und einem Tabellennamen:
SHOW TABLES FROM db_name.tbl_name
SELECT * FROM db_name.tbl_name
Ein nicht weiter qualifizierter Tabellenname verweist auf eine Tabelle in der
(aktuellen) Standarddatenbank. Falls samp_db die Standarddatenbank ist, sind die
beiden folgenden Anweisungen äquivalent:
SELECT * FROM member
SELECT * FROM samp_db.member
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
195
Um auf eine Spalte zu verweisen, gibt es drei Möglichkeiten: voll qualifiziert,
teilweise qualifiziert oder nicht qualifiziert. Ein voll qualifizierter Name (also db_
name.tbl_name.spalten_name) beinhaltet alle Informationen. Ein teilweise qualifizierter Name (also tbl_name.spalten_name) verweist auf die im Kontext verwendete Tabelle. Die beiden folgenden Anfragen verweisen auf dieselben Spaltennamen, aber der Kontext in der FROM-Klausel gibt an, aus welcher Tabelle die
Spalten ausgewählt werden sollen:
SELECT last_name, first_name FROM president
SELECT last_name, first_name FROM members
In der Regel ist es nicht erforderlich, voll qualifizierte Namen anzugeben, obwohl
es natürlich auch nicht verboten ist. Wenn Sie mit einer USE-Anweisung eine
Datenbank auswählen, wird diese zur Standarddatenbank und in jedem nicht qualifizierten Tabellenverweis implizit angenommen. Wenn Sie eine SELECT-Anweisung verwenden, die nur auf eine einzige Tabelle verweist, wird diese Tabelle für
jeden Spaltenverweis innerhalb der Anweisung implizit vorausgesetzt. Namen
müssen nur qualifiziert werden, wenn eine Tabelle oder Datenbank nicht aus dem
Kontext erkannt werden kann. Hier einige Situationen, in denen es zu Mehrdeutigkeiten kommen könnte:
3.2.2
•
Anfragen, die auf Tabellen aus mehreren Datenbanken verweisen. Verweise
auf Tabellen, die sich nicht in der Standarddatenbank befinden, müssen im Format db_name.tbl_name erfolgen, so dass MySQL weiß, in welcher Datenbank
es die Tabelle suchen soll.
•
Anfragen, die eine Spalte aus mehreren Tabellen auswählen, wobei mehrere
Tabellen eine Spalte desselben Namens enthalten.
Groß-/Kleinschreibung in SQL-Anweisungen
Die Regeln zur Groß-/Kleinschreibung in SQL-Anweisungen variieren abhängig
von der Anweisungskomponente und auch davon, worauf Sie verweisen und unter
welchem Betriebssystem der Server ausgeführt wird:
•
SQL-Schlüsselwörter und Funktionsnamen. Bei Schlüsselwörtern und
Funktionsnamen wird die Groß-/Kleinschreibung nicht berücksichtigt. Sie
können groß, klein oder kombiniert geschrieben werden. Die folgenden Anweisungen sind äquivalent:
SELECT NOW()
select now()
sElEcT nOw()
•
Datenbank- und Tabellennamen. Datenbanken und Tabellen in MySQL entsprechen den Verzeichnissen und Dateien des auf dem Server-Host zu Grunde
liegenden Dateisystems. Die Groß-/Kleinschreibung für Datenbank- und Tabellennamen ist deshalb davon abhängig, wie das Betriebssystem auf diesem
196
Datenbanken anlegen, verwerfen und auswählen
Host Dateinamen behandelt. Ein Server unter UNIX berücksichtigt bei Datenbank -und Tabellennamen die Groß-/Kleinschreibung, unter Windows dagegen wird sie nicht berücksichtigt.
Achten Sie auf diese Eigenschaft, wenn Sie auf einem UNIX-Server eine
Datenbank einrichten, die möglicherweise irgendwann auf einen WindowsServer portiert wird. Wenn Sie zwei Tabellen namens abc und ABC anlegen,
können diese später auf der Windows-Maschine nicht mehr unterschieden
werden. Diese Situation können Sie vermeiden, indem Sie sich bei der Vergabe von Datenbank- und Tabellennamen für Groß- oder Kleinschreibung entscheiden. Das Portieren der Datenbank auf einen anderen Server bereitet dann
hinsichtlich der Namensschreibweise keine Probleme.
•
Spalten- und Indexnamen. Spalten- und Indexnamen berücksichtigen die
Groß-/Kleinschreibung nicht. Die folgenden Anfragen sind äquivalent:
SELECT name FROM student
SELECT NAME FROM student
SELECT nAmE FROM student
•
Aliasnamen. Aliase berücksichtigen die Groß-/Kleinschreibung. Sie können
einen Alias beliebig schreiben (Großbuchstaben, Kleinbuchstaben oder kombiniert), müssen aber überall in der Anfrage bei dieser Schreibweise bleiben.
Unabhängig davon, ob für einen Datenbank-, Tabellen- oder Aliasnamen zwischen Groß-und Kleinschreibung unterschieden wird, müssen Sie innerhalb einer
Anfrage bei einer einzigen Schreibweise bleiben. Das gilt nicht für SQL-Schlüsselwörter, Funktionsnamen oder Spalten- und Indexnamen, deren Schreibweise
innerhalb einer Anfrage variieren darf. Natürlich ist die Anfrage leichter lesbar,
wenn Sie eine konsistente Schreibweise verwenden, statt beliebig zu wechseln
(SelECt NamE FrOm ...).
3.3
Datenbanken anlegen, verwerfen und auswählen
MySQL unterstützt drei Anweisungen auf Datenbankebene: CREATE DATABASE
zum Anlegen von Datenbanken, DROP DATABASE zum Verwerfen von Datenbanken
und USE für die Auswahl einer Standarddatenbank.
3.3.1
Die Anweisung CREATE DATABASE
Es ist ganz einfach, eine Datenbank anzulegen: Sie geben sie einfach in einer CREATE DATABASE-Anweisung an:
CREATE DATABASE db_name
Der Name muss zulässig sein, die Datenbank darf noch nicht existieren, und Sie
müssen über die Berechtigung verfügen, sie anzulegen.
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
3.3.2
197
Die Anweisung DROP DATABASE
Das Löschen einer Datenbank ist genau so einfach wie das Anlegen, vorausgesetzt, Sie besitzen die entsprechenden Berechtigungen:
DROP DATABASE db_name
Die Ausführung der Anweisung DROP DATABASE sollte wohl überlegt sein. Sie
löscht die Datenbank und alle darin enthaltenen Tabellen. Nachdem Sie eine
Datenbank gelöscht haben, ist sie für immer verloren. Mit anderen Worten, probieren Sie diese Anweisung nicht einfach aus, um zu sehen, wie sie funktioniert.
Falls Ihr Administrator regelmäßig Sicherungen vornimmt, können Sie die Datenbank möglicherweise wieder herstellen. Aber mit Sicherheit wird Ihr Administrator nicht erfreut sein, wenn Sie ihm sagen »Ah, ich habe ein bisschen mit DROP
DATABASE herumgespielt, um zu sehen, was passiert, und ... äh... könnten Sie die
Datenbank bitte für mich wiederherstellen?«
Beachten Sie, dass eine Datenbank als Verzeichnis im Datenverzeichnis dargestellt wird. Wenn Sie Dateien in diesem Verzeichnis ablegen, bei denen es sich
nicht um Datenbanktabellen handelt, werden diese durch die Anweisung DROP
DATABASE nicht gelöscht. In diesem Fall wird auch das eigentliche Datenbankverzeichnis nicht gelöscht.
3.3.3
Die Anweisung USE
Die Anweisung USE macht eine Datenbank zur (aktuellen) Standarddatenbank für
eine bestimmte Verbindung zum Server:
USE db_name
Sie brauchen Zugriffsberechtigungen für eine Datenbank, damit Sie sie auf diese
Weise auswählen können. Es ist nicht erforderlich, eine Datenbank auszuwählen,
um ihre Tabellen nutzen zu können, weil Sie auch in der Form db_name.tbl_name
darauf verweisen können. Es ist jedoch viel bequemer, auf Tabellen zu verweisen,
ohne den Datenbanknamen dafür angeben zu müssen.
Die Auswahl einer Standarddatenbank bedeutet nicht, dass diese während der
gesamten Verbindung die Standarddatenbank bleiben muss. Sie können beliebig
viele USE-Anweisungen ausführen und zwischen den Datenbanken wechseln, falls
Sie die entsprechenden Zugriffsberechtigungen besitzen. Außerdem werden Sie
durch die Auswahl einer Standarddatenbank nicht auf diese beschränkt, so dass
Sie nur noch Tabellen aus dieser Datenbank verwenden könnten. Mit Hilfe qualifizierter Tabellennamen können Sie weiterhin auf Tabellen in anderen Datenbanken zugreifen.
Nachdem eine Verbindung zum Server abgebaut worden ist, vergisst dieser, welche Standarddatenbank verwendet wurde. Wenn Sie also eine neue Verbindung
einrichten, weiß der Server nicht mehr, welche Datenbank Sie zuvor benutzt
198
Tabellen anlegen, löschen, indizieren und bearbeiten
haben. Das wäre auch nicht besonders sinnvoll, weil MySQL Multithreading
unterstützt und mehrere Verbindungen von einem einzigen Benutzer verwalten
kann, der diese in beliebiger Reihenfolge einrichten und abbauen kann. In dieser
Umgebung wäre nicht klar, was »die zuvor ausgewählte Datenbank« bedeuten
könnte.
3.4
Tabellen anlegen, löschen, indizieren und bearbeiten
Mit Hilfe der Anweisungen CREATE TABLE, DROP TABLE und ALTER TABLE können
Sie in MySQL Tabellen anlegen, löschen und ihre Struktur ändern. Für diese
Anweisungen gibt es MySQL-spezifische Erweiterungen, die sie noch praktischer
machen. Die Anweisungen CREATE INDEX und DROP INDEX ermöglichen Ihnen,
Indizes für bereits existierende Tabellen anzulegen oder zu entfernen.
3.4.1
Die Anweisung CREATE TABLE
Mit CREATE TABLE werden Tabellen angelegt. Die vollständige Syntax für diese
Anweisung ist relativ kompliziert, weil es so viele Optionen gibt, aber in der Praxis ist ihre Anwendung relativ einfach. Beispielsweise waren alle in Kapitel 1
gezeigten CREATE TABLE-Anweisungen völlig unkompliziert.
Ironischerweise entsteht ein Großteil dieser zusätzlichen Komplexität durch die
Klauseln, die MySQL zuerst parst und dann verwirft! Mehr darüber erfahren Sie
in Anhang D. Lesen Sie im Abschnitt über CREATE TABLE nach und beachten Sie,
wie viel Syntax den Klauseln REFERENCES, CONSTRAINT und CHECK gewidmet ist.
Diese Klauseln betreffen Fremdschlüssel, referenzielle Integrität und Eingabewertbeschränkungen. MySQL unterstützt diese Funktionen nicht, parst aber die
Syntax, damit Sie Tabellendefinitionen, die in anderen Datenbanksystemen angelegt wurden, einfacher nutzen können. (Sie können diesen Code einfacher nutzen
und müssen weniger Änderungen daran vornehmen.) Wenn Sie Ihre eigenen,
ganz neuen Tabellenbeschreibungen entwickeln, können Sie diese Klauseln vollständig vergessen. Ich werde sie in diesem Abschnitt mit keinem Wort mehr
erwähnen.
CREATE TABLE muss mindestens den Tabellennamen sowie eine Spaltenliste ange-
ben, z.B.:
CREATE TABLE my_table
(
name CHAR(20),
age INT NOT NULL,
weight INT,
sex ENUM('F','M')
)
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
199
Neben den Spalten der Tabelle können Sie auch angeben, ob ein Index für die
Tabelle angelegt werden soll. Sie brauchen den Index jedoch nicht beim Anlegen
der Tabelle einzurichten, sondern können ihn später ergänzen. Das ist sinnvoll,
wenn Sie die Tabelle erst mit sehr vielen Daten füllen wollen, bevor Sie Anfragen
dafür ausführen. Die Indizes beim Einfügen der einzelnen Daten zu aktualisieren
ist viel zeitaufwändiger, als die Daten in eine nicht-indizierte Tabelle zu laden und
den Index dafür später anzulegen.
Kapitel 1 hat die grundlegende Syntax für die Anweisung CREATE TABLE bereits
erklärt. Kapitel 2 hat sich mit den Spaltentypen beschäftigt. Ich setze voraus, dass
Sie diese Kapitel gelesen haben, werde diese Dinge hier also nicht wiederholen.
Statt dessen geht es im restlichen Abschnitt um einige wichtige Erweiterungen
der Anweisung CREATE TABLE, die in MySQL 3.23 eingeführt wurden, und die
Ihnen beim Anlegen von Tabellen eine ausreichende Flexibilität bieten:
•
Angaben zum Speichertyp der Tabelle
•
Anlegen der Tabelle nur dann, falls diese noch nicht existiert
•
Temporäre Tabellen, die automatisch verworfen werden, nachdem die ClientSitzung beendet ist
•
Die Möglichkeit, eine Tabelle einfach durch Auswahl der darin darzustellenden Daten zu erzeugen
Bezeichner für den Tabellenspeichertyp
Vor MySQL 3.23 verwendeten alle vom Benutzer angelegten Tabellen die Speichermethode ISAM. In MySQL 3.23 können Sie explizit Tabellen mit drei unterschiedlichen Typen anlegen, indem Sie hinter der Spaltenlistenkomponente der
CREATE TABLE-Anweisung TYPE = typ angeben. typ kann MYSIAM, ISAM oder HEAP
sein, beispielsweise:
CREATE TABLE my_tbl (i INT, c CHAR(20)) TYPE = HEAP
Mit ALTER TABLE können Tabellen von einem Typ in einen anderen umgewandelt
werden:
ALTER TABLE my_tbl TYPE = ISAM
ALTER TABLE my_tbl TYPE = MYISAM
ALTER TABLE my_tbl TYPE = HEAP
Es ist wahrscheinlich nicht sinnvoll, eine Tabelle in den Typ HEAP umzuwandeln,
falls diese Ihren nächsten Serverabsturz überleben soll. HEAP-Tabellen werden
im Arbeitsspeicher verwaltet und gehen verloren, sobald der Server heruntergefahren wird oder ausfällt.
200
Tabellen anlegen, löschen, indizieren und bearbeiten
Diese drei Tabellentypen haben die folgenden Eigenschaften:
•
MyISAM-Tabellen. Das Speicherformat MyISAM ist seit Version 3.23 Standard in MySQL:
– Die Dateien können größer sein als bei der ISAM-Speichermethode, falls
Ihr Betriebssystem größere Dateien erlaubt.
– Daten können in einem maschinenunabhängigen Format gespeichert werden, wobei das unterste Byte am Anfang steht. Das bedeutet, Sie können
Tabellen von einer Maschine auf eine andere kopieren, auch wenn beide
unterschiedliche Architekturen aufweisen.
– Numerische Indexwerte belegen weniger Speicher, weil dabei das oberste
Byte zuerst abgelegt wird. Indexwerte sind in der Regel in den unteren
Bytes schneller, deshalb unterliegen die oberen Bytes eher einer Komprimierung.
– Die AUTO_INCREMENT-Verarbeitung ist besser als für ISAM-Tabellen. Die
Details werden in Kapitel 2 im Abschnitt »Folgen« erklärt.
– Einige Einschränkungen hinsichtlich des Index wurden gelockert. Beispielsweise können Sie einen Index für Spalten mit NULL-Werten und auch
für BLOB- und TEXT-Typen anlegen.
– Für eine verbesserte Integritätsprüfung der Tabellen wurde ein Flag eingeführt, das gesetzt wird, wenn die Tabelle von myisamchk überprüft wird. Mit
myisamchk -fast überspringen Sie Überprüfungen für Tabellen, die seit der
letzten Überprüfung nicht geändert wurden, wodurch administrative Aufgaben beschleunigt werden. Die Tabellen weisen außerdem ein Flag auf,
das angibt, ob sie korrekt geschlossen wurden. Wird der Server nicht korrekt heruntergefahren oder stürzt die Maschine ab, kann das Flag genutzt
werden, um Tabellen zu erkennen, die beim Neustarten des Servers überprüft werden müssen.
•
ISAM-Tabellen. Das Speicherformat ISAM ist das vor MySQL 3.23 verwendete Format, das heute jedoch weiterhin zur Verfügung steht. Im Allgemeinen
sollten statt ISAM besser MyISAM-Tabellen verwendet werden, weil sie weniger Einschränkungen aufweisen. Irgendwann werden ISAM-Tabellen nicht
mehr unterstützt, weil sie durch das MyISAM-Format ersetzt werden.
•
HEAP-Tabellen. Das Speicherformat HEAP erzeugt Tabellen im Speicher,
die Zeilen fester Länge aufweisen und damit sehr schnell sind. Außerdem sind
sie flüchtig, das heißt, sie gehen verloren, sobald der Server heruntergefahren
wird oder ausfällt. Im Gegensatz zu temporären Tabellen, die mit CREATE TEMPORARY TABLE angelegt werden, sind HEAP-Tabellen für andere Clients sichtbar. HEAP-Tabellen weisen einige Einschränkungen auf, die es für MyISAMund ISAM-Tabellen nicht gibt:
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
201
– Indizes werden nur für Vergleiche mit = und <=>verwendet.
– Für indizierte Spalten sind keine NULL-Werte erlaubt.
– Es sind keine BLOB- und TEXT-Spalten erlaubt.
– Es können keine AUTO_INCREMENT-Spalten verwendet werden.
Vorsorgliches Anlegen von Tabellen
Um eine Tabelle nur dann anzulegen, wenn sie noch nicht existiert, verwenden Sie
CREATE TABLE IF NOT EXISTS. Diese Anweisung ist sinnvoll für Anwendungen, die
nicht davon ausgehen, dass die von ihnen benötigten Tabellen bereits angelegt wurden, und die deshalb einfach immer versuchen, diese zu erstellen. Die Klausel IF
NOT EXISTS ist insbesondere für Scripts praktisch, die Sie mit mysql als Batch-Jobs
ausführen. In diesem Kontext funktioniert eine normale CREATE TABLE-Anweisung
nicht wie gewünscht. Bei der ersten Ausführung der Anwendung werden alle Tabellen korrekt angelegt, aber bei der zweiten Ausführung tritt ein Fehler auf, weil
die Tabellen bereits existieren. Bei der Angabe von IF NOT EXISTS gibt es kein Problem. Bei der ersten Ausführung der Anwendung werden die Tabellen wie zuvor
angelegt. Bei der zweiten und jeder folgenden Ausführung können die Tabellen
zwar wiederum nicht angelegt werden, aber es wird kein Fehler erzeugt. Auf diese
Weise kann die Ausführung fortgesetzt werden, nachdem der Versuch, die Tabellen
anzulegen, fehlgeschlagen ist.
Temporäre Tabellen
Mit CREATE TEMPORARY TABLES legen Sie temporäre Tabellen an, die nach Beenden
der Sitzung automatisch entfernt werden. Das ist praktisch, weil Sie dann nicht
jedes Mal explizit eine DROP TABLE-Anweisung ausführen müssen, um die Tabelle
löschen, und die Tabelle bleibt nicht zurück, falls Ihre Sitzung nicht ordnungsgemäß beendet wird. Wenn Sie beispielsweise eine Anfrage in einer Datei abgelegt
haben, die Sie mit mysql ausführen, und nicht auf das Beenden warten wollen,
können Sie das Script während der Ausführung problemlos unterbrechen, und der
Server entfernt alle temporären Tabellen.
In älteren Versionen von MySQL gab es keine echten temporären Tabellen, außer
in der Hinsicht, dass Sie sie für sich selbst als temporär betrachten konnten. In
Anwendungen, die solche Tabellen benötigen, müssen Sie darauf achten, sie
irgendwann zu löschen. Falls Sie das vergessen und in dem Client ein Fehler auftritt, der zum vorzeitigen Beenden führt, bleibt die temporäre Tabelle erhalten, bis
irgend jemand das bemerkt und sie entfernt.
Temporäre Tabellen sind nur für die Clients sichtbar, die sie angelegt haben. Sie
können dieselben Namen wie bereits existierende permanente Tabellen haben.
Das ist kein Fehler, und die permanente Tabelle wird dadurch auch nicht gestört.
Angenommen, Sie legen in der Datenbank samp_db die temporäre Tabelle member
an. Die ursprüngliche member-Tabelle wird verborgen (man kann nicht mehr darauf zugreifen) und verweist auf member als temporäre Tabelle. Wenn Sie jetzt die
202
Tabellen anlegen, löschen, indizieren und bearbeiten
Anweisung DROP TABLE member ausführen, wird die temporäre Tabelle entfernt und
die ursprüngliche member-Tabelle erscheint wieder. Falls Sie die Verbindung zum
Server abbauen, ohne die temporäre Tabelle zu entfernen, erledigt der Server das
automatisch für Sie. Wenn Sie das nächste Mal eine Verbindung einrichten, ist die
member-Tabelle wieder sichtbar.
Der Mechanismus, Namen zu verbergen, funktioniert nur auf einer Ebene. Das
bedeutet, dass Sie nicht zwei temporäre Tabellen desselben Namens anlegen können.
Tabellen aus SELECT-Ergebnissen anlegen
Eines der Schlüsselkonzepte relationaler Datenbanken ist, dass alles als Tabelle
mit Zeilen und Spalten dargestellt wird, und dass das Ergebnis jedes SELECT ebenfalls wieder eine Tabelle mit Zeilen und Spalten ist. Häufig ist die aus einem
SELECT resultierende »Tabelle« einfach nur ein Konstrukt aus Zeilen und Spalten,
die nach oben geblättert werden, sobald Sie weiterarbeiten. Vor MySQL 3.23
mussten für das Speichern der SELECT-Ergebnisse für weitere Anfragen spezielle
Vorkehrungen getroffen werden:
1. Ausführung einer DESCRIBE- oder SHOW COLUMNS-Anfrage, die die Typen der
Tabellenspalten ermittelt, aus denen Sie Informationen erhalten wollen.
2. Anlegen einer Tabelle, wobei explizit die Namen und Typen der oben ermittelten Spalten angegeben werden.
3. Ausführung einer INSERT...SELECT-Anfrage nach dem Anlegen der Tabellen,
um die Ergebnisse zu ermitteln und sie in die Tabelle einzufügen.
Mit MySQL 3.23 hat sich alles geändert. Durch die Anweisung CREATE
TABLE...SELECT werden alle diese Dinge überflüssig, und aus den Ergebnissen
beliebiger SELECT-Anfragen können dynamisch neue Tabellen angelegt werden.
Das erfolgt innerhalb eines einzigen Schritts, ohne dass Sie die Datentypen der
betreffenden Spalten kennen oder angeben müssen. Das macht es ganz einfach,
eine Tabelle mit all den Daten anzulegen, die Sie brauchen und die dann sofort in
weiteren Anfragen weiterverwendet werden können.
Sie können eine Tabelle kopieren, indem Sie den gesamten Inhalt markieren
(keine WHERE-Klausel), oder Sie legen eine leere Kopie an, indem Sie eine WHEREKlausel hinzufügen, die immer fehlschlägt:
CREATE TABLE new_tbl_name SELECT * FROM tbl_name
CREATE TABLE new_tbl_name SELECT * FROM tbl_name WHERE 1 = 0
Es ist dann sinnvoll, eine leere Kopie anzulegen, wenn Sie mit LOAD DATA eine
Datendatei in die ursprüngliche Datei laden wollen, aber nicht sicher sind, ob die
Optionen für das Dateiformat korrekt sind. Schließlich wollen Sie nicht die
Datensätze Ihrer Originaldatei zerstören, nur weil Sie die Optionen beim ersten
Mal nicht richtig angegeben haben! Mit Hilfe einer leeren Kopie der Original-
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
203
tabelle können Sie mit den Optionen für LOAD DATA experimentieren, um Spalten
und Zeilen auszuwählen, bis Sie davon überzeugt sind, dass Ihre Eingabedaten
korrekt interpretiert werden. Anschließend können Sie die Daten in die Originaltabelle laden.
Sie können CREATE TEMPORARY mit SELECT kombinieren, um eine temporäre
Tabelle als Kopie ihrer selbst anzulegen:
CREATE TEMPORARY TABLE my_tbl SELECT * FROM my_tbl
Auf diese Weise können Sie den Inhalt von my_tbl ändern, ohne dass das Original
dadurch beeinflusst wird. Das ist praktisch, wenn Sie Anfragen ausprobieren wollen, die den Inhalt der Tabelle ändern, ohne dabei jedoch sofort die Originaltabelle zu ändern. Vorgefertigte Scripts, die den Namen der Originaltabelle angeben, brauchen nicht geändert zu werden, um auf eine andere Tabelle zu
verweisen; fügen Sie einfach nur die Anweisung CREATE TEMPORARY TABLE am
Anfang des Scripts ein. Das Script legt eine temporäre Kopie an und arbeitet mit
dieser. Nachdem das Script abgearbeitet ist, löscht der Server die Kopie.
Um eine Tabelle als leere Kopie von sich selbst anzulegen, verwenden Sie die
Klausel WHERE 0 in Kombination mit CREATE TEMPORARY...SELECT:
CREATE TEMPORARY TABLE my_tbl SELECT FROM my_tbl WHERE 1 = 0
Beim dynamischen Anlegen von Tabellen müssen jedoch einige problematische
Aspekte berücksichtigt werden. Wenn Sie eine Tabelle anlegen, indem Sie Daten
aus einer Anfrage hineinkopieren, werden die Spaltennamen von den betreffenden Spalten übernommen. Wird eine Spalte als Ergebnis eines Ausdrucks berechnet, ist der »Name« der Spalte der Text des Ausdrucks. Ein Ausdruck ist kein
zulässiger Spaltenname. Das erkennen Sie bei der Ausführung der folgenden
Anfrage in mysql:
mysql> CREATE TABLE my_tbl SELECT 1;
ERROR 1166 at line 1: Incorrect column name '1'
Damit das funktioniert, geben Sie einen Spaltenalias an, so dass die Spalte einen
zulässigen Namen erhält:
mysql> CREATE TABLE my_tbl SELECT 1 AS my_col;
Query OK, 1 row affected (0.01 sec)
Ein ähnliches Problem tritt auf, wenn Sie Spalten aus unterschiedlichen Tabellen,
aber mit gleichem Namen auswählen. Angenommen, in den Tabellen t1 und t2
gibt es jeweils eine Spalte c, und Sie wollen eine Tabelle aus allen Zeilenkombinationen aus beiden Tabellen anlegen. Um eindeutige Namen in der neuen Tabelle
anzulegen, stellen Sie Aliase bereit:
CREATE TABLE t3 SELECT t1.c AS c1, t2.c AS c2 FROM t1, t2;
204
Tabellen anlegen, löschen, indizieren und bearbeiten
Beim Anlegen einer Tabelle über das Kopieren von Daten werden die Indizes der
Originaltabelle nicht automatisch kopiert.
3.4.2
Die Anweisung DROP TABLE
Es ist viel einfacher, eine Tabelle zu verwerfen, als sie anzulegen, weil Sie dabei
nichts zu ihrem Inhalt angeben müssen; Sie geben sie einfach nur an:
DROP TABLE tbl_name
MySQL hat die Anweisung DROP TABLE erweitert, wodurch einige praktische
Anwendungen möglich sind. Erstens können Sie mehrere Tabellen gleichzeitig
innerhalb einer einzigen Anweisung verwerfen:
DROP TABLE tbl_name1, tbl_name2, ...
Zweitens: Wenn Sie nicht sicher sind, ob eine Tabelle existiert, diese jedoch gegebenenfalls löschen wollen, fügen Sie der Anweisung die Klausel IF EXISTS hinzu.
Auf diese Weise beschwert sich MySQL nicht und gibt keinen Fehler aus, falls
der in der Anweisung verwendete Tabellenname nicht existiert:
DROP TABLE IF EXISTS tbl_name
IF EXISTS ist nützlich in Scripts, die Sie im Client-Programm mysql ausführen,
weil dieses beim Auftreten eines Fehlers automatisch beendet wird und den Versuch, eine nicht existente Tabelle zu entfernen, als Fehler wertet. Beispielsweise
könnten Sie ein Setup-Script einsetzen, das die Tabellen als Grundlage für die
weitere Verarbeitung in anderen Scripts anlegt. In dieser Situation werden Sie
sicherstellen wollen, dass das Setup-Script beim Aufruf eine saubere Ausgangsposition vorfindet. Wenn Sie am Anfang des Scripts regelmäßig die Anweisung
DROP TABLE ausführen, schlägt diese beim ersten Mal fehl, weil die Tabellen noch
nicht angelegt wurden. Durch IF EXISTS wird dieses Problem umgangen. Falls die
Tabellen existieren, werden sie entfernt, andernfalls wird das Script einfach fortgesetzt.
3.4.3
Indizes anlegen und verwerfen
Indizes beschleunigen den Zugriff auf Ihren Tabelleninhalt, insbesondere für
Anfragen mit Joins über mehrere Tabellen. Dies ist ein wichtiges Thema, das in
Kapitel 4 genauer besprochen wird, in dem es um Indizes und ihre Funktionsweise geht und wie Sie sie für die Optimierung Ihrer Anfragen am besten nutzen.
In diesem Abschnitt geht es um die Eigenschaften von Indexen und um die Syntax, mit der sie angelegt und gelöscht werden.
Indexeigenschaften
MySQL bietet eine relativ hohe Flexibilität beim Anlegen von Indexen. Der Index
kann für eine oder mehrere Spalten angelegt werden. Außerdem können Sie für
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
205
eine Spalte mehrere Indizes einführen, wenn Sie Werte aus unterschiedlichen
Spalten einer Tabelle schnell laden wollen. Hat eine Spalte einen Zeichenkettenoder ENUM- oder SET-Typ, können Sie nur die linken n Zeichen der Spalte mit
einem Index belegen. Ist die Spalte über die ersten n Zeichen eher eindeutig,
opfern Sie keine Leistung, sondern verbessern sie sogar: Durch die Indizierung
eines Spaltenpräfix anstelle der gesamten Spalte machen Sie den Index und damit
den Zugriff schneller.
Es gibt zwar auch einige Einschränkungen hinsichtlich des Index, die jedoch mit
der Weiterentwicklung von MySQL immer geringer geworden sind. Die folgende
Tabelle zeigt einige der Unterschiede zwischen ISAM- und MyISAM-Tabellen in
Hinblick auf die Indizierungsmöglichkeiten:
Indexeigenschaften
ISAM-Tabellen
MyISAM-Tabellen
NULL-Werte
Nicht erlaubt
Erlaubt
BLOB- und TEXT-Spalten
Können nicht indiziert
werden
Können indiziert
werden
Indizes pro Tabelle
16
32
Spalten pro Index
16
16
Maximale Zeilengröße für den Index
256 Byte
500 Byte
Aus dieser Aufstellung erkennen Sie, dass für ISAM-Tabellen indizierte Spalten
als NOT NULL deklariert werden müssen und keine BLOB- oder TEXT-Spalten sein dürfen. Für den Tabellentyp MyISAM wurden diese Einschränkungen aufgehoben
und andere gelockert. Eine Auswirkung dieser Unterschiede bezüglich der Indexeigenschaften der beiden Tabellentypen ist, dass Sie abhängig von Ihrer MySQLVersion unter Umständen einfach nicht in der Lage sind, einen Index für
bestimmte Spalten anzulegen. Beispielsweise können Sie in MySQL-Versionen
vor 3.23 nur ISAM-Tabellen anlegen, d.h. Spalten nur dann indizieren, wenn
keine NULL-Werte darin vorkommen dürfen.
Falls Sie MySQL 3.23 oder neuer einsetzen, haben Sie vielleicht ältere Tabellen,
die ursprünglich als ISAM-Tabellen angelegt wurden. Mit ALTER TABLE wird ihr
Speicherformat ganz einfach in MyISAM geändert, so dass Sie einige der neueren
Indexfunktionen nutzen können:
ALTER TABLE tbl_name TYPE = MYISAM
Indizes anlegen
Indizes für eine neue Tabelle werden bei Ausführung von CREATE TABLE angelegt
oder mit CREATE INDEX oder ALTER TABLE einer bereits existierenden Tabelle hinzugefügt. CREATE INDEX wurde in MySQL 3.22 eingeführt, aber Sie können auch
ALTER TABLE benutzen, falls Sie eine ältere MySQL-Version einsetzen. (MySQL
bildet momentan CREATE INDEX intern auf ALTER TABLE ab.)
206
Tabellen anlegen, löschen, indizieren und bearbeiten
Sie können festlegen, ob der Index doppelte Werte beinhalten darf. Wenn das
nicht der Fall ist, sollte der Index als PRIMARY KEY oder UNIQUE erzeugt werden.
Für einen eindeutigen Index über eine einzige Spalte wird damit sichergestellt,
dass die Spalte keine doppelten Werte enthält. Für einen eindeutigen Index über
mehrere Spalten wird sichergestellt, dass keine Kombination der betreffenden
Werte doppelt vorkommt.
Die als PRIMARY KEY und UNIQUE angelegten Indizes sind sehr ähnlich. Ein PRIMARY
KEY ist einfach nur ein UNIQUE-Index mit dem Namen PRIMARY. Das bedeutet, eine
Tabelle kann nur einen PRIMARY KEY enthalten, weil es nicht zwei Indizes mit demselben Namen geben darf. Sie können einer Tabelle jedoch mehrere UNIQUE-Indizes zuordnen, auch wenn das eher unüblich ist.
Um einer existierenden Tabelle einen Index zuzuordnen, verwenden Sie ALTER
TABLE oder CREATE INDEX. ALTER TABLE ist die flexiblere Anweisung, weil Sie
damit einen normalen Index, einen UNIQUE-Index oder einen PRIMARY KEY anlegen
können:
ALTER TABLE tbl_name ADD INDEX index_name (spalten_liste)
ALTER TABLE tbl_name ADD UNIQUE index_name (spalten_liste)
ALTER TABLE tbl_name ADD PRIMARY KEY (spalten_liste)
tbl_name ist der Name der Tabelle, der der Index hinzugefügt werden soll, und
spalten_liste gibt an, welche Spalten dabei berücksichtigt werden sollen. Besteht
der Index aus mehreren Spalten, trennen Sie diese durch Kommata voneinander ab.
Der Indexname index_name ist optional, Sie können ihn also weglassen. MySQL
wählt dann einen Namen abhängig von der ersten indizierten Spalte aus. ALTER TABLE erlaubt die Angabe mehrerer Tabellenänderungen innerhalb einer einzigen Anweisung, so dass Sie mehrere Indizes gleichzeitig anlegen können.
CREATE INDEX fügt einer Tabelle einen normalen oder einen UNIQUE-Index hinzu:
CREATE UNIQUE INDEX index_name ON tbl_name (spalten_liste)
CREATE INDEX index_name ON tbl_name (spalten_liste)
tbl_name, index_name und spalten_liste haben dieselbe Bedeutung wie für
ALTER TABLE. Der Indexname ist hier jedoch nicht optional. Es ist nicht möglich,
mit CREATE INDEX einen PRIMARY KEY anzulegen.
Um bei der Ausführung der Anweisung CREATE TABLE einen Index für eine neue
Tabelle anzulegen, verwenden Sie eine ähnliche Syntax wie für ALTER TABLE,
geben jedoch als Teil der Anweisung, in der die Tabellenspalten deklariert werden, die Klausel für das Erzeugen des Index an:
CREATE TABLE tbl_name
(
...
INDEX index_name (column_list),
UNIQUE index_name (column_list),
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
207
PRIMARY KEY (column_list),
...
)
Wie bei ALTER TABLE ist auch für INDEX und UNIQUE die Angabe des Indexnamens
optional, und MySQL erlaubt Ihnen, später einen Namen zuzuweisen oder ihn
ganz wegzulassen.
Als Sonderfall können Sie auch einen einspaltigen Primärschlüssel anlegen,
indem Sie am Ende der Spaltendeklaration PRIMARY KEY einfügen:
CREATE TABLE my_tbl
(
i INT NOT NULL PRIMARY KEY
)
Diese Anweisung ist äquivalent zur folgenden:
CREATE TABLE my_tbl
(
i INT NOT NULL,
PRIMARY KEY (i)
)
Jedes dieser Beispiele, in denen Tabellen angelegt werden, hat für die indizierten
Spalten NOT NULL vorgegeben. Für ISAM-Tabellen ist das eine Bedingung, weil
hier keine Indizes für Spalten mit NULL-Werten angelegt werden können. Für
MyISAM-Tabellen dürfen indizierte Spalten NULL-Werte enthalten, so lange der
Index kein PRIMARY KEY ist, also kein Primärschlüssel.
Wenn Sie einen Index für ein Präfix oder eine Zeichenkettenspalte anlegen (die
linken n Zeichen der Spaltenwerte), gilt für die Benennung der Spalte in einem
spalten_liste-Bezeichner die Syntax spalten_name(n) und nicht bloß spalten_
name. Die erste der folgenden Anweisungen beispielsweise erzeugt eine Tabelle
mit zwei CHAR-Spalten und einem Index, der beide Spalten verwendet. Die zweite
Anweisung ist ähnlich, erzeugt aber einen Index für ein Präfix jeder Spalte:
CREATE TABLE my_tbl
(
name CHAR(30),
address CHAR(60),
INDEX (name,address)
)
CREATE TABLE my_tbl
(
name CHAR(30),
address CHAR(60),
INDEX (name(10),address(20))
)
208
Tabellen anlegen, löschen, indizieren und bearbeiten
In bestimmten Situationen kann es erforderlich sein, einen Index für ein Spaltenpräfix anzulegen. Beispielsweise ist die Länge von Indexzeilen nach oben hin
begrenzt, und man muss Präfixe verwenden, falls die Länge der indizierten Spalten diese Obergrenze überschreitet. Präfixe werden auch für BLOB- und TEXT-Spalten in MyISAM-TabellenIndexe benötigt.
Durch die Indizierung eines Spaltenpräfix werden spätere Änderungen an der
Spalte eingeschränkt; Sie können die Spalte nicht kürzer machen als die Präfixlänge, ohne den Index zu verlieren und ihn später mit einem kürzeren Präfix neu
anlegen zu müssen.
Indizes löschen
Indizes werden mit den Anweisungen DROP INDEX oder ALTER TABLE gelöscht. DROP
INDEX wurde mit MySQL 3.22 eingeführt und wird intern momentan behandelt
wie ALTER TABLE. Die Syntax für die Anweisungen zum Löschen eines Index sieht
wie folgt aus:
DROP INDEX index_name ON tbl_name
ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP PRIMARY KEY
Die beiden ersten Anweisungen sind äquivalent. Die dritte wird nur verwendet,
wenn ein PRIMARY KEY gelöscht werden soll; in diesem Fall muss kein Indexname
angegeben werden, weil eine Tabelle nur einen einzigen Primärschlüssel haben
kann. Wurde kein Index explizit als PRIMARY KEY angelegt, verfügt die Tabelle
aber über einen oder mehrere UNIQUE-Indizes, löscht MySQL den ersten dieser
Indizes.
Wenn Sie Spalten einer Tabelle löschen, können davon auch die Indizes betroffen
sein. Wird eine Spalte gelöscht, die Teil eines Index ist, wird diese auch aus dem
Index gelöscht. Werden alle Spalten gelöscht, aus denen sich der Index zusammensetzt, wird der gesamte Index gelöscht.
3.4.4
Die Anweisung ALTER TABLE
ALTER TABLE ist eine sehr flexible Anweisung in MySQL, und Sie können damit
die unterschiedlichsten Dinge tun. Einige der Funktionen wurden bereits vorgestellt (beispielsweise das Anlegen und Löschen von Indexen oder die Umwandlung von Tabellen aus einem Speicherformat in ein anderes). In diesem Abschnitt
geht es um einige andere Fähigkeiten. Die vollständige Syntax von ALTER TABLE
ist in Anhang D beschrieben.
ALTER TABLE ist nützlich, wenn Sie feststellen, dass die Struktur einer Tabelle
nicht mehr ihrer Funktion entspricht. Möglicherweise wollen Sie zusätzliche
Informationen in der Tabelle ablegen, oder sie enthält überflüssige Informationen.
Vielleicht sind bereits existierende Spalten zu klein, oder Sie haben sie größer
deklariert, als es eigentlich notwendig ist, und Sie wollen sie verkleinern, um
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
209
Speicherplatz zu sparen und die Anfrageleistung zu verbessern. Oder vielleicht
haben Sie bei der Ausführung von CREATE TABLE einfach nur den Tabellennamen
falsch eingegeben. Hier einige Beispiele:
•
Sie führen eine web-basierte Umfrage aus und speichern die Ergebnisse als Datensätze in einer Tabelle. Anschließend wollen Sie die Umfrage um einige zusätzliche Fragen erweitern. Sie müssen der Tabelle neue Spalten hinzufügen,
um die Antworten auf die neuen Fragen ablegen zu können.
•
Sie führen ein Forschungsprojekt. Mit Hilfe einer AUTO_INCREMENT-Spalte weisen Sie den einzelnen Datensätzen Nummern zu. Anfangs gingen Sie davon
aus, dass Ihre Erhebungen über maximal 50000 Datensätze gingen, und haben
die Spalte mit dem Typ UNSIGNED SMALLINT angelegt, so dass sie maximal 65535
eindeutige Werte aufnehmen konnte. Das Projekt wurde neu dimensioniert,
und Sie benötigen weitere 50000 Datensätze. Sie müssen einen größeren Typ
verwenden, um mehr Nummern zuweisen zu können.
•
Der Faktor Größe kann sich auch in die andere Richtung ändern. Angenommen, Sie haben eine CHAR(255)-Spalte angelegt, erkennen aber jetzt, dass kein
Wert in der Tabelle länger als 100 Zeichen ist. Sie können die Spalte verkürzen, um Speicher zu sparen.
Die Syntax für ALTER TABLE sieht wie folgt aus:
ALTER TABLE tbl_name action,...
Jede action gibt eine Änderung an, die an der Tabelle vorgenommen werden soll.
MySQL hat die ALTER TABLE-Anweisung erweitert und erlaubt, mehrere Aktionen
anzugeben, die durch Kommata getrennt werden. Auf diese Weise ersparen Sie
sich Schreibarbeit, aber ein viel wichtigerer Grund für diese Erweiterung ist, dass
es unmöglich ist, Tabellen mit Zeilen variabler Länge in Tabellen fixer Länge
umzuwandeln, wenn nicht alle VARCHAR-Spalten gleichzeitig in CHAR-Spalten
umgewandelt werden können.
Die folgenden Beispiele demonstrieren einige andere Funktionen von ALTER
TABLE:
•
Umbenennung einer Tabelle. Das ist einfach; Sie geben dafür nur den alten
und den neuen Namen an:
ALTER TABLE tbl_name RENAME AS neuer_tbl_name
In MySQL 3.23, wo es temporäre Tabellen gibt, führt die Umbenennung einer
temporären Tabelle in einen in der Datenbank bereits existierenden Namen
dazu, dass die Originaltabelle verborgen bleibt, bis die temporäre Tabelle wieder gelöscht wird. Dasselbe passiert, wenn eine Tabelle verborgen wird, weil
eine temporäre Tabelle desselben Namens angelegt wird.
210
Tabellen anlegen, löschen, indizieren und bearbeiten
•
Ändern eines Spaltentyps. Der Spaltentyp wird in den Klauseln CHANGE oder
MODIFY geändert. Angenommen, die Spalte liegt in der Tabelle my_tbl als
SMALLINT UNSIGNED vor und Sie wollen einen MEIDUMINT UNSIGNED daraus
machen. Dazu führen Sie eine der folgenden Anweisungen aus:
ALTER TABLE my_tbl MODIFY i MEDIUMINT UNSIGNED
ALTER TABLE my_tbl CHANGE i i MEDIUMINT UNSIGNED
Warum wird die Spalte in der CHANGE-Anweisung zweimal angegeben? Weil
CHANGE im Gegensatz zu MODIFY in der Lage ist, die Spalte sowohl umzubenennen als auch den Typ zu ändern. Wollten Sie bei der Änderung des Typs die
Spalte gleichzeitig umbenennen, würden Sie folgendes schreiben:
ALTER TABLE my_tbl CHANGE i j MEDIUMINT UNSIGNED
Wichtig dabei ist, die Spalte anzugeben, die geändert werden soll, gefolgt von
einer vollständigen Spaltendeklaration inklusive Spaltenname. Außerdem
müssen Sie in der Deklaration den Namen angeben, auch wenn der alte Name
weiterverwendet wird.
Ein wichtiger Grund für die Änderung von Spaltentypen ist eine verbesserte
Anfrageeffizienz für Joins, die Spalten aus zwei Tabellen vergleichen. Ein
Vergleich ist schneller, wenn die verglichenen Spalten denselben Typ haben.
Angenommen, Sie führen die folgende Anfrage aus:
SELECT ... FROM t1, t2 WHERE t1.name = t2.name
Ist t1.name ein CHAR(10) und t2.name ein CHAR(15), erfolgt die Anfrage langsamer, als hätten beide den Typ CHAR(15). Sie gleichen sie an, indem Sie
t1.name mit einer der folgenden Anweisungen ändern:
ALTER TABLE t1 MODIFY name CHAR(15)
ALTER TABLE t1 CHANGE name name CHAR(15)
Für MySQL-Versionen vor 3.23 müssen über einen Join verbundene Spalten
denselben Typ haben, sonst können für den Vergleich keine Indizes verwendet
werden. Ab Version 3.23 können Indizes auch für ungleiche Typen verwendet
werden, aber die Anfrage ist trotzdem schneller, wenn die Typen identisch
sind.
•
Umwandlung einer Tabelle mit Zeilen variabler Länge in eine Tabelle mit
Zeilen fester Länge. Angenommen, Sie haben die Tabelle chartbl mit VARCHAR-Spalten, die in CHAR-Spalten umgewandelt werden sollen, um zu prüfen, ob
auf diese Weise Leistungsverbesserungen möglich sind. (Tabellen mit Zeilen
fester Länge können im Allgemeinen schneller verarbeitet werden als Tabellen
mit Zeilen variabler Länge.) Die Tabelle wurde wie folgt angelegt:
CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80))
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
211
Das Problem dabei ist, dass Sie alle Spalten gleichzeitig innerhalb einer einzigen ALTER TABLE-Anweisung ändern müssen. Sie können sie nicht nacheinander ändern, weil das schlicht nicht möglich ist. Die Ausführung von DESCRIBE
chartbl zeigt, dass die Spalten dann weiterhin als VARCHAR definiert sind.
Wenn Sie nämlich immer nur eine einzige Spalte entsprechend abändern,
bemerkt MySQL, dass in der Tabelle weiterhin Spalten variabler Länge enthalten sind, und wandelt die geänderten Spalten wieder in VARCHAR um, um
Speicherplatz zu sparen. Deshalb müssen alle VARCHAR-Spalten gleichzeitig
geändert werden:
ALTER TABLE chartbl MODIFY name CHAR(40), MODIFY address CHAR(80)
Jetzt zeigt DESCRIBE, dass die Tabelle CHAR-Spalten enthält. Und genau für
diese Operation ist es so wichtig, dass ALTER TABLE mehrere Aktionen innerhalb einer einzigen Anweisung unterstützt.
Es gibt jedoch eine Tücke, die Sie bei dieser Art der Tabellenumwandlung
beachten sollten: Wenn in einer Tabelle BLOB- oder TEXT-Spalten vorhanden
sind, ist es nicht möglich, sie in eine Tabelle mit Zeilen fester Länge umzuwandeln. Selbst wenn nur eine einzige Spalte eine variable Länge hat, hat die
Tabelle Zeilen variabler Länge, und für diese Spaltentypen gibt es kein Äquivalent fester Länge.
•
Umwandlung einer Tabelle mit Zeilen fester Länge in eine Tabelle mit
Zeilen variabler Länge. chartbl ist also schneller mit Zeilen fester Länge,
aber es belegt mehr Speicher, als Ihnen vielleicht recht ist, deshalb wollen Sie
sie möglicherweise wieder in das ursprüngliche Format umwandeln, um Speicher zu sparen. Die Umwandlung einer Tabelle in diese Richtung ist viel einfacher. Sie brauchen nur eine einzige CHAR-Spalte in VARCHAR umzuwandeln,
dann wandelt MySQL die anderen Spalten automatisch um. Um die chartblTabelle umzuwandeln, führen Sie eine der folgenden Anweisungen aus:
ALTER TABLE chartbl MODIFY name VARCHAR(40)
ALTER TABLE chartbl MODIFY address VARCHAR(80)
•
Umwandlung eines Tabellentyps. Wenn Sie von einer MySQL-Version vor
3.23 auf 3.23 oder höher umgestiegen sind, haben Sie möglicherweise ältere
Tabellen, die als ISAM-Tabellen angelegt wurden. Wenn Sie sie ins MyISAMFormat konvertieren möchten, gehen Sie wie folgt vor:
ALTER TABLE tbl_name TYPE = MYISAM
Warum sollten Sie das tun? Ein Grund dafür, der bereits im Abschnitt »Indizes
anlegen und verwerfen« beschrieben wurde, ist, dass das MyISAM-Speicherformat Indexfunktionen erlaubt, die im ISAM-Format nicht zur Verfügung stehen,
beispielsweise Indizes für Spalten mit NULL-Werten oder BLOB- und TEXT-Spalten.
Darüber hinaus sind MyISAM-Tabellen maschinenunabhängig, so dass Sie sie
auf andere Maschinen verschieben können, indem Sie die Tabellendateien direkt
212
Informationen über Datenbanken und Tabellen ermitteln
kopieren, auch wenn die Maschinen unterschiedliche Hardwarearchitekturen verwenden. Dies wird in Kapitel 11 im Abschnitt »Sichern und Kopieren von Datenbanken« genauer beschrieben.
3.5
Informationen über Datenbanken und Tabellen
ermitteln
MySQL unterstützt mehrere Anweisungen, mit deren Hilfe Sie Informationen
über Datenbanken und die darin enthaltenen Tabellen sammeln. Diese Anweisungen sind praktisch, um den Inhalt Ihrer Datenbanken zu überwachen und sich die
Struktur Ihrer Tabellen anzeigen zu lassen. Außerdem können Sie sie auch als
Hilfe bei der Verwendung von ALTER TABLE einsetzen; es ist einfacher, herauszufinden, wie eine Spalte geändert werden kann, wenn man weiß, wie diese momentan definiert ist.
Mit der SHOW-Anweisung ermitteln Sie unterschiedliche Informationen über Ihre
Datenbanken und Tabellen:
SHOW DATABASES
Listet die auf dem Server geführten Datenbanken auf
SHOW TABLES
Listet die Tabellen der aktuellen Datenbank
auf
SHOW TABLES FROM db_name
Listet die Tabellen in der angegebenen
Datenbank auf
SHOW COLUMNS FROM tbl_name
Zeigt Informationen über Spalten in der
angegebenen Tabelle an
SHOW INDEX FROM tbl_name
Zeigt Informationen über Indizes in der
angegebenen Tabelle an
SHOW TABLE STATUS
Zeigt beschreibende Informationen über
Tabellen in der Standarddatenbank an
SHOW TABLE STATUS FROM
db_name
Zeigt beschreibende Informationen über
Tabellen in der angegebenen Datenbank an
Die Anweisungen DESCRIBE tbl_name und EXPLAIN tbl_name sind synonym zu
SHOW COLUMNS FROM tbl_name.
Der Befehl mysqlshow zeigt einige derselben Informationen wie die SHOW-Anweisung, was Ihnen erlaubt, Informationen über Datenbanken und Tabellen von der
Shell abzurufen:
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
213
% mysqlshow
Listet die auf dem Server geführten Datenbanken auf
% mysqlshow db_name
Listet die Tabellen in der angegebenen
Datenbank auf
% mysqlshow db_name tbl_name
Zeigt Informationen über Spalten in der
angegebenen Tabelle an
% mysqlshow --keys db_name
Zeigt Informationen über Indizes in der
angegebenen Tabelle an
tbl_name
% mysqlshow --status db_name
Zeigt beschreibende Informationen über
Tabellen in der angegebenen Datenbank an
Mit dem Dienstprogramm mysqldum zeigen Sie die Struktur Ihrer Tabellen in
Form einer CREATE TABLE-Anweisung an. (Im Vergleich zur Anweisung SHOW
COLUMNS finde ich die Ausgabe von mysqldum einfacher zu lesen und es werden
außerdem die Indizes der Tabelle angezeigt.) Achten Sie bei der Ausführung von
mysqldump jedoch darauf, es mit der Option --no-data aufzurufen, so dass Sie
nicht von den Daten in Ihren Tabellen erschlagen werden!
% mysqldump --no-data db_name tbl_name
Sowohl für mysqlshow als auch für mysqldump können die üblichen Optionen angegeben werden, beispielsweise --host, um eine Verbindung zu einem Server auf
einem anderen Host einzurichten.
3.6
Datensätze suchen
Es wäre Unsinn, Datensätze in einer Datenbank abzulegen, würde man sie nicht
irgendwann wieder laden und irgendetwas damit tun. Das ist die Aufgabe der
SELECT-Anweisung: Sie hilft Ihnen, an Ihre Daten zu kommen. SELECT ist die vermutlich am häufigsten genutzte Anweisung in der Sprache SQL, kann aber auch
sehr kompliziert sein; die Beschränkungen für die Auswahl von Zeilen kann
beliebig komplex werden und Vergleiche zwischen Spalten verschiedener Tabellen bedingen.
Die grundlegende Syntax der SELECT-Anweisung sieht wie folgt aus:
SELECT auswahl_liste
Welche Spalten ausgewählt werden sollen
FROM tabellen_liste
Wo Zeilen ausgewählt werden sollen
WHERE primäre_bedingung
Welche Bedingungen die Zeilen erfüllen
müssen
GROUP BY spalten_gruppierung
Wie das Ergebnis gruppiert werden soll
214
Datensätze suchen
ORDER BY spalten_sortierung
Wie das Ergebnis sortiert werden soll
HAVING sekundäre_bedingung
Sekundäre Bedingungen, die die Zeilen
erfüllen müssen
LIMIT zähler
Begrenzung des Ergebnisses
Alles in dieser Syntax ist optional, außer dem Wort SELECT und der Komponente
spalten_liste, wo angegeben wird, was Sie suchen wollen. In einigen Datenbanken muss auch die FROM-Klausel angegeben werden. MySQL fordert das nicht, so
dass Sie Ausdrücke auswerten können, ohne auf irgendwelche Tabellen zu verweisen:
SELECT SQRT(POW(3,2)+POW(4,2))
Bereits in Kapitel 1 sind wir auf SELECT eingegangen, insbesondere in Hinblick
auf die Spaltenliste und die Klauseln WHERE, GROUP BY, ORDER BY, HAVING und LIMIT.
In diesem Kapitel geht es vor allem um den vielleicht schwierigsten Aspekt von
SELECT – den Join. Hier wird beschrieben, welche Join-Typen MySQL unterstützt,
was sie bedeuten und wie man sie ausführt. Damit sollte es Ihnen möglich sein,
MySQL effektiver zu nutzen, weil das eigentliche Problem bei der Entwicklung
einer Anfrage meistens ist, wie man die Tabellen korrekt miteinander verbindet.
Lesen Sie dazu auch den Abschnitt »Lösungsvorschläge« später in diesem Kapitel. Dort finden Sie Lösungen zu verschiedenen SQL-Problemen, die größtenteils
mit SELECT zu tun haben.
Ein Problem bei der Verwendung von SELECT ist, dass Sie bei einer neuen Aufgabe nicht immer sofort erkennen, mit welcher SELECT-Anfrage Sie ihr gerecht
werden können. Nachdem Sie es herausgefunden haben, können Sie jedoch diese
Erfahrung auf alle zukünftigen ähnlichen Probleme anwenden. SELECT ist vielleicht die Anweisung, für die Ihre Erfahrung die größte Rolle spielt, um sie effektiv nutzen zu können, weil es einfach so viele Möglichkeiten gibt, sie auszuführen.
Nachdem Sie eine gewisse Erfahrung gesammelt haben, wird Ihnen die Umsetzung neuer Aufgabenstellungen immer leichter fallen, und Sie werden irgendwann nur noch denken »Ach ja, das ist jetzt wieder diese Geschichte mit LEFT
JOIN«, oder »Aha, das ist ein Dreiwege-Join mit einer Einschränkung auf die
gemeinsamen Schlüsselspaltenpaare.« (Ich bin nicht ganz sicher, ob ich das so
sagen sollte. Vielleicht macht es Ihnen Mut, wenn Sie hören, dass Erfahrung Sie
weiter bringt. Andererseits finden Sie es vielleicht komisch, sich vorzustellen,
jemals Gedankengänge wie die oben beschriebenen haben zu können.)
In den nächsten Abschnitten, wo Sie die verschiedenen von MySQL unterstützten
Join-Operationen kennenlernen, werden für die meisten Beispiele die beiden folgenden Tabellen verwendet: Sie sind relativ klein, so dass Sie die Auswirkungen
der verschiedenen Joins sofort erkennen:
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
table t1:
+------+------+
| i1
| c1
|
+------+------+
|
1 | a
|
|
2 | b
|
|
3 | c
|
+------+------+
3.6.1
215
table t2:
+------+------+
| i2
| c2
|
+------+------+
|
2 | c
|
|
3 | b
|
|
4 | a
|
+------+------+
Der triviale Join
Der einfachste Join ist der triviale Join, wofür nur eine einzige Tabelle angegeben
wird. In diesem Fall werden Zeilen aus der angegebenen Tabelle ausgewählt:
SELECT ... FROM t1
+------+------+
| i1
| c1
|
+------+------+
|
1 | a
|
|
2 | b
|
|
3 | c
|
+------+------+
Einige Autoren berücksichtigen diese Form von SELECT mit Join nicht und gehen
nur auf SELECT-Anweisungen ein, die Datensätze aus zwei oder mehr Tabellen
ermitteln. Das ist alles eine Frage der Perspektive.
3.6.2
Der vollständige Join
Werden mehrere Tabellen durch Komma voneinander abgetrennt angegeben, findet ein vollständiger Join statt. Wenn Sie beispielsweise zwei Tabellen mit einem
Join verbinden, wird jede Zeile aus der ersten Tabelle mit jeder Zeile der zweiten
Tabelle verbunden:
SELECT t1.*, t2.* FROM t1, t2
+------+------+------+------+
| i1
| c1
| i2
| c2
|
+------+------+------+------+
|
1 | a
|
2 | c
|
|
2 | b
|
2 | c
|
|
3 | c
|
2 | c
|
|
1 | a
|
3 | b
|
|
2 | b
|
3 | b
|
|
3 | c
|
3 | b
|
|
1 | a
|
4 | a
|
|
2 | b
|
4 | a
|
|
3 | c
|
4 | a
|
+------+------+------+------+
216
Datensätze suchen
Ein vollständiger Join wird auch als Kreuz-Join bezeichnet, weil jede Zeile jeder
Tabelle mit jeder Zeile jeder anderen Tabelle verbunden wird, um alle möglichen
Kombinationen zu erhalten. Man spricht auch vom kartesischen Produkt. Durch
diese Form der Tabellenverknüpfung entstehen möglicherweise sehr viele Zeilen.
Die Anzahl der Zeilen ist das Produkt der Zeilen aller Tabellen. Ein vollständiger
Join für drei Tabellen mit 100, 200 bzw. 300 Datensätzen erzeugt also 100 * 200 *
300 = 6 Millionen Zeilen. Das ist eine ganze Menge, obwohl die einzelnen Tabellen noch relativ klein sind. Normalerweise verwendet man eine WHERE-Klausel,
um ein überschaubareres Ergebnis zu erhalten.
Wenn Sie der WHERE-Klausel eine Bedingung hinzufügen, die mit einem der Werte
bestimmter Spalten übereinstimmen muss, entsteht ein sogenannter Equi-Join,
weil Sie nur Zeilen mit gleichen Werten in den angegebenen Spalten auswählen:
SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2
+------+------+------+------+
| i1
| c1
| i2
| c2
|
+------+------+------+------+
|
2 | b
|
2 | c
|
|
3 | c
|
3 | b
|
+------+------+------+------+
Die Join-Typen JOIN, CROSS JOIN und INNER JOIN sind äquivalent zum JoinOperator.
Ein STRAIGHT_JOIN ist ein vollständiger Join, aber die Tabellen werden in der in
der FROM-Klausel angegebenen Reihenfolge verknüpft. Normalerweise ordnet der
Optimierer von MySQL die Reihenfolge der Tabellen in einem vollständigen Join
so an, dass die Zeilen schneller gefunden werden. Manchmal trifft er dabei jedoch
eine nicht-optionale Auswahl, die Sie mit dem Schlüsselwort STRAIGHT_JOIN
überschreiben.
STRAIGHT_JOIN kann innerhalb einer SELECT-Anweisung an zwei Stellen angegeben werden. Sie können es zwischen dem Schlüsselwort SELECT und der Auswahlliste angeben, so dass es sich global auf alle vollständigen Joins der Anweisung
auswirkt, oder in der FROM-Klausel. Die beiden folgenden Anweisungen sind äquivalent:
SELECT STRAIGHT_JOIN ... FROM table1, table2, table3 ...
SELECT ... FROM table1 STRAIGHT_JOIN table2 STRAIGHT_JOIN table3 ...
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
217
Qualifizieren von Spaltenverweisen
Verweise auf Tabellenspalten in einer SELECT-Anweisung müssen sich den in
der FROM-Klausel angegebenen Tabellen eindeutig zuordnen lassen. Ist hier
nur eine einzige Tabelle angegeben, entstehen keine Mehrdeutigkeiten, weil
die Spalten dieser Tabelle zugehören müssen. Werden mehrere Tabellen angegeben, sind alle Spaltennnamen, die nur in einer dieser Tabelle vorkommen, ebenfalls eindeutig. Wird jedoch ein Spaltenname in mehreren Tabellen verwendet, müssen die Verweise auf die Spalte qualifiziert werden. Mit
Hilfe der Syntax tbl_name.spalten_name geben Sie an, welche Tabelle Sie
meinen. Enthält die Tabelle my_tbl1 die Spalten a und b und die Tabelle my_
tbl2 die Spalten b und c, sind die Verweise auf die Spalten a und c eindeutig, aber die Verweise auf b müssen entweder als my_tbl1.b oder als my_
tbl2.b qualifiziert werden:
SELECT a, my_tbl1.b, my_tbl2.b, c FROM my_tbl1, my_tbl2 …
Manchmal ist ein qualifizierter Tabellenname nicht ausreichend, um einen
Spaltenverweis aufzulösen. Verwenden Sie beispielsweise eine Tabelle in
einer Anfrage mehrmals, ist es nicht sinnvoll, eine Spalte mit dem Tabellennamen zu qualifizieren. In diesem Fall ist die Verwendung von Tabellenaliasen sinnvoll, um Ihre Absichten verständlich formulieren zu können. Sie
weisen jeder Instanz der Tabelle einen Alias zu und verweisen auf die Spalten dieser Instanz als alias_name.spalten_name. Die folgende Anfrage verknüpft eine Tabelle mit sich selbst, weist aber einer Instanz der Tabelle einen
Alias zu, so dass die Spaltenverweise eindeutig sind:
SELECT my_tbl.col1, m.col2
FROM my_tbl, my_tbl AS m
WHERE my_tbl.col1 > m.col1
3.6.3
Der Left-Join
Ein Equi-Join zeigt nur Zeilen an, für die in beiden Tabellen eine Übereinstimmung gefunden wurde. Ein Left-Join zeigt ebenfalls Übereinstimmungen an, aber
auch Zeilen aus der linken Tabelle, für die es in der rechten Tabelle keine Übereinstimmung gibt. Alle Spalten, die für solche Zeilen in der rechten Tabelle
gesucht aber nicht gefunden wurden, werden als NULL angezeigt. Dabei werden
aus der linken Tabelle alle Zeilen ausgewählt. Gibt es eine übereinstimmende
Zeile in der rechten Tabelle, wird diese ausgewählt. Gibt es keine Übereinstimmung, wird die Zeile trotzdem ausgewählt, ist dabei aber gar keine gültige Zeile,
weil alle ihre Spalten auf NULL gesetzt werden. Mit anderen Worten, ein LEFT JOIN
erzwingt, dass das Ergebnis für jede Zeile in der linken Tabelle eine Zeile enthält,
egal ob es in der rechten Tabelle eine Übereinstimmung dafür gibt. Der Vergleich
218
Datensätze suchen
erfolgt für die Spalten in einer ON- oder USING()-Klausel. ON kann immer verwendet werden, unabhängig davon, ob die beiden verknüpften Spalten denselben
Namen haben:
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2
+------+------+------+------+
| i1
| c1
| i2
| c2
|
+------+------+------+------+
|
1 | a
| NULL | NULL |
|
2 | b
|
2 | c
|
|
3 | c
|
3 | b
|
+------+------+------+------+
Die USING()-Klausel ist ON ganz ähnlich, aber hier müssen die Namen der verknüpften Spalten in den Tabellen gleich sein. Die folgende Anfrage verknüpft my_
tbl1.b mit my_tbl2.b:
SELECT my_tbl1.*, my_tbl2.* FROM my_tbl1 LEFT JOIN my_tbl2 USING (b)
LEFT JOIN ist insbesondere in Situationen nützlich, in denen Sie nur die Zeilen der
linken Tabelle herausfinden wollen, die in der rechten Tabelle nicht vorkommen.
Dazu fügen Sie eine WHERE-Klausel ein, die nach Zeilen sucht, für die die rechte
Tabelle NULL-Werte einträgt:
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS
NULL
+------+------+------+------+
| i1
| c1
| i2
| c2
|
+------+------+------+------+
|
1 | a
| NULL | NULL |
+------+------+------+------+
Normalerweise sind uns die Spalten egal, die NULL ergeben, weil sie nicht von
Bedeutung sind. Aber hier suchen Sie nach Spalten der linken Tabelle, für die es
keine Übereinstimmung gibt:
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NULL
+------+------+
| i1
| c1
|
+------+------+
|
1 | a
|
+------+------+
Beachten Sie beim LEFT JOIN, dass, wenn die Spalten, die Sie verknüpfen, als NOT
NULL deklariert sind, Sie möglicherweise Zeilen im Ergebnis erhalten, die keine
Bedeutung haben.
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
219
LEFT JOIN hat einige Synonyme und Varianten. LEFT OUTER JOIN ist ein Synonym
für LEFT JOIN. Außerdem gibt es eine ODBC-Notation für LEFT JOIN, die auch von
MySQL akzeptiert wird (das oj steht für outer join):
{ oj tbl_name LEFT OUTER JOIN tbl_name ON join_ausdruck }
NATURAL LEFT JOIN ist ähnlich dem LEFT JOIN; es führt einen LEFT JOIN aus,
wobei alle Spalten verglichen werden, die in der linken und in der rechten Tabelle
denselben Namen haben.
In einigen Datenbanken gibt es einen entsprechenden RIGHT JOIN; in MySQL gibt
es das noch nicht.
3.7
Kommentare
MySQL erlaubt Ihnen, Ihren SQL-Code mit Kommentaren zu versehen. Das kann
sehr praktisch sein, um die in Dateien gespeicherten Anfragen zu dokumentieren.
Kommentare können auf zweierlei Weise dargestellt werden. Alles von einem #Zeichen bis zum Zeilenende wird als Kommentar betrachtet. Außerdem können
Kommentare im C-Stil verwendet werden. Das bedeutet, alles zwischen /* und */
als Kennzeichnen für Anfang und Ende wird als Kommentar betrachtet. Die Kommentare im C-Stil können sich über mehrere Zeilen erstrecken:
# Dies ist ein einzeiliger Kommentar
/* Auch dies ist ein einzeiliger Kommentar */
/* Dies hingegen
ist ein mehrzeiliger
Kommentar
*/
Seit MySQL 3.23 können Sie MySQL-spezifische Schlüsselwörter in Kommentaren im C-Stil verbergen, indem Sie diesen mit /*! statt mit /* beginnen. MySQL
sucht innerhalb dieses Kommentars nach Schlüsselwörtern, während andere
Datenbank-Server sie als Teil des Kommentars ignorieren. Auf diese Weise können Sie Code schreiben, der MySQL-spezifische Funktionen nutzt, wenn er von
MySQL ausgeführt wird, aber auch ohne weitere Änderungen von anderen
Datenbank-Servern ausgeführt werden kann. Die beiden folgenden Anweisungen
sind äquivalent für andere Datenbank-Server als MySQL, aber MySQL führt für
die zweite eine INSERT DELAYED-Operation aus:
INSERT INTO absence (student_id,date) VALUES(13,"1999-09-28")
INSERT /*! DELAYED */ INTO absence (student_id,date) VALUES(13,"1999-0928")
Seit MySQL 3.23.3 können Sie neben den oben beschriebenen Stilen einen Kommentar auch mit zwei Trennstrichen und einem Leerzeichen beginnen (--); alles
von den Trennstrichen bis zum Zeilenende wird als Kommentar betrachtet. Einige
220
Lösungsvorschläge
andere Datenbanken kennzeichnen den Kommentar mit einem doppelten Trennstrich. MySQL erlaubt diese Schreibweise, fordert jedoch die Angabe eines Leerzeichens, um Mehrdeutigkeiten zu vermeiden.
Bei Anweisungen mit Ausdrücken wie 5--7 könnte man sonst meinen, dass sie
einen Kommentar enthalten. Es wäre aber eher unwahrscheinlich, dass Sie einen
solchen Ausdruck als 5-- 7 schreiben, es ist also eine sinnvolle Methode. Dennoch ist es besser, einen der anderen Kommentarstile zu verwenden und die doppelten Trennstriche nur in dem Code beizubehalten, den Sie aus anderen Datenbanken übernehmen.
3.8
Lösungsvorschläge
Dieser Abschnitt zeigt, wie Sie Anfragen für unterschiedliche Aufgaben schreiben. Größtenteils handelt es sich dabei um Lösungen für Probleme, die immer
wieder auf der Mailing-Liste auftauchen. (Danke den Leuten der Liste, die die
Antworten bereitgestellt haben.)
3.8.1
Subselects als Joins umformulieren
Ab Version 3.24 gibt es in MySQL Subselects. Diese Funktion wurde in MySQL
am meisten vermisst, aber viele Benutzer erkannten einfach nicht, dass Subselects
größtenteils auch als Join ausgedrückt werden können. Und selbst wenn MySQL
Subselects unterstützt, sollte man auf die Formulierung der Anfragen achten, weil
Joins in der Mehrzahl der Fälle effizienter sind als Subselects.
Subselects umformulieren, die übereinstimmende Werte auswählen
Hier folgt eine Beispielanfrage mit einem Subselect; sie wählt Noten aus der
Tabelle score für alle Tests aus (d.h. die Noten für die Stegreifaufgaben werden
ignoriert):
SELECT * FROM score
WHERE event_id IN (SELECT event_id FROM event WHERE type = "T")
Dieselbe Anfrage kann ohne Subselect ausgeführt werden, indem man sie in
einen einfachen Join umwandelt.
SELECT score.* FROM score, event
WHERE score.event_id = event.event_id AND event.type = "T"
Das nächste Beispiel wählt die Noten der weiblichen Schüler aus:
SELECT * from score
WHERE student_id IN (SELECT student_id FROM student WHERE sex = "F")
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
221
Auch das kann in einen Join umgewandelt werden:
SELECT score.* FROM score, student
WHERE score.student_id = student.student_id AND student.sex = "F"
Hier gibt es ein Muster. Die Subselect-Anfragen weisen die folgende Form auf:
SELECT * FROM table1
WHERE spalte1 IN (SELECT spalte2 FROM table2a WHERE spalte2b = wert)
Solche Anfragen können wie folgt in einen Join umgewandelt werden:
SELECT table1.* FROM table1, table2
WHERE table1.spalte1 = table2.spalte2a AND table2.spalte2b = wert
Umformulierung von Subselects, die nicht übereinstimmende Werte
auswählen
Ein weiterer gebräuchlicher Typ einer Subselect-Anfrage sucht nach Werten in
einer Tabelle, die nicht in einer anderen Tabelle vorhanden sind. Wie wir zuvor
gesehen haben, ist die Aufgabenstellung »deren Werte nicht vorhanden sind« ein
Hinweis darauf, dass ein LEFT JOIN verwendet werden könnte. Hier eine Anfrage,
die das Fehlen von Werten in einer Tabelle überprüft (sie findet alle Schüler, die
nie abwesend waren):
SELECT * FROM student
WHERE student_id NOT IN (SELECT student_id FROM absence)
Diese Anfrage kann unter Verwendung eines LEFT JOIN wie folgt umformuliert
werden:
SELECT student.*
FROM student LEFT JOIN absence ON student.student_id = absence.student_
id
WHERE absence.student_id IS NULL
Allgemein ausgedrückt hat die Subselect-Anfrage die folgende Form:
SELECT * FROM table1
WHERE spalten1 NOT EXISTS (SELECT spalte2 FROM table2)
Eine Anfrage dieser Form könnte wie folgt umformuliert werden:
SELECT table1.*
FROM table1 LEFT JOIN table2 ON table1.spalte1 = table2.spalte2
WHERE table2.spalte2 IS NULL
Dabei wird vorausgesetzt, dass table2.spalte2 als NOT NULL deklariert ist.
222
3.8.2
Lösungsvorschläge
Werte suchen, die in einer Tabelle nicht vorhanden sind
Im Abschnitt »Datensätze suchen« haben wir bereits gezeigt, wie man feststellt,
welche Werte in einer Tabelle, nicht aber in einer anderen Tabelle vorhanden sind,
indem man einen LEFT JOIN für die beiden Tabellen ausführt und nach Zeilen
sucht, die für die zweite Tabelle NULL ergeben. Eine einfache Situation wurde
anhand der beiden folgenden Tabellen demonstriert:
table t1:
+------+------+
| i1
| c1
|
+------+------+
|
1 | a
|
|
2 | b
|
|
3 | c
|
+------+------+
table t2:
+------+------+
| i2
| c2
|
+------+------+
|
2 | c
|
|
3 | b
|
|
4 | a
|
+------+------+
Der LEFT JOIN, mit dem alle Werte t1.i1 ermittelt werden, die in der Spalte t2.i2
nicht vorkommen, sieht wie folgt aus:
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NULL
+------+------+
| i1
| c1
|
+------+------+
|
1 | a
|
+------+------+
Jetzt betrachten wir eine kompliziertere Version der Frage »Welche Werte fehlen?«. Für das Notenverwaltungsprojekt aus Kapitel 1 gibt es eine Tabelle student, die alle Schüler enthält, und eine Tabelle event, die die Notenereignisse
festhält, sowie eine Tabelle score, die die Punktewerte auflistet, die die Schüler
bei diesen Prüfungen erreicht haben. War ein Schüler jedoch am Tag einer Prüfung krank, gibt es in der score-Tabelle keinen Eintrag für den Schüler und diese
Prüfung, so dass er eine Nachholprüfung absolvieren muss. Wie können wir die
fehlenden Datensätze ermitteln, um sicherzustellen, dass alle diese Schüler eine
Nachholprüfung erhalten?
Das Problem dabei ist, festzustellen, welche Schüler keinen Punktwert für eine
Prüfung haben. Man könnte auch fragen, welche Kombination aus Schüler und
Ereignis nicht in der Punktetabelle enthalten ist. Die Fragestellung »welche Werte
sind nicht vorhanden« ist ein Hinweis auf die Verwendung eines LEFT JOIN. Dieser Join ist jedoch nicht so einfach wie im vorigen Beispiel, weil wir nicht nur
nach Werten suchen, die in einer einzigen Spalte nicht vorhanden sind; vielmehr
suchen wir nach einer zweispaltigen Kombination.
Wir brauchen alle Schüler/Ereignis-Kombinationen, die durch Kreuzen der
Tabelle student mit der Tabelle event entstehen:
FROM student, event
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
223
Für das Ergebnis dieses Joins führen Sie ein LEFT JOIN mit der score-Tabelle aus,
um die Übereinstimmungen zu finden:
FROM student, event
LEFT JOIN score ON student.student_id = score.student.id
AND event.event_id = score.event_id
Beachten Sie die ON-Klausel. Sie erlaubt, die Zeilen der score-Tabelle mit den
Übereinstimmungen in anderen Tabellen zu verknüpfen. Das ist der Schlüssel zu
unserem Problem. Der LEFT JOIN erzwingt, dass für jede der im Kreuz-Join der
Tabellen student und event erzeugten Zeilen eine Zeile erzeugt wird, auch wenn
es keinen entsprechenden Eintrag in der score-Tabelle gibt. Die Zeilen für die
fehlenden Punktewertdatensätze in der Ergebnismenge werden daran erkannt,
dass die Spalte aus der score-Tabelle gleich NULL ist. Wir wählen diese Datensätze
in der WHERE-Klausel aus. Alle Spalten aus der score-Tabelle könnten dazu herangezogen werden, aber weil wir nach fehlenden Punktewerten suchen, sollten wir
der Klarheit halber die score-Spalte überprüfen:
WHERE score.score IS NULL
Mit Hilfe einer ORDER BY-Klausel sortieren wir die Ereignisse. Logisch wäre ein
Sortieren nach dem Schüler oder nach dem Ereignis. Hier wollen wir die erste
Methode zeigen:
ORDER BY student.student_id, event.event_id
Jetzt brauchen wir nur noch die Spalten anzugeben, die wir in der Ausgabe sehen
wollen, und fertig. Hier die endgültige Anfrage:
SELECT
student.name, student.student_id,
event.date, event.event_id, event.type
FROM
student, event
LEFT JOIN score ON student.student_id = score.student_id
AND event.event_id = score.event_id
WHERE
score.score IS NULL
ORDER BY
student.student_id, event.event_id
Die Ausführung dieser Anfrage führt zum folgenden Ergebnis:
224
Ausführung einer UNION-Operation
+-----------+------------+------------+----------+------+
| name
| student_id | date
| event_id | type |
+-----------+------------+------------+----------+------+
| Megan
|
1 | 1999-09-16 |
4 | Q
|
| Joseph
|
2 | 1999-09-03 |
1 | Q
|
| Katie
|
4 | 1999-09-23 |
5 | Q
|
| Devri
|
13 | 1999-09-03 |
1 | Q
|
| Devri
|
13 | 1999-10-01 |
6 | T
|
| Will
|
17 | 1999-09-16 |
4 | Q
|
| Avery
|
20 | 1999-09-06 |
2 | Q
|
| Gregory |
23 | 1999-10-01 |
6 | T
|
| Sarah
|
24 | 1999-09-23 |
5 | Q
|
| Carter
|
27 | 1999-09-16 |
4 | Q
|
| Carter
|
27 | 1999-09-23 |
5 | Q
|
| Gabrielle |
29 | 1999-09-16 |
4 | Q
|
| Grace
|
30 | 1999-09-23 |
5 | Q
|
+-----------+------------+------------+----------+------+
Hier gibt es einen interessanten Aspekt. Die Ausgabe zeigt die Schüler-ID und die
Ereignis-ID an. Die Spalte student_id wird sowohl in der Tabelle student als
auch in der Tabelle score geführt. Auf den ersten Blick glauben Sie deshalb vielleicht, dass die Auswahlliste entweder student.student_id oder score.student_
id angeben könnte. Das ist jedoch nicht der Fall, weil der eigentliche Grund, dank
dessen wir in der Lage sind, die gewünschten Datensätze zu finden, ist, dass alle
Felder der score-Tabelle als NULL zurückgegeben werden. Die Auswahl von
score.student_id würde nur eine Spalte aus NULL-Werten in der Ausgabe erzeugen. Eine ähnliche Argumentation gilt für die Spalte event_ID, die in beiden
Tabellen geführt wird.
3.9
Ausführung einer UNION-Operation
Wenn Sie eine Ergebnismenge erzeugen wollen, indem Sie Datensätze aus mehreren Tabellen auswählen, die dieselbe Struktur aufweisen, können Sie dazu in einigen Datenbanksystemen eine Art UNION-Anweisung benutzen. MySQL beinhaltet
keine UNION-Anweisung (wenigstens nicht bis MySQL 3.24), aber es gibt verschiedene Möglichkeiten, dasselbe auszudrücken. Hier zwei mögliche Lösungen:
•
Ausführung mehrerer SELECT-Anfragen, eine für jede Tabelle. Das funktioniert, wenn die Reihenfolge der ausgewählten Zeilen keine Rolle spielt.
•
Auswahl von Zeilen aus jeder Tabelle in eine temporäre Tabelle und Auswahl
des Inhalts dieser Tabelle. Auf diese Weise können Sie die Zeilen nach Bedarf
sortieren. In MySQL 3.23 und höher können Sie dieses Problem einfach in den
Griff kriegen, indem Sie dem Server erlauben, diese Behältertabelle für Sie anzulegen. Außerdem können Sie die Tabelle zu einer temporären Tabelle machen, so dass sie automatisch gelöscht wird, nachdem Sie Ihre Sitzung mit dem
Server beendet haben.
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
225
Im folgenden Code löschen wir die Tabelle explizit, so dass der Server die
zugehörigen Ressourcen freigeben kann. Das ist sinnvoll, wenn die Client-Sitzung mit weiteren Anfragen fortgesetzt wird. Außerdem verwenden wir eine
HEAP-Tabelle (im Speicher), um eine bessere Leistung zu erzielen.
CREATE TEMPORARY TABLE hold_tbl TYPE=HEAP SELECT ... FROM table1 WHERE ...
INSERT INTO hold_tbl SELECT ... FROM table2 WHERE ...
INSERT INTO hold_tbl SELECT ... FROM table3 WHERE ...
...
SELECT * FROM hold_tbl ORDER BY ...
DROP TABLE hold_tbl
Für ältere MySQL-Versionen als 3.23 gilt dasselbe Konzept, außer dass Sie
hier die Spalten in der Tabelle hold_tbl explizit selbst deklarieren müssen und
die Anweisung DROP TABLE am Ende zwingend erforderlich ist, damit die
Tabelle nach dem Beenden der Client-Sitzung entfernt wird:
CREATE TABLE hold_tbl (column1 ..., column2 ..., ...)
TYPE=HEAP SELECT ... FROM table1 WHERE ...
INSERT INTO hold_tbl SELECT ... FROM table1 WHERE ...
INSERT INTO hold_tbl SELECT ... FROM table2 WHERE ...
INSERT INTO hold_tbl SELECT ... FROM table3 WHERE ...
SELECT * FROM hold_tbl ORDER BY ...
DROP TABLE hold_tbl
3.9.1
Einfügen einer Spalte für Folgenummern
Wenn Sie mit ALTER TABLE eine AUTO_INCREMENT-Spalte einfügen, wird die Spalte
automatisch mit Folgenummern gefüllt. Die folgenden Anweisungen einer mysqlSitzung zeigen, wie das funktioniert, indem eine Tabelle angelegt, eine Menge
Daten darin abgelegt und dann eine AUTO_INCREMENT-Spalte eingefügt wird:
mysql> CREATE TABLE t (c CHAR(10));
mysql> INSERT INTO t VALUES("a"),("b"),("c");
mysql> SELECT * FROM t;
+------+
| c
|
+------+
| a
|
| b
|
| c
|
+------+
mysql> ALTER TABLE t ADD i INT AUTO_INCREMENT NOT NULL PRIMARY KEY;
mysql> SELECT * FROM t;
+------+---+
| c
| i |
+------+---+
226
Ausführung einer UNION-Operation
| a
| 1 |
| b
| 2 |
| c
| 3 |
+------+---+
3.9.2
Eine existierende Spalte für die Nummerierung benutzen
Wenn Sie eine numerische Spalte haben, können Sie damit Ihre Datensätze nummerieren (oder neu nummerieren, falls sie bereits nummeriert war, Sie aber Zeilen
gelöscht haben und die Werte neu nummerieren wollen, damit sie fortlaufend
sind), etwa so:
ALTER TABLE t MODIFY i INT NULL
UPDATE t SET i = NULL
ALTER TABLE t MODIFY i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
Eine einfachere Methode wäre, die Spalte zu löschen und eine AUTO_INCREMENTSpalte dafür einzufügen. ALTER TABLE erlaubt die Ausführung mehrerer Aktionen,
deshalb kann das alles innerhalb einer einzigen Anweisung passieren:
ALTER TABLE t
DROP i,
ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
3.9.3
Sortieren in außergewöhnlichen Reihenfolgen
Angenommen, Sie haben eine Tabelle, die das Personal eines Sportvereins repräsentieren soll, beispielsweise eine Fußballmannschaft: Sie wollen die Ausgaben
nach der Position der jeweiligen Angestellten sortieren, so dass diese in einer
bestimmten Reihenfolge erscheinen, etwa: Trainer, Co-Trainer, Tormänner, Feldspieler, Ersatzspieler usw. Definieren Sie die Spalte als ENUM und führen Sie die
Auflistungselemente in der Reihenfolge an, in der Sie sie später anzeigen wollen.
Beim Sortieren dieser Spalte wird automatisch die hier vorgegebene Reihenfolge
berücksichtigt.
3.9.4
Einrichten einer Zählertabelle
In Kapitel 2 haben wir im Abschnitt »Folgen« bereits gezeigt, wie man mit LAST_
INSERT_ID(ausdruck) eine Folge erzeugt. Das dortige Beispiel hat gezeigt, wie
man einen Zähler mit Hilfe einer Tabelle mit einer einzigen Zeile einrichtet. Das
ist für einen einzigen Zähler kein Problem, aber wenn Sie mehrere Zähler brauchen, führt diese Methode zu einer unnötig großen Anzahl von Tabellen. Angenommen, Sie haben eine Website und wollen auf mehreren Seiten den Zähler
»Diese Seite hatte bereits nnn Besucher« anzeigen. Bestimmt wollen Sie nicht für
jede Seite mit einem Zähler eine separate Zählertabelle einrichten.
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
227
Eine Lösungsmöglichkeit wäre, eine einzige Tabelle mit zwei Spalten einzurichten. Eine Spalte nimmt einen Zählerwert auf; die andere einen Zählernamen. Wir
können die Funktion LAST_INSERT_ID() verwenden, aber anhand des Zählernamens stellen wir fest, auf welche Zeile sie sich bezieht. Die Tabelle sieht wie folgt
aus:
CREATE TABLE counter
(
count INT UNSIGNED,
name varchar(255) NOT NULL PRIMARY KEY
)
Der Name ist eine Zeichenkette, wir können einem Zähler also einen beliebigen
Namen zuweisen und ihn zu einem PRIMARY KEY machen, um doppelte Namen zu
vermeiden. Damit wird vorausgesetzt, dass die Anwendungen, die die Tabelle
verwenden, sich auf die verwendeten Namen einigen. Für unsere Web-Zähler
können wir die Eindeutigkeit der Zählernamen sicherstellen, indem wir einfach
die Pfadnamen der betreffenden Seite im Dokumentbaum als Zählernamen verwenden. Um beispielsweise einen neuen Zähler für die Homepage der Site einzurichten, gehen Sie wie folgt vor:
INSERT INTO counter (name) VALUES("index.html")
Damit wird der Zähler »index.html« mit einem Wert von Null initialisiert. Um
den nächsten Folgewert zu erzeugen, inkrementieren Sie den Zähler in der entsprechenden Zeile der Tabelle und laden ihn mit LAST_INSERT_ID():
UPDATE counter
SET count = LAST_INSERT_ID(count+1)
WHERE name = "index.html"
SELECT LAST_INSERT_ID()
Eine Alternative wäre, den Zähler ohne LAST_INSERT_ID() zu inkrementieren,
etwa wie folgt:
UPDATE counter SET count = count+1 WHERE name = "index.html"
SELECT count FROM counter WHERE name = "index.html"
Das funktioniert jedoch nicht, wenn ein anderer Client den Zähler inkrementiert,
nachdem Sie das UPDATE und bevor Sie das SELECT ausgeführt haben. Dieses Problem könnten Sie mit der Anwendung von LOCK TABLES und UNLOCK TABLES um
die beiden Anweisungen löschen, um andere Clients auszuschließen, während Sie
den Zähler verwenden. Mit der LAST_INSERT_ID()-Methode ist das Ganze jedoch
einfacher zu bewerkstelligen. Weil ihr Wert client-spezifisch ist, erhalten Sie
immer den Wert, den Sie eingetragen haben, nicht einen Wert irgendeines anderen
Clients. Und Sie müssen den Code nicht verkomplizieren, indem Sie Sperren setzen, die andere Clients blockieren.
228
3.9.5
Funktionen, die MySQL nicht unterstützt
Prüfen, ob eine Tabelle existiert
Manchmal ist es wichtig, innerhalb einer Anwendung zu erkennen, ob eine bestimmte Tabelle existiert. Dazu verwenden Sie eine der folgenden Anweisungen:
SELECT COUNT(*) FROM tbl_name
SELECT * FROM tbl_name WHERE 1=0
Diese Anweisungen sind erfolgreich, falls die Tabelle existiert, andernfalls schlagen sie fehl. Die Anfragen sind für diesen Test gut geeignet. Sie werden sehr
schnell ausgeführt, so dass Sie damit nicht viel Zeit verlieren. Diese Vorgehensweise ist insbesondere für Anwendungsprogramme praktisch, die Sie selbst
schreiben, weil Sie prüfen können, ob die Anfrage erfolgreich war und die entsprechenden Maßnahmen ergreifen können. Weniger sinnvoll dagegen ist sie bei
Verwendung eines Batch-Scripts, das Sie von mysql aus ausführen, weil Sie nichts
tun können, falls ein Fehler auftritt, außer das Programm zu beenden (oder den
Fehler zu ignorieren, was aber nicht Sinn der Anfrage gewesen sein kann).
3.10
Funktionen, die MySQL nicht unterstützt
Dieser Abschnitt beschreibt einige Funktionen, die es in anderen Datenbanksystemen gibt, die in MySQL aber nicht unterstützt werden. Hier wird gezeigt, was
fehlt, und welche Lösungsmöglichkeiten es dafür gibt. Im Allgemeinen fehlen
Funktionen in MySQL, wenn sie eine negative Auswirkung auf die Leistung
haben. Mehrere Punkte auf der Liste sind jedoch von den Entwicklern bereits
geplant und werden vermutlich realisiert, sobald es die Zeit erlaubt und man
damit keine wesentlichen Leistungseinbußen verursacht.
•
Subselects. Ein Subselect ist ein in ein anderes SELECT eingebettetes SELECT,
wie beispielsweise in der folgenden Anfrage gezeigt:
SELECT * FROM score
WHERE event_id IN (SELECT event_id FROM event WHERE type = "T")
Subselects sind für MySQL 3.24 angekündigt. Bis dahin können viele Anfragen, die ein Subselect benötigen, auch mit Hilfe von Joins realisiert werden.
Weitere Informationen finden Sie weiter oben im Abschnitt »Subselects als
Joins formulieren«.
•
Transaktionen und Commit/Rollback. Eine Transaktion ist eine Folge von
SQL-Anweisungen, die als Einheit ausgeführt werden, ohne dass andere Clients sie unterbrechen können. Mit Hilfe der Commit/Rollback-Fähigkeit stellen Sie sicher, dass die Anweisungen komplett als Einheit oder überhaupt nicht
ausgeführt werden. Schlägt also eine Anweisung in der Transaktion fehl, werden alle bis zu diesem Zeitpunkt ausgeführten Anweisungen der Einheit rückgängig gemacht.
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
229
MySQL setzt automatisch Sperren für einzelne SQL-Anweisungen, um zu
verhindern, dass die Clients sich gegenseitig stören. (Beispielsweise können
zwei Clients nicht gleichzeitig in dieselbe Tabelle schreiben.) Darüber hinaus
können Sie mit LOCK TABLES und UNLOCK TABLES Anweisungen zu einer einzigen Einheit zusammenfassen, so dass Sie Operationen ausführen können, für
die die Nebenläufigkeitssteuerung einzelner Anweisungen nicht ausreichend
ist. Problematisch dabei ist, dass MySQL die Anweisungen nicht automatisch
für Sie gruppiert und Sie die Anweisungen nicht rückgängig machen können,
falls eine davon fehlschlägt.
Um die Bedeutung von Transaktionen zu verstehen, stellen Sie sich vor, Sie
hätten ein Bekleidungsgeschäft und aktualisierten die Bestände, wenn einer
Ihrer Verkäufer etwas verkauft. Das folgende Beispiel demonstriert die Problematik, die auftreten kann, wenn mehrere Verkäufer die Datenbank gleichzeitig aktualisieren (wobei vorausgesetzt wird, dass es einen Anfangsbestand
von 47 Hemden gibt):
t1
Verkäufer 1 verkauft 3 Hemden
t2
Verkäufer 1 ermittelt den aktuellen Hemdenstand (47)
SELECT menge FROM bestand WHERE artikel = "hemd"
t3
Verkäufer 2 verkauft 2 Hemden
t4
Verkäufer 2 ermittelt den aktuellen Hemdenstand (47)
t5
Verkäufer 1 berechnet den neuen Lagerbestand als 47-3=44 und setzt den
Hemdenzähler auf 44:
UPDATE bestand SET menge = 44 WHERE artikel = "hemd"
t6
Verkäufer 2 berechnet den neuen Lagerbestand als 47-2=45 und setzt den
Hemdenzähler auf 45:
UPDATE bestand SET menge = 45 WHERE artikel = "hemd"
Nach Ablauf dieser Ereignisse haben Sie fünf Hemden verkauft (was nicht
schlecht ist), aber der Lagerbestand steht auf 45 statt auf 42 (was schlecht ist).
Das Problem dabei ist, dass Sie für das Nachlesen des Lagerbestands in einer
Anweisung und die Aktualisierung des Werts in einer zweiten Anweisung
eine Transaktion für mehrere Anweisungen brauchen. Die Aktion in der zweiten Anweisung ist von dem in der ersten Anweisung ermittelten Wert abhängig. Wenn aber während überlappender Zeitrahmen separate Transaktionen
auftreten, können sich die Anweisungen der verschiedenen Transaktionen vermischen und sich gegenseitig beeinflussen. In einer transaktionsgesteuerten
Datenbank können die Anweisungen der verschiedenen Verkäufer als Transaktionen ausgeführt werden, und die Anweisungen von Verkäufer 2 werden
erst ausgeführt, nachdem die Anweisungen von Verkäufer 1 vollständig abgeschlossen sind. In MySQL gibt es zwei Möglichkeiten, diesen Sachverhalt zu
realisieren:
230
Funktionen, die MySQL nicht unterstützt
•
Lösungsansatz 1: Ausführung einer Gruppe von Anweisungen als Einheit.
Sie können Anweisungen zusammenfassen und sie als atomare Einheit ausführen, indem Sie sie zwischen LOCK TABLES und UNLOCK TABLES schreiben: Sperren
Sie alle betroffenen Tabellen, führen Sie Ihre Anfragen aus und heben Sie die
Sperren auf. Damit wird verhindert, dass jemand die Tabellen benutzt, während
Sie die Sperre dafür halten. Mit dieser Tabellensperre sieht die Situation wie folgt
aus:
t1
Verkäufer 1 verkauft 3 Hemden
t2
Verkäufer 1 fordert eine Sperre an und ermittelt den aktuellen Hemdenstand
(47)
LOCK TABLES bestand WRITE
SELECT menge FROM bestand WHERE artikel = "hemd"
t3
Verkäufer 2 verkauft 2 Hemden
t4
Verkäufer 2 versucht, eine Sperre anzufordern; das wird zurückgewiesen,
weil Verkäufer 1 bereits eine Sperre hält:
LOCK TABLES bestand WRITE
t5
Verkäufer 1 berechnet den neuen Lagerbestand als 47-3=44 und setzt den
Hemdenzähler auf 44:
UPDATE bestand SET menge = 44 WHERE artikel = "hemd"
UNLOCK TABLES
t6
Jetzt kann Verkäufer 2 eine Sperre anfordern. Er ermittelt den neuen Lagerbestand (44):
SELECT menge FROM bestand WHERE artikel = "hemd"
Verkäufer 2 berechnet den neuen Lagerbestand als 47-2=45 und setzt den
Hemdenzähler auf 45:
UPDATE bestand SET menge = 45 WHERE artikel = "hemd"
t7
Verkäufer 2 berechnet den neuen Lagerbestand als 44-2=42, setzt den Hemdenzähler auf 42 und gibt die Sperre frei:
UPDATE bestand SET menge = 42 WHERE artikel = "hemd"
UNLOCK TABLES
Auf diese Weise ist sichergestellt, dass die beiden Transaktionen nicht vermischt werden und der Lagerbestand korrekt gesetzt wird. Wir verwenden hier
eine Schreibsperre (WRITE), weil wir die Tabelle bestand ändern wollen. Wenn
Sie die Tabellen nur lesen, können Sie statt dessen auch eine Lesesperre (READ)
setzen. Bei einer Lesesperre können die anderen Clients die Tabellen lesen,
während Sie diese benutzen, aber sie können nichts hineinschreiben.
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
231
In dem oben gezeigten Beispiel würde Verkäufer 2 vermutlich keinen Unterschied hinsichtlich der Geschwindigkeit bemerken, weil Transaktionen kurz
sind und schnell ausgeführt werden. Sie sollten immer vermeiden, Tabellen
längere Zeit zu sperren.
Wenn Sie mehrere Tabellen verwenden, müssen Sie alle sperren, damit Sie
gruppierte Anfragen ausführen können. Wenn Sie nur aus einer bestimmten
Tabelle lesen, müssen Sie lediglich eine einzelne Lesesperre setzen. Angenommen, Sie haben mehrere Anfragen, die Änderungen an der Tabelle
bestand vornehmen sollen, und Sie wollen außerdem Daten aus der Tabelle
kunden lesen. In diesem Fall setzen Sie für die Tabelle bestand eine Schreibsperre und eine Lesesperre für die Tabelle kunden:
LOCK TABLES bestand WRITE, kunden READ
...
UNLOCK TABLES
Dafür ist es erforderlich, die Tabellen selbst zu sperren und die Sperre auch
wieder aufzuheben. Ein Datenbanksystem mit Transaktionsunterstützung
würde das automatisch für Sie erledigen. Die Gruppierung von Anweisungen
zur Ausführung als Einheit ist jedoch in transaktionsorientierten Datenbanken
auf dieselbe Weise zu planen.
•
Lösungsansatz 2: Verwenden relativer statt absoluter Aktualisierungen.
Die zweite Möglichkeit, wie Sie verhindern, dass sich Anweisungen aus mehreren Transaktionen vermischen, besteht darin, die Abhängigkeit zwischen den
Anweisungen aufzuheben. Das ist zwar nicht immer möglich, aber in unserem
hier gezeigten Beispiel funktioniert es. Für die in Lösungsansatz 1 verwendete
Aktualisierung hat die Transaktion die aktuelle Bestandsebene gesperrt, den neuen Wert abhängig von den verkauften Hemden berechnet und dann den Bestand
mit dem neuen Wert aktualisiert. Das kann innerhalb eines Schritts erfolgen, indem der Hemdenzähler einfach relativ zum aktuellen Wert aktualisiert wird:
t1
Verkäufer 1 verkauft drei Hemden
t2
Verkäufer 1 verringert den Hemdenzähler um drei:
t3
Verkäufer 2 verkauft zwei Hemden
t4
Verkäufer 2 verringert den Hemdenzähler um zwei:
UPDATE bestand SET menge = menge – 3 WHERE artikel = "hemd"
UPDATE bestand SET menge = menge – 2 WHERE artikel = "hemd"
Wie Sie sehen, braucht man überhaupt keine Transaktionen über mehrere
Anweisungen und muss damit auch keine Tabellen sperren, um Transaktionsfähigkeit zu simulieren. Falls Sie hauptsächlich Transaktionen dieser Form
ausführen, können Sie möglicherweise auch ganz ohne Transaktionen auskommen.
232
Funktionen, die MySQL nicht unterstützt
Das obige Beispiel zeigt, wie Sie in bestimmten Situationen ohne Transaktionen auskommen. Das heißt natürlich nicht, dass es keine Situationen gibt, in
denen Transaktionen erforderlich werden. Das typische Beispiel dafür ist eine
Geldüberweisung, im Zuge derer Geld von einem Konto auf ein anderes
Konto gebucht wird. Angenommen, Bill schreibt einen Scheck für Bob über
100 DM und Bob reicht den Scheck ein. Das Konto von Bill wird mit 100 DM
belastet, und dem Konto von Bob werden 100 DM gutgeschrieben.
UPDATE account SET balance = balance – 100 WHERE name = "Bill"
UPDATE account SET balance = balance + 100 WHERE name = "Bob"
Tritt zwischen der Ausführung der beiden Anweisungen ein Absturz auf, ist
die Transaktion unvollständig. Ein Datenbanksystem mit echten Transaktionen und Commit/Rollback-Fähigkeit könnte diese Situation kompensieren.
(Zumindest theoretisch. Auch hier müssten Sie herausfinden, welche Transaktionen eingegeben wurden, und sie wiederholen, aber zumindest müssten Sie
sich keine Gedanken über unvollständig ausgeführte Transaktionen machen.)
In MySQL ermitteln Sie den Status von Transaktionen bei Systemausfällen
anhand des Ereignisprotokolls.
•
Fremdschlüssel und referentielle Integrität. Mit Hilfe eines Fremdschlüssels legen Sie fest, dass ein Schlüssel in einer Tabelle in einer bestimmten Beziehung zu einem Schlüssel in einer anderen Tabelle steht. Die referentielle
Integrität erlaubt Ihnen, Einschränkungen festzulegen, was mit der Tabelle mit
dem Fremdschlüssel gemacht werden darf. Beispielsweise enthält die scoreTabelle in unserer Beispieldatenbank samp_db eine Spalte student_id, die wir
nutzen, um die Punktwertdatensätze mit den Schülern in der student-Tabelle
in eine Beziehung zu setzen. student_id würde in Datenbanken, die dieses
Konzept unterstützen, als Fremdschlüssel deklariert, und wir würden eine Beschränkung dafür festlegen, in der Form, dass kein Punktewertdatensatz für einen Schüler eingetragen werden kann, der in der student-Tabelle nicht
existiert. Darüber hinaus könnten wir ein kaskadenförmiges Löschen erlauben,
d.h., wenn ein Schüler aus der student-Tabelle gelöscht wird, werden auch alle
Punktwertdatensätze für diesen Schüler automatisch aus der score-Tabelle
gelöscht.
Mit Hilfe von Fremdschlüsseln gewährleisten Sie die Konsistenz Ihrer Daten.
Außerdem bieten Fremdschlüssel eine gewisse Bequemlichkeit. Der Grund
dafür, warum sie in MySQL nicht unterstützt werden, sind die negativen Auswirkungen auf die Leistung und die Wartungsfreundlichkeit der Datenbank.
(Im MySQL Reference Manual finden Sie genauere Informationen zu dieser
Begründung.) Beachten Sie, dass sich diese Betrachtung der Fremdschlüssel
von der in anderer Datenbankliteratur unterscheidet, wo sie häufig als »unabdingbar« bezeichnet werden. Die Entwickler von MySQL teilen diese Ansicht
nicht. Wenn Sie anders denken, sollten Sie sich besser für eine andere Datenbank entscheiden, in der Fremdschlüssel unterstützt werden. Haben Ihre
Kapitel 3 • Syntax und Verwendung von SQL in MySQL
233
Daten beispielsweise sehr komplexe Beziehungen, dann wollen Sie diese
Abhängigkeiten sicher nicht unbedingt selbst in Ihren Anwendungen implementieren (auch wenn dazu kaum mehr erforderlich ist als einige zusätzliche
DELETE-Anweisungen).
MySQL unterstützt keine Fremdschlüssel, parst aber FOREIGN KEY-Klauseln in
CREATE TABLE-Anweisungen. (Damit wird es einfacher, SQL-Code von anderen Datenbanken in MySQL zu portieren.) MySQL erzwingt keine Fremdschlüssel und unterstützt auch kein kaskadenförmiges Löschen.
Die Einschränkungen, die durch die Fremdschlüssel erzwungen werden sollen, sind größtenteils auch ganz einfach durch die Anwendungslogik nachzuvollziehen. Manchmal ist es einfach eine Frage der Dateneingabe. Wenn Sie
beispielsweise neue Datensätze in unsere score-Tabelle eingeben, werden Sie
sehr wahrscheinlich keine Werte für nicht existente Schüler eintragen. Um
Punkte einzutragen, würden Sie also von einer Liste der Schüler aus der student-Tabelle ausgehen, für jeden den Punktewert ermitteln und unter Verwendung der Schüler-IDs einen neuen Datensatz in die score-Tabelle eintragen.
Auf diese Weise besteht keine Gefahr, dass ein Datensatz für einen nicht existierenden Schüler angelegt wird. Sie würden ja nicht einfach einen Punktedatensatz erfinden, um ihn in die score-Tabelle einzutragen.
Um kaskadenförmiges Löschen zu ermöglichen, brauchen Sie auch eine
eigene Anwendungslogik. Angenommen, sie wollen den Schüler mit der
Nummer 13 löschen. Damit sollen auch alle Punktedatensätze für diesen
Schüler entfernt werden. In einer Datenbank, die kaskadenförmiges Löschen
unterstützt, würden Sie den Datensatz in der student-Tabelle sowie alle zugehörigen Datensätze in der scores-Tabelle mit der folgenden Anweisung
löschen:
DELETE FROM student WHERE student_id = 13
Die Datensätze für Schüler 13 in der score-Tabelle werden automatisch
gelöscht. In MySQL nehmen Sie dieses sekundäre Löschen mit einer expliziten DELETE-Anweisung vor:
DELETE FROM student WHERE student_id = 13
DELETE FROM score WHERE student_id = 13
•
Gespeicherte Prozeduren und Trigger. Eine gespeicherte Prozedur ist SQLCode, der kompiliert und im Server gespeichert wird. Später kann man darauf
zugreifen, ohne dass er erneut vom Client übertragen und geparst werden
müsste. Außerdem ist es möglich, Änderungen an einer Prozedur vorzunehmen, um damit die Client-Anwendungen entsprechend zu beeinflussen. Trigger erlauben, eine gespeicherte Prozedur zu aktivieren, wenn ein bestimmtes
Ereignis auftritt (beispielsweise wenn ein Datensatz aus einer Tabelle gelöscht
wird). Das könnte sinnvoll sein, wenn Sie nach einem Löschvorgang einen
234
Funktionen, die MySQL nicht unterstützt
komplexen Überblick neu anlegen wollen, in dem der Datensatz zuvor enthalten war. Die Einführung gespeicherter Prozeduren ist für MySQL geplant.
•
Sichten. Eine Sicht ist eine logische Einheit, die sich wie eine Tabelle verhält,
aber keine Tabelle ist. Sie bietet eine Möglichkeit, Spalten aus anderen Tabellen so anzuzeigen, als wären sie alle in derselben Tabelle enthalten. Sichten
werden manchmal auch als virtuelle Tabellen bezeichnet. Sie sind für MySQL
vorgesehen.
•
Berechtigungen und Sperren auf Datensatzebene. MySQL unterstützt unterschiedliche Berechtigungsebenen, von globalen bis hin zu Berechtigungen
für einzelne Datenbanken, Tabellen und Spalten. Berechtigungen auf Datensatzebene sind nicht möglich. Sie können jedoch in Ihren Anwendungen mit
Hilfe der Funktionen GET_LOCK()und RELEASE_LOCK() kooperative Datensatzsperren implementieren. Weitere Informationen finden Sie im Abschnitt über
GET_LOCK() in Anhang C.
•
-- als Kommentar. Dieser Kommentarstil wird nicht unterstützt, weil es sich
dabei um ein mehrdeutiges Konstrukt handelt. In MySQL 3.23.2 wird ein
Kommentar, der mit zwei Trennstrichen beginnt, jedoch akzeptiert. Weitere
Informationen dazu finden Sie weiter oben im Abschnitt »Kommentare«.