SQL et modélisation

Avec Doctrine, on peut écrire une application entière sans taper une ligne de SQL. C'est confortable, jusqu'au jour où une page met huit secondes à s'afficher, où un rapport compte deux fois les mêmes commandes, ou où deux clients achètent le dernier article en même temps. Ces problèmes se comprennent et se règlent dans la base de données.

Cette fiche est longue, parce que le sujet est central. Elle suit l'ordre dans lequel on travaille : modéliser (sections 1 à 5), interroger (6 à 9), aller vite (10 et 11), puis protéger (12 et 13). Tu peux t'arrêter entre deux parties, et la fiche 8 montrera comment Doctrine s'appuie sur tout ça.

1.Le point de départ : un tableur

Beaucoup de projets commencent comme un tableur : une ligne par commande, et dans chaque ligne, tout ce qu'on sait. Voici ce qui arrive quand on met ça tel quel dans une table :

-- Une seule table, comme dans un tableur :
-- chaque commande recopie le client et liste ses produits
CREATE TABLE orders_sheet (
    id             INTEGER PRIMARY KEY,
    customer_name  TEXT,
    customer_email TEXT,
    products       TEXT,
    total_in_cents INTEGER
);

INSERT INTO orders_sheet VALUES
    (1, 'Alice', 'alice@example.com', 'Clavier, Souris', 6980),
    (2, 'Alice', 'alice@example.com', 'Écran', 19900),
    (3, 'Bob', 'bob@example.com', 'Clavier', 4990);

-- Alice change d'e-mail… et on ne met à jour que sa dernière commande
UPDATE orders_sheet SET customer_email = 'alice@new.example' WHERE id = 2;

-- Les e-mails d'Alice, selon la base
SELECT DISTINCT customer_email
FROM orders_sheet
WHERE customer_name = 'Alice';

-- Combien de claviers vendus ? On ne peut compter que des commandes, pas des claviers
SELECT count(*) AS orders_with_keyboard
FROM orders_sheet
WHERE products LIKE '%Clavier%';

▸ Les e-mails d'Alice, selon la base
customer_email
-----------------
alice@example.com
alice@new.example
(2 lignes)

▸ Combien de claviers vendus ? On ne peut compter que des commandes, pas des claviers
orders_with_keyboard
--------------------
2
(1 ligne)

1. L'information est en double : l'e-mail d'Alice est recopié dans chaque commande. Le jour où il change, il faut penser à toutes les lignes. Un oubli, et la base contient deux vérités.
2. Une case contient une liste : « Clavier, Souris » est un texte. Impossible de compter les claviers vendus, ni de connaître la quantité ou le prix de chacun.
3. Rien n'est vérifié : un e-mail vide, un total négatif, un produit qui n'existe pas, tout passe.

Ces problèmes s'appellent des anomalies. Les sections suivantes montrent comment les éviter.

2.Tables et relations

La règle de départ : une table par sorte de chose (les clients, les produits, les commandes), et chaque chose n'est enregistrée qu'une seule fois. Les tables se relient par des clés étrangères : une colonne qui contient l'identifiant d'une ligne d'une autre table. Voici le schéma utilisé par tous les exemples de la fiche :

-- Le schéma et les données utilisés par les exemples de la fiche

CREATE TABLE customer (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL,
    phone TEXT                      -- facultatif : peut rester NULL
);

CREATE TABLE product (
    id             INTEGER PRIMARY KEY,
    name           TEXT    NOT NULL,
    price_in_cents INTEGER NOT NULL CHECK (price_in_cents >= 0),
    stock          INTEGER NOT NULL CHECK (stock >= 0)
);

-- « order » est un mot réservé du SQL : on évite de l'utiliser comme nom de table
CREATE TABLE customer_order (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer (id) ON DELETE RESTRICT,
    created_at  TEXT    NOT NULL,
    shipped_at  TEXT                -- NULL tant que la commande n'est pas expédiée
);

-- Table de liaison entre commandes et produits (relation plusieurs-à-plusieurs)
CREATE TABLE order_item (
    order_id            INTEGER NOT NULL
                        REFERENCES customer_order (id) ON DELETE CASCADE,
    product_id          INTEGER NOT NULL REFERENCES product (id),
    quantity            INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_in_cents INTEGER NOT NULL,   -- le prix AU MOMENT de la commande
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO customer (id, email, name, phone) VALUES
    (1, 'alice@example.com', 'Alice', '0600000001'),
    (2, 'bob@example.com', 'Bob', NULL),
    (3, 'chloe@example.com', 'Chloé', NULL);          -- n'a jamais commandé

INSERT INTO product (id, name, price_in_cents, stock) VALUES
    (1, 'Clavier', 4990, 10),
    (2, 'Souris', 1990, 2),
    (3, 'Écran', 19900, 5),
    (4, 'Câble', 990, 50);                            -- jamais vendu

INSERT INTO customer_order (id, customer_id, created_at, shipped_at) VALUES
    (1, 1, '2026-10-02 10:15:00', '2026-10-03 09:00:00'),
    (2, 1, '2026-11-01 18:40:00', NULL),
    (3, 2, '2026-10-20 08:05:00', '2026-10-21 14:30:00');

INSERT INTO order_item (order_id, product_id, quantity, unit_price_in_cents) VALUES
    (1, 1, 1, 4990),
    (1, 2, 2, 1990),
    (2, 3, 1, 19900),
    (3, 1, 2, 4790);   -- Bob a payé le clavier moins cher : il était en promotion

1. Un-à-plusieurs : une cliente a plusieurs commandes, une commande appartient à un seul client. La clé étrangère customer_id va du côté « plusieurs », dans customer_order.
2. Plusieurs-à-plusieurs : une commande contient plusieurs produits, un produit apparaît dans plusieurs commandes. Il faut une table de liaison, ici order_item, qui porte aussi les informations propres à la relation : la quantité et le prix payé.
3. Un-à-un : plus rare, par exemple un utilisateur et son profil détaillé. Souvent, une seule table suffit.

Remarque unit_price_in_cents dans order_item : ce n'est pas une copie inutile du prix du produit, c'est le prix au moment de la commande. Bob a payé son clavier 47,90 € pendant une promotion, alors qu'il en vaut 49,90 € aujourd'hui. Sa facture ne doit pas changer. C'est une donnée historique, et elle a sa place ici.

3.La normalisation

La normalisation est la méthode qui formalise ce découpage. Elle s'exprime en « formes normales ». Les trois premières suffisent dans la pratique :

1. Première forme normale : une case contient une seule valeur. Pas de liste « Clavier, Souris », pas de colonnes phone1, phone2, phone3.
2. Deuxième forme normale : dans une table dont la clé est composée de plusieurs colonnes, chaque autre colonne dépend de toute la clé. Dans order_item (clé : commande + produit), le nom du produit ne dépend que du produit : il va dans product.
3. Troisième forme normale : une colonne ne dépend pas d'une autre colonne qui n'est pas la clé. Dans une commande, l'e-mail dépend du client, pas de la commande : il va dans customer.

Un moyen mnémotechnique anglais résume les trois : chaque colonne dépend « de la clé, de toute la clé, et de rien d'autre que la clé ».

4.Choisir les bons types

Le type d'une colonne n'est pas un détail : c'est la première vérification que fait la base. Encore faut-il qu'elle la fasse. SQLite, par exemple, est très permissif par défaut :

-- Par défaut, SQLite accepte du texte dans une colonne INTEGER
CREATE TABLE loose_product (id INTEGER PRIMARY KEY, price_in_cents INTEGER);
INSERT INTO loose_product (price_in_cents) VALUES ('douze euros');

-- Ce que la table contient vraiment
SELECT price_in_cents, typeof(price_in_cents) AS stored_type FROM loose_product;

-- Une table STRICT (SQLite 3.37 et plus) vérifie les types, comme PostgreSQL
CREATE TABLE strict_product (id INTEGER PRIMARY KEY, price_in_cents INTEGER) STRICT;

-- La même insertion dans une table STRICT
INSERT INTO strict_product (price_in_cents) VALUES ('douze euros');

-- Un nombre à virgule flottante n'est pas exact : à éviter pour de l'argent
SELECT 0.1 + 0.2 = 0.3 AS is_equal, printf('%.17f', 0.1 + 0.2) AS real_value;

▸ Ce que la table contient vraiment
price_in_cents | stored_type
---------------+------------
douze euros    | text
(1 ligne)

▸ La même insertion dans une table STRICT
ERREUR : cannot store TEXT value in INTEGER column strict_product.price_in_cents

▸ Un nombre à virgule flottante n'est pas exact : à éviter pour de l'argent
is_equal | real_value
---------+--------------------
0        | 0.30000000000000000
(1 ligne)

SQLite a accepté « douze euros » dans une colonne INTEGER, sauf dans une table STRICT. PostgreSQL, lui, refuse toujours une valeur qui ne peut pas être convertie dans le type de la colonne. Retiens-le si tu utilises SQLite pour tes tests : un bug de type peut passer sur SQLite et casser en production.

Sous PostgreSQL, voici les choix qui évitent la plupart des ennuis :

-- Des types choisis pour ce qu'ils garantissent (PostgreSQL)
CREATE TABLE invoice (
    id           UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
    number       TEXT          NOT NULL UNIQUE,            -- « FAC-2026-0042 »
    amount       NUMERIC(10,2) NOT NULL CHECK (amount >= 0), -- décimal exact
    is_paid      BOOLEAN       NOT NULL DEFAULT false,
    issued_on    DATE          NOT NULL,                   -- une date seule
    paid_at      TIMESTAMPTZ,                              -- un instant précis, avec fuseau
    customer_id  BIGINT        NOT NULL REFERENCES customer (id)
);

1. L'argent : des entiers en centimes, ou NUMERIC (décimal exact). Jamais FLOAT ni REAL, qui arrondissent comme le montre 0.1 + 0.2.
2. Le temps : DATE pour une date seule (une date de naissance), TIMESTAMPTZ pour un instant précis (un paiement). Le « TZ » (time zone) évite qu'un même instant soit lu différemment selon le fuseau du serveur.
3. Le texte : sous PostgreSQL, TEXT et VARCHAR(n) ont les mêmes performances. Une limite de longueur sert à exprimer une règle métier, pas à gagner de la place.
4. Les booléens : BOOLEAN, plutôt qu'un entier 0/1 ou un texte « oui »/« non ».

Identifiant auto-incrémenté ou UUID ?

Un identifiant auto-incrémenté (1, 2, 3…) est compact et rapide à indexer. Un UUID (un identifiant aléatoire de 128 bits, comme 3f2c…e91a) se génère n'importe où, même avant d'enregistrer la ligne, et ne révèle rien. Les UUID « version 7 », que le composant Uid de Symfony sait générer, commencent par la date de création : ils restent triables et s'indexent mieux que les UUID purement aléatoires.

Une colonne JSON ?

PostgreSQL propose le type JSONB, qui stocke un document JSON et permet de chercher dedans. C'est tentant pour « tout ce qui ne rentre pas ailleurs » :

-- Des caractéristiques qui changent selon le type de produit :
-- un usage raisonnable d'une colonne JSONB (PostgreSQL)
CREATE TABLE product (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT  NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'   -- {"couleur": "noir", "dpi": 1600}
);

-- On peut chercher dedans…
SELECT name FROM product WHERE attributes @> '{"couleur": "noir"}';

-- … et indexer ces recherches avec un index GIN
CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

-- Mais rien ne garantit que "dpi" est présent, ni que c'est un nombre :
-- ce qui est vérifié par la base doit rester dans de vraies colonnes.

5.Les contraintes : la base vérifie les règles

Une contrainte est une règle déclarée dans le schéma, que la base vérifie à chaque écriture. Voici le schéma de la section 2 face à des données invalides :

-- @requires: schema.sql

-- Un e-mail déjà utilisé
INSERT INTO customer (email, name) VALUES ('alice@example.com', 'Alice bis');

-- Un prix négatif
INSERT INTO product (name, price_in_cents, stock) VALUES ('Câble USB', -100, 3);

-- Une commande pour un client qui n'existe pas
INSERT INTO customer_order (customer_id, created_at) VALUES (99, '2026-11-03 10:00:00');

-- Une quantité nulle
INSERT INTO order_item (order_id, product_id, quantity, unit_price_in_cents)
VALUES (1, 3, 0, 19900);

-- Supprimer une cliente qui a des commandes
DELETE FROM customer WHERE id = 1;

-- Une donnée valide, elle, est acceptée
INSERT INTO customer (email, name) VALUES ('david@example.com', 'David');

-- David est bien enregistré
SELECT id, name, email FROM customer WHERE email = 'david@example.com';

▸ Un e-mail déjà utilisé
ERREUR : UNIQUE constraint failed: customer.email

▸ Un prix négatif
ERREUR : CHECK constraint failed: price_in_cents >= 0

▸ Une commande pour un client qui n'existe pas
ERREUR : FOREIGN KEY constraint failed

▸ Une quantité nulle
ERREUR : CHECK constraint failed: quantity > 0

▸ Supprimer une cliente qui a des commandes
ERREUR : FOREIGN KEY constraint failed

▸ David est bien enregistré
id | name  | email
---+-------+------------------
4  | David | david@example.com
(1 ligne)

• NOT NULL : la valeur est obligatoire.
• UNIQUE : la valeur ne peut apparaître qu'une fois dans la colonne.
• CHECK : une condition doit être vraie (un prix positif, une quantité non nulle).
• REFERENCES (clé étrangère) : la ligne référencée doit exister. ON DELETE dit quoi faire quand on la supprime : RESTRICT refuse, CASCADE supprime aussi les lignes liées, SET NULL vide la référence.

« Mais je vérifie déjà dans mon code PHP ! » C'est utile, pour afficher un message clair à l'utilisateur. Mais seule la base voit toutes les écritures : un script d'import, une commande console, une autre application, ou un collègue en SQL direct. Et deux requêtes simultanées peuvent passer la vérification PHP ensemble : les deux constatent que l'e-mail est libre, puis les deux l'enregistrent. Seule une contrainte UNIQUE empêche ce doublon à coup sûr. Garde les deux : la validation PHP pour l'utilisateur, la contrainte pour la garantie.

6.Les jointures : relier les tables

Une fois les données découpées en tables, il faut pouvoir les recomposer. C'est le rôle de la jointure (JOIN) : associer chaque ligne d'une table aux lignes correspondantes d'une autre, selon une condition, en général l'égalité entre une clé étrangère et un identifiant.

-- @requires: schema.sql

-- INNER JOIN : chaque commande, avec le nom de son client
SELECT o.id AS order_id, c.name, o.created_at
FROM customer_order o
JOIN customer c ON c.id = o.customer_id
ORDER BY o.id;

-- LEFT JOIN : TOUS les clients, même ceux qui n'ont jamais commandé
SELECT c.name, o.id AS order_id
FROM customer c
LEFT JOIN customer_order o ON o.customer_id = c.id
ORDER BY c.name, o.id;

-- Les clients qui n'ont jamais commandé
SELECT c.name
FROM customer c
LEFT JOIN customer_order o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Plusieurs-à-plusieurs : le détail des commandes, en passant par la table de liaison
SELECT o.id AS order_id, c.name AS customer, p.name AS product,
       i.quantity, i.unit_price_in_cents
FROM customer_order o
JOIN customer c ON c.id = o.customer_id
JOIN order_item i ON i.order_id = o.id
JOIN product p ON p.id = i.product_id
ORDER BY o.id, p.name;

▸ INNER JOIN : chaque commande, avec le nom de son client
order_id | name  | created_at
---------+-------+--------------------
1        | Alice | 2026-10-02 10:15:00
2        | Alice | 2026-11-01 18:40:00
3        | Bob   | 2026-10-20 08:05:00
(3 lignes)

▸ LEFT JOIN : TOUS les clients, même ceux qui n'ont jamais commandé
name  | order_id
------+---------
Alice | 1
Alice | 2
Bob   | 3
Chloé | NULL
(4 lignes)

▸ Les clients qui n'ont jamais commandé
name
-----
Chloé
(1 ligne)

▸ Plusieurs-à-plusieurs : le détail des commandes, en passant par la table de liaison
order_id | customer | product | quantity | unit_price_in_cents
---------+----------+---------+----------+--------------------
1        | Alice    | Clavier | 1        | 4990
1        | Alice    | Souris  | 2        | 1990
2        | Alice    | Écran   | 1        | 19900
3        | Bob      | Clavier | 2        | 4790
(4 lignes)

1. JOIN (ou INNER JOIN) ne garde que les lignes qui ont une correspondance des deux côtés. Chloé, qui n'a jamais commandé, n'apparaît pas dans la première requête.
2. LEFT JOIN garde toutes les lignes de la table de gauche, et remplit avec NULL quand il n'y a pas de correspondance. C'est ce qui permet de voir Chloé.
3. Chercher ce qui manque : un LEFT JOIN suivi de WHERE … IS NULL trouve les lignes sans correspondance (les clients sans commande).
4. Plusieurs-à-plusieurs : on traverse la table de liaison, une jointure après l'autre.

Les lettres o, c, i, p sont des alias : des noms courts donnés aux tables dans la requête. Indique toujours à quelle table appartient une colonne (c.name), même quand ce n'est pas obligatoire : la requête reste juste le jour où une autre table aura aussi une colonne name.

7.Regrouper et compter : GROUP BY et HAVING

Les fonctions d'agrégation (count, sum, avg, min, max) résument plusieurs lignes en une seule valeur. Avec GROUP BY, on obtient un résumé par groupe : un total par commande, un chiffre d'affaires par client.

-- @requires: schema.sql

-- Le total de chaque commande
SELECT order_id, sum(quantity * unit_price_in_cents) AS total_in_cents
FROM order_item
GROUP BY order_id;

-- Le chiffre d'affaires et le nombre de commandes de chaque client
SELECT c.name,
       count(DISTINCT o.id) AS orders,
       sum(i.quantity * i.unit_price_in_cents) AS revenue_in_cents
FROM customer c
JOIN customer_order o ON o.customer_id = c.id
JOIN order_item i ON i.order_id = o.id
GROUP BY c.id, c.name
ORDER BY revenue_in_cents DESC;

-- Piège : sans DISTINCT, on compte les LIGNES de commande, pas les commandes
SELECT c.name, count(o.id) AS orders_wrong, count(DISTINCT o.id) AS orders_right
FROM customer c
JOIN customer_order o ON o.customer_id = c.id
JOIN order_item i ON i.order_id = o.id
GROUP BY c.id, c.name;

-- HAVING filtre APRÈS le regroupement : les produits vendus à plus de 2 exemplaires
SELECT p.name, sum(i.quantity) AS sold
FROM product p
JOIN order_item i ON i.product_id = p.id
GROUP BY p.id, p.name
HAVING sum(i.quantity) > 2;

▸ Le total de chaque commande
order_id | total_in_cents
---------+---------------
1        | 8970
2        | 19900
3        | 9580
(3 lignes)

▸ Le chiffre d'affaires et le nombre de commandes de chaque client
name  | orders | revenue_in_cents
------+--------+-----------------
Alice | 2      | 28870
Bob   | 1      | 9580
(2 lignes)

▸ Piège : sans DISTINCT, on compte les LIGNES de commande, pas les commandes
name  | orders_wrong | orders_right
------+--------------+-------------
Alice | 3            | 2
Bob   | 1            | 1
(2 lignes)

▸ HAVING filtre APRÈS le regroupement : les produits vendus à plus de 2 exemplaires
name    | sold
--------+-----
Clavier | 3
(1 ligne)

1. Chaque colonne du SELECT doit être soit dans le GROUP BY, soit dans une fonction d'agrégation. PostgreSQL refuse le reste ; SQLite et certaines configurations de MySQL l'acceptent et renvoient une valeur au hasard parmi le groupe.
2. WHERE filtre les lignes avant le regroupement, HAVING filtre les groupes après. « Les commandes de 2026 » est un WHERE, « les produits vendus à plus de deux exemplaires » est un HAVING.
3. Le piège des jointures : une jointure multiplie les lignes. Alice a deux commandes, mais trois lignes de commande. Sans DISTINCT, count(o.id) compte 3. C'est l'une des erreurs les plus fréquentes dans les rapports, et elle ne déclenche aucune erreur.

8.Les pièges de NULL

NULL ne veut pas dire « vide » ni « zéro ». Il veut dire « inconnu ». Et une comparaison avec une valeur inconnue donne un résultat inconnu : ni vrai, ni faux. Cette logique à trois valeurs produit des bugs silencieux :

-- @requires: schema.sql

-- NULL veut dire « inconnu » : comparer avec NULL ne donne ni vrai ni faux, mais NULL
SELECT NULL = NULL AS equals, NULL <> 1 AS differs, NULL IS NULL AS is_null;

-- Les clients sans téléphone : « = NULL » ne trouve personne…
SELECT name FROM customer WHERE phone = NULL;

-- … il faut écrire « IS NULL »
SELECT name FROM customer WHERE phone IS NULL;

-- count(*) compte les lignes, count(colonne) ignore les NULL
SELECT count(*) AS orders, count(shipped_at) AS shipped FROM customer_order;

-- Une liste de produits en promotion… qui contient un NULL par erreur
CREATE TABLE promotion (product_id INTEGER);
INSERT INTO promotion VALUES (1), (NULL);

-- Les produits HORS promotion avec NOT IN : un seul NULL, et plus rien ne sort
SELECT name FROM product WHERE id NOT IN (SELECT product_id FROM promotion);

-- La même question avec NOT EXISTS : le bon résultat
SELECT name FROM product p
WHERE NOT EXISTS (SELECT 1 FROM promotion pr WHERE pr.product_id = p.id);

-- COALESCE remplace NULL par une valeur de repli
SELECT name, coalesce(phone, 'non renseigné') AS phone FROM customer;

▸ NULL veut dire « inconnu » : comparer avec NULL ne donne ni vrai ni faux, mais NULL
equals | differs | is_null
-------+---------+--------
NULL   | NULL    | 1
(1 ligne)

▸ Les clients sans téléphone : « = NULL » ne trouve personne…
name
----
(0 ligne)

▸ … il faut écrire « IS NULL »
name
-----
Bob
Chloé
(2 lignes)

▸ count(*) compte les lignes, count(colonne) ignore les NULL
orders | shipped
-------+--------
3      | 2
(1 ligne)

▸ Les produits HORS promotion avec NOT IN : un seul NULL, et plus rien ne sort
name
----
(0 ligne)

▸ La même question avec NOT EXISTS : le bon résultat
name
------
Souris
Écran
Câble
(3 lignes)

▸ COALESCE remplace NULL par une valeur de repli
name  | phone
------+--------------
Alice | 0600000001
Bob   | non renseigné
Chloé | non renseigné
(3 lignes)

1. = NULL ne trouve jamais rien. Pour tester l'absence de valeur, écris IS NULL ou IS NOT NULL.
2. count(*) compte les lignes, count(colonne) ignore les NULL. Ici, c'est utile : count(shipped_at) donne directement le nombre de commandes expédiées. Mais sum et avg ignorent aussi les NULL : une moyenne peut ne porter que sur une partie des lignes.
3. NOT IN sur une liste qui contient un NULL ne renvoie rien. Pour chaque produit, la base se demande « est-il différent de 1 et de l'inconnu ? », et la réponse est toujours « inconnu ». Préfère NOT EXISTS, qui n'a pas ce problème.
4. coalesce remplace NULL par une valeur de repli, pour l'affichage ou les calculs.

9.Sous-requêtes, CTE et fonctions de fenêtrage

Certaines questions se posent en plusieurs étapes : « le panier moyen de chaque client » demande d'abord le total de chaque commande, puis la moyenne par client. Trois outils aident à les écrire :

-- @requires: schema.sql

-- Une sous-requête : les produits qui n'ont jamais été vendus
SELECT name
FROM product p
WHERE NOT EXISTS (SELECT 1 FROM order_item i WHERE i.product_id = p.id);

-- Une CTE (WITH) donne un nom à une étape intermédiaire : la requête se lit de haut en bas
WITH order_total AS (
    SELECT order_id, sum(quantity * unit_price_in_cents) AS total_in_cents
    FROM order_item
    GROUP BY order_id
)
SELECT c.name, avg(t.total_in_cents) AS average_basket_in_cents
FROM order_total t
JOIN customer_order o ON o.id = t.order_id
JOIN customer c ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Une fonction de fenêtrage : la dernière commande de chaque client
WITH ranked AS (
    SELECT customer_id, id, created_at,
           row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS position
    FROM customer_order
)
SELECT c.name, r.id AS last_order_id, r.created_at
FROM ranked r
JOIN customer c ON c.id = r.customer_id
WHERE r.position = 1;

▸ Une sous-requête : les produits qui n'ont jamais été vendus
name
-----
Câble
(1 ligne)

▸ Une CTE (WITH) donne un nom à une étape intermédiaire : la requête se lit de haut en bas
name  | average_basket_in_cents
------+------------------------
Alice | 14435
Bob   | 9580
(2 lignes)

▸ Une fonction de fenêtrage : la dernière commande de chaque client
name  | last_order_id | created_at
------+---------------+--------------------
Alice | 2             | 2026-11-01 18:40:00
Bob   | 3             | 2026-10-20 08:05:00
(2 lignes)

1. Une sous-requête est une requête dans une autre. EXISTS et NOT EXISTS demandent simplement « y a-t-il au moins une ligne qui… ? ».
2. Une CTE (Common Table Expression, introduite par WITH) donne un nom à une étape intermédiaire. La requête se lit alors de haut en bas, comme une suite d'étapes, au lieu de s'imbriquer.
3. Une fonction de fenêtrage (OVER) calcule une valeur sur un groupe de lignes, sans les fusionner comme le ferait GROUP BY. row_number() numérote les commandes de chaque client de la plus récente à la plus ancienne : la numéro 1 est la dernière.

10.Les index : retrouver vite

Pour trouver toutes les commandes d'un client, sans aide, la base lit la table entière, ligne par ligne. Un index est comme l'index à la fin d'un livre : une liste triée qui mène directement aux bonnes pages. Il utilise en général un B-tree (arbre équilibré), qui applique l'idée de la recherche dichotomique de la fiche 1. Les exemples suivants utilisent une table de 100 000 commandes :

-- 100 000 commandes réparties sur 5 000 clients, générées par une CTE récursive
CREATE TABLE big_order (
    id             INTEGER PRIMARY KEY,
    customer_id    INTEGER NOT NULL,
    total_in_cents INTEGER NOT NULL
);

INSERT INTO big_order (customer_id, total_in_cents)
WITH RECURSIVE n(i) AS (
    SELECT 1
    UNION ALL
    SELECT i + 1 FROM n WHERE i < 100000
)
SELECT i % 5000, (i * 37) % 20000 FROM n;

-- @requires: big_orders.sql

-- Sans index : la base lit toute la table (SCAN)
EXPLAIN QUERY PLAN
SELECT count(*), sum(total_in_cents) FROM big_order WHERE customer_id = 1234;

CREATE INDEX idx_big_order_customer ON big_order (customer_id);

-- Avec index : la base va directement aux bonnes lignes (SEARCH … USING INDEX)
EXPLAIN QUERY PLAN
SELECT count(*), sum(total_in_cents) FROM big_order WHERE customer_id = 1234;

-- Le résultat est le même, seul le chemin change
SELECT count(*) AS orders, sum(total_in_cents) AS total_in_cents
FROM big_order
WHERE customer_id = 1234;

▸ Sans index : la base lit toute la table (SCAN)
plan
--------------
SCAN big_order
(1 ligne)

▸ Avec index : la base va directement aux bonnes lignes (SEARCH … USING INDEX)
plan
-------------------------------------------------------------------
SEARCH big_order USING INDEX idx_big_order_customer (customer_id=?)
(1 ligne)

▸ Le résultat est le même, seul le chemin change
orders | total_in_cents
-------+---------------
20     | 163160
(1 ligne)

EXPLAIN QUERY PLAN (SQLite) ou EXPLAIN (PostgreSQL, MySQL) montre comment la base compte exécuter une requête, sans deviner. SCAN signifie que toute la table est lue, SEARCH … USING INDEX que l'index est utilisé. Pour mesurer la différence, un petit script PHP (exemples/07/index.php) exécute 200 fois la même requête, sans puis avec l'index :

200 exécutions sans index : 454.9 ms
200 exécutions avec index : 1.5 ms

Sous PostgreSQL, EXPLAIN ANALYZE exécute la requête et donne aussi les temps réels :

-- EXPLAIN montre le plan prévu ; EXPLAIN ANALYZE exécute aussi la requête et mesure.
-- Attention : avec ANALYZE, un UPDATE ou un DELETE est réellement exécuté.
EXPLAIN ANALYZE
SELECT count(*), sum(total_in_cents)
FROM customer_order
WHERE customer_id = 1234;

-- À chercher dans le résultat :
--   « Seq Scan »    : toute la table est lue, ligne par ligne
--   « Index Scan »  : l'index mène directement aux bonnes lignes
--   « actual time » : le temps réellement passé

1. Indexe ce qui sert à chercher : les colonnes utilisées dans WHERE, JOIN et ORDER BY des requêtes fréquentes.
2. Indexe les clés étrangères. PostgreSQL ne le fait pas tout seul (MySQL, si).
3. L'ordre compte dans un index composite : un index sur (customer_id, created_at) sert à chercher par client, ou par client et date, mais pas par date seule.

-- Le même schéma, écrit pour PostgreSQL
CREATE TABLE customer (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      TEXT        NOT NULL UNIQUE,
    name       TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()   -- date AVEC fuseau horaire
);

CREATE TABLE customer_order (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT      NOT NULL REFERENCES customer (id) ON DELETE RESTRICT,
    status      TEXT        NOT NULL CHECK (status IN ('pending', 'paid', 'shipped')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- PostgreSQL ne crée PAS d'index sur une clé étrangère : à faire soi-même
CREATE INDEX idx_order_customer ON customer_order (customer_id);

-- Index composite : sert aux filtres sur customer_id seul, ou sur customer_id ET created_at
CREATE INDEX idx_order_customer_date ON customer_order (customer_id, created_at);

11.Paginer sans ralentir

Pour afficher 20 commandes par page, le réflexe est LIMIT 20 OFFSET 19980 pour la page 1 000. Mais pour sauter 19 980 lignes, la base doit d'abord les parcourir. Plus la page est loin, plus elle est lente. La pagination par curseur (keyset pagination) repart du dernier identifiant affiché :

-- @requires: big_orders.sql

-- Page 1 000 avec OFFSET : la base parcourt puis jette les 19 980 premières lignes
SELECT id, total_in_cents FROM big_order ORDER BY id LIMIT 3 OFFSET 19980;

-- La même page « par curseur » : on repart du dernier id de la page précédente
SELECT id, total_in_cents FROM big_order WHERE id > 19980 ORDER BY id LIMIT 3;

-- Le plan avec OFFSET : un parcours depuis le début de la table
EXPLAIN QUERY PLAN
SELECT id, total_in_cents FROM big_order ORDER BY id LIMIT 3 OFFSET 19980;

-- Le plan par curseur : un saut direct au bon endroit
EXPLAIN QUERY PLAN
SELECT id, total_in_cents FROM big_order WHERE id > 19980 ORDER BY id LIMIT 3;

▸ Page 1 000 avec OFFSET : la base parcourt puis jette les 19 980 premières lignes
id    | total_in_cents
------+---------------
19981 | 19297
19982 | 19334
19983 | 19371
(3 lignes)

▸ La même page « par curseur » : on repart du dernier id de la page précédente
id    | total_in_cents
------+---------------
19981 | 19297
19982 | 19334
19983 | 19371
(3 lignes)

▸ Le plan avec OFFSET : un parcours depuis le début de la table
plan
--------------
SCAN big_order
(1 ligne)

▸ Le plan par curseur : un saut direct au bon endroit
plan
----------------------------------------------------
SEARCH big_order USING INTEGER PRIMARY KEY (rowid>?)
(1 ligne)

Les deux requêtes renvoient la même page, mais la seconde va directement au bon endroit grâce à l'index de la clé primaire, quelle que soit la page.

12.Les transactions : tout ou rien

Passer une commande, c'est plusieurs écritures : créer la commande, ses lignes, décrémenter le stock. Que se passe-t-il si la troisième échoue ? Ici, c'est le code PHP qui pilote la transaction, comme dans une vraie application :

<?php

declare(strict_types=1);

require __DIR__ . '/db.php';

// Passer une commande : créer la commande, ses lignes, et décrémenter le stock
function placeOrder(PDO $db, int $customerId, array $quantities): int
{
    $db->prepare(
        "INSERT INTO customer_order (customer_id, created_at) VALUES (?, datetime('now'))",
    )->execute([$customerId]);
    $orderId = (int) $db->lastInsertId();
    foreach ($quantities as $productId => $quantity) {
        $db->prepare(
            'INSERT INTO order_item SELECT ?, id, ?, price_in_cents FROM product WHERE id = ?',
        )->execute([$orderId, $quantity, $productId]);
        // Si le stock devient négatif, la contrainte CHECK (stock >= 0) lève une erreur
        $db->prepare('UPDATE product SET stock = stock - ? WHERE id = ?')
            ->execute([$quantity, $productId]);
    }

    return $orderId;
}

function state(PDO $db): string
{
    $orders = $db->query('SELECT COUNT(*) FROM customer_order')->fetchColumn();
    $stocks = $db
        ->query("SELECT group_concat(name || '=' || stock, ', ') FROM product")
        ->fetchColumn();

    return "$orders commande(s) ; stocks : $stocks";
}

// 3 claviers (stock 10) et 5 souris (stock 2 seulement)
$order = [1 => 3, 2 => 5];

echo "SANS transaction\n";
$db = createDatabase();
try {
    placeOrder($db, 2, $order);
} catch (PDOException $error) {
    echo '  erreur : ', $error->errorInfo[2], "\n";
}
echo '  après : ', state($db), "\n";

echo "AVEC transaction\n";
$db = createDatabase();
$db->beginTransaction();
try {
    placeOrder($db, 2, $order);
    $db->commit();
} catch (PDOException $error) {
    $db->rollBack();   // tout ce qui a été fait depuis beginTransaction() est annulé
    echo '  erreur : ', $error->errorInfo[2], "\n";
}
echo '  après : ', state($db), "\n";

SANS transaction
  erreur : CHECK constraint failed: stock >= 0
  après : 4 commande(s) ; stocks : Clavier=7, Souris=2, Écran=5, Câble=50
AVEC transaction
  erreur : CHECK constraint failed: stock >= 0
  après : 3 commande(s) ; stocks : Clavier=10, Souris=2, Écran=5, Câble=50

Sans transaction, la base reste dans un état incohérent : une commande fantôme existe, et trois claviers ont disparu du stock. Avec une transaction, les écritures forment un seul bloc : soit tout est validé (COMMIT), soit tout est annulé (ROLLBACK).

On résume les garanties d'une transaction par ACID :

• Atomicité : tout ou rien, comme ci-dessus.
• Cohérence : la base passe d'un état valide à un autre, contraintes respectées.
• Isolation : les transactions simultanées ne se marchent pas dessus (dans une certaine mesure, voir ci-dessous).
• Durabilité : une fois validé, c'est enregistré, même si le serveur s'arrête juste après.

L'isolation n'est pas totale par défaut (PostgreSQL utilise le niveau Read Committed). Deux clients qui achètent la dernière souris au même moment peuvent tous les deux lire « stock = 1 ». Pour l'éviter, on verrouille la ligne, ou mieux, on fait la vérification et la modification en une seule requête :

-- Deux clients achètent la dernière souris au même moment.
-- Sans verrou, les deux lisent « stock = 1 », et les deux commandes passent.

BEGIN;
-- FOR UPDATE verrouille la ligne : la seconde transaction attend la fin de la première
SELECT stock FROM product WHERE id = 2 FOR UPDATE;
-- … vérification du stock côté application …
UPDATE product SET stock = stock - 1 WHERE id = 2;
COMMIT;

-- Plus simple encore, quand c'est possible : une seule requête atomique
UPDATE product SET stock = stock - 1 WHERE id = 2 AND stock >= 1;
-- puis vérifier le nombre de lignes modifiées (0 = plus de stock)

13.Requêtes préparées et injection SQL

Construire une requête en collant une saisie de l'utilisateur dans une chaîne PHP est la faille la plus classique du web. Voici ce que donne un champ de recherche rempli par un attaquant :

<?php

declare(strict_types=1);

require __DIR__ . '/db.php';

$db = createDatabase();

// Ce qu'un attaquant tape dans le champ « e-mail » d'un formulaire de recherche
$input = "' OR '1'='1";

// ✗ La saisie est collée dans la requête : elle devient du SQL
$sql = "SELECT email FROM customer WHERE email = '$input'";
echo "Requête construite : $sql\n";
echo 'Résultats : ', implode(', ', $db->query($sql)->fetchAll(PDO::FETCH_COLUMN)), "\n\n";

// ✓ Requête préparée : la saisie est envoyée à part, comme une simple valeur
$statement = $db->prepare('SELECT email FROM customer WHERE email = :email');
$statement->execute(['email' => $input]);
echo 'Requête préparée, résultats : ', count($statement->fetchAll()), "\n";

Requête construite : SELECT email FROM customer WHERE email = '' OR '1'='1'
Résultats : alice@example.com, bob@example.com, chloe@example.com

Requête préparée, résultats : 0

L'apostrophe tapée par l'attaquant ferme la chaîne SQL, et la suite (OR '1'='1') devient une condition toujours vraie : toute la table sort. Avec une requête un peu différente, il pourrait lire la table des mots de passe, ou en supprimer une.

Pièges fréquents

Checklist

Avant de valider un schéma

• Chaque information n'est enregistrée qu'à un seul endroit, sauf choix de dénormalisation assumé.
• Aucune colonne ne contient de liste ni de valeurs séparées par des virgules.
• Chaque colonne a le type qui correspond à son sens : centimes ou NUMERIC pour l'argent, TIMESTAMPTZ pour un instant.
• Les colonnes obligatoires sont NOT NULL, les valeurs uniques sont UNIQUE, les règles simples sont des CHECK.
• Chaque clé étrangère a une contrainte, un ON DELETE réfléchi, et un index.

Avant de valider une requête

• J'ai choisi entre INNER JOIN et LEFT JOIN en pensant aux lignes sans correspondance.
• Mes comptages après jointure utilisent DISTINCT quand il le faut.
• Chaque colonne qui peut être NULL est traitée avec IS NULL ou coalesce.
• La requête a été vérifiée avec EXPLAIN sur un volume réaliste.
• Les écritures liées sont dans une transaction courte.
• Aucune valeur variable n'est collée dans la requête.

Exercice

Les deux parties s'exécutent avec le petit outil utilisé pour cette fiche, qui affiche les résultats et les erreurs de chaque requête :

docker compose run --rm php php outils/run-sql.php exemples/07/exercice.sql
docker compose run --rm php php outils/run-sql.php exemples/07/exercice_requetes.sql

Partie 1 : modéliser des ateliers

Une association gère ses ateliers dans un tableur, recopié en tête du fichier exemples/07/exercice.sql.

1. Repère les anomalies du tableur : qu'est-ce qui est recopié ? Que se passe-t-il si l'atelier Git change de date ?
2. Écris le schéma normalisé : ateliers, participants, inscriptions. Quelle table porte la relation plusieurs-à-plusieurs ? Quels types choisis-tu pour la date et le nombre de places ?
3. Écris les INSERT qui reproduisent le tableur : ils doivent passer sans erreur.
4. Écris les INSERT invalides prévus dans le fichier, et ajoute les contraintes pour que chacun affiche une ERREUR.
5. Termine par la requête qui liste les inscrits de chaque atelier. Quel index la rendrait rapide ? Vérifie avec EXPLAIN QUERY PLAN.
6. Bonus : l'atelier Git a 2 places. Comment empêcher une troisième inscription, même si deux personnes s'inscrivent au même moment ? Une contrainte suffit-elle, ou faut-il une transaction ?

Partie 2 : interroger la boutique

Le fichier exemples/07/exercice_requetes.sql charge le schéma de la fiche et pose cinq questions. Écris une requête sous chacune. Avant de lancer, devine le résultat à partir des données de la section 2 : c'est le meilleur moyen de repérer un count faussé par une jointure ou un client oublié par un INNER JOIN.

Conseils

1. Dessine le schéma avant de coder. Des rectangles pour les tables, des flèches pour les clés étrangères. Les erreurs de modélisation se voient mieux sur papier, et coûtent bien plus cher à corriger une fois les données en production.
2. Écris du SQL à la main de temps en temps. Ouvre un client (psql, ou l'outil base de données de ton éditeur) et explore tes vraies données. C'est le meilleur moyen de comprendre ce que Doctrine génère.
3. Construis une requête pas à pas. Commence par le FROM et les jointures, regarde les lignes obtenues, puis ajoute le WHERE, puis le GROUP BY. À chaque étape, vérifie que le nombre de lignes est celui que tu attends.
4. Teste avec un volume réaliste. Une requête rapide sur 50 lignes peut être catastrophique sur un million.
5. Lis la doc de PostgreSQL. Elle est l'une des meilleures documentations techniques qui existent, claire et pleine d'exemples.

Documentation officielle

• Les types de données et le type JSONB (PostgreSQL)
• Les contraintes (PostgreSQL)
• Les jointures et le regroupement (PostgreSQL)
• Les comparaisons et NULL (PostgreSQL)
• Les CTE et les fonctions de fenêtrage (PostgreSQL)
• Les index et comprendre EXPLAIN (PostgreSQL)
• Les niveaux d'isolation et les verrous explicites (PostgreSQL)
• Les requêtes préparées et les transactions avec PDO
• Les tables STRICT de SQLite
• Le composant Uid de Symfony (UUID)