Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В этой записи вы познакомитесь с механизмом создания таблиц в базе данных под управлением SQLite. Мы понятно и подробно разберемся с тем, как можно создавать таблицы в базах данных SQLite3. Естественно, что данная публикация изобилует примерами создания таблиц и пояснением этих примеров. Если после прочтения данной записи у вас останутся вопросы о том, как создать таблицу в SQLite или даже в любой другой СУБД, то задавайте их в комментариях, разберемся вместе.

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

Синтаксис создания таблиц в базе данных SQLite3

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

Синтаксис создания таблицы в базе данных SQLite

Обратите внимание: создавать таблицы в SQLite мы можем р азличным образом, но любое предложение для создания таблицы в SQLite начинается с CREATE TABLE.  Если мы хотим создать временную таблицу в базе данных SQLite, то необходимо использовать ключевое слово TEMP или TEMPORARY. Хочу обратить ваше внимание на то, что имена таблиц должны быть уникальны во всей базе данных, поэтому, если вы создаете таблицу, но у вас есть подозрение, что таблица с таким именем существует, то можете использовать конструкцию IF NOT EXISTS, таким образом SQLite сначала проверит: существует ли таблица с таким именем, а уже затем создаст новую, если таблицы с таким именем нет. В базах данных SQLite вы можете создавать таблицы, используя так называемый квалификатор: полное имя таблицы. Для этого, перед именем таблицы вам следует указать имя базы данных, в которой вы хотите создать таблицу.

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

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

Создания таблицы в базе данных SQLite без описания столбцов

Создание таблицы в базе данных SQLite без указания типа данных (на самом деле в SQLite нет типов данных, а есть классы данных) для столбца выглядит следующим образом:

[php]

CREATE TABLE table1 (a, b, c, d);

[/php]

Как вы видите, в данном случае мы создадим таблицу с именем table1, в которой четыре столбца: a, b, c, d. Для столбцов в данном случае не указан класс данных, а так же не указаны ограничения, которые нужны для поддержания целостности данных.

Создание таблицы в базе данных SQLite3 с описанием столбцов и ограничениями

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

[php]

CREATE TABLE table2 (

a INTEGER PRIMARY KEY NOT NULL,

b TEXT  UNIQUE NOT NULL,

c REAL NOT NULL

);

[/php]

В первом примере мы создали таблицу table2 со столбцами a, b, c. И задали для каждого столбца описание: указали тип данных и ограничения: NOT NULL и PRIMARY KEY. Обратите внимание на терминологию SQLite знать не знает про реляционную базу данных и о том, что такое ключ или ключевой атрибут. Для SQLite первичный ключ – это ограничение, то есть некоторое правило, которое нельзя нарушать. Обратите внимание на то, что параметры столбца разделяются между собой пробелами.  А теперь давайте посмотрим второй пример создания таблицы в базе данных SQLite с указанием типов данных и ограничений:

[php]

CREATE TABLE table3 (

a INTEGER NOT NULL,

b TEXT  UNIQUE NOT NULL,

c REAL NOT NULL,

PRIMARY KEY (a)

);

[/php]

В данном случае мы объявили столбец «a» первичным ключом таблицы после описания столбцов, так тоже можно делать. Еще хочу обратить ваше внимание на то, что SQLite при создании таблицы неявно указывает сравнение данных для столбца. Если вам нужно явно указать SQLite как нужно сравнивать данные из столбца — используйте ключевое слово COLLATE.

Создание таблицы в базе данных SQLite с использованием квалификатора

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

[php]

CREATE TABLE world.table4 (

a INTEGER NOT NULL,

b TEXT UNIQUE NOT NULL,

c REAL NOT NULL,

PRIMARY KEY (a)

);

[/php]

Синтаксис создания таблицы с использованием квалификатора ничем не отличается от предыдущих примеров, но вместо имени таблицы теперь использован квалификатор: world.table4.

Проверить и создать таблицу в базе данных, если она не существует

Когда вы создаете новые таблицы, то не всегда знаете структура базы данных, а, соответственно, не всегда знаете имена уже существующих таблиц, поэтому SQLite позволяет проверять имена таблиц на уникальность перед их созданием, для этого используется конструкция IF NOT EXISTS:

[php]

CREATE TABLE IF NOT EXISTS TABLE table5 (

a INTEGER NOT NULL,

b TEXT  UNIQUE NOT NULL,

c REAL NOT NULL,

PRIMARY KEY (a)

);

[/php]

Если таблицы с именем table5 в базе данных не будет, то SQLite создаст эту таблицу, если таблица table5 будет в базе данных, то таблица создана не будет.

Создание временных таблиц в базе данных SQLite3

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

[php]

CREATE TEMP TABLE table6 (

a INTEGER NOT NULL,

b TEXT  UNIQUE NOT NULL,

c REAL NOT NULL,

PRIMARY KEY (a)

);

[/php]

Создание временных таблиц в SQLite ничем не отличается от создания обычных таблиц за исключением ключевого слова TEMP. Обратите внимание: вы легко можете отличить временную таблицу от обычной, воспользовавшись командой SQLite .tables.

[php]

— Посмотрим временную таблицу

.tables

[/php]

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

[php]

CREATE TEMPORARY TABLE table7 (

a INTEGER NOT NULL,

b TEXT  UNIQUE NOT NULL,

c REAL NOT NULL,

PRIMARY KEY (a)

);

[/php]

И затем проверить, что временная таблица действительно создалась.

[php]

— Посмотрим временную таблицу

.tables

[/php]

Создание таблиц в базе данных SQLIte по запросу SELECT

Теперь посмотрим на второй вариант создания таблиц в SQLite, который заключается в использование ключевого слова AS и команды SELECT. Всё очень просто: сперва вы пишите CREATE TABLE, затем указываете имя таблицы, вместо имени можно использовать квалификатор, затем идет ключевое слово AS, после которого идет подзапрос SELECT, который, на самом деле, может быть сколь угодно длинным.

[php]

CREATE TABLE IF NOT EXISTS table8 AS

SELECT id, name, population FROM city LIMIT 15;

[/php]

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

[php]

SELECT * FROM table8;

[/php]

А потом попробуйте воспользоваться командой .schema (на моем блоге есть публикация обо всех полезных командах шелла SQLite3), чтобы посмотреть каким запросом была создана таблица в SQLite.

[php]

.schema table8

[/php]

Хочу обратить ваше внимание на то, что ключевое слово AS можно использовать не только для создания таблицы, но и для указания псевдонима имени столбца при создании таблицы, давайте это посмотрим:

[php]

CREATE TABLE IF NOT EXISTS table9 AS

SELECT id AS ‘Nomer’, name AS ‘Gorod’, population AS ‘naselenie’ FROM city LIMIT 15;

[/php]

Затем выполним SELECT, чтобы убедиться в том, что имена столбцов будут отображаться, как Nomer, Gorod и naselenie.

[php]

SELECT * FROM table9;

[/php]

И посмотрим схему только что созданной таблицы.

[php]

.schema table9

[/php]

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