2. Отбор данных из базы с использованием sql-запросов

Ранее отмечалось, что для работы с данными, отобранными в соответствии с каким-либо условием, может быть использована команда SET FILTER TO <условие> - установить фильтр для открытой таблицы базы данных.

Однако большими возможностями обладает так называемый SQL-запрос - команда SELECT :, сформированная в соответствии с правилами языка запросов SQL (Structured Query Language).

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

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

В VFP и других системах фирмы Microsoft (Word, Excel) можно использовать Конструктор запросов, что упрощает и ускоряет написание запросов. Кроме того, в VFP есть Мастер для разработки запросов разного вида. Однако использование этих средств не позволяет реализовать все возможности языка запросов. Максимальные возможности - при написании запроса в текстовом виде в любом программном модуле в соответствии с синтаксисом команды SELECT (полный синтаксис будет описан далее).

Принцип формирования запросов наиболее легко освоить при использовании Мастера запросов.

Поставим задачу отобрать информацию по экзаменационным оценкам студентов по факультету № 1 за 1-й семестр 2005/2006 учебного года по предмету № 1 ("Математика" в справочной таблице) с сортировкой данных по группам, в группах - в порядке уменьшения оценки.

Для этого выбираем на стандартной панели или в меню команду New, далее выбираем Query и нажимаем кнопку Query Wizard. В появившемся списке из трех пунктов: Cross-Tab Wizard, Graph Wizard и Query Wizard выбираем последний вариант - стандартный запрос.

На первом шаге Мастера необходимо выбрать таблицы и поля, которые следует включить в запрос. Если предварительно база данных не была открыта, следует ее выбрать с помощью кнопки рядом с полем списка Databases and tables. Выбор полей может быть выполнен из нескольких таблиц базы (рис. 15.12.).

Рис. 15.12.  Мастер запросов - шаг 1

На шаге 2 следует добавить в список связей выражение SPISOK.NZ = OCENKI.NZ, что Мастер сам предложит вам сделать.

На шаге 2а вам нужно ответить на вопрос, какие записи при объединении таблиц следует включать в результаты (join conditions - условия объединения):

only matching rows- отбираются только записи при их одновременном присутствии в обеих связанных таблицах для заданных условий отбора и связей (условие объединения Inner Join);

all rows from table SPISOK - отбираются все записи таблицы SPISOK, соответствующие условиям отбора, и связанные записи другой таблицы при их наличии (Left Outer Join);

all rows from table OCENKI- отбираются все записи таблицы OCENKI, соответствующие условиям отбора, и связанные записи другой таблицы при их наличии (Right Outer Join);

all rows from both tables - отбираются все записи таблиц SPISOK и OCENKI, соответствующие условиям отбора, независимо от наличия связанных записей другой таблицы (Full Join).

Выбираем первый вариант для нашего запроса.

На шаге 3 могут быть заданы условия отбора данных (количество условий - не более двух, в Конструкторе запросов или в тексте запроса можно увеличить их количество). Зададим условия - SPISOK.N_FCLT=1 and OCENKI.SEMESTR=1 (рис. 15.13.). В Мастере нельзя задать больше двух условий, в Конструкторе или тексте SQL-программы количество условий не ограничено.

Рис. 15.13.  Мастер запросов - шаг 3

Шаг 4 - задание условий сортировки данных. Здесь можно выбрать последовательно 3 поля таблиц, по которым следует сортировать данные, например, n_grup, ball - упорядочить по группам, внутри групп - по баллу в порядке уменьшения (Descending).

На шаге 4а можно задать процент или количество рассматриваемых записей, здесь мы оставим без изменений условие all records - все записи.

На последнем шаге 5 выберем вариант Save query and modify it in Query Designer - Сохранить запрос и модифицировать его в Конструкторе запросов, зададим путь и имя для сохранения файла запроса.

В результате на диске будет создан текстовый файл запроса с расширением *.qpr (например, query_s1.qpr), который может быть открыт как текстовый файл или в Конструкторе запросов с отображением всех условий запроса.

Как видим на рис. 6.14., в верхней половине Конструктора запросов показаны таблицы, используемые для отбора данных и связи между ними, в нижней части - присутствуют страницы для выбора полей таблиц (Fields), условий объединения (Join), отбора данных (Filter), упорядочения (Order By), группировки (Group By) и разное (Miscellaneous). Щелчок правой кнопкой мыши в окне вызовет контекстное меню, в котором можно выбрать позицию Output settings и задать тип сохранения результата отбора данных - Browse, Cursor, Table, Screen. По умолчанию используется режим Screen - данные сохраняются в курсоре (временной таблице, доступной только для чтения, автоматически уничтожающейся при завершении работы системы) и показываются на экране как Browse.

Рис. 15.14.  Конструктор запросов

Модифицируем запрос для отбора данных только по экзаменационным оценкам за 1 семестр 2005/2006 учебного года. Для этого на странице Filter добавим новые условия: semestr = 1, val(Ocenki.ball)>1, val(Ocenki.ball)<6 и YEAR(Ocenki.data_b) = 2006 (см. рис. 15.15.).

Рис. 15.15.  Страница задания условий отбора данных в Конструкторе запросов

Текст запроса на языке SQL будет выглядеть следующим образом (в контекстном меню можно воспользоваться командой View SQL):

SELECT Spisok.n_fclt, Spisok.n_grup, Spisok.fio, Ocenki.semestr,;

Ocenki.n_predm, Ocenki.ball;

FROM ;

STUDENTS!SPISOK ;

INNER JOIN STUDENTS!OCENKI ;

ON Spisok.nz = Ocenki.nz;

WHERE Spisok.n_fclt = 1; {для всех условий задан одинаковый }

AND Ocenki.semestr = 1; {приоритет (Pri.) равный 0}

AND Ocenki.n_predm = 1;

AND val(Ocenki.ball) > 1;

AND val(Ocenki.ball) < 6; AND YEAR(Ocenki.data_b) = 2006;

ORDER BY Spisok.n_grup, Ocenki.ball DESC

Выполнив запрос (кнопка Run на стандартной панели), мы получим результат отбора данных (рис. 15.16.).

Рис. 15.16.  Результаты отбора данных по запросу

Более сложный вариант - запрос с группировкой данных и расчетом групповых итогов. Поставим задачу показать средний балл для студенческих групп по экзаменационным оценкам для факультета № 1 за 1-й семестр 2005/2006 учебного года по предмету № 1. Сделать это можно путем модификации в Конструкторе предыдущего запроса.

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

• значения столбцов группировки (входящих в предложение GROUP BY);

• расчетные значения для групп, такие, как SUM(°), AVG(°) и пр.

Не допускается отображение значений отдельных строк таблицы.

Оставим в списке полей только поле группировки - n_grup, и добавим новое поле - выражение для расчета средней оценки - AVG(VAL(ball). На странице Order By запишем одно условие - упорядочить по новому расчетному полю в порядке убывания. На странице Filter оставим все прежние условия отбора. Введем вывод результатов запроса в таблицу query_s2.dbf ¹⁾

Текст запроса будет выглядеть следующим образом:

SELECT Spisok.n_grup, AVG(VAL(Ocenki.ball));

FROM ;

STUDENTS!SPISOK ;

INNER JOIN STUDENTS!OCENKI ;

ON Spisok.nz = Ocenki.nz;

WHERE Spisok.n_fclt = 1;

AND Ocenki.semestr = 1;

AND Ocenki.n_predm = 1;

AND VAL(Ocenki.ball) > 1;

AND VAL(Ocenki.ball) < 6;

AND YEAR(Ocenki.data_b) = 2006;

GROUP BY Spisok.n_grup;

ORDER BY 2 DESC;

INTO TABLE query_s2.dbf

Результаты отбора данных по запросу показаны на рис. 15.17.

Рис. 15.17.  Отбор данных по запросу с группировкой

Результаты отбора данных могут быть показаны в виде диаграммы (объект Microsoft Graph). Для этого можно воспользоваться мастером построения диаграмм в VFP - в меню системы Tools - Wizards - All Wizards:, далее в окне полного списка Мастеров выбрать GraphWizard (см. табл. "Полный список Wizard-ов в VFP 9.0"). Результат построения столбчатой диаграммы показан на рис. 15.18. (после дополнительного редактирования объекта в системе VFP).

Рис. 15.18.  Диаграмма, построенная по результатам запроса

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

SELECT Fclt.name_f, Spisok.n_grup, Predmets.name_p,;

AVG(VAL(Ocenki.ball));

FROM ;

STUDENTS!SPISOK ;

INNER JOIN STUDENTS!OCENKI ;

ON Spisok.nz = Ocenki.nz ;

INNER JOIN students!fclt ;

ON Fclt.n_fclt = Spisok.n_fclt ;

INNER JOIN students!predmets ;

ON Predmets.n_predm = Ocenki.n_predm;

WHERE Ocenki.semestr = ( 1 );

AND VAL(Ocenki.ball) > ( 1 );

AND VAL(Ocenki.ball) < ( 6 );

AND YEAR(Ocenki.data_b) = ( 2006 );

GROUP BY Fclt.name_f, Spisok.n_grup, Predmets.name_p;

ORDER BY Fclt.name_f, Spisok.n_grup, Predmets.name_p, 4 DESC;

INTO TABLE query_s3.dbf

Результаты отбора данных по запросу показаны на рис. 15.19.

Рис. 15.19.  Отбор данных по запросу с группировкой

На основе таблицы query_s3.dbf можно построить перекрестный запрос(Cross-Tab Wizard) для отображения в последующем данных на одной трехмерной диаграмме с осями X (Row) - группа, Y (Column) - предмет, Z (Data) - средний балл. Страница 3 мастера с заданием этих параметров показана на рис. 15.20.

Рис. 15.20.  Страница 3 мастера Cross-Tab Wizard

Текст запросав системе VFP будет выглядеть следующим образом:

SELECT Query_s3.n_grup, Query_s3.name_p, AVG(Query_s3.avg_exp_4);

FROM ;

QUERY_S3.DBF;

GROUP BY Query_s3.n_grup, Query_s3.name_p;

ORDER BY Query_s3.n_grup, Query_s3.name_p;

INTO TABLE q_cross.dbf

DO (_GENXTAB) WITH 'Query'

BROWSE NOMODIFY

Результат выполнения перекрестного запроса показан на рис. 15.21.

Рис. 15.21.  Данные перекрестного запроса

Для полученной таблицы можно построить трехмерную диаграмму с помощью Мастера GraphWizard, показанную на рис. 15.22. (после дополнительного редактирования ее в системе VFP).

Рис. 6.22.  Диаграмма, построенная по данным перекрестного запроса