Лабораторная работа № 3 Создание запросов на выборку
Теоретические сведения
Запросы создаются пользователем для выборки необходимых данных из одной или нескольких связанных таблиц. Запрос может формироваться двумя способами:
с помощью запросов по образцу (QBE – Query By Example);
с помощью инструкций языка SQL (Structured Query Language).
Можно создать визуально почти все типы запросов (кроме запросов из других приложений, запросов на изменение структуры данных и запросов объединения).
В Access существует 4 типа запросов:
1. Запросы на выборку отбирают нужные поля данных из записей, удовлетворяющих заданному условию из одной или нескольких таблиц и/или запросов.
2. Перекрестные запросы формируют данные из таблиц и запросов в виде таблицы, в которой заголовки строк образуются из уникальных значений одного поля, заголовки столбцов – из уникальных значений другого поля, а на их пересечении вычисляются суммарные значения для отобранного множества записей.
3. Запросы на изменение.
4. Запросы с параметрами – это запросы любого типа, в которых критерии отбора задаются пользователем при каждом запуске запроса.
Результат выполнения запроса на выборку может быть динамическим или статическим. Результат динамического запроса является виртуальной таблицей, которая при закрытии запроса ликвидируется. Результат статического запроса запоминается в новой базовой таблице.
Запрос можно представить в одном из следующих режимов:
окно QBE – конструктор запроса;
страница SQL – текст запроса на языке SQL;
таблица – предварительный просмотр результатов запроса;
сводная таблица – просмотр результатов запроса в виде сводной таблицы;
сводная диаграмма – форма представления результатов сложных запросов.
Переключение режима можно выполнить посредством панели инструментов (левый крайний инструмент), контекстного меню или главного меню Вид. Изменения, сделанные в окне QBE, отображаются на странице SQL и наоборот. Запрос можно вводить с клавиатуры на языке SQL или конструировать в окне QBE, наблюдая результат в режиме таблицы, сводной таблицы или диаграммы. Результаты выполнения запроса выводятся в режиме таблицы. Можно менять данные в таблице результатов запроса на выборку и изменения будут внесены в базовые таблицы.
Для создания запроса надо выбрать в окне БД закладку Запрос и нажать кнопку Создать. В открывшемся окне Новый запрос из предложенных типов запросов следует выбрать Конструктор. В окне Добавление таблицы выбрать используемые в запросе таблицы и нажать кнопку Добавить. Затем кнопкой Закрыть выйти из окна Добавление таблицы. В результате появится окно конструктора запросов – Имя запроса: запрос на выборку. Верхняя панель окна содержит схему данных запроса, которая включает выбранные для данного запроса таблицы (рис. 24). Таблицы представлены списками полей. Нижняя панель является бланком запроса по образцу – QBE, который нужно заполнить.
Рис. 24. Создание запроса с помощью Конструктора запросов
В окне отображаются выбранные таблицы и связи между ними, имеющиеся в схеме данных. Access автоматически устанавливает дополнительные связи, которых не было в логической модели, если таблицы имеют поля с одинаковыми именами и типами данных. Логические связи между таблицами может создать пользователь, перетащив задействованные в связи поля из одного списка полей в другой. При использовании других запросов или таблиц, не представленных в схеме БД, с ними могут быть установлены связи объединения (связи без ключевого слова).
Бланк запроса по образцу (QBE) представлен в виде таблицы со следующими строками: Поле, Имя таблицы, Сортировка, Вывод на экран, Условие отбора, или. Каждый столбец бланка является полем запроса. Эти поля могут использоваться для включения их в результирующую таблицу, для сортировки по ним, а также для задания условий отбора записей. При заполнении бланка запроса необходимо:
в строку Поле включить имена полей, используемых в запросе;
в строке Вывод на экран отметить поля, которые должны быть включены в результирующую таблицу;
в строке Условия отбора задать условия отбора записей;
в строке Сортировка выбрать порядок сортировки записей результата.
Для включения нужных полей из таблиц БД в столбцы запроса можно:
в первой строке бланка запроса Поле щелчком мыши открыть список полей и выбрать из списка нужное поле;
перетащить нужное поле из списка полей таблицы в схеме данных запроса в первую строку бланка запроса.
В списке полей каждой таблицы на первом месте стоит символ *, означающий «все поля таблицы». Этот пункт выбирается, если в запрос включаются все поля.
Для добавления поля в бланк запроса надо перетащить его с помощью мыши из таблицы в схеме данных в нужное место бланка. Для удаления поля в бланке запроса надо выделить удаляемый столбец, щелкнув мышью в области маркировки столбца и нажать клавишу <Del> (команда Правка > Удалить столбец).
Для перемещения поля в бланке надо выделить столбец и перетащить его в новую позицию с помощью мыши. Столбец, на место которого перемещен новый, и все столбцы справа от него будут сдвинуты вправо.
Условия отбора записей могут задаваться для одного или нескольких полей в соответствующей строке бланка запроса. Условием отбора является выражение, которое состоит из операторов сравнения и логических операторов: =, <, >,<>, Between, In, Like, And, Or, Not. Если условие отбора не содержит оператора, то по умолчанию используется оператор =.
В качестве операндов могут использоваться литералы, константы и идентификаторы. Литералом являются числа, строки, даты. Константами являются значения True, False, Да, Нет, Null. Идентификатор представляет собой ссылку на значение поля, элемент управления или свойство. Идентификаторами могут быть имена полей, таблиц, запросов, форм, отчетов. Они заключаются в квадратные скобки. Если необходимо указать ссылку на поле в конкретной таблице, форме, отчете, то перед именем поля ставится имя таблицы, заключенное в квадратные скобки и отделенное от имени поля восклицательным знаком: [Имя таблицы] ! [Имя поля].
Сформировать условие отбора можно с помощью построителя выражения. Перейти в окно Построитель выражений можно, нажав кнопку Построитель на панели инструментов или выбрав команду Построить в контекстном меню. Курсор мыши должен быть установлен в ячейке ввода условия отбора. После ввода выражения в бланк и нажатия клавиши <Enter> выполняется синтаксический анализ выражения и отображение его в соответствии с результатами анализа. Примеры выражений, используемых в условиях отбора, приведены в табл. 8.
Таблица 8
Поле |
Выражение |
Описание |
ПунктНазначения |
"Москва" |
Отображает заказы на доставку товаров в Москву |
ПунктНазначения |
"Москва" Or "Санкт-Петербург" |
Использует оператор Or для отображения заказов на доставку товаров в Москву или Санкт-Петербург |
ДатаОтгрузки |
Between #05.01.03# And #10.01.03# |
Использует оператор Between ... And для отображения заказов на отгрузку товаров не ранее 5-янв-03 и не позднее 10-янв-03 |
ДатаОтгрузки |
#2/2/03# |
Отображает заказы на отгрузку товаров 2-фев-03 |
СтранаДоставки |
In("Россия"; "США") |
Использует оператор In для отображения заказов на доставку товаров в Россию или США |
СтранаДоставки |
Not "США" |
Использует оператор Not для отображения заказов на доставку товаров во все страны, за исключением США |
ИмяКлиента |
Like "С*" |
Заказы на доставку товаров клиентам, имена которых начинаются с буквы С |
Название |
>= "Н" |
Отображает заказы на доставку товаров в фирмы, названия которых начинаются с букв, находящихся в диапазоне от Н до Я |
ДатаЗаказа |
< Date( ) – 30 |
Использует функцию Date для отображения заказов, сделанных ранее, чем 30 дней назад |
ДатаЗаказа |
Year([ДатаЗаказа]) = 2003 |
Использует функцию Year для отображения заказов, сделанных в 2003 году |
ДатаЗаказа |
Year([ДатаЗаказа]) = Year(Now( )) And Month([ДатаЗаказа]) = Month(Now( )) |
Использует функции Year и Month, а также оператор And для отображения заказов текущего года и месяца |
ОбластьДоставки |
Is Null |
Отображает заказы для клиентов, у которых поле «ОбластьДоставки» является пустым |
ОбластьДоставки |
Is Not Null |
Отображает заказы для клиентов, у которых поле «ОбластьДоставки» содержит какое-либо значение |
Факс |
" " |
Отображает клиентов, у которых нет факсимильного аппарата (у которых поле «Факс» содержит пустую строку, а не значение Null) |
Для выполнения запроса необходимо на панели инструментов Конструктора запросов нажать кнопку Запуск (!). Записи в таблице можно сортировать с помощью кнопок сортировки на панели инструментов (команды меню Записи > Сортировка > Сортировка по возрастанию / Сортировка по убыванию). Для этого следует выбрать поля, используемые для сортировки. Современные СУБД не сортируют таблицы физически. Средства сортировки данных реализованы как автоматически создаваемые запросы. Фильтр – это набор условий, применяемых для отбора подмножества записей. В Access существуют: фильтр по выделенному фрагменту, обычный фильтр, расширенный фильтр и фильтр по вводу. Фильтрование данных производится с помощью кнопок <Фильтр по выделенному> или <Изменить фильтр> (команда меню Записи > Фильтр > Изменить фильтр). После нажатия второй кнопки от таблицы остается одна запись. Каждое поле становится полем со списком (когда в нем находится курсор), в котором можно выбрать все значения для данного поля. После щелчка по кнопке <Применить фильтр> будут выбраны записи, соответствующие измененному фильтру. Более сложные условия фильтрации можно задать командой Записи > Фильтр > Расширенный фильтр.
Запрос следует сохранить командой Файл > Сохранить, назначив уникальное имя. При этом сохраняется не результат запроса, а только его спецификация.
Дополнительными возможностями запросов на выборку являются:
расширение списка полей (вычисления по горизонтали);
группирование записей;
вычисления по вертикали.
Расширение списка полей – это операция, позволяющая добавить к списку полей запроса поле, которого нет в таблицах-источниках. Это поле должно представлять собой выражение, использующее знаки операций, функции, поля из любых таблиц или запросов, элементы управления объектов БД. Можно воспользоваться построителем выражений. Например, чтобы по Дате рождения вычислить Возраст, необходимо включить в список поле-выражение Возраст:Year(Date( )) – Year([Дата рождения]), где Возраст – имя нового поля в запросе, Year( ) –функция выделения года из даты, Date( ) – функция считывания текущей даты.
Группирование – это специальная операция, применяемая к результирующему множеству записей запроса на выборку. Она заключается в том, что все записи делятся на группы с одинаковыми значениями поля группирования, далее для каждой группы формируется одна запись, которая и будет включена в результат.
Вычисления по вертикали – это возможность конструктора запросов, связанная с операциями группирования и расширения (вычисления по горизонтали).
Прежде чем приступить к конструированию запроса, необходимо:
четко сформулировать содержание запроса на естественном языке;
определить, какие поля БД или выражения должны войти в запрос;
определить, какие таблицы и/или запросы должны войти в источник данных;
определить условия отбора записей и порядок сортировки записей.
Практическая работа
При выполнении лабораторной работы необходимо:
для заданной предметной области построить два запроса на выборку с помощью конструктора запросов по образцу QBE;
составить отчет по лабораторной работе.