Использование QBE для создания запросов на выборку данных

Запросы на выборку являются самым распространенным типом запросов. В начале создания нового запроса к базе данных открывается окно, которое будет содержать список таблиц и запросов, существующих в базе данных. В этом окне пользователю необходимо указать таблицы и/или запросы, содержащие интересующие его данные.

Окно Select Query представляет собой графический инструмент языка QBE. Для определения образца интересующих нас записей в графической среде этого окна для выборки, перетаскивания или манипулирования содержащимися в нем объектами можно использовать мышь. Определение полей и записей, которые должны быть включены в результаты запроса, производится в сетке языка QBE. При создании запроса в сетке конструирования QBE-запроса МS Access неявно генерирует для него эквивалентный SQL-оператор. Посмотреть и отредактировать этот оператор можно в окне SQL.

Критерием отбора называют ограничения, которые накладываются на результаты выполнения запроса с целью выборки только тех полей или записей данных, которые представляют интерес для пользователя. Например, для извлечения из таблицы Property_for_Rent (рис. 3) только столбцов номера объекта (Pno), города (city), типа объекта (Type) и суммы арендной платы (Rent) в сетке QBE может быть подготовлен запрос, показанный на рисунке 3, а.

После выполнения этого запроса, полученные результаты будут отображены в результирующей сетке данных, содержащей только указанные столбцы таблицы Property_for_Rent (см.рис. 3, б). Эквивалентный SQL-onepaтop запроса будет выглядеть следующим образом:

SELECT PROPERTY_FOR_RENT.Pno, PROPERTY_FOR_RENT.City, PROPERTY_FOR_RENT_Type, PROPERTY_FOR_RENT.Rent FROM PROPERTY_FOR_RENT;

Рис. 3. Пример запроса

Можно продолжать усложнять условия отбора, вводя дополнительные критерии для полей. Если поместить некоторые выражения в более чем одну ячейку строки Criteria, СУБД Access соединит их, используя логический оператор AND (И) или OR (Или). Если выражения в разных ячейках будут введены в одну и ту же строку сетки QBE, СУБД Access использует дляих соединения оператор AND. Это означает, что в результирующий набор будут помещены только те записи, которые отвечают одновременно всем указанным критериям отбора. Если выражения будут помещены в разные строки сетки QBE, СУБД Microsoft Access использует для соединения логический оператор OR. В этом случае в результирующий набор попадут любые записи, отвечающие хотя бы одному из указанных условий отбора.

Например, для вывода сведений о расположенных в городе Глазго объектах недвижимости, у которых ежемесячная арендная плата находится в диапазоне от 350 до 450 фунтов стерлингов, достаточно поместить значение ‘Glasgow’ в ячейку Criteria стол City и ввести выражение ‘Between 350 And 450’ в ячейку Criteria столбца Rent. Пример соответствующим образом заполненной сетки QBE приведен на рисунке 4, а.

Сетка QBE

SELECT PROPERTY_FOR_RENT. Pno, PROPERTY_FOR_RENT.CIty, PROPERTY_FOR_RENT .Type,

PROPERTY_FOR_RENT.Rent

FROM PROPERTY_FOR_RENT

WHERE (((PROPERTY_FOR_RENT.City)=”GlasgoW) AND ((PROPERTY_FOR_RENT.Rent) Between

350 And 450));

в

Рис. 4. Пример запроса

После выполнения данного запроса будет выведена сетка данных, содержащая только те записи, которые отвечают указанному критерию (рис. 4, б). Эквивалент­ный SQL-оператор для данного запроса представлен на рис. 4, в.

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

Для того чтобы выбрать необходимые данные сразу из нескольких таблиц, следует подготовить запрос на выборку данных; в окне запроса будут представлены требуемые таблицы, а критерий отбора будет определен в сетке QBE. Например, для выборки имени и фамилии владельцев объектов недвижимости с указанием учетных номеров принадлежащих им объектов, а также названий городов, в которых эти объекты расположены, следует создать запрос, показанный на рисунке 5, а. Списки полей целевых таблиц запроса (а именно таблиц Owner и Property_for_Rent) размещены в окне запроса над сеткой QBE.

В результирующую таблицу запроса из таблицы Owner выбираются столбцы Fname и Lname, а из таблицы Property_for_Rent – столбцы Вnо и City. После выполнения данного запроса будет выведена сетка данных, содержащая записи, отвечающие указанному критерию (рис. 5, б). Эквива­лентный SQL-оператор для для этого запроса представлен на рисунке 5, в.

Многотабличный запрос, показанный на рисунке 5, является примером запроса с внутренним (естественным) соединением.

Если в окно запроса помещается больше одной целевой таблицы, следует убедить­ся, что спискиих полей связаны между собой линиями соединения, т.е. СУБД Microsoft Access знает, как соединить таблицы друг с другом. Обратите внимание, что на рисунке 5,а СУБД Access поместила над верхней частьюлинии соединения. Символ 1 указывает на единичную сторону связи типа “один ко многим”. Над нижней частью линии соединения помещен символ °°, отмечающий множественную сторону этой же связи. Это означает, что в нашем примере один владелец может владеть многими объектами недвижимости, сдаваемыми в аренду.

Если СУБД Access не выполнила соединение таблиц автоматически или если ме­жду данными таблицами еще не была описана какая-либо связь, то таблицы в окне запроса не будут связаны линией соединения. Возможность выборки связанных данных из двух таблиц сохраняется, но потребуется указать способ соединения этих таблиц непосредственно при создании запроса в окне сетки QBE. Однако для того чтобы две таблицы можно было соединить непосредственно в запросе, в обеих таблицах должны существовать связанные поля. В примере, показанном на рис. 5, поле Ono (Личный номер владельца) является общим для обеих таблиц – Owner и Ргорerty_for_Rent. Для того чтобы соединение работало корректно, оба столбца должны содержать одинаковые значения в связанных записях данных.

Достаточно часто появляется необходимость обобщения той или иной группы данных. Например, сколько сдаваемых в аренду объектов имеется в каждом из городов? Чему равна средняя заработная плата работников компании?

в SELECT OWNER.FName, OWNER.LName,

PROPERTY_FOR_RENT.Pno,PROPERTY_FOR_RENT.City FROM OWNER INNER JOIN PROPERTY.FOR.RENT ON

OWNER.Ono = PROPERTY_FOR_RENT.Ono;

Рис. 5. Многотабличный запрос

Операцию обобщающих вычислений над группой записей можно выполнить с помощью запросов с подведением итогов (иногда их называют обобщающими запросами). СУБД Microsoft Access позволяет производить различные типы итоговых вычислений, включая функции суммирования (Sum), вычисления среднего значения (Avg), поиска минимального (Min) или максимального (Мах) значения, а также подсчета экземпляров (Count). Чтобы получить доступ к этим функциям, следует изменить тип запроса на Totals, в результате чего в сетке QBE будет отображена дополнительная строка с названием Total. После выполнения обобщающего запроса отображается таблица, содержащая моментальный снимок состояния данных – набор строк, кото­рый не допускает внесения изменений.

Как и в случае запросов других типов, в запросе с подведением итогов может быть указан некоторый критерий отбора записей. Например, предположим, что требуется определить общее количество объектов недвижимости, сдаваемых в аренду в каждомиз городов. Для этого необходимо сначала сгруппировать данные об объектах по значению поля City, для чего используется функция Group By, после чего выполнить для каждой группы вычисление итогового значения с помощью функции Count. Общий вид сетки QBE с подобным запросом показан на рисунке 6,а, а результирующая сетка данных представлена на рисунке 6,б. На рисунке 6,в показан эквивалентый SQL-оператор данного запроса.

б

SELECT PROPERTY_FOR_RENT.CIty, Count (PROPERTY_FOR_RENT.Pno) AS CountOfPno FROM PROPERTY_FOR_RENT GROUP BY PROPERTY_FOR_RENT.City;

Рис. 6. Пример запроса