Последовательность выполнения работы. Цель работы -получить практические навыки разработки запросов к БД

СОЗДАНИЕ ЗАПРОСОВ

Лабораторная работа 3

Цель работы -получить практические навыки разработки запросов к БД.

Задание:

Выполнить тестирование схемы БД на типовых запросах. Необходимо создать как минимум по одному запросу каждого описанного ниже типа. В отчете запросы должны быть представлены формулировкой на естественном языке, на SQL, а также должен быть приведен пример результата выполнения запроса.

1. Создание запроса на выборку по одной таблице с помощью Конструктора запросов

1. В окне БД щелкните на закладке Запросы.

2. Щелкните на кнопку Создать.

3. В окне Новый запрос выберите Конструктор и нажмите кнопку OK.

4. В окне Добавление таблицы выберите из окна списка таблицу (или запрос), по которой будет строиться запрос и нажмите кнопку Добавить. Закрыть текущее окно. Далее на экране появиться окно Конструктора запросов.

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

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

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

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

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

8. В бланке запроса в строке Условие отбора и в строке Или укажите условия выборки из базы данных. Условия представляют собой логические выражения.

Над условиями отбора, расположенными в одной строке, выполняется логическая операция AND. Несколько условий отбора по одному полю можно задать одним из двух способов: ввести все условия в одну ячейку строки Условие отбора, соединив их логическим оператором OR, либо ввести каждое условие в отдельную ячейку строки Или. Переход на следующую строку области ячеек ИЛИ по клавише <¯>. Выражения в ячейку бланка запроса вводятся с клавиатуры или для их создания используется Построитель выражений, который запускается командой Построить из контекстного меню, связанного со строкой Условие отбора (или можно нажать кнопку Построить на панели инструментов Access).

9. Посмотрите результаты запроса, нажав кнопку Запуск (кнопка с изображением восклицательного знака) или кнопку Вид на панели инструментов.

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

Запросы, созданные в режимах Конструктора и Мастера, генерируют команды на языке SQL. Можно просмотреть эти команды, выбрав из меню Вид опцию режим SQL.

ПРИМЕР:

Выбрать сведения о продуктах в количестве большем 100, но меньшем 300 и стоимостью менее 100 рублей.

Рис. 10. Конструктор запроса с условием

Рис. 11. Результат запроса с условием

2. Внутреннее соединение по одному полю

1. Выберите вкладку Запросы окна БД, щелкните кнопку Создать. Откроется диалоговое окно Новый запрос. Выберите опцию Конструктор.

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

3. Заполните бланк запроса.

4. Щелкните на кнопке Запуск или Вид, чтобы отразить результаты запроса.

ПРИМЕР:

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

Рис. 12. Конструктор многотабличного запроса

Рис. 13. Результат многотабличного запроса

3. Внешние соединения

1. Выполните пункты 1- 3 раздела Внутреннее соединение по одному полю.

2. Установите связь между таблицами, если ранее она не была прописана в Схеме БД.

3. Щелкните на линии объединения полей, чтобы выделить ее.

4. Выберите из меню команды Вид опцию Параметры объединения. Отобразится диалоговое окно Параметры объединения.

5. Выберите тип объединения, щелкните по кнопке OK. После этой операции на линии объединения появится стрелка, указывающая тип соединения.

6. Запустите запрос на исполнение.

4. Включение в бланк запроса групповой операции

1. Щелкните по кнопке Групповые операции, знак S на панели инструментов Конструктора запросов. В бланке запроса над строкой Сортировка появится строка Групповая операция.

2. Замените в строке Групповая операция установку Группировка на требуемую групповую операцию, выбрав ее из поля со списком: Sum, Avg, Count, Мin, Max, StDev, Var, First, Last, Expression (выражение), Where (условие), Group by (группировка).

Выражение позволяет ввести вместо названия поля в ячейку какое-либо выражение, например, для вычисления размаха значений по некоторому столбцу Х: Max([X]) – Min([X]).

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

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

ПРИМЕР:

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

Рис. 14. Конструктор запроса с группированием записей

Рис. 15. Результат запроса с группированием записей

5. Включение в бланк запроса вычисляемых полей

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

2. Щелкните в строке Поле по пустому столбцу бланка запроса. Введите имя столбца, затем двоеточие, за ним – выражение. Для ввода выражения щелкните по кнопке Построить, чтобы воспользоваться Построителем выражений.

3. В верхней части Построителя выражений в области ввода создайте выражение.

4. В столбце вычисляемого поля переместите курсор в строку Групповая операция и выберите в раскрывающем списке Выражение (если не выбрать опцию Выражение, при выполнении запрос открывает диалоговое окно Введите значение параметра или возвращает сообщение об ошибке).

5. Запустите запрос.

ПРИМЕР:

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

Рис. 16. Конструктор запроса с вычисляемым полем

Рис. 17. Результат запроса с вычисляемым полем

6. Создание параметрического запроса

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

2. Введите в одну из ячеек строки Условие отбора параметр, а не значение. Например, если выбор записей выполняется по фамилии, то в параметрическом запросе по полю Фамилия в условиях отбора в квадратных скобках может быть записана фраза [введите фамилию].

3. Выберите из меню Запрос опцию Параметры. В диалоговом окне Параметры запроса в левом столбце введите в квадратных скобках параметр, совпадающий с параметром в бланке запроса Конструктора запросов (параметр можно скопировать из бланка запроса). В правом столбце укажите тип данных: нажмите клавишу <F4>, из списка выберите тип данных.

4. Запустите запрос на исполнение. Access отобразит окно Введите значение параметра,в которое введите требуемое Вам значение параметра и нажмите кнопку OK.

5. Запустите запрос на исполнение.

ПРИМЕР:

Выбрать список продуктов, срок годности которых превышает заданное значение.

Рис. 18. Конструктор параметрического запроса

Рис. 19. Ввод значения срока годности продукта

Рис. 20. Результат параметрического запроса

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

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

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

3. Выберите из меню команду Запрос/Перекрестный. Заголовок Конструктора изменится с Запрос 1: на выборку на Запрос 1: перекрестный запрос. В бланке запроса отобразится строка Перекрестная таблица.

4. Для полей, которые будут строками перекрестной таблицы, откройте раскрывающийся список строки Перекрестная таблица выберите опцию Заголовки строк. Каждое поле, являющееся заголовком строки должно иметь в строке Групповая операция установку Группировка.

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

в строке Перекрестная таблица выбрать опцию Заголовки столбцов ив строке Групповая операция установку Группировка, или в строке Групповая операция – Условие, в строках Условие отбора и ИЛИ записать логическое выражение.

6. Задать поле, на основе которого будет создаваться итоговое значение. В строке Перекрестная таблица выбрать Значение. В строке Групповая операция выбрать итоговую функцию или Выражение. В последнем случае в ячейке Поле записать выражение.

7. Запустите запрос на исполнение.

ПРИМЕР:

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

Рис. 21. Конструктор перекрестного запроса

Рис. 22. Результат перекрестного запроса

8. Запрос на создание таблицы

Для того, чтобы создать таблицу из результатов запроса, использовавшегося для отбора записей в связанных таблицах, выполните следующие действия:

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

2. Откройте созданный запрос в режиме Конструктора, выделив его имя в списке запросов в окне БД и нажав кнопку Конструктор.

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

4. Нажмите кнопку ОК. Запрос на выборку преобразуется в запрос на создание таблицы.

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

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

7. Раскройте вкладку Таблицы в окне БД; в списке должна появиться новая таблица.

9. Запрос на обновление записей

1. Создайте копию таблицы, в которой предполагается обновление записей.

2. Создайте новый запрос и включите в него созданную копию таблицы.

3. Выберите в меню команду Запрос/Обновление. В бланке запроса строки Сортировка и Вывод на экран будут заменены строкой Обновление.

4. В строке Обновление задайте выражение, которое представляет собой новое значение для текущего поля.

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

6. Выполните запрос. Проконтролируйте правильность обновления записей.

ПРИМЕР:

Обновить стоимость продуктов, количество которого превышает 500 единиц, установив ее на уровне 90% от первоначальной стоимости.

Рис. 23. Конструктор запроса на обновление

10. Запрос на удаление записей

1. Создайте копию таблицы, в которой предполагается обновление записей.

2. Создайте новый запрос и включите в него соответствующую таблицу.

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

4. Запустите запрос на выборку, чтобы отобразить подлежащие удалению записи.

5. Откройте окно базы данных. Выберите вкладку Таблицы.

6. Активизируйте созданный запрос на выборку и перейдите в режим Конструктора запроса.

7. Выберите в меню команду Запрос/Удаление. В бланке запроса строки Сортировка и Вывод на экран будут заменены строкой Удаление.

8. Щелкните на кнопке Запуск панели инструментов. Появится окно сообщений, запрашивающее подтверждение удаления записей.

Удаление записей в таблице “ один”, для которых в таблице “ многие” существуют связанные записи, нарушает правило целостности данных. Поэтому опция целостности данных, установленная для связи между таблицами, может препятствовать удалению записей. Следует применить каскадное удаление.

ПРИМЕР:

Удалить записи о продуктах, срок годности которых истек к 1 января 2004 года.

Рис. 24. Конструктор запроса на удаление

11. Запрос на добавление записей

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

1. Выберите вкладку Запросы окна БД, щелкните кнопку Создать. Откроется диалоговое окно Новый запрос. Выберите опцию Конструктор.

2. В окне Добавление таблицы выберите таблицу, из которой будут браться данные.

3. Перенесите в строку Поле бланка запроса поля, значения которых будут включаться в другую таблицу.

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

В бланке запроса после строки Сортировка будет включена строка Добавление, в которой требуется указать те поля таблицы, куда добавляются данные. Если имена полей совпадают Aсcess автоматически вставит в строку Добавление имена полей.

5. Запустите запрос на исполнение. Посмотрите как изменилась таблица, в которую внесены новые строки.

Если в запросе на Добавление есть поля типа Счетчик, то работать с такими полями можно двумя способами:

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

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

Дата добавления: 2015-05-09