SQLite Изгледи, индексиране и задействане с пример

⚡ Умно обобщение

SQLite Изгледите, индексите и тригерите са административни инструменти, които улесняват заявките и поддръжката на база данни: изгледите използват повторно сложни заявки, индексите ускоряват търсенията, а тригерите изпълняват предварително дефинирани действия автоматично при промяна на данните.

👁️ Прегледи: Изгледът е логическа таблица, изградена от SELECT оператор, която ви позволява да използвате повторно сложни заявки, без да ги пренаписвате.
⏳ Временни изгледи: Временните изгледи съществуват само за текущата връзка и се изтриват автоматично след затварянето ѝ.
⚡ Индекси: Индексите работят като индекс на книги, позволявайки SQLite бързо намиране на съответстващи редове, вместо сканиране на всеки ред от таблицата.
🎯 Видове индекси: SQLite поддържа изразни, частични и уникални индекси за фина настройка на производителността за специфични модели на заявки.
🔔 Тригери: Тригерите изпълняват предварително дефинирани операции автоматично преди или след INSERT, UPDATE или DELETE оператори в таблица.
🤖 AI помощ: Инструменти за преобразуване на текст в SQL с изкуствен интелект и GitHub Copilot генерират SQLite изгледи, индекси и тригери от подкани на разбираем английски език.

Прочетете повече

При ежедневната употреба на SQLite, ще ви трябват някои административни инструменти над вашата база данни. Можете също така да ги използвате, за да направите заявките към базата данни по-ефективни чрез създаване на индекси или по-възможни за многократна употреба чрез създаване на изгледи.

SQLite Гледка

Изгледите са много подобни на таблиците. Но Views са логически таблици; те не се съхраняват физически като таблиците. Изгледът се състои от оператор за избор.

Можете да дефинирате изглед за вашите сложни заявки и можете да използвате повторно тези заявки, когато пожелаете, като извикате изгледа директно, вместо да пренаписвате отново заявките.

Изявление CREATE VIEW

За да създадете изглед на база данни, можете да използвате оператора CREATE VIEW, последван от името на изгледа, и след това да поставите заявката, която искате след това.

Пример: В следващия пример ще създадем изглед с име „AllStudentsView“ в примерната база данни „TutorialsSampleDB.db“, както следва:

Стъпка 1) Отворете „Моят компютър“ и отидете в следната директория „C:\sqlite“, след което отворете „sqlite3.exe“:

Стъпка 2) Отворете базата данни „TutorialsSampleDB.db“ със следната команда:

Стъпка 3) Следва основен синтаксис на командата sqlite3 за създаване на изглед

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;

Не трябва да има резултат от командата като този:

Стъпка 4) За да сте сигурни, че изгледът е създаден, можете да изберете списъка с изгледи в базата данни, като изпълните следната команда:

SELECT name FROM sqlite_master WHERE type = 'view';

Трябва да видите, че е върнат изгледът „AllStudentsView“:

Стъпка 5) Сега нашият изглед е създаден, можете да го използвате като нормална таблица нещо подобно:

SELECT * FROM AllStudentsView;

Тази команда ще направи запитване към изгледа „Всички студенти“ и ще избере всички редове от него, както е показано на следната екранна снимка:

Временни изгледи

Временните изгледи са временни за текущата връзка към базата данни, използвана за създаването му. След това, ако затворите връзката с базата данни, всички временни изгледи ще бъдат изтрити автоматично. Временните изгледи се създават с помощта на една от следните команди:

CREATE TEMP VIEW или
СЪЗДАВАНЕ НА ВРЕМЕНЕН ИЗГЛЕД.

Временните изгледи са полезни, ако искате да извършите някои операции за това време и не е необходимо това да бъде постоянен изглед. Така че просто създавате временен изглед, след което извършвате обработката си, като използвате този изглед. Later когато затворите връзката с базата данни, тя ще бъде изтрита автоматично.

Пример:

В следващия пример ще отворим връзка с база данни, след което ще създадем временен изглед.

След това ще затворим тази връзка и ще проверим дали временният изглед все още съществува или не.

Стъпка 1) Отворете sqlite3.exe от директорията „C:\sqlite“, както е обяснено по-горе.

Стъпка 2) Отворете връзка с базата данни „TutorialsSampleDB.db“, като изпълните следната команда:

.open TutorialsSampleDB.db

Стъпка 3) Напишете следната команда, която ще създаде временен изглед „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;

Стъпка 4) Уверете се, че временният изглед „AllStudentsTempView“ е създаден, като изпълните следната команда:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Стъпка 5) Затворете sqlite3.exe и го отворете отново.

Стъпка 6) Отворете връзка с базата данни „TutorialsSampleDB.db“ чрез следната команда:

.open TutorialsSampleDB.db

Стъпка 7) Изпълнете следната команда, за да получите списъка с временен изглед, създаден в базата данни:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Не трябва да виждате никакъв изход, тъй като временният изглед, който създадохме, се изтрива, когато затворихме връзката с базата данни в предишната стъпка. В противен случай, докато поддържате връзката с базата данни отворена, ще можете да видите временния изглед с данни.

Забележки:

Не можете да използвате изразите INSERT, DELETE или UPDATE с изгледи, можете само да използвате командата „избор от изгледи“, както е показано в стъпка 5 в примера CREATE View.
За да изтриете VIEW, можете да използвате командата „DROP VIEW“:

DROP VIEW AllStudentsView;

За да сте сигурни, че изгледът е изтрит, можете да изпълните следната команда, която ви дава списък с изгледи в базата данни:

SELECT name FROM sqlite_master WHERE type = 'view';

Няма да намерите върнати изгледи, тъй като изгледът е бил изтрит, както следва:

Освен повторното използване на сложни заявки с изгледи, SQLite също така извлича съответстващи редове по-бързо чрез индекси.

SQLite индекс

Ако имате книга и искате да търсите по ключова дума за тази книга. Ще търсите тази ключова дума в индекса на книгата. След това ще отидете до номера на страницата за тази ключова дума, за да прочетете повече информация за тази ключова дума.

Ако обаче няма индекс на тази книга или номера на страници, вие ще сканирате цялата книга от началото до края, докато намерите ключовата дума, която търсите. И това е много трудно, особено когато имате индекс и много бавен процес за търсене на ключова дума.

Индекси в SQLite (и същата концепция е валидна за други системи за управление на бази данни също така) работи по същия начин като индексите, намиращи се в задната част на книгите.

Когато търсите няколко реда в SQLite таблица с критерии за търсене, SQLite ще търси във всички редове на таблицата, докато намери редовете, които търсите, които отговарят на критериите за търсене. И този процес става много бавен, когато имате по-големи маси.

Индексите ще ускорят заявките за търсене на данни и ще помогнат за извършване на извличане на данни от таблици. Индексите са определени в колоните на таблицата.

Подобряване на производителността с индекси:

Индексите могат да подобрят ефективността на търсене на данни в таблица. Когато създавате индекс на колона, SQLite ще създаде структура от данни за този индекс, където всяка стойност на поле има указател към целия ред, където принадлежи стойността.

След това, ако изпълните заявка с условие за търсене в колона, която е част от индекс, SQLite първо ще потърси стойността на индекса. SQLite няма да сканира цялата таблица за него. След това ще прочете местоположението, където сочи стойността за реда на таблицата. SQLite ще намери реда на това място и ще го извлече.

Ако обаче колоната, която търсите, не е част от индекс, SQLite ще извърши сканиране за стойностите на колоните, за да намери данните, които търсите. Обикновено процесът ще бъде по-бавен, ако няма индекс.

Представете си книга без индекс и трябва да търсите конкретна дума. Ще сканирате цялата книга от първата до последната страница, търсейки тази дума. Въпреки това, ако имате индекс на тази книга, първо ще потърсите думата в нея. Вземете номера на страницата, където се намира, и след това отидете до нея. Което ще бъде много по-бързо от сканирането на цялата книга от корица до корица.

SQLite СЪЗДАВАНЕ ИНДЕКС

За да създадете индекс на колона, трябва да използвате командата CREATE INDEX. И трябва да го дефинирате по следния начин:

Трябва да посочите името на индекса след командата CREATE INDEX.
След името на индекса трябва да поставите ключовата дума “ON”, последвана от името на таблицата, в която ще бъде създаден индексът.
След това списъкът с имена на колони, които се използват за индекса.
Можете да използвате една от следните ключови думи „ASC“ или „DESC“ след всяко име на колона, за да укажете ред на сортиране, използван за подреждане на данните в индекса.

Пример:

В следващия пример ще създадем индекс „StudentNameIndex“ в таблицата students в базата данни „Students“, както следва:

Стъпка 1) Отидете до папката „C:\sqlite“, както е обяснено по-горе.

Стъпка 2) Отворете sqlite3.exe.

Стъпка 3) Отворете базата данни „TutorialsSampleDB.db“ със следната команда:

.open TutorialsSampleDB.db

Стъпка 4) Създайте нов индекс „StudentNameIndex“, като използвате следната команда:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Не трябва да виждате резултат за това:

Стъпка 5) За да сте сигурни, че индексът е създаден, можете да изпълните следната заявка, която ви дава списъка с индекси, създадени в таблицата Студенти:

PRAGMA index_list(Students);

Трябва да видите върнатия индекс, който току-що създадохме:

Забележки:

Индексите могат да се създават не само въз основа на колони, но и на изрази. Нещо като това:

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

„OrderTotalIndex“ ще се базира на колоната OrderId, а също и на умножението на стойността на колоната Quantity и стойността на колоната Price. Така че всяка заявка за „OrderId“ и „Quantity*Price“ ще бъде ефективна, тъй като заявката ще използва индекса.

Ако сте посочили клауза WHERE в оператора CREATE INDEX, индексът ще бъде частичен индекс. В този случай ще има записи в индекса само за редовете, които отговарят на условията в клаузата WHERE. Например в следния индекс:
```
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;
```
( В горния пример индексът ще бъде частичен индекс, тъй като има посочена клауза WHERE. В този случай индексът ще се прилага само към онези поръчки, които имат стойност на количеството, по-голяма от 10000 XNUMX. Имайте предвид, че този индекс се нарича частичен индекс поради клаузата WHERE, а не израза, използван върху него, но можете да използвате изразите с нормални индекси.)
Можете да използвате оператора CREATE UNIQUE INDEX вместо CREATE INDEX, за да предотвратите дублиращи се записи за колоните и по този начин всички стойности за индексираната колона ще бъдат уникални.
За да изтриете индекс, използвайте командата DROP INDEX, последвана от името на индекса за изтриване.

Докато индексите правят четенето по-бързо, тригерите позволяват SQLite реагира автоматично при всяка промяна на данните.

SQLite Тригер

Въведение в SQLite Тригер

Тригерите са автоматични предварително дефинирани операции, изпълнявани, когато се случи конкретно действие върху таблица на база данни. Може да се дефинира тригер, който да се задейства, когато едно от следните действия се случи на таблица:

INSERT в таблица.
ИЗТРИВАНЕ на редове от таблица.
АКТУАЛИЗИРАНЕ на една от колоните на таблицата.

SQLite поддържа тригера ЗА ВСЕКИ РЕД, така че предварително дефинираните операции в тригера да бъдат изпълнени за всички редове, участващи в действията, извършени в таблицата (независимо дали е вмъкване, изтриване или актуализиране).

SQLite СЪЗДАВАНЕ НА ТРИГЕР

За да създадете нов TRIGGER, можете да използвате командата CREATE TRIGGER, както следва:

След CREATE TRIGGER трябва да посочите име на тригер.
След името на тригера трябва да посочите кога точно името на тригера да се изпълни. Имате три възможности:
- BEFORE – тригерът ще бъде изпълнен преди указания оператор INSERT, UPDATE или изтриване.
- След – тригерът ще се изпълни след указания оператор INSERT, UPDATE или изтриване.
- ВМЕСТО – Ще замени извършеното действие, което е задействало тригера, с израза, посочен в TRIGGER. ВМЕСТО тригерът не е приложим с таблици, а само с изгледи.
След това трябва да посочите типа действие, тригерът ще се задейства, когато се случи. Или DELETE, INSERT или UPDATE.
Можете да изберете незадължително име на колона, така че тригерът да не се задейства, освен ако действието не е извършено върху тази колона.
След това трябва да посочите името на таблицата, в която ще бъде създаден тригерът.
Вътре в тялото на тригера трябва да посочите оператора, който трябва да се изпълни за всеки ред, когато тригерът се задейства.

Тригерите ще бъдат активирани (задействани) само в зависимост от типа на оператора, посочен в командата за създаване на тригер. Например:

Тригерът BEFORE INSERT ще бъде активиран (задействан) преди всеки оператор за вмъкване.
Тригерът СЛЕД АКТУАЛИЗАЦИЯ ще бъде активиран (задействан) след всяко изявление за актуализиране, ... и така нататък.

Вътре в тригера можете да се обърнете към нововмъкнатите стойности, като използвате ключовата дума „нова“. Също така можете да се обърнете към изтритите или актуализирани стойности, като използвате старата ключова дума. Както следва:

Вътре в тригерите INSERT – може да се използва нова ключова дума.
В тригерите UPDATE – могат да се използват нови и стари ключови думи.
В тригерите DELETE – може да се използва стара ключова дума.

Пример

По-долу ще създадем тригер, който ще се задейства преди вмъкване на нов студент в таблицата „Студенти“.

Това ще запише нововъведения студент в таблицата „StudentsLog“ с автоматичен времеви печат за текущата дата и час, когато е извършено вмъкването. Както следва:

Стъпка 1) Отидете в директорията „C:\sqlite“ и стартирайте sqlite3.exe.

Стъпка 2) Отворете базата данни „TutorialsSampleDB.db“, като изпълните следната команда:

.open TutorialsSampleDB.db

Стъпка 3) Създайте тригера „InsertIntoStudentTrigger“, като изпълните следната команда:

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

Функцията „datetime()“ ще ви даде текущата дата и час, когато е извършено вмъкването на оператора. Така че можем да регистрираме транзакцията за вмъкване с автоматично добавени времеви печати към всяка транзакция.

Командата трябва да се изпълни успешно и не получавате резултат:

Тригерът „InsertIntoStudentTrigger“ ще се задейства всеки път, когато вмъкнете нов студент в таблицата със студенти. Ключовата дума „new“ се отнася до стойностите, които ще бъдат вмъкнати. Например, „new.StudentId“ ще бъде идентификаторът на студента, който ще бъде вмъкнат.

Сега ще тестваме как се държи тригерът, когато вмъкнем нов студент.

Стъпка 4) Напишете следната команда, която ще вмъкне нов студент в таблицата студенти:

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

Стъпка 5) Напишете следната команда, която ще избере всички редове от таблицата „StudentsLog“:

SELECT * FROM StudentsLog;

Трябва да видите върнат нов ред за новия ученик, който току-що вмъкнахме:

Този ред беше вмъкнат от тригера преди вмъкването на новия ученик с идентификатор 11.

В този пример използвахме създадения от нас тригер „InsertIntoStudentTrigger“, за да регистрираме автоматично всички транзакции за вмъкване в таблицата „StudentsLog“. По същия начин можете да регистрирате всякакви оператори за актуализиране или изтриване.

Предотвратяване на нежелани актуализации с тригери:

Използвайки тригери BEFORE UPDATE на таблица, можете да предотвратите изразите за актуализиране на колона въз основа на израз.

Пример

В следния пример ще предотвратим актуализиране на колоната „име на студент“ в таблицата „Студенти“ от всяко изявление за актуализиране:

Стъпка 1) Отидете в директорията „C:\sqlite“ и стартирайте sqlite3.exe.

Стъпка 2) Отворете базата данни „TutorialsSampleDB.db“, като изпълните следната команда:

.open TutorialsSampleDB.db

Стъпка 3) Създайте нов тригер „preventUpdateStudentName“ в таблицата „Students“, като изпълните следната команда

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

Командата „RAISE“ ще генерира грешка със съобщение за грешка „Не можете да актуализирате името на студента“ и след това ще предотврати изпълнението на оператора за актуализиране.

Сега ще проверим дали тригерът работи добре и предотвратява всяка актуализация за колоната име на студент.

Стъпка 4) Изпълнете следната команда за актуализиране, която ще актуализира името на ученика „Джак“ на „Джак1“.

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

Трябва да получите съобщението за грешка, което посочихме на тригера, което гласи „Не можете да актуализирате името на студента“, както следва:

Стъпка 5) Изпълнете следната команда, която ще избере списъка с имена на ученици от таблицата на учениците.

SELECT StudentName FROM Students;

Трябва да видите, че името на ученика „Джак“ все още е същото и не се променя:

Въпроси и Отговори

Таблицата физически съхранява данни на диск, докато изгледът е виртуална таблица, дефинирана от запазен SELECT оператор. Изгледът не съхранява собствени данни; той изпълнява тази заявка всеки път, когато я четете, представяйки редове от подлежащите таблици.

SQLite Изгледите са само за четене, така че INSERT, UPDATE и DELETE не могат да се изпълняват директно срещу тях. За да направите изглед достъпен за запис, прикачете тригер INSTEAD OF, който преобразува операцията в промени в подлежащите базови таблици.

Създайте индекс на колони, използвани често в клаузите WHERE, JOIN или ORDER BY, особено колони с висока кардиналност и малко дублиращи се стойности. Индексите ускоряват търсенията в SELECT, но добавянето им към рядко търсени или много малки таблици дава малка полза.

Да. Всеки индекс трябва да се актуализира при всяка промяна на редовете, така че всеки допълнителен индекс добавя разходи за запис и място за съхранение. Индексирайте често колоните, в които търсите, но избягвайте прекомерно индексиране на таблици, които получават интензивен трафик на INSERT, UPDATE или DELETE.

Не. SQLite няма команда CREATE MATERIALIZED VIEW и обикновените изгледи никога не кешират резултатите си. За да имитирате такава, създайте истинска таблица и я поддържайте синхронизирана, като използвате тригери AFTER INSERT, UPDATE и DELETE върху изходните таблици.

sqlite_master е вграденият каталог на схемите, който изброява всяка таблица, изглед, индекс и тригер в базата данни. Изпратете заявка към него, например SELECT name FROM sqlite_master WHERE type = 'view';, за да проверите кои обекти съществуват.

Да. Асистентите с изкуствен интелект за преобразуване на текст в SQL превръщат заявките на обикновен английски език в SQLite Оператори CREATE VIEW, CREATE INDEX и CREATE TRIGGER. Предоставянето на реални имена на таблици и колони подобрява точността и всеки генериран оператор трябва да бъде прегледан и тестван преди изпълнение върху производствени данни.

Копилот на GitHub подсказва SQLite изгледи, индекси и тригери, вградени в редактори като VS CodeТой чете близки схеми и коментари, така че довършванията използват повторно истинските ви имена на таблици и колони, но все пак трябва да проверите всяко твърдение, преди да го изпълните.