Laboratorio di Sistemi Informativi
SQL e MySQL: comandi di manipolazione dei dati
Questa lezione è dedicata a un breve ripasso dei comandi di manipolazione dati di SQL. Un'altra ottima fonte di informazione, con spiegazioni probabilmente molto più chiare e complete, è il vostro libro di testo di basi di dati, che vi invito a consultare.
Il comando SELECT
Abbiamo visto nelle lezioni precedenti
come usare il comando SELECT
per esaminare il contenuto di una tabella. Il comando
SELECT è probabilmente il comando più complesso
di SQL e
consente di effettuare query di vario tipo.
La sintassi standard di SELECT è la seguente:
SELECT
<lista di colonne>
FROM <lista di tabelle> WHERE <condizione>
Questa operazione esegue il θ-JOIN delle tabelle indicate
FROM
secondo
la
condizione specificata, e visualizza le colonne indicate. Se si mette
l'asterisco * al posto della lista colonna, vuol dire che si intende
visualizzare tutti i campi disponili.
Ad esempio, consideriamo il database creato dallo script esempioselect.sql.
Possiamo vedere quali
sono i valori inseriti nella tabella t1 col comando:
SELECT
* FROM t1;
o possamo decidere di visualizzare soltanto i nomi presenti nella
tabella t1 con
SELECT
nome FROM t1;
ottenendo
+-------+
|
nome |
+-------+
|
pippo |
|
pluto |
|
caio |
|
pluto |
+-------+
Molto più interessante è un esempio che coinvolge
più di una tabella. Ad esempio, per sapere che stipendio
corrisponde a ogni persona della tabella t1, possiamo dare il comando
SELECT
* FROM t1, t2 WHERE id=id2;
+------+-------+------+-----------+
|
id | nome |
id2 | stipendio |
+------+-------+------+-----------+
|
1 | pippo
| 1
| 1000 |
|
3 |
caio | 3
| 3000 |
|
2 | pluto
| 2
| 1900 |
+------+-------+------+-----------+
o, meglio ancora,
SELECT
nome,stipendio
FROM t1, t2 WHERE
id=id2;
+-------+-----------+
|
nome | stipendio |
+-------+-----------+
|
pippo
| 1000 |
|
caio
| 3000 |
|
pluto
| 1900 |
+-------+-----------+
Possiamo dare anche condizioni più complesse, ad esempio
richiedere gli stessi dati di prima ma solo per le persone con
stipendio
superiore a 2000:
SELECT
nome,stipendio FROM t1, t2
WHERE id=id2 and
stipendio
> 2000;
+------+-----------+
|
nome | stipendio |
+------+-----------+
|
caio
| 3000 |
+------+-----------+
Possiamo anche usare delle funzioni
intrinseche oppure operatori
di MySQL all'interno
del comando SELECT. Le funzioni intrinseche sono
simili
alle funzioni dei linguaggi di programmazione tradizionale, come il C o
il Java: prendono un certo numero di argomenti come parametri e
restituiscono un risultato. Ad esempio la funzione intrinseca concat()
prende un numero a
piacere di stringhe come argomenti e restituisce la stringa ottenuta
dalla loro concatenazione (è quindi simile all'operatore +
per
le stringhe di Java, che però non esiste in MySQL, dove + si
può usare solo con numeri).
Ad esempio
SELECT
concat(nome,' guadagna
',stipendio) FROM
t1, t2 WHERE id=id2
usa la funzione intrinseca concat
mentre
SELECT nome,stipendio FROM t1, t2 WHERE id=id2 and stipendio *
1.2 > 2000
usa l'operatore *.
Vedremo in una lezione successiva alcune delle funzioni che
MySQL mette a disposizione.
È anche possibile fare delle query con
più di una
tabella di
base. Ad esempio, la tabella t3 contiene gli indirizzi delle persone
nella tabella t1. Se vogliamo sapere nome, stipendio e indirizzo per
ogni persona, il comando da dare è:
SELECT
nome,stipendio,indirizzo FROM t1, t2, t3 WHERE id=t2.id2
and id=t3.id2;
Notare l'uso della forma t2.id2
e t3.id2
per identificare
i
campi id2 delle tabelle t2 e t3. Poichè i campi hanno le
stesso
nome, è necessario indicare anche a quale tabella ci si
riferisce.
In realtà il comando SELECT ha varie altre
possibilità
che non abbiamo visto. Esaminiamone qualcuna:
- la clausola AS nella lista
dei campi... ad esempio
SELECT concat(nome,' guadagna
',stipendio) as risultato
FROM t1, t2 WHERE
id=id2;
+---------------------+
|
risultato
|
+---------------------+
|
pippo guadagna 1000 |
|
caio guadagna 3000 |
|
pluto guadagna 1900 |
+---------------------+
È più leggibile delle versione senza as.
- la
clausola AS nella
lista delle tabelle... ad esempio
SELECT
nome,stipendio,indirizzo FROM t1 as
nomi, t2 as
stipendi, t3 as
indirizzi WHERE
id=stipendi.id2 and id=indirizzi.id2;
Viene
usata per dare degli alias comprensibili ai
nomi delle tabelle t1, t2 e t3. In realtà spesso
accade il
contrario: le tabelle hanno nomi descrittivi ma lunghi, e si usa il
comando as per poter utilizzare degli alias più corti, e
quindi
scrivere meno, all'interno di una query.
- la clausola ORDER BY... ad
esempio
SELECT
nome,stipendio FROM
t1, t2 WHERE id=id2 ORDER BY nome;
+-------+-----------+
|
nome | stipendio |
+-------+-----------+
|
caio
| 3000 |
|
pippo
| 1000 |
|
pluto
| 1900 |
+-------+-----------+
oppure in ordine
discendente
SELECT nome,stipendio
FROM t1, t2 WHERE id=id2 ORDER BY nome DESC;
+-------+-----------+
|
nome | stipendio |
+-------+-----------+
|
pluto
| 1900 |
|
pippo
| 1000 |
|
caio
| 3000 |
+-------+-----------+
- la
clausola DISTINCT
SELECT
DISTINCT
nome FROM t1;
+-------+
|
nome |
+-------+
|
pippo |
|
pluto |
|
caio |
+-------+
Join esterni
Consideriamo ancora il database di esempio creato dallo script esempioselect.sql.
Consta di varie
tabelle, in particolare ci interessano la t1 e la t2:
Tabella
t1
+------+-------+
|
id | nome |
+------+-------+
|
1 | pippo |
|
2 | pluto |
|
3 |
caio |
|
4 | pluto |
+------+-------+
Tabella
t2
+------+-----------+
|
id2 | stipendio |
+------+-----------+
|
1
| 1000 |
|
3
| 3000 |
|
2
| 1900 |
|
7
|
10 |
+------+-----------+
Supponiamo di voler sapere, per ogni persona, il relativo stipendio.
Una possibilità è il comando:
SELECT
id, nome, stipendio FROM
t1,t2 where id=id2;
+------+-------+-----------+
|
id | nome |
stipendio |
+------+-------+-----------+
|
1 | pippo
| 1000 |
|
3 |
caio | 3000
|
|
2 | pluto
| 1900 |
+------+-------+-----------+
La query non restituisce nulla riguardo allo stipendio della persona
con id 4 (che è un omonimo della persona di id 2). Questo
perchè nella tabella t2 non c'è alcuna riga con
id pari a
4. Se volessimo avere comunque informazioni su tutte le persone, anche
quelle senza stipendio, dovremmo utilizzare i join esterni:
SELECT
id, nome, stipendio FROM t1 LEFT
JOIN t2 ON
id=id2;
+------+-------+-----------+
|
id | nome |
stipendio |
+------+-------+-----------+
|
1 | pippo
| 1000 |
|
2 | pluto
| 1900 |
|
3 |
caio | 3000
|
|
4 | pluto
| NULL |
+------+-------+-----------+
Il campio stipendio relativo all'id4 è riempito con NULL.
Analogamente abbiamo anche il RIGHT JOIN:
SELECT
id, nome, stipendio FROM t1 RIGHT
JOIN t2 ON
id=id2;
+------+-------+-----------+
|
id | nome |
stipendio |
+------+-------+-----------+
|
1 | pippo
| 1000 |
|
3 |
caio | 3000
|
|
2 | pluto
| 1900 |
|
NULL | NULL
|
10 |
+------+-------+-----------+
MySQL non dispone del FULL JOIN, ovvero della possibilità
di combinare il LEFT e il RIGHT join, in modo da considerare sia le
righe della prima tabella senza valori corrispondenti della seconda,
sia il viceversa. Se esistesse, il FULL JOIN si comporterebbe in
questo modo:
SELECT
id, nome, stipendio FROM t1 FULL JOIN t2
ON id=id2;
+------+-------+-----------+
|
id | nome |
stipendio |
+------+-------+-----------+
|
1 | pippo
| 1000 |
|
3 |
caio | 3000
|
|
2 | pluto
| 1900 |
|
NULL | NULL
|
10 |
| 4 | pluto | NULL
|
+------+-------+-----------+
Attenzione.
Dopo la clausola ON
vanno indicate soltanto
le
condizioni relative al join. Eventuali altre
condizioni vanno di solito indicate, come prima, nella clausola WHERE.
Ad esempio:
SELECT id, nome, stipendio FROM t1 LEFT JOIN t2 ON id=id2 WHERE
stipendio>1000;
+------+-------+-----------+
| id | nome | stipendio |
+------+-------+-----------+
| 3 | caio
| 3000
|
| 2 | pluto
| 1900 |
+------+-------+-----------+
2 rows in set (0.00 sec)
mentre
SELECT
id, nome, stipendio FROM
t1 LEFT JOIN t2 ON id=id2 and stipendio>1600;
+------+-------+-----------+
|
id | nome |
stipendio |
+------+-------+-----------+
|
1 | pippo
| NULL |
|
2 | pluto
| 1900 |
|
3 |
caio | 3000
|
|
4 | pluto
| NULL |
+------+-------+-----------+
4
rows in set (0.00 sec)
Nella maggioranza dei casi, quello che vogliamo è il primo
risultato e non il secondo.
Union
Un'altrà possibilità del comando SELECT è quella
di eseguire due selezioni distinte e di fondere i risultati come se
fossero stati ottenuti da un comando solo. Tutto ciò si
realizza
con la clausola UNION. Ad esempio:
select
* from t1 UNION
select * from t2;
visualizza il contenuto della prima e della seconda tabella una dopo
l'altro.
Notare che elementi duplicati vengono eliminati.. Questo ci
consente di simulare il FULL JOIN:
SELECT id, nome, stipendio FROM t1 LEFT JOIN t2 on id=id2
UNION
SELECT id, nome,
stipendio FROM t1 RIGHT JOIN t2 on id=id2;
È possibile anche
effettuare una union che non elimini i
duplicati, con la clausola UNION
ALL:
SELECT id, nome, stipendio FROM t1 LEFT JOIN t2 on id=id2
UNION ALL SELECT id, nome,
stipendio FROM t1 RIGHT JOIN t2 on id=id2;
+------+-------+-----------+
|
id | nome |
stipendio |
+------+-------+-----------+
|
1 | pippo
| 1000 |
|
3 |
caio | 3000
|
|
2 | pluto
| 1900 |
|
NULL | NULL
|
10 |
| 4 | pluto | NULL
|
|
1 | pippo
| 1000 |
|
2 | pluto
| 1900 |
|
3 |
caio | 3000
|
|
4 | pluto
| NULL |
+------+-------+-----------+
MySQL non implementa EXCEPT ed INTERSECT, due altri operatori insiemistici fanno invece parte dello standard SQL. Questi si possono comunque simulare con comandi SQL più complessi.
La clausola GROUP BY
Altra caratteristica del comando SELECT,
è la
capacità di raggruppare le informazioni in base al valore
assunto
da uno o più campi e di fornire informazioni statistiche su
questi gruppi. Utilizzeremo a questo scopo la tabella t4:
+----------+--------+-----------+
|
nome |
valore | categoria |
+----------+--------+-----------+
|
giuseppe |
100 |
a
|
|
marco
| 200 |
b
|
|
luca
| NULL |
b
|
|
luca
| 100 |
b
|
|
NULL
| NULL |
b
|
|
carlo
| 1000 |
a
|
+----------+--------+-----------+
Supponiamo di dare il comando
SELECT
* FROM t4 GROUP BY
categoria;
+----------+--------+-----------+
|
nome |
valore | categoria |
+----------+--------+-----------+
|
giuseppe |
100 |
a
|
|
marco
| 200 |
b
|
+----------+--------+-----------+
Con
l'aggiunta di GROUP BY,
per ogni valore di
categoria viene visualizzata una e una sola riga corrispondente (la
prima). Questa cosa ha di per se poca utilità, ma diventa
molto
utile quando abbinata alle funzioni aggregate, che consentono di
effettuare varie operazioni statistiche.
Ad esempio, se si vuole conoscere il numero di persone appartenente ad
ogni categoria, possiamo dare il comando:
SELECT
categoria, COUNT(*)
FROM t4 GROUP BY
categoria;
+-----------+----------+
|
categoria | COUNT(*) |
+-----------+----------+
|
a
|
2 |
|
b
|
4 |
+-----------+----------+
La clausola GROUP BY
divide tutte le informazioni in gruppi, ogni gruppo caratterizzato dal
fatto di avere la stessa categoria mentre COUNT(*)
conta il numero di
elementi in ogni gruppo.
Notare che nel conteggio viene anche inserita la riga
(NULL,NULL,'b'). Se si vuole essere più precisi su
cosa
contare, possiamo indicare tra parentesi a COUNT un nome di un campo. A
questo punto, le linee in cui il campo indicato contiene il valore null
verranno escluse dal conteggio. Ad esempio:
SELECT categoria, COUNT(nome)
FROM t4 GROUP BY categoria;
+-----------+-------------+
|
categoria | COUNT(nome) |
+-----------+-------------+
|
a
|
2 |
|
b
|
3 |
+-----------+-------------+
Infine è possibile indicare che occorre contare i valori
senza
ripetizioni. Ad esempio
SELECT
categoria, COUNT(DISTINCT nome)
FROM
t4 GROUP BY categoria;
+-----------+---------------+
|
categoria | COUNT(valore) |
+-----------+---------------+
|
a
|
2 |
|
b
|
2 |
+-----------+---------------+
Esitono altre funzioni
aggregate. Ad esempio la somma (SUM) e la
media (AVG):
SELECT
categoria, SUM(valore)
FROM t4 GROUP
BY categoria;
+-----------+-------------+
|
categoria | SUM(valore) |
+-----------+-------------+
|
a
|
1100 |
|
b
|
300 |
+-----------+-------------+
SELECT
categoria, AVG(valore)
FROM t4 GROUP
BY categoria;
+-----------+-------------+
|
categoria | AVG(valore) |
+-----------+-------------+
|
a
|
550.0000 |
|
b
|
150.0000 |
+-----------+-------------+
Altre funzioni aggregate sono il massimo MAX(), il minimo MIN(), e la
deviazione standard STD().
La clausola HAVING
Le condizioni che si possono specificare con la clausola WHERE sono
anche molto complesse, ma non possono riferirsi ai risultati delle
funzioni aggregate. Ovvero, non è possibile sapere l'insieme
delle categorie, nella tabella t4, che hanno un valore medio superiore
a
200. Un comando del tipo:
select
categoria,avg(valore) from
t4 where avg(valore)>400 group by categoria;
genera errore. Tuttavia, condizioni di questo tipo possono essere
espresse con la clausola HAVING.
select
categoria, avg(valore)
from t4 group by categoria HAVING
AVG(valore)>400;
+-----------+-------------+
|
categoria | avg(valore) |
+-----------+-------------+
|
a
|
550.0000 |
+-----------+-------------+
Notare che con HAVING
si
possono specificare solo condizioni che coinvolgono i campi presenti
nella clausola GROUP BY
o
il risultato di funzioni aggregate. Condizioni di altro tipo DEVONO
essere specificate nella
clausola WHERE.
Ad
esempio:
SELECT
categoria, SUM(VALORE)
FROM t4 WHERE
valore>100
GROUP
BY categoria;
+-----------+-------------+
|
categoria | SUM(VALORE) |
+-----------+-------------+
|
a
|
1100 |
|
b
|
300 |
+-----------+-------------+
mentre
SELECT
categoria, SUM(VALORE)
FROM t4 GROUP BY categoria HAVING
valore>100;
genera un errore di sintassi.
Nota.
Nell'esecuzione di un
comando SELECT,
il
linguaggio SQL prima considera la clasuole FROM
e WHERE,
calcola il risultato, e
solo successivamente considera le eventuali clausole GROUP BY
e HAVING.
Per questo motivo,
nella clausola WHERE
vanno le condizioni da applicare per filtrare l'insieme di prima di
effettuare il raggruppamento, e nella clausola HAVING
quelle per filtrare i
risultati dopo aver raggruppato.
Subqueries
Infine, un comando SELECT può anche apparire come
sotto-interrogazione di un altro comando SELECT principale. In
particolare, una SELECT può apparire
- nella clasuola FROM di
un'altra interrogazione, come in
SELECT AVG(s) FROM (SELECT
SUM(valore) AS s FROM t4 GROUP BY categoria)
AS t4;
che visualizza la media della somma degli stipendi degli impiegati di
categoria A e di categoria B;
- all'interno della condizione
WHERE, come in
SELECT * FROM t1 WHERE
id NOT IN (SELECT
id2 FROM t2);
che visualizza gli impiegati in t1 che non hanno uno stipendio
specificato in t2.
Non approfondiamo di più il discorso, perché le
possibilità offerte dalle subqueries sono tantissime e
non abbiamo tempo per analizzarle (anche tenendo conto che le avete
viste nel corso di Basi di Dati). Puntualizziamo solo due cose:
- molte interrogazioni con subqueries possono essere trasformate in
interrogazioni normali usando opportunamente i join esterni o delle
tabelle temporanee. Ad esempio, la seconda interrogazione di cui sopra,
si può riscrivere come
SELECT id, nome FROM t1 LEFT JOIN t2 ON id=id2 WHERE id2 IS
NULL
- il supporto per le subqueries è stato inserito in MySQL
solo con la versione 4.1.
I comandi INSERT, UPDATE e DELETE
Vediamo ora brevemente alcuni comandi per l'aggiornamento della base di dati. Come riferimento per gli esempi si usano sempre le tabelle create dallo script esempioselect.sql.
- INSERT INTO <nometabella>(<nomecampi>) VALUES (<lista valori>): per l'inserimento di una riga di dati nella tabella
<nometabella>.
- INSERT
INTO
<nometabella>(<nomecampi>) SELECT
<comando select>: esegue il comando SELECT e mette il
risultato dentro
<nometabella>.
Ad esempio:
CREATE
TABLE t5 (nome char(20));
INSERT
INTO t5 SELECT nome FROM
t4;
crea e riempie una tabella con i nomi delle persone in t4.
- UPDATE
<nometabella> SET
<nomecolonna>=<dato> WHERE
<condizione>:
aggiorna il valore del campo <nomecolonna> con il valore
<dato>, per tutte le righe che soddisfano la condizione.
Il dato
può anche contenere espressioni aritmetiche.
Così, per
aumentare le colonne valore del 20% nella tabella t4 per tutti i membri
della categoria 'b', si può dare il comando:
UPDATE
t4 SET
valore=1.2*valore WHERE categoria='b';
- DELETE FROM
<nometabella> WHERE <condizione>:
cancella tutte le
righe in <nometabella> che soddisfano la condizione. Ad
esempio:
DELETE
FROM t4 WHERE
categoria='a';
Se non si specifica alcuna condizione, vengono cancellate tutte le righe della tabella.
Differenze tra lo standard SQL e MySQL
Ecco brevemente le differenze principali tra lo standard SQL e l'implementazione di SQL presente in MySQL.
- non è supportato il
FULL JOIN (sono invece supportati i join sinistri, destri e le varianti naturali).
- non sono supportati gli operatori insiemistici
intersect ed except (è invece supportato union).
- nel comando
INSERT INTO <nometabella> VALUES è possibile specificare più di una tupla di valori usando la virgola come separatore. Ad esempio:
INSERT INTO t3 VALUES (4,'pescara'), (5,'chieti');
L'uso di questa variante di INSERT consente una maggiore velocità di esecuzione, e nel contempo ci fa risparmiare sul numero di caratteri da digitare sulla tastiera. Tuttavia, poché si tratta di una estensione non standard, si consiglia di utilizzarla solo nell'uso interattivo di MySQL, e non all'interno di programmi che potrebbero essere portati un giorno su un un'altra piattaforma.
Esercizio 1
Si consideri la seguente interrogazione SQL:
SELECT AVG(s) FROM (SELECT
SUM(valore) AS s FROM t4 GROUP BY categoria)
AS t4;
Provare a risolvere questa interrogazione senza usare le subquery (SELECT dentro le SELECT), ma adoperandoadoperando una tabella temporanea per immagazzinare il risultato di SELECT
SUM(valore) AS s FROM t4 GROUP BY categoria.
