3.7.2. Командные средстваSql

Текст команд запроса хранится в его свойстве SQL, и этот текст можно откорректировать или ввести заново строчным редактором или сформировать в процессе выполнения программы (динамический запрос), если пользователь знает команды языка запросовSQL.

Команда Selectявляется мощным средством создания запросов на выборку информации. Приведем ее предварительный синтаксис.

Select<поля, выражения или *>From <исходные таблицы>

Where<условие отбора записей>Group By<поля для группировки>

Having<условие отбора группы>Order bY <поля для сортировки>

Рассмотрим фразы команды (не все фразы обязательны).

Select [Distinct] <выражение> [As <псевдоним>] [, ...]

From <таблица> [<тип связи> Join <таблица> On <условие связи>],...

Distinct ‑ дублирующие записи запроса не выводятся. Имя поля может быть составным, с включением имени таблицы, точки и самого имени таблицы. Если имя таблицы содержит пробелы, то оно заключается в апострофы. Вместо имени поля можно указать звездочку, если необходимо построить выборку из всех полей таблицы.

Псевдонимзадает наименование колонки (пробелы запрещены).

Для связывания таблиц используется фраза Join.Тип связи задается словами:Left/Right (в запрос входят все записи из таблицы, стоящей в зап­ро­се слева/справа),Inner(входят только записи с совпадающими ключами связи).

Пример задания базового запроса по нашей базе строек:

SELECT Stroiki.Ns, Stroiki.Ds, Stroiki.Ss, Stroiki.Fs, Stroiki.M, Stroiki.Em, Podrjdhiki.Np, Zakazhiki.Nz FROM «stroiki.DB» Stroiki

INNER JOIN «zakazhiki.db» Zakazhiki ON (Stroiki.Kz = Zakazhiki.Kz)

INNER JOIN «podrjdhiki.DB» Podrjdhiki ON (Stroiki.Kp = Podrjdhiki.Kp)

В результате выполнения запроса получается совокупность колонок, в заголовках которых могут находиться имена полей. Если нас не устраивают имена, формируемые по умолчанию, то можно назначить свои (псевдонимы), указав их после слова AS. В выражении могут исполь­зо­вать­ся собственные специальные арифметические функции, действующие «по вертикали»: среднее значение в группе (Avg), минимальное (Min), максимальное (Max), сумма (Sum), число записей в группе (Count),функция может иметь в качестве аргумента звездочку (Count (*)), что означает подсчет всех записей, попавших в выборку. В выражениях могут использоваться и другие функции, например:Upper(s)/Lower(s)‑ преобразование строкиSв прописные/строчные буквы.

Trim(s)‑ удаление пробелов в начале и в конце строки.

SubString(SFromn1Ton2) ‑ выделение подстроки из строкиS, начинаяcсимволаN1 и заканчиваяN2.

Cast(<выражение>As<тип>) ‑ перевод выражения к указанному типу.

S1||S2 ‑ сцепление двух строкS1 иS2.

Extract(<элемент>, From<выражение типа даты и времени>) - возвра­ща­ется значение из выражения, соответствующего значению элемента:Year(год),Month(месяц),Day(день),Hour(час),Minute(минута),Second(сек.).

Where <условие отбора>- условия отбора записей в запрос. В условиях допускается использование логических операторовAnd, Or, Notи круглых скобок. В условиях, кроме любых функцийPascal, могут содержаться следующие дополнительные операторы языка запросов SQL:

<выражение> Like<шаблон> ‑ позволяет построить условие сравнения по шаблону, набор символов: «_» (неопределенный символ), «%» (любые символы, например:WhereNsLike‘Школа%’), [n‑k]% (любые символы из интервала отnдоk, например:Like‘[A‑D]%’);

<выражение> Between<нижнее значение.> And<верхнее значение>‑ проверяет, находится ли выражение в указанном диапазоне (Where[Ss]Between0And1000000);

<выражение> In(<выражение>,<выражение>,...) ‑ проверяет, находится ли выражение, стоящее слева от слова IN, среди перечисленных справа от него (WhereKzIn(1,2,6)).

Group By <колонка>[,<колонка>...]‑ задаются колонки, по которым производится группирование выходных данных. Все записи таблицы, для которых значения колонок совпадают, отображаются в выборке единствен­ной строкой. Группирование удобно для получения некоторых сводных характеристик (суммы, число записей, среднее) группы.

Having <условие отбора группы>‑ задает критерий отбора сформированных в процессе выборки групп.

Order By <колонка> [Asc/Desc] [,<колонка> [Asc/Desc]...] ‑ опция за­дает упорядочение по колонкам по возрастанию (Asc) или убыванию (Desc). Колонка задается номером или именем поля (OrderByKp,Kz).

Рассмотрим другие команды SQL.

Create Table <имя таблицы> (<поле> <тип поля>, ...) ‑ создание таблицы (Create Table Kadr (Tab Integer, Fam Char (30) Not Null Primary Key (Tab))). Основные типы полей в SQL: SmallInt (Short), Integer (Long Integer), Numeric(x,y), Float (x,y), Char (n), Data, Boolean, Time, Money, Autoinc.

Alter Table <имя таблицы> Add <поле> <тип>, Drop <поле>, ... ‑ изменение структуры таблицы: включение (Add) и удаление (Drop) полей таблицы (Alter Table Stroiki Add Gorod Integer Drop Kp).

Update <таблица> Set <имя поля>=<новое значение>, ...

[Where <условие>] ‑ изменение значений полей (UpdateKadrSetOklad=1.5*OkladWhereCex=«ЦехN2»).

Insert Into<имя таблицы>(<список полей>) {Values (<список значе­ний>)| Select...}‑ включение новой записи или группы записей из другой таблицы.

Примеры:

InsertIntoZakazhiki(Kz,Nz)Values(3,’ЗИЛ’); // включение одной записи

Insert Into Podrjdhiki (Kp,Np) Select KPodr, NPodr From SpravPodr

WhereDSozd>01.01.80; //включение группы записей из таблицыSpravPodr

Create Index <имя индекса>On<таблица> (<поле>,...) ‑ создание индекса (Createindexindprobaonstroiki(kz,kp)).

Drop Table <имя> ‑ удаление таблицы (Drop Table Stroiki).

Drop Index «<имя таблицы>«.<имя индекса> ‑ удаление индекса.

Drop Index «<имя таблицы>«.Primary ‑ удаление главного индекса.

Delete From <имя таблицы> [Where<условие>]‑ удаление записей.

В запросе любого типа может быть вложенный запрос, который называется подзапросом.Подзапрос ‑ это запрос, результат которого используется в условии отбора в выраженииWhereдругого внешнего запроса. Подзапрос заключается в круглые скобки.

Пример. Вывести крупные стройки со сметой выше средней по стройкам:Select*FromStroikiWhereSs> (SelectAvg(Ss)FromStroiki).

Статические SQL-запросыформируются при разработке приложения и не изменяются при выполнении приложения. В запросах можно использовать параметры, имена которых начинаютсяcдвоеточия. Все параметры запроса сохраняются в его свойстве ‑ массивеParams. Для обращения к параметру во время выполнения программы указывается в квадратных скобках его номер (отсчет от нуля) в массивеParams. Тип параметра задается его свойствомDataType, значение ‑Value.

Примерпараметрического запросаQuery1 с командойSelectвида:

Select*FromStroikiWhereKz=:Pkz//код заказчика задается параметромPkz

Procedure TForm1.Button1Click(Sender: TObject);//вывод запроса

Begin Query1.Close; //закрытие запроса

Query1.Params[0].Value:='2'; //значение параметра Pkz=2

//Query1.ParamByName('pkz').asInteger:=2; //другой вариант Pkz=2

Query1.Open; {вывод запроса} End;

Динамические SQL-запросыформируются или изменяются в процессе выполнения приложения (изменяется свойствоSQL).

Пример.Разместим на форме компоненты:Query(со свойствами:DataBaseName=Stroiki,DataSource=Datamodule2.SourceStroiki),DataSource(DataSet=Query1),DBGrid(DataSource=DadaSource1,Visible=False) и кнопкуButtonс процедурой формирования динамического запроса:

Procedure TForm1.Button1Click(Sender: TObject); //формирование запроса

Begin Query1.close {закрытие запроса); Query1.SQL.Clear; {очистка текста}

Query1.SQL.Add('Select ns As Cтройка, ss As Смета, fs As Факт,’+

‘ ss-fsAsОсталосьFromStroiki.db'); //добавление строки с командойSelect

DBGrid1.Visible:=True; Query1.Open; {вывод запроса в таблицу} End;