SQLite Okidač, prikazi i indeks s primjerom

By: Marcus Allen Marcus Allen
Hours Ažurirano

U svakodnevnoj upotrebi SQLite, trebat će vam neki administrativni alati za vašu bazu podataka. Možete ih koristiti i za učinkovitije postavljanje upita u bazu podataka stvaranjem indeksa ili višekratnu upotrebu stvaranjem pogleda.

SQLite Pogled

Prikazi su vrlo slični tablicama. Ali Views su logične tablice; ne pohranjuju se fizički kao tablice. Pogled se sastoji od naredbe select.

Možete definirati pogled za svoje složene upite i možete ponovno koristiti te upite kad god želite izravnim pozivanjem pogleda umjesto ponovnog pisanja upita.

Izjava CREATE VIEW

Da biste stvorili pogled na bazu podataka, možete upotrijebiti naredbu CREATE VIEW iza koje slijedi naziv pogleda, a zatim nakon toga postaviti upit koji želite.

Primjer: U sljedećem primjeru stvorit ćemo pogled s nazivom "AllStudentsView” u uzorku baze podataka “TutorialsSampleDB.db” kako slijedi:

Korak 1) Otvorite Moje računalo i dođite do sljedećeg direktorija "C:\sqlite", a zatim otvorite "sqlite3.exe"

SQLite Pogled

Korak 2) Otvori bazu podataka “TutorialsSampleDB.db” sljedećom naredbom:

SQLite Pogled

Korak 3) Slijedi osnovna sintaksa naredbe sqlite3 za stvaranje prikaza

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;

Ovakva naredba ne bi trebala imati izlaz:

SQLite Pogled

Korak 4) Kako biste bili sigurni da je prikaz stvoren, možete odabrati popis pogleda u bazi podataka pokretanjem sljedeće naredbe:

SELECT name FROM sqlite_master WHERE type = 'view';

Trebali biste vidjeti pogled "AllStudentsView” se vraća:

SQLite Pogled

Korak 5) Sada je naš prikaz stvoren, možete ga koristiti kao normalnu tablicu otprilike ovako:

SELECT * FROM AllStudentsView;

Ova naredba će postaviti upit za prikaz "Svi studenti" i odabrati sve retke iz njega kao što je prikazano na sljedećoj snimci zaslona:

SQLite Pogled

Privremeni prikazi

Privremeni prikazi su privremeni za trenutnu vezu baze podataka koja se koristi za njihovo stvaranje. Zatim, ako zatvorite vezu s bazom podataka, svi će se privremeni prikazi automatski izbrisati. Privremeni prikazi stvaraju se pomoću jedne od sljedećih naredbi:

  • CREATE TEMP VIEW, ili
  • STVORI PRIVREMENI PRIKAZ.

Privremeni prikazi korisni su ako želite obavljati neke operacije za to vrijeme i ne trebate da bude stalni prikaz. Dakle, samo stvorite privremeni prikaz, a zatim izvršite svoju obradu pomoću tog prikaza. Later kada zatvorite vezu s bazom podataka, automatski će se izbrisati.

Primjer:

U sljedećem primjeru otvorit ćemo vezu s bazom podataka, a zatim stvoriti privremeni prikaz.

Nakon toga ćemo zatvoriti tu vezu, te ćemo provjeriti postoji li privremeni prikaz i dalje ili ne.

Korak 1) Otvorite sqlite3.exe iz direktorija "C:\sqlite” kao što je prethodno objašnjeno.

Korak 2) Otvorite vezu s bazom podataka “TutorialsSampleDB.db” pokretanjem sljedeće naredbe:

.open TutorialsSampleDB.db

Korak 3) Napišite sljedeću naredbu koja će stvoriti privremeni pogled "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 Pogled

Korak 4) Provjerite je li temp prikaz "AllStudentsTempView” se stvara izvođenjem sljedeće naredbe:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite Pogled

Korak 5) Zatvorite sqlite3.exe i ponovno ga otvorite.

Korak 6) Otvorite vezu s bazom podataka “TutorialsSampleDB.db” sljedećom naredbom:

.open TutorialsSampleDB.db

Korak 7) Izvedite sljedeću naredbu da biste dobili popis privremenih pogleda kreiranih u bazi podataka:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Ne biste trebali vidjeti nikakav izlaz jer je privremeni prikaz koji smo stvorili izbrisan kada smo zatvorili vezu s bazom podataka u prethodnom koraku. U suprotnom, sve dok vezu s bazom podataka držite otvorenom, mogli biste vidjeti privremeni prikaz s podacima.

SQLite Pogled

Napomene:

  • Ne možete koristiti naredbe INSERT, DELETE ili UPDATE s pogledima, samo možete koristiti naredbu “odaberite iz pogleda” kao što je prikazano u koraku 5 u primjeru CREATE View.
  • Za brisanje POGLEDA možete upotrijebiti naredbu "ISPUSTI POGLED":
DROP VIEW AllStudentsView;

Kako biste bili sigurni da je prikaz izbrisan, možete pokrenuti sljedeću naredbu koja vam daje popis pogleda u bazi podataka:

SELECT name FROM sqlite_master WHERE type = 'view';

Nećete pronaći nijedan vraćeni prikaz jer je prikaz izbrisan, kao što slijedi:

SQLite Pogled

POVEZANI ČLANCI

SQLite indeks

Ako imate knjigu i želite pretraživati ​​ključnu riječ za tu knjigu. Potražit ćete tu ključnu riječ u indeksu knjige. Zatim ćete otići do broja stranice za tu ključnu riječ kako biste pročitali više informacija o toj ključnoj riječi.

Međutim, ako nema indeksa te knjige niti brojeva stranica, skenirat ćete cijelu knjigu od početka do kraja dok ne pronađete ključnu riječ koju tražite. A to je vrlo teško, pogotovo kada imate indeks i vrlo spor proces traženja ključne riječi.

Indeksi u SQLite (i isti koncept vrijedi i za druge sustavi za upravljanje bazama podataka također) radi na isti način kao i indeksi koji se nalaze na poleđini knjiga.

Kada tražite neke retke u SQLite tablica s kriterijima pretraživanja, SQLite pretraživat će sve retke tablice dok ne pronađe retke koje tražite koji odgovaraju kriterijima pretraživanja. A taj proces postaje vrlo spor kada imate veće tablice.

Indeksi će ubrzati upite za pretraživanje podataka i pomoći će u dohvaćanju podataka iz tablica. Indeksi su definirani na stupcima tablice.

Poboljšanje izvedbe s indeksima:

Indeksi mogu poboljšati izvedbu pretraživanja podataka u tablici. Kada kreirate indeks na stupcu, SQLite će stvoriti strukturu podataka za taj indeks gdje svaka vrijednost polja ima pokazivač na cijeli redak kojem vrijednost pripada.

Zatim, ako pokrenete upit s uvjetom pretraživanja na stupcu koji je dio indeksa, SQLite prvo će potražiti vrijednost na indeksu. SQLite neće skenirati cijelu tablicu za to. Zatim će očitati mjesto na koje pokazuje vrijednost za red tablice. SQLite će locirati red na toj lokaciji i dohvatiti ga.

Međutim, ako stupac koji tražite nije dio indeksa, SQLite izvršit će skeniranje vrijednosti stupaca kako bi pronašli podatke koje tražite. Obično će proces biti sporiji ako nema indeksa.

Zamislite knjigu bez indeksa i morate tražiti određenu riječ. Skenirati ćete cijelu knjigu od prve do zadnje stranice tražeći tu riječ. Međutim, ako imate indeks te knjige, prvo ćete potražiti riječ na njoj. Dobijte broj stranice na kojoj se nalazi, a zatim idite do nje. Što će biti mnogo brže od skeniranja cijele knjige od korica do korica.

SQLite IZRADI INDEKS

Da biste kreirali indeks na stupcu, trebate koristiti naredbu CREATE INDEX. I trebali biste to definirati na sljedeći način:

  • Morate navesti naziv indeksa nakon naredbe CREATE INDEX.
  • Nakon imena indeksa potrebno je staviti ključnu riječ “ON”, a zatim i naziv tablice u kojoj će se indeks kreirati.
  • Zatim popis naziva stupaca koji se koriste za indeks.
  • Možete upotrijebiti jednu od sljedećih ključnih riječi "ASC" ili "DESC" nakon naziva bilo kojeg stupca da odredite redoslijed sortiranja koji se koristi za poredak podataka indeksa.

Primjer:

U sljedećem primjeru izradit ćemo indeks “StudentNameIndex" na stolu učenika u "Studenti” baze podataka kako slijedi:

Korak 1) Dođite do mape "C:\sqlite” kao što je prethodno objašnjeno.

Korak 2) Otvorite sqlite3.exe.

Korak 3) Otvori bazu podataka “TutorialsSampleDB.db” sljedećom naredbom:

.open TutorialsSampleDB.db

Korak 4) Napravi novi indeks "Indeks imena učenika” pomoću sljedeće naredbe:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Ne biste trebali vidjeti izlaz za ovo:

SQLite indeks

Korak 5) Kako biste bili sigurni da je indeks kreiran, možete pokrenuti sljedeći upit koji vam daje popis indeksa kreiranih u tablici Studenti:

PRAGMA index_list(Students);

Trebali biste vidjeti vraćeni indeks koji smo upravo stvorili:

SQLite indeks

Napomene:

  • Indeksi se mogu kreirati ne samo na temelju stupaca, već i izraza. Nešto kao ovo:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

“OrderTotalIndex” temeljit će se na stupcu OrderId i također na množenju vrijednosti stupca Količina i vrijednosti stupca Cijena. Dakle, svaki upit za "OrderId" i "Quantity*Price" bit će učinkovit jer će upit koristiti indeks.

  • Ako ste specificirali WHERE klauzulu u CREATE INDEX naredbi, indeks će biti djelomični indeks. U ovom slučaju, bit će unosa u indeks samo za retke koji odgovaraju uvjetima u WHERE klauzuli. Na primjer, u sljedećem indeksu: 
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;

    ( U gornjem primjeru, indeks će biti djelomični indeks budući da je navedena klauzula WHERE. U ovom slučaju, indeks će se primijeniti samo na one narudžbe koje imaju vrijednost količine veću od 10000. Imajte na umu da se ovaj indeks naziva djelomični indeks zbog klauzule WHERE, a ne izraza koji se na njemu koristi. Međutim, možete koristiti izraze s normalnim indeksima.)

  • Možete koristiti naredbu CREATE UNIQUE INDEX umjesto CREATE INDEX kako biste spriječili duple unose za stupce i tako će sve vrijednosti za indeksirani stupac biti jedinstvene.
  • Za brisanje indeksa koristite naredbu DROP INDEX nakon koje slijedi naziv indeksa za brisanje.

SQLite Okidač

Uvod u SQLite Okidač

Okidači su automatske unaprijed definirane operacije koje se izvršavaju kada se dogodi određena radnja na tablici baze podataka. Okidač se može definirati tako da se aktivira kad god se jedna od sljedećih radnji dogodi na tablici:

  • INSERT u tablicu.
  • IZBRIŠI retke iz tablice.
  • AŽURIRAJTE jedan od stupaca tablice.

SQLite podržava FOR EACH ROW okidač tako da će se unaprijed definirane operacije u okidaču izvršiti za sve retke uključene u radnje koje su se dogodile u tablici (bilo da se radi o umetanju, brisanju ili ažuriranju).

SQLite STVARANJE OKIDAČA

Za stvaranje novog TRIGGER-a možete koristiti naredbu CREATE TRIGGER na sljedeći način:

  • Nakon CREATE TRIGGER, trebali biste navesti naziv okidača.
  • Nakon naziva okidača, morate navesti kada se točno naziv okidača treba izvršiti. Imate tri opcije:
  • PRIJE – okidač će se izvršiti prije navedene naredbe INSERT, UPDATE ili delete.
  • Nakon – okidač će se izvršiti nakon navedene naredbe INSERT, UPDATE ili delete.
  • UMJESTO – Zamijenit će radnju koja se dogodila koja je pokrenula okidač naredbom navedenom u TRIGGER-u. Okidač UMJESTO nije primjenjiv na tablice, samo na poglede.
  • Zatim morate odrediti vrstu radnje, okidač će se aktivirati kada se dogodi. Ili DELETE, INSERT ili UPDATE.
  • Možete odabrati izborni naziv stupca tako da se okidač ne aktivira osim ako se radnja ne dogodi u tom stupcu.
  • Zatim morate navesti naziv tablice u kojoj će se stvoriti okidač.
  • Unutar tijela okidača trebali biste navesti naredbu koja bi se trebala izvršiti za svaki red kada se okidač aktivira.

    • Okidači će se aktivirati (pokrenuti) samo ovisno o vrsti iskaza navedenog u naredbi za stvaranje okidača. Na primjer:

    • Okidač BEFORE INSERT bit će aktiviran (pokrenut) prije bilo koje naredbe za umetanje.
    • Okidač NAKON AŽURIRANJA će se aktivirati (pokrenuti) nakon bilo koje izjave o ažuriranju, … i tako dalje.

    Unutar okidača možete se pozvati na novo umetnute vrijednosti pomoću ključne riječi "novo". Također, možete se pozvati na izbrisane ili ažurirane vrijednosti pomoću stare ključne riječi. Kao što slijedi:

    • Unutar INSERT okidača – može se koristiti nova ključna riječ.
    • Unutar okidača UPDATE – mogu se koristiti nove i stare ključne riječi.
    • Unutar okidača DELETE – može se koristiti stara ključna riječ.

    Primjer

    U nastavku ćemo stvoriti okidač koji će se aktivirati prije umetanja novog učenika u "Studenti”Tablica.

    Prijavit će novoumetnutog učenika u tablicu "Dnevnik učenika” s automatskim vremenskim žigom za trenutni datum kada se dogodila izjava umetanja. Kao što slijedi:

    Korak 1) Dođite do imenika "C:\sqlite” i pokrenite sqlite3.exe.

    Korak 2) Otvori bazu podataka “TutorialsSampleDB.db” pokretanjem sljedeće naredbe:

    .open TutorialsSampleDB.db

    Korak 3) stvoriti okidač "InsertIntoStudentTrigger” Pokretanjem sljedeće naredbe:

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

    Funkcija "Datum vrijeme()" dat će vam trenutnu vremensku oznaku datuma kada se dogodila izjava umetanja. Tako da možemo zabilježiti transakciju umetanja s automatskim vremenskim oznakama dodanim svakoj transakciji.

    Naredba bi se trebala uspješno izvesti, a vi ne dobivate izlaz:

    SQLite Okidač

    Okidač “InsertIntoStudentTrigger” aktivirat će se svaki put kada unesete novog učenika u tablicu učenika. "novi” ključna riječ odnosi se na vrijednosti koje će biti umetnute. Na primjer, "novi.StudentId” bit će studentski ID koji će biti umetnut.

    Sada ćemo testirati kako se okidač ponaša kada umetnemo novog učenika.

    Korak 4) Napišite sljedeću naredbu koja će umetnuti novog učenika u tablicu učenika:

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

    Korak 5) Napišite sljedeću naredbu koja će odabrati sve retke iz "Dnevnik učenika” tablica:

    SELECT * FROM StudentsLog;

    Trebali biste vidjeti novi red vraćen za novog učenika kojeg smo upravo umetnuli:

    SQLite Okidač

    Ovaj je redak umetnuo okidač prije umetanja novog učenika s ID-om 11.

    U ovom smo primjeru koristili okidač " InsertIntoStudentTrigger " stvorili smo, kako bismo zabilježili sve transakcije umetanja u tablicu "Dnevnik učenika” automatski. Na isti način možete zabilježiti bilo koje ažuriranje ili izbrisati izjave.

    Sprječavanje nenamjernih ažuriranja pomoću okidača:

    Koristeći okidače BEFORE UPDATE na tablici, možete spriječiti izjave ažuriranja na stupcu na temelju izraza.

    Primjer

    U sljedećem primjeru spriječit ćemo bilo koju izjavu o ažuriranju da ažurira stupac "ime studenta" u tablici Studenti:

    Korak 1) Dođite do imenika "C:\sqlite” i pokrenite sqlite3.exe.

    Korak 2) Otvori bazu podataka “TutorialsSampleDB.db” pokretanjem sljedeće naredbe:

    .open TutorialsSampleDB.db

    Korak 3) Stvorite novi okidač "spriječiUpdateStudentName" na stolu "Studenti” pokretanjem sljedeće naredbe

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

    "PODIĆI” naredba će izazvati pogrešku s porukom o pogrešci “ Ne možete ažurirati ime učenika “, a zatim će spriječiti izvršavanje naredbe ažuriranja.

    Sada ćemo provjeriti radi li okidač dobro i sprječava li bilo kakvo ažuriranje za stupac imena učenika.

    Korak 4) Pokrenite sljedeću naredbu za ažuriranje, koja će ažurirati ime učenika "Utičnica" biti "Jack1".

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

    Trebali biste dobiti poruku o pogrešci koju smo naveli na okidaču, koja kaže da "Ne možete ažurirati ime učenika” kako slijedi:

    SQLite Okidač

    Korak 5) Pokrenite sljedeću naredbu koja će odabrati popis imena učenika iz tablice učenika.

    SELECT StudentName FROM Students;

    Trebali biste vidjeti da je ime učenika "Jack" još uvijek isto i da se ne mijenja:

    SQLite Okidač

    rezime

    Pogledi, indeksi i okidači vrlo su moćni alati za upravljanje SQLite baza podataka. Možete pratiti operacije izmjene podataka kada se dogode na tablici. Također možete optimizirati operaciju dohvaćanja podataka iz baze stvaranjem indeksa.

    Možda ti se svidi: