6 SQL – Structured Query Language

6 SQL – Structured Query Language
Schestag
6.1
Datendefinition – DDL
6.2
Die Relationen-Algebra und Datenmanipulation – DML
6.2.1 Update-Operatoren Insert, Update, Delete
6.2.2 Der Retrieval-Operator Select
6.2.3 JOIN-Strategien eines Optimizers
6.3
Der Systemkatalog
Datenbanken I (Bachelor)
fbi
h_da
Kapitel 6 - 1
fbi
h_da
6 SQL – Structured Query Language (1)
•
Auf der Basis eines physischen Datenmodells (Relationenmodells) kann mit Hilfe
einer entsprechenden Data-SubLangauge (DSL) die Struktur einer Datenbank
definiert werden.
•
Im relationalen Datenmodell bietet sich die Darstellung der Implementierung von
Relationen als Tabellen (tables) mit einer festen Anzahl von Spalten (columns) an,
da die erste Codd‘sche Normalform „gleiche Satzlänge“ für jede Ausprägung einer
Entität (und somit auch einer Tabelle) fordert.
vom physischen
Datenmodell ...
Mitarbeiter
PNR
Name
Vorname
GebDat
Gehalt
INTEGER
<pk>
VARCHAR2(20)
VARCHAR2(20)
DATE
NUMBER(8,2)
Mitarbeiter-Tabelle
PNR Name
10
12
13
...
Schmitt
Meyer
Lang
...
Vorname GebDat Gehalt
Otto
Werner
Sonja
...
2.10.59
12.5.65
8.11.63
...
150000
120000
130000
...
... zur Darstellung der Daten in der
relationalen Datenbank
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 2
fbi
h_da
6 SQL – Structured Query Language (2)
•
Die mathematische Basis des relationalen Datenmodells ist die Relationen-Algebra
- auf dieser Basis konnte E. F. Codd Anfang der 70er Jahre eine Data-Sublanguage
definieren, mit deren Hilfe man
– die Struktur einer Datenbank definieren (DDL - Data Definition Language),
– auf die Daten manipulierend und recherchierend zugreifen konnte (DML - Data
Manipulation Language – vgl. auch 6.2).
•
Die klare mathematische Struktur dieser Sprache ermöglichte die relativ schnelle
Definition eines Standards, der Structured Query Language - SQL.
•
SQL ist eine interpretierte Sprache. Sie verfügt über keinerlei Kontrollstrukturen
und stellt in ihrer DML-Syntax ausschließlich mengenorientierte Operatoren zur
Verfügung.
Die Einbettung von SQL in höhere Programmiersprachen ist i.d.R. erforderlich, um
Datenbankinhalte dynamisch in Applikationen zu integrieren. Die hierfür
erforderlichen Sprachkomponenten sind im Standard unter dem Begriff Embedded
SQL (ESQL) definiert (vgl. Kapitel 7 und 8).
•
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 3
fbi
h_da
6 SQL – ein Überblick über die historische Entwicklung
1969 / 70
E. F. Codd: „A Relational Model of Data for Large Shared Data Banks“
1974
SEQUEL als Vorläufer von SQL wird definiert
1975 / 79
SYSTEM R (IBM) als Prototyp wird entwickelt
1986 / 87
SQL-86 ANSI/ISO Standard (erste genormte Version von SQL)
X/OPEN Standard für UNIX Systeme
IBM SAA Standard
1989
SQL-89
1992
SQL-92 (entspricht SQL2)
mit 3 Leveln (Entry Level, Intermediate Level, Full Level)
1999
SQL:1999 (SQL3)
2003
SQL:2003 (SQL4)
•
•
SQL ist heute „de-facto“ Standard in der relationalen Welt
Produkte unterstützen meist (nur) SQL-92/Intermediate Level und Teile von
SQL:1999 bzw. SQL:2003
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 4
6.1 SQL – Datendefinition DDL (1)
•
fbi
h_da
Zur Verwaltung von Strukturelementen einer Datenbank stehen in der DDL von SQL
die folgenden Operatoren zur Verfügung *):
CREATE Generierung eines Strukturelementes
•
•
ALTER
Ändern eines Strukturelementes
DROP
Löschen eines Strukturelementes
Im Systemkatalog werden alle Strukturinformationen sowie statistische
Informationen zu den Daten verwaltet.
Der Systemkatalog selbst hat ebenfalls die Struktur einer relationalen Datenbank. So
gibt es z.B. eine Systemtabelle, in der Informationen zu allen Tabellen der DB
enthalten sind, eine Systemtabelle mit Informationen zu allen Spalten etc.
*) SQL ist nicht case sensitive, d.h. unterscheidet nicht zwischen Groß- und Kleinschreibung. Aus didaktischen Gründen
werden SQL-Operatoren im Skript oft in Großbuchstaben geschrieben.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 5
fbi
h_da
6.1 SQL – Datendefinition DDL (2)
•
Die Generierung des logischen Datenbanknamens (des Schema-Namens) und
Zuweisung des verfügbaren Plattenbereiches erfolgt mit der CREATE DATABASE
... - Anweisung.
•
Hierbei wir der logische Namen der Datenbank auf vorgegebenem physischen
Plattenbereich mit vorgegebener aber erweiterbarer Größe festgelegt, z.B.:
create database <Datenbank>
datafile <Dateiangabe>{,<Dateiangabe>}
character set <Zeichensatz>
national character set <NatZeichensatz>
...;
•
Die CREATE DATABASE-Anweisung wird in der Regel vom DBA ausgeführt. Sie ist
in der Syntax der verwendeten Klauseln und Parameter von System zu System oft
unterschiedlich.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 6
6.1 SQL – Datendefinition DDL (3)
•
fbi
h_da
Zur Generierung von Tabellen, die als Strukturelemente wiederum Spalten enthalten,
benötigt man die skalaren Datentypen und die Möglichkeit, ggf. spezifische
Wertebereiche zu definieren:
6.1.1 Skalare Datentypen
• Für die Generierung von Tabellen und deren Spaltenelementen stehen in SQL
verschiedene skalare Datentypen zur Verfügung, die je nach Datenbanksystem
unterschiedliche Bezeichner haben können und im Prinzip den Standard-Datentypen
höherer Programmiersprachen entsprechen.
6.1.2 Domains (Wertebereiche) (in Oracle “Type” ab Version 8)
• Aus Basis der skalaren Datentypen können eingeschränkte Domains /
Wertebereiche definiert werden, die zur Sicherung von Wertebereichsintegritäten
dienen:
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 7
fbi
h_da
6.1 SQL – Datendefinition DDL (4)
•
Domains (Wertebereiche) (Fortsetzung)
Syntax:
CREATE DOMAIN <Domain_Name> < Datentyp >
[DEFAULT <...>]
[CONSTRAINT <Constraint_Name>
CHECK (VALUE in (<...>,) ] ;
•
Beispiel:
create domain Fachbereich varchar(20)
default 'Informatik'
check (value in ('Informatik','Mathematik','Elektrotechnik'));
•
Ein DOMAIN ist eine Menge erlaubter Werte eines Datentyps. Beim CREATE
TABLE (s.u.) kann der DOMAIN-Name an Stelle eines skalaren Datentyps
angegeben werden. Optional können auch DEFAULT-Werte zugewiesen werden.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 8
6.1 SQL – Datendefinition DDL (5)
•
Domains (Wertebereiche) (Fortsetzung)
•
Ein DOMAIN kann geändert und gelöscht werden:
ändern:
ALTER DOMAIN <Domain_Name>
DROP CONSTRAINT <Constraint_Name>
fbi
h_da
ALTER DOMAIN <Domain_Name>
ADD <Constraint_Name>
löschen:
DROP DOMAIN <Domain_Name> RESTRICTED
oder
DROP DOMAIN <Domain_Name> CASCADE
RESTRICTED: DROP wird nicht ausgeführt, wenn der DOMAIN an
mindestens einer Stelle benutzt wird.
CASCADE: DROP wird immer ausgeführt; statt des DOMAIN erhält
die Spalte dann z.B. den zugrundeliegenden Datentyp des Domain
ohne Constraints.
•
Der grundlegende Datentyp einer Spalte kann jedoch nicht verändert werden.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 9
fbi
h_da
6.1 SQL – Datendefinition DDL (6)
6.1.3 Tables
Syntax:
CREATE TABLE <Table_Name> (<Table_Element>,) ;
•
Table_Elemente sind
- <Columnn_Namen> [DEFAULT_Definition], oder
- <Table_Constraint_Definitionen>
•
Table_Constraint_Definitionen sind
- candidate key - Definitionen, oder
- foerign key - Definitionen, oder
- “check constraint” - Definitionen.
•
candidate key constraint: UNIQUE (<Columnn_Element>,)
•
primary key constraint: PRIMARY KEY (<Columnn_Element>,)
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 10
fbi
h_da
6.1 SQL – Datendefinition DDL (7)
•
Es empfiehlt sich (und viele DBMS fordern es auch), jede Zeile eindeutig über einen
PRIMARY KEY zu definieren.
⇒ In einer Tabelle gibt es höchstens einen PRIMARY KEY-Constraint, aber beliebig
viele UNIQUE - Constraints.
•
foreign key constraint:
CONSTRAINT <c-name> FOREIGN KEY (<Columnn_Element>,)
REFERENCES <Table_Name> [(<Columnn_Element>,)]
[ON DELETE <option>]
[ON UPDATE <option>]
wobei <option> sein kann
NO ACTION (entspricht dem default)
CASCADE
SET DEFAULT
SET NULL
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 11
fbi
h_da
6.1 SQL – Datendefinition DDL (8)
•
Ein foreign key constraint ist die Implementierung einer Relationship aus dem
logischen Datenmodell.
E1
PK1
A1
E2
E1.PK1=E2.PK1
PK2
PK1
create table E1
( PK1 INTEGER
not null,
A1 CHAR VARYING(20)
null,
constraint PK_E1 primary key (PK1)
);
create table E2
( PK2 INTEGER not null,
PK1 INTEGER not null,
constraint PK_E2 primary key (PK2),
constraint FK_E2_REL_E1
foreign key (PK1) references E1 (PK1)
);
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 12
fbi
h_da
6.1 SQL – Datendefinition DDL (9)
•
Constraints sind Prüfungen, die vom DBMS bei bestimmten DB-Operationen
durchgeführt werden. Für die meisten DBMS stehen im Rahmen der CREATE
TABLE-Anweisung primary key (PK)- und foreign key (FK)- constraints zur
Verfügung:
•
PK-Constraints prüfen bei einem Insert, ob der neue PK-Wert nicht bereits in der
Tabelle enthalten ist, d.h. dieser Constraint prüft die Eindeutigkeit des PK-Wertes.
•
FK-Constraints prüfen beim Insert und Update, ob die Referenz auf das
entsprechende PK-Element einer anderen Tabelle tatsächlich gewährleistet ist.
•
"check constraints": CHECK (<Constraint_Bedingung>)
Das Anlegen einer neuen Zeile in einer Tabelle wird nur ausgeführt, wenn die
Constraint-Bedingung den Rückgabewert “TRUE” liefert. Andernfalls wird der
Datensatz abgewiesen.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 13
fbi
h_da
6.1 SQL – Datendefinition DDL (10)
•
Eine Tabelle, die mit CREATE TABLE generiert wurde, nennt man auch Base Table.
•
Tables können geändert und gelöscht werden:
ändern:
• neue Spalte hinzufügen *):
ALTER TABLE <Table_Name>
ADD COLUMN <Column-Name, Column_Typ/Domain,[Default ...]>
•
Definition eines neuen default-Wertes für eine exisiterende Spalte
•
Löschen eines existierenden Spalten-defaults
•
Löschen einer existierenden Spalte
•
Definition eines neuen Table Integritäts-constraints
•
Löschen eines bestehenden Table Integritäts-constraints
*) Bei Oracle: alter table <table_name> add <Column-Name, Column_Typ/Domain,[Default ...]>;
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 14
fbi
h_da
6.1 SQL – Datendefinition DDL (11)
löschen:
• DROP TABLE <Table_Name> <option>
<option> = RESTRICTED:
DROP wird nur ausgeführt, wenn die Tabelle von keiner anderen referenziert
wird.
<option> = CASCADE*):
Die gesamte Tabelle TABLE und ihre Sichten (vgl. VIEW, s.u.) sowie die
FK-Constraints der referenzierenden Tabelle werden gelöscht.
*) Bei Oracle: drop table <table_name> cascade constraints;
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 15
fbi
h_da
6.1 SQL – Datendefinition DDL (12)
6.1.4 Indexe
Syntax:
CREATE INDEX <Index_Name>
ON <Table_Name> (<Col_1>,...,<Col_n>) [ASC|DESC];
•
Indexe sollten in der Regel erst dann eingerichtet werden, wenn eine Tabelle mit
Daten erstmalig bestückt wurde. Werden zunächst die Daten in index-lose Tabellen
eingefügt, so wird nicht durch jedes Einfügen eines Tupels eine Index-Aktualisierung
ausgelöst.
•
Ein Index kann nicht nur über eine einzelne Spalte, sondern auch über
Spaltenkombinationen angelegt werden.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 16
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (1)
•
Mit der DDL – dem Sprachumfang der Data Definition Language von SQL, werden
Metadaten über die implementierte Datenbank für den Systemkatalog definiert.
•
Mit der DML – dem Sprachumfang der Data Manipulation Language von SQL,
werden die Daten selbst verwaltet:
–
–
–
–
•
Daten einfügen
Daten ändern
Daten löschen
Daten lesen.
Die Definition der Operatoren von SQL – DML basiert auf der Theorie der
Relationen-Algebra, die auf den folgenden Folien kurz vorgestellt wird:
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 17
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (2)
•
Ziel: Basisoperationen auf Tabellen (Relationen) ausführen, die die Berechnung von
neuen Ergebnistabellen aus gespeicherten Datenbanktabellen erlauben, die als
Ergebnis angezeigt oder modifiziert werden können.
•
Die Operationen werden zur so genannten Relationenalgebra zusammengefasst.
In der Mathematik ist eine Algebra definiert durch einen Wertebereich sowie darauf
definierten Operationen.
⇒ Für Datenbankanfragen entsprechen die Inhalte der Datenbank den Werten,
Operationen sind dagegen Funktionen zum Berechnen der Anfrageergebnisse.
⇒ Anfrageoperationen sind beliebig kombinierbar und bilden eine Algebra zum
„Rechnen mit Tabellen (Relationen)“ – die so genannte relationale Algebra oder auch
Relationenalgebra.
⇒ SQL ist „lediglich“ eine Sprache, welche die Relationenalgebra praktisch umsetzt.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 18
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (3)
fbi
h_da
Operationen der Relationen-Algebra
•
Spalten heraussuchen: Projektion π
•
Zeilen heraussuchen: Selektion σ
•
Tabellen verknüpfen: Verbund (Join) ZY
•
Tabellen vereinigen: Vereinigung U
•
Tabellen voneinander abziehen: Differenz −
•
Spalten umbenennen: Umbenennung β
(wichtig für ZY, U, −)
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 19
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (4)
fbi
h_da
Operationen der Relationen-Algebra
•
Spalten heraussuchen: Projektion π
Auswahl von Spalten durch Angabe einer Attributliste
Syntax:
πAttributmenge(Relation)
Definition: πA(R ) := { t(A) | t ∈ R } mit Attributmenge A ⊆ R
Achtung: Die Projektion entfernt Duplikate (Mengensemantik):
•
Beispiele: πPNR, ANR(PERSON)
πANR(PERSON)
PNR ANR
Schestag
ANR
406
K55
K55
123
K51
K51
829
K53
K53
574
K55
Datenbanken I (Bachelor)
Kapitel 6 - 20
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (5)
fbi
h_da
Operationen der Relationen-Algebra
•
Zeilen heraussuchen: Selektion σ
Auswahl von Zeilen einer Tabelle anhand eines Selektionsprädikats
Syntax:
•
σBedingung(Relation)
Beispiel: σANR=‘K55‘(PERSON)
PERSON: PNR
ERGEBNIS:
Schestag
NAME
ALTER
GEHALT
ANR
406
Coy
47
50700
K55
123
Müller
32
43500
K51
829
Schmid
36
45200
K53
574
Abel
28
36000
K55
PNR
NAME
ALTER
GEHALT
ANR
406
Coy
47
50700
K55
574
Abel
28
36000
K55
Datenbanken I (Bachelor)
Kapitel 6 - 21
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (6)
Operationen der Relationen-Algebra
•
Kartesisches Produkt: K = R x S
Zusammenfügen von Zeilen unterschiedlicher Tabellen
Syntax:
K = R x S := { k | ∃ x ∈ R, y ∈ S: (k = <x1, x2, …, yr, y1, y2, …, ys>) }
ABT x PERSON: ANR
Schestag
ANAME
AORT
PNR
NAME
ALTER
GEHALT
ANR‘
K51
Planung
Darmstadt
406
Coy
47
50700
K55
K51
Planung
Darmstadt
123
Müller
32
43500
K51
K51
Planung
Darmstadt
829
Schmid
36
45200
K53
K51
Planung
Darmstadt
574
Abel
28
36000
K55
K53
Einkauf
Frankfurt
406
Coy
47
50700
K55
K53
Einkauf
Frankfurt
123
Müller
32
43500
K51
K53
Einkauf
Frankfurt
829
Schmid
36
45200
K53
K53
Einkauf
Frankfurt
574
Abel
28
36000
K55
K55
Vertrieb
Frankfurt
406
Coy
47
50700
K55
K55
Vertrieb
Frankfurt
123
Müller
32
43500
K51
…
…
…
…
…
…
…
…
Datenbanken I (Bachelor)
Kapitel 6 - 22
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (7)
Operationen der Relationen-Algebra
•
•
Tabellen verknüpfen: Verbund (Join) ZY
Zusammenfügen von Zeilen unterschiedlicher Tabellen
Ein Verbund ist ein kartesisches Produkt zwischen zwei Relationen R und S,
eingeschränkt durch eine Θ-Beziehung zwischen Attribut A von R und Attribut B von
S mit Θ ∈ {<, =, >, ≤, ≠, ≥ }
Syntax:
•
•
R ZY S = σAΘB(RxS)
Wichtigster Spezialfall: Θ = ‘=‘ (Gleichverbund – Equi Join)
Beispiel: ABT ZY PERSON
ANR=ANR
ABTxPERSON: ANR
Schestag
ANAME
AORT
PNR
NAME
ALTER
GEHALT
ANR‘
K51
Planung
Darmstadt
123
Müller
32
43500
K51
K53
Einkauf
Frankfurt
829
Schmid
36
45200
K53
K55
Vertrieb
Frankfurt
406
Coy
47
50700
K55
K55
Vertrieb
Frankfurt
574
Abel
28
36000
K55
Datenbanken I (Bachelor)
Kapitel 6 - 23
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (8)
Operationen der Relationen-Algebra
•
Gleichverbund (Fortsetzung)
Verlustbehafteter Gleichverbund
– wenn Tupel in ABT oder PERS keinen Verbundpartner finden (dangling tuples),
z.B. (K56, Finanzen, München)
Verlustfreier Gleichverbund (losless join)
– Ein Gleichverbund zwischen R und S heißt verlustfrei, wenn alle Tupel von R
und S am Verbund teilnehmen.
•
Natürlicher Verbund (Natural Join)
Ein natürlicher Verbund ist ein Gleichverbund über alle gleichen Attribute und
Projektion über die verschiedenen Attribute.
•
Syntax:
Schestag
R ZY S
Datenbanken I (Bachelor)
Kapitel 6 - 24
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (9)
Operationen der Relationen-Algebra
•
Natürlicher Verbund (Fortsetzung)
•
Beispiel:
ABT ZY PERSON
= πANR, ANAME, AORT, PNR, NAME, ALTER, GEHALT(σANR=ANR(ABT x PERSON)) :
ABT x PERSON: ANR
Schestag
ANAME
AORT
PNR
NAME
ALTER
GEHALT
K51
Planung
Darmstadt
123
Müller
32
43500
K53
Einkauf
Frankfurt
829
Schmid
36
45200
K55
Vertrieb
Frankfurt
406
Coy
47
50700
K55
Vertrieb
Frankfurt
574
Abel
28
36000
Datenbanken I (Bachelor)
Kapitel 6 - 25
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (10)
Operationen der Relationen-Algebra
•
Verbundvarianten
Ziel: Verlustfreier Verbund soll erzwungen werden
⇒ Übernahme von „dangling tuples“ in das Ergebnis und Auffüllen mit Nullwerten
•
voller äußerer Verbund (full outer join) übernimmt alle Tupel beider Operanden
R ZY S
•
linker äußerer Verbund (left outer join) übernimmt alle Tupel des linken Operanden
R ZY S
•
rechter äußerer Verbund (right outer join) übernimmt alle Tupel des rechten
Operanden
R ZY S
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 26
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (11)
Operationen der Relationen-Algebra
•
Verbundvarianten – Beispiele
LINKS
ZY
ZY
Schestag
A
B
1
2
RECHTS
B
C
D
2
3
4
x
3
4
5
y
A
B
C
D
2
3
4
x
A
B
C
D
1
2
⊥
⊥
2
3
4
x
⊥
4
5
y
ZY
ZY
Datenbanken I (Bachelor)
A
B
C
D
1
2
⊥
⊥
2
3
4
x
A
B
C
D
2
3
4
x
⊥
4
5
y
Kapitel 6 - 27
fbi
h_da
6.2 SQL – Datenmanipulation DML: Basis Relationen-Algebra (12)
Mengenoperationen der Relationen-Algebra
•
•
•
•
•
•
Vereinigung
Eine Vereinigung sammelt die Tupelmengen zweier Relationen unter einem
gemeinsamen Schema auf.
Die Attributmengen beider Relationen müssen identisch (bzw.
vereinigungsverträglich) sein – dies gilt für alle nachfolgenden Mengenoperationen.
Syntax:
R U S := { t | t ∈ R v t ∈ S }
Differenz
Eine Differenz eliminiert die Tupel aus der ersten Relation, die auch in der zweiten
Relation vorkommen.
Syntax:
R - S := { t | t ∈ R Λ t ∉ S }
Durchschnitt
Ein Durchschnitt liefert die Tupel, die in beiden Relationen gemeinsam vorkommen.
Syntax:
Schestag
R ∩ S := { t | t ∈ R Λ t ∈ S }
Datenbanken I (Bachelor)
Kapitel 6 - 28
fbi
h_da
6.2 SQL – Datenmanipulation DML
Der DML-Sprachumfang von SQL basiert auf der Relationen-Algebra.
Man unterscheidet in der DML-Komponente von SQL schreibende und lesende
Datenbankzugriffe:
•
Update-Operatoren
Update-Operatioren ist der Oberbegriff für alle Operatoren, die schreibend auf die
Daten zugreifen,
•
Retrieval-Operatoren
Retrieval-Operatoren sind Operatoren, die ausschließlich lesend auf die Daten
zugreifen.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 29
fbi
h_da
6.2.1 Update-Operatoren Insert, Update, Delete (1)
SQL unterscheidet die drei Update-Opertoren:
INSERT
UPDATE
DELETE
einfügen
ändern
löschen.
Der Insert-Operator
• Mit Hilfe des Insert-Operators wird eine neue Zeile (Datensatz, Ausprägung, Tupel) in
eine Tabelle eingefügt.
Syntax: INSERT INTO <Table_Name>
[(<Column_Name>,)]
C1
C2
C3
C4
VALUES (<Column_Werte>,) ;
INSERT INTO
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 30
fbi
h_da
6.2.1 Update-Operatoren Insert, Update, Delete (2)
•
Mit dem INSERT-Statement können in eine Tabelle eine oder mehrere Zeilen
eingefügt werden.
•
Werden die Namen der Spalten nicht angegeben, so müssen die Werte in der
Reihenfolge der Spalten wie im CREATE-Statement der Tabelle angegeben werden.
•
Es muss dann auch für jede Spalte ein Wert angegeben werden.
•
Spalten, die beim INSERT nicht angegeben werden, erhalten den Wert NULL, falls
dies aufgrund der Table-Definition möglich ist.
•
Ist für eine Spalte ein DEFAULT definiert, so kann dieser als Column-Wert
angegeben werden:
INSERT INTO ... VALUES (..., DEFAULT, ...)
•
Die VALUES-Klausel kann durch ein geeignetes SELECT-Statement (s. RetrievalOperatoren, 6.2.2) ersetzt werden.
•
Jede mit Hilfe eines SELECT ermittelte Menge kann auf diese Weise in eine
passende Tabelle gespeichert werden.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 31
6.2.1 Update-Operatoren Insert, Update, Delete (3)
•
fbi
h_da
Beispiel 1 *):
INSERT INTO abflug
(AB_DATUM, F_BEZ, HERST, TYP, SER_NR, PER_NR, AB_ZEIT)
VALUES ('13.11.97','LH-341','Boeing','B737','ba23-0012',10010001,10.23);
•
Beispiel 2:
INSERT INTO pilot
VALUES (10010001,'Boeing','B737','10.04.90',8978);
*) Das logische und physische Datenmodell für die verwendete Beispieldatenbank ist abgebildet
in DBISQLDB.pdf
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 32
fbi
h_da
6.2.1 Update-Operatoren Insert, Update, Delete (4)
Der Update-Operator
• Mit Hilfe des Update-Operators kann der Werte einer bestimmten Spalte in einer oder
mehreren Zeilen einer Tabelle geändert werden.
Syntax: UPDATE <Table_Name>
SET <Column_Name> = <Column_Wert>
[WHERE <Zusatzbedingung>] ;
C1
C2
C3
C4
UPDATE ...
SET C3 =...
WHERE ...
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 33
6.2.1 Update-Operatoren Insert, Update, Delete (5)
fbi
h_da
Der Update-Operator (Fortsetzung)
• Der <Column_Wert> kann ein beliebiger Ausdruck sein, z.B. auch ein SELECTErgebnis.
• Mit dem UPDATE-Statement können in einer Tabelle eine oder mehrere Zeilen
verändert werden.
• Soll eine einzelne Zeile verändert werden, so ist der primary key als Filter geeignet.
• Mit einem einzigen Befehl können alle Sätze einer Tabelle verändert werden.
• Einem UPDATE muss kein “read for update” vorangehen.
•
Beispiel 3:
UPDATE eteil
SET e_preis = 5.24
WHERE e_nr = 67891;
•
Beispiel 4:
UPDATE eteil
SET e_preis = e_preis * 1.16;
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 34
fbi
h_da
6.2.1 Update-Operatoren Insert, Update, Delete (6)
Der Delete-Operator
• Mit Hilfe des Delete-Operators können eine bestimmte Zeile oder mehrere Zeilen
einer Tabelle gelöscht werden.
Syntax: DELETE FROM <Table_Name>
[WHERE <Column_Name> = <Column_Wert>] ;
C1
C2
C3
C4
DELETE FROM ...
WHERE ...
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 35
fbi
h_da
6.2.1 Update-Operatoren Insert, Update, Delete (7)
Der Delete-Operator (Fortsetzung)
• Soll eine einzelne Zeile gelöscht werden, so ist der primary key als Filter geeignet.
• Mit einem einzigen Befehl können alle Zeilen einer Tabelle gelöscht werden.
• Nach dem Löschen aller Zeilen bleibt die Tabelle erhalten.
•
Beispiel 5:
DELETE FROM pilot
WHERE per_nr = '10010002';
•
Beispiel 6:
DELETE FROM pilot;
Schestag
Diese Anweisung löscht alle Datensätze der Tabelle
Pilot, aber nicht die Tabelle als Struktur! Dies erfolgt
mit DROP TABLE pilot; (vgl. 6.1).
Datenbanken I (Bachelor)
Kapitel 6 - 36
fbi
h_da
6.2.2 Der Retrieval-Operator Select (1)
Der Select-Operator
• Mit Hilfe des Select-Operators können Informationen aus der Datenbank ausgelesen
werden.
Das Ergebnis jeder SELECT-Operation
ist wieder eine Tabelle.
•
Ein SELECT-Ausdruck besteht neben dem SELECT-Operator aus mehreren
Klauseln:
FROM ..., WHERE ..., GROUP BY ..., HAVING ..., ORDER BY ... .
Die letzten vier Klauseln sind optional.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 37
fbi
h_da
6.2.2 Der Retrieval-Operator Select (2)
Der Select-Operator (Fortsetzung)
Syntax: SELECT [ALL | DISTINCT] <Column_Elemente,>
FROM
<Table_Name,> ;
C1
C2
C3
C4
SELECT C1, C2, C3
FROM ...;
C1
Schestag
C2
C3
Datenbanken I (Bachelor)
Kapitel 6 - 38
fbi
h_da
6.2.2 Der Retrieval-Operator Select (3)
Der Select-Operator (Fortsetzung)
• Die Liste der Column_Elemente darf nicht leer sein.
• Sollen alle Spalten einer Tabelle selektiert werden, so kann * als Platzhalter
verwendet werden.
• Für das Anlisten der Column_Elemente gibt es keine vordefinierte Ordnung der
Spalten oder Zeilen, es sei denn, man benutzt die ORDER-Klausel, s.u.
•
Beispiel 7:
SELECT * FROM angestellter;
•
•
per_nr name adr beruf gehalt
...
...
... ...
...
Die Option DISTINCT bewirkt, dass doppelte Zeilen aus der Ergebnistabelle
eliminiert werden, die Option ALL bewirkt, dass doppelte Zeilen in der
Ergebnistabelle auch doppelt angezeigt werden.
Ist weder ALL noch DISTINCT spezifiziert, so wird ALL als default angenommen.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 39
fbi
h_da
6.2.2 Der Retrieval-Operator Select (4)
Der Select-Operator (Fortsetzung)
• Die Liste der Table_Namen darf nicht leer sein. Referenziert die Tabellenliste die
Tabellen A, B und C, so berücksichtigt das Selektionsergebnis das kartesische
Produkt A x B x C der drei Tabellen.
• Die Ergebnistabelle enthält dann alle Spalten aus A, B und C.
• Jede Zeile der einen Tabelle wird mit allen Zeilen der anderen Tabelle angelistet.
SELECT * FROM A, B ;
ist semantisch äquivalent zu
SELECT * FROM A CROSS JOIN B ;
•
Beispiel 8:
SELECT * FROM maschine, flugzeug;
herst typ km_h sitze herst typ ser_nr f_std an_datum
...
...
...
...
...
...
...
...
...
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 40
fbi
h_da
6.2.2 Der Retrieval-Operator Select (5)
Der Select-Operator (Fortsetzung)
• Die Auswahl bestimmter Zeilen einer Tabelle nennt
man Selektion.
• Die Auswahl bestimmter Spalten einer Tabelle
nennt man Projektion.
C1
C2
C3
C1
C2
C3
C4
•
Spaltennamen können qualifiziert bzw. umbenannt werden (z.B. Bezeichner in
anderer Sprache):
SELECT <Column_Name/Wert> AS <Column_Name_neu> ...
•
Beispiel 9:
Schestag
select name, gehalt as salary
from angestellter;
Datenbanken I (Bachelor)
C4
name
...
salary
...
Kapitel 6 - 41
6.2.2 Der Retrieval-Operator Select (6)
fbi
h_da
Die ORDER BY - Klausel
• Mit der ORDER BY-Klausel kann die Ergebnistabelle einer SELECT-Operation nach
den Werten einer (oder mehrerer) Spalten aufsteigend oder absteigend sortiert
werden.
Syntax: SELECT <Column_Element,>
FROM <Table_Name,>
ORDER BY <Column_Element [ASC | DESC],>
•
•
•
•
Nur mit Hilfe der Klausel ORDER BY ist sichergestellt, dass die Daten in der
gewünschten Reihenfolge angelistet werden.
Die Optionen ASC (ascending = aufsteigend) bzw. DESC (descending = absteigend)
ermöglichen das Sortieren in unterschiedlichen Reihenfolgen; der default ist ASC.
Das (geschachtelte) Sortieren nach mehreren Spalten ist möglich.
Die Identifizierung der Spalte, nach der sortiert werden soll, ist möglich durch eine
Zahl, die die absolute Position der Spalte in der Ergebnistabelle darstellt: die Zahl
referenziert das Column-Element der Ergebnistabelle:
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 42
fbi
h_da
6.2.2 Der Retrieval-Operator Select (7)
Die ORDER BY-Klausel (Fortsetzung)
• Beispiel 10:
SELECT name, adr, beruf, gehalt
FROM angestellter
ORDER BY beruf, gehalt;
⇔
SELECT name, adr, beruf, gehalt
FROM angestellter
ORDER BY 3, 4;
Die WHERE - Klausel
• Mit der WHERE-Klausel kann auf den zugrunde liegenden Tabellen der SELECTOperation ein Filter definiert werden.
Syntax: SELECT <Column_Element,>
FROM <Table_Name,>
WHERE <logische Bedingung(en)>
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 43
6.2.2 Der Retrieval-Operator Select (8)
fbi
h_da
Die WHERE-Klausel (Fortsetzung)
• Die Bedingungen, die der WHERE-Klausel folgen, werden mit den üblichen
Operatoren und mit Hilfe von Klammern gebildet : AND, NOT, OR, >, >=, <, <=,
(, ) .
• Die Ergebnistabelle enthält nur Zeilen, für die der Wahrheitswert TRUE ermittelt
wird. NULL-Elemente können nicht logisch zugeordnet werden
⇒ In SQL gibt es drei Wahrheitswerte: TRUE, FALSE und UNKNOWN.
•
Beispiel 11:
•
Beispiel 12:
Schestag
SELECT name, adr, per_nr
FROM angestellter
WHERE beruf='Pilot';
SELECT typ, ser_nr, f_std
FROM flugzeug
WHERE (typ = 'A330' OR typ = 'B777')
AND f_std < 6000;
Datenbanken I (Bachelor)
Kapitel 6 - 44
fbi
h_da
6.2.2 Der Retrieval-Operator Select (9)
SELECT mit arithmetischen Ausdrücken
•
Als arithmetische Operatoren mit Klammern “(” bzw. “)” stehen zur Verfügung:
+, –, *, / . Die Bildung der Potenz ist nicht möglich.
• Hat einer der Operanden einen unbekannten Wert (= NULL), dann ist auch das
Ergebnis der arithmetischen Operation unbekannt.
•
Beispiel 13:
SELECT per_nr AS PersonalNr,
gehalt AS 'altes Gehalt',
gehalt * 1.05 AS 'neues Gehalt'
FROM angestellter;
Schestag
PersonalNr
Datenbanken I (Bachelor)
...
altes Gehalt
...
neues Gehalt
...
Kapitel 6 - 45
fbi
h_da
6.2.2 Der Retrieval-Operator Select (10)
SELECT ... WHERE ... BETWEEN ...
• BETWEEN entspricht größer / gleich und kleiner / gleich.
• Es können auch char-Variablen sortiert werden, z.B. beruf between 'a' and 'b'zeigt
alle Datensätze mit Berufen an, die mit ‘A’ anfangen und den Beruf ‘B’ (zum
Sortieren werden ‘A’ bzw. ‘B’ mit Blanks aufgefüllt.).
• Die Sortierung hängt vom maschinensprachlichen characterset ab:
–
–
•
EBCDIC:
ASCII:
Zahlen > Buchstaben Extended binary coded decimal interchange code
Zahlen < Buchstaben
Beispiel 14:
SELECT ab_datum,f_bez
FROM abflug
WHERE ab_datum BETWEEN '14.05.01' AND '30.06.01';
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 46
fbi
h_da
6.2.2 Der Retrieval-Operator Select (11)
SELECT ... WHERE ... IN ...
• Mit dieser Klausel kann die Übereinstimmung eines Spaltenwertes mit einer
vorgegebenen Menge von Werten als Filter geprüft werden.
•
Beispiel 15:
SELECT ab_datum,f_bez
FROM abflug
WHERE ab_datum IN ('13.05.01','14.05.01');
ist äquivalent zu
SELECT f_bez, ab_datum
FROM abflug
WHERE ab_datum = '13.05.01'
OR ab_datum = '14.05.01';
SELECT ... WHERE ... LIKE ...
• LIKE ist einsetzbar für CHAR und VARCHAR.
•
“%” steht für eine beliebige Anzahl von Zeichen;
die Anzahl kann auch null sein, z.B. beruf like '%Ing%'
• “_” steht für ein einzelnes Zeichen.
Schestag
Datenbanken I (Bachelor)
Beispiel 16:
SELECT per_nr, name
FROM angestellter
WHERE name LIKE 'M%';
Kapitel 6 - 47
6.2.2 Der Retrieval-Operator Select (12)
fbi
h_da
Der SUBSTRING-Operator hat die folgende Syntax:
SUBSTRING ( [VAR]CHAR-Column, <ab Position>, <Länge>) = ' .... '
•
Die Verknüpfung (Concatination) von varchar-Variablen ist möglich durch den || Operator.
•
Beispiel 17:
SELECT per_nr, name
FROM angestellter
WHERE SUBSTR(name,1,1) = 'M';
SELECT ... WHERE ... IS NULL ...
• Der Spaltenwert NULL kann für Null-fähige Spalten abgefragt werden mit Hilfe der
Bedingung ... IS NULL ... (und nicht = NULL!, da der NULL-Wert typ-unabhängig ist).
• Entsprechend der Ausprägung von null-fähigen Spalten können Bedingungen außer
den boolschenKonstanten TRUE (T) und FALSE (F) auch noch den boolschen Wert
UNKNOWN (U) haben.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 48
fbi
h_da
6.2.2 Der Retrieval-Operator Select (13)
SELECT ... WHERE ... IS NULL ... (Fortsetzung)
• Die Wirkungsweise soll verdeutlicht werden an der null-fähigen Spalte ERSCHJ
(Erscheinungsjahr) einer Tabelle BUCH:
... WHERE Erschj = 1990
T, F oder U
... WHERE NOT (Erschj = 1990)
T, F
ist immer nur T oder F, nie U!
... WHERE Erschj = 1990 IS NOT UNKNOWN
⇔
... WHERE NOT (Erschj = 1990 IS UNKNOWN)
⇔
... WHERE (Erschj = 1990 IS TRUE)
OR (Erschj = 1990 IS FALSE)
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 49
fbi
h_da
6.2.2 Die Spaltenfunktionen (1)
•
•
SQL bietet die Möglichkeit, auf Spalten von (Ergebnis-)Tabellen oder Zeilengruppen
solcher Tabellen spezielle Funktionen anzuwenden:
COUNT(Spalte)
Anzahl von Werten einer Spalte (Zeilenanzahl)
SUM(Spalte)
Summe der Werte einer Spalte
(nur für numerische Argumente)
AVG(Spalte)
Durchschnitt der Werte einer Spalte
(nur für numerische Argumente)
MIN(Spalte)
kleinster Wert aller Werte einer Spalte
MAX(Spalte)
größter Wert aller Werte einer Spalte
Alle aufgeführten Spaltenfunktionen berücksichtigen keine NULL-Werte!
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 50
fbi
h_da
6.2.2 Die Spaltenfunktionen (2)
•
•
•
•
Die Spaltenfunktion COUNT( ) ist die einzige, die als Argument "*" zulässt, da sie
die Anzahl der Zeilen ermittelt und dieser Wert nicht von einer bestimmten Spalte
abhängig ist.
Sollen nur Zeilen unterschiedlichen Inhalts gezählt werden, so kann dies durch
Einfügen des Schlüsselwortes DISTINCT erreicht werden (nicht unterstützt bei
Oracle!).
Alle anderen Spaltenfunktionen müssen als Argument einen Spaltennamen der
entsprechenden Tabelle erhalten.
Es empfiehl sich, den Spalten, die durch die Anwendung von Spaltenfunktionen neu
entstehen, mit Hilfe der AS-Klausel einen sinnvollen Namen zu geben.
SELECT COUNT(*)as Anzahl
FROM flug;
SELECT DISTINCT COUNT(z_ort)
from flug;
SELECT COUNT(*)as Anzahl
FROM maschine
WHERE sitze > 230;
SELECT SUM(e_preis)
FROM eteil;
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 51
fbi
h_da
6.2.2 Die Spaltenfunktionen (3)
•
Zur Erstellung von Listen / Reports ist es oft wünschenswert, Ergebnistabellen einer
Select-Anfrage nach bestimmten Spaltenwerten zeilenweise zu gruppieren.
•
Die GROUP BY- Klausel unterstützt die zeilenweise Gruppierung von Tabellen.
Diese Klausel findet häufig Anwendung im Zusammenhang mit Spaltenfunktionen, da
Informationen über Gruppen von Zeilen und nicht über jede einzelne Ausprägung /
Zeile der Gruppe erwünscht sind.
• Beispiel 18
) Man interessiert sich für das durchschnittliche Gehalt in jeder Berufsgruppe einer
Organisation.
• Intern erfolgt zunächst immer ein SORT nach der Spalte, nach der gruppiert wird (im
Beispiel ist dies die Spalte beruf). Es ist wichtig, sich dessen bewusst zu sein, da ein
SORT immer mit erheblichen Aufwänden verbunden ist (Performance!).
• Nun wird die angegebene Spaltenfunktion auf alle Zeilen angewandt, die bzgl. der
Gruppierungsspalte den gleichen Wert haben, d.h. solange, bis in der
entsprechenden Spalte ein so genannter Gruppenwechsel eintritt.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 52
fbi
h_da
6.2.2 Die Spaltenfunktionen (4)
SELECT beruf, AVG(gehalt)as average
FROM angestellter
GROUP BY beruf
ORDER BY 2 DESC;
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 53
fbi
h_da
6.2.2 Die Spaltenfunktionen (5)
Weitere Besonderheiten bei Anwendung der GROUP BY-Klausel:
•
Alle Column-Elemente nach SELECT müssen mit Spaltenfunktionen ermittelt
werden, mit Ausnahme der GROUP BY-Spalte (die aus semantischen Gründen
immer in der Ergebnistabelle enthalten sein sollte).
•
Die GROUP BY-Klausel kann um eine HAVING-Klausel erweitert werden, die aus
den ermittelten Gruppen bestimmte Gruppen herausfiltert:
Mit HAVING können Gruppen ausgewählt werden.
•
GROUP BY ... HAVING steht immer ganz am Ende eines (Sub-) SELECTS (ggf.
gefolgt von einer ORDER BY-Anweisung, die dann überflüssig ist, wenn aufsteigend
nach der Gruppierungsspalte sortiert werden soll - denn dies wird intern sowieso
gemacht).
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 54
fbi
h_da
6.2.2 Die Spaltenfunktionen (6)
•
In einer SELECT-Anweisung können sowohl WHERE- als auch HAVING- Klauseln
vorkommen:
WHERE auf Zeilenebene entspricht HAVING auf Gruppenebene
filtert VOR dem Gruppieren
filtert NACH dem Gruppieren
SELECT beruf, AVG(gehalt)as average
FROM angestellter
WHERE adr = "Kaiserslautern"
GROUP BY beruf
HAVING AVG(gehalt) > 5000.0;
•
•
filtert alle Angestellten aus „Kaiserslautern“
vor dem internen SORT und GROUP BY.
filtert alle Berufsgruppen mit AVG(gehalt) > 5000
nach dem internen SORT und GROUP BY.
SQL unterstützt nur einstufige Gruppenwechsel.
Mehrstufige Gruppenwechsel werden ggf. von Reportgeneratoren unterstützt.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 55
fbi
h_da
6.2.2 JOIN-Ausdrücke (1)
•
Ein SELECT über mehrere Tabellen erfolgt über die Referenzierung entsprechender
Key-Spalten und erfordert eine Zuordnung derjenigen Datensätze, deren Primaryund Foreign-Keyspalten die gleiche Ausprägung haben:
Im logischen Modell sind dies
referenzierende Relationships,
Tab1
Primary Key 1
referenzierende Relationship
Tab2
Primary Key 2
im physischen Modell foreign key- &
primary key-Spalten gleichen Wertes
PK1
TAB1
INTEGER
PK2
PK1
PK1 = PK1
TAB2
CHAR(6)
INTEGER
Syntax: SELECT <Column_ Elemente,>
FROM <TAB1>, <TAB2>
WHERE <TAB1>.<PK1> = <TAB2>.<PK1> ;
SELECT a.per_nr, a.name, p.p_std
FROM angestellter a, pilot p
WHERE a.per_nr = p.per_nr;
Schestag
Datenbanken I (Bachelor)
ANGESTELLTER
PER_NR NUMBER(8)
NAME
CHAR(15)
ADR
CHAR(20)
BERUF
CHAR(25)
GEHALT NUMBER(8,2)
PILOT
PER_NR
HERST
TYP
LIZ
P_STD
NUMBER(8)
CHAR(15)
CHAR(10)
CHAR(15)
INTEGER
PER_NR = PER_NR
Kapitel 6 - 56
fbi
h_da
6.2.2 JOIN-Ausdrücke (2) – inner joins
•
•
•
•
Die Ergebnistabelle eines JOINs entsteht durch
Auswahl bestimmter Spalten und Zeilen des kartesischen Produkts der beteiligten Tabellen.
I.d.R. erfolgt der JOIN über Primärschlüssel- und
Fremdschlüsselspalten.
Ein JOIN ist aber auch über beliebige Spalten
möglich, sofern es semantisch sinnvoll ist und
die Datentypen der JOIN-Spalten kompatibel sind.
Äquivalente Formulierungen zur Syntax der
vorhergehenden Folie sind:
–
–
TAB1 JOIN TAB2 ON WHERE-Bedingung
TAB1 JOIN TAB2 USING (gemeinsame Spalte),
dies setzt natürlich voraus, dass die
JOIN-Spalten bei beiden Tabellen gleich
heißen.
–
ANGESTELLTER
PER_NR
NAME
ADR
BERUF
GEHALT
PILOT
PER_NR
HERST
TYP
LIZ
P_STD
NUMBER(8)
CHAR(15)
CHAR(10)
CHAR(15)
INTEGER
PER_NR = PER_NR
PER_NR = PER_NR
ABFLUG
F_BEZ
AB_DATUM
PER_NR
HERST
TYP
SER_NR
AB_ZEIT
CHAR(15)
DATE
NUMBER(8)
CHAR(15)
CHAR(10)
CHAR(15)
NUMBER(4,2)
F_BEZ = F_BEZ
TAB1 NATURAL JOIN TAB2.
Weitere Join-Typen ⇒ vgl. hierzu die Literatur
FLUG
F_BEZ
S_ORT
Z_ORT
ZEIT
KM
Schestag
NUMBER(8)
CHAR(15)
CHAR(20)
CHAR(25)
NUMBER(8,2)
Datenbanken I (Bachelor)
CHAR(15)
CHAR(15)
CHAR(15)
NUMBER(5,2)
INTEGER
Kapitel 6 - 57
fbi
h_da
6.2.2 JOIN-Ausdrücke (3) – inner joins
•
Grundsätzlich erfordert ein JOIN über n Tabellen mindestens n-1 JOIN-Bedingungen:
SELECT ab.f_bez, ab.ab_datum, a.name
FROM angestellter a, abflug ab, flug f
WHERE a.per_nr = ab.per_nr
AND ab.f_bez = f.f_bez;
1. JOIN
2. JOIN
Subselects – Unterabfragen
• Jedes Subselect kann getrennt getestet werden.
• Der Subselect (nested log) wird vom Optimizer i.d.R. zu einem (sort/merge) Join
gemacht (vgl. weiter unten).
SELECT herst,typ,ser_nr
FROM flugzeug
WHERE ser_nr IN
(SELECT ser_nr
FROM abflug
WHERE ab_datum = ’13.11.97’);
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 58
6.2.2 JOIN-Ausdrücke (4) – subselects
•
•
•
fbi
h_da
Subselects (Fortsetzung)
In Subselects können über die Spaltenfunktionen auch Vergleichswerte herangezogen werden, allerdings nur dann, wenn der Subselect genau einen Wert ermittelt.
Innerhalb einer SELECT-Anweisung können beliebig viele Subselects ver wendet
werden.
SELECT per_nr,name
FROM angestellter
WHERE per_nr IN
(SELECT per_nr
FROM abflug
WHERE f_bez IN
(SELECT f_bez
FROM flug
WHERE start ='Luxemburg'));
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 59
fbi
h_da
6.2.2 VIEW – Sichten (1)
•
•
•
Eine VIEW ist eine virtuelle Tabelle, die sich für die Benutzer wie eine normale
Tabelle verhält.
Eine VIEW ist aber kein eigenständiges Objekt, sondern immer dynamisch aus den
Spalten realer Tabellen abgeleitet.
M.a.W.: Eine VIEW ist ein dynamisches Fenster, das als “Maske” nur einen
Ausschnitt der DB zeigt.
Eine VIEW besitzt für ihre Spalten auch Spaltennamen. Werden beim CREATE
VIEW keine Spaltennamen angegeben, so werden die Namen der Spalten aus dem
zugrunde liegenden SELECT-Teil verwendet. Spaltennamen müssen für alle Spalten
angegeben werden, falls eine Funktion oder ein arithmetischer Ausdruck oder
Konstanten verwendet werden.
CREATE VIEW Vbrutto (nr, bez, brutto)
AS SELECT e_nr,e_bez,e_preis*1.16
FROM eteil;
CREATE VIEW Vbr (Bezeichnung, Bruttopreis)
AS SELECT bez,brutto
FROM Vbrutto;
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 60
fbi
h_da
6.2.2 VIEW – Sichten (2)
•
Der Datentyp der Spalten einer VIEW bestimmt sich aus dem Format der Spalten des
SELECT-Teils.
•
Der Anwender kann nicht erkennen, ob er das SELECT auf eine TABLE oder eine
VIEW absetzt.
•
Es entsteht kein Duplikat der Daten. Bei jeder Abfrage wird mit Hilfe der VIEWDefinition das SELECT umgesetzt (dies hat Einfluss auf die Performance!).
•
Auf der Basis von VIEWS können weitere VIEWS definiert werden (vgl. Beispiel
oben).
•
Ein INSERT über eine VIEW ist nicht möglich, wenn die verborgenen Spalten mit
NOT NULL definiert sind.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 61
fbi
h_da
6.2.2 VIEW – Sichten (3)
VIEW mit CHECK OPTION
• Datensätze können über die VIEW nur manipuliert werden, wenn sie über die VIEW
auch wieder sichtbar werden. Die Klausel WITH CHECK OPTION garantiert dabei,
dass bei einem INSERT oder UPDATE auf die VIEW die entsprechende
Filterbedingung geprüft wird.
•
Das Gehalt kann in folgendem Beispiel nicht auf den Wert 5000.0 gesetzt werden:
CREATE VIEW personal (pers_nr,name,adresse,beruf)
AS SELECT per_nr,name,adr,beruf
FROM angestellter
WHERE gehalt < 4000.0
WITH CHECK OPTION;
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 62
fbi
h_da
6.2.2 VIEW – Sichten (4)
Vorteile vonSichten
Die Handhabung der DB wird erleichtert.
•
Es ist nicht nötig, einen SELECT immer wieder einzugeben. Für immer wieder
benötigte komplexe Abfragen wird eine VIEW definiert und getestet. Aus dieser VIEW
können dann einzelne Datensätze selektiert werden. Der Endanwender benützt für
komplexe Abfragen vordefinierte VIEWs.
Datenschutz
• Der Endanwender kann nur die Daten sehen, für die er aufgrund der VIEW-Definition
berechtigt ist (z.B. alle Personalstammsätze mit Gehältern unter 5.000,-- EUR).
Datenunabhängigkeit
• Sichten werden verwendet, um innerhalb eines DB-Systems einen hohen Grad an
Datenunabhängigkeit zu erreichen. Der Benutzer muss nicht wissen, in welchen
TABLE sich die benötigten Daten befinden. Er greift auf die Daten über eine VIEW
zu. Bei einer Änderung der DB-Struktur muss eventuell die VIEW neu definiert
werden, aber die Anwendungsprogramme müssen nicht geändert werden
(Wartungsfreundlichkeit).
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 63
fbi
h_da
6.2.3 JOIN-Strategien eines Optimizers (1)
•
Die drei unten aufgeführten Join-Strategien sollen exemplarisch anhand der
folgenden SQL-Abfrage erklärt werden:
select *
from B, C
where c.x=b.w
and c.z=17;
•
Verschachtelter Loop-Join (Nested Loop)
erste Tabelle
Tabelle C
z
x
17
cc
17
aa
18
ee
17
ff
Schestag
zweite Tabelle
Tabelle B
w
y
aa
2
cc
3
ff
1
resultierendes Tupel
z
x
w
y
17
17
17
cc
aa
ff
cc
aa
ff
3
2
1
Datenbanken I (Bachelor)
Kapitel 6 - 64
fbi
h_da
6.2.3 JOIN-Strategien eines Optimizers (2)
•
Sort/Merge-Join
Sort+Filter
Tabelle C
z
x
17
aa
17
cc
17
ff
Schestag
sortierte
Tabelle B
w
y
aa
2
cc
3
ff
1
Tupel aus C und B
z
x
w
y
17
17
17
aa
cc
ff
aa
cc
ff
2
3
1
Datenbanken I (Bachelor)
Kapitel 6 - 65
fbi
h_da
6.2.3 JOIN-Strategien eines Optimizers (3)
•
Hash-Join
1.1.Tabelle
Tabelle22wird
wirdgelesen
gelesen
und
undinineiner
einerHash-TabelHash-Tabelleleabgelegt.
abgelegt.
Tabelle 2
ZeilenHeader
Zeilen
Tabelle 1
Was nicht in den Speicher
passt, wird auf Platte ausgelagert.
Plattenbereich
Schestag
2.2.Werte
Wertevon
vonTabelle
Tabelle11werden
werden
ininder
derHash-Tabelle
Hash-Tabellegesucht.
gesucht.
Datenbanken I (Bachelor)
Kapitel 6 - 66
fbi
h_da
6.2.3 JOIN-Strategien eines Optimizers (4)
•
Die Strategie des Optimizers kann über Parameter vom DBA beeinflusst werden.
•
Um das Verhalten des Optimizers zu beobachten, können die vom Optimizer
gewählten Strategien pro SQL-Statement angezeigt und so das PerformanceVerhalten analysiert werden.
•
Die Strategie des Optimizers wird beeinflusst durch geschätzte Kosten, geschätzte
Anzahl der Ausgabezeilen, Notwendigkeit für temporäre Dateien, Verwendung von
Indexen, Verwendung von Hash-Joins, Möglichkeit des parallelen Scans auf
fragmentierten Tabellen, erforderliche Zugriffe auf remote-Datenquellen, etc. *)
*) vgl. set explain-Beispiele der Folien in der Vorlesung
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 67
fbi
h_da
6.3 Der Systemkatalog (1)
•
Alle Metadaten zu einem Objekt einer Datenbank, also alle Daten zu den erzeugten
Schemaobjekten wie Tabellen, Views, Indexe etc., werden von einem DBMS in
speziellen Systemtabellen gespeichert, dem so genannten Systemkatalog (man
spricht auch von Data Dictionary).
•
Die Inhalte der Tabellen des Systemkatalogs können mit den SQL-SELECTAnweisungen abgefragt werden.
•
Die einzelnen Tabellen des Systemkatalogs stehen miteinander in Beziehung,
sodass dem Systemkatalog ein eigenes relationales Datenmodell zugrunde liegt, das
automatisch mit Daten gefüllt wird, sobald eine Datenbankstruktur angelegt wird.
•
Die Bezeichner für die Systemtabellen variieren von System zu System.
Bei vielen DBMS beginnen die Systemtabellen mit dem Präfix SYS, z.B. bei
Informix und MS SQL-Server, und haben ansonsten sprechende Bezeichner,
z.B. SYSTABLES, SYSCOLUMNS, SYSVIEWS,SYSREFERENCES etc.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 68
fbi
h_da
6.3 Der Systemkatalog (2)
•
Namenskonventionen der Systemtabellen bei Oracle
Die Systemtabellen sind bei Oracle nach folgendem Schema aufgebaut:
<Präfix>_<Schemaobjekt>
•
•
Das Präfix schränkt die anzuzeigenden Objekte wie folgt ein:
USER_
Alle Schemaobjekte des aktuellen Users, also des entsprechenden
Schemas.
ALL_
Alle Schemaobjekte, auf die der User Zugriffsrechte hat.
DBA_
Alle Schemaobjekte; nur ein Datenbankadministrator (der User DBA)
darf hierauf zugreifen
Eine Auswahl von wichtigen Schemaobjekten *).
CONS_COLUMNS
TABLESPACES
CONSTRAINTS
TAB_COLUMNS
INDEXES
TRIGGERS
TABLES
VIEWS ...
*) Die Tabellenstruktur der Schemaobjekte kann durch entsprechendes DESC <Tablename> erfragt werden.
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 69
fbi
h_da
6.3 Der Systemkatalog (3)
•
Beispiel
create table Auftrag (
AuftragNr integer not null,
AuftragDatum date not null,
KundenNr integer not null,
primary key (AuftragNr),
foreign key (KundenNr)
references Kunde (KundenNr)
);
•
ein insert into user_tables ...
drei insert into user_tab_columns ...
zwei insert into user_constraints ...
und user_cons_columns
Zahlreiche Re-Engineering-Tools nutzen den Systemkatalog, um im reverseVerfahren ein entsprechendes (physisches) relationales und ein konzeptionelles
Datenmodell der Datenbank zu generieren.
Der Systemkatalog ist insbesondere dann wichtig,
wenn mangels Dokumentation zum Datenmodell
keine schriftlichen Informationen über die Datenstruktur vorliegen!
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 70
fbi
h_da
6.3 Der Systemkatalog (4)
•
Welche Informationen liefert diese SELECT-Anweisung?
select *
from all_tab_columns
order by table_name;
•
Neben den statischen Daten zur Datenbankstruktur werden im Systemkatalog
auch dynamisch statistische Daten zu den Datenbankinhalten gespeichert, wie
z.B. Anzahl der Zeilen pro Tabelle Maximale und minimale Ausprägung der Spalten
jeder Tabelle, etc.
•
Diese statistischen Daten werden insbesondere zur Ermittlung des optimalen
Anfrageweges durch den Optimizer benutzt.
•
Statistische Daten werden i.d.R. wegen der erheblichen Belastung des Systems nicht
automatisch nach jedem Schreibvorgang, sondern z.B. im Rahmen der Nachtverarbeitung durch explizit gestartete Prozesse aktualisiert (z.B. UPDATE STATISTICS).
Schestag
Datenbanken I (Bachelor)
Kapitel 6 - 71