Виды и способы создания запросов

ЛАБОРАТОРНАЯ РАБОТА № 6

по курсу “Компьютерные информационные технологии”

ЗАПРОСЫ В ACCESS

Виды и способы создания запросов

Запросы предназначены для выборки информации из базы данных или для внесения изменений в базу данных.

В Access имеются следующие основные виды запросов:

· по способу описания:

- QBE-запросы (Query By Example – выборка по образцу) – вид запроса устанавливается в специальном окне (окне конструктора запросов);

- SQL-запросы (Structured Query Language – структурированный язык запросов) – запрос описывается с помощью команд языка SQL;

· по назначению:

- запросы на выборку – для извлечения информации из базы данных;

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

· по содержанию:

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

- с групповыми операциями (итоговые) – запросы, в которых выполняется суммирование по отдельным полям, вычисление средних, подсчет количества записей и т.д.;

- перекрестные – для разбиения данных на несколько групп и подсчета итогов (количества, суммы, среднего и т.д.) по этим группам;

· по виду описания условий обработки данных:

- фиксированные – запросы, в которых условия обработки данных (т.е. их выборки или изменения) полностью заданы;

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

Имеются два способа создания запросов:

· с помощью Мастера – в процессе построения запроса пользователю предлагаются подсказки и возможности выбора;

· с помощью Конструктора – запрос строится пользователем самостоятельно.

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

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

Запросы с условиями выборки

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

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

1. Сделать копию запроса Список рабочих. Для этого отметить этот запрос и выбрать команду Правка – Копировать. Затем выбрать команду Правка – Вставить. Указать имя запроса – Отобранные.

2. Открыть запрос Отобранные в режиме конструктора.

3. Удалить лишние поля Дата рождения и Дата приема на работу. Чтобы удалить поле, необходимо поместить курсор в это поле и выбрать команду Правка – Удалить столбцы.

4. Из таблицы Рабочие (над описанием запроса) выбрать поле Шифр объекта и поместить его в свободный столбец в конце запроса (после столбца Допуск на высоту).

5. Чтобы добавить в запрос название заказчика, выбрать команду Вид – Добавить таблицу, или нажать правую кнопку мыши в области над описанием запроса и выбрать команду Добавить таблицу. Выбрать таблицу Объекты. Из этой таблицы выбрать поле Заказчик и поместить в свободный столбец в конце запроса.

6. В строке Условие отбора для поля Профессия указать штукатур. Снять флажок Вывод на экран для поля Профессия (так как в запросе будут только данные о штукатурах, выводить название профессии не требуется).

7. В строке Условие отбора для поля Разряд указать >=5.

8. В строке Сортировка для поля Фамилия установить значение По возрастанию.

9. Сохранить внесенные изменения. Просмотреть запрос.

Пример 4.2 (сложное условие выборки). Изменить запрос Отобранные, созданный в примере 4.1, таким образом, чтобы получить список штукатуров и каменщиков, имеющих разряд не ниже пятого.

1. Открыть запрос Отобранные в режиме Конструктора.

2. С помощью мыши переместить поле Профессия, чтобы оно размещалось перед полем Разряд (если оно там не находится).

3. В строке Или для поля Профессия указать каменщик. В этой же строке для поля Разряд указать >=5.

Примечание. Хотя условие >=5 уже было указано для поля Разряд в строке Условие отбора, его необходимо также указать в строке Или. Если не сделать этого, то в запросе будет получен список штукатуров, имеющих разряд не ниже пятого, и всех каменщиков.

4. Для поля Профессия установить флажок Вывод на экран (так как в запросе будут указаны рабочие двух профессий – каменщики и штукатуры, профессию также следует выводить на экран).

5. Сохранить внесенные изменения. Просмотреть запрос.

Перекрестные запросы

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

1. Нажать кнопку Создать. Выбрать команду Перекрестный запрос. Нажать OK.

2. В появившемся окне Создание перекрестных таблиц для переключателя Показать выбрать значение Таблицы (или Все). Выбрать таблицу Рабочие. Нажать Далее.

3. Из списка Доступные поля перенести в список Выбранные поля отметку поля Шифр объекта (т.е. выбрать поле, которому в создаваемой таблице будут соответствовать строки). Нажать Далее.

4. Выбрать поле Профессия (т.е. поле, которому в создаваемой таблице будут соответствовать столбцы). Нажать Далее.

5. В очередном окне Создание перекрестных таблиц в списке Поле выбрать Табельный номер, в списке Функции – Число (так как требуется подсчитать количество рабочих). Установить также флажок Вычислить итоговое значение для каждой строки (чтобы подсчитать количество рабочих на каждом объекте). Нажать Далее.

6. Задать имя запроса Распределение профессий по объектам. Установить переключатель Просмотреть результаты запроса. Нажать Готово. Результаты запроса должны иметь примерно такой вид, как показано на рис.1.

Рисунок 1 – Результаты перекрестного запроса

Пример 6.2. Создать запрос для подсчета количества рабочих каждой профессии, работающих на объектах каждого вида (жилые дома, офисы, цехи и т.д.).

Все данные, необходимые для построения перекрестного запроса, должны содержаться в одной таблице или запросе (другими словами, в перекрестном запросе невозможно использовать данные из нескольких таблиц или запросов). Поэтому сначала необходимо создать обычный запрос, содержащий поля Табельный номер и Профессия (из таблицы Рабочие) и Вид объекта (из таблицы Объекты). Присвоить ему имя Распределение рабочих по видам объектов.

Создать перекрестный запрос, как показано в примере 6.1. На шаге 2 потребуется выбрать для переключателя Показать значение Запросы (или Все) и выбрать запрос Распределение рабочих по видам объектов. В качестве строк создаваемого запроса следует выбрать виды объектов, в качестве столбцов – названия профессий (или наоборот). Присвоить созданному отчету имя Распределение профессий по видам объектов.

Параметрические запросы

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

1. Используя Конструктор, создать обычный запрос, содержащий поля Табельный номер, Фамилия и Профессия (из таблицы Рабочие).

2. Для поля Профессия в строке Условие отбора ввести: [Укажите профессию]. Для поля Разряд в строке Условие отбора ввести >=[Укажите разряд]. Здесь Укажите профессию и Укажите разряд – подсказки, выводимые на экран при выполнении запроса. Они указываются в квадратных скобках.

3. Для поля Профессия снять флажок Вывод на экран (так как в результате запроса должен быть получен список рабочих одной профессии, и выводить ее на экран для каждого рабочего не требуется). Описание запроса должно иметь примерно такой вид, как показано на рис.2.

Рисунок 2 – Создание перекрестного запроса

4. Сохранить запрос под именем Список рабочих по профессии и разряду. Выполнить запрос и убедиться в том, что он выполняется правильно.

SQL-запросы

SQL – стандартный язык управления базами данных, используемый в различных СУБД.

При построении любого QBE-запроса для него автоматически строится описание на языке SQL, и наоборот (за исключением некоторых SQL-запросов, которые не могут быть построены как QBE-запросы). Все запросы, рассмотренные выше, представляли собой QBE-запросы. Чтобы перейти от описания в виде QBE-запроса к SQL-запросу, необходимо открыть запрос в режиме Конструктора и выбрать команду Вид – Режим SQL. Для возврата к описанию в виде QBE-запроса используется команда Вид – Конструктор.

Обычно удобно сначала создать запрос в режиме QBE, а затем перейти в режим SQL и внести необходимые изменения. При изменении запроса в режиме SQL автоматически изменяется его описание в режиме QBE (и наоборот).

При переходе в новую строку в любой команде SQL необходимо нажимать комбинацию клавиш CTRL - ENTER (а не просто клавишу ENTER). В конце команды языка SQL всегда ставится точка с запятой (;).

В данной работе предлагается изучить примеры SQL-запросов, приведенные в подразделах 9.1 – 9.6, и самостоятельно создать SQL-запросы, указанные в разделе 9.7.

Запросы на обновление

Общий вид таких запросов следующий:

UPDATE таблица

SET поле=значение

WHERE условия

Пример 9.11. Создать запрос для выполнения следующей операции: для всех каменщиков и штукатуров, имеющих разряд не ниже пятого, установить шифр объекта П80 (аналогичная задача решалась в примере 7.1).

UPDATE Рабочие

SET [Шифр объекта] = "П100"

WHERE ((Профессия="штукатур") OR (Профессия="каменщик")) AND (Разряд>=5);

Пример 9.12. Создать запрос для выполнения следующей операции: снять всех рабочих с объектов заказчика “Автозавод”.

UPDATE Объекты INNER JOIN Рабочие ON Объекты.[Шифр объекта]=

Рабочие.[Шифр объекта]

SET Рабочие.[Шифр объекта] = Null

WHERE (Объекты.Заказчик="Автозавод");

Здесь требуется изменять значения поля Шифр объекта в таблице Рабочие, но при этом используется информация из таблицы Объекты, где указаны названия заказчиков. Поэтому в команде используется раздел INNER JOIN (связь между таблицами), и перед именами полей указываются имена таблиц. Значение Null, присваиваемое полю Шифр объекта, обозначает, что это поле становится пустым. Важно понимать, что значение Null – это не то же самое, что пустая строка (“”).

Запросы на удаление

Общий вид таких запросов следующий:

DELETE FROM таблица WHERE условие

Пример 9.13. Создать запрос для удаления данных обо всех рабочих, имеющих разряд не выше второго и не имеющих допуска к работам на высоте (аналогичная задача решалась в примере 7.1).

DELETE

FROM Рабочие

WHERE (Разряд<=2) AND ([Допуск на высоту]=False);

Параметрические SQL-запросы

Как и в QBE-запросах, вместо конкретных величин в параметрических SQL-запросах указываются тексты сообщений (подсказок), которые должны выводиться на экран при выполнении запроса. Эти подсказки заключаются в квадратные скобки.

Пример 9.14. Создать запрос для получения списка рабочих заданной профессии, имеющих разряд не ниже заданного. В списке, получаемом в результате выполнения запроса, должны быть указаны табельные номера, фамилии и разряды рабочих. Профессия и разряд, должны вводиться пользователем при выполнении запроса (эта же задача решалась в примере 8.1).

SELECT [Табельный номер], Фамилия, Разряд

FROM Рабочие

WHERE (Профессия=[Укажите профессию]) AND (Разряд>=[Укажите разряд]);

При выполнении этого запроса на экран будут выводиться подсказки Укажите профессию и Укажите разряд.

9.7. Создание SQL-запросов. Задания для самостоятельного выполнения

Для создания SQL-запроса “с нуля” (т.е. без предварительного создания QBE-запроса) необходимо выполнить следующее.

1. Перейти на вкладку Запросы. Нажать кнопку Создать.

2. Выбрать команду Конструктор. Нажать OK. На экран выводится пустой бланк для создания QBE-запроса, а также окно Добавление таблицы.

3. Закрыть окно Добавление таблицы.

4. Выбрать команду Вид – Режим SQL.

5. В появившемся пустом окне ввести текст SQL-запроса. Сохранить запрос.

6. Для выполнения запроса нажать кнопку Запуск (с восклицательным знаком), или выбрать команду Вид – Режим таблицы, или закрыть запрос и выполнить его нажатием кнопки Открыть.

Предлагается самостоятельно создать следующие SQL-запросы (не создавая предварительно QBE-запросы):

а) список всех объектов, представляющих собой жилые дома или офисы. Для каждого объекта должны указываться все данные, имеющиеся в таблице Объекты. Список объектов должен быть упорядочен по названию заказчика, а для каждого заказчика – по шифру объекта;

б) список всех рабочих, занятых на объектах, где заказчиком является автозавод. Для каждого рабочего указывается табельный номер, фамилия, профессия, шифр объекта. Список должен быть упорядочен по фамилиям рабочих (если есть однофамильцы – то по табельному номеру);

в) то же, что в задании б), но для произвольного заказчика. Название заказчика должно вводиться пользователем при выполнении запроса;

г) список всех объектов. Для каждого объекта должны указываться все данные, имеющиеся в таблице Объекты, а также количество дней, оставшихся до окончания строительства. Для вычисления количества дней до конца строительства использовать формулу: [дата окончания]-date() (здесь date() – стандартная функция, возвращающая текущую дату);

д) то же, что в задании г), но только для объектов, у которых до окончания строительства остается не более 30 дней;

е) список всех объектов. Для каждого объекта указывается его шифр, заказчик, вид объекта и количество занятых рабочих. Список должен быть отсортирован по шифрам объектов (по алфавиту);

ж) повышение стоимости контракта на 20% для всех объектов – офисов. Указание: в разделе SET использовать выражение примерно следующего вида: [Стоимость контракта] = [Стоимость контракта]*1,2;

з) удаление данных об указанном рабочем (фамилия рабочего должна вводиться пользователем).

ПРИЛОЖЕНИЕ