Manipolazione dati

Sommario

Comandi DML di SQL

Il linguaggio per la manipolazione dei dati è il DML (Data Manipulation Language). Esso consente le quattro operazioni fondamentali:

CREATE: inserimento di nuovi dati
READ: lettura dei dati esistenti
UPDATE: modifica
DELETE: cancellazione

Ovvero le operazioni CRUD. In SQL questi comandi sono rispettivamente:

INSERT: consente di inserire i dati in una tabella
SELECT: consente di interrogare il database, da una o, come vedremo, più tabelle tramite le loro relazioni
UPDATE: consente di modificare una riga esistente
DELETE: consente di cancellare una riga esistente

INSERT e SELECT

Per inserire una nuova riga in una tabella usiamo INSERT:

INSERT INTO AUTORE (Nome, Cognome) VALUES ('Michael', 'Jackson')

La struttura è

INSERT INTO Tabella (Attributo1, Attributo2, ...)
VALUES (valore1, valore2, ...)

Note:

La lista dei valori deve essere coerente con la lista degli attributi;
non è obbligatorio usare tutti gli attributi;
se non viene inserito il valore per un attributo, il sistema verifica se c’è la constraint NOT NULL e da errore in caso affermativo;
se non viene inserito il valore per un attributo, il sistema, se esiste il default, lo inserisce;
l’id se intero autoincrementale non va inserito, lo inserisce il sistema automaticamente.

E’ anche possibile inserire valori multipli:

INSERT INTO AUTORE (Nome, Cognome) VALUES 
('Queen', ''),
('Michael Jackson', ''),
('Pink Floyd', '');

Verifichiamo ora se i valori sono stati inseriti, usando l’istruzione SELECT:

mysql> SELECT Id, Nome, Cognome FROM AUTORE;
+----+------------+---------+
| Id | Nome       | Cognome |
+----+------------+---------+
|  1 | Queen      |         |
|  2 | Michael    | Jackson |
|  3 | Pink Floyd |         |
+----+------------+---------+

La SELECT ha questa struttura:

SELECT Attributo1, Attributo2, ... FROM Tabella

E’ importante capire che la tabella è un INSIEME, NON è quindi ordinata, infatti l’ordine presentato è solo quello di inserimento. Allo stesso modo le tuple sono un insieme, e non sono ordinate: l’ordine è quello degli attributi della query.

E’ possibile eseguire una query su tutti i campi:

SELECT * FROM AUTORE;

Questo tipo di query è però ambigua, perché senza conoscere con certezza l’ordine degli attributi, possono essere presentati in modo non prevedibile. Le query con il jolly (*) possono andare bene per ricerche veloci eseguite a mano, ma non vanno mai usate per avere risultati puntuali, e soprattutto mai usate quando si creano query SQL per l’utilizzo da parte di sistemi automatici.

Inseriamo ora il resto dei dati. Partiamo dalle tabelle indipendenti (ripartiamo da zero):

INSERT INTO AUTORE (Nome, Cognome) VALUES 
('Queen', ''),
('Michael', 'Jackson'),
('Pink Floyd', '');

INSERT INTO CASA_DISCOGRAFICA (Nome) VALUES 
('EMI Records'),
('Epic Records');

INSERT INTO GENERE (Descrizione) VALUES 
('Rock'),
('Pop'),
('Progressive Rock');

INSERT INTO SUPPORTO (Descrizione) VALUES 
('Vinile'),
('CD'),
('Cassetta');

Ipotizziamo che gli Id inseriti siano autoincrementali e partono da 1, quindi gli inserimenti nell’ordine dato avranno sempre indice 1,2,3,4… e così via. Quando andiamo ad inserire le tabelle dipendenti, dobbiamo inserire le foreign key, ed useremo questa ipotesi:

INSERT INTO CONTRATTO (Durata, AutoreId, CasaDiscograficaId) VALUES 
(5, 1, 1), -- Queen con EMI
(7, 2, 2), -- Michael Jackson con Epic
(6, 3, 1); -- Pink Floyd con EMI

INSERT INTO ALBUM (Titolo, Anno, CasaDiscograficaId, AutoreId) VALUES 
('A Night at the Opera', 1975, 1, 1),
('News of the World', 1977, 1, 1),
('Thriller', 1982, 2, 2),
('Bad', 1987, 2, 2),
('The Dark Side of the Moon', 1973, 1, 3);

INSERT INTO CANZONE (Titolo, AlbumId) VALUES 
('Death on two Legs', 1),
('Lazing on a Sunday Afternoon', 1),
('You''re My Best Friend', 1),
('Love of My Life', 1),
('Bohemian Rhapsody', 1),

('We Will Rock You', 2),
('We Are the Champions', 2),
('Sheer Heart Attack', 2),
('All Dead, All Dead', 2),
('Spread Your Wings', 2),

('Wanna Be Startin'' Somethin''', 3),
('Thriller', 3),
('Beat It', 3),
('Billie Jean', 3),
('Human Nature', 3),

('Bad', 4),
('The Way You Make Me Feel', 4),
('Speed Demon', 4),
('Liberian Girl', 4),
('Smooth Criminal', 4),

('Speak to Me', 5),
('Breathe', 5),
('Time', 5),
('Money', 5),
('Us and Them', 5),
('Brain Damage', 5);

INSERT INTO BOOKLET (Pagine, AlbumId) VALUES 
(24, 1), 
(32, 3); 

INSERT INTO ALBUM_GENERE (AlbumId, GenereId) VALUES 
(1, 1), 
(2, 1), 
(3, 2), 
(4, 2), 
(5, 3); 

INSERT INTO ALBUM_SUPPORTO (AlbumId, SupportoId) VALUES 
(1, 1), (1, 2),         
(2, 1), (2, 3),         
(3, 1), (3, 2), (3, 3), 
(4, 2), (4, 3),         
(5, 1), (5, 2);

Prestiamo attenzione alla stringa 'Wanna Be Startin'' Somethin'''. Siccome l’album contiene un apostrofo, per inserirlo nella stringa usiamo il doppio apice.

SELECT

La SELECT è l’istruzione che permette di estrarre dati da una database. Questa istruzione ha questa struttura di base:

SELECT Attributi
FROM Tabella

Ad esempio:

mysql> SELECT Titolo, ANNO
    -> FROM ALBUM;
+---------------------------+------+
| Titolo                    | ANNO |
+---------------------------+------+
| A Night at the Opera      | 1975 |
| News of the World         | 1977 |
| Thriller                  | 1982 |
| Bad                       | 1987 |
| The Dark Side of the Moon | 1973 |
+---------------------------+------+

Gli attributi dopo la SELECT indicano quindi quali colonne si vogliono visualizzare. Il filtro sulle colonne si chiama proiezione.

WHERE

Se invece vogliamo selezionare solo alcune righe, bisogna porre una condizione:

mysql> SELECT Titolo, Anno
    -> FROM ALBUM
    -> WHERE Anno = 1975;
+----------------------+------+
| Titolo               | Anno |
+----------------------+------+
| A Night at the Opera | 1975 |
+----------------------+------+

Le condizioni della clausola WHERE possono essere su qualsiasi colonna, possono essere combinate con AND, OR e NOT, possono essere usate le parentesi come nei normali linguaggi di programmazione.
Le condizioni dipendono dal tipo di dato. Qui le più comuni:

Tipo di dato	Condizione	Significato
Numerico (INT, BIGINT, DECIMAL, FLOAT, DOUBLE, ecc.) e Date (DATE, TIME, DATETIME)	`=, >, < != >= <=`	Condizioni numeriche, es. Anno > 1975
Numerico (INT, BIGINT, DECIMAL, FLOAT, DOUBLE, ecc.)	`BETWEEN`	Indica che l’attributo deve essere compreso tra due valori. Es. Anno BETWEEN 1975 AND 1979
Stringa	`= "..."`	Uguaglianza su stringa es. Titolo = ‘Bad’
Stringa	`LIKE 'START%' LIKE '%END' LIKE '%MIDDLE%'`	Queste tre condizioni indicano rispettivamente, che il valore deve cominciare, deve finire o deve essere contenuto. Es. Titolo LIKE ‘%of%’ cercherà tutte le righe con Titolo che contiene la stringa ‘of’.

Qui alcuni esempi:

mysql> SELECT Titolo, Anno
    -> FROM ALBUM
    -> WHERE Anno > 1975;
+-------------------+------+
| Titolo            | Anno |
+-------------------+------+
| News of the World | 1977 |
| Thriller          | 1982 |
| Bad               | 1987 |
+-------------------+------+

mysql> SELECT Titolo, Anno 
    -> FROM ALBUM 
    -> WHERE Anno BETWEEN 1975 AND 1982;
+----------------------+------+
| Titolo               | Anno |
+----------------------+------+
| A Night at the Opera | 1975 |
| News of the World    | 1977 |
| Thriller             | 1982 |
+----------------------+------+

mysql> SELECT Titolo, Anno
    -> FROM ALBUM
    -> WHERE Titolo LIKE '%of%';
+---------------------------+------+
| Titolo                    | Anno |
+---------------------------+------+
| News of the World         | 1977 |
| The Dark Side of the Moon | 1973 |
+---------------------------+------+

mysql> SELECT Titolo, Anno 
    -> FROM ALBUM
    -> WHERE Titolo = 'News of the World';
+-------------------+------+
| Titolo            | Anno |
+-------------------+------+
| News of the World | 1977 |
+-------------------+------+

In sintesi il costrutto SELECT…FROM…WHERE è il costrutto base di qualsiasi query di ricerca:

la SELECT filtra per le colonne (esegue una proiezione)
la FROM indica l’oggetto della ricerca (la tabella, e come vedremo tra poco, le tabelle)
la WHERE filtra per le colonne

La programmazione SQL ricorda molto la programmazione funzionale in quanto:

ogni istruzione esegue una porzione dell’elaborazione;
l’istruzione agisce sull’intero insieme, non sono previsti FOR e IF;
sono entrambe stateless, non memorizzano nulla, eseguono una operazione di calcolo;

Tuttavia l’ordine è meno “immediato”: prima si esegue il filtro sulle colonne, poi si indica la fonte dati, infine si filtra sulle righe; in programmazione funzionale l’ordine è più “naturale”, dai dati, ai filtri, alla selezione. In questo senso SQL mostra la sua età.

JOIN

Arrivati a questo punto ci appare chiaro che manca qualcosa. Un database non è un insieme di tabelle, ma una struttura unitaria dove i dati sono distribuiti su più entità. Introduciamo quindi il concetto di JOIN, ovvero una istruzione parte della SELECT che ci consente di collegare due o più tabelle.

Join implicita

La JOIN più semplice è quella implicita (senza cioè l’uso di questa keyword):

mysql> SELECT * 
    -> FROM ALBUM, AUTORE;
+----+---------------------------+------+--------------------+----------+----+------------+---------+
| Id | Titolo                    | Anno | CasaDiscograficaId | AutoreId | Id | Nome       | Cognome |
+----+---------------------------+------+--------------------+----------+----+------------+---------+
|  1 | A Night at the Opera      | 1975 |                  1 |        1 |  3 | Pink Floyd |         |
|  1 | A Night at the Opera      | 1975 |                  1 |        1 |  2 | Michael    | Jackson |
|  1 | A Night at the Opera      | 1975 |                  1 |        1 |  1 | Queen      |         |
|  2 | News of the World         | 1977 |                  1 |        1 |  3 | Pink Floyd |         |
|  2 | News of the World         | 1977 |                  1 |        1 |  2 | Michael    | Jackson |
|  2 | News of the World         | 1977 |                  1 |        1 |  1 | Queen      |         |
|  3 | Thriller                  | 1982 |                  2 |        2 |  3 | Pink Floyd |         |
|  3 | Thriller                  | 1982 |                  2 |        2 |  2 | Michael    | Jackson |
|  3 | Thriller                  | 1982 |                  2 |        2 |  1 | Queen      |         |
|  4 | Bad                       | 1987 |                  2 |        2 |  3 | Pink Floyd |         |
|  4 | Bad                       | 1987 |                  2 |        2 |  2 | Michael    | Jackson |
|  4 | Bad                       | 1987 |                  2 |        2 |  1 | Queen      |         |
|  5 | The Dark Side of the Moon | 1973 |                  1 |        3 |  3 | Pink Floyd |         |
|  5 | The Dark Side of the Moon | 1973 |                  1 |        3 |  2 | Michael    | Jackson |
|  5 | The Dark Side of the Moon | 1973 |                  1 |        3 |  1 | Queen      |         |
+----+---------------------------+------+--------------------+----------+----+------------+---------+

Quello che abbiamo ottenuto è un prodotto cartesiano delle due tabelle. Un prodotto cartesiano tra due tabelle M e N è una tabella K dove per ogni riga di M viene associata ad ogni riga di N, ovvero tutte le combinazioni possibili, quindi se M ha m righe, ed N ha n righe, otterremo m*n righe.

Possiamo però utilizzare la clausola WHERE per imporre esplicitamente il vincolo di integrità referenziale, cioè selezionare solo le righe dove AUTORE.Id corrisponde ad ALBUM.AutoreId. Inoltre, visto che ci siamo, estraiamo solo i campi contenenti informazione: Nome, Cognome, Titolo, Anno.

mysql> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE, ALBUM
    -> WHERE AUTORE.Id = ALBUM.AutoreId;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Queen      |         | A Night at the Opera      | 1975 |
| Queen      |         | News of the World         | 1977 |
| Michael    | Jackson | Thriller                  | 1982 |
| Michael    | Jackson | Bad                       | 1987 |
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
+------------+---------+---------------------------+------+

Adesso la join ha un senso. Notare che per confrontare due attributi di due tabelle diverse si usa l’operatore ., una notazione che abbiamo già visto nei linguaggi ad alto livello (come C, Java o Javascript).

Join Esplicita

Questa sintassi, sebbene valida, è implicita e può portare a confusione nelle condizioni espresse dalla WHERE, perché mettiamo insieme condizioni di filtro legate all’integrità referenziale tra due tabelle (cioè di natura tecnica), e condizioni di filtro sui dati veri e propri (cioè di natura informativa).

Molto meglio la forma esplicita:

mysql> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Queen      |         | A Night at the Opera      | 1975 |
| Queen      |         | News of the World         | 1977 |
| Michael    | Jackson | Thriller                  | 1982 |
| Michael    | Jackson | Bad                       | 1987 |
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
+------------+---------+---------------------------+------+

Se poi vogliamo filtrare sui dati possiamo aggiungere la WHERE.

Da un punto di vista logico, la query prima esegue le JOIN e poi esegue le WHERE. Pertanto il JOIN esplicito consente prima di filtrare le righe e solo dopo eseguire la WHERE sulle righe filtrate. Pertanto in questo senso consente una migliore efficienza rispetto alla JOIN implicita.

Di fatto però negli RDMBS moderni internamente le query sono ottimizzate ed una JOIN implicita viene tradotta automaticamente in una esplicita, senza differenze prestazionali. Resta comunque la problematica concettuale, che fa preferire le JOIN esplicite a quelle implicite.

INNER JOIN e OUTER JOIN

Questo JOIN include solo righe che hanno una corrispondenza in entrambe le tabelle, ovvero tutti gli autori che hanno un album, E tutti gli album che hanno un autore. Rendiamo le cose un po’ più realistiche con questi due inserimenti:

mysql> INSERT INTO AUTORE(Nome, Cognome) VALUES ('Vasco', 'Rossi');
mysql> INSERT INTO ALBUM (Titolo, Anno, CasaDiscograficaId) VALUES ('Master of Puppets', 1987, 1);

Abbiamo inserito un autore senza alcun album ed un album senza autore.

Se ora rieseguiamo la query precedente, avremo sempre lo stesso risultato: il sistema “ignora” righe non collegate, perché abbiamo imposto come vincolo. Questo perché JOIN in realtà è un INNER JOIN, cioè è un JOIN che esclude automaticamente le righe senza corrispondenza tra le due tabelle.

Per vedere i risultati occorre quindi usare un OUTER JOIN, che opera in questo modo:

se scriviamo LEFT OUTER JOIN, includiamo tutte le righe della prima tabella (a sinistra), e solo le righe della seconda tabella (a destra) che hanno una corrispondenza sulla regola ON;

mysql> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE LEFT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Queen      |         | A Night at the Opera      | 1975 |
| Queen      |         | News of the World         | 1977 |
| Michael    | Jackson | Thriller                  | 1982 |
| Michael    | Jackson | Bad                       | 1987 |
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
| Vasco      | Rossi   | NULL                      | NULL |
+------------+---------+---------------------------+------+

Come si vede, dove non c’è corrispondenza, ci viene restituito NULL.

se scriviamo RIGHT OUTER JOIN, operiamo in modo simmetricamente opposto (escludiamo le righe della prima tabella

mysql> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE RIGHT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Queen      |         | A Night at the Opera      | 1975 |
| Queen      |         | News of the World         | 1977 |
| Michael    | Jackson | Thriller                  | 1982 |
| Michael    | Jackson | Bad                       | 1987 |
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
| NULL       | NULL    | Master of Puppets         | 1987 |
+------------+---------+---------------------------+------+

Possiamo fare infine un FULL OUTER JOIN, dove cioè vogliamo avere sia le righe associate, che quelle non associate delle due tabelle. In questo caso Mysql (altri RDBMS lo permettono) richiede di utilizzare un connettore UNION, che unisce i due risultati delle tabelle in un unico insieme.

mysql> SELECT Nome, Cognome, Titolo, Anno FROM AUTORE LEFT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 
    -> UNION
    -> SELECT Nome, Cognome, Titolo, Anno FROM AUTORE RIGHT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Queen      |         | A Night at the Opera      | 1975 |
| Queen      |         | News of the World         | 1977 |
| Michael    | Jackson | Thriller                  | 1982 |
| Michael    | Jackson | Bad                       | 1987 |
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
| Vasco      | Rossi   | NULL                      | NULL |
| NULL       | NULL    | Master of Puppets         | 1987 |
+------------+---------+---------------------------+------+

Vedremo gli operatori insiemistici più avanti.

Join multipli e ALIAS

E’ possibile fare JOIN multipli, collegando insieme tre o più tabelle per ottenere informazioni più ricche. Vediamo un esempio:

mysql> SELECT Nome, Cognome, Titolo, Anno, Pagine
    -> FROM AUTORE 
    -> JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 
    -> JOIN BOOKLET ON ALBUM.Id = BOOKLET.AlbumId;
+---------+---------+----------------------+------+--------+
| Nome    | Cognome | Titolo               | Anno | Pagine |
+---------+---------+----------------------+------+--------+
| Queen   |         | A Night at the Opera | 1975 |     24 |
| Michael | Jackson | Thriller             | 1982 |     32 |
+---------+---------+----------------------+------+--------+

Operazioni di calcolo

La SELECT offre molte operazioni di calcolo che consentono sia di formattare i dati in modo agevole, sia di eseguire condizioni su valori calcolati.

Alias

Poniamo di voler avere il nome della casa discografica associata ad ogni autore:

mysql> SELECT AUTORE.Nome, AUTORE.Cognome, ETICHETTA.Nome AS Etichetta 
 FROM CONTRATTO JOIN AUTORE ON AUTORE.Id = CONTRATTO 
 JOIN CASA_DISCOGRAFICA ETICHETTA ON ETICHETTA.Id = CONTRATTO.CasaDiscograficaId;
                                                                            
+------------+---------+--------------+
| Nome       | Cognome | Etichetta    |
+------------+---------+--------------+
| Queen      |         | EMI Records  |
| Pink Floyd |         | EMI Records  |
| Michael    | Jackson | Epic Records |
+------------+---------+--------------+

Come si può vedere abbiamo introdotto degli alias:

nella SELECT usando la parola chiave AS
nel FROM/JOIN semplicemente aggiungendo il nome dell’alias per la tabella

Gli alias sono utili per avere un risultato leggibile, soprattutto con tabelle con attributi con lo stesso nome.

Campi calcolati

La SELECT può contare il numero di record con COUNT, e darci un risultato calcolato.

mysql> SELECT COUNT(*) AS TotaleCanzoni FROM CANZONE;
+---------------+
| TotaleCanzoni |
+---------------+
|            26 |
+---------------+

DISTINCT consente di avere un conteggio che evita duplicati:

mysql> SELECT COUNT(DISTINCT Anno) AS AnniDistinti FROM ALBUM;
+--------------+
| AnniDistinti |
+--------------+
|            5 |
+--------------+

Molto interessante la possibilità di aggregare i risultati di una JOIN, calcolando quanti valori associati ad una riga sono presenti in un’altra. Questo è possibile usando la clausola GROUP BY. Ad esempio vogliamo vedere il totale numero di album per autore:

mysql> SELECT Nome, Cognome, COUNT(ALBUM.Id) AS Totale 
   FROM AUTORE JOIN ALBUM ON AUTORE.ID = ALBUM.AutoreId 
   GROUP BY AUTORE.Id;
+------------+---------+--------+
| Nome       | Cognome | Totale |
+------------+---------+--------+
| Queen      |         |      2 |
| Michael    | Jackson |      2 |
| Pink Floyd |         |      1 |
+------------+---------+--------+

Se volessimo però eseguire un filtro sul Totale calcolato, non possiamo usare WHERE, perché viene eseguita PRIMA del calcolo del totale. Possiamo però usare la clausola HAVING COUNT:

 mysql> SELECT Nome, Cognome, COUNT(ALBUM.Id) AS Totale 
  FROM AUTORE JOIN ALBUM ON AUTORE.ID = ALBUM.AutoreId 
  GROUP BY AUTORE.Id 
  HAVING Totale > 1;
+---------+---------+--------+
| Nome    | Cognome | Totale |
+---------+---------+--------+
| Queen   |         |      2 |
| Michael | Jackson |      2 |
+---------+---------+--------+

E’ possibile poi fare calcoli con MIN, MAX; SUM, AVG.

mysql> SELECT MIN(Anno) AS AlbumPiuVecchio, MAX(Anno) AS AlbumPiuRecente 
    -> FROM ALBUM;
+-----------------+-----------------+
| AlbumPiuVecchio | AlbumPiuRecente |
+-----------------+-----------------+
|            1973 |            1987 |
+-----------------+-----------------+

mysql> SELECT SUM(Durata) AS TotaleAnniContratti FROM CONTRATTO;
+---------------------+
| TotaleAnniContratti |
+---------------------+
|                  18 |
+---------------------+

mysql> SELECT AVG(Durata) AS DurataMedia FROM CONTRATTO;
+-------------+
| DurataMedia |
+-------------+
|      6.0000 |
+-------------+

Sulle date è possibile poi estrarre anno, mese giorno con YEAR, MONTH, DAY. Inoltre è possibile avere la data attuale con NOW(). Ad esempio possiamo calcolare l’età di ogni album:

mysql> SELECT Titolo, Anno, (YEAR(NOW()) - Anno) AS Età  
  FROM ALBUM;
+---------------------------+------+------+
| Titolo                    | Anno | Età  |
+---------------------------+------+------+
| A Night at the Opera      | 1975 |   51 |
| News of the World         | 1977 |   49 |
| Thriller                  | 1982 |   44 |
| Bad                       | 1987 |   39 |
| The Dark Side of the Moon | 1973 |   53 |
| Master of Puppets         | 1987 |   39 |
+---------------------------+------+------+

E’ poi possibile ordinare il risultato con ORDER BY, notare le clausole ASC (di default) e DESC.

mysql> SELECT Nome, Cognome, Titolo, Anno 
  FROM AUTORE JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 
  ORDER BY Anno; 
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
| Queen      |         | A Night at the Opera      | 1975 |
| Queen      |         | News of the World         | 1977 |
| Michael    | Jackson | Thriller                  | 1982 |
| Michael    | Jackson | Bad                       | 1987 |
+------------+---------+---------------------------+------+

mysql> SELECT Nome, Cognome, Titolo, Anno 
   FROM AUTORE JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 
   ORDER BY Anno ASC;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
| Queen      |         | A Night at the Opera      | 1975 |
| Queen      |         | News of the World         | 1977 |
| Michael    | Jackson | Thriller                  | 1982 |
| Michael    | Jackson | Bad                       | 1987 |
+------------+---------+---------------------------+------+

mysql> SELECT Nome, Cognome, Titolo, Anno 
   FROM AUTORE JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 
   ORDER BY Anno DESC;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Michael    | Jackson | Bad                       | 1987 |
| Michael    | Jackson | Thriller                  | 1982 |
| Queen      |         | News of the World         | 1977 |
| Queen      |         | A Night at the Opera      | 1975 |
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
+------------+---------+---------------------------+------+

Infine, possiamo usare LIMIT per limitare il numero di risultati:

mysql> SELECT Titolo, Anno 
    -> FROM ALBUM 
    -> ORDER BY Anno ASC 
    -> LIMIT 3;
+---------------------------+------+
| Titolo                    | Anno |
+---------------------------+------+
| The Dark Side of the Moon | 1973 |
| A Night at the Opera      | 1975 |
| News of the World         | 1977 |
+---------------------------+------+

Query annidate, IN, ALL, ANY

Quando vogliamo eseguire una WHERE su un campo che deve essere calcolato, possiamo eseguire una sub Query, ovvero una query annidata. Ad esempio se vogliamo sapere gli Album pubblicati entro tre anni dall’album più vecchio presente nella liberia musicale.

mysql> SELECT Titolo, Anno 
  FROM ALBUM 
  WHERE Anno < (
    SELECT MIN(Anno)+3 
    FROM ALBUM
  );
+---------------------------+------+
| Titolo                    | Anno |
+---------------------------+------+
| A Night at the Opera      | 1975 |
| The Dark Side of the Moon | 1973 |
+---------------------------+------+

La Query annidata è una qualsiasi Query sul database, quindi anche su tabelle differenti, che estrae un valore che può essere confrontato nella query esterna col valore della riga corrente che sta venendo esaminata. Il sistema per ogni riga esegue la query annidata, prende il risultato ed esegue la condizione.

La query annidate permettono di svolgere logica di maggiore complessità rispetto ad una ricerca con filtro e join di più tabelle. Infatti la loro caratteristica principale è anziché lavorare sull’intero insieme dato dal FROM, sono eseguite riga per riga.

Ad esempio poniamo di voler vedere solo gli album pubblicati solo nell’ultimo anno di pubblicazione da ciascuna casa discografica:

mysql> SELECT A1.Titolo, A1.Anno, CD.Nome AS Etichetta  
   FROM ALBUM A1 
   JOIN CASA_DISCOGRAFICA CD on CD.id = A1.CasaDiscograficaId 
   WHERE A1.Anno = (     
      SELECT MAX(A2.Anno)
      FROM ALBUM A2
      WHERE A2.CasaDiscograficaId = A1.CasaDiscograficaId );
+-------------------+------+--------------+
| Titolo            | Anno | Etichetta    |
+-------------------+------+--------------+
| Bad               | 1987 | Epic Records |
| Master of Puppets | 1987 | EMI Records  |
+-------------------+------+--------------+

Come si vede per ogni album, si effettua la verifica creando una query che per ogni riga, estrae l’anno di ultima pubblicazione della stessa casa discografica, e se corrisponde, lo include nel risultato.

E’ anche possibile usare le query annidate al posto delle JOIN. Ad esempio al posto di questa JOIN:

SELECT DISTINCT AUTORE.Nome, AUTORE.Cognome 
FROM AUTORE 
JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;

possiamo usare questa con l’operatore IN:

SELECT Nome, Cognome FROM AUTORE 
WHERE Id IN (SELECT AutoreId FROM ALBUM);

Gli operatori possibili sono:

IN: indica se il valore è nell’insieme individuato dalla sotto query;
NOT IN: indica se il valore non è nell’insieme dato dalla sottoquery;
EXISTS: da TRUE se la query restituisce almeno un risultato
NOT EXISTS: da TRUE se la query NON restituisce almeno un risultato

Ad esempio:

SELECT A.Nome, A.Cognome
FROM AUTORE A
WHERE NOT EXISTS (
    SELECT 1 
    FROM ALBUM Al 
    WHERE Al.AutoreId = A.Id
);

La query annidata è prestazionalmente svantaggiosa: mentre una JOIN viene eseguita usando di norma le chiavi, che sono indicizzate e quindi in modo efficiente, la subquery viene eseguita per ogni riga, in modo inefficiente.

Tuttavia la query annidata è più semplice da leggere e da capire, perché è più vicina al modo di pensare di chi, come la maggior parte dei programmatori, viene dalla programmazione imperativa, come si può vedere anche dagli esempi sopra indicati.

Ci sono poi situazioni in cui la query annidata è necessaria, perché con la JOIN non si ottengono gli stessi risultati. Lo vediamo con questo esempio.

Prima di tutto inseriamo questo nuovo record:

INSERT INTO ALBUM (Titolo, Anno, AutoreId) 
VALUES ('Queen', 1973, 1);

Se ora ricerchiamo gli album dell’anno più lontano in assoluto, usiamo questa query:

mysql> SELECT Titolo, Anno 
   FROM ALBUM 
   WHERE Anno = (
      SELECT MIN(Anno)
      FROM ALBUM);
+---------------------------+------+
| Titolo                    | Anno |
+---------------------------+------+
| The Dark Side of the Moon | 1973 |
| Queen                     | 1973 |
+---------------------------+------+

Non c’è modo di eseguire questa query con una JOIN, perchè applichiamo una condizione DINAMICA per ogni riga del nostro dataset.

UNION, INTERSECT, EXCEPT

Talvolta può essere utile unire insieme risultati con la stessa proiezione, ma con diversi filtri:

UNION unisce due insiemi, come nel FULL OUTER JOIN, unione di un LEFT OUTER JOIN e un RIGHT OUTER JOIN. Lo abbiamo già visto sopra. Le righe duplicate vengono scartate

SELECT Nome, Cognome, Titolo, Anno 
FROM AUTORE LEFT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 

UNION

SELECT Nome, Cognome, Titolo, Anno 
FROM AUTORE RIGHT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;

INTERSECT, dove manteniamo solo gli elementi comuni. In questo esempio di fatto otteniamo un INNER JOIN:

mysql> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE LEFT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 
    -> 
    -> INTERSECT
    -> 
    -> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE RIGHT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;
+------------+---------+---------------------------+------+
| Nome       | Cognome | Titolo                    | Anno |
+------------+---------+---------------------------+------+
| Queen      |         | Queen                     | 1973 |
| Queen      |         | News of the World         | 1977 |
| Queen      |         | A Night at the Opera      | 1975 |
| Michael    | Jackson | Bad                       | 1987 |
| Michael    | Jackson | Thriller                  | 1982 |
| Pink Floyd |         | The Dark Side of the Moon | 1973 |
+------------+---------+---------------------------+------+

EXCEPT: gli elementi del primo insieme, tolti gli elementi del secondo. Ad esempio qui estraiamo gli autori senza un album:

mysql> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE LEFT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId 
    -> 
    -> EXCEPT
    -> 
    -> SELECT Nome, Cognome, Titolo, Anno 
    -> FROM AUTORE RIGHT OUTER JOIN ALBUM ON AUTORE.Id = ALBUM.AutoreId;
+-------+---------+--------+------+
| Nome  | Cognome | Titolo | Anno |
+-------+---------+--------+------+
| Vasco | Rossi   | NULL   | NULL |
+-------+---------+--------+------+

VIEW

La View è un modo per memorizzare una query SELECT con un nome. Ad esempio creiamo una query per

mysql> CREATE VIEW VISTA_INFO_CANZONI AS
      SELECT
      C.Titolo AS NomeCanzone,
      Al.Titolo AS NomeAlbum,
      Al.Anno AS AnnoPubblicazione,
      Au.Nome AS NomeAutore FROM CANZONE C JOIN ALBUM Al ON C.AlbumId = Al.Id JOIN AUTORE Au ON Al.AutoreId = Au.Id;

La View è comoda per non dover ripetere sempre la stessa query:

mysql> SELECT NomeCanzone, NomeAlbum, AnnoPubblicazione, NomeAutore FROM VISTA_INFO_CANZONI;
+------------------------------+---------------------------+-------------------+------------+
| NomeCanzone                  | NomeAlbum                 | AnnoPubblicazione | NomeAutore |
+------------------------------+---------------------------+-------------------+------------+
| Death on two Legs            | A Night at the Opera      |              1975 | Queen      |
| Lazing on a Sunday Afternoon | A Night at the Opera      |              1975 | Queen      |
| You're My Best Friend        | A Night at the Opera      |              1975 | Queen      |
| Love of My Life              | A Night at the Opera      |              1975 | Queen      |
| Bohemian Rhapsody            | A Night at the Opera      |              1975 | Queen      |
| We Will Rock You             | News of the World         |              1977 | Queen      |
| We Are the Champions         | News of the World         |              1977 | Queen      |
| Sheer Heart Attack           | News of the World         |              1977 | Queen      |
| All Dead, All Dead           | News of the World         |              1977 | Queen      |
| Spread Your Wings            | News of the World         |              1977 | Queen      |
| Wanna Be Startin' Somethin'  | Thriller                  |              1982 | Michael    |
| Thriller                     | Thriller                  |              1982 | Michael    |
| Beat It                      | Thriller                  |              1982 | Michael    |
| Billie Jean                  | Thriller                  |              1982 | Michael    |
| Human Nature                 | Thriller                  |              1982 | Michael    |
| Bad                          | Bad                       |              1987 | Michael    |
| The Way You Make Me Feel     | Bad                       |              1987 | Michael    |
| Speed Demon                  | Bad                       |              1987 | Michael    |
| Liberian Girl                | Bad                       |              1987 | Michael    |
| Smooth Criminal              | Bad                       |              1987 | Michael    |
| Speak to Me                  | The Dark Side of the Moon |              1973 | Pink Floyd |
| Breathe                      | The Dark Side of the Moon |              1973 | Pink Floyd |
| Time                         | The Dark Side of the Moon |              1973 | Pink Floyd |
| Money                        | The Dark Side of the Moon |              1973 | Pink Floyd |
| Us and Them                  | The Dark Side of the Moon |              1973 | Pink Floyd |
| Brain Damage                 | The Dark Side of the Moon |              1973 | Pink Floyd |
+------------------------------+---------------------------+-------------------+------------+

UPDATE

La funzione di UPDATE consente di modificare il contenuto di una riga di una tabella. Ha questa struttura

UPDATE Tabella SET Attributo1 = Valore1, Attributo2 = Valore2, ... WHERE Condizione

Esempio:

UPDATE CANZONE
SET Titolo = 'Love Of My Life'
WHERE Titolo = 'Love of My Life'

Attenzione: se non si usa la WHERE si modificano tutte le righe!

UPDATE rispetta l’integrità referenziale, e non modifica nulla se c’è una regola di controllo.

DELETE e TRUNCATE

Per cancellare una riga si usa un sistema simile all’UPDATE:

DELETE FROM Tabella WHERE Condizione

Esempio:

DELETE FROM AUTORE 
WHERE Nome='Vasco' AND Cognome='Rossi';

Attenzione: se non si usa la WHERE si eliminano tutte le righe!

Se invece si vuole cancellare la tabella, usiamo TRUNCATE:

TRUNCATE TABLE Tabella

TRUNCATE non è una DELETE senza WHERE, perché resetta il contatore dell’Id autoincrementale, ed è specie per tabelle grandi, molto veloce (non cancella una riga alla volta).

Sia DELETE che TRUNCATE rispettano comunque l’integrità referenziale, e non cancellano nulla se c’è una regola di controllo.

Conclusioni

In questa lezione abbiamo visto le operazioni CRUD con SQL (DML):

INSERT
SELECT
UPDATE
DELETE

Insieme formano le operazioni CRUD su un database.

Tags:

Home

Programmazione

Introduzione alla programmazione

Sviluppo del Software

C/C++

Java

HTML

Javascript

NodeJS

Progettazione

Ciclo di vita del Software

Progettazione del software

Database

Tecnologie

Sistemi operativi

Architetture degli elaboratori

Tecnologie Web

Intelligenza Artificiale

Esercizi di Tecnologie

OpenCV

SpringBoot

Strumenti

Progetti Web

Strumenti web

Strumenti sviluppo

Introduzione all'Informatica