Le langage SQL avancé

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.

ILa 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 :

IIModifications

On peut modifier des valeurs dans une table avec UPDATE :

Par exemple, pour donner 16 en maths à Joe :

On peut supprimer une ligne avec DELETE :

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

On peut ajouter une colonne avec ALTER TABLE :

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

On obtient :

On peut renommer une table :

On peut supprimer une table avec DROP TABLE :

IIIFonctions 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.

On obtient :

IVLe tri : ORDER BY

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

On obtient :

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 :

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

On obtient :

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

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 :

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

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 :

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).