Editions ENI Livres | Vidéos | e-Formations
Utiliser les fonctions MySQL
Introduction
Comme nous l’avons vu à plusieurs reprises depuis le début de cet ouvrage, nous pouvons utiliser des expressions dans les différentes clauses des ordres SQL. Une expression peut être écrite en utilisant des colonnes, des expressions littérales, des opérateurs et des fonctions SQL.
Dans ce chapitre, nous allons présenter les fonctions SQL les plus souvent utilisées. Nous ne présenterons pas toutes les fonctions existantes (il y en a plus de 250 !). Nous ne présenterons pas non plus systématiquement toutes les options possibles d’une fonction. Pour en savoir plus, reportez-vous à la documentation SQL.
La syntaxe générale d’une fonction est la suivante :
nom_fonction([argument][,...]) argument peut être toute expression dont la valeur est passée en paramètre à la fonction ; argument peut lui-même appeler d’autres fonctions.
Rappel : sauf indication contraire, une expression qui contient un NULL donne un résultat NULL.
Fonctions de contrôle
Les fonctions suivantes sont présentées dans cette section :
|
IF |
Fonction du type « si alors sinon » basée sur une condition. |
|
IFNULL |
Fonction du type « si alors sinon » basée sur la nullité d’une expression. |
|
NULLIF |
Retourne NULL si deux expressions sont égales. |
|
CASE |
Structure de contrôle condition du type « si alors sinon » (généralisation de la fonction IF). |
IF
Syntaxe
IF(condition,valeur_si_vrai,valeur_si_faux) Si l’expression condition est vraie (TRUE), la fonction retourne l’expression valeur_si_vrai ; sinon (condition = FALSE ou NULL), elle retourne l’expression valeur_si_faux.
Exemple
mysql> SELECT
-> titre,
-> annee_parution,
-> IF(annee_parution < 2019,'Ancien','Récent') age
-> FROM livre
-> WHERE id_collection = 1;
+---------------------+----------------+---------+
| titre | annee_parution | age |
+---------------------+----------------+---------+
| PHP 7 | 2018 | Ancien |
| PHP 8 | 2021 | Récent |
| Oracle 12c | 2014 | Ancien |
| Oracle 19c | 2021...Fonctions de comparaison
Les fonctions suivantes sont présentées dans cette section :
|
LEAST |
Plus petite valeur d’une liste de valeurs. |
|
GREATEST |
Plus grande valeur d’une liste de valeurs. |
|
COALESCE |
Première expression non NULL d’une liste d’expressions. |
LEAST - GREATEST
Syntaxe
LEAST(expression1,expression2[,...])
GREATEST(expression1,expression2[,...]) Les fonctions LEAST et GREATEST retournent respectivement la plus petite et la plus grande valeur d’une liste d’expressions.
Exemple
mysql> -- Calcul du montant d'une remise
mysql> -- de 5% plafonnée à 1.5
mysql> SELECT
-> nom,
-> prix_ht,
-> LEAST(ROUND(prix_ht*5/100,2),1.5) remise
-> FROM collection;
+--------------------------+---------+--------+
| nom | prix_ht | remise |
+--------------------------+---------+--------+
| Ressources Informatiques | 28.48 | 1.42 |
| Open IT | 6.66 | 0.33 |
| Les TP Informatiques | 25.71 | 1.29 |
| Coffret Technique | 54.19 | 1.50 |
| Epsilon | 51.90 | 1.50 |
| Solutions Informatiques | 36.97 | 1.50 |
+--------------------------+---------+--------+ ...Fonctions numériques
Les fonctions suivantes sont présentées dans cette section :
|
ABS |
Valeur absolue d’un nombre. |
|
CEILING, CEIL |
Plus petit entier qui n’est pas inférieur à un nombre. |
|
DIV |
Résultat de la division entière de deux nombres. |
|
FLOOR |
Plus grand entier qui n’est pas supérieur à un nombre. |
|
MOD, % |
Reste de la division entière de deux nombres. |
|
RAND |
Nombre aléatoire supérieur ou égal à 0 et strictement inférieur à 1. |
|
ROUND |
Nombre arrondi à la précision demandée. |
|
TRUNCATE |
Nombre tronqué à la précision demandée. |
Dans un SELECT, une division par zéro donne un résultat NULL et génère une alerte. Dans une mise à jour (INSERT, UPDATE), une division par zéro génère une erreur si le mode ERROR_FOR_DIVISION_BY_ZERO est actif et associé au mode strict (voir la section Le mode SQL du serveur dans le chapitre Introduction à MySQL).
ABS
Syntaxe
ABS(nombre) La fonction ABS retourne la valeur absolue d’un nombre.
CEILING - CEIL
Syntaxe
CEIL(nombre) La fonction CEILING (ou son synonyme CEIL) retourne le plus petit entier qui n’est pas inférieur à un nombre.
Exemple
mysql> SELECT nom,prix_ht,CEIL(prix_ht) FROM collection;
+--------------------------+---------+---------------+
| nom | prix_ht | CEIL(prix_ht) |
+--------------------------+---------+---------------+
| Ressources Informatiques | 28.48 | 29 |
| Open IT | 6.66 | 7...Fonctions caractères
Les fonctions suivantes sont présentées dans cette section :
|
CONCAT, CONCAT_WS |
Concaténation de chaînes de caractères. |
|
INSTR |
Position de la première occurrence d’une chaîne à l’intérieur d’une autre chaîne. |
|
LEFT, RIGHT |
n premiers ou n derniers caractères d’une chaîne. |
|
LENGTH |
Longueur d’une chaîne. |
|
LOWER, UPPER |
Chaîne en minuscules ou en majuscules. |
|
LPAD, RPAD |
Chaîne complétée à gauche ou à droite par une séquence de caractères jusqu’à une certaine longueur. |
|
LTRIM, RTRIM, TRIM |
Suppression d’espace (ou d’autres caractères) en début ou en fin de chaîne. |
|
REPEAT, SPACE |
Chaîne construite en répétant une séquence de caractères un certain nombre de fois. |
|
REPLACE |
Remplacement de toutes les occurrences d’une chaîne par une autre. |
|
SUBSTRING, SUBSTR, SUBSTRING_INDEX |
Portion d’une chaîne. |
Rappel : seules les chaînes de caractères « binaires » sont sensibles à la casse.
CONCAT - CONCAT_WS
Syntaxe
CONCAT(chaîne1,chaîne2[,...])
CONCAT_WS(séparateur,chaîne1,chaîne2[,...]) La fonction CONCAT retourne une chaîne de caractères qui concatène tous ses arguments.
La fonction CONCAT_WS est une variante de la fonction CONCAT. Le premier argument est une chaîne qui est utilisée comme séparateur dans la concaténation des autres arguments.
Exemples
mysql> SELECT CONCAT(prenom,' ',nom) FROM auteur;
+------------------------+
| CONCAT(prenom,' ',nom) |
+------------------------+
| Alan BOUCARD |
| Stéphane COMBAUDON |
| Yann GLINEUR |...Fonctions dates
Les fonctions suivantes sont présentées dans cette section :
|
ADDDATE, DATE_ADD, DATE_SUB, SUBDATE |
Ajoute ou retranche un intervalle de temps à une date. |
|
CURDATE, CURRENT_DATE, UTC_DATE |
Date courante. |
|
CURTIME, CURRENT_TIME, UTC_TIME |
Heure courante. |
|
CURRENT_TIMESTAMP, NOW, LOCALTIME, LOCALTIMESTAMP, SYSDATE, UTC_TIMESTAMP |
Date/heure courante. |
|
DATE |
Extrait la partie date d’une date/heure. |
|
DATEDIFF |
Différence en nombre de jours entre deux dates. |
|
DAYOFWEEK, WEEKDAY, DAYOFMONTH, DAYOFYEAR |
Extrait le numéro du jour dans la semaine, dans le mois ou dans l’année d’une date. |
|
EXTRACT |
Extrait une composante d’une date. |
|
LAST_DAY |
Dernier jour du mois d’une date. |
|
MONTH |
Numéro de mois d’une date. |
|
WEEK, WEEKOFYEAR |
Numéro de semaine d’une date. |
|
YEAR |
Année d’une date. |
À l’exception de SYSDATE, les fonctions qui retournent la date et/ou l’heure « courante » sont évaluées une fois au début de la requête ; ces fonctions retournent donc la date et/ou l’heure de début d’exécution de la requête. Si une telle fonction est appelée plusieurs fois à l’intérieur de la requête, c’est donc toujours la même valeur qui est retournée.
ADDDATE - DATE_ADD - DATE_SUB - SUBDATE
Syntaxe
ADDDATE(date,INTERVAL valeur unité)
ADDDATE(date,nombre_jours)
DATE_ADD(date,INTERVAL valeur unité)
DATE_SUB(date,INTERVAL valeur unité)
SUBDATE(date,INTERVAL valeur unité)
SUBDATE(date,nombre_jours) Les fonctions ADDDATE, DATE_ADD, DATE_SUB et SUBDATE retournent une date après ajout ou soustraction d’un intervalle de temps.
Dans les syntaxes avec le mot-clé INTERVAL, unité est un mot-clé qui donne l’unité de l’intervalle (voir ci-dessous)...
Fonctions de transtypage et de mise en forme
Les fonctions suivantes sont présentées dans cette section :
|
BINARY |
Conversion d’une chaîne en chaîne binaire. |
|
CAST, CONVERT |
Conversion d’une donnée d’un type en un autre. |
|
DATE_FORMAT |
Formate une date. |
|
FORMAT |
Formate un nombre. |
|
STR_TO_DATE |
Conversion d’une chaîne en date. |
BINARY
Syntaxe
BINARY chaîne L’opérateur BINARY convertit une chaîne en chaîne binaire.
Cet opérateur est déprécié à partir de la version 8.0.27 et sera supprimé dans une version ultérieure. À la place, il est conseillé d’utiliser la fonction CAST(... AS BINARY) présentée dans la suite.
Exemple
mysql> -- Recherche non sensible à la casse
mysql> SELECT prix_ht FROM collection WHERE nom = 'EPSILON';
+---------+
| prix_ht |
+---------+
| 51.90 |
+---------+
1 row in set (0.00 sec)
mysql> -- Recherche sensible à la casse
mysql> SELECT prix_ht FROM collection WHERE nom = BINARY 'EPSILON';
Empty set (0.00 sec) CAST - CONVERT
Syntaxe
CAST(expression AS type)
CONVERT(expression,type) Les fonctions CAST et CONVERT convertissent une expression d’un type quelconque dans un autre type. type peut être une des valeurs suivantes (non exhaustif) :
|
BINARY[(n)] |
Chaîne binaire (éventuellement limitée à n octets). |
|
CHAR[(n)] |
Chaîne binaire (éventuellement limitée à n caractères). |
|
DATE |
Date. |
|
DATETIME |
Date/heure. |
|
DECIMAL[(n[,d])] |
Nombre à virgule fixe. n spécifie le nombre de chiffres significatifs (10 par défaut, 65 au maximum) et d le nombre de chiffres après la virgule (0 par défaut, 30 au maximum). |
|
DOUBLE |
Nombre à virgule flottante en double... |
Fonctions système
Les fonctions suivantes sont présentées dans cette section :
|
CURRENT_USER, SESSION_USER, SYSTEM_USER, USER |
Utilisateur courant. |
|
DATABASE, SCHEMA |
Base de données courante. |
|
FOUND_ROWS |
Nombre de lignes retournées par le dernier ordre SELECT. |
|
LAST_INSERT_ID |
Valeur automatiquement générée par une colonne de type AUTO_INCREMENT lors du dernier INSERT. |
|
ROW_COUNT |
Nombre de lignes mises à jour par le dernier ordre INSERT, UPDATE ou DELETE. |
|
VERSION |
Version de MySQL. |
CURRENT_USER - SESSION_USER - SYSTEM_USER - USER
Syntaxe
CURRENT_USER()
USER()
SESSION_USER()
SYSTEM_USER() La fonction CURRENT_USER retourne le nom d’utilisateur et le nom de la machine de la session courante, sous la forme utilisateur@machine.
La fonction USER retourne le nom d’utilisateur et le nom de la machine spécifiés lors de l’identification avec le serveur MySQL, sous la forme utilisateur@machine. Les fonctions SESSION_USER et SYSTEM_USER sont des synonymes de la fonction USER.
Le résultat des deux fonctions peut être différent. Par exemple, si un client a été identifié par le serveur comme utilisateur anonyme, la fonction CURRENT_USER retournera un nom d’utilisateur vide, alors que la fonction USER retourne le nom réellement spécifié dans la chaîne de connexion.
Exemple
[root@xampp ~]# mysql -u root
...
mysql> SELECT CURRENT_USER(),USER();
+----------------+----------------+
| CURRENT_USER() | USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@xampp ~]# mysql -u eni
...
mysql> SELECT CURRENT_USER(),USER();
+----------------+---------------+ ...Fonctions de chiffrement et de compression
Les fonctions suivantes sont présentées dans cette section :
|
AES_ENCRYPT, AES_DECRYPT |
Chiffrement/déchiffrement de données utilisant l’algorithme AES. |
|
COMPRESS, UNCOMPRESS |
Compression/décompression de données. |
|
MD5, SHA1, SHA, SHA2 |
Somme de vérification d’une chaîne. |
|
PASSWORD |
Mot de passe chiffré. |
Les fonctions de chiffrage et de compression retournent des chaînes binaires ; pour le stockage en base de telles données, il est conseillé d’utiliser une colonne de type BLOB.
Dans les outils comme le client mysql, les chaînes binaires s’affichent en utilisant une notation hexadécimale lorsque l’option cliente --binary-as-hex est active. Cette option est apparue en version 8.0.2 et est active par défaut depuis la version 8.0.19. Lorsque cette option est active, il est possible d’utiliser les fonctions de conversion CAST ou CONVERT pour afficher une chaîne binaire en tant que chaîne de caractères.
Dans le client mysql, la commande status permet de voir si l’option est active ou non :
mysql> status;
--------------
mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
...
Binary data as: Hexadecimal
... Si l’option n’est pas active, la ligne Binary data as n’est pas présente.
Pour désactiver l’option lors du lancement de l’outil mysql, vous pouvez ajouter --binary-as-hex=off dans la ligne de commande (mysql --binary-as-hex=off) ou mettre la directive binary-as-hex = off dans un fichier de configuration.
AES_ENCRYPT - AES_DECRYPT
Syntaxe simplifiée
AES_ENCRYPT(chaîne,clé)
AES_DECRYPT(chaîne,clé) Les fonctions AES_ENCRYPT et AES_DECRYPT chiffrent et déchiffrent une chaîne en utilisant l’algorithme...
Fonctions d’agrégat
Les fonctions d’agrégat sont particulières : elles retournent une ligne de résultat par groupe de lignes en entrée.
Ces fonctions sont la plupart du temps utilisées dans les requêtes qui groupent les données (utilisation de la clause GROUP BY, cf. chapitre Techniques avancées avec MySQL - Grouper les données).
Si ces fonctions sont utilisées dans une requête qui n’effectue pas de groupement de données, cela revient à grouper toutes les lignes : la fonction retourne une seule ligne de résultat. Dans ce cas, la clause SELECT de la requête ne doit contenir que des expressions qui utilisent une fonction d’agrégat.
Les fonctions suivantes sont présentées dans cette section :
|
MIN, MAX |
Minimum ou maximum. |
|
SUM |
Somme. |
|
AVG |
Moyenne. |
|
COUNT |
Nombre. |
Pour toutes ces fonctions, les valeurs NULL sont ignorées ; la présence d’une valeur NULL dans le calcul ne donne pas un résultat NULL.
MIN - MAX
Syntaxe
MIN(expression)
MAX(expression) Les fonctions MIN et MAX retournent respectivement le minimum et le maximum de toutes les valeurs de expression.
Exemple
mysql> SELECT MIN(nombre_pages),MAX(nombre_pages)
-> FROM livre WHERE id_collection = 1;
+-------------------+-------------------+
| MIN(nombre_pages) | MAX(nombre_pages) |
+-------------------+-------------------+
| 515 | 956 |
+-------------------+-------------------+
1 row in set (0.00 sec) SUM - AVG
Syntaxe
SUM(expression)
AVG(expression) Les fonctions SUM et AVG retournent respectivement la somme et la moyenne de toutes les valeurs de expression.
Pour...
