SQL-Einführung

SQL-Einführung mit MySQL
SQL-Einführung
mit MySQL
Karsten Brodmann
Dezember 2013
Vorwort
Das vorliegende Skript entstand im Rahmen einer von mir gehaltenen SQL-Schulung auf Basis von Oracle 11g. Da aber viele Anwender gerne am heimischen PC lernen und dort nur die
wenigsten eine Oracle-Datenbank zur Verfügung haben, habe ich das Skript umgeschrieben,
so dass MySQL als relationales Datenbanksystem zum Ausprobieren und Nachvollziehen der
hier präsentierten Inhalte genutzt werden kann. MySQL ist quasi für jedes nennenswerte Betriebssystem kostenlos verfügbar. Linux-Anwender haben es besonders leicht, da MySQL in
der Regel in den Repositories der verschiedenen Distributoren enthalten ist und so einfach installiert werden kann. Im Übrigen ist MySQL sehr standardkonform, so dass die damit erlangten Kenntnisse schnell und einfach auf andere Datenbankmanagementsysteme übertragen
werden können.
Ich verwende hier MySQL 5.5 auf einem Notebook mit Ubuntu 13.10. Alle Beispiele sind getestet und sollten bei korrekter Installation von MySQL und entsprechender Eingabe der Befehle genau so funktionieren, wie hier abgedruckt.
Stand: 27.12.2013
1
SQL-Einführung mit MySQL
1 Grundlagen
1.1 Relationale Datenbanken
Wenn wir umgangssprachlich von einer Datenbank sprechen, meinen wir damit in der Regel
ein System zur elektronischen Datenverwaltung1. Hierbei ist zwischen der eigentlichen Datenbank, den zu verwaltenden Daten, und dem Datenbankmanagementsystem, der Verwaltungssoftware, zu unterscheiden. Unterschiedliche Datenbankmanagementsysteme organisieren die
von ihnen zu verwaltenden Daten auf verschiedene Weisen. Es gibt jedoch grundsätzliche
Modellierungsansätze, wie Daten organisiert werden. Danach unterscheiden wir im Wesentlichen nach relationalen Datenbankmodellen, hierarchischen Datenbankmodellen, Netzwerkund objektorientierten Datenbankmodellen. Die in der Praxis größte Bedeutung haben die relationalen Datenbanksysteme, alle anderen genießen mehr oder minder ein Nischendasein für
Spezialanwendungen. Die derzeit am Markt verbreitetsten Systeme sind Oracle, Microsoft
SQL Server und MySQL. MySQL ist, im Gegensatz zu den beiden anderen genannten Systemen, auch kostenlos verfügbar. Im Linux- und Webserver-Bereich erfreut sich MySQL äußerst großer Beliebtheit und Verbreitung. Alle drei Systeme sind Vertreter für relationale Datenbankmanagementsysteme (RDBMS).
E.F. CODD, ein Mathematiker in den Diensten von IBM, veröffentlichte im Jahr 1970 die
theoretischen Grundlagen für das relationale Datenbankmodell. Es basiert auf der so genannten Relationenalgebra, einer speziellen mathematischen Disziplin. Die innerhalb dieses Modells verwendeten Begrifflichkeiten sind unter Umständen etwas gewöhnungsbedürftig, weshalb sie im Folgenden kurz eingeführt und erläutert werden sollen.
Eine logisch zusammenhängende Objektmenge (Relation) wird in Form zweidimensionaler
Matrizen dargestellt. Der Volksmund spricht hier kurz und bündig von Tabellen, in welchen
Datensätze zeilenweise abgelegt werden. Die einzelnen Spalten einer Tabelle bezeichnen die
Elemente eines Datensatzes. Man spricht in diesem Zusammenhang auch von Feldern, Attributen oder Eigenschaften. Jede Spalte einer Tabelle stellt immer nur einen bestimmten Attributtyp dar, so dass alle Datensätze einer Tabelle einen identischen Satzaufbau besitzen.
In einer Tabelle muss jeder Datensatz eindeutig identifizierbar sein. Es dürfen sich keine zwei
oder mehr identische Datensätze in einer Tabelle befinden. Um dies zu gewährleisten, gibt es
so genannte Primär- oder Identifikationsschlüssel. Ein Primärschlüssel besteht aus einem oder
mehreren Attributen. Alle Attribute bzw. Attributkombinationen, die eine eindeutige Identifizierbarkeit eines Datensatzes gewährleisten können, nennt man Kandidatenschlüssel.
Primärschlüssel sind hinsichtlich ihres Datentyps oftmals numerischer Natur, wie z.B. Artikelnummern, Kundennummern oder ähnliches mehr. Dies ist jedoch keineswegs zwingend. Fast
jeder mögliche Datentyp, der von einem Datenbankmanagementsystem unterstützt wird, kann
zur Bildung eines Schlüssels herangezogen werden. Entsprechende Details hierzu sind der
Dokumentation des jeweiligen Datenbankmanagementsystems zu entnehmen.
Einige Tabellen enthalten Spalten oder Spaltenkombinationen, deren Inhalte eine Teilmenge
der Identifikationsschlüssel einer anderen Tabelle bilden, weil sie die entsprechenden Datensätze referenzieren sollen. Diese nennt man Fremdschlüssel.
1
Hinweis: Excel-Tabellen sind keine Datenbanken, auch wenn mancher Zeitgenosse das vielleicht meint und
sagt.
Stand: 27.12.2013
2
SQL-Einführung mit MySQL
Die Auswahl von Tabellenzeilen oder -spalten erfolgt mittels mathematischer Verfahren
(Stichwort: Mengenlehre). Die Auswahl einer Teilmenge bezüglich der vorhandenen Spalten
heißt Projektion. Eine Zeilenauswahl ist eine Selektion. Beide sind miteinander kombinierbar.
Das relationale Konzept sieht keine festen Verbindungen zwischen verschiedenen Tabellen
vor. Die Beziehungen werden durch die Abfrage bestimmt und haben temporären Charakter2.
1.2 Entwurf relationaler Datenbanken
In einer Datenbank werden in der Regel größere Datenmengen abgelegt, die gruppiert werden
können/sollen, in gewisser Weise in Beziehung zueinander stehen, für verschiedene
Benutzer(gruppen) individuell aufbereitet werden müssen usw.
Die Daten einer Datenbank stellen inhaltlich und strukturell eine Abstraktion der realen Welt
dar. Sie bilden die für eine bestimmte Problemstellung relevanten Dinge ab, die mittels der
Datenbank und ggf. weiterer Werkzeuge bearbeitet werden sollen. Obgleich durch die Konzentration auf das Wesentliche bereits stark vereinfacht, muss es nicht notwendigerweise trivial sein, ein der Problemstellung angemessenes Datenmodell zu entwerfen und zu implementieren.
Im Software-Engineering wurden zur Bewältigung dieser und anderer Problematiken Konzepte entwickelt, die den Entwicklern helfen sollen, ihre Arbeit überschaubarer, portabler, effizienter zu machen – kurz: zu optimieren. Auch für den Entwurf sowie den Aufbau relationaler
Datenbanksysteme wurden vergleichbare Konzepte entwickelt. Deren wichtigster Vertreter,
der sich in quasi allen kommerziellen Implementierungen von Datenbankmanagementsystemen wiederfindet, ist das Drei-Ebenen-Modell.
1.3 Drei-Ebenen-Modell
Das Drei-Ebenen-Modell ist keine Erfindung der Neuzeit. Es wurde bereits 1975 vom amerikanischen Normungsinstitut ANSI/SPARC3 vorgestellt. Im Drei-Ebenen-Modell werden folgende Ebenen unterschieden: die externe, die konzeptuelle und die interne Ebene. Die Inhalte
und Aufgaben der drei Ebenen sind klar voneinander abgegrenzt.
So können sich Datenbankdesigner, Datenbankprogrammierer und andere, die am Aufbau einer Datenbank beteiligt sind, auf jeweils die Aufgaben konzentrieren, die ihnen obliegen. Sie
brauchen sich nicht stets und ständig Gedanken über das Gesamtproblem (z.B. die physische
Speicherung) und die dazugehörige Komplexität machen. Ebenso braucht sich kein Anwender
um mögliche Änderungen der physischen Datenspeicherung kümmern u. ä. m.
Das externe Schema beschreibt die Benutzersicht einer Datenbank. Es erlaubt dem Anwender
sich auf Datenstrukturen und deren Inhalte zu konzentrieren, die mit Hilfe einer bestimmten
Sprachform erstellt und modifiziert werden. Üblicherweise spricht man hier von einer so genannten DML (Data Manipulation Language).
2
3
Soweit die Theorie – moderne Datenbanksysteme unterstützen (meistens) die Definition von Regeln (Constraints), die bei der Eingabe, Änderung oder Löschung von Dateninhalten, die referentielle Integrität zwischen den Daten verschiedener Tabellen gewährleisten. So wird sichergestellt, dass in einer Tabelle keine
Datensätze existieren, die mittels eines Fremdschlüssels versuchen, Datensätze in einer weiteren Tabelle re ferenzieren, die dort nicht vorhanden sind. In Bezug auf mögliche Abfragen ergeben sich hierdurch keine
Einschränkungen.
ANSI/SPARC = American National Standards Institute/Standards Planning and Requirements Committee
Stand: 27.12.2013
3
SQL-Einführung mit MySQL
Externe
Ebene
Sicht 1
Sicht 2
Sicht 3
...
Sicht n
Logische
Ebene
Konzeptuelle
Ebene
Konzeptuelles Schema
Physische
Ebene
Interne
Ebene
Internes Schema
Abbildung 1: Drei-Ebenen-Modell
Das interne Schema ist demgegenüber für die rein physischen Aspekte eines Datenbanksystems verantwortlich. Hier sind Zugriffsrechte, die Such- und Sortierfunktionen und anderes
mehr implementiert.
Das verbindende Element zwischen diesen beiden sehr verschiedenen Modellen ist das konzeptuelle Schema. Es stellt den logischen Zusammenhang und somit die Verknüpfung zwischen den Benutzern und den physischen Gegebenheiten der Datenbank dar. Hier werden die
Tabellenstrukturen sowie auch alle sonstigen Datenbankobjekte und die zugehörigen Zugriffspfade verwaltet. Die zugehörige Sprache wird als DDL (Data Definition Language) bezeichnet.
1.4 Entity-Relationship-Modell
Es gibt verschiedene Methoden, die den Entwurf relationaler Datenbanken unterstützen. Die
wohl verbreitetste Methode für Entwurf und Darstellung relationaler Datenbankmodelle, ist
das Entity-Relationship-Modell (ER-Modell). Hierbei handelt es sich um eine grafische Darstellung der Daten(strukturen) bzw. der zwischen ihnen bestehenden Beziehungen innerhalb
einer Datenbank. Der ER-Entwurf beschreibt Objekte (Entities) der Datenbank und ihre Beziehungen (Relations) zueinander.
Stand: 27.12.2013
4
SQL-Einführung mit MySQL
Objekte können z.B. die Bücher und Leser einer Bibliotheksverwaltung sein. Objekte können
aber auch abstrakt sein, wie z.B. die Erteilung einer Strafe für ein verspätet zurückgegebenes
Buch. Wie in der Realität besitzen Objekte Eigenschaften, auch Merkmale oder Attribute genannt, die sie beschreiben.
Logisch zusammenhängende Objekte, also Objekte eines bestimmten Typs, können in Objektmengen zusammengefasst werden. In einem relationalen Datenbanksystem entspricht dies (in
der Regel) einer Tabelle mit den in ihr enthaltenen Datensätzen. Grafisch werden Objektmengen (Entities) als Rechtecke dargestellt.
buchnr
autor
titel
gruppe
leihfrist
ausleihzahl
lesernr
buch
buchnr
verleih
ausleihdatum
leihdauer
leser
lesernr
nachname
vorname
wohnort
ausleihzahl
eintrittsdatum
Abbildung 2: Ausschnitt eines ER-Modells einer Leihbibliothek
Für eine Bibliotheksverwaltung benötigt man beispielsweise Leser und Bücher. Es stellt sich
sogleich die Frage nach den Attributen, die diese Objekte im Rahmen einer Bibliotheksverwaltung sinnvoll beschreiben. Beginnen wir mit den Büchern: Buchnummer, Autor, Titel,
Gruppe (z. B. Unterhaltung, Klassik, Wissen) sowie eine Leihfrist und ein Zähler, der angibt,
wie oft ein bestimmtes Buch bereits verliehen wurde, sollten Minimalanforderungen sein. Die
Farbe des Buchdeckels ist dagegen von eher untergeordnetem Interesse. Die Leser sollten
Stand: 27.12.2013
5
SQL-Einführung mit MySQL
mindestens über folgende Attribute verfügen: Lesernummer, Nachname, Vorname, Wohnort 4,
Anzahl bereits ausgeliehener Bücher und Eintrittsdatum.
Die grafische Darstellungsform von Attributen ist gewöhnlich ein Kreis oder eine Ellipse, die
jeweils mittels einer geraden Linie mit der zugehörigen Entitätsmenge verbunden werden.
Identifikationsschlüssel werden durch Unterstreichung der Bezeichner gekennzeichnet. Je
nach verwendetem Modellierungswerkzeug mag die Darstellung jedoch differieren. In der Regel sind die Darstellungen aber einfach zu lesen und es erschließt sich auch ohne großes Rätseln, was Entitäten, Attribute bzw. Schlüssel usw. sind.
Neben den einzelnen Objekten mit ihren Eigenschaften sollen im ER-Modell auch die Beziehungen der Objekte zueinander abgebildet werden. Die grafische Darstellung einer Beziehung
ist die Raute. Diese wird mittels gerader Linien mit jeweils zwei Objekten (Rechtecken) verbunden, deren Beziehung dokumentiert werden soll. Auch Beziehungen haben Eigenschaften,
die wiederum mittels Kreisen oder Ellipsen dargestellt werden.
Wie im realen Leben, werden drei Beziehungstypen unterschieden: 1:1, 1:n, m:n. Die analogen zwischenmenschlichen Beziehungen wollen hier aber außer Acht lassen – das wäre ein
weites Feld. Stattdessen sehen wir uns ein paar weniger brisante Beispiele für die genannten
Beziehungstypen an, wie wir sie in der betrieblichen Praxis häufig antreffen:
Tabelle 1: Beziehungstypen relationaler Datenbanken
Beziehung
Beschreibung / Beispiel
1:1
Ein Artikel besitzt eine Artikelnummer in der Artikelstammtabelle und tritt
mit dieser Nummer genau einmal in einer zugehörigen Preistabelle auf.
1:n
Ein Kunde hat mehrere Fahrzeuge bei einem Autohändler gekauft. Seine
Kundennummer taucht einmal un der Kundentabelle auf. In der
Verkaufstabelle ist seine Kundennummer n mal aufgeführt, einmal für jeden
getätigten Fahrzeugkauf.
m:n
In verschiedenen Projekten arbeiten jeweils mehrere Mitarbeiter. Jeder dieser
Mitarbeiter kann seinerseits an mehreren Projekten beteiligt sein.
1.5 Exkurs: MySQL-Datentypen
Es ist bereits zu diesem Zeitpunkt sinnvoll, sich Gedanken über Datentypen und Wertebereiche für die verschiedenen Attribute zu machen.
Tabelle 2: MySQL-Standarddatentypen
Datentyp
Beschreibung
CHAR(n)
Speichert eine fixe Anzahl von alphanumerischen Zeichen. Das
Maximum liegt bei n = 255 Zeichen.
VARCHAR(n)
Kette alphanumerischer Zeichen mit variabler Länge. n kann maximal
den Wert 65.535 annehmen. Bei der Verwendung von utf8 als
Zeichensatz, bei welchem Zeichen bis zu drei Byte Speicher belegen
können, beträgt die maximale Zeichenkettenlänge 21.844 Zeichen.
4
Dem Autor ist die schwachsinnige Modellierung, ein einzelnes Attribut für alle Komponenten der Adresse zu
verwenden, bewusst. Er würde dies in der Praxis niemals so implementieren!
Stand: 27.12.2013
6
SQL-Einführung mit MySQL
Datentyp
Beschreibung
DECIMAL(p,s)
Numerischer Datentyp, mit Präzision p und Skalierungsfaktor s (oder
Nachkommastellen). Der maximale Wert für p ist 65. Zum Verständnis
dieses Datentyps ein Beispiel: Der Wertebereich von DECIMAL(10,2)
reicht von -99.999.999,99 bis 99.999.999,99.
INT
Ganzzahliger Datentyp (4 Byte). Der Wertebereich reicht von -232 bis
232-1.
DATE
Datentyp zur Speicherung von Datumsformaten. Der Wertebereich reicht
vom 01.01.1000 bis zum 31.12.9999. Datumswerte werden in der Form
YYYY-MM-DD eingegeben.
TIMESTAMP
Datentyp zur Speicherung von Datum und Uhrzeit. Wird auch benötigt,
um Datums-/Zeitwerte als datenbankseitige Vorgabewerte in Tabellen
speichern zu können. Format: YYYY-MM-DD HH-MM-SS.
Neben dieser kleinen Auswahl wichtiger Standarddatentypen kennt MySQL noch viele weitere. So gibt es Datentypen zur Speicherung von so genannten Binary Large Objects und viele
andere mehr. Zur Erlangung weiteren Erkenntnisgewinns sei ein Blick in die Dokumentation
empfohlen.
1.6 Normalisierung
Um Unvollständigkeiten, unerwünschte Redundanzen und Inkonsistenzen im Datenbankaufbau bzw. den Datenbankinhalten zu vermeiden, werden Tabellen und Attribute in eine mathematisch eindeutige Form gebracht. Hierbei geht man gewöhnlich schrittweise vor. Die Ergebnisse der Zwischenschritte werden 1. bis 5. Normalform genannt. Letztere bildet das Ziel des
so genannten Normalisierungsprozesses. In der Praxis werden meistens lediglich die ersten
drei Normalformen verwendet. Die letzten beiden haben eher akademischen Charakter und
werden deshalb im Weiteren nicht betrachtet.
Zur Illustration der einzelnen Normalisierungsschritte soll ein Beispiel aus einem Schulungsbetrieb dienen. Dozenten geben an unterschiedlichen Standorten Kurse für Studierende. In einer Datenbank soll festgehalten werden, welcher Dozent an welchem Standort welchen Kurs
in welchem Raum gibt und wie viele Studenten den jeweiligen Kurs gebucht haben. Bei drei
Dozenten und fünf Kursen könnte eine nicht normalisierte Darstellung der Tabelle dozenten
etwa so aussehen:
dozenten(do1,do2,do3,ku1,ku2,ku3,ku4,ku5,teiln,plz,ort,raum)
do# ist der jeweilige Dozent, ku# bezeichnet jeweils einen Kurs und teiln ist die Anzahl
der angemeldeten Teilnehmer. Diese Tabellenmodellierung ist untragbar. Für jeden neuen Dozenten oder Kurs müsste die Tabellenstruktur geändert werden. Deshalb besagt die erste Normalisierungsregel: Wiederholungsgruppen sind zusammenzufassen. Wiederholungsgruppen sind in unserem Beispiel die verschiedenen Dozenten sowie die Kurse.
Daraus folgt für die o.g. Tabelle dozenten eine neue Form, die gleich mit ein paar Beispieldaten gefüllt werden soll.
Stand: 27.12.2013
7
SQL-Einführung mit MySQL
Tabelle 3: dozenten in 1. Normalform
doznr
kursnr
teiln
plz
ort
raum
1
1
25
51515
Hagen
a
1
2
32
51515
Hagen
b
1
3
18
51515
Hagen
c
2
1
25
44225
Dortmund
a
2
3
18
44225
Dortmund
c
2
5
29
44225
Dortmund
d
3
2
32
44225
Dortmund
b
Die Tabelle erfüllt nun die 1. Normalform in Bezug auf unsere Problemstellung – alle Wiederholungsgruppen sind eliminiert.
Die 2. Normalform verlangt, in einem zweiten Schritt nach zusammengesetzten Schlüsseln zu
suchen. Alle zusammengesetzten Schlüssel haben eine eigene Tabelle zu bilden. Die von
einfachen Schlüsseln abhängigen Merkmale sind ebenfalls in eine eigene Tabelle zu
schreiben. Anders ausgedrückt bedeutet dies die Suche nach Feldern, die nicht von einer
Schlüsselkombination sondern nur von Teilschlüsseln abhängen.
Offensichtlich hängen die Teilnehmerzahlen mit den Kursen zusammen, weshalb wir diesem
Sachverhalt eine eigene Tabelle spendieren wollen. Eine diesbezügliche Beziehung zum
Wohnort des Dozenten ist nicht sinnvoll anzunehmen. Der Wohnort nebst Postleitzahl ist jeweils vom Dozenten abhängig. Auch dies führt zu der Erkenntnis, eine Tabelle zur Abbildung
dieses Sachverhalts kreieren zu müssen, um die 2. Normalform zu erreichen. Dozenten, Kurse
und zugewiesene Räumlichkeit bilden einen zusammengesetzten Schlüssel, bekommen also
ebenfalls eine eigene Tabelle.
Tabelle 4: dozenten in 2. Normalform
Tabelle 5: kurse in 2. Normalform
doznr
plz
ort
kursnr
teiln
1
51515
Hagen
1
25
2
44225
Dortmund
2
32
3
44225
Dortmund
3
18
5
29
Tabelle 6: kurszuordnung in 2. Normalform
kursnr
doznr
raum
1
1
a
2
1
b
3
1
c
1
2
a
3
2
c
Stand: 27.12.2013
8
SQL-Einführung mit MySQL
kursnr
doznr
raum
5
2
d
2
3
b
Aus einer Tabelle sind jetzt drei Tabellen geworden. Beachte: jede höhere Normalform erfüllt
auch stets die Kriterien der vorhergehenden Normalform. Die 2. Normalform erfüllt also auch
immer die Bedingungen der 1. Normalform. Gleiches gilt dann analog für die folgende 3.
Normalform sowie auch die 4. und 5. Normalform, die uns hier aber weiterhin rätselhaft bleiben werden.
Jetzt liegt die Datenbank in der 2. Normalform vor. In der 3. Normalform müssen alle
Nicht-Schlüssel-Attribute direkt vom Schlüssel abhängig sein. Es dürfen keine transitiven Nicht-Schlüssel-Attribute in den Tabellen existieren.
Transitivität bedeutet:
A→ B → C ⇔ A→ C
Direkt abhängig bedeutet:
A→B→C⇔C→A
Prüfen wir dies für die Tabelle dozenten:
doznr → plz → ort ⇔ doznr → ort
(Transitivität: JA)
doznr → plz → ort ⇔ ort → doznr
(Direkte Abhängigkeit: NEIN)
Wenn doznr und ort direkt abhängig wären, müsste
3 → 44225 → Dortmund ⇔ Dortmund → 3
gelten. Das tut es jedoch nicht, weil z.B. auch Dozent 2 aus Dortmund kommt. Deshalb ist die
Tabelle dozenten nochmals aufzuteilen.
Tabelle 8: dozenten in 3. Normalform
Tabelle 7: staedte in 3. Normalform
doznr
plz
plz
ort
1
51515
51515
Hagen
2
44225
44225
Dortmund
3
44225
2 SQL – Structured Query Language
SQL steht für Structured Query Language, was soviel wie strukturierte Abfragesprache bedeutet. Trotz des Namens sind die Fähigkeiten von SQL nicht auf bloße Abfragen beschränkt.
Mittels SQL können ebenso Datenstrukturen erzeugt und modifiziert werden. Gleiches gilt für
die Handhabung von Dateninhalten.
SQL ist 1986 als ANSI-Standard verabschiedet worden. Trotz dieses Standards haben die verschiedenen Datenbankhersteller eigene Erweiterungen implementiert. Diese sollen vorhandene wie vermeintliche Unzulänglichkeiten von SQL beseitigen. Es empfiehlt sich daher ein
Blick in die jeweilige Dokumentation, will man ein bestimmtes Datenbankmanagementsystem optimal nutzen.
Um SQL in den Reigen der verschiedenen Programmiersprachen einzuordnen, denn um eine
solche handelt es sich letztlich, mag die folgende Auflistung hilfreich sein, die in bekannter
Stand: 27.12.2013
9
SQL-Einführung mit MySQL
Weise Sprachen nach Generationen unterteilt. Die Gliederung ist historisch begründet und
spiegelt die Fortschritte der IT bezüglich der Programmierung oder „wie sage ich dem Computer, was er tun soll“ wider.
1. Generation: Maschinencode (krude Folgen von Nullen und Einsen, anfänglich ein
Haufen von Ein-/Aus-Schaltern, die in ihrer Gesamtheit ein Programm darstell(t)en)
2. Generation: Assembler (Zusammenfassung von Nullen und Einsen unter dem Dach eines einigermaßen verständlichen Synonyms, um das Ganze für den Menschen leichter
lesbar zu machen – Bsp.: Add AX,6, um zum Inhalt des 32-Bit Akkumulationsregisters A den Wert 6 zu addieren)
3. Generation: Problemorientierte Sprachen (Pascal, Modula, C, … – i.d.R. an die englische Sprache angelehnte Syntax, bei der sich hinter jedem Befehl in der jeweiligen
Sprache eine ganze Reihe von Assemblerbefehlen verbirgt. Bsp.: Der printf-Befehl
in C würde, in Assembler ausgedrückt, locker eine DIN-A4-Seite füllen, wollte man
den Code ausdrucken).
4. Generation: Anwendersprachen (SQL, Natural, … – SQL lernen wir jetzt kennen)
5. Generation: logische und funktionale Sprachen (Lisp, Prolog, Miranda, … – nur für
Freaks und wissenschaftliche Mitarbeiter von Hochschulen – können wir hier vergessen)
SQL zählt zu den so genannten Anwendersprachen. Diese Klassifikation sagt allerdings noch
nichts über das Wesen der Sprache aus. SQL ist eine logische, mengenorientierte, nicht-prozedurale Sprache. Für Kenner einer gewöhnlichen Programmiersprache, womit eine aus der 3.
Generation gemeint ist, mag SQL daher etwas gewöhnungsbedürftig sein. Die wesentlichen
Merkmale der problemorientierten Sprachen sind Datenstrukturen (Variablen, Datentypen)
und Kontrollstrukturen (Strecke, Verzweigung/Fallunterscheidung, Wiederholung), die aber
SQL gerade nicht anbietet5. An die Stelle konkreter Einzelanweisungen, die vom Rechner
Schritt für Schritt abgearbeitet werden und am Ende (hoffentlich) das gewünschte Ergebnis
erzeugen, tritt eine logische Beschreibung dessen, was als Resultat der Verarbeitung herauskommen soll. Wie dies im Einzelnen genau geschieht, ist nicht Sache des Anwenders, sondern
des SQL-Interpreters. Diese höhere Abstraktionsebene erklärt letztlich den Generationssprung
von den gewöhnlichen Sprachen zu SQL.
Der Unterschied soll an einem einfachen Beispiel illustriert werden. Aus der o.g. Tabelle dozenten sollen alle Datensätze ausgelesen und angezeigt werden (vgl. Tabelle 9 auf der
nächsten Seite).
Bei einer 3GL-Sprache ist das Öffnen der Datei mit den Dozentendaten sowie das abschließende Schließen der Datei explizit anzugeben. Dazwischen findet die eigentliche Datenverarbeitung statt.
In SQL werden diese lästigen Details, wie im Einzelnen vorzugehen ist, vom Anwender ferngehalten. Der sagt einfach, ins Deutsche übersetzt: „Zeige mir alle Datensätze aus der Tabelle
dozenten!“
So einfach das Beispiel auch aussehen mag, so einfach ist SQL letztlich aber doch nicht. Zwar
wird insbesondere in den Medien immer wieder von der Einfachheit bei gleichzeitiger Mäch5
Im Rahmen ihrer individuellen Spracherweiterungen hat dies dennoch jeder mir bekannte Datenbankhersteller getan.
Stand: 27.12.2013
10
SQL-Einführung mit MySQL
tigkeit von SQL berichtet, dennoch sei festgehalten: Komplexe Abfragen sind in der Regel
nicht trivial!
Tabelle 9: Gegenüberstellung 3GL (Pseudocode) und 4GL (SQL)
3GL
4GL (SQL)
open(dozenten);
while(not eof(dozenten)) {
read(dozenten, dozent);
print(dozent);
}
close(dozenten);
SELECT * FROM dozenten;
Ein weiterer Punkt, den man im Hinterkopf behalten sollte ist, dass SQL keine vollständige
Programmiersprache ist. D.h., SQL ist nicht universell für alle nur erdenklichen Programmierprobleme einsetzbar. SQL beschränkt sich auf die Handhabung von relationalen Datenbanken.
Diese Einschränkungen sind aber weder ein bedauerliches Versehen, noch ein offensichtlicher
Mangel. Die Sprache wurde von Anfang an so konzipiert, dass sie in andere Programmiersprachen eingebettet werden kann (Embedded SQL). Diese Absicht ist so tief verwurzelt, dass
sogar der ANSI-SQL-Standard einen Mechanismus vorschreibt, wie SQL in eine problemorientierte Sprache zu integrieren ist.
SQL-Befehle lassen sich in drei Klassen einteilen:
1. DDL-Befehle (Data Definition Language) erzeugen (Datenbanken, Tabellen, Sichten,
Indizes usw., verändern oder löschen sie.
2. DCL-Befehle (Data Control Language) dienen der Administration von Benutzern/Benutzergruppen und deren Zuriffsrechten auf die verschiedenen Datenbankobjekte.
3. DML-Befehle (Data Manipulation Language) werden für Abfragen sowie für das Einfügen, Ändern oder Löschen von Dateninhalten verwendet.
Diese Klassifikation ist eher formaler Natur. Ihre Kenntnis erleichtert uns aber die Übersicht
sowie das Verständnis der verschiedenen SQL-Befehle.
Tabelle 10: SQL-Befehlsklassen
DDL
DML
DCL
CREATE
GRANT
SELECT
(Erzeugen ein Objekts)
(Rechte gewähren)
(Daten auswählen)
ALTER
REVOKE
INSERT
(Ändern eines Objekts)
(Rechte entziehen)
(Daten einfügen)
DROP
DELETE
(Löschen eines Objekts)
(Daten löschen)
RENAME
UPDATE
(Umbenennen eines Objekts)
(Daten ändern)
Stand: 27.12.2013
11
SQL-Einführung mit MySQL
3 Vom Entwurf zur Datenbank
Der Weg zur praktischen Arbeit mit SQL führt, sofern noch nicht vorhanden, zwangsweise
über die Erstellung einer Datenbank. Der hierzu erforderliche Befehl variiert je nach Datenbanksystem. Angemessen wäre beispielsweise:
CREATE DATABASE <datenbankname>;
Was konkret zur Erstellung einer Datenbnak erforderlich ist, sollte im Handbuch des jeweiligen Datenbankmanagementsystems nachgelesen werden. Hier gibt es nahezu unendlich viele
Wege, die nach Rom führen. Das hängt ganz vom Datenbankhersteller ab. Einen wirklichen
Standard gibt es hierbei nicht.
Der nächste Schritt besteht darin, Tabellen in der Datenbank anzulegen. Als Beispiel soll uns
hier die bereits oben angesprochene Bibliotheksverwaltung dienen.
3.1 Beispiel: Bibliotheksveraltung – ER-Modell
Der Hauptzweck einer Bibliothek besteht im Verleihen von Büchern an Leser. Bücher, Leser
und Verleihvorgang wurden bereits in Abbildung 2 modelliert. Das Verleihen bzw. Vormerken
von Büchern für bestimmte Leser stellt Beziehungen zwischen Büchern und Lesern dar. Um
Realitätsnähe bemüht, sollen Strafgebühren für verspätete Rückgaben möglich sein. Ständig
säumige Leser werden gesperrt.
Das vollständige ER-Modell zu dieser Problemstellung ist in Abbildung 3 auf der nächsten
Seite dargestellt. Es wurde mit dem Modellierungswerkzeug von MySQL erstellt und hat daher eine von der bereits bekannten Notation abweichende Darstellung.
Jeder Leser kann mehrere Bücher gleichzeitig ausleihen (1:n-Beziehung). Ein Buch kann aber
auch von mehreren Lesern ausgeliehen werden (1:n-Beziehung). Insgesamt ergibt sich zwischen Lesern und Büchern also eine m:n-Beziehung. Jedes Buch kann jedoch zu einem bestimmten Zeitpunkt nur von je einem Leser ausgeliehen sein. Im Fall einer Ausleihe wird sowohl beim Buch als auch beim Leser jeweils ein Zähler inkrementiert. Der Zähler des Buches
gibt Auskunft darüber, wie oft ein Buch bislang verliehen wurde. Der Zähler des Lesers gibt
die Anzahl der von einem Leser im Lauf seiner Bibliothekszugehörigkeit geliehenen Bücher
wider. Es können nur Bücher ausgeliehen werden, die eine Leihfrist größer als 0 Tage besitzen. Ein Buch mit einer Leihfrist von 0 Tagen ist ein Präsenzexemplar.
Im Falle einer Ausleihe werden das Ausleihdatum und die Ausleihdauer gespeichert. Wird
letztere überschritten, wird eine Strafgebühr fällig. Die Strafgebühren werden kumuliert pro
Leser erfasst (1:1-Beziehung). Bei Rückgabe eines Buches wird der Datensatz in der Verleihtabelle gelöscht. Bei Vormerkungen wird analog verfahren. Allerdings gilt hier: Ein Leser
kann mehrere Bücher vormerken und jedes Buch kann seinerseits gleichzeitig von mehreren
Lesern vorgemerkt werden (m:n-Beziehung).
Stand: 27.12.2013
12
SQL-Einführung mit MySQL
Abbildung 3: ER-Modell einer Leihbücherei
3.2 Tabellen erstellen
Wie wir anhand der mittlerweile bekannten Regeln zur Normalisierung feststellen können, befindet sich das Datenmodell der Bibliotheksverwaltung bereits in der 3. Normalform. Wir
wollen nun die erforderlichen Tabellen anlegen. Hierzu gehen wir schrittweise vor. D.h., wir
legen die Tabellen an und geben ein paar Daten ein. Die Daten werden so beschaffen sein,
Schwachstellen des Modells bzw. seiner Umsetzung schnell zu erkennen, um diese im jeweils
folgenden Schritt zu beseitigen. Das bedeutet gleichzeitig, dass wir nicht immer sofort alle
Möglichkeiten eines SQL-Befehls ausnutzen werden. Vieles lässt sich also eleganter und
schneller formulieren als es im Folgenden geschieht.
Tabellen werden generell mit dem folgende Kommando erzeugt:
CREATE TABLE <tabellenname> (
<spalte1>
<datentyp>
<spalte2>
<datentyp>
...
<spalteN>
<datentyp>
);
[NOT NULL],
[NOT NULL],
[NOT NULL]
Ist ein Feld als NOT NULL definiert, muss ein Wert in das Feld eingegeben werden, es darf
also nicht leer sein. Wir verwenden im Folgenden eine Besonderheit von MySQL, um Datumswerte in unseren Tabellen speichern zu können. Standardmäßig würde man den Datentyp
DATE verwenden, wo wir hier TIMESTAMP schreiben. Wir tun dies, obgleich wir die Uhrzeit
nicht benötigen, um später das aktuelle Datum datenbankseitig automatisiert eintragen lassen
zu können und dennoch die Möglichkeit zu haben, einen expliziten Wert selbst setzen zu könStand: 27.12.2013
13
SQL-Einführung mit MySQL
nen. Das ist in MySQL etwas speziell geregelt. Wir müssen daher bereits jetzt DEFAULT
CURRENT_TIMESTAMP als Regel ergänzen. Täten wir dies nicht, würde MySQL immer, egal
was wir angeben, das aktuelle Datum mit der aktuellen Uhrzeit verwenden. Das im Detail zu
erläutern, wäre jetzt etwas langatmig. Wen aber die Details interessieren, der mag dies in der
MySQL-Dokumentation nachlesen. Diejenigen Leser, die es nicht interessiert, nehmen den
Sachverhalt einfach so hin.
Gemäß dem obigen ER-Modell unserer Bibliothek ergeben sich dann folgende Kommandos
zur Erzeugung der benötigten Tabellen:
CREATE TABLE buecher (
buchnr
autor
titel
gruppe
leihfrist
ausleihzahl
);
CREATE TABLE leser (
lesernr
nachname
vorname
wohnort
ausleihzahl
eintrittsdatum
INT
VARCHAR(40),
VARCHAR(80)
CHAR(1)
INT
INT
1
NOT NULL,
NOT
NOT
NOT
NOT
NULL,
NULL,
NULL,
NULL
INT
NOT NULL,
VARCHAR(30)
NOT NULL,
VARCHAR(30)
NOT NULL,
VARCHAR(100)
NOT NULL,
INT
NOT NULL,
TIMESTAMP
NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE strafen (
lesernr
gebuehr
sperre
);
CREATE TABLE verleih (
lesernr
buchnr
ausleihdatum
leihdauer
INT
DECIMAL(5,2)
INT
NOT NULL,
NOT NULL,
NOT NULL
INT
NOT NULL,
INT
NOT NULL,
TIMESTAMP
NOT NULL
DEFAULT CURRENT_TIMESTAMP,
INT
NOT NULL
);
CREATE TABLE vormerkungen (
lesernr
INT
NOT NULL,
buchnr
INT
NOT NULL,
datum
TIMESTAMP
NOT NULL
DEFAULT CURRENT_TIMESTAMP
);
Wenn wir uns anschauen wollen, was wir bis hierhin geleistet haben, so können wir uns in
MySQL die Definition vorhandener Objekte anzeigen lassen. Mit
SHOW <objekttyp>;
Stand: 27.12.2013
14
SQL-Einführung mit MySQL
also beispielsweise
SHOW tables;
können wir uns die definierten Tabellen anzeigen lassen. Zur Anzeige der Definition eines
Objekts selbst, steht der Befehl DESC (wie describe) zur Verfügung:
DESC <tabellenname>;
Wenn wir im Kommandozeilenwerkzeug von MySQL arbeiten, würde die Beschreibung der
Tabelle buecher beispielsweise wie folgt aussehen:
mysql> desc buecher;
+-------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| buchnr
| int(11)
| NO
|
| NULL
|
|
| autor
| varchar(40) | YES |
| NULL
|
|
| titel
| varchar(80) | NO
|
| NULL
|
|
| gruppe
| char(1)
| NO
|
| NULL
|
|
| leihfrist
| int(11)
| NO
|
| NULL
|
|
| ausleihzahl | int(11)
| NO
|
| NULL
|
|
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> _
Sind alle Tabellen wunschgemäß angelegt, wollen wir ein paar Datensätze eingeben. Hierzu
gibt es das INSERT-Kommando. Dessen Syntax lautet:
INSERT INTO <tabellenname> (<spalte1>, ..., <spalteN>)
VALUES (<wert1>, ..., <wertN>);
In unserem Fall sollen folgende Dateneingaben getätigt werden:
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(1,'Goethe','Wilhelm Meister','K',30,0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(2,'Goethe','Faust','K',30,0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(3,'Goethe','Wahlverwandtschaften','K',30,0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(4,'Schiller','Die Räuber','K',30,0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(5,'Lessing','Nathan der Weise','K',30,0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(6,'Lessing','Nathan der Weise','K',30,0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(7,'Heitsiek','Personal Oracle','W',30, 0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
Stand: 27.12.2013
2
15
SQL-Einführung mit MySQL
ausleihzahl)
VALUES(8,'Böll','Frauen vor Flußlandschaft','U',30,0);
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(9,'Haage','NetObjects Fusion 5/MX','W',0,0);
Fälschlicherweise ist auch die nochmalige Eingabe eine bereits erfassten Buches möglich:
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(7,'Heitsiek','Personal Oracle','W', 30,0);
3
Ein Buch mit der Nummer 7 ist bereits in der Tabelle enthalten. Die Prämisse, jeden Datensatz eindeutig identifizieren zu können, ist augenscheinlich formal (noch) nicht im Datenmodell manifestiert. Wir wollen diesen Fehler korrigieren. Hierzu löschen wir erst den fehlerhaften Datensatz mit dem DELETE-Kommando. Aufgrund dessen der Datensatz nicht eindeutig
identifizierbar ist, gehen uns natürlich gleich zwei Datensätze flöten, so dass wir nach dem
Löschen den verlorenen Datensatz wieder einfügen müssen. Syntax von DELETE:
DELETE FROM <tabellenname>
[WHERE <spaltenname> = <wert> [AND|OR <spaltenname> ...]];
Ohne einschränkende WHERE-Bedingung würden alle Datensätze der betreffenden Tabelle gelöscht.
DELETE FROM buecher WHERE buchnr = 7;
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(7,'Heitsiek','Personal Oracle','W',30,0);
4
Zur Durchsetzung der eindeutigen Identifizierbarkeit eines Datensatzes gibt es, wie wir bereits gehört haben, den so genannten Primärschlüssel. Folgende Primärschlüssel sollen definiert werden:
1. buecher
→ (buchnr)
2. leser
→ (lesernr)
3. verleih
→ (lesernr, buchnr)
4. vormerkungen → (lesernr, buchnr)
5. strafen
→ (lesernr)
Glücklicherweise brauchen wir die schon erzeugten Tabellen nicht zu löschen und neu anlegen. Mittels des ALTER-Kommandos können wir unsere Tabellen so ändern, dass wir Regeln
definieren können (so genannte CONSTRAINTs), die unserem Wunsch nach Eindeutigkeit der
Datensätze Rechnung tragen. Das ALTER-Kommando ist sehr mächtig. Für die Definition eines Primärschlüssels besitzt es folgende Syntax:
ALTER TABLE <tabellenname> ADD CONSTRAINT <regelname>
PRIMARY KEY (<spaltenname>);
Folgende Kommandos optimieren unser Datenmodell:
ALTER TABLE
PRIMARY
ALTER TABLE
PRIMARY
buecher ADD CONSTRAINT idx_buecher_pk
KEY(buchnr);
leser ADD CONSTRAINT idx_leser_pk
KEY(lesernr);
Stand: 27.12.2013
5
16
SQL-Einführung mit MySQL
ALTER TABLE
PRIMARY
ALTER TABLE
PRIMARY
ALTER TABLE
PRIMARY
strafen ADD CONSTRAINT idx_strafen_pk
KEY(lesernr);
verleih ADD CONSTRAINT idx_verleih_pk
KEY(lesernr, buchnr);
vormerkungen ADD CONSTRAINT idx_vormerk_pk
KEY(lesernr, buchnr);
Versuchen wir jetzt nochmals den Datensatz mit der Buchnummer 7 zu erfassen, wird uns die
Datenbank einen Fehler melden.
INSERT INTO buecher(buchnr,autor,titel,gruppe,leihfrist,
ausleihzahl)
VALUES(7,'Heitsiek','Personal Oracle','W', 30,0);
6
MySQL meldet jetzt:
ERROR 1062 (23000) at line 1: Duplicate entry '7' for key 'PRIMARY'
Sie lehnt es ab, den bereits vorhandenen Datensatz nochmals in der Büchertabelle einzufügen.
Fügen wir nun auch in die Lesertabelle einige Datensätze in der bereits bekannten Syntax
ein6:
INSERT INTO leser(lesernr, nachname, vorname, wohnort,
ausleihzahl, eintrittsdatum)
VALUES(1, 'Brodmann', 'Karsten',
'Zum Stutenkerl 14, 49090 Osnabrück',
0, '1999-07-01');
INSERT INTO leser(lesernr, nachname, vorname, wohnort,
ausleihzahl, eintrittsdatum)
VALUES(2, 'Stuckenberg', 'Wolfgang',
'Im Fange 61, 49084 Osnabrück',
0, '2003-01-01');
INSERT INTO leser(lesernr, nachname, vorname, wohnort,
ausleihzahl, eintrittsdatum)
VALUES(3, 'Ellinghaus', 'Heidelore',
'Danziger Weg 2, 49090 Osnabrück',
0, '2001-11-01');
INSERT INTO leser(lesernr, nachname, vorname, wohnort,
ausleihzahl, eintrittsdatum)
VALUES(4, 'Hartmann', 'Jan',
'Moorstraße 2, 49545 Tecklenburg',
0, '2000-08-01');
INSERT INTO leser(lesernr, nachname, vorname, wohnort,
ausleihzahl, eintrittsdatum)
VALUES(5, 'Temmen', 'Andreas',
'Sommerbrockstraße 5, 48432 Rheine',
0, '2003-01-01');
7
Wir stellen fest, uns bei der Adresse des Lesers Andreas Temmen vertan zu haben. Dies kann
mittels des UPDATE-Kommandos korrigiert werden.
Dessen generelle Syntax lautet:
UPDATE <tabellenname> SET <spaltenname> = <wert>
WHERE <spaltenname> = <wert>;
6
Die Uhrzeit darf bei TIMESTAMP unterschlagen werden.
Stand: 27.12.2013
17
SQL-Einführung mit MySQL
Im konkreten Fall geben wir folgende Änderung ein:
UPDATE leser SET wohnort = 'Winterbrockstraße 5, 48432 Rheine'
WHERE lesernr = 5;
8
Wir machen uns hier die Eigenschaft der eindeutigen Identifizierbarkeit eines Datensatzes zu
Nutze, indem wir den zu ändernden Datensatz über seinen Identifikationsschlüssel referenzieren. Natürlich kann man auch mehrere Datensätze in einem Schritt modifizieren. Dies ist lediglich eine Frage des Einschränkungskriteriums, welches über die WHERE-Bedingung definiert wird. Gegebenenfalls wird dann die Änderung für alle Datensätze durchgeführt, für welche die Bedingung zutrifft. Es sei daher eine kleine Mahnung ausgesprochen, mit dem UPDATE-Befehl sorgsam umzugehen. Möglicherweise verändern wir unseren Datenbestand in einem nicht gewünschten Umfang. In unserer Spieldatenbank ist das maximal ärgerlich, im realen Leben ernten wir damit Staunen, Entsetzen und Hohn anderer Anwender. Nicht selten handeln wir uns auch eine Menge Ärger mit unserem Chef ein.
Fügen wir jetzt einen Verleihvorgang in die Verleihtabelle ein:
INSERT INTO verleih(lesernr, buchnr, ausleihdatum, leihdauer)
VALUES(1, 1, NOW(), 30);
UPDATE buecher SET ausleihzahl = ausleihzahl+1
WHERE buchnr = 1;
UPDATE leser SET ausleihzahl = ausleihzahl+1
WHERE lesernr = 1;
9
Neben dem Eintrag in der Verleihtabelle ist jeweils die ausleihzahl in der Tabelle buecher bzw. leser zu erhöhen. Dies ist erforderlich, um die Ausleihstatistik korrekt zu führen.
Soweit so gut – was ist jedoch mit dem nächsten Verleihvorgang?
INSERT INTO verleih(lesernr, buchnr, ausleihdatum, leihdauer)
VALUES(9999, 1, NOW(), 30);
UPDATE buecher SET ausleihzahl = ausleihzahl+1
WHERE buchnr = 1;
UPDATE leser SET ausleihzahl = ausleihzahl+1
WHERE lesernr = 9999;
10
Der Eintrag in die Verleihtabelle ist erfolgreich. Auch der Zähler in der Büchertabelle wird erfolgreich inkrementiert. Beim Leser scheitert die gewünschte Operation, weil der Leser nicht
vorhanden ist.
ACHTUNG: Es erfolgt lediglich der lapidare Hinweis, dass kein Datensatz aktualisiert wurde! Dies aber auch nur dann, wenn man interaktiv mit MySQL arbeitet. Werden die Kommandos im Batchmodus abgesetzt, erhalten wir keinerlei Rückmeldung. Ein eigentlicher Fehler im
Sinne des Datenbanksystems ist hierbei nicht aufgetreten.
Das bedeutet letztlich, dass Einträge in der Verleihtabelle möglich sind, die im Sinne der gedachten Geschäftslogik keinen Sinn machen und insoweit die Konsistenz der Datenbank gefährden. Dies ist ein typisches Beispiel für die fehlende Durchsetzung referentieller Integrität. Die Verleihtabelle referenziert mittels der Attribute buchnr und lesernr jeweils einen
Datensatz in den Tabellen buecher und leser. buchnr und lesernr in der Tabelle verleih sind daher klassische Fremdschlüsselattribute.
Wir bringen unsere Datenbank geschwind wieder in eine konsistente Form:
DELETE FROM verleih WHERE lesernr = 9999;
Stand: 27.12.2013
11
18
SQL-Einführung mit MySQL
UPDATE buecher SET ausleihzahl = ausleihzahl-1
WHERE buchnr = 1;
Zur Wahrung der referentiellen Integrität lassen sich Regeln definieren – wer hätte es nicht
geahnt?
ALTER TABLE <tabellenname> ADD CONSTRAINT <regelname>
FOREIGN KEY(fremdschlüsselspalte)
REFERENCES <referenztabelle>(eindeutige referenzspalte[n])
[ON UPDATE|DELETE RESTRICT|CASCADE];
Die Ergänzung ON DELETE RESTRICT verhindert das Löschen des primären Datensatzes,
wenn er noch referenziert wird. Die Klausel ON UPDATE CASCADE sorgt dafür, den Fremschlüssel passend zu ändern, wenn sich der referenzierte Primärschlüssel ändert. Es gibt weitere Optionen. Hierzu sei wiederum auf die Dokumentation verwiesen.
ALTER TABLE verleih ADD CONSTRAINT con_verleih_leser
FOREIGN KEY(lesernr) REFERENCES leser(lesernr)
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE verleih ADD CONSTRAINT con_verleih_buecher
FOREIGN KEY(buchnr) REFERENCES buecher(buchnr)
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE vormerkungen ADD CONSTRAINT con_vormerkungen_leser
FOREIGN KEY(lesernr) REFERENCES leser(lesernr)
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE vormerkungen ADD CONSTRAINT con_vormerkungen_buecher
FOREIGN KEY(buchnr) REFERENCES buecher(buchnr)
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE strafen ADD CONSTRAINT con_strafen_leser
FOREIGN KEY(lesernr) REFERENCES leser(lesernr)
ON DELETE RESTRICT
ON UPDATE CASCADE;
12
Prüfen wir exemplarisch nach, ob die Regeln auch wirken:
INSERT INTO verleih(lesernr, buchnr, ausleihdatum, leihdauer)
VALUES(9999, 1, NOW(), 30);
13
Jawohl – das Einfügen dieses Datensatzes schlägt jetzt erwartungsgemäß fehl, so wie es beabsichtigt ist.
ERROR 1452 (23000) at line 1: Cannot add or update a child row: a
foreign key constraint fails (`karsten`.`verleih`, CONSTRAINT
`con_verleih_leser` FOREIGN KEY (`lesernr`) REFERENCES `leser`
(`lesernr`) ON UPDATE CASCADE)
Die für unser Beispiel erforderlichen Konsistenzbedingungen werden jetzt schon halbwegs
eingehalten. Dennoch ist es noch möglich, ein Buch an mehrere Leser gleichzeitig zu verleihen. So kann das Buch Nummer 1 parallel zu Leser 1 auch an Leser 2 verliehen werden:
INSERT INTO verleih(lesernr, buchnr, ausleihdatum, leihdauer)
VALUES(2, 1, NOW(), 30);
UPDATE leser SET ausleihzahl = ausleihzahl+1
Stand: 27.12.2013
14
19
SQL-Einführung mit MySQL
WHERE lesernr = 2;
UPDATE buecher SET ausleihzahl = ausleihzahl+1
WHERE buchnr = 1;
Dies ist eindeutig nicht im Sinne des Erfinders. Wir löschen deshalb die Fehleingabe und
überlegen uns, wie wir die Situation zukünftig korrekt meistern können.
DELETE FROM verleih WHERE lesernr = 2;
UPDATE leser SET ausleihzahl = ausleihzahl-1
WHERE lesernr = 2;
UPDATE buecher SET ausleihzahl = ausleihzahl-1
WHERE buchnr = 1;
15
Wir brauchen eine Regel, die gewährleistet, dass ein Buch nur einmal zur gleichen Zeit in der
Verleihtabelle auftauchen kann. Hierzu können wir einen eindeutigen Index definieren. Die
Syntax zur Definition eines Indizes lautet:
CREATE [UNIQUE] INDEX <indexname>
ON <tabellenname>(<spalte1>[, ... <spalteN>]);
Das Schlüsselwort UNIQUE gibt an, dass der Dateninhalt des indizierten Attributs eindeutig
sein soll. Bei nicht-eindeutigen Attributwerten entfällt es logischerweise. Man kann also auch
nicht-eindeutige Indizes definieren. Dies ist vor allem dazu gedacht, das Suchen eines oder
mehrerer Datensätze mit bestimmten Eigenschaften in einer Tabelle zu beschleunigen. Hierbei
sollte man dann bedenken, dass jeder zusätzlich definierte Index Eingabeoperationen verlangsamt. Der Index muss von der Datenbank immer mitgeführt werden, was natürlich Leistung
kostet. Es gilt also immer abzuwägen, was wichtiger ist: Eingabe- oder Abfragegeschwindigkeit.
Wir definieren folgenden Schlüssel:
CREATE UNIQUE INDEX idx_verleih_buchnr ON verleih(buchnr);
16
Ein neuer Versuch, das fragliche Buch doppelt zu erfassen, scheitert nun kläglich:
INSERT INTO verleih(lesernr, buchnr, ausleihdatum, leihdauer)
VALUES(2, 1, NOW(), 30);
17
MySQL gibt folgende Protestmeldung aus:
ERROR 1062 (23000) at line 1: Duplicate entry '1' for key
'idx_verleih_buchnr'
So soll es sein!
Wir sind mit den möglichen Verbesserungen an unserer Datenbank jedoch noch nicht am
Ende. Es ist beispielsweise wünschenswert, für verschiedene Felder sinnvolle Vorgabewerte
zu definieren, um diese nicht bei jeder Einfügeoperation manuell und explizit eingeben zu
müssen.
1. Jedes neu anzulegende Buch soll eine Ausleihzahl von 0 besitzen, da es natürlich (weil
es neu ist) noch nie ausgeliehen wurde. Per Voreinstellung soll es eine Leihfrist von 30
Tagen besitzen.
2. Ein neuer Leser soll als Eintrittsdatum das aktuelle Datum eingetragen bekommen 7,
wenn nichts anderes definiert ist. Die Anzahl der von ihm ausgeliehenen Bücher ist 0.
7
Dies ist bereits bei der Tabellendefinition geschehen.
Stand: 27.12.2013
20
SQL-Einführung mit MySQL
3. Bei einer Ausleihe soll das Ausleihdatum dem aktuellen Datum entsprechen8. Die
Leihdauer soll mit 30 Tagen vorgegeben werden.
4. Eine Vormerkung soll immer zum aktuellen Datum erfasst werden9.
5. Eine Strafgebühr soll nicht sofort mit einer Sperre (1) belegt werden. Der Vorgabewert
soll daher 0 sein.
Hierfür kann man Vorgabewerte je Spalte einer Tabelle definieren. Die allgemeine Syntax lautet:
ALTER TABLE <tabellenname> ALTER <spaltenname> SET DEFAULT <wert>;
Für Zeitstempel gelten in MySQL besondere Regeln, die wir bereits bei der Anlage unserer
Tabellen erläutert und berücksichtigt haben, so dass wir diesbezüglich nichts mehr unternehmen müssen.
Wir definieren also:
ALTER TABLE buecher
ALTER leihfrist SET DEFAULT 30;
ALTER TABLE buecher
ALTER ausleihzahl SET DEFAULT 0;
ALTER TABLE leser
ALTER ausleihzahl SET DEFAULT 0;
ALTER TABLE verleih
ALTER leihdauer SET DEFAULT 30;
ALTER TABLE strafen
ALTER sperre SET DEFAULT 0;
18
Durch die Definition der Default-Werte lassen sich Daten nun leichter und bequemer erfassen.
Default-Werte brauchen beim INSERT-Statement nicht mehr angegeben werden. Sie werden
automatisch gesetzt:
INSERT INTO buecher(buchnr, autor, titel, gruppe)
VALUES(10, 'Haage', 'NetObjects Fusion 5/MX', 'W');
INSERT INTO buecher(buchnr, autor, titel, gruppe, leihfrist)
VALUES(11, 'Goethe', 'Wahlverwandtschaften', 'K', 0);
19
Ohne Absicherung ist noch das Feld gruppe der Tabelle buecher. Wenn wir sicherstellen
wollen, dass nur bestimmte Kategorien erfasst werden können (U=Unterhaltung, K=Klassik,
W=Wissen), sollte der Wertebereich eingeschränkt werden. Der in der Tabellendefinition vorgegebene Datentyp CHAR(1) erlaubt prinzipiell jedes Zeichen, was nicht in unserem Sinne
ist. Es kann daher eine Einschränkung formuliert werden, deren generelle Syntax wie folgt
aussieht:
ALTER TABLE <tabellenname>
ADD CONSTRAINT <regelname> CHECK(<bedingung>);
Neben der Einschränkung für die Buchkategorie wollen wir dafür sorgen, die Ausleihfrist auf
Werte zwischen 0 und 30 Tage zu begrenzen. Wir formulieren deshalb folgende Regeln:
ALTER TABLE buecher
ADD CONSTRAINT chk_buecher_gruppe
CHECK(gruppe IN ('U','K','W'));
8
9
20
dto.
dto.
Stand: 27.12.2013
21
SQL-Einführung mit MySQL
ALTER TABLE buecher
ADD CONSTRAINT chk_buecher_verleihfrist
CHECK(leihfrist BETWEEN 0 AND 30);
Obgleich diese Regeln SQL-konform sind und MySQL sie akzeptiert, entfalten sie keine Wirkung. In Oracle würde es beispielsweise funktionieren. In MySQL müssen wir einen anderen
Weg beschreiten, die Datenkonsistenz durchzusetzen.
DELIMITER $$
CREATE TRIGGER buecher_before_insert BEFORE INSERT ON buecher
FOR EACH ROW
BEGIN
IF NEW.gruppe NOT IN ('U', 'K', 'W') OR
NEW.leihfrist NOT BETWEEN 0 AND 30 THEN
SIGNAL SQLSTATE '12345'
SET MESSAGE_TEXT =
'constraint on buecher(gruppe/leihfrist) failed';
END IF;
END$$
DELIMITER ;
21
Wir bedienen uns hier eines so genannten Triggers. Trigger sind Aktionen, die bei Eintreten
eines Ereignisses, wie dem Einfügen, Ändern oder Löschen eines Datensatzes, automatisch
ausgeführt werden. Die Folgeaktion kann vor oder nach dem auslösenden Ereignis ausgeführt
werden, weshalb wir zwischen BEFORE- und AFTER-Triggern unterscheiden. Das Thema
Trigger ist recht komplex und eigentlich den bereits weiterführenden Themen zuzurechnen,
weshalb wir es hier nicht weiter vertiefen werden. Sie sollen hier aber kurz Erwähnung finden, da sie in der Datenbankimplementierung eine wichtige Rolle spielen, wie unser eigenes
Beispiel bereits eindrucksvoll zeigt.
Wer jetzt denkt, wir hätten alles Erdenkliche getan, um die Konsistenz unserer Datenbank zu
gewährleisten, der irrt leider. Wie wir oben gesehen haben, ist es bei einem Verleihvorgang im
Sinne der Vollständigkeit unserer Daten erforderlich, sowohl in der Buch- wie auch der Lesertabelle einen Zähler zu inkrementieren. Werden die beiden hierzu erforderlichen UPDATE-Kommandos vergessen, so gilt ein Buch zwar erfolgreich als verliehen, die Nutzungsstatistik unserer Bibliothek würde jedoch verfälscht. Aber auch dies lässt sich mit einfachen
Mitteln absichern. – Auch hierzu werden Trigger verwendet. Dies ist auch der klassische Anwendungsfall dafür.
Für unseren Fall der Bibliothek wollen wir den Buchverleih wie folgt vereinfachen bzw. hinsichtlich der Datenkonsistenz absichern:
DELIMITER $$
CREATE TRIGGER verleih_before_insert BEFORE INSERT ON verleih
FOR EACH ROW
BEGIN
UPDATE buecher SET ausleihzahl = ausleihzahl+1
WHERE buchnr = NEW.buchnr;
UPDATE leser SET ausleihzahl = ausleihzahl+1
WHERE lesernr = NEW.lesernr;
END$$
DELIMITER ;
Stand: 27.12.2013
22
22
SQL-Einführung mit MySQL
Der Verleihvorgang kann nunmehr mit nur einem einzigen Kommando korrekt ausgeführt
werden:
INSERT INTO verleih(lesernr, buchnr) VALUES(3, 2);
Auf die Rückgabe eines Buches hat dies selbstredend keinen Einfluss. Die Rückgabe eines
Buches erfolgt einfach mit der Löschung des betreffenden Datensatzes aus der Verleihtabelle.
DELETE FROM verleih WHERE lesernr = 3 AND buchnr = 2;
Unser Datenmodell ist nun soweit perfektioniert, dass wir uns im Weiteren mit Auswertungen,
sprich Abfragen, beschäftigen wollen. Sicherlich lassen sich weitere Verbesserungen implementieren. Im Wesentlichen richten sich diese nach potentiellen Ausgestaltungen des gewünschten Geschäftsmodells bzw. der Prozesse, die wir in unserer Bibliothek abbilden wollen. Der Phantasie sind da kaum Grenzen gesetzt. Dies sei jedoch dem ambitionierten Leser
überlassen. Für alles Folgende gelten die bislang implementierten Tabellen und Regeln in unserem einfachen und sicherlich auch nicht perfekten Modell.
Sollte jemand im Rahmen seiner privaten Forschungen über das Ziel hinausgeschossen sein,
kann er Objekte mit „DROP <datenbakobjekt>;“ wieder löschen. Gelegentlich weigert
sich die Datenbank aber auch, eine geforderte Löschung vorzunehmen. Das geschieht immer
dann, wenn die Löschung gegen eine Konsistenzregel verstößt. Dann müssen Vorarbeiten geleistet werden, wie z.B. das Löschen von referenzierender Datensätzen, um ohne Regelverstöße Änderungen vornehmen zu können. Wer das mal an einem größeren Datenbankmodell mit
Echtdaten im Produktivbetrieb versucht hat, weiß um Angst und Schweiß, die solche Arbeiten
den Entwicklern und Administratoren auf die Stirn treiben können.
4 Abfragen
Zur Abfrage von Daten wird der SELECT-Befehl verwendet. Er ist der wohl am häufigsten
verwendet SQL-Befehl. Der Name verrät es bereits: SELECT selektiert Daten.
SELECT kann auf eine oder mehrere Tabellen einer Datenbank gleichzeitig zugreifen, um die
gewünschten Informationen zu suchen und zu filtern. Der Befehl fungiert also wie ein Trichter oder Filter, in den man eine Menge an Daten hinein wirft und die dann unten in der (hoffentlich) gewünschten Form herauskommen. Das Ergebnis ist in jedem Fall wieder eine Tabelle. Diese wird zwar nicht explizit in der Datenbank abgelegt, sondern lediglich auf dem
Bildschirm angezeigt, besteht aber nichtsdestotrotz aus Zeilen und Spalten. D.h., dass das Ergebnis einer Abfrage (fast) immer auch dort eingesetzt werden kann, wo eine Tabelle gefordert ist. Die Auswahl der Spalten für die Ergebnistabelle heißt Projektion, die der Zeilen Selektion. Werden Daten aus mehreren Tabellen in einer Abfrage zusammengeführt, so sprechen
wir von einem Join.
Statt sofort die komplette Syntax des SELECT-Statements einzuführen, werden wir uns langsam vorarbeiten und unsere Kenntnisse sukzessive erweitern.
4.1 Einfache Abfragen
Die einfachste aller Abfragen hat folgende Form:
SELECT *
FROM <tabellenname>;
Stand: 27.12.2013
23
23
24
SQL-Einführung mit MySQL
Eine derartige Abfrage fragt aus der mit <tabellenname> bezeichneten Tabelle alle Spalten
„*“ und alle Zeilen ab, gibt also den kompletten Inhalt der angegebenen Tabelle aus.
Wenn wir alle Bücher aus der Tabelle buecher mit all ihren Informationen ausgeben wollen,
so können wir folglich formulieren10:
mysql> SELECT * FROM buecher;
+--------+----------+--------------------------+-...-+-------------+
| buchnr | autor
| titel
| ... | ausleihzahl |
+--------+----------+--------------------------+-...-+-------------+
|
8 | Böll
| Frauen vor Flußlands... |
|
0 |
|
1 | Goethe
| Wilhelm Meister
|
|
1 |
|
11 | Goethe
| Wahlverwandtschaften
|
|
0 |
|
3 | Goethe
| Wahlverwandtschaften
|
|
0 |
|
2 | Goethe
| Faust
|
|
1 |
|
9 | Haage
| NetObjects Fusion 5/MX
|
|
0 |
|
10 | Haage
| NetObjects Fusion 5/MX
|
|
0 |
|
7 | Heitsiek | Personal Oracle
|
|
0 |
|
5 | Lessing | Nathan der Weise
|
|
0 |
|
6 | Lessing | Nathan der Weise
|
|
0 |
|
4 | Schiller | Die Räuber
|
|
0 |
+--------+----------+--------------------------+-...-+-------------+
11 rows in set (0.00 sec)
25
mysql> _
Ohne speziell angegebene Sortierung, kann die Reihenfolge der Datensatzausgabe durchaus
überraschen. – Das werden wir in Kürze kontrollieren.
SQL ist übrigens in soweit formatfrei, als wir das Statement so schreiben können (mit/ohne
Zeilenumbruch oder Einrückung), wie es uns beliebt bzw. wir es für uns als gut leserlich erachten.
Wenn wir nicht an allen vorhandenen Informationen interessiert sind, so können wir die anzuzeigenden Spalten dadurch eingrenzen, dass wir die uns interessierenden Spalten explizit angeben. Wir können die Syntax des SELECT-Statements daher wie folgt ergänzen:
SELECT <*|spalte[, ..., spalteN]>
FROM <tabellenname>;
Mehrere Spalten können durch Kommata separiert angegeben werden. Für das obige Beispiel
soll die angezeigte Information auf die Spalten autor und titel eingegrenzt werden.
SELECT autor, titel FROM buecher;
Das Ergebnis dieser Bemühung besteht erwartungsgemäß aus den beiden Spalten autor und
titel. Die Reihenfolge der Zeilen ist immer noch „ungewiss“.
Im Kommandozeilenwerkzeug von MySQL sieht das dann so aus:
mysql> SELECT autor, titel FROM buecher;
+----------+----------------------------+
| autor
| titel
|
+----------+----------------------------+
| Goethe
| Wilhelm Meister
|
10 Die Darstellung des Ergebnisses ist hier aus Platzgründen gekürzt dargestellt.
Stand: 27.12.2013
24
26
SQL-Einführung mit MySQL
| Goethe
| Faust
|
| Goethe
| Wahlverwandtschaften
|
| Schiller | Die Räuber
|
| Lessing | Nathan der Weise
|
| Lessing | Nathan der Weise
|
| Heitsiek | Personal Oracle
|
| Böll
| Frauen vor Flußlandschaft |
| Haage
| NetObjects Fusion 5/MX
|
| Haage
| NetObjects Fusion 5/MX
|
| Goethe
| Wahlverwandtschaften
|
+----------+----------------------------+
11 rows in set (0.00 sec)
mysql> _
Die Reihenfolge der Datensätze entspricht nicht unseren Erwartungen an ein wohl sortiertes,
übersichtliches Ergebnis. Aber auch die Sortierung ist mittels SQL kein Problem:
SELECT <*|spalte[, ..., spalteN]>
FROM <tabellenname>
ORDER BY <spalte|nummer [, ... spalte|nummer> [ASC|DESC];
Eine Sortierung wird über die ORDER BY-Klausel initiiert. Man kann hierbei entweder den
Namen der Spalte(n) angeben, wonach sortiert werden soll oder deren Nummer(n). Die Sortierreihenfolge wird über die Optionen ASC (aufsteigend), welches die Vorgabe ist, und DESC
(absteigend) gesteuert. Beide Optionen lassen sich je Sortierspalte individuell setzen. Bei der
numerischen Angabe, werden die Ausgabespalten von links nach rechts mit 1 beginnend
durchnummeriert.
Wenn wir also nach Autoren aufsteigend und nach Titeln absteigend sortieren wollen, können
wir dies wie folgt bewerkstelligen:
SELECT autor, titel FROM buecher ORDER BY autor, titel DESC;
27
oder aber auch:
SELECT autor, titel FROM buecher ORDER BY 1, 2 DESC;
Nach der Spalte autor bzw. 1 wird implizit aufsteigend sortiert. Die beiden Kommandos sind
äquivalent und produzieren identische Ergebnisse:
+----------+----------------------------+
| autor
| titel
|
+----------+----------------------------+
| Böll
| Frauen vor Flußlandschaft |
| Goethe
| Wilhelm Meister
|
| Goethe
| Wahlverwandtschaften
|
| Goethe
| Wahlverwandtschaften
|
| Goethe
| Faust
|
| Haage
| NetObjects Fusion 5/MX
|
| Haage
| NetObjects Fusion 5/MX
|
| Heitsiek | Personal Oracle
|
| Lessing | Nathan der Weise
|
| Lessing | Nathan der Weise
|
| Schiller | Die Räuber
|
+----------+----------------------------+
Stand: 27.12.2013
25
28
SQL-Einführung mit MySQL
11 rows in set (0.00 sec)
Eigentlich kommt es eher selten vor, alle Zeilen einer Tabelle wissen zu wollen. Meistens
wird nach Datensätzen gesucht, die bestimmte Selektionskriterien erfüllen. Hierzu gibt es die
WHERE-Klausel.
Mit WHERE können eine oder mehrere Bedingungen auf Datensatzebene definiert werden, bei
deren Zutreffen die jeweiligen Datensätze Bestandteil der Ergebnismenge werden. Mehrere
Bedingungen können mit den logischen Operatoren AND, OR und NOT miteinander verkettet
werden.
AND hat Vorrang vor OR, NOT bedeutet lediglich die Negation des Bedingungsausdrucks. Mit
Klammern können die Vorrangregeln übersteuert werden, da Klammern vorrangig vor den logischen Operatoren ausgewertet werden. Letztlich entsprechen die Regeln denen, die wir aus
der Mathematik kennen (sollten).
SELECT <*|spalte[, ..., spalteN]>
FROM <tabellenname>
WHERE <bedingung(en)>
ORDER BY <spalte|nummer [, ... spalte|nummer> [ASC|DESC];
Wollen wir alle ausleihbaren Bücher ermitteln, die noch nie ausgeliehen wurden, so können
wir dies tun, indem wir uns alle Bücher anzeigen lassen, die eine leihfrist > 0 besitzen
und deren ausleihzahl den Wert 0 hat.
SELECT * FROM buecher
WHERE leihfrist > 0
AND ausleihzahl = 0;
29
Bislang wurden nur Buch 1 und 2 ausgeliehen, Buch 9 und 11 sind nicht ausleihbar. Wir erhalten daher folgende Bücher als Ergebnis: 3, 4, 5, 6, 7, 8 und 10.
Wie wichtig es ist, auf die korrekte Klammerung zu achten, soll das folgende Beispiel verdeutlichen. Hierzu nehmen wir jedoch erst eine kleine Änderung an unserem Bibliotheksbestand vor.
Das zweite Exemplar von „Nathan der Weise“, Buch 6, soll zum Präsenzexemplar erklärt
werden. D.h., die Leihfrist wird hierfür auf 0 gesetzt.
UPDATE buecher SET leihfrist = 0 WHERE buchnr = 6;
Wir wollen alle Bücher aus den Gruppen Klassik (K) und Wissen (W) herausfiltern, die ausleihbar sind:
SELECT * FROM buecher
WHERE (gruppe = 'K' OR gruppe ='W')
AND NOT leihfrist = 0;
31
Als Ergebnis erhalten wir:
buchnr
autor
titel
gruppe
leihfrist
ausleihzahl
1
Goethe
…
K
30
1
2
Goethe
…
K
30
1
3
Goethe
…
K
30
0
4
Schiller
…
K
30
0
Stand: 27.12.2013
30
26
SQL-Einführung mit MySQL
5
Lessing
…
K
30
0
7
Heitsiek
…
W
30
0
10
Haage
…
W
30
0
Wenn wir bei dieser Abfrage die Klammern vergessen, wird nur die Gruppe Wissen auf Ausleihbarkeit geprüft, aus der Rubrik Klassik werden alle Bücher angezeigt. Zur Erinnerung:
AND hat Vorrang vor OR.
SELECT * FROM buecher
WHERE gruppe = 'K' OR gruppe ='W' AND NOT leihfrist = 0;
buchnr
autor
titel
gruppe
leihfrist
32
ausleihzahl
1
Goethe
…
K
30
1
2
Goethe
…
K
30
1
3
Goethe
…
K
30
0
4
Schiller
…
K
30
0
5
Lessing
…
K
30
0
6
Lessing
…
K
0
0
7
Heitsiek
…
W
0
10
Haage
…
W
30
0
11
Goethe
…
K
0
0
Die Bücher 6 und 9 sind wegen fehlender Klammerung und dem Vorrang von AND gegenüber OR im Ergebnis enthalten. D.h., die eigentlich ausgewertete Bedingung – aufgrund der
Vorrangregeln – der letzten Abfrage lässt sich mittels Klammerung so verdeutlichen:
WHERE gruppe = 'K' OR (gruppe ='W' AND NOT leihfrist = 0);
Neben den bekannten Vergleichsoperatoren =, >, >=, < und <= kennt SQL noch IN, LIKE und
BETWEEN.
Mit dem Mengenoperator IN ließe sich das das gleiche Ergebnis auch so ermitteln:
SELECT * FROM buecher
WHERE gruppe IN ('K','W')
AND NOT leihfrist = 0;
33
Diese Formulierung mag manchem einleuchtender erscheinen, was aber letztlich eine Frage
des persönlichen Geschmacks ist. Auf alle Fälle vermeidet sie die Gefahr, Klammern eventuell falsch zu setzen.
LIKE dient der Mustersuche innerhalb von Zeichenketten. Hierbei steht „_“ für ein beliebiges
Zeichen, „%“ steht für eine beliebige Zeichenkette. Damit lassen sich z.B. sehr leicht alle Bü-
cher finden, deren Titel mit „W“ beginnen. Eine diesbezügliche Abfrage sieht dann so aus:
SELECT * FROM buecher WHERE titel LIKE 'W%';
Auf den Abdruck des Ergebnisses können wir wohl verzichten. Der Leser mag es selbst ausprobieren.
Stand: 27.12.2013
27
34
SQL-Einführung mit MySQL
Für BETWEEN lässt sich (zu diesem Zeitpunkt) nur sehr schwer ein Beispiel finden. Mit BETWEEN wird ein Intervall von Zeichen oder Zahlen angegeben, zwischen denen ein Wert liegen
soll. Die Intervallgrenzen werden dabei zur Ergebnismenge hinzu gezählt. Nehmen wir einfach mal an, wir wollten alle Bücher ausgeben, deren buchnr im Intervall [3,5] liegt:
SELECT * FROM buecher WHERE buchnr BETWEEN 3 AND 5;
35
Stattdessen hätte man genauso, aber umständlicher, wie folgt formulieren können:
SELECT * FROM buecher WHERE buchnr >= 3 AND buchnr <= 5;
36
Die Ergebnisse beider Abfragen sind identisch.
Ein besonderer Vergleichsoperator ist IS NULL. Er ist insoweit etwas Besonderes, als er Vergleiche mit Nichts ermöglicht. Das kann immer dann sinnvoll sein, wenn man feststellen
möchte, ob in einem bestimmten Datenfeld ein Wert eingetragen ist.
Wir erinnern uns, in der Tabelle buecher das Feld autor so definiert zu haben, dass dort
auch das Fehlen eines Eintrags akzeptiert wird. Fügen wir daher mal einen entsprechenden
Datensatz ein.
INSERT INTO buecher (buchnr, titel, gruppe, leihfrist,
ausleihzahl)
VALUES (12, 'Guinessbuch der Weltrekorde', 'W', 14, 0);
37
Jetzt können wir (sinnvoll) nach Büchern suchen, bei denen wir vergessen haben, den Autor
zu erfassen.
SELECT * FROM buecher WHERE autor IS NULL;
38
Als Ergebnis erhalten wir den soeben eingefügten Datensatz.
4.2 Operatoren(rangfolge)
Der Aspekt der Vorrangigkeit von Operatoren wurde bereits angesprochen. Einschließlich der
Operatoren für die vier Grundrechenarten und einiger hier nicht behandelter Operatoren, ergibt gemäß MySQL-Dokumentation sich folgendes Bild:
INTERVAL
BINARY, COLLATE
!
- (unäres Minus), ~ (unäre Bitinversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (Vergleich), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
= (Zuweisung), :=
Stand: 27.12.2013
28
SQL-Einführung mit MySQL
Der Vorrang gilt von oben nach unten. Die Operatoren in einer Zeile haben jeweils den gleichen Rang. Sie werden in Ausdrücken von links nach rechts ausgewertet.
4.3 Funktionen und Arithmetik
Im SQL-Standard sind neben den vier Grundrechenarten +, -, * und /, genau fünf arithmetische Funktionen definiert: MIN, MAX, SUM, AVG und COUNT. Die Namen sind halbwegs sprechend und stehen für die Berechnung des Minimums, des Maximums, der Summe, des Durchschnitts und der Anzahl.
Fast jeder Datenbankhersteller hat den Kanon der arithmetischen Funktionen für sich erweitert, um beispielsweise Sinus-Werte, Standardabweichungen u.ä. mehr, berechnen zu können.
MySQL macht da keine Ausnahme. Allerdings sollen uns diese Besonderheiten an dieser Stelle nicht weiter belasten und dem interessierten Leser sei die Datenbankdokumentation ans
Herz gelegt, so er solche Berechnungen durchführen möchte.
Da sich arithmetische Operationen nun wahrlich nicht mehr sinnbringend mit unserer Büchertabelle in Verbindung bringen lassen, bauen wir uns folgende Buchungstabelle (als gedachten
Ausschnitt eines Datenmodells einer Finanzbuchhaltung) und fügen auch gleich ein paar Datensätze ein:
CREATE TABLE buchungen(
lfdnr INTEGER,
einnahme DECIMAL(10,2),
ausgabe DECIMAL(10,2)
);
INSERT INTO buchungen VALUES(1, 25.30, 17.20);
INSERT INTO buchungen VALUES(2, 45.00, 38.90);
INSERT INTO buchungen VALUES(3, 33.50, 28.70);
39
Nun können wir ein paar Berechnungen anstellen. So könnten wir beispielsweise den Gewinn
je Buchungsposition/Geschäftsvorfall bestimmen:
SELECT einnahme, ausgabe, einnahme-ausgabe
FROM buchungen;
40
Wir erhalten das folgende Resultat:
Tabelle 11: Überschussermittlung je Geschäftsvorfall
einnahme
ausgabe
einnahme-ausgabe
25.30
17.20
8.10
45.00
38.90
6.10
33.50
28.70
4.80
Nun sieht insbesondere die dritte Spalte hinsichtlich ihrer Überschrift nicht besonders schön
aus.
SQL bietet daher die Möglichkeit eine Umbenennung der Ausgabespalten vorzunehmen.
Hierzu wird der gewünschte Bezeichner einfach an den Selektionausdruck der jeweiligen
Spalte, durch ein Leerzeichen getrennt, angehängt. Wollen wir die dritte Spalte in „überschuss“ umbenennen, sieht unser SQL-Statement so
Stand: 27.12.2013
29
SQL-Einführung mit MySQL
aus:
SELECT einnahme, ausgabe, einnahme-ausgabe AS überschuss
FROM buchungen;
41
Der geneigte Leser mag dies ausprobieren.
Die vorgenommene Umbenennung bezeichnet man gemeinhin als so genannten Alias für die
betreffende Spalte. Dies ist ein einfacher Bezeichner, der jedoch keine Leerzeichen enthalten
darf. Will oder kann man auf Leerzeichen nicht verzichten, so ist dies mittel doppelter Anführungszeichen möglich. Der gewünschte Ausdruck wird einfach in doppelte Anführungszeichen eingeschlossen. Man kann auf diese Weise sogar konstante Ausdrücke in das Ergebnis
eines SELECT-Statements integrieren, wie das folgende Beispiel verdeutlicht:
SELECT 'Gewinn: ' AS "Gewinn",einnahme-ausgabe AS "Einnahme-Ausgabe"
FROM buchungen;
42
Mittels einfacher Anführungszeichen wird hier eine konstante Zeichenkette für die Ausgabe
definiert. Die doppelten Anführungszeichen sorgen dafür, die Ausgabe der Spaltenüberschriften buchstabengetreu gemäß dem SQL-Statement auszugeben. D.h., doppelte Anführungszeichen zwingen MySQL dazu, deren Inhalt zu zitieren. Da MySQL die beiden konstanten Ausdrücke miteinander zu einer Zeichenkette verbinden würde, müssen diese durch das Schlüsselwort AS voneinander getrennt werden. Grundsätzlich dient AS dazu einen Alias zu definieren, kann aber, so wie wir es bislang gteanhaben, entfallen, wenn der Kontext im Sinne von
MySQL eindeutig ist.
Gewinn
Einnahme–Ausgabe
Gewinn:
8.10
Gewinn:
6.10
Gewinn:
4.80
Wollen wir die kumulierten Umsätze betrachten, kommt die SUM-Funktion zum Tragen.
Gleichzeitig wollen wir die prozentuale Gewinnspanne berechnen. Hierbei ist es wichtig zu
erkennen, dass wir uns nicht auf Zwischenergebnisse einer evtl. bereits vorher berechneten
Spalte beziehen können. Jede Ausgabespalte muss den kompletten Ausdruck für die Ausgabe
enthalten. Den mathematischen Gesetzen folgend, kann die Summe des Gewinns auf zwei Arten errechnet werden:
SELECT SUM(einnahme) AS "Einnahmen", SUM(ausgabe) AS "Ausgaben",
SUM(einnahme) - sum(ausgabe)
AS "Gewinn1",
SUM(einnahme-ausgabe)
AS "Gewinn2",
(SUM(einnahme)/SUM(ausgabe)-1)*100
AS "Marge [%]"
FROM buchungen;
43
Wir erhalten:
Tabelle 12: Einsatz der Summenfunktion SUM
Einnahmen
Ausgaben
Gewinn1
Gewinn2
Marge [%]
103.80
84.80
19.00
19.00
22.405660
Verlassen wir nun unsere Buchungstabelle und wenden uns wieder unserem Bibliotheksbeispiel zu, um die weiteren Funktionen kennen zu lernen. Mit der AVG-Funktion können wir
Stand: 27.12.2013
30
SQL-Einführung mit MySQL
Durchschnittswerte berechnen. Wenn wir also die durchschnittliche Ausleihzahl berechnen
wollen, können wir dies wie folgt tun:
SELECT AVG(ausleihzahl) FROM buecher WHERE leihfrist > 0;
Da wir bislang aber keine nennenswerten Ausleihvorgänge in unserer Bibliothek zu verzeichnen haben, ist das Ergebnis ziemlich mager: 0,2222.
44
In etwa analog sieht es aus, wenn wir den Spitzenreiter aus unseren Ausleihen ermitteln wollen.
Hierzu benötigen wir das Maximum der Ausleihzahl:
SELECT MAX(ausleihzahl) FROM buecher;
Das Ergebnis ist erwartungsgemäß 1. Damit kennen wir jetzt zwar die Anzahl der maximalen
Ausleihen unseres beliebtesten Buches, dessen Autor liegt aber immer noch im Verborgenen.
Falsch denkt der, der jetzt meint, die folgende Abfrage würde das Dunkel erhellen:
SELECT autor, MAX(ausleihzahl) FROM buecher;
Das Ergebnis ist erstaunlich und dürfte kaum den Erwartungen entsprechen. Jedes andere mir
bekannte Datenbanksystem verweigert nämlich die Ausführung und mäkelt eine nicht vorhandene Gruppierungen an. MySQL liefert das Maximum der Ausleihzahl und irgendeinen (hier
den ersten) Autor, was hier zufällig auch noch das richtige Ergebnis darstellt. Ich halte das für
gefährlich. Es handelt sich aber dennoch nicht um einen Fehler in MySQL, sondern um ein
„Feature“. Folgende SQL-Statements mögen den Sachverhalt nochmals illustrieren:
UPDATE buecher SET ausleihzahl=1000 WHERE buchnr=5;
Führen wir unsere Abfrage nochmals aus, so erhalten wir ein identisches Ergebnis, was definitiv, im Sinne unserer angenommenen Geschäftslogik, falsch ist.
SELECT autor, MAX(ausleihzahl) FROM buecher;
45
46
46a
46b
Korrigieren wir also schnell unsere Datenbank.
UPDATE buecher SET ausleihzahl=0 WHERE buchnr=5;
Mit den bisherigen Kenntnissen können wir das Problem nicht lösen. Wir verschieben es daher auf einen späteren Zeitpunkt. Es wird eine Lösung geben – versprochen.
46c
Kommen wir nun zur COUNT()-Funktion. Diese ist immer dann gut, wenn wir Ergebniszeilen zählen wollen. COUNT(*) zählt alle Zeilen, COUNT(<spalte>) zählt die Ergebniszeilen
in Bezug auf eine bestimmte Spalte. Auch hier ist auf NULL-Werte zu achten. Sie werden bei
der Zählung ignoriert.
SELECT COUNT(*) "Anzahl Bücher gesamt",
COUNT(autor) "Anzahl Bücher MIT Autoren"
FROM buecher;
47
Wir erhalten 12 für die Gesamtanzahl und 11 für diejenigen, die auch einen Autor benannt haben.
Es gibt einen Datensatz, dessen Autoreneintrag NULL ist.
Wir wissen nun, dass wir 11 Datensätze haben, in denen das Autorenfeld gefüllt ist. Wie viele
unterschiedliche Autoren wir in unserer Bibliothek haben, wissen wir jedoch nicht. Wir haben,
wenn wir uns erinnern wollen bzw. dies nachprüfen, 4 Werke von Goethe und je 2 von LesStand: 27.12.2013
31
SQL-Einführung mit MySQL
sing und Haage. Je ein Buch ist von Böll, Heitsiek und Schiller. Um nun festzustellen, wie
viel unterschiedliche Autoren wir haben, gibt es das Schlüsselwort DISTINCT. Es ist genau in
dem Sinne anzuwenden, in welchem der deutsche Begriff „distinkt“ bildungssprachlich angewendet wird (abgegrenzt, unterschieden), also von überschneidungsfreien, Mengen gesprochen wird.
SELECT COUNT(DISTINCT autor) FROM buecher;
Demnach haben wir 6 unterschiedliche Autoren, was auch stimmt. Wir haben Goethe, Schiller, Lessing, Heitsiek, Böll und Haage im Angebot. In ähnlicher Weise lassen sich diese Autoren ausgeben, ohne uns mit Doppelungen zu konfrontieren:
SELECT DISTINCT autor FROM buecher;
Diese Abfrage liefert uns neben den Autoren den zusätzlichen NULL-Eintrag, also 7 Ergebniszeilen, was verwirren mag. Durch eine geeigenete WHERE-Bedingung können wir die Datensätze mit NULL-Werten ausselektieren.
SELECT DISTINCT autor FROM buecher WHERE autor IS NOT NULL;
48
49
50
Frage: Was liefert das folgende SQL-Statement für ein Ergebnis und wieso tut es das?
SELECT DISTINCT COUNT(autor) FROM buecher;
Erklärung: Das Ergebnis ist 11. Das entspricht der Anzahl der Zeilen unserer Tabelle, bei denen der Autor ungleich NULL ist. COUNT zählt also jede Autorenangabe und ignoriert in bereits
bekannter Weise den Datensatz ohne Autorenangabe. Nachdem COUNT nun bis 11 gezählt hat,
wird (hier völlig sinnlos) DISTINCT angewendet.
51
4.4 Gruppierungen
Bislang waren wir bescheiden und haben uns mit der einfachen Auswahl von einzelnen Datensätzen zufrieden gegeben bzw. haben spaltenweise Aggregate gebildet. Um Informationen
gezielt zusammenfassen zu können, d.h. sie zu gruppieren, kennt SQL die so genannte GROUP
BY-Klausel.
Im Moment sehen unsere Daten aber noch etwas zu dürftig aus, um interessante Abfragen formulieren zu können. Wir werden daher ein paar (historische) Verleihvorgänge durchführen.
Wir manipulieren daher einfach – alle Geschäftslogik umgehend – unsere Tabellen buecher
und leser.
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
buecher
buecher
buecher
buecher
buecher
buecher
buecher
SET
SET
SET
SET
SET
SET
SET
ausleihzahl
ausleihzahl
ausleihzahl
ausleihzahl
ausleihzahl
ausleihzahl
ausleihzahl
= 10 WHERE buch_nr = 1;
= 12 WHERE buch_nr = 2;
= 3 WHERE buch_nr = 3;
= 8 WHERE buch_nr = 4;
= 8 WHERE buch_nr = 5;
= 6 WHERE buch_nr = 7;
= 3 WHERE buch_nr = 8;
52
Parallel hierzu müssen wir natürlich die Zähler in der Lesertabelle modifizieren, wir wollen ja
konsistent bleiben:
UPDATE
UPDATE
UPDATE
UPDATE
leser
leser
leser
leser
SET
SET
SET
SET
Stand: 27.12.2013
ausleihzahl
ausleihzahl
ausleihzahl
ausleihzahl
= 25 WHERE leser_nr =
= 10 WHERE leser_nr =
= 8 WHERE leser_nr =
= 7 WHERE leser_nr =
1;
3;
4;
5;
53
32
SQL-Einführung mit MySQL
Ändern wir zudem unser Präsenzexemplar von „Nathan der Weise“ und behaupten, es wäre
bereits fünfmal ausgeliehen worden. Die Leihfrist soll abweichend von den anderen Büchern
14 Tage betragen.
UPDATE buecher SET leihfrist = 14, ausleihzahl = 5
WHERE buchnr = 6;
UPDATE leser SET ausleihzahl=ausleihzahl+5
WHERE lesernr = 5;
54
Jetzt passt es wieder, wir haben jeweils 55 Verleihvorgänge gebucht.
Das „Große Guinessbuch der Weltrekorde“ wurde nie ausgeliehen, obwohl dies theoretisch
möglich gewesen wäre. Gleiches gilt für Buch 10. Die anderen nicht ausgeliehenen Bücher
sind Präsenzexemplare. Der Leser „Wolfgang Stuckenberg“ ist zwar Mitglied in unserer Bibliothek, scheint aber vielleicht doch eher auf Videos zu stehen, die wir leider nicht führen. Er
hat niemals ein Buch ausgeliehen.
Nun können wir auch hergehen und unsere Autoren im Sinne eines Rankings nach den Ausleihzahlen abfragen:
SELECT autor, MAX(ausleihzahl) maximum FROM buecher
GROUP BY autor
ORDER BY Maximum DESC;
55
Tabelle 13: Autorenranking
autor
maximum
Goethe
12
Schiller
8
Lessing
8
Heitsiek
6
Böll
3
NULL
0
Haage
0
Über die Summenbildung können wir nach den erfolgreichsten Titeln und deren Autoren forschen:
SELECT autor, SUM(ausleihzahl) summe, titel FROM buecher
GROUP BY autor, titel
ORDER BY summe DESC, autor;
56
Wir erkennen, das Freund Lessing mit seinem „Nathan der Weise“ die Liste anführt.
Mit WHERE selektiert man Zeilen, mit GROUP BY werden selektierte Zeilen zusammengefasst.
Auch auf dieser Ebene lässt sich selektieren. Das ist z.B. immer dann sinnvoll, wenn man beispielsweise nur die Autoren aus der obigen Abfrage sehen möchte, die mehr als 5 Verleihvorgänge zu verzeichnen haben. Hierzu dient die HAVING-Klausel. Bevor wir die Beispielabfrage
formulieren, fassen wir kurz nochmal zusammen, was wir bislang über die Syntax von SELECT wissen. HAVING integrieren wir bereits:
Stand: 27.12.2013
33
SQL-Einführung mit MySQL
SELECT [DISTINCT] <spalte1> [Alias] [, ... <spalteN> [Alias]]
FROM <tabelle>
[WHERE <zeilenbedingung(en)>]
[GROUP BY <gruppierungsspalte(n)>]
[HAVING <gruppierungbedinung(en)>]
[ORDER BY <spalte(n)|spatennummer(n)>];
Jetzt können wir unser obiges Ranking auf die wirklich „nennenswerten“ Autoren beschränken, womit diejenigen gemeint sein sollen, die mehr als 5 Ausleihungen für sich in Anspruch
nehmen können:
SELECT autor, SUM(ausleihzahl) anzahl FROM buecher
GROUP BY autor
HAVING SUM(ausleihzahl) > 5
ORDER BY anzahl DESC;
57
Tabelle 14: Autoren mit mehr als 5 Ausleihungen
autor
anzahl
Goethe
25
Lessing
13
Schiller
8
Heitsiek
6
Zu beachten ist: Der Alias anzahl für SUM(ausleihzahl) kann gewöhnlich nicht in der
HAVING-Klausel verwendet werden, auch wenn MySQL dies zulässt – ein gern gemachter
Fehler bei anderen Datenbankmanagementsystemen!
Eine weitere interessante Fragestellung11 könnte lauten: Welche Autoren haben insgesamt wie
viele Verleihvorgänge zu verzeichnen, wenn nur Bücher ausgewertet werden, die jeweils öfter
als 5-mal verliehen wurden. Uns interessieren jedoch nur Autoren mit mindestens 8 Verleihvorgängen (unabhängig vom Buch).
Die Phantasie des Autors ist jetzt in Bezug auf die Büchertabelle erschöpft. Wir wollen jetzt
mit mehreren Tabellen in einer Abfrage arbeiten. Das eröffnet uns neue Horizonte.
SELECT autor, SUM(ausleihzahl) summe
FROM buecher
WHERE ausleihzahl > 5
GROUP BY autor
HAVING SUM(ausleihzahl) >= 8
ORDER BY summe DESC;
58
Tabelle 15: Autoren mit mindestens 8 Ausleihungen und mehr als 5 Ausleihungen je Buch
autor
summe
Goethe
22
11 Was ist daran interessant? - Die Frage ist rein akademisch und ohne jeden dem Autor bekannten Nutzen.
Ohne Scherz: Man mache sich die feinen Nuancen in den Fragestellungen klar und vergleiche die Abfragen
und deren Resultate. Kleine Änderungen bedingen nicht unerhebliche Wirkungen und haben vor allem auch
inhaltlich unterschiedliche Bedeutungen.
Stand: 27.12.2013
34
SQL-Einführung mit MySQL
autor
summe
Schiller
8
Lessing
8
4.5 Joins I: Inner Joins
Selbst unsere Miniaturdatenbank besteht bereits aus mehreren Tabellen, die miteinander in
Beziehung stehen. Wir haben das eingangs hinreichend diskutiert, als wir uns Gedanken über
die Konsistenz der Daten in den jeweiligen Tabellen gemacht haben. Fast jede reale Abfrage
bezieht sich, quasi als logische Konsequenz aus der Normalisierung, auf mehrere Tabellen.
Damit wir ein wenig Futter für die Auswertung bekommen, gehen wir wie gehabt vor und ergänzen erst mal unsere Verleihtabelle um einige Leihvorgänge. Dank unserer Vorkehrungen
ist dies einfach:
INSERT INTO verleih(lesernr, buchnr) VALUES(1, 2);
INSERT INTO verleih(lesernr, buchnr) VALUES(2, 3);
INSERT INTO verleih(lesernr, buchnr) VALUES(3, 4);
59
Bevor wir nun aber mit prickelnden Auswertungen fortfahren, gönnen wir uns einen kurzen
Blick auf die Theorie kartesischer Produkte.
Innerhalb einer Abfrage lassen sich n (prinzipiell beliebige) Tabellen miteinander verknüpfen.
Beschränken wir uns aber vorerst auf zwei, um den Überblick zu behalten. Für jede weitere
einzubindende Tabelle kann dann vom Ergebnis des Joins zweier Tabellen verknüpft mit der
nächsten zusätzlichen Tabelle ausgegangen werden.
Werden zwei Tabellen mittels eines Joins verknüpft, entsteht im Grunde eine neue Tabelle,
und zwar eine von wesentlich größerem Umfang. Sie besteht aus der Kombination aller Zeilen der ersten Tabelle mit allen Zeilen der zweiten Tabelle. Außerdem hat die neue Tabelle alle
Spalten der beiden involvierten Tabellen. Mathematisch ausgedrückt, ist das ein kartesisches
Produkt.
Allgemein gilt: Das kartesische Produkt einer Tabelle A mit sA Spalten und zA Zeilen sowie
einer Tabelle B mit sB Spalten und zB Zeilen ergibt eine Tabelle A × B (sprich: A Kreuz B)
mit sA+sB Spalten und zAzB Zeilen.
Klingt irgendwie kompliziert – ist es aber nicht. Betrachten wir hierzu einfach unsere Tabellen
leser und verleih. Wir haben 5 Datensätze mit Lesern in der Lesertabelle. Diese besitzt 6
Spalten für die Attribute. Die Verleihtabelle hat 4 Spalten und umfasst aktuell 4 Datensätze.
Ein einfacher Join der folgenden Gestalt
SELECT * FROM leser, verleih;
ist syntaktisch vollkommen korrekt. Er bringt allerdings nicht das Ergebnis, welches der geneigte Leser sich gemeinhin bei der Verknüpfung der beiden Tabellen erträumt. Wie wir uns
mittels Ausprobieren überzeugen können, hat das Resultat 20 Zeilen und 10 Spalten. Jeder
Datensatz der einen Tabelle wird mit einem Datensatz der anderen Tabelle verknüpft. Betrachten wir ausschnittsweise das Ergebnis unserer Abfrage:
Stand: 27.12.2013
35
60
SQL-Einführung mit MySQL
Tabelle 16: Kreuzprodukt der Tabellen leser und verleih
lesernr
nachname
vorname
lesernr
buchnr
1
Brodmann
Karsten
1
1
1
Brodmann
Karsten
1
2
1
Brodmann
Karsten
2
3
1
Brodmann
Karsten
3
4
2
Stuckenberg
Wolfgang
1
1
2
Stuckenberg
Wolfgang
1
2
2
Stuckenberg
Wolfgang
2
3
2
Stuckenberg
Wolfgang
3
4
3
Ellinghaus
Heidelore
1
1
3
Ellinghaus
Heidelore
1
2
3
Ellinghaus
Heidelore
2
3
3
Ellinghaus
Heidelore
3
4
4
Hartmann
Jan
1
1
4
Hartmann
Jan
1
2
4
Hartmann
Jan
2
3
4
Hartmann
Jan
3
4
5
Temmen
Andreas
1
1
5
Temmen
Andreas
1
2
5
Temmen
Andreas
2
3
5
Temmen
Andreas
3
4
...
...
In diesem Ausschnitt sind nur die grau hinterlegten Zeilen im Sinne der Semantik unseres Bibliotheksmodells korrekt. Sie bilden jeweils einen tatsächlichen Geschäftsvorgang ab. Bei den
anderen Datensätzen erkennen wir deren Fehlerhaftigkeit daran, dass die miteinander kombinierten Lesernummern voneinander abweichen, was (vermutlich) nicht im Sinne des Erfinders
war. Diese Erkenntnis führt uns nun sofort zur Lösung des Problems. Wir müssen eine einschränkende Bedingung formulieren, so dass nur die Datensätze miteinander verknüpft werden, bei denen die Lersernummer aus der Tabelle leser mit derjenigen aus der Tabelle verleih gleich ist. Dies realisieren wir mittels einer WHERE-Bedingung12.
SELECT leser.lesernr, nachname, buchnr FROM leser, verleih
WHERE leser.lesernr = verleih.lesernr;
61
Da die Spalten in beiden Tabellen gleich heißen, qualifizieren wir sie mit dem davor gestellten
Tabellennamen. So weiß die Datenbank, auf welche Spalte wir uns beziehen.
12 Hinweis: MySQL kennt auch die alternative Formulierung mittels JOIN, was dann so aussieht:
SELECT leser.lesernr, nachname, buchnr FROM leser LEFT JOIN verleih ON
leser.lesernr = verleih.lesernr;
Stand: 27.12.2013
36
SQL-Einführung mit MySQL
Tabelle 17: Korrekte Tabellenverknüpfung mittels Verknüpfungsbedingung
lesernr
nachname
buchnr
1
Brodmann
1
1
Brodmann
2
2
Stuckenberg
3
3
Ellinghaus
4
An diesem Beispiel erkennen wir auch ein grundlegendes Problem, welches sich immer wieder im Umgang mit relationalen Datenbanken ergibt. Im Normalisierungsprozess werden die
Daten auseinandergerissen und es ist nicht nur mühsam, sondern setzt auch Kenntnisse über
die Daten voraus, wenn sie in einer Abfrage wieder zusammengesetzt werden sollen. Manchmal kann das Qualifizieren von Spalten über die zusätzliche Benennung der Tabelle recht
schreibintensiv werden, weshalb man sich das Leben mit zusätzlichen Aliasbezeichnern erleichtern kann. Das sieht dann so aus:
SELECT a.lesernr, a.nachname, b.buchnr
FROM leser a, verleih b
WHERE a.lesernr = b.lesernr;
62
Wir bezeichnen die Tabelle leser einfach kurz mit a bzw. verleih mit b und beziehen uns
im Rahmen der Qualifikation auf diese Aliasbezeichner.
Neben der einschränkenden Join-Bedingung, die für eine korrekte Datensatzzuordnung erforderlich ist, können nun natürlich alle zusätzlich interessanten Bedingungen definiert werden.
D.h., wir können unser bislang erworbenes Wissen weiterhin uneingeschränkt anwenden. Um
unserem Leben noch mehr Würze zu verleihen, ergänzen wir wieder ein paar Datensätze. Wir
belegen Leser mit Strafgebühren und sperren einige von ihnen. Zusätzlich verändern wir die
Verleihdaten in der Verleihtabelle.
INSERT INTO strafen(lesernr, gebuehr, sperre) VALUES(1, 2,
INSERT INTO strafen(lesernr, gebuehr, sperre) VALUES(2, 1,
INSERT INTO strafen(lesernr, gebuehr, sperre) VALUES(4, 10,
INSERT INTO strafen(lesernr, gebuehr, sperre) VALUES(5, 12,
UPDATE verleih SET
ausleihdatum = DATE_SUB(ausleihdatum, INTERVAL 60 DAY)
WHERE buchnr >=2;
0);
0);
1);
1);
63
Jetzt können wir z.B. eine sortierte Liste der gesperrten Leser erstellen. Gesperrte Leser sind
durch eine 1 im Attribut sperre gekennzeichnet.
SELECT nachname
FROM leser l, strafen s
WHERE l.lesernr = s.lesernr
AND sperre = 1
ORDER BY nachname;
64
Die Leser Hartmann und Temmen sind demnach wegen ungebührlichen Betragens gesperrt.
Welche Leser haben noch Bücher, die sie bereits hätten zurückgeben müssen und wie viele
sind es pro Leser?
SELECT l. lesernr, nachname, COUNT(*)
FROM leser l, verleih v
Stand: 27.12.2013
65
37
SQL-Einführung mit MySQL
WHERE l.lesernr = v.lesernr
AND date_add(ausleihdatum, INTERVAL leihdauer DAY) < CURRENT_DATE
GROUP BY l.lesernr, nachname;
Brodmann, Stuckenberg und Ellinghaus haben jeweils ein Buch, welches sie längst hätten zurückgeben müssen.
4.6 Joins II: Outer Joins
Alle bislang ausgeführten Joins enthielten nur Zeilen, für die in allen beteiligten Tabellen ein
passender Wert für die Join-Bedingung vorhanden war. Gelegentlich ist es aber zweckmäßig,
auch die Zeilen einer Tabelle im Endergebnis zu haben, für die in der anderen Tabelle kein
passender Partner gefunden wird. Solche Joins heißen Outer Joins.
Theoretisch ist zwischen vollständigen und einseitigen Outer Joins zu unterscheiden. Bei einem vollständigen Outer Join enthält die Ergebnismenge des Joins zweier Tabellen A und B
alle Zeilen aus A und alle Zeilen aus B. An die Stelle der jeweiligen Partner aus der jeweils
anderen Tabelle treten NULL-Werte, wenn kein passender Partner existiert. Soweit die Theorie,
denn in der Praxis werden (soweit ich weiß) von den Datenbanken nur einseitige Outer Joins
unterstützt.
Beim einseitigen Outer Join werden alle Datensätze einer Tabelle A sowie alle passenden
Partner einer Tabelle B selektiert. Für die in B fehlenden Partner werden NULL-Werte geliefert.
Die Syntax für Outer Joins unterscheidet sich je nach Datenbanksystem. Der Grund hierfür
dürfte der sein, dass im ursprünglichen SQL-Standard der Outer Join nicht vorgesehen war.
Da er jedoch nützlich ist und verschiedene Datenbankhersteller dies erkannt haben, haben sie
jeweils ihre eigene Erweiterung implementiert, um diese Anforderung mit ihrem System erfüllen zu können. Möchte man beispielsweise eine Liste aller Leser generieren, die ggf. vorhandene Strafgebühren ausweist, so benötigt man hierfür einen Outer Join. Ein Inner Join
würde die Liste lediglich auf die Leser beschränken, die einen Eintrag in der Tabelle strafen besitzen.
SELECT l.nachname, l.vorname, s.gebuehr
FROM leser l, strafen s
WHERE l.lesernr = s.lesernr;
66
Alle anderen Leser würden in solch einer Liste fehlen. Mit MySQL lässt sich ein entsprechender Outer Join wie folgt formulieren:
SELECT l.nachname, l.vorname, s.gebuehr
FROM leser l
LEFT OUTER JOIN strafen s ON (l.lesernr = s.lesernr);
67
Frei von Strafen ist augenscheinlich nur die einzige Frau in unserem Reigen – äußert betrüblich für die Männerwelt.
Das Ergebnis lässt hinsichtlich der Formatierung etwas zu wünschen übrig. Statt des bei gebuehr angegebenen NULL-Wertes sähe es schöner aus, dort eine 0 zu zeigen. Für den speziellen Fall, NULL-Werte durch etwas anderes zu ersetzen, gibt es in mySQL die Funktion IFNULL():
SELECT l.nachname, l.vorname, IFNULL(s.gebuehr ,0)
Stand: 27.12.2013
68
38
SQL-Einführung mit MySQL
FROM leser l
LEFT OUTER JOIN strafen s ON (l.lesernr = s.lesernr);
Im nächsten Beispiel wollen wir ermitteln, wie viel Prozent unserer Bücher ausgeliehen sind.
Prinzipiell erscheint die Lösung einfach:
Verliehene Bücher [%]=
Anzahl verliehene Bücher⋅100
Gesamtanzahl Bücher
Leider ist dieses Verfahren nicht so einfach in einen SELECT-Befehl umzusetzen:
SELECT COUNT(*)*100 FROM verleih / SELECT COUNT(*) FROM buecher;
ist völliger Blödsinn. Mit dem Inner Join gibt es auch Probleme, denn der Ansatz
SELECT … irgendwas …
FROM buecher b, verleih v
WHERE b.buchnr = v.buchnr;
liefert maximal die Anzahl verliehener Bücher (weshalb ich aus Faulheit auf komplizierte Berechnungen an dieser Stelle gleich verzichtet habe).
Mit dem Outer Join gibt es jedoch eine einfache und elegante Lösung. Für diejenigen Bücher,
die nicht verliehen sind, liefert ein Outer Join in Bezug auf die buchnr in der Verleihtabelle
NULL. NULL-Werte werden durch die COUNT-Funktion ignoriert, weshalb hierüber die Anzahl
verliehener Bücher ermittelt werden kann. Die Gesamtanzahl der Zeilen in der Ergebnismenge ist die Anzahl aller Bücher, die sich aus buecher ergibt und mit COUNT(*) als Anzahl aller Zeilen ermittelt werden kann. Ein geeignetes SQL-Statement sähe daher so aus:
SELECT COUNT(v.buchnr) * 100 / COUNT(*)
FROM buecher b
LEFT OUTER JOIN verleih v ON (b.buchnr = v.buchnr);
69
Wir sehen: 33,33% unserer vorhandenen Bücher sind aktuell verliehen.
4.7 Single-Row-Subqueries
Wenn wir nur den oder die Titel ausgeben wollen, der oder die am meisten ausgeliehen wurden, ohne alle weiteren verliehenen Titel mit auszugeben, helfen uns unsere bisherigen Kenntnisse nicht weiter.
Beschränken wir uns zunächst auf das meistgelesene Exemplar und unterschlagen die Tatsache, dass es von einem Titel mehrere Exemplare geben kann, die zusammen eine höhere Ausleihzahl erreichen, als das meistgelesene Exemplar. Stellen wir also zunächst die Frage, welches ist die Zeile in der Büchertabelle mit dem größten Wert für die Ausleihzahl. Wir wissen
bereits, dass
SELECT autor, titel, MAX(ausleihzahl)
FROM buecher GROUP BY autor, titel;
nicht zielführend ist – die einleuchtende Begründung kennen wir. Weniger leicht verständlich
mag sein, dass auch
SELECT autor, titel, ausleihzahl
FROM buecher
WHERE ausleihzahl = MAX(ausleihzahl);
Stand: 27.12.2013
70
39
SQL-Einführung mit MySQL
nicht funktioniert. Der einzige Erfolg ist ein protestierender SQL-Interpreter. Nun, aus Perspektive von SQL, hängt der Ausdruck MAX(ausleihzahl) auch irgendwie in der Luft.
SQL-Funktionen arbeiten gewöhnlich nur innerhalb einer Abfrage, also einer SELECT-Klausel. Konsequenterweise ist es daher möglich, innerhalb eines SELECT-Befehls weitere Abfragen unterzubringen. Man nennt eine solche Konstruktion Subquery.
SELECT autor, titel, ausleihzahl
FROM buecher
WHERE ausleihzahl = (
SELECT MAX(ausleihzahl)
FROM buecher
);
71
Jetzt wissen wir (mal wieder), dass der Faust von Goethe mit 13 Verleihen unser erfolgreichstes Exemplar in der Bibliothek ist. Das eigentliche Problem, den oder die meistgelesenen Titel
zu finden, haben wir also nicht gelöst. Hinweis: Es bringt nichts, in dieser Konstruktion mit
der SUM-Funktion hantieren zu wollen. Der geneigte Leser mag dies ausprobieren. Im Moment wissen wir jedenfalls, dass wir im Rahmen der WHERE-Klausel ein weiteres SELECT, ein
so genanntes Subselect, verwenden können, das uns genau einen Datensatz liefert.
Damit lassen sich schon interessante Fragen beantworten. Z.B.: Welche Bücher werden überdurchschnittlich oft verliehen?
SELECT *
FROM buecher
WHERE ausleihzahl > (
SELECT AVG(ausleihzahl) FROM buecher
);
72
Ermitteln wir alle Daten aus der Lesertabelle für den Leser mit den höchsten Gebühren:
SELECT l.lesernr, nachname, vorname, wohnort, ausleihzahl,
eintrittsdatum
FROM leser l, strafen s
WHERE l.lesernr = s.lesernr
AND gebuehr = (
SELECT MAX(gebuehr)
FROM strafen
);
73
Alternativ ist auch diese Formulierung möglich:
SELECT *
FROM leser WHERE lesernr = ( SELECT lesernr
FROM strafen
WHERE gebuehr = ( SELECT MAX(gebuehr)
FROM strafen
)
74
);
In jedem Fall ist es Andreas Temmen, der die höchsten Strafgebühren besitzt.
Stand: 27.12.2013
40
SQL-Einführung mit MySQL
4.8 Multiple-Row-Subqueries
Bislang mussten wir darauf achten, dass eine Subquery genau eine Ergebniszeile zurückliefert. Ein solches Vorgehen ist aber nicht für jede Problemstellung geeignet, weshalb es in SQL
die Operatoren IN, ANY, ALL und EXISTS gibt, die einen Vergleich eines Einschränkungskriteriums in WHERE oder HAVING mit einer beliebigen Anzahl von Zeilen eines Subselects ermöglichen. Wollen wir beispielsweise eine Liste aller verliehenen Bücher erstellen, so können wir das über einen Join realisieren. Das kennen wir schon:
SELECT buecher.*
FROM buecher, verleih
WHERE buecher.buchnr = verleih.buchnr;
Alternativ können wir die Abfrage aber auch so gestalten, dass wir fragen, ob die Bücher der
Hauptabfrage als Untermenge der Menge einer Unterabfrage enthalten sind:
SELECT *
FROM buecher
WHERE buchnr IN (
SELECT buchnr
FROM verleih
);
75
Betrachten wir nun die Operatoren ANY und ALL. Sie sind etwas gewöhnungsbedürftiger als
IN, da ihre Einsatzmöglichkeiten weniger offensichtlich sind. Im Grunde sind sie (fast) überflüssig, da es in nahezu allen Fällen alternative Formulierungen gibt, die ohne sie auskommen. ANY und ALL können nicht allein benutzt werden, sondern müssen immer mit einem der
Operatoren =, <>, <, <=, >, >= kombiniert werden. Wir betrachten das an folgendem Beispiel. Wir erstellen zum x-ten Mal eine Liste der verliehenen Bücher:
SELECT *
FROM buecher
WHERE buchnr = ANY (
SELECT buchnr
FROM verleih
);
76
Der Operator =ANY bedeutet soviel wie „gleich irgendeinem der Werte in der folgenden Menge“.
Die Menge wird im Beispiel von der Subquery geliefert, nämlich alle Buchnummern von verliehenen Büchern. Ohne Einschränkung kann festgehalten werden, =ANY ist identisch zu IN.
Welches ist zur Abwechslung das am wenigsten ausgeliehene, ausleihbare Exemplar?
SELECT *
FROM buecher
WHERE ausleihzahl <= ALL (
SELECT ausleihzahl
FROM buecher
WHERE leihfrist > 0 AND ausleihzahl > 0
);
77
Nicht gut. Neue Überlegung:
SELECT *
FROM buecher
Stand: 27.12.2013
78
41
SQL-Einführung mit MySQL
WHERE ausleihzahl = (
SELECT MIN(ausleihzahl)
FROM buecher
WHERE leihfrist > 0
);
Anderes Ergebnis – aber ebenso unbefriedigend. Diese Abfragen liefern bei insgesamt unterschiedlichen Ergebnissen auch all die Bücher, die ausleihbar sind aber nie ausgeliehen wurden. Sogar nicht ausleihbare Bücher werden angezeigt. Wir müssen folglich anders vorgehen:
SELECT *
FROM buecher
WHERE (ausleihzahl > 0) AND (ausleihzahl <= ALL
(
SELECT ausleihzahl
FROM buecher
WHERE leihfrist > 0
AND ausleihzahl > 0
));
79
Der Operator ALL drückt aus, dass die definierte Bedingung für alle Datensätze der Hauptabfrage gelten muss.
Analog zum eben gelösten Problem sind wir auch in der Lage, die Frage nach dem meistgelesenen Titel zu beantworten:
SELECT autor, titel, SUM(ausleihzahl)
FROM buecher
GROUP BY autor, titel
HAVING SUM(ausleihzahl) >= ALL (
SELECT SUM(ausleihzahl)
FROM buecher
GROUP BY autor, titel
);
80
Das ist, wir wissen es eigentlich alle, nicht nur ein Buch(titel), sondern derer zwei. Der
„Faust“ als Einzelexemplar sowie „Nathan der Weise“, der im Doppelpack vertreten ist, bilden unsere Spitzenreiter. Ihnen gebührt gemeinsam der Thron in unserer Hall of Fame der
meistgelesenen Buchtitel.
EXISTS ist ein eher selten benötigtes Konstrukt. Die mit EXISTS definierte Bedingung ist immer dann wahr, wenn die Subquery mindestens einen Datensatz liefert. D.h., mit EXISTS
kann man prüfen, ob gewisse Daten in der Datenbank vorhanden sind.
Ein Beispiel wäre: Wenn in der Datenbank Bücher von Lessing vorhanden sind, dann wollen
wir nicht nur die Werke von Lessing, sondern gleich eine Übersicht aller Klassiker sehen. Ansonsten wollen wir nichts sehen.
SELECT *
FROM buecher
WHERE gruppe = 'K'
AND EXISTS ( SELECT * FROM buecher
WHERE autor = 'Lessing'
);
Stand: 27.12.2013
81
42
SQL-Einführung mit MySQL
Zur Probe: Einfach mal einen nicht vorhandenen Autor einsetzen. Betrachten wir zu diesem
Thema noch die verschiedenen Möglichkeiten alternativer Formulierungen:
Tabelle 18: Alternativen zu ANY, ALL und EXISTS
ANY / ALL / EXISTS
Alternative
WHERE x = ANY
( SELECT s
FROM ...
)
WHERE x IN
( SELECT s
FROM ...
)
WHERE x <[=] ANY
( SELECT s
FROM ...
)
WHERE x <[=]
( SELECT MIN(s)
FROM ...
)
WHERE x >[=] ANY
( SELECT s
FROM ...
)
WHERE x >[=]
( SELECT MIN(s)
FROM ...
)
WHERE x <> ANY
( SELECT s
FROM ...
)
(fast immer erfüllt)
WHERE x = ALL
( SELECT s
FROM ...
)
(fast nie erfüllt)
WHERE x <[=] ALL
( SELECT s
FROM ...
)
WHERE x <[=]
( SELECT MAX(s)
FROM ...
)
WHERE x >[=] ALL
( SELECT
FROM ...
)
WHERE x >[=]
( SELECT MAX(s)
FROM ...
)
WHERE x <> ALL
( SELECT s
FROM ...
)
WHERE x NOT IN
( SELECT s
FROM ...
)
WHERE EXISTS
( SELECT *
FROM ...
)
WHERE 0 <
( SELECT COUNT(*)
FROM ...
)
WHERE NOT EXISTS
( SELECT *
FROM ...
)
WHERE 0 =
( SELECT COUNT(*)
FROM ...
)
Obige Ersetzungstabelle ist jedoch mit Vorsicht zu genießen. Denn wer jetzt meint, gemäß
dieser Tabelle (grau hinterlegt) eine Ersetzung für das obige Problem des meistgelesenen TiStand: 27.12.2013
43
SQL-Einführung mit MySQL
tels gefunden zu haben, der irrt womöglich (je nach Datenbankmanagementsystem). Das naheliegende SQL-Statement:
SELECT autor, titel, SUM(ausleihzahl)
FROM buecher
GROUP BY autor, titel
HAVING SUM(ausleihzahl) >=
( SELECT MAX(SUM(ausleihzahl))
FROM buecher
GROUP BY autor, titel
);
82
lässt sich auf Grund der Schachtelung der Aggregatfunktionen nicht mit jedem Datenbanksystem ausführen – Oracle macht hier keine Probleme, andere Datenbanksysteme lassen solche
Konstruktionen jedoch nicht immer zu. MySQL bemängelt eine falsche Nutzung einer Gruppierungsfunnktion, also die Schachtelung von MAX und SUM. Es gibt also in der Tat Fälle, in
denen die obigen Ersetzungen nicht zulässig sind, so dass ANY, ALL oder EXISTS zum Einsatz kommen müssen. – Nun, es wäre ja auch sinnlos gewesen, diese Operatoren zu implementieren, wenn man sie eigentlich überhaupt nicht bräuchte, oder?
4.9 Correlated Subqueries
In den letzten beiden Abschnitten waren alle Subqueries, unabhängig von der Anzahl der gelieferten Ergebniszeilen, syntaktisch wie unabhängige Abfragen formuliert. Jede Unterabfrage
hätten wir aus dem Gesamtbefehl ausschneiden und stets als eigenständige Abfrage ausführen
können. Dies ist bei korrelierten Unterabfragen (Correlated Subqueries) anders. Dort gibt es
zwischen Haupt- und Unterabfrage immer eine gemeinsame Spalte (auch Korrelationsvariable genannt), auf die sich beide Teile der Abfrage beziehen. Eine herausgeschnittene Correlated Subquery ist folglich keine syntaktisch korrekte Abfrage mehr.
Eine Bemerkung noch vorweg, bevor wir uns das in der Praxis ansehen. Correlated Subqueries gehören zu den unansehnlichsten Konstrukten in SQL. Glücklicherweise werden wir
gleich sehen, dass man in sehr vielen Fällen auch auf sie verzichten kann.
Betrachten wir nochmals unsere Liste verliehener Bücher, so kann diese mittels einer korrelierten Unteranfrage auch so formuliert werden:
SELECT *
FROM buecher
WHERE buchnr = (
SELECT buchnr
FROM verleih
WHERE buchnr = buecher.buchnr
);
83
Als erstes sollte auffallen, dass die Subquery tatsächlich nicht mehr selbständig existieren
kann, da die Spalte buecher.buchnr nicht im FROM der Subquery aufgeführt ist. Die Unterabfrage ist für sich allein syntaktisch nicht mehr korrekt. buchnr der Tabelle buecher ist
hier die Korrelationsvariable der Abfrage.
Die Hauptabfrage arbeitet alle Zeilen der Büchertabelle ab und prüft, ob die gerade aktuelle
Zeile die WHERE-Bedingung erfüllt. Dazu ist es offensichtlich erforderlich, dass die Subquery
für jede Zeile der Büchertabelle neu ausgewertet wird, da die Buchnummer ja stets eine andeStand: 27.12.2013
44
SQL-Einführung mit MySQL
re ist. Dies ist ein wichtiger Unterschied zu gewöhnlichen Subqueries, weshalb man sich in
umfangreichen Datenbeständen schon Gedanken machen sollte, ob es nicht eine günstigere
Formulierung gibt. Gerade für die soeben diskutierte Fragestellung haben wir bereits bessere
Alternativen kennen gelernt.
Wir wollen diese doch eher seltenen Abfragen nicht weiter vertiefen und mit einem letzten
Beispiel schließen. Hierzu vereinfachen wir jedoch die Wohnortangabe in unserer Lesertabelle und sorgen gleichzeitig dafür, dass einige unserer Leser zu Demonstrationszwecken umziehen:
UPDATE leser SET wohnort = 'Osnabrück' WHERE lesernr IN (1,2,3);
UPDATE leser SET wohnort = 'Rheine' WHERE lesernr IN (4,5);
84
Jetzt lässt sich z.B. folgende interessante Aufgabe stellen: Ermitteln Sie für jede Stadt den Leser mit den höchsten Gebühren!
SELECT nachname, wohnort, gebuehr
FROM leser l, strafen s
WHERE l.lesernr = s.lesernr
AND gebuehr =
( SELECT MAX(gebuehr)
FROM leser ll, strafen ss
WHERE ll.lesernr = ss.lesernr AND ll.wohnort = l.wohnort
);
85
Diese Abfrage ist erheblich komplizierter als das letzte Beispiel und man muss schon genau
hinsehen, um zu erkennen, dass ll.wohnort mit l.wohnort aus der Hauptabfrage korreliert. Durch die Alias-Bezeichner geht ein wenig der Überblick verloren. Jedenfalls sind
Brodmann für Osnabrück und Temmen für Rheine die Leser mit dem höchsten Gebührenkonto.
5 Schlussbemerkung
Wer jetzt glaubt, über SQL Bescheid zu wissen, der unterliegt einem massiven Irrglauben. Wir
haben nicht mal näherungsweise alle Möglichkeiten des SELECT-Befehls erarbeitet. Im Rahmen einer einführenden Schulung soll das aber dennoch genügen. In der Tat lassen sich mit
diesem Wissen die meisten Problemstellungen des täglichen Lebens bereits erfolgreich bewältigen.
Für ein Reporting auf Basis eines Reportgenerators mag folgender Tipp noch ganz hilfreich
sein.
Die meisten Reportgeneratoren versprechen tolle SQL-Unterstützung, die sich im harten Praxisalltag aber häufig als unzulänglich erweist. Spätestens dann, wenn es um den nicht unwichtigen Aspekt der Geschwindigkeit geht, sieht es oft düster aus. Mittels so genannter Views
können wir Abfragen in der Datenbank permanent speichern. Dies erspart der Datenbank die
aufwändige Arbeit, ein SQL-Statement bei jedem Datenabruf zu parsen, Zugriffspfade zu berechnen usw. Ein Report greift dann, wie auf eine Tabelle, einfach auf den View zu, was dann
erheblich schneller ist. Ein View kann mit folgender Syntax generiert werden:
CREATE [OR REPLACE] <viewname> AS
<select-statement>;
Im konkreten Fall einer Verleihliste sieht das dann so aus:
Stand: 27.12.2013
45
SQL-Einführung mit MySQL
CREATE OR REPLACE VIEW verleihliste AS
SELECT l.nachname, l.vorname, b.autor, b.titel,
v.ausleihdatum+v.leihdauer rueckgabedatum
FROM leser l, buecher b, verleih v
WHERE l.lesernr = v.lesernr
AND
b.buchnr = v.buchnr;
86
Eine Abfrage im Reportgenerator formuliert man dann ganz einfach so:
SELECT * FROM verleihliste;
Als absolute Schlussbemerkung sollte Folgendes hängen bleiben: SQL ist ganz offensichtlich
nicht so wahnsinnig kompliziert. Mit etwas logischer Überlegung kann man vergleichsweise
einfach seine Ziele erreichen. Dennoch bleibt festzuhalten, dass es einfach ist, ohne viel Witz
eine Frage zu stellen, die umgekehrt sehr viel Witz bei der Formulierung des entsprechenden
SQL-Statements erfordert. Bleibt lediglich offen, wie man das seinem Vorgesetzten klar
macht, der mal so eben – weil es ja so leicht ist – einen ausgefeilten Bericht innerhalb der
nächsten zehn Minuten erwartet.
In diesem Sinne: Viel Spaß mit SQL!
Karsten Brodmann
Stand: 27.12.2013
46
87