В этом уроке мы поработаем с таблицами: будем создавать их, добавлять, модифицировать и удалять данные. Также разберем типы данных таблицы.
Создание базы данных
Прежде чем создать таблицу, создадим базу данных 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:
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;
Будьте внимательны, так как удаление таблицы приводит к безвозвратной потере данных.
Выводы
В этом уроке мы разобрали работу с таблицами: создавали базу данных и таблицу, устанавливали типы данных и анализировали структуру базы. Еще мы показали, как удалять таблицу. Попрактикуйтесь создавать таблицы — проходите каждый шаг, описанный в уроке, и со временем для вас это станет легкой задачей.
Дополнительные материалы
Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты