Le langage SQL avancé

icône de pdf

Nous continuons à explorer les commandes SQL avec des requêtes plus élaborées avec l’exemple de base de données de la fiche https://www.annabac.com/revision-bac/le-langage-sql.

I) La commande WHERE

La commande WHERE permet de spécifier des critères de sélection. Par exemple, pour savoir quels sont les élèves qui ont plus de 14 en maths ou plus de 18 en informatique :

4db76ec5-3299-4597-95ac-5dbd146d1077

II) Modifications

On peut modifier des valeurs dans une table avec UPDATE :

4064ab6e-19fb-464e-90f1-b3da7f0b244b

Par exemple, pour donner 16 en maths à Joe :

d4f15ad6-da74-4020-87ce-cf894dd8b0c2

On peut supprimer une ligne avec DELETE :

ccda4336-319c-4b54-a1cc-c6a8ae76679e

Par exemple, pour enlever les lignes dont les notes de maths sont inférieures à 14 :

1a5f2484-885e-4878-b00b-b84351621d8c

On peut ajouter une colonne avec ALTER TABLE :

4ad16361-4fb9-406b-9803-889cb53de9f2

Par exemple, pour ajouter une colonne pour enregistrer la classe de l’élève :

8abf8ba1-b8f2-40e1-9843-a7450821839d

On obtient :

059fe71d-bb41-416a-88c0-670d076d71eb

On peut renommer une table :

29d35794-8bc6-448e-8a9b-e9b470230d13

On peut supprimer une table avec DROP TABLE :

9c892da5-9912-4053-8637-4d4614e440df

III) Fonctions de groupes (agrégation)

Les fonctions de groupe permettent d’obtenir des informations sur un ensemble de lignes en travaillant sur les colonnes et non pas sur les lignes comme avec WHERE. Par exemple :

AVG calcule la moyenne d’une colonne ;

SUM calcule la somme d’une colonne ;

MIN, MAX calculent le minimum et le maximum d’une colonne ;

COUNT donne le nombre de lignes d’une colonne.

Exemple : comptons combien d’élèves ont plus de 15 en maths.

ddceea41-b0a8-4421-a711-820e6e20baa8

On obtient :

83123cd9-1b9b-41fb-9723-da8df7c87f4b

IV) Le tri : ORDER BY

Trions les lignes par ordre croissant des notes de maths et, en cas d’égalité, des notes d’informatique.

16ad849d-295c-42b4-8cbb-7fb45f160ccf

On obtient :

93b99710-5a85-494f-bccf-9f433c54d34f

V) Les jointures

Une jointure permet d’associer plusieurs tables dans une même requête.

Par exemple, supposons que nous disposions d’une table associant à chaque note sa mention, alors que nous avons déjà une table associant à chaque élève ses notes. Nous voudrions créer une table liant le nom des élèves à leur mention correspondant à leur note de maths.

Voici un extrait d’une table Table_mentions :

ea85c016-1632-45bd-bc72-64dbc26a31c7

Nous allons joindre la table Table_notes et la table Table_mentions en reliant les lignes telles que la note de maths de la 1re table soit égale à la note de la 2e table :

3ea8553a-11a9-4e93-b8ce-a6e50f3af7e2

On obtient :

be52a6ba-5566-4738-8b75-997fed393179

Si on veut créer une table à partir de cette sélection et la réutiliser, on emploie la syntaxe :

9c9a1ac0-864b-4a45-a64c-06df2907534b

VI) Un exemple de faille de sécurité : l’injection SQL

Il est très important de sécuriser ses BDD qui peuvent contenir des données sensibles. Or il est courant de demander aux utilisateurs des données (nom, mot de passe…) qui sont introduites dans la base. Un utilisateur malveillant pourrait alors entrer du code SQL au lieu de son nom et détruire la base ou bien récupérer des données.

Par exemple, on peut demander à un utilisateur d’entrer un pseudo sans précaution via ce code en Python avec la bibliothèque bottle-sqlite :

510d22d4-d747-4321-bc0b-01b88240e0fd

Si l’utilisateur rentre le pseudo \'Joe\'; DROP TABLE inscrits; alors la requête exécutée est :

c1faa2b6-0681-4782-97ea-744208768343

Joe sera bien ajouté, mais une nouvelle requête est ajoutée qui va détruire la table à l’insu de l’administrateur.

Il est recommandé de faire vérifier les entrées avant de lancer la requête, par exemple à l’aide d’expressions régulières ou en utilisant des paramètres SQL comme ici :

841fb6dd-f6d5-42fb-8b8d-fe52bc416359

SQLite va vérifier que le paramètre pseudo ne contient aucun code créant une requête avant d’exécuter la requête SELECT et ensuite remplacera le ? par la valeur de pseudo vérifiée (requête préparée).