5.3.4 SELECT - Friedrich-Schiller

5.3.4 SELECT
ƒ Fundamentales Konstrukt für den DB-Zugriff mit SQL zur Datenermittlung ist
die SELECT-FROM-WHERE-Klausel (SFW-Klausel)
- SELECT:
• Festlegung der Ergebnisstruktur (Projektionsliste) einer Anfrage
• Welche Tabellenspalten soll die Anfrage liefern?
- FROM:
• Festlegung der Datenherkunft für die Anfrage
• Welche Relationen werden zur Beantwortung der Anfrage benötigt?
- WHERE:
• Festlegung der Selektionsbedingung
• Welche Daten (Tupel) sollen genau ermittelt werden?
• Wie erfolgt ggf. eine Datenverknüpfung über mehrere Relationen
(impliziter Join, expliziter JOIN-Operator seit SQL-92)?
ƒ Ergänzung der SFW-Klausel durch weitere Klauseln
- GROUP BY (Gruppierung)
- HAVING (Bedingung für gruppierte Daten)
- ORDER BY (Sortierung der Ergebnisdaten)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 180
5.3.4 SELECT
ƒ Syntax:
- Quelle: K. Neumann "Datenbanktechnik für Anwender", 1996
- Vereinfachte Darstellung
ALL
variable
SELECT
ziel-liste
FROM
relation
,
DISTINCT
WHERE
where-Bedingung
GROUP BY
attr-ref
,
HAVING
have-Bedingung
ORDER BY
attr-ref
ASC
DESC
,
nur die Ergebnisdarstellung betreffend
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 181
5.3.4 SELECT
ƒ Grundlage für nachfolgende Beispiele:
- Modellierung von Angestellten, Projekten und Mitarbeit (Folie 139)
CREATE TABLE Angest (
AngNr INTEGER PRIMARY KEY,
Name VARCHAR(30),
Wohnort VARCHAR(40),
Beruf VARCHAR(40),
AbtNr INTEGER,
Jahresurlaub INTEGER,
Resturlaub INTEGER)
CREATE TABLE Projekt (
ProNr INTEGER PRIMARY KEY ,
PName VARCHAR(30),
Beschreibung VARCHAR(50),
PLeiter INTEGER)
CREATE TABLE Mitarbeit (
ProNr INTEGER ,
Prozent
AngNr INTEGER,
Prozent INTEGER,
PRIMARY KEY (ProNr, AngNr),
FOREIGN KEY (ProNr) REFERENCES Projekt(ProNr),
FOREIGN KEY (AngNr) REFERENCES Angest(AngNr))
Datenbanksysteme
Friedrich-Schiller-Universität Jena
ANGEST
n
Mitarbeit
m
PROJEKT
Seite 182
5.3.4 SELECT
ƒ Beispiel:
- Gesucht sind alle Angestellten jeweils mit Name und Wohnort
• die in einer Abteilung mit AbtNr>3 arbeiten
• Ausgabe soll nach Name absteigend sortiert erfolgen
• Duplikate sollen nicht eliminiert werden
- SQL-Anfrage:
SELECT ALL
FROM
WHERE
ORDER BY
Name, Wohnort
Angest
AbtNr > 3
Name DESC
- Ergebnis:
• Schlüsselwort ALL erzeugt Multimenge, welche Duplikate erlaubt
• Streng genommen nicht mehr Teil des "rein relationalen" Modells
• Abweichungen in SQL und Produkten (Performance, Funktionalität)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 183
5.3.4 SELECT – Unirelational
ƒ A1: Gib alle Angestellten aus, die von Beruf 'Hundezüchter' sind (Selektion)
- Variante 1
SELECT ALL AngNr, Name, Wohnort, Beruf, AbtNr, Jahresurlaub, Resturlaub
FROM Angest
WHERE Beruf = 'Hundezüchter'
- Variante 2 (ALL ist Default, kann also weggelassen werden)
SELECT AngNr, Name, Wohnort, Beruf, AbtNr, Jahresurlaub, Resturlaub
FROM Angest
WHERE Beruf = 'Hundezüchter'
- Variante 3 (enthält die Zielliste alle Attribute der Eingangsrelation, darf
statt dessen ∗ geschrieben werden
SELECT ∗
FROM Angest
WHERE Beruf = 'Hundezüchter'
- DISTINCT in der SELECT-Klausel sorgt für Duplikatelimination
• Verwendung im Beispiel A1 möglich, aber unnötig
• DBVS sollte diese Angabe ignorieren, wenn in der Zielliste der
Primärschlüssel enthalten ist
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 184
5.3.4 SELECT – Unirelational
ƒ A2: Gib für alle Angestellten Beruf und Wohnort aus und zwar in dieser
Reihenfolge (Projektion, "mehr" als Algebra)
- Variante 1 (WHERE-Klausel entfällt, da alle Angestellten gefragt sind,
alternativ ist "WHERE true" möglich)
SELECT Beruf, Wohnort
FROM Angest
- Attributreihenfolge in der Ergebnisrelation
• Bedeutungslos aus "streng relationaler Sicht"
• Wesentlich für die Ausgabe/Darstellung zum Benutzer
- Verwendung von DISTINCT diesmal relevant? Æ JA!
SELECT DISTINCT Beruf, Wohnort
FROM Angest
• DBVS müsste Duplikate eliminieren, weil der Primärschlüssel (AngNr)
im Ergebnis fehlt
• Duplikatprüfung in der Regel auf sortierter Ergebnisrelation
• Ausgabe dann vermutlich auch sortiert, als Anwender nicht darauf
verlassen sondern bei Bedarf explizit anfordern (ORDER BY)!
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 185
5.3.4 SELECT – Unirelational
ƒ A3: Gib die Namen aller Angestellten mit AbtNr zwischen 2 und 6 aus
(Projektion und Selektion)
- Variante 1
SELECT Name
FROM Angest
WHERE AbtNr >= 2 AND AbtNr <= 6
- Variante 2 ...
WHERE AbtNr BETWEEN 2 AND 6
- Variante 3 ...
WHERE AbtNr IN (2,3,4,5,6)
- Bemerkungen zu Variante 3
• Nach IN folgt Multimenge atomarer Werte (die natürlich auch
einelementig oder leer sein darf)
• Typischerweise Ersatz für die OR-Verknüpfung:
... (AbtNr = 16) OR (AbtNr = 95) OR ... Æ ... AbtNr IN (16,95,...)
• Nach IN darf auch ein SFW-Ausdruck stehen, der eine Tupelmenge
liefert Æ Details dazu später (Subquery, Subselect)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 186
5.3.4 SELECT – Unirelational
ƒ A4: Gib die Attribute Name und Wohnort der Angest-Relation aufsteigend
sortiert nach den Werten des Attributs Name aus
- Variante 1 (die ASC-Angabe kann weggelassen werden, da Default)
SELECT Name, Wohnort
FROM Angest
ORDER BY Name ASC
- In welcher Reihenfolge erscheinen die Werte für Wohnort in der
Ausgabe? Æ unsortiert bzw. "zufällige" Reihenfolge
- Beispielergebnis:
Name
Abeln
Abeln
Dittrich
Dittrich
Dittrich
Kühne
Kühne
Kühne
Datenbanksysteme
Wohnort
Karlsruhe
Mannheim
Jena
Augsburg
Zittau
Zwickau
Leipzig
Leipzig
Friedrich-Schiller-Universität Jena
Seite 187
5.3.4 SELECT – Unirelational
ƒ A5: Erzeuge eine Ausgabe wie bei A4, jedoch sollen innerhalb einer
Namensangabe in der Ausgabe die Wohnorte absteigend sortiert sein
- Variante 1
SELECT Name, Wohnort
FROM Angest
ORDER BY Name, Wohnort DESC
- Anders formuliert: Name sei 1. Sortierkriterium (1. Priorität), Wohnort sei
2. Sortierkriterium (2. Priorität)
- Reihenfolge der Sortierkriterien in ORDER BY ist wesentlich!
- Beispielergebnis:
Name
Abeln
Abeln
Dittrich
Dittrich
Dittrich
Kühne
Kühne
Kühne
Datenbanksysteme
Wohnort
Mannheim
Karlsruhe
Zittau
Jena
Augsburg
Zwickau
Leipzig
Leipzig
Friedrich-Schiller-Universität Jena
Seite 188
5.3.4 SELECT – Unirelational
ƒ A6: Gib die Namen aller Angestellten sowie die Summe von Jahresurlaub
und Resturlaub aus, absteigend sortiert nach dieser Summe (Existenz der
Attribute in Angest vorausgesetzt)
- Variante 1
SELECT Name, Jahresurlaub + Resturlaub
FROM Angest
ORDER BY (Jahresurlaub + Resturlaub) DESC
- ORDER BY-Klausel erlaubt für "att_ref" die Verwendung einer
• Attributnummer statt eines Attributnamens (Position des Attributs in
der Zielliste)
• Sinnvoll beim Sortieren komplexer arithmetischer Ausdrücke, die auch
in der Zielliste vorkommen (erspart Redundanz!)
- Variante 2
SELECT Name, Jahresurlaub + Resturlaub
FROM Angest
ORDER BY 2 DESC
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 189
5.3.4 SELECT – Unirelational
ƒ A7: Verwendung von eingebauten Funktionen ("built-in function") in SQL,
vordefiniert sind u.a. COUNT, SUM, AVG, MIN, MAX, daneben gibt es
sogenannte UDF (user-defined functions)
- Wieviele Mitarbeiter arbeiten in unserer Firma?
SELECT COUNT(∗)
FROM Angest
• Schöner wäre: COUNT(SELECT * FROM Angest), COUNT(Angest)
- Wieviele Mitarbeiter wohnen in Erfurt?
SELECT COUNT(∗)
FROM Angest
WHERE Wohnort = 'Erfurt'
- Wie lautet die maximale Abteilungsnummer?
SELECT MAX(AbtNr)
FROM Angest
- Wieviel Tage Resturlaub haben alle Mitarbeiter insgesamt noch?
SELECT SUM(Resturlaub)
FROM Angest
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 190
5.3.4 SELECT – Unirelational
ƒ A8: Verwendung von eingebauten Funktionen im Zusammenhang mit der
GROUP BY Klausel
- Gib eine Liste der Wohnorte zusammen mit dem durchschnittlichen
Resturlaub der Mitarbeiter aus, die jeweils an diesem Ort wohnen
SELECT Wohnort, AVG(Resturlaub) AS MittelResturlaub
FROM Angest
GROUP BY Wohnort
Æ Durchschnittsbildung (AVG) wird nicht auf die gesamte AngestRelation einmal angewandt, sondern jeweils für eine Gruppierung
(Mitarbeiter mit gleichem Wohnort)
- Denkbarer Lösungsansatz des DBVS: Datengruppierung wird intern als
Zwischenergebnis erzeugt (eventuell auch materialisiert!)
Wohnort
Aachen
Aachen
Berlin
Erfurt
Erfurt
Erfurt
Datenbanksysteme
Resturlaub
14
10
8
6
12
9
1. Gruppe (AVG = 12)
2. Gruppe (AVG = 8)
3. Gruppe (AVG = 9)
Friedrich-Schiller-Universität Jena
Seite 191
5.3.4 SELECT – Unirelational
ƒ
A9: Formulierung von Gruppierungs-Nebenbedingungen / Auswahl
bestimmter Gruppen
- Gib eine Liste der Wohnorte zusammen mit dem durchschnittlichen
und gesamten Resturlaub der Mitarbeiter aus, die jeweils an diesem Ort
wohnen, nicht von Beruf Ingenieur sind und dies nur für Orte mit
mindestens 2 Mitarbeitern
SELECT Wohnort, AVG(Resturlaub), SUM(Resturlaub)
FROM Angest
WHERE Beruf <> 'Ingenieur'
GROUP BY Wohnort
HAVING COUNT(∗)>=2
-
Ausführungsreihenfolge (siehe Syntax Folie 180)
1. SELECT ... FROM ... WHERE ... (ohne "built-in functions")
2. GROUP BY ...
3. "built-in functions" (SUM, AVG, MAX, ...)
4. HAVING
5. ORDER BY
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 192
5.3.4 SELECT – Unirelational
ƒ A10: Gib mir jenen Angestellten, der den geringsten Resturlaub aufweist
(soll für eine Beförderung vorgeschlagen werden)
- Variante 1
SELECT ∗
FROM Angest
ORDER BY Resturlaub
• Liefert gesuchten Angestellten als erstes Tupel
• Zusätzlich aber auch alle anderen Angestellten!
- Variante 2
/
SELECT ∗
FROM Angest
WHERE Resturlaub = (SELECT MIN(Resturlaub) FROM Angest)
• Ausdruck in der WHERE-Bedingung (Subquery) liefert einen Wert
• Bezugnahme in der Subquery "zufällig" auf gleiche Relation wie in der
Anfrage selbst (Angest)
• Vertiefung von Subquery und multirelationale Anfragen folgt noch
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 193
5.3.4 SELECT – Unirelational
ƒ A11: Formulierung von unscharfen Suchanfragen (pattern matching),
beispielweise: "Gib mir die Namen der Angestellten, die vom Beruf
Tierzüchter sind"
- Variante 1
SELECT Name
FROM Angest
WHERE Beruf = 'Züchter'
• Anfrage liefert keinen Treffer, da 'Züchter' <> 'Hundezüchter'
- Variante 2
SELECT Name
FROM Angest
WHERE Beruf = 'Hundezüchter'
• Anfrage liefert 2 Treffer, aber andere Tierzüchter fehlen (z.B. Katzen)
- Variante 3
SELECT Name
FROM Angest
WHERE Beruf LIKE '%züchter'
• Anfrage sucht in Spalte "Beruf" nach allen Attributwerten, die auf
'züchter' enden (Abweichung bei MS Access: ∗ statt %, ? statt _)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 194
5.3.4 SELECT – Unirelational
ƒ Allgemeines zum Vergleichoperator LIKE
- Anwendbar auf CHARACTER-Attribute fester oder variabler Länge
- Zwei Markierungszeichen ("wild cards"):
• An der durch % (Prozentzeichen) markierten Stelle können beliebig
viele (auch keine!) zu ignorierende Zeichen stehen
• An der durch _ (Unterstrich) markierten Stelle wird genau ein Zeichen
ignoriert
- % und _ dürfen in einem Suchausdruck beliebiger gemischt vorkommen
• Bsp: Wohnort LIKE 'H%b_rg' liefert Treffer bei Hamburg, Hammelburg,
Heidelberg, Humburg, Hbirg - nicht aber bei Hbrg oder Humbug
• Bsp: Name LIKE 'M_ _er' liefert Treffer bei Meier, Maier, Meyer,
Mayer, aber auch bei Mauer etc.
- LIKE darf negiert werden (NOT LIKE)
• Bsp: "Gib mir die Namen aller Angestellten, die vom Beruf nicht
Tierzüchter sind"
SELECT Name FROM Angest
WHERE Beruf NOT LIKE '%züchter'
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 195
5.3.4 SELECT – Unirelational
- Phonetische Suche (ähnliche Aussprache) ist im SQL-Standard nicht
abgedeckt, aber in Produkten teilweise vorhanden
• Bsp: DB2 Text Extender
- LIKE kennt keine Wortgrenzen
• Textsemantik: Wo hört ein Wort auf, wo ein Satz etc.
• Reines "pattern matching", das den gesamten Character-Attributwert
als eine unstrukturierte Zeichenkette auffasst und durchsucht
• Anfragen der Art "Finde alle Angestellten, wo im Attribut
Stellenbeschreibung das Wort Datenbank auftritt" nicht so einfach zu
stellen
- Realisierung von LIKE durch das DBVS
• Erfordert häufig sequentielle(!) Suche im Datenbestand (tupelweise)
• Lediglich bei Endmarkierung (z.B. 'Datenbank%' oder 'Datenban_')
kann eventuell vorhandener Baum-Index benutzt werden
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 196
5.3.4 SELECT – Multirelational
ƒ Syntax:
SELECT [ALL | DISTINCT] Attributliste
FROM Rel1, Rel2, ..., Reln
[WHERE Reli.Attributname θ1 Relj.Attributname
AND Relk.Attributname θ2 Rell.Attributname
...
AND Rely.Attributname θx Relz.Attributname]
ƒ Bemerkungen:
- n≥2 für Reln
- θx є {=, <>, >, >=, <, <=}
- Der häufigste Vergleichsoperator θ ist "=" (Equi-Join)
- Verwendung von Relationsnamen/Tupelvariablen (Reln) notwendig
• wenn sonst keine Eindeutigkeit in der Bezeichnung gegeben ist
(welches Attribut in welcher Relation ist gemeint?)
• bei Modellierung rekursiver Anfragen (1:n-Beziehungen) durch Join
einer Relation mit sich selbst
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 197
5.3.4 SELECT – Multirelational
ƒ A12: Bilde das Kreuzprodukt der Relationen Angest und Projekt
- Variante 1
SELECT ∗
FROM Angest, Projekt
- Ausführung
• Erzeugung eines Anfrageergebnisses, das alle Spalten der beteiligten
Relationen umfasst
• Verknüpfung: jedes Tupel der einen Relation mit jedem Tupel der
anderen Relation, im Beispiel: 8 (Angest) * 3 (Projekt) = 24 Tupel
• Kreuzprodukt ist Spezialfall das allgemeinen JOIN mit VerknüpfungsBedingung und wird in der Realität selten gebraucht
- Beispielergebnis
AngNr
Name
Wohnort
Beruf
AbtNr
Jahresurlaub
Resturlaub
ProNr
PName
Beschreibung
PLeiter
112
112
112
205
...
Müller
Müller
Müller
Winter
...
Erfurt
Erfurt
Erfurt
Zittau
...
Ingenieur
Ingenieur
Ingenieur
Programmierer
...
3
3
3
3
...
29
29
29
28
...
15
15
15
18
...
27
16
84
27
...
PKW2000
Wankel99
TrabbiGo
PKW2000
...
Der PKW für..
Der Wankel...
Der Trabbi...
Der PKW für..
...
205
117
117
205
...
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 198
5.3.4 SELECT – Multirelational
ƒ A13: Gib für jeden Mitarbeiter (Name) aus, in welchen Projekten (ProNr) er
mit welchem Prozentsatz mitarbeitet Æ Join zwischen Angest und Mitarbeit
- Variante 1
SELECT Name, ProNr, Prozent
FROM Angest, Mitarbeit
WHERE Angest.AngNr = Mitarbeit.AngNr
- Variante 2 (explizite Relationenbezeichnung für Eindeutigkeit)
SELECT Angest.Name, Mitarbeit.ProNr, Mitarbeit.Prozent
FROM Angest, Mitarbeit
WHERE Angest.AngNr = Mitarbeit.AngNr
- Variante 3 (Verwendung von Tupelvariablen a und m)
SELECT a.Name, m.ProNr, m.Prozent
FROM Angest a, Mitarbeit m
WHERE a.AngNr = m.AngNr
- Variante 4 (explizite JOIN-Angabe)
SELECT a.Name, m.ProNr, m.Prozent
FROM Angest a INNER JOIN Mitarbeit m ON a.AngNr = m.AngNr
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 199
5.3.4 SELECT – Multirelational
ƒ A14: Anfrage wie bei A13, nur soll statt ProNr der Projektname (PName)
eines Projektes ausgegeben werden Æ Join über alle 3 Tabellen
- Variante 1
SELECT a.Name, p.PName, m.Prozent
FROM Angest a, Mitarbeit m, Projekt p
WHERE a.AngNr = m.AngNr
AND p.ProNr = m.ProNr
- Variante 2
SELECT a.Name, p.PName, m.Prozent
FROM (Angest a INNER JOIN Mitarbeit m ON a.AngNr = m.AngNr)
INNER JOIN Projekt p ON p.ProNr = m.ProNr
- Beispielergebnis
Name
Müller
Rüllich
Müller
Ehrmann
...
Datenbanksysteme
PName
PKW2000
Wankel99
Wankel99
Wankel99
...
Prozent
70
100
30
100
...
Friedrich-Schiller-Universität Jena
Seite 200
5.3.4 SELECT – Multirelational
ƒ A15: Anfrage wie bei A14, aber es sollen nur die in Erfurt wohnenden
Mitarbeiter und nur die Projekte, in deren Beschreibung nicht 'Wankel'
auftritt, berücksichtigt werden
- Variante 1
SELECT a.Name, p.PName, m.Prozent
FROM Angest a, Mitarbeit m, Projekt p
WHERE a.AngNr = m.AngNr
AND p.ProNr = m.ProNr
AND a.Wohnort = 'Erfurt'
AND p.Beschreibung NOT LIKE '%Wankel%'
- Variante 2
SELECT a.Name, p.PName, m.Prozent
FROM (Angest a INNER JOIN Mitarbeit m ON a.AngNr = m.AngNr)
INNER JOIN Projekt p ON p.ProNr = m.ProNr
WHERE a.Wohnort = 'Erfurt'
AND p.Beschreibung NOT LIKE '%Wankel%'
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 201
5.3.4 SELECT – Multirelational
ƒ Bisher betrachtet: Join, wie auch im Zusammenhang mit Relationenalgebra
eingeführt, der sogenannte INNER JOIN
- Nachteil: Tupel in einer Relation, für die es kein "Gegenstück" in einer
anderen Relation gibt (für die die Join-Bedingung nie erfüllt ist), sind nicht
Bestandteil des Join-Ergebnis ("gehen also verloren")
- Beispiele:
• Angestellte, die in keinem Projekt mitarbeiten
• Projekte ohne Mitarbeiter
ƒ Lösung seit SQL-92:
- OUTER JOIN als "Ergänzung" zum INNER JOIN
- Varianten:
• LEFT OUTER JOIN: übernimmt unverknüpfte Tupel aus der linken
Relation ins Join-Ergebnis
• RIGHT OUTER JOIN: übernimmt unverknüpfte Tupel aus der rechten
Relation ins Join-Ergebnis
• FULL OUTER JOIN: übernimmt unverknüpfte Tupel sowohl aus der
rechten als auch linken Relation ins Join-Ergebnis
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 202
5.3.4 SELECT – Multirelational
ƒ Syntax seit SQL-92:
SELECT [ALL | DISTINCT] Attributliste
FROM Rel1 [LEFT | RIGHT | FULL] OUTER JOIN Rel2 ON Joinbedingung
[WHERE Bedingung]
ƒ Beispiel: Gib für jeden Mitarbeiter (AngNr, Name) den Anteil (Prozent) am
zugeordneten Projekt (ProNr) aus, auch für unzugeordnete Mitarbeiter
- Variante 1
SELECT a.AngNr, a.Name, m.ProNr, m.Prozent
FROM Angest a LEFT OUTER JOIN Mitarbeit m ON a.AngNr = m.AngNr
- Beispielergebnis (Mitarbeiter mit AngNr=205 ist ohne Projektzuordnung)
AngNr
112
112
117
...
205
Datenbanksysteme
Name
Müller
Müller
Rüllich
...
Winter
ProNr
27
16
16
...
NULL
Prozent
70
30
100
...
NULL
Friedrich-Schiller-Universität Jena
Dieses Tupel wäre
beim INNER JOIN
nicht im Ergebnis,
undefinierten Wert mit
NULL belegt
Seite 203
5.3.4 SELECT – Multirelational
ƒ Neben INNER/OUTER JOIN bietet SQL-92 noch weitere syntaktische und
semantische JOIN-Erweiterungen, hier nur kurz erwähnt
ƒ Vorteile der expliziten Join-Notation mit dem JOIN-Operator
- erspart Schreibarbeit / erhöht Lesbarkeit
- reduziert Fehlermöglichkeiten
- ist näher an Relationenalgebra-Schreibweise
- eröffnet neue semantische Möglichkeiten (z.B. OUTER JOIN)
ƒ CROSS JOIN
- Explizite Notation für kartesisches Produkt (Kreuzprodukt)
- Verknüpfung jedes Tupel der linken Relation mit jedem Tupel der rechten
Relation (siehe Folie 197)
- Bsp:
SELECT ∗
FROM Angest CROSS JOIN Projekt
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 204
5.3.4 SELECT – Multirelational
ƒ NATURAL JOIN
- Explizite Notation für den natürlichen Verbund (Equi-Join über
namensgleiche Attribute in linker und rechter Relation)
- Bsp:
SELECT Name, ProNr, Prozent
FROM Angest NATURAL JOIN Mitarbeit
≡
SELECT Name, ProNr, Prozent
FROM Angest, Mitarbeit
WHERE Angest.AngNr = Mitarbeit.AngNr
≡
SELECT Name, ProNr, Prozent
FROM Angest JOIN Mitarbeit ON Angest.AngNr = Mitarbeit.AngNr
≡
SELECT Name, ProNr, Prozent
FROM Angest JOIN Mitarbeit USING AngNr
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 205
5.3.4 SELECT – Multirelational
ƒ UNION JOIN
- Bildung der Vereinigungsrelation mit allen Attributen aus beiden
beteiltigen Relationen
- Attribute der jeweils anderen Relation werden mit NULL aufgefüllt
- Bsp:
SELECT ∗
FROM Angest UNION JOIN Projekt
- Beispielergebnis:
AngNr
Name
Wohnort
Beruf
AbtNr
Jahresurlaub
Resturlaub
ProNr
Pname
Beschreibung
PLeiter
112
117
120
121
122
123
198
205
NULL
NULL
NULL
Müller
Rüllich
Ehrmann
Müller
Müller
Ehrmann
Schuhmann
Winter
NULL
NULL
NULL
Erfurt
Weimar
Erfurt
Gera
Zittau
Aachen
Jena
Zittau
NULL
NULL
NULL
Ingenieur
Hundezüchter
Ingenieur
Pferdezüchter
Hundezüchter
Programmierer
Katzenzüchter
Programmierer
NULL
NULL
NULL
3
5
4
4
5
6
4
3
NULL
NULL
NULL
29
29
30
30
25
25
28
28
NULL
NULL
NULL
15
20
17
6
24
25
19
18
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
16
27
84
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Wankel99
PKW2000
TrabbiGo
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Der Wankel...
Der PKW für..
Der Trabbi...
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
117
205
117
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 206
5.3.4 SELECT – Subquery/Subselect
ƒ Definition: Ein Subquery/Subselect
- ist ein SFW-Ausdruck innerhalb eines anderen SFW-Ausdrucks
- im Bereich der FROM- oder WHERE-Klausel
- wobei eine beliebige Schachtelung zulässig ist.
- Subquery-Typen
• Korreliert: der innere SFW-Ausdruck nimmt Bezug auf den äußeren
• Unkorreliert: Bezug zwischen innerem/äußerem SFW-Ausdruck fehlt
ƒ Syntax für die WHERE-Klausel:
SELECT [ALL | DISTINCT] Attributliste
FROM Rel1,..., Reln
WHERE {[NOT] EXISTS | Reli.Attribut IN | Reli.Attribut θ [ANY | ALL]}
(SELECT-FROM-WHERE-Ausdruck)
Subquery
ƒ Bemerkungen:
- n≥1 für Reln
- θ є {=, <>, >, >=, <, <=}
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 207
5.3.4 SELECT – Subquery/Subselect
ƒ Beispiel: Gib AngNr und Namen aller Angestellten aus, die in (irgend)einem
Projekt zu 100% mitarbeiten
ƒ Variante 1 (JOIN):
SELECT a.AngNr, a.Name
FROM Angest a, Mitarbeit m
WHERE a.AngNr = m.AngNr AND m.Prozent = 100
- Frage: Sind im Anfrageergebnis Duplikate möglich? Æ Mitarbeit.Prozent!
ƒ Variante 2 (EXISTS):
SELECT a.AngNr, a.Name
FROM Angest a
WHERE EXISTS (SELECT ∗ FROM Mitarbeit m
WHERE a.AngNr = m.AngNr AND m.Prozent = 100)
- Korrelierte Subquery wegen Bezug auf Tupelvariable "Angest a"
- Nested-Loop-Modell: pro äußeres Tupel ein kompletter Durchlauf der
inneren Relation ÆO(n2), wird DBVS-intern eventuell effizienter realisiert
- EXISTS prüft lediglich, ob das Subquery-Ergebnis leer ist oder nicht
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 208
5.3.4 SELECT – Subquery/Subselect
ƒ Variante 3 (ANY):
SELECT a.AngNr, a.Name
FROM Angest a
WHERE a.AngNr = ANY (SELECT AngNr FROM Mitarbeit
WHERE Prozent = 100)
- Eigenschaften der inneren Subquery
• Unkorreliert, ermittelt die Nummern (AngNr) all jener Angestellten, die
in irgendeinem Projekt zu 100% mitarbeiten
• Ausführung nur einmal notwendig, merken als Zwischenergebnis
• Test für jeden AngNr-Wert der "äußeren" Relation, ob er im
Zwischenergebnis enthalten ist Æ O(n)
- Eigenschaften des Schlüsselworts vor der Subquery:
• ANY: prüft, ob irgendein Wert der Subquery (Zwischenergebnis) mit
dem "äußeren" (a.AngNr) übereinstimmt
• ALL: prüft, ob alle Ergebniswerte der Subquery mit dem dem
"äußeren" gegebenen Wert übereinstimmen
• Ohne ANY/ALL: Subquery muss genau einen Wert zurückliefern
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 209
5.3.4 SELECT – Subquery/Subselect
ƒ Variante 4 (IN):
SELECT a.AngNr, a.Name
FROM Angest a
WHERE a.AngNr IN (SELECT AngNr
FROM Mitarbeit
WHERE Prozent = 100)
- Eigenschaften der Subquery analog zu Variante 3 mit ANY
• Unkorreliert, da kein Bezug nach außen
• Ausführung nur einmalig, Prüfungen auf Zwischenergebnis Æ O(n)
ƒ Hinweis:
- Obige Beispiele erwecken den Eindruck, jede Anfrage ließe sich immer
auf diese vier äquivalenten Arten in SQL formulieren
- Diese Allgemeingültigkeit liegt nicht vor!
- Gibt es mehrere Möglichkeiten zur Formulierung, dann ist die zu wählen,
welche am "natürlichsten" erscheint
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 210
5.3.5 CREATE / DROP VIEW
ƒ Sichten/Views als Sprachmittel zur Realisierung der externen Ebene gemäß
ANSI/SPARC-Architektur
- "Entkopplung" der Benutzer auf der externen Ebene von den tiefer
liegenden Ebenen (Æ Datenunabhängigkeit)
- Benutzer brauchen gesamtes, oftmals sehr komplexes Datenbankschema
(konzeptuelle Ebene) nicht im Detail zu kennen
- Zugeschnittene Sichten auf individuelle Benutzergruppen und die
entsprechenden Aufgaben
- Änderungen der konzeptuellen Ebene "schlagen" nicht unbedingt mehr
unmittelbar zum Benutzer hin durch
• Lediglich Anpassung der Sichtdefinition durch DB-Administrator
bzw. den Anwendungsadministrator
• Benutzer muss dies nicht unbedingt mitbekommen bei seiner Arbeit
- Möglichkeit zur zielgerichteten Vergabe von Zugriffsrechten für den
Benutzer durch den Administrator (Datensicherheitaspekt)
• Bsp: Ein Angestellter soll stets nur seinen eigenen Datensatz in der
Angest-Relation sehen dürfen und nicht die Datensätze anderer
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 211
5.3.5 CREATE / DROP VIEW
ƒ Definition:
- Sichten/Views sind "virtuelle Relationen" basierend auf
• Basisrelationen (erzeugt mit CREATE TABLE) oder
• zuvor erzeugten Sichten/Views, d.h. beliebig schachtelbar
- Sichten werden durch SQL-Queries (SFW-Konstrukt) "on top" definiert
- Sichten unterscheiden sich nicht (wesentlich) von Basisrelationen für
den Benutzer
• SELECT-Anfragen gegen Sichten sind beliebig erlaubt
• Einschänkung allerdings bei INSERT, UPDATE und DELETE
ƒ Syntax:
- Anlegen der View:
CREATE VIEW Sichtname [(Attr1, ..., Attrn)]
AS Subselect
- Löschen der View:
DROP VIEW Sichtname
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 212
5.3.5 CREATE / DROP VIEW
ƒ Beispiel 1: Sicht auf Relation Angest, die die Spalte Beruf, Jahres-/Resturlaub
sowie die Tupel mit AbtNr>5 ausblendet
CREATE VIEW MeineSichtDerDinge AS
SELECT AngNr, Name, Wohnort, AbtNr
FROM Angest
WHERE AbtNr <= 5
- Attribute in der SELECT-Anweisung legen das Relationsschema fest
- Testausführung in MS Access ohne CREATE VIEW
ƒ Beispiel 2: Sicht auf Relation Angest, die (nur) die Nummern und Namen jener
Angestellten umfasst, die in (irgend) einem Projekt zu 100% mitarbeiten
CREATE VIEW HundertprozentigeMitarbeit AS
SELECT a.AngNr, a.Name
FROM Angest a INNER JOIN Mitarbeit m ON a.AngNr = m.AngNr
WHERE m.Prozent = 100
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 213
5.3.5 CREATE / DROP VIEW
ƒ Beispiel 3: Sicht auf virtuelle Relation HundertprozentigeMitarbeit, welche um
die Projektnummer ergänzt werden soll
CREATE VIEW HundertprozentigeMitarbeitProjektNr AS
SELECT h.AngNr, h.Name, m.ProNr
FROM HundertprozentigeMitarbeit h, Mitarbeit m
WHERE h.AngNr = m.AngNr
- Sichten dürfen auch Daten aus mehreren (Basis-)Relationen und/oder
anderen Sichten umfassen
- Beliebige Schachtelbarkeit erlaubt
ƒ Beispiel 4: Umbenennung von Attributen
CREATE VIEW MeineSichtDerDingeUmbenannt (Nr, Nachname, Ort, AbtNr) AS
SELECT AngNr, Name, Wohnort, AbtNr
FROM MeineSichtDerDinge
- Testausführung in MS Access:
SELECT AngNr AS Nr, Name AS Nachname, Wohnort AS Ort, AbtNr
FROM MeineSichtDerDinge
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 214
5.3.5 CREATE / DROP VIEW
ƒ Beispiel 5: Sicht auf Relation Mitarbeit, ergänzt um den Namen des jeweiligen
Angestellen und Projektes
CREATE VIEW MitarbeitErweitert AS
SELECT m.ProNr, m.AngNr, m.Prozent, a.Name, p.PName
FROM (Mitarbeit m INNER JOIN Angest a ON m.AngNr = a.AngNr)
INNER JOIN Projekt p ON m.ProNr = p.ProNr
- Realisierung virtueller Attribute zu Relationen
- Benutzer sieht MitarbeitErweitert als (virtuelle) Relation und kann normal
damit arbeiten, z.B. Änderung des Projektnamens
- Testausführung in MS Access
• Geänderter Projektname wird sowohl in der Basisrelation Mitarbeit
geändert als auch sofort an allen Stellen in MitarbeitErweitert
• Änderung nicht möglich bei Syntax mit impliziter JOIN-Notation:
CREATE VIEW MitarbeitErweitert AS
SELECT m.ProNr, m.AngNr, m.Prozent, a.Name, p.PName
FROM Mitarbeit m, Angest a, Projekt p
WHERE m.AngNr = a.AngNr AND m.ProNr = p.ProNr
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 215
5.3.5 CREATE / DROP VIEW
ƒ Änderung auf Sichten Æ View-Update-Problematik
ƒ Rückabbildung auf zugrundeliegende Basisrelationen nicht immer möglich
- SQL-92 schränkt "updatability" von Sichten stark ein:
• Kein DISTINCT, keine arithmetischen Ausdrücke/Aggregate
• Kein GROUP BY / HAVING, kein JOIN
- Produkte weichen diese Restriktionen teilweise stark auf
ƒ Beispiel: Sicht auf Relation Angest mit Wohnort und dem durchschnittlichen
Resturlaub gruppiert nach Wohnorten (siehe Folie 190)
CREATE VIEW ResturlaubStatistik AS
SELECT Wohnort, AVG(Resturlaub)
FROM Angest
GROUP BY Wohnort
- Jegliche Änderungen über die Sicht nicht erlaubt!
- Rückabbildung des Durchschnitts wäre unklar
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 216
5.3.5 CREATE / DROP VIEW
ƒ Weitere Problematik bei Sicht-Änderung:
- Rückabbildung wäre möglich, aber geändertes/neues verlässt den
Definitionsbereich der Sicht
- Beispiel:
• Grundlage sei die Sicht MeineSichtDerDinge (siehe Folie 212)
• Zulässige Einfüge-Operation (will man das wirklich?)
INSERT
INTO MeineSichtDerDinge (AngNr, Name, Wohnort, AbtNr)
VALUES (666, 'Teufel', 'Hölle', 9)
- Explizites Verbieten von Änderungen außerhalb der Sicht-Definition:
CREATE VIEW Sichtname [(Attr1, ..., Attrn)]
AS Subselect
WITH CHECK OPTION
INSERT
• Änderungen der Sicht, die den in ihr
nicht sichtbaren Teil der Datenbank
beeinflussen, werden erkannt und
zurückgewiesen
Datenbanksysteme
Friedrich-Schiller-Universität Jena
DB
Sicht
UPDATE
Seite 217