Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Создание таблиц Основы реляционных баз данных

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

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

Прежде чем создать таблицу, создадим базу данных 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.

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

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

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

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

Числа

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

Имя Описание Диапазон
integer типичный выбор для целых чисел -2147483648 .. +2147483647
bigint целое в большом диапазоне -9223372036854775808 .. 9223372036854775807

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

CREATE TABLE users (
    id          bigint,
    age         integer
);

Даты

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

Имя Описание Наименьшее значение Наибольшее значение Точность
timestamp дата и время (без часового пояса) 4713 до н. э. 294276 н. э. 1 микросекунда
date дата (без времени суток) 4713 до н. э. 5874897 н. э. 1 день
time время суток (без даты) 00:00:00 24:00:00 1 микросекунда

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

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

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

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

Пример Описание
1999-01-08 ISO 8601 (рекомендуемый формат)
January 8, 1999

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

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

Имя Описание
boolean true или false (истина или ложь)

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

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

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

TRUE
't'
'true'
'y'
'yes'
'on'
'1'

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

FALSE
'f'
'false'
'n'
'no'
'off'
'0'

NULL

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

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

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

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;

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

Выводы

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


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

  1. Онлайн-песочница для выполнения SQL-запросов
  2. Типы данных
  3. CREATE TABLE

Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Открыть доступ

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

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»