Лабораторная № 8 «Примеры использования SELECT» по Базам данных (Шурыгин В. Н.)

Кирилл Николоев сб, 25.03.2017 22:34

Продолжение изучения SELECT в т.ч. в комбинации с другими командами. Изучение команды INSERT в сочетании с SELECT для формирования новых таблиц. Изучение конструкции GROUP BY для группировки данных. Изучение на примерах использования групповых (агрегатных) функций

Команда INSERT…SELECT…. Вставка данных в таблицу с использованием SELECT Пример 24. Для выполнения этого запроса придется для каждой строки заказа вычислять произведение стоимости одной книги на количество заказанных книг. А далее суммировать все полученные значения по всем заказам. Это делается в три этапа следующим образом:

Этап 1. Создадим вспомогательную таблицу PRICE, содержащую поля isbn, orderid и price – стоимость всех заказанных книг с данным isbn. Этап 2. Занесем в эту таблицу цены заказов. Для этого воспользуемся командой INSERT … SELECT. Приведем текст запроса, а затем поясним действие команды.

В этом примере мы сформировали все значения, заносимые в таблицу с помощью SELECT. Обратите внимание на тот факт, что мы использовали псевдонимы, совпадающие с именами полей таблицы PRICE. Групповое суммирование. Агрегатная функция SUM()

Для вычисления суммы выделенного столбца отобранных записей MySQL не требует применения каких-либо алгоритмов. Вполне достаточно указать MySQL специальную (групповую или агрегатную) функцию SUM(), назначение которой состоит в том, чтобы вычислить сумму всех отобранных командой SELECT значений. Эта функция применяется на третьем этапе решения примера 24.

Этап 3. Используем созданную таблицу для вычисления суммы всех заказов: mysql> SELECT SUM(price) FROM price; В этом запросе использовалась функция SUM(). Она вычисляет сумму значений в указанном столбце (price) всех записей, отобранных SELECT (если действует часть WHERE, то агрегатная функция учитывает только записи, соответствующие сформулированному в WHERE условию).

Конструкция GROUP BY. Другие агрегатные функции В примере 24 была вычислена суммарная стоимость всех заказов. В указанном виде запрос малоинформативен. Получим значение стоимости каждого заказа. Еще раз рассмотрим вспомогательную таблицу PRICE. Для заказов, имеющих один номер, существует несколько записей. Нам нужно просуммировать стоимости по каждому номеру заказа отдельно. Это может быть сделано с использованием конструкции GROUP BY:

Часть «GROUP BY orderid» команды SELECT сгруппировала в одну сумму все слагаемые с одинаковым значением orderid. Так как у нас 4 различных значений номеров заказа, то мы получили 4 различных суммы. Агрегатная функция COUNT(). Модифицируем запрос таким образом, чтобы мы могли подсчитать число книг в каждом заказе. Для этого воспользуемся функцией COUNT(), которая работает так же, как и SUM(), но, в отличии от последней, подсчитывает не сумму, а число записей, соответствующих условию отбора.

Колонка TOTAL в последнем запросе точно указывает число различных книг в каждом заказе. Вычисление среднего значения. Агрегатная функция AVG(). Написать запрос, вычисляющий среднюю стоимость каждого заказа.

Пример 25. Воспользуемся приемом, использованным ранее. Создадим дополнительную таблицу, а затем вычислим среднюю стоимость заказа. Создание вспомогательной таблицы Вычисление среднего значения Весь процесс и последовательность действий для получения среднего значения были определены ранее. Необходимо отметить, что функция AVG() также является агрегатной. Она вычисляет среднее значение на всем множестве отобранных записей.

Минимальное и максимальное значения. Агрегатные функции MIN() и MAX(). Написать запросы, вычисляющие максимальную и минимальную стоимости заказов. Пример 26. Максимальная стоимость считается так же, как сумма или среднее значение:

Пример 27. Минимальная стоимость считается аналогично: Этот пример допускает интересную модификацию. Допустим, нас интересует номер заказа с минимальной стоимостью. Найти этот номер можно, выполняя два последовательных запроса. Первый запрос должен вычислить минимальную стоимость заказа и присвоить это значение некоторой переменной, а второй – найти записи, которые имеют значение минимальное значение стоимости заказа.

Первый запрос не требует пояснений. Относительно второго заметим, что мы ищем те записи, которые меньше величины немного большей (на 0.001), чем минимальное найденное значение стоимости заказа. Поскольку все заказы считаются с точностью до 2-х знаков после запятой, то сформулированное во втором запросе условие отберет именно записи с минимальной стоимостью заказа. Если в условии отбора:

WHERE sum = @MIN использовать равенство, то (из-за особенностей работы с числами с плавающей запятой) нужные записи можно пропустить (см. результаты ниже). В случае сравнения целых чисел использование равенства дает нужный результат.

Скачать файлы

Похожие документы