SQLite Тригер, перегляди та індекс із прикладом
Маркус Аллен
У щоденному вживанні SQLite, вам знадобляться деякі інструменти адміністрування вашої бази даних. Ви також можете використовувати їх, щоб зробити запити до бази даних більш ефективними, створюючи індекси, або більш зручними для повторного використання, створюючи представлення.
SQLite вид
Види дуже схожі на таблиці. Але представлення — це логічні таблиці; вони не зберігаються фізично, як таблиці. Представлення складається з оператора select.
Ви можете визначити подання для своїх складних запитів і повторно використовувати ці запити, коли забажаєте, викликавши подання безпосередньо замість того, щоб переписувати запити знову.
Оператор CREATE VIEW
Щоб створити представлення бази даних, ви можете використати оператор CREATE VIEW, за яким слідує ім’я представлення, а потім розмістити потрібний запит після цього.
приклад: У наступному прикладі ми створимо представлення з назвою «AllStudentsView"у зразку бази даних"ПідручникиSampleDB.db” наступним чином:
Крок 1) Відкрийте «Мій комп’ютер» і перейдіть до наступного каталогу «C:\sqlite”, а потім відкрийте “sqlite3.exe":
Крок 2) Відкрийте базу даних "ПідручникиSampleDB.db” за такою командою:
Крок 3) Нижче наведено базовий синтаксис команди sqlite3 для створення View
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.
- Щоб видалити ПЕРЕГЛЯД, ви можете використати інструкцію “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. Зауважте, що цей індекс називається частковим індекс через речення WHERE, а не вираз, який у ньому використовується. Однак ви можете використовувати вирази зі звичайними індексами.)
- Ви можете використовувати оператор CREATE UNIQUE INDEX замість CREATE INDEX, щоб запобігти повторюваним записам для стовпців і таким чином усі значення для індексованого стовпця будуть унікальними.
- Щоб видалити індекс, скористайтеся командою DROP INDEX, а потім ім’ям індексу, який потрібно видалити.
SQLite Тригер
Введення в SQLite Тригер
Тригери — це автоматичні попередньо визначені операції, які виконуються, коли в таблиці бази даних відбувається певна дія. Можна визначити, що тригер запускається щоразу, коли над таблицею відбувається одна з наступних дій:
- INSERT в таблицю.
- ВИДАЛИТИ рядки з таблиці.
- ОНОВИТИ один зі стовпців таблиці.
SQLite підтримує тригер FOR EACH ROW, так що попередньо визначені операції в тригері виконуватимуться для всіх рядків, залучених до дій у таблиці (незалежно від того, чи це вставка, видалення чи оновлення).
SQLite СТВОРИТИ ТРІГЕР
Щоб створити новий TRIGGER, ви можете використати оператор CREATE TRIGGER наступним чином:
- Після CREATE TRIGGER слід вказати назву тригера.
- Після назви тригера ви повинні вказати, коли саме ім’я тригера має бути виконано. У вас є три варіанти:
- BEFORE – тригер буде виконано перед указаним оператором INSERT, UPDATE або delete.
- Після – тригер буде виконано після вказаного оператора INSERT, UPDATE або видалення.
- ЗАМІСТЬ – це замінить дію, яка запустила тригер, оператором, указаним у TRIGGER. Тригер INSTEAD OF не застосовується до таблиць, лише до представлень.
Тригери будуть активовані (запущені) лише залежно від типу оператора, указаного в команді create trigger. Наприклад:
- Тригер BEFORE INSERT буде активовано (запущено) перед будь-яким оператором вставки.
- Тригер AFTER UPDATE буде активований (спрацьовуватиме) після будь-якого оператора оновлення, … і так далі.
Всередині тригера ви можете посилатися на щойно вставлені значення за допомогою ключового слова «new». Крім того, ви можете посилатися на видалені або оновлені значення за допомогою старого ключового слова. Як наступне:
- Всередині тригерів 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;
Функція "дата, час()" дасть вам позначку поточної дати, коли відбувся оператор вставки. Щоб ми могли реєструвати транзакцію вставки з автоматичними мітками часу, доданими до кожної транзакції.
Команда має бути виконана успішно, але ви не отримаєте результат:
Тригер "InsertIntoStudentTrigger” запускатиметься кожного разу, коли ви вставлятимете нового студента в таблицю студентів. "newКлючове слово стосується значень, які буде вставлено. Наприклад, "new.StudentId” буде ідентифікатор студента, який буде вставлено.
Тепер ми перевіримо, як поводиться тригер, коли ми вставляємо нового студента.
Крок 4) Напишіть таку команду, яка вставить нового студента в таблицю студентів:
INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');
Крок 5) Напишіть наступну команду, яка вибере всі рядки з "Студентський журнал” таблиця:
SELECT * FROM StudentsLog;
Ви повинні побачити новий рядок для нового студента, якого ми щойно вставили:
Цей рядок було вставлено тригером перед вставленням нового студента з ідентифікатором 11.
У цьому прикладі ми використали тригер " InsertIntoStudentTrigger " ми створили, щоб реєструвати будь-які вставні транзакції в таблиці "Студентський журнал» автоматично. Таким же чином ви можете реєструвати будь-які оновлення або видаляти заяви.
Запобігання ненавмисним оновленням за допомогою тригерів:
Використовуючи тригери BEFORE UPDATE у таблиці, ви можете запобігти операторам оновлення для стовпця на основі виразу.
Приклад
У наведеному нижче прикладі ми заборонимо будь-якому оператору оновлення оновлювати стовпець «studentname» у таблиці Students:
Крок 1) Перейдіть до каталогу "C:\sqlite” і запустіть sqlite3.exe.
Крок 2) Відкрийте базу даних "ПідручникиSampleDB.db”, виконавши таку команду:
.open TutorialsSampleDB.db
Крок 3) Створити новий тригер "preventUpdateStudentName" на столі "Студентам”, виконавши таку команду
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;
Ви повинні побачити, що ім’я учня «Джек» залишається незмінним і не змінюється:
Підсумки
Представлення, індекси та тригери є дуже потужними інструментами для адміністрування SQLite база даних. Ви можете відстежувати операції зміни даних, коли вони відбуваються в таблиці. Ви також можете оптимізувати операцію пошуку даних бази даних шляхом створення індексів.
