Выбор данных в Microsoft Visual FoxPro

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [, ...]
FROM [FORCE] Table_List_Item [, ...]
[[JoinType] JOIN DatabaseName!]Table [[AS] Local_Alias]
[ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...]
[WITH (BUFFERING = lExpr)]
[WHERE JoinCondition | FilterCondition
[AND | OR JoinCondition | FilterCondition] ...]
[GROUP BY Column_List_Item [, ...]]
[HAVING FilterCondition [AND | OR ...]]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, ...]]
[INTO StorageDestination | TO DisplayDestination]
[PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT]

выбирает данные из одной или нескольких таблиц.

Основные опции команды показывает следующий макет команды:

SELECT Select_List
FROM Table_List
[WITH (BUFFERING = lExpr)]
[WHERE Conditions]
[GROUP BY Column_List]
[UNION Clause]
[HAVING Conditions]
[ORDER BY Column_List]
[INTO Clause | TO Clause ]
[Additional_Display_Options]

Если SET TALK установлен в ON, то VFP при выполнении SELECT – SQL отобразит время, потраченное на выполнение запроса, и число записей в результате (его содержит системная переменная _TALLY).

Опции и параметры:

ALL – (по умолчанию) в результате отображаются все записи.

DISTINCT – исключает из результата повторяющиеся записи. Опция используется в команде только 1 раз. Опция не поддерживается в запросе, содержащем memo-, general- и blob-поля. Их, однако, можно заменить выражениями, например употребляя memo-поля в качестве параметров функций PADR( ) или ALLTRIM( ).

TOP nExpr [PERCENT] – в результат включаются первые nExpr записей (из числа отобранных). Если опция PERCENT опущена, то nExpr – это число записей; диапазон изменения nExpr от 1 до 32767. Если опция PERCENT задана, то nExpr – это процент включаемых записей; диапазон изменения nExpr от 0.01 до 99.99.

Если опция TOP задана, то необходимо задать и опцию ORDER BY. Эта опция определяет, по каким столбцам опция TOP определяет число строк в результате.

При задании TOP nExpr, если SET ENGINEBEHAVIOR в результат включаются все записи с одинаковыми значениями в столбцах, указанных в ORDER BY. Поэтому число записей в результате может превышать nExpr.

Select_List_Item – список полей, констант и выражений, отображаемых в результате запроса. Если Select_List_Item задан в виде символа *, то будут выбраны все поля источников данных. Число элементов в списке не должно быть более 255. Список Select_List_Item может содержать следующие элементы:

- константу (она будет присутствовать в каждой строке результата);

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

- [Alias.] Select_Item, где Alias задает псевдоном таблицы, а Select_Item имя поля таблицы, указанной в опции FROM. Псевдоним Alias необходим, когда несколько полей таблиц-источников данных имеют одно имя. Допускается пробел между [Alias.] и Select_Item;

- (Subquery) – подзапрос – вложенная команда SELECT – SQL; должна быть заключена в круглые скобки. Число подзапросов и уровень их вложения произвольный. Можно употреблять подзапрос, коррелированный с ближайшим родительским запросом. Такой подзапрос использует поле родительского запроса; коррелированный подзапрос выполняется для каждой кандидатной строки родительского запроса. Некоррелированный подзапрос может содержать опцию TOP nExpr, несколько условий объединения и опций GROUP BY. Если в подзапросе задана опция TOP nExpr, то в него нужно включить и опцию ORDER BY. Подзапрос, не выбирающий записей, вернет NULL.

Вложенный подзапрос в общем случае имеет следующий синтаксис:

SELECT … WHERE … (SELECT … WHERE … (SELECT …) …) …

В выражениях с именами полей может присутствовать агрегатная функция. При задании в Select_List_Item агрегатной функции используется следующий синтаксис:

AggregateFunction([Alias.]Select_Item) [[AS] Column_Name]

Перечень агрегатных функций приведен в табл. 15.16.

Таблица 15.16

Агрегатные функции

Нельзя использовать вложенные вызовы агрегатных функций.

Каждый элемент Select_List_Item дает один столбец в результате.

После каждого элемента Select_List_Item можно указать опцию [AS] Column_Name, в которой Column_Name – это имя заголовка столбца в результате запроса. Column_Name может быть символьным выражением, но не может содержать запрещенные символы, например пробелы.

Пример. Выражение в тексте запроса, использующее пользовательскую функцию.

close databases

open database 'd:\HomeLibrary\HomeLibrary'

&&

&& Выполняем запрос. Результат запроса см. на рис. 15.21

select top 50 percent AuAndInDate( ) as AuDate from Authors order by Author

function AuAndInDate( )

return Dtoc(InputDate) + " " + Author

endFunc

Рис. 15.21. Результат запроса примера

FROM – задает таблицы, из которых извлекаются данные, условия объединения и фильтрации данных. Число объединяемых таблиц произвольно.

FORCE – указывает, что таблицы соединяются в порядке их появления в списке. Если опция опущена, то VFP пытается оптимизировать запрос. Однако запрос может быть выполнен быстрее, если опция FORCE имеется, поскольку не будет затрат времени на его оптимизацию.

Table_List_Item [, ...] – список таблиц-источников данных; может быть задан с использованием следующего синтаксиса:

• [DatabaseName!]Table [[AS] Local_Alias], где:

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

Table – имя таблицы-источника данных. Если при вызове SELECT – SQL таблиц закрыта, то откроется диалог Open, в котором следует указать таблицу. Таблица, открытая для выполнения запроса, остается открытой и после получения результата.

Local_Alias – локальный, используемый в запросе псевдоним таблицы Table. Если локальный псевдоним Local_Alias задан, то он должен быть использован вместо имени таблицы во всем тексте запроса. Также может быть задана опция JOIN, позволяющая задать несколько таблиц.

(Subquery) AS Subquery_Alias – задает подзапрос. В опции FROM каждый подзапрос должен иметь псевдоним. В этой опции подзапросы не имеют ограничений, которые накладываются на подзапросы в условиях отбора данных. Все подзапросы опции FROM выполняются до оценки основного оператора SELECT.

JoinType – вид объединения таблиц-источников-данных; принимает приведенные в табл. 15.17 значения.

Таблица 15.17

Значения параметра JoinType

Опция OUTER включается для повышения наглядности текста запроса.

ON JoinCondition | FilterCondition – условие объединения двух таблиц и/или фильтрации (отбора) данных. Опция ON может содержать подзапросы, в том числе вложенные.

Условие объединения JoinCondition является логическим выражением и может включать:

- сравнение двух полей FieldName1 и FieldName2 различных таблиц, например:

FieldName1 сравнение FieldName2

- сравнение выражений с полями различных таблиц, например:

Table1.Field1 + Table2.Field1 сравнение Table3.Field1

- пользовательскую функцию, возвращающую логическую величину, например:

MyUDF(Table1.Field1, Table2.Field1)

Операция сравнение может быть любой операцией отношения (см. табл. 3.22) или SQL-операцией LIKE.

Результат операции "=" с символьными операндами зависит от установки SET ANSI. Когда SET ANSI установлен в OFF, то в сравнении участвуют строки, ограниченные длиной короткого операнда (берется соответствующая подстрока длинного операнда). Когда SET ANSI установлен в ON, то VFP следует ANSI-стандарту сравнения строк (см. описание команды SET ANSI).

Условие отбора FilterCondition также является логическим выражением, задающим включение в результат запроса выбираемых данных. В логическом выражении FilterCondition могут быть использованы местные псевдонимы и имена полей или заголовки столбцов результата запроса. Для оценки выражения на пустое значение применяется функция EMPTY( ). При задании FilterCondition употребляется выше описанный синтаксис для JoinCondition, а также выражения следующих видов:

FieldName сравнение cExpression

и

FieldName [NOT] LIKE "cExpression"
    | IS [NOT] NULL
    | [NOT] BETWEEN Start_Range AND End_Range
    | [NOT] IN (Value_Set)

Описание использованных в выражении опций и параметров приведено в табл. 15.18.

Таблица 15.18

Опции и параметры выражения отбора

Несколько условий объединений (отбора) соединяются операциями AND и OR.

Замечания:

1. Описание условия отбора с подзапросом см. ниже.

2. SELECT – SQL игнорирует установки отбора, заданные командой SET FILTER.

Пример 1. Выбираются из таблиц Books и BooksContent названия книг (столбец Book) и названия произведений (столбец Name).

Посредством INNER JOIN выбираются книги, состав которых перечислен в таблице BooksContent.

Посредством LEFT JOIN выбираются все книги, как отраженные в таблице BooksContent, так и не отраженные. В столбце Name результата для книг, состав которых не отражен в таблице BooksContent, будет проставлен NULL.

Посредством RIGHT JOIN выбираются все произведения. Для произведений, не имеющих соответствующих записей в таблице Books, в поле Book результата будет проставлен NULL. (Такая ситуация может возникнуть, если по ошибке или другой причине из таблицы Books было удалено название книги, а в таблице BooksContent названия соответствующих произведений сохранены.)

Посредством FULL JOIN выбираются все книги и все произведения. В столбце Name результата для книг, состав которых не отражен в таблице BooksContent, будет проставлен NULL. Для произведений, не имеющих соответствующих записей в таблице Books, в поле Book результата будет проставлен NULL.

Результаты запросов с INNER и LEFT JOIN совпадут, если для всех книг таблицы Books перечислены произведения в таблице BooksContent.

Результаты запросов с INNER и RIGHT JOIN совпадут, если для каждого произведения таблицы BooksContent в таблице Books имеется книга.

close databases

open database 'd:\HomeLibrary\HomeLibrary'

&&

&& Запрос c INNER JOIN. Результат запроса см. на рис. 15.22

select Book, Name ;

from Books ;

inner join BooksContent on Books.BookId = BooksContent.BookId ;

order by Book into cursor B1

&&

&& Запрос c LEFT JOIN. Результат запроса см. на рис. 15.23

select Book, Name ;

from Books ;

left join BooksContent on Books.BookId = BooksContent.BookId ;

order by Book into cursor B2

&&

&& Запрос c RIGHT JOIN. Результат запроса см. на рис. 15.24

select Book, Name ;

from Books ;

right join BooksContent on Books.BookId = BooksContent.BookId ;

order by Book into cursor B3

&&

&& Запрос c FULL JOIN. Результат запроса см. на рис. 15.25

select Book, Name ;

from Books ;

full join BooksContent on Books.BookId = BooksContent.BookId ;

order by Book into cursor B4

Рис. 15.22. Фрагмент результата запроса с INNER JOIN

Рис. 15.23. Фрагмент результата запроса с LEFT JOIN

Рис. 15.24. Фрагмент результата запроса с RIGHT JOIN

Рис. 15.25. Фрагмент результата запроса с FULL JOIN

Замечание. Параметр JoinCondition может содержать выражения с именами соответствующих полей объединяемых таблиц, например:

on Str(Books.BookId) = Str(BooksContent.BookId)

Пример 2. Условие объединения таблиц ON JoinCondition содержит также и условие отбора.

close databases

open database 'd:\HomeLibrary\HomeLibrary'

&&

&& Запрос c INNER JOIN, включающим условие отбора

&& Результат запроса см. на рис. 15.26

select Book, Name ;

from Books ;

inner join BooksContent ;

on Books.BookId = BooksContent.BookId ;

and BooksContent.Name < 'И' ;

order by Book

Рис. 15.26. Используется условие объединения Books.BookId = BooksContent.BookId
и условие отбора BooksContent.Name < 'И'

Пример 3. Выбор из трех таблиц; дважды используется опция INNER JOIN. Также задается сортировка результата запроса (опция ORDER BY) по 2-м полям – Book и Name.

close databases

open database 'd:\HomeLibrary\HomeLibrary'

&&

&& Запрос c двумя опциями INNER JOIN и с сортировкой по 2-м полям
&& Результат запроса см. на рис. 15.27

select Book, Name, WorkType ;

from Books ;

inner join BooksContent on Books.BookId = BooksContent.BookId ;

inner join WorkType on WorkType.TypeId = BooksContent.TypeId ;

order by Book, Name

Рис. 15.27. INNER JOIN-выбор из 3-х таблиц

[WITH (BUFFERING = lExpr)] – указывает, если lExpr = .T., что запрос основывается на буферизованных данных (если таковые имеются), или, если lExpr = .F. (по умолчанию), что данные выбираются с диска (см. также описание команды SET SQLBUFFERING).

[WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...] – задает условия объединения и/или отбора записей таблиц-источников данных. Детали см. в описании опции FROM.

Замечание. Функция EVALUATE( ) в выражении опции WHERE может возвращать неверные результаты.

Пример 1. Вместо INNER JOIN … ON используется опция WHERE JoinCondition.

close databases

open database 'd:\HomeLibrary\HomeLibrary'

&&

&& Запрос c WHERE JoinCondition. Результат запроса см. на рис. 15.27

select Book, Name, WorkType ;

from Books, BooksContent, WorkType ;

where Books.BookId = BooksContent.BookId ;

and WorkType.TypeId = BooksContent.TypeId ;

order by Book, Name

Пример 2. Условие объединения задается опцией INNER JOIN … ON, а условие отбора – WHERE FilterCondition.

&& Запрос c WHERE FilterCondition. Результат запроса см. на рис. 15.27

select Book, Name ;

from Books ;

inner join BooksContent on Books.BookId = BooksContent.BookId ;

where BooksContent.Name < 'И' ;

order by Book

Пример 3. Выбираются авторы, информация о которых введена в таблицу Authors с 25.11.04 по 30.11.04.

&& Запрос с FieldName BETWEEN Start_Range AND End_Range

close databases

open database 'd:\HomeLibrary\HomeLibrary'

&& Результат см. на рис. 15.28

select Author, InputDate from Authors ;

where InputDate Between {^2004-11-25} and {^2004-11-30} ;

order by Author

Рис. 15.28. Результат запроса с отбором
InputDate BETWEEN {^2004-11-25} AND {^2004-11-30}

Пример 4. Выбираются авторы, фамилия которых начинается на букву Л.

&& Запрос с FieldName LIKE cExpression с маской %

close databases

open database 'd:\HomeLibrary\HomeLibrary'

select Author  from Authors where Author like "Л%"

Пример 5. Выбираются книги, содержащие романы и/или рассказы.

&& Запрос с опцией DISTINCT, с FieldName LIKE cExpression с маской _

close databases

open database 'd:\HomeLibrary\HomeLibrary'

select distinct Book from Books as B ;

inner join BooksContent as C on B.BookId = C.BookId ;

inner join WorkType as W on C.TypeId = W.TypeId ;

where Left(W.WorkType, 2) like "Р_"

Замечание. Использованный в примере отбор эквивалентен следующему отбору:

where W.WorkType like "Р%"

Пример 6. Выбираются авторы, информация о которых введена в заданные даты.

&& Запрос с FieldName IN Value_Set

close databases

open database 'd:\HomeLibrary\HomeLibrary'

select Author, InputDate from Authors ;

where InputDate in ({^2004-11-19}, {^2004-11-22}, {^2004-11-26}) ;

order by Author

GROUP BY Column_List_Item – список, задающий группировку строк результата запроса по одному или нескольким столбцам. Элементы списка разделяются запятыми и могут быть:

- именем поля таблицы из опции FROM или подзапроса;

- полем результата запроса;

- числовым выражением, означающим номер столбца результата запроса. Первый столбец имеет номер 1.

Список опции должен содержать каждое поля из списка SELECT-полей и HAVING-полей, за исключением полей, употребленных в качестве аргументов агрегатных функций (см. также описание команды SET ENGINEBEHAVIOR).

Нельзя выполнять группировку по memo-, general- и blob-полям.

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

&& Запрос c опцией GROUP BY Book и агрегатной функцией COUNT( )

&& Результат запроса см. на рис. 15.29

select Book, Count(Name) as Amount ;

from Books ;

inner join BooksContent on Books.BookId = BooksContent.BookId ;

group by Book ;

order by Book

Рис. 15.29. Результат запроса с функцией COUNT( ) и опцией GROUP BY Book

Пример 2. Группировка в подзапросе.

close databases all

create cursor MyCursor1 (Field1 I, Field2 I, Field3 I)

insert into MyCursor1 values(1, 2, 3)

create cursor MyCursor2 (Field1 I, Field2 I, Field3 I)

insert into MyCursor2 values(1, 2, 3)

&&

select * from MyCursor1 T1 where Field1;

in (select Max(Field1) from MyCursor2 T2 ;

where T2.Field2 = T1.Field2 group by Field3)

HAVING FilterCondition [AND | OR ...] – задает условия, которым должна удовлетворять группа для включения в результат. Логическое выражение FilterCondition строится по тем же правилам, что и FilterCondition опций FROM и WHERE. Выражение FilterCondition, однако, не может содержать подзапрос.

Если опция HAVING используется в запросе без опции GROUP BY, то она интерпретируется как опция WHERE. Если в такой ситуации HAVING не содержит агрегатной функции, то с целью повышения быстродействия вместо HAVING лучше употребить WHERE.

Опция HAVING должна появляться перед опцией INTO. В противном случае возникнет ошибка.

Пример 1. Выбираются книги, в которых число произведений больше 4-х.

&& Запрос c опцией GROUP BY Book, агрегатной функцией COUNT( )

&& и опцией HAVING Amount > 4. В результат запроса попадет

&& только первая строка таблицы, приведенной на рис. 15.29

select Book, Count(Name) as Amount ;

from Books ;

inner join BooksContent on Books.BookId = BooksContent.BookId ;

group by Book ;

having Amount > 4 ;

order by Book

Пример 2. Вместо WHERE FilterCondition употребляется HAVING FilterCondition. Опция GROUP BY не используется.

&& Запрос c HAVING FilterCondition. Результат запроса см. на рис. 15.26

select Book, Name ;

from Books ;

inner join BooksContent on Books.BookId = BooksContent.BookId ;

having BooksContent.Name < 'И' ;

order by Book

[UNION [ALL] SELECTCommand] – объединяет результат запроса одного SQL SELECT-оператора с результатом запроса другого SQL SELECT-оператора. При объединении по умолчанию исключаются повторения. В объединяемых результатах запросов должно быть одинаковое число столбцов. Тогда первый столбец одного результата объединяется с первым столбцом другого, второй – со вторым и т. д. Имя результирующего столбца совпадает с именем столбца первого результата. По умолчанию данные сортируются в возрастающем порядке по первому столбцу результата.

Если типы столбцов не совпадают, то выполняется неявное преобразование типа данных одного столбца в тип данных другого. Причем тип с меньшим приоритетом преобразовывается в тип с большим. Правила преобразования обсуждаются ниже.

Когда один из столбцов имеет тип Memo или General, объединение столбцов разных типов не допускается.

Опция ALL разрешает повторяющиеся строки в результате.

При работе с UNION должны соблюдаться следующие правила:

- нельзя использовать UNION для комбинирования подзапросов;

- в объединяемых результатах запросов должно быть одинаковое число столбцов;

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

В случае нескольких UNION используются круглые скобки (см. пример 1).

Пример 1. В одном столбце выводятся имена всех авторов и названия книг и их произведений.

&& Запрос c двумя опциями UNION. Результат запроса см. на рис. 15.30

select "Автор: " + Author as AuthorsBooksWorks ;

from Authors;

union (select "Книга: " + Book from Books) ;

union (select "Произведение: " + Name from BooksContent) ;

into cursor ABW readwrite

Рис. 15.30. Результат запроса в двумя опциями UNION

Пример 2. В одном столбце выводятся названия книг и их произведений. Названия произведений располагаются после названия книги.

&& Запрос опциями UNION и ORDER BY. Результат запроса см. на рис. 15.31

select Transform(BookId) + ". (b) " + Book ;

from Books ;

union select Transform(BookId) + ". (w) " + Name from BooksContent ;

order by 1 ;

into cursor ABW readwrite

Рис. 15.31. Книги (b) и имеющиеся в них произведения (w);
поскольку опция AS опущена столбец результата имеет имя Exp_1

ORDER BY Order_Item [ASC | DESC] – сортирует результат запроса по одному или нескольким столбцам. Каждый параметр Order_Item должен соответствовать столбцу результата запроса и может быть либо полем таблицы, выступающем в качестве значения параметра Select_Item, либо числовым выражением, возвращающим номер столбца результата запроса; сортировка будет выполняться по данным этого столбца.

ASC – (по умолчанию) обеспечивает возрастающую сортировку.

DESC – задает убывающую сортировку.

Пример:

&& Запрос с опцией ORDER BY и тремя параметрами Order_Item

select Authors.Author, Books.Book, Bookscontent.name, Worktype.WorkType;

from HomeLibrary!Authors ;

inner join HomeLibrary!BooksAuthors ;

on Authors.AuthorId = Booksauthors.AuthorId ;

inner join HomeLibrary!Books ;

on Books.BookId = BooksAuthors.Bookid ;

inner join HomeLibrary!BooksContent ;

on Books.BookId = BooksContent.Bookid ;

inner join HomeLibrary!WorkType ;

on WorkType.TypeId = BooksContent.TypeId;

order by Authors.Author, Books.Book, BooksContent.Name

INTO StorageDestination – указывает объект, принимающий результат запроса. В качестве StorageDestination могут быть заданы следующие опции:

- ARRAY ArrayName – задает имя ArrayName массива, хранящего результат запроса; массив не создается, если результат запроса не содержит записей;

- CURSOR CursorName [NOFILTER | READWRITE] – задает курсор CursorName, хранящий результат запроса. Опция NOFILTER, если задана, позволяет использовать курсор как источник данных в последующих запросах. Если опция READWRITE опущена, то курсор доступен только для чтения, в противном случае – и для чтения и для записи. Свойство AutoInc, если его имеет таблица-источник данных, READWRITE-курсором не наследуется;

- DBF | TABLE TableName [DATABASE DatabaseName [NAME LongTableName]] – задает таблицу TableName, принимающую результат запроса. Если задана опция DATABASE, то таблица будет добавлена в базу данных DatabaseName. Если при этом задана опция NAME, то таблица получит длинное имя LongTableName.

Если опция INTO опущена и не задана опция TO FILE, то результат запроса будет выведен в Browse-окно.

Если Destination – это CURSOR и параметр CursorName совпадает с именем открытой таблицы, то генерируется ошибка. Создаваемый на диске временный файл (курсор) будет удален после закрытия. Временный файл размещается в директории, заданной опцией SORTWORK path файла конфигурации.

Включение NOFILTER может уменьшить быстродействие, поскольку на диске создается временная таблица, которая также удаляется после закрытия курсора.

Если Destination – это DBF | TABLE и параметр TableName совпадает с именем существующей таблицы и если SET SAFETY установлен в OFF, то таблица перезаписывается без предупреждения. Созданная таблица TableName остается открытой и после завершения запроса. Если параметр TableName не содержит расширения, то будет использовано расширение DBF.

[TO DisplayDestination] – направляет результат запроса в текстовый файл, на принтер или окно VFP (главное или активное). В качестве DisplayDestination можно указать:

FileName [ADDITIVE] – вывод направляется в файл; ADDITIVE обеспечит добавление новых данных к уже существующим;

PRINTER [PROMPT] – вывод направляется на принтер; PROMPT обеспечит появления диалога настройки параметров принтера;

SCREEN – вывод направляется в окно VFP.

Если опции INTO и TO включены одновременно, то опция TO игнорируется.

Пример:

select Book, Name ;

from Books ;

inner join BooksContent on Books.BookId = BooksContent.BookId ;

order by Book ;

to file d:\a.txt noConsole

PREFERENCE PreferenceName – сохраняет атрибуты и опции Browse-окна с результатами запроса в файле ресурсов (по умолчанию – это файл FoxUser.dbf). Первое выполнение SELECT с опцией PREFERENCE PreferenceName создает запись в файле ресурсов, поиск которой выполняется по имени PreferenceName. Последующее выполнение SELECT с тем же именем PreferenceName восстанавливает Browse-окно с ранее сохраненными атрибутами. При закрытии Browse-окна его настройки обновляются, если только оно не закрыто посредством Ctrl+Q.

NOCONSOLE – предотвращает вывод на экран результата запроса, посланного в файл или на принтер.

PLAIN – предотвращает вывод заголовков столбцов в результате запроса. Опция имеет эффект, если задана опция TO. Если задана опция INTO, то опция PLAIN игнорируется.

NOWAIT – обеспечивает продолжение выполнения программы после открытия Browse-окна с результатами запроса. Также опция оказывает эффект при выводе результата запроса в окно VFP. При ее отсутствии после заполнения окна работа программы приостанавливается и будет продолжена после нажатия любой клавиши. Если опции задана, то вывод результата будет непрерывным. Опция NOWAIT игнорируется, если включена опция INTO.

Имена полей результата.

Если опущена опция AS, то имя поля результата образуется по следующим правилам:

Если Select_Item – это имя поля с уникальным именем, то поле (столбец) результат будет иметь имя Select_Item-поля.

Если несколько Select_Item имеют одинаковое имя (без учета псевдонима), то к имени каждого производного столбца прибавляется символ подчеркивания, а также буква a – к имени первого столбца, буква b – к имени второго и т. д. Если имена повторяющихся полей имеют 10 символов, то последние 2 символа имен будут заменены на _a, _b и т. д.

Если Select_Item – это выражение, то соответствующий столбец результата будет иметь имя EXP_*, где * – это 1 для первого выражения, 2 для второго и т. д.

Если Select_Item содержит агрегатную функцию, например COUNT(Field), то столбец результата будет иметь имя Cnt_field; в случае функции SUM(Field) – имя Sum_field и т. д.

Пользовательские функции.

При включении в SELECT – SQL пользовательских функций нужно учитывать следующие ограничения:

Пользовательские функции снижают скорость выполнения запроса на время, пропорциональное числу операций в функции и числу строк в результате. Более высокое быстродействие достигается при подключении средствами API пользовательских функций, написанных на языке С или ассемблере.

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

Небезопасно изменять окружение VFP и таблиц в SELECT – SQL-пользовательской функции. В общем случае результаты непредсказуемы.

Единственный надежный способ передачи значений SELECT – SQL-пользовательской функции – это передача через параметры.

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

Объединение таблиц.

VFP поддерживает для опции JOIN синтаксис, предусмотренный стандартом ANSI SQL 92. Математическая модель SQL основывается на теории множеств. Наглядно каждая таблица может быть представлена в виде круга. Тогда внутреннее соединение двух таблиц – это область пересечения двух кругов, представляющих таблицы. Внешнее пересечение – это область, отображающая внутреннее соединение, и внешняя по отношению к внутреннему соединения область правого или левого круга.

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

select Books.Book, BooksContent.Name ;

from Books, BooksContent

равно RECCOUNT('Books') * RECCOUNT('BooksContent').

Включение в условие объединения или отбора таких функций, как DELETED( ), EOF( ), FOUND( ), RECCOUNT( ) и RECNO( ), принимающих в качестве необязательного параметра псевдоним таблицы или номер рабочей области, может привести к непредсказуемым результатам. Это объясняется тем, что SELECT – SQL использует свои псевдонимы, выполняя неявно команду USE ... AGAIN. Запрос к одной таблице с условиями отбора, имеющими подобные функции, даст правильные результаты, если функции употреблены без параметра.

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

select Test.SomeField ;

from Test inner join Test2 on Test.CharField = Test2.CharField2

в котором таблица Test имеет 2 пустых поля CharField, а таблица Test2 – 5 пустых полей CharField2, после выполнения даст результат с 10 пустыми записями.

Пустые поля будут исключены из результата, если употреблена функция EMPTY( ), например:

select Test.SomeField ;

from Test inner join Test2 on Test.CharField = Test2.CharField2 ;

and not Empty(Test.CharField)

Следует однако помнить, что функции ISBLANK( ) и EMPTY( ) рашмор-неоптимизируемы.

Преобразование типов данных.

Если при выполнении SELECT – SQL-запроса с опцией UNION объединяются 2 столбца с разными типами данных, то VFP преобразовывает тип данных с более низким приоритетом в тип с более высоким. При этом свойство поля NULL имеет более высокий приоритет, чем свойство NOT NULL. Преобразование, выполняемое VFP, является неявным. Для явного преобразования типов необходимо использовать подходящую встроенную функцию. Возможные неявные преобразования типов данных и соответствующие им функции явного преобразования приведены в табл. 15.19.

Таблица 15.19

Допустимые неявные преобразования типов данных

Во всех случаях для явного преобразования типов данных можно употребить функцию CAST( ).

В табл. 15.20 приведены для 2-х полей результаты неявного преобразования типов данных при выполнении SELECT...UNION.

Таблица 15.20

Результаты неявного преобразования типов данных двух полей

Подзапросы.

Подзапрос – это SELECT – SQL, расположенный внутри другого SELECT – SQL, либо это SELECT – SQL в опции WHERE.

Подзапрос:

- может включать несколько условий объединения;

- заключается в круглые скобки;

- результат подзапроса содержит 1 столбец;

- выполняется с условиями отбора ALL | ANY | SOME;

- имеет следующий синтаксис записи отбора:

FieldName Comparison ALL | ANY | SOME (Subquery)

или

[NOT] EXISTS (Subquery)

или

FieldName [NOT] IN (Subquery)

Любой из приведенных подзапросов может быть использован в условии отбора.

Пример 1. Из таблицы Authors выбираются авторы, представленные в библиотеки двумя или большим числом книг. Условие отбора ANY означает, что из таблицы Authors в результат запроса попадут все авторы, коды которых находятся в результате подзапроса.

close databases

open database 'd:\HomeLibrary\HomeLibrary'

select Author from Authors ;

where AuthorId = any (select AuthorId ;

from BooksAuthors ;

group by AuthorId having Count(BookId) >= 2) ;

order by Author

Тот же результат будет получен, если использовать подзапрос в условии отбора с опцией IN:

select Author from Authors ;

where AuthorId in (select AuthorId ;

from BooksAuthors ;

group by AuthorId having Count(BookId) >= 2) ;

Пример 2. Приводится отбор с условием ALL. В результат запроса попадут все записи, для которых значение поля Company меньше каждого значения этого же поля подзапроса.

company < all (select Company from Customer where Country = "UK")

Пример 3. Выбираются авторы, книги которых не представлены в базе данных.

&& Запрос с NOT EXISTS (Subquery)

close databases

open database 'd:\HomeLibrary\HomeLibrary'

select Author from Authors ;

where not exists ;

(select * from BooksAuthors where Authors.AuthorId = BooksAuthors.AuthorId) ;

order by Author

Опция ESCAPE.

Употребляется с операцией LIKE, если данные содержат символ-маску % или _.

Пример 1. Выбираются из таблицы Customer записи, в которых поле Company содержит строку с символом "%". VFP будет интерпретировать его как литерал, если использовать приведенный в примере синтаксис.

&& Запрос с Like и опцией Escape, задающей escape-символ "\"

close databases

open database (Home(2) + 'Data\TestData')

select * from Customer ;

where Company like "%\%%" escape "\"

Замечание. TestData – это имя поставляемой с VFP тестовой базы данных. Как вариант функция HOME(2) может вернуть директорию C:\Program Files\Microsoft Visual FoxPro 9\Samples\.

Пример 2. Из таблицы Customer выбираются все записи, в которых поле Company содержит строку с символом "_". VFP будет интерпретировать его как литерал, если использовать приведенный в примере синтаксис.

&& Запрос с Like и опцией Escape, задающей escape-символ "\"

close databases

open database (Home(2) + 'Data\TestData')

select * from Customer ;

where Company like "%\_%" escape "\"

Пример 3. Из таблицы Customer выбираются все записи, в которых поле Company содержит строку с подстрокой из двух символов "%_". VFP будет интерпретировать символы "%" и "_" как литералы, если использовать приведенный в примере синтаксис. При этом символ "_" употребляется в тексте запроса как escape-символ и как литерал.

&& Запрос с Like и опцией Escape, задающей escape-символ "_"

close databases

open database (Home(2) + 'Data\TestData')

select * from Customer ;

where Company like "%_%__%" escape "_"

Замечание. В поставляемой VFP тестовой базе TestData нет записей, содержащих символы "%" и "_". Поэтому для проведения испытаний примеров следует открыть таблицу Customer и изменить надлежащим образом несколько ее записей.

Примеры употребления SELECT – SQL.

Пример 1. Результат запроса направляется в таблицу d:\CustShip.dbf.

&& Запрос с INTO TABLE

close databases

open database (Home(2) + 'Data\TestData')

select X.Company, Y.Order_Date, Y.Shipped_on ;

from Customer as X, Orders as Y ;

where X.Cust_id = Y.Cust_id ;

into table d:\CustShip.dbf

browse last

Пример 2. Опция WHERE с условиями объединения и отбора. Выводятся все покупатели, а также заказы с датой меньшей 16.02.1994.

close databases

open database (Home(2) + 'Data\TestData')

select X.Company, Y.Order_Date, Y.Shipped_on ;

from Customer as X, Orders as Y ;

where X.Cust_id = Y.Cust_id ;

and Y.Order_date < {^1994-02-16}

Пример 3. Опция LEFT JOIN с условиями объединения и отбора. Выводятся все покупатели, а также заказы с датой меньшей 16.02.1994.

&& Запрос с LEFT JOIN с условиями объединения и отбора

close databases

open database (Home(2) + 'Data\TestData')

select X.Company, Y.Order_Date, Y.Shipped_on ;

from Customer as X ;

left join orders as Y ;

on X.Cust_id = Y.Cust_id ;

and Y.Order_date < {^1994-02-16}

Пример 4. Прописными буквами выводятся названия всех имеющихся в таблице Customer городов. Для столбца результата запроса задается имя CityList.

close databases

open database (Home(2) + 'Data\TestData')

select Upper(City) as CityList from Customer

Пример 5. Вложенные подзапросы (см. также в справке тему What's New in Visual FoxPro; SQL Language Improvements).

create cursor MyCursor (Field1 I)

insert into MyCursor values (0)

&&

create cursor MyCursor1 (Field1 I)

insert into MyCursor1 values (1)

&&

create cursor MyCursor2 (Field1 I)

insert into MyCursor2 values (2)

&&

select * from MyCursor where exists ;

(select * from MyCursor1 where not exists ;

(select * from MyCursor2 where Field1 > 2))

На выполнение команды SELECT – SQL влияют установки команд SET DELETED, SET ANSI, SET VARCHARMAPPING, SET SQLBUFFERING и SET ENGINEBEHAVIOR и функций SYS(3055) и SYS(3099).

Команда

SET ANSI ON | OFF

определяет, как выполняется сравнение строк или двоичных данных разной длины операцией "=" в SQL-командах VFP. Если для сравнения выбрана такая операция, то при выполнении SQL-команды строки (двоичные данные) сравниваются посимвольно (побайтно), пока не достигнут последний символ более короткой строки (двоичной величины).

Опции:

ON – длины сравниваемых строк (двоичных величин) выравниваются за счет добавления пробелов (нулевых байтов) к более короткой строке (двоичной величине). При такой установке сравнение

'Tommy' = 'Tom'

вернет .F., поскольку будут сравниваться строки равной длины (по 5 символов).

OFF – (по умолчанию) длины сравниваемых строк (двоичных величин) не изменяются (определяются более короткой величиной). При такой установке SET ANSI сравнения

'Tommy' = 'Tom'

и

'Tom' = 'Tommy'

вернут .T., поскольку сравниваются только первые 3 символа строк.

Команда SET ANSI не оказывает действия на операцию "==". При ее использовании длины сравниваемых строк (двоичных величин) всегда выравниваются за счет добавления пробелов (нулевых байтов) к более короткому значению.

Установка SET ANSI сохраняется с текущий сессий данных.

Команда

SET VARCHARMAPPING ON | OFF

задает тип данных символьного выражения в результате запроса и вычисляемого поля.

Опции:

ON – символьные выражения представляются в результате запроса полями типа Varchar.

OFF – (по умолчанию) символьные выражения представляются в результате запроса полями типа Character.

Пример. В результате первого запроса поле AuthorChr имеет тип Character, а второго поле AuthorVChr имеет тип Varchar. Поэтому длина строки, хранимой в поле AuthorChr, равна длине поля AuthorChr, а строка, хранимая в поле AuthorVChr, не содержит хвостовых пробелов, дополняющих ее длину до размера поля. В обоих случаях длина поля результата запроса равна длине поля Author таблицы-источника данных.

close databases

open database 'd:\HomeLibrary\HomeLibrary'

set varcharmapping off

select Rtrim(Author) as AuthorChr from Authors

? Len(AuthorChr)               && Напечатает: 50

&&

set varcharmapping on

select Rtrim(Author) as AuthorVChr from Authors

? Len(AuthorVChr)            && Напечатает: 29

Установка команды сохраняется с текущей сессией данных. Установка VARCHARMAPPING может быть выполнена и в файле конфигурации Config.fpw в результате соответствующего задания одноименной опции.

Установка VARCHARMAPPING воздействует и на тип вычисляемых полей создаваемых, например, командой SET FIELDS: если вычисляемое поле оценивается как символьное выражение, то результат получит тип Varchar, если SET VARCHARMAPPING установлен в ON, и получит тип Character – в противном случае. Этот результат будет использован, например, при последующем употреблении команды COPY TO.

Пример (приводится в справке VFP). Поле CalcField таблицы CrsTemp будет иметь тип Varchar.

set varcharmapping on

set safety off

close databases all

use Home(2) + 'NorthWind\Customers'

set fields global

set fields to CalcField = Alltrim(CompanyName)

copy to d:\CrsTemp

set fields local

set fields off

set fields to

select 0

use d:\CrsTemp

modify structure                 && Результат приведен на рис. 15.32

Рис. 15.32. Тип поля CalcField таблицы CrsTemp

Команда

SET SQLBUFFERING ON | OFF

указывает, будет ли команда SELECT – SQL использовать данные (ON), имеющиеся в локальном буфере (если таковые имеются), или будет брать данные с диска (OFF, по умолчанию). Если установка команды ON и не имеется буферизованных данных, то они будут взяты с диска.

Установка команды сохраняется с текущей сессией данных.

Опция WITH (BUFFERING = lExpr) команды SELECT – SQL, если задана, имеет больший приоритет, чем установка команды SET SQLBUFFERING.

Команда

SET ENGINEBEHAVIOR 70 | 80 | 90

обеспечивает совместимость исполнителя SQL-запроса с версиями 7.0, 8.0 и 9.0.

Параметры:

70 – VFP обрабатывает SQL-запрос методами, применявшимися в версии 7.0 и более ранних версиях.

80 – реализуется применяемая в версии 8.0 технология обработки SQL-запроса. Ее особенности описываются в табл. 15.21.

Таблица 15.21

Особенности поведения SQL-опций в версии 8.0

90 – (по умолчанию) реализуется применяемая в версии 9.0 технология обработки SQL-запроса. Ее особенности описываются в табл. 15.22.

Таблица 15.22

Особенности поведения SQL-опций в версии 9.0

Область действия SET ENGINEBEHAVIOR – глобальная.

Замечание. То же действие выполняет функция SYS(3099 [, 70 | 80 | 90]).