Datentypen und einfache Funktionen in SQL Inhalt

7. Datentypen in SQL
7-1
7. Datentypen in SQL
7-2
Inhalt
'
$
1. Klassische SQL-Datentypen
&
Datentypen und einfache Funktionen in SQL
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-3
%
2. Weitere SQL-Datentypen
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-4
Datentypen (2)
Datentypen (1)
• Jede Spalte kann nur Werte eines bestimmten Datentyps speichern.
• Datentypen definieren neben der Menge der möglichen Werte auch die Operationen auf den Werten.
• Verschiedene DBMS bieten unterschiedliche Daten-
• Im SQL-86-Standard gab es nur die Datentypfunktionen +, -, *, /.
typen, aber Strings und Zahlen verschiedener Länge
Die Existenz dieser Funktionen kann man in jedem DBMS erwarten.
und Genauigkeit sind immer verfügbar.
• Moderne (objektrelationale) Systeme bieten auch
Benutzer-definierte Datentypen (Erweiterbarkeit).
DB2, Oracle und SQL Server unterstützen Benutzer-definierte Typen.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
• Andere Funktionen sind von DBMS zu DBMS sehr
unterschiedlich.
Die Verwendung kann also zu Portabilitätsproblemen führen. Z.B. ist
der Stringkonkatenations-Operator || im SQL-92-Standard enthalten, aber SQL Server und Access verwenden stattdessen “+” und in
MySQL heißt es “concat(...)”.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-5
7. Datentypen in SQL
Datentypen (3)
Zeichenketten (1)
Kategorien von Datentypen:
CHARACTER(n):
• Zeichenkette fester Länge mit n Zeichen.
• Relativ standardisiert:
Zeichenketten (feste Länge, variable Länge)
• Daten, die in einer Spalte mit diesem Datentyp gespeichert werden, werden mit Leerzeichen bis zur
Länge n aufgefüllt.
Zahlen (Integer, Fest- und Gleitkommazahl)
• Unterstützt, aber in jedem DBMS verschieden:
Also wird immer Plattenspeicher für n Zeichen benötigt. Variiert die
Länge der Daten stark, sollte man VARCHAR verwenden, siehe unten.
Lange Zeichenketten
Binäre Daten
• CHARACTER(n) kann als CHAR(n) abgekürzt werden.
Zeichenketten in nationalem Zeichensatz
• Wird keine Länge angegeben, wird 1 angenommen.
Datums- und Zeitwerte
Somit erlaubt “CHAR” (ohne Länge) das Speichern einzelner Zeichen.
In Access scheint CHAR ohne Länge wie CHAR(255) behandelt zu werden.
• Benutzer-definierte und DBMS-spezifische Typen.
S. Brass, A. Herrmann: Datenbanken und WWW
7-6
Universität Halle, 2014
7. Datentypen in SQL
7-7
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-8
Zeichenketten-Funktionen (1)
Zeichenketten (2)
Zeichenketten-Funktionen in Oracle:
VARCHAR(n):
• Zeichenkette variabler Länge mit bis zu n Zeichen.
• Es wird nur Speicherplatz für die tatsächliche Länge
der Zeichenkette benötigt. Die maximale Länge n
dient als Beschränkung, beeinflusst aber normalerweise das Dateiformat auf der Festplatte nicht.
• Offiziell heißt der Typ CHARACTER VARYING(n), aber
der Standard erlaubt die Abkürzung VARCHAR.
s1 ||s2
concat(s1, s2)
lower(s)
upper(s)
initcap(s)
lpad(s1,n [,s2])
rpad(s1, n [,s2])
replace(s1,s2[,s3])
translate(s1,s2,s3)
substr(s,m[,n])
instr(s1, s2[,n[,m]])
length(s)
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
s1 verkettet mit s2
s1 und s2 konkateniert
s in Kleinbuchstaben
s in Großbuchstaben
erstes Zeichen eines Wortes groß, Rest klein
s1 auf n Zeichen mit s2 von links aufgefüllt
(Defaultwert für s2 ist ein Blank)
s1 auf n Zeichen mit s2 von rechts aufgefüllt
(Defaultwert für s2 ist ein Blank)
suche s2 in s1 und ersetze ihn durch s3 bzw. NULL
in s1 werden alle Zeichen aus s2
durch solche aus s3 ersetzt
Teilstring von s ab Stelle m, n Zeichen lang
(n nicht angegeben => bis Stringende)
suche s2 in s1 und zwar ab der n-ten Stelle das m-te
Auftreten (Defaultwerte fr n und m sind 1),
Ergebnis ist die gefundene Position in s1 oder 0
die Länge von s
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-9
7. Datentypen in SQL
Zeichenketten-Funktionen (2)
7-10
Zahlen (1)
Beispiele:
• INTEGER: Vorzeichenbehaftete ganze Zahl, dezimal
• ’daten’|| ’bank’= ’datenbank’
oder binär gespeichert, Wertebereich ist implemen-
• concat(’daten’,’bank’)=’datenbank’
tierungsabhängig.
• CHARACTER_LENGTH(datenbank) = 9,
Wertebereich bei Oracle (32 Bit):
-2,147,483,648 bis 2,147,483,647
• LOWER(DATENBANK)=’datenbank’
• SMALLINT: Wie oben, Wertebereich kleiner.
• UPPER(Datenbank)= ’DATENBANK’
-32 768 (−215 ) . . +32 767 (215 −1).
• SUBSTR(’DATENBANK’,2,3)=’ATE’
S. Brass, A. Herrmann: Datenbanken und WWW
7. Datentypen in SQL
Universität Halle, 2014
7-11
S. Brass, A. Herrmann: Datenbanken und WWW
7. Datentypen in SQL
7-12
Zahlen (3)
Zahlen (2)
• NUMERIC(p,s): Vorzeichenbehaftete Zahl mit insgesamt p Ziffern (s Ziffern davon hinter dem Komma).
• NUMERIC(p): Ganze Zahl mit p Ziffern (und Vorzeichen).
• NUMBER(p): Gleitkommazahl M ∗ 10E mit mindestens
p Bits Präzision für M (−1 < M < +1).
• DOUBLE(p): doppelte Mantissenlänge und daurch größere Genauigkeit.
• andere Systeme verwenden FLOAT (ohne Parameter)
• DECIMAL(p,s) oder DEC(p,s)
als Datentyp für Gleitkommazahlen.
fast das Gleiche wie NUMERIC(p,s).
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
Oracle versteht FLOAT(p)und NUMBER(p)
Universität Halle, 2014
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-13
7. Datentypen in SQL
Inhalt
Zahlen(4)
Operationen für Zahlen in Oracle:
x + y, x - y, x * y, x / y, - x, + x
abs(a)
ceil(a)
floor(a)
mod(m,n)
power(m,n)
round(n[,m])
sign(a)
sin(a)
sqrt(a)
trunc(a[,m])
exp(n)
ln(n)
log(m,n)
7-14
1. Klassische SQL-Datentypen
'
absoluter Wert von a
kleinste ganze Zahl größer als a
größte ganze Zahl kleiner als a
m Modulo n (Rest von m geteilt durch n)
m hoch n
n auf m Stellen gerundet
Vorzeichen von a (0, 1 oder -1)
Sinus von a (weitere trigonom. Fkt. verfügbar)
Wurzel aus a
a auf m Stellen abgeschnitten
liefert e hoch n (e=2,17828...)
natürlicher Logarithmus von n zur Basis e
Logarithmus von n zur Basis m
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-15
$
2. Weitere SQL-Datentypen
&
%
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-16
Datums- und Zeit-Typen (1)
nur zur Information
• DATE: Ein Wert zwischen 0001-01-01 (1. Jan. 0001)
und 9999-12-31 (31. Dez. 9999).
Lange Zeichenketten
• LONG: Zeichenketten von bis zu 2GB Länge.
• CLOB: Character large object (“großes Zeichenobjekt”), bis zu 4GB.
• BIT(n): Bitfolgen mit genau n Bits.
• RAW(n): Binäre Daten mit der Länge von n Bytes.
• DATE-Konstanten werden als Zeichenkette der Form
YYYY-MM-DD oder DD.MM.JJJJ geschrieben und in Apostroph gesetzt, z.B. DATE ’1965-06-26’
• Trotz seines Namens speichert DATE auch die Zeit
(in Stunden, Minuten, Sekunden) als Wert hinter
dem Komma.
Wird nur ein Datum festgelegt, geht Oracle von der Uhrzeit 00:00:00am
(Mitternacht, Tagesbeginn) aus.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
7. Datentypen in SQL
7-17
7. Datentypen in SQL
Datums- und Zeit-Typen (2)
Datums- und Zeit-Typen (3)
• Oracle hat einen Typ für die Zeit: DATE.
Oracle-Funktionen:
• Das Format von Zeitangaben ist abhängig von der
Implementierung des Systems.
• Wenn das nicht bekannt ist verwendet man die
Beispiele:
to date(’2014/11/06’,’yyyy/mm/dd’) ergibt Datumswert 6. November
2013
to date(’06.11.14’,’dd.mm.yy’) ergibt den gleichen Datumswert.
Universität Halle, 2014
7. Datentypen in SQL
to date(d, format-maske)
add months(d,n)
last day(d)
months between(d1, d2)
round(d[,fmt])
Funktion to date(’Datumsstring’,’Format’)
S. Brass, A. Herrmann: Datenbanken und WWW
7-18
7-19
sysdate()
trunc(d[,fmt])
Datum d in Form der Maske
Datum d plus n Monate
Datum des letzten Tages des
Monats, in dem d enthalten ist
Anzahl der Monate zwischen
d1 und d2
Datum d gerundet je nach
Format (Defaultwert für fmt ist
’dd’ (Tag))
aktuelles Datum und Uhrzeit
Datum d abgeschnitten je nach
Format (Defaultwert für fmt ist
’dd’ (Tag))
S. Brass, A. Herrmann: Datenbanken und WWW
7. Datentypen in SQL
Andere Datentypen
Universität Halle, 2014
7-20
Andere Funktionen
Oracle:
Oracle:
• BFILE: Referenz zu einer Betriebssystem-Datei.
Die Datei selbst wird nicht in der DB gespeichert, nur ihr Name. Im
Gegensatz zu CLOB und BLOB findet keine Transaktionsverwaltung statt.
Externe Dateien können über die Datenbank nur gelesen werden.
• ROWID: Physischer Zeiger auf eine bestimmte Zeile.
Die ROWID spezifiziert Datei, Block und Tupelnummer. Zugriffe über
die ROWID sind sehr schnell. Jede Tabelle hat eine “Pseudo-Spalte”
ROWID (sie kann wie eine normale Spalte unter SELECT und WHERE verwendet werden). ROWID-Komponenten werden z.B. mit
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) angezeigt.
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014
decode(a1,b1,e1,b2,e2 ist a1=b1 dann e1 ... sonst en
,b3,e3,...,en)
nvl(e1, e2)
ist e1 NULL dann e2 sonst e1
nvl2(e1, e2, e3)
ist e1 NULL dann e3 sonst e2
Beispiele:
decode(Gruppe,’MI14’,’Gruppe1’,’MI16’,’Gruppe2’,’ohne
Gruppe’);
nvl(comm,0) ist comm null, so wird es zur dezimalen 0
S. Brass, A. Herrmann: Datenbanken und WWW
Universität Halle, 2014