ИТ.03 - 09 - Основные типы данных SQLite. Работа с датой и временем. Создание и удаление таблиц

Введение

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

В этой теме мы рассмотрим:

• Основные типы данных в SQLite и их сравнение с MySQL
• Работу с датой и временем в SQLite
• Как создавать таблицы с помощью оператора CREATE TABLE
• Как удалять таблицы с помощью оператора DROP TABLE

Эти операции относятся к DDL (Data Definition Language) — языку определения данных, который позволяет создавать и изменять структуру базы данных.

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

Типы данных в SQLite

В отличие от большинства других СУБД, SQLite использует динамическую типизацию. Это означает, что тип значения определяется не по типу столбца таблицы, а по значению самого значения.

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

Основные типы данных SQLite

SQLite поддерживает следующие категории типов данных:

1. NULL

Значение NULL представляет собой "пустое" значение, отсутствие данных.

2. INTEGER

Целочисленные значения. В зависимости от величины числа SQLite использует разное количество байт для хранения: 1, 2, 3, 4, 6 или 8 байт

3. REAL

Числа с плавающей точкой (вещественные числа), хранятся в 8-байтовом формате IEEE.

4. TEXT

Текстовые строки, хранятся в кодировке UTF-8, UTF-16BE или UTF-16LE.

5. BLOB

Двоичные данные (Binary Large Object), хранятся "как есть", без преобразований.

6. NUMERIC

Числовой тип данных, который может хранить различные числовые значения, включая десятичные числа, логические значения, даты и другие числовые типы. SQLite преобразует объявленные типы данных, такие как DECIMAL, BOOLEAN, TIME, DATE, DATETIME в NUMERIC.

Как SQLite определяет типы данных (Type Affinity)

При создании таблиц в SQLite вы можете указывать различные типы данных для столбцов, но SQLite интерпретирует их по-своему. Эта особенность называется "типизирующее сходство" или "type affinity".

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

1. Числовой тип (INTEGER) — для целых чисел
   Если вы укажете тип INT, BIGINT и т.д., SQLite будет считать это числовым типом INTEGER
2. Вещественный тип (REAL) — для чисел с плавающей точкой
   Если вы укажете тип FLOAT, DOUBLE, SQLite будет считать его вещественным типом REAL
3. Текстовый тип (TEXT) — для строк
   Если вы укажете тип CHAR, VARCHAR, TEXT и т.д., SQLite будет считать его текстовым типом TEXT
4. Двоичный тип (BLOB) — для бинарных данных
   Если вы укажете тип BLOB, SQLite будет считать его двоичным типом BLOB
5. Числовой тип (NUMERIC) — для всех остальных случаев
   Если вы укажете тип DECIMAL, NUMERIC, SQLite будет считать его числовым типом NUMERIC

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

Сравнение типов данных SQLite и MySQL

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

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

Работа с датой и временем в SQLite

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

В SQLite есть три формата даты и времени: TEXT, INTEGER и REAL.

формат TEXT

Строка в формате "YYYY-MM-DD HH:MM:SS.SSS". Это наиболее распространенный формат.

Пример: "2025-11-05 15:30:00.000"

формат INTEGER (Unix time)

Число секунд с начала эпохи Unix, то есть с 1970-01-01 00:00:00 UTC.

формат REAL

Число дней с начала юлианской эпохи, то есть с полудня 24 ноября 4714 г. до н.э.

Функции работы с датой и временем

SQLite предоставляет следующие функции для работы с датами и временем:

date()

date(timestring, modifier, modifier, ...)

Возвращает дату в формате "YYYY-MM-DD".

Примеры:

-- Текущая дата
SELECT date('now');

-- Дата через 3 дня
SELECT date('now', '+3 days');

-- Дата начала текущего месяца
SELECT date('now', 'start of month');

time()

time(timestring, modifier, modifier, ...)

Возвращает время в формате "HH:MM:SS".

-- Сохраняем в UTC
INSERT INTO table (event_time) VALUES (datetime('now', 'utc'));

-- При выводе конвертируем в нужный часовой пояс
SELECT datetime(event_time, '+3 hours') as local_time;

Примеры:

-- Текущее время (UTC)
SELECT time('now');

-- Текущее время (локальное)
SELECT time('now', 'localtime');

-- Время через 3 часа
SELECT time('now', '+3 hours');

datetime()

datetime(timestring, modifier, modifier, ...)

Возвращает дату и время в формате "YYYY-MM-DD HH:MM:SS".

Примеры:

-- Текущая дата и время (UTC)
SELECT datetime('now');

-- Текущая дата и время (локальное)
SELECT datetime('now', 'localtime');

-- Дата и время через 1 час
SELECT datetime('now', '+1 hour');

-- Дата и время через 1 неделю
SELECT datetime('now', '+7 days');

strftime()

strftime(format, timestring, modifier, modifier, ...)

Возвращает дату/время в заданном формате.

Основные переменные формата:

Примеры:

-- Год
SELECT strftime('%Y', 'now');

-- Месяц и год
SELECT strftime('%m-%Y', 'now');

-- День месяца
SELECT strftime('%d', 'now');

-- День недели (0-6, где 0=воскресенье)
SELECT strftime('%w', 'now');

julianday()

julianday(timestring, modifier, modifier, ...)

Функция julianday() преобразует указанную дату/время в юлианскую дату — количество дней, прошедших с начала юлианской эры (4714 год до н.э.) до указанной даты, включая дробную часть для точного представления времени суток.

Примеры:

-- Юлианский день для текущей даты
SELECT julianday('now');

-- Разница в днях между двумя датами
SELECT julianday('2025-12-31') - julianday('2025-01-01');

Модификаторы даты и времени

Модификаторы позволяют изменять дату и время:

• NNN days, NNN hours, NNN minutes, NNN seconds
• NNN months, NNN years
• utc, localtime (преобразование часовых поясов)
• start of month, start of year, start of day

Примеры:

-- Первый день следующего месяца
SELECT date('now', '+1 month', 'start of month');

-- Последний день текущего месяца
SELECT date('now', 'start of month', '+1 month', '-1 day');

Создание таблиц (CREATE TABLE)

Для создания новой таблицы в базе данных используется оператор CREATE TABLE.

Базовый синтаксис

CREATE TABLE [IF NOT EXISTS] имя_таблицы (
  имя_столбца1 тип_данных [ограничения],
  имя_столбца2 тип_данных [ограничения],
  ...
  [ограничения_таблицы]
);

-- здесь может идти код создания таблицы:
##CODE##

-- запрашиваем структуру таблицы:
PRAGMA table_info('employees');

Пример простой таблицы

Рассмотрим создание таблицы для хранения информации о сотрудниках:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  salary INTEGER NOT NULL,
  department TEXT,
  hire_date TEXT DEFAULT (date('now'))
);

В этом примере:

• id — целочисленный столбец, являющийся первичным ключом
• first_name и last_name — текстовые столбцы, обязательные для заполнения
• salary — целочисленный столбец, обязательный для заполнения
• department — текстовый столбец, необязательный (может содержать NULL)
• hire_date — текстовый столбец с датой найма по умолчанию

.schema employees

Ограничения (Constraints)

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

PRIMARY KEY

Уникальный идентификатор записи в таблице. Может быть только один первичный ключ в таблице.

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

NOT NULL

Запрещает хранить в столбце значение NULL.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT
);

UNIQUE

Гарантирует уникальность значений в столбце.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT UNIQUE,
  email TEXT UNIQUE
);

CHECK

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

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER CHECK (age >= 0 AND age < 150),
  salary INTEGER CHECK (salary > 0)
);

DEFAULT

Задает значение по умолчанию для столбца.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  quantity INTEGER DEFAULT 1,
  status TEXT DEFAULT 'pending',
  created_at TEXT DEFAULT (datetime('now'))
);

FOREIGN KEY

Создает связь между таблицами (внешний ключ).

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  FOREIGN KEY (customer_id) REFERENCES customers (id),
  FOREIGN KEY (product_id) REFERENCES products (id)
);

Пример сложной таблицы с ограничениями

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
  price REAL NOT NULL CHECK (price >= 0),
  order_date TEXT DEFAULT (date('now')),
  delivery_date TEXT,
  status TEXT DEFAULT 'pending' CHECK (
    status IN (
      'pending',
      'shipped',
      'delivered',
      'cancelled'
    )
  ),
  CHECK (delivery_date >= order_date OR delivery_date IS NULL)
);

PRAGMA table_info(orders);

В этом примере:

• id — первичный ключ
• customer_name и product_name — обязательные текстовые поля
• quantity — целое число по умолчанию 1, с проверкой на положительность
• price — вещественное число с проверкой на неотрицательность
• order_date — дата заказа по умолчанию (текущая дата)
• delivery_date — дата доставки (может быть NULL)
• status — текстовое поле с ограниченным набором значений
• Проверка, что дата доставки не меньше даты заказа

Удаление таблиц (DROP TABLE)

Для удаления таблицы используется оператор DROP TABLE.

Базовый синтаксис

DROP TABLE [IF EXISTS] имя_таблицы;

Примеры

Удаление существующей таблицы:

DROP TABLE employees;

Безопасное удаление таблицы (не вызывает ошибку, если таблицы не существует):

DROP TABLE IF EXISTS non_existent_table;

Самопроверка

Практические задания

Задание 1. CREATE TABLE + проверка структуры через PRAGMA

-- Ваш код можете писать тут

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER NOT NULL,
  group_name TEXT,
  gpa REAL DEFAULT 0.0,
  enrollment_date TEXT DEFAULT (date('now'))
);

PRAGMA table_info('students');

Задание 2. CREATE TABLE с ограничениями (UNIQUE, CHECK, DEFAULT)

-- Ваш код можете писать тут

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL,
  price REAL NOT NULL CHECK (price > 0),
  in_stock INTEGER DEFAULT 0 CHECK (in_stock >= 0),
  created_at TEXT DEFAULT (datetime('now'))
);

PRAGMA table_info('products');

Задание 3. DROP TABLE IF EXISTS — безопасное удаление

-- Ваш код можете писать тут

DROP TABLE IF EXISTS non_existent_table;

Задание 4. Функции date/time/datetime в SQLite

-- Ваш код можете писать тут

-- 1. Текущая дата
SELECT date('now');

-- 2. Текущее время
SELECT time('now');

-- 3. Текущая дата и время
SELECT datetime('now');

Задание 5. Работа с модификаторами дат и времени

-- Ваш код можете писать тут

-- 1. Дата через 5 дней
SELECT date('now', '+5 days');

-- 2. Дата начала текущего месяца
SELECT date('now', 'start of month');

-- 3. Дата и время через 2 часа
SELECT datetime('now', '+2 hours');

Задание 6. Создаём таблицу событий и выводим отсортированный список

-- Ваш код можете писать тут

-- Создание таблицы
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  start_date TEXT NOT NULL,
  end_date TEXT,
  created_at TEXT DEFAULT (datetime('now'))
);

-- Вставка записей
INSERT INTO events (title, start_date, end_date) VALUES
  ('Проектное собрание', '2025-11-10', '2025-11-10'),
  ('Конференция', '2025-12-01', '2025-12-03'),
  ('Дедлайн отчета', '2025-11-15', NULL);

-- Все события, отсортированные по дате начала
SELECT * FROM events ORDER BY start_date;