Query tramite SELECT (superiori)

lezione
lezione
Query tramite SELECT (superiori)
Tipo di risorsa Tipo: lezione
Materia di appartenenza Materia: Informatica (istituti tecnici) per le superiori
Avanzamento Avanzamento: lezione completa al 100%

Interrogare il database mediante il comando Select

modifica

Per 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 DataNascitaCittaNascitaAltezza
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 VotoTipoIDstudente
1 matematica 13/10/2014 7scritto 1
2 storia 23/10/2014 8scritto 3
3 matematica 13/11/2014 6orale 1
4 matematica 13/11/2014 7orale 4
5 geografia 15/5/2013 10orale 4
6 inglese 13/4/2013 2scritto 2
7 storia 3/10/2016 4scritto 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

modifica

1) 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

modifica

Spesso 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

modifica

17) 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

modifica

18) 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.CodiceStudente
7
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