Importante
A tradução é um esforço comunitário você pode contribuir. Esta página está atualmente traduzida em 76.60%.
15.4. Lesson: Queries
When you write a SELECT ... command it is commonly known as a query -
you are interrogating the database for information.
O objetivo dessa lição: Saber como criar consultas que retornam informações úteis.
Nota
If you did not do so in the previous lesson, add the following people
objects to your people table. If you receive any errors related to
foreign key constraints, you will need to add the ‘Main Road’ object to your
streets table first
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. Ordenando os resultados
Vamos recuperar uma lista de pessoas ordenadas pelos números de casa:
select name, house_no from people order by house_no;
Resultado:
name | house_no
--------------+----------
Joe Bloggs | 3
Roger Jones | 33
Jane Smith | 55
Sally Norman | 83
(4 rows)
Você pode classificar os resultados pelos valores de mais de uma coluna:
select name, house_no from people order by name, house_no;
Resultado:
name | house_no
--------------+----------
Jane Smith | 55
Joe Bloggs | 3
Roger Jones | 33
Sally Norman | 83
(4 rows)
15.4.2. filtragem
Muitas vezes, você não vai querer ver cada registro único na base de dados - especialmente se existem milhares de registros e você está interessado apenas em ver um ou dois.
Aqui está um exemplo de um filtro numérico que retorna apenas objetos cujo house_no é menor que 50:
select name, house_no from people where house_no < 50;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Você pode combinar filtros (definidos usando a cláusula ONDE) com classificação (definida usando a cláusula ORDENAR POR):
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)
Você também pode filtrar com base em dados de texto:
select name, house_no from people where name like '%s%';
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Here we used the LIKE clause to find all names with an s in them.
You’ll notice that this query is case-sensitive, so the Sally Norman entry
has not been returned.
If you want to search for a string of letters regardless of case, you can do a
case in-sensitive search using the ILIKE clause:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
That query returned every people object with an r or R in their
name.
15.4.3. Uniões
E se você quiser ver os detalhes da pessoa e o nome da rua em vez da identificação? Para fazer isso, você precisa unir as duas tabelas em uma única consulta. Vamos ver um exemplo:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Nota
Estando unidas, você sempre irá indicar as duas tabelas que as informações sejam provenientes, neste caso, as pessoas e as ruas. Você também precisa especificar quais duas chaves devem coincidir (chave estrangeira & chave primária). Se você não especificar isso, você terá uma lista de todas as combinações possíveis de pessoas e ruas, mas não há maneira de saber quem realmente vive em que rua!
A saída correta deveria ser assim:
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)
Vamos revisitar a união como criar consultas mais complexas mais tarde. Apenas lembre-se que eles fornecem uma maneira simples de combinar as informações a partir de duas ou mais tabelas.
15.4.4. Sub-Seleção
Sub-seleções permitem selecionar objetos de uma tabela com base nos dados de outra tabela que está ligada através de uma relação de chave estrangeira. No nosso caso, queremos encontrar pessoas que vivem em uma rua específica.
Mas antes, vamos fazer um pequeno ajuste em nossos dados:
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;
Vamos dar uma olhada rápida em nossos dados após essas alterações: podemos reutilizar nossa consulta da seção anterior:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Resultado:
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)
Now let’s show you a sub-selection on this data. We want to show only people who
live in street_id number 1:
select people.name
from people, (
select *
from streets
where id=1
) as streets_subset
where people.street_id = streets_subset.id;
Resultado:
name
--------------
Roger Jones
Sally Norman
(2 rows)
Embora este seja um exemplo muito simples e desnecessário com os nossos pequenos conjuntos de dados, ele ilusta como sub-seleções úteis e importantes pode ser ao consultar conjuntos de dados grandes e complexos.
15.4.5. Consultas agregadas
Um dos recursos poderosos de um banco de dados é a capacidade de resumir os dados em suas tabelas. Esses resumos são chamados de pesquisas agregadas. Aqui está um exemplo típico que nos diz quantas pessoas existem na nossa tabela de pessoas:
select count(*) from people;
Resultado:
count
-------
4
(1 row)
Se queremos que as contagens estejam resumidas pelo nome da rua, podemos fazer isso:
select count(name), street_id
from people
group by street_id;
Resultado:
count | street_id
-------+-----------
2 | 1
1 | 3
1 | 2
(3 rows)
Nota
Because we have not used an ORDER BY clause, the order of your
results may not match what is shown here.
Try Yourself: ★★☆
Resumir as pessoas pelo nome de rua e mostrar os nomes de ruas reais em vez das street_ids.
Responda
Aqui está a instrução SQL correta que você deve usar:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
Resultado:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
Você notará que temos nomes de campos prefixados com nomes de tabelas (por exemplo, pessoas.nome e ruas.nome). Isso precisa ser feito sempre que o nome do campo for ambíguo (ou seja, não exclusivo em todas as tabelas do banco de dados).
15.4.6. In Conclusion
Você já viu como utilizar as pequisas para retornar os dados em seu banco de dados de uma forma que lhe permite extrair informações úteis a partir dele.
15.4.7. What’s Next?
Em seguida, você verá como criar visualização com as consultas que você escreveu.