Relationale Datenbanksprachen

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