Смекни!
smekni.com

Организация Web-доступа к базам данных с использованием SQL-запросов (стр. 7 из 22)

Например, выдать содержимое таблицы Блюда, отсортировав ее строки по видам блюд и основе:


Результат:

SELECT * FROM Блюда ORDER BY В Основа;

БЛ

Блюдо

В

Основа

Выход

Труд

21 Пудинг рисовый Г Крупа 160. 6
20 Каша рисовая Г Крупа 210. 4
18 Сырники Г Молоко 220. 4

. . .

16 Драчена Г Яйца 180. 4
28 Крем творожный Д Молоко 160. 4

. . .

26 Яблоки печеные Д Фрукты 160. 3
7 Сметана З Молоко 140. 1
8 Творог З Молоко 140. 2
2 Салат мясной З Мясо 200. 4
6 Мясо с гарниром З Мясо 250. 3
1 Салат летний З Овощи 200. 3

. . .

Кроме того, в список ORDER BY можно включать не только имя столбца, а его порядковую позицию в перечне SELECT. Благодаря этому возможно упорядочение результатов на основе вычисляемых столбцов, не имеющих имен.

Например, запрос

SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)

FROM Продукты

ORDER BY 2;

позволит получить список продуктов, показанный на рис.2.3,в – переупорядоченный по возрастанию значений калорийности список рис.2.3,а.

Агрегирование данных

SQL-функции

В SQL существует ряд специальных стандартных функций (SQL-функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так:

COUNT

7. число значений в столбце,

SUM

8. сумма значений в столбце,

AVG

9. среднее значение в столбце,

MAX

10. самое большое значение в столбце,

MIN

11. самое малое значение в столбце.

Для функций 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