Folien ab 23.10.13

5. SQL: Erstellen von Tabellen
• Erzeugen und Löschen von Tabellen
• Umgang mit Bedingungen (Constraints)
• Einfügen und Löschen von Daten
Datenbanken
Prof. Dr.
Stephan Kleuker
117
SQL
• Structured Query Language
Historie: Anfänge ca. 1974 als SEQUEL (IBM, System R)
SQL 86 und SQL 89: Schnittmenge existierender
Implementierungen
SQL 92 (SQL 2): z.B.
- expliziter Verbund
- Integritätsbedingungen
- referenzielle Integrität
SQL 99 (SQL 3): z.B. (Standard besteht aus 5 Teilen)
- aktive Regeln
- Stored Procedures
- objektorientierte Konzepte
SQL 2003 (SQL 4): z.B.
- MERGE- Befehl
- Numerierungsmöglichkeiten für Zeilen
- SQL/XML Zusammenhänge
Datenbanken
Prof. Dr.
Stephan Kleuker
SQL 2008
SQL 2011
..
118
Anmerkungen zur Syntax
• SQL (Oracle) unterscheidet bei Befehlen, Tabellennamen und
Attributen keine Groß- und Kleinschreibung, es bezeichnen
z.B. CITY, city, City, cItY die gleiche Tabelle
• Innerhalb von Strings (Texten) unterscheidet SQL Groß- und
Kleinschreibung, z.B. NAME = 'Berlin' entspricht nicht
NAME = 'berlin'
• Strings stehen in einfachen Hochkommata (neben Ä auf der
Tastatur)
• Kommentare werden in /* ... */ eingeschlossen, oder,
wenn nur einzeilig, mit -- eingeleitet
• Wenn man statt Oracle eine andere DB nutzt, müssen die
genannten Standards auf ihre Gültigkeit überprüft werden
• In Oracle werden mehrere Befehle durch ein „ ; “ getrennt
Datenbanken
Prof. Dr.
Stephan Kleuker
119
Tabellen nach SQL (ohne Randbedingungen)
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
CREATE TABLE Verkaeufer(
VNR NUMBER,
Vname VARCHAR(12),
Status VARCHAR(10),
Gehalt NUMBER
);
CREATE TABLE Kunde(
KNR NUMBER,
Name VARCHAR(12),
Betreuer NUMBER
);
Datenbanken
Prof. Dr.
Stephan Kleuker
120
Einfacher Aufbau und Beispiel
CREATE TABLE <tabellenname>(
<attributsname> <datentyp>,
...
<attributsname> <datentyp>
)
CREATE TABLE City(
Name VARCHAR(35),
Country VARCHAR(4), --Länderkürzel
Province VARCHAR(32),
Population NUMBER,
Longitude NUMBER,
Latitude NUMBER
)
Datenbanken
Prof. Dr.
Stephan Kleuker
121
Datentypen (sehr kleiner Ausschnitt)
Standard SQL
Oracle
Bedeutung
NUMERIC
NUMBER
bel. Zahlen
NUMERIC(x)
INTEGER
NUMBER(x)
INTEGER
x-stellige Ganzzahl
NUMERIC(x,p)
NUMBER(x,p) x-stellige Zahl, davon p
Nachkommastellen
VARCHAR(x)
VARCHAR(x)
Text mit maximal x Zeichen
VARCHAR2(x) Text mit maximal x Zeichen
DATE
DATE
speichern eines Datums
Hinweis : „Bevorzugte“ Oracle-Datentypen unterscheiden sich
im Namen von „Standard“-SQL-Datentypen, in OracleDokumentation befindet sich Mapping. In der Veranstaltung
wird nur VARCHAR und NUMBER (später DATE) genutzt.
Sollen eigene Skripte auch auf anderen DB laufen, sollte
man zu VARCHAR und NUMERIC übergehen.
Datenbanken
Prof. Dr.
Stephan Kleuker
122
Überblick Bedingungen (Constraints)
Mit Tabellendefinitionen können Bedingungen für konkrete
Attributwerte formuliert werden, die bei Eintragungen
überprüft werden
-
Wertebereichseinschränkungen
Wert muss angegeben werden
(Angabe eines Default-Wertes)
Angaben von Schlüsseln und Fremdschlüsseln
Forderungen an einzelne Tabelleneinträge (Datensätze) in
Form von Prädikaten
Datenbanken
Prof. Dr.
Stephan Kleuker
123
Syntax von Tabellen mit Constraints
CREATE TABLE <tabellenname>(
<attributsname> <datentyp> [DEFAULT <wert>]
[<spaltenconstraint>...
<spaltenconstraint>],
...
<attributsname> <datentyp> [DEFAULT <wert>]
[<spaltenconstraint>...
<spaltenconstraint>],
[<tabellenconstraint>]
...
[<tabellenconstraint>]
)
Teile in eckigen Klammern können weggelassen werden
- <spaltenconstraint> bezieht sich nur auf einen
Spaltenwert
- <tabellenconstraint> kann sich auf eine Zeile beziehen
-
Datenbanken
Prof. Dr.
Stephan Kleuker
124
Bedingungen (Constraints) (1/2)
• Syntax:
[CONSTRAINT <name>] <bedingung>
• Typischer Aufbau einer <bedingung>:
CHECK (<boolesche_bedingung>)
• Besondere <bedingung> mit anderen Formen:
– Primärschlüssel, Fremdschlüssel, eindeutige Attributwerte
– Spalten-Constraints zur Angabe ob Null-Werte erlaubt sind
• Name VARCHAR(10) NOT NULL
• äquivalent als Tabellen-Constraint
CHECK( Name IS NOT NULL)
Hinweis: Man kann es sich einfach machen und alle Constraints
als Tabellen-Constraints aufschreiben
Datenbanken
Prof. Dr.
Stephan Kleuker
125
Beispiel
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Randbedingungen:
• Verkaeufer-Nummer
mindestens vierstellig
• Name und Status
immer angegeben
• ein „Junior“ verdient
maximal 2500
Datenbanken
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
CREATE TABLE Verkaeufer(
VNR NUMBER CHECK(VNR >= 1000),
Vname VARCHAR(12) NOT NULL,
Status VARCHAR(10) NOT NULL,
Gehalt NUMBER,
CONSTRAINT MaxJunior CHECK
(NOT(Status = 'Junior')
OR Gehalt <= 2500)
);
Prof. Dr.
Stephan Kleuker
126
Erinnerung: Boolesche Logik
A
B
T
T
F
T
T
T
T
F
F
F
T
F
F
T
T
F
T
T
F
F
T
F
F
T
Datenbanken
NOT(A) A AND B A OR B
Prof. Dr.
Stephan Kleuker
NOT(A) OR B
127
Auswertung von Constraints – Dreiwertige Logik
• Wenn Änderungen an
Attributwerten durchgeführt
oder neue Zeilen eingefügt
werden, findet Überprüfung
der Constraints statt
• Wird ein Constraint nach
FALSE ausgewertet, wird die
Änderung verworfen
• Achtung !! Datenbanken
haben eine drei-wertige
Logik (TRUE (T), FALSE (F),
UNKNOWN (U)), findet z.B.
eine Prüfung VNR>=1000
statt und ist der Wert von
VNR NULL, wird die
Bedingung nach UNKNOWN
ausgewertet
Datenbanken
A B NOT A
A
(A)
AND OR
B
B
NOT(A)
OR B
T
T
F
T
T
T
T
F
F
F
T
F
T
U F
U
T
U
F
T
T
F
T
T
F
F
T
F
F
T
F
U T
F
U
T
U T
U
U
T
T
U F
U
F
U
U
U U U
U
U
U
Prof. Dr.
Stephan Kleuker
128
Bedingungen (Tabellen-Constraints) (2/2)
• Angabe von Primärschlüsseln (ausgewählter Schlüsselkandidat):
PRIMARY KEY(<attributsname>[,...,<attributsname>])
• Fremdschlüssel:
FOREIGN KEY (<attributsname>[,...,<attributsname>])
REFERENCES <tabellenname>
(<attributsname>[,...,<attributsname>])
[ON DELETE CASCADE]
– In den Attributslisten steht, wie die Attribute in der zu
erstellenden und in der referenzierten Tabelle heißen (müssen
dort PRIMARY KEY sein, Tabelle muss vorher existieren)
– REFRENCES-Bedingung wird durch NULL-Eintrag nicht verletzt
• Eindeutigkeit von Attributswerten (Attributskombinationen)
UNIQUE ((<attributsname>[,...,<attributsname>])
Datenbanken
Prof. Dr.
Stephan Kleuker
129
Beispiel
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Randbedingungen:
• KNR ist Schlüssel
• Kunde hat Name
• Betreuer ist Schlüssel
in Verkäufer-Tabelle
Datenbanken
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
CREATE TABLE Kunde(
KNR NUMBER,
Name VARCHAR(12)
CONSTRAINT Kname NOT NULL,
Betreuer NUMBER,
PRIMARY KEY(KNR),
CONSTRAINT FK_Kunde
FOREIGN KEY (Betreuer)
REFERENCES Verkaeufer(VNR)
);
Prof. Dr.
Stephan Kleuker
130
Schlüsselkandidaten
• Primary Key ist Schlüsselkandidat, der aus der Menge der
Schlüsselkandidaten durch Tabellenersteller ausgewählt wird
• Primary Keys sind eindeutig, dürfen keine NULL-Werte enthalten
• Das Beispiel erfüllt UNIQUE(Eins, Zwei), aber nicht
UNIQUE(Eins) und nicht UNIQUE(Zwei)
Eins
Zwei
• Mit UNIQUE kann man z. B. festhalten, dass
a
b
es Alternativen zum Primary Key gibt
a
NULL
• Beispiel:
CREATE TABLE Country(
Name VARCHAR(32) NOT NULL UNIQUE,
Code VARCHAR(4) PRIMARY KEY,
...)
NULL b
NULL NULL
• Nur wenn ein Attribut Primary Key, dann als Spalten-Constraint
formulierbar
Datenbanken
Prof. Dr.
Stephan Kleuker
131
Beispiel
CREATE TABLE is_member(
Country VARCHAR(4) REFERENCES Country(Code),
Organization VARCHAR(12)
REFRENCES Organization(Abbreviation),
Type VARCHAR(30),
CONSTRAINT MemberKey
PRIMARY KEY(Country,Organization)
)
• Anmerkungen: Hier sind die FOREIGN KEY-Constraints direkt
den Attributen zugeordnet worden, könnten auch getrennt
aufgeführt werden
• Aus einer Design-Entscheidung für Mondial folgt, dass es
Attribute gibt, die wie Tabellen heißen, die sie referenzieren
(eher ungewöhnlich, aber machbar)
Datenbanken
Prof. Dr.
Stephan Kleuker
132
Einfügen von Daten (1/2)
• einfache Variante:
INSERT INTO <tabelle> VALUES (<werteliste>)
Die <werteliste> muss für jedes Attribut einen Wert
enthalten, für undefinierte Werte wird NULL geschrieben
• mit ausgewählten Attributen:
INSERT INTO <tabelle>
(<attributsname>[,...,<attributsname>])
VALUES (<wert>[,...,<wert>])
Werte werden in die ausgewählten Spalten geschrieben, Rest
mit NULL-Werten oder DEFAULT-Werten (s. später) gefüllt
Datenbanken
Prof. Dr.
Stephan Kleuker
133
Einfügen von Daten (2/2)
• Ergebnisse einer Anfrage:
INSERT INTO <tabelle>[(attributsliste)] <anfrage>
(wird später deutlich)
• Beispiel:
INSERT INTO Country (Name, Code, Population)
VALUES('Lummerland','LU',4)
Datenbanken
Prof. Dr.
Stephan Kleuker
134
Beispiel
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
CREATE TABLE Verkaeufer( ...);
CREATE TABLE Kunde(...);
INSERT INTO Verkaeufer VALUES
(1001,'Meier','Junior',1000);
INSERT INTO Verkaeufer VALUES
(1002,'Schmidt','Senior',3000);
INSERT INTO Kunde VALUES (1,'Olm',1001);
INSERT INTO Kunde VALUES (2,'Mai',1002);
Datenbanken
Prof. Dr.
Stephan Kleuker
135
Default-Werte
• Durch Default-Werte kann man darauf verzichten, einen
Wert für ein Attribut anzugeben
• Beispiel:
CREATE TABLE is_member(
Country VARCHAR(4),
Organization VARCHAR(12),
Type VARCHAR(30) DEFAULT 'member',
CONSTRAINT MemberKey PRIMARY KEY
(Country,Organization)
);
• folgende Einfügemöglichkeiten existieren:
INSERT INTO is_member
VALUES ('CZ','EU','membership applicant');
INSERT INTO is_member (Country, Organization)
VALUES('D','EU');
Datenbanken
Prof. Dr.
Stephan Kleuker
136
Löschen von Daten
• Mit DELETE können eine oder mehrere Zeilen aus jeweils
einer Tabelle entfernt werden
DELETE FROM <tabelle> WHERE <bedingung>
• Ob Zeile gelöscht werden darf, hängt davon ab, ob eine
andere Zeile einer anderen Tabelle eine Referenz auf diese
Zeile hat und welche Form die Referenz hat (siehe Beispiele)
• Jede Zeile, für die die <bedingung> nach TRUE ausgewertet
wird, wird (wenn erlaubt) gelöscht
• Löschen aller Städte in Deutschland
DELETE FROM City WHERE Country='D';
• Wird <bedingung> weggelassen, wird sie als TRUE
interpretiert -> alle Zeilen werden (wenn erlaubt) gelöscht
DELETE FROM City;
Datenbanken
Prof. Dr.
Stephan Kleuker
137
Einfügen ohne übergeordneten Schlüssel (1/2)
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
CREATE TABLE Kunde(
KNR NUMBER,
Name VARCHAR(12),
Betreuer NUMBER,
PRIMARY KEY(KNR),
CONSTRAINT FK_Kunde
FOREIGN KEY (Betreuer)
REFERENCES Verkaeufer(VNR)
);
INSERT INTO Kunde
VALUES(3,'Hai',1003)
*
FEHLER in Zeile 1:
ORAORA-02291: Verstoß gegen
Constraint
(KLEUKER.FK_Kunde).
Übergeordn. Schlüssel nicht
gefunden
INSERT INTO Kunde VALUES(3,'Hai',1003);
Datenbanken
Prof. Dr.
Stephan Kleuker
138
Einfügen ohne übergeordneten Schlüssel (2/2)
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
CREATE TABLE Kunde(
KNR NUMBER,
Name VARCHAR(12),
Betreuer NUMBER,
PRIMARY KEY(KNR),
CONSTRAINT FK_Kunde
FOREIGN KEY (Betreuer)
REFERENCES Verkaeufer(VNR)
);
KNR
--1
2
3
NAME
-----Olm
Mai
Hai
BETREUER
---------1001
1002
3 Zeilen ausgewählt.
INSERT INTO Kunde(KNR,Name)
VALUES(3,'Hai');
SELECT * FROM Kunde;
Datenbanken
Prof. Dr.
Stephan Kleuker
139
Foreign Key ohne Delete Cascade
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
CREATE TABLE Kunde(
KNR NUMBER,
Name VARCHAR(12),
Betreuer NUMBER,
PRIMARY KEY(KNR),
CONSTRAINT FK_Kunde
FOREIGN KEY (Betreuer)
REFERENCES Verkaeufer(VNR)
);
SELECT * FROM Kunde;
DELETE FROM Verkaeufer
WHERE VNR=1001;
Datenbanken
Prof. Dr.
Stephan Kleuker
KNR
--1
2
NAME
-----Olm
Mai
BETREUER
---------1001
1002
DELETE FROM Verkaeufer WHERE
VNR=1001
*
FEHLER in Zeile 1:
ORAORA-02292: Verstoß gegen
Constraint (KLEUKER.FK_Kunde).
KLEUKER.FK_Kunde).
Untergeordneter Datensatz
gefunden.
140
Foreign Key mit Delete Cascade
Verkaeufer
VNR
Vname
Status
1001
1002
Junior
Senior
Meier
Schmidt
Gehalt
Kunde
KNR
Name
Betreuer
1000
3000
1
2
Olm
Mai
1001
1002
NAME
-----Olm
Mai
BETREUER
---------1001
1002
CREATE TABLE Kunde(
KNR NUMBER,
Name VARCHAR(12),
Betreuer Number,
PRIMARY KEY(KNR),
CONSTRAINT FK_Kunde
FOREIGN KEY (Betreuer)
REFERENCES Verkaeufer(VNR)
ON DELETE CASCADE
);
SELECT * FROM Kunde;
DELETE FROM Verkaeufer
WHERE VNR=1001;
SELECT * FROM Kunde;
Datenbanken
Prof. Dr.
Stephan Kleuker
KNR
--1
2
2 Zeilen ausgewählt.
1 Zeile wurde gelöscht.
KNR NAME BETREUER
--- ------ ---------2 Mai
1002
1 Zeile wurde ausgewählt.
141
Formatvorlage
• SQL bietet viele Alternativen, wo Constraints stehen können
=> in Projekten Coding-Guideline benötigt
CREATE TABLE Kunde(
Attribute, nur elementare
KNR NUMBER,
Constraints und DefaultName VARCHAR(12) NOT NULL,
Werte
Betreuer Number,
PRIMARY KEY(KNR),
dann Primärschlüssel
dann Fremdschlüssel (am
CONSTRAINT FK_Kunde1
Constraintnamen erkennbar)
FOREIGN KEY (Betreuer)
REFERENCES Verkaeufer(VNR),
CONSTRAINT Kunde_GrosseKNR
dann weitere Constraints
CHECK(KNR>1000),
);
Datenbanken
Prof. Dr.
Stephan Kleuker
142
Tabellen löschen
DROP TABLE <tabelle> [CASCADE CONSTRAINTS]
• Tabellen müssen nicht leer sein, wenn sie gelöscht werden
sollen
• Eine Tabelle, auf die noch eine andere Tabelle mit REFERENCES
zeigt, kann mit dem einfachen DROP TABLE <tabelle> nicht
gelöscht werden
• Mit DROP TABLE <tabelle> CASCADE CONSTRAINTS wird
eine Tabelle immer gelöscht, bei anderen Tabellen, die mit
REFERENCES auf diese Tabelle zeigen, wird diese
Integritätsbedingung (dieses Constraint) gelöscht
• Achtung! SQL-Standard nur DROP TABLE <t> , entspricht dann
CASCADE CONSTRAINTS
Datenbanken
Prof. Dr.
Stephan Kleuker
143
Ändern von Tupeln
UPDATE <tabelle>
SET <attributsname> = <wert> | (<Unteranfrage>),
...
<attributsname> = <wert> | (<Unteranfrage>)
WHERE <Boolesche_Bedingung>
• Für jede Tabellenzeile, die die <Boolesche_Bedingung>
erfüllt, werden die Werte der Attribute wie beschrieben
geändert
UPDATE City
SET Name = 'Leningrad',
Population = Population + 1000
WHERE Name = 'Sankt-Petersburg'
Datenbanken
Prof. Dr.
Stephan Kleuker
144
Überblick über Tabellen in Oracle
• In Oracle kann man sich mit dem Befehl DESCRIBE <tabelle>
oder DESC <tabelle> über die Tabellenstruktur informieren
(so keine Angaben zu PRIMARY KEY oder Constraints)
• DESC City
Name
NAME
COUNTRY
PROVINCE
POPULATION
LONGITUDE
LATITUDE
Null?
NOT NULL
NOT NULL
NOT NULL
Typ
VARCHAR(35)
VARCHAR(4)
VARCHAR(32)
NUMBER
NUMBER
NUMBER
• Hinweise: In Oracle jeden Befehl vor der Ausführung mit
einem Semikolon beenden, alle Eingaben sollten getrennt in
einem Text-File gespeichert werden
Datenbanken
Prof. Dr.
Stephan Kleuker
145
Vorgehensweise bei Übungen
• Will man Definitionen von Tabellen testen, empfiehlt es sich
ein SQL-Skript der folgenden Form für ORACLE zu schreiben:
DROP TABLE A CASCADE CONSTRAINTS;
DROP TABLE B CASCADE CONSTRAINTS;
CREATE TABLE A(
...);
CREATE TABLE B(
...);
INSERT INTO A VALUES(...); ...
INSERT INTO B VALUES(...); ...
SELECT * FROM A;
// genauer nächste VL
SELECT * FROM B;
// genauer nächste VL
• Dieses Skript wird in der DB ausgeführt. Ergebnisse werden
von der DB ausgegeben
• Achtung: Scheitert ein SQL-Befehl wird der Fehler
ausgegeben und trotzdem der nächste Befehl ausgeführt
(deshalb läuft unser Skript)
• Speichern Sie ihre Skripten in *.sql-Dateien
Datenbanken
Prof. Dr.
Stephan Kleuker
146
Mondial
• Übungsbeispiel zusammengestellt von der Uni Freiburg
(„wohnt“ jetzt in Göttingen, Prof. May, [Mon]
http://www.dbis.informatik.uni-goettingen.de/Mondial/)
• Zusammenstellung verschiedener geographischer Daten
(keine Garantie der Richtigkeit, etwas veraltet)
• Simuliert echte DB mit unvollständigen Daten
• Steht auch lokal auf der Veranstaltungsseite zur Verfügung
(Datenbankschema und Inhalt)
• Soll jeder zum Ausprobieren in seine „lokale“ DB-Version
auf dem Oracle-Server einspielen.
• SQL-Aufrufe über das Web:
http://www.semwebtech.org/sqlfrontend/
• Oracle 11g SQL-Referenz beachten
Datenbanken
Prof. Dr.
Stephan Kleuker
147
Ausschnitt ER-Mondial [Mon]
Datenbanken
Prof. Dr.
Stephan Kleuker
148
In Praktika wird Tabellenübersicht genutzt
Datenbanken
Prof. Dr.
Stephan Kleuker
149
Ihr Arbeitsbereich
• Nach dem Anmelden befinden Sie sich in ihrem lokalen
Datenbankbereich, in dem Sie ihre persönlichen Tabellen
anlegen können
• Angelegte Tabellen und eingetragene Werte bleiben nach
dem Verlassen der Datenbank erhalten, es ist kein
vorheriges Abspeichern notwendig
• Für Experten: Bei der Anmeldung wurde bereits eine
konkrete Datenbank ausgewählt, ein USES DBx ist nicht
notwendig, Sie können aber auch keine zusätzlichen
Datenbanken einrichten
Datenbanken
Prof. Dr.
Stephan Kleuker
150
Oberfläche des Worksheets Oracle SQL-Developer
Detailbeschreibung auf Web-Seite
Datenbanken
Prof. Dr.
Stephan Kleuker
151