PostgreSQL: Связи и соединения (Joins)

Введение

В этой лабораторной работе вы изучите связи и объединения (joins) в PostgreSQL. Вы узнаете, как создавать таблицы с ограничениями внешнего ключа (foreign key constraints) для обеспечения целостности данных.

Вы начнете с создания двух таблиц: customers и orders, и установите между ними связь по внешнему ключу. Затем вы вставите данные в эти таблицы, убедившись, что данные соответствуют определенной связи. Наконец, вы узнаете, как извлекать данные с помощью INNER JOIN и сравните результаты операций LEFT, RIGHT и FULL OUTER JOIN, чтобы понять, как они обрабатывают различные отношения между данными.

Skills Graph

Создание таблиц с ограничениями внешнего ключа (Foreign Key Constraints)

В этом шаге вы создадите две таблицы: customers (клиенты) и orders (заказы), и установите между ними ограничение внешнего ключа (foreign key constraint). Это ограничение гарантирует поддержание связи между таблицами, предотвращая ввод недействительных данных.

Понимание внешних ключей (Foreign Keys)

Внешний ключ (foreign key) — это столбец в одной таблице, который ссылается на первичный ключ (primary key) другой таблицы. Он устанавливает связь между двумя таблицами. Таблица, содержащая внешний ключ, называется "дочерней" (child) таблицей, а таблица, содержащая первичный ключ, называется "родительской" (parent) таблицей.

Шаг 1: Подключение к PostgreSQL

Откройте терминал в вашей LabEx VM (виртуальной машине). Подключитесь к базе данных PostgreSQL, используя команду psql:

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL (postgres=#).

Шаг 2: Создание таблицы customers

Создайте таблицу customers со следующими столбцами:

• customer_id: Уникальный идентификатор для каждого клиента (первичный ключ).
• first_name: Имя клиента.
• last_name: Фамилия клиента.
• email: Адрес электронной почты клиента (должен быть уникальным).

Выполните следующую SQL-команду в оболочке psql:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

Эта команда создает таблицу customers. Ключевое слово SERIAL автоматически генерирует последовательность чисел для customer_id, делая его автоматически увеличивающимся (auto-incrementing). PRIMARY KEY определяет customer_id как первичный ключ. NOT NULL гарантирует, что столбцы first_name и last_name не могут быть пустыми, а UNIQUE гарантирует, что каждый адрес электронной почты является уникальным.

Шаг 3: Создание таблицы orders с внешним ключом

Создайте таблицу orders со следующими столбцами:

• order_id: Уникальный идентификатор для каждого заказа (первичный ключ).
• customer_id: Идентификатор клиента, разместившего заказ (внешний ключ, ссылающийся на customers).
• order_date: Дата размещения заказа.
• total_amount: Общая сумма заказа.

Выполните следующую SQL-команду в оболочке psql:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Эта команда создает таблицу orders. Столбец customer_id определен как внешний ключ с использованием ключевого слова REFERENCES. REFERENCES customers(customer_id) указывает, что столбец customer_id в таблице orders ссылается на столбец customer_id в таблице customers. Это устанавливает связь по внешнему ключу.

Шаг 4: Проверка создания таблиц

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

\dt

Вы должны увидеть таблицы customers и orders в списке.

Шаг 5: Описание таблиц

Чтобы увидеть структуру таблиц, используйте команду \d, за которой следует имя таблицы. Например, чтобы описать таблицу customers, выполните:

\d customers

Это покажет вам столбцы, типы данных и ограничения, определенные для таблицы customers. Аналогично, вы можете описать таблицу orders:

\d orders

Это покажет вам ограничение внешнего ключа на столбце customer_id.

Теперь вы успешно создали две таблицы с ограничением внешнего ключа.

Вставка данных и обеспечение ссылочной целостности (Referential Integrity)

В этом шаге вы вставите данные в таблицы customers (клиенты) и orders (заказы), обеспечивая поддержание ссылочной целостности (referential integrity). Это означает, что вы не можете добавить заказ для клиента, которого не существует в таблице customers.

Понимание ссылочной целостности (Referential Integrity)

Ссылочная целостность (referential integrity) гарантирует, что связи между таблицами остаются согласованными. В нашем случае это означает, что customer_id в таблице orders должен существовать в таблице customers.

Шаг 1: Вставка данных в таблицу customers

Вставьте данные в таблицу customers, используя следующую SQL-команду в оболочке psql:

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]'),
('David', 'Lee', '[email protected]');

Эта команда добавляет трех клиентов в таблицу customers. customer_id генерируется автоматически.

Шаг 2: Проверка вставки данных в таблицу customers

Убедитесь, что данные были вставлены правильно, запросив таблицу customers:

SELECT * FROM customers;

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

 customer_id | first_name | last_name |         email
-------------+------------+-----------+------------------------
           1 | John       | Doe       | [email protected]
           2 | Jane       | Smith     | [email protected]
           3 | David      | Lee       | [email protected]
(3 rows)

Шаг 3: Вставка данных в таблицу orders

Вставьте данные в таблицу orders, ссылаясь на значения customer_id из таблицы customers:

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-11-01', 100.00),
(2, '2023-11-05', 250.50),
(1, '2023-11-10', 75.25),
(3, '2023-11-15', 120.00);

Эта команда добавляет четыре заказа в таблицу orders. Каждый заказ связан с customer_id из таблицы customers.

Шаг 4: Проверка вставки данных в таблицу orders

Убедитесь, что данные были вставлены правильно, запросив таблицу orders:

SELECT * FROM orders;

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

 order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
        1 |           1 | 2023-11-01 |       100.00
        2 |           2 | 2023-11-05 |       250.50
        3 |           1 | 2023-11-10 |        75.25
        4 |           3 | 2023-11-15 |       120.00
(4 rows)

Шаг 5: Попытка вставить недействительные данные (демонстрация ссылочной целостности)

Чтобы продемонстрировать ограничение ссылочной целостности, попробуйте вставить заказ с customer_id, который не существует в таблице customers:

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);

Вы должны увидеть сообщение об ошибке, подобное этому:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(4) is not present in table "customers".

Это сообщение об ошибке подтверждает, что ограничение внешнего ключа работает. База данных предотвращает вставку заказа, потому что customer_id 4 не существует в таблице customers.

Теперь вы успешно вставили данные в таблицы customers и orders, обеспечив ссылочную целостность.

Запрос данных с использованием INNER JOIN

В этом шаге вы узнаете, как извлекать данные из нескольких таблиц с помощью предложения INNER JOIN в PostgreSQL. INNER JOIN объединяет строки из двух или более таблиц на основе связанного столбца.

Понимание INNER JOIN

INNER JOIN возвращает только те строки, в которых есть соответствие в обеих объединяемых таблицах. Если соответствия нет, строка исключается из результата.

Шаг 1: Подключение к PostgreSQL

Убедитесь, что вы подключены к базе данных PostgreSQL с помощью команды psql:

sudo -u postgres psql

Шаг 2: Выполнение запроса INNER JOIN

Выполните следующий SQL-запрос в оболочке psql:

SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Давайте разберем этот запрос:

• SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount: Это указывает столбцы, которые вы хотите извлечь из таблиц orders и customers. Использование имени таблицы в качестве префикса (например, orders.order_id) уточняет, из какой таблицы происходит каждый столбец.
• FROM orders: Это указывает первую таблицу, из которой вы запрашиваете данные.
• INNER JOIN customers ON orders.customer_id = customers.customer_id: Это указывает вторую таблицу, с которой вы объединяетесь (customers), и условие объединения (orders.customer_id = customers.customer_id). Предложение ON указывает, что customer_id в таблице orders должен соответствовать customer_id в таблице customers, чтобы строки были включены.

Шаг 3: Анализ результатов

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

 order_id | first_name | order_date | total_amount
----------+------------+------------+--------------
        1 | John       | 2023-11-01 |       100.00
        3 | John       | 2023-11-10 |        75.25
        2 | Jane       | 2023-11-05 |       250.50
        4 | David      | 2023-11-15 |       120.00
(4 rows)

Запрос успешно объединил таблицы orders и customers на основе customer_id и извлек запрошенную информацию. Включены только заказы с соответствующим клиентом в таблице customers.

Шаг 4: Использование псевдонимов (Aliases) (необязательно)

Для более сложных запросов вы можете использовать псевдонимы (aliases), чтобы сделать запрос более читаемым. Предыдущий запрос можно переписать с использованием псевдонимов:

SELECT o.order_id, c.first_name, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

В этом запросе o является псевдонимом для orders, а c является псевдонимом для customers. Результат будет тем же, но запрос более лаконичен.

Теперь вы успешно запросили данные из нескольких таблиц с помощью предложения INNER JOIN.

Сравнение результатов LEFT, RIGHT и FULL OUTER JOIN

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

Понимание OUTER JOIN

• LEFT OUTER JOIN (или LEFT JOIN): Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет соответствия, для столбцов правой таблицы возвращаются значения NULL.
• RIGHT OUTER JOIN (или RIGHT JOIN): Возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если в левой таблице нет соответствия, для столбцов левой таблицы возвращаются значения NULL.
• FULL OUTER JOIN (или FULL JOIN): Возвращает все строки из обеих таблиц. Если в одной таблице нет соответствия, для столбцов другой таблицы возвращаются значения NULL.

Шаг 1: Подключение к PostgreSQL

Убедитесь, что вы подключены к базе данных PostgreSQL с помощью команды psql:

sudo -u postgres psql

Шаг 2: Вставка нового клиента без заказов

Вставьте нового клиента в таблицу customers, который не размещал никаких заказов:

INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', '[email protected]');

Шаг 3: Проверка нового клиента

Убедитесь, что новый клиент был добавлен в таблицу customers:

SELECT * FROM customers;

Вы должны увидеть Alice Brown в результатах с новым customer_id (вероятно, 4).

Шаг 4: Выполнение LEFT OUTER JOIN

Выполните следующий SQL-запрос для выполнения LEFT OUTER JOIN между таблицами customers и orders:

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Этот запрос возвращает всех клиентов вместе с любыми заказами, которые они разместили. Если клиент не размещал никаких заказов, столбцы order_id и order_date будут содержать значения NULL. Вывод должен выглядеть примерно так:

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

Обратите внимание, что Alice Brown включена, даже если она не размещала никаких заказов. Столбцы order_id и order_date имеют значение NULL для нее.

Шаг 5: Выполнение RIGHT OUTER JOIN

Выполните следующий SQL-запрос для выполнения RIGHT OUTER JOIN между таблицами customers и orders:

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id;

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

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 Jane       |        2 | 2023-11-05
 John       |        3 | 2023-11-10
 David      |        4 | 2023-11-15
(4 rows)

Шаг 6: Выполнение FULL OUTER JOIN

Выполните следующий SQL-запрос для выполнения FULL OUTER JOIN между таблицами customers и orders:

SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Этот запрос возвращает всех клиентов и все заказы. Если клиент не размещал никаких заказов, столбцы order_id и order_date будут содержать значения NULL. Если у заказа нет соответствующего клиента (что невозможно в нашей текущей настройке из-за ограничения внешнего ключа (foreign key constraint)), столбец first_name будет содержать значения NULL. Вывод должен выглядеть примерно так:

 first_name | order_id | order_date
------------+----------+------------
 John       |        1 | 2023-11-01
 John       |        3 | 2023-11-10
 Jane       |        2 | 2023-11-05
 David      |        4 | 2023-11-15
 Alice      |          |
(5 rows)

Обратите внимание, что Alice Brown включена со значениями NULL для order_id и order_date.

Шаг 7: Понимание различий

• LEFT OUTER JOIN включает все строки из таблицы customers, даже если нет соответствующих заказов.
• RIGHT OUTER JOIN включает все строки из таблицы orders. В нашем случае он ведет себя как INNER JOIN, потому что у всех заказов есть соответствующий клиент.
• FULL OUTER JOIN включает все строки из обеих таблиц.

Теперь вы изучили и сравнили результаты LEFT OUTER JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN в PostgreSQL.

Итог

В этой лабораторной работе вы узнали, как создавать таблицы с ограничениями внешнего ключа (foreign key constraints) в PostgreSQL для поддержания целостности данных. Вы создали таблицы customers и orders и установили связь между ними с помощью внешнего ключа. Затем вы вставили данные в эти таблицы, убедившись, что ограничение внешнего ключа соблюдается. Наконец, вы изучили различные типы операций JOIN (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN) для извлечения данных из связанных таблиц и понимания того, как они обрабатывают различные отношения между данными.