Raccolta informazioni in unico testo MySQL (materiale reperito in siti internet)

Sito interessante dove reperire materiale: http://www.risorse.net

Sito di riferimento: http://www.mysql.com

I e D rappresentano i numeri Interi e Decimali ammessi.

Ad esempio FLOAT(2,3) indica a MySQL di salvare fino a due cifre per la parte intera di un numero e 3 per quella decimale. Un numero come 45,7869 diventerà 45,787. DOUBLE e DECIMAL funzionano nella stessa maniera ma possono contenere valori maggiori.

I modificatori dei tipi numerici: AUTO_INCREMENT, UNIQUE e ZEROFILL
Il primo modificatore, AUTO_INCREMENT, permette di creare un campo numerico che aumenta ogni nuova riga.

Il secondo modificatore per i dati numerici, UNIQUE, identifica un campo i cui valori sono uno diverso dall'altro.

L'ultimo modificatore ammesso da MySQL per i campi numerici è ZEROFILL, che consente di inserire tanti 0 quanti sono ammessi dal tipo di campo, prima della cifra che realmente viene salvata. Ad esempio, con un campo di tipo INT(5) e ZEROFILL, per salvare un numero come 78, questo verrà immagazzinato nella forma 00078.

(*) X è lo spazio occupato dal testo all'interno del campo

CHAR e VARCHAR
Questi due tipi di campi, nonostante la somiglianza nel nome, si comportano in maniera molto diversa. Il primo ha una lunghezza fissa, mentre il secondo è variabile.
Ciò significa che se creassimo un campo CHAR(9) e al suo interno specificassimo "ciao", questo campo occuperebbe comunque 9 byte invece di 4. Con VARCHAR(9) invece, scrivendo al suo interno "ciao" il campo occuperebbe 5 byte (guardare la tabella superiore X+1 dove in questo caso è X=4, quindi 4+1=5).

TEXT e BLOB
TEXT e BLOB sono i campi di MySQL dedicati a contenere grandi quantità di dati. Fino a 4,2 GB con i LONGTEXT e LONGBLOB.
Il secondo in particolare, il campo di tipo BLOB sta per Bynary Large Object e consente il salvataggio di interi file nel formato binario. Utile per nascondere file dietro username e password, senza così riuscire a rintracciare il percorso fisico del file (che infatti non esiste, essendo incluso direttamente nel database).

I modificatori
I modificatori previsti da questi tipi di campi sono:

• BINARY:
  ammesso dai campi CHAR e VARCHAR: i dati salvati saranno trattati come stringhe binarie.

• DEFAULT:
  ammesso da tutti i tipi di campi: imposta un valore predefinito nel caso il campo fosse lasciato vuoto.

• NOT NULL:
  ammesso da tutti i tipi di campi: impone che il campo non sia lasciato vuoto.

• NULL:
  ammesso da tutti i tipi di campi: se il campo non contiene un valore, sarà vuoto.

• PRIMARY KEY:
  ammesso da tutti i campi, ma è consigliabile impostarlo su dati di tipo numerico. Serve a impostare un indice i quali dati non devono essere vuoti.

Gli altri modificatori: UNIQUE, UNSIGNED e ZEROFILL sono stati trattati nella puntata dedicata ai tipi di dati numerici.

A=anno, M=mese, G=giorno, h=ora, m=minuti, s=secondi

DATETIME
E' il formato più completo e preciso a nostra disposizione. Varia da 1000-01-01 00:00:00 a 9999-12-31 23:59:59

DATE
Uguale al precedente, ma senza l'ora. Ammette infatti dati a partire da 1000-01-01 al 9999-12-31.

TIME
Salva l'ora. I valori vanno da 00:00:00 a 23:59:59. E' possibile però salvare intervalli di valore tra un evento e un altro e qundi, ammettere ore differenti. In questo caso, i dati vanno da -838:59:59 a 838:59:59.
MySQL legge i valori partendo da destra, quindi, salvando un campo con il contenuto 8:32, nel database verrà interpretato come 00:08:32.
Oltre ai due punti ( : ) MySQL ammette altri segni di interpunzione senza particolari difficoltà.
L'immissione di un valore sbagliato sarà salvato come mezzanotte in punto (00:00:00).

YEAR
Salva l'anno ed è il campo più leggero: 1 solo byte. Ammette valori dal 1901 al 2155. Gli anni possono essere salvati a due o a quattro cifre. MySQL, in caso di anni a due cifre, interpreterà i valori da 70 a 99 come dal 1970 al 1999. Quelli dall'1 al 69, come dal 2001 al 2069.
Per evitare fraintendimenti quindi, è consigliabile impostare l'anno a quattro cifre.

TIMESTAMP
Questo campo salva (nel formato scelto dal numero tra le parentesi, si veda la tabella superiore) il momento esatto in cui la tabella viene modificata. Quindi può essere utile per visualizzare (senza doverlo calcolare ogni volta) il momento dell'ultima modifica del record in cui il campo TIMESTAMP appartiene.
Ammette anni compresi tra il 1970 e il 2037.
Tutti i tipi di TIMESTAMP occupano lo stesso spazio: 4 byte. Perché questo? Nonostante i vari formati? Perché MySQL salva comunque tutti i dati e poi ne visualizza solo quelli richiesti. Ad esempio, con TIMESTAMP(2) il database visualizza solo due cifre dell'anno, ma in memoria ha tutti gli altri dati (anno a 4 cifre, mese, giorno, ora, minuti e secondi). Quando infatti modifichiamo il tipo di TIMESTAMP, ad esempio con TIMESTAMP(8) lui ha tutti i dati in memoria.
La stessa cosa avviene quando abbassiamo il valore di TIMESTAMP, visualizzando quindi meno dati. MySQL non cancellerà i vari valori, semplicemente li nasconderà.

I tipi di campi che ancora non abbiamo trattato sono i campi a scelta. Quando l'utente dovrà selezionare per forza una delle voci previste (ad esempio da un menù a tendina: <select>) è bene porre la propria attenzione su questi campi, perché MySQL ci accede più rapidamente di quelli testuali.

ENUM
Indica a MySQL le varie possibilità previste. Ad esempio, con:

ENUM('mare','montagna','lago')

Si impone l'utente la scelta di uno di queste tre possibilità. Altri valori, saranno trattati come valori vuoti (NULL), a meno che non sia definito un valore di default. Si possono inserire fino a 65.535 voci.

Tornando all'esempio precedente: ENUM('mare','montagna','lago') a questo tipo di campo (che chiameremo "scelta_vacanze") è possibile selezionare una voce come se ci si trovasse di fronte a un array (che parte da 1 come in VB e derivati - tipo VBScript).
Ad esempio, con:

SELECT scelta_vacanze FROM nomeTabella WHERE scelta_vacanze = 2

Avremo come risultato il valore "montagna".

SET
Questo tipo di dato è uguale a ENUM, con la differenza di poter effettuare una scelta multipla. Il campo ENUM infatti, consente di scegliere un solo valore alla volta.

Linguaggio SQL di base

SQL, acronimo di Structured Query Language, è un linguaggio utilizzato per manipolare database.
MySQL sfrutta proprio SQL per interagire con gli utenti, attraverso dei comandi comunemente chiamati query.
Una query permette di "parlare" al database e consente di effettuare operazioni sul suo contenuto o sulla sua struttura.

Per selezionare i campi di un database, si usa il termine SELECT. Ammettiamo di dover estrarre il contenuto da tutti i campi di una tabella di nome "clienti", faremo:

SELECT * FROM clienti;

SELECT ordini FROM clienti;

Se i campi fossero più di uno, è necessario separare i vari valori con una virgola ( , ) ad esempio:

SELECT ordini, citta, fatture FROM clienti;

Affinare la query: la clausola WHERE
Può essere necessario specificare meglio una query in modo tale da avere un risultato vicino alle nostre esigenze. Ad esempio, potremmo voler estrarre solo i nomi dei clienti della città di Milano, ecco come fare:

SELECT nome FROM clienti WHERE citta = 'milano';

In questo modo, avremo come risultato solo i nomi dei clienti che hanno sede a Milano.

Più in profondità: AND e OR
Facendo riferimento all'esempio di prima, potremmo richiedere i nomi dei clienti che hanno sede a Milano e hanno effettuato più di 10 ordini. Ecco come:

SELECT nome FROM clienti WHERE citta = 'milano' AND ordini <= 10;

Con la query sopra specificata, avremo sottomano i clienti di Milano con un numero di ordini maggiore o uguale a 10. Il termine AND può essere tranquillamente sostituito da due e commerciali ( && ).

Il termine OR, permette di creare un'alternativa. Ad esempio, se volessimo estrarre i clienti con sede a Milano oppure a Napoli, faremo:

SELECT nome FROM clienti WHERE citta = 'milano' OR citta = 'napoli';

Il termine OR può essere sostituito dalle due barre verticali ( || ).

Ordiniamo i dati: ORDER BY
Per ordinare l'estrazione di una tabella, si può usare ORDER BY, applicandolo magari assieme ai termini ASC (di default) e DESC.

Vediamo un esempio:

SELECT * FROM clienti ORDER BY ordini DESC;

In questo modo avremo i clienti estratti secondo il numero di ordini effettuati. Con DESC specifichiamo dal numero più alto a quello più basso.
Al contrario, ASC ordina dal numero più basso al più alto ed è di default.

Quindi, tornando all'esempio precedente, per avere i nomi delle aziende clienti in ordine alfabetico, faremo:

SELECT * FROM clienti ORDER BY nome ASC;

ORDER BY multipli
Si possono ordinare i campi anche in base a più valori. Ammettiamo di volere i risultati delle query precedenti, dai clienti che hanno fatto più ordini a quelli che ne hanno fatti meno, in ordine alfabetico.

SELECT * FROM clienti ORDER BY nome, ordini DESC;

In questo modo, avremo le società in ordine alfabetico e da chi ha fatto più ordini a chi ne ha fatti di meno.

Operatori matematici

Quando si eseguono query SQL, è importante sapere quali operatori possono essere usati per confrontare un campo con un altro, oppure con un valore da noi specificato.

I più usati sono:

Un approfondimento a parte merita LIKE, che consente di effettuare una comparazione tra campi simili, anche non uguali.

Ad esempio, ammettiamo di voler estrarre, dalla tabella clienti, quelle aziende che contengano nella descrizione della società la parola Internet. Ecco come fare:

SELECT nome FROM clienti WHERE descrizione LIKE '%internet%'

I due simboli di percentuale ( % ), servono a MySQL per sapere che prima e dopo il termine internet, potrebbero esserci altre parole. Se non avessimo usato questa query, magari usando WHERE descrizione = 'internet', il database avrebbe cercato quelle aziende che contengano nel campo descrizione la sola parola internet.

Possiamo anche usare le % in maniera diversa. Ad esempio:

SELECT nome FROM clienti WHERE citta LIKE 'mila%'

In questo modo, MySQL estrarrà tutte quei clienti che hanno come sede la parola mila seguita da altro testo, ad esempio: Milano, Milazzo, Milano Marittima ecc.

Con MySQL è possibile eseguire delle query utilizzando all'interno della sintassi SQL degli operatori matematici, che sono i classici della somma, sottrazione, moltiplicazione e divisione.

Somma
Possiamo sommare due o più campi per ottenere un nuovo campo, ad esempio:

SELECT (primoCampo + secondoCampo) AS totale FROM nomeTabella;

In questo modo, avremo una colonna di nome "totale" in cui saranno contenute tutte le somme dei due campi.

Differenza
Come per la somma, possiamo fare:

SELECT (primoCampo - secondoCampo) AS differenza FROM nomeTabella;

Moltiplicazione
All'interno delle query è possibile anche moltiplicare i valori di più campi, ad esempio:

SELECT (primoCampo * secondoCampo) AS risultato FROM nomeTabella;

Divisione
E ancora, per la divisione, possiamo usare:

SELECT (primoCampo / secondoCampo) AS risultato FROM nomeTabella;

Elevazione a potenza
L'elevazione a potenza con MySQL si può ottenere usando due funzioni: POW(x,y) o POWER(x,y), dove x rappresenta la base della potenza e y l'esponente. Ecco un esempio:

SELECT POW(10,3);

Restituisce: 1000.000000

Radice quadrata
La radice quadrata non negativa di un numero si ottiene con:

SELECT SQRT(9);

E restituisce: 3.000000

Casi pratici
Gli operatori matematici possono essere molto comodi anche per ordinare dei risultati. Ammettiamo di avere una tabella che contenga i voti totali assegnati a un articolo e il numero di persone che hanno votato. Per ordinare i records così da ottenere gli articoli più apprezzati, faremo:

SELECT id,titolo FROM articoli ORDER BY (voti_totali / numero_voti) DESC;

Per migliorare la query e avere sottomano anche la media dei voti di ogni articolo, possiamo crearci un campo provvisorio con il comando AS, ecco come:

SELECT (voti_totali / numero_voti) AS mediaVoto, id, titolo FROM articoli ORDER BY mediaVoto DESC

Nella puntata dedicata agli operatori matematici di base, abbiamo visto le sintassi SQL per eseguire calcoli su un RDBMS MySQL.

In questa puntata vedremo gli operatori più avanzati, che consentono i calcoli di logaritmi, seni e coseni, tangenti, archi e arrotondamenti.

LEAST
La funzione LEAST restituisce la cifra più piccola di quelle passate come parametri. Ad esempio:

SELECT LEAST(1, 4, 5, 8.6, 0.9);

Restituisce: 0.9

GREATEST
Funzione simile alla precedente, ma ricava il numero più grande. Tipo:

SELECT GREATEST(1, 4, 5, 8.6, 0.9);

Restituisce: 8.6

MOD
Questa funzione da' come risultato il resto di un numero (passato come primo parametro) diviso per l'altro numero (passato come secondo parametro). Vediamo:

SELECT MOD(5,2);

Restituisce 1

FLOOR
La funzione FLOOR arrotonda la cifra specificata all'intero più grande inferiore alla cifra stessa. Ecco un esempio chiarificatore:

SELECT FLOOR(11.5);

Restituisce: 11

CEILING
Questa funzione è molto simile alla FLOOR ma esegue l'arrotondamento al valore minore non inferiore alla cifra stessa. Ecco il solito esempio "schiarsci-idee":

SELECT CEILING(11.5);

Restituisce: 12

ROUND
A questa funzione vengono passati due parametri: nel primo il numero da arrotondare e nel secondo parametro, a quale cifra decimale effettuare l'arrotondamento.
Ecco come:

SELECT ROUND(12.5682,2);

Restituisce: 12.57

Se il secondo parametro non venisse specificato, la cifra viene arrotondata all'intero più grande inferiore alla cifra stessa (proprio come la funzione FLOOR). Ad esempio:

SELECT ROUND(12.5);

Restituisce: 12

EXP
La funzione EXP restituisce la base dei logaritmi naturali elevata alla potenza della cifra indicata. Ecco come:

SELECT EXP(2);

Restituisce: 7.389056

LOG
Questa funzione da' come risultato il logaritmo naturale del numero indicato. Ecco:

SELECT LOG(12);

Restituisce: 2.484907

LOG10
Questa funzione, simile alla precedente, restituisce il logaritmo del numero specificato in base 10.

SELECT LOG10(12);

Restituisce: 1.079181

SIGN
La funzione SIGN consente di ottenere tre risultati diversi in base al segno della cifra indicata. Un numero positivo restituirebbe 1, un numero negativo -1 e un numero nullo (0 - zero) restituirebbe per l'appunto 0. Vediamo tre esempi:

SELECT SIGN(5);

Restituisce: 1

SELECT SIGN(-2);

Restituisce: -1

SELECT SIGN(0);

Restituisce: 0

SIN
La funzione SIN ottiene il seno di una cifra data in radianti:

SELECT SIN(10);

Restituisce: -0.544021

COS
Questa funzione calcola il coseno di un numero dato in radianti:

SELECT COS(10);

Restituisce: -0.839072

TAN
La funzione TAN calcola la tangente di un numero espresso in radianti:

SELECT TAN(10);

Restituisce: 0.648361

ASIN
Questa funzione calcola l'arco seno di un numero. Restituisce NULL se la cifra non fosse compresa tra -1 e 1.

SELECT ASIN(-0.5);

Restituisce: -0.523599

ACOS
Simile alla precedente, ma restituisce ovviamente l'arco coseno della cifra indicata quando quest'ultima fosse compresa tra -1 e 1. Altrimenti restituirebbe NULL.

SELECT ACOS(-0.5);

Restituisce: 2.094395

ATAN
Questa restituisce invece l'arco tangente della cifra indicata:

SELECT ATAN(3);

Restituisce: 1.249046

ATAN2
Questa restituisce invece l'arco tangente delle due cifre indicate, tipo:

SELECT ATAN2(3,4);

Restituisce: 0.643501

COT
La funzione COT restituisce la cotagente della cifra data, ad esempio:

SELECT COT(5);

Restituisce: -0.29581292

DEGREES
Questa funzione converte i numeri da radianti a gradi:

SELECT DEGREES(2);

Restituisce: 114.59155902616

RADIANS
Effettua l'operazione inversa della funzione precedente. Ovvero partendo da un numero in gradi, lo converte in radianti.

SELECT RADIANS(114.59155902616);

Restituisce: 1.9999999999999

Le funzioni condizionali di MySQL consentono di eseguire query verificando che un'istruzione sia vera o falsa.
Come per tutti i linguaggi di programmazione, la condizione e il relativo comportamente viene suddiviso su tre passaggi:

1. La condizione

2. Se la condizione è vera, esegue questa istruzione

3. Se la condizione è falsa, esegue un'altra istruzione

Il classico If ... Then ... Else ...

MySQL prevede diverse sintassi per le verifiche condizionali. Vediamone alcune:

La funzione IF
La funzione IF permette di confrontare dei campi e restituire delle istruzioni diversi a seconda della veridicità della condizione iniziale:

SELECT IF(primoCampo != secondoCampo, 1, 0) FROM nomeTabella;

Con questo tipo di query, MySQL confronta il primoCampo con il secondoCampo. Se fossero diversi, restituirebbe 1, altrimenti 0.

Vediamo un caso banale ma pratico. Possiamo effettuare la verifica condizionale IF anche con dei numeri. Ad esempio:

SELECT IF(1<2, 'vero', 'falso');

In questo caso, MySQL restituisce "vero", perché 1 è minore di 2.

IFNULL
Questa funzione può risultare molto comoda per intercettare i NULL che MySQL potrebbe restituire. IFNULL infatti intercetta i casi di NULL e restituisce ciò che il database administrator desidera.

Può essere utile quando si divide un campo per un altro quando non si sa cosa contengano i due campi (magari perché dinamicamente modificati dagli utenti). Se infatti il secondo campo fosse zero ( 0 ), dividere per 0 restituisce un errore, per MySQL quindi è NULL. Onde evitare questo problema, si usa il condizionale IFNULL. Vediamo un esempio con dei numeri:

SELECT IFNULL(2/0,'impossibile');

In questo caso MySQL, invece di restituire NULL, restituisce "impossibile".

CASE
Questo condizionale consente a MySQL di verificare più campi, come per il Select Case di Visual Basic o Switch() per i linguaggi derivati da C (Java, Javascript, C# ecc.).

Quindi, invece di avere una sola condizione, CASE consente di effettuare più verifiche, ad esempio:

SELECT CASE 1 WHEN primoCampo = 'primoValore' THEN 1 WHEN secondoCampo = 'secondoValore' THEN 2 WHEN terzoCampo = 'terzoValore' THEN 3 ELSE 'nessuna condizione è vera' END;

In questo modo, se uno dei valori del campo primoCampo è uguale a "primoValore", allora MySQL restituisce 1. E così via per il secondoCampo e il terzoCampo. Se nessuna condizione è soddisfatta, MySQL restituisce "nessuna condizione è vera". Il comando END finale, chiude il CASE.

Le funzioni per agire sulle stringhe servono per modificare il testo da inviare a MySQL che poi elaborerà come indicato dalla query.
Hanno il grande vantaggio delle prestazioni: è infatti un'ottima scelta per migliorare la velocità delle applicazioni Web che si appoggiano a MySQL.

LOCATE
La funzione LOCATE consente di ricercare una stringa all'interno di un'altra, indicandogli da che posizione partire. Ad esempio, ammettiamo di dover cercare la parola "guida", all'interno della stringa "questa è proprio una bella guida a MySQL". Come facciamo?

SELECT LOCATE("guida","questa è proprio una bella guida a MySQL",1);

La funzione LOCATE ammette quindi tre valori: la stringa da cercare, la stringa nella quale cercare e l'indice di posizione dal quale iniziare la ricerca. Nel nostro esempio, l'1 fa cominciare la ricerca dal primo carattere. Il valore del punto dal quale iniziare la ricerca, se omesso sarà inteso uguale a 1.
Tornando all'esempio, MySQL restituirebbe 28. Se trovasse due occorrenze, restituirebbe solo la posizione della prima.

LTRIM
Come per VB, questa funzione toglie gli spazi iniziali dal testo. Ad esempio:

SELECT LTRIM("    valore");

Restituirà: "valore"

LTRIM, in assenza di spazi bianchi iniziali, non toglie altri caratteri. Ad esempio:

SELECT LTRIM("valore");

Restituirà comunque "valore"

RTRIM
Simile alla precedente, con la differenza di eliminare gli spazi bianchi alla fine della stringa. Ad esempio:

SELECT RTRIM("valore    ");

Restituirà "valore"

TRIM
Questa funzione riunisce le capacità delle due viste precendentemente. E' infatti in grado di rimuovere gli spazi sia dalla fine che dall'inizio di una stringa. Ad esempio:

SELECT TRIM("    valore    ");

Restituirà "valore"

TRIM è anche in grado di ruomevere gruppi di caratteri da una stringa. Per fare questo, utilizza tre termini:

• TRALINING: Elimina i caratteri all'inizio della stringa

• LEADING: Elimina i caratteri alla fine della stringa

• BOTH: Elimina i caratteri sia all'inizio sia alla fine della stringa

Ad esempio:

SELECT * FROM nomeTabella WHERE nomeCampo = TRIM(TRAILING 'NN' FROM 'NNvalore'); SELECT * FROM nomeTabella WHERE nomeCampo = TRIM(LEADING 'NN' FROM 'valoreNN'); SELECT * FROM nomeTabella WHERE nomeCampo = TRIM(BOTH 'NN' FROM 'NNvaloreNN');

MID
La funzione MID consente di levare caratteri all'inizio e alla fine di una stringa. Il numero di caratteri, sono indicati nel secondo e terzo parametro della funzione (il primo è la stringa). A tal proposito, è bene sapere che MySQL inizia a contare da 1 e che il primo carattere viene identificato dal numero 2.
Ad esempio:

SELECT MID("questa è una guida mysql gratuita", 14, 11);

Restituirà: "guida mysql"

REPEAT
La funzione REPEAT ripete una stringa tante volte quanto specificato. Ad esempio:

SELECT REPEAT("guida mysql", 4);

Restituirà: "guida mysqlguida mysqlguida mysqlguida mysql"

REPLACE
Questa funzione, data una stringa, sostiuisce i caratteri specificati con altri. Ad esempio:

SELECT REPLACE("pippo","p","b");

Restituirà: "bibbo"
Attenzione alla differenza che MySQL fa tra caratteri minuscoli e maiuscoli (è case sensitive).

REVERSE
La funzione REVERSE ribalta una stringa, scrivendola quindi al contrario. Ad esempio:

SELECT REVERSE("admin");

Restituirà: "nimda"

SPACE
Questa funzione restituisce una stringa di spazi, tanti quanti specificati all'interno delle parentesi. Ad esempio: SPACE(5) restituirà 5 spazi.

SUBSTRING
Partendo da una stringa, SUBSTRING restituisce un'altra stringa togliendo tanti caratteri quanto indicato nella funzione (partendo da sinistra).
Ad esempio:

SELECT SUBSTRING("guida a mysql", 9);

Restituirà: "mysql"

Si ricorda che MySQL conta partendo da 1, quindi per levare anche un solo carattere, bisognerà specificare 2. Ad esempio:

SELECT SUBSTRING("guida a mysql", 2);

Restituirà: "uida a mysql"

SUBSTRING_INDEX
Questa è una variante della funzione SUBSTRING appena vista. Questa variante, consente di eliminare del testo partendo da un carattere assegnato e iniziando a contare da sinistra. Ad esempio:

SELECT SUBSTRING_INDEX("www.risorse.net", ".", 2);

Restituirà: "www.risorse"

Questa funzione ha anche l'alternativa dei numeri negativi. In questo caso, il conteggio inizia da destra:

SELECT SUBSTRING_INDEX("www.risorse.net", ".", -2);

Restituirà: "risorse.net"

UCASE e LCASE
Come per VB, le funzioni UCASE e LCASE trasformano il testo rispettivamente in maiuscolo e minuscolo. Ad esempio:

SELECT UCASE("mysql");

Restituirà "MYSQL"

Mentre: SELECT LCASE("MySQL");

Restituirà "mysql"

Ci sono poi molte altre funzioni, che abbiamo preso direttamente dal manuale di MySQL.

Le funzioni aggregate servono per eseguire operazioni matematiche su una o più colonne di MySQL.

COUNT
La funzione COUNT viene utilizzata per recuperare il numero di righe di una colonna. Ad esempio:

SELECT COUNT(*) AS totale FROM nomeTabella;

Questa funzione può essere utilizzata su qualunque tipo di dato.

COUNT(DISTINCT)
Questa funzione restituisce il numero delle diverse combinazioni che non contengono il valore NULL.
Ad esempio, se in una colonna abbiamo 10 righe: 5 contenenti la parola "calcio", 3 contenenti il termine "tennis" e le ultime 2 con "golf", effettuando un COUNT(DISTINCT) avremo il numero di combinazioni diverse, ovvero 3 (calcio, tennis, golf).

SELECT(DISTINCT nomeCampo) FROM nomeTabella;

Per riassumere quindi, se avessimo una tabella di MySQL che raccoglie le registrazioni a un determinato sito, SELECT COUNT(DISTINCT) può essere utile per sapere quanti nomi diversi sono stati usati, oppure quanti diversi titoli di studio ecc.

MAX
Questa funzione restituisce il valore più alto contenuto all'interno di una colonna. Per i campi numerici, restituisce il numero più alto, per quelli testuali (nei nuovi MySQL questa operazione è permessa) seleziona il campo che secondo l'ordine alfabetico è più avanti (ad esempio due valori: Alessandro e Filippo prende Filippo)

SELECT MAX(nomeCampo) FROM nomeTabella;

MIN
Questa funzione fa esattamente l'opposto della precedente: prende il valore più basso. Ecco un esempio:

SELECT MIN(nomeCampo) FROM nomeTabella;

AVG
Restituisce una media dei valori presenti in un campo. Da applicare ai soli campi numerici:

SELECT AVG(nomeCampoNumerico) FROM nomeTabella;

SUM
La funzione SUM somma i valori contenuti nel campo:

SELECT SUM(nomeCampoNumerico) FROM nomeTabella;

Anche questa funzione, va applicata ai soli campi numerici.

STD
Questa è una funzione utile per gli statistici. Calcola infatti la distanza di un valore dalla media, e si ottiene con:

SELECT STD(nomeCampoNumerico) FROM nomeTabella;

GROUP BY
La clausola GROUP BY consente di raggruppare un set di risultati in presenza di una delle funzioni aggregate previste da MySQL.

Ammettiamo di avere una tabella con tre voci:

• id (INT e AUTO_INCREMENT)

• ordini (TINYINT)

• cliente (VARCHAR)

Per sapere quale sia la distanza dalla media degli ordini di ogni cliente, faremo:

SELECT STD(ordini) AS dispersione, cliente FROM nomeTabella GROUP BY cliente;

In questo modo avremo due tabelle, "dispersione" che contiene la distanza della media degli ordini e "cliente" contenente appunto il nome del cliente.

Nella puntata SQL di base all'interno di questa guida a MySQL, abbiamo visto che SQL è a tutti gli effetti un linguaggio. In quanto tale, ha regole grammaticali e strutturali che vanno rispettate, così da interagire alla perfezione con il database.

Tra queste regole, come per quasi tutti i linguaggi, esistono nomi riservati che non possono essere utilizzati:

ALTER
AND
AS
CREATE
CROSS JOIN
DELETE
DROP
FROM
FULL JOIN
GROUP BY
INSERT
INTO
JOIN
LEFT JOIN
LIKE
LIMIT
ON
OR
ORDER BY
RIGHT JOIN
SELECT
SET
UPDATE
WHERE

Altri consigli nell'assegnazione dei nomi
Quando andiamo assegnare i nomi delle nostre tabelle e dei nostri campi, esistono altre piccole regole che è meglio seguire. E' bene evitare caratteri particolari all'interno dei nomi. Per un migliore funzionamento, è bene utilizzare solo:

• Caratteri alfanumerici

• Per inserire spazi tra le parole, usare il simbolo underscore ( _ )

E nient'altro. E' bene quindi evitare altri simboli ( ? , . ` ' " @ ù à ò ì è é + * ecc.), utilizzare il carattere di spaziatura o anche il semplice trattino ( - ).
Questo ultimo simbolo, merita un approfondimento a parte. Se ci trovassimo di fronte a un campo dal nome:

voti-totali

Per estrarlo, magari dalla tabella "voti", dovremmo fare:

SELECT `voti-totali` FROM voti

Questo perché la presenza del trattino ( - ) ci impone l'utilizzo del simbolo ` per raggruppare il nome del campo (o della tabella).

Migliorare la leggibilità delle query
Una pratica che seguo da molto tempo per migliorare la leggibilità delle query SQL (con MySQL o altri database) è quella di utilizzare i comandi in maiuscolo e i nomi dei campi in minuscolo. Questo mi è molto utile quando devo scrivere query più strutturate, ad esempio:

SELECT * FROM fornitori WHERE id_prodotto IN (SELECT id_prodotto FROM prodotti_spediti AS o, mezzo_di_trasporto AS s WHERE s.mezzo_di_trasporto LIKE 'gomma%' AND s.mezzo_di_trasporto=o.mezzo_usato)

Casi particolari: gli escape
Esistono casi in cui è necessario applicare gli escape ad alcuni caratteri. Ammettiamo di voler realizzare una query simile:

SELECT * FROM nomeTabella WHERE titolo = 'L'utilità di MySQL';

In questo caso, avremo un errore perché non è possibile usare l'apice sigolo ( ' ) quando abbiamo già usato gli apostrofi come delimitatori del valore del campo. E' necessario quindi applicare l'escape al carattere apostrofo, come segue:

SELECT * FROM nomeTabella WHERE titolo = 'L\'utilità di MySQL';

Un altro carattere che va obbligatoriamente sottoposto a escape è la backslash ( \ ). Come segue:

INSERT INTO nomeTabella VALUES('La backslash (\\) va sottoposta a escape con il segno \\');

Funzioni automatiche per gli escape
Spesso MySQL viene utilizzato nel Web associato ad un linguaggio di sviluppo, come PHP, oppure a una tecnologia lato server come ASP.
Per effettuare gli escape dei caratteri quindi, si possono usare funzioni predefinite:

PHP
Con PHP è possibile sfruttare alcune funzioni predefinite, come ad esempio:

• mysql_escape_string()
  Aggiunge le sequenze di escape in una stringa (implementata dalla versione 4.0.3 di PHP).

• mysql_real_escape_string()
  Aggiunge le sequenze di escape ai caratteri speciali in una stringa per l'uso in una istruzione SQL, tenendo conto dell'attuale set di caratteri della connessione (presente dalla versione 4.3.0 di PHP).

PHP comunque, ha molte altre funzioni dedicate a MySQL, disponibili nella documentazione ufficiale.

Per aggiungere una riga all'interno di una tabella in MySQL, si utilizza il termine INSERT.

Ammettiamo ora di voler aggiungere una nuova voce all'interno della tabella clienti:

INSERT INTO clienti(id,nome,descrizione,citta,ordini) VALUES('102','WebMilano Enterprise','Web agency','Milano','1')

In questo modo, abbiamo inserito i nuovi valori specificati tra le parentesi del termine VALUES all'interno della tabella clienti (INSERT INTO), i cui campi sono specificati anch'essi tra parentesi tonde.

Se dovessimo inserire tutti i valori nella nuova riga, possiamo anche omettere i vari campi all'interno della tabella. Ad esempio:

INSERT INTO clienti VALUES('102','WebMilano Enterprise','Web agency','Milano','1')

I campi che possono essere lasciati vuoti (perché auto incrementali, null ecc.) possono anche non essere inseriti all'interno della query.
Ammettiamo che "id" sia un campo incrementale e che "ordini" abbia come valore predefinito 1. Possiamo evitare di specificarli, ad esempio:

INSERT INTO clienti(nome,descrizione,citta) VALUES('WebMilano Enterprise','Web agency','Milano')

Durante l'operazione di inserimento, dobbiamo fare attenzione che a ogni campo specificato tra le parentesi tonde all'interno della tabella, sia associato un valore interno a VALUES. Se ad esempio inserissimo per errore più valori all'interno di VALUES rispetto a quanti ne avessimo specificati dopo INTO nomeTabella, MySQL ci risponderebbe con un errore simile:

ERROR 1136 - Column count doesn't match value count at row N

Dove N è la riga dell'errore.

In questo caso, significa appunto che nel campo VALUES abbiamo specificato più valori di quanto possibile.

Lo stesso errore ci verrebbe restituito se facessimo il caso opposto, ovvero mancasse l'inserimento di qualche campo specificato nella query.

Effettuare inserimenti multipli con MySQL
Quando dobbiamo inserire più righe all'interno di una sola tabella, a rigor di logica dovremmo usare tante istruzioni INSERT INTO quante sono le nuova righe. MySQL invece, consente gli inserimenti multipli con una sola istruzione INSERT INTO, ad esempio:

INSERT INTO clienti(nome,descrizione,citta)
VALUES('WebMilano.NET','WebAgency','Milano'),
VALUES('Latte & tipici','Casearia','Modena'),
VALUES('Auto e occasioni','Concessionaria','Palermo')

Ogni VALUES va separato dall'altro con una virgola ( , )

Nessuno ci vieta comunque di usare più INSERT INTO, ad esempio:

INSERT INTO clienti(nome,descrizione,citta) VALUES('WebMilano.NET','WebAgency','Milano')
INSERT INTO clienti(nome,descrizione,citta) VALUES('Latte & tipici','Casearia','Modena')
INSERT INTO clienti(nome,descrizione,citta) VALUES('Auto e occasioni','Concessionaria','Milano')

Anche se con più INSERT INTO avremo un calo di prestazioni e soprattutto uno spreco di codice inutile.

La modifica delle righe di una tabella MySQL avviene attraverso il termine UPDATE.

Modificare una sola riga
Il codice è:

UPDATE nomeTabella SET nomeCampo = 'nuovoValore' WHERE id = 'n';

Dove "n" identifica il campo id univoco (di tipo INT e AUTO_INCREMENT) da modificare.

Modificare più righe
La modifica di più righe è molto simile alla procedura seguita per la modifica di una sola riga, è sufficiente solo allargare le coincidenze della clausola WHERE. Ad esempio, per modificare tutti i records che hanno il cmapo "id" maggiore a 10, faremo:

UPDATE nomeTabella SET nomeCampo = 'nuovoValore' WHERE id > 10;

Ci sono casi in cui è necessario modificare più righe che hanno "id" non raggruppabili. Sarà necessario specificare una nuova istruzione UPDATE, ad esempio:

UPDATE nomeTabella SET nomeCampo = 'nuovoValore' WHERE id = 5;
UPDATE nomeTabella SET nomeCampo = 'altroValore' WHERE id = 12;
UPDATE nomeTabella SET nomeCampo = 'ennesimoValore' WHERE id = 25;

Modificare tutte le righe
Per modificare tutte le righe di una tabella, si può prendere la query precedente e omettere la clausola WHERE:

UPDATE nomeTabella SET nomeCampo = 'nuovoValore';

Modificare i valori con operatori matematici
Come abbiamo già visto nella puntata dedicata agli operatori matematici, nel caso in cui avessimo un campo numerico, possiamo modificarlo al volo, senza bisogno di estrarlo e operarci poi sopra con un linguaggio server side come PHP.

Ad esempio, per aggiungere una unità a un campo INT, possiamo fare:

UPDATE nomeTabella SET nomeCampo = nomeCampo + 1;

Per raddoppiare il campo, potremmo fare:

UPDATE nomeTabella SET nomeCampo = nomeCampo * 2;

Per eliminare una o più righe da una tabella MySQL, si utilizza il termine DELETE. La struttura, è identica alla proprietà SELECT, già analizzata nella puntata dedicata a SQL di base.

Eliminare una sola riga
La sintassi è:

DELETE nomeCampo FROM nomeTabella WHERE nomeCampo = 'valore' LIMIT 1;

Una buona abitudine da prendere quando si vuole eliminare un determinato numero di righe (anche una sola) è quella di usare la proprietà LIMIT. Per capire perché, vediamo l'esempio successivo:

DELETE nome FROM clienti WHERE nome = 'WebMilano.NET';

In questo caso, ammettiamo di voler eliminare una società dall'elenco dei clienti che si chiami WebMilano.NET. Se però ci fossero più campi "nome" con tale valore, la query precedente li eliminerebbe tutti, con il rischio di ritrovarsi con una tabella inutilizzabile. Per evitare ciò, è sempre bene aggiungere LIMIT 1, così, in caso di poca specificità della query, perderemmo comunque un solo valore:

DELETE nome FROM clienti WHERE nome = 'WebMilano.NET' LIMIT 1;

Se avessimo un campo di riferimento univico, ad esempio un "id" di tipo INT e AUTO_INCREMENT, potremmo fare:

DELETE nome FROM clienti WHERE id = n;

Dove "n" è il numero univoco che identifica la riga da eliminare.

Eliminare più righe
Per eliminare più righe da una tabella MySQL, la sintassi è uguale. Se sapessimo esattamente quante sono le righe da cancellare, potremmo comunque includere LIMIT:

DELETE nome FROM clienti WHERE citta = 'milano' LIMIT 10;

Se invece non sapessimo il numero di righe da eliminare ma volessimo comunque eliminare tutte quelle che rispondono alla clausola WHERE, potremmo fare:

DELETE nome FROM clienti WHERE citta = 'milano';

Eliminare tutti i records di una tabella
Si possono cancellare tutti i records da una singola tabella con una sola linea di codice:

DELETE * FROM clienti;

In questo caso, resta la struttura della tabella ma vengono rimossi i campi. Nel caso in cui avessimo però dei valori auto incrementali (spesso si usano per campi di nome "id" o simili) i nuovi campi terranno conto dei valori auto incrementali che sono stati rimossi.
Ad esempio, se avessimo avuto un campo "id" auto incrementale arrivato fino a 100, con la sintassi DELETE * FROM nomeTabella, i nuovi campi partiranno da 101.

Per evitare ciò, si utilizza il termine TRUNCATE, ad esempio:

TRUNCATE TABLE nomeTabella;

In questo modo, il contenuto della tabella sarà completamente svuotato e i campi auto incrementali ripartiranno da 1.

Come ricavare l'ultimo record

SELECT * FROM NomeTabella ORDER BY id DESC LIMIT 1