Краткие теоретические сведения. Министерство транспорта Российской Федерации

2.2.1. Однотабличный запрос

2.2.1.1. Запрос на выборку

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

1) На ленте активизировать вкладку Создание и в группе Другие нажать пиктограмму (Конструктор запросов). В окне Добавление таблицы на вкладке Таблицы в списке доступных объектов двойным щелчком выбрать таблицу, на основе которой составляется запрос (например, Товары), и нажать кнопку <Закрыть>. В режиме конструктора запросов экран Microsoft Access 2007 имеет вид, представленный на рис. 1.

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

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

Рис. 1. Вид экрана Microsoft Access 2007 в режиме конструктора запросов

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

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

В строке Условие отбора задается критерий отбора записей в зависи­мости от значений указанного поля. Во многих случаях при вводе условия отбора или других параметров запроса целесообразно применить Построитель выражений (рис. 2), окно которого открывается щелчком на пиктограмме в группе Настройка запроса на ленте или в контекстном меню соответствующего столбца бланка. Окно построителя выражений содержит полный набор кнопок для ввода арифметических и логических операций, а также списки всех объектов базы данных, в том числе списки таблиц и их полей. В условии отбора, формируемом с помощью построителя выражений, во многих случаях автоматически появляется слово «Выражение», которое обычно следует удалить.

Рис. 2. Вид окна построителя выражений

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

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

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

Пример 1. Выведите записи о товарах, произведенных в России, и отсортируйте их по наименованию товаров в алфавитном порядке.

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

Рис. 3. Вид бланка запроса для примера 1

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

Пример 2. Выведите записи о любых ручках, цена которых составляет не менее 25 и не более 35 р., в порядке убывания цены.

Вид бланка запроса для решения этого задания представлен на рис. 4. В условии отбора в столбце Товар имеется ключевое слово Like, с помощью которого можно осуществлять поиск записей, содержащих в поле Товар текст, указанный в кавычках после слова Like. При записи текста можно применять звездочку (*), означающую любое количество любых символов, и вопросительный знак (?), означающий один любой символ. В данном примере запись Like "Ручка*" означает, что будут отображены все записи, в которых значение поля Товар начинается со слова «Ручка».

Для реализации комбинированного критерия поиска записей со стои­мостью товара в указанном диапазоне в столбце Цена в строке Условие отбора применена логическая операция And, означающая «и». Ввод любого условия отбора можно выполнять непосредственно в ячейке бланка или с помощью построителя выражений. Создайте указанный запрос, запустите его на исполнение и проверьте результат работы.

Рис. 4. Вид бланка запроса для примера 2

Пример 3. Выведите записи о любых карандашах, произведенных в России или Чехии. Упорядочите записи по названию страны и фирмы-производителя.

Вид бланка запроса для решения этого задания приведен на рис. 5. При этом в условии отбора по полю Страна применена логическая операция Оr, означающая «или», а в условии отбора по полю Товар – ключевое слово Like (аналогично предыдущему примеру). Запустите запрос на исполнение и убедитесь в правильности его работы.

Рис. 5. Вид бланка запроса для примера 3

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

1) представьте записи таблицы Товары, в которых товар измеряется
поштучно, при этом на экран выведите только поля Товар и Цена, упорядочив записи по уменьшению цены;

2) выведите записи таблицы Реестр продаж, отражающие продажи товаров летом 2009 года в количестве более 1000 единиц, упорядочив записи по
дате продажи.

2.2.1.2. Запрос с вычисляемым полем

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

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

– имена полей таблиц, заключенные в квадратные скобки;

– встроенные функции СУБД;

– знаки арифметических операций;

– числовые константы.

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

1) Установить курсор в ячейку бланка с формулой.

2) На вкладке ленты Конструктор в группе Показать или скрыть нажать пиктограмму или выбрать опцию Свойства в контекстном меню.

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

4) Закрыть окно свойств.

Рассмотрим особенности создания запроса с вычисляемым полем на конкретном примере.

Пример 4. Выведите все записи таблицы Товары, дополнительно указав цену каждого товара в долларах при курсе 1$ = 31,26 р. Установите сортировку записей по наименованию товара в алфавитном порядке.

Для решения этого задания сначала аналогично примерам 1 – 3 создайте запрос на выборку данных из таблицы Товары, включив в бланк запроса поля
Товар (по условию задания установите для него сортировку по возрастанию), Страна, Фирма, Ед_изм, Цена.

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

1) В бланке запроса в свободном столбце после столбца Цена установите курсор в ячейку в строке Поле.

2) Запустите построитель выражений, в нижней части окна на левой панели откройте таблицу Товары, на средней панели выберите поле Цена (см. рис. 2) и нажмите кнопку <Вставить>. Введите знак деления, а за ним – заданный курс доллара – 31,26 (разделителем между целой и дробной частями числа является запятая). В результате в рабочем окне построителя будет записана расчетная формула в виде: [Товары]![Цена]/31,26. Нажмите кнопку <ОК> – указанная формула будет помещена в текущую ячейку бланка.

3) Щелкните любую другую ячейку бланка. При этом в начале ячейки с формулой появится текст Выражение1, который служит приглашением для ввода имени вычисляемого поля. Увеличьте ширину столбца с формулой (лучше это сделать двойным щелчком, как указано в п. 2.2.1.1) и вместо имени
Выражение1 введите имя вычисляемого поля, которое будет отображаться при выводе таблицы, например: Цена, $.

4) В соответствии с рекомендациями, приведенными выше, задайте формат вывода результатов расчета с разделителями разрядов.

5) Установите для вычисляемого поля флажок в строке Вывод на экран.

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

Рис. 6. Вид бланка запроса для примера 4

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

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

Пример 5. Выведите записи о всех продажах товара в магазине «Степлер» с указанием полученного дохода в порядке его убывания.

Сведения о продажах товара во всех магазинах с указанием его количества представлены в таблице Реестр продаж, поэтому она будет являться основным источником информации при создании запроса. Доход от продажи товара определяется умножением количества проданного товара на стоимость единицы товара. В связи с этим помимо таблицы Реестр продаж для выполнения
задания понадобится поле Цена из таблицы Товары. Кроме того, в таблице
Реестр продаж хранятся лишь коды товара и магазина, для расшифровки которых потребуются соответствующие поля таблиц Товары и Магазины.

Активизируйте конструктор и добавьте в схему данных запроса по очереди таблицы Магазины, Реестр продаж и Товары. Заполните бланк запроса в соответствии с рис. 7. При этом вычисляемое поле Доход создайте аналогично полю Цена, $ в примере 4. При вводе расчетной формулы выбирайте поля Количество и Цена в списках полей соответствующих таблиц в окне построителя выражений. Обратите внимание на то, что нет необходимости включать в запрос коды магазинов и товаров из таблицы Реестр продаж, поскольку можно сразу вывести их наименования из других таблиц благодаря установленным связям.

Запустите запрос на исполнение и проверьте соответствие результата его работы условию задания (рис. 8). Обратите внимание на то, что заданная сортировка по убыванию значений поля Доход позволяет быстро выявить продажи товаров, принесшие наибольший доход.

Рис. 7. Вид окна конструктора запроса для примера 5

Рис. 8. Начальный фрагмент результирующей таблицы для примера 5

2.2.3. Запрос с параметром

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

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

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

Рис. 9. Вид окна конструктора запроса для примера 6

2.2.4. Запрос с групповыми операциями