CREATE TABLE

sql-command::=

CREATE TABLE table-name (

Column-def [, column-def]*

[, constraint]*

sql-command::=

CREATE TABLE table-name AS select-statement

column-def::=

name [ column-constraint]*

type::=

typename |

typename (number) |

typename (number , number)

column-constraint::=

NOT NULL [ conflict-clause ] |

PRIMARY KEY [ conflict-clause ] |

UNIQUE [ conflict-clause ] |

CHECK (expr) |

DEFAULT value |

COLLATE collation-name

constraint::=

PRIMARY KEY (column-list) [ conflict-clause ] |

UNIQUE (column-list) [ conflict-clause ] |

CHECK (expr)

conflict-clause::=

ON CONFLICT conflict-algorithm

В команде CREATE TABLE за ключевым словом "CREATE TABLE" обычно следует имя таблицы и список определений полей и условий. Имя таблицы может быть как идентификатором, так и строкой. Имена, начинающиеся с "sqlite_" зарезервированы для использования движком.

Каждое определение поля таблицы состоит из его имени, типа и одним или несколькими дополнительными условиями. Тип данных для поля, не ограничивает размещение в нем данных. Для дополнительной информации смотрите Типы данных в SQLite версии 3. Ограничение UNIQUE вызывает создание индекса на заданных полях. Оператор COLLATE определяет для функцию сравнения, которая будет использоваться для этого поля. По умолчанию применяется встроенная функция BINARY.

Условие DEFAULT определяет значение по умолчанию, которое используется при вставке INSERT. Значение может быть NULL, строковой константой или числом. Начиная с версии 3.1.0, значение по умолчанию, может также принимать специальные ключевые слова CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP. Если значение NULL, строка или число, оно вставляется без изменений в поле, если в команде INSERT не определено значение для этого поля. Если значение CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP, тогда текущая UTC дата и/или время вставляются в поля. Для CURRENT_TIME, формат HH:MM:SS. Для CURRENT_DATE, YYYY-MM-DD. Формат для CURRENT_TIMESTAMP - "YYYY-MM-DD HH:MM:SS".

Определение PRIMARY KEY обычно просто создает индекс UNIQUE на заданных полях. Однако, если первичный ключ на одном столбце имеет тип INTEGER, тогда этот столбец будет использоваться внутри как реальный ключ для B-Дерева (B-Tree) таблицы. Это означает, что поле может содержать только уникальное целое значение. (Исключение в одном случае, SQLite игнорирует типы данных полей и позволяет хранить данные любого типа, независимо от их описания.) Если таблица не имеет поля с INTEGER PRIMARY KEY, тогда целый ключ B-Дерева (B-Tree) будет создан автоматически. Доступ к ключу B-Tree записи всегда можно получить, используя специальные имена "ROWID", "OID", или "_ROWID_". Это верно независимо от наличия INTEGER PRIMARY KEY. После INTEGER PRIMARY KEY также может содержать ключевое слово AUTOINCREMENT. Ключевое слово AUTOINCREMENT изменяет способ автоматической генерации B-Tree. Дополнительная информация о автоматической генерации B-Tree доступна отдельно.

В соответствии со стандартом SQL, PRIMARY KEY подразумевает NOT NULL. К сожалению, по давнему недосмотру, это не так в случае SQLite. SQLite допускает значения NULL в столбцах PRIMARY KEY. Мы можем изменить SQLite для соответствия стандарту (и возможно это будет сделано в будущем), но за прошедшее время, SQLite получил настолько широкое распространения, что мы боимся сделать ошибочным код, который сейчас корректен, если мы исправим проблему. Поэтому сейчас мы решили разрешить NULL в столбцах PRIMARY KEY. Разработчики должны помнить, что мы можем изменить SQLite в соответствии со стандартом SQL в будущем и должны разрабатывать новые программы в соответствии с ним.

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

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

Возможно указание оператора разрешения конфликтов для каждого условия, если требуется задать альтернативный алгоритм. По умолчанию используется алгоритм ABORT. Различные условия в одной таблице могут иметь различные алгоритмы по умолчанию для разрешения проблем. Если в командах COPY, INSERT или UPDATE определены отличные алгоритмы разрешения конфликтов, тогда они вытесняют алгоритмы, заданные в запросе CREATE TABLE. Для детальной информации смотрите ON CONFLICT.

Ограничение CHECK поддерживается с версии 3.3.0. До версии 3.3.0, CHECK мог указываться, но не соблюдался.

Нет явных ограничений по числу полей или количестве ограничений в таблице. Общий размер данных в одной записи ограничен примерно 1 мб в версии 2.8. В версии 3.0 нет жесткого лимита на объем данных в записи.

Форма определения таблицы CREATE TABLE AS создает таблицу из результата запроса. Имена полей таблицы берутся именами полей в результате запросе.

Точный текст каждого запроса CREATE TABLE хранится в таблице sqlite_master. Каждый раз при открытии базы, все запросы CREATE TABLE считываются из таблицы sqlite_master и используются для генерации внутреннего представления SQLite. Если оригинальной командой была CREATE TABLE AS, тогда создается ее эквивалент CREATE TABLE, который сохраняется в sqlite_master вместо оригинального запроса. Текст CREATE TEMPORARY TABLE хранится в таблице sqlite_temp_master.

Если указан опциональный оператор IF NOT EXISTS и уже есть другая таблица с таким именем, то команда не выполняется.

SQLite – это автономный, работающий без сервера транзакционный механизм базы данных SQL. Python получил модуль sqlite3 в версии 2.5, что значит что вы можете создавать базу данных SQLite в любой настоящей версии Python, без необходимости скачивания дополнительных инструментов. Mozilla использует базы данных SQLite в своем популярном браузере Firefox для хранения закладок и прочей различной информации. В данной статье мы рассмотрим следующее:

• Как создать базу данных SQLite
• Как вставить данные в таблицу
• Как редактировать данные
• Как удалять данные
• Базовые запросы SQL

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

Как создавать базу данных и вставлять различные данные

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

Python

import sqlite3 conn = sqlite3.connect("mydatabase.db") # или:memory: чтобы сохранить в RAM cursor = conn.cursor() # Создание таблицы cursor.execute("""CREATE TABLE albums (title text, artist text, release_date text, publisher text, media_type text) """)

Сначала нам нужно модуль sqlite3 и создать связь с базой данных. Вы можете передать название файла или просто использовать специальную строку “:memory: ” для создания базы данных в памяти. В нашем случае, мы создаем его на диске в файле под названием mydatabase.db .

Далее мы создаем объект cursor , который позволяет нам взаимодействовать с базой данных и добавлять записи, помимо всего прочего. Здесь мы используем синтаксис SQL для создания таблицы под названием альбомы с пятью следующими полями: title, artist, release_date, publisher и media_type . SQLite поддерживает только пять типов данных: null , integer , real , text и blob . Давайте напишем этот код и вставим кое-какие данные в нашей новой таблице. Запомните, если вы запускаете команду CREATE TABLE , при этом база данных уже существует, вы получите сообщение об ошибке.

Python

# Вставляем данные в таблицу cursor.execute("""INSERT INTO albums VALUES ("Glow", "Andy Hunter", "7/24/2012", "Xplore Records", "MP3")""") # Сохраняем изменения conn.commit() # Вставляем множество данных в таблицу используя безопасный метод "?" albums = [("Exodus", "Andy Hunter", "7/9/2002", "Sparrow Records", "CD"), ("Until We Have Faces", "Red", "2/1/2011", "Essential Records", "CD"), ("The End is Where We Begin", "Thousand Foot Krutch", "4/17/2012", "TFKmusic", "CD"), ("The Good Life", "Trip Lee", "4/10/2012", "Reach Records", "CD")] cursor.executemany("INSERT INTO albums VALUES (?,?,?,?,?)", albums) conn.commit()

# Вставляем данные в таблицу cursor . execute ("""INSERT INTO albums VALUES ("Glow", "Andy Hunter", "7/24/2012", "Xplore Records", "MP3")""" # Сохраняем изменения conn . commit () # Вставляем множество данных в таблицу используя безопасный метод "?" albums = [ ("Exodus" , "Andy Hunter" , "7/9/2002" , "Sparrow Records" , "CD" ) , ("Until We Have Faces" , "Red" , "2/1/2011" , "Essential Records" , "CD" ) , ("The Good Life" , "Trip Lee" , "4/10/2012" , "Reach Records" , "CD" ) ] cursor . executemany ("INSERT INTO albums VALUES (?,?,?,?,?)" , albums ) conn . commit ()

INSERT INTO SQL чтобы вставить запись в нашу базу данных. Обратите внимание на то, что каждый объект находится в одинарных кавычках . Это может усложнить работу, если вам нужно вставить строчки, которые содержат одинарные кавычки. В любом случае, чтобы сохранить запись в базе данных, нам нужно создать её. Следующая часть кода показывает, как добавить несколько записей за раз при помощи метода курсора . Обратите внимание на то, что мы используем знаки вопроса (? ), вместо строк замещения (%) чтобы вставить значения. Обратите внимание, что использование строки замещения не безопасно, так как может стать причиной появления атаки инъекций SQL . Использование знака вопроса намного лучше, а использование SQLAlchemy тем более, так как он делаете все необходимое, чтобы уберечь вас от правки встроенных одинарных кавычек на то, что SQLite в состоянии принимать.

Редактирование и удаление записей

Возможность обновлять записи в вашей базе данных это ключ к тому, чтобы ваши данные велись аккуратно, и был полный порядок. Если вы не можете редактировать данные , тогда ваша база станет бесполезной достаточно скоро. Иногда вам, в том числе, нужно будет удалять и строки. Мы рассмотрим оба вопроса в данном разделе. Для начала, давайте немного обновимся.

Python

Здесь мы использовали команду SQL UPDATE , чтобы обновить таблицу альбомов. Здесь вы можете использовать команду SET , чтобы изменить поле, так что в нашем случае мы изменим имя исполнителя на John Doe в каждой записи, где поле исполнителя указано для Andy Hunter. Весьма просто, не так ли? Обратите внимание на то, что если вы не подтвердите изменения, то они не будут внесены в базу данных. Команда DELETE настолько же проста. Давайте посмотрим.

Python

Удаление еще проще, чем обновление. У SQL это занимает всего две строчки. В данном случае, все, что нам нужно сделать, это указать SQLite , из какой таблицы удалить (albums), и какую именно запись при помощи пункта WHERE . Таким образом, был выполнен поиск записи, в которой присутствует имя “John Doe” в поле исполнителей, после чего эти данные были удалены .

Основные запросы SQLite

Запросы в SQLite очень похожи на те, которые вы используете в других базах данных, таких как MySQL или Postgres. Мы просто используем обычный синтаксис SQL для выполнения запросов, после чего объект cursor выполняет SQL. Вот несколько примеров:

Python

import sqlite3 conn = sqlite3.connect("mydatabase.db") #conn.row_factory = sqlite3.Row cursor = conn.cursor() sql = "SELECT * FROM albums WHERE artist=?" cursor.execute(sql, [("Red")]) print(cursor.fetchall()) # or use fetchone() print("Here"s a listing of all the records in the table:") for row in cursor.execute("SELECT rowid, * FROM albums ORDER BY artist"): print(row) print("Results from a LIKE query:") sql = "SELECT * FROM albums WHERE title LIKE "The%"" cursor.execute(sql) print(cursor.fetchall())

Легковесная встраиваемая реляционная база данных. Из особенностей: не использует парадигму "клиент-сервер",
поддерживает практически весь стандартный набор SQL инструкций, движок SQLite представляет собой библиотеку (sqlite3.dll), то есть не является
отдельно работающим процессом, предоставляет в качестве протокола обмена вызываемые функции (API).
Для платформы Win32 (Precompiled for Windows) на официальном сайте - sqlite.org - можно скачать непосредственно саму библиотеку - sqlite3.dll
(sqlite - dll - win32), а также функциональную клиентскую часть - sqlite3.exe (sqlite - shell - win32). Последний доступный релиз версии - v.3.7.10
Клиентская оболочка sqlite3.exe позволяет напрямую через консоль работать с базой данных.
Прежде, чем приступать к работе необходимо проделать небольшие манипуляции: 1) шрифт консоли должен быть переведен на Lucida Console
(правый клик по консоли -> Свойства -> Шрифт), 2) перевести кодовую страницу в 1251 с помощью команды "chcp 1251", по умолчанию code page равен 866.
Это необходимо для корректной вставки текстовых данных в таблицы базы данных.

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

Команда Краткое описание
.databases Вывод списка имен всех подключенных в текущем сеансе баз данных и соответствующих им файлов.
.dump ?TABLES?: Дамп всех SQL инструкций использованных в создании БД или отдельной таблицы в текстовом формате
.echo ON|OFF ВКЛ | ВЫКЛ эхо введенных команд
.exit Выход из программы
.explain ON|OFF Управляет режимом вывода виртуальных машинных команд. Используется при выполнении SQL запроса EXPLAIN.
.header(s) ON|OFF ВКЛ | ВЫКЛ показ заголовков столбцов
.import FILE TABLE Импорт данных из файла FILE в таблицу TABLE
.indices TABLE Показывает имена всех индексов таблицы
.mode MODE Установка режима вывода: line(s), column(s), insert, list, html и других
.nullvalue STRING Напечатает строку STRING вместо NULL данных при выводе SQL запроса SELECT
.output FILENAME Послать весь вывод в файл FILENAME
.output stdout Послать весь вывод на экран
.promt MAIN COTINUE Изменить стандартную строку подсказки
.quit Выход их программы
.read FILENAME Выполнение SQL инструкций из файла FILENAME
.schema ?TABLE? Покажет текст SQL инструкции CREATE для всех таблиц или указанной таблицы
.separator STRING Изменить строку разделитель колонок, используется при выводе SQL запроса SELECT и команды.import
.show Показать значения установленных переменных
.tables ?PATTERN? Вывод списка имен таблиц БД (возможно по шаблону)
.timeout MS Блокирование открытия таблиц на число миллисекунд MS
.width NUM NUM: Установка ширины столбцов при выводе в режиме column

Подробно рассмотрим некоторые из этих команд. Предварительно создадим базу данных, на которой мы и рассмотрим специальные команды. Из командной строки запустите консольную программу "sqlite3.exe" введя следующее:

sqlite3 exam.db
SQLite version 3.1.3
Enter ".help" for instructions
Где exam.db имя создаваемой базы данных.

Создадим таблицу tbl1, с помощью SQL инструкции CREATE TABLE.

sqlite> CREATE TABLE tbl1 (one INTEGER PRIMARY KEY AUTOINCREMENT,
two VARCHAR(50), three REAL);

Вставим несколько записей в данную таблицу. Отмечу, что в инструкции создания таблицы для каждого поля мы задали тип данных для его значений, этот тип данных игнорируется программой "SQLite". Система управления базой данных "SQLite" довольно свободно относится к определению типов данных, что не характерно для стандарта языка SQL. Типизация данных в "SQLite" основывается на так называемом принципе "typeless", означающий игнорирование информации о типе данных в определении столбцов таблицы при ее создании с помощью инструкции CREATE TABLE. И все же лучше включать имена типов столбцов при объявлении таблиц, это повысит переносимость вашей базы данных. Имеется одно исключение из принципа "typeless" - это столбец типа INTEGER PRYMARY KEY (INTEGER не INT; INT PRIMARY KEY - удовлетворяет принципу "typeless"). Столбец с типом INTEGER PRIMARY KEY должен содержать 32-х битное целое число, попытка записать данные любого другого типа в столбец этого типа приведет к ошибке. Этот тип обычно используется перед спецификатором столбца AUTOINCREMENT, столбец с типом INTEGER PRIMARY KEY AUTOINCREMENT является ключевым столбцом таблицы, его значения генерируются автоматически.

sqlite> INSERT INTO tbl1 VALUES(NULL, "hello", 3.10);
sqlite> INSERT INTO tbl1 VALUES(NULL, "is", 5.34);
sqlite> INSERT INTO tbl1 VALUES(NULL, "from", NULL);

sqlite> CREATE TABLE tbl2 (ikey INTEGER PRIMARY KEY, nm VARCHAR(50));
sqlite>
sqlite>

Команда.dump

Позволит вам получить полный дамп (листинг) всех SQL инструкций использованных при создании базы данных или некоторой конкретной таблицы.

Синтаксис:

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

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE tbl1 (one integer primary key autoincrement, two varchar(50),
three real);
INSERT INTO "tbl1" VALUES(1, "hello", 3.1);
INSERT INTO "tbl1" VALUES(2, "is", 5.34);
INSERT INTO "tbl1" VALUES(3, "from", NULL);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES("tbl1", 3);

CREATE UNIQUE INDEX itbl2 on tbl2(ikey);
COMMIT;
sqlite>

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

sqlite> .output exam.sql --перенаправить вывод в файл
sqlite> .dump

Команда.header(s)

Включает / выключает вывод названий заголовков столбцов.

Синтаксис:

Header(s) ON | OFF
где,

ON - включить вывод имен заголовков столбцов
OFF - отключить вывод имен заголовков столбцов
Пример:

sqlite> .header ON
sqlite> SELECT * FROM tbl1;
one|two|three
1|hello|3.1
2|is|5.34
3|from|
sqlite> .header OFF
sqlite> SELECT * FROM tbl1;
1|hello|3.1
2|is|5.34
3|from|
sqlite>

Команда.import

С помощью данной команды имеется возможность импортировать данные из текстового файла. Данные в текстовом файле должны быть разделены строкой-разделителем, эта строка разделитель определяется командой.separator. По умолчанию символом-разделителем является символ вертикальной черты "|". Данная команда введена в версии 3.

Синтаксис:

Import FILE TABLE
где,

FILE - имя входного тестового файла с данными
TABLE - имя таблицы куда вставляются данные
Пример:

Подготовим следующие данные в файле exam.txt

1;Roy
2;Ted
3;Sam
4;Bob
Вставим эти данные в таблицу tbl2, для этого выполним следующие команды:

sqlite> .separator ";" -- установим символ-разделитель в точку с запятой
sqlite> .import exam.txt tbl2 -- импорт данных
sqlite> SELECT * FROM tbl2;
1; Roy
2; Ted
3; Sam
4; Bob

Команда.mode

Данная команда изменяет формат вывода таблицы SQL запросом SELECT. "SQLite" может вывести результат запроса в следующих форматах: csv, column, html, insert, line, list, tabs, tcl. Для версии 2.8 поддерживается только следующие форматы: line(s), column(s), insert, list, html. Рассмотрим их по порядку.

Режим csv. В данном формате столбцы записи таблицы разделяются запятыми.

sqlite> .mode csv
sqlite> SELECT * FROM tbl1;
1,"hello",3.1
2,"is",5.34
3,"from",
sqlite>

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

Режим column. В этом режиме вывод выровнен по левой границе столбцов записей таблицы.

sqlite> .mode columns
sqlite> SELECT * FROM tbl1;
1 hello 3.1
2 is 5.34
3 from
sqlite>

Смотрите также команду.width для задания ширины столбцов.

Режим html. Вывод в этом режиме выполнен в стиле определения таблицы языка html. Удобен для включения таблицы в страничку сайта.

sqlite> .mode html
sqlite> SELECT * FROM tbl2;

1
Roy

2
Ted

3
Sam

4
Bob

sqlite>

Режим insert. В данном режиме генерируются SQL запросы INSERT для таблицы, имя которой указано третьим параметром в команде.

Синтаксис:

Mode insert TABLE
где TABLE - имя таблицы, для которой генерируются инструкции INSERT

sqlite> .mode insert tbl3
sqlite> SELECT * FROM tbl1;
INSERT INTO tbl3 VALUES(1,"hello",3.1);
INSERT INTO tbl3 VALUES(2,"is",5.34);
INSERT INTO tbl3 VALUES(3,"from",NULL);
sqlite>

Режим line. Здесь каждый столбец выводится в отдельной строке в формате имя_столбца = значение. Записи разделяются пустой строкой.

Пример:

sqlite> .mode line
sqlite> SELECT * FROM tbl1;
one = 1
two = hello
three = 3.1

one = 2
two = is
three = 5.34

one = 3
two = from
three =
sqlite>

Режим list. Является режимом, установленным по умолчанию. Каждая запись таблицы выводится в отдельной строке с разделением столбцов символом-разделителем. Символ-разделитель определяется командой.separator, и по умолчанию равен прямой черте "|".

sqlite> .mode list
sqlite> SELECT * FROM tbl1;
1|hello|3.1
2|is|5.34
3|from|
sqlite>

Режим tabs. В данном режиме столбцы записей таблицы отделяются друг от друга символом табуляции.

sqlite> .mode tabs
sqlite> SELECT * FROM tbl1;
1 hello 3.1
2 is 5.34
3 from
sqlite>

Режим tcl. Вывод выполнен в формате элементов списка языка TCL.

sqlite> .mode tcl
sqlite> SELECT * FROM tbl2;
"1" " Roy \r"
"2" " Ted\r"
"3" " Sam\r"
"4" " Bob\r"
sqlite>

Команда.output

Данная команда перенаправляет вывод в файл или обратно на экран.

Синтаксис:

Output FILENAME
.output stdout
где,

FILENAME - имя файла, куда направлен вывод
stdout - направит вывод на экран
Команда.read

Читает и выполняет SQL инструкции из файла.

Синтаксис:

Read FILENAME
где FILENAME - имя файла содержащего SQL инструкции

Создайте текстовый файл, содержащий следующие SQL запросы:

BEGIN TRANSACTION;

INSERT INTO tbl3 VALUES ("Jim", "Green", 1653);
INSERT INTO tbl3 VALUES ("Ann", "Folkin", 1891);
INSERT INTO tbl3 VALUES ("Fredy", "Donovan", 2345);
COMMIT;

Назовем созданный файл exam.sql, и выполним следующие команды:

sqlite> .read exam.sql
sqlite> SELECT * FROM tbl3;
Jim|Green|1653
Ann|Folkin|1891
Fredy|Donovan|2345
sqlite>

Команда.schema

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

Синтаксис:

Schema ?TABLE?
где, ?TABLE ? - необязательное имя таблицы

sqlite> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE tbl1 (one integer primary key autoincrement, two varchar(50), three real);
CREATE TABLE tbl2 (ikey integer integer primary key, nm VARCHAR(20));
CREATE TABLE tbl3 (name VARCHAR(30), lastname VARCHAR(50), num INT);
CREATE UNIQUE INDEX itbl2 on tbl2(ikey);
sqlite>

Команда.tables

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

Синтаксис:

Tables ?PATTERN?
где?PATERN? - необязательное определение шаблона поиска

Пример:

sqlite> .tables
sqlite_sequence tbl1 tbl2 tbl3
sqlite> .tables %tb% "с шаблоном
tbl1 tbl2 tbl3
sqlite>

Команда.width

Используется для установки ширины столбцов, при формате вывода установленном в column.

Синтаксис:

Width NUM NUM:
где NUM - ширина соответствующего столбца в символах.

sqlite> .mode column
sqlite> .width 3 10 7
sqlite> SELECT * FROM tbl1;
1 hello 3.1
2 is 5.34
3 from
sqlite>

Это библиотека, написанная на языке C, которая обеспечивает работу с SQL. Данный инструмент относится к Реляционным системам управления базами данных . Большинство баз данных SQL работает по схеме клиент/сервер. Возьмём к примеру MySQL . В процессе работы данные берутся с MySQL сервера, и отправляются в качестве ответа на запрос. В случае использования SQLite, данные будут браться непосредственно с диска, т.е. не будет необходимости обращаться к серверу.

Установка

Мы будем взаимодействовать с базой данных через интерфейс командной строки sqlite3 (CLI) в Linux. Работа с sqlite3 CLI в MAC OS и Windows осуществляется таким же образом, однако я рекомендую вам потратить 5 минут на установку виртуальной машины, чтобы не захламлять свой компьютер лишним софтом.

Для установки sqlite3 на Linux выполняем команду:

sudo apt-get install sqlite3 libsqlite3-dev

В результате на вашей машине будет установлен sqlite3 . Для установки данного инструмента на других ОС следуйте инструкциям . Для запуска sqlite выполняем команду sqlite3 в консоли. Результат должен быть таким:

Во второй строчке указана подсказка о том, что для получения справки необходимо выполнить команду.help . Давайте сделаем это. В результате мы увидим Мета Команды и их описание.

Мета Команды

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

Стандартные команды

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

• Язык описания данных DDL : команды для создания таблицы, изменения и удаления баз данных, таблиц и прочего.

Язык управления данными DML : позволяют пользователю манипулировать данными (добавлять/изменять/удалять).

Язык запросов DQL : позволяет осуществлять выборку данных.

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

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

• Email
• Комментарий

Из всех этих полей только адрес сайта может быть пустым. Так же можем ввести колонку для нумерации комментриев. Назовём её post_id .

Теперь давайте определимся с типами данных для каждой из колонок:

Атрибут	Тип данных
post_id	INTEGER
name	TEXT
email	TEXT
website_url	TEXT
comment	TEXT

Вы сможете найти все типы данных, поддерживаемые в SQLite3.

Так же следует отметить, в SQLite3 данные, вставляемые в колонку могут отличаться от указанного типа. В MySQL такое не пройдёт.

Теперь давайте создадим базу данных. Если вы ещё находитесь в интерфейсе sqlite3, то наберите команду.quit для выхода. Теперь вводим:

sqlite3 comment_section.db

В результате, в текущем каталоге у нас появится файл comment_section.db .

Заметка : если не указать название файла, sqlite3 создаст временную базу данных.

Создание таблицы

Для хранения комментариев нам необходимо создать таблицу. Назовём её comments . Выполняем команду:

CREATE TABLE comments (post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL);

NOT NULL обеспечит уверенность, что ячейка не будет содержать пустое значение. PRIMARY KEY и AUTOINCREMENT расширяют возможности поля post_id .

Чтобы убедиться в том, что таблица была создана, выполняем мета команду.tables . В результате видим нашу таблицу comments .

Заметка : Для получения структуры таблицы наберите.schema comments

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

ВСТАВКА СТРОК

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

Name: Shivam Mamgain Email: [email protected] Website: shivammg.blogspot.com Comment: Great tutorial for beginners.

Для вставки воспользуемся командой INSERT .

INSERT INTO comments (name, email, website_url, comment) VALUES ("Shivam Mamgain", "[email protected]", "shivammg.blogspot.com", "Great tutorial for beginners.");

Указывать значение для post_id не нужно т.к. оно сформируется автоматически благодаря настройке AUTOINCREMENT .

Чтобы набить руку можете вставить ещё несколько строк.

ВЫБОРКА

Для выборки данных воспользуемся командой SELECT .

SELECT post_id, name, email, website_url, comment FROM comments;

Этот же запрос может выглядеть так:

SELECT * FROM comments;

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

Для отображения шапки введите.headers ON .

Для отображения колонок выполните команду.mode column .

Выполняем SELECT запрос ещё раз.

Заметка : вид отображения можно изменить, воспользовавшись мета командой.mode .

ОБНОВЛЕНИЕ

Предположим, что поле email для пользователя ‘Shivam Mamgain’ необходимо изменить на ‘[email protected]’. Выполняем следующую команду:

В результате запись будет изменена.

Заметка : Значение в колонке name может быть не уникально, так что в результате работы команды может быть затронуто более одной строки. Для всех пользователей, где значение name = ‘Shivam Mamgain’, поле email будет изменено на ‘[email protected]’. Для изменения какой-то конкретной строки следует её отследить по полю post_id . Мы его определили как PRIMARY KEY , что обеспечивает уникальность значения.