Recent Changes - Search:
Linuxpedia /

My SQL

Che cos'e' MySQL?

Il database MySQL e' il database relazionale piu' veloce al mondo, e' in grado di gestire milioni di utenti contemporaneamente, e contenere tranquillamente Terabyte di dati (1 Terabyte e' una quantita' spaventosa di dati...). Funziona su un'architettura client/server, cioe' ogni utente e' un client e invia le richieste ad un computer che e' il server (questo computer puo' essere benissimo lo stesso computer da cui vengono mandate le richieste). Non richiede una potenza di calcolo eccessiva (dal 386 in poi va tutto bene) ed e' Open Source, cioe' i codici del programma sono liberamente guardabili, copiabili, modificabili, ridistribuibili, ma cosa ancora piu' stupefacente e' gratis, perche' sviluppato dalla quella particolare comunita' libera di cui anche tu che stai leggendo queste righe fai parte. (Se ancora non l'hai capito tutti possono aiutare a migliorare MySQL, ed e' per questo che e' uno dei migliori database esistenti al mondo, come la maggior parte dei software GNU)

Novita' MariaDB

MariaDB e' la copia di MySQL, nasce dallo stesso codice ed e' portata avanti dagli sviluppatori ca cui non e' piaciuta l'acquisizione di MySQL da parte di Oracle. Sono identici: https://mariadb.com

Tutti i comandi che vedete qui funzionano in maniera identica su MySQL e su MariaDB.

Convenzioni tipografiche

Nella presente guida i comandi digitati da tastiera, quando saranno scritti all'interno di MySQL, appariranno cosi': 

 prova 1 2 3
 prova...

Ogni volta che si va a capo si sottintende di aver premuto il tasto [INVIO].

Se un comando puo' essere lanciato solo da terminale verra' posto il simbolo del dollaro davanti al comando: 

 $ echo ciao
 ciao

se deve essere eseguito da root ci sara' un cancelletto davanti al comando: 

 # prova 1 2 3
 # prova...

L'utente (o utonto) comune si chiamera' max.

Installazione

Si puo' installare sia da RPM, DEB, o dai sorgenti. Una volta installato bisogna fare in modo che si avvii automaticamente, altrimenti nessun utente si potra' collegare al proprio database. Per far cio' bisogna modificare inittab in modo appropriato, leggere cio' che esce dal comando: 

 $ info inittab

guardate se la vostra distribuzione rispecchia cio' che c'e' scritto e agite di conseguenza.

Finire l'installazione

Dopo aver installato MySQL, bisogna creare l'utente mysql appartenente al gruppo mysql: 

 # groupadd mysql
 # useradd -g mysql  mysql

a questo punto va creato il database di partenza con il comando: 

 # mysql_install_db

andate dove e' presente il database installato (di solito in /usr/local/mysql/data) e cambiate i permessi in modo che vi possa accedere solo l'utente mysql: 

 # cd /usr/local/mysql/data
 # chmod -R a-rwx *
 # chown -R mysql:mysql *
 # chmod -R u+rwx *

A questo punto bisogna creare un script di avvio (se gia' non ce n'e' uno presente nella nostra distribuzione), ecco un tipico script di init?: 

 # Start mysqld:
 mysqld_start() {
  if [ -x /usr/bin/mysqld_safe ]; then
    # If there is an old PID file (no mysqld running), clean it up:
    if [ -r /var/run/mysql/mysql.pid ]; then
      if ! ps axc | grep mysqld 1> /dev/null 2> /dev/null ; then
        echo "Cleaning up old /var/run/mysql/mysql.pid."
        rm -f /var/run/mysql/mysql.pid
      fi
    fi
    /usr/bin/mysqld_safe  --pid-file=/var/run/mysql/mysql.pid  &
  fi
 }
 # Stop mysqld:
 mysqld_stop() {
  # If there is no PID file, ignore this request...
  if [ -r /var/run/mysql/mysql.pid ]; then
    killall mysqld
    # Wait at least one minute for it to exit, as we don't know how big the DB is...
    for second in 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 
      0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 60 ; do
      if [ ! -r /var/run/mysql/mysql.pid ]; then
        break;
      fi
      sleep 1
    done
    if [ "$second" = "60" ]; then
      echo "WARNING:  Gave up waiting for mysqld to exit!"
      sleep 15
    fi
  fi
 }
 # Restart mysqld:
 mysqld_restart() {
  mysqld_stop
  mysqld_start
 }
 case "$1" in
 'start')
   mysqld_start
   ;;
 'stop')
   mysqld_stop
   ;;
 'restart')
   mysqld_restart
   ;;
 *)
  echo "usage $0 start|stop|restart"
 esac

A questo punto basta lanciare lo script cosi' (se avete chiamato lo script rc.mysql): 

 # rc.mysql start

Se volete vedere se e' tutto a posto come se riavviaste il pc (per vedere eventuali conflitti) ricordo che, dopo aver modificato inittab, in linux non e' necessario riavviare il pc (rischiando di dimezzargli la vita d'utilizzo) basta digitare da root : 

 # init 3
 # init 5

Ed e' come se aveste riavviato il pc

Politiche di sicurezza

Di default MySQL non chiede la password per accedere ad un database, pero' cosi' la prima persona che si collega come root ha accesso a tutti i dati del database (e settare i permessi diventa inutile). Bisogna impostare la password dell'utente root immediatamente! Per farlo digitate: 

 # mysqladmin -u  root  password  fragole

in questo caso ho usato fragole come password ma sono ammesse lettere e numeri.

Pero' ancora esiste solo root come utente e ogni altro utente che si connette a mysql verra' considerato come anonimo e quindi (se non vengono modificati i permessi di anonimo) non potra' fare nulla. Chiaramente si devono aggiungere gli utenti a mano (modificare i permessi di anonimo e' molto pericoloso, perche' chiunque, anche gente malintenzionata da altri computer, potrebbe fare disastri). Per aggiungere un utente, l'utente root dovrebbe usare il comando "GRANT". 

 # mysql -u root -p
 Enter password:*****
 mysql> grant select,insert,update,delete on *.* to max@localhost identified by 'melanzane' ;
 Query OK, 0 rows affected (0.00 sec)

In questa maniera abbiamo dato all'utente max la possibilita' di consultare qualsiasi database e tabella e modificarne il contenuto, e gli abbiamo assegnato la password melanzane; e' possibile non assegnare password ad un utente omettendo la parte finale con 'indentified by in modo che non serva digitare la password per essere quell'utente.

Pero' cosi' non ha la possibilita' di creare dei database; se volete concedere questa possibilita' ad un utente particolare, per esempio l'utente che usate di solito quando usate linux, da utente root: 

 mysql> grant create on *.* to max@localhost;
 Query OK, 0 rows affected (0.00 sec)

Se si esagera nel concedere i permessi, si puo' sempre tornare indietro utilizzando il comando REVOKE alla stessa identica maniera di GRANT.

Un modo molto veloce per dare tutti i permessi su un database e' il seguente: 

 mysql> grant all on database_ciccio.* to max@localhost identified by 'melanzane' ;
 Query OK, 0 rows affected (0.00 sec)

Connettersi e disconnettersi al server

Se il server risiede sul nostro pc, allora basta scrivere: 

 mysql

a questo punto il terminale dovrebbe apparire cosi': 

 mysql>

Il che vuol dire che possiamo lanciare tutti i comandi direttamente a mysql.

Se ci apparisse un bel ACCESS DENIED allora vuol dire che bisogna sapere la password per collegarsi, quindi basta digitare 

 mysql -p
 Enter password: ******
 mysql>

Nel caso volessimo collegarci al database come se fossimo un altro utente basta digitare: 

 mysql -u utente -p

in questo modo siamo pronti anche a digitare la password, e se non c'e' basta non digitare nulla quando ci viene richiesta, ma solo limitarci a battere [INVIO].

Per uscire basta scrivere "exit": 

 mysql> exit
 Bye

Nel caso volessimo collegarci al server mysql presente su un altro pc allora bisogna specificare l'indirizzo della macchina a cui vogliamo collegarci: 

 mysql -h host -u utente -p

Introduzione ai comandi

Vediamo ora alcune nozioni introduttive su come si scrivono i comandi per MySQL, innanzitutto i comandi di mysql non sono "case sensitive", cioe' e' la stessa identica cosa scrivere "SELECT" o "select" o "SeLecT".

Inoltre, finche' non si scrive il punto e virgola ";", il comando non e' concluso e mysql aspetta. In questo modo e' possibili sia lanciare piu' comandi da una riga sia scrivere tante righe per un comando solo, andando a capo quante volte si vuole. Esempi: 

 mysql> SELECT VERSION(); SELECT NOW();
 +------------+
 |version()   |
 +------------+
 |3.23.52-log |
 +------------+
 +-------------------+
 |now()              |
 +-------------------+
 |2004-2-22 09:54:58 |
 +-------------------+

oppure 

 mysql> SELECT  
     -> USER()
     -> ,
     -> CURRENT_DATE;
 +--------------------+--------------+
 | USER()             | CURRENT_DATE | 
 +--------------------+--------------+      
 | max@localhost      | 2004-02-22   |   
 +--------------------+--------------+

Se mentre state digitando una serie di comandi ci ripensate e volete annullare tutto, basta digitare \c, esempio: 

 mysql> select
     ->user()
     ->\c

Nel caso stiate digitando una stringa contenente degli apici o dei doppi apici, dovete prima chiuderli, per utilizzare \c altrimenti \c verra' interpretato come una serie di caratteri appartenenti alla stringa, esempio (notate come anche il punto e virgola sia ignorato perche' e' all'interno di una stringa, e di come il cursore faccia capire che si e' all'interno della stringa perche' non e' piu' -> ma diventa ">: 

 mysql> SELECT * FROM my_table WHERE name = "Smith" AND age < 30;          
     "> "\c
 mysql>

Come avete visto \c non necessita del punto e virgola per far capire che il comando e' concluso, oltre a \c tutti i comandi che cominciano con "\" (chiamata anche backslash) non necessitano del punto e virgola per essere chiusi. Anche "exit" e altri che incontreremo lungo la guida, per questi comandi mettere o non mettere il punto e virgola e' indifferente.

Un comando molto utile al posto del punto e virgola e' \G, questo comando permette di visualizzare tabelle molto grandi, dividendole verticalmente in maniera tale da poterle vedere molto bene e ordinate.

Lavorare con i database

I database sono degli aggregati di dati, ordinati e collegati tra loro da delle relazioni. Se state leggendo questa guida gia' dovreste sapere a grandi linee cosa e' un database, mysql e' un server di database, cioe' all'interno esistono tanti database e sono tutti consultabili a seconda dei permessi. Per vedere che database ci sono basta scrivere "SHOW DATABASES": 

 mysql> show databases; 
 +----------+ 
 | Database | 
 +----------+ 
 | mysql    | 
 | test     | 
 +----------+ 
 2 rows in set (0.00 sec)

Il database "mysql" e' sempre presente perche' e' quello che contiene tutti i permessi, mentre test di solito c'e' e serve per provare senza far danni. Al database test, di solito, hanno accesso tutti gli utenti, ma proprio tutti!

E' importante sapere che mysql mantiene tutti i dati su tutto all'interno della cartella /usr/local/mysql/data/ (certe distribuzioni la spostano in /var/lib/mysql/), e chiaramente e' accessibile solo a root e all'utente mysql. All'interno della cartella, per ogni tabella esistente vengono creati tre file, si chiamano come la tabella e hanno estensione:

  • .fmr : file che contiene delle statistiche sulla tabella
  • .myd : che contiene i dati
  • .myi : che contiene gli indici

Per sapere che database si sta usando in questo momento basta usare "SELECT DATABASE()": 

 mysql> select database(); 
 +------------+ 
 | database() | 
 +------------+ 
 | prova      | 
 +------------+ 
 1 row in set (0.00 sec)

Creare un database

Se vogliamo creare un nostro database basta usare "CREATE DATABASE", chiaramente dobbiamo avere il permesso di creare database, altrimenti lo deve fare l'utente root: 

 mysql> create database prova;

Attenzione solo i comandi non sono "case sensitive", i nomi lo sono, percio' "prova" e' diverso da "Prova" che e' diverso da "prOva", questa particolarita' dipende dal filesystem su cui e' installato il server mysql, e percio' su linux funziona cosi', mentre su Windows (ma chi sarebbe cosi' folle da usare windows...) non ci sarebbe alcuna differenza.

Per utilizzare il database c'e' il comando "USE" che non necessita di punto e virgola: 

 mysql> use prova
 Database changed

Se si vuole usare un database fin dall'inizio senza doverlo digitare dopo essere entrati un mysql, basta scrivere: 

 mysql -p prova

oppure se non c'e' bisogno di password: 

 mysql prova

Cancellare un database

Per cancellare un database e' necessario utilizzare il comando "DROP DATABASE", ma il privilegio di utilizzare "drop" e' riservato solo a root di default. Personalmente penso che sia meglio non garantire ad altri tale privilegio, perche' si rischia di cancellare inavvertitamente dei dati vitali. 

 mysql -u root -p
 Enter password:*****
 mysql> drop database prova
 Query OK, 0 rows affected (0.00 sec)

Tabelle

I dati all'interno di un database sono generalmente organizzati in tabelle; per vedere le tabelle all'interno del database corrente basta usare ancora una volta il comando "show": 

 mysql> show tables;
 Empty set (0.00 sec)

in questo caso il computer ha risposto che non ci sono tabelle nel database corrente.

Per creare una tabella conviene fare un esempio pratico: per esempio io ho dei canarini a casa e voglio catalogarli, descrivendo il loro nome, l'eta' e se siano maschio o femmina (per sapere se un canarino e' maschio o femmina bisogna aspettare la stagione degli amori, se cantano sono maschi, se cinguettano e basta sono femmine; qualunque altro metodo vi abbiano raccontato non funziona), creero' la seguente tabella nel database corrente usando il comando "CREATE": 

 mysql> create table canarini (nome varchar(20), razza varchar(20),
     -> sesso char(1), nascita date, morte date);
 Quey OK, 0 rows affected (0.01 sec)

come vedete dopo aver specificato il nome bisogna scrivere il titolo di ogni colonna della tabella e che tipo di dato sara' inserito. Per il nome e la razza ho scelto il tipo "varchar" che significa che conterra' una stringa di caratteri di lunghezza variabile e mette a disposizione inizialmente uno spazio di 20 caratteri (pero' si puo' scegliere lo spazio iniziale tra 1 e 255), per il sesso ho scelto "char" con una lunghezza fissa della stringa, nel caso specifico un solo carattere ne' piu' ne' meno; per la data di nascita e di morte c'e' il tipo "date" che serve proprio per le date.

Per sapere come e' fatta una tabella basta usare il comando "DESCRIBE": 

 mysql> describe canarini;
 +---------+-------------+------+-----+---------+-------+ 
 | Field   | Type        | Null | Key | Default | Extra | 
 +---------+-------------+------+-----+---------+-------+
 | nome    | varchar(20) | YES  |     | NULL    |       | 
 | razza   | varchar(20) | YES  |     | NULL    |       | 
 | sesso   | char(1)     | YES  |     | NULL    |       |
 | nascita | date        | YES  |     | NULL    |       |
 | morte   | date        | YES  |     | NULL    |       |
 +---------+-------------+------+-----+---------+-------+ 
 5 rows in set (0.00 sec)

Per cancellare una tabella bisogna usare il comando "DROP", piu' esattamente "DROP TABLE", anche in questo caso bisogna vere il permesso di "drop" per poter cancellare: 

 mysql -u root -p prova
 Enter password:*****
 mysql> drop table canarini
 Query OK, 0 rows affected (0.00 sec)

Inserire i dati all'interno delle tabelle

Per inserire i dati all'interno delle tabelle esistono due vie, quella lunga inserendo dato per dato da linea di comando, e quella breve dicendo a mysql di prendere tutti i dati da un file.

Quella lunga adopera la seguente sintassi, ed il comando "INSERT": 

 mysql> insert into canarini values ('Primo', 'marrone europeo', 'm', 
     ->'1994-5-10', '1995-2-12');

se dei campi sono vuoti si scrive null senza apici.

Se invece abbiamo tanti dati possiamo usare un file e metterli tutti in un colpo solo, poniamo di avere il file "dati.txt" cosi' fatto: 

 Primo        marrone       m            1994-05-10   1995-2-12
 Colombella   grigio        f            1994-08-17   1995-3-15
 Pinocchio    grigio        m            1995-02-13   1995-7-20
 Fang         giallo        m            1995-06-27   2002-3-25
 Bad          giallo        m            1998-04-31   \N
 Mylady       giallo        f            1998-03-11   \N
 Carla        giallo        f            1999-14-09   \N
 SlimShady    giallo        m            1999-04-29   \N

Come vedete al posto di un campo vuoto c'e' \N (non si puo' scrivere minuscolo), questa sequenza di caratteri rappresenta il valore NULL; comunque potete anche evitare di scriverlo, \N o nulla e' quasi la stessa cosa (se dovete compiere delle operazioni sui dati che ne valutano o modificano il contenuto e' meglio mettere \N). Di default il separatore dei campi e' [TAB], quindi se provate a scrivere il file con vim (non usate emacs, perche' col tab fa' dei casini) lo schema non sara' assolutamente ordinato come appare scritto da me, ad ogni riga bisogna andare a capo. Se il [TAB] da' problemi, inventatevi un carattere come separatore, (ad esempio la virgola ",") e poi specificatelo nel comano (se usate la virgola, scrivete tutto attaccato).

A questo punto, per caricare i dati in tabella, basta immettere il comando LOAD DATA, e specificare il file: 

 mysql> load data infile "/home/max/dati.txt" into table canarini;

Attenzione il file da caricare nel database o si trova nella directory del database dovete avere il privilegio "FILE" (usate "grant file..." da root), altrimenti vi verra' negato. Una volta caricati i dati potete comunque cancellare il file.

ATTENZIONE: se il file si trova sul vostro pc, dovete aggiungere la parola LOCAL, altrimenti non vrete i premessi per accedere al file: 

 LOAD DATA LOCAL INFILE "/home/max/dati.txt" INTO TABLE  canarini;

Se avete usato la virgola come separatore, il comando da dare e' il seguente: 

 mysql> load data infile "/home/max/dati.txt" 
     ->into table canarini fields terminated by ',' ;

Se poi volete scegliere anche il carattere che separa le righe, basta usare ... lines terminated by... .

Per cancellare una o piu' righe di una tabella basta usare il comando "DELETE" cosi': 

 mysql> delete from tabella_con_errore where condizione_che_individua_le_righe_sbagliate;

Eliminare i dati da una tabella

Basta usare DELETE cosi': 

 mysql> DELETE FROM canarini where nome = "Carla";
 Query OK, 1 row affected (0.01 sec)

Ricercare i dati nelle tabelle

Per vedere tutto il contenuto di una tabella basta usare il comando "SELECT" insieme al carattere jolly "*", che significa tutti: 

 mysql> select * from canarini;
 +------------+---------+-------+------------+------------+ 
 | nome       | razza   | sesso | nascita    | morte      | 
 +------------+---------+-------+------------+------------+
 | Primo      | marrone | m     | 1994-05-10 | 1995-02-12 | 
 | Colombella | grigio  | f     | 1994-08-17 | 1995-03-15 | 
 | Pinochio   | grigio  | m     | 1995-02-12 | 1995-07-20 | 
 | Fang       | giallo  | m     | 1995-06-27 | 2002-03-25 | 
 | Bad        | giallo  | m     | 1998-04-31 | NULL       | 
 | Mylady     | giallo  | f     | 1998-03-11 | NULL       | 
 | Slimshady  | giallo  | m     | 1999-04-26 | NULL       | 
 +------------+---------+-------+------------+------------+ 
 7 rows in set (0.03 sec)

Ordinare i dati

Per ordinare i dati, basta usare il comando ORDER BY. Per esempio: 

 mysql> select * from canarini order by nome;
 +------------+---------+-------+------------+------------+ 
 | nome       | razza   | sesso | nascita    | morte      | 
 +------------+---------+-------+------------+------------+
 | Bad        | giallo  | m     | 1998-04-31 | NULL       | 
 | Colombella | grigio  | f     | 1994-08-17 | 1995-03-15 | 
 | Fang       | giallo  | m     | 1995-06-27 | 2002-03-25 | 
 | Mylady     | giallo  | f     | 1998-03-11 | NULL       | 
 | Pinochio   | grigio  | m     | 1995-02-12 | 1995-07-20 | 
 | Primo      | marrone | m     | 1994-05-10 | 1995-02-12 | 
 | Slimshady  | giallo  | m     | 1999-04-26 | NULL       | 
 +------------+---------+-------+------------+------------+ 
 7 rows in set (0.03 sec)

Per compiere una ricerca si puo' specificare un qualunque campo della tabella dopo "where": 

 mysql> select * from canarini where nascita >= " 1995-6-6"; 
 +-----------+--------+-------+------------+------------+ 
 | nome      | razza  | sesso | nascita    | morte      | 
 +-----------+--------+-------+------------+------------+
 | Terry     | giallo | m     | 1995-06-27 | 2002-03-25 | 
 | Bad       | giallo | m     | 1998-04-31 | NULL       | 
 | Mylady    | giallo | f     | 1998-03-11 | NULL       | 
 | Slimshady | giallo | m     | 1999-04-26 | NULL       | 
 +-----------+--------+-------+------------+------------+ 
 4 rows in set (0.00 sec)

Se si vogliono vedere solo alcune colonne di una tabella, basta scriverlo: 

 mysql> select nome, sesso from canarini;
 +------------+-------+ 
 | nome       | sesso |
 +------------+-------+ 
 | Primo      | m     |
 | Colombella | f     |
 | Pinochio   | m     |
 | Terry      | m     |
 | Bad        | m     | 
 | Mylady     | f     | 
 | Slimshady  | m     | 
 +------------+-------+ 
 7 rows in set (0.00 sec)

Se ci sono delle ripetizione nell'output, si possono evitare utilizzando il comando "DISTINCT": 

 mysql> select distinct sesso from canarini; 
 +-------+ 
 | sesso | 
 +-------+ 
 | m     | 
 | f     | 
 +-------+ 
 2 rows in set (0.01 sec)

I risultati di una ricerca possono essere anche ordinati, basta usare il comando "ORDER BY": 

 mysql> select nome, sesso from canarini order by nome;
 +------------+-------+ 
 | nome       | sesso | 
 +------------+-------+
 | Bad        | m     | 
 | Colombella | f     | 
 | Mylady     | f     | 
 | Pinochio   | m     | 
 | Primo      | m     | 
 | Slimshady  | m     | 
 | Terry      | m     | 
 +------------+-------+ 
 7 rows in set (0.01 sec)

Se si vuole invertire l'ordine, basta usare "DESC": 

 mysql> select nome, sesso from canarini order by nome desc; 
 +------------+-------+ 
 | nome       | sesso |
 +------------+-------+ 
 | Terry      | m     | 
 | Slimshady  | m     | 
 | Primo      | m     | 
 | Pinochio   | m     | 
 | Mylady     | f     | 
 | Colombella | f     | 
 | Bad        | m     |
 +------------+-------+ 
 7 rows in set (0.01 sec)

Inoltre si puo' ordinare secondo piu' criteri, per esempio se voglio vedere i nomi in ordine alfabetico, prima i maschi e poi le femmine: 

 mysql> select nome, sesso from canarini order by sesso desc, nome; 
 +------------+-------+ 
 | nome       | sesso | 
 +------------+-------+ 
 | Bad        | m     | 
 | Pinochio   | m     | 
 | Primo      | m     | 
 | Slimshady  | m     | 
 | Terry      | m     | 
 | Colombella | f     | 
 | Mylady     | f     | 
 +------------+-------+ 
 7 rows in set (0.02 sec)

Modificare i dati in una tabella

Se, per esempio, il nome (oppure la data di nascita) e' sbagliata di un canarino, si puo' correggere la tabella utilizzando il comando "UPDATE" che serve ad aggiornare uno o piu' dati della tabella: 

 mysql> update canarini set nome="Terry" where nome="Fang";

Come si vede dalla riga precedente, il comando "WHERE" serve per filtrare le informazioni che cerchiamo dando dei criteri di ricerca. Quando si ricerca attraverso stringhe, generalmente, non c'e' differenza tra maiuscole e minuscole, quindi nell'esempio precedente avremmo cambiato in Terry il nome sia ai canarini di nome "Fang", sia ai canarini "FAng" , "fAng", "faNG".

Il comando UPDATE lavora riga per riga, quindi potete scrivere questo codice per aumentare di uno il valore delle celle di una colonna: 

 mysql> UPDATE canarini SET anni=anni+1 ;

ed e' valido anche questo, che lavora riga per riga: 

 mysql> UPDATE temperature SET media=(temp_min + temp_max)/2 ;

Condizioni di ricerca

Quando si ricerca un argomento i criteri di ricerca per trovare i dati possono essere tanti o complicati da esprimere, per fortuna mysql e' dotato di una vasta scelta di operatori che ci aiutano per ricercare i dati e la possibilita' di utilizzare le parentesi per raggruppare meglio le condizioni:

  • NOT ! : Negazione
  • OR ||; O logico, cioe' va bene sia la condizione prima di "or" sia la condizione dopo "or" sia entrambe
  • AND && : E logico, cioe' debbono verificarsi entrambe le condizioni prima e dopo "and"
  • = : Uguale, vale per tutto tranne per il valore NULL, in cui va usato un altro operatore. Cosi' si evita di confondere 0 con un campo vuoto
  • < : Minore
  • > :Maggiore
  • <=:Minore o uguale
  • >=:Maggiore o uguale
  • <> != : Diverso
  • <=> : Uguaglianza compreso NULL.
  • IS NULL / IS NOT NULL : Confronta un campo per vedere se e' NULL
  • expr BETWEEN min AND max : Vede se un'espressione e' tra il valore minimo e massimo
  • expr IN (valore A, valore B,...) : Controlla se un'espressione ha uno dei valori scritti tra parentesi
  • expr NOT IN (valore A, valore B,...) : Controlla se un'espressione non ha uno dei valori tra parentesi
  • COALESCE(lista) : Cerca il primo valore diverso da NULL nella lista.
  • INTERVAL(n1,n2,n3,...) : Controlla che n1<n2<n3<...

Contare le righe

Se si vuole sapere quante righe hanno un particolare dato, ci viene in aiuto "COUNT()", per esempio vogliamo sapere quante righe ha una tabella basta scrivere: 

 mysql> select count(*) from canarini;
 +----------+ 
 | count(*) | 
 +----------+ 
 |        7 | 
 +----------+ 
 1 row in set (0.00 sec)

Ma "count" non si limita a questo, possiamo anche fare dei gruppi e contarli, ci viene in aiuto il comando "GROUP BY"; immaginiamo di voler sapere quanti canarini per razza ci sono in tabella: 

 mysql> select razza, count(*) from canarini group by razza;
 +---------+----------+ 
 | razza   | count(*) | 
 +---------+----------+ 
 | giallo  |        4 | 
 | grigio  |        2 | 
 | marrone |        1 |
 +---------+----------+ 
 3 rows in set (0.01 sec)

Si puo' anche chiedere di contare piu' gruppi per volta e vedere i vari risultati, se volessimo sapere quanti canarini di una razza sono di un sesso e quanti dell'altro: 

 mysql> select razza, sesso,count(*) from canarini group by razza, sesso; 
 +---------+-------+----------+ 
 | razza   | sesso | count(*) | 
 +---------+-------+----------+ 
 | giallo  | f     |        1 | 
 | giallo  | m     |        3 | 
 | grigio  | f     |        1 | 
 | grigio  | m     |        1 | 
 | marrone | m     |        1 |
 +---------+-------+----------+ 
 5 rows in set (0.01 sec)

Lavorare con piu' tabelle

Immaginiamo di scrivere un'altra tabella che contenga un elenco degli avvenimenti che avvengono ai canarini, scrivo un file eventi.txt cosi': 

 Primo,1994-7-1,veterinario,influenza
 Colombella,1995-1-1,cova,\N 
 Mylady,1999-2-3,cova,3 maschi 1 femmina
 Mylady,1999-3-1,cova,1 maschio 
 Mylady,1999-4-5,cova,2 femmine 
 Bad,2000-3-2,vetrinario,ala rotta

Poi creo la tabella: 

 mysql> create table eventi (nome varchar(20), 
     ->data date, evento varchar(20), effetti varchar(20)); 
 Query OK, 0 rows affected (0.00 sec)

Infine inserisco i dati in tabella: 

 mysql> load data infile "/home/max/eventi.txt" 
     ->into table eventi fields terminated by ',' ; 
 Query OK, 6 rows affected (0.00 sec) 
 Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

Bene, ora che abbiamo 2 tabelle e' possibile vedere come cambiano i nomi dei dati richiesti per ottenere informazioni da piu' tabelle. Innanzitutto immaginiamo di voler sapere l'eta' di una canarina quando ha fatto una covata, per ottenere questa informazione e' necessario sapere la data di nascita della canarina dalla tabella "canarini" e la data della covata dalla seconda tabella ("eventi"): 

 mysql> select canarini.nome,effetti, 
     ->year(data)-year(nascita)+((month(data)-month(nascita))/10) 
     ->as 'età  quel giorno'  
    ->from canarini, eventi where canarini.nome=eventi.nome and evento = "cova";
 +------------+--------------------+-----------------+ 
 | nome       | effetti            | età  quel giorno |
 +------------+--------------------+-----------------+ 
 | Colombella | NULL               |            0.30 | 
 | Mylady     | 3 maschi 1 femmina |            0.90 | 
 | Mylady     | 1 maschio          |            1.00 | 
 | Mylady     | 2 femmine          |            1.10 |
 +------------+--------------------+-----------------+ 
 4 rows in set (0.00 sec)

Come si vede dall'esempio, quando si richiamano dati da piu' tabelle, se il nome della colonna e' lo stesso, bisogna specificare di che tabella si sta parlando (e' comunque una buona abitudine specificarlo sempre, si correggono prima eventuali errori).

Alla stessa maniera e' possibile ottenere una tabella di risultati prendendo piu' volte le informazioni da un'unica tabella come se fossero provenienti da piu' tabelle, mi spiego: cerchiamo di ottenere una tabella che in ogni riga ci mostri una possibile coppia tra i canarini. Possiamo far in modo di considerare due tabelle "canarini" e mostrare le righe che hanno la stessa razza e sesso diverso: 

 mysql> select c1.nome, c1.sesso, c2.nome, c2.sesso, 
     ->c1.razza from canarini as c1, canarini as c2 
     ->where c1.razza=c2.razza and c1.sesso="m" and c2.sesso="f";
 +-----------+-------+------------+-------+--------+ 
 | nome      | sesso | nome       | sesso | razza  |
 +-----------+-------+------------+-------+--------+ 
 | Pinochio  | m     | Colombella | f     | grigio | 
 | Terry     | m     | Mylady     | f     | giallo | 
 | Bad       | m     | Mylady     | f     | giallo | 
 | Slimshady | m     | Mylady     | f     | giallo |
 +-----------+-------+------------+-------+--------+ 
 4 rows in set (0.01 sec)

Modificare le colonne

Se volessimo modificare il nome di una colonna, basta utilizzare il comando "ALTER TABLE", ma bisogna avere il permesso "Alter". Immaginiamo di voler modificare il nome della colonna "nascita" in "data_ di_ nascita": 

 mysql> alter table canarini CHANGE nascita data_di_nascita date;

come vedete e' obbligatorio specificare anche che tipo di dato deve essere contenuto nella colonna, questo perche' e' possibili, sempre scrivendo questo comando in questa maniera, cambiare il tipo di dato della colonna. Per aggiungere una colonna, basta aggiungere "ADD" al comando "alter table, aggiungiamo una colonna chiamandola "in_ piu'": 

 mysql> alter table canarini add in_piu' varchar(30); 
 Query OK, 7 rows affected (0.01 sec) 
 Records: 7  Duplicates: 0  Warnings: 0

Se poi vogliamo cancellarla basta usare "DROP" come "add": 

 mysql> alter table canarini drop in_piu' ;

Tipi di dati

Fino ad ora non ci siamo soffermati su che tipi di dati possiamo inserire in una tabella, quando si crea una tabella e' importane prevedere che dati andranno inseriti, perche' in una colonna per numeri, non saranno accettate le lettere, e cosi' via...

I tipi possono essere raggruppati in 4 grandi categorie:

  • Numerici
  • Caratteri o stringa
  • Date o ore
  • dati in formato binario (immagini, suoni, video, qualsiasi tipo di file): BLOB

Ognuna di queste categorie ha delle sottocategorie, ma in questa guida esploreremo solo le piu' utili all'utente comune.

Per completezza metto qui una lista completa dei dati:

  • Numerici:
    • bit (da 1 a 64)
    • bool (0 o 1)
    • int (intero da 0 a 4294967295, oppure da -2147483648 to 2147483647)
    • bigint (intero da -9223372036854775808 o 9223372036854775807, oppure da 0 a 18446744073709551615) non usatelo, e' meglio usare il double.
    • float (decimale, mySQL decide volta per se usare double o decimal) e' quello piu' sensato con i numeri decimali
  • Date:
    • date (2013-12-31)
    • datetime (2013-12-31 23:59:59)
    • time (13:22:36)
    • year (2013)
  • testo:
    • char (alcuni caratteri)
    • varchar (stringhe)
    • text (testi lunghi fino a 65'000 caratteri)
    • long text (testi piu' lunghi)
    • enum (puo' avere solo un valore fra quelli di un elenco)
    • blob (puo' essere testo o dati)

Dati numerici

I dati numerici si possono suddividere in due grandi categorie: interi e decimali. Gli interi si usano per vaolri che non possono essere decimali, come in un censimento, dove si contano le persone e chiaramente non possono sussistere 20.2 persone. ATTENZIONE: I numeri decimali si indicano con il punto "." (es: 0.32 , 12.3 , 125.43 ), poiche' MySQL e' stato sviluppato nei paesi anglosassoni.

Per gli interi si usa "INT", se si inserisce un numero decimale per errore, viene automaticamente approssimato all'intero piu' vicino (come si fa per tutti i calcoli scientifici).

Per le cifre con il punto si usa "DOUBLE(m,n)" dove m dovrebbe rappresentare il numero di cifre visualizzabili al massimo (se non viene scritto nulla e' 255) (serve in congiunzione con "zerofill", v. piu' avanti), mentre n rappresenta il numero di decimali a cui arrotondare e non deve essere maggiore di "m-2" (comunque il massimo possibile e' 30) (e questo funziona).

Se i numeri debbono essere convertiti in positivi automaticamente, basta far precedere il tipo di numero dalla parola "UNSIGNED".

Per avere un contatore automatico che aggiunge 1 al valore attuale basta mettere davanti "AUTO_ INCREMENT", funziona nella seguente maniera, se nel campo viene inserito 0 o NULL o niente, allora incrementa il valore precedente di 1.

Per avere una serie di zeri davanti al numero (come per i numeri di serie) basta anteporre "ZEROFILL" (es: "00023").

Dati carattere o stringa

Se devono essere inseriti una serie di caratteri la cui lunghezza totale sia fissa si usa "CHAR(n)" che e' piu' veloce, mentre se la lunghezza e' variabile si usa "VARCHAR(n)" che e' piu' lento ma risparmia spazio nel caso si inseriscano stringhe di caratteri piu' piccole; n indica lo spazio massimo consentito.

Nel caso si volesse limitare l'immissione dei dati a solo una serie di possibilita' imposte, ci vengono in aiuto "ENUM" e "SET".

Con "ENUM" e' possibile elencare una serie di possibilita' (al massimo 65535), e a chi immette l'input e' consentito solo uno dei valori possibili, inoltre e' possibile definire un valore di default, esempio: 

 mysql> create table magliette ( 
     ->taglia enum('XS','S','M','L','XL','XXL'), 
     ->colore enum('bianco','rosso','verde') DEFAULT 'bianco');

Con "SET" e' possibile scegliere una qualsiasi combinazione fra quelle possibili (al massimo solo 64): 

 mysql> create table media_preferiti 
     ->( media set('Radio','Televisione','Giornale','Internet');

nella colonna media potranno esserci i seguenti valori: 'Radio, Televisione', 'Radio', 'Giornale, Internet, Televisione', ... . I vari dati vengono sempre orinati in ordine alfabetico ed eliminate le ripetizioni, inoltre ogni valore e' separato dalla virgola (,), quindi non e' possibile usare valori che abbiano al loro interno la virgola!

Date o ore

Per le date esiste il forma "DATE" che abbiamo gia' incontrato, il formato "Date" pone la data secondo secondo anno-mese-giorno (YYYY-MM-DD, cioe' 2004-02-29).

Per le ore esiste il formato "TIME" che pone l'ora secondo ore:minuti:secondi (HH:MM:SS, cioe' 14:40:05).

Se si vuole una data completa dell'ore, esiste il formato "DATETIME" che e' il eguente 'YYYY-MM-DD HH:MM:SS'.

Scrivere script per automatizzare certe operazioni

Se dovete eseguire certe operazioni di continuo, potete scrivere un file con tutti i comandi da dare a mysql, per esempio scriviamo un file chiamandolo maxscript: 

 use prova;
 select * from canarini;

Adesso mandiamolo in esecuzione: 

 mysql < maxscript
 nome    razza   sesso   nascita morte 
 Primo   marrone m       1994-05-10      1995-02-12
 Colombella      grigio  f       1994-08-17      1995-03-15
 Pinochio        grigio  m       1995-02-12      1995-07-20
 Terry   giallo  m       1995-06-27      2002-03-25 
 Bad     giallo  m       1998-04-31      NULL 
 Mylady  giallo  f       1998-03-11      NULL 
 Slimshady       giallo  m       1999-04-26      NULL

Come vedete l'output non e' per nulla bello da vedere ([TAB] per separare le voci, [INVIO] per separare le righe), ma molto pratico per trattarlo con altri programmi.

Se vogliamo l'output carino come quando usiamo mysql im maniera interattiva, bisogna dare l'opzione "-t": 

 mysql -t < scriptmax
 +------------+---------+-------+------------+------------+ 
 | nome       | razza   | sesso | nascita    | morte      |
 +------------+---------+-------+------------+------------+ 
 | Primo      | marrone | m     | 1994-05-10 | 1995-02-12 | 
 | Colombella | grigio  | f     | 1994-08-17 | 1995-03-15 | 
 | Pinochio   | grigio  | m     | 1995-02-12 | 1995-07-20 | 
 | Terry      | giallo  | m     | 1995-06-27 | 2002-03-25 | 
 | Bad        | giallo  | m     | 1998-04-31 | NULL       | 
 | Mylady     | giallo  | f     | 1998-03-11 | NULL       | 
 | Slimshady  | giallo  | m     | 1999-04-26 | NULL       |
 +------------+---------+-------+------------+------------+

Funzioni aggregate

Prendono il nome di funzioni aggregate le funzioni che compiono delle operazione su piu' valori, generalmente di una colonna. Con le funzione aggregate si puo' usare la clausola "where" e solo con esse la clausola "GROUP BY".

La clausola "group by" permette di letteralmente raggruppare i dati in maniera di evitare le ripetizione e mostrare degli ottimi report, per esempio se voglio sapere quanti eventi sono successi ad un canarini posso scrivere la seguente query: 

 mysql> select nome, evento , count(nome) from eventi group by nome; 
 +------------+------------+-------------+ 
 | nome       | evento     | count(nome) | 
 +------------+------------+-------------+ 
 | Bad        | vetrinario |           1 | 
 | Colombella | cova       |           1 | 
 | Mylady     | cova       |           3 | 
 | Primo      | vetrinario |           1 | 
 +------------+------------+-------------+ 
 4 rows in set (0.01 sec)

Ma se voglio anche la data il report dara' un risultato sbagliato: 

 mysql> select nome, evento , data ,count(nome) from eventi group by nome;
 +------------+------------+------------+-------------+ 
 | nome       | evento     | data       | count(nome) | 
 +------------+------------+------------+-------------+ 
 | Bad        | vetrinario | 2000-03-02 |           1 | 
 | Colombella | cova       | 1995-01-01 |           1 | 
 | Mylady     | cova       | 1999-02-03 |           3 | 
 | Primo      | vetrinario | 1994-07-01 |           1 | 
 +------------+------------+------------+-------------+ 
 4 rows in set (0.01 sec)

COUNT()

Benche' ci siano ben tre date che riguardano il canarino MyLady, nel report viene visualizzata solo la prima. La funzione count() restituisce il numero degli elementi tra parentesi: 

 mysql> select count(nome) from canarini; 
 +-------------+ 
 | count(nome) | 
 +-------------+ 
 |           7 | 
 +-------------+ 
 1 row in set (0.01 sec)

COUNT(DISTINCT)

La funzione count(distinct) si usa come la funzione count(), ma non conta le ripetizioni, per esempio: 

 mysql> select count(distinct sesso) from canarini; 
 +-----------------------+ 
 | count(distinct sesso) | 
 +-----------------------+ 
 |                     2 | 
 +-----------------------+ 
 1 row in set (0.00 sec)

MAX()

La funzione max() si puo' usare solo con i numeri e restituisce il valore massimo tra i dati selezionati.

MIN()

La funzione min() si puo' usare solo con i numeri e restituisce il valore minimo tra i dati selezionati.

AVG()

La funzione avg() si puo' usare solo con i numeri e restituisce il valor medio tra i dati selezionati. Vi ricordo che il valore NULL significa nulla e viene ignorato, quindi la media tra 7 e NULL rimane 7.

SUM()

Questa funzione restituisce si puo' usare solo con i numeri e restituisce la somma dei valori.

STD()

La funzione std() restituisce la deviazione standard dal valo medio cioe' <math> \sqrt{\frac{\Sigma_{i=1}^n(x_i-\overline{x})^2}{n}}</math> dove <math> \overline{x}</math> e' la media e <math> n</math> e' il numero degli elementi.

Funzioni di base di MySQL

Le funzioni intrinseche di MySQL possono essere suddivise in quattro gruppi:

  • Matematiche
  • Logiche
  • Manipolazione di stringhe
  • Manipolazione di data e ora

Funzioni Matematiche

Addizione

Per le addizioni si usa il segno "+", puo' essere utilizzata sia per valori dati direttamente: 

 mysql> select 2+2;
 +-----+ 
 | 2+2 | 
 +-----+ 
 |   4 | 
 +-----+

Sia per sommare i valori delle colonne: 

 mysql> select (colonna_1 + colonna_2) from tabella;

sia mescolata con piu' funzioni: 

 mysql> select (sum(colonna_1) + sum(colonna_2)) as Totale from tabella;

Sottrazione

Come per l'addizione a parte il fatto che si usa il segno "-".

Moltiplicazione

Come per l'addizione a parte il fatto che si usa il segno "*".

Divisione

Come per l'addizione a parte il fatto che si usa il segno "/". La divisione per zero restituisce il valore NULL.

Resto della divisione (modulo)

Il resto della divisione, chiamato anche modulo, viene calcolato con la funzione MOD(a,b); dove a e' il dividendo e b e' il divisore. Per esempio: 

 mysql> select mod(14,3); 
 +-----------+ 
 | mod(14,3) | 
 +-----------+ 
 |         2 | 
 +-----------+

Funzioni logiche

IF() e IFNULL()

La funzione IF() permette di far prendere una decisione all'elaboratore nel caso si verifichi o no. La sintassi per usarla e' la seguente IF(condizione, se e' vero, se e' falso), in pratica verifica la condizione e stampa la condizione "se e' vero" o "se e' falso": 

 mysql> select interi,if(interi > 10, ' e' maggiore 10', 
     ->' e' minore 10') from numeri;

Una funzione simile studiata per le situazioni quando si manipolano dati dove compare il valore NULL e' la funzione IFNULL(valore, se e' NULL). Con questa funzione ci viene restituito il valore espresso come primo argomento, altrimenti se e' NULL il secondo argomento, esempio: 

 mysql> select ifnull(colonna_1,'Valore non immesso') from tabella;}

CASE

Quando si deve far scegliere al computer fra piu' opzioni ci viene in aiuto il comando CASE, che funziona cosi': CASE WHEN condizione1 THEN allora1 WHEN condizione2 THEN allora2 ...ELSE boh?... END Per esempio: 

 mysql> select CASE 
     ->WHEN interi=12 THEN 'vale dodici' 
     ->WHEN interi=6 THEN 'vale 6' 
     ->WHEN interi=32 THEN 'vale 32' 
     ->ELSE 'non vale ne' 12 ne' 32 ne' 6' 
     ->END 
     ->from numeri;

Esiste un altro modo di usare CASE, e l'esempio sottostante vale piu' di mille parole: 

 mysql> select CASE interi
     ->WHEN 12 THEN 'vale dodici' 
     ->WHEN 6 THEN 'vale 6' 
     ->WHEN 32 THEN 'vale 32' 
     ->ELSE 'non vale ne' 12 ne' 32 ne' 6' 
     ->END 
     ->from numeri;

Funzioni ad operatore logico

MySQL fornisce anche molte funzioni ad operatore logico, basta utilizzare le parole di logica riservata AND, OR, NOT, dopo select e la risposta sara' 1 se e' vera, 0 se e' falsa.

ATTENZIONE: qualunque valore diverso da zero passato al computer viene considerato un valore di "vero", quindi "3 AND 4" da' come risultato vero, cioe' 1.

Funzioni per le stringhe e i caratteri

LTRIM(), RTRIM() e TRIM()

La funzione LTRIM() leva gli spazi eccessivi a sinistra della stringa: 

 LTRIM(' Pippo') -> 'Pippo'

RTRIM() leva gli spazi a destra della stringa.

TRIM() li leva da entrambe le parti.

SUBSTRING()

La funzione SUBSTRING(stringa, n) restituisce una stringa a partire solo dal n-esimo carattere: 

 mysql> select substring('ciao a tutti', 6); 
 +------------------------------+ 
 | substring('ciao a tutti', 6) |
 +------------------------------+ 
 | a tutti                      | 
 +------------------------------+

LOCATE()

Il comando LOCATE(parole, frase, n) da' come output il numero della posizione dove si trova la parola cercata a partire dal n-esimo carattere. Per esempio se abbiamo una lista di cognomi e vogliamo solo i dottori basta usare: 

 mysql> select SUBSTRING( 'Dr.', LOCATE('Dr.', colonna_nomi, 1)) from tabella;

REPLACE()

La funzione REPLACE( frase, xxxx, yyyy) cerca all'interno della stringa la stringa xxx e la cambia in yyy, esempio: 

 mysql> select REPLACE('quante belle cose', 'belle', 'brutte');

REVERSE()

La funzione reverse inverte l'ordine dei caratteri di una stringa: 

 mysql> select reverse('ciao'); 
 +-----------------+ 
 | reverse('ciao') | 
 +-----------------+ 
 | oaic            | 
 +-----------------+

UCASE() e LCASE()

Le funzioni UCASE() e LCASE() convertono le stringhe in caratteri, rispettivamente, tutti maiuscoli e tutti minuscoli.

Lavorare con le date

Mysql e' fornito di funzioni che permettono di manipolare le informazioni sulle date. Per esempio e' possibile aggiungere o sottrarre anni, mesi o giorni ad una data e chiedere di far vedere solo l'anno, il mese, il giorno o un valore particolare che esca fuori da nostri calcoli sulle date.

Se io volessi sapere l'eta' dei miei canarini (senza considerare che alcuni sono morti), potrei dare il seguente comando: 

 mysql> select nome, nascita, CURRENT_DATE, 
     ->YEAR(CURRENT_DATE)-YEAR(nascita) AS età  from canarini;
 +------------+------------+--------------+------+ 
 | nome       | nascita    | current_date | età  |
 +------------+------------+--------------+------+ 
 | Primo      | 1994-05-10 | 2004-02-25   |   10 | 
 | Colombella | 1994-08-17 | 2004-02-25   |   10 | 
 | Pinochio   | 1995-02-12 | 2004-02-25   |    9 | 
 | Terry      | 1995-06-27 | 2004-02-25   |    9 | 
 | Bad        | 1998-04-31 | 2004-02-25   |    6 | 
 | Mylady     | 1998-03-11 | 2004-02-25   |    6 |
 | Slimshady  | 1999-04-26 | 2004-02-25   |    5 |
 +------------+------------+--------------+------+ 
 7 rows in set (0.00 sec)

come avete visto il comando "CURRENT_ DATE" da' come output il la data corrente, mentre "YEAR()" fa apparire solo l'anno della data fra parentesi, inoltre e' possibile fare operazioni matematiche con le date come se fossero dei normali numeri. Il comando "AS" serve per cambiare l'etichetta di una colonna, sarebbe stato brutto avere come etichetta "YEAR(CURRENT_ DATE)-YEAR(nascita)".

Se vogliamo scartare l'eta' dei canarini deceduti (per i quali non ha senso calcolare l'eta'), possiamo modificare la nostra richiesta di dati (che in inglese si chiama query): 

 mysql> select nome, nascita, 
     ->current_date, year(current_date)-year(nascita) 
     ->as età from canarini where morte IS NULL;
 +-----------+------------+--------------+------+ 
 | nome      | nascita    | current_date | età   |
 +-----------+------------+--------------+------+ 
 | Bad       | 1998-04-31 | 2004-02-25   | 6    |
 | Mylady    | 1998-03-11 | 2004-02-25   | 6    |
 | Slimshady | 1999-04-26 | 2004-02-25   | 5    |
 +-----------+------------+--------------+------+ 
 3 rows in set (0.00 sec)

Da notare come ho usato il comando "IS NULL" al posto di "= NULL", perche' NULL non e' un carattere ma la rappresentazione di un campo vuoto.

Altre funzioni utili sulle date sono:

  • DAYOFWEEK(data) : Da' come output un numero rappresentante il giorno (1 = Domenica, 2=Lunedi',...)
  • DAYOFMONTH(data) : Da' come output il numero del giorno, quindi puo' variare da 1 a 31.
  • DAYOFYEAR(data) : Da' come output il numero del giorno rispetto all'anno, cioe' tra 1 e 366.
  • DAYNAME(data) : Da' come output il nome del giorno in inglese (Monday,...)
  • MONTH(data) : Da' come output il numero del mese.
  • MONTHNAME(data) :Da' il nome del mese in inglese
  • QUARTER(data) : Mostra in quale quadrimestre dell'anno siamo (1-4).
  • WEEK(data) WEEK(data, da che giorno inizio a contare) : Dice in che settimana dell'anno siamo (1-53) e se specificato il secondo argomento, si conta la settimana da domenica (mettendo 0) o da Lunedi' (mettendo 1).
  • YEAR(data) : L'anno di una data
  • YEARWEEK(data) YEARWEEK(dat, da che giorno inizio a contare) : Anno e settimana dell'anno, es: 199852
  • HOUR(tempo) : Ora (0-23)
  • MINUTE(tempo) : Minuti (0-59)
  • SECOND(tempo) : Secondi (0-59)
  • ADD_ DATE(data, INTERVAL n Tipo) : Potete aggiungere o sottrarre valori alla data in maniera sempre corretta, in modo da evitare di scrivere 13 come mese o altri errori che avvengono manipolando le date come numeri. Potete utilizzare anche numeri negativi come numeri, al posto di "Tipo" va' sempre specificato che cosa stiamo aggiungendo (mesi, anni, minuti, ore, secondi). Non potete aggiungere piu' di un tipo alla volta, pero' potete incapsularli. Esempio: add_date('1998-5-3', interval -5 month); add_ date(add_ date('1998-5-3', interval 5 month), interval 3 year);
  • DATE_ FORMAT(data,formato) : Permette di cambiare il formato della data nell'output, ha tantissime opzioni, leggetele nella guida in linea (info mysql).
  • CURRENT_ DATE : Dice la data di oggi
  • CURRENT_ TIME : Dice l'ora attuale
  • CURRENT_ TIMESTAMP : Data e ora attuale
  • UNIX_ TIMESTAMP(data) : La data in formato Unix, cioe' nei secondi che sono trascorsi dal primo gennaio 1970, data di creazione del primo computer Unix (inchinatevi tutti...)
  • SEC_ TO_ TIME(secondi) : Trasforma i secondi in ore minuti secondi (2378=00:39:38)
  • TIME_ TO_ SEC(tempo) : Dice di quanti secondi e' composto un tempo (22:23:00=80580)

Le variabili

Per fare comandi piu' complessi potete memorizzare dei valori nelle variabili. Le variabili vanno prima dichiarate con SET: 

 SET @nuovaVariabile;
 SET @var2:=35;
 SET @var3:='Mario';

Potete mettere dei valori anche sa SELECT utilizzando INTO: 

 SELECT nome,cognome INTO @var1,@var2 FROM agenda ;

e le potete usare ovunque in qualunque espressione.

Esecuzione di piu' istruzioni contemporaneamente: come evitare errori.

Quando si progetta un database, bisogna essere molto attenti a cosa puo' succedere se due o piu' utenti lavorano con gli stessi dati. Se, per esempio, si progetta un database per il commercio on-line, e' molto probabile che esista una tabella che contenga le varie quantita' di oggetti presenti nel magazzino. Quando due utenti ordinano via web lo stesso oggetto, i conti della tabella potrebbero dare risultati falsi sia ai venditori che al proprietario del magazzino. Facciamo due esempi grafici con la figura 1 e la figura 2


Fig 1: Errore nella progettazione

Se invece imponiamo di bloccare le tabella del magazzino finche' non e' stata aggiornata, i problemi vengono risolti.


Fig 2: Errore nella progettazione

Per bloccare le tabelle esiste il comando "LOCK TABLE", che ha due opzioni: bloccare le tabelle in modalita' READ o in modalita' WRITE. La modalita' READ impone che la tabella rimanga immutabile fino alla fine delle operazioni, puo' essere letta da tutti, ma modificata da nessuno, incluso il thread che l'ha bloccato. In modalita' write, invece, e' accessibile sia in lettura che in scrittura solo al thread che l'ha bloccata; visto che deve essere modificata, infatti, se qualcuno la leggesse, vedrebbe dei dati sbagliati.

La sintassi da seguire e' quella dell'esempio seguente: 

 mysql> LOCK TABLES tabella1 WRITE, tabella2 READ, tabella3 WRITE, ...;
 ...
 mysql> UNLOCK TABLES;

Ad ogni comando di "lock", puo' essere aggiunta una priorita' alta o bassa per ordinare in base ad un'effettiva priorita' le richieste di blocco, a parita' di priorita' una richiesta di WRITE e' prioritaria rispetto ad una di READ.

ATTENZIONE 1: Se mettete dei blocchi senza capire bene cosa state facendo rischiate di bloccare il database in eterno. Per esempio se il thread A blocca la tabella X e aspetta la tabella Y libera, mentre il thread B ha bloccato Y e aspetta la tabella X libera, si ottiene un bel blocco del sistema...e tanti saluti a MySQL!!!

ATTENZIONE 2: Se si tratta di un unico comando non e' necessario scomodare il comando lock, MySQL e' perfettamente in grado di gestire le richieste di piu' comandi singoli contemporaneamente che vanno a modificare lo stesso dato; al contrario, se sono necessarie piu' operazioni critiche in fila senza che nessuno metta mani ai dati usate senza problemi il comando lock.

Le transazioni

Se invece dovete fare tutta una serie di comandi, le transazioni sono la cosa migliore. Tutti i comandi vanno da mandati insieme, MySQL le elabora, vede qual e' il modo più veloce per farle (milgiorando anche di 1000 volte piu' veloce rispetto al tempo di farle una alla volta) e poi quando vengono eseguite, nient'altro e' eseguito. In questo modo evitate anche errori di sovrapposizioni. Esempio: 

 START TRANSACTION;
 UPDATE tab1 SET sesso='M' WHERE nome='Mario';
 UPDATE tab1 SET sesso='F' WHERE nome='Luisa';
 COMMIT;

Backup e ripristino

Per effettuare dei backup dei nostri database, MySQL mette a disposizione due comandi molto utili MYSQLIMPORT e MYSQLDUMP; questi comandi vanno usati fuori da mysql, piu' esattamente da una shell. Vediamo come usarli.

mysqldump

Se c'e' bisogno di salvare alcune o tutte le tabelle di un database, basta usare questo comando con le dovute opzioni. Esiste anche la possibilita' di copiare direttamente i file ma con questo comando verranno scritti anche i comandi che servono a creare il database. Ecco le opzioni principali:

  • -add-locks :Questa opzione e' molto utile, perche' blocca tutte le tabelle da copiare del database, evitando di copiare tabelle mentre si stanno modificando i dati, rendendole "inconsistenti".
  • -add-drop-table : Aggiunge il comando "DROP TABLE" proma di ogni comando di creazione tabella, cosi' quando si importano, se gia' esistono dei duplicati, vengono cancellati.
  • -e : Il comando "INSERT" e' utilizzato in modo da essere mlto veloce quando si riimettono i dati.
  • --opt : E' la somma di tutti le precedenti opzioni.
  • -d : Tralascia i dati, serve per provare le strategie di backup piu' adatte.
  • -where='condizione' : Serve per specifare di salvare solo alcuni dati.

Esistono molte altre opzioni, queste sono solo le principali.

La sintassi da usare e' la seguente: 

 mysqldump [opzioni] database [tabelle] > miofiledibackup.sql

Chiaramente tra le parentesi quadre ci sono i comandi opzionali.

Un esempio di backup totale molto comodo si puo' fare col seguente script: 

 #!/bin/bash
 mysqldump -p --opt --all-databases > tutto.sql

semplice ed efficace!

mysqlimport

Con il comando "mysqlimport" e' possibile importare dei database che contengano le istruzione di creazione, come quelli che si ottengono con il comando "mysqldump". La sintassi e' la seguente: 

 mysqlimport [opzioni] database file1.sql file2.sql ...

Comunque, se il file si trova in locale, visto che i file sql sono una serie di istruzioni da dare in pasto a mysql, basta usare il seuente comando: 

 $ mysql -p < file.sql

In quale cartella salvare e caricare i files?

Sempre piu' spesso il server MySQL e' configurato con l'opzione --secure-file-priv che limita l'accesso ai file a solo una cartella. Per scoprirequale sia basta lanciare la richiesta: 

 SHOW VARIABLES LIKE "secure_file_priv";

Per eliminare questa opzione basta aggiungere la seguente riga nel file di configurazione di mysqld: 

 secure-file-priv = ""