SQLite Trigger, visualizzazioni e indice con esempio
Marco Allen
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 "Tutti gli studenti Visualizza"nel database di esempio"TutorialSampleDB.db" come segue:
Passo 1) Aprire Risorse del computer e andare alla seguente directory “C:\sqlite" e poi apri "sqlite3.exe"
Passo 2) Apri la banca dati”TutorialSampleDB.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 la vista "Tutti gli studenti Visualizza" viene restituito:
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) Apri sqlite3.exe dalla directory “C:\sqlite"come spiegato prima.
Passo 2) Aprire una connessione al database “TutorialSampleDB.db" eseguendo il seguente comando:
.open TutorialsSampleDB.db
Passo 3) Scrivi il seguente comando che creerà una vista temporanea "TuttiStudentiTempView":
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 visualizzazione temporanea “TuttiStudentiTempView" viene creato eseguendo il seguente comando:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Passo 5) Chiudi sqlite3.exe e aprilo di nuovo.
Passo 6) Aprire una connessione al database “TutorialSampleDB.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:
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 “IndiceNomeStudente" sul tavolo degli studenti nella sezione "Gli studenti"database come segue:
Passo 1) Passare alla cartella “C:\sqlite"come spiegato prima.
Passo 2) Apri sqlite3.exe.
Passo 3) Apri la banca dati”TutorialSampleDB.db" con il seguente comando:
.open TutorialsSampleDB.db
Passo 4) Crea un nuovo indice “StudentNameIndice" 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.
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.
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 verrà attivato prima di inserire un nuovo studente nel "Gli studenti" tavolo.
Verrà registrato nella tabella lo studente appena inserito”Registro degli studenti" con un timestamp automatico per la data e l'ora correnti in cui è avvenuta l'istruzione insert. Come segue:
Passo 1) Passare alla directory “C:\sqlite" ed esegui sqlite3.exe.
Passo 2) Apri la banca dati”TutorialSampleDB.db" eseguendo il seguente comando:
.open TutorialsSampleDB.db
Passo 3) creare il trigger”InserisciIntoStudentTrigger"Eseguendo il seguente comando:
CREATE TRIGGER InsertIntoStudentTrigger
BEFORE INSERT ON Students
BEGIN
INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;
La funzione "appuntamento()" ti fornirà l'indicazione della data e dell'ora corrente in cui è avvenuta l'istruzione di inserimento. In modo da poter registrare la transazione di inserimento con timestamp automatici aggiunti a ciascuna transazione.
Il comando dovrebbe essere eseguito correttamente e non viene visualizzato alcun output:
Il grilletto”InserisciIntoStudentTrigger" si attiverà ogni volta che inserisci un nuovo studente nella tabella degli studenti. IL "new" La parola chiave si riferisce ai valori che verranno inseriti. Ad esempio, il “nuovo.StudentId” sarà la matricola studente che verrà inserita.
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 da “Registro degli studenti" tavolo:
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 “ InserisciIntoStudentTrigger ” abbiamo creato, per registrare eventuali transazioni inserite nella tabella “Registro degli studenti" automaticamente. Allo stesso modo puoi registrare qualsiasi aggiornamento o eliminare le dichiarazioni.
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) Passare alla directory “C:\sqlite" ed esegui sqlite3.exe.
Passo 2) Apri la banca dati”TutorialSampleDB.db" eseguendo il seguente comando:
.open TutorialsSampleDB.db
Passo 3) Crea un nuovo trigger “preventUpdateStudentName" sul tavolo "Gli studenti" 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 "AUMENTARE"il comando genererà un errore con un messaggio di errore" Non puoi aggiornare il nome dello studente ", e quindi impedirà l'esecuzione dell'istruzione di aggiornamento.
Ora verificheremo che il trigger funzioni correttamente e impedisca qualsiasi aggiornamento per la colonna nomestudente.
Passo 4) Esegui il seguente comando di aggiornamento, che aggiornerà il nome dello studente "Giacca" essere "Jack1".
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
Dovresti ricevere il messaggio di errore che abbiamo specificato sul trigger, dicendo che "Non puoi aggiornare il nome dello studente" 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:
Sintesi
Visualizzazioni, indici e trigger sono strumenti molto potenti per amministrare un file SQLite banca dati. Puoi tracÈ possibile monitorare le operazioni di modifica dei dati quando avvengono su una tabella. È inoltre possibile ottimizzare le operazioni di recupero dei dati dal database creando indici.
