SQLite Представлення, індекс та тригер із прикладом

У щоденному вживанні SQLite, вам знадобляться деякі інструменти адміністрування вашої бази даних. Ви також можете використовувати їх, щоб зробити запити до бази даних більш ефективними, створюючи індекси, або більш зручними для повторного використання, створюючи представлення.

SQLite вид

Види дуже схожі на таблиці. Але представлення — це логічні таблиці; вони не зберігаються фізично, як таблиці. Представлення складається з оператора select.

Ви можете визначити подання для своїх складних запитів і повторно використовувати ці запити, коли забажаєте, викликавши подання безпосередньо замість того, щоб переписувати запити знову.

Оператор CREATE VIEW

Щоб створити представлення бази даних, ви можете використати оператор CREATE VIEW, за яким слідує ім’я представлення, а потім розмістити потрібний запит після цього.

приклад: У наступному прикладі ми створимо представлення з назвою «AllStudentsView» у зразку бази даних «TutorialsSampleDB.db» наступним чином:

Крок 1) Відкрийте «Мій комп’ютер» та перейдіть до наступного каталогу «C:\sqlite», а потім відкрийте «sqlite3.exe»:

Крок 2) Відкрийте базу даних “TutorialsSampleDB.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) Відкрийте підключення до бази даних «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.
• Щоб видалити ПЕРЕГЛЯД, ви можете використати інструкцію “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. Зауважте, що цей індекс називається частковим індекс через речення WHERE, а не вираз, який у ньому використовується. Однак ви можете використовувати вирази зі звичайними індексами.)

• Ви можете використовувати оператор CREATE UNIQUE INDEX замість CREATE INDEX, щоб запобігти повторюваним записам для стовпців і таким чином усі значення для індексованого стовпця будуть унікальними.
• Щоб видалити індекс, скористайтеся командою DROP INDEX, а потім ім’ям індексу, який потрібно видалити.

Хоча індекси пришвидшують читання, тригери дозволяють SQLite автоматично реагувати щоразу, коли дані змінюються.

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 можна використовувати старе ключове слово.

Приклад

Далі ми створимо тригер, який спрацює перед вставкою нового студента в таблицю «Студенти».

Він зареєструє щойно доданого студента в таблиці «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» спрацьовуватиме щоразу, коли ви додаєте нового студента до таблиці students. Ключове слово «new» стосується значень, які будуть додані. Наприклад, «new.StudentId» буде ідентифікатором студента, який буде додано.

Тепер ми перевіримо, як поводиться тригер, коли ми вставляємо нового студента.

Крок 4) Напишіть таку команду, яка вставить нового студента в таблицю студентів:

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

Крок 5) Напишіть таку команду, яка вибере всі рядки з таблиці “StudentsLog”:

SELECT * FROM StudentsLog;

Ви повинні побачити новий рядок для нового студента, якого ми щойно вставили:

Цей рядок було вставлено тригером перед вставленням нового студента з ідентифікатором 11.

У цьому прикладі ми використали створений нами тригер «InsertIntoStudentTrigger» для автоматичного реєстрування будь-яких транзакцій вставки в таблицю «StudentsLog». Так само можна реєструвати будь-які оператори оновлення або видалення.

Запобігання ненавмисним оновленням за допомогою тригерів:

Використовуючи тригери BEFORE UPDATE у таблиці, ви можете запобігти операторам оновлення для стовпця на основі виразу.

Приклад

У наведеному нижче прикладі ми заборонимо будь-якому оператору оновлення оновлювати стовпець «studentname» у таблиці Students:

Крок 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';

Ви маєте отримати повідомлення про помилку, яке ми вказали в тригері, а саме: «Ви не можете оновити ім’я студента» (You cannot update studentname) наступним чином:

Крок 5) Виконайте наступну команду, яка вибере список імен студентів із таблиці студентів.

SELECT StudentName FROM Students;

Ви повинні побачити, що ім’я учня «Джек» залишається незмінним і не змінюється:

Поширені запитання