Описание:

Из предыдущего задания вы поняли, что PostgreSQL — это система управления объектно-реляционными базами данных (ORDBMS). Также вы установили в систему определенную версию СУБД PostgreSQL и создали нового пользователя для работы с ней. Теперь вам нужно разобраться в том, как работать с базами данных, и познакомиться с основными понятиями реляционных баз данных. После этого узнать, как создавать свои базы данных и управлять таблицами, как записывать информацию в базу данных и получать для работы конкретные данные из конкретных таблиц.

Мы начнем с разбора главных понятий и описания общего синтаксиса SQL.

Основные понятия

База данных (БД) — это организованная структура данных, предназначенная для хранения, изменения и обработки взаимосвязанной информации, преимущественно больших объемов.

Любая база данных обладает набором преимуществ перед неструктурированными данными, а именно:

• Централизация хранения данных – ввод и хранение всех данных выполняются централизованно. Это минимизирует использование большого количества текстовых файлов, папок, а также уменьшает вероятность потери данных.
• Многопользовательский режим – доступ к данным, находящимся в БД, одновременно могут получить несколько пользователей, используя глобальную, локальную сети или множественный доступ к серверу, при этом физическое или географическое их месторасположение не имеет большого значения.
• Актуальность данных – изменения, внесенные пользователями, мгновенно становятся доступны другим пользователям (если активирован режим autocommit, как правило, это режим работы по умолчанию).
• Скорость и производительность – вы можете искать, сортировать, извлекать, вносить изменения, выводить данные и выполнять различные расчеты значительно быстрее, чем если бы вы это делали вручную.
• Точность и согласованность – вы можете обеспечивать проверку вводимых данных на корректность, тем самым обеспечивая правильность данных, которые содержатся в базе данных. Например, пользователь базы данных не сможет удалить карточку клиента, если у этого клиента имеются заказы (CONSTRAINT).
• Возможность анализа данных – базы данных могут хранить, отслеживать и обрабатывать огромные объемы данных с разными объявленными типами.
• Безопасность – для обеспечения безопасности вы можете определять списки авторизованных пользователей, у которых доступ к данным будет закрыт паролем. Например, вы можете ограничить пользователя в выполнении определенных действий.
• Восстановление данных – системные сбои неизбежны. Целостность данных в результате таких сбоев самой БД не гарантируются, но для решения этих вопросов есть системы управления базами данных (СУБД). Они предоставляют необходимый функционал для осуществления своевременных бэкапов данных и позволяют восстанавливать данные в экстренном режиме в случае сбоев. Также СУБД используют транзакционный журнал (transaction log), для того чтобы проверять правильность восстановления данных после аварии.

Транзакции и восстановление

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

Чтобы удовлетворять определению транзакции, СУБД должна придерживаться следующих четырех свойств!

1. Атомарность (atomicity) – транзакция должна быть полностью завершена или полностью отменена. Не может быть частичных транзакций.
2. Последовательность (consistency) – транзакция должна преобразовывать базу данных из одного непротиворечивого состояния в другое.
3. Изолированность (isolation) – каждая транзакция должна выполняться независимо от любой другой транзакции.
4. Долговечность (durability) – постоянность завершенных транзакций, то есть изменения, внесенные завершенной транзакцией, не могут быть отменены.

Если ваша СУБД удовлетворяет всем свойствам транзакции, то она будет являться ACID-устойчивой.

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

Элементы реляционных баз данных

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

Основными структурными элементами реляционных баз данных в реляционной модели являются:

• Таблица (table)
• Поле (column или field)
• Строка (row или record)
• Ключ (key)

Таблицы

Таблица — это логически связанная информация, рассматриваемая как единое целое.

Обычно таблицы делятся на три типа:

• Основные – это таблицы, являющиеся независимыми сущностями. Они часто обозначают или моделируют объекты, существующие в реальном мире. Например: клиенты, продавцы, сотрудники, товары и прочее.
• Ассоциативные – это таблицы, которые представляют связи между объектами. Например: заказ (Order) связан с клиентом и товарами.
• Таблицы характеристик (служебные) — это служебные таблицы, которые нужны, чтобы квалифицировать и описать какие-либо объекты базы данных. Эти таблицы зависят от объекта, для которого созданы. Информация в них будет бесполезна, если описываемый объект перестанет существовать.

Таблицы состоят из строк (row) (или записей (record)) и столбцов (column) (или полей (field)).

Ниже приведен пример таблицы Users:

Поля (столбцы)

Поле — это «столбец» таблицы, включающий в себя значения определенного типа.

Каждое поле должно иметь конкретный тип данных.

Типы данных:

В таблице Users, представленной выше, имеется 6 столбцов:

• user_id (тип - INT)
• firstname (тип - TEXT)
• lastname (тип - TEXT)
• username (тип - TEXT)
• password (тип - VARCHAR(50))
• is_admin (тип - BOOLEAN)

В таблице реляционной базы данных одинаковый набор столбцов (column) или полей (field) формирует строку этой таблицы.

Строки (записи)

Запись (строка) — это сущность таблицы, содержащая набор значений, размещенных в полях базы данных.
Эти значения характеризуют какой-то конкретный объект, например пользователя приложения из таблицы пользователей. Каждая строка рассматривается как отдельный объект. В таблице Users, представленной выше, содержится четыре строки, каждая из которых содержит информацию о конкретном пользователе. Например, ниже «одной строкой» представлена информация о пользователе (объекте) Petya Pertrov.

Ключи

Как правило, в современных реляционных базах данных существует два основных типа ключей (key):

• Primary Key
• Foreign Key

Primary Key — это столбец или группа столбцов, которые содержат уникальные значения, однозначно идентифицирующие строку в таблице. Поскольку Primary Key всегда уникален, он используется для исключения дублирования данных.

Хороший Primary Key должен обладать следующими характеристиками:

• Обязательность (mandatory) наличия данных — столбец не может содержать нулевые или пустые значения. Если столбец будет оставлен пустым, то существует вероятность возникновения дублирования записей.
• Уникальность (unique). Например, таблица Users должна содержать уникальный идентификатор для каждого пользователя. Столбец user_id в таблице Users — это уникальный идентификатор каждого пользователя. Более практично использовать именно это поле, чем поле firstname, так как разные пользователи могут иметь одинаковые имена. Также база данных не определяет различия в именах как дубликаты, например, Cathy для Catherine или Joe для Joseph. Кроме того, иногда люди могут менять имена или фамилии, например, после вступления в брак.
• Стабильность (stable) — значение Primary Key не может измениться без вмешательства администратора базы банных. Как и в предыдущем примере, user_id — это хороший ключ, так как он может принимать значение, выданное базой данных автоматически, оно не только является уникальным, но и вряд ли когда-то изменится, в то время как имя клиента может поменяться.
• Краткость (short) — ключ не должен содержать много символов. Маленькие столбцы занимают меньше места, они быстрее будут искаться в базе и менее склонны к ошибкам. Например, каждое значение поля password может состоять из 50 символов, обратиться к нему намного сложнее, чем обратиться к полю user_id, которое является просто числом.

Теперь разберемся с Foreign Key.

Foreign Key – это значение столбца в одной таблице, которое должно соответствовать значению Primary Key столбца в другой таблице.
Другими словами, это ссылка одной таблицы на другую.
Если значение Foreign Key не нулевое, то значение Primary Key в другой таблице должно существовать.
Такие связи одного столбца таблицы с другим столбцом другой таблицы образуют реляционную базу данных, способную объединять таблицы.

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

Индексы

Индекс — это объект базы данных, создаваемый для повышения производительности выполнения запросов. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному значению путем последовательного просмотра таблицы строка за строкой может занимать много времени.
Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы. Это позволяет находить нужную строку по заданному значению. Ускорение работы с использованием индексов достигается в первую очередь за счет того, что индекс имеет структуру, оптимизированную под поиск (например, бинарное дерево).
Индексы могут быть уникальными и неуникальными. Отличие их лишь в том, что уникальный реализует ограничение целостности таблицы, исключая возможность вставки повторяющихся значений.

Индексы имеют следующие преимущества:

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

Роли

PostgreSQL использует концепцию ролей (roles) для управления разрешениями на доступ к базе данных. Роль можно рассматривать как пользователя базы данных или как группу пользователей, в зависимости от того, как роль настроена. Роли могут владеть объектами базы данных (например, таблицами и функциями) и выдавать другим ролям разрешения на доступ к этим объектам, управляя тем, кто имеет доступ и к каким объектам. Кроме того, можно предоставить одной роли членство в другой, и таким образом она сможет использовать права других ролей.

Концепция ролей включает в себя концепцию пользователей (users) и групп (groups). До версии 8.1 в PostgreSQL пользователи и группы были отдельными сущностями, но теперь есть только роли. Любая роль может использоваться в качестве пользователя, группы или и того, и другого совместно.

Подробнее роли вы разберете в задании 4.

Теперь перейдем к языку SQL.

SQL (Structured Query Language), основы запросов для PostgreSQL

Structured Query Language, SQL — декларативный язык программирования, применяемый для создания, изменения, перемещения и удаления данных в реляционных базах данных, которыми управляет соответствующая СУБД. В нашем случае — это СУБД PostgreSQL.

SQL содержит в себе следующие 4 подраздела языка:

• Язык определения данных (Data Definition Language, DDL)
• Язык манипулирования данными (Data Manipulation Language, DML)
• Язык управления данными (Data Control Language, DCL)
• Язык управления транзакциями (Transaction Control Language, TCL)

1. Язык определения данных (Data Definition Language, DDL) — это часть языка SQL, которая используется для создания, изменения и уничтожения основных элементов реляционной базы данных. В число этих элементов могут входить таблицы, представления, схемы, каталоги, кластеры и, возможно, не только они.

Основные DDL команды для PostgreSQL:

CREATE — создает объект СУБД или базы данных, например базу данных.

# Это язык DDL SQL
# Создать базу данных db_name

CREATE DATABASE db_name;

ALTER — изменяет параметры объекта СУБД или базы данных, например выбранной роли.

# Это язык DDL SQL
# Изменение пароля роли

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

DROP — удаляет объект СУБД или базы данных, например таблицу для текущей базы данных.

# Это язык DDL SQL
# Удалить таблицу users

DROP TABLE users;

2. Язык манипулирования данными (Data Manipulation Language, DML) — это часть языка SQL, которая позволяет пользователям с помощью специальных команд получать, вставлять, изменять или удалять данные в базе данных.

Основные DМL команды для PostgreSQL:

SELECT — получить строки из таблицы или представления.

SELECT получает строки из множества таблиц (это множество может быть пустым).

# Это язык DML SQL
# Выбрать все значения полей user_id, username, password из таблицы users

SELECT user_id,username,password FROM users;

INSERT — добавить строки в таблицу.

# Это язык DML SQL
# Добавить в таблицу users нового пользователя web-приложения, у которого:
# firstname = 'max'
# lastname = 'weber'
# username = 'web_admin'
# password = 'web_admin_STR0Ng_P@s$woRD'

INSERT INTO users(firstname,lastname,username,password)
VALUES ('max','weber','web_admin','web_admin_STR0Ng_P@s$woRD');

UPDATE — изменить строки таблицы.

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

# Это язык DML SQL 
# Обновление поля username для пользователя с username = 'hero1' в таблице users

UPDATE users SET username = 'Dramatic' WHERE username = 'hero1';

DELETE — удалить записи таблицы.

Команда DELETE удаляет из указанной таблицы строки, удовлетворяющие условию WHERE. Если предложение WHERE отсутствует, она удаляет из таблицы все строки, в результате будет получена рабочая, но пустая таблица.

# Это язык DML SQL
# Удалить столку из таблицы users где user_id = 2;

DELETE FROM users WHERE user_id = 2;

# Удалить все строки из таблицы users

DELETE FROM users;
# Будет получена рабочая, но пустая таблица users.

3. Язык управления данными (Data Control Language, DCL) — это часть языка SQL, предназначенная для административных операций, присваивающих или отменяющих право (привилегию) использовать базу данных, таблицы и другие объекты базы данных, а также выполнять те или иные команды SQL.

Основные DCL команды для PostgreSQL:

GRANT — выдать права доступа.

Команда GRANT имеет два варианта использования:

• назначает права для доступа к объектам баз данных (таблицам, столбцам, представлениям, сторонним таблицам, последовательностям, базам данных, оберткам сторонних данных, сторонним серверам, функциям, процедурам, процедурным языкам, схемам или табличным пространствам);
• назначает одни роли членами других.

# Это язык DСL SQL
# Первый вариант использования GRANT
# Выдать все права на базу данных 'db_name' пользователю db_user

GRANT ALL PRIVILEGES ON DATABASE 'db_name' TO db_user;

# Второй вариант использования GRANT
# Включение в роль admins пользователя joe

GRANT admins TO joe;

REVOKE — отозвать права доступа.

Команда REVOKE лишает одну или несколько ролей прав, назначенных ранее.

# Это язык DСL SQL
# Отозвать все права на базу данных 'db_name' у пользователя db_user

REVOKE ALL PRIVILEGES ON DATABASE 'db_name' TO db_user;

4. Язык управления транзакциями (Transaction Control Language, TCL) — это часть языка SQL, используемая для обработки и контроля транзакций.

BEGIN — начать блок транзакции.

BEGIN начинает блок транзакции, то есть обозначает, что все операторы после команды BEGIN и до явной команды COMMIT или ROLLBACK будут выполняться в одной транзакции. По умолчанию (без BEGIN) PostgreSQL выполняет транзакции в режиме «autocommit» (автофиксация), то есть каждый оператор выполняется в своей отдельной транзакции, которая неявно фиксируется в конце оператора (если оператор был выполнен успешно; в противном случае транзакция откатывается).

COMMIT — зафиксировать текущую транзакцию.

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

ROLLBACK — прервать текущую транзакцию.

ROLLBACK откатывает текущую транзакцию и приводит к аннулированию всех изменений, произведённых транзакцией.

Общая процедура выполнения SELECT

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

В СУБД PostgreSQL, например, для SELECT процедура выполнения следующая:

1. Выполняются все запросы в списке WITH. По сути, они формируют временные таблицы, к которым затем можно обращаться в списке FROM. Запрос в WITH без указания NOT MATERIALIZED выполняется только один раз, даже когда он фигурирует в списке FROM неоднократно.

2. Вычисляются все элементы в списке FROM (каждый элемент в списке FROM представляет собой реальную или виртуальную таблицу). Если список FROM содержит несколько элементов, они объединяются перекрестным соединением.

3. Если указано предложение WHERE, все строки, не удовлетворяющие условию, исключаются из результата.

4. Если присутствует указание GROUP BY либо в запросе вызываются агрегатные функции, вывод разделяется по группам строк, соответствующим одному или нескольким значениям, а затем вычисляются результаты агрегатных функций. Если добавлено предложение HAVING, оно исключает группы, не удовлетворяющие заданному условию.

5. Вычисляются фактические выходные строки по заданным в SELECT выражениям для каждой выбранной строки или группы строк.

6. SELECT DISTINCT исключает из результата повторяющиеся строки. SELECT DISTINCT ON исключает строки, совпадающие по всем указанным выражениям. SELECT ALL (по умолчанию) возвращает все строки результата, включая дубликаты.

7. Операторы UNION, INTERSECT и EXCEPT объединяют вывод нескольких команд SELECT в один результирующий набор. Оператор UNION возвращает все строки, представленные в одном либо обоих наборах результатов. Оператор INTERSECT возвращает все строки, представленные строго в обоих наборах. Оператор EXCEPT возвращает все строки, представленные в первом наборе, но не во втором. Во всех трех случаях повторяющиеся строки исключаются из результата, если явно не указано ALL. Чтобы явно обозначить, что выдаваться должны только неповторяющиеся строки, можно добавить избыточное слово DISTINCT. Заметьте, что в данном контексте по умолчанию подразумевается DISTINCT, хотя в самом SELECT по умолчанию подразумевается ALL.

8. Если присутствует предложение ORDER BY, возвращаемые строки сортируются в указанном порядке. В отсутствие ORDER BY строки возвращаются в том порядке, в каком системе будет проще их выдать.

9. Если указано предложение LIMIT (или FETCH FIRST) либо OFFSET, оператор SELECT возвращает только подмножество строк результата.

10. Если указано FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE или FOR KEY SHARE, оператор SELECT блокирует выбранные строки, защищая их от одновременных изменений.

11. Для всех столбцов, задействованных в команде SELECT, необходимо иметь право SELECT. Применение блокировок FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE требует также права UPDATE (как минимум для одного столбца в каждой выбранной для блокировки таблице).

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

Как сохранить результат запроса на диск?

Иногда требуется сохранить результаты какого-либо запроса на диск, для этого есть несколько вариантов решения.

1. Вы можете прямо из командной строки OС вызвать команду psql указав флаг -о и имя выходного файла:

psql -c "SELECT username,city FROM users;" -U rebrain_admin -d rebrain_courses_db -h localhost -p 5432  -o /tmp/out_fileaname.query_result

2. Уже работая в psql консоли с базой данных (подключившись к ней), можно выполннить метакоманду \o "file_name":

\o /tmp/out_fileaname.query_result2
SELECT * FROM courses;

Чтобы выполнить команду для операционной системы из psql консоли, можно воспользоваться метакомандой \! "cmd":

\! mkdir -p /tmp/your_new_directory_name
\! ls /tmp/
\! cat /tmp/out_fileaname.query_result2

Теперь разберемся с JOIN.

Основы JOIN

Одним из способов обединения таблиц является использование оператора JOIN. Он предоставляет вам возможность объединения данных из одной или из разных таблиц в одном запросе, используя различные варианты записи, в зависимости от требований.

Варианты использования оператора JOIN указаны на рисунке ниже:

Запрос JOIN объединяет два элемента списка FROM, которые мы для простоты дальше будем называть «таблицами», хотя на самом деле это может быть любой объект, допустимый в качестве элемента FROM. Для определения порядка вложенности при необходимости следует использовать скобки. В отсутствие скобок предложения JOIN обрабатываются слева направо. В любом случае JOIN связывает элементы сильнее, чем запятые, разделяющие элементы в списке FROM.

Теперь разберемся в этих вариантах использования оператора JOIN подробнее.

JOIN или INNER JOIN

JOIN или INNER JOIN формирует простое декартово произведение, то же, что можно получить, указав две таблицы на верхнем уровне FROM, но ограниченное возможным условием соединения. Предложение CROSS JOIN равнозначно INNER JOIN ON (TRUE), то есть никакие строки по условию не удаляются. Эти типы соединений введены исключительно для удобства записи, они не дают ничего такого, что нельзя было бы получить, используя просто FROM и WHERE.

# Пример INNER JOIN

SELECT list,of,fields 
FROM table_A AS A
INNER JOIN table_B AS B
ON A.Key = B.Key

LEFT OUTER JOIN

LEFT OUTER JOIN возвращает все строки ограниченного декартова произведения (то есть все объединенные строки, удовлетворяющие условию соединения) плюс все строки в таблице слева, для которых не находится строк в таблице справа, удовлетворяющих условию. Строка, взятая из таблицы слева, дополняется до полной ширины объединенной таблицы значениями NULL в столбцах таблицы справа. Заметьте, что для определения, какие строки двух таблиц соответствуют друг другу, проверяется только условие самого предложения JOIN. Внешние условия проверяются позже.

# Пример LEFT OUTER JOIN

SELECT list,of,fields 
FROM table_A AS A
LEFT OUTER JOIN table_B AS B
ON A.Key = B.Key

LEFT EXCLUDING JOIN

LEFT EXCLUDING OUTER JOIN возвращает только строки для каждой строки слева, не имеющей соответствия справа (эта строка дополняется значениями NULL вправо).

# Пример LEFT OUTER JOIN

SELECT list,of,fields 
FROM table_A AS A
LEFT OUTER JOIN table_B AS B
ON A.Key = B.Key
WHERE B.Key IS NULL

RIGHT OUTER JOIN

RIGHT OUTER JOIN возвращает все строки, имеющие соответствия справа и слева, плюс одну строку для каждой строки справа, не имеющей соответствия слева (эта строка дополняется значениями NULL влево). Это предложение введено исключительно для удобства записи, так как его можно легко свести к LEFT OUTER JOIN, поменяв левую и правую таблицы местами.

# Пример RIGHT OUTER JOIN

SELECT list,of,fields 
FROM table_A AS A
RIGHT OUTER JOIN table_B AS B
ON A.Key = B.Key

RIGHT EXCLUDING JOIN

RIGHT EXCLUDING JOIN возвращает только строки для каждой строки справа, не имеющей соответствия слева (эта строка дополняется значениями NULL влево).

# Пример RIGHT OUTER EXCLUDING JOIN

SELECT list,of,fields 
FROM table_A AS A
RIGHT OUTER JOIN table_B AS B
ON A.Key = B.Key
WHERE A.Key IS NULL

OUTER JOIN, FULL JOIN или FULL OUTER JOIN

FULL JOIN, OUTER JOIN или FULL OUTER JOIN возвращают все соединенные строки плюс все строки слева, не имеющие соответствия справа (дополненные значениями NULL вправо), плюс все строки справа, не имеющие соответствия слева (дополненные значениями NULL влево).

# Пример FULL OUTER JOIN

SELECT list,of,fields 
FROM table_A AS A
FULL OUTER JOIN table_B AS B
ON A.Key = B.Key

OUTER EXCLUDING JOIN, FULL EXCLUDING JOIN или FULL OUTER EXCLUDING JOIN

OUTER EXCLUDING JOIN, или FULL EXCLUDING JOIN, или FULL OUTER EXCLUDING JOIN возвращает все строки слева, не имеющие соответствия справа (дополненные значениями NULL вправо), плюс все строки справа, не имеющие соответствия слева (дополненные значениями NULL влево), исключая все строки, имеющие соответствия справа и слева.

# Пример FULL OUTER EXCLUDING JOIN

SELECT list,of,fields 
FROM table_A AS A
FULL OUTER JOIN table_B AS B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

Вы узнали основы работы с СУБД PostgreSQL и ваших знаний будет достаточно, чтобы пройти задание.

Полезные ссылки:

• Установка postgresql на Ubuntu-server.
• Тренажер для postgresql — различные варианты работы с данными.
• Как работает реляционная БД.
• SELECT
• UPDATE
• INSERT
• DELETE
• CREATE
• ALTER
• DROP
• GRANT
• REVOKE
• COMMIT
• CONSTRAINT

Инструкции к заданию

Представьте, что вам поручили разработку новой базы данных для компании REBRAIN. При этом узнали, что в техническом задании основным пунктом указана совместимость с СУБД PostgreSQL. Указанное имя базы данных: "rebrain_courses_db".

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

Вы приступили к заданию и поняли, что необходимо создать следующие таблицы:

Таблица №1. - users

CREATE TABLE users(
    user_id SERIAL PRIMARY KEY NOT NULL,             -- Primary Key
    username varchar(50) NOT NULL,                   -- Имя пользователя
    email varchar(50) NOT NULL,                      -- Электронная почта
    mobile_phone varchar(12) NOT NULL,               -- Номер телефона
    firstname TEXT NOT NULL,                         -- Имя
    lastname TEXT NOT NULL,                          -- Фамилия
    city  TEXT,                                      -- Название города
    is_curator boolean NOT NULL,                     -- Является ли пользователь куратором
    record_date timestamp NOT NULL DEFAULT now()     -- Время создания записи о пользователе
    );

Исходные данные для Таблицы №1.

Таблица №2. - courses

CREATE TABLE courses(
    course_id SERIAL PRIMARY KEY NOT NULL,  -- Primary Key
    coursename varchar(50) NOT NULL,        -- Название практикума
    tasks_count INT NOT NULL,               -- Количество заданий в практикуме
    price INT NOT NULL                      -- Цена практикума
    );

Исходные данные для Таблицы №2.

Таблица №3. - users__courses

CREATE TABLE users__courses(
    id SERIAL PRIMARY KEY NOT NULL,     -- Primary Key
    user_id INT NOT NULL,               -- Foreign Key to table users 
    course_id INT NOT NULL,             -- Foreign Key to table courses 
    CONSTRAINT fk_user_id
        FOREIGN KEY (user_id) 
            REFERENCES users(user_id),
    CONSTRAINT fk_course_id
        FOREIGN KEY (course_id) 
            REFERENCES courses(price)
    );

Исходные данные для Таблицы №3.

Пока на этом все, продолжим доделывать базу данных "rebrain_courses_db" в следующем задании. А пока можете приступать к текущему заданию.

Правила выполнения задания:

1. Время создания окружения занимает до 5 минут.
2. После нажатия кнопки «Начать выполнение» для вас будет создано окружение для выполнения задания, состоящее из одной или более виртуальнах машин с Ubuntu Linux. К ним будут выданы IP, логин и пароль для доступа по SSH, а также при необходимости — дополнительных ресурсов.
3. Также, если необходимо, вам могут быть выданы переменные, которые в задании указаны в фигурных скобках, — их надо будет подставить при выполнении задания (например, ${base_domain}).
4. После выполнения всех пунктов задания нажмите кнопку «Проверить выполнение», и в течение 3-5 минут скрипт проверит выполнение всех условий и выставит вам оценку.
5. В случае, если вы что-то забыли, можно исправить ошибку и отправить на проверку повторно (нажав кнопку «Проверить выполнение»).
6. После получения удовлетворительной оценки нажмите кнопку «Завершить задание», чтобы созданное окружение уничтожилось и вы могли приступить к следующему заданию.
7. Если у вас закончилось время (истек таймер) — окружение будет автоматически уничтожено и вам придется начать выполнение заново.
8. Также, если вы успешно сдали задание, но у вас остались вопросы — вы всегда сможете задать их куратору после проверки (используя кнопку «Задать вопрос куратору») или в чате практикума в любое удобное для вас время. Обращаем внимание — кураторы проверяют вопросы в течение 24 часов.

Задание:

1. Установите postgresql v13 на VM c OS Ubuntu 20.04.
2. Добавьте в PostgreSQL пользователя root c правами SUPERUSER.
3. Создайте базу данных "rebrain_courses_db".
4. Для работы с базой данных создайте пользователя "rebrain_admin".
5. Выдайте все права пользователю "rebrain_admin" на базу данных "rebrain_courses_db".
6. C помощью утилиты psql, подключитесь к базе данных "rebrain_courses_db".
7. Руководствуясь инструкцией к заданию, убедитесь, что таблица №1 и таблица №3 верно связаны по Foreign Key. Исправьте ошибку, если она есть.
8. Руководствуясь инструкцией к заданию, убедитесь, что таблица №2 и таблица №3 верно связаны по Foreign Key. Исправьте ошибку, если она есть.
9. Руководствуясь инструкцией к заданию, создайте необходимые таблицы, внесите в них исходные данные. Помните, что каждой таблице присвоен свой виртуальный номер, чтобы в дальнейшем проще понимать к какой таблице нужно обращаться.

• Таблица №1. - users
• Таблица №2. - courses
• Таблица №3. - users__courses Не обращайте внимания в эталонных данных на текущее значение поля record_date в таблице №1, у вас автоматически проставится ваша текущая дата в формате timestamp на момент занесения.

10. Внесите информацию о новом пользователе в таблицу №1:

    username	email	mobile_phone	firstname	lastname	city	is_curator
    vladon	Vladislav.Pirushin@gmail.com	+79817937545	Vladislav	Pirushin	NULL	false

11. Внесите информацию о новом курсе "Postgresql" в таблицу №2

12. Внесите в таблицу №3 данные о том, что пользователь c номером мобильного телефона "+79991916526" купил практикум "Devops".
13. Получите все данные из таблицы №2 c информацией о курсах, результат сохраните в файл /tmp/answers/table2_courses_data. Для сохранения результата запроса в файл используйте запрос "COPY (SELECT..) TO /path/to/file CSV HEADER;", psql метакоманду "\o<\b> /path/to/file SELECT ... " или OS команду "psql -o /path/to/file". Проверяйте в выведенных данных наличие заголовков полей (это важно для успешного прохождения автопроверок).
14. Получите из таблицы №1 список имен пользователей (username) и их мобильных номеров (mobile_phone), результат сохраните в файл /tmp/answers/table1_usernames_and_phones.
15. Удалите все данные из таблицы №1, связанные с именем пользователя "yodajedi".
16. Обновите данные цены практикума в таблице №2 для практикума "Postgresql". Новая цена: 10000 тыс. руб.
17. Обновите данные пользователя "Vladislav Pirushin" в таблице №1 указав, что он теперь является куратором.
18. Используя LEFT OUTER JOIN получите всю информацию (SELECT *) из таблицы №2 и таблицы №3, результат сохраните в файл /tmp/answers/LEFT_OUTER_JOIN в формате CSV с заголовками.
19. Используя RIGHT OUTER JOIN получите всю информацию (SELECT *) из таблицы №1 и таблицы №3, результат сохраните в файл /tmp/answers/RIGHT_OUTER_JOIN в формате CSV с заголовками.
20. Сделайте бэкап базы данных командой: pg_dump -U root rebrain_courses_db > rebrain_courses_db.sql.bqp
21. Сохраните файл бэкапа базы данных rebrain_courses_db.sql.bqp к себе на компьютер для выполнения следующих заданий.
22. Если уверены, что все выполнили правильно, оправляйте задание на проверку.