(1) Verbunde/Joins (2)

4. SQL
4-1
4. SQL
4-2
Inhalt
Beispiel-Datenbank
'
$
1. Verbunde
&
%
2. Mehr über Vergleiche, weitere Bedingungen
3. SELECT-Klausel, Duplikate
4. Nullwerte
Universität Halle, 2013
4. SQL
4-3
Universität Halle, 2013
4. SQL
4-4
Verbunde/Joins (2)
• Wenn man Daten aus mehreren Tabellen verknüpft,
spricht man von einem Verbund (eng. Join).
• Folgende Anfrage enthält einen Verbund der Tabellen STUDENTEN und BEWERTUNGEN:
B.ATYP, B.ANR, B.PUNKTE
STUDENTEN S, BEWERTUNGEN B
S.SID = B.SID
-- Verbund-Bedingung
S.VORNAME = ’Lisa’
S.NACHNAME = ’Weiss’
S. Brass, A. Herrmann: Datenbanken und WWW
SID
101
101
101
102
102
102
103
103
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
H
2
9
Z
1
10
H
1
5
Z
1
7
S. Brass, A. Herrmann: Datenbanken und WWW
Verbunde/Joins (1)
SELECT
FROM
WHERE
AND
AND
EMAIL
···
NULL
···
···
AUFGABEN
ATYP ANR THEMA MAXPT
H
1 ER
10
H
2 SQL
10
Z
1 SQL
14
5. Sichten
S. Brass, A. Herrmann: Datenbanken und WWW
SID
101
102
103
104
STUDENTEN
VORNAME NACHNAME
Lisa
Weiss
Michael Grau
Daniel Sommer
Iris
Winter
Universität Halle, 2013
• Die obige Anfrage deklariert zwei Tupelvariablen:
1. S soll über die 4 Tupel in STUDENTEN laufen, und
2. B über die 8 Tupel in BEWERTUNGEN.
• Im Prinzip werden alle 4 ∗ 8 = 32 Kombinationen
betrachtet, und jeweils die WHERE-Bedingung ausgewertet. Ist sie wahr, wird die SELECT-Liste ausgegeben.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-5
4. SQL
4-6
Verbunde/Joins (3)
S −→
B −→
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
SID
101
101
101
102
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
Verbunde/Joins (4)
S.SID = B.SID
(wahr)
S −→
S.VORNAME
= ’Lisa’
(wahr)
S.NACHNAME
= ’Weiss’
(wahr)
also drucken:
B.ATYP:
’H’
B.ANR:
1
B.PUNKTE: 10
Universität Halle, 2013
4. SQL
4-7
B −→
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
SID
101
101
101
102
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
S −→
B −→
SID
101
101
101
102
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
S.VORNAME
= ’Lisa’
(wahr)
S.NACHNAME
= ’Weiss’
(wahr)
also drucken:
B.ATYP:
’H’
B.ANR:
2
B.PUNKTE: 8
Universität Halle, 2013
4. SQL
4-8
Verbunde/Joins (5)
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S.SID = B.SID
(wahr)
Verbunde/Joins (6)
S.SID = B.SID
(wahr)
S.VORNAME
= ’Lisa’
(wahr)
S −→
S.NACHNAME
= ’Weiss’
(wahr)
also drucken:
B.ATYP:
’Z’
B.ANR:
1
B.PUNKTE: 12
Universität Halle, 2013
B −→
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
SID
101
101
101
102
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
S.SID = B.SID
(falsch)
S.VORNAME
= ’Lisa’
(wahr)
S.NACHNAME
= ’Weiss’
(wahr)
nichts
drucken
(Bedingung
falsch)
Universität Halle, 2013
4. SQL
4-9
4. SQL
4-10
Verbunde/Joins (7)
S −→
B −→
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
SID
101
101
101
102
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
Verbunde/Joins (8)
S.SID = B.SID
(falsch)
S.VORNAME
= ’Lisa’
(falsch)
S −→
S.NACHNAME
= ’Weiss’
(falsch)
B −→
nichts
drucken
Universität Halle, 2013
4. SQL
4-11
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
SID
101
101
101
102
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
S −→
B −→
SID
101
101
101
102
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
S.VORNAME
= ’Lisa’
(falsch)
S.NACHNAME
= ’Weiss’
(falsch)
nichts
drucken
Universität Halle, 2013
4. SQL
4-12
Verbunde/Joins (9)
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S.SID = B.SID
(falsch)
Verbunde/Joins (10)
S.SID = B.SID
(falsch)
S.VORNAME
= ’Lisa’
(falsch)
S −→
S.NACHNAME
= ’Weiss’
(falsch)
nichts
drucken
B −→
Universität Halle, 2013
STUDENTEN
SID VORNAME NACHNAME EMAIL
101 Lisa
Weiss
···
102 Michael Grau
NULL
.
.
.
.
.
.
.
.
SID
101
101
101
102
.
.
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
.
.
.
.
.
.
S. Brass, A. Herrmann: Datenbanken und WWW
S.SID = B.SID
(wahr)
S.VORNAME
= ’Lisa’
(falsch)
S.NACHNAME
= ’Weiss’
(falsch)
nichts
drucken
Universität Halle, 2013
4. SQL
4-13
4. SQL
4-14
Verbund-Bedingungen (1)
Verbunde/Joins (11)
• Die Verbund-Bedingung S.SID = B.SID muß ex• Das waren 8 verschiedene Variablenbelegungen.
plizit unter WHERE mit angegeben werden.
Sonst werden auch Tupel kombiniert, die sich auf
Nur für drei davon war die WHERE-Bedingung erfüllt.
verschiedene Studierende beziehen.
• Insgesamt liefert die Anfrage also alle Bewertungen
• Übung: Was wäre das Ergebnis dieser Anfrage. wenn
für die Studentin Lisa Weiss:
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
S. Brass, A. Herrmann: Datenbanken und WWW
die Verbundbedingung fehlen würde?
SELECT
FROM
WHERE
AND
Universität Halle, 2013
4. SQL
4-15
S.VORNAME, S.NACHNAME
STUDENTEN S, BEWERTUNGEN B
S.SID = B.SID
B.ATYP = ’H’ AND B.ANR = 1
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-16
Verbund-Bedingungen (2)
Verbund-Bedingungen (3)
• Es ist fast immer ein Fehler, wenn es zwei Tabellen gibt, die nicht durch Verbund-Bedingungen ver-
• Die Tabellen sind wie folgt verbunden:
knüpft sind (eventuell indirekt).
• Hier sind alle drei Tupelvariablen verbunden:
SELECT
FROM
WHERE
AND
AND
AND
A.ATYP, A.ANR, B.PUNKTE, A.MAXPT
STUDENTEN S, BEWERTUNGEN B, AUFGABEN A
S.SID = B.SID
B.ATYP = A.ATYP AND B.ANR = A.ANR
S.VORNAME = ’Lisa’
S.NACHNAME = ’Weiss’
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
#
#
S
"!
B
"!
B.ATYP = A.ATYP
AND B.ANR = A.ANR
S.SID = B.SID
#
A
"!
• Das entspricht den Schlüssel-FremdschlüsselBeziehungen zwischen den Tabellen.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-17
4. SQL
4-18
Verbund-Bedingungen - andere Notation (2)
Verbund-Bedingungen - andere Notation (1)
Wenn ein natürlicher Verbund vorliegt, d. h. wenn der
Verbund über alle gleichbenannten Attribute der zu
Die Verbund- Bedingung der Anfrage von Folie 3 kann
verbindenden Tabellen erfolgt, kann folgendermaßen
auch folgendermaßen formuliert werden:
formuliert werden:
SELECT
FROM
ON
WHERE
AND
B.ATYP, B.ANR, B.PUNKTE
STUDENTEN S [INNER] JOIN BEWERTUNGEN B
S.SID = B.SID
-- Verbund-Bedingung
S.VORNAME = ’Lisa’
S.NACHNAME = ’Weiss’
SELECT
FROM
WHERE
AND
B.ATYP, B.ANR, B.PUNKTE
STUDENTEN S NATURAL JOIN BEWERTUNGEN B
S.VORNAME = ’Lisa’
S.NACHNAME = ’Weiss’
Hier muß keine Verbund-Bedingung angegeben werden.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-19
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-20
Anfrageformulierung (1)
• Aufgabe: “Geben Sie die Themen aller von Lisa
Weiss gelösten Aufgaben aus.”
• Lisa Weiss ist eine Studentin, daher sind Tupelvariable S über STUDENTEN und folgende Bedingung
nötig: S.VORNAME = ’Lisa’ AND S.NACHNAME = ’Weiss’
• Aufgaben-Themen werden verlangt, so daß eine
Tupelvariable A über AUFGABEN benötigt wird. Folgender Teil kann bereits erstellt werden:
Anfrageformulierung (2)
• S und A sind nicht verbunden.
• Es kann helfen, einen Verbindungsgraphen der Tabellen, basierend auf gemeinsamen Spalten (Fremdschlüssel), zu zeichnen:
STUDENTEN
BEWERTUNGEN
AUFGABEN
SELECT DISTINCT A.THEMA
“DISTINCT”, da viele Aufgaben das gleiche Thema haben können.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-21
4. SQL
4-22
Abkürzung: nochmal (1)
Anfrageformulierung (3)
• Wie bekannt, kann man Spalten ansprechen mit:
Variable.Spalte (geht immer)
• Man sieht, daß die Tabelle BEWERTUNGEN mit der Tupelvariablen B benötigt wird mit folgender Verbund-
Spalte (falls der Spaltenname eindeutig ist)
• Z.B. ist diese Anfrage legal:
Bedingung:
SELECT
FROM
WHERE
AND
S.SID = B.SID AND B.ATYP = A.ATYP AND B.ANR = A.ANR
• Verbinden Sie aber nicht mehr Tabellen als nötig.
ATYP, ANR, PUNKTE
STUDENTEN S, BEWERTUNGEN B
S.SID = B.SID
VORNAME = ’Lisa’ AND NACHNAME = ’Weiss’
“VORNAME” und “NACHNAME” gibt es nur in “S”, “ATYP”, “ANR” und
“PUNKTE” nur in “B”. “SID” allein wäre jedoch mehrdeutig, da sowohl
“S” als auch “B” ein Attribut mit diesem Namen haben.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-23
S. Brass, A. Herrmann: Datenbanken und WWW
4. SQL
4-24
Abkürzung: nochmal (2)
• Beispiel:
SELECT
FROM
WHERE
AND
A.ANR, SID, PUNKTE, MAXPT
BEWERTUNGEN B, AUFGABEN A
B.ANR = A.ANR
B.ATYP = ’H’ AND A.ATYP = ’H’
• SQL verlangt, daß der Nutzer festlegt, ob er B.ANR
oder A.ANR unter SELECT auswählt, obwohl beide
gleich sind, so daß es eigentlich egal wäre.
Die Regel ist rein syntaktisch: Hat mehr als eine Tupelvariable in der
FROM-Klausel das Attribut “ANR”, darf die Tupelvariable nicht fehlen
oder das DBMS (z.B. Oracle) wird den Fehler “ORA-00918: column
ambiguously defined” ausgeben. DB2, SQL Server, Access, MySQL
sind auch so pedantisch.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
Universität Halle, 2013
Selbstverbund (1)
• Es ist möglich, daß mehr als ein Tupel derselben
Relation benötigt wird, um ein bestimmtes Ergebnis zu erhalten.
• Gibt es einen Studenten, der in Hausaufgabe 1 und
in Hausaufgabe 2 jeweils 10 Punkte hat?
SELECT
FROM
WHERE
AND
AND
AND
S.VORNAME, S.NACHNAME
STUDENTEN S, BEWERTUNGEN H1, BEWERTUNGEN H2
S.SID = H1.SID AND S.SID = H2.SID
H1.ATYP = ’H’ AND H1.ANR = 1
H2.ATYP = ’H’ AND H2.ANR = 2
H1.PUNKTE = 10 AND H2.PUNKTE = 10
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-25
Selbstverbund (2)
4-26
Selbstverbund über mehrere Tabellen(1)
• Studenten, die mind. zwei Aufgaben gelöst haben:
SELECT
FROM
WHERE
AND
4. SQL
• Folgende Anfrage soll alle Studenten ausgeben, die
eine Aufgabe über SQL und eine über ER-Entwurf
DISTINCT S.VORNAME, S.NACHNAME
STUDENTEN S, BEWERTUNGEN A1, BEWERTUNGEN A2
S.SID = A1.SID AND S.SID = A2.SID
A1.ANR <> A2.ANR
• Da der Verbund über 3 Tabellen geht, reicht es
• Die Tupelvariablen A1 und A2 könnten auf das glei-
nicht aus, nur 2 Kopien der Tabelle Aufgaben an-
che Tupel in BEWERTUNGEN zeigen, deshalb muß man
zulegen, sondern auch von der Tabelle Bewertungen
verlangen, daß sie verschieden sind:
müssen 2 Kopien angelegt werden.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-27
gelöst haben:
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-28
Inhalt
Selbstverbund über mehrere Tabellen (2)
1. Verbunde
Korrekte Lösung:
'
SELECT DISTINCT S.VORNAME, S.NACHNAME
FROM
STUDENTEN S,
BEWERTUNGEN B1, BEWERTUNGEN B2,
AUFGABEN A1, AUFGABEN A2
WHERE S.SID = B1.SID AND S.SID = B2.SID
AND
B1.ATYP = A1.ATYP AND B1.ANR = A1.ANR
AND
B2.ATYP = A2.ATYP AND B2.ANR = A2.ANR
AND
A1.THEMA = ’SQL’
AND
A2.THEMA = ’ER’
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
$
2. Mehr über Vergleiche, weitere Bedingungen
&
%
3. SELECT-Klausel, Duplikate
4. Nullwerte
5. Sichten
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-29
4. SQL
Beispiel-Datenbank
SID
101
102
103
104
STUDENTEN
VORNAME NACHNAME
Lisa
Weiss
Michael Grau
Daniel Sommer
Iris
Winter
EMAIL
···
NULL
···
···
AUFGABEN
ATYP ANR THEMA MAXPT
H
1 ER
10
H
2 SQL
10
Z
1 SQL
14
SID
101
101
101
102
102
102
103
103
S. Brass, A. Herrmann: Datenbanken und WWW
4-30
Vergleiche - Zur Erinnerung
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
H
2
9
Z
1
10
H
1
5
Z
1
7
Universität Halle, 2013
4. SQL
4-31
• Zahlen werden anders verglichen als Zeichenketten,
z.B. 3 < 20, aber ’3’ > ’20’.
Strings werden Zeichen für Zeichen verglichen, bis das Ergebnis klar
ist. In diesem Fall kommt “3” alphabetisch nach “2”, daher ist der
Rest der Zeichenkette nicht wichtig.
• Es ist falsch, Zeichenketten mit Zahlen zu vergleichen, z.B. 3 > ’20’.
Die verglichenen Werte müssen von kompatiblen Datentypen sein: Alle
numerischen Typen sind kompatibel und alle String-Typen ebenfalls,
aber numerische Typen sind nicht kompatibel mit String-Typen.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-32
Zeichenkettenvergleich (1)
• Das Ergebnis eines Vergleichs (=, <>, <, <=, >, >=)
Zeichenkettenvergleich (2)
zweier Zeichenketten kann vom DBMS abhängen.
• ’a’ < ’b’ usw. und ’A’ < ’B’ usw. gelten in jedem
System.
• Ist die Reihenfolge (<, =, >) zweier Zeichen bekannt,
so vergleicht das System Zeichen für Zeichen und
• Die Systeme unterscheiden sich schon im Vergleich
von Klein- und Großbuchstaben.
der erste Vergleich, der nicht “=” ergibt, bestimmt
das Ergebnis.
In Oracle kommen alle Großbuchstaben vor den Kleinbuchstaben (ASCII),
z.B. ’Z’ < ’a’.
SQL Server, MS Access und MySQL sind case-insensitive, z.B. ’a’ = ’A’.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-33
4. SQL
4-34
LIKE-Bedingungen (1)
BETWEEN-Bedingungen
Form: Vergleichswert [NOT] BETWEEN Wert1 AND Wert2
• x BETWEEN y AND z
Form:
Vergleichswert [NOT] LIKE Musterstring
ist äquivalent zu
• Z.B.: EMAIL LIKE ’%.informatik.uni-halle.de’
x >= y AND x <= z.
• Z.B.: PUNKTE BETWEEN 5 AND 8
S. Brass, A. Herrmann: Datenbanken und WWW
Das ist für alle Email-Adressen wahr, die mit “.informatik.uni-halle.de”
enden.
Universität Halle, 2013
4. SQL
4-35
4. SQL
Universität Halle, 2013
4-36
LIKE-Bedingungen (3)
LIKE-Bedingungen (2)
• Das rechte Argument wird als Muster interpretiert.
• “%” im Muster ersetzt eine Folge beliebiger Zeichen
(den leeren String eingeschlossen).
• “_” passt auf ein beliebiges einzelnes Zeichen.
• LIKE muß zur Mustersuche verwendet werden. Das
Gleichheitszeichen überprüft nur Zeichengleichheit.
Auch wenn der Vergleichs-String “%” oder “_” enthält.
S. Brass, A. Herrmann: Datenbanken und WWW
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
• Alle Studenten, deren Nachname mit ”S” beginnt:
SELECT VORNAME, NACHNAME
FROM
STUDENTEN
WHERE NACHNAME LIKE ’S%’
• Bei Verwendung eines Gleichheitszeichens würde
der String ’S%’ gesucht
• Um die Zeichen “%” und “_” ohne ihre spezielle Bedeutung im Muster zu verwenden, wird ein
“Escape”-Zeichen ( “\”) verwendet.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-37
4. SQL
4-38
Inhalt
IN-Bedingungen
Form:
1. Verbunde
2. Mehr über Vergleiche, weitere Bedingungen
Vergleichswert [NOT] IN ( Aufzählung von Werten,
mit Komma getrennt )
'
$
3. SELECT-Klausel, Duplikate
&
%
4. Nullwerte
• Z.B. ATYP IN (’Z’, ’E’)
5. Sichten
• Dies ist äquivalent zu
ATYP = ’Z’ OR ATYP = ’E’
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-39
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-40
Duplikat-Eliminierung (1)
SELECT-Klausel, ∗
• In SQL müssen Duplikate explizit eliminiert werden.
• SELECT legt die Terme fest, die ausgegeben werden,
falls die WHERE-Bed. wahr ist (Ergebnis-Spalten).
• SELECT * kann verwendet werden, um alle Spalten
der Tabelle(n) unter FROM auszugeben, z.B. ist
SELECT *
FROM
STUDENTEN
äquivalent zu
SELECT SID, VORNAME, NACHNAME, EMAIL
FROM
STUDENTEN
• Z.B.: Welche Aufgaben wurden von mindestens einem Studenten gelöst?
SELECT ATYP, ANR
FROM
BEWERTUNGEN
ATYP
H
H
Z
H
H
Z
H
Z
ANR
1
2
1
1
2
1
1
1
• Die Duplikate treten auf, weil die Anfrage über alle
Tupel in BEWERTUNGEN ausgeführt wird.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-41
4. SQL
4-42
Duplikat-Eliminierung (2)
• Könnte eine Anfrage Duplikate enthalten und gibt
es keinen Grund, diese mit auszugeben, verwendet
man “SELECT DISTINCT”:
SELECT DISTINCT ATYP, ANR
FROM
BEWERTUNGEN
Umbenennung von Spalten (1)
• Um Ausgabe-Spalten umzubenennen:
SELECT VORNAME V_Name, NACHNAME
FROM
STUDENTEN
ATYP ANR
H
1
H
2
Z
1
V_NAME
Lisa
Michael
Daniel
Iris
• Man beachte, daß DISTINCT immer zu ganzen Zeilen
gehört, nicht zu einzelnen Spalten:
Name
Weiss
Grau
Sommer
Winter
Falsch!
SELECT ATYP, ANR, DISTINCT THEMA
FROM
AUFGABEN
S. Brass, A. Herrmann: Datenbanken und WWW
"Name"
Universität Halle, 2013
4. SQL
4-43
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-44
Inhalt
Umbenennung von Spalten (2)
1. Verbunde
• Ohne die doppelten Anführungszeichen wird die Überschrift in Großbuchstaben ausgegeben.
• Mit den doppelten Anführungszeichen wird die Überschrift genau so ausgegeben, wie sie in der SQLAnweisung geschrieben wurde.
2. Mehr über Vergleiche, weitere Bedingungen
3. SELECT-Klausel, Duplikate
'
4. Nullwerte
&
Sie kann dann auch Sonderzeichen bzw. Leerzei-
$
%
5. Sichten
chen enthalten.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-45
4. SQL
Nullwerte (1)
Beispiel-Datenbank
SID
101
102
103
104
STUDENTEN
VORNAME NACHNAME
Lisa
Weiss
Michael Grau
Daniel Sommer
Iris
Winter
EMAIL
···
NULL
···
···
AUFGABEN
ATYP ANR THEMA MAXPT
H
1 ER
10
H
2 SQL
10
Z
1 SQL
14
SID
101
101
101
102
102
102
103
103
S. Brass, A. Herrmann: Datenbanken und WWW
4-46
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
H
2
9
Z
1
10
H
1
5
Z
1
7
• Wie in Kapitel 2 erwähnt, können Tabelleneinträge
einen Nullwert enthalten (falls nicht mit NOT NULL in
der Tabellendeklaration ausgeschlossen).
• Der Nullwert ist von allen normalen Werten des Datentyps verschieden, insbesondere ist er verschieden
von der Zahl 0 und dem leeren String.
• Ohne Nullwerte wäre es nötig, die meisten Relationen aufzuspalten (“Subklassen”):
Z.B. STUDENTEN_MIT_EMAIL, STUDENTEN_OHNE_EMAIL.
Oder extra Relation: STUD_EMAIL(SID, EMAIL).
Universität Halle, 2013
4. SQL
4-47
Nullwerte (2)
S. Brass, A. Herrmann: Datenbanken und WWW
4. SQL
4-48
Dreiwertige Logik (1)
Nullwerte werden in vielen verschiedenen Situationen
verwendet, z.B.:
• SQL benutzt eine dreiwertige Logik, um Bedingun-
• Ein Wert existiert, ist aber unbekannt.
gen mit Nullwerten auszuwerten:
• Es existiert kein Wert.
wahr/falsch/unbekannt.
• Es könnte ein (unbekannter) Wert existieren,
oder auch keiner.
• Spalte ist auf dieses Tupel nicht anwendbar (den
Wert gibt es für dieses Tupel nicht.
• Wert wird später zugewiesen/bekannt gegeben.
• Jeder Wert ist möglich.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
Universität Halle, 2013
Da man an die normale zweiwertige Logik gewöhnt ist, kann es Überraschungen geben — einige Äquivalenzen gelten nicht.
Für Berechnungen (z.B. +) gilt:
• Ist einer der Eingabewerte (Argumente) Null,
so das Ergebnis auch Null.
• Ist z.B. A Null, so ist A+B ebenfalls Null.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-49
4. SQL
4-50
Dreiwertige Logik (2)
Dreiwertige Logik (3)
• Betrachten Sie folgende Anfrage:
SELECT VORNAME, NACHNAME
FROM
STUDENTEN
WHERE EMAIL = ’[email protected]’
• Die Bedingung
EMAIL = ’[email protected]’
• Was passiert, wenn ein Student in der Spalte EMAIL
einen Nullwert hat? Er wird nicht ausgegeben.
• Aber er tritt auch nicht im Ergebnis dieser Anfrage
auf (weil der Wert nicht bekannt ist):
SELECT VORNAME, NACHNAME
FROM
STUDENTEN
WHERE NOT (EMAIL = ’[email protected]’)
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-51
ist, wenn EMAIL Null ist, weder falsch noch wahr.
• SQL verwendet eine dreiwertige Logik, um Nullwerte zu behandeln. Die drei Wahrheitswerte sind
wahr, falsch und (unbekannt oder null).
• In SQL ergibt ein Vergleich mit einem Nullwert immer den dritten Wahrheitswert “unbekannt”.
S. Brass, A. Herrmann: Datenbanken und WWW
4. SQL
Universität Halle, 2013
4-52
Dreiwertige Logik (4)
Test auf Null (1)
P
falsch
falsch
falsch
null
null
null
wahr
wahr
wahr
Q
falsch
null
wahr
falsch
null
wahr
falsch
null
wahr
NOT P
wahr
wahr
wahr
null
null
null
falsch
falsch
falsch
P AND Q P OR Q
falsch
falsch
falsch
null
falsch
wahr
falsch
null
null
null
null
wahr
falsch
wahr
null
wahr
wahr
wahr
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
Form:
Term IS [NOT] NULL
Beispiel:
• EMAIL IS NULL oder
• EMAIL IS NOT NULL
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-53
4. SQL
4-54
Test auf Null (2)
Probleme mit Nullwerten
Aufgabe:
• Die folgende Anfrage gibt alle Studenten mit einer
Email-Adresse in der Domäne “.uni-halle.de” aus:
SELECT
FROM
WHERE
AND
Null-Werte zählen weder zu den Gleichheiten noch zu
VORNAME, NACHNAME
STUDENTEN
EMAIL IS NOT NULL
EMAIL LIKE ’%.uni-halle.de’
den Ungleichheiten:
• Ist der Test auf Null notwendig? Oder ist folgende
Anfrage äquivalent?
SELECT VORNAME, NACHNAME
FROM
STUDENTEN
WHERE EMAIL LIKE ’%.uni-halle.de’
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
Wichtig:
4-55
Z.B. ist X = X “unbekannt” und nicht “wahr”, wenn X
Null ist.
S. Brass, A. Herrmann: Datenbanken und WWW
4. SQL
Inhalt
SID
101
102
103
104
2. Mehr über Vergleiche, weitere Bedingungen
3. SELECT-Klausel, Duplikate
4. Nullwerte
$
5. Sichten
&
S. Brass, A. Herrmann: Datenbanken und WWW
4-56
Beispiel-Datenbank
1. Verbunde
'
Universität Halle, 2013
%
Universität Halle, 2013
STUDENTEN
VORNAME NACHNAME
Lisa
Weiss
Michael Grau
Daniel Sommer
Iris
Winter
EMAIL
···
NULL
···
···
AUFGABEN
ATYP ANR THEMA MAXPT
H
1 ER
10
H
2 SQL
10
Z
1 SQL
14
S. Brass, A. Herrmann: Datenbanken und WWW
SID
101
101
101
102
102
102
103
103
BEWERTUNGEN
ATYP ANR PUNKTE
H
1
10
H
2
8
Z
1
12
H
1
9
H
2
9
Z
1
10
H
1
5
Z
1
7
Universität Halle, 2013
4. SQL
4-57
4. SQL
Sichten (2)
Sichten (1)
• Sichten erlauben es, eine Anfrage in der Datenbank
abzuspeichern, und ihr einen Namen zu geben (man
kann auch die Ergebnisspalten umbenennen):
CREATE VIEW HA 1(NACHNAME, VORNAME, PUNKTE A1)
AS
SELECT S.NACHNAME, S.VORNAME, B.PUNKTE
FROM
STUDENTEN S, BEWERTUNGEN B
WHERE
B.ATYP = ’H’ AND B.ANR = 1
AND
S.SID = B.SID
• Beispiel: Anfrage an die Sicht:
SELECT X.NACHNAME, X.VORNAME
FROM
HA 1 X
WHERE X.PUNKTE A1 > 8
• Das DBMS kann intern den Namen der Sicht einfach durch die definierende Anfrage ersetzen:
SELECT X.NACHNAME, X.VORNAME
FROM (SELECT NACHNAME, VORNAME, PUNKTE
FROM
STUDENTEN S, BEWERTUNGEN B
WHERE ATYP = ’H’ AND ANR = 1
AND
S.SID = B.SID) X
WHERE X.PUNKTE > 8
• Man kann Sichten (“virtuelle Tabellen”) in Anfragen wie normale Tabellen (“Basistabellen”) nutzen.
S. Brass, A. Herrmann: Datenbanken und WWW
4-58
Universität Halle, 2013
4. SQL
4-59
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-60
Sichten (3)
• Sichten sind abgeleitete, virtuelle Tabellen, die aus
den (tatsächlich abgespeicherten) Basistabellen berechnet werden.
Sichten (4)
• Die Anfrage der Sicht wird im Prinzip immer neu
ausgewertet, wenn die Sicht benutzt wird.
Nur der Text der definierenden Anfrage wird dauerhaft im System
gespeichert.
• Wenn die in der Sicht verwendeten Basistabellen
• Sichten können also nie Informationen enthalten,
(STUDENTEN, BEWERTUNGEN) geändert werden, spiegelt
die nicht auch in den Basistabellen enthalten ist.
die Sicht automatisch diese Änderungen wider. Sie
• Sichten können aber die in den Basistabellen ent-
ist also immer ganz aktuell.
haltene Information anders strukturiert anzeigen.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-61
4. SQL
4-62
Syntax (1)
Sichten (5)
CREATE VIEW NAME (attribut1, attribut2, ...)
• Sichten können auch in der Definition anderer Sichten verwendet werden.
AS
Anfrage
• Auf diese Art können komplexe Anfragen Schritt
für Schritt aufgebaut werden.
In Oracle kann man folgendes schreiben:
• Sichtdefinitionen können gelöscht werden mit:
DROP VIEW NAME
CREATE OR REPLACE VIEW NAME ...
Überschreibt ggf. bereits existierende Definition.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-63
Anwendungen von Sichten (1)
• Bequemlichkeit / Wiederverwendung: Wiederkehrende Muster in Anfragen sind bereits vordefiniert.
• Die Basisrelationen sollten keine Redundanzen enthalten. Relationen mit abgeleiteten Informationen
sind aber manchmal in Anfragen bequemer.
Redundante Daten in Sichten sind kein Problem, weil diese Daten ja
nicht abgespeichert werden, sondern nach Bedarf berechnet werden.
• Schrittweiser Aufbau komplexer Anfragen.
• Anpassung des DB-Schemas an die Wünsche verschiedener Benutzer / Benutzer-Gruppen.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013
4. SQL
4-64
Anwendungen von Sichten (2)
• Sicherheit: Bestimmte Benutzer sollten nur einen
Teil einer Tabelle (gewisse Zeilen/Spalten) sehen
können, oder nur aggregierte/anonymisierte Daten.
Ohne Sichten ist die Granularität für Zugriffsrechte im wesentlichen
die Tabelle. Nur die Änderungsrechte können üblicherweise auch für
einzelne Spalten vergeben werden.
• Logische Datenunabhängigkeit: Man kann neue Attribute zu einer Tabelle hinzufügen, und die alte
Version noch als Sicht zur Verfügung stellen.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2013