• 20 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 03/09/2019

Interrogez votre base de données

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Ajouter des données c'est bien, mais pour quoi faire ensuite, si ce n'est les consulter ? Dans ce chapitre, je vais vous apprendre à interroger votre base de données. Non seulement vous saurez rechercher dans une table selon plusieurs critères, mais vous serez aussi capables de recouper des informations disponibles dans plusieurs tables et faire des calculs !

La documentation de PostgreSQL concernant les requêtes de type SELECT est disponible ici : http://docs.postgresql.fr/9.6/sql-select.html

Et toujours le MPD de gestion de tickets en support... :

MPD Gestion de tickets
MPD Gestion de tickets

Impatient·e ?

Alors c'est parti !

Recherchez dans une table

Allez, une première petite requête pour commencer.

Afficher l'id et le nom de tous les projets.

SELECT
    id, nom         -- <liste des colonnes à afficher>
FROM
    public.projet   -- <schéma>.<table>
;

Quelques explications sur la construction de la requête :

  1. SELECT

  2. suivi de la liste des informations à afficher (liste des colonnes)

  3. puis FROM

  4. la table contenant les données

Allégeons un peu l'écriture :

  • Si vous voulez afficher toutes les colonnes, vous pouvez utiliser * au lieu de lister l'ensemble des colonnes

  • Si la table est dans le schéma par défaut (public dans PostgreSQL) vous pouvez omettre le nom du schéma

Afficher le contenu complet de la table utilisateur

SELECT * FROM utilisateur;

Gérer les doublons

Les lignes en doublons sont remontées.

Si vous avez la table utilisateur suivante :

id

nom

prenom

3

Bracame

Édouard

4

Dalton

Joe

5

Dalton

William

6

Dalton

Jack

7

Dalton

Averell

La requête suivante remontera 5 lignes :

SELECT nom FROM utilisateur;

 

nom

 

Bracame

 

Dalton

 

Dalton

 

Dalton

 

Dalton

Vous pouvez supprimer les lignes en doublon grâce au mot clé DISTINCT :

SELECT DISTINCT nom FROM utilisateur;

Cette requête ne va plus retourner que 2 lignes :

 

nom

 

Bracame

 

Dalton

Ajoutez des critères de recherche

Ajoutons maintenant quelques critères pour filtrer notre sélection. On appelle aussi cela des conditions.

Afficher tous les utilisateurs portant le nom de « Dalton ».

SELECT *
FROM utilisateur
WHERE nom = 'Dalton';

Les critères sont précédés par le mot clé WHERE placé après le nom de la table. Il est possible de combiner plusieurs critères grâce aux mots clés AND (ET) et OR (OU). Vous pouvez inverser un critère avec le mot clé NOT et ajouter des parenthèses si besoin.

Afficher la liste des projets :

  • non gérés par Édouard Bracame,

  • ET :

    • clôturés

    • OU créés entre le 10 et le 30 janvier 2017 (inclus)

SELECT * FROM projet
WHERE
    (NOT responsable_id = 3)
AND (cloture = TRUE
        OR (date_creation >= '2017-01-10'
            AND date_creation < '2017-01-31')
    )
;

Dans la requête précédente, j'ai mis date_creation strictement inférieure au 31/01/2017 car il faut prendre en compte les heures. Si j'avais mis date_creation <= '2017-01-30', j'aurais eu comme borne supérieure le 30/01/2017 0h00 et donc un projet créé le 30/01/2017 à 13h00 ne serait pas remonté !

Opérateurs de conditions

Voici les principaux opérateurs permettant d'écrire des conditions :

Opérateurs

Définition

=

égal à

<> ou !=

différent de

<>

(strictement) inférieur à, supérieur à

<=>=

inférieur ou égal à, supérieur ou égal à

IS NULL

est NULL

IS NOT NULL

n'est pas NULL

LIKE

correspond au motif*

NOT LIKE

ne correspond pas au motif*

IN

dans la liste de valeurs

NOT IN

n'est pas dans la liste de valeurs

Opérateur LIKE

L'opérateur LIKE (et NOT LIKE) permet de faire une recherche par motif dans un texte :

  • _ correspond à 1 caractère

  • % correspond à une chaîne de 0 à plusieurs caractères

Voici le résultat des conditions de sélection avec les noms d'utilisateurs Bracame et Dalton :

SELECT ... WHERE

Résultat

nom LIKE 'Dal%'

Dalton

nom LIKE 'dal%'

 

nom LIKE '%a%'

Bracame, Dalton

nom LIKE '_a%'

Dalton

nom LIKE 'a'

 

nom LIKE 'Bracame'

Bracame

Opérateur IN

Les opérateurs IN et NOT IN permettent de vérifier si la valeur est présente ou non dans une liste :

-- Afficher les informations des projets 18 et 42
SELECT * FROM projet
WHERE id IN (18, 42);

-- Afficher les informations des projets autres que 18 et 42
SELECT * FROM projet
WHERE id NOT IN (18, 42);

Faites des opérations et appliquez des fonctions

Il existe des fonctions et des opérateurs standards pour réaliser certaines actions, en voici quelques exemples :

SQL

Définition

SQL

Résultat

nombre + nombre

somme de 2 nombres

40 + 2

42

chaîne || chaîne

concaténation de chaînes de caractères

'AB' || 'CD'

ABCD

lower(chaîne)

conversion de la chaîne en minuscule

lower(aBcD)

abcd

Il est possible de faire ces opérations aussi bien dans la liste des colonnes à afficher que dans les conditions :

SELECT
    nom || prenom
FROM utilisateur
WHERE lower(nom) LIKE `dal%`;

Ordonnez les valeurs

Il est possible d'ordonner les lignes de résultat à l'aide de la clause ORDER BY. Cette clause est à placer après les critères de recherche.

Afficher la liste des utilisateurs dont le nom contient un a triée :

  1. par nom croissant

  2. puis par prénom décroissant

SELECT * FROM utilisateur
WHERE lower(nom) LIKE '%a%'
ORDER BY nom ASC, prenom DESC;

id

nom

prenom

3

Bracame

Édouard

5

Dalton

William

4

Dalton

Joe

6

Dalton

Jack

7

Dalton

Averell

1

Ducable

Jean-Raoul

Suivez les clés étrangères dans vos critères de recherche

Vous pouvez définir des critères de recherche en croisant les valeurs avec celles contenues dans une autre table. Pour cela vous allez faire ce qu'on appelle une sous-requête.

Celle-ci sera mise entre parenthèses et vous utiliserez les opérateurs IN ou EXISTS.

Afficher la liste des projets gérés par les utilisateurs dont le nom est « Dalton ».

Voici comment faire avec l'opérateur IN :

-- le croisement se fait sur projet.responsable_id = utilisateur.id

SELECT * FROM projet
WHERE
    responsable_id IN (                              -- le croisement se fait ici
        SELECT utilisateur.id FROM utilisateur       -- et là
        WHERE utilisateur.nom = 'Dalton'
    )
;

Voici comment faire avec l'opérateur EXISTS :

-- le croisement se fait sur projet.responsable_id = utilisateur.id

SELECT * FROM projet
WHERE EXISTS (
        SELECT 1 FROM utilisateur
        WHERE utilisateur.id = projet.responsable_id   -- le croisement se fait ici
        AND utilisateur.nom = 'Dalton'
    )
;

Afficher la liste des tickets qui ne sont pas de bugs.

SELECT * FROM ticket
WHERE
    numero NOT IN (                      -- j'utilise ici NOT IN
        SELECT ticket_numero FROM bug
    )
;

Afficher la liste des projets n'ayant pas de tickets associés.

SELECT * FROM projet
WHERE NOT EXISTS (                           -- j'utilise ici NOT EXISTS
        SELECT 1 FROM ticket
        WHERE ticket.projet_id = projet.id
    )
;

Définissez des alias pour les tables

Il est possible de définir des alias pour les tables grâce au mot clé AS. Cela est nécessaire si une confusion est possible dans la sous-requête comme dans le cas des requêtes récursives par exemple.

Afficher la liste des projets dont le nom est le même que celui d'un autre projet.

SELECT *
FROM projet AS p1
WHERE EXISTS (
    SELECT 1 FROM projet AS p2
    WHERE p2.nom = p1.nom
    AND p2.id <> p1.id
);

Je suis obligé de mettre la condition p2.id <> p1.id dans la sous-requête car sinon il existera toujours un projet avec le même nom : précisément celui que je suis en train de tester !!

Fusionnez le contenu de plusieurs tables

Pour fusionner le contenu de plusieurs tables, il vous faut faire des jointures.

INNER JOIN

Afficher la liste des noms de projets gérés par les « Dalton » avec les informations du responsable associé.

SELECT projet.nom AS "Nom du projet", utilisateur.*
FROM projet
INNER JOIN utilisateur ON utilisateur.id = projet.responsable_id
WHERE utilisateur.nom = 'Dalton';

La clause INNER JOIN va réaliser un produit cartésien des tables. C'est-à-dire qu'à chaque ligne de la table projet sera associée toutes les lignes de la table utilisateur.

La clause ON va permettre de ne conserver que les lignes qui remplissent les conditions définies après ON. Ici, on ne conserve que les associations de lignes où la valeur de projet.responsable_id correspond à celle de utilisateur.id.

Sans le ON, on aurait par exemple ceci :

projet.id

projet.responsable_id

(utilisateur.id)

utilisateur.prenom

1

4

4

Joe

1

4

5

William

1

4

6

Jack

1

4

7

Averell

2

6

4

Joe

2

6

5

William

2

6

6

Jack

2

6

7

Averell

Avec ON utilisateur.id = projet.responsable_id, on obtient :

projet.id

projet.responsable_id

(utilisateur.id)

utilisateur.prenom

1

4

4

Joe

2

6

6

Jack

OUTER JOIN

Prenons un autre cas, légèrement différent, vous allez voir pourquoi. Soit le contenu des tables ticketevolution et bug suivant.

Table ticket :

numero

titre

1

Bug affichage facture

2

Ajouter une demande de confirmation de commande

3

Ajouter l'envoie d'un SMS à l'expédition

Table evolution :

ticket_numero

priorite

2

1

3

2

Table bug :

ticket_numero

niveau_bug_id

1

2

Afficher la liste de tous les tickets et s'il s'agit d'une évolution, la priorité de l'évolution.

SELECT ticket.*, evolution.priorite
FROM ticket
JOIN evolution ON evolution.ticket_numero = ticket.numero;

Cette requête va retourner :

numero

titre

...

priorite

2

Ajouter une demande de confirmation de commande

 

1

3

Ajouter l'envoie d'un SMS à l'expédition

 

2

Il manque le bug correspondant au ticket numéro 1 !

En effet ! La table evolution ne contient aucune ligne vérifiant la condition evolution.ticket_numero = ticket.numero  quand  ticket.numero  vaut  1. Le produit cartésien (entre les tables  ticket  et  evolution  ) filtré est donc vide pour ce numéro de ticket.

Pour remédier à cela, il ne faut pas faire une jointure INNER JOIN (ou simplement JOIN), mais une jointure OUTER JOIN et plus précisément une LEFT OUTER JOIN.

N'ayez pas peur, voici l'explication.

OUTER JOIN va permettre d'ajouter à un INNER JOIN toutes les lignes où il n'y a aucune correspondance remplissant les conditions du ON.

Et il faut préciser le sens :

  • table_a LEFT OUTER JOIN table_b ON ... :

    va ajouter toutes les lignes de table_a où il n'y a aucune correspondance dans table_b

  • table_a RIGHT OUTER JOIN table_b ON ... :

    va ajouter toutes les lignes de table_b où il n'y a aucune correspondance dans table_a

  • table_a FULL OUTER JOIN table_b ON ... va ajouter :

    • toutes les lignes de table_a où il n'y a aucune correspondance dans table_b

    • toutes les lignes de table_b où il n'y a aucune correspondance dans table_a

Ici, je veux ajouter toutes les lignes de la table ticket, où il n'y a aucune correspondance dans la table evolution.

J'ai donc besoin d'un LEFT OUTER JOIN, et je modifie ma requête en conséquence :

SELECT ticket.*, evolution.priorite
FROM ticket
LEFT JOIN evolution ON evolution.ticket_numero = ticket.numero;

Cette requête va retourner :

numero

titre

...

priorite

1

Bug affichage facture

 

NULL

2

Ajouter une demande de confirmation de commande

 

1

3

Ajouter l'envoi d'un SMS à l'expédition

 

2

Groupez les valeurs et faites des calculs

Ça va, vous tenez le coup ? Alors on continue.

Rassurez-vous, on arrive bientôt au bout ;-)

Il est possible de faire faire quelques calculs au SGBD.

Combien y a-t-il de tickets par projet non clôturé ?

Pour cela, il suffit regrouper les tickets par projet et de compter combien il y en a par groupe :

SELECT
    projet.id, projet.nom,
    COUNT(ticket.numero) AS "Nombre ticket"   -- comptage des tickets par groupe
FROM projet
JOIN ticket ON ticket.projet_id = projet.id
WHERE projet.cloture = FALSE
GROUP BY projet.id, projet.nom;               -- grouper par projet.id, projet.nom

Ce qui donnera un résultat de ce genre :

id

nom

Nombre ticket

1

Projet X

6

3

Projet Y

11

9

Projet Z

24

Vous pouvez même ajouter un filtre se basant sur le résultat d'un calcul sur le groupe :

SELECT
    projet.id, projet.nom,
    COUNT(ticket.numero) AS "Nombre ticket"
FROM projet
JOIN ticket ON ticket.projet_id = projet.id
WHERE projet.cloture = FALSE
GROUP BY projet.id, projet.nom
HAVING COUNT(ticket.numero) > 10;       -- Ne garder que les lignes avec count(...) > 10

Cette requête n'affiche dans le résultat que les projets qui ont plus de 10 tickets :

id

nom

Nombre ticket

3

Projet Y

11

9

Projet Z

24

Félicitations, vous êtes arrivé au bout !

C'était un gros morceau, pas vrai !?

Ce n'est pas sorcier, mais j'avoue, il faut pas mal pratiquer pour que ça rentre. Vous allez tranquillement digérer tout ça en vous exerçant.

Et n'hésitez pas à revenir régulièrement dans ce chapitre pour vous rafraîchir les idées ! ;)

Exemple de certificat de réussite
Exemple de certificat de réussite