DatendefinitionMySQL

SQL
Lutz Kleinostendarp
JOBELMANN-SCHULE
Datendefinition
Die Organisation einer Datenbank basiert auf
einer Anzahl verschiedener Objekte. Diese
können physikalischer oder logischer Natur sein.
Das folgende Kapitel beschäftigt sich mit der
Erstellung und Bearbeitung solcher Objekte.
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Die grundlegende Anweisung zum Erstellen
von Datenbankobjekten ist CREATE.

Datenbankobjekte können sein:
ALTER TABLE

DROP


Informationen über Datenbankobjekte
werden in der jeweils übergeordneten
Datenbank gespeichert, also



Datenbanken
Tabellen
Datenbanken in der Masterdatenbank
Tabellen in der zugehörigen Datenbank
ACCESS kennt keine Masterdatenbank
14.02.2017
2
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
JOBELMANN-SCHULE



DROP
Das erste Objekt, dass erzeugt werden
muss, ist die Datenbank selbst. Der Befehl
dazu lautet:
CREATE DATABASE db_name
Weitere Optionen des Befehls sollen hier
nicht behandelt werden.

Beispiel: CREATE DATABASE testdb;

Besondere Berechtigung zur Erstellung einer
Datenbank erforderlich
siehe hierzu das Kapitel Sicherheitskonzepte

14.02.2017
3
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
JOBELMANN-SCHULE

Da ACCESS keine Masterdatenbank kennt,
kann der Befehl CREATE DATABASE dort
nicht ausgeführt werden. Die DB kann nur
auf der Benutzeroberfläche erstellt werden.

Im Folgenden wird in diesem Kapitel davon
ausgegangen, dass der Benutzer die
betroffene Datenbank als aktuelle
ausgewählt hat und über entsprechende
Berechtigungen verfügt
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP
14.02.2017
4
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE


Innerhalb der Datenbank werden
Informationen in Tabellen gespeichert. Der
Befehl zur Erstellung dieser Tabellen lautet:
CREATE TABLE tab_name;
ALTER TABLE
DROP


Es können sofort Attribute angelegt werden.
Die vollständige Syntax lautet dann:
CREATE TABLE tab_name
(sp_name1 datentyp1 [{NOT NULL|NULL}],
sp_name2 datentyp2 [{NOT NULL|NULL}]...)
14.02.2017
5
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Beispiel: Erstellung der Tabellen der
Beispieldatenbank (Seite 1)

CREATE TABLE abteilung
(abt_nr varchar(4) NOT NULL,
abt_name varchar(20) NOT NULL,
stadt varchar(15) NULL);

CREATE TABLE mitarbeiter
(m_nr int NOT NULL,
m_name varchar(20) NOT NULL,
m_vorname varchar(20) NOT NULL,
abt_nr varchar(4) NULL);
ALTER TABLE
DROP
14.02.2017
6
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Beispiel: Erstellung der Tabellen der
Beispieldatenbank (Seite 2)

CREATE TABLE projekt
(pr_nr varchar(4) NOT NULL,
pr_name varchar(25) NOT NULL,
mittel float NULL);

CREATE TABLE arbeiten
(m_nr int NOT NULL,
pr_nr varchar(4) NOT NULL,
aufgabe varchar(15) NULL,
einst_dat date NULL);
ALTER TABLE
DROP
14.02.2017
7
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
JOBELMANN-SCHULE

Integritätsregeln
unique
primary key
foreign key
ALTER TABLE

DROP



Seit SQL Server 6.0 (auch SQL95 genannt)
lassen sich weitere Parameter übergeben. In
den folgenden Übungen werden diese hier
nicht mehr berücksichtigt.
Die erweiterte Syntax lautet:
CREATE TABLE tab_name
(sp_name1 datentyp1 [DEFAULT wert]
DEFAULT – Voreinstellung der Spalte
wert - ein konstanter Ausdruck
- eine (parameterlose) Systemfunktion
z.B. CURRENT_USER,
CURRENT_TIMESTAMP
- NULL
14.02.2017
8
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
JOBELMANN-SCHULE

Integritätsregeln
unique
primary key
foreign key
ALTER TABLE

DROP



Seit SQL Server 6.0 (auch SQL95 genannt)
lassen sich weitere Parameter übergeben. In
den folgenden Übungen werden diese hier
nicht mehr berücksichtigt.
Die erweiterte Syntax lautet:
CREATE TABLE tab_name
(sp_name1 datentyp1 [DEFAULT wert]
[{IDENTITY | NULL | NOT NULL}]
[Spaltenregeln]
[, {weitere Spalten}]
[Tabellenregeln];
IDENTITY
Regeln
14.02.2017
- entspricht autowert
- Integritätsregeln
9
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
JOBELMANN-SCHULE

Integritätsregeln
unique
primary key
foreign key
ALTER TABLE

DROP




Integritätsregeln (integrity constraints)
dienen dazu, unsinnige oder widersprüchliche Datenwerte zu erkennen und
abzuweisen.
In der CREATE TABLE-Anweisung hinterlegte
Regeln werden zentral vom Server überprüft.
Eine lokale Prüfung (dieser Regel) im
Anwenderprogramm ist somit nicht mehr
notwendig.
Spaltenregeln beziehen sich immer auf ein
Attribut.
Tabellenregeln können sich auf mehrere
Attribute (Attributgruppen) beziehen.
Jede Regel erhält einen Namen.
14.02.2017
10
SQL - Datendefinition
Lutz Kleinostendarp
JOBELMANN-SCHULE
CREATE
DATABASE
TABLE

Integritätsregeln
unique
primary key
foreign key

ALTER TABLE
DROP

Die UNIQUE-Klausel definiert
Schlüsselkandidaten.
Schlüsselkandidaten sind alle Attribute
(Attributgruppen), die in der Tabelle
eindeutig einen Datensatz kennzeichnen.
UNIQUE stellt also die Einmaligkeit jedes
Wertes in dem betroffenen Attribut (der
Attributgruppe) sicher.
14.02.2017
11
SQL - Datendefinition
Lutz Kleinostendarp
JOBELMANN-SCHULE
CREATE
DATABASE
TABLE

Die Syntax der UNIQUE-Klausel:
Integritätsregeln
unique
primary key
foreign key

[CONSTRAINT regel_name]
UNIQUE [CLUSTERED | NONCLUSTERED]
(sp_name1 [{,sp_name}])

CONSTRAINT
ALTER TABLE
DROP


- definiert den expliziten
Namen der Regel
CLUSTERED
- erstellt den Index sortiert
NONCLUSTERED bzw. unsortiert
sp_name
- max. 6 Spaltennamen
sind erlaubt
14.02.2017
12
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Beispiel für die UNIQUE-Klausel
(Spaltenregel):

CREATE TABLE verkauf
(bestell_nr integer unique null,
name char(20) null);

Jede Bestellnummer darf nur einmal
vorkommen, dies gilt auch für den NULLWert!
Die zweite Eingabe des selben Wertes wird
vom System abgewiesen.
Die Regel erhält vom Server automatisch
einen impliziten Namen.
ALTER TABLE
DROP


14.02.2017
13
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Die PRIMARY KEY-Klausel definiert den
Primärschlüssel einer Tabelle.

Die Syntax lautet:

[CONSTRAINT regel_name]
PRIMARY KEY
[CLUSTERED | NONCLUSTERED]
(sp_name1 [{,sp_name}])
ALTER TABLE
DROP
14.02.2017
14
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP
JOBELMANN-SCHULE

Beispiel für die PRIMARY KEY-Klausel
(Tabellenregel):

CREATE TABLE mitarbeiter
(m_nr int NOT NULL,
m_name char(20) NOT NULL,
m_vorname char(20) NOT NULL,
abt_nr char(4) NULL,
CONSTRAINT prim_s_mit
PRIMARY KEY (m_nr));
14.02.2017
15
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP
JOBELMANN-SCHULE

Beispiel für die PRIMARY KEY-Klausel
(Spaltenregel – weniger gebräuchlich):

CREATE TABLE mitarbeiter
(m_nr int NOT NULL
CONSTRAINT prim_s_mit PRIMARY KEY,
m_name char(20) NOT NULL,
m_vorname char(20) NOT NULL
abt_nr char(4) NULL);
14.02.2017
16
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Die FOREIGN KEY-Klausel definiert einen
Fremdschlüsselbezug.

Die Syntax lautet:

[CONSTRAINT regel_name]
[FOREIGN KEY (sp_name1 [{,sp_name}])]
REFERENCES tab_name (sp_1 [{,sp_x}])

REFERENCES - benennt die Bezugstabelle
Die angegebenen Spalten müssen in Anzahl
und Datentyp in der vorliegenden
Reihenfolge übereinstimmen.
ALTER TABLE
DROP

14.02.2017
17
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP
JOBELMANN-SCHULE

Beispiel für die FOREIGN KEY-Klausel
(Tabellenregel):

CREATE TABLE arbeiten
(m_nr int NOT NULL,
pr_nr char(4) NOT NULL,
aufgabe char(15) NULL,
einst_dat datetime NULL,
CONSTRAINT prim_s_arb
PRIMARY KEY (m_nr, pr_nr),
CONSTRAINT fremd_s_arb
FOREIGN KEY (m_nr)
REFERENCES mitarbeiter (m_nr));
14.02.2017
18
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP
JOBELMANN-SCHULE

Beispiel für die FOREIGN KEY-Klausel
(Spaltenregel – weniger gebräuchlich):

CREATE TABLE arbeiten
(m_nr int NOT NULL
CONSTRAINT fremd_s_arb
REFERENCES mitarbeiter (m_nr),
pr_nr char(4) NOT NULL,
aufgabe char(15) NULL,
einst_dat datetime NULL,
CONSTRAINT prim_s_arb
PRIMARY KEY (m_nr, pr_nr));
14.02.2017
19
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Referentielle Integrität

wird durch die PRIMARY KEY- und FOREIGN KEYKlauseln erzeugt.
• Die Tabelle mit dem Primärschlüssel ist die Master- oder
Zieltabelle.
• Die Tabelle mit dem Fremdschlüssel ist die Detail- oder
Referenztabelle.
ALTER TABLE
DROP


sorgt dafür, dass in die Detailtabelle nur Werte
eingetragen werden können, die auch in der
Mastertabelle existieren. Somit wird sichergestellt, dass
ein Tabellenbezug nicht „ins Leere läuft“.
verhindert das Löschen eines Wertes in der
Mastertabelle, solange noch referierte Datensätze in der
Detailtabelle existieren.
14.02.2017
20
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
JOBELMANN-SCHULE

Es gibt weitere Anweisungen zur Erstellung
von Datenbankobjekten, von denen hier aber
nur eine Auswahl kurz erwähnt werden soll:

CHECK
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP


erlaubt die Einschränkung des Wertebereiches
INDEX

kann die Antwortzeit bei Abfragen beschleunigen
14.02.2017
21
SQL - Datendefinition
Lutz Kleinostendarp
JOBELMANN-SCHULE
CREATE
DATABASE
TABLE

Übungsphase 1:
Integritätsregeln
unique
primary key
foreign key

Gehen Sie mit Hilfe des Skriptes die
Möglichkeiten der CREATE TABLE-Anweisung
(Kapitel 3.1.2 – 3.2.4) durch und erstellen
Sie dabei die Übungsdatenbank.
ALTER TABLE
DROP
14.02.2017
22
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
JOBELMANN-SCHULE

Ändern von Objekten
Strukturell änderbare Objekte sind:
 Die Datenbank (ALTER DATABASE)
 Die Tabellenstruktur (ALTER TABLE)
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP

Die Änderungen der Datenbank beziehen
sich nur auf die Größe des Speicherplatzes,
weshalb hier im Folgenden nur auf die
Strukturänderungen von Tabellen
eingegangen wird.
14.02.2017
23
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE

Grundform:

ALTER TABLE tab_name
ADD spalten_name datentyp
[{,spalten_name2 datentyp2}…];
ALTER TABLE
DROP



Diese Anweisung fügt der Tabelle eine neue Spalte
hinzu. Die optionale Angabe NULL kann entfallen, da
NOT NULL nicht erlaubt ist (schon existierende Spalten
können nur NULL enthalten).
ALTER TABLE tab_name
DROP spalten_name
[{,spalten_name2}…];
Diese SQL92-Standarderweiterung wird nicht allgemein
unterstützt.
14.02.2017
24
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
JOBELMANN-SCHULE

Erweiterte Form:


DROP

Auch Integritätsregeln lassen sich nachträglich einfügen
(und löschen):
ALTER TABLE tab_name
[WITH {CHECK | NOCHECK}
ADD
spalten_name datentyp
[{,spalten_name2 datentyp2}…];
| ADD tabellenbezogene_Regel
| DROP tabellenbezogene_Regel
Mit der Angabe CHECK wird dabei geprüft, ob die
Integrität gewährleistet ist.
14.02.2017
25
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
JOBELMANN-SCHULE

Erweiterte Form (Beispiele):


DROP

ALTER TABLE abteilung
ADD constraint prim_s_abt primary key (abt_nr);
ALTER TABLE mitarbeiter
ADD constraint fremd_s_mita foreign key (abt_nr)
references abteilung (abt_nr);
ALTER TABLE abteilung
DROP constraint prim_s_abt;
14.02.2017
26
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
JOBELMANN-SCHULE

Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP
Löschen von Objekten:


DROP objekt obj_name [{,….}]
Beispiele:


DROP DATABASE db_name [{, db_name2} …]
Die Datenbank(en) wird/werden mit allen Objekten
gelöscht. Hierzu muss die aktuelle Datenbank die
Master-Datenbank sein!!!
DROP TABLE tab_name [{, tab_name2} …]
Die Tabelle(n) wird/werden mit allen Elementen und
Regeln gelöscht.
DROP TABLE arbeiten;
14.02.2017
27
SQL - Datendefinition
Lutz Kleinostendarp
JOBELMANN-SCHULE
CREATE
DATABASE
TABLE

Übungsphase 2:
Integritätsregeln
unique
primary key
foreign key

Gehen Sie mit Hilfe des Skriptes die
Anweisungen ALTER TABLE und DROP
(Kapitel 3.3 und 3.4) durch.
ALTER TABLE
DROP
14.02.2017
28