Professoren PersNr Name Rang Raum

Werbung
Kapitel 4
Prof. Dr. Steffen Staab
Dr. Dr. Sergej Sizov
SQL: Grundlagen
ISWeb –
Information Systems & Semantic Web
Lernziele
Kenntnis der Grundkonzepte von SQL
Fähigkeit zur praktischen Anwendung von
einfachen SQL-Anweisungen
 Schema-Definitionen einschl. referentieller Integrität
 mengenorientierte Anfragen deskriptiver Art (SELECT)
 Änderungsoperationen
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
2
SQL: Komponenten der vollständigen DB-Sprache
Datenmanipulation
 Einfügen, Löschen und Ändern von individuellen Tupeln
und von Mengen von Tupeln
 Zuweisung von ganzen Relationen
 Beispiel: Einfügen von Tüpeln über eine SELECT-Anweisung
 Beispiel: Löschen ohne WHERE-Klausel löscht gesamte Relation
Datendefinition
 Definition von Wertebereichen, Attributen und Relationen
 Definition von verschiedenen Sichten auf Relationen
Datenkontrolle
 Spezifikation von Bedingungen zur Zugriffskontrolle
 Spezifikation von Zusicherungen (assertions) zur semantischen
Integritätskontrolle
Datenabfragen
 Abbildung von Eingaberelationen durch Auswertung von Bedingungen
auf die Ergebnisrelation
Kopplung mit einer Wirtssprache (z.B. PHP, PLSQL, …)
 deskriptive Auswahl von Mengen von Tupeln
 sukzessive Bereitstellung einzelner Tupeln
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
3
Beispiel: Universitätsschema
*
Studenten
MatrNr
Name
Semester
hören
*
*
voraussetzen
Vorlesungen
*
VorlNr
Titel
SWS
*
*
lesen
Prüfen
Note
1
1
Professoren
PersNr
Name
Rang
Raum
Grundlagen der Datenbanken, WS 08/09
Assistenten
arbeitenFür
1
Kapitel 4: Grundlagen von SQL
*
PersNr
Name
Fachgebiet
4
Studenten
Professoren
Rang Raum MatrNr
Vorlesungen
Name
Semester
24002
Xenokrates
18
232
25403
Jonas
2127 Kopernikus C3
310
26120
2133
C3
52
26830
2134 Augustinus C3
309
2136
Curie
C4
2137
Kant
C4
PersNr
Name
2125
Sokrates
C4
226
2126
Russel
C4
Popper
VorlNr
Titel
12
5001
Grundzüge
4
2137
Fichte
10
5041
Ethik
4
2125
Aristoxenos
8
5043
Erkenntnistheorie
3
2126
27550 Schopenhauer
6
5049
Mäeutik
2
2125
36
28106
3
4052
Logik
4
2125
7
29120 Theophrastos
2
5052
Wissenschaftstheori
e
3
2126
29555
2
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
voraussetzen
Vorgänger Nachfolger
Carnap
Feuerbach
hören
5001
5041
MatrNr
VorlNr
5001
5043
26120
5001
5001
5049
27550
5001
5041
5216
27550
4052
5043
5052
28106
5041
5041
5052
28106
5052
5052
5259
28106
5216
prüfen
29120
5001
MatrNr VorlNr PersNr Note
29120
5041
28106
5001
2126
1
29120
5049
25403
5041
2125
2
25403
5022
Grundlagen der Datenbanken, WS 08/09
SWS gelesenV
on
Assistenten
PerslNr
Name
Fachgebiet
Boss
3002
Platon
Ideenlehre
2125
3003
Aristoteles
Syllogistik
2125
3004
Wittgenstein
Sprachtheorie
2126
3005
Rhetikus
Planetenbewegung
2127
3006
Newton
Keplersche Gesetze
2127
3007
Spinoza
Gott und Natur
2126
Kapitel 4: Grundlagen von SQL
5
Relationale Invarianten
Integritätsbedingungen des Relationenmodells
Primärschlüsselbedingung (Entity-Integrität)
 Eindeutigkeit des Primärschlüssels
 keine Nullwerte!
Fremdschlüsselbedingung (Referentielle Integrität)
 Darstellung von Beziehungen durch Fremdschlüssel (foreign key)
 Attribut, das in Bezug auf den Primärschlüssel einer anderen
(oder derselben) Relation definiert ist
 zugehöriger Primärschlüssel muss existieren, d.h. zu jedem Wert
(ungleich Null) eines Fremdschlüsselattributs einer Relation R2
muss ein gleicher Wert des Primärschlüssels in irgendeinem Tupel von
Relation R1 vorhanden sein
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
6
Relationale Invarianten (1)
Fremdschlüssel und zugehöriger Primärschlüssel
tragen wichtige interrelationale Informationen
 sie sind auf dem gleichen Wertebereich definiert
 sie gestatten die Verknüpfung von Relationen mit Hilfe von
Relationenoperationen
Fremdschlüssel
 können Nullwerte aufweisen, wenn sie nicht Teil
eines Primärschlüssels sind.
 ein Fremdschlüssel ist „zusammengesetzt“, wenn der zugehörige
Primärschlüssel „zusammengesetzt“ ist
Eine Relation kann mehrere Fremdschlüssel besitzen, die die
gleiche oder verschiedene Relationen referenzieren
Zyklen sind möglich (geschlossener referentieller Pfad)
Eine Relation kann zugleich referenzierende und referenzierte
Relation sein („self-referencing table“).
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
7
SQL: DDL Anweisungen
DDL-Spezifikation in SQL bei CREATE TABLE:
Assistenten
Professoren
PersNr
Name
Fachgebiet
Boss
PersNr
Name
Rang
Raum
3002
Platon
Ideenlehre
2125
2125
Sokrates
C4
226
3003
Aristoteles
Syllogistik
2125
2126
Russel
C4
232
3004
Wittgenstein
Sprachtheorie
2126
2127
Kopernikus
C3
310
3005
Rhetikus
Planetenbewegung
2127
2133
Popper
C3
52
3006
Newton
Keplersche Gesetze
2127
2134
Augustinus
C3
309
3007
Spinoza
Gott und Natur
2126
2136
Curie
C4
36
2137
Kant
C4
7
CREATE TABLE Assistenten
(PersNr INT PRIMARY KEY,
Name VARCHAR2 (100) NOT NULL,
Fachgebiet VARCHAR2(100),
Boss INT
FOREIGN KEY (Boss) REFERENCES Professoren(PersNr)
ON DELETE SET NULL )
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
8
(Einfache) Datendefinition in SQL
Datentypen
character (n), char (n)
character varying (n), varchar (n)
numeric (precision, scale), integer (scale = 0)
Beispiel numeric: 123.45 (precision = 5, scale = 2)
blob oder raw für sehr große binäre Daten
clob für sehr große String-Attribute
date für Datumsangaben
xml für XML-Dokumente
…
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
9
Veränderung am Datenbestand
Einfügen von einzelnen Tupeln:
insert into Studenten (MatrNr, Name)
values (28121, 'Archimedes');
Einfügen von Tupeln aus einer SELECT-Anweisung heraus:
insert into hören
select MatrNr, VorlNr
from Studenten, Vorlesungen
where Titel= 'Logik' ;
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
10
Behandlung von Null-Werten
MatrNr
Studenten
Name
Semester
29120
29555
28121
Theophrastos
Feuerbach
Archimedes
2
2
-
Null-Wert
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
11
Auswertung bei Null-Werten
In arithmetischen Ausdrücken werden Nullwerte propagiert, d.h.
sobald ein Operand null ist, wird auch das Ergebnis null.
Dementsprechend wird z.B. null + 1 zu null ausgewertet-aber
auch null * 0 wird zu null ausgewertet.
SQL hat eine dreiwertige Logik, die nicht nur true und false
kennt, sondern auch einen dritten Wert unknown. Diesen
Wert liefern Vergleichsoperationen zurück, wenn mindestens
eines ihrer Argumente null ist. Beispielsweise wertet SQL das
Prädikat (PersNr=...) immer zu unknown aus, wenn die
PersNr des betreffenden Tupels den Wert null hat.
Logische Ausdrücke werden nach den folgenden Tabellen
berechnet:
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
12
not
true
false
unknown unknown
false
true
and
true
unknown
false
true
true
unknown
false
unknown
unknown
unknown
false
false
false
false
false
or
true
unknown
false
true
true
true
true
unknown
true
unknown
unknown
false
true
unknown
false
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
13
Veränderungen am Datenbestand
Löschen von Tupeln
delete Studenten
where Semester > 13;
Verändern von Tupeln
update Studenten
set Semester = Semester + 1;
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
14
Einfache SQL-Anfrage
select
PersNr, Name
from
Professoren
where
Rang= 'C4';
Professoren
PersNr
Name
Rang
Raum
2125
Sokrates
C4
226
2126
Russel
C4
232
2127
Kopernikus
C3
310
2133
Popper
C3
52
2134
Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Grundlagen der Datenbanken, WS 08/09
PersNr
2125
2126
Name
Sokrates
Russel
2136
2137
Curie
Kant
Kapitel 4: Grundlagen von SQL
15
Einfache SQL-Anfragen
Sortierung
select PersNr, Name, Rang
from Professoren
order by
Rang desc, Name asc;
Professoren
PersNr
Name
Rang
Raum
2125
Sokrates
C4
226
2126
Russel
C4
232
2127
Kopernikus
C3
310
2133
Popper
C3
52
2134
Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Grundlagen der Datenbanken, WS 08/09
PersNr
2136
2137
2126
2125
2134
2127
2133
Kapitel 4: Grundlagen von SQL
Name
Curie
Kant
Russel
Sokrates
Augustinus
Kopernikus
Popper
Rang
C4
C4
C4
C4
C3
C3
C3
16
Duplikateliminierung
select distinct Rang
from Professoren
Rang
C3
C4
Professoren
PersNr
Name
Rang
Raum
2125
Sokrates
C4
226
2126
Russel
C4
232
2127
Kopernikus
C3
310
2133
Popper
C3
52
2134
Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
17
Anfragen über mehrere Relationen
Studenten
Professoren
Rang Raum MatrNr
Vorlesungen
Name
Semester
24002
Xenokrates
18
232
25403
Jonas
2127 Kopernikus C3
310
26120
2133
C3
52
26830
2134 Augustinus C3
309
2136
Curie
C4
2137
Kant
C4
PersNr
Name
2125
Sokrates
C4
226
2126
Russel
C4
Popper
VorlNr
Titel
12
5001
Grundzüge
4
2137
Fichte
10
5041
Ethik
4
2125
Aristoxenos
8
5043
Erkenntnistheorie
3
2126
27550 Schopenhauer
6
5049
Mäeutik
2
2125
36
28106
3
4052
Logik
4
2125
7
29120 Theophrastos
2
5052
Wissenschaftstheori
e
3
2126
29555
2
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
voraussetzen
Carnap
Feuerbach
hören
Vorgänger Nachfolger
SWS gelesenV
on
5001
5041
MatrNr
VorlNr
5022
Glaube und Wissen
2
2134
5001
5043
26120
5001
4630
Die 3 Kritiken
4
2137
5001
5049
27550
5001
5041
5216
27550
4052
5043
5052
28106
5041
5041
5052
28106
5052
PerslNr
Name
Fachgebiet
Boss
5052
5259
28106
5216
3002
Platon
Ideenlehre
2125
28106
5259
3003
Aristoteles
Syllogistik
2125
prüfen
29120
5001
3004
Wittgenstein
Sprachtheorie
2126
MatrNr VorlNr PersNr Note
29120
5041
3005
Rhetikus
Planetenbewegung
2127
28106
29120
5049
3006
Newton
Keplersche Gesetze
2127
5001
2126
1
Assistenten
Anfragen über mehrere Relationen
Welcher Professor liest "Mäeutik"?
select Name, Titel
from Professoren, Vorlesungen
where PersNr = gelesenVon and Titel = `Mäeutik‘ ;
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
19
Anfragen über mehrere Relationen
Professoren
PersNr Name
Rang Raum
2125 Sokrates C4
226
2126
Russel
C4
232
2137
Kant
C4
7
VorlNr
5001
5041
Vorlesungen
Titel
SWS gelesen Von
Grundzüge
4
2137
Ethik
4
2125
5049
Mäeutik
4630 Die 3 Kritiken
2
2125
4
2137
Verknüpfung X
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
20
PersNr
Name
Rang
Raum
VorlNr
Titel
SWS
gelesen Von
2125
Sokrates
C4
226
5001
Grundzüge
4
2137
1225
Sokrates
C4
226
5041
Ethik
4
2125
2125
Sokrates
C4
226
5049
Mäeutik
2
2125
2126
Russel
C4
232
5001
Grundzüge
4
2137
2126
Russel
C4
232
5041
Ethik
4
2125
2137
Kant
C4
7
4630
Die 3 Kritiken
4
2137
Selektion
PersNr
Name
Rang
Raum
VorlNr
Titel
SWS
gelesen Von
2125
Sokrates
C4
226
5049
Mäeutik
2
2125
Projektion
Grundlagen der Datenbanken, WS 08/09
Name
Titel
Sokrates
Mäeutik
Kapitel 4: Grundlagen von SQL
21
Anfragen über mehrere Relationen
Welche Studenten hören welche Vorlesungen?
select Name, Titel
from Studenten, hören, Vorlesungen
where Studenten.MatrNr = hören.MatrNr and
hören.VorlNr = Vorlesungen.VorlNr;
Alternativ:
select s.Name, v.Titel
from Studenten s, hören h, Vorlesungen v
where s. MatrNr = h. MatrNr and
h.VorlNr = v.VorlNr
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
22
Was macht folgende Anfrage?
SELECT
s1.Name, s2.Name
FROM
Studenten s1, hoeren h1, hoeren h2, Studenten s2
WHERE
h1.VorlNr = h2.VorlNr AND
hören
h1.MatrNr = s1.MatrNr AND
MatrNr
VorlNr
26120
5001
h2.MatrNr = s2.MatrNr
Studenten
27550
5001
27550
4052
28106
5041
28106
5052
MatrNr
Name
Semester
24002
Xenokrates
18
25403
Jonas
12
28106
5216
26120
Fichte
10
28106
5259
26830
Aristoxenos
8
29120
5001
27550
Schopenhauer
6
29120
5041
28106
Carnap
3
29120
5049
29120
Theophrastos
2
29555
5022
29555
Feuerbach
2
25403
5022
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
23
Mengenoperationen
Mengenoperationen union, intersect, minus
select Name
from Assistenten
UNION
select Name
from Professoren
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
25
Mengenoperationen: Beispiel UNION
Große Mengenoperationen können effizient
durchgeführt werden.
SELECT * FROM test.soifs, test.urludk
WHERE test.urludk.udk = :variable1 AND
test.soifs.seq = test.urludk.seq AND
test.urludk.sicherheit = 10
UNION ALL
SELECT * FROM test.soifs, test.urludk
WHERE test.urludk.udk = :variable1 AND
test.soifs.seq = test.urludk.seq
AND test.urludk.sicherheit = 9
UNION ALL
SELECT * …
Grundlagen der Datenbanken, WS 08/09
Kapitel 2: Daten- und Informationsmodellierung
26
Geschachtelte Anfragen: Was passiert hier?
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
select p.Name
from Professoren p
where not exists ( select v gelesenVon
from Vorlesungen v
where v.gelesenVon = p.PersNr );
Grundlagen der Datenbanken, WS 08/09
Kapitel 4: Grundlagen von SQL
27
Herunterladen