Cours Détaillé : Le Langage SQL (Structured Query Language)

Introduction

Le SQL est le langage standard et universel pour interagir avec les Systèmes de Gestion de Bases de Données Relationnelles (SGBDR) comme MySQL, PostgreSQL, Oracle ou SQL Server. Il se décompose en plusieurs sous-langages :

DDL (Data Definition Language) : Définir la structure (CREATE, ALTER, DROP).
DML (Data Manipulation Language) : Manipuler les données (INSERT, UPDATE, DELETE).
DQL (Data Query Language) : Interroger les données (SELECT).
TCL (Transaction Control Language) : Gérer les transactions (COMMIT, ROLLBACK).

I. Les Fondations du SQL (DDL & DML)

1. Création et Structure (DDL)

Avant d’insérer des données, il faut définir le contenant.

Créer une base de données :

CREATE DATABASE BaseEva;
USE BaseEva; -- Commande pour sélectionner la base active

Créer une table avec contraintes : Il est crucial de définir le type de données et les contraintes d’intégrité dès la création.

CREATE TABLE Utilisateurs (
    ID INT PRIMARY KEY AUTO_INCREMENT, -- Entier, clé primaire, auto-incrémenté
    Nom VARCHAR(50) NOT NULL,          -- Chaîne de 50 caractères max, ne peut être vide
    Age INT CHECK (Age >= 0),          -- Entier avec contrainte de valeur positive
    Email VARCHAR(100) UNIQUE          -- Chaîne unique (pas de doublons autorisés)
);

Supprimer une table ou une base :

DROP TABLE Utilisateurs; -- Supprime la table et ses données définitivement

2. Manipulation des données (DML)

Insertion de données : On peut insérer une ou plusieurs lignes.

INSERT INTO Utilisateurs (Nom, Age, Email)
VALUES ('Eva', 28, 'eva@example.com'),
       ('John', 35, 'john@example.com'); -- Insertion multiple

Mise à jour de données : Attention : Omettre la clause WHERE mettra à jour toutes les lignes de la table.

UPDATE Utilisateurs
SET Age = 29, Email = 'eva.new@example.com'
WHERE ID = 1; -- Toujours viser l'ID pour la précision

Suppression de données : Tout comme UPDATE, sans WHERE, tout est supprimé.

DELETE FROM Utilisateurs
WHERE ID = 1;

II. Interrogation des données (DQL)

La commande SELECT est la plus utilisée. Elle permet de lire les données.

1. Sélection simple et filtres

Sélection totale ou partielle :

SELECT Nom, Age FROM Utilisateurs;
SELECT * FROM Utilisateurs; -- L'astérisque signifie "toutes les colonnes"

Filtrage avec WHERE : Les opérateurs courants sont : =, <>, >, <, >=, <=, AND, OR, IN, BETWEEN, LIKE.

-- Trouver les utilisateurs entre 25 et 30 ans dont le nom commence par 'E'
SELECT * FROM Utilisateurs
WHERE Age BETWEEN 25 AND 30
  AND Nom LIKE 'E%'; -- '%' est un joker (wildcard) pour remplacer plusieurs caractères

Tri des résultats :

SELECT * FROM Utilisateurs
ORDER BY Age DESC, Nom ASC; -- Tri par âge décroissant, puis par nom croissant

2. Les Jointures (Concept clé)

Les jointures permettent de combiner des lignes de plusieurs tables basées sur une colonne liée (généralement une clé étrangère).

Prenons une seconde table Commandes :

CREATE TABLE Commandes (
    ID_Commande INT PRIMARY KEY,
    ID_Utilisateur INT,
    Produit VARCHAR(100),
    Montant DECIMAL(10, 2),
    FOREIGN KEY (ID_Utilisateur) REFERENCES Utilisateurs(ID)
);

INNER JOIN (Jointure interne) : Retourne les lignes quand il y a une correspondance dans les deux tables.

SELECT Utilisateurs.Nom, Commandes.Produit
FROM Utilisateurs
INNER JOIN Commandes ON Utilisateurs.ID = Commandes.ID_Utilisateur;
-- N'affichera que les utilisateurs qui ont passé une commande.

LEFT JOIN (Jointure externe gauche) : Retourne toutes les lignes de la table de gauche (Utilisateurs), et les correspondances de la table de droite (Commandes) si elles existent (sinon NULL).
- Utile pour lister tous les utilisateurs, même ceux qui n’ont rien commandé.

III. Les Fonctions d’Agrégation et Groupement

Ces fonctions opèrent sur un ensemble de valeurs pour retourner une valeur unique.

1. Fonctions de base

COUNT(*) : Nombre de lignes.
SUM(colonne) : Somme totale.
AVG(colonne) : Moyenne.
MAX(colonne) / MIN(colonne) : Extremums.

2. Le Groupement (`GROUP BY` et `HAVING`)

GROUP BY regroupe les lignes qui ont les mêmes valeurs. HAVING filtre ces groupes (équivalent du WHERE mais pour les groupes).

Exemple : Calculer le montant total des commandes par utilisateur, mais seulement pour ceux qui ont dépensé plus de 100€.

SELECT Utilisateurs.Nom, SUM(Commandes.Montant) AS Total_Depense
FROM Utilisateurs
JOIN Commandes ON Utilisateurs.ID = Commandes.ID_Utilisateur
GROUP BY Utilisateurs.Nom
HAVING SUM(Commandes.Montant) > 100;

IV. Gestion Avancée

1. Transactions (TCL)

Une transaction est un bloc d’opérations qui doit être exécuté comme un tout indivisible (Atomicité). Si une étape échoue, tout est annulé.

BEGIN TRANSACTION; -- ou START TRANSACTION;

-- Étape 1 : Débiter le compte de Pierre
UPDATE Comptes SET Solde = Solde - 500 WHERE ID = 1;

-- Étape 2 : Créditer le compte de Paul
UPDATE Comptes SET Solde = Solde + 500 WHERE ID = 2;

-- Si tout s'est bien passé
COMMIT; -- On valide les changements

-- S'il y a eu une erreur (ex: solde insuffisant détecté par le programme)
-- ROLLBACK; -- On annule tout retour à l'état initial

2. Index et Optimisation

Un index est comme l’index d’un livre : il permet de trouver une information sans lire tout le livre (table). Cela accélère les SELECT mais ralentit légèrement les INSERT/UPDATE.

CREATE INDEX idx_nom ON Utilisateurs (Nom);
-- Maintenant, rechercher par Nom est très rapide.

V. Travaux Dirigés (TP)

Objectif : Mettre en pratique les commandes sur la base BaseEva.

Script complet à exécuter :

Création du schéma :

CREATE DATABASE BaseEva;
USE BaseEva;

CREATE TABLE Utilisateurs (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Nom VARCHAR(50) NOT NULL,
    Age INT,
    Email VARCHAR(100) UNIQUE
);

CREATE INDEX idx_nom ON Utilisateurs(Nom);

Insertion des données :

INSERT INTO Utilisateurs (Nom, Age, Email) VALUES
('Eva', 28, 'eva@example.com'),
('John', 22, 'john@example.com'),
('Marie', 35, 'marie@example.com'),
('Pierre', 28, 'pierre@example.com');

Requêtes demandées :
- Mise à jour conditionnelle : Augmenter l’âge de 1 an pour ceux qui ont moins de 30 ans.
```
UPDATE Utilisateurs SET Age = Age + 1 WHERE Age < 30;
```
- Sélection ordonnée : Afficher les noms et âges triés par âge décroissant.
```
SELECT Nom, Age FROM Utilisateurs ORDER BY Age DESC;
```
- Agrégation : Calculer l’âge moyen des utilisateurs.
```
SELECT AVG(Age) AS Age_Moyen FROM Utilisateurs;
```
- Agrégation avec Groupement : Compter combien d’utilisateurs ont le même âge.
```
SELECT Age, COUNT(*) AS Nombre
FROM Utilisateurs
GROUP BY Age;
```

Gestion Transactionnelle :

Simulez une modification groupée et validez-la.

START TRANSACTION;
DELETE FROM Utilisateurs WHERE Nom = 'Pierre';
-- Vérifiez si la suppression est correcte
COMMIT; -- Si ok, sinon ROLLBACK;