SQLite Näkymät, indeksi ja triggeri esimerkin kanssa

Päivittäisessä käytössä SQLite, tarvitset joitain hallintatyökaluja tietokantaasi. Voit myös käyttää niitä tehostamaan tietokannan kyselyitä luomalla indeksejä tai helpottamaan uudelleenkäytettävyyttä luomalla näkymiä.

SQLite Näytä

Näkymät ovat hyvin samankaltaisia ​​kuin taulukot. Mutta näkymät ovat loogisia taulukoita; niitä ei säilytetä fyysisesti kuten pöytiä. Näkymä koostuu valitusta lauseesta.

Voit määrittää näkymän monimutkaisille kyselyillesi ja voit käyttää näitä kyselyitä uudelleen milloin haluat kutsumalla näkymää suoraan sen sijaan, että kirjoittaisit kyselyt uudelleen.

CREATE VIEW -lausunto

Luodaksesi näkymän tietokantaan, voit käyttää CREATE VIEW -käskyä ja näkymän nimeä ja laittaa haluamasi kyselyn sen jälkeen.

Esimerkiksi: Seuraavassa esimerkissä luomme näkymän nimeltä ”AllStudentsView” esimerkkitietokantaan ”TutorialsSampleDB.db” seuraavasti:

Vaihe 1) Avaa Oma tietokone ja siirry seuraavaan hakemistoon “C:\sqlite” ja avaa sitten “sqlite3.exe”:

Vaihe 2) Avaa tietokanta “TutorialsSampleDB.db” seuraavalla komennolla:

Vaihe 3) Seuraavassa on sqlite3-komennon perussyntaksi näkymän luomiseksi

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;

Komennosta ei pitäisi tulla tällaista tulosta:

Vaihe 4) Varmistaaksesi, että näkymä luodaan, voit valita tietokannan näkymien luettelon suorittamalla seuraavan komennon:

SELECT name FROM sqlite_master WHERE type = 'view';

Sinun pitäisi nähdä näkymä ”AllStudentsView”, joka palautetaan:

Vaihe 5) Nyt näkymämme on luotu, voit käyttää sitä tavallisena taulukkona jotakuinkin näin:

SELECT * FROM AllStudentsView;

Tämä komento kysyy näkymästä "AllStudents" ja valitsee siitä kaikki rivit seuraavan kuvakaappauksen mukaisesti:

Väliaikaiset näkymät

Väliaikaiset näkymät ovat tilapäisiä sen luomiseen käytetylle nykyiselle tietokantayhteydelle. Jos suljet tietokantayhteyden, kaikki väliaikaiset näkymät poistetaan automaattisesti. Väliaikaiset näkymät luodaan jollakin seuraavista komennoista:

• LUO TEMP NÄKYMÄ, tai
• LUO VÄLIAIKAINEN NÄKYMÄ.

Väliaikaiset näkymät ovat hyödyllisiä, jos haluat tehdä joitain toimintoja toistaiseksi etkä tarvitse sitä pysyvänä näkymänä. Joten luot vain väliaikaisen näkymän ja suoritat sitten käsittelysi käyttämällä tätä näkymää. Later kun suljet yhteyden tietokantaan, se poistetaan automaattisesti.

Esimerkiksi:

Seuraavassa esimerkissä avaamme tietokantayhteyden ja luomme sitten väliaikaisen näkymän.

Sen jälkeen suljemme yhteyden ja tarkistamme, onko väliaikainen näkymä edelleen olemassa vai ei.

Vaihe 1) Avaa sqlite3.exe hakemistosta “C:\sqlite” aiemmin kuvatulla tavalla.

Vaihe 2) Avaa yhteys tietokantaan “TutorialsSampleDB.db” suorittamalla seuraava komento:

.open TutorialsSampleDB.db

Vaihe 3) Kirjoita seuraava komento, joka luo väliaikaisnäkymän ”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;

Vaihe 4) Varmista, että väliaikainen näkymä ”AllStudentsTempView” luodaan suorittamalla seuraava komento:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Vaihe 5) Sulje sqlite3.exe ja avaa se uudelleen.

Vaihe 6) Avaa yhteys tietokantaan “TutorialsSampleDB.db” seuraavalla komennolla:

.open TutorialsSampleDB.db

Vaihe 7) Suorita seuraava komento saadaksesi luettelon tietokantaan luoduista väliaikaisista näkymistä:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Sinun ei pitäisi nähdä tulostetta, koska luomamme väliaikaisnäkymä poistetaan, kun suljemme tietokantayhteyden edellisessä vaiheessa. Muuten, niin kauan kuin pidät yhteyden tietokantaan auki, voit nähdä väliaikaisen näkymän tiedoineen.

Huomautuksia:

• Et voi käyttää käskyjä INSERT, DELETE tai UPDATE näkymien kanssa, vain voit käyttää "select from view" -komentoa, kuten kohdassa 5 näytetyssä CREATE View -esimerkissä.
• Voit poistaa VIEW:n käyttämällä "DROP VIEW" -lausetta:

DROP VIEW AllStudentsView;

Varmistaaksesi, että näkymä poistetaan, voit suorittaa seuraavan komennon, joka antaa sinulle luettelon tietokannan näkymistä:

SELECT name FROM sqlite_master WHERE type = 'view';

Et löydä näkymiä, jotka palautettiin, kun näkymä poistettiin, seuraavasti:

Monimutkaisten kyselyiden uudelleenkäytön näkymien kanssa lisäksi SQLite hakee myös vastaavat rivit nopeammin indeksien kautta.

SQLite indeksi

Jos sinulla on kirja ja haluat etsiä sen avainsanan. Haet kyseistä avainsanaa kirjan hakemistosta. Sitten siirryt kyseisen avainsanan sivunumeroon lukeaksesi lisätietoja kyseisestä avainsanasta.

Jos kirjassa ei kuitenkaan ole hakemistoa tai sivunumeroita, skannaat koko kirjan alusta loppuun, kunnes löydät etsimäsi avainsanan. Ja tämä on erittäin vaikeaa varsinkin, kun sinulla on hakemisto ja erittäin hidas prosessi avainsanan etsimiseen.

Indeksit sisään SQLite (ja sama käsite pätee muihinkin tietokannan hallintajärjestelmät samoin) toimii samalla tavalla kuin kirjojen takapuolelta löytyvät hakemistot.

Kun haet joitain rivejä SQLite taulukko hakuehdoilla, SQLite etsii kaikilla taulukon riveillä, kunnes se löytää etsimäsi rivit, jotka vastaavat hakuehtoja. Ja siitä prosessista tulee erittäin hidas, kun sinulla on suurempia pöytiä.

Indeksit nopeuttavat tietojen hakua ja auttavat suorittamaan tietojen hakemista taulukoista. Indeksit määritellään taulukon sarakkeissa.

Suorituksen parantaminen indeksien avulla:

Hakemistot voivat parantaa taulukon tietojen haun suorituskykyä. Kun luot indeksin sarakkeeseen, SQLite luo tälle indeksille tietorakenteen, jossa jokaisella kentän arvolla on osoitin koko riville, johon arvo kuuluu.

Jos sitten suoritat kyselyn hakuehdolla sarakkeessa, joka on osa hakemistoa, SQLite etsii ensin arvon indeksistä. SQLite ei skannaa sitä koko taulukkoa. Sitten se lukee paikan, jossa arvo osoittaa taulukon riville. SQLite paikantaa rivin kyseisestä sijainnista ja hakee sen.

Jos etsimäsi sarake ei kuitenkaan ole osa hakemistoa, SQLite suorittaa sarakearvojen skannauksen löytääkseen etsimäsi tiedot. Prosessi on yleensä hitaampi, jos indeksiä ei ole.

Kuvittele kirja, jossa ei ole hakemistoa ja sinun täytyy etsiä tietty sana. Skannaat koko kirjan ensimmäisestä sivusta viimeiseen sivuun etsiessäsi kyseistä sanaa. Jos sinulla on kuitenkin hakemisto kyseisestä kirjasta, etsi ensin sana siitä. Hanki sivunumero, jossa se sijaitsee, ja siirry sitten siihen. Mikä on paljon nopeampaa kuin koko kirjan skannaaminen kannesta kanteen.

SQLite LUO HAKEMISTO

Luodaksesi hakemiston sarakkeeseen, sinun tulee käyttää komentoa CREATE INDEX. Ja sinun pitäisi määritellä se seuraavasti:

• Sinun on määritettävä indeksin nimi CREATE INDEX -komennon jälkeen.
• Hakemiston nimen jälkeen on laitettava avainsana "ON" ja sen jälkeen taulukon nimi, johon hakemisto luodaan.
• Sitten luettelo sarakkeiden nimistä, joita käytetään indeksissä.
• Voit käyttää jotakin seuraavista avainsanoista "ASC" tai "DESC" minkä tahansa sarakkeen nimen jälkeen määrittääksesi lajittelujärjestyksen, jota käytetään hakemistotietojen järjestämiseen.

Esimerkiksi:

Seuraavassa esimerkissä luomme ”StudentNameIndex”-indeksin ”Students”-tietokannan students-taulukkoon seuraavasti:

Vaihe 1) Siirry kansioon “C:\sqlite” aiemmin selitetyllä tavalla.

Vaihe 2) Avaa sqlite3.exe.

Vaihe 3) Avaa tietokanta “TutorialsSampleDB.db” seuraavalla komennolla:

.open TutorialsSampleDB.db

Vaihe 4) Luo uusi indeksi ”StudentNameIndex” seuraavalla komennolla:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Sinun ei pitäisi nähdä tulostetta tälle:

Vaihe 5) Varmistaaksesi, että hakemisto on luotu, voit suorittaa seuraavan kyselyn, joka antaa sinulle luettelon Opiskelijat-taulukkoon luoduista hakemistoista:

PRAGMA index_list(Students);

Sinun pitäisi nähdä juuri luomamme indeksin palautus:

Huomautuksia:

• Indeksejä voidaan luoda paitsi sarakkeiden myös lausekkeiden perusteella. Jotain tällaista:

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

"OrderTotalIndex" perustuu OrderId-sarakkeeseen ja myös Quantity-sarakkeen arvon ja Price-sarakkeen arvon kertomiseen. Joten mikä tahansa kysely "OrderId" ja "Quantity*Price" on tehokas, koska kysely käyttää indeksiä.

• Jos määritit WHERE-lauseen CREATE INDEX -käskyssä, indeksi on osittainen indeksi. Tässä tapauksessa hakemistossa on merkintöjä vain niille riveille, jotka vastaavat WHERE-lauseen ehtoja. Esimerkiksi seuraavassa hakemistossa:

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

  (Yllä olevassa esimerkissä indeksi on osittainen indeksi, koska WHERE-lauseke on määritetty. Tässä tapauksessa indeksiä sovelletaan vain niihin tilauksiin, joiden määräarvo on suurempi kuin 10000. Huomaa, että tätä indeksiä kutsutaan osittaiseksi indeksi WHERE-lauseen takia, ei siinä käytetyn lausekkeen takia. Voit kuitenkin käyttää lausekkeita normaaleiden indeksien kanssa.)

• Voit käyttää CREATE UNIQUE INDEX -käskyä CREATE INDEX -käskyn sijaan estääksesi sarakkeiden kaksoismerkinnät ja siten kaikki indeksoidun sarakkeen arvot ovat yksilöllisiä.
• Jos haluat poistaa hakemiston, käytä DROP INDEX -komentoa ja sen jälkeen poistettavaa indeksin nimeä.

Vaikka indeksit nopeuttavat lukemista, liipaisimet antavat SQLite reagoi automaattisesti aina, kun tiedot muuttuvat.

SQLite Laukaista

Johdatus SQLite Laukaista

Triggerit ovat automaattisia ennalta määritettyjä toimintoja, jotka suoritetaan, kun tietokantataulukossa tapahtuu tietty toiminto. Triggeri voidaan määrittää käynnistymään aina, kun jokin seuraavista toiminnoista tapahtuu taulukossa:

• LISÄÄ taulukkoon.
• POISTA rivit taulukosta.
• PÄIVITYS yksi taulukon sarakkeista.

SQLite tukee JOKAINEN RIVIN liipaisinta, jotta liipaisimen ennalta määritetyt toiminnot suoritetaan kaikille riveille, jotka liittyvät taulukon toimiin (olipa se sitten lisäys, poistaminen tai päivitys).

SQLite LUO TRIGGER

Voit luoda uuden TRIGGERin käyttämällä CREATE TRIGGER -käskyä seuraavasti:

• CREATE TRIGGER -toiminnon jälkeen sinun tulee määrittää triggerin nimi.
• Triggerin nimen jälkeen on määritettävä, milloin tarkalleen liipaisimen nimi tulee suorittaa. Sinulla on kolme vaihtoehtoa:
  • BEFORE – laukaisu suoritetaan ennen määritettyä INSERT-, UPDATE- tai delete-käskyä.
  • After – laukaisu suoritetaan määritetyn INSERT-, UPDATE- tai delete-käskyn jälkeen.
  • ASIAKKAAN – Se korvaa liipaisimen laukaiseneen toiminnon TRIGGERissä määritetyllä käskyllä. INSTEAD OF liipaisinta ei voi käyttää taulukoissa, vain näkymissä.
• Sitten sinun on määritettävä toiminnon tyyppi, laukaisin käynnistyy, kun se tapahtuu. Joko DELETE, INSERT tai UPDATE.
• Voit valita valinnaisen sarakkeen nimen, jotta triggeri ei käynnisty, ellei toiminto tapahdu kyseisessä sarakkeessa.
• Sitten sinun on määritettävä taulukon nimi, johon liipaisin luodaan.
• Triggerin rungossa sinun tulee määrittää käsky, joka suoritetaan kullekin riville, kun triggeri käynnistetään.

Liipaisimet aktivoidaan (käynnistetään) vain sen mukaan, minkä tyyppinen komento Create Trigger -komennossa määritetään. Esimerkiksi:

• BEFORE INSERT -liipaisin aktivoidaan (käynnistetään) ennen mitään insert-käskyä.
• AFTER UPDATE -laukaisin aktivoituu (käynnistetään) minkä tahansa päivityskäskyn jälkeen, … ja niin edelleen.

Liipaisimen sisällä voit viitata uusiin lisättyihin arvoihin käyttämällä "uusi" avainsanaa. Voit myös viitata poistettuihin tai päivitettyihin arvoihin käyttämällä vanhaa avainsanaa. seuraavasti:

• INSERT-triggerien sisällä – voidaan käyttää uutta avainsanaa.
• UPDATE-triggerien sisällä – uusia ja vanhoja avainsanoja voidaan käyttää.
• DELETE-triggerien sisällä – vanhaa avainsanaa voidaan käyttää.

esimerkki

Seuraavaksi luomme liipaisimen, joka laukeaa ennen uuden oppilaan lisäämistä ”Oppilaat”-taulukkoon.

Se kirjaa juuri lisätyn opiskelijan "StudentsLog"-taulukkoon automaattisella aikaleimalla, joka vastaa lisäyslausekkeen nykyistä päivämäärää ja kellonajaa. Seuraavasti:

Vaihe 1) Siirry hakemistoon ”C:\sqlite” ja suorita sqlite3.exe.

Vaihe 2) Avaa tietokanta ”TutorialsSampleDB.db” suorittamalla seuraava komento:

.open TutorialsSampleDB.db

Vaihe 3) Luo triggeri ”InsertIntoStudentTrigger” suorittamalla seuraava komento:

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

Funktio ”datetime()” antaa sinulle nykyisen päivämäärän ja aikaleiman, jolloin lisäyslauseke tapahtui. Jotta voimme kirjata lisäystapahtuman ja lisätä automaattisesti aikaleimat jokaiseen tapahtumaan.

Komennon pitäisi toimia onnistuneesti, etkä saa tulosta:

Liipaisin ”InsertIntoStudentTrigger” laukeaa aina, kun lisäät uuden opiskelijan opiskelijat-taulukkoon. Avainsana ”new” viittaa lisättäviin arvoihin. Esimerkiksi ”new.StudentId” on lisättävä opiskelijatunnus.

Nyt testaamme, kuinka liipaisin toimii, kun lisäämme uuden opiskelijan.

Vaihe 4) Kirjoita seuraava komento, joka lisää uuden opiskelijan opiskelijataulukkoon:

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

Vaihe 5) Kirjoita seuraava komento, joka valitsee kaikki rivit ”StudentsLog”-taulukosta:

SELECT * FROM StudentsLog;

Sinun pitäisi nähdä uusi rivi juuri lisätylle uudelle opiskelijalle:

Liipaisin lisäsi tämän rivin ennen uuden opiskelijan lisäämistä tunnuksella 11.

Tässä esimerkissä käytimme luomaamme liipaisinta "InsertIntoStudentTrigger" kaikkien lisäystapahtumien automaattiseen kirjaamiseen "StudentsLog"-taulukkoon. Samalla tavalla voit kirjata mitä tahansa päivitys- tai poistolausekkeita.

Ei-toivottujen päivitysten estäminen triggereillä:

Käyttämällä BEFORE UPDATE -laukaisimia taulukossa voit estää päivityskäskyt sarakkeessa lausekkeen perusteella.

esimerkki

Seuraavassa esimerkissä estämme päivityskäskyjä päivittämästä Opiskelijat-taulukon opiskelijanimi-saraketta:

Vaihe 1) Siirry hakemistoon ”C:\sqlite” ja suorita sqlite3.exe.

Vaihe 2) Avaa tietokanta ”TutorialsSampleDB.db” suorittamalla seuraava komento:

.open TutorialsSampleDB.db

Vaihe 3) Luo uusi triggeri ”preventUpdateStudentName” taulukolle ”Students” suorittamalla seuraava komento:

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

Komento ”RAISE” aiheuttaa virheilmoituksen ”Et voi päivittää opiskelijannimeä”, ja estää päivityslausekkeen suorittamisen.

Tarkistamme nyt, että laukaisin toimii hyvin, ja se estää opiskelijanimi-sarakkeen päivittämisen.

Vaihe 4) Suorita seuraava päivityskomento, joka päivittää opiskelijan nimen ”Jack” muotoon ”Jack1”.

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

Sinun pitäisi saada triggerin yhteydessä määrittämämme virheilmoitus ”Et voi päivittää opiskelijanimeä” seuraavasti:

Vaihe 5) Suorita seuraava komento, joka valitsee opiskelijan nimiluettelon opiskelijataulukosta.

SELECT StudentName FROM Students;

Sinun pitäisi nähdä, että opiskelijan nimi "Jack" on edelleen sama, eikä se muutu:

UKK