Одним из основных назначений разработанного приложения является быстрый поиск информации в базе данных и получение ответов на разнообразные вопросы. Для этих целей в Visual FoxPro используются средства, называемые запросами.
Например, вам необходимо выбрать из таблиц информацию о клиентах, проживающих в Москве и Московской области, или сформировать список клиентов, купивших в последний месяц товаров на сумму свыше 5000 рублей, и упорядочить их в алфавитном порядке по полю, содержащему фамилии клиентов. Для решения таких задач предназначен конструктор запросов и команда SELECT языка Visual FoxPro.
С помощью конструктора запросов Visual FoxPro вы можете формировать различной сложности критерии для выбора записей из одной или нескольких таблиц, указывая при этом, какие поля должны быть отображены в запросе. Над полями, выбираемыми из таблиц с помощью запросов, можно выполнять различные вычисления.
Рис. 9.1. Ввод условия выборки в мастере запросов
Результатом запроса является таблица, которую вы можете сохранить в массиве, в создаваемой новой таблице, отобразить на экране в режиме Browse (Просмотр) или вывести в виде отчета.
Для создания запросов вы можете использовать мастер запросов, который последовательно запрашивает наименования таблиц, используемых в запросе, перечень полей таблиц, критерий упорядочения и условия фильтрации данных. На рис. 9.1 приведено диалоговое окно мастера, позволяющее сформировать условия фильтрации выбираемых из таблицы данных. Мы не будем рассматривать создание запросов с помощью мастера, т. к. конструктор запросов достаточно прост и работа в нем у вас не вызовет затруднений.
Для создания запроса в окне конструктора запросов выполните следующие действия:
На экране появляется окно конструктора запросов (рис. 9.2), которое содержит названия выбранных таблиц, а в основном меню появляется пункт Query (Запрос). Можно приступать к формированию условий запроса.
Совет
Для открытия ранее созданного запроса в окне конструктора запросов на вкладке Data (Данные) окна проекта в группе Queries (Запросы) найдите модифицируемый запрос, установите на него курсор и нажмите кнопку Modify (Модифицировать).
Далее, открывая в конструкторе запросов необходимые вкладки, вы выполняете следующие действия:
В верхней части окна конструктора запросов расположена панель, на которой отображаются используемые в запросе таблицы. Ниже находятся вкладки, предназначенные для выбора полей запроса и формирования условий выборки. Назначение этих вкладок приведено в табл. 9.1.
Рис. 9.2. Окно конструктора запросов с выбранной таблицей клиентов
Таблица 9.1. Назначение вкладок окна конструктора запросов
| Вкладка | Назначение |
| Fields (Поля) | Позволяет указать поля исходных таблиц, выбираемые в результирующий запрос |
| Join (Объединение) | Позволяет задать условия объединения таблиц |
| Filter (Фильтр) | Позволяет определить фильтры, накладываемые для выбора записей |
| Order By (Упорядочение) | Позволяет задать критерии упорядочения данных |
| Group By (Группировка) | Позволяет задать условия группировки данных |
| Miscellaneous (Разное) | Позволяет задать дополнительные условия, такие как признак выборки повторяющихся значений, количество или процент выбора данных |
Команды, используемые при формировании запросов
Для работы в окне конструктора запроса можно использовать команды меню Query и панель инструментов Query Designer (Конструктор запроса). Назначение команд и кнопок на панели инструментов описано в табл. 9.2.
Таблица 9.2. Назначение команд меню Query и кнопок панели инструментов Query Designer
| Команда меню | Кнопка | Назначение |
| Add Table (Добавить таблицу) | Добавляет в запрос новую таблицу | |
| Remove Table (Удалить таблицу) | Удаляет выбранную таблицу из запроса | |
| Remove Join Condition (Удалить условие объединения) | — | Удаляет условие объединения таблиц |
| Output Fields (Результирующие поля) | — | Открывает вкладку Fields для выбора полей результирующей таблицы |
| Join (Объединение) | Открывает вкладку Join для задания условия объединения таблиц | |
| Filter (Фильтр) | — | Открывает вкладку Filter для задания фильтра |
| Order By (Упорядочение) | — | Открывает вкладку Order By для определения критерия упорядочения |
| Group By (Группировка) | — | Открывает вкладку Group By для определения условия группировки данных |
| Miscellaneous (Разное) | — | Открывает вкладку Miscellaneous для задания дополнительных параметров запроса |
| Query Destination (Результат запроса) | Открывает диалоговое окно Query Destination, в котором указывается, куда выводить результат запроса | |
| View SQL (Показать SQL) | Открывает диалоговое окно, в котором отображается SQL-оператор, соответствующий созданному запросу | |
| Maximize the table view (Максимизировать панель отображения) | Раскрывает панель отображения используемых в запросе таблиц на весь экран. Повторное нажатие на эту кнопку возвращает панели первоначальный размер | |
| Add Join (Добавить условие объединения) | Открывает диалоговое окно Join Condition для задания условия объединения таблиц | |
| Comments (Комментарии) | Открывает диалоговое окно, в котором вы можете ввести краткое описание создаваемого запроса | |
| Run Query (Выполнить запрос) | — | Запускает запрос на выполнение |
Совет
Для просмотра конструкции SELECT, соответствующей выборке, предназначены команда View SQL и кнопка Show the SQL window панели инструментов. Сформировав запрос, вы можете скопировать конструкцию SELECT во временный буфер Windows и использовать ее при написании программ.
Сформировав в окне конструктора запросов условия выборки, вы можете их сохранить в файле с расширением QPR, что позволит сэкономить время при последующих запусках запроса.
Для сохранения условий выборки в меню File (Файл) выберите команду Save as (Сохранить как). В открывшемся диалоговом окне Save As (Сохранить как) откройте папку, в которой хотите сохранить файл, введите в поле ввода имя файла и нажмите кнопку Сохранить.
В окне проекта запросы размещаются в разделе Queries (Запросы) вкладки Data (Данные). Для открытия запроса необходимо установить на него курсор и нажать кнопку Modify (Модифицировать) окна проекта.
После задания условий для выбора записей и указания результирующих полей в окне конструктора вы можете просмотреть результаты выполнения запроса. Для этого выполните одно из следующих действий:
На экране появятся результаты запроса, представленные в табличном виде.
Для просмотра результатов запроса, не открытого в окне конструктора запросов, необходимо в окне проекта установить курсор на имя запроса и нажать кнопку Run (Выполнить).
Формирование списка полей результирующей таблицы запроса
В результирующей таблице запроса поля формируются на основе полей исходных таблиц и вычисляемых полей. Для задания списка полей результирующей таблицы запроса предназначена вкладка Fields (Поля) конструктора запросов (рис. 9.3).
Рис. 9.3. Вкладка Fields предназначена для выбора полей запроса
Вкладка Fields (Поля) содержит два списка: Available fields (Имеющиеся поля) и Selected fields (Выбранные поля). Список Available fields (Имеющиеся поля) содержит все поля размещенных в окне конструктора таблиц. Selected fields (Выбранные поля) отображает поля формируемого запроса.
Перенести поля из списка Available fields (Имеющиеся поля) в Selected fields (Выбранные поля) вы можете одним из следующих способов.
Совет
Как и при работе с объектами в Windows, для выделения группы полей вы можете использовать мышь совместно с клавишами <Shift> и <Ctrl>. Сначала выделите первое из выбираемых полей и нажмите клавишу <Shift> или <Ctrl> в зависимости от того, расположены выбираемые поля рядом или вразброс. Затем, не отпуская клавишу, щелкните мышью по крайнему полю из группы подряд выбираемых полей или по каждому из отдельно расположенных полей.
Поля в результирующей таблице запроса будут следовать в том порядке, в котором они расположены в списке Selected fields (Выбранные поля). Для изменения расположения полей в этом списке можно использовать находящийся слева от поля маркер перемещения. Установите курсор на маркер и переместите поле в требуемую строку.
Visual FoxPro позволяет включать в результирующую таблицу запроса не только информацию из исходных таблиц, но и производить вычисления над ними, позволяя, таким образом, получить данные, отсутствующие в исходной таблице. Например, из базы данных, предназначенной для учета междугородних разговоров, вы можете выбрать поля таблиц с продолжительностью разговора и стоимостью одной минуты. На основании этой информации вы можете получить в запросе сведения о стоимости разговоров.
Кроме того, вы можете использовать вычисляемые поля для объединения нескольких полей исходной таблицы в одно результирующее поле. Например, таблица customer содержит фамилию, имя и отчество клиента. В результирующий запрос вы можете поместить одно поле, в котором будет размещена фамилия и инициалы клиента.
Для формирования выражения предназначено поле Functions and expressions (Функции и выражения) вкладки Fields (Поля). Нажмите расположенную справа от поля кнопку вызова построителя выражения и в диалоговом окне Expression Builder (Построитель выражения) создайте выражение для вычисляемого поля. После того как выражение в поле Functions and expressions (Функции и выражения) сформировано, нажатием кнопки Add (Добавить) перенесите его в список Selected fields (Выбранные поля).
Замечание
В окне Expression Builder (Построитель выражения) при создании выражения вы можете использовать поля исходных таблиц, константы, функции. Используя круглые скобки, вы можете изменить порядок вычисления или сгруппировать данные.
Используя данные из таблицы Customer, создадим запрос, результирующая таблица которого будет содержать название предприятия и полный адрес клиента с учетом города.
ALL/TRIM (Ccity) + ", " +ALLTRIM(Caddress)
Рис. 9.4. Определение вычисляемого поля
Рис. 9.5. Результат выборки
Вкладка Order By конструктора запросов
Вкладка Order By (Упорядочение) конструктора запросов (рис. 9.6) позволяет указать критерий упорядочения данных в результирующей таблице, используя одно или несколько полей исходной таблицы, перенесенных из списка Selected fields (Выбранные поля) в список Ordering criteria (Критерии упорядочения). Для каждого поля в списке Ordering criteria (Критерии упорядочения) вы можете указать критерий упорядочения, используя следующие опции переключателя Order options (Опции упорядочения):
В списке Ordering criteria (Критерии упорядочения) с левой стороны поля, для которого установлена опция Ascending (По возрастанию), расположена направленная вверх стрелка. Для обозначения упорядочения по убыванию используется стрелка, направленная вниз.
Рис. 9.6. Вкладка Order By конструктора позволяет сформировать условия упорядочения данных в запросе
Порядок сортировки записей результирующей таблицы определяется порядком следования полей в списке Ordering criteria (Критерии упорядочения) и критерием упорядочения отдельных полей. Для изменения порядка следования полей в списке Ordering criteria (Критерии упорядочения) предназначен маркер перемещения, расположенный слева от поля.
Рассмотрим создание запроса для таблицы customer, в котором упорядочим отображаемые в результирующей таблице данные по городам клиентов.
Рис. 9.7. Результаты выборки запроса
Упорядочение по нескольким полям
Теперь рассмотрим ситуацию, когда необходимо упорядочить данные по нескольким полям. В этом примере отобразим данные в результирующей таблице по городам клиентов в алфавитном порядке и по убыванию значений кредита. При формировании запроса воспользуемся запросом, созданным в предыдущем примере.
Рис. 9.8. Использование для упорядочения двух полей
В списке Ordering criteria (Критерии упорядочения) рядом с полем ccity расположена стрелка, направленная вверх, а рядом с полем YcreditLimit — стрелка, направленная вниз. Направленная вверх стрелка означает, что данные отображаются в порядке возрастания, а направленная вниз — что данные расположены в порядке убывания.
Задание условий для выбора записей
Во всех рассмотренных ранее примерах в запрос включались все или часть полей исходной таблицы, упорядоченных соответствующим образом. В большинстве случаев требуется получить отдельные записи исходной таблицы, удовлетворяющие определенным условиям.
Для формирования условий выбора определенных записей предназначена вкладка Filter (Фильтр) конструктора запросов.
Задание условия точного совпадения значений одного поля
На практике часто требуется выбрать из таблицы запись или группу записей, одно из полей которых содержит заданную величину. Например, вам требуется список всех клиентов, живущих в Москве. В этом случае вам необходимо на вкладке Filter (Фильтр) ввести требуемые значения в соответствующих полях, создав тем самым шаблон, с которым, прежде чем помещать записи в результирующую таблицу, Visual FoxPro будет сравнивать все записи исходной таблицы.
В этом примере выберем данные о клиентах из Москвы.
Рис. 9.9. Задание условия точного совпадения значения одного поля
Замечание
Если условие выбора, вводимое в поле Example, содержит специальные символы или ключевые слова языка запросов, поместите задаваемое условие в кавычки.
Выбор полей, не удовлетворяющих заданному условию
Флажок Not (He), расположенный на вкладке Filter (Фильтр) окна конструктора запросов, позволяет выбрать из таблицы записи, не удовлетворяющие заданному условию. Например, в предыдущем примере мы осуществляли выбор клиентов, проживающих в Москве. Если бы в дополнение к заданному условию выборки мы установили флажок Not (He), то в результирующей таблице мы получили бы список клиентов, не проживающих в Москве.
В данном примере выберем из таблицы customer все записи, за исключением записей со значением Казахстан в поле ccountry.
Рис. 9.10. Задание условия для выбора полей, не удовлетворяющих определенному критерию
Список вариантов сравнения Criteria (Критерий) вкладки Filter (Фильтр) содержит значения, позволяющие задавать различные критерии выбираемых в результирующую таблицу записей. При работе с большими таблицами время от времени возникает необходимость найти записи, точное написание которых вы не знаете. Например, вы не знаете, верхний или нижний регистр был использован при вводе записей. В этом случае вы можете осуществить выбор записей по условию неточного совпадения значений.
Предположим, что в таблице customer требуется найти запись о фирме, представитель которой имеет фамилию, начинающуюся на "Ник". Для поиска необходимых записей выполните следующие действия:
Рис. 9.11. Задание условия неточного совпадения
Выбор записей, находящихся в заданном диапазоне значений
Для выбора записей, лежащих в заданном диапазоне значений, используются операторы > (Больше), < (Меньше) и Between (Между) из списка Criteria (Критерий) вкладки Filter (Фильтр). Операторы > (Больше) и < (Меньше) используются в том случае, если задана только нижняя или верхняя граница диапазона, a Between (Между) — когда известны обе границы. Предположим, что вам потребовался список заказов, итоговая сумма которых превышает 10 000 рублей. В другом случае вам нужно получить сведения о клиентах, воспользовавшихся услугами вашей фирмы в определенный интервал времени. В обоих случаях необходимо выбрать из таблицы записи, попадающие в диапазон значений.
Создадим запрос, формирующий список заказов, итоговая сумма которых превышает 10 000 рублей.
На экране появляется результирующая таблица, содержащая заказы, итоговая сумма по которым превышает 10 000.
В этом примере мы задавали только нижнюю границу диапазона. Очевидно, что для получения списка продаж за определенный интервал времени необходимо задать начальную и конечную даты. Воспользуемся запросом, созданным в предыдущем примере, и изменим в нем заданные на вкладке Filter (Фильтр) условия выбора записей.
CTOD("01.11.2001") , CTOD("15.11.2001")
Рис. 9.12. Выбор записей по диапазону значений поля дат
Просмотрите данные в появившейся на экране результирующей таблице. Она содержит информацию о всех продажах за указанный в запросе интервал времени.
Замечание
При задании условий для выбора записей по диапазону значений можно использовать не только числовые поля, но и текстовые. В этом случае при выполнении запроса Visual FoxPro сравнивает коды символьных величин. Например, условие выбора "Л, М", помещенное в поле Example (Образец) для поля Ccompany, позволит выбрать из таблицы Customer список предприятий, названия которых начинаются на буквы Л и М.
Visual FoxPro позволяет при создании запросов формировать несколько условий. В том случае, если все задаваемые условия накладываются на одно поле, их можно разместить в одной строке. В противном случае условия размещаются в разных строках вкладки Filter (Фильтр). Рассмотрим следующий пример. Выберем всех покупателей из Москвы, Новгорода и Киева.
Рис. 9.13. Окно запроса со списком условий отбора
Во всех рассмотренных ранее в этой главе примерах данные при формировании запроса выбирались из одной таблицы. На практике при формировании запросов часто используются выборки из нескольких таблиц, т. к. в реляционных базах данных информация содержится не в одной отдельной таблице, а в совокупности связанных таблиц.
При создании многотабличного запроса в окно конструктора запросов добавляются все участвующие в выборке таблицы и определяются условия их объединения. Если между участвующими в запросе таблицами в базе данных установлены постоянные отношения, то в окне конструктора запросов эта связь будет отображаться в виде линии, соединяющей таблицы, а на вкладке Join (Объединение) появится запись, содержащая условие объединения таблиц (рис. 9.14).
Рис. 9.14. Вкладка Join содержит условия объединения таблиц
Если таблица, добавляемая в конструктор запросов, не имеет установленных в базе данных связей с уже размещенными в конструкторе таблицами, на экране открывается диалоговое окно Join Condition (Условие объединения) (рис. 9.15), в котором необходимо задать условие объединения двух таблиц.
В верхней части диалогового окна Join Condition (Условие объединения) размещены два раскрывающихся списка, содержащие поля, которые можно использовать для объединения таблиц. Ниже расположен переключатель Type of join (Тип объединения), содержащий опции, определяющие тип создаваемой между таблицами связи. Назначение опций описано в табл. 9.3.
Рис. 9.15. Определение условия объединения таблиц в диалоговом окне Join Condition
Таблица 9.3. Назначение опций диалогового окна Join Condition
| Опция | Тип создаваемой связи |
|
Inner join
(Внутреннее объединение) |
Создает объединение, в котором выбираются только те записи, которые содержат совпадающие значения в полях связи |
|
Left join
(Объединение слева) |
Создает объединение, в котором выбираются все записи из левой таблицы, а также записи из правой таблицы, значения поля связи которого совпадают со значениями поля связи левой таблицы |
|
Right join
(Объединение справа) |
Создает объединение, в котором выбираются все записи из правой таблицы, а также записи из левой таблицы, значения поля связи которого совпадают со значениями поля связи правой таблицы |
| Full join (Полное объединение) | Создает объединение, в котором выбираются все записи из правой и левой таблиц |
Замечание
По умолчанию Visual FoxPro при объединении таблиц использует опцию Inner join (Внутреннее объединение), при которой из таблиц выбираются только те записи, которые содержат совпадающие значения в полях связи.
Замечание
В отличие от постоянных отношений, определяемых между таблицами в базе данных, при объединении таблиц в конструкторе запросов вы можете использовать любые поля таблиц.
Установленные между таблицами условия объединения можно изменять. Для этого выполните следующие действия:
Совет
Для установления связи между таблицами в конструкторе запросов, как и в базе данных, можно использовать механизм "перенести-и-оставить". Выберите поле одной из таблиц, нажмите кнопку мыши и удерживая ее нажатой, перенесите поле на связываемое поле в другой таблице. После этого откройте диалоговое окно Join Condition (Условие объединения) и отредактируйте установленное условие объединения таблиц.
Рассмотрим пример выборки товаров, приобретенных клиентами. Для решения ЭТОЙ задачи нам потребуются таблицы Customer, Ordsalem, Ordsaled и Goods. Таблица customer содержит информацию о клиентах, таблица Ordsalem — сведения о заказе на товары, таблица Ordsaled — сведения о товарах, входящих в заказ (количество купленных товаров и цена товара), а таблица Goods — наименования всех товаров. При создании базы данных между этими таблицами уже были определены постоянные отношения.
Рис. 9.16. Вкладка Join окна запроса для выборки из четырех таблиц
Рис. 9.17. Результаты запроса для выборки из четырех таблиц
Группировка полей запроса позволяет получить информацию о подгруппах таблицы. Например, сгруппировав по коду заказа данные в таблице, содержащей сведения о заказах, можно получить сведения об итоговой сумме по каждому заказу.
Для группировки записей в запросе предназначена вкладка Group By (Группировка) (рис. 9.18), содержащая список Grouped fields (Поля группировки) с полями, по которым осуществляется группировка данных.
Рис. 9.18. Вкладка Group By окна конструктора запросов
Использование в запросе выражений и функций полей
В запрос можно включать статистические значения, вычисляемые по одному или нескольким полям исходной таблицы. Например, используя функцию COUNT (), вы можете подсчитать количество клиентов, проживающих в том или ином городе.
Кроме того, Visual FoxPro позволяет выполнять итоговые операции над вычисляемыми в запросе полями. Например, в запросе, выбирающем данные из таблиц ordsaiem и Ordsaied, вы можете вычислить итоговую стоимость продажи каждого товара за интересующий вас интервал времени.
Для вычисления итоговых значений в запросе вы можете использовать функции, описанные в табл. 9.4.
Таблица 9.4. Функции для вычисления итоговых значений в запросе
| Функция | Результат вычисления |
| COUNT ( ) | Количество строк в итоговой таблице |
| МАХ() | Наибольшее значение в столбце |
| MIN() | Наименьшее значение в столбце |
| AVG() | Среднее значение столбца численных данных |
| SUM() | Сумма численных данных столбца |
Для формирования в запросе выражения предназначено поле Functions and expressions (Функции и выражения) вкладки Fields (Поля) и расположенная справа от него кнопка открытия построителя выражения. Нажмите данную кнопку. Откроется диалоговое окно Expression Builder (Построитель выражения) (рис. 9.19). В поле ввода Expression (Выражение) сформируйте итоговое значение, используя поля таблиц запроса, расположенные в списке Fields (Поля), и функции области Functions (Функции), сгруппированные по типам данных и представленные в виде четырех списков.
Рис. 9.19. Диалоговое окно Expression Builder позволяет включить в запрос функцию или выражение
При нажатии кнопки Options (Опции) открывается диалоговое окно Expression Builder Options (Опции построителя выражения) (рис. 9.20). Используя параметры этого окна, можно настроить список наиболее часто используемых в запросе функций, отображаемых в области Functions (Функции) диалогового окна Expression Builder (Построитель выражения).
Рис. 9.20. Диалоговое окно Expression Builder Options
Чтобы настроить список функций, выполните следующие действия:
В нижней части диалогового окна Expression Builder Options (Опции построителя выражений) находится группа переключателей Field aliases (Псевдонимы поля), содержащая опции, описанные в табл. 9.5.
Таблица 9.5. Назначение группы переключателей Field aliases диалогового окна Expression Builder Options
| Переключатель | Назначение |
| Always add alias (Всегда добавлять псевдоним) | Указывает, что имя таблицы или представления данных всегда включается в имя поля |
| Add non-selected alias only (Добавлять только невыбранные псевдонимы) | Если открыто более одной таблицы или представления данных, Visual FoxPro включает имя таблицы или представления данных только в том случае, если они не содержатся в списке псевдонимов в окне Data Session (Сеанс данных) |
| Never add alias (He добавлять псевдоним) | Указывает, что имя таблицы или представления данных никогда не включается в имя поля |
Флажок Show system memory variables (Показывать системные переменные) указывает, будут ли отображаться в окне построителя выражения системные переменные.
При формировании выражения вы можете использовать поля, константы и функции, связанные арифметическими операторами, список которых приведен в табл. 9.6.
Таблица 9.6. Арифметические операторы, используемые при формировании выражений
| Оператор | Функция |
| + | Сложение |
| _ | Вычитание |
| * | Умножение |
| / | Деление |
Для изменения порядка вычислений в формируемом выражении, а также для группировки данных можно использовать круглые скобки.
Включение в запрос итоговых значений
В данном примере создадим запрос для таблиц Ordsalem и Ordsaled об итоговой сумме по каждому заказу.
SUM (Ordsaled. nQuant * Ordsaled.nUnitPri.ee)
Рис. 9.21. Результат выборки
Изменение наименований полей в запросе
Visual FoxPro по умолчанию присваивает полям результирующей таблицы запроса наименования, которые имеют поля исходной таблицы. Вычисляемым и итоговым полям присваиваются наименования в соответствии с соглашением, принятым в Visual FoxPro.
Используя ключевое слово AS, вы можете по своему усмотрению изменить наименования полей результирующей таблицы. При этом наименования полей изменятся только в результирующей таблице, а имена полей в исходной таблице останутся без изменения.
Для изменения наименования поля необходимо за именем поля или выражением, заданным для определения поля результирующей таблицы, поместить ключевое слово AS, а за ним указать новое имя поля.
В предыдущем примере наименование итогового поля было определено Visual FoxPro по умолчанию. Результат запроса будет более читабельным, если вы измените наименования полей. Для этого выполните следующие действия:
SUM(OrdSaled.nQuant * OrdSaled.nUnitPrice) AS Стоимость
Рис. 9.22. Изменение наименования полей
|
|
 |
