Язык sql в foxpro
Язык sql в foxpro
Краткие теоретические сведения
В системах управления базами данных анализ данных обычно реализуется с помощью так называемых запросов (Query). В рамках запроса пользователь формулирует критерии отбора данных и способы преобразования отобранной информации. Выполняя сформулированный запрос к базе данных, пользователь получает заказанные данные в преобразованном и удобном для восприятия виде – результат запроса.
В FoxPro пользователю доступны два способа проектирования запросов:
RQBE и SQL находятся в тесной взаимосвязи, поскольку каждый запрос, конструируемый пользователем в окнах проектирования RQBE, «на заднем плане» автоматически преобразуется Генератором запросов в соответствующие SQL-директивы.
Язык SQL используется как для разработки прикладных программ, так и для «ручного» манипулирования базами данных. FoxPro поддерживает четыре SQL-директивы:
Директива SELECT, без сомнения, является центральной и наиболее часто встречающейся SQL-директивой. Она может содержать множество различных параметров, с помощью которых можно определить самые различные критерии отбора информации из базы, что и составляет суть запроса. Важнейшими параметрами являются
Параметр FROM задается в любом случае. Он определяет имена всех таблиц данных, принимающих участие в запросе. Если в запросе участвует более одной таблицы, то отдельные имена разделяются запятыми.
Параметр WHERE связывает все участвующие таблицы одна с другой и определяет условие, которому должны отвечать отбираемые запросом данные. Без WHERE все записи данных первой таблицы были бы связаны со всеми записями данных второй таблицы и т.д. WHERE ограничивает количество результирующих данных определенным условием. В WHERE-условиях пользователь может применять операторы сравнения (=, и т.д.), а также логические операторы ALL, ANY, BETWEEN, EXISTS, LIKE и отрицание NOT.
В одном WHERE-параметре можно связать между собой несколько условий, объединив их как с помощью логической операции И (AND), так и с помощью логической операции ИЛИ (OR). При вычислении значения логического выражения операции AND имеют приоритет по отношению к операциям OR (подобно тому, как операция умножения имеет приоритет перед операцией сложения).
FROM, clients, order;
WHERE clients-city IN («Псков», «Новгород»);
AND order. quantity>100
В результате запроса отбираются все записи (целиком, т.е. включая все поля) двух баз данных Clients и Order для тех клиентов, фирмы которых находятся в Пскове или Новгороде и число заказов которых превышает 100.
Параметр GROUP BY объединяет записи данных с одинаковым значением некоторого поля ключа. Результат выполнения запроса содержит одну запись для всех записей данных, у которых указанный ключ имеет одинаковое значение.
Пример:
SELECT order.clientnmn, SUM (order.quantity);
GROUP BY order. Clientnum
В результате запроса будет получена информация об объемах заказов отдельных клиентов (поля quantity), причем записи будут сгруппированы по номерам клиентов. Для каждого клиента выводится по одной записи данных с общим объемом заказов.
Когда в обрабатываемой запросом базе данных записи отсортированы или проиндексированы согласно заданному критерию, вывод результата запроса ускоряется.
Параметр HAVING действует подобно параметру WHERE, но он относится не к отдельным записям данных, а к группам данных. Поэтому HAVING должен применяться только в совокупности с GROUP BY. В противном случае HAVING действует так же, как WHERE.
С помощью параметра ORDER BY данные в результате запроса сортируются согласно определенным критериям сортировки.
3. Составление сложных запросов.
Построение простого запроса
Рис. 6.1. Диалоговое окно проектирования запроса
Рис. 6.2. Отбор полей
В правом списке Выбранные поля приведены выбранные выводимые поля в данном случае все поля базы данных адресов. С помощью командных кнопок Удалить и Удалить все можно удалить поля таблицы из выходного списка и перенести их в левый список. Таким образом, число индицируемых в результате запроса полей уменьшается.
Запрос должен отбирать всех клиентов, агентства которых находятся в Норвегии. Критерии запроса устанавливаются по следующей схеме:
Построение сложного запроса для нескольких баз данных
В этом окне определяются связи между участвующими в запросе базами данных через общие поля ключей. Если в открытой и открываемой таблицах есть одноименные поля, то FoxPro автоматически предлагает связь через них.
Для группировки данных применяется параметр GROUP BY
SQL-директивы.
Можно провести группировку по таким полям, которые хотя и обрабатываются запросом, однако в результате запроса не индицируются. Если для группировки выбирается поле, не являющееся выводимым, то в области Поля результата это поле автоматически вставляется в список выводимых полей, но изображается серым шрифтом (как недоступное).
В результате запроса пользователь может представить записи данных в определенной установленной им самим последовательности. По умолчанию FoxPro передает данные, отсортированные по первому полю в списке.
Можно определить произвольное число критериев сортировки. При выполнении запроса данные сначала сортируются по первому критерию сортировки. Все блоки с одинаковыми значениями поля первого критерия сортировки дополнительно сортируются по второму критерию и т. д. Очередность критериев можно изменить путем переноса критериев сортировки в правом списке диалога в другую позицию списка с помощью манипулятора переноса, расположенного слева от имени поля.
Вывод результатов запроса
Отчет по работе должен содержать:
Расширенные Возможности
Расширенные ВозможностиКоличество Аргументов в Секции IN( )
Расширение функциональности использования ПодЗапросов SQL
В версии Visual FoxPro 9.0 обеспечена большая Гибкость при создании Подзапросов. Например, теперь поддерживаются несколько ПодЗапросов в одной команде SELECT-SQL. Далее описаны основные Улучшения функциональности ПодЗапросов.
Несколько ПодЗапросов
Visual FoxPro 9.0 поддерживает множественные вложенные ПодЗапросы, с поддержкой Корреляции элементов относительно Родительского Запроса. Теперь отсутствуют Ограничения по глубине вложенных Подзапросов. В версии Visual FoxPro 8.0, возникала Ошибка = 1842 (SQL: Подзапрос, недопустимое вложение), которая порждалась при использовании более Одного вложения ПодЗапросов в одной команде SQL.
Далее приводится Основная допустимая синтаксическая Конструкция для нескольких вложенных ПодЗапросов.
SELECT … WHERE … (SELECT … WHERE … (SELECT …) …) …
Пример:
Представленный далее Пример приводит к возникновению ошибочной ситуации в версии Visual FoxPro 8.0 ; Однако, в версии Visual FoxPro 9.0. он успешно выполняется:
Секция GROUP BY в Коррелированном ПодЗапросе:
Многие Запросы могут быть оценены как успешно выполняемые в зависимости от содержания ПодЗапроса или от изменяемого Значения в секции WHERE Родительского Запроса. В Запросах, содержащих Коррелированные ПодЗапросы (называемые также Повторяющимися Подзапросами), выполнение подзапроса существенно зависит от значений некоторых элементов Родительского Запроса. Это означает, что указанный ПодЗапрос выполняется многократно, для каждой результирующей Записи Родительского Запроса.
В версии Visual FoxPro 8.0 не допускалось использование выражения GROUP BY в Коррелированном ПодЗапросе, и возникала Ошибочная ситуация = 1828 (SQL: недопустимое использование выражения GROUP BY в ПодЗапросе). В версии Visual FoxPro 9.0 указанное ограничение снято, рассматриваемое выражение GROUP BY может эффективно использоваться в коррелированных подзапросах, в резельтирующий наборе данных включаются уже более одной Записи.
Далее приводится поддерживаемая теперь синтаксическая конструкция команды SQL с секцией GROUP BY в коррелированном Подзапросе.
SELECT … WHERE … (SELECT … WHERE … GROUP BY …) …
Пример:
Следующий программный Код приводит к ошибке в версии Visual FoxPro 8.0, Но успешно выполняется в версии Visual FoxPro 9.0.
Секция TOP N в НеКоррелированных ПодЗапросах:
Версия Visual FoxPro 9.0 поддерживает теперь секцию TOP N в некоррелированных ПодЗапросах. Требуется использовать выражение ORDER BY если указывается рассматриваемая конструкция TOP N; Это единственный случай, при использовании ее в Подзапросах.
Далее представлена основная синтаксическая Конструкция использования утверждения TOP N в некоррелированных Подзапросах.
SELECT … WHERE … (SELECT TOP nExpr [PERCENT] … FROM … ORDER BY …) …
Пример:
Подзапросы в Списках столбцов команды SELECT:
Версия Visual FoxPro 9.0 теперь допускает Подзапросы, как Столбцы или части выражения в Результирующем наборе (списке столбцов). Значение ПодЗапроса оценивается по тем же правилам, как и в операциях Сравнения. Если Подзапрос не возвращает ни одной Записи, то возвражается стандартное значение = NULL.
При использовании описанной выше конструкции в версии Visual FoxPro 8.0, возникаент Ошибочная ситуация = 1810 (SQL: Неверное использование ПодЗапроса).
Далее приводится общая допустимая синтаксическая конструкция Списка столбцов в команде SELECT.
SELECT … (SELECT …) … FROM …
Пример:
Использование Агрегатных Функций в Подзапросах, как столбцов в команде SELECT:
Пример:
Далее приводится программный Код, в котором используется агрегатная функция COUNT( ), как элемент Списка Подзапроса SELECT.
Коррелированные Подзапросы допускают теперь сложные Выражения Сравнения с коррелированным Полем:
В версии Visual FoxPro 8.0, ссылки на коррелируемые Поля (столбцы) могли быть указаны только в следующих Случаях:
Коррелируемое Поле Локальное Поле
Локальное Поле Коррелируемое Поле
В версии Visual FoxPro 9.0, в выражениях Сравнения, допускаются более сложные Конструкции, локальные поля, корреляционные поля, выражения:
Коррелируемое Поле Локальное выражение
Локальное выражение Коррелируемое Поле
В локальном выражении должна быть использована по крайней мере одна ссылка на локальное Поле, и не может быть использована ссылка на Итоговый (коррелированный) Столбец результирующего Набора.
Пример:
В представленном Коде локальное выражение (MyCursor2.field2 / 2) сравнивается с итоговым Полем (Столбцом) (MyCursor.field1).
Нововведения для сравниваемых выражений при использовании Подзапросов:
В версии Visual FoxPro 9.0, конструкции Сравнения работают в следующизх Режимах:
Выражение, размещенное в левой части конструкции сравнения IN должна содержать ссылку по крайней мере на Одну Таблицу, из соответствующей секции FROM.
Подзапросы в секциях SET должны удовлетворять тем же требованиям,ераций Сравнения. Если Подзапрос не возвращает результирующих Записей, то возвращается значение = NULL.
Только один Подзапрос допустим в секции SET. Если в данной секции SET используется Подзапрос, то утверждение типа WHERE недопустимо.
Общая синтаксическая конструкция для описанного случая может выглядеть следующим образом:
UPDATE … SET … (SELECT …) …
Пример:
Составные (вложенные) конструкции SELECT в секциях FROM:
В составных конструкциях SELECT часто появляются ссылки на различные Таблицы. Различные Таблицы в операторе SELECT указываются в секции FROM, которые задаются в виде Алиасов или пользовательских Имен. Результирующий Набор оператора SELECT, с использованием таблиц из секции FROM, создается при помощи Внешнего Объединения SELECT ( Outer Select ). Версия Visual FoxPro 9.0 обеспечивает использовани Подзапросов в секции FROM.
Далее представлена каноническая, синтаксическая конструкция для составного (вложенного) Подзапроса в секции FROM.
SELECT … FROM (SELECT …) [AS] Alias…
Пример:
Далее представлен пример использования вложенного SQL-SELECT из секции FROM.
Конструкция ORDER BY с именами Полей в секции UNION:
При использовании секции UNION в версии Visual FoxPro 8.0, вы были вынуждены использовать Номера результирующих Столбцов для определения необходимой Сортировки, определяемой в секции ORDER BY. В версии Visual FoxPro 9.0, описанное ограничение снято и вы можете указавать Имена результирующих Столбцов.
Требуемые имена столбцов сортировки должны быть определены в итоговом Списке результирующего оператора SELECT (последнего при использовании конструкций UNION); только после этого данные Столбцы могут быть указаны в секции Сортировки ORDER BY.
Пример:
Далее представлен пример использования имен итоговых столбцов для определения режимов Сортировки в секции ORDER BY.
Оптимизация выполнения конструкции TOP N:
В версии Visual FoxPro 8.0, и в более младших версиях, когда используется конструкция TOP N [PERCENT], сначала выполняется выборка и сортировка всех Результирующих Записей, а потом извлекаются требуемые (TOP N). В версии Visual FoxPro 9.0, выполнение подобных SQL-SELECT значительно улучшено, увеличена скорость выборки, за счет Исключающих Записей (Eliminating Records), которые изначально отбрасываются и не участвуют в формировании результирующего Набора, создаваемого по конструкции TOP N, исключаются из процесса сротировуки на ранних стадиях выполнения Запроса.
Конструкция TOP N оптимизируется только в том случа, когда для стандартной команды SET ENGINEBEHAVIOR установлено значение = 90.
Оптимизация требует, чтобы конструкция TOP N возвращала не более чем N записей в Результирующем Наборе (что недействительно для версии Visual FoxPro 8.0 и более ранних версий), когда же определено SET ENGINEBEHAVIOR = 90, то процессы оптимизации Включаются.
Секция TOP N PERCENT не может быть Оптимизирована, если результирующий Набор не поместим целиком в оперативную память Рабочей Станции.
Улучшенная Оптимизация выборки из нескольких Таблиц с составными условиями типа OR (или)
Версия Visual FoxPro 9.0 обеспечивает улучшение Оптимизации типа Rushmore для многотабличных Выборок с условными конструкциями OR (ИЛИ). Visual FoxPro использует оптимизацию Rushmore для фильтрованных сложных составных Условий типа OR для обоих сторон определяющих условия выборки; в данном случае возможна Улучшенная Оптимизация. Следующий далее пример иллюстрирует вышесказанное:
По представленному Сценарию выполнения Запроса, выборка из Таблицы Test1 может быть оптимизированапо Rushmore; для этого рекомендуется использовать следующие Оптимизируемые Конструкции:
(f1 IN (1,2,3) OR f1 IN (17,18,19))
(f2 IN (17,18,19) OR f2 IN (1,2,3))
Поддержка для Локально Буферизованных Данных (Local Buffered Data):
Расширение других Команд SQL (Visual FoxPro)
Далее представлена основная синтаксическая конструкция использования секции UNION:
INSERT INTO … SELECT … FROM … [UNION SELECT … [UNION …]]
Пример:
Далее демонстрируется использование секции UNION в команде INSERT-SQL.
Если указанное Имя явно или неявно совпадает с именем Таблицы, указанной в секции FROM, тогда эта таблицы используется как результирующий набор в операции Изменения (UPDATE SQL).
Если указано имя Таблицы или Файла, то данное имя используется в качестве результирующего набора.
Результирующая Таблица или Курсор не могут быть включены в качестве второстепенного (присоединяемого) Набора Данных в утверждении OUTER JOIN.
Результирующий Курсор не может быть Результатом ПодЗапроса.
Все другие присоединяемые Элементы (JOIN) должны быть уже вычислены, до определения Результирующего набора.
Действителен следующий общий синтаксис для команды SQL- UPDATE.
UPDATE … SET … FROM … WHERE …
Пример:
Если в секции FROM указана более чем одна таблица, тогда Имя, заданное сразу за ключевым словом DELETE является обязательным для успешного выполнения операции удаления. Данное имя может указывать: на имя Таблицы, Алиаса Рабочей Области или имя файла Данных. Рекомендуется поддерживаться следующей Логике:
Если указанное Имя, явно или неявно, соответствует Таблице, указанной в секции FROM, тогда эта таблица является результирующим набором при операции Удаления.
Если Имя соответствует Алиасу Курсора для текущей Сессии Данных, тогда этот курсор является результирующим Набором.
Указанное явно имя Таблицы или файла Данных является результирующим набором.
Результирующий курсор или Таблица не могут быть второстепенным (присоединяемым) элементом в утверждении OUTER JOIN.
Результирующий Курсор не может быть результирующим набором Подзапроса.
Для возможности использования результирующего Курсора в конструкциях Объединения, все прочие Элементы JOIN должны быть определены до вычисления рассматриваемого Курсора.
DELETE [ alias ] FROM alias1 [, alias2 … ] … WHERE …
Пример:
Команда SET ENGINEBEHAVIOR:
Преобразование Типов Данных:
См. также
SQL-функции в Microsoft Visual FoxPro
SQLCONNECT([nStatementHandle])
SQLCONNECT([cConnectionName | cDataSourceName [, cUserID
[, cPassword ]] [, lShared]])
устанавливает SQL-соединение с источником данных. Функция возвращает идентификатор SQL-соединения – положительное значение типа Numeric, если соединение установлено, и –1 – в противном случае.
nStatementHandle – обеспечивает создание нового идентификатора SQL-соединения для существующего разделяемого SQL-соединения, представленного nStatementHandle. Новый идентификатор перенимает установки идентификатора nStatementHandle, а не использует заданные по умолчанию. При попытке создать новый идентификатор для неразделяемого соединения VFP генерирует ошибку.
Функция SQLCONNECT(nStatementHandle) может быть также употреблена для получения нового идентификатора SQL-соединения, на основе разделяемого SQL-соединения открытого функцией SQLSTRINGCONNECT( ).
cConnectionName – имя соединения, созданного командой CREATE CONNECTION или проектировщиком соединения. База данных, которой принадлежит соединение cConnectionName, в момент выполнения SQLCONNECT( ) должна быть текущей.
cDataSourceName – ODBC-имя источника данных.
Источник данных может быть выбран в Select Connection or Data Source-диалоге, который появляется при вызове SQLCONNECT( ) без единого параметра или только с параметром lShared.
cUserID – идентификатор пользователя.
cPassword – пароль пользователя.
&& nStatementHandle – идентификатор SQL-соединения, выработанный SQLCONNECT( )
SQLSetProp(nStatementHandle, ‘DispLogin’, 3)
либо употребляется проектировщик соединения.
Пример. Создаются 3 идентификатора SQL-соединений, ассоциированных с одним и тем же ODBC-источником данных – BookSale.
open database d:\HomeLibrary\HomeLibrary
&& Разделяемое SQL-соединение на основе именованного соединения BookSale
&& Разделяемое SQL-соединение; указываем ODBC-имя источника данных
&& Новый идентификатор на основе существующего SQL-соединения
&& с идентификатором nStHandle2
? nStHandle1, nStHandle2, nStHandle3
SQLSTRINGCONNECT([lShared] | [cConnectString [, lShared]])
устанавливает SQL-соединение с источником данных, используя строку соединения. Возвращает, как и SQLCONNECT( ), идентификатор SQL-соединения или –1, если соединение не установлено.
cConnectString – строка соединения источника данных, требуемая при работе с некоторыми ODBC-драйверами.
Пример. Устанавливается SQL-соединение с источником данных BookSaleDataBase.
&& Пробелы между именами и знаками должны отсутствовать
или, когда идентификатор и пароль пользователя хранят переменные:
nStHandle = SQLStringConnect(‘dsn=BookSaleDataBase;uid= ;pwd= ‘)
if nStHandle > 0 then
MessageBox(‘Не удалось создать SQL-соединение!’)
Пример. Показывает, как использовать SQLSTRINGCONNECT( ) без имени DSN.
lcDSNLess=»driver=SQL Server;server= ;uid= ;pwd=
lcDSNLess=»driver=
+ «app=MicroX(R) Sample App»
SQLDISCONNECT(nStatementHandle)
разрывает SQL-соединение, указанное параметром nStatementHandle, с источником данных.
SQLDISCONNECT(0) && nStatementHandle = 0
разрывает все активные SQL-соединения.
Если функцию вызвать внутри последовательности функций, работающих в асинхронном режиме, или во время транзакции, то VFP сгенерирует ошибку.
Результат и параметр функции описаны в разд. 16.3.3.
SQLIDLEDISCONNECT(nStatementHandle)
временно разрывает SQL-соединение. Возвращает 1, если действие выполнено, или –1 – в противном случае. Неудача может возникнуть, например, если соединение занято.
Временно прерванное соединение автоматически восстанавливается, как только возникает необходимость в выполнении операции. ODBC-свойство ODBChstmt равно 0, если идентификатор соединения временно освобожден; свойство ODBChdbc равно 0, если соединение временно прервано. Разделяемое соединение временно прерывается после временного освобождения всех его идентификаторов.
SQLSETPROP(nStatementHandle, cSetting [, eExpression])
устанавливает свойство, указанное параметром cSetting, активного соединения, заданного параметром nStatementHandle. Если в качестве nStatementHandle передан 0, то SQLSETPROP( ) установит свойство на уровне окружения соединения.
Результат функции и параметр nStatementHandle описаны в разд. 16.3.3.
cSetting – строка с именем устанавливаемого свойства. Может принимать приведенные в табл. 16.7 значения (часть свойств описана в табл. 14.15).
Visual FoxPro 9. Изменения в диалекте SQL
Изменения в диалекте SQL
Диалект SQL в VFP фактически не менялся в течение длительного времени. Начиная с восьмой версии, Microsoft начала включать в диалект дополнительные возможности, тем самым увеличивая соответствие его стандарту ANSI SQL-92. В VFP 9 существенно расширен диалект SQL для команд SELECT, INSERT, UPDATE и DELETE, а также добавлен ряд других усовершенствований.
Многие из ограничений, касающиеся запросов, были отменены, было значительно расширено использование подзапросов, а в командах UPDATE и DELETE стало возможным использование условия объединения записей. В VFP 9 также предложены некоторые усовершенствования, повышающие эффективность, более простые способы проверки эффективности, и способность выполнять запросы для буферизированных таблиц.
Наиболее значимое изменение в системе запросов VFP 9 — это отмена ряда ограничений. В более ранних версиях, например, общее число объединений и подзапросов в одиночном запросе было ограничено девятью; в VFP 9 не имеется никаких ограничений. В таблице 1 показаны ограничения, связанные с запросами, отменённые или расширенные в VFP 9.
Таблица 1. Сравнение ограничений SQL в разных версиях VFP
Ограничения в VFP 8 (и более ранних версиях)
Ограничения в VFP 9
Общее количество объединений и подзапросов
Общее количество Unions
Количество ссылок на таблицы (псевдонимы)
Количество значений в предложении IN
Определяется установкой функции SYS(3055)
Уровни вложенности для подзапросов
В повседневности разработчики, формирующие SQL запросы, обычно не сталкиваются с этими ограничениями; но любой из них может оказаться в ситуации, когда подобная проблема возникнет.
Если данные в базе данных хорошо нормализованы, то существовавшие ранее ограничения как на количество объединений, так и на количество используемых таблиц (псевдонимов), могли приводить к значительным трудностям при выборке данных. Например, рассмотрим базу данных Northwind, которая поставляется с восьмой и девятой версиями VFP. В выборке всех данных, касающихся заказов, включая информацию о заказчиках, информацию о грузоотправителях, информацию о поставщиках и т.д, используется 11 таблиц. В листинге 1 показан такой запрос.
В этом запросе перечислены 11 различных псевдонимов и выполняется 10 объединений; VFP 8 и более ранние версии не могли выполнять такой запрос (генерировалась ошибка 1805, «SQL — Слишком много подзапросов»). VFP 9 без проблем выполняет его. (В то время как этот пример был придуман, выборка из нормализованной базы данных может включать множество источников и объединений).
Почти неограниченное количество параметров в IN
В более ранних версиях VFP количество перечисляемых в предложении IN параметров было ограничено 24. VFP 9 не отменяет это ограничение, но предоставляет вам контроль над ним при помощи функции SYS(3055). Даже без использования этой функции существующее ограничение значительно выше, чем в более ранних версиях языка (154 параметра).
Предложение IN может использоваться как фильтр, основанный на списке параметров, так и как фильтр, основанный на результате подзапроса. Внесённое изменение относится только к использованию IN со списком параметров.
Когда вы самостоятельно управляете запросом, ограничение, налагаемое предложением IN, вообще не является проблемой. Как правило, вы находите другое решение, позволяющее избежать большого количества параметров в предложении IN. Одно из решений состоит в том, чтобы сохранить список значений в курсор и выполнить объединение с этим курсором. Например, этот запрос:
Вложенные подзапросы
Возможность использовать подзапросы (запрос внутри запроса) позволяет получить результат, используя одиночный запрос; иначе потребовалось бы множество запросов. Возможно, наиболее часто используется запрос, включающий подзапрос, для выборки всех записей из одной таблицы, которых нет в другой. Например, следующий запрос (используется база данных TasTrade, поставляемая с VFP) получает список компаний из таблицы Customer, которые не разместили никаких заказов:
Другое общее использование для подзапроса — соединение частей, которое затем позволяет основному запросу выбрать дополнительную информацию из составных результатов. Например, Вы можете использовать подзапрос для получения списка продуктов, последний раз приобретённых каждым покупателем, как показано в листинге 2. Подзапрос в этом примере кореллируется, что означает использование им поля из таблицы, не перечисленной непосредственно в подзапросе, а только в основном запросе. В этом примере OrdLast.Customer_ID используется в предложении WHERE подзапроса, но OrdLast — псевдоним для Orders, определенных в основном запросе.
В VFP 8 и более ранних версиях вложенные подзапросы не поддерживались. То есть в предложении WHERE подзапроса нельзя было использовать другой подзапрос. VFP 9 позволяет использовать вложенные подзапросы, увеличивая число вопросов, на которые вы можете ответить, одиночным запросом.
Предположим, что вы хотите выяснять то, какие продукты компания включила в первый, но не самый последний, заказ. В то время как вы могли бы использовать курсор, созданный в листинге 2 в другом запросе, в VFP 9 вы можете выполнить всю работу целиком в одиночном запросе, показанном в листинге 3.
Дополнительная гибкость подзапросов
Вычисляемые поля с подзапросом
В дополнение к поддержке производных таблиц, VFP 9 позволяет вам помещать подзапросы в список полей запроса. То есть вы можете использовать подзапрос, чтобы вычислить поле, которое должно появиться в результате. Подзапрос, использующий этот способ, должен возвратить одиночное поле и не больше, чем одиночная запись. Если не имеется никаких записей в результирующем наборе для специфической записи, то значение поля устанавливается равным NULL.
Для чего это нужно? Почему бы не включить выражение в основной запрос и добавить любые необходимые таблицы? Как и с производными таблицами, этот подход удобен, когда используется группировка. Допустим, что вы хотите найти общее количество заказов, размещённых каждым заказчиком в конкретном году. Вместе с этим вы хотите иметь больше информации о заказчике, которая включала бы его адрес, номер телефона и факса.
Несомненно, для того, чтобы вычислить общее количество заказов для каждого заказчика, вы должны группировать данные из Order_Line_Items по заказчикам. Вы можете извлекать множественные поля из таблицы Customer обычным способом, указывая их в предложении GROUP BY. (До VFP 8, вы могли указывать в запросе дополнительные поля, даже не перечисляя их в предложении GROUP BY. В VFP 8, Вы можете сделать то же самое, выдав команду SET ENGINEBEHAVIOR 70) В листинге 8 показан запрос, который отыскивает id заказчика, название компании, адрес, телефон и факс наряду с общим значением количества заказов каждого заказчика.
Вычисленние значения замены в UPDATE
Третье новое место, где вы можете использовать подзапросы — это предложение SET команды UPDATE. То есть вы можете использовать подзапрос, чтобы вычислить значение, в которое должно быть установлено поле. Однако, когда Вы используете такой подход, команда UPDATE не может включать подзапрос в предложение WHERE. Кроме того, вы ограничены одиночным подзапросом в предложении SET, так что вы не можете использовать этот подход, чтобы вычислить значения множественных полей.
Для примера предположите, что вы имеете данные по складу (SalesByProduct) для базы данных TasTrade, которые сообщают вам, сколько и какие продукты были проданы и количество этого сбыта в долларах. Это разработано, чтобы хранить данные в течение одиночного месяца, и Вы хотите изменить их в конце месяца.
Чтобы модифицировать данные, используйте следующие команды UPDATE. Установите nMonth и nYear к значениям месяца и года для данных, которые вы собрали перед выполнением кода из листинга 11. (“Соотнесенные изменения”, рассматривающиеся позже в этой главе, обеспечивают лучшее решение для этой проблемы).
Соотнесенные подзапросы и группировка
Использование предложения TOP N в подзапросах
Предложение TOP N в SELECT возвращает в результирующем наборе только первые N записей (или первые N% записей). В то время как функции MIN() и MAX() позволяют вам выбирать одно самое маленькое или самое большое значение в данном поле, TOP N позволяет вам выбирать множественные значения. Вы можете использовать его, чтобы видеть вещи подобно 10 самым последним заказам или 30 наиболее дорогим изделиям. Например, следующий запрос возвратит в результате 10% изделий с наименьшей общей стоимостью.
В VFP 8 и более ранних версиях вы не могли использовать предложение TOP N в подзапросах. VFP 9 разрешает использование TOP N в подзапросах, если подзапрос не соотнесен. Когда вы используете TOP N в подзапросе, вы должны обязательно включить в этот подзапрос предложение ORDER BY.
Представьте себе, что вы рассматриваете вопрос прекращения поставок изделий, извлеченных предыдущим запросом (наиболее плохо продающиеся изделия). Возможно, вы захотите установить контакт с теми заказчиками, которые приобрели эти изделия, чтобы удостовериться, что это не создало для них проблемы. Запрос в листинге 14 использует предыдущий запрос как подзапрос для создания такого списка заказчиков.
Соотнесенные модификации
В дополнение к поддержке подзапросов в предложении SET, команда UPDATE SQL в VFP 9 имеет новое предложение FROM, которое позволяет Вам получать данные модификации из другой таблицы. Это позволяет вам использовать соотнесенные модификации.
Пример в листинге 11 имеет один серьезный недостаток. Вы должны использовать отдельную команду UPDATE для каждого поля, которое вы хотите изменить. При использовании предложения FROM вы можете получить тот же самый результат с запросом, сопровождаемым командой UPDATE. Код в листинге 15 вычисляет новые значения, сохраняет их в курсоре, и затем ссылается на этот курсор в команде UPDATE.
Соотнесённое удаление
В VFP 8 и в более раних версиях, команда DELETE SQL позволяла вам указывать только одну таблицу. В то время как вы могли использовать подзапросы в предложении WHERE, удаление записей, основанных на информации из других таблиц, было сложным. VFP 9 позволяет Вам перечислять множественные таблицы в предложении DELETE’s FROM, соединяя их согласно обычным правилам. Это обеспечивает намного более качественный способ выполнить соотнесенное удаление, удаляя строки из одной таблицы, основанной на данных из одной или большего количества других таблиц.
В синтаксисе для соотнесенного DELETE существует небольшая путаница. Если предложение FROM в DELETE содержит больше чем одну таблицу, вы должны определить целевую таблицу для удаления записей между DELETE и FROM:
DELETE [Адресат] FROM Table1 [JOIN Table2 …]
Используйте локальный псевдоним целевой таблицы между DELETE и FROM. Это может быть имя таблицы, но если вы назначаете локальный псевдоним к таблице в предложении FROM, используйте его вместо имени. (Обратите внимание, что те же самые правила относятся к UPDATE, когда модифицируемая таблица также включена в предложение FROM, как в листинге 16.)
База данныхTasTrade не содержит в себе примеров удаления такого типа; она разработана с учётом того, что записи отмечены неактивными скорее чем удаленными. Тем не менее, предположите, что вы имеете таблицы Products (Продукты) и Suppliers (Поставщики), подобные таким же таблицам в TasTrade, с первичным ключом Supplier, используемым как внешний ключ в Products. Предположите, что имеется проблема при получении изделий из Австралии, и вы решаете убрать из вашего списка изделий все изделия, которые приходят от поставщиков из Австралии. Чтобы выполнять такое удаление в VFP 8, вы используете подзапрос, подобный показанному в листинге 17. В VFP 9 вы можете вместо этого использовать условие JOIN. листинге 18 показана команда DELETE, удаляющая эти изделия из таблицы Products.
Дальнейшее усовершенствование UNION
Предложение UNION в SELECT позволяет Вам объединять результаты нескольких запросов в одиночный набор результатов. В VFP 8, правила для UNION были ослаблены, делая более легким использование этого предложения. VFP 9 предлагает еще два усовершенствования в UNION и одно ограничение.
Использование имён в UNION
Вставка данных из объединённых запросов
Использование круглых скобки в UNION теперь не разрешается
Хотя это действительно не было синтаксически правильно, более ранние версии VFP не создавали объект, если запросы в UNION были включены в круглые скобки. В VFP 9 одиночный запрос в UNION может быть окружен круглыми скобками, но круглые скобки, помещённые вокруг множественных запросов в UNION, генерируют новую ошибку с номером 2196.
Листинг 21 демонстрирует запрос, который работает в VFP 8, но терпит неудачу в VFP 9 из-за этого нового правила.
SELECT Company_Name, Address, City, Region, Postal_Code, Country FROM Customer ;
UNION ;
(SELECT Company_Name, Address, City, Region, Postal_Code, Country FROM Supplier ;
UNION ;
SELECT Company_Name, «», «», «», «», «» FROM Shippers)
Согласно рекомендации группы Fox team, использование круглых скобок для выделения множественных объединений может привести к неправильным результатам.
Комбинирование DISTINCT и ORDER BY
VFP позволяет Вам упорядочивать результаты запроса по любому полю из исходных таблиц; поля, указанные в списке ORDER BY, могут отсутствовать в списке полей запроса. В VFP 9 это больше не работает для запросов, которые используют SELECT DISTINCT. Например, следующий запрос выполняется в VFP 8, но приводит к ошибке 1808 (“SQL: предложение ORDER BY недопустимо”) в VFP 9:
SELECT Distinct Customer_ID FROM Orders ORDER BY Order_Date
На это поведение воздействует установка SET ENGINEBEHAVIOR. (См. “Отключение нового поведения” позже в этой главе).
Изменения в оптимизации
VFP 9 содержит несколько изменений, улучшающих эффективность ваших запросов, а также новую функцию, которая делает тестирование оптимизации проще.
Полностью оптимизирован оператор LIKE с “%”
Улучшение быстродействия для TOP N
Когда вы используете предложение TOP N или TOP N PERCENT, чтобы возвратить только подмножество записей, которые так или иначе соответствуют условиям запроса, VFP должен выяснить, какие записи имеются наверху списка. Когда вы определяете TOP N для большого набора, тот процесс может потребовать значительного времи. VFP 9 улучшает эффективность в этой ситуации.
В наших тестах мы не замечали различий, пока мы работали с не очень большой таблицей. При выборе TOP 20 из таблицы c почти 75,000 записей различия практически отсутствовали. Но когда мы сделали TOP 20 для таблицы, содержащей более чем миллион записей, то VFP 9 закончил выборку приблизительно в три раза быстрее, чем VFP 8.
Помимо уменьшения времени выборки с TOP N, немного изменилось и поведение запросов с TOP N. В более ранних версиях VFP запрос с предложением TOP N мог возвращать больше чем N записей из-за связей в данных. VFP 9 никогда не возвращает больше, чем точное число записей, определенных в предложении TOP N. (См. “Отключение нового поведения” позже в этой главе для обзора исключений из этого правила.) Когда имеются связи, оказывается, что VFP выбирает записи в физическом порядке из группы с тем же самым значением.
Эти изменения относятся к тем, на которые воздействует новая установка SET ENGINEBEHAVIOR TO 90. Для подробной информации см. “Отключение нового поведения” позже в этой главе.
Улучшение эффективности с OR
Вы можете также заметить некоторые усовершенствования в использовании OR в подзапросах.
Фильтрация и временные индексы
Когда вы объединяете в запросе две таблицы, VFP выбирает индексный тэг, подходящий для соответствующей записи. Если рассматриваемые поля не индексированы, или существующий тэг не способен сильно помочь (например, когда обьединение включает маленькую таблицу и намного большую таблицу, и имеется только тэг для меньшей таблицы), VFP создает индекс на лету. При вызове функции SYS(3054) это обьединение показывается как “использующее временный индекс” (“using temp index”).
VFP 9 ускоряет выполнение запросов, которые формируют временный индекс и имеют не оптимизируемый фильтр для этой таблицы (той, для которой индекс сформирован). Эффект проявляется наиболее сильно для больших таблиц и в случаях, где не оптимизированный фильтр маскирует много записей. Мы предполагаем, что двигатель делает фильтрацию перед формированием временного индекса, что ускоряет и формирование временного индекса и использование существующего.
В наших тестах ни одна из типовых таблиц, которые приходят с VFP, не была достаточно большой, чтобы наблюдать этот эффект. Мы смогли продемонстрировать это усовершенствование, используя таблицу с приблизительно 75,000 записей. Наш тестовый запрос соединил таблицу саму с собой на неиндексированном поле, используя условие фильтра, которое выбрало только приблизительно 250 записей. В VFP 9 запрос потребовал две секунды. В VFP 8 тот же самый запрос выполнялся почти 50 секунд.
Ускорение соотнесений (корелляции)
Вы можете никогда не столкнуться с другой областью, где эффективность была так улучшена. Если вы имеете запрос с соотнесенным подзапросом, и запрос также включает фильтр в таблице от основного запроса, которая используется в подзапросе, VFP 9 выполняет измерения лучше, чем VFP 8.
Мы создали следующий запрос, используя данные из TasTrade, и обнаружили, что VFP 9 отработал приблизительно в четыре раза быстрее:
SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;
Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;
FROM Orders ;
JOIN Customer ;
ON Orders.Customer_ID = Customer.Customer_ID ;
JOIN Shippers ;
ON Orders.Shipper_ID = shippers.Shipper_ID ;
WHERE Orders.Discount>0 AND ;
Orders.Order_Date = (SELECT MAX(Order_Date) ;
FROM Orders Ord ;
WHERE Orders.Customer_ID=Ord.Customer_ID );
ORDER BY Cust_Name ;
INTO CURSOR MostRecentOrders
Регистрация результатов оптимизации
Функция SYS(3054) появилась в VFP 5. Она предоставляла вам информацию относительно того, как FoxPro оптимизирует запрос. Она несколько раз улучшалась и теперь предоставляет множество данных относительно процесса оптимизации. Однако, нужно все еще интенсивно использовать SYS(3054), чтобы собрать информацию относительно эффективности запроса для всей программы или приложения.
Вызовите SYS(3092). Эта новая функция позволяет вам направлять вывод от функции SYS(3054) в регистрационный файл. Само собой, функция SYS(3054) может посылать вывод только активному окну или переменной. С SYS(3092) вы можете собирать данные относительно целого ряда запросов и исследовать их в свободное время.
Синтаксис:
CLogFile = SYS(3092 [, cFileName [, lAdditive]])
Параметр cFileName определяет имя (включая путь) регистрационного файла. Используйте lAdditive, чтобы определить, нужно ли записывать информацию поверх существующей. Значение по умолчанию перезавписывает существующий файл.
Выключить регистрацию и сделать файл регистрации доступный для чтения можно, передав пустую строку в качестве параметра cFileName.
Функция возвращает имя активного регистрационного файла. Обратите внимание, что новый регистрационный файл создаётся прежде, чем какое-либо значение возвращено, поэтому для сохранения старого регистрационного файла перед его изменением вы должны вызвать функцию один раз без параметров, и затем вызывите её снова.
Когда Вы включаете регистрацию функцией SYS(3092), вывод из SYS(3054) все еще отображается на экране, в активном окне или сохраняется в указанной переменной.
Как только вы указываете регистрационный файл в SYS(3092), используйте SYS(3054) по мере того, как вы хотите, и выполните запросы, которые вы хотите проверить. Когда проверки выполнены, сбросьте SYS(3054), и затем выдайте SYS(3092, » «) чтобы прекратить регистрацию. Вы можете затем исследовать регистрационный файл, чтобы просмотреть ваши результаты оптимизации.
Информация в регистрационном файле наиболее полезна, если вы передаете значение 2 или 12 как второй параметр для SYS(3054). Добавленная в VFP 7, эта установка включают запрос непосредственно в вывод перед сообщением относительно оптимизации.
Выборка из буферизированных таблиц
Ещё со времён VFP 3 разработчики FoxPro были расстроены поведением SELECT по отношению к буферизированным курсорам. Когда в запросе используется буферизированная таблица, VFP фактическую использует таблицу на диске, а не открытую буферизированную версию. Это означает, что результаты запроса не отражают несохранённые изменения данных.
Это поведение естественным образом следует из нормального поведения запросов. Независимо от того, открыта таблица или нет, когда она указана в предложении FROM запроса, VFP открывает её заново в новой рабочей области. В некоторых случаях было бы действительно удобно иметь возможность переместить данные из буферизированной таблицы в запрос. В VFP 8 и более ранних версиях вместо этого вы должны были обращаться к командам Xbase (типа CALCULATE).
VFP 9 предоставляет вам опцию просмотра буферизированных данных. Добавьте новое предложение WITH (Buffering=.T.) к запросу, и он использует доступный буфер вместо таблицы, сохранённой на диске. В листинге 22 показан запрос, который подсчитывает количество заказчиков в каждой стране, используя буферизированные данные.
Предложение WITH применяется к одиночной таблице. Если запрос перечисляет множественные таблицы, для которых вы хотите использовать буферизированные данные, включите утверждение WITH для каждой таблицы.
Одно большое предупреждение. Если вы используете буферизацию строки, запрос выполняет изменения для текущей строки. Это фактически имеет смысл, поскольку запрос перемещает указатель записи в буферизированной таблице. В более ранних версиях, где запросы запрашивали данные с диска, указатель записи в буфере не двигался, но когда вы делаете запрос непосредственно к буферу, указатель записи перемещается.
Вы можете также управлять поведением запросов с буферизированными таблицами глобально. Новая команда SET SQLBUFFERING позволяет вам определять, получают ли запросы данные по умолчанию с диска или из буферов. Предложение WITH (Buffering = lExpr) отменяет текущую установку для специфицированной таблицы и запроса. SET SQLBUFFERING действует в текущем сеансе данных. Используйте SET(“SQLBUFFERING”), чтобы узнать текущую установку.
Отключение нового поведения
Новые возможности команд SQL
Команда SELECT. SQL и другие команды SQL в девятой версии Visual FoxPro были значительно расширены. В этой главе рассматриваются расширения существующих команд и новые команды, повышающие эффективность применения SQL.
Новые возможности
В Visual FoxPro 9.0 некоторые ограничения команды SELECT. SQL были отменены или дополнены. Эти изменения показаны в следующей таблице.
Количество объединений и подзапросов в команде SELECT
В Visual FoxPro 9.0 отменено ограничение общего числа предложений обьединения и подзапросов. В предыдущих версиях ограничение было равно девяти.
Количество предложений UNION в команде SELECT
В Visual FoxPro 9.0 отменено ограничение количества предложений UNION. В предыдущих версиях ограничение было равно девяти.
Количество таблиц, на которые ссылалась команда SELECT
В Visual FoxPro 9.0 отменено ограничение на количество таблиц (псевдонимов), вызваемых в SQL SELECT. Предыдущее ограничение было равно 30.
Количество параметров в предложении IN ()
Расширенные подзапросы
В Visual FoxPro 9.0 обеспечивает значительно более гибкие подзапросы. Например, теперь поддерживаются множественные подзапросы. Ниже рассматриваются расширения подзапросов в Visual FoxPro 9.0.
Составные подзапросы
Visual FoxPro 9.0 поддерживает множественное вложение подзапросов, с корреляцией, допускаемой непосредственным родителем. Ограничения по глубине вложения отсутствуют. В Visual FoxPro 8.0 исключение 1842 (SQL: превышен уровень вложенности подзапросов) генерировалось, когда имелось более одного уровня вложенности подзапроса.
Ниже показан общий синтаксис для множественных подзапросов:
SELECT … WHERE … (SELECT … WHERE … (SELECT …) …) …
Следующие запросы, выполнение которых приводило к возникновению исключения в Visual FoxPro 8.0, теперь выполняются правильно:
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 T1 WHERE EXISTS ;
(SELECT * from MyCursor1 T2 WHERE NOT EXISTS ;
(SELECT * FROM MyCursor2 T3))
*** Второй множественный подзапрос, вложенный в пример ***
SELECT * FROM table1 WHERE table1.iid IN ;
(SELECT table2.itable1id FROM table2 WHERE table2.iid IN ;
(SELECT table3.itable2id FROM table3 WHERE table3.cValue = «value»))
GROUP BY в соотнесенном подзапросе
Многие запросы могут быть оценены, выполняя подзапрос один раз и заменяя возникающим в результате значением или значениями в предложение WHERE внешнего запроса. В запросах, которые включают, соотнесенный подзапрос (также известный как повторяющийся подзапрос), подзапрос зависит от внешнего запроса для значений. Это означает, что подзапрос выполнен неоднократно, один раз для каждой строки, которая могла бы быть выбрана внешним запросом.
Visual FoxPro 8.0 не позволял использовать GROUP BY в соотнесенном подзапросе (генерировалось исключение 1828 (SQL: Использование GROUP BY в подзапросе запрещено). В Visual FoxPro 9.0 это ограничение отменего; теперь GROUP BY поддерживается для соотнесенных подзапросов, возвращающих больше чем одну запись.
Ниже показан общий синтаксис для предложения GROUP BY в соотнесенном подзапросе.
SELECT … WHERE … (SELECT … WHERE … GROUP BY …) …
Следующий пример генерировал исключение в Visual FoxPro 8.0; теперь, в Visual FoxPro 9.0, он выполняется правильно.
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)
Предложение TOP N в не-соотнесенном подзапросе
Visual FoxPro 9.0 поддерживает предложениеTOP N в не-соотнесенном подзапросе. Предложение ORDER BY должно присутствовать, если предложение TOP N используется, и это — единственный случай, допустимый в подзапросе.
Ниже показан общий синтаксис для предложения TOP N в не-соотнесенном подзапросе.
SELECT … WHERE … (SELECT TOP nExpr [PERCENT] … FROM … ORDER BY …) …
Следующий пример генерировал исключение в Visual FoxPro 8.0; теперь, в Visual FoxPro 9.0, он выполняется правильно.
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 WHERE field1 ;
IN (SELECT TOP 5 field2 FROM MyCursor2 order by field2)
Подзапросы в cписке полей команды SELECT
Visual FoxPro 9.0 позволяет указывать подзапрос как результирующее поле или часть выражения в проекции. Подзапрос в проекции должен удовлетворять тем же самым требованиям, как и подзапрос, используемый в операции сравнения. Если подзапрос не возвращает никакие записи, то возвращается значение NULL.
В Visual FoxPro 8.0, попытка использовать такую конструкцию вызывала исключение 1810 (SQL: Недопустимое использование подзапроса).
Ниже показан общий синтаксис для подзапроса в списке полей команды SELECT
SELECT … (SELECT …) … FROM …
Следующий пример генерировал исключение в Visual FoxPro 8.0; теперь, в Visual FoxPro 9.0, он выполняется правильно.
SELECT T1.field1, (SELECT field2 FROM MyCursor2 T2;
WHERE T2.field1=T1.field1) FROM MyCursor1 T1
Агрегатные функции в списке полей SELECT подзапроса
Следующий пример показывает использование агрегатной функции COUNT в списке SELECT подзапроса.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor (FIELD1 i)
INSERT INTO MyCursor VALUES (6)
INSERT INTO MyCursor VALUES (0)
INSERT INTO MyCursor VALUES (1)
INSERT INTO MyCursor VALUES (2)
INSERT INTO MyCursor VALUES (3)
INSERT INTO MyCursor VALUES (4)
INSERT INTO MyCursor VALUES (5)
INSERT INTO MyCursor VALUES (-1)
CREATE CURSOR MyCursor2 (FIELD2 i)
INSERT INTO MyCursor2 VALUES (1)
INSERT INTO MyCursor2 VALUES (2)
INSERT INTO MyCursor2 VALUES (2)
INSERT INTO MyCursor2 VALUES (3)
INSERT INTO MyCursor2 VALUES (3)
INSERT INTO MyCursor2 VALUES (3)
INSERT INTO MyCursor2 VALUES (4)
INSERT INTO MyCursor2 VALUES (4)
INSERT INTO MyCursor2 VALUES (4)
INSERT INTO MyCursor2 VALUES (4)
SELECT * FROM MYCURSOR WHERE field1 ;
локальное поле
-или-
Локальное поле соотнесённое поле
В Visual FoxPro 9.0 соотнесенные поля поддерживают сравнение с локальными выражениями (как показано в следующих форматах):
Соотнесенное поле локальное выражение
-или-
Локальное выражение соотнесённое поле
Локальное выражение должно использовать по крайней мере одно локальное поле и не может ссылаться на любое внешнее (соотнесенное) поле.
В следующем примере локальное выражение (Mycursor2. field2 / 2) сравнивается с соотнесённым полем (Mycursor. field1).
SELECT * FROM MyCursor ;
WHERE EXISTS(SELECT * FROM MyCursor2 ;
WHERE MyCursor2.field2 / 2 > MyCursor.field1)
Изменения для выражений по сравнению с подзапросами.
Подзапросы в предложении FROM
*** Следующий код генерирует ошибку ***
SELECT * FROM (SELECT TOP 5 field1 FROM MyCursor) ORDER BY field1
GROUP BY с именами полей в предложении UNION
Эффективное выполнение TOP N
В Visual FoxPro 8.0 и более ранних версиях при использовании в TOP N предложения [PERCENT] все записи сортируются и затем TOP N записей извлекается. В Visual FoxPro 9.0 эффективность выполнения была улучшена за счёт удаления записей, которые не удовлетворяют требованиям TOP N из процесса сортировки как можно раньше.
Оптимизация TOP N выполняется только тогда, когда если SET ENGINEBEHAVIOR установлена в 90.
Оптимизация требует, чтобы TOP N возвратила не больше, чем N записей (это не так для Visual FoxPro 8.0 и более ранних версий).
TOP N PERCENT не может быть оптимизирована, если весь результирующий набор записей не может быть считан в память сразу.
Улучшенная оптимизация для множественной таблицы с условием OR
Visual FoxPro 9.0 применяет улучшенную Rushmore оптимизацию при выборке из связанных по условию OR таблиц. Visual FoxPro оптимизирует условия фильтра для таблицы, пока обе стороны условия могут быть оптимизированы. Следующий пример показывает это:
CLEAR
CREATE CURSOR Test1 (f1 I)
FOR i=1 TO 20
INSERT INTO Test1 VALUES (I)
NEXT
INDEX ON f1 TAG f1
CREATE CURSOR Test2 (f2 I)
FOR i=1 TO 20
INSERT INTO Test2 VALUES (I)
NEXT
INDEX ON f2 TAG f2
SYS(3054,12)
SELECT * from Test1, Test2 WHERE (f1 IN (1,2,3) AND f2 IN (17,18,19)) OR ;
(f2 IN (1,2,3) AND f1 IN (17,18,19)) INTO CURSOR Result
SYS(3054,0)
В этом сценарии, таблица Test1 может быть оптимизирована Rushmore, используя следующее условие:
( F1 В (1,2,3) ИЛИ f1 В (17,18,19)) и таблица Test2 со следующими условиями:
( F2 В (17,18,19) ИЛИ f2 В (1,2,3))
Поддержка для локальных буферизированных данных
Время от времени может быть полезно использовать SELECT. SQL, чтобы выбрать записи из локального буферизированного курсора, в котором таблица не модифицировалась. Много раз при создании средств управления подобно сеткам, окнам списка, и комбинированным спискам необходимо рассмотреть недавно добавленные записи, которые еще не были сохранены на диске. В настоящее время команды SQL основаны на содержании, которое уже сохранено на диске.
Visual FoxPro 9.0 обеспечивает расширения языка, которые позволяют вам определять, основаны ли данные, возвращенные командой SELECT. SQL на буферизированных данных или данных, сохранённых на диске.
Команда SELECT. SQL теперь поддерживает предложение WITH. BUFFERING, которое позволяет вам определять, основаны ли извлекаемые данные на буферизированных данных или данных, уже сохранённых на диске. Для подробной информации, см. «Команда SELECT. SQL c предложением WITH».
Если Вы не включаете предложение BUFFERING, характер извлекаемых данных определяется установкой команды SET SQLBUFFERING. Для подробной информации, см. «Команда SET SQLBUFFERING».
Расширения других SQL команд
Следующие разделы описывают расширения, сделанные в командах INSERT. SQL, UPDATE. SQL и DELETE. SQL Visual FoxPro 9.0.
Предложение UNION в команде INSERT. SQL
В Visual FoxPro 9.0 предложение UNION теперь поддерживается в команде INSERT. SQL
Ниже показан общий синтаксис для предложения UNION.
INSERT INTO … SELECT … FROM … [UNION SELECT … [UNION …]]
Следующий пример показывает использование предложения UNION в INSERT. SQL.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor (field1 I,field2 I)
CREATE CURSOR MyCursor1 (field1 I,field2 I)
CREATE CURSOR MyCursor2 (field1 I,field2 I)
INSERT INTO MyCursor1 VALUES (1,1)
INSERT INTO MyCursor2 VALUES (2,2)
INSERT INTO MyCursor SELECT * FROM MyCursor1 UNION SELECT * FROM MyCursor2
SELECT MyCursor
LIST
Соотнесенная команда UPDATE. SQL
Visual FoxPro 9.0 теперь поддерживает соотнесенные модификации в команде UPDATE. SQL.
Если в команду UPDATE. SQL включено предложение FROM, то имя после ключевого слова UPDATE определяет адресата для операции модификации. Это может быть имя таблицы, псевдоним или имя файла. Для того, чтобы выбрать выходную таблицу, используется следующая логика:
Команда UPDATE. SQL в предложении FROM имеет тот же самый синтаксис, как предложение FROM в команде SELECT. SQL со следующими ограничениями:
Соотнесенная команда DELETE. SQL
Команда DELETE. SQL в предложении FROM имеет тот же самый синтаксис, как предложение FROM в команде SELECT. SQL со следующими ограничениями:
Обновляемые поля в команде UPDATE. SQL
Число полей, которые могут модифицироваться командой UPDATE. SQL, больше не ограничивается 128, как в предыдущих версиях Visual FoxPro. Новое ограничение — 255 полей, т.е. равно числу полей, доступных в таблице.
SET ENGINEBEHAVIOR
Команда SET ENGINEBEHAVIOR имеет в Visual FoxPro 9.0 новую опцию, 90, которая воздействует поведение команды SELECT. SQL для предложения TOP N и агрегатных функций. Для дополнительной информации см. «Команда SET ENGINEBEHAVIOR».
Преобразование типов данных
Преобразование между типами данных (например, преобразование между полями memo и character) в Visual FoxPro 9.0 было улучшено. Это усовершенствованное преобразование применяется в команде ALTER TABLE. SQL с опцией COLUMN также, как и структурные изменения, сделанные в Конструкторе Таблиц.
Особенности использования команды SELECT… SQL
При создании запросов и просмотров командами SQL SELECT вы должны учитывать следующие утверждения, соглашения, и ограничения.
Определяемые пользователем функции в командах SELECT. SQL
Вы можете определять выражения, содержащие определяемые пользователем функции для Select_Item в утверждениях SQL SELECT. Однако обратите внимание на следующие рекомендации и ограничения при использовании определяемых пользователем функций в утверждениях SQL SELECT:
Вне этих ограничений, определяемые пользователем функции приемлемы в командах SQL SELECT. Однако помните, что их использование в SQL SELECT способно резко уменьшить эффективность выполнения запроса.
Агрегатные функции
Вы можете использовать агрегатные функции с Select_Item, которые является полем или выражением, включающим поле, или внутри условия фильтра в предложении HAVING. Однако, Вы не можете вкладывать агрегатные функции одна в другую.
В следующай таблице перечислены агрегатные функции, которые вы можете использовать в команде SELECT. SQL.