Установка полей соответствия (Lookup)

Дополним список постоянных полей таблицы строек (п. 3.3) полями с наименованиями заказчика (Nz) и подрядчика (Np). Это можно сделать, так как в таблицах есть поля соответствия с кодами заказчиков и под­ряд­чи­ков и при равенстве этих кодов наименования будут соответ­ст­во­вать кодам в теку­щей записи строек. Для этого от­кро­ем мо­дуль базы, щелк­нем правой мыш­­кой на табли­це Stroiki на панели Com­po­nents и выбе­рем команду Fields Editor, поя­вит­ся окно DataModu­le2.Stroi­ki. Из кон­­текст­ного ме­­­ню это­го окна вы­пол­ним команду New field. Появится ок­но, заполним его (рис. 3.6.1).

Рис. 3.6.1. Установка поля соответствия

Аналогично сформируем второе поле с названием заказчика. Если разместить таблицу (DBGrid) на форме и включить колонки с полями Np, Nz при выводе таблицы строек, то при выборе ячеек с этими полями они будут раскрываться в виде списка с наименованиями, и пользователю достаточно только выбрать нужное наименование, а код его поместится в соответствующее поле с кодом Kp или Kz таблицы строек.

Кнопкой Lookup можно визуально установить связи по полям соответствия линиями со значком в виде «глаза».

Создание запросов

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

Визуальные средства

Для примера составим запрос, в который включим все поля таблицы строек и наименования заказчиков и подрядчиков из таблиц заказчиков и подрядчиков. Разместим компоненты Query (со свойством Active=True), DataSource (со свойствами: Name=SourceBazoviiZapros, DataSet=Query1) с панели Data Access на странице Components модуля данных форме и выберем команду SQL Builder (построитель запросов), и появится окно построителя (рис. 3.7.1.1). В списке Database выберем базу Stroiki. Используя спи­сок Table, вы­бе­­рем таблицы Stroi­ki, Podrjdhiki, Zakaz­hiki. Мыш­кой уста­но­вим связь меж­ду этими табли­цами по по­лям Kp и Kz соот­вет­ствен­но. Га­лоч­кой по­ме­тим по­ля, выводи­мые в зап­рос. На странице Criteria зада­дим ус­ло­вия отбора за­пи­­сей (например, код заказчика равен 1).

Рис. 3.7.1.1. Создание запросов

 

На странице Sorting задается список полей сортировки в порядке убывания приоритетов. Порядок сортировки задается кнопками A..Z (по возрастанию), Z..A (по убыванию).

Для проверки запроса нажмем кнопку Execute Query. Кнопкой Savethe current query сохраним его в базе под именем BazoviiZapros; его можно посмотреть и откорректировать редактором запроса, вызываемого кнопкой Show and Edit SQL (рис. 3.7.1.2).

Рассмотрим формирование итоговых запросов на примере: в запрос включить код, наименование подрядчика и итоговые суммы выполненных работ и сметных сумм по всем стройкам каждого подрядчика (рис.3.7.1.2). Разместим в окне запросов и свяжем по коду подрядчика таблицы строек и подрядчиков. Отметим выводимые в запросе поля Kp, Np, Ss, Fs. Перейдем на страницу Selection и щелкнем по полю Ss, выберем команду Summary, по которой добавится одноименный столбец. Изменим имя Ss на SumSs и в ячейке колонки Summary выберем операцию Sum (итоговая сумма). Всего может быть несколько групповых операций: Count (число всех значений по­ля, например, число стро­ек у подрядчика), Avg, Min, Max (среднее, мини­маль­­ное, максималь­ное чис­­ла в группе). Сло­во Dictinct после наиме­но­ва­ния опера­ции задает режим игнори­ро­вания ду­б­­­ли­рую­щих зна­че­ний. Ана­логично просумми­ру­ем поле Fs. На странице Grouping в колонке Grouped On укажем поля груп­пировки Kp, Np и выполним запрос. Рис. 3.7.1.2. Итоговый запрос

 

На странице Group Criteria можно задать условие отбора итоговых записей по группе, например: вывести только крупных подрядчиков с суммарным объемом выполненных работ более 1000000р.

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

Для отображения запроса на форме, в редакторе полей для объекта Query1 добавим все или отдельные поля запроса. Разместим компонент DBGrid на форме со значением его свойства DataSource, равным Data­Mo­du­le2.SourceBazoviiZapros, сформируем столбцы этой таблицы.

Запрос может быть выполнен непосредственно из программы методами ExecSQL, Execute (п. 3.13, 3.14) или из проводника SQL Explorer, вызываемого командой DataBase/Explore. В проводнике можно набрать текст запроса на странице Enter SQL, выполнить его кнопкой Excute Query и сохранить его в текстовом файле командой Object/Save As.

Запрос можно создать и средствами Database Deskop (командой Tools/Database Desktop/File/New/QBE Query).

Командные средства 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 (S From n1 To n2) ‑ выделение подстроки из строки 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 <шаблон>‑ позволяет построить условие сравнения по шаблону, набор символов: «_» (неопределенный символ), «%» (любые символы, например: Where Ns Like ‘Школа%’), [n‑k]% (любые символы из интервала от n до k, например: Like ‘[A‑D]%’);

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

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

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

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

Order By<колонка> [Asc/Desc] [,<колонка> [Asc/Desc]...] ‑ опция за­дает упорядочение по колонкам по возрастанию (Asc) или убыванию (Desc). Колонка задается номером или именем поля (Order By Kp, 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 <условие>] ‑ изменение значений полей (Update Kadr Set Oklad=1.5*Oklad Where Cex=«Цех N2»).

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

Примеры:

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

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

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

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

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

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

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

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

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

Пример. Вывести крупные стройки со сметой выше средней по стройкам: Select * From Stroiki Where Ss > (Select Avg(Ss) From Stroiki).

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

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

Select* From Stroiki Where Kz=: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-fs As Осталось From Stroiki.db'); //добавление строки с командой Select

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