Cours Le langage SQL en PDF (Intermédiaire)

Qu'est-ce que le Structured Query Language (SQL) ?

Le langage SQL : Ce qu'il faut savoir. SQL (Structured Query Language) est le langage standard for définir, manipuler et contrôler des bases de données relationnelles. Il permet de créer des schémas, d'indexer l'information, d'écrire des requêtes pour extraire ou modifier des données et de garantir l'intégrité via des transactions ; ces fonctions sont indispensables dans tout projet logiciel traitant de volumes structurés. Support pédagogique issu d'un cours IUT, ce document est disponible au format PDF gratuit et contient des exemples exploitables et des TP pour s'exercer.

Le SQL est un langage déclaratif : on décrit le résultat attendu plutôt que la suite d'opérations impératives à exécuter. Cette approche facilite l'optimisation automatique par le SGBDR et met l'accent sur la description des contraintes et des intentions de requête.

Selon la norme ANSI/ISO, SQL est le langage standard pour manipuler des données au sein de systèmes de gestion de bases de données relationnelles.

De l'algèbre relationnelle au langage SQL

Le modèle relationnel s'exprime d'abord avec des opérateurs d'algèbre relationnelle ; SQL traduit ces opérateurs en clauses exploitables par le SGBD. Comprendre cette correspondance permet d'anticiper la complexité algorithmique et d'interpréter les plans d'exécution.

• σ (sigma) → WHERE : sélection des tuples répondant à une condition. Interprétation simple : σ_condition(R) renvoie l'ensemble des lignes de R satisfaisant la condition.
• π (pi) → SELECT : projection des colonnes souhaitées. π_colonnes(R) renvoie uniquement les attributs indiqués, en supprimant les doublons si nécessaire.
• ⋈ (join) → JOIN : combinaison de relations selon une condition d'égalité ou autre ; correspond aux jointures internes et externes en SQL.
• ∪ (union) → UNION : fusion d'ensembles compatibles, utile pour agréger des résultats provenant de requêtes distinctes.
• − (différence) → opérations de différence entre ensembles ; s'exprime parfois via EXCEPT ou des constructions équivalentes selon le SGBDR.
• × (produit cartésien) → cross join ou absence de condition de jointure, produit de toutes les paires de tuples entre relations.

Fondamentaux et algèbre relationnelle en SQL

Le modèle relationnel repose sur des opérations (sélection, projection, jointure, union, différence, produit cartésien) qui définissent formellement le traitement des ensembles de données. La correspondance entre symboles d'algèbre relationnelle et clauses SQL aide à écrire des requêtes optimisées et à interpréter les plans d'exécution produits par le SGBDR.

Langage de définition des données (LDD)

Le LDD couvre la création et la modification des objets de schéma : CREATE DATABASE, CREATE TABLE, ALTER, ainsi que les options d'encodage (CHARSET, COLLATE). Les choix d'encodage et de moteur influent sur la portabilité, les performances et l'intégrité. Rédiger des scripts de définition adaptés à MySQL 8.0+ ou PostgreSQL 16 permet d'assurer une compatibilité raisonnable avec la norme ANSI/ISO tout en tenant compte des extensions propres à chaque SGBDR.

Langage de manipulation des données (LMD)

Le LMD comprend les opérations de lecture et de modification : SELECT, INSERT, UPDATE, DELETE. Écrire des requêtes performantes nécessite des filtres appropriés (WHERE), des tris (ORDER BY), et une compréhension du coût des opérations fréquentes. Les bonnes pratiques incluent l'utilisation d'indexes pertinents, la limitation des résultats et l'utilisation de requêtes préparées côté serveur pour les entrées utilisateur.

Jointures et cas pratiques

Les jointures permettent de combiner des données réparties sur plusieurs tables. Les formes courantes sont INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL OUTER JOIN (support natif selon SGBDR). Choisir la jointure adaptée évite des lectures inutiles et améliore les performances lorsque des index sont présents sur les colonnes de jointure.

Le cas de la jointure FULL OUTER JOIN

Le FULL OUTER JOIN renvoie toutes les lignes des deux tables concernées : lorsqu'il n'existe pas de correspondance, les colonnes manquantes sont nulles. Utile pour comparer deux jeux de données et détecter les éléments exclusifs à l'une ou l'autre table. Certains SGBDR (comme PostgreSQL) supportent FULL JOIN nativement, d'autres exigent une combinaison de LEFT JOIN, RIGHT JOIN et UNION.

-- Exemple en PostgreSQL
SELECT a.id, a.col, b.col
FROM table_a a
FULL JOIN table_b b ON a.id = b.id;

Groupements, agrégats et HAVING

Les fonctions d'agrégation (SUM, COUNT, AVG, MIN, MAX) combinées à GROUP BY permettent de résumer des ensembles. La clause HAVING filtre les groupes après agrégation. Comprendre le comportement des agrégats et l'impact des index est essentiel pour obtenir des rapports performants et corrects.

Transactions et intégrité

Les transactions (BEGIN, COMMIT, ROLLBACK) garantissent la cohérence des opérations concurrentes. L'utilisation d'un moteur transactionnel (InnoDB pour MySQL 8.0+ ou PostgreSQL 16) fournit les garanties ACID nécessaires aux applications critiques. Tester les verrous et observer les effets des transactions sur des jeux de données concrets permet de choisir des niveaux d'isolation adaptés aux besoins fonctionnels.

Introduction aux extensions procédurales (PL/SQL)

Les SGBDR proposent des langages procéduraux intégrés (PL/SQL chez Oracle, PL/pgSQL chez PostgreSQL) pour définir procédures stockées, fonctions, triggers et blocs anonymes. Ces extensions ajoutent contrôle de flux, variables et exceptions, et permettent de déplacer une partie de la logique métier côté serveur pour optimiser les échanges réseau et garantir des transactions locales atomiques.

🎯 Ce que vous allez apprendre

• Langage de définition des données (LDD) — création et modification des schémas, choix d'encodage et moteur.
• Types de tables et moteurs (MyISAM, InnoDB) — comparaison pratique, choix selon contraintes transactionnelles.
• Indexes et clés — bonnes pratiques d'indexation, index composite et impacts sur écritures.
• Langage de manipulation des données (LMD) — requêtes SELECT, INSERT, UPDATE, DELETE.
• Jointures avancées — INNER/LEFT/RIGHT/FULL JOIN et optimisation.
• Groupements et agrégats, transactions — GROUP BY, HAVING, gestion des transactions.

📑 Sommaire du document

• Introduction
• LDD
• LMD
• Jointures
• Agrégats
• Transactions
• PL/SQL

Sécurité — requêtes préparées (PDO) : l'utilisation de requêtes préparées via PDO (PHP Data Objects) empêche l'injection SQL en séparant le code SQL des paramètres. Les requêtes préparées améliorent la sécurité et offrent souvent un gain de performance pour des requêtes répétées ; privilégier cette technique pour toute entrée utilisateur traitée côté serveur.

Pourquoi le SQL est-il un langage indispensable ?

Le SQL reste central for toute application manipulant des données structurées car il combine portabilité standardisée et capacités d'optimisation par le SGBDR. Les entreprises s'appuient sur SQL pour garantir l'intégrité, réaliser des rapports agrégés et automatiser des traitements transverses. Sa simplicité déclarative permet de produire des requêtes expressives tout en délégant l'ordonnancement au moteur de base.

Différences entre SQL et les langages de programmation classiques

Contrairement aux langages procéduraux, SQL exprime des requêtes et contraintes de manière déclarative. Les langages applicatifs (Python, Java) restent nécessaires pour la logique métier, mais s'appuient sur SQL pour l'accès et la transformation efficace des données au niveau du SGBDR.

❓ Foire Aux Questions (FAQ)

Comment choisir entre InnoDB et MyISAM pour une application web ?

InnoDB assure le support des transactions, le verrouillage au niveau de la ligne et l'intégrité référentielle, ce qui le rend adapté aux applications nécessitant ACID ; MyISAM peut offrir des lectures plus rapides en l'absence de contraintes transactionnelles, mais n'assure pas de COMMIT/ROLLBACK ni les verrous fins.

Quand ajouter un index sur une colonne plutôt que laisser une recherche séquentielle ?

Un index doit être privilégié si la colonne a une haute sélectivité et intervient fréquemment dans les clauses WHERE ou les JOIN. Chaque index augmente le coût des opérations d'écriture, il convient donc d'équilibrer fréquence des lectures vs écritures et d'envisager des index composites pour des filtres combinés fréquents.

PL/SQL / Langages procéduraux

L'ajout de procédures stockées et de triggers permet d'exécuter des validations et traitements au sein du SGBDR. Les procédures réduisent le trafic réseau et centralisent la logique critique ; les triggers automatisent des contrôles d'intégrité en réponse à des événements DML. Le cours présente des exemples de création et d'appel de procédures stockées, ainsi que des stratégies de test et de débogage.

Comparaison : MySQL, PostgreSQL, Oracle

SGBD propriétaires vs Open Source

Les SGBD propriétaires proposent des extensions propriétaires (PL/SQL, packages, outils d'administration avancés) et des optimisations spécifiques ; les SGBDR open source (MySQL, PostgreSQL) offrent une large communauté, transparence et extensibilité. La différence principale porte sur les extensions et l'écosystème plutôt que sur les fondamentaux du langage.

Outils recommandés

• MySQL — serveur relationnel couramment utilisé en TP et production.
• PostgreSQL — choix recommandé pour conformité SQL et fonctionnalités avancées (transactions, types, fonctions).
• SQLite — moteur embarqué léger, pratique pour tester des requêtes et petits exercices locaux.

Ressources complémentaires

• MySQL Workbench — modélisation et administration.
• Looping — outil de modélisation (diagrammes).
• pgAdmin — administration PostgreSQL.
• DBeaver — client multi-SGBD pour requêtes et exploration.

Exemples de TP et exercices SQL inclus

Exemple de TP inclus

TP proposé : gestion d'une bibliothèque. Objectifs : concevoir le schéma relationnel (tables livres, auteurs, emprunts), appliquer l'algèbre relationnelle pour formaliser les requêtes, écrire des requêtes SQL conformes au standard ANSI/ISO pour lister les livres empruntés, identifier les retardataires et calculer des statistiques. Les exercices corrigés SQL incluent scripts de création, jeux de données, requêtes d'agrégation et tests de transactions sur un SGBDR local. Ce TP illustre l'utilisation d'indexes, de transactions et de contraintes d'intégrité pour garantir la cohérence des opérations.

Installation d'un environnement de test (MySQL/PostgreSQL)

Installer un environnement de test peut se faire via paquets natifs, gestionnaires de paquets ou conteneurs Docker. Pour des essais rapides, Docker permet d'exécuter MySQL 8.0+ ou PostgreSQL 16 sans perturber le système hôte : récupérer l'image officielle, démarrer un conteneur, puis se connecter via un client (DBeaver, psql). Pour un usage pédagogique, SQLite est une alternative légère. La configuration inclut la création d'un utilisateur, le réglage d'encodage UTF-8 et l'activation des options de journalisation pour observer les plans d'exécution.