Important

La traduction est le fruit d’un effort communautaire auquel vous pouvez vous joindre. Cette page est actuellement traduite à 85.11%.

15.4. Leçon : Requêtes

When you write a SELECT ... command it is commonly known as a query - you are interrogating the database for information.

Objectif de cette leçon : Apprendre comment créer des requêtes qui retourneront des informations utiles.

Note

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. Ordonner les résultats

Récupérons une liste de personnes triée par leur numéro de maison:

select name, house_no from people order by house_no;

Résultat :

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

Vous pouvez trier les résultats par les valeurs de plus d’une colonne :

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

Résultat :

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

15.4.2. Filtrage

Souvent, vous ne voudrez pas voir chaque enregistrement individuel dans la base de données - en particulier s’il y a des milliers d’enregistrements et que vous êtes seulement intéressés à en voir un ou deux.

Voici un exemple d’un filtre numérique qui retourne seulement des objets pour lesquels house_no est inférieur à 50 :

select name, house_no from people where house_no < 50;

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

Vous pouvez combiner les opérations de filtres (définies avec la clause WHERE ) avec les opérations de tri (définies en utilisant la clause 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)

Vous pouvez aussi filtrer sur les données du texte :

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

Et si vous voulez voir les détails de la personne et son nom de rue plutôt que l’ID ? Afin de faire cela, vous devez joindre le deux tables ensemble dans une seule requête. Regardons un exemple :

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

Note

Avec les jointures, vous devez toujours définir les deux tables d’où proviennent les informations, dans notre cas personnes et rues. Vous devez également spécifier quelle clé sert à la correspondance (clé étrangère et clé primaire). Si vous ne spécifiez pas cela, vous allez obtenir une liste de toutes les combinaisons possibles de personnes et de rues, mais il ne sera pas possible de savoir qui habite dans quelle rue!

Voici à quoi la bonne sortie devra ressembler :

     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)

Nous reverrons des jointures plus tard lorsque nous créerons des requêtes plus complexes. Souvenez-vous juste qu’elles permettent de combiner d’une façon simple l’information de deux ou plusieurs tables.

15.4.4. Sous-sélection

Des sous-sélections vous permettent de sélectionner des objets d’une table sur la base des données d’une autre table qui est liée via une relation de clé étrangère. Dans notre cas, nous voulons trouver des personnes qui vivent dans une rue spécifique.

Faisons tout d’abord un petit ajustement de nos données :

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;

Jetons rapidement un coup d’œil à nos données après ces changements; nous pouvons réutiliser notre requête de la section précédente :

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

Résultat :

     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;

Résultat :

     name
--------------
 Roger Jones
 Sally Norman
(2 rows)

Bien que ce soit un exemple très simple et inutile avec nos petits jeux de données, il illustre à quel point les sous-sélections peuvent être utiles et importantes lors de requêtes sur des jeux de données grands et complexes.

15.4.5. Requêtes agrégées

Une des fonctionnalités puissantes d’une base de données est sa capacité à résumer les données dans ses tables. Ces résumés sont appelés requêtes agrégées. Voici un exemple typique qui nous dit comment plusieurs objets « personne » sont dans notre table personne :

select count(*) from people;

Résultat :

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

Si nous voulons les totaux agrégés par nom de rue nous pouvons procéder ainsi:

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

Résultat :

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

Note

Because we have not used an ORDER BY clause, the order of your results may not match what is shown here.

★★☆ Essayez vous-même

Résumez les personnes par nom de rue et montrez les noms de rue actuel à la place de street_ids.

Réponse

Voici la syntaxe SQL correcte à utiliser

select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;

Résultat

count |    name
------+-------------
    1 | Low Street
    2 | High street
    1 | Main Road
(3 rows)

Vous noterez que nous avons préfixé les noms de champ du nom de la table (par exemple people.name ou streets.name). Ceci est nécessaire chaque fois que le nom de champ est ambigu (c’est-à-dire non unique au sein des tables de la base de données).

15.4.6. Conclusion

Vous avez vu comment utiliser des requêtes pour retourner les données dans votre base de données d’une façon qui vous permet d’y extraire de l’information utile.

15.4.7. La suite ?

Ensuite vous verrez comment créer des vues depuis les requêtes que vous avez écrites.