Un ripasso di SQL e MySQL

L'informazione è ormai manipolata in ogni contesto e negli ultimi anni si è affidata sempre più a strumenti informatici. La raccolta e la manipolazione dell'informazione è resa possibile grazie all'uso di database che in ambito informatico viene molto spesso chiamato DBMS: database management system. Nel corso degli anni sono state introdotte diverse strutture per DBMS: gerarchico, reticolare, relazionale, ad oggetti e semantico. In questo ripasso ci occuperemo di un DBMS relazionale: MySQL.

MySQL è un DBMS distribuito con licenza GPL, chiunque può accedere al codice sorgente del DBMS per apportare modifiche. In un database relazionale l'informazione viene memorizzata sotto forma di tabelle, oguna di queste può poi essere messa in relazione con altre tabelle del database. MySQL si basa sul noto modello client/server e prevede pertanto un'interfaccia di tipo client ed una di tipo server. L'interfaccia che MySQL offre di default al programmatore è a riga di comando, chi vuole può in ogni caso installare quella grafica.

Il client interroga o comanda il server (se ha i permessi per farlo) affinchè quest'ultimo restituisca l'informazione cercata (se presente nel database) oppure aggiunga al database una nuova informazione. L'interrogazione di un database (sia per la ricerca dei dati che per la loro immissione), detta query, avviene con un opportuno linguaggio (standard) noto con il nome di SQL (Struct Query Language). Per interagire con il server MySQL bisogna per prima cosa lanciare il daemon. Vediamo come farlo con l'Aspire One (che usa come sistema operativo di default Linux Linpus Lite, le istruzioni per l'avvio del server e client MySQL si adattano comunque a ogni sistema operativo Linux-like):

[user@localhost ~]$ sudo service mysqld start
Starting MySQL: [ OK ]
[user@localhost ~]$

Altri possibili comandi sono: stop (per fermare il daemon) e restart (per riavviare il daemon). L'interfaccia client viene invece avviata con il comando:

[user@localhost ~]$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.0.45 Source distribution

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

mysql>

Il prompt è ora in attesa di comandi. Se vogliamo ad esempio conoscere i nomi dei database che di default si trovano presso il server MySQL va digitato questo comando:

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

mysql>

Per selezionare un database digitiamo:


mysql> use test;
Database changed
mysql>


Prima ancora di vedere come creare database e tabelle, diamo ora uno sguardo ai tipi di dati che MySQL offre al programmatore per descrivere la propria realtà di riferimento.

Tipi di dati numerici

MySQL offre, per i numeri interi, questi tipi di dati:

• TINYINT, occupa 1 byte;
• SMALLINT, occupa 2 byte;
• MEDIUMINT, occupa 3 byte;
• INT, occupa 4 byte;
• BIGINT, occupa 8 byte;

Per i numeri decimali, invece:

• FLOAT, occupa 4 byte;
• DOUBLE, occupa 8 byte;
• DECIMAL(M,D), occupa M+2byte;

Il tipo DECIMAL permette di specificare con M e D, rispettivamente, il numero totale delle cifre che formano l'intero numero decimale e il numero di cifre per la parte decimale. I tipi di dati appena visti possono prevedere o meno delle opzioni. Con l'opzione UNSIGNED, ad esempio, si indica al server MySQL che l'attributo in questione non può avere valori negativi. Con l'opzione ZEROFILL si indica al server MySQL di allineare i numeri (in verticale) di un attributo riempiendolo con tanti zeri. Con l'opzione AUTOINCREMENT si imposta per l'attributo di una tabella una particolare proprietà: ad ogni istanza dello stesso il valore dell'attributo viene incrementato di un'unità.

Tipi di dati per testo

Per i tipi di dato orientati alla gestione del testo, ossia per le stringhe, MySQL prevede due utili tipi:

• CHAR(M), occupa M byte;
• VARCHAR(M), occupa M byte;

L'occupazione (fissa) in memoria viene specificata dal programmatore. Gli attributi di una tabella descritti dal tipo CHAR occupano in memoria uno spazio pari ad M byte. Al contrario invece, gli attributi di una tabella descritti dal tipo VARCHAR occupano un numero di byte variabile che al più può essere di M byte. In altre parole, con VARCHAR l'occupazione in memoria dell'informazione (ovvero della stringa in questo caso) varia a seconda dell'occorrenza.

I tipi di dati appena visti consentono di inserire in un database stringhe non più lunghe di 255 caratteri. Se tale limite non è sufficiente al nostro progetto possiamo allora adoperare altri tipi di dati come: BLOB e TEXT.

Tipi di dati per le date

L'informazione molto spesso deve poter trattare date e ore del giorno. A tale proposito MySQL offre due tipologie di dati, quelli orientati appunto alle date e quelli orientati esclusivamente ad una parte della data. Per la gestione di date complete (che comprendono quindi gli attributi: anno, mese, giorno, ora,minuto e secondo) si può fare riferimento ai tipi: TIMESTAMP (che occupa 14 byte) e DATETIME (che occupa 8 byte). Per la gestione parziale di una data si può invece fare riferimento ai tipi: DATE, TIME e YEAR.

Per il tipo TIMESTAMP bisogna considerare il seguente formato: AAAAMMGGHHMMSS. Passando a TIMESTAMP un parametro per modificare l'occupazione in byte del suddetto formato se ne modifica allora il contenuto. Ad esempio, con TIMESTAMP(12) vado ad escludere gli ultimi 2 byte, quelli che trattano cioè i secondi, e modifico in questo modo il formato: AAAAMMGGHHMM. Ancora, con TIMESTAMP(10) vado ad escludere gli ultimi 4 byte, quelli cioè che trattano i secondi e i minuti, e modifico in questo modo il formato: AAAAMMGGHH.

Se non siamo interessati per intero alla data di un evento possiamo allora usare il tipo DATE (che occupa 3 byte). La restante parte della data, quella che si occupa delle ore, dei minuti e dei secondi, è invece gestita dal tipo TIME (che occupa 3 byte).

DDL, data definition language

Il DDL è il linguaggio usato per definire gli schemi logici (ed eventualmente le autorizzazioni per l'accesso ai dati). Esso si fonda essenzialmente su tre parole chiavi: create (creare), drop (cancellare) e alter (modificare). La parola chiave create permette di creare oggetti nella base di dati:

create table;
create domain;
create view;
create constraint;

Per la creazione di un database:


mysql> create database segreteria;
Query OK, 1 row affected (0.00 sec)

mysql>


Per la creazione di tabelle si ricorre al comando create table. Ecco un esempio:


mysql> create table studente(
-> matricola varchar(15),
-> nome varchar(20),
-> cognome varchar(20),
-> nascita date);
Query OK, 0 rows affected (0.46 sec)

mysql>


In SQL ogni istruzione termina con il carattere ;. Il carattere ,, invece, viene usato per separare la descrizione di una colonna da quella successiva. Al comando create table deve seguire il nome da assegnare alla tabella. La descrizione dei campi che compongono la tabella deve avvenire all'interno delle parentesi ( e ). I campi dati sono specificati da un nome e dal tipo che meglio li rappresenta. I vincoli da assegnare ad un campo dati vengono specificati subito dopo il tipo se essi riguardano esclusivamente il campo stesso, oppure su una riga a parte se interessano più campi dati della tabella.

Valori di default per i tipi di dati

In alcuni casi può essere utile assegnare ad un tipo di dato un valore di default da usare qualora il valore, in fase di immissione, non fosse specificato dall'utente. Quando si vuole seguire questa strada occorre allora usare la parola chiave default e far seguire dopo di questa il valore di default, ecco un esempio:

mysql> create table studente(
-> matricola varchar(15),
-> nome varchar(20),
-> cognome varchar(20),
-> nascita date,
-> esami tinyint default 0);
Query OK, 0 rows affected (0.27 sec)

mysql>

Vincoli intrarelazionali

Sono vincoli (o condizioni) che coinvolgono una sola relazione e che devono essere verificati ad ogni istanza della base di dati, ecco un elenco:

• not null, indica che il valore nullo non è ammeso come valore dell'attributo;
• unique, impone che i valori dell'attributo all'interno della tabella siano unici. Pertanto, righe differenti di una tabella non possono avere gli stessi valori per quell'attributo (per il valore null si fa invece un'eccezione);
• primary key, si tratta di un vincolo che è possibile esprimere in tabella una sola volta poichè identifica la chiave primaria per la tabella stessa. Si esprime sulla riga dell'attributo oppure su una riga separata nel caso in cui la chiave primaria è composta da più attributi.

Rivediamo, allora, l'esempio precedente adoperando ora i vincoli intrarelazionali appena visti:


mysql> create table studente(
-> matricola varchar(15),
-> nome varchar(20) not null,
-> cognome varchar(20) not null,
-> nascita date,
-> esami tinyint default 0,
-> primary key(matricola,cognome,nome));
Query OK, 0 rows affected (0.24 sec)

mysql>


All'interno di schemi scritti, la primary key viene identificata dall'attributo sottolineato, ecco un esempio:

studente(matricola, nome, cognome, nascita, esami)

Vincoli interrelazionali

Sono vincoli che coinvolgono più relazioni creando un legame tra i valori di un attributo della tabella corrente (detta tabella interna) e i valori di un attributo di un'altra tabella (detta tabella esterna). Il valore di un attributo alla quale si fa riferimento deve essere almeno unique (molto spesso accade che questo valore è primary key per la tabella esterna e quindi è anche unique, una chiave primaria è infatti anche unica nella tabella!).

mysql> create table studente(
-> nome varchar(20),
-> cognome varchar(20) not null,
-> matricola char(6) primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> create table esame(
-> studente char(6) references studente(matricola),
-> voto smallint not null check((voto>=18) AND (voto<=30)), -> corso varchar(30) primary key);
Query OK, 0 rows affected (0.17 sec)

mysql>

Politiche di reazione

Cosa deve fare il database quando il sistema rileva una violazione di un vincolo? E' possibile assegnare al sistema alcune politiche di reazione non appena si verifica la violazione di un vincolo. La violazione di un vincolo, ad esempio, può avvenire cancellando o aggiornando un vincolo. Di default si assegna la politica no action ad un vincolo, se ciò deve invece essere modificato o impedito si ricorre a istruzioni così strutturate:

on delete|update cascade|set null|set default| no action

dove con:

• cascade, il valore viene riportato su tutte le righe della tabella esterna;
• set null, si assegna il valore null all'attributo referente;
• set default, si assegna il valore di default all'attributo referente;
• no action, l'azione di modifica o cancellazione non viene consentita;

Drop e alter

Questo comando permette di rimuovere componenti da un database. Tale comando in genere può non funzionare immediatamente. Il sistema infatti (la logica data a MySQL) chiede all'utente di svuotare prima la tabella, solo dopo averla svuotata può infatti essere cancellata. Questo discorso si applica anche a un database: prima si svuotano e cancellano le tabelle poi si cancella il databate!


mysql> drop table esame;
Query OK, 0 rows affected (0.00 sec)

mysql>


Nell'esempio il comando riesce proprio perché la tabella esiste ma è vuota! Il comando alter, invece, permette di modificare domini e schemi di tabelle.

DML, data manipulation language

L'informazione, una volta inserita nel database, va cercata e eventualmente elaborata. In questa sezione ci occuperemo dell'interrogazione di un database. Riporto qui di seguito gli esempi visti a lezione, per alcuni di questi faremo riferimento al seguente schema logico:


impiegato(nome, cognome, dipart, ufficio, stipendio, città)
dipartimento(nome, indirizzo, città)


1. Estrarre lo stipendio degli impiegati di cognome "Rossi":


select stipendio
from impiegato
where cognome='Rossi'


2. Estrarre tutte le informazioni relative agli impiegati di cognome "Rossi":


select *
from impiegato
where cognome='Rossi'


3. Estrarre lo stipendio mensile dell'impiegato che ha cognome "Bianchi":


select stipendio/12
from impiegato
where cognome='Bianchi'


Questo perché nella clausola select possono comparire generiche espressioni sul valore degli attributi di ciascuna riga selezionata.

4. Estrarre i nomi degli impiegati e le città in cui lavorano:


select I.nome, I.cognome, D.città
from impiegato as I,dipartimento as D
where I.dipart=D.nome


L'operatore punto identifica alla sua sinistra le tabelle da cui vengono estratti gli attributi (indicati alla destra del punto). Una tabella può essere rinominata con un opportuno alias preceduto dalla parola chiave as (che può anche essere omesso). In questo esempio la clausola select opera sul prodotto cartesiano delle tabelle in essa elencate e separate dal carattere ,. Nella clausola where in tal caso si specifica la condizione che lega le due tabelle.

5. Estrarre il nome e il cognome degli impiegati che lavorano nell'ufficio 20 del dipartimento di amministrazione:


select nome, cognome
from impiegato
where ufficio=20 and dipar='amministrazione'


Nella clausola where sono possibili gli operatori di relazione "=", ">", "<", "<=" e ">=" oltre a quelli logici and, or, e not. L'ordine con cui considerare tali operatori va eventualmente indicato con opportune parentesi se risulta essere diverso da quello previsto di default.

6. Estrarre i nomi e cognomi degli impiegati che lavorano nel dipartimento amministrazione o nel dipartimento produzione:


select nome, cognome
from impiegato
where dipart='amministrazione' or dipart='produzione'


7. Estrarre i nomi propri degli impiegati di cognome "Rossi" che lavorano nei dipartimenti amministrazione e produzione:


select nome
from impiegato
where cognome='Rossi' and (dipart='amministrazione' or dipart='produzione')


8. Estrarre gli impiegati che hanno un cognome che ha una "o" in seconda posizione e finisce per "i":


select *
from impiegato
where cognome like '_o%i'


L'operatore like permette un confronto di stringhe con stringhe in cui compaiono caratteri speciali. Il carattere "_" indica la possibilità di un qualsiasi carattere. Il carattere "%" indica l'occorrenza di una qualsia stringa.

9. Estrarre le città delle persone il cui cognome è "Rossi", presentando eventualmente più volte lo stesso valore di città:


persona(CodFiscale, nome, cognome, città)

select città
from persona
where cognome='Rossi'


10. Estrarre le città delle persone con cognome "Rossi" facendo comparire ogni città al più una sola volta:


persona(CodFiscale, nome, cognome, città)

select distinct città
from persona
where cognome='Rossi'


Per elimmnari i duplicati di un valore di un campo si fa procedere l'attributo dalla parola chiave distinct.

11. Estrarre il contenuto della tabella automobile ordinandolo in base alla marca e al modello:


automobile(targa, marca, modello, nropatente)

select *
from automobile
order by marca desc, modello


L'SQL permette di specificare come ordinare le righe del risultato di una interrogazione tramite la clasuola order by, che precede il nome dell'attributo, e facendo quindi seguire lo stesso attributo dalla parola chiave asc o desc.

12. Estrarre i nomi degli impiegati e le città in cui lavorano:


select I.nome, I.cognome, D.città
from impiegato as I join dipartimento as D on I.dipart=D.nome


Esiste una sintassi alternativa per la specifica dei join che permette di distinguere le condizioni dell'interrogazione da quelle di join. Usando questa sintassi la condizione che lega le tabelle non compare nella clasuola where.

13. Estrarre i guidatori con le automobili loro associate, mantenendo nel risultato anche i guidatori senza automobile:


automobile(targa, marca, modello, nropatente)
guidatore(nome, cognome, nropatente)

select distinct città
from guidatore G left join automobile A on (g.nropatente=A.nropatente)


Esistono diversi tipi di join: inner join, right outer join, left outer join e full outer join. L'outer join effettua un join mantenendo nel risultato tutte le righe di una tabella. Il left join fornisce come risultato il join esteso con le righe della tabella che compare a sinistra. Il right join fornisce come risultato il join esteso con le righe della tabella che compare a destra. Il full join restituisce il join interno esteso con le righe escluse di entrambe le tabelle.

14. Estrarre tutti i guidatori e tutte le auto, mostrando tutte le relazioni esistenti tra di essi:


select nome, cognome, G.nropatente, targa, marca, modello
from guidatore as G full join automobile as A on (G.nropatente=A.nropatente)


15. Estrarre il numero di impiegati del dipartimento produzione:


select count(*)
from impiegato
where dipart='produzione'


count(*) è un operatore in grado di contare il numero di righe che superano il filtro imposto dalla clausola where.

16. Estrarre il numero di diversi valori dell'attributo stipendio fra tutte le righe della tabella impegato:


select count(distinct stipendio)
from impiegato


17. Estrarre gli stipendi minimo, massimo e medio fra quelli di tutti gli impiegati:


select min(stipendio), max(stipendio), avg(stipendio)
from impiegato


18. Estrarre il massimo stipendio tra quelli degli impiegati che lavorano in un dipartimento con sede a Milano:


select max(stipendio)
from impiegato as I, dipartimento as D
where I.dipart=D.nome and città='Milano'


19. Estrarre la somma degli stipendi di tutti gli impiegati dello stesso dipartimento:


select dipart, sum(stipendio)
from impiegato
group by dipart


L'operatore aggregato sum può essere applicato ad un gruppo di righe mediante l'uso della clausola group by. Tale clausola ammette come argomento un insieme di attributi sicché la query raggrupperà le righe che possiedono effettivamente gli stessi valori per quell'attributo.

20. Estrarre i dipartimenti per cui la media degli stipendi degli impiegati che lavorano nell'ufficio 20 è superiore a 25 mila euro:


select dipart
from impiegato
where ufficio='20'
group by dipart
having avge(stipendio) > 25


21. Estrarre i nomi ed i cognomi di tutti gli impiegati, ad eccezione di quelli appartenenti al dipartimento amministrazione e mantenendo i duplicati:


select nome
from impiegato
where dipartimento<>'amministrazione'

union all

select cognome
from impiegato
where dipartimento<>'amministrazione'


22. Estrarre gli impiegati che lavorano in dipartimenti situati a Firenze:


select *
from impiegato
where dipartimento = any (select nome
from dipartimento
where città='Firenze')


Questo esempio ci permette di introdurre le query nidificate. In tal caso, una query interna seleziona il nome del dipartimento (situato a Firenze) mentre quella esterna confronta il risultato dell'interrogazione con l'attributo dipartimento della tabella impiegato convalidando il risultato nel caso in cui esista in essa almeno un valore valido.

Inserimento di valori in una tabella

Il comando SQL per l'inserimento di nuovi valori in tabella è insert. Per l'inserimento dei valori occorre conoscere l'ordine degli attributi appartenenti alla tabella. Un modo per inserire nuovi valori è il seguente:


insert into impiegato('Mario','Rossi','produzione',20,25000,'Firenze')


Per modificare un valore già presente in tabella si adopera invece il comando update:


update impiegato
set stipendio=20000
where nome='Mario' and cognome='Rossi'


Cancellare valori in una tabella

L'eliminazione di un valore all'interno di una tabella va fatto con il comando delete from. Ecco un esempio:


delete from impiegato
where nome='Mario' and cognome='Rossi'


Se l'argomento della clausola where non viene specificato il comando cancellerà di default tutte le righe della tabella indicata. MySQL offre molte altre cose, a tale proposito vi segnalo il sito di riferimento presso cui rimediare il software e la relativa documentaziione: http://www.mysql.it/.