15.4. Lesson: Query
Quando scrivi un comando SELECT ... è comunemente conosciuto come una query - stai interrogando il database per informazioni.
Obiettivo di questa lezione: Imparare a creare query che restituiscano informazioni utili.
Nota
Se non l’hai fatto nella lezione precedente, aggiungi i seguenti oggetti people alla tua tabella people. Se ricevi qualche errore relativo ai vincoli di chiave esterna, dovrai prima aggiungere l’oggetto “Main Road” alla tua tabella streets
insert into people (name,house_no, street_id, phone_no)
values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
values ('Sally Norman',83,1,'072 932 31 32');
15.4.1. Riordinare i risultati
Otteniamo una lista di persone ordinate in base al loro numero civico:
select name, house_no from people order by house_no;
Risultato:
name | house_no
--------------+----------
Joe Bloggs | 3
Roger Jones | 33
Jane Smith | 55
Sally Norman | 83
(4 rows)
Puoi ordinare i risultati per i valori di più di una colonna:
select name, house_no from people order by name, house_no;
Risultato:
name | house_no
--------------+----------
Jane Smith | 55
Joe Bloggs | 3
Roger Jones | 33
Sally Norman | 83
(4 rows)
15.4.2. Attivare filtri
Spesso non vuoi vedere ogni singolo record nel database - specialmente se ci sono migliaia di record e sei interessato a vederne solo uno o due.
Ecco un esempio di un filtro numerico che restituisce solo gli oggetti il cui house_no è inferiore a 50:
select name, house_no from people where house_no < 50;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Puoi combinare i filtri (definiti usando la clausola WHERE) con l’ordinamento (definito usando la clausola ORDER BY):
select name, house_no from people where house_no < 50 order by house_no;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Puoi anche filtrare in base ai dati di tipo testuale:
select name, house_no from people where name like '%s%';
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Qui abbiamo usato la clausola LIKE per trovare tutti i nomi che contengono una s. Noterai che questa query è case-sensitive, quindi la voce Sally Norman non è stata restituita.
Se vuoi cercare per una stringa di lettere indipendentemente dal maiuscolo/minuscolo, puoi fare una ricerca case in-sensitive usando la clausola ILIKE:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
Questa query ha restituito ogni oggetto people con un r o R nel loro nome.
15.4.3. Join
E se vuoi vedere i dettagli della persona e il nome della sua strada invece dell’ID? Per farlo, devi unire le due tabelle in un’unica query. Vediamo un esempio:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Nota
Con i join, dovrai indicare sempre le due tabelle da cui provengono le informazioni, in questo caso people e streets. Devi anche specificare quali due chiavi devono corrispondere (chiave esterna e chiave primaria). Se non lo specifichi, otterrai una lista di tutte le possibili combinazioni di persone e strade, ma nessun modo di sapere chi vive effettivamente in quale strada!
Ecco come sarà l’output corretto:
name | house_no | name
--------------+----------+-------------
Joe Bloggs | 3 | Low Street
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
(4 rows)
Rivisiteremo le join quando creeremo query più complesse in seguito. Basta ricordare che forniscono un modo semplice per combinare le informazioni di due o più tabelle.
15.4.4. Sub-Select
Le sottoselezioni ti permettono di selezionare oggetti da una tabella in base ai dati di un’altra tabella che è collegata tramite una relazione di chiave esterna. Nel nostro caso, vogliamo trovare le persone che vivono in una specifica strada.
Per prima cosa, facciamo una piccola modifica ai nostri dati:
insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;
Diamo una rapida occhiata ai nostri dati dopo queste modifiche: possiamo riutilizzare la nostra query dalla sezione precedente:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Risultato:
name | house_no | name
--------------+----------+-------------
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
Joe Bloggs | 3 | Low Street
(4 rows)
Ora mostriamo una sotto-selezione su questi dati. Vogliamo mostrare solo le persone che vivono in street_id numero 1:
select people.name
from people, (
select *
from streets
where id=1
) as streets_subset
where people.street_id = streets_subset.id;
Risultato:
name
--------------
Roger Jones
Sally Norman
(2 rows)
Anche se questo è un esempio molto semplice e non necessario con i nostri piccoli insiemi di dati, illustra quanto utili e importanti possano essere le sotto-selezioni quando si interrogano insiemi di dati grandi e complessi.
15.4.5. Query di aggregazione
Una delle potenti caratteristiche di un database è la sua capacità di riassumere i dati delle sue tabelle. Queste sintesi sono chiamate query aggregate. Ecco un tipico esempio che ci dice quanti oggetti persone ci sono nella nostra tabella persone:
select count(*) from people;
Risultato:
count
-------
4
(1 row)
Se vuoi che i conteggi siano riepilogati per nome di strada, puoi fare così:
select count(name), street_id
from people
group by street_id;
Risultato:
count | street_id
-------+-----------
2 | 1
1 | 3
1 | 2
(3 rows)
Nota
Poiché non abbiamo usato una clausola ORDER BY, l’ordine dei tuoi risultati potrebbe non corrispondere a quello mostrato qui.
Try Yourself 
Riepiloga le persone per nome di strada e mostra i nomi reali delle strade invece degli street_id.
Soluzione
Ecco l’istruzione SQL corretta che dovreste usare:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
Risultato:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
Noterai che abbiamo anteposto i nomi dei campi ai nomi delle tabelle (per esempio people.name e streets.name). Questo deve essere fatto ogni volta che il nome del campo è ambiguo (cioè non è univoco in tutte le tabelle del database).
15.4.6. In Conclusion
Hai visto come usare le query per restituire i dati nel tuo database in un modo che ti permette di estrarre informazioni utili da esso.
15.4.7. What’s Next?
Poi vedrai come creare delle viste dalle query che hai scritto.