Création de tables dans SQLite

Introduction

Dans ce labo, vous apprendrez les compétences fondamentales de la création et de la gestion de tables dans SQLite, un moteur de base de données léger et sans serveur, parfait pour les applications à petite échelle. Vous explorerez les types de données SQLite, créerez des tables avec des clés primaires, appliquerez des contraintes telles que NOT NULL et UNIQUE, inspecterez les structures de table à l'aide de la commande .schema, et supprimerez des tables en toute sécurité. Grâce à des exercices étape par étape dans l'environnement de machine virtuelle LabEx (VM), vous acquerrez une expérience pratique de l'utilisation de l'outil de ligne de commande sqlite3 pour construire et gérer des tables de base de données. Ce labo est conçu pour les débutants, offrant un chemin clair vers la compréhension de la gestion des tables SQLite.

Se connecter à SQLite et créer une table contacts

Dans cette première étape, vous vous connecterez à une base de données SQLite et créerez votre première table nommée contacts. SQLite est un moteur de base de données léger qui stocke les données dans un seul fichier. Vous utiliserez l'outil de ligne de commande sqlite3 pour interagir avec la base de données.

Ouvrez un terminal dans l'environnement de machine virtuelle LabEx (VM). Assurez-vous que vous êtes dans le répertoire de travail par défaut /home/labex/project.
Connectez-vous à une base de données SQLite nommée test.db à l'aide de l'outil sqlite3. Cette commande créera le fichier de base de données s'il n'existe pas déjà.
```
sqlite3 test.db
```
Cette commande ouvre l'interpréteur (shell) SQLite, et vous verrez une invite comme sqlite>, indiquant que vous êtes dans l'environnement SQLite.
Créez une table nommée contacts avec des colonnes pour id, name et phone. Entrez la commande SQL suivante à l'invite sqlite> :
```
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone TEXT
);
```
Cette commande crée une table nommée contacts. La colonne id est un entier et est désignée comme PRIMARY KEY (clé primaire), ce qui signifie qu'elle identifiera de manière unique chaque ligne de la table. Les colonnes name et phone sont toutes deux de type TEXT, qui peut stocker n'importe quelle donnée de type chaîne de caractères.

Décomposons la commande :
- CREATE TABLE contacts : Ceci indique à SQLite de créer une nouvelle table nommée contacts.
- id INTEGER PRIMARY KEY : Ceci définit une colonne nommée id avec le type de données INTEGER (entier). La contrainte PRIMARY KEY garantit que chaque valeur de cette colonne est unique et sert d'identifiant principal pour chaque ligne.
- name TEXT : Ceci définit une colonne nommée name avec le type de données TEXT, qui peut stocker n'importe quelle donnée de type chaîne de caractères.
- phone TEXT : Ceci définit une colonne nommée phone avec le type de données TEXT, qui peut stocker n'importe quelle donnée de type chaîne de caractères.

Insérer des données dans la table contacts

Maintenant que vous avez créé la table contacts, insérons-y des données. Vous utiliserez l'instruction INSERT INTO pour ajouter de nouvelles lignes à la table.

Insérez un nouveau contact dans la table contacts. Exécutez cette commande à l'invite sqlite> :
```
INSERT INTO contacts (name, phone) VALUES ('John Doe', '123-456-7890');
```
Cette commande insère une nouvelle ligne dans la table contacts avec le nom 'John Doe' et le numéro de téléphone '123-456-7890'. La colonne id se verra automatiquement attribuer une valeur unique car il s'agit de la clé primaire (primary key).

Décomposons la commande :
- INSERT INTO contacts : Ceci indique à SQLite d'insérer des données dans la table contacts.
- (name, phone) : Ceci spécifie les colonnes dans lesquelles vous souhaitez insérer des données.
- VALUES ('John Doe', '123-456-7890') : Ceci spécifie les valeurs à insérer dans les colonnes correspondantes.
Insérez un autre contact dans la table contacts :
```
INSERT INTO contacts (name, phone) VALUES ('Jane Smith', '987-654-3210');
```
Cette commande insère une autre ligne dans la table contacts avec le nom 'Jane Smith' et le numéro de téléphone '987-654-3210'.

Récupérer des données de la table contacts

Maintenant que vous avez inséré des données dans la table contacts, récupérons-les. Vous utiliserez l'instruction SELECT pour interroger (query) la table et afficher les données.

Récupérez toutes les données de la table contacts. Exécutez cette commande à l'invite sqlite> :
```
SELECT * FROM contacts;
```
Cette commande sélectionne toutes les colonnes (*) de la table contacts et affiche les résultats.

Résultat attendu (Expected Output) :
```
1|John Doe|123-456-7890
2|Jane Smith|987-654-3210
```
La sortie affiche les deux contacts que vous avez insérés, ainsi que leurs valeurs id attribuées automatiquement.

Décomposons la commande :
- SELECT * : Ceci indique à SQLite de sélectionner toutes les colonnes de la table.
- FROM contacts : Ceci spécifie la table à partir de laquelle vous souhaitez récupérer des données.
Récupérez uniquement les colonnes name et phone de la table contacts :
```
SELECT name, phone FROM contacts;
```
Cette commande sélectionne uniquement les colonnes name et phone de la table contacts et affiche les résultats.

Résultat attendu (Expected Output) :
```
John Doe|123-456-7890
Jane Smith|987-654-3210
```

Créer une table users avec des contraintes

Les contraintes (constraints) sont des règles qui garantissent l'intégrité des données dans une table. Ajoutons des contraintes NOT NULL et UNIQUE à une nouvelle table appelée users.

Créez une table nommée users avec des contraintes. Exécutez cette commande à l'invite sqlite> :
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
Cette commande crée une table nommée users avec les contraintes suivantes :
- username ne peut pas être NULL et doit être UNIQUE.
- email ne peut pas être NULL.
Décomposons la commande :
- CREATE TABLE users : Ceci indique à SQLite de créer une nouvelle table nommée users.
- id INTEGER PRIMARY KEY : Ceci définit une colonne nommée id avec le type de données INTEGER. La contrainte PRIMARY KEY garantit que chaque valeur de cette colonne est unique et sert d'identifiant principal pour chaque ligne.
- username TEXT NOT NULL UNIQUE : Ceci définit une colonne nommée username avec le type de données TEXT. La contrainte NOT NULL garantit que cette colonne ne peut pas être vide, et la contrainte UNIQUE garantit que toutes les valeurs de cette colonne sont distinctes.
- email TEXT NOT NULL : Ceci définit une colonne nommée email avec le type de données TEXT. La contrainte NOT NULL garantit que cette colonne ne peut pas être vide.
- age INTEGER : Ceci définit une colonne nommée age avec le type de données INTEGER.
Maintenant, essayons d'insérer des données qui violent ces contraintes pour voir comment elles fonctionnent. Tout d'abord, essayez d'insérer un utilisateur sans nom d'utilisateur (username) :
```
INSERT INTO users (email, age) VALUES ('test@example.com', 25);
```
Vous verrez une erreur car la colonne username est définie comme NOT NULL.

Résultat attendu (Expected Output) :
```
Error: NOT NULL constraint failed: users.username
```
Cette erreur montre que SQLite applique la contrainte NOT NULL sur username, empêchant l'insertion.
Ensuite, essayez d'insérer un utilisateur avec un nom d'utilisateur (username) en double. Tout d'abord, insérez un utilisateur valide :
```
INSERT INTO users (username, email, age) VALUES ('johndoe', 'john.doe@example.com', 30);
```
Ensuite, essayez d'insérer un autre utilisateur avec le même nom d'utilisateur :
```
INSERT INTO users (username, email, age) VALUES ('johndoe', 'jane.doe@example.com', 25);
```
Vous verrez une erreur car la colonne username est définie comme UNIQUE.

Résultat attendu (Expected Output) :
```
Error: UNIQUE constraint failed: users.username
```
Cette erreur démontre que SQLite applique la contrainte UNIQUE sur username, empêchant les valeurs en double.

Inspecter la structure de la table avec .schema

Vous pouvez inspecter la structure d'une table en utilisant la commande .schema. Cette commande affiche l'instruction SQL utilisée pour créer la table, y compris les noms de colonnes, les types de données et les contraintes (constraints).

Inspectez la structure de la table users. Exécutez cette commande à l'invite sqlite> :
```
.schema users
```
Résultat attendu (Expected Output) :
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
La sortie affiche l'instruction CREATE TABLE pour la table users, y compris les contraintes NOT NULL et UNIQUE sur la colonne username.

La commande .schema est un outil utile pour comprendre la structure de vos tables et vérifier que les contraintes sont définies correctement.
Quittez l'interpréteur (shell) SQLite :
```
.quit
```
Cette commande quittera l'interpréteur SQLite et vous ramènera au terminal Linux.

IMPORTANT : Assurez-vous d'utiliser .exit avant de cliquer sur le bouton "Continue". Sinon, l'étape ne pourra pas être vérifiée car l'historique sqlite ne sera pas enregistré.

Supprimer la table contacts

Vous pouvez supprimer une table de la base de données en utilisant la commande DROP TABLE. Cette commande supprime définitivement la table et toutes ses données, il faut donc l'utiliser avec précaution.

Connectez-vous à la base de données test.db :
```
sqlite3 test.db
```
Supprimez la table contacts. Exécutez cette commande à l'invite sqlite> :
```
DROP TABLE contacts;
```
Cette commande supprime la table contacts et toutes ses données de la base de données.

Attention (Warning) : Cette action est irréversible. Une fois que vous avez supprimé une table, vous ne pouvez pas la récupérer ni récupérer ses données.
Vérifiez que la table contacts a été supprimée en essayant de sélectionner des données à partir de celle-ci :
```
SELECT * FROM contacts;
```
Vous verrez une erreur car la table contacts n'existe plus.
Quittez l'interpréteur (shell) SQLite :
```
.quit
```

Résumé

Dans ce labo, vous avez acquis les compétences fondamentales pour construire et gérer des tables dans SQLite. Vous avez exploré les types de données SQLite, créé des tables avec des clés primaires (primary keys), appliqué des contraintes (constraints) telles que NOT NULL et UNIQUE, inspecté les structures de table à l'aide de la commande .schema et supprimé des tables en toute sécurité. Ces compétences sont essentielles pour travailler avec des bases de données SQLite et créer des applications basées sur les données (data-driven applications).