Principe de requêtage
Dans le SQL…
Tout est requête.
La Nouvelle Revue Française, Tome XIII, NRF – Paris – 1919, p390 ligne 13
Que vous souhaitiez importer, modifier ou encore afficher vos données, vous allez utiliser des requêtes.
Le principe des requêtes SQL est simple :
- On pose une question : la requête.
- Le serveur nous répond : la réponse.
Ainsi, nous allons apprendre comment poser des questions au serveur pour qu’il travaille pour nous.
Par exemple, pour l’ajout de donnée, comme nous sommes très polis, nous demandons au serveur si dans son extrême amabilité il accepterait d’insérer nos données (avec bienveillance). Il s’exécutera alors puis nous répondra bien cordialement « ok, c’est fait, j’ai inséré X lignes » (oui bien que nous soyons très polis, le serveur utilise son propre argot que nous décoderons).
Aussi performant qu’ils soient, les serveurs ne laissent aucune place à la diplomatie. Ainsi, vous devrez donc respecter leurs codes et leurs langages à la lettre si vous souhaitez travailler avec eux. Heureusement, ils ne sont aucunement rancuniers et vous indiquerons immédiatement l’erreur commise si vous leur manquez de politesse et pourrez dans la foulée leur reposer votre question.
Sans plus attendre lançons vos premières requêtes. Pour cela, rendez vous dans votre client SQL préféré (DBeaver ?). Pour rappel, dans DBeaver clic droit sur une connexion puis sélectionnez « Editeur SQL » et dans pgAdmin cliquez sur la « loupe SQL » dans le menu du haut, une fenêtre s’ouvre.
Voici une requête toute simple que vous pouvez tester :
-- Exemple de requête simple SELECT 'hello world';
Et voila !! Vous venez de lancer votre première requête : demander au serveur d’afficher « hello world » et le serveur a du vous répondre « hello world », facile non ?
Transactions
Les requêtes que vous aller envoyer au serveur peuvent modifier profondément les données stockées et leur structure. Pour s’assurer du bon déroulement de vos requêtes, PostgreSQL (comme toutes les bases de données) utilise le principe des transactions.
Une transaction est une unité de travail dans laquelle des instructions sont exécutées.
Quand on travaille avec des bases, il faut toujours penser aux ACID
Arthur Bazin
Les transactions respectent toujours les propriétés ACID (plus de détails sur Wikipedia) :
- Atomicité : la transaction se fait totalement ou pas du tout, en cas d’échec les données sont remise dans le même état qu’avant la transaction (même en cas de panne pendant la transaction).
- Cohérence : la transaction n’entraine pas d’invalidité dans un objet de la base (les objets étaient valides avant et le restent après).
- Isolation : chaque transaction s’exécute sans aucun lien avec une quelconque autre transaction.
- Durabilité : les opérations effectuées dans la transaction sont effectuées de façon définitive une fois la transaction terminée (même en cas de panne immédiatement après la transaction).
Les transactions vont donc contenir vos requêtes afin qu’elles s’exécutent correctement. Voici comment se déroule une transaction ainsi que les opérations possibles durant celle-ci :
BEGIN,BEGIN TRANSACTIONouSTART TRANSACTION: permet de démarrer une transaction. Un point de sauvegarde de la BDD est créé à ce moment là.SAVEPOINT mon_pointdesauvegarde: permet de créer un point de sauvegarde à l’intérieur d’une transaction.ENDouCOMMIT: permet de terminer la transaction et de la valider. Les points de sauvegarde concernant cette transaction sont tous supprimés.ROLLBACK: Annule la transaction et restaure la BDD au point de sauvegarde créé lors duBEGIN.ROLLBACK TO mon_poindesauvegarde: Annule les opérations effectuées depuis la création du point de sauvegarde indiqué.
Ainsi, le requêtage doit être effectué de la façon suivante :
-- Démarrage de la transaction BEGIN; -- Mise à jour d'une table UPDATE ma_table SET mon_champ = mon_champ - 100; -- Création d'un point de sauvegarde SAVEPOINT mon_pointdesauvegarde; -- Mise à jour d'une autre table UPDATE ma_table_2 SET mon_champ_2 = mon_champ_2 + 100; -- Annulation de la dernière opération ROLLBACK TO mon_pointdesauvegarde; -- Mise à jour d'une autre table UPDATE ma_table_3 SET mon_champ_3 = mon_champ_3 + 100; -- Validation de la transaction END;
Dans les fait, vous n’utiliserez que très peu ces fonctionnalités sauf lors de la rédaction de scripts complexes pour les raisons suivantes :
- Lorsque vous exécutez quelques requêtes tapées à la main, vous souhaitez voir le résultat immédiatement. Vous « commitez » donc immédiatement la transaction.
- Les clients SQL possèdent des fonctions d’auto-commit qui vous permettent de vous passer de la rédaction de ces instructions. Vous pouvez d’ailleurs gérer la façon dont fonctionne l’auto-commit dans ces clients pour régler finement la façon dont s’appliquent vos instructions.
Mais si besoin, n’hésitez pas à désactiver l’auto-commit pour tester et voir ce qu’il se passe notamment en lançant une requête sans la commiter puis en testant d’autres requêtes dans une autre transaction (dans une autre fenêtre d’instruction SQL).
Les commandements de la requête
Avant de nous lancer dans nos premières requêtes voici quelques règles et conventions à garder à l’esprit et que vous retrouverez dans la suite.
Pour le nom de vos objets (schéma, table, colonne), la norme SQL impose l’utilisation des 26 lettres de l’alphabet, des dix chiffres et du caractère underscore (_). PostgreSQL est plus laxiste et autorise l’utilisation d’accent, d’espace et autre caractères étrange. Je vous recommande (et vous impose) de n’utiliser que les caractères de la norme SQL. En effet, vous risquez certaines incompatibilités avec d’autres clients, extensions ou modules.
Par défaut, PostgreSQL utilise la minuscule pour les noms d’objet, vous devrez donc entourer vos objets par des guillemets s’ils utilisent des majuscules (de même s’ils utilisent des caractères accentués).
Une requête se termine toujours par un point-virgule ;. Ça permet de dire à PostgreSQL que l’instruction est finie.
Privilégiez l’utilisation de lettre majuscule pour les mots-clés lors de la rédaction de vos requêtes, ça vous en simplifiera la lecture.
Usez et abusez de l’indentation pour vous simplifier la lecture des requêtes.
Un commentaire débute par un double tiret --.
Les valeurs textuelles sont entre simples guillemets ('), les valeurs numériques n’en ont pas besoin.
Préfixez le nom des objets pour mieux vous y retrouver : vous pouvez utilisez la première lettre du schéma pour préfixer vos tables et la première lettre de la table pour préfixer vos colonnes par exemple.
Limitez l’utilisation d’abréviation. Les noms d’objet peuvent être long alors profitez-en.
Chaque objet peut être commenté, n’hésitez pas à les détailler. Cela vous aidera beaucoup lorsque vous n’aurez pas mis le nez dans votre base depuis longtemps.
Le sélecteur étoile * permet de tout sélectionner (toutes les colonnes par exemple).
Utilisez des index sur les colonnes les plus utilisées, cela accélèrera de façon impressionnante le traitement de gros jeux de données (de x2 à x100).
NULL n’est pas une valeur, c’est justement l’absence de valeur. Ainsi, il n’est pas possible de recherche ma_valeur = NULL car justement, il n’y a pas d’élément à comparer.
Introduction au Vade-mecum
Maintenant que nous avons vu les bases du requêtage, nous pouvons nous concentrer sur le langage SQL pour que vous puissiez réaliser vos premières requêtes.
Il s’agit ici d’un vade-mecum des requêtes que j’utilise le plus. N’hésitez donc par à approfondir celles-ci en vous rendant sur les nombreux sites d’information dont voici ceux qui me semble le plus intéressant :
- La documentation officielle de PostgreSQL : https://www.postgresql.org/docs/
- PG Formater – Un outil pour mettre en forme vos requêtes : http://sqlformat.darold.net/
- La documentation en français de PostGIS : http://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/index.html
- La liste des fonctions PostGIS : http://postgis.net/docs/reference.html
Dans la suite de ce vade-mecum, chaque page est organisée en deux grandes parties :
- Les requêtes : recueil des requêtes qui me semble intéressantes
- Les concepts clés : quelques détails sur certains éléments des différentes requêtes.
Si vous êtes prêts, allons-y !
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
