Сегодня мы рассмотрим пример базы данных и различные команды агрегации, группировки, сортировки, соединения таблиц и другое на примере MySQL. Сами данные представляют собой набор таблиц с произвольными названиями и значениями. Структура таблиц и их связи представлены ниже.
Для понимания дальнейшего Вам будут желательно начальные знания SQL и баз данных. Данное пособие поможет структурировать информацию, обновить память и может выступать в роли шпаргалки, которую вы можете использовать при надобности. Запросы на создание всех таблиц находятся тут.
С содержанием таблиц удобно ознакомиться в формате excel:
Как ни странно, но начнем мы анализ с таблицы, которая не упоминалась выше и не имеет связей с другими. Она представляет собой статистику за год по некоторым ключевым показателям.
Примечание: дабы не загружать статью всеми видами таблиц из запросов, визуально представлены будут только некоторые, а с остальными Вы без проблем можете поэкспериментировать сами.
Агрегация, группировка, сортировка
Сумма заказов за весь год:
SELECT SUM(amount_of_orders) AS orders_per_year FROM year_statistics;
Убывающая сортировка заказов по месяцам:
SELECT month_name, amount_of_orders
FROM year_statistics
ORDER BY amount_of_orders DESC;
Вывести месяц, где больше всего заказов:
SELECT month_name, amount_of_orders FROM year_statistics WHERE amount_of_orders = (SELECT MAX(amount_of_orders)
FROM year_statistics);
Популярность районов по количествам клиентов:
SELECT district
FROM customers
GROUP BY district
ORDER BY COUNT(district) DESC;
Сколько каждый курьер доставил заказов:
SELECT courier_id, COUNT(order_id)
From delivery_list
WHERE date_arrived IS NOT NULL
GROUP BY courier_id;
Общие запросы и использование операторов IN, EXISTS, UNION и др.
Выборка клиентов, которые живут в районе "South":
SELECT * FROM Customers
WHERE district IN ('South');
Информация о заказах, которые не были доставлены клиентам:
SELECT * FROM delivery_list
WHERE taken NOT IN ('Yes');
Запрос продуктов из меню, которые были заказаны:
SELECT menu_name FROM products
WHERE EXISTS
(SELECT * FROM orders_products
WHERE orders_products.product_id = products.product_id);
Запрос тех продуктов, которые не заказывали:
SELECT menu_name FROM products WHERE NOT EXISTS
(SELECT * FROM orders_products
WHERE orders_products.product_id = products.product_id);
Получаем общую таблицу с информацией о клиентах и курьеров:
SELECT 'Customer' AS category, first_name, last_name, phone_number
FROM customers
UNION
SELECT 'Employee' AS category, first_name, last_name, phone_number
FROM courier_info;
INNER, NATURAL, CROSS, LEFT JOIN
Наиболее интересный запрос, который позволяет видеть детали заказа(номер, название блюда, количество и цена). К тому же здесь использован метод ROUND, позволяющий округлять дробные числа:
SELECT orders_products.order_id, products.menu_name, quantity,
ROUND(price*quantity, 2) AS total_price
FROM orders_products
INNER JOIN products ON orders_products.product_id = products.product_id
ORDER BY order_id, quantity;
Еще один довольно любопытный запрос, показывающий детальную информацию по заказам, а также время их доставки:
SELECT *, SEC_TO_TIME(TIMESTAMPDIFF(second, date_get, date_arrived))
AS time_of_delivery
FROM orders
NATURAL JOIN delivery_list;
Не совсем тривиальный запрос на выборку о том, что каждому курьеру на машине доступен любой район из таблицы клиентов:
SELECT DISTINCT courier_info.courier_id, customers.district
FROM courier_info
CROSS JOIN customers WHERE courier_info.delivery_type = 'car'
ORDER BY courier_id;
И напоследок запрос на информацию об имени клиента, его мобильном телефоне и номере заказа:
SELECT customers.first_name, customers.last_name,
customers.phone_number, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
Заключение
По итогу мы с вами разобрали множество полезных запросов на выборку SQL. Были показаны основные и более редкие операции. В сущности не важно, сколько данных в Вашей таблице - десять или тысяча, от этого запросы не поменяются, а всегда будут оставаться такими же. Главное, чтобы был понятен смысл, количество данных играет намного меньшую роль. Бояться и расстраиваться от того, что у Вас пока не получается получить желаемый запрос, совершенно глупо. Абсолютно нормально, если Вы гуглите, читаете книгу на интересующую тему, а результата так и нет. На это может уходить от десятка минут до целых дней. Мы все люди и одному человеку не под силу знать все. Наберитесь терпения, спросите у товарищей, на форумах и просто продолжайте искать сами, у Вас все получится! Удачи.
