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.

(Einführung in SQL: DDL - Struktur der Datenbank)

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

Die DDL-Befehle sind grundsätzlich so aufgebaut:

 BEFEHL OBJEKTTYP <Objektname> [<weitere Angaben>]

CREATE

CREATE erzeugt ein Datenobjekt, zum Beispiel eine Datentabelle oder gar eine Datenbank.

ALTER

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

Mit DROP kann das Objekt, z. B. eine Tabelle wieder gelöscht werden:

Hauptteile der Datenbank

DATABASE – die Datenbank selbst

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

CREATE TABLE

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.

Aufgabe

In der Beispieldatenbank wird eine Tabelle so erzeugt:

MySQL-Version

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

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:

Aufgabe

Ein Primärschlüssel kann auch nachträglich festgelegt werden, z. B. wie folgt:

Firebird-Version

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.

Aufgabe

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);

Aufgabe

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

Aufgabe

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

Aufgabe

Auf diese Weise wird ein neuer Benutzer für die Arbeit mit der aktuellen Datenbank registriert.

MySQL-Version

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

Weitere Objekte in der Datenbank erleichtern die Arbeit mit Tabellen.

VIEW – besondere Ansichten

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

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

Anstelle von AUTO_INCREMENT verwendet MS-SQL diese Erweiterung für die automatische Nummerierung neuer Datensätze:

MS-SQL-Version

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

Wenn das DBMS für Spalten keine automatische Zählung kennt (Firebird, Oracle), steht dies als Ersatz zur Verfügung.

Firebird-Version

/* 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

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

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

Ü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ösungen

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

Bei diesem Kapitel sind die folgenden Erläuterungen zu beachten:

Datentypen

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

Inhaltsverzeichnis: Grundlagen

Zurück zu: DML (2) – Daten speichern

Weiter zu: TCL – Ablaufsteuerung