Лабораторная работа №4.2 Создание Запросов

Цель работы: научиться создавать запросы различных типов (на выборку, параметрические, на удаление, на обновление, на добавление, на создание таблиц и перекрестные) с помощью мастера и в режиме конструктора запросов; научиться задавать условия отбора и создавать новые поля в запросе, а также работать с построителем выражений для создания сложных условий отбора в запросе;

Краткие теоретические сведения

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

Запрос это совокупность критериев поиска, представленная на одном из языков СУБД и предназначенная для выборки нужных пользователю данных из некоторого набора.

Основные виды запросов следующие:

¾ простой запрос;

¾ запрос на выборку;

¾ запрос на действие (обновление, добавление, удаление).

Простой запрос позволяет выбирать нужную совокупность полей из таблицы. Он легко и быстро создается вручную с использованием некоторых элементов МАСТЕРА ЗАПРОСОВ. С точки зрения реляционной алгебры при простом запросе выполняется операция проецuрованuя на свои указанные атрибуты (поля таблицы).

Запрос па выборку ¾ основной вид запросов, который создается при помощи КОНСТРУКТОРА ЗАПРОСОВ на языке формирования запросов по образцу QBE, упоминавшемся выше. С точки зрения реляционной алгебры при этом выполняется операция выборки из отношения тех кортежей (записей таблицы), которые удовлетворяют заданному предикату.

Запрос па действие базируется на запросе на выборку. Процесс формирования этого запроса распадается на два этапа: сначала осуществляется запрос на выборку, а затем подключаются средства

СУБД, позволяющие пользователю осуществить одно из трех требуемых действий: обновление, добавление или удаление указанных пользователем данных. С точки зрения реляционной алгебры в этом случае осуществляются различные унарные и бинарные операции над отношениями (объединение, пересечение, вычитание, декартово произведение, соединение, деление), а также их комбинации. Высококвалифицированные пользователи имеют возможность при желании создавать запросы на языке структурированных запросов SQL (Structured Query Language).

Порядок выполнения

2.1. Мастер запросов

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

2.1.1. Переходим на закладку «Создание» и нажимаем кнопку

2.1.2. Выбираем «Простой запрос» и нажимаем OK.

2.1.3. В раскрывающемся списке «Таблицы и запросы» выбираем таблицу «Контакты», из списка «Доступные поля» выбираем: Фамилия, Имя и Телефон. Затем из таблицы «Книги» выбираем: Название и Автор; а из таблицы «Записи» - Дата выдачи. И нажимаем «Далее».

2.1.4. Выбираем подробный отчет и нажимаем «Далее», установив (подробный).

2.1.5. Вводим имя запроса, ставим переключатель на «Открыть запрос для просмотра данных» и нажимаем «Готово».

2.1.6. В результате получаем таблицу:

Рис.2.1. Таблица запроса

2.2. Конструктор запросов

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

2.2.1. Переходим на закладку «Создание» и нажимаем кнопку КОНСТРУКТОР ЗАПРОСОВ

2.2.2. Добавляем все три таблицы и закрываем окно.

2.2.3. Из раскрывающегося списка выбираем таблицы и поля для отображения в запросе.

Рис.2.2. Окно конструктора запроса

2.3. Условие отбора

Добавление в запрос условия отбора позволяет выбирать из таблицы не все записи, а лишь те, которые удовлетворяют определенным критериям. Например, нам нужны записи, приходящиеся на февраль 2007 года.

2.3.1. В бланке запроса щелкаем на ячейке Условие отбора пятого столбца правой кнопкой мыши и выбираем в контекстном меню команду «Построить». Откроется окно построителя выражений.

Рис.2.3. Окно построителя выражений

2.3.2. В левом списке построителя щелкаем на папке Операторы.

2.3.3. В среднем списке выбираем категорию Сравнения.

2.3.4. В правом списке дважды щелкаем на пункте Between.

2.3.5. Щелчком выделяем в поле формулы первый местозаполнитель «Выражение».

2.3.6. В левом списке построителя выражений двойным щелчком открываем папку Функции.

2.3.7. Щелкаем на папке Встроенные функции, содержащей стандартные функции Access.

2.3.8. В среднем списке построителя выражений щелкаем на пункте Дата/время.

2.3.9. В правом списке дважды щелкаем на функции DateValue, чтобы заменить ею местозаполнитель «Выражение».

2.3.10. Выделив местозаполнитель «stringexpr», вводим текст «01.02.2007».

2.3.11. Повторяя шаги 2.3.5 – 3.3.10 , заменяем второй местозаполнитель «Выражение» на выражение DateValue («31.02.2007»).

В результате у нас получилась формула Between DateValue(«01.02.2007») And DateValue(«31.02.2007»). Она проверяет условие нахождения даты в интервале от 1 до 31 февраля 2007 г

2.3.12. Нажимаем па кнопке ОК.

2.3.13. Для выполнения запроса нажимаем

2.4. Запрос действия

Запросы могут применяться также для добавления, удаления и обновления группы записей таблицы. Такие запросы являются мощным инструментом преобразования данных, они называются запросами действия. К примеру, нам нужно изменить в таблице «Книги» номер по картотеке. Заменить первую цифру – 4 на 6. Подобную операцию трудно провести «вручную», если в таблице больше тысячи записей.

2.4.1. Создаем новый запрос в режиме конструктора.

2.4.2. В окне конструктора открываем таблицу «Книги».

2.4.3. В значение поле выбираем «Номер по картотеке».

Рис.2.4. Окно конструктора запроса

2.4.4. В поле «Тип запроса» выбираем запрос на обновление, при этом в бланке запроса появиться еще одно поле – обновление, в которое нужно ввести новое значение поля.

2.4.5. В поле «Обновление» вызываем контекстное меню, щелкаем на пункте «Построить»

2.4.6. В окне построителя выражений пишем формулу:

Рис.2.5. Окно построителя выражений

2.4.7. В поле «Условие отбора» вызываем контекстное меню, щелкаем на пункте «Построить»

2.4.8. В окне построителя выражений пишем формулу:

Рис.2.6. Окно построителя выражений

2.4.9. Закрываем запрос, сохранив под именем «Обновление».

2.4.10. Двойным кликом выполняем запрос «Обновление», Access проинформирует об обнаружении одной записи, удовлетворяющей условию отбора, и попросит подтвердить необходимость ее изменения.

2.5. Запрос SQL

Язык SQL является наиболее распространенным языком управления баз данных клиент – серверов. Это язык реляционных баз данных, а не язык систем программирования. В Microsoft Access используется язык Jet SQL.

2.5.1. Создаем новый запрос в режиме конструктора.

2.5.2. Переходим в режим SQL.

2.5.3. В открывшемся окне пишется запрос на языке SQL.

Рис 2.7. Окно запроса SQL.

2.5.4. Напишем запрос на добавление новой записи в таблицу «Книги»:

INSERT INTO Книги (Название, автор, издательство, Год_издания, Номер_по_картотеке )

VALUES ('Му-Му', 'Тургенев', 'Роспечать', "2007", "845623");

2.5.5. Нажимаем кнопку , появится окно подтверждения добавления новой записи, нажимаем ОК.

Практические задания

Задание 1

1. Создайте запрос на выборку ОБЩАЯ СТОИМОСТЬ КОСТЮМОВс полями Наименование, Срок использования/лет, Общая стоимость. ПолеОбщая стоимостьвычисляется по формуле: Общая стоимость = Стоимость * Количество

2. Создайте запрос на обновление НОВЫЙ СРОК ИСПОЛЬЗОВАНИЯ,которыйдля записей со Стоимостью меньшей 1800000, устанавливает значение поля Срок носки/лет равное 5.

3. Создайте запрос на выборку КОНЦЕРТНЫЕ КОСТЮМЫ, который выбирает записи с кодами 1001, 1002 и 1003 и выводит поля Наименование, Количество и Срок использования.

Задание 2

1. Создайте запрос на выборку УДЕРЖАНОс полями Номер, Фамилия, Должность, Аванс, Подоходный налог, Прочие удержания и Удержано. Поле Удержано вычисляется по формуле: Удержано= Аванс + Подоходный налог + Прочие удержания

2. Создайте параметрический запрос ВЫПЛАТА АВАНСА, который запрашивает Номери выводит поля Фамилия, Дата, Аванс согласно введенному значению. Поле Номер в запросе отображаться не должно.

3. Создайте запрос СУММАРНЫЕ УДЕРЖАНИЯ, который выводит поля Номер, Фамилия, Должность и Суммарное удержание, которое вычисляется как сумма значений поля Удержано (применить групповые операции).

Задание 3

1. Создайте запрос на выборку НАЧИСЛЕНО с полями Табельный номер, Фамилия, Должность, Итого начислено. Поле Итого начисленовычисляется по формуле: Итого начислено= Зарплата + Премия + Больничный

2. Создайте запрос на выборку СУММАРНАЯ ЗАРПЛАТА 1754 с полями Табельный номер, Фамилия, Зарплата, который выбирает записи с Табельным номеромравным 1754 и подсчитывает сумму зарплат (применить групповые операции).

3. Создайте запрос на выборку ЗАРПЛАТА, который отбирает записи с датой с 20 по 23 декабря 2010 года и выводит поля Зарплата и Сотрудник (поле Сотрудник является объединением полей Должность и Фамилия).

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

1. Что такое запрос?

2. Какие основные виды запросов используются в Access?

3. Каковы назначения и процедура создания простого запроса?

4. Каковы назначения и процедура создания запроса на выборку?

5. Каковы назначения и порядок использования оператора Like?

6. Каковы назначения и порядок использования оператора In?

7. Каковы назначения и порядок использования оператора Between?

8. Каковы назначения и процедура создания запроса на обновление?

9. Каковы назначения и процедура создания запроса на добавление?

10. Каковы назначения и порядок использования запроса на удаление?

11. Каковы содержание и цель построения запроса с групповой операцией? Какие групповые функции применяются в Access?

12. Какова процедура создания запроса на выборку с групповой операцией?

13. Каковы содержание и цель построения запроса на создание таблицы?

14. Какова процедура создания запроса на создание таблицы?

15. Каковы содержание и цель построения перекрестного запроса?

16. Какова процедура создания перекрестных запросов?