Vous pouvez me contacter via Facebook pour questions & suggestions :
Page Facebook relative à mon site
Les Bases De Données s’imposent comme une façon efficace de stockage de données. Il est alors plutôt aisé d’interagir avec celles-ci en
utilisant un Système de Gestion de Base de Données (SGBD).
Un SGBD est un logiciel spécialement conçu pour manipuler les bases de données à l’aide d’un langage normalisé :
le SQL (Structured Query Language).
Nous utiliserons SQLite car il est beaucoup plus simple à mettre en œuvre que les autres :
pas nécessaire d'installer un serveur de bases de données sur votre PC (à la différence de mySQL).
Comme mySQL, SQLite est un SGBD de type relationnel ce qui signifie que les tables d'une même base peuvent être reliées entre-elles (jointures)
et qu'au sein d'une table toutes les lignes ont la même strucure.
Une base de données a un nom et comprend une ou plusieurs tables. Elle comprend aussi éventuellement des vues.
Un table c'est comme un tableau : des lignes et des colonnes. Les colonnes sont aussi appelée champs ("fields") et les lignes sont
aussi appelées enregistrements ("records").
A l'intersection d'une colonne et d'une ligne se trouve une cellule contenant une donnée.
Chaque ligne décrit un individu (au sens statistique du terme) : un salarié, un client, un contact, un article du catalogue, un patient, etc.
Tous les individus sont donc décrits de la même façon.
Nous devons gérer les espèces animales d'un biotope marin.
Une espèce a un nom et appartient à un taxon (et un seul). Je rappelle qu'un taxon est un groupe d'animaux "cousins" (qui ont un ancêtre commun). Les taxons peuvent avoir des niveaux hiérarchiques
différents : embranchement, classe ou ordre.
Ainsi "éponges & cténaires" sont des embranchements tandis que "bivalves, gastéropodes, céphalopodes" sont des classes de l'embranchement "mollusques".
Mais quand on dit que telle espèce est un gastéropode on sous-entend naturellement qu'il s'agit d'un mollusque.
La base de données se nommera "animaux.db". Elle comprendra donc deux tables :
Ce que doit contenir la table taxons (extrait) :
Une ligne (ou enregistrement) par taxon.
Ce que doit contenir la table "especes" (extrait) :
1 homard deca 2 orange de mer epon 3 fesses d'éléphant epon 4 crevette grise deca 5 bouquet deca 6 étrille deca ...
Remarque : une ligne (ou enregistrement ou "record") par espèce.
Dans toute table une colonne (et une seule) doit faire fonction de clé primaire.
Il ne peut y avoir de doublons pour cette colonne. Il y aura automatiquement création d'un index sur cette colonne. Donc la recherche
d'une ligne sera extrèment rapide : recherche indéxée.
Il y a une colonne commune entre les deux tables ; elle porte d'ailleurs le même nom : "code" (mais ce n'est pas obligatoire).
Cette colonne commune aux deux tables est appelé "champ de jointure". En effet elle permet de mettre en relation les deux tables.
Ainsi à chaque enregistrement de "espèces" on pourra associer une ligne (et une seule) de "taxons".
A contrario pour chaque ligne de "taxons" on peut associer 0 à N lignes de "especes".
On dit que "taxons" est mère par rapport à "espèces" OU que "espèces" est fille par rapport à "taxons".
Le langage SQL peut paraitre difficile pour un débutant. Aussi nous allons créer et remplir les tables de notre base SQLite avec un utilitaire intitulé DB Brower for SQLliste. Ce programme est est l'équivalent de PHPMyAdmin pour MySQL.
En fait cette interface génère du SQL à partir de tous les renseignements que vous lui fournissez dans le cadre de grilles.
Disons qu'avec DB browser vous allez découvrir en douceur le SQL.
Il suffit de se rendre dans la page de téléchargement du site sqlitebrower.org : https://sqlitebrowser.org/dl/
Nous allons employer cette interface pour créer la base de données "animaux.db" et les différentes tables.
Il faut maintenant créer chacune des deux tables (définir leur structure) puis leur ajouter des données (insérer des enregistrements).
Il faut donc générer pour chaque table deux requêtes SQL : une de type CREATE TABLE ... et plusieurs de type INSERT INTO ...
Nous allons toujours faire appel à DB browser pour créer et remplir les deux tables.
Cliquez sur "Créer une table".
Vous remplissez la fenêtre qui s'affiche comme suit :
Remarque : différence et U et CP : dans les deux cas les doublons pour cette colonne sont impossible. Mais avec CP il y a création d'un index. Ce qui n'est pas le cas avec U.
Pour la table "taxons" il n'y a que deux colonnes à définir.
Les deux colonnes sont de type "text". La première est clé primaire (pour identifier de manière unique un taxon).
Mais comme le type est TEXT, l'autoincrémentation est bien sûr impossible.
Observons attentivement le code SQL généré :
La table "taxons" existe désormais (a une structure) mais est vide de toute données. Il faut désormais y ajouter des lignes (ou enregistrements).
Sélectionnez la table "taxons" à partir de la fenêtre "parcourir les donnees"
puis cliquez sur l'icône "insérer un nouvel enregistrement dans la table" : vous pointez alors la fin de table. il suffit de remplir la ligne
vierge du tableau.
Recommencez la procédure autant de fois que nécessaire.
Après avoir saisi plusieurs lignes dans la table "taxons" vous pouvez trier par valeurs croissantes/décroissantes les noms des taxons. Il suffit à partir du contenu sous forme d'un tableau (fenêtre "parcourir les données) de cliquer sur l'étiquette de colonne.
Attention le code SQL de création de la table "especes" est plus complexe que pour la table "taxons".
Il y a effet une contrainte d'intégrité référentielle à poser. On ne doit pouvoir saisir dans la colonne "especes.code"
qu'une valeur qui existe déjà dans la colonne "taxons.code". Il faut définir la colonne "especes.code" comme clé étrangère par rapport à
"taxons.code".
CREATE TABLE "especes" ( "id" INTEGER NOT NULL, "nom_espece" TEXT NOT NULL, "code" TEXT NOT NULL, PRIMARY KEY("id" AUTOINCREMENT), FOREIGN KEY("code") REFERENCES "taxons"("code") )
"id" INTEGER NOT NULL : le champ nommé "id" doit contenir un entier,
une cellule de cette colonne ne peut rester vide.
"nom_espece" TEXT NOT NULL : le champ "nom_espece" doit contenir obligatoirement du texte, pas de cellule vide
"code" TEXT NOT NULL : même remarque que pour le champ précédent
PRIMARY KEY("id" AUTOINCREMENT) : la colonne "id" sert de clé primaire (doublons interdits, indexation sur cette colonne.
La commande AUTOINCREMENT est utilisée dans le langage SQL afin de spécifier qu'une colonne numérique sera remplie automatiquement à
chaque ajout d'enregistrement. C'est le SGBD qui affecte un entier à la cellule en fonction de l'enregistrement précédent :
incrémentation.
FOREIGN KEY("code") REFERENCES "taxons"("code") : la colonne "code" de la table 'especes" est clé étrangère par rapport à la colonne
"taxons.code". Ce qui veut dire qu'on ne peut saisir dans la première qu'une valeur qui existe déjà dans la seconde.
À partir de la fenêtre "parcourir les données" sélectionnez "especes" dans la liste déroulante "table" puis cliquez
sur l'icône "insérer un nouvel enregistement".
Notez que c'est désormais une grille de saisie qui apparait (et non pas un tableau). Cette grille est légendée :
"ajouter un enregistrement". Le mode de saisie d'une nouvelle ligne dans une table dépend de l'absence ou de la présence d'une contrainte
d'intégrité.
En haut de la grille et pour chaque champ, il faut saisir la valeur (sauf pour le champ ID qui se remplie automatiquement).
Pour "nom_espece" saisissez "cristatelle" et pour "code" saisissez "bryo" (comme bryozoaires) : un message d'erreur apparait :
"Foreign key constraint failed ..." . La requête échoue ! En effet vous n'avez pas pas respecté l'intégrité référentielle ; la valeur "bryo"
n'existe pas encore dans "taxons.code". Dans un langage imagé on dirait : "pas d'enfant sans mère".
Saisissez alors "mérou" & "osse".
Puis observez le code SQL que vous avez généré (bas de la grille):
INSERT INTO "main"."especes" ("nom_espece", "code") VALUES ('mérou', 'osse');
Cette fois l'insertion est autorisée puisque la valeur "osse" (poissons osseux) existe dans la colonne "taxons.code".
Dons si vous voulez ajouter des espèces appartenant au taxon des "bryozoaires" il faudra d'abord créer ce groupe dans la table "taxons" puis saisir les espèces de ce groupe dans la table 'especes".
Après avoir saisi plusieurs lignes dans la table "especes" vous pouvez trier par valeurs croissantes/décroissantes les codes et les noms d'espèces. Il suffit à partir du contenu sous forme d'un tableau (fenêtre "parcourir les données) de cliquer sur l'étiquette de colonne ad hoc.
Les requêtes sélection ne modifient pas les données, à la différence des requêtes action telles INSERT INTO ... Elles se contentent d'afficher des données extraites d'une ou plusieurs tables avec éventuellement tri et filtage.
De la table "especes" : SELECT * FROM especes
De la table "taxons" : SELECT * FROM taxons
* veut dire "afficher toutes les colonnes" de la table
Attention il faut taper les requêtes donc utiliser la fenêtre "Exécutez le SQL".
Colonne "nom_espece" & "code" : SELECT nom_espece, code FROM especes
Donc la colonne ID n'est pas affichée.
On affiche seulement les lignes correspondant à la condition de filtrage.
Afficher seulement les décapodes (code ="deca") :
SELECT nom_espece, code FROM especes WHERE code ="deca"
Afficher seulement les poissons osseux (code ="osse") :
SELECT * FROM especes WHERE code ="osse"
Utiliser l'opérateur LIKE :
SELECT * FROM especes WHERE nom_espece LIKE "h%"
On affiche seulement les espèces dont le nom commence par la lettre "h".
LIKE s'associe à "%" qui remplace n'importe qu'elle chaîne de caractères et "_" qui remplacement un seul et unique caractère.
SELECT * FROM especes WHERE id BETWEEN '1' AND '10'
On affiche uniquement les enregistrements dont l'id est compris entre 1 et 10, donc le début de table
Exemple :
SELECT nom_espece, code FROM especes ORDER by code
Dans la liste retournée, les lignes sont triées par valeurs croissantes pour la colonne code.
SELECT * FROM especes WHERE code ="osse" ORDER by nom_espece
Dans la liste retournée, seuls les poissons osseux figurent et ils sont ordonnées par nom.
Attention la clause WHERE doit précéder la clause ORDER.
L'ordre physique des enregistrements dans la table n'est pas modifié par les tris et filtrages des requêtes SELECT
Nous voulons associer à chaque ligne de "especes" un enregistement (et un seul) de "taxons".
Entre "especes" et "taxons" nous avons un champ commun qui porte le même nom dans les deux tables : "code".
Nous pouvons donc poser une "jointure" entre ces deux tables.
SELECT * FROM especes, taxons WHERE especes.code = taxons.code
La requête retourne autant de lignes que d'enregistements dans la table "especes".
Pour lever toute ambiguïté il faut employer les noms complets des champs "code" (nomtable.nomColonne) : "especes.code" d'une part
et "taxons.code" d'autre part.
Le rendu (extrait) :
1 homard deca deca décapodes 4 crevette grise deca deca décapodes 5 bouquet deca deca décapodes 6 étrille deca deca décapodes 7 tourteau deca deca décapodes 8 araignée de mer deca deca décapodes 9 bulot gast gast gastéropodes ...
Pour chaque ligne on a deux fois la même info ...
SELECT nom_espece, taxons.code, nom_taxon FROM especes, taxons WHERE especes.code = taxons.code
Cette fois on affiche que les champs précisés après le mot SELECT : le code n'est affiché qu'une fois !
Le rendu (extrait) :
homard deca décapodes crevette grise deca décapodes bouquet deca décapodes étrille deca décapodes tourteau deca décapodes araignée de mer deca décapodes bulot gast gastéropodes ...
Nous avons utilisé le mot clé WHERE pour poser une jointure entre deux tables et si nous voulions aussi effectuer un filtrage en plus
on serait bien ennuyé ...
Il est donc préférable d'utiliser l'expression INNER JOIN pour poser une jointure entre deux tables.
SELECT nom_espece, taxons.code, nom_taxon FROM especes INNER JOIN taxons ON especes.code = taxons.code
Nous allons donc la sauvegarder la requête sélection précédente sous forme d'une vue.
A partir de la fenêtre "Exécuter le SQL" cliquez sur l'icône légendé "Enregistrer la vue des résultats". Nommons la vue "vue1".
Donc une vue c'est tout simplement une requête sélection sauvegardée car elle est complexe à recréer.
SELECT nom_espece, taxons.code, nom_taxon FROM especes INNER JOIN taxons ON especes.code = taxons.code WHERE nom_espece BETWEEN "b%" AND "m%" ORDER BY nom_espece
Une requête multitables avec en plus filtrage et tri.
Le rendu (totalité) :
balane cirr cirripèdes bouquet deca décapodes bulot gast gastéropodes calmar ceph céphalopodes cigale de mer deca décapodes cione asci ascidies comatue crin crinoïdes coque biva bivalves crabe chinois deca décapodes crevette grise deca décapodes crépidule gast gastéropodes dugong sire siréniens fraise de mer asci ascidies homard deca décapodes huitre creuse biva bivalves lamantin sire siréniens langouste deca décapodes langoustine deca décapodes lion de mer pinn pinnipèdes
Affichage des espèces dont le nom commence par les lettres "b" à "m" classées par odre alphabétique.
Il est préférable, vu sa complexité, de la sauvegarder sous forme d'une vue : "vue2".
Parmi les requêtes SELECT il y a les requêtes statistiques.
Exemples :
Ces requêtes ne retournent qu'une ligne affichant un entier (le nombre de lignes de la source).
Dans la table "espèces" et pour chaque valeur de code il y a plusieurs lignes (plusieurs espèces par taxon).
SELECT code, COUNT(*) FROM especes GROUP BY code
Le rendu (extrait) :
... biva 6 cart 2 ceph 3 cirr 2 crin 1 deca 11 ...
Nous pouvons dire qu'il y a 4 biva(lves), 2 cart(ilagineux), etc.
Cette requête statistique comprend autant de lignes que de groupes ; requête statistique par groupe.
Ces deux requêtes retournent respectivement la valeur maxi et mini dans la colonne ID.
Remarque : suite à des suppressions de lignes, il peut y avoir des trous dans la numérotation : un
enregistrement ne changera jamais d'ID même si des lignes qui le précédaient ont été supprimées.
a titre d'exemple la valeur maxi de ID dans la table "especes" est de 56 alors que le nombre de lignes est 40.
En effet de nombreuses lignes ont été supprimées dans cette table : les espèces de poissons osseux et les éponges (avant de rajouter le mérou).
Ne confondez pas les suppressions de tables (commande DROP TABLE nomTable en SQL) avec les suppressions de lignes dans une table (DELETE FROM nomTable WHERE condition en SQL).
Mais attention dans le cadre de l'intégrité référentielle on ne peut pas supprimer "une mère tant qu'elle a des filles".
Par contre rien n'interdit de supprimer des lignes dans la table "fille".
Essayez la commande : delete from especes where code ='epon'.
C'est un succès
Testez la commande DROP TABLE taxons
À cause (ou plutôt grâce à la contrainte d'intégrité référentielle) entre les deux tables, la requête a échoué !
On ne peut supprimer la table "mères" tant que la table "filles" existe.