Grundlagen CGI-Programmierung mit Perl von Prof. Jürgen Plate

5 Perl und MySQL

5.1 Datenbank-Grundlagen

An Datenbank-Systeme werden in der Regel zwei grundlegende Forderungen gestellt: Vermeiden von Redundanz und Sicherstellung der Datenintegrität.

• Redundanzfreiheit bedeutet, dass Informationen nicht mehrfach abgelegt werden. Wurden bestimmte Informationen erfasst, so sollen diese Daten nur ein einziges Mal in der Datenbank vorhanden sein. Enthält die Datenbank z. B. Informationen zur Person "Hannibal Lecter, Tel: 0010-555-43421", so sollen die Eigenschaften Vorname, Name und Telefonnummer nur einmal existieren. Ändert sich etwa die Telefonnummer, muss sie nur an einer einzigen Stelle geändert werden. An allen anderen Stellen werden nur Verweise auf den Datensatz abgelegt.
• Datenintegrität: Alle Informationen unterliegen oft Einschränkungen. Ein Feld "Geburtsdatum" darf nur gültige Datumswerte enthalten. Zur Datenintegrität zählt ferner, dass das Datenbanksystem (Data Base Management System, DBMS) parallele Benutzerzugriffe gestattet, sodass parallele Lese- und Schreibzugriffe sich nicht gegenseitig behindern. Wird ein Datensatz gelöscht, müssen auch alle Verweise auf diesen Datensatz, die an anderer Stelle stehen, gelöscht werden (referenzielle Integrität).

• Die externe Sicht beschreibt den Zugriff der Benutzer auf die Datenbank. Diese Sicht verbirgt das Modell der konzeptionellen Sicht. Außerdem können für verschiedene Gruppen von Benutzern verschiedene Sichten definiert werden, was Sie oben schon gesehen haben: "root" hat eine andere Sicht als zum Beispiel "john".
• Die konzeptionelle Sicht der Daten stellt die zu modellierenden Objekte der realen Welt mit ihren Attributen und Verknüpfungen dar. Zum Beispiel hat ein Objekt "Firma" die Attribute "Name" und "Umsatzsteuer-Identifikation", ein Objekt "Mitarbeiter" besitzt dagegen die Attribute "Name", "Abteilung" und "Personalnummer". Mitarbeiter und Firma sind miteinander verknüpft, da zu einer Firma mehrere Mitarbeiter, zu einem Mitarbeiter (meist) genau eine Firma gehören.
• Die interne Sicht der Daten beschreibt die Realisierung des konzeptionellen Datenmodells in einem Data Base Management System (DBMS). Die internen Sicht der Daten ist vollständig unabhängig von der Modellierung auf der konzeptionellen Ebene. Hier geht es beispielsweise um effiziente Datenspeicherung in B-Bäumen.

Ein DBMS ist eine anwendungsunabhängige Software, das ein Datenmodell implementiert, d. h. die Definition eines DB-Schemas für eine konkrete Anwendung ermöglicht. Da das DBMS anwendungsunabhängig ist, speichert es das Schema normalerweise auf der Festplatte, oft zusammen mit dem DB-Zustand. Es erlaubt das Speichern des aktuellen DB-Zustands, z. B. auf der Festplatte, das Abfragen des aktuellen DB-Zustands (Datenbankrecherche) und das Ändern des DB-Zustands (Eintragen, Ändern und Löschen von Daten).

Das heute wichtigste DBMS-Modell ist das relationale Datenmodell, das von E. F. Codd in den 1970er Jahren entwickelt wurde: Eine Relation beschreibt die Zusammenhänge zwischen verschiedenen Eigenschaften von Daten in Form von Relationen, und die Operationen auf den Daten sind im Prinzip Mengenoperationen. Die Umsetzung des Datenmodells geschieht, indem Relationen als Tabellen dargestellt werden können. Die Spalten der Tabelle sind die Attribute, und jeder Datensatz der Relation wird als eine Zeile der Tabelle dargestellt, man nennt ihn auch Tupel (weshalb manche Leute Excel irrtümlicherweise für eine Datenbank halten).

Die Tabelle selbst besteht aus einem Kopf, in dem die Spalten benannt werden und einem Rumpf, der aus den Dateneinträgen besteht. Bei dem Entwurf der Relationen sind folgende Prinzipien zu berücksichtigen:

• In einer Tabelle dürfen keine doppelten Tupel vorkommen. Jeder Datensatz bezeichnet genau eine Tatsache bzw. ein Objekt. In der relationalen Terminologie werden solche Objekte auch als "Entitäten" bezeichnet.
• Die einzige Ordnung der Datensätze einer Relation ist die Reihenfolge, in der sie angelegt wurden. Andere Ordnungen, z. B. eine alphabetische Sortierung, werden erst bei der Ausgabe der Daten erzeugt.
• Auch die Attribute sind nicht geordnet. Bei der Anlage einer Relation ergibt sich eine solche Ordnung meist von selbst aus dem logischen Zusammenhang. Bei der späteren Pflege der Datentabelle werden neue Attribute einfach an das Ende der Relation angehängt. Eine Ordnung der Attribute wird ebenfalls erst bei der Abfrage der Daten angelegt.
• Alle Attribute einer Tabelle dürfen in jedem Datensatz nur genau einen einzigen Wert enthalten. Man nennt dies "atomar".
• Kein Attribut darf in einer Tabelle mehrfach vorkommen. Auch auf die gesamte Datenbank bezogen, sollte es keine solchen mehrfachen Vorkommen von Attributen (Redundanz) geben. Für jedes Attribut wird genau eine Spalte vorgesehen, es darf nicht in mehreren Spalten vorkommen.

Die wichtigsten Richtlinien neben den obigen Regeln sind dabei neben der Redundanzfreiheit das Verwenden von Entitäten und das strikte Vermeiden von Prozessdaten. In einer Relation werden nur Entitäten (Tatsachen) aufgenommen, etwa eine Adresse, ein Artikelstammsatz, eine Bestellung, usw. Jede Entität wird durch ihre Attribute (Eigenschaften) eindeutig beschrieben. So sind Werte, die durch Rechnerei aus den Attributen ermittelt werden können, keine Entitäten, sondern Prozessdaten, die eine Form von Redundanz darstellen.

Würde man beispielsweise das Datum der Inbetriebnahme und die Laufzeit (also das Alter) einer Maschine in der Datenbank speichern, führt das zu einer ständigen Updatebedarf, da jeden Tag nachgesehen werden muss, ob irgendeine Maschine "Geburtstag" hat. Bei der Laufzeit handelt es sich offensichtlich um ein Prozessdatum, das sich jederzeit aus dem Datum der Inbetriebnahme und dem aktuellen Datum berechnen lässt.

Das zweite wichtige Kriterium ist die Eindeutigkeit der Daten. Die Beschreibung einer Entität muss stets eindeutig sein. So darf beispielsweise die Beschreibung einer Person nur auf eine ganz bestimmte Person zutreffen. Um diese Eindeutigkeit zu erreichen, erweitert man den Datensatz oft um ein Identifikationsmerkmal, etwa eine Personaloder Artikelnummer. Solch ein Identifikationskriterium kann auch gleich als Schlüssel (Primärschlüssel, siehe unten) verwendet werden.

Datenbankanfragen erzeugen aus den vorhandenen Tabellen (= Relationen) neue Tabellen, indem aus einer Tabelle entweder einzelne Datensätze mit bestimmten Eigenschaften oder bestimmte Attribute (Spalten) ausgewählt werden. Zusätzlich lassen sich verschiedene Tabellen zueinander in Relation setzen.

Objekttypen werden als Relationen dargestellt, bei der die Attribute in Klammern aufgezählt werden. Jedem Objekttyp sind bestimmte Eigenschaften zugeordnet, die Attribute eines Objekttyps. Alle Objekte eines Objekttyps besitzen daher die gleichen Eigenschaften. Da doppelte Datensätze verboten sind, müssen sie sich in mindestens einem Attribut unterscheiden.

Attribute, nach denen man suchen oder die Daten ordnen kann, nennt man Schlüssel. Ein Attribut, das für jeden Datensatz einzigartig und eindeutig ist, nennt man Primärschlüssel. Die Attribute des Primärschlüssels werden oft unterstrichen oder in Fettdruck dargestellt. Beispiele für Relationen sind:

Student (Matrikelnummer, Name, Vorname, Straße, PLZ, Ort, Geburtsdatum)
Kurs (Kursnummer, Thema, Art, Halbjahr)

Belegt (Matrikelnummer, Kursnummer, Punkte)

Für eine einzelne Spalte können unabhängig voneinander zwei Einschränkungen definiert werden:

• Nicht Null (not null): Ein solches Feld darf nicht leer sein, es muss auf jeden Fall einen Wert enthalten.
• Eindeutig (unique): Jeder Wert in dieser Spalte darf innerhalb der Spalte nur einmal vorkommen.

Das relationale Datenmodell kennt nur Tabellen. Die Formate der Datenbanktabellen werden als Schema beschrieben. Die Tabellen im Datenbankschema tragen eindeutige Namen. Jede Tabelle im Relationenmodell hat eine feste Anzahl eindeutig benannter Spalten. Für jede dieser Spalten muss ein Wertebereich angegeben sein (was dem Datentyp bei einer Programmiersprache entspricht). Die in den Ausprägungen der Tabellen stehenden Zeilen (Tupel) entsprechen alle der gemeinsamen Definition des Schemas. Beziehungen im Relationenmodell sind immer binär. Die Fachbegriffe sind in der folgenden Tabelle zusammengefasst:

Domain	Wertebereich
Relation	Tabelle
Attribut	Spalte
Tupel	Datensatz, Rekord
Primary-Key	Hauptschlüssel
Alternate-Key	Zweitschlüssel
Foreign-Key	Fremdschlüssel

Es gibt drei verschiedene Arten von Relationen, je nach Art der gespeicherten Information:

• Eine 1:1-Relation verknüpft den Datensatz einer Tabelle mit genau einem Datensatz einer anderen Tabelle. Dies ist immer dann nützlich, wenn bestimmte Informationen nicht so oft benötigt werden wie andere. In der Regel handelt es sich um sogenannte Stammdaten.
• Eine 1:n-Relation verbindet den Datensatz einer Tabelle mit mehreren Datensätzen einer anderen Tabelle. Diese Form der Verknüpfung kommt am häufigsten vor: Informationen über Werte, die in einer Spalte einer Tabelle beliebig oft vorkommen können, werden in einer separaten Tabelle erfasst. Die erste Tabelle enthält nur noch einen Verweis auf einen bestimmten Datensatz der zweiten Tabelle.
• Eine m:n-Relation kombiniert beliebig viele Datensätze einer Tabelle mit beliebig vielen Records einer anderen, beispielsweise eine Tabelle, die eine Liste lieferbarer Produkte enthält, und eine zweite Tabelle mit Kundenadressen. Jeder Artikel kann von beliebig vielen Kunden gekauft werden, und jeder Kunde kann beliebig viele verschiedene Artikel kaufen. Im relationalen Datenbankmodell wird diese Art von Relation indirekt dargestellt: Eine dritte Tabelle listet die einzelnen Kaufaktionen auf; jeder Datensatz enthält dabei eine Verknüpfung zu einem bestimmten Kunden und eine weitere zu einem bestimmten Artikel.

Datenbank-Entwurf

Mithilfe der Normalisierung soll ein redundanzfreies Modell erarbeitet werden. Dies wird dadurch erreicht, dass sämtliche Attribute nur einmal in der Datenbank abgespeichert sind. Dadurch soll verhindert werden, dass die Datenintegrität verletzt wird.

Erste Normalform (1NF)
Eine Tabelle besitzt die erste Normalform, wenn sie folgende Bedingungen erfüllt:

• Sie ist zweidimensional mit Zeilen und Spalten.
• Jede Zeile enthält Daten, die zu einem Objekt oder einem Teil eines Objektes gehören.
• Jede Spalte enthält Daten für ein einziges Attribut des Objektes.
• Jede Datenzelle enthält einen einzigen Eintrag.
• ede Spalte muss einen (in der Tabelle) einmaligen Namen tragen.
• Keine zwei Reihen dürfen identisch sein.
• Die Reihenfolge der Spalten und Reihen ist bedeutungslos.

Dazu ein Beispiel: Die Tabelle "Person" besitzt die Attribute Personalnummer, Name und Anschrift. Das Attribut "Anschrift" besteht jedoch auch drei Unterkategorien: Postleitzahl, Ort und Straße. Die erste Normalform verlangt, das Attribut Anschrift durch die drei oben aufgeführten Attribute zu ersetzen. Auf diese Weise wird vermieden, dass in einer Tabellenspalte mehrere Werte auftreten.

Ein zweiter problematischer Fall, der nicht so offensichtlich ist, sind Spalten, die gleichartige Attribute enthalten. Sie alle kennen beim Neuwagenkauf die diversen Ausstattungsalternativen, die normalerweise zu Paketen geschnürt werden. So könnte sich beispielsweise folgende Tabelle ergeben:

Modell	Farbe	...	Paket 1	Paket 2	Paket 3
1500	rot	...	X
1502	silber	...	X	X
1504	schwarz	...	X	X
und so weiter

Wenn sich jetzt der Vertrieb noch ein weiteres Paket ausdenkt, kommt auch eine weitere Spalte hinzu und dann noch eine und so fort. In fast allen Spalten steht nichts drin. Diese Tabelle kann recht schnell in die 1NF übergeführt werden, indem man nämlich statt eines binären Wertes in jeder Paketspalte eine Spalte "Paket" einführt, in der das jeweilige Paket mit seiner Nummer aufgeführt ist. Gibt es Modelle mit mehreren Paketen, erhält die Datenbank zwei oder mehr Zeilen:

Modell	Farbe	...	Paket
1500	rot	...	1
1502	silber	...	1
1502	silber	...	2
1504	schwarz	...	1
1504	schwarz	...	3

Das Problem ist verschwunden. Egal wie viele Ausstattungspakete es gibt, es bleibt bei einer Spalte dafür. Allerdings tauchen nun einige Modelle in mehreren Zeilen auf. Formal ist das kein Problem, denn die Zeilen unterscheiden sich ja in der letzten Spalte. Jedoch erkaufen wir diesen Vorteil mit erhöhter Redundanz. Das kann also noch nicht der Weisheit letzter Schluss sein.

Zweite Normalform (2NF)
Eine Tabelle liegt in der zweiten Normalform vor, wenn sie sich in der ersten Normalform befindet und wenn jedes nicht dem Primärschlüssel angehörende Attribut funktional vom gesamten Primärschlüssel, aber nicht von Teilen des Primärschlüssels abhängt.

Unter "funktionaler Abhängigkeit" versteht man in diesem Zusammenhang, dass von einem Attributwert direkt auf einen anderen Attributwert geschlossen werden kann. Umgekehrt formuliert: Eine Tabelle ist noch nicht in der zweiten Normalform, wenn sie einen zusammengesetzten Primärschlüssel hat und ein Nichtschlüssel-Attribut nicht vom ganzen Primärschlüssel, sondern nur von einem Teilschlüssel abhängt. In diesem Fall wird das Nichtschlüssel-Attribut mit dem Primärschlüsselteil, von dem es funktional abhängig ist, in eine eigene Tabelle herausgezogen.

Mit anderen Worten: Eine Tabelle befindet sich dann in der zweiten Normalform, wenn sie schon in der ersten Normalform ist und jedes zum Identifikationsschlüssel gehörende Attribut voll vom Identifikationsschlüssel abhängig ist, nicht aber von einzelnen Schlüsselteilen.

Beispielsweise sind in einer Relation "Person" die Attribute Name, Postleitzahl, Ort und Strasse funktional abhängig vom Attribut Personennummer, da von der Personennummer direkt auf den Namen und die Anschrift einer Person geschlossen werden kann. Umgekehrt kann man nicht von einem Ort oder einem Namen auf eine bestimmte Person (repräsentiert durch die Personennummer) schließen.

Für den Entwurf der Autohaus-Datenbank heißt das: Immer dann, wenn sich Inhalte in Spalten wiederholen, müssen die Tabellen in mehrere Teiltabellen zerlegt werden. Diese Tabellen müssen durch sogenannte Fremdschlüssel (Foreign Keys) miteinander verbunden werden. Die Tabelle der 1NF lautete:

Modell	Farbe	...	Paket
1500	rot	...	1
1502	silber	...	1
1502	silber	...	2
1504	schwarz	...	1
1504	schwarz	...	3

Die Modellnummer ist ein brauchbarer Primärschlüssel der Modelltabelle, und die Paketnummer eignet sich für den gleichen Zweck bei den Paketen (womit wir auch gleich einen Platz haben, um die Ausstattungsdetails unterzubringen). Zuerst die Modelltabelle (die nun keine Paketspalte mehr hat):

Modell	Farbe	...
1500	rot	...
1502	silber	...
1502	silber	...
1504	schwarz	...
1504	schwarz	...

Dann die Ausstattungstabelle:

Paket	Alufelgen	Ledersitze	...
1	X	X	...
2	X	...	...
3	X	...	...
4	...	...	...

Nun fehlt aber noch die Verbindung der beiden. Diese Tabelle besteht nur noch aus Schlüsseln, genauer Fremdschlüsseln. Nicht unbedingt, um Eindeutigkeit herzustellen, sondern wegen der leichteren Handhabbarkeit bekommt sie noch einen Primärschlüssel, der "Id" heißt (diese Methode, einen zusätzlichen Primärschlüsseel hinzuzufügen wird oft angewendet):

Id	Modell	Paket
1	1500	1
2	1502	1
3	1502	2
4	1504	1
5	1504	3

Dritte Normalform
Eine Relation befindet sich in der dritten Normalform, wenn sie sich in der zweiten Normalform befindet und alle Attribute, die nicht Schlüsselattribute sind, funktional nicht voneinander abhängig sind. Die einzige Regel der dritten Normalform lautet: 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).

Das kann am obigen Beispiel nicht mehr demonstriert werden, es ist schon in der 3NF. Stellen Sie sich vor, dass es bei den Alufelgen ja wieder mehrere Hersteller, Größen und Ausprägungen gibt, die zunächst als Spalten in der Pakettabelle gelandet wären. Dann wären noch weitere Schritte nötig, um auch diese Tabellen zu "entflechten".

Die Notwendigkeit der ersten Normalform dürfte unmittelbar einleuchten. Die zweite und dritte Normalform folgen einem relativ stark mathematisch geprägten Konzept. Die zweite Normalform verlangt, dass Attribute in den richtigen Tabellen untergebracht sind. Normalerweise lassen sich statische Datenfelder schnell erkennen und in eigene Tabellen mit Primärschlüsseln exportieren. Daneben gibt es zusätzliche Daten, die sich häufiger ändern und die Grunddaten miteinander verknüpfen. Eine solche Tabelle enthält einige Fremdschlüssel mit Verweisen auf die Grundtabellen sowie zusätzliche Spalten. Die dritte Normalform entspricht im Wesentlichen der Forderung, eigenständige Einheiten richtig zu identifizieren. Ist beispielsweise eine Tabelle mit Filmtiteln, Regisseuren und ihren Biographien gegeben, ist ganz klar die Biographie nur vom Regisseur, nicht jedoch von einem Film abhängig, bei dem er Regie geführt hat. Die Regisseure landen daher in einer eigenen Tabelle mit Primärschlüssel. Nur dieser wird in die Tabelle mit den Filmtiteln eingebunden.

Zusammenfassung Normalformen

• Die erste Normalform (1NF) fordert, dass die Information in jedem Feld einer Datenbank atomar ist (eine nicht weiter zerlegbare Einzelinformation).
• Die zweite Normalform (2NF) fordert zusätzlich, dass Datensätze nur direkte Informationen über einen einzigen Sachverhalt enthalten. Alle Felder in einer Tabelle, die die zweite Normalform erfüllt, dürfen nur vom Primärschlüssel dieser Tabelle abhängen.
• Die dritte Normalform (3NF) ist erfüllt, wenn alle Felder funktional unabhängig voneinander sind. Eine Tabelle, die zwar die zweite, aber nicht die dritte Normalform erfüllt, belässt eine eindeutig zu einem bestimmten Feld gehörige Zusatzinformation innerhalb einer Tabelle, in der dieses Feld keinen Schlüssel bildet.

Mit der Normalisierung vermeidet man auch sogenannte Anomalien. Geht man von einer nicht normalisierten Tabelle aus, bei der alle Attribute und Entitäten in einer einzigen Tabelle gespeichert sind, können folgende Anomalien auftreten, die ich an einer Kundendatei erläutern will (bei der Sie die Notwendigkeit einer Normalisierung sicher klar erkennen). Die Tabelle hat die Spalten:

Auftragsnr., Datum, Kunde, Artikelnr., Art.-Bezeichnung, Menge

• Einfüge-Anomalie: Ein Kunde kann nur dann eingetragen werden, wenn er auch eine Ware kauft.
• Lösch-Anomalie: Wird die eventuell einzige Lieferung an einen Kunden gelöscht, so sind auch die Informationen über Wohnort, Anschrift usw. verloren.
• Änderungs-Anomalie: Falls ein Kunde umzieht, so sind mehrere Tupel nachträglich zu ändern, und man kann nur hoffen, dass keiner übersehen wird.
• Update-Anomalie: Wird eine Artikelnummer geändert, müssen alle bisherigen Einträge ebenfalls aktualisiert werden, da sonst Inkonsistenzen entstehen.

5.2 MySQL-Tabellen anlegen

#!/usr/bin/perl
use DBI;

Anmerkung: Es wird vorausgesetzt, daß der MySQL-Server installiert ist. Zusätzlich sind zwei Perl-Module nötig: das allgemeine Datenbankinterface DBI und der spezielle DBD::mysql-Treiber vom CPAN. Die Installation vom CPAN ist recht einfach:

perl -MCPAN -e shell
cpan> install DBD::mysql
cpan> install DBI

Dazu packen wir noch eine laufende Fahrzeugnummer (Integer) in die Tabelle (als Primärindex, siehe unten). Das Kennzeichen wird als Text-String und die Anzahl der Sitze als Integer definiert. Der Autoverleih legt die Daten aller Fahrzeuge in der Tabelle "fahrzeuge" in der Datenbank ab. Jede Zeile der Tabelle entspricht genau einem Fahrzeug des Fuhrparks:

ID     INT
kennz  VARCHAR(20)
sitze  INT

Das Programm braucht eine logische Datenbank namens "bring_m_backalive". Das Anlegen kann direkt mit dem mysql-Client erfolgen (CREATE DATABASE bring_m_backalive;). Oder Sie installieren sich das Tool PHPMyAdmin, mit dem man alle Datenbankoperationen über den Browser erledigen kann.

Das folgende Skript zeigt die Schritte in Perl, um in der leeren oder schon vorbesetzten Datenbank "bring_m_backalive" eine leere Tabelle "fahrzeuge" anzulegen. Enthält die Datenbank schon eine Tabelle gleichen Namens, wird die alte gelöscht und eine neue angelegt.

#!/usr/bin/perl
use warnings;
use strict;

my $USER = "root";
my $PASS = "secret";

use DBI;

my @dsn = ("DBI:mysql:database=bring_m_backalive;" .
           "host=localhost", $USER, $PASS);

# Datenbank andocken
my $dbh = DBI->connect(@dsn,
    { PrintError => 0,
      AutoCommit => 1,
    }
    ) or die $DBI::errstr;

# Alte Tabelle loeschen
$dbh->do("DROP TABLE fahrzeuge");

# Neu anlegen
$dbh->do("CREATE TABLE fahrzeuge (
      ID INTEGER AUTO_INCREMENT,
      kennz VARCHAR(20),
      sitze INTEGER,
      PRIMARY KEY (ID))"
      ) or die $dbh->errstr();

$dbh->disconnect();

"DBI:mysql:database=bring_m_backalive;host=localhost"

"DBI:mysql:bring_m_backalive;localhost"

Die connect-Funktion der DBI-Klasse verbindet das Skript mit der laufenden Datenbank. Sie nimmt die eben definierten Konfigurationsparameter in "@dsn" und eine Referenz auf einen Hash mit weiteren Steuerungsparametern entgegen. Die Option "PrintError" wird auf 0 (False) gesetzt, damit die DBI-Methoden Fehlermeldungen unterdrücken. Die von connect() zurückgelieferte Referenz auf ein DBI-Objekt erlaubt es nun, mit der Datenbank zu kommunizieren.

Die do()-Methode setzt SQL-Befehle ab, die von der Datenbankengine ausgefüht werden. Nach dem Löschen einer etwa vorhandenen Tabelle wird eine leere Tabelle "fahrzeuge" mit allen Felddefinitionen angelegt. Den mehrzeiligen String zwischen den Anführungszeichen gibt Perl an die Methode weiter. Die Oder-Bedingung fängt eventuell von do() gelieferte Fehler ab.

Mit errstr() des Datenbank-Handles $dbh erhält man den Text des letzten Fehlers zurück, so daß das Konstrukt oben einfach mit die() und der Fehlermeldung der Datenbank abbricht, falls etwas schief geht. Bei CGI-Programmen landen solche Fehlermeldungen im Error-Log des Webservers.

"ID" in der Tabelle ist die laufende Nummer des Fahrzeugs. Das Attribut "AUTO_INCREMENT" ist eine MySQL-spezifische SQL-Erweiterung und gibt an, dass die Datenbank den numerischen Wert dieses Feldes selbst erzeugen soll: Beginnend mit "1" wird jeder neue Tabelleneintrag um eins erhöht. Das Attribut "PRIMARY KEY" (Primärschlüssel) bestimmt, über welche Spalte die Datenbank die Tabelle primär indiziert. Da die Seriennummer "ID" numerisch und für jede Zeile eindeutig ist, bietet sie sich als Primärschlüssel an. Es ist übrigens in der Regel sinnvoll bei jeder Tabelle einen solchen Primary Key anzulegen, über den sich Datensätze eindeutig refernziert lassen.

Ein erzeugtes Handle wird durch die Methode disconnect() wieder geschlossen.

Wer will, kan sich auch eine Funktion für das Öffnen der Datenbank schreiben, in der alle unveränderlichen Angaben enthalten sind. Die folgenden Funktion öffnet eine Datenbank auf einem angegebenen Host und liefert ein Datenbankhandle $dbh zurück:

sub open_dbase
  {
  my %Datenbank =
    ( dbname => 'fahrzeuge',
      user   => $ENV{'LOGNAME'},
      # ggf. hier einen festen User eintragen
      pass   => 'Geheim',
      host   => 'localhost',
      driver => 'mysql',
      @_
    );
  # alternativ kann das Passwort auch von Datei eingelesen werden
  # (guenstig, wenn mehrere Programme auf die DB zugreifen  
  my $dsn = "DBI:$Datenbank{'driver'}:$Datenbank{'dbname'}:$Datenbank{'host'}";
  my $dbh = DBI->connect($dsn, $Datenbank{'user'}, $Datenbank{'pass'});
  print STDERR "Error: $DBI::errstr\n"  unless $dbh;
  return $dbh;
  }

$dbh = open_dbase( dbname => 'lager' );
$dbh = open_dbase( dbname => 'lager', pw => 'secret' );

Will man sehen, was beim Datenbankzugtiff geschieht, kann noch die Zeile

$dbh->trace(LEVEL);

Sind alle Tabellen angelegt, kann die Datenbank mit Informationen gefüllt werden. Zuvor sollen aber noch zwei nützliche Hilfsdienste vorgestellt werden. Eine Liste der Tabelle bekommt man mit der Zeile:

my @tables = $dbh->func('_ListTables');

Eine Beschreibung einer Tabelle $table bekommt man mit:

my $sth = $dbh->prepare("DESC $table");
$sth->execute();
die "$DBI::errstr\n" if ($DBI::err);
... wie unten gezeigt auslesen

5.3 Die Datenbank füttern

$dbh->do("INSERT INTO fahrzeuge (kennz, sitze) VALUES ('M-AX 007','8') ")
            or die $dbh->errstr();

my $sitze = 8;
my $kennz  = "M-AX 007";

my $kennz_q = $dbh->quote($kennz);

$dbh->do("INSERT INTO fahrzeuge (kennz, sitze) VALUES ($kennz_q, $sitze)")
            or die $dbh->errstr();

Will man die geschilderten Ouotierungs-Probleme bei der do()-Methode vermeiden, verwendet man stattdessen eine Kombination aus prepare() und execute(). Grundsätzlich entspricht die Kombination dem do():

my $sth = $dbh->prepare("INSERT INTO fahrzeuge (kennz, sitze) VALUES ('M-AX 007','8')")
          or die $dbh->errstr();
$sth->execute() or die $dbh->errstr();

my $sth = $dbh->prepare("INSERT INTO fahrzeuge (kennz, sitze) VALUES (?,?)")
          or die $dbh->errstr();
$sth->execute($kennz, $sitze) or die $dbh->errstr();

• prepare() liefert ein Statement-Handle $sth zurück.
• execute() nimmt anschließend die Parameterliste entgegen, füllt die Fragezeichen im Statement mit den aktuellen Werten und fügt bei Bedarf automatisch Quotes ein und verhinder "SQL-Injection" (siehe später).

use warnings;
use strict;
use DBI;

my $USER     = "root";
my $PASSWORD = "";

my $dbh = DBI->connect("DBI:mysql:database=bring_m_backalive;" .
                       "host=localhost",$USER, $PASSWORD,
                       {PrintError => 0,
                        AutoCommit => 1}
                       );

my @fuhrpark = (
    [ "M-AX 007", 8 ],
    [ "M-N 1718", 4 ],
    [ "M-OP 471", 4 ],
    [ "M-DD 313", 2 ],
    [ "M-IX 707", 2 ],
    );

my $sth = $dbh->prepare("INSERT INTO fahrzeuge (kennz, sitze)
                         VALUES (?,?)") or die $dbh->errstr();

for (@fuhrpark)
  {
  my($kennz, $sitze) = @$_;
  $sth->execute($kennz, $sitze) or die $dbh->errstr();
  }

$dbh->disconnect();

$dbh->do("DELETE FROM fahrzeuge WHERE sitze = '2'")
       or die $dbh->errstr();

Der SQL-"UPDATE"-Befehl verändert Spaltenwerte bestimmter Datensätze. Er ist immer dann anzuwenden, wenn ein Datensatz schon existiert und sich nur einzelne Ausprägungen ändern sollen. Ändert sich beispielsweise das Kennzeichen der Limousine M-AX 007 nach MA-X 008, erledigt das folgende Programmstück die Korrektur:

$dbh->do("UPDATE fahrzeuge SET kennz = 'MA-X 008' WHERE kennz = 'M-AX 4712'")
          or die $dbh->errstr();

5.4 Daten auslesen mit SELECT

[ [z1s1, z1s2, ...],
  [z2s2, z2s2, ...],
  ...,
]

my $aref = $dbh->selectall_arrayref("SELECT * FROM fahrzeuge")
               or die $dbh->errstr();

for my $row (@$aref)
  {
  my($id, $kennz, $sitze) = @$row;
  printf "%02d %-10s %d\n", $id, $kennz, $sitze;
  }

Selektiert die Datenbank-Abfrage nur eine einzige Zeile, geht es auch einfacher. Um die Zahl der Sitze des Fahrzeugs M-AX 007 herauszufinden, holt selectrow_array() die Daten der ersten passenden Zeile ein:

my ($ID, $sitze) = $dbh->selectrow_array
                    ("SELECT ID, sitze FROM fahrzeuge WHERE kennz = 'M-AX 007'")
                   or die $dbh->errstr();
print "$ID $sitze\n";

my ($count) = $dbh->selectrow_array("SELECT count(*) FROM fahrzeuge");
die $dbh->errstr() unless defined $count;
print "$count Einträge in der Tabelle.\n";

Bei vielen Ergebnis-Datensätzen muß man nicht alle auf einmal in ein Array oder Hash einzulesen. Stattdessen geht man Satz für Satz vor. Die zuvor benutzte Methode selectall_arrayref() ist eigentlich eine Zusammenfassung dreier Befehle: prepare() bereitet eine SQL-Query vor und liefert eine Objektreferenz zurück. Per execute() wird die Query an die Datenbank geschickt, von der die fetchrow_array()-Methode das Ergebnis zeilenweise abholt und jeweils eine Liste der Spaltenwerte zurückgibt:

my $sth = $dbh->prepare("SELECT * FROM fahrzeuge WHERE kennz LIKE 'M-%'")
          or die $dbh->errstr();
$sth->execute() or die $dbh->errstr;

while (my($id, $kennz, $sitze) = $sth->fetchrow_array())
  {
  print("$id, $kennz, $sitze\n");
  }

Mit selectrow_hashref() erhält man die Ergebnisse in einem Hash, der den Spalten-Namen die Werte zuweist. Das folgende Beispiel selektiert alle Mietwagen mit 4 bis 8 Sitzen.

my $sth = $dbh->prepare
            ("SELECT * FROM fahrzeuge WHERE sitze >= 4 AND sitze <= 8")
          or die $dbh->errstr();
$sth->execute() or die $dbh->errstr;

while (my $h = $sth->fetchrow_hashref())
  {
   print "$h->{kennz} $h->{sitze}\n";
  }
die $dbh->errstr() if $dbh->err();

5.5 Fehler abfangen

$dbh->methode(...) or die "Fehler: ", $dbh->errstr();

my $dbh = DBI->connect(@DSN,
    { RaiseError => 1,
      AutoCommit => 1,
    };

$dbh->do("...");
$dbh->do("...");
# ...

eval
  {
  $dbh = DBI->connect(@DSN,
    { RaiseError => 1,
      AutoCommit => 1,
    };


    $dbh->do(...);
    $dbh->do(...);
  };

print "Fehler: $@\n" if($@);

5.6 SQL-Injection

$sql = "select count(*) from Users where Username='" . $User .
       ' AND Password='" $Pass "';"

select count(*) from users where Username='admin' AND Password='geheim';

select count(*) from users where
    Username='' OR '1'='1' AND
    Password='' OR '1'='1';

Aber es geht noch besser! Wie wär es mit dem Benutzernamen '; DROP TABLE users; --? Das ergibt die SQL-Abfrage:

select count(*) from users where Username='';
      DROP TABLE users; --' AND Password='';

select count(*) from users where Username='';
   insert into Users values('Joe','topsecret'); --' AND Password='';

SQL-Injection ist also das Einfügen von beliebigen SQL-Befehlen in Formulare, die dann am Server ausgeführt werden. Warum passiert das? Weil die Formulare dem Input der Benutzer vertrauen, und ihn nicht entsprechend validieren! Generell sind alle Daten, die von außen kommen, "böse". Die Validierung erfolgt mit Stringfunktionen, Regulären Ausdrücken, Plausbilitäts-Checks usw.

Haben Sie den Input auf Gültigkeit abgeklopft, sollten Sie nicht den Fehler machen, trotzdem die alten, fehleranfälligen SQL-Konstrukte weiterzuverwenden. Sie könnten ja ein Angriffs-Szenario vergessen haben. Für die Umstellung bieten sich parametrisierte Kommandos an. Generell sieht ein solches Kommando für das Loginformular aus, wie wir es schon bei der Dateneingabe gesehen haben:

SELECT COUNT(*) FROM Users WHERE Username=? AND Password=?

check($User);  # Validierung
check($Pass);  # Validierung
$sth = $dbh->prepare("SELECT COUNT(*) FROM Users WHERE Username=? AND Password=?")
          or die $dbh->errstr();
$sth->execute($User, $Pass) or die $dbh->errstr();

Auch völlig ohne Validierung funktioniert hier keinerlei SQL-Injection mehr (vergessen Sie die Idee gleich wieder, All input is evil, until proven otherwise!). Der gezeigte Code macht etwas mehr Aufwand als das einfache Zusammenbauen eines SQL-Statements. Allerdings ersparen Sie sich einiges an Kopfschmerzen. Und er funktioniert nicht nur für SELECT, sondern genauso für INSERT, UPDATE oder DELETE.

Schlussbemerkung: In einer Forumsanfrage fand ich das folgende Statement (gekürzt), das in PHP formuliert war (hätte aber auch Perl sein können):

$sql = "SELECT ... FROM ... WHERE Bezeichnung LIKE '%" . $_POST["suchtext"] . "%'";

5.7 Übersicht: Portable DBI-Methoden

Die portablen Perl-Methoden werden im Folgenden etwas genauer erläutert. Die Variablen für die zurückgegebenen Werte haben folgende Bedeutung:

$dbh	Datenbank-Handle
$sth	Statement-Handle
$rc	Rückgabe-Code (Status)
$rv	Rückgabewert (Status)

connect($datenquelle, $benutzername, $passwort)

Benutzen Sie die connect-Methode, um eine Verbindung zur Datenbank der Datenquelle herzustellen. Der $datenquelle-Wert sollte mit DBI:Treiber_name: beginnen. Beispielanwendungen von connect mit dem DBD::mysql Treiber:

$dbh = DBI->connect("DBI:mysql:$datenbank", $benutzer, $passwort);
$dbh = DBI->connect("DBI:mysql:$datenbank:$hostname",
                    $benutzer, $passwort);
$dbh = DBI->connect("DBI:mysql:$datenbank:$hostname:$port",
                    $benutzer, $passwort);

Wenn der Benutzername und/oder das Passwort nicht angegeben werden, verwendet DBI die Werte der DBI_USER- und DBI_PASS- Umgebungsvariablen. Wenn Sie keinen Hostnamen angeben, wird 'localhost' verwendet. Wenn Sie keine Portnummer angeben, wird der MySQL-Port verwendet. Modifikatoren:

mysql_read_default_file=datei	Liest `datei' als eine Optionsdatei.
mysql_read_default_group=group_name	Beim Lesen einer Optionsdatei ist die Standardgruppe normalerweise die[client]-Gruppe. Wenn Sie die mysql_read_default_group- Option angeben, wird die Standardgruppe [gruppenname].
mysql_compression=1	Aktiviert die Kompression während der Kommunikation zwischen Client und Server.
mysql_socket=/pfad/zur/socket	Gibt den Pfad des Unix-Sockets an, der zum Verbinden mit dem Ser verwendet wird.

Sie können mehrere Modifikatoren angeben, dabei muss jedem ein Semikolon vorangestellt sein. Wenn Sie zum Beispiel vermeiden wollen, dass sie Benutzername und Passwort im DBI-Skript angeben müssen, können Sie sie aus der Optionsdatei '~/.my.cnf' nehmen. Ihr connect-Aufruf sieht folgendermaßen aus:

$dbh = DBI->connect("DBI:mysql:$datenbank"
               . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
                $benutzer, $passwort);

Dieser Aufruf liest die Optionen für die [client]-Gruppe aus der Optionsdatei. Wenn Sie dasselbe für die [perl]-Gruppe tun wollen, könnte Ihr Code so aussehen:

$dbh = DBI->connect("DBI:mysql:$Datenbank"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
                . ";mysql_read_default_group=perl",
                $benutzer, $passwort);

disconnect

Die disconnect-Methode beendet die Verbindung mit der Datenbank. Dies wird typischerweise kurz vor dem Ende eines Scripts ausgeführt. Beispiel:

$rc = $dbh->disconnect;

prepare($statement)

Bereitet ein SQL-Statement zum Ausführen durch den Datenbankserver vor und gibt ein "Statement-Handle" ($sth) zurück, mit der Sie die execute-Methode aufrufen. Normalerweise werden Sie SELECT-Statements (und SELECT-ähnliche Statements so wie SHOW, DESCRIBE und EXPLAIN) mit der Bedeutung von prepare und execute verwenden. Beispiel:

$sth = $dbh->prepare($statement)
    or die "$statement: $dbh->errstr kann nicht vorbereitet werden\n";

execute

Die execute-Methode führt ein vorbereitetes Statement aus. Bei Nicht-SELECT-Statements gibt execute die Anzahl der betroffenen Zeilen zurück. Wenn Zeilen betroffen sind, gibt execute "0E0" zurück, was in Perl als 0 und true erkannt wird. Wenn ein Fehler auftritt, gibt execute undef zurück. Bei SELECT-Statements beginnt execute die SQL-Anfrage in der Datenbank; Sie müssen eine der fetch_*-Methoden nutzen, die weiter unten beschrieben sind, um Daten erhalten. Beispiel:

$rv = $sth->execute
          or die "Die Query: $sth->errstr kann nicht ausgeführt werden.";

do($statement)

Die do-Methode bereitet ein Statement vor, führt es aus und gibt die Anzahl der betroffenen Zeilen zurück. Wenn Zeilen betroffen sind, gibt execute "0E0" zurück, was in Perl als 0 und true erkannt wird. Diese Methode wird normalerweise verwendet, um Nicht-SELECT-Statements zu bearbeiten, die (z. B. wegen Treiber-Beschränkungen) nicht vorbereitet werden können, oder die nicht mehr als einmal vorbereitet werden müssen (INSERTS, DELETE usw.). Beispiel:

$rv = $dbh->do($statement)
        or die "$statement: $dbh- >errstr kann nicht vorbereitet werden\n";

Im Allgemeinen ist die do-Methode VIEL schneller (und vorzuziehen) als die prepare/execute-Methoden, die ohne Parameter aufgerufen werden.

quote($string)

Die quote-Methode wird verwendet, um Sonderzeichen zu "escapen", die in Zeichenketten enthalten sein können, und um notwendige äußere Anführungszeichen hinzuzufügen. Beispiel:

$rv = $dbh->quote($string)

fetchrow_array

Die Methode holt die nächste Datenzeile und gibt sie als ein Array mit den Feldwerten zurück. Beispiel:

while(@row = $sth->fetchrow_array)
  {
  print "$row[0]  $row[1]  $row[2]\n";
  }

fetchrow_arrayref

Die Methode holt die nächste Datenzeile und gibt sie als eine Referenz auf ein Array mit den Feldwerten zurück. Beispiel:

while($row_ref = $sth->fetchrow_arrayref)
  {
  print "$row_ref->[0]  $row_ref->[1]  $row_ref->[2]\n";
  }

fetchrow_hashref

Diese Methode holt eine Datenzeile und gibt eine Referenz auf einen Hash zurück, der Name-/Wert-Paare enthält. Die Methode ist lange nicht so performant wie das Verwenden von Referenzen auf ein Array, wie weiter oben beschrieben ist. Beispiel:

while($hash_ref = $sth->fetchrow_hashref)
  {
  print "$hash_ref->{vorname}";
  print "$hash_ref->{nachname}";
  print "$hash_ref->{title}\n";
  }

fetchall_arrayref

Diese Methode gibt alle Zeilen eines Ergebnisses einer SQL-Anfrage zurück. Sie gibt eine Referenz auf ein Array mit Referenzen auf Arrays mit den Werten der einzelnen Zeilen zurück. Sie können mit zwei verschachtelten Schleifen auf die Werte zugreifen. Beispiel:

my $table = $sth->fetchall_arrayref
                or die "$sth->errstr\n";
my($i, $j);
for $i ( 0 .. $#{$table} )
  {
  for $j ( 0 .. $#{$table->[$i]} )
    {
    print "$table->[$i][$j]  ";
    }
  print "\n";
  }

finish

Bewirkt, dass keine weiteren Daten von dem SQL-Anfrageergebnis geholt werden. Sie können diese Methode aufrufen, um Systemressourcen freizugeben. Beispiel:

$rc = $sth->finish;

rows

Gibt die Anzahl der veränderten Zeilen (die aktualisiert oder gelöscht wurden) des letzten Befehls zurück. Dies wird normalerweise nach Nicht-SELECT-execute-Statements verwendet. Beispiel:

$rv = $sth->rows;

NULLABLE

Gibt eine Referenz auf ein Array mit Boole'schen Werten zurück; für jedes Element TRUE kann die Spalte NULL-Werte enthalten. Beispiel:

$rv = $sth->{NULLABLE};

NUM_OF_FIELDS

Dieses Attribut enthält die Anzahl der Zeilen, die eine SELECT- oder SHOW FIELDS-SQL-Anfrage zurückgibt. Sie können es verwenden, um zu prüfen, ob eine Anfrage ein Ergebnis zurückgegeben hat: 0 weist auf eine Nicht-SELECT-Anfrage hin, wie INSERT, DELETE oder UPDATE. Beispiel:

$rv = $sth->{NUM_OF_FIELDS};

datasource($Treiber_name)

Diese Methode gibt einen Array zurück, der die Namen der verfügbaren Datenbanken auf 'localhost' enthält. Beispiel:

@dbs = DBI->datasource("mysql");

ChopBlanks

Dieses Attribut gibt an, ob die fetchrow_*-Methoden vor- und nachstehende Leerzeichen entfernen. Beispiel:

$sth->{'ChopBlanks'} = 1;

trace($trace_ebene)

trace($trace_ebene, $trace_dateiname)

trace aktiviert oder deaktiviert "Tracing". Wenn DBI als eine Klassenmethode aufgerufen wird, steuert es das "Tracing" mit allen Datenbankverbindungen. Wenn es als Datenbank- oder Statement-Handle-Methode aufgerufen wird, steuert es nur die verwendete Verbindung (und deren spätere Ableitungen). Wenn Sie $trace_ebene auf 2 setzen, bewirkt es detaillierte Informationen. Der Wert 0 stellt "Tracing" ab. Die Ausgabe des "Tracing" wird vorgabemäßig nach "standard error" geleitet. Wenn $trace_dateiname angegeben ist, wird die Ausgabe für alle "getraceten" Verbindungen an das Ende dieser Datei geschrieben. Beispiel:

DBI->trace(2);                # alles tracen
DBI->trace(2,"/tmp/dbi.out"); # alles nach /tmp/dbi.out tracen
$dth->trace(2);               # diese Datenbankverbindung tracen
$sth->trace(2);               # dieses Statement-Handle tracen.

Sie können DBI-Tracing auch anschalten, indem Sie die DBI_TRACE-Umgebungsvariable setzen. Wenn Sie sie auf einen numerischen Wert setzen, ist das dasselbe, wie DBI->(wert) aufzurufen. Wenn Sie sie auf einen Pfadnamen setzen, ist das dasselbe, wie DBI->(2,wert) aufzurufen.

5.8 Übersicht: MySQL-spezifische DBI-Methoden

insertid

Wenn das Feature AUTO_INCREMENT von MySQL verwendet wird, erhält man so den neuen auto-incrementierten Wert. Zum Beispiel:

$neue_id = $sth->{insertid};

Alternativ kann $dbh->{'mysql_insertid'} verwendet werden.

is_blob

Gibt die Referenz auf ein Array mit boole'schen Werten zurück. Jedes Element des Arrays entspricht einer Spalte der Datenbank-Tabelle, der Wert TRUE besagt, dass die entprechende Spalte BLOB-Werte enthät. Zum Beispiel:

$keys = $sth->{is_blob};

is_key

Gibt die Referenz auf ein Array mit boole'schen Werten zurück. Jedes Element des Arrays entspricht einer Spalte der Datenbank-Tabelle, der Wert TRUE besagt, dass die entprechende Spalte einen Schlüssel enthät. Zum Beispiel:

$keys = $sth->{is_key};

is_num

Gibt die Referenz auf ein Array mit boole'schen Werten zurück. Jedes Element des Arrays entspricht einer Spalte der Datenbank-Tabelle, der Wert TRUE besagt, dass die entprechende Spalte Zahlenwerte enthät. Zum Beispiel:

$nmbrs = $sth->{is_num};

is_pri_key

Gibt die Referenz auf ein Array mit boole'schen Werten zurück. Jedes Element des Arrays entspricht einer Spalte der Datenbank-Tabelle, der Wert TRUE markiert diejenigen Spalten mit Primär-Schlüsseln. Zum Beispiel:

$pri_keys = $sth->{is_pri_key};

is_not_null

Gibt die Referenz auf ein Array mit boole'schen Werten zurück. Jedes Element des Arrays entspricht einer Spalte der Datenbank-Tabelle, der Wert FALSE besagt, dass die entprechende Spalte NULL-Werte enthalten kann. Zum Beispiel:

$nulli = $sth->{is_not_null};

Statt is_not_null sollte bevorzugt das NULLABLE-Attribut verwendet werden (siehe weiter oben).

length und max_length

Beide Methoden geben die Referenz auf ein Array mit Längenangaben zurück, die besagen, wie groß die jeweiligen Spalten werden können. length liefert die Größe gemäß der Tabellendefinition. Das von max_length gelieferte Array liefert die aktuellen Maximalwerte. Zum Beispiel:

$lengths = $sth->{length};
$max_lengths = $sth->{max_length};

NAME

Gibt die Referenz auf ein Array mit den Spaltenbezeichnungen zurück. Zum Beispiel:

$names = $sth->{NAME};

table

Gibt die Referenz auf ein Array mit Tabellennamen zurück. Zum Beispiel:

$tables = $sth->{table};

type

Gibt die Referenz auf ein Array mit SPaltentype zurück. Zum Beispiel:

$types = $sth->{type};

5.9 MySQL-Datentypen

In der folgenden Aufstellung finden Sie die verschiedenen Datentypen von MySQL:

Numerische Datentypen

Zeichenketten-Datentypen

Hinweise: "BLOB" ist die Abkürzung für "Binary Large Object", z. B. für Fotos oder Musikdateien.

Ein String ist eine Zeichenfolge, die entweder von einfachen Anführungszeichen '...') oder Gänsefüsschen ("...") eingeschlossen wird. Innerhalb eines Strings gibt es (ähnlich wie bei Programmiersprachen) Zeichensequenzen mit spezieller Bedeutung. Jede dieser Sequenzen wird mit einem Backslash (\)als escape character eingeleitet. MySQL kernn die folgenden Spezialzeichen:

\0

ASCII 0 (NUL)

\n

Newline (Zeilenvorschub, line feed)

\t

Horizontaltabulator

\r

Zum Zeilenanfang, (Carriage Return)

\b

Backspace

\'

einfaches Anführungszeichen (')

\"

Gänsefüsschen(")

\\

Backslash

\%

Prozentzeichen. Die Sequenz wird an den Stellen verwendet, wo das Prozentzeichen spezielle Bedeutung hat (z. B. als Jokerzeichen).

\_

Unterstrich. Die Sequenz wird an den Stellen verwendet, wo der Unterstrich spezielle Bedeutung hat.

Wenn Binärdaten in einem BLOB gespeichert werden sollen, müssen auf jeden Fall die folgenden Zeichen durch die entsprechenden Escape-Strings "maskiert" werden:

• ASCII 0 ersetzen durch \0
• ASCII 92, backslash ersetzen durch \\.
• ASCII 39, single quote ersetzen durch \'.
• ASCII 34, double quote ersetzen durch \".

Datums- und Uhrzeit-Datentypen

Hinweis: Der Datentyp TIMESTAMP kann dazu benutzt werden, INSERT- oder UPDATE-Operationen mit dem aktuellen Zeitstempel zu versehen. Bei mehrfacher Verwendung diese Datentypes in einem Datensatz wird nur das erste TIMESTAMP-Feld automatisch aktualisiert, wenn folgenden Bedingungen erfüllt sind:

• Das Feld wird bei einem INSERT- oder LOAD DATA INFILE-Statement nicht explizit mit angegeben.
• Das Feld wird in einem UPDATE-Statement nicht explizit angegeben, und es wird mindestens ein Feld in seinem Wert geändert.
• Wenn das TIMESTAMP-Feld explizit auf NULL gesetzen wird.

Spaltentyp      Anzeigeformat
TIMESTAMP(14)   YYYYMMDDHHMMSS
TIMESTAMP(12)   YYMMDDHHMMSS
TIMESTAMP(10)   YYMMDDHHMM
TIMESTAMP(8)    YYYYMMDD
TIMESTAMP(6)    YYMMDD
TIMESTAMP(4)    YYMM
TIMESTAMP(2)    YY

5.10 SELECT und WHERE

SELECT
    [rechenoperation|funktionsauswahl]
  spaltenname
  FROM tabelle
    [WHERE bedinungen]
    [GROUP BY spalten]
    [HAVING aggregatfunktionen]
    [ORDER BY spalte(n) [ASC] [DESC]
    [LIMIT [Anfang][Zeilenanzahl]]

Sortierung

Beispiel: Sortieren nach Spalten

SELECT 
  nachname, vorname  
  FROM person
  ORDER BY nachname;

SELECT
  nachname, vorname 
  FROM person
  ORDER BY nachname DESC, vorname;

Limitierung

Bedingungen setzen mit WHERE

Operator	Funktion
=	gleich
<> oder !=	ungleich
<	kleiner als
>	größer als
<=	kleiner gleich
>=	größer gleich
BETWEEN min AND max	im Intervall [min,max]
IS NULL	ist Nullwert (Wert nicht vorhanden)
IS NOT NULL	ist kein Nullwert (Wert vorhanden)
IN (Liste)	ist in der Werteliste enthalten Listenwerte sind Konstanten
NOT Bed	Negation der Bedingung
Bed1 OR Bed2	ODER-Verknüpfung zweier Bedingungen
Bed1 AND Bed2	UND-Verknüpfung zweier Bedingungen

Die Vergleichsausdrücke lassen sich nicht nur beliebig mit NOT, OR und AND verknüpfen, sondern es ist auch die Klammerung solcher Ausdrücke gestattet.

Beachten Sie auch, dass Strings in ".." bzw. '..' eingeschlossen werden müssen. Am besten setzen Sie die rechte Seite eines Vergleichs immer in Anführungszeichen.

Beispiele: Es werden nur Personen selektiert, deren Id 641, 642, 643, 644, 741, 742, 743 oder 744 ist.

SELECT Id, nachname, vorname FROM person
WHERE (Id>640 and Id<645) OR Id BETWEEN 741 AND 744; 

SELECT Id, nachname, vorname FROM person
WHERE eintrittsdatum > '1980-01-31'; 

SELECT Id, nachname, vorname FROM person
WHERE lieblingsfarbe = 'schwarz'
OR (geburtsdatum >= '1970-01-01'
    AND geburtsdatum <= '1970-12-31');

• _ steht für ein beliebiges Zeichen
• % steht für beliebig viele beliebige Zeichen, auch 0 Zeichen

SELECT Id, nachname, vorname FROM person
WHERE nachname LIKE 'M__er';

MySQL-Abfragen setzen exakte Einhaltung der Syntax voraus. Ganz anders verhalten sich dagegen Anfragen, die man beispielsweise bei Google eingibt. Da genügt es, einfach die Suchbegriffe durch Leerzeichen getrennt einzutippen und Google macht automatisch eine UND-Verknüpfung draus. Trennt man die Begriffe durch Kommata, wird ODER-verknüpft usw. Das folgende Perl-Script setzt solch vereinfachte Anfragen in korrekte MySQL-Syntax um. Dabei sind folgende Eingabemöglichkeiten realisiert:

• Kommata bzw. 'or' zwischen zwei Worten stellen eine ODER-Verknüpfung dar,
• '+' bzw. 'and' zwischen zwei Worten stellen eine UND-Verknüpfung dar,
• '!' bzw. 'not' ist der Negationsoperator
• Ausdrücke lassen sich klammern (Vorrang)
• '*' kann als Jokerzeichen verwendet werden
• es gibt keine Vorrangregeln, ggf. sind Klammern zu setzen

sub ParseQuery
  {
  my $qcmd = "SELECT * FROM xxx WHERE ";  # Anfang des Ergebnis-Strings
  my($query) = @_;                        # Abfrage-String (Parameter)
  my($part, $first);                      # Hilfsvariablen
  $query =~ s|^/|\\/|;
  # $query =~ tr/A-Z/a-z/;
  $query =~ s|\(| ( |g;                   # Klammern separat stellen
  $query =~ s|\)| ) |g;
  $query =~ s|["'`]||g;                   # Quotes loeschen
  $query =~ s|\+| and |g;                 # erlaubt +wort +wort ... fuer 'AND'
  $query =~ s|,| or |g;                   # erlaubt wort,wort, ... fuer 'OR'
  $query =~ s|!| not |g;                  # erlaubt Negation
  $query =~ s|\*|\%|g;                    # erlaubt '*' als Jokerzeichen

  $first = 1;                             # 'NOT' (am Anfang) oder 'AND NOT' (woanders)
  for (split(/[ \t]+/, $query))           # for each "word", do ...
    {
    next if /^$/;                         # Leerzeile
    if ($_ eq ")")   { $qcmd .= $_; next; }
    if ($_ eq "(")   { $qcmd .= "$part$_"; $part = ""; $first = 1; next; }
    if ($_ eq "not") { $part = ($first)?" NOT ":" AND NOT "; next; }
    if ($_ eq "or" ) { $part = " OR "; next; }
    if ($_ eq "and") { $part = " AND "; next; }
    if ($_ =~ /\%/)                       # Jokerzeichen
      { $qcmd .= "$part KEY LIKE '$_'"; }
    else
      { $qcmd .= "$part KEY='$_'"; }
    $part = " AND ";                      # default: AND Verknuepfungen
    $first = 0;
    }
  $qcmd .= ";";                           # Semikolon am Schluss
  return $qcmd;
  }

print ParseQuery("foo bar foobar"),"\n";
print ParseQuery("foo, bar, foobar"),"\n";
print ParseQuery("bla +foo,bar nix"),"\n";
print ParseQuery("foo and not bar"),"\n";
print ParseQuery("TEST and not (dies and das) or (not tit* and *tat)"),"\n";

SELECT * FROM xxx WHERE  KEY='foo' AND  KEY='bar' AND  KEY='foobar';
SELECT * FROM xxx WHERE  KEY='foo' OR  KEY='bar' OR  KEY='foobar';
SELECT * FROM xxx WHERE  KEY='bla' AND  KEY='foo' OR  KEY='bar' AND  KEY='nix';
SELECT * FROM xxx WHERE  KEY='foo' AND NOT  KEY='bar';
SELECT * FROM xxx WHERE  KEY='TEST' AND NOT ( KEY='dies' AND  KEY='das') 
   OR ( NOT  KEY LIKE 'tit%' AND  KEY LIKE '%tat');

Aggregatfunktionen

Funktion	Aufgabe
COUNT(*)	Anzahl (Nummer, String, Datum)
MIN (Spalte)	Minimum (Nummer, String, Datum)
MAX (Spalte)	Maximum (Nummer, String, Datum)
SUM (Spalte)	Summe (Nummer)
AVG (Spalte)	Durchschnitt (Nummer)
STD (Spalte)	Standardabweichung (Nummer)

Beispiele:
Durchschnitt des Gehalts

SELECT AVG(gehalt) FROM person;

SELECT COUNT(*) FROM person 
WHERE familienstand='ledig';

SELECT COUNT(*), SUM(gehalt) FROM person
WHERE familienstand='ledig';

Datumsfunktionen

Funktion	Ergebnis
NOW()	Heute, Jetzt im Timestamp-Format
CURDATE()	Heute als Datum
CURTIME()	Jetzt als Uhrzeit
WEEKDAY(datum)	0–6 (Montag bis Sonntag)
DAYNAME(datum)	Sunday–Saturday
WEEK(datum)	Kalenderwoche
WEEK(datum,n)	Kalenderwoche (Wochenbeginn am Tag n (So=0))
MONTH(datum)	Monatswert des Datums
QUARTER(datum)	Quartal des Datums (1 bis 4)
YEAR(datum)	Jahrewert des Datums
DAYOFWEEK(datum)	1-7 (Sonntag bis Samstag)
DAYOFMONTH(datum)	Tag des Monats
DAYOFYEAR(datum)	Tag des Datums ab dem 1.1. des Jahres
TO_DAYS(datum)	Anzahl Tage vom Jahr 0 bis heute
FROM_DAYS(n)	Datum zu einer Anzahl Tage vom Jahr 0 an
SECOND(zeit)	Sekunden aus der Uhrzeit
MINUTE(zeit)	Minuten aus der Uhrzeit
HOUR(zeit)	Stunden aus der Uhrzeit
DATE_ADD(date,INTERVAL expr typ)	Zeitintervall zum Datum addieren
DATE_SUB(date,INTERVAL expr typ)	Zeitintervall vom Datum subtrahieren

Anmerkung: Bei DATE_ADD() und DATE_SUB() gibt expr einen Ausdruck an, der zu addieren bzw. subtrahieren ist und typ den Datentyp (SECOND, MINUTE, HOUR, DAY usw.).

Beispiele:
Alle Personen, die im Jahr 1980 geboren sind

SELECT Id, nachname, vorname FROM person
WHERE YEAR(geburtsdatum)=1980;

SELECT Id, nachname, vorname FROM person
WHERE WEEKDAY(geburtsdatum)=6;

SELECT Id, nachname, vorname FROM person
WHERE YEAR(geburtsdatum) >= 1980
  AND YEAR(geburtsdatum) <= 1990;

SELECT Id, nachname, vorname FROM person
  TO_DAYS(CURDATE()) - TO_DAYS( geburtsdatum ) 
FROM person

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
→ 1998-01-01 00:00:00

SELECT INTERVAL 1 DAY + "2007-12-31";
→ 2008-01-01

SELECT "2008-01-01" - INTERVAL 1 SECOND;
→ 2007-12-31 23:59:59 

SELECT DATE_ADD("2007-12-31 23:59:59", INTERVAL 1 SECOND);
→ 2008-01-01 00:00:00

SELECT DATE_ADD("2007-12-31 23:59:59", INTERVAL 1 DAY);
→ 2008-01-01 23:59:59

SELECT DATE_ADD("2007-12-31 23:59:59", INTERVAL 2 MINUTE);
→ 2008-01-01 00:01:59

SELECT DATE_ADD("2008-01-01 00:00:00", INTERVAL -1 DAY);
→ 2007-12-30 00:00:00

SELECT DATE_SUB("2008-01-02", INTERVAL 31 DAY);
→ 2007-12-02

Mathematikfunktionen

ABS(X)

Absolutwert von X.

SIGN(X)

Signumsfunktion. Rückgabewert -1 (X negativ), 0 (X = 0) oder 1 (X positiv).

MOD(N,M)

Modulo-Funktion: Ergebnis von N mod M (bzw. N % M).

FLOOR(X)

Liefert die größte ganze Zahl < X. Beispiel: FLOOR(1.3) ergibt 1, FLOOR(-1.3) ergibt -2. Der Rückgabewert ist vom Typ BIGINT.

CEILING(X)

Liefert die kleinste ganze Zahl ≥ X. Beispiel: CEILING(1.3) ergibt 2, CEILING(-1.3) ergibt -1. Der Rückgabewert ist vom Typ BIGINT.

ROUND(X)

Liefert die Rundung der Dezimalzahl X auf eine ganze Zahl. Der Rückgabewert ist vom Typ BIGINT.

ROUND(X,D)

"Rundet" X auf eine Dezimalzahl mit D Nachkommastellen. Für D = 0 entspricht diese Funktion dem ROUND(X). Der Rückgabewert ist vom Typ BIGINT.

TRUNCATE(X,D)

Schneidet die Dezimalzahl X auf D Nachkommastellen ab.

EXP(X)

Liefert e^X.

LOG(X)

Liefert den natürlichen Logarithmus von X (Basis e).

LOG10(X)

Liefert den Logarithmus von X zur Basis 10.

POW(X,Y) bzw. POWER(X,Y)

Liefert X^Y.

SQRT(X)

Liefert die Wurzel aus X.

LEAST(X,Y,...)

Liefert den kleinsten Wert der Argumentliste, wobei folgende Regeln gelten:

• Im Ganzzahlkontext bzw. wenn alle Argumente ganze Zahlen sind, erfolgt ein Vergleich ganzer Zahlen.
• Im Real-Kontext bzw. wenn alle Argumente Real-Werte sind, erfolgt ein Vergleich von Real-Zahlen.
• In allen anderen Fällen, erfolgt ein String-Vergleich.

GREATEST(X,Y,...)

GREATEST(X,Y,...) Liefert den größten Wert der Argumentliste, wobei dieselben Regeln wie bei LEAST() gelten.

PI()

Gibt den Wert der Kreiszahl Pi zurück.

SIN(X)

Liefert den Wert des Sinus von X (X im Bogenmass).

COS(X)

Liefert den Wert des Cosinus von X (X im Bogenmass).

TAN(X)

Liefert den Wert des Tangens von X (X im Bogenmass).

ACOS(X)

Liefert den Wert des Arcuscosinus von X (X im Bogenmass). Der Rückgabewert ist NULL, falls X nicht im Intervall [-1 , 1] liegt.

ASIN(X)

Liefert den Wert des Arcuscosinus von X (X im Bogenmass). Der Rückgabewert ist NULL, falls X nicht im Intervall [-1 , 1] liegt.

ATAN(X)

Liefert den Wert des Arcustangens von X (X im Bogenmass).

ATAN2(X,Y)

Liefert den Wert des Arcustangens von Y/X (X, Y im Bogenmass) wobei der Quadrant aus den Vorzeichen von X und Y ermittelt wird.

COT(X)

Liefert den Wert des Cotangens von X (X im Bogenmass).

DEGREES(X)

Konvertiert X vom Bogenmass in einen Winkel.

RADIANS(X)

Konvertiert X von Grad ins Bogenmass.

RAND() bzw. RAND(N)

Liefert eine Zufallszahl zwischen 0 und 1.0. Ist ein Parameter N angegeben, wird dieser für die Generierung der Start-Zufallszahl verwendet (Seed). RAND() kann ncht in einem ORDER BY-Ausdruck verwendet werden, weil RAND() ja für jeden Datensatz aufgerufen würde. Jedoch kann mittels SELECT * FROM table_name ORDER BY RAND() ein Datensatz zufällig ausgewählt werden, z. B.:

SELECT * FROM table1,table2 WHERE a=b AND c=d ORDER BY RAND() LIMIT 1000

CONV(X,B1,B2)

Konvertiert einen Integer-Zahlenwert X zur Basis B1 in einen Zahlenwert zur Basis B2. Das Ergebnis wird als String zurückgegeben. Gibt NULL zurück, falls eines der Argumente NULL ist. B1 und B2 müssen zwischen 2 und 36 liegen. Ist B2 negativ, wird X als vorzeichenbehaftete Zahl interpretiert, sonst als positive Zahl.

BIN(X)

Gibt einen String zurück, der die Binärdarstellung der Zahl X (BIGINT) enthält. Gibt NULL zurück, falls X NULL ist.

OCT(X)

Gibt einen String zurück, der die Oktaldarstellung der Zahl X (BIGINT) enthält. Gibt NULL zurück, falls X NULL ist.

HEX(X)

Gibt einen String zurück, der die Oktaldarstellung der Zahl X (BIGINT) enthält. Gibt NULL zurück, falls X NULL ist.

Zeichenketten-Funktionen

Bei Funktionen, die mit Stringpositionen arbeiten gilt immer: Der Index des ersten Zeichens ist 1 (und nicht 0 wie bei manchen Programmiersprachen).

Dieser Abschnitt beschreibt nur eine Auswahl der wichtigsten Stringfunktionen von MySQL - gegebenfalls ziehen Sie das Handbuch zu Rate.

CHAR(N,...)

Interpretiert die Argumente als Codewerte (positive Zahlen) und gibt die entsprechenden ASCII-Zeichen als String zurück. Beispiel:

SELECT CHAR(77,121,83,81,76); → 'MySQL'

CONCAT(S1,S2,...)

Verbindet die als Argumente übergebenen Strings zu einem Ergebnisstring (Konkatenation). Gibt NULL zurück, falls eines der Argumente NULL ist. Hauptanwendung ist das Kombinieren von Spalten (SELECT ... CONCAT(name, ' ', vorname) ...).

CHARACTER_LENGTH(S)

Gibt die Länge eines Strings zurück. Synonym verwendbar sind LENGTH(S), OCTET_LENGTH(S) und CHAR_LENGTH(S), wobei die letzte Funktion Zeichen, die aus mehreren ASCII-Zeichen bestehen, nur als ein Zeichen zählt.

LOCATE(Substr, S [,Pos])

Wenn Pos fehlt, wird die Position des ersten Auftretens von Substr im String S zurückgegeben. Ist Substr nicht enthalten, wird 0 retourniert.
Ist Pos vorhanden, beginnt die Suche nach Substr erst bei der durch Pos spezifizierten Position in S. So können mehrfach auftretende Teilstrings gesucht werden.

INSTR(S,Substr)

Wie LOCATE(Substr, S), nur mit vertauschten Argumenten. Vorsicht Falle!

LPAD(S, Len, Padstr)

Ergänzt S von der linken Seite her durch Voranstellen von Padstr, bis die Länge Len erreicht ist, zum Beispiel:

SELECT LPAD('Hallo',9,'??'); → '????Hallo'

RPAD(S, Len, Padstr)

Ergänzt S von der rechten Seite her durch Anhängen des Padstr, bis die Länge Len erreicht ist, zum Beispiel:

SELECT LPAD('Hallo',8,'.'); → 'Hallo...'

LEFT(S, Len)

liefert die linken Len Zeichen von S.

RIGHT(S, Len)

liefert die rechten Len Zeichen von S.

MID(S, Pos, Len)

Liefert einen Len Zeichen langen Teilstring von S, beginnend bei der Position Pos. Beispiel:

SELECT MID('foobar',3,2); → 'ob'
SELECT LEFT('foobar',3);  → 'foo'
SELECT RIGHT('foobar',3); → 'bar'

SUBSTRING(S, Pos, Len)

Arbeitsweise wie MID(S, Pos, Len). Alternative Form: SUBSTRING(S FROM Pos FOR Len).
Fehlt das dritte Argument Len, wird der Substring ab Pos bis zum Ende von S zurückgegeben. Alternative Form: SUBSTRING(S FROM Pos).

SUBSTRING_INDEX(S, Delim, Anz)

Liefert einen Teilstring von S nachdem Anz mal der Begrenzer Delim aufgetreten ist. Ist Anz positiv wird ausgehend vom gefundenen Delimiter der linke Teilstring zurückgegeben, ist Anz negativ entsprechend der rechte Teilstring. Beispiele:

SELECT SUBSTRING_INDEX('www.netzmafia.de', '.', 2);  → 'www.netzmafia'
SELECT SUBSTRING_INDEX('www.netzmafia.de', '.', -2); → 'netzmafia.de'

LTRIM(S)

Entfernt führende Leerzeichen aus S.

RTRIM(S)

Entfernt Leerzeichen am Ende von S.

REPLACE(S, Such_str, Ersatz_str)

Ersetzt im String S alle Auftreten von Such_str

INSERT(S, Pos, Len, Neu)

Ersetzt in S den Substring, der bei Position Pos beginnt und Len Zeichen lang ist durch den String Neu. Beispiel:

SELECT INSERT('Warschau', 2, 5, '...'); → 'W...au'

REVERSE(S)

Dreht den String S um (z. B. '123' → '321').

LCASE(S) bzw. LOWER(S)

Wandelt alle Buchstaben in S in Kleinbuchstaben um.

UCASE(S) bzw. UPPER(S)

Wandelt alle Buchstaben in S in Großbuchstaben um.

LOAD_FILE(Dateiname)

Liest die angegebene Datei (Pfadangabe) ein und speicher sie in einem MySQL-kompatiblen String. Ist die Datei nicht vorhanden/nicht lesbar etc. liefert die Funktion NULL zurück. Dient primär zum Speichern von Binärinfo (Bilder, PDF usw.) in Datensätzen. Zum Beispiel:

UPDATE tabelle_x 
    SET blob_column=LOAD_FILE("/home/plate/bild.jpg")
    WHERE id='110';

Diverse

SELECT menge, artikelnr FROM bestellung 
GROUP BY artikelnr;

SELECT DISTINCT farbe FROM fahrzeuge;

SELECT LAST_INSERT_ID();

select PASSWORD('badpwd'); → '7f84554057dd964b'

Zusammenfassung der wichtigsten Regeln

• Immer Strings in Hochkomma (" .. " oder ' .. ') einschließen (sonst wird die Angabe als Spaltenname aufgefaßt)
• Dezimalstellen mit Punkt statt Komma
• mehrere Bedingungen durch AND oder OR verbinden
• Klammerung von Teilbedingungen möglich
• bei Mustersuche (LIKE) entspricht Zeichen % dem * bei der Shell-Filenamengenerierung und das Zeichen _ dem ?
• DISTINCT: gleiche Zeilen werden nur einmal angezeigt
• Es wurden bei Weitem(!) nicht alle Funktionen behandelt, die MySQL kennt

5.11 JOIN - komplexe Abfragen

Für die folgenden Beispiele stellen wir uns eine Datenbank vor, die einerseits eine Tabelle für die aktuellen Bestellungen hat und andererseits eine Tabelle mit den Stammdaten zu jedem Artikel. Mit SELECT wird nun auf beide Datenbestände zugegriffen, was bedeutet, dass neben dem Spaltennamen auch der Name der Tabelle aufgeführt werden muss, um eventuelle Zweideutigkeiten zu vermeiden. Zum Beispiel brauche ich für die Rechnung nicht nur die Bestelldaten mit Artikenummer, sondern auch die Artikelbezeichnung:

SELECT bestellung.nr, bestellung.menge, bestellung.artikelnr, artikel.bezeichnung
   FROM bestellung, artikel WHERE bestellung.nr='345';

SELECT b.nr, b.menge, b.artikelnr, a.bezeichnung
   FROM bestellung AS b, artikel AS a 
   WHERE b.nr='345';

Der so genannte Cross-Join ist die Grundform aller Tabellenverknüpfungen. Alle weiteren Joins sind lediglich Verfeinerungen oder Erweiterungen davon. Den häufigsten Typ von Join haben Sie eventuell schon einmal verwendet. Wir brauchen Ihn zur Lösen des o. a. Problems. In der Tabelle bestellung ist jeder bestellte Artikel aufgeführt, während die Tabelle artikel Detailinformationen zu jedem Artikel enthält. Wir müssen also neben der Einschränkung auf eine Bestellungs- oder Kundennummer zusätzlich auch die Artikel einschränken - auf jene, deren Artikelnummer die aktuelle Bestellung enthält:

SELECT b.nr, b.menge, b.artikelnr, a.bezeichnung
   FROM bestellung AS b, artikel AS a 
   WHERE b.nr='345' AND b.artikelnr = a.artikelnr;

Der Left-Join erweitert die Ergebnismenge des o. a. Join um jene Datensätze aus der ersten Tabelle, zu denen es keinen passenden Eintrag in der zweiten Tabelle gibt. Alle Feldwerte, die sich auf die zweite Tabelle beziehen, haben in diesem Fall den Wert NULL. Um aus unserer Abfrage einen Left-Join zu machen, formulieren wir eine Abfrage aller Bestellungen:

SELECT b.nr, b.menge, b.artikelnr, a.bezeichnung
   FROM bestellung AS b 
   LEFT JOIN artikel AS a ON b.artikelnr = a.artikelnr;

SELECT b.nr, b.menge, b.artikelnr, a.bezeichnung FROM bestellung AS b 
  LEFT JOIN artikel AS a ON b.artikelnr = a.artikelnr 
    WHERE a.bezeichnung IS NULL;

SELECT b.nr, b.menge FROM bestellung AS b1, bestellung AS b2 
   WHERE b1.artikelnr = b2.artikelnr;

Mit den ab MySQL-Subselects können Joins auch anders formuliert werden. Um die Kunden zu erfassen, die noch nie bestellt haben, schreiben wir als Join:

SELECT kunden.*,bestellung.kundennr FROM kunden 
  LEFT JOIN bestellung
    ON bestellung.kundennr=kunden.kundennr 
      WHERE bestellung.kundennr IS NULL;

SELECT * FROM kunden
  WHERE NOT EXISTS 
    (SELECT * FROM bestellung WHERE bestellung.kundennr=kunden.kundennr)

SELECT * FROM bestellung 
  WHERE artikelnr=
    (SELECT artikelnr FROM artikel WHERE bezeichnung='WLAN-Router');

SELECT * FROM bestellung 
  WHERE artikelnr IN
   (SELECT artikelnr FROM artikel WHERE bezeichnung='WLAN-Router);

Nachtrag - weil es so oft gefragt wird:
MySQL-Passwort für den Datenbank-Root-User zurücksetzen

#Stopp des MySQL-Servers
/etc/init.d/mysql stop

mysql -u root

USE mysql;
UPDATE user SET Password=PASSWORD('NEUESPASSWORT') WHERE User='root'; 
FLUSH PRIVILEGES;
QUIT

Nun wird der immer noch laufende ungeschützte mysqld gestoppt (Kommandos ps und kill) und der reguläre MySQL-Server wieder gestartet:

#Stopp des MySQL-Servers
/etc/init.d/mysql start

Zum vorhergehenden Abschnitt

Zum Inhaltsverzeichnis

Zum nächsten Abschnitt

Copyright © Hochschule München, FK 04, Prof. Jürgen Plate
Letzte Aktualisierung: