Le SQL pour le nuls

Bases de SQL

Afin de vous accompagner dans l’écriture de requêtes simples, voici quelques basiques. Vous pouvez également vous tourner vers internet qui est une mine d’apprentissage.
Quelques sites :

Conventions

Le langage SQL ne tient pas compte de la casse pour ce qui est de ses commandes et de ses fonctions. Mais, par convention, on écrit au moins les commandes en majuscules pour bien se retrouver dans le code.
Par contre, pour le nom des tables et des champs, la casse compte. De ce fait, il est plutôt recommandé de tout mettre en mincuscule. Inutile de dire que les caractères non génériques ne sont pas pris en charge (accents, espaces, caractères spéciaux...). Seule le caractère underscore ( _ ) est autorisé.
Si malgré ces conventions de prudence le concepteur d’une table vous fait la blague de mettre des majuscules, il vous faudra déclarer le nom des tables ou des champs concernés entre guillemets : "leChampPas_Bien".

Par ailleurs, le SQL n’interprète pas les sauts à la ligne et les espaces multiples, vous êtes donc libre d’organiser le code comme bon vous semble, à condition de bien mettre au minimum un espace entre chaque élément de commande.

SELECT

Le but d’une base, c’est de trouver des informations, de les sélectionner, depuis quelque part, une provenance, c’est donc la commandes la plus utilisé.

SELECT champ1, champ2 FROM ma_table ;

Au niveau du choix des champs, si vous ne les voulez pas tous, il vous faudra citer tous les champs désirés. La solution de simplicité c’est de tout prendre, çà peut s’écrire de deux façons :

SELECT * FROM ma_table ;
SELECT ma_table.* FROM ma_table ;

La petite subtilité de la deuxième écriture, c’est que dans le cas ou vous avez plusieurs tables sources, vous précisez ici tous les champs d’une table. Ce qui peut donner :

SELECT ma_table1.*, ma_table2.champ4 FROM ma_table1,ma_table2

(cette syntaxe n’est pas suffisante car il manque une clause de relation entre les deux tables, nous présentons ainsi à but pédagogique)

L’écriture du nom de la table dans un contexte postgresql oblige à plus de précisions. Postgresql permet de créer l’équivalent d’une structure arborescente de dossier. Dans une base, on peut créer un premier niveau de dossier qu’on appelle des "schémas", dans lesquelles on place des tables (sous-dossier) eux-mêmes composés de champs, qu’on pourrait qualifier de fichiers pour finir l’analogie. Du coup la syntaxe devient :

SELECT * FROM mon_schema.ma_table1
Exemple : SELECT * FROM inventaire.vum_faune

Critère de filtre=clause WHERE

Sélectionner c’est bien, mais sans critères de filtrage, çà va peut faire du monde !
La commande permettant d’exprimer des conditions, c’est WHERE.

SELECT * FROM ma_table1 WHERE champ1=’toto’

WHERE prend à minima un nom de champ, un opérateur, une valeur.
Les opérateurs courant sont "=", ">", "<", " !=" (différent de).
Les valeurs s’expriment de la façon suivante :

  • une chaine de caractère : ’toto1155*fdf !’ (le caractère apostrophe ’ est à bien saisir), là vous pouvez mettre des accents,caractères spéciaux, etc... Attention à un seul caractère, l’apostrophe justement qu’il vous faudra doubler.
  • un numerique : 546.46 (ici pas d’apostrophe, le séparateur décimal est le point)

En plus de ces opérateurs courants, il existe des opérateurs que nous pouvons qualifier de flou ou multiple.

  • 1er opérateur : LIKE

    SELECT * FROM ma_table2 WHERE champ6 LIKE ’le debut et%’

    On va indiquer une portion de la chaîne de caractère. Le caractère spéciale ’%’ remplace n’importe quel caractère (y compris spécial ou des espaces), et mêmes plusieurs caractères. Si l’on veut les valeurs qui contiennent un mot n’importe où dans les valeurs on va écrire :

    SELECT * FROM ma_table2 WHERE champ6 LIKE ’%le mot%’

    Attention, LIKE respecte la casse. Pour passer outre, utilisez l’opérateur suivant.

  • 2eme opérateur : ILIKE
    Identique à like, à ceci près qu’il se moque de la casse.
  • 3eme opérateur : IN
    Cet opérateur va vous permettre de définir une liste de valeurs possibles, mais des valeurs complètes (pas une portion comme dans like).

    SELECT * FROM ma_table2 WHERE champ6 IN (’valeur1, ’valeur2’, ’valeur3’)

Cas particulier de l’absence de valeur, qu’on appelle chaine vide. La requête va se faire de la façon suivante :

SELECT * FROM ma_table1 WHERE champ2 IS NULL
SELECT * FROM ma_table1 WHERE champ2 IS NOT NULL

Dans la première requête on recherche les lignes pour lesquelles le champ2 est vide, dans la deuxième ligne, on cherche celles pour lesquelles le champ a une valeur. Attention, un espace (’ ’) ou une chaine vide (plusieurs espaces) est une valeur !

Valeurs différentes=DISTINCT

Des fois, en aimerait bien savoir quelles sont les différentes valeurs possibles pour un champ afin de mettre en place des requêtes. Pour cela on peut utiliser la commande DISTINCT :

SELECT DISTINCT(champ1) FROM mon_schema.ma_table1

On peut même faire le travail sur deux (ou plus) champs pour obtenirs des couples différents :

SELECT DISTINCT(champ1, champ2) as les_couples FROM mon_schema.ma_table1

Notez ici qu’on a ajouté quelque chose, on a nommé le champ résultat, car sinon par défaut il s’appelle ’ROW’. Ce qu’on vient de faire s’appele un alias (un surnom quelque part).

Regroupement dans le cas du DISTINCT
Ce qui peut être intéressant en plus d’avoir les valeurs distinctes, c’est de compter combien de fois elle apparaît. Pour cela on va utiliser deux choses, la commande de regroupement GROUP BY et la fonction d’agrégation COUNT (autrement dit compter). On écrit :

SELECT DISTINCT(champ1), count(champ1) FROM mon_schema.ma_table1
GROUP BY champ1

Renommer un objet dans la requête=Alias

Il est parfois pratique de ne pas écrire le nom entier des tables, surtout quand on doit spécifier le schéma. Aussi est-il possible de faire appel une seule fois au nom entier puis de déclarer un "surnom" nommé alias en SQL.

SELECT * FROM mon_schema.ma_table1 t
WHERE t.champ2 > 100

Une autre situation ou on utilise les alias, c’est quand on enchaine plusieurs requetes

SELECT req.champ3
FROM ( SELECT * FROM ma_table1 WHERE champ2=’toto’) req
WHERE champ3 IS NOT NULL

Dans cet exemple, une première requête est lancé pour prendre uniquement les enregistrements de la table dont la champ2 vaut ’toto’. On nomme le résultat de cette première requête "req". Puis on récupère la valeur du champ3 selon une condition, des enregistrements précédemment sélectionnés.

Jointure

Une base de données est constituée de nombreuses tables séparant les objets en entités distinctes et uniques. Le but est d’éviter la redondance d’information. Pour pouvoir exploiter les données, il faut recréer les liens entre les tables à l’aide de jointure.
Par exemple, il peut être intéressant pour une liste d’espèce de trouver leurs statuts de protection qui sont situés dans une table protections.
Toutes les jointures sont basées sur les champs contenant des valeurs uniques (obligatoirement sans doublon), on les appelles clés primaires. Par exemple, le code INSEE d’une commune, ou le "code_site" d’un site Cen.
La syntaxe à retenir est la suivante :

SELECT table1.*, table2.*
FROM table1
JOIN table2 ON table1.cle1=table2.ref_cle1

La jointure se fait entre le champ de clé primaire de la table1 et un champ de la table2 qui stocke la référence à cette clé primaire. Dans cette table2, la valeur de "ref_cle1" peut ne pas être unique, c’est une référence à la table1. Par contre, le champ "cle2" de la table2 a des valeurs uniques. Il ne faut pas confondre un champ de référence à une clé primaire d’une autre table (que l’on appelle clé étrangère) avec la clé primaire de la table2.
Il existe différente type de jointure, retenez au moins ces deux là :

  • JOIN : le résultat prendra uniquement les éléments des deux tables qui correspondent au critère de jointure, les autres ne sont pas dans le résultat,
  • LEFT JOIN : tous les enregistrements de la première table sont conservés, même s’il ne sont pas concernés par la jointure. Très utile quand on multiplie les jointures avec différentes tables qui ne correspondent pas à tous les enregistrements de la table principale de la requête.

Voici un exemple pédagogique de jointure.

Sous-requête=CTE(Common Table Expression)

Quand les requêtes deviennent complexes, plutôt que d’emboiter des sous-requête, vous pouvez faire appel aux CTE (Common Table Expression). Le principe est proche d’une notion de requête enregistrée.

WITH mon_ensemble as
(
SELECT champ1, ref_cle_site FROM ma_table_ensemble
WHERE ref_cle_site LIKE ’28%’
)

SELECT table_site.*, mon_ensemble.champ1 FROM table_site
JOIN mon_ensemble ON mon_ensemble.ref_cle_site=table_site.code_site

Aggrégation, commande GROUP BY

Pour les adeptes de tableau croisé dynamique sous Excel, voici l’outil équivalent en base de données, les fonctions d’aggrégation. La syntaxe de base :

SELECT champ1, count(champ2) FROM ma_table1 GROUP BY champ1

En voici un très bonne exemple :
https://www.1keydata.com/fr/sql/sql-group-by.php
Dans l’exemple ci-dessus, on utilise la fonction d’aggégation SUM. La liste de ces fonctions disponibles dans PostgreSQL se trouve sur cette page.