1. DDL: Create, Drop, Alter — Создание и изменение структуры

Базы данных от А до Я: Основные операции, оптимизация и тонкости работы
(С примерами на Python и SQL)

---

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):

SELECT department, AVG(salary) OVER (PARTITION BY department)  
FROM employees;  

UNION:

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)  

Хранимые процедуры:

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;  

Триггеры: Автоматические действия при событиях.

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)"  
)  

Базы данных — это мощный инструмент. Главное — правильно выбрать подход под ваши задачи!