Online Suche im Handbuch

28.1 CREATE TABLE

CREATE TABLE

CREATE TABLE tbl_name (create_definition,...) 

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  oder  PRIMARY KEY (index_col_name,...)
  oder  KEY [index_name] KEY(index_col_name,...)
  oder  INDEX [index_name] (index_col_name,...)
  oder  UNIQUE [index_name] (index_col_name,...)
  oder  [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  oder  CHECK (expr)

Typ:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  oder  SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  oder  MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  oder  INT[(length)] [UNSIGNED] [ZEROFILL]
  oder  INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  oder  BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  oder  REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  oder  DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  oder  FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  oder  DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  oder  NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  oder  CHAR(length) [BINARY]
  oder  VARCHAR(length) [BINARY]
  oder  DATE
  oder  TIME
  oder  TIMESTAMP
  oder  DATETIME
  oder  TINYBLOB
  oder  BLOB
  oder  MEDIUMBLOB
  oder  LONGBLOB
  oder  TINYTEXT
  oder  TEXT
  oder  MEDIUMTEXT
  oder  LONGTEXT
  oder  ENUM(value1,value2,value3...)
  oder  SET(value1,value2,value3...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
        type = [ISAM | MYISAM | HEAP]
oder      auto_increment = #
oder      avg_row_length = #
oder      checksum = [0 | 1]
oder      comment = "string"
oder      max_rows = #
oder      min_rows = #
oder      pack_keys = [0 | 1]
oder      password= "string"

select_statement:
        [ | IGNORE | REPLACE] SELECT ...  (Hier kann irgendein SELECT Statement eingefügt werden)

Jede Tabelle wird durch drei Dateien im Datenbankverzeichnis dargestellt:

  
     tbl_name.frm - Tabellendefinition 
     tbl_name.ISD - Datenfile 
     tbl_name.ISM - Indexfile 

Was hat es mit den drei Dateien auf sich ? In der Tabellendefinition befindet sich die Struktur der Datenbank, die Angaben über die Größen der Felder u.s.w. Im Datenfile befinden sich alle Daten, die dort so abgelegt sind, daß die schnell wiedergefunden werden können. Im Indexfile ist die Datenbank als HASH-Tabelle abgelegt, welches die Suche im Datenfile drastisch beschleunigt.

Zur Syntax von CREATE

Der CREATE Befehl ist besonders wichtig, da ohne eine solide Datenbankstruktur nichts korrekt läuft. Man sollte sich gut überlegen, welche Typen und Attribute man verwendet, sowie man die Datenbank am geschicktesten anlegt. Spätere Korrekturen sind schlecht möglich. Bevor man nun einfach eine Datenbank erzeugt, sollte man das Kapitel Datenbank Grundlagen und das Kapitel zur Normalisierug von Datenbanken

• Wird eine Spalte ohne NULL oder NOT NULL definiert, wird diese automatisch als NULL definiert.
• Eine Spalte vom Typ INTEGER kann zusätzlich das Attribut AUTO_INCREMENT erhalten. Wodurch automatisch nach dem Einfügen eines neuen Datensatzes dieses Feld einen um 1 höheren Wert erhält als der höchste in dieser Spalte vorhandene Wert. Wird die Spalte wieder gelöscht, so steht dieser Feldwert bei dem nächsten Datensatz wieder zur Verfügung. Anmerkung: Pro Tabelle kann immer nur 1 Feld das AUTO_INCREMENT Attribut erhalten.
• NULL-Werte werden bei Feldern vom Typ TIMESTAMP ignoriert. In diesen Feldern kann nicht der Wert NULL abgelegt werden. In diesem Fall wird immer das aktuelle Datum abgelegt. Hierbei ist zu beachten, daß der Wert 0 nicht dem Wert NULL entspricht. Der Wert 0 ist wiederum ein zulässiger Wert, welcher in solch einem Feld abgelegt werden kann.
• Wird für eine Spalte kein DEFAULT vorgegeben und wird diese als NOT NULL definiert, wird der Wert NULL als DEFAULT verwendet.
• Wird eine Spalte ohne DEFAULT-Wert angelegt und als NOT NULL definiert, legt MySQL, abhängig vom Datentyp einen Defaultwert automatisch fest.
  • Bei nummerischen Datentypen ist dies 0.
  • Bei DATE und TIME Typen ist dies der Startwert dieses Typs.
  • Bei String-Typen ist dies ein leerer String.
• KEY ist ein Synonym für INDEX
• Ein UNIQUE KEY kann nur eindeutige Werte besitzen. Wird mehrmals versucht den gleichen Wert zu verwenden, kommt es zu einer Fehlermeldung
• Ein PRIMARY KEY stellt einen eindeutigen Key dar. Jede Tabelle kann maximal einen PRIMARY KEY besitzen. MySQL legt den ersten UNIQUE KEY als PRIMARY KEY fest, sofern kein PRIMARY KEY definiert wurde
• Ein PRIMARY KEY kann aus mehreren Spalten zusammengesetzt sein (bis zu 16 bzw. 32 (3.23.x). Allerdings darf ein mehrspaltiger Index keine PRIMARY KEY Spalte enthalten. Dies führt zu einer Fehlermeldung.
• Wird beim Definieren eines INDEX kein Name für diesen INDEX angegeben, so wird der Name des ersten Feldes, daß im Index enthalten ist, mit einem Suffix z.B. _2, _3, ... verwendet.
• Spalten, welche indiziert oder Teil eines INDEX sind, können keine NULL Werte enthalten. Diese Spalten müssen als NOT NULL deklariert werden.
• Mit der col_name(länge)-Syntaxe kann der Teil eines CHAR oder VARCHAR feldes festgelegt werden, der in den Index einfließt.
• TEXT und BLOB Felder können nicht iniziert sein.
• Die FOREIGN KEY, CHECK und REFERENCES Klausen haben zur Zeit keinerlei Auswirkung. Diese Syntax wird nur zur Verfügung gestellt um kompatibel zu anderen SQL-Servern zu sein.
• In manchen Fällen ändert MySQL automatisch die Felddefinitionen, welche in der CREATE-Anweisung enhalten sind um:
  • VARCHAR Felder mit einer Länge kleiner 4 werden in CHAR umgewandelt
  • TIMESTAMP Felder müssen eine Länge von mindesten 2 und maximal 14 haben. Wird ein Wert auserhalb dieser Range angegeben, wird er auf 14 gesetzt.

• ISAM Der originale Tabellen Handler (default Einstellung)
• MyISAM Ist die neue, für SQL optimierte MyISAM Datenbankstruktur. Diese ist nicht zum Vorgänger kompatibel. Falls Sie also updaten möchten, müssen Sie alle Daten zwischen zwei MySQL Datenbanken kopieren. (Setzen Sie einfach hierzu eine neue MySQL Datenbank auf einem anderen Port auf)
• HEAP Ist die Datenstruktur für eine Tabelle, die nur im RAM abgelegt wird. Entsprechend sind die Zugriffe darauf ca. 5-30x schneller, als auf die normale Datenbank. Dies kann VIEWS vollständig ersetzen.

• checksum ist ganz nützlich, wenn man eine Korruption in der Datenstruktur feststellen können muß. (Wert auf 1 setzen)
• max_rows ist der Wert der maximalen Spalten in einer Tabelle und wichtig für ISP´s, die die Auswirkungen von unsinnigen CREATE Statements klein halten möchten.
• min_rows ist der entsprechende Minimalwert
• pack_keys sollte auf 1 gesetzt werden, damit der INDEX kleiner wird. Bei Updates ist MySQL etwas langsamer, beim Lesen viel schneller.
• password verschlüsselt die .frm Datei mit einem Passwort

Beispiele zu CREATE und ALTER

Hier sind einige Beispiele die zeigen, wie man mit CREATE und ALTER TABLE umgeht:

mysql> CREATE TABLE test (a int not null auto_increment, primary key (a), key(b))
     TYPE=HEAP SELECT b,c from test2;

Dieses Beispiel legt eine temporäre HEAP Tabelle mit 3 Spalten an. Diese HEAP Tabelle wird vollständig im RAM abgelegt. Eventuell werden Teile auch im SWAP - File ausgelagert (erst ab Version 3.23). Die Tabelle kann nachher wieder gelöscht werden. Falls ein Fehler auftritt, dann wird diese Tabelle automatisch gelöscht.

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

Um Tabelle von t1 nach t2 umzubenennen:

mysql> ALTER TABLE t1 RENAME t2;

Um eine Spalte von INTEGER nach TINYINT NOT NULL zu konvertieren, und gleichzeitig Spalte b von CHAR(10) nach CHAR(20) unter Umbenennung von b nach c zu verändern:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

Einfügen einer TIMESTAMP Spalte mit Namen d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Um einen Index an Spalte d anzufügen, und diese als primary key zu bestimmen:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Spalte c Löschen:

mysql> ALTER TABLE t2 DROP COLUMN c;

Hinzufügen einer AUTO_INCREMENT integer Spalte namens c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

Man beachte, daß wir c indiziert haben, weil AUTO_INCREMENT Spalten zwangsläufig indiziert werden müssen. Wir erklären c als NOT NULL, weil indizierte Spalten nicht NULL sein dürfen.

Online Suche im Handbuch