SQLite Päästik, vaatamised ja indeks näitega
Igapäevases kasutuses SQLite, vajate oma andmebaasi haldustööriistu. Samuti saate neid kasutada andmebaasi päringute tõhusamaks muutmiseks, luues indekseid, või taaskasutatavamaks, luues vaateid.
SQLite vaade
Vaated on väga sarnased tabelitega. Kuid vaated on loogilised tabelid; neid ei säilitata füüsiliselt nagu tabeleid. Vaade koosneb valitud lausest.
Saate määratleda oma keerukate päringute jaoks vaate ja saate neid päringuid igal ajal uuesti kasutada, kutsudes päringute uuesti ümberkirjutamise asemel vaate otse.
CREATE VIEW avaldus
Andmebaasi vaate loomiseks võite kasutada käsku CREATE VIEW, millele järgneb vaate nimi, ja seejärel sisestada soovitud päringu.
Näide: Järgmises näites loome vaate nimega "AllStudentsView" näidisandmebaasis "TutorialsSampleDB.db” järgmiselt:
Step 1) Avage Minu arvuti ja liikuge järgmisse kataloogi "C:\sqliteja seejärel avage "sqlite3.exe"
Step 2) Ava andmebaas "TutorialsSampleDB.db” järgmise käsuga:
Step 3) Järgmine on sqlite3 käsu põhisüntaks vaate loomiseks
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;
Käsklusest ei tohiks olla sellist väljundit:
Step 4) Vaate loomise tagamiseks saate valida andmebaasis vaadete loendi, käivitades järgmise käsu:
SELECT name FROM sqlite_master WHERE type = 'view';
Sa peaksid nägema vaadet "AllStudentsView” tagastatakse:
Step 5) Nüüd on meie vaade loodud, saate seda tavalise tabelina kasutada umbes nii:
SELECT * FROM AllStudentsView;
See käsk küsib vaadet "Kõik õpilased" ja valib sellest kõik read, nagu on näidatud järgmisel ekraanipildil:
Ajutised vaated
Ajutised vaated on selle loomiseks kasutatud praeguse andmebaasiühenduse jaoks ajutised. Kui sulgete andmebaasiühenduse, kustutatakse kõik ajutised vaated automaatselt. Ajutised vaated luuakse ühe järgmistest käskudest:
- CREATE TEMP VIEW või
- LOO AJUTINE VAADE.
Ajutised vaated on kasulikud, kui soovite selleks ajaks mõnda toimingut teha ega vaja, et see oleks püsivaade. Seega loote lihtsalt ajutise vaate ja seejärel töötlete seda vaadet kasutades. Later kui sulgete ühenduse andmebaasiga, kustutatakse see automaatselt.
Näide:
Järgmises näites avame andmebaasiühenduse, seejärel loome ajutise vaate.
Pärast seda sulgeme selle ühenduse ja kontrollime, kas ajutine vaade on endiselt olemas või mitte.
Step 1) Avage sqlite3.exe kataloogist "C:\sqlite” nagu eelnevalt selgitatud.
Step 2) Avage ühendus andmebaasiga "TutorialsSampleDB.db", käivitades järgmise käsu:
.open TutorialsSampleDB.db
Step 3) Kirjutage järgmine käsk, mis loob ajutise vaate "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;
Step 4) Veenduge, et tempvaade "AllStudentsTempView” luuakse järgmise käsu käivitamisega:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Step 5) Sulgege sqlite3.exe ja avage see uuesti.
Step 6) Avage ühendus andmebaasiga "TutorialsSampleDB.db” järgmise käsuga:
.open TutorialsSampleDB.db
Step 7) Käivitage järgmine käsk, et saada andmebaasis loodud ajutise vaate loend:
SELECT name FROM sqlite_temp_master WHERE type = 'view';
Te ei peaks nägema väljundit, kuna meie loodud ajutine vaade kustutatakse, kui sulgesime eelmises etapis andmebaasiühenduse. Vastasel juhul näete ajutist vaadet andmetega seni, kuni hoiate ühendust andmebaasiga.
Märkused:
- Te ei saa vaadete puhul kasutada lauseid INSERT, DELETE ega UPDATE, saate kasutada ainult käsku "vali vaadetest", nagu on näidatud näite CREATE View näites punktis 5.
- VIEW-i kustutamiseks võite kasutada lauset „DROP VIEW”.
DROP VIEW AllStudentsView;
Vaate kustutamise tagamiseks võite käivitada järgmise käsu, mis annab teile andmebaasis olevate vaadete loendi:
SELECT name FROM sqlite_master WHERE type = 'view';
Vaate kustutamisel ei tagastata ühtegi vaadet järgmiselt:
SQLite indeks
Kui teil on raamat ja soovite otsida selle raamatu märksõna. Te otsite seda märksõna raamatu registrist. Seejärel liigute selle märksõna leheküljenumbrile, et selle märksõna kohta lisateavet lugeda.
Kui aga sellel raamatul pole registrit ega leheküljenumbreid, skannite kogu raamatu algusest lõpuni, kuni leiate otsitava märksõna. Ja see on väga raske, eriti kui teil on märksõna otsimiseks indeks ja väga aeglane protsess.
Indeksid sisse SQLite (ja sama kontseptsioon kehtib ka teiste kohta andmebaasihaldussüsteemid samuti) töötab samamoodi nagu raamatute tagaküljel olevad registrid.
Kui otsite mõnda rida an SQLite tabel otsingukriteeriumitega, SQLite otsib kõigilt tabeli ridadelt, kuni leiab otsitavad read, mis vastavad otsingukriteeriumidele. Ja see protsess muutub väga aeglaseks, kui teil on suuremad lauad.
Indeksid kiirendavad andmete otsingupäringuid ja aitavad tabelitest andmeid hankida. Indeksid on määratletud tabeli veergudel.
Toimivuse parandamine indeksite abil:
Indeksid võivad parandada tabelist andmete otsimise toimivust. Kui loote veerule indeksi, SQLite loob selle indeksi jaoks andmestruktuuri, kus igal välja väärtusel on kursor kogu reale, kuhu väärtus kuulub.
Seejärel, kui käivitate otsingutingimusega päringu veerus, mis on indeksi osa, SQLite otsib kõigepealt indeksist väärtuse. SQLite ei skaneeri selle jaoks tervet tabelit. Seejärel loeb see asukohta, kuhu tabelirea väärtus osutab. SQLite otsib sellest asukohast rea ja hangib selle.
Kui aga otsitav veerg ei ole registri osa, SQLite otsib otsitavate andmete leidmiseks veeru väärtusi. Kui indeksit pole, on see tavaliselt aeglasem protsess.
Kujutage ette raamatut, millel pole registrit ja peate otsima konkreetset sõna. Skaneerite kogu raamatut esimesest leheküljest viimase leheküljeni, otsides seda sõna. Kui teil on selle raamatu kohta register, otsite kõigepealt sellelt sõna. Hankige lehenumber, kus see asub, ja seejärel navigeerige sellele. Mis on palju kiirem kui terve raamatu kaanest kaaneni skannimine.
SQLite LOO INDEKS
Veerule indeksi loomiseks tuleks kasutada käsku CREATE INDEX. Ja peaksite selle määratlema järgmiselt:
- Pärast käsku CREATE INDEX peate määrama indeksi nime.
- Indeksi nime järele tuleb panna märksõna “ON”, millele järgneb tabeli nimi, milles indeks luuakse.
- Seejärel loendi veergude nimedest, mida indeksi jaoks kasutatakse.
- Indeksiandmete järjestamiseks kasutatava sortimisjärjestuse määramiseks võite kasutada mis tahes veeru nime järel üht järgmistest märksõnadest "ASC" või "DESC".
Näide:
Järgmises näites loome indeksi „Õpilasenimeindeks" õpilaste laual "Õpilased” andmebaas järgmiselt:
Step 1) Liikuge kausta "C:\sqlite” nagu eelnevalt selgitatud.
Step 2) Avage sqlite3.exe.
Step 3) Ava andmebaas "TutorialsSampleDB.db” järgmise käsuga:
.open TutorialsSampleDB.db
Step 4) Loo uus register "StudentNameIndex" kasutades järgmist käsku:
CREATE INDEX StudentNameIndex ON Students(StudentName);
Selle jaoks ei tohiks te väljundit näha:
Step 5) Indeksi loomise tagamiseks võite käivitada järgmise päringu, mis annab teile tabelis Õpilased loodud indeksite loendi:
PRAGMA index_list(Students);
Peaksite nägema äsja loodud indeksi tagastamist:
Märkused:
- Indekseid saab luua mitte ainult veergude, vaid ka avaldiste põhjal. Midagi sellist:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);
„OrderTotalIndex” põhineb veerul OrderId ning ka veeru Quantity väärtuse ja hinna veeru väärtuse korrutamisel. Seega on kõik päringud „OrderId” ja „Quantity*Price” jaoks tõhusad, kuna päring kasutab indeksit.
- Kui määrasite lauses CREATE INDEX klausli WHERE, on indeks osaline indeks. Sel juhul on registris kirjed ainult nende ridade kohta, mis vastavad klausli WHERE tingimustele. Näiteks järgmises registris:
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price) WHERE Quantity > 10000;
( Ülaltoodud näites on indeks osaline indeks, kuna on määratud WHERE-klausel. Sel juhul rakendatakse indeksit ainult nendele tellimustele, mille koguse väärtus on suurem kui 10000. Pange tähele, et seda indeksit nimetatakse osaliseks indeks WHERE-klausli tõttu, mitte sellel kasutatud avaldise tõttu. Siiski saate kasutada tavaliste indeksidega avaldisi.)
- Saate kasutada CREATE UNIQUE INDEX käsku CREATE INDEX asemel, et vältida veergude dubleerivaid kirjeid ja seega on kõik indekseeritud veeru väärtused kordumatud.
- Indeksi kustutamiseks kasutage käsku DROP INDEX, millele järgneb kustutamiseks indeksi nimi.
SQLite Trigger
Sissejuhatus SQLite Trigger
Päästikud on automaatsed eelmääratletud toimingud, mis käivitatakse siis, kui andmebaasitabelis toimub konkreetne toiming. Päästiku saab määrata käivituma iga kord, kui tabelis toimub üks järgmistest toimingutest.
- Sisesta tabelisse.
- KUSTUTA tabelist read.
- VÄRSKENDAGE ühte tabeli veergu.
SQLite toetab IGA REA päästikut, nii et käivitaja eelmääratletud toimingud täidetakse kõigi tabelis toimunud toimingutega seotud ridade jaoks (olgu see siis sisestamine, kustutamine või värskendamine).
SQLite LOO TRIGGER
Uue TRIGGERi loomiseks saate kasutada lauset CREATE TRIGGER järgmiselt.
- Pärast CREATE TRIGGER'i peaksite määrama päästiku nime.
- Pärast trigeri nime peate määrama, millal täpselt käivitaja nimi käivitatakse. Teil on kolm võimalust.
- ENNE – päästik käivitatakse enne määratud käsku INSERT, UPDATE või delete.
- Pärast – päästik käivitatakse pärast määratud käsku INSERT, UPDATE või delete.
- ASEMEL – see asendab käivitu käivitanud toimingu TRIGGERis määratud avaldusega. Päästiku ASEMEL ei ole tabelite puhul rakendatav, vaid ainult vaadetega.
Päästikud aktiveeritakse (käivitatakse) ainult sõltuvalt käivitamiskäskluses määratud avalduse tüübist. Näiteks:
- Päästik BEFORE INSERT aktiveeritakse (käivitatakse) enne mis tahes insert-lauset.
- Päästiku AFTER UPDATE aktiveeritakse (käivitatakse) pärast iga värskendusavaldust jne.
Päästiku sees saate viidata äsja sisestatud väärtustele, kasutades märksõna "uus". Samuti saate vana märksõna abil viidata kustutatud või värskendatud väärtustele. järgmiselt:
- INSERT-päästikute sees – saab kasutada uut märksõna.
- UPDATE päästikute sees – saab kasutada uusi ja vanu märksõnu.
- DELETE päästikute sees – saab kasutada vana märksõna.
Näide
Järgnevalt loome päästiku, mis käivitub enne uue õpilase lisamistÕpilased”Tabel.
See logib äsja sisestatud õpilase tabelisse "ÕpilasedLogi” automaatse ajatempliga praeguse kuupäeva ja kellaaja jaoks, millal sisestamislause juhtus. järgmiselt:
Step 1) Liikuge kataloogi "C:\sqlite” ja käivitage sqlite3.exe.
Step 2) Ava andmebaas "TutorialsSampleDB.db", käivitades järgmise käsu:
.open TutorialsSampleDB.db
Step 3) loo päästik "InsertIntoStudentTrigger” Käitades järgmise käsu:
CREATE TRIGGER InsertIntoStudentTrigger BEFORE INSERT ON Students BEGIN INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert'); END;
funktsioon "datetime()" annab teile praeguse kuupäeva ja kellaaja templi, millal sisestamise avaldus toimus. Et saaksime sisestatud tehingu logida igale tehingule lisatud automaatsete ajatemplitega.
Käsk peaks edukalt töötama ja te ei saa väljundit:
Päästiku "InsertIntoStudentTrigger” käivitub iga kord, kui lisate õpilaste tabelisse uue õpilase. "uus” märksõna viitab väärtustele, mis lisatakse. Näiteks "new.StudentId” on õpilase ID, mis lisatakse.
Nüüd testime, kuidas päästik käitub uue õpilase sisestamisel.
Step 4) Kirjutage järgmine käsk, mis lisab õpilaste tabelisse uue õpilase:
INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');
Step 5) Kirjutage järgmine käsk, mis valib kõik read "ÕpilasedLogi" tabel:
SELECT * FROM StudentsLog;
Peaksite nägema äsja sisestatud uue õpilase kohta tagastatud uut rida:
Selle rea sisestas päästik enne uue õpilase sisestamist ID-ga 11.
Selles näites kasutasime päästikut " InsertIntoStudentTrigger " lõime, et logida kõik tehingud tabelisse "ÕpilasedLogi” automaatselt. Samamoodi saate värskendusi logida või avaldusi kustutada.
Soovimatute värskenduste vältimine päästikutega:
Kasutades tabeli päästikuid BEFORE UPDATE, saate takistada avaldise põhjal veerus värskenduslauseid.
Näide
Järgmises näites takistame ühelgi värskendusavaldusel tabelis Õpilased veeru „õpilase nimi” värskendamist.
Step 1) Liikuge kataloogi "C:\sqlite” ja käivitage sqlite3.exe.
Step 2) Ava andmebaas "TutorialsSampleDB.db", käivitades järgmise käsu:
.open TutorialsSampleDB.db
Step 3) Loo uus päästik "preventUpdateStudentName"laual"Õpilased” käivitades järgmise käsu
CREATE TRIGGER preventUpdateStudentName BEFORE UPDATE OF StudentName ON Students FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'You cannot update studentname'); END;
"TÕSTA"käsk tekitab veateate ja veateate" Õpilase nime ei saa värskendada “, ja siis see takistab värskendusavalduse täitmist.
Nüüd kontrollime, kas päästik töötab hästi, ja see takistab õpilase nime veeru värskendamist.
Step 4) Käivitage järgmine värskenduskäsk, mis värskendab õpilase nime "tungraud"olla"Jack1".
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
Peaksite saama tõrketeate, mille me päästikul määrasime, öeldes, et "Õpilase nime ei saa värskendada” järgmiselt:
Step 5) Käivitage järgmine käsk, mis valib õpilaste tabelist õpilaste nimede loendi.
SELECT StudentName FROM Students;
Peaksite nägema, et õpilase nimi "Jack" jääb samaks ja see ei muutu:
kokkuvõte
Vaated, indeksid ja päästikud on väga võimsad tööriistad haldamiseks SQLite andmebaas. Saate jälgida andmete muutmise toiminguid, kui need tabelis toimuvad. Samuti saate optimeerida andmebaasi andmete otsimise toimingut, luues indekseid.