Grundlagen CGI-Programmierung mit Perl


von Prof. Jürgen Plate

5 Perl und MySQL

Für den Zugriff auf Datenbanken wird bevorzugt die DBI-Schnittstelle eingesetzt, denn sie bietet von der verwendeten Datenbank unabhängige virtuelle Methoden. DBI besitzt mehrere Treibermodule aus der "DBD::xxxxx"-Familie, die es für alle marktüblichen Datenbanksysteme frei verfügbar auf dem CPAN gibt. So wird der einheitliche Zugriff auf Oracle, Sybase, MySQL, PostgreSQL oder sogar einer Pseudo-Datenbank, die ihre Daten kommasepariert in Testdateien ablegt. Leider verwenden die einzelnen Datenbankprodukte jedoch ihre eigenen SQL-Dialekte. Wer seine Felder automatisch durchnummerieren will oder Datumsfelder und binäre Blöcke verwendet, stößt schnell an die Grenzen des beschränkten SQL-Standards. Darum bleiben wir in diesem Kapitel bei der beliebten Open-Source-Datenbank MySQL. Hier nicht behandelt wird der gesamte Komplex "Datenbanken", also Aufbau, Modellierung etc. und auch nicht die MySQL-Datenbank selbst mit Installation und dem ganzen drumherum. Ebensowenig wird auf die Tiefen der Datenbank-Abfragesprache SQL eingegangen. Hier geht es vornehmlich um die Einbindung der Datenbank in Perl-Programme, obwohl Ihnen eine knappe, grundlegende Einleitung nicht erspart bleibt.

5.1 Datenbank-Grundlagen

Was ist eine Datenbank? Es gibt leider keine präzise und allgemein anerkannte Definition. Die Hauptaufgabe eines Datenbanksystems ist die Beantwortung gewisser Fragen über eine Teilmenge der realen Welt, z. B. "Wie lautet die Adresse von Steven Wozniak?". Das Datenbanksystem dient nur der Speicherung und Wiedergabe von Informationen, die zuerst eingegeben und dann immer aktualisiert werden müssen. Ein Datenbanksystem ist im Grunde nur eine Computerversion eines Karteikastens (nur mächtiger). Normalerweise nehmen Datenbanksysteme auch keine besonders komplizierten Berechnungen mit den gespeicherten Daten vor. Sie können jedoch die gesuchten Daten schnell in einer großen Datenmenge ausfindig machen, und sie können auch Daten aggregieren oder kombinieren, um eine Antwort aus vielen Einzeldaten zusammenzusetzen. Jede Datenbank kann nur Informationen einer vorher definierten Struktur speichern, was den Vorteil komplexer Auswertungen bietet.

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

Die Daten eines Informationssystems können auf verschiedenen Ebenen modelliert werden, die wiederum eine unterschiedliche Sicht auf die Daten der Datenbank bieten. Diese verschiedenen Sichten stellen verschieden hohe Abstraktionsgrade dar. Der Zugriff auf die Daten bezüglich der externen Sicht ist unabhängig davon, wie die Daten auf der konzeptionellen Schicht modelliert wurden. Und dies ist wieder unabhängig von der internen Darstellung der Daten im DBMS: Bleiben wir kurz bei der konzeptionellen Sicht: Man kann bei einer Datenbank unterscheiden zwischen deren Realisierung in Form eines Datenbankschemas, das die formale Definition der Struktur des Datenbankinhalts darstellt. Es wird nur einmal definiert, wenn die Datenbank erstellt wird. In der Praxis kann es manchmal notwendig sein, das Schema zu ändern und an aktuelle Gegebenheiten anzupassen. Das geschieht jedoch recht selten und ist nicht unproblematisch. Der Zustand der Datenbank wird durch die aktuellen Daten repräsentiert, die dem Schema entsprechend strukturiert sind. Er ändert sich oft – jedes Mal wenn Datenbankinformationen geändert werden.

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:

Beim ersten Entwurf werden meist nicht alle der obigen Regeln eingehalten. Deshalb wird die Datenbank mit ihren Tabellen einem Normalisierungsprozess unterzogen, der auf die Einhaltung aller Regeln hinführt – womit wir schon beim Datenbankdesign angekommen wären; siehe nächsten Abschnitt.

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)
In relationalen Datenbanksystemen wie MySQL werden auch Beziehungen auf Relationen abgebildet. Dabei gilt folgende Grundregel für Beziehungstypen: Jede Beziehung wird auf eine Relation abgebildet. Die Relation besteht aus den Primärschlüsseln der beteiligten Objekttypen sowie den Attributen der Beziehung. Zum Beispiel wird die Beziehung "Student belegt Kurs" wird auf die Relation
Belegt (Matrikelnummer, Kursnummer, Punkte)
abgebildet. Der Primärschlüssel dieser Relation besteht aus den beiden Primärschlüsseln der Objekttypen. Die Primärschlüssel Matrikelnummer und Kursnummer sind nur zusammengenommen der Primärschlüssel für die Relation "Belegt". Jeder der beiden Primärschlüssel verweist auf eine andere Relation und wird daher Fremdschlüssel genannt (ein Schlüssel in einer fremden Relation). Das folgende Bild zeigt die Elemente einer relationalen Datenbank:

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

Der NULL-Wert ("Wert derzeit unbekannt" oder "Wert undefiniert" spielt bei relationalen Datenbanken eine wichtige Rolle. Er stellt eine Möglichkeit dar, unvollständige Information (missing values) im relationalen Modell zu repräsentieren, und darf nicht mit der Zahl 0 oder mit dem Leer-String verwechselt werden.

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:

DomainWertebereich
RelationTabelle
AttributSpalte
TupelDatensatz, Rekord
Primary-KeyHauptschlüssel
Alternate-KeyZweitschlüssel
Foreign-KeyFremdschlüssel

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

Datenbank-Entwurf

Der Datenbankentwurf richtet sich nach den oben aufgeführten Kriterien, insbesondere der Redundanzfreiheit und Eindeutigkeit. Da man beim ersten Entwurf sicher nicht alle Punkte korrekt implementiert, kann der gesamte Prozess schrittweise vollzogen werden. Es sind daher verschiedene Normalformen der Relationen bzw. Tabellen definiert worden, die schrittweise zum endgültigen Datenmodell führen. Für die meisten Problemstellungen reichen die ersten drei Normalformen aus.

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:

Die erste Normalform verlangt, dass jede Relation in ihrer atomaren Form vorliegt, d. h. Attribute lassen sich nicht mehr weiter unterteilen. Praktisch wirkt sich das so aus, dass an jeder Zeilen- und Spaltenposition der Tabelle jeweils nur ein Wert vorkommt.

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:

ModellFarbe...Paket 1Paket 2Paket 3
1500rot...X  
1502silber...XX 
1504schwarz...XX 
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:

ModellFarbe...Paket
1500rot...1
1502silber...1
1502silber...2
1504schwarz...1
1504schwarz...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:

ModellFarbe...Paket
1500rot...1
1502silber...1
1502silber...2
1504schwarz...1
1504schwarz...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):

ModellFarbe...
1500rot...
1502silber...
1502silber...
1504schwarz...
1504schwarz...

Dann die Ausstattungstabelle:

PaketAlufelgenLedersitze...
1XX...
2X......
3X......
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):

IdModellPaket
115001
215021
315022
415041
515043

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

Werden Tabellen, die eigenständige Objekte oder "Stammdaten" beschreiben, mit einem zusätzlichen Primärschlüssel definiert (z. B. einer ganzen Zahl, die bei jedem Datensatz automatisch inkrementiert wird), so sind diese Tabellen in der Regel bereits in der 2NF.

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

Versuchen Sie doch einmal, die obige Tabelle sinnvoll zu normalisieren.

5.2 MySQL-Tabellen anlegen

Die Anweisung
#!/usr/bin/perl
use DBI;
bindet das DBI-Modul in ein Perl-Programm ein. Die Schnittstelle zur Datenbank ist damit jedoch noch nicht komplett. Es fehlt noch der spezielle Treiber welcher von DBI verwendet wird, hier DBD::mysql.

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
Bevor es richtig losgeht, muß eine Datenbank angelegt werden. Es versteht sich von selbst, daß man sich vorher Gedanken darüber gemacht hat, wie die Datenbank aussehen soll, z. B. welche Tabellen sie enthalten muß und welche Felder mit welchem Datentyp die Tabellen enthalten. Aber das ist ein anderes Thema. Für unser Beispiel wird eine ganz einfach Datenbank mit nur einer Tabelle verwendet: Der Autoverleih "Bring M. Backalive" unterhält einen Fuhrpark von diversen Fahrzeugen, deren Daten gespeichert werden sollen. Damit es übersichtlich bleibt, lassen wir Typ, Baujahr, Leistung, Kilometerstand, und vieles andere weg - uns reicht das polizeiliche Kennzeichen und die Anzahl der Sitzplätze.

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();
Oben definieren wir in "$USER" und "$PASS" Benutzerkennung und Passwort, unter denen die MySQL-Installation Zugriffe erlaubt. Im Beispiel spezifiziert @dsn (Data Source Name) die Konfigurationsparameter für die verwendete Datenbankinstallation. Der erste Parameter
"DBI:mysql:database=bring_m_backalive;host=localhost"
legt den "DBD::mysql"-Treiber fest; der Name der Datenbank lautet "bring_m_backalive" der Datenbankserver läuft auf "localhost". In der Regel kann man den Prarameter noch abkürzen und
"DBI:mysql:bring_m_backalive;localhost"
schreiben - man muß nur die Reihenfolge einhalten. Gegebenenfalls kann noch die Portnummer anhängen (wieder durch ":" getrennt), sofern diese vom Standardport abweicht. Die weiteren Parameter "$USER" und "$PASS" übernehmen die Benutzerkennung und das Passwort.

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;
  }
Einen kleinen Perl-Trick stellt die Zeile "@_" im Hash %Datenbank dar. Man kann so über die Parameterzeile beim Aufruf die voreingestellten Werte überschreiben, z.B.:
$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);
vor dem "return" eingefügt werden. Als Trace-LEVEL reicht normalerweise der Wert 1. Je höher man geht, desto mehr Informationen erhät man.

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');
Das entspricht dem SQL-Befehle SHOW TABLES.

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

Die neue Stretch-Limousine mit dem Kennzeichen M-AX 007 wird durch das folgende Programmfragment als neuer Datensatz in "fahrzeuge" eingefügt:
$dbh->do("INSERT INTO fahrzeuge (kennz, sitze) VALUES ('M-AX 007','8') ")
            or die $dbh->errstr();
Das SQL-Kommando "INSERT INTO ..." spezifiziert die Spaltennamen in der ersten Klammer und weist ihnen die Werte der zweiten Klammer zu. SQL-Zeichenketten müssen in einfache Anführungszeichen eingeschlossen sein. Enthält der String einfache Quotes, müssen diese mit "\'" ausmaskiert werden. Werden Variableninhalte verwendet, wie beim folgenden Beispiel, müssen die Strings mit Quotes versehen werden:
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();
Dafür stellt DBI eine datenbankspezifische Methode bereit: $dbh->quote. Beispielsweise gibt der Aufruf $dbh->quote("Da gibt's was auf die Ohren!") als Ergebnis 'Da gibt\'s was auf die Ohren!' zurück.

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();
Damit ist aber noch nicht viel gewonnen. Erst wenn man prepare() einen Rumpf des "INSERT"-Befehls mitgibt, in dem alle Werte durch Fragezeichen ersetzt sind, kann man die aktuellen Werte der execute()-Methode mitgeben:
my $sth = $dbh->prepare("INSERT INTO fahrzeuge (kennz, sitze) VALUES (?,?)")
          or die $dbh->errstr();
$sth->execute($kennz, $sitze) or die $dbh->errstr();
Dieses Verfahren bietet sich insbesondere dann an, wenn Sie mehrere Datensätze in einem Durchgang einfügen wollen. Die Trennung zwischen prepare() und execute() bietet dann sogar Performance-Vorteile. Ein einmal vorbereitetes Statement kann immer wieder mit neuen Werten ausgeführt werden:
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();
Auch das SQL-"DELETE"-Kommando kann mit DBI über die "do"-Methode abgesetzt werden. Die "WHERE"-Klausel gibt an, welche Zeilen gelöscht werden sollen. Folgendes Programmstück löscht alle Zweisitzer:
$dbh->do("DELETE FROM fahrzeuge WHERE sitze = '2'")
       or die $dbh->errstr();
Das "DELETE"-Kommando gibt die Anzahl der gelöschten Datensätze zurück. Besitzt Der Verleih keine klassischen Roadster, läuft der Methodenaufruf auf die die()-Anweisung. Ganz korrekt wäre es allerdings, den Rückgabewert von do() einer Variablen zuzuweisen und sie mit defined() daraufhin zu prüfen, ob do() den Wert "undef" zurückgeliefert hat.

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();
Der Annahme folgend, dass der Wagen M-AX 007 auf jeden Fall in der Datenbank ist, geht auch hier die "or"-Bedingung durch. Sie bricht nicht nur bei allgemeinen Fehlern ab, sondern auch, wenn die Datenbank das Kennzeichen nicht findet. Erst ein defined() und dann Testen, ob der Rückgabewert ungleich 0 ist, wäre sauber.

5.4 Daten auslesen mit SELECT

Alle Mietwagen der Tabelle liefert der Befehl "SELECT * FROM fahrzeuge". Doch wie kommt die Ergebnistabelle nach Perl? Die Methode selectall_arrayref() legt jede gelieferte Tabellenzeile in ein Array, erzeugt ein weiteres Array mit Referenzen auf diese Arrays und gibt schließlich eine Referenz darauf zurück. Wenn "z1z1" das Element aus Zeile 1 und Spalte 1 ist, sieht das Ergebnis so aus:
[ [z1s1, z1s2, ...],
  [z2s2, z2s2, ...],
  ...,
]
Auf das Beispiel angewendet nimmt folgendes Programmstück das Ergebnis des "SELECT"-Befehls entgegen und gibt die Daten anschließend als Tabelle formatiert aus:
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";
Auch wenn das Ergebnis nur aus einem einzigen Wert besteht, kann man selectrow_array nutzen. Um die Anzahl der Datensätze zu bestimmen, verwendet man "SELECT COUNT(*) FROM fahrzeuge". Das Ergebnis ist garantiert eine einzige Zeile mit einer einzigen Spalte:
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();
Mehr zu SELECT erfahren Sie weiter unten in diesem Kapitel.

5.5 Fehler abfangen

Ein nicht gestarteter oder fehlerhaft arbeitender Datenbankserver, syntaktisch falsches SQL oder Kommunikationsstörungen lassen die DBI-Methoden auf Fehler laufen, die der Programmierer kontrollieren muß. Alle DBI-Methoden liefern "undef" zurück, falls etwas schief ging. Daher empfiehlt es sich, jeden Methodenaufruf als
$dbh->methode(...) or die "Fehler: ", $dbh->errstr();
zu formulieren, um das Programm abzubrechen, oder den Fehler wenigstens abzufangen, um darauf im Programm zu reagieren. Die Methode $dbh->errstr() gibt im Fehlerfall stets den zugehörigen Text der Fehlermeldung zurück, der Hinweise zur Ursache liefert. Eine Ausnahme: connect() meldet im Erfolgsfall die Objektreferenz für weitere Aufrufe und im Fehlerfall "undef". Die Fehlermeldung ist aber trotzdem über den Skalar "$DBI::errstr" zugänglich. Man kann "DBI" aber auch mittels RaiseError => 1 anweisen, bei jedem auftretenden Fehler sofort zu sterben:
my $dbh = DBI->connect(@DSN,
    { RaiseError => 1,
      AutoCommit => 1,
    };

$dbh->do("...");
$dbh->do("...");
# ...
Um die die-Exception abzufangen, läßt sich dann alles in eine "eval"-Anweisung einbetten. Dann springt Perl sofort aus dem "eval"-Block, sobald irgendwo ein Fehler auftritt, und setzt die Variable "$@" auf den Fehlertext. Auf diesen kann der folgende "if"-Block reagieren.
eval
  {
  $dbh = DBI->connect(@DSN,
    { RaiseError => 1,
      AutoCommit => 1,
    };


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

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

5.6 SQL-Injection

Was ist SQL Injection? Am besten läßt sich das anhand eines Beispiels erklären. Nehmen wir an, beim Zugriff auf eine Webseite ist Autorisierung per Username und Passwort nötig. Die eingegebenen Daten werden durch eine Datenbankabfrage verifiziert. Es ist eigentlich egal, ob die Abfrage direkt oder über irgend ein Programmierinterface abgesetzt wird. An dieser Stelle interessiert nur die SQL-Abfrage ($User und $Pass stammen in den folgenden Beispielen aus dem HTML-Eingabeformular).
$sql = "select count(*) from Users where Username='" . $User .
       ' AND Password='" $Pass "';"
Sieht ganz brauchbar aus, oder? Sehen Sie sich an, wie der SQL-String aussieht, wenn sich der Administrator mit admin / geheim einloggt:
select count(*) from users where Username='admin' AND Password='geheim';
Alles bestens! Was aber würde ein Cracker versuchen? Der gibt beispielsweise für Benutzername und und Passwort jeweils die Zeichenfolge ' OR '1'='1 ein (beachten Sie auch die Apostrophe). Der SQL String sieht dann folgendermaß aus:
select count(*) from users where
    Username='' OR '1'='1' AND
    Password='' OR '1'='1';
Da 1 immer gleich 1 ist, braucht der Bösewicht weder Userkennung noch Passwort, um auf die geschützte Seite zu gelangen. Nur, weil die Benutzereingaben nicht validiert wurden.

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='';
Oha! Da hat Joe Crack doch gerade die Tabelle mit den Benutzeraccounts gelöscht! Die zwei Striche (--) leiten einen Kommentar ein, weswegen der String "AND ..." keinen Fehler ergab. Statt die Usertabelle platt zu machen, kann sich unser schlauer Freund aber auch einen eigenen Account anlegen:
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=?
Der SQL-String wird auch hier nicht mehr dynamisch zusammengebaut. Adaptiert auf das aktuelle Problem ergibt sich:
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();
(Coding of sub check ist left as a punishment to the reader.)

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"] . "%'";
Weiterer Kommentar überflüssig.

5.7 Übersicht: Portable DBI-Methoden

Die portablen DBI-Methoden sind für verschiedene Datenbanksysteme anwendbar und verhalten sich immer (nahezu) gleich. Sie erlauben daher einen späteren Wechsel des darunter liegenden Datenbanksystems.

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

$dbhDatenbank-Handle
$sthStatement-Handle
$rcRückgabe-Code (Status)
$rvRü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

Die folgenden Methoden sind mur unter MySQLO anwendbar und nicht Teil des DBI-Standards. Einige der noch immer existierenden Methden sind auch als "veraltet" bzw. engl. "deprecated" markiert und sollten daher möglichst nicht verwendet werden. Es handelt sich um die Methoden is_blob, is_key, is_num, is_pri_key, is_not_null, length, max_length und table. Wenn Alternativen existieren, sind diese angegeben.

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

MySQL-Felder (Spalten) werden mit Datentypen versehen, um einerseits durch die Bindung an einen Typ die Größe des Feldes an die zu speichernden Datenanzupassen und andererseits eine grundlegende Fehlererkennung zu gewährleisten. Durch die Typisierung steigt zudem die Verarbeitungsgeschwindigkeit. Falsch eingesetzt kann die Typisierung aber auch zu Problemen führen, da jeder Typ nur limitierte Datenmengen aufnehmen kann (beispielsweise würde das Jahr "2003" beim Datentyp TINYINT als 127 bzw. 255 abgelegt).

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

Numerische Datentypen

Datentyp Bedeutung Bereich Speicherbedarf
TINYINT kleine ganze Zahl -128 bis 127
(0 bis 255)
1 Byte
SMALLINT kleine ganze Zahl -32768 bis 32767
(0 bis 65535)
2 Byte
MEDIUMINT mittelgroße ganze Zahl -8388608 bis 8388607
(0 bis 16777215)
3 Byte
INT ganze Zahl (Standard) -2147283648 bis 2147283647
(0 bis 4294967295)
4 Byte
BIGINT große ganze Zahl -9223372036854775808 bis 9223372036854775807
(0 bis 18446744073709551615)
8 Byte
FLOAT Gleitkommazahl, einfache Genauigkeit 32-Bit-IEEE-Format 4 Byte
DOUBLE Gleitkommazahl, doppelte Genauigkeit 64-Bit-IEEE-Format 8 Byte
DECIMAL(g,n) Gleitkommazahl Länge g mit n Nachkommastellen Größe g + 2 Byte

Zeichenketten-Datentypen

Datentyp Bedeutung Länge Speicherbedarf
CHAR(L) String mit fester Länge L max. 255 Zeichen L Byte
VARCHAR(L) String mit variabler Länge L max. 255 Zeichen 1 - L Byte
TINYTEXT, TINYBLOB kurzer String max. 255 Byte 28 - 1 Byte
TEXT, BLOB String max. 65535 Byte 216 - 1 Byte
MEDIUMTEXT, MEDIUMBLOB Mittelgroßer String max. 16777215 Byte 224 - 1 Byte
LONGTEXT, LONGBLOB Großer String max. 4294967295 Byte 232 - 1 Byte
ENUM Aufzählungstyp Auswahl nur eines Wertes aus der Liste 65535 Elemente
SET Mengentype (Aufzählung) Mehrfachauswahl aus der Liste 64 Elemente

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:

Datums- und Uhrzeit-Datentypen

Datentyp Bedeutung Bereich Speicherbedarf
DATE Datum 1.1.1000 bis 31.12.9999
Format: YYYY-MM-DD
3 Byte
DATETIME Datum und Uhrzeit 1.1.1000, 0:00:00 Uhr bis 31.12.9999, 23:59:59
Format: YYYY-MM-DD hh:mm:ss
8 Byte
TIMESTAMP Unixzeit 1.1.1970 bis 31.12.2036 4 Byte
TIME Uhrzeit Format hh:mm:ss 3 Byte
YEAR Jahreszahl 1901 bis 2155 1 Byte

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:

Bei TIMESTAMP kann eine numerische Formatangabe hinzugefügt werden, die das Anzeigeformat bestimmt:
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

Datenbankabfragen erfolgen mit der SELECT-Anweisung, wie das weiter oben schon kurz gezeigt wurde. Der Befehl ist aber viel mächtiger als es dort gezeigt wurde. Insbesondere ist die Auswahl von Datensätzen nach beliebigen, oft sehr komplexen, Kriterien möglich. Auch kennt die SELECT-Anweisung viele "eingebaute" Funktionen, die man nutzen kann. Der allgemeine Aufbau der SELECT-Anweisung zeigt die Komplexität (alle Angaben in [ ... ] sind optional):
SELECT
    [rechenoperation|funktionsauswahl]
  spaltenname
  FROM tabelle
    [WHERE bedinungen]
    [GROUP BY spalten]
    [HAVING aggregatfunktionen]
    [ORDER BY spalte(n) [ASC] [DESC]
    [LIMIT [Anfang][Zeilenanzahl]]
Eine Globalauswahl der Form SELECT * FROM fahrzeuge; haben Sie schon kennengelernt - wobei Sie, wann immer möglich, statt des '*' besser die Spaltennamen verwenden sollten. Wenn nämlich z. B. irgendwann später neue Spalten in die Datenbank eingefügt werden, kann es sein, dass Ihr Programm sehr seltsame Ausgaben erzeugt. Deshalb ist beispielsweise SELECT id, kennz FROM fahrzeuge; nicht nur aus dem o. g. Grund günstiger, sondern auch lesbarer.

Sortierung

Bei der Abfrage lassen sich auch gleich die Reihenfolge und Sortierung der Spalten festlegen, was effizienter ist, als die Daten in ein Arry zu packen und dieses dann im Perl-Programm zu sortieren. Dies geschieht mit dem Anweisungsteil ORDER BY. Mit den Zusätzen ASC (ascending) und DESC (descending) kann alphabetisch aufsteigend bzw. absteigend sortiert werden. Per Default wird aufsteigend sortiert (ASC).

Beispiel: Sortieren nach Spalten

SELECT 
  nachname, vorname  
  FROM person
  ORDER BY nachname;
Beispiel: Sortieren nach zwei Spalten, erste Spalte absteigend
SELECT
  nachname, vorname 
  FROM person
  ORDER BY nachname DESC, vorname;

Limitierung

Die Anzahl der Datensätz darf auch limitiert werden. Dies geschieht mit dem Anweisungsteil LIMIT [Anfang][Anzahl]. Es wird mit dem durch Anfang festgelegten Datensatz begonnen und Anzahl Datensätze werden ausgegeben. LIMIT 1000,1 würde also genau den tausendsten Datensatz liefern.

Bedingungen setzen mit WHERE

Wie schon erwähnt, besitzt SELECT viele Operatoren und Funktionen. Allen voran stehen natürlich die Vergleichsoperatoren.

OperatorFunktion
=gleich
<> oder !=ungleich
< kleiner als
> größer als
<=kleiner gleich
>=größer gleich
BETWEEN min AND maxim Intervall [min,max]
IS NULList Nullwert (Wert nicht vorhanden)
IS NOT NULList kein Nullwert (Wert vorhanden)
IN (Liste)ist in der Werteliste enthalten
Listenwerte sind Konstanten
NOT BedNegation der Bedingung
Bed1 OR Bed2ODER-Verknüpfung zweier Bedingungen
Bed1 AND Bed2UND-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; 
Es werden alle Vereinsmitglieder selektiert, die nach dem 31.1.1980 eingetreten sind:
SELECT Id, nachname, vorname FROM person
WHERE eintrittsdatum > '1980-01-31'; 
WHERE-Bedingung mit Klammern: alle Personen mit der Lieblingsfarbe gruen ODER Geburtstag zwischen 01.01.1980 und 31.12.1980:
SELECT Id, nachname, vorname FROM person
WHERE lieblingsfarbe = 'schwarz'
OR (geburtsdatum >= '1970-01-01'
    AND geburtsdatum <= '1970-12-31');
Bisher wurde nur mit festen Konstanten verglichen. Die Vergleichsoperatoren LIKE bzw. NOT LIKE erlauben die Angabe von Jokerzeichen (Wildcards). Leider sind die Jokerzeichen nicht die gleichen, wie z. B. bei Dateiangaben auf der Kommandozeile: Der Begriff '_aus' würde sich beispielsweise mit 'Haus' und 'Maus' decken, aber nicht mit 'aus'. '%aus' liefert bei allen drei Begriffen WAHR, aber auch bei 'hinaus' oder 'da wird nix draus'. Beide Jokerzeichen dürfen mehrfach und auch innerhalb von Zeichenketten vorkommen. Alle Namensvarianten von "Meier" (Meier, Maier, Meyer, Mayer) erhält man mit
SELECT Id, nachname, vorname FROM person
WHERE nachname LIKE 'M__er';
Vermutlich bekommt man aber auch 'Moser' und ähnliche Namen. Da hilft dann nur noch Pattern-Matching mit Regulären Ausdrücken (REGEXP, RLIKE), was an dieser Stelle nicht vertieft wird.

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:

Der Vorspann SELECT FORM ... ist fest kodiert und muss entsprechend angepasst oder Parametrisiert werden. Als Spaltenname ist KEY fest eingetragen. Aber auch das kann jederzeit geändert werden. Die Funktion arbeitet in zwei Schritten. Zuerst werden die Zeichen ',', '+', '!' und '*' durch die entsprechenden MySQL-Konstrukte ersetzt. Danach werden die Klammerstruktur und die Suchbegriffe in die korrekte MySQL-Syntax umgesetzt:
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;
  }
Zum Testen werden hier einige Suchstrings an die Funktion übergeben:
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";
Das Ergebnis sieht dann folgendermaßen aus (die letzte Zeile wurde aus optischen Gründen umbrochen):
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');
Neben den oben behandelten logischen Funktionen kennt MySQL noch zahlreiche weitere Funktionen, die in SELECT-Klauseln verwendbar sind:

Aggregatfunktionen

Diese Funktionen fassen die selektierten Datensätze zu einem Wert zusammen. Si können daher nur auf eine einzige Spalte angewendet werden:

FunktionAufgabe
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;
Anzahl der Singles
SELECT COUNT(*) FROM person 
WHERE familienstand='ledig';
Verdienst aller Singles und deren Anzahl
SELECT COUNT(*), SUM(gehalt) FROM person
WHERE familienstand='ledig';

Datumsfunktionen

MySQL kann wesentlich besser mit Datums- und Zeitangaben umgehen wie andere Programmsysteme, wie man am Datumsvergleich oben schon gesehen hat. Hier steht eine ganze Palette von Funktionen zur Verfügung:

FunktionErgebnis
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;
Alle Sonntags-Kinder
SELECT Id, nachname, vorname FROM person
WHERE WEEKDAY(geburtsdatum)=6;
Alle Personen, die zwischen 1980 und 1989 geboren wurden
SELECT Id, nachname, vorname FROM person
WHERE YEAR(geburtsdatum) >= 1980
  AND YEAR(geburtsdatum) <= 1990;
Gelebte Tage seit Geburtstag bis heute
SELECT Id, nachname, vorname FROM person
  TO_DAYS(CURDATE()) - TO_DAYS( geburtsdatum ) 
FROM person
Datums und Uhrzeit-Rechnerei (Ergebnisse immer in der Zeile darunter)
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

Neben den vier Grundrechenarten inklusive Modulo-Operator (+ - * / %) und Klammerung sind folgende mathematische Funktionen verfügbar:

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 eX.
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 XY.
SQRT(X)
Liefert die Wurzel aus X.
LEAST(X,Y,...)
Liefert den kleinsten Wert der Argumentliste, wobei folgende Regeln gelten:
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

Stringfunktionen geben NULL zurück, wenn die Länge des Ergebnisses den Wert max_allowed_packet der MySQL-Serverkonfiguration überschreitet.

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

GROUP BY ...:
Gruppieren der Ausgabe nach bestimmten Kriterien zusammen mit einer Aggregatfunktion, z. B.:
SELECT menge, artikelnr FROM bestellung 
GROUP BY artikelnr;
DISTINCT:
Inhaltlich gleiche Werte ausschließen (jeden Wert nur einmal)
SELECT DISTINCT farbe FROM fahrzeuge;
LAST_INSERT_ID():
Liefert den zuletzt von einer AUTO_INCREMENT-Spalte erzeugten Wert. Das braucht man gelegentlich, wenn zuerst ein Datensatz erzeugt wurde, der anschliessend mit Daten gefüllt werden soll.
SELECT LAST_INSERT_ID();
PASSWORD(text):
Erzeugt ein verschlüsseltes Passwort aus einem Klartext-Passwort. Wird z. B. benötigt, wenn neue User einzutragen sind.
select PASSWORD('badpwd'); → '7f84554057dd964b'

Zusammenfassung der wichtigsten Regeln

5.11 JOIN - komplexe Abfragen

Wenn Sie für einen bestimmten Zweck mehrere Datenbankabfragen nacheinander stellen und die Ergebnisse dann im Programm kombinieren, sollten die Alarmglocken klingeln. Fast immer sind dann die Abfragen nicht optimal und lassen sich zu einer einzigen Abfrage zusammenfassen, was zudem beträchtliche Performance-Gewinne bringt.

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';
Das wird schon recht länglich, insbesondere, wenn noch etliche Bedingungen hinzukommen. Zur Abkürzung kann man den Spalten einen neuen (temporären) Namen geben:
SELECT b.nr, b.menge, b.artikelnr, a.bezeichnung
   FROM bestellung AS b, artikel AS a 
   WHERE b.nr='345';
Mit der oben angegebenen Anfrage würden Sie jedoch nicht besonders glücklich, weil Sie zwar nur die Daten einer Bestellung bekämen, jedoch zu jedem Posten alle(!) Artikelbezeichnungen. Es müssen also die Artikelbezeichnungen auf die aktuelle Bestellung eingeschränkt werden. Sogenannte Joins machen es möglich.

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;
Diese Abfrage nennt man auch Equi-Join. Es handelt sich im Prinzip um einen Cross-Join, bringt aber nur die Kombinationen, bei denen die Artikelnummer in der Tabelle bestellung mit der Artikelnummer in artikel identisch ist.

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;
Wurde versehentlich eine falsche Artikelnummer eingegeben oder ein Artikel gelöscht, erhalten Sie ein Ergebnis, bei dem einige Artikelbezeichnungen "NULL" lauten. Bei einer Verwendung des normalen Equi-Joins wären solche Zeilen nicht aufgeführt worden, da der Bezug zu artikel fehlte. Versuchen wir, die Bestellungen herauszufinden, bei denen Artikelbezüge fehlen. Dazu wird das Ergebnis auf einen NULL-Wert geprüft:
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;
Bei einem Auto-Join (Self-Join) wird eine Tabelle mit sich selbst verknüpft. Das ist beispielsweise dann nützlich, wenn in einer Tabelle hierarchische Daten abgelegt sind. Dazu muss man nur für ein und dieselbe Tabelle zwei verschiedene Alias-Namen angeben:
SELECT b.nr, b.menge FROM bestellung AS b1, bestellung AS b2 
   WHERE b1.artikelnr = b2.artikelnr;
Manchmal braucht man bei einem UPDATE oder DELETE eine Bedingung, die sich nicht auf die Tabelle selbst bezieht, sondern vom Inhalt einer anderen Tabelle abhängt. Auch hier lassen sich Joins einsetzen.

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;
Mit einem Subselect, der in Klammern steht, sieht die Abfrage folgendermaßen aus:
SELECT * FROM kunden
  WHERE NOT EXISTS 
    (SELECT * FROM bestellung WHERE bestellung.kundennr=kunden.kundennr)
Das ist manchmal verständlicher als ein Join. Eine weitere Anwendung für Subselects sind Vergleiche von Werten mit Ergebnisabfragen, wie in folgendem Beispiel:
SELECT * FROM bestellung 
  WHERE artikelnr=
    (SELECT artikelnr FROM artikel WHERE bezeichnung='WLAN-Router');
Hier muss man aber achtgeben, denn ermittelt die zweite SELECT-Abfrage mehr als ein Ergebnis, erhält man eine Fehlermeldung. Für derartige Abfragen verwendet man besser den IN-Operator:
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

Um das MySQL-Passwort für den "root"-User zurückzusetzen oder ein neues Passwort zu vergeben, bedarf es eines kleinen Tricks. Man loggt sich dazu auf dem DB-Server als "root" ein (oder wechselt per "su" oder "sudo bash" in die "root"-Identität). Nun kann der laufende MySQL-Server-Prozess gestoppt und mit einem Zusatzparameter wieder gestartet werden:
#Stopp des MySQL-Servers
/etc/init.d/mysql stop
#Start des MySQL-Servers ohne Rechte-Tabellen im Hintergrund /usr/sbin/mysqld --skip-grant-tables & Nun kann sich der Root-User des MySQL-Servers ohne Passworteingabe anmelden:
mysql -u root
Nun setzt man das Passwort für den Root-User über eine MySQL-Query neu und meldet sich wieder ab:
USE mysql;
UPDATE user SET Password=PASSWORD('NEUESPASSWORT') WHERE User='root'; 
FLUSH PRIVILEGES;
QUIT
In der zweiten Zeile oben muss natürlich der Platzhalter 'NEUESPASSWORT' durch das neue Root-Passwort ersetzt werden.

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
#Login in MySQL als root-Benutzer ohne Passworteingabe. mysql -u root mysql
Zum vorhergehenden Abschnitt Zum Inhaltsverzeichnis Zum nächsten Abschnitt


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