SQL-Einführung Teil 1

SQL-Einführung
Teil 1
Prof. Dr. Waldemar Rohde
Dipl.-Ing. Jörg Höppner
19.10.2006
1
Historie
1974
SEQUEL als Abfragesprache des Systems R von IBM wird als nicht kommerzielles System
erstmals erstellt.
1976
System R , der erste Prototyp einer relationalen Datenbank, entstand
1979
SEQUEL SQL wird als Sprache für DB2 von IBM erstmalig für kommerzielle Zwecke
angeboten.
1980
ORACLE, INFORMIX und andere Hersteller bieten erstmals relationale Datenbanken mit
SQL an.
1981
SQL/DS von IBM unter DOS/VSE .
1982
ANSI gründet ein Komitee, um SQL zu standardisieren.
1983
SQL/DS wird von IBM mit DB2 unter MVS angeboten.
1986
Der erste SQL-Standard wird von der ANSI verabschiedet.
ab 1987
SQL entwickelt sich zum Industriestandard.
ab 1987
viele neue Datenbankanbieter: INGRES, SYBASE, etc.
1989
Verabschiedung des SQL1-Standards von der ISO-Behörde.
1992
SQL 2 Standard wird von der ISO-Behörde verabschiedet.
1999
Verabschiedung von SQL 1999 als Vorläufer von SQL 2003
2003
SQL 2003 wurde verabschiedet
(19.10.2006) 2
SQL - Structured Query Language

Standard SQL 1



Datendefinitionssprache ( Data Definition Language, DDL) mit den
Anweisungen CREATE TABLE, CREATE VIEW etc.
Sprache zur Definition von Speicherstrukturen, Benutzerrechten und
anderen Verwaltungsaufgaben
(Data Storage Definition Language (DSDL) und Data Administration
Language, (DAL))
Datenmanipulationssprache (Data Manipulation Language, DML), zu
der auch die Anfragen zählen.
(19.10.2006) 3
SQL - Structured Query Language

Standard SQL 2




Einführung von Domänen, einigen neue Datentypen, die
Veränderungen von Tabellen mit ALTER TABLE und Transaktionen
zum Inhalt hat.
Möglichkeit, Integritätsprüfungen vorzunehmen mit Hilfe der
Vereinbarung von CHECK-Klauseln um Gültigkeitsprüfungen für
einzufügende Werte vorzunehmen.
Festlegen von Primärschlüsseln, Fremdschlüsseln und
Zweitschlüsseln.
Die meisten Datenbanksysteme unterstützen heute diesen Standard
nicht vollständig, andererseits gehen sie in vielen Punkten weit über
den Standard hinaus.
(19.10.2006) 4
SQL - Structured Query Language

Standard SQL 3

Inhalt sind SQLJ, objektrelationale Erweiterungen, die Definition von
Stored Procedures und Datenbanktriggern. (Stored Procedures sind
Prozeduren und Funktionen, die in der Datenbank selber abgespeichert werden und unter
SQLJ versteht man die Einbettung von SQL–Anweisungen in JAVA–Programme.)


Einführung von weiteren Standarddatentypen wie BOOLEAN und
Multimediatypen, die im SQL 2 - Standard noch fehlten.
Einführung von benutzerdefinierten Datentypen, objekt-relationalen
Erweiterungen, Subtypen und Supertypen.
(19.10.2006) 5
Einsatz von SQL
SQL
DatenDefinition
DatenOrganisation
DatenManipulation
DatenSchutz
Create table
Create index
Alter table
Drop table
Drop index
…
Create tablespace
Alter tablespace
Drop tablespace
Create user
Create profile
...
Select
Insert
Update
Delete
Commit
Rollback
…
Create role
Grant priv/role
to ...
Revoke priv/role
from ...
(19.10.2006) 6
Datentypen Übersicht

Alpanumerische Objekte



Numerischer Objekte



Ziffern
Oracle: Number
Datumsobjekte



Ziffern, Zeichen
Oracle: Char, Varchar, Varchar2, Long
Ziffern, Zeichen
Oracle: Date
Großobjekte


Binäre Datenströme oder Zeichendatenströme
Oracle: BLOB, BFILE (binär), CLOB (Zeichen)
(19.10.2006) 7
Datentypen für Zeichenfolgen
CHAR
Speichert Zeichenketten mit fester Speicherreservierung
1 bis 2000 Byte
Voreinstellung ist 1 Byte
zu kurze Ketten werden mit Blanks aufgefüllt
zu lange Zeichenfolgen werden um führen Blanks gekürzt
bis der Wert passt
zu große Strings werden mit Fehlermeldung abgewiesen
impliziete Konvertierung nach VARCHAR2
zugrundeliegender Zeichensatz ist in der Datenbank definiert
(19.10.2006) 8
Datentypen für Zeichenfolgen (Forts.)
VARCHAR2
speichert Zeichenketten mit variabler Speicherreservierung
1 bis 4000 Byte
zu kurze Ketten werden mit Banks aufgefüllt
zu große Strings werden mit Fehlermeldung abgewiesen
impliziete Konvertierung nach CHAR
zugrundeliegender Zeichensatz ist in der Datenbank definiert
(19.10.2006) 9
Datentypen für Zeichenfolgen (Forts.)
CHAR / VARCHAR2
Beispiel:
Es ist ein Attribut mit dem Namen Bezeichner einmal als CHAR(8) und einmal als VARCHAR(8)
deklariert. Die Unterschiede oder auch die Vor- und Nachteile werden aus der Grafik ersichtlich.
Varchar(8) A
Neueingabe:
Char(8)
Speicher
frei
eingesetzt
anders genutzt
u t o
A u t o .. .. .. ..
neuer Speicher
wird ermittelt
Wertänderung:
Varchar(8)
Au t o x y za
Char(8)
Au t ohau s
…
Au t ohaus
(19.10.2006) 10
Datentyp für Datumsdaten
DATE
Datumsinformationen (SQL 2: nur Datum, Oracle: Datum und Uhrzeit)
Oracle speichert das Datum in 7 Bytes
Jahrhundert (20. Jahrhundert voreingestellt)
Jahr
Monat
Tag
Stunden
Minuten
Sekunden
Oracle Standarteingabeformat: DD-MON-YY (15-Dec-92)
Eingabeformat durch Umgebungsvariable NLS_DATE_FORMAT
und durch die Funktion TO_DATE() veränderbar
Beispiel:
Datenbankspeicher
31-DEC-92 15:31:32
(Benutzereingabe)
20 92 12 31 15 31 32
Datumsaufbau
Datum
Uhrzeit
(19.10.2006) 11
Datentyp für Zahlen
NUMBER
natürliche und reele Zahlen
Genauigkeit 38 Stellen
Speicherung 1.Byte Exponent 2. – 21. Byte Mantisse
pos. Wertebereich 1 * 10-130 bis 9.99…9 * 10125
neg. Wertebereich -1 * 10-130 bis 9.99…9 * 10125
Dimensionierung über 2 Parameter Number(p,s)
p Anzahl der Stellen
s Anzahl der Stellen nach dem Dezimalpunkt
führende und hängende Nullen werden nicht gespeichert
Beispiel:
Möchte man nun die 567 als Nummer speichern, so findet die Speicherung der Zahl als 5.67 * 102
statt.
byte 1
Number
567 ist nach 5670 zu ändern: Number
3
4
5
6
7
8
2 567
Speicher
Mantisse
Exponent
567 ist zu speichern:
2
frei
eingesetzt
3 567
byte 1
2
3
4
5
6
7
8
(19.10.2006) 12
Datentyp
für Zahlen (Forts.)
NUMBER
Dimensionierungsbeispiele:
Number(8,2)
8 Stellen insgesamt, wovon 2 Stellen nach dem Dezimalpunkt reserviert sind
Number(*,2 )
max. 38 Stellen insgesamt mit 2 Nachkommastellen
Number(8)
8 Stellen insgesamt ohne Nachkommstellen
Number(8,-1) 8 Stellen, wobei bis auf eine Stelle vor dem Dezimaltrennzeichen gerundet wird
Dimensionierungsauswirkungen auf die Speicherung:
Wert
7456123.89
Datentyp
Auswirkung
NUMBER
7456123.89
NUMBER(9)
7456124
NUMBER(9,2)
7456123.89
NUMBER(9,1)
7456123.9
NUMBER(6)
nicht speicherbar
NUMBER(7,-2)
7456100
(19.10.2006) 13
Datentyp für binäre Daten
RAW, LONG RAW (ORACLE Vers.7 und DB2)
Mit dem Datentyp RAW können Binärdaten bis zu einer Länge von 255 Bytes, bei LONG RAW
bis zu 2 GB Daten gespeichert werden. Diese Datentypen werden genutzt, um z.B. Grafiken,
Tondokumente oder lange Texte zu speichern.
Hinweis: Leider sind diese Multimediadatentypen nicht einheitlich definiert. In SYBASE oder MSSQL-Server beispielsweise wird IMAGE an Stelle von LONG RAW verwendet.
BLOB, BFILE (ORACLE und DB2)
Mit diesen Datentypen können binäre Objekte (ORACLE 9 bis zu 4 GB, DB2 bis zu 2 GB) für
Grafiken oder Tonaufzeichnungen in der Datenbank gespeichert werden. Außerdem stehen
einige Methoden zur Verfügung, mit denen diese Objekte auf dem Server manipuliert werden
können. Bei BLOB erfolgt die Speicherung in der Datenbank, während Datenströme vom Typ
BFILE in Dateien des Dateisystems abgelegt werden
CLOB (ORACLE und DB2)
Dieser Datentyp ist eine Modifikation des Typs BLOB, er wird für große Textobjekte mit den
entsprechenden Methoden zur Verfügung gestellt.
(19.10.2006) 14
Datendefinitionssprache (DDL)
Create
Man kann nahezu jedes
Objekt (z.B. eine Tabelle)
mithilfe von
Drop
DDL
Alter




Create erzeugen
Alter verändern
Rename umbenennen
Drop löschen
Rename
(19.10.2006) 15
DDL
CREATE TABLE Anweisung:
Syntax:
CREATE TABLE Tabellenname
(<Spaltendefinition> [, <Spaltendefinition> ...]
[, <Constraint>] );
Spaltendefinition:
<Spaltendefinition> := Spaltenname <Datentyp>
[DEFAULT <Ausdruck> ]
[NOT NULL ][NULL]
[, <Constraint>]
Beispiel:
CREATE TABLE mydemo
(
Feld1
CHAR,
Feld2
VARCHAR2(1)
);
(19.10.2006) 16
DDL
CREATE TABLE Anweisung:
Hinweis:
Bezeichnungsempfehlungen:
keine Umlaute
keine Sonderzeichen
Unterstriche anstelle von Bindestriche
(19.10.2006) 17
DDL
CONSTRAINT definiert eine Integritätsbedingung für ein Tabellenattribut
NULL / NOT NULL
legt fest, dass eine Spalte NULL-Werte haben kann oder nicht haben darf.
UNIQUE
bestimmt eine oder mehrere Spalten als eindeutige Schlüssel.
PRIMARY KEY
bestimmt eine oder mehrere Spalten als Primärschlüssel
FOREIGN KEY
bestimmt eine oder mehrere Spalten als Fremdschlüssel, die der
referentiellen Integrität genügen müssen.
REFERENCES
identifiziert den Primärschlüssel, der als FOREIGN-KEY festgelegt wurde.
ON DELETE CASCADE
referentielle Integrität wird gesichert, indem automatisch Zeilen,
die diese Integrität verletzen, entfernt werden.
CHECK
legt eine Bedingung fest, die jede Zeile der Tabelle erfüllen muß.
(19.10.2006) 18
DDL
CONSTRAINT
Syntax:
<Constraint> := { [CONSTRAINT Constraintname ]
| [NULL | NOT NULL]
| [UNIQUE | PRIMARY KEY]
| [FOREIGN KEY (Spaltenname [, Spaltenname .]... ) ]
| [REFERENCES Tabellenname. ( Spaltenname
[, Spaltenname ...] ) [ON DELETE CASCADE]]
| [CHECK (<Checkbedingung>)] }
Beispiel:
CREATE TABLE mydemo
(
Feld1
CHAR CONSTRAINT PK PRIMARY KEY,
Feld2
VARCHAR2(1) NOT NULL
);
(19.10.2006) 19
DDL
COLUMN CONSTRAINT
Beispiel:
CREATE TABLE Teile
(
TNr
NUMBER(38) NOT NULL CONSTRAINT const2 PRIMARY KEY,
Me
VARCHAR2(10),
Bezeichnung
VARCHAR2(50),
Typ
VARCHAR2(50),
Herstellkosten NUMBER,
Einkaufspreis
NUMBER,
Mindestbestand NUMBER,
Bestand
NUMBER
CONSTRAINT Check_Bestand
CHECK (Bestand > 0 ),
Lieferzeit
NUMBER,
Herstelldauer
NUMBER,
Gewicht
NUMBER,
Reserviert
NUMBER,
Verfuegbar
NUMBER,
Zeitstempel
DATE
);
(19.10.2006) 20
DDL
TABLE CONSTRAINT
Beispiel:
CREATE TABLE Struktur
(
OTeil
NUMBER(38) NOT NULL,
UTeil
NUMBER(38) NOT NULL,
Position
NUMBER(38) NOT NULL,
Menge
NUMBER NOT NULL,
Ausschuss
NUMBER,
Arbeitsgang
NUMBER(38),
Zeitstempel
DATE,
CONSTRAINT const1 PRIMARY KEY (UTeil, OTeil)
);
(19.10.2006) 21
DDL
Checkbedingung
Syntax:
<Checkbedingung> := { <Ausdruck> <Vergleichsoperator> <Ausdruck>
| <Ausdruck> [NOT] BETWEEN <Ausdruck> AND Ausdruck>
| <Ausdruck> [NOT] IN ( Konstante [, Konstante ...] )
| <alphanumerischer Ausdruck> [NOT] LIKE <Schablone>
| <Ausdruck> IS [NOT] NULL }
Beispiel:
CREATE TABLE mydemo
(
Feld1
CHAR CONSTRAINT PK PRIMARY KEY,
Feld2
VARCHAR2(1),
Feld3
NUMBER(2) CONSTRAINT CW CHECK (Feld3 > 10)
)
(19.10.2006) 22
DDL
Ausdruck
Syntax:
<Ausdruck> := {<numerischer Ausdruck>
| <alphanumerischer Ausdruck>}
<numerischer Ausdruck> :=
{ numerische Konstante
| Spaltenname
| <numerischer Ausdruck> <Operator>
<numerischer Ausdruck>
| (<numerischer Ausdruck>)
| <Funktion> (<Ausdruck>) }
<alphanumerischer Ausdruck> :=
{ alphanumerische Konstante
| Spaltenname
| <Systemvariable>
| <alphanumerischer Ausdruck> ||
<alphanumerischer Ausdruck>
| (<alphanumerischer Ausdruck> )
| <Funktion> (<Ausdruck>) }
<Operator>
:= { + | - | * | / }
<Systemvariable>
:= { USER | SYSDATE | TIME | USERID }
<Schablone>
:= Ein Wort bestehend aus Wildcards und alphanumerischen Konstanten in ‘ ‘
<Funktion>
:= nur Single-Row-Funktionen
<Vergleichsoperator>:= { < | <= | > | >= | = |<> | != }
(19.10.2006) 23
DDL
CREATE TABLE Anweisung
Beispiel:
CREATE TABLE Angestellte (
Ang_Nr
NUMBER(38) NOT NULL
CONSTRAINT t_pk PRIMARY KEY,
Abt_Nr
NUMBER(38) NULL
CONSTRAINT t_fk REFERENCES
Abteilungen(Abt_Nr),
Aufgabenbeschreibung VARCHAR2(50) NOT NULL,
Beruf
VARCHAR2(50) NOT NULL,
Nachname
VARCHAR2(50) NOT NULL,
Vorname
VARCHAR2(50) NOT NULL,
Geschlecht
CHAR(1) NOT NULL,
Eintrittsdatum
DATE,
Gehalt
NUMBER(9,2) DEFAULT 0,
Abzuege
NUMBER(9,2) DEFAULT 0,
Ort
VARCHAR2(50),
Strasse
VARCHAR2(50),
Zeitstempel
DATE DEFAULT SYSDATE
);
(19.10.2006) 24
DDL
ALTER TABLE Anweisung
Mit der ALTER TABLE Anweisung können nachträglich Tabellendefinitionen verändert werden. Am
häufigsten werden mit dieser Anweisung Spalten geändert, gelöscht oder hinzugefügt. Insbesondere
können auch CONSTRAINTS hinzugefügt, nachträglich bearbeitet oder zeitweise außer Kraft
gesetzt werden.
Syntax:
ALTER TABLE Tabellenname { ADD { (<Spaltendefinition> [, <Spaltendefinition> ... ] )
|CONSTRAINT Constraintname }
| DROP { (<Spaltendefinition> [, <Spaltendefinition> ... ] )
| CONSTRAINT Constraintname }
| MODIFY (<Spaltendefinition> [, <Spaltendefinition> ... ] )}
[ENABLE { ALL TRIGGERS
| UNIQUE (Spaltenname, [, Spaltenname ...])
| PRIMARY KEY
| CONSTRAINT Constraintname } ]
[DISABLE {
ALL TRIGGERS
| UNIQUE (Spaltenname, [, Spaltenname ...])
| PRIMARY KEY
| CONSTRAINT Constraintname } ]
(19.10.2006) 25
DDL
ALTER TABLE Anweisung
Beispiele:
ALTER TABLE Teile
ADD ( Preis NUMBER);
Diese Anweisung fügt eine neue Spalte zur Teile-Tabelle hinzu.
ALTER TABLE Teile
MODIFY (Bezeichnung VARCHAR(70));
Die maximale Länge des Attributs Bezeichnung wird auf 70 Zeichen eingestellt.
ACHTUNG!!! Mit der MODIFY-Klausel können keine Datentypumwandlungen vorgenommen werden.
ALTER TABLE teile
DROP Column Art;
Die Spalte „Art“ aus der Tabellendefinition der Teile-Tabelle wird entfernt.
(19.10.2006) 26
DDL
ALTER TABLE Anweisung
Beispiele (Forts.):
ALTER TABLE Auftraege
ADD CONSTRAINT Auf_Pk PRIMARY KEY (AuftragsNr);
Primärschlüssel für die Auftrags-Tabelle fest.
ALTER TABLE Artikel
ADD CONSTRAINT Art_T_Fk FOREIGN KEY (TNr)
REFERENCES Teile (TNr);
Legt einen Fremdschlüssel für die Artikel-Tabelle fest,
der auf die Spalte TNr in der Teile-Tabelle verweist.
ALTER TABLE EMP
ADD CONTRAINT geld CHECK(SAL > 0);
Diese Anweisung legt einen Check-Constraint fest, der Veränderung
oder Neueingaben hinsichtlich SAL > 0 überprüft.
(19.10.2006) 27
DDL
DROP TABLE Anweisung
Drop entfernt eine Tabelle mit allen gespeicherten Daten von der Datenbank.
Syntax:
DROP TABLE Tabellenname [CASCADE CONSTRAINTS];
Beispiel:
DROP TABLE Angestellte CASCADE CONSTRAINTS;
Löscht die Tabelle Angestellte und alle Datensätze aus Tabellen, die durch einen
Fremdschlüssel auf Angestellte verweisen.
(19.10.2006) 28