Cours de SQL

Les fondations : lire des données d'une table et les filtrer.

1. 1.1Introduction au SQLCe qu'est le SQL, pourquoi il est utilisé et à quoi ressemble une base de données.~5 min
2. 1.2SELECT … FROMLa requête la plus simple : lire des lignes d'une table.~10 min
3. 1.3Filtrer avec WHERESélectionner uniquement les lignes qui satisfont à une condition.~10 min
4. 1.4Trier avec ORDER BYMettre les résultats en ordre, croissant ou décroissant.~8 min

Limiter, dédoublonner, renommer et compter : les outils que vous utilisez tous les jours.

1. 2.1Limiter avec LIMITRetourner uniquement les N premières lignes, par exemple pour construire des classements.~6 min
2. 2.2Valeurs uniques avec DISTINCTSupprimer les doublons du résultat d'un SELECT.~6 min
3. 2.3Renommer avec ASDonner des noms plus lisibles aux colonnes et expressions dans le résultat.~6 min
4. 2.4Compter et résumer : COUNT, AVG, MAXFonctions d'agrégation : obtenir un seul nombre à partir de plusieurs lignes.~8 min

Combiner plusieurs tables et produire des rapports par groupe : le cœur de chaque vraie requête.

1. 3.1Joindre deux tables : INNER JOINCombiner les lignes de différentes tables en suivant leurs relations.~12 min
2. 3.2Garder tout à gauche : LEFT JOINInclure les lignes qui n'ont pas de correspondance dans l'autre table.~10 min
3. 3.3Grouper avec GROUP BYCalculer un agrégat par groupe, par exemple par département.~10 min
4. 3.4Filtrer les groupes : HAVINGNe garder que les groupes qui satisfont à une condition sur l'agrégat.~8 min

Les outils qui font la différence dans le travail réel : listes, plages, NULL et logique conditionnelle.

1. 4.1Listes et plages : IN et BETWEENFiltres compacts pour « l'un de ceux-ci » et « entre A et B ».~8 min
2. 4.2Gestion des NULL : IS NULL et COALESCEReconnaître et gérer les valeurs manquantes sans surprises.~10 min
3. 4.3Logique conditionnelle : CASE WHENTransformer les valeurs en catégories directement dans le SELECT.~10 min
4. 4.4Une requête dans une autre : sous-requêtesUtiliser le résultat d'un SELECT dans un autre SELECT.~12 min

JOINs multi-tables, auto-jointures, OUTER, CROSS et opérations d'ensembles : combiner les données comme un pro.

1. 5.1JOINs sur trois tables ou plusParcourir une chaîne de relations : commandes → clients → produits, en une seule requête.~12 min
2. 5.2Self-JOIN : une table avec elle-mêmeHiérarchies et relations internes : lire une table avec deux rôles (enfant et parent).~10 min
3. 5.3OUTER JOIN : trouver les « écarts »Utiliser LEFT/RIGHT/FULL OUTER JOIN pour trouver des lignes sans correspondance.~10 min
4. 5.4UNION, INTERSECT, EXCEPT (et CROSS JOIN)Combiner les résultats sous forme d'ensembles : union, intersection, différence et produit cartésien.~12 min

Transformer et manipuler les données avec les fonctions natives de PostgreSQL : chaînes, dates, nombres, conversions de types.

1. 6.1Fonctions de chaînesLOWER, UPPER, LENGTH, concaténation, SUBSTRING, TRIM, REPLACE, LIKE et ILIKE.~12 min
2. 6.2Fonctions de date et d'horodatageEXTRACT, DATE_TRUNC, intervalles et différences de dates.~12 min
3. 6.3Fonctions numériquesROUND, CEIL, FLOOR, ABS, MOD et division entière vs décimale.~10 min
4. 6.4CAST et conversions de typesConvertir entre types avec `::type` et `CAST(... AS ...)`.~10 min

Requêtes en plusieurs étapes (CTE), classements et totaux cumulés (fonctions de fenêtre), hiérarchies illimitées (WITH RECURSIVE).

1. 7.1CTE : requêtes en plusieurs étapes avec WITHDécomposer une requête complexe en étapes nommées pour la lisibilité et la réutilisation.~12 min
2. 7.2Fonctions de fenêtre : classements et calculs par groupeROW_NUMBER, RANK, DENSE_RANK avec OVER(PARTITION BY …) pour les classements au sein d'un groupe.~14 min
3. 7.3LAG, LEAD et totaux cumulésComparer chaque ligne à la précédente/suivante et calculer des sommes cumulées.~12 min
4. 7.4CTEs récursives : hiérarchies de profondeur arbitraireWITH RECURSIVE pour parcourir des hiérarchies comme des catégories.~14 min

INSERT, UPDATE, DELETE, CREATE TABLE avec contraintes, transactions : boucler la boucle avec les écritures.

1. 8.1Insérer des données : INSERTINSERT d'une ou plusieurs lignes, clause RETURNING, choix explicite des colonnes.~10 min
2. 8.2Mettre à jour et supprimer : UPDATE et DELETEModifications sélectives avec WHERE, et pourquoi un WHERE oublié est un désastre.~10 min
3. 8.3Créer des tables et des contraintes : DDLCREATE TABLE avec PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK et DEFAULT.~12 min
4. 8.4Transactions : tout ou rienBEGIN, COMMIT, ROLLBACK : rendre atomique un groupe d'opérations.~10 min

Créer et modifier des tables, contraintes avancées, types complexes et vues.

1. 9.1Types de données avancés et ENUMsTypes numériques, TEXT vs VARCHAR, BOOLEAN, UUID et types ENUM personnalisés.~10 min
2. 9.2Contraintes multiples et au niveau de la tableContraintes composites UNIQUE et PRIMARY KEY, CHECK avancé et clés étrangères (ON DELETE CASCADE).~12 min
3. 9.3Modifier la structure : ALTER TABLEAjouter, modifier et supprimer des colonnes ou des contraintes de tables existantes.~10 min
4. 9.4Vues et abstraction des donnéesCréer des VIEWs pour simplifier les requêtes complexes et des MATERIALIZED VIEWs pour la performance.~12 min

L'art de rendre les requêtes rapides : index, plans d'exécution et optimisation de base de données.

1. 10.1Index et B-TreesCréer des index pour accélérer considérablement les requêtes de recherche sur de grandes tables.~15 min
2. 10.2EXPLAIN et ANALYZEApprendre à lire les plans d'exécution de la base de données pour diagnostiquer les goulots d'étranglement.~15 min
3. 10.3Index composites et partielsUtiliser des index sur plusieurs colonnes à la fois ou seulement sur une partie spécifique des données.~12 min
4. 10.4Le problème N+1L'infâme problème de performance ORM et les méthodes SQL natives pour le surmonter en une seule requête.~12 min

Gérer en toute sécurité les modifications de données simultanées : transactions ACID, verrous et niveaux d'isolement.

1. 11.1Transactions ACID (BEGIN/COMMIT)Garder les données cohérentes avec BEGIN, COMMIT et ROLLBACK, en regroupant les requêtes dans un seul bloc invisible pour les autres.~15 min
2. 11.2Conditions de concurrenceScénarios limites : ce qui se passe lorsque deux employés de base de données parallèles agissent sur le même panier d'achat.~10 min
3. 11.3Verrouillage et SELECT FOR UPDATEVerrouiller activement des lignes pendant les lectures, forçant les autres threads de mutation à attendre leur tour.~15 min
4. 11.4Niveaux d'isolementPousser les limites en implémentant Serializable, ou passer à Read Committed pour des performances non sécurisées mais rapides.~12 min

Automatiser la logique métier dans la base de données : écrire des fonctions réutilisables et réagir aux événements avec des déclencheurs.

1. 12.1UDFs et SQL de baseCréez vos propres fonctions définies par l'utilisateur pour encapsuler des calculs complexes et les réutiliser comme des macros dans chaque requête.~15 min
2. 12.2Logique avancée avec PL/pgSQLDonnez des super-pouvoirs à vos fonctions : variables, branches if-else et boucles en style procédural pur dans la base de données.~15 min
3. 12.3Introduction aux déclencheurs (triggers)Définir des fonctions spéciales qui peuvent inspecter instantanément l'enregistrement SORTANT (OLD) ou ENTRANT (NEW).~15 min
4. 12.4Triggers pratiques et auditAutomatiser la sécurité : mise à jour automatique de la date « updated_at » et archivage magique des lignes supprimées pour la conformité.~12 min

Analyse de données avancée sans réduire les lignes : découvrez OVER(), PARTITION BY et les fonctions analytiques.

1. 13.1Introduction et clause OVERExécuter des calculs d'agrégation (comme SUM ou AVG) tout en préservant les lignes individuelles d'origine.~15 min
2. 13.2Grouper avec PARTITION BYCréer des groupements logiques (fenêtres) dans les données auxquelles appliquer des fonctions, sans utiliser GROUP BY.~15 min
3. 13.3Classements : ROW_NUMBER, RANK et DENSE_RANKAttribuer des numéros de ligne séquentiels et des rangs aux résultats, en gérant correctement les égalités.~15 min
4. 13.4Décalages : LAG et LEADAccéder aux valeurs de la ligne précédente ou suivante directement depuis la ligne actuelle pour calculer les deltas et les variations.~12 min

Simplifier les requêtes volumineuses avec les expressions de table communes et naviguer dans les arbres de données illimités avec les CTE récursives.

1. 14.1Introduction aux CTEs (WITH)Remplacer les sous-requêtes imbriquées illisibles par un format procédural propre en déclarant des « tables virtuelles » temporaires.~12 min
2. 14.2CTEs multiples et enchaînéesMaîtriser la complexité en divisant le traitement en plusieurs étapes séquentielles dans une seule instruction WITH.~15 min
3. 14.3CTEs récursives : données sous forme d'arbreNaviguer sans limites dans les hiérarchies de gestionnaires et les catégories parent-enfant en appelant la CTE depuis elle-même.~18 min

Tirer parti de la puissance relationnelle combinée à un support de document flexible via JSON et JSONB sans utiliser de bases de données externes.

1. 15.1Intégration de JSON et JSONBStocker des documents complexes dans une colonne et interroger leurs champs avec des opérateurs fléchés.~15 min
2. 15.2Fonctions d'agrégation JSONGénérer des fichiers JSON ou des tableaux d'objets à la volée nativement en SQL à l'aide de json_build_object() et json_agg().~15 min