Справочный гайд по командам SQLite

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

Полное руководство по SQLite: от установки до продвинутого использования

Оглавление

  1. Введение в SQLite
  2. Установка SQLite
  3. Основы работы с SQLite
  4. Управление базами данных
  5. Работа с таблицами
  6. Типы данных
  7. CRUD-операции
  8. Индексы и оптимизация
  9. Транзакции
  10. Представления и триггеры
  11. Импорт и экспорт данных
  12. Интеграция с языками программирования
  13. Оптимизация производительности
  14. Резервное копирование
  15. Безопасность

Введение в SQLite

SQLite — это встраиваемая реляционная СУБД, которая:

  • Не требует сервера (работает с файлами напрямую)
  • Поддерживает стандартный SQL
  • Имеет минимальные требования к ресурсам
  • Используется в мобильных приложениях, браузерах и встроенных системах

Установка SQLite

Windows

  1. Скачать предварительно скомпилированные бинарники с официального сайта
  2. Добавить путь к sqlite3.exe в переменную PATH

Linux (Ubuntu/Debian)

sudo apt update
sudo apt install sqlite3 libsqlite3-dev

MacOS

brew install sqlite

Проверка установки

sqlite3 --version

Основы работы с SQLite

Запуск интерфейса командной строки

sqlite3 mydatabase.db

Основные команды интерфейса

.help       -- Показать все команды
.tables     -- Показать список таблиц
.schema     -- Показать структуру таблиц
.mode       -- Изменить режим вывода (csv, column, json и др.)
.headers on -- Показать заголовки столбцов
.quit       -- Выйти из интерфейса

Управление базами данных

Создание/открытие базы данных

sqlite3 database.db

Резервное копирование

-- В командной строке SQLite:
.output backup.sql
.dump
.output stdout

-- Или через командную строку ОС:
sqlite3 database.db .dump > backup.sql

Восстановление из резервной копии

sqlite3 newdatabase.db < backup.sql

Работа с таблицами

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

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Изменение таблицы

-- Добавление столбца
ALTER TABLE users ADD COLUMN is_active INTEGER DEFAULT 1;

-- Удаление таблицы
DROP TABLE users;

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

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL CHECK(price > 0),
    category_id INTEGER REFERENCES categories(id),
    UNIQUE(name, category_id)
);

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

SQLite использует динамическую типизацию. Основные типы:

  1. NULL - значение NULL
  2. INTEGER - целое число
  3. REAL - число с плавающей точкой
  4. TEXT - текстовая строка
  5. BLOB - бинарные данные

CRUD-операции

Вставка данных (Create)

INSERT INTO users (username, email, age)
VALUES ('john_doe', 'john@example.com', 30);

-- Множественная вставка
INSERT INTO users (username, email, age)
VALUES 
    ('alice', 'alice@example.com', 25),
    ('bob', 'bob@example.com', 28);

Чтение данных (Read)

-- Простой запрос
SELECT * FROM users;

-- С условиями
SELECT username, email FROM users WHERE age > 25;

-- Сортировка
SELECT * FROM users ORDER BY age DESC;

-- Группировка
SELECT age, COUNT(*) FROM users GROUP BY age;

-- Ограничение вывода
SELECT * FROM users LIMIT 10 OFFSET 5;

Обновление данных (Update)

UPDATE users SET age = 31 WHERE username = 'john_doe';

Удаление данных (Delete)

DELETE FROM users WHERE id = 5;

Индексы и оптимизация

Создание индекса

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);

Удаление индекса

DROP INDEX idx_users_email;

Анализ запросов

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'john@example.com';

Транзакции

Использование транзакций

BEGIN TRANSACTION;
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE users SET last_transaction = CURRENT_TIMESTAMP WHERE id = 1;
COMMIT;

-- В случае ошибки:
ROLLBACK;

Представления и триггеры

Создание представления

CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = 1;

Создание триггера

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

Импорт и экспорт данных

Импорт из CSV

.mode csv
.import data.csv tablename

Экспорт в CSV

.headers on
.mode csv
.output data.csv
SELECT * FROM tablename;
.output stdout

Импорт/экспорт JSON

-- Для работы с JSON требуется SQLite версии 3.38.0+
SELECT json_object('id', id, 'name', username) FROM users;

Интеграция с языками программирования

Python

import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

conn.close()

Node.js

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('database.db');

db.all("SELECT * FROM users", (err, rows) => {
    console.log(rows);
});

db.close();

Оптимизация производительности

  1. Используйте транзакции для групповых операций
  2. Создавайте индексы для часто используемых полей
  3. Используйте PRAGMA команды для настройки: 
    PRAGMA journal_mode = WAL;  -- Режим записи журнала
PRAGMA synchronous = NORMAL; -- Баланс между надежностью и скоростью
PRAGMA cache_size = -2000;  -- Размер кеша в KB

Резервное копирование

Полное резервное копирование

sqlite3 database.db ".backup backup.db"

Инкрементное резервное копирование

Используйте API SQLite для реализации инкрементного бэкапа в вашем приложении.

Безопасность

  1. Проверяйте входные данные перед выполнением SQL-запросов
  2. Используйте параметризованные запросы: 
    # Python пример
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
  3. Ограничивайте права доступа к файлу базы данных на уровне ОС

Заключение

SQLite — мощная и легковесная СУБД, идеально подходящая для:

  • Мобильных приложений
  • Встраиваемых систем
  • Локальных приложений
  • Тестирования и прототипирования

Для более сложных сценариев обратитесь к официальной документации.