Requêter une base de données

Introduction à SQL

Les SGBD

Il est fréquent de devoir traiter de larges quantités de données :

Ces données peuvent représenter des milliers, des millions (voire bien plus), d'éléments. Il est évident qu'on ne peut pas les manipuler "à la main". Par exemple, si on souhaite compter le nombre de ventes d'une entreprise à la main, c'est :

Il est alors nécessaire d'utiliser un logiciel permettant d'efficacement :

Pour répondre à ces besoins, on utilise un Système de Gestion de Bases de Données (SGBD). Un SGBD est un logiciel permettant de créer, stocker, manipuler et requêter des bases de données. Un SGBD répond aussi à d'autres besoins comme :

💡 Dans le cadre de ce module, on se concentrera sur SQL (Structured Query Language), un langage permettant, entres autres, de structurer et de manipuler des bases de données.

Les SGBD SQL

Le langage SQL est normalisé par la norme ISO/CEI 9075. Cependant certaines fonctionnalités diffèrent selon le SGBD utilisé.

Parmi eux, SQLite est un SGBD léger où chaque base est stockée sous la forme d'un fichier. Il est très utilisé pour stocker et gérer des données locales ayant peu d'accès concurrents.

Pour des usages plus intensifs, PostgreSQL, MySQL, et MariaDB sont des SGBD open sources plus complets, sous la forme de serveurs SQL. On interagit alors avec le SGBD via un client qui se charge de :

Les clients peuvent prendre plusieurs formes :

Les tables SQL

Comme dans un tableur, les données sont représentées sous forme de tables :

Par exemple, la table suivante contient 2 entrées ayant les propriétés , , , :

ID Nom Prenom Age
1 Doe John 32
2 Durand John 64

Vocabulaire

💡 Vous pouvez voir la base de données comme un fichier tableur, et les tables comme les feuilles de calculs contenues dans ce fichier.

💡 Les tables sont aussi parfois appelées relations, d'où le terme base de données relationnelle.

Les types SQL

En SQL chaque colonne a un type, i.e. le type des données qu'on peut y insérer. SQL supporte en général 4 familles de types :

💡 Par défaut, chaque type accepte la valeur , qui indique une absence de données.

💡 En SQL les chaînes de caractères s'écrivent entre guillemets simples (). Pour échapper un guillemet simple dans la chaîne de caractères, il suffit de l'écrire en double, e.g. .

En général, les SGBD (sauf SQLite) offrent une variété de sous-types permettant d'optimiser leur stockage. Une table (non-exhaustive) des sous-types les plus communs vous est proposée ci-dessous à titre informatif :

Montrer la table des sous-types
Famille Type Octets Description
TEXT CHAR 1 à 3 Caractère UTF8
CHAR(N) N*1 à N*3 Taille fixe
VARCHAR(N) 0 à N*3 Taille variable
NCHAR 2 Caractère UTF16
NCHAR(N) N*2 Taille fixe
NVARCHAR(N) 0 à N*2 Taille variable
TEXT(N) N Pour de gros textes.
BLOB BIT(N) N/8 Champ de bits
BLOB(N) N
INT BOOLEAN 1
SMALLINT 2
INTEGER 4
BIGINT 8
NUMERIC(N) N
ENUM(val[,...]) 2 Peut aussi être TEXT
REAL FLOAT 4
DOUBLE 8
FLOAT(N) N Virgule flottante
DECIMAL(N,S) N Précision fixe
DATE DATE 4 10 en SQLite (TEXT)
TIME 8 à 12
DATETIME 12 à 16? 19 en SQLite (TEXT)
TIMESTAMP 8
💡 En fonction du SGBD utilisé, il existe une pléthore d'autres types plus ou moins complexes.

Documentation

💡 Vous trouver plus amples documentation via les liens suivants :

Requêtes de bases

Les types de requêtes

Il existe plusieurs types de commandes SQL permettant d'effectuer différent types actions :

Nous allons dans un premier temps nous concentrer sur le premier, et verrons les 3 autres par la suite.

La requête SELECT

La requête SQL permet de récupérer des données présentes dans une base de donnée, et se présente usuellement sous la forme suivante :

⚠ Il est d'usage de ne récupérer que les colonnes dont on a réellement besoin. En effet, sur de grandes tables et de grosses requêtes, réduire le nombre de colonnes récupérées permet de réduire le volume des données transférées.

⚠ Le nom de la colonne devra être écrite entre guillemets s'il contient des caractères non-alphanumériques.

Sélection des colonnes

{"cols": "*"} {"cols": "Nom"} {"cols": "ID, Prenom"}

Alias de colonnes

💡 Dans le cadre d'une requête SQL, vous pouvez temporairement associer un alias à une colonne grâce au mot clé :

{"col_as": "" } {"col_as": "as User"}

Trier par ordre croissant/décroissant les lignes

La clause permet de trier les lignes retournées par ordre croissant () ou décroissant ().

{"row_sort": "Age ASC"} {"row_sort": "Age DESC"} {"row_sort": "Age DESC, Nom ASC"}

Selection des lignes

retourne l'intégralité des entrées de la table, ce qui peut représenter des milliers de lignes. Or, bien souvent, seules quelques entrées nous intéressent.

Il est ainsi important de sélectionner uniquement les lignes qui nous intéressent afin de réduire le volume des données transférées, ainsi que de réduire la consommation de ressources serveurs (processeur, RAM, disque).

Il est ainsi possible de sélectionner des lignes de différentes manières :

Clause WHERE

La manière générique de filter les lignes à retourner est d'ajouter une clause à la requête. Seules les lignes pour lesquelles est vraie seront retournées.

{"cond": "Age > 18"} {"cond": "Nom == 'Doe'"}

Opérateurs de comparaisons

💡 La structure d'une condition est très simple, utilisant les opérateurs de comparaison que vous connaissez déjà : , , , , .

{"cond": "ID > 1"} {"cond": "Prenom == Nom"} {"cond": "0 < Age < 18"}

💡 Vous pouvez utiliser les noms de colonnes comme opérandes, même si elles n'apparaissent pas dans les colonnes à retourner.

⚠ Comme vous le constatez dans l'exemple ci-dessus, bien que cela soit syntaxiquement valide, chaîner les opérateurs de comparaisons ne produira pas le résultat attendu.
Vous ne devez ainsi pas écrire, e.g. , mais (cf suite du cours) :

Opérateurs logiques

Vous pouvez composer des conditions à l'aide d'opérateurs logiques que vous connaissez déjà : , , .

{"cond": "Age > 18 AND Age < 45"} {"cond": "Age > 18 OR Age < 45"} {"cond": "NOT ( Age > 18 )"}

💡 Vous pouvez aussi utiliser des parenthèses.

Autres opérateurs

{"cond": "Age IS NULL"} {"cond": "Nom IN ('Doe', 'Nescio')"} {"cond": "Age BETWEEN 0 AND 18"} {"cond": "Nom LIKE 'D%'"}

Supprimer les doublons

La clause permet de supprimer les doublons dans les lignes retournées :

{"row_distinct": ""} {"row_distinct": "DISTINCT"}

Tops et pagination

Tops

La clause permet de ne récupérer que les premières entrées retournées. Souvent utilisée avec la clause , elle permet notamment de générer des tops, e.g. les X utilisateurs les plus jeunes.

{"pagination": "1"} {"pagination": "2"} {"pagination": "1", "orderby": "ORDER BY Age"}

Pagination

Lorsqu'une requête retourne des milliers de lignes, il peut être coûteux de toutes les récupérer et les afficher en même temps. On peut alors découper la liste en plusieurs pages, en affichant que e.g. 50 lignes par pages. Ainsi la première page récupère et affiche les 50 premières lignes, la seconde les 50 suivantes, et ainsi de suite.

Pour cela, on utilisera la clause afin de ne récupérer que 50 entrées par requêtes. En conjonction, on utilisera la clause qui permet de ne pas récupérer les premières entrées retournées. étant alors le numéro de la page actuelle.

{"pagination": "1", "p": "0"} {"pagination": "1", "p": "1"} {"pagination": "1", "p": "2"} {"pagination": "2", "p": "0*2"} {"pagination": "2", "p": "1*2"}

Astuces pour rédiger efficacement les requêtes SQL

  1. Indentez vos requêtes (e.g. en alignant les clauses), elle en devient bien plus lisible (et facile à modifier).
  2. Lorsque vous ouvrez une parenthèse/guillemet/etc. fermez-la immédiatement, puis remplissez son contenu.
  3. Évitez autant que possible d'écrire à la main :
    1. Utilisez l'auto-complétion pour les mots clefs et noms de colonnes (touche de tabulation ).
    2. Utilisez les flèches (/) pour remonter l'historique et (/) pour modifier une requête.
    3. Utilisez les copier/coller (/).
  4. Comme en programmation, ne jamais tout écrire d'un coup, mais procédez par étapes :
    1. commencez par un basique.
    2. affichez les colonnes que vous utiliserez.
    3. ajoutez les nouvelles clauses une par une, en testant à chaque fois.
    4. idem pour les expressions, commencez simple, puis ajoutez les opérations au fur et à mesure.

💡 Cela vous aidera à écrire plus simplement des requêtes SQL avec méthode, en transformant un problème complexe, en plusieurs petits problèmes simples. De surcroît, en cas d'erreur, vous saurez immédiatement son origine, et sera alors bien plus facile à déboguer et corriger.

Ordre d'exécution et d'écriture

Le différentes clauses des requêtes SQL sont généralement écrites dans leur ordre d'exécution, et exécutées dans leur ordre d'écriture, i.e. de gauche à droite.

Par exemple lors d'une requête :

  1. Récupère la table (clause ).
  2. Défini les alias de colonnes.
  3. On filtre les entrées (clause ).
  4. Groupe les entrées en lignes
    (clause ).
  5. Défini les alias de colonnes d'agrégats.
  6. Filtre les lignes agrégées
    (clause ).
  7. Supprime les doublons
    (clause ).
  8. Trie les lignes (clause ).
  9. Limite le nombre de lignes retournées
    (clauses /).
SELECT DISTINCT Date, Ref, Q as Nb
FROM Produits
WHERE Ref = "Gomme"
ORDER BY Q
LIMIT 2;

💡 Les étapes en gris seront étudiées au CM suivant.

⚠ En rouge sont indiqués des cas d'exceptions dont l'ordre d'écriture ne correspond pas à leur ordre d'exécution. Une autre exception existe dans les requêtes où la clause s'écrit avant la clause .