Query tramite SELECT (superiori)
Interrogare il database mediante il comando Select
modificaPer poter eseguire una interrogazione (query) del database utilizziamo il comando SELECT del linguaggio SQL
Con questo comando e' possibile decidere quali colonne visualizzare (proiezione) , quali righe visualizzare (selezione) e quali tabelle unire (congiunzione)
Per prima cosa vediamo di capire come si interroga un database
partiamo da un database in cui ci sono 2 tabelle collegate da una associazione 1:N
studenti
IDStudente | Nome | Cognome | DataNascita | CittaNascita | Altezza |
1 | Marco | Rossi | 21/1/1980 | Rovigo | 178 |
2 | Luca | Verdi | 25/12/1978 | Padova | 180 |
3 | Silvia | Blu | 13/9/1997 | Rovigo | 173 |
4 | Michele | Giallo | 14/9/1997 | Padova | 167 |
5 | Anna | Rossi | 23/2/1995 | Venezia | 180 |
compiti
IDCompito | Materia | Data | Voto | Tipo | IDstudente |
1 | matematica | 13/10/2014 | 7 | scritto | 1 |
2 | storia | 23/10/2014 | 8 | scritto | 3 |
3 | matematica | 13/11/2014 | 6 | orale | 1 |
4 | matematica | 13/11/2014 | 7 | orale | 4 |
5 | geografia | 15/5/2013 | 10 | orale | 4 |
6 | inglese | 13/4/2013 | 2 | scritto | 2 |
7 | storia | 3/10/2016 | 4 | scritto | 1 |
L'istruzione per interrogare ( query) un database e' l'struzione SELECT, la sua struttura e' la seguente
select listacolonne from listatabelle where condizionesullerighe group by listacolonneoggettoraggruppamento having condizionesuiraggruppamenti order by listacolonneoggettodiordinamento ;
per partire però partiamo lavorando su una sola tabella .
PROIEZIONE
modifica1) Vogliamo ottenere il nome e cognome di tutti gli studenti, il comando e':
select nome,cognome from studenti ;
in pratica questo comando isola solo alcune colonne della tabella studenti (l'operazione di selezione di alcune colonne e' detta proiezione) quello che otteniamo da questa query e' una tabella ( con due sole colonne e con tutte le righe della tabella studenti, quindi il grado diminuisce e la cardinalità si conserva , come capita in tutte le operazioni di proiezione)
Query1
Nome | Cognome |
Marco | Rossi |
Luca | Verdi |
Silvia | Blu |
Michele | Giallo |
Anna | Rossi |
se si preferisce possiamo cambiare l'ordine delle colonne
2) Vogliamo ottenere il cognome e nome di tutti gli studenti, il comando e':
select cognome,nome from studenti ;
Query2
Cognome | Nome |
Rossi | Marco |
Verdi | Luca |
Blu | Silvia |
Giallo | Michele |
Rossi | Anna |
3) Vogliamo ottenere il cognome e cognome di tutti gli studenti,ordinando per cognome il comando e':
select cognome,nome from studenti order by cognome ;
Query3
Cognome | Nome |
Blu | Silvia |
Giallo | Michele |
Rossi | Marco |
Rossi | Anna |
Verdi | Luca |
4) Vogliamo ottenere il cognome e nome di tutti gli studenti,ordinando per cognome e nel caso di cognome uguale ordinando anche per nome il comando e':
select cognome,nome from studenti order by cognome,nome ;
Query4
Cognome | Nome |
Blu | Silvia |
Giallo | Michele |
Rossi | Anna |
Rossi | Marco |
Verdi | Luca |
il comando query4 poteva essere scritto
select cognome,nome from studenti order by cognome ASC,nome ASC ;
dove ASC significa ordine ascendente (dal più piccolo al più grande) e può essere omesso perche' e' il valore di default
5) Vogliamo ottenere il cognome e cognome di tutti gli studenti,ordinando per cognome e nome in senso discendente (zorro prima di annibale) il comando e':
select cognome,nome from studenti order by cognome DESC ,nome DESC ;
Query5
Cognome | Nome |
Rossi | Marco |
Rossi | Anna |
Verdi | Luca |
Giallo | Michele |
Blu | Silvia |
6) dopo aver visto l'ordinamento ritorniamo alla nostra proiezione (selezione delle colonne)
possiamo voler ottenere tutte le colonne della tabella studenti si può scrivere
select IDStudente,Nome,Cognome,DataNascita,CittaNascita,Altezza from Studenti ;
o più comodamente
select * from studenti ;
dove * significa tutte le colonne
7) vogliamo ottenere tutte le citta' in cui sono nati gli studenti, il comando e'
select CittaNascita from studenti ;
otteniamo
Query6
CittaNascita |
Rovigo |
Padova |
Rovigo |
Padova |
Venezia |
per evitare le ripetizioni delle stesse voci si usa DISTINCT
select DISTINCT CittaNascita from Studenti ;
ottenendo
Query7
CittaNascita |
Rovigo |
Padova |
Venezia |
se le volete ordinate
select DISTINCT CittaNascita from Studenti order by Cittanascita ;
8) possiamo rinominare le colonne del risultato specificando l'attributo AS
select IDStudente AS Matricola , Cognome, Nome from Studenti ;
otteniamo
Query8
Matricola | Nome | Cognome |
1 | Marco | Rossi |
2 | Luca | Verdi |
3 | Silvia | Blu |
4 | Michele | Giallo |
5 | Anna | Rossi |
pensiamo di avere la tabella rettangoli
IdRettangolo | Base | Altezza |
1 | 10 | 5 |
2 | 15 | 20 |
come sapete per evitare problemi di ridondanza i campi che possono essere calcolati da altri campi della tabella non vengono inseriti nella tabella nel nostro caso quindi non e' possibile aggiungere il campo area rettangolo. È invece possibile visualizzare un campo calcolato mediate una query, non esistendo il nome del campo calcolato nella tabella gli viene fornito un nome di default del tipo campo1, campo2 etc, possiamo usando l'AS decidere noi un nome particolare per la colonna calcolata
select IdRettangolo,Base,Altezza, Base*Altezza AS AreaRettangolo from rettangoli ;
IdRettangolo | Base | Altezza | AreaRettangolo |
1 | 10 | 5 | 50 |
2 | 15 | 20 | 300 |
quando si costruisce l'espressione di un campo calcolato si possono usare anche delle funzioni matematiche come sin( ) cos( ) tang( )etc
9) il risultato di una query e' una tabella ( che nei casi più semplici può diventare una sola colonna oppure una sola riga oppure una sola cella) può essere salvata come una particolare tabella di cui possiamo scegliere il nome e riutilizzata per ulteriori interrogazioni
10) nel caso di ambiguità (piu tabelle con lo stesso nome del campo) per indicare un particolare campo si specifica il nomedellatabella.nomecampo ad esempio
studenti.Cognome
altre volte il nome del campo/tabella contiene degli spazi e allora va racchiuso fra parentesi quadre ad esempio [Nome Studente]
Selezione
modificaSpesso quando si ricerca qualcosa non si vogliono come risultato tutte le righe della tabella , si impone allora una o più condizioni che le righe devono soddisfare per ricadere nella soluzione, queste condizioni possono essere composte utilizzando gli operatori di confronto
< <= > >= <> diverso = uguale
e gli operatori logici
AND OR NOT
11) selezioniamo tutti gli studenti alti più di 175 e nati a Rovigo
select * from studenti where altezza>175 AND CittaNascita='Rovigo' ;
il where permette di decidere quali righe filtrare (selezionare, questa operazione di selezione diminusce la cardinalità e mantiene il grado) , quindi il select isola le colonne (ne riduce il grado) mentre il where ne riduce la cardinalità (numero di righe)
12) selezioniamo tutti gli studenti nati a Rovigo o a Padova
select * from studenti where CittaNascita='Rovigo' OR CittaNascita='Padova' ;
questo comando può essere scritto in modo diverso , dicendo che il valore del campo CittaNascita deve assumere uno dei valori specificati in un elenco tramite il comando IN
select * from studenti where CittaNascita IN ('Rovigo','Padova') ;
per specificare un particolare gruppo di valori che un campo deve avere, posso usare il comando LIKE e i simboli _ e % . Il simbolo _ significa un carattere qualsiasi (un solo carattere); il simbolo % significa una sequenza di caratteri qualsiasi di lunghezza qualsiasi (anche nulla).
Nel programma Microsoft Access si usano i simboli ? e * . Il simbolo ? sta per _ , invece il simbolo * sta per % .
se voglio tutti gli studenti il cui conome inizia con la lettera R posso allora scrivere
select * from studenti where cognome LIKE 'R%' ;
se voglio tutti gli studenti che hanno un cognome che termina in so scrivo allora
select * from studenti where cognome LIKE '%so' ;
se voglio tutti gli studenti che hanno un cognome che contiene la sequenza os scrivo allora
select * from studenti where cognome LIKE '%os%' ;
se voglio tutti gli studenti con un cognome di 5 caratteri di cui il primo carattere e' R
select * from studenti where cognome LIKE 'R____' ;
____ sono 4 underscore consecutivi
e cosi' via
13) se si vogliono ottenere tutti gli studenti la cui data di nascità e' compresa fra due particolari date si può utilizzare l'operatore BETWEEN ( che serve per specificare un intervallo di valori , da un valore minimo a un valore max)
selezionare tutti gli studenti nati fra il 1/1/1985 e il 25/12/1985
select * from studenti where DataNascita BETWEEN '01/01/1985' AND '25/12/1985' ;
oppure per selezionare tutti gli studenti con altezza compresa fra 178 cm e 185 cm possiamo scrivere
select * from studenti where Altezza BETWEEN 178 AND 185 ;
per lavorare con il tipo di dato date ci sono diverse funzioni per isolare anno , mese , giorno e per fare calcoli con le date ( per poi poter scrivere condizioni visualizzare tutte le fatture con data di pagamento che scadono entro 30 giorni, visualizzare le buste paga del mese di maggio etc)
funzione | descrizione |
now() | restituisce la data e ora corrente |
curdate() | restituisce la data corrente |
dateadd() | aggiuge a una data un certo numero di giorni |
datediff() | calcola il numero di giorni che intercorrono fra due date |
extract( ) | estrae da una data o dataora una specifica parte : anno mese giorno ora secondi |
datesub() | sottrae a una data un certo numero di giorni |
dateformat() | visualizza la data in un formato specifico |
il formato della data e' spesso il seguente ( ed e' fonte di errori se non lo si sa), ad esempio
tipo dato formato DATE YYYY-MM-DD DATETIME YYYY-MM-DD HH:MI:SS TIMESTAMP YYYY-MM-DD HH:MI:SS YEAR YYYY or YY
14 nelle condizioni di ricerca qualche volta si vuole trovare un campo che e' stato lasciato vuoto (lasciato vuoto non vuol dire con dei caratteri spazio o invio" per individuare questi campi si usa
is null oppure nel caso opposto is not null
select * from studenti where cognome is null ;
15) certe volte si vuole scrivere una condizione legata a un parametro scelto dall'utente nel momento dell'esecuzione della query
ad esempio trovare tutti gli studenti con altezza maggiore di un valore specificato dall'utente
select * from studenti where altezza > [specificare altezza] ;
quando eseguita . all'utente compare una finestra popup che gli domanda il valore della'altezza, il valore digitato dall'utente sarà utilizzato per eseguire la query al posto del parametro [specificare altezza]
16) se vogliamo estrarre solo un certo numero di righe ad esempio visualizzare i primi 3 studenti in ordine alfabetico utilizziamo la clausola TOP
select TOP 5 * from studenti order by Cognome
oppure selezionare (nome cognome eta) lo studente più alto
select TOP 1 nome,cognome,altezza from studenti order by Altezza DESC
Funzioni di aggregazione
modifica17) qualche volta si vuole calcolare il valor medio, max , minimo, o la somma dei valori numerici di una particolare colonna o contare il numero di righe estratte, per farlo si usano le funzioni di aggregazione ;
COUNT(nomecolonna) conta il numero di righe della tabella ottenuta AVG (nomecolonna) calcola il valor medio MAX (nomecolonna) calcola il valore massimo MIN (nomecolonna) calcola il valore minimo SUM (nomecolonna) calcola la somma dei valori di una certa colonna
Quando si vuole utilizzare una o più funzioni di aggregazione tutti gli elementi posti dopo il select devono essere funzioni di aggregazione
calcolare l'altezza massima degli studenti
select max(altezza) from studenti;
non si può scrivere
select nome, max(altezza) from studenti;
( per ottenere questo risultato bisogna procedere con una subquery spiegata successivamente o con un TOP 1 applicato su un elenco ordinato)
per calcolare l'altezza min media e max
select min(altezza) as altezzaminima ,avg(altezza) as altezzamedia, max(altezza) as altezzamassima from studenti;
per calcolare la somma di tutte le aree dei diversi rettangoli
select sum( base*altezza) as areatotale from rettangoli;
per contare quanti studenti sono nati a rovigo posso scrivere
select count(*) from studenti where CittaNascita = 'Rovigo';
in questo caso quando faccio il conteggio delle righe non ha importanza la particolare colonna a cui si riferisce e quindi si può utilizzare il simbolo * invece di specificare una particolare colonna, comunque
select count(cognome) from studenti where CittaNascita = 'Rovigo';
avrebbe restituito lo stesso risultato
se voglio calcolare quante sono le città di nascita (distinte, quindi conto Rovigo una sola volta ...)
select count(distinct(CittaNascita)) from studenti;
Congiunzione
modifica18) Diversamente da tutte le precedenti query spesso l'interrogazione coinvolge diverse tabelle ( spesso quelle di un legame 1:N) pensiamo di avere le seguenti tabelle
Tabella1
IDStudente | Nome |
1 | Marco |
2 | Luca |
3 | Anna |
4 | Eva |
e la tabella
Tabella2
CodiceStudente | Voto |
1 | 6 |
3 | 8 |
3 | 10 |
7 | 7 |
le due tabelle non sono 2 tabelle del tipo 1:N per evidenziare meglio le possibili congiunzioni.
Ora se scriviamo
select * from Tabella1,tabella2
si ottengono tutte le colonne e righe del prodotto fra le 2 tabelle ( il prodotto fra le 2 tabelle e' dato da tutte le possibili combinazioni delle righe della prima tabella con le righe della seconda) quindi si ottiene
IDStudente | Nome | CodiceStudente | Voto |
1 | Marco | 1 | 6 |
1 | Marco | 3 | 8 |
1 | Marco | 3 | 10 |
1 | Marco | 7 | 7 |
2 | Luca | 1 | 6 |
2 | Luca | 3 | 8 |
2 | Luca | 3 | 10 |
2 | Luca | 7 | 7 |
3 | Anna | 1 | 6 |
3 | Anna | 3 | 8 |
3 | Anna | 3 | 10 |
3 | Anna | 7 | 7 |
4 | Eva | 1 | 6 |
4 | Eva | 3 | 8 |
4 | Eva | 3 | 10 |
4 | Eva | 7 | 7 |
si vede che il prodotto non esprime nessun legame logico fra i campi della prima e della seconda tabella
se si vuole specificare un legame logico fra le due tabelle (di solito la corrispondenza fra chiave primaria e chiave esterna corrispondenti di due tabelle legate da un legame 1:N) si deve scrivere
select * from Tabella1,Tabella2 where Tabella1.IDStudente= Tabella2.CodiceStudente;
oppure
select * from tabella1 INNER JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente;
oppure
select * from tabella1 JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente;
in questo caso l'unione delle tabelle unisce fra loro solo le righe della prima e seconda tabella in cui i campi IDStudente e Codicestudente si corrispondono si ga allora un INNER-JOIN o semplice JOIN
IDStudente | Nome | CodiceStudente | Voto |
1 | Marco | 1 | 6 |
3 | Anna | 3 | 8 |
3 | Anna | 3 | 10 |
nella congiunzione della tabella abbiamo perso qualche studente perche' non c'era un corrispondente elemento nella 2 tabella
sono possibili anche altri tipi di congiunzione nel caso del LEFT-JOIN si collegano tutte le righe della prima tabella (left) con quelle corrispondenti della seconda e nel caso non ci fosse una riga corrispondente nella seconda tabella con una riga vuota, in questo modo siamo sicuri che tutte le righe della prima tabella compaiono
select * from tabella1 LEFT JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente
IDStudente | Nome | CodiceStudente | Voto |
1 | Marco | 1 | 6 |
2 | Luca | ||
3 | Anna | 3 | 8 |
3 | Anna | 3 | 10 |
4 | Eva |
analogamente nel caso di RIGHT-JOIN tutte le righe della seconda tabella si uniscono con quelle della prima e se non c'e' una corrispondente riga nella prima con una riga vuota
select * from tabella1 RIGHT JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente7
IDStudente | Nome | CodiceStudente | Voto |
1 | Marco | 1 | 6 |
3 | Anna | 3 | 8 |
3 | Anna | 3 | 10 |
7 |
esiste anche la possibilità di congiungere una tabella con se stessa collegando lo stesso campo o campi diversi in questo caso si parla di SELF-JOIN
esiste infine anche la possibilità di congiungere la tabella1 con la tabella2 facendo in modo che compaiano tutte le righe della prima e della seconda (se non c'e' una riga corrispondente viene aggiunta una riga vuota) in questo caso si parla di FULL-JOIN
select * from tabella1 FULL JOIN tabella2 ON tabella1.IDStudente= tabella2.CodiceStudente