Vocabulaire Fondamental et Mots-Clés SQL

La maîtrise de MySQL nécessite une compréhension précise du vocabulaire technique et des mots-clés utilisés pour la définition et la manipulation des données.

Commandes et Modificateurs Principaux

Opérations de Manipulation de Données (DML/DDL)

• Création et Structure : CREATE, ALTER, DROP, DESCRIBE.
• Requêtes et Données : SELECT, INSERT, UPDATE, DELETE.
• Contraintes : PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, REFERENCES.

Implémentation Pratique : Système de Gestion Commerciale

Voici un exemple complet de modélisation et d'implémentation d'une base de données relationnelle pour un système de gestion commerciale. Le schéma intègre des contraintes d'intégrité, des clés étrangères et des index.

-- Création de la base de données avec un encodage moderne
CREATE DATABASE IF NOT EXISTS gestion_commerciale
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE gestion_commerciale;

-- 1. Table des régions (Table parente)
CREATE TABLE regions (
    region_id CHAR(5) PRIMARY KEY,
    nom_region VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- 2. Table des fournisseurs
CREATE TABLE fournisseurs (
    fournisseur_id CHAR(8) PRIMARY KEY,
    raison_sociale VARCHAR(100) NOT NULL,
    personne_contact VARCHAR(50),
    telephone CHAR(15),
    region_id CHAR(5),
    CONSTRAINT fk_fournisseur_region FOREIGN KEY (region_id) 
        REFERENCES regions(region_id) ON UPDATE CASCADE
) ENGINE=InnoDB;

-- 3. Table des catégories de produits
CREATE TABLE categories (
    categorie_id CHAR(6) PRIMARY KEY,
    libelle VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
) ENGINE=InnoDB;

-- 4. Table des produits
CREATE TABLE produits (
    produit_id CHAR(10) PRIMARY KEY,
    designation VARCHAR(100) NOT NULL,
    prix_unitaire DECIMAL(10, 2) UNSIGNED CHECK (prix_unitaire >= 0),
    stock_minimum INT UNSIGNED DEFAULT 0,
    categorie_id CHAR(6),
    fournisseur_id CHAR(8),
    CONSTRAINT fk_produit_categorie FOREIGN KEY (categorie_id) REFERENCES categories(categorie_id),
    CONSTRAINT fk_produit_fournisseur FOREIGN KEY (fournisseur_id) REFERENCES fournisseurs(fournisseur_id)
) ENGINE=InnoDB;

-- 5. Table des clients
CREATE TABLE clients (
    client_id CHAR(10) PRIMARY KEY,
    nom_client VARCHAR(80) NOT NULL,
    email VARCHAR(100) UNIQUE,
    telephone CHAR(15) NOT NULL,
    region_id CHAR(5),
    CONSTRAINT fk_client_region FOREIGN KEY (region_id) REFERENCES regions(region_id)
) ENGINE=InnoDB;

-- 6. Table des commandes (En-tête)
CREATE TABLE commandes (
    commande_id INT AUTO_INCREMENT PRIMARY KEY,
    client_id CHAR(10) NOT NULL,
    date_commande DATETIME DEFAULT CURRENT_TIMESTAMP,
    statut ENUM('En attente', 'Expédiée', 'Livrée', 'Annulée') DEFAULT 'En attente',
    CONSTRAINT fk_commande_client FOREIGN KEY (client_id) REFERENCES clients(client_id)
) ENGINE=InnoDB;

-- 7. Table de détail des commandes (Liaison N-N avec attributs)
CREATE TABLE details_commande (
    commande_id INT,
    produit_id CHAR(10),
    quantite INT UNSIGNED CHECK (quantite > 0),
    prix_vente DECIMAL(10, 2) UNSIGNED,
    PRIMARY KEY (commande_id, produit_id),
    CONSTRAINT fk_detail_commande FOREIGN KEY (commande_id) REFERENCES commandes(commande_id) ON DELETE CASCADE,
    CONSTRAINT fk_detail_produit FOREIGN KEY (produit_id) REFERENCES produits(produit_id)
) ENGINE=InnoDB;

Insertion et Modification des Données

-- Insertion dans les tables parentes
INSERT INTO regions (region_id, nom_region) VALUES 
('FR-IDF', 'Île-de-France'), 
('FR-PAC', 'Provence-Alpes-Côte d\'Azur');

INSERT INTO clients (client_id, nom_client, email, telephone, region_id) VALUES
('CLI-001', 'Dupont SARL', 'contact@dupont.fr', '0145678900', 'FR-IDF');

INSERT INTO categories (categorie_id, libelle) VALUES 
('CAT-INFO', 'Informatique');

INSERT INTO produits (produit_id, designation, prix_unitaire, categorie_id) VALUES
('PROD-001', 'Laptop Pro 15', 1299.99, 'CAT-INFO');

-- Création d'une commande
INSERT INTO commandes (client_id, statut) VALUES ('CLI-001', 'En attente');
SET @last_cmd = LAST_INSERT_ID();

-- Ajout de détails à la commande
INSERT INTO details_commande (commande_id, produit_id, quantite, prix_vente) VALUES
(@last_cmd, 'PROD-001', 2, 1250.00);

-- Modification de structure (Ajout d'une colonne)
ALTER TABLE clients ADD COLUMN date_inscription DATE DEFAULT (CURRENT_DATE);

Concepts Théoriques et Architecture Relationnelle

Modèle Relationnel et Entité-Asociation (E-A)

Une base de données relationnelle organise les données en tables (relations) composées de lignes (tuples) et de colonnes (attributs). Le modèle Entité-Associasion est utilisé pour la conception conceptuelle :

• Entité : Représentée par un rectangle, elle correspond à une table (ex: Client).
• Attribut : Représenté par une ellipse, il correspond à une colonne (ex: Nom).
• Association : Représentée par un losange, elle définit les liens (1:1, 1:N, N:N) entre les entités.

Les Formes Normales (Normalisation)

La normalisation vise à éliminer les redondances et les anomalies de mise à jour.

1. Première Forme Normale (1FN) : Tous les attributs doivent être atomiques (non décomposables) et la table doit posséder une clé primaire.
2. Deuxième Forme Normale (2FN) : Être en 1FN et tous les attributs non-clés doivent dépendre de la totalité de la clé primaire (élimine les dépendances partielles).
3. Troisième Forme Normale (3FN) : Être en 2FN et aucun attribut non-clé ne doit dépendre d'un autre attribut non-clé (élimine les dépendances transitives).

Types de Données et Moteurs de Stockage

Typologie des Données

• Numériques : INT, DECIMAL. L'attribut UNSIGNED double la capacité maximale en n'autorisant que les valeurs positives.
• Chaînes : CHAR(n) (longueur fixe, padding avec des espaces) vs VARCHAR(n) (longueur variable, optimisation de l'espace).
• Temporels : DATE, TIME, DATETIME, TIMESTAMP. Les fonctions NOW() ou CURRENT_TIMESTAMP capturent l'instant d'exécution.
• Énumérations : ENUM (choix unique dans une liste) et SET (choix multiples dans une liste).

Comparaison des Moteurs de Stockage : InnoDB vs MyISAM

Gestion des Contraintes et Évolution du Schéma

Contraintes d'Intégrité

Les contraintes garantissent la fiabilité des données :

• PRIMARY KEY : Identifiant unique et non nul.
• FOREIGN KEY : Assure l'intégrité référentielle entre deux tables.
• UNIQUE : Empêche les doublons, mais autorise une valeur NULL.
• CHECK : Valide une condition booléenne (pleinement supporté depuis MySQL 8.0.16).

Bonnes Pratiques pour l'Évolution du Schéma (ALTER TABLE)

La modification de tables contenant des millions de lignes peut bloquer la base de données. Voici les stratégies recommandées :

1. Opérations en heure creuse : Planifier les modifications lourdes lors des périodes de faible trafic.
2. Stratégie de la table fantôme (Ghost Table) :
   • Créer une nouvelle table avec la structure désirée.
   • Copier les données de l'ancienne table vers la nouvelle par lots (INSERT INTO ... SELECT).
   • Renommer l'ancienne table (backup) et renommer la nouvelle table avec le nom original.
3. Ajout de colonnes : L'ajout d'une colonne à la fin de la table avec une valeur par défaut est généralement une opération métadonnée rapide (Online DDL) dans InnoDB, ne nécessitant pas de réécriture complète de la table.