SQLite Déclencheur, vues et index avec exemple

Par : Marcus Allen Marcus Allen
Hours Le kit de préparation mis à jour

Dans l'utilisation quotidienne de SQLite, vous aurez besoin de quelques outils d'administration sur votre base de données. Vous pouvez également les utiliser pour rendre l'interrogation de la base de données plus efficace en créant des index, ou plus réutilisable en créant des vues.

SQLite Résumé

Les vues sont très similaires aux tableaux. Mais les vues sont des tableaux logiques ; ils ne sont pas stockés physiquement comme les tables. Une vue est composée d'une instruction select.

Vous pouvez définir une vue pour vos requêtes complexes et réutiliser ces requêtes à tout moment en appelant la vue directement au lieu de réécrire les requêtes.

Instruction CREATE VIEW

Pour créer une vue sur une base de données, vous pouvez utiliser l'instruction CREATE VIEW suivie du nom de la vue, puis placer la requête souhaitée après cela.

Mise en situation : Dans l’exemple suivant, nous allons créer une vue avec le nom «Tous les étudiantsVoir" dans la base de données exemple "TutorielsSampleDB.db" comme suit:

Étape 1) Ouvrez Poste de travail et accédez au répertoire suivant «C:\sqlite"Puis ouvrez"sqlite3.exe"

SQLite Résumé

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

SQLite Résumé

Étape 3) Voici une syntaxe de base de la commande sqlite3 pour créer la vue

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Il ne devrait y avoir aucun résultat de la commande comme celui-ci :

SQLite Résumé

Étape 4) Pour vous assurer que la vue est créée, vous pouvez sélectionner la liste des vues dans la base de données en exécutant la commande suivante :

SELECT name FROM sqlite_master WHERE type = 'view';

Vous devriez voir la vue "Tous les étudiantsVoir» est renvoyé :

SQLite Résumé

Étape 5) Maintenant que notre vue est créée, vous pouvez l'utiliser comme une table normale, comme ceci :

SELECT * FROM AllStudentsView;

Cette commande interrogera la vue « AllStudents » et en sélectionnera toutes les lignes, comme indiqué dans la capture d'écran suivante :

SQLite Résumé

Vues temporaires

Les vues temporaires sont temporaires pour la connexion à la base de données actuelle utilisée pour la créer. Ensuite, si vous fermez la connexion à la base de données, toutes les vues temporaires seront automatiquement supprimées. Les vues temporaires sont créées à l'aide de l'une des commandes suivantes :

  • CRÉER UNE VUE TEMPÉRATURE, ou
  • CRÉER UNE VUE TEMPORAIRE.

Les vues temporaires sont utiles si vous souhaitez effectuer certaines opérations pour le moment et que vous n'avez pas besoin qu'il s'agisse d'une vue permanente. Ainsi, vous créez simplement une vue temporaire, puis effectuez votre traitement en utilisant cette vue. Later lorsque vous fermez la connexion avec la base de données, elle sera automatiquement supprimée.

Mise en situation :

Dans l'exemple suivant, nous allons ouvrir une connexion à une base de données, puis créer une vue temporaire.

Après cela, nous fermerons cette connexion et vérifierons si la vue temporaire existe toujours ou non.

Étape 1) Ouvrez sqlite3.exe depuis le répertoire «C:\sqlite» comme expliqué précédemment.

Étape 2) Ouvrir une connexion à la base de données «TutorielsSampleDB.db» en exécutant la commande suivante :

.open TutorialsSampleDB.db

Étape 3) Écrivez la commande suivante qui créera une vue temporaire «Tous les étudiantsTempView":

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

SQLite Résumé

Étape 4) Assurez-vous que la vue temporaire "Tous les étudiantsTempView" est créé en exécutant la commande suivante :

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite Résumé

Étape 5) Fermez sqlite3.exe et ouvrez-le à nouveau.

Étape 6) Ouvrir une connexion à la base de données «TutorielsSampleDB.db» par la commande suivante :

.open TutorialsSampleDB.db

Étape 7) Exécutez la commande suivante pour obtenir la liste des vues temporaires créées sur la base de données :

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Vous ne devriez voir aucune sortie car la vue temporaire que nous avons créée est supprimée lorsque nous avons fermé la connexion à la base de données à l'étape précédente. Sinon, tant que vous maintenez la connexion avec la base de données ouverte, vous pourrez voir la vue temporaire avec les données.

SQLite Résumé

Notes:

  • Vous ne pouvez pas utiliser les instructions INSERT, DELETE ou UPDATE avec des vues, vous seul pouvez utiliser la commande « sélectionner parmi les vues » comme indiqué à l'étape 5 de l'exemple CREATE View.
  • Pour supprimer une VUE, vous pouvez utiliser l'instruction « DROP VIEW » :
DROP VIEW AllStudentsView;

Pour vous assurer que la vue est supprimée, vous pouvez exécuter la commande suivante qui vous donne la liste des vues de la base de données :

SELECT name FROM sqlite_master WHERE type = 'view';

Aucune vue ne sera renvoyée car la vue a été supprimée, comme suit :

SQLite Résumé

ARTICLES LIÉS

SQLite Sommaire

Si vous avez un livre et que vous souhaitez rechercher un mot-clé sur ce livre. Vous rechercherez ce mot-clé dans l’index du livre. Ensuite, vous accéderez au numéro de page de ce mot-clé pour lire plus d’informations sur ce mot-clé.

Cependant, s'il n'y a pas d'index sur ce livre ni de numéros de page, vous parcourrez tout le livre du début à la fin jusqu'à ce que vous trouviez le mot-clé que vous recherchez. Et cela est très difficile surtout lorsque vous disposez d’un index et d’un processus très lent pour rechercher un mot-clé.

Index dans SQLite (et le même concept valable pour d'autres Systèmes de gestion de bases de données également) fonctionne de la même manière que les index que l’on trouve au dos des livres.

Lorsque vous recherchez certaines lignes dans un SQLite tableau avec critères de recherche, SQLite recherchera sur toutes les lignes du tableau jusqu'à ce qu'il trouve les lignes que vous recherchez qui correspondent aux critères de recherche. Et ce processus devient très lent lorsque vous avez des tables plus grandes.

Les index accéléreront les requêtes de recherche de données et aideront à effectuer la récupération de données à partir des tables. Les index sont définis sur les colonnes du tableau.

Améliorer les performances avec les index :

Les index peuvent améliorer les performances de la recherche de données sur une table. Lorsque vous créez un index sur une colonne, SQLite créera une structure de données pour cet index où chaque valeur de champ a un pointeur vers la ligne entière à laquelle appartient la valeur.

Ensuite, si vous exécutez une requête avec une condition de recherche sur une colonne faisant partie d'un index, SQLite recherchera d'abord la valeur sur l'index. SQLite je ne scannerai pas toute la table pour cela. Ensuite, il lira l'emplacement où pointe la valeur pour la ligne du tableau. SQLite localisera la ligne à cet emplacement et la récupérera.

Cependant, si la colonne que vous recherchez ne fait pas partie d'un index, SQLite effectuera une analyse des valeurs de colonne pour trouver les données que vous recherchez. Ce sera généralement un processus plus lent s’il n’y a pas d’index.

Imaginez un livre sans index et vous devez rechercher un mot spécifique. Vous scannerez tout le livre de la première page à la dernière page à la recherche de ce mot. Cependant, si vous avez un index sur ce livre, vous chercherez d’abord le mot dessus. Obtenez le numéro de page où il se trouve, puis accédez-y. Ce qui sera beaucoup plus rapide que de numériser le livre entier d’un bout à l’autre.

SQLite CRÉER UN INDEX

Pour créer un index sur une colonne, vous devez utiliser la commande CREATE INDEX. Et vous devez le définir comme suit :

  • Vous devez spécifier le nom de l'index après la commande CREATE INDEX.
  • Après le nom de l'index, vous devez mettre le mot-clé « ON », suivi du nom de la table dans laquelle l'index sera créé.
  • Ensuite, la liste des noms de colonnes utilisés pour l'index.
  • Vous pouvez utiliser l'un des mots-clés suivants « ASC » ou « DESC » après n'importe quel nom de colonne pour spécifier un ordre de tri utilisé pour classer les données d'index.

Mise en situation :

Dans l'exemple suivant, nous allons créer un index « IndexNomÉtudiant» sur la table des étudiants dans le «Étudiante" base de données comme suit :

Étape 1) Accédez au dossier «C:\sqlite» comme expliqué précédemment.

Étape 2) Ouvrez sqlite3.exe.

Étape 3) Ouvrez la base de données "TutorielsSampleDB.db» par la commande suivante :

.open TutorialsSampleDB.db

Étape 4) Créer un nouvel index "IndexNomÉtudiant" en utilisant la commande suivante :

CREATE INDEX StudentNameIndex ON Students(StudentName);

Vous ne devriez voir aucun résultat pour ceci :

SQLite Sommaire

Étape 5) Pour vous assurer que l'index a été créé vous pouvez exécuter la requête suivante, qui vous donnera la liste des index créés dans la table Etudiants :

PRAGMA index_list(Students);

Vous devriez voir l'index que nous venons de créer renvoyé :

SQLite Sommaire

Notes:

  • Les index peuvent être créés non seulement sur la base de colonnes mais également d'expressions. Quelque chose comme ça:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

Le « OrderTotalIndex » sera basé sur la colonne OrderId ainsi que sur la multiplication de la valeur de la colonne Quantité et de la valeur de la colonne Prix. Ainsi, toute requête pour « OrderId » et « Quantity*Price » sera efficace car la requête utilisera l’index.

  • Si vous avez spécifié une clause WHERE dans l'instruction CREATE INDEX, l'index sera un index partiel. Dans ce cas, il y aura des entrées dans l'index uniquement pour les lignes qui correspondent aux conditions de la clause WHERE. Par exemple, dans l'index suivant : 
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;

    (Dans l'exemple ci-dessus, l'index sera un index partiel car une clause WHERE est spécifiée. Dans ce cas, l'index sera appliqué uniquement aux commandes dont la valeur de quantité est supérieure à 10000 . Notez que cet index est appelé un index partiel. index à cause de la clause WHERE, et non de l'expression utilisée dessus. Cependant, vous pouvez utiliser les expressions avec des index normaux.)

  • Vous pouvez utiliser l'instruction CREATE UNIQUE INDEX au lieu de CREATE INDEX pour éviter les entrées en double pour les colonnes et ainsi toutes les valeurs de la colonne indexée seront uniques.
  • Pour supprimer un index, utilisez la commande DROP INDEX suivie du nom de l'index à supprimer.

SQLite Gâchette

Introduction à la SQLite Gâchette

Les déclencheurs sont des opérations prédéfinies automatiques exécutées lorsqu'une action spécifique se produit sur une table de base de données. Un déclencheur peut être défini pour être déclenché chaque fois que l'une des actions suivantes se produit sur une table :

  • INSÉRER dans un tableau.
  • SUPPRIMER les lignes d'un tableau.
  • MISE À JOUR d'une des colonnes du tableau.

SQLite prend en charge le déclencheur FOR EACH ROW afin que les opérations prédéfinies dans le déclencheur soient exécutées pour toutes les lignes impliquées dans les actions survenues sur la table (qu'il s'agisse d'insertion, de suppression ou de mise à jour).

SQLite CRÉER UN DÉCLENCHEUR

Pour créer un nouveau TRIGGER, vous pouvez utiliser l'instruction CREATE TRIGGER comme suit :

  • Après CREATE TRIGGER, vous devez spécifier un nom de déclencheur.
  • Après le nom du déclencheur, vous devez spécifier quand exactement le nom du déclencheur doit être exécuté. Vous avez trois options :
  • AVANT – le déclencheur sera exécuté avant l’instruction INSERT, UPDATE ou delete spécifiée.
  • Après – le déclencheur sera exécuté après l'instruction INSERT, UPDATE ou delete spécifiée.
  • INSTEAD OF – Il remplacera l'action qui a déclenché le déclencheur par l'instruction spécifiée dans TRIGGER. Le déclencheur INSTEAD OF n’est pas applicable aux tables, uniquement aux vues.
  • Ensuite, vous devez spécifier le type d'action, le déclencheur se déclenchera quand cela se produira. Soit DELETE, INSERT ou UPDATE.
  • Vous pouvez choisir un nom de colonne facultatif afin que le déclencheur ne se déclenche que si l'action s'est produite sur cette colonne.
  • Ensuite, vous devez spécifier le nom de la table dans laquelle le déclencheur sera créé.
  • Dans le corps du déclencheur, vous devez spécifier l'instruction qui doit être exécutée pour chaque ligne lorsque le déclencheur est déclenché.

    • Les déclencheurs seront activés (déclenchés) uniquement en fonction du type d'instruction spécifié dans la commande create trigger. Par exemple:

    • Le déclencheur BEFORE INSERT sera activé (déclenché) avant toute instruction d'insertion.
    • Le déclencheur AFTER UPDATE sera activé (déclenché) après toute instruction de mise à jour,… et ainsi de suite.

    À l'intérieur du déclencheur, vous pouvez faire référence aux valeurs nouvellement insérées à l'aide du mot-clé « new ». Vous pouvez également faire référence aux valeurs supprimées ou mises à jour à l’aide de l’ancien mot-clé. Comme suit:

    • À l’intérieur des déclencheurs INSERT – un nouveau mot-clé peut être utilisé.
    • À l’intérieur des déclencheurs UPDATE – des mots-clés nouveaux et anciens peuvent être utilisés.
    • À l’intérieur des déclencheurs DELETE – l’ancien mot-clé peut être utilisé.

    Exemple

    Dans ce qui suit, nous allons créer un déclencheur qui se déclenchera avant d'insérer un nouvel étudiant dans le «Étudiante" tableau.

    Il enregistrera l'étudiant nouvellement inséré dans le tableau "Journal des étudiants" avec un horodatage automatique pour la date et l'heure actuelles à laquelle l'instruction d'insertion s'est produite. Comme suit:

    Étape 1) Accédez au répertoire «C:\sqlite" et exécutez sqlite3.exe.

    Étape 2) Ouvrez la base de données "TutorielsSampleDB.db» en exécutant la commande suivante :

    .open TutorialsSampleDB.db

    Étape 3) créer le déclencheur "Insérer dans le déclencheur d'étudiant» En exécutant la commande suivante :

    CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

    La fonction "dateheure()" vous donnera l'horodatage de la date actuelle à laquelle l'instruction d'insertion s'est produite. Afin que nous puissions enregistrer la transaction d'insertion avec des horodatages automatiques ajoutés à chaque transaction.

    La commande devrait s'exécuter correctement et vous n'obtenez aucun résultat :

    SQLite Gâchette

    Le déclencheur »Insérer dans le déclencheur d'étudiant» se déclenchera à chaque fois que vous insérerez un nouvel étudiant dans la table des étudiants. Le "nouvelleLe mot-clé « fait référence aux valeurs qui seront insérées. Par exemple, le «nouveau.StudentId» sera la carte d’étudiant qui sera insérée.

    Nous allons maintenant tester le comportement du déclencheur lorsque nous insérons un nouvel étudiant.

    Étape 4) Écrivez la commande suivante qui insérera un nouvel étudiant dans la table des étudiants :

    INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

    Étape 5) Écrivez la commande suivante qui sélectionnera toutes les lignes du «Journal des étudiants" tableau:

    SELECT * FROM StudentsLog;

    Vous devriez voir une nouvelle ligne renvoyée pour le nouvel étudiant que nous venons d'insérer :

    SQLite Gâchette

    Cette ligne a été insérée par le déclencheur avant d'insérer le nouvel étudiant avec l'identifiant 11.

    Dans cet exemple, nous avons utilisé le déclencheur « Insérer dans le déclencheur d'étudiant " nous avons créé, pour enregistrer toutes les transactions d'insertion dans la table "Journal des étudiants» automatiquement. De la même manière, vous pouvez enregistrer toute mise à jour ou supprimer des déclarations.

    Empêcher les mises à jour involontaires avec des déclencheurs :

    En utilisant les déclencheurs BEFORE UPDATE sur une table, vous pouvez empêcher les instructions de mise à jour sur une colonne en fonction d'une expression.

    Exemple

    Dans l'exemple suivant, nous empêcherons toute instruction update de mettre à jour la colonne « studentname » dans la table Students :

    Étape 1) Accédez au répertoire «C:\sqlite" et exécutez sqlite3.exe.

    Étape 2) Ouvrez la base de données "TutorielsSampleDB.db» en exécutant la commande suivante :

    .open TutorialsSampleDB.db

    Étape 3) Créer un nouveau déclencheur "PreventUpdateStudentName" sur la table "Étudiante» en exécutant la commande suivante

    CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

    Le "ÉLEVER" La commande générera une erreur avec un message d'erreur " Vous ne pouvez pas mettre à jour le nom de l'étudiant ", et cela empêchera l'exécution de l'instruction de mise à jour.

    Nous allons maintenant vérifier que le déclencheur fonctionne bien et qu'il empêche toute mise à jour de la colonne studentname.

    Étape 4) Exécutez la commande de mise à jour suivante, qui mettra à jour le nom de l'étudiant "Jacques" être "Jack1" .

    UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

    Vous devriez recevoir le message d'erreur que nous avons spécifié sur le déclencheur, disant que «Vous ne pouvez pas mettre à jour le nom de l'étudiant" comme suit:

    SQLite Gâchette

    Étape 5) Exécutez la commande suivante, qui sélectionnera la liste des noms des étudiants dans la table des étudiants.

    SELECT StudentName FROM Students;

    Vous devriez voir que le nom de l'élève « Jack » est toujours le même et ne change pas :

    SQLite Gâchette

    Résumé

    Les vues, index et déclencheurs sont des outils très puissants pour administrer un SQLite base de données. Vous pouvez suivre les opérations de modification de données lorsqu'elles se produisent sur une table. Vous pouvez également optimiser l'opération de récupération des données de la base de données en créant des index.

    Tu pourrais aimer: