LE GRIMOIRE
COMPLET

SELECT choisit les colonnes, FROM la table. C’est le squelette de toute requête de lecture.

SELECT name, tonnage
FROM vessels;

SELECT * renvoie toutes les colonnes — pratique pour explorer, à éviter en production.

AS donne un nom d’affichage à une colonne ou à un calcul. Le résultat est plus lisible et les alias servent de raccourci dans le reste de la requête.

SELECT name AS navire, tonnage AS poids
FROM vessels;

AS est optionnel : `tonnage poids` marche aussi. Les guillemets doubles gardent espaces et majuscules : AS "Poids en tonnes".

DISTINCT retire les doublons du résultat. Utile pour répondre à « quelles valeurs existent ? ».

SELECT DISTINCT category
FROM goods;

DISTINCT porte sur toutes les colonnes listées : `DISTINCT category, name` garde les paires uniques, pas les catégories uniques.

WHERE ne garde que les lignes qui remplissent la condition. Opérateurs : = <> < > <= >=.

SELECT name, tonnage
FROM vessels
WHERE tonnage > 100;

Le texte se compare entre apostrophes : WHERE status = 'livré'. `<>` (ou `!=`) veut dire « différent de ».

AND exige les deux conditions, OR au moins une, NOT inverse. On en enchaîne autant qu’on veut.

SELECT name, tonnage, port_id
FROM vessels
WHERE tonnage >= 100 AND port_id = 2;

AND est évalué avant OR : mets des parenthèses au moindre doute — WHERE a AND (b OR c).

BETWEEN teste un intervalle, IN une liste de valeurs. Plus court et plus clair qu’une chaîne de OR.

SELECT name, tonnage
FROM vessels
WHERE tonnage BETWEEN 90 AND 120;

BETWEEN inclut les deux bornes. `port_id IN (1, 2)` = port_id = 1 OR port_id = 2 ; `NOT IN (…)` pour exclure.

LIKE compare du texte à un motif : `%` remplace n’importe quelle suite de caractères, `_` un seul.

SELECT name
FROM ports
WHERE name LIKE 'C%';

ILIKE ignore la casse (propre à PostgreSQL). Pour un `%` littéral, il faut l’échapper.

NULL veut dire « inconnu ». Il ne vaut rien, pas même un autre NULL, d’où un test dédié.

SELECT name
FROM captains
WHERE home_port_id IS NULL;

Toujours IS NULL / IS NOT NULL — jamais `= NULL`, qui ne renvoie jamais vrai.

COALESCE renvoie le premier de ses arguments qui n’est pas NULL — parfait pour afficher une valeur de repli.

SELECT name, COALESCE(home_port_id, 0) AS port_ou_zero
FROM captains;

`NULLIF(a, b)` fait l’inverse : il renvoie NULL quand a = b.

CASE choisit une valeur selon des conditions, comme un if/else. Il se termine par END.

SELECT name,
       CASE WHEN tonnage >= 120 THEN 'lourd'
            WHEN tonnage >= 90  THEN 'moyen'
            ELSE 'léger' END AS gabarit
FROM vessels;

CASE s’utilise partout : SELECT, WHERE, ORDER BY, et dans un agrégat — sum(CASE WHEN … THEN 1 ELSE 0 END).

Une colonne peut être un calcul, pas seulement un champ brut. Les opérateurs : + - * / %.

SELECT name, unit_price,
       round(unit_price * 1.2, 2) AS prix_ttc
FROM goods;

Concaténer du texte : `name || ' — ' || category`. `round(x, n)` arrondit à n décimales.

PostgreSQL fournit des dizaines de fonctions : sur le texte, upper/lower changent la casse, length compte les caractères.

SELECT upper(name) AS majuscule, length(name) AS taille
FROM regions;

Autres utiles : trim, substr(x, 1, 3), replace(x, 'a', 'b'), left(x, 4), position('-' in x).

ORDER BY range les lignes : ASC (croissant) par défaut, DESC pour l’inverse.

SELECT name, tonnage
FROM vessels
ORDER BY tonnage DESC;

Tri à plusieurs niveaux : `ORDER BY category, unit_price DESC`. `NULLS LAST` renvoie les vides en fin de liste.

LIMIT plafonne le nombre de lignes renvoyées. OFFSET en saute au début — les deux servent à paginer.

SELECT name, tonnage
FROM vessels
ORDER BY tonnage DESC
LIMIT 3;

`LIMIT 10 OFFSET 20` = la 3ᵉ page de 10. Sans ORDER BY, « les 3 premières » n’a aucun sens : l’ordre n’est pas garanti.

Les fonctions d’agrégat réduisent plusieurs lignes à une seule valeur : count, sum, avg, min, max.

SELECT count(*) AS navires,
       round(avg(tonnage), 1) AS tonnage_moyen,
       max(tonnage) AS plus_gros
FROM vessels;

count(*) compte les lignes ; count(colonne) ignore les NULL de cette colonne.

GROUP BY réunit les lignes qui partagent une valeur, puis les agrégats calculent une valeur par groupe.

SELECT status, count(*) AS total
FROM shipments
GROUP BY status;

Règle d’or : toute colonne du SELECT qui n’est pas dans un agrégat doit figurer dans le GROUP BY.

HAVING est un WHERE qui s’applique aux groupes, une fois les agrégats calculés.

SELECT port_id, count(*) AS navires
FROM vessels
GROUP BY port_id
HAVING count(*) > 1;

WHERE filtre les lignes AVANT le GROUP BY, HAVING filtre les groupes APRÈS. Les deux peuvent coexister.

JOIN relie deux tables sur une colonne commune, ici l’id du port rangé dans vessels.port_id.

SELECT v.name AS navire, p.name AS port
FROM vessels v
JOIN ports p ON p.id = v.port_id;

JOIN seul = INNER JOIN : seules les lignes qui ont une correspondance des deux côtés ressortent.

LEFT JOIN garde toutes les lignes de la table de gauche ; les colonnes de droite valent NULL quand rien ne correspond.

SELECT v.name, c.name AS capitaine
FROM vessels v
LEFT JOIN captains c ON c.id = v.captain_id;

RIGHT JOIN fait l’inverse, FULL JOIN garde les deux côtés. `WHERE c.id IS NULL` isole les lignes sans match.

On enchaîne autant de JOIN que nécessaire pour remonter une chaîne de tables : navire → capitaine → port → région.

SELECT v.name AS navire, c.name AS capitaine, r.name AS region
FROM vessels v
JOIN captains c ON c.id = v.captain_id
JOIN ports p ON p.id = c.home_port_id
JOIN regions r ON r.id = p.region_id;

Chaque JOIN a sa condition ON. L’ordre des JOIN ne change pas le résultat d’un INNER JOIN, seulement la lisibilité.

Quand une ligne pointe deux fois vers la même table — ici un départ et une arrivée dans ports — on joint ports deux fois, sous deux alias.

SELECT cr.id,
       dep.name AS depart,
       arr.name AS arrivee
FROM crossings cr
JOIN ports dep ON dep.id = cr.from_port_id
JOIN ports arr ON arr.id = cr.to_port_id;

Sans les alias dep / arr, PostgreSQL ne saurait pas de quelle copie de ports on parle.

UNION met bout à bout les lignes de deux SELECT qui ont les mêmes colonnes.

SELECT name FROM ports
UNION
SELECT name FROM regions
ORDER BY name;

UNION dédoublonne ; `UNION ALL` garde tout (et va plus vite). Voir aussi INTERSECT (commun) et EXCEPT (différence).

Une sous-requête entre parenthèses est calculée d’abord ; sa valeur sert ensuite de condition.

SELECT name, tonnage
FROM vessels
WHERE tonnage > (SELECT avg(tonnage) FROM vessels);

Si la sous-requête peut renvoyer plusieurs lignes, utilise `IN (SELECT …)` au lieu de `= (SELECT …)`.

EXISTS répond « y a-t-il au moins une ligne ? ». Corrélée : la sous-requête cite la ligne courante (p.id).

SELECT p.name
FROM ports p
WHERE EXISTS (
SELECT   1 FROM vessels v WHERE v.port_id = p.id
);

EXISTS s’arrête au premier match, donc il est souvent rapide. `NOT EXISTS` pour « aucune ligne ».

Dans le FROM, une sous-requête se comporte comme une table temporaire. Elle doit avoir un alias.

SELECT region_id, count(*) AS nb_ports_anciens
FROM (
SELECT   id, region_id FROM ports WHERE founded_year < 1750
) AS anciens
GROUP BY region_id;

Souvent remplaçable par une CTE `WITH`, qui se lit de haut en bas plutôt que de l’intérieur vers l’extérieur.

Une CTE (Common Table Expression) est un résultat intermédiaire nommé, défini en tête de requête et réutilisé ensuite.

WITH livraisons AS (
SELECT   vessel_id, count(*) AS n
FROM   shipments
WHERE   status = 'livré'
GROUP BY   vessel_id
)
SELECT v.name, livraisons.n
FROM livraisons
JOIN vessels v ON v.id = livraisons.vessel_id
ORDER BY livraisons.n DESC, v.name;

On chaîne plusieurs CTE séparées par des virgules. `WITH RECURSIVE` permet même de parcourir une hiérarchie.

Une fonction fenêtre calcule sur un groupe de lignes MAIS garde chaque ligne — la valeur s’affiche à côté.

SELECT name, port_id, tonnage,
       round(avg(tonnage) OVER (PARTITION BY port_id), 1) AS moy_du_port
FROM vessels;

PARTITION BY découpe la fenêtre en groupes ; sans lui, la fenêtre couvre toute la table.

row_number, rank et dense_rank numérotent les lignes selon un ORDER BY à l’intérieur de la fenêtre.

SELECT name, tonnage,
       rank() OVER (ORDER BY tonnage DESC) AS rang
FROM vessels;

row_number() : numéro unique. rank() : laisse un trou après les ex æquo. dense_rank() : sans trou.

Les dates se comparent avec < > =, et se décomposent avec extract(). CURRENT_DATE donne le jour même.

SELECT id, shipped_on,
       extract(month FROM shipped_on) AS mois
FROM shipments
WHERE shipped_on >= DATE '2024-05-01'
ORDER BY shipped_on;

Soustraire deux dates donne un nombre de jours : `arrived_on - departed_on`. `age(a, b)` donne un intervalle lisible.

Quatre requêtes pour ancrer les JOIN. Lis la solution, puis refais-la de mémoire dans le bac à sable.

SELECT v.name AS navire, p.name AS port
FROM vessels v
JOIN ports p ON p.id = v.port_id
ORDER BY v.name;

Modifie une seule chose à la fois — une table, une condition ON, un type de jointure — pour voir ce que chacune change.

COUNT, SUM, AVG, GROUP BY, HAVING réunis sur un même jeu de données. Cinq gestes, une base.

SELECT category, count(*) AS lignes, round(avg(unit_price), 2) AS prix_moyen
FROM goods
GROUP BY category
ORDER BY category;

Un agrégat sans GROUP BY renvoie une ligne pour toute la table ; avec, une ligne par groupe.

Décomposer un problème : une requête calcule une valeur ou un ensemble, une seconde s’appuie dessus.

SELECT name, tonnage
FROM vessels
WHERE tonnage > (SELECT avg(tonnage) FROM vessels)
ORDER BY tonnage DESC;

Dès qu’une sous-requête devient dure à lire, sors-la dans une CTE `WITH` en tête : même résultat, plus clair.

SQL sert aussi à écrire et à définir des tables. Le bac à sable est en lecture seule, mais ces commandes font partie du langage.

-- Modifier les données
INSERT INTO goods (id, name, category, unit_price) VALUES (11, 'Thé', 'boisson', 5.0);
UPDATE goods SET unit_price = 6.0 WHERE id = 11;
DELETE FROM goods WHERE id = 11;

Structure : CREATE TABLE, ALTER TABLE, DROP TABLE. Transactions : BEGIN … COMMIT / ROLLBACK. Ici, on ne pratique que SELECT.