(tratto dal sito: http://www.risorse.net/mysql/ - scritto da Livio Giordano)

Installazione di MySQL su Linux

MySQL è distribuito gratuitamente per Linux sul sito http://www.mysql.com. Di solito l'ultima versione stabile viene identificata come "recommended". Vedremo come installare MySQL partendo dai file mysql-xxx.tar.gz (dove xxx sta per il numero della versione), sappiate comunque che esistono anche versioni in RPM o DEB.

Accertatevi di essere entrati nel sistema come root, altrimenti usate il comando su root.
Per cominciare scompattate il file e spostatevi nella directory appena creata.

tar xzf mysql-xxx.tar.gz
cd mysql-xxx

Adesso è il momento di configurare il programma che curerà l'installazione di MySQL nel sistema. Se non conoscete bene le opzioni il consiglio è quello di limitarsi a specificare il percorso di installazione: le altre impostazioni di default vanno bene nella maggioranza dei casi. Solitamente il percorso consigliato è /usr/local/mysql, ma naturalmente potete scegliere il percorso che vi sembra più comodo.

./configure --prefix=/usr/local/mysql

Adesso vedrete molte schermate di configurazione, teneto sott'occhio il README e fate attenzione alle opzioni proposte. Finita questa parte siete pronti per lanciare la compilazione vera e propria:

make

Il programma adesso è installato, ma è necessario compiere ancora qualche operazione prima di essere operativi. La prima è quella di installare i file che permetteranno a MySQL di funzionare. Rimanete nella stessa directory dalla quale avete lanciato gli altri comandi e digitate:

scripts/mysql_install_db

A questo punto potete tranquillamente cancellare la directory nella quale vi trovate. Essa infatti contiene solamente file di installazione e temporanei; nel caso dobbiate reinstallare il programma dovrete solamente scompattare nuovamente mysql-xxx.tar.gz.

Avviamo il server:

mysqld

Per controllare che tutto funzioni correttamente digitate

mysqladmin -u root status

Adesso potete vedere il paragrafo sulla configurazione di MySQL per iniziare a lavorare con il database.

Installazione e configurazione di MySQL su Win32

Prima di tutto andate sul sito http://www.mysql.org (oppure nella lista dei mirror http://www.mysql.com/downloads/mirrors.html) e scaricate l'ultima versione disponibile.
Una volta installato MySQL (eseguendo setup.exe) il server è quasi pronto a funzionare, è sufficiente seguire alcune semplici istruzioni per metterlo a punto per poter iniziare a lavorare. La directory di default è C:\mysql, naturalmente potete installarlo in qualsiasi directory vogliate.
Il programma del server MySQL si trova nella sottocartella "bin" all'interno della directory nella quale avete installato il programma, il nome dell'eseguibile è mysqld.exe.

Dal prompt MS-DOS, fate partire il server:

C:\mysql\bin> mysqld

oppure su NT/2000, dopo averlo installato come servizio

C:\mysql\bin> mysqld-nt --install

potete usare il comando

NET START mysql

Per i sistemi Windows 95/98 che utilizzano processori Intel Pentium^® è disponibile anche l'eseguibile mysqld-opt, ottimizzato per questa famiglia di processori.

In Windows 95/98 se volete far partire automaticamente il server ogni volta che riavviate Windows, potete creare un collegamento al programma nella cartella C:\Windows\Menu Avvio\Esecuzione automatica.

Per fermare il server su Windows 95/98 dovete eseguire il comando

C:\mysql\bin> mysqladmin -u root shutdown

oppure su Windows NT/2000 usate i comandi

NET STOP mysql

Adesso potete vedere il paragrafo sulla configurazione di MySQL per iniziare a lavorare con il database.

Configurazione di MySQL su Windows e Linux

Adesso che il server MySQL è installato e attivo, la prima cosa da fare è quella di impostare una password per l'utente root, ovvero l'utente che è in grado di compiere tutte le operazioni su MySQL, compresa quella di fermare il server. MySQL permette l'accesso ai dati contenuti nei database esclusivamente agl utenti autorizzati, per questo è importante creare da subito degli utenti che abbiano delle possibilità di azione limitate per evitare seri problemi di sicurezza. Appena installato, MySQL ha un utente "root" che può accedere senza inserire alcuna password, in grado di compiere qualsiasi azione sul server. Quindi la prima cosa da fare adesso è quella di assegnare una password a "root" in modo da evitare che chiunque possa entrare e fare danni senza incontrare alcun blocco.
Attenzione perché quello che stiamo per fare è estremamente importante: MySQL è un server e in quanto tale esso permette l'accesso a chiunque si trovi nella stessa rete. Questo significa che se il server è collegato a Internet, CHIUNQUE potrà accedere al server! Naturalmente la possibilità di effettuare delle operazioni su di esso sarà vincolata al fatto di avere un nome utente e una password di accesso.

Iniziamo assegnando una password all'utente "root". La password va inserita tra virgolette, come nell'esempio.

mysqladmin -u root password "mia_password"

Per assicurarci che MySQL abbia registrato il cambiamento, ricarichiamo le tabelle di accesso:

mysqladmin -u root reload

Se appare un messaggio d'errore, va tutto bene :)) Questo infatti significa che d'ora in poi dovrete inserire la password per operare sul server.

Proviamo ora a vedere se la password viene riconosciuta, ad esempio esaminando i parametri di lavoro del server:

mysqladmin -u root -p status

Appena premuto invio vi verrà richiesta la password. Mentre la scriverete non apparirà nulla; non preoccupatevi, è una misura di sicurezza per evitare che qualche curioso, sbirciando, riesca a vedere quanto è lunga la password.
-u root indica a MySQL che volete accedere con il nome utente "root"; -p indica che vi dovrà essere richiesta la password per eseguire l'operazione. Come abbiamo visto omettendo -p il server si rifiuta di compiere l'operazione.

Adesso dovrete inserire la password anche per fermare il server:

mysqladmin -u root -p shutdown
 

Tipi di campo:

MySQL, come tutti i database, prevede tipi di campi differenti a seconda dei dati che ogni campo dovrà contenere.
 

Tipi numerici:

Quando si va ad assegnare un campo di tipo numerico, è importante sapere quale scegliere, così da non trovarsi con una tabella che va sistemata o ritoccata per contenere i dati.

Ci sono casi in cui, quando si è certi che il database dovrà contenere una piccola quantità di dati, si va erroneamente a identificare comunque un campo di tipo INT, quando magari un TINYINT farebbe comunque lo stesso lavoro in maniera egregia.

Ma perché scervellarsi per trovare il tipo di dato numerico adatto? Per risparmiare spazio e migliorare le prestazioni del database. Se avessimo una tabella di nome "admin" con username e password dell'amministratore unico del sito, è bene evitare di scegliere un campo eccessivamente grande (come può essere BIGINT) perché occuperemo spazio inutile e peggioreremo le prestazioni di MySQL.

 

Tipi testuali:

Un tipo di dato stringa è un campo che può contenere qualunque tipo di carattere: numerico, alfanumerico, simboli ecc.
Come per i campi numerici ce ne sono di vari tipi, a seconda della quantità di dati che si vuole salvare.

Nella tabella seguente, maggiori informazioni:

 

• 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.

Tipi data:

I tipi di campi data sono tutti quei campi che contengono come valore la data e/o l'ora.
Nella tabella che segue, li mostriamo uno ad uno:

 

Altri tipi di campo:

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.

MySQL: le basi

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;

L'asterisco ( * ) ci consente di richiamare i campi senza specificare tutti i loro nomi. Il termine FROM permette di identificare il nome della tabella dalla quale estrapolare i dati. Se volessimo invece estrarre un solo campo, è inutile estrarli tutti e potremmo quindi usare il nome specifico, ad esempio:

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.

ASC e DESC funzionano anche con campi diversi dai campi di tipo INT o simili. In caso di campi di stringhe (TEXT, VARCHAR ecc.) seguiranno l'ordine alfabetico:
ASC dalla A alla Z.
DESC il contrario, dalla Z alla A.

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 di confronto

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:

<
>
<=
>=
=
!=
LIKE

I primi cinque operatori, sono uguali a tutti gli altri linguaggi di scripting o di programmazione e identificano:

Minore
Maggiore
Minore o uguale
Maggiore o uguale
Uguale
Diverso

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.

Operatori matematici

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

Funzioni Stringa

ASCII(S)

Returns the ASCII code value of the leftmost character of S. If S is NULL return NULL.

mysql> SELECT ascii(2);                         -> 50
mysql> SELECT ascii('dx');                      -> 100

CHAR(X,...)

Returns a string that consists of the characters given by the ASCII code values of the arguments. NULLs are skipped.

mysql> SELECT char(77,121,83,81,'76');          -> 'MySQL'

CONCAT(X,Y...)

Concatenates strings. May have more than 2 arguments.

mysql> SELECT CONCAT('My', 'S', 'QL');          -> 'MySQL'

LENGTH(S)

OCTET_LENGTH(S)

CHAR_LENGTH(S)

CHARACTER_LENGTH(S)

Length of string.

mysql> SELECT length('text');                   -> 4
mysql> SELECT octet_length('text');             -> 4

LOCATE(A,B)

POSITION(B IN A)

Returns position of A substring in B. The first position is 1. Returns 0 if A is not in B.

mysql> select locate('bar', 'foobarbar');         -> 4
mysql> select locate('xbar', 'foobar');           -> 0

INSTR(A,B)

Returns position of first substring B in string A. This is the same as LOCATE with swapped parameters.

mysql> select instr('foobarbar', 'bar');         -> 4
mysql> select instr('xbar', 'foobar');           -> 0

LOCATE(A,B,C)

Returns position of first substring A in string B starting at C.

mysql> select locate('bar', 'foobarbar',5);        -> 7

LEFT(str,length)

Gets length in characters from beginning of string.

mysql> select left('foobarbar', 5);             -> 'fooba'

RIGHT(A,B)

SUBSTRING(A FROM B)

Gets B characters from end of string A.

mysql> select right('foobarbar', 5);            -> 'arbar'
mysql> select substring('foobarbar' from 5);    -> 'arbar'

LTRIM(str)

Removes space characters from the beginning of string.

mysql> select ltrim('  barbar');                -> 'barbar'

RTRIM(str)

Removes space characters from the end of string. mysql> select rtrim('barbar '); -> 'barbar'

TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)

Returns a character string with all A prefixes and/or suffixes removed from B. If BOTH, LEADING and TRAILING isn't used BOTH are assumed. If A is not given, then spaces are removed.

mysql> select trim('  bar   ');                      -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx');    -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx');       -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz');   -> 'barx'

SOUNDEX(S)

Gets a soundex string from S. Two strings that sound 'about the same' should have identical soundex strings. A 'standard' soundex string is 4 characters long, but this function returns an arbitrary long string. One can use SUBSTRING on the result to get a 'standard' soundex string. All non alpha characters are ignored in the given string. All characters outside the A-Z range are treated as vocals.

mysql> select soundex('Hello');                 -> 'H400'
mysql> select soundex('Bättre');                -> 'B360'
mysql> select soundex('Quadratically');         -> 'Q36324'

SUBSTRING(A, B, C)

SUBSTRING(A FROM B FOR C)

MID(A, B, C)

Returns substring from A starting at B with C chars. The variant with FROM is ANSI SQL 92 syntax.

mysql> select substring('Quadratically',5,6);          -> ratica

SUBSTRING_INDEX(String, Delimiter, Count)

Returns the substring from String after Count Delimiters. If Count is positive the strings are searched from left else if count is negative the substrings are searched and returned from right.

mysql> select substring_index('www.tcx.se', '.', 2);   -> 'www.tcx'
mysql> select substring_index('www.tcx.se', '.', -2);  -> 'tcx.se'

SPACE(N)

Return a string of N spaces.

mysql> select SPACE(6);         -> '      '

REPLACE(A, B, C)

Replaces all occurrences of string B in string A with string C.

mysql> select replace('www.tcx.se', 'w', 'Ww');  -> 'WwWwWw.tcx.se'

REPEAT(String, Count)

Repeats String Count times. If Count <= 0 returns a empty string. If String or Count is NULL or LENGTH(string)*count > max_allowed_size returns NULL.

mysql> select repeat('MySQL', 3);                -> 'MySQLMySQLMySQL'

REVERSE(String)

Reverses all characters in string.

mysql> select reverse('abc');  -> 'cba'

INSERT(Org, Start, Length, New)

Replaces substring in Org starging at Start and Length long with New. First position in Org is numbered 1.

mysql> select insert('Quadratic', 3, 4, 'What');   -> 'QuWhattic'

INTERVAL(N, N1, N2, N3...)

It is required that Nn > N3 > N2 > N1 is this function shall work. This is because a binary search is used (Very fast). Returns 0 if N < N1, 1 if N < N2 and so on. All arguments are treated as numbers.

mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);     -> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);           -> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);            -> 0

ELT(N, A1, A2, A3...)

Returns A1 if N = 1, A2 if N = 2 and so on. If N is less than 1 or bigger than the number of arguments NULL is returned.

mysql> select elt(1, 'ej', 'Heja', 'hej', 'foo');  -> 'ej'
mysql> select elt(4, 'ej', 'Heja', 'hej', 'foo');  -> 'foo'

FIELD(S, S1, S2, S3...)

Returns index of S in S1, S2, S3... list. The complement of ELT(). Return 0 when S is not found.

mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');       -> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');       -> 0

FIND_IN_SET(string,string of strings)

Returns a value 1 to N if the 'string' is in 'string of strings'. A 'string of strings' is a string where each different value is separated with a ','. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET is optimized to use bit arithmetic!

mysql> SELECT FIND_IN_SET('b','a,b,c,d')		-> 2

This function will not work properly if the first argument contains a ','.

LCASE(A)

LOWER(A)

Changes A to lower case according to current character set ,dmappings (Default Latin1).

mysql> select lcase('QUADRATICALLY');           -> 'quadratically'

UCASE(A)

UPPER(A)

Changes A to upper case.

mysql> select ucase('Hej');             -> 'HEJ'

Funzioni aggregate

Le regole dei nomi (tabelle e campi)

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:

Inserire un nuovo record

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.

Modificare un record

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 un record

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.

Quando si estrapolano informazioni da un database, si ha spesso la necessità di paginarli, ovvero estrarne un po' alla volta e permetterne poi la navigazione degli altri attraverso l'utilizzo delle pagine seguenti e poi precedenti.
E' una pratica diffusissima nel Web, ma anche quando si lavora con grandi quantità di dati al di fuori dal WWW. Pe revitare tempi di attesa troppo lunghi, si può infatti eseguire la cosidetta paginazione.

Paginazione in MySQL

MySQL supporta la paginazione dei dati attraverso l'istruzione LIMIT, che limita appunto l'estrapolazione di dati. Una query simile:

SELECT * FROM dati LIMIT 1,5

Specifica a MySQL di prendere i records dal primo al quinto all'interno della tabella dati. I due valori che seguono l'istruzione LIMIT, indicano da quale record iniziare a prendere i dati e quanti prenderne (1,5: per l'appunto dal primo record, prendine cinque).

Se avessimo voluto prenderli dal ventesimo al trentesimo, avremmo scritto:

SELECT * FROM dati LIMIT 20,10

Ovvero, partendo dal ventesimo record, prendine 10

Abbiamo visto che l'istruzione LIMIT circoscrive la quantità di informazioni da ricavare dal database. Quando però si ha la necessità di paginare i dati, è importante che gli estremi della proprietà LIMIT vengano calcolati dinamicamente.
Questo perché è necessario indicare ogni volta da che record partire l'estrazione e a quale fermarsi.

Ammettiamo di voler estrarre 5 records per pagina, la query dovrebbe essere:

SELECT * FROM dati LIMIT 1,5

Per la prima pagina, ma poi per la seconda sarà:

SELECT * FROM dati LIMIT 6,5

E per la terza:

SELECT * FROM dati LIMIT 11,5

E così via... Questi due valori da indicare a LIMIT vanno ricavati dinamicamente, ovvero ad ogni pagina deve corrispondere il record iniziale e finale dell'estrazione, fermandosi quando i records saranno terminati.

Otteniamo il totale di records
Prima di iniziare la paginazione vera e propria, è necessario estrapolare il dato totale di records, così da evitare di paginare dati quando i records saranno finiti. La query è:

SELECT COUNT(*) AS totale FROM NomeTabella

In questo modo con la voce totale avremo il numero di records estratti dalla query.

Paginazione in ASP e MySQL

Da questo punto in poi andremo avanti con le ASP, ma tradurre il codice in PHP (o altri linguaggi) è davvero molto semplice. Si tratta solo di costrutti if e calcoli matematici.

Prepariamo la paginazione
Con la query di prima, avevamo ricavato il totale di record estratti. Ora possiamo calcolarci dinamicamente i vari valori di LIMIT. Attraverso queste semplici istruzioni:

dim pagina
pagina = Cint(request.querystring("pag"))
if pagina<=0 then pagina=1

SQLcount = "SELECT COUNT(NomeTabella.NomeCampo) AS totale FROM NomeTabella GROUP BY NomeTabella.id"

Set adoRstCount=adoCon.Execute(SQLcount)

        PageSize = 5
        ' Quanti records per pagina

        inizio = (pagina-1)*PageSize
        ' il primo record di ogni pagina

        totali = Cint(adoRstCount("totale"))
        ' Quanti records abbiamo estratto dalla query

        NumPagine = totali/PageSize
        ' Quante pagine abbiamo ottenuto

        ' L'if successivo serve ad arrotondare per eccesso il numero di pagine
        ' nel caso in cui ottenessimo un numero decimale
        ' ad esempio 20 records da distribuire in 6 pagine

        If NumPagine-Cint(NumPagine) > 0 Then
                NumPagine = Cint(NumPagine+1)
        Else
                NumPagine = Cint(NumPagine)
        End If
        

adoRstCount.Close
Set adoRstCount=Nothing

In questo modo, all'interno delle variabili:

PageSize: abbiamo i records da distribuire in ogni pagina.
inizio: Il primo valore da assegnare a LIMIT.
NumPagine: Il numero totale di pagine.

MySQL e le Regular Expression

Query FULL TEXT

Le query Full Text consentono di eseguire ricerche ad alta precisione all'interno di un database. E' possibile sfruttarle solo dalla versione 3.23.23 di MySQL e solo con tabelle di tipo MyISAM e campi CHAR, VARCHAR, o TEXT.
Per eseguire una query Full text su MySQL, è necessario creare un indice che riunisca i vari campi che vanno sfruttati nella query.
Per fare ciò, è sufficiente aggiungere un INDEX di tipo FULLTEXT a tali campi. Su una tabella già esistente, possiamo fare:

ALTER TABLE nomeTabella ADD FULLTEXT (primoCampo,secondoCampo,terzoCampo,...);

Se invece dovessimo creare una nuova tabella, allora nel momento in cui andiamo a specificare i vari campi, possiamo aggiungere il FULLTEXT, ad esempio:

CREATE TABLE nomeTabella (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      titolo VARCHAR(200),
      testo TEXT,
      FULLTEXT (titolo,testo)
);

Una volta aggiunto l'indice FULLTEXT, possiamo iniziare a eseguire le nostre query. Ammettiamo di voler cercare la parola MySQL all'interno dei campi "titolo" e "testo" (della tabella "notizie") e ordinare i risultati secondo l'attinenza della parola ricercata nei due campi. Ecco come fare:

SELECT titolo,descrizione, MATCH(titolo,testo) AGAINST('mysql') AS attinenza FROM notizie WHERE MATCH(titolo,testo) AGAINST('mysql') ORDER BY attinenza DESC

Così otterremo i due campi titolo e descrizione che all'interno di titolo e testo avranno la parola mysql, ordinati secondo l'attinenza data da MySQL. Ecco come potrebbe essere il risultato della query precedente: