Quando si lavora con siti web che presentano milioni di visitatori giornalieri, e' tipico dover gestire molteplici database con all'interno decine e centinaia di tabelle. Se ti occupi di analisi dei dati è normale che ti venga richiesto di estrarre delle informazioni da molteplici tabelle, ma anche una semplice dashboard per un sito di e-commerce richiede spesso la visualizzazione di dati ottenuti da più tabelle.
In molti casi la query SQL che progetti per l'obiettivo richiesto, presenta una lunga sequenza di JOIN tra le diverse tabelle. Risulta utile avere a disposizione uno strumento che confezioni l'intera query in una "scatola nera" rappresentativa del risultato ottenuto, ossia una nuova tabella costituita da un certo numero di colonne e righe, che risulti quindi più semplice da manipolare per ulteriori analisi.
Si parla per l'appunto di "Vista" o "View" o in generale di tabella virtuale. Le "View" nascondono eventuali operazioni potenzialmente complesse di join, calcoli e filtri. Le viste sembrano tabelle regolari, quindi possiamo utilizzare le nostre istruzioni di SELECT e possiamo anche unire più viste.
Facciamo un esempio nell'ipotesi di gestire un sito di e-commerce di libri e avere solo due tabelle "Autori" e "Catalogo", relative rispettivamente ai nomi di alcuni autori di libri "famosi", e ai relativi libri scritti. Le due tabelle qui sotto sono volutamente con poche righe per rendere più semplice l'analisi.
AUTORI LIBRI
+----+------------+--- -----+-------------+ | id | nome | cognome | nazionalità | +----+------------+---- ----+-------------+ | 1 | Alessandro | Manzoni | italiana | | 2 | Davide | Copelli | italiana | | 3 | Umberto | Eco | italiana | | 4 | Italo | Calvino | italiana | +----+------------+---------+-------------+
CATALOGO LIBRI
+-----+------------------------+------+ | ida | titolo | Anno | +-----+------------------------+------+ | 1 | I Promessi Sposi | 1840 | | 2 | Angular 100% Operativo | 2017 | | 2 | React 100% Operativo | 2018 | | 3 | Il nome della Rosa | 1980 | +-----+------------------------+------+
Quello che potremmo voler visualizzare è il numero di libri presenti nel nostro catalogo, scritti da ciascun autore, ossia impostare una query del tipo:
SELECT a.id, a.nome, a.cognome, COUNT(c.ida) AS numlibri
FROM autori AS a
JOIN catalogo AS c ON a.id = c.ida
GROUP BY c.ida;
Il risultato che ottengo sarà una nuova tabella filtrata e con i dati raggruppati per autore:
+----+------------+--- -----+----------+ | id | nome | cognome | numlibri | +----+------------+---- ----+----------+ | 1 | Alessandro | Manzoni | 1 | | 2 | Davide | Copelli | 2 | | 3 | Umberto | Eco | 1 | | 4 | Italo | Calvino | 0 | +----+------------+---------+----------+
Se da questa nuova sequenza di dati volessi eseguire ulteriori operazioni, ossia contare quanti autori non hanno libri in catalogo, oppure ordinare la lista autori in base al numero di libri, invece di reimpostare tutta la query potrei semplicemente usare i dati della tabella appena creata e agire su questa:
SELECT COUNT(id) FROM autoripresenti
WHERE numlibri=0
Oppure per ordinare gli autori con più libri:
SELECT * FROM autoripresenti
ORDER by numlibri DESC
Come vedi sono due query semplici da leggere e capire.
Per ottenere questo devo dare un nome alla tabella di dati ottenuta in precedenza. Questo viene fatto sfruttando la sintassi della creazione di una vista:
CREATE VIEW NomeTabella AS TipoQuery
Nel nostro caso:
CREATE VIEW autoripresenti AS
SELECT a.nome, a.cognome, COUNT(c.ida) AS numlibri
FROM autori AS a
JOIN catalogo AS c ON a.id = c.ida
GROUP BY c.ida;
I nomi delle colonne di una vista sono gli stessi che appaiono nella query che definisce la vista stessa. Quindi se nella query di SELECT individuo le colonne id, nome, cognome, questi saranno i nomi che compariranno nella vista.
NB: Le viste si comportano come normali tabelle per le istruzioni SELECT, ma non per le istruzioni UPDATE, INSERT e DELETE. In alcuni casi, non è possibile usarle. Questo perché una vista è solo una definizione; i dati che restituisce rimangono ancora nelle tabelle originali. Al liik seguente troverai un insieme di informazioni aggiuntive: View e Aggiornamento
Un altro uso frequente delle viste, è per limitare i rischi legati a tentativi di manipolare/accedere ai dati di una tabella in modo fraudolento. Se ad esempio, voglio mostrare ad un utente iscritto ad un'area riservata solo alcune informazioni memorizzate in una tabella, sia in termini di colonne che di campi riga, invece di usare la stessa query di SELECT che sfrutta l'amministratore dell'area riservata, e che agisce sulla tabella originale completa, potrei creare una vista, che già in partenza limiti l'accesso a particolari colonne e filtri i dati sulla base di specifici valori memorizzati in altre colonne.
Ad esempio:
CREATE VIEW utenti_attivi AS
SELECT nome, cognome
FROM iscritti
WHERE stato=1
In questo modo una query sulla vista utenti_attivi non potrà mai visualizzare (anche con tecniche di attacco SQL) tutti i campi colonna e riga della tabella originale iscritti.
