Лабораторная работа № 2. Создание и применение запросов в Access
Цель: Научиться создавать с помощью Конструктора запросов простые однотабличные, многотабличные запросы на выборку данных.
Теоретические сведения.
Запросы – это объекты БД, которые служат для извлечения данных из таблиц и представления их пользователю в удобном виде.
С помощью запросов выполняют операции:
Отбор данных;
Сортировку;
Фильтрацию;
Изменение данных;
Удаление данных;
Создание таблиц…
Замечания.
1. Запрос хранит определенный пользователем способ получения данных из таблиц, но не сами данные.
2. На основе запроса создается некоторая результирующая таблица, которая представляет собой образ отобранных полей и записей из основных таблиц БД. Вследствие этого увеличивается скорость обработки данных.
3. Работа с запросами предпочтительней с точки зрения безопасности:
- Снижается риск повреждения БД от неумелых действий;
- Представление разным пользователям различных запросов разграничивает доступ к данным в строгом соответствии с кругом персональных обязанностей.
Приемы создания запросов.
Запросы можно создавать в режиме:
Мастера;
Конструктора;
На языке SQL (Select Query Language)
Создание запросов в режиме Конструктора.
1. В окне БД выбрать объект Запросы.
2. Создать запрос в режиме Конструктора (аналогично созданию таблиц).
В результате откроется Окно запроса, которое называется «Бланком запроса по образцу».
Структура окна запроса
В верхней части окна должны быть представлены таблицы, участвующие в запросе.
В нижней части окна представлены:
Анализируемые поля;
Условия сортировки записей;
Необходимость вывода результатов анализа для каждого поля на экран;
Условия отбора записей.
Одновременно с Окном запроса открывается окно «Добавление таблицы».
В этом окне из списка таблиц БД выбираются те таблицы, данные которых участвуют в запросе. Выбранные таблицы располагаются в верхней части Окна запроса.
Правила заполнения бланка запроса.
1. Строка «Поле» - из верхней части окна выбираются 2-м щелчком поля, включаемые в запрос. Поля также можно выбрать, открыв список в строке «Поле».
2. Строка «Имя таблицы» - заполняется автоматически при выборе полей из соответствующей таблицы.
3. Строка «Сортировка» - представляет записи в определенном порядке. Выбор порядка сортировки осуществляется из раскрывающего списка.
При многоуровневой сортировке первичный (главный) ключ занимает самое левое положение в бланке запроса, затем по убыванию важности ключей – слева направо. В связи с этим, столбцы в бланке должны располагаться с учетом будущей сортировки.
4. Строка «Вывод на экран» - позволяет представлять те или иные поля в запросе. Если, включенное в запрос поле не должно выводиться на экран, флажок в соответствующей строке необходимо отключить.
5. Строка «Условие отбора» - задает критерии выборки.
Правила применения критериев отбора.
При задании условий используются:
-операции отношения: >, <, <>, >=, <=
Функции AND, OR, NOT,
BETWEEN значение1 AND значение2 – для задания интервала значений
LIKE – похоже - для задания приблизительных критериев.
2. Критерии на одной строке связываются по правилу И, на разных строках – по правилу ИЛИ.
Реализовать запрос можно:
Команда Запрос. Запуск.
Закрыть запрос с одновременным именованием запроса.
В Окне запросов выбрать запрос по имени и активизировать его двойным щелчком.
Примеры построения запросов рассмотрим для БД «Отдел кадров»:
Отделы
Сотрудники
Из БД «Отдел кадров» выбрать:
Пример 1. Всех сотрудников, имеющих оклад от 2000 до 3000 ден.ед. В запрос включить фамилию, оклад, номер отдела, год приема на работу, упорядочить по году приема на работу.
Конструктор:
Результат
Пример 2. Те же условия, но для сотрудников 3-го и 5-го отделов.
Пример 3. Выбрать всех сотрудников, фамилии которых начинаются на «А» и «В»; в запрос включить поля Фамилия, Должность, Количество детей; упорядочить по Количеству детей.
Пример 4. Выбрать всех сотрудников, их оклады, названия отделов, в которых они работают, упорядочить по № отдела (первичный ключ), а также фамилии сотрудников по алфавиту (вторичный ключ)
Пример 5. Выбрать всех заведующих отделами, их табельные номера и оклады, упорядочить по № отдела.
Изменение запроса
1. Выделить запрос в окне БД.
2. Активизировать кнопку Конструктор.
3. В окне Бланк запроса по образцу внести соответствующие изменения.
Удаление запроса
1. Выделить запрос в окне БД.
2. Клавиша DEL, или команда контекстного меню.
Запросы с параметром
Критерий отбора может задавать пользователь при запуске запроса в специальном диалоговом окне. Для этого в Бланке запроса в строке Условие набрать в квадратных скобках [] текст, обращенный к пользователю и соответствующую операцию отношения.
Пример 6. Выбрать сотрудников, работающих в указанном отделе, а также их должности и оклады.
Пример 7. Выбрать сотрудников, имеющих оклад, больший указанного в качестве критерия.
Итоговые запросы
Используются для подсчета простейших числовых характеристик. В итоговых запросах используются следующие функции:
SUM - сумма
AVG - среднее
MIN - минимальное
MAX – максимальное
COUNT – подсчет записей
Создание итоговых запросов рассмотрим на следующем примере.
Необходимо вычислить средний оклад по отделам, а также фонд заработной платы и число работающих в каждом отделе.
Итоговые значения вычисляются на основе группировки. Группировать значения можно по столбцам, содержащим одинаковые значения, например, № отдела.
Порядок действий:
Выбрать таблицу «Сотрудники» (младшую).
Выбрать поля:
- Оклад – для вычисления среднего оклада;
- Оклад – для вычисления суммы, фонда заработной платы
- Номер отдела – для группировки
- Фамилия – для подсчета записей.
3. Выполнить команду Вид. Групповая операция. В бланке запроса появится одноименная строка.
4. В строке Групповая операция установить:
- для поля Номер отдела - Группировка
- для полей Оклад - SUM и AVG;
- для поля Фамилия – COUNT.
5. Завершить и дать имя запросу.
Бланк запроса «по образцу» и результат по запросу представлены на рисунке:
Задание для выполнения л.р. №2.
1. С помощью Конструктора запросов создать простой запрос на выборку данных из таблиц, применяя:
числовые критерии;
текстовые критерии;
критерии по нескольким полям, связанные функциями И, ИЛИ.
2. На основе запроса на выборку создать запрос с параметром.
3. Создать итоговые запросы, вычисляя характеристики:
средние;
суммарные;
подсчет записей;
минимальные и максимальные значения полей.
4. Освоить приемы сохранения запросов, удаления запросов, изменения запросов.
5. Оформить отчет о проделанной работе.
Пункты 2 и 3 выполняются по выбору студентов на оценки «хорошо» и «отлично».
Содержание отчета
Цель работы.
Постановка задачи.
Описание используемых критериев при создании запросов к базе данных по всем пунктам задания.
Лабораторная работа № 3 (дополнительная).