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