DDL – Struktur der Datenbank
Mit den Befehlen der Data Definition Language (DDL) wird die Struktur der Datenbank gesteuert. Diese Aufgaben gehen über eine Einführung in SQL hinaus. Hier werden deshalb nur einige grundlegende Informationen und Beispiele behandelt, für welche Objekte einer Datenbank diese Befehle verwendet werden.
Am Ende des Buches werden einige Erweiterungen behandelt:
Bitte beachten Sie, dass ein Benutzer vor allem bei DDL-Befehlen über die entsprechenden Rechte verfügen muss, siehe DCL – Zugriffsrechte.
Allgemeine Syntax[Bearbeiten]
Die DDL-Befehle sind grundsätzlich so aufgebaut:
BEFEHL OBJEKTTYP <Objektname> [<weitere Angaben>]
CREATE[Bearbeiten]
CREATE erzeugt ein Datenobjekt, zum Beispiel eine Datentabelle oder gar eine Datenbank.
ALTER[Bearbeiten]
Mit ALTER kann das Objekt, z. B. die Tabelle, auch wieder geändert werden:
In neueren Versionen gibt es auch den gemeinsamen Aufruf (unterschiedlich je nach DBMS):
- CREATE OR ALTER
- CREATE OR REPLACE
- RECREATE
Das DBMS entscheidet dann selbst: Wenn das Objekt schon existiert, wird es geändert, andernfalls erzeugt.
DROP[Bearbeiten]
Mit DROP kann das Objekt, z. B. eine Tabelle wieder gelöscht werden:
Hauptteile der Datenbank[Bearbeiten]
DATABASE – die Datenbank selbst[Bearbeiten]
Der Befehl zum Erstellen einer Datenbank lautet:
CREATE DATABASE <Dateiname> [ <Optionen> ] ;
Der <Dateiname> ist meistens ein vollständiger Name einschließlich Pfad; in einer solchen Datei werden alle Teile der Datenbank zusammengefasst. Zu den <Optionen> gehören z. B. der Benutzername des Eigentümers der Datenbank mit seinem Passwort, der Zeichensatz mit Angaben zur Standardsortierung, die Aufteilung in eine oder mehrere Dateien usw.
Jedes DBMS bietet sehr verschiedene Optionen; wir können hier keine Gemeinsamkeiten vorstellen und müssen deshalb ganz auf Beispiele verzichten.
Wegen der vielen Möglichkeiten ist zu empfehlen, dass eine Datenbank nicht per SQL-Befehl, sondern innerhalb einer Benutzeroberfläche erstellt wird.
Mit ALTER DATABASE werden die Optionen geändert, mit DROP DATABASE wird die Datenbank gelöscht. Diese Befehle kennt nicht jedes DBMS.
TABLE – eine einzelne Tabelle[Bearbeiten]
CREATE TABLE[Bearbeiten]
Um eine Tabelle zu erzeugen, sind wesentlich konkretere umfangreiche Angaben nötig.
CREATE TABLE <Tabellenname>
( <Spaltenliste>
[ , <Zusatzangaben> ]
);
Zum Erzeugen einer Tabelle werden folgende Angaben benutzt:
- der Name der Tabelle, mit dem die Daten über die DML-Befehle gespeichert und abgerufen werden
- die Liste der Spalten (Felder), und zwar vor allem mit dem jeweiligen Datentyp
- Angaben wie der Primärschlüssel (PRIMARY KEY, PK) oder weitere Indizes
Jede Spalte und Zusatzangabe wird mit einem Komma abgeschlossen; dieses entfällt vor der schließenden Klammer. Die Zusatzangaben werden häufig nicht sofort festgelegt, sondern durch anschließende ALTER TABLE-Befehle; sie werden deshalb weiter unten besprochen.
In der Beispieldatenbank wird eine Tabelle so erzeugt:
create table Dienstwagen
( ID integer not null auto_increment primary key,
Kennzeichen varchar(30) not null,
Farbe varchar(30),
Fahrzeugtyp_ID integer not null,
Mitarbeiter_ID integer
);
Die einzelnen Spalten berücksichtigen mit ihren Festlegungen unterschiedliche Anforderungen:
- ID ist eine ganze Zahl, darf nicht NULL sein, wird automatisch hochgezählt und dient dadurch gleichzeitig als Primärschlüssel.
- Das Kennzeichen ist eine Zeichenkette von variabler Länge (maximal 30 Zeichen), die unbedingt erforderlich ist.
- Die Farbe ist ebenfalls eine Zeichenkette, deren Angabe entfallen kann.
- Für den Fahrzeugtyp wird dessen ID benötigt, wie er in der Tabelle Fahrzeugtyp gespeichert ist; diese Angabe muss sein – ein „unbekannter“ Fahrzeugtyp macht bei einem Dienstwagen keinen Sinn.
- Für den Mitarbeiter, dem ein Dienstwagen zugeordnet ist, wird dessen ID aus der Tabelle Mitarbeiter benötigt. Dieser Wert kann entfallen, wenn es sich nicht um einen „persönlichen“ Dienstwagen handelt.
ALTER TABLE[Bearbeiten]
Die Struktur einer Tabelle wird wie folgt geändert:
ALTER TABLE <Aufgabe> <Zusatzangaben>
Mit der Aufgabe ADD CONSTRAINT wird eine interne Einschränkung – Constraint genannt – hinzugefügt:
Ein Primärschlüssel kann auch nachträglich festgelegt werden, z. B. wie folgt:
ALTER TABLE Dienstwagen
ADD CONSTRAINT Dienstwagen_PK PRIMARY KEY (ID);
Die Einschränkung bekommt den Namen Dienstwagen_PK und legt fest, dass es sich dabei um den PRIMARY KEY unter Verwendung der Spalte ID handelt.
In der Tabelle Mitarbeiter muss auch die Personalnummer eindeutig sein (zusätzlich zur ID, die als PK sowieso eindeutig ist):
ALTER TABLE Mitarbeiter
ADD CONSTRAINT Mitarbeiter_PersNr UNIQUE (Personalnummer);
In der Tabelle Zuordnung_SF_FZ – Verknüpfung zwischen den Schadensfällen und den Fahrzeugen – wird ein Feld für sehr lange Texte eingefügt:
alter Table Zuordnung_SF_FZ
add Beschreibung blob;
Mit ALTER ... DROP Beschreibung kann dieses Feld auch wieder gelöscht werden.
DROP TABLE[Bearbeiten]
Damit wird eine Tabelle mit allen Daten gelöscht (diese Tabelle gab es in einer früheren Version der Beispieldatenbank):
DROP TABLE ZUORD_VNE_SCF;
Warnung: Dies löscht die Tabelle einschließlich aller Daten unwiderruflich!
USER – Benutzer[Bearbeiten]
Auf diese Weise wird ein neuer Benutzer für die Arbeit mit der aktuellen Datenbank registriert.
CREATE USER Hans_Dampf IDENTIFIED BY 'cH4y37X1P';
Dieser Befehl richtet einen neuen Benutzer mit dem Namen Hans_Dampf ein, der sich mit dem Passwort 'cH4y37X1P' anmelden muss. – Jedes DBMS kennt eigene Regeln und weitere Optionen für die Zuordnung des Passworts und die Verwendung von Anführungszeichen.
Ergänzungen zu Tabellen[Bearbeiten]
Weitere Objekte in der Datenbank erleichtern die Arbeit mit Tabellen.
VIEW – besondere Ansichten[Bearbeiten]
Eine VIEW ist eine spezielle Sicht auf eine oder mehrere Tabellen. Für den Anwender sieht es wie eine eigene Tabelle aus; es handelt sich aber „nur“ um eine fest gespeicherte Abfrage, die immer wieder in der gleichen Form benutzt und ausgeführt wird. Bitte beachten Sie: Nur die Abfrage wird fest gespeichert, nicht das Ergebnis; dieses muss bei jedem neuen Aufruf nach den aktuellen Daten neu erstellt werden.
Einzelheiten werden unter Erstellen von Views behandelt.
INDEX – Datenzugriff beschleunigen[Bearbeiten]
Ein Index beschleunigt die Suche nach Datensätzen. Um beispielsweise in der Tabelle Versicherungsnehmer nach dem Namen „Schulze“ zu suchen, würde es zu lange dauern, wenn das DBMS alle Zeilen durchgehen müsste, bis es auf diesen Namen träfe. Stattdessen wird ein Index angelegt (ähnlich wie in einem Telefon- oder Wörterbuch), sodass schnell alle passenden Datensätze gefunden werden.
So wird ein Index mit der Bezeichnung Versicherungsnehmer_Name für die Kombination „Name, Vorname“ angelegt:
create index Versicherungsnehmer_Name
on Versicherungsnehmer (Name, Vorname);
Es ist dringend zu empfehlen, dass Indizes für alle Spalten bzw. Kombinationen von Spalten angelegt werden, die immer wieder zum Suchen benutzt werden.
Weitere Einzelheiten werden unter DDL – Einzelheiten behandelt.
IDENTITY – auch ein automatischer Zähler[Bearbeiten]
Anstelle von AUTO_INCREMENT verwendet MS-SQL diese Erweiterung für die automatische Nummerierung neuer Datensätze:
CREATE TABLE Fahrzeug
(ID INTEGER NOT NULL IDENTITY(1,1),
Kennzeichen VARCHAR(10) NOT NULL,
Farbe VARCHAR(30),
Fahrzeugtyp_ID INTEGER NOT NULL,
CONSTRAINT Fahrzeug_PK PRIMARY KEY (ID)
);
Der erste Parameter bezeichnet den Startwert, der zweite Parameter die Schrittweite zum nächsten ID-Wert.
SEQUENCE – Ersatz für automatischen Zähler[Bearbeiten]
Wenn das DBMS für Spalten keine automatische Zählung kennt (Firebird, Oracle), steht dies als Ersatz zur Verfügung.
/* zuerst die Folge definieren */
CREATE SEQUENCE Versicherungsnehmer_ID;
/* dann den Startwert festlegen */
ALTER SEQUENCE Versicherungsnehmer_ID RESTART WITH 1;
/* und im Trigger (s.u.) ähnlich wie eine Funktion benutzen */
NEXT VALUE FOR Versicherungsnehmer_ID
Während der automatische Zähler, der durch AUTO_INCREMENT eingerichtet wird, genau zu der betreffenden Tabelle gehört, bezieht sich eine „Sequenz“ nicht auf eine einzelne Tabelle, sondern auf die gesamte Datenbank. Es ist ohne weiteres möglich, eine einzige Sequenz AllMyIDs zu definieren und die neue ID einer jeden Tabelle daraus abzuleiten. Dies ist durchaus sinnvoll, weil die ID als Primärschlüssel sowieso keine inhaltliche Bedeutung haben darf, sondern nur ein fortlaufender Zähler ist. In der Beispieldatenbank benutzen wir getrennte Sequenzen, weil sie für die verschiedenen DBMS „ähnlich“ aussehen soll.
Oracle arbeitet (natürlich) mit anderer Syntax (mit mehr Möglichkeiten) und benutzt dann NEXTVAL.
Programmieren mit SQL[Bearbeiten]
Die Funktionalität einer SQL-Datenbank kann erweitert werden, und zwar auch mit Bestandteilen einer „vollwertigen“ Programmiersprache, z. B. Schleifen und IF-Abfragen.
Dies wird unter Programmierung behandelt; dabei gibt es relativ wenig Gemeinsamkeiten zwischen den DBMS.
- FUNCTION – Benutzerdefinierte Funktionen
Eigene Funktionen ergänzen die (internen) Skalarfunktionen des DBMS.
- PROCEDURE – Gespeicherte Prozeduren
Eine Prozedur – gespeicherte Prozedur, engl. StoredProcedure (SP) – ist vorgesehen für Arbeitsabläufe, die „immer wiederkehrende“ Arbeiten ausführen sollen. Es gibt sie mit und ohne Argumente und Rückgabewerte.
- TRIGGER – Ereignisse beim Speichern
Ein Trigger ist ein Arbeitsablauf, der automatisch beim Speichern in einer Tabelle ausgeführt wird. Dies dient Eingabeprüfungen oder zusätzlichen Maßnahmen; beispielsweise holt sich Firebird durch einen Trigger den NEXT VALUE einer SEQUENCE (siehe oben).
TRIGGER werden unterschieden nach INSERT-, UPDATE- oder DELETE-Befehlen und können vor oder nach dem Speichern sowie in einer bestimmten Reihenfolge ausgeführt werden.
Zusammenfassung[Bearbeiten]
In diesem Kapitel lernten wir die Grundbegriffe einer Datenbankstruktur kennen:
- Die DATABASE selbst sowie TABLE sind die wichtigsten Objekte, ein USER arbeitet damit.
- Eine VIEW ist eine gespeicherte Abfrage, die wie eine Tabelle abgerufen wird.
- Ein INDEX beschleunigt den Datenzugriff.
Darüber hinaus wurde auf weitere Möglichkeiten wie SEQUENCE, Funktionen, Prozeduren und Trigger hingewiesen.
Übungen[Bearbeiten]
| Übung 1 | Objekte bearbeiten | Zur Lösung |
Welche der folgenden SQL-Befehle enthalten Fehler?
- CREATE DATABASE C:\DBFILES\employee.gdb DEFAULT CHARACTER SET UTF8;
- DROP DATABASE;
- CREATE TABLE Person ( ID PRIMARY KEY, Name VARCHAR(30), Vorname VARCHAR(30) );
- ALTER TABLE ADD UNIQUE KEY (Name);
| Übung 2 | Tabelle erstellen | Zur Lösung |
Auf welchen zwei Wegen kann der Primärschlüssel (Primary Key, PK) einer Tabelle festgelegt werden? Ein weiterer Weg ist ebenfalls üblich, aber noch nicht erwähnt worden.
| Übung 3 | Tabelle ändern | Zur Lösung |
Skizzieren Sie einen Befehl, mit dem die Tabelle Mitarbeiter um Felder für die Anschrift erweitert werden kann.
| Übung 4 | Tabellen | Zur Lösung |
Worin unterscheiden sich TABLE und VIEW in erster Linie?
| Lösung zu Übung 1 | Objekte bearbeiten | Zur Übung |
Bei 2. fehlt der Name der Datenbank.
Bei 3. fehlt der Datentyp zur Spalte ID.
Bei 4. fehlt der Name der Tabelle, die geändert werden soll.
| Lösung zu Übung 2 | Tabelle erstellen | Zur Übung |
- Im CREATE TABLE-Befehl zusammen mit der Spalte, die als PK benutzt wird, z. B.:
ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, - Durch einen ALTER TABLE-Befehl, der den PK hinzufügt:
ALTER TABLE Dienstwagen ADD PRIMARY KEY (ID);
| Lösung zu Übung 3 | Tabelle ändern | Zur Übung |
alter table Mitarbeiter
add PLZ CHAR(5),
add Ort VARCHAR(24),
add Strasse VARCHAR(24),
add Hausnummer VARCHAR(10);
| Lösung zu Übung 4 | Tabellen | Zur Übung |
Eine TABLE (Tabelle) ist real in der Datenbank gespeichert. Eine VIEW (Sichttabelle) ist eine Sicht auf eine oder mehrere tatsächlich vorhandene Tabellen; sie enthält eine Abfrage auf diese Tabellen, die als Abfrage in der Datenbank gespeichert ist, aber für den Anwender wie eine eigene Tabelle aussieht.
Siehe auch[Bearbeiten]
Bei diesem Kapitel sind die folgenden Erläuterungen zu beachten:
Manche Themen werden in den folgenden Kapiteln genauer behandelt:
- Eigene Funktionen als Ergänzung zu denen, die in Funktionen und Funktionen (2) behandelt werden.
- Prozeduren
- Trigger