Requête SQLite INSERT, UPDATE, DELETE avec exemple

Table des matières:

Anonim

Les clauses de modification de données dans SQLite sont des instructions INSERT, UPDATE et DELETE. Il est utilisé pour insérer de nouvelles lignes, mettre à jour des valeurs existantes ou supprimer des lignes de la base de données.

Dans ce didacticiel, vous apprendrez-

  • INSÉRER
  • Mettre à jour
  • Effacer
  • Clause de conflit

Notez que, pour tous les exemples suivants, vous devez exécuter sqlite3.exe et ouvrir une connexion à la base de données exemple en cours d'exécution:

Étape 1) Dans cette étape,

  1. Ouvrez Poste de travail et accédez au répertoire suivant " C: \ sqlite " et
  2. Ensuite, ouvrez " sqlite3.exe ":

Étape 2) Ouvrez la base de données " TutorialsSampleDB.db " par la commande suivante:

Tutoriels .openSampleDB.db

Vous êtes maintenant prêt à exécuter n'importe quel type de requête sur la base de données.

INSERT SQLite

SQLite INSERT est utilisé pour insérer des enregistrements dans une table spécifiée de la base de données. vous devez utiliser la clause 'INSERT'. La syntaxe de la clause INSERT est la suivante:

  • Après la clause INSERT, vous devez indiquer dans quelle table vous devez insérer les valeurs.
  • Après le nom de la table dans laquelle vous écrivez la liste des colonnes, vous souhaitez insérer les valeurs.
  • Vous pouvez ignorer le nom des colonnes et ne pas y écrire.
  • Si vous n'écrivez pas le nom des colonnes, les valeurs seront insérées dans toutes les colonnes trouvées dans le tableau avec le même ordre, les colonnes sont définies dans le tableau.
  • Après la clause VALUES, vous devez lister les valeurs à insérer.
  • Chaque clause INSERT insère une seule ligne. Si vous souhaitez insérer plusieurs lignes, vous devez écrire plusieurs clauses INSERT, une pour chaque ligne.

Exemple d'insertion SQLite

Dans l'exemple suivant, nous allons insérer 2 lignes dans le tableau des étudiants, une pour chaque étudiant:

INSÉRER DANS Étudiants (StudentId, StudentName, DepartmentId, DateOfBirth)VALEURS (11, «Ahmad», 4, «1997-10-12»);INSÉRER DANS LES VALEURS DES ÉLÈVES (12, «Aly», 4, «1996-10-12»);

Cela devrait fonctionner correctement et il n'y a pas de sortie pour cela:

Cela insérera deux étudiants:

  • Le premier étudiant avec StudentId = 11, StudentName = Ahmad, DepartmentId = 4 et DateOfBirth = 1997-10-12.
  • Le deuxième étudiant avec StudentId = 12, StudentName = Aly, DepartmentId = 4 et DateOfBirth = 1996-10-12 '.

Dans la première instruction, nous avons répertorié les noms de colonnes « StudentId, StudentName, DepartmentId, DateOfBirth » .Cependant , dans la deuxième instruction, nous ne l'avons pas fait.

Les quatre valeurs " 12, 'Aly', 4, '1996-10-12' " seront insérées dans les quatre colonnes de la table Students dans le même ordre que les colonnes sont définies.

Maintenant, vérifions que les deux étudiants ont été insérés dans la table Students en exécutant la requête suivante:

SELECT * FROM étudiants;

Ensuite, vous devriez voir les deux étudiants renvoyés par cette requête comme suit:

Mise à jour SQLite

La requête SQLite UPDATE est utilisée pour modifier les enregistrements existants dans une table. Vous pouvez utiliser la clause WHERE avec la requête UPDATE pour mettre à jour les lignes sélectionnées. La clause UPDATE met à jour une table en modifiant une valeur pour une colonne spécifique. Voici la syntaxe de la clause UPDATE:

Comme suit:

  • Après la "clause de mise à jour", vous devez écrire le nom de la table à mettre à jour.
  • Vous devez écrire la "clause SET" qui sert à écrire le nom de la colonne à mettre à jour et la valeur à mettre à jour.
  • Vous pouvez mettre à jour plusieurs colonnes. Vous pouvez utiliser une virgule entre chaque ligne.
  • Vous pouvez spécifier une clause WHERE pour spécifier uniquement certaines lignes. Seules les lignes que l'expression évalue à true sont mises à jour. Si vous n'avez pas spécifié de clause WHERE, toutes les lignes seront mises à jour.

Exemple de mise à jour SQLite

Dans l'instruction UPDATE suivante, nous mettrons à jour le DepartmentId pour l'étudiant avec StudentId = 6 pour qu'il soit 3:

METTRE À JOUR les étudiantsSET DepartmentId = 3O StudentId = 6;

Cela devrait fonctionner correctement et vous ne devriez pas obtenir de sortie:

Dans la clause UPDATE, nous avons spécifié que nous souhaitons mettre à jour la table Students.

  • Dans la clause WHERE, nous avons filtré tous les étudiants pour sélectionner uniquement la ligne pour StudentId = 6.
  • La clause SET mettra à jour la valeur de l'ID de département pour les étudiants sélectionnés à 3.

Maintenant, vérifions que l'étudiant avec l'ID 6 est mis à jour, en exécutant la commande suivante:

SELECT * FROM Students WHERE StudentId = 6;

Vous devriez maintenant voir que la valeur de l'ID de service est maintenant 3 comme suit:

Supprimer SQLite

La requête SQLite DELETE est utilisée pour supprimer les enregistrements existants d'une table spécifiée. Vous pouvez utiliser la clause WHERE avec les requêtes DELETE pour supprimer les lignes sélectionnées.

La clause DELETE a la syntaxe suivante:

  • Vous devez écrire un nom de table après la clause DELETE FROM, à partir de laquelle vous souhaitez supprimer des enregistrements. ( Remarque: la clause DELETE est utilisée pour supprimer certains enregistrements d'une table ou pour supprimer tous les enregistrements et elle ne supprimera pas la table elle-même. Cependant, la clause DROP est utilisée pour supprimer la table entière avec tous les enregistrements dessus. )
  • Si vous écrivez la clause DELETE comme ceci "DELETE FROM guru", cela supprimera tous les enregistrements de la table "guru".
  • Vous pouvez spécifier une condition WHERE avec une expression si vous souhaitez supprimer des lignes spécifiques. Seules les lignes pour lesquelles l'expression prend la valeur true seront supprimées. Par exemple, "DELETE FROM guru WHERE id> 5" - cela supprimera uniquement les enregistrements dont l'ID est supérieur à 5.

Exemple

Dans la déclaration suivante, nous supprimerons deux étudiants avec StudentId 11 et 12:

SUPPRIMER DES étudiants WHERE StudentId = 11 OU StudentId = 12;

L'expression " StudentId = 11 OR StudentId = 12 " ne sera vraie que pour les étudiants avec les ID 11 et 12. La clause DELETE sera donc appliquée sur les deux et les supprimera uniquement.

Cette commande devrait s'exécuter avec succès et vous ne devriez pas obtenir de sortie comme suit:

Vous pouvez vérifier que les deux étudiants ont été supprimés, en sélectionnant tous les enregistrements du tableau des étudiants comme suit:

SELECT * FROM étudiants;

Vous ne devriez pas voir les deux étudiants avec les identifiants 11 et 12 comme suit:

Clause de conflit SQLite

Supposons que vous ayez une colonne qui a l'une des contraintes de colonne suivantes: UNIQUE, NOT NULL, CHECK ou PRIMARY KEY. Et puis vous avez essayé d'insérer ou de mettre à jour une valeur sur cette colonne avec une valeur qui entre en conflit avec cette contrainte.

Par exemple, si une colonne a une contrainte UNIQUE et que vous avez essayé d'insérer une valeur qui existe déjà (une valeur en double), qui entre en conflit avec la contrainte UNIQUE. Ensuite, la clause CONFLICT vous permet de choisir quoi faire dans de tels cas pour résoudre ce conflit.

Avant de continuer à expliquer comment la clause CONFLICT résout le conflit. Vous devez comprendre ce qu'est une transaction de base de données.

Transaction de base de données:

Le terme transaction de base de données est une liste d'opérations SQLite (insérer ou mettre à jour ou supprimer). La transaction de base de données doit être exécutée comme une seule unité, toutes les opérations exécutées avec succès ou pas du tout. Toutes les opérations seront annulées si l'une d'elles échoue à s'exécuter.

Exemple pour une transaction de base de données:

La transaction de transfert d'argent d'un compte bancaire à un autre impliquera quelques activités. Cette opération de transaction comprend le retrait d'argent du premier compte et son dépôt sur un autre compte. Cette transaction doit être entièrement ou entièrement annulée et ne pas échouer à mi-chemin.

Voici la liste de cinq résolutions que vous pouvez choisir dans la clause CONFLICT:

  1. ROLLBACK - cela annulera la transaction dans laquelle l'instruction SQLite actuelle qui a le conflit (cela annulera la transaction entière). Par exemple, si vous essayez de mettre à jour 10 lignes et que la cinquième ligne a une valeur qui entre en conflit avec une contrainte, aucune ligne ne sera mise à jour, les 10 lignes resteront les mêmes. Une erreur sera générée.
  2. ABORT - cela annulera (annulera) l'instruction SQLite actuelle uniquement qui a le conflit et la transaction ne sera pas annulée. Par exemple, si vous essayez de mettre à jour 10 lignes et que la cinquième ligne a une valeur qui entre en conflit avec une contrainte, seule la cinquième valeur ne sera pas mise à jour mais les 9 autres lignes seront mises à jour. Une erreur sera générée.
  3. FAIL - abandonne l'instruction SQLite actuelle qui a le conflit. Cependant, la transaction ne se poursuivra pas, mais les modifications précédentes apportées aux lignes antérieures à la ligne en conflit seront validées. Par exemple, si vous essayez de mettre à jour 10 lignes et que la cinquième ligne a une valeur qui entre en conflit avec une contrainte, seules les 4 lignes seront mises à jour et l'autre non. Une erreur sera générée.
  1. IGNORER - cela ignorera la ligne qui contient la violation de contrainte et poursuivra le traitement des autres lignes suivantes de l'instruction SQLite. Par exemple, si vous essayez de mettre à jour 10 lignes et que la cinquième ligne a une valeur qui entre en conflit avec une contrainte, seules les 4 lignes seront mises à jour et l'autre non. Il ne poursuivra pas la mise à jour des autres lignes et ne s'arrêtera pas à la ligne qui a la valeur de conflit. Aucune erreur ne sera générée.
  1. REMPLACER - cela dépend du type de contrainte qui a la violation:
  • Lorsqu'il y a une violation de contrainte pour la contrainte UNIQUE ou PRIMARY KEY. Le REPLACE remplacera la ligne qui provoque la violation par la nouvelle ligne insérée ou mise à jour.
  • En cas de violation de contrainte NOT NULL, la clause REPLACE remplace la valeur NULL par la valeur par défaut de cette colonne. Si la colonne n'a pas de valeur par défaut, alors SQLite abandonnera l'instruction (l'instruction sera annulée)
  • SI la violation de contrainte CHECK se produit, la clause sera abandonnée.

Remarque: les 5 résolutions ci-dessus sont des options sur la façon dont vous souhaitez résoudre le conflit. Ce n'est pas nécessairement ce qui est applicable pour résoudre un conflit ou pour résoudre d'autres types de conflits.

Comment déclarer la clause CONFLICT

Vous pouvez déclarer la clause ON CONFLICT lorsque vous définissez une contrainte pour une définition de colonne dans la clause CREATE TABLE. En utilisant la syntaxe suivante:

Vous pouvez choisir l'une des cinq résolutions pour résoudre le conflit comme expliqué précédemment.

ON CONFLICT IGNORE Exemple

Étape 1) Créez un nouveau sujet de table comme suit:

CREATE TABLE [Sujets] ([SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE,[SubjectName] NVARCHAR NON NULL);

Notez que nous avons défini une contrainte PRIMARY KEY sur la colonne SubjectId. La contrainte de clé primaire n'autorise pas l'insertion de deux valeurs dupliquées dans la colonne SubjectId afin que toutes les valeurs de cette colonne soient uniques. Notez également que nous choisissons une résolution de conflit pour être « IGNORER ».

La commande devrait s'exécuter avec succès et vous ne devriez obtenir aucune erreur:

Étape 2) Maintenant, insérons quelques valeurs dans les nouveaux sujets de table, mais avec une valeur qui viole la contrainte de clé primaire:

INSÉRER DANS LES SUJETS VALEURS (1, 'Algèbre');INSÉRER DANS LES SUJETS VALEURS (2, «Cours de base de données»);INSERT INTO Subjects VALUES (2, 'Structures de données');INSERT INTO Subjects VALUES (4, 'Algorithmes');

Dans ces instructions INSERT, nous avons essayé d'insérer deux cours avec le même ID de sujet de clé primaire 2, ce qui constitue une violation de la contrainte de clé primaire.

Les commandes devraient fonctionner correctement et vous ne devriez obtenir aucune erreur. Comme suit:

Étape 3) Sélectionnez tous les sujets du tableau comme suit:

SELECT * FROM Sujets;

Cela vous donnera la liste des sujets:

Notez que seuls trois sujets ont été insérés " Algèbre, cours de base de données et algorithmes " au lieu de 4 lignes.

La ligne dont la valeur ne respecte pas la contrainte de clé primaire, à savoir «Structures de données», a été ignorée et n'a pas été insérée. Cependant, SQLite continue d'exécuter d'autres instructions après cette ligne.

Étape 4) SUPPRIMEZ les sujets du tableau pour le créer à nouveau avec une clause ON CONFLICT différente pour l'exemple suivant en exécutant la commande suivante:

DROP TABLE Subjects;

La commande drop supprime la table entière. Les sujets de table n'existent plus.

ON CONFLICT REPLACE Exemple

Étape 1) Créez un nouveau sujet de table comme suit:

CREATE TABLE [Sujets] ([SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,[SubjectName] NVARCHAR NON NULL);

Notez que nous avons défini une contrainte PRIMARY KEY sur la colonne SubjectId. La contrainte de clé primaire n'autorise pas l'insertion de deux valeurs dupliquées dans la colonne SubjectId afin que toutes les valeurs de cette colonne soient uniques.

Notez également que nous choisissons une option de résolution de conflit pour être " REMPLACER ". La commande devrait s'exécuter avec succès et vous ne devriez obtenir aucune erreur:

Étape 2) Maintenant, insérons quelques valeurs dans la nouvelle table Subjects, mais avec une valeur qui viole la contrainte de clé primaire:

INSÉRER DANS LES SUJETS VALEURS (1, 'Algèbre');INSÉRER DANS LES SUJETS VALEURS (2, «Cours de base de données»);INSERT INTO Subjects VALUES (2, 'Structures de données');INSERT INTO Subjects VALUES (4, 'Algorithmes');

Dans ces instructions INSERT, nous avons essayé d'insérer deux cours avec le même ID de sujet de clé primaire 2, ce qui constitue une violation de la contrainte de clé primaire.

Les commandes devraient fonctionner correctement et vous ne devriez obtenir aucune erreur. Comme suit:

Étape 3) Sélectionnez tous les sujets du tableau comme suit:

SELECT * FROM Sujets;

Cela vous donnera la liste des sujets:

Notez que seuls trois sujets ont été insérés " Algèbre, structures de données et algorithmes " alors que nous avons essayé d'insérer 4 lignes.

La ligne dont la valeur ne respecte pas la contrainte de clé primaire, à savoir « Structures de données », a remplacé la valeur « Cours de base de données » comme suit:

  • Les deux premières instructions d'insertion fonctionnent correctement sans problème. Deux matières Algèbre et Cours de base de données seront insérées avec les identifiants 1, 2.
  • Lorsque SQLite essaie d'exécuter la troisième instruction d'insertion avec SubjectId 2 et SubjectName " Structures de données ", il découvre qu'il existe déjà un sujet avec SubjectId = 2. Ce qui est une violation pour la contrainte de clé primaire définie sur la colonne SubjectId.
  • SQLite choisira une résolution de REMPLACEMENT pour ce conflit. Il remplace la valeur qui existe déjà dans la table des sujets par la nouvelle valeur de l'instruction d'insertion. Ainsi, le SubjectName " Database Course " sera remplacé par " Data Structures " SubjectName.

Résumé:

Les clauses INSERT, UPDATE et DELETE sont utilisées pour modifier les données dans la base de données SQLite. La clause CONFLICT est une clause puissante pour résoudre tout conflit entre les données et les données à modifier.