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

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

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

Итак, базовый синтаксис. За основу возьмем уже хорошо знакомую нам таблицу passenger и запрос, с помощью которого ее создали:

create table passenger (
  id                  bigserial,
  first_name          varchar(100),
  last_name           varchar(100),
  birth_date          date,
  male                boolean         default true, 
  last_purchase       timestamp,
  favorite_airports   text[]
);

Полагаю, синтаксис достаточно очевиден, но есть моменты, которые стоит подсветить.

Оператор CREATE является одним из основополагающих в DDL и позволяет создать какой-либо элемент структуры в рамках СУБД. Какой элемент требуется создать - указывается следующим (если опустить необязательные модификаторы) оператором.

Мы встречали как минимум три структурных элемента, создаваемых через CREATE:

1. DATABASE;
2. SCHEMA;
3. TABLE.

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

После этого указывается имя элемента. В нашем случае - таблицы. 

Как и в других языках программирования, в SQL есть зарезервированные слова. И, что логично, эти слова нельзя использовать в качестве названий таблиц, колонок, алиасов и прочих имен. Но иногда очень хочется (чаще всего - при создании таблицы user, а это слово - тоже зарезервировано в SQL). Стоит ли так делать - вопрос не однозначный, но обычно - нет.
В любом случае, возможность использовать ключевое слово для названия есть - с поправкой на СУБД, обычно она заключается в необходимости заключить такое слово в кавычки (в PostgreSQL - двойные, для других СУБД - уточняйте в документации).
То есть:
create table user (...); - ошибка;
create table "user" (...); - все ок:)

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

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

Рассматривая запрос выше, можно увидеть, что не всегда дело ограничивается названием колонки и типом данных - есть, например, такое:

 male                boolean         default true, 

На самом деле, в описании колонки тоже можно указать массу полезных атрибутов, которые мы постепенно разберем. Пока же остановимся на DEFAULT.

В случае использования этого оператора к описанию колонки, он позволяет задать значение по умолчанию, которое будет использовано, если при добавлении записи в таблицу не было явно указано значения для соответствующего столбца. Так, в нашем случае будет проставляться true в колонке male, если не указано иного.

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

Но в DEFAULT можно указать, например, и функцию (правда, в параметрах нельзя использовать столбцы таблицы), что открывает более широкие возможности. Например, распространено заполнение даты создания записи через DEFAULT now(), что позволяет автоматически записывать текущую дату и/или время (с поправкой на тип самого поля).

В целом, на этом можно завершить описание базового синтаксиса - он довольно прост.

И перейти к практике:)

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

Задача 1

Добавьте в БД таблицу airport. В состав колонок в обязательном порядке должны входить автоинкрементирующийся id и имя аэропорта. Остальные атрибуты могут быть добавлены на ваш вкус.

Задача 2

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

Данная промежуточная таблица должна позволить через функциональность JOIN связывать пассажиров с их любимыми аэропортами. Обычно такого рода таблицы именуются как table1_table2.

Задача 3

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

Обновление таблицы билетов сделаем уже в ближайших уроках:)

Задача 4

Добавьте таблицу user. Она должна обеспечивать информацию об учетной записи в нашей условной информационной системе. Полагаю, в нее стоит добавить id, username и пароль, остальные колонки - на ваш вкус. 

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!