Skip to content

Latest commit

 

History

History
259 lines (171 loc) · 10.3 KB

03-SQL1.md

File metadata and controls

259 lines (171 loc) · 10.3 KB
Raw
title author date
Bases de donnees - Conception & Utilisation
Pierre Tocquin <[email protected]>
Octobre 2015

Création de requêtes SQL

% ??SQL?? (??Structured Query Language??) est le langage par excellence pour la manipulation de bases de données relationnelles. Il est défini par une norme depuis près de 30 ans et est reconnu par la plupart des ??SGBD??. Il comprend 2 volets ou sous-langages que sont le ??DDL?? (??Data Definition Language??) et le ??DML?? (??Data Manipulation Language??). Dans le cadre de la formation, nous ne traiterons pas du DDL: son apprentissage est utile mais il n'est pas absolument nécessaire étant donné les possibilités d'automatisation de la production des commandes SQL du DDL, comme cela vous sera illustré avec le logiciel DB-MAIN.

% Le DML est le sous-langage qui permet d'interroger une base de données par le biais de requêtes. Celles-ci sont traduites via une commande unique: SELECT.

% Dans la suite de ce chapitre nous utiliserons une base de données exemple dont le schéma est illustré ci-dessous (Figure \ref{fig31.png}):

Schéma de la base de données modèle. \label{fig31.png}

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

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

sqlite> select nom from proteines;
sqlite> select * from proteines;
sqlite> 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.

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

sqlite> select source from publications order by source desc;

Extraction & sélection de résultats uniques

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

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

sqlite> 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
sqlite> select accession,taxon from genes where taxon 
   ...> in ('4565','4572');
sqlite> select * from auteurs where nom like 'T%';
sqlite> 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??:

sqlite> 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 numériques

Fonctions chaînes de caractères

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

: Fonctions sur les 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.

sqlite> select length(sequence) as Longueur from proteines
   ...> where nom = 'subtilisin';
sqlite> 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 statistiques

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

sqlite> select count(*) as 'Nombre de Subtilisines' from proteines 
   ...> where nom = 'subtilisin';

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

sqlite> select distinct count(taxon) from genes
sqlite> 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)