Обработка запросов

Цели работы:

• научиться составлять выражение запроса;
• научиться генерировать запрос в среде FoxPro;
• разработать обработку запроса на разных версиях базы данных.

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

В FoxPro пользователю доступны два способа проектирования запросов:

1. Воспользовавшись методом RQBE (RQBE = Relational Query by Example = Запрос по образцу), можно в диалоге (интерактивно) формулировать запросы к базам данных.
2. Запрос можно описать последовательностью параметров SELECT-директивы языка SQL (SQL = Sructured Query Language = Структурированный язык запросов) в командном окне или программе FoxPro.

Язык SQL используется как для разработки прикладных программ, так и для "ручного" манипулирования базами данных. FoxPro поддерживает четыре SQL-директивы:

- CREATE CURSOR;

- CREATE TABLE;

- INSERT;

- SELECT.

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

- FROM;

- WHERE;

- GROUP BY;

- HAVING;

- ORDER BY.

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

Параметр WHERE связывает все участвующие таблицы одна с другой и определяет условие, которому должны отвечать отбираемые запросом данные. Без WHERE все записи данных первой таблицы были бы связаны со всеми записями данных второй таблицы и т.д. WHERE ограничивает количество результирующих данных определенным условием. В WHERE-условиях пользователь может применять операторы сравнения (=,<,> и т.д.), а также логические операторы ALL, ANY, BETWEEN, EXISTS, LIKE и отрицание NOT.

В одном WHERE-параметре можно связать между собой несколько условий, объединив их как с помощью логической операции И (AND), так и с помощью логической операции ИЛИ (OR). При вычислении значения логического выражения операции AND имеют приоритет по отношению к операциям OR (подобно тому, как операция умножения имеет приоритет перед операцией сложения).

Пример:
SELECT *;

FROM, clients, order;

WHERE clients-city IN ("Псков", "Новгород");
AND order. quantity>100

В результате запроса отбираются все записи (целиком, т.е. включая все поля) двух баз данных Clients и Order для тех клиентов, фирмы которых находятся в Пскове или Новгороде и число заказов которых превышает 100.

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

Пример:
SELECT order.clientnmn, SUM (order.quantity);

FROM order;

GROUP BY order. Clientnum

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

Примечание:

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

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

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

• форма вывода информации;
• полученные результаты;
• текст на SQL.

5. Измените содержимое БД (добавьте, удалите или откорректируйте записи).
6. Выполните запрос для новой версии БД. Сделайте выводы.

2. Выполните генерацию запроса в среде FoxPro.
3. Выполните запрос. Результаты выведите на экран.
4. Изучите текст SQL.

2. Настройте запрос, пользуясь возможностями диалогового окна "Запрос".
3. Выполните запрос. Проанализируйте результаты.
4. Изучите текст на SQL.
5. Сохраните запрос.

Построение простого запроса

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

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

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

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

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

Рис. 6.2. Отбор полей

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

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

Теперь определим критерии отбора записей в области Критерий отбора RQBE-окна (рис. 6.3).

Рис. 6.3. Простой запрос в RQBE-окне

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

1. Необходимо перейти в первую пустую зону критериев и выполнить щелчок мышью на поле Имя поля, чтобы развернулся список всех полей базы данных. Из данного списка нужно выбрать поле Client.city, поскольку в этом поле будет организован поиск.
2. В средней колонке уже находится список операторов сравнения, из которого нужно выбрать например, оператор похоже на.
3. В последней колонке Образец пользователь вводит искомое значение: Норвегия.

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

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

Для того, чтобы в дальнейшем можно было выполнить данный запрос еще раз, необходимо данный проект запроса сохранить. Выбрав директиву Файлñ Сохранить как... (при активном RQBE-окне), выберите нужную директорию и назначьте файлу имя. FoxPro помещает проект запроса в файл с расширением QPR.

Построение сложного запроса для нескольких баз данных

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

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

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

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

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

Группировка данных

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

Для группировки данных применяется параметр GROUP BY
SQL-директивы.

Для определения группировки необходимо активизировать контрольный индикатор Группы. FoxPro откроет диалоговое окно выбора поля группировки. В левом списке приведены не только поля, объявленные выводимыми, но и все остальные поля выбранных таблиц.

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

Сортировка данных

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

Для сортировки данных используется параметр ORDER BY SQL-директивы.

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

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

Вывод результатов запроса

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

1. Просмотр. Результат выводится в виде стандартной таблицы, в которой каждая строка - это выбранная запросом запись, а колонки - выводимые поля. Как только таблица-результат закрывается, результаты запроса теряются. Это установка по умолчанию.
2. Report/lLabel. Результат вносится в существующий или автоматически создаваемый для такого случая отчет или в этикетки.
3. Таблица/DBF. Данные результата запроса записываются в FoxPro-базу данных и таким образом могут храниться длительное время. После выбора данной опции появляется диалог Save As, в котором для этой базы данных вводится нужное имя. После выполнения запроса новая база данных остается открытой в свободной рабочей области.
4. Курсор. Автоматически создается таблица-курсор. FoxPro при этом предлагает имя для этой таблицы, соответствующее имени файла запроса. Однако пользователь может назначить в поле ввода другое имя. После выполнения запроса пользователь может обратиться к результирующей таблице, которая открыта в свободной рабочей области. Данные в этой таблице можно только прочитать, внесение изменений запрещено. Кроме того, речь идет о временной таблице, которая при закрытии окна удаляется.
5. Графика. SQL-запрос подготавливается с помощью программы, имя которой задано в системной FoxPro-переменной GENGRAPH. Предполагается, что запрос содержит не более одного текстового поля, которое используется в этом случае в качестве надписей для числовых полей данных. Сама графика создается программой MS Graph.

Отчет по работе должен содержать:

1. Проектные материалы (описание запросов, логические выражения).
2. На диске Х:\ файлы содержащие проекты простых и сложных запросов.

Контрольные вопросы

1. Что такое запрос?
2. Привести примеры 2–3 простых запроса (логические выражения).
3. Привести пример сложного запроса.
4. Прокомментировать текст на SQL для примеров, указанных преподавателем.