Kapitel 4 - Datenbanksprache SQL

CREATE TABLE
4. Datenbanksprache SQL
SELECT
FROM
WHERE
‣ Standard-Sprache für das Arbeiten mit relationalen Datenbanken:
Structured Query Language
• Datendefinition:
Anlegen, Ändern und Löschen von Datenbankstrukturen
• Datenmanipulation: Einfügen, Ändern und Löschen von Datensätzen
• Datenabfrage:
Abfragen von Datensätzen
4.1. Datendefinition
Bevor man Daten in einer DB ablegen kann, muss diese zunächst angelegt, ein Schema sowie und
Tabellenstrukturen definiert werden.
4.1.1. Schema und Tabellen
‣ alle Definitionen für relationale Datenbank werden im relationalen Schema gesammelt
➡ zunächst leeres Schema definieren:
CREATE SCHEMA
<Schema-Name>
➡ Datenbankschema löschen:
DROP SCHEMA
<Schema-Name>
CASCADE | RESTRICT
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
1
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
Sobald ein Schema definiert ist, können die normalisierten Tabellen angelegt werden:
➡ Tabelle (Relation) definieren:
CREATE TABLE <Tabellen-Name>
( <Attribut-Definitionen>, [<Tabellen-Integritätsbedingungen>] )
➡ Attribut definieren (<Attribut-Definition>):
<Attribut-Name>
<Datentyp>
[<Attribut-Integritätsbedingungen>]
4.1.2. Datentypen und weitere Integritätsbedingungen
➡ Basis-Datentypen für Attribute (<Datentyp>):
• INT | INTEGER: ganze Zahl zwischen -231 und 231-1
• SMALLINT: ganze Zahl zwischen -215 und 215-1
• DEC | DECIMAL | DECIMAL(p, [q]): Dezimalzahl (mit insgesamt p Stellen, davon q
Dezimalstellen)
• NUM | NUMERIC | NUMERIC(p, [q]): (wie DECIMAL)
• FLOAT | FLOAT(p): Zahl in (4 Byte-) Fließkomma-Darstellung mit Vorzeichen
• REAL | DOUBLE PRECISION: reelle Zahl (double precision ist genauer)
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
2
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
•
•
•
•
•
•
•
CHAR(n): alphanumerische Zeichenkette mit fester Länge n
VARCHAR(n): alphanumerische Zeichenkette mit variabler Länge (maximal n)
CLOB(m [K|M|G]): Text mit max. m Zeichen (angebbar in Kilo-, Mega- oder Giga-Byte)
BOOLEAN: Wahrheitswerte true, false, unknown
DATE: Datum - als Zeichenkette in der Form JJJJ-MM-TT
TIME: (Uhr-)Zeit - als Zeichenkette in der Form HH:MM:SS
TIMESTAMP: Zeitstempel - als Zeichenkette in der Form JJJJ-MM-TT HH:MM:SS
➡ Integritätsbedingungen für Attribute (<Attribut-Integritätsbedingungen>):
•
•
•
•
UNIQUE
NOT NULL
DEFAULT <Default-Wert>
CHECK (<<Attribut-Name> | VALUE> <Prüfbedingung 1>
[AND|OR … AND|OR <<Attribut-Name> | VALUE> <Prüfbedingung n>])
• PRIMARY KEY
• FOREIGN KEY REFERENCES <Tabellen-Name> (<Attribut-Name>)
[ON DELETE CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION]
[ON UPDATE CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION]
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
3
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
➡ Prüfbedingungen für CHECK-Klausel:
• <Vergleichsoperator> <Wert-Ausdruck>
mit:
<Vergleichsoperator>: = | < | <= | > | >= | <>
<Wert-Ausdruck>: <konkreter Wert> | <arithm. Ausdruck> | <skalare SELECT-Anweisung>
• [NOT] IN (<Werte-Aufzählung> | <SELECT-Anweisung>)
• [NOT] BETWEEN <Untergrenze> AND <Obergrenze>
• [NOT] LIKE <Textmuster> [ESCAPE <Sonderzeichen>]
➡ Integritätsbedingungen für Tabelle (= Attribut-übergreifend):
•
•
•
•
•
<Attribut-Name> WITH OPTIONS <Attribut-Integritätsbedingungen>
UNIQUE (<Liste Attribut-Namen>)
CHECK (<Selektionsbedingung 1> [AND|OR … AND|OR <Selektionsbedingung n>])
PRIMARY KEY (<Liste Attribut-Namen>)
FOREIGN KEY (<Liste Attribut-Namen>) REFERENCES <Tabellen-Name> (<Liste Attribut-Namen>)
[ON DELETE CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION]
[ON UPDATE CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION]
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
4
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
Beispiel 4-1:
Eine Kaufhaus-Kette möchte seine in den Filialen verkauften Waren in einer relationalen Datenbank
verwalten, die aus den folgenden Relationen bestehen soll:
→ Kunde mit den Attributen
-
KNr (Kundennummer, alphanumerisch) als Primärschlüssel,
Name (alphanumerisch), der immer mit einem Wert belegt sein soll,
Wohnort (alphanumerisch) und
Bonität, die zwischen 0 (für schlechter Kunde) und 100 (für guter Kunde) liegt und beim Einfügen eines
Kunden mit dem Wert 50 belegt werden soll,
→ Artikel mit den Attributen
- ANr (Artikelnummer, alphanumerisch) als Primärschlüssel,
- Bezeichner, der ebenfalls immer mit einem Wert belegt sein soll,
- Preis, der immer mit einem Wert belegt und größer 0 sein soll,
→ Filiale mit den Attributen
- FNr (Filialnummer, alphanumerisch) als Primärschlüssel und
- Ort, der immer mit einem Wert belegt und eindeutig sein soll (d.h. es gibt pro Ort max. eine Filiale),
→ Kauf mit den Attributen
- KNr (Kundennummer) als Teil des Primärschlüssels und Fremdschlüssel zum Attribut KNr der Relation
Kunde,
- ANr (Artikelnummer) als Teil des Primärschlüssels und Fremdschlüssel zum Attribut ANr der Relation
Artikel,
- FNr (Filialnummer) als Teil des Primärschlüssels und Fremdschlüssel zum Attribut FNr der Relation Filiale,
- Menge, die immer mit einem Wert belegt und größer 0 sein soll, und
- Datum.
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
5
CREATE TABLE
4.1. Datendefinition
CREATE SCHEMA
SELECT
FROM
WHERE
→ Beispiel
Verkauf;
CREATE TABLE Kunde
(
KNr
VARCHAR (7) PRIMARY KEY,
Name
VARCHAR (30) NOT NULL,
Wohnort
VARCHAR (60),
Bonität
SMALLINT
DEFAULT 50
CHECK (VALUE BETWEEN 0 AND 100)
CREATE TABLE Artikel
(
ANr
VARCHAR (5) PRIMARY KEY,
Bezeichner
VARCHAR (20) NOT NULL,
Preis
DECIMAL (7,2)
NOT NULL
CREATE TABLE
(
FNr
Ort
Filiale
VARCHAR (3) PRIMARY KEY,
VARCHAR (60) NOT NULL
UNIQUE
CREATE TABLE Kauf
(
KNr
VARCHAR (7) FOREIGN KEY
ANr
VARCHAR (5) FOREIGN KEY
FNr
VARCHAR (3) FOREIGN KEY
Menge
SMALLINT
NOT NULL,
Datum
DATE ,
PRIMARY KEY (KNr, ANr, FNr),
Menge WITH OPTIONS CHECK (VALUE >
CHECK (VALUE > 0)
);
);
„Kauf“ muss zuletzt
);
definiert werden, da
sonst die FS nicht
definiert werden
REFERENCES Kunde (KNr), können
REFERENCES Artikel (ANr),
REFERENCES Filiale (FNr),
0)
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
);
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
6
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
4.1.3. Änderung einer Tabelle
ALTER TABLE <Tabellen-Name>
ADD [COLUMN] <Attribut-Name> <Datentyp> [<Attribut-Integritätsbedingungen>]|
DROP [COLUMN] <Attribut-Name> CASCADE | RESTRICT
Die ALTER TABLE Anweisung ist zwingend erforderlich, wenn zwei Relationen definiert werden
sollen, die sich über FS-Attribute gegenseitig referenzieren.
Beispiel 4-2:
In einem Unternehmen sollen die Angestellten in einer eigenen Relation und die Abteilungen in einer
eigenen Relation verwaltet werden.
Für die Angestellten soll die eindeutige Personalnummer (PersNr, 10-stellige Zeichenkette) als
Primärschlüssel, der Name (max. 30-stellige Zeichenkette, muss immer Wert aufweisen) und die
Abteilungsnummer (AbtNr, Zahl) als Fremdschlüssel mit Referenz auf das gleichnamige Attribut in der
Relation Abteilung geführt werden.
Für die Abteilungen soll die eindeutige Abteilungsnummer (AbtNr, Zahl) als Primärschlüssel, der Name
(max. 25-stellige Zeichenkette, muss immer Wert aufweisen) und die Personalnummer des
Abteilungsleiters (Leiter) als Fremdschlüssel mit Referenz auf das Attribut PersNr in der Relation
Angestellter geführt werden.
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
7
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
CREATE SCHEMA
Ang_Abt_Datenbank;
CREATE TABLE Angestellter
(
PersNr CHAR (10)
PRIMARY KEY,
Name
VARCHAR (30) NOT NULL
);
CREATE TABLE Abteilung
(
AbtNr
SMALLINT
PRIMARY KEY,
Name
VARCHAR (25) NOT NULL,
Leiter CHAR (10)
FOREIGN KEY REFERENCES Angestellter (PersNr)
ALTER TABLE Angestellter
ADD
AbtNr
);
SMALLINT FOREIGN KEY REFERENCES Abteilung (AbtNr);
➡ Tabelle löschen:
DROP TABLE
<Tabellen-Name>
CASCADE | RESTRICT
Löscht nicht nur den Inhalt einer Tabelle, sondern auch deren Definition.
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
8
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
Bisher: SQL-Anweisungen für die Definition eines relationalen Datenbankschemas auf der
logischen Ebene.
Zusätzlich: SQL-Anweisungen auf der externen Ebene (View) und internen Ebene (Index).
4.1.4. Views und Indexe
‣ View = Sicht eines Benutzers auf die Datenbank
• Daten aus Basisrelationen und anderen Views extrahieren
• Inhalt einer View nicht permanent in Datenbank gespeichert (= virtuelle Relation)
➡ View definieren:
CREATE VIEW <View-Name> [( <Liste Attribut-Namen> )]
AS <SELECT-Anweisung>
Extraktion der
gewünschten Daten
mittels SELECT
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
9
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition → Views
Beispiel 4-3:
Die Personalabteilung benötigt eine bestimmte Sicht auf die beiden Tabellen Angestellter und
Abteilung aus Beispiel 4-2. Es wird eine Aufstellung benötigt, welche Abteilung von welchem
Mitarbeiter geleitet wird. Dies soll in einer View Abteilungsleiter mit den Attributen Abteilung (Name
der Abteilung) und Leiter (Name des Abteilungsleiters) ausgegeben werden.
a. Basistabellen
Angestellter
Abteilung
PersNr
Name
AbtNr
AbtNr
Name
Leiter
007
Bond
A-1
A-1
Außendienst
007
0815
Smith
A-1
P-0
Personal
4711
4711
Walker
P-0
4712
Miller
P-0
b. View
Abteilungsleiter
Abteilung
Leiter
Außendienst
Bond
Personal
Walker
Abb. 4-1: Relationale Datenbank mit Basistabellen und View
➡ View löschen:
DROP VIEW
<View-Name>
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
CASCADE | RESTRICT
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
10
CREATE TABLE
SELECT
FROM
WHERE
4.1. Datendefinition
‣ Index = Attributwertverzeichnis (Inhalts- oder Stichwortverzeichnis)
• dient zum schnellen Zugriff auf einen bestimmten Datensatz
• liefert zu einem bestimmten Attributwert die Speicheradresse des zugehörigen Datensatzes
• Index kann über einzelnes Attribut oder über eine Attributkombination definiert werden
(Hinweis: Index nur auf der logischen Ebene definierbar, also nicht für Views!)
• Index im SQL-Standard nicht vorgesehen, aber in Datenbanksystemen unentbehrlich
➡ Index definieren:
CREATE [UNIQUE] INDEX <Index-Name>
ON <Tabellen-Name> (<Attribut-Name 1> [ASC | DESC], …,
<Attribut-Name n> [ASC | DESC] )
Bsp.: CREATE INDEX PersNrIndex ON Angestellter (PersNr);
➡ Index löschen:
DROP INDEX
<Index-Name>
© Duale Hochschule Baden-Württemberg Stuttgart, Prof. Dr. Nikolai Preiß, Holger Seubert
Datenbanksysteme – Kap. 4 Datenbanksprache SQL
11