SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Master Sécurité des Systèmes d'Information

Année académique 2025–2026

SQL AVANCÉ
Maîtrisez le langage des bases de données relationnelles

Cours complet — Du rappel fondamental aux concepts avancés

Procédures stockées • Triggers • Fonctions • Vues • Transactions • Index • Optimisation

Formateur

Youssoupha LAM
Consultant en Système d'Information

Page 1 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Table des matières

Table des matières ........................................................................................................................2
Chapitre 1 — Rappel des fondamentaux SQL ..............................................................................5
1.1 Introduction et historique ......................................................................................................5
1.2 Les sous-langages SQL .......................................................................................................5
1.3 DDL — Définition des structures ..........................................................................................5
CREATE TABLE .....................................................................................................................5
ALTER TABLE ........................................................................................................................6
Contraintes d'intégrité .............................................................................................................6
1.4 DML — Manipulation des données ......................................................................................6
INSERT ..................................................................................................................................6
UPDATE .................................................................................................................................6
DELETE ..................................................................................................................................7
1.5 SELECT — Les bases de la requête ...................................................................................7
Opérateurs et filtres WHERE ..................................................................................................7
ORDER BY, LIMIT, OFFSET .................................................................................................7
1.6 Jointures ...............................................................................................................................8
1.7 Fonctions d'agrégation et GROUP BY .................................................................................9
Chapitre 2 — Requêtes avancées ...............................................................................................10
2.1 Fonctions de fenêtrage (Window Functions) ......................................................................10
Fonctions de numérotation ...................................................................................................10
Fonctions analytiques ...........................................................................................................10
Agrégats glissants (Running / Rolling) .................................................................................11
2.2 Expressions conditionnelles ...............................................................................................11
CASE WHEN ........................................................................................................................11
Fonctions COALESCE et NULLIF ........................................................................................11
2.3 Opérateurs ensemblistes ...................................................................................................12
2.4 Fonctions de chaînes, dates et mathématiques.................................................................12
Fonctions sur les chaînes .....................................................................................................12
Fonctions sur les dates .........................................................................................................12
Fonctions mathématiques ....................................................................................................13
Chapitre 3 — Sous-requêtes (Subqueries) ..................................................................................14
3.1 Concepts et types ..............................................................................................................14
3.2 Sous-requêtes scalaires .....................................................................................................14
3.3 Sous-requêtes avec IN, ANY, ALL .....................................................................................15
3.4 Sous-requêtes EXISTS / NOT EXISTS ..............................................................................15
3.5 Sous-requêtes corrélées ....................................................................................................16
3.6 Tables dérivées et CTE ......................................................................................................16

Page 2 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Table dérivée (FROM subquery) ..........................................................................................16

CTE — Common Table Expression (WITH) .........................................................................17
Chapitre 4 — Vues (Views) .........................................................................................................18
4.1 Qu'est-ce qu'une vue ? ......................................................................................................18
4.2 Création et manipulation ....................................................................................................18
4.3 Vues modifiables ................................................................................................................19
4.4 Vues matérialisées (PostgreSQL, Oracle) .........................................................................19
Chapitre 5 — Procédures stockées et fonctions..........................................................................20
5.1 Procédures stockées ..........................................................................................................20
Gestion des erreurs avec DECLARE HANDLER .................................................................21
5.2 Fonctions stockées ............................................................................................................21
Différences Procédure vs Fonction ......................................................................................22
5.3 Structures de contrôle ........................................................................................................22
Chapitre 6 — Triggers (Déclencheurs) ........................................................................................23
6.1 Concept et utilisation ..........................................................................................................23
6.2 Création de triggers ............................................................................................................23
Trigger de journalisation (audit) ............................................................................................23
Trigger BEFORE pour validation ..........................................................................................24
Trigger pour table de stock ...................................................................................................24
Gestion des triggers .............................................................................................................24
Chapitre 7 — Transactions ..........................................................................................................25
7.1 Les propriétés ACID ...........................................................................................................25
7.2 Syntaxe et usage ...............................................................................................................25
7.3 SAVEPOINT — Points de sauvegarde ..............................................................................25
7.4 Niveaux d'isolation .............................................................................................................26
Phénomènes d'anomalies ....................................................................................................26
Chapitre 8 — Index et optimisation des performances ................................................................27
8.1 Qu'est-ce qu'un index ? ......................................................................................................27
8.2 Types d'index .....................................................................................................................27
8.3 Création et gestion des index .............................................................................................27
8.4 Analyse avec EXPLAIN ......................................................................................................28
Colonnes importantes de EXPLAIN .....................................................................................28
8.5 Bonnes pratiques d'indexation ...........................................................................................28
Index couvrant (Covering Index) ..........................................................................................28
Chapitre 9 — Sécurité et gestion des accès (DCL) .....................................................................29
9.1 Gestion des utilisateurs ......................................................................................................29
9.2 GRANT — Attribution des droits ........................................................................................29
9.3 REVOKE — Révocation des droits ....................................................................................29
9.4 Principe du moindre privilège .............................................................................................30

Page 3 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 10 — Partitionnement ...................................................................................................31

10.1 Concept ............................................................................................................................31
10.2 Types de partitionnement .................................................................................................31
RANGE — par plage de valeurs ...........................................................................................31
LIST — par liste de valeurs ..................................................................................................31
HASH — distribution uniforme ..............................................................................................31
Gestion des partitions ...........................................................................................................31
Chapitre 11 — Normalisation .......................................................................................................32
11.1 Pourquoi normaliser ? ......................................................................................................32
11.2 Les formes normales ........................................................................................................32
Exemple de normalisation ....................................................................................................32
Chapitre 12 — Optimisation des requêtes ...................................................................................33
12.1 Règles générales .............................................................................................................33
12.2 Pièges courants ...............................................................................................................33
Fonctions sur colonnes indexées .........................................................................................33
LIKE avec wildcard au début ................................................................................................33
Pagination efficace ...............................................................................................................33
12.3 Statistiques et maintenance .............................................................................................34
Annexe — Aide-mémoire SQL ....................................................................................................35
Fonctions d'agrégation .............................................................................................................35
Fonctions de fenêtrage ............................................................................................................35
Niveaux d'isolation — rappel ....................................................................................................35
Checklist de performance ........................................................................................................36

Page 4 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 1 — Rappel des fondamentaux SQL

1.1 Introduction et historique

SQL (Structured Query Language) est le langage standard pour interagir avec les bases de
données relationnelles. Créé dans les années 1970 par IBM (System R), normalisé par
ANSI/ISO en 1986, il reste aujourd'hui incontournable.
Les principaux SGBDR (Systèmes de Gestion de Bases de Données Relationnelles) qui
implémentent SQL :
• MySQL / MariaDB — open-source, très répandu pour le web
• PostgreSQL — open-source, riche en fonctionnalités avancées
• Oracle Database — leader entreprise
• Microsoft SQL Server — environnement Microsoft
• SQLite — embarqué, léger, sans serveur

1.2 Les sous-langages SQL

Sous-langage Signification Commandes principales
DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE
DML Data Manipulation Language SELECT, INSERT, UPDATE, DELETE
DCL Data Control Language GRANT, REVOKE
TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT
DQL Data Query Language SELECT (parfois séparé)

1.3 DDL — Définition des structures

CREATE TABLE
-- Création d'une table complète
CREATE TABLE employes (
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(100) NOT NULL,
prenom VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
salaire DECIMAL(10,2) DEFAULT 0.00,
date_embauche DATE,
departement_id INT,
CONSTRAINT fk_dept FOREIGN KEY (departement_id)
REFERENCES departements(id)
ON DELETE SET NULL
ON UPDATE CASCADE
);

Page 5 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

ALTER TABLE
-- Ajouter une colonne
ALTER TABLE employes ADD COLUMN telephone VARCHAR(20);

-- Modifier le type d'une colonne

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

-- Supprimer une colonne

ALTER TABLE employes DROP COLUMN telephone;

-- Renommer une table

ALTER TABLE employes RENAME TO salaries;

Contraintes d'intégrité
Contrainte Rôle Exemple
PRIMARY KEY Identifiant unique de la ligne id INT PRIMARY KEY
FOREIGN KEY Lien vers une autre table REFERENCES clients(id)
UNIQUE Valeur unique dans la colonne email VARCHAR UNIQUE
NOT NULL Valeur obligatoire nom VARCHAR NOT NULL
CHECK Validation d'une expression CHECK (age >= 18)
DEFAULT Valeur par défaut statut VARCHAR DEFAULT 'actif'

1.4 DML — Manipulation des données

INSERT
-- Insertion ligne par ligne
INSERT INTO employes (nom, prenom, email, salaire)
VALUES ('Dupont', 'Marie', 'marie@[Link]', 3500.00);

-- Insertion multiple (plus performant)

INSERT INTO employes (nom, prenom, email, salaire) VALUES
('Martin', 'Paul', 'paul@[Link]', 3200.00),
('Leblanc', 'Anne', 'anne@[Link]', 4100.00),
('Garcia', 'Pedro', 'pedro@[Link]', 2900.00);

-- INSERT depuis une autre table

INSERT INTO archives_employes SELECT * FROM employes WHERE date_depart IS
NOT NULL;

UPDATE
-- Mise à jour ciblée
UPDATE employes SET salaire = salaire * 1.05 WHERE departement_id = 2;

-- Mise à jour avec jointure (MySQL)

UPDATE employes e
JOIN departements d ON e.departement_id = [Link]
SET [Link] = [Link] * 1.10
WHERE [Link] = 'Informatique';

Page 6 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

DELETE
-- Suppression ciblée
DELETE FROM employes WHERE date_depart < '2020-01-01';

-- Supprimer toutes les lignes (DML, logué, annulable)

DELETE FROM temp_data;

-- TRUNCATE (DDL, beaucoup plus rapide)

TRUNCATE TABLE temp_data;
Attention : TRUNCATE supprime TOUTES les lignes sans possibilité de ROLLBACK sur certains
SGBDR. Toujours utiliser DELETE avec WHERE quand un filtrage est nécessaire.

1.5 SELECT — Les bases de la requête

-- Syntaxe complète d'un SELECT
SELECT [DISTINCT] colonnes
FROM table
[JOIN autre_table ON condition]
[WHERE condition]
[GROUP BY colonnes]
[HAVING condition_aggrégat]
[ORDER BY colonnes [ASC|DESC]]
[LIMIT n [OFFSET m]];

Opérateurs et filtres WHERE

-- Comparaison classique
SELECT * FROM employes WHERE salaire BETWEEN 2000 AND 5000;

-- Correspondance de chaîne
SELECT * FROM employes WHERE nom LIKE 'D%'; -- commence par D
SELECT * FROM employes WHERE email LIKE '%@[Link]';

-- Liste de valeurs
SELECT * FROM employes WHERE departement_id IN (1, 3, 5);

-- Valeurs nulles
SELECT * FROM employes WHERE telephone IS NULL;
SELECT * FROM employes WHERE telephone IS NOT NULL;

-- Combinaison logique
SELECT * FROM employes
WHERE (salaire > 3000 OR anciennete > 5) AND statut = 'actif';

ORDER BY, LIMIT, OFFSET

-- Tri multi-colonnes
SELECT nom, prenom, salaire FROM employes
ORDER BY salaire DESC, nom ASC
LIMIT 10 OFFSET 20; -- page 3 si 10 résultats par page

Page 7 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

1.6 Jointures
Les jointures permettent de combiner des données de plusieurs tables. C'est le coeur du
modèle relationnel.
Type Résultat
INNER JOIN Lignes avec correspondance dans les deux tables
LEFT JOIN Toutes les lignes gauche + correspondances droite (NULL si absent)
RIGHT JOIN Toutes les lignes droite + correspondances gauche (NULL si absent)
FULL OUTER JOIN Toutes les lignes des deux tables
CROSS JOIN Produit cartésien (toutes combinaisons)
SELF JOIN Jointure d'une table avec elle-même

-- INNER JOIN : employés avec leur département

SELECT [Link], [Link], [Link] AS departement
FROM employes e
INNER JOIN departements d ON e.departement_id = [Link];

-- LEFT JOIN : tous les employés, même sans département assigné

SELECT [Link], [Link] AS departement
FROM employes e
LEFT JOIN departements d ON e.departement_id = [Link];

-- SELF JOIN : trouver les managers et leurs équipes

SELECT [Link] AS employe, [Link] AS manager
FROM employes e
LEFT JOIN employes m ON e.manager_id = [Link];

-- Multi-jointures
SELECT [Link], [Link] AS dept, [Link] AS projet
FROM employes e
JOIN departements d ON e.departement_id = [Link]
JOIN affectations a ON [Link] = a.employe_id
JOIN projets p ON a.projet_id = [Link];

Page 8 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

1.7 Fonctions d'agrégation et GROUP BY

-- Fonctions d'agrégation de base
SELECT
COUNT(*) AS total_employes,
COUNT(telephone) AS avec_telephone, -- exclut les NULL
AVG(salaire) AS salaire_moyen,
MIN(salaire) AS salaire_min,
MAX(salaire) AS salaire_max,
SUM(salaire) AS masse_salariale
FROM employes;

-- GROUP BY : agrégation par groupe

SELECT departement_id, AVG(salaire) AS moy
FROM employes
GROUP BY departement_id;

-- HAVING : filtrer sur les agrégats (pas WHERE !)

SELECT departement_id, COUNT(*) AS nb, AVG(salaire) AS moy
FROM employes
GROUP BY departement_id
HAVING COUNT(*) >= 5 AND AVG(salaire) > 3000
ORDER BY moy DESC;
Note : WHERE filtre AVANT l'agrégation (lignes). HAVING filtre APRÈS (groupes). Ne jamais
confondre les deux.

Page 9 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 2 — Requêtes avancées

2.1 Fonctions de fenêtrage (Window Functions)

Les fonctions de fenêtrage (OVER) permettent d'effectuer des calculs sur un ensemble de
lignes liées à la ligne courante, sans réduire le nombre de lignes retournées — contrairement à
GROUP BY.
-- Syntaxe générale
fonction() OVER (
[PARTITION BY colonne]
[ORDER BY colonne [ASC|DESC]]
[ROWS BETWEEN ... AND ...]
)

Fonctions de numérotation
SELECT
nom,
salaire,
departement_id,
ROW_NUMBER() OVER (ORDER BY salaire DESC) AS
rang_global,
RANK() OVER (PARTITION BY departement_id ORDER BY salaire DESC)
AS rang_dept,
DENSE_RANK() OVER (PARTITION BY departement_id ORDER BY salaire DESC)
AS dense_rang,
NTILE(4) OVER (ORDER BY salaire DESC) AS quartile
FROM employes;
Fonction Comportement en cas d'égalité
ROW_NUMBER() Numéro unique, pas de saut — arbitraire si ex-aequo
RANK() Même rang pour les égaux, saut après (1,2,2,4)
DENSE_RANK() Même rang pour les égaux, pas de saut (1,2,2,3)
NTILE(n) Répartit les lignes en n groupes égaux

Fonctions analytiques
SELECT
nom,
salaire,
departement_id,
LAG(salaire, 1) OVER (PARTITION BY departement_id ORDER BY id) AS
sal_precedent,
LEAD(salaire, 1) OVER (PARTITION BY departement_id ORDER BY id) AS
sal_suivant,
FIRST_VALUE(salaire) OVER (PARTITION BY departement_id ORDER BY salaire
DESC) AS max_dept,
LAST_VALUE(salaire) OVER (
PARTITION BY departement_id ORDER BY salaire DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS min_dept
FROM employes;

Page 10 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Agrégats glissants (Running / Rolling)

-- Cumul des ventes et moyenne mobile sur 3 jours
SELECT
date_vente,
montant,
SUM(montant) OVER (ORDER BY date_vente
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumul,
AVG(montant) OVER (ORDER BY date_vente
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moy_3j
FROM ventes
ORDER BY date_vente;

2.2 Expressions conditionnelles

CASE WHEN
-- CASE simple (équivalent switch)
SELECT nom, statut,
CASE statut
WHEN 'actif' THEN 'En poste'
WHEN 'conge' THEN 'En congé'
WHEN 'retraite'THEN 'Retraité'
ELSE 'Inconnu'
END AS libelle_statut
FROM employes;

-- CASE recherché (plus flexible)

SELECT nom, salaire,
CASE
WHEN salaire < 2000 THEN 'Bas'
WHEN salaire < 4000 THEN 'Moyen'
WHEN salaire < 7000 THEN 'Élevé'
ELSE 'Très élevé'
END AS tranche
FROM employes;

-- CASE dans une agrégation (pivot manuel)

SELECT
SUM(CASE WHEN departement_id = 1 THEN 1 ELSE 0 END) AS nb_dept1,
SUM(CASE WHEN departement_id = 2 THEN 1 ELSE 0 END) AS nb_dept2
FROM employes;

Fonctions COALESCE et NULLIF

-- COALESCE : retourne la première valeur non nulle
SELECT nom, COALESCE(telephone, email, 'Pas de contact') AS contact
FROM employes;

-- NULLIF : retourne NULL si les deux valeurs sont égales

SELECT nom, salaire / NULLIF(heures, 0) AS taux_horaire
FROM employes; -- évite la division par zéro

Page 11 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

2.3 Opérateurs ensemblistes

-- UNION : réunit les résultats (déduplique)
SELECT id, nom FROM clients_france
UNION
SELECT id, nom FROM clients_espagne;

-- UNION ALL : réunit sans dédoublonner (plus rapide)

SELECT id, nom FROM clients_france
UNION ALL
SELECT id, nom FROM clients_espagne;

-- INTERSECT : éléments présents dans les DEUX

SELECT email FROM abonnes
INTERSECT
SELECT email FROM acheteurs;

-- EXCEPT / MINUS : éléments du premier absent du second

SELECT email FROM abonnes
EXCEPT
SELECT email FROM desabonnes;
Note : UNION déduplique (coûteux), UNION ALL ne déduplique pas (plus rapide). Préférez UNION
ALL quand les doublons ne posent pas problème.

2.4 Fonctions de chaînes, dates et mathématiques

Fonctions sur les chaînes
SELECT
UPPER(nom) AS nom_maj,
LOWER(email) AS email_min,
LENGTH(nom) AS longueur,
SUBSTRING(nom, 1, 3) AS trois_premiers,
CONCAT(prenom, ' ', nom) AS nom_complet,
TRIM(' bonjour ') AS sans_espaces,
REPLACE(nom, 'a', 'A') AS remplace,
POSITION('o' IN nom) AS pos_o
FROM employes;

Fonctions sur les dates

SELECT
NOW() AS maintenant,
CURDATE() AS aujourd_hui,
DATEDIFF(NOW(), date_embauche) AS jours_anciennete,
DATE_ADD(date_embauche, INTERVAL 1 YEAR) AS 1er_anniversaire,
YEAR(date_embauche) AS annee_embauche,
MONTH(date_embauche) AS mois_embauche,
DATE_FORMAT(date_embauche, '%d/%m/%Y') AS date_fr
FROM employes;

Page 12 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Fonctions mathématiques
SELECT
ROUND(salaire, 2) AS arrondi,
FLOOR(salaire) AS plancher,
CEIL(salaire) AS plafond,
ABS(solde) AS valeur_absolue,
MOD(id, 2) AS pair_impair,
POWER(base, exposant) AS puissance,
SQRT(valeur) AS racine_carree
FROM donnees;

Page 13 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 3 — Sous-requêtes (Subqueries)

3.1 Concepts et types

Une sous-requête est une requête SELECT imbriquée dans une autre requête. Elle peut
apparaître dans SELECT, FROM, WHERE ou HAVING.
Type Emplacement Retourne
Scalaire SELECT, WHERE, HAVING Une seule valeur (1 ligne × 1 colonne)
Colonne WHERE avec IN/ANY/ALL Une colonne, plusieurs lignes
Ligne WHERE avec comparateurs de ligne Une seule ligne, plusieurs colonnes
Table FROM Un ensemble de lignes comme une table
(dérivée) virtuelle
Corrélée WHERE (référence la requête Re-exécutée pour chaque ligne externe
externe)

3.2 Sous-requêtes scalaires

-- Dans WHERE : employés au-dessus de la moyenne
SELECT nom, salaire
FROM employes
WHERE salaire > (SELECT AVG(salaire) FROM employes);

-- Dans SELECT : comparaison avec la moyenne du département

SELECT
nom, salaire,
(SELECT AVG(salaire) FROM employes e2
WHERE e2.departement_id = e1.departement_id) AS moy_dept,
salaire - (SELECT AVG(salaire) FROM employes e2
WHERE e2.departement_id = e1.departement_id) AS ecart
FROM employes e1;

Page 14 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

3.3 Sous-requêtes avec IN, ANY, ALL

-- IN : employés dans les départements ayant plus de 10 personnes
SELECT nom FROM employes
WHERE departement_id IN (
SELECT departement_id FROM employes
GROUP BY departement_id HAVING COUNT(*) > 10
);

-- NOT IN : employés n'ayant aucun projet

SELECT nom FROM employes
WHERE id NOT IN (SELECT DISTINCT employe_id FROM affectations);

-- ANY : salaire supérieur à AU MOINS UN salaire de l'équipe RH

SELECT nom, salaire FROM employes
WHERE salaire > ANY (
SELECT salaire FROM employes e JOIN departements d ON e.departement_id
= [Link]
WHERE [Link] = 'RH'
);

-- ALL : salaire supérieur à TOUS les salaires de l'équipe RH

SELECT nom, salaire FROM employes
WHERE salaire > ALL (
SELECT salaire FROM employes e JOIN departements d ON e.departement_id
= [Link]
WHERE [Link] = 'RH'
);
Attention : NOT IN avec des valeurs NULL dans la sous-requête retourne TOUJOURS un ensemble
vide. Préférez NOT EXISTS dans ce cas.

3.4 Sous-requêtes EXISTS / NOT EXISTS

-- EXISTS : s'arrête dès qu'une ligne est trouvée (très efficace)
-- Clients ayant passé au moins une commande
SELECT nom FROM clients c
WHERE EXISTS (
SELECT 1 FROM commandes cmd
WHERE cmd.client_id = [Link]
);

-- NOT EXISTS : clients sans aucune commande

SELECT nom FROM clients c
WHERE NOT EXISTS (
SELECT 1 FROM commandes cmd
WHERE cmd.client_id = [Link]
);
Note : EXISTS est souvent plus performant que IN pour les grandes tables car il court-circuite dès la
première correspondance trouvée.

Page 15 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

3.5 Sous-requêtes corrélées

Une sous-requête corrélée référence la requête externe. Elle est ré-exécutée pour chaque ligne
de la requête externe.
-- Les 3 meilleurs salaires par département
SELECT nom, salaire, departement_id
FROM employes e1
WHERE (
SELECT COUNT(*) FROM employes e2
WHERE e2.departement_id = e1.departement_id
AND [Link] > [Link]
) < 3
ORDER BY departement_id, salaire DESC;

3.6 Tables dérivées et CTE

Table dérivée (FROM subquery)
-- Sous-requête dans FROM
SELECT dept_stats.departement_id, dept_stats.moy, [Link]
FROM (
SELECT departement_id, AVG(salaire) AS moy
FROM employes
GROUP BY departement_id
) AS dept_stats
JOIN employes e ON e.departement_id = dept_stats.departement_id
WHERE [Link] > dept_stats.moy;

Page 16 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

CTE — Common Table Expression (WITH)

Les CTE améliorent la lisibilité et permettent de référencer plusieurs fois le même résultat
intermédiaire.
-- CTE simple
WITH moyennes_dept AS (
SELECT departement_id, AVG(salaire) AS moy
FROM employes
GROUP BY departement_id
)
SELECT [Link], [Link], [Link], [Link] - [Link] AS ecart
FROM employes e
JOIN moyennes_dept m ON e.departement_id = m.departement_id
WHERE [Link] > [Link];

-- CTE multiples (chaînées)

WITH
actifs AS (
SELECT * FROM employes WHERE statut = 'actif'
),
top_salaires AS (
SELECT *, RANK() OVER (PARTITION BY departement_id ORDER BY salaire
DESC) AS rang
FROM actifs
)
SELECT * FROM top_salaires WHERE rang <= 3;

-- CTE RÉCURSIVE : hiérarchie managériale

WITH RECURSIVE hierarchie AS (
-- Cas de base : PDG (sans manager)
SELECT id, nom, manager_id, 0 AS niveau
FROM employes WHERE manager_id IS NULL

UNION ALL

-- Cas récursif : collaborateurs

SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employes e
JOIN hierarchie h ON e.manager_id = [Link]
)
SELECT LPAD(' ', niveau * 2, ' ') || nom AS arbre, niveau
FROM hierarchie ORDER BY niveau, nom;

Page 17 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 4 — Vues (Views)

4.1 Qu'est-ce qu'une vue ?

Une vue est une requête SELECT stockée sous forme d'objet nommé dans la base de données.
Elle se comporte comme une table virtuelle : on peut l'interroger avec SELECT, la joindre à
d'autres tables, voire l'utiliser dans d'autres vues.
Avantage Description
Abstraction Cache la complexité des jointures aux développeurs
Sécurité Limite l'accès à certaines colonnes/lignes
Maintenabilité Centralise la logique métier en un seul endroit
Compatibilité Isole les applications des changements de schéma

4.2 Création et manipulation

-- Créer une vue
CREATE VIEW v_employes_actifs AS
SELECT [Link], [Link], [Link], [Link], [Link] AS departement
FROM employes e
JOIN departements d ON e.departement_id = [Link]
WHERE [Link] = 'actif';

-- Utiliser la vue
SELECT * FROM v_employes_actifs WHERE departement = 'Informatique';

-- Modifier une vue

CREATE OR REPLACE VIEW v_employes_actifs AS
SELECT [Link], [Link], [Link], [Link], [Link], [Link] AS departement
FROM employes e
JOIN departements d ON e.departement_id = [Link]
WHERE [Link] = 'actif';

-- Supprimer une vue

DROP VIEW IF EXISTS v_employes_actifs;

Page 18 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

4.3 Vues modifiables

Une vue est modifiable (INSERT/UPDATE/DELETE) si elle respecte ces conditions :
• Elle ne contient pas DISTINCT, GROUP BY, HAVING, UNION
• Elle ne contient pas de fonctions d'agrégation
• Elle référence une seule table de base
• Toutes les colonnes NOT NULL sont incluses
-- Vue modifiable simple
CREATE VIEW v_employes_dept2 AS
SELECT id, nom, salaire FROM employes WHERE departement_id = 2;

-- Cette mise à jour affecte la table employes

UPDATE v_employes_dept2 SET salaire = 4000 WHERE id = 15;

-- WITH CHECK OPTION : empêche les modifications qui sortiraient la ligne

de la vue
CREATE VIEW v_employes_dept2 AS
SELECT id, nom, salaire, departement_id
FROM employes WHERE departement_id = 2
WITH CHECK OPTION;

4.4 Vues matérialisées (PostgreSQL, Oracle)

Contrairement aux vues ordinaires, une vue matérialisée stocke physiquement le résultat de la
requête. Elle doit être rafraîchie explicitement.
-- PostgreSQL
CREATE MATERIALIZED VIEW mv_stats_dept AS
SELECT departement_id,
COUNT(*) AS nb_employes,
AVG(salaire) AS salaire_moyen,
SUM(salaire) AS masse_salariale
FROM employes GROUP BY departement_id;

-- Rafraîchir (recalcule les données)

REFRESH MATERIALIZED VIEW mv_stats_dept;

-- Rafraîchir sans bloquer les lectures (concurrent)

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_stats_dept;

Page 19 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 5 — Procédures stockées et fonctions

5.1 Procédures stockées

Une procédure stockée est un bloc de code SQL précompilé et stocké dans la base de
données, exécutable par un simple appel. Elle peut prendre des paramètres IN, OUT ou INOUT.
-- Délimiteur (MySQL) : évite la confusion avec le ; des instructions
internes
DELIMITER $$

CREATE PROCEDURE augmenter_salaires(

IN p_departement_id INT,
IN p_pourcentage DECIMAL(5,2),
OUT p_nb_employes INT
)
BEGIN
-- Variables locales
DECLARE v_msg VARCHAR(200);

-- Corps de la procédure
UPDATE employes
SET salaire = salaire * (1 + p_pourcentage / 100)
WHERE departement_id = p_departement_id;

-- Récupération du nombre de lignes affectées

SET p_nb_employes = ROW_COUNT();

SET v_msg = CONCAT('Augmentation appliquée à ', p_nb_employes, '

employés.');
SELECT v_msg AS message;
END$$

DELIMITER ;

-- Appel
CALL augmenter_salaires(2, 5.0, @nb);
SELECT @nb AS nb_modifies;

Page 20 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Gestion des erreurs avec DECLARE HANDLER

DELIMITER $$
CREATE PROCEDURE inserer_employe(
IN p_nom VARCHAR(100),
IN p_email VARCHAR(150)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Erreur : opération annulée' AS message;
END;

START TRANSACTION;
INSERT INTO employes (nom, email) VALUES (p_nom, p_email);
COMMIT;
SELECT 'Employé inséré avec succès' AS message;
END$$
DELIMITER ;

5.2 Fonctions stockées

Contrairement aux procédures, une fonction retourne UNE valeur et peut être utilisée
directement dans une requête SELECT.
DELIMITER $$
CREATE FUNCTION calculer_bonus(
p_salaire DECIMAL(10,2),
p_anciennete INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_bonus DECIMAL(10,2);
SET v_bonus = CASE
WHEN p_anciennete >= 10 THEN p_salaire * 0.15
WHEN p_anciennete >= 5 THEN p_salaire * 0.10
WHEN p_anciennete >= 2 THEN p_salaire * 0.05
ELSE 0
END;
RETURN v_bonus;
END$$
DELIMITER ;

-- Utilisation dans une requête

SELECT nom, salaire,
DATEDIFF(NOW(), date_embauche) / 365 AS anciennete,
calculer_bonus(salaire, FLOOR(DATEDIFF(NOW(), date_embauche)/365)) AS
bonus
FROM employes;

Page 21 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Différences Procédure vs Fonction

Critère Procédure Fonction
Valeur de retour 0, 1 ou plusieurs (via OUT) Exactement 1 valeur
Utilisation dans SELECT Non Oui
Appel CALL proc(...) SELECT func(...)
Transaction Peut contenir BEGIN/COMMIT Ne peut pas gérer les transactions
Usage typique Traitements par lots, ETL Calculs réutilisables

5.3 Structures de contrôle

-- IF / ELSEIF / ELSE
IF condition THEN
...
ELSEIF autre_condition THEN
...
ELSE
...
END IF;

-- CASE
CASE variable
WHEN valeur1 THEN ...
WHEN valeur2 THEN ...
ELSE ...
END CASE;

-- Boucles
-- WHILE
WHILE condition DO
-- instructions
END WHILE;

-- LOOP (sortie avec LEAVE)

mon_label: LOOP
IF condition_sortie THEN LEAVE mon_label; END IF;
END LOOP;

-- REPEAT UNTIL
REPEAT
-- instructions
UNTIL condition END REPEAT;

Page 22 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 6 — Triggers (Déclencheurs)

6.1 Concept et utilisation

Un trigger est un bloc de code automatiquement exécuté en réponse à un événement DML
(INSERT, UPDATE, DELETE) sur une table. Les triggers permettent d'assurer l'intégrité des
données, de tracer les modifications, ou d'automatiser des processus.
Timing Événement Usage typique
BEFORE INSERT Avant insertion Validation, transformation des données
AFTER INSERT Après insertion Log, mise à jour de tables liées
BEFORE UPDATE Avant mise à jour Audit, contrôle de valeurs
AFTER UPDATE Après mise à jour Propagation de changements
BEFORE DELETE Avant suppression Contrôle d'intégrité, archivage
AFTER DELETE Après suppression Nettoyage en cascade, log

6.2 Création de triggers

Trigger de journalisation (audit)
-- Table d'audit
CREATE TABLE audit_salaires (
id INT PRIMARY KEY AUTO_INCREMENT,
employe_id INT,
ancien_salaire DECIMAL(10,2),
nouveau_salaire DECIMAL(10,2),
modifie_par VARCHAR(100),
modifie_le DATETIME
);

-- Trigger AFTER UPDATE

DELIMITER $$
CREATE TRIGGER trg_audit_salaire
AFTER UPDATE ON employes
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO audit_salaires
(employe_id, ancien_salaire, nouveau_salaire, modifie_par,
modifie_le)
VALUES
([Link], [Link], [Link], USER(), NOW());
END IF;
END$$
DELIMITER ;

Page 23 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Trigger BEFORE pour validation

DELIMITER $$
CREATE TRIGGER trg_valider_salaire
BEFORE INSERT ON employes
FOR EACH ROW
BEGIN
-- Empêcher un salaire négatif
IF [Link] < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Le salaire ne peut pas être négatif';
END IF;

-- Normaliser l'email en minuscules

SET [Link] = LOWER([Link]);
END$$
DELIMITER ;

Trigger pour table de stock

DELIMITER $$
CREATE TRIGGER trg_maj_stock
AFTER INSERT ON lignes_commande
FOR EACH ROW
BEGIN
UPDATE produits
SET stock = stock - [Link]
WHERE id = NEW.produit_id;

-- Alerte si stock insuffisant

IF (SELECT stock FROM produits WHERE id = NEW.produit_id) < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Stock insuffisant pour ce produit';
END IF;
END$$
DELIMITER ;

Gestion des triggers

-- Lister les triggers d'une base
SHOW TRIGGERS FROM ma_base;

-- Voir le code d'un trigger

SHOW CREATE TRIGGER trg_audit_salaire;

-- Supprimer un trigger
DROP TRIGGER IF EXISTS trg_audit_salaire;

-- Désactiver temporairement (MySQL)

SET @TRIGGER_CHECKS = FALSE;
Attention : Les triggers sont transparents pour l'application appelante, ce qui peut rendre le
débogage difficile. Documentez-les soigneusement et évitez les effets de bord en cascade.

Page 24 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 7 — Transactions

7.1 Les propriétés ACID

Une transaction est un ensemble d'opérations SQL exécutées comme une unité indivisible. Elle
respecte les propriétés ACID :
Propriété Signification Garantit
Atomicité Atomicity Tout ou rien : toutes les opérations réussissent ou aucune
Cohérence Consistency La BD passe d'un état valide à un autre état valide
Isolation Isolation Les transactions concurrentes ne s'interfèrent pas
Durabilité Durability Un COMMIT est permanent, même après crash

7.2 Syntaxe et usage

-- Virement bancaire : exemple classique
START TRANSACTION;

UPDATE comptes SET solde = solde - 1000 WHERE id = 1; -- débit

UPDATE comptes SET solde = solde + 1000 WHERE id = 2; -- crédit

-- Vérification
SELECT solde INTO @solde_debiteur FROM comptes WHERE id = 1;

IF @solde_debiteur < 0 THEN

ROLLBACK; -- Annulation complète
SELECT 'Solde insuffisant, virement annulé' AS msg;
ELSE
COMMIT; -- Validation permanente
SELECT 'Virement effectué avec succès' AS msg;
END IF;

7.3 SAVEPOINT — Points de sauvegarde

START TRANSACTION;

INSERT INTO commandes (client_id, date) VALUES (1, NOW());

SET @commande_id = LAST_INSERT_ID();

SAVEPOINT sp_commande_creee;

INSERT INTO lignes_commande (commande_id, produit_id, qte) VALUES

(@commande_id, 5, 2);
INSERT INTO lignes_commande (commande_id, produit_id, qte) VALUES
(@commande_id, 8, 1);

SAVEPOINT sp_lignes_ajoutees;

-- Si le paiement échoue, on revient avant les lignes

-- ROLLBACK TO SAVEPOINT sp_commande_creee;

-- Si tout va bien
COMMIT;

Page 25 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

7.4 Niveaux d'isolation

Le niveau d'isolation définit la façon dont les transactions concurrentes se voient mutuellement.
Plus l'isolation est forte, plus les performances diminuent.
Niveau Lecture sale Lecture non répétable Lecture fantôme
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Non Possible Possible
REPEATABLE READ Non Non Possible
SERIALIZABLE Non Non Non
-- Changer le niveau d'isolation (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Voir le niveau actuel

SELECT @@transaction_isolation;

Phénomènes d'anomalies
Anomalie Description
Dirty Read (lecture Lire des données non encore commitées par une autre transaction
sale)
Non-repeatable Read Une même ligne retourne des valeurs différentes dans la même transaction
Phantom Read Une requête retourne des lignes différentes à deux exécutions (lignes
ajoutées/supprimées)

Page 26 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 8 — Index et optimisation des

performances

8.1 Qu'est-ce qu'un index ?

Un index est une structure de données (souvent un B-Tree) qui accélère les recherches en
évitant les balayages complets de table (full table scan). En contrepartie, il ralentit légèrement
les écritures (INSERT, UPDATE, DELETE) et consomme de l'espace disque.

8.2 Types d'index

Type Description Cas d'usage
B-Tree (défaut) Arbre binaire équilibré Comparaisons =, <, >, BETWEEN, LIKE 'abc%'
Hash Table de hachage Uniquement égalité (=), très rapide
FULLTEXT Index inversé sur le texte Recherche plein texte (MATCH AGAINST)
Spatial (R-Tree) Index géographique Données géométriques (GIS)
Composite Plusieurs colonnes Requêtes filtrant sur plusieurs colonnes
Partiel/Filtré Index sur un sous-ensemble Quand on filtre souvent sur une valeur

8.3 Création et gestion des index

-- Index simple
CREATE INDEX idx_employe_nom ON employes(nom);

-- Index unique
CREATE UNIQUE INDEX idx_employe_email ON employes(email);

-- Index composite (ordre des colonnes important !)

CREATE INDEX idx_dept_salaire ON employes(departement_id, salaire);

-- Index FULLTEXT
CREATE FULLTEXT INDEX idx_article_contenu ON articles(titre, contenu);

-- Index partiel (PostgreSQL)

CREATE INDEX idx_employes_actifs ON employes(nom) WHERE statut = 'actif';

-- Lister les index d'une table

SHOW INDEX FROM employes;

-- Supprimer un index
DROP INDEX idx_employe_nom ON employes;

Page 27 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

8.4 Analyse avec EXPLAIN

EXPLAIN analyse et affiche le plan d'exécution d'une requête. C'est l'outil de base pour
identifier les problèmes de performance.
-- Analyser une requête
EXPLAIN SELECT * FROM employes WHERE departement_id = 3 AND salaire > 3000;

-- Format étendu (MySQL 8+)

EXPLAIN FORMAT=JSON SELECT ...

-- EXPLAIN ANALYZE : exécute vraiment et mesure (PostgreSQL)

EXPLAIN ANALYZE SELECT * FROM employes WHERE nom LIKE 'D%';

Colonnes importantes de EXPLAIN

Colonne Signification Valeurs à surveiller
type Type d'accès ALL = full scan (mauvais), ref/eq_ref/const = bon
key Index utilisé NULL = aucun index utilisé
rows Lignes estimées à examiner Doit être le plus bas possible
Extra Informations supplémentaires Using filesort / Using temporary = lent

8.5 Bonnes pratiques d'indexation

• Indexer les colonnes fréquemment utilisées dans WHERE, JOIN ON, ORDER BY
• L'ordre dans un index composite compte : placer d'abord la colonne la plus sélective
• Éviter d'indexer les colonnes à faible cardinalité (ex. : colonne booléenne)
• Ne pas sur-indexer : chaque index ralentit les INSERT/UPDATE/DELETE
• Utiliser des index couvrants (covering index) : toutes les colonnes du SELECT dans
l'index
• Surveiller les index inutilisés et les supprimer

Index couvrant (Covering Index)

-- Requête fréquente : récupérer nom et salaire par département
SELECT nom, salaire FROM employes WHERE departement_id = 2;

-- Index couvrant : inclut toutes les colonnes nécessaires

-- MySQL lit directement l'index, sans accéder à la table
CREATE INDEX idx_covering ON employes(departement_id, nom, salaire);

-- EXPLAIN montrera 'Using index' dans Extra : très efficace

Page 28 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 9 — Sécurité et gestion des accès (DCL)

9.1 Gestion des utilisateurs

-- Créer un utilisateur
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'MotDePasse@2024!';

-- Créer un utilisateur accessible depuis n'importe quelle IP

CREATE USER 'reporting'@'%' IDENTIFIED BY 'Secure@Pass2024';

-- Modifier le mot de passe

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NouveauMDP@2024';

-- Supprimer un utilisateur
DROP USER 'app_user'@'localhost';

9.2 GRANT — Attribution des droits

-- Tous les droits sur une base
GRANT ALL PRIVILEGES ON ma_base.* TO 'admin'@'localhost';

-- Droits spécifiques
GRANT SELECT, INSERT, UPDATE ON ma_base.employes TO 'app_user'@'localhost';

-- Lecture seule sur toutes les bases

GRANT SELECT ON *.* TO 'reporting'@'%';

-- Droit de créer des objets

GRANT CREATE, DROP ON ma_base.* TO 'dev_user'@'localhost';

-- Appliquer les changements

FLUSH PRIVILEGES;

-- Voir les droits d'un utilisateur

SHOW GRANTS FOR 'app_user'@'localhost';

9.3 REVOKE — Révocation des droits

-- Révoquer des droits spécifiques
REVOKE INSERT, UPDATE ON ma_base.employes FROM 'app_user'@'localhost';

-- Révoquer tous les droits

REVOKE ALL PRIVILEGES ON ma_base.* FROM 'reporting'@'%';

Page 29 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

9.4 Principe du moindre privilège

Chaque utilisateur ou application ne doit avoir que les droits strictement nécessaires à son
fonctionnement. C'est un principe fondamental de sécurité.
Profil Droits recommandés
Application web (lecture/écriture) SELECT, INSERT, UPDATE, DELETE sur les tables nécessaires
Application web (lecture seule) SELECT uniquement
Reporting / BI SELECT sur les vues, jamais sur les tables brutes
Administrateur DBA ALL PRIVILEGES, uniquement depuis localhost
ETL / batch SELECT, INSERT sur les tables cibles uniquement

Page 30 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 10 — Partitionnement

10.1 Concept
Le partitionnement consiste à diviser physiquement une grande table en plusieurs morceaux
(partitions) tout en la présentant comme une table unique. Cela améliore les performances sur
les très grandes tables (> millions de lignes) et facilite la gestion des données historiques.

10.2 Types de partitionnement

RANGE — par plage de valeurs
CREATE TABLE ventes (
id INT,
montant DECIMAL(10,2),
date_vente DATE
)
PARTITION BY RANGE (YEAR(date_vente)) (
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pfutur VALUES LESS THAN MAXVALUE
);

LIST — par liste de valeurs

PARTITION BY LIST (region_id) (
PARTITION p_nord VALUES IN (1, 2, 3),
PARTITION p_sud VALUES IN (4, 5, 6),
PARTITION p_est VALUES IN (7, 8),
PARTITION p_ouest VALUES IN (9, 10)
);

HASH — distribution uniforme

PARTITION BY HASH (client_id)
PARTITIONS 8; -- 8 partitions

Gestion des partitions

-- Ajouter une partition
ALTER TABLE ventes ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

-- Supprimer une partition (et ses données)

ALTER TABLE ventes DROP PARTITION p2021;

-- Voir les partitions

SELECT partition_name, table_rows
FROM information_schema.partitions
WHERE table_name = 'ventes';

Page 31 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 11 — Normalisation

11.1 Pourquoi normaliser ?

La normalisation est le processus de structuration d'une base de données pour réduire la
redondance et améliorer l'intégrité des données. Elle est définie par des formes normales (FN)
progressives.

11.2 Les formes normales

Forme normale Règle
1NF — Première Chaque cellule contient une valeur atomique (indivisible). Pas de groupes
FN répétés.
2NF — Deuxième En 1NF + toutes les colonnes dépendent de la clé ENTIÈRE (élimine les
FN dépendances partielles)
3NF — Troisième En 2NF + pas de dépendance transitive (A → B → C doit devenir deux tables)
FN
BCNF — Boyce- Forme renforcée de la 3NF : chaque déterminant est une clé candidate
Codd
4NF — Quatrième Pas de dépendances multi-valuées indépendantes
FN

Exemple de normalisation
-- Table non normalisée (viola 1NF et 2NF)
-- commande_id | client_nom | client_ville | produit1 | produit2 | total

-- Après normalisation 3NF :

-- TABLE clients (id, nom, ville)
-- TABLE produits (id, nom, prix_unitaire)
-- TABLE commandes (id, client_id, date, total)
-- TABLE lignes_commande (commande_id, produit_id, quantite, prix)

Page 32 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Chapitre 12 — Optimisation des requêtes

12.1 Règles générales

• Sélectionner uniquement les colonnes nécessaires (éviter SELECT *)
• Utiliser des index sur les colonnes de filtrage, jointure et tri
• Éviter les fonctions sur les colonnes indexées dans WHERE (empêchent l'utilisation de
l'index)
• Préférer JOIN à des sous-requêtes corrélées quand possible
• Utiliser LIMIT pour les grandes tables lors du développement
• Analyser régulièrement avec EXPLAIN

12.2 Pièges courants

Fonctions sur colonnes indexées
-- MAUVAIS : l'index sur date_embauche n'est pas utilisé
SELECT * FROM employes WHERE YEAR(date_embauche) = 2022;

-- BON : reformulé pour utiliser l'index

SELECT * FROM employes
WHERE date_embauche BETWEEN '2022-01-01' AND '2022-12-31';

LIKE avec wildcard au début

-- MAUVAIS : wildcard en début = scan complet
SELECT * FROM employes WHERE nom LIKE '%dupont%';

-- BON si possible : wildcard seulement en fin

SELECT * FROM employes WHERE nom LIKE 'dupont%';

-- Alternative pour recherche plein texte

SELECT * FROM articles WHERE MATCH(titre) AGAINST('dupont' IN BOOLEAN
MODE);

Pagination efficace
-- LENT pour grandes pages (OFFSET 100000 scanne 100000 lignes)
SELECT * FROM employes ORDER BY id LIMIT 10 OFFSET 100000;

-- RAPIDE : keyset pagination (cursor-based)

SELECT * FROM employes
WHERE id > 100000 -- dernier id de la page précédente
ORDER BY id LIMIT 10;

Page 33 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

12.3 Statistiques et maintenance

-- Mettre à jour les statistiques (MySQL)
ANALYZE TABLE employes;

-- Reconstruire et optimiser la table

OPTIMIZE TABLE employes;

-- PostgreSQL : vacuum et analyze

VACUUM ANALYZE employes;

-- Voir la taille des tables

SELECT table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_MB,
ROUND(index_length / 1024 / 1024, 2) AS index_MB
FROM information_schema.tables
WHERE table_schema = 'ma_base'
ORDER BY data_length DESC;

Page 34 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Annexe — Aide-mémoire SQL

Fonctions d'agrégation
Fonction Description
COUNT(*) Nombre de lignes
COUNT(col) Nombre de valeurs non-NULL
SUM(col) Somme
AVG(col) Moyenne
MIN(col) / MAX(col) Minimum / Maximum
GROUP_CONCAT(col) Concaténation groupée (MySQL)
STRING_AGG(col, sep) Concaténation groupée (PostgreSQL)

Fonctions de fenêtrage
Fonction Description
ROW_NUMBER() Numéro unique par ligne
RANK() Rang avec saut
DENSE_RANK() Rang sans saut
NTILE(n) Découpage en n groupes
LAG(col, n) Valeur de la ligne n positions avant
LEAD(col, n) Valeur de la ligne n positions après
FIRST_VALUE(col) Première valeur de la fenêtre
LAST_VALUE(col) Dernière valeur de la fenêtre
SUM/AVG/MIN/MAX OVER() Agrégat fenêtré

Niveaux d'isolation — rappel

Niveau Performance Isolation
READ UNCOMMITTED Très haute Très faible
READ COMMITTED Haute Faible
REPEATABLE READ Moyenne Bonne
SERIALIZABLE Basse Totale

Page 35 / 36
SQL Avancé — Master SSI | Youssoupha LAM, Consultant SI

Checklist de performance
• EXPLAIN analysé pour les requêtes lentes
• Index sur toutes les colonnes de JOIN et WHERE
• SELECT * remplacé par la liste des colonnes nécessaires
• Pas de fonction sur colonnes indexées dans WHERE
• LIKE avec wildcard en début évité
• Pagination en keyset si large OFFSET
• Statistiques à jour (ANALYZE TABLE)
• Transactions courtes pour éviter les locks
• Index inutilisés supprimés

Page 36 / 36