Для сдачи тестов, рубежного контроля, а также закрепления материала используйте браузеры MS Internet Explorer, Mozilla Firefox, Chromium
    Главная страница электронного учебника План урока
    Содержание дисциплины

    Содержание дисциплины


    Учебная тема
    Работа с запросами

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

    Запрос — это одновременно и объект базы данных, и операция по обработке данных. Если таблицы и формы являются статичными элементами базы, то есть при их открытии данные базы не меняются, то в процессе открытия запросов происходит обработка данных, и в результате формируется новая таблица, которая может быть сохранена или передана другому объекту базы данных (например, в форму).

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

    Виды запросов, используемых в Microsoft Access

    В Access различают несколько основных видов запросов:

    • Запросы на выборку.
    • Перекрестные запросы.
    • Запросы на изменение данных (на создание, удаление, обновление и добавление).
    • Запросы SQL (на объединение, управляющие, к серверу, подчиненные).

    Теперь рассмотрим каждый из этих видов запросов и ознакомимся с особенностями его применения.

    Запрос на выборку

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

    Рис. 1. Выбираем запрос Запрос по сумме оплаты

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

    Рис. 2. Запрос на выборку: Запрос по сумме оплаты

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

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

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

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

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

    Запросы на изменение

    В результате выполнения запроса на изменения происходит (в зависимости от его типа) либо изменение, либо удаление, либо перемещение нескольких записей. Существуют следующие типы запросов на изменение:

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

    • Запрос на создание таблицы формирует таблицу на базе других таблиц. В эту таблицу записи отбираются по определенным критериям.

    Запросы SQL

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

    Все ранее рассмотренные запросы могут быть записаны и на языке SQL. Выделяются следующие виды SQL-запросов:

    • Запрос к серверу передает команды прямо на сервер базы данных, где происходит их обработка. Дело в том, что некоторые базы данных хранятся на одном компьютере, а доступ к ним осуществляется с других (другого) компьютеров. Тот компьютер, на котором хранится база данных, обычно является сервером, а другие компьютеры — клиентами. Запрос к серверу дает ему команды по обработке данных базы.
    • Управляющий запрос изменяет или создает новые объекты базы данных.
    • Запрос на объединение позволяет объединить результаты выполнения двух или более запросов на выборку.
    • Подчиненные запросы SQL характеризуются тем, что являются частью других запросов SQL.

    При построении запросов SQL применяются совокупные функции SQL или функции агрегирования (см. табл. 5.1). Для построения запроса с использованием этих функций, выполняется группировка по одному или более полям. Для этого следует воспользоваться командой Групповые операции из меню Вид либо соответствующим значком на панели инструментов.

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

    Пример создания таблиц с запросом

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

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

    1. Щелчком на кнопке Окно базы данных переключимся на список таблиц базы данных.

    2. Дважды щёлкнем по значку Создание таблицы в режиме конструктора.

    3. Введем слово Дата − название создаваемого поля.

    4. Щелчком на ячейке Тип данных первой строки списка полей раскроем структуру и размер данных.

    5. Щелчком на появившейся стрелке каскадного меню в ячейке Тип данных выберем пункт Дата/время.

    Рис. 3. Электронный журнал успеваемости учащихся

    6. Нажмем клавишу “Tab” и введем расшифровку назначения поля: Дата получения оценки.

    7. Щелчком на второй ячейке столбца Имя поля создадим поле с именем Оценка.

    8. Зададим для второго поля текстовый тип. Описание поля — Оценка, полученная на текущую дату.

    9. Мы будем создавать связанные таблицы так, чтобы в таблице, содержащей список оценок, полученных за день, не нужно было дублировать информацию об учащемся. Достаточно будет добавить ссылку на одну из записей таблицы Учащиеся. Эта ссылка будет иметь числовой тип. Для этой цели добавим ещё поле с именем Код_студента и типом Числовой и описанием Ссылка на одну из записей таблицы Учащиеся.

    10. Теперь нужно добавить индексированное поле и присвоить ему ключевой тип. Индексированные поля отличаются от обычных тем, что для них Access создаёт списки, позволяющие выполнять быструю сортировку и поиск по содержимому этого поля. Таблица может содержать несколько индексов. Ключ — это специальный индекс, идентифицирующий записи. Значения такого поля должны быть уникальными. Добавим поле Ключ и назначим ему тип Счетчик. Такой тип инициирует автоматическую генерацию уникальных значений.

    11. Сохраняя активной строку Ключ, щелкнем мышкой по кнопке Ключевое поле. В результате на кнопке этой строки появится значок ключа (см. рис. 4).

    12. Теперь закроем окно конструктора, Access спросит о необходимости сохранения структуры таблицы. Ответим “ДА”, щелкнув мышкой по соответствующей кнопке.

    13. В открывшемся диалоговом окне введем имя Оценки и нажмем на кнопку ОК.

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

    Рис. 4. Создание ключевого поля

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

    Рис. 5. Заполнение таблицы Студенты

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

    Заполним таблицу Оценки (см. рис. 6).

    Рис. 6. Заполнение таблицы Оценки

    В таблице Оценки в поле Код_студента указываются ссылки на записи таблицы Студенты. При этом Access позволяет автоматизировать использование ссылок между таблицами с помощью списка подстановки, который настраивается следующим образом:

    1. Щелкнем по раскрывающемуся списку Тип данных поля Код_студента и выберем пункт Мастер подстановок (см. рис. 7).

    Рис. 7. Мастер подстановок

    2. В первом окне мастера выберем переключатель Объект “столбец подстановки” и нажмем кнопку Далее.

    3. Переключатель второго окна мастера предлагает список таблиц, запросов или объединение этих списков. Выберем таблицу Студенты в качестве источника подстановки (см. рис. 8) и нажмем кнопку Далее.

    Рис. 8. Создание подстановки: выбор таблицы Студенты

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

    5. Добавим в список Выбранные поля пункты Фамилия, Имя и ФИО родителей, как показано на рис. 9.

    Рис. 9. Создание подстановки: добавление в список: Выбранные поля

    6. Нажмем кнопку Далее и Мастер предложит нам выбрать порядок сортировки — рис. 10.

    Рис. 10. Выбор порядка сортировки

    7. В следующем окне Мастер продемонстрирует три столбца таблицы источника — рис. 11.

    Рис. 11. Три столбца таблицы источника

    8. Нажмем кнопку Далее и зададим подпись, которую должен содержать столбец подстановки, эта подпись заменит имя столбца Код_студента (см. рис. 12).

    Рис. 12. Подпись, которую должен содержать столбец подстановки

    9. Нажмем кнопку Готово и подтвердим сохранение построенной структуры (рис. 13).

    Рис. 13. Подтверждение сохранения построенной структуры

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

    1. В окне базы данных нажмем на кнопку Запросы.

    2. Дважды щелкнем мышкой по значку Создание запроса с помощью мастера.

    3. В раскрывшемся списке Таблицы и запросы первого окна мастера

    выберем таблицу Оценка.

    4. В списке Доступные поля щелкнем мышкой по строке Дата.

    5. Щелкнем мышкой по кнопке >, чтобы переместить выделенное поле в список Выбранные поля.

    6. Повторяя шаги 3-5, добавим в список Выбранные поля поле Оценки, Дисциплина таблицы Оценки и поля Фамилия и таблицы Студенты (см. рис. 14). По окончании нажмем на кнопку Далее.

    Рис. 14. Добавление в список Выбранные поля

    7. На рис. 15 представлен завершающий этап создания запроса — выбор имени запроса. В качестве имени по умолчанию Access предложил имя Оценка Запрос, которое можно заменить на любое другое имя.

    Рис. 15. Задание имени запроса

    8. Дадим запросу имя Учебный запрос и щелкнем мышкой по кнопке Готово.

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

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

    Дополнительно создадим еще запросы по успевающим и неуспевающим студентам. Для этого выполним следующие действия:

    1. Щелчком на кнопке Окно базы данных переключимся на список запросов.
    2. Дважды щелкнем по значку Создание запроса в режиме конструктора.
    3. Добавим ранее созданные нами таблицы Оценки и Студенты (рис. 16).
    4. Рис. 16. Добавление таблицы

    5. Для запроса Успевающие студенты (так заранее назовем этот запрос) необходимо выбрать имя таблицы, нужное поле и задать условие отбора для поля Оценка. В поле Условие отбора для поля Оценка введем выражение вида Like “3*” Or Like “4*” Or Like “5*” (рис. 17).
    6. Рис. 17. Задание условия отбора

    7. Нажмем кнопку Запуск на панели инструментов. В результате создания данного запроса на экране появится запрос Успевающие студенты (рис. 18).
    8. Рис. 18. Запрос на выборку

    9. При закрытии данного запроса Access спросит пользователя о сохранении запроса. Нажмем кнопку “Да” и дадим имя запросу “Успевающие студенты”.
    10. Повторяя шаги 2-5, создаем запрос для неуспевающих студентов с тем отличием, что из таблицы Студенты берем дополнительно следующие поля: Домашний адрес, ФИО родителей и Домашний телефон, и для поля Оценка в поле Условие отбора необходимо ввести выражение Like “2*” (рис. 19).

    Рис. 19. Запрос: Неуспевающие студенты

    Видеоурок по данной теме

    Видеоурок для Интернета

    Видеоурок для локальной сети

    Скачать Real Player

    - Содержание дисциплины


    Закрепление материала
    Тестирование материала
    Содержание дисциплины