4. SQL
4-1
4. SQL
4-2
Inhalt
Beispiel-Datenbank
'
$
1. Verbunde &
%
2. Mehr u ¨ber Vergleiche, weitere Bedingungen 3. SELECT-Klausel, Duplikate 4. Nullwerte
Universit¨ at Halle, 2012
4. SQL
4-3
Universit¨ at Halle, 2012
4. SQL
4-4
Verbunde/Joins (2)
• Wenn man Daten aus mehreren Tabellen verkn¨ upft, spricht man von einem Verbund (eng. Join). • Folgende Anfrage enth¨ alt 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¨ at Halle, 2012
• Die obige Anfrage deklariert zwei Tupelvariablen: 1. S soll u ¨ber die 4 Tupel in STUDENTEN laufen, und 2. B ¨ uber 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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ at Halle, 2012
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¨ ur drei davon war die WHERE-Bedingung erf¨ ullt.
verschiedene Studierende beziehen.
• Insgesamt liefert die Anfrage also alle Bewertungen
¨ bung: Was w¨ • U are das Ergebnis dieser Anfrage. wenn
f¨ ur 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¨ urde? SELECT FROM WHERE AND Universit¨ at Halle, 2012
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¨ at Halle, 2012
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¨ upft 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¨ at Halle, 2012
#
#
S "!
B "! B.ATYP = A.ATYP AND B.ANR = A.ANR
S.SID = B.SID
#
A
"!
• Das entspricht den Schl¨ ussel-Fremdschl¨ usselBeziehungen zwischen den Tabellen.
S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
4. SQL
4-17
4. SQL
4-18
Verbund-Bedingungen - andere Notation (2) Verbund-Bedingungen - andere Notation (1)
Wenn ein nat¨ urlicher Verbund vorliegt, d. h. wenn der Verbund u ¨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¨ at Halle, 2012
4. SQL
4-19
S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
4. SQL
4-20
Anfrageformulierung (1) • Aufgabe: “Geben Sie die Themen aller von Lisa Weiss gel¨ osten Aufgaben aus.” • Lisa Weiss ist eine Studentin, daher sind Tupelvariable S ¨ uber STUDENTEN und folgende Bedingung n¨ otig: S.VORNAME = ’Lisa’ AND S.NACHNAME = ’Weiss’ • Aufgaben-Themen werden verlangt, so daß eine Tupelvariable A ¨ uber AUFGABEN ben¨ otigt 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¨ ussel), zu zeichnen: STUDENTEN
BEWERTUNGEN
AUFGABEN
SELECT DISTINCT A.THEMA “DISTINCT”, da viele Aufgaben das gleiche Thema haben k¨ onnen. S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
4. SQL
4-21
4. SQL
4-22
Abk¨ urzung: 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¨ otigt 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¨ otig.
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¨ are jedoch mehrdeutig, da sowohl “S” als auch “B” ein Attribut mit diesem Namen haben. S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
4. SQL
4-23
S. Brass, A. Herrmann: Datenbanken und WWW
4. SQL
4-24
Abk¨ urzung: 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¨ ahlt, obwohl beide gleich sind, so daß es eigentlich egal w¨ are. 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¨ at Halle, 2012
Universit¨ at Halle, 2012
Selbstverbund (1) • Es ist m¨ oglich, daß mehr als ein Tupel derselben Relation ben¨ otigt 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¨ at Halle, 2012
4. SQL
4-25
Selbstverbund (2)
4-26
Selbstverbund u ¨ber mehrere Tabellen(1)
• Studenten, die mind. zwei Aufgaben gel¨ ost haben: SELECT FROM WHERE AND
4. SQL
• Folgende Anfrage soll alle Studenten ausgeben, die eine Aufgabe u ¨ber SQL und eine u ¨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 u ¨ber 3 Tabellen geht, reicht es
• Die Tupelvariablen A1 und A2 k¨ onnten 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¨ ussen 2 Kopien angelegt werden.
S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
4. SQL
4-27
gel¨ ost haben:
S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
4. SQL
4-28
Inhalt Selbstverbund u ¨ber mehrere Tabellen (2) 1. Verbunde
Korrekte L¨ osung:
'
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¨ at Halle, 2012
$
2. Mehr u ¨ber Vergleiche, weitere Bedingungen &
%
3. SELECT-Klausel, Duplikate 4. Nullwerte 5. Sichten
S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
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¨ at Halle, 2012
4. SQL
4-31
• Zahlen werden anders verglichen als Zeichenketten, z.B. 3 < 20, aber ’3’ > ’20’. Strings werden Zeichen f¨ ur 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¨ ussen 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¨ at Halle, 2012
4. SQL
4-32
Zeichenkettenvergleich (1) • Das Ergebnis eines Vergleichs (=, , =)
Zeichenkettenvergleich (2)
zweier Zeichenketten kann vom DBMS abh¨ angen. • ’a’ < ’b’ usw. und ’A’ < ’B’ usw. gelten in jedem System.
• Ist die Reihenfolge () zweier Zeichen bekannt, so vergleicht das System Zeichen f¨ ur 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¨ at Halle, 2012
S. Brass, A. Herrmann: Datenbanken und WWW
Universit¨ at Halle, 2012
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 ¨ aquivalent zu
• Z.B.: EMAIL LIKE ’%.informatik.uni-halle.de’
x >= y AND x