Запросы

 

Существует несколько типов запросов: на выборку, на добавление, на уда­ление, на обновление, запрос на создание таблиц, перекрестный запрос.

Запросы могут создаваться двумя способами: «вручную» и с помощью мас­тера. Имеется четыре мастера запросов:

Простой запрос

Служит для создания простых запросов на основе вы­бранных полей.

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

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

Повторяющиеся за­писи

Запрос такого типа позволяет выбирать из таблицы или простого запроса повторяющиеся записи.

Записи без подчинен­ных

Такой запрос выбирает из таблицы записи, не связанные с записями из другой таблицы.

С помощью Конструктора запросы создаются «вручную».

 

Создание запросов

При создании макета запроса в общем случае необходимо выполнить следующие базовые операции:

1)      указать системе, какие поля и из каких таблиц мы хотим включить в запрос;

2)      указать тип запроса (по умолчанию установлен запрос на выборку);

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

4)      описать групповые операции над записями исходных таблиц;

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

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

           

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

Задание 9

Создать запрос Адреса для вывода фамилий, имен и адресов студентов.

1)      перейдем на вкладку Запросы и щелкнем на кнопке Создать с помощью мастера, чтобы перей­ти к созданию запроса;

2)      на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3)      выберем значение Простои запрос и нажмем кнопку ОК;

4)      в первом диалоговом окне в списке Таблицы/Запросы выберем таблицу, по­ля которой будут анализироваться при выполнении запроса (Сведения о студентах);

5)      в списке Доступные поля отметим поле Фамилия и перенесем его в список Выбранные поля:

6)      те же действия выполним для полей Имя и Адрес. Нажмем кнопку Далее;

7)      в последнем окне присвоим запросу имя Адреса. После этого нажмем кноп­ку Готово.

 

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

введем в поле номера  >4 – отбор записей, номер которых больше 4;

  для даты рождения >12.07.76, - вывод всех, кто родился позже указанной даты;

для фамилий  И*  – отбор начинающихся на указанную букву.

Примеры использования символов задания шаблонов

Использования символов

Образец

Поиск в Access

?

обозначает один любой символ

И?анов

Иванов

Ипанов

Исанов

*

обозначает любое количество любых символов

Д*нь

 

Добрый день

День

Длинная тень

*а

Петрова

Анна

Васильевна

#

обозначает любую цифру

#-й

1-й

9-й

Кроме операций сравнения можно использовать арифметические (+ - * / ^), логические (Or, And, Not), например, введем в поле фамилия  >=В* and <= Д*,- отбор записей, чья фамилия начинается на букву от В до Д 

Примеры условных выражений

Условные выражения

Вывод записей, которые

Пермь

Имеют значение Пермь

Not Пермь

Не имеют значение Пермь

<М*                  

Начинаются на букву от А-Л

>О*

Начинаются на букву от П-Я

50

Имеют значение 50

>01.01.2004

Имеют значение даты позднее 01.01.2004

**.02.**

Имеют значение месяца февраль

Like “P*”

Начинаются с буквы Р

Кроме этого есть ряд специфических операций, а также функции. Примеры можно найти в справочной системе (в разделе "создание выражений").

 

Задание 10.

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

1)    Для выполнения задания воспользуемся конструктором запросов. Активируем в левом меню БД объект запросы и вызовем режим создания запроса с помощью конструктора.

 

Рис. 14. Диалоговое окно создания запроса.

 

Добавим в конструктор таблицу «Сведения о студентах»

 

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

 

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

 

Рис. 16. Режим конструктора запроса

 

В верхней половине отображается выбранная таблица.

Нижняя часть окна - бланк запроса - содержит описание запроса в таблич­ной форме. Каждая колонка в нем отвечает одному полю. Строки Поле и Имя таблицы содержат списки, которые позволяют определить нужное поле.

2)    Щелчком мыши в строке поле активируем список и последовательно выберем все поля таблицы Таблица расширяется вправо автоматически, когда пользователь добавляет в запрос новые поля.

3)    Заполнив строку Условие отбора, мы сможем произвести выборку из базы данных. В поле «Пол» в строку Условие отбора внести букву «м» - в том формате, (в котором вносилась информация в таблицу!, то есть маленькая русская буква без кавычек и точек)

4)    В поле «Фамилия» в строке Сортировка выбрать «По возрастанию»

5)    Закрыть окно конструктора, присвоив запросу имя «1 отдел»

 

Задание 11.

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

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

2)    В поле Фамилия в строке Условие отбора внести текст в квадратных скобках «Введи фамилию». Текст должен выглядеть так:   [Введи фамилию].

3)    Сохранить запрос с именем Фамилия.

4)    Выполнить запрос.

 

Самостоятельная работа № 3

1.       Создайте запрос Телефоны, позволяющий выводить фамилии, имена, теле­фоны студентов.

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

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

 

Формирование запросов с вычисляемыми полями.  Использование построителя выра­жений

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

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

1)      открыть запрос в режиме конструктора;

2)      установить указатель в позицию, в которую требуется ввести выраже­ние, и нажать правую клавишу мыши;

3)      в контекстном меню выбрать команду Построить. Другой вариант -нажать кнопку Построить на панели инструментов.

Построитель выражений состоит из трех разделов.

 

Рис 17. Построитель выражений

 

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

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

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

Кроме традиционных знаков математических действий существует еще не­сколько операторов:

\

Обратная косая черта обозначает деление целых частей де­лимого и делителя. Результат округляется до целых.

^

Возведение в степень.

Mod

Операция получения остатка от деления целых частей аргу­ментов.

&

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

Like

Используется для создания масок при определении строк с неизвестными символами и требует дополнительных спе­циальных символов и правил синтаксиса. Например,

? - знак вопроса означает любой одиночный символ

* - звездочка означает любую последовательность сим­волов

# - обозначает любую неизвестную цифру.

And Or Not

Логические операторы также используются для построения условий отбора. Они могут применяться к двум или не­скольким выражениям и используются со скобками.

Чтобы вывести полный список операторов, выберите папку Операторы в нижнем левом поле и нужный тип в среднем поле. В правом поле будут выве­дены все операторы выбранного типа.

В нижней части окна построителя находятся три поля.

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

В среднем поле задаются определенные элементы или типы элементов для папки, заданной в левом поле. Например, если выбрать в левом поле Встроенные функции, то в среднем поле появится список всех типов функций Microsoft Access.

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

 

Задание 12.

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

Для решения поставленной задачи можно воспользоваться готовым за­просом Рейтинг студентов.

Выполним следующие действия:

1)           в окне базы данных перейдем на вкладку Запросы и щелкнем на кноп­ке Создать, чтобы перейти к созданию запроса;

2)           на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3)           выберем значение Конструктор и нажмем кнопку ОК;

4)           добавим таблицу Сведения о студентах, необходимую для создания запроса и запрос Рейтинг студентов;

5)           в первую колонку строки Поле бланка запроса поместим поле Фами­лия таблицы Сведения о студентах;

6)           в строке Сортировка установим значение по возрастанию;

7)           во вторую колонку поместим поле Avg_оценка, в котором хранятся средние значения;

8)           в строке Условие отбора запустим Построитель выражений;

9)           в левой части построителя выберем объект Запрос, а из раскрывшегося списка - запрос Рейтинг студентов;

10)      в средней части построителя выражений выберем поле, необходи­мое для построение выражения - Avg_оценка и вставим его в поле вы­ражений;

11)      зададим значение <3. Таким образом, мы задали отбор тех студентов группы, у которых средний балл меньше 3;

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

13)      аналогично, выберем таблицу Сведения о студентах и поле Пол;

14)      укажем условие отбора - ~ "ж";

15)      нажмем кнопку ОК после чего окно построителя закроется и мы вернемся в бланк запроса;

16)      снимем флажок в строке Вывод на экран столбца Avg_оценка;

17)      сохраним запрос.

 

 Группировка в запросах

Создать запрос Рейтинг студентов для подсчета среднего балла каждого студента в группе.

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

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

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

В нашем случае отношение между таблицами уже задано, между полями таблиц проведена линия.

1)      в окне базы данных перейдем на вкладку Запросы и щелкнем на кноп­ке Создать, чтобы перейти к созданию запроса;

2)      на экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса;

3)      выберем значение Конструктор и нажмем кнопку ОК;

4)      в результате на экране появится два окна: окно конструктора запросов Запрос: Запрос на выборку и окно выбора таблиц Добавление табли­цы. Окно Добавление таблицы состоит из трех вкладок, содержащих перечни объектов, предлагаемых программой для проектирования за­проса: Таблицы, Запросы, Таблицы и запросы. В бланке запроса ука­жем параметры запроса и данные, которые нужно отобрать, а также способ их отображения на экране;

5)      перейдем на вкладку Таблицы, маркируем таблицу Сведения о студентах и щелкнем на кнопке Добавить. Затем маркируем таблицу Ус­певаемость  группы и снова выполним щелчок на кнопке Добавить. Закроем диалоговое окно (кнопка Закрыть). В окне проектирования запроса появятся имена выбранных таблиц;

6)      установим тип запроса Выборка, нажав кнопку Тип запроса на панели инструментов;

7)      выполним двойной щелчок на поле Фамилия таблицы Сведения о студентах. В результате имя этого поля будет помещено в строку Поле бланка запроса. Добавить нужные поля в бланк запроса можно также перетаскиванием их имен из списка, находящегося в верхней части ок­на конструктора, в строку бланка Поле,

8)      аналогично во вторую колонку строки Поле вставим имя поля Имя из таблицы Сведения о студентах;

9)      для упорядочения отобранных записей по алфавиту воспользуемся возможностями сортировки самого запроса. Для этого в строке Сор­тировка столбца Фамилия откроем список значений и зададим сорти­ровку данных по возрастанию;

10) аналогичные операции выполним для поля Имя таблицы Сведения о студентах;

11) вставим в бланк запроса поле Оценка из таблицы Годовая Успе­ваемость группы;

12) в бланке запроса добавим строку Групповые операции;

13) в полях Фамилия и Имя строки Групповые операции установим значение Группировка:

14) в поле Оценка установим значение Avg. Нам необходимо, чтобы после запятой стояло только одно число (например, 3.4 или 4.5). Для этого пало указать формат вывода результатов в запросе. Выполним следующие операции: в строке Групповая операция поля Оценка щелкнем правой клавишей мыши и из раскрывшегося меню выберем команду Свойства. В строке Формат поля окна Свойства поля вве­дем значение Фиксированный, а в поле Число десятичных знаков -числовой. Можно также указать подпись поля - Средний балл.

15) сохраним запрос с именем Рейтинг студентов.

 

Самостоятельная работа № 4

1.       Исправьте запрос Рейтинг студентов так, чтобы выводились данные  только хорошо успевающих студентов.

2.       Создать запрос Наименьшая оценка, позволяющий выводить мини­мальную оценку каждого студента группы.

 

Сложные запросы

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

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

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

Для создания запроса на обновление выполните следующее:

1)      предварительно составьте запрос на выборку;

2)      затем выполните составленный запрос и оцените результат, переклю­чившись в режим таблицы с помощью команды Режим таблицы из меню Вид;

3)      после этого вернитесь в режим конструктора и активизируйте команду Обновление из меню Запрос;

4)      Access 97 добавит в бланк запроса строку Обновление, которая пред­назначена для указания новых значений полей таблицы. В качестве та­ковых могут выступать и вычисляемые выражения;

5)      в специальном диалоговом окне Access укажет, сколько записей будет изменено в таблице, и потребует подтвердить выполнение этой опера­ции.

 

Задание 13.

Используя запрос Оценки студента и возможности запроса на обновление, измените все оценки какого-либо студента на 5.

 

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

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

Выполните следующее:

1)      предварительно подготовьте запрос на выборку;

2)      выполните составленный запрос для проверки его правильности;

3)      вернитесь в режим конструктора и в меню Запрос выберите команда Созда­ние таблицы, в которое необходимо ввести имя новой таблицы;

4)      выполните запрос, нажав кнопку с восклицательным знаком на панели инст­рументов;

5)      в специальном окне Access укажет, сколько записей добавляется с новую таблицу, и потребует подтвердить выполнение этой операции.

 

Задание 14

С помощью запроса Дата рождения и запроса на создание таблицы соз­дайте таблицу Выборка дней рождения студентов.

 

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

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

1)      для отбора добавляемых записей сначала составьте запрос на выборку;

2)      выполните составленный запрос и оцените результат, переключились в режим таблицы с помощью команды Режим таблицы из меню Вид;

3)      после этого вернитесь в режим конструктора и активизируйте команду  Добавление из меню Запрос;

4)      в открывшемся при этом диалоговом окне Добавление задайте в поле Имя таблицы имя таблицы, к которой вы будете присоединять данные из вы­бранного набора записей;

5)      после нажатия кнопки OK Access добавляет в бланк запроса строку Добав­ление. В эту строку автоматически или в ручную вставляются имена тех по­лей целевой таблицы, которые совпадают с именами полей запроса;

6)      выполните запрос, нажав кнопку Запуск на панели инструментов;

7)      в специальном диалоговом окне Access укажет, сколько записей будет до­бавлено к целевой таблице, и потребует подтвердить выполнение этой опера­ции.

 

Задание 15

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

 

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

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

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

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

2)      затем выполните составленный запрос и оцените результат, переклю­чившись в режим таблицы с помощью команды Режим таблицы из  меню Вид;

3)      после этого вернитесь в режим конструктора и активизируйте команду Удаление из меню Запрос;

4)      Access добавит в бланк запроса строку Удаление и введет в ее ячейки значение Условие. Это означает, что пользователь может установить дополнительные критерии отбора;

5)      выполните запрос, нажав кнопку Запуск на панели инструментов;

6)      в специальном диалоговом окне Access укажет, сколько записей будет удалено из таблицы, и потребует подтвердить выполнение этой опера­ции.

 

Задание 16.

Удалите из таблицы Выборка дней рождения учеников данные об учени­ках, родившихся с I по 12 число.

 

 

Hosted by uCoz