Skip to content

Requêtes SQL

Introduction

SQL (Structured Query Language) est le langage standard pour interagir avec les bases de données relationnelles.

Types de commandes SQL

  • DDL (Data Definition Language) : CREATE, ALTER, DROP
  • DML (Data Manipulation Language) : SELECT, INSERT, UPDATE, DELETE
  • DCL (Data Control Language) : GRANT, REVOKE
  • TCL (Transaction Control Language) : COMMIT, ROLLBACK

Structure de base d'une requête SELECT

SELECT colonnes
FROM table
WHERE conditions
GROUP BY colonnes
HAVING conditions_groupe
ORDER BY colonnes
LIMIT nombre;

Requêtes de base

SELECT - Sélection de données

Sélection simple

-- Toutes les colonnes
SELECT * FROM employes;

-- Colonnes spécifiques
SELECT nom, prenom, salaire FROM employes;

-- Avec alias
SELECT nom AS nom_famille, prenom AS prenom_employe FROM employes;

Clause WHERE - Filtrage

-- Condition simple
SELECT * FROM employes WHERE salaire > 3000;

-- Conditions multiples
SELECT * FROM employes WHERE salaire > 3000 AND age < 30;

-- Opérateurs de comparaison
SELECT * FROM employes WHERE nom = 'Dupont';
SELECT * FROM employes WHERE salaire BETWEEN 2000 AND 4000;
SELECT * FROM employes WHERE nom IN ('Dupont', 'Martin', 'Durand');
SELECT * FROM employes WHERE nom LIKE 'Dup%';  -- Commence par "Dup"
SELECT * FROM employes WHERE email LIKE '%@gmail.com';  -- Finit par "@gmail.com"

Tri avec ORDER BY

-- Tri croissant
SELECT * FROM employes ORDER BY salaire;

-- Tri décroissant
SELECT * FROM employes ORDER BY salaire DESC;

-- Tri multiple
SELECT * FROM employes ORDER BY departement, salaire DESC;

Limitation avec LIMIT

-- Les 10 premiers résultats
SELECT * FROM employes LIMIT 10;

-- Pagination (OFFSET)
SELECT * FROM employes LIMIT 10 OFFSET 20;  -- Résultats 21 à 30

INSERT - Insertion de données

-- Insertion simple
INSERT INTO employes (nom, prenom, salaire, departement)
VALUES ('Dupont', 'Jean', 3500, 'IT');

-- Insertion multiple
INSERT INTO employes (nom, prenom, salaire, departement)
VALUES 
    ('Martin', 'Marie', 3200, 'RH'),
    ('Durand', 'Pierre', 4000, 'Finance'),
    ('Moreau', 'Sophie', 3800, 'IT');

-- Insertion depuis une autre table
INSERT INTO employes_archive
SELECT * FROM employes WHERE date_fin IS NOT NULL;

UPDATE - Modification de données

-- Modification simple
UPDATE employes 
SET salaire = 3600 
WHERE nom = 'Dupont';

-- Modification multiple
UPDATE employes 
SET salaire = salaire * 1.1, 
    date_modification = NOW()
WHERE departement = 'IT';

-- Modification conditionnelle
UPDATE employes 
SET statut = 'Senior'
WHERE experience > 5 AND salaire > 4000;

DELETE - Suppression de données

-- Suppression avec condition
DELETE FROM employes WHERE nom = 'Dupont';

-- Suppression multiple
DELETE FROM employes WHERE date_fin < '2020-01-01';

-- Suppression de toutes les données (attention !)
DELETE FROM employes;

-- Suppression avec jointure
DELETE e FROM employes e
JOIN departements d ON e.dept_id = d.id
WHERE d.nom = 'Marketing';

Fonctions d'agrégation

Fonctions de base

-- Comptage
SELECT COUNT(*) FROM employes;
SELECT COUNT(DISTINCT departement) FROM employes;

-- Somme
SELECT SUM(salaire) FROM employes;

-- Moyenne
SELECT AVG(salaire) FROM employes;

-- Min/Max
SELECT MIN(salaire), MAX(salaire) FROM employes;

-- Avec GROUP BY
SELECT departement, COUNT(*), AVG(salaire)
FROM employes
GROUP BY departement;

GROUP BY et HAVING

-- Groupement simple
SELECT departement, COUNT(*) as nb_employes
FROM employes
GROUP BY departement;

-- Filtrage des groupes avec HAVING
SELECT departement, AVG(salaire) as salaire_moyen
FROM employes
GROUP BY departement
HAVING AVG(salaire) > 3500;

-- Groupement multiple
SELECT departement, poste, COUNT(*), AVG(salaire)
FROM employes
GROUP BY departement, poste
ORDER BY departement, poste;

Jointures

Types de jointures

INNER JOIN - Jointure interne

SELECT e.nom, e.prenom, d.nom as departement
FROM employes e
INNER JOIN departements d ON e.dept_id = d.id;

LEFT JOIN - Jointure externe gauche

-- Tous les employés, même sans département
SELECT e.nom, e.prenom, d.nom as departement
FROM employes e
LEFT JOIN departements d ON e.dept_id = d.id;

RIGHT JOIN - Jointure externe droite

-- Tous les départements, même sans employés
SELECT e.nom, e.prenom, d.nom as departement
FROM employes e
RIGHT JOIN departements d ON e.dept_id = d.id;

FULL OUTER JOIN - Jointure externe complète

-- Tous les employés ET tous les départements
SELECT e.nom, e.prenom, d.nom as departement
FROM employes e
FULL OUTER JOIN departements d ON e.dept_id = d.id;

Jointures multiples

SELECT e.nom, e.prenom, d.nom as departement, p.titre as projet
FROM employes e
INNER JOIN departements d ON e.dept_id = d.id
INNER JOIN affectations a ON e.id = a.employe_id
INNER JOIN projets p ON a.projet_id = p.id;

Auto-jointure

-- Employés et leurs managers
SELECT e.nom as employe, m.nom as manager
FROM employes e
LEFT JOIN employes m ON e.manager_id = m.id;

Sous-requêtes

Sous-requête dans WHERE

-- Employés avec salaire supérieur à la moyenne
SELECT nom, prenom, salaire
FROM employes
WHERE salaire > (SELECT AVG(salaire) FROM employes);

-- Employés du département IT
SELECT nom, prenom
FROM employes
WHERE dept_id IN (SELECT id FROM departements WHERE nom = 'IT');

Sous-requête dans SELECT

SELECT nom, prenom, salaire,
       (SELECT AVG(salaire) FROM employes) as salaire_moyen
FROM employes;

Sous-requête corrélée

-- Employés avec le salaire le plus élevé de leur département
SELECT nom, prenom, salaire, departement
FROM employes e1
WHERE salaire = (
    SELECT MAX(salaire)
    FROM employes e2
    WHERE e2.departement = e1.departement
);

EXISTS et NOT EXISTS

-- Départements ayant des employés
SELECT nom FROM departements d
WHERE EXISTS (SELECT 1 FROM employes e WHERE e.dept_id = d.id);

-- Départements sans employés
SELECT nom FROM departements d
WHERE NOT EXISTS (SELECT 1 FROM employes e WHERE e.dept_id = d.id);

Fonctions de chaînes

-- Concaténation
SELECT CONCAT(prenom, ' ', nom) as nom_complet FROM employes;

-- Longueur
SELECT nom, LENGTH(nom) as longueur FROM employes;

-- Majuscules/Minuscules
SELECT UPPER(nom), LOWER(prenom) FROM employes;

-- Extraction
SELECT LEFT(nom, 3), RIGHT(nom, 2) FROM employes;
SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) as username FROM employes;

-- Remplacement
SELECT REPLACE(telephone, '-', '.') FROM employes;

-- Suppression d'espaces
SELECT TRIM(nom) FROM employes;

Fonctions de dates

-- Date actuelle
SELECT NOW(), CURDATE(), CURTIME();

-- Extraction de parties de date
SELECT YEAR(date_embauche), MONTH(date_embauche), DAY(date_embauche)
FROM employes;

-- Calculs de dates
SELECT nom, date_embauche, 
       DATEDIFF(NOW(), date_embauche) as jours_anciennete,
       DATE_ADD(date_embauche, INTERVAL 1 YEAR) as premiere_annee
FROM employes;

-- Formatage de dates
SELECT DATE_FORMAT(date_embauche, '%d/%m/%Y') as date_fr FROM employes;

Expressions conditionnelles

CASE WHEN

SELECT nom, salaire,
       CASE 
           WHEN salaire < 2500 THEN 'Junior'
           WHEN salaire BETWEEN 2500 AND 4000 THEN 'Confirmé'
           ELSE 'Senior'
       END as niveau
FROM employes;

IF (MySQL)

SELECT nom, IF(salaire > 3000, 'Élevé', 'Normal') as niveau_salaire
FROM employes;

COALESCE - Gestion des valeurs NULL

SELECT nom, COALESCE(telephone, 'Non renseigné') as contact
FROM employes;

Requêtes avancées

WITH (CTE - Common Table Expression)

WITH salaires_dept AS (
    SELECT departement, AVG(salaire) as salaire_moyen
    FROM employes
    GROUP BY departement
)
SELECT e.nom, e.salaire, s.salaire_moyen
FROM employes e
JOIN salaires_dept s ON e.departement = s.departement
WHERE e.salaire > s.salaire_moyen;

Fonctions de fenêtrage (Window Functions)

-- Rang par département
SELECT nom, departement, salaire,
       RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) as rang
FROM employes;

-- Numérotation
SELECT nom, salaire,
       ROW_NUMBER() OVER (ORDER BY salaire DESC) as numero
FROM employes;

-- Somme cumulative
SELECT nom, salaire,
       SUM(salaire) OVER (ORDER BY date_embauche) as salaire_cumule
FROM employes;

UNION - Combinaison de résultats

-- Union simple
SELECT nom, 'Employé' as type FROM employes
UNION
SELECT nom, 'Client' as type FROM clients;

-- Union avec tri
SELECT nom FROM employes
UNION ALL
SELECT nom FROM clients
ORDER BY nom;

Optimisation des requêtes

Index

-- Création d'index
CREATE INDEX idx_nom ON employes(nom);
CREATE INDEX idx_dept_salaire ON employes(departement, salaire);

-- Index unique
CREATE UNIQUE INDEX idx_email ON employes(email);

Analyse de performance

-- Plan d'exécution (MySQL)
EXPLAIN SELECT * FROM employes WHERE departement = 'IT';

-- Statistiques (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM employes WHERE departement = 'IT';

Bonnes pratiques

  • Utiliser des index sur les colonnes de jointure et de filtrage
  • Éviter SELECT * dans les requêtes de production
  • Utiliser LIMIT pour les requêtes de test
  • Préférer EXISTS à IN pour les sous-requêtes
  • Utiliser les jointures plutôt que les sous-requêtes quand possible

Transactions

-- Début de transaction
START TRANSACTION;

-- Opérations
UPDATE employes SET salaire = salaire * 1.1 WHERE departement = 'IT';
INSERT INTO historique_salaires (employe_id, ancien_salaire, nouveau_salaire, date_modification)
SELECT id, salaire / 1.1, salaire, NOW() FROM employes WHERE departement = 'IT';

-- Validation
COMMIT;

-- Ou annulation en cas d'erreur
-- ROLLBACK;

Exemple complet : Système de gestion d'employés

Structure des tables

-- Table départements
CREATE TABLE departements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(50) NOT NULL,
    budget DECIMAL(10,2)
);

-- Table employés
CREATE TABLE employes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salaire DECIMAL(8,2),
    date_embauche DATE,
    dept_id INT,
    manager_id INT,
    FOREIGN KEY (dept_id) REFERENCES departements(id),
    FOREIGN KEY (manager_id) REFERENCES employes(id)
);

Requêtes métier

-- 1. Employés par département avec salaire moyen
SELECT d.nom as departement, 
       COUNT(e.id) as nb_employes,
       AVG(e.salaire) as salaire_moyen,
       MAX(e.salaire) as salaire_max
FROM departements d
LEFT JOIN employes e ON d.id = e.dept_id
GROUP BY d.id, d.nom
ORDER BY salaire_moyen DESC;

-- 2. Top 5 des salaires les plus élevés
SELECT nom, prenom, salaire, 
       RANK() OVER (ORDER BY salaire DESC) as rang
FROM employes
ORDER BY salaire DESC
LIMIT 5;

-- 3. Employés embauchés cette année
SELECT nom, prenom, date_embauche
FROM employes
WHERE YEAR(date_embauche) = YEAR(NOW())
ORDER BY date_embauche DESC;

-- 4. Managers avec leur équipe
SELECT m.nom as manager, 
       COUNT(e.id) as taille_equipe,
       AVG(e.salaire) as salaire_moyen_equipe
FROM employes m
INNER JOIN employes e ON m.id = e.manager_id
GROUP BY m.id, m.nom
HAVING COUNT(e.id) > 2
ORDER BY taille_equipe DESC;

-- 5. Budget vs masse salariale par département
SELECT d.nom as departement,
       d.budget,
       COALESCE(SUM(e.salaire * 12), 0) as masse_salariale_annuelle,
       d.budget - COALESCE(SUM(e.salaire * 12), 0) as budget_restant
FROM departements d
LEFT JOIN employes e ON d.id = e.dept_id
GROUP BY d.id, d.nom, d.budget
ORDER BY budget_restant;

Sécurité

Injection SQL - Prévention

-- ❌ Vulnérable
SELECT * FROM employes WHERE nom = '" + userInput + "'

-- ✅ Sécurisé avec paramètres
SELECT * FROM employes WHERE nom = ?

Gestion des droits

-- Création d'utilisateur
CREATE USER 'lecteur'@'localhost' IDENTIFIED BY 'motdepasse';

-- Attribution de droits
GRANT SELECT ON entreprise.employes TO 'lecteur'@'localhost';
GRANT INSERT, UPDATE ON entreprise.employes TO 'editeur'@'localhost';

-- Révocation de droits
REVOKE INSERT ON entreprise.employes FROM 'editeur'@'localhost';