Stockage Temporaire SQLite

Introduction

Dans ce laboratoire, nous explorerons le stockage temporaire de SQLite, en nous concentrant sur la création et l'utilisation de tables temporaires. Les tables temporaires fournissent un mécanisme pour stocker des résultats intermédiaires ou des données nécessaires uniquement pour une courte durée au sein d'une connexion de base de données.

Ce laboratoire vous guide à travers la création d'une table temporaire nommée temp_employees en utilisant l'instruction CREATE TEMP TABLE. Vous découvrirez la portée (scope), la durée de vie (lifetime) et les conventions de nommage des tables temporaires, en comprenant comment elles diffèrent des tables régulières. Les étapes suivantes impliqueront le remplissage de la table temporaire avec des données, l'interrogation (querying) des données et la confirmation du nettoyage automatique de la table temporaire lors de la fermeture de la connexion à la base de données.

Créer une Table Temporaire

Dans cette étape, vous apprendrez à créer une table temporaire dans SQLite. Les tables temporaires sont utiles pour stocker des résultats intermédiaires lors de requêtes complexes ou pour conserver des données qui ne sont nécessaires que pendant une courte période. Elles sont automatiquement supprimées lorsque la connexion à la base de données est fermée.

Tout d'abord, ouvrez votre terminal dans la VM LabEx. Votre chemin d'accès par défaut est /home/labex/project.

Maintenant, créons une base de données SQLite nommée employees.db. Exécutez la commande suivante pour créer le fichier de base de données et ouvrir l'outil de ligne de commande SQLite :

sqlite3 employees.db

Vous verrez une invite indiquant que vous êtes maintenant à l'intérieur du shell SQLite :

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Pour créer une table temporaire, vous utilisez l'instruction CREATE TEMP TABLE. La syntaxe est similaire à la création d'une table régulière :

CREATE TEMP TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Créons une table temporaire nommée temp_employees pour stocker les informations sur les employés. Entrez la commande SQL suivante à l'invite sqlite> et appuyez sur Entrée :

CREATE TEMP TABLE temp_employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    department TEXT
);

Cette commande crée une table temporaire nommée temp_employees avec trois colonnes : id (une clé primaire entière qui s'incrémente automatiquement), name (texte) et department (texte).

CREATE TEMP TABLE temp_employees : Cette instruction SQL crée une table temporaire nommée temp_employees. Le mot-clé TEMP spécifie que cette table est temporaire et sera automatiquement supprimée lorsque la connexion à la base de données sera fermée.

id INTEGER PRIMARY KEY AUTOINCREMENT : Ceci définit une colonne nommée id comme un entier, la définit comme la clé primaire (primary key) de la table et la configure pour qu'elle s'incrémente automatiquement à chaque nouvelle ligne insérée.

name TEXT : Ceci définit une colonne nommée name comme un champ de texte pour stocker les noms des employés.

department TEXT : Ceci définit une colonne nommée department comme un champ de texte pour stocker le département auquel chaque employé appartient.

Insérer des Données dans la Table Temporaire

Maintenant que vous avez créé la table temp_employees, ajoutons-y des données. Nous allons insérer plusieurs enregistrements d'employés dans la table.

Vous pouvez insérer des données dans une table temporaire en utilisant l'instruction INSERT INTO, comme vous le feriez avec une table régulière.

Insérez les enregistrements d'employés suivants dans la table temp_employees en exécutant ces commandes une par une à l'invite sqlite> :

INSERT INTO temp_employees (name, department) VALUES ('Alice', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Bob', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Charlie', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('David', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Eve', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Frank', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('Grace', 'HR');
INSERT INTO temp_employees (name, department) VALUES ('Henry', 'Sales');

Ces commandes ajoutent huit lignes à la table temp_employees.

INSERT INTO temp_employees (name, department) : Ceci spécifie que vous insérez des données dans les colonnes name et department de la table temp_employees.

VALUES ('Alice', 'Sales') : Ceci fournit les valeurs à insérer pour chaque enregistrement.

Pour confirmer que les données ont été ajoutées correctement, exécutez cette commande pour afficher tous les enregistrements de la table :

SELECT * FROM temp_employees;

Résultat attendu :

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

Cette sortie affiche l'id, le name et le department pour chaque enregistrement. La commande SELECT * récupère toutes les colonnes de la table spécifiée.

Interroger les Données de la Table Temporaire

Dans cette étape, vous apprendrez à récupérer des données de votre table temporaire en utilisant l'instruction SELECT avec différentes clauses. L'interrogation de données (querying data) est une compétence essentielle pour travailler avec des bases de données, car elle vous permet de visualiser et d'analyser les informations stockées.

À l'invite sqlite>, commençons par récupérer toutes les données de la table temp_employees. Exécutez cette commande :

SELECT * FROM temp_employees;

Résultat attendu :

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

Le * dans SELECT * signifie "toutes les colonnes", donc cette commande affiche chaque colonne et chaque ligne de la table temp_employees.

Ensuite, filtrez les données en utilisant une condition avec la clause WHERE. Pour afficher uniquement les enregistrements des employés du département 'Sales', exécutez :

SELECT * FROM temp_employees WHERE department = 'Sales';

Résultat attendu :

1|Alice|Sales
4|David|Sales
8|Henry|Sales

La clause WHERE réduit les résultats aux lignes qui correspondent à la condition, dans ce cas, où le department est 'Sales'.

• SELECT * FROM temp_employees sélectionne toutes les colonnes de la table temp_employees.
• WHERE department = 'Sales' filtre les résultats pour n'inclure que les lignes où la colonne department est égale à 'Sales'.

Maintenant, triez les données par nom en utilisant la clause ORDER BY :

SELECT * FROM temp_employees ORDER BY name;

Résultat attendu :

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

La clause ORDER BY trie les résultats en fonction de la colonne spécifiée, dans ce cas, la colonne name.

Enfin, limitez le nombre de résultats en utilisant la clause LIMIT. Pour afficher uniquement les 3 premiers enregistrements, exécutez :

SELECT * FROM temp_employees LIMIT 3;

Résultat attendu :

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering

La clause LIMIT limite le nombre de lignes renvoyées par la requête (query).

Confirmer le Nettoyage Automatique de la Table Temporaire

Dans cette étape, vous confirmerez que la table temporaire temp_employees est automatiquement supprimée lorsque la connexion à la base de données est fermée. C'est une caractéristique essentielle des tables temporaires.

Tout d'abord, quittez l'interpréteur (shell) SQLite en tapant :

.exit

Cela fermera la connexion à la base de données employees.db.

Maintenant, rouvrez l'interpréteur SQLite en exécutant :

sqlite3 employees.db

Essayez d'interroger la table temp_employees :

SELECT * FROM temp_employees;

Vous devriez voir un message d'erreur indiquant que la table n'existe pas :

Error: no such table: temp_employees

Cela confirme que la table temporaire a été automatiquement supprimée lorsque la connexion précédente à la base de données a été fermée.

SELECT * FROM temp_employees : Cette instruction SQL tente de sélectionner toutes les colonnes et toutes les lignes de la table temp_employees.

Error: no such table: temp_employees : Ce message d'erreur indique que la table temp_employees n'existe pas dans la base de données, confirmant qu'elle a été automatiquement supprimée lorsque la connexion à la base de données a été fermée.

Résumé

Dans ce labo, vous avez appris à créer et à utiliser des tables temporaires dans SQLite. Vous avez créé une table temporaire nommée temp_employees, inséré des données dedans, interrogé les données en utilisant diverses instructions SELECT, et confirmé que la table temporaire est automatiquement supprimée lorsque la connexion à la base de données est fermée. Ces exercices pratiques ont fourni une introduction complète aux tables temporaires, vous dotant des connaissances fondamentales pour gérer efficacement les données temporaires en utilisant SQLite.