Operatori e funzioni

Operatori e funzioni vengono utilizzati in diversi punti delle istruzioni
SQL. Ad esempio per determinare i valori da selezionare, per determinare
le condizioni in una WHERE, o nelle clausole ORDER BY, GROUP BY, HAVING.
Vedremo ora i principali, tenendo a mente un paio di regole generali:

• Un'espressione che contiene un valore NULL
  sempre NULL
• Fra il nome di una funzione e le parentesi che contengono i parametri
  non devono rimanere spazi. È possibile modificare questo comportamento
  con l'opzione --sql-mode=IGNORE_SPACE
  di funzione diventano parole riservate.

Quelle che stiamo per elencare, come detto, sono solo alcune delle
funzioni disponibili: per una lista e una descrizione completa vi
rimandiamo al manuale
ufficiale

Per cominciare, i classici operatori aritmetici

• "+
• "-
• "*
• "/
• "%

Ricordate che una divisione per zero dà come risultato (e modulo) NULL.

Passiamo agli operatori di confronto: il risultato di
un'espressione di confronto può essere "1" (vero),
"0" (falso), o NULL.

Gli operatori sono:

• "=
• "<> !=
• "<
• ">
• "<=
• ">=
• "<=> null-safe

Con quest'ultimo operando otteniamo il valore 1 se entrambi i valori sono
null, e 0 se uno solo dei due lo è.

Abbiamo quindi i classici operatori logici:

• NOT
• AND
• OR
• XOR (OR esclusivo)

Come sinonimo di NOT possiamo usare "!
"&& ||
posto di OR.

Abbiamo poi IS NULL IS NOT NULL
verificare se un valore è (o non è) NULL; BETWEEN
test su valori compresi fra due estremi (inclusi); IN
per verificare l'appartenenza di un valore ad una lista di valori dati.

Vediamo un esempio:

SELECT a,b,c,d,e,f,g FROM t1
WHERE a=b AND a<=c
AND (d=5 OR d=8)
AND e BETWEEN 7 and 9
AND f IN('a','b','c')
AND g IS NOT NULL;

Questa query estrae le righe di t1 in cui a
b c d
8, e f
fra parentesi e g

Come avete visto abbiamo usato le parentesi per indicare che
l'espressione "d=5 or d=8" deve essere valutata prima delle altre. Il
consiglio è di utilizzarle sempre in questi casi, invece di imparare a
memoria il lungo elenco delle precedenze.

Molto importante è l'operatore LIKE
trovare corrispondenze parziali sulle stringhe. Possiamo usare due
caratteri jolly nella stringa da trovare: "%
rappresenta "qualsiasi numero di caratteri o nessun carattere", e
"_

Quindi, ad esempio:

SELECT * FROM tab1 colonna
SELECT * FROM tab1 colonna
SELECT * FROM tab1 colonna

La prima query troverà 'paolo', 'paola' e 'paolino'; la seconda troverà
'moro' ma non 'tesoro' perchè si aspetta esattamente un carattere in
testa alla stringa; l'ultima invece troverà 'moro', 'tesoro' e anche
'oro'.

La funzione CAST
quello originale (ad esempio un numero in stringa). Il tipo di dato
ottenuto può essere: DATE, DATETIME, TIME, DECIMAL, SIGNED INTEGER,
UNSIGNED INTEGER, BINARY, CHAR. Con questi ultimi due può essere
specificata anche la lunghezza richiesta.

SELECT CAST(espressione
-> converte in formato data
SELECT CAST(espressione
-> converte in una stringa binaria di 5 byte

È anche possibile utilizzare l'operatore BINARY
sintassi veloce per considerare la stringa seguente come binaria; questo
fa sì che eventuali confronti vengano fatti byte per byte e non carattere
per carattere, rendendo sempre significativa la differenza fra maiuscole
e minuscole, così come gli spazi in fondo alle stringhe.

SELECT 'a' = 'A'
SELECT BINARY 'a' = 'A'

Il primo di questi due test sarà vero, il secondo sarà falso. "BINARY
'a'" equivale a "CAST('a' AS BINARY)".

Esiste poi una funzione CONVERT (... USING ...)
per convertire una stringa fra diversi character set (vedere lez.10). Ad
esempio:

SELECT CONVERT('abc' USING utf8)

Restituisce la stringa 'abc' nel set di caratteri utf8. Attenzione:
esiste un'altra sintassi della funzione CONVERT, che però è un sinonimo
di CAST: ad esempio CONVERT(espressione

Funzioni per il controllo di flusso

Sono utili quando vogliamo eseguire dei test sui valori contenuti in una
tabella e decidere cosa estrarre in base al risultato. Le indichiamo con
la loro sintassi:

valore valore1 risultato1
[WHEN [valore2 risultato2
risultatoN
- CASE WHEN [condizione1 risultato1
[condizione2 risultato2
risultatoN
-
IF(espressione1 espressione2 espressione3

- IFNULL(espressione1 espressione2
- NULLIF(espressione1 espressione2

Le prime due (CASE
viene specificato un valore che sarà confrontato con quelli espressi dopo
la WHEN; il primo che risulta uguale determinerà il risultato
corrispondente (quello espresso con THEN).

Nel secondo caso non c'è un valore di riferimento, ma vengono valutate le
varie condizioni come espressioni booleane: la prima che risulta vera
determina il risultato corrispondente. In entrambi i casi, se è presente
il valore ELSE finale viene usato nel caso in cui nessuna delle
condizioni precedenti sia soddisfatta (in mancanza di ELSE verrebbe
restituito NULL).

Con la IF
viene restituita la seconda, altrimenti la terza. IFNULL
restituisce la prima espressione se diversa da NULL, altrimenti la
seconda. NULLIF

Funzioni sulle stringhe

CONCAT e CONCAT_WS si utilizzano per
concatenare due o più stringhe, nel secondo caso aggiungendo un
separatore.

LOWER e UPPER consentono di trasformare
una stringa, rispettivamente, in tutta minuscola o tutta maiuscola.

LEFT e RIGHT estraggono n caratteri a
sinistra o a destra della stringa.

LENGTH e CHAR_LENGTH restituiscono la
lunghezza di una stringa, con la differenza che la prima misura la
lunghezza in byte, mentre la seconda restituisce il numero di caratteri;
evidentemente i valori saranno diversi per le stringhe che contengono
caratteri multi-byte.

LPAD e RPAD aggiungono, a sinistra
(LPAD) o a destra, i caratteri necessari a portare la stringa alla
lunghezza specificata (eventualmente accorciandola se più lunga).

LTRIM e RTRIM eliminano gli spazi a
sinistra (LTRIM) o a destra.

SUBSTRING restituisce una parte della stringa, a partire
dal carattere specificato fino alla fine della stringa o, se indicato,
per un certo numero di caratteri.

FIND_IN_SET, infine, è una funzione particolarmente
utile con i campi di tipo SET, per verificare se un dato valore è attivo.

Alcuni esempi, seguiti dai rispettivi risultati:

-> Primo;Secondo;Terzo
SELECT LOWER('Primo');
-> primo
SELECT RIGHT('Primo',2);
-> mo
SELECT LENGTH('Primo');
-> 5
SELECT LPAD('Primo',7,'_');
-> __Primo
SELECT LTRIM(' Primo');
-> Primo
SELECT SUBSTRING('Primo',2);
-> rimo
SELECT SUBSTRING('Primo',2,3);
-> rim
SELECT * FROM tabella col1

Funzioni matematiche

ABS restituisce il valore assoluto (non segnato) di un
numero; POWER effettua l'elevamento a potenza (richiede
base ed esponente); RAND genera un valore casuale
compreso tra 0 e 1.

Abbiamo poi le funzioni di arrotondamento, che sono:

• FLOOR
• CEILING
• ROUND
  altrimenti all'inferiore)
• TRUNCATE
  quantità specificata di decimali

Ecco gli esempi:

SELECT ABS(-7.9);
-> 7.9
SELECT POWER(3,4);
-> 81
SELECT RAND();
-> 0.51551992494196 (valore casuale)
SELECT CEILING(6.15);
-> 7
SELECT ROUND(5.5);
-> 6
SELECT TRUNCATE(6.15,1);
-> 6.1

Se abbiamo bisogno di generare un intero compreso fra x e y, possiamo
usare questa formula: "FLOOR(x + RAND() * (y - x + 1))". Ad esempio, per
avere un numero compreso fra 1 e 100:

SELECT FLOOR(1 + RAND() * 100);