Requêtes simples

Création de requêtes SQL

Le DML est le sous-langage qui permet de modifier le contenu d’une base de données (insertion, modification, délétion) ou d’interroger une base de données par le biais de requêtes.

Dans ce chapitre, et le suivant, nous allons nous concentrer sur les requêtes qui permettent d’interroger le contenu des bases de données. Celles-ci sont traduites via une commande unique: SELECT.

Dans la suite de ce chapitre nous utiliserons une base de données exemple téléchargeable ici dont le schéma est illustré ci-dessous (fig. 1):

Figure 1: Schéma de la base de données modèle.
Figure 1: Schéma de la base de données modèle.

Le SGDB que nous utiliserons est SQLite. Il s’agit d’un moteur de base de données relationnelle accessible par le langage SQL. Sa particularité est que la base de données (déclarations, tables, index et données) est stockée dans un seul fichier indépendant de la plateforme. Il est donc extrêmement facile à manipuler.

Vérifier la présence de SQLite sur votre système en lançant la commande sqlite3. Pour l’installer, il suffirait de lancer: sudo apt-get install sqlite3. Toutes les requêtes SQL peuvent être introduites directement dans le shell SQLite. Cependant, si vous souhaitez profiter d’une interface graphique, vous pourriez installer sudo apt-get install sqliteman.

La base de données modèle vous est fournie sous la forme d’un fichier unique que vous pouvez ouvrir directement dans SQLiteman ou via la ligne de commande:

SQLite 3

$ sqlite3 nom_du_fichier

Comme à chaque fois, je vous recommande de parcourir l’aide .help ou de visiter le site très bien documenté de SQLite.

Remarque

Vous constaterez que les commandes liées à la gestion de la base de données sont précédées d’un . (obligatoire). Ce n’est pas le cas des requêtes SQL, mais qui sont, elles, obligatoirement terminées par un ;.

Requêtes élémentaires

Extractions simples

select ... from ...

select nom from proteines;
select * from proteines;
select accession,nom from proteines;

Extraction & sélection

select ... from ... where *condition*

La condition peut être traduite par l’utilisation d’opérateur de comparaison comme =, >, <, >=, <=, <> (différent). D’autres conditions de sélection plus complexes seront abordées plus loin.

select * from proteines where nom='hypothetical protein';

Tri

Il est possible de trier les résultats d’un requête en utilisant la commande order by ... combinée à asc ou desc pour définir le sens du classement.

select source from publications order by source desc;

Extraction & sélection de résultats uniques

Lorsque l’on procède à la requête suivante :

select taxon from genes;

on constate, logiquement, que le même taxon apparaît plusieurs fois. Si on souhaite afficher les taxons présents dans la base de manière non redondante, on utilise le type de requêtes suivant :

select distinct … from … where …

select distinct taxon from genes;

Conditions complexes

Comme mentionné plus haut, la clause where accepte des opérateurs permettant des comparaisons complexes:

  • is null, is not null
  • in, not in (liste)
  • between … and … , not between … and …
  • like (_ = 1 caractère, % = n caractères, escape)
  • where … (and, or, not) … ! emploi de parenthèses quand nécessaire
select accession,taxon from genes where taxon
   ...> in ('4565','4572');
select * from auteurs where nom like 'T%';
select taxon from organismes where commun is not null;

Données dérivées

Renommage des données extraites

Il est souvent utile de pouvoir renommer le nom d’un champs soit pour rendre plus clair le résultat, soit pour faire référence au résultat au sein même de la requête (voir plus loin). Pour cela, il faut utiliser la commande as:

select nom as Description, gene_accession as Accession
   ...> from proteines where nom = 'subtilisin';

Fonctions SQL

Certaines fonctions SQL ne sont pas supportées par SQLite. Il est donc utile de parcourir la documentation spécifique sur le site SQLite.org.

Fonctions numériques

Il s’agit des opérations de base qui permettent certains calculs sur les données :

Fonctions Actions
+, -, * et / somme, soustraction, multiplication et division
Fonctions chaînes de caractères

Ces fonctions permettent d’extraire, de modifier ou de reformater des chaînes de caractères.

Fonctions Actions
length(ch) donne le nombre de caractères
group_concat(*) concaténation
lower(ch), upper(ch) transforme en minuscules, majuscules
substr(ch, i, j) extrait une chaîne de longueur j hors de ch à partir de la position i
trim(i j) supprime les caractères j aux extrémités de i
trim(ch) équivaut à trim(i ’ ‘)

Elles peuvent s’avérer très utiles pour extraire des informations secondaires, telles que la longueur des séquences.

select length(sequence) as Longueur from proteines
   ...> where nom = 'subtilisin';
select accession, length(sequence) as Longueur from proteines
   ...> where Longueur > 2000;
Fonctions statistiques

Les fonctions statistiques ou agrégatives sont fort utiles pour réaliser des analyses quantitatives sur la base de données.

Fonctions Actions
count(*) nombre de lignes
count(nom de colonne) nombre de valeurs de la colonne
avg(nom de colonne) moyenne des valeurs de la colonne
sum(nom de colonne) somme des valeurs de la colonne
min(nom de colonne) minimum des valeurs de la colonne
max(nom de colonne) maximum des valeurs de la colonne
Exemples
select count(*) as 'Nombre de Subtilisines' from proteines
   ...> where nom = 'subtilisin';

Notez la différence de traitement des 2 requêtes suivantes:

select distinct count(taxon) from genes
select count(distinct taxon) from genes

Exercices

Ecrire et exécuter les requêtes permettant de répondre aux questions suivantes:

  • Combien d’auteurs sont référencés dans la base de données ?
  • Combien d’auteurs portent un nom commençant par un C ?
  • Affichez le nom des protéines présentes dans la base.
    • Combien y en a-t-il ?
    • Affichez-les par ordre alphabétique
    • (identifiez les ’noms’ qui signifient ‘inconnu’)
    • Afficher les numéros d’accession et le nom des protéines dont le nom est connu
    • Afficher les numéros d’accession des protéines inconnues
  • Produisez une table résumant le nombre de numéros de taxon, de noms et de noms communs de la table Organismes.
  • Combien y a-t-il de Parents distinct dans la table Organismes ?
  • Affichez les numéros d’accession des séquences de la table Genes dont la longueur est comprise entre 1000 et 1250 bp.
    • Extraire les 20 premières bases de ces séquences
    • Afficher les en minuscules
    • Afficher celles qui débutent par ATG (2 manières)