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):
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.
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.
- http://www.sqlite.org/lang_corefunc.html
- http://www.sqlite.org/lang_aggfunc.html
- http://www.sqlite.org/lang_expr.html
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 tableOrganismes
? - 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)