практическая работа. Практическая работа - для слияния. Практическая работа 1 sql создание базы данных и таблиц Цель

• Название: Практическая работа 1 sql создание базы данных и таблиц Цель
• Анкор: практическая работа
• Дата: 14.02.2023
• Размер: 3.41 Mb.
• Имя файла: Практическая работа - для слияния.docx
• Тип: Практическая работа; #936408
• страница: 11 из 18

Практическая работа № 11

SQL - Итоговые функции, вычисляемые столбцы и представления

Цель: научиться работать с итоговыми функциями, вычисляемыми столбцами и представлениями

Итоговые функции еще называют статистическими, агрегатными или суммирующими. Эти функции обрабатывают набор строк для подсчета и возвращения одного значения. Таких функций всего пять:

• 
  AVG() Функция возвращает среднее значение столбца.
  
• 
  COUNT() Функция возвращает число строк в столбце.
  
• 
  MAX() Функция возвращает самое большое значение в столбце.
  
• 
  MIN() Функция возвращает самое маленькое значение в столбце.
  
• 
  SUM() Функция возвращает сумму значений столбца.
  

С одной из них - COUNT() - мы уже познакомились в уроке 8. Сейчас познакомимся с остальными. Предположим, мы захотели узнать минимальную, максимальную и среднюю цену на книги в нашем магазине. Тогда из таблицы Цены (prices) надо взять минимальное, максимальное и среднее значения по столбцу price. Запрос простой:
SELECT MIN(price), MAX(price), AVG(price) FROM prices;



Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик "Дом печати" (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:

1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком "Дом печати" (id=2):
SELECT id_incoming FROM incoming

WHERE id_vendor=2;



2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:
SELECT id_product, quantity FROM magazine_incoming

WHERE id_incoming=(SELECT id_incoming FROM incoming WHERE id_vendor=2);



3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price FROM magazine_incoming, prices

WHERE magazine_incoming.id_product= prices.id_product AND id_incoming=

(SELECT id_incoming FROM incoming WHERE id_vendor=2);



4. В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца. Возможность создания таких столбцов предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового столбца отделяется словом AS:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price,

magazine_incoming.quantity*prices.price AS summa

FROM magazine_incoming, prices

WHERE magazine_incoming.id_product= prices.id_product AND id_incoming=

(SELECT id_incoming FROM incoming WHERE id_vendor=2);


5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик "Дом печати". Синтаксис для использования функции SUM() следущий:
SELECT SUM(имя_столбца) FROM имя_таблицы;

Имя столбца нам известно - summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать? Для таких случаев в MySQL существуют Представления. Представление - это запрос на выборку, которому присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования. 

Синтаксис создания представления следующий:
CREATE VIEW имя_представления AS запрос;

Давайте сохраним наш запрос, как представление с именем report_vendor:
CREATE VIEW report_vendor AS

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price,

magazine_incoming.quantity*prices.price AS summa

FROM magazine_incoming, prices

WHERE magazine_incoming.id_product= prices.id_product AND id_incoming=

(SELECT id_incoming FROM incoming WHERE id_vendor=2);

6. Вот теперь можно использовать итоговую функцию SUM():
SELECT SUM(summa) FROM report_vendor;


Вот мы и достигли результата, правда для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. Да, иногда для получения результата приходится подумать, без этого никуда. Зато мы коснулись двух очень важных тем - вычисляемые столбцы и представления. Давайте поговорим о них поподробнее.