Тема 12. Основы языка SQL и его использование в приложениях

1.    Оператор выбора Select

2.    Операции с записями

3.    Операции с таблицами

4.    Операции с индексами

5.    Основные свойства компонента Query

6.    Основные методы и события компонента Query

Презентация к лекции

 

1. Оператор выбора Select

Язык SQL (Structured Query Language - язык структурированных запросов) позволяет формировать запросы к базам данных. Запрос - это вопрос к базе данных, возвращающий запись или множество записей, удовлетворяющих вопросу.

C++Builder позволяет приложению при помощи запросов SQL использовать данные:

§  Таблиц PARADOX и dBase - используется синтаксис локального SQL.

§  Локального сервера InterBase - полностью поддерживается соответствующий синтаксис.

§  Удаленных серверов SQL через драйверы SQL Links.

Язык SQL не чувствителен к регистру. Если в программе используется  несколько операторов SQL, то в конце каждого оператора ставится точка с запятой ";".

 

Отбор записей из таблицы

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

В качестве примера перенесите на форму компонент Query со страницы Data Access и установите его свойство DatabaseName равным dbP - базе данных Paradox.

Поместите на форму компонент DataSource и в его свойстве DataSet задайте Query1. Поместите также на форму компонент DBGrid и в его свойстве DataSource задайте DataSource1.

Операторы SQL задаются в свойстве SQL компонента Query1, далее нужно установить значение свойства Active компонента Query1 в true.

Одна из форм оператора Select имеет следующий синтаксис:

SELECT <список имен полей> FROM <таблица> WHERE <условие отбора> ORDER BY <список имен полей>;

Элементы оператора WHERE и ORDER BY не являются обязательными. Элемент WHERE определяет условие отбора записей: отбираются только те, в которых условие выполняется. Элемент ORDER BY определяет упорядочивание возвращаемых записей.

<таблица> - это таблица базы данных, из которой осуществляется отбор, например, Pers.

После ключевого слова Select указываются имена тех полей таблицы, которые будут возвращены. Имена полей разделяются запятыми. Например, оператор

SELECT Fam, Nam, Par, Year_b FROM Pers указывает, что следует вернуть поля Fam, Nam, Par и Year_b из таблицы Pers. Запишите его в свойстве SQL компонента Query1, установите значение свойства Active в true и посмотрите результаты.

Если указать вместо списка полей символ "*" - это будет означать, что требуется вернуть все поля. Например, оператор

SELECT * FROM Pers означает выбор всех полей.

В списке могут быть и арифметические выражения с помощью операций +, -, *, /. После выражения может записываться псевдоним выражения в форме: AS <псевдоним>. В качестве псевдонима может фигурировать любой идентификатор, на который потом можно при необходимости ссылаться. Он будет при отображении результатов фигурировать в заголовке таблицы.

Такими простыми средствами создаются аналоги вычисляемых полей.

Приведем пример использования выражения:

SELECT Fam, Nam, (2000-Year_b) AS Age FROM Pers

Этот оператор создает поле Age, вычисляемое по формуле (2000-Year_b).

Рассмотрим условия отбора, задаваемые после ключевого слова WHERE. Это условие определяет критерий, по которому отбираются записи. Оператор Select отбирает только те записи, в которых заданное условие истинно. Условие может включать имена полей (кроме вычисляемых), константы, логические выражения, содержащие арифметические операции, логические операции and, or, not и операции отношения:

 

=

равно

> 

больше

>=

больше или равно

< 

меньше

<=

меньше или равно

!=

не равно

Like

наличие заданной последовательности символов

between ... and

диапазон значений

in

соответствие элементу множества

=

равно

> 

больше

 

Первые шесть операций очевидны. Например, оператор

SELECT Fam FROM Pers WHERE Pol=false and Year_b > 1960 отберет записи, относящиеся к женщинам, родившимся после 1960 года.

Операция Like имеет синтаксис: <поле> LIKE <последовательность символов>

Эта операция применима к полям типа строк и возвращает true, если в строке встретился фрагмент, заданный в операции как <последовательность символов>. Заданным символам может предшествовать и их может завершать символ процента "% ", который означает - любое количество любых символов. Если символ процента не указан, то заданная последовательность символов должна соответствовать только целому слову. Например, условие

Fam LIKE 'A%'

означает, что будут отобраны все записи, в которых фамилия начинается с заглавной русской буквы "А" (операция Like различает строчные и прописные символы). Условию

Fam LIKE 'Иванов%' будут удовлетворять фамилии «Иванов» и «Иванова», а условию

Fam LIKE ' %ван%' кроме этих фамилий будет удовлетворять, например, фамилия «Иванников».

Операция between ... and имеет синтаксис:

<поле> between <значение> and <значение>

и задает для указанного поля диапазон отбираемых значений. Например, оператор

SELECT Fam, Year_b FROM Pers WHERE Year_b BETWEEN 1960 AND 1970

отберет записи сотрудников в заданном диапазоне возраста (включая граничные значения 1960 и 1970).

Операция in имеет синтаксис: <поле> in (<множество>)

и отбирает записи, в которых значение указанного поля является одним из элементов указанного множества. Например, оператор

SELECT Fam, Year_b FROM Pers WHERE Fam IN('Иванов','Петров','Сидоров')

отберет записи сотрудников с заданными фамилиями, а оператор

SELECT Fam, Year_b FROM pers WHERE Year_b IN (1950,1960)

отберет записи сотрудников указанных годов рождения.

Ключевое слово ORDER BY определяет упорядочивание (сортировку) записей. После ORDER BY следует список полей, определяющих сортировку. Можно указывать только поля, фигурирующие в списке отобранных (в списке после ключевого слова SELECT). Причем эти поля могут быть и вычисляемыми.

Если в списке сортировки указано только одно поле, то сортировка производится по умолчанию в порядке возрастания значений этого поля. Например, оператор

SELECT Dep, Fam, Year_b FROM Pers ORDER BY Year_b

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

SELECT Dep, Fam, Year_b FROM Pers ORDER BY Year_b DESC

Если в списке после ORDER BY перечисляется несколько полей, то первое из них - главное и сортировка проводится прежде всего по значениям этого поля. Записи, имеющие одинаковое значение первого поля упорядочиваются по значениям второго поля и т.д. Например, оператор

SELECT Dep, Fam, Year_b FROM Pers ORDER BY Dep, Fam

сортирует записи прежде всего по отделам (значениям поля Dep), а внутри каждого отдела - по алфавиту. Оператор

SELECT Dep, Fam, Year_b, Pol FROM Pers  ORDER BY Dep, Pol, Fam

сортирует записи по отделам, полу и алфавиту.

 

Совокупные характеристики

Оператор Select позволяет возвращать также и некоторые совокупные (агрегированные) характеристики, подсчитанные по всем или по указанным записям таблицы. Одна из функций, возвращающих такие совокупные характеристики, соunt(<условие>) - количество записей в таблице, удовлетворяющих заданным условиям. Например, оператор

SELECT count(*) FROM Pers подсчитает полное количество записей в таблице Pers.

А оператор SELECT count (*) FROM Pers WHERE Dep='Цex 1'

выдаст число записей сотрудников цеха 1.

Ключевое слово DISTINCT (уникальный) позволяет выбор числа неповторяющихся значений в указанном поле. Например, оператор

SELECT count(DISTINCT Dep) FROM Pers

вернет число различных подразделений, упомянутых в поле Dep таблицы Pers.

Функции min(<поле>), mах(<поле>), avg(<поле>), sum(<поле>) возвращают соответственно минимальное, максимальное, среднее и суммарное значения указанного поля. Например, оператор

SELECT min(Year_b), max(Year_b), avg(Year_b) FROM Pers

вернет минимальное, максимальное и среднее значение года рождения, а оператор

SELECT min(2007-Year_b), max (2007-Year_b), avg(2007-Year_b) FROM Pers WHERE Dep='Бухгалтерия'

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

В операторе Select можно указывать не только суммарные характеристики, но и любые выражения от них. Например, оператор

SELECT 2007-(min(Year_b)+max (Year_b))/2 FROM Pers WHERE Dep='Бухгалтерия'

выдаст моду (среднее между максимальным и минимальным значениями) возраста сотрудников бухгалтерии.

При использовании суммарных характеристик надо учитывать, что в списке возвращаемых значений после ключевого слова SELECT могут фигурировать или поля, или совокупные характеристики, но не могут фигурировать и те, и другие

Смешение в одном операторе полей и совокупных характеристик возможно, если использовать группировку записей, задаваемую ключевыми словами GROUP BY. После этих ключевых слов перечисляются все поля, входящие в список SELECT. В этом случае смысл совокупных характеристик изменяется: они проводят вычисления не по всем записям таблицы, а по тем, которые соответствуют одинаковым значениям указанных полей. Например, оператор

SELECT Dep, count(*) FROM Pers GROUP BY Dep вернет таблицу, в которой будет 2 столбца: столбец с названиями отделов, и столбец, в котором будет отображено число сотрудников в каждом отделе.

 

Вложенные запросы

Результаты, возвращаемые оператором Select, можно использовать в другом операторе Select. Причем это относится и к операторам, возвращающим совокупные характеристики, и к операторам, возвращающим множество значений. Например, нужно узнать фамилию самого молодого сотрудника. Это можно сделать с помощью вложенных запросов:

SELECT Fam,Year_b FROM Pers WHERE Year_b=(SELECT max(Year_b) FROM Pers)

Здесь вложенный оператор SELECT max(Year_b) FROM Pers возвращает максимальный год рождения, который используется в элементе WHERE основного оператора Select для поиска сотрудника (или сотрудников), чей год рождения совпадает с максимальным.

Вложенные запросы могут обращаться к разным таблицам. Пусть, например, мы имеем две аналогичных по структуре таблицы Pers и Persl, относящиеся к разным организациям, и хотим в таблице Pers найти всех однофамильцев сотрудников другой организации. Чтобы проверить работу с несколькими таблицами, откройте в Database Desktop таблицу Pers, выполнить команду Table | Restructure и кнопкой Save as сохранить таблицу под именем Persl. Следующий оператор определяет всех однофамильцев в этих двух таблицах.

SELECT * FROM Pers WHERE Fam IN (SELECT Fam FROM Pers1)

Вложенный оператор Select Fam from Pers1 возвращает множество фамилий из таблицы Pers1, а конструкция WHERE основного оператора Select отбирает из фамилий в таблице Pers те, которые имеются в множестве фамилий из Persl.

При работе в условии WHERE с множествами записей можно использовать ключевые слова: All и Any. All означает, что условие выполняется для всех записей, a Any - хотя бы для одной записи. Например, оператор

SELECT * FROM Pers WHERE Year_b >= ALL (SELECT Year_b FROM Persl)

ищет сотрудников в Pers, которые не старше любого сотрудника в Persl. Кстати, если в этом операторе заменить Persl на Pers, то получим список самых молодых сотрудников организации, который мы получали ранее другим способом. А оператор

SELECT * FROM Pers WHERE Year_b > ANY (SELECT Year_b FROM Persl)

ищет сотрудников в Pers, которые моложе хотя бы одного сотрудника в Pers1.

 

Объединения таблиц

В запросе можно объединить данные двух или более таблиц. Пусть, нужно получить список сотрудников всех производственных подразделений. В таблице Pers есть список сотрудников с указанием в поле Dep подразделений, в которых они работают. А в таблице Dep есть список всех подразделений в поле Dep и характеристику каждого подразделения в поле Prosv. Тогда получить список сотрудников всех производственных подразделений можно оператором:

SELECT Pers.* FROM Pers, Dep WHERE (Pers.Dep=Dep.Dep) AND (Dep.Proisv=true)

Здесь обращаемся сразу к двум таблицам Pers и Dep, которые перечислены после ключевого слова FROM. Поэтому каждое имя поля предваряется ссылкой на таблицу, к которой оно относится. Впрочем, это надо делать только для полей, имя которых повторяется в разных таблицах (поле Dep). Перед полем Proisv ссылку на таблицу можно опустить. В конструкции WHERE условие Pers.Dep=Dep.Dep ищет запись в таблице Dep, в которой поле Dep совпадает с полем Dep текущей записи таблицы Pers. А условие Dep.Proisv=true отбирает те записи, в которых в таблице Dep найденному подразделению соответствует поле Proisv = true.

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

SELECT P.* FROM Pers P, Dep D WHERE (P.Dep=D.Dep)AND(D.Proisv=true)

В этом примере таблице Pers дан псевдоним Р, а таблице Dep - D. Эти псевдонимы действуют только в данном операторе и не имеют никакого отношения к псевдонимам баз данных.

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

SELECT p1.fam, p2.fam, p1.Year_b FROM Pers p1, Pers p2

WHERE (p1.year_b = p2.year_b) AND (p1.fam!= p2.fam) and (p1.Fam < p2.Fam)

Для таблицы Pers использовали два псевдонима: p1 и р2. В конструкции WHERE ищутся в этих якобы разных таблицах записи с одинаковым годом рождения. Второе условие pl.fam!= p2.fam нужно, чтобы сотрудник не отображался в результатах как ровесник сам себя. Чтобы исключить дублирование добавлено условие - pl.Fam < p2.Fam:

Возможны и объединений, которые выдают записи независимо от того, есть ли соответствующее поле во второй таблице. Это внешние объединения (outer join). Их три типа: левое, правое и полное. Левое объединение (обозначается ключевыми словами LEFT OUTER JOIN ... ON) включает в результат все записи первой таблицы, даже те, для которых не имеется соответствия во второй. Правое объединение (обозначается ключевыми словами RIGHT OUTER JOIN ... ON) включает в результат все записи второй таблицы, даже если им нет соответствия в записях первой. Полное объединение (обозначается ключевыми словами FULL OUTER JOIN ... ON) включает в результат объединение записей обеих таблиц, независимо от их соответствия.

Пусть, например, имеется таблица сотрудников некоей компании Pers и есть таблица Chef, в которой занесены данные на членов совета директоров этой компании. В число членов совета входят и сотрудники компании, и посторонние лица. Для определенности положим, что в таблице Pers имеются записи на сотрудников «Иванов» и «Петров», причем Петров является членом совета, а Иванов - нет. В таблице Chef имеются записи на членов совета «Петров» и «Сидоров», причем Сидоров - не сотрудник компании. Тогда оператор

SELECT * FROM Pers LEFT OUTER JOIN Chef ON Pers.Fam = Chef.Fam выдаст результат вида:

 

Поля таблицы Pers

Поля таблицы Chef

Иванов

 

 

 

Петров

 

Петров

 

 

Оператор задал левое объединение таблицы Pers (она указана после ключевого слова FROM) с таблицей Chef (она указана после ключевых слов LEFT OUTER JOIN). Условие объединения указано после ключевого слова ON и заключается в совпадении фамилий.

Результат включает все поля и таблицы Pers, и таблицы Chef. Число строк соответствует числу записей таблицы Pers. В строках, относящихся к записям, для которых в Chef не нашлось соответствие, поля таблицы Chef остаются пустые.

Оператор правого объединения

SELECT * FROM Pers RIGHT OUTER JOIN Chef ON Pers.Fam = Chef.Fam выдаст результат вида:

 

Поля таблицы Pers

Поля таблицы Chef

Петров

 

Петров

 

 

 

Сидоров

 

 

Число строк соответствует числу записей таблицы Chef. В строках, относящихся к записям, для которых в Pers не нашлось соответствие, поля таблицы Pers остаются пустые. Оператор полного объединения

SELECT * FROM Pers FULL OUTER JOIN Chef ON Pers.Fam =Chef.Fam  выдаст результат вида:

 

Поля таблицы Pers

Поля таблицы Chef

Иванов

 

 

 

Петров

 

Петров

 

 

 

Сидоров

 

 

В нем к строкам, относящимся к таблице Pers, добавлены строки, относящиеся к таблице Chef, для которых не нашлось соответствия в таблице Pers.

2. SQL операторы манипулирования с записями

 

Вставка новой записи в таблицу осуществляется оператором Insert, который может иметь вид: INSERT INTO <имя таблицы> (<список полей>) VALUES (<список значений:»)

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

Например:

INSERT INTO Pers (Fam, Nam, Par, Pol) VALUES ('Иванов', 'Андрей', 'Андреевич', true)

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

Другая форма оператора Insert использует множество значений, возвращаемых оператором Select. Этот оператор может выбирать записи из какой-то другой таблицы и вставлять их в данную. Синтаксис этой формы Insert :

INSERT INTO <имя таблицы> <оператор Select>

Пусть, например, имеется таблицу Old_Pers пожилых людей организации и нужно заполнить ее соответствующими записями из таблицы Pers. Это можно сделать одним оператором:

INSERT INTO O1d_Pers SELECT * FROM Pers WHERE Year_b < 1939

Таблица Old_Pers сразу заполнится множеством соответствующих записей из Pers.

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

Редактирование записей осуществляется оператором Update:

UPDATE <имя табл.> SET <список вида <поле>=<выражение>> WHERE <условие>

Наличие условия позволяет редактировать не только одну запись, но сразу множество их. Например, если при очередной реорганизации предприятия решили слить «Цех 1» и «Цех 2» в один «Цех 1», то исправление всех записей в таблице можно сделать одним оператором:

UPDATE Pers SET Dep = 'Цех 1' WHERE Dep = 'Цех 2'

Удаление записей осуществляется оператором Delete:

DELETE FROM <имя таблицы> WHERE <условие>

Наличие в операторе условия позволяет удалять не только одну, но сразу множество записей. Например, если при реорганизации предприятия подразделение «Цех 1» ликвидировали и всех его сотрудников уволили из штата данной организации, то удалить из таблицы все соответствующие записи можно оператором:

DELETE FROM Pers WHERE Dep = 'Цех 1'

3. SQL операторы манипулирования  таблицами

Создание новой таблицы осуществляется оператором Create Table:

CREATE TABLE <имя таблицы> (<список вида  <имя поля> <тип>(<размер>)>)

Размер указывается только для полей строковых и некоторых других типов. После объявления некоторых полей могут включаться слова PRIMARY KEY, что указывает на то, что данное поле входит в первичный ключ. Кроме того, после объявления некоторых полей можно вставлять слова NOT NULL, означающие, что значение этого поля обязательно должно быть задано в каждой записи. Например:

CREATE TABLE Person

(Fam char(15) NOT NULL PRIMARY KEY,

Nam char(15) NOT NULL PRIMARY KEY,

Par char(15) NOT NULL PRIMARY KEY,

Year_b integer )

Приведенная форма оператора Create Table - простейшая. Более сложные формы позволяют задавать в таблице вычисляемые поля, значения по умолчанию, ограничения значений, связывать разные таблицы по ключам и многое другое.

Удаление таблицы осуществляется оператором Drop Table:

DROP TABLE <имя таблицы>

Надо учесть, что удаление таблицы полностью уничтожает таблицу.

Модификация структуры существующей таблицы осуществляется оператором Alter Table:

ALTER TABLE <имя таблицы> <действие> <имя поля> <тип данных> ...

В этом операторе <действие> может принимать значения ADD - добавить новое поле, или DROP - удалить существующее поле. Если поле добавляется, то для него надо указывать <тип данных>. Если поле удаляется, то тип данных не указывается. Приведем пример оператора модификации структуры:

ALTER TABLE Pers DROP Year_b, ADD Age integer

 

4. SQL операторы манипулирования индексами

Индексы существенно ускоряют процесс поиска и упорядочивания записей таблицы.

Создание нового индекса осуществляется оператором Create Index:

CREATE  INDEX <имя индекса> ON <имя таблицы > <список полей>

Например:

CREATE INDEX depyear ON Pers Dep, Year_b

Удаление существующего индекса осуществляется оператором Drop Index:

DROP  INDEX <имя таблицы >.<имя индекса>

Например: DROP Index Pers.depyear

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

ALTER INDEX <имя индекса> DEACTIVATE

ALTER INDEX <имя индекса> ACTIVATE

 

5. Основные свойства компонент Query.

Компонент Query позволяет работать с таблицами, используя операторы SQL. Большинство свойств и методов Query совпадают с Table. Дополнительные преимущества Query - возможность формировать запросы на языке SQL.

Исходя из возможностей Table и Query, можно заключить, что в серверных приложениях целесообразнее использовать Query, а при работе с локальными базами данных - компонент Table.

Рассмотрим пример использования Query. Поместим на форму компоненты Query, DataSource, DBGrid. В свойстве DataSet компонента DataSource1 задайте Query1, а в свойстве DataSource компонента DBGrid1 задайте DataSource1. Создали обычную цепочку: набор данных (Query1), источник данных (DataSource1), компонент визуализации и управления данными (DBGrid1).

Основное свойство Query - SQL типа TStrings. Это список строк, содержащих запросы SQL. В процессе проектирования приложения обычно необходимо сформировать в этом свойстве некоторый предварительный запрос SQL, который показал бы, с какой таблицей или таблицами будет проводиться работа. Но далее во время выполнения приложения свойство SQL может формироваться программно методами, обычными для класса TStrings: Clear - очистка, Add - добавление строки и т.д.

Настройку компонента Query в процессе проектирования можно производить вручную

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

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

Предупреждение .Прежде, чем начинать детальную настройку компонента Query, надо сформировать в свойстве SQL запрос, в котором указывается таблица и перечисляются параметры, если они используются в приложении. Пока токай запрос в SQL отсутствует, дальнейшая настройке Query невозможно. Запрос, заносимый в SQL в начале проектирования, носит чисто служебный характер. В дальнейшем можно его программно заменить на любой другой запрос.

Запрос, заносимый в SQL в начале проектирования, может иметь, например, следующий вид:

Select * from pers

После этого система поймет, с какой таблицей будет проводиться работа, и можно будет настроить поля в Query. Если работа будет проводиться с несколькими таблицами, нужно их указать в запросе. Например:

Select * from pers, dep

После того как соответствующий запрос написан, можете установить свойстве Active компонента Query в true. Если все выполнено правильно, то в компоненте DBGrid1 отобразится информация из запрошенных таблиц.

Приложение предоставляет возможность просматривать записи, но, к сожалению, не позволяет их редактировать. Это связано с тем, что запрос Select возвращает таблицу только для чтения. В таком простом приложении это легко исправить. Достаточно установить в компоненте Query1 свойство RequestLive в true. Это позволяет возвращать как результат запроса изменяемый, «живой» набор данных, вместо таблицы только для чтения. Точнее, установка RequestLive в true делает попытку вернуть «живой» набор данных. Успешной эта попытка будет только при соблюдении ряда условий, в частности:

§  набор данных формируется обращением только к одной таблице

§  набор данных не упорядочен (в запросе не используется ORDER BY)

§  в наборе данных не используются совокупные характеристики типа Sum, Count и др.

§  набор данных не кэшируется (свойство CashedUpdates равно false)

В примере все эти условия соблюдаются, поэтому установив RequestLive в true можно редактировать данные, удалять записи, вставлять новые записи.

Для управления отображением данных, как и в компоненте Table, имеется Редактор Полей (Field Editor). Вызывается двойным щелчком на Query, или щелчком правой кнопки мыши на Query и выбором Fields Editor из всплывающего меню. В нем можно добавить имена получаемых полей (щелчок правой кнопкой мыши и выбор раздела меню Add), задать заголовки полей, отличающиеся от их имен, сделать какие-то поля невидимыми (Visible), не редактируемыми (Readonly), в логических полях можно задать высвечиваемые слова (да;нет), задать формат высвечивания чисел, создать вычисляемые поля, поля просмотра, задать диапазоны значений и многое другое.

 

Динамические запросы и параметры Query

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

Параметры задаются в запросе с двоеточием, предшествующим имени параметра:

:<имя параметра>

Например, можно записать в запросе Select элемент WHERE в виде:

WHERE Year_b <= :PYear

Здесь сравнивается год рождения не с какой-то константой, а со значением параметра PYear.

Теперь обратимся к компоненту Query. Нужно ввести в его свойство SQL запрос, содержащий параметры, например:

Select * from pers where (year_b>:PYear) and (dep=:Dep)

в Инспекторе Объектов при выборе свойства Params, откроется е окно со списком объектов - указанных в запросе параметров PYear и Dep. В этом списке можно выделять по очереди параметры и в Инспекторе Объектов устанавливать их свойства. Это свойства:

 

DataType

тип данных параметра (int, string и т.п.)

Name

имя параметра

ParamType

тип параметра (используется при обращении к хранимым на сервере процедурам

Value

значение параметра по умолчанию

Type - подсвойство Value

тип значения по умолчанию

DataType

тип данных параметра (int, string и т.п.)

 

После установки свойства всех параметров, можно использовать их при программировании приложения.

Программный доступ к параметрам во время выполнения приложения осуществляется аналогично доступу к полям набора данных. Свойство Params является указателем на массив параметров типа TParam, к элементам которого можно обращаться по индексу через его свойство Items[Word Index]. Последовательность, в которой располагаются параметры в массиве, определяется последовательностью их упоминания в запросе SQL.

Значения параметров, как и значения полей, определяются такими свойствами объектов-параметров, как Value, AsString, Aslnteger и т.п. Например, оператор

for (int i = 0; i < Query1->Params->Count; i++)

if (Query1->Params->Items[i]->IsNull && Query1->Params->Items[i]->DataType == ftInteger)

Query1->Params->Items[i]->AsInteger = -1;

задаст значение «-1» всем целым параметрам, которым до этого не было присвоено значение. В нем использовано свойство Count - число параметров, свойство IsNull, равное true, если параметру не задано никакое значение, свойство DataType, указывающее тип параметра, и свойство AsInteger, дающее доступ к значению параметра как к целому числу. Другой пример: операторы

Query1->Params->Items[0]->AsInteger = 1950;

Query1->Params->Items[1]->AsString = "Бухгалтерия";

задают значения первому (индекс 0) и второму (индекс 1) параметрам компонента Query1, в свойстве SQL которого записан приведенный ранее оператор Select с параметрами :PYear и :Dep. Поскольку в этом операторе параметр :PYear упоминается первым, го его индекс равен 0.

У Params есть еще свойство - ParamValues. Оно представляет собой массив значений параметров типа Variant. В качестве индекса в это свойство передается имя параметра или несколько имен, разделяемых точками с запятой. Например, операторы

Query1->Params->ParamValues["PYear"] = 1950;

Query1->Params->ParamValues["Dep"] = "Бухгалтерия";

задают те же значения параметрам, что и приведенные выше. Преимуществом является то, что при записи этих операторов не надо помнить индексы параметров. Другим преимуществом свойства ParamValues является возможность задать значения сразу нескольким параметрам. Например:

Variant par[] = {1950,"Бухгалтерия");

Query1->Params->ParamValues["PYear;Dep"] = VarArrayOf(par,1);

Другой способ обращения к параметрам - использование метода ParamByName компонента Query- Например, операторы

Query1->ParamByName("PYear")->AsInteger = 1950;

Query1->ParamByName("Dep")->AsString = "Бухгалтерия";

задают параметрам с именами PYear и Dep те же значения.

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

 

Связывание таблиц

Большинство свойств Query аналогичны свойствам Table. Объекты полей создаются автоматически для тех полей, которые перечислены в операторе SQL. Программный доступ к этим полям осуществляется так же с помощью свойства Fields (например, Query1-> Fields[0]) или методом FieldByName (например, Query1->FieldByName("Dep")). Можно также создавать объекты полей с помощью Редактора Полей, вызываемого двойным щелчком на Query или из меню, всплывающего при щелчке на Query правой кнопкой мыши. В этом случае доступ к объекту поля можно осуществлять также и по его имени (например, Query1Dep).

При использовании для Query Редактора Полей надо добавлять в нем все поля, перечисленные в операторе SQL Иначе поля не добавленные в Редакторе Полей не будут доступны

Предупреждение. Доступ к полям по имени возможен только в случае, если объекты полей были созданы с помощью Редактора Полей.

Для доступа к значениям полей используются те же их свойства Value, AsString, Aslnteger. Точно так же, как в Table, можно осуществлять навигацию по набору данных, устанавливать фильтры, ограничивать вводимые значения полей, кэшировать изменения.

Из свойств, отличных от Table, остановимся на свойстве DataSource. Это свойство позволяет строить приложения, содержащие связанные друг с другом таблицы. Рассмотрим, как это делается, на примере. Построить приложение, включающее в себя таблицу Dep, содержащую список отделов (в поле Dep) и их характеристику, в качестве головной таблицы и таблицу персонала Pers, содержащую в поле Dep имя отдела, в котором работает каждый сотрудник. Нужно, чтобы при выборе записи в таблице Dep в таблице Pers отбирались записи, относящиеся к выбранному отделу.

Поместим на форму компоненты Query1, DataSource1, DBGrid1 и соединим их обычной цепочкой: в DBGrid1 задайте свойство DataSource равным DataSource1, а в DataSource1 задайте свойство DataSet равным Query1. Компонент Query1 настроим на таблицу Dep. Для этого установим свойство DatabaseName (например, dbP), а в свойстве SQL напишем оператор

Select * from Dep

Установим свойство Active в true и в DBGrid1 должно отобразиться содержимое таблицы Dep.

Создадим другую аналогичную цепочку, перенеся на форму компоненты Query2, DataSource2, DBGrid2, и свяжем ее с таблицей Pers запросом

Select * from Pers в компоненте Query2. Установим свойство Active компонента Query2 в true и в DBGrid2 отобразится содержимое таблицы Pers.

Пока таблицы независимы. Теперь нужно связать эти таблицы. Делается это следующим образом. Измените текст запроса в свойстве SQL вспомогательного компонента набора данных Query2 на

Select * from Pers where (Dep=:Dep)

В этом запросе указано условие отбора: значение поля Dep должно быть равно параметру :Dep. Далее в свойстве DataSource компонента Query2 надо сослаться на DataSource1 - источник данных, связанный с таблицей Dep. Это скажет приложению, что оно должно взять значения параметра :Dep из текущей записи этого источника данных. А поскольку имя параметра совпадает с именем поля в источнике данных, то в качестве значения параметра будет взято текущее значение этого поля. Таким образом, вспомогательная таблица, запрашиваемая в Query2, оказывается связанной с головной таблицей, запрашиваемой в Query1.

После изменения содержимого свойства SQL свойство Active компонента Query2 сбросится в false. Нужно установить его в true и запустить приложение. Увидим, что при перемещении по первой таблице во второй отображаются только те записи, которые относятся к отделу, указанному в текущей записи первой таблицы.

6. Основные методы компонента Query

К основным методам Query можно отнести методы открытия и закрытия соединения с базой данных.

Метод Close закрывает соединение с базой данных, переводя свойство Active в false. Этот метод надо выполнять перед изменением каких-то свойств, влияющих на выполнение запроса или на отображение данных.

Метод Open открывает соединение с базой данных и выполняет запрос, содержащийся в свойстве SQL. - применим только в случае, если запрос сводится к оператору Select. Если же запрос содержит другой оператор, например, Update или Insert, то при выполнении Open будет генерироваться исключение EDatabaseError.

В некоторых случаях приложению требуется получить список имен полей таблицы, связанной с Query. Это может быть сделано методом GetFieldNames, который загружает список имен полей в любую переменную типа TStrings, передаваемую в него в качестве аргумента. Например, оператор

Query1->GetFieldNames(ComboBox1->Items) ;

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

 

Кэширование изменений, совместное применение Query и UpdateSQL

Часто удобнее кэшировать изменения (хранить их временно в памяти), а после того, как все изменения и проверки сделаны, переслать их в базу данных или отменить все сделанные исправления.

Это делается так же, как и для компонента Table: устанавливается в true свойство CachedUpdates компонента Query и применяются методы ApplyUpdates для записи изменений в базу данных, метод CancelUpdates для отмены изменений и метод CommitUpdates для очистки буфера кэша.

Но режим кэширования позволяет также подключить в приложение компонент UpdateSQL. Этот компонент(страница Data Access) позволяет модифицировать наборы данных, открытые в режиме только для чтения. Это особенно важно для наборов данных, открываемых Query с запросом Select, поскольку Select создает таблицу только для чтения.

Построим приложение, демонстрирующее режим кэширования. Поместим на форму компоненты Query1, Datasource1, DBGrid1, DBNavigator, две кнопки Фиксация и Отмена, индикатор Кэширование, который  переключает режим кэширования. Кнопка Фиксация фиксирует все сделанные изменения в базе данных. Кнопка Отмена отменяет сделанные изменения. Когда приложение закрывается, надо проверить, не работало ли оно в режиме кэширования и не было ли сделано изменений, которые не зафиксированы в базе данных. Если были, то следует спросить пользователя о необходимости их сохранения и при положительном ответе зафиксировать изменения.

В свойстве SQL компонента Query1 запишите «Select * from Pers» и установите свойство CachedUpdates в true.

Запустите приложение, и увидите, что оно не работает. Отредактировать запись или вставить новую запись невозможно. А из кнопок навигатора доступны только кнопки навигации. Причина всего этого была указана ранее - Query с запросом Select создает таблицу только для чтения.

Поместите компонент UpdateSQL. Чтобы связать его с приложением, установите в компоненте Query1 свойство UpdateObject равным UpdateSQL1, выбрав из выпадающего списка этого свойства.

Далее нужно задавать следующие свойства компонента UpdateSQL1: DeleteSQL, InsertSQL и ModifySQL. Они содержат соответственно запросы, которые должны выполняться при удалении, вставке или модификации записи. Эти запросы можно записать обычным образом, вручную, или воспользоваться редактором UpdateSQL, который вызывается двойным щелчком на UpdateSQL. Первая страница Options Редактора UpdateSQLсодержит два окна: Key Fields и Update Fields.

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

В окне Update Fields надо выделить поля, значения которых будут задаваться при модификации или вставке записи (кроме вычисляемых полей).

После этого нужно нажать кнопку Generate SQL. После этого отобразится вторая страница SQL редактора UpdateSQL, на которой можно просмотреть сгенерированные запросы для модификации (Modify), вставки (Insert) и удаления (Delete) записи. После щелчка на ОК эти запросы перенесутся в свойства DeleteSQL, InsertSQL и ModifySQL, где можно их дополнительно отредактировать.

При всех выделенных полях запрос ModifySQL будет иметь вид:

update Pers

set

Num =:Num, Dep =:Dep, Fam =:Fam, Nam =:Nam, Par =:Par, Year_b = :Year_b, Pol = :Pol, Charact =:Charact, Photo = :Photo

where   Num = :OLD_Num and Dep = :OLD_Dep and Fam = :OLD_Fam and

Nam = :OLD_Nam and Par = :OLD_ Par and Year b = :OLD_Year_b and

Pol = :OLD_Pol and Charact = :OLD_Charact and Photo  = :OLD_Photo

В нем в разделе Set указана установка всех полей в значения, задаваемые соответствующими параметрами с именами, тождественными именам полей -  включаются те поля, которые выделены в окне Update Fields редактора UpdateSQL. Заполнение этих параметров при выполнении соответствующих команд приложения вам не потребуется: все это сделают автоматически методы компонента UpdateSQL. В разделе Where содержатся условия, по которым идентифицируется модифицируемая запись. В этих условиях используются параметры с именами, тождественными именам полей, но с префиксом OLD_. Эти параметры - прежние значения соответствующих полей, которые были получены компонентом до модификации записи. В условия Where включены те поля, которые вы выделили в окне Key Fields редактора UpdateSQL.

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

Для того чтобы запросы компонента UpdateSQL срабатывали, все объекты полей, имена которых в них используются, должны быть или автоматически сгенерированы (т.е. без применения в компоненте Query Редактора Полей), или введены в Редакторе Полей. В противном случае при попытке выполнить запрос будет выдано сообщение: «Field ... is of an unknown type» (поле ... неизвестного типа) и запрос не выполнится.

Предупреждение. В запросах компонента UpdateSQL должны фигурировать только те поля, которые введены вами в Редакторе Полей компонента Query, или не должны пользоваться в Query Редактором Полей.

Посмотрим на приведенный выше сгенерированный запрос ModifySQL с точки зрения нашего приложения. Прежде всего очевидно, что из условия запроса where надо удалить поля Charact и Photo, так как сравнение полей такого типа бессмысленно. Вообще достаточно оставить сравнение только по полю Num, поскольку это поле обеспечивает уникальность каждой записи. Кроме того, из раздела set надо исключить поле Num, так как оно типа Autoincrement, а значения полей этого типа устанавливать нельзя: они автоматически нарастают с каждой новой записью. Имеет также смысл исключить поля Charact и Photo, так как они в приложении не отображаются. Таким образом, запрос ModifySQL имеет смысл оставить в виде:

update Pers

set

Dep =:Dep, Fam =:Fam, Nam =:Nam, Par =:Par, Year b = :Year, Pol = :Pol where Num =OLD_Num

Запрос в свойстве DeleteSQL строится по такому же принципу. Его можно записать в виде

delete from Pers where Num = :OLD_Num

Запрос InsertSQL, построенный при выделении всех полей имеет вид:

insert into Pers

(Num, Dep, Fam, Nam, Par, Year_b, Pol, Charact, Photo) values

(:Num, :Dep, : Fam, :Nam, :Par, : Year_b, :Pol, :Charact, :Photo)

Здесь желательно убрать задание полей Num, Charact и Photo, так как они не фигурируют в приложении. В итоге запрос приобретет следующий вид:

insert into Pers

(DEP, FAM, NAM, PAR, YEARJ3, POL) values

(:DEP, :FAM, :NAM, : PAR, :YEAR_B, :POL)

Тогда обработчик события OnClick кнопки Фиксация примет вид:

Query1->ApplyUpdates() ;

Query1->CommitUpdates();

Query1->Close();

Query1->Open() ;

modif = false;

 

В него добавлены операторы закрывания (Close) и открывания (Open) соединения с базой данных компонента Query1. Это желательно сделать, чтобы в Query1 отобразилось новое состояние базы данных после внесенных в нее изменений. Если не предусмотреть этого, то, например, будет невозможно вставить в сеансе работы новую запись, подтвердить изменение, а затем удалить эту запись. Дело в том, что если не обновили данные в Query1, то в этих данных отсутствует вставленная в данном сеансе работы запись. И, следовательно, ее не удастся удалить.

Запустите теперь приложение, увидите, что можно редактировать записи, удалять, вставлять новые записи, управлять кэшированием.

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

Метод Apply(Db::TUpdateKind UpdateKind) обеспечивает загрузку значений всех необходимых параметров и выполнение одного из запросов компонента UpdateSQL. Какого именно - определяется параметром UpdateKind, который может принимать значения ukModify, uklnsert или ukDelete, что соответствует выполнению запроса, хранящегося в свойствах ModifySQL, InsertSQL и DeleteSQL.

Например, оператор UpdateSQLl->Apply(ukModify) вызовет выполнение запроса, содержащегося в свойстве ModifySQL.

Если нужный запрос SQL не содержит параметров, то эффективнее вызвать метод ExecSQL компонента UpdateSQL:

ExecSQL(Db::TUpdateKind UpdateKind) который тоже обеспечивает выполнение указанного запроса, но без предварительной загрузки значений параметров.

Метод SetParams(Db::TUpdateKind UpdateKind) обеспечивает загрузку значений параметров указанного запроса SQL без его выполнения. Таким образом, метод Apply - это просто последовательное выполнение методов SetParams и ExecSQL.

 

Формирование произвольных запросов SQL

Часто в приложениях пользователю бывает необходимо формировать любые запросы к базе данных. В таких приложениях без языка SQL и, соответственно, без компонентов Query не обойтись. Рассмотрим на примере, как строить подобные приложения.

Приложение позволяет пользователю сформировать различные запросы Select к таблице Pers. Выпадающий список Поля (типа TComboBox, в программе назван CBFilds) заполнен именами полей, которые пользователь может выбирать из него при формировании запроса. Выпадающий список Операции, знаки (типа TComboBox, в программе назван СВОр) заполнен символами допустимых операций, функций и знаков, которые пользователь также может выбирать при формировании запроса. Окно, расположенное ниже кнопок, является компонентом типа ТМеmо (в программе названо MSQL). Это окно служит для отображения формируемого запроса SQL. Ниже расположена таблица DBGrid1 типа TDBGrid, которая через компонент DataSource связана с компонентом Query типа TQuery. Этот компонент и выполняет сформированные пользователем запросы.

Кнопка Новый запрос начинает формирование запроса, посылая в MSQL текст «Select ». Затем пользователь может вводить имена полей или непосредственно в текст, или, чтобы не ошибиться, выбирая их имена из Списка Поля. При вводе совокупных характеристик или вычисляемых полей пользователь может брать необходимые символы и ключевые слова из списка Операции, знаки. При формирования запроса пользователь может щелкнуть на кнопке Условие (в запрос вводится элемент WHERE), на кнопке Порядок (в запрос вводится элемент ORDER BY) или кнопке Группировка (в запрос вводится элемент GROUP BY) и сформировать условия отбора, сортировки, группирования. После того, как запрос сформирован, пользователь выполняет его щелчком на кнопке Выполнить и в окне отображаются результаты запроса.

Ниже приведен полный текст данного приложения.

// Перечислимый тип, определяющий режим работы в каждый момент времени

ermm TRegim {RNone, RFields, RWhere, ROrder, REnd) Regim;

void _fastcall TForml::ADDS(String s)

{

// Добавление в конец последней строки в Memo новой строки s

MSQL->Lines->Strings(MSQL->Line3->Count-l] =MSQL->Lines->Strings[MSQL->Lines->Count-l] + s;

}

void _fastcall TForml::CBFieldsChange(TObject *Sender)

{

if ((Regim == REnd)||(MSQL->Lines->Count < 1))

ShowMessage("Начните новый запрос или вводите оператор вручную");

else ADDS(" "+CBFields->Text);

}

void _fastcall TForml::FormCreate(TObject *Sender)

{

// Загрузка в выпадающий список имен полей таблицы

Query->GetFieldNames(CBFields->Items) ;

CBFields->Items->Insert(0, "*") ;

CBFields->ItemIndex = 0;

CBOp->ItemIndex = 0;

Regim = RNone;

}

void _fastcall TForml::BbeginClick(TObject *Sender)

{

MSQL->Clear();

MSQL->Lines->Add("Select ");

Regim = RFields;

}

void _fastcall TForml::BexecClick(TObject *Sender)

{

if (Regim == RNone)

{

ShowMessage("Вы не ввели запрос");

return;

}

 if (Regim == RFields)

ADDS(" FROM PERS");

Regim = REnd;

Query->SQL->Assign(MSQL->Lines);

Query->Open() ;

}

void _fastcall TForml::BWhereClick(TObject *Sender)

{

if (Regim == RFields)

ADDS(" FROM PERS");

ADDS(" WHERE");

Regim = RWhere;

}

void _fastcall TForml::CBOpChange(TObject *Sender)

{

if ((Regim == REnd) || (MSQL->Lines->Count < 1))

ShowMessage("Начните новый запрос или вводите оператор вручную");

else ADDS(" "+CBOp->Text);

}

void _fastcall TForml::BOrderClick(TObject *Sender)

{

if (Regim == RFields)

ADDS(" FROM PERS");

ADDS(" ORDER BY");

Regim = ROrder;

}

void _fastcall TForml::BGroupClick(TObject *Sender)

{

if (Regim == RFields)

ADDS(" FROM PERS");

ADDS(" GROUP BY");

Regim = ROrder;

}

В начале вводится переменная Regim перечислимого типа:

enum TRegim (RNone, RFields, RWhere, ROrder, REnd) Regim;

возможные значения которой определяют, в каком режиме в данный момент находится приложение. В начале значение переменной Regim задается равным RNone. Обработчики всех кнопок проверяют значение переменной Regim и в зависимости от результатов производят те или иные операции. Кроме того, они изменяют значение этой переменной, сообщая приложению, какие операции выполняет пользователь. Например, обработчик щелчка кнопки Выполнить в процедуре BexecClick проверяет Regim и, если значение этой переменной оказывается RNone, это означает, что пользователь, не нажав до этого ни одной кнопки, сразу щелкнул на кнопке Выполнить. В этом случае пользователю выдается замечание «Вы не ввели запрос» и обработка события прерывается. Соответствующие проверки режима можно увидеть и в других обработчиках событий.

Далее в приложении вводится процедура ADDS, которая добавляет заданную текстовую строку в конец текста, содержащегося в MSQL. Это сделано просто во избежание повтора входящего в. эту процедуру оператора во многих местах кода. Конечно, при этом в заголовочном файле модуля добавляется в описание класса соответствующее объявление этой процедуры: void _fastcall ADDS(String s);

В процедуре FormCreate производится загрузка списка CBFields значениями имен полей в таблице методом GetFieldNames, который загружает список имен полей в любую переменную типа TStrings, передаваемую в него в качестве аргумента.

Остальные процедуры приложения специальных комментариев не требуют