Appunti di Programmazione
Le Viste
Le Viste sono Tabelle che permettono la visualizzazione dei dati provenienti da più fonti i cui elementi sono combinati fra loro.
Il mio scopo è quello di creare una classifica di realizzatori sia per la categoria Esordienti che per quella Aquilotti e poi due Registri Presenze per annotare il numero di partite a cui ha partecipato ciascun ragazzo anch'esso suddiviso per categoria.
Registro Presenze
mysql> CREATE VIEW presenze_aquilotti
-> AS SELECT Partita.Codice_Partita,Campionato.Categoria, Giocatori.* FROM
-> Giocatori inner join Partita
-> ON Giocatori.Numero_Maglia=Partita.Numero_Maglia INNER JOIN Campionato
-> ON Partita.Codice_Partita=Campionato.Codice_Partita
-> WHERE Campionato.Categoria="Aquilotti"
-> ORDER BY Giocatori.Numero_Maglia, Partita.Codice_Partita;
Query OK, 0 rows affected (0,12 sec)
mysql> SELECT * FROM presenze_aquilotti;
+----------------+-----------+---------------+-----------+------------+------+
| Codice_Partita | Categoria | Numero_Maglia | Cognome | Nome | Anno |
+----------------+-----------+---------------+-----------+------------+------+
| 4 | Aquilotti | 8 | Azzurro | Alberto | 2007 |
| 1 | Aquilotti | 11 | Beige | Andrea | 2006 |
| 4 | Aquilotti | 11 | Beige | Andrea | 2006 |
| 1 | Aquilotti | 13 | Bordeaux | Riccardo | 2006 |
| 4 | Aquilotti | 13 | Bordeaux | Riccardo | 2006 |
| 1 | Aquilotti | 15 | Carbone | Lorenzo | 2006 |
| 4 | Aquilotti | 15 | Carbone | Lorenzo | 2006 |
| 1 | Aquilotti | 17 | Arancioni | Romeo | 2006 |
| 4 | Aquilotti | 17 | Arancioni | Romeo | 2006 |
| 1 | Aquilotti | 18 | Catrame | Vittorio | 2006 |
| 4 | Aquilotti | 18 | Catrame | Vittorio | 2006 |
| 1 | Aquilotti | 20 | Rossi | Alessandro | 2006 |
| 4 | Aquilotti | 20 | Rossi | Alessandro | 2006 |
| 1 | Aquilotti | 23 | Gialli | Paolo | 2006 |
| 1 | Aquilotti | 30 | Neri | Tommaso | 2006 |
| 4 | Aquilotti | 30 | Neri | Tommaso | 2006 |
| 1 | Aquilotti | 33 | Blu | Emanuele | 2007 |
| 4 | Aquilotti | 33 | Blu | Emanuele | 2007 |
| 4 | Aquilotti | 36 | Ecru | Federico | 2007 |
| 1 | Aquilotti | 37 | Fucsia | Francesco | 2007 |
| 4 | Aquilotti | 37 | Fucsia | Francesco | 2007 |
| 1 | Aquilotti | 40 | Granata | Roberto | 2007 |
| 1 | Aquilotti | 42 | Ambra | Francesco | 2007 |
| 4 | Aquilotti | 42 | Ambra | Francesco | 2007 |
+----------------+-----------+---------------+-----------+------------+------+
24 rows in set (0,06 sec)
Manuale Online: CREATE VIEW
Viene creata una Vista di nome "presenze_aquilotti", che per convenzione viene scritta totalmente in minuscolo per distinguerla dalle Tabelle, le cui colonne sono selezionate in questo modo:
- "Codice_Partita" - recuperata dalla Tabella Partita.
- "Categoria" - recuperata dalla Tabella Campionato
- Tutte le colonne della Tabella Giocatori.
Le tre Tabelle sono unite/collegate fra loro attraverso la clausola INNER JOIN in questo modo:
- "Giocatori" e "Campionato" attraverso la colonna Numero_Maglia.
- "Partita" e "Campionato" attraverso la colonna Codice_Partita
Sono elencati solo i ragazzi che giocano nella catergoria Aquilotti e la lista è ordinata usando come criterio prima il numero della maglia e poi i codice della partita.
La colonna che funge da tramite deve essere identica in entrambe le Tabelle, e in una delle due deve essere una Chiave Primaria.
Manuale Online: JOIN
La Vista così come è non è molto pratica da leggere, in quanto produce una lista dei bambini che hanno partecipato alle varie partite ripetendo il nome di ciascun ragazzo tante volte quante sono i match che ha disputato.
Per avere un elenco in cui per ogni giocatoe viene visualizzato il numero di presenze, si usa una SELECT con l'opzione COUNT(*) che permette di conteggiare la ripetizione di un valore in una colonna:
mysql> SELECT Numero_Maglia, Cognome, Nome, Anno, COUNT(*) AS Presenze
-> FROM presenze_aquilotti
-> WHERE Anno>2005 and Anno<2009
-> GROUP BY Numero_Maglia HAVING COUNT(*)>=0;
+---------------+-----------+------------+------+----------+
| Numero_Maglia | Cognome | Nome | Anno | Presenze |
+---------------+-----------+------------+------+----------+
| 8 | Azzurro | Alberto | 2007 | 1 |
| 11 | Beige | Andrea | 2006 | 2 |
| 13 | Bordeaux | Riccardo | 2006 | 2 |
| 15 | Carbone | Lorenzo | 2006 | 2 |
| 17 | Arancioni | Romeo | 2006 | 2 |
| 18 | Catrame | Vittorio | 2006 | 2 |
| 20 | Rossi | Alessandro | 2006 | 2 |
| 23 | Gialli | Paolo | 2006 | 1 |
| 30 | Neri | Tommaso | 2006 | 2 |
| 33 | Blu | Emanuele | 2007 | 2 |
| 36 | Ecru | Federico | 2007 | 1 |
| 37 | Fucsia | Francesco | 2007 | 2 |
| 40 | Granata | Roberto | 2007 | 1 |
| 42 | Ambra | Francesco | 2007 | 2 |
+---------------+-----------+------------+------+----------+
14 rows in set (0,04 sec)
La SELECT visualizza a video le colonne "Numero_Maglia", "Cognome", "Nome", "Anno" e una colonna dei conteggi di nome "Presenze", recuperate dalla Vista "presenze_aquilotti" dove i ragazzi sono nati negli anni 2006, 2007 e 2008 raggruppati per la colonna "Numero_Maglia". La clauola COUNT(*) permette di conteggiare le righe con lo stesso tipo di informazione e di raggrupparle secondo criteri a noi utili.
Manuale Online: COUNT(*)
Similmente si realizzano la Vista e la SELECT per il Registro Presenze degli Erordienti
mysql> CREATE VIEW presenze_esordienti
-> AS SELECT Partita.Codice_Partita, Campionato.Categoria, Giocatori.* FROM
-> Giocatori inner join Partita
-> ON Giocatori.Numero_Maglia=Partita.Numero_Maglia INNER JOIN Campionato
-> ON Partita.Codice_Partita=Campionato.Codice_Partita
-> WHERE Campionato.Categoria="Esordienti"
-> ORDER BY Giocatori.Numero_Maglia, Partita.Codice_Partita;
Query OK, 0 rows affected (0,06 sec)
mysql> SELECT Numero_Maglia, Cognome, Nome, Anno, COUNT(*) AS Presenze
-> FROM presenze_esordienti
-> WHERE Anno>2004 and Anno<2007
-> GROUP BY Numero_Maglia HAVING COUNT(*)>=0;
+---------------+-----------+------------+------+----------+
| Numero_Maglia | Cognome | Nome | Anno | Presenze |
+---------------+-----------+------------+------+----------+
| 4 | Celesti | Nicolò | 2005 | 1 |
| 7 | Avorio | Andrea | 2005 | 1 |
| 10 | Marroni | Ivan | 2005 | 1 |
| 11 | Beige | Andrea | 2006 | 1 |
| 14 | Antracite | Lorenzo | 2005 | 2 |
| 15 | Carbone | Lorenzo | 2006 | 2 |
| 17 | Arancioni | Romeo | 2006 | 2 |
| 18 | Catrame | Vittorio | 2006 | 2 |
| 19 | Antracite | Ferdinando | 2005 | 2 |
| 20 | Rossi | Alessandro | 2006 | 2 |
| 21 | Bianchi | Antonio | 2005 | 1 |
| 23 | Gialli | Paolo | 2006 | 1 |
| 24 | Ciano | Federico | 2005 | 2 |
| 25 | Corallo | Luca | 2005 | 1 |
| 26 | Cremisi | Emiliano | 2005 | 2 |
| 30 | Neri | Tommaso | 2006 | 1 |
+---------------+-----------+------------+------+----------+
16 rows in set (0,03 sec)
Classifiche Realizzatori
Per la realizzazione delle due classifiche ho innanzitutto creato una Vista con i canestri realizzati da ciascun giocatore in ogni partita suddivisi per valore; poi da questa ho estratto i giocatori che hanno frequentato il campionato Aquilotti e quello Esordienti.
mysql> CREATE VIEW canestri AS SELECT Partita.Codice_Partita,
-> Partita.Numero_Maglia,Campionato.Categoria,
-> Partita.Canestri_2p_1T+Partita.Canestri_2p_2T+
-> Partita.Canestri_2p_3T+Partita.Canestri_2p_4T+
-> Partita.Canestri_2p_5T+Partita.Canestri_2p_6T+
-> Partita.Canestri_2p_7T+Partita.Canestri_2p_8T AS Canestri_2p,
-> Partita.Canestri_1p_1T+Partita.Canestri_1p_2T+
-> Partita.Canestri_1p_3T+Partita.Canestri_1p_4T+
-> Partita.Canestri_1p_5T+Partita.Canestri_1p_6T+
-> Partita.Canestri_1p_7T+Partita.Canestri_1p_8T AS Canestri_1p
-> FROM Partita INNER JOIN Campionato
-> ON Partita.Codice_Partita=Campionato.Codice_Partita;
Query OK, 0 rows affected (0,11 sec)
mysql> SELECT * FROM canestri;
+----------------+---------------+------------+-------------+-------------+
| Codice_Partita | Numero_Maglia | Categoria | Canestri_2p | Canestri_1p |
+----------------+---------------+------------+-------------+-------------+
| 1 | 11 | Aquilotti | 1 | 1 |
| 1 | 13 | Aquilotti | 0 | 0 |
| 1 | 15 | Aquilotti | 1 | 0 |
| 1 | 17 | Aquilotti | 1 | 1 |
| 1 | 18 | Aquilotti | 1 | 1 |
| 1 | 20 | Aquilotti | 0 | 1 |
| 1 | 23 | Aquilotti | 1 | 0 |
| 1 | 30 | Aquilotti | 0 | 0 |
| 1 | 33 | Aquilotti | 0 | 3 |
| 1 | 37 | Aquilotti | 2 | 0 |
| 1 | 40 | Aquilotti | 0 | 0 |
| 1 | 42 | Aquilotti | 0 | 0 |
| 2 | 4 | Esordienti | 0 | 0 |
| 2 | 7 | Esordienti | 1 | 0 |
| 2 | 11 | Esordienti | 0 | 0 |
| 2 | 14 | Esordienti | 0 | 1 |
| 2 | 15 | Esordienti | 0 | 0 |
| 2 | 17 | Esordienti | 1 | 0 |
| 2 | 18 | Esordienti | 1 | 1 |
| 2 | 19 | Esordienti | 1 | 0 |
| 2 | 20 | Esordienti | 2 | 0 |
| 2 | 21 | Esordienti | 0 | 0 |
| 2 | 24 | Esordienti | 9 | 1 |
| 2 | 26 | Esordienti | 3 | 0 |
| 3 | 10 | Esordienti | 0 | 0 |
| 3 | 14 | Esordienti | 1 | 1 |
| 3 | 15 | Esordienti | 0 | 0 |
| 3 | 17 | Esordienti | 0 | 0 |
| 3 | 18 | Esordienti | 2 | 1 |
| 3 | 19 | Esordienti | 1 | 0 |
| 3 | 20 | Esordienti | 0 | 0 |
| 3 | 23 | Esordienti | 1 | 0 |
| 3 | 24 | Esordienti | 8 | 4 |
| 3 | 25 | Esordienti | 0 | 0 |
| 3 | 26 | Esordienti | 0 | 1 |
| 3 | 30 | Esordienti | 0 | 0 |
| 4 | 8 | Aquilotti | 0 | 0 |
| 4 | 11 | Aquilotti | 5 | 0 |
| 4 | 13 | Aquilotti | 2 | 0 |
| 4 | 15 | Aquilotti | 6 | 1 |
| 4 | 17 | Aquilotti | 0 | 1 |
| 4 | 18 | Aquilotti | 7 | 2 |
| 4 | 20 | Aquilotti | 5 | 0 |
| 4 | 30 | Aquilotti | 3 | 0 |
| 4 | 33 | Aquilotti | 0 | 0 |
| 4 | 36 | Aquilotti | 0 | 0 |
| 4 | 37 | Aquilotti | 4 | 2 |
| 4 | 42 | Aquilotti | 1 | 0 |
+----------------+---------------+------------+-------------+-------------+
48 rows in set (0,07 sec)
Viene creata una Vista selezionando le colonne Codice Partita e Numero Maglia dalla Tabella Partita, la Categoria dalla Tabella Campionato e poi vengono sommati i canestri realizzati in ciascun tempo da ogni singolo bambino raccolti in due variabili Canestri_2p e Canestri_1p di 2 e 1 punto rispettivamente, mettendo in relazione le due Tabelle Partita e Campionato attraverso i campi Codice Partita.
E adesso si scrivono le Viste per estrarre i dati relativi agli Aquilotti e agli Esordienti.
mysql> CREATE VIEW canestri_aquilotti AS SELECT Numero_Maglia,
-> SUM(Canestri_2p) AS Totale_2p, SUM(Canestri_1p) AS Totale_1p,
-> (SUM(Canestri_2p)*2+SUM(Canestri_1p)) AS Punteggio
-> FROM canestri WHERE Categoria="Aquilotti" GROUP BY Numero_Maglia;
Query OK, 0 rows affected (0,31 sec)
mysql> SELECT * FROM canestri_aquilotti;
+---------------+-----------+-----------+-----------+
| Numero_Maglia | Totale_2p | Totale_1p | Punteggio |
+---------------+-----------+-----------+-----------+
| 8 | 0 | 0 | 0 |
| 11 | 6 | 1 | 13 |
| 13 | 2 | 0 | 4 |
| 15 | 7 | 1 | 15 |
| 17 | 1 | 2 | 4 |
| 18 | 8 | 3 | 19 |
| 20 | 5 | 1 | 11 |
| 23 | 1 | 0 | 2 |
| 30 | 3 | 0 | 6 |
| 33 | 0 | 3 | 3 |
| 36 | 0 | 0 | 0 |
| 37 | 6 | 2 | 14 |
| 40 | 0 | 0 | 0 |
| 42 | 1 | 0 | 2 |
+---------------+-----------+-----------+-----------+
14 rows in set (0,06 sec)
mysql> CREATE VIEW canestri_esordienti AS SELECT Numero_Maglia,
-> SUM(Canestri_2p) AS Totale_2p, SUM(Canestri_1p) AS Totale_1p,
-> (SUM(Canestri_2p)*2+SUM(Canestri_1p)) AS Punteggio
-> FROM canestri WHERE Categoria="Esordienti" GROUP BY Numero_Maglia;
Query OK, 0 rows affected (0,06 sec)
mysql> SELECT * FROM canestri_esordienti;
+---------------+-----------+-----------+-----------+
| Numero_Maglia | Totale_2p | Totale_1p | Punteggio |
+---------------+-----------+-----------+-----------+
| 4 | 0 | 0 | 0 |
| 7 | 1 | 0 | 2 |
| 10 | 0 | 0 | 0 |
| 11 | 0 | 0 | 0 |
| 14 | 1 | 2 | 4 |
| 15 | 0 | 0 | 0 |
| 17 | 1 | 0 | 2 |
| 18 | 3 | 2 | 8 |
| 19 | 2 | 0 | 4 |
| 20 | 2 | 0 | 4 |
| 21 | 0 | 0 | 0 |
| 23 | 1 | 0 | 2 |
| 24 | 17 | 5 | 39 |
| 25 | 0 | 0 | 0 |
| 26 | 3 | 1 | 7 |
| 30 | 0 | 0 | 0 |
+---------------+-----------+-----------+-----------+
16 rows in set (0,01 sec)
E adesso le due SELECT per la visualizzazione delle due Classifice:
mysql> SELECT Giocatori.*, canestri_aquilotti.Totale_2p,
-> canestri_aquilotti.Totale_1p, canestri_aquilotti.Punteggio
-> FROM Giocatori INNER JOIN canestri_aquilotti
-> ON Giocatori.Numero_Maglia = canestri_aquilotti.Numero_Maglia
-> WHERE Giocatori.Anno<2009 ORDER BY canestri_aquilotti.Punteggio DESC;
+---------------+-----------+------------+------+-----------+-----------+-----------+
| Numero_Maglia | Cognome | Nome | Anno | Totale_2p | Totale_1p | Punteggio |
+---------------+-----------+------------+------+-----------+-----------+-----------+
| 18 | Catrame | Vittorio | 2006 | 8 | 3 | 19 |
| 15 | Carbone | Lorenzo | 2006 | 7 | 1 | 15 |
| 37 | Fucsia | Francesco | 2007 | 6 | 2 | 14 |
| 11 | Beige | Andrea | 2006 | 6 | 1 | 13 |
| 20 | Rossi | Alessandro | 2006 | 5 | 1 | 11 |
| 30 | Neri | Tommaso | 2006 | 3 | 0 | 6 |
| 17 | Arancioni | Romeo | 2006 | 1 | 2 | 4 |
| 13 | Bordeaux | Riccardo | 2006 | 2 | 0 | 4 |
| 33 | Blu | Emanuele | 2007 | 0 | 3 | 3 |
| 42 | Ambra | Francesco | 2007 | 1 | 0 | 2 |
| 23 | Gialli | Paolo | 2006 | 1 | 0 | 2 |
| 8 | Azzurro | Alberto | 2007 | 0 | 0 | 0 |
| 40 | Granata | Roberto | 2007 | 0 | 0 | 0 |
| 36 | Ecru | Federico | 2007 | 0 | 0 | 0 |
+---------------+-----------+------------+------+-----------+-----------+-----------+
14 rows in set (0,00 sec)
mysql> SELECT Giocatori.*, canestri_esordienti.Totale_2p,
-> canestri_esordienti.Totale_1p, canestri_esordienti.Punteggio
-> FROM Giocatori INNER JOIN canestri_esordienti
-> ON Giocatori.Numero_Maglia = canestri_esordienti.Numero_Maglia
-> WHERE Giocatori.Anno>2004 ORDER BY canestri_esordienti.Punteggio DESC;
+---------------+-----------+------------+------+-----------+-----------+-----------+
| Numero_Maglia | Cognome | Nome | Anno | Totale_2p | Totale_1p | Punteggio |
+---------------+-----------+------------+------+-----------+-----------+-----------+
| 24 | Ciano | Federico | 2005 | 17 | 5 | 39 |
| 18 | Catrame | Vittorio | 2006 | 3 | 2 | 8 |
| 26 | Cremisi | Emiliano | 2005 | 3 | 1 | 7 |
| 14 | Antracite | Lorenzo | 2005 | 1 | 2 | 4 |
| 19 | Antracite | Ferdinando | 2005 | 2 | 0 | 4 |
| 20 | Rossi | Alessandro | 2006 | 2 | 0 | 4 |
| 7 | Avorio | Andrea | 2005 | 1 | 0 | 2 |
| 17 | Arancioni | Romeo | 2006 | 1 | 0 | 2 |
| 23 | Gialli | Paolo | 2006 | 1 | 0 | 2 |
| 4 | Celesti | Nicolò | 2005 | 0 | 0 | 0 |
| 15 | Carbone | Lorenzo | 2006 | 0 | 0 | 0 |
| 25 | Corallo | Luca | 2005 | 0 | 0 | 0 |
| 10 | Marroni | Ivan | 2005 | 0 | 0 | 0 |
| 21 | Bianchi | Antonio | 2005 | 0 | 0 | 0 |
| 11 | Beige | Andrea | 2006 | 0 | 0 | 0 |
| 30 | Neri | Tommaso | 2006 | 0 | 0 | 0 |
+---------------+-----------+------------+------+-----------+-----------+-----------+
16 rows in set (0,01 sec)
