Запросы

Запросы позволяют объединять различные источники данных, легко фильтровать записи, манипулировать данными и сортировать их ѕ и все это с помощью конструкции SELECT - SQL.

SELECT - это команда языка SQL, встроенная в Visual FoxPro и работающая как любая другая команда Visual FoxPro. Когда вы представляете запрос с помощью SELECT, Visual FoxPro интерпретирует этот запрос и извлекает из таблиц указанную информацию. Запрос SELECT можно создать в следующих средах:

· В окне команд

· В программе Visual FoxPro (в виде обычной команды Visual FoxPro)

· В конструкторе запросов

Если вы выдали SET TALK ON и выполнили команду SELECT, Visual FoxPro отображает величину интервала времени, затраченного на выполнение запроса, и количество записей в полученных результатах. Переменная _TALLY содержит число записей в результатах запроса.

С помощью предложений конструкции SELECT можно определить место сохранения результата запроса.

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

Отдельную таблицу INTO TABLE mytable

Массив INTO ARRAY aMyArray

Временную таблицу INTO CURSOR mycursor

Активное окно TO SCREEN

Окно просмотра Направляется автоматически, если не.

указано ничего иного

Команда SELECT – SQL

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

Синтаксис

SELECT [ALL | DISTINCT]

[Alias.] Select_Item [AS Column_Name]

[, [Alias.] Select_Item [AS Column_Name] ...]

FROM [DatabaseName!]Table [Local_Alias]

[, [DatabaseName!]Table [Local_Alias] ...]

[[INTO Destination]

| [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]]

[PREFERENCE PreferenceName]

[NOCONSOLE]

[PLAIN]

[NOWAIT]

[WHERE JoinCondition [AND JoinCondition ...]

[AND | OR FilterCondition [AND | OR FilterCondition ...]]]

[GROUP BY GroupColumn [, GroupColumn ...]]

[HAVING FilterCondition]

[UNION [ALL] SELECTCommand]

[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]

[[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN

DatabaseName!]Table [Local_Alias]

[ON JoinCondition …]

Параметры

[ALL | DISTINCT]

[Alias.] Select_Item [AS Column_Name]

[, [Alias.] Select_Item [AS Column_Name] ...]

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

Опция ALL по умолчанию отображает в результатах запроса все строки.

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

Замечание Опцию DISTINCT можно использовать в предложении SELECT только один раз.

Префикс Alias. уточняет имена элементов. Каждый элемент Select_Item генерирует один столбец результатов запроса. Если несколько элементов имеют одинаковые имена, укажите перед именем элемента псевдоним таблицы с точкой, чтобы столбцы не повторялись.

Select_Item задает элемент, который следует включить в столбец результатов запроса. Эти выбираемые элементы могут быть следующими:

· Имя поля из таблицы, указанной в предложении FROM.

· Константа, которая задает одинаковое постоянное значение во всех строках результатов запроса.

· Выражение, которое может быть именем пользовательской функции.

Пользовательские функции в предложении SELECT

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

· Скорость выполнения операций с помощью SELECT может быть ограничена скоростью выполнения этих пользовательских функций.

· В пользовательских функциях, выдаваемых в предложении SELECT, ничего неизвестно о структуре ввода-вывода или табличной среде Visual FoxPro. В общем случае вы не знаете ни номера выбранной рабочей области, ни имени текущей таблицы, ни даже имен обрабатываемых полей. Значения этих переменных зависят от того, в какой именно момент в процессе оптимизации инициирована пользовательская функция.

· Довольно рискованно изменять среду ввода-вывода или таблиц Visual FoxPro в пользовательских функциях, инициируемых в предложении SELECT. В общем случае результаты будут непредсказуемыми.

· Единственный надежный метод передачи значений пользовательским функциям, инициируемым в предложении SELECT, - передача по списку аргументов в момент инициирования функции.

Не считая этих ограничений, пользовательские функции вполне допустимы в предложении SELECT. Однако не забывайте, что применение SELECT может снизить производительность.

Для выбираемого элемента SELECT, являющегося полем или выражением с участием поля, могут быть использованы следующие функции полей:

· AVG(Select_Item), вычисляющая среднее в столбце числовых данных.

· COUNT(Select_Item), подсчитывающая число выбираемых элементов в столбце. COUNT(*) подсчитывает число строк в выводе запроса.

· MIN(Select_Item), определяющая наименьшее значение среди выбираемых элементов Select_Item в столбце.

· MAX(Select_Item), определяющее наибольшее значение Select_Item в столбце.

· SUM(Select_Item), вычисляющая итоговую сумму столбца числовых данных.

Функции полей не могут быть вложенными.

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

FROM [DatabaseName!]Table [Local_Alias]

[, [DatabaseName!]Table [Local_Alias] ...] - задает список таблиц, которые содержат данные, извлекаемые по данному запросу. Если ни одна из таблиц не открыта, Visual FoxPro отображает диалоговое окно Open, в котором можно задать местоположение файла. Если вы открыли таблицу, она останется открытой до завершения запроса.

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

Local_Alias задает временное имя для таблицы Table. Если этот локальный псевдоним задан, его нужно будет использовать вместо имени таблицы на протяжении всего оператора SELECT.

INTO Destination - указывает, где следует сохранить результаты запроса. Если задать в одном запросе и предложение INTO, и предложение TO, последнее будет проигнорировано. Если предложение INTO опущено, результаты запроса отображаются в окне просмотра. Вы также можете использовать предложение TO, позволяющее направлять результаты запроса на принтер или в файл.

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

· ARRAY ArrayName, сохраняющее результаты запроса в массиве переменных памяти. Если в запросе выбирается 0 записей, этот массив не создается.

· CURSOR CursorName, сохраняющее результаты запроса в курсоре. Если вы задали имя открытой таблицы и установили значение SET SAFETY равным OFF, Visual FoxPro сгенерирует сообщение об ошибке. После того как SELECT выполнится, временный курсор останется открытым и активным, но будет доступен только для чтения. Когда вы закроете этот временный курсор, он будет удален. Курсоры могут создаваться в виде временных файлов на диске, заданном SORTWORK.

· DBF TableName | TABLE TableName, сохраняющее результаты запроса в таблице. Если вы задали уже открытую таблицу, когда действует SET SAFETY OFF, Visual FoxPro без предупреждения запишет результаты поверх содержимого таблицы. Если вы не задали расширение имени, Visual FoxPro присвоит таблице расширение .DBF. Таблица останется открытой и активной после того, как SELECT выполнится.

TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN

Используя предложение TO вместо предложения INTO, можно направить результаты запроса в текстовый ASCII-файл FileName, на принтер или в основное окно Visual FoxPro.

Опция ADDITIVE добавляет вывод запроса к существующему содержимому текстового файла, заданного в предложении TO FILE FileName.

Опция TO PRINTER направляет вывод запроса на принтер.

Опция TO SCREEN направляет вывод запроса в основное окно Visual FoxPro или в активное пользовательское окно.

Когда вы создаете вывод запроса, имена столбцов формируются в соответствии со следующими правилами:

· Если выбираемый элемент является полем с уникальным именем, столбец вывода получает имя этого поля.

· Если несколько выбираемых элементов обладают одним и тем же именем, происходит следующее. Например, если в таблице Customer есть поле STREET и в таблице Employees тоже есть поле с таким именем, то столбцы вывода получают имена Extension_A и Extension_B (STREET_A и STREET_B). Если имя выбираемого элемента состоит из 10 символов, оно укорачивается таким образом, чтобы можно было добавить символ подчеркивания и букву. Например, DEPARTMENT превратится в DEPARTME_A.

· Если выбираемый элемент является выражением, соответствующий ему столбец вывода получит имя EXP_A. Если существуют другие выражения, им назначаются имена EXP_B, EXP_C и т.д.

· Если выбираемый элемент содержит функцию поля, такую как COUNT( ), столбец вывода получает имя CNT_A. Если какой-либо выбираемый элемент содержит SUM( ), его столбец в выводе получает имя SUM_B.

PREFERENCE PreferenceName

Если результаты запроса направляются в окно просмотра, предложение PREFERENCE позволяет сохранить атрибуты и опции окна просмотра для использования впоследствии. Это предложение сохраняет атрибуты (настройки) в файле ресурсов FOXUSER на неопределенный период времени. Настройки можно извлечь в любой момент.

Когда SELECT впервые выдается с предложением PREFERENCE PreferenceName, создается набор настроек. Если затем вновь выдать SELECT с этим именем набора настроек, окно просмотра будет восстановлено в том же виде. При закрытии окна просмотра набор настроек обновляется.

Если выйти из окна просмотра, нажав клавиши Ctrl+Q, изменения, внесенные в окно просмотра, не сохранятся в файле ресурсов.

NOCONSOLE - запрещает отображение результатов запроса, направленных в файл, на принтер или в основное окно Visual FoxPro.

PLAIN - запрещает отображение заголовков столбцов в выводе запроса. Предложение PLAIN можно использовать независимо от того, задано предложение TO или нет. Если включить предложение INTO, предложение PLAIN игнорируется.

NOWAIT- продолжает выполнение программы после того, как окно просмотра откроется и в него будут направлены результаты запроса. Программа не ждет закрытия окна просмотра, а продолжает выполняться со строки, непосредственно следующей за оператором SELECT.

Если вы включили предложение TO SCREEN, чтобы направить вывод в основное окно Visual FoxPro или в пользовательское окно, то когда это окно заполнится результатами запроса, вывод приостановится. Чтобы посмотреть следующую группу результатов запроса, нажмите любую клавишу.

Если задано NOWAIT, результаты запроса будут прокручиваться в основном окне Visual FoxPro или в пользовательском окне непрерывно, без пауз. Предложение NOWAIT игнорируется, если оно включено вместе с предложением INTO.

WHERE JoinCondition [AND JoinCondition ...]

[AND | OR FilterCondition [AND | OR FilterCondition ...]] - указывает, что Visual FoxPro включает в результаты запроса только некоторые записи. Предложение WHERE требуется при извлечении данных из нескольких таблиц.

Условие JoinCondition задает поля, которые связывают таблицы, указанные в предложении FROM. Если в запрос включено более одной таблицы, то для каждой таблицы, кроме первой, необходимо задать условие объединения.

При создании условия объединения необходимо учитывать следующие обстоятельства:

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

· С осторожностью пользуйтесь в условиях объединения такими функциями, как DELETED( ), EOF( ), FOUND( ), RECCOUNT( ) и RECNO( ), поддерживающими необязательный аргумент в виде псевдонима или номера рабочей области

· Будьте внимательны, объединяя таблицы с пустыми полями, поскольку в Visual FoxPro такие поля соответствуют друг другу. Чтобы связать несколько условий объединения, воспользуйтесь оператором AND (И). Каждое условие объединения имеет следующий формат:

FieldName1 Comparison FieldName2

FieldName1 - это имя поля из одной таблицы, FieldName2 - имя поля из другой таблицы, а Comparison представляет один из следующих операторов.

Оператор Тип сравнения

= Равно

== В точности равно

LIKE SQL LIKE (похоже)

< >, !=, # Не равно

> Больше

> = Больше или равно

< Меньше

< = Меньше или равно

Последняя группа строк оператора позволяет объединять таблицы только в версии Visual FoxPro 5, 6, 7.

FilterCondition задает критерий, которому должны удовлетворять записи, чтобы быть включенными в результаты запроса. В запросе можно задать сколько угодно условий фильтра, связывая их операторами AND (И) и OR (ИЛИ). Также можно использовать оператор NOT (НЕ), изменяющий значение логического выражения на противоположное, и функцию EMPTY( ), проверяющую наличие пустых полей.

GROUP BY GroupColumn [, GroupColumn ...] - группирует строки в запросе исходя из значений в одном или нескольких столбцах.

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

HAVING FilterCondition - задает условие фильтра, которому должны удовлетворять группы, чтобы быть включенными в результаты запроса. Предложение HAVING следует использовать вместе с GROUP BY.

Оно может включать сколько угодно условий фильтра, связанных операторами AND (И) и OR (ИЛИ). Также можно использовать оператор NOT (НЕ), изменяющий значение логического выражения на противоположное.

Условие FilterCondition не может содержать подзапрос.

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

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

Если задана опция ALL, предложение UNION не будет удалять из комбинированных результатов повторяющиеся строки.

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

· С помощью UNION нельзя комбинировать подзапросы.

· У обеих команд SELECT в выводе запросов должно быть одинаковое число столбцов.

· Каждый столбец результатов запроса одной команды SELECT должен иметь тот же тип данных и ту же ширину, что и соответствующий столбец другого запроса SELECT.

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

Предложение UNION также можно использовать для моделирования внешнего объединения (outer join).

Когда вы объединяете две таблицы в одном запросе, в вывод включаются только записи с совпадающими значениями в объединяемых полях. Если у некоторой записи родительской таблицы нет соответствующей ей записи в дочерней таблице, эта родительская запись не включается в вывод.

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

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

· Поле в таблице, указанной в предложении FROM, которое также является выбираемым элементом в основном предложении SELECT (не в подзапросе).

· Числовое выражение, указывающее местоположение столбца в таблице результатов. (Самый левый столбец имеет номер 1.)

Опция ASC задает возрастающий порядок сортировки результатов запроса, в соответствии с элементом (или элементами) Order_Item, и используется по умолчанию в предложении ORDER BY.

Опция DESC задает убывающий порядок сортировки результатов запроса.

Если вы не задали порядок результатов запроса с помощью предложения ORDER BY, они останутся неупорядоченными.

Подзапрос, о котором идет речь в описании некоторых аргументов, - это команда SELECT внутри другой команды SELECT; она должна быть заключена в скобки. В предложении WHERE можно формировать несколько подзапросов на одном и том же уровне, то есть не вложенных друг в друга (см. описание предложения WHERE). Подзапросы могут содержать несколько условий объединения.

Команда CREATE SQL VIEW

Отображает конструктор представлений, давая возможность создать представление SQL.

Синтаксис

CREATE SQL VIEW [ViewName ] [REMOTE]

[AS SQLSELECTStatement]

Параметры

ViewName - задает имя представления, которое предстоит создать.

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

AS SQLSELECTStatement - задает определение представления. В качестве SQLSELECTStatement должен использоваться допустимый оператор SQL SELECT, который не следует заключать в кавычки. Если описывается локальное представление, можно задавать представления и таблицы баз данных, отличных от текущей, предваряя имя представления или таблицы именем базы данных с восклицательным знаком (!).

Прежде чем вы сможете создать представление SQL, необходимо открыть базу данных в монопольном режиме. Используйте команду OPEN DATABASE с ключевым словом EXCLUSIVE для открытия существующей базы данных в монопольном режиме или команду CREATE DATABASE для создания новой базы данных.

Команда DELETE VIEW

Удаляет представление SQL из текущей базы данных.

Синтаксис

DELETE VIEW ViewName

Параметры

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

Команда DELETE VIEW требует эксклюзивного использования базы данных.

Команда DISPLAY VIEWS

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

Синтаксис

DISPLAY VIEWS

[TO PRINTER [PROMPT] | TO FILE FileName] [NOCONSOLE]

Параметры

TO PRINTER [PROMPT] - направляет информацию, возвращаемую командой DISPLAY VIEWS, на принтер.

Можно использовать предложение PROMPT, чтобы перед началом печати отображать диалоговое окно Print. Ключевое слово PROMPT нужно указывать сразу после TO PRINTER.

TO FILE FileName - направляет вывод команды DISPLAY VIEWS в файл диска, заданный именем FileName. Если такой файл уже существует и команда SET SAFETY установила значение ON, Visual FoxPro выдаст приглашение с вопросом, следует ли вести запись поверх этого файла.

NOCONSOLE - подавляет вывод в главное окно Visual FoxPro или в активное пользовательское окно.

Команда MODIFY VIEW

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

Синтаксис

MODIFY VIEW ViewName [REMOTE]

Параметры

ViewName - задает имя представления, которое следует модифицировать.

REMOTE - указывает, что данное представление является удаленным и использует удаленные таблицы. Если опустить REMOTE, можно модифицировать представление с помощью локальных таблиц.

Команда RENAME VIEW

Переименовывает представление текущей SQL базы данных.

Синтаксис

RENAME VIEW ViewName1 TO ViewName2

Параметры

ViewName1 – задает имя представления SQL, которое требуется переименовать.

ViewName2 – задает новое имя представления SQL.

Чтобы представление SQL можно было переименовать, содержащая его база данных должна быть открыта в монопольном режиме и должна быть текущей.