Database  »  Guide  »  Guida MySQL 

Funzioni su date e ore

Teniamo presente che le date vengono fornite da MySQL nel formato 'AAAA-MM-GG', mentre le ore sono nel formato 'HH:MM:SS'. Il timestamp è formato dalle due stringhe separate da uno spazio.

CURRENT_DATE e CURRENT_TIME restituiscono, rispettivamente, la data e l'ora attuali; CURRENT_TIMESTAMP e NOW, che sono sinonimi, restituiscono data e ora. DATE estrae la parte data da un timestamp; TIME fa lo stesso con la parte ora.

Ci sono poi le funzioni che estraggono da una data, da un orario o da un timestamp le singole informazioni: DAY restituisce il giorno del mese, DAYOFWEEK il giorno della settimana (1 è la domenica e 7 il sabato), DAYOFYEAR il giorno dell'anno (da 1 a 366), MONTH il mese, YEAR l'anno; HOUR, MINUTE e SECOND, rispettivamente, ore, minuti e secondi.

In questi esempi ipotizziamo che in questo momento siano le 17.35.21 del 28 dicembre 2005:

SELECT NOW();
-> 2005-12-28 17:35:21
SELECT CURRENT_TIME();
-> 17:35:21
SELECT DATE(NOW()); (equivale a CURRENT_DATE())
-> 2005-12-28
SELECT MONTH(NOW());
-> 12
SELECT DAYOFWEEK(NOW());
-> 4
SELECT MINUTE(NOW());
-> 35

È possibile anche lavorare con il timestamp Unix (cioè il numero di secondi trascorsi dal 1 gennaio 1970): la funzione FROM_UNIXTIME accetta in input tale timestamp e restituisce un timestamp MySQL, mentre UNIX_TIMESTAMP lavora al contrario, cioè restituisce il timestamp Unix a partire da quello MySQL o da una semplice data.

SELECT UNIX_TIMESTAMP('2005-12-28 17:35:21');
-> 1135787721
SELECT FROM_UNIXTIME(1135787721);
-> 2005-12-28 17:35:21

Ci sono infine due funzioni (DATE_FORMAT e TIME_FORMAT) che permettono di formattare i valori di date e ore. Esse richiedono in input una data o un timestamp (DATE_FORMAT) o un orario (TIME_FORMAT), nonchè una stringa contenente i simboli per la formattazione voluta.

Elenchiamo alcuni di questi simboli seguiti da qualche esempio:

• Anno: '%Y' (4 cifre), '%y' (2 cifre)
• Mese: '%m' (2 cifre), '%c' (1 o 2 cifre), '%M' (nome intero in inglese), '%b' (nome abbreviato)
• Giorno del mese: '%d' (due cifre), '%e' (1 o 2 cifre)
• Giorno della settimana: '%W' (nome intero in inglese), '%a' (nome abbreviato), '%w' (numerico con 0=domenica e 6=sabato)
• Ore: '%H' (2 cifre), '%k' (1 o 2 cifre), '%h' (2 cifre da 01 a 12), '%l' (1 o 2 cifre da 1 a 12)
• Minuti: '%i' (2 cifre)
• Secondi: '%s' (2 cifre)

SELECT DATE_FORMAT('2006-01-09 08:59:15','%d/%m/%y %H.%i');
-> 09/01/06 08.59
SELECT DATE_FORMAT('2006-01-09 08:59:15','%W %e %b %Y - %k:%i');
-> Monday 9 Jan 2006 - 8:59

Funzioni per il criptaggio e la decriptazione dei dati

Le funzioni che effettuano criptaggio irreversibile sono: PASSWORD, utilizzata da MySQL per le password degli utenti; OLD_PASSWORD, da utilizzare per ottenere le password codificate con l'algoritmo utilizzato fino a MySQL 4.0 (vedere lez.7); MD5 e SHA1, che sfruttano noti algoritmi di hashing il cui risultato sono stringhe di, rispettiamente, 32 e 40 caratteri. é consigliabile usare uno di questi ultimi due per memorizzare le password delle vostre applicazioni.

Abbiamo poi le funzioni AES_ENCRYPT e AES_DECRYPT, che sfruttano un algoritmo reversibile per codificare e decodificare una stringa attraverso una password. Se volete memorizzare le stringhe codificate è consigliabile utilizzare una colonna di tipo BLOB, in quanto si tratta di stringhe binarie. MySQL consiglia questa coppia di funzioni come il metodo più sicuro disponibile per il criptaggio di stringhe.

SELECT PASSWORD('albero');
-> *7D69DA9FAAC2BF8B8181DCABB4E38B9DA1916389
SELECT MD5('albero');
-> 338a96591f778e7af4cce7b601d785d2
SELECT SHA1('albero');
-> 0389de785fafb5418e0221c49bb9545a9a82a2b1
INSERT INTO tabella SET campoBlob = AES_ENCRYPT('albero','pw');
SELECT AES_DECRYPT(campoBlob,'pw') FROM tabella
-> albero

Nell'ultimo esempio salviamo su un campo di tipo BLOB la stringa 'albero' criptata con la password 'pw'; successivamente possiamo recuperarla utilizzando la funzione di decodifica con la stessa password.

Funzioni di aggregazione

Le funzioni di aggregazione non lavorano, come quelle viste finora, su un solo dato, ma su insiemi di dati. Ciò significa che ci restituiranno un unico valore come "sintesi" di n valori. Normalmente i valori NULL vengono ignorati.

Ecco le principali di queste funzioni:

• COUNT conta i valori trovati
• AVG ne calcola la media
• MIN e MAX trovano il minore e il maggiore
• SUM effettua la somma

Esempi:

SELECT COUNT(*) FROM tab1
-> conta il numero di righe della tabella
SELECT COUNT(colonna) FROM tab1
-> conta i valori non NULL di colonna
SELECT COUNT(DISTINCT colonna) FROM tab1
-> conta i diversi valori (duplicati valgono per 1)
SELECT AVG(colonna) FROM tab1
-> calcola la media dei valori
SELECT AVG(DISTINCT colonna) FROM tab1
-> calcola la media conteggiando una sola volta i duplicati
SELECT MAX(colonna) FROM tab1
-> trova il valore massimo
SELECT SUM(colonna) FROM tab1
-> calcola la somma

Le funzioni di aggregazione vengono usate spesso in combinazione con la clausola GROUP BY (vedere lez.14)).
In questi casi le aggregazioni non vengono svolte su tutte le righe estratte dalla WHERE, ma singolarmente su ogni gruppo di righe formato dalla GROUP BY. In questo caso è possibile aggiungere alla query la clausola WITH ROLLUP che produce subtotali ad ogni cambiamento di valori dei campi raggruppati.

SELECT anno, paese, prodotto, SUM(profitti)
FROM vendite GROUP BY anno, paese, prodotto WITH ROLLUP

Questa query genererà totali suddivisi per prodotto, per paese e per anno, ma fornirà anche i totali per paese, per anno e infine un totale generale. Nella tabella risultato le colonne su cui vengono calcolati i totali generali sono mostrate con valore NULL (ad esempio nella riga finale con il totale generale saranno a NULL i valori di anno, paese e prodotto).

Lo standard SQL vuole che in una SELECT che contiene funzioni di aggregazione tutte le colonne su cui tali funzioni non lavorano siano comprese nella GROUP BY. Ad esempio questa query non sarebbe valida, perchè manca la GROUP BY sul campo nome:

SELECT ordini.idCliente, clienti.nome, MAX(pagamenti)
FROM ordini,clienti
WHERE ordini.idCliente = clienti.id
GROUP BY ordini.idCliente

La query ha tuttavia un senso logico, perchè il valore di nome, dipendendo dalla join, sarà sempre lo stesso in ogni gruppo di righe con lo stesso 'idCliente': di conseguenza è superfluo inserirlo nella GROUP BY. MySQL quindi ci consente di usare questa sintassi: bisogna però fare attenzione a non omettere la GROUP BY su un campo che non ha un valore unico, in quanto ne otterremmo un risultato non prevedibile.

Ricerche full-text

Le ricerche full-text sono ricerche basate sul "linguaggio naturale", effettuabile su campi di tipo CHAR, VARCHAR e TEXT. Si tratta del tipo di interrogazione che normalmente facciamo quando utilizziamo un motore di ricerca. Per poterla effettuare è necessario che le colonne interessate facciano parte di un indice FULLTEXT (vedi lez.12) e che la tabella sia di tipo MyISAM. Le ricerche vengono effettuate con la funzione MATCH:

SELECT * FROM articoli
WHERE MATCH(titolo,testo) AGAINST('database')

Questa query effettua la ricerca del termine 'database' sulle colonne titolo e testo della tabella, ed estrae quelle che hanno una qualche rilevanza. Le due colonne devono far parte dello stesso indice FULLTEXT.

SELECT id, titolo, MATCH(titolo,testo) AGAINST('database') as rilevanza
FROM articoli
WHERE MATCH(titolo,testo) AGAINST('database')

In questo caso, oltre all'id e al titolo ci verrà fornito, nella colonna 'rilevanza', anche il risultato della funzione MATCH, che è un numero compreso fra 0 e 1.

Segnaliamo anche la funzione FOUND_ROWS, da utilizzare per ottenere il numero di righe che sarebbero state trovate da una SELECT in cui abbiamo usato la LIMIT, se tale clausola non fosse stata presente:

SELECT SQL_CALC_FOUND_ROWS * FROM tabella LIMIT 10;
SELECT FOUND_ROWS();

Nella prima istruzione selezioniamo il contenuto di una tabella limitandolo alle prime 10 righe; tuttavia l'uso della clausola SQL_CALC_FOUND_ROWS indica a MySQL che vogliamo sapere quante sarebbero le righe estratte senza la LIMIT; otteniamo poi tale valore con la seconda istruzione, che deve essere immediatamente successiva.

UNION

Concludiamo con la sintassi della UNION, che non è una funzione ma una clausola che ci consente di unire due query in una unica tabella risultato. La condizione per poter fare questo è che le due query selezionino lo stesso numero di colonne e che le colonne in posizione corrispondente siano dello stesso tipo.

Vediamo l'esempio più semplice:

SELECT cola, colb, colc FROM tab1
UNION
SELECT col1, col2, col3 FROM tab2

Da notare che, in una UNION, viene applicata di default la clausola DISTINCT, cioè le righe duplicate non vengono restituite. Se volete il comportamento opposto dovete specificare UNION ALL.

Altri articoli

Altre guide

Altre newsletter