Базы данных: PostgreSQL и SQL Server с использованием DBeaver

Введение в реляционные базы данных

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

От электронных таблиц к базам данных

Когда мы говорим о хранении данных в виде таблиц, первой ассоциацией часто становится Microsoft Excel или Google Таблицы. Для личного использования или небольшого бизнеса это отличные инструменты. Однако, когда речь заходит о разработке программного обеспечения, обычные файлы перестают справляться.

Представьте, что вы создаете интернет-магазин. В первый месяц у вас 100 клиентов и 50 товаров. Вы можете вести учет в Excel. Но через год у вас 100 000 клиентов, каждый из которых делает по несколько заказов в месяц.

Обычные электронные таблицы имеют физические ограничения (например, в Excel максимум 1 048 576 строк). Кроме того, если два менеджера одновременно попытаются изменить один и тот же файл, возникнет конфликт версий. А если приложение должно искать конкретный заказ среди миллионов записей, чтение файла от начала до конца займет недопустимо много времени.

Здесь на сцену выходят базы данных (БД) — структурированные массивы информации, организованные таким образом, чтобы компьютер мог максимально быстро находить и обрабатывать нужные фрагменты.

Сама по себе база данных — это просто набор файлов на жестком диске сервера. Чтобы с ними работать, нужна система управления базами данных (СУБД). Это сложная программа, которая берет на себя всю черновую работу: она записывает данные на диск, распределяет оперативную память, управляет одновременным доступом тысяч пользователей и защищает информацию от сбоев.

В рамках нашего курса мы будем работать с двумя мощнейшими промышленными СУБД: PostgreSQL (открытая и бесплатная система, стандарт де-факто в современной веб-разработке) и SQL Server (коммерческая система от Microsoft, широко используемая в корпоративном секторе).

Анатомия реляционной модели

Существуют разные подходы к хранению данных, но самым популярным и надежным остается реляционный подход. Термин реляционная база данных происходит от английского слова relation (отношение). В математике и теории баз данных под «отношением» понимается то, что мы в быту привыкли называть таблицей.

В реляционной БД вся информация строго структурирована и хранится в двумерных таблицах. Каждая таблица описывает только одну сущность реального мира.

Структура таблицы состоит из двух главных элементов: * Столбцы (атрибуты) — задают структуру данных. Например, в таблице клиентов могут быть столбцы «Имя», «Email» и «Дата регистрации». Для каждого столбца жестко задается тип данных: нельзя записать текст в столбец, предназначенный для чисел. * Строки (записи) — содержат сами данные. Одна строка — это один конкретный экземпляр сущности (один клиент, один товар, один заказ).

Проблема дублирования данных

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

Допустим, мы записываем все продажи в одну таблицу. В ней есть столбцы: Номер заказа, Имя клиента, Адрес клиента, Название товара, Цена товара.

Если клиент Иван Иванов с адресом «г. Москва, ул. Пушкина, д. 10» сделает 50 разных заказов за год, его имя и длинный адрес будут скопированы в таблице 50 раз.

Это порождает три критические проблемы:

Перерасход памяти. Текст занимает место на диске.

Риск опечаток. В одном заказе менеджер напишет «Иван Иванов», в другом — «Иванов И.», и система будет считать их разными людьми.

Сложность обновления. Если Иван переедет, нам придется найти и обновить все 50 строк с его старыми заказами. Если мы пропустим хотя бы одну, данные станут противоречивыми.

Реляционная модель решает эту проблему элегантно: мы разбиваем данные на несколько независимых таблиц и связываем их между собой.

Ключи и связи: как таблицы общаются друг с другом

Вместо одной большой таблицы мы создадим три маленькие: «Клиенты», «Товары» и «Заказы». Но как системе понять, какой клиент какой товар купил? Для этого используются ключи.

Первичный ключ (Primary Key, PK) — это уникальный идентификатор строки в таблице. Чаще всего это просто порядковый номер (ID), который база данных генерирует автоматически. У каждого клиента есть свой уникальный ID, который никогда не меняется и не повторяется.

Внешний ключ (Foreign Key, FK) — это столбец в одной таблице, который ссылается на первичный ключ в другой таблице.

Посмотрим на конкретный пример с числами.

Таблица «Клиенты»: | ID клиента (PK) | Имя | Адрес | | :--- | :--- | :--- | | 1 | Иван Иванов | Москва, ул. Пушкина | | 2 | Анна Смирнова | Казань, ул. Баумана |

Таблица «Товары»: | ID товара (PK) | Название | Цена (руб.) | | :--- | :--- | :--- | | 101 | Ноутбук | 80000 | | 102 | Мышь | 1500 |

Таблица «Заказы»: | ID заказа (PK) | ID клиента (FK) | ID товара (FK) | Количество | | :--- | :--- | :--- | :--- | | 5001 | 1 | 102 | 2 | | 5002 | 2 | 101 | 1 |

В таблице «Заказы» мы больше не пишем имена и адреса. Мы используем только числа (ID). Заказ №5001 говорит нам: клиент с ID=1 (Иван) купил товар с ID=102 (Мышь) в количестве 2 штук.

Если Иван переедет, мы изменим его адрес ровно в одном месте — в строке №1 таблицы «Клиенты». Все его прошлые и будущие заказы автоматически будут связаны с новым адресом, так как они ссылаются на его ID.

Общее количество связей в базе может быть огромным. Если у нас 10 000 пользователей и каждый делает в среднем по 5 заказов, таблица заказов будет содержать записей, но при этом ни один байт личной информации не будет продублирован.

!Схема реляционной базы данных интернет-магазина

Иерархия объектов в базе данных

Чтобы не запутаться в тысячах таблиц, современные СУБД используют строгую иерархию хранения. Представьте себе матрешку:

Сервер (Экземпляр) — самый большой уровень. Это физический или виртуальный компьютер, на котором запущена программа СУБД (например, процесс PostgreSQL).

База данных — изолированное хранилище внутри сервера. На одном сервере может быть база ecommerce_app для магазина и база hr_system для отдела кадров. Они полностью независимы.

Схема (Schema) — логическая папка внутри базы данных. В базе магазина может быть схема inventory для таблиц склада и схема sales для таблиц продаж. По умолчанию в PostgreSQL все таблицы создаются в стандартной схеме public.

Таблица — конкретный набор строк и столбцов внутри схемы.

Понимание этой структуры критически важно, так как при написании запросов вам часто придется указывать точный путь к данным.

Язык SQL: как разговаривать с базой

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

SQL — это стандартный язык для всех реляционных баз данных. Выучив его основы, вы сможете работать и с PostgreSQL, и с SQL Server, и с MySQL.

Все операции в базах данных сводятся к четырем базовым действиям, которые программисты называют аббревиатурой CRUD: * Create (Создание) — добавление новых строк (оператор INSERT). * Read (Чтение) — поиск и извлечение данных (оператор SELECT). * Update (Обновление) — изменение существующих данных (оператор UPDATE). * Delete (Удаление) — стирание данных (оператор DELETE).

Например, чтобы найти всех клиентов из Москвы, мы напишем запрос, который читается почти как обычное английское предложение:

DBeaver: ваш пульт управления

Исторически администраторы работали с базами данных через черное окно терминала, вводя команды текстом. Это мощный, но не самый наглядный способ.

В нашем курсе мы будем использовать DBeaver — универсальный графический интерфейс (GUI) для работы с базами данных.

DBeaver подключается к вашему серверу PostgreSQL или SQL Server и отображает всю сложную иерархию (базы, схемы, таблицы) в виде удобного дерева папок слева на экране.

С помощью DBeaver вы сможете: * Просматривать содержимое таблиц в виде сетки, похожей на Excel. * Писать SQL-запросы в удобном редакторе с подсветкой синтаксиса и автодополнением (программа сама подскажет названия таблиц). * Автоматически строить визуальные схемы связей между таблицами (ER-диаграммы). * Импортировать данные из обычных CSV-файлов прямо в таблицы базы данных.

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

Настройка DBeaver и подключение к СУБД

В предыдущем материале мы выяснили, что реляционная база данных — это строго структурированное хранилище информации, а управляет им специальная программа — система управления базами данных (СУБД). Однако сама по себе СУБД, будь то PostgreSQL или SQL Server, работает в фоновом режиме. У нее нет привычного графического интерфейса с кнопками и красивыми таблицами. Это мощный, но «невидимый» двигатель, который просто ждет команд.

Чтобы человек мог комфортно взаимодействовать с этим двигателем, отправлять ему запросы и визуально оценивать результат, нужны специальные программы-клиенты. В нашем курсе роль такого универсального пульта управления выполняет DBeaver.

Клиент-серверная архитектура баз данных

Прежде чем нажимать кнопки в интерфейсе, необходимо понять, как именно ваш компьютер общается с базой данных. Все современные промышленные СУБД работают по клиент-серверной архитектуре.

Сервер базы данных — это программа, которая постоянно запущена на компьютере (это может быть ваш личный ноутбук или мощный облачный сервер где-то в дата-центре). Она слушает сетевые подключения и ждет указаний.

Клиент — это программа, которую запускает пользователь. Клиент формирует текстовый запрос на языке SQL, отправляет его по сети на сервер, дожидается ответа и отрисовывает полученные данные на экране. DBeaver — это классический «толстый» клиент (полноценное десктопное приложение).

!Схема взаимодействия клиента DBeaver и сервера базы данных

Главное преимущество такого подхода заключается в том, что к одному серверу базы данных могут одновременно подключаться тысячи разных клиентов: DBeaver на вашем компьютере, веб-сервер интернет-магазина, мобильное приложение курьера. Сервер обрабатывает их все параллельно.

Анатомия подключения: 5 главных параметров

Чтобы DBeaver смог найти сервер и получить доступ к данным, ему нужно передать точные координаты. Независимо от того, используете вы PostgreSQL, SQL Server, MySQL или Oracle, вам всегда потребуются пять базовых параметров подключения.

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

Хост (Host) — это адрес «бизнес-центра». Сетевой адрес компьютера, на котором запущена СУБД. Если база данных установлена на вашем же компьютере, используется специальный локальный адрес localhost (или IP-адрес 127.0.0.1). Если база находится в интернете, это будет доменное имя (например, db.example.com) или внешний IP-адрес.

Порт (Port) — это «номер кабинета». На одном сервере может работать множество разных программ (веб-сервер, почтовый сервер, несколько разных СУБД). Порт — это числовой идентификатор, который указывает сетевому трафику, к какой именно программе он должен попасть.

Имя базы данных (Database) — это «название отдела». Как мы помним из иерархии объектов, на одном сервере могут находиться десятки изолированных баз данных. При подключении нужно указать, с какой именно вы планируете работать.

Пользователь (Username) — ваше имя в системе (имя на «пропуске»).

Пароль (Password) — секретный ключ, подтверждающий, что вы действительно тот, за кого себя выдаете.

Стандартные параметры по умолчанию

При установке СУБД на локальный компьютер системы автоматически задают стандартные значения для портов и главных администраторов. Их полезно знать наизусть.

| Параметр | PostgreSQL | SQL Server | | :--- | :--- | :--- | | Стандартный порт | 5432 | 1433 | | Суперпользователь | postgres | sa (System Administrator) | | База по умолчанию | postgres | master |

> Важное отличие: SQL Server поддерживает два режима аутентификации. Первый — классический (по логину и паролю СУБД). Второй — Windows Authentication, когда SQL Server доверяет вашей учетной записи Windows и не требует вводить отдельный пароль. PostgreSQL в базовой настройке всегда требует логин и пароль.

Что такое драйвер базы данных

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

Зачем он нужен? DBeaver — это универсальная программа, написанная на языке Java. Она умеет рисовать красивые таблицы и кнопки, но изначально не знает специфического внутреннего языка, на котором общается PostgreSQL или SQL Server.

Драйвер — это программа-переводчик. Он берет стандартные команды от DBeaver и переводит их в бинарный сетевой протокол, понятный конкретной СУБД. Для каждой базы данных нужен свой драйвер. К счастью, DBeaver умеет скачивать их автоматически из интернета — вам нужно лишь нажать кнопку подтверждения.

Пошаговое подключение в DBeaver

Рассмотрим процесс создания нового подключения на практике. Логика действий одинакова для любой СУБД.

Нажмите кнопку с иконкой розетки (или выберите в меню База данных → Новое соединение).

В появившемся списке выберите нужную СУБД (PostgreSQL или Microsoft SQL Server) и нажмите «Далее».

Заполните вкладку «Главное» (Main). Введите localhost в поле Хост, убедитесь, что указан правильный порт. Введите имя базы данных, пользователя и пароль.

Нажмите кнопку «Тест соединения» (Test Connection). Это критически важный шаг. DBeaver попытается связаться с сервером без сохранения настроек.

Если вы видите зеленое сообщение «Connected» (Подключено) с указанием версии сервера — всё отлично, можно нажимать «Готово».

Чтение ошибок подключения

Если тест провалился, DBeaver покажет ошибку. Умение читать эти ошибки — важный навык разработчика. Вот две самые частые ситуации:

Connection refused* (В соединении отказано). Это означает, что DBeaver нашел компьютер (хост), но по указанному порту никто не ответил. Причины: СУБД не запущена, вы ошиблись цифрой в порте, или доступ блокирует брандмауэр (firewall). FATAL: password authentication failed for user* (Сбой аутентификации). Сервер найден, СУБД работает и ответила вам, но она не пускает вас внутрь. Причина только одна: вы ввели неправильный логин или пароль.

Обзор рабочего пространства DBeaver

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

Навигатор баз данных (Database Navigator) — панель слева. Это древовидная структура вашего сервера. Раскрыв подключение, вы увидите список баз данных. Раскрыв конкретную базу — список схем (например, public в PostgreSQL или dbo в SQL Server). Внутри схем лежат папки «Таблицы», «Представления», «Индексы». Это визуальное отображение иерархии, которую мы обсуждали ранее.

Редактор SQL (SQL Editor) — главное рабочее поле. Чтобы его открыть, кликните правой кнопкой мыши по вашему подключению и выберите Редактор SQL → Новый редактор SQL (или нажмите F3). Здесь мы будем писать код. Редактор поддерживает подсветку синтаксиса и автодополнение: если вы начнете печатать название таблицы, DBeaver предложит варианты.

Давайте напишем наш первый, самый простой запрос, чтобы убедиться, что всё работает:

Чтобы выполнить этот код, установите курсор на строку с запросом и нажмите Ctrl + Enter (или иконку оранжевого треугольника на панели слева от редактора).

Сетка результатов (Result Grid) — панель, которая появится внизу после выполнения запроса. В ней вы увидите таблицу с одной колонкой result и значением 300. Сетка результатов в DBeaver интерактивна: когда мы начнем извлекать реальные данные из таблиц, вы сможете сортировать их, фильтровать и даже редактировать прямо в ячейках, как в Excel, после чего сохранять изменения в базу одной кнопкой.

Настроив подключение и освоив базовую навигацию в DBeaver, мы полностью подготовили рабочее место. Инфраструктура готова. На следующем этапе мы перейдем к проектированию структуры данных и созданию наших первых таблиц с помощью языка SQL.

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

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

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

Три этапа проектирования

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

Анализ требований. На этом этапе вы общаетесь с заказчиком или анализируете бизнес-логику будущего приложения. Ваша цель — понять, какие сущности существуют в реальном мире и какие их свойства нужно сохранить. Например, для университета это будут студенты, преподаватели, кафедры и учебные курсы.

Логическое проектирование. Здесь предметная область переносится на бумагу или в специализированную программу. Вы определяете таблицы, их столбцы (атрибуты) и связи между ними. На этом этапе мы не думаем о том, какую именно СУБД будем использовать. Логическая модель универсальна.

Физическое проектирование. Это адаптация логической модели под конкретную СУБД (в нашем случае — PostgreSQL или SQL Server). На этом этапе абстрактные «текст» и «число» превращаются в строгие типы данных, назначаются индексы для ускорения поиска и пишутся SQL-скрипты для создания таблиц.

!Логическая схема базы данных университета, показывающая связи между сущностями

Нормализация: искусство избавляться от дубликатов

Главный враг любой реляционной базы данных — избыточность (дублирование информации). Представьте, что мы решили хранить данные о студентах и их кафедрах в одной большой таблице (как на листе Excel):

| ID Студента | Имя | Кафедра | Бюджет кафедры | Заведующий | | :--- | :--- | :--- | :--- | :--- | | 1 | Иван | Программирование | 5 000 000 | Смирнов А.А. | | 2 | Анна | Программирование | 5 000 000 | Смирнов А.А. | | 3 | Олег | Дизайн | 3 000 000 | Попова В.И. |

В этой структуре кроется серьезная проблема, которую называют аномалией обновления. Если бюджет кафедры программирования изменится, нам придется найти и обновить тысячи строк со студентами этой кафедры. Если мы пропустим хотя бы одну строку, данные станут противоречивыми. А если с кафедры отчислят всех студентов, мы потеряем информацию о самой кафедре (ее бюджете и заведующем), так как удалятся все строки.

Чтобы избежать этого, применяется нормализация — процесс разделения больших таблиц на несколько меньших с установлением связей между ними.

Первое правило (Первая нормальная форма — 1NF)

Каждая ячейка таблицы должна содержать только одно атомарное (неделимое) значение. Если у студента два номера телефона, нельзя записывать их в одну ячейку через запятую: +79991234567, +79997654321. Для телефонов нужно создать отдельную связанную таблицу или, как минимум, выделить каждому номеру отдельную строку.

Второе и третье правила (2NF и 3NF)

Если говорить простыми словами, эти правила гласят: каждый столбец в таблице должен зависеть только от первичного ключа этой таблицы и ни от чего больше.

В нашем примере «Бюджет кафедры» зависит не от студента, а от самой кафедры. Значит, мы должны разделить данные на две таблицы:

Таблица Departments (Кафедры): * department_id (Primary Key) * name (Название) * budget (Бюджет) * head_name (Заведующий)

Таблица Students (Студенты): * student_id (Primary Key) * name (Имя) * department_id (Foreign Key — ссылка на кафедру)

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

Физическое проектирование: PostgreSQL против SQL Server

Когда логическая структура (таблицы и связи) готова, мы переходим к физическому проектированию. Здесь начинают играть роль архитектурные различия между СУБД. Главная задача на этом этапе — правильно подобрать типы данных.

Если вы выберете слишком большой тип данных для маленьких чисел, база будет занимать лишнее место на диске и медленнее работать в оперативной памяти.

Рассмотрим ключевые отличия в типах данных между изучаемыми нами системами:

| Тип данных | PostgreSQL | SQL Server | Описание и применение | | :--- | :--- | :--- | :--- | | Целые числа | INTEGER | INT | Стандартное целое число. Максимальное значение ограничено формулой , что составляет чуть более 2 миллиардов. Подходит для большинства ID. | | Автоинкремент | SERIAL (или GENERATED ALWAYS AS IDENTITY) | IDENTITY(1,1) | Автоматический счетчик для первичных ключей. При добавлении новой строки СУБД сама вставит число на 1 больше предыдущего. | | Строки (текст) | VARCHAR(n) или TEXT | VARCHAR(n) или NVARCHAR(n) | В SQL Server для хранения символов Unicode (например, кириллицы или эмодзи) обязательно использовать NVARCHAR. PostgreSQL хранит Unicode в обычном VARCHAR по умолчанию. | | Логический тип | BOOLEAN | BIT | Хранит значения Истина/Ложь. В SQL Server используется BIT, который принимает значения 1 или 0. |

Реализация проекта в DBeaver

Теория нормализации и типов данных обретает смысл, когда мы начинаем писать SQL-код. DBeaver предоставляет отличные инструменты как для написания скриптов, так и для визуального контроля созданной архитектуры.

Давайте напишем скрипт для создания наших нормализованных таблиц. Откройте SQL-редактор в DBeaver (как мы учились в предыдущей статье) и введите следующий код.

Пример синтаксиса для PostgreSQL:

Обратите внимание на последовательность: мы не можем создать таблицу студентов раньше таблицы кафедр, потому что внешний ключ (FOREIGN KEY) должен ссылаться на уже существующий объект. Это строгое правило реляционных баз данных.

После успешного выполнения этого скрипта (нажатием Ctrl + Enter), обновите навигатор баз данных слева. Вы увидите созданные таблицы.

Визуализация связей (ER-диаграммы)

Одной из самых мощных функций DBeaver является автоматическое построение ER-диаграмм (Entity-Relationship, «Сущность-Связь»). Вам не нужно рисовать схемы вручную в сторонних программах.

Чтобы увидеть результат вашей работы визуально:

В навигаторе баз данных слева найдите вашу схему (обычно это public в PostgreSQL или dbo в SQL Server).

Дважды кликните по названию схемы.

В открывшемся центральном окне перейдите на вкладку «ER-диаграмма» (ER Diagram).

DBeaver автоматически проанализирует внешние ключи и нарисует прямоугольники таблиц, соединив их линиями. Линия от students к departments будет иметь специальный значок на конце (обычно в виде «куриной лапки»), который означает связь «многие к одному» — на одной кафедре учится много студентов.

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

В предыдущей статье мы разработали логическую и физическую архитектуру базы данных: определили сущности, связи между ними и подобрали подходящие типы данных. Теперь пришло время перенести эти чертежи в реальную среду. Процесс создания и изменения структур в базе данных сродни строительству здания: сначала закладывается фундамент (таблицы), затем возводятся стены с жесткими правилами (ограничения), а при необходимости делается перепланировка (модификация).

Для управления структурой базы данных используется специальное подмножество языка SQL, которое называется DDL (Data Definition Language — язык определения данных). В отличие от запросов на получение информации, команды DDL напрямую меняют архитектуру хранилища.

Анатомия команды CREATE TABLE

Создание таблицы начинается с команды CREATE TABLE. Это базовая инструкция, которая сообщает СУБД имя новой таблицы, названия ее столбцов и типы данных, которые в них будут храниться.

Представьте, что вы создаете жестко структурированную таблицу Excel, где в колонку «Дата рождения» физически невозможно вписать слово «Вчера», а колонка «ID» автоматически выдает новый номер каждой новой строке.

Рассмотрим синтаксис на примере создания таблицы учебных курсов.

Пример для PostgreSQL:

Пример для SQL Server:

Разберем ключевые отличия и общие элементы этих скриптов.

Во-первых, обратите внимание на генерацию уникальных идентификаторов. В PostgreSQL используется псевдотип SERIAL, который автоматически создает последовательность чисел. В SQL Server применяется свойство IDENTITY(1,1), которое означает: «начни с единицы и увеличивай на единицу для каждой новой записи».

Во-вторых, для хранения текста в SQL Server мы использовали NVARCHAR вместо VARCHAR. Приставка N означает поддержку Unicode, что критически важно для корректного сохранения кириллицы или спецсимволов в продуктах Microsoft.

Ограничения (Constraints): стражи целостности данных

В примерах выше вы могли заметить слова NOT NULL, DEFAULT и CHECK. Это ограничения — строгие правила, которые СУБД применяет к данным на этапе их добавления. Если попытаться вставить строку, нарушающую хотя бы одно правило, база данных выдаст ошибку и отменит операцию.

Ограничения — это ваш главный инструмент защиты от «грязных» данных. Разработчики приложений могут ошибиться в коде и отправить некорректную информацию, но база данных с правильными ограничениями ее не пропустит.

| Ограничение | Описание | Пример из жизни | | :--- | :--- | :--- | | PRIMARY KEY | Первичный ключ. Гарантирует уникальность строки и запрещает пустые значения. | Номер паспорта или ИНН гражданина. | | NOT NULL | Запрещает оставлять ячейку пустой. | Поле «Имя» при регистрации на сайте. | | UNIQUE | Гарантирует, что все значения в столбце уникальны, но, в отличие от первичного ключа, допускает пустые значения. | Адрес электронной почты пользователя. | | DEFAULT | Устанавливает значение по умолчанию, если оно не было передано при вставке. | Статус «Активен» для нового аккаунта. | | CHECK | Проверяет данные по заданному логическому или математическому условию. | Возраст пользователя должен удовлетворять условию . |

> База данных — это последний рубеж обороны ваших данных. Никогда не полагайтесь только на проверки на стороне клиентского приложения. Правила должны быть зашиты в самой структуре БД. > > Medium

!Схема создания таблицы базы данных: столбцы, типы данных и ограничения

Модификация таблиц: команда ALTER TABLE

Бизнес-требования редко остаются неизменными. Сегодня приложению нужны только имена студентов, а завтра маркетологи попросят добавить дату рождения и номер телефона. Для изменения структуры уже существующей таблицы используется команда ALTER TABLE.

Добавление нового столбца

Если нам нужно добавить столбец с описанием курса, синтаксис будет одинаковым для обеих СУБД:

Изменение типа данных столбца

Предположим, мы поняли, что 100 символов для названия курса (title) недостаточно, и хотим увеличить лимит до 200. Здесь синтаксис начинает различаться.

В PostgreSQL:

В SQL Server:

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

Если колонка больше не нужна, от нее можно избавиться. Эта операция необратима — вместе со столбцом удалятся и все данные, которые в нем хранились.

Удаление и очистка таблиц: DROP против TRUNCATE

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

Команда DROP TABLE courses; полностью уничтожает таблицу. Удаляется сама структура, все данные, индексы и ограничения. Это эквивалентно сносу здания до основания.

Команда TRUNCATE TABLE courses; работает иначе. Она мгновенно удаляет все строки внутри таблицы, но сама структура (колонки, типы данных) остается нетронутой. Это похоже на выселение всех жильцов из здания — дом пуст, но готов к заселению новых. TRUNCATE работает значительно быстрее, чем удаление строк по одной, так как не записывает каждое удаление в журнал транзакций.

Работа со структурой через интерфейс DBeaver

Хотя знание SQL-команд DDL абсолютно необходимо для профессионального разработчика, DBeaver предоставляет мощный графический интерфейс (GUI), который делает процесс проектирования более наглядным и защищает от синтаксических опечяток.

Визуальное создание таблицы

Чтобы создать таблицу без написания кода, выполните следующие шаги:

В панели Навигатор баз данных (слева) раскройте ваше подключение, затем базу данных и схему (например, public в PostgreSQL или dbo в SQL Server).

Кликните правой кнопкой мыши по папке Таблицы (Tables) и выберите Создать объект «Таблица» (Create New Table).

Откроется окно редактора свойств. В верхнем поле введите имя таблицы.

В центральной части окна, на вкладке «Столбцы» (Columns), кликните правой кнопкой мыши и выберите Создать новый столбец (Create New Column).

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

Магия кнопки «Сохранить»

Главная особенность DBeaver заключается в том, что все ваши визуальные действия не применяются к базе данных мгновенно. Они сохраняются в памяти программы как проект.

Когда вы закончили настраивать столбцы, нажмите кнопку Сохранить (Save) внизу окна (или Ctrl + S). DBeaver не просто молча создаст таблицу. Он откроет окно Предварительный просмотр SQL (Persist Changes).

В этом окне программа покажет вам сгенерированный SQL-код (CREATE TABLE ...), который она собирается отправить на сервер. Это невероятно полезный инструмент для обучения: вы можете визуально «накликать» сложную структуру с внешними ключами и ограничениями, а затем посмотреть, как выглядит идеальный SQL-код для этой задачи. Если код вас устраивает, нажмите «Выполнить» (Persist).

Импорт данных из CSV

DBeaver также упрощает первичное наполнение таблиц. Если у вас есть данные в формате Excel или CSV, вам не нужно писать сотни команд INSERT.

Достаточно кликнуть правой кнопкой мыши по созданной таблице, выбрать Импорт данных (Import Data) и указать путь к вашему CSV-файлу. Мастер импорта позволит сопоставить столбцы из файла со столбцами в таблице и автоматически загрузит тысячи строк за пару секунд.

Умение создавать и модифицировать таблицы — это базовый навык инженерии данных. Комбинируя понимание SQL-синтаксиса с визуальными инструментами DBeaver, вы сможете быстро разворачивать архитектуру любой сложности, готовя надежный фундамент для будущих аналитических запросов.

В прошлой статье мы заложили фундамент нашей базы данных: спроектировали таблицы, определили типы данных и установили жесткие правила с помощью ограничений (Constraints). Мы использовали язык DDL (Data Definition Language), чтобы создать пустые хранилища. Теперь пришло время наполнить эти хранилища реальной информацией и научиться ей управлять.

Для работы с содержимым таблиц используется другая часть языка SQL — DML (Data Manipulation Language, язык манипулирования данными). Если DDL строит книжный шкаф и определяет размер полок, то DML позволяет ставить на эти полки книги, переставлять их местами и выбрасывать ненужные.

В основе управления данными лежит концепция CRUD. Это акроним, описывающий четыре базовые функции любой системы хранения информации:

* Create (Создание) — добавление новых записей. * Read (Чтение) — извлечение существующих данных. * Update (Обновление) — изменение существующих записей. * Delete (Удаление) — уничтожение записей.

В этой статье мы подробно разберем операции модификации данных: добавление, обновление и удаление. Чтению данных (команде SELECT) будет посвящена отдельная, более объемная тема, так как извлечение информации — это самый сложный и многогранный процесс в SQL.

Добавление данных: команда INSERT

Команда INSERT используется для добавления одной или нескольких новых строк в существующую таблицу.

Представьте, что вы заполняете анкету при регистрации на сайте. Когда вы нажимаете кнопку «Отправить», сервер берет введенные вами данные и формирует SQL-запрос INSERT, чтобы сохранить вашу анкету в базе.

Базовый синтаксис выглядит так:

Рассмотрим практический пример. Допустим, у нас есть таблица students со столбцами student_id (автоматический счетчик), first_name, last_name и enrollment_date.

Обратите внимание на несколько важных правил:

Текстовые строки и даты всегда заключаются в одинарные кавычки ('Иван').

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

Мы не указали столбец student_id. Поскольку при создании таблицы мы задали ему свойство автоинкремента (SERIAL в PostgreSQL или IDENTITY в SQL Server), СУБД сама сгенерирует уникальный номер для Ивана.

Множественная вставка

Если вам нужно добавить сразу 100 студентов, писать 100 отдельных команд INSERT крайне неэффективно. Базы данных позволяют вставлять множество строк одним запросом, перечисляя блоки значений через запятую:

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

Как узнать ID добавленной записи?

Часто в программировании возникает задача: мы добавляем нового пользователя и нам нужно сразу же получить его сгенерированный student_id, чтобы использовать в других таблицах. Здесь синтаксис PostgreSQL и SQL Server различается.

В PostgreSQL используется ключевое слово RETURNING:

В SQL Server применяется конструкция OUTPUT:

Обе команды не просто добавят строку, но и вернут таблицу с одним столбцом и одним значением — новым идентификатором.

!Схема работы операций DML: добавление новой строки, изменение ячейки и удаление строки из таблицы

Обновление данных: команда UPDATE

Данные редко остаются статичными. Студенты меняют фамилии, курсы обновляют программы, а цены в магазинах растут. Для изменения уже существующих строк применяется команда UPDATE.

Синтаксис команды:

Допустим, студентка Анна Смирнова вышла замуж и сменила фамилию на Морозову. Запрос будет выглядеть так:

> Критическое правило: Никогда не забывайте блок WHERE в команде UPDATE. > > Medium

Блок WHERE — это фильтр. Он указывает базе данных, какие именно строки нужно изменить. Если вы выполните запрос UPDATE students SET last_name = 'Морозова'; без условия WHERE, база данных послушно изменит фамилии абсолютно всех студентов в таблице на «Морозова». В реальном проекте такая ошибка может стоить компании миллионов рублей и нескольких дней на восстановление данных из резервных копий.

Для надежности обновления всегда лучше использовать первичный ключ (Primary Key), так как имена могут совпадать:

Удаление данных: команда DELETE

Когда данные больше не нужны, их можно удалить с помощью команды DELETE.

Как и в случае с UPDATE, отсутствие блока WHERE приведет к катастрофе — команда DELETE FROM students; удалит все строки в таблице.

Практика Soft Delete (Мягкое удаление)

В современной разработке физическое удаление строк (DELETE) используется редко. Представьте, что вы удалили пользователя из базы. Вместе с ним, из-за настроек внешних ключей, могут удалиться все его заказы, комментарии и история платежей. Бухгалтерия не сойдется, а аналитика сломается.

Вместо этого инженеры используют паттерн Soft Delete (мягкое удаление). В таблицу добавляется специальный столбец, например is_deleted (логический тип) или deleted_at (дата и время).

Когда пользователь нажимает кнопку «Удалить аккаунт», приложение отправляет не DELETE, а UPDATE:

Данные остаются в базе, связи не рушатся, но во всех запросах на чтение программисты добавляют фильтр WHERE is_deleted = false, чтобы «удаленный» студент больше нигде не отображался.

Защита от ошибок: Транзакции

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

Транзакция — это логическая единица работы, состоящая из одного или нескольких SQL-запросов, которые должны быть выполнены целиком или не выполнены вообще.

Представьте банковский перевод: нужно списать 1000 рублей со счета А и зачислить на счет Б. Это два запроса UPDATE. Если первый выполнится, а на втором сервер отключится от питания, деньги исчезнут. Транзакция гарантирует, что либо выполнятся оба обновления, либо ни одного.

Вы можете управлять транзакциями вручную:

BEGIN; — открывает транзакцию. Все последующие изменения происходят в оперативной памяти и не видны другим пользователям.

Выполняете ваши UPDATE или DELETE.

Проверяете результат.

Если вы ошиблись (например, забыли WHERE), пишете ROLLBACK; — база данных отменит все изменения с момента BEGIN.

Если все верно, пишете COMMIT; — изменения навсегда записываются на жесткий диск.

Работа с DML в интерфейсе DBeaver

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

Редактирование в сетке данных (Data Grid)

В навигаторе слева дважды кликните по нужной таблице.

Перейдите на вкладку Данные (Data) в основном окне. Вы увидите таблицу, похожую на Excel.

Добавление: Нажмите кнопку с зеленым плюсом на нижней панели (или Alt+Insert). Появится новая пустая строка. Заполните ячейки.

Обновление: Просто дважды кликните по любой ячейке и измените ее значение.

Удаление: Выделите строку и нажмите кнопку с красным крестиком (или Delete). Строка зачеркнется красной линией.

Безопасное сохранение

Главное преимущество DBeaver — он не применяет ваши действия мгновенно. Все измененные ячейки подсвечиваются цветом (зеленым для новых, оранжевым для измененных). Это ваша локальная транзакция.

Чтобы отправить изменения в базу, нужно нажать кнопку Сохранить (Save) внизу экрана. DBeaver автоматически сгенерирует безопасные команды INSERT, UPDATE и DELETE на основе ваших кликов и отправит их на сервер. Если вы передумали, нажмите кнопку Отменить (Cancel), и таблица вернется в исходное состояние.

Освоив команды модификации данных, вы получили полный контроль над содержимым вашей базы. Вы умеете создавать структуры и управлять информацией внутри них. Следующий шаг — научиться задавать базе данных сложные вопросы и извлекать именно ту информацию, которая нужна бизнесу.