Einführung in SQL

Einführung in SQL
Jörn Lenhardt, Dipl. Ing. (FH)
1
EINFÜHRUNG .................................................................................................... 4
1.1
Historie .................................................................................................................................................... 4
1.2
Datenbanken ........................................................................................................................................... 4
1.3
Das relationale Modell ............................................................................................................................ 4
1.4
Normalisierung ....................................................................................................................................... 6
1.5
Die erste Normalform ............................................................................................................................. 6
1.6
Die zweite Normalform .......................................................................................................................... 7
1.7
Die dritte Normalform............................................................................................................................ 8
1.8
Integrität .................................................................................................................................................. 8
2
2.1
ABFRAGEN VON DATEN .................................................................................. 9
Einführung .............................................................................................................................................. 9
2.2
Einfache Abfragen .................................................................................................................................. 9
2.2.1
Spaltenaliase .................................................................................................................................. 10
2.2.2
Ändern der Spaltendaten beim Lesen ............................................................................................ 10
2.3
Einschränkung der Ergebnismenge .................................................................................................... 10
2.3.1
Die WHERE Klausel ..................................................................................................................... 10
2.3.2
Die IN Operator............................................................................................................................. 11
2.3.3
Die BETWEEN Operator .............................................................................................................. 12
2.4
Skalarfunktionen .................................................................................................................................. 13
2.4.1
Numerische Skalarfunktionen ....................................................................................................... 13
2.4.2
Zeichenketten Skalarfunkionen ..................................................................................................... 13
2.5
Aggregatfunktionen .............................................................................................................................. 13
2.5.1
Die COUNT Funktion ................................................................................................................... 14
2.5.2
Die SUM Funktion ........................................................................................................................ 14
2.5.3
Die MIN, MAX Funktionen .......................................................................................................... 14
2.5.4
Die AVG Funktion ........................................................................................................................ 15
2.5.5
Spalten mit und ohne Gruppierungsfunktion abfragen .................................................................. 15
2.6
Sortierung .............................................................................................................................................. 15
2.7
Gruppierungsfunktionen in WHERE Klauseln ................................................................................. 16
2.8
Kombination verschiedener Klauseln ................................................................................................. 16
Einführung in SQL
30.8.2001
Seite 2 von 31
2.9
ACCESS Ergänzungen ......................................................................................................................... 17
2.9.1
Selbstdefinierte VBA Funktionen ................................................................................................. 17
3
VIEWS ............................................................................................................... 18
3.1
Allgemeines ............................................................................................................................................ 18
3.2
Erstellen einer View .............................................................................................................................. 18
3.3
Lesen aus einer View ............................................................................................................................ 19
3.4
Ändern der Daten ................................................................................................................................. 19
3.5
ACCESS Ergänzungen ......................................................................................................................... 19
3.5.1
Abfragen........................................................................................................................................ 20
4
VERKNÜPFUNG VON TABELLEN .................................................................. 20
4.1
Equi Joins .............................................................................................................................................. 21
4.2
Non Equi Join ........................................................................................................................................ 23
4.3
Inner Join .............................................................................................................................................. 23
4.4
Outer Joins ............................................................................................................................................ 23
4.4.1
Left Outer Join .............................................................................................................................. 23
4.5
Right Outer Join ................................................................................................................................... 24
4.6
Tabellen mit sich selbst verknüpfen .................................................................................................... 25
5
UNTERABFRAGEN .......................................................................................... 26
5.1
Exists ...................................................................................................................................................... 27
5.2
Any / Some ............................................................................................................................................. 28
5.3
Korrelierende Unterabfragen .............................................................................................................. 28
6
ANHANG ........................................................................................................... 30
6.1
Tabellenverzeichnis .............................................................................................................................. 30
6.2
SQL Beispiele Verzeichnis ................................................................................................................... 30
6.3
Abbildungsverzeichnis.......................................................................................................................... 31
6.4
Quellen ................................................................................................................................................... 31
Einführung in SQL
30.8.2001
Seite 3 von 31
1 Einführung
Dieses Dokument soll eine kleine Einführung in die Datenbankabfragesprache SQL
darstellen.
Es behandelt die Grundlagen der Datenabfrage und der Datenänderung. Hierzu
gehören die Befehle SELECT, INSERT, UPDATE und DELETE. Dies sind die von
den Anwendern einer Datenbank am häufigsten genutzten Befehle.
Auf administrativen Tätigkeiten, wie das Erstellen von Tabellen, das Anlegen von
Indizes oder der Erstellung von referentieller Integrität wird bewußt nicht
eingegangen. Hierzu wird auf anderen Dokumente verwiesen.
Nach dem Durcharbeiten dieses Dokumentes soll der Anwender in die Lage versetzt
werden, Daten aus bestehenden Datenbanken abzufragen, entsprechend
aufzubereiten und manipulieren zu können.
Angesprochen werden hierin die einfachen Abfrage einer Tabelle, Skalarfunktionen
und Aggregatfunktionen, die Verknüpfung mehrerer Tabellen und Unterabfragen.
Desweiteren wird das Hinzufügen, das Ändern und das Löschen von Daten
aufgezeigt.
Berücksichtigt werden die Datenbanken von Microsoft (Access und SQL-Server) und
Oracle.
1.1 Historie
Die Sprache SQL (Structured Query Language) ist die de facto Standardabfragesprache für relationale Datenbanken. Sie entstand Ende der 70er Jahre bei
IBM für ihre Datenbank DB2.
SQL ist eine nicht prozedurale Sprache, d.h. man beschreibt das „was“, und nicht
das „wie“.
Relational bedeutet, das die Daten untereinander in Beziehung stehen. Durch
mengenalgebraische Operationen werden die Daten bearbeitet (abgefragt, eingefügt,
geändert, gelöscht).
Die Organisationen ANSI und ISO haben versucht, diese Sprache zu
standardisieren, aber wenige Hersteller halten sich an diese Normung.
1.2 Datenbanken
In den geschäftlichen und privaten Bereichen werden Daten bearbeitet, die
gespeichert werden müssen. Zur Speicherung dieser Daten hat man Datenbanken
entwickelt. Dies ist im Prinzip eine geordnete Sammlung von Daten, den
Informationen. Durch die rasante Entwicklung im PC/Homecomputerbereich werden
die Datenbanken immer mehr vom Großrechner auf die PCs verlagert und somit für
jeden Anwender erschwinglich.
1.3 Das relationale Modell
Im Jahre 1970 veröffentlichte Dr. E. F. Codd den Artikel „A Relational Model of Data
for Large Shared Data Banks“, der als Basis des relationalen Modells gilt. Er stützt
sich hierin auf math. Konzepte der relationalen Algebra, um Daten in Mengen und
untereinander in Beziehung stehende Untermengen aufzuteilen.
Einführung in SQL
30.8.2001
Seite 4 von 31
Informationen werden in verschiedenen Mengen gruppiert. Das relationale Modell
gliedert die Daten in Sätze und stellt diese in Form einer Tabelle dar. Diese
Tabellenstruktur besteht also aus einzelnen Datenelementen (Spalten oder Felder).
Als Datensatz bezeichnet man einen einzelnen Satz (Zeile), der eine Gruppe von
Feldern zusammenfaßt.
Tabelle
Spalte 1
Spalte 2
Spalte 3
A
E
I
B
F
J
C
G
K
D
H
L
Datum
Datensatz
(Zeile)
gleicher
Datentyp
Abbildung 1 - Aufbau einer Tabelle
Eine Relation ist als eine logisch zusammenhängende Objektmenge zu verstehen,
die in einer mn Matrix (Tabelle) festgehalten wird.
R  W 1  W 2  ...  Wm
Jedes Element r einer Relation muß aus einer geordneten Menge einzelner Werte w1
 W 1 ; w2  W 2 ; ... zusammengesetzt sein. Formal gilt
r = (w1, w2, ..., wn)
Auf eine Datenbank übertragen läßt sich festhalten:
Jede Relation ist eine Menge von Entitäten (oder Datensätzen) r. Jede Entität hat
verschiedene Eigenschaften, bzw. Attribute w, die den Spalten der Matrix R
entsprechen. Ein Attribut w ums sich im Wertebereich W  befinden.
Beispiel: Relation Kunde
KUNDE (Nummer, Name, Adresse, ...)
Die Relation Kunde wird durch die Attribute Nummer, Name, Adresse, ...
beschrieben. Einer Entität r wird jede dieser Attribute ein Wert aus der
jeweiligen Wertemenge zugeordnet (Kundennummer von 1 bis 9999)
Einführung in SQL
30.8.2001
Seite 5 von 31
Hinter diesem Ansatz steht, das die Daten in Tabellen (Relationen) abgelegt werden,
die in ihrer Gesamtheit die eigentliche Datenbank darstellen. Zu beachten ist, das die
Daten möglichst in redundanzfreier Form gespeichert werden sollen und
Beziehungen zwischen den Relationen berücksichtigt werden müssen.
Die Beziehung der Entitäten zueinander werden mit Hilfe von Schlüsselattributen
hergestellt. Jede Relation hat deshalb einen identifizierenden Primärschlüssel. Mit
Hilfe eines Bezugsschlüssels (foreign key) kann eine Beziehung zwischen den
Relationen hergestellt werden.
Hierbei unterscheidet man zwischen 3 Beziehungstypen
 In einer 1:1 Beziehung steht jede Entität Ex mit höchstens einer Entität Ey in
Beziehung B1 zueinander (Bsp.: jede Abteilung hat höchstens einen
Abteilungsleiter)
 Bei jeder 1:n Beziehung sind jeder Entität Ex n (mit n  0) Entitäten Ey mit der
Beziehung Bn zugeordnet. Umgekehrt steht jedes Ey mit höchstens einer
Entität Ex in Beziehung (Bsp.: Ein Abteilungsleiter „regiert“ eine Anzahl von
Mitarbeitern, ein Mitarbeiter wird von einem Abteilungsleiter „regiert“)
 Eine Beziehung vom Typ m:n liegt vor, wenn jede Entität E x mit n (n  0)
Entitäten Ey in Beziehung Bn steht und jede Entität Ey mit m (m  0) Entitäten
Ex in Beziehung Bm steht. (Bsp.: Mitarbeiter arbeiten an mehreren Projekten
und ein Projekt wird von mehreren Mitarbeitern bearbeitet)
1.4 Normalisierung
Die Normalisierung ist ein Prozeß. Bei dem die zu bearbeitenden Daten in ein
logisches Modell gebracht werden. Dieses logische Modell wird daraufhin verfeinert,
das die Bearbeitung der Daten optimiert wird. Hierbei unterscheidet man 3 Formen
der Normalisierung, die im folgenden aus einem Beispiel heraus dargestellt werden.
Als Beispiel soll eine Arbeitertabelle dienen, die folgende Einträge hat
Name Geburtsdatum
Adresse
Heinz
01.01.70 Bad
Sepp
01.07.80 Dürkheim
Freinsheim
Jupp
04.09.75 Wachenheim
Jochen
04.03.60 Deidesheim
Jürgen
05.08.73 Forst
Thomas
03.03.69 Herxheim
Hans
02.04.68 Frankental
Manager
Josef
Josef
Karl
Karl
Karl
Karl
Josef
Faehigkeit1 Faehigkeit2 Faehigkeit3
malen
techn.
rechnen
Zeichnen
kreativ
organisieren unterhalten
planen
schreiben
Geographie
Tabelle 1 - Die Arbeitertabelle
1.5 Die erste Normalform
Im ersten Schritt werden die Daten in separate Tabellen verschoben, wobei
gleichartige Daten in eigene Tabellen zusammengefaßt werden. Jede Tabelle
bekommt einen Primärschlüssel, einen eindeutigen Kennzeichner oder Bezeichner.
So kann man z.B. die Fähigkeiten eines Arbeiters in einer eigenen Tabelle
zusammenfassen. Man kann dann pro Arbeiter mehr als drei Fähigkeiten angeben,
Einführung in SQL
30.8.2001
Seite 6 von 31
anstatt in der Arbeitertabelle mehr als einen Eintrag für einen Arbeiter einzufügen.
Als nächstes definiert man dann pro Tabelle einen Primärschlüssel. Dies ist ein für
diese Tabelle eindeutiger Bezeichner. Somit erhält man eine neue Arbeitertabelle
und eine Fähigkeitentabelle, die hier aufgeführt sind.
Name Geburtsdatum
Adresse
Manager
Heinz
01.01.70 Bad Dürkheim Josef
Sepp
01.07.80 Freinsheim
Josef
Jupp
04.09.75 Wachenheim Karl
Jochen
04.03.60 Deidesheim
Karl
Jürgen
05.08.73 Forst
Karl
Thomas
03.03.69 Herxheim
Karl
Hans
02.04.68 Frankental
Josef
Tabelle 2 - Die Arbeitertabelle (1NF)
Name
Hans
Heinz
Jupp
Jupp
Jupp
Jürgen
Sepp
Sepp
Thomas
Faehigkeit
Geographie
malen
kreativ
organisieren
unterhalten
planen
rechnen
techn. Zeichnen
rechnen
Beschreibung
Kennt sich überall gut aus
Landschaftsbilder
viele gute Ideen
guter Überblick
kennt Witze
gute Terminvoraussagen
Analysis, Algebra
Motorenbilder
Numerik
Tabelle 3 - Die Fähigkeitentabelle (1NF)
Primärschlüssel der Arbeitertabelle ist der Name, die beiden Spalten Bame und
Fähigkeit bilden bei der Fähigkeitentabelle den Primärschlüssel.
Die Fähigkeitentabelle hat noch einen Nachteil. Die Beschreibung der Fähigkeit
hängt von der Fähigkeit ab, nicht vom Namen. Dieser Nachteil wird in einem weiteren
Normalisierungsschritt eliminiert.
1.6 Die zweite Normalform
Die zweite Normalform entfernt die Daten aus einer Tabelle, die nur von einem Teil
des Primärschlüssels abhängen. Also wird hier die Fähigkeit und die Beschreibung
dieser in getrennten Tabellen untergebracht.
Wenn die Fähigkeit und die Beschreibung in der 1NF gelassen werden, dann kann
nur eine Beschreibung für eine Fähigkeit angegeben werden, d.h. man kann keine
zwei Beschreibungen für eine Fähigkeit angeben (vgl. rechnen: Analysis, Algebra vs.
Numerik)
Wenn ein Arbeiter mit einer besonderen Fähigkeit die Stadt verläßt und damit aus
der Datenbank entfernt wird, dann wird auch diese Fähigkeit aus der Datenbank
gelöscht. Mit der 2NF kann die Datenbank diese Fähigkeit speichern, obwohl zur Zeit
niemand diese Fähigkeit besitzt.
Einführung in SQL
30.8.2001
Seite 7 von 31
Name
Hans
Heinz
Jochen
Jupp
Jürgen
Sepp
Thomas
Geburtsdatum
02.04.68
01.01.70
04.03.60
04.09.75
05.08.73
01.07.80
03.03.69
Adresse
Frankental
Bad Dürkheim
Deidesheim
Wachenheim
Forst
Freinsheim
Herxheim
Manager
Josef
Josef
Karl
Karl
Karl
Josef
Karl
Tabelle 4 - Die Arbeitertabelle 2NF
Faehigkeit
Geographie
kreativ
malen
organisieren
planen
rechnen
techn. Zeichnen
unterhalten
Tabelle 5 - Tabelle Fähigkeiten 2NF
Name
Hans
Heinz
Jupp
Jupp
Jupp
Jürgen
Sepp
Sepp
Thomas
Faehigkeit
Geographie
malen
kreativ
organisieren
unterhalten
planen
rechnen
techn. Zeichnen
rechnen
Beschreibung
kennt sich überall gut aus
Landschaftsbilder
viele gute Ideen
guter Überblick
kennt Witze
gute Terminvoraussagen
Analysis, Algebra
Motorenbilder
Numerik
Tabelle 6 - Die Arbeiter-Fähigkeiten-Tabelle 2NF
1.7 Die dritte Normalform
In dieser dritten Normalform, die man für jedes Modell anstreben soll, werden alle
Elemente aus den Tabellen entfernt, die nicht unmittelbar vom Primärschlüssel
abhängen.
... überspringen wir
1.8 Integrität
... überspringen wir
Einführung in SQL
30.8.2001
Seite 8 von 31
2 Abfragen von Daten
2.1 Einführung
Eine der Hauptaufgaben von Datenbanken ist das zur Verfügung stellen der
gespeicherten Informationen. Der Anwender hat mit der Abfragesprache SQL ein
mächtiges Werkzeug, mit dem er die Daten aus relationalen Datenbanken abfragen
kann.
Grundvoraussetzung ist jedoch, das der Anwender die gespeicherten Daten kennt.
Das heißt, das der Anwender – bevor er eine Abfrage stellt – sich mit den Strukturen
in der Datenbank auseinandersetzt. Er muß mindestens die Tabellen und die
Schlüssel (Primär- als auch Fremdschlüssel) kennen.
Für das Abfragen von Informationen aus relationalen Datenbanken stellt SQL den
Befehl SELECT zur Verfügung. Dieses Kapitel beschreibt die vielfältigen
Einsatzmöglichkeiten dieses komplexen Befehls.
Der Befehl SELECT generiert aus den gespeicherten Daten und den Kriterien des
Befehls eine Ergebnismenge, die dann am Bildschirm sichtbar gemacht wird. Man
kann sich die Ergebnismenge so vorstellen, als ob der SELECT Befehl eine
temporäre Tabelle erzeugt und diese dem Anwender präsentiert. Diese Vorstellung
ist wichtig für das Verständnis einiger Aspekte, die wir weiter unten ansprechen
werden.
2.2 Einfache Abfragen
Unter einen einfachen Abfrage versteht man das Auslesen von Rohdaten aus einer
einzigen Tabelle. Man fragt also nur die Attribute aller Entitäten einer Relation ab.
Hierzu benutzt man den SQL Befehl SELECT, dem man die abzufragenden Spalten
und den Tabellennamen mitgibt.
Die Syntax hierzu lautet
SELECT Spalte [, Spalte]{0..*} FROM Tabelle
SQL unterscheidet nicht zwischen Groß- und Kleinschreibung. Somit ist es egal, ob
man die Schlüsselwörter ganz in Großbuchstaben, ganz in Kleinbuchstaben oder
gemischt schreibt. Genauso ist es nicht notwendig, den Befehl in eine einzige Zeile
zu schreiben. Da es dem SQL Interpreter egal ist, in welcher Form die befehle
formuliert sind, sollte man sich angewöhnen, eine Formatierung zu verwenden. Dies
hilft dem besseren Verständnis des Befehls und der Lesbarkeit enorm.
Beispiel: Lesen der Kundendaten
SELECT
Nummer,
Name,
Vorname,
Jahre
FROM
Einführung in SQL
30.8.2001
Seite 9 von 31
Kunde
SQL 1 - Lesen der Kundendaten
2.2.1 Spaltenaliase
Es ist in SQL möglich, den Originalspaltennamen einen anderen Namen zu geben,
den Alias. Den Aliasnamen schreibt man hinter den eigentlichen Spaltennamen.
Sinnvoll wird dies, wenn die Spaltendaten vor der Ausgabe weiterverarbeitet werden.
Syntaktisch kann die Spaltenangabe somit so erfolgen
Spalte = Name [ [ As ] Aliasname ]
Beispiel: Umbenennen der Spaltennamen für Kundendaten
SELECT
Nummer
Name
As Nummer,
As Name,
Vorname
Jahre
As Vorname,
As ‘Alter‘
FROM
Kunde
SQL 2 - Lesen der Kundendaten mit Spaltenalias
2.2.2 Ändern der Spaltendaten beim Lesen
Der Inhalt der Spalten der Ergebnismenge kann direkt durch die Abfrage geändert
werden, ohne der Originalinhalt ändern zu müssen. Hierbei wird die Spalte z.B. mit
einem arithmetischen Ausdruck verknüpft. Man macht sich den Umstand zu Nutze,
das die Ergebnismenge fast unabhängig von den Originaldaten ist.
Beispiel: Kundenalter in fünf Jahren
SELECT
Nummer
Name
As Nummer,
As Name,
Vorname
Jahre + 5
As Vorname,
As Alter_In_5_Jahren
FROM
Kunde
SQL 3 - Ändern der Spalteninhalte der Ergebnismenge
2.3 Einschränkung der Ergebnismenge
Um nicht alle Datensätze einer Tabelle als Ergebnis zu erhalten, kann man diese
Menge durch eine Klausel einschränken.
2.3.1 Die WHERE Klausel
Mit Hilfe der WHERE Klausel kann die Ergebnismenge eingeschränkt werden. Dieser
Klausel gibt man eine Bedingung an, der die Ergebnismenge genügen muß. Diese
Einführung in SQL
30.8.2001
Seite 10 von 31
Bedingung besteht normalerweise aus einzelnen Termen, die mit logischen
Operatoren verknüpft sind.
SELECT Spalten(n) FROM Tabelle WHERE Bedingung
Ein Term dieser Bedingung ist aufgebaut „A Operator B“, wobei A und B Spalten,
Literale oder wiederum Bedingungen sein können. Als Operatoren kommen die
Basisvergleichsoperatoren Gleich (=), Ungleich (<>), Größer (>) und Kleiner (<) oder
deren Kombination in Frage. Weitere Operatoren werden an geeigneter Stelle
vorgestellt.
Beispiel: Abfragen bestimmter Kunden
SELECT
Name,
Vorname
FROM
Kunde
WHERE
Name = ’Schmitt’
SQL 4 - Einschränkung der Ergebnismenge
Die Bedingungsterme werden i.d.R. durch die logischen Operatoren AND, OR oder
NOT miteinander verknüpft. Somit können mehrere Einschränkungen der
Ergebnismenge parallel erfolgen.
Stringliterale werden dabei in einfache Hochkommata gesetzt und sind CaseSensitiv. Zahlen werden direkt angegeben, man sollte jedoch das Format beachten.
Formate der Datumsangaben sind von Datenbank zu Datenbank verschieden
Beispiel: Abfragen bestimmter Kunden (2)
SELECT
Name,
Vorname
FROM
Kunde
WHERE
Name = ’Schmitt’
AND Vorname = ’Friedhelm’
SQL 5 - Einschränkung der Ergebnismenge (2)
2.3.2 Die IN Operator
Der IN Operator ist eine weitere Schreibweise eines speziellen OR Operators. Bei
diesem Operator beziehen sich die Terme auf eine Spalte. Anstelle mehrerer, mit
OR verknüpfter Terme zu formulieren, kann man einen einzigen Term mit dem In
Operator formulieren.
Einführung in SQL
30.8.2001
Seite 11 von 31
Beispiel: Suche von Kunden in best. Städten
SELECT
Name,
Vorname
FROM
Kunde
WHERE
Ort = ’Bad Dürkheim’ OR
Ort = ’Freinsheim’
SQL 6 - Terme gleicher Spalten mit OR verknüpfen
SELECT
Name,
Vorname
FROM
Kunde
WHERE
Ort IN (’Bad Dürkheim’, ’Freinsheim’)
SQL 7 - Anwendung des IN Operators
Analog dazu können auch numerisch Werte in der IN Liste aufgezählt werden oder
Datumsangaben verwendet werden.
2.3.3 Die BETWEEN Operator
Möchte man eine Einschränkung auf einen Bereich beziehen, so kann dies elegant
mit dem BETWEEN Operator erfolgen. Anstelle zweier – mit AND verknüpfter –
Terme kann man einen Ausdruck mit BETWEEN formulieren.
Beispiel: Kunden zwischen 20 und 30 Jahren
SELECT
Name,
Vorname
FROM
Kunde
WHERE
Jahre >= 20
AND Jahre <= 30
SQL 8 - Einschränkung der Ergebnismenge auf einen Bereich
SELECT
Name,
Vorname
FROM
Kunde
WHERE
Jahre BETWEEN 20 AND 30
SQL 9 - Einschränkung der Ergebnismenge auf einen Bereich (2)
Einführung in SQL
30.8.2001
Seite 12 von 31
2.4 Skalarfunktionen
Skalarfunktionen sind echte Funktionen im mathematischen Sinne, d.h. sie liefern zu
einem Argument ein eindeutiges Ergebnis. Das Argument ist ein Einzelwert (Skalar),
und nicht wie bei den Aggregatfunktionen (Kapitel 2.5) eine Zusammenfassung
mehrerer Spaltenwerte.
Allgemein angewendet werden diese Skalarfunktionen wie folgt
Ergebnis = Skalarfunktion ( Argument )
wobei das Ergebnis wiederum Argument einer weiteren Skalarfunktion sein kann.
2.4.1 Numerische Skalarfunktionen
Pla
ABS, LN, MOD, POWER, SIGN, SQRT
2.4.2 Zeichenketten Skalarfunkionen
Pla
UPPER, LOWER, MID / SUBSTR, INSTR, LENGTH
2.5 Aggregatfunktionen
Häufig kommt es vor, das bestimmte Datensätze nicht einzeln ausgewertet werden
sollen, sondern erst zusammengefaßt werden müssen und daraus das Ergebnis
ermittelt wird. Als Beispiel stelle man sich eine Umsatzliste pro Kunde vor, bei dem
die Einzelaufträge des Kunden zuerst zusammengerechnet werden, bevor das
Ergebnis ermittelt wird.
Für diese Anforderung hat man Aggregatfunktionen (Gruppierungsfunktionen) zur
Verfügung, die einem diese Arbeit elegant lösen lassen.
Man beachte, das die Aggregatfunktionen Werte liefern, die aus mehreren Zeilen
einer Spalte zusammengefaßt sind, d.h. diese Zeilen sind dann gruppiert worden.
Zum Zusammenfassen von bestimmten Zeilen wird die GROUP BY Klausel
verwendet. Hierbei wird angegeben, das die Zeilen mit gleichem Inhalt
zusammengefaßt werden sollen. Wichtig bei dieser Klausel ist, das die angegebenen
Spalten komplett sein müssen. Man kann keine Zeilen Zusammenfassen, in denen
andere Spalten noch unterschiedliche Werte besitzen.
Wird keine GROUP BY Klausel angegeben, so beziehen sich die Aggregatfunktionen
auf die gesamte Tabelle, d.h. die Ergebnismenge besteht aus einer Zeile mit dem
gruppierten Wert für die ganze Tabelle.
Einführung in SQL
30.8.2001
Seite 13 von 31
2.5.1 Die COUNT Funktion
Die COUNT Funktion liefert die Anzahl der Zeilen der Gruppierung zurück. Wird
keine Gruppierung angegeben, so bezieht sich COUNT auf die komplette
Quellmenge, die ja dann eine einzige Gruppe darstellt.
Selbstverständlich kann die Ergebnismenge eingeschränkt werden und keine
Gruppierung angegeben werden. Damit erhält man die Anzahl der Datensätze, die
dieser Einschränkung genügen.
Bsp.: Anzahl der Kunden, die 29 Jahre alt sind
select
COUNT(*)
from
Kunde
where
Alter = 29
SQL 10 - Die COUNT Funktion
2.5.2 Die SUM Funktion
Mit der SUM Funktion wird die Summe der gruppierten Werte einer Spalte berechnet
Bsp.: Die Gesamtsumme eines Auftrages
select
SUM(Betrag)
From
Auftrag_Artikel
Where
Auftrag_Nummer = 1
SQL 11 - Die SUM Funktion
2.5.3 Die MIN, MAX Funktionen
Den kleinsten und den größten Wert einer gruppierten Spalte wird über die beiden
Funktionen MIN und MAX bestimmt.
Bsp.: Der jüngste und der älteste Kunde
select
MIN(Alter),
MAX(Alter)
From
Kunde
SQL 12 - Die MIN und MAX Funktion
Einführung in SQL
30.8.2001
Seite 14 von 31
2.5.4 Die AVG Funktion
Um den Durchschnittswert der zusammengefaßten Werte zu berechnen, wird die
AVG (Average) Funktion eingesetzt.
Bsp.: Durchschnittsalter der Kunden
select
AVG(Alter)
From
Kunde
SQL 13 - Die AVG Funktion
2.5.5 Spalten mit und ohne Gruppierungsfunktion abfragen
Werden Gruppierungsfunktionen mit anderen Spalten ohne eine solche
Gruppierungsfunktion abgefragt, so muß ein Gruppierungskriterium angegeben
werden, sonst kann die Gruppierungsfunktion nicht korrekt arbeiten.
Es ist nicht möglich, Zeilen zusammenfassen, in denen Spalten existieren, die noch
unterschiedliche Werte haben. Darauf muß man beim Erstellen einer solchen
Abfrage achten.
Bsp.: Auftragsnummer und –betrag aller Aufträge
select
Auftrag_Nummer,
SUM(Betrag)
from
Auftrag_Artikel
GROUP BY
Auftrag_Nummer
SQL 14 - Gemischte Abfrage mit und ohne Gruppierungsspalten
2.6 Sortierung
Bisher wurden die Ergebniszeilen ohne eine erkennbare Sortierung geliefert. Oft ist
es aber sinnvoll, ein Sortierkriterium auf die Daten anzuwenden, damit z.B. der
Auftrag mit dem größten Umsatz an erster Stelle erscheint.
Hierzu wird in SQL die ORDER BY Klausel verwendet. Ihr werden die Spalten
angegeben, nach denen sortiert wird. Werden mehr als eine Spalte angegeben, so
wir die Sortierung in der Reihenfolge erfolgen, in der die Spalten angegeben wurde.
Zuerst wird nach der zuerst angegebenen Spalte sortiert. Bei Gleichheit werden die
Daten nach der zweiten Spalte sortiert. Dies wird so weit fortgeführt, bis alle
Sortierspalten berücksichtigt wurden, anschließend ist die Sortierung wieder nicht
bestimmbar.
Es ist nicht notwendig, das die Sortierspalten in der SELECT Liste enthalten sein
müssen.
Bsp.: Kundenliste mit Altersangabe alphabetisch sortiert
Einführung in SQL
30.8.2001
Seite 15 von 31
Select
Name,
Vorname,
Alter
From
Kunde
Order by
Name,
Vorname
SQL 15 - Alphabetisch Sortierte Kundenliste
2.7 Gruppierungsfunktionen in WHERE Klauseln
Es ist nicht möglich, Gruppierungsfunktionen in einer WHERE Klausel einzusetzen.
In einer WHERE Klausel werden die Vergleiche zeilenweise ausgeführt.
Gruppierungsfunktionen fassen aber die Zeilen zuerst zusammen und können damit
nicht in Verbindung mit einer WHERE Klausel benutzt werden. Dazu nutzt man die
HAVING Klausel, die dieses Manko umgeht. HAVING wird auf die gruppierten Daten
angewendet, d.h. die Gruppierung wird zuerst durchgeführt.
Bsp.: Kundenliste, die jünger sind als das Durchschnittsalter
select
Name,
Vorname
from
Kunde
where
Alter < AVG(Alter)
SO NICHT !!
select
Name,
Vorname
from
Kunde
having
Alter < AVG(Alter)
SQL 16 - die HAVING Klausel
2.8 Kombination verschiedener Klauseln
Die angesprochenen Klauseln lassen sich natürlich beliebig kombinieren. Man kann
die Einschränkung mit der Gruppierung und der Sortierung benutzen.
Bsp.: Sortierte Auftragsliste, die bestimmten Kriterien genügen
select
SUM(Betrag),
Auftrag_Nummer
from
Auftrag_Artikel
Einführung in SQL
30.8.2001
Seite 16 von 31
where
Auftrag_Nummer IN (1,3,5,7,9)
having
SUM(Betrag) > 100
order by
Auftrag_Nummer
SQL 17 - Sortierte Auftragsliste mit Kriterien
Beachte, das die Verwendung einer WHERE Klausel die Daten, die zum Gruppieren
herangezogen werden, beeinflußt, da die WHERE Klausel auf der Quelldatenmenge
arbeitet.
2.9 ACCESS Ergänzungen
2.9.1 Selbstdefinierte VBA Funktionen
In ACCESS hat man die Möglichkeit, eigene VBA Funktionen zu schreiben. Diese
Funktionen können dann in einem SELECT Befehl wie Skalarfunktionen verwendet
werden. Desweiteren können integrierte VBA Funktionen verwendet werden.
Beispielsweise könnte man sich seine eigene UPPERCASE Funktion schreiben.
In einem Modul:
Public Function Uppercase(strText as String) As String
Uppercase = UCase(strText)
End Function
Die Abfrage:
select
Uppercase (kd.Name),
Uppercase (kd.Vorname)
from
Kunde kd
where
kd.Nummer = 1
SQL 18 - Eine selbstgeschriebene VBA Funktion nutzen
Beachte, das ACCESS hier die alle zu Grunde liegenden Daten anpacken muß, um
das Ergebnis zu liefern. Bei jeder einzelnen Zeile wird der Ausdruck Uppercase()
angewendet und dann erst diese Daten in die Ergebnismenge aufgenommen. Das
heißt auch, das eine Abfrage mit einer WHERE Klausel keinen Index benutzen kann,
um die Ergebnismenge schnell auffinden zu können, da zuerst die Funktion auf alle
Zeilen der Tabelle angewendet wird und anschließend erst die WHERE Klausel
ausgewertet wird.
Einführung in SQL
30.8.2001
Seite 17 von 31
3 Views
3.1 Allgemeines
Mit einer Sicht (engl. View) kann man komplexe Abfragen kapseln. Das heißt, man
schreibt nicht mehr einen komplexen SQL-Befehl, sondern kann die Daten von
dieser View – wie bei einer einfachen Tabelle – direkt abfragen, als ob man die
Daten aus einer Tabelle liest. Man muß sich das wie eine virtuelle Tabelle vorstellen,
die aber einigen Einschränkungen bei der operativen Bearbeitung unterliegt.
Änderungen an der zu Grunde liegenden Daten wirken sich direkt auf die View aus,
diese liefert dann sofort die neuen Daten. Das heißt auch, das die View die Daten
nicht gesondert speichert, sondern auf die vorhandenen Daten zurückgreift. Somit
belegt eine View auch keinen Speicherplatz in der Datenbank (bis auf die
Deklaration).
Eine View wird eingesetzt, um dem Anwender eine besondere Sicht auf die Daten zu
geben. Wenn den Anwender nicht an allen Daten interessieren oder der Anwender
nicht alle Daten sehen darf, werden Views eingesetzt. Als Beispiel sei eine
Mitarbeitertabelle, in der auch das Gehalt eines Mitarbeiters gespeichert ist. Nur die
Geschäftsleitung soll das Gehalt sehen. Der Mitarbeiter, der die Wochenpläne
aufstellt, soll die Gehälter nicht sehen und benötigt diese auch nicht für seine
Arbeitspläne. Hier kann man dem Anwender eine View auf die Mitarbeitertabelle zur
Verfügung stellen, in der nur der Name und die Abteilung enthalten sind, der
Geschäftsleitung erteilt man den kompletten Zugriff auf die Mitarbeitertabelle.
3.2 Erstellen einer View
Eine View wird mit dem Befehl CREATE VIEW erzeugt. Diesem Befehl gibt man die
zu Grunde liegende Abfrage mit. Die Spaltennamen der View ergeben sich aus der
Abfrage oder werden explizit vergeben. Hiermit kann man die Spaltennamen nach
seinen eigenen Bedürfnissen anpassen. Der Datentyp der Ergebnisspalten werden
aus den zu Grunde liegenden Tabellenspalten vererbt.
CREATE VIEW <Name> [<Spalte 1> (,<Spalte x>){0..n} AS
SELECT <Spalte 1> (,<Spalte y){0..n}
FROM ...
SQL 19 - Syntax für das Anlegen einer View
Für die Erstellung View, die minimale Kundendaten zur Verfügung stellt, schreibt
man also
Create view Minimale_Kundendaten (Nummer, Name, Vorname) AS
Select
kd.Nummer,
kd.Name,
kd.Vorname
from
Einführung in SQL
30.8.2001
Seite 18 von 31
Kunde kd
SQL 20 - Eine einfahce Sicht auf die Kundendaten
Man kann für den zu Grunde liegenden Abfragebefehl fast alle Elemete des SELECT
Befehls verwenden, also die Daten mit WHERE Einschränken usw. Einschränkungen
bestehen darin, das man bei der Abfrage keine UNION Klausel verwenden darf und
das keine ORDER BY Klausel verwenden darf.
3.3 Lesen aus einer View
Wie oben angedeutet kann man sich eine View wie eine virtuelle Tabelle vorstellen.
Das bedeutet gleichzeitig, das man die Daten mit einem SELECT Befehl lesen kann.
Bei dem Lesen aus einer View unterliegt man bezüglich der verwendbaren Klauseln
des SELECT Befehls keinen Einschränkungen.
Für die Abfrage der Daten aus der oben erstellten View schreibt man
Select
*
from
Minimale_Kundendaten
SQL 21 - Abfrage von Daten aus einer View
3.4 Ändern der Daten
Bezüglich der Änderung der Daten, die eine View liefert, gibt es einige
Einschränkungen. Diese werden hier aufgeführt
 Aus Sichten, die aus mehreren Tabellen bestehen, können keine Zeilen gelöscht
werden
 In Sichten, die aus Tabellen besteht, die NOT NULL Spalten haben, diese aber
nicht in der View enthalten sind, können keine Daten eingefügt werden
 Wenn Daten in einer Sicht eingefügt oder geändert werden, müssen die Daten zu
einer einzigen physikalischen Tabelle gehören
 Wird bei der Erzeugung die DISTINCT Klausel verwendet, können keine Daten
eingefügt oder geändert werden
 Enthält die View virtuelle Spalten (berechnete Spalten), so können keine Daten
eingefügt oder geändert werden
Dies sind die wichtigsten Einschränkungen, jedes DBMS hat noch seine eigenen
Einschränkungen, die in der Produktbeschreibung nachzulesen sind.
3.5 ACCESS Ergänzungen
Einführung in SQL
30.8.2001
Seite 19 von 31
3.5.1 Abfragen
Bei ACCESS wird eine View in der Regel nicht mit dem Befehl CREATE VIEW
erzeugt. Hier wird eine Abfrage angelegt, der man nur den SELECT Befehl angibt.
Diese Abfrage wird dann unter einen Namen – dem Viewnamen – abgespeichert und
kann dann damit arbeiten.
Beachte, das die ACCESS Abfragen nicht nur Views sein müssen. Hierbei kann
ACCESS jeden Befehl ausführen. Der Name ist hierbei leicht verwirrend.
4 Verknüpfung von Tabellen
Neben der Abfrage von Daten aus einer Tabelle kommt es viel häufiger vor, Daten
aus mehreren Tabellen gleichzeitig abzufragen. Hierbei kommen Verknüpfungen
(engl. join) zum Einsatz. Es gilt hierbei alles, was bisher dargestellt wurde.
Die zu verknüpfenden Tabellen werden in der FROM Klausel aufgezählt, wobei die
Reihenfolge keine Rolle spielt. Die Verknüpfung wird in der WHERE Klausel
deklariert. Bei den Spalten wird – wenn die Namen nicht eindeutig sind – der
Tabellenname oder ein Alias mit angegeben. Doch Vorsicht bei dem Fehlen der
Tabellenbezeichnern. Ich empfehle, diesen bei Verknüpfungen immer mitanzugeben.
SELECT [[<Tabelle.>]Spalte(,)]{1..n} FROM [Tabelle(,)]{1..n}
Als erstes Beispiel soll folgende Verknüpfung dienen:
select
Tabelle1.Spalte1,
Tabelle1.Spalte2,
Tabelle2.Spalte1,
Tabelle2.Spalte2
from
Tabelle1,
Tabelle2
SQL 22 - Das karthesische Produkt
Hierbei wird das kartesische Produkt Tabelle1 x Tabelle2 zurückgegeben, da keine
Verknüpfung einschränkend angegeben wurde. Dies nennt man dann auch
Crossjoin.
Anstelle des Tabellennamens – der meist lang ist – kann man einen Aliase für jede
Tabelle vergeben, mit denen der Befehl verkürzt wird. Hierdurch wird der Befehl
meist lesbarer. Diesen Alias schreibt man direkt hinter die Tabellenangabe in der
FROM Klausel.
Einführung in SQL
30.8.2001
Seite 20 von 31
select
t1.Spalte1,
t1.Spalte2,
t2.Spalte1,
t2.Spalte2
from
Tabelle1 as t1,
Tabelle2 as t2
SQL 23 - das karthesische Produkt mit Tabellenaliase
Die Angabe von AS bei der Aliasdeklaration ist optional.
4.1 Equi Joins
Bei dieser – am häufigsten eingesetzten – Verknüpfungsform werden zwei Tabellen
miteinander Verknüpft, die über gleiche Spalten verfügen, in denen gleiche Werte
stehen. Dies sind in der Regel der Primärschlüssel einer Tabelle und der
Fremdschlüssel dieser Tabelle in der zweiten Tabelle.
Man nennt diese Verknüpfungsart equi join, da die beiden Tabellen mit den gleichen
Spalten über ein Gleichheitszeichen verknüpft werden.
Bsp.: Aufträge und ihre Artikel
select
aa.Auftrag_Nummer,
art. Nummer,
art.Bezeichnung
from
AuftragArtikel aa,
Artikel art
where
aa.Artikel_Nummer = art. Nummer
SQL 24 - Aufträge und deren Artikel
Dies Abfrage bewirkt, das alle Artikel eines Auftrags zusammen ausgegeben werden,
und dies für jeden Auftrag, der in der Datenbank steht.
Natürlich können mehrere einschränkende Bedingungen in der WHERE Klausel
angegeben werden. Diese Bedingung wird auf die Verknüpfung angewendet. Hiermit
ist es z.B. möglich, die Artikeldaten nur eines Auftrags abzurufen.
select
aa.Auftrag_Nummer,
art. Nummer,
art.Bezeichnung
from
AuftragArtikel aa,
Artikel art
where
Einführung in SQL
30.8.2001
Seite 21 von 31
aa.Artikel_Nummer = art. Nummer
AND aa.Auftrag_Nummer = 1
SQL 25 - Auftrag 1 mit deren Artikeln
Genauso kann man die Umsätze jedes Artikels bestimmen.
Select
SUM (aa.Anzahl * art.Preis)
From
Artikel art,
Auftrag_Artikel aa
Where
aa.Artikel_Nummer = art. Nummer
AND art.Bezeichnung = ‘Dosenbier‘
SQL 26 - Umsatz eines Artikels
Beachte hierbei die Aliasnamen der beiden Tabellen. Der erste Term der WHERE
Klausel stellt die Verknüpfung dar und der zweite Term ist eine einschränkende
Bedingung.
Natürlich kann man mehr als zwei Tabellen verknüpfen. Hierbei werden jeweils zwei
Tabellen in einem Term verknüpft und jeweils eine weitere Tabelle in einem weiteren
Term verknüpft.
select
kd.Name,
kd.Vorname,
art.Bezeichnung,
art.Preis * aa.Anzahl,
au.Nummer
from
Artikel art,
Auftrag au,
Kunde kd
where
kd. Nummer = au.Kunde_Nummer
AND au. Nummer = aa.Auftrag_Nummer
AND aa.Artikel_Nummer = art. Nummer
SQL 27 - Abfrage über mehrere Tabellen
Man sieht an der Ergebnismenge, das, wenn mehr als eine entsprechende Zeile in
der zweiten Tabelle existiert, mehrere Zeilen für dieselben Daten der ersten Tabelle
generiert werden. Desweiteren sind keine Kunden in der Ergebnismenge enthalten,
die bisher keinen Bestellung (=> Auftrag) aufgegeben haben, da hier keine Gleichheit
existiert. Dies ist ein ganz wichtiger Punkt, der häufig vergessen wird.
Hinweis. Diese Art der Abfrage ist sehr viel schneller, als die Daten der drei Tabellen
einzeln anzufordern. D.h. man nimmt die Kundentabelle, sucht zu jeder
Kundennummer die Aufträge und zu jedem Auftrag die Artikel in jeweils einzelnen
SQL Befehlen. Dieser Lösungsansatz ist zwar genau so richtig, kostet aber sehr viel
Zeit und Prozessorlast. Also Finger davon und ein großer SQL Befehl schreiben.
Einführung in SQL
30.8.2001
Seite 22 von 31
4.2 Non Equi Join
Als non equi join bezeichnet man jede Verknüpfung, die nicht das Gleichheitszeichen
verwendet. Diese Art wird jedoch sehr selten eingesetzt und wird hier nicht weiter
erläutert.
4.3 Inner Join
Eine andere Schreibweise für die equi joins ist der inner join. Diese Begriffe sind
gleich zu verwenden. Microsoft (mit Sybase) hat diese Syntaxform eingeführt,
beherrscht aber auch noch die andere Syntax. Die meisten anderen Datenbanken
verstehen aber nur die equi join Form (oder zum teil wieder Eigenentwicklungen).
Die allgemeine Syntax lautet:
SELECT <Spalten> FROM <Tabelle1>
INNER JOIN <Tabelle2> ON
<Tabelle1.Spalte> = <Tabelle2.Spalte>
SQL 28 - Die Inner Join Syntax
Beachte, das hier keine WHERE Klausel angegeben wird, um die Verknüpfung zu
beschreiben.
4.4 Outer Joins
Bei den equi join (inner join) Abfragen sieht man, das nur Ergebniszeilen geliefert
werden, die eine Entsprechung in beiden Tabellen haben.
Häufig ist es aber wichtig, alle Zeilen einer Tabelle zu bekommen, auch wenn keine
entsprechenden Zeilen in der verknüpften Tabelle existieren. Beispielsweise möchte
man eine Kunden Umsatz Tabelle haben, in der alle Kunden vorhanden sind, auch
solche, mit denen man keinen Umsatz gemacht hat. Hierbei kommt man mit einem
equi join nicht weiter.
Dieses Problem kann mit den outer joins gelöst werden. Hierbei macht man der
Datenbank klar, das alle Zeilen der einen Tabelle zur Ergebnismenge gehören
sollen. Die Datenbank generiert dann für die Spalten der Ergebniszeilen NULL
Einträge, in denen es keine Entsprechungen gibt.
Welche Tabelle alle Zeilen zur Ergebnismenge hinzufügt wird über die left oder die
right outer join Befehle festgelegt.
4.4.1 Left Outer Join
Bei dem left outer join werden alle Zeilen der linken Tabelle der Ergebnismenge
zugefügt. Links bedeutet hier die Tabelle in dem SQL Befehl, die weiter links steht,
also zuerst in dem Befehl angegeben wird.
Einführung in SQL
30.8.2001
Seite 23 von 31
Bsp.: Kunden Auftrag Tabelle
select
Kd.Name,
kd.Vorname,
au.Nummer
from
Kunde kd
left outer join
Auftrag au
on
au.Kunden_Nummer = kd.Nummer
SQL 29 - Ein einfacher left outer join Befehl
Das Ergebnis enthält alle Kunden. Die entsprechende Auftragsspalte sind evtl. durch
einen NULL Eintrag gefüllt. Ein Kunde kann mehrfach vorkommen, da zu einem
Kunde mehr als ein Auftrag existieren kann.
Das left bezieht sich hier auf die Tabelle Kunde, da diese im Befehl als erstes
angegeben wurde, also weiter links steht.
Beachte, das hier zwar das Gleichheitszeichen angegeben wird, es sich aber nicht
um einen reinen equi join handelt.
Diese Syntax kennt nur Microsoft (Sybase). Die von anderen Datenbanken
verwendete Syntax hat das o.g. Schlüsselwort nicht. Hierbei wird die equi join Syntax
verwendet. Die Tabelle, die die NULL Einträge liefern soll, wird mit einem (+) hinter
der Verknüpfung gekennzeichnet.
Allgemein lautet die Syntax:
SELECT <Spalten> FROM <Tabellen>
WHERE <Tabelle1> = <Tabelle2>(+)
SQL 30 - Die allgemeine outer join Syntax
Obiges Beispiel mit dieser Syntax lautet dann
select
kd.Name,
kd.Vorname,
au.Nummer
from
Kunde kd,
Auftrag au
where
kd.Nummer = au.Kunden_Nummer(+)
SQL 31- Ein einfaches outer join Beispiel (2)
4.5 Right Outer Join
Wie gesehen, fügt bei dem left outer join die linke Tabelle alle Zeilen zur
Ergebnismenge bei. Soll jedoch die zweite Tabelle alle Zeilen der Ergebnismenge
Einführung in SQL
30.8.2001
Seite 24 von 31
beifügen, so setzt man den right outer join ein. Die Syntax ist analog zu o.g. Syntax,
jedoch wird die Klausel right outer join eingesetzt.
Voriges Beispiel schreibt man als right outer join folgendermaßen:
select
kd.Name,
kd.Vorname,
au.Nummer
from
Auftrag au
right outer join
Kunde kd
on
kd.Nummer = au.Kunden_Nummer
SQL 32 - Ein einfaches right outer join Beispiel
Wie man sieht, ist der right outer join die Umkehrung des left outer join (Vertauschen
der Tabellenreihenfolge und Vertauschen von left/right).
Die analoge Syntax der restlichen – nicht Microsoft – Welt, lautet:
select
kd.Name,
kd.Vorname,
au.Nummer
from
Kunde kd,
Auftrag au
where
au.Kunden_Nummer(+) = kd.Nummer
SQL 33 - Ein einfaches outer join Beispiel (2)
4.6 Tabellen mit sich selbst verknüpfen
Durch die Vergabe von Aliasnamen können Tabellen mit sich selbst verknüpft
werden. Diese Technik wird jedoch seltener eingesetzt.
Bsp.: Suchen nach doppelten Einträgen in einer Tabelle
select
kd.Nummer,
kd.Name
from
Kunde kd,
Kunde kd2
where
kd.Nummer = kd2.Nummer
AND kd.Name <> kd2.Name
SQL 34 - Tabelle mit sich selbst verknüpft
Einführung in SQL
30.8.2001
Seite 25 von 31
5 Unterabfragen
Eine weitere Art der Verknüpfung ist die Unterabfrage. Hierbei wird das Ergebnis
einer Abfrage dazu genutzt, die Eingabe einer weiteren Abfrage zu bilden.
Einfach ausgedrückt bedeutet dies, ein Zwischenergebnis zu berechnen und daraus
das Gesamtergebnis zu bestimmen. Das hat den Vorteil, das man den kompletten
Befehl zerlegen kann. Somit kann das Zwischenergebnis kontrolliert werden und
dann daraus das Gesamtergebnis berechnet werden. Dies ist vorteilhaft für die
Fehlersuche und für einen schrittweise Entwicklung komplexer Abfragen.
Als Beispiel wollen wir die Kunden bestimmen, deren Alter über dem
Durchschnittsalter aller Kunden liegt. Zuerst ermitteln wir also das Durchschnittsalter
aller Kunden.
select
AVG(Jahre)
from
Kunde
SQL 35 - Das Durchschnittsalter aller Kunden
Diese Ergebnis vergleicht man mit der Altersangabe jedes einzelnen Kunden und
wählt die aus, die älter als das Durchschnittsalter sind.
select
kd.Name,
kd.Vorname
from
Kunde kd
where
Jahre > {berechnetes Durchschnittsalter}
SQL 36 - Kunden über dem Durchschnittsalter (1)
Wenn man jetzt weiß, das man Unterabfragen in Klammern setzten muß, dann kann
man den ganzen Befehl hinschreiben
select
kd.Name,
kd.Vorname
from
Kunde kd
where
Jahre >
(
select
AVG(Jahre)
from
Kunde kd
Einführung in SQL
30.8.2001
Seite 26 von 31
)
SQL 37 Kunden über dem Durchschnittsalter (2)
Unterabfragen haben keine Beschränkung bezüglich der Verwendung von
Syntaxelementen. Achten Sie aber darauf, das die so verknüpften Daten
zusammenpassen. Desweiteren muß man auf die Kardinalität achten.
Liefert die Unterabfrage mehr als einen Datensatz, so funktionieren die einfachen
Verknüpfungsoperatoren verständlicherweise nicht mehr. Dann müssen andere
Operatoren eingesetzt werden.
Beispielsweise kann eine Liste der Aufträge von Kunden aus Bad Dürkheim
folgendermaßen bestimmt werden
select
kd.Name,
kd.Vorname,
au.Nummer
from
Kunde kd,
Auftrag au
where
kd.Nummer = au.Kunden_Nummer
AND kd.Nummer IN
(
select
kd.Nummer
from
Kunde kd
where
Kd.Ort = ‘Bad Dürkheim‘
SQL 38 - Auftragsliste der Kunden aus Bad Dürkheim
5.1 Exists
Interessiert für die Haupt-/Oberabfrage nur, ob die Unterabfrage Zeilen liefert, jedoch
nicht deren Inhalt, so nutzt man das EXISTS Schlüsselwort. Dies ist eine boole‘sche
Auswertung der Ergebnismenge, die TRUE liefert, sobald eine Ergebnismenge
vorhanden ist. Sind keine Zeilen in der Ergebnismenge enthalten (leere Menge),
dann liefert dieser Ausdruck FALSE.
Als Beispiel wollen wir wissen, ob wir Kunden mit dem Nachnamen Schmitt in
unserer Kundenkartei haben
select
‘Ja‘
from
Dual
where
Einführung in SQL
30.8.2001
Seite 27 von 31
exists
(
select
*
from
Kunde kd
where
kd.Name = ‘Schmitt‘
)
SQL 39 - Gibt es Kunden mit einem bestimmten Namen
Beachte, das die Hauptabfrage so viele Zeilen liefert, wie die Tabelle der
Hauptabfrage Zeilen hat. Dies kommt daher, das bei der Auswertung nur auf TRUE
oder FALSE geprüft wird, Im TRUE Falle wird die Hauptabfrage ausgeführt, sonst
nicht.
Eine Anwendung hierfür sind z.B. Updatescripte für Datenbankschemata, die dann
ausgeführt werden, wenn bestimmte Daten noch nicht vorhanden sind.
5.2 Any / Some
Im Gegensatz zu Exists liefern die Any und die Some Klauseln für jede
entsprechende Zeile der Unterabfrage zur Oberabfrage.
Als Beispiel soll die Auftragsliste der Kunden aus Bad Dürkheim ermittelt werden.
select
kd.Name,
kd.Vorname,
au.Nummer
from
Kunde kd,
Auftrag au
where
kd.Nummer = any
(
select
kd.Nummer
from
Kunde kd
where
kd.Ort = ‘Bad Dürkheim‘
SQL 40 - Auftragsliste der Kunden aus Bad Dürkheim (mit ANY)
Wie man sieht, liefert diese Abfrage dasselbe Ergebnis wie die Abfrage mit IN. Der
Unterschied zwischen IN und ANY / SOME besteht darin, das IN nur auf Gleichheit
prüfen kann, ANY / SOME kann dahingegen mit allen Operatoren verwendet werden.
5.3 Korrelierende Unterabfragen
Einführung in SQL
30.8.2001
Seite 28 von 31
Die bisher verwendeten Abfragen waren Unabhängig voneinander. Die
korrelierenden haben eine Referenz zur Ober-/Hauptabfrage.
Einführung in SQL
30.8.2001
Seite 29 von 31
6 Anhang
6.1 Tabellenverzeichnis
Fehler! Es konnten keine Einträge für ein Abbildungsverzeichnis gefunden
werden.
6.2 SQL Beispiele Verzeichnis
SQL 1 - Lesen der Kundendaten .............................................................................. 10
SQL 2 - Lesen der Kundendaten mit Spaltenalias .................................................... 10
SQL 3 - Ändern der Spalteninhalte der Ergebnismenge ........................................... 10
SQL 4 - Einschränkung der Ergebnismenge ............................................................ 11
SQL 5 - Einschränkung der Ergebnismenge (2) ....................................................... 11
SQL 6 - Terme gleicher Spalten mit OR verknüpfen ................................................ 12
SQL 7 - Anwendung des IN Operators ..................................................................... 12
SQL 8 - Einschränkung der Ergebnismenge auf einen Bereich................................ 12
SQL 9 - Einschränkung der Ergebnismenge auf einen Bereich (2) .......................... 12
SQL 10 - Die COUNT Funktion ................................................................................ 14
SQL 11 - Die SUM Funktion ..................................................................................... 14
SQL 12 - Die MIN und MAX Funktion ....................................................................... 14
SQL 13 - Die AVG Funktion...................................................................................... 15
SQL 14 - Gemischte Abfrage mit und ohne Gruppierungsspalten ............................ 15
SQL 15 - Alphabetisch Sortierte Kundenliste............................................................ 16
SQL 16 - die HAVING Klausel .................................................................................. 16
SQL 17 - Sortierte Auftragsliste mit Kriterien ............................................................ 17
SQL 18 - Eine selbstgeschriebene VBA Funktion nutzen ......................................... 17
SQL 19 - Syntax für das Anlegen einer View............................................................ 18
SQL 20 - Eine einfahce Sicht auf die Kundendaten .................................................. 19
SQL 21 - Abfrage von Daten aus einer View ............................................................ 19
SQL 22 - Das karthesische Produkt ......................................................................... 20
SQL 23 - das karthesische Produkt mit Tabellenaliase ............................................ 21
SQL 24 - Aufträge und deren Artikel......................................................................... 21
SQL 25 - Auftrag 1 mit deren Artikeln ....................................................................... 22
SQL 26 - Umsatz eines Artikels ................................................................................ 22
SQL 27 - Abfrage über mehrere Tabellen ................................................................ 22
SQL 28 - Die Inner Join Syntax ................................................................................ 23
SQL 29 - Ein einfacher left outer join Befehl ............................................................. 24
SQL 30 - Die allgemeine outer join Syntax ............................................................... 24
SQL 31- Ein einfaches outer join Beispiel (2) ........................................................... 24
SQL 32 - Ein einfaches right outer join Beispiel........................................................ 25
SQL 33 - Ein einfaches outer join Beispiel (2) .......................................................... 25
Einführung in SQL
30.8.2001
Seite 30 von 31
SQL 34 - Tabelle mit sich selbst verknüpft ............................................................... 25
SQL 35 - Das Durchschnittsalter aller Kunden ......................................................... 26
SQL 36 - Kunden über dem Durchschnittsalter (1) ................................................... 26
SQL 37 Kunden über dem Durchschnittsalter (2) ..................................................... 27
SQL 38 - Auftragsliste der Kunden aus Bad Dürkheim ............................................. 27
SQL 39 - Gibt es Kunden mit einem bestimmten Namen ......................................... 28
SQL 40 - Auftragsliste der Kunden aus Bad Dürkheim (mit ANY) ............................ 28
6.3 Abbildungsverzeichnis
Abbildung 1 - Aufbau einer Tabelle ............................................................................ 5
6.4 Quellen
Einführung in SQL
30.8.2001
Seite 31 von 31