> For the complete documentation index, see [llms.txt](https://evgenii-afanasev.gitbook.io/ruby-course/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://evgenii-afanasev.gitbook.io/ruby-course/12_database/2_ddl_operations.md).

# Создание, изменение и удаление таблиц

Операции по изменению таблиц имеют более стандартизированное название - DDL (Data Definition Language) и в данном уроке мы разберем все основные DDL операции, присутствующие в SQL.

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

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

Что такое таблица ? Самое простое представление это обычная таблица Excel или CSV, где у нас есть колонки, названия колонок и строки со значениями для этих колонок.

Для того, чтобы создать новую таблицу нам нужно воспользоваться командой `CREATE TABLE`. Ее синтаксис:

```sql
CREATE TABLE [IF NOT EXISTS] название_таблицы(
  название_колонки ТИП_КОЛОНКИ [ПАРАМЕТРЫ],
  название_другой_колонки ТИП_КОЛОНКИ [ПАРАМЕТРЫ]
);
```

### Основные типы данных в SQL

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

#### Числа

Числовые значения передаются без каких-либо дополнительных атрибутов.

* `INT` - Натуральное число (например `100`)
* `DOUBLE` - число с плавающей запятой запятой (например `125.83`)

#### Строки

Строковые значения при добавлении обрамляются одинарными ковычками (например `'John'`)

* `CHAR(макс_кол-во_символов)` - строка с фиксированным максимальным размером. Если размер строки меньше указанного, то строка дополняется пробелами. Максимум символов - 8000
* `VARCHAR(макс_кол-во_символов)` - строка с нефиксированным размером. Если размер меньше установленного, то размер не увеличивается и добавляется как есть.Максимум символов - 8000
* `TEXT` - строка с максимальным размером значения в 2GB.

#### Метка времени

* `DATE` - дата в формате `YYYY-MM-DD`. Значение передается с помощью обрамления в одинарные ковыки (например `'2023-03-02'`)
* `TIME` - время в формате `HH:MI:SS`. Значение передается с помощью обрамления в одинарные ковыки (например `'00:00:00'`)
* `DATETIME` - дата и время в формате `YYYY-MM-DD HH:MI:SS`. Значение передается с помощью обрамления в одинарные ковыки (например `'2023-03-02 00:00:00'`)
* `TIMESTAMP` - POSIX-время, количество секунд прошедшее с 00:00:00 UTC 1 января, 1970 года. Значение передается в цифровом формате без каких либо дополнительных атрибутов (например `1683107624`)

### Первичный ключ (PK)

Первичный ключ — особенное поле в SQL-таблице, которое позволяет однозначно идентифицировать каждую запись в ней. Первичный ключ всегда уникален. Благодаря нему БД может определять дубли. Например если вы добавили в таблицу сущность с ключом равным 3, то при попытке добавить еще одну сущнсть с таким же ключом, даже если все остальные поля отличаются, БД вернет нам ошибку и сущность добавлена не будет.

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

#### Односоставный ключ

Односоставный ключ, судя по названию, является ключом таблицы состоящим из одной колонки. Создать односоставный ключ можно 2 способами:

* Добавить `PRIMARY KEY` в качестве параметра у атрибута:

```sql
CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ PRIMARY KEY
);
```

* Указать первичный ключ в качестве отдельного атрибута:

```sql
CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ,
  PRIMARY KEY (название_колонки)
);
```

#### Композитный ключ

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

```sql
CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ,
  название_другой_колонки ТИП_ДАННЫХ
  PRIMARY KEY (название_колонки, название_другой_колонки)
);
```

### Внешний ключ (FK)

Если первичный ключ является идентификатором нашей таблицы, то внешние ключи (Foreign Keys) предсталвяют из себя указатели на перевичный ключ другой таблицы. Отсюда и происходит название "реляционная". Когда мы объявляем внешний ключ, то мы всегда указываем на таблицу к которой он привязан, а также первичный ключ этой таблицы, к которому будет привязан наш внешний ключ.

Синтаксис внешнего ключа:

```sql
CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ,
  FOREIGN KEY (название_колонки) REFERENCES название_связанной_таблицы(название_колонки_связяанной_таблицы)
);
```

### Ограничения

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

* `NULL` - параметр, обозначающий, что значение данного столбца может содержать в себе пустое значение `NULL`. По аналогии `nil` в Ruby.

Синтаксис:

```sql
CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ NULL
);
```

На самом деле `NULL` не является обязательным параметром. По-умолчанию, все колонки при создании могут содержать `NULL`.

* `NOT NULL` - параметр, обозначающий, что значение данного столбца не может содержать в себе пустое значение `NULL`.

Синтаксис:

```sql
CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ NOT NULL
);
```

Если вы объявляете колонку `NOT NULL`, то, соответственно, вы переопределяете поведение по умолчанию. `PRIMARY KEY` по-умолчанию включает в себя параметр `NOT NULL`.

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

Синтаксис:

```sql
CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ UNIQUE
);
```

`PRIMARY KEY` по-умолчанию включает в себя параметр `UNIQUE`.

* `AUTOINCREMENT` - параметр, обозначающий, что каждое значение в данной колонке будет иметь на одну единицу больше, чем предыдущее добавленное. Например, вы добавили значение `3`, при попытке добавить еще одно значение в эту же таблицу со значением для этой колонки `NULL`, оно будет равняться `4`, а не `NULL`.

Синтаксис:

```sql
CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ AUTOINCREMENT
);
```

* `DEFAULT` - параметр, обозначающий, что в случае передачи в качестве значения `NULL`, будет подставляться значение по-умолчанию, которое было заранее определено при создании таблицы.

Синтаксис:

```sql
CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ DEFAULT значение_по_умолчанию
);
```

Помимо статичного значения можно использовать динамичное, например `current_timestamp`, которое показывает каждый раз текущее время в формате POSIX.

## Изменение таблицы

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

```sql
ALTER TABLE название_таблицы ПАРАМЕТР
```

### Добавление колонок

Добавление колонки происходит с помощью добавления параметра `ADD COLUMN`.

Синтаксис:

```sql
ALTER TABLE название_таблицы ADD COLUMN название_добавляемой_колонки ТИП_КОЛОНКИ
```

Кроме того мы можем добавлять ограничения для новых колонок, например `NOT NULL`, `UNIQUE` или добавить значение по умолчанию.

Синтексис:

```sql
ALTER TABLE название_таблицы ADD COLUMN название_добавляемой_колонки ТИП_КОЛОНКИ ПАРАМЕТР
```

### Удаление колонок

Синтаксис:

```sql
ALTER TABLE название_таблицы DROP COLUMN название_колонки
```

### Переименнование колонок

Синтаксис:

```sql
ALTER TABLE название_таблицы RENAME COLUMN исходное_название_столбца TO итоговое_название_столбца
```

## Удаление таблиц

Синтаксис:

```sql
DROP TABLE название_таблицы
```

## Пример из реальной жизни

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

* Товары. Любой интернет магазин занимается продажей вещей, соответственно у любого интернет магазина есть товары.
* Покупатели. Покупатель может зарегистрироваться на сайте, совершать покупки и тд.
* Заказы. Заказы слушат некой связкой покупателя и товара. По заказу мы модем определить кто покупает и что покупает.

### Товар

Какие атрибуты могут быть у товара ?

* номер товара или его идентификатор. Значение, которое будет служить для отличия товаров друг от друга.
* Производитель. Нам магазин может продать разные модели от одного и того же производителя
* модель. Модель конкретного производителя
* цвет. В магазине у нас может продаваться модель в разных цветах.
* Дата добавления. Полезный атрибут, позволяющий отличить старый товар от нового.
* цена

Соответсвенно мы можем все эти атрибуты превратить в таблицу:

| id | manufacture | model     | color | date\_added | price   |
| -- | ----------- | --------- | ----- | ----------- | ------- |
| 1  | apple       | iphone 14 | black | 01.12.2022  | 100000  |
| 2  | samsung     | a54       | white | 01.04.2023  | 30000   |
| 3  | apple       | iphone 14 | white | 21.12.2022  | 1010000 |

Как будет выглядеть создание такой таблицы:

```sql
CREATE TABLE IF NOT EXISTS items (
  id INTEGER PRIMARY KEY,
  manufacture TEXT NOT NULL,
  model TEXT NOT NULL,
  color TEXT NOT NULL,
  price INTEGER NOT NULL,
  date_added TIMESTAMP NOT NULL
);
```

Здесь мы указали, что нашим первичным ключом будет служить поле `id`, а все остальные атрибуты будут иметь параметр `NOT NULL`, что означает, что все поля обязательны для заполнения при добавлении нового товара.

### Покупатели

Теперь подумаем какие атрибуты необходимы для покупателя:

* идентификатор. Тоже самое, что и с товарами
* имя. Часть персональных данных, необходимая для идентификации человека
* фамилия. Другая часть персональных данных, необходимая для идентификации человека
* телефон. Во время доставки нам важно иметь возможность иметь связь с покупателем для уточнения деталей.
* email. Почта позволит оповещать нашего клиента о новом статусе заказа или рассылать рекламные предложения.

Итоговая таблица покупателей:

| id | first\_name | last\_name | delivery\_address                    | phone       | email                   |
| -- | ----------- | ---------- | ------------------------------------ | ----------- | ----------------------- |
| 1  | Иван        | Петров     | Moscow, Lenina st, appt 12           | 79999999999 | <ivan.petrov@mail.ru>   |
| 2  | Ксения      | Андреева   | Vladimir, Aviation st, appt. 123, 23 | 79991111111 | <ksenya.andreeva@ya.ru> |

SQL синтаксис:

```sql
CREATE TABLE IF NOT EXISTS customers (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  phone INTEGER NOT NULL UNIQUE,
  delivery_address TEXT NOT NULL,
  email TEXT
);
```

В данном случае у нас `id` снова служит первичным ключом, а все остальные поля обязательны, кроме `email`, что позволяет пользователю при регистрации его не вводить.

### Заказы

Ну и наконец разберем атрибуты заказов:

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

Итоговая таблица покупателей:

| item\_id | customer\_id | date\_added         | description                     |
| -------- | ------------ | ------------------- | ------------------------------- |
| 1        | 2            | 2023-03-25 15:21:11 | Доставка 2023.03.31 после 16:00 |
| 2        | 1            | 2023-02-12 10:32:16 | Доставка 2023.02.13 после 08:00 |
| 1        | 2            | 2023-01-11 19:29:46 | Доставка 2023.01.13 после 18:00 |

Синтаксис создания таблицы:

```sql
CREATE TABLE orders (
  item_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL,
  date_added TIMESTAMP NOT NULL,
  description TEXT,
  PRIMARY KEY (item_id, customer_id, date_created),
  FOREIGN KEY (item_id)  REFERENCES items(id),
  FOREIGN KEY (customer_id)  REFERENCES customers(id)
);
```

В данном случае нашим первичным ключом является композитный ключ состоящий из 3 колонок: `item_id`, `customer_id` и `date_created`. Так как:

* Допустим мы бы сделали односоставный ключ с идентификатором товара или пользователя, тогда мы сталкиваемся с проблемой, что у нас либо может быть куплен только один товар, либо пользователь может совершить только одну покупку.
* Если бы мы сделали композитный ключ состоящий из идентификатора пользователя и товара только, тогда бы пользователь не смог купить один и тот же товар более 1 раза.

Помимо первичного ключа у нас также присуствуют внешние ключи из которых:

* `item_id` является значением столбца `id` из таблицы `items`.
* `customer_id` является значением столбца `id` из таблицы `customers`.

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

## Задание university

В конце урока будет приложена ER диаграмма базы данных university. Вам необходимо:

* Написать для каждой таблицы отдельный файл в формате `.sql` с командой для создания этой таблицы. Файл должен называться `create_название_таблицы_table.sql`.
* Запустить все эти скрипты для проверки работоспособности.

Рекомендации:

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

## Дополнительный материал

* [Создание и удаление таблиц](https://metanit.com/sql/sqlite/2.1.php)
* [Ограничения столбцов](https://metanit.com/sql/sqlite/2.3.php)
* [Внешние ключи](https://metanit.com/sql/sqlite/2.4.php)
* [Изменение таблиц](https://metanit.com/sql/sqlite/2.5.php)