Опубликован: 19.05.2006 | Доступ: свободный | Студентов: 8143 / 1485 | Оценка: 4.04 / 3.81 | Длительность: 09:46:00
Лекция 6:

СУБД Microsoft Visual FoxPro. Отчеты, запросы, проекты и приложения

< Лекция 5 || Лекция 6: 1234 || Лекция 7 >

Отбор данных из базы с использованием 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. Выбор полей может быть выполнен из нескольких таблиц базы (рис. 6.12.).

Мастер запросов - шаг 1

Рис. 6.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 (рис. 6.13.). В Мастере нельзя задать больше двух условий, в Конструкторе или тексте SQL -программы количество условий не ограничено.

Мастер запросов - шаг 3

Рис. 6.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.

Конструктор запросов

увеличить изображение
Рис. 6.14. Конструктор запросов

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

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

увеличить изображение
Рис. 6.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 на стандартной панели), мы получим результат отбора данных (рис. 6.16.).

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

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

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

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

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

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

Оставим в списке полей только поле группировки - n_grup, и добавим новое поле - выражение для расчета средней оценки - AVG(VAL(ball). На странице Order By запишем одно условие - упорядочить по новому расчетному полю в порядке убывания. На странице Filter оставим все прежние условия отбора. Введем вывод результатов запроса в таблицу query_s2.dbf1Параметры вывода итогов запроса могут быть следующие:INTO Destination В качестве Destination можно использовать одно из следующих предложений: ARRAY ArrayName - в массиве переменных памяти; CURSOR CursorName - в курсор; DBF TableName | TABLE TableName - в таблицу. Вместо INTO можно использовать. TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN

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

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

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

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

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

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

"Полный список Wizard-ов в VFP 9.0"
Application Builder
Application Wizard
Cross-Tab Wizard
Database Wizard
Documenting Wizard
Form Wizard
Graph Wizard
Import Wizard
Label Wizard
Local View Wizard
Mail Merge Wizard
Microsoft SQL Server Upsizing Wizard
One-to-Many Form Wizard
One-to-Many Report Wizard
One-to-Many Report Wizard
Query Wizard
Remote View Wizard
Report Wizard
Table Wizard
Web Publishing Wizard
Web Services Publisher
Диаграмма, построенная по результатам запроса

Рис. 6.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

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

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

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

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

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

Рис. 6.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

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

Данные перекрестного запроса

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

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

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

Рис. 6.22. Диаграмма, построенная по данным перекрестного запроса
< Лекция 5 || Лекция 6: 1234 || Лекция 7 >
Данил Корляков
Данил Корляков

Прошел весь курс всего за день, щёлкал ваши тесты, как орешки. Хочу вторую часть! laugh

Олеся Талдыкина
Олеся Талдыкина