Importante
La traduzione è uno sforzo comunitario you can join. Questa pagina è attualmente tradotta al 100.00%.
15.4. Lezione: Query
Quando scrivi un comando SELECT ..., questo viene comunemente noto come una query: stai interrogando il database per ottenere 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 errori relativi 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 interrogazione è case-sensitive, quindi la voce Sally Norman non è stata restituita.
Se vuoi cercare una stringa di lettere indipendentemente da maiuscole e minuscole, puoi fare una ricerca case-insensitive 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 interrogazione ha restituito tutti gli oggetti people con una r o una R nel 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 ti mostriamo una sotto-selezione di questi dati. Vogliamo mostrare solo le persone che vivono nello 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.
Prova Tu: ★★☆
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 Conclusione
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. Cosa Segue?
Poi vedrai come creare delle viste dalle query che hai scritto.