Les instructions DDL (Data Definition Language) dans MySQL sont essentielles pour définir et modifier la structure des objets de la base de données. Elles permettent de gérer les schémas, les tables, les colonnes, les index et d'autres composants structurels. Contrairement au DML (Data Manipulation Language) qui agit sur les données, le DDL se concentre sur les définitions des objets de la base.

Types de Données Fondamentaux

Le choix du type de données approprié est crucial pour l'efficacité et l'intégrité de la base de données. Voici une présentation des types courants en MySQL :

Types Numériques

Type MySQL	Description
FLOAT(m,d)	Type virgule flottante simple précision sur 4 octets. m est le nombre total de chiffres, d le nombre de chiffres après la virgule.
DOUBLE(m,d)	Type virgule flottante double précision sur 8 octets. m est le nombre total de chiffres, d le nombre de chiffres après la virgule.
DECIMAL(m,d)	Type virgule flottante stocké sous forme de chaîne, offrant une précision exacte. m est le nombre total de chiffres (précision), d l'échelle (chiffres après la virgule).

Les types FLOAT et DOUBLE peuvent présenter des imprécisions dues à leur représentation binaire. Par exemple, si une colonne est définie comme FLOAT(5,3) :

• L'insertion de 123.45678 peut résulter en 99.999 (débordement, arrondi au maximum admissible).
• L'insertion de 12.34567 peut donner 12.346 (arrondi à la précision spécifiée).

Pour les valeurs monétaires ou toute donnée nécessitant une précision absolue, DECIMAL est fortement recommandé.

Note sur ZEROFILL : L'attribut ZEROFILL peut être appliqué aux types entiers. Il remplit les valeurs numériques avec des zéros à gauche jusqu'à la largeur spécifiée. Cela implique également que la colonne sera UNSIGNED.

CREATE TABLE NombresFormatés (
    identifiant INT(5) ZEROFILL
);

INSERT INTO NombresFormatés (identifiant) VALUES (10);
SELECT identifiant FROM NombresFormatés;
-- Résultat: 00010

Types Chaînes de Caractères

Type MySQL	Description
CHAR(n)	Chaîne de longueur fixe, de 0 à 255 caractères. L'espace est alloué même si la chaîne est plus courte. Les espaces de fin sont tronqués lors de la lecture.
VARCHAR(n)	Chaîne de longueur variable, jusqu'à 65535 caractères (limité par la taille de ligne max de 65535 octets). Seul l'espace nécessaire + 1 ou 2 octets pour la longueur est utilisé. Les espaces de fin sont conservés.
TINYTEXT	Chaîne de texte variable, max 255 caractères.
TEXT	Chaîne de texte variable, max 65535 caractères.
MEDIUMTEXT	Chaîne de texte variable, max (224 - 1) caractères.
LONGTEXT	Chaîne de texte variable, max (232 - 1) caractères.

Points clés :

• La valeur n dans CHAR(n) et VARCHAR(n) représente le nombre de caractères, non le nombre d'octets. Avec UTF-8, un caractère peut occuper plusieurs octets.
• CHAR alloue toujours n caractères d'espace, tandis que VARCHAR n'utilise que l'espace nécessaire plus un préfixe pour stocker la longueur.
• Si une chaîne insérée dépasse n pour CHAR ou VARCHAR, elle est tronquée.
• CHAR tronque les espaces de fin lors du stockage ; VARCHAR et les types TEXT les conservent.

Jeux de Caractères UTF-8

MySQL propose deux implémentations principales pour UTF-8 :

1. utf8mb3 (historique) : Par défaut jusqu'à MySQL 5.7, il supporte jusqu'à 3 octets par caractère. Il ne peut pas représenter tous les caractères Unicode, notamment les emojis ou certains caractères asiatiques.
2. utf8mb4 (recommandé) : Par défaut depuis MySQL 8.0, il supporte jusqu'à 4 octets par caractère, couvrant ainsi l'intégralité du plan Unicode. Il est recommandé pour toute nouvelle application.

Opérations DDL sur les Bases de Données

Les commandes DDL permettent de gérer le cycle de vie des bases de données :

Créer une Base de Données

Permet de créer une nouvelle base de données, en spécifiant éventuellement son jeu de caractères et sa collation par défaut.

CREATE DATABASE MaNouvelleDB
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Afficher les Bases de Données

Liste toutes les bases de données accessibles par l'utilisateur courant.

SHOW DATABASES;

Afficher la Définition d'une Base de Données

Montre la commande CREATE DATABASE exacte utilisée pour définir une base de données.

SHOW CREATE DATABASE MaNouvelleDB;
-- Ou pour un affichage plus lisible :
SHOW CREATE DATABASE MaNouvelleDB\G

Modifier une Base de Données

Permet de changer les attributs d'une base de données existante, tels que son jeu de caractères ou sa collation. Notez que ces modifications n'affectent que les nouvelles tables créées dans cette base de données par la suite.

ALTER DATABASE MaNouvelleDB
    CHARACTER SET latin1
    COLLATE latin1_general_ci;

Utiliser une Base de Données

Sélectionne la base de données active pour les opérations suivantes, évitant ainsi de préfixer les noms de table.

USE MaNouvelleDB;

Supprimer une Base de Données

Supprime définitivement une base de données et toutes ses tables, vues, procédures stockées, etc. Cette commande est irrévesrible et doit être utilisée avec prudence.

DROP DATABASE MaNouvelleDB;

Opérations DDL sur les Tables

La gestion des tables est au cœur du DDL. Voici les commandes courantes :

Pour les exemples suivants, nous utiliserons une base de données nommée entreprise_db. Assurez-vous qu'elle existe ou créez-la :

CREATE DATABASE IF NOT EXISTS entreprise_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
USE entreprise_db;

Afficher les Tables

Liste les tables au sein de la base de données actuellement sélectionnée.

SHOW TABLES;
-- Pour afficher les tables d'une autre base de données sans changer la base courante :
SHOW TABLES FROM mysql;

Créer une Table

Définit la structure d'une nouvelle table, incluant les noms de colonnes, leurs types de données, et diverses contraintes (clés primaries, valeurs par défaut, etc.).

CREATE TABLE Personnel (
    employe_id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    date_naissance DATE,
    fonction VARCHAR(100),
    salaire DECIMAL(10, 2),
    date_embauche DATE DEFAULT CURRENT_DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Afficher la Structure et la Définition d'une Table

Permet d'inspecter la structure d'une table.

• DESCRIBE (ou DESC) montre les colonnes, leurs types, si elles peuvent être NULL, les clés, les valeurs par défaut et d'autres informations.
• SHOW CREATE TABLE affiche l'instruction DDL exacte utilisée pour créer la table.

DESCRIBE Personnel;

SHOW CREATE TABLE Personnel\G;

Modifier la Structure d'une Table (ALTER TABLE)

La commande ALTER TABLE est polyvalente pour modifier une table existante.

Ajouter une Colonne

Permet d'ajouter une nouvelle colonne à une table, spécifiant son nom, son type, et optionnellement sa position (FIRST ou AFTER une autre colonne).

-- Ajouter une colonne à la fin
ALTER TABLE Personnel ADD COLUMN email VARCHAR(100);

-- Ajouter une colonne après une colonne existante
ALTER TABLE Personnel ADD COLUMN departement_id INT AFTER fonction;

-- Ajouter une colonne au début de la table
ALTER TABLE Personnel ADD COLUMN numero_matricule VARCHAR(10) FIRST;

Supprimer une Colonne

Supprime une colonne spécifiée de la table.

ALTER TABLE Personnel DROP COLUMN numero_matricule;

Modifier le Type de Données d'une Colonne

Change le type de données d'une colonne existante. Cela peut entraîner une perte de données si le nouveau type est moins restrictif ou incompatible.

ALTER TABLE Personnel MODIFY COLUMN salaire DECIMAL(12, 2);

Renommer une Colonne et/ou Modifier son Type

La commande CHANGE COLUMN permet de renommer une colonne et de modifier son type de données en une seule opération. Il faut spécifier l'ancien nom, le nouveau nom et le nouveau type.

ALTER TABLE Personnel CHANGE COLUMN fonction poste_actuel VARCHAR(120) NOT NULL;

Modifier l'Ordre des Colonnes

Permet de réorganiser les colonnes en utilisant MODIFY COLUMN avec FIRST ou AFTER.

ALTER TABLE Personnel MODIFY COLUMN email VARCHAR(100) AFTER nom;

Renommer une Table

Change le nom d'une table existante.

RENAME TABLE Personnel TO Employes;
-- Alternativement :
ALTER TABLE Employes RENAME TO ListeEmployes;

Supprimer une Table

Supprime définitivement la table spécifiée et toutes les données qu'elle contient. Cette commande est irréversible.

DROP TABLE ListeEmployes;

Gestion des Clés Étrangères et Index

Les clés étrangères et les index sont cruciaux pour l'intégrité référentielle et les performances.

Clés Étrangères

Les clés étrangères (FOREIGN KEY) établissent des liens entre les tables et garantissent l'intégrité référentielle. Elles définissent également des actions à effectuer lors de la suppression (ON DELETE) ou de la mise à jour (ON UPDATE) des enregistrements de la table parente.

• RESTRICT (par défaut) : Empêche la suppression/mise à jour du parent si des enfants existent.
• CASCADE : Supprime/met à jour les enfants lorsque le parent est supprimé/mis à jour.
• SET NULL : Définit la clé étrangère des enfants à NULL lorsque le parent est supprimé/mis à jour. (Nécessite que la colonne de la clé étrangère accepte NULL).
• NO ACTION : Similaire à RESTRICT, mais peut être reporté jusqu'à la fin de la transaction dans certains systèmes.

-- Création d'une table 'Departements'
CREATE TABLE Departements (
    departement_id INT PRIMARY KEY AUTO_INCREMENT,
    nom_departement VARCHAR(50) UNIQUE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Création de la table 'Employes_FK' avec une clé étrangère
CREATE TABLE Employes_FK (
    employe_id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    departement_id INT,
    CONSTRAINT fk_departement_employe
        FOREIGN KEY (departement_id)
        REFERENCES Departements (departement_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Afficher la structure pour voir la clé étrangère
DESCRIBE Employes_FK;

-- Supprimer une clé étrangère (nécessite le nom de la contrainte)
ALTER TABLE Employes_FK DROP FOREIGN KEY fk_departement_employe;

-- Ajouter ou recréer une clé étrangère avec des actions différentes
ALTER TABLE Employes_FK
ADD CONSTRAINT fk_departement_new_action
    FOREIGN KEY (departement_id)
    REFERENCES Departements (departement_id)
    ON DELETE SET NULL ON UPDATE CASCADE;