SQLite Vaated, indeks ja päästik koos 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 näidisandmebaasis „TutorialsSampleDB.db” vaate nimega „AllStudentsView” järgmiselt:

Step 1) Ava Minu arvuti ja navigeeri järgmisse kataloogi „C:\sqlite” ning seejärel ava „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” (kõikide õpilaste vaade):

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) Ava 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 ajutine vaade „AllStudentsTempView” luuakse järgmise käsu abil:

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:

Lisaks keerukate päringute taaskasutamisele vaadetega, SQLite leiab ka indeksite kaudu kiiremini vastavad read.

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 õpilaste tabelisse andmebaasis „Õpilased“ indeksi „StudentsNameIndex“ järgmiselt:

Step 1) Navigeeri 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 indeks „StudentNameIndex” järgmise käsuga:

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.

Kuigi indeksid kiirendavad lugemist, lasevad päästikud SQLite reageerib automaatselt iga kord, kui andmed muutuvad.

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.
• Seejärel peate määrama toimingu tüübi, päästik käivitub, kui see juhtub. Kas DELETE, INSERT või UPDATE.
• Saate valida valikulise veeru nime, nii et päästik ei käivitu, välja arvatud juhul, kui toiming toimus selles veerus.
• Seejärel tuleb määrata tabeli nimi, milles päästik luuakse.
• Päästiku põhiosas peaksite määrama avalduse, mis käivitatakse iga rea ​​jaoks, kui käivitatakse.

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 tabelisse „Õpilased“.

See logib äsja lisatud õpilase tabelisse „StudentsLog“ koos automaatse ajatempliga praeguse kuupäeva ja kellaaja järgi, millal lisamislause toimus. Järgmiselt:

Step 1) Liikuge kataloogi „C:\sqlite” ja käivitage sqlite3.exe.

Step 2) Avage andmebaas „TutorialsSampleDB.db”, käivitades järgmise käsu:

.open TutorialsSampleDB.db

Step 3) Loo päästik „InsertIntoStudentTrigger”, käivitades järgmise käsu:

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

Funktsioon „datetime()” annab sulle praeguse kuupäeva ja kellaaja templi, millal sisestuslause toimus. Nii saame sisestamistehingu logida, lisades igale tehingule automaatsed ajatempli.

Käsk peaks edukalt töötama ja te ei saa väljundit:

Käivituskäsk „InsertIntoStudentTrigger” käivitub iga kord, kui lisate õpilaste tabelisse uue õpilase. Märksõna „new” viitab lisatavatele väärtustele. Näiteks „new.StudentId” on lisatav õpilase ID.

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 tabelist „StudentsLog”:

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 tabelisse „StudentsLog“ loodud päästikut „InsertIntoStudentTrigger“, et logida automaatselt kõik sisestustehingud. Samamoodi saate logida ka värskendus- või kustutuslauseid.

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) Avage andmebaas „TutorialsSampleDB.db”, käivitades järgmise käsu:

.open TutorialsSampleDB.db

Step 3) Loo tabelile „Õpilased“ uus päästik „preventUpdateStudentName“, 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;

Käsk „RAISE” tekitab veateate „Te ei saa õpilase nime uuendada” ja seejärel takistab see uuendamislause käivitamist.

Nüüd kontrollime, kas päästik töötab hästi, ja see takistab õpilase nime veeru värskendamist.

Step 4) Käivita järgmine värskenduskäsk, mis uuendab õpilase nime „Jack“ nimeks „Jack1“.

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

Sa peaksid saama päästikul määratud veateate „Te ei saa õpilase nime uuendada” 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:

KKK