SQLite Триггер, просмотры и индекс с примером
Маркус Аллен
В повседневном использовании SQLite, вам понадобятся некоторые инструменты администрирования вашей базы данных. Вы также можете использовать их, чтобы сделать запросы к базе данных более эффективными за счет создания индексов или более удобными для повторного использования за счет создания представлений.
SQLite Просмотр
Представления очень похожи на таблицы. Но представления — это логические таблицы; они не хранятся физически, как таблицы. Представление состоит из оператора выбора.
Вы можете определить представление для своих сложных запросов и повторно использовать эти запросы в любое время, вызывая представление напрямую, вместо того, чтобы заново переписывать запросы.
Оператор CREATE VIEW
Чтобы создать представление в базе данных, вы можете использовать оператор CREATE VIEW, за которым следует имя представления, а затем поместить после этого нужный запрос.
Пример: В следующем примере мы создадим представление с именем «ВсеСтудентыПросмотреть«в образце базы данных»Учебники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';
Вы должны увидеть вид »ВсеСтудентыПросмотреть» возвращается:
Шаг 5) Теперь наше представление создано, вы можете использовать его как обычную таблицу примерно так:
SELECT * FROM AllStudentsView;
Эта команда запросит представление «AllStudents» и выберет из него все строки, как показано на следующем снимке экрана:
Временные просмотры
Временные представления являются временными для текущего подключения к базе данных, использованного для их создания. Затем, если вы закроете соединение с базой данных, все временные представления будут автоматически удалены. Временные представления создаются с помощью одной из следующих команд:
- СОЗДАТЬ ВРЕМЕННЫЙ ПРОСМОТР или
- СОЗДАТЬ ВРЕМЕННЫЙ ПРОСМОТР.
Временные представления полезны, если вы хотите выполнить некоторые операции в данный момент и не хотите, чтобы это было постоянное представление. Итак, вы просто создаете временное представление, а затем выполняете обработку с использованием этого представления. Later при закрытии соединения с базой данных она автоматически удаляется.
Это критически важно для анализа и выбора наиболее эффективных ключевых слов для улучшения рейтинга вашего сайта.
В следующем примере мы откроем соединение с базой данных, а затем создадим временное представление.
После этого мы закроем это соединение и проверим, существует ли еще временное представление или нет.
Шаг 1) Откройте sqlite3.exe из каталога «C:\sqliteкак объяснялось ранее.
Шаг 2) Открыть соединение с базой данных»УчебникиSampleDB.db» выполнив следующую команду:
.open TutorialsSampleDB.db
Шаг 3) Напишите следующую команду, которая создаст временное представление:ВсеСтудентыTempView"
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) Убедитесь, что временное представление «ВсеСтудентыTempView» создается путем выполнения следующей команды:
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, а также на умножении значения столбца «Количество» и значения столбца «Цена». Таким образом, любой запрос «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 поддерживает триггер FOR EACH ROW, поэтому предопределенные операции в триггере будут выполняться для всех строк, участвующих в действиях, происходящих с таблицей (будь то вставка, удаление или обновление).
SQLite СОЗДАТЬ ТРИГГЕР
Чтобы создать новый TRIGGER, вы можете использовать оператор CREATE TRIGGER следующим образом:
- После CREATE TRIGGER необходимо указать имя триггера.
- После имени триггера необходимо указать, когда именно имя триггера должно быть выполнено. У вас есть три варианта:
- BEFORE – триггер будет выполнен до указанного оператора INSERT, UPDATE или удаления.
- После — триггер будет выполнен после указанного оператора INSERT, UPDATE или удаления.
- ВМЕСТО — оно заменит произошедшее действие, вызвавшее срабатывание триггера, оператором, указанным в TRIGGER. Триггер INSTEAD OF неприменим к таблицам, только к представлениям.
Триггеры будут активированы (запущены) только в зависимости от типа оператора, указанного в команде создания триггера. Например:
- Триггер BEFORE INSERT будет активирован (запущен) перед любым оператором вставки.
- Триггер AFTER UPDATE будет активирован (запущен) после любого оператора обновления и т. д.
Внутри триггера вы можете ссылаться на вновь вставленные значения, используя ключевое слово «new». Кроме того, вы можете ссылаться на удаленные или обновленные значения, используя старое ключевое слово. Как следующее:
- Внутри триггеров INSERT – можно использовать новое ключевое слово.
- Внутри триггеров UPDATE — можно использовать новые и старые ключевые слова.
- Внутри триггеров DELETE – можно использовать старое ключевое слово.
Пример
Далее мы создадим триггер, который будет срабатывать перед добавлением нового учащегося в «Студенты" Таблица.
Он зарегистрирует вновь добавленного студента в таблицу «СтудентыЖурнал» с автоматической отметкой времени текущей даты, когда произошел оператор вставки. Как следующее:
Шаг 1) Перейдите в каталог «C:\sqlite» и запустите sqlite3.exe.
Шаг 2) Откройте базу данных»УчебникиSampleDB.db» выполнив следующую команду:
.open TutorialsSampleDB.db
Шаг 3) создать триггер»ВставкаИнтоСтудентТриггер» Выполнив следующую команду:
CREATE TRIGGER InsertIntoStudentTrigger
BEFORE INSERT ON Students
BEGIN
INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;
Функция «дата-время()» предоставит вам текущую отметку даты и времени, когда произошел оператор вставки. Чтобы мы могли регистрировать транзакцию вставки с автоматическими метками времени, добавляемыми к каждой транзакции.
Команда должна выполниться успешно, и вы не получите никаких результатов:
Курок "ВставкаИнтоСтудентТриггер» будет срабатывать каждый раз, когда вы добавляете нового ученика в таблицу студентов. «новыйКлючевое слово «» относится к значениям, которые будут вставлены. Например, «новый.StudentId» будет идентификатор студента, который будет вставлен.
Теперь мы проверим, как ведет себя триггер, когда мы добавляем нового студента.
Шаг 4) Напишите следующую команду, которая вставит нового студента в таблицу студентов:
INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');
Шаг 5) Напишите следующую команду, которая выберет все строки из «СтудентыЖурнал" стол:
SELECT * FROM StudentsLog;
Вы должны увидеть новую строку, возвращенную для нового студента, которого мы только что вставили:
Эта строка была вставлена триггером перед вставкой нового студента с идентификатором 11.
В этом примере мы использовали триггер « ВставкаИнтоСтудентТриггер ” мы создали, чтобы регистрировать любые транзакции вставки в таблицу “СтудентыЖурнал» автоматически. Таким же образом вы можете регистрировать любое обновление или удаление операторов.
Предотвращение непреднамеренных обновлений с помощью триггеров:
Используя триггеры BEFORE UPDATE в таблице, вы можете запретить операторы обновления столбца на основе выражения.
Пример
В следующем примере мы запретим любому оператору обновления обновлять столбец «studentname» в таблице «Студенты»:
Шаг 1) Перейдите в каталог «C:\sqlite» и запустите sqlite3.exe.
Шаг 2) Откройте базу данных»УчебникиSampleDB.db» выполнив следующую команду:
.open TutorialsSampleDB.db
Шаг 3) Создать новый триггер»предотвратить обновлениестудентнаме" на столе "Студенты» выполнив следующую команду
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 база данных. Вы можете отслеживать операции изменения данных, когда они происходят в таблице. Вы также можете оптимизировать операцию извлечения данных из базы данных, создав индексы.
