4. Выполнение лабораторной работы Абсолютные ссылки на базы данных и таблицы
Рисунок 1 – Сложные запросы
Использование объединений для запросов к нескольким таблицам
Объединения двух таблиц, извлекающий из БД order фамилии покупателей вместе с номерами сделанных ими сделок.
Рисунок 2 – Объединения двух таблиц (фамилии покупателей вместе с номерами сделанных ими cделок)
Объединение нескольких таблиц аналогично объединению двух таблиц. Необходимо выяснить, какое название товара из сделки, сделанного 10 января 2023 г. в 13:41:05, представлена на рисунке 3.
Рисунок 3 – Объединения нескольких таблиц (Название товара из сделки, сделанного 10 января 2023 г. в 13:41:05)
Самообъединение таблиц
Можно объединить таблицу саму с собой (когда интересуют связи между строками одной и той же таблицы)
Рисунок 4 – Объединить таблицу саму с собой
Основное объединение.
Набор таблиц, перечисленных в выражении FROM и разделенных запятыми, – это декартово произведение (полное или перекрестное объединение), которое возвращает полный набор комбинаций. Добавление к нему условного выражения WHERE превращает его в объединение по эквивалентности, ограничивающее число возвращаемых запросом строк.
Рисунок 5 – Основные объединения
Вместо JOIN с тем же результатом можно использовать CROSS JOIN (перекрестное объединение) или INNER JOIN (внутреннее объединение).
Рисунок 6 – Основные объединения
Вывести список клиентов и число осуществленных ими сделок, причем клиентов необходимо отсортировать по убыванию числа сделок:
Рисунок 7 – Основные объединения
В список не входят клиенты, которые не сделали ни одной покупки. Чтобы вывести полный список покупателей, необходимо вместо перекрестного объединения таблиц clients и deals использовать левое объединение (левой таблицей должна быть таблица clients):
Рисунок 8 – Основные объединения
Вложенный запрос.
Вывести количество сделок и время сделки из таблицы deals для товара «Базы данных» таблицы products:
Рисунок 9 – Вложенный запрос.
В первом случае вложенный запрос возвращает скалярное значение или литерал, которое используется во внешнем запросе (подставляет результат на место своего выполнения). Необходимо определить название товара, самый дорогой по цене:
Рисунок 10 – Вложенный запрос.
Чтобы выбрать строки из таблицы products, у которых первичный ключ совпадает с одним из значений, возвращаемых вложенным запросом, следует воспользоваться конструкцией IN:
Рисунок 11 – Вложенный запрос.
Возвращающий имена и фамилии клиенты, совершивших хотя бы одну сделку:
Рисунок 12 – Вложенный запрос.
Возвращающий все сделки, цена которых превышает среднюю количество заказанных товаров:
Рисунок 13 – Вложенный запрос.
Запрос, формирующий список клиентов, совершивших хотя бы одну сделку, можно записать следующим образом:
Рисунок 14 – Вложенный запрос.
Ниже представлен листинг процесса выполнения лабораторной работы №5. Листинг 1. Процесс выполнения задания.
mysql -u root -p
USE order;
SELECT products.p_name from products;
SELECT deals.deal_ID, clients.c_surname
FROM deals, clients
WHERE deals.deal_client_ID = clients.client_ID
ORDER BY deals.deal_ID;
SELECT products.p_name
FROM products,deals,clients
WHERE deal_time='2023-01-10 13:41:05'
AND clients.client_ID = deal_client_ID
AND products.product_ID = deal_product_ID;
SELECT d2.deal_number
FROM deals d1 , deals d2
WHERE d1.deal_number=10
AND d1.deal_ID=d2.deal_ID;
SELECT deals.deal_ID, clients.c_surname
FROM deals JOIN clients
ON deals.deal_client_ID=clients.client_ID
ORDER BY deals.deal_ID;
SELECT products.p_name, COUNT(deal_ID)
FROM products JOIN deals ON products.product_ID=deals.deal_product_ID
GROUP BY deals.deal_product_ID;
SELECT clients.c_name, clients.c_surname,
COUNT(deals.deal_ID) AS total
FROM clients JOIN deals ON clients.client_ID=deals.deal_client_ID
GROUP BY clients.client_ID
ORDER BY total DESC;
SELECT clients.c_name, clients.c_surname,
COUNT(deals.deal_ID) AS total
FROM clients LEFT JOIN deals ON clients.client_ID=deals.deal_client_ID
GROUP BY clients.client_ID
ORDER BY total DESC;
SELECT deal_number, deal_time FROM deals
WHERE deal_product_ID = (SELECT product_ID FROM products WHERE p_name = 'Apple')
ORDER BY deal_time;
SELECT p_name FROM products
WHERE product_ID=(SELECT deal_product_ID FROM deals
WHERE deal_number=(SELECT MAX(deal_number) FROM deals));
SELECT p_name FROM products
WHERE product_ID IN (SELECT deal_product_ID FROM deals GROUP BY deal_product_ID);
SELECT c_name,c_surname FROM clients
WHERE client_ID=ANY(SELECT deal_client_ID FROM deals);
SELECT deal_number,deal_time FROM deals
WHERE deal_number>ALL(SELECT AVG(deal_number) FROM deals
GROUP BY deal_product_ID);
SELECT c_name, c_surname FROM clients
WHERE EXISTS (SELECT * FROM deals
WHERE deals.deal_client_ID=clients.client_ID);