Сегодня мы рассмотрим пример базы данных и различные команды агрегации, группировки, сортировки, соединения таблиц и другое на примере 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. Были показаны основные и более редкие операции. В сущности не важно, сколько данных в Вашей таблице - десять или тысяча, от этого запросы не поменяются, а всегда будут оставаться такими же. Главное, чтобы был понятен смысл, количество данных играет намного меньшую роль. Бояться и расстраиваться от того, что у Вас пока не получается получить желаемый запрос, совершенно глупо. Абсолютно нормально, если Вы гуглите, читаете книгу на интересующую тему, а результата так и нет. На это может уходить от десятка минут до целых дней. Мы все люди и одному человеку не под силу знать все. Наберитесь терпения, спросите у товарищей, на форумах и просто продолжайте искать сами, у Вас все получится! Удачи.