Методические указания к лабораторной работе №2.

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

Основные сведения

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

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

В общем случае существует пять основных категорий запросов:

1.    Запрос на выборку - самый распространенный тип запроса, извлекает данные из одной или нескольких таблиц и отображает результаты в режиме таблицы.

2.    Запрос с параметрами - запрос, при запуске которого открывается диалоговое окно с приглашением ввести условия отбора записей.

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

4.    SQL - запрос, создаваемый с помощью инструкции SQL.

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

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

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

Предварительные операции

1. Необходимо разработать модель БД и создать таблицы на её основе.

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

Создание запроса (на примере телефонного справочника)

1.      В окне  контейнера БД выбираем вкладку Запросы и нажимаем кнопку «Конструктор», запуская режим создания запроса (рис.1).

Рисунок 1 – Окно управления запросами

2.      После нажатия кнопки Access откроет два окна: окно конструктора запросов и диалоговое окно Добавление таблицы (рис.2). Здесь:

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

Добавить таблицы при создании запроса можно также с помощью команды Запрос|Отобразить таблицу, кнопки панели инструментов Отобразить таблицу:

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

Удалить таблицу из конструктора запросов, предварительно выделив ее, можно либо клавишей Del, либо с помощью команды Запрос|Удалить таблицу.

Рисунок 2 – Окно конструктора запросов при добавлении таблиц

Окно конструктора запросов состоит из двух областей: области таблиц (рис.3, вверху) и бланка запроса (рис.3, внизу).

Рисунок 3 – Окно редактирования запроса в режиме конструктора

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

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

Добавить поля в бланк запроса из таблиц, расположенных в области таблиц запроса можно несколькими способами:

- перетаскиванием нужного поля с помощью мыши;

- двойным щелчком по нужному полю;

- выбором из списка непосредственно в поле бланка.

Можно осуществить Выбор всех полей таблицы:

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

Удаление всех полей из бланка запроса выполняется с помощью команды Правка| Очистить бланк.

Отображение результирующего набора данных выполняется с помощью команды Вид|Режим или первой слева кнопки панели инструментов. Таким же образом можно переключаться между режимами Конструктор и Режим таблицы.

Работа с полями - изменение порядка расположения, выделение, удаление.

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

Ø       установить курсор слева от первой буквы имени поля в строке Поле в области бланка запроса;

Ø       набрать новое имя и поставить двоеточие (таким образом, новое и старое имена будет разделять символ :).

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

Установка сортировки. Сортировка по нескольким полям. Access всегда начинает сортировку с крайнего слева поля.

Отбор записей

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

При формировании условия нужно уделять внимание типу данных: 

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

Иванов Иван Иванович;    = Иванов Иван Иванович;

" Иванов Иван Иванович ";    =" Иванов Иван Иванович ".

- поля с типом Числовой, Дата/время и Логический часто требуют заключения условия в специальные символы #, позволяющие отличать их от текстовых значений:

#23.03.2003#;   =#23.03.2003#

Условия можно задавать c помощью символов отношения:

>=#23.03.2003#;   <" Иванов Иван Иванович "

что означает выделение дат, больших чем или равных 23-му марта 2003 года, или же выделение абонентов с фамилией, меньшей по алфавиту, чем «Иванов …».

Кроме того, можно использовать логические операции над условиями AND (и), OR (или), NOT (не), а также операторы сравнения Is (есть) и Like (подобный). Например, можно ввести not Иванов Иван Иванович.

Заполнение бланка запросов делаем так:

1. В столбце бланка выбираем таблицу, из которой затем выберем поле (рис.4):

à

Рисунок 4 – Выбор таблицы и поля из неё

2. После выбора поля определяем, нужна ли сортировка результатов запроса по этому полю (рис.5), а также определяем, будем ли выводить значения этого поля на экран или нет (галочка в строке «Вывод на экран»)
3. Указываем условие отбора, если это необходимо для данного столбца. Например, если мы хотим вывести все фамилии на «П», то можем записать условие вида (рис.6):                                               Like “П*”

«Звёздочка» означает, что после буквы П может быть сколько угодно каких угодно символов.

4. Добавим ещё несколько столбцов, чтобы получить более представительный результат запроса (рис.7). Получим запрос с простым условием отбора.

Рисунок 7 – Заполненный бланк запроса

5. Запустим запрос щелчком мыши на кнопке «Запуск» панели инструментов (рис.8). Увидим результат запроса на выборку данных (рис.9).

Рисунок 8 – Кнопка «Запуск»

Рисунок 9 – Результат выполнения созданного запроса

Для формирования сложного условия необходимо использовать знаки операций сравнения и логические функции: AND, OR, XOR и NOT.

Функция AND выбирает запись в случае, если и первое и второе условие выполнены.

Функция OR выбирает запись в случае, если или первое, или второе, или оба условия выполняются (XOR при выполнении обоих условий игнорирует запись).

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

Расположение условий в бланке имеет свой смысл: например, условие вида рис.10 приведёт к выбору только тех записей, которые удовлетворяют обоим условиям. Т.е. это реализация функции AND: Выбрать те, что «(Like “П*”) AND (=”администрация”)» (см.рис.11).

Если же расположить условия на разных строках (рис.12), то это будет функция OR:

Выбрать те, что «(Like “П*”) OR (=”администрация”)» (см.рис.13).

Рисунок 10 – Запрос с составным условием

Рисунок 11 – Результат запроса с составным условием

Рисунок 12 – Задание условия на разных строках

Рисунок 13 – Результат запроса с условием на разных строках

Наконец, можно задавать условия с вводом данных пользователем. Для этого указываем в условии приглашение пользователю на ввод данных, заключив его в   « [ ] » (см.рис.15). При запуске запроса Access выведет на экран окно вида рис.14:

Рисунок 14

Рисунок 15

и результат запроса выглядит так (рис.16):

Рисунок 16

Операторы, функции и выражения

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

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

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

Более подробные сведения об операторах можно найти в справочной системе.

Функции - это выражения, которые всегда возвращают значение, полученное на основе входящих в выражение действий. В зависимости от типа функции могут возвращать число или строку символов. В  Access предусмотрены сотни встроенных функций общего назначения, которые можно использовать в таблицах, запросах, формах и отчетах. Кроме того, с помощью языка Visual Basic можно создавать функции, определенные пользователем.

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

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

Ø     щелчком по кнопке Построить панели инструментов (на кнопке рисунок - палочка и точки);

Ø     командой Построить контекстного меню.

Создание запросов на выборку

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

При создании запросов необходимо руководствоваться правилом: для задания условий отбора записей следует использовать операторы отношения и логические операторы, а для создания вычисляемых полей - математические и строковые операторы.

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

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

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

Создание групповых запросов

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

Для выполнения группового запроса необходимо в режиме конструктора активизировать строку Групповая операция. Для этого служит команда Вид|Групповые операции или кнопка Групповые операции панели инструментов.

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

Ø       группировка;

Ø       итоговые функции;

Ø       выражение;

Ø       условие.

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

Категория Итоговых функций состоит из 9 функций: Sum, Avg, Min, Max, Count, StDev, Var, First и Last. Каждая из этих функций выполняет вычисление над полем.

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

Категория Условие также состоит только из одноименной функции.

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

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

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

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

Для перекрестного запроса необходимо указать три параметра:

Ø         поле заголовков строк;

Ø         поле заголовков столбцов;

Ø         поле для выбора значения.

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

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

1.      Создать перекрестный запрос.

2.      Выполнить команду Вид|Свойства в режиме Конструктор.

3.      В диалоговом окне свойств запроса щелкнуть в строке Заголовки столбцов.

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

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

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

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

Создание параметрического запроса о прибылях и убытках за определенный период времени

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

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

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

Ø       удаление записей из таблицы или группы таблиц;

Ø       добавление записей из одной таблицы в другую;

Ø       обновление информации в группе записей;

Ø       создание таблицы.

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

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

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

2.      Просмотреть выбранные записи в режиме просмотра результирующей таблицы.

3.      Запустить запрос на изменение с помощью кнопки Запуск.

4.      Проверить внесенные изменения.

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

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

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

Создание запроса на обновление выполняется по следующему алгоритму.

1.      Создать запрос на выборку и просмотреть необходимые данные в режиме таблицы.

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