SQLite: CREATE TABLE, ALTER TABLE и DROP — работа со структурой таблиц

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

Полезные ссылки:

• Основы SQL в SQLite: начало работы с запросами (Урок 3)
• CRUD операции в SQLite: вставка, выбор, обновление и удаление данных (Урок 5)

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

Основы создания таблиц

Таблицы в SQLite создаются с помощью команды CREATE TABLE. Эта команда позволяет определять структуру таблицы, включая названия столбцов и их типы данных.

Синтаксис команды CREATE TABLE

CREATE TABLE имя_таблицы (
    имя_столбца1 тип_данных [ОГРАНИЧЕНИЯ],
    имя_столбца2 тип_данных [ОГРАНИЧЕНИЯ],
    ...
);

Пример создания простой таблицы

Рассмотрим пример создания таблицы users с тремя столбцами: id, name и email.

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

Объяснение типов данных и их использования

В SQLite есть несколько основных типов данных:

• INTEGER: Целочисленные значения.
• REAL: Числа с плавающей точкой.
• TEXT: Текстовые строки.
• BLOB: Двоичные данные.
• NULL: Отсутствие значения.

Практические примеры создания таблиц с различными типами данных

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0
);

Как посмотреть структуру таблицы

Чтобы узнать какие столбцы есть в таблице и их типы — используйте PRAGMA table_info:

PRAGMA table_info(users);
-- Результат: cid | name  | type    | notnull | dflt_value | pk
--            0   | id    | INTEGER | 1       |            | 1
--            1   | name  | TEXT    | 1       |            | 0
--            2   | email | TEXT    | 1       |            | 0

Изменение структуры таблиц в SQLite

Обзор команды ALTER TABLE

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

Добавление новых столбцов к существующей таблице

Вы можете добавить новый столбец в таблицу с помощью команды ADD COLUMN.

ALTER TABLE users ADD COLUMN age INTEGER;

Изменение типа данных столбца

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

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

Вы можете переименовать таблицу с помощью команды RENAME TO.

ALTER TABLE users RENAME TO customers;

Для переименования столбцов также создайте новую таблицу и перенесите данные.

Удаление столбца в SQLite

Начиная с SQLite 3.35.0 (2021) удалить столбец можно напрямую:

-- SQLite 3.35+ — прямое удаление столбца
ALTER TABLE users DROP COLUMN age;

Если у вас более старая версия SQLite — используйте обходной путь через пересоздание таблицы:

-- Шаг 1: новая таблица без ненужного столбца
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

-- Шаг 2: перенос данных
INSERT INTO users_new SELECT id, name, email FROM users;

-- Шаг 3: удаляем старую таблицу
DROP TABLE users;

-- Шаг 4: переименовываем новую
ALTER TABLE users_new RENAME TO users;

Проверить версию SQLite: SELECT sqlite_version();

Лучшие практики работы с таблицами в SQLite

Советы по проектированию таблиц

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

Обеспечение целостности данных

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

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

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

CREATE INDEX idx_users_email ON users (email);

Примеры реальных сценариев использования таблиц

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

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