КР Информационные системы в экономике. 1. Рынок информационных услуг. Правовое регулирование на информационном рынке
 Скачать 1.49 Mb.
|
|
2. Запросы: итоговые, параметрические, перекрестные, многотабличные. Использование Мастера запросов 2.1 Создание запроса Прежде чем приступить к созданию запроса, следует продумать ответы на следующие вопросы: какая таблица или таблицы содержит нужную информацию; как связать эти таблицы; какой тип запроса использовать; каким условиям должны удовлетворять отбираемые записи; каким должен быть порядок их сортировки; какие вычисления нужно выполнить над отобранными данными; какое имя должен получить создаваемый запрос; Для создания запроса нужно щелкнуть по корешку Запросы окна БД, а затем по кнопке Создать над списком запросов. На экране появится диалоговое окно Новый запрос, и Access предоставит выбор: создать запрос самому в режиме конструктора или воспользоваться помощью одного из мастеров по разработке запросов [6, c.71]. С помощью мастеров можно создать запросы следующих типов: Простой запрос. Создается запрос на выборку из указанных полей. Перекрестный запрос. Выводит данные в формате электронной таблицы. Поиск повторяющихся записей. Осуществляется поиск повторяющихся записей в указанной таблице или запросе. Поиск записей, не имеющих подчиненных. Находит все записи в главной таблице, не имеющие связанных с ними записей в подчиненной таблице. 2.2 Использование мастера Простой запрос Для быстрого создания запроса можно воспользоваться мастером Простой запрос. Этот мастер позволяет не только отобрать нужные поля из таблиц или ранее созданных запросов, но также суммировать, вычислять средние значения и находить другие статистические характеристики определенных групп записей. После выбора этого варианта появится диалоговое окно Создание простых запросов. В раскрывающемся списке Таблицы/запросы нужно выбрать таблицу или запрос и с помощью двойного щелчка отобрать поля, которые будут содержаться в создаваемом запросе. Если в запросе используются поля из нескольких таблиц или запросов, то эту операцию отбора полей следует повторить нужное число раз. Отметим, что мастер не разрешит использовать таблицы, между которыми не были установлены связи. Затем нужно выбрать тип создаваемого запроса. Если нужно, чтобы отображались все записи, требуется щелкнуть на переключателе Подробный. Для вывода только общей информации о записях щелкните на переключателе Итоговый и затем по кнопке Итоги. После этого указать, какие итоговые значения необходимо вычислять, и щелкнуть по кнопке ОК. В последнем диалоговом окне нужно ввести имя запроса и выбрать дальнейшие действия: просмотр результатов выполнения запроса в режиме таблицы или изменение его структуры в режиме конструктора. Затем следует щелкнуть по кнопке Готово [6, c.74]. 2.3 Использование режима Конструктор Больше возможностей для создания запроса предоставляет конструктор запросов. Чтобы создать запрос с его помощью, нужно выбрать пункт Конструктор в диалоговом окне Новый запрос. На экране появится окно Запрос на выборку, а поверх него окно Добавление таблицы со списком таблиц, хранящихся в текущей БД. По мере того как вы будете выбирать нужные таблицы, Access помещает списки их полей в верхней части окна запроса. Для выбора таблицы достаточно дважды щелкнуть мышью по ее имени или щелкнуть сначала по ее имени, а затем по кнопке Добавить. Если в создаваемом запросе вы хотите использовать поля из ранее созданного запроса, то нужно щелкнуть по ярлыку Запросы или Запросы и таблицы. В этом случае список таблиц будет заменен или, соответственно, расширен списком уже существующих запросов, из которого можно выбрать нужный запрос. После завершения отбора нужных таблиц и запросов следует нажать кнопку Закрыть. Чтобы снова открыть окно Добавление таблицы, нужно щелкнуть по кнопке Добавить таблицу  , расположенной на панели инструментов, или выбрать команду Добавить таблицу в меню Запрос (рис.2.1). Рисунок 2.1 - Окно запроса в режиме конструктора Если вы включили в запрос несколько таблиц, и между ними были установлены связи, то Access автоматически показывает их, рисуя линии объединения между связанными полями. Линию объединения можно создать самостоятельно, переместив ключевое поле главной таблицы на связываемое с ним поле подчиненной таблицы. 2.4 Использование параметров в условиях отбора В условиях отбора можно использовать не только конкретные значения, но и параметры. Перед выполнением запроса, содержащего один или несколько параметров, Access каждый раз будет запрашивать конкретные условия отбора и затем произведет отбор нужной информации. Используя параметры, можно заменить несколько однотипных запросов к данным одним запросом. Чтобы задать параметр, нужно ввести в строку Условие отбора вместо конкретного значения произвольный текст, заключенный в квадратные скобки [ ]. Этот текст Access рассматривает как имя параметра и выводит его в специальном диалоговом окне при выполнении запроса. Поэтому в качестве имени параметра лучше использовать содержательную фразу, причем имя каждого параметра должно быть уникальным. Для каждого параметра запроса можно указать тип данных. Эту информацию Access использует для проверки введенного значения. Например, если параметр определен как числовой, то Access не разрешит ввести значение, содержащее буквы. По умолчанию все параметры запроса имеют текстовый тип. Для задания типа данных параметра нужно выбрать пункт меню Запрос, а затем Параметры или вызвать щелчком правой кнопки мыши контекстное меню запроса и выбрать в нем пункт Параметры. Access выведет на экран диалоговое окно Параметры запроса. В этом окне нужно ввести в столбце Параметр имя того параметра, тип которого следует изменить, точно в том же виде, в каком оно было введено в бланк запроса, но без квадратных скобок. В столбце Тип данных следует выбрать из раскрывающегося списка нужный тип данных параметра. После того как определены типы всех параметров, нужно нажать кнопку ОК. При выполнении запроса Access попросит ввести одно за другим значения для каждого из параметров, используя для ввода диалоговые окна Введите значение параметра. После ввода всех значений запрос будет выполнен и на экране появится результирующий набор записей. Пример 2.1 Чтобы получить список студентов любой учебной группы, используем запрос с параметром. Для его создания нужно включить в бланк запроса к таблице Студенты поля Группа, Фамилия и Имя. В строке Условие отбора в поле Группа следует ввести текст Введите номер группы и заключить его в квадратные скобки (см. рис. 2.2).  Рисунок 2.2 - Пример запроса с параметром При выполнении запроса появится диалоговое окно с приглашением ввести номер группы (см. рис. 2.3).  Рисунок 2.3 - Окно для ввода параметра После ввода номера и нажатия кнопки ОК Access выполнит запрос и создаст требуемый список группы. Параметры могут использоваться в условиях отбора совместно с шаблонами. Например, для нахождения всех студентов, фамилия которых начинается с данной буквы, нужно включить в бланк запроса к таблице Студенты в поле Фамилия условие Like [введите букву] & "*". 2.5 Многотабличные запросы Часто возникает необходимость в создании запроса, отбирающего информацию из нескольких таблиц или ранее сохраненных запросов. В этом случае списки полей всех используемых таблиц/запросов должны быть включены в окно конструктора создаваемого запроса. Процедура добавления таблиц/запросов в запрос, создаваемый в режиме конструктора, была описана выше. Далее рассматриваются различные типы установления связи (объединения) таблиц/запросов, находящихся в окне конструктора, и соответствующие им принципы отбора записей в запросе. Задание связей между таблицами — важный этап конструирования запроса и сделанная на этом этапе ошибка, как правило, приводит к тому, что запрос дает неверный результат. Отметим, что обычно Access устанавливает связи добавленной таблицы с другими таблицами автоматически. Это происходит в следующих случаях: таблицы были связаны между собой в окне Схема данных; информация о связи между ними берется из схемы данных; связь (внутреннее объединение) устанавливается между таблицами, имеющими поля с одинаковыми именами, если одно из них ключевое. Однако нередко возникает ситуация, когда приходится либо задавать связь самостоятельно, либо переопределять тип связи, которую установил Access. Рассмотрим основные принципы отбора записей, соответствующие различным типам объединения таблиц, на примере двух таблиц А и В. Каждая из них содержит по одному текстовому полю и две записи (см. рис. 2.4).  Рисунок 2.4 Содержимое таблиц А и В 2.5.1 Случай несвязанных таблиц Начнем с рассмотрения ситуации, когда таблицы А и В включены в запрос, но между ними не установлена связь. В этом случае Access формирует динамический набор данных, записи которого являются декартовым произведением1 записей исходных таблиц. Его можно вывести на экран, включив поля обеих таблиц в бланк запроса (см. рис. 2.5).  Рисунок 2.5 - Результат выполнения запроса Инструкция SQL, соответствующая этому запросу, имеет вид: SELECT А.a1, В.b1 FROM А, В; Если включить в бланк запроса поле только одной таблицы, например А (см. рис. 2.6), то результат выполнения запроса будет следующим: итоговый набор содержит записи таблицы А, но они продублированы: их не две, как можно было бы ожидать, а четыре (см. рис. 2.7).  Рисунок 2.6 - Еще один запрос с несвязанными таблицами  Рисунок 2.7 - Результат запроса Таким образом, сам факт присутствия в окне конструктора посторонней таблицы (В), не связанной с основной таблицей (А), используемой в запросе, приводит к дублированию отбираемых записей. Появление лишних записей вызвано тем, что Access в этом случае создает такой же динамический набор, как и в предыдущем запросе (декартово произведение таблиц А и В). Однако на этот раз выводятся на экран не оба столбца набора, а лишь первый столбец. Ясно, что число дубликатов записей таблицы А в итоговом наборе равно количеству записей, содержащихся в таблице В. Инструкция SQL для запроса, представленного на рис. 2.6, имеет вид: SELECT А.a1 FROM А, В; Она отличается от инструкции SQL предыдущего запроса лишь списком выводимых полей. Итак, если в окне конструктора находятся две несвязанные таблицы, то Access рассматривает в качестве исходной таблицы их декартово произведение и число обрабатываемых им логических записей равно числу строк первой таблицы, умноженному на число строк второй таблицы. Обычно наличие несвязанных таблиц в окне конструктора —следствие невнимательности пользователя, создающего запрос. Однако можно добавить таблицу в запрос, не связывая ее с другими таблицами, для того чтобы иметь возможность ссылаться на значения ее полей. Например, для получения списка студентов младше студента Иванова можно поступить следующим образом (см. также пример 2.7): Создать и сохранить запрос, дающий дату рождения Иванова. Поместить в окно конструктора таблицу Студенты и сохраненный запрос, не связывая их. Поместить в бланк запроса поля Фамилия, Имя, Дата рождения и в поле Дата рождения задать условие отбора: >[Дата рождения Иванова]![Дата рождения] Выполнить запрос. Присутствие в окне конструктора запроса Дата рождения Иванова позволяет использовать его значение в условии отбора (см. рис. 2.8).  Рисунок 2.8 - Использование несвязанных таблиц 2.5.2 Внутреннее объединение таблиц Чтобы установить связь между таблицами А и В, нужно переместить поле одной таблицы на связываемое с ним поле другой таблицы. Access покажет установленную связь в виде тонкой линии. Включим оба связанных поля в бланк запроса и выполним созданный запрос (см. рис. 2.9). Результирующий набор данных содержит лишь одну запись, причем значения полей совпадают (см. рис. 2.10).  Рисунок (5.15) 2.9 - Запрос со связанными таблицами  Рисунок (5.16) 2.10 - Результат запроса со связанными таблицами Мы видим, что при создании связи между таблицами А и В Access объединяет в динамическом наборе записи, имеющие одинаковые значения в связанных полях. Такой тип объединения называется внутренним. Конечно, если запрос содержит какие-либо условия на отбор записей, то в итоговый набор попадут лишь те записи, которые удовлетворяют этим условиям. Инструкция SQL для созданного нами запроса имеет вид: SELECT А.a1, В.b1 FROM А INNER JOIN В ON А.a1 = В.b1; Предложение FROM в ней выглядит не так, как в случае несвязанных таблиц. В нем добавилась операция INNER JOIN (внутреннее объединение), указаны объединяемые таблицы А и В и правило объединения записей А.a1 = В.b1 — совпадение значений в связанных полях. Итак, для установления связи между таблицами в режиме конструктора запроса нужно переместить с помощью мыши поле из списка полей одной таблицы в соответствующее поле из списка полей другой таблицы. Обычно «перетаскивается» ключевое поле главной таблицы, выделенное жирным шрифтом, на поле внешнего ключа подчиненной таблицы. Связываемые поля должны иметь одинаковый или совместимый тип данных. По умолчанию Access использует внутреннее объединение, при котором из обеих таблиц отбираются лишь записи, содержащие одинаковые значения в полях связи. Если значения в этих полях одинаковы, запрос объединяет две соответствующие записи из обеих таблиц и отображает их как одну запись в динамическом наборе данных. Если для записей одной таблицы нет соответствующих записей из другой таблицы, то динамический набор будет пустым. Пример 2.2. Нужно узнать, как студенты 9701 группы сдали экзамен по информатике. Фамилии студентов хранятся в таблице Студенты, а их оценки — в таблице Сессия. Поэтому создание запроса нужно начать с включения в окно конструктора этих таблиц. После того как списки полей таблиц появятся в окне конструктора, Access автоматически соединит их линией по полю Код студента, так как связь между этими таблицами типа «один-ко-многим» содержится в схеме данных. Добавим в бланк запроса поле Группа из таблицы Студенты и поля Оценка и Код экзамена из таблицы Сессия. Для отбора нужных записей введем условие на значения в поле Группа: 9701 и условие на значения в поле Код экзамена: 1 (информатика). Отменим вывод этих полей на экран снятием флажков в строке Вывод на экран. Для вывода фамилий и имен студентов создадим вычисляемое поле, введя в любую пустую ячейку строки Поле выражение: Студент: [Студенты]![Фамилия] & " " & [Студенты]![Имя]. Для его создания удобно использовать построитель выражений. Чтобы список появлялся на экране в отсортированном виде, установим для поля Студент в строке Сортировка значение по возрастанию. На этом создание запроса завершено (см. рис. 2.11).  Рисунок 2.11 - Внутреннее объединение таблиц Студенты и Сессия В том случае, когда две таблицы непосредственно связать нельзя, для их объединения нужно использовать дополнительные таблицы или запросы. Пример 2.3. Требуется создать запрос, позволяющий определять, у каких студентов принял экзамен тот или иной преподаватель. Так как таблицы Студенты и Преподаватели не имеют общих полей, то для установления связи между ними нужно добавить в окно конструктора таблицу Сессия, имеющую общие поля с обеими таблицами. Связь между таблицами Студенты и Сессия устанавливается по полю Код студента, а между таблицами Преподаватели и Сессия — по полю Код преподавателя. Запрос должен выдавать список студентов для любого преподавателя. Поэтому следует использовать запрос с параметром. Параметр лучше задать в поле Код преподавателя, чтобы обеспечить получение правильного результата и в том случае, когда у экзаменатора есть однофамилец. Для этого нужно ввести в этом поле в строке Условие отбора текст в квадратных скобках примерно такого содержания: Введите код преподавателя. В бланк запроса следует также добавить поле Фамилия из таблицы Преподаватели и поля Группа и Фамилия из таблицы Студенты. Так как в запрос включены два поля с одинаковым именем, имеет смысл их переименовать, введя перед старым именем новое имя и отделив имена двоеточием. Затем созданный запрос (см. рис. 2.12) нужно сохранить. При его запуске Access попросит ввести код преподавателя и после того как это будет сделано, выдаст на экран фамилию преподавателя и список его студентов.  Рисунок 2.12 - Запрос Созданный запрос обычно можно легко изменить. Например, если возникло желание узнать, какие оценки получили студенты, то нужно открыть запрос в режиме конструктора, добавить в бланк поле Оценка и сохранить сделанные изменения. Access позволяет также объединять таблицы по совпадению значений внескольких полях связи (см. пример 2.8). |