Выражения и предикаты в SQL

Язык SQL FoxPro 5.0

СУБД FoxPro, за многие годы своей эволюции, накопила колоссальное количество стандартных операций, функций и условий (предикатов), с которыми программист знакомится по мере освоения системы. В принципе, все эти возможности можно использовать во вложенном SQL (при соблюдении некоторых мер осторожности, что особенно относится к использованию пользовательских функций).

 

С другой стороны, SQL образует вполне изолированный подъязык FoxPro, отличный от него по духу и синтаксису: в отличие от "базового" FoxPro, SQL является декларативным языком преобразований таблиц в целом, а не процедурным языком обработки их записей; синтаксис команд SQL замкнут и не содержит ссылок на текущее состояние окружение, столь многочисленных в командах FoxPro.

 

Щадя читателя и следуя принципу "разумной достаточности", мы далее немного упрощаем синтаксис - в основном, за счет исключения синонимии и редко используемых опций. Наиболее полный синтаксис FoxPro SQL, как всегда, можно найти в справочной системе FoxPro, на которую мы в дальнейшем будем ссылаться как на “документацию”. Расширения языка, отсутствующие в стандарте (и, возможно - в используемой читателем версии) SQL отмечены символом *

 

Очевидно, что, если программист использует SQL FoxPro "по прямому назначению" - именно, в качестве средства связи с другими СУБД в рамках технологии "клиент-сервер", то он должен отказаться от многочисленных дополнительных возможностей FoxPro и обязан использовать исключительно описываем ниже набор функций, предикатов и команд стандартного SQL.

 

SQL FoxPro образуют замкнутый язык СУБД, достаточный для выполнения основных задач обработки локальных данных, а также обработки удаленных данных в рамках технологии удаленных представлений. Главное достоинство последней - простота; от программиста скрыты все детали реализации обработки данных на удаленном источнике (сервере) в терминах его собственной версии SQL.

 

Поддерживаемый также FoxPro аппарат "сквозного SQL" , или технологии SPT (SQL-pass-through) более эффективен, но и более изощрен - кроме знания достаточно обширного дополнительного набора функций SQL, он предполагает и знание версии SQL, "работающей" на сервере. Любознательный читатель, как всегда, может найти отсутствующую в настоящем пособии информацию о “сквозном SQL”в справочной системе FoxPro.

 

Выражения и предикаты в SQL.

 

Итак, в рамках данного пособия достаточно знания лишь "классического набора” выражений SQL, а именно - ариметических выражений и предикатов следующего вида:

 

а) Сравнения

 

Аргумент1 Знак_сравнения Аргумент2

 

где знак сравнения - один из символов отношений = (равенство), <>, !=, # (неравенство) и >, >=,<,<=, а также, для символьных строк, == (точное равенство) и [NOT] LIKE (сравнение по маске),

 

Заметьте, что, если для числовых типов и типа "дата" такие сравнения имеют обычный смысл, то для символьных строк имеется ввиду лексикографическое (словарное) сравнение, а равенство = несимметрично и понимается в FoxPro как вхождение второго аргумента в первый как начального подслова (см. документацию о зависимости такого сравнения от установок системных переменных ANSI и EXACT).

 

Предикат сравнения по маске имеет вид

 

Аргумент [NOT] LIKE Маска

 

где Маска - произвольная символьная строка, (возможно) содержащая специальные символы кратной замены % и одиночной замены _. Предикат [NOT] LIKE считается истинным, если маску можно превратить в стоящий слева аргумент подстановкой некоторых слов вместо символа % и одиночных символов - вместо символов _.

 

б) Булевские формулы, как обычно, образуются с помощью операций конъюнкции AND, дизъюнкции OR и отрицания NOT

 

в) "Синтаксический сахар" - группа предикатов, выразимых через булевские комбинации сравнений, но более кратких и привычных по форме (для англоязычного пользователя)

 

Аргумент1 [NOT] BETWEEN Аргумент2 AND Аргумент3

- краткая форма записи предиката

[NOT] (Аргумент2 £Аргумент1 AND Аргумент1£Аргумент3)

 

Аргумент [NOT] IN (список_значений)

- краткая форма записи предиката

[NOT] (Аргумент=Аргумент1 OR Аргумент£Аргумент2 OR … Аргумент=АргументN)

 

 

г) Предикаты, использующие выборку

 

Имя_поля Знак_сравнения ALL (команда SELECT)

 

истинно, если сравнение выполняется для стоящего слева значения поля и всех значений, выбранных командой SELECT - при этом предполагается, что последняя выдает список значений - таблицу с единственным полем, совместиммым по типу со значением поля, стояшим слева. Условие считается истинным также, если подвыборка пуста.

 

Имя_поля Знак_сравнения ANY | SOME (команда SELECT)

 

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

 

[NOT] EXISTS (команда_SELECT)

 

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

 

Имя_поля [NOT] IN (команда_SELECT)

 

имеет тот же смысл, что и одноименный предикат IN, рассмотренный выше, с той лишь разницей, что список значений не задается явно, но является результатом подвыборки; истинен, если значение поля совпадает хотя бы с одним значением из (единственного поля) результата выборки.

 

 

СОЗДАНИЕ БД

 

CREATE TABLE Имя_создаваемой_таблицы [NAME* Длинное - до 128 символов - имя_таблицы] [FREE*]

[(ОПИСАНИЕ ПОЛЯ 1 [ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ 1)],

[(ОПИСАНИЕ ПОЛЯ 2] [ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ 2])],

…..

[(ОПИСАНИЕ ПОЛЯ n] [ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ n])],

[ПРАВИЛА КОРРЕКТНОСТИ ЗАПИСИ]

 

(опция FREE - свободная таблица - используется в том случае, если программист не хочет включать создаваемую таблицу ни в одну из существующих БД; в противном случае, по умолчанию, таблица будет включена в БД, открытую на момент исполнения команды CREATE TABLE)

 

где

 

ОПИСАНИЕ ПОЛЯ -

Имя_поля

Тип_поля

[(Ширина_поля [,Точность (число знаков после запятой - для числовых типов])]

 

далее перечисляются допустимые в FoxPro скалярные типы, с указанием имени типа и допустимости для соответствующего типа, опций указания ширины поля (Ш) и точности (Т).

 

Тип Ш Т Комментарий

C n - Character - cимвольная строка некоторой длины n

D - - Date - Дата

T - - dateTime - Дата-время

N n d Numeric - вешественное длины n , d знаков после

запятой

F n d Floating numeric - вещественное длины n, d знаков

после запятой, в форме с плавающей точкой

I - - Integer - целое

B - d douBle - целое двойной точности

Y - - currencY - денежная сумма

L - - Logical - логический

M - - Memo - строка неопределенной длины

G - - General - ссылка на внешний объект

 

Замечание. Скалярные типы данных могут различаться как по синтаксису, так и по семантике в различных версиях SQL.

 

ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ - одна или несколько опций вида

 

[NULL | NOT NULL] - значением поля может (не может) быть неопределенное значение NULL; по умолчанию, значение опции определяется значением системной (SET-)переменной NULL; также по умолчанию, значение NULL не допустимо для первичных ключей и уникальных (UNIQUE) полей;

 

[CHECK lУсловие корректности поля [ERROR* Текст сообщения о нарушении условия]] - проверяется при каждой модификации, а также при добавлении "пустой" записи; сообщение об ошибке появляется лишь при работе в интерактивном режиме

 

 

[DEFAULT Значение поля по умолчанию]

 

[PRIMARY KEY | UNIQUE] - значение поля является (единственным) первичным ключом записи | должно быть уникальным для каждой записи таблицы; для поддержки соответствующего правила создается индекс с именем, совпадающим с именем поля:

 

[REFERENCES Имя_родительской_таблицы [TAG Имя (тег) индекса]] - значение поля является внешним ключом указанной родительской таблицы [по данному индексу]

 

ПРАВИЛА КОРРЕКТНОСТИ ЗАПИСИ - список из одной или несколько опций вида

 

[PRIMARY KEY Выражение TAG* Имя (тег) индекса |, UNIQUE Выражение TAG* Имя индекса] - указанное выражение определяет (единственный составной) первичный ключ записи | обязано быть уникальным для каждой записи; для поддержки соответствующего правила создается индекс с указанным именем (тегом);

 

[, FOREIGN KEY Выражение TAG* Имя_индекса

REFERENCES Имя_родительской таблицы [TAG* Имя индекса родительской таблицы]] - указанное выражение определяет (составной) внешний ключ записи, поддерживаемым родительским индексом с указанным тегом

 

[, CHECK Условие корректности записи [ERROR* Текст сообщения о нарушении условия]]) - проверяется при каждой модификации, а также при добавлении "пустой" записи; сообщение об ошибке появляется лишь при работе в интерактивном режиме

 

МОДИФИКАЦИЯ СТРУКТУРЫ БД

 

УДАЛЕНИЕ ТАБЛИЦЫ

 

DROP TABLE Имя_таблицы | Имя_файла | ? [RECYCLE]

удаляет таблицу из текущей БД; опция ? выдает диалоговое окно выбора таблицы; подопция RECYCLE указывает на то, что удаленную таблицу нужно поместить в "корзину" ОС Windows c возможностью последующего восстановления, в противном случае восстановление невозможно.

 

УДАЛЕНИЕ ПРЕДСТАВЛЕНИЯ

 

DROP VIEW имя_представления

 

МОДИФИКАЦИЯ СТРУКТУРЫ ТАБЛИЦЫ

 

ALTER TABLE Имя_таблицы

ADD | ALTER [COLUMN] Имя_поля

Тип_поля [(Ширина_поля [, Точность])]

[NULL | NOT NULL]

[CHECK Правило_корректности_поля

[ERROR Текст_сообщения_о_нарушении_правила]]

[DEFAULT Значение_по_умолчанию]

[PRIMARY KEY | UNIQUE]

[REFERENCES Имя_родительской_таблицы

[TAG Тег(имя)_индекса]]

 

или

 

ALTER TABLE Имя_таблицы

ALTER [COLUMN] Имя_поля

[NULL | NOT NULL]

[SET DEFAULT Значение_по_умолчанию]

[SET CHECK Правило_корректности_поля

[ERROR Текст_сообщения_о_нарушении_правила]]

[DROP DEFAULT]

[DROP CHECK]

или

 

ALTER TABLE Имя_таблицы

[DROP [COLUMN] Имя_поля]

[SET CHECK Правило_корректности_поля

[ERROR Текст_сообщения_о_нарушении_правила]]

[DROP CHECK]

[ADD PRIMARY KEY Выражение_первичного_ключа

TAG Тег(имя)_индекса_первичного_ключа]

[DROP PRIMARY KEY]

[ADD UNIQUE Выражение [TAG Тег_индекса]]

[DROP UNIQUE TAG Тег_индекса]

[ADD FOREIGN KEY [Выражение_внешнего_ключа]

TAG Тег_индекса

REFERENCES Имя_родительской_таблицы

[TAG Тег_родительского_индекса]]

[DROP FOREIGN KEY TAG Тег_индекса]

[RENAME COLUMN Старое_имя_поля TO Новое_имя_поля]

 

Несмотря на устрашающий синтаксис, семантика команды ALTER TABLE легко выводится из пояснений к команде CREATE TABLE и значений английских слов ALTER - изменить, ADD - добавить, DROP - удалить, SET - положить равным, RENAME - переименовать.

 

Замечание. Пожалуй, единственным существенным упущением текущей версии FoxPro по сравнению с другими реализациями SQL является отсутствие команды создания пользовательского индекса Create Index (и, соответственно команды удаления индекса Drop Index). Правда, это упущение – чисто синтаксическое, поскольку имеется достаточно мощная команда-аналог самого FoxPro – а именно, команда Index (см.документацию).

 

МОДИФИКАЦИЯ СОДЕРЖИМОГО ТАБЛИЦ

 

Добавление записи.

 

INSERT INTO имя_таблицы [(список_имен_полей])]

VALUES (список_выражений)

 

где

имя_таблицы - имя dbf-файла (возможно - с указанием пути) или алиас рабочей области; может быть задано строковым выражением;

если список имен полей задан, то он, очевидно, должен быть согласован по типу со списком выражений, задающим значения соответствующих полей в добавляемой записи; если список имен полей отсутствует, то, по умолчанию, подразумевается список имен всех полей таблицы. В любом случае, необъявленные значения полей либо берутся равным значению по умолчанию (см. опцию DEFAULT в команде CREATE TABLE), либо - равными NULL (если системная SET-переменная NULL имеет значение ON)

 

Замечания.

1) Текущая версия FoxPro не содержит второй стандартной формы команды - INSERT INTO имя_таблицы (команда SELECT), которая, впрочем легко реализуется - например с помощью цикла SCAN прохода по соответствующему команде выборки представлению или курсору (см. далее)

2) С другой стороны, FoxPro содержит свои собственные, достаточно многочисленные и часто - изощренные способы генерации записей - см., например APPEND, INSERT в справочной системе FoxPro.

 

 

Редактирование записей

 

UPDATE [Имя_базы_данных!]Имя_таблицы

SET Список выражений вида Имя_поля=Выражение

[WHERE Условие_обновления]

 

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

 

 

Логическое удаление записей

 

DELETE FROM [Имя_базы_данных!]Имя_таблицы

[WHERE Условие_удаления]

 

помечает записи [,удовлетворяющие заданному условию,] как удаленные.

 

Замечание. Как обычно в СУБД, логическое удаление записей не означает их фактического, т.е. физического удаления - производимого в FoxPro командой упаковки таблицы PACK; до выполнения последней помеченные как удаленные записи могут быть восстановлены командой RECALL. Помеченные, но фактически не удаленные записи могут включаться или не включаться в последующую обработку в зависимости от значения системной SET-переменной DELETED.