MySQL
Tabellenerstellung

Syntax

• CREATE TABLE Tabellenname (Spaltenname1 Datentyp (Größe), Spaltenname2 Datentyp (Größe), Spaltenname3 Datentyp (Größe), ....); // Grundlegende Tabellenerstellung

• CREATE TABLE Tabellenname [IF NOT EXISTS] (Spaltenname1 Datentyp (Größe), Spaltenname2 Datentyp (Größe), Spaltenname3 Datentyp (Größe), ....); // Überprüfung der Tabellenerstellung vorhanden

• CREATE [TEMPORARY] TABLE tabellenname [IF NOT EXISTS] (Spaltenname1 Datentyp (Größe), Spaltenname2 Datentyp (Größe), Spaltenname3 Datentyp (Größe) ... // Temporäre Tabellenerstellung

• CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; // Tabellenerstellung aus SELECT

Bemerkungen

CREATE TABLE table_name ( column_definitions ) ENGINE=engine;

Grundlegende Tabellenerstellung

Die CREATE TABLE Anweisung wird zum Erstellen einer Tabelle in einer MySQL-Datenbank verwendet.

CREATE TABLE Person (
    `PersonID`      INTEGER NOT NULL PRIMARY KEY,
    `LastName`      VARCHAR(80),
    `FirstName`     VARCHAR(80),
    `Address`       TEXT,
    `City`          VARCHAR(100)
) Engine=InnoDB;

Jede Felddefinition muss Folgendes aufweisen:

1. Feldname: Ein gültiger Feldname. Stellen Sie sicher, dass die Namen in `-chars eingebettet sind. Dadurch wird sichergestellt, dass Sie im Feldnamen zB Leerzeichen verwenden können.
2. Datentyp [Länge]: Wenn das Feld CHAR oder VARCHAR ist, muss eine Feldlänge angegeben werden.
3. Attribute NULL | NOT NULL : Wenn NOT NULL angegeben ist, schlägt der Versuch eines NULL Werts in diesem Feld fehl.
4. Weitere Informationen zu Datentypen und ihren Attributen finden Sie hier .

Engine=... ist ein optionaler Parameter zur Angabe der Speicher-Engine der Tabelle. Wenn keine Speicher-Engine angegeben ist, wird die Tabelle mit der standardmäßigen Tabellen-Speicher-Engine des Servers (normalerweise InnoDB oder MyISAM) erstellt.

Voreinstellungen festlegen

DEFAULT es sinnvoll ist, können Sie außerdem mit DEFAULT einen Standardwert für jedes Feld DEFAULT :

CREATE TABLE Address (
    `AddressID`   INTEGER NOT NULL PRIMARY KEY,
    `Street`      VARCHAR(80),
    `City`        VARCHAR(80),
    `Country`     VARCHAR(80) DEFAULT "United States",
    `Active`      BOOLEAN DEFAULT 1,
) Engine=InnoDB;

Wenn beim Einfügen keine Street angegeben wird, ist dieses Feld beim Abrufen NULL . Wenn beim Einfügen kein Country angegeben wird, wird standardmäßig "USA" verwendet.

Sie können Standardwerte für alle Spaltentypen, stellen mit Ausnahme für BLOB , TEXT , GEOMETRY und JSON Felder aus .

Tabellenerstellung mit Primärschlüssel

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66),
    PRIMARY KEY (PersonID)
);

Ein Primärschlüssel ist ein ein- oder mehrspaltiger NOT NULL -Bezeichner, der eine Zeile einer Tabelle eindeutig identifiziert. Ein Index wird erstellt, und wenn MySQL nicht explizit als NOT NULL deklariert wird, werden sie von MySQL so still und implizit deklariert.

Eine Tabelle kann nur einen PRIMARY KEY , und für jede Tabelle wird empfohlen, einen zu haben. InnoDB erstellt in seiner Abwesenheit automatisch eine (wie in der MySQL-Dokumentation zu sehen ), obwohl dies weniger wünschenswert ist.

Häufig wird ein AUTO_INCREMENT INT auch als "Ersatzschlüssel" bezeichnet, für die Optimierung des dünnen Index und für Beziehungen zu anderen Tabellen verwendet. Dieser Wert wird (normalerweise) um 1 erhöht, wenn ein neuer Datensatz hinzugefügt wird. Der Standardwert ist 1.

Trotz des Namens ist es jedoch nicht das Ziel, zu gewährleisten, dass die Werte inkrementell sind, sondern lediglich, dass sie sequentiell und eindeutig sind.

Ein INT Wert für die automatische Erhöhung wird nicht auf den Standardstartwert zurückgesetzt, wenn alle Zeilen in der Tabelle gelöscht werden, es sei denn, die Tabelle wird mit der TRUNCATE TABLE abgeschnitten.

Definieren einer Spalte als Primärschlüssel (Inline-Definition)

Wenn der Primärschlüssel aus einer einzelnen Spalte besteht, kann die PRIMARY KEY Klausel mit der PRIMARY KEY inline platziert werden:

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL PRIMARY KEY,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66)
);

Diese Befehlsform ist kürzer und leichter lesbar.

Definieren eines mehrspaltigen Primärschlüssels

Es ist auch möglich, einen Primärschlüssel mit mehr als einer Spalte zu definieren. Dies kann beispielsweise in der untergeordneten Tabelle einer Fremdschlüsselbeziehung erfolgen. Ein mehrspaltiger Primärschlüssel wird durch Auflisten der beteiligten Spalten in einer separaten PRIMARY KEY Klausel definiert. Inline-Syntax ist hier nicht zulässig, da nur eine Spalte als PRIMARY KEY inline deklariert werden kann. Zum Beispiel:

CREATE TABLE invoice_line_items (
    LineNum      SMALLINT UNSIGNED NOT NULL,
    InvoiceNum   INT UNSIGNED NOT NULL,
    -- Other columns go here
    PRIMARY KEY (InvoiceNum, LineNum),
    FOREIGN KEY (InvoiceNum) REFERENCES -- references to an attribute of a table
);

Beachten Sie, dass die Spalten des Primärschlüssels sollte in logische Sortierreihenfolge festgelegt werden, die von der Reihenfolge verschieden sein können , in denen die Säulen definiert sind , wie in dem obigen Beispiel.

Größere Indizes erfordern mehr Speicherplatz, Speicher und E / A. Daher sollten Schlüssel möglichst klein sein (insbesondere bei zusammengesetzten Schlüsseln). In InnoDB enthält jeder "Sekundärindex" eine Kopie der Spalten des PRIMARY KEY .

Tabellenerstellung mit Fremdschlüssel

CREATE TABLE Account (
    AccountID     INT UNSIGNED NOT NULL,
    AccountNo     INT UNSIGNED NOT NULL,
    PersonID    INT UNSIGNED,
    PRIMARY KEY (AccountID),
    FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
) ENGINE=InnoDB;

Fremdschlüssel: Ein Fremdschlüssel ( FK ) ist entweder eine einzelne Spalte oder eine mehrspaltige Zusammenstellung von Spalten in einer Verweistabelle . Es wurde bestätigt, dass diese FK in der referenzierten Tabelle vorhanden ist. Es wird dringend empfohlen, dass der referenzierte Tabellenschlüssel, der den FK bestätigt, ein Primärschlüssel ist, der jedoch nicht erzwungen wird. Es wird als schnelle Suche in Bezug auf die referenzierten Objekte verwendet, wo es nicht eindeutig sein muss. In der Tat kann es sich um einen Index ganz links handeln.

Fremdschlüsselbeziehungen umfassen eine übergeordnete Tabelle, die die zentralen Datenwerte enthält, und eine untergeordnete Tabelle mit identischen Werten, die auf ihr übergeordnetes Element verweisen. Die FOREIGN KEY-Klausel wird in der untergeordneten Tabelle angegeben. Die übergeordneten und untergeordneten Tabellen müssen dieselbe Speicher-Engine verwenden. Sie dürfen keine temporären Tabellen sein.

Entsprechende Spalten im Fremdschlüssel und der referenzierte Schlüssel müssen ähnliche Datentypen haben. Die Größe und das Vorzeichen von Integer-Typen müssen gleich sein. Die Länge der Zeichenfolgentypen muss nicht gleich sein. Für nicht-binäre (Zeichen-) Zeichenfolgenspalten müssen der Zeichensatz und die Sortierung identisch sein.

Hinweis: Fremdschlüsseleinschränkungen werden von der InnoDB-Speicher-Engine unterstützt (nicht MyISAM oder MEMORY). DB-Setups, die andere Engines verwenden, akzeptieren diese CREATE TABLE Anweisung, CREATE TABLE jedoch keine Fremdschlüsseleinschränkungen. (Obwohl neuere MySQL-Versionen standardmäßig InnoDB , ist es jedoch InnoDB , explizit zu sein.)

Eine vorhandene Tabelle klonen

Eine Tabelle kann wie folgt repliziert werden:

CREATE TABLE ClonedPersons LIKE Persons;

Die neue Tabelle hat genau dieselbe Struktur wie die ursprüngliche Tabelle, einschließlich Indizes und Spaltenattributen.

Neben dem manuellen Erstellen einer Tabelle können Sie auch eine Tabelle erstellen, indem Sie Daten aus einer anderen Tabelle auswählen:

CREATE TABLE ClonedPersons SELECT * FROM Persons;

Sie können die normalen Funktionen einer SELECT Anweisung verwenden, um die Daten während des SELECT zu ändern:

CREATE TABLE ModifiedPersons
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

Primärschlüssel und Indizes bleiben beim Erstellen von Tabellen aus SELECT nicht erhalten. Sie müssen sie erneut deklarieren:

CREATE TABLE ModifiedPersons (PRIMARY KEY (PersonID))
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

CREATE TABLE VON SELECT

Sie können eine Tabelle aus einer anderen erstellen, indem Sie am Ende der CREATE TABLE Anweisung eine SELECT Anweisung hinzufügen:

CREATE TABLE stack (
    id_user INT,
    username VARCHAR(30),
    password VARCHAR(30)
);

Erstellen Sie eine Tabelle in derselben Datenbank:

-- create a table from another table in the same database with all attributes
CREATE TABLE stack2 AS SELECT * FROM stack;

-- create a table from another table in the same database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM stack;

Erstellen Sie Tabellen aus verschiedenen Datenbanken:

-- create a table from another table from another database with all attributes
CREATE TABLE stack2 AS SELECT * FROM second_db.stack;

-- create a table from another table from another database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM second_db.stack;

NB

Um eine Tabelle einer anderen Tabelle zu erstellen, die in einer anderen Datenbank vorhanden ist, müssen Sie den Namen der Datenbank folgendermaßen angeben:

FROM NAME_DATABASE.name_table

Tabellenstruktur anzeigen

Wenn Sie die Schemainformationen Ihrer Tabelle anzeigen möchten, können Sie eine der folgenden Möglichkeiten verwenden:

SHOW CREATE TABLE child; -- Option 1

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(100) NOT NULL,
  `myParent` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mommy_daddy` (`myParent`),
  CONSTRAINT `mommy_daddy` FOREIGN KEY (`myParent`) REFERENCES `parent` (`id`) 
      ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Wenn es vom mysql-Befehlszeilentool verwendet wird, ist dies weniger ausführlich:

SHOW CREATE TABLE child \G

Eine weniger beschreibende Art, die Tabellenstruktur darzustellen:

mysql> CREATE TABLE Tab1(id int, name varchar(30));
Query OK, 0 rows affected (0.03 sec)

mysql> DESCRIBE Tab1; -- Option 2 

+-------+-------------+------+-----+---------+-------+ 
| Field | Type        | Null | Key | Default | Extra |  
+-------+-------------+------+-----+---------+-------+ 
| id    | int(11)     | YES  |     | NULL    |       | 
| name  | varchar(30) | YES  |     | NULL    |       |  
+-------+-------------+------+-----+---------+-------+ 

Sowohl DESCRIBE als auch DESC liefern das gleiche Ergebnis.

DESCRIBE wie DESCRIBE für alle Tabellen in einer Datenbank gleichzeitig ausgeführt wird, finden Sie in diesem Beispiel .

Tabelle mit Zeitstempelspalte erstellen, um die letzte Aktualisierung anzuzeigen

Die TIMESTAMP-Spalte zeigt an, wann die Zeile zuletzt aktualisiert wurde.

CREATE TABLE `TestLastUpdate` (
    `ID` INT NULL,
    `Name` VARCHAR(50) NULL,
    `Address` VARCHAR(50) NULL,
    `LastUpdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COMMENT='Last Update'
;