Перейти к содержимому

Хранение данных в реляционных БД (SQL)

Хранение данных в реляционных БД (SQL)

Содержание

Введение в системы управления базами данных

Что такое СУБД?

Система управления базами данных (СУБД или DBMS - Database Management System) — это комплекс программного обеспечения, который управляет базами данных и обеспечивает взаимодействие с ними. База данных хранит данные, а СУБД управляет этими данными и предоставляет инструменты для работы с ними.

Зачем нужны базы данных?

Базы данных нужны для:

  • Структурированного хранения данных
  • Быстрого доступа к данным
  • Обеспечения целостности данных
  • Многопользовательского доступа
  • Надежности и восстановления данных

Альтернативы базам данных:

  • Хранение в файлах (JSON, CSV) - минусы: необходимость загружать все данные в память, проблемы с синхронизацией при одновременном доступе, сложность поиска и фильтрации
  • Самописные файловые системы - минусы: необходимость создания своей системы индексации, сложность обеспечения целостности данных, отсутствие встроенных механизмов резервного копирования

Типы баз данных

Реляционные базы данных (SQL) основаны на концепции отношений (relation) между таблицами. Обеспечивают гарантии целостности данных при правильной настройке.

Популярные реляционные СУБД:

  • PostgreSQL
  • MySQL/MariaDB
  • SQLite
  • Microsoft SQL Server
  • Oracle

Нереляционные базы данных (NoSQL) имеют различные подходы к хранению данных:

  • Документ-ориентированные (MongoDB) — хранят JSON-документы
  • Key-value хранилища (Redis) — эффективные словари с быстрым доступом
  • Графовые базы (Neo4j) — оптимизированы для хранения связей и графов
  • Колоночные базы — оптимизированы для аналитических запросов

Почему именно PostgreSQL?

  • Поддержка большого количества типов данных
  • Расширяемость
  • Поддержка сложных запросов
  • Обеспечение целостности данных
  • Открытый исходный код
  • Активное сообщество разработчиков
  • Стабильность и надежность
↑ К оглавлению

Установка и настройка PostgreSQL

Разворачивание PostgreSQL через Docker

Docker позволяет легко и быстро развернуть PostgreSQL без сложной настройки системы.

 Пример файла docker-compose.yml:

version: '3'
services:
  db:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: example
      POSTGRES_DB: blog
    volumes:
      - postgres_data:/var/lib/postgresql/data

  adminer:
    image: adminer
    ports:
      - "8080:8080"

  pgadmin:
    image: dpage/pgadmin4
    ports:
      - "5050:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.
      PGADMIN_DEFAULT_PASSWORD: admin

volumes:
  postgres_data:
YAML

Команды для запуска:

docker-compose up -d  # Запуск в фоновом режиме
docker-compose ps     # Проверка запущенных сервисов
Bash

Инструменты для работы с PostgreSQL

Существует несколько инструментов для работы с PostgreSQL:

  • PyCharm Professional — встроенная поддержка баз данных
  • pgAdmin — веб-интерфейс для управления PostgreSQL
  • Adminer — легковесный веб-интерфейс для работы с базами данных
  • DBeaver — бесплатный универсальный клиент для работы с базами данных
  • Database Navigator — плагин для PyCharm Community Edition
↑ К оглавлению

Основы языка SQL

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

SQL (Structured Query Language) — язык структурированных запросов, предназначенный для управления данными в реляционных базах данных.

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE
);
SQL

Вставка данных

INSERT INTO authors (username) VALUES ('admin');
INSERT INTO authors (username, email) VALUES
    ('kyle', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.'),
    ('bob', Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.');
SQL

Выборка данных

-- Выбрать все поля из таблицы
SELECT * FROM authors;

-- Выбрать конкретные поля
SELECT id, username FROM authors;

-- Выбрать с фильтрацией
SELECT username, email FROM authors
WHERE LENGTH(username) = 4 AND email IS NULL;

-- Сортировка результатов
SELECT * FROM authors ORDER BY id DESC;
SQL

Обновление и удаление данных

-- Обновление данных
UPDATE authors
SET email = CONCAT(username, Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.')
WHERE LENGTH(username) = 4 AND email IS NULL;

-- Удаление данных
DELETE FROM authors WHERE username = 'admin';
SQL
↑ К оглавлению

Отношения между таблицами

Внешние ключи и целостность данных

Внешние ключи (foreign keys) обеспечивают ссылочную целостность между таблицами:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    body TEXT DEFAULT '',
    author_id INTEGER NOT NULL,
    CONSTRAINT fk_author
        FOREIGN KEY (author_id)
        REFERENCES authors(id)
);
SQL

Это ограничение гарантирует, что в поле author_id можно вставить только те значения, которые существуют в таблице authors в колонке id.

JOIN операторы

JOIN операторы используются для объединения данных из разных таблиц.

INNER JOIN

Возвращает только те записи, для которых есть совпадения в обеих таблицах:

SELECT posts.*, authors.username
FROM posts
INNER JOIN authors ON posts.author_id = authors.id;
SQL

LEFT OUTER JOIN

Возвращает все записи из левой таблицы и совпадающие записи из правой. Если совпадений нет, поля из правой таблицы будут содержать NULL:

SELECT authors.username, posts.id, posts.title
FROM authors
LEFT OUTER JOIN posts ON authors.id = posts.author_id;
SQL
↑ К оглавлению

Продвинутые техники работы с данными

Пагинация данных

Использование LIMIT и OFFSET

-- Первые 2 записи
SELECT * FROM authors ORDER BY id LIMIT 2;

-- Следующие 2 записи
SELECT * FROM authors ORDER BY id LIMIT 2 OFFSET 2;
SQL

Эффективная пагинация через WHERE

-- После записи с id=3
SELECT * FROM authors WHERE id > 3 ORDER BY id LIMIT 2;
SQL

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

-- Авторы с email на домене yaru.ru
SELECT * FROM authors WHERE email LIKE '%@yaru.ru';

-- Авторы с email не на домене yaru.ru или без email
SELECT * FROM authors WHERE email NOT LIKE '%@yaru.ru' OR email IS NULL;
SQL

Ограничения целостности данных

Внешние ключи обеспечивают целостность данных, например, невозможно удалить автора, если у него есть посты:

-- Эта команда завершится ошибкой, если у автора есть посты
DELETE FROM authors WHERE id = 3;
SQL

Для каскадного удаления можно использовать:

-- В определении таблицы
CONSTRAINT fk_author
    FOREIGN KEY (author_id)
    REFERENCES authors(id)
    ON DELETE CASCADE;
SQL
↑ К оглавлению

Заключение

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

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

Дальнейшее изучение темы включает работу с индексами, оптимизацию запросов, транзакции, хранимые процедуры и использование ORM (Object-Relational Mapping) в приложениях.

Конспект:
Четверг, 22 января 2026
Хранение данных в реляционных БД (SQL)