Aide-mémoire SQL

L'ordre des clauses

On écrit les clauses dans cet ordre, mais SQL les exécute dans un autre : FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. C'est pour cela que l'on filtre les groupes avec HAVING : au moment du WHERE, les groupes n'existent pas encore.

La syntaxe par thème

SELECT col1, col2 AS alias
FROM ma_table;

WHERE prix > 10 AND ville IN ('Lyon', 'Paris')
WHERE nom LIKE 'B%'     -- commence par B
WHERE age IS NULL

ORDER BY prix DESC, nom
LIMIT 10 OFFSET 20

SELECT DISTINCT ville FROM clients;
SELECT COUNT(DISTINCT ville) FROM clients;

SELECT COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x)
FROM ma_table;

SELECT ville, COUNT(*) AS nb
FROM clients
GROUP BY ville
HAVING COUNT(*) > 5;

SELECT o.id, c.nom
FROM commandes o
JOIN clients c ON c.id = o.client_id;

SELECT c.nom
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
WHERE o.id IS NULL;   -- clients sans commande

WHERE prix > (SELECT AVG(prix) FROM produits)
WHERE id IN (SELECT client_id FROM commandes)
WHERE NOT EXISTS (SELECT 1 FROM … WHERE …)

WITH etape AS (
  SELECT client_id, COUNT(*) AS nb
  FROM commandes GROUP BY client_id
)
SELECT * FROM etape WHERE nb > 5;

RANK() OVER (PARTITION BY categorie ORDER BY prix DESC)
ROW_NUMBER() OVER (ORDER BY date_commande)
SUM(ca) OVER (ORDER BY mois)   -- cumul

CASE
  WHEN prix >= 100 THEN 'premium'
  ELSE 'standard'
END AS gamme

INSERT INTO t (a, b) VALUES (1, 'x');
UPDATE t SET a = a + 1 WHERE id = 3;
DELETE FROM t WHERE id = 3;

CREATE TABLE t (id INTEGER PRIMARY KEY, nom TEXT);
ALTER TABLE t ADD COLUMN age INTEGER;
CREATE VIEW v AS SELECT * FROM t;

ROUND(x, 2)    LENGTH(s)    UPPER(s)    LOWER(s)
COALESCE(x, 0) a || b       substr(s, 1, 3)
strftime('%Y', date)        date('now')

Les valeurs manquantes (NULL)

On ne compare jamais avec = NULL : on écrit IS NULL ou IS NOT NULL. Les fonctions SUM, AVG, MIN, MAX et COUNT(colonne) ignorent les NULL, alors que COUNT(*) compte toutes les lignes. Voir le cours sur NULL.