SQLite - Краткое руководство

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

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

Что такое SQLite?

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

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

Почему именно SQLite?

• SQLite не требует отдельного серверного процесса или системы для работы (без сервера).

• SQLite поставляется с нулевой конфигурацией, что означает отсутствие необходимости в настройке или администрировании.

• Полная база данных SQLite хранится в одном кроссплатформенном дисковом файле.

• SQLite очень маленький и легкий, менее 400 КБ полностью настроен или менее 250 КБ с опущенными дополнительными функциями.

• SQLite является самодостаточным, что означает отсутствие внешних зависимостей.

• Транзакции SQLite полностью совместимы с ACID, что обеспечивает безопасный доступ из нескольких процессов или потоков.

• SQLite поддерживает большинство функций языка запросов, имеющихся в стандарте SQL92 (SQL2).

• SQLite написан на ANSI-C и предоставляет простой и легкий в использовании API.

• SQLite доступен в UNIX (Linux, Mac OS-X, Android, iOS) и Windows (Win32, WinCE, WinRT).

SQLite Краткая история

• 2000 - Д. Ричард Хипп разработал SQLite, не требующий администрирования для работы с программой.

• 2000 - В августе выпущен SQLite 1.0 с GNU Database Manager.

• 2011 - Hipp объявил о добавлении интерфейса UNQl в базу данных SQLite и разработке UNQLite (документно-ориентированной базы данных).

Ограничения SQLite

Есть несколько неподдерживаемых функций SQL92 в SQLite, которые перечислены в следующей таблице.

Sr.No.	Особенности и описание
1	RIGHT OUTER JOIN Реализовано только LEFT OUTER JOIN.
2	FULL OUTER JOIN Реализовано только LEFT OUTER JOIN.
3	ALTER TABLE Поддерживаются варианты RENAME TABLE и ADD COLUMN команды ALTER TABLE. DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT не поддерживаются.
4	Trigger support Поддерживаются триггеры FOR EACH ROW, но не FOR EACH STATEMENT.
5	VIEWs ВИДЫ в SQLite доступны только для чтения. Вы не можете выполнять инструкции DELETE, INSERT или UPDATE для представления.
6	GRANT and REVOKE Единственные права доступа, которые можно применить, - это обычные права доступа к файлам базовой операционной системы.

Команды SQLite

Стандартные команды SQLite для взаимодействия с реляционными базами данных аналогичны SQL. Это CREATE, SELECT, INSERT, UPDATE, DELETE и DROP. Эти команды можно разделить на группы в зависимости от их рабочего характера:

DDL - язык определения данных

Sr.No.	Команда и описание
1	CREATE Создает новую таблицу, представление таблицы или другой объект в базе данных.
2	ALTER Изменяет существующий объект базы данных, например таблицу.
3	DROP Удаляет всю таблицу, представление таблицы или другой объект в базе данных.

DML - язык манипулирования данными

Sr.No.	Команда и описание
1	INSERT Создает запись
2	UPDATE Изменяет записи
3	DELETE Удаляет записи

DQL - язык запросов данных

Sr.No.	Команда и описание
1	SELECT Извлекает определенные записи из одной или нескольких таблиц

SQLite известен своей отличной функцией нулевой настройки, что означает отсутствие необходимости в сложной настройке или администрировании. В этой главе вы узнаете, как настроить SQLite в Windows, Linux и Mac OS X.

Установить SQLite в Windows

• Step 1- Перейдите на страницу загрузки SQLite и загрузите предварительно скомпилированные двоичные файлы из раздела Windows.

• Step 2 - Загрузите заархивированные файлы sqlite-shell-win32 - *. Zip и sqlite-dll-win32 - *. Zip.

• Step 3 - Создайте папку C: \> sqlite и распакуйте два заархивированных файла в этой папке, в результате вы получите файлы sqlite3.def, sqlite3.dll и sqlite3.exe.

• Step 4 - Добавьте C: \> sqlite в переменную среды PATH и, наконец, перейдите в командную строку и введите команду sqlite3, которая должна отобразить следующий результат.

C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Установите SQLite в Linux

Сегодня почти все разновидности ОС Linux поставляются с SQLite. Поэтому вы просто вводите следующую команду, чтобы проверить, установлен ли на вашем компьютере SQLite.

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Если вы не видите вышеуказанного результата, это означает, что на вашем компьютере с Linux не установлен SQLite. Ниже приведены следующие шаги для установки SQLite:

• Step 1- Перейдите на страницу загрузки SQLite и загрузите sqlite-autoconf - *. Tar.gz из раздела исходного кода.

• Step 2 - Выполните следующую команду -

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local
$make $make install

Вышеупомянутая команда завершится установкой SQLite на вашем компьютере с Linux. Что вы можете проверить, как описано выше.

Установите SQLite в Mac OS X

Хотя последняя версия Mac OS X поставляется с предустановленной SQLite, но если у вас нет доступной установки, просто выполните следующие шаги:

• Step 1- Перейдите на страницу загрузки SQLite и загрузите sqlite-autoconf - *. Tar.gz из раздела исходного кода.

• Step 2 - Выполните следующую команду -

$tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local $make
$make install

Вышеупомянутая процедура завершится установкой SQLite на вашем компьютере Mac OS X. Что вы можете проверить, выполнив следующую команду -

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Наконец, у вас есть командная строка SQLite, в которой вы можете вводить команды SQLite для своих упражнений.

В этой главе вы познакомитесь с простыми и полезными командами, используемыми программистами SQLite. Эти команды называются точечными командами SQLite, и исключение из этих команд состоит в том, что они не должны заканчиваться точкой с запятой (;).

Начнем с набора простого sqlite3 в командной строке, которая предоставит вам командную строку SQLite, в которой вы будете вводить различные команды SQLite.

$sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

Чтобы просмотреть список доступных команд с точкой, вы можете в любое время ввести «.help». Например -

sqlite>.help

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

Sr.No.	Команда и описание
1	.backup ?DB? FILE Резервное копирование БД (по умолчанию "основная") в ФАЙЛ
2	.bail ON|OFF Остановитесь после появления ошибки. По умолчанию ВЫКЛ.
3	.databases Список имен и файлов прикрепленных баз данных
4	.dump ?TABLE? Дамп базы данных в текстовом формате SQL. Если указано TABLE, дампить только таблицы, соответствующие шаблону LIKE TABLE
5	.echo ON|OFF Включение или выключение эха команды
6	.exit Выйти из приглашения SQLite
7	.explain ON|OFF Включите или выключите режим вывода, подходящий для EXPLAIN. Без аргументов включается EXPLAIN.
8	.header(s) ON|OFF Включение или отключение отображения заголовков
9	.help Показать это сообщение
10	.import FILE TABLE Импортировать данные из ФАЙЛА в ТАБЛИЦУ
11	.indices ?TABLE? Показать названия всех индексов. Если указано TABLE, показывать только индексы для таблиц, соответствующих шаблону LIKE TABLE
12	.load FILE ?ENTRY? Загрузите библиотеку расширений
13	.log FILE|off Включите или выключите вход. ФАЙЛ может быть stderr / stdout
14	.mode MODE Установите режим вывода, где РЕЖИМ является одним из - csv - значения, разделенные запятыми column - Столбцы с выравниванием по левому краю. html - HTML <table> код insert - Операторы вставки SQL для ТАБЛИЦЫ line - Одно значение в строке list - Значения, разделенные строкой .separator tabs - Значения, разделенные табуляцией tcl - Элементы списка TCL
15	.nullvalue STRING Печатать STRING вместо значений NULL
16	.output FILENAME Отправить вывод в FILENAME
17	.output stdout Отправить вывод на экран
18	.print STRING... Печатать буквальное значение STRING
19	.prompt MAIN CONTINUE Заменить стандартные подсказки
20	.quit Выйти из приглашения SQLite
21 год	.read FILENAME Выполнить SQL в FILENAME
22	.schema ?TABLE? Покажите операторы CREATE. Если указано TABLE, показывать только таблицы, соответствующие шаблону LIKE TABLE
23	.separator STRING Изменить разделитель, используемый режимом вывода и .import
24	.show Показать текущие значения для различных настроек
25	.stats ON|OFF Включить или выключить статистику
26	.tables ?PATTERN? Список имен таблиц, соответствующих шаблону LIKE
27	.timeout MS Попробуйте открыть заблокированные таблицы за миллисекунды MS
28	.width NUM NUM Установите ширину столбца для режима «столбец»
29	.timer ON|OFF Включение или выключение измерения таймера ЦП

Давай попробуем .show , чтобы увидеть настройки по умолчанию для командной строки SQLite.

sqlite>.show
     echo: off
  explain: off
  headers: off
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>

Убедитесь, что между приглашением sqlite> и командой точки нет пробелов, иначе это не сработает.

Форматирование вывода

Вы можете использовать следующую последовательность команд с точками для форматирования вывода.

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

Приведенная выше настройка даст результат в следующем формате.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
CPU Time: user 0.000000 sys 0.000000

Таблица sqlite_master

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

sqlite>.schema sqlite_master

Это даст следующий результат.

CREATE TABLE sqlite_master (
   type text,
   name text,
   tbl_name text,
   rootpage integer,
   sql text
);

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

Чувствительность к регистру

Важно отметить, что SQLite - это case insensitive, то есть пункты GLOB и glob имеют то же значение в операторах SQLite.

Комментарии

Комментарии SQLite - это дополнительные примечания, которые вы можете добавить в свой код SQLite для повышения его читабельности, и они могут появляться где угодно; могут встречаться пробелы, в том числе внутри выражений и в середине других операторов SQL, но они не могут быть вложенными.

Комментарии SQL начинаются с двух последовательных символов «-» (ASCII 0x2d) и продолжаются до следующего символа новой строки (ASCII 0x0a) включительно или до конца ввода, в зависимости от того, что наступит раньше.

Вы также можете использовать комментарии в стиле C, которые начинаются с «/ *» и простираются до следующей пары символов «* /» включительно или до конца ввода, в зависимости от того, что наступит раньше. Комментарии в стиле C могут занимать несколько строк.

sqlite> .help -- This is a single line comment

Заявления SQLite

Все операторы SQLite начинаются с любого из ключевых слов, таких как SELECT, INSERT, UPDATE, DELETE, ALTER, DROP и т. Д., И все операторы заканчиваются точкой с запятой (;).

Заявление SQLite ANALYZE

ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;

Предложение SQLite AND / OR

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;

Оператор SQLite ALTER TABLE

ALTER TABLE table_name ADD COLUMN column_def...;

Оператор SQLite ALTER TABLE (переименование)

ALTER TABLE table_name RENAME TO new_table_name;

Заявление SQLite ATTACH DATABASE

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

Оператор SQLite BEGIN TRANSACTION

BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

Предложение SQLite BETWEEN

SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

Заявление SQLite COMMIT

COMMIT;

Оператор SQLite CREATE INDEX

CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

Оператор SQLite CREATE UNIQUE INDEX

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

Оператор SQLite CREATE TABLE

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Оператор SQLite CREATE TRIGGER

CREATE TRIGGER database_name.trigger_name 
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN 
   stmt1; 
   stmt2;
   ....
END;

Оператор SQLite CREATE VIEW

CREATE VIEW database_name.view_name AS
SELECT statement....;

Оператор SQLite CREATE VIRTUAL TABLE

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

Заявление SQLite COMMIT TRANSACTION

COMMIT;

Предложение SQLite COUNT

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

Оператор SQLite DELETE

DELETE FROM table_name
WHERE {CONDITION};

Заявление SQLite DETACH DATABASE

DETACH DATABASE 'Alias-Name';

Предложение SQLite DISTINCT

SELECT DISTINCT column1, column2....columnN
FROM table_name;

Оператор SQLite DROP INDEX

DROP INDEX database_name.index_name;

Оператор SQLite DROP TABLE

DROP TABLE database_name.table_name;

Оператор SQLite DROP VIEW

DROP INDEX database_name.view_name;

Заявление SQLite DROP TRIGGER

DROP INDEX database_name.trigger_name;

Предложение SQLite EXISTS

SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM   table_name );

Заявление SQLite EXPLAIN

EXPLAIN INSERT statement...;
or 
EXPLAIN QUERY PLAN SELECT statement...;

Предложение SQLite GLOB

SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };

Предложение SQLite GROUP BY

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;

Предложение SQLite HAVING

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

Оператор SQLite INSERT INTO

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

Предложение SQLite IN

SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);

Предложение SQLite Like

SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };

Предложение SQLite NOT IN

SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);

Предложение SQLite ORDER BY

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

Заявление SQLite PRAGMA

PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

Заявление SQLite RELEASE SAVEPOINT

RELEASE savepoint_name;

Заявление SQLite REINDEX

REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

Заявление SQLite ROLLBACK

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

Заявление SQLite SAVEPOINT

SAVEPOINT savepoint_name;

Оператор SQLite SELECT

SELECT column1, column2....columnN
FROM table_name;

Заявление SQLite UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];

Заявление SQLite VACUUM

VACUUM;

Предложение SQLite WHERE

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;

Тип данных SQLite - это атрибут, определяющий тип данных любого объекта. Каждый столбец, переменная и выражение имеют связанный тип данных в SQLite.

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

Классы хранилища SQLite

Каждое значение, хранящееся в базе данных SQLite, имеет один из следующих классов хранения:

Sr.No.	Класс хранения и описание
1	NULL Это значение NULL.
2	INTEGER Значение представляет собой целое число со знаком, хранящееся в 1, 2, 3, 4, 6 или 8 байтах в зависимости от величины значения.
3	REAL Значение представляет собой значение с плавающей запятой, сохраненное как 8-байтовое число с плавающей запятой IEEE.
4	TEXT Значение представляет собой текстовую строку, хранящуюся с использованием кодировки базы данных (UTF-8, UTF-16BE или UTF-16LE).
5	BLOB Значение - это блок данных, хранящийся точно в том виде, в котором он был введен.

Класс хранилища SQLite является немного более общим, чем тип данных. Например, класс хранения INTEGER включает 6 различных целочисленных типов данных разной длины.

Тип соответствия SQLite

SQLite поддерживает концепцию type affinityна колоннах. Любой столбец по-прежнему может хранить данные любого типа, но предпочтительный класс хранения для столбца называется егоaffinity. Каждому столбцу таблицы в базе данных SQLite3 назначается одно из следующих сходств типов:

Sr.No.	Сходство и описание
1	TEXT В этом столбце хранятся все данные с использованием классов хранения NULL, TEXT или BLOB.
2	NUMERIC Этот столбец может содержать значения, использующие все пять классов хранения.
3	INTEGER Ведет себя так же, как столбец с ЧИСЛЕННЫМ сходством, за исключением выражения CAST.
4	REAL Ведет себя как столбец с числовым сходством, за исключением того, что переводит целые значения в представление с плавающей запятой.
5	NONE Столбец со сродством NONE не предпочитает один класс хранилища другому, и не предпринимается никаких попыток принудительного преобразования данных из одного класса хранилища в другой.

Сходство SQLite и имена типов

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

Тип данных	Близость
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT НЕПОДПИСАННЫЙ БОЛЬШОЙ ИНТ INT2 INT8	ЦЕЛОЕ
CHARACTER(20) VARCHAR(255) РАЗЛИЧНЫЙ ХАРАКТЕР (255) NCHAR(55) РОДНЫЙ ХАРАКТЕР (70) NVARCHAR(100) TEXT CLOB	ТЕКСТ
BLOB не указан тип данных	НИКТО
REAL DOUBLE ДВОЙНАЯ ТОЧНОСТЬ FLOAT	НАСТОЯЩИЙ
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	ЧИСЛОВЫЙ

Логический тип данных

SQLite не имеет отдельного логического класса хранения. Вместо этого логические значения сохраняются как целые числа 0 (ложь) и 1 (истина).

Дата и время Тип данных

SQLite не имеет отдельного класса хранения для хранения дат и / или времени, но SQLite может хранить даты и время как значения TEXT, REAL или INTEGER.

Sr.No.	Класс хранения и формат даты
1	TEXT Дата в формате "ГГГГ-ММ-ДД ЧЧ: ММ: СС.ССС".
2	REAL Количество дней с полудня в Гринвиче 24 ноября 4714 г. до н.э.
3	INTEGER Количество секунд с 1970-01-01 00:00:00 UTC

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

В SQLite sqlite3команда используется для создания новой базы данных SQLite. Для создания базы данных не требуется никаких специальных привилегий.

Синтаксис

Ниже приводится базовый синтаксис команды sqlite3 для создания базы данных: -

$sqlite3 DatabaseName.db

Имя базы данных всегда должно быть уникальным в пределах СУБД.

пример

Если вы хотите создать новую базу данных <testDB.db>, тогда инструкция SQLITE3 будет выглядеть следующим образом:

$sqlite3 testDB.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Приведенная выше команда создаст файл testDB.dbв текущем каталоге. Этот файл будет использоваться движком SQLite как база данных. Если вы заметили при создании базы данных, команда sqlite3 предоставитsqlite> запрос после успешного создания файла базы данных.

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

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db

Вы будете использовать SQLite .quit команда для выхода из приглашения sqlite следующим образом:

sqlite>.quit
$

Команда .dump

Вы можете использовать .dump точка для экспорта полной базы данных в текстовый файл с помощью следующей команды SQLite в командной строке.

$sqlite3 testDB.db .dump > testDB.sql

Приведенная выше команда преобразует все содержимое testDB.db базу данных в операторы SQLite и выгружать ее в текстовый файл ASCII testDB.sql. Вы можете выполнить восстановление из сгенерированного testDB.sql простым способом следующим образом:

$sqlite3 testDB.db < testDB.sql

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

Рассмотрим случай, когда у вас есть несколько доступных баз данных и вы хотите использовать любую из них одновременно. SQLiteATTACH DATABASE Оператор используется для выбора конкретной базы данных, и после этой команды все операторы SQLite будут выполняться в подключенной базе данных.

Синтаксис

Ниже приведен основной синтаксис оператора SQLite ATTACH DATABASE.

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

Вышеупомянутая команда также создаст базу данных, если база данных еще не создана, иначе она просто присоединит имя файла базы данных с логическим именем базы данных «Псевдоним».

пример

Если вы хотите присоединить существующую базу данных testDB.db, тогда оператор ATTACH DATABASE будет следующим:

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';

Используйте SQLite .database команда для отображения присоединенной базы данных.

sqlite> .database
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

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

sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database TEMP is already in use

SQLite DETACH DATABASEОператор используется для отсоединения именованной базы данных от соединения с базой данных, которое ранее было подключено с помощью оператора ATTACH. Если один и тот же файл базы данных был прикреплен с несколькими псевдонимами, то команда DETACH отключит только заданное имя, а остальная часть вложения продолжится. Вы не можете отсоединитьmain или же temp базы данных.

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

Синтаксис

Ниже приведен базовый синтаксис оператора SQLite DETACH DATABASE 'Alias-Name'.

DETACH DATABASE 'Alias-Name';

Здесь «Alias-Name» - это тот же псевдоним, который вы использовали при присоединении базы данных с помощью оператора ATTACH.

пример

Предположим, у вас есть база данных, которую вы создали в предыдущей главе и прикрепили к ней с помощью «test» и «currentDB», как мы можем видеть, используя .database команда.

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db
3    currentDB        /home/sqlite/testDB.db

Давайте попробуем отсоединить currentDB от testDB.db, используя следующую команду.

sqlite> DETACH DATABASE 'currentDB';

Теперь, если вы проверите текущее вложение, вы обнаружите, что testDB.db все еще связан с 'test' и 'main'.

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

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

Синтаксис

Ниже приводится основной синтаксис оператора CREATE TABLE.

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

CREATE TABLE - это ключевое слово, указывающее системе баз данных создание новой таблицы. Уникальное имя или идентификатор таблицы следует за оператором CREATE TABLE. При желании вы можете указать имя_базы_данных вместе с именем_таблицы .

пример

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

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

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

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

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

sqlite>.tables
COMPANY     DEPARTMENT

Здесь вы можете увидеть таблицу COMPANY дважды, потому что она показывает таблицу COMPANY для основной базы данных и таблицу test.COMPANY для псевдонима test, созданного для вашего testDB.db. Вы можете получить полную информацию о таблице, используя следующий SQLite.schema команда.

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

SQLite DROP TABLE Оператор используется для удаления определения таблицы и всех связанных данных, индексов, триггеров, ограничений и спецификаций разрешений для этой таблицы.

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

Синтаксис

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

DROP TABLE database_name.table_name;

пример

Давайте сначала проверим таблицу COMPANY, а затем удалим ее из базы данных.

sqlite>.tables
COMPANY       test.COMPANY

Это означает, что таблица COMPANY доступна в базе данных, поэтому давайте отбросим ее следующим образом -

sqlite>DROP TABLE COMPANY;
sqlite>

Теперь, если вы попробуете команду .TABLES, вы больше не найдете таблицу COMPANY.

sqlite>.tables
sqlite>

Он ничего не показывает, что означает, что таблица из вашей базы данных была успешно удалена.

SQLite INSERT INTO Оператор используется для добавления новых строк данных в таблицу в базе данных.

Синтаксис

Ниже приведены два основных синтаксиса оператора INSERT INTO.

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

Здесь column1, column2, ... columnN - это имена столбцов в таблице, в которые вы хотите вставить данные.

Возможно, вам не потребуется указывать имя столбца (столбцов) в запросе SQLite, если вы добавляете значения для всех столбцов таблицы. Однако убедитесь, что порядок значений соответствует порядку столбцов в таблице. Синтаксис SQLite INSERT INTO будет следующим:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

пример

Предположим, вы уже создали таблицу COMPANY в своем testDB.db следующим образом:

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Теперь следующие инструкции создадут шесть записей в таблице COMPANY.

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

Вы можете создать запись в таблице COMPANY, используя второй синтаксис следующим образом:

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

Все приведенные выше операторы создадут следующие записи в таблице COMPANY. В следующей главе вы узнаете, как отобразить все эти записи из таблицы.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Заполнить одну таблицу с помощью другой таблицы

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

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

На данный момент вы можете пропустить приведенное выше утверждение. Во-первых, давайте изучим предложения SELECT и WHERE, которые будут рассмотрены в следующих главах.

SQLite SELECTОператор используется для извлечения данных из таблицы базы данных SQLite, которая возвращает данные в виде таблицы результатов. Эти таблицы результатов также называютсяresult sets.

Синтаксис

Ниже приведен базовый синтаксис оператора SQLite SELECT.

SELECT column1, column2, columnN FROM table_name;

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

SELECT * FROM table_name;

пример

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример выборки и отображения всех этих записей с помощью оператора SELECT. Здесь первые три команды были использованы для установки правильно отформатированного вывода.

sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;

В итоге вы получите следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Если вы хотите получить только выбранные поля таблицы КОМПАНИЯ, используйте следующий запрос -

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

Вышеупомянутый запрос даст следующий результат.

ID          NAME        SALARY
----------  ----------  ----------
1           Paul        20000.0
2           Allen       15000.0
3           Teddy       20000.0
4           Mark        65000.0
5           David       85000.0
6           Kim         45000.0
7           James       10000.0

Установка ширины выходного столбца

Иногда вы столкнетесь с проблемой, связанной с усеченным выводом в случае .mode columnчто происходит из-за ширины отображаемого столбца по умолчанию. Что вы можете сделать, вы можете установить ширину отображаемого столбца, используя.width num, num.... команда следующим образом -

sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

Выше .width Команда устанавливает ширину первого столбца на 10, ширину второго столбца на 20 и ширину третьего столбца на 10. Наконец, приведенный выше оператор SELECT даст следующий результат.

ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

Информация о схеме

Как и все dot commands доступны в приглашении SQLite, поэтому при программировании с помощью SQLite вы будете использовать следующий оператор SELECT с sqlite_master table, чтобы перечислить все таблицы, созданные в вашей базе данных.

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

Предполагая, что у вас есть только таблица COMPANY в вашем testDB.db, это даст следующий результат.

tbl_name
----------
COMPANY

Вы можете перечислить полную информацию о таблице КОМПАНИИ следующим образом -

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

Предполагая, что у вас есть только таблица COMPANY в вашем testDB.db, это даст следующий результат.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
)

Что такое оператор в SQLite?

Оператор - это зарезервированное слово или символ, используемый в основном в предложении WHERE оператора SQLite для выполнения операции (операций), таких как сравнения и арифметические операции.

Операторы используются для указания условий в операторе SQLite и служат в качестве союзов для нескольких условий в операторе.

• Арифметические операторы
• Операторы сравнения
• Логические операторы
• Побитовые операторы

Арифметические операторы SQLite

Предположим переменную a содержит 10 и переменную b содержит 20, то арифметические операторы SQLite будут использоваться следующим образом:

Показать примеры

Оператор	Описание	пример
+ (Дополнение)	Добавляет значения по обе стороны от оператора	a + b даст 30
- (Вычитание)	Вычитает правый операнд из левого операнда	а - б даст -10
* (Умножение)	Умножает значения по обе стороны от оператора	a * b даст 200
/ (Деление)	Делит левый операнд на правый операнд	б / у даст 2
% (Модуль)	Делит левый операнд на правый и возвращает остаток.	b% a даст 0

Операторы сравнения SQLite

Предположим переменную a содержит 10 и переменную b содержит 20, то операторы сравнения SQLite будут использоваться следующим образом

Показать примеры

Оператор	Описание	пример
==	Проверяет, равны ли значения двух операндов или нет, если да, то условие становится истинным.	(a == b) неверно.
знак равно	Проверяет, равны ли значения двух операндов или нет, если да, то условие становится истинным.	(a = b) неверно.
знак равно	Проверяет, равны ли значения двух операндов или нет, если значения не равны, тогда условие становится истинным.	(a! = b) верно.
<>	Проверяет, равны ли значения двух операндов или нет, если значения не равны, тогда условие становится истинным.	(a <> b) верно.
>	Проверяет, больше ли значение левого операнда, чем значение правого операнда, если да, то условие становится истинным.	(a> b) неверно.
<	Проверяет, меньше ли значение левого операнда, чем значение правого операнда, если да, то условие становится истинным.	(a <b) верно.
> =	Проверяет, больше ли значение левого операнда или равно значению правого операнда, если да, то условие становится истинным.	(a> = b) неверно.
<=	Проверяет, меньше ли значение левого операнда или равно значению правого операнда, если да, то условие становится истинным.	(a <= b) верно.
! <	Проверяет, не меньше ли значение левого операнда, чем значение правого операнда, если да, то условие становится истинным.	(a! <b) ложно.
!>	Проверяет, не превышает ли значение левого операнда значение правого операнда, если да, то условие становится истинным.	(a!> b) верно.

Логические операторы SQLite

Вот список всех логических операторов, доступных в SQLite.

Показать примеры

Sr.No.	Оператор и описание
1	AND Оператор AND допускает наличие нескольких условий в предложении WHERE оператора SQL.
2	BETWEEN Оператор BETWEEN используется для поиска значений, которые находятся в наборе значений с учетом минимального и максимального значений.
3	EXISTS Оператор EXISTS используется для поиска наличия строки в указанной таблице, соответствующей определенным критериям.
4	IN Оператор IN используется для сравнения значения со списком указанных литеральных значений.
5	NOT IN Оператор отрицания IN, который используется для сравнения значения со списком указанных литеральных значений.
6	LIKE Оператор LIKE используется для сравнения значения с аналогичными значениями с использованием операторов подстановки.
7	GLOB Оператор GLOB используется для сравнения значения с аналогичными значениями с помощью операторов подстановки. Кроме того, GLOB чувствителен к регистру, в отличие от LIKE.
8	NOT Оператор НЕ меняет значение логического оператора, с которым он используется. Например. НЕ СУЩЕСТВУЕТ, НЕ МЕЖДУ, НЕ В И т.д.This is negate operator.
9	OR Оператор OR используется для объединения нескольких условий в предложении WHERE оператора SQL.
10	IS NULL Оператор NULL используется для сравнения значения со значением NULL.
11	IS Оператор IS работает как =
12	IS NOT Оператор IS работает как! =
13	|| Добавляет две разные струны и создает новую.
14	UNIQUE Оператор UNIQUE ищет каждую строку указанной таблицы на уникальность (без дубликатов).

Побитовые операторы SQLite

Побитовый оператор работает с битами и выполняет побитовую операцию. Ниже приводится таблица истинности для& и |.

п	q	p & q	p | q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

Предположим, если A= 60; иB = 13, то в двоичном формате они будут такими:

А = 0011 1100

В = 0000 1101

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

A&B = 0000 1100

А | В = 0011 1101

~ А = 1100 0011

Побитовые операторы, поддерживаемые языком SQLite, перечислены в следующей таблице. Предположим переменнуюA содержит 60 и переменную B имеет 13, тогда -

Показать примеры

Оператор	Описание	пример
&	Двоичный оператор И копирует бит в результат, если он существует в обоих операндах.	(A и B) даст 12, что составляет 0000 1100
|	Оператор двоичного ИЛИ копирует бит, если он существует в любом из операндов.	(A | B) даст 61, что составляет 0011 1101
~	Оператор дополнения двоичных единиц является унарным и имеет эффект «переворачивания» битов.	(~ A) даст -61, что составляет 1100 0011 в форме дополнения до 2 из-за двоичного числа со знаком.
<<	Оператор двоичного сдвига влево. Значение левого операнда сдвигается влево на количество битов, указанное правым операндом.	<< 2 даст 240, что составляет 1111 0000
>>	Оператор двоичного сдвига вправо. Значение левого операнда перемещается вправо на количество битов, указанное правым операндом.	A >> 2 даст 15, что равно 0000 1111

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

Выражения SQL похожи на формулы и написаны на языке запросов. Вы также можете использовать для запроса базы данных для определенного набора данных.

Синтаксис

Рассмотрим основной синтаксис оператора SELECT следующим образом:

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION | EXPRESSION];

Ниже приведены различные типы выражений SQLite.

SQLite - логические выражения

Логические выражения SQLite извлекают данные на основе совпадающего единственного значения. Ниже приводится синтаксис -

SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHTING EXPRESSION;

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведены простые примеры, показывающие использование логических выражений SQLite.

sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

SQLite - числовое выражение

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

SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

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

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

Есть несколько встроенных функций, таких как avg(), sum(), count(), и т.д., чтобы выполнить то, что известно как aggregate data calculations против таблицы или определенного столбца таблицы.

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 
RECORDS = 7

SQLite - выражения даты

Date Expressions возвращает текущие системные значения даты и времени. Эти выражения используются в различных манипуляциях с данными.

sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

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

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

Предложение WHERE используется не только в операторе SELECT, но также в операторе UPDATE, DELETE и т. Д., Которые будут рассмотрены в следующих главах.

Синтаксис

Ниже приведен базовый синтаксис оператора SQLite SELECT с предложением WHERE.

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

пример

Вы можете указать условие с помощью операторов сравнения или логических операторов, таких как>, <, =, LIKE, NOT и т. Д. Рассмотрим таблицу COMPANY со следующими записями:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведены простые примеры, показывающие использование логических операторов SQLite. После оператора SELECT перечислены все записи, в которых AGE больше или равен 25.AND зарплата больше или равна 65000.00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

После оператора SELECT перечислены все записи, в которых AGE больше или равен 25. OR зарплата больше или равна 65000.00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Следующий оператор SELECT перечисляет все записи, в которых AGE не равно NULL, что означает все записи, потому что ни одна из записей не имеет AGE, равного NULL.

sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

После оператора SELECT перечисляются все записи, в которых NAME начинается с Ki, не имеет значения, что идет после Ki.

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

После оператора SELECT перечисляются все записи, в которых NAME начинается с Ki, не имеет значения, что идет после Ki.

sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

После оператора SELECT перечислены все записи, в которых значение AGE равно 25 или 27.

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Следующий оператор SELECT перечисляет все записи, в которых значение AGE не равно 25 или 27.

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Следующий оператор SELECT перечисляет все записи, в которых значение AGE находится в МЕЖДУ 25 И 27.

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Следующий оператор SELECT использует подзапрос SQL, где подзапрос находит все записи с полем AGE, имеющим SALARY> 65000 и более поздних версий. Предложение WHERE используется вместе с оператором EXISTS для вывода списка всех записей, в которых существует AGE из внешнего запроса в результате, возвращаемом подзапросом -

sqlite> SELECT AGE FROM COMPANY 
   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

AGE
----------
32
25
23
25
27
22
24

Следующий оператор SELECT использует подзапрос SQL, в котором подзапрос находит все записи с полем AGE, имеющим SALARY> 65000 и более поздних версий. Предложение WHERE используется вместе с оператором> для вывода списка всех записей, для которых AGE из внешнего запроса больше чем возраст в результате, возвращенном подзапросом.

sqlite> SELECT * FROM COMPANY 
   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

SQLite AND & ORоператоры используются для компиляции нескольких условий для сужения выбранных данных в инструкции SQLite. Эти два оператора называютсяconjunctive operators.

Эти операторы позволяют выполнять несколько сравнений с разными операторами в одном и том же операторе SQLite.

Оператор AND

В ANDОператор допускает наличие нескольких условий в предложении WHERE SQLite. При использовании оператора AND полное условие будет считаться истинным, если все условия верны. Например, [условие1] И [условие2] будет истинным только тогда, когда истинны и условие1, и условие2.

Синтаксис

Ниже приводится базовый синтаксис оператора AND с предложением WHERE.

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

Вы можете комбинировать Nколичество условий с использованием оператора AND. Чтобы оператор SQLite мог выполнить какое-либо действие, будь то транзакция или запрос, все условия, разделенные оператором AND, должны иметь значение ИСТИНА.

пример

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

После оператора SELECT перечислены все записи, в которых AGE больше или равен 25. AND зарплата больше или равна 65000.00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Оператор OR

Оператор OR также используется для объединения нескольких условий в предложении WHERE SQLite. При использовании оператора OR полное условие будет считаться истинным, если хотя бы одно из условий истинно. Например, [условие1] ИЛИ [условие2] будет истинным, если истинно либо условие1, либо условие2.

Синтаксис

Ниже приведен базовый синтаксис оператора ИЛИ с предложением WHERE.

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

Вы можете комбинировать Nколичество условий с использованием оператора ИЛИ. Для действия, которое должно быть выполнено оператором SQLite, будь то транзакция или запрос, только любое ОДНО из условий, разделенных ИЛИ, должно быть ИСТИНА.

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

После оператора SELECT перечислены все записи, в которых AGE больше или равен 25. OR зарплата больше или равна 65000.00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite UPDATEЗапрос используется для изменения существующих записей в таблице. Вы можете использовать предложение WHERE с запросом UPDATE для обновления выбранных строк, иначе все строки будут обновлены.

Синтаксис

Ниже приведен базовый синтаксис запроса UPDATE с предложением WHERE.

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Вы можете комбинировать N количество условий с использованием операторов И или ИЛИ.

пример

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример, который обновит АДРЕС для клиента с идентификатором 6.

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

Теперь в таблице COMPANY будут следующие записи.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          Texas       45000.0
7           James       24          Houston     10000.0

Если вы хотите изменить все значения столбцов ADDRESS и SALARY в таблице COMPANY, вам не нужно использовать предложение WHERE, и запрос UPDATE будет следующим:

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

Теперь в таблице COMPANY будут следующие записи -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          Texas       20000.0
2           Allen       25          Texas       20000.0
3           Teddy       23          Texas       20000.0
4           Mark        25          Texas       20000.0
5           David       27          Texas       20000.0
6           Kim         22          Texas       20000.0
7           James       24          Texas       20000.0

SQLite DELETEЗапрос используется для удаления существующих записей из таблицы. Вы можете использовать предложение WHERE с запросом DELETE для удаления выбранных строк, иначе все записи будут удалены.

Синтаксис

Ниже приведен базовый синтаксис запроса DELETE с предложением WHERE.

DELETE FROM table_name
WHERE [condition];

Вы можете комбинировать N количество условий с использованием операторов И или ИЛИ.

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример УДАЛЕНИЯ клиента с идентификатором 7.

sqlite> DELETE FROM COMPANY WHERE ID = 7;

Теперь в таблице КОМПАНИЯ будут следующие записи.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

Если вы хотите УДАЛИТЬ все записи из таблицы COMPANY, вам не нужно использовать предложение WHERE с запросом DELETE, который будет следующим:

sqlite> DELETE FROM COMPANY;

Теперь в таблице COMPANY нет записей, так как все записи были удалены оператором DELETE.

SQLite LIKEОператор используется для сопоставления текстовых значений с шаблоном с использованием подстановочных знаков. Если выражение поиска может быть сопоставлено с выражением шаблона, оператор LIKE вернет истину, которая равна 1. В сочетании с оператором LIKE используются два подстановочных символа -

• Знак процента (%)
• Подчеркивание (_)

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

Синтаксис

Ниже приведен основной синтаксис% и _.

SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or 
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

Вы можете комбинировать Nколичество условий с использованием операторов И или ИЛИ. Здесь XXXX может быть любым числовым или строковым значением.

пример

В следующей таблице перечислены несколько примеров, показывающих, что часть WHERE имеет различное предложение LIKE с операторами '%' и '_'.

Sr.No.	Заявление и описание
1	WHERE SALARY LIKE '200%' Находит любые значения, начинающиеся с 200
2	WHERE SALARY LIKE '%200%' Находит любые значения, у которых 200 в любой позиции
3	WHERE SALARY LIKE '_00%' Находит любые значения, у которых на второй и третьей позициях стоит 00
4	WHERE SALARY LIKE '2_%_%' Находит любые значения, которые начинаются с 2 и имеют длину не менее 3 символов
5	WHERE SALARY LIKE '%2' Находит любые значения, заканчивающиеся на 2
6	WHERE SALARY LIKE '_2%3' Находит любые значения, у которых 2 во второй позиции и заканчивается на 3
7	WHERE SALARY LIKE '2___3' Находит любые значения в пятизначном числе, которое начинается с 2 и заканчивается на 3

Давайте возьмем реальный пример, рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример, в котором будут отображаться все записи из таблицы COMPANY, где AGE начинается с 2.

sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример, в котором будут отображаться все записи из таблицы КОМПАНИЯ, где АДРЕС будет иметь дефис (-) внутри текста.

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

SQLite GLOBОператор используется для сопоставления только текстовых значений с шаблоном с использованием подстановочных знаков. Если выражение поиска можно сопоставить с выражением шаблона, оператор GLOB вернет истину, которая равна 1. В отличие от оператора LIKE, GLOB чувствителен к регистру и следует синтаксису UNIX для указания следующих подстановочных знаков.

• Знак звездочки (*)
• Знак вопроса (?)

Знак звездочки (*) означает ноль или несколько цифр или символов. Знак вопроса (?) Представляет собой одну цифру или символ.

Синтаксис

Ниже приводится базовый синтаксис * и ?.

SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or 
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'

Вы можете комбинировать Nколичество условий с использованием операторов И или ИЛИ. Здесь XXXX может быть любым числовым или строковым значением.

пример

В следующей таблице перечислены несколько примеров, показывающих, что часть WHERE имеет разные предложения LIKE с '*' и '?' операторы.

Sr.No.	Заявление и описание
1	WHERE SALARY GLOB '200*' Находит любые значения, начинающиеся с 200
2	WHERE SALARY GLOB '*200*' Находит любые значения, у которых 200 в любой позиции
3	WHERE SALARY GLOB '?00*' Находит любые значения, у которых на второй и третьей позициях стоит 00
4	WHERE SALARY GLOB '2??' Находит любые значения, которые начинаются с 2 и имеют длину не менее 3 символов
5	WHERE SALARY GLOB '*2' Находит любые значения, заканчивающиеся на 2
6	WHERE SALARY GLOB '?2*3' Находит любые значения, у которых во второй позиции стоит 2 и заканчивается 3
7	WHERE SALARY GLOB '2???3' Находит любые значения в пятизначном числе, начинающиеся с 2 и заканчивающиеся на 3

Давайте возьмем реальный пример, рассмотрим таблицу COMPANY со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример, в котором будут отображаться все записи из таблицы COMPANY, где AGE начинается с 2.

sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB '2*';

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример, в котором будут отображаться все записи из таблицы КОМПАНИЯ, где АДРЕС будет иметь дефис (-) внутри текста -

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  GLOB '*-*';

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

SQLite LIMIT Предложение используется для ограничения количества данных, возвращаемых оператором SELECT.

Синтаксис

Ниже приведен базовый синтаксис оператора SELECT с предложением LIMIT.

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

Ниже приводится синтаксис предложения LIMIT, когда оно используется вместе с предложением OFFSET.

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

Механизм SQLite будет возвращать строки, начиная со следующей строки до данного СМЕЩЕНИЯ, как показано ниже в последнем примере.

пример

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

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

sqlite> SELECT * FROM COMPANY LIMIT 6;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

Однако в определенных ситуациях вам может потребоваться выбрать набор записей с определенного смещения. Вот пример, который берет 3 записи, начиная с 3- ^й позиции.

sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite ORDER BY Предложение используется для сортировки данных в порядке возрастания или убывания на основе одного или нескольких столбцов.

Синтаксис

Ниже приведен основной синтаксис предложения ORDER BY.

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

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

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример сортировки результатов в порядке убывания по ЗАРПЛАТЕ.

sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Ниже приведен пример сортировки результатов в порядке убывания по ИМЕНИ и ЗАРПЛАТУ.

sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
5           David       27          Texas       85000.0
7           James       24          Houston     10000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0

Ниже приведен пример сортировки результатов по ИМЯ в порядке убывания.

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
1           Paul        32          California  20000.0
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
5           David       27          Texas       85000.0
2           Allen       25          Texas       15000.0

SQLite GROUP BY Предложение используется в сотрудничестве с оператором SELECT для организации идентичных данных в группы.

Предложение GROUP BY следует за предложением WHERE в операторе SELECT и предшествует предложению ORDER BY.

Синтаксис

Ниже приведен основной синтаксис предложения GROUP BY. Предложение GROUP BY должно соответствовать условиям в предложении WHERE и должно предшествовать предложению ORDER BY, если оно используется.

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

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

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Если вы хотите узнать общую сумму заработной платы каждого клиента, запрос GROUP BY будет следующим:

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

Это даст следующий результат -

NAME        SUM(SALARY)
----------  -----------
Allen       15000.0
David       85000.0
James       10000.0
Kim         45000.0
Mark        65000.0
Paul        20000.0
Teddy       20000.0

Теперь давайте создадим еще три записи в таблице COMPANY, используя следующие операторы INSERT.

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );

Теперь в нашей таблице есть следующие записи с повторяющимися именами.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

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

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

Это даст следующий результат.

NAME        SUM(SALARY)
----------  -----------
Allen       15000
David       85000
James       20000
Kim         45000
Mark        65000
Paul        40000
Teddy       20000

Давайте использовать предложение ORDER BY вместе с предложением GROUP BY следующим образом:

sqlite>  SELECT NAME, SUM(SALARY) 
   FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

Это даст следующий результат.

NAME        SUM(SALARY)
----------  -----------
Teddy       20000
Paul        40000
Mark        65000
Kim         45000
James       20000
David       85000
Allen       15000

Предложение HAVING позволяет указать условия, которые фильтруют результаты группы, которые появляются в окончательных результатах.

Предложение WHERE помещает условия в выбранные столбцы, тогда как предложение HAVING помещает условия в группы, созданные предложением GROUP BY.

Синтаксис

Ниже показано положение предложения HAVING в запросе SELECT.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Предложение HAVING должно следовать за предложением GROUP BY в запросе, а также должно предшествовать предложению ORDER BY, если оно используется. Ниже приводится синтаксис оператора SELECT, включая предложение HAVING.

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

Ниже приведен пример, в котором будет отображаться запись, для которой количество имен меньше 2.

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000
5           David       27          Texas       85000
6           Kim         22          South-Hall  45000
4           Mark        25          Rich-Mond   65000
3           Teddy       23          Norway      20000

Ниже приведен пример, в котором будет отображаться запись, для которой счетчик имен больше 2.

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
10          James       45          Texas       5000

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

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

Синтаксис

Ниже приводится базовый синтаксис ключевого слова DISTINCT для устранения повторяющихся записей.

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

Во-первых, давайте посмотрим, как следующий запрос SELECT возвращает повторяющиеся записи о зарплате.

sqlite> SELECT name FROM COMPANY;

Это даст следующий результат.

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James
Paul
James
James

Теперь давайте использовать DISTINCT ключевое слово с указанным выше запросом SELECT и посмотрите результат.

sqlite> SELECT DISTINCT name FROM COMPANY;

Это приведет к следующему результату, в котором нет повторяющейся записи.

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James

SQLite PRAGMAcommand - это специальная команда, которая используется для управления различными переменными среды и флагами состояния в среде SQLite. Значение PRAGMA можно прочитать, а также его можно установить в зависимости от требований.

Синтаксис

Чтобы запросить текущее значение PRAGMA, просто укажите имя прагмы.

PRAGMA pragma_name;

Чтобы установить новое значение для PRAGMA, используйте следующий синтаксис.

PRAGMA pragma_name = value;

Режим установки может быть именем или целочисленным эквивалентом, но возвращаемое значение всегда будет целым числом.

auto_vacuum Pragma

В auto_vacuumpragma получает или устанавливает режим автоматического вакуумирования. Ниже приводится простой синтаксис.

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

где mode может быть любым из следующих -

Sr.No.	Ценность и описание Pragma
1	0 or NONE Автоматический вакуум отключен. Это режим по умолчанию, который означает, что размер файла базы данных никогда не уменьшится, если он не будет очищен вручную с помощью команды VACUUM.
2	1 or FULL Автоматический вакуум включен и полностью автоматический, что позволяет уменьшить размер файла базы данных по мере удаления данных из базы данных.
3	2 or INCREMENTAL Автоматический вакуум включен, но его необходимо активировать вручную. В этом режиме справочные данные сохраняются, но свободные страницы просто помещаются в список свободных. Эти страницы можно восстановить с помощьюincremental_vacuum pragma любое время.

cache_size Pragma

В cache_sizepragma может получить или временно установить максимальный размер кеша страниц в памяти. Ниже приводится простой синтаксис.

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

В pagesvalue представляет количество страниц в кеше. Размер встроенного кэша страниц по умолчанию составляет 2000 страниц, а минимальный размер - 10 страниц.

case_sensitive_like Pragma

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

PRAGMA case_sensitive_like = [true|false];

Невозможно запросить текущее состояние этой прагмы.

count_changes Pragma

count_changespragma получает или устанавливает возвращаемое значение операторов обработки данных, таких как INSERT, UPDATE и DELETE. Ниже приводится простой синтаксис.

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

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

database_list Pragma

В database_listpragma будет использоваться для вывода списка всех прикрепленных баз данных. Ниже приводится простой синтаксис.

PRAGMA database_list;

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

кодирование Pragma

В encodingpragma контролирует, как строки кодируются и хранятся в файле базы данных. Ниже приводится простой синтаксис.

PRAGMA encoding;
PRAGMA encoding = format;

Значение формата может быть одним из UTF-8, UTF-16le, или же UTF-16be.

freelist_count Pragma

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

PRAGMA [database.]freelist_count;

Значение формата может быть одним из UTF-8, UTF-16le, или же UTF-16be.

index_info Pragma

В index_infopragma возвращает информацию об индексе базы данных. Ниже приводится простой синтаксис.

PRAGMA [database.]index_info( index_name );

Набор результатов будет содержать по одной строке для каждого столбца, содержащегося в индексе, с указанием последовательности столбцов, индекса столбца с таблицей и имени столбца.

index_list Pragma

index_listpragma перечисляет все индексы, связанные с таблицей. Ниже приводится простой синтаксис.

PRAGMA [database.]index_list( table_name );

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

journal_mode Pragma

В journal_modepragma получает или устанавливает режим журнала, который контролирует, как файл журнала хранится и обрабатывается. Ниже приводится простой синтаксис.

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

Существует пять поддерживаемых режимов журнала, перечисленных в следующей таблице.

Sr.No.	Ценность и описание Pragma
1	DELETE Это режим "по умолчанию". Здесь при завершении транзакции файл журнала удаляется.
2	TRUNCATE Файл журнала обрезается до нулевой длины.
3	PERSIST Файл журнала остается на месте, но заголовок перезаписывается, чтобы указать, что журнал больше не действителен.
4	MEMORY Запись журнала хранится в памяти, а не на диске.
5	OFF Записи в журнале не ведутся.

max_page_count Pragma

В max_page_countpragma получает или устанавливает максимально допустимое количество страниц для базы данных. Ниже приводится простой синтаксис.

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

Значение по умолчанию - 1 073 741 823, что составляет одну гига-страницу, что означает, что если размер страницы по умолчанию 1 КБ, это позволяет базам данных увеличиваться до одного терабайта.

page_count Pragma

В page_countpragma возвращает текущее количество страниц в базе данных. Ниже приводится простой синтаксис -

PRAGMA [database.]page_count;

Размер файла базы данных должен быть page_count * page_size.

page_size Pragma

В page_sizepragma получает или устанавливает размер страниц базы данных. Ниже приводится простой синтаксис.

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

По умолчанию допустимые размеры: 512, 1024, 2048, 4096, 8192, 16384 и 32768 байт. Единственный способ изменить размер страницы в существующей базе данных - это установить размер страницы, а затем немедленно очистить базу данных.

parser_trace Pragma

В parser_tracepragma управляет печатью состояния отладки при анализе команд SQL. Ниже приводится простой синтаксис.

PRAGMA parser_trace = [true|false];

По умолчанию для него установлено значение false, но при включении, задав для него значение true, анализатор SQL будет печатать свое состояние при анализе команд SQL.

recursive_triggers Pragma

В recursive_triggerspragma получает или устанавливает функциональность рекурсивного триггера. Если рекурсивные триггеры не включены, действие триггера не вызовет другого триггера. Ниже приводится простой синтаксис.

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

schema_version Pragma

В schema_versionpragma получает или устанавливает значение версии схемы, которое хранится в заголовке базы данных. Ниже приводится простой синтаксис.

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

Это 32-битное целое число со знаком, которое отслеживает изменения схемы. Каждый раз, когда выполняется команда изменения схемы (например, CREATE ... или DROP ...), это значение увеличивается.

secure_delete Pragma

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

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

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

sql_trace Pragma

В sql_tracepragma используется для вывода результатов трассировки SQL на экран. Ниже приводится простой синтаксис.

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

SQLite должен быть скомпилирован с директивой SQLITE_DEBUG, чтобы эта прагма была включена.

синхронная прагма

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

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLite поддерживает следующие режимы синхронизации, перечисленные в таблице.

Sr.No.	Ценность и описание Pragma
1	0 or OFF Никаких синхронизаций
2	1 or NORMAL Синхронизация после каждой последовательности критических дисковых операций
3	2 or FULL Синхронизация после каждой критической дисковой операции

temp_store Pragma

В temp_storepragma получает или устанавливает режим хранения, используемый для временных файлов базы данных. Ниже приводится простой синтаксис.

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite поддерживает следующие режимы хранения.

Sr.No.	Ценность и описание Pragma
1	0 or DEFAULT Использовать значение по умолчанию времени компиляции. Обычно FILE.
2	1 or FILE Используйте файловое хранилище.
3	2 or MEMORY Используйте хранилище на основе памяти.

temp_store_directory Pragma

В temp_store_directorypragma получает или устанавливает местоположение, используемое для временных файлов базы данных. Ниже приводится простой синтаксис.

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

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

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

Это 32-битное целое число со знаком, которое может быть установлено разработчиком для отслеживания версий.

Writable_schema Pragma

В writable_schemapragma получает или устанавливает возможность изменения системных таблиц. Ниже приводится простой синтаксис.

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

Если эта прагма установлена, можно создавать и изменять таблицы, начинающиеся с sqlite_, включая таблицу sqlite_master. Будьте осторожны при использовании прагмы, так как это может привести к полному повреждению базы данных.

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

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

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

• NOT NULL Constraint - Гарантирует, что столбец не может иметь значение NULL.

• DEFAULT Constraint - Предоставляет значение по умолчанию для столбца, если ничего не указано.

• UNIQUE Constraint - Гарантирует, что все значения в столбце различны.

• PRIMARY Key - Однозначно идентифицирует каждую строку / запись в таблице базы данных.

• CHECK Constraint - Гарантирует, что все значения в столбце удовлетворяют определенным условиям.

NOT NULL ограничение

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

NULL - это не то же самое, что нет данных, скорее, он представляет неизвестные данные.

пример

Например, следующий оператор SQLite создает новую таблицу с именем COMPANY и добавляет пять столбцов, три из которых, ID, NAME и AGE, запрещают принимать значения NULL.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

DEFAULT Ограничение

Ограничение DEFAULT предоставляет значение по умолчанию для столбца, когда оператор INSERT INTO не предоставляет конкретное значение.

пример

Например, следующий оператор SQLite создает новую таблицу с именем КОМПАНИЯ и добавляет пять столбцов. Здесь для столбца SALARY по умолчанию установлено значение 5000,00, поэтому, если оператор INSERT INTO не предоставляет значение для этого столбца, тогда по умолчанию для этого столбца будет установлено значение 5000,00.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

Уникальное ограничение

Ограничение UNIQUE не позволяет двум записям иметь одинаковые значения в определенном столбце. Например, в таблице «КОМПАНИЯ» вы можете запретить двум или более людям совпадать возраст.

пример

Например, следующий оператор SQLite создает новую таблицу с именем КОМПАНИЯ и добавляет пять столбцов. Здесь для столбца AGE установлено значение UNIQUE, поэтому у вас не может быть двух записей с одинаковым возрастом -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

Ограничение PRIMARY KEY

Ограничение PRIMARY KEY однозначно идентифицирует каждую запись в таблице базы данных. В таблице может быть больше УНИКАЛЬНЫХ столбцов, но только один первичный ключ. Первичные ключи важны при разработке таблиц базы данных. Первичные ключи - это уникальные идентификаторы.

Мы используем их для обозначения строк таблицы. Первичные ключи становятся внешними ключами в других таблицах при создании отношений между таблицами. Из-за «давнего надзора за кодированием» первичные ключи в SQLite могут иметь значение NULL. Это не относится к другим базам данных.

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

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

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

пример

Вы уже видели различные примеры выше, где мы создали таблицу COMPANY с идентификатором в качестве первичного ключа.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

ПРОВЕРИТЬ ограничение

CHECK Constraint позволяет условию проверить значение, вводимое в запись. Если условие оценивается как ложное, запись нарушает ограничение и не вводится в таблицу.

пример

Например, следующий SQLite создает новую таблицу с именем COMPANY и добавляет пять столбцов. Здесь мы добавляем ПРОВЕРКУ с столбцом SALARY, чтобы у вас не могло быть нулевой SALARY.

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

Удаление ограничения

SQLite поддерживает ограниченное подмножество ALTER TABLE. Команда ALTER TABLE в SQLite позволяет пользователю переименовать таблицу или добавить новый столбец в существующую таблицу. Невозможно переименовать столбец, удалить столбец, а также добавить или удалить ограничения из таблицы.

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

SQL определяет три основных типа объединений:

• КРЕСТ ПРИСОЕДИНЯТЬСЯ
• ВНУТРЕННЕЕ СОЕДИНЕНИЕ
• ВНЕШНЕЕ СОЕДИНЕНИЕ

Прежде чем продолжить, давайте рассмотрим две таблицы КОМПАНИЯ и ОТДЕЛ. Мы уже видели инструкции INSERT для заполнения таблицы COMPANY. Итак, давайте предположим список записей, доступных в таблице COMPANY -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Другая таблица - это DEPARTMENT со следующим определением -

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Вот список операторов INSERT для заполнения таблицы DEPARTMENT -

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Наконец, у нас есть следующий список записей, доступных в таблице DEPARTMENT -

ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineering 2
3           Finance     7

КРЕСТ ПРИСОЕДИНЯТЬСЯ

CROSS JOIN сопоставляет каждую строку первой таблицы с каждой строкой второй таблицы. Если во входных таблицах есть строки x и y, соответственно, результирующая таблица будет иметь строку x * y. Поскольку CROSS JOIN могут создавать очень большие таблицы, следует проявлять осторожность, чтобы использовать их только тогда, когда это необходимо.

Ниже приведен синтаксис CROSS JOIN -

SELECT ... FROM table1 CROSS JOIN table2 ...

Основываясь на приведенных выше таблицах, вы можете написать CROSS JOIN следующим образом:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

Вышеупомянутый запрос даст следующий результат -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineering
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineering
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineering
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineering
7           Mark        Finance
1           David       IT Billing
2           David       Engineering
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineering
7           Kim         Finance
1           James       IT Billing
2           James       Engineering
7           James       Finance

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

INNER JOIN создает новую таблицу результатов, объединяя значения столбцов двух таблиц (table1 и table2) на основе предиката соединения. Запрос сравнивает каждую строку table1 с каждой строкой table2, чтобы найти все пары строк, которые удовлетворяют предикату соединения. Когда предикат соединения удовлетворяется, значения столбцов для каждой совпавшей пары строк A и B объединяются в строку результата.

ВНУТРЕННЕЕ СОЕДИНЕНИЕ - это наиболее распространенный тип соединения по умолчанию. При желании вы можете использовать ключевое слово INNER.

Ниже приведен синтаксис INNER JOIN -

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

Чтобы избежать избыточности и сделать формулировку короче, условия INNER JOIN могут быть объявлены с помощью USINGвыражение. Это выражение определяет список из одного или нескольких столбцов.

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ похоже на JOIN...USING, только он автоматически проверяет равенство между значениями каждого столбца, который существует в обеих таблицах -

SELECT ... FROM table1 NATURAL JOIN table2...

На основе приведенных выше таблиц вы можете написать INNER JOIN следующим образом:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Вышеупомянутый запрос даст следующий результат -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
7           James       Finance

ВНЕШНЕЕ СОЕДИНЕНИЕ

OUTER JOIN - это расширение INNER JOIN. Хотя стандарт SQL определяет три типа ВНЕШНИХ СОЕДИНЕНИЙ: LEFT, RIGHT и FULL, SQLite поддерживает толькоLEFT OUTER JOIN.

ВНЕШНИЕ СОЕДИНЕНИЯ имеют условие, идентичное ВНУТРЕННИМ СОЕДИНЕНИЯМ, выраженное с помощью ключевого слова ON, USING или NATURAL. Таблица исходных результатов рассчитывается аналогично. Как только основное соединение JOIN вычислено, OUTER JOIN возьмет все несоединенные строки из одной или обеих таблиц, дополнит их значениями NULL и добавит их в результирующую таблицу.

Ниже приведен синтаксис LEFT OUTER JOIN -

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Чтобы избежать дублирования и сделать формулировку короче, условия OUTER JOIN могут быть объявлены с помощью выражения USING. Это выражение определяет список из одного или нескольких столбцов.

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

На основе приведенных выше таблиц вы можете написать внутреннее соединение следующим образом:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

Вышеупомянутый запрос даст следующий результат -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
            Teddy
            Mark
            David
            Kim
7           James       Finance

SQLite UNION Предложение / оператор используется для объединения результатов двух или более операторов SELECT без возврата повторяющихся строк.

Чтобы использовать UNION, каждый SELECT должен иметь одинаковое количество выбранных столбцов, одинаковое количество выражений столбцов, один и тот же тип данных и иметь их в одинаковом порядке, но они не должны быть одинаковой длины.

Синтаксис

Ниже приводится базовый синтаксис UNION.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Здесь данное условие может быть любым заданным выражением в зависимости от вашего требования.

пример

Рассмотрим следующие две таблицы: (а) Таблица КОМПАНИИ :

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

(b) Другая таблица - это ОТДЕЛЕНИЕ :

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

Теперь давайте объединим эти две таблицы, используя оператор SELECT вместе с предложением UNION следующим образом:

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID
         
         UNION
         
         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Это даст следующий результат.

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

Статья UNION ALL

Оператор UNION ALL используется для объединения результатов двух операторов SELECT, включая повторяющиеся строки.

Те же правила, которые применяются к UNION, применимы и к оператору UNION ALL.

Синтаксис

Ниже приводится базовый синтаксис UNION ALL.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Здесь данное условие может быть любым заданным выражением в зависимости от вашего требования.

пример

Теперь давайте объединим две вышеупомянутые таблицы в нашем операторе SELECT следующим образом:

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID
         
         UNION ALL

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Это даст следующий результат.

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

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

Поле со значением NULL - это поле без значения. Очень важно понимать, что значение NULL отличается от нулевого значения или поля, содержащего пробелы.

Синтаксис

Ниже приведен основной синтаксис использования NULL при создании таблицы.

SQLite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

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

Поле со значением NULL - это поле, которое было оставлено пустым во время создания записи.

пример

Значение NULL может вызвать проблемы при выборе данных, поскольку при сравнении неизвестного значения с любым другим значением результат всегда неизвестен и не включается в окончательные результаты. Рассмотрим следующую таблицу, КОМПАНИЯ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Давайте использовать оператор UPDATE, чтобы установить несколько значений NULL как NULL следующим образом:

sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Теперь в таблице COMPANY будут следующие записи.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22
7           James       24

Затем давайте посмотрим, как использовать IS NOT NULL для вывода списка всех записей, в которых SALARY не равно NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;

Вышеупомянутый оператор SQLite даст следующий результат -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Ниже приводится использование IS NULL , который выведет список всех записей, в которых SALARY равно NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

Вышеупомянутый оператор SQLite даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22
7           James       24

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

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

Синтаксис

Ниже приводится базовый синтаксис table псевдоним.

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

Ниже приводится базовый синтаксис column псевдоним.

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

пример

Рассмотрим следующие две таблицы: (а) Таблица КОМПАНИИ выглядит следующим образом:

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

(b) Другая таблица - это ОТДЕЛЕНИЕ :

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

Теперь следующее использование TABLE ALIAS где мы используем C и D как псевдонимы для таблиц COMPANY и DEPARTMENT соответственно -

sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

Вышеупомянутый оператор SQLite даст следующий результат -

ID          NAME        AGE         DEPT
----------  ----------  ----------  ----------
1           Paul        32          IT Billing
2           Allen       25          Engineering
3           Teddy       23          Engineering
4           Mark        25          Finance
5           David       27          Engineering
6           Kim         22          Finance
7           James       24          Finance

Рассмотрим пример использования COLUMN ALIAS где COMPANY_ID - это псевдоним столбца идентификатора, а COMPANY_NAME - псевдоним столбца имени.

sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

Вышеупомянутый оператор SQLite даст следующий результат -

COMPANY_ID  COMPANY_NAME  AGE         DEPT
----------  ------------  ----------  ----------
1           Paul          32          IT Billing
2           Allen         25          Engineering
3           Teddy         23          Engineering
4           Mark          25          Finance
5           David         27          Engineering
6           Kim           22          Finance
7           James         24          Finance

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

• Можно указать триггер SQLite для срабатывания всякий раз, когда происходит DELETE, INSERT или UPDATE для конкретной таблицы базы данных или всякий раз, когда происходит UPDATE в одном или нескольких указанных столбцах таблицы.

• В настоящее время SQLite поддерживает только триггеры FOR EACH ROW, но не триггеры FOR EACH STATEMENT. Следовательно, явное указание FOR EACH ROW необязательно.

• И предложение WHEN, и действия триггера могут обращаться к элементам вставляемой, удаляемой или обновляемой строки с использованием ссылок в форме NEW.column-name и OLD.column-name, где имя-столбца - это имя столбца из таблицы, с которой связан триггер.

• Если предоставляется предложение WHEN, указанные операторы SQL выполняются только для строк, для которых предложение WHEN истинно. Если предложение WHEN не указано, операторы SQL выполняются для всех строк.

• Ключевое слово BEFORE или AFTER определяет, когда будут выполняться действия триггера относительно вставки, изменения или удаления связанной строки.

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

• Изменяемая таблица должна существовать в той же базе данных, что и таблица или представление, к которому привязан триггер, и нужно использовать только tablename не database.tablename.

• Специальная функция SQL RAISE () может использоваться в программе-триггере для вызова исключения.

Синтаксис

Ниже приведен базовый синтаксис создания trigger.

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

Вот, event_nameможет быть операция INSERT, DELETE и UPDATE базы данных в указанной таблицеtable_name. Вы можете дополнительно указать FOR EACH ROW после имени таблицы.

Ниже приводится синтаксис для создания триггера для операции UPDATE для одного или нескольких указанных столбцов таблицы.

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

пример

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

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Чтобы продолжить аудит, мы создадим новую таблицу под названием AUDIT, куда будут вставляться сообщения журнала всякий раз, когда в таблице COMPANY есть запись для новой записи.

sqlite> CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Здесь ID - это идентификатор записи AUDIT, а EMP_ID - это идентификатор, который будет взят из таблицы COMPANY, а DATE сохранит метку времени, когда запись будет создана в таблице COMPANY. Теперь давайте создадим триггер для таблицы COMPANY следующим образом:

sqlite> CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

Теперь мы приступим к реальной работе. Давайте начнем вставлять запись в таблицу COMPANY, что должно привести к созданию записи журнала аудита в таблице AUDIT. Создайте одну запись в таблице КОМПАНИИ следующим образом:

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

Это создаст одну запись в таблице COMPANY, которая выглядит следующим образом:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

В то же время в таблице AUDIT будет создана одна запись. Эта запись является результатом триггера, который мы создали для операции INSERT в таблице COMPANY. Точно так же вы можете создавать свои триггеры для операций UPDATE и DELETE в зависимости от ваших требований.

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

Листинг триггеров

Вы можете перечислить все триггеры из sqlite_master таблица следующим образом -

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

Вышеупомянутый оператор SQLite перечислит только одну запись следующим образом:

name
----------
audit_log

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

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

Вышеупомянутый оператор SQLite также перечислит только одну запись следующим образом:

name
----------
audit_log

Отбрасывание триггеров

Ниже приводится команда DROP, которую можно использовать для отбрасывания существующего триггера.

sqlite> DROP TRIGGER trigger_name;

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

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

Индекс помогает ускорить запросы SELECT и предложения WHERE, но замедляет ввод данных с помощью операторов UPDATE и INSERT. Индексы можно создавать или удалять, не влияя на данные.

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

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

Команда CREATE INDEX

Ниже приводится базовый синтаксис CREATE INDEX.

CREATE INDEX index_name ON table_name;

Одностолбцовые индексы

Индекс с одним столбцом - это индекс, который создается на основе только одного столбца таблицы. Основной синтаксис выглядит следующим образом -

CREATE INDEX index_name
ON table_name (column_name);

Уникальные индексы

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

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Составные индексы

Составной индекс - это индекс по двум или более столбцам таблицы. Основной синтаксис выглядит следующим образом -

CREATE INDEX index_name
on table_name (column1, column2);

Независимо от того, создавать ли индекс с одним столбцом или составной индекс, примите во внимание столбцы, которые вы можете очень часто использовать в предложении WHERE запроса в качестве условий фильтрации.

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

Неявные индексы

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

Example

Ниже приведен пример, в котором мы создадим индекс в таблице КОМПАНИИ для столбца зарплаты.

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Теперь давайте перечислим все индексы, доступные в таблице COMPANY, используя .indices команда следующим образом -

sqlite> .indices COMPANY

Это приведет к следующему результату, где sqlite_autoindex_COMPANY_1 - это неявный индекс, который был создан при создании самой таблицы.

salary_index
sqlite_autoindex_COMPANY_1

Вы можете перечислить всю базу данных индексов следующим образом:

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

Команда DROP INDEX

Индекс можно удалить с помощью SQLite DROPкоманда. При удалении индекса следует соблюдать осторожность, поскольку производительность может снизиться или повыситься.

Ниже приведен базовый синтаксис:

DROP INDEX index_name;

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

sqlite> DROP INDEX salary_index;

Когда следует избегать индексов?

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

Индексы не должны использоваться в -

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

Предложение «INDEXED BY index-name» указывает, что именованный индекс должен использоваться для поиска значений в предыдущей таблице.

Если имя-индекса не существует или не может использоваться для запроса, подготовка оператора SQLite завершается неудачей.

Предложение «NOT INDEXED» указывает, что никакой индекс не должен использоваться при доступе к предыдущей таблице, включая подразумеваемые индексы, созданные ограничениями UNIQUE и PRIMARY KEY.

Однако INTEGER PRIMARY KEY по-прежнему можно использовать для поиска записей, даже если указано «NOT INDEXED».

Синтаксис

Ниже приведен синтаксис предложения INDEXED BY, который можно использовать с операторами DELETE, UPDATE или SELECT.

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

пример

Рассмотрим таблицу COMPANY. Мы создадим индекс и будем использовать его для выполнения операции INDEXED BY.

sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

Теперь, выбирая данные из таблицы COMPANY, вы можете использовать предложение INDEXED BY следующим образом:

sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

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

Команда ALTER TABLE в SQLite не поддерживает никаких других операций, кроме переименования таблицы и добавления столбца в существующую таблицу.

Синтаксис

Ниже приводится базовый синтаксис ALTER TABLE ПЕРЕИМЕНОВАТЬ существующую таблицу.

ALTER TABLE database_name.table_name RENAME TO new_table_name;

Ниже приводится базовый синтаксис ALTER TABLE чтобы добавить новый столбец в существующую таблицу.

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

пример

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Теперь давайте попробуем переименовать эту таблицу с помощью оператора ALTER TABLE следующим образом:

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

Приведенный выше оператор SQLite переименует таблицу COMPANY в OLD_COMPANY. Теперь давайте попробуем добавить новый столбец в таблицу OLD_COMPANY следующим образом:

sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

Таблица COMPANY теперь изменена, и ниже будет вывод оператора SELECT.

ID          NAME        AGE         ADDRESS     SALARY      SEX
----------  ----------  ----------  ----------  ----------  ---
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Следует отметить, что вновь добавленный столбец заполняется значениями NULL.

К сожалению, у нас нет команды TRUNCATE TABLE в SQLite, но вы можете использовать SQLite DELETE для удаления полных данных из существующей таблицы, хотя рекомендуется использовать команду DROP TABLE, чтобы удалить всю таблицу и заново создать ее.

Синтаксис

Ниже приведен основной синтаксис команды DELETE.

sqlite> DELETE FROM table_name;

Ниже приведен основной синтаксис DROP TABLE.

sqlite> DROP TABLE table_name;

Если вы используете команду DELETE TABLE для удаления всех записей, рекомендуется использовать VACUUM команда для очистки неиспользуемого пространства.

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример усечения приведенной выше таблицы.

SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;

Теперь таблица COMPANY полностью усечена, и на выходе из оператора SELECT ничего не будет.

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

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

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

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

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

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

Представления SQLite доступны только для чтения, и поэтому вы не сможете выполнить оператор DELETE, INSERT или UPDATE для представления. Однако вы можете создать триггер для представления, который срабатывает при попытке УДАЛИТЬ, ВСТАВИТЬ или ОБНОВИТЬ представление и делать то, что вам нужно, в теле триггера.

Создание представлений

Представления SQLite создаются с использованием CREATE VIEWзаявление. Представления SQLite могут быть созданы из одной таблицы, нескольких таблиц или другого представления.

Ниже приводится базовый синтаксис CREATE VIEW.

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Вы можете включить несколько таблиц в свой оператор SELECT так же, как вы используете их в обычном запросе SQL SELECT. Если присутствует необязательное ключевое слово TEMP или TEMPORARY, представление будет создано в базе данных temp.

пример

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Ниже приведен пример создания представления из таблицы COMPANY. Это представление будет использоваться, чтобы иметь только несколько столбцов из таблицы COMPANY.

sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

Теперь вы можете запросить COMPANY_VIEW так же, как вы запрашиваете фактическую таблицу. Ниже приведен пример -

sqlite> SELECT * FROM COMPANY_VIEW;

Это даст следующий результат.

ID          NAME        AGE
----------  ----------  ----------
1           Paul        32
2           Allen       25
3           Teddy       23
4           Mark        25
5           David       27
6           Kim         22
7           James       24

Удаление просмотров

Чтобы удалить представление, просто используйте оператор DROP VIEW с view_name. Базовый синтаксис DROP VIEW следующий:

sqlite> DROP VIEW view_name;

Следующая команда удалит представление COMPANY_VIEW, которое мы создали в предыдущем разделе.

sqlite> DROP VIEW COMPANY_VIEW;

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

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

Фактически вы объедините множество запросов SQLite в группу и будете выполнять их все вместе как часть транзакции.

Свойства транзакций

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID.

• Atomicity- Гарантирует, что все операции в рамках единицы работы завершены успешно; в противном случае транзакция прерывается в момент сбоя, а предыдущие операции возвращаются в исходное состояние.

• Consistency - Гарантирует, что база данных правильно меняет состояния после успешно зафиксированной транзакции.

• Isolation - Позволяет транзакциям работать независимо и прозрачно друг для друга.

• Durability - Гарантирует сохранение результата или эффекта зафиксированной транзакции в случае сбоя системы.

Контроль транзакций

Ниже приведены следующие команды, используемые для управления транзакциями:

• BEGIN TRANSACTION - Начать сделку.

• COMMIT - Чтобы сохранить изменения, в качестве альтернативы вы можете использовать END TRANSACTION команда.

• ROLLBACK - Отменить изменения.

Команды управления транзакциями используются только с командами DML INSERT, UPDATE и DELETE. Их нельзя использовать при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.

Команда BEGIN TRANSACTION

Транзакции можно запустить с помощью BEGIN TRANSACTION или просто команды BEGIN. Такие транзакции обычно сохраняются до тех пор, пока не встретится следующая команда COMMIT или ROLLBACK. Однако транзакция также откатится, если база данных закрывается или возникает ошибка. Ниже приводится простой синтаксис для начала транзакции.

BEGIN;
or 
BEGIN TRANSACTION;

COMMIT команда

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

Команда COMMIT сохраняет все транзакции в базе данных с момента последней команды COMMIT или ROLLBACK.

Ниже приведен синтаксис команды COMMIT.

COMMIT;
or
END TRANSACTION;

ROLLBACK команда

Команда ROLLBACK - это транзакционная команда, используемая для отмены транзакций, которые еще не были сохранены в базе данных.

Команда ROLLBACK может использоваться только для отмены транзакций с момента выполнения последней команды COMMIT или ROLLBACK.

Ниже приводится синтаксис команды ROLLBACK.

ROLLBACK;

Example

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Теперь давайте запустим транзакцию и удалим записи из таблицы, имеющие возраст = 25. Затем используйте команду ROLLBACK, чтобы отменить все изменения.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;

Теперь, если вы проверите таблицу КОМПАНИИ, в ней все еще есть следующие записи:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Давайте запустим еще одну транзакцию и удалим записи из таблицы, имеющие возраст = 25, и, наконец, мы используем команду COMMIT для фиксации всех изменений.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;

Если вы сейчас проверите, что в таблице КОМПАНИИ все еще есть следующие записи -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Подзапрос, внутренний запрос или вложенный запрос - это запрос внутри другого запроса SQLite, встроенный в предложение WHERE.

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

Подзапросы можно использовать с операторами SELECT, INSERT, UPDATE и DELETE вместе с такими операторами, как =, <,>,> =, <=, IN, BETWEEN и т. Д.

Есть несколько правил, которым должны следовать подзапросы:

• Подзапросы должны быть заключены в круглые скобки.

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

• ORDER BY нельзя использовать в подзапросе, хотя в основном запросе можно использовать ORDER BY. GROUP BY может использоваться для выполнения той же функции, что и ORDER BY в подзапросе.

• Подзапросы, возвращающие более одной строки, можно использовать только с операторами нескольких значений, такими как оператор IN.

• Оператор BETWEEN нельзя использовать с подзапросом; однако BETWEEN можно использовать в подзапросе.

Подзапросы с оператором SELECT

Подзапросы чаще всего используются с оператором SELECT. Основной синтаксис выглядит следующим образом -

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
   (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

пример

Рассмотрим таблицу COMPANY со следующими записями.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Теперь давайте проверим следующий подзапрос с помощью оператора SELECT.

sqlite> SELECT * 
   FROM COMPANY 
   WHERE ID IN (SELECT ID 
      FROM COMPANY 
      WHERE SALARY > 45000) ;

Это даст следующий результат.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Подзапросы с оператором INSERT

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

Ниже приведен базовый синтаксис:

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

пример

Рассмотрим таблицу COMPANY_BKP со структурой, аналогичную таблице COMPANY, и ее можно создать с использованием той же таблицы CREATE TABLE с использованием COMPANY_BKP в качестве имени таблицы. Чтобы скопировать полную таблицу COMPANY в COMPANY_BKP, следующий синтаксис:

sqlite> INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY 
   WHERE ID IN (SELECT ID 
      FROM COMPANY) ;

Подзапросы с оператором UPDATE

Подзапрос можно использовать вместе с оператором UPDATE. При использовании подзапроса с оператором UPDATE можно обновить один или несколько столбцов в таблице.

Ниже приведен базовый синтаксис:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

пример

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

В следующем примере SALARY обновляется в 0,50 раза в таблице COMPANY для всех клиентов, чей AGE больше или равен 27.

sqlite> UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

Это повлияет на две строки, и, наконец, в таблице COMPANY будут следующие записи:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  10000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Подзапросы с оператором DELETE

Подзапрос можно использовать вместе с оператором DELETE, как и с любыми другими операторами, упомянутыми выше.

Ниже приведен базовый синтаксис:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

пример

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

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

sqlite> DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
   WHERE AGE > 27 );

Это повлияет на две строки, и, наконец, таблица COMPANY будет иметь следующие записи:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

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

Ключевое слово AUTOINCREMENT может использоваться только с полем INTEGER.

Синтаксис

Основное использование AUTOINCREMENT ключевое слово выглядит следующим образом -

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

пример

Рассмотрим таблицу КОМПАНИИ, которая будет создана следующим образом -

sqlite> CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Теперь вставьте следующие записи в таблицу COMPANY -

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

Это вставит 7 кортежей в таблицу КОМПАНИЯ, и КОМПАНИЯ будет иметь следующие записи:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

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

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

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

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){ $db = new SQLiteDatabase('filename');
   $result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
   echo "username not accepted";
}

Чтобы продемонстрировать проблему, рассмотрите этот отрывок -

$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = '{$name}'");

Вызов функции должен получить запись из таблицы пользователей, в которой столбец имени совпадает с именем, указанным пользователем. При нормальных обстоятельствах,$nameбудет содержать только буквенно-цифровые символы и, возможно, пробелы, такие как строка ilia. Однако в этом случае, добавляя совершенно новый запрос к $ name, вызов базы данных превращается в катастрофу: введенный запрос DELETE удаляет все записи от пользователей.

Существуют интерфейсы баз данных, которые не позволяют складывать запросы или выполнять несколько запросов за один вызов функции. Если вы попытаетесь объединить запросы в стек, вызов не удастся, но SQLite и PostgreSQL успешно выполнят составные запросы, выполняя все запросы, представленные в одной строке, и создают серьезную проблему безопасности.

Предотвращение внедрения SQL

Вы можете грамотно обрабатывать все escape-символы в таких языках сценариев, как PERL и PHP. Язык программирования PHP предоставляет функциюstring sqlite_escape_string() для экранирования входных символов, специальных для SQLite.

if (get_magic_quotes_gpc()) {
   $name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");

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

Note - addslashes()НЕ следует использовать для цитирования строк в запросах SQLite; это приведет к странным результатам при извлечении ваших данных.

Оператору SQLite может предшествовать ключевое слово «EXPLAIN» или фраза «EXPLAIN QUERY PLAN», используемая для описания деталей таблицы.

Любая модификация заставляет оператор SQLite вести себя как запрос и возвращать информацию о том, как оператор SQLite работал бы, если бы ключевое слово или фраза EXPLAIN были опущены.

• Вывод EXPLAIN и EXPLAIN QUERY PLAN предназначен только для интерактивного анализа и устранения неполадок.

• Детали формата вывода могут изменяться от одного выпуска SQLite к другому.

• Приложения не должны использовать EXPLAIN или EXPLAIN QUERY PLAN, поскольку их точное поведение является переменным и только частично задокументировано.

Синтаксис

синтаксис для EXPLAIN выглядит следующим образом -

EXPLAIN [SQLite Query]

синтаксис для EXPLAIN QUERY PLAN выглядит следующим образом -

EXPLAIN  QUERY PLAN [SQLite Query]

пример

Рассмотрим таблицу КОМПАНИИ со следующими записями -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Теперь давайте проверим следующий подзапрос с помощью оператора SELECT -

sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;

Это даст следующий результат.

addr        opcode      p1          p2          p3
----------  ----------  ----------  ----------  ----------
0           Goto        0           19
1           Integer     0           0
2           OpenRead    0           8
3           SetNumColu  0           5
4           Rewind      0           17
5           Column      0           4
6           RealAffini  0           0
7           Integer     20000       0
8           Lt          357         16          collseq(BI
9           Rowid       0           0
10          Column      0           1
11          Column      0           2
12          Column      0           3
13          Column      0           4
14          RealAffini  0           0
15          Callback    5           0
16          Next        0           5
17          Close       0           0
18          Halt        0           0
19          Transactio  0           0
20          VerifyCook  0           38
21          Goto        0           1
22          Noop        0           0

Теперь давайте проверим следующее Explain Query Plan с оператором SELECT -

SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000;

order       from        detail
----------  ----------  -------------
0           0           TABLE COMPANY

Команда VACUUM очищает основную базу данных, копируя ее содержимое во временный файл базы данных и перезагружая исходный файл базы данных из копии. Это устраняет свободные страницы, выравнивает данные таблицы, чтобы они были смежными, и в противном случае очищает файловую структуру базы данных.

Команда VACUUM может изменить ROWID записей в таблицах, которые не имеют явного INTEGER PRIMARY KEY. Команда VACUUM работает только с основной базой данных. Пылесосить прикрепленный файл базы данных невозможно.

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

Ручной ВАКУУМ

Ниже приведен простой синтаксис для выполнения команды VACUUM для всей базы данных из командной строки.

$sqlite3 database_name "VACUUM;"

Вы можете запустить VACUUM из командной строки SQLite, а также следующим образом:

sqlite> VACUUM;

Вы также можете запустить VACUUM для конкретной таблицы следующим образом:

sqlite> VACUUM table_name;

Авто-ВАККУМ

SQLite Auto-VACUUM не делает то же самое, что VACUUM, а только перемещает свободные страницы в конец базы данных, тем самым уменьшая размер базы данных. Тем самым он может значительно фрагментировать базу данных, в то время как VACUUM обеспечивает дефрагментацию. Следовательно, Auto-VACUUM просто сохраняет базу данных небольшого размера.

Вы можете включить / отключить автоматическую очистку SQLite с помощью следующих прагм, запущенных в приглашении SQLite:

sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum

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

$sqlite3 database_name "PRAGMA auto_vacuum;"

SQLite поддерживает пять функций даты и времени, а именно:

Sr.No.	Функция	пример
1	дата (строка времени, модификаторы ...)	Это возвращает дату в следующем формате: ГГГГ-ММ-ДД.
2	время (строка времени, модификаторы ...)	Это возвращает время как ЧЧ: ММ: СС
3	datetime (строка времени, модификаторы ...)	Это возвращает ГГГГ-ММ-ДД ЧЧ: ММ: СС
4	julianday (временная строка, модификаторы ...)	Это возвращает количество дней с полудня по Гринвичу 24 ноября 4714 г. до н.э.
5	strftime (строка времени, модификаторы ...)	Это возвращает дату, отформатированную в соответствии со строкой формата, указанной в качестве первого аргумента, отформатированного в соответствии с описанными ниже средствами форматирования.

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

Строки времени

Строка времени может быть в любом из следующих форматов:

Sr.No.	Строка времени	пример
1	ГГГГ-ММ-ДД	2010-12-30
2	ГГГГ-ММ-ДД ЧЧ: ММ	2010-12-30 12:10
3	ГГГГ-ММ-ДД ЧЧ: ММ: СС.ССС	2010-12-30 12: 10: 04.100
4	ММ-ДД-ГГГГ ЧЧ: ММ	30-12-2010 12:10
5	ЧЧ: ММ	12:10
6	ГГГГ-ММ-ДДTЧЧ: ММ	2010-12-30 12:10
7	ЧЧ: ММ: СС	12:10:01
8	ГГГГММДД ЧЧММСС	20101230 121001
9	в настоящее время	2013-05-07

Вы можете использовать букву «T» как буквальный символ, разделяющий дату и время.

Модификаторы

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

В SQLite доступны следующие модификаторы -

• NNN дней
• NNN часов
• NNN минут
• NNN.NNNN секунд
• NNN месяцев
• NNN лет
• начало месяца
• начало года
• начало дня
• будний день N
• unixepoch
• localtime
• utc

Форматировщики

SQLite предоставляет очень удобную функцию strftime()для форматирования любой даты и времени. Вы можете использовать следующие замены для форматирования даты и времени.

Замена	Описание
% d	День месяца, 01-31
% f	Дробные секунды, SS.SSS
%ЧАС	Час, 00-23
% j	День года, 001-366
% J	Юлианский день недели, DDDD.DDDD
% m	Месяц, 00-12
% M	Минуты, 00-59
% s	Секунды с 01.01.1970
% S	Секунды, 00-59
% w	День недели, 0-6 (0 - воскресенье)
% W	Неделя года, 01-53
% Y	Год, ГГГГ
%%	% условное обозначение

Примеры

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

sqlite> SELECT date('now');
2013-05-07

Следующая команда вычисляет последний день текущего месяца.

sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31

Следующая команда вычисляет дату и время для данной временной метки UNIX 1092941466.

sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

Следующая команда вычисляет дату и время для данной временной метки UNIX 1092941466 и компенсирует ваш местный часовой пояс.

sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06

Следующая команда вычисляет текущую временную метку UNIX.

sqlite> SELECT strftime('%s','now');
1393348134

Следующая команда вычисляет количество дней с момента подписания Декларации независимости США.

sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023

Следующая команда вычисляет количество секунд с определенного момента в 2004 году.

sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572

Следующая команда вычисляет дату первого вторника октября текущего года.

sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01

Следующая команда вычисляет время с эпохи UNIX в секундах (например, strftime ('% s', 'now'), за исключением дробной части).

sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598

Чтобы преобразовать между значениями UTC и местным временем при форматировании даты, используйте модификаторы utc или localtime следующим образом:

sqlite> SELECT time('12:00', 'localtime');
05:00:00

sqlite> SELECT time('12:00', 'utc');
19:00:00

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

Sr.No.	Описание функции
1	SQLite COUNT Function Агрегатная функция SQLite COUNT используется для подсчета количества строк в таблице базы данных.
2	SQLite MAX Function Агрегатная функция SQLite MAX позволяет выбрать наивысшее (максимальное) значение для определенного столбца.
3	SQLite MIN Function Агрегатная функция SQLite MIN позволяет выбрать наименьшее (минимальное) значение для определенного столбца.
4	SQLite AVG Function Агрегатная функция SQLite AVG выбирает среднее значение для определенного столбца таблицы.
5	SQLite SUM Function Агрегатная функция SQLite SUM позволяет выбрать сумму для числового столбца.
6	SQLite RANDOM Function Функция SQLite RANDOM возвращает псевдослучайное целое число от -9223372036854775808 до +9223372036854775807.
7	SQLite ABS Function Функция SQLite ABS возвращает абсолютное значение числового аргумента.
8	SQLite UPPER Function Функция SQLite UPPER преобразует строку в заглавные буквы.
9	SQLite LOWER Function Функция SQLite LOWER преобразует строку в строчные буквы.
10	SQLite LENGTH Function Функция SQLite LENGTH возвращает длину строки.
11	SQLite sqlite_version Function Функция SQLite sqlite_version возвращает версию библиотеки SQLite.

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

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Функция SQLite COUNT

Агрегатная функция SQLite COUNT используется для подсчета количества строк в таблице базы данных. Ниже приведен пример -

sqlite> SELECT count(*) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

count(*)
----------
7

Функция SQLite MAX

Агрегатная функция SQLite MAX позволяет выбрать наивысшее (максимальное) значение для определенного столбца. Ниже приведен пример -

sqlite> SELECT max(salary) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

max(salary)
-----------
85000.0

Функция SQLite MIN

Агрегатная функция SQLite MIN позволяет выбрать наименьшее (минимальное) значение для определенного столбца. Ниже приведен пример -

sqlite> SELECT min(salary) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

min(salary)
-----------
10000.0

Функция SQLite AVG

Агрегатная функция SQLite AVG выбирает среднее значение для определенного столбца таблицы. Ниже приведен пример -

sqlite> SELECT avg(salary) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

avg(salary)
----------------
37142.8571428572

Функция SQLite SUM

Агрегатная функция SQLite SUM позволяет выбрать сумму для числового столбца. Ниже приведен пример -

sqlite> SELECT sum(salary) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

sum(salary)
-----------
260000.0

Функция SQLite RANDOM

Функция SQLite RANDOM возвращает псевдослучайное целое число от -9223372036854775808 до +9223372036854775807. Ниже приведен пример -

sqlite> SELECT random() AS Random;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

Random
-------------------
5876796417670984050

Функция SQLite ABS

Функция SQLite ABS возвращает абсолютное значение числового аргумента. Ниже приведен пример -

sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

abs(5)      abs(-15)    abs(NULL)   abs(0)      abs("ABC")
----------  ----------  ----------  ----------  ----------
5           15                      0           0.0

Функция SQLite UPPER

Функция SQLite UPPER преобразует строку в заглавные буквы. Ниже приведен пример -

sqlite> SELECT upper(name) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMES

Функция SQLite LOWER

Функция SQLite LOWER преобразует строку в строчные буквы. Ниже приведен пример -

sqlite> SELECT lower(name) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

lower(name)
-----------
paul
allen
teddy
mark
david
kim
james

Функция SQLite LENGTH

Функция SQLite LENGTH возвращает длину строки. Ниже приведен пример -

sqlite> SELECT name, length(name) FROM COMPANY;

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

NAME        length(name)
----------  ------------
Paul        4
Allen       5
Teddy       5
Mark        4
David       5
Kim         3
James       5

SQLite sqlite_version Функция

Функция SQLite sqlite_version возвращает версию библиотеки SQLite. Ниже приведен пример -

sqlite> SELECT sqlite_version() AS 'SQLite Version';

Вышеупомянутый SQL-оператор SQLite выдаст следующее.

SQLite Version
--------------
3.6.20

В этой главе вы узнаете, как использовать SQLite в программах на C / C ++.

Установка

Прежде чем вы начнете использовать SQLite в наших программах C / C ++, вам необходимо убедиться, что на вашем компьютере установлена ​​библиотека SQLite. Вы можете проверить главу «Установка SQLite», чтобы понять процесс установки.

Интерфейсные API C / C ++

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

Sr.No.	API и описание
1	sqlite3_open(const char *filename, sqlite3 **ppDb) Эта процедура открывает соединение с файлом базы данных SQLite и возвращает объект соединения с базой данных, который будет использоваться другими подпрограммами SQLite. Если аргумент имени файла равен NULL или ': memory:', sqlite3_open () создаст базу данных в оперативной памяти в ОЗУ, которая будет существовать только на время сеанса. Если имя файла не равно NULL, sqlite3_open () пытается открыть файл базы данных, используя его значение. Если файла с таким именем не существует, sqlite3_open () откроет новый файл базы данных с этим именем.
2	sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg) Эта процедура обеспечивает быстрый и простой способ выполнения команд SQL, предоставленных аргументом sql, который может состоять из нескольких команд SQL. Здесь первый аргумент sqlite3 - это открытый объект базы данных, sqlite_callback - это обратный вызов, для которого данные являются 1-м аргументом, а errmsg будет возвращен для захвата любой ошибки, вызванной подпрограммой. Подпрограмма SQLite3_exec () анализирует и выполняет каждую команду, указанную в sql аргумент, пока он не достигнет конца строки или не встретит ошибку.
3	sqlite3_close(sqlite3*) Эта процедура закрывает соединение с базой данных, ранее открытое вызовом sqlite3_open (). Все подготовленные операторы, связанные с подключением, должны быть завершены до закрытия подключения. Если остались незавершенные запросы, sqlite3_close () вернет SQLITE_BUSY с сообщением об ошибке «Невозможно закрыть из-за незавершенных операторов».

Подключиться к базе данных

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

#include <stdio.h>
#include <sqlite3.h> 

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;

   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }
   sqlite3_close(db);
}

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

$gcc test.c -l sqlite3
$./a.out
Opened database successfully

Если вы собираетесь использовать исходный код C ++, вы можете скомпилировать свой код следующим образом:

$g++ test.c -l sqlite3

Здесь мы связываем нашу программу с библиотекой sqlite3, чтобы предоставить необходимые функции программе C. Это создаст файл базы данных test.db в вашем каталоге, и вы получите следующий результат.

-rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out
-rw-r--r--. 1 root root  323 May 8 02:05 test.c
-rw-r--r--. 1 root root    0 May 8 02:06 test.db

Создать таблицу

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

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stdout, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Table created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Когда вышеуказанная программа скомпилирована и запущена, она создаст таблицу COMPANY в вашем test.db, и окончательный список файла будет следующим:

-rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out
-rw-r--r--. 1 root root 1207 May 8 02:31 test.c
-rw-r--r--. 1 root root 3072 May 8 02:31 test.db

ВСТАВИТЬ операцию

Следующий сегмент кода C показывает, как вы можете создавать записи в таблице COMPANY, созданной в приведенном выше примере.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Records created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Когда вышеуказанная программа скомпилирована и выполнена, она создаст указанные записи в таблице КОМПАНИИ и отобразит следующие две строки:

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

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

typedef int (*sqlite3_callback)(
   void*,    /* Data provided in the 4th argument of sqlite3_exec() */
   int,      /* The number of columns in row */
   char**,   /* An array of strings representing fields in the row */
   char**    /* An array of strings representing column names */
);

Если указанный выше обратный вызов предоставляется в подпрограмме sqlite_exec () в качестве третьего аргумента, SQLite будет вызывать эту функцию обратного вызова для каждой записи, обрабатываемой в каждом операторе SELECT, выполняемом в аргументе SQL.

Следующий сегмент кода C показывает, как вы можете извлекать и отображать записи из таблицы COMPANY, созданной в приведенном выше примере.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Когда вышеуказанная программа скомпилирована и выполнена, она выдаст следующий результат.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий сегмент кода C показывает, как мы можем использовать оператор UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из таблицы COMPANY.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create merged SQL statement */
   sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Когда вышеуказанная программа скомпилирована и выполнена, она выдаст следующий результат.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

УДАЛИТЬ операцию

Следующий сегмент кода C показывает, как вы можете использовать оператор DELETE для удаления любой записи, а затем извлечь и отобразить оставшиеся записи из таблицы COMPANY.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *data, int argc, char **argv, char **azColName) {
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create merged SQL statement */
   sql = "DELETE from COMPANY where ID=2; " \
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Когда вышеуказанная программа скомпилирована и выполнена, она выдаст следующий результат.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

В этой главе вы узнаете, как использовать SQLite в программах на Java.

Установка

Прежде чем вы начнете использовать SQLite в наших программах Java, вам необходимо убедиться, что на вашем компьютере установлены драйвер SQLite JDBC и Java. Вы можете проверить руководство по Java для установки Java на свой компьютер. Теперь давайте проверим, как настроить драйвер SQLite JDBC.

• Загрузите последнюю версию sqlite-jdbc- (VERSION) .jar из репозитория sqlite-jdbc .

• Добавьте загруженный файл jar sqlite-jdbc- (VERSION) .jar в свой путь к классу или вы можете использовать его вместе с параметром -classpath, как описано в следующих примерах.

В следующем разделе предполагается, что вы мало знакомы с концепциями Java JDBC. Если вы этого не сделаете, то рекомендуется потратить полчаса на JDBC Tutorial, чтобы освоиться с концепциями, описанными ниже.

Подключиться к базе данных

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

import java.sql.*;

public class SQLiteJDBC {
  public static void main( String args[] ) {
      Connection c = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Теперь давайте скомпилируем и запустим вышеуказанную программу, чтобы создать нашу базу данных. test.dbв текущем каталоге. Вы можете изменить свой путь в соответствии с вашими требованиями. Мы предполагаем, что текущая версия драйвера JDBC sqlite-jdbc-3.7.2.jar доступна по текущему пути.

$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Open database successfully

Если вы собираетесь использовать машину с Windows, вы можете скомпилировать и запустить свой код следующим образом:

$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfully

Создать таблицу

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

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
                        "(ID INT PRIMARY KEY     NOT NULL," +
                        " NAME           TEXT    NOT NULL, " + 
                        " AGE            INT     NOT NULL, " + 
                        " ADDRESS        CHAR(50), " + 
                        " SALARY         REAL)"; 
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

Когда вышеуказанная программа скомпилирована и запущена, она создаст таблицу COMPANY в вашем test.db и окончательный список файла будет следующим -

-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar
-rw-r--r--. 1 root root    1506 May  8 05:43 SQLiteJDBC.class
-rw-r--r--. 1 root root     832 May  8 05:42 SQLiteJDBC.java
-rw-r--r--. 1 root root    3072 May  8 05:43 test.db

ВСТАВИТЬ операцию

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

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                        "VALUES (1, 'Paul', 32, 'California', 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"; 
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

Когда вышеуказанная программа скомпилирована и выполнена, она создаст заданные записи в таблице КОМПАНИИ и отобразит следующие две строки:

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

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

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {

   Connection c = null;
   Statement stmt = null;
   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
   System.out.println("Operation done successfully");
  }
}

Когда вышеуказанная программа скомпилирована и выполнена, она выдаст следующий результат.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код Java показывает, как использовать оператор UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из таблицы COMPANY.

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {
  
   Connection c = null;
   Statement stmt = null;
   
   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
      stmt.executeUpdate(sql);
      c.commit();

      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
    System.out.println("Operation done successfully");
   }
}

Когда вышеуказанная программа скомпилирована и выполнена, она выдаст следующий результат.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

УДАЛИТЬ операцию

Следующий код Java показывает, как использовать оператор DELETE для удаления любой записи, а затем извлечь и отобразить оставшиеся записи из нашей таблицы COMPANY.

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         
         while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Когда вышеуказанная программа скомпилирована и выполнена, она выдаст следующий результат.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

В этой главе вы узнаете, как использовать SQLite в программах PHP.

Установка

Расширение SQLite3 включено по умолчанию, начиная с PHP 5.3.0. Его можно отключить, используя--without-sqlite3 во время компиляции.

Пользователи Windows должны включить php_sqlite3.dll, чтобы использовать это расширение. Эта DLL включена в дистрибутивы PHP для Windows, начиная с версии PHP 5.3.0.

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

API интерфейса PHP

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

Sr.No.	API и описание
1	public void SQLite3::open ( filename, flags, encryption_key ) Открывает базу данных SQLite 3. Если сборка включает шифрование, она попытается использовать ключ. Если имя файла указано как':memory:', SQLite3 :: open () создаст базу данных в оперативной памяти, которая существует только на время сеанса. Если имя файла является фактическим именем файла устройства, SQLite3 :: open () пытается открыть файл базы данных, используя его значение. Если файла с таким именем не существует, создается новый файл базы данных с таким именем. Необязательные флаги, используемые для определения того, как открыть базу данных SQLite. По умолчанию open использует SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.
2	public bool SQLite3::exec ( string $query ) Эта процедура обеспечивает быстрый и простой способ выполнения команд SQL, предоставленных аргументом sql, который может состоять из нескольких команд SQL. Эта процедура используется для выполнения запроса без результата к данной базе данных.
3	public SQLite3Result SQLite3::query ( string $query ) Эта процедура выполняет SQL-запрос, возвращая SQLite3Result объект, если запрос возвращает результаты.
4	public int SQLite3::lastErrorCode ( void ) Эта процедура возвращает числовой код результата последнего неудачного запроса SQLite.
5	public string SQLite3::lastErrorMsg ( void ) Эта процедура возвращает текст на английском языке, описывающий последний неудачный запрос SQLite.
6	public int SQLite3::changes ( void ) Эта процедура возвращает количество строк базы данных, которые были обновлены, вставлены или удалены самым последним оператором SQL.
7	public bool SQLite3::close ( void ) Эта подпрограмма закрывает соединение с базой данных, ранее открытое вызовом SQLite3 :: open ().
8	public string SQLite3::escapeString ( string $value ) Эта процедура возвращает строку, которая была правильно экранирована для безопасного включения в инструкцию SQL.

Подключиться к базе данных

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

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db');
      }
   }
   $db = new MyDB(); if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
?>

Теперь давайте запустим указанную выше программу, чтобы создать нашу базу данных. test.dbв текущем каталоге. Вы можете изменить свой путь в соответствии с вашими требованиями. Если база данных успешно создана, отобразится следующее сообщение -

Open database successfully

Создать таблицу

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

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db');
      }
   }
   $db = new MyDB(); if(!$db) {
      echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = $db->exec($sql); if(!$ret){
      echo $db->lastErrorMsg(); } else { echo "Table created successfully\n"; } $db->close();
?>

Когда вышеуказанная программа будет выполнена, она создаст таблицу COMPANY в вашем test.db и он отобразит следующие сообщения -

Opened database successfully
Table created successfully

ВСТАВИТЬ операцию

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

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db'); } } $db = new MyDB();
   if(!$db){ echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); EOF; $ret = $db->exec($sql);
   if(!$ret) { echo $db->lastErrorMsg();
   } else {
      echo "Records created successfully\n";
   }
   $db->close();
?>

Когда вышеуказанная программа будет выполнена, она создаст указанные записи в таблице COMPANY и отобразит следующие две строки.

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

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

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db');
      }
   }
   
   $db = new MyDB(); if(!$db) {
      echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>

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

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код PHP показывает, как использовать инструкцию UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из таблицы COMPANY.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db'); } } $db = new MyDB();
   if(!$db) { echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF UPDATE COMPANY set SALARY = 25000.00 where ID=1; EOF; $ret = $db->exec($sql);
   if(!$ret) { echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record updated successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   
   $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>

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

Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

УДАЛИТЬ операцию

Следующий код PHP показывает, как использовать оператор DELETE для удаления любой записи, а затем выборки и отображения оставшихся записей из таблицы COMPANY.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db'); } } $db = new MyDB();
   if(!$db) { echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF DELETE from COMPANY where ID = 2; EOF; $ret = $db->exec($sql);
   if(!$ret){ echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record deleted successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>

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

Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

В этой главе вы узнаете, как использовать SQLite в программах на Perl.

Установка

SQLite3 может быть интегрирован с Perl с помощью модуля Perl DBI, который представляет собой модуль доступа к базе данных для языка программирования Perl. Он определяет набор методов, переменных и соглашений, которые обеспечивают стандартный интерфейс базы данных.

Ниже приведены простые шаги по установке модуля DBI на ваш компьютер Linux / UNIX.

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625 $ perl Makefile.PL
$ make $ make install

Если вам нужно установить драйвер SQLite для DBI, его можно установить следующим образом:

$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz $ tar xvfz DBD-SQLite-1.11.tar.gz
$ cd DBD-SQLite-1.11 $ perl Makefile.PL
$ make $ make install

API интерфейса DBI

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

Sr.No.	API и описание
1	DBI->connect($data_source, "", "", \%attr) Устанавливает соединение с базой данных или сеанс с запрошенным $ data_source. Возвращает объект дескриптора базы данных, если соединение установлено. Источник данных имеет вид - DBI:SQLite:dbname = 'test.db'где SQLite - это имя драйвера SQLite, а test.db - имя файла базы данных SQLite. Если имя файла указано как':memory:', он создаст базу данных в оперативной памяти, которая будет работать только на время сеанса. Если имя файла является фактическим именем файла устройства, он пытается открыть файл базы данных, используя его значение. Если файла с таким именем не существует, создается новый файл базы данных с таким именем. Вы сохраняете второй и третий параметр как пустые строки, а последний параметр должен передавать различные атрибуты, как показано в следующем примере.
2	$dbh->do($sql) Эта процедура подготавливает и выполняет один оператор SQL. Возвращает количество затронутых строк или undef в случае ошибки. Возвращаемое значение -1 означает, что количество строк неизвестно, неприменимо или недоступно. Здесь $ dbh - это дескриптор, возвращаемый вызовом DBI-> connect ().
3	$dbh->prepare($sql) Эта процедура подготавливает оператор для последующего выполнения ядром базы данных и возвращает ссылку на объект дескриптора оператора.
4	$sth->execute() Эта процедура выполняет любую обработку, необходимую для выполнения подготовленного оператора. В случае ошибки возвращается undef. Успешное выполнение всегда возвращает истину независимо от количества затронутых строк. Вот,$sth is a statement handle returned by $вызов dbh-> prepare ($ sql).
5	$sth->fetchrow_array() Эта процедура выбирает следующую строку данных и возвращает ее в виде списка, содержащего значения полей. Нулевые поля возвращаются в списке как значения undef.
6	$DBI::err Это эквивалентно $ h-> err, где $h is any of the handle types like $дбх, $sth, or $дрх. Это возвращает код ошибки собственного ядра базы данных из последнего вызванного метода драйвера.
7	$DBI::errstr Это эквивалентно $ h-> errstr, где $h is any of the handle types like $дбх, $sth, or $дрх. Это возвращает сообщение об ошибке собственного ядра базы данных из последнего вызванного метода DBI.
8	$dbh->disconnect() Эта процедура закрывает соединение с базой данных, ранее открытое вызовом DBI-> connect ().

Подключиться к базе данных

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

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite"; 
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

Теперь давайте запустим указанную выше программу, чтобы создать нашу базу данных test.db в текущем каталоге. Вы можете изменить свой путь в соответствии с вашими требованиями. Сохраните приведенный выше код в файле sqlite.pl и выполните его, как показано ниже. Если база данных успешно создана, отобразится следующее сообщение -

$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfully

Создать таблицу

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

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(CREATE TABLE COMPANY
   (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););

my $rv = $dbh->do($stmt); if($rv < 0) {
   print $DBI::errstr; } else { print "Table created successfully\n"; } $dbh->disconnect();

Когда вышеуказанная программа будет выполнена, она создаст таблицу COMPANY в вашем test.db и отобразит следующие сообщения:

Opened database successfully
Table created successfully

NOTE - Если вы видите следующую ошибку в любой операции -

DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398

В таком случае откройте dbdimp.c file доступно в установке DBD-SQLite и узнайте sqlite3_prepare() функцию и измените ее третий аргумент на -1 вместо 0. Наконец, установите DBD :: SQLite, используя make и делай make install чтобы решить проблему.

ВСТАВИТЬ операцию

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

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "SQLite"; my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database"; my $userid = "";
my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
               VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
               VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););

$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

Когда приведенная выше программа будет выполнена, она создаст данные записи в таблице КОМПАНИИ и отобразит следующие две строки:

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

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

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt ); my $rv = $sth->execute() or die $DBI::errstr;

if($rv < 0) { print $DBI::errstr;
}

while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

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

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код Perl показывает, как выполнить оператор UPDATE для обновления любой записи, а затем извлечь и отобразить обновленные записи из таблицы COMPANY.

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "SQLite"; my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database"; my $userid = "";
my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ) {
   print $DBI::errstr; } else { print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr; if($rv < 0) {
   print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n"; print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

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

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

УДАЛИТЬ операцию

Следующий код Perl показывает, как использовать оператор DELETE для удаления любой записи, а затем выборки и отображения оставшихся записей из таблицы COMPANY.

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(DELETE from COMPANY where ID = 2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;

if( $rv < 0 ) { print $DBI::errstr;
} else {
   print "Total number of rows deleted : $rv\n"; } $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr;

if($rv < 0) { print $DBI::errstr;
}

while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

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

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

В этой главе вы узнаете, как использовать SQLite в программах на Python.

Установка

SQLite3 может быть интегрирован с Python с помощью модуля sqlite3, написанного Герхардом Харингом. Он предоставляет интерфейс SQL, соответствующий спецификации DB-API 2.0, описанной в PEP 249. Вам не нужно устанавливать этот модуль отдельно, поскольку он поставляется по умолчанию вместе с Python версии 2.5.x и более поздних версий.

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

API модуля Python sqlite3

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

Sr.No.	API и описание
1	sqlite3.connect(database [,timeout ,other optional arguments]) Этот API открывает соединение с файлом базы данных SQLite. Вы можете использовать ": memory:", чтобы открыть соединение с базой данных, которая находится в ОЗУ, а не на диске. Если база данных открыта успешно, возвращается объект подключения. Когда к базе данных обращается несколько подключений и один из процессов изменяет базу данных, база данных SQLite блокируется до тех пор, пока транзакция не будет зафиксирована. Параметр тайм-аута указывает, как долго соединение должно ждать снятия блокировки до возникновения исключения. По умолчанию для параметра тайм-аута установлено значение 5,0 (пять секунд). Если данное имя базы данных не существует, этот вызов создаст базу данных. Вы также можете указать имя файла с требуемым путем, если хотите создать базу данных в любом другом месте, кроме текущего каталога.
2	connection.cursor([cursorClass]) Эта процедура создает cursorкоторый будет использоваться на протяжении всего программирования вашей базы данных с помощью Python. Этот метод принимает единственный необязательный параметр cursorClass. Если указан, это должен быть пользовательский класс курсора, расширяющий sqlite3.Cursor.
3	cursor.execute(sql [, optional parameters]) Эта подпрограмма выполняет инструкцию SQL. Оператор SQL может быть параметризован (т. Е. Заполнители вместо литералов SQL). Модуль sqlite3 поддерживает два типа заполнителей: вопросительные знаки и именованные заполнители (именованный стиль). For example - cursor.execute ("вставить в людей значения (?,?)", (Кто, возраст))
4	connection.execute(sql [, optional parameters]) Эта процедура является сокращением вышеупомянутого метода execute, предоставляемого объектом курсора, и она создает промежуточный объект курсора, вызывая метод курсора, а затем вызывает метод выполнения курсора с заданными параметрами.
5	cursor.executemany(sql, seq_of_parameters) Эта процедура выполняет команду SQL для всех последовательностей параметров или сопоставлений, найденных в последовательности sql.
6	connection.executemany(sql[, parameters]) Эта процедура представляет собой ярлык, который создает промежуточный объект курсора путем вызова метода курсора, а затем вызывает метод cursor.s executemany с заданными параметрами.
7	cursor.executescript(sql_script) Эта процедура одновременно выполняет несколько операторов SQL, представленных в форме сценария. Сначала он выдает оператор COMMIT, а затем выполняет сценарий SQL, который он получает в качестве параметра. Все операторы SQL следует разделять точкой с запятой (;).
8	connection.executescript(sql_script) Эта процедура представляет собой ярлык, который создает промежуточный объект курсора путем вызова метода курсора, а затем вызывает метод курсора executescript с заданными параметрами.
9	connection.total_changes() Эта процедура возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия соединения с базой данных.
10	connection.commit() Этот метод фиксирует текущую транзакцию. Если вы не вызовете этот метод, все, что вы сделали с момента последнего вызова commit (), не будет видно из других подключений к базе данных.
11	connection.rollback() Этот метод откатывает любые изменения в базе данных с момента последнего вызова commit ().
12	connection.close() Этот метод закрывает соединение с базой данных. Обратите внимание, что это не вызывает автоматического вызова commit (). Если вы просто закроете соединение с базой данных без предварительного вызова commit (), ваши изменения будут потеряны!
13	cursor.fetchone() Этот метод выбирает следующую строку набора результатов запроса, возвращая одну последовательность, или None, если больше нет доступных данных.
14	cursor.fetchmany([size = cursor.arraysize]) Эта процедура выбирает следующий набор строк результата запроса, возвращая список. Когда строк больше нет, возвращается пустой список. Метод пытается получить столько строк, сколько указано в параметре размера.
15	cursor.fetchall() Эта процедура извлекает все (оставшиеся) строки результата запроса, возвращая список. Если строк нет, возвращается пустой список.

Подключиться к базе данных

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

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')

print "Opened database successfully";

Здесь вы также можете указать имя базы данных в качестве специального имени :memory:для создания базы данных в оперативной памяти. Теперь давайте запустим указанную выше программу, чтобы создать нашу базу данных.test.dbв текущем каталоге. Вы можете изменить свой путь в соответствии с вашими требованиями. Сохраните приведенный выше код в файле sqlite.py и выполните его, как показано ниже. Если база данных успешно создана, отобразится следующее сообщение.

$chmod +x sqlite.py $./sqlite.py
Open database successfully

Создать таблицу

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

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute('''CREATE TABLE COMPANY
         (ID INT PRIMARY KEY     NOT NULL,
         NAME           TEXT    NOT NULL,
         AGE            INT     NOT NULL,
         ADDRESS        CHAR(50),
         SALARY         REAL);''')
print "Table created successfully";

conn.close()

Когда вышеуказанная программа будет выполнена, она создаст таблицу COMPANY в вашем test.db и он отобразит следующие сообщения -

Opened database successfully
Table created successfully

ВСТАВИТЬ операцию

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

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

Когда приведенная выше программа будет выполнена, она создаст данные записи в таблице КОМПАНИИ и отобразит следующие две строки:

Opened database successfully
Records created successfully

ВЫБРАТЬ операцию

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

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

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

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

ОБНОВЛЕНИЕ Операция

Следующий код Python показывает, как использовать оператор UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из таблицы COMPANY.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

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

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

УДАЛИТЬ операцию

Следующий код Python показывает, как использовать оператор DELETE для удаления любой записи, а затем извлечь и отобразить оставшиеся записи из таблицы COMPANY.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

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

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully