Déclencheur SQLite, vues et index avec exemple

Dans l'utilisation quotidienne de SQLite, vous aurez besoin de certains 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.

Vue SQLite

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 votre complex requêtes, et vous pouvez réutiliser ces requêtes quand vous le souhaitez en appelant directement la vue au lieu de réécrire à nouveau 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 la suitewing exemple, nous allons créer une vue avec le nom «Tous les étudiantsVoir" dans la base de données exemple "TutorielsSampleDB.db" comme suitwing:

Étape 1) Ouvrez Poste de travail et accédez à ce qui suitwing répertoire "C:\sqlite"Puis ouvrez"sqlite3.exe"

Vue SQLite

Étape 2) Ouvrez la base de données "TutorielsSampleDB.db» par le suivantwing commander:

Vue SQLite

Étape 3) Following est 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 :

Vue SQLite

É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 suivantewing commander:

SELECT name FROM sqlite_master WHERE type = 'view';

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

Vue SQLite

É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 ce qui suit.wing capture d'écran:

Vue SQLite

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 méthodes suivanteswing commandes:

  • 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 la suitewing Par exemple, nous ouvrirons une connexion à une base de données, puis créerons 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 le suiviwing commander:

.open TutorialsSampleDB.db

Étape 3) Écrivez le suivantwing commande 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;

Vue SQLite

Étape 4) Assurez-vous que la vue temporaire "Tous les étudiantsTempVIEW" est créé en exécutant le suiviwing commander:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Vue SQLite

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

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

.open TutorialsSampleDB.db

Étape 7) Exécutez le suiviwing commande 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. Autrewise, tant que vous maintenez la connexion avec la base de données ouverte, vous pourrez voir la vue temporaire avec les données.

Vue SQLite

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 suivantewing commande qui vous donne la liste des vues de la base de données :

SELECT name FROM sqlite_master WHERE type = 'view';

Vous ne trouverez aucune vue renvoyée car la vue a été supprimée, comme suitwing:

Vue SQLite

Index SQLite

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 scannerez tout le livre du début à la fin jusqu'à ce que vous trouviez le mot-clé que vous recherchez.arching pour. 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 une table SQLite avec des critères de recherche, SQLite recherchera sur toutes les lignes de la table 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 soiarching des données sur une table. Lorsque vous créez un index sur une colonne, SQLite crée 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 ne recherchera pas toute la table. 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 voyezarching for, 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 INDICE

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 éléments suivantswing mots-clés « 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 la suitewing exemple, nous allons créer un index « IndexNomÉtudiant» sur la table des étudiants dans le «de" base de données comme suitwing:

É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 le suivantwing commander:

.open TutorialsSampleDB.db

Étape 4) Créer un nouvel index "IndexNomÉtudiant" en utilisant le suivantwing commander:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Vous ne devriez voir aucun résultat pour ceci :

Index SQLite

Étape 5) Pour vous assurer que l'index a été créé, vous pouvez exécuter la commande suivantewing requête, qui vous donne 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é :

Index SQLite

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 le cas suivantwing indice:
    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.

Déclencheur SQLite

Introduction au déclencheur SQLite

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'un des éléments suivantswing des actions se produisent 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 suitwing:

  • 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 suitwing:

    • À 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 la suitewing, nous allons créer un déclencheur qui se déclenchera avant d'insérer un nouvel élève dans le «de" 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 suitwing:

    É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 le suiviwing commander:

    .open TutorialsSampleDB.db

    Étape 3) créer le déclencheur "Insérer dans le déclencheur d'étudiant" En exécutant le suiviwing commander:

    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 :

    Déclencheur SQLite

    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 "neufsLe 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 le suivantwing commande qui insérera un nouvel étudiant dans la table des étudiants :

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

    Étape 5) Écrivez le suivantwing commande 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 :

    Déclencheur SQLite

    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 la suitewing Par exemple, 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 le suiviwing commander:

    .open TutorialsSampleDB.db

    Étape 3) Créer un nouveau déclencheur "PreventUpdateStudentName" sur la table "de" en exécutant le suiviwing commander

    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 le suiviwing commande update, qui mettra à jour le nom de l'élève "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 suitwing:

    Déclencheur SQLite

    Étape 5) Exécutez le suiviwing commande, 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 :

    Déclencheur SQLite

    Résumé

    Les vues, index et déclencheurs sont des outils très puissants pour administrer un Base de données SQLite. 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.