Das große MySQL-Tutorial für Einsteiger

Die Grundlage unseres digitalen Universums ist ein stetig wachsender Datenberg. Zentrales Element des Internets und der vernetzten Welt sind Datenbank-Management-Systeme wie MySQL. Diese ermöglichen es, große Datenmengen elektronisch zu verarbeiten, widerspruchsfrei abzulegen und dauerhaft aufzubewahren. Dabei werden komplexe Datenbestände in handliche Teilmengen zerlegt und bei Bedarf in Relation gesetzt. In unserem MySQL-Tutorial für Anfänger führen wir Sie in die Grundlagen der Datenbankverwaltung ein und zeigen Ihnen an Beispielen, wie Sie die Datenhaltung für Ihr Webprojekt mit MySQL optimieren.

Was ist MySQL?

MySQL gehört neben Oracle und Microsoft SQL Server zu den weltweit populärsten relationalen Datenbank-Management-Systemen (eine aktuelle Übersicht finden Sie auf db-engines.com). Die 1994 vom schwedischen Unternehmen MySQL AB entwickelte Software steht heute unter der Schirmherrschaft der Oracle Corporation und wird auf Basis eines dualen Lizenzsystems vertrieben: Neben der proprietären Enterprise-Ausführung bietet Oracle eine GPL-lizenzierte, quelloffene Version an.

Diese Doppellizenzierung gibt Unternehmen die Möglichkeit, eigene Anwendungen auf Basis von MySQL zu entwickeln, ohne diese der Open-Source-Lizenz unterwerfen zu müssen. In der Open-Source-Gemeinde stößt die Übernahme von MySQL durch Oracle jedoch überwiegend auf Kritik.

Tipp

Datenbank Hosting gesucht? Hier finden Sie professionelles SQL-Hosting mit dem Datenbank-Management-System Ihrer Wahl.

MySQL ist in C und C++ geschrieben und verfügt über einen Yacc-basierten SQL-Parser mit selbst entwickeltem Tokenizer (lexikalischer Scanner). Das Datenbank-Management-System zeichnet sich zudem durch eine breite Betriebssystem-Unterstützung aus.  

Fakt

Die Abkürzung SQL steht für „Structured Query Language“, eine Computersprache, die der Verwaltung von Datenbankstrukturen dient. Mögliche Operationen umfassen das Abfragen, Einfügen, Aktualisieren und Löschen von Datenbeständen.

MariaDB – MySQL-Fork mit Potenzial

Die Eingliederung des MySQL-Projekts in das Oracle-Produktportfolio stößt in der Entwicklerszene überwiegend auf Misstrauen und Kritik. Dies hat in erster Linie damit zu tun, dass die Unterschiede zwischen der GPL-lizenzierten MySQL-Version und dem kostenpflichtigen Enterprise-Produkt stetig zunehmen. Neue Funktionen des Datenbank-Management-Systems stehen immer häufiger nur in der proprietären Version zur Verfügung. Nichtöffentliche Fehler-Datenbanken und mangelnde Tests lassen die Community vermuten, dass das Open-Source-Projekt unter den Fittichen des Software-Goliaths Oracle nur stiefmütterlich behandelt wird. Der Rückhalt in der Open-Source-Gemeinschaft nimmt daher stetig ab.

Bereits 2009 hat das Kernentwicklerteam um den MySQL-Erfinder Michael „Monty“ Widenius dem beliebten Datenbanksystem den Rücken gekehrt und mit MariaDB einen quelloffenen Fork der Software initiiert. Ende 2012 leiteten mit Fedora, OpenSUSE, Slackware und Arch Linux die ersten Linux-Distributionen die Umstellung von MySQL auf MariaDB als Standard-Installation ein. Zahlreiche Open-Source-Projekte sowie namhafte Software-Firmen und Webplattformen folgten dem Beispiel – darunter Mozilla, Ubuntu, Google, Red Hat Enterprise Linux, Web of Trust, Team Speak, die Wikimedia Foundation und das bereits angesprochene Software-Projekt XAMPP.

Schon jetzt zeichnet sich ab, dass sich MariaDB im Vergleich zur quelloffenen MySQL-Version kontinuierlicher weiterentwickelt. Es ist daher anzunehmen, dass der Fork seinem Mutterprojekt bald den Rang ablaufen wird.

Tipp

Als Fork wird in der Software-Branche ein Entwicklungszweig bezeichnet, der durch Abspaltung von einem (meist quelloffenen) Projekt entsteht. Ein Fork baut auf dem Quellcode des Mutterprojekts auf und entwickelt diesen in einem unabhängigen Folgeprojekt weiter.

Datenbanksysteme

Die elektronische Datenverwaltung findet heute größtenteils in Datenbanksystemen (DBS) statt. Diese bestehen grundsätzlich aus zwei Komponenten: der Datenbank (DB) selbst und dem zu ihrer Verwaltung nötigen Datenbank-Management-System (DBMS).

  • Das Datenbank-Management-System: Beim DBMS handelt es sich um eine Software wie MySQL, die der Verwaltung des DBS dient. Zu den Aufgaben dieser Verwaltungssoftware gehört die Strukturierung der Daten gemäß einem vordefinierten Datenbankmodell. Darüber hinaus kontrolliert das DBMS Schreib- und Lesezugriffe auf die Datenbank, verwaltet große Datenmengen sowie parallele Datenbankzugriffe und sorgt dafür, dass Richtlinien zu Datenintegrität, Datenschutz und Datensicherheit eingehalten werden.
  • Die Datenbank: Eine Datenbank ist ein inhaltlich zusammenhängender Datenbestand, z. B. Kundendaten oder die CMS-Daten. Ein DBMS kann mehrere DBs zugleich verwalten.

Folgende Grafik zeigt eine schematische Darstellung eines Datenbanksystems:

Das relationale Datenbankmodell

MySQL ist der Definition nach ein relationales DBMS. Das bedeutet, dass die Datenverwaltung auf einem tabellenbasierten Datenbankmodell beruht. Sämtliche Daten, die MySQL verarbeitet, werden in Tabellen gespeichert, die sich durch Schlüssel (keys) in Beziehung setzen lassen.

Wir veranschaulichen dies an einem einfachen Beispiel. Nachstehend finde Sie die beiden Tabellen autoren und werke:

Alle Tabellen einer relationalen Datenbank bestehen aus Spalten und Zeilen. Jede Spalte einer Tabelle steht für ein bestimmtes Attribut. In der Tabelle autoren finden sich beispielsweise die Attribute id, vorname und nachname. Die Zeilen einer Tabelle werden als Rows bezeichnet und beinhalten je einen Datensatz. Dieser wird in der Regel durch einen Primärschlüssel eindeutig identifiziert (nummeriert). Welches Attribut als Primarschlüssel fungiert, wird bei der Erstellung der Tabelle definiert. Voraussetzung ist, dass der Primärschlüssel eine eindeutige Zuordnung ermöglicht. Dazu darf jeder Primärschlüssel innerhalb einer Spalte nur einmal verwendet werden. Es empfiehlt sich eine Durchnummerierung via ID.

Die Tabelle werke führt neben dem Primärschlüssel werke_id zudem die autoren_id als Fremdschlüssel (foreign key) auf. Dieser stellt eine Beziehung zwischen beiden Tabellen her und ermöglicht es, die Datensätze der einen Tabelle mit denen der anderen zu verknüpfen. Setzt man zwei Tabellen einer relationalen Datenbank in Verbindung, spricht man von einem Join. Ein solcher lässt sich beispielsweise durch folgende Datenbankabfrage realisieren: „Rufe alle Werke des Autors John Ronald Reuel Tolkien inklusive Datum der Erstveröffentlichung ab“.

Tolkien ist in der Tabelle autoren mit dem Primärschlüssel autoren_id 1 gelistet. Um sämtliche Werke des Autors abzufragen, kommt dieser in der Tabelle werke als Fremdschlüssel zum Einsatz. Es werden somit alle Rows abgerufen, die mit der autoren_id 1 verknüpft sind.

In der Praxis werden MySQL-Datenbankoperationen mit standardisierten SQL-Befehlen wie SELECT, INSERT, UPDATE und DELETE realisiert. Auf diese werden wir in den folgenden Kapiteln unseres MySQL-Tutorial noch näher eingehen.

Natürlich hätte man sämtliche Daten zu beiden Autoren und deren Werken in nur einer Tabelle speichern können. Eine solche Datenhaltung führt jedoch dazu, dass eine Datenbank eine Vielzahl redundanter Einträge beinhaltet, da beispielsweise die Daten zu den Spalten vorname und nachname in diesem Fall für jedes Werk einzeln aufgeführt werden müssten. Eine solche Redundanz belastet nicht nur den Speicher, sondern führt auch dazu, dass etwaige Aktualisierungen an mehreren Stellen der Datenbank vorgenommen werden müssen. Bei der Arbeit mit relationalen Datenbanksystemen beschränkt man sich daher darauf, nur einen Sachverhalt pro Tabelle abzubilden. Man spricht in diesem Fall von einer Normalisierung der Daten.

Das zentrale Anwendungsfeld von MySQL ist die Datenspeicherung im Rahmen dynamischer Webangebote. Die Kombination von MySQL mit der Webserver-Software Apache und den Skriptsprachen PHP oder Perl hat sich als klassisches Software-Gerüst in der Webentwicklung etabliert. Der Web-Stack lässt sich als LAMP (Linux), MAMP (macOS) oder WAMP (Windows) mit allen gängigen Server-Betriebssystemen realisieren.

MySQL-Einsteigern empfehlen wir die lokale Textumgebung XAMPP, um erste Erfahrungen mit dem Datenbank-Verwaltungssystem zu sammeln. Dieses setzt in der aktuellen Version auf MariaDB.

Installation des Datenbank-Management-Systems

Um Ihnen die MySQL-Grundlagen näher zu bringen, werden wir mit praxisnahen Beispielen arbeiten. Unserer MySQL-Einführung liegt dabei die Testumgebung XAMPP zugrunde. Code-Snippets und Screenshots konzentrieren sich auf Datenbankoperationen, die via PHP mithilfe eines Apache HTTP Servers lokal auf einem Windows-Rechner realisiert wurden. Statt der klassischen MySQL-Datenbank kommt dabei der Fork MariaDB zum Einsatz. Derzeit sind beide Datenbank-Management-Systeme so kompatibel, dass sich alle Operationen 1:1 übertragen lassen. Für Sie als Endanwender macht es im Rahmen eines Einsteiger-Tutorials keinen Unterschied, ob Sie mit MySQL oder MariaDB arbeiten.

Wie Sie eine solche Testumgebung lokal auf Ihrem Windows-Rechner installieren, ist Thema unseres XAMPP-Tutorials. Möchten Sie den Umgang mit relationalen Datenbanken von der Pike auf lernen, empfehlen wir, direkt mit MariaDB einzusteigen. Eine alternative Testumgebung auf Basis von MySQL steht Ihnen mit AMPPS jedoch ebenfalls kostenlos zur Verfügung.

Darüber hinaus besteht die Möglichkeit, einen benutzerdefinierten Web-Stack aufzusetzen. MySQL und MariaDB lassen sich bei Bedarf mit diversen Betriebssystemen, Webservern und Skriptsprachen kombinieren. Kostenlose, GPL-lizenzierte Downloadpakete werden unter mysql.de und mariadb.com angeboten. Detaillierte Installationsanleitungen für verschiedene Plattformen finden Sie in den englischsprachigen Dokumentationen von MySQL und MariaDB.

Datenbankverwaltung mit phpMyAdmin

Bei der Verwaltung von MySQL stützen wir uns auf die freie Webanwendung phpMyAdmin. Diese ist bereits im Installationsumfang des XAMPP-Software-Bundles enthalten, wird auf der offiziellen Projekt-Website jedoch auch separat als Download-Paket angeboten.

phpMyAdmin gilt als Standardsoftware zur Administration von MySQL-Datenbanken im World Wide Web. Die in PHP und JavaScript geschriebene Webanwendung ermöglicht es, Datenbankoperationen über eine grafische Benutzeroberfläche durchzuführen. So erstellen und verwalten Sie die Tabellen Ihrer relationalen Datenbank bequem per Klick im Webbrowser. Die Kenntnis entsprechender SQL-Befehle ist dazu zunächst nicht notwendig.

phpMyAdmin aufrufen

Wurde das Software-Bundle XAMPP installiert, starten Sie das Datenbank-Management-System (MySQL oder MariaDB) analog zu den anderen Komponenten des Test-Stacks über das Control-Panel. Nutzen Sie dazu die unter „Actions“ aufgeführte Schaltfläche „Start“. Um phpMyAdmin über den Webbrowser aufrufen zu können, müssen Sie zudem den Webserver Apache starten. Aktivierte Module werden im XAMPP-Control-Panel grün hinterlegt. Zusätzlich bekommen Sie den aktuellen Status der XAMPP-Module als Meldung im Textfenster ausgegeben.

Fakt

XAMPP wurde im Rahmen des Software-Projekts Apache Friends als kompaktes Testsystem für den Einsatz auf einem lokalen Rechner entwickelt. Das Software-Bundle ist nicht dazu gedacht, Webdienste im Internet bereitzustellen. Als Produktivsystem ist XAMPP aufgrund zahlreicher Einschränkungen im Bereich der Sicherheit nicht geeignet.

Im lokalen Testbetrieb steht Ihnen die Weboberfläche der Administrationssoftware unter "http://localhost/phpmyadmin/" zur Verfügung.

Sofern Sie bei der MySQL-Installation ein Passwort für den root-Account definiert haben, fragt phpMyAdmin dieses über einen Log-in-Bildschirm ab. Nutzen Sie phpMyAdmin im Rahmen eines Webhosting-Produkts, werden entsprechende Anmeldedaten über den jeweiligen Anbieter vergeben. In der Regel besitzen Sie in diesem Fall keine root-Rechte.

Nach erfolgreichem Log-in präsentiert phpMyAdmin Ihnen die Startseite der Anwendung. Diese bietet Ihnen die Möglichkeit, grundlegende Einstellungen zum Zeichensatz (Kollation) der MySQL-Verbindung zu treffen sowie den gewünschten Anzeigemodus (Sprache, Design und Schriftgröße) zu wählen. Zudem finden Sie auf der rechten Seite eine Übersicht der Eckdaten Ihres Datenbank-Servers, der verwendeten Webserver-Software sowie Informationen über die aktuelle Version von phpMyAdmin. Die Menüleiste der Startseite ist wie alle anderen Menüleisten der Anwendung in Form von Registerkarten gestaltet. Zur Auswahl stehen die Tabs Databases, SQL, Status, User accounts, Export, Import, Settings, Replication, Variables und More.

Am linken Rand der Benutzeroberfläche finden Sie ein Navigations-Panel. Hier werden alle Tabellen aufgeführt, auf die Sie mithilfe von phpMyAdmin in Ihrer Datenbank zugreifen können. Unter dem Programm-Logo in der linken oberen Ecke hält die Software Links zur Startseite sowie zu offiziellen Dokumentationen bereit. Darüber hinaus haben Sie die Möglichkeit, das Navigations-Panel zu konfigurieren und Ihre Ansicht zu aktualisieren.

Wir starten unseren MySQL-Crashkurs damit, dass Sie Ihre erste Datenbank anlegen.

Datenbank anlegen

Um eine Datenbank mit phpMyAdmin anzulegen, wählen zunächst den Tab „Databases“ (Datenbanken) in der Menüleiste der Startseite aus.

Geben Sie einen gewünschten Namen für Ihre Datenbank in das Eingabefeld unter „Create database“ (Neue Datenbank anlegen) ein und wählen Sie einen Zeichensatz. Wir empfehlen die Kollation utf8mb4_unicode_ci. Mit der Wahl eines Zeichensatzes teilen Sie dem Datenbankserver mit, welche Codierung für die zu sendenden bzw. empfangenden Daten verwendet werden soll. Die mb4-Varianten erlauben auch exotische Zeichen wie Symbole oder Emojis, die außerhalb der Unicode-Basis-Ebene (Basic Multilingual Plane) liegen, und sind deshalb zu empfehlen.

Bestätigen Sie Ihre Eingabe mit einem Klick auf „Create“ (Anlegen). Die von Ihnen erstellte Datenbank wird im Navigations-Panel auf der linken Bildschirmseite angezeigt. Neu erstellte Datenbanken enthalten zunächst keine Inhalte. Um Daten zu hinterlegen, erstellen Sie im nächsten Schritt eine Tabelle.

Tabellen anlegen

Um eine neue Tabelle anzulegen, wählen Sie die gewünschte Datenbank aus und navigieren über die Menüleiste in den Tab „Structure“ (Struktur).

Legen Sie eine Tabelle an, indem Sie einen Namen (z. B. users) sowie die gewünschte Anzahl an Spalten in die Schaltfläche „Create table“ (Erzeuge Tabelle) eingeben. Sie erinnern sich: Jede Spalte steht für ein Attribut eines Datensatzes. Benötigen Sie zusätzliche Spalten, können Sie diese auch nachträglich hinzufügen.

Möchten Sie beispielsweise eine Nutzerdatenbank für Ihre Website anlegen, bieten sich folgende Bezeichnungen für die Spalten der Tabelle an:

Für Ihre Nutzerdatenbank erstellen Sie somit eine Tabelle users mit sieben Spalten. Ihre Eingabe bestätigen Sie mit „Go“.

Nachdem die Tabelle angelegt wurde, gibt phpMyAdmin Ihnen die Möglichkeit, Bezeichnungen für die Tabellenspalten zu definieren und Formateinstellungen für die zu erwartenden Daten zu treffen.

Eine Beschreibung der Tabellenstruktur und möglicher Formatierungen finden Sie in folgender Tabelle.

Option

Beschreibung

Name

Jede Spalte einer Datenbanktabelle wird mit einem Namen versehen. Dieser kann mit Einschränkungen frei gewählt werden. Unproblematisch sind lateinische Buchstaben (groß und klein, aber ohne Umlaute), Ziffern, Dollarzeichen und Unterstrich. Diesen können Sie als Alternative zum nicht erlaubten Leerzeichen verwenden (falsch: user id; richtig: user_id). Spaltennamen dürfen nicht nur aus Ziffern bestehen. Zudem finden sich in der Datenbanksprache SQL diverse Keywords, die für bestimme Aufgaben reserviert sind. Eine Liste lässt sich der MySQL-Dokumentation entnehmen. Die meisten dieser Einschränkungen dürfen Sie umgehen, müssen dann aber die jeweilige Spalte immer in Backticks (``) setzen. Die gleichen Regeln gelten übrigens auch für Tabellen- und andere Namen in MySQL. Zu empfehlen sind sprechende Spaltennamen passend zum jeweiligen Attribut.

Type

Der Datentyp gibt an, welche Art von Daten in einer Spalte gespeichert wird. MySQL und MariaDB ermöglichen Ihnen, Daten als Ganz- und Fließkommazahlen, Zeit- und Datumsangaben sowie Textstrings und Binärdaten zu definieren. Eine Beschreibung finden Sie in der Tabelle Datentypen.

Length/Values

Bei manchen Datentypen (zum Beispiel Textstrings) können Sie den Werten einer Spalte eine Maximallänge zuweisen. Diese Formatierung ist optional.

Default

Die Option „Default“ erlaubt Ihnen, einen Standardwert für eine Spalte zu definieren. Dieser wird immer dann automatisch eingefügt, wenn ein Datensatz keinen Wert für die jeweilige Spalte enthält.

Collation

Mit der Option „Collation“ definieren Sie für eine Spalte einen bestimmten Zeichentyp, der von den globalen Datenbankeinstellungen abweichen kann. Sie können die Kodierung auch auf Tabellenebene für alle Spalten ändern.

Attributes

Manche Datentypen lassen sich durch optionale Attribute näher bestimmen. So können Sie beispielsweise mit den Attributen signed und unsigned festlegen, ob eine Ganz- oder Fließkommazahl auch negative (signed) oder nur positive (unsigned) Werte annehmen kann.

Index

Über die Option „Index“ definieren Sie Regeln für die Indexierung. Wählen Sie für eine Spalte die Index-Einstellung PRIMARY, gilt diese als Primärschlüssel der Tabelle. Die Einstellung UNIQUE legt fest, dass Werte innerhalb dieser Spalte nur einmal gespeichert werden können. So lassen sich Doppelungen bei Bedarf vermeiden.

A_I

Die Abkürzung „A_I“ steht für AUTO_INCREMENT und weist das Datenbank-Management-System an, einen Wert automatisch hochzuzählen, wenn beim Anlegen eines Datensatzes keiner angegeben wurde. Zum Einsatz kommt diese Option bei der Indexierung von Datensätzen.

Comments

Das Feld „Comments“ ermöglicht Ihnen, Tabellenspalten mit Kommentaren zu versehen.

Die hier dargestellten Optionen umfassen die wichtigsten Einstellungen zur Formatierung von Tabellenspalten. Wenn Sie mithilfe der Bildlaufleiste weiter nach rechts scrollen, finden Sie weitere Einstellungsmöglichkeiten, die wir in diesem MySQL-Tutorial für Einsteiger nicht ansprechen.

Nachstehende Tabelle führt verschiedene Datentypen, die sich mit MySQL und MariaDB verarbeiten lassen, sowie deren Wertebereich und Speicherbedarf auf.

Typ

Beschreibung

Wertebereich

Speicherbedarf

TINYINT

Eine sehr kleine Ganzzahl

Ohne Vorzeichen: 0 bis 255

Mit Vorzeichen: -128 bis +127

1 Byte

SMALLINT

Eine kleine Ganzzahl

Ohne Vorzeichen: 0 bis 65.535

Mit Vorzeichen: -32.768 bis +32.767

2 Byte

MEDIUMINT

Eine Ganzzahl mittlerer Größe

Ohne Vorzeichen: 0 bis 16.777.215

Mit Vorzeichen: -8.388.608 bis +8.388.607

3 Byte

INT/INTEGER

Eine Ganzzahl normaler Größe

Ohne Vorzeichen: 0 bis 4.294.967.295

Mit Vorzeichen: -2.147.483.648 bis +2.147.483.647

4 Byte

BIGINT

Eine große Ganzzahl

Ohne Vorzeichen: 0 bis 18.446.744.073.709.551.615

Mit Vorzeichen:

-9.223.372.036.854.775.808 bis +9.223.372.036.854.775.807

8 Byte

FLOAT

Eine Fließkommazahl mit einfacher Genauigkeit

Ohne Vorzeichen: 0 bis 3,4e+38

Mit Vorzeichen: -3,4e+38 bis 3,4e+38

4 Byte

DOUBLE

Eine Fließkommazahl mit doppelter Genauigkeit

Ohne Vorzeichen: 0 bis 3,4e+38

Mit Vorzeichen: -3,4e+38 bis 3,4e+38

8 Byte

DATE

Datum im Format 'YYYY-MM-DD'

'1000-01-01' bis '9999-12-31'

3 Byte

TIME

Zeitangabe im Format 'HH:MM:SS.ssssss'

'-838:59:59.999999' bis '838:59:59.999999'

3 Byte

DATETIME

Datumsangabe im Format 'YYYY-MM-DD HH:MM:SS.ssssss'

Entspricht DATE und TIME (bis 23:59:59.999999 Stunden)

8 Byte

TIMESTAMP

Zeitstempel im Format 'YYYY-MM-DD HH:MM:DD'

'1970-01-01 00:00:01' (UTC) bis '2038-01-19 05:14:07' (UTC)

4 Byte

YEAR

Jahr zwischen 1901 bis 2155

1901 bis 2155 und 0000

1 Byte

CHAR

Zeichenkette fester Länge; Anzahl der Zeichen entspricht M

Für M: 0 bis 255 Zeichen

M Byte

VARCHAR

Zeichenkette variabler Länge; Anzahl der Zeichen entspricht M

Für M: 0 bis 65.535 Zeichen

Max. M + 2 Byte

TINYTEXT

Sehr kleine Zeichenkette variabler Länge; Anzahl der Zeichen entspricht M

Für M: 0 bis 255 Zeichen

M + 1 Byte

TEXT

Zeichenkette variabler Länge; Anzahl der Zeichen entspricht M

Für M: 0 bis 65.535 Zeichen

M + 2 Byte

MEDIUMTEXT

Mittelgroße Zeichenkette variabler Länge; Anzahl der Zeichen entspricht M

Für M: 0 bis 16.777.215 Zeichen

M + 3 Byte

LONGTEXT

Lange Zeichenkette variabler Länge; Anzahl der Zeichen entspricht M

Für M: 0 bis 4.294.967.295 Zeichen (4 GB)

M + 4 Byte

BLOB

Bei einem BLOB (Binary Large Object) handelt es sich um ein binäres Objekt mit Daten variabler Länge (z. B. Bilder, Audiodaten).

Max. Länge M: 65.535 Byte

M + 2 Byte

TINYBLOB

Kleines binäres Objekt mit Daten variabler Länge

Max. Länge M: 255 Byte

M + 1 Byte

MEDIUMBLOB

Mittleres binäres Objekt mit Daten variabler Länge.

Max. Länge M: 16.777.215 Byte

M + 3

Byte

LONGBLOB

Großes binäres Objekt mit Daten variabler Länge.

Max. Länge M: 4.294.967.295 Byte (4 GB).

M + 4 Byte

ENUM (Aufzählung)

Ein String-Objekt, dessen zulässige Werte bei der Erstellung der Spalte definiert werden

Max. 65,535 unterschiedliche Elemente

1 oder 2 Bytes, abhängig von der Anzahl der möglichen Werte

SET

Ein String-Objekt, dessen zulässige Werte bei der Tabellenerstellung definiert werden. Mehrfachauswahl ist möglich

Max. 64 unterschiedliche Werte

1, 2, 3, 4, oder 8, abhängig von der Anzahl der möglichen Werte

Für die Beispieltabelle users haben wir folgende Einstellungen getroffen:

Mögliche Werte für die Spalte id wurden als Ganzzahl (Integer, INT) definiert und tragen das Attribut UNSIGNED. Die id kann somit lediglich positive Zahlenwerte annehmen. Unter „Index“ haben wir für die id die Einstellung PRIMARY ausgewählt. Die Identifikationsnummer fungiert somit als Primärschlüssel für die Tabelle users. Das Häkchen bei „A_I“ (Auto_Increment) signalisiert dem Datenbank-Management-System, dass IDs für jeden Eintrag als fortlaufende Nummern automatisch erzeugt werden sollen.

Die Werte für die Spalten forename, surname, email und password wurden als Datentyp VARCHAR definiert. Es handelt sich somit um variable Zeichenketten, deren Länge (M) wir mit der Option „Length/Value“ auf 50 Zeichen eingeschränkt haben. Für die Spalte email wurde zudem die Index-Option UNIQUE aktiviert. So stellen wir sicher, dass jede E-Mail-Adresse in unserer Tabelle nur einmal gespeichert wird.

Für die Spalten created_at und updated_at haben wir den Datentyp TIMESTAMP gewählt. Das Datenbank-Management-System speichert Zeitdaten zur Erstellung und Aktualisierung von Datensätzen somit im Format YYYY-MM-DD HH:MM:DD. Da das System für jeden neuen Eintrag automatisch einen Zeitstempel erstellen soll, wählen wir für die Spalte created_at den Standardwert CURRENT_TIMESTAMP. Die Spalte updated_at wird erst dann relevant, wenn wir einen Eintrag aktualisieren. Wir erlauben daher Null-Werte für diese Spalte und setzten NULL als Standardwert.

Fakt

Der Wert NULL repräsentiert in PHP einen leeren Eintrag. Ein Datenfeld hat den Wert NULL, wenn diesem noch kein Wert zugewiesen wurde.

Als Storage-Engine verwenden wir das MySQL-Standard-Tabellenformat InnoDB.

Alle Tabelleneinstellungen, die Sie über die grafische Benutzeroberfläche treffen, werden von phpMyAdmin in SQL-Code übersetzt. Diesen lassen Sie sich bei Bedarf über die Schaltfläche „SQL-Vorschau“ ausgeben.

CREATE TABLE test.users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , forename VARCHAR(50) NOT NULL , surname VARCHAR(50) NOT NULL , email VARCHAR(50) NOT NULL , password VARCHAR(50) NOT NULL , created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , updated_at TIMESTAMP NULL DEFAULT NULL , PRIMARY KEY (id), UNIQUE (email)) ENGINE = InnoDB;

Eine detaillierte Beschreibung der SQL-Syntax folgt im Kapitel zu Datenbankabfragen.

Ein Klick auf „Save“ speichert Ihre Einstellungen. Die Tabelle users wird im Navigations-Panel unter der Datenbank test angezeigt.

Tabellen verwalten

Um eine erstellte Tabelle zu verwalten, klicken Sie auf den Tabellennamen im Navigations-Panel. phpMyAdmin zeigt Ihnen im Tab „Browse“ (Anzeige) eine Übersicht der in der jeweiligen Tabelle gespeicherten Daten. Im Fall unserer Beispieltabelle liefert die Abfrage ein leeres Resultat. Es befinden sich noch keine Datensätze in der Tabelle users.

Zur Verwaltung von Datentabellen stehen Ihnen in der Menüleiste verschiedene Tabs zur Verfügung. Möchten Sie die Struktur einer Datentabelle verändern, wählen Sie den Tab „Structure“. Neue Datensätze fügen Sie Ihrer Tabelle im Tab „Insert“ (Einfügen) hinzu. Darüber hinaus ermöglicht es phpMyAdmin, Tabellen zu durchsuchen, Berechtigungen zu verwalten und Datensätze zu exportieren oder aus anderen Tabellen zu importieren.

Tabellenstruktur überarbeiten

Möchten Sie Ihre Tabelle nachträglich um zusätzliche Spalten ergänzen, existierende löschen oder überarbeiten, wechseln Sie in den Reiter „Structure“.

Ergänzen Sie Spalten über die Schaltfläche „Add columns“, indem Sie die gewünschte Anzahl neuer Spalten sowie die jeweilige Position angeben.

In folgender Grafik wird die neue Spalte nach updated_at eingefügt:

Möchten Sie bereits bestehende Spalten löschen, wählen Sie diese über ein Häkchen in der Check-Box aus und klicken anschließend auf „Drop“.

Das Bearbeiten einer Spalte erfolgt über die Schaltfläche „Change“. Sie gelangen in eine Bearbeitungsansicht, die der gleicht, die Sie bereits von der Tabellenerstellung kennen:

Änderungen an der Tabellenstruktur können unter Umständen zu Datenverlust führen. Bevor sie bereits angelegte Tabellenspalten bearbeiten oder löschen, sollten Sie in jedem Fall ein Back-up Ihrer Datenbank anlegen. Wechseln Sie dazu in den Tab „Export“ (Exportieren), wählen Sie das gewünschte Datenformat für das Back-up aus und bestätigen Sie mit „Go“. Es öffnet sich ein Dialogfenster, in dem Ihr Webbrowser das Speicherziel für den Download abfragt. Eine Alternative zum Datenbank-Back-up via phpMyAdmin stellt das kostenlose Sicherungsprogramm MySQLDumper dar.

Tabelleneinträge erstellen

Um Ihre Tabelle via phpMyAdmin mit Daten zu befüllen, stehen Ihnen zwei Möglichkeiten offen: Entweder Sie importieren Datensätze aus einer externen Datei (z. B. aus einem Back-up) oder Sie erstellen Tabelleneinträge manuell. Wählen Sie dazu die Beispieltabelle users aus und navigieren Sie in den Tab „Insert“ (Einfügen).

phpMyAdmin zeigt Ihnen folgende Dateneingabemaske:

Unter „Column“ sind die Spalten aufgelistet, die wir für die Tabelle users definiert haben. Unter „Type“ finden Sie die Information, welcher Datentyp für die jeweilige Spalte erwartet wird, sowie eine etwaige Zeichenbegrenzung in Klammern. Den Bereich „Function“ klammern wir aus und gehen direkt zu „Value“ über. In diesem Bereich definieren wir die Werte für die einzelnen Spalten unserer Beispieltabelle.

Im vorherigen Kapitel haben wir die Tabelle users so konfiguriert, dass die Datenfelder für die Spalten id, created_at und updated_at vom Datenbank-Management-System automatisch mit Inhalten versehen werden. In der Spalte id vergeben wir via AUTO_INCREMENT eine laufende Identifizierungsnummer für jeden neuen Eintrag. Das Feld created_at wird automatisch mit dem aktuellen Zeitstempel versehen. Und für updated_at vergibt das System zunächst den Standardwert NULL. Wir müssen somit lediglich die Daten für die Spalten forename, surname, email und password manuell eintragen. Wir veranschaulichen dies an fiktiven Nutzerdaten:

  • forename: John
  • surname: Doe
  • email: john@doe.com
  • password: qwertz

Mit einem Klick auf „Go“ übertragen Sie die Daten in Ihre Tabelle. phpMyAdmin springt automatisch in den Tab „SQL“ und zeigt Ihnen die ausgeführte Datenbankoperation als Statement gemäß der SQL-Syntax an:

INSERT INTO users (id, forename, surname, email, password, created_at, updated_at) VALUES (NULL, 'John', 'Doe', 'john@doe.com', 'qwertz', CURRENT_TIMESTAMP, NULL);

Prinzipiell lassen sich alle Datenbankoperationen, die Sie via phpMyAdmin komfortabel über eine grafische Benutzeroberfläche durchführen, auch in der Datenbanksprache SQL notieren. Dies gehört im Rahmen der Webentwicklung zum Standardvorgehen.

Sogenannte SQL-Queries (Abfragen) finden sich im Quellcode aller dynamischen Webanwendungen und ermöglichen dem Webserver die Interaktion mit der Datenbank. Die Datenbanksprache SQL stützt sich dabei auf Befehle – beispielsweise, um Daten abzurufen und im Rahmen der Programmausführung zu nutzen. Die wichtigsten SQL-Befehle SELECT, INSERT, DELETE und UPDATE sowie die Syntax der grundlegenden Datenbankoperationen thematisieren wir in den folgenden Kapiteln unsres MySQL-Tutorials.

Zunächst füllen wir unsere Tabelle users jedoch mit weiteren Nutzerdaten und schauen uns die Tabellenübersicht im Tab „Browse“ an:

Ein Klick auf den jeweiligen Spaltennamen sortiert die Tabelle in der gewünschten Reihenfolge.

Datenbankverbindung aufbauen

Nachdem wir unsere Bespieltabelle users mit Einträgen befüllt haben, werden wir uns in den nächsten Kapiteln anschauen, wie sich die hinterlegten Daten via PHP über den Apache Webserver abfragen lassen.

Dazu stellen wir im ersten Schritt eine Datenbankverbindung her. In PHP stehen Ihnen dazu drei Schnittstellen zur Verfügung: MySQL Extension, MySQL Improved Extension (MySQLi) und PHP Data Objects (PDO).

  • MySQL-Extension: Bei MySQL-Extension handelt es sich um eine überholte MySQL-Schnittstelle, die früher sehr populär war, heute jedoch als veraltet gilt. Gegenüber MySQLi und PDO hat die alte MySQL-Extension den Nachteil, dass sie weder Prepared Statements noch benannte Parameter unterstützt.
  • MySQLi: Bei MySQLi handelt es sich um eine verbesserte Version der klassischen PHP-Erweiterung für den Zugriff auf MySQL-Datenbanken. Die Schnittstelle arbeitet sowohl prozedural als auch objektorientiert. Der Einsatz ist auf MySQL- und MariaDB-Datenbanken beschränkt.
  • PDO: Bei PHP Data Objects (PDO) handelt es sich um eine objektorientierte Schnittstelle, die eine Abstraktionsschicht für den Datenzugriff zur Verfügung stellt. Somit lassen sich via PDO nicht nur MySQL-Datenbanken, sondern auch andere Datenbanksysteme wie PostgreSQL, Oracle, MSSQL oder SQLite in PHP einbinden.

Im Folgenden werden wir uns auf Datenbankverbindungen via PDO beschränken.

Um im Rahmen eines PHP-Scripts Anfragen an eine Datenbank stellen zu können, muss diese zunächst authentifiziert werden. Eine Datenbankverbindung via PDO wird mithilfe folgender Codezeile hergestellt:

<?php
$pdo = new PDO('DSN', 'username', 'password');
?>

Es empfiehlt sich, diese am Anfang eines jeden Scripts einzufügen, das Datenbankoperationen enthält.

Wir nutzen das PHP-Schlüsselwort new, um eine Instanz der PDO-Basisklasse zu erstellen. Deren Konstruktor erwartet grundsätzlich drei Parameter. Den Data Source Name (DSN) sowie einen Benutzernamen und das Passwort für die Datenbank – sofern vorhanden. Der DSN besteht in unserem Fall aus folgenden Parametern:

  • PDO-Datenbanktreiber: mysql
  • Datenbankserver (host=): localhost
  • Datenbankname (dbname=): test
  • Zeichensatz (charset=): utf8

Haben Sie für Ihre Datenbank keine Zugangsdaten definiert, nutzen Sie den Benutzernamen root und ein leeres Passwort:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
?>

Die Datenbankverbindung wird in der Variable $pdo gespeichert. Diese ermöglicht Ihnen, im späteren Verlauf des Programmcodes Bezug auf die Datenbankverbindung zu nehmen.

Wurde eine Verbindung zur Datenbank aufgebaut, können Sie im nachfolgenden Script-Code beliebig viele Abfragen an die Datenbank senden. Beenden Sie das Script, bricht auch die Datenbankverbindung ab.

Datenabfragen mit SELECT, INSERT, UPDATE und DELETE

Um Daten aus unserer Datenbank abzurufen, greifen wir auf die Datenbanksprache SQL zurück. Diese ist semantisch an die englische Sprache angelehnt und bewusst einfach gehalten. Die SQL-Syntax ist weitgehend selbsterklärend.

In SQL arbeitet man mit Statements, diese werden auch als Abfragen oder Anfragen bezeichnet.

Eine einfache SELECT-Query besteht beispielsweise aus folgenden Komponenten:

SELECT spalte FROM tabelle;

Sie definieren zunächst den SQL-Befehl SELECT und im Anschluss die jeweiligen Spalten und Tabellen, auf die sich der Befehl beziehen soll. Ein Strichpunkt schließt das Statement ab.

Zudem haben Sie die Möglichkeit, das Statement um eine optionale Bedingung sowie eine Sortier- oder Gruppierfunktion zu erweitern:

SELECT spalte FROM tabelle WHERE bedingung ORDER BY sortierreihenfolge;

Dabei gilt die Konvention, dass SQL-Befehle großgeschrieben werden, Datenbank-, Tabellen- und Feldnamen hingegen klein. Diese dient lediglich der Lesbarkeit. SQL ist prinzipiell eine formatfreie Sprache und macht somit keinen Unterschied zwischen Groß- und Kleinschreibung.

Nutzen Sie Tabellen- und Spaltennamen die bereits vordefinierten SQL-Schlüsselwörtern entsprechen (was nicht zu empfehlen ist), müssen diese in Backticks (``) gesetzt werden.

Wir veranschaulichen die Syntax einfacher SQL-Statements am Beispiel der Befehle SELECT, INSERT, UPDATE und DELETE.

SELECT

Den Befehl SELECT nutzen Sie, um ausgewählte Datenreihen (rows) aus einer beliebigen Anzahl von Tabellen abzurufen. Möchten Sie beispielsweise Vor- und Nachnamen sowie die E-Mail-Adressen aller Benutzer in der zuvor erstellten Beispieltabelle im Webbrowser ausgeben, erstellen Sie im Verzeichnis htdocs Ihrer XAMPP-Umgebung eine neue PHP-Datei text.php und fügen folgendes Skript ein:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'] . "<br />";
   echo "E-Mail: " . $row['email'] . "<br /><br />";
}
?>

Das Code-Beispiel liest sich wie folgt: Zunächst starten wir das Skript mit dem PHP-Start-Tag <?php. In Zeile 2 stellen wir die Verbindung zu unserer Datenbank test auf localhost her und speichern diese in der Variable $pdo. Das SQL-Statement mit dem Befehl SELECT findet sich in Zeile 3. Hier wird das Datenbank-Management-System angewiesen, die Spalten forename, surname und email aus der Tabelle users abzurufen. Dieses Statement speichern wir in der Variablen $sql.

Die Zeilen 4 bis 7 zeigen eine foreach-Schleife. Diese bietet uns die Möglichkeit, über ein beliebiges Array zu iterieren, sprich: eine Datenstruktur schrittweise durchzugehen. Welches Array wir iterieren möchten und wie die abgefragten Daten gespeichert werden sollen, definieren wir in Klammern hinter dem foreach-Konstrukt:

$pdo->query($sql) as $row

Die Variable $pdo spricht die gewünschte Datenbank über die in Zeile 2 definierte Verbindung an. Dieser senden wir mit der Funktion query() das in der Variable $sql gespeicherte SQL-Statement.

Der Webserver ruft somit die Spalten forename, surname und email der Tabelle users aus der Datenbank test ab und geht im Rahmen einer foreach-Schleife jede einzelne Tabellenzeile durch. Wohin die ausgelesenen Daten gespeichert werden sollen, definiert das PHP-Schlüsselwort as in der Array-Variable $row.

Im ersten Durchgang der foreach-Schleife sähe dieses Array folgendermaßen aus.

$row = array (
    forename => John,
    surname => Doe,
    email => john@doe.com
)

Teil der foreach-Schleife ist im aktuellen Beispiel zudem die Textausgabe via echo in jedem Schleifendurchgang. Wir gehen somit jede Zeile der Tabelle users einzeln durch, lesen die hinterlegten Daten für die im SQL-Statement definierten Spalten ein und geben diese über den Webbrowser aus.

Sollen alle Spalten einer Datentabelle ausgelesen werden, verwenden Sie im SQL-Statement den Asterisken (*) als Platzhalter.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM users";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'] . "<br />";
   echo "E-Mail: " . $row['email'] . "<br />";
   echo "Created at: " . $row['created_at'] . "<br /><br />";
}
?>

Damit haben wir die Möglichkeit, alle in users hinterlegten Daten im Rahmen des Scripts zu verwenden. In folgendem Screenshot wurde die Textausgabe um den Zeitstempel der Eintragserstellung ergänzt:

In beiden vorhergehenden Beispielen gibt uns der Webserver Nutzerdaten in der Reihenfolge aus, in der wir sie in die Tabelle users eingetragen haben (der ID entsprechend). Möchten Sie die Daten sortiert ausgeben, definieren Sie die Reihenfolge mithilfe des SQL-Schlüsselworts ORDER BY. In folgendem Beispiel werden die Daten gemäß der alphabetischen Reihenfolge der Vornamen ausgegeben:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users ORDER BY forename";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'] . "<br />";
   echo "E-Mail: " . $row['email'] . "<br /><br />";
}
?>

INSERT

Die Erstellung von Datenbankeinträgen erfolgt nur selten manuell via phpMyAdmin. In der Regel werden Daten im Rahmen der Skriptausführung vom Webserver in die Datenbank geschrieben – z. B., wenn ein Internetnutzer ein Onlineformular auf einer Website ausfüllt oder einen Kundenkommentar in einem Webshop hinterlässt. In beiden Fällen kommt im Hintergrund der SQL-Befehl INSERT zum Einsatz. Ein SQL-Statement mit dem Befehl INSERT wird nach folgendem Schema erstellt:

INSERT INTO tabelle (spalte1, spalte2, spalte3) VALUES (wert1, wert2, wert3);

Dieses liest sich wie folgt: Rufe die genannte Tabelle auf und füge die Werte 1, 2 und 3 in die Spalten 1, 2 und 3 ein.

Ein einfaches PHP-Skript, um unserer Bespieltabelle users einen weiteren Datensatz hinzuzufügen, könnte folgendermaßen aussehen:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "INSERT INTO users (forename, surname, email, password) 
VALUES ('Paddy', 'Irish', 'paddy@irish.com', 'qaywsx')";
if ($pdo->exec($sql) === 1)
  echo "New record created successfully";
?>

Zunächst bauen wir die Verbindung zur Datenbank auf und speichern diese in der Variable $pdo. Anschließend definieren wird das SQL-Statement und speichern dieses in der Variable $sql. In Zeile 5 nutzen wir den Pfeil-Operator (->), um auf die Variable $pdo zuzugreifen und mithilfe der Funktion exec() das in $sql gespeicherte SQL-Statement auszuführen.

Um sicherzugehen, dass unser Skript lediglich einen Datensatz in die Tabelle users eingefügt, überprüfen wir die Anzahl der betroffenen Zeilen mithilfe einer if-Bedingung. Diese sorgt dafür, dass der String New record created successfully nur dann über den Webbrowser ausgegeben wird, wenn die Anzahl der eingefügten Datensätze 1 beträgt. Wird das Skript erneut ausgeführt, bleibt die Meldung aus. Doppelte Einträge verhindern wir dadurch, dass der Wert email als UNIQUE definiert wurde.

Rufen wir die Übersicht unserer Beispieltabelle users in der Datenbank test auf, sehen wir, dass die Tabelle um den Datensatz 5 erweitert wurde. Die laufende Identifizierungsnummer und der Zeitstempel wurden wie beabsichtigt automatisch ergänzt.

UPDATE

Möchten Sie einen bestehenden Datensatz aktualisieren, nutzen Sie den SQL-Befehl UPDATE gemäß folgendem Grundschema:

UPDATE tabelle SET spalte1 = wert1, spalte2 = wert2 WHERE spalte3 = wert3

In Worten ausgedrückt bedeutet dieses SQL-Statement: Wähle die angegebenen Tabelle aus und ersetzte den Wert in Spalte1 durch Wert1 und den Wert in Spalte2 durch Wert2, sofern die Spalte3 den Wert3 enthält. Achtung: Vergessen Sie die Bedingung, überschreibt MySQL die betreffenden Felder in allen Datensätzen.

Wir haben es somit mit einem SQL-Statement zu tun, das eine Datenbankoperation an eine Bedingung knüpft. Übertragen auf unsere Beispieltabelle, ließe sich die E-Mail-Adresse des Nutzers John Doe durch folgendes PHP-Skript aktualisieren:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users 
SET email = 'john.doe@example.com', updated_at = NOW() WHERE id = 1";
if ($pdo->exec($sql) === 1)
  echo "Update successful";
?>

Im SQL-Statement definieren wir, dass der aktuelle Wert in der Spalte email durch den neuen Wert john.doe@example.com ersetzt werden soll, sofern der Wert in der Spalte id 1 beträgt. Wir aktualisieren somit lediglich den Datensatz mit dem Primärschlüssel 1. Darüber hinaus aktualisieren wir im selben SQL-Statement den Wert für die Spalte updated_at mithilfe der MySQL-Funktion now(), die den aktuellen Zeitstempel zurückgibt. Ausgeführt wird das SQL-Statement wie zuvor mit der Codezeile $pdo->exec($sql) im Rahmen einer if-Bedingung.

War das Update erfolgreich, sollte Ihnen phpMyAdmin im Tab „Browse“ die aktualisierte Tabelle anzeigen:

Im Beispiel haben wir eine E-Mail-Adresse aktualisiert und den Standardwert NULL in der Spalte updated_at durch einen Zeitstempel ersetzt. Der UPDATE-Befehl ermöglicht zudem, Werte aus einer Spalte in eine andere zu übertragen. Einsetzen lässt sich diese Operation beispielsweise, wenn wir unsere Beispieltabelle users um die Spalte email_registration erweitern. Dies gibt uns die Möglichkeit, zwischen zwei E-Mail-Adressen zu unterscheiden: eine, die bei der Registrierung verwendet wurde, und eine aktuelle Kontaktadresse, die sich mit der Zeit ändern kann. Zunächst sind beide Adressen jedoch gleich, sodass wir die Werte von einem Feld ins andere übertragen können. Dazu legen wir mit phpMyAdmin via „Add columns“ im Tab „Structure“ zunächst die neue Spalte email_registration an:

Um die Werte zu übertragen, nutzen wir folgendes UPDATE-Statement:

UPDATE users SET email_registration = email

Da wir alle Datensätze aktualisieren möchten, formulieren wir keine Bedingung für das Update.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users SET email_registration = email";
$affected = $pdo->exec($sql);
echo "$affected rows updated<br>";
?>

Wird das Skript über den Webserver ausgeführt, überträgt das Datenbank-Management-System die Werte der Spalte email für alle Datensätze in die Spalte email_registration.

DELETE

Löschen lassen sich Datenbankeinträge über den SQL-Befehl DELETE. Diesen nutzen Sie nach folgendem Schema:

DELETE FROM tabelle WHERE spalte = wert

Arbeiten Sie in Ihrer Datenbank mit IDs, bietet es sich an, anhand dieser zu löschende Einträge zu identifizieren. Möchten Sie beispielsweise den Eintrag 5 in unserer Beispieltabelle löschen, gehen Sie wie folgt vor:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "DELETE FROM users WHERE id = 5";
$affected = $pdo->exec($sql);
echo "$affected rows updated<br>";
?>

Der SQL-Befehl DELETE löscht stets eine ganze Datenbankzeile. Möchten Sie lediglich Werte in bestimmten Spalten eines Datensatzes löschen, realisieren Sie dies mit einem UPDATE-Statement. Mit UPDATE tabelle SET spalte = NULL WHERE … können Sie einer Spalte den Wert NULL zuweisen – vorausgesetzt natürlich, dass Sie für die betreffende Spalte den NULL-Wert zugelassen haben.

Prepared Statements

Mit PDO lassen sich Datenbankoperationen als sogenannte Prepared Statements umsetzen. Diese „vorgefertigten Abfragen“ sind heute gängige Praxis in der Webentwicklung und werden von allen modernen Datenbank-Management-Systemen unterstützt.

In den bisherigen Beispielen haben wir Parameterwerte direkt im SQL-Statement übergeben. Prepared Statements hingegen arbeiten mit Platzhaltern, die erst nachträglich mit Werten besetzt werden. Dies ermöglicht es dem Datenbank-Management-System, die Gültigkeit von Parametern zu prüfen, bevor diese verarbeitet werden. Prepared Statements bieten, sofern diese im Quelltext konstant umgesetzt werden, einen effektiven Schutz gegen SQL-Injection. Bei diesem Angriffsmuster erstellen oder verändern Hacker SQL-Befehle, um an sensible Daten zu gelangen, Daten zu überschreiben oder eigene Befehle in ein System einzubringen.

SQL-Injection basiert auf einer bekannten Sicherheitslücke im Umgang mit SQL-Datenbanken: Werden Benutzereingaben beispielsweise via $_GET mit statischen Parametern übergeben, bietet dies Angreifern die Möglichkeit, den Input mit Metazeichen anzureichern, die zu ungewollten Effekten führen können, sofern diese ohne Maskierung in den SQL-Interpreter gelangen. Dem lässt sich durch parametrisierte Abfragen effektiv vorbeugen. Prepared Statements fungieren dabei als Templates für SQL-Befehle, die getrennt von den eigentlichen Parametern an die Datenbank übermittelt werden. Diese validiert die übergebenen Daten, maskiert Metazeichen automatisch und fügt die Parameter anstelle der Platzhalter in das SQL-Statement ein.

Prepared Statements bieten neben dem Sicherheitsaspekt zudem einen Performance-Vorteil. Dieser zeigt sich, wenn derselbe SQL-Befehl in einer Schleife mit verschiedenen Parametern ausgeführt werden soll. Nachdem ein Prepared Statement einmal geparst wurde, liegt dieses im Datenbanksystem vor und muss dort lediglich mit neuen Parametern ausgeführt werden. Komplizierte Abfragen lassen sich so deutlich beschleunigen.

In PDO werden Prepared Statements mithilfe der Funktion prepare() umgesetzt. Diese bereitet ein Statement für die Ausführung vor und gibt ein Statement-Objekt zurück. Als Platzhalter für die jeweiligen Werte kommt entweder das Fragezeichen (?) oder ein benannter Parameter (Named Parameter) zum Einsatz.

Prepared Statements mit unbenannten Parametern

Folgendes Codebeispiel zeigt die Datenbankoperation INSERT als Prepared Statement mit unbenannten Parametern:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');

// SQL-Statement vorbereiten
$statement = $pdo->prepare("INSERT INTO users (forename, surname, email, password) 
VALUES (?, ?, ?, ?)");

// Parameter binden
$statement ->bindParam(1, $forename);
$statement ->bindParam(2, $surname);
$statement ->bindParam(3, $email);
$statement ->bindParam(4, $password);

// Datensätze einfügen
$forename = "Anders";
$surname = "Andersen";
$email = "anders@andersen.com";
$password = "mnbvc";
if ($statement->execute())
  echo "New record $forename created successfully<br>";

$forename = "Matti";
$surname = "Meikäläinen";
$email = "matti@meikäläinen.com";
$password = "jhgfd";

// Status ausgeben
if ($statement->execute())
  echo "New record $forename created successfully<br>";
?>

Zunächst erzeugen wir mithilfe der Funktion prepare() ein Statement-Objekt der gewünschten Abfrage und speichern dieses im Array $statement. Statt konkreter Parameterwerte kommt das Fragezeichen als Platzhalter zum Einsatz.

Beinhaltet ein SQL-Statement lediglich Platzhalter, müssen separat übergebene Werte im nachfolgenden Code an dieses gebunden werden. In PHP kommt dazu die Funktion bindParam() zum Einsatz. Wir nutzen den Pfeil-Operator (->), um auf die bindParam()-Methode des Objekts $statement zuzugreifen und diesem Variablen zuzuweisen (1 entspricht dem ersten Fragezeichen, 2 dem zweiten usw.).

Das so erstellte SQL-Template kann nun beliebig oft mit den gewünschten Parametern ausgeführt werden. Im aktuellen Beispiel definieren wir Variablenwerte für zwei Datensätze. Die Ausführung des vorgefertigten SQL-Statements erfolgt für jeden Datensatz via execute().

Prepared Statements mit benannten Parametern

Übersichtlicher als der Fragezeichenplatzhalter sind benannte Parameter. Dabei handelt es sich um benutzerdefinierte Platzhalter, die Sie nach Bedarf nach folgendem Schema benennen können:

:beispiel

Benannte Parameter dürfen weder Leerzeichen noch Bindestriche (-) enthalten. Verwenden Sie stattdessen den Unterstrich (_).

Folgendes Beispiel zeigt die Datenbankoperation INSERT als Prepared Statement mit benannten Parametern:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');

// SQL-Statement vorbereiten
$statement = $pdo->prepare("INSERT INTO users (forename, surname, email, password) 
VALUES (:forename, :surname, :email, :password)");

// Parameter binden
$statement ->bindParam(':forename', $forename);
$statement ->bindParam(':surname', $surname);
$statement ->bindParam(':email', $email);
$statement ->bindParam(':password', $password);

// Datensätze einfügen
$forename = "Anders";
$surname = "Andersen";
$email = "anders@andersen.com";
$password = "mnbvc";

if ($statement->execute())
  echo "New record $forename created successfully<br>";

$forename = "Matti";
$surname = "Meikäläinen";
$email = "matti@meikäläinen.com";
$password = "jhgfd";
$statement->execute();

// Status ausgeben
if ($statement->execute())
  echo "New record $forename created successfully<br>";
?>

Im Prepared Statement finden sich die benannten Parameter :forename, :surname, :email und :password. Diese binden wir via bindParam() an die Variablen $forename, $surname, $email und $password. Im aktuellen Beispiel haben wir sowohl die Parameter als auch die Variablen den Spaltennamen unserer Beispieltabelle entsprechend benannt. Durch die Syntax vorgegeben wird dies nicht. Eine einheitliche Benennung ist im Sinne eines gut lesbaren Quellcodes jedoch zu empfehlen. Die Zuweisung der Variablenwerte sowie die Ausführung des SQL-Statements erfolgt analog zum vorhergehenden Beispiel.

Datums- und Zeitfunktionen in MySQL

MySQL und MariaDB unterstützen diverse Funktionen, um mit Datums- und Zeitangaben zu arbeiten. Eine vollständige Liste finden Sie hier. In unserem MySQL-Tutorial für Einsteiger beschränken wir uns auf eine Auswahl.

Datums- und Zeitfunktion Beschreibung
CURRENT_TIMESTAMP() / NOW() Im Bespiel zum SQL-Befehl UPDATE haben Sie bereits die Funktion NOW() kennengelernt. Bei dieser handelt es sich lediglich um ein Synonym der Funktion CURRENT_TIMESTAMP(). Zur Anwendung kommt die Funktion immer dann, wenn im Rahmen einer Datenbankoperation das aktuelle Datum inklusive Uhrzeit festgehalten werden soll.
CURDATE() / CURRENT_DATE() Die Funktion CURDATE() gibt das aktuelle Datum zurück.
CURTIME() / CURRENT_TIME() Die Funktion CURTIME() gibt die aktuelle Uhrzeit zurück.
DAY() / DAYOFMONTH() Gibt den Tag des Monats zurück (0–31); benötigt ein Datum oder Timestamp als Argument
DAYOFWEEK() Gibt den Tag der Woche zurück (1 = Sonntag); benötigt ein Datum oder Timestamp als Argument
MONTH() Gibt den Monat zurück (1–12); benötigt ein Datum oder Timestamp als Argument
YEAR() Gibt eine Jahreszahl zurück (1000–9999, 0); benötigt ein Datum oder Timestamp als Argument
DATE() Extrahiert das Datum aus einer Zeit- oder Datumsangabe; benötigt ein Datum oder Timestamp als Argument
TIME() Extrahiert die Uhrzeit aus seiner Zeit oder Datumsangabe; benötigt ein Datum oder Timestamp als Argument
DATE_FORMAT() Formatiert eine Zeit oder Datumsangabe gemäß der angegeben Parameter; benötigt ein Datum oder Timestamp als Argument

Ein mögliches Anwendungsszenario für Zeit- und Datumsfunktionen in MySQL sind beispielsweise Datenbankabfragen, bei denen alle Datensätze ausgelesen werden sollen, die an einem bestimmten Tag erstellt wurden.

Folgendes Skript gibt uns alle Datensätze unserer Beispieltabelle users aus, die heute erstellt wurden:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, email FROM users WHERE DATE(created_at) = CURDATE()";
foreach ($pdo->query($sql) as $row) {
   echo $row['forename'] . " " . $row['surname'] . "<br />";
   echo "E-Mail: " . $row['email'] . "<br /><br />";
}
?>

Um lediglich die Einträge von heute auszugeben, nutzen wir im SQL-Statement folgende Bedingung:

DATE(created_at) = CURDATE()

Zunächst extrahieren wir mithilfe der Funktion DATE() das Datum aus dem in der Spalte created_at gespeicherten Zeitstempel und gleichen diesen im nächsten Schritt mit dem aktuellen Datum ab. Der Befehl SELECT wählt somit lediglich die Einträge aus, deren Zeitstempel mit dem heutigen Datum übereinstimmen.

Alternativ könnten wir den Eintrag auswählen, den wir am 16.12.2016 aktualisiert haben. Dazu müssen wir lediglich die Bedingung unseres SQL-Statements anpassen:

SELECT forename, surname, email FROM users WHERE DATE(updated_at) = '2016-12-16'

In diesem Fall wird die aus dem Zeitstempel extrahierte Datumsangabe mit einem konkreten Datum abgeglichen. Darüber hinaus können Sie Abfragen auf ein bestimmtes Jahr, einen Monat oder eine Tag eingrenzen.

Folgendes Statement bezieht sich auf alle Einträge in der Tabelle users, die im Dezember erstellt wurden:

SELECT forename, surname, email FROM users WHERE month(created_at) = 12";

Neben dem Gleichheitszeichen unterstützt SQL in Bedingungen zudem folgende Vergleichsoperatoren:

Vergleichsoperator Beschreibung
= ist gleich
< kleiner als
> größer als
<= kleiner oder gleich
>= größer oder gleich
!= ungleich

Darüber hinaus lassen sich mehrere Bedingungen mit logischen Operatoren verknüpfen:

Logischer Operator Beschreibung
OR bzw. Logisches Oder
AND bzw. && Logisches Und

Folgendes Statement wählt beispielsweise alle Einträge aus, die nach Februar und vor April erstellt wurden:

SELECT forename, surname, email FROM users WHERE MONTH(created_at) > 2 AND MONTH(created_at) < 4";

Bisher haben wir Datums- und Zeitangaben in unserer Datenbank im vorgegeben Format gespeichert. Mit MySQL und MariaDB sind Sie auf dieses jedoch nicht festgelegt. Die Funktion DATE_FORMAT() bietet Ihnen die Möglichkeit, Daten und Uhrzeiten mit optionalen Parametern beliebig zu formatieren.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT forename, surname, DATE_FORMAT(created_at,'%D %M %Y') AS created_at FROM users WHERE id=1";
  foreach ($pdo->query($sql) as $row) {
  echo $row['forename'] . " " . $row['surname'] . " your profile was created at: " . $row['created_at'] . ".<br /><br />";
}
?>

Die nachstehende Tabelle zeigt mögliche Parameter für die Funktion DATE_FORMAT() gemäß MySQL-Dokumentation.

Parameter Beschreibung Wertebereich/Beispiele
%c Numerische Monatsangabe mit 1 oder 2 Stellen 0 bis 12
%d Numerische Angabe des Tags im Monat mit 2 Stellen 00 bis 31
%e Numerische Angabe des Tags im Monat mit 1 oder 2 Stellen 0 bis 31
%H Stundenanzeige mit 2 Stellen 00 bis 23
%i Numerische Minutenangabe mit 2 Stellen 00 bis 59
%j Numerische Angabe des Tags im Jahr mit 3 Stellen 001 bis 366
%k Numerische Stundenangabe mit 1 oder 2 Stellen 0 bis 23
%M Name des Monats im aktuellen Gebietsschema January, February, March etc.
%m Numerische Monatsangabe mit 2 Stellen 00 bis 12
%s Numerische Sekundenanzeige mit 2 Stellen 00 bis 59
%T Zeit im 24-Stundenfomat (Kurzform für '%H:%i:%S'. HH:MM:SS
%u Nummer der Woche im Jahr, beginnend mit Montag 00 bis 53
%W Name des Wochentags im aktuellen Gebietsschema Monday, Tuesday etc.
%w Wochentag 0 = Sunday, 6 = Saturday
%Y Jahreszahl mit 4 Stellen z. B. 2016
%y Jahreszahl mit 2 Stellen z. B. 16

MySQL-Fehlermeldungen

Lässt sich ein Skript nicht wie gewünscht ausführen, liegt dies in der Regel an syntaktischen Fehlern im Quellcode oder falsch benannten Tabellen, Spalten und Variablen. Der Datenbankserver spielt in diesem Fall jedoch nicht zwangsläufig eine Fehlermeldung aus. Oft bleibt das gewünschte Ergebnis ohne Hinweis auf die fehlgeschlagene Operation aus.

Mit errorInfo() stellt PDO daher eine Funktion bereit, mit der sich erweiterte Fehlerinformationen zur letzten Datenbankoperation gezielt abrufen lassen – beispielsweise, um diese über den Webbrowser auszugeben.

In folgendem Skript zur Aktualisierung der E-Mail-Adresse kommt die Funktion errorInfo() in Kombination mit einer if-Schleife zum Einsatz. Deren Bedingung ist die korrekte Ausführung des SQL-Statements. Wird dieses fehlerfrei ausgeführt, gibt der Webserver den String Update successful aus. Andernfalls wird der unter else aufgeführte Code ausgeführt.

Im aktuellen Beispiel informieren wir den Nutzer darüber, dass ein SQL-Fehler aufgetreten ist und geben das betreffende SQL-Statement sowie erweiterte Fehlerinformationen via errorInfo() aus:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$statement = $pdo -> prepare("UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1");
$statement ->bindParam(':password', $password);
$password = "YXDli_89%s";
if ($statement ->execute()){
   echo "Update successful";
} else {
   echo "SQL Error <br />";
   echo $statement->queryString."<br />";
   echo $statement->errorInfo()[2];
}
?>

Führen wir das Skript über den Webserver aus, bekommen wir folgende Information ausgegeben:

SQL Error 
UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1
Table 'test.user' doesn't exist

Der SQL-Befehl UPDATE bezieht sich auf eine Tabelle mit dem Namen user. Unsere Beispieltabelle haben wir jedoch mit users benannt. Der SQL-Server kann die angeforderte Tabelle daher nicht finden und gibt die Meldung Table 'test.user' doesn't exist aus. Fehlerursache ist somit lediglich ein Tippfehler im SQL-Statement, der schnell behoben ist.

Bei den Rückgabewerten der Funktion errorInfo() handelt es sich um ein Array, das drei Elemente enthält:

[0] = SQL-Fehlercode

[1] = Treiberspezifischer Fehlercode

[2] = Treiberspezifische Fehlermeldung

Welche Informationen Sie via errorInfo() abrufen, definieren Sie durch die Angabe des gewünschten Elements in eckigen Klammern.

In der Praxis werden detaillierte Fehlerinformationen jedoch nur selten über den Webbrowser ausgegeben. Endanwender können mit diesen Angaben in der Regel nur wenig anfangen. Potenzielle Angreifer hingegen nutzen Fehlermeldungen mitunter, um SQL-Abfragen nachzuvollziehen und so Schwachstellen einer Anwendung ausfindig zu machen. Es empfiehlt sich daher, Nutzer nur allgemein über einen Fehlerfall zu informieren und konkrete Fehlerinformationen intern zu speichern. Umsetzen lässt sich dies beispielsweise folgendermaßen:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$statement = $pdo->prepare("UPDATE user SET password = :password, updated_at = NOW() WHERE id = 1");
$statement->bindParam(':password', $password);
$password = "YXDli_89%s";
if ($statement->execute()) {
   echo "Update successful";
} else {
  echo "Leider ist ein Fehler bei der Aktualisierung Ihres Passworts aufgetreten. Bitte kontaktieren Sie unseren Administrator unter: admin@website.de.";
  $error = DATE("Y-m-d H:i:s") . " - " . __FILE__ . " - " . $statement->queryString . " - " . $statement->errorInfo()[2] . " \r\n";
  file_put_contents("sqlerrors.log", $error, FILE_APPEND); 
}
?>

Statt den Rückgabewert der Funktion errorInfo() via echo im Webbrowser auszugeben, speichern wir diesen zusammen mit dem aktuellen Zeitstempel sowie dem Pfad zur Datei und dem betreffenden SQL-Statement in der Variable $error.

Die PHP-Funktion DATE() gibt den Unix-Zeitstempel im angegebenen Format aus. Die sogenannte magische Konstante __FILE__ liefert uns den vollständigen Pfad zur Datei test.php. Das aktuelle SQL-Statement rufen wir wie im vorherigen Beispiel via $statement->queryString ab. Im Anschluss werden die in $error gespeicherten Daten mithilfe der Funktion file_put_contents() als Text in der Datei sqlerrors.log im Ordner htdocs gespeichert.

JOIN

Wie im Kapitel zu relationalen Datenbanken angesprochen, können wir Daten aus mehreren Tabellen gleichzeitig abfragen. Nachdem Sie nun mit den grundlegenden Datenbankoperationen vertraut sind, greifen wir dies erneut auf und demonstrieren Ihnen, wie Sie verschiedene Tabellen Ihrer Datenbank in einem Join (dt. „Verbindung“ oder „Verbund“) verknüpfen.

Die Zusammenführung von Tabellen im Rahmen einer Abfrage erfolgt mithilfe des SQL-Befehls JOIN. Dabei werden zwei oder mehr normalisierte Tabellen über gemeinsame Spalten verbunden. Realisieren lässt sich dies via Fremdschlüssel.

Wir demonstrieren die Verknüpfung von Datenbanktabellen an folgendem Beispiel:

Die Tabelle zeigt eine Auswahl unserer Lieblingssongs aus den 1960er Jahren und soll als Negativbeispiel für ein schlechtes Datenbankdesign dienen.

Auf den ersten Blick fällt auf, dass die Tabelle zahlreiche redundante Datenfelder aufweist. Diese beseitigen wir, in dem wir die Daten im Rahmen der Normalisierung in separate Tabellen aufteilen und mithilfe von Fremdschlüsseln verknüpfen.

Normalformen

Gutes Datenbankdesign zeichnet sich durch ein Minimum an Redundanz aus. Vermeiden lassen sich doppelte Einträge durch die Normalisierung von Datentabellen. Im Rahmen des relationalen Datenbankmodells haben sich drei aufeinander aufbauende Normalformen etabliert, die Ihnen feste Regeln für die optimale Datenstrukturierung vorgeben.

1. Normalform

Eine Tabelle entspricht der 1. Normalform, wenn alle Attributwerte atomar sind. Als atomar gelten Attributwerte, wenn diese lediglich eine Information enthalten. Verdeutlichen lässt sich dies an unserem Negativbeispiel.

Betrachten Sie beispielsweise die Spalten album_title und interpret in der Tabelle album. Statt jede in der Tabelle enthaltene Information in einer separaten Spalte aufzuführen, haben wir es uns vermeintlich leicht gemacht und Informationen zum Erscheinungsjahr eines Albums sowie zum Bestehen einer Band einfach in Klammern hinter dem Albumtitel bzw. der Interpretenangabe notiert. Doch diese Schludrigkeit rächt sich spätestens dann, wenn wir beispielsweise alle Titel abfragen wollen, die in einem bestimmten Jahr veröffentlicht wurden.

Wir empfehlen daher Datentabellen stets nach den Regeln der 1. Normalform zu erstellen. Für unser Tabellenbeispiel würde dies folgendermaßen aussehen:

album

album_id

album_title

released

interpret

years_active

track

title

1

Revolver

1966

The Beatles

1960–1970

2

Eleanor Rigby

1

Revolver

1966

The Beatles

1960–1970

8

Good Day Sunshine

2

Abbey Road

1969

The Beatles

1960–1970

1

Come Together

2

Abbey Road

1969

The Beatles

1960–1970

7

Here Comes the Sun

3

Beggars Banquet

1968

The Rolling Stones

1962–present

1

Sympathy for the Devil

3

Beggars Banquet

1968

The Rolling Stones

1962–present

6

Street Fighting Man

4

Sticky Fingers

1971

The Rolling Stones

1962–present

1

Brown Sugar

4

Sticky Fingers

1971

The Rolling Stones

1962–present

3

Wild Horses

Sämtliche Daten sind nun separiert und somit ohne weiteres auslesbar. Unsere Tabelle enthält jedoch nach wie vor Redundanzen. Wie sich diese beseitigen lassen, zeigen wir in den nächsten Schritten.

2. Normalform

Eine Tabelle entspricht der 2. Normalform, wenn alle Bedingungen der 1. Normalform erfüllt sind und jedes Nichtschlüsselattribut vom gesamten Primärschlüssel der Tabelle voll funktional abhängig ist.

Oft enthalten Datentabellen lediglich eine Spalte, die als Primärschlüssel fungiert. Tabellen dieser Art entsprechen automatisch der 2. Normalform, wenn die Bedingungen der 1. Normalform erfüllt sind. Mitunter kommt es aber vor, dass sich der Primärschlüssel einer Tabelle aus zwei Spalten ergibt. Dies ist bei unserem Tabellenbeispiel der Fall.

Um einen gewünschten Titel aus der title-Spalte der Tabelle title abzurufen, bräuchten wir zum einen die album_id als auch die in der Spalte track hinterlegte Titelnummer. Der Titel Sympathy for the Devil ließe sich beispielweise durch den Primärschlüssel album_ID = 3 AND track = 1 abrufen. Wir haben es also mit einem zusammengesetzten Primärschlüssel zu tun. Dieser wird jedoch ausschließlich für Abfragen benötigt, die sich auf die title-Spalte beziehen. Die Spalten album_title, released, interpret und years_active sind ausschließlich von der album_id abhängig. Es besteht für diese Spalten somit keine volle funktionale Abhängigkeit vom gesamten Primärschlüssel. Die Bedingungen der 2. Normalform sind nicht erfüllt.

Dies lässt sich ändern, indem wir die Spalte title in eine neue Tabelle auslagern und durch einen Fremdschlüssel (album_id) mit der Ausgangstabelle verknüpfen:

album

album_id

album_title

released

interpret

years_active

1

Revolver

1966

The Beatles

1960–1970

2

Abbey Road

1969

The Beatles

1960–1970

3

Beggars Banquet

1968

The Rolling Stones

1962–present

4

Sticky Fingers

1971

The Rolling Stones

1962–present

title

album_id

track

title

1

2

Eleanor Rigby

1

8

Good Day Sunshine

2

1

Come Together

2

7

Here Comes the Sun

3

1

Sympathy for the Devil

3

6

Street Fighting Man

4

1

Brown Sugar

4

3

Wild Horses

Die überarbeitete Tabelle album beinhaltet lediglich einen einteiligen Primärschlüssel und erfüllt somit automatisch die Bedingungen der 2. Normalform. Die neue Tabelle title beinhaltet lediglich die Nicht-Schlüsselspalte title. Diese ist von beiden Teilen des Primärschlüssels (album_id und track) voll funktional abhängig und entspricht somit ebenfalls der 2. Normalform.

Doch auch in der 2. Normalform enthält unsere Datentabelle album redundante Einträge.

3. Normalform

Soll eine Tabelle der 3. Normalform entsprechen, müssen alle Bedingungen der 2. Normalform (und somit auch der 1. Normalform) erfüllt sein. Zudem gilt, dass kein Nichtschlüsselattribut in transitiver Abhängigkeit zu einem Schlüsselattribut stehen darf. Diese Bedingung hört sich kompliziert an, lässt sich jedoch mit wenigen Worten erklären: Eine transitive Abhängigkeit besteht immer dann, wenn ein Nichtschlüsselattribut von einem anderen Nichtschlüsselattribut abhängt.

Genau dies trifft auf unsere Beispieltabelle album zu. Hier finden sich die Spalten interpret und years_active. Während sich der Interpret durch die album_id ermitteln lässt, ist die Jahresangabe des Bandbestehens wiederum vom Interpreten und somit transitiv von der album_id abhängig. Dies hat den Nachteil, dass das Datenbank-Management-System jedes Mal, wenn ein neues Album eines bereist aufgeführten Interpreten eingetragen wird, automatisch einen redundanten Wert in der Spalte years_active abspeichert.

Um die Bedingungen der 3. Normalform zu erfüllen und somit alle Redundanzen aus unserer Tabelle zu entfernen, müssen wir auch die Spalte interpret inklusive years_active in eine separate Tabelle auslagern und via Fremdschlüssel (interpret_id) mit der Ausgangstabelle album verknüpfen.

Wir erhalten somit drei normalisierte Tabellen: interpret, album und title.

interpret

interpret_id

interpret

years_active

1

The Beatles

1960–1970

2

The Rolling Stones

1962–present

album

album_id

interpret_id

album_title

released

1

1

Revolver

1966

2

1

Abbey Road

1969

3

2

Beggars Banquet

1968

4

2

Sticky Fingers

1971

title

album_id

track

title

1

2

Eleanor Rigby

1

8

Good Day Sunshine

2

1

Come Together

2

7

Here Comes the Sun

3

1

Sympathy for the Devil

3

6

Street Fighting Man

4

1

Brown Sugar

4

3

Wild Horses

Möchten wir nun einen bestimmten Titel in unserer Datenbank inklusive der Informationen zum Album sowie zum Interpreten ausgeben, müssen wir die drei separaten Tabellen mithilfe des SQL-Befehls JOIN und der jeweiligen Fremdschlüssel verbinden.

Fremdschlüssel via phpMyAdmin definieren

Sofern Sie als Datenbank-Engine InnoDB gewählt haben, definieren Sie Fremdschlüsselbeziehungen bequem über die grafische Benutzeroberfläche Ihrer Administrationssoftware phpMyAdmin. Dabei kann der Primärschlüssel einer Tabelle als Fremdschlüssel in beliebig vielen anderen Tabellen zum Einsatz kommen.

In unserem Beispiel benötigen wir zwei Verbindungen, um die drei normalisierten Tabellen album, interpret und title zu verknüpfen:

  • Für die erste Verbindung nutzen wir den Primärschlüssel album_id der Tabelle album als Fremdschlüssel in der Tabelle title.
  • Für die zweite Verbindung nutzen wir den Primärschlüssel interpret_id der Tabelle interpret als Fremdschlüssel in der Tabelle album.

Veranschaulichen lassen sich die Fremdschlüsselbeziehungen durch folgende Grafik:

Bei der Verknüpfung von Datentabellen gilt: Eine Spalte, die als Fremdschlüssel fungieren soll, muss mit den Attributen UNIQUE oder INDEX versehen werden.

Die Beziehung zwischen Primärschlüssel und Fremdschlüssel entspricht in der Regel dem Beziehungstyp 1:n. Jedes Datenfeld in der Primärschlüssel-Spalte der Tabelle A steht mit beliebig vielen (n) Datenfeldern in der Fremdschlüssel-Spalte der Tabelle B in Beziehung. Doch jedes Datenfeld in der Fremdschlüssel-Spalte der Tabelle B bezieht sich immer auf genau ein Datenfeld in der Primärschlüssel-Spalte der Tabelle A. Beispielsweise haben wir in der Primärschlüssel-Spalte album_id in der Tabelle album vier Einträge, die über den Fremdschlüssel title.album_id mit acht Einträgen der Tabelle title verknüpft sind.

Um die gewünschten Verknüpfungen einzurichten, legen wir die Tabellen album, interpret und title in phpMyAdmin an und definieren unsere Primärschlüssel im Rahmen der Tabellenerstellung wie bereits beschrieben über die Option „Index“. Achten sie zudem darauf, dass Spalten, die später als Fremdschlüssel fungieren sollen, ebenfalls über die Index-Option als INDEX oder UNIQUE markiert werden. Für 1:n-Beziehungen eignet sich aber nur INDEX, denn die Werte in einem UNIQUE-Feld dürfen sich schließlich nicht wiederholen.

Im nächsten Schritt definieren wir die Fremdschlüssel. Wir demonstrieren dies am Beispiel der Tabelle album. Dazu wählen wir die Tabelle im Navigation-Panel aus und rufen den Tab Structure in der Menüleiste auf. Hier findet sich die Schaltfläche „Relation view“ (Beziehungsansicht):

Fremdschlüsselbeziehungen definieren Sie in der Beziehungsansicht einer Datentabelle über das Eingabefeld „Foreign key constraints“:

In der Tabelle album soll die Spalte interpret_id als Fremdschlüssel fungieren, der auf dem Primärschlüssel interpret_id der Tabelle interpret beruht.

Wir wählen somit im Dropdown-Menü unter „Column“ die Spalte interpret_id als Fremdschlüssel aus. Beachten Sie, dass hier nur Spalten aufgeführt werden, die als INDEX, UNIQUE oder PRIMARY markiert wurden. Im dreiteiligen Eingabefeld „Foreign key constraint (InnoDB)“ bestimmen wir, auf welchem Primärschlüssel welcher Tabelle in welcher Datenbank unser Fremdschlüssel beruhen soll. Wir wählen wie folgt:

Datenbank: test

Tabelle: interpret

Primärschlüssel: interpret_id

Das Feld „Constraint name“ kann leer bleiben. Das Datenbank-Management-System vergibt hier automatisch einen Namen. Definieren müssen Sie jedoch, wie sich eine Tabelle mit Fremdschlüssel verhält, sobald der dem Fremdschlüssel zugrundeliegende Primärschlüssel verändert oder gelöscht wird.

Wird beispielsweise ein Interpret aus der Elterntabelle interpret gelöscht, verschwindet auch der mit diesem Eintrag verbundene Primärschlüssel. Es gilt somit zu klären, was mit Einträgen passieren soll, die sich auf diesen Eintrag via Fremdschlüssel beziehen – in unserem Beispiel also Alben eines Interpreten.

Um das Verhalten einer Tabelle mit Fremdschlüssel im Fall eines UPDATE oder DELETE zu bestimmen, stehen in MySQL bzw. MariaDB vier Optionen zur Verfügung.

  • RESTRICT: Die Option RESTRICT schließt eine Veränderung der Elterntabelle aus, sofern andere Tabellen existieren, die auf diese verweisen. In unserem Fall könnte ein Datensatz in der Tabelle interpret nicht gelöscht werden, wenn Datensätze in der Tabelle album mit diesem verknüpft sind.
  • CASCADE: Die Option CASCADE sorgt dafür, dass eine Veränderung in der Elterntabelle an alle Tabellen weitergeben wird, die sich auf diese Elterntabelle beziehen. Setzen wir beispielsweise die interpret_id des Interpreten The Rolling Stones von 2 auf 8, wird diese Änderung bei der Foreign-Key-Option CASCADE auch für alle Tabellen übernommen, die die interpret_id als Fremdschlüssel nutzen. Wird ein Eintrag in der Elterntabelle gelöscht, führt dies dazu, dass auch alle mit diesem Eintrag verbundenen Datensätze in anderen Tabellen gelöscht werden. Achtung: Auf diese Weise kann das Löschen eines einzelnen Eintrags das Verschwinden zahlreicher Datensätze nach sich ziehen.
  • SET NULL: Wählen Sie die Option SET NULL, wird der Wert in der Fremdschlüsselspalte auf NULL gesetzt, sobald sich der Primärschlüssel in der Elterntabelle ändert oder dieser gelöscht wird.
  • NO ACTION: In MySQL ist die Option NO ACTION ein Äquivalent der Option RESTRICT.

Haben Sie die gewünschte Option für die Fremdschlüsselbeziehung definiert, bestätigen Sie Ihre Eingabe mit einem Klick auf „Save“. Das Datenbank-Management-System vergibt automatisch einen Namen für die neu angelegte Beziehung.

JOIN-Typen in MySQL und MariaDB

Fremdschlüsselbeziehungen ermöglichen es Ihnen, Daten aus verschiedenen Tabellen gleichzeitig mit nur einem SQL-Statement abzurufen. Dazu stehen Ihnen in MySQL und MariaDB vier JOIN-Typen zur Verfügung:

  • INNER JOIN: Bei einem INNER JOIN sucht das Datenbank-Management-System nach gemeinsamen Einträgen in beiden via JOIN verbunden Tabellen. Es werden nur die Datensätze eingelesen, in denen es Übereinstimmungen gibt, sprich: in denen die Werte in den verknüpfenden Spalten (Primärschlüssel und Fremdschlüssel) in beiden Tabellen übereinstimmen.
  • OUTER JOIN: Bei einem OUTER JOIN unterschiedet man zwischen linker und rechter Datentabelle. Anders als beim INNER JOIN werden nicht nur die Datensätze eingelesen, bei denen sich Übereinstimmungen in beiden Tabellen finden, sondern zusätzlich auch alle übrigen Datensätze der rechten bzw. der linken Tabelle.
  • LEFT JOIN: Es werden alle Datensätze der linken Tabelle eingelesen sowie alle Datensätze der rechten Tabelle, in der sich Übereinstimmungen finden.
  • RIGHT JOIN: Es werden alle Datensätze der rechten Tabelle eingelesen sowie alle Datensätze der linken Tabelle, in der sich Übereinstimmungen finden.

Im Rahmen unseres MySQL-Einsteiger-Tutorials beschränken wir auf den INNER JOIN.

Die Syntax eines INNER JOINs entspricht folgendem Grundschema:

SELECT * FROM tabelle1
INNER JOIN tabelle2 ON tabelle1.fremdschlüssel = tabelle2.primärschlüssel
WHERE spalte = wert

Der SQL-Befehl SELECT in Kombination mit dem Platzhalter * weist das Datenbank-Management-System an, die Werte aller Spalten auszulesen, für die die Bedingungen der ON- und der WHERE-Klausel gelten.

Da es sich um einen INNER JOIN handelt, werden nur die Datensätze, bei denen sich eine Übereinstimmung zwischen dem Fremdschlüssel der tabelle1 und dem Primärschlüssel der tabelle2 findet, aus der Datenbank geholt. Darüber hinaus lässt sich mithilfe der WHERE-Klausel eine optionale Filterfunktion definieren.

Wir verdeutlichen dies an einem Beispiel, das sich auf unsere normalisierten Tabellen album, interpret und title bezieht:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id";
foreach ($pdo->query($sql) as $row) {
   echo $row['album_title'] . " was released by " . $row['interpret'] . " in " . $row['released'] . ".<br /><br />";
}
?>

Das Beispielskript zeigt einen INNER JOIN, bei dem die Tabelle album mit der Tabelle interpret verbunden wird. Wir lesen lediglich die Datensätze aus, bei denen eine Übereinstimmung zwischen Primärschlüssel und Fremdschlüssel besteht.

album.interpret_id = interpret.interpret_id

In unserem Datenbestand sind das alle Datensätze (ein LEFT oder RIGHT JOIN hätte daher das gleiche Ergebnis gehabt). Im Anschluss geben wir die eingelesenen Werte mithilfe einer foreach-Schleife und dem Sprachkonstrukt echo im Browser aus.

Im Rahmen eines SQL-Statements haben wir somit die Interpretenangabe aus der Tabelle interpret und die Angaben zum Albumtitel und zum Erscheinungsjahr der Platte aus der Tabelle album ausgelesen.

Welche Datensätze des Joins ausgegeben werden, lässt sich mit einer Bedingung in der WHERE-Klausel einschränken. Möchten wir beispielsweise lediglich die Alben ausgeben, die im Jahr 1968 erschienen sind, können wir folgendermaßen vorgehen:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id WHERE released = 1968";
foreach ($pdo->query($sql) as $row) {
   echo $row['album_title'] . " was released by " . $row['interpret'] . " in " . $row['released'] . ".<br /><br />";
}
?>

Durch die Bedingung WHERE released = 1968 schränken wir die Ausgabe im Browser auf ein Album ein. Beggars Banquet von den Rolling Stones ist das einzige Album in unserer noch recht überschaubaren Datenbank, das im Jahr 1968 veröffentlicht wurde.

Mithilfe des JOIN-Befehls lassen sich beliebig viele Tabellen zur einen Datenverbund zusammenschließen. Im folgenden Beispiel verknüpfen wir die Tabelle album in einem INNER JOIN mit den Tabellen interpret und title, um uns sämtliche Informationen zu den in der Datenbank gespeicherten Musiktiteln ausgeben zu lassen.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id 
INNER JOIN title ON album.album_id = title.album_id";
foreach ($pdo->query($sql) as $row) {
   echo $row['title'] . " was released by " . $row['interpret'] . " as Track " . $row['track'] . " on " . $row['album_title'] . " in " . $row['released'] . ".<br /><br />";
}
?>

Auch hier können wir nach Bedarf eine WHERE-Klausel mit einer Filterfunktion definieren. Beispielsweise, wenn wir lediglich die Informationen zu Track 7 auf dem Album „Abbey Road“ ausgeben möchten.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM album
INNER JOIN interpret ON album.interpret_id = interpret.interpret_id 
INNER JOIN title ON album.album_id = title.album_id WHERE track = 7 AND album_title = 'Abbey Road'";
foreach ($pdo->query($sql) as $row) {
   echo $row['title'] . " was released by " . $row['interpret'] . " as Track " . $row['track'] . " on " . $row['album_title'] . " in " . $row['released'] . ".<br /><br />";
}
?>

Beachten Sie, dass wir es bei der Tabelle title mit einem mehrteiligen Primärschlüssel zu tun haben. Möchten wir auf einen bestimmten Titel verweisen, benötigen wir neben der Track-Nummer die album_id, die zusammen mit dem Titel des Albums in der Tabelle album hinterlegt ist.

Vom Einsteiger zum Profi

Unser MySQL-Tutorial für Einsteiger versteht sich als Crashkurs, der Ihnen die Grundlagen SQL-basierter Datenbanksysteme näherbringt und an praxisrelevanten Beispielen einfache Datenbankoperationen veranschaulicht. Sollte Ihr Interesse über die hier dargestellten Anwendungsmöglichkeiten hinausgehen, empfehlen wir die bereits im Einführungskapitel verlinkten Dokumentationen der DBMS MySQL und MariaDB. Außerdem finden Sie im Internet zahlreiche Websites, die Tutorials und Anwendungsbeispiele zum beliebten Datenbank-Management-System anbieten.

Zu empfehlen ist zudem die Internetplattform Stack Overflow, auf der sich eine rege Nutzercommunity von mehr als 6,5 Millionen Entwicklern über aktuelle Fragen und Problemstellungen der Software-Entwicklung austauscht. Und natürlich stehen Ihnen auch hier im IONOS Digital Guide viele weiterführende Artikel zum Thema Datenbanken zur Verfügung – Sie finden sie über die unten aufgeführten Tags.