Лабораторная работа по
Системе управления базами данных Microsoft Access (запросы)
Цель: освоить среду системы управления базами данных
Теоретическая часть:
Запросы
Запрос – это поиск данных из многообразия записей, находящихся во всех таблицах. Запросы выбирают из БД информацию, которая в данный момент интересует пользователя. Результат такого запроса представляет собой новую таблицу, в которой содержится только нужная информация.
Запросы могут выполнять следующие операции:
- выбирать данные из таблиц;
- группировать записи и получать итоговые значения полей по группам;
- получать данные из нескольких таблиц одновременно.
Запросы классифицируются по нескольким признакам:
1. по используемым операциям:
- стандартные (содержат условия поиска);
- нестандартные (содержат различные вычисления, обновляют, добавляют и удаляют данные и т.д.).
2. по используемому языку:
- запросы по образцу (используют язык QBE – Query By Example, требуется заполнение бланка запросов);
|
|
- структурированные запросы (используют язык SQL –Structured Query Language, требуется составление небольшой программы).
3. по способу конструирования:
- формируемые самостоятельно;
- разрабатываемые с помощью мастера.
4. по методу настройки:
- параметрический;
- непараметрический.
В MS Access можно создавать запросы следующих видов:
1. запросы на выборку (запросы на отбор данных из одной или нескольких таблиц и отображение их в виде таблиц);
2. запрос с параметром (запросы, при запуске которых выводится специальное окно для ввода необходимых для выполнения запроса данных);
3. перекрестные запросы (запросы на выполнение расчетов и группировку результатов в виде таблицы по двум полям, один из которых определяет заголовки столбцов, а другой – заголовки строк);
4. запросы на изменение, а именно:
- на удаление записи (удаление группы записей из одной или нескольких таблиц);
- на обновление записи (внесение общих изменений в группу записей одной или нескольких таблиц);
- на добавление записей (добавление группы записей из одной или нескольких таблиц в конец одной или нескольких таблиц);
- на создание таблиц (создание новой таблицы на основе всех или части данных одной или нескольких таблиц).
При задании условий на поиск данных в запросах можно использовать следующие символы:
Символ | Использование символа | Образец | Результаты поиска |
Вопросительный знак (?) | для шаблона любого символа | За? ор | Затор, Забор |
Звездочка (*) | для шаблона любого количества любых символов | Д * нь * й | День, Добрый день Первый, 10й |
Знак решетка (#) | для шаблона любого количества любых цифр (чисел) | # й | 10й, 5й |
Восклицательный знак (!) | для поиска символа, который не входит в указанный набор символов | Иванов [! а ы] | Иванову |
Примеры условных выражений:
|
|
Условные выражения | Вывод записей, которые |
Саратов | имеют значение Саратов |
Not Саратов | не имеют значение Саратов |
In (Саратов, Томск, Уфа) | имеют значение Саратов, Томск или Уфа |
<M | начинаются с букв А-Л |
>=М | начинаются с букв М-Я |
имеют значение 100 | |
<=20 | имеют значение не более 20 |
Date() | имеют значение текущей даты |
>=11.12.2006 | имеют значение даты позднее 11 декабря 2006 года |
Between 01.01.2006 And 31.12.2006 | имеют значение 2006 года |
*.12.* | имеют значение декабря любого года |
Null | содержат пустое значение |
Not Null | содержат не пустое значение |
Like “p*” | начинаются с буквы р |
Для выполнения математических итоговых вычислений или статистических расходов следует сгруппировать записи по какому-либо признаку. Затем для каждого из групп можно использовать следующие функции:
Функции | Действия |
Sum | Суммирует все значения поля |
Avg | Определяет среднее арифметическое поля |
Max | Определяет максимальное значения из всех данных поля |
Min | Находит минимальное значение из всех данных поля |
Count | Определяет количество записей поля |
First | Значение первой записи поля |
Last | Значение последней записи поля |
Группировка | Указывает, что для поля задана группировка по одинаковым значениям |
Выражение | Сообщает программе о выполнении вычислений в поле |
Условие | Используется для задания условия отбора записей в группе |
Типовые действия, допустимые в Access:
Что сделать? | Как сделать? |
Создание запроса на выборку | Меню Создание -> группа Другие -> кнопка Конструктор запросов -> выбрать необходимые таблицы и запросы -> выбрать необходимые поля, установить необходимый порядок следования и разобраться - выводить ли их на экран -> упорядочить записи -> задать условия отбора допустимых значений полей -> Сохранить -> Запустить запрос |
Выбор таблиц и запросов, участвующих в создании запроса | ПКМ на верхнем поле окна конструктора запросов -> Добавить таблицу … -> Выбрать необходимую таблицу или запрос -> Добавить -> Закрыть |
Удаление таблиц или запросов, не участвующих в создании запроса | ПКМ на удаляемой таблице -> Удалить таблицу |
Выбор необходимых полей | Выбрать из раскрывающегося списка или двойной щелчок по имени поля на необходимой таблице или запросе в верхней части окна запроса |
Изменение порядка следования полей | Перетащить столбец на необходимое место: щелкнуть ЛКМ на заголовке столбца -> Снова навести указатель мыши на этот заголовок -> удерживая ЛКМ переместить на необходимое место |
Не выводить на экран | Снять флажок с помощью щелчка ЛКМ на строке «Вывод на экран» соответствующего поля |
Упорядочивание записей | Выбрать способ сортировки на строке «Сортировка» соответствующего поля |
Сохранение запроса | Кнопка Microsoft -> Сохранить -> Ввести имя запроса -> ОК |
Задание условия отбора | На строке «Условия отбора» соответствующего поля набрать необходимое условное выражение |
Запуск запроса | Меню Работа с запросами -> меню Конструктор -> группа Результаты -> кнопка Выполнить (с рисунком красного восклицательного знака) |
Создание запроса на выполнение вычисления | Создать запрос на выборку -> в заголовке одного из столбцов набрать необходимую формулу -> меню Работа с запросами -> меню Конструктор -> группа Показать или скрыть -> кнопка Итоги (с рисунком операции суммы) -> в строке «Групповые операции» в поле, где записана формула, из раскрывающегося списка выбрать необходимую операцию -> Сохранить -> Запустить запрос |
Запись формулы | Сначала написать имя поля, которое создается, через двоеточие (:) набрать формулу, в которой участвуют имена полей заключенные в квадратных скобках и знаки математических операций |
Создание запроса с параметром | Создать запрос на выборку -> в строке «Условия отбора» соответствующего поля набрать приветствующее сообщение, заключенное в квадратных скобках -> Сохранить -> Запустить запрос |
Создание перекрестного запроса | Создать запрос на выборку -> меню Работа с запросами -> меню Конструктор -> группа Тип запроса -> кнопка Тип запроса: перекрестный -> в строке «Групповая операция» в поле, значение которого будет вычисляться, выбрать функцию, в остальных полях оставить значение Группировка -> в строке «Перекрестная таблица» в поле, значения которого будут заголовками строк, из раскрывающегося списка выбрать пункт «Заголовки строк»; в поле, значения которого будут заголовками столбцов - «Заголовки столбцов»; в поле, в котором выбрана функция вычисления - «Значение» -> Сохранить -> Запустить запрос |
Создание запроса на обновление | Создать запрос на выборку -> меню Работа с запросами -> меню Конструктор -> группа Тип запроса -> кнопка Тип запроса: обновление -> в строке «Обновление» в поле, которое нужно обновить, записать выражение для вычисления (имена полей заключить в квадратные скобки) -> Сохранить -> Запустить запрос |
Практическая часть:
|
|
Задание (если нет ни одного студента, удовлетворяющего ниже перечисленным запросам, то добавить их):
1. Создать запрос, отвечающий на вопрос «Кто из студентов родился в 1991 году?»;
2. Создать запрос, отвечающий на вопрос «Кто из студентов получил пятерку по математике или информатике?»;
3. Создать запрос, отвечающий на вопрос «Кто из девушек не любит заниматься аэробикой?»;
4. Создать запрос, отвечающий на вопрос «Сколько в группе юношей?»;
5. Создать запрос, отвечающий на вопрос «Кто из студентов учиться на отлично и хорошо?»;
6. Создать запрос, рассчитывающий среднюю оценку каждого студента по всем дисциплинам;
7. Создать запрос с параметром, который по введенному коду выводит сведения студента;
|
|
8. Создать перекрестный запрос, содержащий данные об оценках, полученных студентами по каждому предмету;
9. Добавить в таблицу «Студент» новое поле «Стипендия» (тип - денежный), ввести необходимые данные и создать запрос на обновление, который увеличивал бы стипендию на 10%;
10. Создать запрос, отвечающий на вопрос «Кто из юношей не получает стипендию?».