Rappel sqlite3
Dans sqlite3, il faut faire ces 2 commandes pour avoir un meilleur affichage.
.header on .mode column
Types de données supportés par SQLite
La description exacte des types de données supportées par SQLite est décrite sur http://www.sqlite.org/datatype3.html
En résumé :
Il y a 5 types supportés : INTEGER, NUMERIC, REAL, TEXT, BLOB. Toutes les autres BD fonctionnent avec un typage statique rigide. SQLite est une des seules BD
permettant une si grande conversion du type des données. Cela permet d'importer des données vers SQLite très facilement (mais pourrait causer des problèmes lors de l'exportation de SQLite vers une autre BD).
N'importe quelle valeur peut être entreposée dans n'importe quelle colonne sauf si la colonne est de type INTEGER PRIMARY KEY.
Si une valeur n'est pas du bon type, elle pourra être convertie ; ex : integer ou real vers string.
Il n'y a pas de type boolean. La norme est d'utiliser un INTEGER avec 0=false et 1=true.
Il n'y a pas de type pour les dates et l'heure. Utilisez un des 3 formats suivants :
TEXT YYYY-MM-DD HH:MM:SS.SSS(recommandé)REALle nombre de jours depuis midi à Greenwich, le 24 novembre 4714 B.C. en accord avec le calendrier Gregorian.INTEGERle temps en format UNIX, c.-à-d. le nombre de secondes depuis 1970-01-01 00:00:00 UTC
Vous pouvez choisir le format que vous désirez et utiliser les fonctions de conversion fournies par SQLite. Mais soyez consistant d'une table à l'autre.
Il y aura une conversion automatique si les données ne concordent pas avec le type de la colonne.
Définition d'une table
Dans une base de données, la table consiste au contenant et les données au contenu.
Pour être en mesure d'avoir des données dans une base de données, il faut des tables.
Pour être en mesure d'ajouter des données dans une table, il faut en connaitre sa définition.
Les éléments qui définissent une table sont :
- Le nom des colonnes
- Le type des colonnes
- Les contraintes
Téléchargez la base de données d'exercice du cours 2.
Avec SQLite, la commande .schema permet de voir la définition de toutes les tables. Une base de données avec plusieurs tables, il peut être difficile de s'y retrouver.
Il est possible de spécifier la table à obtenir .schema nomtable
Effectuez la commande ci-dessous dans la base de données.
.schema Autos
Le résultat sera ceci.
.schema Autos CREATE TABLE Autos ( AutoId INTEGER PRIMARY KEY NOT NULL, Nom TEXT NOT NULL, Prix INTEGER, Couleur TEXT DEFAULT 'blanc' );
La commande retourne le script de création de la table. Vous ne connaissez pas encore la syntaxe et elle sera expliquée plus tard en session.
Le premier élément est le nom de la colonne, le deuxième est le type de la colonne et ensuite ce sont les contraintes.
Pour débuter, il sera plus facile d'utiliser SQLiteStudio pour comprendre la composition d'une table.
Pour voir la définition de la table, faites un double-clic sur la table et ensuite sélectionnez l'onglet Structure dans la section de droite.
La définition est présentée sous forme de tableau et par catégorie.
Clé primaire
La clé primaire ou primary key permet d'identifier de façon unique un enregistrement. Il n'est pas possible d'avoir dans la table 2 enregistrements avec la même clé.
Les champs ayant cette contrainte font partie de la clé primaire. Il peut y avoir un ou plusieurs champs faisant partie de cette clé. Quand la clé primaire contient plusieurs champs, on dit qu'elle est composée, sinon elle est simple.
Dans l'exemple-ci dessous, la colonne Id est la clé primaire. Le symbole de la clé est visible sous la colonne Primary Key.
Pour les clés numériques sans signification propre, la norme est de nommer le champ Id ou le nom de la table au singulier en préfix et ensuite Id. Par exemple pour la table Autos, la clé est AutoId.
Selon mon opinion, je préfère utiliser le AutoId, car dans les requêtes complexes, la quantité de colonnes Id peut devenir importante. Il est donc plus visuel de mettre le nom de la table dans une clé simple. Par contre, ce n'est pas universel comme approche et ceci dépend des conventions de l'équipe de travail.
Dans l'exemple ci-dessous, la clé primaire est composée, car il y a 3 champs. Il est possible de définir une auto par son année, sa marque et son modèle.
C'est la combinaison des 3 qui définit la clé. Il ne sera pas possible d'avoir 2 fois 2022, Hyundai, Tucson. Mais il sera possible d'avoir 2021, Hyundai, Tucson et 2021, Hyundai, Kona et 2021, Fiat, 500.
Clé naturelle ou artificielle ?
Une clé numérique sans signification est appelée clé artificielle ou surrogate en anglais. La table Autos utilise une clé artificielle.
Une clé naturelle est une clé qui a une signification pour l'utilisateur. Il est souvent difficile d'avoir une clé naturelle, car elle doit tout de même être unique. Dans le cas de AutosV2, c'est une clé naturelle, car chacun de ses membres a une signification.
Est-ce que le numéro d'assurance maladie est une clé naturelle ou artificielle ? Certains diront que oui, car elle est créée à partir du nom et de la date de naissance de l'utilisateur. Par contre, les 2 derniers chiffres sont aléatoires, donc elle n'est pas 100% naturelle.
Champ optionnel ou obligatoire : NULL et NOT NULL
Il est possible d'indiquer si un champ est obligatoire ou optionnel.
Par défaut, le champ est optionnel, il n'est pas obligatoire de mettre la contrainte NULL dans le code. La contrainte NOT NULL indique que le champ est obligatoire.
Dans la table AutosV2, quels champs sont obligatoires et quels champs sont optionnels ? S'il y a une pastille dans la colonne Not NULL, le champ est obligatoire.
IMPORTANT : Une autre particularité de SQLite est que la valeur NULL est permise dans les champs de la clé primaire. Il est donc recommandé d'utiliser la contrainte NOT NULL sur les champs de la clé primaire. Il est préférable d'éviter la valeur NULL pour une clé primaire.
Valeur par défaut : DEFAULT
Cette contrainte de champs permet d'indiquer la valeur qui sera insérée dans le champ si l'usager ne spécifie pas de valeur lors de l'insertion.
La valeur par défaut d'un champ est NULL si ce n'est pas spécifié dans les contraintes du champ. Par contre, la contrainte de champ obligatoire NOT NULL à priorité.
Dans la table AutosV2, les 3 clés ont la valeur par défaut NULL, mais il ne sera pas possible de mettre cette valeur, car les champs ont la contrainte prioritaire NOT NULL.
Regardez de nouveau la table Autos.
La colonne Couleur à la valeur blanc par défaut. Donc si le champ n'est pas spécifié lors de l'insertion, la couleur de l'auto sera blanc.
Valeur unique - UNIQUE
La contrainte de valeur unique indique qu'il n'est pas possible pour un champ d'avoir la même valeur dans 2 enregistrements différents. Ça vous rappelle quelque chose ? La clé primaire a également la contrainte d'unicité.
Dans quel cas il faudrait avoir une valeur unique pour un enregistrement et ce n'est pas une clé ?
Voici une table qui enregistre l'information des employés.
Table : Employes
| Colonne | Type | Description |
|---|---|---|
| EmployeId | INTEGER | Clé primaire qui représente le numéro de l'employé |
| Nom | TEXT | Nom de l'employé |
| Prenom | TEXT | Prénom de l'employé |
| Courriel | TEXT | Le courriel de l'entreprise pour l'employé |
| Poste | TEXT | Poste occupé par l'employé |
| Salaire | REAL | Salaire de l'employé |
| NAS | TEXT | Numéro d'assurance sociale de l'employé |
Quels champs pourraient être uniques sans être une clé ? Le NAS est nécessairement unique. Le courriel de l'entreprise également.
La pastille sera présente dans la colonne Unique pour indiquer les tables avec cette contrainte.
L'insertion - INSERT
Pour être en mesure d'insérer des données, il faut connaitre la structure de la table et ses contraintes.
La commande pour insérer des entrées dans une table est INSERT.
Maintenant que nous avons des tables, il faut mettre des données dans celle-ci. La commande pour insérer des entrées dans une table est INSERT.
http://www.sqlite.org/lang_insert.html
Commande de base
La commande INSERT à 3 parties.
- Table à utiliser
- Colonnes affectées
- Valeurs à ajouter
Exemples :
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (1, 'Audi', 52642, 'bleu'); -- ou INSERT INTO Autos VALUES (2, 'Mazda', 32542, 'mauve');
La première forme est celle recommandée. Pourquoi selon vous ?
Si le nom des colonnes n'est pas spécifié dans la commande INSERT, la base de données ajoute la première valeur dans la première colonne, la 2e valeur, dans la 2e colonne...
En spécifiant le nom des colonnes, les données seront alors insérées dans la bonne colonne.
Est-ce qu'il est possible que l'ordre des colonnes change avec le temps ? Oui il est possible si des modifications sont effectuées sur la structure. L'administrateur de la base de données (DBA) modifie la table Autos et il inverse les colonnes Nom et Couleur. Les requêtes dans votre programme ne fonctionneront plus correctement et il y aura un bug dans votre programme. L'utilisateur va inscrire dans le Mazda et mauve dans le formulaire d'insertion, mais lors de la visualisation, il remarquera que c'est inversé.
Comme indiqué précédemment, il ne faut jamais présumer de l'ordre des données lorsque vous manipulez des données avec une BD. Il faut toujours être le plus explicite possible afin d'éviter des bugs à long terme.
Il est important que le nombre de colonnes déclarées soit identique au nombre de valeur.
INSERT INTO Autos (AutoId, Nom, Prix, Couleur) VALUES (3, 'Dodge RAM', 55365);
Le système retournera cette erreur. Parse error: 3 values for 4 columns.
Même si la colonne a une valeur par défaut, si la colonne est déclarée dans le INSERT, il faut indiquer une valeur.
Remarquez également que les valeurs numériques sont inscrites directement et le texte est entre guillemets simple '.
Ordre des colonnes
Il est également possible de changer l'ordre des champs dans la déclaration de l'insertion.
INSERT INTO Autos (Couleur, AutoId, Nom, Prix) VALUES ('rouge', 3, 'Dodge RAM', 55365);
Affichez le contenu de la table avec un SELECT.
SELECT * FROM Autos;
Voici le résultat.
AutoId Nom Prix Couleur ------ --------- ----- ------- 1 Audi 52642 bleu 2 Mazda 32542 mauve 3 Dodge RAM 55365 rouge
Les valeurs sont dans les bonnes colonnes.
Insertion partielle
Il est possible de spécifier seulement une partie des colonnes. Les colonnes non spécifiées prendront la valeur par défaut. Si aucune valeur par défaut n’est définie et que le champ est obligatoire, il y aura une erreur.
L'exemple ci-dessous fonctionnera, car les colonnes Prix et Couleur sont optionnelles.
INSERT INTO Autos(AutoId, Nom) VALUES (4, 'Toyota');
Que seront les valeurs des champs Prix et Couleur ?
Effectuez un SELECT pour le voir.
SELECT * FROM Autos WHERE AutoId = 4;
Voici le résultat.
AutoId Nom Prix Couleur ------ ------ ---- ------- 4 Toyota blanc
La colonne Prix est vide, car il n'y a pas de valeur par défaut spécifique et Couleur est blanc.
Pour utiliser la valeur par défaut qui est spécifiée dans les contraintes du champ, il ne faut pas déclarer la colonne dans la commande INSERT.
Chaine vide vs null
Effectuez le INSERT ci-dessous dans la table AutosV2.
INSERT INTO AutosV2 (Annee, Marque, Modele, Prix, Couleur) VALUES (2022, 'Porsche', '911', 101242, ''); --Chaine de caractère vide INSERT INTO AutosV2 (Annee, Marque, Modele, Prix) VALUES (2022, 'Dodge', 'Cavavan', 29365); --Couleur par défaut
Effectuez le SELECT ci-dessous pour la table AutosV2 avec sqlite3 et SQLiteStudio.
SELECT * FROM AutosV2;
Voici le résultat dans sqlite3.
Annee Marque Modele Prix Couleur ----- ------- ------- ------ ------- 2022 Porsche 911 101242 2022 Dodge Cavavan 29365
Voici le résultat dans SQLiteStudio.
Dans sqlite3, il n'y a pas de différence entre les 2 enregistrements pour la colonne Couleur contrairement à SQLiteStudio..
Est-ce qu'un NULL est équivalent à une chaine vide ?
Effectuez le SELECT ci-dessous pour obtenir les voitures avec une couleur vide.
SELECT * FROM AutosV2 WHERE Couleur = '';
Voici le résultat. Seulement la Porsche répond à ce critère.
Annee Marque Modele Prix Couleur ----- ------- ------ ------ ------- 2022 Porsche 911 101242
Quelle est la requête pour obtenir les voitures avec une couleur null ?
Est-ce que la requête ci-dessous fonctionne ? Non, elle retournera rien.
SELECT * FROM AutosV2 WHERE Couleur = NULL;
Pour faire une condition avec NULL ou NOT NULL, il faut remplacer le = par IS.
Donc la bonne requête est celle-ci.
SELECT * FROM AutosV2 WHERE Couleur IS NULL;
Voici le résultat. Seulement la Dodge répond à ce critère.
Annee Marque Modele Prix Couleur ----- ------ ------- ----- ------- 2022 Dodge Cavavan 29365
Il peut être important de voir la différence entre un NULL et un vide avec sqlite3. Il faut utiliser la commande .nullvalue [NULL].
Donc maintenant la requête SELECT * FROM AutosV2; affichera se résultat.
Annee Marque Modele Prix Couleur ----- ------- ------- ------ ------- 2022 Porsche 911 101242 2022 Dodge Cavavan 29365 [NULL]
Il est possible de mettre le texte de remplacement que l'on désire pour affiche la valeur NULL.
.nullvalue {NULL} .nullvalue |NULL| .nullvalue ~NULL~ .nullvalue Je_suis_null
Pour insérer une valeur NULL explicitement dans une commande INSERT, il faut mettre le mot clé NULL comme valeur.
INSERT INTO AutosV2 (Annee, Marque, Modele, Prix, Couleur) VALUES (2022, 'Hyundai', 'Tucson', 32159, NULL);
Insertion multiple
Pour insérer plusieurs lignes à la fois, il est aussi possible d'utiliser cette syntaxe :
INSERT INTO AutosV2 (Annee, Marque, Modele, Prix, Couleur) VALUES (2021, 'Audi', 'A8', 52642, 'bleu'), (2022, 'Audi', 'A8', 75666, 'blanc'), (2019, 'KIA', 'Soul', 18881, 'jaune');
Clé primaire simple INTEGER
Lorsque la clé primaire est une clé simple et de type INTEGER, il n'est pas nécessaire de la spécifier.
INSERT INTO Autos(Nom, Prix, Couleur) VALUES('Lada', 1856, 'rouille');
L'insertion fonctionnera sans erreur. Quelle sera la valeur de AutoId ? SQLite prendra la valeur la plus grande et augmentera de 1. Il est commun de laisser le contrôle de la création des clés à la base de données pour les clés artificielles numériques. Selon la technologie utilisée, la gestion des clés peut fonctionner différemment.
Conversion automatique
SQLite convertit automatiquement les données lorsque la valeur envoyée n'est pas du bon type. D'autres technologies de base de données sont plus rigides que SQLite dans la conversion. Il faut être attentif aux erreurs.
Essayez cette commande et elle fonctionnera en SQLite. Le Prix aura la valeur A.
INSERT INTO Autos(Nom, Prix, Couleur) VALUES('Tesla', 'A', 'noir');
Sans trop entrer dans les détails techniques, le type réel est attaché directement à la donnée et non à la colonne comme la plupart des bases de données.
Il est possible de rendre une table strict pour éviter ce genre de problème. Ce concept sera expliqué lors de la création des tables.
Soyez vigilant !
Apostrophe
En SQL, le guillemet simple est également l'apostrophe.
Essayez d'insérer la voiture nommée D'iamo.
INSERT INTO Autos(Nom, Prix, Couleur) VALUES('D'iamo', 134, 'noir');
La commande ne se terminera pas. Le moyen de sortir est d'effectuer un CTRL+Z pour terminer le processus.
Même les notes de cours détectent qu'il y a une erreur, car c'est uniquement le 'D' qui est en rouge. Le rouge indique que c'est du texte dans Typora lorsque le bloc de code est en mode SQL.
Il faut utiliser un double '' lorsqu'il est utilisé à l'intérieur d'une chaine de caractères.
INSERT INTO Autos(Nom, Prix, Couleur) VALUES('D''iamo', 134, 'noir');
La valeur du champ de l'enregistrement sera D'iamo avec un seul '
Exercices
Version originale par François St-Hilaire
Mise à jour 2023 par Pierre-Luc Boulanger