© 2025, Ахматов Максим Сергеевич 29 0
СДЕЛАЙТЕ СВОИ УРОКИ ЕЩЁ ЭФФЕКТИВНЕЕ, А ЖИЗНЬ СВОБОДНЕЕ
Благодаря готовым учебным материалам для работы в классе и дистанционно
Скидки до 50 % на комплекты
только до
Готовые ключевые этапы урока всегда будут у вас под рукой
Организационный момент
Проверка знаний
Объяснение материала
Закрепление изученного
Итоги урока
Был в сети 01.07.2025 15:42
Ахматов Максим Сергеевич
Преподаватель профессиональных дисциплин
3 247
18 475 
Управление и автоматизация БД Практическая работа №11
Категория:
Информатика
29.06.2025 19:04
Просмотр содержимого документа
«Управление и автоматизация БД Практическая работа №11»
Практическое занятие № 11
Соединение таблиц
Цель работы: получение навыков создания сложных запросов в среде MS SQL Server.
Краткие теоретические основания выполнения задания Неявное соединение таблицДля сведения данных из разных таблиц мы можем использовать стандартную команду SELECT. Допустим, у нас есть следующие таблицы, которые связаны между собой связями:
USE productsdb; CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0, Price MONEY NOT NULL
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL
);
Здесь таблицы Products и Customers связаны с таблицей Orders связью один ко многим. Таблица Orders в виде внешних ключей ProductId и CustomerId содержит ссылки на столбцы Id из соответственно таблиц Products и Customers. Также она хранит количество купленного товара (ProductCount) и и по какой цене он был куплен (Price). И кроме того, таблицы также хранит в виде столбца CreatedAt дату покупки.
Пусть эти таблицы будут содержать следующие данные:
INSERT INTO Products
VALUES ('iPhone 6', 'Apple', 2, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Mi 5X', 'Xiaomi', 2, 26000),
('OnePlus 5', 'OnePlus', 6, 38000)
INSERT INTO Customers VALUES ('Tom'), ('Bob'),('Sam')
INSERT INTO Orders
VALUES (
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'), (SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-11',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
), (
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'), (SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-13',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
), (
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'), (SELECT Id FROM Customers WHERE FirstName='Bob'),
'2017-07-11',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
)
Теперь соединим две таблицы Orders и Customers:
SELECT * FROM Orders, Customers
При такой выборке для каждая строка из таблицы Orders будет совмещаться с каждой строкой из таблицы Customers. То есть, получится перекрестное соединение. Например, в Orders три строки, а в Customers то же три строки, значит мы получим 3 * 3 = 9 строк:
То есть в данном случае мы получаем прямое (декартово) произведение двух групп. Но вряд ли это тот результат, который хотелось бы видеть. Тем более каждый заказ из Orders связан с конкретным покупателем из Customers, а не со всеми возможными покупателями.
Чтобы решить задачу, необходимо использовать выражение WHERE и фильтровать строки при условии, что поле CustomerId из Orders соответствует полю Id из Customers:
SELECT * FROM Orders, Customers
WHERE Orders.CustomerId = Customers.Id
Теперь объединим данные по трем таблицам Orders, Customers и Proucts. То есть получим все заказы и добавим информацию по клиенту и связанному товару:
SELECT Customers.FirstName, Products.ProductName, Orders.CreatedAt
FROM Orders, Customers, Products
WHERE Orders.CustomerId = Customers.Id AND Orders.ProductId=Products.Id
Поскольку надо соединить три таблицы, то применяются как минимум два условия. Ключевой таблицей остается Orders, из которой извлекаются все заказы, а затем к ней подсоединяется данные по клиенту по условию Orders.CustomerId = Customers.Id и данные по товару по условию Orders.ProductId=Products.Id
Поскольку в данном случае названия таблиц сильно увеличивают код, то мы его можем сократить за счет использования псевдонимов таблиц:
SELECT C.FirstName, P.ProductName, O.CreatedAt FROM Orders AS O, Customers AS C, Products AS P WHERE O.CustomerId = C.Id AND O.ProductId=P.Id
Если необходимо при использовании псевдонима выбрать все столбцы из определенной таблицы, то можно использовать звездочку:
SELECT C.FirstName, P.ProductName, O.*
FROM Orders AS O, Customers AS C, Products AS P
WHERE O.CustomerId = C.Id AND O.ProductId=P.Id
INNER JOINДля явного соединения данных из двух таблиц применяется оператор JOIN. Общий формальный синтаксис применения оператора INNER JOIN:
SELECT столбцы
FROM таблица1
[INNER] JOIN таблица2
ON условие1
[[INNER] JOIN таблица3
ON условие2]
После оператора JOIN идет название второй таблицы, из которой надо добавить данные в
выборку. Перед JOIN может использоваться необязательное ключевое слово INNER. Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.
Возьмем таблицы с данными из прошлой темы:
USE productsdb; CREATE TABLE Products (
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0, Price MONEY NOT NULL
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL
);
Используя JOIN, выберем все заказы и добавим к ним информацию о товарах:
SELECT Orders.CreatedAt, Orders.ProductCount, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например, "Orders.ProductCount".
Также используя псевдонимы, мы можем сократить код:
SELECT O.CreatedAt, O.ProductCount, P.ProductName
FROM Orders AS O
JOIN Products AS P
ON P.Id = O.ProductId
Подобным образом мы можем присоединять и другие таблицы. Например, добавим к заказу информацию о покупателе из таблицы Customers:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
JOIN Customers ON Customers.Id=Orders.CustomerId
Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId JOIN Customers ON Customers.Id=Orders.CustomerId WHERE Products.Price 45000
ORDER BY Customers.FirstName
Условия после ключевого слова ON могут быть более сложными по составу:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId AND
Products.Manufacturer='Apple'
JOIN Customers ON Customers.Id=Orders.CustomerId
ORDER BY Customers.FirstName
В данном случае выбираем все заказы на товары, производителем которых является Apple.
При использовании оператора JOIN следует учитывать, что процесс соединения таблиц может быть ресурсоемким, поэтому следует соединять только те таблицы, данные из которых
действительно необходимы. Чем больше таблиц соединяется, тем больше снижается производительность.
OUTER JOINMS SQL Server также поддерживает внешнее соединение или outer join. В отличие от inner join внешнее соединение возвращает все строки одной или двух таблиц, которые участвуют в соединении.
Outer Join имеет следующий формальный синтаксис:
SELECT столбцы
FROM таблица1
{LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1
[{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]...
Перед оператором JOIN указывается одно из ключевых слов LEFT, RIGHT или FULL, которые определяют тип соединения:
LEFT: выборка будет содержать все строки из первой или левой таблицы
RIGHT: выборка будет содержать все строки из второй или правой таблицы
FULL: выборка будет содержать все строки из обоих таблиц
Также перед оператором JOIN может указываться ключевое слово OUTER, но его применение необязательно. Далее после JOIN указывается присоединяемая таблица, а затем идет условие соединения.
Например, соединим таблицы Orders и Customers:
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders LEFT JOIN Customers
ON Orders.CustomerId = Customers.Id
Таблица Orders является первой или левой таблицей, а таблица Customers - правой таблицей.
Поэтому, так как здесь используется выборка по левой таблице, то вначале будут выбираться все строки из Orders, а затем к ним по условию Orders.CustomerId = Customers.Id будут добавляться связанные строки из Customers.
По вышеприведенному результату может показаться, что левостороннее соединение аналогично INNER Join, но это не так. Inner Join объединяет строки из дух таблиц при соответствии условию.
Если одна из таблиц содержит строки, которые не соответствуют этому условию, то данные строки не включаются в выходную выборку. Left Join выбирает все строки первой таблицы и затем присоединяет к ним строки правой таблицы. К примеру, возьмем таблицу Customers и добавим к покупателям информацию об их заказах:
-- INNER JOIN
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers JOIN Orders
ON Orders.CustomerId = Customers.Id
--LEFT JOIN
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers LEFT JOIN Orders
ON Orders.CustomerId = Customers.Id
Изменим в примере выше тип соединения на правостороннее:
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders RIGHT JOIN Customers
ON Orders.CustomerId = Customers.Id
Теперь будут выбираться все строки из Customers, а к ним уже будет присоединяться связанные по условию строки из таблицы Orders:
Поскольку один из покупателей из таблицы Customers не имеет связанных заказов из Orders, то соответствующие столбцы, которые берутся из Orders, будут иметь значение NULL.
Используем левостороннее соединение для добавления к заказам информации о пользователях и товарах:
SELECT Customers.FirstName, Orders.CreatedAt, Products.ProductName, Products.Manufacturer
FROM Orders
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
LEFT JOIN Products ON Orders.ProductId = Products.Id
И также можно применять более комплексные условия с фильтрацией и сортировкой. Например, выберем все заказы с информацией о клиентах и товарах по тем товарам, у которых цена меньше 45000, и отсортируем по дате заказа:
SELECT Customers.FirstName, Orders.CreatedAt, Products.ProductName, Products.Manufacturer
FROM Orders
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id LEFT JOIN Products ON Orders.ProductId = Products.Id WHERE Products.Price 45000
ORDER BY Orders.CreatedAt
Или выберем всех пользователей из Customers, у которых нет заказов в таблице Orders: SELECT FirstName FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL
Также можно комбинировать Inner Join и Outer Join:
SELECT Customers.FirstName, Orders.CreatedAt, Products.ProductName, Products.Manufacturer
FROM Orders
JOIN Products ON Orders.ProductId = Products.Id AND Products.Price 45000
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
ORDER BY Orders.CreatedAt
Вначале по условию к таблице Orders через Inner Join присоединяется связанная информация из Products, затем через Outer Join добавляется информация из таблицы Customers.
Cross JoinCross Join или перекрестное соединение создает набор строк, где каждая строка из одной таблицы соединяется с каждой строкой из второй таблицы. Например, соединим таблицу заказов Orders и таблицу покупателей Customers:
SELECT * FROM Orders CROSS JOIN Customers
Если в таблице Orders 3 строки, а в таблице Customers то же три строки, то в результате
перекрестного соединения создается 3 * 3 = 9 строк вне зависимости, связаны ли данные строки или нет.
При неявном перекрестном соединении можно опустить оператор CROSS JOIN и просто перечислить все получаемые таблицы:
SELECT * FROM Orders, Customers
Группировка в соединенияхВ выражениях INNER/OUTER JOIN также можно использовать группировку. Например, выведем для каждого пользователя количество заказов, которые он сделал:
SELECT FirstName, COUNT(Orders.Id) FROM Customers JOIN Orders
ON Orders.CustomerId = Customers.Id
GROUP BY Customers.Id, Customers.FirstName;
Критерием группировки выступают Id и имя покупателя. Выражение SELECT выбирает имя покупателя и количество заказов, используя столбец Id из таблицы Orders.
Так как это INNER JOIN, то в группах будут только те покупатели, у которых есть заказы.
Если необходимо вывести даже тех покупателей, у которых нет заказов, то применяется OUTER JOIN:
SELECT FirstName, COUNT(Orders.Id) FROM Customers LEFT JOIN Orders
ON Orders.CustomerId = Customers.Id
GROUP BY Customers.Id, Customers.FirstName;
Или выведем товары с общей суммой сделанных заказов:
SELECT Products.ProductName, Products.Manufacturer, SUM(Orders.ProductCount * Orders.Price) AS Units
FROM Products LEFT JOIN Orders
ON Orders.ProductId = Products.Id
GROUP BY Products.Id, Products.ProductName, Products.Manufacturer
Персональный компьютер с установленной ОС Windows. Браузер с доступом в интернет. MS SQL Server. SQL Server Management Studio.
Порядок выполнения заданияСоздайте многотабличный запрос на выборку, который выводит фамилии, имена и отчества покупателей магазина, сделавших менее двух покупок:
Создайте запрос, который выводит список книг и содержит наименование книги, автора и наименование каталога, в котором содержится книга. Запрос создайте, используя блок WHERE оператора SELECT.
Создайте запрос, аналогичный предыдущему, но с использованием JOIN.
С помощью запроса добавьте в таблицу catalogs ещё один каталог: "Компьютеры".
Создайте запрос, который выводит список каталогов и количество книг в каждом каталоге, в порядке убывания количества книг в каталоге.
Измените этот запрос таким образом, чтобы каталог "Компьютеры" попал в выдачу.
Создайте запрос на выборку, который выводит номер заказа, ФИО автора, название и автора книги.
Контрольные вопросы:
1. Что такое соединение таблиц (JOIN)? Для чего оно используется?
2. Что такое ключ соединения (join key)?
Вебинар для учителей
Свидетельство об участии БЕСПЛАТНО!
