MySQL Fonctions : 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 aussi effectuer des manipulations sur les données avant de le récupérer ou de le sauvegarder. C'est là que MySQL Les fonctions entrent en jeu. Les fonctions sont simplement des morceaux de code qui exécutent certaines opérations et renvoient ensuite 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 MySQL fonction. 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 MySQL fonction intégrée DATE_FORMAT pour y parvenir. DATE_FORMAT est l'une des fonctions les plus utilisées dans MySQLNous l’examinerons plus en détail au fur et à mesure que nous déroulerons 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 se demander « Pourquoi s'embêter ? MySQL Les fonctions? Le même effet peut être obtenu avec un langage de script/programmation ? » C'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. Quand on utilise MySQL des fonctions telles que DATE_FORMAT, nous pouvons alors 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. Ce réduit les retouches dans la logique métier et réduit les incohérences des données.

Une autre raison pour laquelle nous devrions envisager d'utiliser MySQL fonctions est le fait qu'il peut aider à réduire le trafic réseau dans les applications client/serveur. Business Layer n'aura qu'à appeler les fonctions stockées sans avoir besoin de 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 MySQL serveur. Ces fonctions nous permettent d'effectuer différents types de manipulations sur les données. Les fonctions intégrées peuvent être classées dans les catégories les plus utilisées suivantes.

Fonctions de chaînes – opérer sur des types de données chaîne
Fonctions numériques – opérer sur des types de données numériques
Fonctions de date – opérer sur les types de données de date
Fonctions d'agrégation – opérer sur tous les types de données ci-dessus et produire 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.

Examinons maintenant en détail chacune des fonctions mentionnées ci-dessus. 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 examinerons un exemple pratique qui les utilise. Dans notre table de films, les titres des films sont stockés en utilisant des combinaisons de lettres minuscules et majuscules. Supposons que nous souhaitions obtenir une liste de requêtes qui renvoie les titres des films en lettres 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 démontre 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 lettres majuscules avec le nom d'alias `upper_case_title`.

Exécuter le script ci-dessus dans MySQL L'établi contre Myflixdb nous donne les résultats suivants présenté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

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 on MySQL en ligne.

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 arithmétiques

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

Nom	Description
DIV	Division entière
/	Division
-	Soustraction
+	Addition
*	Multiplier
% ou MOD	Module

Examinons 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.

Opérateur de division (/)

Regardons maintenant l'exemple de l'opérateur de division. Nous allons modifier 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.

résultat_multiplication
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 ;

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 démontre son utilisation.

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

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

Résultat_étage
3

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

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

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

Résultat_tour
4

Frontière – cette fonction permet de générer un nombre aléatoire, sa valeur change à chaque fois que la fonction est appelée. Le script ci-dessous démontre son utilisation.

SELECT RAND() AS `random_result`;

Fonctions stocké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 type
   DETERMINISTIC
   STATEMENTS

ICI

« CRÉER UNE FONCTION nom_sf ([paramètre(s)]) » est obligatoire et indique MySQL serveur pour créer une fonction nommée `sf_name' avec des paramètres facultatifs définis entre parenthèses.
« Type de données RETOURNE » est obligatoire et spécifie le type de données que la fonction doit renvoyer.
« DÉTERMINISTE » signifie que la fonction retournera les mêmes valeurs si les mêmes arguments lui sont fournis.
« DÉCLARATIONS » est le code procédural que la fonction exécute.

Voyons maintenant un exemple pratique qui implémente une fonction intégrée. Supposons que nous souhaitons 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 à la date actuelle dans MySQL serveur. Si la date actuelle est inférieure à la date de retour du film, alors nous renvoyons « Non », sinon nous renvoyons « Oui ». Le script présenté ci-dessous nous aide à y parvenir.

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
  RETURNS VARCHAR(3)
   DETERMINISTIC
    BEGIN
     DECLARE sf_value VARCHAR(3);
        IF curdate() > return_date
            THEN SET sf_value = 'Yes';
        ELSEIF  curdate() <= return_date
            THEN 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`;

Exécuter le script ci-dessus dans MySQL Le 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

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 ajoutez-les à MySQL serveur. Une fois ajoutées, elles peuvent être utilisées 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 livrées avec MySQLIls peuvent être classés en fonction des types de données sur lesquels ils opèrent, c'est-à-dire des chaînes, des dates et des fonctions numériques intégrées.
Les fonctions stockées sont créées par l'utilisateur dans MySQL serveur et peut être utilisé dans les instructions SQL.
Les fonctions définies par l'utilisateur sont créées à l'extérieur MySQL et peut être incorporé dans MySQL serveur.