Мастер-класс по PostgreSQL

Что потребуется перед началом

1. Установленный PostgreSQL.
2. Установленный pgAdmin.

Для выполнения задания вам предоставляется доступ к развернутому экземпляру PostgreSQL и pgAdmin на виртуальной машине.

Вы можете самостоятельно поднят PostgreSQL и pgAdmin с помощью Docker Compose демонстрационный пример. Для этого, скачайте проект и выполните docker compose up -d.

Часть 1. Создание таблиц

Создание таблиц с помощью SQL

Материал основан на статье Introduction to the PostgreSQL tutorial |SQLShack

В этом руководстве мы предполагаем, что вы уже подключены к pgAdmin. Если нет, то ознакомьтесь в Moodle как подключиться в pgAdmin.

Сначала, чтобы создать запрос, перейдите в Databases>postgres>Schemas,, щелкните правой кнопкой мыши и выберите Query Tool:

Следующий запрос создает таблицу с именем customers.

CREATE TABLE customers (
  id integer PRIMARY KEY,
  Firstname VARCHAR (50) NOT NULL,
  Lastname VARCHAR (50) NOT NULL,
  email VARCHAR (255) NOT NULL,
  birthday DATE NOT NULL
);

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

Также у нас есть varchar, который используется для хранения символов. Столбцы Firstname, Lastname и email используют этот тип данных для строковых символов. Используемое ограничение составляет 50 символов для имени и фамилии, а адрес электронной почты поддерживает 255 символов.

Наконец, birthday столбец содержит даты. Он содержит в день рождения.

Для получения дополнительной информации о типах данных PostgreSQL перейдите по ссылке.

Создание таблиц с помощью pgAdmin

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

Сначала зайдите в Browser, а затем перейдите в Databases>postgres>Schemas>public>Table, щелкните правой кнопкой мыши и выберите опцию Create>Table:

На вкладке General введите название таблицы, а затем перейдите к Columns.

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

Имя и фамилия используют тип данных, изменяющийся на символьный, и длину, равную 50.

Наконец, нажмите кнопку Save.

Часть 2. Вставка данных в таблицу

Сначала, чтобы вставить данные в таблицу, мы будем использовать инструкцию insert . Следующие предложения позволят вставить 1 строку в ранее созданную таблицу customer.

INSERT INTO customers
VALUES (1, 'Jane', 'Fonda', 'jfonda@gmail.com', '1937-12-21');

Для вставки данных используется команда INSERT INTO, за которой следует название таблицы. Кроме того, вы используете слово VALUES и в круглых скобках пишете цифры без кавычек, а затем имя, фамилию и адрес электронной почты, используя кавычки для строк, а затем дату в формате ГГГГ-ММ-ДД.

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

INSERT INTO customers
VALUES (
    2,
    'Raquel',
    'Welch',
    'rwelch@gmail.com',
    '1940-09-05'
  ),
  (
    3,
    'Sophia',
    'Loren',
    'sloren@gmail.com',
    '1934-09-20'
  ),
  (
    4,
    'Elizabeth',
    'Taylor',
    'etaylor@gmail.com',
    '1932-02-27'
  );

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

Часть 3. Редактирование таблиц

Редактирование таблиц с помощью pgAdmin

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

Перейдите на вкладку Columns, и вы сможете добавлять столбцы с помощью значка +, редактировать столбцы с помощью карандаша и удалять столбцы с помощью значка корзины для удаления. Наконец, нажмите кнопку Save.

Редактирование таблиц с помощью SQL

В качестве альтернативы вы можете использовать SQL для изменения таблиц.

Оператор ALTER используется для изменения таблицы. Давайте рассмотрим несколько примеров.

Сначала мы добавим столбец с именем age типа integer в таблицу клиента.

ALTER TABLE public.customers
ADD COLUMN age integer;

При желании мы можем использовать условие IF EXISTS для выполнения предложения, только если таблица существует.

ALTER TABLE IF EXISTS public.customers
ADD COLUMN age integer;

Если этот запрос будет выполнен дважды, то на второй раз будет ошибка:

ERROR:  column "age" of relation "customers" already exists 

SQL state: 42701

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

ALTER TABLE customers DROP COLUMN age

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

ALTER TABLE IF EXISTS public.customers DROP COLUMN IF EXISTS age;

Для получения дополнительной информации об ИЗМЕНЕНИИ предложения вы можете прочитать по ссылке.

Часть 4. Изменение данных

Обновление данных с помощью pgAdmin

В pgAdmin щелкните правой кнопкой мыши таблицу и выберите View/Edit data. Вы можете редактировать все строки или работать с первой и последней строками, или использовать фильтры. Если строк не так много, опция all-rows подходит. Если в таблице много строк, возможно, вам придется поработать с другими параметрами.

Дважды щелкните ячейку, которую вы хотите изменить.

Введите новое значение для ячейки и нажмите OK.

Есть возможность сохранить изменения.

Удаление строк с помощью pgAdmin

В pgAdmin щелкните правой кнопкой мыши таблицу и выберите View/Edit data>All rows.

Выберите строку или ряды для удаления и нажмите значок Delete, а затем сохраните изменения.

Как обновить данные с помощью SQL

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

В следующем примере показано, как заменить значение Sophia на Sofia в столбце firstname таблицы customers.

UPDATE customers
SET firstname = 'Sofia'
WHERE firstname = 'Sophia';

Как удалить данные с помощью SQL

Теперь мы покажем, как удалить данные. В следующем примере будет удалена строка таблицы customers, идентификатор которой равен 2.

DELETE FROM customers
WHERE id=2;

Часть 5. Запросы с JOIN

Материал основан на статье PostgreSQL Join overview with examples | SQLShack.

Соединения используются для получения информации из 2 или более таблиц. Существует несколько типов JOINS, таких как INNER JOIN, FULL OUTER JOIN, LEFT OUTER JOIN и RIGHT OUTER JOIN.

Выполним SQL-запросы с помощью pgAdmin и создадим две простые таблицы, как показано ниже:

CREATE TABLE E1 (
	id int primary key,
	firstname varchar(50)
);

CREATE TABLE E2 (
	id int primary key,
	firstname varchar(50)
);

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

INSERT INTO E1
VALUES (1, 'Gauri'),
  (2, 'Sid'),
  (3, 'Rahul'),
  (4, 'Gautam');
INSERT INTO E2
VALUES (1, 'Sid'),
  (2, 'Gauri'),
  (3, 'Ishita'),
  (4, 'Prakriti');

Первый тип соединений, который мы рассмотрим, известен как INNER JOIN. Это наиболее часто используемое соединение, обычно по сравнению с другими типами соединений PostgreSQL. Предположим, что мы намерены найти значения из обеих таблиц, имена которых совпадают, мы можем использовать INNER JOIN, как показано ниже. Здесь мы получили только две из четырех записей в обеих таблицах, поскольку в обеих таблицах совпадают только два имени. Этот набор данных представляет собой пересечение данных, которые являются общими в обеих таблицах.

SELECT *
FROM E1
  INNER JOIN E2 on E1.Firstname = E2.Firstname

Точной противоположностью этому соединению является соединение PostgreSQL, известное как полное внешнее соединение, которое представляет собой объединение всех данных из двух таблиц. Совпадающие записи возвращаются так же, как и записи, которые не совпадают, также возвращаются. Записи, которые не совпали, будут иметь идентификатор со значением null из таблицы, к которой присоединена запись, поскольку это запись, которая не имеет совпадения в другой таблице, как показано ниже.

SELECT *
FROM E1
  FULL OUTER JOIN E2 on E1.Firstname = E2.Firstname

Теперь, когда мы разобрались с двумя соединениями PostgreSQL – INNER JOIN и FULL OUTER JOIN, пришло время изучить варианты OUTER JOIN PostgreSQL – LEFT OUTER JOIN и RIGHT OUTER JOIN.

Допустим, мы намерены извлечь все записи из таблицы A, но из таблицы B мы намерены извлекать только записи, соответствующие значениям в таблице A. Рассмотрим таблицу A как LEFT таблицу, а таблицу B - как правую таблицу. Запрос для извлечения записей, соответствующих вышеупомянутым критериям, будет выглядеть так, как показано ниже. Записи, где поле id равно null, указывают на записи, которые не соответствовали критериям объединения, но из-за характера соединения в LEFT OUTER JOIN эти записи все равно были включены в том виде, в каком они находятся в таблице A, которая в данном случае является LEFT таблицей.

SELECT *
FROM E1
  LEFT OUTER JOIN E2 on E1.Firstname = E2.Firstname

Если вышеупомянутый сценарий должен был быть отменен, когда мы хотели получить все записи из правой таблицы, но только совпадающие записи из левой таблицы, мы можем использовать RIGHT OUTER JOIN соединения, как показано ниже. Если мы сравним приведенную ниже таблицу с приведенной выше таблицей, то на этот раз были извлечены все записи из таблицы B, а также те, которые совпадали с таблицей A. А те, которые не совпали, имеют нулевое значение для поля id из таблицы A.

SELECT *
FROM E1
  RIGHT OUTER JOIN E2 on E1.Firstname = E2.Firstname

Теперь мы рассмотрели различные типы соединений PostgreSQL, такие как INNER JOIN,, FULL OUTER JOIN, LEFT JOIN и RIGHT JOIN. Все эти объединения имели одну общую черту, у всех них был критерий объединения для сопоставления атрибутов из двух таблиц. Допустим, у нас есть сценарий, в котором мы намерены найти записи из обеих таблиц, которые не имеют совпадений в другой таблице, т.е. уникальные записи из обеих таблиц. В этом случае мы все еще можем использовать FULL OUTER JOIN, поскольку оно выбрало бы все записи из обеих таблиц, но с условием фильтрации, чтобы выбрать только те записи, которые не совпали. Это можно сделать, добавив в запрос:

SELECT *
FROM E1
  FULL OUTER JOIN E2 on E1.Firstname = E2.Firstname
WHERE E1.ID IS NULL
  OR E2.ID IS NULL

Одним из особых типов соединений PostgreSQL является CROSS JOIN. Допустим, мы хотим создать декартово произведение выбранных атрибутов/записей из двух таблиц, мы можем использовать перекрестное соединение. Декартово произведение 4 значений из одной таблицы на четыре значения из другой таблицы составило бы 16 записей. Здесь значение означает запись с одним полем. По мере того, как мы добавляем больше полей, это будет увеличиваться в несколько раз. Ниже показан пример, в котором мы выбираем одно поле из обеих таблиц и создаем его декартово произведение, используя перекрестное соединение PostgreSQL.

SELECT E1.firstname,
  E2.firstname
FROM E1
  CROSS JOIN E2

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

Часть 6. Работа с представлениями

Создание представлений с помощью pgAdmin

Представление в Postgres - это виртуальная таблица, в которой хранится запрос к одной или нескольким таблицам. В pgAdmin перейдите в базу данных и найдите представления. Щелкните правой кнопкой мыши и выберите Create>View.

В General вкладке писать имя представления и перейти к Code закладки.

На вкладке Codeнапишите следующий запрос:

SELECT lastname
FROM customers

Наконец, нажмите Save.

Просмотр и редактирование данных в представлениях с помощью pgAdmin

Вы можете редактировать или просматривать данные в представлениях PostgreSQL таким же образом, как и в таблицах. Щелкните правой кнопкой мыши представление и выберите View/Edit Data>All rows.

Редактирование представлений с помощью pgAdmin

Чтобы изменить представление в PostgreSQL, щелкните правой кнопкой мыши на представлении для изменения и выберите Properties.

Перейдите на вкладку Code, измените запрос в соответствии с вашими потребностями и нажмите Save.

Создание и редактирование представлений с помощью SQL

Чтобы создать представление в Postgres с использованием SQL, вам нужно использовать предложение create . В следующем примере создается представление с именем v_customers и отображается фамилия таблицы customers.

CREATE view v_customers AS
SELECT lastname
FROM customers;

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

В следующем примере будет изменено существующее представление с именем v_customer.

CREATE OR REPLACE VIEW v_customers AS
SELECT lastname,email
FROM customers;

Часть 7. Создание запросов к базе данных

Развертывание тестовой базы данных

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

Сначала создадим тестовую базу данных. Для этого с помощью pgAdmin создадим базу данных. Щелкните правой кнопкой мыши по Databases и выберите Create>Database:

Далее в окне создания базы данных укажите имя базы данных:

Нажмите кнопку Save.

Далее откройте Query Tool базы данных:

В папке репозитория ChinookDatabase/DataSources имеется скрипт Chinook_PostgreSql.sql, содержимое которого нужно скопировать в буфер:

Если не удалось открыть этот файл в GitHub, то данный файл содержится в материалах к практическому занятию в Moodle.

Из содержимого буфера вам необходимо вставить в Query Tool созданной нами ранее базы данных. ВНИМАТЕЛЬНО ПРОВЕРЬТЕ, что Query Tool открыт для нужной нам базы данных. После вставки нажмите кнопку Execute:

Поздравляем, вы создали тестовую базу и наполнили ее данными.

Описание тестовой базы данных

Раздел основан на материалах SQLite Sample Database | SQLITE Tutorial и Chinook Database | Business Analytics Course.

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

В базе данных образцов Chinook 11 таблиц:

• Employee - хранятся данные о сотрудниках, такие как идентификатор сотрудника, фамилия, имя и т. д. Он также имеет поле с именем ReportsTo, чтобы указать, кто кому отчитывается.
• Customer - хранит данные клиентов.
• Invoice и InvoiceLine в этих двух таблицах хранятся данные счета-фактуры. В таблице Invoice хранятся данные заголовка счета-фактуры, а в таблице InvoiceLine - хранятся данные позиций счета-фактуры.
• Artist - хранятся данные исполнителей. Это простая таблица, которая содержит только идентификатор и имя исполнителя.
• Album - хранятся данные о списке дорожек. Каждый альбом принадлежит одному исполнителю. Тем не менее у одного исполнителя может быть несколько альбомов.
• MediaType - хранятся типы носителей, такие как аудиофайлы MPEG и аудиофайлы AAC.
• Genre - жанры музыки такие как рок, джаз, метал и т. д.
• Track - данные песен. Каждый трек принадлежит одному альбому.
• Playlist и PlaylistTrack в таблице списков воспроизведения хранятся данные о списках воспроизведения. Каждый список воспроизведения содержит список треков. Каждый трек может принадлежать нескольким спискам воспроизведения. Связь между таблицей списков воспроизведения и таблицей дорожек - "многие ко многим". Таблица PlaylistTrack используется для отражения этой взаимосвязи.

Составление произвольных запросов к базе данных

Раздел основан на материалах PostgreSQL tutorial | ChrisT-CC/CI-Postgres-tutorial, A More Useful Port of the Chinook Database to Postgresql | John Atten и Chinook Database | Business Analytics Course.

Простые произвольные запросы

Как только будет похоже, что у вас все настроено, вы можете начать составлять запросы к базе данных! Сначала мы могли бы просмотреть все данные из таблицы Invoice:

SELECT * FROM "Invoice";

Теперь посмотрите, что находится в таблице Employee.

SELECT * FROM "Employee";

Выберите все данные из таблицы "Исполнитель"

SELECT * FROM "Artist";

Выберите только столбец "Имя" из таблицы "Исполнитель"

SELECT "Name" FROM "Artist";

Выберите только "Queen" из таблицы "Исполнитель":

SELECT * FROM "Artist" WHERE "Name" = 'Queen';

Выберите только "Queen" из таблицы "Artist", но используя "ArtistID" из '51':

SELECT * FROM "Artist" WHERE "ArtistId" = 51;

Выберите все альбомы из таблицы "Album", используя "ArtistID", равный '51':

SELECT * FROM "Album" WHERE "ArtistId" = 51;

Выберите все треки из таблицы "Дорожки", используя "Композитор" "Queen":

SELECT * FROM "Track" WHERE "Composer" = 'Queen';

Самый быстрый способ просмотреть список заголовков столбцов в таблице - это просто вернуть false , что намеренно дает нам нулевые результаты.

SELECT * FROM "Artist" WHERE false;

Выберите некоторые конкретные записи из таблицы альбомов базы данных Chinook:

SELECT * FROM "Album" WHERE "ArtistId" = 2

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

Выберите записи из таблицы альбомов Chinook DB, используя JOIN с исполнителями:

SELECT 
  ar."Name", 
  al."Title"
FROM 
  "Album" AS al, 
  "Artist" AS ar
WHERE 
  al."ArtistId" = ar."ArtistId";

Попробуем более сложные запросы.

В каких странах больше всего счетов-фактур?

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

SELECT "BillingCountry",
  COUNT("InvoiceId")
FROM "Invoice"
GROUP BY 1
ORDER BY 2 DESC

В каком городе лучшие клиенты?

Мы хотели бы провести рекламный музыкальный фестиваль в городе, в котором мы заработали больше всего денег. Напишите запрос, который вернет 1 город с наибольшей суммой выставленных счетов. Верните как название города, так и сумму всех счетов-фактур.

SELECT "BillingCity",
  SUM("Total")
FROM "Invoice"
GROUP BY 1
ORDER BY 2 DESC

Кто лучший клиент?

Клиент, потративший больше всего денег, будет объявлен лучшим клиентом. Создайте запрос, который вернет имя человека, потратившего больше всего денег. Найти решение можно, связав следующие три таблицы: Invoice, InvoiceLine и Customer для получения этой информации, но вы, вероятно, можете сделать это с меньшим количеством!

SELECT C."CustomerId",
  SUM(I."Total")
FROM "Customer" C
  JOIN "Invoice" I ON C."CustomerId" = I."CustomerId"
GROUP BY 1
ORDER BY 2 DESC

Примеры других запросов вы можете найти в лекции "3. Основы SQL". Также можно посмотреть как такие запросы выглядят для SQLite и используются в python в этой статье.

Часть 8. Создание пользователя

Данный материал изучается самостоятельно по статье Как создать пользователя в PostgreSQL | Академия Selectel.

Часть 9. Задание для самостоятельного выполнения

В качестве самостоятельного задания для совершенствования в составлении запросов для PostgreSQL необходимо выполнить:

• Придумать свою базу данных (2-3 таблицы). Примеры возможных тем:
  • Меню ресторана;
  • Продуктовый склад;
  • Служба доставки;
  • Корзина покупателя.
• Составить скрипт создания базы данных, который создаст все таблицы.
• Составить скрипт, который наполнит данными созданную вами базу данных (по 4-5 записей на таблицу).
• Подготовить произвольные запросы к базе данных (7-8 запросов).

Результат выполнения данного задания необходимо вставить в отчет.

Для дальнейшего изучения

• Learn PostgreSQL | TutorialsTeacher
• psql command line tutorial and cheat sheet
• PostgreSQL Tutorial | TutorialsPoint
• PostgreSQL Query: Introduction, Explanation, and 50 Examples | EDB
• PostgreSQL Administration | POSTRESQL Tutorial
• PostgreSQL. Основы языка SQL