SQLite Viste, indici e trigger con esempio

Nell'uso quotidiano di SQLite, avrai bisogno di alcuni strumenti amministrativi sul tuo database. È inoltre possibile utilizzarli per eseguire query sul database in modo più efficiente creando indici o più riutilizzabili creando visualizzazioni.

SQLite Visualizzare

Le viste sono molto simili alle tabelle. Ma le viste sono tabelle logiche; non vengono archiviati fisicamente come le tabelle. Una vista è composta da un'istruzione select.

Puoi definire una vista per le tue query complesse e riutilizzarle ogni volta che vuoi chiamando direttamente la vista anziché riscrivere nuovamente le query.

Istruzione CREATE VIEW

Per creare una vista su un database, puoi utilizzare l'istruzione CREATE VIEW seguita dal nome della vista, quindi inserire la query desiderata.

Esempio: Nell'esempio seguente creeremo una vista con il nome "AllStudentsView" nel database di esempio "TutorialsSampleDB.db" come segue:

Passo 1) Apri Risorse del computer e vai alla seguente directory "C:\sqlite", quindi apri "sqlite3.exe":

Passo 2) Aprire il database “TutorialsSampleDB.db” con il seguente comando:

Passo 3) Di seguito è riportata una sintassi di base del comando sqlite3 per creare la vista

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;

Non dovrebbe esserci alcun output dal comando come questo:

Passo 4) Per garantire che la vista venga creata, è possibile selezionare l'elenco delle viste nel database eseguendo il seguente comando:

SELECT name FROM sqlite_master WHERE type = 'view';

Dovresti vedere che viene restituita la vista "AllStudentsView":

Passo 5) Ora che la nostra vista è stata creata, puoi usarla come una normale tabella in questo modo:

SELECT * FROM AllStudentsView;

Questo comando interrogherà la vista “AllStudents” e selezionerà tutte le righe da essa come mostrato nello screenshot seguente:

Viste temporanee

Le viste temporanee sono temporanee per la connessione al database corrente utilizzata per crearla. Quindi, se si chiude la connessione al database, tutte le viste temporanee verranno eliminate automaticamente. Le viste temporanee vengono create utilizzando uno dei seguenti comandi:

• CREA VISUALIZZAZIONE TEMPORANEA, o
• CREA VISTA TEMPORANEA.

Le viste temporanee sono utili se vuoi eseguire alcune operazioni per il momento e non è necessario che siano una vista permanente. Quindi, crei semplicemente una vista temporanea, quindi esegui l'elaborazione utilizzando quella vista. Later quando chiudi la connessione con il database, verrà eliminato automaticamente.

Esempio:

Nell'esempio seguente, apriremo una connessione al database e poi creeremo una vista temporanea.

Successivamente, chiuderemo la connessione e controlleremo se la vista temporanea esiste ancora o meno.

Passo 1) Aprire sqlite3.exe dalla directory "C:\sqlite" come spiegato in precedenza.

Passo 2) Apri una connessione al database "TutorialsSampleDB.db" eseguendo il seguente comando:

.open TutorialsSampleDB.db

Passo 3) Scrivi il seguente comando che creerà una vista temporanea "AllStudentsTempView":

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;

Passo 4) Assicurarsi che la vista temporanea "AllStudentsTempView" venga creata eseguendo il seguente comando:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Passo 5) Chiudi sqlite3.exe e aprilo di nuovo.

Passo 6) Apri una connessione al database "TutorialsSampleDB.db" con il seguente comando:

.open TutorialsSampleDB.db

Passo 7) Eseguire il seguente comando per ottenere l'elenco delle viste temporanee create sul database:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Non dovresti vedere alcun output, poiché la vista temporanea che abbiamo creato è stata eliminata quando abbiamo chiuso la connessione al database nel passaggio precedente. Altrimenti, finché mantieni aperta la connessione con il database, sarai in grado di vedere la vista temporanea con i dati.

Note:

• Non è possibile utilizzare le istruzioni INSERT, DELETE o UPDATE con le viste, solo è possibile utilizzare il comando "seleziona dalle viste" come mostrato nel passaggio 5 nell'esempio CREATE View.
• Per eliminare una VIEW, è possibile utilizzare l'istruzione "DROP VIEW":

DROP VIEW AllStudentsView;

Per assicurarti che la vista venga eliminata, puoi eseguire il seguente comando che ti fornirà l'elenco delle viste nel database:

SELECT name FROM sqlite_master WHERE type = 'view';

Non troverai alcuna visualizzazione restituita poiché la visualizzazione è stata eliminata, come segue:

Oltre a riutilizzare query complesse con viste, SQLite Inoltre, grazie agli indici, recupera più velocemente le righe corrispondenti.

SQLite Indice

Se hai un libro e desideri cercare una parola chiave su quel libro. Cercherai quella parola chiave nell'indice del libro. Quindi passerai al numero di pagina relativo a quella parola chiave per leggere ulteriori informazioni su quella parola chiave.

Tuttavia, se non c'è un indice su quel libro né numeri di pagina, scannerizzerai l'intero libro dall'inizio alla fine finché non trovi la parola chiave che stai cercando. E questo è molto difficile, specialmente quando hai un indice e un processo molto lento per cercare una parola chiave.

Indici dentro SQLite (e lo stesso concetto vale per other sistemi di gestione di database (anche) funziona allo stesso modo degli indici che si trovano sul retro dei libri.

Quando cerchi alcune righe in un file SQLite tabella con criteri di ricerca, SQLite cercherà in tutte le righe della tabella finché non troverà le righe che stai cercando che corrispondono ai criteri di ricerca. E questo processo diventa molto lento quando si hanno tabelle più grandi.

Gli indici velocizzeranno le query di ricerca dei dati e aiuteranno a eseguire il recupero dei dati dalle tabelle. Gli indici sono definiti nelle colonne della tabella.

Miglioramento delle prestazioni con gli indici:

Gli indici possono migliorare le prestazioni della ricerca di dati in una tabella. Quando crei un indice su una colonna, SQLite creerà una struttura dati per quell'indice in cui ogni valore di campo ha un puntatore all'intera riga a cui appartiene il valore.

Quindi, se esegui una query con una condizione di ricerca su una colonna che fa parte di un indice, SQLite cercherà prima il valore sull'indice. SQLite non scansionerà l'intera tabella per questo. Quindi leggerà la posizione in cui punta il valore per la riga della tabella. SQLite individuerà la riga in quella posizione e la recupererà.

Tuttavia, se la colonna che stai cercando non fa parte di un indice, SQLite eseguirà una scansione dei valori delle colonne per trovare i dati che stai cercando. Di solito il processo sarà più lento se non è presente alcun indice.

Immagina un libro senza indice e devi cercare una parola specifica. Scansionerai l'intero libro dalla prima all'ultima pagina cercando quella parola. Tuttavia, se hai un indice su quel libro, cercherai prima la parola su di esso. Ottieni il numero di pagina in cui si trova, quindi raggiungilo. Il che sarà molto più veloce che scansionare l'intero libro da copertina a copertina.

SQLite CREA INDICE

Per creare un indice su una colonna, dovresti usare il comando CREATE INDEX. E dovresti definirlo come segue:

• È necessario specificare il nome dell'indice dopo il comando CREATE INDEX.
• Dopo il nome dell'indice bisogna mettere la parola chiave “ON”, seguita dal nome della tabella in cui verrà creato l'indice.
• Quindi l'elenco dei nomi di colonna utilizzati per l'indice.
• È possibile utilizzare una delle seguenti parole chiave "ASC" o "DESC" dopo qualsiasi nome di colonna per specificare un criterio di ordinamento utilizzato per ordinare i dati dell'indice.

Esempio:

Nell'esempio seguente, creeremo un indice "StudentNameIndex" sulla tabella degli studenti nel database "Students" come segue:

Passo 1) Come spiegato in precedenza, accedi alla cartella "C:\sqlite".

Passo 2) Apri sqlite3.exe.

Passo 3) Aprire il database “TutorialsSampleDB.db” con il seguente comando:

.open TutorialsSampleDB.db

Passo 4) Crea un nuovo indice "StudentNameIndex" utilizzando il seguente comando:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Non dovresti vedere alcun output per questo:

Passo 5) Per assicurarti che l'indice sia stato creato, puoi eseguire la seguente query, che ti fornirà l'elenco degli indici creati nella tabella Studenti:

PRAGMA index_list(Students);

Dovresti vedere restituito l'indice che abbiamo appena creato:

Note:

• Gli indici possono essere creati non solo in base alle colonne ma anche alle espressioni. Qualcosa come questo:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

Il valore "OrderTotalIndex" sarà basato sulla colonna OrderId e anche sulla moltiplicazione del valore della colonna Quantità e del valore della colonna Prezzo. Pertanto, qualsiasi query per "OrderId" e "Quantity*Price" sarà efficiente poiché utilizzerà l'indice.

• Se hai specificato una clausola WHERE nell'istruzione CREATE INDEX, l'indice sarà un indice parziale. In questo caso, ci saranno voci nell'indice solo per le righe che corrispondono alle condizioni nella clausola WHERE. Ad esempio, nel seguente indice:

  CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
  WHERE Quantity > 10000;

  (Nell'esempio precedente, l'indice sarà un indice parziale poiché è specificata una clausola WHERE. In questo caso, l'indice verrà applicato solo a quegli ordini con un valore di quantità maggiore di 10000. Si noti che questo indice è chiamato parziale indice a causa della clausola WHERE, non dell'espressione utilizzata al suo interno. Tuttavia, è possibile utilizzare le espressioni con indici normali.)

• È possibile utilizzare l'istruzione CREATE UNIQUE INDEX invece di CREATE INDEX per evitare voci duplicate per le colonne e quindi tutti i valori per la colonna indicizzata saranno univoci.
• Per eliminare un indice, utilizzare il comando DROP INDEX seguito dal nome dell'indice da eliminare.

Mentre gli indici rendono le letture più veloci, i trigger permettono SQLite Risponde automaticamente ogni volta che i dati cambiano.

SQLite grilletto

Introduzione alla SQLite grilletto

I trigger sono operazioni automatiche predefinite eseguite quando si verifica un'azione specifica su una tabella di database. Un trigger può essere definito in modo che venga attivato ogni volta che si verifica una delle seguenti azioni su una tabella:

• INSERIRE in una tabella.
• ELIMINA righe da una tabella.
• AGGIORNA una delle colonne della tabella.

SQLite supporta il trigger FOR EACH ROW in modo che le operazioni predefinite nel trigger vengano eseguite per tutte le righe coinvolte nelle azioni eseguite sulla tabella (siano esse inserimento, eliminazione o aggiornamento).

SQLite CREARE TRIGGER

Per creare un nuovo TRIGGER, è possibile utilizzare l'istruzione CREATE TRIGGER come segue:

• Dopo CREATE TRIGGER, dovresti specificare un nome di trigger.
• Dopo il nome del trigger è necessario specificare quando esattamente il nome del trigger deve essere eseguito. Hai tre opzioni:
  • BEFORE – il trigger verrà eseguito prima dell'istruzione INSERT, UPDATE o delete specificata.
  • After: il trigger verrà eseguito dopo l'istruzione INSERT, UPDATE o delete specificata.
  • INVECE DI – Sostituirà l'azione avvenuta che ha attivato il trigger con l'istruzione specificata nel TRIGGER. Il trigger INSTEAD OF non è applicabile alle tabelle, solo alle viste.
• Quindi, devi specificare il tipo di azione, il trigger si attiverà quando accade. ELIMINA, INSERISCI o AGGIORNA.
• Puoi scegliere un nome di colonna facoltativo in modo che l'attivatore non venga attivato a meno che l'azione non sia avvenuta su quella colonna.
• Quindi devi specificare il nome della tabella in cui verrà creato il trigger.
• All'interno del corpo del trigger, dovresti specificare l'istruzione che dovrebbe essere eseguita per ogni riga quando viene attivato il trigger.

I trigger verranno attivati ​​(attivati) solo in base al tipo di istruzione specificata nel comando create trigger. Per esempio:

• Il trigger BEFORE INSERT verrà attivato (attivato) prima di qualsiasi istruzione di inserimento.
• Il trigger AFTER UPDATE verrà attivato (attivato) dopo qualsiasi istruzione di aggiornamento, ... e così via.

All'interno del trigger, puoi fare riferimento ai valori appena inseriti usando la parola chiave "new". Inoltre, puoi fare riferimento ai valori eliminati o aggiornati usando la parola chiave old. Come segue:

• All'interno dei trigger INSERT: è possibile utilizzare una nuova parola chiave.
• All'interno dei trigger UPDATE: è possibile utilizzare parole chiave nuove e vecchie.
• All'interno dei trigger DELETE: è possibile utilizzare la vecchia parola chiave.

Esempio

Di seguito, creeremo un trigger che si attiverà prima di inserire un nuovo studente nella tabella "Studenti".

Il sistema registrerà il nuovo studente inserito nella tabella "StudentsLog" con un timestamp automatico corrispondente alla data e all'ora correnti in cui è avvenuta l'istruzione di inserimento. Come segue:

Passo 1) Accedere alla directory "C:\sqlite" ed eseguire sqlite3.exe.

Passo 2) Aprire il database “TutorialsSampleDB.db” eseguendo il seguente comando:

.open TutorialsSampleDB.db

Passo 3) crea il trigger "InsertIntoStudentTrigger" eseguendo il seguente comando:

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

La funzione "datetime()" restituisce la data e l'ora correnti in cui è stata eseguita l'istruzione INSERT. In questo modo possiamo registrare la transazione di inserimento con timestamp aggiunti automaticamente a ciascuna transazione.

Il comando dovrebbe essere eseguito correttamente e non viene visualizzato alcun output:

Il trigger "InsertIntoStudentTrigger" si attiverà ogni volta che si inserisce un nuovo studente nella tabella degli studenti. La parola chiave "new" si riferisce ai valori che verranno inseriti. Ad esempio, "new.StudentId" sarà l'ID dello studente che verrà inserito.

Ora testeremo come si comporta il trigger quando inseriamo un nuovo studente.

Passo 4) Scrivi il seguente comando che inserirà un nuovo studente nella tabella degli studenti:

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

Passo 5) Scrivi il seguente comando che selezionerà tutte le righe dalla tabella "StudentsLog":

SELECT * FROM StudentsLog;

Dovresti vedere una nuova riga restituita per il nuovo studente che abbiamo appena inserito:

Questa riga è stata inserita dal trigger prima di inserire il nuovo studente con ID 11.

In questo esempio abbiamo utilizzato il trigger "InsertIntoStudentTrigger" che abbiamo creato, per registrare automaticamente tutte le transazioni di inserimento nella tabella "StudentsLog". Allo stesso modo è possibile registrare qualsiasi istruzione di aggiornamento o eliminazione.

Prevenire aggiornamenti involontari con trigger:

Utilizzando i trigger BEFORE UPDATE su una tabella, è possibile impedire le istruzioni di aggiornamento su una colonna in base a un'espressione.

Esempio

Nell'esempio seguente, impediremo a qualsiasi istruzione di aggiornamento di aggiornare la colonna "studentname" nella tabella Studenti:

Passo 1) Accedere alla directory "C:\sqlite" ed eseguire sqlite3.exe.

Passo 2) Aprire il database “TutorialsSampleDB.db” eseguendo il seguente comando:

.open TutorialsSampleDB.db

Passo 3) Crea un nuovo trigger "preventUpdateStudentName" sulla tabella "Students" eseguendo il seguente comando

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

Il comando "RAISE" genererà un errore con il messaggio "Non è possibile aggiornare studentname" e impedirà l'esecuzione dell'istruzione di aggiornamento.

Ora verificheremo che il trigger funzioni correttamente e impedisca qualsiasi aggiornamento per la colonna nomestudente.

Passo 4) Eseguire il seguente comando di aggiornamento, che aggiornerà il nome dello studente "Jack" in "Jack1".

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

Dovresti ricevere il messaggio di errore specificato nel trigger, che indica "Non è possibile aggiornare studentname", come segue:

Passo 5) Eseguire il seguente comando, che selezionerà l'elenco dei nomi degli studenti dalla tabella degli studenti.

SELECT StudentName FROM Students;

Dovresti vedere che il nome dello studente "Jack" è sempre lo stesso e non cambia:

DOMANDE FREQUENTI