MySQL besteht aus zwei Bestandteilen: Einem MySQL-Server, der
auf dem DM-Webserver installiert ist, und einem MySQL-Client.
Der Server stellt die Datenbanken bereit, deren Daten mit dem
Client ausgelesen und geändert werden. Der MySQL-Client kann
entweder ebenfalls auf einem Server-Rechner installiert sein
oder auf dem Rechner des Benutzers. Die Installation auf einem
Server-Rechner ist die Standardlösung der Webprovider, da sie
die Benutzung der Datenbanken ohne vorherige Installation von
Software auf dem Rechner des Benutzers ermöglicht. Ebenfalls
fällt keine Freischaltung des MySQL-Ports durch eine i.d.R.
vorhandene Firewall an. Jeder freigegebene Port bedeutet ein
weiteres potentielles Einfallstor für Angreifer.
Hinsichtlich der Client-Programme kann man reine
Anwendungsprogramme, die in einer üblichen Programmiersprache,
wie z. B. C++, implementiert wurden und wie jedes andere
Programm ausgeführt werden, von Anwendungen unterscheiden, die
in einer Script-Sprache implementiert wurden, und von einem
entsprechenden Interpreter ausgeführt werden. Ein bekannter
Vertreter der letztgenannten Lösung ist z. B. phpMyAdmin,
eine in PHP programmierte Webanwendung, die auf dem Server des
Service-Providers installiert wird. Dies erfolgt häufig sogar
auf demselben Rechner, auf dem auch der MySQL-Server läuft.
phpMyAdmin wird auf der Anwenderseite über einen Webbrowser
aufgerufen und dann über eine grafische Benutzeroberfläche
bedient. Hierin unterscheidet sich phpMyAdmin z. B. vom
originalen, rein textbasierten MySQL-Client mysql.exe,
der als Konsolenprogramm entweder auf dem Server oder dem
Rechner des Anwenders ausgeführt wird und der nur die Eingabe
textueller SQL-Befehle gestattet.
Im Rahmen dieses Übungsblattes werden wir phpMyAdmin einsetzen,
wobei wir die MySQL-Befehle per Hand eingeben werden, da dies
einen Einblick in die Steuerung eines
Datenbankverwaltungssystems mittels Befehlen gestattet. Auf
diese Weise simulieren wir die Situation, in der sich ein
Programmierer befindet, der aus einer Script-Sprache heraus
Anfragen an das Datenbankverwaltungssystem schickt. Dies werden
wir dann im Zuge des nächsten Übungsblattes ebenfalls tun.
Es erscheint ein Anmeldedialog, in dem Sie den Namen Ihres
Accounts (z. B. dmuserx) und das zugehörende
Passwort eingeben müssen.
Sobald Sie das Formular abschicken, wird das Begrüßungsfenster
von phpMyAdmin geladen, dass in etwa wie in Abbildung 1 aussehen
sollte.
Abb.1:
Begrüßungsfenster
Das Erscheinen dieses Fensters sagt Ihnen, dass Sie jetzt
erfolgreich eine Verbindung zum MySQL-Server aufgebaut haben und
Kommandos eingeben können.
Aufgabe 2: Eingabe von Kommandos
In diesem Abschnitt werden wir zunächst die Grundlagen des
Eingebens von Anfragen an das Datenbankverwaltungssystem (DBVS)
erarbeiten.
Links oben unter Home werden Ihnen die unter Ihrem
Account verfügbaren Datenbanken angezeigt. Es existiert bereits
eine Datenbank, die den Namen Ihres Accounts trägt: dmuserx
(ersetzen Sie bitte das x gedanklich durch die Nummer
ihrer Benutzergruppe). Wenn Sie versuchen, die Datenbank durch
Drücken auf das +-Zeichen zu öffnen, so wechselt dieses
lediglich zu einem Minuszeichen.
Dies bedeutet, dass es sich um eine leere Datenbank handelt, die
bisher keine Datentabellen enthält. Wenn Sie auf den Link
des Datenbanknamens
klicken, wird die Datenbank ausgewählt und Sie können mit ihr
arbeiten. Daraufhin erscheint ein neues Fenster (Abb. 2).
Abb.2:
Neue Tabelle anlegen
In diesem wird noch einmal explizit darauf hingewiesen, dass
bisher keine Tabellen in der Datenbank existieren. Klicken Sie
jetzt auf SQL.
Ein weiteres Fenster erscheint, in dem Sie SQL-Befehle eingeben
und durch Drücken der OK-Taste an den Datenbankserver schicken
können (Abb. 3).
Abb.3 :
SQL-Befehl eingeben
Geben Sie im Textfeld die folgenden beiden SQL-Kommandos ein,
um die MySQL-Version und das aktuelle Datum auszulesen:
select version(), current_date
Schicken Sie den Befehl mit OK ab. Danach erhalten Sie die
Ausgabe in Abbildung 4, mit entsprechend angepasster
Versionsnummer und aktuellem Datum.
Abb.4:
Ergebnis des Befehls
Diese Abfrage lässt einige Rückschlüsse hinsichtlich der
Eingabe und Verarbeitung von Kommandos durch MySQL zu:
Im DBVS existiert ein Interpreter für die Verarbeitung von
in SQL formulierten Befehlen. SQL ist also eine
interpretierte Sprache.
Wenn ein Kommando eingegeben wird, schickt phpMyAdmin
dieses zur Bearbeitung an den MySQL-Server: das DBVS. Das
Ergebnis des Kommandos wird nachfolgend vom Client wieder
entgegengenommen und angezeigt.
Da MySQL ein relationales Datenbanksystem ist, werden die
Ergebnisse in Form einer Tabelle angezeigt, bestehend aus
Zeilen und Spalten. Die erste Zeile enthält dabei in
Fettdruck die Namen der Spalten ("version( )
current_date"). Bei Zugriff auf eine Datenbank
entsprechen diese Namen den Spaltennamen der Tabelle, aus
der die Information ausgelesen wurde.
Über diese Informationen hinausgehend wird ebenfalls
angezeigt, wie viele Zeilen aus der Datenbank ausgelesen
wurden und wieviel Zeit dieser Vorgang erforderte: 1
insgesamt, die Abfrage dauerte 0.0003 sek.
SQL-Kommandos berücksichtigen keine Groß- und Kleinschreibung,
d. h. die folgenden Kommandos sind äquivalent:
Dies gilt allerdings nur für die direkte Kommunikation mit dem
DBVS. Beim Zusammenspiel mit PHP ist Groß- und Kleinschreibung
häufig zu berücksichtigen, z. B. bezüglich der Namen von
Tabellenspalten. Dies werden wir später noch sehen.
Das folgende Kommando demonstriert, dass nicht alle Befehle in
MySQL zwangsläufig etwas mit der Bearbeitung von Datenbanken zu
tun haben müssen. MySQL kann beispielsweise ebenfalls als
einfacher Taschenrechner benutzt werden. Bevor Sie den folgenden
Befehl eingeben können, müssen Sie wieder SQL drücken,
um zum Eingabefenster zurückzugelangen.
Um Platz zu sparen und die Datenein- und -ausgabe
übersichtlicher zu gestalten, werden im weiteren Verlauf dieses
Textes die SQL-Befehle durch mysql>
ausgezeichnet. Diese Darstellung entspricht dem Kommandoprompt
des Konsolenprogramms mysql.exe. Das Ergebnis der
jeweiligen Abfrage erscheint darunter in einer textuell
dargestellten Tabelle. Im Gegensatz zum Konsolenprogramm, ist es
in phpMyAdmin nicht erforderlich, Befehle mit Semikolon
abzuschließen, es stört aber auch nicht. Den Kommandoprompt mysql>
müssen sie jeweils nicht mit eintippen.
Nach diesen einleitenden Übungen kommen wir nun zu unserem
eigentlichen Thema: Dem Ablegen von Daten im Datenbanksystem und
deren Abfrage. Im MySQL-Client können Sie sich die Ihnen
zugeordneten Datenbanken auch explizit mit dem Befehl
SHOW DATABASES;
anzeigen lassen. Die Abfrage ergibt wieder dmuserx.
In allen folgenden Übungen werden Sie mit dieser Datenbank
arbeiten. Neue Datenbanken lassen sich relativ leicht mit dem
Befehl
CREATE DATABASE database_name;
erzeugen und mit dem Befehl
DROP DATABASE database_name;
löschen. Dies erfordert indes erweiterte Benutzerrechte, über
die Sie nicht verfügen. Das Erzeugen weiterer Datenbanken wird
im Rahmen dieser Veranstaltung allerdings auch nicht
erforderlich sein.
Um mit der Datenbank dmuserx arbeiten zu können,
müssten Sie MySQL in einem textbasierten Client, wie mysql.exe,
zunächst mit dem Befehl USE mitteilen, dass Sie
diese benutzen wollen:
mysql> USE dmuserx;
Database changed
Hier ist dies indes nicht erforderlich, da dies von phpMyAdmin
implizit erledigt wurde, als Sie zu Beginn auf den
Datenbanknamen dmuserx klickten.
Durch Eingabe des Befehls
SHOW TABLES
ist es möglich, die vorhandenen Datentabellen auflisten zu
lassen. Auch dies wurde schon implizit von phpMyAdmin erledigt
und führte zu der Meldung "Es wurden keine Tabellen in der
Datenbank gefunden." Durch Eingabe des Befehls können
Sie
die entsprechende Meldung MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze). (Die Abfrage dauerte 0,0003 Sekunden.)
auslösen.
Im weiteren Verlauf der Übungen werden Sie ihre Datenbank mit
Tabellen füllen. Nehmen Sie hierzu an, sie würden ein Webportal
betreiben, welches Sie um einen Buchversand erweitern wollen.
Hierzu müssen Daten über Bücher, wie Autor, Titel etc. in der
Datenbank erfasst werden. Zu diesem Zweck erzeugen Sie zunächst
eine Tabelle "Buch".
Die allgemeine Form des SQL-Befehls zum Erzeugen einer Tabelle
sieht wie folgt aus:
Innerhalb einer Tabelle müssen die Spaltennamen eindeutig sein,
d. h. keine zwei Spalten dürfen denselben Namen besitzen. In den
Spalten der Tabelle werden unterschiedliche Informationen über
den erfassten Gegenstand, hier: Bücher, kodiert. In unserem Fall
z. B. der Buchtitel, der Name des Autors, das Erscheinungsjahr,
der Preis, der Verlag und ggf. die ISBN-Nummer.
Diese Informationen müssen in Form geeigneter Datentypen
kodiert werden. Wie sie im Abschnitt 6.2
Column Types des MySQL-Manuals nachlesen können, besitzt
MySQL eine Vielzahl verschiedener Datentypen.
Der Titel des Buchs und der Name des Autors sollten
zweckmäßigerweise als Zeichenketten kodiert werden. Hierfür
bieten sich zwei Datentypen an: CHAR(m) und VARCHAR(m).
m gibt dabei die Länge der in die zugehörende
Spalte einzufügenden Zeichenketten an. m kann
irgendeinen Wert zwischen 1 und 255 annehmen. Der wesentliche
Unterschied zwischen CHAR und VARCHAR besteht darin, dass die
Länge m bei CHAR eingehalten werden muss, während
sie bei VARCHAR variabel ist. Hier definiert m
also nur die maximal erlaubte Länge der Zeichenketten. VARCHAR
kodiert Zeichenketten effizienter als CHAR, da nur die
tatsächlich benutzten Zeichen in die Datenbank geschrieben
werden, plus ein Byte, welches die Anzahl der Zeichen kodiert.
CHAR hingegen schreibt immer m Zeichen in die
entsprechende Zeile der Tabelle, unabhängig davon, wieviele
hiervon tatsächlich für den entsprechenden Eintrag benutzt
wurden.
Wir kodieren den Namen des Autors, den Titel und den Verlag als
VARCHAR(100), d. h. als Zeichenketten der maximalen
Länge 100 Zeichen.
Für das Erscheinungsjahr bietet sich der Typ YEAR
als vierstellige natürliche Zahl an. Den Preis kodieren wir als
DECIMAL(7,2) UNSIGNED. Dies bedeutet, dass für den
Preis eine positive Zahl mit maximal 5 Stellen vor dem Komma und
zwei Stellen nach dem Komma erlaubt ist.
Um Datensätze eindeutig identifizieren zu können, benötigen wir
noch einen Primärschlüssel. Der Name des Autors scheidet in
diesem Zusammenhang aus, da ein Autor mehrere Bücher geschrieben
haben kann. Ein zunächst als geeignet erscheinender Kandidat ist
die ISBN-Nummer. Leider ist auch diese nicht eindeutig, da
oftmals mehrbändige Werke eine einzelne ISBN-Nummer besitzen.
Ebenfalls tragen Lizenzausgaben, z. B. von Buchclubs, häufig
keine ISBN-Nummer. Eine einfache Lösung dieses Problems besteht
darin, eine weitere Spalte einzuführen, die jedem Datensatz eine
eindeutige Nummer zuordnet. Die Vergabe der Nummer bzw. des
künstlichen Schlüssels wird von MySQL automatisch erledigt, wenn
wir dieser Spalte den Datentyp INT UNSIGNED mit
der Option AUTO_INCREMENT zuweisen. Um MySQL
mitzuteilen, dass es sich bei dieser Spalte um den
Primärschlüssel handelt, muss diese noch die Option PRIMARY
KEY zugewiesen bekommen. Schließlich müssen wir noch
sicherstellen, dass für den Primärschlüssel nicht NULL
eingetragen werden darf. NULL ist ein spezieller
Wert und steht für einen fehlenden oder unbekannten Eintrag.
Bücher mit dem Schlüssel NULL wären nicht
eindeutig identifizierbar, was Fehlfunktionen von
Programmscripten zur Folge hätte. Durch Zuweisen der Option NOT
NULL wird der Gebrauch des speziellen Werts NULL
für diese Spalte verboten.
NOT NULL kann auf beliebige Spalten angewendet
werden und bedeutet nicht, dass diese dann in jedem Fall
ausgefüllt werden müssen. Als NOT NULL
ausgezeichnete Spalten bekommen lediglich von MySQL beim
Hinzufügen eines Datensatzes, der keinen Wert für diese Spalte
aufweist, einen DEFAULT-Wert zugewiesen, sofern bei der
Erstellung der Tabelle einer definiert wurde. Wurde kein
Default-Wert angegeben, so wird bei numerischen Datentypen
automatisch der Wert 0 oder bei Zeichenketten der leere String
eingetragen. Als NULL ausgezeichnete Spalten
erhalten hingegen den DEFAULT-Wert NULL
eingetragen.
Hinsichtlich dieser Übung wollen wir es bei den bisher
definierten Spalten belassen. Sollten wir bei der Spezifikation
der Tabelle Design-Fehler gemacht haben, z. B. eine als zu klein
deklarierte Länge einer Zeichenkette, so können diese später mit
dem Befehl ALTER TABLE behoben werden.
Nachdem wir nun die einzelnen Spalten der Tabelle mit Namen und
Datentyp spezifiziert haben, können wir die Tabelle erzeugen:
mysql> CREATE TABLE Buch (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Autor VARCHAR(100),
Titel VARCHAR(100),
Verlag VARCHAR(100),
Erscheinungsjahr YEAR,
Preis DECIMAL(7,2)
);
Query OK, 0 rows affected (0.11 sec)
Das SHOW TABLES Kommando liefert nun:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_dmuserx |
+---------------------+
| buch |
+---------------------+
1 row in set (0.00 sec)
Die Tabelle "Buch" existiert also jetzt. Wenn Sie nun auf der
linken Seite des Clientfensters unter Home nachschauen,
sehen Sie, dass unter dem Datenbanknamen ein Eintrag für die
Tabelle erschienen ist. Durch Klicken auf den Namen der Tabelle
werden die Spalten der Tabelle mit ihren zugehörenden Datentypen
angezeigt. Auf diese Weise können Sie verifizieren, dass die
Tabelle auch in der beabsichtigten Weise erzeugt wurde. Das
entsprechende MySQL-Kommando, welches hierbei implizit von
phpMyAdmin ausgeführt wurde, ist DESCRIBE buch:
Nachdem die Tabelle erzeugt wurde, können wir Daten über Bücher
einfügen, die im Internet-Shop angeboten werden sollen. Auch
hierfür existieren spezielle MySQL-Befehle: INSERT
und LOAD DATA.
INSERT ist für die Eingabe von Daten über die
Kommandozeile gedacht. Die Syntax dieses Befehls ist:
INSERT INTO tabellenname SET
spaltenname_1 = wert_1,
spaltenname_2 = wert_2,
…
spaltenname_n = wert_n,
;
Für diesen Befehl existiert auch eine alternative Syntax:
Es muss also jeweils neben dem eigentlichen Wert auch angegeben
werden, in welche Spalte dieser eingesetzt werden soll. Wird für
jede Spalte ein Wert eingegeben und dies in der Reihenfolge der
Spalten in der Tabelle, kann auch eine vereinfachte Syntax
benutzt werden:
INSERT INTO tabellenname
VALUES (wert_1,wert_2, ..., wert_n);
Um Absatzproblemen aus dem Wege zu gehen, entscheiden Sie sich,
nur Bestseller anzubieten. Das Buch "Harry Potter und der
Feuerkelch" von Joanne K. Rowling, bei Carlsen zum Preis von
22,50 Euro erschienen, wird dann wie folgt eingegeben:
mysql> INSERT INTO buch VALUES (
1, "Joanne K. Rowling", "Harry Potter und der Feuerkelch",
"Carlsen", 2001, 22.50
);
Query OK, 1 row affected (0.05 sec)
Eine der Hauptaufgaben eines DBVS besteht darin, dem/der
Benutzer/-in Befehle zur Verfügung zu stellen, mit denen die in
der Datenbank enthaltene Information auf einfache Weise selektiv
ausgelesen werden kann. In MySQL ist dies der SELECT-Befehl.
Um uns anzuschauen, wie der obige Datensatz in der Tabelle
"Buch" abgelegt worden ist, reicht die folgende, einfache
Variante des SELECT-Befehls:
mysql> SELECT * FROM buch;
+----+------------+------------------+----------+------------------+-------+
| ID | Autor | Titel | Verlag | Erscheinungsjahr | Preis |
+----+------------+------------------+----------+------------------+-------+
| 1 | Joanne K. | Harry Potter und | Carlsen | 2001 | 22.50 |
| | Rowling | der Feuerkelch | | | |
+----+------------+------------------+----------+------------------+-------+
1 row in set (0.05 sec)
Das Buch "Harry Potter und der Feuerkelch" wurde also als
erster Datensatz in die Tabelle eingefügt.
Der SELECT-Befehl ist ein sehr mächtiger Befehl,
mit dessen Syntax wir uns weiter unten noch etwas genauer
beschäftigen werden.
Um Daten aus einer Tabelle löschen zu können, besitzt MySQL den
DELETE-Befehl:
DELETE FROM tabellenname WHERE bedingungen;
Eine Kurzform dieses Befehls kann benutzt werden, um alle Daten
einer Tabelle zu löschen:
DELETE FROM tabellenname;
Wie Sie sehen, geht das Löschen von Daten in MySQL gefährlich
einfach. Der DELETE-Befehl sollte daher nur nach sorgfältiger
Überlegung eingesetzt werden.
Nach Absenden des obigen Befehls für die Tabelle buch
und Bestätigen der Operation ist die Tabelle leer:
mysql> DELETE FROM buch;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM buch;
Empty set (0.00 sec)
Nun wäre es etwas mühsam, wenn wir alle Datensätze manuell in
die Tabelle eintippen müssten. Um dies zu vermeiden, verfügt
MySQL über den LOAD DATA-Befehl zum Einlesen von
Daten aus einer lokal abgelegten Textdatei. Speichern Sie
zunächst die vorbereitete Textdatei buecher.txt,
die eine Reihe von Titeln mit Phantasiewerten für das
Erscheinungsjahr enthält, auf ihrem Rechner.
Wir werden zum Laden der Datei den LOAD DATA
Befehl implizit benutzen. Selektieren Sie in phpMyAdmin
die Tabelle buch. Klicken
Sie dann oben im Fenster den Reiter Importieren an. In dem Fenster,
welches daraufhin geöffnet wird, können Sie mit Datei auswählen
die vorbereitete Textdatei auswählen. Als Zeichencodierung wählen Sie
utf-8, falls nicht ohnehin schon
angegeben. Als Dateiformat nehmen Sie unter der
Rubrik Format: den Eintrag CSV using LOAD DATA.
Die CSV Optionen,
die nachfolgend angezeigt werden, können Sie unverändert übernehmen, da der Aufbau
der Textdatei den voreingestellten Werten folgt, d. h. jeder Datensatz nimmt
eine Zeile ein. Innerhalb der Zeile sind die einzelnen Spaltenwerte durch
Semikolon voneinander getrennt und die Einträge in Anführungsstriche gesetzt.
Klicken Sie dann auf OK. Es erscheint eine Meldung, dass 40
Zeilen eingefügt wurden. Durch Eingabe von SELECT * FROM buch
können Sie sich die komplette Tabelle auflisten lassen.
Aufgabe 5: Gespeicherte Daten selektiv auslesen
Die obige Anwendung des SELECT-Befehls ist ein
sehr einfaches Beispiel dafür, wie Daten aus einer Datenbank
mittels SELECT extrahiert werden können.
Tatsächlich ist der SELECT-Befehl das komplexeste SQL-Kommando
überhaupt. Die vollständige Syntax des SELECT-Befehls kann im
Abschnitt 6.4.1
SELECT Syntax des MySQL-Manuals nachgeschlagen werden. In
diesem Abschnitt werden wir nur die wichtigsten Funktionen an
Hand einiger Beispiele ausprobieren.
Allgemein wird SELECT dafür benutzt, um Zeilen
aus einer Tabelle unter bestimmten Kriterien auszulesen. Dabei
kann ebenfalls definiert werden, welche Spalten der selektierten
Zeilen angezeigt werden sollen. Der Kern des Kommandos lässt
sich auf folgende Syntax reduzieren:
SELECT Spalten FROM Tabellen [WHERE Bedingungen] [ORDER BY Reihenfolge]
Spalten: Dies ist i. d. R. der Name
einer Spalte oder eine Liste durch Kommas voneinander
abgetrennter Spaltennamen, z. B.: Autor, Titel.
In der einfachsten Form wird das Wildcard-Zeichen *
benutzt, was soviel bedeutet wie: alle Spalten.
Wird aus mehreren Tabellen im Rahmen einer Abfrage
selektiert, was durchaus möglich ist, und kommen
Spaltennamen in mehreren Tabellen vor, so ist jeweils noch
der Tabellenname vor den Spaltennamen zu setzen und durch
Punkt abzutrennen, z. B. so: Buch.Autor.
Tabellen: Eine durch Kommas getrennte
Liste von Tabellennamen. Im einfachsten Fall ein einzelner
Tabellenname, wie z. B. Buch.
WHERE: Die WHERE-Klausel ist
optional und dient als Filterkriterium. Als Filterkriterien
stehen alle üblichen Vergleichsoperatoren
zur Verfügung, z. B. ID < 20. Diese
Filterregel würde alle Bucheinträge mit ID kleiner 20
selektieren. Vergleichsoperatoren können nicht nur für
Zahlenwerte, sondern auch für Zeichenketten benutzt werden.
Dann erfolgt die Selektion gemäß der normalen lexikalischen
Ordnung.
Angenommen, wir wollen uns nur die Einträge in der Datenbank
für die Autorin "Joanne K. Rowling" auflisten lassen, dann
können wir dies erreichen, indem wir den SELECT-Befehl
mit einer entsprechenden WHERE-Klausel versehen:
mysql> SELECT * FROM buch
WHERE autor="Joanne K. Rowling";
+----+-----------+-----------------------+---------+------------------+-------+
| ID | Autor | Titel | Verlag | Erscheinungsjahr | Preis |
+----+-----------+-----------------------+---------+------------------+-------+
| 16 | Joanne K. | Harry Potter und | Carlsen | 2001 | 22.50 |
| | Rowling | der Feuerkelch | | | |
| 18 | Joanne K. | Harry Potter und der | Carlsen | 2000 | 15.50 |
| | Rowling | Gefangene von Askaban | | | |
+----+-----------+-----------------------+---------+------------------+-------+
2 rows in set (0.00 sec)
Die Ausgabe mag jetzt diverse Informationen enthalten, die uns
gar nicht näher interessieren und deshalb mehr verwirren als
helfen. So wissen wir z. B. bereits, dass wir nach Büchern von
"Joanne K. Rowling" suchen. Es ist deshalb nicht erforderlich,
diese Information noch einmal auszugeben. Deshalb reduzieren wir
die Ausgabe auf eine kurze Liste uns interessierender Spalten:
mysql> SELECT titel, verlag, preis
FROM buch
WHERE autor="Joanne K. Rowling";
+--------------------------------------------+---------+-------+
| Titel | Verlag | Preis |
+--------------------------------------------+---------+-------+
| Harry Potter und der Feuerkelch | Carlsen | 22.50 |
| Harry Potter und der Gefangene von Askaban | Carlsen | 15.50 |
+--------------------------------------------+---------+-------+
2 rows in set (0.00 sec)
Diese Ausgabe ist übersichtlicher, da wir die auszugebende
Information auf die wesentlichen Spalten Titel, Verlag und Preis
eingeschränkt haben.
Nun ist es ziemlich unpraktisch, nach Büchern von "Joanne K.
Rowling" zu suchen, wenn wir dafür jedesmal "Joanne K. Rowling"
eintippen müssen. Zum Glück unterstützt MySQL in Ausdrücken
Platzhalter. Innerhalb eines Ausdrucks wird der Unterstrich "_"
z. B. als Platzhalter für ein beliebiges Zeichen interpretiert.
Das Prozentzeichen "%" steht für eine beliebige Anzahl von
Zeichen. Damit lässt sich unsere Anfrage nach Büchern von
"Joanne K. Rowling" einfacher eingeben:
mysql> SELECT titel, verlag, preis
FROM buch
WHERE autor LIKE "%row%";
+--------------------------------------------+---------+-------+
| Titel | Verlag | Preis |
+--------------------------------------------+---------+-------+
| Harry Potter und der Feuerkelch | Carlsen | 22.50 |
| Harry Potter und der Gefangene von Askaban | Carlsen | 15.50 |
+--------------------------------------------+---------+-------+
2 rows in set (0.00 sec)
%row% bedeutet hier: Wähle alle Zeilen in der
Tabelle aus, die in der Spalte autor eine
Zeichenkette aufweisen, welche die Teil-Zeichenkette row
enthält. Wenn Sie sich die Anfrage genauer anschauen, wird Ihnen
vermutlich noch eine weitere Änderung auffallen: Das =
wurde durch LIKE ersetzt. Platzhalter-Operationen
funktionieren nicht mit = oder < >
(ungleich). Anstatt dessen müssen LIKE und NOT
LIKE verwendet werden. Mehr über Platzhalter in
Ausdrücken finden Sie im Abschnitt 3.3.4.7
Pattern Matching des MySQL-Manuals.
Mit den obigen Beispielen für Bedingungen sind die
Möglichkeiten von SELECT keineswegs erschöpft.
Interessieren wir uns z. B. für die Frage, welche Bestseller der
Hanser-Verlag in den Jahren nach 2001 im Programm hatte, so
können wir dies mit Hilfe eines boolschen Ausdrucks mit AND
abfragen:
mysql> SELECT autor, titel, erscheinungsjahr, preis
FROM buch
WHERE erscheinungsjahr > 2001 AND verlag = "Hanser";
+------------------+--------------------+------------------+-------+
| Autor | Titel | Erscheinungsjahr | Preis |
+------------------+--------------------+------------------+-------+
| Philip Roth | Das sterbende Tier | 2013 | 16.90 |
| Elke Heidenreich | Rudernde Hunde | 2005 | 15.90 |
| /Bernd Schröder | | | |
+------------------+--------------------+------------------+-------+
2 rows in set (0.06 sec)
Nun könnte es natürlich sein, dass die Ausgabe zu sehr in die
Breite läuft und dadurch die Übersichtlichkeit leidet. Zur
Lösung dieses Problems ermöglicht MySQL die Beschränkung der
Ausgabe auf eine Maximalzahl darzustellender Zeichen pro Spalte.
Wir formulieren die Anfrage wie folgt neu:
mysql> SELECT LEFT(autor, 20), titel, erscheinungsjahr, preis
FROM buch
WHERE erscheinungsjahr > 2001 AND verlag = "Hanser";
+----------------------+--------------------+------------------+-------+
| LEFT(autor, 20) | Titel | Erscheinungsjahr | Preis |
+----------------------+--------------------+------------------+-------+
| Philip Roth | Das sterbende Tier | 2013 | 16.90 |
| Elke Heidenreich/Ber | Rudernde Hunde | 2005 | 15.90 |
+----------------------+--------------------+------------------+-------+
2 rows in set (0.06 sec)
Mit der Direktive LEFT(autor, 20) weisen wir
MySQL an, von der Spalte autor nur die ersten 20
Zeichen, ausgehend von links, auszugeben.
Eine derartige Anfrage mag für den Hanser-Verlag problemlos
sein. Bei komplizierteren Verlagsnamen, wie z. B. "Kiepenheuer
& Witsch" ist dies unpraktisch. Dies stellt aber kein
Problem dar, da wir in Ausdrücken Teilausdrücke mit Platzhaltern
verwenden können:
mysql> SELECT LEFT(autor, 20), LEFT(titel, 20), erscheinungsjahr, preis
FROM buch
WHERE erscheinungsjahr > 2001 AND verlag LIKE "%kiep%";
+----------------------+----------------------+------------------+-------+
| LEFT(autor, 20) | LEFT(titel, 20) | Erscheinungsjahr | Preis |
+----------------------+----------------------+------------------+-------+
| Gabriel Garcia Márqu | Leben, um davon zu e | 2016 | 24.90 |
| Peter Ustinov/John M | Die Gabe des Lachens | 2005 | 22.90 |
| Bernd-Lutz Lange | Mauer, Jeans und Pra | 2009 | 17.90 |
+----------------------+----------------------+------------------+-------+
3 rows in set (0.06 sec)
Durch Kombination von Teilausdrücken mit AND und
OR lassen sich beliebig komplexe Anfragen an den
Server konstruieren.
Um herauszufinden, welche Verlage in der Datenbank vertreten
sind, wäre es hilfreich, wenn man sich diese unabhängig von
bestimmten Buchtiteln anzeigen lassen könnte. Die naheliegende
Eingabe des Befehls
SELECT verlag FROM buch;
löst dieses Problem nicht, da dieser Befehl nur zum Auflisten
des Inhaltes der entsprechenden Spalte - hier: Verlag - führt,
wobei Verlage, die durch mehrere Bücher in der Datenbank
vertreten sind, mehrfach aufgelistet werden. Für derartige
Probleme besitzt MySQL eine Lösung mit der DISTINCT-Direktive.
Wenn Sie den Befehl
SELECT DISTINCT verlag FROM buch;
eingeben, wird jeder Verlag nur einmal aufgelistet, allerdings
in ungeordneter Form. Auch dies lässt sich beheben, da MySQL die
Möglichkeit bietet, Ausgaben nach vorgegebenen Spalten zu
ordnen. Geben Sie folgendes ein:
SELECT DISTINCT verlag FROM buch ORDER BY verlag;
Nun erscheinen die Verlagsnamen in lexikalischer Ordnung.
Oftmals ist man an Fragen interessiert, wie oft bestimmte
Einträge in der Tabelle enthalten sind. Wir könnten z. B. daran
interessiert sein, wie viele unterschiedliche Titel von Joanne
K. Rowling zur Zeit in unserem Webshop angeboten werden. Zu
diesem Zweck bietet MySQL den COUNT-Befehl:
SELECT COUNT(*) FROM buch WHERE autor LIKE "%row%";
2 unterschiedliche Titel von Joanne K. Rowling sind in
unserer Datenbank zu finden.
Genauso könnten wir fragen, wie viele Verlage in unserem
Webshop vertreten sind. Da ein Verlag mehrere Buchtitel stellen
kann, versehen wir COUNT mit einem DISTINCT
als Filter:
SELECT COUNT(DISTINCT verlag) FROM buch;
22 Verlage sind in unserer Datenbank vertreten.
Mit den bisher gelernten SQL-Befehlen und den Möglichkeiten der
grafischen Benutzeroberfläche von phpMyAdmin sind Sie nun in der
Lage, Datenbanken und Tabellen zu erzeugen, zu editieren und
Informationen aus ihnen zu extrahieren. Zu einem
datenbankbasierten Webshop, wie z. B. Amazon, gehört allerdings
auch die Möglichkeit, die in den Datenbanktabellen enthaltenen
Produktinformationen über ein Webinterface abzufragen. Zu diesem
Zweck benötigen wir eine Anbindung der Bücherdatenbank an eine
geeignete Scriptsprache, wie z. B. PHP. Mit Hilfe der
Scriptsprache können dann per Webformular gesendete Anfragen an
den MySQL-Server weitergeleitet werden. Dies ist das Thema des
nächsten Übungsblattes.
