4-auf-1-PDF

9-3
Andreas Heuer, Gunter Saake – Datenbanken I
Andreas Heuer, Gunter Saake – Datenbanken I
■ Selektionsbedingungen an Gruppen
having
■ Gruppierung für Aggregatfunktionen
group by
■ Geschachtelte Anfragen (wieder ein SFW-Block)
■ Verbundbedingungen
■ Selektionsbedingungen
where
‘alias’)
■ eventuelle Umbenennungen (durch Tupelvariable oder
■ zu verwendende Relationen
from
■ arithmetische Operationen und Aggregatfunktionen
liefert die gesamte Relation
■ Projektionsliste
➠ SQL-Kern
Relationale Datenbanksprachen
9-1
➠ SQL-Versionen
➠ Weitere Sprachkonstrukte von SQL
9-2
Andreas Heuer, Gunter Saake – Datenbanken I
select
from
Bereichskalkül
Andreas Heuer, Gunter Saake – Datenbanken I
Tupelkalkül
Relationenalgebra
QUEL
select
from
select
ISBL
SQL-Kern
Kommerzielle
Sprachen
Grundlagen
Sprachen und ihre Grundlagen
from-Klausel
Syntax
SQL
QBE
Beispiel
9-4
Bezug zum Tupelkalkül
Weitere Verbunde in SQL-92
korrespondierende Kalkülausdrücke automatisch sicher
■
Gleichverbund
!
■
kartesisches Produkt
cross join
from
from
select
select
■
Verbund über Verbundbedingungen
join-Operator: -Verbund
■
from
select
where
Ergebnis hat acht Spalten:
Andreas Heuer, Gunter Saake – Datenbanken I
on
9-5
join
from
select
Selbst-Verbund (Self-Join) für tupelübergreifende Selektionen
Andreas Heuer, Gunter Saake – Datenbanken I
Verbunde als explizite Operatoren
from
9-8
■
Einführung von Tupelvariablen: etwa auf eine Relation mehrfach zugreifen
select
Andreas Heuer, Gunter Saake – Datenbanken I
SQL-92-Spezialitäten
Bei mehr als einer Relation hinter from: kartesisches Produkt
from
natural join
jeder SFW-Block hinter from (SQL-92 orthogonal)
Kartesisches Produkt
select
9-6
from
Andreas Heuer, Gunter Saake – Datenbanken I
■
natürlicher Verbund
select
■
■
join
using
■
■
from
select
select
from
where
9-7
Projektionsergebnis Menge oder Multimenge
Äußere Verbunde II
from
C
4
5
4
B
3
4
C
A
2
B
2
3
C
4
B
3
A
1
2
A
2
4
5
select
C
4
5
B
3
4
C
B
2
3
4
A
1
2
B
2
3
A
1
2
Name
Meyer
Schulz
Müller
Meyer
from
select distinct
Name
Meyer
Schulz
Müller
Andreas Heuer, Gunter Saake – Datenbanken I
9-10
Andreas Heuer, Gunter Saake – Datenbanken I
Die select-Klausel
Äußere Verbunde
Statt inner join nun outer join (dangling tuples übernehmen und mit Nullwerten auffüllen)
Relationenalgebra: abschließende Projektion
Relationenkalkül: Zielliste
full outer join: in beiden Operanden
from
distinct
select
■
9-12
left outer join: im linken Operanden
■
right outer join: im rechten Operanden
■
■
Attribute aus from-Relationen
■
Arithmetische Ausdrücke über Attributen und Konstanten
■
Aggregatfunktionen über Attributen
distinct: Ergebnismenge statt Multimenge
Andreas Heuer, Gunter Saake – Datenbanken I
9-9
Andreas Heuer, Gunter Saake – Datenbanken I
9-11
Tupelvariablen und Relationennamen II
Die where-Klausel
Selektionsbedingung der Relationenalgebra oder Verbundbedingung
(falsch!)
where
from
select
Bedingung:
(richtig)
Konstanten-Selektion
■
Attribut-Selektion zwischen Attributen mit kompatiblen Wertebereichen:
■
select
from
where
select
from
where
Andreas Heuer, Gunter Saake – Datenbanken I
9-14
Andreas Heuer, Gunter Saake – Datenbanken I
Tupelvariablen und Relationennamen
Bezug zum Tupelkalkül
Angabe der Attributnamen durch Präfix ergänzen
select: Zielliste im Tupelkalkül
Letzte Anfrage entspricht
from
select
from
und
select
9-16
Tupelvariable kann benutzt werden:
Andreas Heuer, Gunter Saake – Datenbanken I
select
from
9-13
Andreas Heuer, Gunter Saake – Datenbanken I
9-15
Bereichsselektion
Ungewißheitsselektion II
Anwendung: Selektion nach Büchern von Benjamin/Cummings
and
between
like
9-18
or
or
or
or
or
from
and
between
from
select
where
and
Andreas Heuer, Gunter Saake – Datenbanken I
Andreas Heuer, Gunter Saake – Datenbanken I
Verbundbedingung
9-20
Ungewißheitsselektion
■
theoretisch nur Abkürzung für disjunktiv verknüpfte Bedingung
■
Syntax
Beispiel: natürlicher Verbund
like
■
Spezialkonstante kann beinhalten
◆
select
from
where
where
ist Abkürzung für
Beispiel
select
where
Abkürzung für
from
select
◆
‘%’ (kein oder beliebig viele Zeichen)
‘ ’ (genau ein Zeichen)
auch Gleichverbund und -Verbund erlaubt
Andreas Heuer, Gunter Saake – Datenbanken I
9-17
Andreas Heuer, Gunter Saake – Datenbanken I
9-19
Bezug zum Tupelkalkül
■
Das in-Prädikat und geschachtelte Anfragen
where-Klausel: qualifizierende Formel in Tupelkalkülanfragen
Syntax:
!
■
Beispiel:
!
!
from
from
in
select
entspricht im Tupelkalkül
select
where
■
in
and
or
■
select
from
where
■
natürlicher Verbund mit nachfolgender Projektion
Andreas Heuer, Gunter Saake – Datenbanken I
9-22
Andreas Heuer, Gunter Saake – Datenbanken I
Weitere Bedingungen
■
Schachtelung von Anfragen
Null-Selektion
is null
■
boolesche Ausdrücke mit Konnektoren
or, and und not
where-Klausel kann geschachtelt werden
■
SFW-Blöcke liefern im allgemeinen mehrere Werte
■
Vergleiche mit Wertemengen
◆
◆
9-21
Standardvergleiche in Verbindung mit Quantoren all ( ) oder any ( )
spezielle Prädikate für den Zugriff auf Mengen in und exists
Andreas Heuer, Gunter Saake – Datenbanken I
Quantifizierte Bedingungen, wenn ein Argument in Vergleich Menge liefert
(all, any, some und exists)
■
■
Andreas Heuer, Gunter Saake – Datenbanken I
9-24
9-23
Verzahnt geschachtelte Anfragen
■
Das exists-Prädikat
in der inneren Anfrage Relationen- oder Tupelvariablen-Name aus dem
from-Teil der äußeren Anfrage verwenden
testet, ob Ergebnis der inneren Anfrage nicht leer
select
from
where exists
select
from
where
!
Andreas Heuer, Gunter Saake – Datenbanken I
!
9-26
Andreas Heuer, Gunter Saake – Datenbanken I
9-28
Verzahnt geschachtelte Anfragen II
Das in-Prädikat und geschachtelte Anfragen II
■
select
from
where
in
select
from
where
■
■
Abarbeitung
1. In der äußeren Anfrage das erste Personen-Tupel untersuchen
Ergebnis in innere Anfrage einsetzen
2. innere Anfrage
select
from
where
auswerten, liefert Werteliste ( 2.0, 2.3 )
3. Ergebnis der inneren Anfrage in die äußere einsetzen
1.0 in ( 2.0, 2.3 ) ergibt false
ersten Prüfer nicht berücksichtigen
4. in der äußeren Anfrage das zweite Personen-Tupel untersuchen usw.
1. Ergebnis der inneren select-Anweisung hinter in als Liste von Konstanten einsetzen
2. Dann modifizierte Anfrage
from
select
where
in
Abarbeitung
!
abarbeiten
Andreas Heuer, Gunter Saake – Datenbanken I
9-25
Andreas Heuer, Gunter Saake – Datenbanken I
9-27
Bezug zum Tupelkalkül
Kompatible Attribute
exists-Prädikat: -Quantor des Tupelkalküls
■
Attribute sind kompatibel bei kompatiblen Wertebereichen
■
andere Schachtelungsoperatoren ebenfalls auf Quantoren zurückführen
■
Zwei Wertebereiche sind kompatibel, wenn sie
◆
simulieren
-Quantor mit
■
■
◆
gleich sind oder
beides auf
basierende Wertebereiche sind (unabhängig von
der Länge der Strings) oder
beides numerische Wertebereiche sind (unabhängig von dem genauen
Typ) wie
oder
)
◆
9-30
Kompatible Attribute können in Vergleichen und Mengenoperationen benutzt werden
Andreas Heuer, Gunter Saake – Datenbanken I
exists: Simulation des Allquantors
9-32
SQL-92: Tupelbildungen
!
select
from
where
where
row constructors bilden Tupel aus Konstanten oder Attributen
■
!
Attribute müssen kompatibel sein (siehe unten)
and
■
select
from
where
select
from
where not exists
select
from
where
and not exists
Andreas Heuer, Gunter Saake – Datenbanken I
■
für alle
gilt
Andreas Heuer, Gunter Saake – Datenbanken I
!
!
9-29
Andreas Heuer, Gunter Saake – Datenbanken I
und
wahr, wenn ein existiert, für das
(lexikographische Ordnung)
9-31
Simulation der Differenz
Vereinigung, Durchschnitt und Differenz in SQL-92
union, intersect und except orthogonal in andere Anfragen einsetzbar
!
!
select count
from
select
select
!
in SQL
!
!
!
select
from
from
not in
select
where
from
union
from
!
corresponding-Klausel: zwei Relationen nur über ihren gemeinsamen Bestandteilen vereinigen
!
!
!
9-36
Vereinigung und äußere Verbunde
left outer natural join
from
select
SQL-89: Vereinigung union einzige Mengenoperation
umgesetzt
union
null
"
Andreas Heuer, Gunter Saake – Datenbanken I
select
from
where
!
9-33
from
where not exists
Vereinigung nur als “äußerste” Operation erlaubt.
■
Ergebnis: Attributnamen des linken Operanden
where
■
Andreas Heuer, Gunter Saake – Datenbanken I
from
union
select
from
Attributkompatiblität: von
und von ,
von
und von , von
und von
union select
from
select
select
Beispiel:
■
Andreas Heuer, Gunter Saake – Datenbanken I
SQL-89: Vereinigung
■
9-34
union corresponding
Andreas Heuer, Gunter Saake – Datenbanken I
select count
from
9-35
Weitere Sprachkonstrukte von SQL
from
select
union (nicht orthogonal)
Vereinigung
Ausdrücke werden tupelweise ausgewertet
where mit Schachtelung
■
Durchschnitt
◆
where mit Schachtelung
◆
Differenz
numerischen Wertebereiche: etwa , , ,
Strings: char length, Konkatenation , substring (Teilzeichenkette)
Datumstypen, Zeitintervalle: current date, current time, , ,
from mit Tupelvariable
◆
Umbenennung
select distinct
where ohne Schachtelung
from, where
from mit join oder natural join
from mit Tupelvariable
as
where mit Schachtelung
except corresponding
where mit Schachtelung
intersect corresponding
union corresponding
innerhalb von select und where: statt Attribute auch skalare Ausdrücke
select distinct
where ohne Schachtelung
from, where
■
Projektion
Selektion
Verbund
9-40
Operationen auf Wertebereichen
SQL-92
SQL-89
Andreas Heuer, Gunter Saake – Datenbanken I
Vergleich Relationenalgebra und SQL
Relationenalgebra
as
9-38
Andreas Heuer, Gunter Saake – Datenbanken I
Andreas Heuer, Gunter Saake – Datenbanken I
Änderungs-Operationen
■
select
from
Nullwerte
■
order by
■
in SQL-92 : Attributname zuordnen:
Beispiele für Selbst-Verbund
■
Quantoren und Mengenvergleiche
from
■
select
group by und having
■
Aggregatfunktionen
■
Bsp.: zweite Spalte nicht benannt, in SQL-89 über Spaltennummer identifizierbar:
Operationen auf Wertebereichen
■
SQL-92-Spezialitäten
Ergebnis
9-37
Andreas Heuer, Gunter Saake – Datenbanken I
ISBN
3-89319-175-5
0-8053-1753-8
0-8053-1753-8
0-201-53771-0
3-929821-31-1
54,86
50,24
61,70
60,73
54,86
9-39
Aggregatfunktionen: Beispiele
■
darf Bezug nehmen auf
having ist Selektionsbedingung auf gruppierter Relation
from
!
◆
!
◆
from
■
Gruppierungsattribute
beliebige Aggregatfunktionen über Nicht-Gruppierungsattributen
select avg all
where
select count distinct
!
!
from
select count
from
select sum
group by und having II
all notwendig
!
!
from
all
from
select avg
select
where
Andreas Heuer, Gunter Saake – Datenbanken I
9-42
Andreas Heuer, Gunter Saake – Datenbanken I
Aggregatfunktionen
9-44
group by und having
■
■ built-in-Funktionen: tupelübergreifend
Syntax
where
einer Relation
◆ sum: Summe der Werte einer Spalte
◆ avg: arithmetisches Mittel der Werte einer Spalte
◆ max bzw. min: größter bzw. kleinster Wert einer Spalte
select
from
group by
having
◆ count: Anzahl der Werte einer Spalte oder (Spezialfall count( )) Anzahl der Tupel
■ Nullwerte werden vor Anwendung aus Wertemenge eliminiert (außer bei count( ))
Andreas Heuer, Gunter Saake – Datenbanken I
9-41
Andreas Heuer, Gunter Saake – Datenbanken I
◆
■ Vor Argument (außer bei count( )) optional: distinct oder all (all Voreinstellung)
◆ bei count auch
◆ gültiger skalarer Ausdruck
Relationenschema und Attributmenge hinter Gruppierung
schachteln nach Attributen
, d.h. für gleiche -Werte werden
Resttupel in Relation gesammelt
der where-Klausel genügende Tupel also schachteln gemäß
◆
◆
◆ Attribut der durch from spezifizierten Relation
Semantik (virtuelle geschachtelte Relation):
■ Argumente einer Aggregatfunktion:
■
9-43
Gruppierung: Beispiele
■
!
as
Syntax
!
!
!
and sum(D)
having A
max(C)
sum(D)
9
A
1
4
12
2
3
9
1
Andreas Heuer, Gunter Saake – Datenbanken I
Schritt 3:
Andreas Heuer, Gunter Saake – Datenbanken I
from
select avg
Schritt 4:
D
4
5
4
5
7
N
C
3
4
3
4
6
sum(D)
3
3
2
3
A
4
5
4
5
7
3
4
3
4
6
2
9-45
and
select A, sum(D)
group by A, B
D
N
C
1
Schritt 2:
B
from
A
!
from und where
PANr
7754
9912
select
group by
having avg
Schritt 1:
2
2
D
4
5
4
5
7
!
from
!
select count
group by
having count
C
3
4
3
4
6
9-48
B
2
2
3
3
3
Andreas Heuer, Gunter Saake – Datenbanken I
Gruppierung: Beispiele II
A
1
1
2
3
3
select
from
any
Gruppierung: Schema
!
Beispiele
■
Bedeutung all Allquantor, any, some Existenzquantoren
9-46
where
■
select
from
where
Andreas Heuer, Gunter Saake – Datenbanken I
PANr
7754
4711
5588
9912
Anzahl
2
1
1
2
select
from
some
any
all
select count
from
group by
Quantoren und Mengenvergleiche
9-47
order by-Klausel
Selbst-Verbund
letzte Anfrage erst mit Selbst-Verbund zu lösen
■
Vergleich von Wertemengen
Menge von Tupeln
■
Syntax
aufsteigend (asc) oder absteigend (desc) sortieren
Andreas Heuer, Gunter Saake – Datenbanken I
9-52
Selbst-Verbund II
Zählen von Wertemengen
!
select distinct
from
where
and
from
select
where
all
■
from
asc
Quantoren und Mengenvergleiche II
select
where
and
9-50
Andreas Heuer, Gunter Saake – Datenbanken I
■
Vossen
3-89319-175-5
select
from
where
order by
Witt
Beispiel
B A 2.Autor
Vossen
Witt
Elmasri
B A 2.ISBN
3-89319-175-5
3-89319-175-5
0-8053-1753-8
B A 1.Autor
Vossen
Vossen
Vossen
3-89319-175-5
■
B A 1.ISBN
3-89319-175-5
3-89319-175-5
3-89319-175-5
and
order by
and
Liste
from
select
where
■
■
Anwendbarkeit eingeschränkt: Test auf Mengen-Gleichheit
in SQL so nicht umsetzbar:
Gib alle Bücher aus, an denen ‘Vossen’ und ‘Witt’ gemeinsam als Autoren beteiligt waren
Andreas Heuer, Gunter Saake – Datenbanken I
9-49
Andreas Heuer, Gunter Saake – Datenbanken I
9-51
Behandlung von Nullwerten
■
Änderungsoperationen
skalare Ausdrücke: Ergebnis null, sobald Nullwert in die Berechnung eingeht
In allen Aggregatfunktionen bis auf count( ) werden Nullwerte vor Anwendung der Funktion entfernt
■
Fast alle Vergleiche mit Nullwert ergeben Wahrheitswert unknown (statt
true oder false). Ausnahme: is null ergibt true, is not null ergibt false
■
Einfügen von Tupeln in Basisrelationen (oder Sichten) insert
■
Löschen von Tupeln aus Basisrelationen (oder Sichten) delete
■
Ändern von Tupeln in Basisrelationen (oder Sichten) update
■
■
Boolesche Ausdrücke basieren dann auf dreiwertiger Logik
Diese Operationen jeweils als
■
Eintupel-Operationen (etwa die Erfassung einer neuen Ausleihung)
■
Mehrtupel-Operationen (erhöhe das Gehalt aller Mitarbeiter um 4.5%)
SQL: vor allem Mehrtupel-Operationen
Änderungsoperationen auf Sichten: später
Andreas Heuer, Gunter Saake – Datenbanken I
9-54
order by-Klausel II
■
9-56
Behandlung von Nullwerten II
Sortierung wird auf das Ergebnis der jeweils vorangehenden SFW-Anfrage
angewendet, also FALSCH:
and
true
unknown
false
true
true
unknown
false
or
true
unknown
false
true
true
true
true
unknown
unknown
unknown
false
false
false
false
false
select
from
where
order by
Andreas Heuer, Gunter Saake – Datenbanken I
(falsch!)
not
true
unknown
false
Andreas Heuer, Gunter Saake – Datenbanken I
9-53
Andreas Heuer, Gunter Saake – Datenbanken I
unknown
true
unknown
unknown
false
true
unknown
false
false
unknown
true
9-55
update II
Angestellte
!
!
values
!
!
insert into
values
insert into
where
!
update
insert into
values
Gehalt
4000
4500
7200
5400
Name
Meyer
Schulz
Bond
Schulz
set
set
update
insert
insert into
!
!
where
delete from
where
delete from
■
9-60
delete
Syntax
update
set
where
Andreas Heuer, Gunter Saake – Datenbanken I
update
■
from
select
9-58
Andreas Heuer, Gunter Saake – Datenbanken I
insert into
Beispiele
Standardfall ist Löschen mehrerer Tupel:
Angestellte
where
delete from
Gehalt
3000
3500
7200
4400
Löschen der gesamten Relation:
set
9-57
Andreas Heuer, Gunter Saake – Datenbanken I
delete from
update
where
Name
Meyer
Schulz
Bond
Schulz
Andreas Heuer, Gunter Saake – Datenbanken I
9-59
SEQUEL2
■
bot mehr als SQL-89
intersect und minus neben union
■
Level 2: wie hier beschrieben
■
Level 2
IEF (Integrity Enhancement Feature)
!
where
from
◆
oder exists
Beispiele:
from
select
◆
keine Nullwerte
keine Selektionsbedingungen mit
keine union-Operation
◆
Mengenvergleiche mit set und zwei SFW-Blöcken
select
where
Level 1
◆
und sogar mehr als SQL-92
◆
■
■
◆
■
SQL-89
◆
◆
!
where
from
select
Andreas Heuer, Gunter Saake – Datenbanken I
9-62
check-Klausel: where-Klausel als Integritätsbedingung
Definition von Primärschlüsseln und Fremdschlüsseln
Andreas Heuer, Gunter Saake – Datenbanken I
9-64
SQL-Versionen
■
SEQUEL2 II
Geschichte
◆
möglich
select
from
where set
select
from
from
in
IEF)
!
◆
select
where
◆
auch
◆
Statt
◆
■
◆
SEQUEL (1974, IBM Research Labs San Jose)
SEQUEL2 (1976, IBM Research Labs San Jose)
SQL (1982, IBM)
ANSI-SQL (SQL-86; 1986)
ISO-SQL (SQL-89; 1989; drei Sprachen Level 1, Level 2,
(ANSI / ISO) SQL2 (SQL-92)
(ANSI / ISO) SQL3 (geplant)
◆
!
!
■
SQL
◆
◆
■
set ermittelt alle Prüfernr pro Matrnr
DDL, (SSL,) IQL, DML
Sichtdefinition, Transaktionsdefinition, Rechtevergabe, Integrit ätssicherung
Andreas Heuer, Gunter Saake – Datenbanken I
9-61
Andreas Heuer, Gunter Saake – Datenbanken I
9-63
SQL-92 II
■
Sprache fast vollständig orthogonal (etwa union, SFW hinter from
■
dreiwertige Logik
■
set transaction: verschiedene Isolationsstufen (siehe Datenbanken II)
■
Embedded SQL und Dynamic SQL sind Teil der Norm (siehe nächstes
Kapitel)
■
Data Dictionary ist Teil der Norm
Andreas Heuer, Gunter Saake – Datenbanken I
9-66
SQL-92
SQL-92 III
Feature in SQL-92
Full
allgemeine Integritätsbedingungen (mehrere Tabellen)
Intermediate
■
Entry
Änderung des Datenbankschemas: alter table und drop table
■
)
Domänenkonzept (create domain, alter domain)
■
neue Datentypen (wie
■
Datum, Intervalltypen, domain
-Operationen
join
except, intersect
alter, drop table
set transaction
Dynamic SQL
union orthogonal
andere Orthogonalitätsverbesserungen
corresponding bei Mengenoperationen
dreiwertige Logik
allgemeine Integritätsbedingungen
check mit Bezug zu anderen Tabellen
alter domain
Tabellenkonstruktoren
■
-Operationen erweitert
auch intersect und except
9-65
Andreas Heuer, Gunter Saake – Datenbanken I
Andreas Heuer, Gunter Saake – Datenbanken I
■
join, cross join, natural join, outer join als eigene Operatoren
■
Namen für abgeleitete Spalten
■
9-67