Если внимательно посмотреть на текст запроса, то в конце выражения можно увидеть предложение GROUP BY. В стандартном SQL вам просто необходимо группировать запрос по всем выбираемым полям, либо использовать поля как выражения функций агрегирования. Совершенно аналогичные ограничения накладываются на запросы в Microsoft Access. В Visual FoxPro и Microsoft SQL Server таких ограничений нет, и вам не обязательно группировать данные по всем выводимым полям.

При создании запросов перед вами обязательно возникнет необходимость отфильтровать данные по какому-нибудь наименованию. Например, по модели M3 3.0. В принципе, вы можете использовать следующую строку, сформированную с помощью предложения WHERE:

WHERE model.name model = "M3 3.0"

Но это будет не совсем правильно с точки зрения стандарта SQL. Правильно построенный запрос будет выглядеть следующим образом:

SELECT DISTINCTROW Sum(account.sum) AS Сумм, model.name model

FROM model INNER JOIN (account INNER JOIN [automobile passenger car] ON account.key auto = [automobile passenger car].key auto) ON model.key model = [automobile passenger car].key model GROUP BY model.name model HAVING model.name model = "M3 3.0"

Результат этого запроса будет выглядеть следующим образом:

Сумма Наименование

37000 M3 3.0

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

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

Подзапросы присоединяются к основному запросу через операторы IN, EXIST, SOME, ANY, ALL.

Рассмотрим пример использования оператора IN. Допустим, у нас есть две таблицы с одинаковой структурой. Необходимо вывести данные из первой таблицы при условии, что по полю kto у выводимых записей нет совпадающих значений.

SELECT kto, skolko FROM first WHERE kto NOT IN ; (SELECT DISTINCT kto FROM second)

Пример использования оператора EXIST. Оператор EXIST - единственный из операторов, не требующий выражения между ключевым словом WHERE и самим собой. Он возвращает истину в зависимости от того, есть ли хоть одна запись в выборке подзапроса. Рассмотрим два решения одной задачи. Нам необходимо выбрать все модели автомобилей, которые стоят больше 25000 и которые мы ухитрились продать хотя бы один раз. Код записан в синтаксисе FoxPro.

SELECT DISTINCT Model.name model,; Automobile passenger car.cost; FROM "auto store!model",;

"auto store!automobile passenger car" ; Automobile passenger car ; WHERE Model.key model = ; Automobile passenger car.key model AND ; Automobile passenger car.cost >>=25000 AND Exist ; (SELECT * FROM account,;

"auto store!automobile passenger car" Automobile passenger car ; WHERE account.key auto = ; Automobile passenger car.key auto) SELECT DISTINCT Model.name model, ; Automobile passenger car.cost ;

FROM "auto store!model",;

"auto store!automobile passenger car" ; Automobile passenger car; WHERE Model.key model = Automobile passenger car.key model AND ; Automobile passenger car.cost >>=25000 ; And Automobile passenger car.key auto NOT IN;

(SELECT DISTINCT account.key auto FROM account)

Возможно, вы найдете еще более короткое решение данной задачи, не корите нас, так как мы думаем еще и об учебных целях приводимых примеров. В первом решении с помощью оператора EXIST мы просто проверяем таблицу Account на наличие записей во внутреннем подзапросе. Во втором решении нас интересует просто список значений, которые у нас имеются, после выполнения внутреннего запроса по полю key auto.

Второе решение дает правильный ответ, а первое неправильный. Почему? Обратимся к теории. Внутренний запрос выполняется только один раз, и внешний запрос при своей работе обращается только к его итогу, раз за разом проходя по всем записям. Естественно, что оператор EXIST здесь оказывается совершенно бесполезным. Есть ли выход из положения? Безусловно. Согласно теории, если мы свяжем внешний и внутренний запрос, получив при этом связанный подзапрос, то вынудим внешний запрос обращаться к внутреннему каждый раз во время обработки записей при решении, выводить ли выбранные поля в итоговый курсор. Поэтому мы выводим во внешнем запросе еще одно поле Automobile passenger car.key auto и связываем внешний и внутренний запрос по этому полю. Теперь правильный запрос с использованием оператора EXIST выглядит так:

SELECT Model.name model, ; Automobile passenger car.cost, ; Automobile passenger car.key auto;

FROM "auto store!model",; "auto store!automobile passenger car" Automobile passenger car; WHERE Model.key model = Automobile passenger car.key model AND ;

Automobile passenger car.cost >>=25000 And Exist ;

(SELECT * FROM account WHERE ;

Automobile passenger car. key auto=account.key auto)

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

Пример использования операторов ANY и SOME. Использование операторов ANY и SOME приводит к совершенно одинаковым результатам. В качестве примера решим предыдущую задачу:

SELECT Model.name model, Automobile passenger car.cost;

FROM "auto store!model",;

"auto store!automobile passenger car" Automobile passenger car; WHERE Model.key model = Automobile passenger car.key model AND ; Automobile passenger car.cost >>=25000 And Automobile passenger car.key auto= ; ANY (SELECT DISTINCT account.key auto FROM account);

Пример использования операторов ALL. Оператор ALL используется с операторами сравнения >> и << и подзапросом. Оператор ALL с оператором >> перед ним означает, что сравниваемое значение должно быть больше любого значения в списке значений, полученных в подзапросе. В противоположном случае - соответственно меньше любого значения в списке.

SELECT Model.name model, Automobile passenger car.cost;

FROM "auto store!model",;

"auto store!automobile passenger car" Automobile passenger car ; WHERE Model.key model = Automobile passenger car.key model and ; Automobile passenger car.cost >>=ALL ; (SELECT DISTINCT summa FROM account ;

WHERE summa>>=30000) Запросы добавления

Не стоит особо пропагандировать необходимость добавления записей в таблицы в системах обработки данных. Они ведь и становятся настоящими таблицами, когда в них появляются записи. За этот процесс в языке SQL отвечает команда INSERT. Она имеет два варианта использования. В первом случае вы добавляете одну запись с конкретными данными в конкретные поля. Во втором случае вы можете добавить одну и более записей, набор которых формируется запросом. Необходимо отметить, что второй вариант синтаксиса не реализован в версии Visual FoxPro 3.0. Тем не менее его легко реализовать в два шага. Рассмотрим конкретные примеры.

В таблицу Account добавим одну запись:

INSERT INTO account VALUES (106,6,100007,{12.07.96},.T.,26000)

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

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

INSERT INTO Account (account,key customer,key auto,date write)

VALUES (106,6,100007,{12.07.96})

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

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

INSERT INTO mytable ( kto, skolko )

SELECT DISTINCTROW acmytable.kto, acmytable.kto

FROM Acmytable

WHERE (((acmytable.kto)="cd"))

Совершенно естественно, что соответствующие поля в выборке заполнения и в выборке добавления должны быть одного типа и размерности.

Запросы обновления

Запросы обновления, которые начинаются с ключевого слова UPDATE, служат для замены значений в полях таблицы в записях, которые выбираются по определенному критерию. Если вы не задаете никакого критерия, то обновляются все записи по указанному полю. Вы можете обновлять одно или несколько полей, но только одной таблицы. Есть некоторые различия в синтаксисе этой команды для приложений, которые мы рассматриваем. Самый описательный синтаксис в Microsoft SQL Server

UPDATE [[database.]owner.]{table name view name}

SET [[[database.]owner.]{table name. view name.}]

column name1 = {expression1 NULL (select statement)}

[, column name2 = {expression2 NULL (select statement)}...]

[FROM [[database.]owner.]{table name view name}

[, [[database.]owner.]{table name view name}]... ]

[WHERE search conditions]

Из приведенного синтаксиса видно, что вы можете связывать изменения в таблице в зависимости от связи с конкретной таблицей. Притом указывать это в предикате, связанном с предложением FROM. Таким образом, мы можем сформировать следующую строку

UPDATE Account SET summa=summa*1.25 FROM Account, sale

WHERE Account.account=Sale.account AND Sale.date sale>>{10.01.96}

В Microsoft Access, несмотря на отсутствие в синтаксисе предложения FROM, мы тем не менее можем легко связывать две таблицы в запросе и обновлять данные в зависимости от значения в другой таблице.

UPDATE DISTINCTROW account

INNER JOIN sale ON account.account = sale.account SET account.summa = summa*1.25