Определение структуры данных в SQLite
Создание и удаление таблицы. Прикрепление базы данных
Последнее обновление: 26.11.2021
Для создания таблиц используется команда CREATE TABLE . Общий формальный синтаксис команды CREATE TABLE :
CREATE TABLE название_таблицы (название_столбца1 тип_данных атрибуты_столбца1, название_столбца2 тип_данных атрибуты_столбца2, . название_столбцаN тип_данных атрибуты_столбцаN, атрибуты_уровня_таблицы )
После команды CREATE TABLE указывается название таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть уникальным. Кроме того, оно не должно начинаться на «sqlite_», поскольку названия таблиц, которые начинаются на «sqlite_», зарезервированы для внутреннего пользования.
Затем после названия таблицы в скобках перечисляются названия столбцов, их типы данных и атрибуты. В самом конце можно определить атрибуты для всей таблицы. Атрибуты столбцов, а также атрибуты таблицы указывать необязательно.
Создадим простейшую таблицу. Перед выполнением команды CREATE TABLE вне зависимости, что мы используем — консольный клиент sqlite3, графический клиент DB Browser for SQLite или какой-то другой клиент, вначале откроем базу данных, где мы хотим создать таблицу.
Для создания таблицы выполним следующий скрипт:
CREATE TABLE Users ( Id INTEGER, Name TEXT, Age INTEGER );
В данном случае таблица называется «Users». В ней определено три столбца: Id, Age, Name. Первые два столбца представляют идентификатор пользователя и его возраст и имеют тип INTEGER , то есть будут хранить числовые значения. Столбец «Name» представляет имя пользователя и имеет тип TEXT , то есть представляет строку. В данном случае для каждого столбца определены имя и тип данных, при этом атрибуты столбцов и таблицы в целом отсутствуют.
И в результате выполнения этой команды будет создана таблица Users с тремя столбцами.
Создание таблицы при ее отсутствии
Если мы повторно выполним выше определенную sql-команду для создания таблицы Users, то мы столкнемся с ошибкой — ведь мы уже создали таблицу с таким названием. Но могут быть ситуации, когда мы можем точно не знать или быть не уверены, есть ли в базе данных такая таблица (например, когда мы пишем приложение на каком-нибудь языке программирования и используем базу данных, которая не нами создана). И чтобы избежать ошибки, с помощью выражения IF NOT EXISTS мы можем задать создание таблицы, если она не существует:
CREATE TABLE IF NOT EXISTS Users ( Id INTEGER, Name TEXT, Age INTEGER );
Если таблицы Users нет, она будет создана. Если она есть, то никаких действий не будет производиться, и ошибки не возникнет.
Прикрепление базы данных
Также мы можем прикрепить базу данных и затем в ней уже создать базу данных.
Для прикрепления базы данных применяется команда ATTACH DATABASE :
ATTACH DATABASE 'C:\sqlite\test.db' AS test;
После команды ATTACH DATABASE указывается путь к файлу базы данных (в данном случае это путь «C:\sqlite\test.db»). Затем после оператора AS идет псевдоним, на который будет проецироваться база данных. То есть в коде для обращения к базе данных «C:\sqlite\test.db» будет применяться имя «test». При обращении к таблице из этой базы данных, сначала указывается псевдоним базы данных и через точку название таблицы:
псевдоним_бд.таблица
Например, создадим таблицу в прикрепленной базе данных:
ATTACH DATABASE 'C:\sqlite\test.db' AS test; CREATE TABLE test.users ( id INTEGER, name TEXT, age INTEGER );
Для создания таблицы users в бд test.db название таблицы предваряется псевдонимом: test.users .
И после открытия базы данных test.db в ней можно будеть увидеть таблицу users.
Удаление таблиц
Для удаления таблицы применяется команда DROP TABLE , после которой указывается название удаляемой таблицы. Например, удалим таблицу users:
DROP TABLE users;
По аналогии с созданием таблицы, если мы попытаемся удалить таблицу, которая не существует, то мы столкнемся с ошибкой. В этом случае опять же с помощью операторов IF EXISTS проверять наличие таблицы перед удалением:
DROP TABLE IF EXISTS users;
SQLite – создание базы данных
В SQLite команда sqlite3 используется для создания новой базы данных SQLite. Вам не нужно иметь никаких особых привилегий для создания базы данных.
Синтаксис
Ниже приведен основной синтаксис команды sqlite3 для создания базы данных:
$sqlite3 DatabaseName.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 dot для экспорта всей базы данных в текстовый файл, используя следующую команду SQLite в командной строке.
$sqlite3 testDB.db .dump > testDB.sql
Приведенная выше команда преобразует все содержимое базы данных testDB.db в операторы SQLite и помещает его в текстовый файл ASCII testDB.sql . Вы можете выполнить восстановление из сгенерированного testDB.sql простым способом:
$sqlite3 testDB.db testDB.sql
В данный момент ваша база данных пуста, так что вы можете попробовать две выше процедуры, если у вас есть несколько таблиц и данных в вашей базе данных. А пока давайте перейдем к следующей главе.
Создать базу данных SQLite (Управление данными)
Создает базу данных GeoPackage или SQLite , содержащую пространственный тип ST_Geometry или SpatiaLite .
Использование
- Параметр Имя выходной базы данных автоматически получит расширение, которое определяется значением параметра Пространственный тип . Если значение параметра пространственного типа – ST_Geometry или SpatiaLite , расширение выходного имени будет .sqlite . Если значение параметра пространственного типа – GeoPackage , расширение в имени будет .gpkg .
Параметры
Имя выходной базы данных
Местоположение создаваемой базы данных SQLite или GeoPackage и имя файла. Расширение .sqlite назначается автоматически ,если значение параметра Пространственный тип – ST_Geometry или SpatiaLite . Если значение параметра Пространственный тип – GeoPackage или любая версия GeoPackage , будет автоматически назначено расширение .gpkg .
Пространственный тип
(Дополнительный)
Указывает пространственный тип, который будет установлен с новой базой данных SQLite или версией GeoPackage , который будет создан.
- ST_Geometry — Будет установлен тип пространственного хранилища Esri . Это значение по умолчанию
- SpatiaLite — Будет установлен тип пространственного хранилища SpatiaLite .
- GeoPackage (эквивалент GeoPackage 1.3) — Создан набор данных OGC GeoPackage 1.3.
- GeoPackage 1.0 — Будет создан набор данных OGC GeoPackage 1.0.
- GeoPackage 1.1 — Будет создан набор данных OGC GeoPackage 1.1.
- GeoPackage 1.2.1 — Будет создан набор данных OGC GeoPackage 1.2.1.
- GeoPackage 1.3 — Будет создан набор данных OGC GeoPackage 1.3.
arcpy.management.CreateSQLiteDatabase(out_database_name, )
out_database_name
Местоположение создаваемой базы данных SQLite или GeoPackage и имя файла. Расширение .sqlite назначается автоматически ,если значение параметра spatial_type – ST_GEOMETRY или SPATIALITE . Если значение параметра spatial_type – GEOPACKAGE , будет автоматически назначено расширение .gpkg .
spatial_type
(Дополнительный)
Указывает пространственный тип, который будет установлен с новой базой данных SQLite или версией GeoPackage , который будет создан.
- ST_GEOMETRY — Будет установлен тип пространственного хранилища Esri . Это значение по умолчанию
- SPATIALITE — Будет установлен тип пространственного хранилища SpatiaLite .
- GEOPACKAGE — Создан набор данных OGC GeoPackage 1.3.
- GEOPACKAGE_1.0 — Будет создан набор данных OGC GeoPackage 1.0.
- GEOPACKAGE_1.1 — Будет создан набор данных OGC GeoPackage 1.1.
- GEOPACKAGE_1.2 — Будет создан набор данных OGC GeoPackage 1.2.1.
- GEOPACKAGE_1.3 — Будет создан набор данных OGC GeoPackage 1.3.
Пример кода
CreateSQLiteDatabase, пример 1 (окно Python)
Следующий скрипт окна Python показывает, как использовать инструмент CreateSQLiteDatabase в режиме прямого запуска для создани GeoPackage .
import arcpy arcpy.CreateSQLiteDatabase_management('c:/data/example.gpkg', 'GEOPACKAGE_1.2')CreateSQLiteDatabase, пример 2 (автономный скрипт)
Пример автономного скрипта для использования инструмента CreateSQLiteDatabase для создания базы данных SQLite , содержащей пространственный тип ST_Geometry.
import arcpy # Execute CreateSQLiteDatabase arcpy.CreateSQLiteDatabase_management('C:/data/example.sqlite', 'ST_GEOMETRY')Sqlite как создать базу данных
На этом шаге мы рассмотрим выполнение указанной операции .
Попробуем создать новую базу данных, для чего прежде всего запускаем командную строку, выбрав в меню Пуск пункт Выполнить . В открывшемся окне набираем команду cmd и нажимаем кнопку ОК — откроется черное окно с приглашением для ввода команд. Переходим в папку C:\book , выполнив команду (рисунок 1):
cd С:\book
В командной строке должно появиться приглашение:
С:\book>
По умолчанию в консоли используется кодировка ср866 . Чтобы сменить кодировку на ср1251 , в командной строке вводим команду:
chcp 1251
Теперь необходимо изменить название шрифта, т. к. точечные шрифты не поддерживают кодировку Windows 1251 . Щелкаем правой кнопкой мыши на заголовке окна и из контекстного меню выбираем пункт Свойства . В открывшемся окне переходим на вкладку Шрифт и в списке выделяем пункт Lucida Console (рисунок 1).
Рис.1. Выбор шрифта
На этой же вкладке также можно установить и размер шрифта. Нажимаем кнопку ОК , чтобы изменения вступили в силу. Для проверки правильности установки кодировки вводим команду chcp . Результат выполнения должен выглядеть так:
Рис.2. Кодовая стриница установлена верно
Для создания новой базы данных вводим команду:
C:\book>sqlite3.exe testdb.db
Если файл testdb.db не существует, новая база данных с этим именем будет создана и открыта для дальнейшей работы. Если такая база данных уже существует, то она просто откроется без удаления содержимого. Результат выполнения команды будет выглядеть так:
Рис.3. База данных testdb.db создана
Замечание . В примерах следующих шагов предполагается, что база данных была открыта указанным способом. Поэтому запомните способ изменения кодировки в консоли и способ создания (или открытия) базы данных.
Строка sqlite> здесь является приглашением для ввода SQL -команд. Каждая SQL -команда должна завершаться точкой с запятой. Если точку с запятой не ввести и нажать клавишу Enter , то приглашение примет вид . > . В качестве примера получим версию SQLite :
sqlite> SELECT sqlite_version();
Рис.4. Вывод версии базы данных
SQLite позволяет использовать комментарии. Однострочный комментарий начинается с двух тире и заканчивается в конце строки — в этом случае после комментария точку с запятой указывать не нужно. Многострочный комментарий начинается с комбинации символов /* и заканчивается комбинацией */ . Допускается отсутствие завершающей комбинации символов — в этом случае комментируется фрагмент до конца файла. Многострочные комментарии не могут быть вложенными. Если внутри многострочного комментария расположен однострочный комментарий, то он игнорируется. Пример использования комментариев:
Рис.5. Пример использования комментариев
Чтобы завершить работу с SQLite и закрыть базу данных, следует выполнить команду .exit или .quit .
Рис.7. Завершение работы с базой данных
На следующем шаге мы рассмотрим создание таблицы .