MySQL Funzioni: stringa, numerica, definita dall'utente, memorizzata

Cosa sono le funzioni?

MySQL può fare molto di più che semplicemente archiviare e recuperare dati. Possiamo anche eseguire manipolazioni sui dati prima di recuperarlo o salvarlo. Ecco dove MySQL Le funzioni entrano in gioco. Le funzioni sono semplicemente pezzi di codice che eseguono alcune operazioni e poi restituiscono un risultato. Alcune funzioni accettano parametri mentre altre funzioni non accettano parametri.

Vediamo brevemente un esempio di MySQL funzione. Per impostazione predefinita, MySQL salva i tipi di dati della data nel formato "AAAA-MM-GG". Supponiamo di aver creato un'applicazione e i nostri utenti desiderano che la data venga restituita nel formato "GG-MM-AAAA", possiamo utilizzare MySQL costruito nella funzione DATE_FORMAT per raggiungere questo obiettivo. DATE_FORMAT è una delle funzioni più utilizzate in MySQLLo esamineremo più in dettaglio man mano che svolgiamo la lezione.

Perché usare le funzioni?

Perché usare Funzioni

Sulla base dell’esempio fornito nell’introduzione, le persone con esperienza nella programmazione di computer potrebbero pensare “Perché preoccuparsi MySQL Funzioni? Lo stesso effetto può essere ottenuto con il linguaggio di scripting/programmazione?” È vero che possiamo raggiungere questo obiettivo scrivendo alcune procedure/funzioni nel programma applicativo.

Tornando al nostro esempio DATE nell'introduzione, affinché i nostri utenti possano ottenere i dati nel formato desiderato, il livello aziendale dovrà eseguire l'elaborazione necessaria.

Questo diventa un problema quando l'applicazione deve integrarsi con altri sistemi. Quando usiamo MySQL funzioni come DATE_FORMAT, allora possiamo incorporare quella funzionalità nel database e qualsiasi applicazione che necessita dei dati li ottiene nel formato richiesto. Questo riduce le rielaborazioni nella logica aziendale e riduce le incoerenze dei dati.

Un altro motivo per cui dovremmo considerare l'utilizzo MySQL funzioni è il fatto che può aiutare a ridurre il traffico di rete nelle applicazioni client/server. Il livello aziendale dovrà solo effettuare chiamate alle funzioni memorizzate senza la necessità di manipolare i dati. In media, l'uso delle funzioni può aiutare a migliorare notevolmente le prestazioni complessive del sistema.

Tipi di funzioni

Funzioni integrate

MySQL viene fornito in bundle con una serie di funzioni integrate. Le funzioni integrate sono semplicemente funzioni già implementate nel file MySQL server. Queste funzioni ci permettono di eseguire diversi tipi di manipolazioni sui dati. Le funzioni integrate possono essere fondamentalmente categorizzate nelle seguenti categorie più utilizzate.

  • Funzioni delle stringhe – operare su tipi di dati stringa
  • Funzioni numeriche – operare su tipi di dati numerici
  • Funzioni data – operare su tipi di dati di data
  • Funzioni aggregate – operare su tutti i tipi di dati di cui sopra e produrre set di risultati riepilogativi.
  • altre funzioni - MySQL supporta anche altri tipi di funzioni integrate ma limiteremo la nostra lezione solo alle funzioni sopra nominate.

Vediamo ora nel dettaglio ciascuna delle funzioni sopra menzionate. Spiegheremo le funzioni più utilizzate utilizzando il nostro "Myflixdb".

Funzioni di stringa

Abbiamo già visto cosa fanno le funzioni stringa. Vedremo un esempio pratico che li utilizza. Nella nostra tabella dei film, i titoli dei film vengono memorizzati utilizzando combinazioni di lettere minuscole e maiuscole. Supponiamo di voler ottenere un elenco di query che restituisca i titoli dei film in lettere maiuscole. Possiamo usare la funzione “UCASE” per farlo. Prende una stringa come parametro e converte tutte le lettere in maiuscolo. Lo script mostrato di seguito dimostra l'uso della funzione “UCASE”.

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

QUI

  • UCASE(`title`) è la funzione integrata che accetta il titolo come parametro e lo restituisce in lettere maiuscole con il nome alias `upper_case_title`.

Eseguendo lo script precedente in MySQL workbench su Myflixdb ci fornisce i seguenti risultati mostrati di seguito.

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 supporta una serie di funzioni stringa. Per un elenco completo di tutte le funzioni stringa integrate, fare riferimento a questo collegamento http://dev.mysql.com/doc/refman/5.0/en/string-functions.html on MySQL di LPI.

Funzioni numeriche

Come accennato in precedenza, queste funzioni operano su tipi di dati numerici. Possiamo eseguire calcoli matematici su dati numerici nelle istruzioni SQL.

Operatori aritmetici

MySQL supporta i seguenti operatori aritmetici che possono essere utilizzati per eseguire calcoli nelle istruzioni SQL.

Nome Descrizione
DIV Divisione intera
/ Divisione
- Sottrazione
+ Aggiunta
* Moltiplicazione
% o MOD Modulo

Diamo ora un'occhiata agli esempi di ciascuno degli operatori di cui sopra

Divisione intera (DIV)

SELECT 23 DIV 6 ;

L'esecuzione dello script sopra riportato produce i seguenti risultati.

3

Operatore di divisione (/)

Consideriamo ora l'esempio dell'operatore di divisione. Modificheremo l'esempio DIV.

SELECT 23 / 6 ;

L'esecuzione dello script sopra riportato produce i seguenti risultati.

3.8333

Operatore di sottrazione (-)

Consideriamo ora l'esempio dell'operatore di sottrazione. Utilizzeremo gli stessi valori dei due esempi precedenti

SELECT 23 - 6 ;

L'esecuzione dello script sopra ci dà 17

Operatore di addizione (+)

Consideriamo ora l'esempio dell'operatore di addizione. Modificheremo l'esempio precedente.

SELECT 23 + 6 ;

L'esecuzione dello script sopra ci dà 29

Operatore di moltiplicazione (*)

Consideriamo ora l'esempio dell'operatore di moltiplicazione. Utilizzeremo gli stessi valori degli esempi precedenti.

SELECT 23 * 6 AS `multiplication_result`;

L'esecuzione dello script sopra riportato produce i seguenti risultati.

risultato_moltiplicazione
138

Operatore modulo (-)

L'operatore modulo divide N per M e ci dà il resto. Consideriamo ora l'esempio dell'operatore modulo. Utilizzeremo gli stessi valori degli esempi precedenti.

SELECT 23 % 6 ;

OR

SELECT 23 MOD 6 ;

L'esecuzione dello script sopra ci dà 5

Diamo ora un'occhiata ad alcune delle funzioni numeriche comuni in MySQL.

Pavimento – questa funzione rimuove le cifre decimali da un numero e lo arrotonda al numero più basso più vicino. Lo script mostrato di seguito ne dimostra l'utilizzo.

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

L'esecuzione dello script sopra riportato produce i seguenti risultati.

Pavimento_risultato
3

Rotondo – questa funzione arrotonda un numero con cifre decimali al numero intero più vicino. Lo script mostrato di seguito ne dimostra l'utilizzo.

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

L'esecuzione dello script sopra riportato produce i seguenti risultati.

Round_risultato
4

Confine – questa funzione viene utilizzata per generare un numero casuale, il suo valore cambia ogni volta che viene chiamata la funzione. Lo script mostrato di seguito ne dimostra l'utilizzo.

SELECT RAND() AS `random_result`;

Funzioni memorizzate

Le funzioni memorizzate sono proprio come le funzioni integrate, tranne per il fatto che devi definire tu stesso la funzione memorizzata. Una volta creata, una funzione memorizzata può essere utilizzata nelle istruzioni SQL proprio come qualsiasi altra funzione. La sintassi di base per creare una funzione memorizzata è quella mostrata di seguito

CREATE FUNCTION sf_name ([parameter(s)])
   RETURNS data type
   DETERMINISTIC
   STATEMENTS

QUI

  • “CREA FUNZIONE nome_sf ([parametro(i)]) “ è obbligatorio e dice MySQL server per creare una funzione denominata "sf_name" con parametri opzionali definiti tra parentesi.
  • “Tipo di dati RETURNS” è obbligatorio e specifica il tipo di dati che la funzione dovrebbe restituire.
  • "DETERMINISTICO" significa che la funzione restituirà gli stessi valori se le vengono forniti gli stessi argomenti.
  • “DICHIARAZIONI” è il codice procedurale eseguito dalla funzione.

Diamo ora un'occhiata a un esempio pratico che implementa una funzione incorporata. Supponiamo di voler sapere quali film noleggiati sono oltre la data di restituzione. Possiamo creare una funzione memorizzata che accetta la data di restituzione come parametro e quindi la confronta con la data corrente in MySQL server. Se la data corrente è inferiore alla data del film restituito, restituiamo "No", altrimenti restituiamo "Sì". Lo script mostrato di seguito ci aiuta a raggiungere questo obiettivo.

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'esecuzione dello script precedente ha creato la funzione memorizzata `sf_past_movie_return_date`.

Testiamo ora la nostra funzione memorizzata.

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

Eseguendo lo script precedente in MySQL workbench su myflixdb ci fornisce i seguenti risultati.

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

Funzioni definite dall'utente

MySQL supporta anche funzioni definite dall'utente che si estendono MySQL. Le funzioni definite dall'utente sono funzioni che è possibile creare utilizzando un linguaggio di programmazione come C, C++ ecc. e poi aggiungerli a MySQL server. Una volta aggiunte, possono essere utilizzate come qualsiasi altra funzione.

Sommario

  • Le funzioni ci consentono di migliorare le capacità di MySQL.
  • Le funzioni restituiscono sempre un valore e facoltativamente possono accettare parametri.
  • Le funzioni integrate sono funzioni fornite con MySQLPossono essere categorizzati in base ai tipi di dati su cui operano, ad esempio stringhe, date e numeri nelle funzioni integrate.
  • Le funzioni memorizzate vengono create dall'utente all'interno MySQL server e può essere utilizzato nelle istruzioni SQL.
  • Le funzioni definite dall'utente vengono create all'esterno MySQL e può essere incorporato in MySQL server.