Inhaltsverzeichnis

Inhaltsverzeichnis
Das Script für die Lehrveranstaltung Datenmanagement wurde im Wintersemester 2007/2008
komplett überarbeitet und neu strukturiert. Wir bitten darum, eventuelle Fehler im Script an
Milan Karow ([email protected]) zu melden.
Inhaltsverzeichnis
4 Structured Query Language
4.1 SQL als Standard . . . . . . . . . . . . . . . . . . .
4.2 Ziele . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3 Bezeichner . . . . . . . . . . . . . . . . . . . . . . .
4.4 Werte . . . . . . . . . . . . . . . . . . . . . . . . .
4.4.1 Zeichenketten . . . . . . . . . . . . . . . . .
4.4.2 Zahlen . . . . . . . . . . . . . . . . . . . . .
4.4.3 Null-Werte . . . . . . . . . . . . . . . . . .
4.5 Datentypen . . . . . . . . . . . . . . . . . . . . . .
4.5.1 Numerische Datentypen . . . . . . . . . . .
4.5.2 Zeitbezogene Datentypen . . . . . . . . . .
4.5.3 Zeichenkettenbezogene Datentypen . . . . .
4.6 Erstellen von Tabellen (CREATE TABLE) . . . .
4.7 Ändern der Tabellenstruktur (ALTER TABLE) . .
4.8 Entfernen von Tabellen (DROP TABLE) . . . . .
4.9 Einfügen von Daten (INSERT) . . . . . . . . . . .
4.9.1 Direktes Einfügen . . . . . . . . . . . . . .
4.9.2 Einfügen aus anderen Tabellen . . . . . . .
4.10 Abfragen (SELECT) . . . . . . . . . . . . . . . . .
4.10.1 Einfache Abfragen . . . . . . . . . . . . . .
4.10.2 Formulierung von Bedingungen (WHERE)
4.10.3 Sortieren (ORDER BY) . . . . . . . . . . .
4.10.4 JOIN-Syntax . . . . . . . . . . . . . . . . .
4.10.5 Aggregation von Daten . . . . . . . . . . .
4.10.6 Gruppenbildung (GROUP BY) . . . . . . .
4.10.7 Gruppenbedingungen (HAVING) . . . . . .
4.10.8 Reihenfolge bei der Abfragenberechnung . .
4.10.9 Unterabfragen . . . . . . . . . . . . . . . .
4.11 Ändern von Daten (UPDATE) . . . . . . . . . . .
4.12 Löschen von Daten (DELETE) . . . . . . . . . . .
51
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
52
52
52
52
53
53
54
54
55
55
56
57
58
60
61
61
61
62
62
62
63
65
65
69
70
70
71
71
74
74
4 Structured Query Language
4 Structured Query Language
4.1 SQL als Standard
Die Structured Query Language (SQL) ist eine Sprache zur Definition, Abfrage und Manipulation von Daten in relationalen Datenbanken. Sie wurde erstmals vom American National
Standards Institute (ANSI) 1986 und ein Jahr später von der International Organisation for
Standardization (ISO) standardisiert. 1992 wurde von der ISO die nächste Version des Standards veröffentlicht, die unter den Namen SQL-92 oder SQL2 bekannt ist. Alle aktuellen (relationalen) Datenbankmanagementsysteme ((R)DBMS) halten sich im Wesentlichen an diese
Standardversion. Auch dieses Skript orientiert sich an SQL-92.
Hersteller von DBMS implementieren in ihren Produkten häufig zusätzliche Funktionalitäten
oder weichen geringfügig von dem Standard ab. Eine konkrete produktspezifische Variante der
SQL-Sprache wird SQL-Dialekt genannt. In diesem Skript wird aus Gründen der Nachvollziehbarkeit und Praxistauglichkeit der Dialekt des MySQL Community Server 5.0 mit InnoDBEngine verwendet. Selbstverständlich wird hier der Dialekt und Funktionsumfang von MySQL
nicht vollständig beschrieben. Weiterführende Informationen findet der interessierte Leser im
MySQL Referenzhandbuch.1
Viele durch die unterschiedlichen Hersteller im Laufe der Zeit eingebrachte Erweiterungen zu
SQL-92 wurden auch von ISO 1999 und 2003 standardisiert. Einige der Themenbereiche, die
dort angesprochen werden, sind Objektorientierung, XML-Einbindung und rekursive Anfragen.
Diese Themen sind jedoch nicht Gegenstand dieses Skripts.
4.2 Ziele
SQL wurde entwickelt, um die Benutzer von DBMS bei folgenden Aufgaben zu unterstützen:
• Erstellen von Datenbank- und Relationsstrukturen
• Erstellen von Datenbank- und Relationsstrukturen Durchführung grundlegender Datenmanagementaufgaben, wie z.B. Hinzufügen, Modifikation und Löschen von Daten innerhalb
der Datenbank
• Ausführen von einfachen und komplexen Anfragen
SQL hat eine relativ einfache Syntax und ist semantisch an die englische Sprache angelehnt. Es
besteht hauptsächlich aus zwei Teilen:
• Data Definition Language (DDL) - zum Definieren von Datenbankstrukturen und Steuerung der Datenzugriffsrechte
• Data Manipulation Language (DML) - zum Auslesen und Aktualisieren von Daten
SQL ist generell eine deklarative Sprache (im Gegensatz zu imperativen Sprachen, wie den
Programmiersprachen C oder Java). Der Benutzer formuliert also im Code, welche Information
(was) benötigt wird und nicht wie diese zu extrahieren und berechnen ist.
4.3 Bezeichner
Bezeichner werden in SQL benutzt, um Objekte (wie z.B. Datenbanken, Tabellen, Spalten und
Alias) innerhalb des DBMS zu identifizieren. Ein Standardbezeichner kann aus großen und kleinen lateinischen Buchstaben, Ziffern und dem Unterstrichzeichen bestehen und muss mit einem
1
MySQL Referenzhandbuch ist in verschiedenen Varianten unter http://dev.mysql.com/doc/ zu finden.
52
4 Structured Query Language
Buchstaben anfangen. Darüber hinaus ist es in MySQL möglich, auch andere Zeichen zu verwenden, allerdings muss der Bezeichner in diesem Fall stets in Anführungszeichen gesetzt werden.
Das Anführungszeichen ist hier der Backtick (zu finden eine Taste rechts vom ß, mit Umschalttaste). Das Anführungszeichen muss auch benutzt werden, wenn ein reserviertes Wort als
Bezeichner genutzt werden soll. Reservierte Wörter sind solche, die in der Sprache SQL eine besondere Bedeutung haben (wie z.B. Befehlsklauseln, Datentypen, Funktionen und Operatoren)2 .
Beispiele von Bezeichnerverwendung sind:
EineTabelle
Tabelle332
‘Eine schöne Tabelle‘
‘Lieferant/Artikel‘
MySQL unterstützt Namen, die aus einem oder mehreren Bezeichnern bestehen. Die Bestandteile
eines mehrteiligen Namens müssen durch Punkte getrennt werden. Die ersten Bestandteile eines
mehrteiligen Namens agieren als Qualifikationsmerkmal, das den Kontext beeinflusst, in dem
der endgültige Bezeichner interpretiert wird.
Spaltenreferenzierung
col name
tbl name.col name
db name.tbl name.col name
Bedeutung
Die Spalte col name einer in der Anweisung verwendeten Tabelle hat diesen Namen.
Die Spalte col name der Tabelle tbl name aus
der Standarddatenbank.
Die Spalte col name der Tabelle tbl name aus
der Datenbank db name.
Das Präfix tbl name oder db name.tbl name muss für eine Spaltenreferenzierung in einer Anweisung nicht angeben werden, sofern die Referenzierung eindeutig ist.
Die Unterscheidung von Groß- und Kleinschreibung bei Bezeichnern ist generell vom Betriebssystem abhängig. Unter Microsoft Windows kann man annehmen, dass nicht zwischen Großund
Kleinschreibung unterschieden wird. Allerdings sollte innerhalb eines Befehls eine durchgehend
einheitliche und konsequente Schreibweise verwendet werden.
4.4 Werte
4.4.1 Zeichenketten
Eine Zeichenkette (String) ist eine Abfolge von Zeichen, die in einfache Anführungszeichen gesetzt ist 3 . Innerhalb eines Strings haben bestimmte Sequenzen jeweils eine spezielle Bedeutung.
Jede dieser Sequenzen beginnt mit einem Backslash. Dieser wird häufig als Escape-Zeichen bezeichnet. Es stehen unter anderem folgende Escape-Sequenzen zu Verfügung:
2
MySQL gestattet auch die Verwendung bestimmter Schlüsselwörter als Bezeichner ohne Anführungszeichen, da
viele Benutzer sie in der Vergangenheit bereits eingesetzt haben. Beispiel: DATE, ENUM, TEXT, TIME
3
Doppelte Anführungszeichen können unter Umständen auch angewendet werden.
53
4 Structured Query Language
\’
\“
\n
\r
\t
\n
\\
einfaches Anführungszeichen (’)
doppeltes Anführungszeichen (“)
Zeilenwechsel bzw. -vorschub
Absatzschaltung
Tabulator
Zeilenwechsel bzw. -vorschub
Backslash (umgekehrter Schrägstrich)
Beispiel:
’Das ist eine Zeichenkette’
’Ein \n \’String\’ \n ist auch eine Zeichenkette.’
4.4.2 Zahlen
Ganze Zahlen werden als Abfolge von Ziffern dargestellt. Fest- und Gleitkommazahlen verwenden
den Punkt als Dezimaltrennzeichen. Bei allen Zahlentypen werden durch ein vorangestelltes Plusoder Minuszeichen negative bzw. positive Werte angezeigt.
4.4.3 Null-Werte
Der Wert Null bedeutet keine Daten“. Die Groß-/Kleinschreibung wird bei Null nicht unter”
schieden. Ein Null-Wert unterscheidet sich maßgeblich von Werten wie 0 für numerische Typen
oder vom Leer-String ’ ’ für String-Typen:
• In Spalten, die zum Primärschlüssel gehören, werden Null-Werte automatisch verboten.
• Null-Werte können in jeder Spalte manuell verboten werden, in dem der Zusatz Not
Null bei der Spaltendefinition verwendet wird.
• Der Wert eines Ausdrucks der Form p ⊕ q, wobei ⊕ für ein Element der Operatormenge
{<, >, =, <>, +, −, ∗, /} steht, beträgt dann Null, wenn mindestens eines der Argumente
p oder q Null ist. Aus diesem Grund exisitieren mit IS NULL und IS NOT NULL in SQL
spezielle Vergleichsfunktionen, welche einen Vergleich mit Null ermöglichen.
• Die Gruppierungs- und Sortierfunktionen DISTINCT, GROUP BY und ORDER BY betrachten
alle Null-Werte als gleich.
• SQL benutzt eine dreiwertige Logik mit den Werten True, False und Null. Der logische
Wert von zusammengesetzten logischen Ausdrücken wird dabei – wie in folgenden Tabellen
dargestellt – bestimmt:
p
true
true
true
false
false
false
Null
Null
Null
q
true
false
Null
true
false
Null
true
false
Null
p∧q
true
false
Null
false
false
false
Null
false
Null
p∨q
true
true
true
true
false
Null
true
Null
Null
54
p
true
false
Null
NOT p
false
true
Null
4 Structured Query Language
• Aggregationsfunktionen wie COUNT(), MIN() und SUM() ignorieren Null-Werte. Eine Ausnahme bildet die Funktion COUNT(*), die Zeilen und nicht einzelne Spaltenwerte zählt.
• MySQL behandelt Null-Werte für manche Datentypen abweichend. Wird beispielsweise
Null in eine Integer-Spalte eingefügt, welche das AUTO INCREMENT-Attribut gesetzt hat,
wird stattdessen die nächste Folgenummer eingesetzt.
4.5 Datentypen
Jeder Spalte innerhalb einer Tabelle muss ein Datentyp zugewiesen werden. Es gibt grundsätzlich
numerische, zeitbezogene und zeichenkettenbezogene Datentypen.
4.5.1 Numerische Datentypen
Bei numerischen Datentypen wird weiter zwischen exakten Datentypen (ganze Zahlen, Festkommazahlen) und gerundeten Datentypen (Gleitkommazahlen) unterschieden. Exakte Datentypen
haben eine feste Repräsentation. Sie bestehen aus Ziffern, einem optionalen Komma und einem
optionalen Vorzeichen. Alle Berechnungen werden exakt durchgeführt und es gibt keine Rundungsfehler. Gleitkommazahlen dienen dagegen einer approximativen Darstellung reeller Zahlen.
Sie stellen einen viel größeren Wertebereich zur Verfügung, haben dafür aber nur eine begrenzte
Genauigkeit. Dadurch können bei Berechnungen Rundungsfehler entstehen und einige wichtige
mathematische Rechenregeln werden außer Kraft gesetzt.
Exakte Datentypen
BOOLEAN:
Dieser Datentyp dient der Darstellung zweier möglicher Wahrheitswerte (TRUE und FALSE).4
SMALLINT [UNSIGNED]:
Repräsentiert einen verkürzten Bereich bereich von Ganzzahlen (Integer). Der vorzeichenbehaftete Bereich liegt zwischen -32768 und 32767. Der vorzeichenlose Bereich liegt zwischen 0 und
65535.
INTEGER [UNSIGNED], INT [UNSIGNED]:
Repräsentiert einen Ganzzahlenbereich zwischen -2147483648 und 2147483647 (vorzeichenbehaftet), bzw. zwischen 0 und 4294967295 (vorzeichenlos).
{DECIMAL | NUMERIC}[(M[,D])] [UNSIGNED]:
Exakte Festkommazahl. M ist die Gesamtzahl der Dezimalstellen (Genauigkeit), D die Anzahl
der Stellen hinter dem Dezimalpunkt. Der Dezimalpunkt sowie das Zeichen ’-’ (für negative
Zahlen) werden bei der Zählung für M nicht berücksichtigt. Wenn D 0 ist, haben die Werte keinen
Dezimalpunkt und keine Nachkommastellen. Die maximale Anzahl der Stellen (M) beträgt bei
DECIMAL 65, die maximale Anzahl unterstützter Dezimalstellen (D) 30. Wird D weggelassen,
wird als Vorgabe 0 verwendet; fehlt die Angabe M, ist 10 der Standardwert. Sofern angegeben,
verbietet UNSIGNED negative Werte. Berechnungen in den Grundrechenarten (+, -, *, /) erfolgen
bei DECIMAL-Spalten stets mit einer Genauigkeit von 65 Stellen.
Gerundete Datentypen
4
MySQL interpretiert BOOLEAN (auch BOOL) als TINYINT(1). Dabei sind TRUE und FALSE Aliase für 1 und 0. Aus
diesem Grund wird BOOLEAN den numerischen Datentypen zugeordnet.
55
4 Structured Query Language
FLOAT[(M,D)] [UNSIGNED]
Kleine Gleitkommazahl (mit einfacher Genauigkeit). Darstellbar sind Werte aus der Menge
[−3, 40 · 1038 ; −1, 18 · 10−38 ] ∪ {0} ∪ [1, 18 · 10−38 ; 3, 40 · 1038 ]. M ist die Gesamtzahl von Dezimalstellen, D die Anzahl der Stellen hinter dem Dezimalpunkt. Wenn M und D nicht angegeben
werden, werden die Werte in diesem Rahmen gespeichert, was hardwareseitig unterstützt wird.
Eine Gleitkommazahl mit einfacher Genauigkeit ist auf bis zu sieben Dezimalstellen genau. Sofern angegeben, verbietet UNSIGNED negative Werte.
{DOUBLE PRECISION| DOUBLE | REAL}[(M,D)] [UNSIGNED]
Gleitkommazahl normaler Größe (mit doppelter Genauigkeit). Darstellbar sind Werte aus der
Menge [−1, 80 · 10308 ; −2, 23 · 10−308 ] ∪ {0} ∪ [2, 23 · 10−308 ; 1, 80 · 10308 ]. M ist die Gesamtzahl von
Dezimalstellen, D die Anzahl der Stellen hinter dem Dezimalpunkt. Wenn M und D nicht angegeben werden, werden die Werte im Rahmen dessen gespeichert, was hardwareseitig unterstützt
wird. Eine Gleitkommazahl mit einfacher Genauigkeit ist auf bis zu 15 Dezimalstellen genau.
Sofern angegeben, verbietet UNSIGNED negative Werte.
4.5.2 Zeitbezogene Datentypen
Zur Darstellung zeitbezogener Daten dienen die Datentypen DATETIME, DATE und TIME.
DATETIME
DATETIME dient der Repräsentation von Zeitpunkten, welche sowohl das Datum als auch die Uhrzeit umfasst. Der unterstützte Bereich liegt zwischen 1000-01-01 00:00:00 und 9999-12-31
23:59:59. Die Werte können u.a. in einem der folgenden Formate angegeben werden:
• als String im Format ’YYYY-MM-DD HH:MM:SS’
• als String im Format ’YYYY-MM-DD’; für die Uhrzeit wird 00:00:00 angenommen
• als Zahl in den Formaten YYYYMMDDHHMMSS oder YYMMDDHHMMSS
• als Zahl in den Formaten YYYYMMDD oder YYMMDD; für die Uhrzeit wird 00:00:00 angenommen
• als Ergebnis einer Funktion, die einen in entsprechenden Zeitwert zurückgibt, z.B. NOW()
oder CURRENT DATE.
DATE
Dient der Repräsentation von Zeitpunkten, die nur durch ein Datum beschrieben werden. Der
unterstützte Bereich liegt zwischen 1000-01-01 und 9999-12-31. Die Werte können u.a. in
einem der folgenden Formate angegeben werden:
• als String im Format ’YYYY-MM-DD’
• als Zahl in den Formaten YYYYMMDD oder YYMMDD
• als Ergebnis einer Funktion, die einen in entsprechenden Zeitwert zurückgibt, z.B. NOW()
oder CURRENT DATE.
TIME
Dient der Repräsentation von Zeitwerten, die entweder einen Zeitpunkt oder einen Zeitintervall
in der Form HH:MM:SS beschreiben. Unterstützt wird der Bereich zwischen -838:59:59 und
838:59:59. Die Werte können u.a. in einem der folgenden Formate angegeben werden:
• als String im Format ’D HH:MM:SS’, ’HH:MM:SS’, ’HH:MM’, ’D HH:MM’, ’D HH’ oder ’SS’.
Dabei steht D für Tage und kann einen Wert zwischen 0 und 34 haben
56
4 Structured Query Language
• als Zahl im Format HHMMSS
• als Ergebnis einer Funktion, die einen Zeitwert zurückgibt, z.B. CURRENT TIME.
4.5.3 Zeichenkettenbezogene Datentypen
Zeichenketten (Strings) sind Folgen von Zeichen, die grundsätzlich mit einem bestimmten Zeichensatz5 kodiert sind. Es stehen folgende Datentypen zur Verfügung:
CHAR[(M)]
Die Länge einer CHAR-Spalte ist auf den beim Anlegen der Tabelle deklarierten Wert M beschränkt. Dieser kann zwischen 0 und 255 liegen. Wenn CHAR-Werte gespeichert werden, werden
sie nach rechts mit Leerzeichen bis auf die angegebene Länge aufgefüllt. Beim Abrufen von
CHAR-Werten werden die am Ende stehenden Leerzeichen entfernt. Wird kein M angegeben, wird
standardmäßig die Länge von einem Zeichen (M=1) angenommen.
VARCHAR[(M)]
Werte in VARCHAR-Spalten sind Strings variabler Länge M. Diese kann zwischen 0 und 65.535
liegen. Im Gegensatz zu CHAR werden VARCHAR-Werte nur mit so vielen Zeichen wie erforderlich
zuzüglich 1-2 Bytes, welche die Länge angeben. Die folgende Tabelle veranschaulicht die Unterschiede zwischen den Typen CHAR und VARCHAR. Hierzu wird das jeweilige Ergebnis der
Speicherung verschiedener String-Werte in CHAR(4)- und VARCHAR(4)-Spalten angezeigt:
Wert
’’
’ab’
’abcd’
’abcdefgh’
CHAR(4)
’
’
’ab ’
’abcd’
’abcd’
Speicherbedarf
4 Byte
4 Byte
4 Byte
4 Byte
VARCHAR(4)
’’
’ab’
’abcd’
’abcd’
Speicherbedarf
1 Byte
3 Byte
5 Byte
5 Byte
TEXT
Repräsentiert lange Zeichenketten, die mit einem bestimmten Zeichensatz kodiert sind. Die
maximale Länge der Zeichenkette beträgt dabei 216 − 1. Wie viel Speicherplatz tatsächlich in
Anspruch genommen wird, hängt von dem verwendeten Zeichensatz ab. Beispielsweise ist bei
Verwendung des utf8-Unicode-Zeichensatzes darauf zu achten, dass einige Zeichen mehr als ein
Byte Speicherplatz benötigen.
BLOB
Die Abkürzung BLOB steht für Binary Large Object und dient der Speicherung langer Zeichenketten, die keinen Zeichensatz zugewiesen haben. In diesem Fall spricht man von binären Strings
(Byte-Strings), da ein Zeichen einem Byte gleichgestellt wird. Die Sortierung basiert auf den
numerischen Werten der Bytes in den Spaltenwerten. In manchen Fällen kann es wünschenswert
sein, Binärdaten – wie bspw. Mediendateien – in BLOB-Spalten zu speichern.
ENUM(’value1’ [,’value2’] ...)
ENUM6 (Enumeration) ist ein Datentyp, der nur solche String-Werte erlaubt, die beim Erstellen der Tabelle explizit in der Spaltendefinition aufgelistet wurden. Als Werte kommen unter
bestimmten Umständen auch der Leer-String (’’) oder Null in Frage. Jeder Wert in der Auflistung bekommt einen mit 1 beginnenden nummerierten Index. Der Indexwert des als Fehlerwert
verwendeten Leer-Strings ’’ ist 0. Es kann also bspw. folgende SELECT-Anweisung verwendet
werden, um Datensätze zu ermitteln, bei denen ungültige ENUM-Werte zugewiesen wurden:
5
6
Ein Zeichensatz ist eine Zuordnung zwischen alphanumerischen Zeichen und Zahlen.
Der Datentyp ENUM ist MySQL-spezifisch und ist nicht Bestandteil des ISO-Standards.
57
4 Structured Query Language
SELECT ∗ FROM tbl_name WHERE enum_col = 0 ;
Eine Spalte, die als ENUM(’ja’, ’nein’, ’vielleicht’) definiert ist, kann jeden der nachfolgend angegebenen Werte annehmen. Auch die Indizes der einzelnen Werte werden in der Tabelle
angezeigt:
Wert
Null
’’
’ja’
’nein’
’vielleicht’
Index
Null
0
1
2
3
Eine Auflistung der erlaubten Werte darf maximal 65.535 Elemente enthalten.
4.6 Erstellen von Tabellen (CREATE TABLE)
Relationen werden in einer relationalen Datenbank in Tabellen gespeichert. Um eine Tabelle zu
erstellen und ihre Struktur zu definieren, wird der Befehl CREATE TABLE mit folgender Syntax
verwendet:
CREATE TABLE tbl_name
( create_definition , . . . )
−−c r e a t e d e f i n i t i o n :
col_name data_type [ NOT NULL | NULL ] [ DEFAULT default_value ]
[ AUTO_INCREMENT ] [ UNIQUE | PRIMARY KEY ]
| PRIMARY KEY ( col_name , . . . )
| UNIQUE ( col_name , . . . )
| FOREIGN KEY ( col_name , . . . ) REFERENCES tbl_name ( col_name , . . . )
[ ON DELETE reference_option ]
[ ON UPDATE reference_option ]
−−r e f e r e n c e o p t i o n :
CASCADE | SET NULL | NO ACTION
Nach dem Schlüsselwort CREATE TABLE folgt der Bezeichner für die neue Tabelle und (in Klammern gesetzt) eine Liste von Spalten, gefolgt von zusätzlichen optionalen Definitionen von
Primär- oder Fremdschlüsseln sowie UNIQUE-Indizes. Ein UNIQUE-Index verbietet wiederholende
Werte innerhalb einer Spalte oder Spalten.
Beispiel 1
CREATE TABLE Kategorie (
KategorieNr INT AUTO_INCREMENT PRIMARY KEY ,
Kategoriename VARCHAR ( 2 0 ) NOT NULL UNIQUE ,
Beschreibung TEXT ,
Abbildung BLOB
);
In diesem Beispiel wird eine Tabelle mit der Bezeichnung Kategorie erstellt. Die neue Tabelle hat vier Spalten. Die Spalte KategorieNr ist vom Typ INT und wird zum Primärschlüssel
58
4 Structured Query Language
deklariert. Der Zusatz AUTO INCREMENT7 schaltet eine zusätzliche Funktionalität ein, die beim
Einfügen neuer Datensätze dafür sorgt, dass die Kategorienummer automatisch fortlaufend nummeriert wird. Der Kategoriename ist ein String mit variabler Länge, jedoch nicht größer als
20 Zeichen. Der Zusatz NOT NULL bewirkt, dass das Feld erforderlich ist, d.h. es dürfen keine
Null-Werte eingetragen werden. Auf dieser Spalte wird auch ein UNIQUE-Index deklariert, der
wiederholende Werte verbietet. Die Beschreibung ist eine große nicht-binäre Zeichenkette. In
der Spalte Abbildung werden dagegen digitale Grafiken der Artikel in Form binärer Zeichenketten (Byte-Strings) gespeichert.8 Die letzten beiden Felder sind optional (da kein NOT NULL
angegeben wurde).
Beispiel 2
CREATE TABLE Artikel (
ArtikelNr INT NOT NULL AUTO_INCREMENT ,
Artikelname VARCHAR ( 4 0 ) DEFAULT NULL ,
KategorieNr INT NOT NULL ,
Einzelpreis DECIMAL ( 1 9 , 4 ) DEFAULT NULL ,
Lagerbestand SMALLINT DEFAULT NULL ,
PRIMARY KEY ( ArtikelNr ) ,
FOREIGN KEY ( KategorieNr ) REFERENCES Kategorie ( KategorieNr )
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
In Beispiel 2 werden mit dem Zusatz DEFAULT Standardwerte für bestimmte Spalten definiert.
Sie werden beim Einfügen neuer Datensätze dann angewendet, wenn kein expliziter Wert für
diese Spalte angegeben wird.
Zur Primärschlüssel-Definition wird hier eine andere Syntax als zuvor verwendet. Die Definition
geschieht gesondert nach der Angabe aller Spalten mit dem Schlüsselwort PRIMARY KEY, gefolgt
von einer Liste der Schlüsselspalten in Klammern. Diese Syntaxvariante muss verwendet werden,
wenn der Primärschlüssel aus mehr als einer Spalte besteht.
Nach der Definition des Primärschlüssels wird ein Fremdschlüssel definiert, was für sogenannte
referentielle Integrität sorgt. In diesem Beispiel referenziert die Spalte KategorieNr die gleichnamige Spalte aus der Elterntabelle Kategorie. Es besteht noch zusätzlich die Möglichkeit,
bestimmte Regeln zu definieren, die das Systemverhalten im Hinblick auf die FremdschlüsselBeziehung steuern. Es kann angegeben werden, was bei einem Versuch passieren soll, einen
referenzierten Wert aus der Elterntabelle zu löschen (ON DELETE) oder einen solchen Wert zu
ändern (ON UPDATE). Dabei gibt es grundsätzlich drei Möglichkeiten:
7
8
AUTO INCREMENT ist eine MySQL-Erweiterung zum Standard-SQL.
Zu beachten ist, dass das Speichern von Grafikdaten in der Datenbank nicht unbedingt sinnvoll sein muss. In
der Regel werden in Datenbanken lediglich Referenzen auf die Dateien im Dateisystem gespeichert, um die
Größe der Datenbank im Rahmen zu halten.
59
4 Structured Query Language
Option
NO ACTION (Voreinstellung)
CASCADE
SET NULL
Wirkung
Ändern/Löschen referenzierter Datensätze in der Elterntabelle
nicht möglich.
Änderungen in der referenzierten Tabelle werden in dem referenzierenden Datensatz automatisch übernommen. Wird ein referenzierter Datensatz in der Elterntabelle gelöscht, so werden alle ihn
referenzierenden Datensätze aus der Tabelle automatisch gelöscht.
Wird ein referenzierter Datensatz in der Elterntabelle gelöscht oder
geändert, so werden alle ihn referenzierende Werte aus der Tabelle
mit Null-Werten ersetzt. Diese Option ergibt nur dann Sinn, wenn
die Spaltendefinition Null-Werte zulässt.
Wenn keine Option zur referentiellen Integrität angegeben wird, wird standardmäßig NO ACTION angewendet.
4.7 Ändern der Tabellenstruktur (ALTER TABLE)
Wenn die Struktur einer bereits angelegten Tabelle geändert werden soll, wird dazu der Befehl
ALTER TABLE mit folgender Syntax verwendet:
ALTER TABLE tbl_name
alter_specification [ , alter_specification ]
...
−− a l t e r s p e c i f i c a t i o n :
ADD [ COLUMN ] column_definition [ FIRST | AFTER col_name ]
| ADD PRIMARY KEY ( col_name , . . . )
| ADD UNIQUE ( col_name , . . . )
| ADD FOREIGN KEY ( col_name , . . . ) REFERENCES tbl_name ( col_name , . . . )
[ ON DELETE reference_option ]
[ ON UPDATE reference_option ]
| CHANGE [ COLUMN ] old_col_name column_definition
[ FIRST | AFTER col_name ]
| DROP [ COLUMN ] col_name
| DROP PRIMARY KEY
| DROP FOREIGN KEY fk_symbol
| DROP INDEX index_name
| RENAME TO new_tbl_name
Nach dem Schlüsselwort ALTER TABLE und dem Tabellennamen folgt eine Liste von durch
Kommata getrennten Änderungsanweisungen.
Beispiel
ALTER TABLE Artikel
ADD COLUMN Artikelbeschreibung TEXT AFTER Artikelname ,
CHANGE COLUMN Lagerbestand Lagerbestand SMALLINT NOT NULL ,
DROP COLUMN Einzelpreis ;
Im Beispiel wird die Struktur der Tabelle Artikel geändert, indem eine neue Spalte Artikelbeschreibung des Typs TEXT hinzugefügt wird. Mit der Option FIRST bzw. AFTER col name
hat man die Möglichkeit, die Position der neuen Spalte in der Tabelle anzugeben. Mit CHANGE
60
4 Structured Query Language
COLUMN werden bestehende Spaltendefinitionen geändert. Hierbei ist anzumerken, dass direkt
nach dem Namen der zu ändernden Spalte der neue Spaltenname und weitere Bestandteile
einer vollständigen Spaltendefinition folgen. Wird der Spaltenname nicht geändert, so muss er
zweimal hintereinander angegeben werden. Die Anweisung DROP COLUMN ermöglicht das Löschen
von Spalten.
Des Weiteren besteht die Möglichkeit Primär-, Fremdschlüssel und UNIQUE-Indices zu definieren
sowie diese zu löschen. In den zwei letzteren Fällen muss der Name der entsprechenden Bedingung
angegeben werden, der mit dem Befehl SHOW CREATE TABLE tbl name herauszufinden ist. Mit
der Anweisung RENAME TO besteht die Möglichkeit, eine Tabelle umzubenennen.
4.8 Entfernen von Tabellen (DROP TABLE)
Das Schema einer Relation und alle bereits eingegebenen Daten können mit dem DROP TABLEBefehl gelöscht werden. Somit wird eine Tabelle aus der Datenbank vollständig und endgültig
entfernt:
DROP TABLE tbl_name
Beispiel
DROP TABLE Kunde ;
Im Beispiel wird die Tabelle Kunde vollständig aus dem System entfernt. Dabei gehen alle darin
enthaltenen Daten verloren!
4.9 Einfügen von Daten (INSERT)
Das Einfügen von Datensätzen in eine Tabelle geschieht unter Verwendung des INSERT-Befehls.
Der Vorgang kann entweder durch direkte Angabe der einzufügenden Datensätze oder durch
Einfügen von Datensätzen aus einer anderen Tabelle geschehen.
4.9.1 Direktes Einfügen
Syntax
INSERT INTO tbl_name [ ( col_name , . . . ) ]
VALUES ( { expr | DEFAULT } , . . . ) , ( . . . ) , . . .
Werden Spaltennamen (col name,...) angegeben, so werden Werte nur in die entsprechenden
Spalten der Tabelle eingefügt. Dies ist nur möglich, wenn die nicht angegebenen Spalten einen
Standardwert haben. Der Standardwert kann entweder explizit durch die DEFAULT-Option
angegeben werden oder es wird bei Spalten, die Null-Werte zulassen, Null als Standardwert
angenommen.
Werden keine Spaltennamen angegeben, so bezieht sich das INSERT auf alle Spalten der Tabelle.
Soll in eine Spalte, die nicht als NOT NULL definiert wurde, ein Null-Wert eingefügt werden,
so geschieht das durch Eingabe des Wertes Null. Wenn mehrere Datensätze mit einem Befehl
hinzugefügt werden sollen, so können die einzelnen VALUES-Gruppen durch Kommata getrennt
angegeben werden.
61
4 Structured Query Language
4.9.2 Einfügen aus anderen Tabellen
Syntax
INSERT INTO tbl_name [ ( col_name , . . . ) ]
SELECT . . .
Mit dieser Syntaxvariante besteht die Möglichkeit, sich die hinzufügenden Werte von einer beliebigen SELECT-Anfrage liefern zu lassen. Natürlich müssen dabei die Spalten des Abfrageergebnisses den angegebenen (oder allen - wenn kein (col name,...) vorhanden) Spalten hinsichtlich der
Tabellendefinition entsprechen. D.h. die Tabelle muss das SELECT-Ergebnis aufnehmen können.
4.10 Abfragen (SELECT)
Um die in den Relationen gespeicherten Daten abzurufen, werden an das DBMS Abfragen (Queries) gestellt. Nach der Bearbeitung der Abfrage, liefert das System ein Ergebnis zurück. Das Ergebnis hat die Form einer Tabelle, d.h. es besteht aus benannten Spalten und in Zeilen organisierten Datensätzen. Jeder Spalte ist dabei auch ein bestimmter Typ zugewiesen. Die Durchführung
der Abfragen ermöglicht der SELECT-Befehl.
Syntax
SELECT
[ ALL | DISTINCT ]
select_expr , . . .
[ FROM table_references
[ WHERE where_condition ]
[ GROUP BY { col_name | expr } , . . . ]
[ HAVING where_condition ]
[ ORDER BY { col_name | expr } , . . . ]
..
4.10.1 Einfache Abfragen
Die Grundform einer Abfrage in SQL wird durch die ’SELECT...FROM...WHERE’-Klausel gebildet. Hinter dem Schlüsselwort SELECT werden die Ergebnisspalten spezifiziert, die ausgegeben
werden sollen (Projektion). Hinter dem Schlüsselwort FROM müssen die Namen aller Tabellen
angegeben werden, deren Spalten ausgegeben werden sollen oder zur Formulierung der Bedingungen benötigt werden. Auf das Schlüsselwort WHERE folgend können Bedingungen angegeben
werden, denen die Elemente der beteiligten Relationen genügen müssen, um Bestandteil der
Lösungsmenge zu werden (Selektion). Da es möglich ist, dass nach Projektion und Selektion in
der Lösungsmenge gleiche Datensätze mehrmals vorkommen, bietet SQL die Möglichkeit, durch
die Angabe von DISTINCT hinter SELECT nur verschiedene Datensätze anzuzeigen. Wird ALL
(oder nichts) anstatt DISTINCT angegeben, so bleibt die Lösungsmenge unverändert.
Beispiel 1
SELECT Artikelname , Einzelpreis
FROM Artikel
WHERE ArtikelNr =1234;
62
4 Structured Query Language
In Beispiel 1 werden solche Datensätze in der Tabelle Artikel gesucht, die den Wert 1234 in der
Spalte ArtikelNr haben. Da in diesem Fall ArtikelNr Primärschlüssel ist, dürfen sich dessen
Werte nicht wiederholen, es wird also nach genau einem Datensatz gesucht. Dabei wird hier nicht
der vollständige Datensatz zurückgeliefert, sondern nur die Werte der Spalten Artikelname und
Einzelpreis.
Wird als select expr, ... ein * (Sternchen) angegeben, so werden alle verfügbaren Spalten des
Ergebnisses zurückgeliefert. Sollen alle Datensätze (Zeilen) einer Tabelle ohne Einschränkung
ausgegeben werden, kann auf die WHERE-Klausel verzichtet werden.9 In Beispiel 2 wird eine
Abfrage gezeigt, die uneingeschränkt alle Daten aus der Tabelle Artikel zurück gibt.
Beispiel 2
SELECT ∗
FROM Artikel ;
select expr kann auch ein Ausdruck sein, der Tabellenspalten verwendet oder sogar ein solcher,
der ohne Referenzierung einer Tabelle berechnet wird. Es kann sich als praktisch erweisen, dem
Ausdruck einen Namen (sogenannten Alias) zu geben. Diese Möglichkeiten werden in Beispiel 3
gezeigt.
Beispiel 3
SELECT Einzelpreis ∗2 AS ‘ Doppelter Preis ‘ , 2∗2+3 AS Berechnung
FROM Artikel ;
Auf das Schlüsselwort AS kann verzichtet werden. Wenn nur Tabellen-unabhängige Ausdrücke
berechnet werden, kann die FROM-Klausel ebenfalls weggelassen werden.
Beispiel 4
SELECT 1 Eins ;
Das Ergebnis der Abfrage aus dem Beispiel 4 hat eine Spalte mit dem Namen Eins und einen
Datensatz. Es wird einfach die Zahl 1 ausgegeben.
4.10.2 Formulierung von Bedingungen (WHERE)
Hinter dem Schlüsselwort WHERE können Bedingungen in Form von Ausdrücken angegeben werden. Diese bestimmen die vorzunehmende Selektion und können unterschiedlicher Art sein. Zum
einen sind einfache Vergleiche möglich, zum anderen besteht die Möglichkeit, neue Abfragen (sogenannte Unterabfragen oder Subqueries) in die Bedingungen zu integrieren. Solche Ausdrücke
sind auch an anderen Stellen zulässig, wie z.B. hinter dem SELECT-Schlüsselwort oder (in beschränkter Form) in der HAVING-Klausel.
In den folgenden Beispielen wird von der Relation Kunde mit den Attributen KundenCode, Firma
und PLZ (Postleitzahl des Kundenwohnorts) ausgegangen. Für die Formulierung von Bedingungen gibt es in SQL unter anderem folgende Möglichkeiten:
• einfacher Vergleich (=, <, >, <>, <=, >=)
– WHERE Firma=’BAKER AG ’
– WHERE PLZ<>’48161 ’
9
Es kann auch eine Bedingung gesetzt werden, die immer erfühlt ist, wie z.B. WHERE 1=1. Dies kann z.B. bei
dynamischer SQL-Generierung praktisch sein.
63
4 Structured Query Language
• Verknüpfung von Bedingungen mit AND, OR oder NOT
– WHERE ( PLZ=’48161 ’ OR PLZ=’48149 ’ ) AND Firma<>’ERCIS ’
• der BETWEEN-Operator zur Definition eines Suchbereiches
– WHERE Firma BETWEEN ’BAKER AG ’ AND ’ERCIS ’}
Statt mit BETWEEN zu arbeiten kann auch die Ober- und Untergrenze des Bereichs separat
überprüft werden:
– WHERE Firma>=’BAKER AG ’ AND Firma<=’ERCIS ’
• der LIKE-Operator
– WHERE Firma LIKE ’B_ker ’}
Mit dem LIKE-Operator kann eine Ähnlichkeitsabfrage für alphanumerische Konstanten
durchgeführt werden. Als Wildcards dienen der Unterstrich ( ) als Platzhalter für ein
Zeichen und das Prozentzeichen (%) als Platzhalter für n Zeichen (n >= 0).
Beispiel
SELECT ∗ FROM Kunde WHERE Firma LIKE ’M%’ ;
Es werden alle Kunden ausgegeben, deren Namen mit ’M’ beginnen.
• die Operatoren IS NULL und IS NOT NULL
– WHERE Firma IS NULL
– WHERE PLZ IS NOT NULL
• der IN-Operator
– WHERE PLZ IN ( ’48149 ’ , ’48161 ’ , ’48143 ’ )
Es werden solche Datensätze in die Ergebnismenge übernommen, die einem der Einträge in der Liste entsprechen.
– Als Liste für den IN-Operator kann auch eine SELECT-Query dienen:
WHERE PLZ IN
( SELECT PLZ FROM Postleitzahlen WHERE Bundesland=’NRW ’ )
• der EXISTS-Operator in Verbindung mit einer Unterabfrage
– WHERE EXISTS ( SELECT . . . FROM . . . WHERE . . . )
Diese Bedingung prüft, ob es für einen Datensatz ein Ergebnis in der Subquery gibt.
• verschiedene String-Funktionen, z.B. CONCAT()
– SELECT CONCAT ( ’Kundennummer : ’ , KundenCode , ’, Firma : ’ , Firma )
AS ‘ Kundencode und Firma ‘
FROM Kunde ;
CONCAT(str1,str2,...) gibt den String zurück, der aus der Verkettung der Argumente
entsteht.
• verschiedene mathematische Funktionen, z.B. ROUND()
64
4 Structured Query Language
– SELECT Artikelname , ROUND ( Einzelpreis )
FROM Artikel
WHERE ROUND ( Einzelpreis ) >20;
ROUND(X) gibt das Argument X gerundet auf den nächstgelegenen Integer zurück.
• verschiedene Datumsfunktionen, z.B. YEAR()
– SELECT BestellNr , YEAR ( Bestelldatum )
FROM Bestellung
WHERE YEAR ( Bestelldatum )=2001;
YEAR(date) gibt für ein Datum im Bereich zwischen 1000 und 9999 das Jahr als Zahl
zurück.
4.10.3 Sortieren (ORDER BY)
Wird eine Abfrage durch eine ORDER BY-Klausel abgeschlossen, so bewirkt dies eine Sortierung
der Lösungsmenge anhand der Werte einer oder mehrerer vorgegebener Spalten. Für jeden Spaltennamen hinter ORDER BY kann angegeben werden, ob anhand dieser Spalte aufsteigend (ASC)
oder absteigend (DESC) sortiert werden soll. Wird weder ASC noch DESC angegeben, so wird automatisch ASC, also aufsteigende Sortierung, angenommen. In dieser Klausel ist es möglich, die
in der SELECT-Klausel definierten Aliase zu verwenden.
Beispiel 6
SELECT Firma , PLZ
FROM Kunde
ORDER BY PLZ DESC , Firma ;
In Beispiel 6 wird eine Kundenliste nach Postleitzahlen absteigend geordnet ausgegeben. Wohnen
mehrere Kunden im gleichen Ort, werden sie namentlich aufsteigend geordnet ausgegeben.
4.10.4 JOIN-Syntax
Da sich Informationen in stark normalisierten Datenbanken auf verschiedene Tabellen verteilen,
ist es bei der Abfragen meist notwendig, diese wieder zu verknüpfen. Das wird durch einen
Verbund (Join) erreicht, der die Tabellen temporär (d.h. für die Dauer der Anfrage) verbindet.
Das Ergebnis eines Joins ist wie eine neue vollständige Tabelle anzusehen.
Als Beispiel sind folgende zwei Tabellen gegeben:
Tabelle Servicepunkt
SPID
VERTRID
---------- ---------1
1
2
1
3
2
4
NULL
BEZEICH
------Punkt 1
Punkt 2
Punkt 3
Punkt 4
Tabelle Vertriebsregion
VERTRID SUPERVERTRID NAME
---------- ------------ ----------
65
4 Structured Query Language
1
2
3
NULL Region 1
1 Region 2
1 Region 3
Wenn mehrere Tabellen verknüpft werden, kann es vorkommen, dass sich gleichnamige Spalten
in verschiedenen Tabellen befinden. Werden solche Spalten in einem Befehl referenziert, müssen
qualifizierte Namen der Form tbl name.col name benutzt werden, um Eindeutigkeit zu gewährleisten. In solchen Fällen ist es oft nützlich, den Tabellen (kürzere) Aliasnamen zu vergeben. Dies
geschieht durch Angabe des Aliases mit dem optionalen Wort AS hinter dem Tabellennamen in
der FROM-Klausel, wie im folgenden Beispiel.
. . . FROM servicepunkt AS alias_s , vertriebsregion alias_v
CROSS JOIN
Ein Cross Join bildet das kartesische Produkt (=Kreuzprodukt) zweier Tabellen. Es wird jede
Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle kombiniert. Eine praktische Anwendung gibt es dafür jedoch eher selten.
Es ist folgende Syntax zugelassen:
SELECT ∗ FROM servicepunkt s CROSS JOIN vertriebsregion v ;
#oder :
SELECT ∗ FROM servicepunkt s , vertriebsregion v ;
Ausgabe:
SPID
VERTRID BEZEICH
VERTRID SUPERVERTRID NAME
---------- ---------- ------- ---------- ------------ -------1
1 Punkt 1
1
NULL Region 1
1
1 Punkt 1
2
1 Region 2
1
1 Punkt 1
3
1 Region 3
2
1 Punkt 2
1
NULL Region 1
2
1 Punkt 2
2
1 Region 2
2
1 Punkt 2
3
1 Region 3
3
2 Punkt 3
1
NULL Region 1
3
2 Punkt 3
2
1 Region 2
3
2 Punkt 3
3
1 Region 3
4
NULL Punkt 4
1
NULL Region 1
4
NULL Punkt 4
2
1 Region 2
4
NULL Punkt 4
3
1 Region 3
INNER JOIN
Ein Inner Join verbindet genau die Zeilen von zwei Tabellen miteinander, für die eine explizit
angegebene Bedingung erfüllt wird. Sollen mehrere Bedingungen gleichzeitig angewendet werden,
werden diese mittels logischer Operatoren (AND, OR) verknüpft.
Folgende Syntax ist zulässig:
SELECT ∗
FROM servicepunkt s INNER JOIN vertriebsregion v
ON s . VERTRID=v . VERTRID ;
66
4 Structured Query Language
Die Bedingung im Teil hinter ON muss keine Äquivalenz sein - auch bspw. “größer als“oder
“kleiner als“sind als Bedingung zulässig.
Alternativ kann der Join über die WHERE-Klausel erfolgen:
SELECT ∗
FROM servicepunkt s , vertriebsregion v
WHERE s . VERTRID=v . VERTRID ;
Ausgabe:
SPID
VERTRID BEZEICH
VERTRID SUPERVERTRID NAME
---------- ---------- ------- ---------- ------------ -------1
1 Punkt 1
1
NULL Region 1
2
1 Punkt 2
1
NULL Region 1
3
2 Punkt 3
2
1 Region 2
Wenn als Bedingung die Äquivalenz bzgl. eines oder mehrerer Attribute definiert werden soll
(also bspw. “’kunde.kundenID=bestellung.kundenID’) und die entsprechenden Spalten in beiden Tabellen den gleichen Namen und Datentyp besitzen, kann auch folgende Syntax verwendet
werden:
SELECT ∗
FROM servicepunkt s INNER JOIN vertriebsregion v
USING ( VERTRID ) ;
mit der Ausgabe:
VERTRID
SPID BEZEICH SUPERVERTRID NAME
---------- ---------- ------- ------------ -------1
1 Punkt 1
NULL Region 1
1
2 Punkt 2
NULL Region 1
2
3 Punkt 3
1 Region 2
Wie zu sehen ist, besteht der Unterschied darin, dass die (gleichnamigen) Spalten nicht doppelt
zurückgegeben werden.
NATURAL JOIN
Wenn die JOIN-Bedingungen Äquivalenzen sind und die entsprechenden Spalten in beiden Tabellen den gleichen Namen und Datentyp besitzen und es keine anderen Spalten (also solche,
die nicht Bestandteil der Bedingung des Joins sein sollen) gibt, die in beiden Tabellen die den
gleichen Namen und Datentyp besitzen, so kann der obige INNER JOIN mit USING durch einen
NATURAL JOIN ersetzt werden:
SELECT ∗
FROM servicepunkt s NATURAL JOIN vertriebsregion v ;
ergibt
VERTRID
SPID BEZEICH SUPERVERTRID NAME
---------- ---------- ------- ------------ -------1
1 Punkt 1
NULL Region 1
1
2 Punkt 2
NULL Region 1
2
3 Punkt 3
1 Region 2
67
4 Structured Query Language
LEFT OUTER JOIN = LEFT JOIN
Außer INNER JOINs gibt es auch so genannte OUTER JOINs. Ein LEFT (OUTER) JOIN
kombiniert jede Zeile der ersten Tabelle mit den Zeilen der zweiten Tabelle, die die Bedingungen
erfüllen oder mit Null-Werten, wenn keine passenden Zeilen der zweiten Tabelle vorhanden
sind.
So gibt es im Beispiel eine Vertriebsregion, in der sich keine Servicepunkte befinden. Wird eine
Liste aller Regionen benötigt, auf der zusätzlich entsprechende Servicepunkte annotiert sind, so
ist folgende Anfrage hilfreich:
SELECT ∗
FROM vertriebsregion v LEFT JOIN servicepunkt s
ON v . VERTRID=s . VERTRID ;
Ausgabe:
VERTRID SUPERVERTRID NAME
SPID
VERTRID BEZEICH
---------- ------------ -------- ---------- ---------- ------1
NULL Region 1
1
1 Punkt 1
1
NULL Region 1
2
1 Punkt 2
2
1 Region 2
3
2 Punkt 3
3
1 Region 3
NULL
NULL
NULL
Sind nur die Regionen zu ermitteln, in der sich keine Servicepunkte befinden, liefert folgende
Anfrage das gewünschte Ergebnis:
SELECT ∗
FROM vertriebsregion v LEFT JOIN servicepunkt s
ON v . VERTRID=s . VERTRID
WHERE s . VERTRID IS NULL ;
Ausgabe:
VERTRID SUPERVERTRID NAME
SPID
VERTRID BEZEICH
---------- ------------ -------- ---------- ---------- ------3
1 Region 3
NULL
NULL
NULL
RIGHT OUTER JOIN = RIGHT JOIN
Ein RIGHT (OUTER) JOIN funktioniert genauso wie LEFT (OUTER) JOIN, nur werden hier
alle Zeilen der zweiten (rechten) Tabelle mit passenden Zeilen der ersten (linken) Tabelle oder
Null-Werten kombiniert.
FULL OUTER JOIN = FULL JOIN10
Ein vollständiger Außenverbund kombiniert die Funktionsweise der beiden LEFT und RIGHT
JOINs. Es werden die Zeilen der linken Tabelle mit denen der rechten verknüpft, die die angegebenen Bedingungen erfüllen. Außerdem werden die verbleibenden Zeilen sowohl der linken als
auch der rechten mit Null-Werten verknüpft. Folgende Anfrage erstellt uns eine Liste von allen
Vertriebsregionen und allen Servicepunkten mit ihrer Zuordnung, sofern vorhanden.
10
FULL (OUTER) JOIN wird von MySQL nicht unterstützt.
68
4 Structured Query Language
SELECT ∗
FROM vertriebsregion v FULL JOIN servicepunkt s
ON v . VERTRID=s . VERTRID ;
Ausgabe:
VERTRID SUPERVERTRID NAME
SPID
VERTRID BEZEICH
---------- ------------ -------- ---------- ---------- ------1
Region 1
1
1 Punkt 1
1
Region 1
2
1 Punkt 2
2
1 Region 2
3
2 Punkt 3
3
1 Region 3
NULL
NULL
NULL
NULL
NULL
NULL
4
NULL Punkt 4
JOINS von mehreren Tabellen
Wenn ein Verbund von mehr als zwei Tabellen gebildet wird, werden die Tabellen normalerweise
von links nach rechts verknüpft. Da dies bei Outer Joins eine Rolle spielen kann, kann man diese
Reihenfolge durch Setzen von Klammern verändern.
Beim Ausführen von komplexeren Anfragen sorgt das DBMS (der Optimierer) dafür, dass der
Vorgang möglichst kurz dauert. Es werden beispielsweise zuerst die Bedingungen betrachtet, die
die Menge der Zeilen aus einer (oder aus mehreren) Tabelle(n) am meisten begrenzen. Dies hat
zur Folge, dass später wesentlich weniger Zeilen in der Join-Phase verbunden werden.
Eine Ausnahme von dieser Vorgehensweise bilden hier z.B. die Bedingungen, die sich im HAVINGTeil befinden. Diese werden erst nach der Verbunderstellung und Gruppenbildung angewendet.
Aus diesem Grund sollten nur solche Ausdrücke in den HAVING-Teil gesetzt werden, die nicht
innerhalb der WHERE-Klausel formuliert werden können, da sonst Performance-Nachteile entstehen.
4.10.5 Aggregation von Daten
In manchen Situationen sind nicht die Werte der einzelnen Datensätze von Interesse, sondern
solche, die eine Gruppe von Datensätzen zusammenfassend beschreiben. Um sie zu ermitteln,
gibt es in SQL die folgenden fünf speziellen Aggregationsfunktionen:
COUNT([DISTINCT] expr)
COUNT(*)
MIN(expr)
MAX(expr)
SUM(expr)
AVG(expr)
Ermittelt die Anzahl der gültigen Werte innerhalb einer
Gruppe. Wird die Option DISTINCT verwendet, werden
gleiche Werte nur einmal gezählt.
Ermittelt die Anzahl der Datensätze im Ergebnis.
Ermittelt den kleinsten Wert einer Gruppe von Werten.
Ermittelt den größten Wert einer Gruppe von Werten.
Ermittelt die Summe der Werte einer Gruppe von Werten.
Ermittelt das arithmetische Mittel einer Gruppe von Werten.
Grundsätzlich gilt die Regel, dass Null-Werte durch die Aggregationsfunktionen ignoriert werden. Eine Ausnahme ist COUNT(*), das alle Datensätze in der Gruppe – unabhängig der darin
enthaltenen Werte – zählt.
Beispiel 7
69
4 Structured Query Language
SELECT MAX ( Einzelpreis )
FROM Artikel ;
In Beispiel 7 wird der höchste Preis aller Artikel gesucht.
Beispiel 8
SELECT COUNT ( DISTINCT PLZ )
FROM Kunde ;
In Beispiel 8 wird die Anzahl unterschiedlicher Postleitzahlen aller Kunden gesucht.
4.10.6 Gruppenbildung (GROUP BY)
Mit der GROUP BY-Klausel können die Zeilen eines (Zwischen-)Ergebnisses anhand der Werte
einer oder mehrerer Spalten gruppiert werden. Die Gruppierung erfolgt so, dass die Spalten,
nach denen die Gruppierung erfolgt, keine doppelten Werte mehr enthalten.
Hinter GROUP BY erfolgt die Angabe eines oder mehrerer Spaltennamen. Wenn Gruppierung
angewendet wird, dürfen hinter SELECT nur solche Spalten stehen, über die gruppiert wird, oder
Ausdrücke, die genau einen Wert pro Gruppe liefern (s. Aggregationsfunktionen). Der Sinn hinter
der Gruppenbildung liegt in der Anwendung von Aggregationsfunktionen, die nach Ausführung
genau einen Wert pro Gruppe liefern.
Beispiel 9
SELECT Land , COUNT ( ∗ )
FROM Kunde
GROUP BY Land ;
In Beispiel 9 wird eine Liste von Ländern, in denen Kunden angesiedelt sind, mit der Anzahl
von Kunden in dem jeweiligen Land ausgegeben.
4.10.7 Gruppenbedingungen (HAVING)
Durch die HAVING-Komponente erfolgt nach der Bildung der Gruppen mittels GROUP BY eine Auswahl der Gruppen, die den durch die HAVING-Bedingung gegebenen Anforderungen genügen. Der
Unterschied zur Auswahl durch die Bedingungen hinter WHERE liegt darin, dass Tabellenzeilen,
die den Bedingungen hinter WHERE nicht genügen, bei der Gruppenbildung durch GROUP BY nicht
berücksichtigt werden, während durch die HAVING-Bedingung bereits gebildete Gruppen aus der
Lösungsmenge ausgeschlossen werden können. In der HAVING-Bedingung werden im Allgemeinen
Aggregationsfunktionen angewendet.
Beispiel 10
SELECT PLZ , COUNT ( Firma ) as KundenProPLZ
FROM Kunde
WHERE NOT ( ( Firma=’Baker AG ’ ) AND ( PLZ=’48149 ’ ) )
GROUP BY PLZ
HAVING KundenProPLZ >1;
In Beispiel 10 werden Kunden, die die gleiche Postleitzahl haben, zusammengruppiert. Pro Postleitzahl wird anschließend die Anzahl der dazugehörigen Kunden ausgegeben. Bei der Abfrage
wird der Kunde Maier aus 48149 (Münster) nicht berücksichtigt.
70
4 Structured Query Language
4.10.8 Reihenfolge
bei der
Abfragenberechnung
3.2.8
Reihenfolge
bei
der Abfragenberechnung
Die Bearbeitung von Abfragen wird konzeptionell immer in einer bestimmten Reihenfolge abge-
Die
Bearbeitung
vonauf
Abfragen
konzeptionell
immer in einer bestimmten Reihenfolge
wickelt.
Diese wird
folgenderwird
Abbildung
dargestellt:
abgewickelt. Diese wird auf folgender Abbildung dargestellt.
FROM:
WHERE:
Definiert die Ausgangstabellen
Selektiert die Reihen, die der
Bedingung genügen
GROUP BY: Gruppiert Reihen auf der Basis
gleicher Werte in Spalten
HAVING:
Selektiert Gruppen, die der
Bedingung genügen
SELECT:
Selektiert Spalten
ORDER BY: Sortiert Reihen auf der Basis
von Spalten
Zuerst werden die zu verknüpfenden Tabellen verbunden. Anschließend werden die WHEREZuerst werden
die zu verknüpfenden
Tabellen
verbunden. Anschließend
werden
die WHEREBedingungen
angewendet,
die das Ergebnis
beschränken.
Danach werden
Gruppen
gebildet,
angewendet,
die das
Ergebnis beschränken.
werden
Gruppen
gebildet, in
in Bedingungen
dem Datensätze
mit gleichen
Wertekombinationen
derDanach
GROUP BY
-Spalten
zusammengefasst
dem Datensätze
mit-Klausel
gleichen Wertekombinationen
der dass
GROUPunerwünschte
BY-Spalten zusammengefasst
werwerden.
Die HAVING
bewirkt als nächstes,
Gruppen aussortiert
den. Die
HAVING-Klausel
bewirkt
dass unerwünschte
werden.
werden.
Danach
werden nur
die als
im nächstes,
SELECT-Teil
ausgewähltenGruppen
Spalten aussortiert
aus dem Ergebnis
Danach werden nur die im SELECT-Teil ausgewählten Spalten aus dem Ergebnis ausgewählt und
ausgewählt und anschließend erfolgt eine Sortierung anhand angegebener Kriterien.
anschließend erfolgt eine Sortierung anhand angegebener Kriterien.
3.2.9
Unterabfragen
4.10.9 Unterabfragen
Innerhalb eines SELECT-Befehls können sich weitere Abfragen befinden (d. h. es werden
Innerhalb eines SELECT-Befehls können sich weitere Abfragen befinden (d. h. es werden mehrere
mehrere SELECT...FROM...WHERE-Statements ineinandergeschachtelt). Bei den inneren
SELECT...FROM...WHERE-Statements ineinandergeschachtelt). Bei den inneren Abfragen spricht
Abfragen spricht man von Unterabfragen (Subqueries). Die äußere Abfrage wird dabei als
man von Unterabfragen (Subqueries). Die äußere Abfrage wird dabei als Hauptabfrage bezeichHauptabfrage
bezeichnet.selbst
Jedekann
Unterabfrage
selbst kann
als Hauptabfrage
werden,
net. Jede Unterabfrage
als Hauptabfrage
angesehen
werden, wennangesehen
sie Unterabfragen
wenn sie Unterabfragen besitzt. Subqueries erlauben es, komplexe Abfragen strukturiert
aufzubauen und eigenständige Lösungsteile zu isolieren.
71
23
4 Structured Query Language
besitzt. Subqueries erlauben es, komplexe Abfragen strukturiert aufzubauen und eigenständige
Lösungsteile zu isolieren.
Grundsätzlich gibt es vier Arten von Unterabfragen bezüglich des Ergebnistyps:
• Eine Skalarunterabfrage liefert genau eine Spalte und genau eine Zeile, d.h. einen einzelnen Wert zurück. Grundsätzlich kann eine solche Unterabfrage an allen Stellen verwendet
werden, an denen einzelne Werte zulässig sind (Beispiele 11, 12 und 18 ).
• Eine Spaltenunterabfrage liefert genau eine Spalte, aber mehrere Zeilen zurück. Diese Unterabfragen werden vor allem dort verwendet, wo ein Vergleich mit einer Liste von Werten
durchgeführt wird, z.B. mit dem IN-Operator (Beispiel 13 ).
• Eine Zeilenunterabfrage liefert mehrere Spalten, aber genau eine Zeile zurück. Sie findet
in einfachen Vergleichen Anwendung, in denen mehrere Spalten involviert sind (zeilenbasierter Vergleich) (Beispiele 14 und 15 ).
• Eine Tabellenunterabfrage liefert mehr als eine Spalte und mehr als eine Zeile zurück. Sie
kann zum einen in einem zeilenbasierten Vergleich mit einer Liste von Zeilen (z.B. mit
dem IN-Operator) eingesetzt werden, zum anderen können solche Unterabfragen in der
FROM-Klausel an Stelle von Tabellen verwendet werden (Beispiele 16 und 17 ).
Beispiel 11
SELECT ArtikelNr , LieferantenNr , Einkaufspreis
FROM
Liefernachweis
WHERE Einkaufspreis=(SELECT MAX ( Einkaufspreis ) FROM Liefernachweis ) ;
In Beispiel 11 werden Artikelnummer, Lieferantennummer und Preis von dem am teuersten
eingekauften Artikel ausgegeben. Gibt es mehrere solche Artikel, werden alle ausgegeben. Hier
wird eine Skalarunterabfrage im WHERE-Teil angewendet. Wie zu sehen ist, werden Unterabfragen
stets in Klammern gesetzt.
Beispiel 12
SELECT ArtikelNr , Einkaufspreis −
(
SELECT AVG ( Einkaufspreis ) FROM Liefernachweis
) AS ‘ Abweichung vom Durchschnittspreis ‘
FROM Liefernachweis ;
In Beispiel 12 werden für jeden Artikel seine Nummer und die Abweichung vom Durchschnittspreis aller Artikel ausgegeben. Es ist ein Beispiel einer Skalarunterabfrage im SELECT-Teil.
Beispiel 13
SELECT ∗
FROM Artikel
WHERE ArtikelNr IN (
SELECT ArtikelNr
FROM Liefernachweis
WHERE Einkaufspreis <10);
Beispiel 13 stellt die Verwendung einer Spaltenunterabfrage mit dem IN-Operator dar. In der
Unterabfrage werden zuerst die Nummern von denjenigen Artikeln ermittelt, deren Einkaufspreis
weniger als 10 beträgt. Somit wird eine Liste von Werten gebildet. In der Hauptabfrage werden
alle Informationen zu den Artikeln ausgegeben, deren Nummern sich in der Liste befinden.
Beispiel 14
72
4 Structured Query Language
SELECT ArtikelNr FROM Artikel
WHERE ( Einzelpreis , Lagerbestand )=(
SELECT MAX ( Einzelpreis ) , MIN ( Lagerbestand )
FROM Artikel ) ;
In Beispiel 14 wird eine Zeilenunterabfrage in einem einfachen Vergleich verwendet. In der Unterabfrage werden zuerst der maximale Einzelpreis und der minimale Lagerbestand von allen
Artikeln ermittelt. In der Hauptabfrage wird dann überprüft, ob es Artikel gibt, die gleichzeitig
den maximalen Preis und minimalen Lagerbestand haben. Sind solche vorhanden, werden ihre
Nummern ausgegeben. Die Unterabfrage liefert hier genau eine Zeile mit zwei Spalten. Der Vergleich erfolgt zeilenbasiert, da Einzelpreis und Lagerbestand hinter dem durch die Klammern zu
einer Zeile zusammengefasst werden. Beispiel 14 ist somit semantisch äquivalent zum Beispiel
15:
Beispiel 15
SELECT ArtikelNr
FROM Artikel
WHERE Einzelpreis=(SELECT MAX ( Einzelpreis ) FROM Artikel )
AND
Lagerbestand=(SELECT MIN ( Lagerbestand ) FROM Artikel ) ;
Beispiel 16
SELECT ∗
FROM Bestellposition
WHERE ( ArtikelNr , LieferantenNr ) IN (
SELECT ArtikelNr , LieferantenNr FROM Liefernachweis
WHERE Einkaufspreis <10
);
Beispiel 16 stellt die Verwendung einer Tabellenunterabfrage mit dem IN-Operator dar. In der
Unterabfrage werden zuerst die Kombinationen von Artikeln und Lieferanten ermittelt, denen
ein Einkaufspreis von weniger als 10 entspricht. Somit wird eine Liste von Zeilen gebildet. In
der Hauptabfrage werden dann die Bestellpositionen ausgegeben, die den Kombinationen von
Artikeln und Lieferanten aus der Liste entsprechen.
Beispiel 18
SELECT ArtikelNr , LieferantenNr , Einkaufspreis
FROM
Liefernachweis ln1
WHERE Einkaufspreis=(
SELECT MAX ( ln2 . Einkaufspreis )
FROM Liefernachweis ln2
WHERE ln1 . ArtikelNr = ln2 . ArtikelNr
);
Im Beispiel 18 wird für jeden Artikel der Lieferant (oder Lieferanten) gesucht, der diesen Artikel zum höchsten Preis verkauft. Hier wird diese Aufgabe mit einer korrelierten Unterabfrage
gelöst. Korrelierte Unterabfragen sind solche, die Tabellen aus der Hauptabfrage referenzieren.
In einer Unterabfrage können alle Tabellen (oder Tabellenaliasse) der übergeordneten Abfragen
verwendet werden. Im Beispiel 18 referenzieren beide Aliasse ln1 und ln2 die gleiche Tabelle Liefernachweis. Allerdings verwendet die Unterabfrage auch den Alias ln1, welcher in der
Hauptabfrage vergeben wird.
73
4 Structured Query Language
Korrelierte Unterabfragen sind aber mit Vorsicht zu verwenden, da sie oft sehr ineffizient und
recht langsam sind. Insbesondere muss grundsätzlich eine solche Unterabfrage für jede Zeile
der Hauptabfrage einzeln berechnet werden. Das Umschreiben der Abfrage als Join kann die
Leistung unter Umständen verbessern.
4.11 Ändern von Daten (UPDATE)
Das Ändern bestehender Datensätze geschieht mit dem UPDATE-Befehl. Es können die Werte
einer oder mehrerer Spalten gleichzeitig geändert werden. Während des Änderungsvorgangs sind
die alten Werte zugänglich; so ist es beispielsweise in der Tabelle Artikel möglich, die Preise
aller Artikel um 1 zu erhöhen, ohne die Preise explizit angeben zu müssen.
Es können Bedingungen an die Zeilen der Tabelle gestellt werden, für die Änderungen stattfinden sollen. Änderungen finden immer für alle Zeilen statt, die den Bedingungen hinter dem
Schlüsselwort WHERE genügen. Wird keine WHERE-Klausel verwendet, so werden alle Datensätze
der Tabelle geändert!
Syntax:
UPDATE tbl_name
SET col_name1=expr1 [ , col_name2=expr2 . . . ]
[ WHERE where_condition ]
Beispiel 1
UPDATE Artikel
SET Einzelpreis=Einzelpreis +1;
In Beispiel 1 werden die Preise aller Artikel um 1 erhöht.
Beispiel 2
UPDATE Kunde
SET
PLZ = ’48149 ’ ,
Straße = ’Leonardo - Campus 3’ ,
Region = ’Münsterland ’ ,
Ort = ’Münster ’ ,
Land = ’Deutschland ’
WHERE
Firma = ’ERCIS ’ ;
Im Beispiel 2 werden über mehrere Spalten verteilte Adressdaten des Kunden ERCIS aktualisiert.
4.12 Löschen von Daten (DELETE)
Das Löschen von Datensätzen geschieht durch den DELETE-Befehl. Es können nur ganze Zeilen gelöscht werden. Es kann eine Bedingung angegeben werden, die die zu löschenden Zeilen
erfüllen müssen. Wird keine Bedingung angegeben, so werden alle Datensätze aus einer Tabelle
gelöscht! Die Tabellenstruktur bleibt dabei noch erhalten, kann jedoch mit dem Befehl DROP
TABLE entfernt werden.
Syntax:
DELETE FROM tbl_name
[ WHERE where_condition ]
74
4 Structured Query Language
Beispiel 1
DELETE FROM Kunde
WHERE Ort=’Münster ’ ;
In Beispiel 1 werden alle Kunden, die in Münster ansässig sind, gelöscht.
Beispiel 2
DELETE FROM Artikel ;
In Beispiel 2 werden sämtliche Datensätze aus der Tabelle Artikel entfernt. Die Tabellenstruktur
bleibt dabei noch erhalten, sodass neue Datensätze hinzugefügt werden können.
75