SQLite Triggeri, katselukerrat ja hakemisto esimerkin avulla
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 nimellä "AllStudentsView" näytetietokannassa "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" 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"kuten aiemmin selitettiin.
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ä lämpötilanä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:
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 indeksin "StudentNameIndex" opiskelijoiden pöydällä "Opiskelijat” tietokanta seuraavasti:
Vaihe 1) Siirry kansioon "C:\sqlite"kuten aiemmin selitettiin.
Vaihe 2) Avaa sqlite3.exe.
Vaihe 3) Avaa tietokanta "TutorialsSampleDB.db" seuraavalla komennolla:
.open TutorialsSampleDB.db
Vaihe 4) Luo uusi hakemisto "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ä.
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ä.
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
Seuraavassa luomme triggerin, joka käynnistyy ennen kuin lisäät uuden opiskelijan "Opiskelijat" pöytä.
Se kirjaa juuri lisätyn opiskelijan taulukkoon "Opiskelijaloki", jossa on automaattinen aikaleima nykyiselle päivämäärälle ja kellolle, jolloin insert-käsky tapahtui. 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 laukaisin"InsertIntoStudentTrigger" Suorittamalla seuraava komento:
CREATE TRIGGER InsertIntoStudentTrigger BEFORE INSERT ON Students BEGIN INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert'); END;
Toiminto "treffiaika()" antaa sinulle nykyisen päivämäärän aikaleiman, kun insert-lause tapahtui. Jotta voimme kirjata lisäystapahtuman jokaiseen tapahtumaan lisätyillä automaattisilla aikaleimoilla.
Komennon pitäisi toimia onnistuneesti, etkä saa tulosta:
Laukaisin "InsertIntoStudentTrigger" käynnistyy aina, kun lisäät uuden opiskelijan oppilastaulukkoon. "uusi”-avainsana viittaa arvoihin, jotka lisätään. Esimerkiksi "new.StudentId” on opiskelijatunnus, joka lisätään.
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 "Opiskelijaloki" pöytä:
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 laukaisinta " InsertIntoStudentTrigger " loimme kirjataksemme kaikki tapahtumat taulukkoon "Opiskelijaloki" automaattisesti. Samalla tavalla voit kirjata mitä tahansa päivityksiä tai poistaa lausuntoja.
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" pöydällä "Opiskelijat" suorittamalla seuraava komento
CREATE TRIGGER preventUpdateStudentName BEFORE UPDATE OF StudentName ON Students FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'You cannot update studentname'); END;
"NOSTAA"komento aiheuttaa virheen virheilmoituksella" Et voi päivittää opiskelijan nimeä ", ja sitten se estää päivityskäskyn 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 "tunkki" olla "Jack1".
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
Sinun pitäisi saada virheilmoitus, jonka määritimme laukaisimeen, jossa sanotaan, että "Et voi päivittää opiskelijan nimeä" 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:
Yhteenveto
Näkymät, hakemistot ja triggerit ovat erittäin tehokkaita työkaluja järjestelmän hallintaan SQLite tietokanta. Voit seurata tietojen muokkaustoimintoja, kun ne tapahtuvat taulukossa. Voit myös optimoida tietokannan tietojen hakutoimintoa luomalla indeksejä.