5 семестр - ИПОВС / БД лабы / БДлаб3

Лабораторная работа № 3

Создание и использование запросов

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

Продолжительность работы - 4 ч.

Теоретические сведения

В современных СУБД для взаимодействия с базами данных предусмотрены спе-

циальные языки, одним из которых является табличный язык запросов по образцу QBE (Query By Example), предназначенный для работы в диалоговом режиме и ориентиро-

ванный на обычного пользователя. Язык QBE реализован во многих СУБД, например, dBase, Paradox, Access и т.д. Конкретные реализации несколько отличаются друг от дру-

га, но построены по единому принципу.

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

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

стики запроса.

Рис. 1. Вид окна запроса

В Access предусмотрено несколько типов запросов: запрос на выборку, создание таблицы, перекрестный запрос, запрос на обновление, добавление, удаление и др. Наи-

более часто используются запросы на выборку. В области переходов тип запроса обо-

значается соответствующим типу значком перед именем запроса. Тип формируемого или

открытого запроса дополнительно индицируется на ленте в коллекции “Тип запроса” цветовым выделением команды, соответствующей типу запроса.

Создание и сохранение запроса

В Access для создания запроса следует на ленте активизировать вкладку “Созда-

ние” и выбрать на ней команду “Конструктор запросов”. В активизированном окне “До-

бавление таблицы” (рис. 2) на вкладках осуществляется выбор источников данных для запроса.

Перед созданием запроса определяются таблицы, содержащие исходную инфор-

мацию. В Access допускается создавать запрос также на основе других запросов или од-

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

сколько последовательно выполняемых обойтись не удается.

Рис. 2. Выбор источников данных для запроса

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

окне кнопки Office. Имя сохраняемого запроса не должно совпадать с именем другого запроса или именем какой-либо таблицы в открытой базе данных.

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

жимов можно задать, выбрав его из меню команды “Режим”, расположенной на ленте, а

режим таблицы устанавливается также командой “Выполнить”, расположенной на кон-

2

текстной вкладке “Конструктор” ленты (рис. 3). Выбор режима изменяет вид окна за-

проса.

Рис. 3. Лента с контекстной вкладкой “Конструктор”

Добавление и удаление таблиц

Чтобы добавить таблицу в окно запроса, нужно в режиме конструктора воспользо-

ваться командой “Отобразить таблицу”, расположенной на контекстной вкладке “Конст-

руктор” ленты. После этого должно активизироваться окно “Добавление таблицы” (см.

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

мый запрос (таблице, запросе или том и другом одновременно), нужно выбрать соответ-

ствующую вкладку, выделить источник данных и нажать кнопку “Добавить”. Добавляе-

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

Удалить из запроса ошибочно включенную или ставшую ненужной таблицу мож-

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

той базе данных.

Включение полей в запрос и их удаление из запроса

После размещения выбранных источников данных в верхней области окна запро-

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

вует несколько способов включения поля источника данных в бланк запроса: перетаски-

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

Выделение полей, подлежащих переносу, осуществляется стандартными способа-

ми: нажать клавишу Shift для выделения полей, расположенных друг за другом, и, удер-

3

живая ее, выбрать первое и последнее поле; при нажатой клавише Ctrl выбрать поля,

располагающиеся в произвольном порядке (не подряд друг за другом).

Можно выделить одновременно все поля таблицы, если дважды щелкнуть по за-

головку списка полей или установить указатель на символ звездочки (*) в списке полей и нажать кнопку мыши.

Чтобы удалить поле из запроса, нужно выделить соответствующую графу в бланке запроса щелчком мыши по заголовку графы, когда указатель мыши примет форму на-

правленной вниз стрелки, и затем нажать клавишу Delete.

Выбор полей, отображаемых в таблице результатов

Не все поля, которые используются при формировании запроса, должны обяза-

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

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

Надо учитывать, как поля были включены в запрос. Так как поля, включенные в запрос путем использования “*”, в явном виде в бланке запроса не присутствуют, то те поля, которые используются в условии отбора, надо дополнительно включить в бланк запроса. Чтобы эти поля дважды не выводились в таблицу результатов, надо у этих по-

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

Выбор записей, включаемых в таблицу результатов

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

ловие, задаваемое в строке “Условие отбора” и последующих строках бланка запроса.

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

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

ных.

4

При создании простого запроса условия отбора записываются в графы бланка за-

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

формацию о конкретном студенте, то в графы “Фамилия”, “Имя”, “Отчество” в строке

“Условие отбора” надо записать реквизиты нужного студента (рис. 4).

Рис. 4. Простой запрос

В большинстве СУБД при вводе в выражение значений того или иного типа ис-

пользуются соответствующие данному типу данных ограничители. В Access при форми-

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

В столбце можно записывать не только значение поля, но и знак операции сравне-

ния; по умолчанию принимается знак “=”. Если получить список всех студентов, родив-

шихся после 1 января 1990 года, то условие отбора в графе для поля “ДатаРождения” будет набрано как неравенство >01.01.1990, которое автоматически преобразуется к виду

>#01.01.90#.

В условиях отбора можно задавать диапазон значений, используя операторы Be- tween-And, например: Between 01.01.1990 And 31.12.1990. Это же условие отбора можно записать иначе: >=01.01.1990 And <=31.12.1990.

Возможны запросы с открытыми двусторонними диапазонами. Например, для формирования списка студентов, родившихся до 1 января 1990 года или после 31 декаб-

ря 1990 года, условие отбора надо задать следующим образом: <01.01.1990 Or >31.12.1990.

5

Для поиска текстовых значений по шаблону в условии отбора используется опера-

тор Like. Например, для поиска студентов, проживающих в Московской области, в графе для поля “Адрес” в качестве условия отбора следует записать: Like *Московская*. Стро-

ка “*Московская*” является шаблоном, в котором используется подстановочный знак

“*”, обозначающий любую последовательность символов. Кроме звездочки в качестве подстановочных знаков в Access предусмотрены еще несколько символов, информацию о которых можно получить в справочной системе СУБД Access.

Для формирования условия отбора можно воспользоваться построителем выраже-

ний, который вызывается командой “Построитель”, находящейся на контекстной вклад-

ке “Конструктор” ленты.

Если в условиях отбора используется несколько полей, то они могут соединяться оператором “И” либо “ИЛИ”. Если аргументы поиска записаны в одной строке, то счи-

тается, что они соединены оператором “И” (“And”). Если аргументы поиска записаны в разных строках, то считается, что они соединены оператором “ИЛИ” (“Or”). На рис. 5

показаны примеры таких запросов. Первый запрос выдает список проживающих в Мо-

скве студентов группы с кодом 1 (оператор “И” - операнды запроса расположены на од-

ной строке), второй - сведения о студентах, которые проживают в Москве или учатся в группе с кодом 1 (оператор “ИЛИ” - операнды запроса расположены на разных строках).

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

(рис. 6). По этой причине язык QBE, реализованный в Access, называется табличным двумерным языком запросов: результат зависит от взаимного расположения операндов запроса относительно друг друга.

а

6

б

Рис. 5. Сложные запросы с операторами “И” (а) и “ИЛИ” (б)

а

б

Рис. 6. Таблицы результатов запросов с операторами “И” (а) и “ИЛИ” (б)

Выполнение запроса, просмотр и сортировка результатов

Для того чтобы выполнить запрос и посмотреть таблицу результатов, можно вы-

брать команду “Выполнить” (“!”) на контекстной вкладке “Конструктор” ленты (см. рис.

3).

Порядок сортировки записей в таблице результатов указывается на бланке запроса в строке “Сортировка” (см. рис. 5) перед выполнением запроса. Возможна сортировка по возрастанию или убыванию значений одного или нескольких полей одновременно.

Если анализ результатов запроса указывает на необходимость корректировки за-

проса, то для этого следует перейти в режим конструктора.

Формирование запросов к связанным таблицам

Если в базе данных определены связи таблиц на схеме данных, то при добавлении таблиц в запрос связи между ними будут показаны в окне запроса. Более того, даже если

7

связи между таблицами не были созданы пользователем предварительно, то при добав-

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

1)в окне кнопки Office нажать кнопку “Параметры Access”;

2)щелкнуть на ссылке “Конструкторы объектов”;

3)в разделе “Конструктор запросов” установить или снять флажок “Разрешить автообъединение”.

Параметр флажок “Разрешить автообъединение” относится только к новым запро-

сам.

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

Итоговые запросы

Итоговые запросы относятся к запросам на выборку и позволяют подводить ито-

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

тистических функций Sum (сумма), Count (число значений или записей, возвращаемых запросом), Avg (среднее), Var (дисперсия) и т.д. Поскольку итоговые вычисления имеют смысл только для группы записей, то предварительно записи надо сгруппировать по ка-

кому-либо признаку.

Создания итогового запроса начинается с выбора таблиц, участвующих в запросе.

Затем выбором команды “Итоги” на контекстной вкладке “Конструктор” ленты (см. рис. 3) в бланк запроса добавляется строка “Групповая операция” для задания групповых операций над полями. В этой строке для нужного поля выбирается одна из статистиче-

ских функций для вычисления итогов.

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

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

8

Первыми полями, выводимыми в таблицу результатов, должны быть поля, по ко-

торым производится операция “Группировка”, а затем – поля, над которыми произво-

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

кроме функций Count, Min, Max, могут выполняться только над числовыми полями.

Рис. 7. Формирование итогового запроса

Бланк запроса может содержать также условия отбора, с помощью которых опре-

деляются группы записей, для которых вычисляются итоговые значения, записи, вклю-

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

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

Count и выводит список учебных групп, в которых число студентов больше 25.

Примечание. Если из этого итогового запроса исключить условие отбора, то он на основе данных, хранящихся только в таблице “Студент”, определит число студентов в каждой учебной группе, т.е. те величины, которые хранятся в поле “ЧислоСтудентов” таблицы “Группа” базы данных “Институт” из лабораторной работы № 2. Следовательно, поле “ЧислоСтудентов” содержит избыточные (“лишние”) данные, которые могут быть в любой момент вычислены запросом. Это поле с вычисляемыми данными целесообразно исключить из базы данных не только для экономии компьютерной памяти, но и для того, чтобы избежать появления ошибочных сведений в базе данных, поскольку есть вероятность того, что при добавлении или исключении сведений о студентах в таблице “Студент” не будут оперативно скорректированы сведения о числе студентов в таблице “Группа”.

Создание запроса с параметрами

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

ранда в ответ на приглашение в автоматически отображаемом окне при выполнении за-

проса.

9

Для каждого поля, в котором предполагается использовать параметры, в соответ-

ствующую ячейку строки “Условие отбора” бланка запроса необходимо вместо операн-

да ввести текст приглашения, заключенный в квадратные скобки. Например, для запро-

сов, показанных на рис. 4, 5, 7, можно предусмотреть следующие параметры в условиях отбора:

1) для полей “Фамилия”, “Имя”, “Отчество” (см. рис. 4) в ячейки строки “Усло-

вие отбора” ввести соответствующие приглашения: [Введите фамилию], [Введите имя], [Введите отчество];

2) для поля “Адрес” (см. рис. 5) в ячейку строки “Условие отбора” ввести при-

глашение на месте шаблона: Like [Введите шаблон для адреса] ;

3) для поля “КодСтудента” (см. рис. 7) в ячейку строки “Условие отбора” ввести приглашение вместо числовой константы: > [Введите число студентов].

Запрос на создание таблицы

Запрос на создание таблицы фактически означает сохранение таблицы результа-

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

ку, результаты которого требуется сохранить, отметив на бланке запроса в строке “Вы-

вод на экран” поля, включаемые в создаваемую таблицу, а затем на контекстной вкладке

“Конструктор” выбрать команду “Создание таблицы”, которая активизирует одноимен-

ное окно (рис. 8). В этом окне указывается имя таблицы, в которой будут сохранены вы-

бранные данные.

Для просмотра содержимого таблицы до ее создания на контекстной вкладке

“Конструктор” следует выбрать команду “Режим”. Повторный выбор этой команды обеспечивает возврат в режим конструктора, в котором можно изменить или выполнить запрос.

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

Запрос, показанный на рис. 8, выбирает из таблиц базы данных “Институт” запи-

си, содержащие шифры групп и ФИО студентов факультета МПиТК, и сохраняет эти данные в таблице “ФакультетМП”.

10