12. Извлечение и отображение данных

12.1. Перечень команд и системных переменных

Приведен в табл. 12.1

Таблица 12.1

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

Команда / системная переменная	Описание
Команды
CREATE QUERY | LABEL | REPORT	Открывает проектировщик запроса | отчета | этикетки для создания нового запроса | отчета | этикетки
EXTERNAL QUERY | REPORT | LABEL	Предупреждает диспетчер проекта о внешней ссылке (внешнем запросе | отчете | этикетке)
LABEL	Отображает и/или печатает этикетки, беря данные из таблицы и используя макет
MODIFY QUERY | LABEL | REPORT	Открывает проектировщик запроса | отчета | этикетки для создания нового запроса | отчета | этикетки или для редактирования существующего
REPORT FORM	Отображает и/или печатает отчет, беря данные из таблицы и используя макет
SET REPORTBEHAVIOR	задает, как VFP выполняет команды REPORT FORM и LABEL FORM
SYS(2040)	Функция. Определяет, печатается ли отчет или находится в режиме просмотра
TYPE	Печатает или копирует файл
Системные переменные
_ASCIICOLS | _ASCIIROWS	Число колонок | строк в тексте, создаваемом командой REPORT ... TO FILE ASCII
_GENGRAPH	Хранит имя приложения-построителя диаграммы
_GENXTAB	Хранит имя программы-генератора перекрестной таблицы
_PAGETOTAL	Общее число страниц в отчете
_PAGENO	Номер текущей страницы отчета
_PBPAGE	Номер первой печатаемой страницы отчета
_PEPAGE	Номер последней печатаемой страницы отчета
_REPORTOUTPUT	Содержит имя приложения, управляющего объектно-поддерживаемым выводом отчета

12.2. Проектировщик запроса

Назначение проектировщика – помочь пользователю в создании текста SQL-запроса к таблицам и/или видам. Запрос, созданный в проектировщике, сохраняется в виде QPR-файла.

Запросы, хранимые в виде QPR-файлов, могут быть добавлены в проект – в ветвь Queries вкладки Data.

Проектировщик открывается, например, после нажатия на Ctrl+N и последующего выбора в диалоге New переключателя Query или в результате выполнения одной их двух приводимых ниже команд.

Команда

CREATE QUERY [FileName | ?] [NOWAIT]

открывает проектировщик запроса для создания нового запроса, задавая имя файла FileName для результата.

Команда

MODIFY QUERY [FileName | ?] [[WINDOW WindowName1] [IN SCREEN] [NOWAIT] [SAVE] [AS nCodePage]

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

Опции и параметры см. в описании команды MODIFY FILE и в табл. 1.6.

Созданный запрос запускается на исполнение командой DO; имя файла указывается с расширением, например:

do SomeQuery.qpr

Используемые при работе с проектировщиком запроса понятия см. в описании команды SELECT – SQL, приведенной в разд. 15.19.

Пример. Выбрать из базы данных HomeLibrary авторов и их книги. Для выбираемых записей должно выполняться условие Authors.InputDate > {^2004-11-25}.

Зная организацию базы данных HomeLibrary, легко понять, что данные должны выбираться из таблиц Authors, Books и BooksAuthors. При этом таблица Authors объединяется с таблицей BooksAuthors по полю AuthorId, а таблица BooksAuthors – с таблицей Books по полю BookId. Условия объединения: Authors.AuthorId = BooksAuthors.AuthorId и BooksAuthors.BookId = Books.BookId. Выбираются только те записи, которые отвечают условиям объединения, то есть употребляется INNER JOIN.

Откроем проект HomeLibrary, имеющий одноименную базу данных. Выполним команду

create query d:\AuthorsAndBooks

После ввода команды откроется проектировщик запроса и диалог Add Table or View. Выберем в нем базу данных HomeLibrary, а затем таблицу Authors и добавим ее в запрос, далее выберем таблицу BooksAuthors (рис. 12.1).

Рис. 12.1. Добавляется таблица BooksAuthors

Поскольку таблицы Authors и BooksAuthors имеют одноименные поля AuthorId, то VFP автоматически оформит межтабличное объединение как Inner join. Если совпадающих полей не будет обнаружено, то появится диалог Join Condition (рис. 12.2), предоставляющий возможность выбрать поля и тип объединения.

Рис. 12.2. Диалог Join Condition

Выберем затем таблицу Books и оформим условия ее объединения с таблицей BooksAuthors.

Из списка доступных полей выберем поля, отображаемые в результате запроса (рис. 12.3).

Рис. 12.3. Проектировщик запроса после выбора таблиц, условий объединения и полей

Открыв вкладку Join проектировщика, обнаружим описание условий объединения таблиц, которые можно отредактировать.

На вкладке Filter зададим фильтр (рис. 12.4), а на вкладке Order By – критерии сортировки (рис. 12.5).

Рис. 12.4. Задание фильтра

Рис. 12.5. Критерии сортировки

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

Используя кнопку панели инструментов Query Designer, просмотрим текст запроса:

SELECT Authors.Author, Authors.Inputdate, Books.Book, Books.PDate, Books.Price;

FROM ;

HomeLibrary!Authors ;

INNER JOIN HomeLibrary!BooksAuthors ;

ON Authors.AuthorId = Booksauthors.AuthorId ;

INNER JOIN HomeLibrary!Books ;

ON Books.bookid = BooksAuthors.BookId ;

WHERE Authors.Inputdate > {^2004-11-25} ;

ORDER BY Authors.Author, Books.Book

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

Нажав на Ctrl+Q, исполним запрос и просмотрим его результат (рис. 12.6).

Рис. 12.6. Авторы и их книги

Результат направлен в Browse-окно. Эта установка может быть изменена в диалоге Query Destination (рис. 12.7).

Рис. 12.7. Выбор приемника результата запроса

Пример 2. Сформировать по данным базы данных HomeLibrary таблицу со столбцами Authors и NumberOfBooks, содержащими соответственно фамилии авторов и число имеющихся в библиотеке книг каждого автора.

Для получения результата достаточно обратиться к 2-м таблицам: Authors и BooksAuthors. По-прежнему таблица Authors объединяется с таблицей BooksAuthors по полю AuthorId по условию INNER JOIN. Фильтр отсутствует.

Вторая таблица позволит узнать, сколько книг каждого автора имеется в библиотеке. Для этого одновременно с группировкой по полю Authors.Author придется употребить агрегатную функцию COUNT( ).

Воспользуемся существующим запросом

modify query d:\AuthorsAndBooks

и сохраним его под именем d:\AuthorsNumberOfBooks.

Удалим из верхней части проектировщика запроса таблицу Books, на вкладке Fields оставим в списке Selected Fields только одно поле – Authors.Author, а на вкладке Filter устраним, оперируя кнопкой Remove, существующий фильтр (см. рис. 12.4). Вкладку Order By оставим без изменений (второй критерий сортировки будет удален проектировщиком одновременно с таблицей Books).

Перейдем на вкладку Group By и добавим в список Grouped Fields поле Authors.Author (рис. 12.8).

Рис. 12.8. Данные группируются по полю Authors.Author

Теперь, если выполнить запрос к 2-м таблицам, получим только список авторов. Чтобы получить в результате второе поле, вернемся на вкладку Fields (см. рис. 12.3) проектировщика и в поле Functions and Expressions введем выражение

Count(BooksAuthors.BookId)

и нажмем на кнопку Add (рис. 12.9).

Рис. 12.9. Добавлено поле с агрегатной функцией COUNT( )

Если исполнить запрос, то второе поле результата получит имя Cnt_bookid (рис. 12.10).

Рис. 12.10. Результат запроса с количеством книг авторов

Текст запроса, сформированный проектировщиком:

SELECT Authors.Author, COUNT(BooksAuthors.BookId) ;

FROM ;

HomeLibrary!Authors ;

INNER JOIN HomeLibrary!BooksAuthors ;

ON Authors.AuthorId = BooksAuthors.AuthorId ;

GROUP BY Authors.Author ;

ORDER BY Authors.Author

Замечание. Большинство вопросов, связанных с употреблением проектировщика запроса, будет снято после более тесного знакомства с командой SELECT – SQL.