1. DDL: Create, Drop, Alter — Создание и изменение структуры
Create: Создание таблиц, индексов, представлений.
# SQLAlchemy
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine("sqlite:///db.sqlite")
metadata = MetaData()
users = Table(
"users", metadata,
Column("id", Integer, primary_key=True),
Column("name", String)
)
metadata.create_all(engine)
Drop: Удаление объектов.
DROP TABLE users;
Alter: Изменение структуры (добавить/удалить столбец).
# Alembic (миграция)
def upgrade():
op.add_column("users", Column("age", Integer))
2. DML: Insert, Update, Delete — Работа с данными
Insert:
# SQLAlchemy
insert_query = users.insert().values(name="Алиса", age=25)
engine.execute(insert_query)
# Django ORM
User.objects.create(name="Алиса", age=25)
Update:
# SQLAlchemy
update_query = users.update().where(users.c.id == 1).values(age=26)
engine.execute(update_query)
Delete:
DELETE FROM users WHERE id = 1;
3. Запросы: Выборки, JOIN, агрегации
Простая выборка:
# SQLAlchemy
result = engine.execute(select(users).where(users.c.age > 20))
JOIN:
# Объединение таблиц
query = select(users, addresses).join(addresses, users.c.id == addresses.c.user_id)
Агрегации (GROUP BY, Window Functions):
--sql
SELECT department, AVG(salary) OVER (PARTITION BY department)
FROM employees;
UNION:
--sql
SELECT name FROM users
UNION
SELECT name FROM admins;
4. Нормализация и денормализация
Нормализация:
- 1NF: Уникальные строки, атомарные значения.
- 2NF: Нет частичных зависимостей от первичного ключа.
- 3NF: Нет транзитивных зависимостей.
Денормализация: Намеренное дублирование данных для ускорения чтения (например, хранение суммы заказа в отдельном столбце).
5. Транзакции и изоляция
ACID:
- Atomicity (Атомарность): Все операции транзакции выполняются или отменяются.
- Isolation (Изоляция): Уровни:
- Read Uncommitted — Видны «грязные» данные.
- Read Committed — Только подтвержденные данные.
- Repeatable Read — Консистентность в рамках транзакции.
- Serializable — Полная изоляция.
CAP-теорема: Система может гарантировать только 2 из 3 свойств:
- Consistency (Консистентность),
- Availability (Доступность),
- Partition Tolerance (Устойчивость к разделению).
6. Индексы, процедуры, курсоры
Индексы: Ускоряют поиск.
# Django
class User(models.Model):
name = models.CharField(db_index=True)
Хранимые процедуры:
--sql
CREATE FUNCTION get_user_count() RETURNS INT AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;
Курсоры: Постраничная выборка.
# SQLAlchemy
result = session.execute(query).fetchmany(100)
7. Оптимизация: Explain Plan, партиционирование
Explain Plan: Анализ выполнения запроса.
# PostgreSQL
query = "EXPLAIN ANALYZE SELECT * FROM users WHERE age > 20;"
result = engine.execute(query)
print(result.fetchall())
Партиционирование: Разделение таблиц на части (например, по дате).
- Горизонтальное: По диапазону значений.
- Вертикальное: По столбцам.
8. Расширенные операции
Merge/Upsert: Обновление или вставка.
-- PostgreSQL
INSERT INTO users (id, name)
VALUES (1, 'Алиса')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
Триггеры: Автоматические действия при событиях.
--sql
CREATE TRIGGER log_user_changes
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION log_change();
Итоги и рекомендации
- Нормализуйте базу на старте проекта, денормализуйте для оптимизации.
- Используйте индексы для часто фильтруемых полей.
- Транзакции — ваши друзья для консистентности.
- Explain Plan поможет найти «узкие» места в запросах.
Пример оптимизации через партиционирование:
# SQLAlchemy (создание партиционированной таблицы)
from sqlalchemy import PartitionedTable
orders = PartitionedTable(
"orders", metadata,
Column("id", Integer),
Column("order_date", Date),
postgresql_partition_by="RANGE (order_date)"
)
Базы данных — это мощный инструмент. Главное — правильно выбрать подход под ваши задачи!