16.4. Lesson: Consultas

Quando você escreve um comando SELECT ... é comumente conhecido como uma consulta - você está interrogando o banco de dados para obter informações.

O objetivo dessa lição: Saber como criar consultas que retornam informações úteis.

Nota

Se você não o fez na lição anterior, adicione nos seguintes objetos pessoais para a sua tabela people. Se você receber erros relacionados a restrições de chave estrangeira, você vai precisar adicionar o objeto ‘Main Road’ para a sua mesa ruas primeira

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');

16.4.1. Ordenando os resultados

Let’s retrieve a list of people ordered by their house numbers:

select name, house_no from people order by house_no;

Result:

     name     | house_no
--------------+----------
 Joe Bloggs   |        3
 Roger Jones  |       33
 Jane Smith   |       55
 Sally Norman |       83
(4 rows)

You can sort the results by the values of more than one column:

select name, house_no from people order by name, house_no;

Result:

     name     | house_no
--------------+----------
 Jane Smith   |       55
 Joe Bloggs   |        3
 Roger Jones  |       33
 Sally Norman |       83
(4 rows)

16.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.

Here is an example of a numerical filter which only returns objects whose house_no is less than 50:

select name, house_no from people where house_no < 50;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

You can combine filters (defined using the WHERE clause) with sorting (defined using the ORDER BY clause):

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)

You can also filter based on text data:

select name, house_no from people where name like '%s%';

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

Aqui usamos a cláusula LIKE para encontrar todos os nomes com um s neles. Você notará que esta consulta é sensível ao caso, por isso a entrada Sally Norman não foi devolvida.

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.

16.4.3. Uniões

What if you want to see the person’s details and their street’s name instead of the ID? In order to do that, you need to join the two tables together in a single query. Lets look at an example:

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!

Here is what the correct output will look like:

     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.

16.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.

First, let’s do a little tweaking of our data:

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;

Let’s take a quick look at our data after those changes: we can reuse our query from the previous section:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

Result:

     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;

Result:

     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.

16.4.5. Consultas agregadas

One of the powerful features of a database is its ability to summarise the data in its tables. These summaries are called aggregate queries. Here is a typical example which tells us how many people objects are in our people table:

select count(*) from people;

Result:

 count
-------
     4
(1 row)

If we want the counts to be summarised by street name we can do this:

select count(name), street_id
from people
group by street_id;

Result:

 count | street_id
-------+-----------
     2 |         1
     1 |         3
     1 |         2
(3 rows)

Nota

Porque nós não usamos uma cláusula ORDER BY, a ordem de seus resultados podem não coincidir com o que é mostrado aqui.

16.4.5.1. Try Yourself moderate

Resumir as pessoas pelo nome de rua e mostrar os nomes de ruas reais em vez das street_ids.

Confira seus resultados

16.4.6. In Conclusion

Você já viu como utilizar as consultas para retornar os dados em seu banco de dados de uma forma que lhe permite extrair informações úteis a partir dele.

16.4.7. What’s Next?

Em seguida, você verá como criar visualização com as consultas que você escreveu.