Теория: Создание таблиц

В этом уроке мы поработаем с таблицами: будем создавать их, добавлять, модифицировать и удалять данные. Также разберем типы данных таблицы.

Создание базы данных

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

DROP DATABASE hexlet;
CREATE DATABASE hexlet;

В примере выше два SQL запроса:

• DROP DATABASE hexlet — удаляет базу данных с именем hexlet

• CREATE DATABASE hexlet — создает базу данных с таким же именем

Базовые правила построения запросов:

• Каждый запрос должен заканчиваться точкой с запятой. Иначе psql будет думать, что вы продолжаете вводить команды

• Регистр не важен. Можно было написать drop database hexlet;. По традиции принято использовать верхний регистр для ключевых слов самого SQL. Это позволяет визуально разделять структуру запроса от данных внутри него. Последнее в примере — это имя базы данных, которое может быть произвольным

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

Команды createdb и createuser, которые мы разобрали в прошлых уроках, выполняют SQL-запросы внутри СУБД. Их сделали ради удобства первоначальной настройки, и чтобы использовать в скриптах автоматизации.

SQL поддерживает комментарии — строчка, которая начинается с двух дефисов. Комментарии игнорируются СУБД при построении запросов:

hexlet=> -- i am comment
hexlet=>

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

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

Таблица создается с помощью запроса CREATE TABLE:

-- Это один запрос, хоть и многострочный.
-- Описание запроса заканчивается символом ;
CREATE TABLE courses (
    name          varchar(255),
    slug          varchar(255),
    lessons_count integer,
    body          text
);

Чтобы создать таблицу, необходимо указать ее имя, набор полей и их типы. В примере выше названия полей — это name, slug, lessons_count и body, а varchar(255), integer и text — их типы.

Типы данных

У каждого поля в PostgreSQL определенный тип, который задается на этапе создания таблицы. Это значит, что значением этого поля могут быть только определенные данные. Если поле имеет числовой тип, то в него невозможно вставить строку, и наоборот. База данных выдаст ошибку при попытке выполнить подобный запрос.

-- Выполняем запрос на вставку передавая в lessons_count строку вместо числа
ERROR:  invalid input syntax for type integer: "wrong value"

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

Строки

Для строк в базах данных в основном используются два типа:

• varchar — для строк с ограничением максимальной длины
• text — для строк без ограничения. Как правило, это полноценные тексты

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

Если размер данных не известен, то придется просматривать весь файл в поисках нужного значения. Чтобы избежать подобной ситуации, тип text хранится отдельно. Это тоже негативно влияет на скорость, но уже не так сильно. Если размер строки известен или он меньше какого-то значения, то предпочтительнее использовать varchar.

• varchar. Полное название типа character varying (varchar может использоваться как псевдоним). Размер строки с таким типом указывается в скобках после названия типа, например, varchar(10). Это значит, что в поле с таким типом можно записать строку длиной до 10 символов.

• text. Не требует указания размера и может содержать текст произвольной длины

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

CREATE TABLE blog_posts (
    name          varchar(80),
    body          text
);

Числа

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

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

CREATE TABLE users (
    id          bigint,
    age         integer
);

Даты

Типы для хранения дат отличаются друг от друга очень сильно, в первую очередь по решаемой задаче. Нам надо хранить день без конкретного времени? Это тип date. Нужно конкретный момент времени, тогда timestamp. Просто время без даты? Тогда time.

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

CREATE TABLE events (
    start_date          date,
     -- имя поля может называться как тип данных
    time                time,
    updated_at          timestamp,
    created_at          timestamp
);

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

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

Логический тип

Содержит всего два значения: true и false. Этот тип используется для флагов:

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

CREATE TABLE blog_posts (
    -- флаг: опубликован?
    published          boolean
);

Состояние «true» может задаваться следующими значениями:

Для состояния «false» можно использовать следующие варианты:

NULL

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

Анализ структуры базы данных

Чтобы исследовать структуру таблиц в визуальном режиме, используется PgAdmin:

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

Просмотр списка таблиц базы данных hexlet

hexlet=> \d
         List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+---------
 public | courses    | table | vagrant
 public | events     | table | vagrant
 public | blog_posts | table | vagrant

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

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

Просмотр структуры таблицы courses

hexlet=> \d courses
# public - обозначает схему по умолчанию
               Table "public.courses"
    Column     |          Type          | Modifiers
---------------+------------------------+-----------
 name          | character varying(255) |
 slug          | character varying(255) |
 lessons_count | integer                |
 body          | text                   |

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

Кроме перечисленных полезными могут оказаться следующие команды:

• \l — список всех баз данных
• \dt — список всех таблиц
• \? — вывод справки

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

Чтобы удалить таблицу, выполняется запрос DROP:

DROP TABLE courses;

Будьте внимательны, так как удаление таблицы приводит к безвозвратной потере данных.

Выводы

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