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

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

-- Функции агрегации


CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
pro TEXT NOT NULL,
sex TEXT NOT NULL,
sal REAL CHECK (sal (знак больше) 15000)
);


INSERT INTO users (name, pro,sex, sal)
VALUES ('Пупкин Матвей', 'Дантист', 'м', 55000.00);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Cумкин Денис', 'Юрист', 'м', 35040.90);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Иван Иванов', 'Младший юрист', 'м', 16000.00);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Ирина Маркова', 'Бухгалтер', 'ж', 31200.10);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Алина Петрова', 'Менеджер продаж', 'ж', 21200.10);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Любовь Михайлова', 'Секретарь', 'ж', 16200.10);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Инна Сидорова', 'Руководитель отдела обслуживания', 'ж', 66200.10);


-- avg(x)
SELECT avg(sal) FROM users;


-- count(X)
-- count(*)

SELECT count(*) FROM users;
SELECT count(name) FROM users;



-- group_concat(X)
-- group_concat(X,Y)

SELECT group_concat(name) FROM users;
SELECT group_concat(name, '|') FROM users;

-- max(X)
SELECT max(sal) FROM users;


-- min(X)
SELECT min(sal) FROM users;

-- sum(X)
-- total(X)
SELECT sum(sal) FROM users;
SELECT total(sal) FROM users;


-- Основная разница между функциями в том, что total возвращает в качестве результата число с плавающей точкой всегда

Помощь проекту:
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 команды CREATE INDEX и DROP INDEX

-- Давайте посмотрим, как создавать индексы для таблиц базы данных под управлением SQLite
-- Хотя мы уже много раз создавали индексы в базах данных,
-- когда объявляли ограничение первичного ключа для столбца – PRIMARY KEY
-- Но индексы можно создавать явно при помощи команды CREATE INDEX, вот ее синтаксис:

CREATE INDEX IF NOT EXISTS dbname.ixname ON tblname (columnname, columnname2,...) WHERE…



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

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


-- Удаляем индекс мы как и любой другой объект при помощи команды DROP
DROP INDEX IF EXISTS dbname.ixname;


CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
pro TEXT NOT NULL,
sex TEXT NOT NULL,
sal REAL CHECK (sal (знак больше) 15000)
);

CREATE INDEX IF NOT EXISTS iXname ON users (name);
DROP INDEX IF EXISTS ixname;

Помощь проекту:
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

-- Композитные и частичные индексы в базах данных SQLite
/*
Мы упоминали о том, что SQLite позволяет создавать несколько индексов для одной таблицы в базе данных,
давайте разберемся с синтаксисом создания нескольких индексов для одной таблицы, такие индексы еще называют
композитными:
*/

CREATE INDEX ix1_table1 ON table1 (a);


CREATE INDEX ix1_table1 ON table1 (a, b);
/*
Создание нескольких индексов для одной таблицы ничем принципиально не отличается от создания одного
индекса: мы просто перечисляем столбцы в круглых скобках, которые будут проиндексированы.
*/

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

SELECT col1, col2 FROM table1;
SELECT col1, col3, col4 FROM table1;

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




/*
Но SQLite дает возможность создавать индексы не для всех значений столбца (не для всех строк в таблице),
а только частичные индексы. Частичные индексы создаются путем наложения определенных
условий на значения, хранящиеся в столбце при помощи клаузулы WHERE:
*/


col1
1
2
3
4
5
6
null
8
12
null
45
null
null

CREATE INDEX partInd_table1 ON table1 (col1)
WHERE col1 IS NOT NULL;

/*
Мы знаем, что в SQLite значение с типом данных NULL всегда уникально, так как значение с классом данных NULL
не равно даже другому NULL, в данном случае будет создан частичный индекс для всех строк столбца, кроме тех строк,
у которых в столбце column_name хранится значение NULL.
*/

-- Частичные индексы экономят место на вашем сервере в том случае, если индексируемый столбец имеет много значений NULL или в столбце много повторяющихся одинаковых значений (вы просто исключаете эти повторяющиеся значения из индекса,поскольку при огромном количестве одинаковых значений будет все равно происходить "тупой" перебор).

-- Используя частичные индексы в вышеописанных случаях вы ускоряете процесс выборки данных и при этом операции, связанные с изменением данных не так сильно "тормозят".

Рубрика 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

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


-- Использование индексов в базах данных SQLite. Уникальные индексы. Переиндексация таблиц.


-- Можем убедиться в том, что индекс для таблицы table1 действительно существует и в том,
-- что это столбец «а», воспользуемся командой PRAGMA
PRAGMA index_list(users);

-- еще можно так:
.indices
.indices users
SELECT * FROM sqlite_master WHERE type = 'index';


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


CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
pro TEXT NOT NULL,

sex TEXT NOT NULL,
sal REAL CHECK (sal (знак больше) 15000)
);

CREATE UNIQUE INDEX iXname ON users (name);


INSERT INTO users (name, pro,sex, sal)
VALUES ('Пупкин Матвей', 'Дантист', 'м', 55000.00);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Cумкин Денис', 'Юрист', 'м', 35040.90);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Иван Иванов', 'Младший юрист', 'м', 16000.00);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Ирина Маркова', 'Бухгалтер', 'ж', 31200.10);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Алина Петрова', 'Менеджер продаж', 'ж', 21200.10);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Любовь Михайлова', 'Секретарь', 'ж', 16200.10);

INSERT INTO users (name, pro,sex, sal)
VALUES ('Инна Сидорова', 'Руководитель отдела обслуживания', 'ж', 66200.10);
-- дублирующееся значение
INSERT INTO users (name, pro,sex, sal)
VALUES ('Пупкин Матвей ', 'Дантист', 'м', 55000.00);



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

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

REINDEX tblname;
REINDEX table1;
REINDEX table2;
REINDEX table3;
-- допустим и такой вариант
REINDEX indexname1;
REINDEX indexname2;

/*
У нас есть два варианта переиндексации таблиц, если мы указываем имя таблицы, то
SQLite сделает переиндексацию для всех столбцов таблицы, которые указаны, как индекс.
Если мы делаем переиндексацию, указывая имя индекса, то значения последовательность
будет удалена и сформирована заново для конкретного индекса.
*/

Рубрика 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

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


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

Индексы удобны и хороши, но бывают ситуации, когда индексы не стоит использовать, например:

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

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

Если вы часто добавляете новые строки в таблицы базы данных (часто используете команду INSERT), то для таблиц, которым много SQL запросов INSERT не стоит создавать индексы, так как SQLite будет постоянно переформировывать индексные таблицы, из-за чего скорость операции INSERT очень уменьшится.

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

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

Помощь проекту:
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

-- Столбец ROWID или внутренние индексы таблицы в базе данных SQLite

-- У каждой таблицы базы данных SQLite по умолчанию есть внутренний индекс, который называет ROWID. Иногда этот индекс совпадает с первичным ключом таблицы PRIMARY KEY, иногда не совпадает, но ROWID есть у каждой таблицы. Если говорить просто и понятно, то ROWID – это дополнительный столбец, который есть у любой таблицы SQLiteи является индексом для данной таблицы.
-- Помимо того, что ROWID – это индекс, это еще один способ поддержание целостности данных. Давайте разберемся с индексом ROWID в базах данных SQLite. ROWID–это 64-разрядное число, которое однозначно идентифицирую любую строку в базе данных SQLite.

-- Создадим таблицу без индексов и наполним ее данными
CREATE TABLE table1(a, b ,c ,d);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);
INSERT INTO table1 (a, b, c , d)
VALUES (1, 2, 3, 4);

-- посмотрим содержимое таблицы
SELECT*FROM table1;


-- SQLite не показывает столбец ROWID, если не обращаться к нему по имени, давайте изменим наш запрос SELECT

SELECT rowid, a, b, c, d FROM table1;
SELECT rowid, a FROM table1;

-- Чтобы первичный ключ совпадал с ROWID, нужно создать таблицу следующим образом
CREATE TABLE table1(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE table2(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE table3(x INTEGER, y, z, PRIMARY KEY(x DESC));

-- Обратите внимание: если столбец PRIMARY KEY объявить как-нибудь по-другому, то значения PRIMARY KEY и ROWID совпадать не будут.


-- Таблицы WITHOUT ROWID в базах данных SQLite, таблицы без внутренних индексов


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

CREATE TABLE IF NOT EXISTS wordcount(
word TEXT PRIMARY KEY,
cnt INTEGER
) WITHOUT ROWID;


INSERT INTO wordcount (word, cnt)
VALUES ('word1', 2);
INSERT INTO wordcount (word, cnt)
VALUES ('word2', 5);

INSERT INTO wordcount (word, cnt)
VALUES (NULL, 5);

CREATE TABLE IF NOT EXISTS wordcount1(
word TEXT PRIMARY KEY,
cnt INTEGER
);

INSERT INTO wordcount1 (word, cnt)
VALUES (NULL, 5);

-- Теперь у таблицы не будет столбца ROWID. Еще одной особенностью таблиц WITHOUT ROWID является то, что ограничение уровня столбца AUTOINCREMENT работать не будет. Первичный ключ таблицы WITHOUT ROWID ни при каких обстоятельствах не может иметь значение NULL, если вы попробуете добавить NULL в столбец PRIMARY KEY таблицы WITHOUT ROWID, то SQLite выдаст ошибку.

Из-за особенностей работы таблиц WITHOUT ROWID, разработчики SQLiteрекомендуют использовать такие таблицы в тех случаях, когда:

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

Если вам нужно повысить скорость работы таблицы с первичным ключом INTEGER, создавайте таблицу без внутреннего индекса, этим вы ускорите выборку и уменьшите объем базы данных. Если таблицы имеет индекс ROWID и индекс в виде INTEGER PRIMARY KEY, то SQLIte делает перебор двумя циклами: первый цикл идет по столбцу ROWID, второй цикл идет по столбцу INTEGER PRIMARY KEY, хотя значения этих столбцов совпадают.

Если в таблицах хранится небольшое количество строк, то таблицы без индекса (WITHOUT ROWID) будут работать несколько быстрее, чем со столбцом ROWID.

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

Рубрика 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

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

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

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

1. Управление пользователями в базах данных SQLite.
2. Три способа создать базу данных в SQLite.
3. Как удалить базу данных в SQLite.
4. SQL команда ATTACH DATABASE или как подключить базу данных.
5. SQL команда DETACH DATABASE или как отключить базу данных.
6. SQL команда VACUUM: повторная сборка или дефрагментация базы данных SQLite.
7. Как сделать резервную копию базы данных SQLite. Бэкап базы данных SQLite.
8. Импорт баз данных SQLite.

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

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

Рубрика 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 событие BEFORE


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


-- Создадим две таблицы

CREATE TABLE users(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL,
address TEXT NOT NULL,
mydate TEXT NOT NULL
);

CREATE TABLE user_log (
Id_u INTEGER NOT NULL,
u_date TEXT NOT NULL
);

-- Для наполнения второй таблицы мы будем использовать триггер
CREATE TRIGGER my_u_log BEFORE INSERT
ON users
BEGIN
INSERT INTO user_log(id_u, u_date) VALUES (NEW.id, datetime('now'));
END;

-- Проверим работу триггера
INSERT INTO users(name, age, address, mydate)

VALUES ('Пупкин', 27, 'Адрес', datetime('now'));

SELECT * FROM users;
SELECT * FROM user_log;

-- К сожалению, поле date в данном случае не показывает, что вставка данных в таблицу user_log произошла до того, как были вставлены данные в таблицу users. Но этот факт мы можем заметить по значению столбца id_u, которое равно -1, так как SQLite3 просто не знает: какое значение будет в столбце id таблицы users.

Рубрика 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 событие AFTER
-- Давайте теперь изменим наш пример, таблица останется той же, но мы изменим код триггера, только одну его часть: поменяем BEFORE на AFTER, чтобы посмотреть, как сработает триггер после выполнения запроса:


-- Создадим две таблицы

CREATE TABLE users(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL,
address TEXT NOT NULL,
mydate TEXT NOT NULL
);

CREATE TABLE user_log (
Id_u INTEGER NOT NULL,
u_date TEXT NOT NULL
);

CREATE TRIGGER my_u_log AFTER INSERT
ON users
BEGIN
INSERT INTO user_log(id_u, u_date) VALUES (NEW.id, datetime('now'));
END;

-- Сделаем проверку
INSERT INTO users(name, age, address, mydate)
VALUES ('Пупкин', 27, 'Адрес', datetime('now'));
INSERT INTO users(name, age, address, mydate)
VALUES ('Сумкин', 17, 'Адрес2', datetime('now'));

SELECT * FROM users;
SELECT * FROM user_log;

-- Теперь идентификаторы записываются корректно во вторую таблицу. Обратите внимание на модификатор NEW. Модификатор NEW – это ключевое слово, которое используется в теле триггера для того, чтобы сказать СУБД о том, что нужно брать новые значения (значение, которое мы добавляем в таблицу или модифицированный вариант значения). Надеюсь, что вы разобрались в разнице между BEFORE и AFTER.

Рубрика 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

-- INSERT и DELETE триггеры
DROP TABLE users;
DROP TABLE user_log;

CREATE TABLE users(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL,
address TEXT NOT NULL,
mydate TEXT NOT NULL
);

CREATE TABLE user_log (
Id_u INTEGER NOT NULL,
u_date TEXT NOT NULL,
operation TEXT NOT NULL
);
-- Мы добавили столбец operation, в котором будем хранить информацию о том, что мы сделали с пользователем: удалили или добавили.

-- Напишим новый INSERT триггер, который будет учитывать новый столбец

CREATE TRIGGER my_u_log AFTER INSERT
ON users
BEGIN
INSERT INTO user_log(id_u, u_date, operation) VALUES (NEW.id, datetime('now'),'ins');
END;

-- Напишем триггер, который будет срабатывать по событию удаления строк
CREATE TRIGGER after_delete AFTER DELETE
ON users
BEGIN
INSERT INTO user_log(id_u, u_date, operation) VALUES (OLD.id, datetime('now'), 'del');
END;

-- Сделаем проверку
INSERT INTO users(name, age, address, mydate)
VALUES ('Пупкин', 27, 'Адрес', datetime('now'));
INSERT INTO users(name, age, address, mydate)
VALUES ('Сумкин', 17, 'Адрес2', datetime('now'));
INSERT INTO users(name, age, address, mydate)
VALUES ('Иванов', 37, 'Адрес3', datetime('now'));
INSERT INTO users(name, age, address, mydate)
VALUES ('Петров', 47, 'Адрес4', datetime('now'));
INSERT INTO users(name, age, address, mydate)
VALUES ('Сидоров', 57, 'Адрес5', datetime('now'));
INSERT INTO users(name, age, address, mydate)
VALUES ('Парамонов', 7, 'Адрес6', datetime('now'));

DELETE FROM users WHERE id = 4;

SELECT * FROM user_log;
SELECT * FROM users;


-- давайте обратим внимание на код триггера AFTER DELETE, в котором мы использовали модификатор OLD, модификатор OLD в SQL и SQLite используется в коде триггера для того, чтобы обратиться к старому значению или к значению, которое хранится в таблице (значение, которое будет изменено или модифицировано).

Рубрика 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

-- UPDATE триггеры

CREATE TRIGGER after_update AFTER UPDATE
ON users
BEGIN
INSERT INTO user_log(id_u, u_date, operation) VALUES (OLD.id, datetime('now'), 'upd');
END;

UPDATE users SET name = 'Марков'
WHERE id = 6;

SELECT * FROM user_log;
SELECT * FROM users;

-- Обратите внимание: триггер модификации или UPDATE триггер может отслеживать изменения не только для всей таблицы, но и для какого-то конкретного столбца, чтобы указать столбец, который будет отслеживать триггер модификации, используйте следующий синтаксис:

CREATE TRIGGER trigg_name AFTER UPDATE
OF (column1, column2)
BEGIN
-- тело триггераы
END;

Рубрика 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

-- Условия срабатывания триггера WHEN


CREATE TRIGGER when_insert AFTER INSERT
ON users WHEN (SELECT count(*) FROM user_log) (знак больше) 21
BEGIN
DELETE FROM user_log WHERE u_date = (SELECT min(u_date) FROM user_log);
INSERT INTO user_log(id_u, u_date, operation) VALUES (NEW.id, datetime('now'), 'ins');
END;

-- Этот триггер делает очень простую вещь: он ограничивает количество записей в логе до двадцати одной. То есть в таблице user_log будет храниться информация не обо всех модификациях, а только о последних, понятно, что количество записей в таблице можно регулировать.

-- Попробуйте реализовать данный триггер, чтобы посмотреть, как работает условие WHEN. В теле триггера выполняется две операции: первая удаляет лишнюю строку из лог-таблицы, вторая добавляет новую строку в таблицу лога.

Рубрика 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

-- Некоторые особенности триггеров в базах данных SQLite

/*
Ранее мы говорили, что в теле триггера можно использовать любую команду манипуляции данных с довольно полным синтаксисом. Но ключевая часть выражения здесь: довольно полный синтаксис. Всё дело в том, что синтаксис команд манипуляции данными в теле триггера SQLite поддерживается не полностью. Итак, ваш триггер не будет создан/не будет работать если:

В теле триггера вы не можете использовать квалификаторы для обращения к таблицам базы данных. Можно использовать только имена таблиц.
Когда в деле триггера вы выполняете операцию INSERT, то вы не можете добавлять значения DEFAULT. Добавляемые значения должны быть явно указаны.
Нельзя использовать ключевые слова INDEX BY и NOT INDEXED в командах UPDATE и DELETE.
Клаузулы LIMIT и ORDER BY нельзя использовать с командами UPDATE и DELETE в теле триггера.

Вот такие ограничения накладывает SQLite на команды SQL, которые мы можем использовать в теле триггера.

*/

-- Посмотреть триггеры в БД SQLite
SELECT name FROM sqlite_master
WHERE type = 'trigger';

-- Триггеры для какой-то опредленной таблицы
SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';

Рубрика 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

-- Редактирование VIEW при помощи INSTEAD OF триггера в SQLite


-- В SQLite нет возможности редактировать VIEW, Но мы можем манипулировать данными VIEW, которые хранятся в представлениях (данное выражение не совсем корректно, так как данные в представлениях не хранятся, это всего лишь результирующая таблица запроса SELECT) при помощи INSTEAD OF триггера.


-- UPDATE VIEW
CREATE TRIGGER trigg_name
INSTEAD OF UPDATE OF column_name ON view_name
BEGIN
-- делаем команду UPDATE для таблицы, на основе которой создана VIEW
END;


-- INSERT VIEW
CREATE TRIGGER trigg_name
INSTEAD OF INSERT ON view_name
BEGIN
-- делаем команду INSERT для таблицы, на основе которой создана VIEW
END;


-- DELETE VIEW
CREATE TRIGGER trigg_name
INSTEAD OF DELETE ON view_name
BEGIN
-- делаем команду DELETE для таблицы, на основе которой создана VIEW
END;

-- создадим представление
CREATE VIEW vw_city AS
SELECT city.name AS gorod, city.population AS naselenie
FROM city LIMIT 10;

-- посмотрим на нашу VIEW
SELECT * FROM vw_city;

-- Попробуем отредактировать VIEW
CREATE TRIGGER update_vw_city
INSTEAD OF UPDATE ON vw_city
BEGIN
UPDATE city SET name = NEW.gorod WHERE population = NEW.naselenie;
UPDATE city SET population = NEW.naselenie WHERE name = NEW.gorod;
END;

UPDATE vw_city SET gorod = 'Новое название города'
WHERE naselenie = 234323;

UPDATE vw_city SET naselenie = 15
WHERE gorod = 'Kabul';

SELECT * FROM vw_city;

SELECT * FROM city LIMIT 10;

-- по аналогии вы можете сделать свои триггеры для редактирования VIEW

Рубрика 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

-- Функция RAISE () и SQL триггеры в базах данных SQLite

-- к сожалению, ни одна в мире СУБД не знает, например, что Хемингуэй написал «Старик и море», а Исаак Ньютон – это физик, поэтому когда мы наполняем наши таблицы данными, могут возникать не очевидные ошибки с точки зрения логики реляционных баз данных, от которых не сможет защитить ни одна нормальная форма.
-- Например, когда мы говорили про внешние ключи, мы реализовывали связь один ко многим между таблицами, для этого мы создавали результирующую таблицу и я демонстрировал пример неудачно добавления данных, при котором получалось, что Джек Лондон написал «Войну и мир». Такие ошибки могут происходить довольно часто и их можно назвать конфликтами в базе данных.
-- Конфликт в базе данных не стоит путать с аномалией, потому что с точки зрения логики СУБД ничего криминального не происходит, а вот с точки зрения человека, знающего предметную область происходит существенная ошибка, которая может ввести человека в ступор.
-- Избавиться от подобных конфликтов мы можем при помощи триггеров и функции RAISE ()

/*
Отметим, что RAISE () – это специальная функция, которая используется только вместе с триггерами, ее синтаксис вы найдете на рисунке ниже.

Функция RAISE может принимать два аргумента. Первый аргумент описывает действие при возникновении конфликта:
Значение IGNORE. Говорит SQLite3 о том, чтобы она игнорировала строку породившую конфликт и продолжала выполнение последующих операций.
Значение ROLLBACK. ROLLBACK говорит о том, что SQLite должна откатить все операции к исходному состоянию при возникновении конфликта. При этом пользователь может изменить и повторить запрос. Не путайте с командой ROLLBACK, которая откатывая транзакцию
Значение ABORT. Данное значение похоже на ROLLBACK, но разница в том, что оно отменяет не все выполненные ранее SQL запросы, а только тот запрос, при котором возникла конфликтная ситуация.
Значение FAIL. Данное значение говорит СУБД о том, что нужно прервать выполнение текущей операции и сохранить результаты успешных операций, при этом операции, следующие за конфликтной, выполнены не будут.
*/

-- Создадим таблицы и наполним их данными
PRAGMA foreign_keys=on;
CREATE TABLE books(
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
count_page INTEGER NOT NULL),
price REAL
);

CREATE TABLE auth(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER
);

CREATE TABLE auth_book (
auth_id INTEGER NOT NULL,
books_id INTEGER NOT NULL,
FOREIGN KEY (auth_id) REFERENCES auth(id)
FOREIGN KEY (books_id) REFERENCES books(id)
);



INSERT INTO books (id, title, count_page, price)
VALUES (1, 'Белый клык', 287, 300.00);

INSERT INTO books (id, title, count_page, price)
VALUES (2, 'Война и мир', 806, 780.00);

INSERT INTO books (id, title, count_page, price)
VALUES (3, '12 стульев', 516, 480.00);

-- Затем добавим несколько авторов
INSERT INTO auth (id, name, age)
VALUES (1, 'Джек Лондон', 40);

INSERT INTO auth (id, name, age)
VALUES (2, 'Лев Толстой', 82);

INSERT INTO auth (id, name, age)
VALUES (3, 'Илья Ильф', 39);

INSERT INTO auth (id, name, age)
VALUES (4, 'Евгений Петров', 38);

-- Мы не стали наполнять таблицу-справочник, которая реализует связь многие ко многим
-- Давайте сперва напишем триггер, который будет проверять добавляемые значения

1 2

CREATE TRIGGER books_result BEFORE INSERT
ON auth_book
BEGIN
SELECT RAISE(FAIL, 'Произошла ошибка, вы неправильно связали автора и книгу') FROM auth_book
WHERE (NEW.auth_id = 1 AND books_id = 2) OR (NEW.auth_id = 1 AND NEW.books_id = 3) OR
(NEW.auth_id = 2 AND NEW.books_id = 3) OR (NEW.auth_id = 2 AND NEW.books_id = 3) OR
(NEW.auth_id = 3 AND NEW.books_id = 2) OR (NEW.auth_id = 3 AND NEW.books_id = 1) OR
(NEW.auth_id = 4 AND NEW.books_id = 2) OR (NEW.auth_id = 4 AND NEW.books_id = 1);
END;

-- Проверим работу триггера
INSERT INTO auth_book (auth_id, books_id)
VALUES (1, 1);

INSERT INTO auth_book (auth_id, books_id)
VALUES (2, 2);

INSERT INTO auth_book (auth_id, books_id)
VALUES (3, 3);

INSERT INTO auth_book (auth_id, books_id)
VALUES (4, 3);

INSERT INTO auth_book (auth_id, books_id)
VALUES (1, 2);


SELECT * FROM auth_book;

Помощь проекту:
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

1. Ускоряем выборку данных в SQL: для чего нужны индексы в базах данных.

2. Удаление и создание SQL индексов в базе данных.

3. Частичные и композитные индексы в базах данных: как создать индекс из нескольких столбцов.

4. Работа с индексами в базах данных SQLite. Уникальные индексы и переиндексация таблиц.

5. Когда не стоит индексировать таблицы базы данных.

6. SQL таблицы WITHOUT ROWID и столбец ROWID в базах данных SQLite

7. Алгоритм B-tree. За счет чего индексы ускоряют выборку данных из базы данных?