Проектирование БД

31.10.2024

Создание и модификация базы данных

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

---

1. Создание базы данных в различных СУБД

Создание базы данных — это первый шаг, который требует команды, зависящей от конкретной СУБД. Примеры команд для разных СУБД:

СУБД	Команда для создания БД
MySQL	CREATE DATABASE имя_базы;
PostgreSQL	CREATE DATABASE имя_базы;
SQL Server	Подключение к master БД, затем CREATE DATABASE имя_базы;
Oracle	CREATE DATABASE имя_базы; (может потребоваться дополнительная настройка окружения и файлов конфигурации)
SQLite	sqlite3 имя_базы.db (эта команда создаёт файл базы данных с указанным именем и открывает его для работы)

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

---

2. Запросы на создание таблицы

Таблица — это структура, в которой хранятся данные. Она состоит из строк (записей) и столбцов (полей), и каждое поле имеет определённый тип данных (например, INT, VARCHAR, DATE и т.д.).

Основные компоненты создания таблицы:

• CREATE TABLE — команда для создания таблицы.

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

• Ограничения — правила, задающие уникальность и целостность данных (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL).

Пример создания таблицы в MySQL и PostgreSQL:

CREATE TABLE сотрудники (
    id INT PRIMARY KEY,
    имя VARCHAR(50) NOT NULL,
    должность VARCHAR(50),
    зарплата DECIMAL(10, 2),
    дата_найма DATE
);

Объяснение полей:

• id INT PRIMARY KEY — столбец id является уникальным идентификатором записи (первичный ключ).

• имя VARCHAR(50) NOT NULL — поле имя хранит текст длиной до 50 символов, не может быть пустым.

• зарплата DECIMAL(10, 2) — числовое поле с двумя знаками после запятой.

• дата_найма DATE — дата найма сотрудника.

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

CREATE TABLE сотрудники (
    id INT PRIMARY KEY,
    имя NVARCHAR(50) NOT NULL,
    должность NVARCHAR(50),
    зарплата MONEY,
    дата_найма DATE
);

Особенности SQL Server: Тип NVARCHAR используется для хранения строк с поддержкой Юникода, а тип MONEY оптимизирован для финансовых данных.

Пример для SQLite:

CREATE TABLE сотрудники (
    id INTEGER PRIMARY KEY,
    имя TEXT NOT NULL,
    должность TEXT,
    зарплата REAL,
    дата_найма TEXT
);

Особенности SQLite: Не требует явного указания размера для TEXT, а REAL используется для хранения чисел с плавающей запятой.

---

3. Запросы на редактирование и модификацию таблицы

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

Операция	Описание	SQL Запрос
Добавление столбца	Добавляет новый столбец с заданным типом данных.	ALTER TABLE сотрудники ADD email VARCHAR(50);
Удаление столбца	Удаляет существующий столбец (не поддерживается в SQLite напрямую).	ALTER TABLE сотрудники DROP COLUMN должность;
Изменение типа данных столбца	Меняет тип данных столбца. В некоторых СУБД, таких как SQLite, требует создания новой таблицы.	ALTER TABLE сотрудники ALTER COLUMN зарплата FLOAT;
Переименование столбца	Переименовывает столбец (в PostgreSQL и SQLite используется RENAME COLUMN для этого).	ALTER TABLE сотрудники CHANGE COLUMN имя full_name VARCHAR(50); (MySQL)
Добавление внешнего ключа	Связывает таблицы, ссылаясь на первичный ключ другой таблицы.	ALTER TABLE сотрудники ADD FOREIGN KEY (департамент_id) REFERENCES департаменты(id);
Удаление внешнего ключа	Удаляет внешний ключ (например, если больше не требуется поддерживать связь между таблицами).	ALTER TABLE сотрудники DROP FOREIGN KEY fk_департамент; (MySQL)
Переименование таблицы	Изменяет имя таблицы (в некоторых СУБД используется отдельная команда RENAME).	ALTER TABLE сотрудники RENAME TO staff; (PostgreSQL)

Детальное описание операций:

• Добавление столбца: позволяет расширить таблицу новым столбцом. Пример: ALTER TABLE сотрудники ADD возраст INT; добавит столбец возраста.

• Удаление столбца: в некоторых СУБД, таких как SQLite, эта операция может потребовать пересоздания таблицы, так как прямой поддержки DROP COLUMN нет.

• Изменение типа данных: меняет формат данных, если нужно, например, большее число знаков после запятой. Пример: ALTER TABLE сотрудники ALTER COLUMN зарплата DECIMAL(12, 2);.

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

  ALTER TABLE сотрудники
  ADD CONSTRAINT fk_департамент
  FOREIGN KEY (департамент_id) REFERENCES департаменты(id);