SQLite Declanșare, vizualizări și indexare cu exemplu

prin: Marcus Allen Marcus Allen
Hours Actualizat

În utilizarea zilnică a SQLite, veți avea nevoie de câteva instrumente administrative pentru baza de date. De asemenea, le puteți folosi pentru a face interogarea bazei de date mai eficientă prin crearea de indexuri sau mai reutilizabilă prin crearea de vizualizări.

SQLite Vizualizare

Vizualizările sunt foarte asemănătoare cu tabelele. Dar Vizualizările sunt tabele logice; nu sunt stocate fizic ca niște mese. O vizualizare este compusă dintr-o declarație select.

Puteți defini o vizualizare pentru interogările dvs. complexe și puteți reutiliza aceste interogări oricând doriți, apelând direct vizualizarea în loc să rescrieți interogările din nou.

Instrucțiunea CREATE VIEW

Pentru a crea o vizualizare într-o bază de date, puteți utiliza instrucțiunea CREATE VIEW urmată de numele vizualizării și apoi puneți interogarea dorită după aceea.

Exemplu: În exemplul următor vom crea o vizualizare cu numele „AllStudentsView” în baza de date exemplu ”TutorialeSampleDB.db” după cum urmează:

Pas 1) Deschideți My Computer și navigați la următorul director „C:\sqlite” și apoi deschideți ”sqlite3.exe„:

SQLite Vizualizare

Pas 2) Deschide baza de date „TutorialeSampleDB.db” prin următoarea comandă:

SQLite Vizualizare

Pas 3) Urmează o sintaxă de bază a comenzii sqlite3 pentru a crea vizualizarea

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;

Nu ar trebui să existe nicio ieșire de la comandă ca aceasta:

SQLite Vizualizare

Pas 4) Pentru a vă asigura că vizualizarea este creată, puteți selecta lista de vizualizări din baza de date rulând următoarea comandă:

SELECT name FROM sqlite_master WHERE type = 'view';

Ar trebui să vezi priveliștea „AllStudentsView” este returnat:

SQLite Vizualizare

Pas 5) Acum vizualizarea noastră este creată, o puteți folosi ca un tabel normal, ceva de genul acesta:

SELECT * FROM AllStudentsView;

Această comandă va interoga vizualizarea „ToțiStudenții” și va selecta toate rândurile din aceasta, așa cum se arată în următoarea captură de ecran:

SQLite Vizualizare

Vizualizări temporare

Vizualizările temporare sunt temporare pentru conexiunea curentă la baza de date folosită pentru a o crea. Apoi, dacă închideți conexiunea la baza de date, toate vizualizările temporare vor fi șterse automat. Vizualizările temporare sunt create folosind una dintre următoarele comenzi:

  • CREATE TEMP VIEW sau
  • CREAȚI O VIZIUNE TEMPORARĂ.

Vizualizările temporare sunt utile dacă doriți să faceți unele operații deocamdată și nu aveți nevoie să fie o vizualizare permanentă. Deci, trebuie doar să creați o vizualizare temporară, apoi să faceți procesarea folosind acea vizualizare. Later când închideți conexiunea cu baza de date, aceasta va fi ștearsă automat.

Exemplu:

În exemplul următor, vom deschide o conexiune la baza de date, apoi vom crea o vizualizare temporară.

După aceea, vom închide acea conexiune și vom verifica dacă vizualizarea temporară mai există sau nu.

Pas 1) Deschideți sqlite3.exe din directorul „C:\sqlite” după cum s-a explicat mai înainte.

Pas 2) Deschide o conexiune la baza de date „TutorialeSampleDB.db” prin rularea următoarei comenzi:

.open TutorialsSampleDB.db

Pas 3) Scrieți următoarea comandă care va crea o vizualizare temporară „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;

SQLite Vizualizare

Pas 4) Asigurați-vă că vizualizarea temp „AllStudentsTempView” este creat prin rularea următoarei comenzi:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite Vizualizare

Pas 5) Închideți sqlite3.exe și deschideți-l din nou.

Pas 6) Deschide o conexiune la baza de date „TutorialeSampleDB.db” prin următoarea comandă:

.open TutorialsSampleDB.db

Pas 7) Rulați următoarea comandă pentru a obține lista de vizualizări temporare create în baza de date:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Nu ar trebui să vedeți nicio ieșire, deoarece vizualizarea temporară pe care am creat-o este ștearsă când am închis conexiunea la baza de date în pasul anterior. În caz contrar, atâta timp cât păstrați conexiunea cu baza de date deschisă, veți putea vedea vizualizarea temporară cu date.

SQLite Vizualizare

note:

  • Nu puteți utiliza instrucțiunile INSERT, DELETE sau UPDATE cu vizualizări, doar puteți utiliza comanda „select from views”, așa cum se arată în pasul 5 din exemplul CREATE View.
  • Pentru a șterge o vizualizare, puteți utiliza instrucțiunea „DROP VIEW”:
DROP VIEW AllStudentsView;

Pentru a vă asigura că vizualizarea este ștearsă, puteți rula următoarea comandă care vă oferă lista de vizualizări din baza de date:

SELECT name FROM sqlite_master WHERE type = 'view';

Nu veți găsi nicio vizualizare returnată deoarece vizualizarea a fost ștearsă, după cum urmează:

SQLite Vizualizare

ARTICOLE SIMILARE

SQLite index

Dacă aveți o carte și doriți să căutați un cuvânt cheie în cartea respectivă. Veți căuta acel cuvânt cheie în indexul cărții. Apoi veți naviga la numărul paginii pentru acel cuvânt cheie pentru a citi mai multe informații despre acel cuvânt cheie.

Cu toate acestea, dacă nu există nici un index pe acea carte sau numere de pagină, veți scana întreaga carte de la început până la sfârșit până când veți găsi cuvântul cheie pe care îl căutați. Și acest lucru este foarte dificil mai ales atunci când aveți un index și un proces foarte lent pentru a căuta un cuvânt cheie.

Indici în SQLite (și același concept valabil și pentru alții sisteme de management al bazelor de date de asemenea) funcționează în același mod ca indicatorii aflați în spatele cărților.

Când căutați câteva rânduri într-un SQLite tabel cu criterii de căutare, SQLite va căuta pe toate rândurile tabelului până când va găsi rândurile pe care le căutați care corespund criteriilor de căutare. Și acest proces devine foarte lent atunci când aveți mese mai mari.

Indecșii vor accelera interogările de căutare pentru date și vor ajuta la efectuarea recuperării datelor din tabele. Indecii sunt definiți pe coloanele tabelului.

Îmbunătățirea performanței cu indici:

Indecșii pot îmbunătăți performanța căutării datelor pe un tabel. Când creați un index pe o coloană, SQLite va crea o structură de date pentru acel index în care fiecare valoare de câmp are un pointer către întregul rând din care aparține valoarea.

Apoi, dacă executați o interogare cu o condiție de căutare pe o coloană care face parte dintr-un index, SQLite va căuta mai întâi valoarea de pe index. SQLite nu va scana întregul tabel pentru asta. Apoi va citi locația în care indică valoarea pentru rândul tabelului. SQLite va localiza rândul în acea locație și îl va prelua.

Cu toate acestea, dacă coloana pe care o căutați nu face parte dintr-un index, SQLite va efectua o scanare pentru valorile coloanei pentru a găsi datele pe care le căutați. Va fi de obicei un proces mai lent dacă nu există index.

Imaginați-vă o carte fără index și trebuie să căutați un anumit cuvânt. Veți scana întreaga carte de la prima pagină la ultima pagină căutând acel cuvânt. Cu toate acestea, dacă aveți un index pe acea carte, veți căuta mai întâi cuvântul de pe ea. Obțineți numărul paginii unde se află, apoi navigați la el. Ceea ce va fi mult mai rapid decât scanarea întregii cărți de la copertă la copertă.

SQLite CREAȚI INDICE

Pentru a crea un index pe o coloană, ar trebui să utilizați comanda CREATE INDEX. Și ar trebui să o definiți după cum urmează:

  • Trebuie să specificați numele indexului după comanda CREATE INDEX.
  • După numele indexului, trebuie să puneți cuvântul cheie „ON”, urmat de numele tabelului în care va fi creat indexul.
  • Apoi lista de nume de coloane care sunt folosite pentru index.
  • Puteți utiliza unul dintre următoarele cuvinte cheie „ASC” sau „DESC” după orice nume de coloană pentru a specifica o ordine de sortare utilizată pentru a ordona datele indexului.

Exemplu:

În exemplul următor, vom crea un index „StudentNameIndex” pe masa studenților în „Elevi” baza de date după cum urmează:

Pas 1) Navigați la folderul „C:\sqlite” după cum s-a explicat mai înainte.

Pas 2) Deschideți sqlite3.exe.

Pas 3) Deschide baza de date „TutorialeSampleDB.db” prin următoarea comandă:

.open TutorialsSampleDB.db

Pas 4) Creați un nou index „StudentNameIndex” folosind următoarea comandă:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Ar trebui să nu vedeți nicio ieșire pentru aceasta:

SQLite index

Pas 5) Pentru a vă asigura că indexul a fost creat, puteți rula următoarea interogare, care vă oferă lista de indici creați în tabelul Studenți:

PRAGMA index_list(Students);

Ar trebui să vedeți returnat indexul pe care tocmai l-am creat:

SQLite index

note:

  • Indecșii pot fi creați nu numai pe baza coloanelor, ci și a expresiilor. Ceva de genul:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

„OrderTotalIndex” se va baza pe coloana OrderId și, de asemenea, pe multiplicarea valorii coloanei Cantitate și a valorii coloanei Price. Deci, orice interogare pentru „IdComandă” și „Cantitate*Preț” va fi eficientă, deoarece interogarea va folosi indexul.

  • Dacă ați specificat o clauză WHERE în instrucțiunea CREATE INDEX, indexul va fi un index parțial. În acest caz, vor exista intrări în index doar pentru rândurile care corespund condițiilor din clauza WHERE. De exemplu, în următorul index: 
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;

    (În exemplul de mai sus, indexul va fi un index parțial, deoarece există o clauză WHERE specificată. În acest caz, indicele va fi aplicat numai acelor comenzi care au o valoare a cantității mai mare de 10000. Rețineți că, acest indice este numit parțial index din cauza clauzei WHERE, nu a expresiei utilizate pe ea. Cu toate acestea, puteți utiliza expresiile cu indici normali.)

  • Puteți utiliza instrucțiunea CREATE UNIQUE INDEX în loc de CREATE INDEX pentru a preveni intrările duplicate pentru coloane și astfel toate valorile pentru coloana indexată vor fi unice.
  • Pentru a șterge un index, utilizați comanda DROP INDEX urmată de numele indexului de șters.

SQLite trăgaci

Introducere în SQLite trăgaci

Declanșatoarele sunt operații automate predefinite executate atunci când are loc o anumită acțiune pe un tabel al bazei de date. Un declanșator poate fi definit pentru a fi declanșat ori de câte ori are loc una dintre următoarele acțiuni pe o masă:

  • INSERT într-un tabel.
  • DELETE rânduri dintr-un tabel.
  • ACTUALIZAȚI una dintre coloanele tabelului.

SQLite suportă PENTRU FIECARE RÂND declanșator astfel încât, operațiunile predefinite în declanșator să fie executate pentru toate rândurile implicate în acțiunile apărute pe tabel (fie că este inserare, ștergere sau actualizare).

SQLite CREAȚI DECLICATORUL

Pentru a crea un nou TRIGGER, puteți utiliza instrucțiunea CREATE TRIGGER după cum urmează:

  • După CREATE TRIGGER, ar trebui să specificați un nume de declanșator.
  • După numele declanșatorului, trebuie să specificați când trebuie executat exact numele declanșatorului. Ai trei variante:
  • BEFORE – declanșatorul va fi executat înainte de instrucțiunea INSERT, UPDATE sau delete specificată.
  • După – declanșatorul va fi executat după instrucțiunea INSERT, UPDATE sau delete specificată.
  • ÎN LOC DE – Va înlocui acțiunea care a declanșat declanșatorul cu instrucțiunea specificată în TRIGGER. DEclanșatorul INSTEAD OF nu este aplicabil cu tabele, doar cu vizualizări.
  • Apoi, trebuie să specificați tipul de acțiune, declanșatorul se va declanșa când se va întâmpla. Fie DELETE, INSERT sau UPDATE.
  • Puteți alege un nume de coloană opțional, astfel încât declanșatorul să nu se declanșeze decât dacă acțiunea a avut loc pe acea coloană.
  • Apoi trebuie să specificați numele tabelului în care va fi creat declanșatorul.
  • În interiorul corpului declanșatorului, ar trebui să specificați instrucțiunea care ar trebui să fie executată pentru fiecare rând atunci când declanșatorul este declanșat.

    • Declanșatoarele vor fi activate (declanșate) numai în funcție de tipul instrucțiunii specificate în comanda create trigger. De exemplu:

    • Declanșatorul BEFORE INSERT va fi activat (declanșat) înaintea oricărei instrucțiuni de inserare.
    • Declanșatorul AFTER UPDATE va fi activat (declanșat) după orice instrucțiune de actualizare, … și așa mai departe.

    În interiorul declanșatorului, puteți face referire la valorile nou introduse folosind cuvântul cheie „nou”. De asemenea, vă puteți referi la valorile șterse sau actualizate folosind vechiul cuvânt cheie. După cum urmează:

    • În interiorul declanșatorilor INSERT – poate fi folosit un cuvânt cheie nou.
    • În declanșatoarele UPDATE – pot fi folosite cuvinte cheie noi și vechi.
    • În declanșatoarele DELETE – se poate folosi cuvântul cheie vechi.

    Exemplu

    În cele ce urmează, vom crea un declanșator care se va declanșa înainte de a introduce un nou student în „Elevi" masa.

    Acesta va înregistra studentul nou introdus în tabel „Students Log” cu un marcaj de timp automat pentru data curentă la care a avut loc declarația de inserare. După cum urmează:

    Pas 1) Navigați la directorul „C:\sqlite” și rulați sqlite3.exe.

    Pas 2) Deschide baza de date „TutorialeSampleDB.db” prin rularea următoarei comenzi:

    .open TutorialsSampleDB.db

    Pas 3) creează declanșatorul „InsertIntoStudentTrigger” Prin rularea următoarei comenzi:

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

    Funcția „datetime()” vă va oferi ștampila data curentă când a avut loc declarația de inserare. Astfel încât să putem înregistra tranzacția de inserare cu marcaje de timp automate adăugate la fiecare tranzacție.

    Comanda ar trebui să ruleze cu succes și nu obțineți nicio ieșire:

    SQLite trăgaci

    Trăgaciul "InsertIntoStudentTrigger” se va declanșa de fiecare dată când introduceți un nou student în tabelul studenților. „nou” cuvântul cheie se referă la valorile care vor fi inserate. De exemplu, „nou.StudentId” va fi id-ul de student care va fi inserat.

    Acum, vom testa cum se comportă declanșatorul atunci când introducem un nou student.

    Pas 4) Scrieți următoarea comandă care va insera un nou student în tabelul studenților:

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

    Pas 5) Scrieți următoarea comandă care va selecta toate rândurile din „Students Log" masa:

    SELECT * FROM StudentsLog;

    Ar trebui să vedeți un rând nou returnat pentru noul student pe care tocmai l-am inserat:

    SQLite trăgaci

    Acest rând a fost inserat de declanșator înainte de a introduce noul student cu id 11.

    În acest exemplu am folosit declanșatorul „ InsertIntoStudentTrigger ” am creat, pentru a înregistra orice tranzacții de inserare în tabel ”Students Log" automat. În același mod, puteți înregistra orice actualizare sau șterge declarații.

    Prevenirea actualizărilor neintenționate cu declanșatoare:

    Folosind declanșatoarele BEFORE UPDATE pe un tabel, puteți împiedica instrucțiunile de actualizare pe o coloană bazate pe o expresie.

    Exemplu

    În următorul exemplu, vom împiedica orice instrucțiune de actualizare să actualizeze coloana „nume student” din tabelul Studenți:

    Pas 1) Navigați la directorul „C:\sqlite” și rulați sqlite3.exe.

    Pas 2) Deschide baza de date „TutorialeSampleDB.db” prin rularea următoarei comenzi:

    .open TutorialsSampleDB.db

    Pas 3) Creați un nou declanșator „preventUpdateStudentName" pe masă "Elevi” prin rularea următoarei comenzi

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

    "A RIDICA„comanda va genera o eroare cu un mesaj de eroare „ Nu puteți actualiza numele studentului „, iar apoi va împiedica executarea instrucțiunii de actualizare.

    Acum, vom verifica dacă declanșatorul funcționează bine și împiedică orice actualizare pentru coloana studentname.

    Pas 4) Rulați următoarea comandă de actualizare, care va actualiza numele studentului „Sacou / jachetă" a fi "Jack1".

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

    Ar trebui să primiți mesajul de eroare pe care l-am specificat pe declanșator, spunând că „Nu puteți actualiza numele studentului” după cum urmează:

    SQLite trăgaci

    Pas 5) Rulați următoarea comandă, care va selecta lista cu numele elevilor din tabelul studenților.

    SELECT StudentName FROM Students;

    Ar trebui să vedeți că numele elevului „Jack” rămâne același și nu se schimbă:

    SQLite trăgaci

    Rezumat

    Vizualizările, indexurile și declanșatoarele sunt instrumente foarte puternice pentru administrarea unui SQLite Baza de date. Puteți urmări operațiunile de modificare a datelor atunci când acestea au loc pe un tabel. De asemenea, puteți optimiza operația de recuperare a datelor bazei de date prin crearea de indici.