SQL-2 — Изменение данных: CRUD

Читать данные из базы — это половина работы любого приложения. Когда пользователь регистрируется, вы не просто показываете ему список — вы создаёте запись в базе. Сменил email — запись нужно обновить. Удалил аккаунт — удалить. Вот три сценария, ради которых существуют INSERT, UPDATE и DELETE.

Прежде чем писать первый INSERT — нужна таблица. Поэтому начнём с CREATE TABLE. SQL делится на два раздела: DDL (Data Definition Language) — язык структуры, отвечает за создание и изменение таблиц, и DML (Data Manipulation Language) — язык данных, отвечает за SELECT, INSERT, UPDATE, DELETE. В прошлом уроке мы работали с DML. Сегодня разберём оба.

CREATE TABLE — проектируем структуру

SQLite — гибкая база с минимальным набором типов хранения: их всего пять.

Это не PostgreSQL и не MySQL, где типов десятки. Даты в SQLite хранят как TEXT — именно поэтому тип created_at в примере ниже будет TEXT, а не DATETIME.

Теперь создадим таблицу пользователей — откройте dbfiddle.uk, выберите движок SQLite и вставьте:

CREATE TABLE users (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    username   TEXT    NOT NULL UNIQUE,
    email      TEXT    NOT NULL UNIQUE,
    role       TEXT    NOT NULL DEFAULT 'viewer',
    score      REAL    DEFAULT 0.0,
    created_at TEXT    DEFAULT (datetime('now'))
);

Разберём ограничения — они защищают качество данных прямо на уровне базы:

• PRIMARY KEY AUTOINCREMENT — id уникален для каждой строки, SQLite назначает следующее число автоматически, вам думать не нужно.
• NOT NULL — поле обязательно. Попытка вставить запись без username завершится ошибкой ещё до того, как данные попадут в таблицу.
• UNIQUE — каждое значение в колонке уникально. Два пользователя с одинаковым email — невозможно.
• DEFAULT — значение по умолчанию, если при вставке поле не указано. role получит 'viewer', score — 0.0.
• datetime('now') — встроенная функция SQLite, вычисляется в момент каждой вставки и записывает текущее UTC-время в формате строки: '2025-01-15 10:30:00'. Нужно локальное время — используйте datetime('now', 'localtime').

INSERT — добавляем данные

Вставим первого пользователя:

INSERT INTO users (username, email, role)
VALUES ('alice', 'alice@example.com', 'admin');

После INSERT INTO — имя таблицы. В скобках — список колонок, которые заполняем явно. VALUES — значения в том же порядке. Обратите внимание: id и created_at не передаём — они заполнятся автоматически. score тоже пропустили — получит 0.0 по умолчанию.

Сразу проверим:

SELECT * FROM users;

id | username | email             | role  | score | created_at
---|----------|-------------------|-------|-------|--------------------
1  | alice    | alice@example.com | admin | 0.0   | 2025-01-15 10:30:00

Запись появилась. Теперь вставим несколько строк за один запрос — это эффективнее, чем три отдельных INSERT:

INSERT INTO users (username, email)
VALUES
    ('bob',   'bob@example.com'),
    ('carol', 'carol@example.com'),
    ('dave',  'dave@example.com');

SELECT * FROM users;

id | username | email             | role   | score
---|----------|-------------------|--------|------
1  | alice    | alice@example.com | admin  | 0.0
2  | bob      | bob@example.com   | viewer | 0.0
3  | carol    | carol@example.com | viewer | 0.0
4  | dave     | dave@example.com  | viewer | 0.0

bob, carol и dave получили role = 'viewer' — значение DEFAULT, потому что мы колонку не указали.

Одно важное правило по INSERT: всегда перечисляйте колонки явно. Вставка без списка колонок — INSERT INTO users VALUES (...) — требует передать значения для всех колонок в правильном порядке. Если потом добавить в таблицу новую колонку, такой запрос сломается. Явный список колонок защищает от этого.

UPDATE и DELETE — и главная ловушка

UPDATE обновляет существующие записи. Синтаксис:

UPDATE users
SET score = 9.5
WHERE username = 'alice';

SET — что именно меняем. WHERE — каких строк касается изменение. Результат:

id | username | score   (остальные поля не изменились)
---|----------|--------
1  | alice    | 9.5     ← обновилась
2  | bob      | 0.0
3  | carol    | 0.0
4  | dave     | 0.0

Только alice. Остальные не тронуты — потому что WHERE username = 'alice' ограничил область действия.

А теперь — самая дорогостоящая ошибка в SQL. Та, которую разработчики совершают в продакшене:

UPDATE users
SET role = 'admin';

id | username | role
---|----------|-------
1  | alice    | admin  ← была admin
2  | bob      | admin  ← была viewer
3  | carol    | admin  ← была viewer
4  | dave     | admin  ← была viewer

Все четыре пользователя стали admin. SQLite не спросил подтверждения, не предупредил — просто обновил каждую строку таблицы.

⚠️ Важно: UPDATE без WHERE — это не синтаксическая ошибка. База выполнит запрос успешно и обновит все строки без исключения. Именно поэтому перед любым UPDATE или DELETE в продакшене сначала выполняйте SELECT с тем же WHERE — убедитесь что выбираете нужные строки, и только потом пишите изменяющий запрос.

DELETE работает по той же логике:

DELETE FROM users
WHERE username = 'dave';

dave удалён. Остальные на месте. А без WHERE:

-- НЕ ВЫПОЛНЯТЬ без осознанной цели:
DELETE FROM users;

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

UPSERT — вставить или обновить

UPSERT (update + insert) — операция, которая пробует вставить запись, а если такая уже существует — обновляет её.

Зачем это нужно? Представьте синхронизацию с сервером: приходит список пользователей, часть из них уже есть в локальной базе, часть — новые. Без UPSERT пришлось бы делать SELECT, проверять наличие записи в коде, потом INSERT или UPDATE. UPSERT решает это одной командой прямо в базе.

В SQLite два способа:

Вариант 1 — INSERT OR REPLACE:

INSERT OR REPLACE INTO users (username, email, role, score)
VALUES ('alice', 'alice@example.com', 'admin', 10.0);

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

Вариант 2 — ON CONFLICT DO UPDATE:

INSERT INTO users (username, email, score)
VALUES ('bob', 'bob@example.com', 7.5)
ON CONFLICT(username) DO UPDATE SET
    score = excluded.score;

Здесь при конфликте по username запись не удаляется — обновляется только указанное поле. excluded — специальное ключевое слово SQLite, ссылается на строку, которую мы пытались вставить. excluded.score означает «то значение score, которое передано в VALUES». id и все остальные поля при этом остаются нетронутыми.

Когда что использовать: INSERT OR REPLACE — если id не важен; ON CONFLICT DO UPDATE — если нужно сохранить id и обновить только конкретные поля.

Как это выглядит в ORM

ORM (Object-Relational Mapping) — это прослойка, которая позволяет работать с базой через объекты вашего языка, не писать SQL вручную. Но под капотом ORM генерирует тот же самый SQL, который мы сегодня писали руками. Если вы понимаете SQL — вы понимаете что происходит в любом ORM.

Посмотрим на три фреймворка из ваших треков.

Room (Android, Kotlin)

// Описание таблицы — @Entity = CREATE TABLE
@Entity(
    tableName = "users",
    indices = [
        Index(value = ["username"], unique = true), // те же UNIQUE-ограничения
        Index(value = ["email"], unique = true)
    ]
)
data class UserEntity(
    @PrimaryKey(autoGenerate = true) val id: Int = 0, // = INTEGER PRIMARY KEY AUTOINCREMENT
    val username: String,
    val email: String,
    val role: String = "viewer",
    val score: Double = 0.0
)

// DAO — интерфейс с операциями
@Dao
interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE) // = INSERT OR REPLACE
    suspend fun insert(user: UserEntity)

    @Update
    suspend fun update(user: UserEntity)

    @Delete
    suspend fun delete(user: UserEntity)

    // Кастомный SQL с WHERE — пишем напрямую
    @Query("UPDATE users SET role = :role WHERE username = :username")
    suspend fun updateRole(username: String, role: String)
}

@Entity — аннотация, которая говорит Room «этот класс — таблица». @Dao помечает интерфейс с методами для работы с БД. suspend fun — механизм асинхронности Kotlin: запрос выполняется не в главном потоке, приложение не зависает. OnConflictStrategy.REPLACE — это тот самый INSERT OR REPLACE. Для кастомных запросов с конкретным WHERE — @Query с прямым SQL.

Exposed (Kotlin Backend)

// Описание таблицы
object Users : Table("users") {
    val id       = integer("id").autoIncrement()
    val username = varchar("username", 50).uniqueIndex()
    val email    = varchar("email", 100).uniqueIndex()
    val role     = varchar("role", 20).default("viewer")
    val score    = double("score").default(0.0)
    override val primaryKey = PrimaryKey(id)
}

// CRUD в транзакции
transaction {
    Users.insert { it[username] = "alice"; it[email] = "alice@example.com" }
    Users.update({ Users.username eq "alice" }) { it[score] = 9.5 }
    Users.deleteWhere { Users.username eq "dave" }
    Users.upsert { it[username] = "bob"; it[email] = "bob@example.com"; it[score] = 7.5 }
}

.autoIncrement(), .uniqueIndex(), .default() — те же ограничения из CREATE TABLE, но через Kotlin API. Все операции — внутри transaction {}: это гарантирует что либо все изменения применятся, либо ни одно.

SQLAlchemy (Python, AI-трек)

class User(Base):
    __tablename__ = "users"
    id       = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String, nullable=False, unique=True)  # NOT NULL UNIQUE
    email    = Column(String, nullable=False, unique=True)
    role     = Column(String, nullable=False, default="viewer")
    score    = Column(Float, default=0.0)

with Session(engine) as session:
    session.add(User(username="alice", email="alice@example.com", role="admin"))  # INSERT
    user = session.query(User).filter_by(username="alice").first()
    user.score = 9.5        # UPDATE — изменение атрибута
    session.delete(user)    # DELETE
    session.merge(User(username="bob", email="bob@example.com", score=7.5))  # UPSERT
    session.commit()

nullable=False — NOT NULL, unique=True — UNIQUE. session.merge() — UPSERT: вставит запись если её нет, обновит если есть.

Сводная таблица соответствий:

Итого

Сегодня мы прошли полный цикл изменения данных — от создания структуры до всех четырёх операций.

CREATE TABLE с ограничениями NOT NULL, UNIQUE, DEFAULT и PRIMARY KEY AUTOINCREMENT — это фундамент, который защищает качество данных ещё до того, как приложение успело что-то сломать.

INSERT, UPDATE, DELETE — три операции DML, которые вы будете использовать в каждом проекте. Главное правило, которое стоит запомнить сейчас: UPDATE и DELETE без WHERE — не ошибка синтаксиса, а молчаливое изменение всей таблицы. Сначала SELECT с тем же условием, потом изменяющий запрос.

UPSERT через INSERT OR REPLACE или ON CONFLICT DO UPDATE — инструмент для синхронизации данных. Выбор между ними зависит от одного вопроса: важен ли вам id существующей записи.

ORM в Room, Exposed и SQLAlchemy генерирует тот же SQL под капотом. Синтаксис разный — логика операций одна.

• Создал таблицу users в dbfiddle.uk с ограничениями NOT NULL, UNIQUE, DEFAULT, AUTOINCREMENT
• Написал INSERT с явным списком колонок — одну запись и несколько за раз
• Написал UPDATE с WHERE и убедился что без WHERE изменяются все строки
• Написал DELETE с WHERE
• Понимает разницу между INSERT OR REPLACE и ON CONFLICT DO UPDATE по поведению с id
• Может соотнести @Insert, .insert {}, session.add() с SQL-командой INSERT

В следующем уроке — SQL-3: JOIN. Как объединить данные из нескольких таблиц одним запросом — и зачем вообще разбивать данные по разным таблицам.