AMIGA-Magazin · Ausgabe 9/01 · Kurs: MySQL-Praxis auf dem Amiga (Folge 2)

9/01

Daten servieren

Im zweiten Teil unseres My-SQL-Kurses beschäftigen wir uns mit den wichtigsten Befehlen der SQL-Syntax. Außerdem zeigen wir Ihnen, wie Sie Datenbanken und Tabellen anlegen bzw. bearbeiten.

von Michael Christoph

Fallbeispiele:
Unterschiedliche Relationen zwischen Artikel und Lieferant in der Datenbank.

Der zweite Teil unseres Kurses beschäftigt sich in erster Linie mit dem Thema SQL. Die Abkürzung steht für Structured Query Language und liegt als ANSI-92-Standard vor. MySQL unterstützt einen Großteil dieses Standards und stellt darüber hinaus noch eigene Befehlserweiterungen zur Verfügung. Die hier angesprochenden Grundfunktionen, sind allgemeingültig und können auch mit anderen, SQL-fähigen Datenbanken, angewendet werden.

Relationen, Primär und Fremdschlüssel

MySQL ist eine so genannte »Relationale Datenbank«. Das bedeutet, dass die Daten nicht alle in einer einzigen Tabelle vorliegen, sondern sich über beliebig viele Tabellen verteilen können. Dabei unterscheidet man zwischen drei Arten der Relationen (s. »Fallbeispiele«):

1:1 Jeweils ein Satz der einen Tabelle ist mit einem Satz der anderen Tabelle verknüpft (z.B. das Gehalt zu einem Mitarbeiter)

1:n Ein Satz der einen Tabelle kann mit beliebig vielen einer anderen Tabelle verknüpft sein (z.B. viele E-Mail-Adressen einer Person)

n:m Hierbei können beliebig viele Sätze einer Datenbank mit beliebig vielen Sätzen einer anderen Datenbank verknüpft sein (mehrere Fotos, die mehrere Personen enthalten). Dazu wird eine eigene Datenbank benötigt, die nur die beiden Verknüpfungen enthält (z.B. BildNr und PersonNr).

Mit dem Primärschlüssel (primary key) kann eindeutig ein Eintrag einer Tabelle identifiziert werden. Der Index ist mit NOT NULL zu deklarieren und pratischerweise mit AUTO_INCREMENT zu versehen. Der Schlüssel kann sich dabei auch aus mehreren Feldern der Tabelle bilden (z.B. »Familienname + Vorname + Geburtsdatum«). Über den Fremdschlüssel (foreign key) werden Tabellen verbunden. Dabei können auch mehrere Sätze (oder keiner) einer anderen Datenbank zutreffen.

Datenbanken, Tabellen, Spalten und Felder
Im SQL-Sprachgebrauch ist eine Datenbank lediglich ein Container, der beliebig viele Tabellen aufnehmen kann. Jede Tabelle hat einen eigenen Formataufbau und kann Daten enthalten. Eine Datenbank dagegen kann niemals Daten enthalten.
Ein Eintrag in eine Tabelle wird als Spalte bezeichnet, dessen kleinste Einheit ein Feld ist. Dieses Feld entspricht einem Datenwert in der Tabelle.

Das Datenbankdesign

Bevor jedoch eine neue Tabelle erzeugt werden kann, muss überlegt werden, welche Daten darin gespeichert werden sollen. Entsprechend stellt SQL dann unterschiedliche Datentypen für (Fließkomma-) Zahlen und Strings zur Verfügung. Auch Binärdaten, so genannte BLOBs, können in der Datenbank gesichert werden. So können z.B. Bild- oder Musikdaten direkt mit einem Datensatz verknüpft werden. Effizienter und platzsparender für solche Dateien ist jedoch, nur den Datennamen in der Datenbank zu hinterlegen. Für das Beispiel im dritten Kursteil wird eine Adressdatenbank benötigt. Den Aufbau der gespeicherten Datensätze finden Sie in der Tabelle »Aufbau: Adressdatenbank«

Update:
Eine erzeugte Dump-Datei lässt sich in einem Rutsch mit phpMyAdmin auf den Server hochladen und dort interpretieren.

Bevor allerdings eine Tabelle mit all diesen Feldern angelegt wird, kommen in der Praxis die »Normalformen« zum Zuge. Diese legen fest, wie eine Tabelle strukturiert sein muss, damit später eine vernünftige Pflege möglich ist. In der Praxis kommen meist nur die ersten drei Normalformen zum Einsatz, die nachfolgend kurz vereinfacht aufgezeigt werden. Wer sich näher damit befassen möchte, sei auf die umfangreiche Literatur zu SQL und Datenbankdesign verwiesen.

Die erste Normalform .

Spalten mit gleichartigem Inhalt müssen eliminiert werden. .
Für jede Gruppe zusammengehöriger Daten muss eine eigene Tabelle gebildet werden. .
Jeder Datensatz muss durch einen so genannten Primärschlüssel eindeutig identifizierbar sein

Die zweite Normalform .

Immer dann, wenn sich Inhalte in Spalten wiederholen, müssen die Tabellen in mehrere Teiltabellen zerlegt werden.
Die Tabellen müssen durch so genannte Fremdschlüssel miteinander verbunden werden.

Die dritte Normalform .

Spalten, die nicht in unmittelbarer Abhängigkeit zum Primärschlüssel einer Tabelle stehen, müssen eliminiert werden also in eine eigene Tabelle ausgelagert werden.

Auf unsere Daten bezogen bedeutet das, dass die E-Mail-Adressen in eine eigene Datenbank auszulagen sind (Muster: 1-zu-n). Da das Foto auch mehrere Personen enthalten kann, wird dieses ebenfalls ausgelagert, um mit mehreren Personen verknüpft werden zu können hier wird nur 1-zu-n verwendet. Bei den Postleitzahlen und Orten wiederholen sich die Inhalte. Diese müssten also auch ausgelagert und mittels Primärschlüssel verknüpft werden. Soll die Datenbank auch ausländische Adressen aufnehmen, wäre zusätzliche eine Länderkennung notwendig. Achtung: Sich wiederholende Daten sind in einer eigenen Datenbank unterzubringen! Die Postleitzahl muss evtl. auch ein String sein das hängt davon ab, ob auch ausländische Adressen aufgenommen werden sollen.

Familien- und Vorname wurden getrennt, damit es zu keiner Verwechslung kommen kann und zum anderen soll auch eine Sortierung nach Familiennamen möglich sein.

Soweit die Theorie. In der Praxis wird man allerdings aus Performancegründen oftmals gegen diese Regeln verstoßen! So ist es z.B. einfacher, zu jeder Adresse direkt PLZ und Ort einzutragen. Es hängt immer von der Zielanwendung ab, inwieweit eine Tabelle optimiert wird. Sollen schnelle Suchanfragen möglich sein, wird man wiederholende Spaltenwerte in Kauf nehmen, um die Suche in einer zusätzlichen Datenbank zu sparen. Auch wenn die verknüpften Daten zu einem späteren Zeitpunkt nicht mehr vorhanden sind, müssen diese gesichert werden. Beispiel: Wenn ein Artikel gelöscht wird, muss trotzdem noch der Lieferschein rekonstruierbar sein. Ein Beispiel zur Erzeugung unserer Datenbank finden Sie in Listing 1 .

Integrität der Datenbank sicherstellen

Bei diesem Punkt muss MySQL leider passen. Für die Integrietät sind Sie also selber verantworlich. Durch Unachtsamkeit kann es dabei passieren, dass »tote« Datensätze vorhanden sind. Z.B. eine E-Mail-Adresse die auf eine Person verweist, die bereits gelöscht wurde. Soweit wäre das noch nicht weiter schlimm. Gehen wir aber einen Schritt weiter und legen eine neue Person unter der alten Personennummer an. Dann hat diese »von Geisterhand« eine völlig falsche E-Mail-Adresse erhalten die alte!

Beim Löschen von Tabellen-Einträgen ist also mit Sorgfalt vorzugehen und evtl. abhängige Datensätze unbedingt zu löschen! Natürlich nur, wenn nicht von einer anderen Datenbank darauf verwiesen wird.

Datenbanken und Tabellen anlegen

Informationen im Internet

MySQL	http://www.mysql.com
FAQ zu MySQL	http://mysql-faq.sourceforge.net
ANSI	http://www.ansi.org

Als Datenbank wird im MySQL-Sprachgebrauch ein Container bezeichnet, der beliebig viele Tabellen in sich aufnehmen kann. So hat z.B. die mitgelieferte MySQL-Datenbank u.a. die Tabellen user, host und dbP. Nur auf die Tabellen wird per SQL-Kommandos zugegriffen. Im Filesystem ist die Datenbank lediglich eine Schublade, in der die Tabellen als einzelne Dateien abgelegt werden. Jede Tabelle besteht aus mehreren Teildateien, die die Daten (*.myd), die Indexwerte (*.myi) und die Tabellenbeschreibung (*.frm) enthalten.

Eine neue Datenbank wird mit dem SQL-Kommando CREATE DATABASE erzeugt. In der Amiga-Version ist das allerdings nicht möglich! Stattdessen ist ein makedir MYSQL:data/name im Datenverzeichnis von mySQL auszuführen. Die einzelnen Tabellen werden mit CREATE TABLE <name> <eigenschaften> angelegt. Der Tabellenaufbau lässt sich mit ALTER TABLE <name> <eigenschaften> verändern und mit DROP TABLE <name> komplett löschen (Tabellenbeschreibung und vorhandene Daten). Neue Einträge in den Tabellen werden mit INSERT INTO <tabelle> VALUES <werte> erfasst oder mit UPDATE <tabelle> SET <wert> WHERE <bedingung> ein bereits vorhandener Eintrag aktualisiert.

Mit DELETE FROM <tabelle> WHERE <bedingung> werden alle auf die Bedingung zutreffenden Einträge aus der Tabelle gelöscht.

Kursübersicht

Teil 1: Beschreibung der Installation und Konfiguration von MySQL und Apache. Erster Test mit einem PHP-Skript und Sicherheitshinweise.

Teil 2: Vorstellung der einzelnen SQL-Kommandos und ihrer Anwendung mit Hilfe des MySQL-Clients.

Teil 3: Abfrage von Datenbanken mit Hilfe von PHP und Generierung dynamischer Webseiten.

Das mächtigste Kommando ist SELECT <ausgabefelder> FROM <tabelle> , mit dem sich Einträge in der Tabelle suchen lassen. Dabei können umfangreiche Such- (WHERE), Gruppierungs- (GROUP BY) und Sortierungsoptionen (SORT BY) zum Einsatz kommen.

Mit den beschriebene Befehlen können Sie in Kombination mit dem MySQL-Client Datenbanken und Tabellen ändern. Groß- und Kleinschreibung ist MySQL egal, sowohl bei den Kommandos als auch bei den Tabellennamen oder Datenbankfeldern. Zum Vergleichen von Zahlen und Texten existieren zwei unterschiedliche Operatoren:

Mit einfachem Gleichheitszeichen (=) werden zwei Zahlen verglichen, bei Texten ist LIKE zu verwenden. Der Vergleich muss 100-prozentig übereinstimmen. Als Platzhalter kann jedoch das Prozentzeichen (%) für beliebig viele Zeichen verwendet werden (entspricht #? beim Amiga).

Der MySQl-Monitor verlangt als Abschluss jeder Eingabe ein Semikolon dieses ist jedoch nicht Bestandteil eines SQL-Kommandos. Bei Abfragen mit anderen Tools (z.B. mit PHP) ist dieses Semikolon nicht notwendig.

MySQL-Administration

Im lokalen Einsatz lassen sich die Datenbanken einfach von der Shell aus anlegen und bearbeiten. Auch Select-Anfragen können hier ausprobiert werden. Dazu dient das Kommando mysql.
Der Aufruf erfolgt so

mysql [ -u <user> -p <passwort> -h <host> ]

Ist ein Passwort erforderlich kann es direkt (ohne Leerstelle!) mit angegeben werden. Host und User sind i.d.R. nicht erforderlich. Es erscheint der MySQL-Prompt. Sie können ihn mit status überprüfen, ob eine korrekte Verbindung zum Server besteht. Angezeigt werden im Erfolgsfall ein paar statistische Daten über den Zustand des MySQL-Servers. Weitere nützliche Kommandos sind z.B. SHOW DATABASES; oder SHOW TABLES um vorhandene Datenbanken oder Tabellen der aktuellen Datenbank anzuzeigen.

Aufbau: Adressdatenbank

Vorname	Text
Anschrift	Text
PLZ	Zahl
Ort	Text
(mehrere) E-Mail-Adressen	Text
Homepage-URL	Text
Bemerkung	Text
Foto	Binärdaten

Befindet sich die Datenbank bei einem Provider auf dessen Festplatte, werden Sie keinen direkten Zugriff (z.B. per Telnet) haben. Eine komfortable Lösung stellt »phpMyAdmin« dar. In der Datei config.inc.php sind die Einträge User/Passwort/ Server/Datenbank anzupassen. Danach kann die komplette Schublade zum Internet-Provider kopiert werden. Die Schublade dort sollte unbedingt mit einem Zugangsschutz versehen werden. Nutzen Sie beim Apache die Sicherung per .htaccess. Sie stellen so sicher, dass nicht jeder Zugriff auf Ihre Datenbank hat. Für Informationen wenden Sie sich an Ihren Internet-Provider, der einen Verzeichnisschutz mittels htpasswd anlegen kann. Die lokale Datenbank kann auch nicht einfach zum ISP kopiert werden. Zum einen werden Sie keine entsprechenden Rechte erhalten, zum anderen kann beim ISP auch eine ganz andere Version von mySQL im Einsatz sein. Der letzte Punkt ist unbedingt abzuklären, damit keine »zukünftigen« Kommandos zum Einsatz kommen.

Die komplette Datenbank incl. Beschreibung kann über mysqldump ausgelesen und in eine Datei geschrieben werden. Importiert wird das Ganze über das MySQL-Kommando. Diese Vorgehensweise ist auch bei einer Versionsänderung von MySQL notwendig/empfohlen. In diesem Zusammenhang können Sie einfach auf phpMyAdmin zurückgegreifen. Dort kann die komplette Dump-Datei übertragen und durch MySQL interpretiert werden (s. »Update«).

Im nächsten Teil des Kurses werden wir die Theorie in die Praxis umsetzen und ein Web-fähiges Adressbuch programmieren, das Sie auch im Internet verwenden können.

SQL-Befehls-Übersicht

CREATE DATABASE <datenbank>
Neue Datenbank mit dem angegebenen Namen erzeugen z.B:

CREATE DATABASE person
Hinweis: Beim Amiga nicht möglich! Statt dessen ist in der Shell makedir mysql:data/<datenbankname> zu verwenden!

CREATE TABLE <tabelle> <eigenschaften>
Eine neue Tabelle in der aktuellen Datenbank unter dem angegebenem Namen erzeugen. Unter <eigenschaften> werden die Datenfelder und -typen festgelegt Beispiel:

CREATE TABLE person (iNr
INT NOT NULL AUTO_INCREMENT,
sName CHAR,
dGebdatum DATE,
PRIMARY KEY (iNr) );

Mögliche Datentypen sind:

INT	Ganzzahl
REAl	Fließommazahl
DATE	Datum (amerikanisches Format Jahr-Monat-Tag)
TIME	Zeitwert
CHAR	Zeichenkette
TEXT	längere Zeichenkette
BLOB	Binärdaten Mit NUT NULL wird festgelegt, dass dieses Feld nicht leer bleiben darf.

AUTO_INCREMENT erhöht automatisch mit jedem neuen Eintrag das Zahlenfeld. PRIMARY KEY legt einen eindeutigen Index fest. Weitere Indizies können mit INDEX festgelegt werden. Durch einen Index wird die Suchzeit verkürzt, da nicht mehr jeder einzelne Satz der Reihe nach gelesen und verglichen werden muss.

DROP DATABASE <datenbank>
Löscht eine komplette Datenbank incl. aller darin enthaltenen Tabellen. Auch die Tabellenbeschreibungen gehen verloren! Dieses Kommando ist also mit Vorsicht zu genießen Beispiel:

DROP DATABASE person

DROP TABLE <tabelle>
Löscht eine komplette Tabelle mit allen enthaltenen Datensätzen und der Tabellenbeschreibung Beispiel:

DROP TABLE person

INSERT INTO <tabelle> VALUES (<werte>)
Füllt die Tabelle mit neuen Einträgen Beispiel:

INSERT INFO person VALUES (1,`Christoph, Michael`,`2001-06-14`)
Hierbei muss die Reihenfolge der Werte mit dem Tabellenaufbau übereinstimmen.

INSERT INTO person SET dGebdatum=`2001-06-14`, sName=`Christoph, Michael`
Bei dieser Anweisung werden nur die angegebenen Felder belegt.
Achtung: Existiert bereits ein Eintrag mit der angegebenen iNr (eindeutiger Index), erhalten Sie eine Fehlermeldung.

UPDATE <tabelle> SET (<werte>) [ WHERE <bedingung> ]
Aktualisiert Daten in der Tabelle, die die angegebene Bedingung erfüllen. Dabei können auch mehrere Sätze gleichzeitig verändert werden Beispiel:

UPDATE person SET dGebdatum = ¸2001-06-14` WHERE iNr=1
Setzt das Geburtsdatum für den Eintrag mit der iNr=1 neu. Ohne WHERE-Bedingung würden alle Einträge der Tabelle auf das neue Datum gesetzt:

UPDATE artikelpreise VALUES preis=preis*1.10 WHERE preis<10.0
Alle Preise, die unter 10 Mark liegen, werden um 10 Prozent erhöht.

DELETE FROM <tabelle> [ WHERE <bedingung> ]
Löscht komplette Datensätze aus der Tabelle, die die Bedingung erfüllen Beispiel:

DELETE FROM person WHERE sName LIKE ¸Christoph, Michael`
Hinweis: Ohne WHERE-Bedingung werden alle Einträge gelöscht!

SELECT <felder> FROM <tabelle> [ WHERE <bedingung> ] [ GROUP BY <groupfeld> ] [ ORDER BY <feld> ] [ LIMIT [offset,] <anzahlsaetze> ]
Suche von Daten in der Tabelle. Die einfachste Abfrage stellt SELECT * FROM person dar, die den kompletten Inhalt der Tabelle person mit allen Feldern anzeigt. Anhand der WHERE-Bedingung kann die Suche eingeschränkt werden. Durch GROUP können Gruppenspalten mit identischem Inhalt zusammengefasst werden. Mit ORDER wird die Ausgabe anhand der felder sortiert. LIMIT legt schließlich noch fest, wie viele Sätze angezeigt werden sollen Beispiel:

SELECT (sFamilienname,sOrt) FROM person WHERE sOrt LIKE ¸Passau` ORDER BY sFamilienname LIMIT 25
In der Ausgabe sieht man die ersten 25 Personen mit Ort sortiert nach Familienname, die in Passau wohnen. Ein weiteres Beispiel:

SELECT (sFamilienname,sEMailAdresse) FROM person,email WHERE person.iNr=email.person_iNr WHERE person.iNr=1
Jetzt werden alle E-Mail-Adressen der Person Nr 1 ausgegeben. Dazu wird die Person- und E-Mail-Datenbank miteinander verknüpft.

SELECT (sFamilienname,sEMailAdresse) FROM person LEFT JOIN email ON iNr=person_iNr
Zeigt alle Personen mit ihren E-Mail-Adressen an. Per LEFT JOIN werden auch solche Personen angezeigt, die keine E-Mail-Adresse haben. Wird hingegen FROM person,email verwendet, werden nur diejenigen Datensätze angezeigt, die in beiden Tabellen vorkommen. Eine E-Mail-Adresse ohne Person wird nicht angezeigt und auch eine Person ohne E-Mail-Adresse nicht. Die Verknüpfungsart spielt also eine sehr wichtige Rolle.
Zum Select-Kommando kann man ganze Bücher füllen! Daher wurde hier nur ein kleiner Überblick gegeben. Interessierte seien wieder auf die umfangreiche SQL-Literatur verwiesen. Im nächsten Teil wird außerdem anhand der Praxis-Beispiele SELECT weiter vertieft.

Kommentare, Fragen, Korrekturen und Kritik bitte an Webmaster AMIGA schicken.
Zuletzt aktualisiert am 23. Oktober 2001, Michael Christoph.