Create-Table-Befehl

Create-Table-Befehl

CREATE TABLE Tabellenname

( { Spalte { Datentyp | Gebietsname } [ Spaltenbedingung [ … ] ] | Tabellenbedingung }

[ , … ] )

Liste der wichtigsten Datentypen in SQL

INTEGER INT

Ganzzahl

SMALLINT Ganzzahl

NUMERIC(x,y) x stellige Dezimalzahl mit y Nachkommastellen

DECIMAL(x,y) mindestens x stellige Dezimalzahl mit y Nachkomma- stellen

FLOAT (x) Gleitpunktzahl mit x Nachkommastellen CHARACTER(n)

CHAR(n)

Zeichenkette der Länge n

CHARACTER VARYING(n) VARCHAR(n)

Variable Zeichenkette mit bis zu n Zeichen

DATE Datumsfeld

Zeichenketten in SQL

Es gibt zwei verschiedene Zeichenketten-Datentypen:

Char(n): Zeichenkette hat genau n Zeichen Auffüllen mit Leerzeichen

VarChar(n): Zeichenketten hat maximal n Zeichen Kein Auffüllen mit Leerzeichen

in Oracle: VarChar2(n) Beispiel:

UPDATE Personal SET Ort = 'Regensburg'

WHERE Persnr = 7;

SELECT Persnr, Name FROM Personal

WHERE Ort = 'Regensburg';

Ort vom Typ Char(20): Persnr 7 wird nicht gefunden!

Ort vom Typ VarChar(20): Persnr 7 wird gefunden!

Datumsfeld in SQL, Oracle, MySQL, MS-Access

Variable (String) in Datum verwandeln:

SQL: CAST z.B. CAST( '2002-11-04' AS DATE ) DATE z.B. DATE '2002-11-04'

Oracle: TO_DATE z.B. TO_DATE( '04-Nov-02', 'YY-MMM-DD' ) MySQL: STR_TO_DATE

z.B. STR_TO_DATE( '4.11.2004', '%d.%m.%y') MS-Access: CDATE z.B. CDATE( '4.12.02' )

Heutiges Datum:

SQL: Current_Date (auch Oracle, MySQL) Oracle: Sysdate

MS-Access: Date()

Spaltenbedingung (Constraints, Teil 1)

[ CONSTRAINT Bedingungsname ] NOT NULL

[ CONSTRAINT Bedingungsname ] { PRIMARY KEY | UNIQUE } [ CONSTRAINT Bedingungsname ]

REFERENCES Tabellenname [ ( Spalte [ , ... ] ) ]

[ ON DELETE { NO ACTION | CASCADE | SET NULL } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL } ] [ CONSTRAINT Bedingungsname ]

CHECK ( Bedingung )

Tabellenbedingung (Constraints, Teil 2)

[ CONSTRAINT Bedingungsname ]

{ PRIMARY KEY | UNIQUE } ( Spalte [ , … ] ) [ CONSTRAINT Bedingungsname ]

FOREIGN KEY ( Spalte [ , … ] )

REFERENCES Tabellenname [ ( Spalte [ , ... ] ) ]

[ ON DELETE { NO ACTION | CASCADE | SET NULL } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL } ] [ CONSTRAINT Bedingungsname ]

CHECK ( Bedingung )

Hinweise zu Constraints

¾ Jede Relation enthält genau eine PRIMARY-KEY-Bedingung

¾ Jeder Fremdschlüssel wird genau einmal mit der (FOREIGN-KEY-) REFERENCES-Bedingung angegeben

¾ Jeder alternative Schlüssel wird mittels der UNIQUE-Bedingung identifiziert

¾ Alle Attribute (außer Primärschlüssel), die nicht NULL werden dürfen, werden mit der NOT-NULL-Bedingung versehen

¾ Alle weiteren bekannten und vermutlich für die gesamte Lebens- dauer der Datenbank gültigen Einschränkungen werden mit der CHECK-Bedingung angegeben

Beispiel

CREATE TABLE Personal

( Persnr INTEGER PRIMARY KEY, Name CHAR (25) NOT NULL, Ort CHAR (15),

Vorgesetzt INTEGER REFERENCES Personal

ON DELETE SET NULL

ON UPDATE CASCADE,

Gehalt NUMERIC (8,2)

CHECK(Gehalt BETWEEN 800 AND 9000),

CONSTRAINT MaxVerdienst

CHECK(Gehalt >= Coalesce((6-Beurteilung)*100, 300) ) ;

Alter-Table-Befehl

ALTER TABLE Tabellenname

{ ADD [ COLUMN ] Spalte { Datentyp | Gebietsname }

[ Spaltenbedingung [ … ] ]

| DROP [ COLUMN ] Spalte { RESTRICT | CASCADE } | ADD Tabellenbedingung

| DROP CONSTRAINT Bedingungsname

{ RESTRICT | CASCADE }

Hinweise:

¾ Der ALTER-TABLE-Befehl fügt genau ein Attribut oder eine Bedin- gung hinzu oder entfernt genau ein Attribut oder eine Bedingung

¾ In Oracle muss in ADD COLUMN der Bezeichner COLUMN weg- gelassen werden, in DROP COLUMN hingegen zwingend geschrie- ben werden (außer die Spalte wird in Klammern gesetzt)!

Alter-Table-Befehl

DROP TABLE Tabellenname { RESTRICT | CASCADE }

Beispiele:

DROP TABLE Personal CASCADE ;

Hinweis:

In Oracle 9i lautet die Syntax:

DROP TABLE Tabellenname [ CASCADE CONSTRAINTS ]

Indizes (SQL1)

CREATE [UNIQUE] INDEX Name ON

Tabellenname ( { Spalte [ ASC | DESC ] } [ , ... ] )

DROP INDEX Name

Beispiel:

CREATE UNIQUE INDEX IPers ON Personal (Persnr);

Sichten (VIEW)

CREATE VIEW Sichtname [ ( Spalte [ , ... ] ) ] AS Select-Befehl

[ WITH CHECK OPTION ]

DROP VIEW Sichtname { RESTRICT | CASCADE }

Hinweis:

In Oracle 9i lautet die Syntax:

DROP VIEW Sichtname [ CASCADE CONSTRAINTS ]

Beispiel zu Sichten

CREATE VIEW VAuftrag (AuftrNr, Datum, Kundenname, Persname, Preis) AS

SELECT A.AuftrNr, Datum, Kunde.Name, P.Name, SUM (Gesamtpreis) FROM Auftrag A, Kunde, Personal P, Auftragsposten

WHERE Kunde.Nr = A.Kundnr AND P.Persnr = A.Persnr

AND A.AuftrNr = Auftragsposten.AuftrNr

GROUP BY A.AuftrNr, Datum, Kunde.Name, P.Name;

Sicht VAuftrag

AuftrNr Datum Kundenname Persname Preis 1 04.08.1998 Fahrrad Shop Anna Kraus 1400.00 2 06.09.1998 Maier Ingrid Johanna Köster 4450.00 3 07.10.1998 Rafa – Seger KG Anna Kraus 1570.00 4 18.10.1998 Fahrräder Hammerl Johanna Köster 1573.00 5 06.11.1998 Fahrrad Shop Anna Kraus 125.00

Beispiel zu Sichten (Fortsetzung)

Auf Sichten kann grundsätzlich wie auf Tabellen zugegriffen werden:

¾ lesend ohne Einschränkung

¾ schreibend, falls Sicht änderbar ist

Beispiel:

SELECT *

FROM VAuftrag

WHERE Persname = 'Johanna Köster' ;

Änderbare Sichten

Update-, Delete- und Insert-Befehle sind nicht erlaubt, falls der Select- Befehl in der Sichtdefinition

¾ in der From-Klausel auf mehr als eine Relation zugreift,

¾ eine Group-By-Klausel enthält,

¾ eine Distinct-Angabe enthält,

¾ die Spaltenliste nicht nur aus einzelnen Spaltennamen besteht,

¾ mindestens einen der Operatoren Union, Intersect oder Except enthält.

Eine solche Sicht heißt nicht änderbar.

Eine Sicht ist ebenfalls nicht änderbar, wenn in der From-Klausel des definierenden Select-Befehls auf eine nicht änderbare Sicht zugegriffen wird.

With-Check-Option

In einer änderbaren Sicht mit der With-Check-Option werden Änderungen in der Sicht nur dann ausgeführt, wenn die betroffenen Tupel weiterhin Elemente der Sicht bleiben.

CREATE TABLE Vereinsmitglieder (

( Nr INTEGER PRIMARY KEY, Name Char(20) NOT NULL, …

Alter SMALLINT NOT NULL CHECK (Alter BETWEEN 10 AND 99), …

) ;

CREATE VIEW Jugend AS

SELECT * FROM Vereinsmitglieder WHERE Alter < 21 WITH CHECK OPTION ;

Schemata

CREATE SCHEMA [ Schemaname ] [ Schemaelement [ , ... ] ]

DROP SCHEMA Schemaname { CASCADE | RESTRICT }

Schemaelement:

Create-Table-Befehl, Create-View-Befehl, Grant-Befehl, Revoke-Befehl

Datenbanken und Benutzer in Oracle

Anlegen einer Datenbank (Admin-Rechte):

¾ Oberfläche der Oracle-Administration

¾ CREATE DATABASE

Beispiel: Datenbank RFHS8012_ORA9I

Anlegen von Benutzern (Admin-Rechte):

CREATE USER …

Beispiel: Create User sce39014 identified by xxx ;

Benutzer in Oracle entsprechen in etwa dem SQL-Schema!

Systemtabellen in SQL: INFORMATION_SCHEMA

Relation enthält

SCHEMATA alle Schemata

DOMAINS alle Gebiete

TABLES alle Basisrelationen

VIEWS alle Sichten

VIEW_TABLE_USAGE alle Abhängigkeiten der Sichten von Relationen VIEW_COLUMN_USAGE alle Abhängigkeiten der Sichten von Spalten COLUMNS alle Spaltennamen aller Basisrelationen

TABLE_PRIVILEGES alle Zugriffsrechte auf Relationen

COLUMN_PRIVILEGES alle Zugriffsrechte auf Spalten aller Relationen DOMAIN_CONSTRAINTS alle Gebietsbedingungen für alle Gebiete

TABLE_CONSTRAINTS alle Tabellenbedingungen aller Relationen REFERENTIAL_CONSTRAINTS alle referentiellen Bedingungen

CHECK_CONSTRAINTS alle Check-Bedingungen aller Relationen ASSERTIONS alle allgemeinen Bedingungen

Systemtabellen in Oracle

Relation enthält

DICTIONARY Zusammenfassung über alle Systemrelationen USER_TABLES alle Relationen des Benutzers

USER_TAB_COLUMNS alle Attribute aller Relationen des Benutzers USER_VIEWS alle Sichten des Benutzers

USER_CONSTRAINTS alle Spalten- und Tabellenbedingungen

USER_CONS_COLUMNS alle Attribute, die Spalten- und Tabellenbedin- gungen enthalten

USER_INDEXES alle Indexe in Relationen des Benutzers USER_IND_COLUMNS alle Attribute, die Indexe besitzen

USER_TAB_PRIVS alle Privilegien in Bezug auf Relationen USER_COL_PRIVS alle Privilegien in Bezug auf Attribute USER_TABLESPACES alle Tablespaces des Benutzers