SQLite Тригер, изгледи и индекс с пример
Маркъс Алън
При ежедневната употреба на SQLite, ще ви трябват някои административни инструменти над вашата база данни. Можете също така да ги използвате, за да направите заявките към базата данни по-ефективни чрез създаване на индекси или по-възможни за многократна употреба чрез създаване на изгледи.
SQLite Гледка
Изгледите са много подобни на таблиците. Но Views са логически таблици; те не се съхраняват физически като таблиците. Изгледът се състои от оператор за избор.
Можете да дефинирате изглед за вашите сложни заявки и можете да използвате повторно тези заявки, когато пожелаете, като извикате изгледа директно, вместо да пренаписвате отново заявките.
Изявление CREATE VIEW
За да създадете изглед на база данни, можете да използвате оператора CREATE VIEW, последван от името на изгледа, и след това да поставите заявката, която искате след това.
Пример: В следващия пример ще създадем изглед с името „AllStudentsView" в примерната база данни "УроциSampleDB.db”, както следва:
Стъпка 1) Отворете Моят компютър и отидете до следната директория „C:\sqlite" и след това отворете "sqlite3.exe"
Стъпка 2) Отворете базата данни "УроциSampleDB.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) Отворете връзка към базата данни “УроциSampleDB.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) Отворете връзка към базата данни “УроциSampleDB.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 СЪЗДАВАНЕ ИНДЕКС
За да създадете индекс на колона, трябва да използвате командата CREATE INDEX. И трябва да го дефинирате по следния начин:
- Трябва да посочите името на индекса след командата CREATE INDEX.
- След името на индекса трябва да поставите ключовата дума “ON”, последвана от името на таблицата, в която ще бъде създаден индексът.
- След това списъкът с имена на колони, които се използват за индекса.
- Можете да използвате една от следните ключови думи „ASC“ или „DESC“ след всяко име на колона, за да укажете ред на сортиране, използван за подреждане на данните в индекса.
Пример:
В следващия пример ще създадем индекс „Индекс на име на ученик” на студентската маса в „Студенти” база данни, както следва:
Стъпка 1) Отидете до папката „C:\sqlite”, както беше обяснено преди.
Стъпка 2) Отворете sqlite3.exe.
Стъпка 3) Отворете базата данни "УроциSampleDB.db” чрез следната команда:
.open TutorialsSampleDB.db
Стъпка 4) Създайте нов индекс "Индекс на име на ученик” с помощта на следната команда:
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 Тригер
Тригерите са автоматични предварително дефинирани операции, изпълнявани, когато се случи конкретно действие върху таблица на база данни. Може да се дефинира тригер, който да се задейства, когато едно от следните действия се случи на таблица:
- INSERT в таблица.
- ИЗТРИВАНЕ на редове от таблица.
- АКТУАЛИЗИРАНЕ на една от колоните на таблицата.
SQLite поддържа тригера ЗА ВСЕКИ РЕД, така че предварително дефинираните операции в тригера да бъдат изпълнени за всички редове, участващи в действията, извършени в таблицата (независимо дали е вмъкване, изтриване или актуализиране).
SQLite СЪЗДАВАНЕ НА ТРИГЕР
За да създадете нов TRIGGER, можете да използвате командата CREATE TRIGGER, както следва:
- След CREATE TRIGGER трябва да посочите име на тригер.
- След името на тригера трябва да посочите кога точно името на тригера да се изпълни. Имате три възможности:
- BEFORE – тригерът ще бъде изпълнен преди указания оператор INSERT, UPDATE или изтриване.
- След – тригерът ще се изпълни след указания оператор INSERT, UPDATE или изтриване.
- ВМЕСТО – Ще замени извършеното действие, което е задействало тригера, с израза, посочен в TRIGGER. ВМЕСТО тригерът не е приложим с таблици, а само с изгледи.
Тригерите ще бъдат активирани (задействани) само в зависимост от типа на оператора, посочен в командата за създаване на тригер. Например:
- Тригерът BEFORE INSERT ще бъде активиран (задействан) преди всеки оператор за вмъкване.
- Тригерът СЛЕД АКТУАЛИЗАЦИЯ ще бъде активиран (задействан) след всяко изявление за актуализиране, ... и така нататък.
Вътре в тригера можете да се обърнете към нововмъкнатите стойности, като използвате ключовата дума „нова“. Също така можете да се обърнете към изтритите или актуализирани стойности, като използвате старата ключова дума. Както следва:
- Вътре в тригерите INSERT – може да се използва нова ключова дума.
- В тригерите UPDATE – могат да се използват нови и стари ключови думи.
- В тригерите DELETE – може да се използва стара ключова дума.
Пример
По-долу ще създадем тригер, който ще се задейства, преди да вмъкнем нов ученик в „Студенти”Маса.
Той ще регистрира нововмъкнатия ученик в таблицата "Студентски дневник” с автоматично времево клеймо за текущата дата и час, когато се е случило изразът за вмъкване. Както следва:
Стъпка 1) Отидете до директорията „C:\sqlite” и стартирайте sqlite3.exe.
Стъпка 2) Отворете базата данни "УроциSampleDB.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.StudentId” ще бъде идентификаторът на ученик, който ще бъде вмъкнат.
Сега ще тестваме как се държи тригерът, когато вмъкнем нов студент.
Стъпка 4) Напишете следната команда, която ще вмъкне нов студент в таблицата студенти:
INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');
Стъпка 5) Напишете следната команда, която ще избере всички редове от „Студентски дневник” таблица:
SELECT * FROM StudentsLog;
Трябва да видите върнат нов ред за новия ученик, който току-що вмъкнахме:
Този ред беше вмъкнат от тригера преди вмъкването на новия ученик с идентификатор 11.
В този пример използвахме тригера „ InsertIntoStudentTrigger ” създадохме, за да регистрираме всички вмъкнати транзакции в таблицата “Студентски дневник” автоматично. По същия начин можете да регистрирате всяка актуализация или да изтриете извлечения.
Предотвратяване на нежелани актуализации с тригери:
Използвайки тригери BEFORE UPDATE на таблица, можете да предотвратите изразите за актуализиране на колона въз основа на израз.
Пример
В следния пример ще предотвратим актуализиране на колоната „име на студент“ в таблицата „Студенти“ от всяко изявление за актуализиране:
Стъпка 1) Отидете до директорията „C:\sqlite” и стартирайте sqlite3.exe.
Стъпка 2) Отворете базата данни "УроциSampleDB.db”, като изпълните следната команда:
.open TutorialsSampleDB.db
Стъпка 3) Създайте нов тригер "предотвратиUpdateStudentName"на масата"Студенти”, като изпълните следната команда
CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
SELECT RAISE(ABORT, 'You cannot update studentname');
END;
"ВДИГАНЕ" командата ще предизвика грешка със съобщение за грешка " Не можете да актуализирате името на ученик “ и след това ще предотврати изпълнението на израза за актуализиране.
Сега ще проверим дали тригерът работи добре и предотвратява всяка актуализация за колоната име на студент.
Стъпка 4) Изпълнете следната команда за актуализиране, която ще актуализира името на ученика „крик„да бъда“Jack1".
UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';
Трябва да получите съобщението за грешка, което посочихме на тригера, което казва, че „Не можете да актуализирате името на ученик”, както следва:
Стъпка 5) Изпълнете следната команда, която ще избере списъка с имена на ученици от таблицата на учениците.
SELECT StudentName FROM Students;
Трябва да видите, че името на ученика „Джак“ все още е същото и не се променя:
Oбобщение
Изгледи, индекси и тригери са много мощни инструменти за администриране на SQLite база данни. Можете да проследявате операциите за модифициране на данни, когато се случват на таблица. Можете също да оптимизирате операцията за извличане на данни от базата данни чрез създаване на индекси.