SQL/92, SQL:1999 und TSQL (SQL Server 2000)

Jürgen Bayer
SQL/92, SQL:1999 und TSQL (SQL Server 2000)
Erzeugen von Datenbanken, Abfragen und Verändern von Daten
Inhaltsverzeichnis
1
Einführung
1
1.1
Der Inhalt dieses Artikels
1
1.2
Was ist SQL?
1
2
Grundlegendes
6
2.1
Datentypen
6
2.2
Literale
3
11
Die Data Definition Language
13
3.1
Anlegen einer Datenbank
13
3.2
Löschen eines Schemas bzw. einer Datenbank
17
3.3
Modifizieren eines Schemas bzw. einer Datenbank
18
3.4
Domänen
19
3.5
Anlegen von Tabellen
20
3.6
Tabellen modifizieren
27
3.6.1
Hinzufügen von Spalten
27
3.6.2
Löschen von Spalten
28
3.6.3
Ändern von Spalten
28
3.6.4
Einschränkungen anfügen
29
3.6.5
Einschränkungen löschen
29
3.6.6
Einschränkungen im SQL Server deaktivieren und aktivieren
30
3.7
Tabellen löschen
30
3.8
Indizes erstellen und löschen
31
3.8.1
Indizes erstellen
31
3.8.2
Löschen eines Index
32
4
Die Data Manipulation Language
33
4.1
Anfügen von Datensätzen
33
4.2
Löschen von Datensätzen
36
4.3
Ändern von Datensätzen
38
4.4
Abfragen von Datensätzen
40
4.4.1
Einführung
40
4.4.2
Die Restriktion
41
4.4.3
Die Projektion
41
4.4.4
Die AS-Klausel
42
4.4.5
Einschränken der Ausgabe
43
4.4.6
Arithmetische Ausdrücke
48
4.4.7
Sortieren
49
4.4.8
Gruppieren
49
4.4.9
Funktionen
56
4.4.10
ALL, DISTINCT und TOP
66
4.4.11
Unterabfragen (Subquerys)
67
4.4.12
Die Operatoren ANY, ALL und EXISTS
68
4.4.13
Joins (Verbundausdrücke)
70
5
Sichten
86
6
Die Data Control Language
89
6.1
Einführung
89
6.2
Erteilen und Verweigern von Zugriffsrechten
90
7
7.1
8
Anhang
Literaturverzeichnis
Index
91
91
92
Dieser Artikel behandelt die für die Abfrage und die Manipulation von Datenbanken wichtigen
Aspekte von SQL/92, dem zurzeit in den meisten Datenbanksystemen verwendeten SQLStandard, einige wichtige Neuerungen von SQL:1999 und das SQL des Microsoft SQL Servers
2000 (TSQL).
Kapitel 1 beschreibt zunächst, was SQL überhaupt ist und gibt einen Überblick über die im
Artikel verwendete Beispiel-Datenbank und über die verwendeten Konventionen. Die weiteren
Kapitel beschreiben die einzelnen Teile von SQL:
Kapitel 2 erläutert einige grundlegende Dinge wie die in SQL/92, SQL:1999 und TSQL
verwendeten Datentypen und Literale.
Kapitel 3 behandelt die SQL-Anweisungen zum Erstellen und Modifizieren von Datenbanken
und Tabellen.
Kapitel 4 beschreibt die SQL-Anweisungen zum Abfragen, Anfügen, Aktualisieren und
Löschen von Daten.
Das Kapitel 5 zeigt schließlich, wie Sie Views (Sichten) erzeugen und verwenden.
SQL (Structured Query Language) wird von den meisten Datenbanksystemen unterstützt und ist
eine vom ANSI1-Komitee genormte Sprache zur Abfrage und Modifikation von Datenbanken.
Der Grundstein von SQL wurde 1974 bei IBM mit der Entwicklung der DatenbankAbfragesprache SEQUEL (Structured English Query Language) gelegt. Der erste genormte
SQL-Standard wurde 1986 vom ANSI-Kommitee ratifiziert und wird offiziell als SQL/86
bezeichnet. 1987 wurde der ANSI-Standard dann auch als internationaler Standard von der
International Organization for Standards (ISO) akzeptiert. 1989 wurde SQL um das Merkmal
Integrity Enhancement Feature2 (IEF) erweitert und damit zu SQL/89. SQL/92, der derzeit von
den meisten Datenbanksystemen unterstützte Standard, wurde 1992 als stark erweiterte Version
von SQL/89 ratifiziert. 1995 wurde SQL/92 um das Call Level Interface (CLI) erweitert und
stark geändert und korrigiert. SQL/92 wurde zu Anfang auch SQL/2 genannt, ist aber im
Allgemeinen nur als SQL/92 bekannt. Der zurzeit neueste Standard ist SQL:1999. SQL:1999
(auch bekannt als SQL-99) erweitert SQL/92 hauptsächlichlich um die Möglichkeit,
benutzerdefinierte Datentypen zu speichern und unterstützt damit objektorientierte
Datenbanksysteme. SQL:1999 wird zurzeit von sehr wenigen Datenbanksystemen wie
beispielsweise
Oracle
8
unterstützt.
Im
Dokument
dbs.unileipzig.de/en/lokal/standards.pdf
finden Sie eine Beschreibung der
Unterschiede zu SQL/92.
Der SQL-Standard, der vom ANSI-Komitee verfasst wurde, steht leider im Internet nicht frei
zur
Verfügung.
Sie
können
die
Standard-Dokumente
käuflich
erwerben
(webstore.ansi.org/ansidocstore/find.asp), die Preise sind allerdings so hoch,
dass Sie wohl besser ein gutes Buch kaufen sollten. Sehr zu empfehlen ist der Klassiker »SQL –
1
ANSI = American National Standards Institute
2
Was immer das auch ist ...
Einführung 1
Der Standard« von Chris J. Date und Hugh Darwen. Bei Ocelot finden Sie die Beschreibung der
wichtigen SQL-Features: www.ocelot.ca/dbms.htm.
!
"
Da ANSI-SQL nur eine Empfehlung darstellt, verwenden viele Datenbanksysteme eine von
SQL/92 mehr oder weniger abweichende SQL-Syntax. Die grundsätzlichen Elemente sind zwar
fast immer dieselben, Unterschiede sind jedoch zumindest in der Verwendung von Wildcards
und bei der Angabe von Datumswerten zu beobachten. So verwendet SQL/92 z. B. die
Wildcards »%« für beliebige Zeichen und »_« für ein einzelnes beliebiges Zeichen und AccessSQL statt dessen die Wildcards »*« und »?«.
Client/Server-Datenbanksysteme, wie z. B. der Microsoft SQL Server oder Oracle erweitern
SQL/92 meist noch um spezielle Befehle, die eine Programmierung mit SQL über einfache
Programmstrukturen (wie z. B. Abfragen und Schleifen) ermöglichen. Dieses erweiterte SQL
wird oft in Stored Procedures und Triggern verwendet.
Dieser Artikel beschreibt die grundsätzliche Anwendung von SQL/92 und geht speziell auf die
SQL-Syntax des Microsoft SQL Server ein. Einige Bestandteile des SQL/92-Standards, wie
Module, Cursors und Dynamic SQL werden allerdings nicht besprochen.
#$
Das englischsprachige Datenbanksystem Ocelot behauptet von sich, ein Datenbanksystem zu
sein, das den SQL/92-Standard vollständig und SQL:1999 in Teilen unterstützt. Das Schöne
daran ist, dass Ocelot nichts kostet und aus dem Internet heruntergeladen werden kann. Sie
finden Ocelot auf der Website www.ocelot.ca/dbms.htm. Ein Nachteil von Ocelot ist
allerdings, dass die Schnittstelle zur Datenbank aus klassischen (ODBC-)DLLs besteht und Sie
entweder die ODBC-Schnittstelle zum Zugriff auf Ocelot-Datenbanken oder die DLLFunktionen direkt verwenden müssen, um mit dieser Datenbank zu arbeiten.
% &
'
SQL wird üblicherweise in drei Bereiche unterteilt:
• Die Data Definition Language (DDL) ermöglicht das Anlegen und Modifizieren von
Datenbankobjekten.
•
Die Data Manipulation Language (DML) ermöglicht das Abfragen, Hinzufügen, Ändern
und Löschen von Daten.
•
Mit der Data Control Language (DCL) können Sie administrative Aufgaben, wie z. B. das
Anlegen von Benutzerkonten und die Vergabe von Benutzerrechten vornehmen.
(
)
*
+
Eine SQL-Anweisung beginnt immer mit einem Schlüsselwort, das dem Typ der Abfrage
entspricht (z. B. SELECT, INSERT oder UPDATE) und wird mit einem Semikolon abgeschlossen
(das jedoch in vielen Datenbanksystemen auch weggelassen werden kann). Zusätzlich können
noch sogenannte „Klauseln“ hinzugefügt werden, die z. B. das Ergebnis einschränken,
gruppieren oder sortieren.
Einführung 2
,
,
-
Den Beispielen liegen die folgenden Basistabellen zugrunde, die in der beim SQL Server 2000
beiliegenden Beispieldatenbank Northwind gespeichert sind (Abbildung 2).
Abbildung 1: Die Beispieltabellen der Northwind-Datenbank mit den Beziehungen zwischen den Tabellen
Diese Abbildung wurde mit Microsoft Access erzeugt und spiegelt die Beziehungen zwischen
den Tabellen sehr gut wieder. Eine 1 in einer Beziehung steht dafür, dass diese Tabelle die
Mastertabelle ist. Eine liegende 8 steht dafür, dass es sich um eine 1:N-Beziehung handelt.
./ 0
/
Dieser Artikel verwendet die folgenden (größtenteils auch in anderen Umgebungen
verwendeten) typographischen Konventionen:
Beispiele werden in diesem Artikel schattiert dargestellt:
SELECT CustomerID, CompanyName
FROM Customers
ORDER BY CompanyName DESC;
Syntaxbeschreibungen werden ebenfalls schattiert:
SELECT Datenfeldliste
FROM Tabelle(n)
[WHERE Bedingung(en)]
[GROUP BY Datenfeld(er) [HAVING Bedingung(en)]]
[ORDER BY Datenfeld(er) [{ASC | DESC}]];
Kursive Wörter in Syntaxbeschreibungen sind Platzhalter für von Ihnen anzugebende
Informationen. Im obigen Beispiel müssen Sie für das Argument Datenfeld(er) z. B. alle Felder
eintragen, die Sie im Ergebnis der Abfrage ausgeben wollen. Die in eckigen Klammern
stehenden Elemente sind optional. Diese Elemente können, müssen Sie jedoch nicht angeben.
Die eckigen Klammern werden allerdings nicht mit angegeben.
Einführung 3
Wenn irgendwo in Syntaxbeschreibungen der Begriff »Liste« vorkommt, ist immer eine
kommabegrenzte Liste einzelner Werte gemeint. Die Datenfeldliste aus dem Beispiel oben
könnte z. B. folgendermaßen aussehen:
CustomerID, CompanyName, ContactName
Die oben beschriebene SELECT-Anweisung können Sie demnach also z. B. folgendermaßen
aufrufen:
SELECT CustomerID, CompanyName
FROM Customers;
SELECT CustomerID, CompanyName
FROM Customers
WHERE City = 'Berlin';
SELECT CustomerID, CompanyName
FROM Customers
WHERE City = 'Berlin'
ORDER BY CompanyName;
SELECT CustomerID, CompanyName
FROM Customers
ORDER BY CompanyName;
Geschweifte Klammern mit vertikalen Trennlinien ({ASC | DESC}) bedeuten, dass Sie die
Wahl zwischen zwei oder mehr Elementen haben. Ist eines der Elemente unterstrichen
dargestellt, ist dieses Element die Voreinstellung, die zum Tragen kommt, wenn Sie keines der
Elemente angeben. Sie müssen eines der Elemente wählen, es sei denn, alle Elemente stehen
außerdem in eckigen Klammern. Bei der Abfrage von Daten können Sie z. B. aufsteigend oder
absteigend sortieren:
[ORDER BY Datenfeld(er) [{ASC | DESC}]]
Beispiele:
SELECT CustomerID, CompanyName
FROM Customers
ORDER BY CompanyName;
SELECT CustomerID, CompanyName
FROM Customers
ORDER BY CompanyName ASC;
SELECT CustomerID, CompanyName
FROM Customers
ORDER BY CompanyName DESC;
Da ASC im verwendeten Beispiel unterstrichen dargestellt ist, können Sie davon ausgehen, dass
SQL die aufsteigende Sortierung verwendet, wenn Sie weder ASC noch DESC angeben.
Weitere Konventionen stellt Tabelle 1.1 dar.
Konvention
Beschreibung
SELECT, FROM
Im normalen Text werden sprachspezifische Schlüsselwörter in der Schriftart
Courier dargestellt.
sql.doc
Dateinamen werden kursiv dargestellt.
DATEI / DRUCKEN
Menübefehle werden in Kapitälchen bezeichnet. Ein Schrägstrich trennt die zu
aktivierenden Menüs voneinander und vom Befehl.
Tabelle 1.1: Weitere Konventionen in diesem Artikel
Einführung 4
Hinweise, die Sie beachten sollten, werden wie in diesem Beispiel schattiert
dargestellt.
Besondere „Warnungs“-Hinweise weisen auf wichtige Informationen hin, die Sie
auch jeden Fall beachten sollten. Häufig werden damit Fallen gekennzeichnet, in
die Sie tappen können, wenn Sie die entsprechende Warnung nicht kennen.
Tipps werden dargestellt wie in diesem Beispiel.
Einführung 5
(
!#$
Spätestens bei der Erzeugung von Tabellen werden Sie in SQL mit Datentypen konfrontiert.
SQL/92 unterstützt die in Tabelle 2.1 aufgeführten Datentypen.
Datentyp
Beschreibung
CHARACTER(n)
oder
CHAR(n)
Zeichenkette fester Länge mit genau n Zeichen, wobei n größer als 0
sein muss. In CHAR-Spalten, die keine NULL-Werte erlauben, werden
Zeichenketten nach rechts mit Leerzeichen aufgefüllt, die u. U. bei der
Bearbeitung abgeschnitten werden müssen. Werden Zeichenketten
eingegeben, die größer sind als der Maximalwert, so schneidet das
Datenbanksystem die Zeichen nach rechts einfach ab. Der SQL Server
erlaubt maximal 8000 Zeichen in einem CHAR-Feld.
CHARACTER VARYING(n)
oder
VARCHAR(n)
Zeichenkette variabler Länge mit bis zu n Zeichen, wobei n größer als 0
sein muss. Bei den meisten Datenbanksystemen ist VARCHAR(n) dem
Datentyp CHAR(n) vorzuziehen, da variable Datentypen immer nur die
Daten physikalisch speichern, die auch im Datenfeld abgelegt sind.
Somit können Sie ein Datenfeld ruhig größer gestalten als notwendig,
um für später genügend Spielraum zu besitzen. Der SQL Server erlaubt
maximal 8000 Zeichen in einem VARCHAR-Feld.
BIT(n)
Binäre Daten fester Länge. Der SQL Server kennt den Datentyp BIT
nicht als Datentyp für binäre Daten, sondern als Datentyp für boolsche
Daten (0 und 1). Statt BIT(n) verwendet der SQL Server BINARY(n).
BIT VARYING(n)
Binäre Daten variabler Länge. Der SQL Server kennt den Datentyp BIT
VARYING nicht. Statt BIT VARYING(n) verwendet der SQL Server
varbinary(n).
DECIMAL(p, q)
oder
DEC(n)
speichert Dezimalzahlen mit festen Dezimalstellen und Vorzeichen mit p
Ziffern insgesamt und mindestens q Ziffern rechts vom Komma. Die
tatsächliche Anzahl der Stellen rechts vom Komma hängt von der
Implementierung dieses Datentyps im Datenbanksystem ab. DECIMAL
zählt wie NUMERIC zu den Festkommazahlen. Beim SQL Server können
Sie für p maximal 38 angeben. q muss größer 0 und kleiner/gleich p sein.
Der Wertebereich von DECIMAL liegt beim SQL Server zwischen -1038
+1 und 1038 - 1.
NUMERIC(p, q)
entspricht funktional dem Typ DECIMAL. SQL/92 sieht vor, dass die
Genauigkeit bei NUMERIC(p, q) genau p Stellen beträgt, bei DECIMAL(p,
q) allerdings mindestens p Stellen. Beim SQL Server bestehen keine
Unterschiede zwischen NUMERIC und DECIMAL.
INTEGER
oder
INT
Vorzeichenbehaftete ganze Zahl. Die Größe hängt von der
Implementierung dieses Datentyps im Datenbanksystem ab. Bei 32 BitBetriebssystemen beträgt die Größe in der Regel 32 Bit. Der SQL Server
verwendet 32 Bit und speichert damit Zahlen zwischen -231
(-2.147.483.648) und 231-1 (2.147.483.647).
Grundlegendes 6
SMALLINT
Vorzeichenbehaftete ganze Zahl. Die Größe hängt von der
Implementierung dieses Datentyps im Datenbanksystem ab. Bei 32 BitBetriebssystemen beträgt die Größe in der Regel 16 Bit. Der SQL Server
verwendet 16 Bit und speichert damit Zahlen zwischen -32768 und
32767.
FLOAT(p)
FLOAT ist eine Gleitkommazahl mit insgesamt p Ziffern. Die Anzahl der
Stellen rechts vom Komma hängt von der Implementierung dieses
Datentyps im Datenbanksystem und von der Anzahl Ziffern vor dem
Komm ab. Beim SQL Server können Sie für p einen Wert zwischen 1
und 53 angeben. Bei 1-24 werden sieben Dezimalstellen verwaltet, bei
25-53 dagegen 15. Der SQL Server erlaubt in einem FLOAT-Feld (je
nach p-Wert) maximal Zahlen im Bereich von –1,79 * 10308 bis 1,79 *
10308.
Tabelle 2.1: Die Datentypen von SQL/92
SQL/92 kennt noch einige Abkürzungen für Datentypen, die in Tabelle 2.2 dargestellt werden.
Abkürzung
Beschreibung
CHARACTER
oder
CHAR
CHAR ist eine Abkürzung für CHAR(1).
NUMERIC(p)
steht für NUMERIC(p, 0).
DECIMAL(p)
steht für DECIMAL(p, 0) .
FLOAT
FLOAT ist eine Abkürzung für FLOAT(p), wobei p durch das
Datenbanksystem definiert wird. Der SQL Server verwendet für p den Wert
24. Damit sind im SQL Server Zahlen im Bereich von -3,40 * 1038 bis 3,40 *
1038 möglich.
REAL
REAL ist eine Abkürzung für FLOAT(p), wobei p durch das Datenbanksystem
definiert wird. Der SQL Server verwendet den Wert 24 für p. Damit sind
Zahlen zwischen -3,40 * 1038 und 3,40 * 1038 möglich.
DOUBLE PRECISION
oder
DOUBLE
DOUBLE ist eine Abkürzung für FLOAT(p), wobei p durch das
Datenbanksystem definiert wird. Beim SQL Server steht DOUBLE für FLOAT.
Tabelle 2.2: Die Abkürzungen für Datentypen bei SQL/92
Grundlegendes 7
1 $$$
SQL:1999 fügt den SQL/92-Datentypen vier weitere hinzu (Tabelle 2.3).
Datentyp
Bedeutung
LARGE OBJECT
oder
LOB
Dieser Datentyp kann entweder Zeichenketten (CHAR), CLOBs (Character
Large Objects) oder BLOBs (Binary Large Objects) speichern. CLOBs
erlauben sehr große Zeichenkettendaten, BLOBs sehr große binäre Daten.
LOBs weisen gegenüber normalen Feldern einige Einschränkungen auf. So
können Sie diese Felder z. B. nicht als Primärschlüssel, in UNIQUEEinschränkungen, in GROUP BY- oder ORDER BY-Klauseln verwenden.
BOOLEAN
Dieser boolesche Datentyp kann in SQL-Anweisungen direkt mit den
Konstanten true, false und unknown verglichen werden.
Datentyp ARRAY[n] Dieser Datentyp erlaubt die Deklaration eines Arrays in einer Spalte:
CREATE TABLE Demo (
Weekdays VARCHAR(10) ARRAY[7])
ROW(Datentypliste)
Über Row können Sie eine Spalte unterstrukturieren. So können Sie z. B. in
einer Kunden-Tabelle eine Spalte Name erzeugen, die aus den Unterspalten
FirstName und LastName besteht:
CREATE TABLE Customer (
CustomerId INTEGER,
Name ROW (
FirstName VARCHAR(255),
LastName VARCHAR(255)),
Street VARCHAR(255),
City VARCHAR(255));
Tabelle 2.3: Die zusätzlichen Datentypen von SQL:1999
"
2
!-
SQL/92 unterstützt Datums- und Uhrzeitangaben leider in einer sehr komplexen Form,
weswegen ich diese Datentypen separat und nur grundlegend beschreibe. Die meisten
Datenbanksysteme reduzieren die Komplexität auf einen oder zwei einfacher anzuwendende
Datentypen.
Datentyp
Beschreibung
DATE
DATE speichert in SQL/92 eine Folge von Zahlen, die das Jahr, den Monat und
den Tag eines Datums speichern. Das Jahr wird vierstellig im Bereich von 0000
bis 9999 angegeben, der Monat zweistellig im Bereich 01 bis 12 und der Tag
ebenfalls zweistellig im Bereich 01 bis 31.
TIME[(n)]
TIME speichert in SQL/92 eine Folge von Zahlen, die eine Zeit angibt. Die
[WITH TIMEZONE] Stunde wird zweistellig im Bereich 00 bis 23 angegeben, die Minute ebenfalls
zweistellig im Bereich 00 bis 59. Die Sekunde kann als Dezimalzahl angegeben
werden. Die optionale Angabe n bezieht sich auf die maximale Anzahl Ziffern
rechts vom Komma für die Sekunde. Geben Sie die Option WITH TIMEZONE
an, wird die Zeit nicht als Ortszeit gespeichert, sondern als sogenannte UTCZeit (eine allgemeine Vergleichszeit, die an allen Orten der Welt den gleichen
Wert besitzt). Damit sind Zeitvergleiche auch an verschiedenen Orten der Welt
möglich. Die Umwandlung nach UTC übernimmt das Datenbanksystem beim
Anfügen und Ändern von Datensätzen in der Regel automatisch.
TIMESTAMP[(n)]
Ein TIMESTAMP-Wert (Zeitstempel) speichert ein Datum mit Zeitangabe.
Tabelle 2.4: Die Abkürzungen für Datentypen bei SQL/92
Grundlegendes 8
Daneben können SQL/92-Datenbanken noch Datums- und Zeitintervalle speichern. Ein Intervall
ist eine Angabe wie z. B. ein Monat oder 90 Tage oder 1 Jahr, 2 Monate. Ich spreche Intervalle
hier nur grundlegend an, weil ich denke, dass diese von den meisten Datenbanksystemen nicht
unterstützt werden. Sie deklarieren ein Intervall folgendermaßen:
INTERVAL Anfang [TO Ende]
Anfang bezeichnet, welchen Datumsbereich Sie als erste Angabe im Intervall verwenden wollen
(YEAR, MONTH, DAY, HOUR oder SECOND). Ende bezeichnet die zweite optionale Intervallangabe.
Wenn Sie z. B. Monate speichern wollen, geben Sie
INTERVALL MONTH
an. Wenn Sie Jahre und Monate speichern wollen, geben Sie
INTERVALL YEAR TO MONTH
an.
Da Datums- und Zeitdatentypen in den meisten Datenbanksystemen sowieso anders
implementiert sind, verzichte ich auf eine tiefergehende Beschreibung.
0
Der SQL Server definiert zusätzlich zu den SQL/92-Datentypen die in Tabelle 2.5 dargestellten.
Datentyp
Beschreibung
nchar[(n)]
Der (SQL/92-)Datentyp char speichert ASCII-Zeichen. In einem solchen
Feld können Sie beim SQL Server maximal 8000 Zeichen speichern. nchar
wird wie char verwendet, speichert jedoch Unicode-Zeichen. Das Unicode
zwei Byte pro Zeichen verwaltet, können Sie in einem nchar-Feld maximal
4000 Zeichen verwalten. Auf Betriebssystemen, die Texte standardmäßig als
Unicode speichern (Windows NT, 2000, XP) führt die Verwendung von
nchar gegenüber char zu einer Performance-Erhöhung, da dann keine
Konvertierung erfolgt.
nvarchar[(n)]
nvarchar ermöglicht wir varchar die dynamische Speicherung von
Zeichen, speichert jedoch (wie nchar) Unicode-Zeichen.
datetime
Dieser Datentyp speichert Datumswerte zwischen dem 1.1.1753 und dem
31.12.9999. Wenn bei der Eingabe die Zeit weggelassen wird, wird der Wert
00:00:00 verwendet, wird das Datum weggelassen wird der 01.01.1900
eingetragen.
smalldatetime
Speichert Datumswerte zwischen dem 01.01.1900 und dem 06.06.2079 mit
einer Genauigkeit von einer Minute. Wenn Sie erreichen wollen, dass Ihre
Software noch nach dem 06.06.2079 korrekt läuft, verwenden Sie diesen
Datentyp nicht (denken Sie an das Jahr 2000-Problem).
bigint
speichert ganze Zahlen zwischen -263 (-9.223.372.036.854.775.808)
und 263-1 (9.223.372.036.854.775.807).
tinyint
speichert ganze Zahlen zwischen 0 und 255.
money
speichert Dezimalzahlen mit vier festen Dezimalstellen zwischen 922.337.203.685.477,5808 und
+922.337.203.685.477,5807. Wird für Währungen verwendet, da diese mit
vier Dezimalstellen gerechnet werden. Berechnungen mit festen
Dezimalstellen sind schneller als Berechnungen mit dynamischen
Dezimalstellen.
smallmoney
speichert Währungsbeträge zwischen -214.748,3648 und +214.748,3647
Grundlegendes 9
text
speichert dynamische ASCII-Zeichenketten bis zu 2.147.483.647 Zeichen.
ntext
ntext speichert wie text dynamische Zeichenketten, allerdings als
Unicode-Zeichen. Deswegen können auch nur 1.073.741.823 Zeichen
gespeichert werden.
image
image speichert Bitdaten (normalerweise Bilddaten) bis 2.147.483.647 Byte
Größe. image wird häufig aber auch für die Speicherung von binären
Objekten, wie z. B. OLE-Objekten verwendet.
binary
Dieser Datentyp speichert binäre Daten mit einer maximalen Länge von
8000 Byte.
varbinary
varbinary speichert wie binary Bitmasken. Der Server benötigt jedoch
nur soviel Speicherplatz, wie die tatsächlich eingegebenen Daten und nicht
wie bei binary immer die eingestellte Größe.
bit
Bit speichert nur 0 oder 1 und ermöglicht damit boolesche Felder. Sie
können normale Integerwerte in einem solchen Feld speichern, Werte
ungleich 0 werden jedoch als 1 gespeichert. Bit benötigt ein Byte
Speicherplatz.
timestamp
Ein timestamp-Feld wird bei jedem Hinzufügen oder Ändern eines
Datensatzes automatisch mit einem über die gesamte Tabelle eindeutigen
Wert (der aus der Systemzeit generiert wird) belegt. Solche Felder benötigen
Sie immer dann (und eigentlich auch nur dann), wenn Tabellen in mehreren
Datenbanken repliziert werden sollen. Der SQL Server erkennt beim
Synchronisieren der einzelnen Datenbanken am timestamp-Feld, ob ein
Datensatz verändert wurde. Die Speicherung entspricht der eines
varbinary(8)-Feldes. Sie können nur ein timestamp-Feld pro Tabelle
generieren.
uniqueidentifier
Dieser Datentyp dient der Speicherung einer GUID (Global Unique
Identifier). Eine GUID ist ein 16-Bit Hexadezimalwert, der über die gesamte
Welt eindeutig ist. In TSQL kann eine GUID mit der NEWID-Funktion
erzeugt werden. GUID’s werden häufig zur Benennung und Identifikation
von Objekten verwendet.
sysname
sysname ist ein benutzerdefinierter Datentyp, der als nvarchar(128)
definiert ist. Nullwerte werden nicht erlaubt. Dieser Datentyp wird vom SQL
Server in Systemtabellen verwendet.
cursor
verweist auf einen Cursor. Dieser Datentyp wird eigentlich nur innerhalb von
Stored Procedures eingesetzt, um einen Cursor zu verwalten, über den eine
Tabelle oder Abfrage sequenziell durchlaufen werden soll.
table
Dieser Datentyp wird hauptsächlich innerhalb von Stored Procedures
verwendet, um das Ergebnis einer Abfrage temporär zwischenzuspeichern
(ähnlich einem Recordset bei ADO oder einem ResultSet bei JDBC).
sql_variant
Dieser Datentyp ermöglicht das Speichern verschiedener Datentypen in
einem Feld, ähnlich dem Datentyp Variant in Visual Basic.
Tabelle 2.5: Die zusätzlichen Datentypen des SQL Servers
Grundlegendes 10
#$
Wenn Sie Werte für Spalten in SQL-Anweisungen eingeben, müssen Sie für diese Werte die
korrekten Literale einsetzen. Deswegen folgt hier eine kurze Beschreibung der am meisten
verwendeten Literale.
Datentyp
Schreibweise
Zeichenketten
Zeichenketten werden üblicherweise in einfachen Anführungszeichen
eingeschlossen: '
Dies ist eine Zeichenkette'
. Kommt das einfache
Anführungszeichen innerhalb der eigentlichen Zeichenkette vor, wird
dieses üblicherweise verdoppelt:
'
That'
'
s how it is'
. Besonders dann, wenn Sie die Eingabe einer
Zeichenkette, die Sie in SQL-Anweisungen verwenden, dem
Anwender Ihrer Applikation überlassen, müssen Sie darauf achten,
dass eingegebene einfache Anführungszeichen verdoppelt werden.
Bitketten
Bitketten werden eigentlich selten in SQL-Anweisungen eingesetzt.
Für den Fall, dass Sie dies einmal benötigen: Bitketten werden als
Duale Zahl in einfachen Anführungszeichen geschrieben und ein B
vorangestellt: B'
11001001'
. Wenn Sie die Zahl hexadezimal angeben
wollen, stellen Sie ein X voran: X'
C19A'
.
Genau numerische Werte
Genau numerische Werte werden als Dezimalzahl mit oder ohne
Vorzeichen geschrieben. Als Dezimaltrennzeichen gilt der Punkt: 1.234.
Annähernd genau
numerische Werte
Annähernd genau numerische Werte werden in wissenschaftlicher
Notation geschrieben: xEy bedeutet x * 10y.
Datumswerte
Datumswerte werden in der Form DATE '
yyyy-mm-dd'angegeben. Das
Schlüsselwort DATE scheint bei SQL/92 zwingend notwendig zu sein.
Zeitwerte
Zeitwerte werden in der Form TIME '
hh:mm:ss[.Dezimalwert]'
angegeben. Das Schlüsselwort TIME scheint zwingend notwendig zu
sein.
Zeitstempel
Zeitstempel werden ähnlich wie Datums- und Zeitwerte angegeben:
TIMESTAMP '
yyyy-mm-dd hh:mm:ss[.Dezimalwert]'
.
Tabelle 2.6: Die Literale von SQL/92
Grundlegendes 11
0
Die Literale des SQL Server basieren auf SQL/92 und sind um Literale für die zusätzlichen
Datentypen erweitert (Tabelle 2.7).
Datentyp
Schreibweise
Datumswerte
Für Datumsangaben können Sie unter vier verschiedenen Varianten wählen:
'
Monat_ausgeschrieben Tag, Jahr' (Beispiel: '
April 15, 1998'
),
'
Tag Monat_ausgeschrieben Tag, Jahr' (Beispiel: '
15 April, 1998'
),
'
JahrMonatTag'(Beispiel: '
19980415'
) oder
'
Monat/Tag/Jahr'(Beispiel: '
04/15/98'
).
Die Variante '
Monat/Tag/Jahr'ist sprachspezifisch (hier für die englische
Version). In einer deutschen Version des SQL Servers funktioniert diese
Variante nicht (dafür gibt es dort eine deutsche Variante). Wenn Sie
Datumswerte angeben müssen, verwenden Sie idealerweise die Variante
'
JahrMonatTag'
, weil Sie diese in allen Sprachen einsetzen können.
Zeitwerte werden in der Form '
hh:mm:ss'(Beispiel: '
12:59:10'oder (für
Amerikaner) '
hh:mm {AM | PM}'(Beispiel: '
12:59 PM'
) angegeben.
bit
Beim Datentyp bit geben Sie eine 0 für False oder eine 1 für True an.
Tabelle 2.7: Die zusätzlichen Literale beim SQL Server
!-
0/
Der Datentyp eines Literals leitet sich aus der Form ab, in der es geschrieben ist (Tabelle 2.8).
Literal
Datentyp
Zeichenketten
CHAR(n)
Bitketten
BIT(n)
Genau numerische Werte
NUMERIC(p, q), wobei p die Anzahl der Ziffern vor dem
Annähernd genau
numerische Werte in der
wisschenschaftlichen
Schreibweise (z. B. 0.5E2)
FLOAT(p), wobei p die Anzahl der Stellen ist, die dem E im Literal
Dezimaltrennzeichen und q die Anzahl der Stellen hinter dem
Dezimaltrennzeichen ist.
voranstehen.
Tabelle 2.8: Datentypen von Literalen
Grundlegendes 12
3
/
3
Die meisten mir bekannten Datenbanksysteme verwenden das Konzept der Datenbanken.
SQL/92 jedoch kennt keine Datenbanken, sondern nur Kataloge und Schemata. Ein Katalog ist
eine Sammlung aus Schemata. Ein Schema ist in SQL/92 eine Sammlung aus Domänen3,
Basistabellen, Sichten4, Einschränkungen5, Rechten, Zeichenmengen, Zeichenordnungen und
Zeichenübersetzungen, also der Objekte, die üblicherweise auch in einer Datenbank gespeichert
werden. Ein Vergleich des Schema-Konzepts mit dem Datenbankkonzept ist recht schwierig.
Eine Datenbank enthält dieselben Objekte wie ein Schema, ist jedoch unabhängig von anderen
Datenbanken innerhalb des Datenbanksystems. Ein Schema gehört dagegen immer zu einem
Katalog, der auch mehrere Schemata enthalten kann. Bei mehreren Schemata innerhalb eines
Katalogs können Sie nicht davon ausgehen, dass die einzelnen Schemata voneinander
unabhängig sind.
Einige Datenbanksysteme, wie z. B. Oracle, basieren ausschließlich auf dem Schemakonzept,
andere, wie z. B. Access und dBASE, basieren ausschließlich auf dem Datenbankkonzept.
Einige Datenbanksysteme, wie z. B. der SQL Server, unterstützen beide Konzepte (wobei beim
SQL Server das Schemakonzept eigentlich nicht verwendet wird und Schemata in Datenbanken
angelegt werden).
#$
SQL/92 enthält keine Anweisung zur Erzeugung von Katalogen und überläßt die
Implementierung dieser Anweisungen den Datenbanksystemen. Ein Schema erzeugen Sie mit
der CREATE SCHEMA-Anweisung:
CREATE SCHEMA
{Schema-Name | AUTHORIZATION Benutzername}
[DEFAULT CHARACTER SET Zeichenmenge]
[Schema-Element-Liste]
Viele Datenbanksysteme (wie Oracle und der SQL Server) assoziieren ein Schema mit einem
im Datenbanksystem gespeicherten Benutzer. Der über AUTHORIZATION Benutzername
angegebene Benutzer wird zum Besitzer der in diesem Schema angelegten Objekte. SQL/92
erlaubt daneben auch die Erzeugung von Schemata, die keinem Benutzer zugeordnet sind. Die
Schema-Element-Liste setzt sich zusammen aus einer oder mehreren Anweisungen zur
Erzeugung von Domänen, Basistabellen und den anderen Schema-Elementen und der
Gewährung von Rechten auf diesen Objekten.
3
Domänen sind auch als „benutzerdefinierte Datentypen“ oder „Wertebereiche“ bekannt. Mit einer
Domäne können Sie z.B. den Datentyp char(25) als „address“ bezeichnen und bei Ihrer
Tabellenerzeugung statt char(25) immer den neuen Datentyp address verwenden.
4
Eine Sicht (engl. View) ist eine gespeicherte Abfrage über eine oder mehrere Basistabellen, die eine
spezielle Sicht auf die Daten der Tabellen ermöglicht.
5
Einschränkungen (engl. Constraints) definieren Primär- und Fremdschlüssel und überprüfen die
eingegebenen Daten in einer Tabelle.
Die Data Definition Language 13
Beispiel: Erzeugen eines Schemas mit zwei Tabellen für den Benutzer sa beim SQL Server:
CREATE SCHEMA
AUTHORIZATION sa
CREATE TABLE Customers (
CustomerID int NOT NULL,
CompanyName nvarchar(80) NOT NULL,
PostCode nvarchar(10) NOT NULL,
City nvarchar(80) NOT NULL,
Street nvarchar(80) NOT NULL )
CREATE TABLE Products (
ProductID int NOT NULL,
ProductName nvarchar(80) NOT NULL,
Price money NOT NULL)
Der SQL Server erzeugt ein Schema innerhalb einer Datenbank. Das SchemaKonzept dient beim SQL Server lediglich zur Erzeugung von Tabellen und Sichten
und Rechten darauf in einem Schritt und wird in der Praxis eigentlich nicht
verwendet.
Für Datenbanksysteme, die nach dem Datenbankkonzept arbeiten, wird an Stelle von CREATE
SCHEMA die CREATE DATABASE-Anweisung verwendet. Die Syntax dieser Anweisung
unterscheidet sich erheblich bei den verschiedenen Datenbanksystemen. In der einfachsten
Form können Sie die folgende Syntax verwenden:
CREATE DATABASE Datenbankname;
Der Datenbankname richtet sich nach den Konventionen, die im verwendeten Datenbanksystem
gültig ist.
Je nach Datenbanksystem müssen (oder können) Sie beim Anlegen einer Datenbank weitere
Argumente übergeben.
0
Beim SQL Server können Sie beim Anlegen einer Datenbank u. a. steuern, in welchen Dateien
die Datenbank angelegt wird:
CREATE DATABASE Datenbankname
[ON
[Dateispezifikation [,...n]]
[,Dateigruppe [,...n]]
]
[LOG ON Dateispezifikation [,...n]]
[COLLATE Sortierungsname]
[FOR LOAD | FOR ATTACH]
Das Argument Dateispezifikation bezieht sich auf die folgenden Angaben, die pro Datei
notwendig sind:
[PRIMARY]
( NAME = Logischer_Dateiname
[, FILENAME = 'Dateiname']
[, SIZE = Größe]
[, MAXSIZE = {Maximale_Größe | UNLIMITED}]
[, FILEGROWTH = Größen_Inkrement])
Wenn Sie mehrere Dateien für eine Datenbank anlegen (was z. B. in Systemen mit mehreren
Festplatten Sinn macht, um die Auslastung der Platten zu verteilen), müssen Sie eine Datei über
PRIMARY als primäre Datei angeben. Die primäre Datei enthält die Systemtabellen der
Datenbank. Wenn Sie PRIMARY nicht angeben, wird die erste Datei zur primären Datei.
Die Data Definition Language 14
Das Argument Dateigruppe bezieht sich auf Angaben, die für Dateigruppen notwendig sind:
FILEGROUP Dateigruppen_Name Dateispezifikation [,...n]
Dateigruppen werden weiter unten erläutert. Übersichtlicher wird die Syntax unten in den
Beispielen.
Mit ON legen Sie fest, in welcher Datei bzw. in welchen Dateien die Datenbank angelegt wird.
Der SQL Server ermöglicht die Anlage von Datenbanken in mehreren Dateien, von denen eine
die primäre Datei ist. Die Anlage in mehreren Dateien kann Performancevorteile bringen, wenn
die einzelnen Dateien auf verschiedenen Festplatten angelegt werden und besitzt Vorteile bei
sehr großen Datenbanken bei einem Plattencrash. In einem solchen Fall können Sie mehrere
Dateien auch auf mehreren kleineren Festplatten aus einem Backup restaurieren und benötigen
nicht unbedingt eine große Festplatte mit ausreichend viel Platz. Im Notfall, wenn die
Datenbank schnell wieder laufen muss, haben Sie vielleicht keine große Festplatte, aber (evtl.
aus den Client-Computern) mehrere kleinere zur Verfügung.
Mit dem Argument FILEGROUP können Sie mehrere Dateien in einzelnen Dateigruppen
anlegen. Mit Dateigruppen können Sie die Performance erhöhen, indem Sie festlegen, in
welchen Dateigruppen (die in der Regel auf verschiedenen Festplatten angelegt werden), die
Systemtabellen und in welchen die verschiedenen Tabellen und Indizes angelegt werden (beim
Erstellen von Tabellen und Indizes können Sie festlegen, in welcher Dateigruppe diese angelegt
werden). Tabellen und Indizes, auf die häufig zugegriffen wird, können Sie z. B. in einer
Dateigruppe anlegen, deren Dateien auf einer sehr schnellen Festplatte gespeichert sind.
Dateigruppen können zudem Sinn machen, wenn nur Teile der Datenbank über ein Backup
gesichert werden sollen (ein Backup spezieller Dateigruppen). Der SQL Server verwaltet die in
der CREATE DATABASE-Anweisung oben angegebenen Dateien in der primären, namenlosen
Dateigruppe. In dieser Dateigruppe werden die Systemtabellen der Datenbank angelegt. Per
Voreinstellung ist die primäre Dateigruppe auch die Default-Dateigruppe, in der neue Tabellen
und Indizes angelegt werden, wenn bei deren Anlage derselben keine Dateigruppe angegeben
wird. Sie können mit der ALTER DATABASE-Anweisung eine andere Dateigruppe zur DefaultDateigruppe machen.
Das Argument LOG ON steuert, in welchen Dateien das Transaktionsprotokoll6 der Datenbank
angelegt wird.
Über COLLATE können Sie die Standardsortierung der Datenbank angeben, wenn Sie eine
andere als die Standardsortierung des SQL Servers verwenden wollen (hauptsächlich für
internationale Datenbanken). Sie können hier einen Windows- oder SQL-Sortierungsnamen
angeben. Die entsprechenden Namen sind in der TSQL-Referenz beschrieben. Die
entsprechenden Seiten finden Sie im Inhaltsverzeichnis über INSTALLIEREN VON SQL SERVER /
SORTIERUNGSOPTIONEN FÜR INTERNATIONALE UNTERSTÜTZUNG.
FOR LOAD existiert nur noch aus Kompatibilitätsgründen zum SQL Server 6.5. Mit FOR ATTACH
können Sie eine Datenbank aus bereits vorhandenen Dateien aufbauen. Dieses Argument
benötigen Sie immer dann, wenn Sie Datenbanken mit mehr als 16 Dateien aufbauen wollen, da
CREATE DATABASE in einem Schritt nur maximal 16 Dateien zulässt.
Für jede Datei müssen sie den physikalischen Dateinamen im Argument FILENAME und den
logischen Dateinamen im Argument NAME angeben. Der logische Dateiname muss nicht
angegeben werden, wenn Sie eine Datenbank mit FOR ATTACH aus bereits vorhandenen Dateien
zusammensetzen. Dieser Dateiname wird in SQL-Anweisungen angegeben, wenn diese sich auf
die Datei beziehen (wie z. B. ALTER DATABASE). Der logische Dateiname muss innerhalb der
Datenbank eindeutig sein.
Das Argument SIZE steuert die Anfangsgröße der Datei. Wird SIZE nicht angegeben,
verwendet der SQL Server für die primäre Datei die Größe der model-Datenbank7 und für alle
6
Das Transaktionsprotokoll wird im Artikel »SQL Server 2000« erläutert.
7
Die model-Datenbank dient beim SQL Server als Vorlage für neu angelegte Datenbanken
Die Data Definition Language 15
weiteren Dateien eine Größe von 1 MB. MAXSIZE bestimmt, wie groß die Datei automatisch
wachsen kann. Geben Sie dieses Argument nicht an, oder geben Sie hier UNLIMITED an, kann
die Datei bis zu einer (theoretisch) unendlichen Größe wachsen. Geben Sie den Suffix KB oder
MB an, um die maximale Dateigröße festzulegen.
Das Argument FILEGROWTH bestimmt, wie die Datei vergrößert wird. Sie können hier einen
Wert in KB, MB oder in Prozent angeben. Wenn Sie den Wert 0 angeben, wird die Datei nicht
automatisch vergrößert.
Normalerweise legen Sie eine SQL Server-Datenbank mit einer Anfangsgröße von 1 MB pro
Datei an und ermöglichen das automatische Wachsen in %-Schritten (z. B. 15%). Dadurch wird
gewährleistet, dass die Datenbank nach einer anfänglichen Initialisierungsphase im späteren
Betrieb nicht allzu häufig vergrößert werden muss. Das automatische Wachsen der Dateien
verhindert massive Probleme, die entstehen, wenn der freie Platz in der Datenbank bzw. im
Transaktionsprotokoll zu klein ist.
Das folgende Beispiel erzeugt eine einfache Datenbank mit einer Datei für die Datenbank und
einer Datei für das Transaktionsprotokoll:
CREATE DATABASE Bestellungen
ON
PRIMARY
( NAME = Bestellungen_Data,
FILENAME = 'c:\Bestellungen_Data.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% )
LOG ON
( NAME = Bestellungen_Log,
FILENAME = 'c:\Bestellungen_Log.ldf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% )
Das nächste Beispiel legt diese Datenbank mit mehreren Dateien für die Datenbank und einer
Datei für das Transaktionsprotokoll an:
CREATE DATABASE Bestellungen
ON
PRIMARY
( NAME = Bestellungen_Data_1,
FILENAME = 'c:\Bestellungen_Data_1.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% ),
( NAME = Bestellungen_Data_2,
FILENAME = 'c:\Bestellungen_Data_2.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% )
LOG ON (
NAME = Bestellungen_Log,
FILENAME = 'c:\Bestellungen_Log.ldf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% )
Die Data Definition Language 16
Das letzte Beispiel legt die Datenbank mit mehreren in Dateigruppen organisierten Dateien für
die Datenbank und einer Datei für das Transaktionsprotokoll an:
CREATE DATABASE Bestellungen
ON
PRIMARY
( NAME = Bestellungen_Data_1,
FILENAME = 'c:\Bestellungen_Data_1.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% ),
FILEGROUP Bestellungen_Gruppe_1
( NAME = Bestellungen_Data_2,
FILENAME = 'c:\Bestellungen_Data_2.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% ),
( NAME = Bestellungen_Data_3,
FILENAME = 'c:\Bestellungen_Data_3.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% ),
FILEGROUP Bestellungen_Gruppe_2
( NAME = Bestellungen_Data_4,
FILENAME = 'c:\Bestellungen_Data_4.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% ),
( NAME = Bestellungen_Data_5,
FILENAME = 'c:\Bestellungen_Data_5.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% )
LOG ON (
NAME = Bestellungen_Log,
FILENAME = 'c:\Bestellungen_Log.ldf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% )
3
4*
* "
+
#$
In SQL/92 löschen Sie ein Schema über
DROP SCHEMA Name {RESTRICT | CASCADE};
Geben Sie RESTRICT an, können Sie das Schema nur dann löschen, wenn dieses keine Objekte
enthält. Mit CASCADE werden auch alle enthaltenen Objekte automatisch mit gelöscht.
Der SQL Server kennt keine DROP SCHEMA-Anweisung.
Eine Datenbank können Sie über
DROP DATABASE Datenbankname;
löschen.
Beachten Sie, dass die in Daten in den enthaltenen Objekten nach dem Löschen für
immer verloren sind!
Die Data Definition Language 17
0
Der SQL Server erweitert die DROP DATABASE-Anweisung lediglich darin, dass mehrere
Datenbanken gelöscht werden können:
DROP DATABASE Datenbankname [,...n]
3 3 &/
* "
+
#$
Das Modifizieren eines Schemas ist in SQL/92 scheinbar nicht möglich und wohl auch nicht
notwendig. Der Begriff „Modifizieren“ steht übrigens nicht für das Löschen, Ändern und
Anfügen von Schema-Objekten (was über die entsprechenden SQL-Anweisungen wie z. B.
CREATE TABLE möglich ist), sondern für die Veränderung der Einstellungen des Schemas.
0
Eine Datenbank über ALTER DATABASE modifizieren, d.h., die Größe und verschiedene
Einstellungen ändern. Die Syntax der ALTER DATABASE-Anweisung variiert sehr stark
zwischen den verschiedenen Datenbanksystemen und wird hier nur für den SQL Server
beschrieben:
ALTER DATABASE Datenbankname
{
ADD FILE Dateispezifikation [,...n] [TO FILEGROUP Dateigruppen_Name]
| ADD LOG FILE Dateispezifikation [,...n]
| REMOVE FILE Logischer_Dateiname
| ADD FILEGROUP Dateigruppen_Name
| REMOVE FILEGROUP Dateigruppen_Name
| MODIFY FILE Dateispezifikation
| MODIFY NAME = Neuer_Datenbankname
| MODIFY FILEGROUP Dateigruppen_Name
{Dateigruppen_Eigenschaft | NAME = Neuer_Name}
| SET Optionsspezifikation [,...n] [WITH Terminierung]
| COLLATE Sortierungsname
}
Das Argument Dateispezifikation bezieht sich auf die folgenden Angaben, die pro Datei
notwendig sind:
(NAME = Logischer_Dateiname
[, NEWNAME = Neuer_logischer_Dateiname]
[, FILENAME = 'Dateiname']
[, SIZE = Größe]
[, MAXSIZE = {Maximalgröße | UNLIMITED}]
[, FILEGROWTH = Größen_Increment] )
Das Argument Dateigruppe bezieht sich auf Angaben, die für Dateigruppen notwendig sind:
FILEGROUP Dateigruppen_Name Dateispezifikation [,...n]
Das Argument Optionsspezifikation spezifiziert die Optionen der Datenbank. Da dieses
Argument sehr komplex ist und wahrscheinlich eher selten genutzt wird (da diese Optionen
übersichtlicher im Enterprise Manager des SQL Servers definiert werden können), verzichte ich
hier auf eine nähere Beschreibung.
Die Data Definition Language 18
Mit ADD FILE können Sie die Datenbank um weitere Dateien erweitern und diese Dateien direkt
vorhandenen oder neuen Dateigruppen zuordnen. Das folgende Beispiel fügt der Datenbank
Bestellungen eine weitere Datei hinzu:
ALTER DATABASE Bestellungen
ADD FILE
( NAME = Bestellungen_Data_3,
FILENAME = 'c:\Bestellungen_Data_3.mdf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 15% );
Mit ADD LOG FILE können Sie neue Dateien für das Transaktionsprotokoll hinzufügen. REMOVE
FILE löscht eine Datei aus der Datenbank. So können Sie eine Datei z. B. aus einer Datenbank
entfernen:
ALTER DATABASE Bestellungen
REMOVE FILE Bestellungen_Data_3;
Mit ADD FILEGROUP können Sie neue Dateigruppen hinzufügen, mit REMOVE FILEGROUP
existierende löschen. Das Argument MODIFY FILE ermöglicht die Einstellungen (SIZE,
MAXSIZE oder FILEGROWTH) einer Datei zu verändern. Zur Veränderung müssen Sie den
logischen Dateinamen angeben. Sie können jeweils nur eine Einstellung verändern.
Das folgende Beispiel setzt die automatische Vergrößerung der Datei Bestellungen_Data_2 auf
20%:
ALTER DATABASE Bestellungen
MODIFY FILE
( NAME = Bestellungen_Data_2,
FILEGROWTH = 20% );
Wenn Sie die Größe verändern wollen (was nur notwendig ist, wenn die Datei nicht automatisch
vergrößert wird), können Sie nur einen Wert einstellen, der größer ist als die aktuelle Größe.
Mit MODIFY FILEGROUP können Sie die Optionen einer Dateigruppe einstellen. Mit dem
Argument READONLY schützen Sie die Dateigruppe vor Änderungen. Das Argument
READWRITE hebt diesen Schreibschutz wieder auf. Mit dem Argument DEFAULT wird Sie die
Dateigruppe zur Default-Dateigruppe. In der Default-Dateigruppe werden neue Tabellen und
Indizes angelegt, wenn beim Anlegen derselben keine spezielle Dateigruppe angegeben wird. Ist
keine spezielle Dateigruppe als Default-Dateigruppe definiert, verwendet der SQL Server die
primäre Dateigruppe als Default-Dateigruppe.
35
/")
#$
Laut Date und Darven (SQL - Der Standard) stellen Domänen „... einen absolut fundamentalen
Bestandteil des theoretischen Relationenmodells dar ...“. SQL/92 unterstützt Domänen jedoch
nur extrem schwach in Form der Spezifikation von Datentypen für Spalten. So können Sie z. B.
mit
CREATE DOMAIN ADDRESS varchar(80);
einen Datentyp definieren, den Sie beim Erstellen und Modifizieren von Tabellen verwenden
können:
CREATE TABLE Customers(
CustomerID int,
CompanyName ADDRESS,
City ADDRESS);
Die Data Definition Language 19
0
Der SQL Server unterstützt keine Domänen. Benutzerdefinierte Datentypen werden beim SQL
Server über die Stored Procedure sp_addtype angelegt:
sp_addtype ADDRESS, 'varchar(80)'
Beim Anlegen solcher Datentypen können Sie zudem festlegen, ob die Spalte später NULLWerte erlaubt oder nicht:
sp_addtype ADDRESS, 'varchar(80)', 'NOT NULL'
Löschen können Sie Benutzerdefinierte Datentypen im SQL Server über die Stored Procedure
sp_droptype.
36
0/ 7
#$
Das Anlegen von Tabellen geschieht über die CREATE TABLE-Anweisung. SQL/92 definiert
dazu die folgende Syntax:
CREATE TABLE Tabellenname (
{Spaltendefinition | Tabelleneinschränkung}
[,...n]
);
Der Tabellenname richtet sich nach der Namengebung im verwendeten Datenbanksystem. Die
Spaltendefinition definiert jeweils eine Spalte und kann mehrfach angegeben werden. Die
Spaltendefinition gliedert sich auf in:
Spaltenname {Spaltentyp | Domäne}
[DEFAULT {Defaultwert | Funktion | NULL}]
[Spalteneinschränkung]
Für jede Spalte geben Sie den Namen und den Datentyp oder die Domäne dieser Spalte an.
Optional können Sie einen Defaultwert angeben, der in diese Spalte für neue Datensätze
eingetragen wird. Voreinstellung hier ist der Wert NULL. Sie können jedoch auch jedes, zum
Datentyp der Spalte passende Literal oder eine Funktion angeben, die diesen Datentyp
zurückgibt.
Beispiel: Erzeugen einer einfachen Kundentabelle:
CREATE TABLE Customers (
CustomerID int DEFAULT 0,
CompanyName varchar(80),
PostCode int DEFAULT 47475,
City varchar(80) DEFAULT 'Kamp-Lintfort',
Street varchar(80) );
Die Data Definition Language 20
* )
-
Zunächst eine kurze Begriffsklärung: Eine „Einschränkung“ (engl. Constraint) definiert gewisse
Einschränkungen der für eine Tabellenspalte möglichen Werte. Diese Werte sind zwar zunächst
über den Datentyp der Spalte eingeschränkt, können über Constraints jedoch noch weiter
eingeschränkt werden. Zu den üblichen Einschränkungen gehören die folgenden:
Einschränkung
Bedeutung
PrimärschlüsselEinschränkungen
(Primary Key
Constraints)
Ein Primärschlüssel definiert eine oder mehrere Spalten einer Tabelle als
primärem Zugriffsschlüssel. Dieser ermöglicht einen eindeutigen Zugriff
auf die Datensätze der Tabelle. Primärschlüssel werden auch verwendet, um
Beziehungen zwischen Tabellen zu definieren. In der Mastertabelle ist dazu
ein Primärschlüssel definiert, der mit einem Fremdschlüssel in einer
Detailtabelle in Beziehung steht. Primärschlüssel werden häufig nur über
eine Spalte definiert, können jedoch auch aus mehreren Spalten bestehen.
Ein Primärschlüssel ist immer eindeutig indiziert. Damit können die Werte
im Primärschlüssel nur einmal in der Tabelle vorkommen.
Fremdschlüssel
(Foreign Key
Constraint)
Ein Fremdschlüssel definiert die Beziehung zwischen einer Mastertabelle
und einer Detailtabelle. Der Fremdschlüssel wird dabei immer in der
Detailtabelle angelegt.
EindeutigkeitsEinschränkung
(Unique Constraint)
Eine Eindeutigkeits-Einschränkung sorgt dafür, dass in die entsprechende
Spalte keine Werte mehrfach eingegeben werden (ohne dass diese Spalte
zum Primärschlüssel wird). Dasselbe können Sie in den meisten
Datenbanksystemen auch erreichen, indem Sie einen eindeutigen Index
über diese Spalten definieren (siehe bei »Indizes erstellen und löschen« in
Abschnitt 3.8). Beim SQL Server wird eine Eindeutigkeits-Einschränkung
sowieso eindeutig indiziert, weshalb ich hier keinen Unterschied zu einem
Index erkennen kann. SQL/92 kennt jedoch keine Indizes, womit
Eindeutigkeits-Einschränkungen hier eine Berechtigung besitzen.
Prüfeinschränkungen
(Check Constraints)
Mit einer Prüfeinschränkung sorgen Sie dafür, dass in einer Spalte nur
Werte eingegeben werden können, die einer anzugebenden Prüfbedingung
entsprechen. In einer Rabatt-Spalte können Sie mit einer Prüfbedingung z.
B. dafür sorgen, dass nur Zahlen zwischen 0 und 100 eingegeben werden
können.
Tabelle 3.1: Die üblichen Einschränkungen für Spalten
Einschränkungen für Spalten beziehen sich immer nur auf genau eine Spalte. Die weiter unten
beschriebenen Einschränkungen für Tabellen können sich dagegen auf mehr als eine Spalte
beziehen.
Das in der Spaltendefinition optionale Argument Spalteneinschränkung bezieht sich auf das
Erzeugen von Einschränkungen für diese Spalte. Die genaue SQL/92-Syntax dazu konnte ich
bisher nicht ermitteln. Die wahrscheinlichste Syntax für Spalteneinschränkungen ist:
[CONSTRAINT [Name]]
{
[PRIMARY KEY]
| [UNIQUE]
| [FOREIGN KEY REFERENCES Mastertabelle [(Spaltenliste)]]
| [CHECK (Prüfbedingung)]
}
Die Data Definition Language 21
Das folgende Beispiel erzeugt eine Kunden- und eine Bestellungen-Tabelle mit je einem
Primärschlüssel, einem Fremdschlüssel auf der Bestellungen-Tabelle auf der Spalte
CustomerID, einer Eindeutigkeits-Einschränkung auf dem Feld CompanyName, und einer
Prüfeinschränkung auf der Spalte PostCode:
CREATE TABLE Customers (
CustomerID int DEFAULT 0
CONSTRAINT PK_Customers PRIMARY KEY,
CompanyName varchar(80)
CONSTRAINT UQ_Customers_CompanyName UNIQUE,
PostCode int DEFAULT 47475
CONSTRAINT CK_Customers_Postcode
CHECK(PostCode >= 1000 AND PostCode <= 99999),
City varchar(80) DEFAULT 'Kamp-Lintfort',
Street varchar(80)
)
CREATE TABLE Orders (
OrderID int DEFAULT 0
CONSTRAINT PK_Orders PRIMARY KEY,
CustomerID int
CONSTRAINT FK_Orders_Customers
FOREIGN KEY REFERENCES Customers(CustomerID),
OrderDate char(10)
)
Wie Sie sehen, verwende ich in den Beispielen das Schlüsselwort CONSTRAINT
damit ich den Einschränkungen einen Namen geben kann. Wenn Sie den Namen
selbst angeben (und die Namensgebung damit nicht dem Datenbanksystem
überlassen) können Sie die Einschränkung später ohne Probleme löschen, aufheben
oder verändern.
* )
7
Im Gegensatz zu Spalteneinschränkungen beziehen sich Einschränkungen für die Tabelle auf
die gesamte Tabelle, können also mehrere Spalten referenzieren. Wenn Sie z. B. einen
Primärschlüssel aufbauen wollen, der über mehr als eine Spalte geht, können Sie diesen nur
über eine Tabelleneinschränkung erzeugen. Das optionale Argument Tabelleneinschränkung
gliedert sich dazu auf in:
{
[CONSTRAINT Name PRIMARY KEY (Spaltenliste)]
| [CONSTRAINT Name FOREIGN KEY (Spaltenliste)
REFERENCES Mastertabelle(Spaltenliste)]
| [CONSTRAINT Name CHECK(Prüfbedingung)]
}
Die Data Definition Language 22
Das bei den Spalteneinschränkungen beschriebene Beispiel sieht mit Tabelleneinschränkungen
so aus:
CREATE TABLE Customers (
CustomerID int DEFAULT 0,
CompanyName varchar(80),
PostCode int DEFAULT 47475,
City varchar(80) DEFAULT 'Kamp-Lintfort',
Street varchar(80),
CONSTRAINT PK_Customers PRIMARY KEY (CustomerID),
CONSTRAINT UK_Customers UNIQUE (CompanyName),
CONSTRAINT CK_Customers_Postcode
CHECK (PostCode > 1000 AND PostCode <= 99999)
)
CREATE TABLE Orders (
OrderID int DEFAULT 0,
CustomerID int,
OrderDate char(10),
CONSTRAINT PK_Orders PRIMARY KEY (OrderID),
CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
)
0/ 7
"
0
Die Syntax der CREATE TABLE-Anweisung basiert beim SQL Server auf der SQL/92-Syntax, ist
jedoch erweitert:
CREATE TABLE [{Datenbankname.[Besitzer]. | Besitzer.}]Tabellenname(
{
Spaltendefinition
| Spaltenname AS Ausdruck_für_eine_berechnete_Spalte
| Tabelleneinschränkung
}
[,...n])
[ON {Dateigruppe | DEFAULT} ]
[TEXTIMAGE_ON {Dateigruppe | DEFAULT} ]
Im Unterschied zu SQL/92 kann hier bei der Erzeugung der Besitzer der Tabelle angegeben
werden. Der Besitzer einer Tabelle besitzt immer alle Rechte auf dieser Tabelle. Per
Voreinstellung ist immer der Benutzer, der die Tabelle anlegt, der Besitzer. Ein weiterer
Unterschied ist, dass statt einer Spaltendefinition auch eine Berechnung für eine berechnete
Spalte angegeben werden kann.
Das folgende Beispiel erzeugt eine Tabelle für Bestelldetails mit einer berechneten Spalte für
die Detailsumme:
CREATE TABLE Order_Details (
ID int,
OrderID int,
ProductID int,
Quantity int,
UnitPrice money,
DetailSum AS Quantity * UnitPrice
)
Um diesen Beispiel zu testen, können Sie die folgenden SQL-Anweisungen verwenden:
INSERT INTO ORDER_DETAILS
(ID, OrderID, ProductID, Quantity, UnitPrice)
VALUES (1,1000,110,10,1.35)
SELECT * FROM Order_Details
Die Data Definition Language 23
Das Argument Spaltendefinition bezieht sich auf die folgenden Angaben:
Spaltenname Datentyp
[COLLATE Sortierungsname]
[ {
[DEFAULT Default_Literal]
| [IDENTITY [(Startwert, Schrittweite) [NOT FOR REPLICATION]]
} ]
[ROWGUIDCOL]
[Spalteneinschränkung]
Im Unterschied zu SQL/92 kann die Spalte beim SQL Server zu einer Identity-Spalte (Identity
Column) werden. Eine Identity-Spalte ist eine Spalte, die einen numerischen Datentyp besitzen
muss und vom Server bei jedem neuen Datensatz um einen einzustellenden Wert inkrementiert
wird. Sie können pro Tabelle nur eine Identity-Spalte definieren und nur solche Spalten
verwenden, die einen numerischen Datentyp besitzen, keine Nullwerte zulassen und keinen
Defaultwert besitzen. Die Identity-Spalte kann mit der Spalte für den Primärschlüssel identisch
sein. Der Option Startwert spezifiziert den Anfangswert für neue Datensätze. Schrittweite
definiert den Wert mit dem für jeden neuen Datensatz inkrementiert wird. Die Voreinstellung
für Startwert und Schrittweite ist jeweils 1.
Das folgende Beispiel erzeugt eine Tabelle Order_Details mit einer Identity-Spalte, deren Wert
bei 1000 beginnt und die mit 1 addiert wird:
CREATE TABLE Order_Details (
ID int IDENTITY(1000,1),
OrderID int,
ProductID int,
Quantity int,
UnitPrice money,
DetailSum AS Quantity * UnitPrice
)
Die Option ROWGUIDCOL besagt, dass die Spalte eine Spalte ist, die einen GUID8-Wert
speichern soll, der einen Datensatz ein-eindeutig identifizieren soll. GUID-Werte sind
hexadezimale Werte (wie z. B. der Wert BA2DE025-8467-11D3-9E43-00400548C419), die per
„intelligentem“ Zufallsgenerator mit Hilfe des aktuellen Datums, der Zeit, der ID der
Netzwerkkarte und aktuellen Registerinhalten erzeugt werden und die über die gesamte Welt
(bzw. über das gesamte „Universum“) eindeutig sind. GUID’s werden üblicherweise zur
Identifizierung gespeicherter Objekte verwendet. Mit GUID‘s können Sie auch viele Probleme
umgehen, die bei Identitätsspalten auftreten können, wenn mehrere Benutzer gleichzeitig neuen
Datensätze anlegen. Der SQL Server nutzt GUID-Spalten außerdem bei Replikationen.
Eine GUID-Spalte muss den Datentyp uniqueidentifier besitzen und sollte als Defaultwert
die Funktion NEWID() zugewiesen bekommen, die einen neuen GUID-Wert erzeugt:
CREATE TABLE Objects (
GUID uniqueidentifier ROWGUIDCOL DEFAULT NEWID(),
ObjectName varchar(80)
)
Testen können Sie diese Tabelle mit den folgenden Anweisungen:
INSERT INTO Objects (ObjectName) VALUES ('ObjectA')
INSERT INTO Objects (ObjectName) VALUES ('ObjectB')
SELECT * FROM Objects
8
Global Unique Identifier
Die Data Definition Language 24
-
* )
Das in der Spaltendefinition verwendete Argument Spalteneinschränkung definiert wie bei
SQL/92 eine Einschränkung für ein Datenfeld mit den folgenden Angaben:
[CONSTRAINT Einschränkungsname]
{
[{NULL | NOT NULL}]
| [{PRIMARY KEY | UNIQUE} [{CLUSTERED | NONCLUSTERED}]
[WITH FILLFACTOR = Füllfaktor] [ON {Dateigruppe | DEFAULT}]]
| [FOREIGN KEY REFERENCES Mastertabelle [(Spaltenliste)]
[ON DELETE {CASCADE | NO ACTION}]
[ON UPDATE {CASCADE | NO ACTION}]
[NOT FOR REPLICATION]]
| [CHECK [NOT FOR REPLICATION] (Prüfbedingung)]
}
Zusätzlich zu SQL/92 können Sie hier mit der Option NOT NULL festlegen, dass die Spalte keine
NULL-Werte erlaubt. Der Wert NULL steht für „Nichts“ und ist nicht zu verwechseln mit der
Zahl 0. Eine leere Spalte speichert immer einen NULL-Wert (Spalten mit ZeichenkettenDatentypen können daneben auch eine leere Zeichenkette speichern, die jedoch kein NULL-Wert
ist).
8 "
*
"
4*
+
Ab der Version 2000 erlaubt der SQL Server (endlich) Fremdschlüssel, die eine Löschoder/und Aktualisierungsweitergabe besitzen. Normale Fremdschlüssel erlauben das Löschen
von Master-Datensätzen und das Verändern des Primärschlüssels dieser Datensätze nicht, wenn
eine Detailtabelle den Primärschlüssel referenziert. Damit sichern Datenbanksysteme ab, dass
die über Fremdschlüssel aufgebauten Beziehungen nicht in einen inkonsistenten Zustand
versetzt werden können (was ja der eigentliche Sinn eines Fremdschlüssels ist).
Wenn Sie für eine Beziehung beim SQL Server nun über ON DELETE CASCADE die
Löschweitergabe einschalten, können Sie Master-Datensätze löschen, die in Detailtabellen
referenziert werden. In diesem Fall löscht der SQL Server allerdings automatisch und ohne
weitere Meldungen gleich auch alle Detaildatensätze.
Die Löschweitergabe sollten Sie nur sparsam und in Sonderfällen einsetzen. Wenn
Sie zwischen einer Kunden- und einer Bestellungen-Tabelle eine Beziehung mit
Löschweitergabe erstellen und einen Kunden löschen, wird kein Fehler generiert,
auch wenn die Kunden-Nummer in mehreren Bestellungen verwendet wird. Der
SQL Server löscht dann einfach auch alle Bestellungen, was in der Praxis zu
enormen Fehlern führt. So sind Umsatzauswertungen in diesem Fall z. B. nicht
mehr korrekt, da die Bestellungen der gelöschten Kunden fehlen.
Eine Löschweitergabe wäre aber z. B. zwischen einer Bestellungen- und der BestelldetailsTabelle sinnvoll: Wird eine Bestellung gelöscht, ist es sinnvoll, gleich auch die Bestelldetails zu
löschen.
Die Aktualisierungsweitergabe, die Sie mit ON UPDATE CASCADE einschalten, ermöglicht, dass
der Primärschlüsselwert einer Mastertabelle geändert werden kann, auch wenn dieser bereits in
Detailtabellen verwendet wird. Der SQL Server gibt die Aktualisierung einfach an alle
Detailtabellen weiter. Wenn Sie z. B. ermöglichen wollen, dass eine Kunden-Nummer geändert
werden kann, müssen Sie alle Fremdschlüsseln, die die Kunden-Nummer referenzieren, mit
Aktualisierungsweitergabe definieren.
Die Data Definition Language 25
Das folgende Beispiel erzeugt je eine Tabelle Bestellungen und Bestelldetails und setzt diese
Tabellen über das Feld Bestell_Nr in Beziehung. Die Beziehung wird so definiert, dass die
Aktualisierungs- und die Löschweitergabe eingeschaltet ist:
CREATE TABLE Bestellungen (
Bestell_Nr int PRIMARY KEY NOT NULL,
Datum datetime DEFAULT GETDATE() NOT NULL,
Kunden_Nr int NOT NULL)
CREATE TABLE Bestelldetails (
ID int IDENTITY PRIMARY KEY NOT NULL,
Bestell_Nr int NOT NULL
CONSTRAINT FK_Bestellungen FOREIGN KEY
REFERENCES Bestellungen(Bestell_Nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
Artikel_Nr int NOT NULL,
Anzahl int NOT NULL DEFAULT 0)
Sie können nun z. B. eine Bestellung mit zwei Bestelldetails hinzufügen:
INSERT INTO Bestellungen
(Bestell_Nr, Kunden_Nr)
VALUES(1, 1001)
INSERT INTO Bestelldetails
(Bestell_Nr, Artikel_Nr, Anzahl)
VALUES(1, 11588, 100)
INSERT INTO Bestelldetails
(Bestell_Nr, Artikel_Nr, Anzahl)
VALUES(1, 18941, 50)
Da die Aktualisierungsweitergabe eingeschaltet ist, können Sie die Bestellnummer nachträglich
ändern (was ansonsten zu einem Fehler führen würde):
UPDATE Bestellungen SET Bestell_Nr = 200200001 WHERE Bestell_Nr = 1
Wenn Sie nun die Bestelldetails abfragen:
SELECT * FROM Bestelldetails
erkennen Sie, dass der SQL Server die Bestellnummer in diesen auf den neuen Wert gesetzt hat.
Da auch die Löschweitergabe eingeschaltet ist, können Sie die Bestellung auch löschen:
DELETE FROM BEstellungen WHERE Bestell_Nr = 200200001
Der SQL Server meldet, dass ein Datensatz gelöscht wurde und verschweigt damit, dass die
zwei Bestelldetail-Datensätze im Hintergrund ebenfalls gelöscht wurden. Wenn Sie die
Bestelldetails nun noch einmal abfragen, stellen Sie fest, dass die Detaildatensätze im
Hintergrund vom SQL Server gelöscht wurden.
* )
"
7
Das Argument Tabelleneinschränkung definiert wie bei SQL/92 eine Einschränkung für die
gesamte Tabelle und ist ebenfalls erweitert:
[CONSTRAINT Einschränkungsname]
{
{PRIMARY KEY | UNIQUE} [{CLUSTERED | NONCLUSTERED}] (Spaltenliste)
[WITH FILLFACTOR = Füllfaktor] [ON {Dateigruppe | DEFAULT}]
| FOREIGN KEY (Spaltenliste)
REFERENCES Mastertabelle [(Spaltenliste)]
[ON DELETE { CASCADE | NO ACTION }]
[ON UPDATE { CASCADE | NO ACTION }]
[NOT FOR REPLICATION]
| CHECK [NOT FOR REPLICATION](Prüfbedingung)
}
Die Data Definition Language 26
Beim Erzeugen von Primärschlüsseln können Sie mit der Option CLUSTERED einen gruppierten
Index erzeugen. Mit der Option WITH FILLFACTOR können Sie den Füllfaktor einstellen.
Gruppierte Indizes und den Füllfaktor erläutere ich im Artikel »SQL Server 2000«. Mit der
Option ON Dateigruppe können Sie den Primärschlüssel in einer bestimmten Dateigruppe
anlegen.
39 7
"/
Bei bestehenden Tabellen können Sie über die ALTER TABLE-Anweisung
•
Spalten hinzufügen, löschen und ändern,
•
Einschränkungen hinzufügen und löschen.
Die einzelnen Varianten werden der Übersicht wegen im Folgenden einzeln erläutert, können
jedoch (in logisch korrekter Folge) auch in einer ALTER TABLE-Anweisung stehen.
39
:
0/
-
#$
Sie fügen Spalten in SQL/92 mit
ALTER TABLE Tabellenname
ADD [COLUMN] Spaltendefinition
[,...n]
hinzu.
Das folgende Beispiel fügt der Customers-Tabelle die Spalte Discount hinzu:
ALTER TABLE Customers
ADD COLUMN Discount smallint DEFAULT 0
0
Der SQL Server kann mit dem Schlüsselwort COLUMN nichts anfangen, ermöglicht jedoch
zudem das Anfügen von berechneten Spalten:
ALTER TABLE Tabellenname ADD
{
Spaltendefinition
| Spaltenname AS Ausdruck für eine berechnete Spalte
} [,...n]
Die Data Definition Language 27
39
4*
0/
-
#$
Spalten löschen Sie in SQL/92 mit
ALTER TABLE Tabellenname
DROP COLUMN Spaltenname {RESTRICT | CASCADE}
[,...n]
Mit der Option RESTRICT können Sie eine Spalte nur dann löschen, wenn keine Einschränkung
auf dieser Spalte definiert ist. Mit der Option CASCADE werden alle Einschränkungen
automatisch mit gelöscht.
Beispiel: Löschen der Spalte Discount aus der Customers-Tabelle:
ALTER TABLE Customers
DROP COLUMN Discount CASCADE;
0
Der SQL Server kennt die Optionen RESTRICT und CASCADE nicht und verhält sich, als ob
RESTRICT angegeben ist.
393
;
0/
-
#$
SQL/92 erlaubt beim Ändern einer Spalte lediglich das Löschen und Neudefinieren des
Defaultwertes:
ALTER TABLE Tabellenname
ALTER [COLUMN] Spaltenname
{SET {Defaultwert | Funktion | NULL} | DROP DEFAULT}
[,...n]
0
Der SQL Server ermöglicht dagegen die Änderung des Datentyps, der Sortierfolge und der
NULL-Option und das Anfügen bzw. Löschen der ROWGUID-Einstellung:
ALTER TABLE Tabellenname
ALTER COLUMN Spaltenname
{
Neuer Datentyp [(Genauigkeit[, Dezimalstellen])]
[COLLATE Sortierungsname]
[NULL | NOT NULL]
| {ADD | DROP} ROWGUIDCOL
}
[,...n]
Der Defaultwert einer Spalte wird über das Ändern der entsprechenden DefaultEinschränkung realisiert.
Das folgende Beispiel ändert den Datentyp der Discount-Spalte der Customers-Tabelle:
ALTER TABLE Customers
ALTER COLUMN Discount tinyint NOT NULL
Die Data Definition Language 28
Das Ändern von Spalten funktioniert beim SQL Server mit SQL nur dann, wenn keine
Einschränkungen auf dieser Spalte definiert sind. Der Enterprise Manager ist in der Lage, auch
solche Spalten ohne Probleme zu ändern.
395
* )
#$
In SQL/92 fügen Sie eine Einschränkung folgendermaßen hinzu:
ALTER TABLE Tabellenname
ADD
{
[CONSTRAINT Name PRIMARY KEY (Spaltenliste)]
| [CONSTRAINT Name FOREIGN KEY (Spaltenliste)
REFERENCES Mastertabelle(Spaltenliste)]
| [CONSTRAINT Name CHECK(Prüfbedingung)]
}
[,...n]
0
Beim SQL Server verwenden Sie die folgende Syntax:
ALTER TABLE Tabellenname
[{WITH CHECK | WITH NOCHECK}]
ADD Tabelleneinschränkung
[,...n]
Einschränkungen bauen sich dabei genauso auf wie bei der Erzeugung von Tabellen. Die WITH
NOCHECK-Option bewirkt, dass die Einschränkung zunächst deaktiviert ist. Solche
Einschränkungen können Sie später über
ALTER TABLE Tabellenname
CHECK Einschränkungsname
aktivieren.
Das folgende Beispiel fügt der Customers-Tabelle eine Prüfeinschränkung an:
ALTER TABLE Customers
ADD CONSTRAINT CK_Discount CHECK (Discount >= 0 AND Discount <= 100)
396
* )
4*
Das Löschen einer Einschränkung geschieht in SQL/92 und beim SQL Server über
ALTER TABLE Tabellenname
DROP CONSTRAINT Einschränkungsname
[, ...n]
Beispiel: Löschen der zuvor erzeugten Prüfeinschränkung auf der Customers-Tabelle:
ALTER TABLE Customers
DROP CONSTRAINT DF_Discount
Die Data Definition Language 29
399
* )
0
"
0
0
Beim SQL Server können Sie Einschränkungen deaktivieren, was häufig eine große Hilfe ist,
wenn Sie Daten importieren müssen, die nicht den Regeln der Einschränkung entsprechen. Sie
deaktivieren eine Einschränkung über
ALTER TABLE Tabellenname
NOCHECK CONSTRAINT Einschränkungsname
Das Aktivieren geschieht über die CHECK-Option:
ALTER TABLE Tabellenname
CHECK CONSTRAINT Einschränkungsname
Beispiel: Deaktivieren der Einschränkung auf der Discount-Spalte, um Datensätze mit
ungültigem Rabatt importieren zu können (wird hier nur simuliert), importieren dieser
Datensätze und wiedereinschalten der Einschränkung:
ALTER TABLE Customers NOCHECK CONSTRAINT DF_Discount
INSERT INTO Customers VALUES (1001, 'Panzerknacker AG', 12345,
'Entenhausen', 'Entenweg 1', 101)
ALTER TABLE Customers CHECK CONSTRAINT DF_Discount
INSERT INTO Customers VALUES (1002, 'Trillians Galaxy Shop, 99999,
'Galaxy', 'No Street 0', 101)
Die erste INSERT-Anweisung erzeugt keinen Fehler, weil die Einschränkung deaktiviert ist. Die
zweite INSERT-Anweisung erzeugt dagegen einen Fehler und wird nicht ausgeführt.
3< 7
4*
#$
Das Löschen einer Tabelle geschieht in SQL/92 recht einfach über
DROP TABLE Tabellenname {RESTRICT | CASCADE}
Geben Sie RESTRICT an, wird DROP TABLE scheitern, wenn die Tabelle in einer Sicht
verwendet wird oder wenn die Tabelle von Fremdschlüsseln auf anderen Tabellen referenziert
wird. Wenn Sie CASCADE angeben, werden alle abhängigen Sichten und Fremdschlüssel
automatisch mit gelöscht.
0
Der SQL Server kennt die Optionen RESTRICT und CASCADE nicht und arbeitet immer, als
wenn Sie RESTRICT angeben.
Die Data Definition Language 30
3=
4*
3=
Indizes beschleunigen Abfragen, wenn die im Index enthaltenen Felder in WHERE-, ORDER BY-,
oder GROUP BY-Klauseln oder als Fremdschlüssel in Joins verwendet werden oder wenn mit
anderen Techniken (z. B. mit der Seek-Methode in ADO) in diesen Spalten gesucht wird. Ein
Index wird in der Regel in einem balancierten Baum gespeichert. Der SQL Server speichert für
normale (nicht gruppierte) Indizes für jeden Datensatz ein Blatt im Baum mit den
Schlüsselwerten und einem Verweis auf den Datensatz. Die Suche in einem balancierten Baum
ist wesentlich schneller als die sequentielle Suche in den Datensätzen, die ohne Index notwendig
wäre.
Die zur Erzeugung von Indizes in vielen SQL-Büchern beschriebene CREATE INDEXAnweisung scheint gar kein Bestandteil von SQL/92 zu sein (jedenfalls ist weder diese
Anweisung noch die Verwendung des Begriffs „Index“ im Standard-Werk »SQL - Der
Standard«
zu
finden.
Im
Internet
ist
unter
www.se.postgressql.org/docs/postgres/sql-createindex.htm
(Beschreibung des SQL-Dialekts von Postgres) nachzulesen, dass SQL/92 keine CREATE
INDEX-Anweisung kennt).
"
Die allgemeine Form der CREATE INDEX-Anweisung ist (nach dem Buch »The practical SQL
Handbook«):
CREATE [UNIQUE] INDEX Indexname
ON Tabellenname (Spaltenliste)
Damit können Sie einen mehrdeutigen oder eindeutigen Index auf einer oder mehreren Spalten
erzeugen. Ein eindeutiger Index lässt in den Spalten des Index nur eindeutige Werte zu. Ein
eindeutiger Index auf der Spalte CompanyName z. B. lässt nicht zu, dass in zwei Datensätzen
derselbe Firmenname gespeichert wird. Wird ein Index über mehrere Spalten definiert, bezieht
sich die Eindeutigkeit auf die Summe der Spalten.
Das folgende Beispiel erzeugt einen Index auf den Spalten CompanyName und City der
Customers-Tabelle
CREATE UNIQUE INDEX IX_Customers_CompanyName_City
ON Customers (CompanyName, City)
Dieses Beispiel bewirkt, dass die Spalten CompanyName und City zusammen in einem
eindeutigen Index definiert sind. Nun kann in dieser Tabelle ein Kunde „Panzerknacker AG“ in
„Entenhausen“ und ein Kunde „Panzerknacker AG“ in „Berlin“ gespeichert werden, aber keine
zwei Kunden „Panzerknacker AG“ in „Entenhausen“.
Für binäre Spalten (dazu gehören auch Memo-Spalten, die große Texte speichern können) kann
in der Regel kein Index vergeben werden.
Die Data Definition Language 31
0
Der SQL Server kennt eine erweiterte Syntaxvariante zur Erzeugung von Indizes:
CREATE [UNIQUE] [{CLUSTERED | NONCLUSTERED}] INDEX Indexname
ON Tabellenname (Spaltenliste)
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = Füllfaktor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
[[,] SORT_IN_TEMPDB]
]
[ON Dateigruppe]
Die
Optionen CLUSTERED und NONCLUSTERED, der Füllfaktor und die Option
IGNORE_DUP_KEY sind im Artikel » SQL Server 2000« beschrieben. Mit der Option
DROP_EXISTING können Sie einen bereits vorhandenen Index gleichen Namens überschreiben
(was sonst nicht möglich ist). Mit ON Dateigruppe können Sie einen Index in einer bestimmten
Dateigruppe erzeugen.
3=
4*
>
Mit DROP INDEX Tabellenname.Indexname können Sie einen Index löschen.
Die Data Definition Language 32
5
&
-
/
Die Data Manipulation Language besteht aus den folgenden Teilbereichen:
•
Anfügen und Löschen von Datensätzen
•
Ändern von Datensätzen
•
Abfragen von Datensätzen
Das Anfügen, Löschen und Ändern von Datensätzen wird zuerst erläutert, weil dies die logische
Reihenfolge bei der Erstellung von Datenbanken darstellt. Ich würde eigentlich lieber zuerst das
Abfragen von Datensätzen behandeln, weil die in Abfragen oft verwendete WHERE-Klausel und
andere Features, wie z. B. Joins auch beim Anfügen, Löschen und Ändern von Datensätzen
verwendet werden, aber ich will die logische Reihenfolge nicht durcheinanderbringen
(schließlich muss man ja nach dem Erstellen einer Datenbank diese zunächst füllen, damit
Abfragen überhaupt möglich sind).
"
" 7
,
-
Die Beispiele zum Anfügen, Ändern und Löschen von Daten verändern natürlich die
Datenbank. Wenn Sie solche Beispiele nur testen wollen ohne die Daten tatsächlich zu
verändern, können Sie diese einfach in eine Transaktion einschließen. Sie starten eine
Transaktion mit BEGIN TRANSACTION und verwerfen alle Änderungen mit ROLLBACK
TRANSACTION. Wenn Sie die Anweisungen also in diese Anweisungen einschließen, werden
die Änderungen nicht wirklich vorgenommen. Zu Überprüfung, ob die Änderungen an der
Datenbank ausgeführt würden (wenn Sie keine Transaktion verwenden oder die Transaktion mit
COMMIT TRANSACTION abschließen), können Sie vor dem Verwerfen der Transaktion einfach
eine SELECT-Anweisung einfügen.
Das folgende Beispiel aktualisiert Artikelpreise in einer Transaktion, die am Ende verworfen
wird:
BEGIN TRANSACTION
UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE SupplierID = 1
SELECT * FROM Products
ROLLBACK TRANSACTION
Die SELECT-Anweisung, die innerhalb der Transaktion ausgeführt wird, zeigt die geänderten
Daten. Nach dem Rollback besitzen die Daten dann aber wieder die alten Werte.
5
0/
)
#$
Datensätze werden in SQL/92 mit der INSERT-Anweisung angefügt. Mit diese Anweisung
können Sie einzelne Datensätze anfügen, indem Sie die Werte der Spalten für den Datensatz
angeben, oder Sie können alle über eine SELECT-Anweisung ausgewählten Datensätze anderer
Tabellen an eine Tabelle oder eine Sicht9 anfügen.
9
Eine Sicht (engl. View) ist eine in der Datenbank gespeicherte Abfrage, die eine bestimmte, über das
SELECT-Statement definierte Sicht auf die Basistabelle(n) bietet. Über eine Sicht können Sie komplexe
Abfragen vor Benutzern verbergen und Benutzern spezifische Rechte auf den Basisdaten vergeben.
Datensätze können Sie nicht nur an Tabellen, sondern auch an Sichten anfügen. Sichten sind weiter unten
näher beschrieben.
Die Data Manipulation Language 33
Mit
INSERT INTO {Tabelle | Sicht} [(Spaltenliste)] VALUES (Wertliste)
fügen Sie einen einzelnen Datensatz an eine Tabelle oder eine Sicht an. Wenn alle Spalten
gefüllt werden, kann die Spaltenliste entfallen. Die Werte der Spalten werden dann der Reihe
nach in der Wertliste eingetragen. Wenn Sie nicht alle Spalten füllen wollen, können Sie zum
einen die Spaltenliste weglassen und an Stelle der auszulassenden Spalte den Wert NULL oder
die Option DEFAULT (für den Defaultwert der Spalte) einsetzen, oder Sie geben die zu füllenden
Spalten in der Spaltenliste an.
Das folgende Beispiel fügt einen Datensatz an die Customers-Tabelle unter Angabe aller
Spaltenwerte an:
INSERT INTO Customers
VALUES (1001, 'Panzerknacker AG', 12345, 'Entenhausen', 'Entenweg 1')
Soll im Beispiel die Spalte PostCode ausgelassen werden, können Sie das DEFAULTSchlüsselwort verwenden um die Spalte mit dem Defaultwert der Tabelle zu füllen:
INSERT INTO Customers
VALUES (1002, 'Trillians Galaxy Shop', DEFAULT, 'Galaxy', 'No Street 0')
Besonders dann, wenn Sie nicht wissen, in welcher Reihenfolge die Spalten definiert sind,
sollten Sie die die Variante mit Angabe der Spaltennamen vorziehen, da Sie hier die Spalten in
beliebiger Reihenfolge angeben können:
INSERT INTO Customers
(CustomerID, CompanyName, City, Street)
VALUES (1002, 'Trillians Galaxy Shop', 'Galaxy', 'No Street 0')
Mit
INSERT INTO {Tabelle | Sicht}
[(Spaltenliste)]
SELECT-Anweisung
fügen Sie alle in der SELECT-Anweisung ausgewählten Datensätze an eine Tabelle an. Die in
der SELECT-Anweisung ausgewählten Spalten müssen mit den Spalten der Tabelle bzw. den in
der Spaltenliste angegebenen Spalten im Datentyp identisch bzw. konvertierbar sein. Der Name
der Spalten in der SELECT-Anweisung spielt allerdings keine Rolle. Die SELECT-Anweisung ist
in Abschnitt 4.4 ausführlich beschrieben und wird hier nur in einfacher Form dargestellt.
Beachten Sie, dass Sie auch sehr komplexe SELECT-Anweisungen (wie z. B. solche mit
Gruppierungen und Joins) verwenden können, um Datensätze anzufügen.
Die folgende Anweisung fügt alle Lieferanten (Suppliers), die aus den USA stammen, an die
Kundentabelle an:
INSERT INTO Customers
SELECT SupplierID, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, Phone, Fax
FROM Suppliers
WHERE Country = 'USA'
Die explizite Angabe der Spalten aus der Suppliers-Tabelle ist hier notwendig, da diese
zusätzlich noch die Spalte Homepage enthält. Die folgende Anweisung
INSERT INTO Customers
SELECT * FROM Suppliers
WHERE Country = 'USA'
scheitert beim SQL Server mit dem Fehler
Server: Nachr.-Nr. 213, Schweregrad 16, Status 5, Zeile 1
INSERT-Fehler: Spaltenname oder Anzahl der übergebenen Werte
entspricht nicht der Tabellendefinition.
Die Data Manipulation Language 34
Der Stern (*) in der SELECT-Anweisung steht für „Alle Spalten“.
Das folgende, etwas komplexere Beispiel schreibt die Jahresumsätze aller Kunden in eine neue
Tabelle. Das Beispiel setzt bereits Gruppierungen und Joins ein, die erst im Abschnitt 4.4.13
erläutert werden:
CREATE TABLE Sales (
Year int,
CustomerID varchar(10),
Sales float)
INSERT INTO Sales
SELECT YEAR(OrderDate), CustomerID, SUM(UnitPrice * Quantity)
FROM [Order Details] INNER JOIN Orders
ON [Order Details].OrderID = Orders.OrderID
GROUP BY YEAR(OrderDate), CustomerID
ORDER BY YEAR(OrderDate), CustomerID
Dieses (SQL Server-) Beispiel erzeugt eine Tabelle Sales, die mit den Summen der Umsätze
jedes Kunden für je ein Jahr gefüllt wird. Die Summen werden über eine Gruppierung errechnet.
Die Umsätze sind in der Tabelle Order Details gespeichert, die Kunden-ID in der Tabelle
Orders, weswegen ein Join über diese beiden Tabelle notwendig ist.
Der SQL Server erlaubt (leider) Bezeichner mit Leerzeichen (die in den meisten
Datenbanksystemen nicht erlaubt sind). Wenn solche Bezeichner in SQLAnweisungen verwendet werden, müssen diese von eckigen Klammern
umschlossen werden (die hier ausnahmsweise nicht für optionale Elemente stehen).
0
Die INSERT-Anweisung beim SQL Server ist sehr komplex und kann an dieser Stelle nicht
ausführlich erläutert werden. Zunächst die (wie so oft verwirrende) Syntax:
INSERT [INTO]
{
Tabelle [WITH (Tabellenhinweise [...n])]
| Sicht
| Datensatzgruppen_Funktion
}
{
[(Spaltenliste)]
{
VALUES ({DEFAULT | NULL | Ausdruck } [,...n])
| Abgeleitete_Tabelle
| SELECT-Anweisung
}
| DEFAULT VALUES
}
Neben Tabellen und Sichten können Sie beim SQL Server Datensätze noch in Tabellen anfügen,
die in entfernten Datenbanken gespeichert sind. Dazu geben Sie im Argument
Datensatzgruppen_Funktion eine TSQL10-Funktion an, die eine Datensatzgruppe zurückgibt.
Sie können dazu die Funktionen OPENQUERY verwenden, die eine Abfrage auf einem
verknüpften entfernten Server ausführt, oder die Funktion OPENROWSET, die eine Abfrage auf
einer beliebigen OLE-DB11-Datenquelle ausführt. Besonders die Variante mit der OPENROWSETFunktion ist interessant, wenn Sie Daten in andere Datenbanken exportieren müssen (was
allerdings auch mit dem Enterprise Manager möglich ist). Dazu sollten Sie sich allerdings ein
wenig mit OLE-DB auskennen. An dieser Stelle muss ein (funktionierendes!) Beispiel
10
TSQL (Transact SQL) ist die erweiterte SQL-Syntax des SQL Servers
11
OLE DB ist der aktuelle Microsoft Standard zum Zugriff auf Datenbanken. Der SQL Server selbst
basiert auf OLE DB.
Die Data Manipulation Language 35
ausreichen: In der Tabelle Orders der Northwind-Datenbank eines anderen SQL Servers soll ein
Datensatz angefügt werden:
INSERT INTO
OPENROWSET('SQLOLEDB', 'Arthur';'sa';'', 'SELECT * FROM
Northwind.dbo.Orders')
(CustomerID, EmployeeID, OrderDate)
VALUES ('ALFKI', 1, '19991231')
Dieses Beispiel verwendet den OLE DB-Provider SQLOLEDB, der den Zugriff auf SQL ServerDatenbanken realisiert und fügt an die Tabelle Orders der Datenbank Northwind im SQL Server
Arthur einen Datensatz an. Als Benutzer wird sa ohne Passwort angegeben.
Das Argument Tabellenhinweise gliedert sich auf in:
{ INDEX(index_val [,...n])
| FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
Diese Optionen sind recht gut in der TSQL-Hilfe bei der FROM-Klausel beschrieben und
betreffen den Isolations-Level bei Transaktionen, das Sperren von Datensätzen etc.
Wenn Sie statt der Spaltenliste das Argument DEFAULT VALUES angeben, wird der neue
Datensatz mit den Defaultwerten für die einzelnen Spalten gefüllt.
5
4*
0/
)
#$
In SQL/92 ist das Löschen von Datensätzen recht einfach:
DELETE FROM {Tabelle | Sicht} [WHERE Bedingung]
Geben Sie keine Bedingung an, werden alle Datensätze gelöscht. Wenn Sie nur bestimmte
Datensätze löschen wollen, verwenden Sie eine WHERE-Klausel, die die zu löschenden
Datensätze einschränkt. Die WHERE-Klausel wird in Abschnitt 4.4.5 ausführlich beschrieben und
hier nur an einem einfachen Beispiel erläutert:
Die folgende Anweisung löscht alle Kunden aus Berlin:
DELETE FROM Customers WHERE City = 'Berlin'
Wenn die Datenbank referentielle Integrität unterstützt, können keine Datensätze aus
Mastertabellen gelöscht werden, deren Schlüsselwerte in Detailtabellen vorkommen. Das obige
Beispiel schlägt z. B. fehl, weil Kunden aus Berlin in der Tabelle Orders vorkommen.
Die WHERE-Klausel kann (natürlich) auch Unterabfragen enthalten, wie das folgende Beispiel
zeigt, das alle Bestelldetails der Bestellungen von 1996 löscht:
DELETE FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM Orders WHERE YEAR(OrderDate) = 1996)
Wenn Sie z. B. Datensätze aus zwei über mehrere Datenfelder verknüpften Tabellen löschen
wollen, können Sie keine einfache DELETE-Anweisung einsetzen. Sind die Tabellen nur über
ein Datenfeld miteinander verknüpft, ist das Löschen der Datensätze aus der Detailtabelle und
der Mastertabelle recht einfach.
Die Data Manipulation Language 36
In der Northwind-Datenbank würden Sie alle Bestellungen des Kunden mit dem Kunden-Code
"ALFKI" z. B. folgendermaßen löschen:
DELETE FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM Orders WHERE CustomerID = "ALFKI")
DELETE FROM Orders WHERE CustomerID = "ALFKI"
0
Der SQL Server erweitert die DELETE-Anweisung um einige komplexe Features. Ich will die
komplexe Syntax hier nicht beschreiben und verweise dazu auf die TSQL-Hilfe.
Interessant ist, dass Sie beim SQL Server auch Abfragen einsetzen können, die mehrere
Tabellen über Joins miteinander in Beziehung setzen. Da Sie jedoch immer nur aus einer
Tabelle löschen können, verwenden Sie eine erweiterte Syntaxvariante:
DELETE {Tabelle1 | Sicht1}
[FROM {Tabelle1 | Sicht1}
{INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN
{Tabelle2 | Sicht2} ON
{Tabelle1 | Sicht1}.Schlüsselspalte = {Tabelle2 | Sicht2}.Schlüsselspalte]
Joins werden in Abschnitt 4.4.13 ausführlich erläutert.
Mit dieser Syntax löschen Sie aus Tabelle1 alle Datensätze, die der Bedingung entsprechen. Das
Besondere dabei ist die Verwendung eines Joins, damit Sie auch Spalten in der Bedingung
aufnehmen können, die in anderen verknüpften Tabellen gespeichert sind.
Diese Variante wird zwar selten eingesetzt, ist aber oft die einzige Möglichkeit, Datensätze aus
verknüpften Tabellen zu löschen. Wären die Tabellen Order Details und Orders z. B. über mehr
als ein Datenfeld miteinander verknüpft, wäre die Abfrage mit dem IN-Operator nicht mehr
möglich12.
12
Der theoretische Ansatz, alle Bestellungen des Kunden mit dem Kunden-Code "ALFKI" aus dem
unteren Beispiel über
DELETE FROM Bestelldetails
WHERE (Lieferschein_Nr IN (SELECT Lieferschein_Nr FROM Bestellungen
WHERE Kunden_Nr = "ALFKI")) AND (Lieferschein_Jahr IN (SELECT
Lieferschein_Jahr FROM Bestellungen WHERE Kunden_Nr = "ALFKI"))
zu löschen, würde dazu führen, dass auch Bestellungen anderer Kunden gelöscht werden, nämlich die, bei
denen eine Lieferschein-Nummer-/Lieferschein-Jahr-Kombination aus den beiden Listen zufällig zutrifft.
Wenn z.B. für den Kunden "ALFKI" zwei Bestellungen mit der Kombination 1000-98 und 1001-99
gespeichert sind und für den Kunden "ANATR" sind zwei Bestellungen mit der Kombination 1001-98
und 1002-99 gespeichert, wird die Bestellung 1001-98 dieses Kunden gelöscht, weil die LieferscheinNummer und das Lieferschein-Jahr in den über die SELECT-Statements erzeugten Listen vorkommen.
Die Data Manipulation Language 37
Eine, der Northwind-Datenbank ähnliche Datenbank könnte z. B. aufgebaut sein wie in
Abbildung 2).
Abbildung 2: Bestelldatenbank mit Referenz über zwei Datenfelder
Hier müssen Sie die erweiterte Syntax der DELETE-Anweisung mit einem JOIN verwenden,
wenn Sie alle Bestellungen des Jahres 1996 löschen wollen:
Beispiel: Löschen der Bestelldetails des Jahres 1996:
DELETE Bestelldetails
FROM Bestelldetails INNER JOIN Bestellungen
ON (Bestelldetails.Lieferschein_Nr = Bestellungen.Lieferschein_Nr
AND Bestelldetails.Lieferschein_Jahr = Bestellungen.Lieferschein_Jahr)
WHERE YEAR(Bestellungen.Rechnungsdatum) = 1996
Beispiel: Löschen der Bestellungen des Jahres 1996:
DELETE FROM Bestellungen
WHERE YEAR(Bestellungen.Rechnungsdatum) = 1996
53 ;
0/
)
#$
Die Werte einzelner Spalten können Sie in SQL/92 mit der UPDATE-Anweisung ändern:
UPDATE {Tabelle | Sicht}
SET Spalte = {Ausdruck | NULL | DEFAULT} [,...n]
[WHERE Bedingung]
Die zu ändernden Datensätze werden wie beim Löschen über eine WHERE-Klausel
eingeschränkt. Der Ausdruck für den neuen Wert muss lediglich einen für die Spalte gültigen
Datentyp enthalten und kann Konstanten, Berechnungen und Funktionen enthalten.
Beispiel: Erhöhen der Artikelpreise der Artikel, die vom Lieferanten mit der ID 1 geliefert
werden, um 10%:
UPDATE Products SET UnitPrice = UnitPrice * 1.1
WHERE SupplierID = 1
Sie können in einem Schritt gleich mehrere Spalten ändern.
Beispiel: Ändern des Lieferanten von 1 auf 2 mit gleichzeitiger Erhöhung der Artikelpreise um
10%:
UPDATE Products SET SupplierID = 2, UnitPrice = UnitPrice * 1.1
WHERE SupplierID = 1
Etwas schwieriger ist das Aktualisieren von Spalten nach den Daten aus einer anderen Tabelle.
Wenn z. B. alle Artikelpreise der Tabelle Order Details auf den aktuellen Stand gebracht
Die Data Manipulation Language 38
werden sollen (z. B. nach einer Änderung der Artikelpreise), können Sie eine Unterabfrage
verwenden:
UPDATE [Order Details]
SET UnitPrice = (SELECT UnitPrice FROM Products
WHERE Products.ProductID = [Order Details].ProductID)
Die Unterabfrage wird für jeden Datensatz der Order Details-Tabelle ausgeführt und ergibt
genau einen Wert, nämlich den aktuellen Einzelpreis des Artikels.
Anmerkung: In der Praxis können Sie solche Abfragen natürlich nur für Bestellungen
ausführen, für die noch keine Rechnung gedruckt wurde. Die Tabelle Orders sollte dazu eine
zusätzliche Spalte Bill_Printed oder ähnlich besitzen, die Sie in die Abfrage einbeziehen
können. Die Abfrage oben würde dann noch komplexer werden. Dazu ein ähnlich gelagertes
Beispiel: Anpassen nur der Einzelpreise der Bestellungen des Kunden ALFKI:
UPDATE [Order Details]
SET UnitPrice = (SELECT UnitPrice FROM Products
WHERE Products.ProductID = [Order Details].ProductID)
WHERE [Order Details].OrderID IN
(SELECT OrderID FROM Orders WHERE CustomerID = 'ALFKI')
0
Die UPDATE-Anweisung des SQL Servers basiert auf der SQL/92-Syntax und ist wieder um
einiges komplexer und wird an dieser Stelle nur vereinfacht beschrieben. Interessant ist, dass
Sie wie beim Löschen auch Joins und die Funktionen OPENQUERY und OPENROWSET verwenden
können:
UPDATE {Tabelle1 | Sicht1 | Datensatzgruppen_Funktion}
SET Spalte = {Ausdruck | NULL | DEFAULT} [,...n]
[FROM {Tabelle1 | Sicht1}
{INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN {Tabelle2 |
Sicht2}
ON {Tabelle1 | Sicht1}.Schlüsselspalte = {Tabelle2 |
Sicht2}.Schlüsselspalte]
[WHERE Bedingung]
Das folgende Beispiel aktualisiert die Spalte Notes der Mitarbeiter (Employees), die 2002 eine
Bestellung aufgenommen haben:
UPDATE Employees
SET Notes = 'Hat 2002 Bestellungen erfasst'
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE Year(Orders.OrderDate) = 2002
Wie so oft können Sie dieses Beispiel auch anders programmieren. So können Sie
statt dem Join z. B. eine Unterabfrage verwenden:
UPDATE Employees
SET Notes = 'Hat 2002 Bestellungen erfasst'
WHERE EmployeeID IN
(SELECT DISTINCT EmployeeID FROM Orders WHERE Year(OrderDate) = 2002)
Welche dieser beiden Varianten schneller ausgeführt wird, müssen Sie im Einzelfall testen. Im
Normalfall müsste die Join-Variante schneller ausgeführt werden.
Beachten Sie die Möglichkeit, Daten aus anderen Datenbanken über die OPENQUERY-Funktion
ändern zu können. Ein ähnliches Beispiel dazu finden Sie beim Hinzufügen von Datensätzen.
Die Data Manipulation Language 39
55
0/
)
55
Ist die Datenbank einmal erzeugt und gefüllt, stellt das Abfragen von Datensätzen die wohl
wichtigste Aufgabe dar. Die für diese „Auswahlabfragen“ verwendete SELECT-Anweisung ist
wohl die komplizierteste Anweisung in SQL. Mit einer Auswahlabfrage können Sie in der
Regel fast jede, auch noch so komplizierte Fragestellung lösen, Sie müssen lediglich wissen,
wie .
Eine Auswahlabfrage ergibt immer eine temporäre Tabelle mit Datensätzen, deren Spalten aus
einzelnen Datensätzen einer oder mehrerer Basistabellen oder Sichten zusammengesetzt sind.
Diese Ergebnistabelle können Sie in der Regel genauso bearbeiten, wie eine Basistabelle.
Beachten Sie, dass Auswahlabfragen in der Regel nicht in einem Abfragetool, wie dem Query
Analyzer des SQL Server, geschrieben werden, sondern im Programmcode einer ClientAnwendung. Schreiben Sie z. B. eine Visual Basic-Anwendung, die auf die Tabellen eines SQL
Servers zurückgreifen soll, erzeugen Sie über ADO13 ein sogenanntes Recordset (eine
„Datensatzgruppe“) und übergeben beim Öffnen des Recordsets eine SELECT-Anweisung. Das
Recordset enthält nach dem Öffnen alle Datensätze, die die SELECT-Anweisung zurückgibt.
Normalerweise können Sie mit dem Ergebnis solcher Abfragen in Client-Anwendungen alles
machen, was die im Client verwendete Datenzugriffstechnologie (bei Visual Basic ist das ADO)
erlaubt. Einige Datenzugriffstechnologien, wie z. B. die von Borland Delphi verwendete BDE14
lassen allerdings nicht zu, dass die Ergebnisse von Abfragen, die auf mehreren Basistabellen
basieren, editiert werden können (die Abfrage ist dann keine „Life“-Datenmenge).
Mit einer Auswahlabfrage können Sie nicht nur Spalten aus einer oder mehreren Tabellen
auswählen und darstellen, sondern auch Spalten berechnen und mit Hilfe von Funktionen Werte,
wie z. B. eine Summe berechnen. Das Ganze wird Ihnen weiter unten ausführlich erläutert.
Auswahlabfragen werden grundsätzlich unterteilt in Restriktionen, Projektionen und
Verknüpfungen.
#$
In SQL/92 besitzt die SELECT-Anweisung die folgende Form:
SELECT [{ALL | DISTINCT}] Spaltenliste
[INTO Neue_Tabelle]
FROM Tabellenliste
[WHERE Bedingungsausdruck]
[[GROUP BY Spaltenliste]
[HAVING Bedingungsausdruck]]
[ORDER BY Spaltenliste];
Die Reihenfolge der Klauseln muss eingehalten werden, auch wenn optionale Teile weggelassen
werden. Die einzelnen Klauseln werden im Folgenden ausführlich der Reihe nach erläutert.
0
Der SQL Server erweitert die SELECT-Anweisung in der Grundform fast gar nicht. Lediglich
die einzelnen Klauseln sind (teilweise massiv) erweitert. Die zusätzlichen Features werden in
der TSQL-Hilfe zur SELECT-Anweisung beschrieben.
13
Die ActiveX Data Objects (ADO) basieren auf OLE DB und stellen ein einfaches Objektmodell zum
Zugriff auf verschiedene Datenbanken dar.
14
BDE = Borland Database Engine
Die Data Manipulation Language 40
55
/
Die Restriktion (auch „Auswahl“, „Selektion“ oder „Einschränkung“) ergibt eine Teilmenge
von Zeilen einer Tabelle. Im einfachsten Fall verwenden Sie eine Anweisung der folgenden
Form:
SELECT * FROM Tabelle;
Der Asterisk (*) steht für „Alle Datenfelder“.
Das Beispiel:
SELECT * FROM Categories;
ergibt also alle Datensätze der Basistabelle in derselben Reihenfolge, wie diese in der
Basistabelle gespeichert sind und mit allen Datenfeldern:
CategoryID CategoryName Description
Picture
1
Beverages
Soft drinks, coffees, teas, beers, and ales
2
Condiments
Sweet and savory sauces, relishes, spreads, and seasonings
3
Confections
Desserts, candies, and sweet breads
4
Dairy Products
Cheeses
5
Grains/Cereals
Breads, crackers, pasta, and cereal
6
Meat/Poultry
Prepared meats
7
Produce
Dried fruit and bean curd
8
Seafood
Seaweed and fish
553
? /@
/
Die Projektion ergibt ebenfalls eine Teilmenge von Zeilen einer Tabelle, wobei jedoch nur
bestimmte Spalten oder Ergebnisse von Berechnungen bzw. Funktionen mit in das Ergebnis
übernommen werden. Im einfachsten Fall geben Sie die benötigten Spalten statt dem Asterisk
an, wobei die einzelnen Spalten durch Kommata voneinander getrennt werden:
SELECT Spalte1, Spalte2, Spalte3 [,...n] FROM Tabelle;
Beispiel:
SELECT CategoryName, Description FROM Categories;
Ergebnis:
CategoryName Description
Beverages
Soft drinks, coffees, teas, beers, and ales
Condiments
Sweet and savory sauces, relishes, spreads, and seasonings
Confections
Desserts, candies, and sweet breads
Dairy Products Cheeses
Grains/Cereals Breads, crackers, pasta, and cereal
Meat/Poultry
Prepared meats
Produce
Dried fruit and bean curd
Seafood
Seaweed and fish
Sie können in der Spaltenliste der Projektion auch eine Funktion oder eine Berechnung
einsetzen. Näheres dazu finden Sie unter Funktionen in Abschnitt 4.4.9.
Die Data Manipulation Language 41
555
.
Wenn Sie den Namen eines Datenfeldes im Ergebnis einer Projektion ändern wollen, oder das
Ergebnis einer Berechnung mit einem Spaltennamen versehen wollen, verwenden Sie die ASKlausel.
Beispiel: Umbenennen der Spalten ShipperID und CompanyName:
SELECT ShipperID AS ID, CompanyName AS Name, Phone FROM Shippers;
Ergebnis:
ID Name
Phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931
Beispiel: Benennen einer berechneten Spalte:
SELECT (Quantity * UnitPrice) AS Sum FROM [Order Details]
Ergebnis:
Sum
168.00
98.00
174.00
167.40
...
: +
+
A "
0/
7
+
Manche Datenbanksysteme wie Access oder der SQL Server erlauben Feld- und
Tabellennamen, die normalerweise nicht verwendbare Zeichen, wie z. B. das Leerzeichen oder
Bindestriche enthalten. Ist dies der Fall, müssen Sie den Feld- oder Tabellennamen in spezielle
Zeichen einbetten. In Access und beim SQL Server ist dies z. B. die eckige Klammer.
Beispiele:
SELECT * FROM Orders;
SELECT * FROM [Order Details];
Die Data Manipulation Language 42
556
* )
Sie können die Ergebniszeilen einer Auswahlabfrage einschränken, indem Sie in der WHEREKlausel eine Bedingung angeben, die bei der Abfrage der Basistabellen auf diese angewendet
wird. Eine einfache SELECT-Anweisung mit WHERE-Klausel hat die folgende Form:
SELECT Spaltenliste FROM Tabelle WHERE Bedingung;
Beispiel:
SELECT CompanyName FROM Customers WHERE City = 'Madrid';
Ergebnis:
CompanyName
Bólido Comidas preparadas
FISSA Fabrica Inter. Salchichas S.A.
Romero y tomillo
Die WHERE-Klausel ist grundsätzlich die erste Klausel in der SELECT-Anweisung wenn noch
andere Klauseln verwendet werden (z. B. ORDER BY zum Sortieren). Gültige Operatoren für die
WHERE-Klausel sind:
=, <, >, <=, >=, AND, OR, NOT, IS, IN, LIKE, MATCHES, BETWEEN
Einzelne Bedingungen können Sie mit AND und OR verknüpfen.
Das folgende Beispiel fragt alle Produkte ab, die der Kategorie 2 angehören und deren
Einzelpreis >= 25 ist:
SELECT ProductName, UnitPrice
FROM Products
WHERE CategoryID = 2 AND UnitPrice >= 25;
Zur besseren Übersicht und um Fehler zu vermeiden, können Sie die Bedingungen auch
klammern:
SELECT ProductName, UnitPrice
FROM Products
WHERE (CategoryID = 2) AND (UnitPrice >= 25);
Das Klammern ist besonders wichtig, wenn Sie AND, OR und/oder NOT gleichzeitig verwenden,
da bei einer Abfrage ohne Klammerung die Priorität dieser Operatoren die Auswertung
bestimmt (NOT wird vor AND ausgewertet und AND vor OR). Mit der Priorität hab ich selbst oft
so meine Probleme. Wenn Sie z. B. alle Produkte ermitteln wollen, die zur Kategorie 2 oder 3
gehören und einen Preis >= 25 haben, können Sie die Abfrage nicht folgendermaßen definieren:
SELECT ProductName, CategoryID, UnitPrice
FROM Products
WHERE CategoryID = 2 OR CategoryID = 3 AND UnitPrice >= 25
Das Ergebnis ist falsch, da AND vor OR ausgewertet wird, was der folgenden Klammerung
entspricht:
SELECT ProductName, CategoryID, UnitPrice
FROM Products
WHERE (CategoryID = 2) OR ((CategoryID = 3) AND (UnitPrice >= 25));
Die Data Manipulation Language 43
Das falsche Ergebnis:
ProductName
CategoryID UnitPrice
Aniseed Syrup
2
10.00
Chef Anton'
s Cajun Seasoning
2
22.00
Chef Anton'
s Gumbo Mix
2
21.35
Grandma'
s Boysenberry Spread
2
25.00
Northwoods Cranberry Sauce
2
40.00
Genen Shouyu
2
15.50
Sir Rodney'
s Marmalade
3
81.00
Gumbär Gummibärchen
3
31.23
Schoggi Schokolade
3
43.90
Gula Malacca
2
19.45
Sirop d'
érable
2
28.50
Tarte au sucre
3
49.30
Vegie-spread
2
43.90
Louisiana Fiery Hot Pepper Sauce 2
21.05
Louisiana Hot Spiced Okra
2
17.00
Original Frankfurter grüne Soße
2
13.00
Sie müssen hier klammern um ein korrektes Ergebnis zu erzielen:
SELECT ProductName, CategoryID, UnitPrice
FROM Products
WHERE (CategoryID = 2 OR CategoryID = 3) AND (UnitPrice >= 25);
Das korrekte Ergebnis:
ProductName
CategoryID UnitPrice
Grandma'
s Boysenberry Spread 2
25.00
Northwoods Cranberry Sauce
2
40.00
Sir Rodney'
s Marmalade
3
81.00
Gumbär Gummibärchen
3
31.23
Schoggi Schokolade
3
43.90
Sirop d'
érable
2
28.50
Tarte au sucre
3
49.30
Vegie-spread
2
43.90
Die Data Manipulation Language 44
""
!-
Bei der Abfrage werden Zeichenketten in einfache Anführungszeichen eingeschlossen.
Zahlwerte werden direkt angegeben:
SELECT CompanyName FROM Customers WHERE City = 'Madrid';
SELECT ProductName FROM Products WHERE SupplierID = 2;
Sie können Klammern verwenden, um die Priorität der Operatoren zu verschieben. Die
Reihenfolge der logischen Operatoren ist: NOT wird vor AND; AND wird vor OR ausgewertet. Sie
müssen bei der Verwendung von Dezimalzahlen die Ländereinstellung des Systems beachten,
auf dem die Datenbank installiert ist. Bei Desktop-Datenbanken ist dies in der Regel die
Einstellung in der Systemsteuerung des Client-Rechners (der ja bei Desktop-Datenbanken die
Abfrage ausführt). Bei Client/Server-Datenbanken wird die Ländereinstellung oft im
Datenbank-Server unabhängig von der Systemeinstellung vorgenommen. Ist beim SQL Server
z. B. die englische Sprache eingestellt, müssen Sie den Punkt als Dezimaltrennzeichen
verwenden:
Select ProductName FROM Products WHERE UnitPrice = 21.35;
Datumswerte führen immer zu Problemen. Die Syntax, wie Datumswerte angegeben werden,
unterscheidet sich erheblich zwischen den einzelnen Datenbanksystemen. Bei Access müssen
Sie z. B., unabhängig von der Spracheinstellung, ein Datum immer in Hashs (#) eingebettet in
der englischen Syntax (mm/dd/yyyy) angeben. Beim SQL Server können Sie ein Datum
sprachunabhängig idealerweise als String in der Form '
yyyymmdd'angeben. Das folgende
Beispiel zeigt eine Datumsabfrage in Microsoft Access (alle Bestellungen vom 11.07.1996):
SELECT * FROM Orders WHERE OrderDate = #07/11/96#;
Eine Datumsabfrage im SQL Server (alle Bestellungen vom 11.07.1996) sieht etwas anders aus:
SELECT * FROM Orders WHERE OrderDate = '19960711';
Bei Datumsabfragen müssen Sie immer beachten, dass eine Datumsspalte in den
meisten Datenbanksystemen auch eine Zeit speichern kann. Wenn Sie nach dem
01.07.1996 suchen, meinen Sie eigentlich 01.07.1996 00:00:00. Wenn Datensätze
zwar den 01.07.1996 im Datumsfeld gespeichert haben, dies jedoch mit einer
Zeitangabe, werden Sie diese Datensätze nicht unbedingt finden, wenn Sie mit
einer einfachen Vergleichsbedingung suchen. Um das Problem zu lösen, können
Sie den Datumswert einfach in einen Vergleich der folgenden Form einschließen:
(Datumsspalte >= Erster_Suchtag) AND (Datumsspalte < Letzter_Suchtag + 1)
Das folgende SQL-Server-Beispiel ermittelt alle Bestellungen vom 11.07.1996:
SELECT *
FROM Orders
WHERE (OrderDate >= '19960711') AND (OrderDate < '19960712');
Alternativ können Sie die SQL/92-Funktionen DAY, MONTH und YEAR verwenden, um einen
Datumsbereich zu filtern:
SELECT *
FROM Orders
WHERE (DAY(OrderDate) = 7) AND (MONTH(OrderDate) = 11)
AND (YEAR(OrderDate) = 1996);
Die Data Manipulation Language 45
&
*
Wenn eine gesuchte Zeichenkette nicht in der genauen Schreibweise bekannt ist, können Sie
diese Zeichenkette mit Wildcards suchen. SQL/92 kennt dazu den LIKE-Operator:
WHERE Ausdruck LIKE 'Muster'
Im Muster können Sie die Wildcards % und _ verwenden. Das Prozentzeichen steht für „Kein
oder mehrere beliebige Zeichen“, der Unterstrich steht für „genau ein beliebiges Zeichen“.
Das folgende Beispiel ermitttelt alle Kunden, deren Name ‚tru‘ enthält:
SELECT * FROM Customers WHERE CompanyName LIKE '%tru%';
Der SQL Server erweitert die Wildcards des LIKE-Operators um eine in eckigen Klammern
eingesetzte Zeichenliste. Diese Zeichenliste funktioniert ähnlich dem Unterstrich-Wildcard, mit
dem Unterschied, dass Sie die gültigen Zeichen in der Form abcdef oder als Liste in der Form af angeben.
Beispiel: Suchen aller Kunden, deren Name mit ‚A‘ beginnt, dann ein ‚n‘ oder ‚r‘ folgt und
beliebig weitergeht:
SELECT * FROM Customers WHERE CompanyName LIKE 'A[nr]%';
Wenn Sie Zeichen vom Suchergebnis ausschließen wollen, stellen Sie ein ^ vor dieses Zeichen.
Beispiel: Suchen aller Kunden, deren Name mit ‚A‘ beginnt und dann nicht mit einem ‚n‘
weitergeht:
SELECT * FROM Customers WHERE CompanyName LIKE 'A[^n]%';
Sie können den LIKE-Operator auch mit Nicht-Textfeldern verwenden, wenn Sie diese explizit
in einen Textdatentyp umwandeln. Die meisten Datenbanken bieten dazu spezielle Funktionen.
Das folgende SQL-Server –Beispiel ermittelt alle Produkte mit einem Preis, der mit .35 endet
SELECT * FROM Products WHERE CONVERT(varChar, UnitPrice) LIKE '%.35';
&
*
Wenn Sie einen Wert in einer festgelegten Menge suchen, können Sie den IN-Operator
verwenden:
WHERE Datenfeld IN (Wertliste)
Die folgende Anweisung sucht alle Kunden, die in Berlin oder Mannheim ansässig sind:
SELECT CustomerID, CompanyName, City
FROM Customers
WHERE City IN ('Berlin', 'Mannheim');
Ergebnis:
CustomerID CompanyName
City
ALFKI
Alfreds Futterkiste
Berlin
BLAUS
Blauer See Delikatessen Mannheim
Die Wertliste kann auch über eine Unterabfrage (siehe Abschnitt 4.4.11) ermittelt werden. Die
Unterabfrage darf dabei nur eine Spalte ergeben.
Das folgende Beispiel ermittelt alle Kunden, die im Jahr 1997 bestellt haben:
SELECT CompanyName FROM Customers WHERE CustomerID IN
(SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 1997)
Die Data Manipulation Language 46
,
*
*
Wenn Sie nach einem Wert innerhalb eines bestimmten Bereichs suchen, können Sie den
BETWEEN-Operator verwenden:
WHERE Ausdruck BETWEEN Wert1 AND Wert2
So können Sie z. B. alle Produkte ermitteln, deren Einzelpreis zwischen 12 und 15 liegt (inkl.
der 12 und der 15!):
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 12 AND 15;
Ergebnis:
ProductID ProductName
UnitPrice
25
NuNuCa Nuß-Nougat-Creme
14.00
31
Gorgonzola Telino
12.50
34
Sasquatch Ale
14.00
42
Singaporean Hokkien Fried Mee 14.00
46
Spegesild
12.00
48
Chocolade
12.75
58
Escargots de Bourgogne
13.25
67
Laughing Lumberjack Lager
14.00
68
Scottish Longbreads
12.50
70
Outback Lager
15.00
73
Röd Kaviar
15.00
77
Original Frankfurter grüne Soße
13.00
-
*
Wenn Sie Datensätze mit leeren Spalten finden wollen, so müssen Sie, da in leeren Spalten der
Wert NULL abgelegt ist, mit NULL vergleichen. Sie können für den Vergleich jedoch nicht den
Vergleichsoperator (=) verwenden! Um NULL-Werte zu finden, verwenden Sie den ISOperator:
WHERE Ausdruck IS [NOT] NULL
Wenn Sie in einem Textfeld suchen, kann es sein, dass in diesem Feld eine leere
Zeichenkette gespeichert ist. Eine leere Zeichenkette ist ungleich NULL! Sie
müssen also in Textfeldern u. U. auch mit einer leeren Zeichenkette vergleichen.
So können Sie z. B. herausfinden, bei welchen Kunden keine Region gespeichert ist:
SELECT CustomerID, CompanyName, Region
FROM Customers
WHERE (Region IS NULL) OR (Region = '')
Die Data Manipulation Language 47
Ergebnis:
CustomerID CompanyName
Region
ALFKI
Alfreds Futterkiste
ANATR
Ana Trujillo Emparedados y helados
ANTON
Antonio Moreno Taquería
AROUT
Around the Horn
BERGS
Berglunds snabbköp
BLAUS
Blauer See Delikatessen
...
...
-
...
8
0
Der SQL Server ermöglicht über die Funktionen OPENQUERY und OPENROWSET auch in
externen Datenbanken zu suchen. Diese beiden Funktionen habe ich bereits grundsätzlich bei
Anfügen von Datensätzen in Abschnitt 4.1 beschrieben.
Beispiel: Suchen aller Autoren aus der Datenbank pubs im SQL Server Arthur:
SELECT a.* FROM OPENROWSET('SQLOLEDB','Arthur';'sa';'',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
559
"
*
*
In Projektionen können Sie die Operatoren +, -, *, / und () verwenden um mit Spalten zu
rechnen. Die Anwendung dieser Operatoren unterscheidet sich nicht von der allgemeinen
mathematischen Anwendung.
Das folgende Beispiel berechnet Zwischensummen für eine Teilbestellung:
SELECT OrderID, Quantity * UnitPrice AS Amount
FROM [Order Details]
WHERE OrderID = 10248
Ergebnis:
OrderID Amount
10248
168.00
10248
98.00
10248
174.00
Die Data Manipulation Language 48
55<
/
Das Ergebnis einer SELECT-Abfrage können Sie mit der ORDER BY-Klausel sortieren:
ORDER BY Spalte [{ASC|DESC}] [,...n]
Wenn Sie die Option ASC oder nichts angeben wird diese Spalte aufsteigend sortiert, bei DESC
wird absteigend sortiert. Wenn Sie eine Untersortierung erreichen wollen, geben Sie die
weiteren Spalten durch Kommata getrennt an.
Das folgende Beispiel ergibt eine nach Lieferant und Artikel-Nr sortierte Artikel-Tabelle:
SELECT SupplierID , ProductName FROM Products ORDER BY SupplierID, ProductName
Ergebnis:
SupplierID ProductName
1
Aniseed Syrup
1
Chai
1
Chang
2
Chef Anton'
s Cajun Seasoning
2
Chef Anton'
s Gumbo Mix
2
Louisiana Fiery Hot Pepper Sauce
2
Louisiana Hot Spiced Okra
3
Grandma'
s Boysenberry Spread
3
Northwoods Cranberry Sauce
3
Uncle Bob'
s Organic Dried Pears
...
...
55=
(
--
Das Gruppieren ist ein wichtiges Hilfsmittel zur Erleichterung von Berechnungen über
gespeicherte Daten. Mit einer Gruppierung können Sie z. B. die Summe aller Umsätze der
einzelnen Kunden in einem bestimmten Zeitraum ermitteln. Sie gruppieren Datensätze mit der
GROUP BY-Klausel:
GROUP BY Spaltenliste [HAVING Bedingung]
Die GROUP BY Klausel führt dazu, dass für alle Datensätze, die in Bezug auf die gruppierten
Spalten den gleichen Inhalt aufweisen, nur noch ein einziger Datensatz angezeigt wird. GROUP
BY wird verwendet, wenn für nach bestimmten Datenfeldern gruppenweise zusammengefasste
Datensätze Berechnungen durchgeführt werden sollen oder Sie einfach einen Überblick über
Datensätze mit gleichen Feldwerten erhalten wollen. Beispielsweise wird hiermit in der
Customers-Tabelle die Anzahl der Kunden ermittelt, die in den einzelnen Orten ansässig sind.
Da die GROUP BY Klausel eine Gruppe von Datensätzen zu einem einzigen
Datensatz zusammenfaßt, können Sie nur die Spalten, nach denen Sie gruppieren,
in der Projektion direkt angeben. Alle weiteren Spalten können Sie nur als
Argument einer Aggregatfunktion (siehe weiter unten) einsetzen. Die Funktion
bezieht sich dann immer auf die Datensätze, die in der Gruppe zusammengefasst
werden.
Die Data Manipulation Language 49
Das folgende Beispiel ermittelt alle in der Artikeltabelle gespeicherten Lieferanten (unabhängig
davon, ob der Lieferant auch in der Lieferanten-Tabelle existiert) mit der Anzahl der Artikel, die
der jeweilige Lieferant liefert:
SELECT SupplierID, COUNT(*) AS Quantity FROM Products GROUP BY SupplierID;
Ergebnis:
SupplierID Quantity
1
3
2
4
3
3
4
3
...
...
* )
(
--
Mit der HAVING-Klausel können Sie die Ausgabe der gruppierten Datensätze einschränken:
HAVING Bedingung
HAVING legt Bedingungen fest, die auf eine Gruppe anzuwenden sind. Die Bedingung wird
genauso formuliert, wie bei der WHERE-Klausel.
Das folgende Beispiel ermittelt alle Lieferanten, die mindestens fünf Artikel liefern:
SELECT SupplierID, COUNT(*) AS Quantity
FROM Products
GROUP BY SupplierID
HAVING COUNT(*) >= 5
Ergebnis:
SupplierID Quantity
7
5
12
5
HAVING wird erst angewendet, nachdem die Datensätze gruppiert wurden. Wenn
Sie die Datensätze vor Ausführung der Gruppierung einschränken wollen, wenden
Sie die WHERE-Klausel an. Stellen Sie sich den Vorgang so vor: Das
Datenbanksystem schreibt zuerst alle Datensätze, die der WHERE-Klausel
entsprechen, in eine temporäre Ergebnistabelle. Auf diese Tabelle wird danach erst
die Gruppierung angewendet und eine weitere temporäre Ergebnistabelle erzeugt.
Auf diese wird schließlich die HAVING-Klausel und die eventuell noch vorhandene
ORDER BY-Klausel angewendet. Wenn Sie Datensätze nicht nach einem Ergebnis
der Gruppierung (z. B. einer Summe oder einer Anzahl) einschränken wollen,
sondern nach ganz normalen Datenfeldern, sollten Sie immer die WHERE-Klausel
einsetzen. Da das Gruppieren wesentlich mehr Zeit in Anspruch nimmt als das
Auswählen bestimmter Datensätze, ist eine gruppierte SQL-Abfrage, die Daten
über WHERE filtert, wesentlich schneller als eine, die die bereits gruppierten Daten
mit HAVING filtert.
Die Data Manipulation Language 50
Das folgende Beispiel ermittelt die Anzahl der Produkte der Lieferanten, die Produkte der
Kategorie 1 liefern:
SELECT SupplierID, COUNT(*) AS Quantity
FROM Products
WHERE CategoryID = 1
GROUP BY SupplierID
Ergebnis:
SupplierID Quantity
1
2
7
1
10
1
12
1
16
3
18
2
20
1
23
1
Alternativ könnten Sie auch mit HAVING filtern, wenn Sie zusätzlich nach CategoryID
gruppieren:
SELECT SupplierID, COUNT(*) AS Quantity
FROM Products
GROUP BY SupplierID, CategoryID
HAVING CategoryID = 1
Die erste Variante mit der WHERE-Klausel besitzt jedoch Vorteile in Bezug auf die
Ausführungsgeschwindigkeit, da das Gruppieren immer wesentlich länger dauert als eine
WHERE-Abfrage.
Bei Abfragen über mehrere verknüpfte Tabellen ist die Anwendung der WHERE-Klausel oft auch
aus logischer Sicht notwendig.
-
( BC? ,D B- /
"
0
Der SQL Server kennt die Optionen CUBE und ROLLUP, die bei der GROUP BY-Klausel
angewendet werden können:
GROUP BY Feldnamen WITH CUBE
GROUP BY Feldnamen WITH ROLLUP
Mit CUBE und ROLLUP können Sie Zwischen- und Endergebnisse für berechnete Spalten
ermitteln. Beide Optionen fügen der Ergebnisliste Datensätze an, die Summenwerte enthalten.
Diese zusätzlichen Datensätze enthalten in den Spalten, für die die Summe berechnet wurde,
den Wert NULL.
Das folgende Beispiel berechnet die Anzahl der Artikel in den einzelnen Kategorien:
SELECT CategoryId, COUNT(*) AS Quantity
FROM Products
GROUP BY CategoryID WITH ROLLUP
Die Data Manipulation Language 51
Ergebnis:
SupplierId
Quantity
1
12
2
12
3
13
4
10
5
7
6
6
7
5
8
12
NULL
77
Wenn Sie die Aggregatfunktion GROUPING verwenden, können Sie besser erkennen, welche
Datensätze von CUBE oder ROLLUP angefügt wurden. GROUPING(Datenfeld) AS
Spaltenname ergibt eine zusätzliche Spalte, die den Wert 1 enthält, wenn ein Datensatz eine
Summe für das Datenfeld enthält und 0, wenn nicht:
SELECT SupplierID, CategoryID, COUNT(*) AS Quantity,
GROUPING(CategoryID) AS IsSumRecord
FROM Products
GROUP BY SupplierID, CategoryID WITH ROLLUP
Ergebnis:
SupplierID CategoryID Quantity IsSumRecord
1
1
2
0
1
2
1
0
1
NULL
3
1
2
2
4
0
2
NULL
4
1
...
...
...
...
NULL
NULL
77
1
Der Unterschied zwischen CUBE und ROLLUP ist, dass ROLLUP nur die Summen der
Gruppenfelder in hierarchischer Form ergibt. CUBE bildet zusätzlich Summen für alle anderen
Kombinationen von Feldern.
Beispiel: Ermitteln der Bestellsummen für einzelne Städte, Kunden und Kategorien:
SELECT Customers.City, Customers.CompanyName, Products.CategoryID,
Sum([Order Details].[UnitPrice]*[Order Details].[Quantity]) AS Amount
FROM Orders, [Order Details], Products, Customers
WHERE Orders.OrderID = [Order Details].OrderID
AND Orders.CustomerID = Customers.CustomerID
AND [Order Details].ProductID = Products.ProductID
GROUP BY Customers.City, Customers.CompanyName, Products.CategoryID
WITH ROLLUP
Die Data Manipulation Language 52
Ergebnis (Erläuterungen hinzugefügt):
City
CompanyName
CategoryID
Amount
Aachen
Drachenblut
Delikatessen
1
247.20
Aachen
Drachenblut
Delikatessen
3
374.76
Aachen
Drachenblut
Delikatessen
4
2270.00
Aachen
Drachenblut
Delikatessen
6
656.00
Aachen
Drachenblut
Delikatessen
8
215.25
Aachen
Drachenblut
Delikatessen
NULL
3763.21 Summe für Drachenblut
Delikatessen
Aachen
NULL
NULL
3763.21 Summe für Aachen
Albuquerque Rattlesnake
Grocery
Canyon 1
19334.90
Albuquerque Rattlesnake
Grocery
Canyon 2
1690.00
Albuquerque Rattlesnake
Grocery
Canyon 3
11369.70
Albuquerque Rattlesnake
Grocery
Canyon 4
8056.40
Albuquerque Rattlesnake
Grocery
Canyon 5
4833.30
Albuquerque Rattlesnake
Grocery
Canyon 6
3830.80
Albuquerque Rattlesnake
Grocery
Canyon 7
2213.25
Albuquerque Rattlesnake
Grocery
Canyon 8
917.55
Albuquerque Rattlesnake
Grocery
Canyon NULL
Albuquerque NULL
NULL
...
...
...
Erläuterung
52245.90 Summe für Rattlesnake
Canyon Grocery
52245.90 Summe für Albuquerque
Buenos Aires Cactus Comidas para 1
llevar
1091.00
Buenos Aires Cactus Comidas para 3
llevar
75.00
Buenos Aires Cactus Comidas para 4
llevar
37.50
Buenos Aires Cactus Comidas para 7
llevar
364.80
Die Data Manipulation Language 53
Buenos Aires Cactus Comidas para 8
llevar
Buenos Aires Cactus Comidas para NULL
llevar
246.50
1814.80 Summe für Cactus Comidas
para llevar
Buenos Aires Océano Atlántico Ltda. 1
180.00
Buenos Aires Océano Atlántico Ltda. 2
622.00
Buenos Aires Océano Atlántico Ltda. 3
1311.00
Buenos Aires Océano Atlántico Ltda. 4
914.00
Buenos Aires Océano Atlántico Ltda. 7
403.20
Buenos Aires Océano Atlántico Ltda. 8
30.00
Buenos Aires Océano
Ltda.
Atlántico NULL
3460.20 Summe
für
Atlántico Ltda.
Océano
Buenos Aires Rancho grande
1
527.00
Buenos Aires Rancho grande
2
285.00
Buenos Aires Rancho grande
3
749.10
Buenos Aires Rancho grande
4
192.00
Buenos Aires Rancho grande
5
390.00
Buenos Aires Rancho grande
7
371.00
Buenos Aires Rancho grande
8
330.00
Buenos Aires Rancho grande
NULL
2844.10 Summe für Rancho grande
Buenos Aires NULL
NULL
8119.10 Summe für Buenos Aires
...
...
...
NULL
NULL
NULL
1354458.59 Gesamtsumme
ROLLUP ermittelt lediglich die Summen für die Gruppenhierarchie. In diesem Beispiel ermittelt
ROLLUP die folgenden Summen:
•
Summe für eine Stadt-Firma-Kombination
•
Summe für eine Stadt
•
Gesamtsumme
CUBE ermittelt zusätzlich alle weiteren Summen für alle möglichen Spalten-Kombinationen. In
diesem Beispiel ermittelt CUBE die folgenden Summen:
•
Summe für eine Stadt-Firma-Kombination
•
Summe für eine Stadta
•
Gesamtsumme
•
Summe für eine Firma-Kategorie-Kombination
•
Summe für eine Firma
•
Summe für eine Stadt-Kategorie-Kombination
•
Summe für eine Kategorie
Die Data Manipulation Language 54
Zusätzlich zu der Ausgabe, die oben mit ROLLUP erreicht wurde, ermittelt CUBE die folgenden
Datensätze (Auszug):
City
CompanyName
CategoryID Amount
NULL
Alfreds Futterkiste
1
648.00 Summe für Alfreds Futterkiste,
Kategorie 1
NULL
Alfreds Futterkiste
2
1364.00 Summe für Alfreds Futterkiste,
Kategorie 2
NULL
Alfreds Futterkiste
4
1255.00 Summe für Alfreds Futterkiste,
Kategorie 4
NULL
Alfreds Futterkiste
7
775.20 Summe für Alfreds Futterkiste,
Kategorie 7
NULL
Alfreds Futterkiste
8
554.00 Summe für Alfreds Futterkiste,
Kategorie 8
NULL
Alfreds Futterkiste
NULL
NULL
Ana Trujillo Emparedados y 1
helados
...
...
NULL
Ana Trujillo Emparedados y 8
helados
NULL
Ana Trujillo Emparedados y NULL
helados
...
...
...
Aachen
NULL
1
Albuquerque NULL
1
Århus
NULL
1
2436.00 Summe für Århus, Kategorie 1
Århus
NULL
1
1485.70 Summe für Århus, Kategorie 1
Barcelona
NULL
1
Barquisimeto NULL
1
...
...
...
NULL
NULL
1
Albuquerque NULL
2
Anchorage
NULL
2
Århus
NULL
2
...
...
...
...
Erläuterung
4596.20 Summe für Alfreds Futterkiste
60.00 Summe für Ana Trujillo ...,
Kategorie 1
... ...
60.00 Summe für Ana Trujillo ...,
Kategorie 8
1402.95 Summe für Ana Trujillo
...
247.20 Summe für Aachen, Kategorie 1
19334.90 Summe
für
Kategorie 1
90.00 Summe
für
Kategorie 1
669.00 Summe
für
Kategorie 1
Albuquerque,
Barcelona,
Barquisimeto,
..
286526.95 Summe für Kategorie 1
1690.00 Summe
für
Kategorie 2
720.15 Summe
für
Kategorie 2
Albuquerque,
Anchorage,
3521.40 Summe für Århus, Kategorie 2
...
Die Data Manipulation Language 55
55$
8
/
Funktionen helfen Ihnen, Datensätze auszuwerten. Unterschieden werden Funktionen in:
• Aggregatfunktionen (Funktionen, die einen Wert über mehrere Datensätze ermitteln) und
• skalare Funktionen (Funktionen, die einen Wert für jeden Datensatz einzeln ermitteln).
/
Aggregatfunktionen werden, sofern die SQL-Anweisung keine GROUP BY-Klausel enthält, auf
die gesamte Tabelle angewendet. Sie erhalten dann nur einen einzigen Ergebnisdatensatz. In
diesem Fall darf keine Spalte in die Anweisung übernommen werden, die nicht Argument einer
Funktion ist.
Das Beispiel:
SELECT SupplierID, MIN(UnitPrice) AS MinUnitPrice FROM Products;
funktioniert nicht, da das Feld SupplierID nicht in eine Funktion eingeschlossen ist!
Das Beispiel:
SELECT MIN(UnitPrice) AS MinUnitPrice FROM Products;
ergibt einen Datensatz, der den kleinsten Nettobetrag aller Datensätze der Tabelle beinhaltet:
MinUnitPrice
2.50
Wenn Sie mit der GROUP BY-Klausel nach bestimmten Datenfeldern gruppieren, können Sie die
gruppierten und alle anderen Spalten als Argumente von Funktionen einsetzen. Funktionen
werden dann auf die Gruppen angewendet.
Das folgende Beispiel ermittelt den minimalen und maximalen Preises für Artikel jedes
Lieferanten:
SELECT SupplierID, MIN(UnitPrice) AS MinUnitPrice, MAX(UnitPrice) AS
MaxUnitPrice
FROM Products
GROUP BY SupplierID;
Ergebnis:
SupplierID MinUnitPrice MaxUnitPrice
1
10.00
19.00
2
17.00
22.00
3
25.00
40.00
4
10.00
97.00
5
21.00
38.00
6
6.00
23.25
...
...
...
Aggregatfunktionen können Sie nicht direkt in eine WHERE-Klausel einbinden.
Die Data Manipulation Language 56
Das Beispiel:
SELECT * FROM Products WHERE UnitPrice < AVG(UnitPrice);
zur Ermittlung aller Artikel, deren Nettobetrag unterhalb des mittleren Nettobetrags liegt,
funktioniert so nicht (der SQL Server z. B. meldet den Fehler „Ein Aggregat darf nicht in der
WHERE-Klausel auftreten - es sei denn, es befindet sich in einer Unterabfrage, die in einer
HAVING-Klausel oder einer Auswahlliste enthalten ist, und die Spalte, die aggregiert wird, ist
ein Außenverweis“)! Sie müssen sich dann mit einer komplexen Abfrage behelfen:
SELECT * FROM Products WHERE UnitPrice < (SELECT AVG(UnitPrice) FROM
Products);
/
#$
SQL/92 stellt die in Tabelle 4.1 dargestellten Aggregatfunktionen zur Verfügung.
Funktion
Beschreibung
AVG([DISTINCT] Ausdruck)
ermittelt den Durchschnittswert.
COUNT( {* | [DISTINCT]
COUNT ermittelt die Anzahl der Datensätze innerhalb einer
MAX(Ausdruck)
ermittelt den größten Wert.
MIN(Ausdruck)
ermittelt den kleinsten Wert.
SUM([DISTINCT] Ausdruck)
ermittelt die Summe der Werte in der angegebenen Spalte.
Ausdruck})
Gruppe. Normalerweise verwenden Sie zur Ermittlung der
Anzahl COUNT(*), das angeblich schneller ausgeführt wird als
Count(Datenfeld). Wenn Sie die Anzahl der unterschiedlichen
Werte eines Feldes berechnen wollen, verwenden Sie
Count(DISTINCT Datenfeld).
Tabelle 4.1: Die Aggregatfunktionen von SQL/92
Die Data Manipulation Language 57
0
Der SQL Server kennt einige weitere Aggregatfunktionen (Tabelle 4.2).
Funktion
Beschreibung
CHECKSUM_AGG(Ausdruck)
Diese Funktion (Checksum Aggregate) ermittelt die aggregierte
Prüfsumme der Werte in einer Gruppe. Mit Hilfe der Funktion
BINARY_CHECKSUM, die die Checksumme einzelner Felder ermittelt,
können Sie z. B. ermitteln, ob eine Tabelle gegenüber einer
vorherigen Checksummenabfrage geändert wurdeDazu fragen Sie die
aggregierte Checksumme aller Felder ab:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Products
Sie können auch ermitteln, ob einzelne Felder geändert wurden,
indem Sie einfach die Feldnamen angeben:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(UnitPrice))
FROM Products
Das Ergebnis ist eine Integer-Zahl. Diese können Sie mit einer zuvor
ermittelten Checksumme vergleichen um Änderungen zu erkennen.
COUNT_BIG(Ausdruck)
ermittelt wie COUNT die Anzahl von Datensätzen bzw. Nicht-NullFeldern in einer Gruppe, gibt aber einen bigint-Wert zurück.
GROUPING(Feldname) Name
GROUPING wird zusammen mit der GROUP BY-Option CUBE oder
ROLLUP verwendet um zu ermitteln, ob eine Zeile von CUBE oder
ROLLUP hinzugefügt wurde (siehe in Abschnitt 4.4.8 bei Spezielle
GROUP BY-Optionen beim SQL Server).
STDEV(Ausdruck)
berechnet die statistische Standardabweichung für einen Ausdruck.
Der Ausdruck kann auch ein Feldname sein.
STDEVP(Ausdruck)
berechnet die statistische Standardabweichung für die Gesamtheit
aller Werte für einen Ausdruck.
VAR(Ausdruck)
berechnet die statistische Varianz für einen Ausdruck.
VARP(Ausdruck)
berechnet die statistische Varianz für die Gesamtheit aller Werte für
einen Ausdruck.
Tabelle 4.2: Die weiteren Aggregatfunktionen des SQL Servers
7 A'7
Durch die Angabe von DISTINCT werden alle mehrfach vorkommenden Werte für die Ausgabe
oder Berechnung eliminiert. Wenn in der Tabelle mehrere Datensätze mit identischen Werten in
der mit DISTINCT angegebenen Spalte existieren, wird nur jeweils der erste Datensatz in das
Ergebnis übernommen bzw. innerhalb einer Funktion nur der erste Datensatz zur Berechnung
verwendet. Wenn das angegebene Feld indiziert ist, wird der erste Datensatz aus dem Index,
sonst der erste Satz aus der Tabelle verwendet.
Die Data Manipulation Language 58
8
/
#$
SQL/92 stellt deutlich mehr skalare Funktionen zur Verfügung als Aggregatfunktionen. Die in
Tabelle 4.3 beschriebenen skalaren Funktionen können Sie beliebig in Ausgabespalten und
WHERE-Bedingungen einsetzen. Die wichtigen Funktionen sind hervorgehoben dargestellt.
Funktion
Beschreibung
BIT_LENGTH({Zeichenkette |
gibt die Länge einer angegebenen Zeichenkette oder Bitkette in Bits
zurück. Der SQL Server kennt kann nichts damit anfangen.
Verwenden Sie hier stattdessen die LEN-Funktion.
CASE
CASE gibt abhängig von einer der mehreren Bedingungen einen
skalaren Wert zurück. CASE wird aufgrund der Komplexität unten
Bitkette})
separat beschrieben.
CAST(Ausdruck AS {Datentyp |
Domäne}
wandelt den Datentyp eines Ausdrucks in einen anderen Datentyp
um. Die Umwandlung funktioniert nur dann, wenn der Datentyp
konvertierbar ist. Das Beispiel:
SELECT CAST('123' AS INT)
funktioniert, das Beispiel
SELECT CAST('abc' AS INT)
ergibt einen Fehler.
CHAR_LENGTH({Zeichenkette |
CHAR_LENGTH gibt die Länge in Zeichen zurück, wenn Sie eine
CONVERT(Zeichenkette USING
CONVERT wandelt eine angegebene Zeichenkette in einen anderen
CURRENT_DATE
gibt das aktuelle Datum zurück. Der SQL Server kennt diese
Funktion nicht..
CURRENT_TIME
gibt die aktuelle Zeit zurück. Der SQL Server kennt diese Funktion
nicht.
CURRENT_TIMESTAMP
gibt das aktuelle Datum inkl. der aktuellen Zeit zurück.
CURRENT_USER
gibt den Login-Namen des aktuellen Benutzers zurück.
Anmerkungen zu CURRENT_USER und SESSION_USER finden Sie
unten. Sie können auch die Kurzform USER verwenden.
EXTRACT(Feld FROM
gibt eine Zahl zurück, die einen Teil des angegebenen Datums
darstellt. Als Feld können Sie die folgenden Schlüsselwörter
einsetzen: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
TIMEZONE_HOUR oder TIMEZONE_MINUTE. Der SQL Server kennt
diese Funktion nicht.
LOWER(Zeichenkette)
gibt die Zeichenkette in Kleinschrift zurück.
Bitkette})
Umwandlung)
Datumsausdruck)
Zeichenkette übergeben und die Länge in Oktetten15, wenn Sie eine
Bitkette übergeben (aber wer will schon Oktette ermitteln ...).
Zeichencode um. Als Umwandlung können Sie z. B.
EBCDIC_TO_ASCII oder ASCII_TO_EBCDIC angeben, um
zwischen ASCII und EBCDIC (IBM-Zeichencode) zu konvertieren.
Achtung: Beim SQL Server hat CONVERT eine andere Funktion,
hier wandelt CONVERT Datentyen in andere Datentypen um.
15
Ein Oktett ist die Länge einer Bitkette dividiert durch 8, wobei der eventuelle Rest der Division
ignoriert wird.
Die Data Manipulation Language 59
OCTET_LENGTH(Bitkette)
ermittelt die Länge der Bitkette in Oktetten (siehe bei
CHAR_LENGTH).
POSITION(Suchzeichenkette IN gibt die Position der Suchzeichenkette in der Zeichenkette zurück.
Zeichenkette)
Der SQL Server kennt diese Funktion nicht. Verwenden Sie hier die
Funktion CHARINDEX.
SESSION_USER
gibt den Login-Namen des aktuellen Benutzers der aktuellen
Sitzung zurück. Anmerkungen zu CURRENT_USER und
SESSION_USER finden Sie unten.
SUBSTRING(Zeichenkette FROM gibt einen Teil einer Zeichenkette zurück, wobei Start die Position
des ersten Zeichens definiert und Anzahl die Anzahl der Zeichen,
Start FOR Anzahl)
die ab Start zurückgegeben werden. Der SQL Server verwendet eine
andere Syntax (siehe weiter unten).
SYSTEM_USER
gibt den Login-Namen des am Betriebssystem (nicht am SQL
Server!) angemeldeten Benutzers zurück.
TRANSLATE(Zeichenkette
USING Zeichenübersetzung)
gibt eine übersetzte Zeichenkette zurück, wobei eine vordefinierte
Zeichenübersetzung verwendet wird. Mit
TRANSLATE('
A,B,C,D'USING PUNCTATION_TO_SPACES)
wandeln Sie z. B. die Zeichenkette '
A,B,C,D'um in '
A B C D'
. Der
SQL Server kennt das Schlüsselwort TRANSLATE nicht.
TRIM(Art FROM Zeichenkette)
gibt eine Zeichenkette ohne vorangestellte und/oder angehangene
Füllzeichen (Leerzeichen etc.) zurück. Mit Art geben Sie an, welche
Füllzeichen entfernt werden sollen. Sie können hier LEADING,
TRAINLING oder BOTH angeben. Der SQL Server kennt TRIM nicht.
Verwenden Sie hier stattdessen LTRIM. und RTRIM.
UPPER(Zeichenkette)
gibt die Zeichenkette in Großschrift zurück.
USER
USER ist eine Kurzform von CURRENT_USER.
Tabelle 4.3: Skalare Funktionen bei SQL/92
"
'C
A7EC
BAEC
Der Unterschied zwischen CURRENT_USER und SESSION_USER ist recht schwierig zu
verstehen (sogar Soukup und Delaney behaupten auf Seite 504, dass CURRENT_USER und
SESSION_USER identische Ergebnisse liefern!). Wenn ein Benutzer sich in ein
Datenbanksystem einloggt, wird immer eine Sitzung aufgebaut. SESSION_USER gibt demnach
zunächst den Login-Namen des aktuell eingeloggten Benutzers zurück. Mit der Anweisung SET
SESSION AUTHORIZATION kann die Sitzungs-Authentisierungs-ID allerdings auch nachträglich
geändert werden (was ich allerdings nie machen würde).
Die CURRENT_USER-Funktion gibt in SQL/92 zunächst die Authentisierungs-ID des Moduls (?)
zurück, in dem die SQL-Anweisung gespeichert ist, die die CURRENT_USER-Funktion enthält
(vgl. dazu Date, Darven S. 250). Was der Begriff „Modul“ in SQL/92 bedeutet, konnte ich
bisher nicht herausfinden. In einem Test mit dem SQL Server gab CURRENT_USER auch dann
den Login-Namen des aktuell eingeloggten Benutzers zurück, wenn dieser eine Stored
Procedure aufgerufen hatte, die die CURRENT_USER-Funktion enthielt und die im Besitz eines
anderen Benutzers war.
Die Data Manipulation Language 60
'
CASE ist eine mächtige Funktion, mit der Sie einen Wert abhängig von einer oder mehreren
Bedingungen zurückgeben können. CASE besitzt zwei Varianten:
CASE Zu_Prüfender_Ausdruck
WHEN Prüfausdruck THEN
Ergebnisausdruck
[...n]
[ELSE
Else_Ergebnisausdruck]
END
Bei dieser Variante wird der zu prüfende Ausdruck mit den Prüfausdrücken verglichen. Wenn
ein Vergleich wahr wird, wird der in Ergebnisausdruck zurückgegeben. Sie können mehrere
WHEN-Blöcke einbauen.
Das folgende Beispiel wertet den aktuellen Wochentag aus:
SELECT
CASE DATEPART(dw, GETDATE())
WHEN 5 THEN 'Noch einen Tag bis zum Wochenende :-)'
WHEN 6 THEN 'Yeah, Wochenende'
WHEN 7 THEN 'Nur noch einen Tag Wochenende :-('
WHEN 1 THEN 'UUH, Montag :-(('
ELSE 'Waiting for weekend ...'
END
Bei der zweiten Variante können Sie unterschiedliche Bedingungsausdrücke verwenden:
CASE
WHEN Bedingungsausduck THEN Ergebnisausdruck
[...n]
[ELSE Else_Ergebnisausdruck]
END
So können Sie den aktuellen Wochentags auch auswerten:
SELECT
CASE
WHEN (DATEPART(dw, GETDATE()) = 6) OR
(DATEPART(dw, GETDATE()) = 7) THEN 'Yeah, Wochenende'
WHEN (DATEPART(dw, GETDATE()) = 1) THEN 'UUH, Montag :-(('
ELSE 'Waiting for weekend'
END
8
/
Die folgenden Funktionen sind zwar in „SQL - Der Standard“ nicht als SQL/92-Funktionen
beschrieben, stehen in vielen Datenbanksystemen jedoch zur Verfügung:
Funktion
Beschreibung
DATE()
gibt das aktuelle Systemdatum zurück.
DAY(Datumsausdruck)
ermittelt den Tag aus einem Datumsausdruck als Zahl.
MONTH(Datumsausdruck)
ermittelt den Monat aus einem Datumsausdruck als Zahl.
YEAR(Datumsausdruck)
ermittelt das Jahr aus einem Datumsausdruck als Zahl.
WEEKDAY(Datumsausdruck)
ermittelt den Tag aus einem Datumsausdruck als Zahl: 1 = Sonntag, 2
= Montag ...7 = Samstag (amerikanisches Format).
Tabelle 4.4: Weitere skalare Funktionen bei Standard-SQL
Beispiel: Ermitteln aller Bestellungen des Jahres 1996:
SELECT * FROM Orders WHERE YEAR(OrderDate) = 1996;
Die Data Manipulation Language 61
0
Um die Übersicht nicht unter der Vielzahl der Funktionen leiden zu lassen, beschreibe ich hier
die wichtigsten der skalaren Funktionen des SQL Servers inklusive den in SQL/92 bereits
definierten.
Datumsfunktionen
Funktion
Beschreibung
DATEADD(Datumsteil, Anzahl,
ergibt ein Datum, bei dem eine in Anzahl übergebene Anzahl von in
Datumsteil definierten Intervallen addiert ist. Die Intervalle werden als
String übergeben und sind weiter unten beschrieben.
DATEDIFF(Datumsteil,
berechnet die Differenz zwischen zwei Datumswerten in Intervallen, die
in Datumsteil übergeben werden.
DATENAME(Datumsteil,
Datumsausdruck)
ergibt einen Text, der das in Datumsteil übergebene Intervall des Datums
bezeichnet.
DATEPART(Datumsteil,
ergibt einen Integerwert für ein bestimmtes Intervall eines Datums.
DAY(Datumsausdruck)
ermittelt den Tag aus einem Datumsausdruck als Zahl.
GETDATE()
ergibt das aktuelle Systemdatum.
GETUTCDATE()
ermittelt das aktuelle Datum als UTC-Datumswert (Universal Time
Coordinate oder Greenwich Mean Time).
MONTH(Datumsausdruck)
ermittelt den Monat aus einem Datumsausdruck als Zahl.
WEEKDAY(Datumsausdruck)
ermittelt den Tag aus einem Datumsausdruck als Zahl: 1 = Sonntag, 2 =
Montag ...7 = Samstag (amerikanisches Format).
YEAR(Datumsausdruck)
ermittelt das Jahr aus einem Datumsausdruck als Zahl.
Datumsausdruck)
Startdatum, Enddatum)
Datumsausdruck)
Tabelle 4.5: Die Datumsfunktionen des SQL Servers
Intervallangaben der Datumsfunktionen
Intervall
Beschreibung
yy, yyyy
Jahr
qq, q
Quartal
mm, m
Monat
dy, y
Tag des Jahres
dd, d
Tag
wk, ww
Woche
hh
Stunde
ss, s
Sekunde
ms
Millisekunde
Tabelle 4.6: Intervallangaben der Datumsfunktionen des SQL Servers
Die Data Manipulation Language 62
Mathematische Funktionen
Funktion
Bedeutung
ABS(Numerischer_Ausdruck)
berechnet den Absolutwert.
ACOS(Float_Ausdruck)
berechnet den Arkuskosinus (den Winkel im Bogenmaß,
dessen Kosinus der Wert des übergebenen Float-Ausdrucks
ist).
ASIN(Float_Ausdruck)
berechnet den Arkussinus (den Winkel im Bogenmaß, dessen
Sinus der Wert des übergebenen Float-Ausdrucks ist).
ATAN(Float_Ausdruck)
berechnet den Arkustangens (den Winkel im Bogenmaß,
dessen Tangens der Wert des übergebenen Float-Ausdrucks
ist).
ATN2(Float_Ausdruck,
berechnet den Winkel im Bogenmaß, dessen Tangens
zwischen den beiden angegebenen Float-Ausdrücken liegt.
CEILING(Numerischer_Ausdruck)
gibt den kleinsten Integerwert zurück, der größer oder gleich
dem übergebenen Dezimalwert ist.
COS(Float_Ausdruck)
berechnet den Cosinus.
COT(Float_Ausdruck)
gibt den Kotangens des in Bogenmaß übergebenen Winkels
zurück.
DEGREES(Numerischer_Ausdruck)
berechnet einen in Radians übergebenen Winkel in Grad.
EXP(Float_Ausdruck)
berechnet den Exponentialwert.
FLOOR(Numerischer_Ausdruck)
gibt den größten Integerwert zurück, der kleiner oder gleich
dem übergebenen Dezimalwert ist.
LOG(Float_Ausdruck)
berechnet den natürlichen Logarithmus.
LOG10(Float_Ausdruck)
berechnet den Logarithmus zur Basis 10.
PI()
gibt den Wert für PI zurück.
POWER(Numerischer_Ausdruck, y)
potenziert den übergebenen numerischen Ausdruck mit der in
y übergebenen Potenz . POWER(2, 3) entspricht also z. B.
23.
RADIANS(Numerischer_Ausdruck)
berechnet einen in Grad übergebenen Winkel in Radians.
RAND([Startwert])
gibt einen dezimalen Zufallswert zwischen 0 und 1 zurück.
Startwert spezifiziert einen optionalen Integer-Startwert.
ROUND(Numerischer_Ausdruck,
rundet einen Wert auf die in Länge übergebene Anzahl
Dezimalstellen. Ist Funktion nicht oder mit 0 angegeben,
wird gerundet, ist Funktion mit 1 angegeben, wird
abgeschnitten.
SIGN(Numerischer_Ausdruck)
gibt das Vorzeichen einer Zahl codiert als Zahl zurück: +1 =
positives Vorzeichen, 0 = kein Vorzeichen, -1 = negatives
Vorzeichen.
SIN(Float_Ausdruck)
berechnet den Sinus.
SQUARE(Float_Ausdruck)
berechnet das Quadrat eines Ausdrucks.
SQRT(Float_Ausdruck)
berechnet die Quadratwurzel eines Ausdrucks.
TAN(Float_Ausdruck)
berechnet den Tangens.
Float_Ausdruck)
Länge [, Funktion])
Tabelle 4.7: Die mathematischen Funktionen des SQL Servers
Die Data Manipulation Language 63
Stringfunktionen
Funktion
Bedeutung
ASCII(Zeichenkette)
gibt den ASCII-Wert des äußerst linken Zeichens im übergebenen
String zurück.
CHAR(Zeichencode)
gibt einen ASCII-String mit einem Zeichen zurück, das dem
übergebenen Zeichencode entspricht.
CHARINDEX(Ausdruck1,
gibt die Position des ersten Auftretens von Ausdruck1 in Ausdruck2. In
Start_Position können Sie angeben, ab welchem Zeichen gesucht
werden soll
DIFFERENCE(Zeichenkette1,
ergibt den SOUNDEX-Unterschied zwischen zwei Strings. 0 steht für
den kleinstmöglichen, 4 für den größtmöglichen Unterschied.
LEFT(Zeichenkette, Anzahl)
ergibt die in Anzahl definierten linken Zeichen des übergebenen
Strings.
LEN(Zeichenkette)
ergibt die Länge des Strings in Zeichen.
LOWER(Zeichenkette)
wandelt einen String in Kleinschreibung um.
LTRIM(Zeichenkette)
gibt einen String ohne führende Leerzeichen zurück.
NCHAR(Zeichencode)
gibt einen Unicode-String mit einem Zeichen zurück, das dem
übergebenen Zeichencode entspricht.
PATINDEX('
%Muster%'
,
ergibt den Index des ersten Vorkommens eines Stringmusters in einem
String. Das Stringmuster wird mit Wildcards angegeben.
REPLACE('Zeichenkette1',
gibt einen String zurück, bei dem alle Vorkommen von Zeichenkette2
in Zeichenkette1 durch Zeichenkette3 ersetzt ist
QUOTENAME(Zeichenkette
Diese Funktion konvertiert eine Zeichenkette in eine mit SQL-ServerFeldtrennzeichen ausgerüstete Zeichenkette, sodass diese als
Objektname verwendet werden kann. Als Trennzeichenn können Sie
einfache Apostrophe, Anführungszeichen und eine linke oder rechte
eckige Klammer angeben. Wenn Sie kein Zeichen angeben, verwendet
der SQL Server die Standardzeichen für Bezeichner-Begrenzungen ([,
]). Damit können Sie Objektnamen, die Zeichen außerhalb von
Buchstaben, Zahlen und dem Unterstrich beinhalten, recht einfach in
für SQL-Anweisungen gültige Bezeichner umwandeln.
QUOTENAME('Kunden-Nr') ergibt z. B. [Kunden-Nr]
REPLICATE(Zeichenkette,
Integer_Ausdruck)
gibt einen String zurück, der den in Zeichenkettenausdruck
übergebenen String so oft enthält, wie in Integer_Ausdruck angegeben.
REVERSE(Zeichenkette)
dreht einen String um.
Ausdruck2
[, Start_Position])
Zeichenkette2)
Ausdruck)
'Zeichenkette2',
'Zeichenkette3')
[, '
Trennzeichen'
])
RIGHT(Zeichenkette, Anzahl) ergibt die in Anzahl übergebene Anzahl rechter Zeichen des
übergebenen Strings.
RTRIM(Zeichenkette)
Gibt einen String ohne rechte Leerzeichen zurück.
SOUNDEX(Zeichenkette)
Gibt einen vier-Zeichen SOUNDEX-Code zurück.
SPACE(Anzahl)
Gibt eine bestimmte Anzahl Leerzeichen zurück
STR(Float_Ausdruck [,
konvertiert einen Dezimalwert in einen String.
Länge [, Dezimalstellen]])
Die Data Manipulation Language 64
STUFF(Zeichenkette1, Start,
Länge, Zeichenkette2)
Diese Funktion löscht die in Länge angegebene Anzahl Zeichen ab der
in Start angegebenen Position aus der ersten übergebenen Zeichenkette
und fügt dort die zweite übergebene Zeichenkette ein.
SUBSTRING(Ausdruck, Start, gibt einen Teistring zurück.
Länge)
UNICODE(Zeichenkette)
gibt einen Integerwert zurück, der den Unicode-Wert des übergebenen
Zeichens definiert.
UPPER(Zeichenkette)
wandelt eine Zeichenkette in Großschreibung um.
Tabelle 4.8: Die Stringfunktionen des SQL Servers
Systemfunktionen (Auszug)
Funktion
Bedeutung
APP_NAME()
gibt den Anwendungsnamen zurück, sofern von der aktuellen
Sitzung ein solcher festgelegt wurde.
CASE Zu_Prüfender_Ausdruck
WHEN Prüfausdruck THEN
CASE ist eine mächtige Funktion, mit der Sie einen Wert abhängig
von einer oder mehreren Bedingungen zurückgeben können. CASE
Ergebnisausdruck
[...n]
[ELSE
Else_Ergebnisausdruck ]
wird in Abschnitt 4.4.9 näher beschrieben.
END
CASE
Diese zweite Form von CASE wird ebenfalls in Abschnitt 4.4.9
WHEN Bedingungsausduck THEN näher beschrieben.
Ergebnisausdruck
[...n]
[ELSE
Else_Ergebnisausdruck ]
END
COALESCE(Ausdruck1
[, Ausdruck2] [...])
gibt den ersten der übergebenen Ausdrücke zurück, der nicht
NULL ist. COALESCE(Ausdruck1, Ausdruck2) entspricht:
CASE
WHEN (Ausdruck1 IS NOT NULL) THEN Ausdruck1
WHEN (Ausdruck2 IS NOT NULL) THEN Ausdruck2
ELSE NULL
CURRENT_USER()
gibt den Login-Namen des Benutzers zurück, der diese Funktion
aufgerufen hat. Entspricht USER_NAME().
DATALENGTH (Ausdruck)
gibt die Anzahl Bytes zurück, die zur Speicherung eines
Ausdrucks verwendet werden.
ISDATE(Ausdruck)
gibt 1 zurück wenn der übergebene Stringausdruck ein gültiges
Datum ist und 0, wenn nicht.
ISNULL(Prüfausdruck,
gibt den in Ersatz_Ausdruck übergebenen Wert zurück wenn der
in Prüfausdruck übergebene Ausdruck NULL ergibt.
ISNUMERIC(Ausdruck)
gibt 1 zurück wenn der übergebene Stringausdruck numerisch ist
und 0, wenn nicht.
NEWID()
erzeugt einen weltweit eindeutigen GUID-Wert.
Ersatz_Ausdruck)
Tabelle 4.9: Die wichtigsten Systemfunktionen des SQL Servers
Die Data Manipulation Language 65
Konvertierungsfunktionen
Funktion
Bedeutung
CAST(Ausdruck AS Datentyp)
gibt den in Ausdruck übergebenen Ausdruck mit dem in Datentyp
übergebenen Datentyp zurück.
CONVERT (Datentyp [(Länge)],
CONVERT ist im Wesentlichen identisch mit CAST.
Ausdruck [, Stil])
Tabelle 4.10: Die Konvertierungsfunktionen des SQL Servers
55
F
%
7 A'7
7B?
#$
In SQL/92 können Sie bei der SELECT-Anweisung mit DISTINCT angeben, dass doppelte
Ergebnisdatensätze nicht in das Ergebnis übernommen werden:
SELECT {ALL | DISTINCT} FROM Tabelle ...
Geben Sie DISTINCT an, muss jeder Ergebnisdatensatz in den abgefragten Spalten eindeutig
sein. Mit Hilfe von DISTINCT können Sie Redundanz in der Ausgabe vermeiden. Die
Voreinstellung ALL bewirkt, dass immer alle Datensätze im Ergebnis erscheinen.
Das folgende Beispiel ermittelt alle Lieferanten aus der Artikeltabelle ohne Redundanzen:
SELECT DISTINCT SupplierID FROM Products;
Ergebnis:
SupplierID
1
2
3
4
5
6
7
...
Die Data Manipulation Language 66
0
Wie viele andere Datenbanksysteme auch erweitert der SQL Server die SELECT-Optionen um
die TOP-Option:
SELECT {ALL | DISTINCT | TOP n [PERCENT]} FROM Tabelle ...
Mit TOP n erhalten Sie die ersten n Datensätze aus dem Ergebnis. Die Abfrage sollte dazu nach
den Feldern sortiert sein, die im Ergebnis oben stehen sollen. Sie können für n ganze Zahlen
oder Prozentwerte angeben. So können Sie z. B. die zehn teuersten Artikel ermitteln:
SELECT TOP 10 ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC
Beachten Sie, dass die Abfrage absteigend sortiert ist, sodass die teuersten Artikel
im Ergebnis oben stehen.
Die folgende Abfrage ergibt 20 Prozent der teuersten Artikel:
SELECT TOP 20 PERCENT ProductName, UnitPrice FROM Products ORDER BY UnitPrice
DESC
Sie können TOP 100 PERCENT verwenden, um Sichten (Views, siehe Kapitel 5),
die normalerweise nicht sortiert werden können, dennoch zu sortieren:
CREATE VIEW Products_with_Category AS
SELECT TOP 100 PERCENT ProductId, ProductName, UnitPrice, CategoryName
FROM Products INNER JOIN Categories
ON Products.CategoryId = Categories.CategoryId
ORDER BY ProductName
55
C
G
H
!I
SQL/92 ermöglicht, dass Sie innerhalb eines Bedingungsausdrucks weitere SELECTAnweisungen einsetzen, die Sie in Klammern setzen. Diese „Subquerys“ werden bei der
Ausführung der SQL-Anweisung von innen nach außen gehend zuerst ausgewertet. Das
Ergebnis der Unterabfrage können Sie in der Projektion oder als Operand in einer Bedingung
verwenden. Damit können die Daten einer Tabelle abhängig von den Daten einer anderen
Tabelle ausgegeben werden. Bevor ich Unterabfragen weiter ausführe möchte ich anmerken,
dass die meisten Unterabfragen durch Joins wesentlich eleganter und auch performanter gelöst
werden können. Joins werden in Abschnitt 4.4.13 beschrieben.
Das folgende Beispiel ermittelt alle Lieferanten, die Produkte liefern:
SELECT * FROM Suppliers WHERE SupplierID IN (SELECT SupplierID FROM Products);
In diesem Beispiel werden nur alle Lieferanten ausgegeben, die auch in der Artikeltabelle mit
Ihrer Id-Nummer gespeichert sind. Alle Lieferanten, die zurzeit nichts liefern, werden somit
ausgeschlossen.
Die folgende Anweisung ermittelt die Artikel, deren Preis dem größten Preis entspricht:
SELECT * FROM Products WHERE UnitPrice IN (SELECT MAX(UnitPrice) FROM
Products);
In der Mengenklammer muss die Projektion auf eine Spalte reduziert werden, deren Datentyp
zum Datentyp der Vergleichsspalte passen muss.
Da eine Unterabfrage normalerweise, wie andere Abfragen auch, mehrere Datensätze (mit
jeweils einem Datenfeld) ergibt, müssen Sie den IN-Operator verwenden, um festzustellen, ob
das Suchfeld im Ergebnis der Unterabfrage vorkommt. Vergleichsoperatoren können nur dann
Die Data Manipulation Language 67
verwendet werden, wenn die Unterabfrage definitiv nur einen Datensatz liefert, oder wenn einer
der Zusatzoperatoren ANY und ALL mit verwendet wird (siehe weiter unten).
Die SELECT-Anweisung kann beliebig tief geschachtelt werden. Eine Unterabfrage kann
optional auch eine WHERE-Klausel beinhalten. In der WHERE-Klausel können Sie sich auf
Spalten aus der übergeordneten Abfrage beziehen. Die Unterabfrage wird (aus logischer Sicht)
für jeden Datensatz der in der übergeordneten Abfrage verwendeten Tabellen aufgerufen.
Das folgende Beispiel ermittelt alle Bestelldetails der Bestellungen des Jahres 1996:
SELECT *
FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM Orders WHERE YEAR(OrderDate) = 1996)
Die Spalte OrderDate bezieht sich in diesem Beispiel auf die übergeordnete Tabelle Orders.
55
B-
/
AD%
J
7
Die Operatoren ANY, ALL und EXISTS werden im Zusammenhang mit einem
Vergleichsoperator und Wertlisten verwendet (die üblicherweise mit Unterabfragen ermittelt
werden):
Ausdruck Vergleichsoperator [ANY | ALL | EXISTS] (Wertliste)
Die Operatoren ANY und ALL werden in der Praxis kaum eingesetzt weil Abfragen
mit diesen Operatoren meist (oder vielleicht immer?) durch andere Abfragen
ersetzt werden können. In den Beispielen zu ANY und ALL sind solche Abfragen
dargestellt.
AD
Mit dem ANY-Operator (Any = engl.: Irgendein) wird die Bedingung bereits wahr, wenn
mindestens einer der von der Unterabfrage gelieferten Werte dem Vergleich entspricht. Mit ANY
ermitteln Sie Datensätze die in einer Spalte einen Wert besitzen, der bezogen auf die
kleiner/gleich als der größte oder größer/gleich als der kleinste Wert der Wertliste ist oder Sie
ermitteln Datensätze mit einem Spaltenwert, der in der Menge der in der Wertliste enthaltenen
Werte vorkommt.
Beispiele:
Bedingung
Werte, die die Bedingung
erfüllen
Bemerkungen
> ANY(1, 2, 3)
alle Werte größer als 1
ermittelt, ob ein Wert größer als der kleinste Wert der
Wertliste ist.
< ANY(1, 2, 3)
alle Werte kleiner als 3
ermittelt, ob ein Wert kleiner als der größte Wert der
Wertliste ist.
= ANY(1, 2, 3)
1 oder 2 oder 3
ist identisch mit IN (1, 2, 3).
Tabelle 4.11: Beispiele für den ANY-Operator
Das folgende Beispiel ermittelt alle Produkte mit einem Preis kleiner als der größte der vom
Lieferanten mit der ID 1 gelieferten Artikel (der größte Einzelpreis des Lieferanten 1 ist 19,00):
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice < ANY (SELECT UnitPrice FROM Products WHERE SupplierID = 1)
ORDER BY UnitPrice
Die Data Manipulation Language 68
Ergebnis:
ProductName
UnitPrice
Geitost
2.5000
Guaraná Fantástica
4.5000
Konbu
6.0000
Filo Mix
7.0000
Tourtière
7.4500
Rhönbräu Klosterbier
7.7500
...
...
Chai
18.0000
Steeleye Stout
18.0000
Chartreuse verte
18.0000
Lakkalikööri
18.0000
Boston Crab Meat
18.4000
Der ANY-Operator kann häufig auch umgangen werden. Das vorstehende Beispiel
kann z. B. auch anders gelöst werden:
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice < (SELECT MIN(UnitPrice) FROM Products WHERE SupplierID = 1)
ORDER BY UnitPrice
Das folgende Beispiel ermittelt alle Bestelldetails der Bestellungen des Jahres 1996:
SELECT *
FROM [Order Details]
WHERE OrderID = ANY (SELECT OrderID FROM Orders WHERE YEAR(OrderDate) =
1996)
Der Vergleich mit = ANY ist redundant, da auch der IN-Operator dazu verwendet
werden kann:
SELECT *
FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM Orders WHERE YEAR(OrderDate) = 1996)
Mit dem ALL-Operator wird die Bedingung nur dann war, wenn alle von der Unterabfrage
gelieferten Werte dem Vergleich entsprechen. Hiermit können Sie Datensätze ermitteln, die in
einer Spalte einen kleineren oder größeren Wert besitzen, als die Werte, die in der Unterabfrage
ermittelt werden.
Die Bedingung
> ALL (Wertliste)
bedeutet: »Größer als alle Werte, die in der Wertliste angegeben sind.« Entsprechend können
Sie die Operatoren <, <= und >= einsetzen. Der Operator = hat beim ALL-Operator wenig Sinn
(damit ein Wert gleich allen Werten einer Liste ist, darf die Liste nur aus einem Wert bestehen).
Die Data Manipulation Language 69
Tabelle 4.12 zeigt einige Beispiele für den All-Operator.
Bedingung
Werte, die
erfüllen
die
Bedingung Bemerkungen
> ALL(1, 2, 3)
alle Werte größer als 3
ermittelt, ob ein Wert größer als der größte Wert
der Wertliste ist.
< ALL(1, 2, 3)
alle Werte kleiner als 1
ermittelt, ob ein Wert kleiner als der kleinste
Wert der Wertliste ist
Tabelle 4.12: Beispiele für den ALL-Operator
Das folgende Beispiel ermittelt alle Artikel, deren Preis größer ist als der größte Preis der
Artikel, die vom Lieferanten mit der ID 1 geliefert werden:
SELECT ProductName, UnitPrice, SupplierID
FROM Products
WHERE UnitPrice > ALL (SELECT UnitPrice FROM Products WHERE SupplierID = 1);
Der ALL-Operator kann häufig auch umgangen werden. Das vorstehende Beispiel
kann z. B. auch anders gelöst werden:
SELECT ProductName, UnitPrice, SupplierID
FROM Products
WHERE UnitPrice > (SELECT MAX(UnitPrice) FROM Products WHERE SupplierID =
1);
J
7
Der EXISTS-Operator überprüft, ob die Wertliste Werte enthält. Dazu verwenden Sie die
folgende Syntax:
WHERE [NOT] EXISTS (Wertliste)
EXISTS lässt die Bedingung wahr werden, wenn mindestens ein Wert (bzw. mit NOT kein
Wert) in der Wertliste gefunden wird. Wenn Sie für die Wertliste eine Unterabfrage einsetzen,
können Sie an Stelle einer Spalte auch den Wert Null einsetzen, was die Ausführung der
Abfrage erheblich beschleunigt.
Die folgende Anweisung ermittelt alle Lieferanten, die mindestens einen Artikel liefern:
SELECT *
FROM Suppliers
WHERE EXISTS (SELECT NULL FROM Products
WHERE Products.SupplierID = Suppliers.SupplierID)
Dieses Beispiel ist sehr uneffektiv. Sie sollten idealerweise beide Tabellen über
einen Inner Join miteinander verknüpfen. Ein Inner Join ergibt automatisch nur alle
Datensätze, deren Schlüsselfelder in den betroffenen Tabellen vorkommen und
wird wesentlich schneller ausgeführt.
55
3
K/
GL
* I
Joins verbinden mehrere Tabellen miteinander und ergeben eine virtuelle Tabelle, die aus den in
der Projektion angegebenen Spalten der beteiligten Basistabellen zusammengesetzt werden. Die
zu verbindenden Tabellen müssen für alle Joins ausser dem Cross-Join, dazu über mindestens
eine Spalte verfügen, über die die Verknüpfung aus logischer Sicht realisiert ist (viele
Datenbanksysteme erlauben auch die „physikalische“ Verknüpfung von Tabellen über die
sogenannte Referentielle Integrität).
Die Data Manipulation Language 70
Verbundausdrücke können in zwei Varianten geschrieben werden: Die ältere Variante
verwendet keine speziellen Schlüsselwörter, sondern einen einfachen Vergleich der
Schlüsselspalten in einer WHERE-Klausel.
Das folgende Beispiel ermittelt alle Bestellungen mit den Spalten OrderID, OrderDate (aus
Orders) und CompanyName (aus Customers):
SELECT OrderID, OrderDate, CompanyName
FROM Orders, Customers
WHERE Orders.CustomerID = Customers.CustomerID
Ergebnis:
OrderID OrderDate
CompanyName
10248
1996-07-04 00:00:00.000 Vins et alcools Chevalier
10249
1996-07-05 00:00:00.000 Toms Spezialitäten
10250
1996-07-08 00:00:00.000 Hanari Carnes
10251
1996-07-08 00:00:00.000 Victuailles en stock
10252
1996-07-09 00:00:00.000 Suprêmes délices
10253
1996-07-10 00:00:00.000 Hanari Carnes
...
...
...
Diese ältere Variante wird von vielen Datenbanksystemen unterstützt und ist recht intuitiv,
besonders dann, wenn mehrere Tabellen miteinander verbunden werden sollen.
Die neuere Variante verwendet das Schlüsselwort JOIN.
Das folgende Beispiel ermittelt alle Bestellungen mit den Spalten OrderID, OrderDate (aus
Orders) und CompanyName (aus Customers):
SELECT OrderID, OrderDate, CompanyName
FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
SQL/92 beschreibt bereits die JOIN-Variante. Schon aus diesem Grunde sollten Sie sich damit
auseinandersetzen. Ein anderer wichtiger Grund für die neue Variante ist die Tatsache, dass z.
B. der SQL Server (und wahrscheinlich auch andere Datenbanksysteme) bei der Verwendung
der alten Variante bei der Verwendung von Outer-Joins in manchen Fällen ein falsches Ergebnis
liefert. Hierzu ein Zitat:
The old SQL Server *= and =* operators are prone ("sind anfällig für”, Anm. des
Verfassers) to ambiguities (“Mehrdeutigkeiten”, Anm. des Verfassers), especially
when three or more tables, views or subqueries are involved. Often the results
anren’t what you’d expect, even though you might be able to explain them.
(Soukup, Delaney. Inside SQL Server 7.0, S. 314)
Wenn schon die Autoren von Inside SQL Server 7.0 so etwas schreiben, sollten wir, so denke
ich, die Finger von der alten Variante lassen. Ich selbst habe schon einmal Stunden nach dem
Fehler gesucht und diesen dann in der Verwendung der alten Join-Syntax lokalisiert.
: +
-
"
Wenn Sie Spalten aus mehreren Tabellen in die Projektion eintragen, müssen Sie den
Tabellennamen mit einem Punkt getrennt vor die Spalte setzen, wenn ein Spaltenname in
mehreren Tabellen vorkommt. Das Voranstellen des Tabellennamens schadet jedoch auch nicht,
wenn die Spaltennamen eindeutig sind.
Die Data Manipulation Language 71
Beispiel:
SELECT Products.ProductName, Suppliers.CompanyName
FROM Products INNER JOIN Suppliers ON Products.SupplierID =
Suppliers.SupplierID;
Wenn aus einer der auszugebenden Tabellen alle Spalten ausgegeben werden sollen, muss vor
dem Asterisk explizit der Tabellenname angegeben werden:
Tabelle.*
Beispiel:
SELECT Products.*, Suppliers.CompanyName
FROM Products INNER JOIN Suppliers ON Products.SupplierID =
Suppliers.SupplierID;
Wenn nur der Asterisk angegeben ist, werden automatisch alle Spalten aller beteiligten Tabellen
ausgegeben.
: +
!
>
*
Die Syntaxbeschreibung bezieht sich der Übersicht wegen zunächst immer auf den Verbund von
nur zwei Tabellen. Weiter unten wird Ihnen jedoch auch der Verbund von mehr als zwei
Tabellen erläutert.
"
!
>
L
*
#$
Die allgemeine Syntax der Verbundausdrücke besitzt in SQL/92 die folgende Form:
SELECT Spaltenliste
FROM Tabellenreferenz1 [NATURAL] [Verbundtyp] JOIN Tabellenreferenz2
[{ON Bedingungsausdruck | USING (Spaltenliste)}]
Verbundtyp kann sein: CROSS, INNER, LEFT [OUTER], RIGHT [OUTER], FULL [OUTER] oder
UNION. Die einzelnen Verbundtypen werden unten näher beschrieben. Die Verbundbedingung
können Sie entweder in einem Bedingungsausdruck angeben (wie in den Beispielen oben) oder,
sofern die Verbundspalten gleich benannt sind, mit der USING-Klausel. In SQL/92 können Sie
die Abfrage der Produkte mit den Lieferantennamen (siehe oben) also auch folgendermaßen
schreiben:
SELECT Products.ProductName, Suppliers.CompanyName
FROM Products INNER JOIN Suppliers USING(SupplierID);
Wird nur das Schlüsselwort JOIN angegeben, wird ein INNER JOIN verwendet.
Die Tabellenreferenz kann eine Basistabelle, eine Sicht, eine abgeleitete Tabelle oder wieder
eine über einen Verbundausdruck ermittelte virtuelle Tabelle sein (dazu mehr weiter unten unter
Die Data Manipulation Language 72
Joins über mehr als zwei Tabellen in Abschnitt 4.4.13).
Wenn Sie das Schlüsselwort NATURAL oder UNION verwenden, dürfen Sie keinen
Bedingungsausdruck und keine USING-Klausel angeben. Ein NATURAL JOIN verbindet zwei
Tabellen über gleichnamige Schlüsselspalten, d.h., alle Spalten in den in der Abfrage
enthaltenen Tabellen, die denselben Namen besitzen, werden miteinander in Beziehung gesetzt.
Das Beispiel oben könnte also auch so geschrieben werden:
SELECT Products.ProductName, Suppliers.CompanyName
FROM Products NATURAL INNER JOIN Suppliers;
Die Data Manipulation Language 73
"
!
>
L
*
"
0
Der SQL Server kennt die Schlüsselwörter USING und NATURAL nicht und erweitert die
Verbundausdrücke um einige einstellbare Optionen:
SELECT Spaltenliste
FROM Tabellenreferenz1 [Verbundtyp] [Join_Option] JOIN Tabellenreferenz2
[ON Bedingungsausdruck]
Als Verbundtyp ist beim SQL Server UNION nicht erlaubt (solche Abfragen werden anders
geschrieben, siehe weiter unten). Möglich sind also: CROSS, INNER, LEFT [OUTER], RIGHT
[OUTER] oder FULL [OUTER].
Die Tabellenreferenz kann eine Basistabelle, eine Sicht, eine abgeleitete Tabelle oder wieder
eine über einen Verbundausdruck ermittelte virtuelle Tabelle sein (dazu mehr weiter unten unter
Die Data Manipulation Language 74
Joins über mehr als zwei Tabellen in Abschnitt 4.4.13).
In der Join-Option können Sie die interne Ausführung des Verbundes für die Query-Engine des
SQL Servers beeinflussen um Abfragen evtl. optimieren zu können. Da diese Aufgabe intern
bereits der „Query Optimizer“ vornimmt, wird dafür wenig Bedarf sein. Für eine Beschreibung
der Join-Optionen verweise ich auf die TSQL-Hilfe.
'/
K/
G.
0
I
Der Begriff „Cross Join“ ist lediglich eine anderen Bezeichnung für das Erweiterte Karthesische
Produkt, das allerdings meist nur als „Karthesisches Produkt“ bezeichnet wird. Das
Karthesische Produkt „multipliziert“ jeden Datensatz einer Tabelle mit jedem Datensatz der
anderen Tabelle.
In der alten Form schreiben Sie dazu:
SELECT Spaltenliste FROM Tabellenreferenz1, Tabellenreferenz2
In der neuen Form verwenden Sie das Schlüsselwort CROSS JOIN:
SELECT Spaltenliste
FROM Tabellenreferenz1 CROSS JOIN Tabellenreferenz2
Am besten lässt sich das Karthesische Produkt am Beispiel einer Tabelle mit Vornamen und
einer Tabelle mit Nachnamen erläutern: Wenn Sie alle möglichen Kombinationen von Vor- und
Nachnamen erhalten wollen, schreiben Sie z. B.:
SELECT FirstNames.FirstName, LastNames.LastName
FROM FirstNames CROSS JOIN LastNames
Um das Beispiel mit der Northwind-Datenbank umsetzen zu können, wollen wir alle
Firmennamen aus der Kunden-Tabelle mit allen Produktnamen verbinden:
SELECT CompanyName, ProductName
FROM Customers CROSS JOIN Products
Das Karthesische Produkt wird übrigens häufig nur zufällig und ungewollt erzeugt, nämlich
dann, wenn bei Verwendung der alten Join-Variante der Vergleich der Schlüsselspalten
vergessen wird.
K/
Der Inner Join ergibt alle Datensätze, bei denen der Schlüsselwert gleich ist. Er ergibt keine
Datensätze der Mastertabelle, deren Schlüsselwert nicht in der Detailtabelle vorkommt und
keine Datensätze der Detailtabelle, deren Schlüsselwert nicht in der Mastertabelle vorkommt
(was bei Verwendung der Referentiellen Integrität nicht vorkommt!). Besonderes Augenmerk
muss auf die Möglichkeit gesetzt werden, dass Schlüsselspalten evtl. auch den Wert NULL
enthalten können. Solche Datensätze werden beim Inner Join ebenfalls nicht in das Ergebnis
übernommen.
Die Data Manipulation Language 75
Der Inner Join wir nach der alten Syntax folgendermaßen geschrieben:
SELECT Spaltenliste
FROM Tabellenreferenz1, Tabellenreferenz2
WHERE Tabellenreferenz1.Schlüsselspalte {= | < | <= | > | >= | <> }
Tabellenreferenz2.Schlüsselspalte
[AND Tabellenreferenz1.Schlüsselspalte2 {= | < | <= | > | >= | <> }
Tabellenreferenz2.Schlüsselspalte2 [...n]]
Nach der neuen Syntax schreiben Sie:
SELECT Spaltenliste
FROM Tabellenreferenz1 [INNER] JOIN Tabellenreferenz2
ON Tabellenreferenz1.Schlüsselspalte {= | < | <= | > | >= | <> }
Tabellenreferenz2.Schlüsselspalte
[AND Tabellenreferenz1.Schlüsselspalte2 {= | < | <= | > | >= | <> }
Tabellenreferenz2.Schlüsselspalte2 [...n]]
Die Vergleiche mit anderen Operatoren als = sind für spezielle Anwendungszwecke gedacht. In
den meisten Fällen vergleichen Sie die Schlüsselspalten mit =. Das Schlüsselwort INNER ist
optional, Sie können dies also auch weglassen.
Beispiel: Ermitteln der Produktnamen und der Namen der Lieferanten für das jeweilige Produkt:
SELECT Products.ProductName, Suppliers.CompanyName
FROM Products INNER JOIN Suppliers ON Products.SupplierID =
Suppliers.SupplierID;
Ergebnis:
ProductName
CompanyName
Chai
Exotic Liquids
Chang
Exotic Liquids
Aniseed Syrup
Exotic Liquids
Chef Anton'
s Cajun Seasoning
New Orleans Cajun Delights
Chef Anton'
s Gumbo Mix
New Orleans Cajun Delights
Grandma'
s Boysenberry Spread Grandma Kelly'
s Homestead
...
...
Ein Inner Join, der alle Spalten aller beteiligten Tabellen ausgibt und der den Vergleich mit =
verwendet, wird übrigens auch als „Equijoin“ bezeichnet, wenn nur bestimmte Spalten
ausgegeben werden, wird der Join auch als „Natürlicher Join“ bezeichnet.
7
@
/
Wenn Sie die Schlüsselfelder der Tabellen mit einem Vergleichsoperator vergleichen, so
erhalten Sie einen Thetajoin. Da Sie auch den Gleichheitsoperator einsetzen können, ist jeder
Equijoin oder natürliche Join auch ein Thetajoin. Der Umgang mit Joins, die andere Operatoren
verwenden ist ein recht „tricky“ und hilft Ihnen in seltenen Fällen zu einem Ergebnis, dass Sie
sonst (evtl. über eine Unterabfrage?) vielleicht nicht erhalten würden. Bei der Anwendung von
anderen Operatoren als dem = beziehen Sie sich übrigens in der Regel nicht auf die
Schlüsselspalten, sondern auf andere Spalten. Wenn Sie z. B. die folgende Syntax verwenden:
SELECT Spaltenliste
FROM Tabellenreferenz1 [INNER] JOIN Tabellenreferenz2
ON Tabellenreferenz1.SpalteX <> Tabellenreferenz2.SpalteX
erhalten Sie ein Karthesisches Produkt aller Datensätze aus Tabelle1 und Tabelle2, die sich im
Wert in der SpalteX unterscheiden.
Die Data Manipulation Language 76
Schreiben Sie stattdessen:
SELECT Spaltenliste
FROM Tabellenreferenz1 [INNER] JOIN Tabellenreferenz2
ON Tabellenreferenz1.Schlüsselspalte = Tabellenreferenz2.Schlüsselspalte
AND Tabellenreferenz1.SpalteX <> Tabellenreferenz2.SpalteX
, so heben Sie das Karthesische Produkt auf und geben nur Datensätze aus, die miteinander in
Beziehung stehen und sich im Wert der SpalteX unterscheiden. Im Prinzip gleicht diese Syntax
der folgenden, häufig zu sehenden intuitiveren Lösung:
SELECT Spaltenliste
FROM Tabellenreferenz1 [INNER] JOIN Tabellenreferenz2
ON Tabellenreferenz1.Schlüsselspalte = Tabellenreferenz2.Schlüsselspalte
WHERE Tabellenreferenz1.SpalteX <> Tabellenreferenz2.SpalteX
Diese stellt jedoch eine Mischung aus der alten und der neuen Syntaxvariante dar und sollte
deswegen nicht verwendet werden.
Beispiel: Ermitteln der Bestelldetails, deren Einzelpreis ein anderer ist als der im verbundenen
Produkt gespeicherte:
SELECT [Order Details].OrderID, [Order Details].ProductID,
[Order Details].UnitPrice, Products.UnitPrice
FROM [Order Details] INNER JOIN Products ON
[Order Details].ProductID = Products.ProductID AND
[Order Details].UnitPrice <> Products.UnitPrice
K/
B
K/
Nun zu der Besonderheit des Inner Join im Vergleich zum Outer Join: Der Inner Join nimmt,
wie bereits ober beschrieben, nur Datensätze in das Ergebnis auf, bei denen die Werte der
Verbundspalten gleich sind. Nehmen Sie zwei Tabellen an, die folgendermaßen aussehen:
Vornamen
ID
Vorname
1
Zaphod
2
Arthur
4
Tricia
NULL Nobody
Nachnamen
ID
Nachname
1
Beeblebrox
2
Dent
3
Duck
NULL Unknown
Die Data Manipulation Language 77
Diese Tabellen können Sie folgendermaßen erzeugen und füllen:
CREATE
CREATE
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
TABLE Vornamen (ID int, Vorname Varchar(50))
TABLE Nachnamen (ID int, Nachname Varchar(50))
INTO Vornamen VALUES (1, 'Zaphod')
INTO Vornamen VALUES (2, 'Arthur')
INTO Vornamen VALUES (4, 'Tricia')
INTO Vornamen VALUES (NULL, 'Nobody')
INTO Nachnamen VALUES (1, 'Beeblebrox')
INTO Nachnamen VALUES (2, 'Dent')
INTO Nachnamen VALUES (3, 'Duck')
INTO Nachnamen VALUES (NULL, 'Unknown')
Eine Abfrage wie die folgende:
SELECT Vornamen.Vorname, Nachnamen.Nachname
FROM Vornamen INNER JOIN Nachnamen ON Vornamen.ID = Nachnamen.ID
würde das folgende Ergebnis liefern:
Vorname Nachname
Zaphod
Beeblebrox
Arthur
Dent
Die Datensätze mit der ID 4 aus Vornamen und der ID 3 und 5 aus Nachnamen werden nicht in
das Ergebnis übernommen, da die Werte der Verbundspalten nicht in der jeweils anderen
Tabelle gefunden werden. Außerdem werden die Datensätze mit dem NULL-Wert in Vornamen
und Nachnamen ebenfalls nicht in das Ergebnis übernommen!
In der Praxis werden Sie selten solche Abfragen über zwei gleichwertige Tabellen ausführen.
Meistens wird eine der Tabellen eine Detailtabelle sein (z. B. die Products-Tabelle) und die
andere stellt als Mastertabelle (z. B. die Suppliers-Tabelle) Datensätze zur Verfügung, deren
Schlüsselwerte in der Detailtabelle gespeichert sind. Probleme mit Werten in den
Verbundspalten, die in der jeweils anderen Tabelle nicht vorkommen, werden in der Praxis in
der Regel nicht auftreten, weil die Beziehung zwischen den Tabellen im Datenbanksystem mit
der sogenannten Referentiellen Integrität gesichert ist. Auf NULL-Werte, die in der
Verbundspalte gespeichert sein können, müssen Sie jedoch achten!
Wir wollen nun einmal die Products-Tabelle ein wenig ändern: Der Lieferant mit der ID 1 ist in
Konkurs gegangen und wird in der Products-Tabelle mit dem Wert NULL überschrieben, da
noch kein neuer Lieferant für diese Produkte verfügbar ist:
UPDATE Products SET SupplierID = NULL WHERE SupplierID = 1
Wenn Sie nun die bereits oben verwendete Abfrage ausführen:
SELECT Products.ProductName, Suppliers.CompanyName
FROM Products INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID;
ist das Ergebnis nicht das, welches eigentlich erwartet war:
ProductName
CompanyName
Chef Anton'
s Cajun Seasoning
New Orleans Cajun Delights
Chef Anton'
s Gumbo Mix
New Orleans Cajun Delights
Grandma'
s Boysenberry Spread
Grandma Kelly'
s Homestead
Uncle Bob'
s Organic Dried Pears Grandma Kelly'
s Homestead
Northwoods Cranberry Sauce
Grandma Kelly'
s Homestead
...
...
Die Data Manipulation Language 78
Die Produkte „Chai“, „Chang“ und „Aniseed Syrup“ fehlen im Ergebnis, da bei diesen
Produkten nun in der Spalte SupplierID der Wert NULL eingetragen ist.
Dieses Problem können Sie mit einem Outer Join lösen. Ein Outer Join existiert in drei
Varianten: Left Outer Join, Right Outer Join und Full Outer Join. Der Left Outer Join ergibt alle
Datensätze der im Verbundausdruck linken Tabelle, auch wenn in der rechten Tabelle keine
Datensätze mit einem passenden Wert in der Verbundspalte gefunden werden. Der Right Outer
Join ergibt entsprechend alle Datensätze der im Verbundausdruck rechten Tabelle. Der Full
Outer Join ergibt alle Datensätze beider Tabellen, auch wenn in beiden Tabellen keine
Datensätze mit einem jeweils passenden Wert in der Verbundspalte gefunden werden.
Die folgenden Beispiele demonstrieren die verschiedenen Outer Joins an Hand der Vornamenund Nachnamen-Tabelle (die Tabelle, deren Datensätze komplett ausgegeben werden, ist
jeweils fett formatiert):
Left Outer Join
SELECT Vornamen.Vorname, Nachnamen.Nachname
FROM Vornamen LEFT OUTER JOIN Nachnamen
ON Vornamen.ID = Nachnamen.ID
Ergebnis:
Vorname Nachname
Zaphod
Beeblebrox
Arthur
Dent
Tricia
NULL
Nobody
NULL
Right Outer Join
SELECT Vornamen.Vorname, Nachnamen.Nachname
FROM Vornamen RIGHT OUTER JOIN Nachnamen
ON Vornamen.ID = Nachnamen.ID
Ergebnis:
Vorname Nachname
Zaphod
Beeblebrox
Arthur
Dent
NULL
Duck
NULL
Unknown
Full Outer Join
SELECT Vornamen.Vorname, Nachnamen.Nachname
FROM Vornamen FULL OUTER JOIN Nachnamen
ON Vornamen.ID = Nachnamen.ID
Die Data Manipulation Language 79
Ergebnis:
Vorname Nachname
Zaphod
Beeblebrox
Arthur
Dent
NULL
Duck
NULL
Unknown
Tricia
NULL
Nobody
NULL
Outer Joins sind sehr wichtig, wenn Sie unbedingt alle Datensätze einer bestimmten Tabelle in
der Abfrage berücksichtigen müssen, die Ihren Bedingungen entsprechen. Bei Umsatzabfragen
z. B. führt die Verwendung eines Inner Joins in der Regel zu Fehlern, wenn die Detailtabelle
Datensätze enthält, deren Verknüpfungsfelder leer sind oder in der Mastertabelle nicht gefunden
werden.
Das folgende (komplizierte) Beispiel soll die Umsätze aller Artikel gruppiert nach Lieferant
ermitteln. Mit einem Inner Join erhalten Sie ein falsches Ergebnis, da die Artikel, die zuvor vom
Lieferant 1 geliefert wurden, nun in der Spalte SupplierID den Wert NULL gespeichert haben:
SELECT Suppliers.CompanyName,
SUM([Order Details].Quantity * [Order Details].UnitPrice) AS Sales
FROM ([Order Details] INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID) INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
GROUP BY Suppliers.CompanyName
ORDER BY Suppliers.CompanyName
Dieses Beispiel verwendet bereits Joins über drei Tabellen, die weiter unten näher
beschrieben werden. Im Prinzip ist die Auflösung der Joins einfach: Der Join
zwischen Order Details und Products ergibt eine virtuelle Ergebnistabelle, die als
linke Tabellenreferenz für den Join mit der Suppliers-Tabelle verwendet wird.
Ergebnis:
CompanyName
Sales
Aux joyeux ecclésiastiques
163135.0000
Bigfoot Breweries
23776.8000
Cooperativa de Quesos '
Las Cabras' 26768.8000
Escargots Nouveaux
6664.7500
Forêts d'
érables
66266.7000
Formaggi Fortini s.r.l.
51082.5000
Gai pâturage
126582.0000
...
...
Die Data Manipulation Language 80
Die Umsätze der Artikel mit SupplierID NULL fehlen! Die Lösung für dieses Problem ist die
Verwendung eines Outer Joins, der alle Datensätze der Products-Tabelle mit einbezieht:
SELECT Suppliers.CompanyName,
SUM([Order Details].Quantity * [Order Details].UnitPrice) AS Sales
FROM ([Order Details] INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID)
LEFT OUTER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
GROUP BY Suppliers.CompanyName
ORDER BY Suppliers.CompanyName
Ergebnis:
CompanyName
Sales
NULL
35916.8000
Aux joyeux ecclésiastiques
163135.0000
Bigfoot Breweries
23776.8000
Cooperativa de Quesos '
Las Cabras' 26768.8000
Escargots Nouveaux
6664.7500
Forêts d'
érables
66266.7000
Formaggi Fortini s.r.l.
51082.5000
Gai pâturage
126582.0000
G'
day, Mate
69636.6000
...
...
8/ "
In der alten Form werden Outer Joins mit den Vergleichsoperatoren *= (Left Outer Join) und =*
(Right Outer Join) geschrieben. Ob ein Full Outer Join (evtl. *=*) möglich ist konnte ich nicht
herausfinden.
Beachten Sie die Hinweise in Abschnitt 4.4.13 bevor Sie die alte Syntax
verwenden.
,
0/ K/
Wie bei normalen Abfragen können Sie das Ergebnis aller Joins über die WHERE-Klausel
einschränken, gruppieren und sortieren.
Beispiel:
SELECT ProductName, CompanyName AS Supplier
FROM Products INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
WHERE Products.CategoryID = 1;
Die Data Manipulation Language 81
7 A'7
Mit SELECT DISTINCT wählen Sie Daten aus mehrfach auftretenden Datensätzen verknüpfter
Tabellen nur einmal aus. Die Suppliers-Tabelle enthält z. B. keine mehrfach vorkommenden
Einträge für die Spalte SupplierID, im Gegensatz zur Products-Tabelle, in der mehrfache
Datensätze mit gleichen Werten in der Spalte SupplierID vorkommen können. Die folgende
SQL-Anweisung zeigt, wie Sie mit DISTINCT eine Liste der Lieferanten erstellen können, die
mindestens einen Artikel liefern:
SELECT DISTINCT Suppliers.CompanyName AS Supplier
FROM Products INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID;
Ergebnis:
Supplier
Aux joyeux ecclésiastiques
Bigfoot Breweries
Cooperativa de Quesos '
Las Cabras'
Escargots Nouveaux
Exotic Liquids
Forêts d'
érables
...
Ohne DISTINCT würde die folgende Ausgabe resultieren:
Supplier
Aux joyeux ecclésiastiques
Aux joyeux ecclésiastiques
Bigfoot Breweries
Bigfoot Breweries
Bigfoot Breweries
...
Wenn Sie Abfragen in Access mit DISTINCT verwenden wollen, werden Sie einen
Unterschied zu Standard SQL feststellen. Access verwendet unverständlicherweise
DISTINCTROW an Stelle von DISTINCT, wenn mehrere Tabellen miteinander
verknüpft werden. Bei Access wird DISTINCTROW ignoriert, wenn die Abfrage nur
eine Tabelle umfasst, oder wenn Sie Felder von allen abgefragten Tabellen
ausgeben. In einer Abfrage über eine einzige Tabelle müssen Sie stattdessen
DISTINCT verwenden.
Die Data Manipulation Language 82
K/
"
+
7
Joins über mehr als zwei Tabellen sind nicht einfach zu schreiben und in den meisten SQLBüchern auch nicht beschrieben. Im Prinzip ist die Auflösung von Joins über mehr als zwei
Tabelle jedoch recht einfach: Die grundsätzliche Syntax eines Verbundausdrucks bezieht sich ja
auf Tabellenreferenzen:
Tabellenreferenz1 [NATURAL] [Verbundtyp] JOIN Tabellenreferenz2
Eine Tabellenreferenz kann nun auch das Ergebnis eines Joins sein. Der Übersicht halber
schreiben Sie diesen Join besser in Klammern.
Beispiel: Mit den Daten der Tabellen Products, Suppliers und Categories (Abbildung 3) soll
eine Liste erstellt werden, die den Artikelnamen, den Lieferanten- und den und Kategoriename
auflistet.
Abbildung 3: Auszug aus der Northwind-Datenbank
Der Join dazu sieht folgendermaßen aus:
SELECT Suppliers.CompanyName AS Supplier, Products.ProductName,
Categories.CategoryName AS Category
FROM Categories INNER JOIN (Products INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID)
ON Categories.CategoryID = Products.CategoryID
ORDER BY Suppliers.CompanyName, Products.ProductName;
Die Categories-Tabelle referenziert hier das Ergebnis des Joins zwischen Products und
Suppliers. Der Join zwischen Products und Suppliers wird natürlich zuerst ausgeführt. Sie
müssen also beachten, dass die Categories-Tabelle nur die Datensätze referenzieren kann, die
auch im Ergebnis des inneren Joins erscheinen. In diesem Beispiel werden z. B. nicht alle
Produkte ausgegeben, da bei einigen in der Spalte SupplierID der Wert Null gespeichert ist.
Die Data Manipulation Language 83
Ergebnis:
Supplier
Productname
Category
Aux joyeux ecclésiastiques
Chartreuse verte
Beverages
Aux joyeux ecclésiastiques
Côte de Blaye
Beverages
Bigfoot Breweries
Laughing Lumberjack Lager Beverages
Bigfoot Breweries
Sasquatch Ale
Beverages
Bigfoot Breweries
Steeleye Stout
Beverages
Cooperativa de Quesos '
Las Cabras' Queso Cabrales
Dairy Products
...
...
...
Die Auflösung des Problems, dass nicht alle Produkte erscheinen, ist natürlich wieder ein Outer
Join:
SELECT Suppliers.CompanyName AS Supplier, Products.ProductName,
Categories.CategoryName AS Category
FROM Categories LEFT OUTER JOIN (Products LEFT OUTER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID)
ON Categories.CategoryID = Products.CategoryID
ORDER BY Suppliers.CompanyName, Products.ProductName;
Der linke Left Outer Join ist aus logischer Sicht notwendig. Weiter unten finden
Sie Erläuterungen dazu.
Ergebnis:
Supplier
Productname
Category
NULL
Aniseed Syrup
Condiments
NULL
Chai
Beverages
NULL
Chang
Beverages
Aux joyeux ecclésiastiques Chartreuse verte
Beverages
Aux joyeux ecclésiastiques Côte de Blaye
Beverages
Bigfoot Breweries
Laughing Lumberjack Lager Beverages
...
...
...
Outer Joins in Abfragen über mehr als zwei Tabellen haben so ihre Tücken. In manchen Fällen
lässt das Datenbanksystem die Kombination von Inner und Outer Joins oder die Kombination
von Left und Right Outer Joins nicht zu oder, was viel schlimmer ist, die Kombination von
Inner und Outer Joins führt zu einem falschen Ergebnis.
Beispiel: Nun sollen nicht alle Produkte, sondern alle Lieferanten ausgegeben werden (auch
Lieferanten, die zurzeit gar keine Produkte liefern). Der erste Ansatz wäre vielleicht der
folgende:
SELECT Suppliers.CompanyName AS Supplier, Products.ProductName,
Categories.CategoryName AS Category
FROM Categories INNER JOIN (Products RIGHT OUTER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID)
ON Categories.CategoryID = Products.CategoryID
ORDER BY Suppliers.CompanyName, Products.ProductName;
Der SQL Server 7 (!) führt die Abfrage mit falschem Ergebnis aus (der Lieferant „Exotic
Liquids“ fehlt). Der SQL Server 2000 hat allerdings keine Probleme mit der Abfrage. Wenn Sie
Die Data Manipulation Language 84
diese Abfrage mal mit einer Access-Datenbank ausführen, meldet die Jet-Engine korrekterweise
den Fehler »Die Abfrage konnte nicht ausgeführt werden, da sie mehrdeutige
Inklusionsverknüpfungen enthält«.
Sie können keine Outer Joins mit Inner Joins kombinieren und davon ausgehen,
dass das Ergebnis korrekt ist. Dieses Verhalten ist recht logisch. Der innere Join
ergibt alle Lieferanten, d.h., auch die, die keine Produkte liefern. Die virtuelle
Spalte CategoryID dieser Datensätze enthält dann den Wert NULL. Ist der äußere
Join ein Inner Join, werden diese Datensätze natürlich nicht in das Endergebnis
übernommen.
Die Lösung des Problems ist die korrekte Verwendung von Outer Joins:
SELECT Suppliers.CompanyName AS Supplier, Products.ProductName,
Categories.CategoryName AS Category
FROM Categories RIGHT OUTER JOIN (Products RIGHT OUTER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID)
ON Categories.CategoryID = Products.CategoryID
ORDER BY Suppliers.CompanyName, Products.ProductName;
Die korrekte Verwendung der Left und Right Outer Joins ist hier sehr schwierig. Gehen Sie
dabei von innen nach außen vor: Ist der geklammerte Join ein Outer Join, muss der andere Join
ebenfalls ein Outer Join sein, der alle Datensätze des geklammerten Joins berücksichtigt.
Sie können sich die Arbeit zur Erstellung eines Joins oft erheblich erleichtern,
indem Sie das Abfrage-Tool von Access verwenden. In eine Access-Datenbank
können Sie zur Erstellung von Abfragen die benötigten Tabellen über ODBC
einbinden (bei Access 2000 auch über ADO). Der SQL Server Enterprise Manager
besitzt ebenfalls ein grafisch orientiertes Tool zur Abfragegenerierung, das Sie
allerdings nur verwenden können, wenn Sie eine neue Sicht erzeugen.
Die Data Manipulation Language 85
6
*
Sichten (Views) sind gespeicherte Abfragen (die oft auch als virtuelle Tabellen bezeichnet
werden), die sich aus einzelnen ausgewählten Tabellenspalten aus einer oder mehreren
Basistabellen zusammensetzen. Sichten werden erstellt, um dem Benutzer eine bestimmte
Auswahl von Spalten (auch aus verschiedenen Tabellen) innerhalb einer (virtuellen) Tabelle zur
Verfügung zu stellen. Der Benutzer kann mit dieser Sicht arbeiten wie mit einer Basistabelle.
Änderungen in den Spalten der Sicht werden immer auch in die Spalten der Basistabellen
übertragen (sofern die Sicht aktualisierbar ist). Genauso sind Änderungen der Basistabellen
natürlich in der Sicht sichtbar.
Über eine Sicht können Sie
•
komplexe Abfragen vor Clients verbergen. So können Sie zum Beispiel eine komplexe
Abfrage in einer Sicht speichern. Ein Client führt nur einen SELECT, UPDATE, INSERT oder
DELETE auf dieser Sicht aus und weiß nichts von der Komplexität der zugrundeliegenden
Abfrage.
•
Benutzern Zugriffsrechte auf Tabellen oder Spalten geben, auf die diese normalerweise
keine Rechte besitzen.
•
die Sicherheit komplexer verwalten, als dies mit Zugriffsrechten allein möglich ist (Beispiel
dazu siehe unten).
•
die Performance erhöhen, da eine Sicht in der Datenbank bereits kompiliert ist.
#$
Eine Sicht erzeugen Sie in SQL/92 über:
CREATE VIEW Viewname [(Spaltenliste)] AS
Select_Anweisung
[WITH [CASCADED | LOCAL] CHECK OPTION]
In der Abfrage geben Sie eine SELECT-Anweisung an. Basiert die Abfrage auf mehr als einer
Tabelle, können Sie die Spaltennamen für Spalten, deren Name mehrfach vorkommen, in der
optionalen Spaltenliste anpassen (einfacher wäre allerdings, die Spalten mit der AS-Klausel in
der Abfrage umzubenennen). Die CHECK OPTION wird weiter unten erläutert.
Beispiel:
CREATE VIEW Products_With_Category_Name AS
SELECT Products.ProductID, Products.ProductName,
Categories.CategoryName
FROM Products INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
0
Die Syntax des SQL Servers unterscheidet sich ein wenig von der SQL/92-Syntax:
CREATE VIEW Viewname [(Spaltenliste)] [WITH ENCRYPTION] AS
SELECT_Anweisung
[WITH CHECK OPTION]
Die Check-Option ist hier einfacher aufgebaut als bei SQL/92. Erläuterungen dazu finden Sie
weiter unten. Die Option WITH ENCRYPTION sorgt dafür, dass die Sicht in der Systemtabelle,
die für die Speicherung von Sichten verwendet wird, verschlüsselt gespeichert wird.
Sichten 86
/
*
Ein häufiges Problem bei Sichten ist, dass diese normalerweise nicht innerhalb der SELECTAnweisung sortiert werden können (ORDER BY ist in SQL/92 nicht zulässig). Sie können die
Sicht natürlich bei der Abfrage sortieren:
SELECT Feldliste FROM Sicht ORDER BY Feldliste
Der SQL Server 2000 ermöglicht aber das direkte Sortieren, allerdings nur dann, wenn die
Option TOP mit angegeben ist. Um eine Sicht zu sortieren, geben Sie also einfach TOP 100
PERCENT an:
CREATE VIEW Products_With_Category_Name AS
SELECT TOP 100 PERCENT Products.ProductID, Products.ProductName,
Categories.CategoryName
FROM Products INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
ORDER BY Products.ProductName
Dabei sollten Sie beachten, dass die Abfrage der Daten aufgrund der TOP-Option
u. U. länger dauert als ohne diese Option, was ich allerdings bisher nicht
nachweisen konnte.
L +
0/
*
Sichten werden verwendet wie Tabellen. Sie können Auswahlabfragen, aber auch
Aktualisierungs-, Anfüge- und Löschabfragen auf der Sicht ausführen. Voraussetzung dafür ist
allerdings, dass die der Sicht zugrundeliegende Abfrage das Aktualisieren, Anfügen bzw.
Löschen zulässt.
Das folgende Beispiel erzeugt eine Sicht, die alle Produkte der Kategorie 1 auflistet:
CREATE VIEW Products_Of_Cat1 AS
SELECT * FROM Products WHERE CategoryID = 1
Eine Auswahlabfrage würde dann z. B. folgendermaßen aussehen:
SELECT * FROM Products_Of_Cat1 WHERE SupplierID = 16
Eine Aktualisierungsabfrage würde z. B. so aussehen:
Update Products_Of_Cat1 SET UnitPrice = UnitPrice * 1.2
WHERE SupplierID = 16
Bei der Abfrage müssen Sie bedenken, dass die Sicht die Datensätze eventuell bereits
einschränkt. Die Beispiel-Sicht zeigt z. B. grundsätzlich nur die Produkte der Kategorie 1. Eine
Abfrage auf die Sicht berücksichtigt dann natürlich nur diese Produkte.
'
* B- /
Normalerweise erlaubt eine Sicht die enthaltenen Datensätze so zu ändern, dass diese aus der
Sicht entfernt werden und neue Datensätze anzufügen, die nicht in der Sicht erscheinen.
Beispiel: Ändern der Kategorie der Produkte, die vom Lieferant 16 geliefert werden:
UPDATE Products_Of_Cat1 SET CategoryID = 2 WHERE SupplierID = 16
Beispiel: Einfügen eines neuen Produkts der Kategorie 2:
INSERT INTO Products_Of_Cat1
VALUES ('Test', 16, 2, NULL, 11.25, 0, 0, 10, 0)
Sichten 87
Dieses nicht intuitive Verhalten der Sicht können Sie mit der Check-Option unterbinden:
CREATE VIEW Products_Of_Cat1 AS
SELECT * FROM Products WHERE CategoryID = 1
WITH CHECK OPTION
Eine Abfrage, die die enthaltenen Datensätze so ändert, dass diese aus der Sicht entfernt würden
oder neue Datensätze anfügt, die nicht in der Sicht erscheinen würden, schlägt nun mit einer
Fehlermeldung fehl.
In SQL/92 können Sie bei der Check-Option noch den Zusatz LOCAL oder CASCADED angeben.
Mit der Option LOCAL wird die Bedingung der Sicht nur gegen die Sicht selbst geprüft. Mit der
Option CASCADED werden auch alle Bedingungen von Sichten mit geprüft, auf denen die Sicht
basiert. Da Sie wahrscheinlich selten Sichten definieren, die auf Sichten basieren und die
meisten Datenbanksysteme diese Optionen gar nicht kennen, ist das Verständnis dieser
Optionen nicht allzu wichtig.
Sichten 88
9
'/
/
9
Jedes Objekt in einer Datenbank hat normalerweise einen Eigentümer (Owner). Der Eigentümer
eines Objekts hat normalerweise alle Rechte auf diesem Objekt. Zusätzlich können allen
anderen Benutzern Zugriffsrechte auf diesen Objekten vergeben werden. Daneben werden
Benutzern noch allgemeine Rechte, wie z. B. das Recht Tabellen anzulegen, erteilt. Die
Zugriffsrechte werden in SQL/92 folgendermaßen definiert:
+
*
Recht
Bedeutung
CREATE DATABASE
Recht, Datenbanken zu erzeugen
CREATE TABLE
Recht, Tabellen zu erzeugen
CREATE VIEW
Recht, Sichten zu erzeugen
Tabelle 6.1: Die Anweisungs-Rechte von SQL/92
*
B @
Recht
Bedeutung
USAGE
Recht zur Nutzung einer spezifischen Domäne
SELECT
Recht zur Abfrage einer spezifischen Tabelle
INSERT(Spalte)
Recht zum Einfügen in eine bestimmte Spalte einer spezifischen Tabelle
INSERT
Recht zum Einfügen in alle Spalten einer spezifischen Tabelle
UPDATE(Spalte)
Recht zum Aktualisieren einer bestimmten Spalte einer spezifischen
Tabelle
UPDATE
Recht zum Aktualisieren aller Spalten einer spezifischen Tabelle
DELETE
Recht zum Löschen aus einer spezifischen Tabelle
REFERENCES(Spalte)
Recht zur Referenzierung einer bestimmten Spalte einer spezifischen
Tabelle
REFERENCES
Recht zur Referenzierung aller Spalten einer spezifischen Tabelle
Tabelle 6.2: Rechte für einzelne Objekte bei SQL/92
Die Data Control Language 89
9
L +
2
0/
*
Anweisungs- und Objekt-Rechte werden mit der GRANT-Anweisung vergeben:
GRANT {ALL | Anweisungsliste} TO Benutzerliste
GRANT Rechteliste ON Objekt TO Benutzerliste [WITH GRANT OPTION]
Beispiel: Erteilen des SELECT- und UPDATE-Rechts auf der Tabelle Orders für den Benutzer
Zaphod:
GRANT SELECT, UPDATE ON Orders TO Zaphod
Die Option WITH GRANT OPTION bewirkt, dass der Benutzer das Recht erhält, selbst Rechte auf
diesem Objekt zu vergeben. Zunächst dürfen immer nur der Objekteigentümer und in der Regel
auch der Administrator der Datenbank Rechte vergeben.
Rechte können mit der REVOKE-Anweisung wieder entfernt werden:
REVOKE [GRANT OPTION FOR] Rechteliste
ON Objekt FROM Benutzerliste {RESTRICT | CASCADE}
Die Data Control Language 90
<
<
0
*
Bowman, Judith S., Emerson, Sandra L., Darnovsky Marcy: The practical SQL Handbook.
Using Structured Query Language. Addison-Wesley Developers Press. 1997.
Date, Chris J, Darwen Hugh: SQL-Der Standard. SQL/92 mit den Erweiterungen CLI und PSM.
Addison Wesley München. 1999. Anmerkung: Standardwerk, aber kompliziert geschrieben und
der Autor verweist sehr häufig für Erklärungen auf andere Kapitel.
Soukup, Ron, Delaney, Kalen: Inside Microsoft SQL Server 7.0. Microsoft Press Redmond,
Washington. 1999.
Delaney, Kalen: Inside Microsoft SQL Server 2000. Microsoft Press Redmond, Washington.
2001.
Anhang 91
=
>
ABS-Funktion 63
COS-Funktion 63
ACOS-Funktion 63
COT-Funktion 63
Aggregatfunktionen 56
COUNT_BIG-Funktion 58
Aktualisierungsweitergabe 25
COUNT-Funktion 57
ALL 66
CREATE DATABASE-Anweisung 14
ALL-Operator 69
CREATE SCHEMA-Anweisung 13
ALTER DATABASE-Anweisung 18
CREATE TABLE-Anweisung (SQL Server) 23
ANSI 1
CREATE TABLE-Anweisung. 20
ANSI-SQL 2
CROSS JOIN 75
ANY-Operator 68
CURRENT_DATE-Funktion 59
Arithmetische Ausdrücke 48
CURRENT_TIME-Funktion 59
ARRAY-Datentyp 8
CURRENT_TIMESTAMP-Funktion 59
ASCII-Funktion 64
CURRENT_USER-Funktion 59, 60, 65
ASIN-Funktion 63
cursor-Datentyp 10
AS-Klausel 42
Data Control Language 2, 89
ATAN2-Funktion 63
Data Definition Language 2
ATAN-Funktion 63
Data Manipulation Language 2, 33
AVG-Funktion 57
DATALENGTH-Funktion 65
Bedingungen 43
DATEADD-Funktion 62
Benutzerdefinierte Datentypen 20
DATE-Datentyp 8
Bereichssuche 47
DATEDIFF-Funktion 62
BETWEEN-Operator 47
DATE-Funktion 61
bigint-Datentyp 9
DATENAME-Funktion 62
binary-Datentyp 10
Datenbanken
BIT VARYING-Datentyp 6
Anlegen 13
BIT_LENGTH-Funktion 59
Dateien hinzufügen 19
bit-Datentyp 10
Dateien löschen 19
BIT-Datentyp 6
Dateigruppen 15
BOOLEAN-Datentyp 8
Dateigruppen hinzufügen und löschen 19
CASE-Funktion 59, 61
Einstellungen verändern 19
CAST-Funktion 59
Löschen 17
CEILING-Funktion 63
Modifizieren 18
CHAR_LENGTH-Funktion 59
Datensätze
CHARACTER VARYING-Datentyp 6
Abfragen 40
CHARACTER-Datentyp 6
Ändern 38
CHAR-Datentyp 6
Anfügen 33
CHARINDEX-Funktion 64
Anfügen mit Werten aus anderen Tabellen 34
Check-Option 87
Löschen 36
CHECKSUM_AGG-Funktion 58
Datentypen 6
COALESCE-Funktion 65
ARRAY 8
Constraints Siehe Einschränkungen
bei Abfragen 45
CONVERT-Funktion 59
bigint 9
Index 92
binary 10
Datums- und Zeitdatentypen 8
bit 10
DAY-Funktion 61, 62
BIT 6
DCL 2, 89
BIT VARYING 6
DDL 2
BOOLEAN 8
DEC-Datentyp 6
CHAR 6
DECIMAL-Datentyp 6
CHARACTER 6
DEGREES-Funktion 63
CHARACTER VARYING 6
DELETE-Anweisung 36
cursor 10
DIFFERENCE-Funktion 64
DATE 8
DISTINCT 66, 82
datetime 9
DML 2, 33
Datums- und Zeitdatentypen 8
Domänen 19
DEC 6
DOUBLE PRECISION-Datentyp 7
DECIMAL 6
DOUBLE-Datentyp 7
DOUBLE 7
DROP DATABASE-Anweisung 17
DOUBLE PRECISION 7
DROP SCHEMA-Anweisung 17
FLOAT 7
DROP TABLE-Anweisung 30
image 10
Eigentümer 89
INT 6
Einschränkungen 21
INTEGER 6
beim SQL Server 25
LARGE OBJECT 8
Deaktivieren und aktivieren 30
LOB 8
für die Tabelle 22
money 9
für Spalten 21
nchar 9
Hinzufügen 29
ntext 10
Löschen 29
NUMERIC 6
Equijoin 76
nvarchar 9
EXISTS-Operator 70
ROW 8
EXP-Funktion 63
smalldatetime 9
FLOAT-Datentyp 7
SMALLINT 7
FLOOR-Funktion 63
smallmoney 9
Full Outer Join 79
sql_variant 10
Funktionen 56
sysname 10
GETDATE-Funktion 62
table 10
GETUTCDATE-Funktion 62
text 10
GROUP BY-Klausel 49
TIME 8
GROUPING-Funktion 58
timestamp 10
Gruppieren 49
TIMESTAMP 8
GUID-Werte 24
tinyint 9
HAVING-Klausel 50
uniqueidentifier 10
image-Datentyp 10
varbinary 10
Index
VARCHAR 6
DATEPART-Funktion 62
datetime-Datentyp 9
Erstellen 31
Löschen 32
Indizes 31
Index 2
INNER JOIN 75
ON UPDATE CASCADE 25
IN-Operator 46
Operatoren 43
INSERT-Anweisung 33
ORDER BY-Klausel 49
INT-Datentyp 6
Outer Join 77, 79, 84
INTEGER-Datentyp 6
Korrekte Verwendung 85
ISDATE-Funktion 65
Owner 89
ISNULL-Funktion 65
PATINDEX-Funktion 64
ISNUMERIC-Funktion 65
PI-Funktion 63
IS-Operator 47
POSITION-Funktion 60
Joins 70
POWER-Funktion 63
Karthesisches Produkt 75
Projektion 41
Kataloge 13
QUOTENAME-Funktion 64
Konventionen 3
RADIANS-Funktion 63
LARGE OBJECT-Datentyp 8
RAND-Funktion 63
Left Outer Join 79
REPLACE-Funktion 64
LEFT-Funktion 64
REPLICATE-Funktion 64
LEN-Funktion 64
Restriktion 41
LIKE-Operator 46
REVERSE-Funktion 64
Literale 11
Right Outer Join 79
Datentypen 12
RIGHT-Funktion 64
LOB-Datentyp 8
ROUND-Funktion 63
LOG10-Funktion 63
ROW-Datentyp 8
LOG-Funktion 63
RTRIM-Funktion 64
Löschweitergabe 25
Schemata 13
LOWER-Funktion 59, 64
SELECT-Anweisung 40
LTRIM-Funktion 64
SEQUEL 1
MAX-Funktion 57
SESSION_USER-Funktion 60
Mengensuche 46
Sichten 86
MIN-Funktion 57
Check-Option 87
money-Datentyp 9
SIGN-Funktion 63
MONTH-Funktion 61, 62
SIN-Funktion 63
Mustersuche 46
Skalare Funktionen 59
Natürlicher Join 76
smalldatetime-Datentyp 9
nchar-Datentyp 9
SMALLINT-Datentyp 7
NCHAR-Funktion 64
smallmoney-Datentyp 9
NEWID-Funktion 65
Sortieren 49
NOT NULL 25
SOUNDEX-Funktion 64
ntext-Datentyp 10
SPACE-Funktion 64
NULL-Werte 25
Spalten
NUMERIC-Datentyp 6
Ändern 28
nvarchar-Datentyp 9
Hinzufügen 27
Ocelot 2
Löschen 28
OCTET_LENGTH-Funktion 60
ON DELETE CASCADE 25
Spalteneinschränkungen 21
SQL Server 25
Index 3
SQL/86 1
Thetajoin 76
SQL/89 1
TIME-Datentyp 8
SQL/92 1
timestamp-Datentyp 10
sql_variant-Datentyp 10
TIMESTAMP-Datentyp 8
SQRT-Funktion 63
tinyint-Datentyp 9
SQUARE-Funktion 63
TOP 67
STDEV-Funktion 58
TRANSLATE-Funktion 60
STDEVP-Funktion 58
TRIM-Funktion 60
STR-Funktion 64
UNICODE-Funktion 65
STUFF 65
uniqueidentifier-Datentyp 10
STUFF-Funktion 65
Unterabfragen 67
Subquerys 67
UPDATE-Anweisung 38
SUBSTRING-Funktion 60, 65
UPPER-Funktion 60, 65
SUM-Funktion 57
USER-Funktion 60
sysname-Datentyp 10
USING-Klausel 72
SYSTEM_USER-Funktion 60
varbinary-Datentyp 10
Tabelle
VARCHAR-Datentyp 6
Löschen 30
Tabellen
Anlegen 20
Tabelleneinschränkungen 22
SQL Server 26
VAR-Funktion 58
VARP-Funktion 58
Verbundausdrücke 70
Views 86
WEEKDAY-Funktion 61, 62
table-Datentyp 10
WHERE-Klausel 43
TAN-Funktion 63
YEAR-Funktion 61, 62
text-Datentyp 10
Index 4