4. Espressioni SQL

4.1. Introduzione

Nella maggior parte dei comandi SQL è possibile utilizzare un'espressione. Prendiamo ad esempio il comando SELECT:

sintassi

SELECT espr1, espr2, ... da tabella WHERE espressione

SELECT seleziona le righe per le quali l'espressione è vera e visualizza i valori di expr1, expr2, ... per ciascuna di esse.

Esempi

SQL> select prix*1.186 from biblio
SQL> select titre from biblio where prix between 100 and 150

In questa sezione spiegheremo il concetto di espressione. Un'espressione di base ha la forma:

operando1 operatore operando2

oppure

funzione(parametri)

Esempio

Nell'espressione GENRE = 'ROMAN'

• GENRE è l'operando1
• 'ROMAN' è l'operando2
• = è l'operatore

Nell'espressione upper(genre)

• upper è una funzione
• "genre" è un parametro di questa funzione.

Tratteremo prima le espressioni con gli operatori, poi presenteremo le funzioni disponibili in Firebird.

4.2. Espressioni con operatori

Classificheremo le espressioni con operatori in base al tipo dei loro operandi:

• numeriche
• stringa
• data
• booleano o logico

4.2.1. Espressioni con operandi numerici

4.2.1.1. Elenco degli operatori

Siano numero1, numero2 e numero3 dei numeri. È possibile utilizzare i seguenti operatori:

Operatori relazionali

numero1 > numero2	: numero1 è maggiore di numero2
numero1 >= numero2	: numero1 è maggiore o uguale a numero2
numero1 < numero2	: numero1 è minore di numero2
numero1 <= numero2	: numero1 è minore o uguale a numero2
numero1 = numero2	: numero1 è uguale a numero2
numero1 != numero2	: numero1 non è uguale a numero2
numero1 ≠ numero2	: come
numero1 COMPRESO TRA numero2 E numero3	: numero1 è compreso nell'intervallo [numero2, numero3]
numero1 IN (elenco di numeri)	: numero1 appartiene all'elenco di numeri
numero1 È NULL	: numero1 non ha alcun valore
numero1 NON È NULL	: number1 ha un valore

Operatori aritmetici

numero1 + numero2	: addizione
numero1 - numero2	: sottrazione
numero1 * numero2	: moltiplicazione
numero1 / numero2	: divisione

4.2.1.2. Operatori relazionali

Un'espressione relazionale esprime una relazione che è vera o falsa. Il risultato di tale espressione è quindi un valore booleano o logico.

Esempi:

SQL> select titre,prix from biblio where prix between 100 and 150

SQL> select titre,prix from biblio where prix not between 100 and 150

SQL> select titre,prix from biblio where prix in (200,210)

4.2.1.3. Operatori aritmetici

Conosciamo bene le espressioni aritmetiche. Esse esprimono un calcolo da eseguire su dati numerici. Abbiamo già incontrato espressioni di questo tipo: supponiamo che il prezzo memorizzato nei record del file BIBLIO sia un prezzo al netto delle imposte. Vogliamo visualizzare ogni titolo con il suo prezzo comprensivo di IVA, applicando un'aliquota del 18,6%:

    SELECT TITRE, PRIX*1.186 FROM BIBLIO

Se i prezzi dovessero aumentare del 3%, il comando sarà

    UPDATE BIBLIO SET PRIX = PRIX*1.03

Un'espressione può contenere più operatori aritmetici, oltre a funzioni e parentesi. Questi elementi vengono elaborati secondo diverse priorità:

1	funzioni	<---- priorità massima
2	()
3	* e /
4	+ e -	<---- priorità inferiore

Quando in un'espressione sono presenti due operatori con la stessa priorità, viene valutato per primo quello all'estrema sinistra.

Esempi

L'espressione PREZZO*TASSO+IMPOSTE verrà valutata come (PREZZO*TASSO)+IMPOSTE. Questo perché l'operatore di moltiplicazione viene utilizzato per primo. L'espressione PREZZO*TASSO/100 verrà valutata come (PREZZO*TASSO)/100.

4.2.2. Espressioni con operandi caratteri

4.2.2.1. Elenco degli operatori

È possibile utilizzare i seguenti operatori:

Siano string1, string2, string3 e string pattern

string1 > string2	: string1 è maggiore di string2
string1 >= string2	: string1 è maggiore o uguale a string2
string1 < string2	: string1 è minore di string2
string1 <= string2	: string1 è minore o uguale a string2
string1 = string2	: string1 è uguale a string2
string1 != string2	: string1 non è uguale a string2
string1 ≠ string2	: uguale
string1 TRA string2 E string3	: string1 è compreso nell'intervallo [string2, string3]
string1 IN elenco di stringhe	: string1 appartiene all'elenco di stringhe
string1 È NULL	: string1 non ha alcun valore
string1 NON È NULL	: channel1 ha un valore
string1 è simile a pattern	: string1 corrisponde a pattern

Operatore di concatenazione

string1 || string2 : string2 concatenata a string1

4.2.2.2. Operatori relazionali

Cosa significa confrontare stringhe utilizzando operatori come <, <=, ecc.?

Ogni carattere è codificato come un numero intero. Quando si confrontano due caratteri, vengono confrontati i loro codici interi. La codifica utilizzata segue l'ordine naturale del dizionario:

blanc<..< 0 < 1 < ...< 9 < ...< A < B <... < Z < ... < a < b < ... < z

I numeri precedono le lettere e le lettere maiuscole precedono quelle minuscole.

4.2.2.3. Confronto tra due stringhe

Consideriamo la relazione 'CAT' < 'DOG'. È vera o falsa? Per eseguire questo confronto, il DBMS confronta le due stringhe carattere per carattere in base ai loro codici interi. Non appena si riscontra che due caratteri sono diversi, la stringa contenente il più piccolo dei due viene considerata più piccola dell'altra stringa. Nel nostro esempio, 'CAT' viene confrontato con 'DOG'. Otteniamo i seguenti risultati successivi:

    'CHAT'    'CHIEN'
----------------------------
    'C'    =    'C'
    'H'    =    'H'
    'A'    <    'I'

Dopo quest'ultimo confronto, la stringa 'CAT' viene dichiarata più corta della stringa 'DOG'. La relazione 'CAT' < 'DOG' è quindi vera.

Ora confrontiamo 'CHAT' e 'chat'.

    'CHAT'    'chat'
--------------------------
    'C'    <    'c'

Dopo questo confronto, la relazione 'CHAT' < 'chat' viene dichiarata vera.

Esempi

SQL> select titre from biblio

SQL> select titre from biblio where upper(titre) between 'L' and 'M'

4.2.2.4. L'operatore LIKE

L'operatore LIKE si utilizza come segue: stringa LIKE pattern

La condizione è vera se string corrisponde a pattern. Il pattern è una stringa che può contenere due caratteri jolly:

%	che indica una sequenza qualsiasi di caratteri
_	che sta per un singolo carattere qualsiasi

Esempi

SQL> select titre from biblio

SQL> select titre from biblio where titre like 'M%';

SQL> select titre from biblio where titre like 'L_ %';

4.2.2.5. L'operatore di concatenazione

SQL > select '[' || titre || ']' from biblio where upper(titre) LIKE 'L_ %'

4.2.3. Espressioni con operandi di tipo data

Siano date1, date2 e date3 delle date. È possibile utilizzare i seguenti operatori:

Operatori relazionali

date1 < date2	è vero se date1 è precedente a date2
date1 <= date2	è vero se data1 è precedente o uguale a data2
data1 > data2	è vero se data1 è successiva a data2
date1 >= date2	è vero se data1 è uguale o successiva a data2
data1 = data2	è vero se data1 e data2 sono identiche
data1 ≠ data2	è vero se data1 e data2 sono diverse.
date1 != date2	equivalente a
date1 BETWEEN date2 AND date3	è vero se data1 è compresa tra data2 e data3
data1 IN (elenco di date)	è vero se data1 è nell'elenco delle date
date1 IS NULL	è vero se data1 non ha alcun valore
date1 NON È NULL	è vero se date1 ha un valore
date1 LIKE pattern	è vero se date1 corrisponde al pattern
ALL, ANY, EXISTS

Operatori aritmetici

data1 - data2	: numero di giorni tra data1 e data2
data1 - numero	: data2 tale che data1 - data2 = numero
data1 + numero	: data2 tale che data2 - data1 = numero

Esempi

SQL> select achat from biblio

SQL>select achat from biblio
   where achat between '01.01.1988' and '31.12.1988';

SQL> select titre, achat from biblio
where cast(achat as char(10)) like '1988'

Quanti anni hanno i libri della biblioteca?

SQL> select titre, cast('now' as date)-achat "age(jours)" from biblio

4.2.4. Espressioni con operandi booleani

Ricordiamo che un valore booleano o logico può assumere due valori: vero o falso. L'operando logico è spesso il risultato di un'espressione relazionale.

Siano boolean1 e boolean2 due valori booleani. Esistono tre possibili operatori, che sono, in ordine di precedenza:

boolean1 AND boolean2	è vero se sia boolean1 che boolean2 sono veri.
boolean1 OR boolean2	è vero se boolean1 o boolean2 è vero.
NOT boolean1	ha un valore che è l'inverso del valore di boolean1.

Esempi

SQL> select titre,genre,prix from biblio order by prix desc

Stiamo cercando libri in una determinata fascia di prezzo:

SQL> select titre,genre,prix from biblio
     where prix>=130 and prix <=170

Ricerca inversa:

SQL> select titre,genre,prix from biblio
     where prix<130 or prix >170
     order by prix asc

Attenzione alla precedenza degli operatori!

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and prix>200 or prix<100
  order by prix asc

Utilizza le parentesi per controllare la precedenza degli operatori:

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and (prix>200 or prix<100)
  order by prix asc

4.3. Funzioni predefinite di Firebird

Firebird dispone di funzioni predefinite. Queste non possono essere utilizzate immediatamente nelle istruzioni SQL. È necessario prima eseguire lo script SQL <firebird>\UDF\ib_udf.sql, dove <firebird> si riferisce alla directory di installazione del DBMS Firebird:

Con IBExpert, si procede come segue:

• utilizziamo lo strumento [Script Executive] accessibile tramite l'opzione [Tools/Script Executive]:

• Una volta aperto lo strumento, carichiamo lo script <firebird>\UDF\ib_udf.sql:

• quindi eseguiamo lo script:

Una volta fatto ciò, le funzioni predefinite di Firebird saranno disponibili per il database a cui eri connesso al momento dell'esecuzione dello script. Per verificarlo, basta andare nell'esploratore del database e cliccare sul nodo [UDF] relativo al database in cui sono state importate le funzioni:

Sopra sono riportate le funzioni disponibili per il database. Per testarle, è utile disporre di una tabella a riga singola. Chiamiamola TEST:

e definiamola come segue (clic destro su Tabelle / Nuova tabella):

Aggiungiamo una singola riga a questa tabella:

Ora apriamo l'editor SQL (F12) ed eseguiamo la seguente istruzione SQL:

SQL> select cos(0) from test

che utilizza la funzione cos (coseno) predefinita. Il comando sopra riportato calcola il valore di cos(0) per ogni riga della tabella TEST, quindi di fatto per una singola riga. Visualizza quindi semplicemente il valore di cos(0):

Le UDF (funzioni definite dall'utente) sono funzioni che gli utenti possono creare, e sul web si possono trovare librerie di UDF. Qui descriviamo solo alcune di quelle disponibili con la versione scaricabile di Firebird (2005). Le classifichiamo in base al tipo predominante dei loro parametri o in base al loro ruolo:

• funzioni con parametri numerici
• funzioni con parametri di tipo stringa

4.3.1. Funzioni con parametri numerici

abs(numero)	valore assoluto di numero abs(-15) = 15
ceil(numero)	il numero intero più piccolo maggiore o uguale a numero ceil(15,7) = 16
floor(numero)	il numero intero più grande minore o uguale a numero floor(14,3) = 14
div(numero1, numero2)	quoziente della divisione intera (il quoziente è un numero intero) di numero1 per numero2 div(7,3)=2
mod(numero1, numero2)	resto della divisione intera (il quoziente è un numero intero) di numero1 per numero2 mod(7,3)=1
sign(numero)	-1 se numero < 0 0 se numero=0 +1 se numero > 0 segno(-6) = -1
sqrt(numero)	radice quadrata di numero se numero >= 0 -1 se numero < 0 sqrt(16) = 4

4.3.2. Funzioni con parametri stringa

ascii_char(numero)	numero del carattere nel codice ASCII ascii_char(65) = 'A'
lower(stringa)	converte la stringa in minuscolo lower('INFO')='info'
ltrim(stringa)	Taglia a sinistra - Gli spazi che precedono il testo nella stringa vengono rimossi: ltrim(' kitten')='kitten'
replace(stringa1, stringa2, stringa3)	Sostituisce string2 con string3 in string1. replace('gatto e cane','gatto','**')='**atto e **ane'
rtrim(string1, string2)	Right Trim - uguale a ltrim ma a destra rtrim('gatto ')='gatto'
substr(stringa, p, q)	sottostringa di una stringa che inizia nella posizione p e termina nella posizione q. substr('kitten',3,5)='to'
ascii_val(carattere)	Codice ASCII di un carattere ascii_val('A')=65
strlen(stringa)	numero di caratteri nella stringa strlen('kitten')=6