Fachbereich Informatik 1 Grundelemente von SQL

Oberschule am Elsengrund
- Gymnasium Elsenstraße 9 – 11
12623 Berlin
Tel. / Fax 030 / 56 77 91 7
Fachbereich Informatik
e- mail : informatik @og- elsengrund.cidsnet.de
http://www.og- elsengrund.cidsnet.de
Berlin, 04.08.04
1 Grundelemente von SQL
Die Datenbanks prach e SQL, (Structured Query Language), gesprochen 'es ku eil', wurde als Abfragesprache für Datenba nksy st em e geschaffen und steht in engem Zusam m en h an g mit der Entwicklung des
relationalen Datenm odells in den letzten beiden Dekaden. Die Sprache ist nach den ersten Implementierungen in den frühen achtziger Jahren heute in Standards (1986, 1989, 1992, 1999) definiert und (mit
herstellerabh ängigen Zusätzen) in vielen - auch nichtrelationalen - Datenba nksy ste m e n als Sprachsch nittstelle verfügbar. Der Standard von 1989 wird häufig SQL1, der von 1992 SQL2 genannt und der Standard
von 1999 SQL- 99 oder SQL3.
SQL enthält Eleme nte zur Datendefinition, zur Datenm anipulation und zur Datenzuga ngs k ontrolle.
Man unterscheidet :
- die Sprachanteile DDL (Data Definition Language) zur Definition der Struktur der
Daten und
- die DML (Data Manipulation Language), die die Operationen auf den Daten zur Verfügung
stellt.
Zur Beschreibung der Syntax von SQL wird eine EBNF- ähnliche Notation verwandt. Es werden nur
die wichtigsten Konstruktionen vorgestellt, dabei liegt der Schwerpunkt auf dem SQL- Kern.
1.1 Datendefinition in SQL
Die Sprachant eile zum Generieren und Löschen von Schema definitionen (Datenban ksc he m a, Relationsschemata, Sichten etc.) sind häufig dem Benutzer von Datenbank an w e nd un g e n gar nicht zugänglich und
dem Datenbank ad ministrator vorbehalten.
1.1.1 Schema-Generierung
Zur Erstellung eines Datenban ks che m a s wird in Standard- SQL die Anweisung
CREA T E SCHE M A AUTH O RI Z A TI O N Nutzer
benutzt (zentrale Verwaltung der Datenbanke n durch den DBA), bei Einbettung der Datenba nke n ins
Dateisystem des Nutzers lautet sie:
CREA T E DATA B A S E Datenba nkna m e
Auf der Interpretierer- Schnittstelle eines DB- Systems wird mit der interaktiven Eingabe dieser Anweisung ein Satz leerer Systemtab ellen für die Verwaltung der benannten Datenbank generiert. Auf diese
Schema gen erierung folgen dann (null oder mehr) Tabellengenerierung en, Sichtdefinitionen oder Grantoperationen.
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
Version : /home/ustredak/Datenbanksysteme/grundlagen_sql.sxw
Seite 1
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
1.1.2 Tabellen-Generierung
CREA T E TABL E Tabellenna m e
( Spaltenna m e Datentyp [ NOT NULL — ... ]
{ , Spaltenna m e Datentyp [ NOT NULL — ...]});
Beispiel: (Bücher)
CREA T E TABL E B
( BNr
CHAR(5) NOT NULL,
Autor CHAR(25) ,
Titel CHA R(40) ,
Preis DECI M AL (3) );
Mit dieser Anweisung wird die Struktur einer Basistabelle festgelegt, der im logischen Modell das
Relationssche m a entspricht. Es können zwei wesentlich verschiede ne Arten von Tabellen definiert werden:
–
Basistabellen, die auch physikalisch auf den Festspeicher abgebildet werden, und
–
Sichttabellen (Views), die dynamisch generiert werden. Nur die Metadaten (die Struktur) der Sichten
werden gespeichert. Zur Erzeugun g von Sichten gibt es eine eigene SQL- Anweisung (s.u.).
Innerhalb der Klam m ern der CRE AT E TABLE- Anweisung werden die Tabellenspalten mit Attributnam e
und -typ spezifiziert. Wird NOT NULL angegebe n, muß für jedes Tupel in dieser Spalte auch ein Wert
existieren, dies wird bei der Aufnahme der Tupel in die Tabelle überprüft.
Zur Deklaration der Relationenattribute kann man aus folgenden Datentype n wählen:
Standard:
CHAR [ ( länge ) ]
NUME RI C [ ( stellemahl [ , nachko m m a ] )]
DECI MA L [ ( stellenzahl, nachko m m a ) ]
FLOAT [ ( stellenzahl ) ]
INTE G E R
Beispiel
CHAR (25)
- CHA RA C T E R
NUM E RI C (7,2), NUM E RIC (6),
DECIM A L (6,2)
FLOAT (23)
SMALLINT
DOUB LE PRE CI SIO N
REAL
CHARA C T E R VARYIN G ( länge )
VARC H A R (20)
DAT E
TIME
TIMES T A M P
INTE R V A L
BIT
INTE R V A L YEA R TO MONT H, INTERV A L HOUR TO SECO N D
BIT ( Länge )
BIT (255)
BIT VARYI N G ( länge )
BIT VARYIN G (100)
Hierbei sind stellenzahl und nachkomma die Anzahl der gültigen Stellen und die Anzahl der Nachkommastellen, das Maximu m der Stellenzahl ist implementierungsab hä ngig. Am Ende der Kom m aliste
einer Tabellendefinition werden in Standard- SQL Primärschlüssel bzw. Fremdschlüssel spezifiziert.
Die Gegenstück e zu den Erzeugungso perationen sind die syntaktisch analogen DROP- Anweisun gen
(s.u.).
Seite 2
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
1.1.3 Änderung der Tabellenstruktur
ALTER TABLE Tabellenna m e
[ ADD l DRO P l ALTER | RENA M E ] ( Spaltenna m e Datentyp [ NOT NULL ]
{ ,Spaltenna m e Datentyp [ NOT NULL ] } );
Mit der ALTE R- Anweisung ist es möglich, die Struktur eines Relationssche m a s zu ändern.
1.1.4 Löschen von Strukturen
Eine gesa mte Datenbank als Menge von Relationssche m ata kann (mit Inhalt) mit
DRO P DATA B A S E Datenba nkna m e
gelöscht werden, ein einzelnes Relationssche m a mit
DRO P TABL E Tabellenna m e
Die meisten Implementierun gen enthalten im Sinne eines 'orthogonalen' Sprachdesigns die zu den Erzeugungsan weisun ge n symmetrischen Löschanweis ung en:
DRO P VIE W
DRO P SYNO N Y M
DRO P INDEX
DRO P ASSE R T I O N
DRO P DOM AIN
1.2 Manipulationsanweisungen für Relationen - Einfügen , Lösc hen und Ändern
von Tupelmengen
Nach dem Standard werden Tupel mit folgender Operation in eine Tabelle eingetrage n:
INSERT INTO Tabellennam e [ ( Komm aliste der Spalten ) ]
VALUE S ( Liste der Werte ) | SELE CT - Anweisung
Mit dem Schlüsselwort VALUE S können einzelne Zeilen eingefügt werden. Durch die SELECT - Anweisung
(s.u.) wird das Anfrageergeb nis in die Tabelle überno m m e n .
Beispiel:
INSERT INTO E VALUE S ("El","Schmidt","Steglitz")
INSERT INTO E VALUE S ("E2","Meier","Span da u")
INSERT INTO TEMP (BNR, Autor,Verlag)
SELECT B.BNR, B.Autor, B.Verlag
FRO M B
WHE R E B.Preis < 60
Die zu INSER T symmetrische Löschoperation ist
DELE T E
FRO M Tabellenna m e
[ WHE RE Suchbedingung ]
Läßt man die WHE R E- Klausel weg, so werden alle Zeilen gelöscht; sonst nur solche, die die Suchbedingung erfüllen.
Seite 3
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
Um einzelne Spaltenwerte zu verändern, eine Anforderung, die in Anwen dung en häufig ausgeführt
werden muß, verwendet man die UPDAT E- Operation:
UPDA T E Tabellenna m e
SET Spaltennam e = Ausdruck
{ , Spaltenna m e = Ausdruck}
[ WHE RE Suchbedingung ]
Beispiel: Zehnprozentige Preiserhöhung aller Bücher aus dem Verlag Addison- Wesley:
UPDA T E B
SET B.Preis = B.Preis*1.10
WHER E B.Verlag = "Addison- Wesley"
Nahezu alle DBMS bieten in ihren SQL- Implementationen eine Operation oder ein Dienstprogram m
an, um aus (ggf. unterschiedlich formatierten) Dateien Daten in bereits erzeugte Relationssche m at a zu
überneh m en . Diese Anweisung trägt meist den Namen COP Y, die Syntax ist z.T. unterschiedlich. Beispiel
PostgreS Q L:
COPY Tabellenna m e FRO M 'Dateina m e' [ DELI MIT E R ] Zeichen
Mit dieser Anweisung können die Tabellenwerte aus einer Datei importiert werden. Jede Zeile in der
Datei entspricht einer Zeile in der Tabelle (einem Tupel). Die Daten in der Datei müssen in Typ und
Format den Spaltenattributtypen bzw. -formaten entsprechen. Die einzelnen Spalten müssen durch ein
Begrenzu ngszeichen (Vorgabe: TAB) getrennt werden.
Beispiel: Ist der Inhalt der Datei a.dat z.B.
E1 | L1 | B1 l 10.10.2003
E1 l L1 | B4 l 02.10.2003
so liest die Anweisung
COPY A FRO M 'a.dat' DELI MI T E R '|'
die Zeilen aus der ASCII- Datei a.dat in die Tabelle A ein.
2 Die SELECT - Anweisung
Für alle Operationen der Relationenalgebra gibt es eine Entsprechun g in einer bestimmte n Form der
(äußerst komplexen) SELECT - Anweisung. SQL wird häufig geradezu identifiziert mit dieser Anweisung.
Wie kommt man von den relationalen Operationen zur zugehörigen SELECT - Anweisung ? Ein Satz erzeugender Grundoperationen der Relationenalge bra sind Vereinigung, Differenz, kartesisches Produkt,
Selektion und Projektion. Daraus sind z.B. die Operationen natürlicher Verbund, ? -Verbund und Division ableitbar.
Für die folgenden SELECT - Anweisung en gilt, daß die Implementierunge n Duplikate zulassen; diese
können durch SELE CT DISTINCT gezielt beseitigt werden.
Vereinigung :
Die Vereinigung zweier vereinigungsk o m p atibler Relationen r und s wird in SQL als UNION
zweier SELECT - Anfragen formuliert:
SELE CT * FROM r
UNION
SELE CT * FROM s ;
Seite 4
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
Differenz :
Die (Mengen- ) Differenz zweier Relationen r und s ist in SQL- 89 nur mit einer komplizierten
SELE CT- Konstruktion definierbar: r-s , die Menge aller Tupel von r, die nicht in s sind (wobei r und
s vereinigungsko m p atibel sind) läßt sich z.B. mit Hilfe des EXISTS-Prädikats und einer Unteranfrage
darstellen:
SELE CT *
FRO M r
WHER E NOT EXISTS
( SELE CT *
FRO M s
WHE R E r.r1=s.s1,...,r.rn=s.sn );
wobei r1 ... rn und s1l ... sn die jeweils kompatiblen Attribute von r bzw. s sind.
Ab SQL- 92 gibt es
Durchschnitt und
Differenz:
SELECT *
SELE CT *
FRO M r
FRO M r
INTER S E C T
EXCEP T
SELECT *
SELE CT *
FRO M s ;
FRO M s ;
Kartesisches Produkt :
Das kartesische Produkt r x s der beiden Relationen r und s enthält die Attribute von r und s (im Falle der
Übereinstim m u ng doppelt!). Als Tupel in r x s treten alle Kombinatione n von Tupeln aus r und s auf. Dem
entspricht in Kern- SQL die einfache Anweisung
SELE CT * FROM r,s ;
Ab SQL- 92 gibt es den CROSS JOIN (s.u.).
Selektion :
Die Selektion findet in der SELECT- Anweisung im WHE R E- Klauselteil ihre Entsprech ung.
Beispielsweise lautet die Übertragun g der Selektion
?
r.a <= 100 /\ r.name=fritz (r) in SQL:
_
SELE CT * FROM r
WHER E r.a <= 100 AND r.name = "fritz";
bei geeignet festgelegter Relation r.
Projektion :
Die Projektion
? a,b,c (r) der
Tabelle r auf die Attribute a, b, c (einzelne Tabellenspalten) ge-
lingt durch einfache Angabe der Spaltenbez eichner als Kom m aliste nach dem Schlüsselwort SELECT :
SELE CT r.a, r.b, r.c
FRO M r;
Seite 5
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
Verbund :
Beim Verbund r (a Verbund b) s wird die Menge aller Tupel des kartesische n Produkts von r und s gebildet, bei
denen r.a in der Relation mit s.b steht.
SELE CT *
FRO M r,s
WHER E r.a (Verbund) s.b ;
Der natürliche Verbund :
Der natürliche Verbund ist im 89er Standard durch die SELE CT - Anweisung nicht element ar ausdrückbar, kann aber erzeugt werden durch eine Aufeinanderfolge von Kartesische m Produkt, Selektion und Projektion. Zum Beispiel ist für die Relationen r und s mit den Attributen a,b,c,d und
c,d,e,f der Verbund r (nat.Verbund) s:
SELE CT r.a, r.b, r.c, r.d, s.e, s.f
FRO M r,s
WHER E r.c = s.c AND r.d = s.d
In SQL- 99 ist ein reichhaltiges Sortiment von JOIN- Operationen vorgesehe n: CROS S JOIN (Kartesisches Produkt), NATU R A L JOIN (natürlicher Verbund), JOIN ... ON (Theta- JOIN), LEFT OUTER JOIN,
RIGHT OUT ER JOIN (Äußere Verbundop erationen). Sie folgen im wesentlichen der Syntax
SELE CT * FROM r Jointyp s.
Beispiele:
SELE CT *
FRO M r NATU R A L JOIN s;
SELE CT *
FRO M r JOIN s
ON r.a1 = s.a3;
Beachte das Schlüsselwort ON im Gegensatz zu dem sonst an dieser Stelle auftauchend en WHE R E.
3 Syntax der SELECT - Anweisung
Die nachfolgende Syntaxdefinition stellt nur eine Annäherun g an die komplizierte (89er) Syntax des
SELE CT- Komm a nd os dar.
Selectko m m a n d o ::=
SELE CT [ ALL | DISTINCT ] { Selectiiste | * }
FRO M Tabelle [ Alias ] { , Tabelle [ Alias ] }
[ WHE R E Suchbedingu ng ]
[ GRO U P BY Spalte { , Spalte } ]
[ HAVI NG Sucbbedingun g ]
[ ORDE R BY Spaltenna m e [ ASC | DES C ] { Spaltenna m e { ASC | DES C } ]
AllgSelectko m m a n d o ::=
Selectko m m a n d o UNION [ ALL ] Selectkom m a nd o
Im Kern- SQL können statt UNION auch alle oben aufgeführten zweistelligen Operationen (JOIN,
INTE R S E C T , . . .) stehen. Die Erläuterung der Syntax von WHER E - und HAVING - Klausel erfolgt später,
ebenso wie Hinweise zur Benutzung der GRO U P BY- und ORDE R BY- Teile des SELEC T- Kom man d o s.
Seite 6
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
SELECT – Anweisung
Die Grundform der SELE CT- Anweisung ist
SELE CT [ ALL l DISTINC T ] <Kom m aliste von Spaltenbezeichnern>
FRO M
<Kom m aliste von Tabellenbez eichnern>
[WHE RE
<Bedingung> ] ;
ALL (default) läßt doppelte Zeilen in der Ergebnistabelle zu.
DISTINC T unterdrückt doppelte Zeilen im Ergebnis.
Im FRO M- Teil werden die verwendete n Relationen festgelegt.
Im WHER E - Teil werden aufgrund der Bedingung die Tupel der Ergebnisrelation selektiert .
Es ist üblich, die einzelnen Kompone nten der SELEC T - Anweisun g je in eine Zeile zu schreiben; die Syntax erzwingt dies aber nicht. Die Reihenfolge der Komp one nten ist dem natürlichsprachlichen Gebrauch
nache m pfu nde n.
Das Ergebnis einer SELECT - Anfrage ist im allgemeinen wieder eine Tabelle, die aus anderen Tabellen
zusam m e n ge s etzt worden ist, also eine Menge von Tupeln.
Die Ordnung der Zeilen der Tabelle ist unspezifiziert (impleme ntations ab hän gig), mit der optionalen Klausel (ORDE R BY, s.u.) kann aber eine bestim mte Reihenfolge erzwung en werden. Wird die ganze
WHE R E - Klausel weggelass en, so bezieht sich die Anfrage auf alle Zeilen der Tabellenreferenz en. Die Spalten der Tabellen müssen ggf. durch den Tabellenbezeichner qualifiziert werden, wenn keine Eindeutigkeit
gegeben ist.
Beispiel: Suche alle Bücher mit Seitenzahlen zwischen 300 und 600 heraus.
Lösung: Relationenalge braisc h:
(pi)BNr( (sigma) Seiten>=300 /\ Seiten<=600 (B))
SQL:
select bnr
frorn b
where b.Seiten >= 300 AND b.Seiten <= 600 ;
WHERE - Ausdrücke
In der WHER E- Klausel können zum Vergleich von Attributwerten die Operatoren BET W E E N AND, IN und
LIKE verwendet werden.
BET W E E N . . . AND . . .
sucht nach Werten innerhalb eines Bereiches
LIKE
vergleicht die Zeichenfolge einer Spalte mit einer
festgelegten Zeichenfolge
Mit Hilfe der ORDE R BY- Klausel werden eine oder mehrere Spalten in aufsteigend er (ASC) bzw. absteigender (DESC) Reihenfolge sortiert.
SELE CT [ ALL l DISTINC T ] <Kom m aliste von Spaltenbezeichnern>
FRO M
<Kom m aliste von Tabellenbez eichnern>
ORDE R BY
<Spaltenbez eichner> [ ASC l DESC ]
[ , <Spaltenbezeichner> [ ASC l DES C] .. ] ;
Seite 7
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
Beispiel:
SELE CT a.ENr, a.Datum
FRO M a
ORDE R BY a.Datu m ASC ;
Sichten
( read only )
Mit Hilfe des Sichten- Konzepts ist 'information hiding' möglich. Sichten gestatten aber auch (berechn ete)
Daten zur Verfügung zu stellen, die es in dieser Form im System gar nicht gibt. Die Daten werden mittels
einer SELE C T- Anweisung aus den vorhanden en Tabellen zusam m en g estellt.
CREA T E VIEW Sichtnam e [ ( Komm a ndoliste von Spaltenbez eichnern )]
AS SELE CT- Anweisung
[ WITH CHECK OPTIO N ]
Beispiel:
Mit der Anweisung
CREA T E VIEW NeuereBu ec her
AS SELE CT BNR, Autor, Jahr, Preis
FRO M B
WHER E Jahr > 1990
WITH CHE CK OPTIO N
[ ;]
bei PostgreS Q L ist hier Schluß
ist in PostgreS Q L nicht implementiert
wird dem Benutzer die Existenz einer neuen Tabelle NeuereBu ec her vorgespiegelt.
Mit der WITH CHEC K OPTIO N werden in die Sicht und damit in die Basistabelle neu einzufügend e
oder zu aktualisierend e Zeilen auf ihre semantische Übereinstim m u n g mit der Sichtdefinition überprüft.
Ist keine Übereinstim m un g gegeben, so wird das Einfügen oder Updaten abgelehnt. Dieser Mechanism us
dient der semantische n Konsistenzerhaltung der Datenbank(an w e n d un g). Zu beachten ist aber, daß ein
Updaten von Sichten nur unter ganz bestimmte n an die Definition der Sicht gestellten Voraussetzung en
möglich ist. Dies hat prinzipielle logische Ursachen. Die genauen Voraussetzungen für ein Update von
Sichten sind implementierungsab hä ngig (PostgreS Q L hat noch keine updatefähigen Sichten).
Aggregatfunktionen, GROUP BY
Spalten mit den Typen INTEG E R, SMALLINT , FLOAT, SMALLF L O A T , DECIM A L und MONE Y gestatten
Sum m en- und Mittelwertsbildung en ebenso wie die Ermittlung des Maximal- bzw. Minimalwertes.
Für Spalten anderen Datentyps kann man immerhin Zähloperationen durchführen, um etwa die Anzahl der
Attributwerte eines Attributs zu ermitteln, die einer bestimmten Bedingung genügen.
Die wichtigsten im Standard definierten Aggregatfunktionen sind:
COUNT
SUM
AVG
MAX
zählt die Einträge in einer Spalte
summiert die Einträge einer numerischen Spalte
mittelt die Einträge einer numerische n Spalte
Maximu m einer numerischen Spalte
MIN
Minimu m einer numerischen Spalte
Seite 8
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
COUNT (*)
liefert die Anzahl der Zeilen, wobei Zeilen mit Nullwerten mitgezählt werden
Die Aggregatfunktionen können anstelle eines Spaltenbezeichners in der SELECT - Klausel verwendet
werden.
Beispiel:
SELE CT MIN (Preis), AVG (Preis) , MAX (Preis)
FRO M B ;'
Mit dieser Anweisung wird eine neue Relation mit drei unbenannten Attributen erzeugt, die vom gleichen
Typ wie der Attributtyp von Preis sind.
Insbesondere bei numerischen Auswertunge n sind zwei optionale SELE C T- Konstruktionen nützlich:
die GRO UP BY- Klausel und mit ihr die HAVING - Klausel.
Beispiel: In der Bücher- Relation B gibt es verschieden e Bücher vom gleichen Autor. Angeno m m e n, man
möchte nur wissen, wann welcher Autor das letzte Buch geschrieben hat, so kann man B nach Autor
gruppieren und das Maximu m des Erscheinungsdat u m s ermitteln.
SELE CT Autor, MAX(Jahr)
FRO M b
GROU P BY Autor ;
Damit werden die Tupel für jeden Autor zu einer impliziten Tabelle zusam m e n g ef aßt, bei der dann
die Selektion das Paar Autor - LetztesJahr liefert.
Syntax:
SELE CT [ ALL | DISTI NCT ] <Kom m aliste von Spaltenbezeichnern>
FRO M
<Kom m aliste von Tabellenbez eichnern>
[ GRO U P BY <Kom m aliste von Spaltenbezeich nern> ]
Jeder der hinter GRO U P BY angegeben en Spaltenbez eichner muß vor der FROM- Anweisun g ebenfalls
aufgeführt sein. Genaugen o m m e n wird von einer gruppierten Anfrage gar keine Tabelle zurückgeliefert,
sondern eine Menge von Tabellen. Das Ergebnis ist übrigens nicht notwendig sortiert.
HAVING-Klausel
Im Zusam m e n ha ng mit der GRO U P- Klausel tritt häufig die HAVING- Klausel auf. Durch eine logische
Bedingung nach dem Schlüsselwort HAVI NG werden bestimmte Gruppen selektiert. Mit anderen Worten:
HAVING spielt die gleiche Rolle für GRO UP BY wie WHE R E für SELE CT.
Beispiel: (Natürlichsprachliche Formulierung?)
SELE CT a.LNr, COUNT(*), MAX (a.Datu m)
FRO M a
GROU P BY a.LNr
HAVING
MAX (a.Datum) > '15.11.2003' ;
Unteranfragen
Normalerweise enthält die SELE CT- Anweisung im WHER E - Klauselteil einfache logische Selektionsbedingunge n, entspricht also in diesem Teil der Selektionsoperation der Relationenalgebra. Da die WHER EBedingung zu einem logischen Wert führt und nicht nur Vergleiche, sondern auch Aussagen über Mengen zu
Seite 9
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
boole' schen Werten ausgewertet werden, liegt es nahe, das Enthaltensein, das Nichtenthaltensein und allund existenzquantifizierte Aussagen über Mengen zuzulassen. Deshalb existieren in SQL die
Mengenprädikate
IN, NOT IN, ALL und ANY.
Beispiel:
SELE CT L.LNa m e
FRO M L
WHER E L.Bezirk IN ( 'Charlottenburg- Wilmersdorf, 'Mitte' );
liefert
TU- Lehrbuchs ainmiung
TU- Informatik
HU- UB
Labi
Die Frage ist nur, wie im allgemeinen die zugehörigen Mengen gewonnen werden. Hierzu dienen die
Unteranfragen. Sie führen zur Schachtelung von SELE CT- Anfragen.
Sollen beispielsweise alle Büchereina m e n für Entleihvorgänge gefunden werden, die Entleiher E1 betreffen, so ist es sinnvoll, erst die Menge der Entleihvorgä nge von E1 zu ermitteln und dann die Büchereinamen zu diesen Num mern herauszusuchen.
SELE CT L.LNa m e
FRO M L
WHER E L.LNr IN
( SELE CT a.LNr
FRO M a
WHE R E a.ENr = 'E1');
Man erhält nur dann sinnvolle Antwortme ng en, wenn bei der Unteranfrage genau eine Spalte entsteht. Der
Datentyp des Attributs dieser Spalte muß kompatibel sein zum Datentyp des in der Menge zu suchenden
Elements.
Die Anfrage des Beispiels könnte auch mit dem Existenz- Operator formuliert werden:
SELE CT L.LNa m e
FRO M L
WHER E EXIST S
(SELECT *
FRO M a
WHE R E a.LNr = L.LNr
AND
a.ENr = 'E1' );
Der Ausdruck EXISTS Unteranfrage wird wahr, falls die Unteranfrage mindestens ein Tupel liefert. Beachte, daß die einschränkend e Spaltenbeding ung in der EXIST S- Variante wegfällt und deshalb im WHER E Selektionsteil der Unteranfrage auftauchen muß.
Seite 10
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
4 Beispiele
t1 : num
1
2
3
name t2 : num wert t3 : num name
a
1 xxx
1 a3
b
3 yyy
2 b3
c
5 zzz
3 c3
t4 : num wert
1 xxx
3 yyy
t5 : num wert t6 : num name
1 xxx
1 a6
2 yyy
2 b6
3 c6
4 d6
5 e6
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
CROS S JOIN
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
testjoin=# select * from t1 cross join t2;
testjoin=# select * from t1, t2;
num | name | num | wert
num | name | num | wert
-----+- -----+- ----+- ---------+- -----+- ----+- ----1 | a | 1 | xxx
1 | a | 1 | xxx
1 | a | 3 | yyy
1 | a | 3 | yyy
1 | a | 5 | zzz
1 | a | 5 | zzz
2 | b | 1 | xxx
2 | b | 1 | xxx
2 | b | 3 | yyy
2 | b | 3 | yyy
2 | b | 5 | zzz
2 | b | 5 | zzz
3 | c | 1 | xxx
3 | c | 1 | xxx
3 | c | 3 | yyy
3 | c | 3 | yyy
3 | c | 5 | zzz
3 | c | 5 | zzz
(9 rows)
(9 rows)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
INNER JOIN
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
testjoin=# select * from t1 inner join t2 on t1.num = t2.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)
testjoin=# select * from t1 inner join t3 on t1.num = t3.num;
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a3
2 | b | 2 | b3
3 | c | 3 | c3
(3 rows)
testjoin=# select * from t1 inner join t4 on t1.num = t4.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)
Seite 11
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from t1 inner join t5 on t1.num = t5.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 | b | 2 | yyy
(2 rows)
testjoin=# select * from t1 inner join t6 on t1.num = t6.num;
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a6
2 | b | 2 | b6
3 | c | 3 | c6
(3 rows)
testjoin=# select * from t2 inner join t5 on t2.num = t5.num;
num | wert | num | wert
-----+- -----+- ----+- ----1 | xxx | 1 | xxx
(1 row)
testjoin=# select * from t2 inner join t5 on t2.wert = t5.wert;
num | wert | num | wert
-----+- -----+- ----+- ----1 | xxx | 1 | xxx
3 | yyy | 2 | yyy
(2 rows)
------------------------------------------------------------------testjoin=# select * from t1 inner join t2 using (num);
num | name | wert
-----+- -----+- ----1 | a | xxx
3 | c | yyy
(2 rows)
testjoin=# select * from t1 inner join t2 using (name);
ERRO R: JOIN/ USI N G column "name" not found in right table
testjoin=# select * from t1 inner join t2 using (wert);
ERRO R: JOIN/ USI N G column "wert" not found in left table
testjoin=# select * from t1 inner join t3 using (num);
num | name | name
-----+- -----+- ----1 | a | a3
2 | b | b3
3 | c | c3
(3 rows)
testjoin=# select * from t1 inner join t4 using (num);
num | name | wert
-----+- -----+- ----1 | a | xxx
3 | c | yyy
(2 rows)
Seite 12
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from
num | name | wert
-----+- -----+- ----1 | a | xxx
2 | b | yyy
(2 rows)
testjoin=# select * from
num | name | name
-----+- -----+- ----1 | a | a6
2 | b | b6
3 | c | c6
(3 rows)
testjoin=# select * from
name | num | num
------+- ----+- ---(0 rows)
testjoin=# select * from
num | wert | wert
-----+- -----+- ----1 | xxx | xxx
(1 row)
testjoin=# select * from
wert | num | num
------+- ----+- ---xxx | 1 | 1
yyy | 3 | 2
(2 rows)
t1 inner join t5 using (num);
t1 inner join t6 using (num);
t1 inner join t6 using (name);
t2 inner join t5 using (num);
t2 inner join t5 using (wert);
------------------------------------------------------------------testjoin=# select * from t1 natural inner join t2;
num | name | wert
-----+- -----+- ----1 | a | xxx
3 | c | yyy
(2 rows)
testjoin=# select * from t1 natural inner join t3;
num | name
-----+- ----(0 rows)
testjoin=# select * from t1 natural inner join t4;
num | name | wert
-----+- -----+- ----1 | a | xxx
3 | c | yyy
(2 rows)
testjoin=# select * from t1 natural inner join t5;
num | name | wert
-----+- -----+- ----1 | a | xxx
2 | b | yyy
(2 rows)
Seite 13
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from t1 natural inner join t6;
num | name
-----+- ----(0 rows)
testjoin=# select * from t5 natural inner join t6;
num | wert | name
-----+- -----+- ----1 | xxx | a6
2 | yyy | b6
(2 rows)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
LEFT (OUT ER)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
testjoin=# select * from t1 left outer join t2 on t1.num = t2.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 |b |
|
3 | c | 3 | yyy
(3 rows)
testjoin=# select * from t1 left outer join t3 on t1.num = t3.num;
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a3
2 | b | 2 | b3
3 | c | 3 | c3
(3 rows)
testjoin=# select * from t1 left outer join t4 on t1.num = t4.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 |b |
|
3 | c | 3 | yyy
(3 rows)
testjoin=# select * from t1 left outer join t5 on t1.num = t5.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 | b | 2 | yyy
3 |c |
|
(3 rows)
testjoin=# select * from t1 left outer join t6 on t1.num = t6.num;
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a6
2 | b | 2 | b6
3 | c | 3 | c6
(3 rows)
Seite 14
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from t6 left
num | name | num | name
-----+- -----+- ----+- ----1 | a6 | 1 | a
2 | b6 | 2 | b
3 | c6 | 3 | c
4 | d6 |
|
5 | e6 |
|
(5 rows)
testjoin=# select * from t1 left
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
(3 rows)
testjoin=# select * from t1 left
num | name | name
-----+- -----+- ----1 | a | a3
2 | b | b3
3 | c | c3
(3 rows)
testjoin=# select * from t1 left
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
(3 rows)
testjoin=# select * from t1 left
num | name | wert
-----+- -----+- ----1 | a | xxx
2 | b | yyy
3 |c |
(3 rows)
testjoin=# select * from t1 left
num | name | name
-----+- -----+- ----1 | a | a6
2 | b | b6
3 | c | c6
(3 rows)
testjoin=# select * from t6 left
num | name | name
-----+- -----+- ----1 | a6 | a
2 | b6 | b
3 | c6 | c
4 | d6 |
5 | e6 |
(5 rows)
outer join t1 on t1.num = t6.num;
outer join t2 using (num);
outer join t3 using (num);
outer join t4 using (num);
outer join t5 using (num);
outer join t6 using (num);
outer join t1 using (num);
Seite 15
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from
num | wert | wert
-----+- -----+- ----1 | xxx | xxx
3 | yyy |
5 | zzz |
(3 rows)
testjoin=# select * from
wert | num | num
------+- ----+- ---xxx | 1 | 1
yyy | 3 | 2
zzz | 5 |
(3 rows)
testjoin=# select * from
num | wert | wert
-----+- -----+- ----1 | xxx | xxx
2 | yyy |
(2 rows)
testjoin=# select * from
wert | num | num
------+- ----+- ---xxx | 1 | 1
yyy | 2 | 3
(2 rows)
testjoin=# select * from
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
(3 rows)
testjoin=# select * from
num | wert | name
-----+- -----+- ----1 | xxx | a
3 | yyy | c
5 | zzz |
(3 rows)
testjoin=# select * from
num | name
-----+- ----1 |a
2 |b
3 |c
(3 rows)
testjoin=# select * from
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
(3 rows)
t2 left outer join t5 using (num);
t2 left outer join t5 using (wert);
t5 left outer join t2 using (num);
t5 left outer join t2 using (wert);
t1 natural left outer join t2;
t2 natural left outer join t1;
t1 natural left outer join t3;
t1 natural left outer join t4;
Seite 16
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from t1 natural left outer join t5;
num | name | wert
-----+- -----+- ----1 | a | xxx
2 | b | yyy
3 |c |
(3 rows)
testjoin=# select * from t1 natural left outer join t6;
num | name
-----+- ----1 |a
2 |b
3 |c
(3 rows)
testjoin=# select * from t6 natural left outer join t1;
num | name
-----+- ----1 | a6
2 | b6
3 | c6
4 | d6
5 | e6
(5 rows)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RIGHT (OUT E R) nur noch am Beispiel ON ( nicht using , nicht natural)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
testjoin=# select * from t1 right outer join t2 on t1.num = t2.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
3 | c | 3 | yyy
|
| 5 | zzz
(3 rows)
testjoin=# select * from t1 right outer join t3 on t1.num = t3.num;
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a3
2 | b | 2 | b3
3 | c | 3 | c3
(3 rows)
testjoin=# select * from t1 right outer join t4 on t1.num = t4.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)
testjoin=# select * from t1 right outer join t5 on t1.num = t5.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 | b | 2 | yyy
(2 rows)
Seite 17
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from t1 right outer join t6 on t1.num = t6.num;
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a6
2 | b | 2 | b6
3 | c | 3 | c6
|
| 4 | d6
|
| 5 | e6
(5 rows)
testjoin=# select * from t2 right outer join t5 on t2.num = t5.num;
num | wert | num | wert
-----+- -----+- ----+- ----1 | xxx | 1 | xxx
|
| 2 | yyy
(2 rows)
testjoin=# select * from t2 right outer join t5 on t2.wert = t5.wert;
num | wert | num | wert
-----+- -----+- ----+- ----1 | xxx | 1 | xxx
3 | yyy | 2 | yyy
(2 rows)
testjoin=# select * from t5 right outer join t2 on t2.num = t5.num;
num | wert | num | wert
-----+- -----+- ----+- ----1 | xxx | 1 | xxx
|
| 3 | yyy
|
| 5 | zzz
(3 rows)
testjoin=# select * from t5 right outer join t2 on t2.wert = t5.wert;
num | wert | num | wert
-----+- -----+- ----+- ----1 | xxx | 1 | xxx
2 | yyy | 3 | yyy
|
| 5 | zzz
(3 rows)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
FULL (OUT ER)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
testjoin=# select * from t1 full outer join t2 on t1.num = t2.num;
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 |b |
|
3 | c | 3 | yyy
|
| 5 | zzz
(4 rows)
testjoin=# select * from t1 full outer join t3 on t1.num = t3.num;
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a3
2 | b | 2 | b3
3 | c | 3 | c3
(3 rows)
Seite 18
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from t1 full outer join t4 on t1.num
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 |b |
|
3 | c | 3 | yyy
(3 rows)
testjoin=# select * from t1 full outer join t5 on t1.num
num | name | num | wert
-----+- -----+- ----+- ----1 | a | 1 | xxx
2 | b | 2 | yyy
3 |c |
|
(3 rows)
testjoin=# select * from t1 full outer join t6 on t1.num
num | name | num | name
-----+- -----+- ----+- ----1 | a | 1 | a6
2 | b | 2 | b6
3 | c | 3 | c6
|
| 4 | d6
|
| 5 | e6
(5 rows)
testjoin=# select * from t6 full outer join t1 on t1.num
num | name | num | name
-----+- -----+- ----+- ----1 | a6 | 1 | a
2 | b6 | 2 | b
3 | c6 | 3 | c
4 | d6 |
|
5 | e6 |
|
(5 rows)
testjoin=# select * from t6 natural full outer join t1;
num | name
-----+- ----1 |a
1 | a6
2 |b
2 | b6
3 |c
3 | c6
4 | d6
5 | e6
(8 rows)
testjoin=# select * from t1 natural full outer join t6;
num | name
-----+- ----1 |a
1 | a6
2 |b
2 | b6
3 |c
3 | c6
4 | d6
= t4.num;
= t5.num;
= t6.num;
= t6.num;
Seite 19
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
5 | e6
(8 rows)
testjoin=# select * from
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
5 |
| zzz
(4 rows)
testjoin=# select * from
num | name
-----+- ----1 |a
1 | a3
2 |b
2 | b3
3 |c
3 | c3
(6 rows)
testjoin=# select * from
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
(3 rows)
testjoin=# select * from
num | name | wert
-----+- -----+- ----1 | a | xxx
2 | b | yyy
3 |c |
(3 rows)
testjoin=# select * from
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
5 |
| zzz
(4 rows)
testjoin=# select * from
num | name | name
-----+- -----+- ----1 | a | a3
2 | b | b3
3 | c | c3
(3 rows)
t1 natural full outer join t2;
t1 natural full outer join t3;
t1 natural full outer join t4;
t1 natural full outer join t5;
t1 full outer join t2 using(num);
t1 full outer join t3 using(num);
Seite 20
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
testjoin=# select * from t1 full outer join t4 using(num);
num | name | wert
-----+- -----+- ----1 | a | xxx
2 |b |
3 | c | yyy
(3 rows)
testjoin=# select * from t1 full outer join t5 using(num);
num | name | wert
-----+- -----+- ----1 | a | xxx
2 | b | yyy
3 |c |
(3 rows)
testjoin=# select * from t1 full outer join t6 using(num);
num | name | name
-----+- -----+- ----1 | a | a6
2 | b | b6
3 | c | c6
4 |
| d6
5 |
| e6
(5 rows)
testjoin=# select * from t6 full outer join t1 using(num);
num | name | name
-----+- -----+- ----1 | a6 | a
2 | b6 | b
3 | c6 | c
4 | d6 |
5 | e6 |
(5 rows)
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
======= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
GROU P BY
=> SELE CT * FROM test1; => SELE CT x FRO M test1 GRO U P BY x;
x |y
-----a |3
c |2
b |5
a |1
x
-a
b
c
Die zweite Anweisung hätte man nicht als SELECT * FRO M test1 GRO U P BY x; schreiben können, weil es
keinen einzigen Wert der Spalte y gibt, der der Gruppe hätte zugeordn et werden können.
Seite 21
Oberschule am Elsengrund – Gymnasium – Berlin Marzahn / Hellersdorf – Elsenstraße 9-11 – 12623 Berlin – Fachbereich Informatik
=> SELE CT x, sum(y) FRO M test1 GRO U P BY x;
x | sum
----------.
a |4
b |5
c |2
HAVING
Wenn die Tabelle mit GRO U P BY gruppiert wurde aber nur bestim mte Gruppen von Interesse sind, dann
kann die HAVIN G Klausel verwendet werden.
=> SELE CT x, sum(y) FRO M test1 GRO U P BY x HAVI NG sum(y) > 3;
x | sum
-----------.
a |4
b |5
=> SELE CT x, sum(y) AS betrag FRO M test1 GRO U P BY x HAVIN G sum(y) > 3;
x | betrag
-----------.
a |4
b |5
Seite 22