Модификация данных и структура БД

0. DDL vs DML: два семейства команд

В SQL команды делятся на две большие группы:

DDL (Data Definition Language) — работает со структурой базы данных:

• CREATE TABLE — создать таблицу
• ALTER TABLE — изменить структуру таблицы
• DROP TABLE — удалить таблицу целиком

DML (Data Manipulation Language) — работает с данными внутри таблиц:

• INSERT — вставить строки
• UPDATE — обновить строки
• DELETE — удалить строки

💡 Простая аналогия: DDL — это строительство дома (стены, комнаты), DML — это расстановка мебели внутри.

1. Типы данных в PostgreSQL

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

Числовые типы

⚠️ Для денег всегда используй NUMERIC, а не REAL.REAL даёт погрешности: 0.1 + 0.2 может быть не равно 0.3.

Строковые типы

💡 В PostgreSQL TEXT и VARCHAR практически идентичны по производительности.VARCHAR(n) полезен только если нужно ограничить длину (валидация на уровне БД).

Дата и время

💡 Всегда используй TIMESTAMPTZ, если работаешь с разными часовыми поясами. Это спасёт тебя от множества багов.

Булев тип

Специальные типы (упоминаем)

• UUID — универсальный уникальный идентификатор
• JSON / JSONB — для хранения JSON-документов (JSONB быстрее и индексируется)
• ARRAY — массивы значений

2. Создание таблиц: CREATE TABLE

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

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Создадим таблицу departments

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    budget NUMERIC(12, 2) NOT NULL DEFAULT 0,
    location VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Разберём по частям:

• id SERIAL PRIMARY KEY — автоинкрементный первичный ключ
• name VARCHAR(100) NOT NULL UNIQUE — строка до 100 символов, обязательная, уникальная
• budget NUMERIC(12, 2) NOT NULL DEFAULT 0 — число с двумя знаками после запятой, обязательное, по умолчанию 0
• location VARCHAR(100) — необязательная строка
• created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP — дата создания, по умолчанию — текущее время

💡 SERIAL — это удобство PostgreSQL. На самом деле он создаёт последовательность (sequence) и делает столбец автоинкрементным. В современном SQL (PostgreSQL 10+) можно использовать более стандартный синтаксис: id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

Создадим таблицу employees

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id),
    city VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2) NOT NULL CHECK (salary > 0),
    bonus NUMERIC(10, 2),
    is_active BOOLEAN DEFAULT TRUE,
    hire_date DATE DEFAULT CURRENT_DATE
);

Здесь появились:

• REFERENCES departments(id) — внешний ключ, ссылка на departments.id
• CHECK (salary > 0) — ограничение: зарплата должна быть положительной
• DEFAULT TRUE / DEFAULT CURRENT_DATE — значения по умолчанию

Создадим таблицу projects

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    CHECK (end_date IS NULL OR end_date >= start_date)
);

💡 ON DELETE SET NULL — что делать с проектом, если удалят отдел. Варианты:

• CASCADE — удалить и проект
• SET NULL — обнулить department_id
• RESTRICT / NO ACTION — запретить удаление отдела
• SET DEFAULT — подставить значение по умолчанию

3. Ограничения (Constraints): подробно

Ограничения — это правила, которые защищают целостность данных.

PRIMARY KEY (PK)

• Уникально идентифицирует строку
• Не может быть NULL
• В таблице может быть только один PK
• Может состоять из нескольких столбцов (составной ключ)

-- Составной первичный ключ:
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

FOREIGN KEY (FK)

• Ссылается на PK (или UNIQUE) другой таблицы
• Обеспечивает ссылочную целостность: нельзя вставить сотрудника в несуществующий отдел

department_id INTEGER REFERENCES departments(id)

UNIQUE

• Значения в столбце должны быть уникальны
• В отличие от PK, может быть NULL (один раз)
• Может быть несколько UNIQUE в таблице

email VARCHAR(255) UNIQUE

NOT NULL

• Значение не может быть NULL
• Обязательное поле

CHECK

Произвольное условие, которое должно выполняться:

CHECK (salary > 0)
CHECK (end_date >= start_date)
CHECK (status IN ('active', 'archived', 'deleted'))

DEFAULT

Значение по умолчанию, если при вставке оно не указано:

created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
is_active BOOLEAN DEFAULT TRUE

💡 Именованные ограничения — хорошая практика. Если ограничение нарушится, ты увидишь понятное имя в ошибке:

CREATE TABLE employees (
    ...
    salary NUMERIC(10, 2) CONSTRAINT positive_salary CHECK (salary > 0),
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id)
);

4. Изменение структуры: ALTER TABLE

Добавить столбец

ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(100);

Удалить столбец

ALTER TABLE employees
DROP COLUMN middle_name;

⚠️ Осторожно: удаление столбца удаляет и все данные в нём.

Изменить тип столбца

ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC(12, 2);

⚠️ Изменение типа может потребовать USING для преобразования данных:

ALTER TABLE employees
ALTER COLUMN salary TYPE INTEGER USING salary::INTEGER;

Добавить / удалить ограничение

-- Добавить NOT NULL (только если в столбце нет NULL):
ALTER TABLE employees
ALTER COLUMN city SET NOT NULL;

-- Добавить CHECK:
ALTER TABLE employees
ADD CONSTRAINT valid_bonus CHECK (bonus IS NULL OR bonus >= 0);

-- Удалить ограничение:
ALTER TABLE employees
DROP CONSTRAINT valid_bonus;

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

ALTER TABLE employees RENAME TO staff;
ALTER TABLE staff RENAME COLUMN city TO location;

5. Удаление таблиц: DROP и TRUNCATE

DROP TABLE — удалить таблицу целиком

DROP TABLE employees;

⚠️ Если на таблицу ссылаются другие таблицы (через FK), будет ошибка. Варианты:

-- Удалить вместе с зависимыми объектами:
DROP TABLE departments CASCADE;

-- Удалить только если таблица существует:
DROP TABLE IF EXISTS employees;

💡 Разница между DROP и DELETE:

• DROP TABLE — удаляет всю таблицу (структуру + данные)
• DELETE FROM — удаляет строки, оставляя таблицу

TRUNCATE TABLE — очистить данные, оставив структуру

TRUNCATE TABLE employees;

Быстрее, чем DELETE FROM employees (без WHERE), потому что не удаляет строки по одной, а сбрасывает всю таблицу. Но:

• Нельзя использовать с WHERE
• Не вызывает триггеры ON DELETE
• Сбрасывает SERIAL-счётчики

6. Вставка данных: INSERT

Вставка одной строки

INSERT INTO departments (name, budget, location)
VALUES ('IT', 5000000.00, 'Москва');

💡 Мы не указали id — он сгенерируется автоматически через SERIAL.

💡 Мы не указали created_at — подставится CURRENT_TIMESTAMP.

Вставка нескольких строк

INSERT INTO departments (name, budget, location)
VALUES
    ('Продажи', 3000000.00, 'СПб'),
    ('Маркетинг', 2000000.00, 'Казань'),
    ('HR', 1500000.00, 'Москва');

Получить ID вставленной строки: RETURNING

Это фишка PostgreSQL — очень удобная:

INSERT INTO departments (name, budget, location)
VALUES ('Финансы', 2500000.00, 'Москва')
RETURNING id, name;

Результат:

Без RETURNING пришлось бы делать отдельный запрос, чтобы узнать ID.

Вставка с выбором данных из другой таблицы

INSERT INTO departments_archive (name, budget, location)
SELECT name, budget, location
FROM departments
WHERE budget > 2000000;

Что будет при нарушении ограничений

-- Ошибка: salary не может быть NULL
INSERT INTO employees (name, department_id, city, salary)
VALUES ('Тест', 1, 'Москва', NULL);

-- Ошибка: нарушен CHECK (salary > 0)
INSERT INTO employees (name, department_id, city, salary)
VALUES ('Тест', 1, 'Москва', -1000);

-- Ошибка: нарушен FK (нет отдела с id=999)
INSERT INTO employees (name, department_id, city, salary)
VALUES ('Тест', 999, 'Москва', 50000);

7. Обновление данных: UPDATE

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

UPDATE employees
SET salary = 125000
WHERE id = 1;

⚠️ Без WHERE обновятся ВСЕ строки! Это частая и очень болезненная ошибка.

Обновить несколько столбцов

UPDATE employees
SET salary = 135000, bonus = 30000
WHERE name = 'Мария';

Обновление с вычислениями

-- Повысить зарплату на 10% всем в IT:
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT id FROM departments WHERE name = 'IT');

UPDATE с FROM: обновление на основе другой таблицы

UPDATE employees e
SET salary = e.salary * 1.05
FROM departments d
WHERE e.department_id = d.id
  AND d.budget > 3000000;

Это PostgreSQL-специфичный синтаксис. В других СУБД используются подзапросы.

RETURNING в UPDATE

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1
RETURNING id, name, salary;

Вернёт обновлённые строки с новыми значениями.

8. Удаление данных: DELETE

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

DELETE FROM employees
WHERE id = 8;

⚠️ Без WHERE удалятся ВСЕ строки! Для полной очистки лучше использовать TRUNCATE.

Удаление по условию из другой таблицы

DELETE FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE name = 'HR'
);

RETURNING в DELETE

DELETE FROM employees
WHERE is_active = FALSE
RETURNING id, name;

💡 DELETE vs TRUNCATE:

• DELETE — построчное удаление, можно с WHERE, медленнее, вызывает триггеры
• TRUNCATE — сброс всей таблицы, быстрее, нельзя с WHERE, сбрасывает счётчики

9. Транзакции: атомарность операций

Когда нужно выполнить несколько операций как единое целое (либо все, либо ничего), используются транзакции.

BEGIN;

UPDATE employees SET salary = salary * 1.10 WHERE department_id = 1;
UPDATE departments SET budget = budget - 100000 WHERE id = 1;

-- Если всё хорошо:
COMMIT;

-- Если что-то пошло не так:
-- ROLLBACK;

Пример: перевод денег между отделами. Если один UPDATE упадёт, второй тоже откатится — деньги не потеряются.

💡 В PostgreSQL BEGIN можно заменить на START TRANSACTION.

10. Практика: 8 задач на этот этап

Попробуй решить самостоятельно, не подглядывая ниже.

1. Создай таблицу clients с полями: id (автоинкрементный PK), name (обязательное, до 150 символов), email (уникальное, до 255 символов), registered_at (с значением по умолчанию — текущее время).
2. Добавь в таблицу clients столбец phone (до 20 символов).
3. Добавь ограничение CHECK, чтобы email содержал символ @.
4. Вставь в clients три записи за один запрос.
5. Обнови email клиента с id = 1, используя RETURNING.
6. Удали всех клиентов, у которых email заканчивается на @test.com.
7. Создай таблицу orders с внешним ключом на clients, чтобы при удалении клиента удалялись и его заказы.
8. Напиши транзакцию: вставь нового клиента и сразу создай для него заказ.

-- 1
CREATE TABLE clients (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255) UNIQUE,
    registered_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 2
ALTER TABLE clients
ADD COLUMN phone VARCHAR(20);

-- 3
ALTER TABLE clients
ADD CONSTRAINT valid_email CHECK (email LIKE '%@%');

-- 4
INSERT INTO clients (name, email)
VALUES
    ('Ольга', 'olga@example.com'),
    ('Дмитрий', 'dmitry@example.com'),
    ('Светлана', 'svetlana@example.com');

-- 5
UPDATE clients
SET email = 'new_email@example.com'
WHERE id = 1
RETURNING id, name, email;

-- 6
DELETE FROM clients
WHERE email LIKE '%@test.com';

-- 7
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 8
BEGIN;

INSERT INTO clients (name, email)
VALUES ('Николай', 'nikolay@example.com')
RETURNING id;
-- Предположим, вернул id = 4

INSERT INTO orders (client_id, amount)
VALUES (4, 1500.00);

COMMIT;

Чеклист, что этап (и весь курс!) освоен

Ты прошла все шесть этапов, если можешь без подсказки объяснить:

• чем DDL отличается от DML;
• какой тип данных выбрать для денег, для даты с часовым поясом, для длинного текста;
• что делают PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL;
• чем DROP TABLE отличается от DELETE FROM и от TRUNCATE;
• зачем нужен RETURNING (в PostgreSQL);
• что такое транзакция и зачем нужны BEGIN / COMMIT / ROLLBACK.

🎉 Поздравляю, Вика!

Ты прошла весь план обучения SQL. Давай вспомним, что ты теперь умеешь:

Что делать дальше

1. Pet-проект. Придумай свою базу данных (учёт финансов, библиотека, трекер задач) и реализуй её целиком: от создания таблиц до сложных аналитических запросов с оконными функциями.
2. Решай задачи. HackerRank, LeetCode (раздел Database), PGExercises — там задачи уровня собеседований.
3. Углубляйся. Следующие темы для роста:
   • Индексы и оптимизация запросов (EXPLAIN ANALYZE)
   • Триггеры и хранимые процедуры
   • Работа с JSON в PostgreSQL
   • Транзакции и уровни изоляции
   • Проектирование БД (нормализация, ER-диаграммы)