Fonctions MySQL: chaîne, numérique, définie par l'utilisateur, stockée

Que sont les fonctions?

MySQL peut faire bien plus que simplement stocker et récupérer des données . Nous pouvons également effectuer des manipulations sur les données avant de les récupérer ou de les enregistrer. C'est là qu'interviennent les fonctions MySQL. Les fonctions sont simplement des morceaux de code qui effectuent certaines opérations puis renvoient un résultat. Certaines fonctions acceptent des paramètres tandis que d'autres n'acceptent pas de paramètres.

Regardons brièvement un exemple de fonction MySQL. Par défaut, MySQL enregistre les types de données de date au format "AAAA-MM-JJ". Supposons que nous ayons construit une application et que nos utilisateurs souhaitent que la date soit renvoyée au format "JJ-MM-AAAA", nous pouvons utiliser la fonction intégrée MySQL DATE_FORMAT pour y parvenir. DATE_FORMAT est l'une des fonctions les plus utilisées dans MySQL. Nous l'examinerons plus en détail au fur et à mesure que nous déploierons la leçon.

Pourquoi utiliser des fonctions?

Sur la base de l'exemple donné dans l'introduction, les personnes ayant de l'expérience en programmation informatique peuvent penser "Pourquoi déranger les fonctions MySQL? Le même effet peut être obtenu avec le langage de script / programmation?" Il est vrai que nous pouvons y parvenir en écrivant des procédures / fonctions dans le programme d'application.

Pour revenir à notre exemple DATE dans l'introduction, pour que nos utilisateurs obtiennent les données dans le format souhaité, la couche métier devra effectuer le traitement nécessaire.

Cela devient un problème lorsque l'application doit s'intégrer à d'autres systèmes. Lorsque nous utilisons des fonctions MySQL telles que DATE_FORMAT, nous pouvons intégrer cette fonctionnalité dans la base de données et toute application qui a besoin des données les obtient dans le format requis. Cela réduit les retouches dans la logique métier et les incohérences de données.

Une autre raison pour laquelle nous devrions envisager d'utiliser les fonctions MySQL est le fait que cela peut aider à réduire le trafic réseau dans les applications client / serveur . Business Layer n'aura besoin que d'appeler les fonctions stockées sans avoir à manipuler les données. En moyenne, l'utilisation de fonctions peut contribuer à améliorer considérablement les performances globales du système.

Types de fonctions

Fonctions intégrées

MySQL est livré avec un certain nombre de fonctions intégrées. Les fonctions intégrées sont simplement des fonctions déjà implémentées dans le serveur MySQL. Ces fonctions nous permettent d'effectuer différents types de manipulations sur les données. Les fonctions intégrées peuvent être fondamentalement classées dans les catégories les plus utilisées suivantes.

• Fonctions de chaînes - fonctionnent sur des types de données de chaîne
• Fonctions numériques - fonctionnent sur des types de données numériques
• Fonctions de date - fonctionnent sur les types de données de date
• Fonctions d'agrégation - fonctionnent sur tous les types de données ci-dessus et produisent des ensembles de résultats résumés.
• Autres fonctions - MySQL prend également en charge d'autres types de fonctions intégrées, mais nous limiterons notre leçon aux fonctions nommées ci-dessus uniquement.

Regardons maintenant chacune des fonctions mentionnées ci-dessus en détail. Nous expliquerons les fonctions les plus utilisées en utilisant notre "Myflixdb".

Fonctions de chaîne

Nous avons déjà examiné ce que font les fonctions de chaîne. Nous allons regarder un exemple pratique qui les utilise. Dans notre tableau des films, les titres des films sont stockés à l'aide de combinaisons de lettres minuscules et majuscules. Supposons que nous souhaitons obtenir une liste de requêtes qui renvoie les titres des films en majuscules. Nous pouvons utiliser la fonction "UCASE" pour ce faire. Il prend une chaîne comme paramètre et convertit toutes les lettres en majuscules. Le script ci-dessous illustre l'utilisation de la fonction "UCASE".

SELECT `movie_id`,`title`, UCASE(`title`) FROM `movies`;

ICI

• UCASE (`title`) est la fonction intégrée qui prend le titre comme paramètre et le renvoie en majuscules avec le nom d'alias` upper_case_title`.

L'exécution du script ci-dessus dans MySQL workbench contre Myflixdb nous donne les résultats suivants indiqués ci-dessous.

 
 

 
 
movie_id
 
title
 
UCASE('title')
 

 
 
16
 
67% Guilty
 
67% GUILTY
 

 
 
6
 
Angels and Demons
 
ANGELS AND DEMONS
 

 
 
4
 
Code Name Black
 
CODE NAME BLACK
 

 
 
5
 
Daddy's Little Girls
 
DADDY'S LITTLE GIRLS
 

 
 
7
 
Davinci Code
 
DAVINCI CODE
 

 
 
2
 
Forgetting Sarah Marshal
 
FORGETTING SARAH MARSHAL
 

 
 
9
 
Honey mooners
 
HONEY MOONERS
 

 
 
19
 
movie 3
 
MOVIE 3
 

 
 
1
 
Pirates of the Caribean 4
 
PIRATES OF THE CARIBEAN 4
 

 
 
18
 
sample movie
 
SAMPLE MOVIE
 

 
 
17
 
The Great Dictator
 
THE GREAT DICTATOR
 

 
 
3
 
X-Men
 
X-MEN
 

 

 

MySQL prend en charge un certain nombre de fonctions de chaîne. Pour une liste complète de toutes les fonctions de chaîne intégrées, reportez-vous à ce lien http://dev.mysql.com/doc/refman/5.0/en/string-functions.html sur le site Web de MySQL.

Fonctions numériques

Comme mentionné précédemment, ces fonctions fonctionnent sur des types de données numériques. Nous pouvons effectuer des calculs mathématiques sur des données numériques dans les instructions SQL.

Opérateurs arithématiques

MySQL prend en charge les opérateurs arithmatiques suivants qui peuvent être utilisés pour effectuer des calculs dans les instructions SQL.

Nom	Description
DIV	Division entière
/	Division
-	Soustraction
+	Une addition
*	Multiplication
% ou MOD	Module

Regardons maintenant des exemples de chacun des opérateurs ci-dessus

Division entière (DIV)

SELECT 23 DIV 6 ;

L'exécution du script ci-dessus nous donne les résultats suivants.

3

Opérateur de division (/)

Regardons maintenant l'exemple de l'opérateur de division. Nous modifierons l'exemple DIV.

SELECT 23 / 6 ;

L'exécution du script ci-dessus nous donne les résultats suivants.

3,8333

Opérateur de soustraction (-)

Regardons maintenant l'exemple de l'opérateur de soustraction. Nous utiliserons les mêmes valeurs que dans les deux exemples précédents

SELECT 23 - 6 ;

L'exécution du script ci-dessus nous donne 17

Opérateur d'addition (+)

Regardons maintenant l'exemple de l'opérateur d'addition. Nous modifierons l'exemple précédent.

SELECT 23 + 6 ;

L'exécution du script ci-dessus nous donne 29

Opérateur de multiplication (*)

Regardons maintenant l'exemple de l'opérateur de multiplication. Nous utiliserons les mêmes valeurs que dans les exemples précédents.

SELECT 23 * 6 AS `multiplication_result`;

L'exécution du script ci-dessus nous donne les résultats suivants.

multiplication_result

138

Opérateur modulo (-)

L'opérateur modulo divise N par M et nous donne le reste. Regardons maintenant l'exemple de l'opérateur modulo. Nous utiliserons les mêmes valeurs que dans les exemples précédents.

SELECT 23 % 6 ;

OU

SELECT 23 MOD 6 ;

L'exécution du script ci-dessus nous donne 5

Examinons maintenant certaines des fonctions numériques courantes dans MySQL.

Sol - cette fonction supprime les décimales d'un nombre et l'arrondit au nombre le plus bas le plus proche. Le script ci-dessous montre son utilisation.

SELECT FLOOR(23 / 6) AS `floor_result`;

L'exécution du script ci-dessus nous donne les résultats suivants.

Floor_result

3

Arrondir - cette fonction arrondit un nombre avec des décimales au nombre entier le plus proche. Le script ci-dessous montre son utilisation.

SELECT ROUND(23 / 6) AS `round_result`;

L'exécution du script ci-dessus nous donne les résultats suivants.

Round_result

4

Rand - cette fonction est utilisée pour générer un nombre aléatoire, sa valeur change à chaque fois que la fonction est appelée. Le script ci-dessous montre son utilisation.

SELECT RAND() AS `random_result`;

Fonctions mémorisées

Les fonctions stockées sont comme les fonctions intégrées, sauf que vous devez définir vous-même la fonction stockée. Une fois qu'une fonction stockée a été créée, elle peut être utilisée dans des instructions SQL comme n'importe quelle autre fonction. La syntaxe de base pour créer une fonction stockée est la suivante

CREATE FUNCTION sf_name ([parameter(s)])RETURNS data typeDETERMINISTICSTATEMENTS

ICI

• "CREATE FUNCTION sf_name ([paramètre (s)])" est obligatoire et indique au serveur MySQL de créer une fonction nommée `sf_name 'avec des paramètres optionnels définis entre parenthèses.
• "Type de données RETURNS" est obligatoire et spécifie le type de données que la fonction doit renvoyer.
• "DETERMINISTIC" signifie que la fonction retournera les mêmes valeurs si les mêmes arguments lui sont fournis.
• "STATEMENTS" est le code de procédure que la fonction exécute.

Regardons maintenant un exemple pratique qui implémente une fonction intégrée. Supposons que nous voulions savoir quels films loués ont dépassé la date de retour. Nous pouvons créer une fonction stockée qui accepte la date de retour comme paramètre, puis la compare avec la date actuelle sur le serveur MySQL. Si la date actuelle est inférieure à la date de retour du film, alors nous retournons "Non" sinon nous retournons "Oui". Le script ci-dessous nous aide à y parvenir.

DELIMITER |CREATE FUNCTION sf_past_movie_return_date (return_date DATE)RETURNS VARCHAR(3)DETERMINISTICBEGINDECLARE sf_value VARCHAR(3);IF curdate() > return_dateTHEN SET sf_value = 'Yes';ELSEIF curdate() <= return_dateTHEN SET sf_value = 'No';END IF;RETURN sf_value;END|

L'exécution du script ci-dessus a créé la fonction stockée `sf_past_movie_return_date`.

Testons maintenant notre fonction stockée.

SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) FROM `movierentals`;

L'exécution du script ci-dessus dans MySQL workbench contre myflixdb nous donne les résultats suivants.

 
 

 
 
movie_id
 
membership_number
 
return_date
 
CURDATE()
 
sf_past_movie_return_date('return_date')
 

 
 
1
 
1
 
NULL
 
04-08-2012
 
NULL
 

 
 
2
 
1
 
25-06-2012
 
04-08-2012
 
yes
 

 
 
2
 
3
 
25-06-2012
 
04-08-2012
 
yes
 

 
 
2
 
2
 
25-06-2012
 
04-08-2012
 
yes
 

 
 
3
 
3
 
NULL
 
04-08-2012
 
NULL
 

 

 

Fonctions définies par l'utilisateur

MySQL prend également en charge les fonctions définies par l'utilisateur qui étendent MySQL. Les fonctions définies par l'utilisateur sont des fonctions que vous pouvez créer à l'aide d'un langage de programmation tel que C, C ++, etc., puis les ajouter au serveur MySQL. Une fois ajoutés, ils peuvent être utilisés comme n'importe quelle autre fonction.

Résumé

• Les fonctions nous permettent d'améliorer les capacités de MySQL.
• Les fonctions renvoient toujours une valeur et peuvent éventuellement accepter des paramètres.
• Les fonctions intégrées sont des fonctions fournies avec MySQL. Ils peuvent être classés selon les types de données sur lesquels ils opèrent, c'est-à-dire les chaînes, la date et les fonctions numériques intégrées.
• Les fonctions stockées sont créées par l'utilisateur dans le serveur MySQL et peuvent être utilisées dans des instructions SQL.
• Les fonctions définies par l'utilisateur sont créées en dehors de MySQL et peuvent être incorporées au serveur MySQL.