a: select

Anwendersoftware (AS)
Grundlagen von Datenbanken und
Informationssystemen
Kapitel
p
4:
Standardsprache SQL
Holger Schwarz
Wintersemester 2009/10
Teile zu diesem Folienskript beruhen auf einer ähnlichen Vorlesung, gehalten von Prof. Dr. T. Härder am
Fachbereich Informatik der Universität Kaiserslautern und Prof. Dr. N. Ritter am Fachbereich Informatik der
Universität Hamburg. Für dieses Skriptum verbleiben alle Rechte (insbesondere für Nachdruck) bei den
Autoren.
SQL
Übersicht
•
•
•
•
•
•
•
•
Grundlagen
g
Mengenorientierte Anfragen (Retrieval)
Möglichkeiten der Datenmanipulation
Möglichkeiten der Datendefinition
Beziehungen und referentielle Integrität
Schemaevolution
Indexierung
Sichten
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
2
SQL
Grundlagen
•
•
•
Seit 1974 viele Sprachentwürfe
ƒ SQUARE: Specifying Queries As Relational Expressions
ƒ SEQUEL: Structured English Query Language
Weiterentwicklung zu
ƒ SQL: Structured Query Language
- strukturierte Sprache
- basiert auf englischen Schlüsselwörtern
ƒ Weitere ähnliche Sprachen: QUEL, . . .
Mächtigkeit
g
von SQL
Q
ƒ Auswahlvermögen äquivalent dem Relationenkalkül und der
Relationenalgebra
ƒ Trotz Vermeidung von mathematischen Konzepten wie Quantoren relational
vollständig
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
3
SQL
Sprachentwicklung von SQL
•
•
•
•
•
•
Entwicklung einer vereinheitlichten DB-Sprache für alle Aufgaben der
DB-Verwaltung
Erweiterung der Anfragesprache zur „natürlichen“ Formulierung
bestimmter Fragen
Lehrexperimente mit Studenten mit und ohne Programmiererfahrung
gezielte Verbesserungen verschiedener Sprachkonstrukte zur Erleichterung des
Verständnisses und zur Reduktion von Fehlern
leichter Zugang durch verschiedene „Sprachebenen“ anwachsender Komplexität:
ƒ einfache Anfragemöglichkeiten für den gelegentlichen Benutzer
ƒ mächtige
ä hti Sprachkonstrukte
S
hk
t kt für
fü d
den besser
b
ausgebildeten
bild t Benutzer
B
t
Spezielle Sprachkonstrukte für den DBA
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
4
SQL
Standardisierung
•
•
SQL wurde „de facto“-Standard in der relationalen Welt
ƒ 1986 von ANSI akzeptiert
ƒ 1987 von ISO akzeptiert
Weiterentwicklung des Standards in mehreren Stufen
ƒ SQL2 (1992)
ƒ (SQL3) SQL:1999
ƒ SQL:2003
ƒ SQL:2008
Artikel Präsentationen und Dokumente zu SQL:
Artikel,
http:// www.wiscorp.com/SQLStandards.html
http://www.jcc.com/sql.htm
Bücher zum SQL-Standard:
SQL Standard:
[MSG99] Melton, J., Simon, A.R., Gray, J.: SQL: 1999 - Understanding Relational Language
Components, Morgan Kaufmann Series in Data Management Systems, 1999.
[[Me02]]
Melton,, J.: Advanced SQL:
Q 1999 - Understanding
g Object-Relational
j
and Other
Advanced Features, Morgan Kaufmann Series in Data Management Systems, 2002.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
5
SQL
SQL Standards
SQL-Standards
• ISO/IEC 9075:1986
(SQL86)
• ISO/IEC 9075:1989
((SQL89)
Q
)
• ISO/IEC 9075:1992
(SQL2, SQL92)
• ISO/IEC 9075-x:1999
(SQL3, SQL99)
zusätzliche Sprachanbindung
(z.B. C und Ada),
DDL als separate Teilsprache,
Integritätsbedingungen: UNIQUE,
NOT NULL, CHECK, CONSTRAINT,
…
Datentypen: DATE, TIME, …
Joins: NATURAL JOIN, OUTER JOIN, …
Isolationsstufen
so at o sstu e für
ü Transaktionen
a sa t o e
Objekt-relationale Erweiterungen:
benutzerdefinierte Datentypen,
yp ,
Tabellenhierarchien, …,
TRIGGER, OLAP-Erweiterungen
• ISO/IEC 9075-x:2003
1987
1989
1992
1999
2003
(SQL2003)
• ISO/IEC 9075-x:2008
(SQL2008)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
XML-Unterstützung,
Datentypen: MULTISET, BIGINT,
Funktionen: RANK,
RANK
Sampling,
MERGE-Statement
2008
6
SQL
SQL:2008
• ISO/IEC 9075-x:2008
Part 1: Framework (SQL/Framework)
Part 2: Foundation (SQL/Foundation)
Part 3: Call-Level
Call Level Interface (SQL/CLI)
Part 4: Persistent Stored Modules (SQL/PSM)
Part 9: Management of External Data (SQL/MED)
Part 10: Object Language Bindings (SQL/OLB)
Part 11: Information and Definition Schemas
((SQL/Schemata)
Q /
)
ƒ Part 13: SQL Routines and Types Using the Java TM
Programming Language (SQL/JRT)
ƒ Part 14: XML-Related
XML Related Specifications (SQL/XML)
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
Seitenzahl
≈ 80
≈ 1300
≈ 400
≈ 190
≈ 490
≈ 400
≈ 290
≈ 200
≈ 440
Die Teile 5, 6, 7, 8, 12
existieren
i ti
nicht!
i ht!
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
7
SQL
SQL: Eine abbildungsorientierte Sprache
ƒ Grundbaustein:
SELECT ...
FROM ...
WHERE ...
Abbildung
ƒ Ein bekanntes Attribut oder eine Menge von Attributen wird mit Hilfe einer
Relation in ein gewünschtes Attribut oder einer Menge von Attributen
abgebildet.
abgebildet
ƒ Allgemeines Format:
<Spezifikation der Operation>
<Liste der referenzierten Tabellen>
[WHERE Boolescher Prädikatsausdruck]
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
8
SQL
SQL92-Syntax
SQL92
Syntax (Auszug)
•
Table=Relation, Column=Attribut, Listenelemente durch Komma getrennt
SQL-statement:
query
CREATE TABLE
table-name
(
attr-defn-list
)
attr-name-list
, PRIMARY KEY (
CREATE VIEW
view-name
AS
(
attr-name-list
)
query
)
table-name
query
INSERT INTO
view-name
(
attr-name-list
)
VALUES
set-list
table-name
UPDATE
SET
set-list
view-name
WHERE
condition
table-name
DELETE FROM
view-name
DROP TABLE
table name
table-name
DROP VIEW
view-name
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
WHERE
condition
9
SQL
SQL92-Syntax
SQL92
Syntax
Bitte keine
Aggregatfunktionen
verwenden
query:
SELECT
expr-list
DISTINCT
*
GROUP BY
attr-spec-list
FROM
from-list
WHERE
HAVING
condition
ORDER BY
condition
attr-spec-list
ASC
DESC
predicate:
condition:
predicate
NOT
condition
condition
expr
condition
NOT
OR
expr-list
condition
expr
expr
constant
comparison
expr-list
ALL
comparison
NOT
EXISTS
attr-spec
function
query
condition
expr:
expr
expr
comparison
ANY
IN
query
expr-list
query
query
NOT
arithm op
arithm.op
expr
attr-spec
attr-spec
arithm.op: + - * /
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
IS
NOT
NULL
LIKE
constant
comparison: = <> < > <= >=
10
SQL
SQL92 Syntax
SQL92-Syntax
set:
from:
attr-spec
attr
spec
table-name
table
name
view-name
from
=
expr
.
attr-name
var-name
LEFT JOIN
from
ON
condition
attr-spec:
attr-name
tt
table-name
var-name
function:
attr-defn:
attr-name
AVG(
MAX(
MIN(
expr
)
INTEGER
NUMERIC (
DECIMAL (
DISTINCT
attr-spec
SUM(
CHAR (
length
)
NOT NULL
VARCHAR (
COUN(*)
COUNT(DISTINCT
(
attr-spec
p
)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
11
SQL
Übersicht
• Grundlagen
g
• Mengenorientierte Anfragen (Retrieval)
ƒ einfache Anfragen
ƒ Erklärungsmodell
ƒ Suchbedingungen
• Möglichkeiten der Datenmanipulation
• Möglichkeiten der Datendefinition
• Beziehungen und referentielle Integrität
• Schemaevolution
• Indexierung
• Sichten
Si h
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
12
SQL
Anfragen
•
SELECT-Anweisung
select-exp ::=
•
SELECT [ALL | DISTINCT] select-item-commalist
FROM table-ref-commalist
[
[WHERE
cond-exp]
d
]
[GROUP BY column-ref-commalist]
[HAVING cond-exp]
Grob:
ƒ SELECT * :
ƒ FROM-Klausel:
ƒ WHERE-Klausel:
Ausgabe ‚ganzer‘ Tupel
spezifiziert zu verarbeitende Relation bzw.
Sammlung (elementarer) Prädikate der Form
Ai Θ ai oder Ai Θ Aj (Θ ∈ { =,, <>, <, <, >, > }),
die mit AND und OR verknüpft sein können
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
13
SQL
Beispiel-Schema
Beispiel
Schema
DICHTER (DI)
AUTOR G-ORT G-JAHR
DRAMA (DR)
TITEL
AUTOR KRITIKER U-ORT U-JAHR
SCHAUSPIELER (SP)
PNR
W-ORT NAME
ROLLE (RO)
FIGUR
TITEL
R-TYP
DARSTELLER (DA)
PNR FIGUR
A JAHR A
A-JAHR
A-ORT
ORT THEATER
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
14
SQL
Anfragen
•
Untermengenbildung
Welche Dramen von Goethe wurden nach 1800 uraufgeführt?
SELECT
FROM
WHERE
•
*
DRAMA
AUTOR = ‘Goethe’ AND U-JAHR > 1800;
Benennung von Ergebnis-Spalten
ƒ Ausgabe von Attributen, Text oder Ausdrücken
ƒ Spalten der Ergebnisrelation können (um)benannt werden (AS)
SELECT
FROM
NAME,,
‘Berechnetes Alter: ‘ AS TEXT,
CURRENT_DATE – GEBDAT AS ALTER
SCHAUSPIELER;
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
15
SQL
Anfragen
•
Test auf Mengenzugehörigkeit
Ai IN (a1, aj, ak)
Ai IN (SELECT . . .)
explizite Mengendefinition
implizite Mengendefinition
Finde die Schauspieler (PNR), die Faust, Hamlet oder Wallenstein gespielt
haben.
SELECT
FROM
WHERE
•
DISTINCT PNR
DARSTELLER
FIGUR IN („Faust
( Faust“, „Hamlet
Hamlet“, „Wallenstein
Wallenstein“);
);
Duplikateliminierung
ƒ Default:
D f lt keine
k i Duplikateliminierung
D lik t li i i
ƒ DISTINCT erzwingt Duplikateliminierung
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
16
SQL
Anfragen
•
Geschachtelte Abbildung
g
Welche Figuren kommen in Dramen von Schiller oder Goethe vor?
SELECT
FROM
äußere
Abbildung WHERE
•
•
DISTINCT FIGUR
ROLLE
TITEL IN (SELECT TITEL
FROM
DRAMA
innere
( Schiller“, „Goethe
Goethe“));
));
Abbildung WHERE AUTOR IN („Schiller
Innere und äußere Relationen können identisch sein
Eine geschachtelte Abbildung kann beliebig tief sein
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
17
SQL
Anfragen
•
Symmetrische Abbildung
Finde die Figuren und ihre Autoren,
Autoren die in Dramen von Schiller oder Goethe
vorkommen.
SELECT
FROM
WHERE
•
•
FIGUR, AUTOR
FIGUR
ROLLE RO, DRAMA DR
(RO.TITEL=DR.TITEL) AND
(DR AUTOR=„Schiller
(DR.AUTOR
Schiller“ OR DR.AUTOR
DR AUTOR=„Goethe
Goethe“);
);
Einführung von Tupelvariablen (correlation names) erforderlich
Vorteile der symmetrischen Notation
ƒ keine Vorgabe der Auswertungsrichtung (DBS optimiert !)
ƒ direkte Formulierung von Vergleichsbedingungen über Relationengrenzen
hinweg möglich
ƒ einfache Formulierung des Verbundes
ƒ Ausgabe
g
von Größen aus inneren Blöcken
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
18
SQL
Anfragen
•
Symmetrische Abbildung
Finde die Dichter (AUTOR, G-ORT), deren Dramen von Dichtern
mit demselben Geburtsort (G-ORT) kritisiert wurden.
SELECT A.AUTOR, A.G-ORT
FROM DICHTER A, DRAMA D, DICHTER B
WHERE A.AUTOR
A AUTOR = D.AUTOR
D AUTOR
AND D.KRITIKER = B.AUTOR
AND A.G-ORT = B.G-ORT;
•
Diskussion:
Welche Rolle spielen die Bedingungen A.AUTOR = D.AUTOR und
D KRITIKER = B.AUTOR
D.KRITIKER
B AUTOR in
i der
d erhaltenen
h lt
Lösung?
Lö
?
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
19
SQL
Anfragen
•
Symmetrische
y
Abbildung
g
Finde die Schauspieler (NAME, W-ORT), die bei in Weimar uraufgeführten
Dramen an ihrem Wohnort als ’Held’ mitgespielt haben.
A:
•
SELECT S.NAME, S.W-ORT
FROM SCHAUSPIELER S, DARSTELLER D, ROLLE R, DRAMA A
WHERE
S.PNR = D.PNR
AND
D.FIGUR = R.FIGUR
AND
R.TITEL = A.TITEL
AND
A U ORT = ’Weimar’
A.U-ORT
’W i
’
AND
R.R-TYP = ’Held’
AND
D.A-ORT = S.W-ORT;
F1
F2
F3
F4
F5
F6
Diskussion:
Wie sieht das Auswertungsmodell (Erklärungsmodell) bei symmetrischer
Notation aus?
a s?
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
20
SQL
Auswertungs /Erklärungsmodell
Auswertungs-/Erklärungsmodell
•
Einfacher Operatorbaum für Anfrage A
πNAME, W
W-ORT
ORT
σF1 ∧ F2 ∧ F3 ∧ F4 ∧ F5 ∧ F6
Optimierung?
i i
×
×
×
SP
DA
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
RO
DR
21
SQL
Auswertungs /Erklärungsmodell
Auswertungs-/Erklärungsmodell
•
Optimierter Operatorbaum für Anfrage A
πNAME, W
W-ORT
ORT
Heuristische Optimierungsregeln:
σF6
1. Führe Selektionen so früh wie möglich aus!
g so,, dass
2. Bestimme die Verbundreihenfolge
die Anzahl und Größe der Zwischenobjekte
minimiert wird!
F1
F2
F3
SP
DA
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
σF5
σF4
RO
DR
22
SQL
Benutzer-spezifizierte
Benutzer
spezifizierte Reihenfolge der
Ausgabe
ORDER BY order-item-commalist
Finde die Schauspieler, die an einem Ort wohnen, an dem sie gespielt haben,
sortiert nach Name (aufsteigend), W-Ort (absteigend).
SELECT
FROM
WHERE
ORDER BY
S.NAME, S.W-ORT
SCHAUSPIELER S, DARSTELLER D
S.PNR = D.PNR AND S.W-ORT = D.A-ORT
S.NAME ASC, S.W-ORT DESC;
ƒ Ohne Angabe der ORDER-BY-Klausel wird die Reihenfolge der Ausgabe durch
das System bestimmt (Optimierung der Auswertung).
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
23
SQL
Aggregatfunktionen
aggregate-function-ref ::= COUNT(*) |
{AVG | MIN | MAX | SUM | COUNT} ( [ALL | DISTINCT] scalar-exp )
ƒ Standard-Funktionen: AVG, SUM, COUNT, MIN, MAX
- Elimination von Duplikaten : DISTINCT
- keine Elimination : ALL (Defaultwert)
- Typverträglichkeit erforderlich
Bestimme das Durchschnittsgehalt der Schauspieler, die älter als 50
Jahre sind (GEHALT und ALTER seien Attribute von SP).
SP)
SELECT AVG(GEHALT) AS Durchschnittsgehalt
FROM
SCHAUSPIELER
WHERE ALTER > 50;
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
24
SQL
Aggregatfunktionen
•
Auswertung
ƒ Aggregat-Funktion (AVG) wird angewendet auf einstellige Ergebnisliste
(GEHALT)
ƒ keine Eliminierung von Duplikaten
ƒ Verwendung von arithmetischen Ausdrücken ist möglich:
AVG (GEHALT/12)
An wie vielen Orten wurden Dramen uraufgeführt (U-Ort)?
SELECT
FROM
COUNT (DISTINCT U
U-ORT)
ORT)
DRAMA;
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
25
SQL
Aggregatfunktionen
An welchen Orten wurden mehr als zwei Dramen uraufgeführt?
SELECT
FROM
WHERE
DISTINCT U-ORT
DRAMA
COUNT(U ORT) 2
COUNT(U-ORT)>2;
ƒ Aggregat-Funktionen in WHERE-Klausel unzulässig!
ƒ keine geschachtelte Nutzung von Funktionsreferenzen!
SELECT
FROM
WHERE
DISTINCT U-ORT
DRAMA D
2 < (SELECT COUNT(*)
FROM
DRAMA X
WHERE X.U-ORT = D.U-ORT);
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
26
SQL
Aggregatfunktionen
Welches Drama wurde zuerst aufgeführt ?
SELECT
FROM
TITEL, MIN(U-JAHR)
DRAMA;
SELECT
FROM
WHERE
TITEL, U-JAHR
TITEL
DRAMA
U-JAHR = ( SELECT MIN(U-JAHR)
FROM DRAMA X);
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
27
SQL
Partitionierung
GROUP BY column-ref-commalist
Beispielschema:
p
PERS ((PNR, NAME, GEHALT, ALTER, ANR))
PRIMARY KEY (PNR)
Liste alle Abteilungen
g und das Durchschnittsgehalt
g
ihrer Angestellten
g
auf
(Monatsgehalt).
SELECT
FROM
GROUP BY
ANR,, AVG(GEHALT)
(
)
PERS
ANR;
ƒ Die GROUP-BY-Klausel wird immer zusammen mit einer AggregatFunktion benutzt. Die Aggregat-Funktion wird jeweils auf die Tupel
einer Gruppe angewendet
angewendet. Die Ausgabe-Attribute
Ausgabe Attribute müssen verträglich
miteinander sein!
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
28
SQL
Partitionierung
HAVING cond-exp
Liste die Abteilungen zwischen K50 und K60 auf, bei denen das
Durchschnittsalter ihrer Angestellten kleiner als 30 ist.
SELECT
FROM
WHERE
GROUP BY
HAVING
ANR
PERS
ANR > K50
0 AND ANR < K60
60
ANR
AVG(ALTER) < 30;
Diskussion: Allgemeines Erklärungsmodell?
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
29
SQL
Hierarchische Beziehungen
g
auf einer Relation
Beispielschema:
PERS (PNR, NAME, GEHALT, MNR)
PRIMARY KEY (PNR)
FOREIGN KEY MNR REFERENCES PERS
Finde die Angestellten, die mehr als ihre (direkten) Manager
verdienen (Ausgabe: NAME, GEHALT, NAME des Managers).
B: SELECT
FROM
WHERE
AND
X.NAME, X.GEHALT, Y.NAME
PERS X, PERS Y
X.MNR = Y.PNR
X.GEHALT > Y.GEHALT;
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
30
SQL
Hierarchische Beziehungen
g
auf einer Relation
•
Erklärung der Auswertung der Formel
X.MNR = Y.PNR AND X.GEHALT > Y.GEHALT
in Anfrage B am Beispiel
PERS
PNR
NAME GEH. MNR
PERS
PNR
NAME GEH. MNR
406
Abel
50 K
829
406
Abel
50 K
829
123
Maier
60 K
829
123
Maier
60 K
829
829
Müller
55 K
574
829
Müller
55 K
574
574
May
50 K
999
574
May
50 K
999
AUSGABE
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
X.NAME
X.GEHALT
Y.NAME
Maier
60 K
Müller
Müller
55 K
May
31
SQL
SELECT-Anweisungen
SELECT
Anweisungen – Erklärungsmodell
1. Die auszuwertenden Relationen werden
durch die FROM
FROM-Klausel
Klausel bestimmt.
• Alias-Namen erlauben die mehrfache Verwendung
derselben Relation.
• Das Kartesische Produkt aller Relationen der FROMKlausel wird gebildet.
2. Tupel werden durch die WHEREKlausel ausgewählt.
3. Qualifizierte Tupel werden gemäß der
GROUP-BY-Klausel in Gruppen
eingeteilt.
i
il
4. Gruppen werden ausgewählt, wenn sie
• Das Prädikat in der HAVING-Klausel darf sich nur auf
Gruppeneigenschaften beziehen: Attribute der GROUPBY-Klausel
BY
Klausel oder Anwendung von Aggregat
Aggregat-Funktionen
Funktionen.
5. Die Ausgabe wird durch die Auswertung
• Ist eine GROUP-BY-Klausel spezifiziert, dürfen als
SELECT-Elemente nur Ausdrücke aufgeführt werden,
die für die gesamte Gruppe genau einen Wert ergeben:
A ib
Attribute
der
d GROUP-BY-Klausel
GROUP BY Kl
l oder
d Anwendung
A
d
von
Aggregat-Funktionen.
6. Die Ausgabereihenfolge wird gemäß der
• Ist keine ORDER-BY-Klausel angegeben, ist die
Ausgabereihenfolge systembestimmt (nicht
deterministisch).
die HAVING-Klausel erfüllen.
der SELECT-Klausel abgeleitet.
ORDER-BY-Klausel hergestellt
hergestellt.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
32
SQL
Erklärungsmodell – Beispiel
R
FROM R
R‘
WHERE B <= 50
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
A
B
C
Rot
Rot
Gelb
Rot
Gelb
Blau
Blau
Blau
10
20
10
10
80
10
80
20
10
10
50
20
180
10
10
200
A
B
C
Rot
Rot
Gelb
Rot
Gelb
Blau
Blau
Blau
10
20
10
10
80
10
80
20
10
10
50
20
180
10
10
200
33
SQL
Erklärungsmodell – Beispiel
R‘‘
GROUP BY A
R‘‘
HAVING MAX(C)>100
SELECT A, SUM(B), 12
ORDER BY A
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
R‘‘‘‘
R‘‘‘‘‘
A
B
C
Rot
Rot
Rot
Gelb
Blau
Blau
10
20
10
10
10
20
10
10
20
50
10
200
A
B
C
Rot
Rot
Rot
Gelb
Blau
Blau
10
20
10
10
10
20
10
10
20
50
10
200
A
SUM(B)
12
Blau
30
12
A
SUM(B)
12
Blau
30
12
34
SQL
Erklärungsmodell – weitere Beispiele
PERS
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
PNR
ANR
GEH
BONUS
ALTER
0815
K45
80K
0
52
4711
K45
30K
1
42
1111
K45
50K
2
43
1234
K56
40K
3
31
7777
K56
80K
3
45
0007
K56
20K
3
41
ANR,, SUM(GEH)
(
)
PERS
BONUS <> 0
ANR
((COUNT(*)
( ) > 1))
ANR DESC
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
ANR
K56
K45
SUM(GEH)
140K
80K
35
SQL
Erklärungsmodell – weitere Beispiele
PERS
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
PNR
ANR
GEH
BONUS
ALTER
0815
K45
80K
0
52
4711
K45
30K
1
42
1111
K45
50K
2
43
1234
K56
40K
3
31
7777
K56
80K
3
45
0007
K56
20K
3
41
ANR,, SUM(GEH)
(
)
PERS
BONUS <> 0
ANR
((COUNT(DISTINCT
(
BONUS)) > 1))
ANR DESC
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
ANR
K45
SUM(GEH)
80K
36
SQL
Erklärungsmodell – weitere Beispiele
PERS
PNR
ANR
GEH
BONUS
ALTER
0815
K45
80K
0
52
4711
K45
30K
1
42
1111
K45
50K
2
43
1234
K56
40K
3
31
7777
K56
80K
3
45
0007
K56
20K
3
41
Die Summe der Gehälter pro Abteilung, in der mindestens ein Mitarbeiter 40
Jahre oder älter ist, soll berechnet werden:
SELECT
FROM
WHERE
GROUP BY
HAVING
ANR, SUM(GEH)
PERS
ALTER >= 40
ANR
(COUNT(*) >= 1)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
ANR
K45
K56
SUM(GEH)
160K
100K
37
SQL
Erklärungsmodell – weitere Beispiele
PERS
PNR
ANR
GEH
BONUS
ALTER
0815
K45
80K
0
52
4711
K45
30K
1
42
1111
K45
50K
2
43
1234
K56
40K
3
31
7777
K56
80K
3
45
0007
K56
20K
3
41
Die Summe der Gehälter pro Abteilung, in der mindestens ein Mitarbeiter 40
Jahre oder älter ist,
ist soll berechnet werden:
SELECT
FROM
GROUP BY
HAVING
ANR, SUM(GEH)
PERS
ANR
(MAX(ALTER) >= 40)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
ANR
SUM(GEH)
K45
K56
160K
140K
38
SQL
Suchbedingungen (Übersicht)
•
•
•
Sammlung
g (elementarer)
(
) Prädikate
Verknüpfung mit AND, OR, NOT
Ggf. Bestimmung der Auswertungsreihenfolge durch Klammerung
Nicht-quantifizierte Prädikate:
• Vergleichsprädikate
• BETWEEN-Prädikate
• IN-Prädikate
• Ähnlichkeitssuche
• Prädikate über Nullwerten
Quantifizierte Prädikate:
• ALL
• ANY,
ANY SOME
SOME, EXISTS
Weitere Prädikate:
• MATCH-Prädikat
• UNIQUE-Prädikat
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
comparison-cond ::= row-constructor
Θ row-constructor
row-constructor ::= scalar-exp |
(scalar-exp-commalist) |
(table-exp)
Beispiel: GEHALT BETWEEN 80K AND 100K
39
SQL
IN-Prädikate
IN
Prädikate
row-constr [[NOT]] IN ((table-exp)
p)
•
•
•
x IN (a, b, . . ., z)
row-constr
row
constr IN (table
(table-exp)
exp)
x NOT IN erg
•
Beispiel:
⇔ x = a OR x = b . . . OR x = z
⇔ row
row-constr
constr = ANY (table
(table-exp)
exp)
⇔ NOT (x IN erg)
Finde die Namen der Schauspieler, die den Faust gespielt haben.
SELECT S.NAME
SCHAUSPIELER S
FROM
WHERE ’Faust’ IN
(SELECT D.FIGUR
D FIGUR
DARSTELLER D
FROM
WHERE D.PNR = S.PNR)
SELECT S.NAME
FROM SCHAUSPIELER S
WHERE S.PNR IN
(SELECT D.PNR
D PNR
DARSTELLER D
FROM
WHERE D.FIGUR = ’Faust’)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
SELECT S.NAME
SCHAUSPIELER S,
FROM
DARSTELLER D
S PNR = D
D.PNR
PNR AND
WHERE S.PNR
D.FIGUR = ‘Faust”
40
SQL
Ähnlichkeitssuche
•
•
Unterstützung der Suche nach Objekten, von denen nur Teile des Inhalts
bek nnt sind
bekannt
ind oder
ode die einem vorgegebenen
o gegebenen Suchkriterium
S hk ite i m möglichst
mögli h t nahe
n he
kommen.
Klassen:
ƒ Syntaktische Ähnlichkeitssuche
Ä
(LIKE-Prädikat)
ƒ Phonetische Ähnlichkeit (spezielle DBS)
ƒ Semantische Ähnlichkeit (benutzerdefinierte Funktionen)
•
LIKE-Prädikat
char-string-exp [ NOT ] LIKE char-string-exp [ ESCAPE char-string-exp ]
ƒ Unscharfe Suche: LIKE-Prädikat
LIKE Prädikat vergleicht einen Datenwert mit
einem „Muster“ bzw. einer „Maske“
ƒ Das LIKE-Prädikat ist TRUE, wenn der entsprechende Datenwert der
Maske mit zulässigen Substitutionen von Zeichen für % und _
entspricht
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
41
SQL
Ähnlichkeitssuche
•
•
LIKE-Prädikat: Beispiele
ƒ NAME LIKE ’%SCHMI%’
wird z. B. erfüllt von ‘H.-W. SCHMITT’, ‘SCHMITT, H.-W.’,
‘BAUSCHMIED’, ‘SCHMITZ’
ƒ ANR LIKE ’_7%’
wird erfüllt von Abteilungen mit einer 7 als zweitem Zeichen
ƒ NAME NOT LIKE ’%-%’
wird erfüllt von allen Namen ohne Bindestrich
ƒ Suche nach ‘%’ und ‘_’ durch Voranstellen eines Escape-Zeichens
möglich:
g
STRING LIKE ’%\_%’ ESCAPE ’\’
wird erfüllt von STRING-Werten mit Unterstrich
SIMILAR-Prädikat in SQL:1999
Q
ƒ erlaubt die Nutzung von regulären Ausdrücken zum Maskenaufbau
ƒ Beispiel:
NAME SIMILAR TO ‘(SQL-(86
(SQL-(86 | 89 | 92 | 99)) | (SQL(1 | 2 | 3))
3))’
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
42
SQL
Prädikate über Nullwerten
•
•
•
Attributspezifikation:
ƒ Es kann für jedes Attribut festgelegt werden, ob NULL-Werte
zugelassen sind oder nicht
Verschiedene Bedeutungen von Nullwerten:
ƒ Datenwert ist momentan nicht bekannt
ƒ Attributwert existiert nicht für ein Tupel
Auswertung von booleschen Ausdrücken mit 3-wertiger Logik:
NOT
T
F
?
•
•
AND T F ?
F
T
?
T
F
?
T F ?
F F F
? F ?
OR T F ?
T
F
?
T T T
T F ?
T ? ?
Elementares Prädikat wird zu UNKNOWN (?) ausgewertet,
falls Nullwert vorliegt
nach vollständiger Auswertung einer WHERE-Klausel wird das Ergebnis ?
wie FALSE behandelt
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
43
SQL
Prädikate über Nullwerten
•
Beispiele:
PERS
PNR
0815
4711
1111
1234
7777
ANR
K45
K45
K45
K56
K56
GEH
80K
30K
20K
80K
ƒ GEH > PROV:
ƒ GEH > 70K AND PROV > 50K:
ƒ GEH > 70K OR PROV > 50K:
PROV
50K
100K
0815: ?,
0815: ?,
0815: T,
1111: ?,
1111: F,
1111: ?,
•
Test auf Nullwert: row-constr IS [[NOT]] NULL
•
Beispiel:
1234: ?
1234: ?
1234: ?
SELECT PNR, PNAME
FROM PERS
WHERE GEHALT IS NULL;
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
44
SQL
Weiteres zu Nullwerten
•
Eine arithmetische Operation
p
(+,
( , -,, *,, /) mit einem NULL-Wert
führt zu einem NULL-Wert
SELECT PNR, GEH + PROV
FROM PERS:
•
•
PERS
PNR
0815
4711
1111
1234
7777
ANR
K45
K45
K45
K56
K56
GEH
80K
30K
20K
80K
0815: ?,,
4711: 80K,
...
Verbund
ƒ Tupel mit NULL-Werten im Verbundattribut nehmen nicht am
Verbund teil
PROV
50K
100K
Achtung:
ƒ Im Allgemeinen ist AVG (GEH) <> SUM (GEH) / COUNT (PNR)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
45
SQL
Quantifizierung
•
ALL-, SOME- und ANY-Prädikate
row-constr Θ { ALL | ANY | SOME} (table-exp)
ƒ Θ ALL: Prädikat wird zu „true“ ausgewertet, wenn der Θ-Vergleich für alle
Ergebniswerte von table-exp „true“ ist
ƒ Θ ANY / Θ SOME: analog, wenn der Θ-Vergleich für mindestens einen
Ergebniswert „true“ ist
•
Existenztests
[NOT] EXISTS (table-exp)
ƒ Das Prädikat wird zu „false“ ausgewertet, wenn table-exp eine leere Menge
liefert, sonst zu „true“
EXISTS-Kontext
Kontext darf table
table-exp
exp mit (SELECT * ...) spezifiziert werden
ƒ Im EXISTS
(Normalfall)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
46
SQL
Quantifizierung
•
Semantik
ƒ x Θ ANY (SELECT y FROM T WHERE p) ⇔
EXISTS (SELECT * FROM T WHERE (p) AND x Θ T.y)
ƒ x Θ ALL (SELECT y FROM T WHERE p) ⇔
NOT EXISTS (SELECT * FROM T WHERE (p) AND NOT (x Θ T.y))
•
Beispiel:
Finde die Manager, die mehr verdienen als alle ihre direkten Untergebenen
SELECT
FROM
WHERE
M.PNR
PERS M
M.GEHALT > ALL
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
(
(SELECT
P.GEHALT
FROM PERS P
WHERE P.MNR = M.PNR)
47
SQL
Quantifizierung
•
Beispiel:
Finde die Namen der Schauspieler, die mindestens einmal gespielt haben
(... nie gespielt haben)
SELECT
FROM
WHERE
SP.NAME
SCHAUSPIELER SP
(NOT) EXISTS (SELECT *
FROM DARSTELLER DA
WHERE DA.PNR = SP.PNR)
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
48
SQL
Quantifizierung
•
Beispiel:
Finde die Namen aller Schauspieler, die alle Rollen gespielt haben.
SELECT S.NAME
FROM
SCHAUSPIELER S
WHERE NOT EXISTS
(SELECT *
FROM
O
ROLLE
O
R
WHERE NOT EXISTS
(SELECT *
FROM DARSTELLER D
WHERE D.PNR = S.PNR
AND D.FIGUR = R.FIGUR))
Andere Formulierung:
Finde die Namen der Schauspieler, so dass keine Rolle „existiert“, die sie
nicht gespielt haben
haben.
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
49
SQL
Anfragemöglichkeiten
•
Es gibt meist viele Möglichkeiten!
•
B i i l
Beispiel:
Schema: station (snr, name, ...); wettert (datum, snr, mintemp, ...)
Anfrage: Finde die Messstation mit der niedrigsten gemessenen Temperatur
ƒ Gute Lösung: (Aggregat-Funktion in Subquery)
SELECT s.name FROM station s, wettert w
WHERE s.snr=w.snr AND w.mintemp=
p ( SELECT MIN(ww.mintemp)
(
p)
FROM wettert ww);
ƒ Schlechte Lösung: Keine Joins
SELECT name FROM station WHERE snr=(
SELECT DISTINCT snr FROM wettert WHERE mintemp=(
SELECT MIN(mintemp) FROM wettert));
ƒ Naja, worst case?!: Keine Aggregat-Funktion
SELECT DISTINCT name FROM station
WHERE snr IN (SELECT W1.snr FROM wettert W1
WHERE NOT EXISTS (SELECT * FROM wettert W2
WHERE W2.mintemp < W1.mintemp));
© Prof. Dr. B. Mitschang, Dr. H. Schwarz, Universität Stuttgart
50