Создание запросов средствами MS Access 2000
Лекция 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)
Операторы и и или применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или.
Оператор Between позволяет задать диапазон значений, например диапазон от 10 до 20 может быть определен следующим образом:
between 10 and 20
Оператор In позволяет использовать для сравнения список значений, указываемый в качестве аргумента. Например:
in (“первый”,”второй”,”третий”)
Оператор Like полезен для поиска образцов в текстовых полях, причем позволяет использовать шаблоны:
* - обозначает любое количество символов;
? - любой одиночный символ;
# - указывает, что в данной позиции должна быть цифра.
Например, для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать следующие условие:
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. Что представляет собой оператор between?
10. В чем назначение оператора Like?
11. Как создаются вычисляемые поля?
12. Преимущества и недостатки конструктора запросов?
Задания для самостоятельной работы
Задание 1. Создайте запрос «Просроченные книги», по следующему описанию:
1. В окне «Базы данных» выбираем категорию «Запросы».
2. Открываем окно мастера командой «Создание запроса с помощью мастера».
3. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Книги». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Название» и «Автор».
4. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Экземпляры». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Инвентарный номер» , «Дата возврата» и «Наличие».
5. В раскрывающемся списке «Таблиц и запросы» выбираем таблицу «Читатели». Из списка «Доступные поля» в список «Выбранные поля» перемещаем поля «Номер_ЧБ», «ФИО», «Тел_дом», «Тел_раб». Нажимаем кнопку «Далее».
6. На данном этапе выбираем подробный запрос и нажимаем кнопку «Далее».
7. Указываем имя запроса Просроченные_книги и нажимаем кнопку «Готово».
8. Откроем запрос «Просроченные_книги» в режиме конструктора, нажав кнопку «Конструктор» (рисунок 16.7).
Рисунок 16.7 Запрос «Просроченные_книги» в режиме конструктора
9. В бланке запроса в строке «Условие отбора» для поля «Дата_возврата» укажем значение < Now().
Вместе с этой лекцией читают "Введение и содержание".
10. В бланке запроса в строке «Условие отбора» для поля «Наличие» установим значение нет и снимем флажок «Вывод на экран».
11. Закроем окно конструктора.
Таким образом, отбираемые записи должны удовлетворять сразу двум условиям: дата возврата должна быть меньше текущей и книга должна отсутствовать в библиотеке.
Задание 2. Создайте простой запрос, на получение информации о том, к каким предметным областям относятся книги.
Задание 3. Из запроса «Просроченные_книги» извлеките информацию о должниках (Номер читательского билета, ФИО, Домашний и рабочий телефоны)
Задание 4. Создайте запрос на получение списка книг одного автора.
