Admin/Dev

SQL est un language de programmation qui permet de manipuler des données dans une base de données. De nombreuses solutions de gestion de base de données utilisent le language SQL mais ce n'est pas le cas de toutes. Les plus connues sont MySQL et son fork MariaDb, mais aussi PostgreSQL, ou encore SQLite3.

S'ils utilisent tous le même langage, de manière générale, la syntaxe varie selon les spécificités de chacun des systèmes de gestion de base de données.

Nous avons récemment vu comment utiliser SQLite3 sur un Mac, aujourd'hui nous allons voir comment manipuler des données dans une base de données de ce type.

Les commandes SQL ne sont pas nombreuses, mais peuvent être customisées à l'infini. Avec cet article, nous allons voir l'utilisation simple des 4 commandes principales d'SQL : CREATE, INSERT, UPDATE, SELECT.

Dans SQL, les données doivent stockées dans des tables. Dans ces tables, nous trouvons des lignes, et chacune de ces lignes comprend des cellules structurées selon les informations définies dans les colonnes. Globalement, nous retrouvons les informations comme dans un tableur type Excel ou Numbers. La différence avec ces outils, c'est que nous y accédons avec des lignes de commandes, ils peuvent donc être utilisés par d'autres applications ou utilisés via un site internet.

Pour débuter, il est donc nécessaire de créer une première table. Ouvrez donc une base de données SQLite3 vierge, comme vu dans le précédent article, qui nous servira de test.

Nous allons créer une première table qui nous permettra d'enregistrer une liste de personnes, à la manière d'un carnet d'adresses.

Nous devons donc réfléchir à nos besoins, selon ce que l'on souhaite faire avec notre base de données. Pour notre test, nous allons avoir besoin d'enregistrer les noms et prénoms de nos contacts. Comme nous allons avoir besoin de les trier, nous allons mettre les informations dans 2 colonnes différentes. Puis, nous allons aussi avoir besoin d'enregistrer une adresse e-mail, un numéro de téléphone, et une adresse. Vous l'avez compris, je l'espère, si nous voulons jouer avec ces données, il séparer la voie, le code postal et la ville.

Ce n'est pas tout ! Et nous attaquons les subtilités des bases de données, nous allons rajouter une colonne d'index qui nous permettra d'accéder à nos données.

Enfin, la commande pour créer une table est CREATE TABLE. Voici comment nous allons créé notre table contacts.

CREATE TABLE contacts ( 
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    prenom TEXT NOT NULL,
    nom TEXT NOT NULL, 
    email TEXT NOT NULL, 
    telephone TEXT NOT NULL, 
    adresse_voie TEXT NOT NULL,
    adresse_complement TEXT NOT NULL,
    adresse_code TEXT NOT NULL,
    adresse_ville TEXT NOT NULL
);

Si SQLite3 revient sur son prompt, la commande a été acceptée, tout va bien. Si SQLite3 vous renvoie une ligne commençant par

Error:

Il faut revoir votre commande, elle comporte une erreur, et SQLite3 vous aidera à la trouver.

Regardons la structure de cette commande de plus près. La première ligne est notre commande suivie du nom de notre table. Ensuite, nous ouvrons une parenthèse pour définir les colonnes de notre table.

Les définitions sont séparées par des virgules. Elles sont structurées en indiquant en premier, le nom de la colonne. Celui-ci ne doit pas comporter d'espace ni de caractères spéciaux, comme des caractères accentués.

Comme vous pouvez le voir avec l'adresse, j'ai remplacé les espaces par des underscores. Et pour le prénom, j'ai remplacé le é par un e.

C'est d'ailleurs pour cette raison que, personnellement, je préfère nommer mes colonnes en anglais plutôt qu'en français, mais c'est un choix personnel.

Ensuite nous avons le format de la colonne, tout nos champs sont des textes, excepté l'index nommé id, qui est un numérique entier.

Le NOT NULL indique que nos colonnes doivent contenir quelque chose et ne peuvent pas rester vides.

Notre colonne d'index comporte d'autres information. C'est un peu plus compliqué, mais cela fait parti de la gestion SQL des données. PRIMARY KEY, comme son nom l'indique, définit la colonne comme clé primaire. Cela veut dire que les données peuvent être récupérées facilement et sans risque d'erreur à partir de cette colonne. Cette information doit être unique pour chacune des lignes présentes dans notre table.

D'ailleurs l'information AUTOINCREMENT va nous y aider. En effet, notre colonne est un entier qui doit donc être unique. L'attribut AUTOINCREMENT fera en sorte que la valeur de l'index soit définit automatiquement et unique, sans que l'on s'en occupe.

Enfin, nous fermons la commande avec une parenthèse fermante et un point-virgule. Le point-virgule indique la fin d'une commande. Il est possible d'enchainer les commandes en une seule fois, SQLite3 se débrouillera très bien, tant que les points-virgules sont bien marqués.

A noter que la commande peut être envoyées sur une seule ligne, comme cela

CREATE TABLE contacts (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, prenom TEXT NOT NULL, nom TEXT NOT NULL, email TEXT NOT NULL, telephone TEXT NULL, adresse_voie TEXT NOT NULL, adresse_complement TEXT NOT NULL, adresse_code TEXT NOT NULL, adresse_ville TEXT NOT NULL);

Cependant, l'écriture ligne à ligne est plus lisible, et permet de mieux voir l'ensemble des données, et leurs attributs, que nous souhaitons dans notre table.

Regardons le résultat

.tables

le résultat devrait être

contacts

Regardons ce qui se trouve dans notre table. Cela se fait avec la commande SELECT. Voici un exemple simple pour récupérer le contenu de notre table.

SELECT * FROM contacts;

La réponse est rapide, notre table étant vide, la commande ne renvoie rien.

Il faut donc commencer à insérer des données. Regardons immédiatement la commande INSERT, en ajoutant une première personne dans notre table.

INSERT INTO contacts ( 
    prenom, 
    nom, 
    email, 
    telephone,
    adresse_voie,
    adresse_complement,
    adresse_code,
    adresse_ville
) VALUES ( 
    'Tony', 
    'Stark',
    'ironman@avengers.assembly', 
    '',
    'Tour Stark',
    'Dernier étage',
    '10001',
    'New York City'
);

Comme vous pouvez le voir la structure n'est pas très éloignée. Nous commençons par indiquer la commande suivie de la table. Puis, entre parenthèses, nous retrouvons nos colonnes. Ensuite nous indiquons les valeurs que nous insérons de nouveau entre parenthèses.

Facile, non ?

Comme vous pouvez le voir, il est une colonne qui n'est pas indiquée, il s'agit de la colonne d'index. Dans notre configuration, nous avons demandé à SQLite3 de le gérer automatiquement, ce qu'il a fait.

Regardons le contenu de notre table, toujours avec la commande SELECT. Voici le résultat

1|Tony|Stark|ironman@avengers.assembly||Tour Stark|Dernier étage|10001|New York City

Si vous êtes sur de l'ordre de vos colonnes, vous pouvez vous abstenir de les indiquer. Ainsi vous pouvez n'indiquer que les valeurs telles que

INSERT INTO contacts VALUES ( 
    null,
    'Pepper',
    'Pots', 
    'pepper@avengers.assembly', 
    '',
    'Tour Stark',
    'Dernier étage',
    '10001',
    'New York City'
);

Attention, avec cette écriture, il faut indiquer une valeur "null" pour l'index, qui continuera à s'insérer automatiquement. C'est une spécificité de SQLite3. Sur MySQL ou MariaDb, par exemple, il n'est nécessaire de l'inclure, et le système de gestion de base de données insérera tout de même automatiquement l'index.

Tout aussi pratique, il est possible possible d'insérer plusieurs lignes à la fois. Voici un exemple avec deux nouvelles personnes.

INSERT INTO contacts ( 
    prenom, 
    nom, 
    email, 
    telephone,
    adresse_voie,
    adresse_complement,
    adresse_code,
    adresse_ville
) VALUES ( 
    'Scott', 
    'Lang',
    'antman@avengers.assembly', 
    '',
    '',
    '',
    '10471',
    'New York City'
), (
    'Hope',
    'van Dyne',
    'wasp@@avengers.assembly', 
    '',
    '',
    '',
    '10471',
    'New York City'
);

Regardez une nouvelle fois le contenu de votre table, qui devrait contenir 4 lignes.

Si vous avez l'oeil, vous pourrez voir que j'ai fais une petite erreur dans la ligne de la Guèpe. J'ai mis deux arobases dans l'adresse e-mail, et il faut absolument corriger cela.

Pour cela, il faut utilisera la commande UPDATE. Elle va permettre de mettre à jour une ligne existante. Voici notre correction

UPDATE contacts SET 'email'='wasp@avengers.assembly' WHERE id="4";

Regardons la commande en détail, elle débute, comme toujours, par le nom de la commande suivi du nom de notre table. "SET" va indiquer la ou les colonnes à modifier, avec leur valeur. Dans notre exemple nous ne changeons qu'une seule valeur, si nous avions voulu mettre à jour le code postal aussi, voici la commande que nous aurions utilisé

UPDATE contacts SET 'email'='wasp@avengers.assembly', 'adresse_code'='10470' WHERE id="4";

Les couples nom de colonne/nouvelle valeur sont simplement séparés par des virgules. Vous pouvez lancer la commande sans stress, mais tout simplement, il n'y aura aucun changement sur la valeur de l'e-mail puisque nous avions déjà fait le changement.

Enfin, nous indiquons pour quelle ligne nous souhaitons faire la modification. La ligne de la Guèpe étant la 4, nous utilisons l'élément WHERE pour indiquer qu'il faut faire une recherche. Littéralement, cela veut dire que nous mettons à jour les données indiquées pour toutes les lignes dont la valeur de la colonne nommée "id" est 4. Comme notre colonne d'index id est une clé primaire, nous sommes sûrs que la valeur est unique, et donc que nous allons modifié la bonne ligne et seulement celle-ci.

Nous aurions pu lancer la commande

UPDATE contacts SET 'email'='wasp@avengers.assembly' WHERE prenom="Hope";

Mais nous aurions pris le risque de mettre à jour toutes les lignes dont le prénom est Hope. Dans notre exemple, cela aurait fonctionné, car nous n'avons que 4 lignes, que nous connaissons car nous venons de les renseigner. Mais imaginons que nous avons une table disposant 100 lignes ? 1000 lignes ? 10000 lignes ? voir des millions de lignes, il vous est impossible de toutes les connaitre, et c'est d'ailleurs pour cela que nous utilisons l'outil informatique. Vous prendriez donc le risque de modifier plusieurs lignes plutôt que la seule ligne que nous souhaitons. Même si l'on avait indiqué le nom de famille tel que

UPDATE contacts SET 'email'='wasp@avengers.assembly' WHERE prenom="Hope" and nom="van Dyne";

Vous prendriez le risque d'avoir un homonyme et de modifier plusieurs lignes au lieu d'une seule. Vous comprenez peut être mieux désormais pourquoi nous utilisons des identifiants numériques pour identifier nos lignes.

Par contre, cette édition multiple des lignes peut avoir ses avantages et être parfois utile. Par exemple, si nous souhaitons mettre à jour toutes les lignes qui ne disposent pas d'un numéro de téléphone, nous pouvons le faire en une seule commande, plutôt que de chercher toutes les lignes et de les mettre à jour une par une.

UPDATE contacts SET 'telephone'='Inconnu' WHERE telephone="";

Regardez désormais votre table.

1|Tony|Stark|ironman@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
3|Scott|Lang|antman@avengers.assembly|Inconnu|||10471|New York City
4|Hope|van Dyne|wasp@avengers.assembly|Inconnu|||10470|New York City

Toutes les lignes dont le téléphone était vide, c'est à dire toute, ont désormais la valeur "Inconnu" à la place.

Dans le prochain article, nous verrons qu'il est aussi possible de jouer avec SELECT pour obtenir des données optimisées à nos besoins. Nous en profiterons pour aller beaucoup plus loin avec la recherche de ligne avec WHERE. Et oui, nous ne sommes vraiment qu'au début.