⚡ Sous-requêtes

Une sous-requête est un SELECT imbriqué dans une autre requête. Elle peut apparaître dans le WHERE, le FROM, ou le SELECT.

Dans WHERE (scalaire)

-- Produits au-dessus de la moyenne
SELECT nom, prix
FROM produits
WHERE prix > (SELECT AVG(prix) FROM produits);

-- Clients ayant passé une commande livrée
SELECT nom FROM clients
WHERE id IN (SELECT DISTINCT client_id FROM commandes WHERE statut = 'livree');

Sous-requête corrélée

-- Produits au-dessus de la moyenne de LEUR catégorie
SELECT p.nom, p.prix,
       (SELECT AVG(prix) FROM produits WHERE categorie_id = p.categorie_id) AS moy_cat
FROM produits p
WHERE p.prix > (SELECT AVG(prix) FROM produits WHERE categorie_id = p.categorie_id);

Dans FROM (table dérivée)

-- Clients avec plus de 500€ de commandes
SELECT cl.nom, stats.total
FROM clients cl
JOIN (
  SELECT client_id, SUM(total) AS total
  FROM commandes
  GROUP BY client_id
  HAVING total > 500
) stats ON cl.id = stats.client_id;

EXISTS

-- Plus efficace que IN sur de grandes tables
SELECT nom FROM clients cl
WHERE EXISTS (
  SELECT 1 FROM commandes
  WHERE client_id = cl.id AND statut = 'livree'
);

📋 CTE — Common Table Expressions

Les CTE (WITH ... AS) définissent des sous-requêtes nommées pour améliorer la lisibilité.

-- CTE simple
WITH clients_actifs AS (
  SELECT client_id, SUM(total) AS ca
  FROM commandes WHERE statut != 'annulee'
  GROUP BY client_id
)
SELECT cl.nom, ca.ca
FROM clients cl
JOIN clients_actifs ca ON cl.id = ca.client_id
ORDER BY ca.ca DESC;

-- Plusieurs CTE chaînées
WITH
  stats_par_categorie AS (
    SELECT categorie_id, AVG(prix) AS moy_cat
    FROM produits GROUP BY categorie_id
  ),
  produits_premium AS (
    SELECT p.id, p.nom, p.prix, s.moy_cat
    FROM produits p
    JOIN stats_par_categorie s ON p.categorie_id = s.categorie_id
    WHERE p.prix > s.moy_cat * 1.5
  )
SELECT * FROM produits_premium ORDER BY prix DESC;

👁️ Vues (VIEWS)

Une vue est une requête stockée sous un nom. Elle est ré-exécutée à chaque appel.

-- Créer une vue
CREATE OR REPLACE VIEW vue_produits_complet AS
  SELECT p.id, p.nom, p.prix, p.stock, c.nom AS categorie
  FROM produits p
  LEFT JOIN categories c ON p.categorie_id = c.id;

-- Utiliser la vue comme une table normale
SELECT * FROM vue_produits_complet WHERE categorie = 'Electronique';
SELECT * FROM vue_produits_complet WHERE stock < 20 ORDER BY stock;

-- Supprimer une vue
DROP VIEW IF EXISTS vue_produits_complet;

-- Voir la définition d'une vue
SHOW CREATE VIEW vue_produits_complet;

🔒 Transactions ACID

Une transaction groupe plusieurs opérations : soit toutes réussissent (COMMIT), soit toutes sont annulées (ROLLBACK).

START TRANSACTION;  -- ou BEGIN;

  -- Opérations...
  INSERT INTO commandes (client_id, total) VALUES (1, 150.00);
  SET @id = LAST_INSERT_ID();
  INSERT INTO commande_produits VALUES (@id, 3, 2, 29.99);
  UPDATE produits SET stock = stock - 2 WHERE id = 3;

COMMIT;   -- Valide tout
-- ou
ROLLBACK; -- Annule tout

Niveaux d'isolation

-- Voir le niveau actuel
SELECT @@transaction_isolation;

-- Changer pour la session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Niveaux (du moins au plus isolé) :
-- READ UNCOMMITTED  : voit les changements non-committed (dirty reads)
-- READ COMMITTED    : voit seulement les committed
-- REPEATABLE READ   : défaut InnoDB, même résultat si la requête est relancée
-- SERIALIZABLE      : transactions totalement séquentielles

Savepoints

START TRANSACTION;
  INSERT INTO logs VALUES ('debut');
  SAVEPOINT sp1;

  INSERT INTO produits (nom, prix) VALUES ('Test', -1);  -- sera annulé
  ROLLBACK TO sp1;  -- annule seulement jusqu'au savepoint

  INSERT INTO logs VALUES ('fin');
COMMIT;  -- le premier et le dernier INSERT sont committed

⚙️ Procédures stockées

DELIMITER $$

CREATE PROCEDURE passer_commande(
  IN p_client_id INT,
  IN p_produit_id INT,
  IN p_quantite INT
)
BEGIN
  DECLARE v_prix DECIMAL(10,2);
  DECLARE v_stock INT;
  DECLARE v_cmd_id INT;

  -- Vérifier le stock
  SELECT prix, stock INTO v_prix, v_stock
  FROM produits WHERE id = p_produit_id FOR UPDATE;

  IF v_stock < p_quantite THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock insuffisant';
  END IF;

  START TRANSACTION;
    INSERT INTO commandes (client_id, total)
    VALUES (p_client_id, v_prix * p_quantite);
    SET v_cmd_id = LAST_INSERT_ID();

    INSERT INTO commande_produits (commande_id, produit_id, quantite, prix_unitaire)
    VALUES (v_cmd_id, p_produit_id, p_quantite, v_prix);

    UPDATE produits SET stock = stock - p_quantite WHERE id = p_produit_id;
  COMMIT;

  SELECT v_cmd_id AS commande_id;
END$$

DELIMITER ;

-- Appel
CALL passer_commande(1, 3, 2);

⚡ Triggers

-- Mettre à jour un compteur à chaque INSERT
DELIMITER $$
CREATE TRIGGER after_commande_insert
AFTER INSERT ON commandes
FOR EACH ROW
BEGIN
  UPDATE clients
  SET nb_commandes = nb_commandes + 1
  WHERE id = NEW.client_id;
END$$
DELIMITER ;

-- NEW = valeurs de la ligne insérée
-- OLD = valeurs avant modification/suppression (UPDATE/DELETE)