Langage SQL : manipulation des données

Le langage de manipulation de données, dont l’acronyme est DML pour Data Manipulation Langage, permet de travailler sur les données des tables précédemment créées.

Il existe deux types de commandes de manipulation des données : celles qui permettent de lire des données, et donc de les extraire de la base de données, et celles qui permettent de les modifier.

Les commandes permettant de lire les données sont SELECT et COPY.

Les commandes permettant de modifier les données sont INSERT et COPY pour ajouter de nouvelles données, UPDATE pour mettre à jour les données existantes, DELETE et TRUNCATE pour effacer des données existantes. Il est aussi possible d’extraire les données manipulées avec les commandes INSERT, UPDATE et DELETE.

Langage de modification des données

Avant de pouvoir lire des données, il est nécessaire d’ajouter de nouvelles données dans les tables nouvellement créées. Pour cela, il existe deux commandes : la première utilise l’ordre INSERT, conforme à la norme SQL et la seconde la commande COPY pour notamment des volumes de données importants à l’insertion.

1. Insertion et copie de données

L’ordre INSERT respecte la notation de la norme SQL en apportant quelques modifications. Cette commande permet d’ajouter de nouvelles lignes de données dans une table. Le synopsis de l’ordre INSERT est le suivant :

INSERT INTO table 
 [ ( colonne [, ...] ) ] 
  {  DEFAULT VALUES 
     | VALUES ( { expression | DEFAULT } [, ...] ) 
     | requête 
  } 
 [ RETURNING * | expr ]

Après le nom de la table, la liste des colonnes permet d’indiquer celles qui seront en effet renseignées dans les nouvelles lignes ajoutées. Lorsque toutes les colonnes de la table sont utilisées, cette liste n’est pas obligatoire. Lors de l’insertion, les colonnes qui ne sont pas présentes dans la liste utilisent la valeur ou l’expression par défaut, ou, en son absence, la valeur NULL. Si une colonne absente de la liste n’a pas de valeur par défaut et est définie comme étant NOT NULL, alors la commande échoue.

L’expression DEFAULT VALUES permet d’ajouter une ligne en utilisant les valeurs ou expressions par défaut exprimées dans la définition de la table. Pour utiliser le potentiel de cette expression, il faut que la définition des valeurs ou expressions par défaut soit riche, et très souvent, qu’on utilise la liste des colonnes dans la commande.

L’expression VALUES permet d’exprimer les valeurs ou expressions à insérer. Pour chaque colonne, la valeur est exprimée soit avec sa valeur littérale, soit avec une expression, en respectant l’ordre des colonnes indiquées dans la première partie de la commande.

L’exemple qui suit montre une insertion simple :

INSERT INTO commandes.clients (cli_nom, cli_email) 
 VALUES ('Larisa...

Langage de requêtage des données

La lecture des données est une étape fondamentale et certainement une des plus importantes lorsqu’on évoque les bases de données : les données stockées sont généralement dédiées à être exploitées et c’est l’ordre SELECT qui va permettre leur lecture et leur transformation.

Un ordre SELECT simple distingue plusieurs parties :

La première partie, juste après le mot-clé SELECT, est la liste des champs qui seront récupérés dans la réponse. Chacun de ces champs peut être une expression ou un champ d’une table. Le champ peut avoir un autre nom que la colonne de la table en utilisant avec le mot-clé AS. Il est possible d’afficher tous les champs en utilisant le caractère joker * à la place de la liste des champs.
La seconde partie, avec le mot-clé FROM, liste les tables utilisées.
Enfin, un ensemble de mots-clés permet de filtrer les données selon de nombreux critères :

La clause WHERE permet de filtrer sur des conditions, en comparant des valeurs entre elles, par exemple.
La clause GROUP BY permet de regrouper des lignes sur leurs valeurs et la clause HAVING permet de filtrer ces regroupements.
La clause ORDER BY permet de trier les lignes en sortie, selon l’expression indiquée.
La clause LIMIT indique le nombre de lignes en sortie et la clause OFFSET permet de décaler cette restriction.

Les mots-clés UNION, INTERSECT et EXCEPT permettent de combiner plusieurs expressions SELECT.

Les clauses FOR UPDATE et FOR SHARE permettent de verrouiller les lignes des tables, permettant une mise à jour ultérieure.

Le synopsis suivant montre l’ordre SELECT :

SELECT 
 [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] 
       * | expression [ AS nom_champ ] [, ...] 
 [ FROM from [, …] [TABLESAMPLE methode (ratio)] ] 
 [ WHERE condition ] 
 [ GROUP BY expression [, ...] ] 
 [ HAVING condition [, ...] ] 
 [ { UNION | INTERSECT | EXCEPT } [ ALL ] SELECT ... ] 
 [ ORDER BY expression [ ASC | DESC | USING operateur ] 
 [, ...] ] 
 [ LIMIT { nombre | ALL } ] 
 [ OFFSET début ] 
 [ FOR {...

Transactions et verrouillage

PostgreSQL étant un serveur de bases de données, il accepte des connexions depuis des clients. Un des aspects les plus importants du fonctionnement d’une instance PostgreSQL est de permettre à tous les clients connectés de lancer les requêtes simultanément : on parle alors de concurrence d’accès aux données.

PostgreSQL implémente les quatre caractéristiques normalisées des systèmes de bases de données relationnelles, connues sous l’acronyme ACID :

Atomicité : une transaction forme un ensemble atomique. L’ensemble de la transaction est validé, ou pas du tout.
Cohérence : une transaction ne peut pas rendre la base de données incohérente.
Isolation : une transaction ne voit pas les autres transactions en cours.
Durabilité : les données validées le sont durablement.

Afin d’implémenter ces caractéristiques, PostgreSQL utilise le concept MVCC : Multi Version Concurrency Control : contrôle de la concurrence basée sur de multiples versions des enregistrements.

En résumé, chaque modification d’un enregistrement introduit une nouvelle version de l’enregistrement, évitant ainsi de verrouiller complètement la ligne, et donc permettant aux sessions concurrentes de continuer à utiliser l’enregistrement original.

Pour cela, toutes les requêtes utilisent des identifiants pour marquer les enregistrements : ces identifiants sont propres à une transaction, et de fait, toutes les requêtes sont des transactions. Pour simplifier, PostgreSQL compare les identifiants des transactions avec les identifiants des enregistrements pour savoir si un enregistrement est visible et s’il est modifiable : c’est comme ça que PostgreSQL verrouille les enregistrements.

1. Validation des transactions

Étant donné que chaque requête est exécutée dans une transaction, il est nécessaire de comprendre à quel moment la transaction est validée. Dans PostgreSQL, chaque requête est exécutée dans sa propre transaction qui est validée automatiquement. Une action comme un INSERT ou un CREATE TABLE sont implicitement validées dans la base de données dès...

SQL - manipulation