Организация Web-доступа к базам данных с использованием SQL-запросов
)
FROM Продукты
ORDER BY 2;
позволит получить список продуктов, показанный на рис.2.3,в –
переупорядоченный по возрастанию значений калорийности список рис.2.3,а.
Агрегирование данных
SQL-функции
В SQL существует ряд специальных стандартных функций (SQL-функций).
Кроме специального случая COUNT(*) каждая из этих функций оперирует
совокупностью значений столбца некоторой таблицы и создает единственное
значение, определяемое так:
COUNT
5. число значений в столбце,
SUM
6. сумма значений в столбце,
AVG
7. среднее значение в столбце,
MAX
8. самое большое значение в столбце,
MIN
9. самое малое значение в столбце.
Для функций SUM и AVG рассматриваемый столбец должен содержать числовые
значения.
Следует отметить, что здесь столбец – это столбец виртуальной таблицы, в
которой могут содержаться данные не только из столбца базовой таблицы, но и
данные, полученные путем функционального преобразования и (или) связывания
символами арифметических операций значений из одного или нескольких
столбцов. При этом выражение, определяющее столбец такой таблицы, может
быть сколь угодно сложным, но не должно содержать SQL-функций (вложенность
SQL-функций не допускается). Однако из SQL-функций можно составлять любые
выражения.
Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое
слово DISTINCT (различный), указывающее, что избыточные дублирующие
значения должны быть исключены перед тем, как будет применяться функция.
Специальная же функция COUNT(*) служит для подсчета всех без исключения
строк в таблице (включая дубликаты).
Функции без использования фразы GROUP BY
Если не используется фраза GROUP BY, то в перечень элементов_SELECT
можно включать лишь SQL-функции или выражения, содержащие такие функции.
Другими словами, нельзя иметь в списке столбцы, не являющихся аргументами
SQL-функций.
Например, выдать данные о массе лука (ПР=10), проданного поставщиками,
и указать количество этих поставщиков:
| |Резул| |
| |ьтат:| |
| | | |
|SELECT SUM(К_во),COUNT(К_во) | | |
| | | |
|FROM Поставки | | |
|WHERE ПР = 10; | | |
| |SUM(К|COUNT(К_во) |
| |_во) | |
| |220 |2 |
Если бы для вывода в результат еще и номера продукта был сформирован
запрос
SELECT ПР,SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция
создает единственное значение из множества значений столбца-аргумента, а
для «свободного» столбца должно быть выдано все множество его значений. Без
специального указания (оно задается фразой GROUP BY) SQL не будет выяснять,
одинаковы значения этого множества (как в данном примере, где ПР=10) или
различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос
отвергается системой.
Правда, никто не запрещает дать запрос
SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
|Результат: | | |
|'Кол-во лука =' |SUM(К_во) |COUNT(К_во) |
|Кол-во лука = |220 |2 |
Отметим также, что в столбце-аргументе перед применением любой
функции, кроме COUNT(*), исключаются все неопределенные значения. Если
оказывается, что аргумент – пустое множество, функция COUNT принимает
значение 0, а остальные – NULL.
Например, для получения суммы цен, средней цены, количества
поставляемых продуктов и количества разных цен продуктов, проданных
коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов,
которые могут поставляться этим коопторгом, можно дать запрос
SELECT SUM(Цена),AVG(Цена),COUNT(Цена),
COUNT(DISTINCT Цена),COUNT(*)
FROM Поставки
WHERE ПС = 5;
и получить
|SUM(Цена) |AVG(Цена) |COUNT(Цена) |COUNT(DISTINCT Цена) |COUNT (*) |
|6.2 |1.24 |5 |4 |7 |
В другом примере, где надо узнать «Сколько поставлено моркови и
сколько поставщиков ее поставляют?»:
SELECT SUM(К_во),COUNT(К_во)
FROM Поставки
WHER ПР = 2;
будет получен ответ:
|SUM(К_во) |COUNT (К_во) |
|-0- |0 |
Наконец, попробуем получить сумму массы поставленного лука с его
средней ценой («Сапоги с яичницей»):
| |Результат: |
| | |
|SELECT (SUM(К_во) | |
|+AVG(Цена)) | |
|FROM Поставки | |
|WHERE ПР = 10; | |
| |SUM(К_во)+AVG(Цена)|
| |220.6 |
Фраза GROUP BY
Мы показали, как можно вычислить массу определенного продукта,
поставляемого поставщиками. Предположим, что теперь требуется вычислить
общую массу каждого из продуктов, поставляемых в настоящее время
поставщиками. Это можно легко сделать с помощью предложения
SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР;
Результат показан на рис. 2.5,а.
|а) | |б| | | |в) | |г) |
| | |)| | | | | | |
|ПР | |ПС |ПР |Цена |К_во |ПР | |ПР | |
|9 |0 |1 |9 |-0- |-0- |1 |370 |9 |0 |
|11 |150 |3 |9 |-0- |-0- |2 |0 |11 |150 |
|12 |30 |5 |9 |-0- |-0- |3 |250 |12 |30 |
|15 |370 |1 |11 |1.50 |50 |4 |100 |15 |70 |
|1 |370 |5 |11 |-0- |-0- |5 |170 |1 |370 |
|3 |250 |6 |11 |-0- |-0- |6 |220 |3 |250 |
|5 |170 |8 |11 |1.00 |100 |7 |200 |5 |70 |
|6 |220 |1 |12 |3.00 |10 |8 |150 |6 |140 |
|8 |150 |3 |12 |2.50 |20 |9 |0 |8 |150 |
|7 |200 |6 |12 |-0- |-0- |10 |220 |7 |200 |
|2 |0 |1 |15 |2.00 |170 |11 |150 |2 |0 |
|4 |100 |3 |15 |1.50 |200 |12 |30 |4 |100 |
|13 |190 |2 |1 |3.60 |300 |13 |190 |13 |190 |
|14 |70 |7 |1 |4.20 |70 |14 |70 |14 |70 |
|16 |250 |2 |3 |-0- |-0- |15 |370 |16 |250 |
|17 |50 |7 |3 |4.00 |250 |16 |250 |17 |50 |
|10 |220 |. . .| | | |17 |50 |10 |220 |
Рисунок 2.5
Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной
во FROM таблицы по группам, каждая из которых имеет одинаковые значения в
столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы
Поставки группируются так, что в одной группе содержатся все строки для
продукта с ПР = 1, в другой – для продукта с ПР = 2 и т.д. (см. рис.
2.5,б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в
этой фразе должно принимать единственное значение для группы, т.е. оно
может быть либо значением столбца, указанного в GROUP BY, либо
арифметическим выражением, включающим это значение, либо константой, либо
одной из SQL-функций, которая оперирует всеми значениями столбца в группе и
сводит эти значения к единственному значению (например, к сумме).
Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы
гарантировать упорядочение по ПР результата рассматриваемого примера (рис.
2.5,в) следует дать запрос
SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР
ORDER BY ПР;
Наконец, отметим, что строки таблицы можно группировать по любой
комбинации ее столбцов. Так, по запросу
SELECT Т, БЛ, COUNT(БЛ)
FROM Заказ
GROUP BY Т, БЛ;
можно узнать коды и количество порций блюд, заказанных отдыхающими
пансионата (32 человека) на каждую из трапез следующего дня:
|Т |БЛ |COUNT(БЛ) |
|1 |3 |18 |
|1 |6 |14 |
|1 |19 |17 |
|1 |21 |15 |
|… | | |
Использование фразы HAVING
Фраза HAVING (рис. 2.3) играет такую же роль для групп, что и фраза
WHERE для строк: она используется для исключения групп, точно так же, как
WHERE используется для исключения строк. Эта фраза включается в предложение
лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать
единственное значение для группы.
Например, выдать коды продуктов, поставляемых более чем двумя
поставщиками:
| |Результат: |ПР |
|SELECT | | |
|FROM Поставки | | |
|GROUP BY ПС | | |
|HAVING COUNT(*) 2;| | |
| | |9 |
| | |11 |
| | |12 |
| | | |
2.2.3. Использование запросов с использованием нескольких таблицы.
О средствах одновременной работы с множеством таблиц
Затрагивая вопросы проектирования баз данных, мы выяснили, что базы
данных – это множество взаимосвязанных сущностей или отношений (табли
| |  скачать работу |
Организация Web-доступа к базам данных с использованием SQL-запросов |
