Формирование запросов к базе данных. Объектная модель запроса "схема запроса" - теория и примеры использования Помощью запроса в access можно

Формирование запросов.

Наименование параметра	Значение
Тема статьи:	Формирование запросов.
Рубрика (тематическая категория)	Связь

Запросы. Типы запросов.

Когда требуется просмотреть, добавить, изменить или удалить данные из базы данных, удобно использовать запросы.

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

Запрос представляет собой обращение к данным для получения информации и выполнения действий с данными. Запрос можно использовать для получения ответа на простой вопрос, выполнения расчетов, объединœения данных из разных таблиц или даже добавления, изменения или удаления данных в таблице.

Запрос (query) - ϶ᴛᴏ средство выбора крайне важно й информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос. Применяются два типа запросов: по образцу (QBE – Query by example) и структурированный язык запросов (SQL – Structured Query Language).

QBE - запрос по образцу – средство для отыскания крайне важно й информации в базе данных. Он формируется не на специальном языке, а путем заполнения бланка запроса в окне Конструктора запросов.

SQL – запросы - ϶ᴛᴏ запросы, которые составляются (программистами) из последовательности SQL – инструкций. Эти инструкции задают, что нужно сделать с входным набором данных для генерации выходного набора. Все запросы Access строит на базе SQL – запросов, чтобы посмотреть их, крайне важно в активном окне проектирования запроса выполнить команду Вид/SQL.

Существует несколько типов запросов: на выборку, на обновление, на добавление, на удаление, перекрестный запрос, создание таблиц. Наиболее распространенным является запрос на выборку. Запросы на выборку используются для отбора нужной пользователю информации, содержащейся в таблицах. Οʜᴎ создаются только для связанных таблиц.

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

Запросы, используемые для добавления, изменения или удаления данных, называются запросами на изменение .

Создание запроса на выборку с помощью Мастера

При создании query крайне важно определить:

· Поля в базе данных, по которым будет идти поиск информации

· Предмет поиска в базе данных

· Перечень полей в результате выполнения запроса

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

В окне мастера выбрать необходимую таблицу (таблицу - источник) из опции Таблицы и запросы и выбрать поля данных. В случае если query формируется на базе нескольких таблиц, крайне важно повторить действия для каждой таблицы – источника.

Затем в окне Мастера нужно выбрать подробный или итоговый отчет и щелкнуть на кнопке Далее. После этого крайне важно задать имя запроса и выбрать один из вариантов дальнейшего действия: Открыть query для просмотра данных или Изменить макет запроса и нажать кнопку Готово. В результате чего получите готовый query.

Создание запроса на выборку с помощью Конструктора

С помощью конструктора можно создать следующие виды запросов:

· Простой

· По условию

· Параметрические

· Итоговые

· С вычисляемыми полями

Чтобы вызвать Конструктор запросов, крайне важно перейти в окно базы данных. В окне база данных крайне важно выбрать вкладку Запросы и дважды щелкнуть на пиктограмме Создание запроса в режиме конструктора. Появится активное окно Добавление таблицы на фоне неактивного окна ʼʼЗапрос: запрос на выборкуʼʼ.

В окне Добавление таблицы следует выбрать таблицу – источник или несколько таблиц из представленного списка таблиц, на базе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить. После этого закрыть окно Добавление таблицы, окно ʼʼЗапрос: запрос на выборкуʼʼ станет активным.

Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список таблиц – источников и отражает связь между ними.

В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определœенную функцию:

Поле – указывает имена полей, которые участвуют в запросœе

Имя таблицы – имя таблицы, с которой выбрано это поле

Сортировка – указывает тип сортировки

Вывод на экран – устанавливает флажок просмотра поля на экране

Условия отбора - задаются критерии поиска

Или – задаются дополнительные критерии отбора

В окне ʼʼЗапрос: запрос на выборкуʼʼ с помощью инструментов формируем query:

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

Переместить имена полей с источника в Бланк запроса. К примеру, из таблицы Группы студентов отбуксировать поле Название в первое поле Бланка запросов, из таблицы Студенты отбуксировать поле Фамилии во второе поле Бланка запросов, а из таблицы Успеваемость отбуксировать поле Оценка в третье поле и из таблицы Дисциплины отбуксировать поле Название в четвертое поле Бланка запросов.

Задать принцип сортировки. Курсор мыши переместить в строку Сортировка для любого поля, появится кнопка открытия списка режимов сортировки: по возрастанию и по убыванию. К примеру, установить в поле Фамилия режим сортировки – по возрастанию.

В строке вывод на экран автоматически устанавливается флажок просмотра найденной информации в поле.

В строке "Условия" отбора и строке "Или" крайне важно ввести условия ограниченного поиска – критерии поиска. К примеру, в поле Оценка ввести - "отл/A", ᴛ.ᴇ. отображать всœе фамилии студентов, которые получили оценки отл/A.

После завершения формирования запроса закрыть окно Запрос на выборку. Откроется окно диалога Сохранить – ответить Да (ввести имя созданного запроса, к примеру, Образец запроса в режиме Конструктор) и щелкнуть ОК и вернуться в окно базы данных.

Чтобы открыть query из окна базы данных, крайне важно выделить имя запроса и щелкнуть кнопку Открыть, на экране появится окно запрос на выборку с требуемым именем.

Чтобы внести изменения в query его крайне важно выбрать щелчком мыши в окне базы данных, выполнить щелчок по кнопке Конструктор, внести изменения. Сохранить запрос, повторить его выполнение.

Формирование запросов. - понятие и виды. Классификация и особенности категории "Формирование запросов." 2017, 2018.

Запрос (query) – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос.

Применяются два типа запросов: по образцу (QBE – Query by example) и структурированный язык запросов (SQL – Structured Query Language).

QBE - запрос по образцу – средство для отыскания необходимой информации в базе данных. Он формируется не на специальном языке, а путем заполнения бланка запроса в окне Конструктора запросов.

SQL – запросы – это запросы, которые составляются (программистами) из последовательности SQL – инструкций. Эти инструкции задают, что надо сделать с входным набором данных для генерации выходного набора. Все запросы Access строит на основе SQL – запросов, чтобы посмотреть их, необходимо в активном окне проектирования запроса выполнить команду Вид/SQL.

Существует несколько типов запросов: на выборку, на обновление, на добавление, на удаление, перекрестный запрос, создание таблиц. Наиболее распространенным является запрос на выборку. Запросы на выборку используются для отбора нужной пользователю информации, содержащейся в таблицах. Они создаются только для связанных таблиц.

С помощью конструктора можно создать следующие виды запросов:

Простой

По условию

Параметрические

Итоговые

С вычисляемыми полями

При создании query необходимо определить:

Поля в базе данных, по которым будет идти поиск информации

Предмет поиска в базе данных

Перечень полей в результате выполнения запроса

Чтобы вызвать Конструктор запросов , необходимо перейти в окно базы данных. В окне база данных необходимо выбрать вкладку Запросы и дважды щелкнуть на пиктограмме Создание запроса в режиме конструктора . Появится активное окно Добавление таблицы на фоне неактивного окна «Запрос: запрос на выборку».

В окне Добавление таблицы следует выбрать таблицу – источник или несколько таблиц из представленного списка таблиц, на основе которых будет проводиться выбор данных, и щелкнуть на кнопке Добавить . После этого закрыть окно Добавление таблицы , окно «Запрос: запрос на выборку » станет активным.

Окно Конструктора состоит из двух частей – верхней и нижней. В верхней части окна размещается схема данных запроса, которая содержит список таблиц – источников и отражает связь между ними.

В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию:

Поле – указывает имена полей, которые участвуют в запросе

Имя таблицы – имя таблицы, из которой выбрано это поле

Сортировка – указывает тип сортировки

Вывод на экран – устанавливает флажок просмотра поля на экране

Условия отбора - задаются критерии поиска

Или – задаются дополнительные критерии отбора

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

Запрос 1 Выбрать все затраты организации ОАО «Всестрой»

Все текстовые значения вводятся в кавычках, но если кавычки уже существуют то такое выражение включают в одинарных кавычках.

В окне «Запрос: запрос на выборку» с помощью инструментов формируем query:

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

Переместить имена полей с источника в Бланк запроса. Например, из таблицы Организации отбуксировать поле организации в первое поле Бланка запросов, из таблицы затраты отбуксировать поле Затраты во второе поле Бланка запросов, а из таблицы Затраты отбуксировать поле Стоимость в третье поле

Задать принцип сортировки. Курсор мыши переместить в строку Сортировка для любого поля, появится кнопка открытия списка режимов сортировки: по возрастанию и по убыванию. Например, установить в поле Стоимость режим сортировки – по возрастанию.

В строке вывод на экран автоматически устанавливается флажок просмотра найденной информации в поле. Поэтому убрать флажок в поле Организации

В строке "Условия" отбора и строке "Или" необходимо ввести условия ограниченного поиска – критерии поиска. Например, в поле Код организации ввести код 302 т.е. отображать все затраты,организации ОАО «Всестрой»

После завершения формирования запроса закрыть окно Запрос на выборку. Откроется окно диалога Сохранить – ответить Да (ввести имя созданного запроса, например, Образец запроса в режиме Конструктор) и щелкнуть ОК и вернуться в окно базы данных.

Запрос 2 Затраты Иркутских организаций

Запрос по условию

Запрос3 Затраты за первый квартал 2012г ,

Параметрические запросы

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

Предположим, что пользователю необходимо видеть все документы в информационной базе одним списком. Несмотря на протесты разработчика из-за отрицательного влияния на производительность этой разработки, под давлением руководства было решено сделать такой отчет.

Чтож, раз задача требует реализации - давайте выполним ее!

Как это сделать

Сразу оговоримся, что в отчете будут выводиться два поля: "Ссылка" и "Проведен". Эти стандартные реквизиты есть у всех документов. Запрос для получения всех документом мы будем формировать программным образом, обходя коллекцию метаданных "Метаданные.Документы". Для каждого элемента коллекции будем создавать запрос к его таблице по реквизитам "Ссылка" и "Проведен", а дальше объединять его результат с результатом аналогичного запроса к другому документу.

" ВЫБРАТЬ | Док. Ссылка КАК Ссылка, | Док. Проведен КАК Проведен |ИЗ | Документ. ABCКлассификацияПокупателей КАК Док . Ссылка КАК Ссылка, | Док. Проведен КАК Проведен |ИЗ | Документ. АвансовыйОтчет КАК Док |ОБЪЕДИНИТЬ ВСЕ |ВЫБРАТЬ | Док. Ссылка КАК Ссылка, | Док. Проведен КАК Проведен |ИЗ | Документ. АккредитивПереданный КАК Док |ОБЪЕДИНИТЬ ВСЕ | . . . "

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

При компоновке результата отчета будет необходимо поместить сформированный запрос в основной набор данных. Остальные действия при программном формировании отчета будут стандартными.

Реализация

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

Вообще, в нашем случае не важно, какой запрос будет для набора данных. Его мы добавили только для того, чтобы удобно было настраивать структуру отчета в конструкторе, поскольку иначе бы поля на закладке "Настройка" отсутствовали. Конечная настройка структуры отчета следующая:

Теперь в обработчике события "ПриКомпоновкеРезультата" отключим стандартную обработку и выполним формирование отчета программно. Программный код формирования отчета на СКД представлен на следующем листинге:

Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка) СтандартнаяОбработка = Ложь ; // отключаем стандартный вывод отчета - будем выводить программно Настройки = КомпоновщикНастроек. Настройки; // Получаем настройки отчета ДанныеРасшифровки = Новый ДанныеРасшифровкиКомпоновкиДанных; // Создаем данные расшифровки КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных; // Создаем компоновщик макета // Инициализируем макет компоновки используя схему компоновки данных // и созданные ранее настройки и данные расшифровки " ) ; МакетКомпоновки = КомпоновщикМакета. Выполнить (СхемаКомпоновкиДанных, Настройки, ДанныеРасшифровки) ; // Скомпонуем результат ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных; ПроцессорКомпоновки. Инициализировать(МакетКомпоновки, , ДанныеРасшифровки) ; ДокументРезультат. Очистить() ; // Выводим результат в табличный документ ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент; ПроцессорВывода. УстановитьДокумент(ДокументРезультат) ; ПроцессорВывода. Вывести(ПроцессорКомпоновки) ; КонецПроцедуры

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

// .................................................................. СхемаКомпоновкиДанных = ПолучитьМакет(" ОсновнаяСхемаКомпоновкиДанных " ) ; // Получаем схему компоновки данных СхемаКомпоновкиДанных. НаборыДанных. НаборДанных1. Запрос = " " ; // Очищаем запрос в наборе данных ДокументыКонфигурации = Метаданные. Документы; // Получаем коллекцию метаданных документов КоличествоДокументов = ДокументыКонфигурации. Количество() ; // Определяем общее количество документов в конфигурации Сч = 1 ; ТекстЗапроса = " " ; Для Каждого Док Из ДокументыКонфигурации Цикл ТекстЗапроса = ТекстЗапроса + // Для каждого документа формируем текст запроса к его таблице " ВЫБРАТЬ | Док. Ссылка КАК Ссылка, | Док. Проведен КАК Проведен |ИЗ | Документ. " + Док. Имя+ " КАК Док " ; // В текст запроса нам необходимо лишь подставить имя таблицы документа в дереве метаданных Если Сч КоличествоДокументов Тогда // Если документ не последний в коллекции - добавляем инструкцию "ОБЪЕДИНИТЬ ВСЕ" для ТекстЗапроса = ТекстЗапроса + // объединения результатов запросов по документам в единый список " |ОБЪЕДИНИТЬ ВСЕ | " ; КонецЕсли ; Сч = Сч + 1 ; КонецЦикла ; СхемаКомпоновкиДанных. НаборыДанных. НаборДанных1. Запрос = ТекстЗапроса; // Помещаем новый текст запроса в набор данных МакетКомпоновки = КомпоновщикМакета. Выполнить (СхемаКомпоновкиДанных, Настройки, ДанныеРасшифровки) ; // Компонуем макет // ....................................................................

Отчет готов к тестированию.

Что в итоге?

Запустим отчет в режиме 1С:Предприятие. Отчет успешно выполнится и мы увидим список всех документов в информационной базе.

При всех проделанных действия отчет имеет все стандартные возможности СКД: расшифровка, настройка структуры отчета, отборы, условное оформление и прочее.

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

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

Запросы можно создавать самостоятельно и с помощь(о мастеров. Мастера запросов автоматически выполняют основные действия в зависимости от ответов пользователя на поставленные вопросы. Самостоятельно разработать запросы можно в режиме конструк­тора.

В Access можно создавать следующие типы запросов:

В перекрестном запросе отображаются результаты статистических расчетов (такие, как суммы, количество записей, средние значения), выполненных по данным из одного поля таблицы. Эти результаты группируются по двум наборам данных, один из которых распо­ложен в левом столбце таблицы, а второй - в верхней строке. Например, нам надо узнать средний стаж работы ассистентов, доцентов и профессоров на разных кафедрах (на основе таблицы Преподаватели). Перекрестный запрос позволит легко решить эту задачу, создав таблицу, в которой заголовками строк будут служить должности, заголовками столбцов - названия кафедр, а в ячейках будут рассчитаны средние значения стажа преподавателей.

Запрос на изменение - это запрос, который за одну операцию вносит изменения в не­сколько записей. Существует четыре типа запросов на изменение: на удаление, обновление и добавление записей, а также на создание таблицы.

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

Запрос на обновление записей вносит общие изменения в группу записей одной или нескольких таблиц. Например, на 10 процентов увеличилась заработная плата ассистентов. Запрос на обновление позволит быстро внести эти изменения в таблицу Преподаватели.

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

Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Например, на основе таблицы Преподаватели можно создать новую таблицу, содержащую данные только о профессорах.

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

Запрос на выборку используется наиболее часто. При его выполнении данные, удовле­творяющие условиям отбора, выбираются из одной или из нескольких таблиц и выводятся в определенном порядке. Например, можно вывести на экран данные о фамилиях доцентов, стаж которых более 15 лет (на основе таблицы Преподаватели). Можно также использовать запрос на выборку, чтобы сгруппировать записи для вы­числения сумм, средних значений, пересчета и других действий. Например, используя за­прос на выборку, можно получить данные о среднем стаже доцентов и профессоров (на основе таблицы Преподаватели). Простые запросы на выборку практически не отличаются от фильтров. Более того, фильтры можно сохранять как запросы.

Запрос с параметрами - это запрос, при выполнении которого в его диалоговом окне пользователю выдается приглашение ввести данные, на основе которых будет выполняться запрос. Например, часто требуются данные о том, какие дисциплины ведут преподаватели. Чтобы не создавать отдельные запросы по каждому преподавателю, можно создать один запрос с параметрами, где в качестве параметра будет использоваться фамилия преподавателя. При каждом вызове этого запроса вам будет предложено ввести фамилию преподавателя, а затем на экран будут выведены все поля, которые вы указали в запросе, например фамилия, имя, отчество преподавателя и читаемая им дисциплина.

Для создания нового запроса надо в окне базы данных выбрать вкладку Запросы и щелкнуть по кнопке <Создать>. Откроется окно «Новый запрос».

В окне вы должны выбрать один из пяти пунктов:

Конструктор,

Простой запрос.

Перекрестный запрос,

Повторяющиеся записи.

Записи без подчиненных.

Конструктор позволит вам самостоятельно создать любой тип запроса, но этот режим рекомендуется пользователям, уже имеющим некоторый опыт создания запросов.

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

При выборе пункта Повторяющиеся записи будет создан запрос на поиск повторяющихся записей в простой таблице или в запросе, а при выборе пункта

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

У вас может возникнуть вопрос: как создавать запросы с параметрами и запросы на изменение, если при создании запроса они явно не указаны? Следует отметить, что основой для всех этих запросов является запрос на выборку, т.е. сначала необходимо определить набор данных, с которым хотите работать. Затем для созданного запроса на выборку надо перейти в режим конструктора. Задание параметров производится в строке Условия отбора для соответствующих полей. Подробнее это будет рассмотрено ниже при выполнении задания. Для доступа к запросам на изменение надо открыть пункт меню Запрос - в открыв­шемся списке вы увидите все виды запросов на изменение.

При выполнении запроса на выборку Access извлекает записи из таблиц и формирует результирующий набор данных. Он выглядит, как таблица, хотя и не является ею. Результирующий набор данных является динамическим (или виртуальным) набором записей и не хранится в базе данных.

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

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

на физическом носителе информации (обычно это жесткий диск) требуется меньший объем пространства;

запрос может использовать обновленные версии любых записей, измененных со времени последнего запуска запроса.

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

Для сохранения запроса следует выполнить следующие действия. Выполните коман­ду Файл, Сохранить или щелкните по кнопке <Сохранить> на панели инструментов. Если вы впервые сохраняете запрос, введите новое имя запроса в диалоговом окне «Сохране­ние».

Запрос лучше всего создавать с помощью Конструктора (язык QBE). Для этого есть специальный значок в окне База данных . Он называется Создание запроса в режиме конструктора и открывает специальный окно в режиме языка QBE (см. рисунок 1). Окно состоит из двух частей. В верхней отображается структура таблиц, к которым запрос адресован, а нижняя область разбита на столбцы – по одному столбцу на каждое поле будущей результирующей таблицы.

Рис. 1 Окно создания запрос в режиме QBE

Памятка «Условия отбора»
Оператор	Описание
? Улица	Знак вопроса заменяет один символ
43 место*	Звездочка заменяет несколько символов, идущих после определенного слова
	Значение меньше 100
	Значение больше или равно 1
<>"Москва"	Все города кроме Москвы
Between 1 and 10	Значения между 1 и 10
Is Null Is Not Null	Находит пустые записи или находит все записи кроме пустых
Like "a*"	Все слова, начинающиеся с буквы а
>0 And <=10	Все значения большие 0 и меньшие 10
"Bob" Or "Jane"	Значения равные или Bob, или Jane

Лекция 16. Создание запросов средствами MS Access 2000

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

§ соединение данных нескольких таблиц;

§ отображение только требуемых полей;

§ формирование сложных критериев отбора записей;

§ вычисления с использованием данных из исходных таблиц;

§ группировку информации по каким-либо критериям;

§ модификацию данных в таблицах.

MS Access предоставляет удобные механизмы быстрого создания запросов без особой подготовки, тогда как в других СУБД часто требуются знание языковых средств, для создания запросов. Для создания запросов MS Access предлагает следующие средства:

§ Конструктор запросов – средство конструирования запросов с использованием QBE (Query by example – запрос по образцу), требует минимальных знаний. Средство, уступающее режиму SQL .

§ Простой запрос – позволяет быстро создавать запросы на основе одной или нескольких таблиц, в том числе итоговые запросы.

§ Перекрестный запрос – позволяет создавать результирующие таблицы на основе результатов расчетов, полученных при анализе группы таблиц;

§ Запрос на повторяющиеся записи – средство создания запросов для выявления повторяющихся записей, выполнено в виде мастера.

§ Записи без подчиненных – позволяет создать запрос, отыскивающий те записи главной таблицы, которые не имеют подчиненных записей в дочерних таблицах.

Создание запросов начинается с выбора категории «Запросы» окна диалога «Базы данных». Затем, кнопкой «Создать» может быть вызвано окно диалога «Новый запрос», в котором выбирается один из вариантов создания запроса (рисунок 16.1).

Рисунок 16.1 - Диалоговое окно «Новый запрос»

Создание простых запросов с помощью мастера

Мастер запросов позволяет сформировать запрос на основе одной или нескольких таблиц с возможностью группирования (итоговый запрос), но без определения условий выборки, именно поэтому он получил название «Простой запрос».

Рисунок 16.2 - Окно мастера создания запросов

Процесс создания запроса с помощью мастера сводится к следующим шагам:

§ Запускается мастер создания простых запросов, например командой – Создание запроса с помощью мастера

§ в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля (рисунок 16.2).

§ На втором шаге работы мастера определяется тип запроса: подробный или итоговый. Если выбран итоговый запрос, то необходимо определить итоговые операции над полями запроса: Max , Min , Sum , Avg или Count .

§ На третьем шаге указываем имя запроса и один из вариантов действий: открыть запрос для просмотра данных или изменить макет запроса , в первом случае результаты запроса будут выданы на экран, а во втором, запрос откроется в режиме конструктора.

Создание запроса в режиме конструктора

Конструктор редко используется для создания запросов, чаще используется как средство модификации уже существующих. Конструктор запросов использует QBE (Query by example – запрос по образцу) позволяющий сформировать относительно сложные запросы на основе специального бланка, заполнение которого позволяет сформировать запрос, безусловно, этот способ уступает непосредственному использованию конструкций языка SQL , но требует значительно меньших знаний.

При необходимости можно использовать язык SQL , для этого необходимо перейти в режим SQL , переход выполняется командой контекстного меню конструктора запросов – «Режим SQL ».

Окно конструктора (рисунок 16.3) разделено на две части. Верхняя часть предназначена для расположения таблиц, на основе которых формируется запрос. Добавление таблиц в запрос осуществляется командой панели инструментов - Отобразить таблицу .

Рисунок 16.3 - Окно конструктора запроса. Пример ввода условия.

Нижняя часть является бланком запроса (QBE), з десь выполняется построение самого запроса. Формирование запроса в QBE осуществляется определением таблиц и полей, участвующих в запросе, параметров вывода, сортировки, указанием условия отбора, вычислений и т.д.

Включение новых полей в запрос выполняется простым перетаскиванием имени поля из списков полей таблиц (верхней части конструктора) в требуемый столбец строки «Поле:», при этом имя таблицы определяется автоматически.

При необходимости сортировки по какому-либо полю в столбце этого поля устанавливается свойство «Сортировка» в значение По возрастанию или По убыванию , выбором из раскрывающегося списка.

Строка – «Вывод на экран» управляет отображением полей в результирующем наборе данных, полученном в результате выполнения запроса.

Для любого из полей можно указать условие отбора, тем самым, выполнив фильтрацию данных.

Поля запроса, наследуют свойства, заданные для соответствующих полей таблицы, но можно изменить формат вывода полей. Для изменения формата вывода полей используется команда контекстного меню – Свойства .

Количество строк в QBE может варьироваться в зависимости от типа создаваемого запроса, например при использовании группировки появится новая строка – «Групповая операция».

Установка критериев отбора записей

Определение критериев выборки в запросах является одной из основных задач. Чтобы сформировать требуемый пользователю набор данных необходимо определить значения условий отбора в строке «Условие отбора:» QBE. В условиях отбора можно использовать логические операторы (or , and , not ), операторы сравнения (<, >, <=, >=, <>, = ), а также операторы Between , In и Like .

Можно задать несколько условий отбора, соединенных логическим оператором или ( or ) , для некоторого поля одним из двух способов:

1. ввести всё условие в одну ячейку строки «Условие отбора:», с использованием логическогооператораили (or ).

2. одну часть условия указать в ячейке строки «Условие отбора:», а вторую часть условия вписать в нижнюю ячейку строки «Или:».

Оба варианта будут эквивалентными, второй вариант изображен на рисунке 16.4. Результатом этого запроса будет список студентов имеющих оценки 4 или 5.

Рисунок 16.4 - Использование логического оператора или ( or ) .

Логическая операцияи ( and ) используется в том случае, когда должны быть выполнены оба условия одновременно и только в этом случае запись будет включена в результирующий набор данных. Например, условие >2 and <5 даст список студентов имеющих оценки 3 и 4.

Логическая операция and может быть использована не только для одного поля, но и для нескольких полей, условия в которых должны выполниться одновременно, однако, в этом случае она используется неявно. На рисунке 16.5 показано неявное использование логической операции and . В результате такого запроса будут выданы все студенты, проживающие в городе «Уфа» и имеющие оценку 5. Фактически, если заглянуть в код SQL (в режиме SQL ) то можно увидеть следующее условие:

[Студенты].[Город] = “Уфа” and [Студенты и занятия].[Балл] = “5”

Рисунок 16.5 – Пример использования логической операции и ( and )

Операторы и и или применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или .

Оператор B etween позволяет задать диапазон значений, например диапазон от 10 до 20 может быть определен следующим образом:

between 10 and 20

Оператор I n позволяет использовать для сравнения список значений, указываемый в качестве аргумента. Например:

in (“первый”,”второй”,”третий”)

Оператор L ike полезен для поиска образцов в текстовых полях, причем позволяет использовать шаблоны:

* - обозначает любое количество символов;

Любой одиночный символ;

# - указывает, что в данной позиции должна быть цифра.

Например, для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать следующие условие:

Like П*ов

Особый подход к работе с полями типа дата. Значение даты в условиях отбора должно быть заключено в символы #. Например:

>#31.12.1996#

Если требуется выделить временные события конкретного дня, месяца или года, то можно использовать встроенные функции MS Access :

§ Day (дата) – возвращает значение дня месяца в диапазоне от 1 до 31;

§ Month (дата) – возвращает значение месяца в диапазоне от 1 до 12;

§ Year (дата) – возвращает значение года в диапазоне от 100 до 9999;

Текущую дату можно определить функцией Now (), используемую без аргументов.

Вычисляемые поля

В запросах на выборку можно определять вычисляемые поля, значения которых будут получены в результате каких-либо арифметических операций над полями участвующими в запросе. Например, если необходимо определить размер надбавки, равной 15% от оклада, то можно записать в строке «Поле» свободного столбца бланка запросов следующее выражение:

Надбавка: [ Оклад]*0.15

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

При создании выражений для вычисляемых полей можно использовать «Построитель выражений», который можно вызвать кнопкой «Построить», панели инструментов.

В верхней части окна область ввода, в которой редактируется выражение. Выражение может быть введено вручную, но обычно используются кнопки арифметических и логических операторов, расположенные под областью ввода. Нижний, средний список содержит перечень полей входящих в запрос, эти поля можно использовать в выражении. Поля добавляются двойным кликом мыши или нажатием кнопки «Вставить». пи поля полей входящих в запрос, которые можно использовать в выражении.ражений

Допустим, существует запрос «Отпуск товаров со склада», который содержит поля «КолОтп» (количество отпущенное) и «ЦОтп» (цена отпускная), требуется определить сумму продажи. Открываем запрос в режиме конструктора, переходим на пустой столбец и запускаем построитель выражений командой «Построить», контекстного меню . Находим в среднем списке имя поля «ЦОтп» и дважды щелкаем по нему мышкой. Нажимаем кнопку «*» и повторяем ту же последовательность действий для поля «КолОтп». Получаем требуемое выражение (рисунок 16.6).

Рисунок 16.6 - Окно построителя выражений

Прежде чем применить созданное выражение, рекомендуется определить имя вычисляемого поля, например «Сумма»:

Сумма: [ЦОтп]*[КолОтп]

После нажатия «ОК» полученный результат будет помещен в новый столбец бланка QBE.

Контрольные вопросы

1. Что такое запрос?

2. В чем заключаются функции запроса?

3. Какие способы создания запросов существуют в MS Access ?

4. Чем отличаются возможности различных способов создания запросов?

5. Как создается запрос с помощью мастера?

6. Что представляет собой бланк запросов?

7. Как сформировать условия отбора записей?

8. Назовите основные логические операторы.

9. Что представляет собой оператор b etween?

10. В чем назначение оператора Like ?

11. Как создаются вычисляемые поля?

12. Преимущества и недостатки конструктора запросов?

Задания для самостоятельной работы

Задание 1. Создайте запрос «Просроченные книги», по следующему описанию:

1. В окне «Базы данных» выбираем категорию «Запросы».

2. Открываем окно мастера командой «Создание запроса с помощью мастера».

3. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Книги». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Название» и «Автор».

4. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Экземпляры». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Инвентарный номер», «Дата возврата» и «Наличие».

5. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Читатели». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Номер_ЧБ», «ФИО», «Тел_дом», «Тел_раб». Нажимаем кнопку «Далее».

6. На данном этапе выбираем подробный запрос и нажимаем кнопку «Далее».

7. Указываем имя запросаПросроченные_книги и нажимаем кнопку «Готово».

8. Откроем запрос«Просроченные_книги» в режиме конструктора, нажав кнопку «Конструктор» (рисунок 16.7).

9. В бланке запроса в строке «Условие отбора» для поля «Дата_возврата» укажем значение < Now ().

10. В бланке запроса в строке «Условие отбора» для поля «Наличие» установим значение нет и снимем флажок «Вывод на экран».

11. Закроем окно конструктора.

Таким образом, отбираемые записи должны удовлетворять сразу двум условиям: дата возврата должна быть меньше текущей и книга должна отсутствовать в библиотеке.

Задание 2. Создайте простой запрос, на получение информации о том, к каким предметным областям относятся книги.

Задание 3. Из запроса «Просроченные_книги» извлеките информацию о должниках (Номер читательского билета, ФИО, Домашний и рабочий телефоны)

Задание 4. Создайте запрос на получение списка книг одного автора.