8 erzeugen und verwalten von tabellen. 10-2 ziele kennenlernen der wichtigsten datenbankobjekte...

88

Erzeugen und Verwalten von Tabellen

Erzeugen und Verwalten von Tabellen

10-2

ZieleZiele

• Kennenlernen der wichtigsten Datenbankobjekte

• Anlegen von Tabellen

• Datentypen zur Definition von Spalten kennenlernen

• Ändern von Tabellendefinitionen

• Löschen, Umbenennen und Abschneiden von Tabellen

• Kennenlernen der wichtigsten Datenbankobjekte

• Anlegen von Tabellen

• Datentypen zur Definition von Spalten kennenlernen

• Ändern von Tabellendefinitionen

• Löschen, Umbenennen und Abschneiden von Tabellen

10-3

Datenbank-ObjekteDatenbank-Objekte

Objekt Beschreibung

Tabelle Basiselement; besteht aus Zeilen und

Spalten

View Stellt eine logische Teilmenge von Daten

aus ein oder mehr Tabellen dar (Sicht)

Sequence Generiert Primärschlüsselwerte

Index Verbessert die Performance von Anfragen

Synonym Alternativer Name für Objekte

10-4

NamenskonventionenNamenskonventionen

• Beginnt mit einem Buchstaben

• Länge 1-30 Zeichen

• Enthält nur A–Z, a–z, 0–9, _, $, und #

• Name darf nicht bereits für ein anderes Objekt des gleichen Benutzers vergeben sein

• Darf kein in Oracle reserviertes Wort sein

• Beginnt mit einem Buchstaben

• Länge 1-30 Zeichen

• Enthält nur A–Z, a–z, 0–9, _, $, und #

• Name darf nicht bereits für ein anderes Objekt des gleichen Benutzers vergeben sein

• Darf kein in Oracle reserviertes Wort sein

10-5

Der CREATE TABLE BefehlDer CREATE TABLE Befehl

• Spezifiziert werden:

– Name der Tabelle

– Name, Datentyp und Länge der Spalte

• Spezifiziert werden:

– Name der Tabelle

– Name, Datentyp und Länge der Spalte

CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);

10-6

Referenzen auf Tabellen anderer Benutzer

Referenzen auf Tabellen anderer Benutzer

• Tabellen, die anderen Benutzern gehören, sind nicht im eigenen Schema zu finden.

• Zum Zugriff auf diese Tabelle ist der Name des Eigentümers als Präfix zu verwenden.

• Tabellen, die anderen Benutzern gehören, sind nicht im eigenen Schema zu finden.

• Zum Zugriff auf diese Tabelle ist der Name des Eigentümers als Präfix zu verwenden.

10-7

Die DEFAULT OptionDie DEFAULT Option• Definiert einen Standardwert (Default) für

eine Spalte während des Insert.• Definiert einen Standardwert (Default) für

eine Spalte während des Insert.

… hiredate DATE DEFAULT SYSDATE, …

• Gültige Werte sind Literale, Ausdrücke oder SQL-Funktionen.

• Illegale Werte sind Namen anderer Spalten oder Pseudospalten.

• Der Default-Datentyp muß zum Spaltentyp passen.

• Gültige Werte sind Literale, Ausdrücke oder SQL-Funktionen.

• Illegale Werte sind Namen anderer Spalten oder Pseudospalten.

• Der Default-Datentyp muß zum Spaltentyp passen.

10-8

Anlegen von TabellenAnlegen von Tabellen

SQL> CREATE TABLE dept 2 (deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13));Table created.Table created.

• Erzeugen (Create).• Erzeugen (Create).

• Prüfe das Ergebnis. • Prüfe das Ergebnis. SQL> DESCRIBE dept

Name Null? Type --------------------------- -------- --------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)

10-9

Tabellen in der Oracle DatenbankTabellen in der Oracle Datenbank

• User Tables (Benutzertabellen)

– Menge von Tabellen, die vom Benutzer erzeugt und gepflegt werden

– Enthält Benutzerinformationen

• Data Dictionary (Datenwörterbuch)

– Menge von Tabellen, die vom Oracle Server angelegt werden

– Enthält Datenbankinformationen

• User Tables (Benutzertabellen)

– Menge von Tabellen, die vom Benutzer erzeugt und gepflegt werden

– Enthält Benutzerinformationen

• Data Dictionary (Datenwörterbuch)

– Menge von Tabellen, die vom Oracle Server angelegt werden

– Enthält Datenbankinformationen

10-10

Anfragen im Data DictionaryAnfragen im Data Dictionary

• Tabellen, die dem Benutzer gehören.• Tabellen, die dem Benutzer gehören.

• Anzeige von Tabellen, Views, Synonymen und Sequenzen, die dem Benutzer gehören. • Anzeige von Tabellen, Views, Synonymen

und Sequenzen, die dem Benutzer gehören.

SQL> SELECT * 2 FROM user_tables;

SQL> SELECT * 2 FROM user_catalog;

10-11

DatentypenDatentypen

Datentyp Beschreibung

VARCHAR2(länge) Variabel lange Zeichenkette

CHAR(länge) Zeichenkette mit fester Länge

NUMBER(p,s) Variabel lange numerische Daten

DATE Datum und Uhrzeit

LONG Variabel lange Zeichenkette bis zu 2 Gigabytes

CLOB Zeichendaten bis zu 4 Gigabytes

RAW and LONG RAW Binäre Rohdaten

BLOB Binärdaten bis zu 4 Gigabytes

BFILE Binärdaten gespeichert in einer externen Datei, bis zu 4 Gigabytes

10-12

Erzeugen einer Tabelle mit Hilfe von Subquery

Erzeugen einer Tabelle mit Hilfe von Subquery

• Erzeugen einer Tabelle und Einfügen von Zeilen durch Kombination von CREATE TABLE Befehl und AS subquery Option.

• Anzahl Spalten entspricht der Anzahl der Spalten in der Subquery.

• Define columns with column names anddefault values.

• Erzeugen einer Tabelle und Einfügen von Zeilen durch Kombination von CREATE TABLE Befehl und AS subquery Option.

• Anzahl Spalten entspricht der Anzahl der Spalten in der Subquery.

• Define columns with column names anddefault values.

CREATE TABLE table [(column, column...)]AS subquery;

10-13

Erzeugen einer Tabelle mittels Subquery

Erzeugen einer Tabelle mittels Subquery

Name Null? Type ---------------------------- -------- ----- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) ANNSAL NUMBER HIREDATE DATE

Name Null? Type ---------------------------- -------- ----- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) ANNSAL NUMBER HIREDATE DATE

SQL> DESCRIBE dept30

SQL> CREATE TABLE dept30 2 AS 3 SELECT empno, ename, sal*12 ANNSAL, hiredate 4 FROM emp 5 WHERE deptno = 30;Table created.Table created.

10-14

Der ALTER TABLE BefehlDer ALTER TABLE Befehl

ALTER TABLE Befehl für:ALTER TABLE Befehl für:

• Hinzufügen einer neuen Spalte

• Ändern einer bestehenden Spalte

• Definiere Default-Wert für die neue Spalte

ALTER TABLE Befehl für:ALTER TABLE Befehl für:

• Hinzufügen einer neuen Spalte

• Ändern einer bestehenden Spalte

• Definiere Default-Wert für die neue Spalte

ALTER TABLE tableADD (column datatype [DEFAULT expr]

[, column datatype]...);

ALTER TABLE tableMODIFY (column datatype [DEFAULT expr]

[, column datatype]...);

10-15

Hinzufügen einer SpalteHinzufügen einer Spalte

DEPT30DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81...

“…“…füge füge eine neue eine neue Spalte in Spalte in die die DEPT30 DEPT30 Tabelle ein Tabelle ein …”…”

DEPT30DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81...

JOB

JOB

Neue SpalteNeue Spalte

10-16

Hinzufügen einer SpalteHinzufügen einer Spalte

• Verwendung der ADD Klausel • Verwendung der ADD Klausel

EMPNO ENAME ANNSAL HIREDATE JOB--------- ---------- --------- --------- ---- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81...6 rows selected.

EMPNO ENAME ANNSAL HIREDATE JOB--------- ---------- --------- --------- ---- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81...6 rows selected.

SQL> ALTER TABLE dept30 2 ADD (job VARCHAR2(9));Table altered.Table altered.

• Die neue Spalte wird die letzte Spalte• Die neue Spalte wird die letzte Spalte

10-17

Ändern einer SpalteÄndern einer Spalte

• Änderbar sind Datentyp, Länge und Default-Wert der Spalte.

• Eine Änderung des Default-Werts beeinflußt nur nachfolgende Ein-fügungen in die Tabelle.

• Änderbar sind Datentyp, Länge und Default-Wert der Spalte.

• Eine Änderung des Default-Werts beeinflußt nur nachfolgende Ein-fügungen in die Tabelle.

ALTER TABLE dept30MODIFY (ename VARCHAR2(15));Table altered.Table altered.

10-18

Löschen einer Table (Drop)Löschen einer Table (Drop)

• Alle Daten und die Struktur der Tabelle werden gelöscht.

• Laufende Transaktionen werden beendet.

• Alle Indexe werden gelöscht.

• Dieser Befehl kann nicht rückgängig gemacht werden.

• Alle Daten und die Struktur der Tabelle werden gelöscht.

• Laufende Transaktionen werden beendet.

• Alle Indexe werden gelöscht.

• Dieser Befehl kann nicht rückgängig gemacht werden.

SQL> DROP TABLE dept30;Table dropped.Table dropped.

10-19

Objekte umbennenObjekte umbennen

• Zur Umbenennung von Tabelle, View oder Sequenz: RENAME Befehl.

• Nur der Eigentümer kann umbennen.

• Zur Umbenennung von Tabelle, View oder Sequenz: RENAME Befehl.

• Nur der Eigentümer kann umbennen.

SQL> RENAME dept TO department;Table renamed.Table renamed.

10-20

ZusammenfassungZusammenfassung

Befehl Beschreibung

CREATE TABLE Erzeugt eine Tabelle

ALTER TABLE Ändert Tabellenstruktur

DROP TABLE Löscht Zeilen und Tabellenstruktur

RENAME Ändert den Namen von Tabelle, Sicht oder Sequenz

TRUNCATE Löscht alle Zeilen der Tabelle und gibt den Speicherbereich frei

COMMENT Hinzufügen von Kommentaren zu einer Tabelle oder View

10-21

ÜbungenÜbungen

• Anlegen neuer Tabellen

• Erzeugen neuer Tabellen mittels CREATE TABLE AS ...

• Ändern von Spaltendefinitionen

• Vorhandensein von Tabellen testen

• Löschen von Tabellen

• Ändern von Tabellen

• Anlegen neuer Tabellen

• Erzeugen neuer Tabellen mittels CREATE TABLE AS ...

• Ändern von Spaltendefinitionen

• Vorhandensein von Tabellen testen

• Löschen von Tabellen

• Ändern von Tabellen