Créons de "vraies" tables, c'est-à-dire des tables avec des contraintes. Puis, remplissons les.

.open

Lançons SQLite et créons une base de données ( vide ), via la commande .open suivi d'un nom de fichier. Si ce fichier existe, il sera chargé en mémoire. Sinon, il sera créé sur le disque dur dans le dossier où se trouve l'exécutable sqlite.exe. Ici, notre fichier s'appelle contacts.db.

L'extension .db est l'abréviation de Data Base ( qui signifie en français base de données, data signifiant données )

full pathname

You might want to use a full pathname to ensure that the file is in the directory that you think it is in. Use forward-slashes as the directory separator character. In other words use "c:/work/ex1.db", not "c:\work\ex1.db".

Chacun crée sa base de données comme il le souhaite. Voici mes règles.

Le nom des tables se termine souvent pas "s", car elle contient beaucoup de contacts, localités, ...

Dans toutes les tables, le nom de la première colonne débute par "id_" suivi du nom de la table au singulier.
Cette première colonne est toujours du type INTEGER et ayant pour contrainte PRIMARY KEY.

On commence par créer les tables secondaires.

Dans une table secondaire, le second champ a toujours la contrainte UNIQUE.

CREATE TABLE localites(
id_localite  INTEGER PRIMARY KEY,
nomLocalite  TEXT UNIQUE,
CP           TEXT NOT NULL);

Un code postal peut correspondre à plusieurs localités.

Dans la table principale, le nom du champ qui est relié au champ d'une autre table commence toujours par "num_" suivi du nom de la table secondaire au singulier.
Cette colonne est toujours du type INTEGER.

CREATE TABLE contacts(
id_contact    INTEGER PRIMARY KEY,
nom           TEXT NOT NULL,
prenom        TEXT,
email         TEXT,
rue           TEXT,
num_localite  INTEGER,
dateAnni      TEXT,
FOREIGN KEY(num_localite) REFERENCES localites(id_localite)
);

Le champ prenom peut contenir un NULL car le contact peut être une personne morale.

Si SQLite semble ne plus vouloir vous rendre la main, tapez le point-virgule ( puis Enter ). SQLite vous signalera qu'une erreur s'est produite ( car votre instruction n'a aucun sens ) et attendra l'instruction suivante. Ainsi, vous récupérez la main.

.tables

Un petit .tables prouvera que les deux tables sont bien créées.

.schema

Fermons SQLite, via .exit, la sauvegarde se faisant automatiquement. Puis, relançons SQLite et ouvrons la base de données contacts.db. Vérifions la présences des tables, via .tables. Vérifions la structure de la table contacts, via la commande .schema contacts. Cette commande affiche les instructions pour créer cette table.

Tout est parfait. Nous allons pouvoir remplir les tables.

SQL : INSERT INTO ... ;

Syntaxe complète pour insérer un enregistrement :

Plus simplement, pour ajouter une ligne :

INSERT INTO nom_table (nom_colonne1,nom_colonne2, ...) VALUES (donnée1,donnée2, ...);

Commençons par remplir les tables secondaires. Ici, il n'y en a qu'une.

Normalement, les enregistrements ne se font pas manuellement, mais via une belle interface écrite en C++. Mais, avant d'écrire cette interface, il faut savoir faire les opérations manuellement. D'ailleurs, les interfaces sont généralement conçues pour les simples utilisateurs. Si vous êtes DB administrator, vous devez savoir donner vos instructions en ligne de commande.

Faisons notre premier enregistrement, en ligne de commande.

INSERT INTO localites (nomLocalite,CP) VALUES ("MONS","7000");

Puisque le champ id_localite est autoincrement, il est inutile et même recommandé de ne pas insérer cette donnée manuellement.

SQLite ne signale que les erreurs. Pas de messages d'erreur = tout est OK.

Nous verrons plus en détail comment obtenir des résultats plus tard. Cependant, pour être tout à fait rassuré, utilisons l'instruction SQL suivante :

SELECT * FROM nom_table;

Ce qui pourrait se traduire par : sélectionne tout depuis la table ...

Vous savez maintenant comment introduire correctement des données dans une base de données.

Si le caractère qui entoure la chaîne de caractères doit faire partie de la chaîne, il faut alors le doubler.
INSERT INTO localites (nomLocalite,CP) VALUES ('Bois-D''Haine',"7170");

Ajoutons que la chaîne doit être entourée (commencer et finir) par " ou '

champ TEXT

Le champ de type "description" d'un formulaire contient des retours de ligne et souvent des caractères spéciaux.

Le programme permettant pourrait interdire le caractère encadrant la string transmise.

Supporte tous les caractères UTF-8 (donc y compris chinois) sauf le guillemet (")
qui sera automatiquement remplacé par l'apostrophe (')

Français  : àâçéèêëîïôùûüÿææ ÀÂÇÉÈÊËÎÏÔÙÛÜŸÆŒ
Spéciaux : -_.<>/\`£¤%§;,@&=#:!¤¨°²µ«»'()[]{}|^$+*?
Alphabet grec : ΑαΒβΓγΔδΕεΖζΗηΘθΙιΚκΛλΜμΝνΞξΟοΠπΡρΣσςΤτΥυΦφΧχΨψΩω
Alphabet hébreu : אבגדהוזחטיכלמנסעפצקרשת

The SQL standard says that strings must use 'single quotes', and identifiers (such as table and column names), when quoted, must use "double quotes".

For compatibility with MySQL, SQLite also allows to use single quotes for identifiers and double quotes for strings, but only when the context makes the meaning unambiguous. (In SELECT 'rowid' ..., a string is allowed, so a string is what you get.) If possible, always use the standard SQL quotes.

For compatibility with MySQL, SQLite also allows `backticks` for identifiers.

For compatibility with Microsoft databases, SQLite also allows [brackets] for identifiers.

nombre de colonnes

Évidemment, le nombre de données doit correspondre au nombre de colonnes. Cependant, l'ordre des colonnes peut ne pas correspondre à celui de la table et toutes les colonnes de la table ne doivent pas être indiquées.
Exemple : INSERT INTO localites (CP,nomLocalite) VALUES ("5000","NAMUR");

Les colonnes qui peuvent être omises sont celles qui acceptent NULL comme donnée ou disposant d'une clause DEFAULT

Si la donnée omise est celle d'une colonne de type INTEGER PRIMARY KEY, elle sera automatiquement convertie en nombre entier qui sera le plus grand de cette colonne.
Il est également possible d'insérer un enregistrement sans nommer les colonnes. Toutefois, dans ce cas, il doit exister autant de données que de colonnes. L'ordre des colonnes sera alors celui de la table.

Pour indiquer l'absence de donnée, il faut alors explicitement indiquer NULL comme donnée.
Exemple : INSERT INTO localites VALUES (NULL,"TOURNAI","7500");

L'insertion ne sera autorisée que si la colonne accepte les NULL

Si NULL est la donnée d'une colonne de type INTEGER PRIMARY KEY, elle sera automatiquement convertie en nombre entier qui sera le plus grand de cette colonne.

Puisque chaque colonne doit recevoir une donnée - fusse-t-elle NULL - la valeur par défaut n'est jamais utilisée.

La possibilité d'insérer des données sans nommer une seule colonne doit être utilisée avec précaution.

Je vais maintenant faire ce que vous ne devez pas faire pour vous éviter de le faire pour voir ce qui se passe si ...

Donner soi-même un ID
INSERT INTO localites (id_localite,nomLocalite,CP) VALUES (15,"Charleroi","6000");
Aucune erreur n'est signalée, mais vous venez de " perdre " 14 identifiants. Les numéros 2 à 14 ne seront plus jamais attribués par la base de données. Ce n'est pas dramatique. Lorsque vous supprimerez un enregistrement, l'ID utilisé ne sera plus jamais ré-attribué. ( En réalité, il est possible de les réutiliser. Mais la technique de récupération est hors du cadre d'une mini-cours. )

Toutefois, on peut donner soi-même un ID, si cet ID n'existe plus.
Oublier d'insérer une donnée
INSERT INTO localites (nomLocalite) VALUES ("Liège");

La contrainte NOT NULL sur le champ CP de la table localites n'est pas respectée. L'insertion n'a donc pas été faite conformément au vœu du DB administrator.
Insérer une donnée déjà donnée
INSERT INTO localites (nomLocalite,CP) VALUES ("MONS","7000");

La contrainte UNIQUE sur le champ nomLocalite de la table localites n'est pas respectée. L'insertion n'a donc pas été faite conformément au vœu du DB administrator.

Rassurez-vous. Vous pourrez encore faire plein de bêtises ...
INSERT INTO localites (CP,nomLocalite) VALUES ("Mons","7000");

Il est possible d'ajouter des contraintes supplémentaires à une colonne pour empêcher des erreurs de ce type. Mais l'étude de la contrainte CHECK sort du cadre de ce mini-cours.

Après plusieurs enregistrements réussis, vérifions le contenu de la table,
via l'instruction SQL SELECT * FROM nom_table;

Ne soyez pas attristé par le contenu de la table. A la fin de cette page, je vous demanderai de la supprimer. Demain, votre table localites contiendra des centaines de lignes sans devoir faire un seul INSERT !

Nous constatons que la ligne identifiée sous le numéro 16 est de trop !

Comment supprimer cette ligne ?

SQL : DELETE FROM ... ;

Syntaxe complète pour supprimer un enregistrement :

Plus simplement, pour supprimer une ligne :

DELETE FROM nom_table WHERE id=... ;

Nous tapons donc DELETE FROM localites WHERE id_localite=16;

Attention, si la clause WHERE est oubliée, toutes les lignes seront effacées ! Sans avertissement !

Et, pour la ligne 15, comment mettre le nom de la ville en majuscule ?

Heureusement, il n'est pas nécessaire de la supprimer et de la re-créer correctement. Il est possible de modifier une donnée ( tant qu'on respecte les contraintes )

SQL : UPDATE ... SET ... WHERE ... ;

Syntaxe complète pour mettre à jour un enregistrement :

Plus simplement, pour modifier une donnée dans une colonne :

UPDATE nom_table SET nom_colonne=nouvelle_donnée WHERE id=... ;

Nous tapons donc UPDATE localites SET nomLocalite="CHARLEROI" WHERE id_localite=15;

Attention, si la clause WHERE est oubliée, toutes les lignes seront modifiées ! Sans avertissement !

mettre à jour plusieurs données

Il est possible de mettre à jour plusieurs données (d'un seul enregistrement).

UPDATE localites SET nomLocalite="LIEGE", CP="4000" WHERE id_localite=15;

Même si SQLite ne détecte aucune erreur; vous, vous pouvez faire une grosse erreur. Maintenant, certains de vos contacts viennent de changer de ville ( CHARLEROI est devenu LIEGE ) en restant dans la même rue !

La mise à jour peut être refusée par SQLite conformément au vœu du DB administrator.
Comme cette instruction : UPDATE localites SET nomLocalite="MONS" WHERE id_localite=15;

Cependant, une chaîne vide n'est pas une absence de donnée ( NULL ). Conséquemment, on peut remplacer le nom d'une localité par une chaîne vide. Toutefois, conformément au vœu du DB administrator, il ne peut y avoir dans la colonne nomLocalite deux chaînes identiques, donc deux chaînes vides. D'où le refus de SQLite d'ajouter une seconde ville qui n'a pas de nom.