Основные сведения. Тема: Создание запросов в режиме Конструктора

Тема: создание запросов в режиме конструктора

Цель работы: Изучить основные виды запросов, освоить технологию создания запросов в режиме QBE (запрос по образцу) и фильтров.

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

Информация в таблице – ответе на запрос может быть упорядочена, отфильтрована, объединена или обработана каким-либо другим образом. При этом исходные таблицы не изменяются. В запросах могут выполняться итоговые вычисления. Наиболее часто используются запросы на выборку. В результате такого запроса создается таблица, содержащая только данные, отвечающие условию запроса. Проще всего сформировать запрос с помощью бланка QBE – запроса по образцу.

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

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

Открыть вкладку Запрос диалогового окна База данных, и щелкнуть по кнопке Создать;

В диалоговом окне Новый запрос выбрать пункт Конструктор;

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

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

Строка Поле заполняется перетаскиванием названий полей из верхней части бланка.

Строка Имя таблицы формируется автоматически при перетаскивании поля.

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

Флажки в строке Вывод на экран обеспечивают отображение данных столбца.

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

Запуск запроса осуществляется щелчком по кнопке Запуск(!) на панели инструментов. После этого формируется результирующая таблица.

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

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

Ввод условий отбора

Для отбора записей с конкретным значением поля, это значение вводится в ячейку Условие отбора. Текстовое значение вводится без кавычек, они добавляются автоматически. Если необходимо задать несколько условий для одного поля, они вводятся в строку Условие отбора с использованием знаков логических операций – AND, OR. Условия, в которых используется логическая операция ИЛИ, могут быть введены двумя способами:

в поле Условие отбора вводится составное условие со знаком операции OR;

в поле Условие отбора вводится первое условие, остальные условия вводятся в строку Или.

Кроме общепринятых логических операций, Access предоставляет три специальных оператора, предназначенных для отбора данных:

BETWEEN – определяет диапазон значений. Например, выражение BETWEEN 100 And 200 означает то же, что >=100 And <=200.

Например, для отображения дат а диапазоне 5-декабря-2006 и 5-декабря-2007 использует условие: Between #05.12.06# And #25.12.07# (Значения типа Дата и время вводятся в символах – “#”).

IN – задает используемый список значений. Так, выражение IN(“A”,”B”,”C”) означает то же, что “А” Or “B” Or “C”.

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

“?” – один символ в данной позиции и “*”- любое количество (включая нулевое) символов в данной позиции.

! – исключение символов, указанных после подстановочного знака.

“#” означает, что в данной позиции должна стоять цифра. (Полный список подстановочных знаков приведен в разделе справочной системы “Использование подстановочных знаков для поиска значений”).

Допустимый диапазон символов для данной позиции должен быть заключен в квадратные скобки, восклицательный знак используется для указания исключений. Выражение Like ”?[d-h]a[0-9*]” проверяет наличие произвольного символа в первой позиции, буквы от d до h – во второй позиции, буквы а - в третьей позиции, цифры – в четвертой позиции, и произвольного числа и набора символов в конце строки. Выражение Like ”?[!d-h]a[0-9*]” отличается от предыдущего тем, что во второй позиции могут быть любые символы, исключая диапазон от d до h.

Вычисляемые поля

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

9. установить курсор в строку поле;

10. щелкнуть по кнопке Построить на панели инструментов;

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

Запросы на изменение

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

Запрос на удаление

Создать запрос на удаление, предназначенный для удаления из таблицы Для этого следует:

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

· В режиме конструктора запроса выбрать тип запроса - Удаление.

· Переместить символ (*) из списка полей в бланк запроса. (В ячейке Удаление в этом столбце появляется значение “Из”).

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

· Ввести условие отбора в ячейку Условие отбора для полей, перемещенных в бланк запроса.

· Для удаления записей нажать кнопку Запуск (!) на панели инструментов.

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

Запрос на создание новой таблицы строится на основе запроса на выборку.

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

Затем запрос на выборку преобразуется в запрос на создание таблицы (команда Запрос/Создание таблицы).

Вводится имя новой таблицы.

Фильтры.

Фильтр выполняет ту же функцию, что и запрос – отбор нужных данных. Если запрос – это один из объектов БД, то фильтр – это свойство объекта базы данных, предназначенное для поиска записей. Основные разновидности фильтров:

· Фильтр по выделенному;

· Обычный фильтр;

· Расширенный фильтр.

Для задания фильтра по выделенному следует выделить одно или несколько смежных полей (типы данных в полях должны совпадать) и выполнить команду Записи/Фильтр/По выделенному или щелкнуть кнопку Фильтр по выделенному. В таблице останутся только те записи, у которых значения полей совпадает с выделенным. Команда Исключить выделенное также обеспечивает фильтрацию, но с обратным критерием – скрывает записи, значения полей которых совпадают с выделенными. Команда Удалить фильтр не удаляет сам использованный критерий, а только отменяет действие фильтра, делая все записи доступными. Заданный режим фильтрации можно применить командой Применение фильтра.

Команда Изменить фильтр позволяет отредактировать условия отбора в окне Обычного фильтра. Команда Изменить фильтр выводит окно Запроса по форме. Если условие отбора для таблицы еще не задано, то в нижней части находится ярлычок вкладки Найти. В каждом столбце, следует ввести логическое выражение. Для ввода составных условий используется ярлычок Или. Для выполнения фильтрации следует выполнить команду Применить фильтр. Отменяется фильтрация командой Удалить фильтр.

Расширенный фильтр вызывается командой Записи/Фильтр/Расширенный фильтр. Окно расширенного фильтра напоминает окно создания запроса. Контекстное меню расширенного фильтра содержит команды, позволяющие преобразовывать расширенные фильтры в запросы и наоборот.

КОНТРОЛЬНЫЕ ВОПРОСЫ.

1. Для чего предназначены запросы?

2. Какие виды запросов Вам известны?

3. Каким образом задаются таблицы, из которых берутся данные для запроса?

4. Какими способами вводятся составные условия отбора?

5. Перечислить операторы, предназначенные для отбора данных.

6. Каким образом создается вычисляемое поле?

7. Как создать итоговый запрос?

8. Какие виды запросов относятся к числу запросов на изменение?

9. Для чего предназначен запрос на добавление данных?

10. Что представляет собой перекрестный запрос и как он формируется?

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ.

1. На основе таблицы Преподаватели создать простой запрос на выборку, в котором должны отображаться фамилии, имена, отчества преподавателей и их должность.

Для создания простого запроса:

· в окне базы данных открыть вкладку Запросы;

· в открывшемся окне щелкнуть по кнопке Создать;

· из появившихся пунктов окна Новый запрос выбрать Простой запрос, щелкнуть по кнопке ОК;

· в появившемся окне в строке Таблицы/запросы выбрать таблицу Преподаватели;

· в окне Доступные поля перевести выделение на поле Фамилия

· щелкнуть по кнопке >. Слово Фамилия перейдет в окно Выбранные поля;

· аналогично в окно «Выбранные поля» перевести поля «Имя», «Отчество», «Должность» (порядок важен — в таком порядке данные и будут выводиться);

· щелкнуть по кнопке Далее;

· в строке параметра Задать имя запроса ввести новое имя Должности препо­давателей,

· щелкнуть по кнопке Готово. На экране появится таблица с результатами запроса. Для сортировки данных:

· щелкнуть в любой строке поля Должность;

· Выполнить запуск запроса (кнопка!)

Для сохранения запроса:

· выполнить команду Файл/Сохранить;

· закрыть окно запроса.

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

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

· В окне базы данных выбрать вкладку Запрос,

· щелкнуть кнопку Создать,

· Выбрать пункт Конструктор

· Щелкнуть ОК.

· В окне конструктора добавить таблицы Преподаватели и Дисциплины.

· Перетащить мышью необходимые поля из таблиц в поля запроса.

· В поле Условие отбора ввести текст в квадратных скобках [Введите фамилию преподавателя] (см. рисунок 23)

Рисунок 23

· Выполнить этот запрос для преподавателя Григорьева.

· Выполнить запуск запроса (кнопка!)

· Сохранить запрос. Дать имя запросу Преподаваемые дисциплины;

3. Создать запрос, в котором выводятся оценки студентов по задан­ной как параметр дисциплине для заданной как параметр группы.

1. Создать запрос в режиме Конструктора. Окно запроса представлено на рисунке 24.

Рисунок 24

· Сохранить запрос под именем Оценки по группам.

4. Создать запрос для определения средней оценки по каждой дисциплине.

· Создать запрос в режиме Конструктора. Перетащить поля таблиц в поля запроса. Для нахождения средней оценки выполнить групповую операцию (щелчок по кнопке Групповая операция на панели инструментов). В строке Групповая операция поля оценки выбрать функцию Avg. Отсортировать запрос в алфавитном порядке дисциплин.

Рисунок 25

· Сохранить запрос под именем Средние оценки.

5. Создать запрос, содержащий сведения о том, сколько преподавателей ведут каждую дисциплину.

· Создать запрос в режиме Конструктора. Окно запроса представлено на рисунке 26.

Рисунок 26

· Сохранить под именем Количество преподавателей.

6. Вывести список студентов, не получающих стипендию.

· Создать запрос в режиме Конструктора. Источником запроса является таблица Студенты.

· Перетащить поля таблицы Фамилия, Имя, Отчество, Стипендия из полей таблиц в поля запроса.

· В строку Условие отбора поля стипендия ввести нет.

7. Создать запрос для вычисления стипендии студентов по формуле Стипендия = Средняя *10. Окно запроса представлено на рисунке 27.

Рисунок 27

· Сохранить запрос под именем Стипендия.

8. Вывести список студентов, получивших более двух двоек. Окно запроса представлено на рисунке 28.

Рисунок 28

· Сохранить запрос под именем Двоечники.

9. Создать таблицу К отчислению, содержащую список студентов, получивших более двух двоек.

· Создать запрос аналогичный предыдущему.

· В меню Запрос панели инструментов выбрать Создание таблицы.

· В открывшемся окне Создание таблицы задать имя таблицы, нажмите ОК.

· Запустить запрос на выполнение.

10. Удалить из таблицы Студенты всех студентов, коды которых содержатся в таблице К отчислению.

· В режиме Конструктора создать запрос на удаление.

Рисунок 29

Задания для самостоятельного выполнения.

1. Создать запрос, содержащий список номеров телефонов студентов группы П1. (отобрать поля Фамилия, Имя, Номер телефона).

2. Определить средний балл по группе.

3. Вывести список студентов, получивших хотя бы одну двойку.

4. Вывести количество двоек по каждой дисциплине.

5. Подсчитать количество оценок “5”, ”4”, ”3”, полученных студентами.

6. Вывести список студентов, у которых сумма баллов 15.

ЛАБОРАТОРНАЯ РАБОТА №17

Дата добавления: 2014-11-28