MySQL
Création de table

Syntaxe

• CREATE TABLE nom_table (nom_colonne1 type_données (taille), nom_colonne2 type_données (taille), nom_colonne3 type_données (taille), ....); // Création de table de base

• CREATE TABLE nom_table [IF NOT EXISTS] (nom_colonne1 type_données (taille), nom_colonne2 type_données (taille), nom_colonne3 type_données (taille), ....); // Création de la table en vérifiant existante

• CREATE [TEMPORARY] TABLE nom_table [IF NOT EXISTS] (nom_colonne1 type_données (taille), nom_colonne2 type_données (taille), nom_colonne3 type_données (taille), ....); // Création de table temporaire

• CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; // Création de table à partir de SELECT

Remarques

CREATE TABLE table_name ( column_definitions ) ENGINE=engine;

Création de table de base

L'instruction CREATE TABLE est utilisée pour créer une table dans une base de données MySQL.

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

Chaque définition de champ doit avoir:

1. Nom de champ: Nom de champ valide. Assurez-vous de bien inscrire les noms dans `-chars. Cela garantit que vous pouvez utiliser par exemple des caractères spatiaux dans le nom du champ.
2. Type de données [Longueur]: si le champ est CHAR ou VARCHAR , il est obligatoire de spécifier une longueur de champ.
3. Attributs NULL | NOT NULL : Si NOT NULL est spécifié, toute tentative de stockage d'une valeur NULL dans ce champ échouera.
4. Voir plus sur les types de données et leurs attributs ici .

Engine=... est un paramètre facultatif utilisé pour spécifier le moteur de stockage de la table. Si aucun moteur de stockage n'est spécifié, la table sera créée à l'aide du moteur de stockage de table par défaut du serveur (généralement InnoDB ou MyISAM).

Définition des valeurs par défaut

De plus, là où il est logique, vous pouvez définir une valeur par défaut pour chaque champ en utilisant 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;

Si pendant l'insertion, aucune Street n'est spécifiée, ce champ sera NULL lors de sa récupération. Lorsqu'aucun Country n'est spécifié lors de l'insertion, il sera par défaut "États-Unis".

Vous pouvez définir des valeurs par défaut pour tous les types de colonne, à l' exception des champs BLOB , TEXT , GEOMETRY et JSON .

Création de table avec clé primaire

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)
);

Une clé primaire est un identificateur unique ou multi-colonnes NOT NULL qui identifie de manière unique une ligne d'une table. Un index est créé et, s'il n'est pas déclaré explicitement comme NOT NULL , MySQL les déclarera de manière implicite et silencieuse.

Une table ne peut avoir qu'une seule PRIMARY KEY et chaque table est recommandée. InnoDB en créera automatiquement un en son absence (comme vu dans la documentation MySQL ), bien que cela soit moins souhaitable.

Souvent, une INT AUTO_INCREMENT également connue sous le nom de "clé de substitution" est utilisée pour l'optimisation d'index léger et les relations avec d'autres tables. Cette valeur augmente (normalement) de 1 à chaque fois qu'un nouvel enregistrement est ajouté, à partir d'une valeur par défaut de 1.

Cependant, malgré son nom, le but n’est pas de garantir que les valeurs sont incrémentielles, mais simplement qu’elles sont séquentielles et uniques.

Une valeur INT incrémentée automatiquement ne sera pas réinitialisée à sa valeur de démarrage par défaut si toutes les lignes de la table sont supprimées, à moins que la table ne soit tronquée à l'aide de l' TRUNCATE TABLE .

Définition d'une colonne en tant que clé primaire (définition en ligne)

Si la clé primaire est constituée d'une seule colonne, la clause PRIMARY KEY peut être intégrée à la définition de la colonne:

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

Cette forme de la commande est plus courte et plus facile à lire.

Définition d'une clé primaire à plusieurs colonnes

Il est également possible de définir une clé primaire comprenant plusieurs colonnes. Cela peut être fait par exemple sur la table enfant d'une relation de clé étrangère. Une clé primaire multi-colonnes est définie en répertoriant les colonnes participantes dans une clause PRIMARY KEY distincte. La syntaxe inline n'est pas autorisée ici, car une seule colonne peut être déclarée en ligne PRIMARY KEY . Par exemple:

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
);

Notez que les colonnes de la clé primaire doivent être spécifiées dans l'ordre de tri logique, qui peut être différent de l'ordre dans lequel les colonnes ont été définies, comme dans l'exemple ci-dessus.

Les index plus grands nécessitent plus d'espace disque, de mémoire et d'E / S. Les clés doivent donc être aussi petites que possible (en particulier en ce qui concerne les touches composées). Dans InnoDB, chaque «index secondaire» inclut une copie des colonnes de la PRIMARY KEY .

Création de table avec clé étrangère

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;

Clé étrangère: une clé étrangère ( FK ) est soit une colonne unique, soit un composite de colonnes à colonnes multiples, dans une table de référence . Ce FK est confirmé pour exister dans la table référencée . Il est fortement recommandé que la clé de table référencée confirmant le FK soit une clé primaire, mais cela n'est pas appliqué. Il est utilisé comme une recherche rapide dans les références où il n’a pas besoin d’être unique et peut en fait être un index le plus à gauche.

Les relations de clé étrangère impliquent une table parent contenant les valeurs de données centrales et une table enfant avec des valeurs identiques renvoyant à son parent. La clause FOREIGN KEY est spécifiée dans la table enfant. Les tables parent et enfant doivent utiliser le même moteur de stockage. Ils ne doivent pas être des tables TEMPORAIRES .

Les colonnes correspondantes dans la clé étrangère et la clé référencée doivent avoir des types de données similaires. La taille et le signe des types entiers doivent être identiques. La longueur des types de chaîne ne doit pas nécessairement être la même. Pour les colonnes de chaîne non binaires (caractères), le jeu de caractères et le classement doivent être identiques.

Remarque: les contraintes de clé étrangère sont prises en charge sous le moteur de stockage InnoDB (pas MyISAM ou MEMORY). Les configurations de base de données utilisant d'autres moteurs accepteront cette instruction CREATE TABLE mais ne respecteront pas les contraintes de clé étrangère. (Bien que les nouvelles versions de MySQL soient par défaut à InnoDB , mais il est recommandé d’être explicite.)

Cloner une table existante

Un tableau peut être répliqué comme suit:

CREATE TABLE ClonedPersons LIKE Persons;

La nouvelle table aura exactement la même structure que la table d'origine, y compris les index et les attributs de colonne.

Outre la création manuelle d'une table, il est également possible de créer une table en sélectionnant des données d'une autre table:

CREATE TABLE ClonedPersons SELECT * FROM Persons;

Vous pouvez utiliser l'une des fonctionnalités normales d'une SELECT pour modifier les données au fur et à mesure:

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

Les clés primaires et les index ne seront pas conservés lors de la création de tables à partir de SELECT . Vous devez les redéclarer:

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

CREATE TABLE FROM SELECT

Vous pouvez créer une table à partir d'une autre en ajoutant une SELECT à la fin de l'instruction CREATE TABLE :

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

Créez une table dans la même base de données:

-- 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;

Créez des tables à partir de différentes bases de données:

-- 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

Pour créer une table identique à une autre table existant dans une autre base de données, vous devez spécifier le nom de la base de données comme suit:

FROM NAME_DATABASE.name_table

Afficher la structure du tableau

Si vous souhaitez voir les informations de schéma de votre table, vous pouvez utiliser l'une des options suivantes:

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;

S'il est utilisé à partir de l'outil en ligne de commande mysql, c'est moins prolixe:

SHOW CREATE TABLE child \G

Une manière moins descriptive de montrer la structure de la table:

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    |       |  
+-------+-------------+------+-----+---------+-------+ 

DESCRIBE et DESC donnent le même résultat.

Pour voir DESCRIBE exécuté sur toutes les tables d'une base de données à la fois, voir cet exemple .

Table Create With TimeStamp Column pour afficher la dernière mise à jour

La colonne TIMESTAMP affichera la dernière mise à jour de la ligne.

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'
;