DatendefinitionMySQL - Druckversion

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
23.02.2015
2
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE



ALTER TABLE
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.
DROP

Beispiel: CREATE DATABASE testdb;

Besondere Berechtigung zur Erstellung einer
Datenbank erforderlich
siehe hierzu das Kapitel Sicherheitskonzepte

23.02.2015
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
23.02.2015
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}]...)
23.02.2015
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
23.02.2015
6
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
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);
DROP
23.02.2015
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
23.02.2015
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
23.02.2015
- entspricht autowert
- Integritätsregeln
9
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
JOBELMANN-SCHULE


ALTER TABLE
DROP




Integritätsregeln (integrity constraints) dienen
dazu, unsinnige oder widersprüch-liche 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.
23.02.2015
10
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
DROP
JOBELMANN-SCHULE



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.
23.02.2015
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
23.02.2015
12
SQL - Datendefinition
Lutz Kleinostendarp
JOBELMANN-SCHULE
CREATE
DATABASE
TABLE

Beispiel für die UNIQUE-Klausel (Spaltenregel):
Integritätsregeln
unique
primary key
foreign key

CREATE TABLE verkauf
(bestell_nr integer unique null,
name char(20) null);

Jede Bestellnummer darf nur einmal vorkommen,
dies gilt auch für den NULL-Wert!
Die zweite Eingabe des selben Wertes wird vom
System abgewiesen.
Die Regel erhält vom Server automatisch einen
impliziten Namen.
ALTER TABLE
DROP


23.02.2015
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
23.02.2015
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));
23.02.2015
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);
23.02.2015
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

23.02.2015
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));
23.02.2015
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));
23.02.2015
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 KEY-Klauseln
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.
23.02.2015
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
23.02.2015
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
23.02.2015
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.
23.02.2015
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}…];

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.
ALTER TABLE
DROP
23.02.2015
24
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
JOBELMANN-SCHULE

Erweiterte Form:

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.
DROP
23.02.2015
25
SQL - Datendefinition
Lutz Kleinostendarp
CREATE
DATABASE
TABLE
Integritätsregeln
unique
primary key
foreign key
ALTER TABLE
JOBELMANN-SCHULE

Erweiterte Form (Beispiele):

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;
DROP
23.02.2015
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;
23.02.2015
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
23.02.2015
28