Les requêtes
INSERT – Ajouter de la donnée
Insérer des données simples
INSERT INTO nom_schema.ma_table ( colonne_1, colonne_3, colonne_4, colonne_7 ) -- On indique les colonnes dans lesquels on va ajouter de la donnée VALUES ( 'valeur1', 'valeurA', 'valeurX', 'autre valeur' ) -- On renseigne les valeurs (on peut utiliser des fonctions) dans l'ordre des colonnes ;
Insérer plusieurs données en même temps
INSERT INTO mon_schema.ma_table ( colonne_1, colonne_3, colonne_4, colonne_7 ) VALUES ('valeur1', 'valeurA', 'valeurX', 'autre valeur'), ('valeur2', 'valeurB', 'valeurY', 'autre valeur'), ('valeur3', 'valeurC', 'valeurZ', 'autre valeur') -- Chaque jeu de données est séparé par une virgule ;
Insérer des données depuis une autre table
INSERT INTO mon_schema.ma_table ( colonne_1, colonne_3, colonne_4, colonne_7, colonne_8 ) SELECT -- SELECT sur la table d'origine colonne_3, colonne_2, colonne_6, colonne_6, -- On importe 2x la même colonne dans deux colonnes différentes mafonction(colonne_6) -- On peut utiliser des fonctions pour transformer la donnée source FROM mon_schema.ma_table_initiale ;
SELECT – affichez des données
La base
Faire un simple SELECT : afficher les données
SELECT -- SELECT = sélection des données à afficher 'mon texte', -- Les textes sont entre guillemets simples '23-10-2019', -- Les dates sont entre guillemets simples 22, -- Les chiffres sont écrit sans guillemets 12.386, -- Le séparateur des décimales est le point TRUE -- Les valeurs booléennes sont écrits sans guillemets null -- Les valeurs nulles sont écrit sans guillemets
Afficher les données d’une table
SELECT -- On choisit les colonnes dont on veut les données ("*" pour sélectionner toutes les colonnes) colonne_1, -- Il suffit de nommer la colonne que l'on souhaite utiliser "Colonne_2", -- Si une colonne (ou tout autre objet) contient des majuscules il faut utiliser des guillemets doubles "Colonne 3" -- Si une colonne (ou tout autre objet) contient des caractères spéciaux il faut utiliser des guillemets doubles FROM -- FROM = désignation de la table dont on veut afficher les données mon_schema.ma_table ;
Nommer les objets
SELECT ma_table.colonne_1, -- On spécifie à partir de quelle table on veut la colonne -- En réalité, PostgreSQL le fait implicitement mais cela peut être utile avec des jointures t1.colonne_2, -- On peut spécifier le nouveau nom de la table (pour rendre les requêtes plus lisible) colonne_3 as nouveau_nom_3, -- Le "as" permet de redéfinir le nom de la colonne colonne_4 as "Nouveau nom 4" -- On utilise des guillemets car on a des majuscules et des espaces (des caractères spéciaux) FROM mon_schema.ma_table as t1 -- Le "as" permet également de définir un nouveau nom pour une table ;
Afficher les données d’une table virtuelle :
SELECT id, nom, mois, salaire FROM (VALUES -- On utilise VALUES puis on définit les valeurs (1,'arthur',1000), (2,'alfred',1400), (3,'juliette',1100), (4,'albert',1230) ) AS t1 (id,nom,salaire) -- On spécifie un nom de table puis le nom de chaque colonne dans l'ordre des valeurs
WHERE
Afficher des données filtrées
SELECT colonne_1, colonne_2, colonne_3 FROM mon_schema.ma_table WHERE -- On applique un filtre colonne_2 = 'ma valeur' ;
Afficher des données avec plusieurs filtres
SELECT colonne_1, colonne_2, colonne_3 FROM mon_schema.ma_table WHERE -- On applique un filtre colonne_2 > 10 AND -- On applique un deuxième filtre : AND = ET = la condition doit également être satisfaite colonne_2 < 20 AND -- On applique un troisième filtre ( -- On ouvre une parenthèse : on compare ce qui est dedans colonne_1 = 'valeur 1' OR -- On compare la condition précédent avec la suivante colonne_1 = 'valeur 2' ) ;
Filtrer les valeurs nulles
SELECT colonne_1, colonne_2, colonne_3 FROM mon_schema.ma_table WHERE colonne_2 IS NOT NULL -- NULL représente l’absence de valeur, les opérateurs classiques ne fonctionnent donc pas ;
LIMIT
Limiter le nombre de résultats
SELECT colonne_1, colonne_2, colonne_3 FROM mon_schema.ma_table LIMIT -- On limite à 3 résultats 3 ;
Limiter le nombre de résultats de façon focalisé
SELECT colonne_1, colonne_2, colonne_3 FROM mon_schema.ma_table LIMIT -- On limite à 3 résultats 3 OFFSET -- Mais on passe 22 lignes avant de renvoyer les données 22 ;
GROUP BY
Regrouper les données
SELECT colonne_1, -- Le regroupement s'effectue sur cette colonne, nous aurons donc des valeurs unique ici count(id), -- Il peut y avoir plusieurs valeur pour la colonne "id" puisqu'il s'agit d'un regroupement. Il faut donc obligatoirement un opérateur de regroupement sum(surface), -- Ici aussi on utilise un opérateur de regroupement min(surface), max(surface) FROM mon_schema.ma_table GROUP BY -- On regroupe selon un colonne (on peut utiliser plusieurs colonnes séparées par des virgules) colonne_1 ;
Filtrer les données puis les regrouper
SELECT colonne_1, count(id) as "Nombre d'objet", sum(surface), min(surface), max(surface) FROM mon_schema.ma_table WHERE surface > 150 GROUP BY colonne_de_regroupement ;
Regrouper les données puis les filtrer
SELECT colonne_1, count(id) as "Nombre d'objet", sum(surface), min(surface), max(surface) FROM mon_schema.ma_table GROUP BY colonne_de_regroupement HAVING -- On filtre les données groupées sum(surface) > 150 ;
ORDER BY
Trier les données
SELECT colonne_1 FROM mon_schema.ma_table ORDER BY -- On tri les données colonne_de_tri ASC -- On choisi la colonne et l'ordre de tri : ascendant (DESC pour descendant) ;
UPDATE – Modifier la donnée
Modifier la valeur d’une colonne entière
UPDATE mon_schema.ma_table SET ma_colonne = 'valeur' ;
Modifier la valeur d’un champ pour une sélection de ligne
UPDATE mon_schema.ma_table SET ma_colonne = 'valeur' WHERE ma_condition ; -- Il est possible de se baser sur une autre table : UPDATE mon_schema.ma_table t1 SET mon_champ = 'ma_valeur' WHERE t1.champ_id IN ( SELECT t2.champ_id FROM mon_schema.ma_table_2 t2 WHERE t2.mon_champ_y IS NOT NULL ) ;
Modifier la valeur d’un champ depuis une autre table
UPDATE mon_schema.ma_table as t1 SET ma_colonne = t2.une_colonne FROM mon_schema.ma_table_2 as t2 WHERE t1.id = t2.id ; -- Variante avec concaténation des valeurs s'il existe plusieurs correspondances UPDATE mon_schema.ma_table as t1 SET ma_colonne = t2.une_colonne_concatenee FROM ( SELECT string_agg(une_colonne, ' - ') as une_colonne_concatenee FROM mon_schema.ma_table_2 GROUP BY id ) as t2 WHERE t1.id = t2.id ;
Il est possible de mettre à jour plusieurs colonne d’un seul coup
UPDATE mon_schema.ma_table SET ma_colonne_1 = 'valeur' ma_colonne_2 = 'valeur' ; -- Ou encore à partir d'une autre table UPDATE mon_schema.ma_table as t1 SET ma_colonne_1 = t2.une_colonne, ma_colonne_2 = t2.une_autre_colonne, FROM mon_schema.ma_table_2 as t2 WHERE t1.id = t2.id ;
Par défaut, PostgreSQL retourne le nombre de lignes mises à jour. Vous pouvez spécifier les colonnes à retourner :
UPDATE mon_schema.ma_table SET ma_colonne = 'valeur' WHERE ma_condition RETURNING ma_colonne, une_autre_colonne ;
DELETE – Supprimer la donnée
Supprimer une ligne
DELETE FROM mon_schema.ma_table WHERE -- On applique une condition pour ne supprimer qu'une (ou plusieurs) ligne(s) ma_condition ;
Supprimer une ligne en renvoyant une valeur
DELETE FROM mon_schema.ma_table WHERE ma_condition RETURNING count() -- On renvoi le nombre d'élément supprimé ;
Suppression de toutes le données d’une table
DELETE FROM mon_schema.ma_table ; -- OU TRUNCATE mon_schema.ma_table -- TRUNCATE est une fonction spécifique à PostgreSQL, plus rapide que le DELETE ;
Quelques requêtes complexes
Les jointures
Le but des jointures est de lier une table avec une ou plusieurs autre(s). Attention, les jointures peuvent rapidement être gourmandes en ressource (surtout les jointures géométriques).
Comme dans les requêtes précédentes, vous pouvez choisir les colonnes à afficher ou utiliser le sélecteur ‘*’ pour tout récupérer.
Voici les tables qui vont servir d’exemple
-- Table enfants id | nom -----+--------- 0 | juliette 1 | arthur 2 | alfred -- Table jouets id | objet -----+------- 1 | ski 2 | ballon 3 | arc
CROSS JOIN
On récupère toutes les combinaisons possibles
SELECT * FROM mon_schema.enfants CROSS JOIN mon_schema.jouets ; -- OU SELECT * FROM mon_schema.enfants, mon_schema.jouets ; --> Résultat id | nom | id | objet -----+----------+-----+------- 0 | juliette | 1 | ski 1 | arthur | 1 | ski 2 | alfred | 1 | ski 0 | juliette | 2 | ballon 1 | arthur | 2 | ballon 2 | alfred | 2 | ballon 0 | juliette | 3 | arc 1 | arthur | 3 | arc 2 | alfred | 3 | arc
INNER JOIN
On ne récupère que les correspondances
SELECT * FROM mon_schema.enfants as t1 INNER JOIN mon_schema.jouets as t2 ON t1.id=t2.id ; -- OU SELECT * FROM mon_schema.enfants as t1, mon_schema.jouets as t2 WHERE t1.id=t2.id ; --> Résultat id | nom | id | objet -----+---------+-----+------- 1 | arthur | 1 | ski 2 | alfred | 2 | ballon
LEFT OUTER JOIN
On récupère tous les enregistrements de la table de gauche ainsi que ceux de la table de droite qui correspondent.
SELECT * FROM mon_schema.enfants as t1 LEFT OUTER JOIN mon_schema.jouets as t2 ON t1.id=t2.id ; -- OU SELECT * FROM mon_schema.enfants as t1 LEFT JOIN mon_schema.jouets as t2 ON t1.id=t2.id ; -- OU SELECT * FROM mon_schema.enfants LEFT JOIN mon_schema.jouets USING (id) ; --> Résultat id | nom | id | objet -----+-----------+-----+------- 0 | juliette | | -- Aucune valeur ne correspond dans la table "jouets" 1 | arthur | 1 | ski 2 | alfred | 2 | ballon
RIGHT OUTER JOIN
On récupère tous les enregistrements de la table de droite ainsi que ceux de la table de gauche qui correspondent.
SELECT * FROM mon_schema.enfants as t1 RIGHT OUTER JOIN mon_schema.jouets as t2 ON t1.id=t2.id ; -- OU SELECT * FROM mon_schema.enfants as t1 RIGHT JOIN mon_schema.jouets as t2 ON t1.id=t2.id ; -- OU SELECT * FROM mon_schema.enfants RIGHT JOIN mon_schema.jouets USING (id) ; --> Résultat id | nom | id | objet -----+--------+-----+------- 1 | arthur | 1 | ski 2 | alfred | 2 | ballon | | 3 | arc
FULL OUTER JOIN
On récupère tous les enregistrements en conservant la correspondance.
SELECT * FROM mon_schema.enfants as t1 FULL OUTER JOIN mon_schema.jouets as t2 ON t1.id=t2.id ; -- OU SELECT * FROM mon_schema.enfants as t1 FULL JOIN mon_schema.jouets as t2 ON t1.id=t2.id ; -- OU SELECT * FROM mon_schema.enfants FULL JOIN mon_schema.jouets USING (id) ; --> Résultat id | nom | id | objet -----+----------+-----+------- 0 | juliette | | 1 | arthur | 1 | ski 2 | alfred | 2 | ballon | | 3 | arc
NATURAL JOIN
Il s’agit d’une jointure implicite sur les champs possédants le même nom dans chaque table. Plusieurs variantes existent :
- NATURAL JOIN = INNER JOIN
- NATURAL FULL JOIN = FULL JOIN
- NATURAL LEFT JOIN = LEFT OUTER JOIN
SELECT * FROM mon_schema.enfants as t1 NATURAL JOIN mon_schema.jouets as t2 ; --> Résultat id | nom | id | objet -----+---------+-----+------- 1 | arthur | 1 | ski 2 | alfred | 2 | ballon
SELF JOIN
Jointure sur soi-même : on ressort les correspondances avec soi-même
SELECT * FROM mon_schema.enfants as t1 INNER JOIN mon_schema.enfants as t2 ON t1.id=t2.id ; --> Résultat id | nom | id | objet -----+-----------+-----+------- 0 | juliette | 0 | juliette 1 | arthur | 1 | arthur 2 | alfred | 2 | alfred
Union
Il s’agit d’une jointure particulière : le but n’est pas de joindre des colonnes mais plutôt des lignes.
Le principe est simple, on colle deux requêtes l’une à la suite de l’autre avec le mot clé UNION
, leurs résultat se mettent à la suite les uns des autres.
Attention, les colonnes doivent avoir le même nom et le même type.
SELECT 1 as nb, 'mon texte' as txt UNION SELECT 12 as nb, 'autre texte' as txt ;
Cette syntaxe supprime automatiquement les doublons, essayez ceci :
SELECT 12 as nb, 'autre texte' as txt UNION SELECT 12 as nb, 'autre texte' as txt ;
En ajoutant ALL, vous récupérer ces valeurs en doublon :
SELECT 12 as nb, 'autre texte' as txt UNION ALL SELECT 12 as nb, 'autre texte' as txt ;
Gérer les doublons
Petit cas particulier : si vous avez besoin de retrouver des doublons
SELECT count(mon_champ_id) as "Nombre", string_agg(mon_champ_id::text, ',') as "Identifiants" FROM mon_schema.ma_table GROUP BY mon_champ_a_comparer_1, mon_champ_a_comparer_2, mon_champ_a_comparer_3 ; -- Variante n'affichant que les doublons (et plus) SELECT count(mon_champ_id) as "Nombre", string_agg(mon_champ_id::text, ',') as "Identifiants" FROM mon_schema.ma_table GROUP BY mon_champ_a_comparer_1, mon_champ_a_comparer_2, mon_champ_a_comparer_3 HAVING count(mon_champ_id) > 1 ; -- Variante affichant les objets, le nombre de doublon ainsi que les ids doublons SELECT t1.mon_champ_id, t2."Nombre", t2."Identifiants" FROM mon_schema.ma_table as t1, (SELECT count(mon_champ_id) as "Nombre", string_agg(mon_champ_id::text, ',') as "Identifiants" FROM mon_schema.ma_table GROUP BY mon_champ_a_comparer_1, mon_champ_a_comparer_2, mon_champ_a_comparer_3 HAVING count(mon_champ_id) > 1) as t2 WHERE t1.id::text = ANY (string_to_array(t2."Identifiants",',')) ;
Pour supprimer les doublons
DELETE FROM mon_schema.ma_table WHERE mon_champ_id NOT IN ( SELECT max(mon_champ_id) FROM mon_schema.ma_table GROUP BY mon_champ_a_verifie_1, mon_champ_a_verifie_2, mon_champ_a_verifie_3 ) ;
Concepts clé
Les structures conditionnelles
PostgreSQL vous permet d’utiliser ce qu’on appelle les structures conditionnelles. Voici comment cela se construit :
CASE WHEN mon_champ = 'valeur 1' -- Condition n°1 THEN 'Je renvoie ceci' -- Valeur renvoyée si la condition n°1 est respectée WHEN mon_champ = 'valeur 2' -- Condition n°2 THEN 'Je renvoie cela' -- Valeur renvoyée si la condition n°2 est respectée -- Si aucune condition n'est remplie, alors la valeur renvoyée est 'null' END
Il est également possible de spécifier la valeur à retourner lorsqu’aucune condition n’est remplie.
CASE WHEN mon_champ = 'valeur 1' THEN 'Je renvoie ceci' WHEN mon_champ = 'valeur 2' THEN 'Je renvoie cela' ELSE 'Je renvoie rien' -- Si aucune condition n'est remplie, alors on indique la valeur à retourner END
Voici une autre syntaxe
CASE mon_champ -- On déclare le champ sur lequel tester les conditions WHEN 'valeur 1' -- On indique la valeur permettant de remplir la condition (uniquement du égal) THEN 'Je renvoie ceci' WHEN 'valeur 2' THEN 'Je renvoie cela' ELSE 'Je renvoie rien' END
Voici un cas plus complexe utilisant plusieurs colonnes
CASE WHEN mon_champ = 'valeur 1' OR mon_champ = 'valeur 2' -- Il est possible d'utiliser des opérateurs logiques pour combiner les conditions THEN 'Je renvoie ceci' WHEN mon_champ_2 = 'valeur A' -- Il est possible de se baser sur un deuxième champ THEN 'Je renvoie cela' ELSE 'Je renvoie rien' END
Où utiliser ces structures ?
Dès que vous devez utiliser une colonne (dans un SELECT, dans une clause WHERE…) vous pouvez utiliser les structures conditionnelles.
Voici un exemple :
SELECT ma_colonne, CASE ma_colonne_test WHEN TRUE THEN 'Vrai' WHEN FALSE THEN 'Faux' ELSE 'Inconnu' END, CASE WHEN ma_colonne_test_2 < 0 THEN 'Négatif' WHEN ma_colonne_test_2 > 0 THEN 'Positif' ELSE 'Nul' END FROM ma_table ;
Les CTE (Common Table Expression)
Il existe une syntaxe particulière permettant de stocker temporairement le résultat d’une requête pour l’utiliser juste après dans une seconde requête. Il s’agit des Common Table Expression.
CTE classique
Le but est de définir une requête puis d’y faire référence dans une seconde :
WITH requete_1 AS ( -- La première requête est nommée "requete_1" puis définie (il s'agit d'un simple SELECT) SELECT colonne_1 as colonne_a, colonne_2 as colonne_b FROM mon_schema.ma_table_1 ) SELECT -- La deuxième requête est lancée t1.colonne_a FROM requete_1 as t1 -- La première requête est réutilisée simplement en l'appelant dans la clause FROM WHERE t1.colonne_b > 12 ;
Cette syntaxe permet de simplifier certaines requêtes, voici un exemple sur une requête que nous avions précédemment pour détecter les doublons. Cette requête affiche les objets, le nombre de doublon ainsi que les ids doublons.
Requête initiale :
SELECT t1.mon_champ_id, t2."Nombre", t2."Identifiants" FROM mon_schema.ma_table as t1, (SELECT count(mon_champ_id) as "Nombre", string_agg(mon_champ_id::text, ',') as "Identifiants" FROM mon_schema.ma_table GROUP BY mon_champ_a_comparer_1, mon_champ_a_comparer_2, mon_champ_a_comparer_3 HAVING count(mon_champ_id) > 1) as t2 WHERE t1.id::text = ANY (string_to_array(t2."Identifiants",',')) ;
Requête CTE :
WITH liste_doublon AS ( SELECT count(mon_champ_id) as "Nombre", string_agg(mon_champ_id::text, ',') as "Identifiants" FROM mon_schema.ma_table GROUP BY mon_champ_a_comparer_1, mon_champ_a_comparer_2, mon_champ_a_comparer_3 HAVING count(mon_champ_id) > 1 ) SELECT t1.mon_champ_id, t2."Nombre", t2."Identifiants" FROM mon_schema.ma_table as t1, liste_doublon as t2 WHERE t1.id::text = ANY (string_to_array(t2."Identifiants",',')) ;
L’utilisation de cette syntaxe permet de simplifier considérablement la lecture de la requête même si le résultat reste le même.
Notez que vous pouvez enchainer les CTE et les utiliser les unes dans les autres :
WITH requete_1 AS ( SELECT colonne_1 FROM mon_schema.ma_table ), -- On démarre une autre requête CTE requete_2 AS ( SELECT t1.colonne_2, t2.colonne_4 FROM mon_schema.ma_table_2 AS t1 LEFT JOIN -- On utilise la première requête requete_1 AS t2 ON t1.colonne_3 = t2.colonne_1 ), SELECT * FROM requete_2 ;
CTE Récursif
Il est également possible d’introduire de la récursivité dans vos requêtes avec cette syntaxe. Le but de la récursivité est d’utiliser une requête dans elle-même afin d’obtenir une sorte d’incrémentation.
Une requête récursive est toujours construite de la façon suivante :
- Le mot clé RECURSIVE
- Une requête de base
- Une jointure avec une requête récursive qui utilise la requête CTE en cours de définition
La récursivité s’arrête lorsque la requête récursive ne renvoie plus de ligne. Attention à veiller à ne pas créer de boucle infinie.
Voici un exemple qui permet d’afficher les nombre de 1 à 100 :
WITH RECURSIVE ma_table AS ( -- Déclaration du caractère récursif avec le mot clé RECURSIVE SELECT -- Requête de base non récursive 1 AS colonne_valeur UNION ALL -- Jointure des résultats d'une seconde requête SELECT -- Seconde requête, récursive : réutilisant la requête CTE colonne_valeur+1 FROM ma_table -- Appel de la requête WITH dans elle-même WHERE -- La clause permet de stopper la récursivitée lorsque la valeur 100 est atteinte colonne_valeur < 100 ) SELECT colonne_valeur FROM ma_table ;
Voici les opérations effectuées par PostgreSQL pour cette requête :
- Exécution de la requête non récursive :
SELECT 1
Valeur retournée :1
- Exécution de la requête récursive :
SELECT 1+1
Valeur retournée :2
- Exécution de la requête récursive jusqu’à ce qu’aucune ligne ne soit renvoyée (jusqu’à
colonne_valeur = 99
)SELECT 99+1
Valeur retournée :100
- Exécution de la requête principale (qui appelle la requête CTE)
Affichage de toutes les valeurs.
Le fenêtrage
Le fenêtrage consiste en un regroupement de données au sein d’une requête sur laquelle une opération est effectuée.
J’ai consacré un article entier sur le fenêtrage, vous en apprendrez plus par ici :
Maintenant que vous savez gérer votre données, nous allons voir quelques fonctions qui peuvent être pratique.
Sommaire général
Voici le sommaire général du cours :
Cet article vous a plu ?
N'hésitez pas à le partager, il interessera surement certains de vos contacts.
Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :
BDDPostgreSQLProgrammationSQL tuto