ЛАБОРАТОРНАЯ РАБОТА №2
СОЗДАНИЕ ЗАПРОСОВ.
Цель работы: Создание запросов на обновление и на выработку средствами СУБД ACCESS и ANSI SQL.
Запросы предназначены для отбора данных, (например, данные о мероприятиях, проведенных за какой-то период, данные о количественном и качественном составе конференций). Результатом выполнения запроса является набор записей, собранных в таблице. В ACCESS запросы делятся на QBE - запросы (Query By Example – запрос по образцу) и SQL – запросы (Structured Query Language – структурированный язык запросов), при создании которых применяются операторы и функции языка SQL. В ACCESS легко преобразовать QBE - запрос в SQL - запрос и наоборот.
Запросы на выборку.
Чтобы научиться составлять запросы на выборку, разберем несколько примеров.
Запрос 1. Выбрать сведения о мероприятиях проведенных БГПА во втором квартале 2000 года. В результате запроса вывести поля: Организатор, Название_мероприятия, Дата_ проведения. Использовать таблицы Мероприятия и Организаторы.
· Сначала построим QBE – запрос.
Для этого в окне Базы Данных активизируйте вкладку ЗАПРОСЫ и выполните щелчок на кнопке СОЗДАТЬ. На экране появится диалоговое окно НОВЫЙ ЗАПРОС, в котором необходимо выбрать способ создания запроса (см. рис. 7).
Рис. 7
Выберите способ Конструктор и нажмите кнопку ОК. В результате появится пустое окно Конструктора запросов и диалоговое окно добавление таблицы. Маркируйте таблицу Мероприятия и выполните щелчок на кнопке ДОБАВИТЬ. Затем маркируйте таблицу Организаторы и снова выполните щелчок на кнопке ДОБАВИТЬ. Закройте окно, щелкнув на кнопке ЗАКРЫТЬ. Имена таблиц появятся в окне проектирования запроса. Окно конструктора
запроса разделено на две части. В верхней части находятся окна таблиц со списками полей. Имя каждой таблицы отображается в строке заголовка такого окна. Между полями Организатор обеих таблиц проведена линия, определяющая связь между таблицами (см. рис. 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 организаторы.Организатор = мероприятия.Организатор
GROUP BY организаторы.Город, мероприятия.Название_мероприятия
HAVING организаторы.Город="Минск"
ORDER BY Count([Участники мероприятий]![Регистрационный_номер]) DESC;
Запрос 3. Выбрать сведения о мероприятиях, для которых сумма взноса за участие превышаетсреднее значение. В результате запроса вывести поля: Организатор, Название_мероприятия, Взнос. Использовать таблицы Мероприятия и Организаторы.
Сначала нужно определить значение среднего размера взноса по всем мероприятиям, а затем сравнивать значение поля Взнос для каждого мероприятия с вычисленным средним значением. Здесь необходимо выполнить вложение запросов. Внутренний запрос сгенерирует значение среднего размера взноса за участие, которое будет тестироваться на предмет истинности условия, т.е. сравниваться со значением поля Взнос каждой записи. Запросы такого типа не совсем удобно выполнять средствами КОНСТРУКТОРА ЗАПРОСОВ, но в принципе возможно. В строку УСЛОВИЕ ОТБОРА поля Взнос необходимо ввести текст вложенного запроса (рис. 16).
Рис.16
Результат выполнения запроса представлен в таблице на рис. 17.
Рис.17
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.
· Простой запрос с вычисляемым полем;
· Вложенный запрос на базе простого запроса, используя функцию MAX
8. Обновить поле Город таблицы Участники_мероприятия, внеся туда индекс города. Использовать дополнительную таблицу индексов городов.
