Quintilien

Quis, quid, ubi, quibus auxiliis, cur, quomodo, quando

DB Browser for SQLite, le couteau suisse

Page classée dans : Bases de données
Page précédente : Les bases de données SQLite
Page suivante : Facilitez-vous la vie avec Oscar

Un formidable « couteau suisse » permet de gérer instantanément les bases de données de type SQLite : DB Browser for SQLite.

Ce petit logiciel peut être téléchargé sur https://sqlitebrowser.org/

Utilisation

Ne soyez pas surpris, une fois installé, il arrive que DB Browser donne un petit message d'erreur au démarrage : il a en effet parfois du mal à vérifier son numéro de version ... c'est sans conséquence.

OscarDB

DBBrower permet

de créer ou de modifier la structure d'une base de données
de créer, lire, modifier ou supprimer des données
d'exécuter des commandes SQL

OscarDB

Exemple de base de données

Un exemple de base de données déjà créée et complétée peut être téléchargé ici

OscarDB

Dans cet exemple, la base de données contient deux tables

La table "collections"

id	titre
1	Tintin
2	Lucky Luke
3	Astérix

La table "titres"

id	nom	parution	fk_collection
1	Tintin au pays des Soviets	1930	1
2	Tintin au Congo	1931	1
3	Tintin en Amérique	1932	1
...	...	...	...
34	Lucky Luke contre Joss Jamon	1958	2
35	Les Cousins Dalton	1958	2
36	Le Juge	1959	2
...	...	...	...
107	Astérix le Gaulois	1961	3
108	La Serpe d'or	1962	3
109	Astérix et les Goths	1963	3

Le champ fk_collection renvoie au numéro de collection repris dans la première table.

Création de tables

Si elles n'existent pas, ces tables peuvent être générées au moyen des instructions SQL suivantes :

CREATE TABLE "collections" 
    (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"titre"	TEXT
	)

Le champ "id" contiendra une information différente d'un enregistrement à l'autre, ce qui permettra d'identifier un enregistrement de manière unique. On utilisera pour cela un simple numéro qui s'incrémentera automatiquement au fur et à mesure de la création de nouveaux enregistrements (c'est l'instruction AUTOINCREMENT UNIQUE en SQL).

CREATE TABLE "titres" 
    (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"nom"	TEXT,
	"parution"	INTEGER,
	"fk_collection"	INTEGER,
	FOREIGN KEY("fk_collection") REFERENCES "collections"("id")
    )

On remarquera l'instruction FOREIGN KEY (clé étrangère) qui met en place un lien entre les deux tables.

Les liens entre les tables

La table "titres" est liée à la table "collections" grâce à l'instruction foreign key; en effet, dans la table "titres", le champ "fk_collection" doit contenir une valeur qui renvoie à l'identifiant unique de la table "collection".

Cela a comme conséquence

d'imposer qu'un titre soit rattaché à une collection existante ;
d'interdire de supprimer une collection si certains titres sont rattachés à celle-ci.

OscarDB

Essayez d'en faire autant avec Excel, pour voir ?!

Les champs de type unique

Lorsqu'un champ est défini comme unique, son contenu est contrôlé de manière à ce que le même champ dans un autre enregistrement ne puisse pas contenir la même valeur !

OscarDB

Cela évite les doublons, comme celui consistant à enregistrer deux fois la référence d'un même livre dans une bibilothèque.

Essayez avec Excel (Bis) ...

Structure de la base de données

OscarDB

Les clés étrangères

Le logiciel gère automatiquement les clés étrangères ("Foreign Keys").

C'est ce mécanisme qui vérifie en permanence les liens entre les tables. C'est pour cela ces systèmes sont parfois appelés 'bases de données relationnelles.

Pour mémoire, dans notre exemple la table "titres" est liée à la table "collections".

OscarDB

Contenu d'une table

OscarDB

Les requêtes SQL

OscarDB

Exemple 1

Classement par collections et par dates de parution

SELECT collections.titre, titres.nom, titres.parution 
FROM titres, collections 
WHERE titres.fk_collection = collections.id 
ORDER BY collections.titre, titres.parution

titre	nom	parution
Astérix	Astérix le Gaulois	1961
Astérix	La Serpe d'or	1962
Astérix	Astérix et les Goths	1963
...	...	...
Astérix	Astérix et la Transitalique	2017
Astérix	La fille de Vercingétorix	2019
Astérix	Astérix et le Griffon	2021
...	...	...
Lucky Luke	La Mine d'or de Dick Digger	1949
Lucky Luke	Rodéo	1950
Lucky Luke	Arizona	1951
...	...	...
Lucky Luke	Un cow-boy à Paris	2018
Lucky Luke	Un cow-boy dans le coton	2020
Lucky Luke	L'Arche de Rantanplan	2022
...	...	...
Tintin	Tintin au pays des Soviets	1930
Tintin	Tintin au Congo	1931
Tintin	Tintin en Amérique	1932
...	...	...
Tintin	Les Bijoux de la Castafiore	1963
Tintin	Vol 714 pour Sydney	1968
Tintin	Tintin et les Picaros	1976

Variante avec des titres de colonnes explicites

SELECT 
     collections.titre AS Collection , 
     titres.nom AS Titre, 
     titres.parution AS 'Date de parution' 
FROM titres, collections 
WHERE titres.fk_collection = collections.id 
ORDER BY collections.titre, titres.parution

Collection	Titre	Date de parution
Astérix	Astérix le Gaulois	1961
Astérix	La Serpe d'or	1962
Astérix	Astérix et les Goths	1963
...	...	...

Exemple 2

Classement par collections et par ordre alphabétique des titres

SELECT 
    collections.titre AS Collection, 
    titres.nom AS Titre, 
    titres.parution AS Parution 
FROM titres, collections 
WHERE titres.fk_collection = collections.id 
ORDER BY collections.titre, titres.nom

Collection	Titre	Parution
Astérix	Astérix aux Jeux olympiques	1968
Astérix	Astérix chez Rahàzade	1987
Astérix	Astérix chez les Belges	1979
Astérix	Astérix chez les Bretons	1966
Astérix	Astérix chez les Helvètes	1970
...	...	...
Lucky Luke	7 histoires de Lucky Luke	1974
Lucky Luke	Alerte aux Pieds-Bleus	1958
Lucky Luke	Arizona	1951
Lucky Luke	Belle Starr	1995
Lucky Luke	Billy the Kid	1962
Lucky Luke	Calamity Jane	1967
...	...	...
Tintin	Coke en stock	1958
Tintin	L'Affaire Tournesol	1956
Tintin	L'Oreille cassée	1937
...	...	...

Exemple 3

Classement par année de parution

SELECT 
    titres.parution AS "Parution", 
    collections.titre AS "Collection", 
    titres.nom AS "Titre"
FROM titres, collections 
WHERE titres.fk_collection = collections.id 
ORDER BY titres.parution

Parution	Collection	Titre
...	...	...
1958	Tintin	Coke en stock
1958	Lucky Luke	Alerte aux Pieds-Bleus
1958	Lucky Luke	Lucky Luke contre Joss Jamon
1958	Lucky Luke	Les Cousins Dalton
1959	Lucky Luke	Le Juge
1960	Tintin	Tintin au Tibet
1960	Lucky Luke	Ruée sur l'Oklahoma
1960	Lucky Luke	L'Évasion des Dalton
1961	Lucky Luke	En remontant le Mississippi
1961	Astérix	Astérix le Gaulois
1962	Lucky Luke	Sur la piste des Dalton
1962	Lucky Luke	À l'ombre des derricks
1962	Lucky Luke	Les Rivaux de Painful Gulch
1962	Lucky Luke	Billy the Kid
1962	Astérix	La Serpe d'or
1963	Tintin	Les Bijoux de la Castafiore
1963	Lucky Luke	Les Collines noires
...	...	...

Exemple 4

Sélection de titres contenant les lettres "As"

SELECT collections.titre, titres.nom, titres.parution 
FROM titres, collections 
WHERE titres.fk_collection = collections.id 
    AND titres.nom  LIKE '%As%' 
ORDER BY collections.titre, titres.nom

Parution	Collection	Titre
...	...	...
Astérix	L'anniversaire d' Astérix et Obélix	2009
Astérix	L'odyssée d' Astérix	1981
Astérix	Le fils d' Astérix	1983
Astérix	Tour de Gaule d' Astérix	1965
Lucky Luke	Chasse aux fantômes	1992
Lucky Luke	Chasseur de primes	1972
Lucky Luke	L'Homme de Washington	2008
Lucky Luke	L'Évasion des Dalton	1960
Lucky Luke	Tortillas pour les Dalton	1967
Tintin	L'Oreille cassée	1937
Tintin	Les Bijoux de la Castafiore	1963
...	...	...

Exemple 5

Sélection de titres contenant les lettres "ée"

SELECT 
    collections.titre AS Collection, 
    titres.nom AS Titre, 
    titres.parution AS Parution 
FROM titres, collections 
WHERE titres.fk_collection = collections.id 
    AND titres.nom  LIKE '%ée%' 
ORDER BY collections.titre, titres.nom

Collection	Titre	Parution
Astérix	Astérix et la rentrée gauloise	1993
Astérix	L'odyssée d'Astérix	1981
Astérix	La grande traversée	1975
Lucky Luke	La Fiancée de Lucky Luke	1985
Lucky Luke	Ruée sur l'Oklahoma	1960
Tintin	L'Oreille cassée	1937

Exemple 6

Classement chronologique des parutions des années "60"

SELECT 
    titres.parution AS Année, 
    collections.titre AS Collection, 
    titres.nom AS Titre
FROM titres, collections 
WHERE titres.fk_collection = collections.id 
    AND titres.parution >= '1960' 
    AND titres.parution <= '1969'  
ORDER BY titres.parution, collections.titre, titres.nom

Année	Collection	Titre
1960	Lucky Luke	L'Évasion des Dalton
1960	Lucky Luke	Ruée sur l'Oklahoma
1960	Tintin	Tintin au Tibet
1961	Astérix	Astérix le Gaulois
1961	Lucky Luke	En remontant le Mississippi
1962	Astérix	La Serpe d'or
1962	Lucky Luke	Billy the Kid
1962	Lucky Luke	Les Rivaux de Painful Gulch
1962	Lucky Luke	Sur la piste des Dalton
1962	Lucky Luke	À l'ombre des derricks
1963	Astérix	Astérix et les Goths
1963	Lucky Luke	Les Collines noires
1963	Lucky Luke	Les Dalton dans le blizzard
1963	Tintin	Les Bijoux de la Castafiore
1964	Astérix	Astérix gladiateur
1964	Lucky Luke	La Caravane
1964	Lucky Luke	Les Dalton courent toujours
1965	Astérix	Astérix et Cléopâtre
1965	Astérix	Tour de Gaule d'Astérix
1965	Lucky Luke	La Ville fantôme
1965	Lucky Luke	Le 20ème de cavalerie
1965	Lucky Luke	Les Dalton se rachètent
1966	Astérix	Astérix chez les Bretons
1966	Astérix	Astérix et les Normands
1966	Astérix	Le combat des chefs
1966	Lucky Luke	L'Escorte
1967	Astérix	Astérix légionnaire
1967	Lucky Luke	Calamity Jane
1967	Lucky Luke	Des barbelés sur la prairie
1967	Lucky Luke	Tortillas pour les Dalton
1968	Astérix	Astérix aux Jeux olympiques
1968	Astérix	Le bouclier arverne
1968	Lucky Luke	La Diligence
1968	Lucky Luke	Le Pied-Tendre
1968	Tintin	Vol 714 pour Sydney
1969	Astérix	Astérix en Hispanie
1969	Astérix	Astérix et le Chaudron
1969	Lucky Luke	Dalton City
1969	Lucky Luke	Jesse James

↑ Retourner en début de page
← Page précédente : Les bases de données SQLite
→ Page suivante : Facilitez-vous la vie avec Oscar