Банк Рефератов - Организация Web-доступа к базам данных с использованием SQL-запросов

Рефераты. Организация Web-доступа к базам данных с использованием SQL-запросов p> Выборка с упорядочением

Простейший вариант этой фразы – упорядочение строк результата по значению одного из столбцов с указанием порядка сортировки или без такого указания. (По умолчанию строки будут сортироваться в порядке возрастания значений в указанном столбце.)

Например, выдать перечень продуктов и содержание в них основных веществ в порядке убывания содержания белка
| |Продукт|Белки |Жиры |Углев |
|SELECT Продукт,| | | | |
|Белки, Жиры, | | | | |
|Углев | | | | |
|FROM Продукты | | | | |
|ORDER BY Белки | | | | |
|DESC; | | | | |
| |Судак |190. |80. |0. |
| |Говядин|189. |124. |0. |
| |а | | | |
| |Творог |167. |90. |13. |
| |Яйца |127. |115. |7. |
| |Кофе |127. |36. |9. |
| |Мука |106. |13. |732. |

При включении в список ORDER BY нескольких столбцов СУБД сортирует строки результата по значениям первого столбца списка пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по значениям следующего столбца из списка ORDER BY и т.д.

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

| |Результат: | | | |
|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
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; то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для «свободного» столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой.
Правда, никто не запрещает дать запрос

Отметим также, что в столбце-аргументе перед применением любой функции, кроме 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 |

В другом примере, где надо узнать «Сколько поставлено моркови и сколько поставщиков ее поставляют?»:

Фраза 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 должно принимать единственное значение для группы.

2.2.3. Использование запросов с использованием нескольких таблицы.

О средствах одновременной работы с множеством таблиц

Затрагивая вопросы проектирования баз данных, мы выяснили, что базы данных – это множество взаимосвязанных сущностей или отношений (таблиц) в терминологии реляционных СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектантами, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Типичен вопрос: как же получить сведения о том, где купить продукты для приготовления того или иного блюда и определить его калорийность и стоимость, если нужные данные «рассыпаны» по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных ПАНСИОН ?

Страницы: 1, 2, 3, 4, 5, 6, 7