Zum Inhalt springen

Datentypen

Links hinzufügen
Aus Wikibooks

Seitentitel: Einführung in SQL: Datentypen
(Einführung in SQL: Datentypen)
(Einführung in SQL: Datentypen)


SQL kennt verschiedene Arten von Datentypen: vordefinierte, konstruierte und benutzerdefinierte. Diese Arten und ihre Verwendungen werden in diesem Abschnitt erklärt.

Vordefinierte Datentypen

[Bearbeiten]

Laut SQL-Standard sind die folgenden Datentypen vordefiniert. Die fettgedruckten Begriffe sind die entsprechenden reservierten Schlüsselwörter.

Achtung
Bei allen Datentypen weichen die SQL-Dialekte mehr oder weniger vom Standard ab.

Dies betrifft vor allem die folgenden Punkte:

  • bei den möglichen Werten, z. B. dem maximalen Wert einer ganzen Zahl oder der Länge einer Zeichenkette
  • bei der Bearbeitung einzelner Typen; z. B. kennt Firebird noch nicht sehr lange den BOOLEAN-Typ und musste mit dem Ersatz „ganze Zahl gleich 1“ leben
  • bei der Art der Werte, z. B. ob Datum und Zeit getrennt oder gemeinsam gespeichert sind

Zeichen und Zeichenketten

[Bearbeiten]

Es gibt Zeichenketten mit fester, variabler und sehr großer Länge.

  • CHARACTER(n), CHAR(n)

Hierbei handelt es sich um Zeichenketten mit fester Länge von genau n Zeichen. Für ein einzelnes Zeichen muss die Länge (1) nicht angegeben werden.

  • CHARACTER VARYING(n), VARCHAR(n)

Dies sind Zeichenketten mit variabler Länge bei maximal n Zeichen.

  • CHARACTER LARGE OBJECT

Hierbei handelt es sich um beliebig große Zeichenketten. Diese Variante ist relativ umständlich zu nutzen; sie wird vorwiegend für die Speicherung ganzer Textdateien verwendet.

Zu allen diesen Varianten gibt es auch die Festlegung eines nationalen Zeichensatzes durch NATIONAL CHARACTER bzw. NCHAR oder NATIONAL CHARACTER VARYING bzw. NVARCHAR. Erläuterungen dazu siehe unten im Abschnitt über Zeichensätze.

Die maximale Länge von festen und variablen Zeichenketten hängt vom DBMS ab. In früheren Versionen betrug sie oft nur 255, heute sind 32767 verbreitet.

Die maximale Feldlänge bei Zeichenketten ist für Eingabe, Ausgabe und interne Speicherung wichtig. Die DBMS verhalten sich unterschiedlich, ob am Anfang oder Ende stehende Leerzeichen gespeichert oder entfernt werden. Wichtig ist, dass bei fester Feldlänge ein gelesener Wert immer mit genau dieser Anzahl von Zeichen zurückgegeben wird und bei Bedarf rechts mit Leerzeichen aufgefüllt wird.

In der Praxis sind folgende Gesichtspunkte von Bedeutung:

  • Für indizierte Felder (also Spalten, denen ein Index zugeordnet wird) sind feste Längen vorzuziehen; beachten Sie aber die nächsten Hinweise.
  • Als CHAR(n), also mit fester Länge, sind Felder vorzusehen, deren Länge bei der überwiegenden Zahl der Datensätze konstant ist. Beispiel: die deutschen Postleitzahlen mit CHAR(5).
  • Als VARCHAR(n), also mit variabler Länge, sind Felder vorzusehen, deren Länge stark variiert. Beispiel: Namen und Vornamen mit VARCHAR(30).
  • In Zweifelsfällen ist pragmatisch vorzugehen. Beispiel: Die internationalen Postleitzahlen (Post-Code, Zip-Code) benötigen bis zu 10 Zeichen. Wenn eine Datenbank überwiegend nur deutsche Adressen enthält, passt VARCHAR(10) besser, bei hohem Anteil von britischen, US-amerikanischen, kanadischen und ähnlichen Adressen ist CHAR(10) zu empfehlen.

Zahlen mit exakter Größe

[Bearbeiten]

Werte dieser Datentypen werden mit der genauen Größe gespeichert.

  • INTEGER bzw. INT

Ganze Zahl mit Vorzeichen. Der Größenbereich hängt von der Implementierung ab; auf einem 32-bit-System entspricht es meistens ±231-1, genauer von –2 147 483 648 bis +2 147 483 647.

  • SMALLINT

Ebenfalls ein Datentyp für ganze Zahlen, aber mit kleinerem Wertebereich als INTEGER, oft von -32 768 bis +32 767.

  • BIGINT

Ebenfalls ein Datentyp für ganze Zahlen, aber mit größerem Wertebereich als INTEGER. Auch der SQL-Standard akzeptiert, dass ein DBMS diesen Typ nicht kennt.

  • NUMERIC(p,s) sowie DECIMAL(p,s)

Datentypen für Dezimalzahlen mit exakter Speicherung, also „Festkommazahlen“, wobei p die Genauigkeit und s die Anzahl der Nachkommastellen angibt. Dabei muss 0 ≤ s ≤ p sein, und s hat einen Defaultwert von 0. Der Parameter s = 0 kann entfallen; der Vorgabewert für p hängt vom DBMS ab.

Diese Dezimalzahlen sind wegen der genauen Speicherung z. B. für Daten der Buchhaltung geeignet. Bei vielen DBMS gibt es keinen Unterschied zwischen NUMERIC und DECIMAL.

Zahlen mit „näherungsweiser“ Größe

[Bearbeiten]

Werte dieser Datentypen werden nicht unbedingt mit der genauen Größe gespeichert, sondern in vielen Fällen nur näherungsweise.

  • FLOAT, REAL, DOUBLE PRECISION

Diese Datentypen haben grundsätzlich die gleiche Bedeutung. Je nach DBMS gibt FLOAT(p,s) die Genauigkeit oder die Anzahl der Dezimalstellen an; auch der Wertebereich und die Genauigkeit hängen vom DBMS ab.

Diese „Gleitkommazahlen“ sind für technisch-wissenschaftliche Werte geeignet und umfassen auch die Exponentialdarstellung. Wegen der Speicherung im Binärformat sind sie aber für Geldbeträge nicht geeignet, weil sich beispielsweise der Wert 0,10 € (entspricht 10 Cent) nicht exakt abbilden lässt. Es kommt immer wieder zu Rundungsfehlern.

Zeitpunkte und Zeitintervalle

[Bearbeiten]

Für Datum und Uhrzeit gibt es die folgenden Datentypen:

  • DATE

Das Datum enthält die Bestandteile YEAR, MONTH, DAY, wobei die Monate innerhalb eines Jahres und die Tage innerhalb eines Monats gemeint sind.

  • TIME

Die Uhrzeit enthält die Bestandteile HOUR, MINUTE, SECOND, wobei die Minute innerhalb einer Stunde und die Sekunden innerhalb einer Minute gemeint sind. Sehr oft werden auch Millisekunden als Bruchteile von Sekunden registriert.

  • TIMESTAMP

Der „Zeitstempel“ enthält Datum und Uhrzeit zusammen.

Zeitangaben können WITH TIME ZONE oder WITHOUT TIME ZONE deklariert werden. Ohne die Zeitzone ist in der Regel die lokale Zeit gemeint, mit der Zeitzone wird die Koordinierte Weltzeit (UTC) gespeichert.

Bei Datum und Uhrzeit enden die Gemeinsamkeiten der SQL-Dialekte endgültig; sie werden unterschiedlich mit „eigenen“ Datentypen realisiert. Man kann allenfalls annehmen, dass ein Tag intern mit einer ganzen Zahl und ein Zeitwert mit einem Bruchteil einer ganzen Zahl gespeichert wird.

Beispiele:

Datenbanksystem Datentyp Geltungsbereich Genauigkeit
MS-SQL Server 2005 datetime 01.01.1753 bis 31.12.9999 3,33 Millisekunden
  smalldatetime 01.01.1900 bis 06.06.2079 1 Minute
MS-SQL Server 2008 date 01.01.0001 bis 31.12.9999 1 Tag
  time 00:00:00.0000000 bis 23:59:59.9999999 100 Nanosekunden
  datetime 01.01.0001 bis 31.12.9999 3,33 Millisekunden
  smalldatetime 01.01.1900 bis 06.06.2079 1 Minute
Firebird DATE 01.01.0100 bis 29.02.32768 1 Tag
  TIME 00:00 bis 23:59.9999 6,67 Millisekunden
MySQL 5.x DATETIME 01.01.1000 00:00:00 bis 31.12.9999 23:59:59 1 Sekunde
  DATE 01.01.1000 bis 31.12.9999 1 Tag
  TIME –838:59:59 bis 838:59:59 1 Sekunde
  YEAR 1901 bis 2055 1 Jahr

Bitte wundern Sie sich nicht: bei jedem DBMS gibt es noch weitere Datentypen und Bezeichnungen.

Die Deklaration von TIME bei MySQL zeigt schon: Es muss sich dabei nicht um eine Uhrzeit innerhalb eines Datums handeln, sondern kann auch einen Zeitraum, d. h. ein Intervall darstellen.

  • INTERVAL
    Ein Intervall setzt sich – je nach betrachteter Zeitdauer – zusammen aus:
    • YEAR, MONTH für längere Zeiträume (der SQL-Standard kennt auch nur die Bezeichnung "year-month-interval")
    • DAY, HOUR, MINUTE, SECOND für Zeiträume innerhalb eines Tages oder über mehrere Tage hinweg

Große Objekte

[Bearbeiten]

BLOB (Binary Large Object, binäre große Objekte) ist die allgemeine Bezeichnung für unbestimmt große Objekte.

  • BLOB

Allgemein werden binäre Objekte z. B. für Bilder oder Bilddateien verwendet, nämlich dann, wenn der Inhalt nicht näher strukturiert ist und auch Bytes enthalten kann, die keine Zeichen sind.

  • CLOB

Speziell werden solche Objekte, die nur „echte“ Zeichen enthalten, zum Speichern von großen Texten oder Textdateien verwendet.

Je nach DBMS werden BLOB-Varianten durch Sub_Type oder spezielle Datentypen für unterschiedliche Maximalgrößen oder Verwendung gekennzeichnet.

Boolean

[Bearbeiten]

Der Datentyp BOOLEAN ist für logische Werte vorgesehen. Solche Felder können die Werte TRUE (wahr) und FALSE (falsch) annehmen; auch NULL ist möglich und wird als UNKNOWN (unbekannt) interpretiert.

Wenn ein DBMS diesen Datentyp (noch) nicht kennt – wie MySQL –, dann ist mit einem der numerischen Typen eine einfache Ersatzlösung möglich (wie früher bei Interbase und Firebird); siehe unten im Abschnitt über Domains.

Konstruierte und benutzerdefinierte Datentypen

[Bearbeiten]

Diese Datentypen, die aus den vordefinierten Datentypen zusammengesetzt werden, werden hier nur der Vollständigkeit halber erwähnt; sie sind in der Praxis eines Anwenders ziemlich unwichtig.

  • ROW

Eine Zeile ist eine Sammlung von Feldern; jedes Feld besteht aus dem Namen und dem Datentyp. Nun ja, eine Zeile in einer Tabelle ist (natürlich) von diesem Typ.

  • REF

Referenztypen sind zwar im SQL-Standard vorgesehen, treten aber in der Praxis nicht auf.

  • ARRAY, MULTISET

Felder und Mengen sind Typen von Sammlungen ("collection type"), in denen jedes Element vom gleichen Datentyp ist. Ein Array gibt die Elemente durch die Position an, ein Multiset ist eine ungeordnete Menge. Wegen der Notwendigkeit, Tabellen zu normalisieren, sind diese Typen in der Praxis unwichtig.

  • Benutzerdefinierte Typen

Dazu gehören nicht nur ein Typ, sondern auch Methoden zu ihrer Verwendung. Auch für solche Typen sind keine sinnvollen Anwendungen zu finden.

Spezialisierte Datentypen

[Bearbeiten]

Datentypen können mit Einschränkungen, also CONSTRAINTs versehen werden; auch der Begriff Domain wird verwendet. (Einen vernünftigen deutschen Begriff gibt es dafür nicht.) Der SQL-Standard macht nicht viele Vorgaben.

Der Befehl zum Erstellen einer Domain sieht allgemein so aus: 

 CREATE DOMAIN <Domain-Name> <zugehöriger Datentyp> [<Vorgabewert>] [<Einschränkungen>]

Unter Interbase/Firebird wurde auf diese Weise ein Ersatz für den BOOLEAN-Datentyp erzeugt:

Firebird-Version
CREATE DOMAIN BOOLEAN     
  -- definiere diesen Datentyp
  AS INT      
  -- als Integer 
  DEFAULT 0     NOT NULL 
  -- Vorgabewert 0, hier ohne NULL-Werte
  CHECK (VALUE BETWEEN 0 AND 1);
  -- Werte können nur 0 (= false) und 1 (= true) sein

Bei MySQL können Spalten mit ENUM oder SET auf bestimmte Werte eingeschränkt werden, allerdings nur auf Zeichen.

Nationale und internationale Zeichensätze

[Bearbeiten]

Aus der Frühzeit der EDV ist das Problem der nationalen Zeichen geblieben: Mit 1 Byte (= 8 Bit) können höchstens 256 Zeichen (abzüglich 32 Steuerzeichen sowie Ziffern und einer Reihe von Satz- und Sonderzeichen) dargestellt werden; und das reicht nicht einmal für die Akzentbuchstaben (Umlaute) aller westeuropäischen Sprachen. Erst mit Unicode gibt es einen Standard, der weltweit alle Zeichen (z. B. auch die chinesischen Zeichen) darstellen und speichern soll.

Da ältere EDV-Systeme (Computer, Programme, Programmiersprachen, Datenbanken) weiterhin benutzt werden, muss die Verwendung nationaler Zeichensätze nach wie vor berücksichtigt werden. Dafür gibt es verschiedene Maßnahmen – jedes DBMS folgt eigenen Regeln für CHARACTER SET (Zeichensatz) und COLLATE (alphabetische Sortierung) und benutzt eigene Bezeichner für die Zeichensätze und die Regeln der Reihenfolge.

Vor allem wegen der DBMS-spezifischen Bezeichnungen kommen Sie nicht um intensive Blicke in die jeweilige Dokumentation herum.

Zeichensatz festlegen mit CHARACTER SET / CHARSET

[Bearbeiten]

Wenn eine Datenbank erstellt wird, muss ein Zeichensatz festgelegt werden. Ohne eine Festlegung regelt jedes DBMS selbst je nach Version, welcher Zeichensatz als Standard gelten soll. Wenn ein Programm Zugriff auf eine vorhandene Datenbank nimmt, muss ebenso der Zeichensatz angegeben werden; dieser muss mit dem ursprünglich festgelegten übereinstimmen. Wenn Umlaute falsch angezeigt werden, dann stimmen in der Regel diese Angaben nicht.

Eine neue Datenbank sollte, wenn das DBMS und die Programmiersprache dies unterstützen, möglichst mit Unicode (in der Regel als UTF8) angelegt werden.

In neueren Versionen steht die Bezeichnung NCHAR (= NATIONAL CHAR) oft nicht für einen speziellen nationalen Zeichensatz, sondern für den allgemeinen Unicode-Zeichensatz. Bei CHAR bzw. VARCHAR wird ein spezieller Zeichensatz verwendet, abhängig von der Installation oder der Datenbank.

In diesem Abschnitt kann deshalb nur beispielhaft gezeigt werden, wie Zeichensätze behandelt werden.

  • Firebird, Interbase; MySQL

CHARACTER SET beschreibt den Zeichensatz einer Datenbank. Dieser gilt als Vorgabewert für alle Zeichenketten: CHAR(n), VARCHAR(n), CLOB. Für eine einzelne Spalte kann ein abweichender Zeichensatz festgelegt werden. Beispiel:

Firebird-Version
CREATE DATABASE 'europe.fb' DEFAULT CHARACTER SET ISO8859_1;
ALTER TABLE xyz ADD COLUMN lname VARCHAR(30) NOT NULL CHARACTER SET CYRL;

Es kommt auch vor, dass ein Programm mit einem anderen Zeichensatz arbeitet als die Datenbank. Dann können die Zeichensätze angepasst werden:

Firebird-Version
SET NAMES DOS437;
CONNECT 'europe.fb' USER 'JAMES' PASSWORD 'U4EEAH';
--  die Datenbank selbst arbeitet mit ISO8859_1, das Programm mit DOS-Codepage 437
  • MS-SQL

Die Dokumentation geht nur allgemein auf „Nicht-Unicode-Zeichendaten“ ein. Es gibt keinerlei Erläuterung, wie ein solcher Zeichensatz festgelegt wird.

Sortierungen mit COLLATE

[Bearbeiten]

COLLATE legt fest, nach welchen Regeln die Reihenfolge von Zeichenketten (englisch: Collation Order) bestimmt wird. Der Vorgabewert für die Datenbank bzw. Tabelle hängt direkt vom Zeichensatz ab. Abweichende Regeln können getrennt gesteuert werden für Spalten, Vergleiche sowie Festlegungen bei ORDER BY und GROUP BY.

Firebird-Version
CREATE DATABASE 'europe.fb' DEFAULT CHARACTER SET ISO8859_1;
--  dies legt automatisch die Sortierung nach ISO8859_1 fest
ALTER TABLE xyz ADD COLUMN lname VARCHAR(30) NOT NULL COLLATE FR_CA;
--  dies legt die Sortierung auf kanadisches Französisch fest
SELECT ... WHERE lname COLLATE FR_FR <= :lname_search;
--  dabei soll der Vergleich nach Französisch (Frankreich) durchgeführt werden
SELECT ...
  ORDER BY LNAME COLLATE FR_CA, FNAME COLLATE FR_CA
  GROUP BY LNAME COLLATE FR_CA, FNAME COLLATE FR_CA;
--  vergleichbare Festlegungen für Reihenfolge und Gruppierung bei SELECT

Diese Beispiele arbeiten mit den Kürzeln für Interbase/Firebird. Andere DBMS nutzen eigene Bezeichnungen; aber die Befehle selbst sind weitgehend identisch.

Zusammenfassung

[Bearbeiten]

In diesem Kapitel lernten Sie die Datentypen unter SQL kennen:

  • Bei Zeichenketten ist zwischen fester und variabler Länge zu unterscheiden und der Zeichensatz – UNICODE oder national – zu beachten.
  • Für Zahlen ist zwischen exakter und näherungsweiser Speicherung zu unterscheiden und die Genauigkeit zu beachten.
  • Für Datums- und Zeitwerte ist vor allem auf den jeweiligen Geltungsbereich und die Genauigkeit zu achten.
  • Für spezielle Zwecke gibt es weitere Datentypen wie BLOB oder BOOLEAN.

Übungen

[Bearbeiten]

Zahlen und Datumsangaben verwenden immer die im deutschsprachigen Raum üblichen Schreibweisen. Zeichen werden mit Hochkommata begrenzt.

Übung 1 Texte und Zahlen Zur Lösung

Geben Sie zu den Werten jeweils an, welche Datentypen passen, welche fraglich sind (also u. U. möglich, aber nicht sinnvoll oder unklar) und welche falsch sind.

  1. 'A' als Char, Char(20), Varchar, Varchar(20)
  2. der Ortsname 'Bietigheim-Bissingen' als Char, Char(20), Varchar, Varchar(20)
  3. das Wort 'Übungen' als Varchar(20), NVarchar(20)
  4. 123.456 als Integer, Smallint, Float, Numeric, Varchar(20)
  5. 123,456 als Integer, Smallint, Float, Numeric, Varchar(20)
  6. 789,12 [€] als Integer, Smallint, Float, Numeric, Varchar(20)

Übung 2 Datum und Zeit Zur Lösung

Geben Sie jeweils an, welche Datentypen passen, welche fraglich sind (also u. U. möglich, aber nicht sinnvoll oder unklar) und welche falsch sind.

  1. '27.11.2009' als Date, Time, Timestamp, Char(10), Varchar(20)
  2. '11:42:53' als Date, Time, Timestamp, Char(10), Varchar(20)
  3. '27.11.2009 11:42:53' als Date, Time, Timestamp, Char(10), Varchar(20)
  4. 'November 2009' als Date, Time, Timestamp, Char(10), Varchar(20)

Übung 3 Personen Zur Lösung

Bereiten Sie eine Tabelle Person vor: Notieren Sie mit möglichst konsequenter Aufteilung der Bestandteile die möglichen Spaltennamen und deren Datentypen; berücksichtigen Sie dabei auch internationale Adressen, aber keine Kontaktdaten (wie Telefon).

Übung 4 Buchhaltung Zur Lösung

Bereiten Sie eine Tabelle Kassenbuch vor: Notieren Sie die möglichen Spaltennamen und deren Datentypen; berücksichtigen Sie dabei auch, dass Angaben der Buchhaltung geprüft werden müssen.

Lösungen

Lösung zu Übung 1 Texte und Zahlen Zur Übung

Die „richtige“ Festlegung hängt vom Zusammenhang innerhalb einer Tabelle ab.

  1. Char und Varchar(20) passen, Varchar ist nicht sinnvoll, Char(20) ist falsch.
  2. Char(20) und Varchar(20) passen, Char und Varchar sind falsch.
  3. Je nach verwendetem Zeichensatz können beide Varianten richtig, ungeeignet oder falsch sein.
  4. Integer und Numeric sind richtig, Float ist möglich, Smallint ist falsch, Varchar(20) ist nicht ganz ausgeschlossen.
  5. Float und Numeric sind richtig, Integer und Smallint sind falsch, Varchar(20) ist nicht ganz ausgeschlossen.
  6. Numeric ist richtig, Float ist möglich, Integer und Smallint sind falsch, Varchar(20) ist nicht ganz ausgeschlossen.

Lösung zu Übung 2 Datum und Zeit Zur Übung
  1. Date und Timestamp sind richtig, Char(10) und Varchar(20) sind möglich, Time ist falsch.
  2. Time und Timestamp sind richtig, Varchar(20) ist möglich, Char(10) und Date sind falsch.
  3. Timestamp ist richtig, Varchar(20) ist möglich, Date, Time und Char(10) sind falsch.
  4. Varchar(20) ist richtig, alles andere falsch.

Lösung zu Übung 3 Personen Zur Übung

Bitte wundern Sie sich nicht über unerwartete Unterteilungen: Bei der folgenden Lösung werden auch Erkenntnisse der Datenbank-Theorie und der praktischen Arbeit mit Datenbanken berücksichtigt.

  • ID Integer
  • Titel Varchar(15)
  • Vorname Varchar(30)
  • Adelszusatz Varchar(15) – Trennung ist wegen der alphabetischen Sortierung sinnvoll
  • Name Varchar(30)
  • Adresszusatz Varchar(30)
  • Strasse Varchar(24)
  • Hausnr Integer (oder Smallint)
  • HausnrZusatz Varchar(10) – Trennung ist wegen der numerischen Sortierung sinnvoll
  • Länderkennung Char(2) – nach ISO 3166, auch Char(3) möglich, Integer oder Smallint denkbar; der Ländername ist auf jeden Fall unpraktisch
  • PLZ Char(10) oder Varchar(10) – international sind bis zu 10 Zeichen möglich
  • Geburtsdatum Date

Lösung zu Übung 4 Buchhaltung Zur Übung
  • ID Integer
  • Buchungsjahr Integer oder Smallint
  • Buchungsnummer Integer
  • Buchungstermin Timestamp – je nach Arbeitsweise genügt auch Date
  • Betrag Numeric oder Decimal
  • Vorgang Varchar(50) – als Beschreibung der Buchung
  • Bearbeiter Varchar(30) – derjenige, der den Kassenbestand ändert
  • Nutzer Varchar(30) – derjenige, der die Buchung registriert
  • Buchhaltung Timestamp – Termin, zu dem die Buchung registriert wird

Wenn das Kassenbuch explizit ein Teil der Buchhaltung ist, werden auch Spalten wie Buchungskonto (Haupt- und Gegenkonten) benötigt.

Siehe auch

[Bearbeiten]

In Wikipedia gibt es zusätzliche Hinweise:


Abgerufen von „https://de.wikibooks.org/w/index.php?title=Einführung_in_SQL:_Datentypen&oldid=931552“