Aller au menu - Aller au contenu

Icône Les fonctions SQL

Par Avatar Mathieu Nebra (M@teo21)
Mise à jour : 03/04/2010
Difficulté : Intermédiaire Intermédiaire Creative Commons BY-NC-SA
133 390 visites depuis 7 jours, dont 1 666 sur ce chapitre classé 2/786
Vous connaissez déjà les fonctions en PHP, mais vous allez découvrir dans ce chapitre que le SQL propose lui aussi toute une série de fonctions ! Le langage SQL permet en effet d'effectuer des calculs directement sur ses données à l'aide de fonctions toutes prêtes.

Celles-ci sont moins nombreuses qu'en PHP mais elles sont spécialement dédiées aux bases de données et se révèlent très puissantes dans la pratique. Pour reprendre notre exemple de la table jeux_video, elles permettent de récupérer très simplement le prix moyen de l'ensemble des jeux, de compter le nombre de jeux que possède chaque personne, d'extraire le jeu le plus cher ou le moins cher, etc. Les fonctions se révèlent aussi indispensables lorsqu'on doit travailler avec des dates en SQL, comme nous le ferons dans le chapitre suivant.

Les fonctions SQL peuvent être classées en 2 catégories :
  • Les fonctions scalaires : elles agissent sur chaque entrée. Par exemple, vous pouvez transformer en majuscules la valeur de chacune des entrées d'un champ.
  • Les fonctions d'agrégat : lorsque vous utilisez ce type de fonction, des calculs sont faits sur l'ensemble de la table pour retourner une valeur. Par exemple, calculer la moyenne des prix retourne une valeur : le prix moyen.

Retour en haut
Sommaire du chapitre :
Icône du chapitre
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

Les fonctions scalaires

Nous allons d'abord découvrir le mode d'emploi d'une fonction SQL de type scalaire : la fonction UPPER. Lorsque vous aurez appris à vous en servir, vous serez capables de faire de même avec toutes les autres fonctions scalaires. Je vous proposerai alors une petite sélection de fonctions scalaires à connaître, sachant qu'il en existe d'autres mais que nous ne pouvons pas toutes les passer en revue, ce serait bien trop long. ;)

Utiliser une fonction scalaire SQL



Nous allons pour nos exemples nous baser sur la table jeux_video que nous connaissons bien maintenant. Pour rappel, voici à quoi elle ressemble :

IDnompossesseurconsoleprixnbre_joueurs_maxcommentaires
1 Super Mario Bros Florent NES 4 1 Un jeu d'anthologie !
2 Sonic Patrick Megadrive 2 1 Pour moi, le meilleur jeu au monde !
3 Zelda : ocarina of time Florent Nintendo 64 15 1 Un jeu grand, beau et complet comme on en voit rarement de nos jours
4 Mario Kart 64 Florent Nintendo 64 25 4 Un excellent jeu de kart !
5 Super Smash Bros Melee Michel GameCube 55 4 Un jeu de baston délirant !


On écrit les noms des fonctions SQL en majuscules, comme on le fait déjà pour la plupart des mots-clé comme SELECT, INSERT, etc. Ce n'est pas une obligation mais plutôt une convention, une habitude qu'ont prise les programmeurs.

Pour vous montrer comment on utilise les fonctions SQL scalaires, je vais me baser sur la fonction UPPER() qui permet de convertir l'intégralité d'un champ en majuscules. Supposons que nous souhaitions obtenir en majuscules les noms de tous les jeux. Voici comment on écrirait la requête SQL :

Code : SQL
1
SELECT UPPER(nom) FROM jeux_video


La fonction UPPER est utilisée sur le champ nom. On récupère ainsi tous les noms des jeux en majuscules.

Cela modifie-t-il le contenu de la table ?


Non ! La table reste la même. La fonction UPPER modifie seulement la valeur envoyée à PHP. On ne touche donc pas au contenu de la table.

Cela crée en fait un "champ virtuel" qui n'existe que le temps de la requête. Il est conseillé de donner un nom à ce champ virtuel qui représente les noms des jeux en majuscules. Il faut utiliser pour cela le mot-clé AS comme ceci :

Code : SQL
1
SELECT UPPER(nom) AS nom_maj FROM jeux_video


On récupère les noms des jeux en majuscules via un champ virtuel appelé nom_maj.

Ce champ virtuel est appelé alias.


Voici le tableau que retournera MySQL d'après la requête précédente :

nom_maj
SUPER MARIO BROS
SONIC
ZELDA : OCARINA OF TIME
MARIO KART 64
SUPER SMASH BROS MELEE


On peut s'en servir en PHP pour afficher les noms des jeux en majuscules :

Code : PHP
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
<?php
$reponse = $bdd->query('SELECT UPPER(nom) AS nom_maj FROM jeux_video');

while ($donnees = $reponse->fetch())
{
	echo $donnees['nom_maj'] . '<br />';
}

$reponse->closeCursor();

?>

Essayer !


Comme vous le voyez, PHP ne récupère qu'un champ nommé nom_maj (même s'il n'existe pas dans la table). En affichant le contenu de ce champ, on ne récupère que les noms des jeux en majuscules.

Bien entendu vous pouvez aussi récupérer le contenu des autres champs comme avant sans leur appliquer forcément une fonction :

Code : SQL
1
SELECT UPPER(nom) AS nom_maj, possesseur, console, prix FROM jeux_video


On récupèrera alors les données suivantes :

nom_majpossesseurconsoleprix
SUPER MARIO BROS Florent NES 4
SONIC Patrick Megadrive 2
ZELDA : OCARINA OF TIME Florent Nintendo 64 15
MARIO KART 64 Florent Nintendo 64 25
SUPER SMASH BROS MELEE Michel GameCube 55


Vous savez maintenant utiliser une fonction SQL scalaire. :)
Passons en revue quelques fonctions du même type, qui s'utilisent donc de la même manière.

Présentation de quelques fonctions scalaires utiles



Je vais vous présenter une sélection de fonctions scalaires qu'il peut être utile de connaître. Il en existe bien d'autres comme nous le verrons à la fin de cette liste, mais il serait trop long et peu utile de toutes les présenter ici.

UPPER : convertir en majuscules



Cette fonction convertit le texte d'un champ en majuscules. Nous l'avons découverte pour introduire les fonctions SQL :

Code : SQL
1
SELECT UPPER(nom) AS nom_maj FROM jeux_video


Ainsi, le jeu "Sonic" sera renvoyé sous la forme "SONIC" dans un champ nommé nom_maj.

LOWER : convertir en minuscules



Cette fonction a l'effet inverse : le contenu sera entièrement écrit en minuscules.

Code : SQL
1
SELECT LOWER(nom) AS nom_min FROM jeux_video


Cette fois, le jeu "Sonic" sera renvoyé sous la forme "sonic" dans un champ nommé nom_min.

LENGTH : compter le nombre de caractères



Vous pouvez obtenir la longueur d'un champ avec la fonction LENGTH() :

Code : SQL
1
SELECT LENGTH(nom) AS longueur_nom FROM jeux_video


Pour "Sonic", on récupèrera donc la valeur 5 dans un champ longueur_nom.

ROUND : arrondir un nombre décimal



La fonction ROUND() s'utilise sur des champs comportant des valeurs décimales. Il n'y en a pas dans la table jeux_video, mais si on avait des prix décimaux, on pourrait arrondir les valeurs avec cette fonction.

Celle-ci prend cette fois 2 paramètres : le nom du champ à arrondir et le nombre de chiffres après la virgule que l'on souhaite obtenir. Exemple :

Code : SQL
1
SELECT ROUND(prix, 2) AS prix_arrondi FROM jeux_video


Ainsi, si un jeu coûte 25,86999 €, il sera retourné sous forme de la valeur 25,87 € dans un champ prix_arrondi.

Et bien d'autres !



Il existe beaucoup d'autres fonctions SQL du même type mais je ne peux pas toutes vous les présenter. La documentation de MySQL vous propose une liste bien plus complète de fonctions mathématiques (comme ROUND) et de fonctions sur les chaînes de caractères (comme UPPER). Si vous voulez en découvrir d'autres, c'est par là qu'il faut aller !
Retour en haut

Les fonctions d'agrégat

Comme précédemment, nous allons d'abord voir comment on utilise une fonction d'agrégat dans une requête SQL et comment on récupère le résultat en PHP, puis je vous présenterai une sélection de fonctions à connaître. Bien entendu, il en existe bien d'autres que vous pourrez découvrir dans la documentation. L'essentiel est de comprendre comment s'utilise ce type de fonction : vous pourrez ensuite appliquer ce que vous connaissez à n'importe quelle autre fonction du même type. :)

Utiliser une fonction d'agrégat SQL



Ces fonctions sont assez différentes des précédentes. Plutôt que de modifier des valeurs une à une, elles font des opérations sur plusieurs entrées pour retourner une seule valeur.

Par exemple, ROUND permettait d'arrondir chaque prix. On récupérait autant d'entrées qu'il y en avait dans la table. En revanche, une fonction d'agrégat comme AVG renvoie une seule entrée : la valeur moyenne de tous les prix.

Regardons de près la fonction d'agrégat AVG. Elle calcule la moyenne d'un champ contenant des nombres. Utilisons-la sur le champ prix :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen FROM jeux_video


On donne là encore un alias au résultat donné par la fonction. La particularité, c'est que cette requête ne va retourner qu'une seule entrée, le prix moyen de tous les jeux :

prix_moyen
28.34


Pour afficher cette information en PHP, on pourrait faire comme on en a l'habitude (cela fonctionne) :

Code : PHP
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
<?php
$reponse = $bdd->query('SELECT AVG(prix) AS prix_moyen FROM jeux_video');

while ($donnees = $reponse->fetch())
{
	echo $donnees['prix_moyen'];
}

$reponse->closeCursor();

?>


Néanmoins, pourquoi s'embêterait-on à faire une boucle étant donné qu'on sait qu'on ne va récupérer qu'une seule entrée, puisqu'on utilise une fonction d'agrégat ?

On peut se permettre d'appeler fetch() une seule fois, en dehors d'une boucle, étant donné qu'il n'y a qu'une seule entrée. Le code suivant est donc un peu plus adapté dans le cas présent :

Code : PHP
1
2
3
4
5
6
7
8
9
<?php
$reponse = $bdd->query('SELECT AVG(prix) AS prix_moyen FROM jeux_video');

$donnees = $reponse->fetch();
echo $donnees['prix_moyen'];

$reponse->closeCursor();

?>


Essayer !


Ce code est plus simple et plus logique. On récupère la première (et seule) entrée une fois avec fetch() et on affiche ce qu'elle contient, puis on ferme le curseur. Inutile de le faire dans une boucle étant donné qu'il n'y a pas de seconde entrée.

N'hésitez pas à filtrer !



Bien entendu, vous pouvez profiter de toute la puissance du langage SQL pour obtenir, par exemple, le prix moyen des jeux appartenant à Patrick. Voici comment on s'y prendrait :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen FROM jeux_video WHERE possesseur='Patrick'


Le calcul de la moyenne ne sera fait que sur la liste des jeux qui appartiennent à Patrick. Vous pourriez même combiner les conditions pour obtenir le prix moyen des jeux de Patrick qui se jouent à 1 seul joueur. Essayez !

Ne pas mélanger une fonction d'agrégat avec d'autres champs



Soyez attentifs à ce point car il n'est pas forcément évident à comprendre : vous ne devez pas récupérer d'autres champs de la table quand vous utilisez une fonction d'agrégat, contrairement à tout à l'heure avec les fonctions scalaires. En effet, quel sens cela aurait-il de faire :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen, nom FROM jeux_video


On récupèrerait d'un côté le prix moyen de tous les jeux et de l'autre la liste des noms de tous les jeux ? Ca ne peut pas se représenter dans un seul et même tableau.

Comme vous le savez, SQL renvoie les informations sous la forme d'un tableau. Or on ne peut pas représenter la moyenne des prix (qui tient en une seule entrée) en même temps que la liste des jeux. Il faudrait faire 2 requêtes si on voulait avoir ces 2 informations.

Présentation de quelques fonctions d'agrégat utiles



AVG : calculer la moyenne



C'est la fonction que l'on vient d'étudier pour découvrir les fonctions d'agrégat. Elle retourne la moyenne d'un champ contenant des nombres :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen FROM jeux_video


SUM : additionner les valeurs



La fonction SUM permet d'additionner toutes les valeurs d'un champ. Ainsi, on pourrait connaître la valeur totale des jeux appartenant à Patrick :

Code : SQL
1
SELECT SUM(prix) AS prix_total FROM jeux_video WHERE possesseur='Patrick'


MAX : retourner la valeur maximale



Cette fonction analyse un champ et retourne la valeur maximale trouvée. Pour obtenir le prix du jeu le plus cher :

Code : SQL
1
SELECT MAX(prix) AS prix_max FROM jeux_video


MIN : retourner la valeur minimale



De même on peut obtenir le prix du jeu le moins cher :

Code : SQL
1
SELECT MIN(prix) AS prix_min FROM jeux_video


COUNT : compter le nombre d'entrées



La fonction COUNT permet de compter le nombre d'entrées. Elle est très intéressante mais plus complexe. On peut en effet l'utiliser de plusieurs façons différentes.

L'utilisation la plus courante consiste à lui donner * en paramètre :

Code : SQL
1
SELECT COUNT(*) AS nbjeux FROM jeux_video


On obtient ainsi le nombre total de jeux dans la table.

On peut bien entendu filtrer avec une clause WHERE, pour obtenir le nombre de jeux de Florent par exemple :

Code : SQL
1
SELECT COUNT(*) AS nbjeux FROM jeux_video WHERE possesseur='Florent'


Il est possible de compter uniquement les entrées pour lesquelles l'un des champs n'est pas vide, c'est-à-dire qu'il ne vaut pas NULL. Il n'y a pas de jeu de ce type dans notre table jeux_video, mais supposons que pour certains jeux on ne connaisse pas le nombre de joueurs maximum. On laisserait certaines entrées vides, ce qui aurait pour effet d'afficher NULL (pas de valeur) sur la colonne nbre_joueurs_max comme ceci :


IDnompossesseurconsoleprixnbre_joueurs_maxcommentaires
1 Super Mario Bros Florent NES 4 NULL Un jeu d'anthologie !
2 Sonic Patrick Megadrive 2 NULL Pour moi, le meilleur jeu au monde !
3 Zelda : ocarina of time Florent Nintendo 64 15 1 Un jeu grand, beau et complet comme on en voit rarement de nos jours
4 Mario Kart 64 Florent Nintendo 64 25 4 Un excellent jeu de kart !
5 Super Smash Bros Melee Michel GameCube 55 NULL Un jeu de baston délirant !


Dans ce cas, on peut compter uniquement les jeux qui ont un nombre de joueurs maximum défini. On doit indiquer en paramètre le nom du champ à analyser :

Code : SQL
1
SELECT COUNT(nbre_joueurs_max) AS nbjeux FROM jeux_video


Dans notre exemple, seuls les jeux Zelda et Mario Kart seront comptés car on connaît leur nombre de joueurs maximum. Donc on obtiendra 2 en réponse.

Enfin, il est possible de compter le nombre de valeurs distinctes sur un champ précis. Par exemple dans la colonne possesseur, Florent apparaît plusieurs fois, Patrick aussi, etc. Mais combien y a-t-il de personnes différentes dans la table ? On peut le savoir en utilisant le mot-clé DISTINCT devant le nom du champ à analyser, comme ceci :

Code : SQL
1
SELECT COUNT(DISTINCT possesseur) AS nbpossesseurs FROM jeux_video


On peut ainsi facilement savoir combien de personnes différentes sont référencées dans la table. Essayez de faire de même pour connaître le nombre de consoles différentes dans la table !
Retour en haut

GROUP BY et HAVING : le groupement de données

Je vous disais un peu plus tôt qu'on ne pouvait pas récupérer d'autres champs lorsqu'on utilisait une fonction d'agrégat. Prenons par exemple la requête suivante :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen, console FROM jeux_video


Ca n'a pas de sens de récupérer le prix moyen de tous les jeux et le champ "console" à la fois. Il n'y a qu'un seul prix moyen pour tous les jeux, mais plusieurs consoles. MySQL ne peut pas renvoyer un tableau correspondant à ces informations-là.

Si vous essayez d'exécuter la requête, vous obtiendrez des résultats incohérents :

prix_moyenconsole
28.34 NES


On a bien le bon prix moyen de tous les jeux, mais on récupère juste une console (pourquoi celle-là plutôt qu'une autre ?). Bref, ça ne veut rien dire.

GROUP BY : grouper des données



Par contre, ce qui pourrait avoir du sens, ce serait de demander le prix moyen des jeux pour chaque console ! Pour faire cela, on doit utiliser un nouveau mot-clé : GROUP BY. Cela signifie "grouper par". On utilise cette clause en combinaison d'une fonction d'agrégat (comme AVG) pour obtenir des informations intéressantes sur des groupes de données.

Voici un exemple d'utilisation de GROUP BY :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen, console FROM jeux_video GROUP BY console


Essayer !


Il faut utiliser GROUP BY en même temps qu'une fonction d'agrégat, sinon il ne sert à rien. Ici, on récupère le prix moyen et la console, et on choisit de grouper par console. Par conséquent, on obtiendra la liste des différentes consoles de la table et le prix moyen des jeux qu'elles contiennent !

prix_moyenconsole
12.67 Dreamcast
5.00 Gameboy
47.50 GameCube


Cette fois les valeurs sont cohérentes ! On a la liste des consoles et le prix moyen des jeux associés.

Exercice : essayez d'obtenir de la même façon la valeur totale des jeux que possède chaque personne.

HAVING : filtrer les données regroupées



HAVING est un peu l'équivalent de WHERE, mais il agit sur les données une fois qu'elles ont été regroupées. C'est donc une façon de filtrer les données à la fin des opérations.

Voyez la requête suivante :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen, console FROM jeux_video GROUP BY console HAVING prix_moyen <= 10


Essayer !


Avec cette requête, on récupère uniquement la liste des consoles et leur prix moyen si ce prix moyen ne dépasse pas 10 euros.

HAVING ne doit s'utiliser que sur le résultat d'une fonction d'agrégat. Voilà pourquoi on l'utilise ici sur prix_moyen et non sur console.

Je ne comprends pas la différence entre WHERE et HAVING ? Les deux permettent de filtrer non ?


Oui, mais pas au même moment. WHERE agit en premier, avant le groupement des données, tandis que HAVING agit en second, après le groupement des données. On peut d'ailleurs très bien combiner les deux, regardez l'exemple suivant :

Code : SQL
1
SELECT AVG(prix) AS prix_moyen, console FROM jeux_video WHERE possesseur='Patrick' GROUP BY console HAVING prix_moyen <= 10


Ca commence à faire de la requête maousse costaud. :D

Essayer !


Ici, on demande à récupérer le prix moyen par console de tous les jeux de Patrick (WHERE), à condition que le prix moyen des jeux de la console ne dépasse pas 10 euros (HAVING).
Retour en haut

Q.C.M.

Une fonction qui retourne la valeur maximale d'un champ est une fonction...
Quel effet a la requête SQL suivante :


Code : SQL
1
SELECT UPPER(nom) AS nom_maj FROM jeux_video

Pourquoi est-il inutile de faire une boucle lorsqu'on utilise une fonction d'agrégat ?
Quel effet a la requête suivante :


Code : SQL
1
SELECT COUNT(DISTINCT console) AS tot_consoles FROM jeux_video

Quel effet a la requête suivante :


Code : SQL
1
SELECT MAX(prix) AS prix_max, possesseur FROM jeux_video GROUP BY possesseur

Statistiques de réponses au QCM

Retour en haut
Les fonctions SQL ouvrent de nombreuses possibilités comme vous avez pu le constater.

On pourrait se dire que certaines fonctionnalités auraient aussi bien pu être traitées en PHP. Par exemple, mettre en majuscules le nom des jeux aurait pu être fait avec une fonction PHP adaptée au moment de l'affichage. C'est vrai, mais les fonctions SQL sont souvent plus souples et plus indiquées car on récupère ainsi un résultat déjà mis en forme. D'autre part, si on se met à utiliser des fonctions d'agrégat en même temps qu'un regroupement de données, on peut rapidement obtenir des informations très intéressantes à partir d'une table toute simple.

L'utilisation de GROUP BY et HAVING n'est d'ailleurs pas toujours très bien comprise, j'ai parfois vu des gens les utiliser avec des fonctions scalaires ce qui n'a pas de sens. Prenez donc bien le temps de comprendre ce qu'elles font pour ne pas les utiliser n'importe comment. Demandez-vous toujours "Qu'est-ce que je veux obtenir" et construisez la requête bout par bout car tout faire d'un coup peut parfois se révéler vraiment difficile.
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

Partager

Retour en haut
22 commentaires pour "Les fonctions SQL"
Note moyenne : 3.61 / 4 (2518 votes)
Pseudo Commentaire
Hors ligne Zizim0u # Posté le 16/08/2011 à 23:31:56
Je suis un Geek et Alors ?

Avis : Bon

J'ai un peu galerer sur le group by rien ne s'afficher met tout est resolut ;')
Trust - Acdc - Jimi Hendrix - ZzTop - Motorhëad - Iron Maiden - Gun's And Roses - The Police - Téléphone..

----------------------------
Dylan : 15 ans;
Passion : Hard-Rock; Guitare; Programmation.
 
Hors ligne hugo7781 # Posté le 30/10/2011 à 14:18:20
Avatar

 Exellente explication des fonctions SQL !
Hors ligne The6able # Posté le 28/02/2012 à 16:32:37

Pour ceux qui galèrent avec le GROUP BY, voila la solution:

<?php
try
{
$pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
$bdd = new PDO('mysql:host=localhost;dbname=test', 'root', 'bilboss6', $pdo_options);

$reponse = $bdd->query('SELECT AVG(prix) AS prix_moyen, console FROM jeux_video GROUP BY console');

while ($donnees = $reponse->fetch())
{echo $donnees['prix_moyen'] . ' - '; echo $donnees['console'] . '<br/>';}

$reponse->closeCursor();
}

catch(Exception $e)
{
die('Erreur : '.$e->getMessage());
}
?>


Il fallait notamment penser à réaliser à nouveau une boucle pour afficher toutes les lignes et à demander d'afficher la variable "console" (grâce à $donnees['console']).
Hors ligne lapin-math # Posté le 11/03/2012 à 11:01:28
J'aime l'informatique
Avatar

 19/20 !!!

:) :) :)
Citation : Albert Camus
Combien de crimes ont été commis simplement parce que leur auteur ne supportait pas d'avoir tort.

Si je suis à côté de la plaque, envoyez-moi un MP
 
Hors ligne Hwoary # Posté le 27/03/2012 à 14:54:54
Avatar

Avis : Très bon

Études : UTC

 Excellent chapitre, très bien expliqué.
Ce sont des choses que je n'avais pas totalement comprises en cours (il y a 3 ans maintenant), et là c'est passé tout seul.
Encore merci M@teo ;)

Voir tous les commentaires