Laboratorio di Sistemi Informativi

Introduzione a MySQL

Come abbia detto in credenza, MySQL è un DBMS con architettura client/server.

Architettura Client-Server

Quando il server MySQL è installato in un sistema Linux, tipicamente è configurato in modo tale da partire automaticamente all'accensione del sistema. Per verificare che il server MySQL stia girando, basta controllare se il processo mysqld è in esecuzione (abbiamo visto la lezione precedente come fare). mysqld è il server MySQL.

MySQL è un sistema client-server. Il vero e proprio DBMS è il server, che gestisce i database ed esegue i comandi SQL. Per poter collegarsi al server e impartire ad esso dei comandi, è necessario un programma client. Il più semplice di tutti è il monitor di MySQL.

Una sessione di lavoro col il monitor di MySQL

Per far partire il monitor, entrare in una finestra terminale e lanciare il programma mysql. Il sistema risponde con qualcosa del tipo

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

e attende l'immissione dei comandi. I comandi devono esseri comandi SQL validi (riconosciuti da MySQL) e terminati da un punto e virgola.

ATTENZIONE! mysql> è il prompt di MySQL! Non confonderlo con il prompt della shell! Dalla shell si possono invocare altri programmi e dare comandi al sistema operativo. Dal prompt di MySQL, invece, si possono inserire dei comandi in SQL che vengono eseguiti dal server del database. Se provate un comando come ls il sistema risponde con

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ls' at line 1

Questo è un messaggio di errore standard di MySQL. C'è un numero che identifica il tipo di errore seguito dalla sua descrizione.

Vediamo alcuni comandi SQL di esempio:

show databases : mostra le basi di dati presenti nel sistema. Attualmente ce n'è visibile solo una, detta test, pensata per effettuare degli esperimenti.
use <nomedb> : seleziona un database come database corrente. Tutte le operazioni SQL da quel momento agiranno all'interno del database selezionato.
show tables [from <nomedb>]: mostra le tabelle che fanno parte di un database. Se non si specifica nessun database, si considera il database corrente, impostato tramite il comando use.
quit : esce dal monitor di MySQL.

Come abbiamo detto, all'inizio è presente un unico database, test. Un database è fondamentalmente un insieme di tabelle. All'inizio il database test è vuoto, non contiene neanche una tabella. Vedremo in seguito che è possibile creare nuovi database, ma per il momento lavoriamo all'interno di test.

ATTENZIONE!! Se non si termina il comando con un punto e virgola, il monitor di MySQL risponde con il prompt

->

e richiede di completare il comando. Questo perchè, per MySQL, un comando non è finito finchè non appare il punto e virgola! In realtà, ci sono delle eccezioni. Ad esempio use funziona anche senza punto e virgola finale. La differenza è che use viene interpretato dal monitor di MySQL (ovvero dal processo mysql) mentre gli altri comandi sono interpretati dal server di MySQL (ovvero dal processo mysqld). Onde evitare problemi, basta mettere sempre il punto e virgola alla fine.

Un database senza tabelle non serve a molto... è il momento di iniziare a crearne qualcuna. Il comando SQL che fa a caso nostro è CREATE TABLE. La sua sintassi è la seguente:

CREATE TABLE <nome-tabella> (
nome-colonna-1 tipo [modificatori]
[, nome-colonna-2 tipo [modificatori]]
...
)

Creiamo quindi una tabella per i dipendenti di un ufficio. Prima di tutto dobbiamo entrare in un database, altrimenti il comando fallisce. Visto che l'unico database esistente è test, entriamo lì dentro con il comando

use test;

e successivamente:

CREATE TABLE dipendenti (
    codfiscale char(16) primary key,
    nome        char(30),
    cognome    char(30),
    datanascita date,
    tempopieno enum('y','n') not null,
    anniserv    int default 0
);

Il moto più comodo di inserire quest'ultimo comando è col "taglia e incolla". Il taglia è incolla in Linux si può usare in due modi distinti:

alla Windows: selezionare il testo da copiare sul browser, col taso destro del mouse cliccare su Copia, poi spostarli sulla finestra e premere di nuovo il tasto destro del mouse: cliccare quindi su Incolla.
in maniera abbreviata: selezionare col mouse il testo da copiare, spostarsi sulla finestra della shell e premere il tasto centrale del mouse.

È possibile inserire valori nella tabella con

insert into dipendenti values ('codfisc1','mario','rossi','1960-12-3','y',1);

ATTENZIONE!!! Quando si inseriscono i valori in una tabella con il comando insert, dopo la parola chiave values vanno specificati tanti parametri quanti sono i campi nella tabella che si vuole modificare. Così, un comando del tipo

insert into dipendenti values ('codfisc1','mario','rossi','1960-12-3',1);

termina con il messaggio di errore

ERROR 1136: Column count doesn't match value count at row 1

Se non si vogliono specificare tutti i campi, ma solo alcuni di essi, è possibile usare un comando del tipo

insert into dipendenti(codfiscale,nome,cognome) values ('codfisc2','mario','bianchi');

dove si indica, dopo il nome della tabella, i nomi dei campi che si vogliono specificare. Al solito, dopo values bisognerà specificare tanti elementi quanti sono i campi indicati dopo il nome della tabella. I campi che non sono indicati verranno riempiti con valori di default.

ATTENZIONE!!! Un altro prompt che talvolta compare è

'>

Questo vuol dire che nella riga precedente avete aperto degli apici, per inserire una stringa, senza poi chiuderli. Finchè non inserite un altro apice, il sistema continuerà a riprovorvi questo prompt, e tutto quello che digitate (compresi i caratteri di "andata a capo") verrà a far parte della stringa

Talvolta è interessante vedere non il contenuto della tabella, ma la descrizione dei campi che la compongono. Si possono allora usare i seguenti comandi:

describe <nometabella> : visualizza la descrizione dei campi che compongono la tabella;
show create table <nometabella>: visualizza il comando CREATE TABLE che è necessario per ricostruire la tabella.

Ad esempio, describe dipendenti restituisce il seguente risultato:

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| codfiscale  | char(16)      |      | PRI |         |       |
| nome        | char(30)      | YES  |     | NULL    |       |
| cognome     | char(30)      | YES  |     | NULL    |       |
| datanascita | date          | YES  |     | NULL    |       |
| tempopieno  | enum('y','n') |      |     | y       |       |
| anniserv    | int(11)       | YES  |     | 0       |       |
+-------------+---------------+------+-----+---------+-------+
6 rows in set (0.05 sec)

I campi field e type sono autoesplicativi. Il campo Null indica se il corrispondente attributo può assumere valori nulli oppure no, il campo Key specifica qual è la chiave primaria della tabella, e il campo Default indica i valori di default dei vari attributi. Vedremo in futuro il significato del campo Extra.

Per quanto riguarda l'output del comando SHOW CREATE TABLE dipendenti, ci sono da notare due cose:

l'output è "sporcato" dalla presenza di vari caratteri che dovrebbero costituire l'intelaiatura di una tabella (come in describe table) ma che, a causa della dimensione eccessiva di quest'ultima, si trasformano solo in fonte di confusione;
il comando visualizzato non è lo stesso di quello che è stato dato per creare la tabella, perchè vengono visualizzati anche i parametri opzionali (come default) che avevamo omesso.

Per quanto riguarda il primo problema, è possibile risolverlo sostituendo al punto e virgola finale la combinazione \G . Questa indica al monitor MySQL di visualizzare il risultato del comando in un altro formato, più adatto nel caso di tabelle particolarmente "larghe". Ad esempio, confrontare l'output noemale di select * from dipendenti con quello prodotto con \G:

*************************** 1. row ***************************
codfiscale: codfisc1
       nome: mario
    cognome: rossi
datanascita: 1960-12-03
tempopieno: y
   anniserv: 1
*************************** 2. row ***************************
codfiscale: codfisc2
       nome: mario
    cognome: bianchi
datanascita: NULL
tempopieno: y
   anniserv: 0
2 rows in set (0.00 sec)

Esercizio 1

Iniziamo la creazione della nostra applicazione di prenotazione online. Si vuole creare una tabella che contiene informazioni sugli aerei della nostra compagnia aerea. I campi di questa tabella devono essere:

id, di tipo char(20) che funge da chiave primaria
produttore
modello
dataimm che contiene la data di immatricolazione (primo volo) dell'aereo
numposti, che contiene il numero di posti disponibili sull'aereo

Comandi per la manipolazione di tabelle

Mentre siamo in argomento, analizziamo altri comandi che possono essere utilizzati per la manipolazione delle tabelle.

Prima di tutto, possiamo eliminare una tabella (e tutto il suo contenuto) con:

DROP TABLE <nometabella>: elimina una tabella.

Talvolta occorre modificare la struttura di una tabella perchè ci rendiamo conto che essa non soddisfa i requisiti richiesti. Il comando adatto è ALTER TABLE. Alcuni esempi:

ALTER TABLE <nometab> DROP PRIMARY KEY: elimina la chiave primaria. Dopo questo comando la tabella non ha più nessuna chiave primaria.
ALTER TABLE <nometab> ADD PRIMARY KEY (<nome campi>): aggiunge una chiave primaria alla tabella. I campi specificati tra parentesi formeranno la nuova chiave primaria.
ALTER TABLE <nometab> DROP <nomecolonna>: elimina una colonna
ALTER TABLE <nometab> CHANGE <vecchio_nomecolonna> <nuovo_nomecolonna> <nuono tipo>: cambia nome e tipo di una colonna
ALTER TABLE <nometab> ADD <nome_colonna> <tipo>: aggiunge un nuovo campo alla tabella
ALTER TABLE <nometab> RENAME <nuovo nome tabella>: cambia nome a una tabella

Esercizio 2

Comandi per la manipolazione di database

MySQL può gestire contemporaneamente più di un database. In questo modo lo stesso server può essere utilizzato per più di una applicazione. Ogni applicazione opera su un database distinto, e di solito, la gestione di ogni database è affidata a uno o più utenti differenti.

Vedremo in una lezione futura come è possibile cambiare i privilegi dei vari utenti in modo da dargli accesso a database diversi. Nella configurazione di default, gli utenti hanno accesso soltanto al database test tranne un utente particolare, l'utente root, che ha accesso totale al server MySQL e può anche creare nuovi database.

Quando si fa partire il monitor di MySQL, è possibile specificare il nome utente con il quale collegarsi. Se vogliamo collegarci col nome utente root basta usare l'opzione -u (user), in questo modo:

mysql -u root

Se dal monitor vogliamo controllare di essere veramente l'utente root, possiamo dare il comando

select user() : visualizza il nome utente usato correntemente per collegarsi a MySQL

È ovvio che in un sistema "vero", il collegamento con nome utente "root" dovrebbe essere protetto da password, altrimenti tutti possono avere accesso indiscriminato ai database gestiti da MySQL.

Quando si entra come utente root, il comando show databases mostra anche il database mysql. Questo è un database speciale che mantiene informazioni usate internamente da MySQL. È possibile inoltre creare nuovi database e cancellarli con i comandi:

create database <nomedb>: crea un nuovo database di nome <nomedb>
drop database <nomedb>: cancella il database <nomedb> e tutte le tabelle presenti al suo interno.
grant all privileges on <nomedb>.* to <nomeutente>@localhost: concede all'utente <nomeutente> tutti i diritti di accesso al database <nomedb>. La indicazione @localhost serve a specificare che a <nomeutente> deve essere consentito l'accesso solo dalla macchina locale. Vedremo infatti che è possibile utilizzare il monitor di MySQL per connettersi ad un server che risiede su un altro computer.

ATTENZIONE!! Notare che gli utenti MySQL e gli utenti Linux non coincidono del tutto. Normalmente, se l'utente "pippo" da il comando mysql, il monitor si collega al server con nome utente "pippo". Tuttavia, col parametro -u, è possibile cambiare a piacimento il nome utente utilizato per il collegamento. Subito dopo l'installazione del software tutti i nomi utenti sono equivalenti, tranne root.

Esercizio 3

Creare un database di nome airdb e ricreare al suo interno la tabella aerei vista nell'esercizio precedente. È possibile usare show create table per recuperarne la definizione. Infine, concedere all'utente studente tutti i diritti di accesso al database airdb.

Il comando HELP

Nella versione 4.1.x di MySQL è stato aggiunto un utile comando di aiuto (HELP). Esistono vari modi per utilizzare il comando:

help: visualizza i comandi che sono interpretati direttamente dal monitor di MySQL. Si può vedere, ad esempio, come i comandi use e quit ricadano in questa categoria.
help contents: visualizza un elenco di argomenti su cui è possibile chiedere aiuto. Ad esempio, sulla mia macchina genera l'elenco:

Administration
Column Types
Data Definition
Data Manipulation
Functions
Geographic features
Transactions

Si può quindi richiedere informazioni sui tipi di colonna supportati da MySQL con help Column Types. I messaggi di aiuto per alcuni argomenti rimandano a loro volta a sotto-argomenti.
help <comando>: visualizza informazioni sul comando specificato. Ad esempio, help create table visualizza un messaggio di aiuto sul comando create table.

Notare che su MySQL 4.0.x e precedenti, il comando help non esiste!

Tipi di Dato

Particolare attenzione va posta nella scelta del tipo di dato più adatto per ogni campo. I libri di MySQL contengono una trattazione dei vari tipi di dato disponibili. Quì ne vediamo alcuni:

Valori Interi

Sono usati per rappresentare numeri interi positivi e/o negativi. Ne esistono varie varianti, a seconda del numero di bit destinati alla codifica del numero, e quindi dell'intervallo massimo di valori ammissibili.

TINYINT: 8 bit, -128...127
SMALLINT: 16 bit, -32.768 ... 32.767
MEDIUMINT: 24 bit, -8.338.608 ... 8.338.607
INT: 32 bit, -2.147.483.648 ... 2.147.483.647
BIGINT: 64 bit, -9.223.372.036.854.775.808 ... 9.223.372.036.854.775.807

Tutti i tipi interi posso essere dichiarati UNSIGNED. In questo caso il valore massimo dei dati aumenta: TINYINT passa fa -128.. 127 a 0..255, SMALLINT da -32.768..32.767 a 0...65535.. e così via. Il modificatore UNSIGNED va messo dopo il tipo e non prima, come di solito si fa in Java e in C. Ad esempio:

CREATE TABLE T (VAL INT UNSIGNED);

Valori in virgola mobile

Sono usati per rappresentare numeri "con la virgola". Anche questi si distinguono in base al numero di bit usati per la rappresentazione, che influisce sia sull'intervallo massimo di valori rappresentabili che sul numero di cifre significative.

FLOAT: 32 bit, singola precisione.
Valori ammessi: da -3.402823466E+38 a -1.175494351E-38, 0, e 1.175494351E-38 to 3.402823466E+38
DOUBLE: 64 bit, doppia precisione.
Valori ammessi: da -1.7976931348623157E+308 a -2.2250738585072014E-308, 0, e 2.2250738585072014E-308 to 1.7976931348623157E+308

Tutti i tipi in virgola mobile posso essere dichiarati UNSIGNED.

Esercizio 4

Fare un po' di esperimenti con i tipi. In particolare:

cosa succede se tento di inserire in un campo intero un numero più grande di quello che è possibile rappresentare? (ad esempio un valore maggiore di 127 in un campo tinyint)
cosa succede se trasformo (con ALTER TABLE) un campo int in un campo int unsigned e la tabella conteneva dei valori negativi?
provate a memorizzare il numero 0.3 in un campo float.. e poi cambiare il campo in double.. cosa succede?

Valori stringa

CHAR(n): stringa di n caratteri a lunghezza fissa. Se la stringa è più corta, viene riempita di spazi alla fine.
VARCHAR(n): stringa di al massimo n caratteri, n <= 255

I tipi a lunghezzi fissa come CHAR(n) creano tabelle più efficienti di quelli a lunghezza variabile, a fronte di uno spreco di memoria di massa.

TINYTEXT(n): come VARCHAR(n)
TEXT(n): come VARCHAR(n) ma n fino a 65535 = 2^16
MEDIUMTEXT(n): come VARCHAR(n) ma n fino a 2^24
LONGTEXT(n): come VARCHAR(n) ma n fino a 2^32

Esercizio 5

Notare che MySQL esegue delle conversioni automatiche. Ad esempio, considerate il comando:

CREATE TABLE T1 (S1 CHAR(10), S2 CHAR(10), S3 CHAR(2));

e controllate il risultato con DESCRIBE T1;

Poi fate lo stesso con

CREATE TABLE T2 (S1 CHAR(10), S2 VARCHAR(10), S3 CHAR(2));

e controllate il risultato con DESCRIBE T2;

Cosa è successo?

Tipi insiemi ed enumerati

ENUM('val1','val2',.... 'valn'): un valore qualunque tra quelli indicati (massimo 65535). In realtà è ammesso anche il valore '' (stringa nulla).
SET('val1','val2'... 'valn'): uno o più valori tra quelli indicati (massimo 64).

MySQL rappresenta i valori ENUM e SET con dei numeri, per cui, quando è possibile, è preferibile riccorre a questi tipi piuttosto che a semplici stringhe. Per i valori ENUM usa un numero da 1 e 65535 per ogni valore elencato (e usa 0 per la stringa vuota). Per i valori SET usa un numero a 64 bit, dove ogni bit rappresenta un possibile elemento dell'insieme (il bit è a 1 set l'elemento fa parte dell'insieme, a 0 altrimenti)

Tipi data

DATE: data nel formato aaaa-mm-gg
DATETIME: ora e data nel formato aaaa-mm-gg hh:mm:ss

I valori di tipo DATE coprono un range che va dal 1 gennaio 1 DC al 31 dicembre 9999 DC. Tuttavia, è possibile specificare tutta una serie di date "non valide" come il 2002-2-31 (non esiste il 31 febbraio) o il
0000-1-1 (non esiste l'anno zero). L'idea è che, non controllando la validità delle date, il server MySQL è più efficiente: i controlli possono comunque essere effettuati da parte del software applicativo che utilizza MySQL come server database.

Un valore è 0000-00-00 che viene utilizzata da MySQL automaticamente quando l'utente specifica una stringa che il server non riesce a interpretare come data (ad esempio quando si vuole scrivere 'pluto' in un campo di tipo data).

Il tipo DATETIME, in più, consente di specificare un'ora da 00:00:00 a 23:59:59.

Campi AUTO_INCREMENT

Nel database airdb abbiamo usato un campo id di tipo char(20) come chiave primaria. In realtà una scelta di questo tipo non conviene mai: o si usa un qualche attributo "sensibile" della tabella come chiave primaria (ad esempio il codice fiscale), oppure conviene usare delle chiavi primarie "sintetiche" (cioè prive di qualsiasi significato effettivo e usate solo come identificatori) di tipo intero.

Creiamo ad esempio la seguente tabella:

CREATE TABLE T3 (VALINT INT PRIMARY KEY, NOME CHAR(20), COGNME CHAR(20), NASCITA DATE);

Per inserire valori in questa tabella dobbiamo specificare anche il valore di VALINT, con comandi del tipo:

INSERT INTO T3 VALUES (1,'mario','rossi','2002-12-3');

Il problema è che, visto che VALINT non ha alcun significato, non è facile decidere un valore appropriato, visto che bisogna stare attenti a sceglierne uno che non è già presente nella tabella. Possiamo semplificarci il lavoro specificando la chiave VALINT come AUTO_INCREMENT.

ALTER TABLE T3 CHANGE VALINT VALINT INT AUTO_INCREMENT;

In questo modo non abbiamo più bisogno di inserire valori per il campo VALINT: li genera MySQL automaticamente, quando si tenta di inserire un valore null per la chiave. Ad esempio:

INSERT INTO T3 VALUES (null,'mario','bianchi','2000-1-4');

oppure

INSERT INTO T3(NOME,COGNOME) VALUES ('giuseppe','verdi');

Esercizio 6

Esercizio 7

Abbiamo visto due modificatori per i tipi di MySQL: UNSIGNED e AUTO_INCREMENT. Ne esiste ancora un altro, ZEROFILL. Provare a creare una tabella con un campo di tipo intero zerofill, inserire dei dati, e vedere il risultato.

Accesso ad altri server

Il monitor di MySQL può essere utilizzato per collegarsi ad un server che risiede in un altra macchina. Per far ciò usare l'opzione "-h <nomepc>" dove <nomepc> può essere o un nome simbolico o un indirizzo IP. Ad esempio

mysql -h 192.167.2.3

si collega al server MySQL (se presente) alla macchina che ha indirizzo IP 192.167.2.3. Perchè ciò sia possibile è necessario:

che nel server SQL siano stati concessi degli opportuni diritti agli utenti che si vogliono collegare dall'esterno; ad esempio, si può usare il comando

grant all privileges on test.* to studente@'%'

per consetire all'utente "studente" di collegarsi da qualunque macchina e avere accesso al database test. Notare l'uso di '%' invece di localhost per indicare che accettiamo connessioni da computer remoti.
che non ci sia nessun firewall che blocca il collegamento. Sulla versione di Linux installata in laboratorio si può disattivare il firewall eseguendo il programma Applicazioni -> Impostazioni di Sistema -> Livello di Sicurezza.

Una volta entrati sul server remoto il tutto procede normalmente, come quando siamo collegati al server locale. Ovviamente, se più persone sono collegate allo stesso server, le modifiche apportate da un utente sono immediatamente visibili a tutti gli altri.

Nota: per sapere il proprio indirizzo IP si può utilizzare il comando /sbin/ifconfig eth0. L'indirizzo IP è costituito dai 4 numeri (separati da punti) dopo la scritta inet addr. Ad esempio, se l'output è

eth0      Link encap:Ethernet HWaddr 00:E0:18:18:1E:F1
          inet addr:131.114.3.121 Bcast:131.114.3.255 Mask:255.255.254.0
          UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
          RX packets:32576 errors:1 dropped:16 overruns:1 frame:0
          TX packets:13866 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:18835992 (17.9 Mb) TX bytes:1846804 (1.7 Mb)
          Interrupt:9 Base address:0x4c00

l'indirizzo IP è 131.114.3.121.

In alternativa, si può attivare l'applet Network Monitor cliccando col tasto destro sul pannello (la barra in alto o in basso nel Desktop) e selezionando "Aggiungi al Pannello...".

Lezione Precedente

Lezione Successiva