Использование функций для обработки событий
ЛАБОРАТОРНАЯ РАБОТА №2
СОЗДАНИЕ ЗАПРОСОВ.
Цель работы: Создание запросов на обновление и на выработку
средствами СУБД ACCESS и ANSI SQL.
Запросы предназначены
для отбора данных, (например, данные о мероприятиях, проведенных за какой-то
период, данные о количественном и качественном составе конференций).
Результатом выполнения запроса является набор записей, собранных в таблице. В ACCESS
запросы делятся на QBE - запросы (Query By Example
– запрос по образцу) и SQL – запросы (Structured Query Language
– структурированный язык запросов), при создании которых применяются операторы
и функции языка SQL. В ACCESS легко преобразовать QBE -
запрос в SQL - запрос и наоборот.
Запросы на выборку.
Чтобы научиться составлять запросы на выборку, разберем
несколько примеров.
Запрос 1. Выбрать сведения о мероприятиях
проведенных БГПА во втором квартале 2000 года. В результате запроса вывести
поля: Организатор, Название_мероприятия, Дата_ проведения. Использовать
таблицы Мероприятия и Организаторы.
·
Сначала построим QBE –
запрос.
Для этого в окне Базы Данных активизируйте
вкладку ЗАПРОСЫ и выполните щелчок на кнопке СОЗДАТЬ. На экране
появится диалоговое окно НОВЫЙ ЗАПРОС, в котором необходимо выбрать
способ создания запроса (см. рис. 7).
Рис. 7
Выберите способ Конструктор и нажмите кнопку ОК. В
результате появится пустое окно Конструктора запросов и диалоговое окно
добавление таблицы. Маркируйте таблицу Мероприятия и выполните щелчок на
кнопке ДОБАВИТЬ. Затем маркируйте таблицу Организаторы и снова
выполните щелчок на кнопке ДОБАВИТЬ. Закройте окно, щелкнув на кнопке ЗАКРЫТЬ.
Имена таблиц появятся в окне проектирования запроса. Окно конструктора
Рис.8
запроса разделено на две части. В верхней части находятся окна таблиц
со списками полей. Имя каждой таблицы отображается в строке заголовка такого
окна. Между полями Организатор обеих таблиц проведена линия,
определяющая связь между таблицами (см. рис. 8)
.
Разработка запроса выполняется в несколько этапов:
Ø Добавление полей в запрос;
Ø Установка критериев отбора записей;
Ø Сортировка записей;
Добавить нужные поля в бланк запроса можно путем перетаскивания их
имен из списка, находящегося в верхней части конструктора, в строку Поле бланка.
Еще один способ – двойной щелчок на имени поля. Из таблицы Организаторы
выберите одним из предложенных способов поле Организатор, из
таблицы Мероприятия – поля Название_мероприятия и Дата_
проведения. Теперь необходимо ввести критерии отбора.
Формируемый в данном примере запрос должен отбирать данные о мероприятиях,
проведенных БГПА, во втором квартале 2000 года, то есть в период с 01.04.2000г.
до 30.06.2000г. поэтому для перечисленных ниже полей установим в строке Условие
отбора: такие критерии:
Организатор = БГПА
Дата _ проведения BETWEEN 01.04.2000 AND 30.06.2000
После ввода каждого из критериев следует нажимать кнопку <ENTER>,
вследствие чего ACCESS проверит его синтаксис и нормализует запись в
соответствии с правилами записи критериев. Записи в генерируемом при выполнении
запроса динамическом наборе данных могут быть отсортированы по алфавиту, по
возрастанию или по убыванию. Отсортируем названия мероприятий в алфавитном
порядке. Для этого в строке СОРТИРОВКА: поля Название_ мероприятия
выполните щелчок и в появившемся списке выберете способ сортировки по
возрастанию. Окно конструктора запроса представлено на рис.9
Рис.9
Готовый запрос выполняется после щелчка по кнопке с изображением
восклицательного знака на панели инструментов Конструктора запросов или
командой ЗАПУСК меню ЗАПРОС. ACCESS отобразит на экране результирующий набор
записей, которые отобраны из таблиц Организаторы и Мероприятия в
соответствии с заданными критериями (см. рис10).
Рис.10
Запросы, созданные в окне Конструктора запроса, ACCESS
автоматически преобразует в SQL – запросы. Если у пользователя возникает
необходимость создать SQL – запрос, ему следует спроектировать QBE –
запрос, а затем преобразовать его в SQL – запрос. При этом необходимо выполнить
следующую последовательность операций:
Ø В окне проектирования QBE –
запроса создайте QBE – запрос, который нужно преобразовать в SQL –
запрос.
Ø В меню ВИД выберите команду РЕЖИМ SQL.
На экране появится окно SQL.
Ø В окне SQL индицируется SQL – запрос,
соответствующий QBE – запросу, составленному в окне Конструктора.
Для запроса1 – см. рис. 11
Рис.11
SQL – запрос можно отредактировать в окне SQL.
После закрытия окна изменения автоматически отображаются и в QBE –
запросе.
В SQL – окно можно непосредственно вводить команды, составляющие
SQL – запрос. Это рекомендуется делать в тех случаях, когда пользователь
хорошо знает язык SQL. Проектирование SQL – запроса начинается с создания нового QBE –
запроса. После открытия окна конструктора QBE – запроса следует сразу активизировать
команду РЕЖИМ SQL меню ВИД. На экране появится окно SQL. В
окне SQL вводятся инструкции, составляющие SQL –
запрос. При вводе текста слишком длинные строки разрываются. Для повышения
наглядности инструкций каждую командную строк SQL можно начинать с новой
строки, используя комбинацию клавиш <CTRL> + <ENTER>.для сохранения набранной в SQL –
окне последовательности инструкций следует нажать кнопку ОК. Запрос можно
выполнить, вызвав из меню ЗАПРОС команду ЗАПУСК, или,
активизировав кнопку с восклицательным знаком на панели инструментов.
Запрос1, написанный
на ANSI SQL, выглядит следующим образом:
SELECT Организаторы.Организаторы,
Мероприятия.Название_мероприятия,
Мероприятия.Дата
_ проведения
FROM Организаторы, Мероприятия
WHERE (Организаторы.Организатор=
"БГПА") AND (Мероприятия.Дата _ проведения Between #4/1/2000# And #8/30/2000#) AND (Организаторы. Организатор =
Мероприятия. Организатор)
ORDER BY
Название_
мероприятия DESC ;
Нетрудно заметить, что синтаксис SQL или ACCESS и
ANSI
SQL некоторым образом отличается. В частности, когда выборка производится
из двух (или более) таблиц, SQL для ACCESS объединяет их, используя операцию INNER JOIN как
часть параметра FROM. Объединение производится при условии
равенства полей, приведенных после ключевого слова ON, в
записях таблиц, указанных в операции INNER JOIN:
…
FROM Организаторы INNER JOIN Мероприятия ON Организаторы.Организатор = Мероприятия.Организатор
…
ANSI SQL оформляет объединение, как часть условия WHERE:
…
WHERE (Организаторы. Организатор =
Мероприятия. Организатор)
…
Запрос можно использовать для выполнения расчетов и подведения
итогов, обобщив данные из исходных таблиц. Для этих целей в ACCESS
предусмотрены статистические функции SQL: Sum, Avg, Min, Max, Count и некоторые
другие.
Запрос 2. Определить количество участников каждого мероприятия,
проведенного в Минске. В результате запроса вывести поля: Название_
мероприятия (из таблицы Мероприятия), Город (из
таблицы Организаторы), Количество_участников (вычисляемое поле на
базе поля Регистрационный_№ из таблицы Участники_ мероприятия).
Вычисляемому полю присвоить собственное имя: Количество_участников.
Использовать таблицы Мероприятия, Организаторы и Участники
мероприятий.
Это так называемый запрос с группировкой. Для создания вычисляемого
поля собственное имя поля Количество_участников: – ввести в строку ПОЛЕ:
конструктора запросов (двоеточие – признак создания вычисляемого поля). Для
поля Количество_участников: набрать в построителе: Количество_участвующих=Участники_мероприятия..регистр_№..
Задать статистическую функцию COUNT. Статистические функции задают в строке ГРУППОВАЯ ОПЕРАЦИЯ окна КОНСТРУКТОРА
ЗАПРОСОВ, которая появляется после нажатия кнопки с греческой литерой
сигма, расположенной на панели инструментов, или после вызова команды ГРУППОВЫЕ
ОПЕРАЦИИ меню ВИД.
В строку Условие отбора: нужно ввести критерий: Минск. В строке СОРТИРОВКА:
задать сортировку по убыванию в поле Количество_участников (см. рис.12)
Рис. 12
Результат запроса представляет собой следующую таблицу (см. рис.13).
Рис.13
Этот же запрос на ANSI SQL:
SELECT организаторы.Город,
мероприятия.Название_мероприятия, Count([Участники
мероприятий]![Регистрационный_номер]) AS [количество_уч]
FROM организаторы,
мероприятия, [Участники мероприятий]
WHERE мероприятия.Код_мероприятия
= [Участники мероприятий].Код_мероприятия AND организаторы.Организатор =
мероприятия.Организатор
HAVING
организаторы.Город="Минск"
ORDER BY Count([Участники
мероприятий]![Регистрационный_номер]) DESC;
Запрос 3. Выбрать сведения о мероприятиях, для которых сумма
взноса за участие превышаетсреднее значение. В результате запроса вывести поля:
Организатор, Название_мероприятия, Взнос. Использовать таблицы Мероприятия
и Организаторы.
Сначала нужно определить значение среднего размера взноса по всем
мероприятиям, а затем сравнивать значение поля Взнос для каждого
мероприятия с вычисленным средним значением. Здесь необходимо выполнить
вложение запросов. Внутренний запрос сгенерирует значение среднего размера
взноса за участие, которое будет тестироваться на предмет истинности условия,
т.е. сравниваться со значением поля Взнос каждой записи. Запросы
такого типа не совсем удобно выполнять средствами КОНСТРУКТОРА ЗАПРОСОВ, но в
принципе возможно. В строку УСЛОВИЕ ОТБОРА поля Взнос необходимо ввести
текст вложенного запроса (рис. 16).
Рис.16
Результат выполнения запроса представлен в таблице на рис. 17.
Рис.17
Этот же запрос на ANSI SQL:
SELECT
Организаторы.Организатор, [Название_мероприятия], Взнос
FROM
Организаторы, Мероприятия
WHERE
Организаторы.Организатор = Мероприятия.Организатор
AND
(((мероприятия.Взнос)>(select avg(Взнос) from Мероприятия)));
Параметрические запросы.
Особый интерес представляют так называемые «запросы с
параметром». Они представляют собой варианты базового запроса и незначительно
отличаются друг от друга.
Рассмотрим создание параметрического запроса для отбора из
таблицы Участники_ мероприятий фамилий лиц, проживающих в определенном
регионе (поле Город) и имеющих определенный научный уровень (поле Должность).
Прежде всего, разработаем запрос на выборку данных. Включим в него поля Город,
ФИО _участника, Должность, ВУЗ. Поля строки Условие отбора
заполним не конкретными критериями отбора, а обращением к пользователю для
ввода критерия. На основании этого критерия и будет осуществляться отбор
записей. Обращение нужно заключить в квадратные скобки, например: [Назовите
город], [Должность] (см. рис.18).
Рис.18
Далее активизируем Вкладку ПАРАМЕТРЫ меню ЗАПРОС.
В поле ПАРАМЕТР диалогового окна ПАРАМЕТРЫ ЗАПРОСА введём текст,
который находится в поле Условие отбора спецификации запроса. В данном
случае скобки не нужны. После определения типа в поле ТИП ДАННЫХ и
нажатия кнопки ОК запрос будет готов (см. рис.19).
Рис.19
После запуска запроса на выполнение можно приступать к вводу
параметров (см. рис.20). Присвоим первому параметру значение Минск, второму
- профессор.
Рис.20
После ввода параметров будет получен результат параметрического запроса
(см. рис.21).
Рис.21
Запросы на обновление.
Часто возникает необходимость изменить значение какого-либо поля в
группе записей таблицы, отобранных на основании определённого критерия или во
всех записях таблицы (например, в связи с инфляцией повысить взнос за
участие на определённый процент или рассчитать значение какого-либо поля по уже
внесённым в таблицу данным). Для того, чтобы не вводить новые значения во все
записи, используется, так называемый ЗАПРОС НА ОБНОВЛЕНИЕ. Команда ОБНОВЛЕНИЕ
вызывается из меню ЗАПРОС во время проектирования QBE-запроса.
В том случае в проект запроса добавляется строка ОБНОВИТЬ, которая
предназначается для указания новых значений полей таблиц. В качестве таковых могут
выступать и вычисляемые выражения. После нажатия кнопки ЗАПУСК
(восклицательный знак) ACCESS укажет в специальном диалоговом окне, сколько
записей изменяется в таблице, и потребует подтвердить изменения. Рассмотрим на
примере.
Запрос 5. Изменить содержимое поля Город
таблицы Участники_мероприятия, добавив в него индекс 210035, для
проживающих в Витебске.
Для реализации этого запроса в Конструкторе запросов в меню ЗАПРОС
выбираем команду ОБНОВЛЕНИЕ. В появившуюся строку ОБНОВИТЬ вводим
выражение:“210035”+[Город].
В строку УСЛОВИЕ ОТБОРА: Витебск (см. рис.22)
Рис.22
После запуска на выполнение появится диалоговое окно (см. рис.23), в
котором нужно нажать кнопку «Да».
Рис.23
В результате выполнения этого запроса в поле Город для
участников из Витебска будут внесены изменения, которые занесутся в таблицу Участники
мероприятий.
Текст этого запроса на ANSI SQL приведён ниже:
UPDATE
[Город] SET Город=”210035”+[Город]
WHERE
(Город=”Витебск”);
(?) САМОСТОЯТЕЛЬНО:
Составить следующие запросы. Текст запросов 2 - 5 записать на ANSI SQL.
- Обновить поле Взнос таблицы Мероприятия,
увеличив его на 10%.
- Выполнить качественный анализ участников каждой конференции.
Использовать таблицы Участники_мероприятий, Мероприятия. В
результате запроса вывести поля: Название_мероприятия, Должность,
вычисляемое поле Количество, отражающее количество
участников, занимающих различный должностной статус.
- Определить количество участников каждой конференции из каждого
ВУЗа. Использовать таблицы Участники_мероприятий, Мероприятия.
В результате запроса вывести поля: Название_мероприятия, ВУЗ,
Количество (вычисляемое поле).
- Подсчитать число студентов, посетивших студенческие конференции
и семинары, проведенные в городе Минске с августа по октябрь 2000 года.
Использовать таблицы Участники_мероприятий, Мероприятия. В
результате запроса вывести поля: Дата_проведения, Название_мероприятия,
Город, Количество (вычисляемое поле). Отсортировать по полю Название_мероприятия.
- Вывести статистику должностного состава мероприятий, в названии
которых присутствует слово “НТК”. Использовать таблицы Участники_мероприятий,
Мероприятия. В результате запроса вывести поля: Название_мероприятия,
Должность. Вычисляемому полю присвоить имя Количество.
Отсортировать по полю Должность. Для реализации запроса
используйте оператор LIKE ”выражение” (символ * в <выражении>
интерпрититуется как любые символы).
- Вывести сведения о конференции, имеющей максимальное количество
участников. Использовать таблицы Участники_мероприятий, Мероприятия,
Организаторы. В результате запроса вывести поля: Название_мероприятия,
Организатор, Дата_проведения. Вычисляемому полю
присвоить имя Количество. Реализовать запрос в два
этапа:
·
Простой запрос с
вычисляемым полем;
·
Вложенный запрос на базе
простого запроса, используя функцию MAX
- Составить сводный список участников мероприятий и организаторов.
Использовать таблицы Участники_мероприятий, Организаторы. В
результате запроса вывести поля: ФИО_участника, Статус.
Для поля Статус сформировать дополнительный столбец, в
котором напротив каждой фамилии указать, участник это или организатор. Отсортировать
по полю ФИО_участника. Для реализации запроса воспользоваться
функцией IIF(<выражение>;<значение1>;<значение2>),
которая возвращает одно из двух значений в зависимости от истиности
указанного выражения. При организации запроса необходимо объединить
таблицы (поля ФИО_участника, Контактно_лицо), выбрав параметр
объединения всех записей таблицы Участники_мероприятий и только тех
записей таблицы Организаторы, у которых связаны поля..
8.
Обновить поле Город
таблицы Участники_мероприятия, внеся туда индекс города. Использовать
дополнительную таблицу индексов городов.