SQL-Handout

150.422
14
14.1
Datenbanken – SQL Handout 9
Erstellen und Ändern von Tabellen und Datenbanken
Erstellen und Löschen
• Datenbanken • können mit
CREATE DATABASE dbname
und
DROP DATABASE dbname
erstellt bzw. gelöscht werden.
• Tabellen • können entsprechend der folgenden Syntax erstellt werden. Dabei sind optionale Angaben
in [] gesetzt.
CREATE TABLE tabname(
spalte1 typ[(laenge1)] [NOT NULL] [DEFAULT default1 ] [AUTO INCREMENT],
spalte2 typ[(laenge2)] [NOT NULL] [DEFAULT default2 ] [AUTO INCREMENT],
...,
[PRIMARY KEY (spalte1a,spalte1b,...),]
[FOREIGN KEY (spalte2a,spalte2b,...) REFERENCES tab (tabspalte1 ,tabspalte2 ,...)]
)
Dabei gibt typ den Datentyp der zu erstellenden Spalte an. Gebräuchliche Datentypen sind int, float,
double, date und varchar. Für einige Datentypen wie etwa varchar muss die Länge (Anzahl der
Zeichen, die in der Spalte maximal gespeichert werden können) angegeben werden. Mit NOT NULL wird
angezeigt, dass der Wert der Spalte niemals NULL sein darf, während mit DEFAULT ein Defaultwert für
die Spalte angegeben werden kann. Eine weitere nützliche Option ist AUTO INCREMENT, das in einer
Spalte automatisch fortlaufende Nummern generiert.
Eine Tabelle kann mit
DROP TABLE tabname
wieder aus der Datenbank entfernt werden.
B Beispiel. Wir legen zwei neue Tabellen an, in denen Studenten und ihre Studien gespeichert werden.
Die Tabelle studenten hat als Spalten matrnr, name und studium. Die zweite Tabelle studien
enthält die Studienkennzahl stkz und den Namen der Studienrichtung str. Offensichtlich kann die
stkz in dieser Tabelle als Primärschlüssel verwendet werden. In der Tabelle studenten besteht der
Primärschlüssel aus matrnr und studium, da jeder Student mehr als eine Studienrichtung haben kann.
Die Spalte studium in studenten verweist als Fremdschlüssel auf stkz in studien. Entsprechend
werden diese Tabellen wie folgt erstellt:
CREATE TABLE studien (
stkz varchar(10) NOT NULL,
str varchar(50) NOT NULL,
PRIMARY KEY (stkz)
)
CREATE TABLE studenten (
matrnr varchar(7) NOT NULL,
name varchar(50) NOT NULL,
studium varchar(10) NOT NULL DEFAULT ’033214’,
PRIMARY KEY (matrnr, studium),
FOREIGN KEY (studium) REFERENCES studien(stkz)
)
Man beachte, dass die Tabelle studien zuerst erstellt werden muss, um den Primärschlüssel von
studien als Fremdschlüssel in studenten verwenden zu können.
SS 2016
14.2
Einfügen und Ändern von Daten
• Einfügen von Zeilen •
Mit
INSERT INTO tabname (spalte1, spalte2, ...)
VALUES (wert1, wert2, ... )
können einzelne Zeilen in eine Tabelle eingefügt werden. Mit
INSERT INTO tabname (spalte1, spalte2, ...)
SELECT ...
kann das Ergebnis einer Abfrage in eine Tabelle eingefügt werden. In beiden Fällen ist es im allgemeinen
nicht nötig, Daten für alle Spalten der Tabelle anzugeben. Nicht angebene Spalten erhalten entweder
einen Defaultwert (falls bei Erstellen der Tabelle angegeben) oder werden auf NULL gesetzt (falls dies
zulässig ist). Falls eine Spalte als AUTO INCREMENT definiert wurde, enthält sie eine fortlaufende Nummer.
B Beispiel. Zunächst fügen wir die Studienrichtung Industrielogistik in die Tabelle studien ein.
1
INSERT INTO studien ( stkz , str )
VALUES ( ’ 033214 ’ , ’ Industrielogistik Bakk . ’)
Als nächstes fügen wir alle Studenten der MUL aus der Tabelle stud der Students Datenbank in unsere
Tabelle studenten.
3
INSERT INTO studenten ( matrnr , name )
SELECT mn , CONCAT ( vorname , ’ ’ , nachname ) FROM oracle1_00_stud . stud
WHERE SUBSTR ( mn ,3 ,2) = ’ 35 ’
Beachten Sie, dass als Defaultwert für alle Studenten die Studienrichtung ’033214’ eingefügt wird.
• Löschen von Zeilen •
Mit
DELETE FROM tabname
WHERE bedingung
werden jene Zeilen aus der Tabelle gelöscht, für die die angegebene Bedingung gilt. Ohne WHERE
Bedingung werden alle Daten aus der Tabelle gelöscht (aber nicht die Tabelle selbst!).
B Beispiel. Wir löschen alle Studenten, die im Jahr 1996 inskribiert haben.
2
DELETE FROM studenten
WHERE LEFT ( matrnr ,2) = ’ 96 ’
• Hinzufügen und Löschen von Spalten •
gelöscht werden.
Mit ALTER TABLE können Spalten hinzugefügt oder
ALTER TABLE tabname
ADD COLUMN neuespalte typ[(laenge)]
ALTER TABLE
DROP COLUMN spalte
• Updates • von Werten in einzelnen Spalten erfolgen durch
UPDATE tabname
SET spalte1 = wert1, spalte2 = wert2, ...
Wichtig ist die Möglichkeit, Updates über korrelierte Subqueries zu machen.
B Beispiel. Wir möchten die Spalte name in der Tabelle studenten durch zwei Spalten vorname und
nachname ersetzen, die den Vor- bzw. den Nachnamen enthalten. Zunächst fügen wir zwei neue Spalten
hinzu.
ALTER TABLE studenten
ADD COLUMN vorname varchar(25)
ALTER TABLE studenten
ADD COLUMN nachname varchar(35)
Als nächstes spalten wir den Namen nach dem ersten Leerzeichen in den Vor- und den Nachnamen auf
und schreiben die entsprechenden Werte in die dafür vorgesehenen neuen Spalten.
3
UPDATE studenten
SET nachname = SUBSTR ( name , INSTR ( name , ’ ’) ) ,
vorname = LEFT ( name , INSTR ( name , ’ ’) - 1 )
Alternativ könnte man auch folgende korrelierte Subquery auf die ursprüngliche Tabelle stud durchführen.
2
UPDATE studenten s
SET nachname =
( SELECT nachname FROM oracle1_00_stud . stud os
WHERE os . mn = s . matrnr )
Zuletzt entfernen wir die Spalte name.
1
ALTER TABLE studenten
DROP COLUMN name
14.3
Views und Indizes
• Views • sind eine Mischung zwischen Abfragen und Tabellen. Eine View kann über
CREATE VIEW viewname AS
SELECT ...
erstellt werden. Auf Views können wie auf Tabellen Abfragen über SELECT * FROM viewname gestellt
werden. Im Unterschied zu einer Tabelle, die statisch ist, reagiert die View dynamisch auf Änderungen
in den zugrundeliegenden Tabellen. Mit REPLACE VIEW und derselben Syntax wie CREATE VIEW kann
eine View geändert werden.
B Beispiel.
CREATE VIEW m_numbers AS
SELECT DISTINCT matrnr FROM studenten
Mit SELECT * FROM m numbers erhält man alle Matrikelnummern. Ändert sich die Menge der Matrikelnummern in studenten, so ändert sich auch das Ergebnis der Abfrage auf die View.
• Indizes • auf gewisse Spalten einer Tabelle können Abfragen auf die indizierten Spalten erheblich
beschleunigen. Indizes können gleich beim Erstellen einer Tabelle über Angabe von
INDEX [indexname] (spalte1, spalte2, . . . )
im CREATE TABLE angelegt werden. Für bereits bestehende Tabellen kann mittels
CREATE INDEX [indexname] ON tabname (spalte1, spalte2, . . . )
ein neuer Index angelegt werden. Zu beachten ist, dass das Anlegen zuvieler Indizes zu Performanceeinbußen führen kann. Da bei Änderungen in der Tabelle der Index neu angelegt oder zumindest adaptiert
werden muss, beschleunigen Indizes zwar Abfragen, verlangsamen jedoch Änderungsoperationen.