SQL &
Modélisation
Modélisation entité-relation, DDL, DML et jointures — exemples MySQL / MariaDB.
Entités, attributs & relations
La modélisation conceptuelle (MCD) décrit les données indépendamment de toute technologie. On identifie les entités, leurs attributs et les relations entre elles.
Exemple : système de commandes
La relation N:N entre COMMANDE et PRODUIT sera résolue par une table d'association LIGNE_COMMANDE avec ses propres attributs (quantité, prix unitaire).
Du MCD au MLD (passage au relationnel)
Chaque entité devient une table. Chaque attribut devient une colonne.
La clé primaire du côté "1" est ajoutée comme FK du côté "N".
Nouvelle table avec les deux PK comme FK. Les attributs de la relation deviennent des colonnes.
La FK est ajoutée dans l'une des tables avec contrainte UNIQUE.
-- COMMANDE N:N PRODUIT → LIGNE_COMMANDE
CREATE TABLE ligne_commande (
id_commande INT NOT NULL,
id_produit INT NOT NULL,
quantite INT NOT NULL DEFAULT 1,
prix_unitaire DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id_commande, id_produit),
FOREIGN KEY (id_commande) REFERENCES commande(id_commande),
FOREIGN KEY (id_produit) REFERENCES produit(id_produit)
);Cardinalités
La cardinalité exprime le nombre minimum et maximum de fois qu'une entité participe à une relation. Notation : (min, max).
Notation UML vs Merise : en Merise, on écrit (min,max) près de l'entité concernée. En UML, la multiplicité s'écrit à l'autre extrémité. Ne pas confondre les deux conventions dans un même document.
Merise — MCD (Modèle Conceptuel des Données)
Merise est une méthode française d'analyse et de conception des systèmes d'information. Le MCD représente les données de façon abstraite, indépendante de toute implémentation.
Symboles du MCD
Rectangle à double bandeau. L'identifiant est souligné. Représente un objet du monde réel.
Losange. Lie deux ou plusieurs entités. Peut avoir des attributs propres. Le verbe décrit le lien.
Ellipse (notation complète). En pratique souvent listés directement dans la boîte entité.
Exemple MCD — Bibliothèque
Règles de construction du MCD
Cardinalités en Merise — notation précise
| Notation | Lecture | Exemple |
|---|---|---|
0,1 | zéro ou un | Un employé peut avoir 0 ou 1 bureau |
1,1 | exactement un | Une commande est passée par exactement 1 client |
0,N | zéro ou plusieurs | Un client passe 0 ou N commandes |
1,N | un ou plusieurs | Une commande contient 1 ou N lignes |
Piège fréquent : en Merise, la cardinalité (min,max) se lit "combien de fois cette entité participe à l'association". Elle s'écrit du côté de l'entité, proche d'elle — pas de l'autre côté comme en UML.
Merise — MLD & MPD
Le passage du MCD au MLD (Modèle Logique des Données) traduit le modèle conceptuel en tables relationnelles selon des règles mécaniques. Le MPD ajoute les détails propres au SGBD cible (types, index).
Règles de passage MCD → MLD
L'identifiant devient la clé primaire. Chaque attribut devient une colonne.
La clé primaire du côté "1" migre dans la table du côté "N" comme clé étrangère.
Nouvelle table dont la clé primaire est la concaténation des deux clés étrangères. Les attributs de l'association deviennent des colonnes.
Les deux entités peuvent être fusionnées, ou l'une reçoit la FK de l'autre avec contrainte UNIQUE.
Exemple complet — Bibliothèque
↳ attribut d'association : date_retour
AUTEUR (1,N) ────ÉCRIT───── (1,N) LIVRE
LIVRE(isbn, titre, annee, disponible)
AUTEUR(id_auteur, nom_auteur)
EMPRUNTE(#num_adherent, #isbn, date_retour)
↳ R3 : N,N → table d'association
ÉCRIT(#id_auteur, #isbn)
↳ R3 : N,N → table d'association
Convention de notation MLD : colonne soulignée = clé primaire, #colonne = clé étrangère. Les deux peuvent se combiner pour une table d'association.
Du MLD au MPD — ajout des détails SQL
EMPRUNTE(#num_adherent, #isbn, date_retour)
CREATE TABLE adherent (
num_adherent INT UNSIGNED AUTO_INCREMENT,
nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
email VARCHAR(254) NOT NULL,
PRIMARY KEY (num_adherent),
UNIQUE KEY uq_email (email)
) ENGINE=InnoDB;
CREATE TABLE emprunte (
num_adherent INT UNSIGNED NOT NULL,
isbn CHAR(13) NOT NULL,
date_retour DATE,
PRIMARY KEY (num_adherent, isbn),
FOREIGN KEY (num_adherent)
REFERENCES adherent(num_adherent),
FOREIGN KEY (isbn)
REFERENCES livre(isbn)
) ENGINE=InnoDB;Résumé des trois niveaux Merise : le MCD est indépendant de tout SGBD (conceptuel), le MLD est indépendant du SGBD mais exprime les tables et les liens (logique), le MPD est spécifique au SGBD cible avec les types, index et contraintes SQL (physique).
Formes normales
La normalisation élimine la redondance et les anomalies de mise à jour. On applique les formes normales successivement.
| Forme | Règle | Anomalie éliminée | Exemple de violation |
|---|---|---|---|
| 1NF | Chaque cellule contient une valeur atomique (non divisible). Pas de répétition de colonnes. | Valeurs multiples dans un champ | telephones = "0499, 0477" |
| 2NF | 1NF + chaque attribut non-clé dépend de toute la clé primaire (pas d'une partie). | Dépendance partielle (clé composite) | Table (cmd_id, prod_id, nom_produit) — nom_produit ne dépend que de prod_id |
| 3NF | 2NF + pas de dépendance transitive entre attributs non-clés. | Dépendance transitive | Table (emp_id, dept_id, ville_dept) — ville_dept dépend de dept_id, pas de emp_id |
| BCNF | Forme renforcée de 3NF. Chaque déterminant est une clé candidate. | Anomalies résiduelles rares | Cas avec plusieurs clés candidates qui se chevauchent |
En pratique, 3NF suffit pour la quasi-totalité des projets. BCNF et au-delà sont réservés aux cas académiques ou aux systèmes très complexes.
Types de données MySQL
| Type | Usage | Taille / plage |
|---|---|---|
INT | Entier standard | ±2 milliards |
BIGINT | Grand entier (IDs) | ±9×10¹⁸ |
TINYINT(1) | Booléen (0/1) | 0 ou 1 |
DECIMAL(p,s) | Montant, prix | p chiffres, s décimales |
FLOAT / DOUBLE | Mesures scientifiques | Imprécis — éviter pour l'argent |
VARCHAR(n) | Texte variable | max n caractères (≤ 65 535) |
CHAR(n) | Texte fixe (codes) | Toujours n octets |
TEXT | Long texte | Jusqu'à 65 535 octets |
DATE | Date seule | YYYY-MM-DD |
DATETIME | Date + heure | YYYY-MM-DD HH:MM:SS |
TIMESTAMP | Horodatage auto | Stocké en UTC |
ENUM('a','b') | Valeur parmi une liste | Stocké comme entier |
Ne jamais utiliser FLOAT ou DOUBLE pour stocker des montants monétaires — les erreurs d'arrondi sont inévitables. Toujours utiliser DECIMAL(10,2).
VARCHAR(255) est la valeur par défaut raisonnable pour les champs texte courts. Pour les emails : VARCHAR(254) (limite RFC 5321). Pour les hash bcrypt : CHAR(60).
-- ID auto-incrémenté
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
-- Montant prix
prix DECIMAL(10,2) NOT NULL DEFAULT 0.00
-- Statut avec liste fermée
statut ENUM('actif','inactif','banni') DEFAULT 'actif'
-- Timestamps automatiques
cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
modifie_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMPCREATE TABLE
CREATE TABLE client (
-- Clé primaire auto-incrémentée
id_client INT UNSIGNED AUTO_INCREMENT,
nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
email VARCHAR(254) NOT NULL,
telephone VARCHAR(20),
date_nais DATE,
actif TINYINT(1) NOT NULL DEFAULT 1,
cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id_client),
UNIQUE KEY uq_email (email)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
CREATE TABLE commande (
id_commande INT UNSIGNED AUTO_INCREMENT,
id_client INT UNSIGNED NOT NULL,
date_cmd DATE NOT NULL,
statut ENUM('en_attente','validee','livree','annulee')
NOT NULL DEFAULT 'en_attente',
total_ht DECIMAL(10,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY (id_commande),
FOREIGN KEY (id_client)
REFERENCES client(id_client)
ON DELETE RESTRICT
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Options ON DELETE / ON UPDATE
| Option | Comportement si parent modifié/supprimé |
|---|---|
RESTRICT | Interdit l'opération si des enfants existent |
CASCADE | Propage la modification/suppression aux enfants |
SET NULL | Met la FK à NULL dans les lignes enfants |
NO ACTION | Similaire à RESTRICT (vérification différée) |
InnoDB est le seul moteur MySQL qui supporte les clés étrangères. MyISAM les ignore silencieusement — ne jamais utiliser MyISAM pour des données relationnelles.
Toujours spécifier utf8mb4 (et non utf8) pour un vrai support Unicode incluant les emojis. utf8 en MySQL est un encodage tronqué sur 3 octets.
Contraintes & clés
| Contrainte | Rôle | Syntaxe MySQL |
|---|---|---|
| PRIMARY KEY | Identifie de façon unique chaque ligne. Implique NOT NULL + UNIQUE. Une seule par table. | PRIMARY KEY (col)col INT AUTO_INCREMENT PRIMARY KEY |
| FOREIGN KEY | Référence une PK ou UK d'une autre table. Garantit l'intégrité référentielle. | FOREIGN KEY (col) REFERENCES table(col) |
| UNIQUE | Interdit les doublons dans une colonne (ou groupe). Autorise NULL (une fois). | UNIQUE KEY nom (col) |
| NOT NULL | La colonne ne peut pas contenir de valeur NULL. | col VARCHAR(100) NOT NULL |
| DEFAULT | Valeur insérée si aucune valeur n'est fournie. | col INT DEFAULT 0 |
| CHECK | Vérifie une condition sur la valeur (MySQL 8.0.16+). | CHECK (age >= 0 AND age <= 150) |
| INDEX | Accélère les recherches. Créé automatiquement sur PK et FK. | INDEX idx_nom (nom) |
ALTER TABLE & DROP
-- Ajouter une colonne
ALTER TABLE client
ADD COLUMN adresse VARCHAR(255) AFTER prenom;
-- Modifier le type d'une colonne
ALTER TABLE client
MODIFY COLUMN telephone VARCHAR(30);
-- Renommer une colonne (MySQL 8.0+)
ALTER TABLE client
RENAME COLUMN date_nais TO date_naissance;
-- Supprimer une colonne
ALTER TABLE client
DROP COLUMN telephone;
-- Ajouter une clé étrangère
ALTER TABLE commande
ADD CONSTRAINT fk_client
FOREIGN KEY (id_client) REFERENCES client(id_client);
-- Supprimer une table (DANGER)
DROP TABLE IF EXISTS ligne_commande;
DROP TABLE IF EXISTS commande; -- ordre important (FK)
DROP TABLE IF EXISTS client;Toujours supprimer les tables dans l'ordre inverse de leur création (enfants avant parents) pour respecter les contraintes de clés étrangères. Ou désactiver temporairement avec SET FOREIGN_KEY_CHECKS = 0;.
SELECT — Structure complète
Ordre d'écriture des clauses (différent de l'ordre d'exécution).
SELECT
c.nom,
c.prenom,
COUNT(cmd.id_commande) AS nb_commandes,
SUM(cmd.total_ht) AS total_achats,
MAX(cmd.date_cmd) AS derniere_cmd
FROM client c
INNER JOIN commande cmd ON c.id_client = cmd.id_client
WHERE
c.actif = 1
AND cmd.statut != 'annulee'
AND cmd.date_cmd >= '2024-01-01'
GROUP BY c.id_client, c.nom, c.prenom
HAVING COUNT(cmd.id_commande) > 3
ORDER BY total_achats DESC
LIMIT 10;Ordre d'exécution (≠ ordre d'écriture)
WHERE filtre avant le GROUP BY — on ne peut pas y utiliser les alias ou les fonctions d'agrégation. Pour filtrer un résultat agrégé, utiliser HAVING.
Opérateurs & fonctions utiles
Filtres WHERE
| LIKE 'A%' | Commence par A |
| IN (1,2,3) | Dans une liste |
| BETWEEN a AND b | Intervalle inclus |
| IS NULL | Valeur nulle |
| IS NOT NULL | Non nulle |
INSERT / UPDATE / DELETE
── INSERT ──────────────────────────────────────
-- Insertion simple
INSERT INTO client (nom, prenom, email)
VALUES ('Dupont', 'Alice', 'alice@example.com');
-- Insertion multiple
INSERT INTO client (nom, prenom, email) VALUES
('Martin', 'Bob', 'bob@example.com'),
('Leroy', 'Carol', 'carol@example.com');
-- Insérer ou mettre à jour si doublon de clé unique
INSERT INTO client (email, nom, prenom)
VALUES ('alice@example.com', 'Dupont', 'Alice')
ON DUPLICATE KEY UPDATE
nom = VALUES(nom);
── UPDATE ──────────────────────────────────────
-- Toujours avec WHERE (sinon : toute la table !!)
UPDATE client
SET actif = 0,
modifie_le = NOW()
WHERE id_client = 42;
── DELETE ──────────────────────────────────────
-- Toujours avec WHERE (sinon : toute la table !!)
DELETE FROM commande
WHERE statut = 'annulee'
AND date_cmd < '2023-01-01';
-- Vider une table (plus rapide que DELETE sans WHERE)
TRUNCATE TABLE logs_temp; -- remet l'auto_increment à 1UPDATE et DELETE sans WHERE affectent toutes les lignes de la table. Toujours tester sa condition avec un SELECT avant d'exécuter un UPDATE/DELETE critique.
Jointures
Une jointure combine les lignes de deux tables selon une condition. Le type de jointure détermine ce qui se passe quand il n'y a pas de correspondance.
SELECT c.nom, cmd.date_cmd
FROM client c
INNER JOIN commande cmd
ON c.id_client = cmd.id_client;-- Clients SANS commande : IS NULL
SELECT c.nom, cmd.id_commande
FROM client c
LEFT JOIN commande cmd
ON c.id_client = cmd.id_client
WHERE cmd.id_commande IS NULL;SELECT c.nom, cmd.date_cmd
FROM client c
RIGHT JOIN commande cmd
ON c.id_client = cmd.id_client;-- Simulation MySQL
SELECT * FROM client c
LEFT JOIN commande cmd ON c.id_client = cmd.id_client
UNION
SELECT * FROM client c
RIGHT JOIN commande cmd ON c.id_client = cmd.id_client;Cheat sheet
DDL — structure
| CREATE TABLE | Créer une table |
| ALTER TABLE … ADD | Ajouter une colonne |
| ALTER TABLE … MODIFY | Modifier un type |
| ALTER TABLE … DROP | Supprimer une colonne |
| DROP TABLE IF EXISTS | Supprimer une table |
| TRUNCATE TABLE | Vider une table |
DML — données
| SELECT … FROM … WHERE | Lire |
| INSERT INTO … VALUES | Insérer |
| UPDATE … SET … WHERE | Modifier |
| DELETE FROM … WHERE | Supprimer |
| GROUP BY / HAVING | Agréger / filtrer |
| ORDER BY … LIMIT | Trier / paginer |
Fonctions d'agrégation
| COUNT(*) | Nombre de lignes |
| COUNT(col) | Lignes non NULL |
| SUM(col) | Somme |
| AVG(col) | Moyenne |
| MIN(col) | Minimum |
| MAX(col) | Maximum |
Jointures — résumé
| INNER JOIN | Correspondance des deux côtés |
| LEFT JOIN | Tout à gauche + correspondances |
| RIGHT JOIN | Tout à droite + correspondances |
| LEFT JOIN … IS NULL | Lignes sans correspondance |
| UNION | Simule FULL OUTER JOIN |