Manipuler des entrées

Modifier le contenu d'une table

Pour rappel, il existe plusieurs types de commandes SQL permettant d'effectuer différent types actions :

: lire des données ;
: modifier des entrées ;
: insérer des entrées ;
: supprimer des entrées.

Nous allons désormais nous intéresser aux 3 dernières.

Créer des entrées

La requête SQL permet de créer des entrées, et se présente sous la forme suivante :

représente une entrée. Les différentes valeurs de l'entrée sont séparées par une virgule.
💡 Vous pouvez créer plusieurs entrées à la fois en les séparant par une virgule.

{"m_vals": "(NULL, 'Doe', 'Jon', 32)"}

{"m_vals": "(NULL, 'Doe', 'Jon', 32),\n (NULL, 'Sawyer', 'Tom', 15)"}

Insérer des entrées

La requête SQL permet d'insérer des entrées dans une table, et se présente usuellement sous la forme suivante :

représente une entrée à insérer. Les différentes valeurs de l'entrée sont séparées par une virgule.
💡 Vous pouvez insérer plusieurs entrées à la fois en les séparant par une virgule.

{"m_vals": "(NULL, 'Doe', 'Jon', 32)"}

{"m_vals": "(NULL, 'Doe', 'Jon', 32),\n (NULL, 'Sawyer', 'Tom', 15)"}

{"cols": "(Prenom, Nom )", "m_vals": " ('Jon' , 'Doe')"}

💡 Vous pouvez aussi ne renseigner les valeurs que pour certaines colonnes dont vous indiquez les noms avant . Dans ce cas, les colonnes non renseignées vaudront .

Modifier des entrées

La requête SQL de modifier des entrées dans une table, et se présente usuellement sous la forme suivante :

est le nom de la colonne à modifier.
est la nouvelle valeur de cette colonne.
indique quelles entrées doivent être modifiées.

💡 Vous pouvez modifier plusieurs colonnes à la fois en séparant les par une virgule.

{"vals": "Nom = 'Durand'", "cond": "Nom == 'Durant'"}

{"vals": "Nom = 'Durand', Prenom='Théo'", "cond": "ID == 2"}

{"vals": "Age = 23", "cond": "Age == 43"}

{"vals": "Age = Age + 10", "cond": "Nom LIKE 'D%'"}

💡 Vous pouvez calculer les nouvelles valeurs à partir des valeurs initiales de l'entrée.

⚠ Il est possible d'avoir des utilisateurs avec le même nom et/ou prénoms. Il est ainsi préférable, autant que possible, d'effectuer la condition les ID pour s'assurer de modifier les bonnes entrées, et non celles d'homonymes.

Supprimer des entrées

La requête SQL de supprimer des entrées dans une table, et se présente usuellement sous la forme suivante :

indique quelles entrées doivent être supprimées.

{"cond": "Age < 18"}

{"cond": "Age > 18"}

{"cond": "ID == 2"}

⚠ Il est possible d'avoir des utilisateurs avec le même nom et/ou prénoms. Il est ainsi préférable, autant que possible, d'effectuer la condition les ID pour s'assurer de supprimer les bonnes entrées, et non celles d'homonymes.

Prétraitements

Il est bien souvent important de pré-traiter les données avant de les utiliser (e.g. comparaisons, insertions), ce afin de s'assurer que les informations soient bien représentées de la même manière. Par exemple, un nom de famille peut s'écrire de plusieurs manières : , "", ou "". Si ces trois chaînes de caractères représentent bien la même information, elles ne sont pas égales pour autant. Ainsi, aucune entrée ne sera retournée si on recherche dans la base de données, alors qu'il est stocké en majuscules (i.e. ).

Pré-traiter les données permet alors de s'assurer de l'uniformité des données, i.e. que les données manipulées suivent le même format, facilitant e.g. leur comparaisons. Pré-traiter les données avant insertions permet aussi de s'assurer de la cohérence et consistance de la base de données.

💡 Il est possible de tester les différentes fonctions de prétraitements en effectuant soit une requête :

sans la clause (ou avec pour certains SGBD) :
avec la clause .

{"col1": "'nom' ", "col2": "UPPER('nom')"}

{"col1": "1.2 " , "col2": "ROUND(1.2)"}

⚠ Dans les requêtes , il ne faut pas trop abuser de ces pré-traitements pour les valeurs retournées. Il est souvent préférable de gérer des données structurées en SQL, et de gérer leur pré-traitement via le langage utilisé par votre application (e.g. Python, R, JavaScript, etc). Cela permet notamment de pouvoir réutiliser une même requête pour différents usages.

💡 Certains SGBD ont aussi des systèmes de cache, permettant de recalculer plus rapidement le résultat d'une requête exécutée fréquemment.

Sur les nombres

Avec SQL, vous pouvez utiliser les opérateurs et fonctions arithmétiques classiques :

{"cols": "1+1, 4%3, 4/3, 4/3.0"}

{"cols": "FLOOR(.2),ROUND(.2),CEIL(.2)"}

{"cols": "ABS(1), ABS(-1)"}

{"cols": "RANDOM()"}

{"cols": "ABS(RANDOM()) % 10"}

⚠ utilisé avec des nombres entiers effectuera la division entière. Si vous souhaitez faire la division décimale, vous devez avoir au moins une opérande décimale.

: arrondir à décimales.
: la valeur absolue du nombre .
: génère un entier aléatoire.
💡 Utilisez pour générer un entier dans .
💡 Certains SGBD proposent une fonction permettant de générer un réel dans .

💡 Vous avez aussi beaucoup d'autres fonctions trigonométriques, de logs, etc.

Sur les dates

Les opérations sur les dates sont complexes à effectuer à la main. En effet, il faut alors gérer :

les mois avec des nombres de jours différents ;
les années bissextiles ;
les fuseaux horaires ;
les heures d'été et d'hiver.

Les SGBD fournissent donc des fonctions permettant de manipuler les dates qui s'occupent de prendre tout cela en compte. Par exemple permet de calculer la durée entre deux dates.

Il est aussi fréquent d'utiliser des timestamps, e.g. l'unix timestamp, qui représente une date par le nombre de secondes écoulées depuis le 1er janvier 1970. Il permet notamment d'aisément comparer et stocker des dates.

{"cols": "DATE(), TIME(), DATETIME()"}

{"cols": "DATE(), STRFTIME('%d/%m/%Y', DATE())"}

{"cols": "UNIXEPOCH('now')"}

{"cols": "DATETIME(10, 'unixepoch')"}

{"cols": "TIMEDIFF('now', '2001-01-01')"}

// : retourne la date et/ou l'heure actuelle.
/ : formate la date au format (cf TP2).
: donne la durée entre deux dates.
: donne le nombre de secondes écoulées entre le 1er janvier 1970 et la date .

💡 Idem, il existe de nombreuses autres opérations et formatage sur les dates que nous ne verrons pas dans le cadre de ce cours.

⚠ Les fonctions de dates ne sont pas normalisées et dépendent donc du SGBD utilisé.

Sur les chaînes de caractères

D'un utilisateur à un autre, la casse des lettres dans les chaines de caractères est souvent inconsistante. Lorsque la casse importe peu quant à l'information stockée, il est alors fréquent de mettre la chaîne de caractère en minuscule (ou en majuscule) afin de pouvoir ensuite plus facilement les manipuler/comparer en s'assurant de la consistance des données (e.g. e-mail, nom, etc.).

Les chaînes de caractères sont généralement stockées sur des colonnes à taille variables. Mais il arrive que pour des raisons d'optimisations, elles soient stockées sur des colonnes à tailles fixes. Dès lors, pour stocker une chaîne de caractère d'une taille moindre, on lui ajoutera généralement des espaces en début ou fin de chaîne (pad). L'opération inverse consiste à retirer les espaces en début ou fin de chaîne (trim).

{"cols": "LOWER('Hello'), UPPER('Hello')"}

{"cols": "LENGTH('23'), LENGTH(32)"}

{"cols": "TRIM(' w '),LTRIM(' w '),RTRIM(' w ')"}

{"cols": "FORMAT('{x=%.2f, y=%.2f}', 1.2, 1.)"}

/ : transforme tous les caractères en minuscules/majuscules.
: la taille de .
: enlève les espaces en début et/ou fin de chaîne.
: l'inverse de , ajoute des caractères en début/fin de , de sorte à avoir une chaîne de caractère de taille .
⚠ Cette fonction n'est pas disponible sur SQLite.
: formate des données au format (cf TP2).
⚠ Pour SQLite < 3.38, cette fonction s'appelle .

Il existe bien d'autres fonctions que nous ne verrons pas dans le cadre de ce cours :

Afficher les autres pré-traitements

: inverse l'ordre des caractères.
: dans , remplace par .
: retourne la sous-chaîne de commençant à l'index et de taille .
: retourne l'index à laquelle se trouve dans .

Autres prétraitements (en TP)

Sur les valeurs nulles

{"cols": "NULL"}

{"cols": "COALESCE(NULL, 4)"}

{"cols": "COALESCE(NULL, NULL)"}

: renvoie la première valeur non nulle (ou null si toutes nulles).

Conversions

{"col_cast": "'1.2' AS REAL"}

{"col_cast": "'1.2' AS INT"}

{"col_cast": "1.2 AS TEXT"}

: converti la valeur dans le type .
💡 sera ainsi généralement : , /, ou .

💡 Il existe bien d'autres fonctions de conversions plus ou moins spécifiques aux SGBD.

Concaténer des colonnes

💡 Vous pouvez aussi concaténer des colonnes en utilisant l'opérateur :

{"col_concat": "Nom"}

{"col_concat": "Nom || ' ' || Prenom"}

{"col_concat": "Nom || ' (' || Age || ')'"}

⚠ Attention à ne pas abuser des concaténations. Il est bien souvent préférable de récupérer des données structurées, puis de gérer leurs formatages et affichages via le langage utilisé par votre application (e.g. Python, R, JavaScript, etc). Cela permet notamment de pouvoir réutiliser une même requête pour différents usages.

💡 Certains SGBD ont aussi des systèmes de cache, permettant de recalculer plus rapidement le résultat d'une requête exécutée fréquemment.

Conditions

De manière rare, il est possible qu'on souhaite effectuer des conditions.

{"age": "2"}

{"age": "20"}

Le format est relativement simple :

il commence par et fini par .
retourne la valeur si est évalué à vrai.
est la valeur par défaut si aucune condition n'est vraie.

⚠ Le n'est là qu'à des fins de démonstrations, afin de vous permettre de modifier la valeur de l'âge. Pour le moment ignorez-le.

⚠ Si vous utilisez fréquemment la clause dans les // pour :

calculer la valeur d'une colonne dépendant d'autres colonnes : est-il vraiment nécessaire de la stocker ?
transformer la valeur d'une colonne : serait-il intéressant de plutôt stocker la valeur transformée ?

Considérations de sécurité

Il est possible de hasher certaines données avant de les insérer dans la base de données. Cela peut par exemple être utilisé afin de rapidement comparer deux fichiers, ou de contrôler l'intégrité d'un fichier.

⚠ La fonction permet de hasher les données passées en paramètre. Elle n'est cependant pas disponible dans tous les SGBD. Sur SQLite, elle requière d'installer une extension.

⚠ Il faut impérativement hasher ou chiffrer les données sensibles AVANT de les transmettre à la requête SQL. En effet, si vous hashez/chiffrez des données sensibles à l'intérieur même d'une requête SQL, il y a de fortes chances qu'elles soient ensuite stockées en clair dans les logs du SGBD...

💡 Vous approfondirez les questions de chiffrement et de hashage dans d'autres modules.

Agrégation

Une agrégation permet de regrouper, au sein d'une même ligne, plusieurs entrées, e.g. pour obtenir la liste des nombres de produits vendus, par dates ou par produits :

{"grp": "Ref"}

{"grp": "Date"}

Pour cela, on utilise la clause qui permet de fusionner, au sein d'une même ligne, les entrées dont les valeurs de sont égales. Vous trouverez ci-dessous une représentation visuelle d'une agrégation :

Vous remarquerez alors que les lignes contiennent plusieurs valeurs pour une même colonne. Il convient alors d'utiliser une fonction d'agrégation qui prendra la liste des valeurs et retournera une valeur unique. Par exemple, concatène, pour chaque ligne, la colonne de ses entrées :

{"grp": "Ref", "op": "GROUP_CONCAT(Q)"}

{"grp": "Date", "op": "SUM(Q)"}

{"grp": "Ref", "op": "AVG(Q)"}

{"grp": "Date", "op": "MIN(Q)"}

{"grp": "Date", "op": "MAX(Q)"}

: concatène les valeurs.
/ : fait la somme des valeurs.
: fait la moyenne des valeurs.
: retourne la valeur minimale.
: retourne la valeur maximale.

💡 Par défaut, en l'absence d'une fonction d'agrégation, SQL retourne la première valeur.

💡 Par défaut, si une fonction d'agrégation est utilisée sans clause , alors toutes les entrées sont regroupées dans la même ligne.

Compter les lignes/entrées

💡 Il est aussi possible de compter les entrées/valeurs de chaque lignes :

{"grp": "Date", "op": "COUNT(*)"}

{"grp": "Date", "op": "COUNT(Q)"}

{"grp": "Date", "op": "COUNT(DISTINCT Q)"}

: compte le nombre d'entrées.
: compte le nombre de valeurs non-nulles.
: permet de ne pas compter les doublons.

Conditions sur les agrégations

Jusqu'à présent, nous utilisions la clause pour sélectionner les entrées à récupérer. Cependant, la clause filtre les entrées avant leur agrégation au sein d'une même ligne. Ainsi, l'usage d'une fonction d'agrégation dans sa condition générera un message d'erreur :

{"aggr": "", "cond": ">=10", "cols": "Q"}

{"aggr": "SUM", "cond": ">=20", "cols": "Q"}

Pour filtrer les lignes après l'agrégation, il convient alors d'utiliser la clause :

{"grp": "Date", "lcondp": "", "lcond": "SUM(Q)", "rcond": ">= 0"}

{"grp": "Date", "lcondp": "SUM(Q) as", "lcond": "Total", "rcond": ">= 30"}

{"grp": "Date", "lcondp": "SUM(Q) as", "lcond": "Total", "rcond": ">= 50"}