Файл: Failid.doc

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

Программа

SELECT punum, COUNT(*), SUM(price)

FROM part

GROUP BY punum

выдает следующую таблицу:

punum	COUNT(*)	SUM OF price
U1	3	1475
U2	3	1625
U3	3	2775

В первой строке для записей, где в поле punum имеется значение U1 заданы их количество и сумма значений в поле price.

Оставим читателю вопрос: что выдает следующая программа?

SELECT pname, punum, AVG(price), COUNT(*)

FROM part

GROUP BY pname, punum

ORDER BY 3

Примечание:написание последней строки в виде ORDER BY AVG(price) является ОШИБКОЙ! См. об этом выше.

Правила, которые необходимо выполнять при использовании оператора GROUP BY:

• при наличии в программе оператора GROUP BY в SELECT можно использовать только имена полей, включенных в GROUP BY и имена функций: AVG (среднее арифметическое), COUNT (количество), MAX (максимальное значение), MIN (минимальное значение), SUM (сумма); подумайте над вопросом: почему такое ограничение?

• если в программе имеются операторы GROUP BY и SELECT одновременно, то сначала формируют группы и затем осуществляется селекция.

С помощью оператора GROUP BY можно легко запрограммировать задачи, программы для которых на Pascal’е были бы весьма длинными. Допустим, что имеется файл с кадровыми данными (в том числе и фамилия, имя, отчество) студентов. Требуется определить, какое имя сколько раз встречается. Пусть имеются сведения об автомобилестроительных компаниях: название компании, модель выпускаемой машины, ее характеристики. Естественно, что каждая компания выпускает много моделей. Определить для каждой компании самую скоростную, дорогую, дешевую, экономичную и т.д. машину.

Оператор HAVING непосредственно связан с оператором GROUP By и позволяет задавать дополнительные условия. Программа

SELECT pname, AVG(price), COUNT(*)

FROM part

GROUP BY pname

HAVING AVG(price)>800

выдает ответ:

pname	AVERAGE OF price	COUNT(*)
AT	2000	1
XT	1100	2

Здесь приведено далеко не полное описание языка SQL. Мы ограничились лишь основными его возможностями и базовыми операторами.

6.2. Использование языка sql в Delphi

В гл. 5 мы построили следующую цепочку (на самом деле упрощенную) от данных на диске до таблицы на экране

Table DataSource DBEdit

DBGrid

Вспомним, что мы имели возможность выбора полей, которые будут представлены на экране в таблицах, имели средства для улучшения внешнего вида таблиц, но не имели возможности выполнения поисковых операций и сортировки. Для использования языка SQL в Delphi необходимо заменить объект Table на Query и получим следующую цепочку

Query DataSource DBEdit

DBGrid

Объект Query находится в палитре Data Access и его легко узнать по буквам SQL и вопросительному знаку. На создаваемой форме должны быть компоненты Query, DataSource, DBGrid и/или DBEdit. Работа со знакомыми нам объектами ничем не отличается от описанного в § 5.4, за исключением того, что свойству DataSet объекта DataSource вместо значения Table1 (2, 3, ...) дадим значение Query1 (2, 3,...) путем выбора из выпадающего списка. Поэтому можем ограничиться рассмотрением лишь компоненты Query. Для нас существенны следующие его свойства (как и для Table, они должны быть заданы в строго определенной очередности):

1. DataВaseName – значением является, как и при Table, путь до файла с данными.

2. SQL – содержит программу.

3. Active – имеет два возможных значения: True и False.

Если в программе на SQL допущена ошибка, то при попытке дать значение True свойству Active выдается диагностическое сообщение о программе. Свойство SQL имеет три точки, что означает наличие диалогового окна для написания программы. Повторим приведенную рекомендацию: операторы SQL пишем большими буквами и каждый оператор с новой строки, все остальное маленькими буквами и за оператором, к которому оно относится. При возникновении необходимости внесения изменений в программу на SQL, необходимо сначала переключить значение свойства Active на False, внести необходимые изменения и затем снова переключить на True. Для улучшения внешнего вида таблицы на экране поступим точно так, как при работе с Table: делаем двойной щелчок по иконке Query: откроется знакомое нам диалоговое окно Form1.Query1; работа с ним ничем не отличается от описанного выше. Для DBEdit можно из списка выбрать для представления поля из списка полей после оператора SELECT.

Описанный только что способ включения в наше приложение программы на SQL является простейшим, но и весьма ограниченным: для внесения любого изменения придется остановить приложение, менять программу в свойстве SQL объекта Query и запустить приложение снова. Имеется возможность изменить программу на SQL не останавливая приложение. Для этого на форму необходимо перенести следующие компоненты: Query, DataSource, DBGrid (или DBEdit); Memo и Button из палитры Standard (рис.6.2). Memo отличается от знакомой нам компоненты Edit только тем, что может содержать несколько строк текста. Нетрудно догадаться, что Memo нужно для представления текста программы на SQL, а кнопка для запуска этой программы.

Рис. 6.2. Окно для программы на языке SQL

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

procedure TForm1.Button1Click(Sender: TObject);

begin

with Query1 do {Работаем с Query1}

begin

close; {Свойство Active получит значение False,}

{ как всегда перед внесением изменений}

SQL.Clear; {стираем старую программу на SQL}

SQL.Add(Memo1.Text);{подключаем новую программу на SQL}

Open; {свойство Active получит значение True,}

{ как всегда после внесения изменений}

end;

end;

Этими средствами можно менять программу на SQL во время выполнения приложения. Если мы пользуемся только динамическими полями, то никаких сложностей при этом не ожидается. При работе со статическими полями возникают те же проблемы, которые мы обсуждали в п. 5.4.1 при работе с Table.

6.3. Параметры в программах на sql

Описанный в § 6.2 способ изменения программы на SQL во время выполнения приложения неудобен тем, что он предъявляет весьма высокие требования к квалификации пользователя приложения: он должен знать структуру файлов и владеть языком. Конечно, за это он имеет в своем распоряжении всю мощь языка и обширные возможности для поиска данных. Имеется и третья возможность: программа на SQL может содержать формальные параметры и пользователь приложения, не меняя структуру программы, может с помощью фактических параметров формулировать разные запросы.

Допустим, что необходимо из таблицы part выдать данные на основе запросов, содержащих ограничения по полям price и pvnum. Для этого в объекте Query2:

• в свойстве SQL пишем следующую программу:

SELECT pname,price

FROM part

WHERE (price BETWEEN :pr1 AND :pr2) AND pvnum=:num1

• свойство Active сохраняет значение False.

Как видно из примера, признаком формального параметра является двоеточие : на параметры можно ссылаться по имени и по номеру; нумерация начинается с нуля (:pr1 имеет номер 0).

Форма для работы с параметрами приведена на рис. 6.3. Реализация кнопки “Выполнить”

procedure TForm1.Button1Click(Sender: TObject);

begin

with Query2 do

begin{Свойство Active получит значение False, как всегда}

close; {перед внесением изменений}

{Обращение к параметрам по номеру}

Params[0].AsInteger:=StrToInt(Edit1.Text);

Params[1].AsInteger:=StrToInt(Edit2.Text);

{Обращение к параметру по имени}

ParamByName('num1').AsString:=Edit3.Text;

Open; {Свойство Active получит значение True, как всегда}

{после внесения изменений}

end;

end;

Рис. 6.3. Работа с параметрами

На вопросы надежности при использовании SQL с параметрами необходимо обращать особое внимание. Во время создания приложения практически не проверяется соответствие между формальными и фактическими параметрами. Если после нажатия на кнопку “Выполнить” будет обнаружено несоответствие, то возникает программное прерывание. Для уменьшения такого риска можно, например, пользоваться масками ввода. Параметры позволяют иногда и исключить проверку по какому-либо критерию: можно дать такие значения фактическим параметрам, что все записи файла удовлетворяют им. Легко видеть, что в нашем примере по цене такой диапазон выбрать можно, но по именам нет.