Requêtes avancées MySQL & mécanismes de transaction

  1. Requêtes de base avec WHERE

Clause WHERE avec conditions avancées

SELECT id_employe, nom_employe, salaire, date_embauche FROM personnel_temp
WHERE departement = 10 AND (salaire+IFNULL(commission,0)*12)>=15000 
AND DATEDIFF(NOW(),date_embauche)/365>=20;

Opérateurs utilisables dans WHERE

  • Opérateurs arithmétiques : + - * / %
  • Opérateurs de comparaison : > >= < <= = != IN IS NULL, IS NOT NULL, BETWEEN AND, LIKE , REGEXP
  • Opérateurs logiques : AND OR NOT XOR

Ordre d'exécution des différentes clauses : FROM → WHERE → SELECT → ORDER BY → LIMIT

Notes techniques concernant l'ordre des opérations

# Requête de base
SELECT * FROM nom_table;
SELECT id_employe, salaire*12 AS "revenu_annuel" FROM table_personnel;
SELECT * FROM table_tutoriel WHERE auteur = 'Tutoriel';
SELECT DISTINCT id_produit FROM nom_table; # Retourne des valeurs uniques
SELECT * FROM table ORDER BY DATE DESC LIMIT 1; # Le plus récent
SELECT * FROM table ORDER BY DATE DESC LIMIT 0,1; # Le plus récent (peut avoir un effet de déduplication)
SELECT * FROM table ORDER BY DATE DESC LIMIT 1 OFFSET 2; # Équivalent à LIMIT 2,1

# Opérateur UNION pour combiner résultats
SELECT pays FROM sites_web) UNION ALL (SELECT pays FROM applications ORDER BY pays); # Garde les doublons

# Mots-clés where BETWEEN AND OR IN ()
SELECT nom_produit,prix_produit FROM produits WHERE(fournisseur_id = 1002 OR fournisseur_id = 1003) AND prix_produit >=10;
SELECT nom_produit,prix_produit FROM produits WHERE fournisseur_id IN (1002,1003);
SELECT nom_produit,prix_produit FROM produits WHERE fournisseur_id NOT IN (1002,1003);

# Caractères génériques avec like
% représente plusieurs caractères, _ représente un caractère unique
SELECT nom_produit,prix_produit FROM produits WHERE fournisseur_id like '100_';
SELECT nom_produit,prix_produit FROM produits WHERE nom_produit like 'jet%';

# Expressions régulières REGEXP
SELECT nom_produit,prix_produit FROM produits WHERE nom_produit REGEXP '1000' ORDER BY nom_produit;
SELECT nom_produit,prix_produit FROM produits WHERE nom_produit REGEXP '1000|2000' ORDER BY nom_produit;
SELECT nom_produit,prix_produit FROM produits WHERE nom_produit REGEXP '[123]' ORDER BY nom_produit;
# Différence entre like et REGEXP : like correspond à tout le champ, \\ est l'antislash
  1. Requêtes avancées
Fonctions d'agrégation (ne peuvent pas être utilisées dans la clause WHERE)

SELECT COUNT(*) FROM employes WHERE date_embauche >="1985-01-01" AND salaire>MOYENNE(salaire); Cette requête ne peut pas s'exécuter

MOYENNE SOMME MAX MIN COUNT()

count(*) inclut les valeurs nulles

SELECT SUM(nom_employe) FROM personnel_temp;

Regroupement de données GROUP BY
  • Par défaut, les fonctions d'agrégation effectuent des statistiques sur l'ensemble de la table
  • La clause GROUP BY divise un ensemble de données en plusieurs petites régions, et effectue un traitement de résumé pour chaque région

SELECT departement,MOYENNE(salaire) FROM employes GROUP BY departement;

  • Si une instruction de requête contient une clause GROUP BY, la clause SELECT peut inclure des fonctions d'agrégation ou les colonnes de regroupement de GROUP BY, le reste ne peut pas apparaître dans la clause SELECT
Clause HAVING - Filtrage des groupes

WHERE filtre les lignes spécifiées, HAVING filtre après le regroupement des données

Exemple : Rechercher les numéros de département où le salaire moyen est supérieur à 2000

Mauvaise écriture : SELECT departement FROM employes WHERE MOYENNE(salaire) GROUP BY departement

Raison : WHERE précède GROUP BY, mais la base ne sait pas comment calculer MOYENNE

Bonne écriture : SELECT departement FROM emploeys GROUP BY departement HAVING MOYENNE(salaire) >=2000

Exemple 2 : Rechercher les numéros de département où plus de 2 employés ont été embauchés après 1982

SELECT departement FROM employes WHERE date_embauche >="1982-01-01" GROUP BY departement HAVING COUNT(*) >=2

5.2 Jointures de tables multiples

Spécification des conditions de jointure

drop table departements;
create table departements(
	id_departement int not null primary key,
	nom_departement varchar(50),
	localisation varchar(50));
	
	INSERT INTO departements
	(id_departement,nom_departement,localisation) values
	(20, "RECHERCHE", "DALLAS"),
	(30,"VENTES","CHICAGO"),
	(40,"OPERATIONS","BOSTON");

DROP table employes;

create table employes(
	id_employe int not null primary key,
	nom_employe varchar(50),
	poste varchar(50),
	id_superviseur int,
	date_embauche DATE not null,
	salaire FLOAT NOT NULL,
	commission FLOAT,
	id_departement int,
	FOREIGN KEY (id_departement) REFERENCES departements(id_departement));

INSERT INTO employes
(id_employe,nom_employe,poste,id_superviseur,date_embauche,salaire,commission,id_departement) values 
(7369,"SMITH","CLERK",7902,"1980-12-17",800.00,NULL,20),
(7499,"ALLEN","VANDEUR",7689,"1981-02-20",1600,300,30),
(7521,"WARD","VANDEUR",7689,"1981-02-22",1250,500,30),
(7566,"JONES","MANAGER",7839,"1981-04-02",2975,NULL,20),
(7654,"MARTIN","VANDEUR",7698,"1981-09-28",1250,1400,30),
(7698,"BLAKE","MANAGER",7839,"1981-05-01",2850,NULL,30),
(7782,"CLARK","MANAGER",7839,"1981-06-09",2450,NULL,10),
(7788,"SCOTT","ANALYSTE",7566,"1982-12-09",3000,NULL,20),
(7839,"ROI","PRESIDENT",NULL,"1981-11-17",5000,0,30),
(7844,"TURNER","VANDEUR",7698,"1981-09-08",1500,0,30),
(7876,"ADAMS","CLERK",7788,"1983-01-12",1100,NULL,20),
(7900,"JAMES","CLERK",7698,"1981-12-03",950,NULL,30),
(7902,"FORD","ANALYSTE",7566,"1981-12-03",3000,NULL,20),
(7934,"MILLER","CLERK",7782,"1982-01-23",1300,NULL,10);

CREATE table niveau_salaire (
	niveau INT PRIMARY KEY,
	salaire_min DOUBLE,
	salaire_max DOUBLE
    
);

INSERT INTO niveau_salaire (niveau,salaire_min,salaire_max) VALUES 
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);

## Requêtes avancées

#Fonctions d'agrégation
SELECT MOYENNE(salaire+IFNULL(commission,0)) as moyenne FROM employes;
SELECT SUM(salaire) FROM employes WHERE id_departement IN (10,20);
SELECT MAX(salaire + IFNULL(commission,0)) FROM employes WHERE id_departement IN(10,20);
SELECT MAX(LENGTH(nom_employe)) FROM employes;
SELECT MIN(id_employe) FROM employes;
# Différence entre les deux
SELECT COUNT(*),COUNT(commission) FROM employes; 

# Rechercher le nombre d'employés dans les départements 10 et 20 avec un salaire > 2000 et une ancienneté > 15 ans
SELECT COUNT(*)
FROM employes
WHERE id_departement IN(10,20) AND salaire>=2000 AND DATEDIFF(NOW(),date_embauche)/365>=15;
# Rechercher le nombre d'employés embauchés après 1985 avec un salaire > la moyenne de l'entreprise
SELECT COUNT(*) FROM employes WHERE date_embauche >="1985-01-01" AND salaire>MOYENNE(salaire)# Erreur, les fonctions d'agrégation ne peuvent pas être dans WHERE

# Regroupement de données
SELECT id_departement,ROUND(MOYENNE(salaire + IFNULL(commission,0))) 
FROM employes
GROUP BY id_departement;
# Regroupement progressif
# Rechercher pour chaque département et chaque poste le nombre d'employés et le salaire moyen
SELECT id_departement,poste,COUNT(*),ROUND(MOYENNE(salaire)) 
FROM employes
GROUP BY id_departement,poste
ORDER BY id_departement;
# WITH ROLLUP pour agréger les résultats
SELECT id_departement,COUNT(*),ROUND(MOYENNE(salaire)) 
FROM employes
GROUP BY id_departement WITH ROLLUP;

# Fonction GROUP_CONCAT pour concaténer les champs d'un groupe
SELECT id_departement,GROUP_CONCAT(nom_employe),COUNT(*)
FROM employes WHERE salaire>=2000 GROUP BY id_departement;
# Ordre d'exécution des clauses : FROM ->WHERE ->GROUP BY->SELECT->ORDER BY->LIMIT

# Clause HAVING
# Rechercher les départements avec un salaire moyen > 2000
SELECT id_departement FROM employes GROUP BY id_departement HAVING MOYENNE(salaire) >=2000;
# Rechercher les départements avec plus de 2 employés embauchés après 1982
SELECT id_departement
FROM employes
WHERE date_embauche>="1982-01-01"
GROUP BY id_departement HAVING COUNT(*)>=2
ORDER BY id_departement;
# Regroupement par colonne numérique
SELECT id_departement,COUNT(*)
FROM employes
GROUP BY 1 HAVING id_departement IN(10,30);



# Jointures (JOIN)
# Extraction de données de plusieurs tables, doit spécifier les conditions de jointure
# Par défaut, jointure interne, peut utiliser "," à la place de join, where à la place de on
SELECT e.id_employe,e.nom_employe,d.nom_departement
FROM employes e JOIN departements d
ON e.id_departement=d.id_departement;
# Exercice de jointure interne 1
# Rechercher pour chaque employé : ID, nom, département, salaire, poste, niveau salarial
SELECT id_employe,nom_employe,nom_departement,salaire,poste,niveau
FROM employes e JOIN departements d ON e.id_departement=d.id_departement
JOIN niveau_salaire s ON e.salaire BETWEEN s.salaire_min AND s.salaire_max 

# Exercice de jointure interne 2
# Rechercher les collègues de "SCOTT"
# Méthode peu efficace
SELECT nom_employe
FROM employes
WHERE id_departement=(SELECT id_departement FROM employes WHERE nom_employe="SCOTT")
AND nom_employe!="SCOTT";
# Méthode efficace
SELECT e2.nom_employe
FROM employes e1 JOIN employes e2 ON e1.id_departement=e2.id_departement
WHERE e1.nom_employe="SCOTT" AND e2.nom_employe!="SCOTT";

# Exercice de jointure interne 3
# Rechercher les employés avec un salaire > la moyenne de l'entreprise
SELECT e.id_employe,e.nom_employe,e.salaire
FROM employes e JOIN 
(SELECT MOYENNE(salaire) moyenne FROM employes) t ON e.salaire>t.moyenne; # Erreur

# Exercice de jointure interne 4
# Rechercher pour le département "RECHERCHE" : nombre d'employés, salaire max/min/moyen, ancienneté moyenne
SELECT COUNT(e.nom_employe),MAX(e.salaire),MIN(e.salaire),MOYENNE(e.salaire),FLOOR(MOYENNE(DATEDIFF(NOW(),e.date_embauche)/365))
FROM employes e JOIN departements d on e.id_departement=d.id_departement
WHERE d.nom_departement="RECHERCHE";

# Rechercher pour chaque poste : salaire max/min/moyen, niveau salarial max/min
SELECT e.poste,MAX(e.salaire + IFNULL(e.commission,0)),MIN(e.salaire + IFNULL(e.commission,0)),MOYENNE(e.salaire + IFNULL(e.commission,0)),MAX(s.niveau),MIN(s.niveau)
FROM employes e JOIN niveau_salaire s ON e.salaire BETWEEN s.salaire_min AND s.salaire_max
GROUP BY e.poste

# Rechercher chaque employé avec un salaire > la moyenne de son département
SELECT e.nom_employe
FROM employes e JOIN
(SELECT id_departement,MOYENNE(salaire) as moyenne FROM employes GROUP BY id_departement) ad on e.id_departement=ad.id_departement AND e.salaire>=ad.moyenne;

# Jointure externe : conserve les enregistrements qui ne correspondent pas
INSERT INTO employes (id_employe,nom_employe,poste,id_superviseur,date_embauche,salaire,commission,id_departement) VALUES (8000,"CHEN","VANDEUR",7698,"1982-07-19",1500.00,NULL,NULL)
# Jointure externe gauche : conserve tous les enregistrements de la table de gauche
SELECT e.id_employe,e.nom_employe,d.nom_departement
FROM employes e LEFT JOIN departements d ON e.id_departement=d.id_departement;

# Exercice de jointure externe 1
# Rechercher le nom de chaque département et le nombre d'employés
SELECT nom_departement,count(e.id_departement)
FROM departements d LEFT JOIN employes e ON d.id_departement=e.id_departement
GROUP BY e.id_departement;
# Alternative pour les départements sans employés
(SELECT nom_departement,count(e.id_departement)
FROM departements d LEFT JOIN employes e ON d.id_departement=e.id_departement
GROUP BY e.id_departement) UNION 
(SELECT nom_departement,count(*)
FROM departements d RIGHT JOIN employes e ON d.id_departement=e.id_departement
GROUP BY d.id_departement)


# Rechercher pour chaque employé : ID, nom, département, salaire mensuel, niveau salarial, ancienneté, ID/nom/département du superviseur

SELECT 
e.id_employe,e.nom_employe,d.nom_departement,e.salaire+IFNULL(e.commission,0),s.niveau,
FLOOR(DATEDIFF(NOW(),e.date_embauche)/365),t.id_employe,t.nom_employe,t.nom_departement
FROM employes e LEFT JOIN departements d ON e.id_departement=d.id_departement
LEFT JOIN niveau_salaire s ON e.salaire BETWEEN s.salaire_min AND s.salaire_max
LEFT JOIN 
(SELECT e1.id_employe,e1.nom_employe,d1.nom_departement
FROM employes e1 JOIN departements d1 ON e1.id_departement=d1.id_departement) t ON e.id_superviseur=t.id_employe;

# Sous-requêtes : peuvent être utilisées dans where, from, select, from recommandé
# Comparaison entre where et from pour les employés avec salaire > moyenne
SELECT id_employe,nom_employe,salaire FROM employes WHERE salaire>=(SELECT MOYENNE(salaire) FROM employes);
SELECT e.id_employe,e.nom_employe,e.salaire,t.moyenne
FROM employes e JOIN (SELECT id_departement,MOYENNE(salaire) as moyenne 
FROM employes GROUP BY id_departement) t ON e.id_departement=t.id_departement AND e.salaire>=t.moyenne;
# Les jointures sont généralement plus efficaces

# Sous-requête mono-ligne (un seul résultat) et multi-lignes (plusieurs résultats)
# Exemple : trouver les collègues de "FORD" et "MARTIN"
SELECT nom_employe
FROM employes WHERE 
id_departement IN (SELECT id_departement FROM employes WHERE nom_employe IN ("FORD","MARTIN")) AND nom_employe NOT IN ("FORD","MARTIN");
# Dans WHERE, on peut utiliser IN, All, ANY, EXISTS pour gérer les résultats multi-lignes
# Rechercher les employés avec un salaire > celui de "FORD" et "MARTIN"
SELECT nom_employe
FROM employes WHERE 
salaire > ALL (SELECT salaire FROM employes WHERE nom_employe IN ("FORD","MARTIN"));

# Exemple avec EXISTS : trouver les employés avec un niveau salarial de 3 ou 4
SELECT nom_employe
FROM employes 
WHERE EXISTS(
SELECT * FROM niveau_salaire
WHERE salaire BETWEEN salaire_min AND salaire_max AND niveau IN (3,4)
);
  1. Synthèse des requêtes

Ordre de syntaxe MySQL

  1. select[distinct]
  2. from
  3. join (ex: left join)
  4. on
  5. where
  6. group by
  7. having
  8. union
  9. order by
  10. limit

Ordre d'exécution MySQL

  1. from
  2. on
  3. join
  4. where
  5. group by
  6. having
  7. select
  8. distinct
  9. union
  10. order by
  11. Mécanismes de transaction

Contenu détaillé dans "MySQL Must Know" chapitre 26

Toutes les instructions ci-dessus s'exécutent directement sur la base de données, mais en cas de panne ou d'erreur, cela peut entraîner des modifications partielles. Pour éviter l'écriture directe sur les fichiers de données, on utilise le mécanisme de transaction pour garantir que soit toutes les modifications sont appliquées, soit aucune (avec un retour en arrière).

Un transaction est un mécanisme qui gère un ensemble d'opérations MySQL qui doivent être exécutées ensemble, pour garantir que la base de données ne contient pas de résultats d'opérations incomplètes. Avec les transactions, on garantit qu'un ensemble d'opérations ne s'arrête pas en cours de route : elles s'exécutent entièrement ou pas du tout (sauf instruction contraire). S'il n'y a pas d'erreur, l'ensemble des instructions est validé (écrit) dans les tables de la base de données. S'il y a une erreur, on effectue un retour en arrière pour restaurer la base de données à un état connu et sécurisé. Référence : "MySQL Must Know"

À partir de MySQL 5.0, le mécanisme de transaction a été introduit

  1. Journaux undo et redo, démarrage de transaction (start transaction), validation (commit), annulation (rollback)

Utilisation des journaux pour une écriture indirecte

Les fichiers journaux sont des copies des fichiers de données. Le journal undo copie les données, le journal redo enregistre les modifications. On essaie d'abord de modifier dans le journal redo, et si tout va bien, on synchronise avec les données. En cas de problème, après redémarrage de la base, on resynchronise.

RDBMS = instructions SQL + transactions (ACID)

  1. Gestion manuelle des transactions
START TRANSACTION;
N'importe quel nombre d'instructions SQL;
[COMMIT|ROLLBACK];
  1. Propriétés ACID des transactions

Atomicité : Toutes les opérations d'une transaction sont soit toutes complétées, soit toutes annulées. Une transaction ne peut pas rester dans un état intermédiaire.

Cohérence : Peu importe le nombre de transactions concurrentes, le résultat doit toujours être cohérent.

Isolation : Une transaction ne doit pas être affectée par d'autres transactions concurrentes, comme si elle était la seule transaction en cours d'exécution dans la base. Par défaut, une transaction ne peut voir que les données de sa propre transaction dans le journal.

Durabilité : Une fois une transaction validée, son résultat est permanent. Même en cas de panne, les journaux de transaction permettent de persister les données.

Niveaux d'isolation pour les transactions concurrentes

Différents niveaux d'isolation peuvent être appliqués selon les besoins spécifiques

  1. READ UNCOMMITTED : la transaction en cours peut lire les données temporaires non validées d'autres transactions. Scénario : achat de billet de train
SET SESSION TRANSACTION ISOLATION LEVEL 
READ UNCOMMITTED;

READ COMMITTED : Scénario : vérification du solde après un transfert d'argent

REPEATABLE READ : Scénario : le prix d'un produit ne change pas pendant une transaction en cours (comme dans un panier d'achat). C'est le niveau par défaut de MySQL.

SERIALIZABLE : Sacrifie la concurrence de la base de données.

Étiquettes: MySQL requêtes avancées transactions jointures Sous-requêtes

Publié le 10 juillet à 02h34

Étiquettes Populaires

©2026 L'Atelier Monstre