/ SQLite

Tutoriel SQL avec SQLite

SQL est un peu partout, et je ne dis pas ça parce que je l'utilise. C'est juste un fait. Tous les smartphones Android, ou iPhone utilisent ou accèdent à des base de données SQL ou SQLite et beaucoup d'applications de vos téléphones les utilisent directement. Même quand il s'agit de banques, d'hôpitaux, d'universités, de gouvernements, de petites entreprises ou de grandes, c'est juste que tout ordinateur et tout individu sur cette planète à un lien avec SQL.

SQL a un incroyable succès, et est à part entière, une technologie plus que solide sur laquelle on peut compter.

Table des matières

  1. Introduction

  2. Installation et configuration

  3. Exercices

    1. Exercice 1 - Création d'une table
    2. Exercice 2 - Créer plusieurs tables
    3. Exercice 3 - Insérer des données

Introduction

Mais SQL c'est quoi plus précisément?

On prononce SQL de deux manières :

  1. "sequel" (pour les fans de cinémas ;-) )
  2. mais on peut aussi dire, et c'est plus commun, "ESS-QUEUE-ELL" (/ˈɛs kjuː ˈɛl/).

SQL signifie surtout Structured Query Language mais aujourd'hui tout le monde s'en fiche un peu, puisque les gens l'utilisent d'un point de vue marketing uniquement. Ce que SQL offre à la base, c'est un langage rationnel (mathématiques) permettant d'interagir avec les données d'une base de données. Bien que son intérêt premier réside dans le fait qu'il soit si proche d'une théorie mathématique datant de plusieurs années, définissant les propriétés de structures de données bien définies (Théorie des langages).
Aujourd'hui, ce n'est plus tout à fait le cas, au grand regret des puristes. Le langage reste cependant suffisamment proche pour être super utile.

Comment SQL oeuvre-t-il pour comprendre les différents champs qui sont dans des tables, et comment trouver les données de ces tables en fonction du contenu de ces champs? Toutes les opérations SQL se résument tout simplement à quatre choses que vous faites généralement sur les tables d'une base de données :

  • Create : créer des données dans une table ou des tables.
  • Read : lire, interroger, chercher des données des tables.
  • Update : mettre à jour, en changeant le contenu des données déjà existantes dans une table.
  • Delete : effacer, des données existantes dans une table.

On a donnée à ces 4 opérations, l'acronyme "CRUD" et cela est considéré comme l'ensemble de fonctionnalités fondamentales qu'un système de stockage de données doit posséder.

En fait, si jamais, vous ne pouvez pas faire une seule de ces opérations, alors il vaudrait mieux qu'il existe une très très bonne raison à ça.

Une manière que je trouve élégante pour expliquer comment SQL fonctionne, est en faisant l'analogie avec un logiciel de type "tableur" ou "feuille de calcul" comme Excel :

  • Une base de données c'est un fichier, comme un tableur complet avec tous ces onglets.
  • Une table de la base de données, c'est comme un seul onglet du tableur (une seule feuille de calcul), et pour chacune, on a donnée un nom spécifique et différent.
  • Une colonne, c'est une colonne pour le tableur ou la base de données.
  • Une ligne c'est une ligne pour le tableur ou la base de données.

SQL vous offre alors un langage permettant de faire des opérations CRUD sur ces tables pour produire de nouvelles tables ou altérer les existantes.

Ce dernier point est super super important et ne pas comprendre ça est la raison pour laquelle la plupart des gens finissent assez vite avec un mal de crâne (pour être poli).
SQL ne connaît que les tables, et rien d'autre. Il ne sait travailler qu'avec ça, et ne produira rien d'autre que ça. Ainsi, toute opération produira comme résultat, une table.
Au choix, il produira une table en en modifiant une existante ou il en retournera une nouvelle, temporaire, contenant votre ensemble de données.
Donc si vous demander les résultats d'une rechercher, alors SQL crééera sûrement une table temporaire avec pour chaque ligne, un résultat correspondant à votre recherche.

Vous commencez sûrement à présent à comprendre la signification de ce design. Par exemple, une des raisons pour laquelle les langages orientés objets (Object Oriented Languages) ne sont pas semblables avec les bases de données SQL c'est que les langages de programmation orienté objet (Object Oriented Programming OOP) soint organisés à travers des graphes, et que SQL ne peut retourner que des tables. Puisqu'il est presque possible de pouvoir trouver une correspondance entre un graphe et une table et vice-versa dans tous les cas, ça marche, mais ça met beaucoup de travail de la part du langage de POO, pour effectuer la traduction de l'un vers l'autre.

Un autre point quelque peu problématique avec les concepts SQL, ce sont les relations ternaires et les relations d'attributs, que les langages de POO ne peuvent comprendre.
En SQL, je peux créer 3 tables liées entre elles par une 4ème table. La quatrième table est alors une table de cohésion avec les trois autres. Pour réaliser la même chose en programmation orientée objet, je devrais créer une 4ème classe pour faire une chose équivalente.

Je pense que vous vous dites qu'ils s'agit d'incantations magique à l'heure actuelle, mais d'ici peu, après quelques unes des lectures qui suivent, vous comprendrez les problématiques, et saurez comment les appréhender.

Installation et configuration de l'environnement

Nous allons utiliser SQLite3 comme outil d'entrainement. SQLite3 est un système de base de données complet qui offre l'avantage de ne nécessiter aucune configuration particulière afin de pouvoir l'utiliser. Donc, vous téléchargez le binaire (programme) et vous pouvez l'utiliser comme la plupart des langages. Autant dire qu'un débutant peu très vite commencer à utiliser une base de données, et apprendre le SQL sans rentrer dans l'ensemble des procédures d'administration d'un serveur de bases de données.

Installation du binaire

L'installation de SQLite3 est simple :

Vous pouvez télécharger SQLite3 directement sur le site web, sur le Site de téléchargement des binaires SQLite3 pour votre plateforme (Windows, Mac, Linux, etc). Cherchez des binaires pré-compilés pour votre système d'exploitation :

Vérification de l'installation

Une fois installé, nous allons nous assurer que nous pouvons rentrer une ligne de commande et exécuter ce binaire.

Voici un petit test :

$ sqlite3 test.db
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test (id);
sqlite> .quit

Regardez ensuite le fichier test.db soit bien présent sur votre disque via la commande ls -l, et vous devriez avoir un résultat à peu près similaire, indiquant la présence du fichier test.db:

$ ls -l
total 1944
-rw-r--r--@ 1 vaseltior  staff  338123 Feb  9 14:58 sqlite-shell-osx-x86-3071502.zip
-rwxr-xr-x@ 1 vaseltior  staff  645988 Jan  9 14:54 sqlite3
-rw-r--r--  1 vaseltior  staff    8192 Feb  9 15:07 test.db

Vous devriez vous assurer, avant d'aller plus loin, que la version de SQLite3 que vous utilisez est bien supérieure ou égale à la version 3.7.8. Quelque fois les choses ne fonctionnent pas très bien avec les versions antérieures.

Pour vérifier votre version de sqlite :

$ sqlite3 --version
3.7.12 2012-04-03 19:43:07 86b8481be7e76cccc92d14ce762d21bfb69504af

3.7.12, on est bon, nous sommes en 3.7.12, et nous sommes bien supérieur à 3.7.8.

Outils complémentaires dont vous aurez besoin

Vous aurez également besoin pour continuer, des outils suivants :

  • Un éditeur de texte basique. Utilisez celui que vous voulez.
  • Etre familiarisé des lignes de commandes (aka Terminal, aka cmd.exe). Vous lancerez vos commandes à partir de cela. Si aujourd'hui vous n'êtes pas sûr à 100% de pouvoir maîtriser les outils de terminal. Sinon, vous en apprendrez certains, via ce tutoriel.
  • Une connection internet avec un navigateur interner de manière à pouvoir rechercher dans la documentation, et rechercher les choses que je vous demanderais de chercher.

Une fois que vous avez tout ça, nous sommes prêt. Allons-y!

Exercice 1 - Création d'une table

Créer des tables

En introduction, j'ai expliqué que l'on pouvait exécuter des operations de Création, Lecture, Mise à jour, et Suppression sur les données des tables. En anglais, cela nous donne les termes suivants, respectivement :
Create, Read, Update, Delete

Alors, ok, comment fait-on pour créer une table?

On fait cela en réalisant des opérations CRUD, non pas sur la table elle-même, mais sur le schéma de la base de données. Cela nous permettra par la suite, avec nos tables, d'effectuer des operations CRUD sur le contenu des tables elles-mêmes.

Et la première instruction SQL que nous allons apprendre est CREATE. En anglais nous utilisons le terme statement pour faire référence aux instructions.

Donc prenez votre editeur de texte préféré (Textedit, TextWrangler, Smultron, Coda, etc).

Liste d'éditeurs de texte (gratuits, et payants).

Vous pourriez très bien écrire cette instruction sur une seule ligne, elle aurait été tout aussi valide, mais… si on veut parler d'une ligne en particulier et la décrire, ça devient difficile d'identifier de quoi l'on parle. Donc on utilisera plusieurs lignes, et nous y verrons tous plus clair.

Code

Donc ouvrez votre éditeur, copiez-y le code suivant et sauvegardez ce fichier sous le nom exercice-1.sql. Dans un premier temps, je vous conseille de sauvegarder ce fichier dans le même dossier que celui où vous avez placé le binaire sqlite3 :

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

Signification

Voici la signification de chacune des lignes de l'instruction de création :

  • ligne 1 : "CREATE TABLE" demande de création d'une table, dont le nom de cette table est spécifiée par person, on place alors entre parenthèses tout ce qui permettra de définir la structure de cette table.
  • ligne 2 : Une colonne id qui permettra d'identifier de manière unique chaque ligne du tableau. Le format d'une colonne est le suivant :
    • COLUMN-NAME (un nom pour la colonne), dans notre cas id
    • COLUMN-TYPE (un type de données), dans notre cas, nous sommes en train de dire que nous voulons un type entier INTEGER (les nombres de type 0, 1, 2, … 50, etc.)
    • COLUMN-CONSTRAINT (les contraintes appliquées sur la colonne), dans notre cas, nous précison que nous nous servons de cette première colonne comme une clef primaire, PRIMARY KEY . Il s'agit d'une clef unique qui permet d'identifier de manière unique chaque ligne de notre tableau.
  • lignes 3 et 4 : Nous décrivons 2 colonnes de notre table qui seront toutes les deux de type TEXT car elles contiendront du texte afin de stocker les informations de prénom et nom de la personne; first_name et last_name, respectivement.
  • ligne 5 : Une dernière colonne de type INTEGER qui permettra de stocker l'âge de la personne.
  • ligne 6 : Cette dernière ligne, clôture la description des colonnes qui structureront la table. Cela ce fait grâce à la parenthèse fermante ) qui vient préciser que la fermeture, et l'adjonction d'un point-virgule ; après celle-ci qui spécifie que l'instruction de création est s'arrête à cet endroit.

Résultats attendus

Le moyen le plus simple d'exécuter ceci serait d'exécuter la commande suivante :

$ sqlite3 exercice-1.db < exercice-1.sql

Cela aurait pour conséquence de demander au binaire sqlite3 d'utiliser, ou créer si elle n'existe pas, la base de données exercice-1.db, et qui plus est, de lui injecter directement via < l'intégralité du contenu du fichier exercice-1.sql
que vous venez de créer.

i.e. Vous demandez à sqlite3 de créer un fichier de base de données et d'y jouer l'instruction de création de la table personne dont vous avez défini la structure des colonnes précédemment.

Listez alors le contenu de votre dossier avec la commande ls -l, et vous devriez avoir votre fichier .sql, et un fichier exercice-1.db.

$ ls -l
-rw-r--r--  1 vaseltior  staff    8192 Feb  9 18:50 exercice-1.db
-rw-r--r--  1 vaseltior  staff     110 Feb  9 18:50 exercice-1.sql

Si vous exécutez une seconde fois le code d'injection (i.e. jouer l'instruction de création), SQLite3 devrait vous répondre très gentiment par une erreur :

Error: near line 1: table person already exists

Donc oui, la première fois vous avez bien créé une table person et cela s'est fait de manière très silencieuse, mais maintenant, si vous demandez exactement la même chose, sqlite vous dit : "Je ne peux pas créer une telle table, car celle-ci existe déjà. Si jamais je m'autorisais à le faire, alors, vu qu'il y a déjà une table qui existe sous ce nom, je ne pourrais qu'altérer l'intégrité de ce qui existe déjà, donc je ne le fais pas."

Exercices pratiques

SQL est un langage quasiment insensible à la casse. Ca ne veut pas dire que vous ne pouvez pas tout péter, ça veut seulement dire que vous pouvez écrire indépendamment le texte de syntaxe en minuscule.

Exercice 1 :

Réécrivez la commande de création de la table person de manière à ce que tout y soit écrit en minuscule. Et vérifier que ça fonctionne toujours.

Pour cela vous aurez besoin de supprimer le fichier exercice-1.db que vous avez créé précédemment, sans quoi vous aurez l'erreur dont je viens de vous parler.

Pour supprimer le fichier, entrez la commande rm (pour remove) suivie du nom du fichier à supprimer :

$ rm exercice-1.db

Exercice 2 :

Ajoutez à cette table, deux colonnes supplémentaires qui pourraient caractériser une personne, avec la recommandation suivante :

  • une colonne de type entier (INTEGER)
  • une colonne de type texte (TEXT)

Divers

Les types utilisés par Sqlite3 sont globalement les mêmes que ceux utilisés dans les autres systèmes de gestion de base de données. Cependant, faites attention car les autres constructeurs de bases de données SQL, afin de se différencier entre eux, ont voulu étendre certains types de données ou se les réappropier.

Donc, en SQLite alors que l'on utilise INTEGER pour référencer un nombre entier, on trouvera en SQL tous les équivalents suivants : INT, SMALLINT, BIGINT, BOOLEAN, etc...

Les pires sont les types primaires de date et heure.

Les dates et heures, ou les encodages en informatique, c'est un peu comme la peste et le choléra.

Exercice 2 - Créer plusieurs tables

Code

Créer une table, n'est pas en soi, super utile. Maintenant, je voudrais que vous créiez 3 tables distinctes afin que vous puissiez commencer à y stocker des valeurs dedans. L'insertion de nouvelles données sera le sujet de l'exercice 3.

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);

CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);

Dans ce fichier, vous créez des tables qui vont vous permettre de stocker deux types de données, et enfin de les lier entre elles avec une troisième table.
On appelle ces liens entre les tables, des relations.

Certains utilisent un peu le mot relation à l'extrème, ce qui peut être psychologiquement déstabilisant au premier abord.

Nous dirons donc que les tables qui ont des données bruts sont des tables et que les tables qui servent à créer des relations, sont des relations.

Donc, par rapport à l'exercice précedent, il n'y a rien de nouveau; sauf que si vous regardez la relation person_pet, vous verrez qu'elle est constituée de deux colonnes :

  • person_id qui fait référence à la colonne id de la table person,
  • pet_id qui fait référence à la colonne id de la table pet.

Vous remarquerez le choix du nom des colonnes de cette table, qui permet de directement préciser son rôle.

Si maintenant vous deviez lier ces deux tables entre elles, cela pourrait se faire en insérant une ligne dans la table person_pet qui possèderait alors les deux identifiants des colonnes des lignes des deux autres tables que vous souhaitez connecter entre elles.

Par exemple :

Si une personne contient une ligne dont l'identifiant est id=12 et un animal de compagnie (pet) dont l'identifiant de ligne est id=32,
alors pour dire que cette personne possède ce chien; il vous suffirait d'insérer une ligne dans la table de relation person_pet en attribuant les valeurs suivantes, person_id=12 et pet_id=32. Cela aura pour conséquence de marquer le lien entre l'animal de compagnie identifié par 32 et la personne identifiée par 12.

Vous allez en fait insérer des données comme celles-ci dans les quelques prochains exercices.

exercice 2 - Schéma

Ce que vous devriez obtenir

Lancer ce script SQL de la même manière que précédement, mais en spécifiant cette fois-ci le fichier exercice-2.db plutôt que exercice-1.db. Comme la fois précédente, vous ne devriez avoir aucun retour en sortie d'exécution, si tout c'est bien passé.

$ sqlite3 exercice-2.db < exercice-2.sql
$ ls -l

Cette fois-ci, j'aimerais que vous ouvriez la base de données que vous venez de créer afin de vous assurer que le schéma qui a été généré suite à vos instructions de création, correspond bien à ce que vous souhaitiez.

Pour cela, lancez l'exécution du binaire sqlite3 et utilisez la commande sqlite suivante pour afficher le schéma : .schema.

$ sqlite3 exercice2.db

Le "schema" devrait correspondre à ce que vous avez entré.

sqlite> .schema
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);
CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);
CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);
sqlite>

Utilisez la commande .quit pour quitter sqlite3.

Exercices supplémentaires

  • Dans ces tables, j'ai commencé par créer une troisième table de relation pour lier les deux autres.
    • Comment pourriez-vous vous débarrasser de cette table de relation person_pet afin que cette information soit accessible directement depuis la table person?
    • Quel est l'implication d'un tel changement?
  • Si vous pouvez ajouter une ligne dans la table person_pet, pouvez-vous en ajouter plus d'une? Comment feriez-vous pour enregistrer des informations sur une dame qui est fana des chats et qui en possède 50?
  • Créez une autre table pour les voitures que pourraient posséder les personnes, et créez également sa table de relation.

Recherchez les différents types de données de sqlite3 dans votre moteur de recherche préféré et lisez le document "Datatypes In SQLite Version 3". Prenez note des différents types de données que vous pouvez utiliser, ainsi que les autres choses qui vous paraissent importantes. Nous traiterons ce sujet dans un autre post.

Réponses aux questions d'exercice

TODO

Divers

Les bases de données ont plein d'options permettant de spécifier les clefs de ces relations, mais pour l'instant, nous allons garder ce genre de choses très simple.

Exercice 3 - Insérer des données

Insérer des données en SQL

Maintenant, nous avons quelques tables avec lesquelles nous pouvons réellement travailler.

INSERT INTO person (id, first_name, last_name, age) 
    VALUES (NULL, "Samuel", "Grau", 33);

INSERT INTO pet (id, name, breed, age, dead)
    VALUES (NULL, "Photon", "Chien", 10, 0);

INSERT INTO pet 
	VALUES (NULL, "Gigantor", "Robot", 1, 1);

Explication des commandes

TODO

Résultats attendus

TODO

Exercices supplémentaires

Solutions

Divers