Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/chast-11
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

-- Зачем нужны индексы?
/*


Для начала скажем, что SQL индексы являются объектами базы данных, как, например, таблица или представление, а это
означает, что имя индекса должно быть уникальным.

Типичным примером индекса в базе данных является первичный ключ (столбец PEIMARY KEY).

Поскольки SQL индекс - это объект базы данных, то к нему можно применять некоторые SQL команды, например,
мы можем создавать и удалять индексы.

В базах данных SQLite к индексам нельзя применить команды манипуляции данными,
хотя мы с легкостью можем применять эти команды к таблицам, столбцы которых являются индексами.

Основное назначение индексов в базе данных заключается в том, чтобы ускорить операцию выборки данных из базы данных.

Индексы очень ускоряют выполнения команд SELECT за счет того,
что реализованы они, на самом деле, при помощи быстрого алгоритма поиска информации,
одним из самых популярных таких алгоритмов является алгортим Б-дерева, но об этом мы
поговорим несколько позже. Но, поскольку индексы ускоряют выборку, вернее потому,
но, если где-то что-то прибыло, то обязательно где-то что-то убудет, в природе по-другому не бывает,
индексы ускоряя выборку данных, замедляют следующие SQL операции:

- добавление строк в таблицы;
- удаление данных из таблиц;
- и модификацию данных.

Если алгоритм индексов реализован в виде B-tree (чаще все именно этот алгоритм), то замеделние других
операций манипуляции данными происходит из-за того, что данный алгоритм требует, чтобы данные были
упорядочены, а само дерево сбалансированным, поэтому СУБД при выполнении этих операций приходится
поддерживать дерево в актуальном/сбалансированном состояние, а на это требуются вычислительные мощности.


Если говорить просто, то при создании индекса мы создаем еще одну таблицу, в которой хранятся значения
индексируемого столбца в упорядоченном виде и при этом СУБД вместо того, чтобы перебирать данные в исходной
таблице, пользуются таблицей индексов, в которой хранятся ссылки на исходные таблицы, за счет этого происходит
уменьшение количества операций по перебору и сравнению значений с искомым.

*/

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Ранее мы упоминали о том, что транзакции должны быть изолированы, а также мы сказали, что полная изоляция транзакции – очень дорогая операция, поэтому было принято разделение по степени изолированности. Чем выше уровень изолированности транзакции, тем выше точность и согласованность данных, но минус в том, что может уменьшится число параллельных транзакций, которые выполняются СУБД.

Также при выполнении параллельных транзакций в базе данных могут возникать некоторые проблемы.

1. Потерянное обновление (lost update). Если две или более, запущенных параллельно транзакции пытаются модифицировать одни и те же данные, то все вносимые изменения, кроме первой транзакции, будут потеряны.
2. Неповторяющееся чтение (non-repeatable read). При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.
3. Грязное чтение (dirty read). Данная проблема возникает в том случае, когда вы делаете выборку данных, которые были изменены транзакцией, но в дальнейшем произойдет откат транзакции и эти изменения не подтвердятся.
4. Фантомное чтение (phantom reads). Представим, что у нас запущено две транзакции, первая лишь читает данные из базы данных, вторая манипулирует данными, например: добавляет строки, удаляет данные или их модифицирует. Допустим, что в первой транзакции условия выборки данных всегда одинаковые, но результаты могут оказаться разными, так как вторая транзакция изменяет данные в таблицах.

Справиться с подобными проблемами при выполнении параллельных транзакций помогают блокировки и изолированность транзакций.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

В информатике есть такое понятие как уровни изолированности транзакций, которые помогаю справляться с вышеперечисленными проблемами. Стандарт SQL-92 выделяет четыре уровня изолированности транзакций в реляционных базах данных. Чем выше уровень изолированности, тем меньше проблем может возникнут, а это значит, что ваша база данных более надежна.

Заметим, что каждый последующий уровень изолированности включает требования предыдущего уровня и добавляет некоторые свои требования (нечто похожее мы наблюдали, когда говорили про нормальные формы в базе данных: третья нормальная форма включает в себя требования второй нормальной формы и первой нормальной формы, плюс накладывает свои требования на отношение).

1. Read uncommitted (чтение незафиксированных данных). Уровень изолированности Read uncommitted или чтение незафиксированных данных – это самый низший уровень изолированности транзакций. Данный уровень справляется с проблемами потерянного обновления. Обычно этот уровень реализуется путем блокировки таблиц для других транзакций. Например, выполняется первая транзакция и, пока она выполняется, ни одна другая транзакция не может изменять данные в этой таблице, а может их только читать. При этом, как только завершится первая транзакция, таблица станет доступна для второй транзакции, которая может изменять данные. Таким образом при уровне изоляции Read uncommitted транзакции будут выполняться последовательно и ни одно изменение потеряно не будет. Но в то же самое время любая другая транзакция может выполнять чтение данных из этой таблицы (даже тех данных, которые еще не были подтверждены командой COMMIT).

2. Read committed (чтение фиксированных данных). Данный уровень изолированности транзакций решает проблему грязного чтения данных. Но уровень изолированности Read committed или чтение фиксированных данных может быть реализован двумя способами.

2.1Первый способ заключается в том, что читающая транзакция блокирует считываемые данные и при этом транзакция, выполняющая какие-то изменения не может их совершить до тех пор, пока читающая транзакция не будет завершена. Если же пишущая транзакция началась раньше, то она блокирует данные для читающих транзакций до тех пор, пока изменения не будут подтверждены. Этот способ получил название блокирование или блокирование читаемых и изменяемых данных.

2.2 Второй подход или второй способ изоляции основан на версионности данных. СУБД создает новую версию строки для транзакции при каждом изменении данных строки. С этой новой версией продолжает работать та транзакция, которая ее создала, но любая другая транзакция видит строку в том, виде, в котором она была зафиксирована. Этот способ гораздо быстрее первого, но требует гораздо большего объема оперативной памяти, так как «новые версии строк» хранятся в оперативной памяти до тех пор, пока они не будут подтверждены.
3. Repeatable read (повторяемость чтения). Уровень изоляции транзакции Repeatable read или повторяемость чтения делает так, что транзакция, изменившая данные, не видит своих изменений, до тех пор, пока они не будут подтверждены. При этом никакая другая транзакция не сможет вносить изменения, пока не будет завершена первая транзакция.

4. Serializable (упорядочиваемость). Уровень изоляции транзакции Serializable или упорядочиваемость – самый высокий уровень изолированности транзакций, описанный в стандарте SQL-92. На этом уровне транзакции не подвержены проблемам фантомного чтения, так как уровень Serializable (упорядочиваемость) делает их, можно сказать, последовательными. На данном уровне транзакции максимально изолированы друг от друга.

Обратите внимание: четыре уровня изолированности транзакций описаны в стандарте SQL-92, каждая СУБД поддерживает разное количество уровней изолированности транзакций (какие-то имеют большее количество уровней, какие-то меньшее), а также в каждой СУБД реализован свой подход к изоляции транзакций. В дальнейшем мы несколько более подробно поговорим о том, как реализованы транзакции в базах данных под управлением библиотеки SQLite.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

-- Начать транзакцию

BEGIN TRANSACTION;
DEFERRED
IMMEDIATE
EXCLUSIVE

-- DEFERRED – данный режим блокировки является режимом по умолчанию в SQLite. В режиме DEFERRED SQLite начинает блокировать таблицы только после того, как будет начато выполнение какой-либо команды, при этом другие транзакции могут читать данные из таблицы, но не могут их изменять.

-- IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.

-- EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.



-- Подтверждение транзакций

COMMIT TRANSACTION;
END TRANSACTION;

-- Подтвердить изменения, внесенные транзакцией, позволяет ключевая фраза COMMIT TRANSACTION.

-- Откатить транзакцию

-- Откатить транзакции в базах данных под управлением SQLite позволяет ключевое слово ROLLBACK.

ROLLBACK TRANSACTION;

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

SELECT * FROM City LIMIT 10;

-- Создаем транзакцию с именем при помощи команды SAVEPOINT
SAVEPOINT  transact1;
-- Посмотрим первых 10 записей из таблицы City
SELECT * FROM City LIMIT 10;
-- Удалим одну запись
DELETE FROM city WHERE  id = 2;
SELECT * FROM City LIMIT 10;



-- Из другого терминала:
-- Посмотрим на первых 10 записей
SELECT * FROM city LIMIT 10;
-- Пока вы не выполните команду RELEASE SAVEPOINT, строка не будет удалена

RELEASE transact1;

-- Посмотрим на первых 10 записей после подтверждения транзакции
SELECT * FROM city LIMIT 10;









--Начинаем транзакцию с именем transact1
SAVEPOINT  transact1;

-- Выберем первых 10 записей из таблицы City
SELECT * FROM city LIMIT 10;

-- Удаляем третью строку из таблицы city
DELETE FROM city WHERE  id = 3;

-- Посмотрим на первых 10 записей
SELECT * FROM city LIMIT 10;

-- Пока вы не выполните команду ROLLBACK, транзакция не будет завершена, а вы будете видеть все изменения
ROLLBACK;

-- Посмотрим на первых 10 записей после подтверждения транзакции, вы увидите
SELECT * FROM city LIMIT 10;


















--Начинаем транзакцию с именем transact1

SAVEPOINT  transact1;

-- Выберем первых 10 записей из таблицы City
SELECT * FROM city LIMIT 10;

-- Удаляем третью строку из таблицы city
DELETE FROM city WHERE  id = 3;

-- Посмотрим на первых 10 записей и увидим
SELECT * FROM city LIMIT 10;

-- Пока вы не выполните команду ROLLBACK TRANSACTION TO SAVEPOINT,
-- отката изменений не произойдет, и вы будете видеть все изменения

ROLLBACK TRANSACTION TO SAVEPOINT transact1;

-- Посмотрим на первых 10 записей
SELECT * FROM city LIMIT 10;

-- Хочу обратить ваше внимание на то, что последний запрос SELECT будет выполняться в рамках транзакции transact1, так как мы сделали откат до метки transact1, то есть отменили все запросы, начиная с SAVEPOINT transact1, но транзакцию не завершили.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

sqlite3 block.db3

-- Создадим таблицу и наполним ее
CREATE TABLE table1(a,b,c,d);
INSERT INTO table1 VALUES (1,2,3,4);
INSERT INTO table1 VALUES (5,6,7,8);
INSERT INTO table1 VALUES (9,10,11,12);
INSERT INTO table1 VALUES (13,14,15,16);
INSERT INTO table1 VALUES (17,18,19,20);
INSERT INTO table1 VALUES (21,22,23,24);
INSERT INTO table1 VALUES (25,26,27,28);
SELECT * FROM table1;


-- для примера запустим второй терминал и откроем нашу БД

sqlite3

.open block.db3

-- DEFERRED – данный режим блокировки является режимом по умолчанию в SQLite. В режиме DEFERRED SQLite начинает блокировать таблицы только после того, как будет начато выполнение какой-либо команды, при этом другие транзакции могут читать данные из таблицы, но не могут их изменять.

-- терминал 1

BEGIN DEFERRED TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
INSERT INTO table1 VALUES (29,30,31,32);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);


----------------------------------------------------------------
-- IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.

-- терминал 1
BEGIN IMMEDIATE TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (37,38,39,40);

-- терминал 1
INSERT INTO table1 VALUES (41,42,43,44);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (45,46,47,49);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (50,51,51,52);




-------------------------------------------------------------------

-- EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.
-- терминал 1
BEGIN EXCLUSIVE TRANSACTION;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

-- терминал 1
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

sqlite3 block.db3

-- Создадим таблицу и наполним ее
CREATE TABLE table1(a,b,c,d);
INSERT INTO table1 VALUES (1,2,3,4);
INSERT INTO table1 VALUES (5,6,7,8);
INSERT INTO table1 VALUES (9,10,11,12);
INSERT INTO table1 VALUES (13,14,15,16);
INSERT INTO table1 VALUES (17,18,19,20);
INSERT INTO table1 VALUES (21,22,23,24);
INSERT INTO table1 VALUES (25,26,27,28);
SELECT * FROM table1;


-- для примера запустим второй терминал и откроем нашу БД

sqlite3

.open block.db3

-- DEFERRED – данный режим блокировки является режимом по умолчанию в SQLite. В режиме DEFERRED SQLite начинает блокировать таблицы только после того, как будет начато выполнение какой-либо команды, при этом другие транзакции могут читать данные из таблицы, но не могут их изменять.

-- терминал 1

BEGIN DEFERRED TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
INSERT INTO table1 VALUES (29,30,31,32);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (33,34,35,36);


----------------------------------------------------------------
-- IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.

-- терминал 1
BEGIN IMMEDIATE TRANSACTION;


-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (37,38,39,40);

-- терминал 1
INSERT INTO table1 VALUES (41,42,43,44);
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (45,46,47,49);

-- терминал 1
COMMIT;
SELECT * FROM table1;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (50,51,51,52);




-------------------------------------------------------------------

-- EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.
-- терминал 1
BEGIN EXCLUSIVE TRANSACTION;

-- терминал 2
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

-- терминал 1
SELECT * FROM table1;
INSERT INTO table1 VALUES (53,54,55,56);

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

/*
1. Что такое триггер и для чего нужны триггеры в реляционных базах данных?
2. Виды SQL триггеров в базах данных SQLite.
3. Удаление и создание триггеров: SQL команды DROP TRIGGER и CREATE TRIGGER.
4. SQL событие BEFORE или выполнение триггера перед выполнением SQL запроса.
5. SQL событие AFTER или выполнение триггера после выполнения SQL запроса.
6. INSERT и DELETE триггеры в базах данных SQLite.
7. UPDATE триггеры или триггеры модификации в базах данных SQLite.
8. Условие срабатывания SQL триггера: уточняющее выражение WHEN в базах данных SQLite.
9. Важные особенности использования триггеров в базах данных SQLite.
10. INSTEAD OF триггеры в базах данных SQLite и редактирование VIEW.
11. Устранение конфликтов в базах данных при помощи SQL функции RAIS и триггеров.
*/

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Триггер – это особая разновидность хранимых процедур в базе данных. Особенность триггеров заключается в том, что SQL код, написанные в теле триггера, будет исполнен после того, как в базе данных произойдет какое-либо событие. События в базах данных происходят в результате выполнения DML команд или команд манипуляции данными. Если вы помните, то к командам манипуляции данными относятся: UPDATE, INSERT, DELETE и SELECT.

Команду SELECT мы не берем в расчет из-за того, что она никак не изменяет данные в базе данных, а лишь делает выборку данных. Основное назначение триггеров заключается в обеспечение целостности данных в базе данных, еще при помощи триггеров в SQL можно реализовать довольно-таки сложную бизнес-логику.

SQL код, написанный в теле триггера, будет выполнен автоматически, как только в базе данных произойдет одно из трех, указанных выше событий. Также мы можем задать самостоятельно события, по которым триггер будет срабатывать, а также SQL таблицу, для которой триггер будет срабатывать.

Для любой СУБД триггер – это в первую очередь объект базы данных, поэтому имя триггера должно быть уникальным во всей базе данных, SQLite в этом плане не исключение. У триггеров в SQL есть момент запуска. Момент запуска триггера можно разделить на два вида: BEFORE и AFTER. Момент запуска триггера AFTER говорит о том, что триггер будет запущен после выполнения какого-либо события в базе данных. Соответственно, момент запуска триггера BEFORE говорит о том, что триггер будет запущен до выполнения события в базе данных.

SQLite позволяет только читать данные из VIEW, в отличии, скажем, от MySQL или Oracle. Триггеры могут быть созданы для представлений с целью расширить набор операций манипуляции данными того или иного представления. Такой вид триггеров получил название INSTEAD OF триггер.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Итак, триггеры можно разделить на три вида по их применению:

триггер BEFORE, который срабатывает до выполнения какого-либо события в базе данных;
триггер AFTER, который срабатывает после выполнения события в базе данных;
INSTEAD OF триггер, который используется для манипуляции данными представлений.


Так же мы можем разделить триггеры по типам SQL команд:

DELETE триггер. Триггер DELETE запускается при попытке удаления данных/строк из таблицы базы данных;
UPDATE триггер. Триггер UPDATE будет запущен при попытке обновления/модификации данных в таблице базы данных;
INSERT триггер. Триггер INSERT будет запущен в том случае, если вы попытаетесь вставить/добавить строку в таблицу базы данных.
В некоторых СУБД триггер – это довольно мощное и полезное явление. Будьте аккуратны, используя триггеры, не используйте триггеры в рабочих базах данных. Перед тем, как реализовать триггер, создайте тестовую базу данных и посмотрите, что в итоге получится. Неправильный составленный триггер может навредить вашему проекту, повредив часть данных или удалив данные из базы данных.

Давайте перечислим самые распространенные функции триггеров:

Функция журнализации. Часто при помощи триггеров разработчики создают таблицы-журналы, в которых фиксируются различные изменения в базе данных. Обычно журналы создаются для фиксации изменений, которые вносят различные пользователи базы данных, таким образом можно отследить какой пользователь внес то или иное изменение в ту или иную таблицу базы данных.
Функция согласования данных. Мы уже упоминали, что триггеры используются для обеспечения целостности данных в базе данных. Мы можем связать триггер с той или иной SQL командой, таким образом, чтобы триггер проверял связанные таблицы на согласованность данных, тем самым мы обезопасим данные.
Функция очистки данных. Данная функция является подмножество функции из второго пункта. Например, вы выполняете каскадное удаление данных, в этом случае данные удаляются из таблиц, связанных ограничением внешнего ключа, но что если данные об одном объекте хранятся в несвязанных таблицах? В этом случае нас спасают триггеры. То же самое можно сказать и про операции каскадной модификации данных.
Другие функции триггеров. К сожалению, в SQLite3 нет хранимых процедур за исключением триггеров. В тех СУБД, у которых реализованы хранимые процедуры, мы можем создавать собственные процедуры в теле триггера, которые могут выполнять операции, не связанные с изменением данных.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-13-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

CREATE TRIGGER trigger_name -- говорим СУБД о том, что хотим создать триггер
BEFORE -- триггер будет срабатывать до выполнения SQL запроса
INSERT ON table_name -- а конкретно до выполнения INSERT в таблицу table_name
BEGIN -- здесь начинается тело триггера
-- здесь хранится SQL код триггера
-- его СУБД будет выполнять, когда будет происходить
-- событие, по которому срабатывает триггер (оно описано выше)
-- SQL запросы в теле триггера разделяются точкой с запятой
INSERT... ;
INSERT... ;
DELETE... ;
END; -- а здесь тело заканчивается


CREATE TRIGGER trigger_name -- говорим СУБД о том, что хотим создать триггер
AFTER -- триггер будет срабатывать после выполнения SQL запроса
DELETE ON table_name -- а конкретно после выполнения DELETE из таблицы table_name
BEGIN -- здесь начинается тело триггера
-- здесь хранится SQL код триггера
-- его СУБД будет выполнять, когда будет происходить
-- событие, по которому срабатывает триггер (оно описано выше)
-- SQL запросы в теле триггера разделяются точкой с запятой
INSERT... ;
INSERT... ;
DELETE... ;
END; -- а здесь тело заканчивается

Мы рассмотрели внутреннее объединение таблиц, реализуемое с помощью INNER JOIN, мы рассмотрели внешнее объединение таблиц, реализуемое в базах данных SQLite при помощи LEFT JOIN, теперь перейдем к перекрестному объединению таблиц, которое реализуется при помощи ключевой фразы CROSS JOIN.

Перекрестное объединение таблиц или CROSS JOIN является симметричной операцией, как и INNER JOIN, а это означает, что порядок написания таблиц в запросе не имеет значения, результирующая таблица будет одинакова.

Перекрестное объединение таблиц в математике представляет собой декартово произведение, а это означает, что в результате выполнения операции CROSS JOIN SQLite будет соединять каждую строку первой таблицы с каждой строкой второй таблицы и в результате мы получим таблицу, в которой будут все сочетания строк, которые возможны.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/obedinen
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Вы можете использовать для всех запросов на объединение таблиц ключевое слово NATURAL: NATURAL LEFT JOIN, NATURAL INNER JOIN и NATURAL CROSS JOIN. Принцип действия NATURAL JOIN. NATURAL JOIN автоматически осуществляет проверку на равенство между значениями каждого столбца, которые есть в обеих таблицах.

Вместе с ключевым словом NATURAL в базах данных SQLite вы не можете использовать предикаты ON и USING, если вы их будете использовать, то SQLite выдаст вам ошибку.

Проще говоря, СУБД сама определяет столбцы, по которым происходит объединение таблиц и делает она это очень просто: объединение происходит по столбцам с одинаковыми именами.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/obedinen
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Операция JOIN очень мощная, но довольно-таки ресурсоемкая штука, без объединения таблиц нам бы не было смысла нормализовывать отношения до второй нормальной формы или до третьей нормальной, конечно, мы немного утрируем, но операции объединения таблиц очень облегчают нам работу с базами данных.

Мы уже видели на примерах INNER JOIN, CROSS JOIN и LEFT JOIN, как объединяются две таблицы, а теперь давайте посмотрим: как с помощью JOIN объединить три и более таблиц в одну. На самом деле всё очень просто, сперва пример объединения трех SQL таблиц в одну с помощью INNER JOIN.

-- можно комбинировать вместе INNER JOIN, LEFTT JOIN и CROSS JOIN это обусловлено тем, что все действия по объединению нескольких таблиц делаются библиотекой SQLite3 по порядку (можно представить, что JOIN – это операторы сложения).
-- сначала объединятся первых две таблицы и получится результирующая, после чего результирующая таблица будет объединена с третьей таблицей и так можно до бесконечности, главное, чтобы ваши таблицы были связаны значениями.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/obedinen
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

1. Для начала мы рассмотрели простые варианты запроса SELECT и разобрались с тем, что команда SELECT позволяет выводить данные на экран или делать выборку данных из базы данных, команда SELECT всегда нам возвращает таблицу.

2. Далее мы разобрались с уточняющей фразой (клаузлуой) WHERE, которая была введена в язык SQL для того, чтобы выполнять фильтрацию данных: с помощью WHERE мы можем задавать условия, по которым СУБД определяет выводить ей строку или нет.

3. Затем мы познакомились с группировкой данных и агрегатами или функциями агрегации. Группировка в SQL реализуется при помощи клазулы GROUP BY.

4. Далее мы узнали о том, как сделать дополнительную фильтрацию данных или как задать условие выборки данных после группировки, реализуется данная операция в SQL при помощи клазулы HAVING. HAVING работает точно так же как и WHERE, но используется после группировки.

5. Также мы узнали о запросе SELECT ORDER BY, который нам позволяет выполнять сортировку данных, сортировка выборки может происходить как в прямом пордяке, так и в обратном. Для опреления порядка используются модификаторы ASC и DESC. Также стоит заметить, что в базах данных SQLite запрос SELECT ORDER BY позволяет указать сортирующую последовательность при помощи ключевого слова COLLATE.

6. Следующим запросом был пример команды SELECT ORDER BY LIMIT. Ключевое слово LIMIT в SQL позволяет указать количество строк, которое будет выведено на экран. Также LIMIT имеет модификатор OFFSET, который позволяет сперва пропустить N-ое количество строк, а затем вывести на экран M-ое количество строк.

7. Также мы разобрались с SQL операторами AND и OR, которые позволяют усложнять и объединять условия выборки данных.

8. Еще один SQL оператор, который мы рассмотрели - это BETWEEN. Данный оператор вместе с командой SELECT позволяет указать диапазон значений, по которому будет осуществлена выборка данных из базы данных.

9. SQL оператор LIKE, который очень часто используется вместе с командой SELECT позволяет организовать поиск по шаблону или поиск подстроки в базе данных. К сожалению, в базах данных SQLite не реализованы регулярные выражения, поэтому шаблон лайк ограничен лишь двумя символами: % и _.

10. Также мы рассмотели как избавиться от дублирующихся / повторяющихся строк при помощи SQL запроса SELECT DISTINCT.

11. Еще мы поговорили как осуществить поиск в базе данных по опередленному набору значений, за это действие отвечает SQL оператор IN.

12. Далее мы посмотрели как составлять SQL подзапросы SELECT и узнали о том, что подзапросы могут быть связанными и обязательно должны быть написаны в круглых скобках. Также мы выяснили, что подзапросы в SQL очень часто составляются вместе с операторами IN и EXISTS, хотя никто не запрещает использовать подзапросы с другими операторами и даже вместо имени столбца или имени табилцы, из которой будет делаться выборка данных.

13. А здесь мы разобрались с тем как объединить результаты двух и более SQL запросов SELECT при помощи ключевого слова UNION.

14. Затем мы поговорили про сравнение результатов двух SQL запросов SELECT, познакомились с диаграмми Эйлера-Венна и узнали о ключевых словах INTERSECT и EXCEPT.

15. После чего мы уделили отдельное внимание ключевому слову INTERSECT, которое в SQL реализует операцию пересечения строк, а увидив диаграмму этого запроса, мы поняли, что такой запрос является симметричным.

16. Тут мы узнали про то как получить разность строк в базах данных SQLite при помощи ключевого слова EXCEPT и посмотрели диаграмму такого запроса для того, чтобы понять, что он несимметричный.

17. Далее мы поговорили о разновидностях запроса SELECT JOIN и узнали какими образом в SQL и в базах данных происходит объединение таблиц.

18. Поговорили про внутреннее объединение таблиц SELECT INNER JOIN и узнали о его особенностях.

19. Разобрались с внешними объединением таблиц LEFT OUTER JOIN.

20. Посмотрели на перекрестное объединение SQL таблиц CROSS JOIN.

21. Увидили особенности натурального объединения таблиц SELECT NATURAL JOIN.

22. И в завершении мы поговорили о том как объединить три и более таблицы в базах данных SQLite.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/sql-zapr
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-14- блога в ВК: https://vk.com/zametkinapolyahТематическая группа в ВК: https://vk.com/zametki_bdПомощь проекту:WMR: R288272666982WMZ: Z293550531456Яндекс.Деньги: 410011531129223Прежде чем переходить к работе с VIEW (представлением, вьюхой, просмотром), давайте разберемся что же такое VIEW в SQL и наших базах данных и чего в ней такого хорошего, почему рзаработчики баз данных любят их использовать? На мой взгляд в Вики определение VIEW не совсем корректное, поскольку VIEW - это не виртуальная таблица. Автор Вики взял эту информацию из документации MySQL (в ней в одном единственном месте написано:A view acts as a virtual table), в документациях других СУБД и в стандартах SQL слова VIEW и VIRTUAL TABLE в принципе близко не стоят (во всяком случае при беглом просмотре), кстати, многие сайты транслируют информацию о том, что представление в базе данных - это виртуальная таблица. К тому же, в базах данных SQLite есть специальные команды для создания виртуальных таблиц, но об этом позже. Мы разобрались с тем, чем VIEW не является. Давайте посмотрим на то, чем VIEW все-таки является. На самом деле, VIEW - это хранимый запрос в базе данных с именем. VIEW - это такой же объект базы данных, как и таблица, у представления должно быть уникальное имя во всей БД, но хранится VIEW в базе данных в виде SQL запроса. Когда мы создали представление, мы можем работать с ним как с обычной таблицей, многие СУБД позволяют модифицировать и изменять данные VIEW, а вот SQLite дает возможность только просматривать содержимое VIEW (хотя эта проблема решается при помощи триггеров). Но, например, в базах данных MySQL, вы не сможете использовать команды UPDATE, INSERT, DELETE, если SQL запрос создающий VIEW содержит: 1. Функции агрегации. 2. Ключевое слово LIMIT. 3. Клаузулу GROUP BY, позволяющую сделать группировку данных. 4. Клаузула HAVING, фильтрующая данные после группировки. 5. Операторы UNION и UNION ALL, объединяющие результаты двух запросов. 6. Любой подзапрос SELECT, даже подзапрос JOIN, объединяющий две таблицы. 7. Если запрос содержит пользовательские переменные. 8. Если нет базовой таблицы. 9. Поэтому рекомендую вам сперва ознакомиться с документацией той или иной СУБД, прежде чем начать создавать представления в базе данных.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-14-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Первое и очевидное применение VIEW в базах данных заключается в том, чтобы упростить запросы на выборку данных. Ведь нам же не хочется писать полотно SELECT, которое объединяет три-четыре таблицы каждый раз, а потом еще задавать какие-нибудь условия выборки данных клаузулой WHERE? Итак, первое, для чего мы можем использовать представление – это для упрощения запросов выборки данных.

Второй пункт можно назвать безопасность. Во-первых, при помощи VIEW можно скрыть бизнес-логику и архитектуру базы данных от прикладных приложений, сделав так, что программа будет обращаться не к таблицам базы данных, а к представлениям. Во-вторых, так вы избавитесь от некоторых видов SQL-инъекций, плюсом к этому, особо талантливые программисты лишаться «чудесной возможности» конкатенировать SQL запросы (как только вы увидите, что программист конкатенирует SQL запрос, можете зарядить ему в щи с вертушки и прокричать: я угорел по базам данных, а ты не знаешь даже таких простых вещей), тем самым вы еще уменьшите вариативность атак на вашу базу данных.

Третий вариант применения VIEW сводится к обновлениям. Вы редко можете встретить базу данных без прикладного приложения. Мир не стоит на месте, всё летит, всё развивается, компании растут и объединяются, у клиентов появляются всё новые потребности и рано или поздно старые приложения становятся неудобными и возникает потребность в их модификации. Мы уже говорили, что VIEW позволяют скрывать бизнес-логику базы данных, но не всегда, создавая базу данных, вы создаете представления. Поэтому если у вас возникла потребность в обновлении программного кода, работающего с базой данных, вы можете создать новую структуру базы данных в виде представлений, с которой будет работать новый программный код, тем самым вы разделите схему хранения данных и схему представления данных. Если потребности в разделении схем нет, то в дальнейшем вы можете отказаться от использования VIEW и вернуться к таблицам, после того, как код приложения будет обновлен.

Пожалуй, это три самых важных аспекта работы с базами данных, для которых можно и даже нужно использовать представления.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-14-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Синтаксис создания VIEW в базах данных очень простой и чем-то напомниает процесс создания SQL таблицы. Создание VIEW начинается с той же команды, что и создание таблицы в базе данных: с команды CREATE. Это обусловлено тем, что VIEW – это такой же объект базы данных, как и таблица. Далее мы указываем, что хотим создать представление при помощи ключевого слова VIEW. Представление может быть временным, поэтому после ключевого слова CREATE вы можете использовать слово TEMP или TEMPORARY. Если вы не уверены, что создаете представление с уникальным именем и не хотите возникновения ошибок при создании VIEW в базе данных, то можете использовать ключевую фразу IF NOT EXIST (кстати, оператор EXISTS может быть использован для создания подзапроса SELECT). Далее вам необходимо указать имя представления, которое должно быть уникальным, в качестве имени можно использовать квалификатор, в том случае, если вы работаете с несколькими базами данных и хотите быть уверенным в том, что создаете VIEW для нужной базы данных.

После имени представления идет ключевое слово AS и запрос SELECT, который как раз-таки и будет храниться в файле базы данных SQLite и к которому SQLite будет обращаться по тому имени, которое вы указали при создании VIEW.

Рассмотрим SQL синтаксис удаления VIEW из базы данных под управлением SQLite3.

Хоть обычное представление, хоть временное, удаляются из базы данных под управлением SQLite одинаково: ключевое слово DROP, за которым следует VIEW, говорит SQLite о том, что вы хотите удалить из базы данных не просто объект, а представление. Далее следует конструкция IF EXISTS, которая осуществляет проверку наличия представления в базе данных, чтобы SQLite не возвращала ошибки в том случае, если представление, которое вы хотите удалить, уже удалено. После чего идет имя представления или квалификатор.

Отметим, что для представлений в SQLite команда ALTER не реализована. Если вам нужно изменить структуру VIEW, то вам нужно удалить старое представление, а затем создать новой и с новой структурой.

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

/*
1. Что такое транзакции и зачем они нужны в SQL и БД.
2. Четыре свойства транзакций в базах данных. Требования ACID в SQL.
3. Проблемы, которые могут возникать при выполнении параллельных транзакций в SQL базах данных.
4. Уровни изолированности транзакций в реляционных базах данных.
5. SQL команды BEGIN, COMMIT и ROLLBACK: начало, подтвреждение и откат транзакций.
6. SQL команды RELEASE и SAVEPOINT: транзакции с именем, вложенные транзакции и контрольные точки.
7. Режимы блокировки транзакций. Как выполняются транзакции в базах данных SQLite.
8. Как ускорить SQL команду INSERT. Скорость выполнения SQL команд.
*/

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-m...
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/tema-15-
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Давайте разберемся что такое транзакция в SQL и попробуем поработать с транзакциями в базах данных SQLite. Некоторые говорят, что транзакция - это объект в базе данных, как например, таблица или представление. Другие говорят, нет, транзакция - это процесс, происходящий в базе данных.

Я ближе склонен ко второму варианту, хотя можно сказать, что транзакция - это объект в базе данных, который живет ровно то время, которое длится процесс.

Транзакция – это неделимый процесс, который включает в себя группу последовательных операций (этих операций может быть очень много) над данными в базе данных. Операции в транзакции могут быть либо выполнены целиком и полностью все, либо не выполнены вообще.

При этом соблюдается целостность данных в базе данных, а транзакция выполняется вне зависимости и параллельно от других транзакций. В качестве жизненного примера транзакций в базе данных можно описать следующую ситуацию. Например, вы оплачиваете покупку в магазине, это неделимое действие, которое условно можно разбить на несколько операций:

1. Кассир пробивает товар.
2. Вы даете ему карточку.
3. Кассир вставляет карточку в картоприемник.
4. Вы вводите пин-код.
5. Происходит перечисление денег с вашего счета на счет магазина.
6. Кассир отдает вам карточку с чеком.
7. Вы забираете товар и уходите.

У этого действия может быть два окончания: либо вы оплатили товар и забрали его, либо вы его не оплатили, и вам его не отдали. Забрать пол телевизора или половину бутылки с газировкой вы не сможете. Это типичный пример транзакций в SQL.

Транзакции повышают надежность и быстродействие операций в базе данных. С надежностью всё понятно, но за счет чего происходит увеличение быстродействия.

Мы знаем, что СУБД создает нам абстракцию. На самом деле все данные в базе данных – это обычный файл, лежащий на жестком диске, а СУБД представляет нам этот файл в виде базы данных, таблиц и других неестественных для файловой системы компьютера объектов. Когда мы выполняем ту или иную операцию в базе данных, то СУБД создает соединение с файлом на жестком диске, делает какие-то свои внутренние операции, затем выполняет SQL запрос и закрывает соединение с файлом. И, например, в SQLite каждый запрос к базе данных – это маленькая транзакция, состоящая из одной операции (за исключением команды SELECT).

Но, когда мы явно говорим SQLite о том, что хотим запустить транзакцию, она устанавливает соединение с файлом и не закрывает его до тех пор, пока транзакция не будет выполнена. Таким образом мы можем существенно экономить ресурсы, особенно, когда выполняем очень много однотипных операций, например, когда вставляем несколько сотен/тысяч строк в таблицу базы данных.