Ревунков Г.И. - Методические указания к лабораторному практикуму по курсу «Базы данных», страница 16

Можно определять арифметические операции операции для некоторых полей.

Добавив условие where , можно указать, какие записи вас интересуют:

select *

from customer

where cust_no = 100

После выполнения этой команды будет выбрана одна запись - та, которая относится к покупателю с номером 100. В выражении where может быть указано несколько критериев, объединенных оператором and, or или not, например :

select *

from cusomer

where cult_id = 100 or cust_id = 200

В критериях отбора могут использоваться функции, а также выполняться стандартные операции, такие как +, -, >, <, =, <>, >= и <=.

Кроме того, существует еще несколько специальных операторов SQL:

• is null - проверяет, задано ли значение поля;

• in <список> - возвращает True, если значение входит в список;

• between <min> and <max> обозначает значение, содержащееся в заданном диапозоне.

Вот пример использования этих операторов:

select *

from customer

where address is not null and cust_id between 100 and 150

еще одной полезной операцией является выражение like, используемое для сравнения строк. Например, если вы хотите просмотреть все фамилии, начинающиеся с буквы B, это можно сделать следующим образом:

select *

from customer

where fio like "B%"

Символ % обозначает любой набор символов; его можно использовать не только в конце, но и в середине строки. В следующем выражении ищутся все фамилии, начинающиеся с К и заканчивающиеся на О:

select *

from customer

where upper(fio) like "К%О"

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

Данные, возвращаемые выражением select, можно отсортировать при помощи оператора order by:

select *

from customer

order by fio

Для упорядочивания по возрастанию/убыванию используются операторы asc/desc. По умолчанию упорядочивание происходит по возрастанию.

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

select distinct city

from customer

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

• avg - вычисляется среднее значение по столбцу результирующего набора (применяется только для числовых полей);

• count - вычисляется количество элементовв результирующем наборе, то есть количество элементов, удовлетворяющих заданным условиям;

• min и max - вычисляются минимальное и максимальное значения по столбцу результирующего набора;

• sum - вычисляется сумма значений по столбцу результирующего набора (применяется только для числовых полей).

Эти функции применяются для результирующего набора, обычно к отдельному его полю, исключая пустые значения (null) .

Например, необходимо вычислить среднюю заработную плату преподавателей:

select avg(salary)

from teacher

Функции, вычисляющие итоговые значения, не могут находится в выражении where . Они должны помещаться в специальное выражение having:

select min(salary), department

from employee

having min(salary) < 3000

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

Например, определить среднее и максимальное жалованье сотрудников по отделам:

select avg(salary), max(salary), departament

from employee

group by departament

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

Этот прием заключается в использовании одного выражения select внутри другого.

Например, необходимо найти сотрудника с наибольшей заработной платой:

select fio

from employee

where salary = (select max(salary) from employee)

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

Операция по слиянию данных из нескольких таблиц названа обединением последних - table join.

Внутреннее (inner) объединение таблиц удобно применять, когда между ними существует отношнение "один к одному" (когда запись одной таблицы соотносится только с одной записью другой таблицы). Стандартный синтаксис для внутреннего объединения имеет вид:

select *

from <table1> left join <table2>

on <table1.keyfield>=<table2.externalkey>

Либо можно использовать вариант:

select *

from <table1>, <table2>

where <table1.keyfield> = <table2.externalkey>

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

Внешнее (outer) объединение таблиц задается следующим выражением :

select *

from <table1> left outer join <table2>

on <table1.keyfield> = <table2.keyfield>

Применяются объединения типа декартова произведения - cartesian-join. В этом случае отсутствует условие where и каждая запись table1 объединяется с каждой записью table2.

Множественное объединение - multi-join, когда объединяется более двух таблиц.

Самообъединение self-join, в этом случае таблица объединяется сама с собой.

О п е р а т о р i n s e r t

Оператор insert используется для добавления новых строк в таблицу или ее представление. Например:

insert into employee (emp_id, fio, salary)

values (10, "Petrov", 10000)

или можно вставлять в таблицу результирующий набор:

insert into <table> <выражение select>

О п е р а т о р u p d a t e

Оператор update обновляет одну или несколько строк таблицы или ее представления.

Например:

update employee

set salary = 5000

where emp_id = 47

или

update employee

set salarry = salary * 1.05

where hiredate <"01-01-1995"

О п е р а т о р d e l e t e

Оператор delete используется для удаления строк из таблицы. Например:

delete from employee

where empid = 28

Примечание

1. В Delphi для компонента Query с помощью свойства RowsAffected можно узнать, сколько записей было изменено в операторе update или сколько записей было удалено в операторе delete.

2. При некорректной формулировке условия в выражении where оператора update может произойти непреднамеренное изменение многих строк в таблице. При этом Delphi никаких сообщений об ошибке не сгенирирует.

3. В InterBase при использовании оператора like для индексированного поля серверу не всегда удается использовать индекс, что замедляет поиск. В этом случае для организации поиска по началам строк вместо оператора like лучше использовать операторы starting with - "начинающийся с". Поиск ускоряется за счет использования индекса. Можно использовать также оператор containing - "содержащий".

4. При получении с помощью функций итогового значения по данным таблицы целесообразно создавать псевдоним для результата в случае, если к нему в дальнейшем в программе Delphi потребуется доступ. Псевдоним для результата создается с помощью ключевого слова as:

select min(salary) as minsalary, department

from employee

having min(salary) < 3000

3.2. РАБОТА С ПОЛЯМИ В DELPHI

В последующих разделах используется понятие набор данных (НД). Оно отличается от понятия набора, которое было рассмотрено выше. Под набором данных (НД) понимается группа записей из одной или нескольких таблиц базы данных (ТБД), доступная для использования с помощью компонентов-источников данных TTable, TQuery или TStoredProc.

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

Для вызова редактора полей нужно дважды щелкнуть по установленному на форме компоненту-источнику или щелкнуть по нему правой кнопкой мыши и выбрать Fields Editor.

Предварительно источник должен быть связан с нужной ТБД: в TTable должны быть определены свойства DatabaseName и TableName, в TQuery - DatabaseName и SQL (т. е. это поле должно содержать текст SQL-запроса, который создаст НД), в TStoredProc - DatadaseName и StoredProcName.

Чтобы добавить объекты-поля, необходимо щелкнуть по окну редактора правой кнопкой и выбрать Add Fields. В появившемся списке будут выделены поля, для которых еще не созданы объекты. Можно выбрать любую комбинацию полей в списке Add Fields и нажать кнопку OK. Для выбранных полей будут созданы объекты, а окно редактора будет содержать список этих полей.

Чтобы удалить определение какого-либо объекта, нужно выбрать соответствующее поле в окне редактора и нажать клавишу Delete.

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

В Delphi имеется возможность использовать при работе с наборами данных (НД) или все поля, опреде¬ленные в реляционной таблице базы данных (ТБД), или только часть существующих полей.

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

По умолчанию для каждого компонента-набора доступны все поля НД. К полю в этом случае можно обращаться с помощью метода FieldByName компонента-набора:

function FieldByName(const FieldName: String): TField

или через его свойство

property Fields[Index]: TField.

Для удобства работы с конкретным полем можно создать объект-поле класса TField или одного из его специализированных потомков TStringField, TIntegerField, TBIobField и т. д.

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

Редактор полей присваивает объектам-полям имена путем сцепления имени источника данных и имени поля. Если, например, источник Tablel связан с НД, у которого определен объект для поля Town, объекту будет присвоено имя Tablel Town, и мож¬но использовать прямое обращение к нему. Например, эти операторы эквивалентны:

Tablel.FieldByName('Town').AsString := 'Санкт-Питербург';

Tablel.Fields[0].AsString := ' Санкт-Питербург ';

TablelTown.AsString := ' Санкт-Питербург ';

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

property DefaultFields: Boolean.

Значение True указывает, что используются поля по умолчанию; False - что используются поля, определенные при помощи редактора полей.

Если хотя бы для одного поля НД создан объект-поле, все поля НД, для которых такие объекты не определены, становятся недоступными. К «несуществующим» полям обратиться из данного НД нельзя. Такие попытки будут возбуждать исключительные ситуации с сообщением

Field <имя> not found.

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

3.2.1. Типы полей

Поля в таблицах баз данных различаются по типу - символьные, целочисленные, логические, blob-поля и т.д. Для каждого типа данных есть соответствующий спе¬циализированный потомок. TField есть родительский тип, определяющий базовые свойства и методы для своих потомков - типизированных полей.

TField

TBIobField Большой двоичный объект.

TGraphicField Графическое поле (работает с содержимым blob-поля как с графическим изображением)

TMemoFieid Мемо-поле (интерпретирует BLOB-поле как большой текст).

TBooleanFieid Логическое поле.

TBinaryFieid Нетипизированное двоичное поле.

TBytesFieid Байтовые значения фиксированной длины.

TVarBytesFieid Байтовые значения переменной длины.

TDatefimeField Дата И Время.

TDateField ТОЛЬКО Дата.

TTimeField ТОЛЬКО Время.

TNumericField Числовые значения.

TBCDFieid ДСД-значения.

TFioaTField Вещественные значения.

TCurrencyFieid Денежные значения.

TintegerFieid Целочисленные значения.

TAutoincFieid Автоинкрементное целочисленное значение.

TSmallIntField Tипа SmallInt.

TWordField Типа Word.

TStringField Типа String.

К значению поля можно обратиться при помощи свойств Value и AsXXXX.

Свойство property Value: Variant возвращает значения следующих типов:

• Variant - Все компоненты

• String - TStringField, TBIobField

• Longint - TAutoincFieid, TintegerFieid, TSmallintField, TWordField

• Double - TBCDFieid, TCurrencyFieid, TFloatField