MySQL : Requêtes SQL

1. Qu'est-ce qu'une requête SQL ?

Une requête SQL est un texte qui donne un ordre à exécuter à un moteur de base de données sur une base de données. Elle peut concerner un enregistrement ou plusieurs, elle peut mettre en jeu une table ou plusieurs. La requête est composée de mots clés, les commandes SQL (que j'écrirai arbitrairement en majuscules). Je reprécise également que les commandes SQL ne sont pas sensibles à la casse (différence majuscules/minuscules) ni à l'écriture mais les champs sont sensibles ; pour plus de clarté j'écrirai mes requêtes sur plusieurs lignes, permettant d'individualiser les blocs de données. les commandes SQL seront écrites en majuscules et les noms des champs intégralement en minuscules (sauf indication contraire).

2. Types de requêtes SQL

Une requête est une ligne de texte demandant une action à la base de données. Il y a 3 principales catégories de requêtes :

  1. La manipulation de données :
    • SELECT : extraire toutes les données (éventuellement correspondant à un ou plusieurs critères) ;
    • UPDATE : mettre à jour des données (éventuellement selon un ou plusieurs critères) ;
    • DELETE : supprimer des enregistrements (éventuellement selon un ou plusieurs critères) ;
    • INSERT : ajouter des enregistrements dans la base.
  2. La gestion de la structure de la base ou des tables, par exemple (liste non exhaustive) :
    • CREATE : créer une table ou une base ;
    • ALTER : altérer la structure d'une table (rajouter un champ ou in index par exemple) ;
    • DROP : supprimer une table ;
    • TRUNCATE : vider une table (mais ne supprime pas la table pour autant) ;
    • CREATE ROUTINE : créer une routine (suite d'instructions à exécuter de façon automatisée) ;
    • CREATE VIEW : créer une vue (table virtuelle, résultat de requête(s)) ;
    • TRIGGER : créer un déclencheur (permet d'exécuter une action, routine, etc. dès qu'un événement arrive : par exemple, vérification d'une date)
  3. La gestion de la base de données (administration) - liste non exhaustive :
    • GRANT : donner des droits à un utilisateur ;
    • CREATE USER : créer un utilisateur

3. Requêtes les plus usuelles : SELECT, INSERT, DELETE, UPDATE

Pour les exemples qui vont suivre, je supposerai cet exemple de base de données sur 2 tables :

-- TABLE : contacts
id     nom     prenom     ville     cp     id_pays
-----------------------------------------------------------
1      Alpha   Alain      Paris     75000  1
2      Beta    Benjamin   Lyon      69000  1
3      Beta    Benjamin   Lyon      69000  1
4      Gamma   Gaëlle     London    1234   2

-- TABLE : pays
id     pays
-----------
1      FRANCE
2      UNITED KINGDOM

Le SELECT : choix d'enregistrements

La requête SELECT permet de choisir des enregistrements suivant une certaine condition. Sa syntaxe est la suivante :

	SELECT
		champ1,
		champ2,
		... champX
	FROM
		nom_de_la_table
	WHERE
		{ condition };

Le SELECT retournera ainsi un jeu de données ("dataset") qui sera une sorte de table virtuelle contenant tous les enregistrements qui remplissent la condition (si existante) organisés selon les champs choisis.

ATTENTION : Les programmeurs du dimanche ont vite fait d'utiliser la syntaxe SELECT * FROM ... qui permet de rapatrier tous les champs. Très pratique pour être sûr d'avoir l'information désirée, le SELECT * est à éviter pour 3 raisons :

  1. Problème de poids : la plupart du temps, on n'a pas besoin de tous les champs, mais seulement des principaux. Si le résultat du SELECT retourne un enregistrement dont la totalité des champs pèse 5 kilo octets, imaginez alors si dans quelques jours cette réponse renvoie 1000 enregistrements ... Cela fait 5 mille kilo octets soit 5 mega octets ... Juste pour avoir l'ID et le prénom ...
  2. Problème de sécurité : supposons que notre enregistrement soit un contact et que figure dans sa "fiche" son numéro de compte, ou son mot de passe. Un SELECT * va rapatrier le numéro de compte ou le mot de passe, même s'il n'est pas utilisé dans le script ; l'information sera rapatriée en mémoire, donc potentiellement utilisable (imaginons - au pire - le cas d'une faille de sécurité ...)
  3. Problème d'évolutivité : (indirectement lié au problème de taille) aujourd'hui la table comporte 3 champs. Dans quelques jours, j'aurai ajouté 3 autres champs, ce qui en fera 6 au total. Or dans la partie du script où est fait ce SELECT *, je n'ai pas forcément besoin des 6 champs, dont je rapatrie de l'information en trop : consommation inutile de ressources.

Bref, j'espère que ces raisons vous auront convaincu de ne pas utiliser le SELECT *. Le SELECT permet de rapatrier les champs tels qu'indiqués, mais par simplicité, il est possible de les manipuler un peu. Voici quelques exemples (exemple simple pour le premier, pour comparer) :

	SELECT
		nom,
		prenom,
		ville,
		cp
	FROM
		contacts;

Renvoie :

nom     prenom     ville     cp
----------------------------------
Alpha   Alain      Paris     75000
Beta    Benjamin   Lyon      69000
Beta    Benjamin   Lyon      69000
Gamma   Gaëlle     London    1234

Exemple de renommage des champs à la volée :

	SELECT
		nom AS nom_de_famille,
		prenom AS prenom_contact,
		ville,
		cp
	FROM
		contacts;

Renvoie :

nom_de_famille     prenom_contact     ville     cp
----------------------------------------------------
Alpha              Alain              Paris     75000
Beta               Benjamin           Lyon      69000
Beta               Benjamin           Lyon      69000
Gamma              Gaëlle             London    1234

Exemple de concaténation :

	SELECT
		CONCAT('[ ', nom, '] ', prenom) AS employe,
		ville,
		cp
	FROM
		contacts;

Renvoie :

employe             ville     cp
------------------------------------
[ Alpha ] Alain     Paris     75000
[ Beta ] Benjamin   Lyon      69000
[ Beta ] Benjamin   Lyon      69000
[ Gamma ] Gaëlle    London    1234

On peut faire encore bien d'autres manipulations, la documentation de MySQL en donne beaucoup d'autres.

L'INSERT : ajout d'enregistrements

L'INSERT ajoute des valeurs suivant des champs. Il peut prendre 2 écritures :

	INSERT INTO
		la_table
	VALUES
		('valeur1', 'valeur2', ... 'valeurX');

-- ou bien

	INSERT INTO
		la_table
			(champ1, champ2, ... champX)
		VALUES
			('valeur1', 'valeur2', ... 'valeurX');

Si vous avez bien suivi les indications du SELECT, vous savez tout de suite quelle écriture est ma préférée... Il s'agit en effet de la seconde forme. La première forme dit "INSERT INTO la_table VALUES ('', '', '');" c.à.d. que l'on doit spécifier tous les champs dans l'ordre exact de la structure de la table.

  • Avantage : plus rapide à taper. (est-ce vraiment un avantage ?)
  • Inconvénients : j'en vois 2. Le premier : il ne faut pas faire d'erreur dans l'ordre des champs. Le second, plus grave, est pour l'évolutivté : si aujourd'hui ma table comporte 3 champs, alors la requête ressemblera à "INSERT INTO la_table VALUES ('', '', '');" mais si dans quelques jours je rajoute un quatrième champ (ou plus !) alors la requête retournera une erreur ... Oui, puisqu'elle ne correspondra plus à la structure de la table => d'importantes mises à jour de toutes les requêtes doivent alors être faites.

La seconde écriture, plus contraignante, donne ces avantages :

  • On spécifie l'ordre des champs, les valeurs doivent suivre cet ordre. Par exemple, on peut tout à fait écrire "INSERT INTO la_table (champ2, champX, champ1) VALUES ('valeur2', 'valeurX', 'valeur1');" donc il n'y a pas de souci quant à l'ordre des champs ;
  • Si aujourd'hui ma table a 3 champs et que dans quelques jours elle en a 5 de plus, il n'y aura aucun problème dans la requête, les champs manquants prendront leurs valeurs par défaut.

Bien entendu, chaque type d'écriture peut être utilisé, il n'y a pas plus de performance dans une forme que dans l'autre, mais pour des raisons évoquées, je préfère l'une à l'autre.

Le DELETE : suppression d'enregistrements

Cette commande supprime des enregistrements répondant à une condition. Elle ne renvoie pas de dataset.

Sa syntaxe est la suivante :

	DELETE FROM
		la_table
	WHERE
		{ condition };

Supposons mes contacts affichés plus haut. La requête qui correspond à effacer tous les contacts en France :

	DELETE FROM
		contacts
	WHERE
		ID_PAYS = 1;

Modifiera la table comme ceci :

-- TABLE : contacts
id     nom     prenom     ville     cp     id_pays
-----------------------------------------------------------
4      Gamma   Gaëlle     London    1234   2

L'UPDATE : mise à jour d'enregistrements

Mettre à jour un ou des enregistrements, c'est changer le contenu de certains champs. Pour effectuer la mise à jour, le moteur de la base de données va d'abord examiner la condition puis sélectionner les enregistrements qui la vérifient; ensuite il met à jour ceux-ci. Attention, si la condition est mal formulée, il est possible de mettre à jour beaucoup plus d'enregistrements que prévu ...

L'UPDATE a pour syntaxe :

	UPDATE
		la_table
	SET
		champ1 = 'nouvelle_valeur_1',
		champ2 = 'nouvelle_valeur_2',
		champX = 'nouvelle_valeur_X'
	WHERE
		{ CONDITION };

(On n'est pas obligé de mettre à jour tous les champs systématiquement). Je prends toujours mes contacts, je souhaite renommer la ville "Lyon" en "Toulouse", et changer le code postal vers "31000" :

	UPDATE
		contacts
	SET
		ville = 'Toulouse',
		cp = '31000'
	WHERE
		ville = 'Lyon';

Une fois exécutée, la table devient :

-- TABLE : contacts
id     nom     prenom     ville     cp     id_pays
-----------------------------------------------------------
1      Alpha   Alain      Paris     75000  1
2      Beta    Benjamin   Toulouse  31000  1
3      Beta    Benjamin   Toulouse  31000  1
4      Gamma   Gaëlle     London    1234   2

4. Les jointures

La jointure concerne 2 tables, c'est une sorte de raccordement réalisé pour relier les données. On peut distinguer 2 formes classiques de jointures, les jointures par produit cartésien et les jointures droites ou gauches.

Jointure par produit cartésien

La différence se fait sentir sur des grosses tables, en termes de performances. Réaliser le produit cartésien de 2 tables sera d'autant plus long et coûteux en ressources (charge CPU, mémoire) que les tables contiennent d'une part beaucoup d'enregistrements, et d'autre part, beaucoup de champs.

Prenons cette fois un exemple d'inscrits à des newsletters. Imaginons donc 4 newsletters différentes. Pour la première, 5 inscrits. Pour la seconde, 4 inscrits. Pour la 3ème, 3 inscrits et pour la dernière, 1 inscription.

-- TABLE : newsletters
id     titre
-----------------------------------------------------------
1      Les recettes de cuisine
2      Astuces pour le jardin
3      Conseils pour dépanner sa voiture
4      Le cours de la bourse

-- TABLE : inscriptions
id     id_newsletter   email_abonne
-----------------------------------
1      1               toto@toto.fr
2      1               titi@titi.fr
3      1               tata@tata.fr
4      1               tutu@tutu.fr
5      1               tete@tete.fr

6      2               aa@aa.fr
7      2               bb@bb.fr
8      2               cc@cc.fr
9      2               dd@dd.fr

10     3               leon@garage.com
11     3               president@renault.fr
12     3               president@peugeot.fr

13     4               georges@google.co.uk

Une requête SELECT avec produit cartésien réalise la jointure dans la clause "WHERE" :

	SELECT
		titre,
		COUNT(email_abonne) AS nombre_abonnes,
	FROM
		newsletters,
		inscriptions
	WHERE
		newsletters.id = inscriptions.id_newsletter;

Cette requête renvoie le dataset suivant :

titre                               nombre_abonnes
-----------------------------------------------------------
Les recettes de cuisine             5
Astuces pour le jardin              4
Conseils pour dépanner sa voiture   3
Le cours de la bourse               1

Cette table virtuelle n'a que 4 enregistrements, elle n'est pas coûteuse réellement. Mais imaginons un cas de figure : que se passe-t-il si georges@google.co.uk se désinscrit ?

Ainsi sont les limites de cette jointure. Si j'enlève un email (par exemple, georges@google.co.uk), alors pour "Le cours de la bourse", la requête ne trouvera pas d'abonné correspondant : cette newsletter sera absente du tableau de résultats !

titre                               nombre_abonnes
-----------------------------------------------------------
Les recettes de cuisine             5
Astuces pour le jardin              4
Conseils pour dépanner sa voiture   3

Jointures droites et gauches

Ces jointures permettent de raccoller bout à bout les tables sans passer par une table intermédiaire. D'autre part, dans le cas d'un champ manquant (par exemple une newsletter n'ayant aucun inscrit ...) ce type de jointure fait sortir les résultats en remplissant le champ manquant par un NULL ou par un 0 :

	SELECT
		titre,
		COUNT(email_abonne) AS nombre_abonnes,
	FROM
		newsletters
			LEFT JOIN
				inscriptions
			ON
				newsletters.id = inscriptions.id_newsletter
	GROUP BY
		newsletters.id;

Cette requête renvoie :

titre                               nombre_abonnes
-----------------------------------------------------------
Les recettes de cuisine             5
Astuces pour le jardin              4
Conseils pour dépanner sa voiture   3
Le cours de la bourse               0

Bien entendu, on peut tout à fait renommer "à la volée" les noms des champs (comme je l'ai fait pour le COUNT qui permet de compter les occurences) ou les tables :

	SELECT
		titre,
		COUNT(email_abonne) AS nombre_abonnes,
	FROM
		newsletters n
			LEFT JOIN
				inscriptions i
			ON
				n.id = i.id_newsletter
	GROUP BY
		n.id;

Pour plus d'infos sur les jointures, consulter la documentation MySQL.

Ces pages peuvent vous intéresser :