Гіпермаркет Знань>>Інформатика>>Інформатика 11 клас>> Інформатика: Запити в системі управління БД. Формування звітів

Практична робота до предмету Інформатика 11 клас.

Тема «Лабораторная работа: Создание запросов на выборку к однотабличным и многотабличным БД».

Розгляд теми: Запити в системі управління БД. Формування звітів

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

Цель работы: Освоить принципы создания запросов выборки.

Задачи работы: Создать запросы выборки и получить сведения о данных с использо-ванием различных критериев.

                                                                       1. Понятие запроса

При работе с таблицами можно в любой момент выбрать из базы данных необходимую информа-цию с помощью запросов.

Запрос - это обращение к БД для поиска или изменения в базе данных информации, соответст-вующей заданным критериям.

С помощью Access могут быть созданы следующие типы запросов:

- запросы на выборку (включая итоговые запросы),
- запросы на изменение,
- перекрестные запросы,
- запросы с параметром.

                                                                    2. Создание запроса

Для начала создания запроса следует открыть базу данных, и, перейдя на вкладку
Запросы нажать кнопку Создать. Появится окно Новый запрос для выбора способа построения за-проса (рис 1).

Конструктор - создает запрос на основе пустого бланка запроса.

Простой запрос - создает простой запрос из определенных полей.

Перекрестный запрос - создает запрос, данные в котором имеют компактный формат, подобный формату сводных таблиц в Excel.

Повторяющиеся записи - создает запрос, выбирающий повторяющие записи из таблицы или простого запроса.

Записи без подчиненных - создает запрос, выбирающий из таблицы записи, не связанные с за-писями из другой таблицы.

                                                 2.1 Создание запроса с помощью Конструктора.

При выборе Конструктора через диалоговое окно Добавление таблицы (рис.2) добавляются имена таблиц в окно конструктора запроса.

Окно Добавление таблицы состоит из трех вкладок, содержащих перечни объектов, предлагае-мых программой для проектирования запроса: Таблицы, Запросы и Таблицы и запросы. При выборе вкладки Таблицы следует выделить нужную нам таблицу из предложенного списка и с помощью кнопки Добавить можно добавить несколько таблиц. Например, на рис.2 выбрана таблица Студенты.

Имена таблиц должны быть представлены в окне конструктора запроса (рис 3).

                                                               Окно конструктора запроса

Окно конструктора (рис.3) разделено на две части. В верхней части находятся окна таблиц со списками полей. Имя каждой таблицы отображается в строке заголовка такого окна.

Например, на рис.3 представлены таблицы Студенты и Студенты и занятия.

Нижняя часть является бланком запроса, или, как его называют, QBE - областью 'Query by Exam-ple - запрос по образцу). Здесь указываются параметры запроса и данные, которые нужно отобрать, а также определяется способ их отображения на экране.

Для перемещения из верхней панели окна в нижнюю и обратно используется клавиша F6.

                                                            Включение полей в запрос

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

Например, на рис.3 в бланк запроса включены поля Фамилия, Имя из таблицы Студент и Балл из таблицы Студенты и Занятия.

Примечание:

Если установлен флажок Имена таблиц в меню Вид, то во второй строке бланка QBE выйдет на экран имя таблицы, из которой выбрано поле (см. рис.3). В строке Вывод на экран флажком поме-чаются те поля, которые должны быть выведены на экран.

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

Можно определить другие значения свойств, выполнив команду из меню Вид/ Свойства:

- Описание (текст, содержащий описание объекта), 
- Формат поля (представление данных на экране),
- Число десятичных знаков (для числовых данных),
- Маска ввода,
- Подпись (заголовок столбца).

Удалить поле из бланка запроса можно клавишей [Delete] или через меню Правка командой Уда-лить столбцы. Чтобы удалить таблицу, следует маркировать ее в верхней части окна конструктора запроса, выполнив щелчок по имени, и нажать [Delete] или в меню Правка командой Удалить.

                                                      2.2 Установка критериев отбора записей

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

Чтобы найти записи с конкретным значением в каком либо поле, нужно ввести это значение в данное поле в строке бланка QBE Условие отбора (см. рис.3).

Критерии, устанавливаемые в QBE - области, должны быть заключены в кавычки. Если ACCESS идентифицирует введенные символы как критерии отбора, то заключает их в кавычки автоматиче-ски, а если нет, то сообщает о синтаксической ошибке.

Например, как показано на рис.3, построен запрос, по которому из данных по баллам будут вы-браны фамилии и имена студентов с оценками только 4 и 5.

                                                               Виды критериев

Для создания запроса с несколькими критериями пользуются различными операторами.

                                                       Логическая операция или

Можно задать несколько условий отбора, соединенных логическим оператором или (or), для не-которого поля одним из двух способов:

1) можно ввести все условия в одну ячейку строки Условие отбора, соединив их логическим оператором или (or). В этом случае будут выбраны данные, удовлетворяющие хотя бы одному из условий.

Например, запись 4 or 5 соответствует тому, что будут выбраны фамилии с оценками 4 или 5.

2) ввести второе условие в отдельную ячейку строки или (см.рис.4). И если используется не-сколько строк или, то чтобы запись была выбрана, достаточно выполнения условий хотя бы в одной из строк или, как, например, показано на рис. 4.

При такой записи условия также будут выбраны фамилии с оценками 4 или 5.

                                                        Логическая операция и

Логическая операция и (and) используется в том случае, когда должны быть выполнены оба условия и только в этом случае запись будет выбрана.
Например, записав условие >2 and <5 будут выбраны только оценки 3 и 4.

Чтобы объединить несколько условий отбора оператором и (and), следует привести их в одной строке.

Например, на рис. 5 показано, как можно задать условие для выбора фамилий студентов, про-живающих в городе Уфе, которые учатся только на 5.

Исключить группу данных из состава анализируемых запросом записей позволяет следующий критерий <> 4. В этом случае можно не использовать кавычки.

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

                                                    Оператор Between

Оператор Between позволяет задать диапазон значений. Например: between 10 and 20

Оператор In позволяет задавать используемый для сравнения список значений. Например: in ("первый","второй","третий")

                                                        Оператор Like

Оператор Like полезен для поиска образцов в текстовых полях, причем можно использовать шаблоны:
* — обозначает любое количество ( включая нулевой) символов;
 ? — любой одиночный символ;
# — указывает что в данной позиции должна быть цифра.

Например: для выбора фамилии, начинающейся с буквы П и с окончанием "ов" можно записать like П*ов

                                          Операторы для даты и времени

Можно ввести дату и время, при этом значения должны быть заключены между символами #. Например:

1. 10мая2009#
   >#31.12.2011#

В Access используется ряд других функций, которые помогут задать условия отбора для даты и времени, например:
Day(dama) - возвращает значение дня месяца в диапазоне от 1 до 31
Month(dama) - возвращает значение месяца года в диапазоне от 1 до 12
Year(dama) - возвращает значение года в диапазоне от 100 до 9999

                                       2.3 Сортировка данных в запросе

Данные можно упорядочить по возрастанию или убыванию.

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

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

Можно задать вычисления над любыми полями таблицы и сделать вычисляемое значение новым полем в запросе.
Для этого в строке Поле бланка QBE вводится формула для вычисления, причем имена полей за-ключаются в квадратные скобки. Например:=[Оклад]*0.15
В выражениях можно использовать следующие операторы:
■ арифметические: умножение (*); сложение (+); вычитание (-); деление (/); возведение в степень (Л);
■ соединение частей текста знаком & (and, и). Например: =/ Фамилия] & " "&[Имя]
В кавычки заключен пробел для того, чтобы запись не была слитной.

                                           2.5 Использование построителя выражений

При создании выражений для вычисляемых полей можно использовать кнопку ™ Построить. Для этого нужно щелкнуть по пустому полю в бланке QBE, а затем по кнопке панели инструментов Построить. При этом откроется окно Построитель выражений (рис.6)

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

Для начала нужно щелкнуть дважды в левом списке по папке Таблицы, и выбрать саму табли-цу (например, как показано на рис 6, была выбрана таблица Студенты), а затем в колонке справа само поле и щелкнуть по кнопке Вставить.

Все имена объектов, из которых строится выражение для вычисления, заключены в квадрат-ные скобки, причем перед именем поля может стоять восклицательный знак (!) разделяющий его и имя таблицы. Например, [Студенты]![Стипендия]
Ошибку при составлении выражения можно отменить, щелкнув по кнопке Отмена.

В результате произведенных действий получится выражение для поля. Например, если нужно к стипендии прибавить 30 рублей, составляется выражение [Студенты]! [Стипендия] + 30
Если щелкнуть по кнопке (Ж, то полученный результат будет перенесен в бланк QBE.

                                                               2.6 Итоговые запросы

Итоговые запросы значительно отличаются от обычных. В них поля делятся на 2 типа:

- поля, по которым осуществляется группировка данных;
- поля, для которых проводятся вычисления.

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

Для составления итогового запроса, находясь в режиме конструктора, следует нажать кнопку Групповые операции ,m„,«, на панели инструментов или воспользоваться командой Групповые операции из меню Вид.

В результате чего в бланке запроса появится строка Групповая операция. Если для соответст-вующего поля из списка выбрать функцию Группировка (рис. 7), то при выполнении запроса запи-си по этому полю группируются по значениям в этом иппр и<~> итог не подводится.

Группировка в итоговом запросе производится только по одному полю. Во всех ос-тальных полях вводятся итоговые функции.

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

Некоторые групповые функции, которыми можно воспользоваться:

Функция         Описание 
SUM ()            вычисляет сумму всех значений заданного поля (для числовых или денежных полей), отобранных запросом; 
AVG ()            вычисляет среднее значение в тех записях определенного поля, которые отобраны запросом (для числовых или денежных полей); 
MIN ()             выбирает минимальное значение в записях определенного поля, отобранных запросом; 
МАХ ()            выбирает максимальное значение в записях определенного поля, отобранных запросом; 
COUNT ()        вычисляет количество записей, отобранных запросом в определенном поле, в которых значения данного поля отличны от нуля; 
FIRST ()          определяет первое значение в указанном поле записей; 
LAST ()           определяет последнее значение в указанном поле записей. 

                                          3. Выполнение запроса

Готовый запрос выполняется после щелчка по кнопке !.панели инструментов в режиме Конструкто-ра запросов или при активизации команды Запуск из меню Запрос. В результате будет получена таблица с ответом на заданные условия . Например, на рис 8 показан результат запроса, построенно-го на рис. 3.

                                    4. Запросы к нескольким таблицам

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

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

                                                    5. ЗАДАНИЯ

1. Создать простой запрос (выбрать этот тип из окна Новый запрос)- выбрать несколько произволь-ных полей из таблицы Студенты.

2. С помощью Конструктора создать запросы, удовлетворяющие условиям:

- единственное значение факультета;
- два различных факультета;
- фамилии студентов, начинающиеся с определенной буквы (использовать шаблоны);
- фамилии студентов, заканчивающиеся на "ова";
- фамилии студентов одного факультета и одного курса;
- фамилии и имена студентов, проживающие в одном из городов или обучающиеся на одном из факультетов;
- фамилии студентов, у которых стипендия больше 400 рублей;
- фамилии студентов, занимающиеся не в 1-ой группе и стипендия которых в пределах от 200 до 500 р.

Примечание:В запрос должны быть включены поля Фамилия, Имя, Отчество и те поля, где вводятся критерии.

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

3. Добавьте в запрос поле Дата рождения и выберите записи, удовлетворяющие условиям:

- дата больше 1.1.80;
- дата в интервале значений и задан факультет;
- фамилии и имена студентов, родившихся в 90-х годах;
- фамилии и имена студентов, родившихся в первой половине месяца;
- фамилии студентов, получивших по математике оценку 4 или 5.

4. Создать итоговый запрос:

• подсчитать количество студентов по группам здоровья (используя Count)
• добавить к предыдущему запросу курс;
• добавить к предыдущему запросу группу; отсортировать поле курс и группа по возрастанию: оставить данные только для 1 курса;
• оставить данные только для 1 курса 1 группы;
• вычислить максимальную стипендию для каждого курса каждой группы (как показано на ри-сунке);
• изменить запрос, чтобы вычислялась максимальная стипендия для только каждого курса;
• вычислить общую сумму стипендии для каждого курса каждой группы.

5. Запрос с вычисляемыми полями:

- создать любой запрос, в котором одно поле является результатом сцепления текстовых по-лей Фамилия, Имя, Отчество. Назвать поле Ф. И. О. студента.
- в предыдущий запрос добавить новое поле, используя построитель выражений, подсчитать студентам стипендию с надбавкой 30 руб ;
- в предыдущий запрос добавить новое поле, используя построитель выражений, подсчитать надбавку студентам, равную 15% от стипендии;

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

- вычислить среднюю оценку за экзамены для каждого студента.

                                                  6. Контрольные вопросы

1 .Что такое запрос?
2.Назовите элементы окна конструктора запросов.
З.Что такое бланк QBE?
4.Где записываются критерии условия выбора для запроса?
5.Как удалить таблицу из запроса?
6.Как удалить поле из запроса?
7.Перечислите основные операторы, используемые в запросе.
8.В каких случаях используется оператор OR?
9.В каких случаях используется оператор AND?
10.В чем различие между операторами OR и AND?
11 .Назначение итоговых запросов.
12.Назначение построителя выражений.
13.Как вычислить сумму значений заданного поля?
14. Как осуществить сортировку записей в запросе?
15. Какие виды вычислений можно произвести в итоговых полях?

Надіслав викладач інформатики Миловидов Юрій Олегович, университет «КРОК»