Просмотр структуры компонентов БД
Просмотр структуры компонентов БД (метаданных) осуществляется с помощью элемента меню View. Выбрав в выпадающем меню единственный элемент Metadata Information, можно затем указать компонент БД, информацию о котором необходимо получить(рис. 18.6).
Например, получим информацию о всех хранимых процедурах, объявленных в БД. Для этого в выпадающем списке выберем Procedure (поле View Information On), а поле Object Name оставим пустым (рис. 18.7) и нажмем кнопку Ok. В результате получим список хранимых процедур, объявленных в БД (рис. 18.8)
Ввод в поле Object Name имени конкретного компонента БД (в данном случае процедуры RASHOD_TOVARA) приведет к выводу тела процедуры и списка параметров (рис. 18.9).
Просмотр структуры БД
Полную структуру базы данных (все метаданные БД) можно получить, выбрав элемент меню Extract.
Выбор в выпадающем меню режима SQL Metadata for Database приведет к выдаче всех метаданных БД (в том виде, в котором их интерпретировал InterBase (рис. 18.10). Например, можно заметить, что внешние ключи, объявленные внутри оператора CREATE TABLE, преобразуются к ALTER TABLE...ADD FOREIGN KEY. Для получения данных за пределами окна воспользуйтесь полосой прокрутки. Перед выдачей метаданных поступит предложение сохранить их в файле на диске.
Выбор в выпадающем меню режима SQL Metadata for Table и выбор имени соответствующей таблицы приведут к выдаче метаданных по конкретной таблице. Перед выдачей метаданных поступит предложение сохранить их в файле на диске.
Установка режимов работы WISQL
Режимы работы текущей сессии WISQL можно определить, выбрав элемент меню Settings \ Basic Settings (рис. 18.11).
Auto Commit DDL
определяет, следует ли автоматически завершать транзакцию для операторов языка определения данных (Data Definition Language), например, CREATE TABLE, DROP PROCEDURE и т.д. По умолчанию установлено автоматическое подтверждение операторов DDL.Display Query Plan
определяет, показывать или нет план выполнения запроса на чтение данных из БД, т.е. какие реальные индексы использует InterBase для выполнения операторов SELECT (рис. 18.12).Display Statistics
определяет, выводить или нет после каждого запроса системную информацию, например:SELECT R.DAT_RASH,R.TOVAR, (R.KOLVO * Т.ZENA) AS STOIM FROM RASHOD R, TOVARY T
WHERE R.TOVAR = T.TOVAR
DAT_RASH TOVAR STOIM
10-JAN-1997 Сахар 80
10-JAN-1997 Сахар 2036
10-JAN-1997 Ставрида консерв. 15000
10-JAN-1997 Кока-кола 12000
20-JAN-1997 Сахар 120
20-JAN-1997 Кока-кола 60
20-JAN-1997 Кока-кола 3000
10-JAN-1997 Кока-кола 300
Current memory = 346112
Delta memory = -1024
Max memory = 352256
Elapsed time= 0.06 sec
Buffers = 256
Reads = 0
Writes 0
Fetches = 53
Display
in List Formal определяет, включен ли показ данных в режиме списка, то есть показ записей не по горизонтали, а по вертикали:SELECT R.DAT_RASH,R.TOVAR, (R.KOLVO * T.ZENA) AS STOIM
FROM RASHOD R, TOVARY T
WHERE R.TOVAR = T.TOVAR AND
R.KOLVO > 1000
DAT_RASH 10-JAN-1997
TOVAR Ставрида консерв.
STOIM 15000
DAT_RASH 10-JAN-1997
TOVAR Кока-кола
STOIM 12000
По умолчанию данный режим отключен.
Display Row Count
включает и отключает показ счетчика строк при выдача результата запроса:SELECT R.DAT_RASH,R.TOVAR, (R.KOLVO * T.ZENA) AS STOIM
FROM RASHOD R, TOVARY T
WHERE R.TOVAR = T.TOVAR AND
R.KOLVO > 1000
DAT_RASH TOVAR STOIM
10-JAN-1997 Ставрида консерв. 15000
10-JAN-1997 Кока-кола 12000
Records affected: 2
По умолчанию данный режим отключен.
Display Time Datatype
включает и выключает режим показа времени в значениях типа DATE, где, как известно, хранятся дата и время одновременно:SELECT R.DAT_RASH,R.TOVAR, (R.KOLVO * T.ZENA) AS STOIM
FROM RASHOD R, TOVARY T
WHERE R.TOVAR = T.TOVAR AND
R.KOLVO > 1000
DAT_RASH TOVAR STOIM
10-JAN-1997 00:00:00 Ставрида консерв. 15000
10-JAN-1997 00:00:00 Кока-кола 12000
По умолчанию данный режим отключен.
Выполнение Script-файлов
Текст SQL-запросов может быть оформлен в виде файла и затем выполнен (элемент меню File [ Run an ISQL Script}. Преимущество такого подхода очевидно в тех случаях, когда необходимо периодически выполнять повторяющиеся последовательности операторов. Операторы создания БД, таблиц, процедур, триггеров и т.д. также могут выполняться из отдельного Script-файла.
Для случая создания в Script-файле хранимых процедур и триггеров необходимо применять оператор
SET TERM НовыйРазделитель;
Необходимость его применения связана с тем, что стандартным разделителем SQL-операторов является точка с запятой ';'. В WISQL этот разделитель можно опускать, а вот в SQL-скрипте разделитель обязателен. Как известно, в теле хранимых процедур и триггеров операторы разделяются таким же разделителем:
CREATE PROCEDURE FIND_MAX_KOLVO (IN_TOVAR VARCHAR(20))
RETURNS(MAX_KOLVO INTEGER) AS
BEGIN
SELECT MAX(KOLVO)
FROM RASHOD
WHERE TOVAR = : IN_TOVAR
INTO : MAX_KOLVO;
SUSPEND;
END;
Поэтому перед выполнением оператора CREATE PROCEDURE или CREATE TRIGGER устанавливают новый разделитель, завершают им одно или несколько идущих подряд определений процедур и триггеров, а затем восстанавливают старый разделитель. Например,
SET TERM ### ;
CREATE PROCEDURE FIND_MAX_KOLVO (IN_TOVAR VARCHAR(20))
RETURNS(MAX_KOLVO INTEGER) AS
BEGIN
SELECT MAX(KOLVO)
FROM RASHOD
WHERE TOVAR = : IN_TOVAR
INTO : MAX_KOLVO;
SUSPEND;
END ###
SET TERM ; ###
При вводе и выполнении SQL-операторов непосредственно в окне WISQL, автоматически формируется последовательность операторов
SET TERM ^;
SET TERM ;^
Пример.
Рассмотрим Script-файл 'CR_DB.SQL', осуществляющий создание БД и в ней нескольких таблиц, процедур и триггеров. Перед созданием БД ее старая версия уничтожается и выполняется оператор форматаSET NAMES
НаборСимволов; а именно SET NAMES WIN1251;Он аналогичен по действию установке набора символов WIN1251 в Session | Advanced Settings WISQL.
Содержимое Script-файла:
set names WIN1251;
connect "d:\book\ib_sklad\ib_sklad.gdb" USER "SYSDBA"
PASSWORD "masterkey";
drop database;
create database "d:\book\ib_sklad\ib_sklad.gdb" USER "SYSDBA"
PASSWORD "masterkey"
default character set WIN1251;
connect "d:\book\ib_sklad\ib_sklad.gdb" USER "SYSDBA"
PASSWORD "masterkey";
CREATE TABLE POKUPATELI(
POKUP VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
GOROD VARCHAR(12) COLLATE PXW_CYRL,
ADRES VARCHAR(20) COLLATE PXW_CYRL,
PRIMARY KEY(POKUP)
);
CREATE TABLE TOVARY(
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
ED_IZM VARCHAR(lO) NOT NULL COLLATE PXW_CYRL,
ZENA INTEGER NOT NULL,
PRIMARY KEY(TOVAR)
) ;
CREATE TABLE RASHOD(
N_RASH INTEGER NOT NULL,
DAT_RASH DATE NOT NULL,
KOLVO INTEGER NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
POKUP VARCHAR(20) COLLATE PXW_CYRL,
PRIMARY KEY(N_RASH),
FOREIGN KEY(POKUP) REFERENCES POKUPATELI,
FOREIGN KEY(TOVAR) REFERENCES TOVARY
);
SET TERM ###;
CREATE PROCEDURE FIND_MAX_KOLVO (IN_TOVAR VARCHAR(20))
RETURNS(MAX_KOLVO INTEGER) AS
BEGIN
SELECT MAX(KOLVO)
FROM RASHOD
WHERE TOVAR = : IN_TOVAR
INTO : MAX_KOLVO;
SUSPEND;
END ###
CREATE PROCEDURE POK_LIST (IN_TOVAR VARCHAR(20))
RETURNS(РОК VARCHAR(20)) AS
DECLARE VARIABLE AVG_KOLVO INTEGER;
BEGIN
SELECT AVG(KOLVO)
FROM RASHOD
WHERE TOVAR = :IN_TOVAR
INTO : AVG_KOLVO;
FOR SELECT POKUP
FROM RASHOD
WHERE KOLVO > : AVG_KOLVO
INTO : РОК
DO
BEGIN
IF (:POK IS NULL) THEN
РОК = "Покупатель не указан";
SUSPEND;
END
END ###
CREATE TABLE TOVARY_LOG(
DAT_IZM DATE,
DEISTV CHAR(3),
OLD_TOVAR VARCHAR(20),
NEW_TOVAR VARCHAR(20)
) ###
CREATE TRIGGER TOVARY_ADD_LOG FOR TOVARY
ACTIVE
AFTER INSERT
AS
BEGIN
INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_TOVAR,
NEW_TOVAR)
VALUES ("NOW","ADD","",NEW.TOVAR) ;
END ###
CREATE TRIGGER TOVARY_UPD_LOG FOR TOVARY
ACTIVE
AFTER UPDATE
AS
BEGIN
INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_TOVAR, NEW_TOVAR)
VALUES ("NOW","UPD",OLD.TOVAR,NEW.TOVAR) ;
END ###
CREATE TRIGGER TOVARY_DEL_LOG FOR TOVARY
ACTIVE
AFTER UPDATE
AS
BEGIN
INSERT INTO TOVARY_LOG(DAT_IZM, DEISTV, OLD_TOVAR, NEW_TOVAR)
VALUES ("NOW","DEL",OLD.TOVAR,"") ;
END ###
SET TERM ; ###
COMMIT;
Работа с утилитой Windows Interactive SQL (WISQL)
Утилита WISQL позволяет:
• соединяться с БД на локальном или удаленном сервере;
• выполнять любые запросы к БД и просматривать результаты их выполнения;
• получать информацию о структуре БД.
Обзор типов данных InterBase
ЗАМЕЧАНИЕ. То, что для "персональных" БД типа Paradox и dBase принято называть полями записи (fields), для InterBase, как и иных "промышленных" БД, работающих с SQL, принято называть столбцами (columns). Далее будем следовать этой устоявшейся традиции.
В ТБД InterBase могут использоваться столбцы следующих типов:
| Тип столбца | Размер, байт | Описание |
| SMALLINT | 2 | Целочисленные значения от -32768 до +32767. |
| INTEGER | 4 | Целочисленные значения от -2 147 483 647 до +2 147 483 647. |
| FLOAT | 4 | Значения с плавающей точкой до 7 знаков от 3.4Е-38 до 3.4Е+38. |
| DOUBLE
PRECISION |
8 | Значения с плавающей точкой до 15 знаков от1,7Е-308до1,7Е+308. |
| CHAR(n) или
CHARACTER |
0-32767 | Символьный столбец длиной в п символов. |
| VARCHAR(n) или CHARACTER] VARYING | 0-32767 | Символьный столбец переменной длины, содержащий до п символов. |
| DATE | 8 | Дата в пределах от 01.01.0100 до 11.12.5941. Также может хранить сведения о времени. |
| BLOB | переменный | Любой тип двоичных данных. |
Столбцы могут определяться в следующих SQL-операторах:
• CREATE TABLE - создать таблицу БД;
• CREATE DOMAIN - создать домен;
• ALTER TABLE - изменить структуру таблицы БД.
Синтаксис определения столбцов:
<тип_данных> = {
{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION} [<размерность_массива>]
| {DECIMAL | NUMERIC) [(точность [, масштаб])] [<размерность_массива>]
| DATE [<размерность_массива>]
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}
[(целое)] [<размерность_массива>] [CHARACTER SET набор_символов]
| {NCHAR I NATIONAL CHARACTER | NATIONAL CHAR}
[VARYING] [(целое)] [<размерность_массива>]
| BLOB [SUB_TYPE {целое | имя_подтипа}] [SEGMENT SIZE целое]
[CHARACTER SET набор_символов] | BLOB [(длина_сегмента [, подтип])]
}
Целочисленные значения
Столбец типа SMALLINT хранит целочисленные значения в диапазоне от -32768..+32767. При определении структуры таблицы БД в Database Desktop этот тип называется SHORT.
Столбец типа INTEGER хранит целочисленные значения в диапазоне от -2 147 483 647 до +2 147 483 647. При определении структуру таблицы БД в Database Desktop этот тип называется LONG.
Формат определения целочисленных столбцов:
<тип_данных> = {SMALLINT | INTEGER} [<размерность_массива>]
Значения с плавающей запятой
Столбец типа FLOA Т хранит значения с плавающей запятой длиной до 7 знаков в диапазоне от 3,4Е-38 до 3,4Е+38.
Столбец типа DOUBLE PRECISION хранит значения с плавающей запятой длиной до 15 знаков в диапазоне от 1,7Е-308 до 1.7Е+308.
Управлять числом знаков в дробной части числа с плавающей запятой нельзя, то есть нельзя при определении столбца БД указать фиксированное число знаков после запятой. В один и тот же столбец ТБД типа FLOAT или DOUBLE PRECISION можно поместить значения и "222.3333", и "22.33". Однако в приложении на значения таких полей можно наложить маску с точным числом знаков после запятой. Например, наложение маски '#,###.00' приведет к тому, что будут показываться только два знака после запятой. Если значение столбца имеет в дробной части более знаков, чем указано в маске, они будут округляться. Значение 100.678 в этом случае будет округлено как 100.68. Если в дробной части значения столбца меньше знаков, чем указано в маске, недостающие разряды будут дополняться нулями. Так, значение 100.2 будет показано как 100.20.
Значения указанных типов следует применять в столбцах, где число знаков в дробной части при хранении значений несущественно и где может понадобиться большая точность представляемых вещественных значений, например, до 7 (FLOAT) или до 15 (DOUBLE PRICISION) знаков после запятой.
При попытке представления числа с большим числом разрядов, чем это разрешено типом данных, хранимые данные округляются до последней значащей цифры в разрешенном разряде. Например, попытка записать в столбец типа FLOAT значение " 123.456789" приведет к запоминанию значения "123.4568".
Формат определения целочисленных значений:
<тип_данных> = {FLOAT DOUBLE PRECISION} [<размерность_массива >]
Фиксированно-десятичные значения
Типы DECIMAL и NUMERIC задают значения с плавающей запятой и определяют в них фиксированное число знаков после запятой. Формат определения этих типов
<тип_данных> ={DECIMAL | NUMERIC} [(точность [, масштаб])] [<размерность массива >]
где точность определяет число знаков в хранимом числе (максимум 15), а масштаб - число знаков после десятичной точки и может быть равен нулю. Во всех случаях масштаб должен быть меньше точности.
ВНИМАНИЕ!
Специальных столбцов типа DECIMAL и NUMERIC физически не существует; вместо этого, столбцы, описанные типами DECIMAL и NUMERIC, хранятся как INTEGER или DOUBLE PRECISION.При этом действует правило: если точность (число знаков в числе) меньше 10, то реальный тип столбца INTEGER; если точность больше или равно 10, реальный тип столбца DOUBLE PRECISION. Например:
Объявление
Реальный тип столбца
DECIMAL(5,2)
INTEGER
DECIMAL(12,2)
DOUBLE PRECISION
DECIMAL(12,0)
DOUBLE PRECISION
Известно, что в столбцах типа INTEGER дробных значений хранить нельзя. Поэтому, независимо от того, указано число знаков после десятичной точки или нет, объявление DECIMAL и NUMERIC с общим числом разрядов, меньшим 10, приведет к тому, что фактически в столбце можно будет хранить только целочисленные значения.
Пример.
Определим в ТБД 4 столбца с типами DECIMAL(5,2), DECI-MAL(12,2), DECIMAL(5,0), DECIMAL(12,0) и присвоим им всем одно и то же значение 123.4567. Результат:DECIMAL(5,2)
DECIMAL (12,2)
DECIMAL(5,0)
DECIMAL(12,0)
123
123.4567
123
123.4567
Как можно заметить, действительно существенным в объявлении типов DECIMAL и NUMERIC является общее число разрядов в числе (точность);
число знаков после десятичной точки (масштаб) существенного значения не имеет.
Значения типа даты
Столбцы типа DATE позволяют хранить значения даты в пределах от 01.01.0100 до 11.12.5941, а также, вместе с датой - и значения времени. Формат объявления:
<тип_данных> = DATE [<размерность_массива >]
Формат столбца типа DATE InterBase полностью совместим с типом TDateTime, объявленном в Delphi. Этот тип позволяет одновременно хранить в одной переменной дату и время.
Если ввод данных в столбец типа DATE производится из утилиты WISQL, значения даты должны указываться в формате InterBase. Согласно этому формату, значения даты состоят из номера дня (01-31), месяца (JAN-DEC) и года. Эти значения отделяются разделителями. Стандартным разделителем является символ '-', но принимаются и пробел ' ', правый слеш '/', точка '.'. Значения дат в InterBase должны лежать в диапазоне "1-JAN-100".." 11-DEC-5941".
Пример.
Запишем в столбец DATAP типа DATE результат выполнения функции Now, определенной в Delphi (Now возвращает текущие время и дату, установленные на компьютере). Затем в Label I.Caption поместим дату, а в Label2.Caption - время из столбца DATAP:WITH Table1 do begin
Insert;
FieldByName('DATAP').Value := Now; //дата прихода товара
FieldByName('TOVAR').Value : 'Брус'; // товар
FieldByName('Kolvo').Value :- 10; // количество
Post;
Labell.Caption := DateToStr(Fields[0].Value);
Label2.Caption := TimeToStr(Fields[0].Value);
END;//with
Результат выполнения кода приведен на рис. 20.1.
Интерпретация формата представления значений типа даты зависит от настроек - программных или операционной системы компьютера. Рекомендую всякий раз при старте приложения программно переустанавливать формат даты к привычному нам российскому формату:
procedure TForm1.FormCreate(Sender: TObject) ;
begin
DateSeparator := '.';
ShortDateFormat := 'dd.mrn. yyyy' ;
ShortTimeFormat := 'hh:mm:ss';
end;
Таким образом можно игнорировать неопределенность текущих установок операционной системы на конкретном компьютере.
ЗАМЕЧАНИЕ.
В InterBase значения типа даты совместимы с рассматриваемыми ниже строковыми типами. Поэтому, если в SQL-операторах InterBase необходимо интерпретировать значения типа даты в строку, нет необходимости в приведении типов, например, можно так записать в символьное поле S1 значение типа даты (NOW возвращает текущие дату и время):UPDATE SOMETABLE
SET S1 = "Дата отгрузки" || NOW) ;
Кроме того, присвоение значения полям типа дата также производится в символьном формате:
INSERT INTO SOMETABLE (DATA_PRIHODA, KOLVO)
VALUES("01-FEB-1997",100) ;
Символьные типы данных
Столбцы типа CHAR(n) и VARCHAR(n) позволяют хранить строковые значения длиной до n символов. В любом случае n не может превышать 32 Кб.
Различие типов CHAR(n) и VARCHAR(n) документация по InterBase определяет так:
• CHAR(n)
определяет строковые столбцы фиксированной длины. Определение "фиксированная длина" означает, что даже если в столбец записано меньше п символов, незаполненное пространство заполняется пробелами. При хранении хвостовые пробелы усекаются, что позволяет хранить действительно значащее содержимое столбцов типа CHAR(n) и сближает их со строками переменной длины. При чтении значения столбца хвостовые пробелы вставляются в столбец снова (см. замечание ниже). Такой механизм придуман для экономии дискового пространства, когда действительная длина значений данного столбца в различных записях варьируется достаточно широко.• VARCHAR(n)
определяет строковые столбцы переменной длины. Определение "переменная длина" означает, что в записи хранится ровно столько символов, сколько их имеется в значении столбца. Например, если для VARCHAR(lOO) значения столбцов для одной записи -"Петров", а для другой - "Барабанов", в первом случае хранится 6 символов, а во втором - 9.Столбцы VARCHAR(n) позволяют экономить дисковое пространство, давая возможность серверу располагать больше записей на странице БД. Это увеличивает число операций ввода-вывода, возможных для одной страницы. К недостаткам относится то, что VARCHAR(n) читаются медленнее, чем CHAR(n).
Попытка записать в столбец более чем п символов приведет к усечению лишних символов.
ЗАМЕЧАНИЕ.
Опытным путем нетрудно убедиться в том, что Delphi при работе с InterBase интерпретирует столбцы типа CHAR(n) и VARCHAR(n) как TStringField. При этом:• столбцы типа CHAR(n) всегда читаются без хвостовых пробелов;
• при занесении хвостовых пробелов в столбец (а часто это бывает необходимо) CHAR(n) всегда усекает их, в то время как VARCHAR(n), наоборот, всегда их хранит.
Формат определения символьных столбцов:
<тип_данных> = {CHAR CHARACTER CHARACTER VARYING I VARCHAR} [ (n)] [<размерность_массива >] [CHARACTER SET набор_символов]
где n определяет длину символьного столбца. Если n опущено, по умолчанию подразумевается 1.
CHARACTER SET позволяет установить кодировку путем указания имени используемого набора символов. Если CHARACTER SET опущен, используется кодировка, принятая по умолчанию для БД (устанавливается в предложение DEFAULT CHARACTER SET оператора CREATE DATABASE). Рекомендуется явно указывать CHARACTER SET целиком для таблицы, базы данных или, если в таблице наборы символов различны для разных столбцов, - для каждого конкретного столбца.
Если определен набор символов, то в столбец невозможно ввести символы, не входящие в данную таблицу кодировки.
Для представления столбцов, которые будут содержать русскоязычные тексты, рекомендуем использовать набор символов WIN1251, например:
SYMPOLE CHARACTER(20) CHARACTER SET WIN1251;
Если при создании БД был принят набор символов по умолчанию, для чего в оператор CREATE DATABASE добавлено предложение DEFAULT CHARACTER SET, то этот набор символов будет принят по умолчанию для всех столбцов. Например:
CREATE DATABASE BAZA ...DEFAULT CHARACTER SET WIN1251;
При определении столбца набор символов, принятый для БД по умолчанию, можно переопределить. Для этого и служит предложение CHARACTER SET в определении столбца. Если при объявлении столбца предложение CHARACTER SET опущено, используется набор символов, принятый по умолчанию для всей БД при ее создании. Если при создании БД набор символов по умолчанию не был определен, т.е. предложение DEFAULT CHARACTER SET отсутствовало в операторе CREATE DATABASE, по умолчанию принимается набор символов NONE. Он позволяет вводить в символьные столбцы символы любых кодировок. Данные хранятся так, как они вводятся. Однако для столбцов с набором символов NONE могут возникнуть проблемы при записи их значения в столбцы, которым набор символов явно назначен.
Порядок сортировки символов
Порядок сортировки символов определяет порядок перечисления символов для набора символов. Например, порядок сортировки PXW_CYRL для кириллического набора символа WIN1251 определяет порядок следования символов 'А','Б','В',Г',...'Я'. Известно, что для символьных значений сравнение производится по правилу: меньшим считается символ, определенный в наборе символов раньше. То есть, в нашем случае, 'А' < 'Б' и 'Анискин' меньше, чем 'Артюхин', а 'Артюхин' меньше, чем 'Бармалеев' и 'Якушев'.
Порядок сортировки символов определяет, таким образом, принцип, по которому символьные значения будут сравниваться и сортироваться в операторах SELECT (если в нем присутствуют разделы WHERE, GROUP BY), при обновлении индексов и т.д.
Порядок сортировки символов определяется предложением
COLLATE <collation order>
где collation order определяет имя порядок сортировки.
Для кириллического набора символов WIN1251 рекомендуется порядок сортировки PXW_CYRL. Например:
SYMPOLE CHARACTER (20) CHARACTER SET WIN12151 COLLATE PXW_CYRL;
Значения типа BLOB
В столбцах типа BLOB хранят данные, которые невозможно хранить в столбцах других типов. BLOB (binary large object,- большой двоичный объект) имеет переменную длину и понимается как последовательность байтов, т.е. нетипизированное, с точки зрения хранения информации, поле данных В BLOB-столбцах можно хранить графические образы, тексты большой длины, звуковые файлы, видеоизображения, мультимедиа-информацию В BLOB-столбцах также можно хранить указатели на неструктурированные файлы информации, которые расположены в ином, фиксированном месте При этом интерпретация содержимого BLOB-столбца может ложиться не только на клиентскую часть, т е. на прикладное программное обеспечение, но также и на сервер В последнем случае в серверной БД могут быть определены так называемые BLOB-фильтры, определяющие способы преобразования BLOB-значений от одного вида к другому В качестве одной из задач, могущих обусловить необходимость определения пользовательского фильтра BLOB, можно назвать преобразование изображений из одного графического формата в другой
В отличие от "персональных" (или "локальных") СУБД типа Paradox, InterBase хранит значения BLOB-столбцов не в отдельных файлах, а в самой БД. Он хранит их в виде сегментов. Одна операция ввода-вывода при доступе к BLOB-информации оперирует с одним сегментом В самой таблице БД, если в ней объявлен столбец типа BLOB, хранится указатель на начальный сегмент BLOB-столбца в области хранения BLOB-информации этой БД. Указатель на BLOB представляет собой уникальное целочисленное значение и формируется автоматически
Длина сегмента BLOB не влияет на производительность InterBase Однако, исходя из некоторых соображений, ее можно определять явно при создании BLOB-столбца
BLOBPOLE BLOB SEGMENT SIZE 1024, ...
По умолчанию длина сегмента 80; максимальная длина сегмента 32 Кбайт (32 767 байт).
Для столбца типа BLOB может быть указан подтип, определяющий, какой тип данных будет записан в этот столбец Подтип есть число. Позитивные значения зарезервированы InterBase. О (по умолчанию) для указания двоичных данных и 1 для указания ASCII-текстов Отрицательные значения могут использоваться программистами для определения собственных подтипов В общем случае указание подтипа производится через опцию SUB_TYPE
<имя столбца> BLOB SUB_TYPE 1 CHARACTER SET WIN1251 ...
Однако InterBase автоматически не отслеживает правильность занесения информации в столбцы типа BLOB. Это необходимо делать в приложениях.
Совместимость типов столбцов
При выполнении операций над столбцами следует помнить, что операции могут выполняться только над столбцами совместимых типов В случае невыполнения этого требования InterBase пытается автоматически привести типы таким образом, чтобы значения, участвующие в операции, принадлежали совместимым типам. Кроме того, для ручного приведения типов можно использовать функцию CAST, которая приводит типы внутри оператора SELECT, обычно в предложении WHERE
CAST (<значение> I NULL AS ТипДанных}
CAST может преобразовать исходные типы данных в результирующие типы
Исходные типы данных Результирующие типы данных
DATE CHAR или NUMERIC
char DATE или NUMERIC
NUMERIC DATE или CHAR
Например,
привести значение Datal (тип DATE) к типу CHAR и сравнить его со значением Date2 (типа CHAR)SELECT .....
WHERE DATE2 <= CAST (DATE1 AS CHAR);
Понятие домена
Если в таблице БД или в нескольких таблицах БД присутствуют столбцы, обладающие одними и теми же характеристиками, можно предварительно описать тип такого столбца и его поведение через домен, а затем поставить в соответствие каждому из одинаковых столбцов имя домена.
Например, создать домен POL_TYPE и затем использовать его при создании таблицы SOTR как тип столбца POL:
CREATE DOMAIN POL_TYPE AS
CHAR(3) COLLATE PXW_CYRL;
CREATE TABLE SOTR(
FIO CHAR(20) NOT NULL,
POL POL_TYPE,
OTDEL CHAR(10),
DOLJ CHAR(20),
PRIMARY KEY(FIO)
) ;
Как можно заметить, домен есть описание какого-либо столбца, то есть абстрактное понятие; как такового домена физически не существует. В языке Object Pascal сходным по назначению с определением домена является определение типа в блоке type; фактически определение типа не приводит к выделению памяти под переменную, но может использоваться при объявлении переменных в блоке var. Для приведенного выше примера, домена POL_TYPE как структуры данных физически нет, поскольку им невозможно воспользоваться для хранения данных и доступа к ним. Однако физически существует и доступен для хранения и доступа к данным столбец POL в таблице SOTR, при создании которого было использовано описание домена POL_TYPE.
Домен определяется оператором CREATE DOMAIN. Его формат:
CREATE DOMAIN домен [AS] <тип_данных>
[DEFAULT {литерал| NULL | USER}]
[NOT NULL] [CHECK (<усл_поиска_домена>)]
[COLLATE collation] ;
Предложение COLLATE позволяет указать порядок сортировки символов, например:
CREATE DOMAIN POL_TYPE AS
CHAR(3) COLLATE PXW_CYRL;
Предложение DEFAULT определяет оператор, который по умолчанию заносится в колонку, ассоциированную с доменом, при создании записи таблицы. Это значение будет присутствовать в соответствующем столбце данной записи до тех пор, пока пользователь не изменит его каким-либо образом. Значения по умолчанию могут быть выражены как:
• литерал- значение (числовое, строковое или дата);
• NULL - специфицирует пустое значение;
• USER - имя текущего пользователя.
Заметим, что значения по умолчанию, присваиваемые данному столбцу и объявленные в операторах CREATE TABLE или ALTER TABLE, переопределяют значения, присваиваемые по умолчанию тому же столбцу согласно директивам, содержащимся в CREATE DOMAIN.
Предложение NOT NULL указывает, что столбцы, ассоциированные с доменом, обязательно должны содержать какое-либо значение, отличное от пустого. Нужно следить за тем, чтобы в объявлении домена не было противоречий, например, в описании значения по умолчанию как NULL и объявлении директивы NOT NULL.
Ограничения на значения столбцов, ассоциированных с доменом
Предложение CHECK определяет требования к значениям каждого столбца, ассоциированного с доменом. Столбцу не могут быть присвоены значения, не удовлетворяющие ограничениям, наложенным в предложении CHECK. Формат ограничения, накладываемого на значения полей, ассоциированных с доменом:
<огранич_домена> = {
VALUE <оператор> <значение>
| VALUE [NOT] BETWEEN <значение1> AND <значение2>
| VALUE [NOT] LIKE <значение> [ESCAPE <значение>]
| VALUE [NOT] IN (<значение1> [, <значение2> ...]) VALUE IS [NOT] NULL
| VALUE [NOT] CONTAINING <значение>
| VALUE [NOT] STARTING [WITH] <значение>
| (<огранич домена>) | NOT <огранич домена>
| <огранич_домена> ОR<огранич_домена> | <огранич домена> AND <огранич_домена>
}
где <оператор> = {= | < | > | <= | >= | !< | !> | <> | !=}
Ключевое слово VALUE далее означает все правильные значения, которые могут быть присвоены столбцу, ассоциированному с доменом.
• VALUE <оператор> <значение>
определяет, что значение домена находится с параметром значение во взаимоотношениях, определяемых параметром оператор.Например, значение, которое может быть записано в столбец, ассоциированный с доменом ID_TYPE, должно быть больше или равно 100:
CREATE DOMAIN ID_TYPE AS INTEGER CHECK(VALUE >= 100);
• BETWEEN <значение1> AND <значение2>
определяет, что значение домена должно находиться в промежутке между значение! и значение!, включая их;• LIKE <значение1> [ESCAPE <значение2>1
определяет, что значение домена должно "походить" на параметр значение!. При этом употребляется символ '%' для указания любого значения любой длины и символ '_' (подчеркивания) для указания любого единичного символа. Например,LIKE "%USD" - вводимое значение должно оканчиваться символами 'USD', независимо от того, какие символы и сколько расположены перед ними;
LIKE "_94" - вводимое значение может содержать 4 символа, из которых первые два - любые и последние два - '94'.
ESCAPE <значение2>
используется, если в операторе LIKE символы '%' или '_' должны использоваться в шаблоне подобия. В этом случае выбирается некоторый символ, например "!", после которого символы '%', '_' входят в поисковую строку как непосредственно символы. Символ Т указывается после слова ESCAPE, например:CREATE DOMAIN SUMMA AS CHAR(lO) CHECK(LIKE "%!%" ESCAPE "!");
Значения столбца SUMMA должны заканчиваться символом "%".
• IN (<значение1 > [, <значение2>...])
определяет, что значение домена должно совпадать с одним из приведенных в списке параметров значение Х,, например:CREATE DOMAIN POL_TYPE AS CHAR(3) CHECK(VALUE IN ("Муж","Жен"));
• CONTAINING <значение>
определяет, что значение домена должно содержать вхождение параметра значение, неважно в каком месте. Например, в наименовании отдела вхождение "041" может встретиться где угодно ("Отдел-041002", "003404192", и т.д.):CREATE DOMAIN OTDEL_TYPE AS VARCHAR(lO) CHECK(VALUE CONTAINING "041") COLLATE
PXW_CYRL;
STARTING [WITH] <значение>
требует, чтобы значение домена начиналось параметром значение. Например, название отдела должно начинаться с "041":CREATE DOMAIN OTDEL_TYPE AS VARCHAR(lO) CHECK(VALUE STARTING WITH "041")
COLLATE PXW_CYRL;
• Может быть задана комбинация условий, которым должно соответствовать значение домена. В этом случае отдельные условия соединяются операторами AND или OR. Например:
CREATE DOMAIN OTDEL_TYPE AS VARCHAR(lO) CHECK(VALUE STARTING WITH "041" AND
VALUE CONTAINING "-12") COLLATE PXW_CYRL;
• Для большинства условий можно указать слово NOT, которое изменит условие с точностью до наоборот. Например: CHECK(VALUE NOT BETWEEN 1 AND 100);
Изменение определения домена
Оператор
ALTER DOMAIN имя {
[SET DEFAULT {литерал] NULL | USER}]
| [DROP DEFAULT] | [ADD [CONSTRAINT] CHECK (<огранич_домена>)] | [DROP CONSTRAINT]
};
позволяет изменить параметры домена, определенного ранее оператором CREATE DOMAIN. Однако нельзя изменить тип данных и определение NOT NULL. Следует помнить, что все сделанные изменения будут учтены для всех столбцов, определенных с использованием данного домена (в том случае, если параметры домена не были переопределены при определении столбцов таблицы или впоследствии).
SET DEFAULТ устанавливает значения по умолчанию, подобно тому, как это делается в операторе CREATE DOMAIN.
• DROP DEFAULТ отменяет текущие значения по умолчанию, назначенные домену.
• [ADD [CONSTRAINT] CHECK (<огранич_домена>)] добавляет условия, которым должны соответствовать значения столбца, ассоциированного с доменом. При этом возможно определение условий, рассмотренных выше для предложения CHECK оператора CREATE DOMAIN.
• DROP CONSTRAINT удаляет условия, определенные для домена в предложении CHECK оператора CREATE DOMAIN или предыдущих операторов ALTER DOMAIN.
Например, пусть определен домен ID_TYPE
CREATE DOMAIN ID_TYPE AS INTEGER CHECK(VALUE >= 100);
и в дальнейшем он использован при создании таблицы ААА:
CREATE TABLE ААА( ID ID_TYPE NOT NULL, FIO VARCHAR(20), PRIMARY KEY(ID));
Изменить условие, определяемое CHECK так, чтобы значение было больше или равно 100 и меньше или равно 500, можно за два такта. Сначала нужно удалить старое условие:
ALTER DOMAIN ID_TYPE DROP CONSTRAINT;
после чего добавить новое (которое на самом деле есть модифицированное старое):
ALTER DOMAIN ID_TYPE CHECK (VALUE >= 100 AND VALUE <= 500);
Заметим, что изменять определение таблицы ААА нет необходимости, и отныне в столбец ID этой таблицы можно занести значения, большие или равные 100 и меньшие или равные 500.
Общий вид оператора CREATE TABLE
Перед созданием таблиц БД необходимо продумать определение всех столбцов таблицы и характеристик каждого столбца (таких, как тип, длина, обязательность для ввода, ограничения, накладываемые на значения и пр.), индексов, ограничений целостности по отношению к другим таблицам. Если при определении столбцов используются домены, эти домены должны быть предварительно созданы оператором CREATE DOMAIN.
Та БД, в которую будет добавлена создаваемая таблица, должна быть открыта, т.е. с ней должно быть установлено активное соединение.
Создание таблицы БД осуществляется оператором
CREATE TABLE ИмяТаблицы [EXTERNAL [FILE] "<имя файла>"] (<опр_столбца> [, <опр_столбца> | <ограничение> ...]);
[EXTERNAL [FILE] "< имя файла >"]
относится к внешним, т.е. расположенным отдельно от БД, таблицам БД.• <опр_столбца> -определение столбца БД. Основные сведения об определении столбцов см. выше в разделе " Типы столбцов таблиц БД". Определение столбца имеет формат:
<опр_столбца> = опр_столбца{тип_данных | COMPUTED [BY] (<выражение>) | домен}
[DEFAULT {литерал! NULL | USER}] [NOT NULL] [<огранич_столбца>] [COLLATE collation]
где опр_столбца - имя столбца; тип_данных - тип столбца, и, возможно, размерность массива, если столбец - массив; для символьных столбцов может быть указан набор символов, отличный от принятого по умолчанию, при помощи предложения CHARACTER SET; COMPUTED [BY] (<выражете>) - служит для определения столбца вычисляемых значений (подробнее см. ниже);
domain -
имя домена, т.е. ранее описанного типа столбца;• DEFAULT определяет значение, которое по умолчанию заносится в столбец, ассоциированный с доменом, при создании записи таблицы; это значение будет присутствовать в соответствующем столбце данной записи до тех пор, пока пользователь не изменит его каким-либо образом;
значения по умолчанию (см. в п.21.1);
огранич_столбца -
ограничения, накладываемые на значения столбца (подробно рассматриваются ниже);COLLAТЕ collation
определяет порядок сортировки символов (для символьных столбцов) для набора символов, принятого по умолчанию или явно определенного предложением CHARACTER SET.Столбцы вычисляемых значений
Такие столбцы описываются как COMPUTED [BY] (<выражение>)
Значение таких столбцов не вводится пользователем, а вычисляется автоматически согласно выражению. Тип результирующего значения и будет служить типом вычисляемого столбца. Например, таблица SAL_HIST, показанная на рис. 22.1, содержит номер квартала (QUORTER), количество продаж в данном квартале, в прошлом (LAST_YEAR) и текущем году (THIS_YEAR) и прирост продаж за квартал (GROWTH). Она создана таким образом:
CREATE TABLE SAL_HIST (
QUORTER INTEGER NOT NULL,
LAST_YEAR INTEGER,
THIS_YEAR INTEGER,
GROWTH COMPUTED BY (THIS_YEAR - LAST_YEAR),
PRIMARY KEY (QUORTER)
Ограничения целостности
Ограничения целостности бывают двух уровней:
• ограничения, накладываемые на отдельный столбец;
ограничения, накладываемые на таблицу.
В случае наложения ограничений целостности на отдельный столбец, описание ограничения приводится следом за именем столбца и его типом:
TOVAR TOVAR VARCHAR(20) NOT NULL PRIMARY KEY, ...
В данном случае столбец TOVAR составляет первичный ключ, что объявлено в предложении PRIMARY KEY.
В случае наложения ограничений на таблицу, их описание указывается после объявлений отдельных столбцов, например:
CREATE TABLE . . . (TOVAR TOVAR VARCHAR(20) NOT NULL,PRIMARY KEY(TOVAR)) ;
Первичный ключ
Если по столбцу строится первичный ключ, столбцу может быть приписан атрибут PRIMARY KEY:
CREATE TABLE SAL_HIST (QUORTER INTEGER NOT NULL PRIMARY KEY,) ;
Заметим, что первичный ключ может быть построен и путем включения имени (имен) ключевого столбца (столбцов) в качестве параметров отдельного предложения PRIMARY KEY:
CREATE TABLE SAL_HIST (QUORTER INTEGER NOT NULL,PRIMARY KEY (QUORTER)) ;
Однако и в том, и в другом случае ключевой столбец (столбцы), входящий (входящие) в состав первичного ключа, должен быть помечен как NOT NULL, поскольку первичный ключ не может быть построен даже по одному пустому значению.
Первичный ключ, если он служит для обеспечения ссылочной целостности, должен корреспондировать с внешним ключом (FOREIGN KEY) другой (дочерней) таблицы. Определение ссылочной целостности между родительской и дочерней таблицами описано ниже, в подразделе " Внешний ключ и определение ссылочной целостности ".
Уникальный ключ
Атрибут UNIQUE, если он приписан столбцу, означает, что в столбце не могут содержаться два одинаковых значения. Уникальный ключ строится по столбцу (столбцам), когда столбец не входит в состав первичного ключа, но тем не менее его значение должно всегда быть уникальным. Например, для таблицы VLADLIM ("владельцы бюджетных лимитов") первичный ключ строится по коду владельца KODVLAD, введенному для сокращения объема первичного ключа и времени поиска по нему (объем ключа по столбцу типа INTEGER много меньше объема ключа по символьному полю максимальной
длиной в 50 символов). Однако и название владельца лимита NAZVVLAD должно быть уникальным, для чего ему приписан атрибут UNIQUE:
CREATE TABLE VLADLIM (
KODVLAD INTEGER NOT NULL PRIMARY KEY,
NAZVVLAD VARCHAR(50) NOT NULL UNIQUE );
Уникальность может быть приписана и на уровне таблицы:
CREATE TABLE VLADLIM (KODVLAD INTEGER NOT NULL PRIMARY KEY,
NAZVVLAD VARCHAR(SO) NOT NULL, UNIQUE (NAZVVLAD));
Столбец, объявленный с атрибутом UNIQUE, как и первичный ключ, может применяться для обеспечения ссылочной целостности между родительской и дочерней таблицами. В этом случае столбец с атрибутом UNIQUE должен принадлежать к родительской таблице и должен корреспондировать с внешним ключом (FOREIGN KEY) другой (дочерней) таблицы.
Внешний ключ и определение ссылочной целостности
Внешний ключ строится в дочерней таблице для соединения родительской и дочерних таблиц БД. Формат определения:
FOREIGN KEY (<список столбцов внешнего ключа>)
REFERENCES <имя родительской таблицы> [<список столбцов родительской таблицы>]
Список столбцов внешнего ключа
определяет столбцы дочерней таблицы, по которым строится внешний ключ.Имя родительской таблицы
определяет таблицу, в которой описан первичный ключ (или столбец с атрибутом UNIQUE). На этот ключ (столбец) должен ссылаться данный внешний ключ дочерней таблицы для обеспечения ссылочной целостности. Список столбцов родительской таблицы необязателен при ссылке на первичный ключ родительской таблицы. При ссылке в родительской таблице на столбец с атрибутом UNIQUE этот список лучше привести.Пример.
Определим две таблицы:• родительскую "Справочник товаров" SPR_TOVAR с полями TOVAR (наименование товара), ZENA_ED (цена за единицу измерения);
первичный ключ по полю TOVAR;
• дочернюю " Приход товара на склад" PRIHOD с полями ID_PRIHOD (номер прихода), DATAPRIH (дата прихода), TOVAR (товар), KOLVO
(количество прихода, ед.). Первичный ключ по полю ID_PRIHOD внешний ключ по полю TOVAR для обеспечения ссылочной целостности с таблицей SPR_TOVAR.
Тогда для определения данных таблиц в БД необходимо выполнить операторы
CREATE TABLE SPR_TOVAR(TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
ZENA_ED INTEGER NOT NULL, PRIMARY KEY(TOVAR)) ;
CREATE TABLE PRIHOD(ID_PRIHOD INTEGER NOT NULL PRIMARY KEY,
DATAPRIH DATE NOT NULL,TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
KOLVO INTEGER NOT NULL, FOREIGN KEY(TOVAR) REFERENCES SPR_TOVAR);
Теперь для таблицы SPR_TOVAR будет установлена блокировка удаления или изменения значения в столбце TOVAR записи, если в таблице PRIHOD имеются записи о приходе этого товара.
Заметим, что определение общих полей родительской и дочерней таблиц (полей связи) должно в точности совпадать. Если в родительской таблице объявить TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
а в дочерней объявить TOVAR VARCHAR(20) NOT NULL,
то из-за различий в порядке сортировки символов эти столбцы не будут фактически идентичными, из-за чего будут постоянно возникать ошибки нарушения ссылочной целостности.
ЗАМЕЧАНИЕ.
Если ссылочная целостность между таблицей Р (родительской) и С (дочерней) обеспечивается при помощи связки PRIMARY KEY - FOREIGN KEY, то InterBase запрещает:• изменять значение столбца связи в таблице Р;
• удалять запись в таблице Р,если для нее есть записи в таблице С с таким же значением поля связи.
Иными словами, связь таблиц по внешнему ключу блокирует каскадные изменения и удаления в таблицах Р и С. В том случае, если необходимо осуществлять каскадные воздействия на таблицу С при изменении (удалении) родительской записи в Р, целостность между таблицами поддерживают при помощи триггеров, а ограничение внешнего ключа удаляют. Сам внешний ключ в дочерней таблице может и не удаляться в том случае, если он используется в запросах (оператор SELECT) оптимизатором запросов InterBase. В этом случае он определяется как индекс (оператором CREATE INDEX). Об организации ссылочной целостности при помощи триггеров, а также оптимизации запросов к БД, см. ниже соответствующие разделы.
Именование ссылочной целостности
Ссылочная целостность может именоваться:
[CONSTRAINT <имя ссылочной целостности>]
FOREIGN KEY (<список столбцов внешнего ключа>)
REFERENCES <имя родительской таблицы> [<список столбцов родительской таблицы>]
Необязательное имя ссылочной целостности присутствует в системных сообщениях относительно нарушения целостности, а также может использоваться при анализе структуры БД и изменении структуры таблиц. В случае, если имя ссылочной целостности опущено, InterBase сам установит ее имя.
Например, назначим в приведенной выше таблице PRIHOD имя ссылочной целостности:
CREATE TABLE PRIHOD(
ID_PRIHOD INTEGER NOT NULL PRIMARY KEY, DATAPRIH DATE NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL, KOLVO INTEGER NOT NULL,
CONSTRAINT PO_TOVARU FOREIGN KEY(TOVAR) REFERENCES SPR_TOVAR);
Имя ссылочной целостности может назначаться также и при определении первичного ключа, и уникального набора атрибутов:
CREATE TABLE VLADLIM ( KODVLAD INTEGER NOT NULL PRIMARY KEY,
NAZVVLAD VARCHAR(50) NOT NULL, CONSTRAINT PO_NAZV UNIQUE (NAZVVLAD));
Требования к значениям столбцов
Требования к значениям отдельных столбцов могут определяться как на уровне конкретного столбца, так и на уровне таблицы. Например, для таблицы PERSON_PARAMS (параметры человека) рост (HEIGHT) должен быть больше веса (WIEGHT). Тогда данное ограничение на уровне столбца определяется следующим образом:
CREATE TABLE PERSON_PARAMS( ID INTEGER NOT NULL PRIMARY KEY,
HEIGHT INTEGER NOT NULL, WIEGHT INTEGER NOT NULL CHECK(HEIGHT >WIEGHT) ) ;
а на уровне таблицы определяется - так:
CREATE TABLE PERSON_PARAMS(ID INTEGER NOT NULL, HEIGHT INTEGER NOT NULL,
WIEGHT INTEGER NOT NULL, PRIMARY KEY(ID), CHECK(HEIGHT > WEIGHT) );
Ограничения, накладываемые на столбцы таблицы, определяются при помощи предложения CHECK, общий формат которого приводится ниже.
CHECK (<условия поиска>) <условия_поиска> = {<значение> <оператор> {<значение1> | (<выбор_одного>)}
<значение> [NOT] BETWEEN <значение1> AND <значение2>
<значение> [NOT] LIKE <значение> [ESCAPE <значение>]
<значение> [NOT] IN ( <значение1> [, <значение2> ...] |
<выбор_многих>)
<значение> IS [NOT] NULL
<значение> { [NOT] {=|<|>} I >= I <=}
{ALL ] SOME | ANY} (<выбор_многих>)
EXISTS (<выражение_выбора>)
| SINGULAR (<выражение_выбора>) | <значение> [NOT] CONTAINING <значение1>
<значение> [NOT] STARTING [WITH] <значение1>
(<условия_поиска>) NOT <условия поиска>
<условия поиска> OR <условия поиска>
<условия поиска> AND <условия_поиска>}
<значение> = {столбец | <константа> I <выражение> I <функция> | NULL I USER | RDB$DB_KEY
} [COLLATE collation]
<константа> = число | "строка"
<функция> = {
COUNT (* | [ALL] <значение> | DISTINCT <значение>)
| SUM ([ALL] <значение> | DISTINCT <значение>)
AVG ([ALL] <значение> | DISTINCT <значение>)
MAX ([ALL] <значение> | DISTINCT <значение>)
MIN ([ALL] <значение> | DISTINCT <значение>)
CAST ( <значение> AS <тип_данных>)
UPPER ( <значение>)
GEN ID (генератор, <значение>)
}
<оператор>
= {= | < | > | <= | >= | !< | !> | о | !=}. Назначение операторов:= равно;
< меньше;
> больше;
<= меньше или равно (не больше);
>= больше или равно (не меньше);
!< не меньше (больше или равно);
!> не больше (меньше или равно);
<> неравно;
!= неравно;
<выбор_одного>
= оператор SELECT, возвращающий одно значение или ни одного.<выбор_многих>
= оператор SELECT, который может возвращать более одного значения (список значений) или ни одного.<выражение_выбора>
= оператор SELECT , который может возвращать более одного значения (список значений) или ни одного.• <значение> <оператор> <значение1> определяет, что значение столбца находится со значением 1 во взаимоотношениях, определяемых оператором, который может принимать одно из следующих значений:
<оператор> = {= | < | > | <= | >= | !< | !> | <> | !=} Например, значение столбца STOLBEZ не должно быть меньше 100: CREATE TABLE TBL (CHECK(STOLBEZ >= 100););
• <значение> <оператор> (<выбор_одного>) определяет, что значение столбца находится во взаимоотношениях, определяемых оператором оператор, с результатом выполнения запроса SELECT к одной или нескольким таблицам БД, причем в качестве результата выполнения запроса возвращается единичное значение {выбор_одного).
Пример.
Пусть существует таблица TOVAR (остаток товара на складе).CREATE TABLE TOVAR (TOVAR VARCHAR(20) CHARACTER SET WIN1251 NOT NULL
COLLATE PXW_CYRL,OSTATOK INTEGER NOT NULL,PRIMARY KEY (TOVAR));
Требуется создать таблицу RASHOD (расход товара со склада) и для столбца KOLVO_R (количество расхода) предусмотреть ограничение: количество расхода данного товара не может быть больше его текущего остатка (значение столбца OSTATOK для данного значения товара) в таблице TOVAR)
CREATE TABLE RASHOD (
ID_RS INTEGER NOT NULL,
TOVAR VARCHAR(20) CHARACTER SET WIN1251 NOT NULL
COLLATE PXW_CYRL,
DATE_RASH DATE NOT NULL,
KOLVO_R INTEGER NOT NULL,
PRIMARY KEY (ID_RS),
CONSTRAINT RASH_TOVAR
FOREIGN KEY (TOVAR) REFERENCES TOVAR(TOVAR),
CONSTRAINT PO_OSTATKU
CHECK(KOLVO_R <=
(SELECT TOVAR.OSTATOK FROM TOVAR WHERE TOVAR.TOVAR = RASHOD.TOVAR))
);
• <энвчение> BETWEEN <значение1> AND <эначение2>
определяет, что значение столбца должно находиться в диапазоне от значение] до эначение2.CREATE TABLE TBL (CHECK(STOLBEZ BETWEEN 100 AND 200);) ;
• LIKE <значение> [ESCAPE <значение>]
определяет, что содержимое столбца должно "походить" на значение. При этом употребляется символ '%' для указания любого значения любой длины и символ '_' (подчеркивания) для указания любого единичного символа. Например, указание ограниченияCREATE TABLE TBL (CHECK (STOLBEZ LIKE "%USD"));
читается так: вводимое в столбец значение должно оканчиваться символами •USD', независимо от того, какие символы и сколько расположены перед ними;
указание же ограничения CREATE TABLE TBL(CHECK (STOLBEZ LIKE "_94");) ;
означает, что вводимое в столбец значение может содержать 4 символа, из которых первые два - любые и последние два - '94'.
ESCAPE <значение>
используется, если в операторе LIKE символы "%" или '_' должны использоваться в шаблоне подобия. В этом случае выбирается некоторый символ, например '!', после которого символы '%', '_' входят в поисковую строку как непосредственно поисковые символы. В этом случае символ '!' указывается после слова ESCAPE, например:CREATE TABLE TBL(CHECK(STOLBEZ LIKE "%!%" ESCAPE "!"););
<значение>
{= | < | >} | >= | <=} {ALL | SOME | ANY} (<выбор_иногих>) определяет, что значение столбца больше, меньше и т.д. всех (ALL) или некоторых (SOME или ANY), значений в списке выбор_многих. Список значений выбор_иногих выдается как результат выполнения оператора SELECT по отношению к од но и или нескольким таблицам БД.Пример.
Пусть определены таблицы TOVAR и PRIHOD:CREATE TABLE TOVAR ( TOVAR VARCHAR(20) CHARACTER SET WIN1251 NOT NULL
COLLATE PXW_CYRL, PRIMARY KEY (TOVAR) );
CREATE TABLE PRIHOD ( ID_PR INTEGER NOT NULL,
TOVAR VARCHAR(20) CHARACTER SET WIN1251 NOT NULL
COLLATE PXW_CYRL,
DATE_PRIH DATE NOT NULL,
KOLVO_P INTEGER NOT NULL,
PRIMARY KEY (ID_PR)
CONSTRAINT PRIHOF_TOVAR
FOREIGN KEY (TOVAR) REFERENCES TOVAR(TOVAR)
) ;
Определим таблицу RASHOD, у которой дата расхода товара DATE_RASH больше всех дат прихода данного товара DATE_PRIH в таблице PRIHOD:
CREATE TABLE RASHOD (ID_RS INTEGER NOT NULL,
TOVAR VARCHAR(20).CHARACTER SET WIN1251 NOT NULL
COLLATE PXW_CYRL,
DATE_RASH DATE NOT NULL,
KOLVO_R INTEGER NOT NULL,
PRIMARY KEY (ID_RS),
CONSTRAINT RASH_TOVAR
FOREIGN KEY (TOVAR) REFERENCES TOVAR(TOVAR),
CONSTRAINT PO_DATE_RASH
CHECK ( DATE_RASH > ALL
SELECT DATE_PRIH
FROM PRIHOD
WHERE PRIHOD.TOVAR = RASHOD.TOVAR)));
Как видно, в операторе SELECT в предложении WHERE указана только таблица PRIHOD. Таблица RASHOD в предложении WHERE не указана, но подразумевается, поскольку именно на нее наложено ограничение PO_DA ТЕ. Кроме того, в этом случае условие выборки WHERE PRIHOD.TOVAR = RASHOD.TOVAR подразумевает текущее значение RASHOD. TOVAR, т.е. значение, введенное в добавляемую запись таблицы RASHOD, - запись, значение столбца DA TE_RASH которой и проверяется на соответствие условию, заданному в CHECK в ограничении CONSTRAINT PO_DA TE_RASH. Указание в предложении WHERE обеих таблиц, FROM PRIHOD, RASHOD приведет к тому, что условие WHERE PRIHOD.TOVAR = RASHOD.TOVAR будет воспринято как внутреннее соединение таблиц PRIHOD и RASHOD по столбцу ТОVAR. В этом случае соединение будет произведено для всех значений столбца TOVAR в таблице RASHOD, а не для текущего значения той записи таблицы RASHOD, чье значение DA TE_RASH проверяется на соответствие условию.
• EXISTS (<выражение_вь1бора>) - возвращает True, если список выражение„выбора непустой, т.е. содержит хотя бы одну строку. Список выражение_выбора выдается как результат выполнения оператора SELECT по отношению к одной или нескольким таблицам БД.
В приводимом ниже примере обязательно существование хотя бы одной записи в таблице PRIHOD с таким же значением столбца TOVAR, что и в поле TOVAR записи таблицы RASHOD.
CREATE TABLE RASHOD (ID_RS INTEGER NOT NULL,
TOVAR VARCHAR(20) CHARACTER SET WIN1251 NOT NULL
COLLATE PXW_CYRL,
DATE_RASH DATE NOT NULL,
CONSTRAINT RASH_TOVAR
FOREIGN KEY (TOVAR) REFERENCES TOVAR(TOVAR),
CONSTRAINT PO_DATE_RASH
CHECK (EXISTS (SELECT TOVAR FROM PRIHOD
WHERE PRIHOD.TOVAR = RASHOD.TOVAR))
);
• SINGULAR (<выражение_вь1бора>) - возвращает True, если список выражение_выбора содержит только одну строку. Список выражение_выбора выдается как результат выполнения оператора SELECT по отношению к одной или нескольким таблицам БД.
В приводимом ниже примере обязательно существование единственной записи в таблице PRIHOD с таким же значением столбца TOVAR, что и в поле TOVAR записи таблицы RASHOD.
CREATE TABLE RASHOD (ID_RS INTEGER NOT NULL,
TOVAR VARCHAR(20) CHARACTER SET WIN1251 NOT NULL
COLLATE PXW_CYRL,
DATE_RASH DATE NOT NULL,
CONSTRAINT RASH_TOVAR
FOREIGN KEY (TOVAR) REFERENCES TOVAR(TOVAR),
CONSTRAINT PO__DATE_RASH
CHECK (SINGULAR(SELECT TOVAR FROM PRIHOD
WHERE PRIHOD.TOVAR = RASHOD.TOVAR))
);
• <значение> CONTAINING <значение1> определяет, что значение столбца должно содержать вхождение значение1, не важно в каком месте.
В приводимом ниже примере поле значение STOLBEZ должно содержать вхождение символов 'USD', независимо от того, какие символы и в каком количестве расположены перед ними или после них.
CREATE TABLE TBL(CHECK (STOLBEZ CONTAINING "USD"));
• <значение> STARTING [WITH] <значение1> определяет, что значение столбца должно начинаться с символов значение1.
В приводимом ниже примере поле значение STOLBEZ должно начинаться с символов 'USD'.
CREATE TABLE TBL (CHECK (STOLBEZ STARTING WITH "USD")) ;
• Может быть задана комбинация условий, которым должно соответствовать значение. В этом случае отдельные условия соединяются операторами AND или OR.
• Для многих из приведенных условий (см. формат CHECK) может быть выдано отрицание при помощи слова NOT. Например:
CHECK(NOT STOLBEZ1 > STOLBEZ2);
• В качестве <значение> можно указывать имя столбца, константу, выражение, функцию.
• В качестве функций могут использоваться:
• COUNT -
счетчик повторения;• SUM -
сумма;• AVG -
среднее значение;• МАХ -
максимальное значение;• MIN -
минимальное значение;• CAST -
приведение типов;• UPPER -
приведение всех букв к заглавным;• GEN_ID -
возвращает уникальное значение генератора.Изменение объявления таблиц
Оператор ALTER TABLE позволяет:
• добавить определение нового столбца;
• удалить столбец из таблицы;
• удалить атрибуты целостности таблицы или отдельного столбца;
добавить новые атрибуты целостности.
Перед изменением каких-либо атрибутов столбца данные, которые хранятся в нем, нужно сохранить. Для этого в таблице определяют временный столбец, в точности повторяющий все характеристики того столбца, который планируется изменить. Затем данные из изменяемого столбца копируют во временный столбец (используя, например, оператор UPDATE). После этого столбец, подлежащий изменению, попросту удаляют из таблицы, а на его месте создают новый, одноименный столбец с желаемыми атрибутами. В заключение в него копируют данные из временного столбца, а временный столбец уничтожают.
Пример.
Пусть имеется таблицаCREATE TABLE SOTR(ID_SOTR INTEGER NOT NULL PRIMARY KEY,
FIO CHAR(10) COLLATE PXW_CYRL,
OTDEL VARCHAR(lO) COLLATE PXW_CYRL,
DOLJNOST CHAR(10) COLLATE PXW_CYRL
);
Пусть необходимо изменить характеристики столбца FIO, изменив тип столбца с CHAR(IO) на VARCHAR(25). Тогда:
1. Добавляем в таблицу новый временный столбец FIO_TMP, полностью повторяющий характеристики изменяемого столбца FIO:
ALTER TABLE SOTR
ADD FIO_TMP CHAR(10) CHARACTER SET WIN1251
COLLATE PXW_CYRL;
2. Копируем данные из FIO в FIO_TMP:
UPDATE SOTR
SET FIO_TMP = FIO;
3. Удаляем столбец FIO:
ALTER TABLE SOTR
DROP FIO;
4. Создаем новый столбец FIO с необходимыми характеристиками:
ALTER TABLE SOTR
ADD FIO VARCHAR(25) COLLATE PXW_CYRL;
5. Переписываем данные из временного столбца FIO_TMP в новый столбец FIO:
UPDATE SOTR
SET FIO = FIOJTMP;
6. Удаляем временный столбец FIO_TMP:
ALTER TABLE SOTR
DROP FIO_TMP;
ЗАМЕЧАНИЕ.
Следует помнить, что изменение характеристик столбца, а также удаление столбца может закончиться неудачей, если:• столбец приобретает атрибуты PRIMARY KEY или UNIQUE, но старые значения в столбце нарушают требования уникальности данных;
• удаляемый столбец входил как часть в первичный или внешний ключ, что привело к нарушению ссылочной целостности между таблицами;
• столбцу были приписаны ограничения целостности CHECK на уровне таблицы;
• столбец использовался в иных компонентах БД - в просмотрах, триггерах, в выражениях для вычисляемых столбцов.
Все вышесказанное свидетельствует о том, что в случае необходимости изменения атрибутов столбца или в случае удаления столбца, сначала необходимо тщательно проанализировать, какие последствия для таблицы и базы данных в целом может повлечь такое изменение или удаление.
Изменение атрибутов столбца
Добавление нового столбца в таблицу БД производится оператором
ALTER TABLE <имя таблицы> ADD <определения столбца>;
Добавление новых ограничений целостности производится оператором
ALTER TABLE <имя таблицы> ADD [CONSTRAINT <имя ограничения>] <определения целостности>;
Удаление столбца (столбцов) из таблицы производится оператором
ALTER TABLE <имя таблицы> DROP <имя столбца1>[,<имя столбца2>.. . ] ;
Удаление ограничений целостности (уровень таблицы) производится оператором
ALTER TABLE <имя таблицы> DROP <имя ограничения целостности>;
Пример.
Для таблицы PRIHODCREATE TABLE PRIHOD(ID_PRIHOD INTEGER NOT NULL PRIMARY KEY,
DATAPRIH DATE NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
KOLVO INTEGER NOT NULL,
CONSTRAINT PO_TOVARU
FOREIGN KEY(TOVAR) REFERENCES SPR_TOVAR
) ;
удалить целостность PO_TOVARU: ALTER TABLE PRIHOD DROP PO_TOVARU;
ЗАМЕЧАНИЕ.
В случае необходимости непоименованной целостности придется использовать ее системное имя. Его можно узнать из системной таблицы БД с именем RDB$RELATION_CONSTRAINTS.Удаление таблицы
Удаление таблицы целиком производится оператором DROP TABLE <имя таблицы>;
Удаление может быть блокировано для родительских таблиц, для которых в дочерних таблицах (на данный момент не удаленных) имеются ссылки по внешнему ключу этих таблиц. Действительно, удаление родительской таблицы разрушило бы ссылочную целостность. Поэтому следует либо удалить ограничения ссылочной целостности во всех дочерних таблицах, либо - по необходимости - сначала удалить сами дочерние таблицы, а затем уже удалять родительскую.
Логическое разделение на ключи и индексы
В InterBase разделено понятие ключей и индексов. Это разделение, впрочем, имеет логическую окраску, то есть ценно при создании ТБД. Первичный (PRIMARY KEY) и внешний (FOREIGN KEY) ключи строятся для обеспечения ссылочной целостности реляционно связанных таблиц в БД. Первичный ключ, помимо этого, выполняет функции поддержания уникальности своих значений, что обусловлено его основным назначением - однозначно характеризовать запись в таблице БД. Для таких же целей может использоваться и просто уникальный ключ (UNIQUE).
"Обычные" индексы, создаваемые оператором CREATE INDEX, в отличие от ключей, служат для обеспечения сортировок и оптимизации доступа к данным.
С физической точки зрения, то, что логически при создании таблиц подразделялось на ключи и индексы, преобразуется в индексы. Однако, если "обычным" индексам можно назначить имя, то физические индексы, реализованные на основе определений ключей, строятся и именуются системой автоматически. Например, для таблицы SOTR будут построены два физических индекса - по первичному ключу и по "обычному" индексу DLJ:
CREATE TABLE SOTR (ID_SOTR INTEGER NOT NULL, OTDEL VARCHAR(lO) CHARACTER SET WIN1251
COLLATE PXW_CYRL, DOLJNOST CHAR(10) CHARACTER SET WIN1251
COLLATE PXW_CYRL, FIO VARCHAR(25) CHARACTER SET WIN1251
COLLATE PXW_CYRL, PRIMARY KEY (ID_SOTR)
) ;
CREATE INDEX DLJ ON SOTR (DOLJNOST) ;
но именоваться они будут следующим образом:
DLJ INDEX ON SOTR(DOLJNOST)
RDB$PRIMARY18 UNIQUE INDEX ON SOTR(ID_SOTR)
Заметим, что вывести определения всех индексов БД можно оператором SHOW INDEX;
А проделать то же для конкретной таблицы можно оператором SHOW INDEX <имя таблицы>;
Необходимость создания индексов
Индексы необходимо создавать в случае, когда по столбцу или группе столбцов:
• часто производится поиск в БД (столбец или группа столбцов часто перечисляются в предложении WHERE оператора SELECT);
• часто строятся объединения таблиц;
• часто производится сортировка в НД, возвращаемых в качестве результатов запросов к БД (то есть столбец или столбцы часто используются в предложении ORDER BY оператора SELECT).
Не рекомендуется строить индексы по столбцам или группам столбцов, которые:
• редко используются для поиска, объединения и сортировки результатов запросов;
• часто меняют значение, что приводит к необходимости часто обновлять индекс и способно существенно замедлить скорость работы с БД;
• содержат небольшое число вариантов значения.
В случае, когда при выполнении запросов используется сортировка по одним и тем же столбцам, необходимо помнить следующее: создание единого индекса по этим полям способно ускорить выполнение запросов. Однако:
• при использовании в некоторых запросах не всех столбцов из этого индекса следует использовать только непрерывную последовательность столбцов; например, если индекс построен по столбцам Р1, Р2, РЗ, Р4, то в некотором операторе SELECT допустимо указать SELECT ... ORDER BY P1,P2,P3 но никак не SELECT...ORDER BY P1,P2,P4 или SELECT...ORDER BY Р1,РЗ, Р4, поскольку в последних двух случаях индекс, построенный по столбцам Р1, Р2, РЗ, Р4, не будет использован;
• последовательность указания в предложении ORDER BY столбцов является важной; так, для индекса, построенного по столбцам Р1, Р2, РЗ, Р4, указание SELECT ... ORDER BY P2,P1,P3 не приведет к использованию указанного индекса для сортировки результирующего набора данных;
• при частом использовании в условной части WHERE оператора SELECT нескольких столбцов, связанных между собой операцией "или" (OR): SELECT . . . WHERE Р1 = значение! OR Р2 = значение2 OR РЗ = ...
вместо индекса по столбцам Р1, Р2, РЗ лучше создать несколько индексов, построенных по каждому из этих полей, поскольку в противном случае будет осуществлен последовательный просмотр всей таблицы. Это неудивительно, поскольку индексно-последовательный доступ для индекса по столбцам Р1, Р2, РЗ может быть осуществлен только для столбца Р1; значения столбцов Р2 и РЗ в этом индексе спонтанно разбросаны по индексу. Например:
Создание индекса
Индекс может быть создан оператором
CREATE [UNIQUE] |ASC[ENDING][ DESC[ENDING]]
INDEX ИмяИндекса ON ИмяТаблицы (столбец! |,столбец2 ...]);
• UNIQUE - требует создания уникального индекса, не допускающего одинаковых значений индексных полей для разных записей таблицы;
• ASC[ ENDING] - указывает на необходимость сортировки значений индексных полей по возрастанию (режим принят по умолчанию);
• DESC[ENDING] - указывает на необходимость сортировки значений индексных полей по убыванию;
• ИмяИндекса - имя создаваемого индекса;
• table - имя таблицы, для которой создается индекс;
• cтолбецN - имена столбцов, по которым создается индекс.
Например,
для таблицы PRIHODCREATE TABLE PRIHOD(ID_PRIHOD INTEGER NOT NULL PRIMARY KEY,
DATAPRIH DATE NOT NULL,
TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
KOLVO INTEGER NOT NULL
) ;
создать индекс в порядке убывания значений DATAPRIH и TOVAR:
CREATE DESC INDEX D_P ON PRIHOD (DATAPRIH,TOVAR);
Перестройка индекса
Перестройка индекса заключается в пересоздании и балансировке индекса, что наступает после деактивизации индекса оператором ALTER INDEX <имя индекса> DEACTIVATE;
и последующей его активизации оператором ALTER INDEX <имя индекса> DEACTIVATE;
Деактивизация индекса полезна также в том случае, когда имеет место вставка в таблицу БД большого числа записей. В обычном режиме добавления записей при активном индексе изменения вносятся в индекс по мере добавления записей в таблицу, что может разбалансировать индекс. Следует помнить, что:
• нельзя перестроить используемый в данный момент индекс, что может иметь место при выполнении другими пользователями запросов к БД;
• нельзя перестроить индекс, созданный в результате определения первичного и внешнего ключей, а также уникальности значений столбца или группы столбцов (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для этой цели следует применять оператор ALTER TABLE;
для выполнения оператора ALTER INDEX нужно иметь соответствующие привилегии доступа к БД.
Повторное вычисление показателя "полезности" индекса
Показатель "полезности" индекса основан на сведениях о числе повторяющихся строк индекса. Он используется InterBase при доступе к таблице для выработки оптимального плана удовлетворения запроса. Пересчет показателя "полезности" производится оператором
SET STATISTICS INDEX <имя индекса:-;
Заметим что SET STATISTICS не перестраивает индекс. Для этой цели необходимо использовать оператор ALTER INDEX. Для выполнения оператора SET STATISTICS нужно иметь соответствующие привилегии
доступа к БД.
Улучшение производительности индекса
После многократного внесения изменений в таблицу БД индексы этой таблицы могут быть разбалансированы. Разбалансировка приводит к тому, что "глубина" индекса (depth) возрастает сверх критического значения (2). "Глубина" индекса - параметр, показывающий максимальное число операций, необходимых для нахождения искомого значения в таблице БД с использованием данного индекса. В случае разбалансировки индекса его ценность при выполнении запросов снижается из-за увеличения времени выполнения запроса. Поэтому время от времени необходимо производить одно из перечисленных ниже действий:
• выполнять перестройку индекса оператором ALTER INDEX;
• пересчитать показатель "выбираемости" индекса оператором SET STATISTICS;
уничтожить индекс оператором DROP INDEX и заново создать его оператором CREATE INDEX.
Удаление существующего индекса
Для удаления индекса, ранее созданного оператором CREATE INDEX, используется оператор
DROP INDEX <имя индекса>;
Нельзя удалить индекс, созданный в результате определения первичного и внешнего ключей, а также уникальности значений столбца или группы столбцов (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для этой цели следует применять оператор ALTER TABLE.
Нельзя также удалить используемый индекс, что может иметь Место при выполнении другими пользователями запросов к БД. Кроме этого, для удаления индекса нужно иметь соответствующие привилегии доступа к БД.
Компонент Tdatabase
204 25. Оператор SELECT
25.1. Простейший вид оператора SELECT *
25.2. Использование предложения WHERE *
25.2.1. Сравнение значения столбца с константой *
25.2.2. Сравнение значения столбца из одной таблицы со значением столбца из другой таблицы (внутреннее соединение) *
25.3. Использование псевдонимов таблиц *
25.4. Предложение ORDER BY - определение сортировки *
25.5. Устранение повторяющихся значений *
25.6. Расчет значений результирующих столбцов на основе арифметических выражений *
25.8. Использование группировок записей *
25.9. Предложение HAVING - наложение ограничений на группировку записей *
25.10. Предложение WHERE : задание сложных условий поиска *
25.10.1. Использование логических выражений *
25.10.2. Сравнение столбца с результатом вычисления выражения *
25.10.3. Использование BETWEEN *
25.10.4. Использование IN (список значений) *
25.10.5. Использование STARTING *
25.10.6. Использование CONTAINING *
25.10.7. Использование функции UPPER *
25.10.9. Использование функции CAST *
25.11. Использование подзапросов *
25.13. Дополнительные возможности использования подзапросов, возвр единичное значение *
25.13.1. Использование EXISTS *
25.13.2. Использование SINGULAR *
25.14. Использование подзапросов, возвращающих множество значений *
25.14.1. Использование ALL, SOME *
25.14.2. Использование HAVING и агрегатных функций для вложенных подзапросов *
25.16. UNION - объединение результатов выполнения нескольких операторов SELECT *
25.17. Использование IS NULL *
25.18. Использование операции сцепления строк *
25.19. Работа с разными БД в одном запросе *
глава 26 26. Добавление, изменение, удаление записей26.1.1. Явное указание списка значений *
26.1.2. Указание значений при помощи оператора SELECT *
глава 27 27. Работа с просмотрами VIEW27.1. Понятие просмотра как виртуальной таблицы *
27.2. Способы формирования просмотров *
27.3. Указание столбцов просмотра в операторе CREATE VIEW *
27.4. Обновляемые и необновляемые просмотры *
27.5. Использование CHECK OPTION *
27.6. Компоненты Delphi и использование просмотров *
28. Работа с хранимыми процедурами28.1. Понятие хранимой процедуры *
28.2. Создание хранимой процедуры *
28.3. Алгоритмический язык хранимых процедур *
28.3.1. Объявление локальных переменных *
28.3.2. Операторные скобки BEGIN... END *
28.3.3. Оператор присваивания *
28.3.4. Оператор IF... THEN ... ELSE *
28.3.6. Оператор FOR SELECT... DO *
28.3.8. Оператор WHILE... DO *
28.3.10. Оператор EXECUTE PROCEDURE *
28.3.11. Оператор POST_EVENT *
28.4. Вызов процедур выбора в приложении клиента *
28.5. Обращение к процедурам действия. *
28.6. Изменение и удаление хранимых процедур *
глава 29 29. Работа с триггерами29.2. Определение заголовка триггера *
29.4. Обеспечение каскадных воздействий *
29.5. Ведение журнала изменений *
29.6. Использование триггеров для реализации бизнес-правил *
29.7. Изменение и удаление триггеров *
глава 30 30. Использование генераторов * 31. Использование утилиты Database Explorer31.2.1. Просмотр и изменение данных *
31.2.3. Просмотр ограничений на значения столбцов *
глава 32 32. Транзакции32.1. Откат изменений и целостность БД *
32.3. Уровни изоляции транзакций: приложение клиента *
32.3.1. Уровень изоляции транзакций Dirty Read *
32.3.2. Уровень изоляции транзакций Read Commited *
32.3.3. Уровень изоляции транзакций Repeatable Read *
32.3.4. Установка уровней изоляции транзакций в Delphi *
32.4. Свойство UpdateMode и обновление записей *
32.5. Явно и неявно стартуемые транзакции *
32.6. Управление транзакциями на SQL-сервере InterBase *
глава 33 33. Кэшированные изменения33.1. Использование кэшированных изменений *
33.2. Активизация режима кэшированных изменений *
33.3. Отмена кэшированных изменений *
33.4. Подтверждение кэшированных изменений *
33.4.1. Подтверждение кэшированных изменений методом ApplyUpdates компонента TDatabase *
33.4.2. Подтверждение кэшированных изменений методом ApplyUpdates набора данных *
33.5. Видимость измененных записей *
33.6. Обработка ошибок. Обработчик события OnUpdateError *
33.6.1. Использование обработчика OnUpdateError *
33.6.2. Использование параметра UpdateKind *
33.6.3. Использование параметра UpdateAction *
33.6.4. Использование параметра Е *
33.7.1. Использование компонента TUpdateSQL *
33.7.2. Компонент TUpdateSQL: этап разработки *
33.7.3. Компонент TUpdateSQL: выполнение SQL-операторов *
глава 34 34.Работа с событиями34.2. Приложения Delphi и компонент TIBEventAlerter *
34.3. Использование компонента TIBEventAlerter для обработки событий сервера БД в клиентском приложении *
34.4. Обмен сообщениями между приложениями *
34.5. Обмен инициализирующими сообщениями между приложениями *
глава 35 35. Функции, определяемые пользователем35.1. Понятие функции, определяемой пользователем *
35.2. Разработка DLL и UDF в Delphi *
35.2.2. Совместимость типов параметров *
35.2.3. Особенности использования в UDF параметров типа PChar *
35.2.4. Особенности использования в UDF параметров типа даты и времени *
35.3. Объявление UDF в БД InterBase *
35.4. Пример создания DLL с несколькими UDF и объявления их в БД *
глава 36 36. Определение бизнес-правил36.1. Размещение бизнес-правил *
36.2. Реализация бизнес-правил на сервере *
36.2.1. Ограничения значения столбца записи *
36.2.2. Запрет добавления записей в просмотре *
36.2.3. Использование триггеров для поддержания ссылочной целостности *
36.3. Реализация бизнес-правил в приложении клиента *
36.3.1. Реализация бизнес-правил в компонентах типа "набор данных" *
36.3.2. Свойство Constrained (компонент TQuery) *
36.3.3. Свойство Constraints *
36.3.4. Реализация бизнес-правил в компоненте TField *
36.3.5. Реализация бизнес-правил в иных компонентах *
36.4. Использование словаря данных для определения атрибутов полей *
37. Оптимизация работы с БД37.1. Оптимизация структуры БД *
37.1.1. Нормализация таблиц: теория и практика *
37.1.2. Частичная зависимость структуры данных от методов доступа к ним *
37.1.3. Физические характеристики БД *
37.2.1. Оптимальная структура индексов *
37.2.2. "Полезность" индексов *
37.2.3. Просмотр плана выполнения запросов *
37.2.4. Целесообразность создания индексов *
37.2.5. Частичное использование составного индекса *
37.2.6. Многопоточность поиска по OR и IN *
37.2.7. Уменьшение общего количества индексов *
37.3. Оптимизация клиентских приложений *
37.3.1. Минимизация соединений с БД *
37.3.2. Использование TQuery *
37.3.3. Перенос тяжести вычислительной работы на сервер *
глава 38 38. Работа с утилитой InterBase Server Manager38.1.1. Соединение с сервером *
38.1.3. Выбор текущего сервера и БД *
38.2. Изменение конфигурации сервера *
38.3.1. Статистические данные непосредственно о БД *
38.4. Принудительная сборка мусора *
38.5. Создание резервной копии (сохранение) и восстановление БД *
38.5.1. Переход в однопользовательский режим соединения с БД *
38.5.2. Резервное копирование БД *
38.5.3. Восстановление БД из резервной копии *
38.6. Принудительная запись на диск *
38.7. Восстановление транзакций *
38.8. Регистрация новых пользователей *
глава 39 39. Установка привилегий доступа 39.1. Привилегии доступа по умолчанию * 39.2. Виды привилегий * 39.3. Минимальный состав параметров при предоставлении привилегий доступа к таблице БД * 39.4. Предоставление нескольких привилегий * 39.5. Предоставление привилегий нескольким пользователям * 39.6. Назначение привилегий всем пользователям * 39.7. Установка привилегий доступа к отдельным столбцам таблицы * 39.8. Предоставление пользователю права назначать привилегии другим пользователям * 39.9. Назначение привилегий вызова хранимых процедур * 39.10. Назначение процедуре прав доступа к таблице * 39.11. Ликвидация привилегий * приложение А Свойства, методы, события. приложение BФормат SQL-операторов (СУБД InterBase)
Ограничение, накладываемое на значения полей, ассоциированных с доменом *
Изменение определения домена *
Определение первичного ключа *
Определение уникального ключа *
Определение ограничения внешнего ключа и ссылочной целостности с родительской таблицей *
Ограничения на значения столбца *
Добавление нового столбца в таблицу БД *
Добавление новых ограничений целостности *
Удаление столбца (столбцов) из таблицы БД *
Удаление ограничений целостности *
Вычисление показателя "полезности" индекса *
Обращение к хранимой процедуре (утилита WISQL) *
Изменение хранимой процедуры *
Удаление хранимой процедуры: *
Алгоритмический язык триггеров и хранимых процедур *
Объявление локальных переменных *
Оператор циклической выборки *
Принудительная выдача выходных параметров (только хранимые процедуры) *
Выход из процедуры, триггера *
Вложенный вызов другой хранимой процедуры *
Инициация наступления события *
Обращение к старому и новому значениям столбца (только триггеры) *
Изменение существующего триггера *
Установка стартового значения генератора *
Инициация наступления события *
UDF (Функции, определяемые пользователем) *
Объявление UDF (функций, определяемых пользователем) в базе данных *
Предоставление привилегий доступа к таблицам БД, просмотрам, вызовам процедур *
Компонент TDataBase создается для каждого факта соединения сессии с отдельной БД Если для факта конкретного соединения в приложении не предусмотрен компонент TDatabase, создается временный компонент TDatabase Таким образом, каждая открытая БД имеет свой компонент TDataBase. Список активных БД данной сессии содержится в коллекции TSession Databases, каждый элемент которой имеет тип TDatabase. Число активных БД сессии определяется через свойство TSession DatabaseCount.
Случаи явного использования в приложении компонента TDatabase чаще всего связаны с работой в архитектуре "клиент-сервер". Явно определенный в приложении компонент TDatabase позволяет управлять БД, создавая постоянные соединения с БД, настраивая сеанс соединения с сервером, управляя транзакциями и создавая локальные псевдонимы BDE в приложении.
Несомненно, каждый набор данных, работающий с таблицами одной и той же БД, может иметь с этой БД отдельное соединение. Однако это нерационально, поскольку на каждое соединение затрачиваются системные ресурсы Для минимизации их использования рекомендуется в приложении создавать единственное соединение с удаленной БД при помощи компонента TDatabase, a все наборы данных и компоненты, реализующие действия над БД, соединять с компонентом TDatabase.
Свойство property AliasName: TSymbolStr; указывает псевдоним BDE, ассоциированный с данным компонентом TDatabase. Если заполнено свойство DnverNawe, значение свойства A liasName очищается.
Свойство property DatabaseName: TFileName; определяет локальный псевдоним приложения, который может использоваться при доступе к БД вместо псевдонима BDE, пути к файлам БД или имени БД. Именно значение, определяемое данным свойством, показывается в выпадающем списке свойства DatabaseName компонентов TTab/e, TQuery и TSforedProc при разработке приложения.
Свойства соединения компонента TDatabase с удаленной БД определяются:
• параметрами псевдонима БД;
• параметрами драйвера БД (например, InterBase);
• общесистемными установками в утилите BDE Administrator.
Эти установки могут изменяться во время разработки приложения в редакторе базы данных. Для этого нужно в приложении выбрать при помощи мыши компонент TDatabase, щелкнуть по нему правой кнопкой мыши и во всплывающем меню выбрать элемент Database Editor. В диалоговом окне редактора БД (рис. 24.1) в поле Parameter overrides можно переустановить параметры псевдонима БД и драйвера.
Для того чтобы вернуться к параметрам псевдонима, принятым по умолчанию, нужно нажать кнопку Defaults (результат на рис. 24.2).
Не рекомендуется явно указывать значение параметра SERVER NAME, например
SERVER NAME=D:\BOOK\IB_SKLAD\Ib_sklad.gdb
поскольку при изменении местонахождения БД придется изменять значение пути и в параметрах компонента TDatabase (вместо того, чтобы изменить путь к БД в определении псевдонима в утилите BDE Administrator).
На панели Options имеется два поля. Снятие отметки с поля Login Prompt приводит к тому, что диалог запроса имени пользователя и пароля не выдается. В этом случае имя пользователя и пароль можно указать в параметрах соединения (в поле Parameter), например
USER NAME=SYSDBA
PASSWORD=masterkey
Отметка поля Login Prompt равносильна присваиванию значения True свойству
property LoginPrompt: Boolean;
Это свойство определяет, выдавать ли при первом соединении с БД окно ввода имени пользователя и пароля, или же они должны быть указаны в свойстве Params.
Другое поле отметки на панелях Options, Keep Inactive Connection в отмеченном состоянии означает, что соединение с БД будет храниться даже в случае, если ни один набор данных в приложении, работающий с этой БД, не открыт. Отметка поля аналогична установке в True свойства
property KeepConnection: Boolean;
указывает, следует ли хранить соединение с БД, если нет связанных с ней открытых НД.Свойство property Connected: Boolean; в значении True указывает, имеет ли компонент TDatabase активное соединение с БД.
Свойства соединения с БД могут быть переустановлены во время работы приложения. Для этого используется синтаксис доступа к конкретному параметру как к элементу компонента TStrings свойства
property Params: TStrings;
когда название параметра выступает в качестве индекса, например:Databasel.Params[' USER NAME '] := 'PASHA';
Databasel.Params['PASSWORD'] := 'ppp';
При этом следует помнить, что в момент смены параметров соединения не должно быть активного соединения с БД, то есть свойство Connected должно быть установлено в значение False.
Рассмотрим другие свойства компонента TDatabase.
Свойство property DatasetCount: Integer; определяет число активных НД, связанных с настоящей БД (компоненты TTable и TQuery).
Свойство property Datasets|Index: Integer]: TDBDataSet; представляет собой коллекцию активных НД (компоненты TDBDataSet), связанных с настоящей БД. Число компонентов определяется свойством DatasetCount. Минимальный индекс 0, максимальный DatasetCount -1.
property DriverName: TSymboIStr;
Указывает имя драйвера BDE, такого как STANDARD (dBASE и Paradox), ORACLE, SYBASE, INFORMIX или INTERBASE. Значение свойства очищается, если устанавливается значение свойства AliasName, и наоборот.property IsSQLBased: Boolean;
Указывает, ассоциирован ли данный компонент TDatabase с SQL-ориентированной БД.property SessionName: string;
Указывает компонент TSession, с которым связана БД.property Temporary: Boolean;
Указывает, создавать ли временный компонент TDatabase для БД, для которых компоненты TDatabase не определены в приложении явно.Следующие свойства используются для управления транзакциями.
property Translsolation: TTransIsolation;
Устанавливает уровень изоляции транзакций для SQL-сервера.property InTransaction: Boolean;
Возвращает True, если для компонента TDatabase (для текущего соединения с БД) существует активная транзакция, и False, если для соединения с БД активных транзакций нет.Метод procedure StartTransaction; инициирует начало транзакции. Если в этот момент активна некоторая транзакция, возбуждается исключение. Транзакционные изменения в наборах БД, имевшие место после выполнения метода Start Transaction, либо подтверждаются методом Commit, либо отменяются методом Rollback. До подтверждения или отмены изменений транзакция, начатая StartTransaction, считается активной.
Метод procedure Commit; подтверждает текущую транзакцию, т.е. подтверждает все модификации в БД, имевшие место с момента последнего вызова метода StartTransaction. Если ни одна транзакция не активна, возбуждается исключение.
Метод procedure Rollback; откатывает текущую транзакцию, т.е. отменяет все модификации в БД, имевшие место с момента последнего вызова метода Start Transaction.
Сведения об уровнях изоляции транзакций, равно как и о других вопросах, связанных со стартом, подтверждением или откатом транзакций в приложении и на сервере БД, вы можете найти в разделе книги, посвященном управлению транзакциями.
Метод procedure Apply Updates(const DataSets: array of TDataSet); применяется для подтверждения кэшированных изменений сразу в нескольких наборах данных. Список НД определяется параметром DataSets. В случае указания нескольких НД их имена разделяются запятыми.
Более подробную информацию об использовании этого метода и о кэшированных изменениях вообще Вы можете найти в разделе книги, посвященном кэшированным изменениям в БД.
Метод procedure Close; закрывает БД и все открытые НД, связанные с ней.
Метод procedure CloseDatasets; закрывает открытые НД, связанные с БД, но не закрывает саму БД.
Метод procedure Open; открывает БД, соединяя компонент TDatabase с сервером или BDE для Paradox или dBASE.
Событие TLoginEvent = procedure(Database: TDatabase; LoginParams:TStrings) of object;
property OnLogin: TLoginEvent; возникает, когда компонент TDatabase начинает сессию соединения с SQL-сервером.
204 25. Оператор SELECT
25.1. Простейший вид оператора SELECT *
25.2. Использование предложения WHERE *
25.2.1. Сравнение значения столбца с константой *
25.2.2. Сравнение значения столбца из одной таблицы со значением столбца из другой таблицы (внутреннее соединение) *
25.3. Использование псевдонимов таблиц *
25.4. Предложение ORDER BY - определение сортировки *
25.5. Устранение повторяющихся значений *
25.6. Расчет значений результирующих столбцов на основе арифметических выражений *
25.8. Использование группировок записей *
25.9. Предложение HAVING - наложение ограничений на группировку записей *
25.10. Предложение WHERE : задание сложных условий поиска *
25.10.1. Использование логических выражений *
25.10.2. Сравнение столбца с результатом вычисления выражения *
25.10.3. Использование BETWEEN *
25.10.4. Использование IN (список значений) *
25.10.5. Использование STARTING *
25.10.6. Использование CONTAINING *
25.10.7. Использование функции UPPER *
25.10.9. Использование функции CAST *
25.11. Использование подзапросов *
25.13. Дополнительные возможности использования подзапросов, возвр единичное значение *
25.13.1. Использование EXISTS *
25.13.2. Использование SINGULAR *
25.14. Использование подзапросов, возвращающих множество значений *
25.14.1. Использование ALL, SOME *
25.14.2. Использование HAVING и агрегатных функций для вложенных подзапросов *
25.16. UNION - объединение результатов выполнения нескольких операторов SELECT *
25.17. Использование IS NULL *
25.18. Использование операции сцепления строк *
25.19. Работа с разными БД в одном запросе *
глава 26 26. Добавление, изменение, удаление записей26.1.1. Явное указание списка значений *
26.1.2. Указание значений при помощи оператора SELECT *
глава 27 27. Работа с просмотрами VIEW27.1. Понятие просмотра как виртуальной таблицы *
27.2. Способы формирования просмотров *
27.3. Указание столбцов просмотра в операторе CREATE VIEW *
27.4. Обновляемые и необновляемые просмотры *
27.5. Использование CHECK OPTION *
27.6. Компоненты Delphi и использование просмотров *
28. Работа с хранимыми процедурами28.1. Понятие хранимой процедуры *
28.2. Создание хранимой процедуры *
28.3. Алгоритмический язык хранимых процедур *
28.3.1. Объявление локальных переменных *
28.3.2. Операторные скобки BEGIN... END *
28.3.3. Оператор присваивания *
28.3.4. Оператор IF... THEN ... ELSE *
28.3.6. Оператор FOR SELECT... DO *
28.3.8. Оператор WHILE... DO *
28.3.10. Оператор EXECUTE PROCEDURE *
28.3.11. Оператор POST_EVENT *
28.4. Вызов процедур выбора в приложении клиента *
28.5. Обращение к процедурам действия. *
28.6. Изменение и удаление хранимых процедур *
глава 29 29. Работа с триггерами29.2. Определение заголовка триггера *
29.4. Обеспечение каскадных воздействий *
29.5. Ведение журнала изменений *
29.6. Использование триггеров для реализации бизнес-правил *
29.7. Изменение и удаление триггеров *
глава 30 30. Использование генераторов * 31. Использование утилиты Database Explorer31.2.1. Просмотр и изменение данных *
31.2.3. Просмотр ограничений на значения столбцов *
глава 32 32. Транзакции32.1. Откат изменений и целостность БД *
32.3. Уровни изоляции транзакций: приложение клиента *
32.3.1. Уровень изоляции транзакций Dirty Read *
32.3.2. Уровень изоляции транзакций Read Commited *
32.3.3. Уровень изоляции транзакций Repeatable Read *
32.3.4. Установка уровней изоляции транзакций в Delphi *
32.4. Свойство UpdateMode и обновление записей *
32.5. Явно и неявно стартуемые транзакции *
32.6. Управление транзакциями на SQL-сервере InterBase *
глава 33 33. Кэшированные изменения33.1. Использование кэшированных изменений *
33.2. Активизация режима кэшированных изменений *
33.3. Отмена кэшированных изменений *
33.4. Подтверждение кэшированных изменений *
33.4.1. Подтверждение кэшированных изменений методом ApplyUpdates компонента TDatabase *
33.4.2. Подтверждение кэшированных изменений методом ApplyUpdates набора данных *
33.5. Видимость измененных записей *
33.6. Обработка ошибок. Обработчик события OnUpdateError *
33.6.1. Использование обработчика OnUpdateError *
33.6.2. Использование параметра UpdateKind *
33.6.3. Использование параметра UpdateAction *
33.6.4. Использование параметра Е *
33.7.1. Использование компонента TUpdateSQL *
33.7.2. Компонент TUpdateSQL: этап разработки *
33.7.3. Компонент TUpdateSQL: выполнение SQL-операторов *
глава 34 34.Работа с событиями34.2. Приложения Delphi и компонент TIBEventAlerter *
34.3. Использование компонента TIBEventAlerter для обработки событий сервера БД в клиентском приложении *
34.4. Обмен сообщениями между приложениями *
34.5. Обмен инициализирующими сообщениями между приложениями *
глава 35 35. Функции, определяемые пользователем35.1. Понятие функции, определяемой пользователем *
35.2. Разработка DLL и UDF в Delphi *
35.2.2. Совместимость типов параметров *
35.2.3. Особенности использования в UDF параметров типа PChar *
35.2.4. Особенности использования в UDF параметров типа даты и времени *
35.3. Объявление UDF в БД InterBase *
35.4. Пример создания DLL с несколькими UDF и объявления их в БД *
глава 36 36. Определение бизнес-правил36.1. Размещение бизнес-правил *
36.2. Реализация бизнес-правил на сервере *
36.2.1. Ограничения значения столбца записи *
36.2.2. Запрет добавления записей в просмотре *
36.2.3. Использование триггеров для поддержания ссылочной целостности *
36.3. Реализация бизнес-правил в приложении клиента *
36.3.1. Реализация бизнес-правил в компонентах типа "набор данных" *
36.3.2. Свойство Constrained (компонент TQuery) *
36.3.3. Свойство Constraints *
36.3.4. Реализация бизнес-правил в компоненте TField *
36.3.5. Реализация бизнес-правил в иных компонентах *
36.4. Использование словаря данных для определения атрибутов полей *
37. Оптимизация работы с БД37.1. Оптимизация структуры БД *
37.1.1. Нормализация таблиц: теория и практика *
37.1.2. Частичная зависимость структуры данных от методов доступа к ним *
37.1.3. Физические характеристики БД *
37.2.1. Оптимальная структура индексов *
37.2.2. "Полезность" индексов *
37.2.3. Просмотр плана выполнения запросов *
37.2.4. Целесообразность создания индексов *
37.2.5. Частичное использование составного индекса *
37.2.6. Многопоточность поиска по OR и IN *
37.2.7. Уменьшение общего количества индексов *
37.3. Оптимизация клиентских приложений *
37.3.1. Минимизация соединений с БД *
37.3.2. Использование TQuery *
37.3.3. Перенос тяжести вычислительной работы на сервер *
глава 38 38. Работа с утилитой InterBase Server Manager38.1.1. Соединение с сервером *
38.1.3. Выбор текущего сервера и БД *
38.2. Изменение конфигурации сервера *
38.3.1. Статистические данные непосредственно о БД *
38.4. Принудительная сборка мусора *
38.5. Создание резервной копии (сохранение) и восстановление БД *
38.5.1. Переход в однопользовательский режим соединения с БД *
38.5.2. Резервное копирование БД *
38.5.3. Восстановление БД из резервной копии *
38.6. Принудительная запись на диск *
38.7. Восстановление транзакций *
38.8. Регистрация новых пользователей *
глава 39 39. Установка привилегий доступа 39.1. Привилегии доступа по умолчанию * 39.2. Виды привилегий * 39.3. Минимальный состав параметров при предоставлении привилегий доступа к таблице БД * 39.4. Предоставление нескольких привилегий * 39.5. Предоставление привилегий нескольким пользователям * 39.6. Назначение привилегий всем пользователям * 39.7. Установка привилегий доступа к отдельным столбцам таблицы * 39.8. Предоставление пользователю права назначать привилегии другим пользователям * 39.9. Назначение привилегий вызова хранимых процедур * 39.10. Назначение процедуре прав доступа к таблице * 39.11. Ликвидация привилегий * приложение А Свойства, методы, события. приложение BФормат SQL-операторов (СУБД InterBase)
Ограничение, накладываемое на значения полей, ассоциированных с доменом *
Изменение определения домена *
Определение первичного ключа *
Определение уникального ключа *
Определение ограничения внешнего ключа и ссылочной целостности с родительской таблицей *
Ограничения на значения столбца *
Добавление нового столбца в таблицу БД *
Добавление новых ограничений целостности *
Удаление столбца (столбцов) из таблицы БД *
Удаление ограничений целостности *
Вычисление показателя "полезности" индекса *
Обращение к хранимой процедуре (утилита WISQL) *
Изменение хранимой процедуры *
Удаление хранимой процедуры: *
Алгоритмический язык триггеров и хранимых процедур *
Объявление локальных переменных *
Оператор циклической выборки *
Принудительная выдача выходных параметров (только хранимые процедуры) *
Выход из процедуры, триггера *
Вложенный вызов другой хранимой процедуры *
Инициация наступления события *
Обращение к старому и новому значениям столбца (только триггеры) *
Изменение существующего триггера *
Установка стартового значения генератора *
Инициация наступления события *
UDF (Функции, определяемые пользователем) *
Объявление UDF (функций, определяемых пользователем) в базе данных *
Предоставление привилегий доступа к таблицам БД, просмотрам, вызовам процедур *
Сравнение значения столбца с константой
При сравнении значения столбца с константой условие поиска имеет вид
<имя столбца> <оператор> константа
где в качестве оператора могут выступать операции отношения = равно
< меньше
> больше
<=
меньше или равно>= больше или равно
!< не меньше (т.е. больше или равно)
!> не больше (т.е. меньше или равно)
<> не равно
!= не равно
В качестве константы могут выступать строковые и числовые значения, указанные явно.
Пример. Показать все операции отпуска товаров объемом 20 единиц (результат на рис. 25.2)
SELECT *
FROM RASHOD R
WHERE KOLVO=20
Сравнение значения столбца
При сравнении значения столбца одной таблицы со значением столбца из другой таблицы условие поиска имеет вид
<имя столбца таблицы 1> <оператор> <имя столбца таблицы 2>
Пример.
Выдать все записи о расходе товара из таблицы RASHOD. Для каждого товара выдать его цену из таблицы ТО VARY (результат на рис. 25.3)SELECT RASHOD.*, TOVARY.ZENA
FROM RASHOD, TOVARY
WHERE RASHOD.TOVAR = TOVARY.TOVAR
При выполнении оператора SELECT для каждой записи из таблицы RASHOD ищется запись в таблице TOVARY, у которой значение в поле TOVAR совпадает со значением в поле TOVAR текущей записи таблицы RASHOD
При этом безразлично, в каком порядке перечислять таблицы в условии поиска, т.е безразлично, какая из таблиц будет упомянута слева, а какая справа. Таким образом, следующие условия поиска идентичны
RASHOD. TOVAR = TOVARY. TOVAR идентично условию
TOVARY.TOVAR = RASHOD.TOVAR
Такой способ соединения таблиц называется внутренним соединением
При внутреннем соединении двух таблиц А и В логический порядок формирования результирующего НД можно представить себе следующим образом.
1. Из столбцов, которые указаны после слова SELECT, составляется декартово произведение путем сцепления результирующих столбцов каждой записи из таблицы А и результирующих столбцов записи из таблицы В
2 Из получившегося НД отбрасываются все записи, не удовлетворяющие условию поиска в предложении WHERE
ЗАМЕЧАНИЕ
Определение "логический порядок формирования результирующего набора данных" употреблено не случайно В проектировании данных всегда различают два уровня - логический и физический. Логический уровень - это часто достаточно абстрактный уровень; физический уровень определяет действительно протекающие процессы, в большинстве случаев скрытые от взгляда, не лежащие на поверхности.Поэтому, когда мы говорим о "логическом порядке выполнения запроса", это подразумевает, что так нам легче понимать процессы, происходящие при выполнении внутреннего соединения; на самом же деле физические процессы, реально протекающие на сервере БД при выполнении запроса, могут не совпадать с нашим логическим представлением о них.
SQL-сервер при выполнении запроса всегда стремится его оптимизировать, то есть выполнить с максимальной быстротой при минимально возможных затратах ресурсов. В частности, оптимизация запросов в InterBase представляет собой "черный ящик", то есть нельзя сказать, как именно будет выполняться конкретный запрос, поскольку, помимо прочего, при оптимизации не последнюю роль играет текущее состояние БД. Подобные вопросы рассматриваются ниже в разделе, посвященном оптимизации запросов.
Пример. Пусть имеются таблицы А и В. Тогда выполнение оператора
SELECT А.P1, A.P2, В.Р2
FROM А, В
WHERE A.P2 = В.P1
с логической точки зрения приведет к формированию такого декартова произведения таблиц А (столбцы А Р1 и А Р2) и В (столбцы В.Р1 и В.Р2)
Примечание.
Столбец В.Р1, которого нет в результирующем запросе, показан для наглядности.Затем путем исключения из декартова произведения записей, не удовлетворяющих условию "A.P2 = В.Р1", будет получен такой результирующий НД:
Пример.
Выдать все записи о расходе товара из таблицы RASHOD Для каждого покупателя выдать его адрес из таблицы POKUPATELI (результат на рис 25 4)SELECT RASHOD.*, POKUPATELI.ADRES
FROM RASHOD, POKUPATELI
WHERE POKUPATELI.POKUP = RASHOD.POKUP
Рис 25.4 Как можно видеть, в результирующий НД не включены:
1. записи из таблицы RASHOD с пустым наименованием покупателя (записи с пустым наименованием покупателя в таблице POKUPATELI нет);
2. запись из таблицы POKUPATELI (POKUP = "Геракл"), поскольку по данному покупателю нет записей в таблице RASHOD.
Пример.
Используя оператор SELECT из предыдущего примера, выдать декартово произведение по всем столбцам таблицы RASHOD и по столбцу ADRES из таблицы POKUPATELI. Для этого нужно удалить из оператора SELECT предложение WHERE. Результат выполнения запроса не приводим из-за его величины (8 * 4 = 32 записи).SELECT RASHOD.*, POKUPATELI.ADRES
FROM RASHOD, POKUPATELI
Использование предложения WHERE
С использованием предложения WHERE оператор SELECT имеет следующий формат:
SELECT {* | <значение1> [, <значение2> ...]}
FROM <таблица1> [, < таблица2> ...]
WHERE <условия поиска>
В набор данных, который возвращается как результат выполнения оператора SELECT, будут включаться только те записи, которые удовлетворяют условию поиска. Ниже, в соответствующих подразделах, будут рассмотрены варианты формирования разнообразных условий поиска - их для оператора SELECT в SQL имеется достаточное количество, и все вместе они делают оператор исключительно мощным средством для построения запросов к БД.
Пока разберем два простейших условия поиска.
Использование псевдонимов таблиц
В приведенном выше примере оператора SELECT
SELECT RASHOD.*, POKUPATELI.ADRES
FROM RASHOD, POKUPATELI
WHERE POKUPATELI.POKUP = RASHOD.POKUP
в перечне возвращаемых столбцов после слова SELECT и в условии поиска после слова WHERE перед именем столбца через точку пишется название таблицы:
WHERE POKUPATELI.POKUP = RASHOD.POKUP
Указание имени таблицы перед именем столбца является совершенно необходимым, поскольку в разных таблицах могут оказаться одноименные столбцы (как в нашем примере), и SQL-сервер должен знать, со столбцом какой таблицы он имеет дело.
Использование общих имен таблиц для идентификации столбцов неудобно из-за своей громоздкости. Намного лучше присвоить каждой таблице какое-нибудь краткое обозначение, псевдоним. Такие псевдонимы называются псевдонимами таблиц. Они определяются после имени каждой таблицы в указании списка таблиц-источников после слова FROM:
SELECT
FROM <таблица1 псевдоним1> [, < таблица2 псевдоним2> ...]
WHERE ...
Например, приведенный выше запрос
SELECT RASHOD.*, POKUPATELI.ADRES
FROM RASHOD, POKUPATELI
WHERE POKUPATELI.POKUP = RASHOD.POKUP
после введения в него псевдонимов таблиц выглядит намного компактнее:
SELECT R.*, Р.ADRES
FROM RASHOD R, POKUPATELI P
WHERE P.POKUP = R.POKUP
Предложение ORDER BY - определение сортировки
Набор данных, выдаваемый в результате выполнения оператора SELECT, в общем случае возвращается в неотсортированном виде. Это удобно далеко не всегда. Определить, по каким полям необходимо отсортировать записи в результирующем НД, можно, указав после предложения, следующего за словом WHERE, предложение
ORDER BY <список_столбцов>
Список столбцов
должен содержать имена столбцов, по которым будет производиться сортировка.В случае указания имен нескольких столбцов, разделенных через запятую, столбец, указанный первым, будет использован для глобальной сортировки, второй столбец - для сортировки внутри группы, определяемой единым значением первого столбца, и т.д.
Пример. Выдать все записи отпуска товара "Кока-кола", отсортировав их по каждому покупателю (результат на рис. 25.5)
SELECT POKUP, DAT_RASH, TOVAR, KOLVO
FROM RASHOD
WHERE TOVAR = "Кока-кола"
ORDER BY POKUP
Пример.
Выдать все записи из таблицы RASHOD, отсортировав их по каждому покупателю (результат на рис. 25.6)SELECT POKUP, DAT_RASH, TOVAR, KOLVO
FROM RASHOD
ORDER BY POKUP
Пример.
Выдать все записи из таблицы RASHOD, отсортировав их по каждому покупателю, для каждого покупателя - по названию товара, для каждого товара - по дате (результат на рис. 25.8)SELECT POKUP, TOVAR, DAT_RASH, KOLVO
FROM RASHOD
ORDER BY POKUP, TOVAR, DAT_RASH
Устранение повторяющихся значений
Часто в результирующий НД необходимо включать не все записи с одинаковым значением какого-либо столбца (комбинации столбцов), а только одну из них. В этом случае после ключевого слова SELECT указывают ключевое слово DISTINCT
SELECT [DISTINCT | ALL] {* <значение1> [, <значение2> ...]}
FROM <таблица1> [, < таблица2> ...]
Повторяющимися считаются записи, содержащие идентичные значения во всех столбцах результирующего НД+.
Наоборот, если в результирующий запрос нужно включить все записи, после слова SELECT указывают слово ALL. В InterBase нет необходимости использовать ALL явно, поскольку это значение действует по умолчанию.
Пример.
Выдать наименования всех отпущенных со склада товаров (результат на рис. 25.9)SELECT DISTINCT TOVAR FROM RASHOD
Рис. 25.9.
ЗАМЕЧАНИЕ.
Использование в запросе DISTINCT следует ограничивать, кроме случаев, когда это действительно необходимо. Так, в рассматриваемых ниже примерах для агрегатных функций по таблице RASHOD, часто невозможно обойтись без указания DISTINCT.Причиной ограничений в применении DISTINCT является то обстоятельство, что его использование может резко замедлять выполнение запросов.
Расчет значений результирующих столбцов на основе арифметических выражений
Арифметические выраженияиспользуются для расчета значений вычисляемых столбцов результирующего НД. При их формировании следует придерживаться общих правил формирования арифметических выражений, принятых в алгоритмических языках, например Object Pascal для Delphi. При этом в списке возвращаемых столбцов после слова SELECT вместо имени вычисляемого столбца указывается выражение:
SELECT [DISTINCT ALL] {* | <столбец1> [, <выражение1> ...]} FROM <таблица1> [, < таблица2> ...]
Пример.
Выдать все записи об отпуске товаров из таблицы RASHOD, для каждого отпуска товара рассчитать общую стоимость отпущенного товара (результат на рис. 25.10)SELECT R.*, T.ZENA, R.KOLVO * T.ZENA FROM RASHOD R, TOVARY T WHERE R.TOVAR == T.TOVAR
Puc.25.10
Как видно из рисунка, результат вычисления выражения R.KOLVO * T.ZENA для каждой записи из таблицы RASHOD записан в сгенерированный столбец, которому по умолчанию присвоено имя COLUMN7. В случае, если нужно присвоить имя столбцу, содержащему результаты вычисления выражения, это имя можно указать после выражения вслед за ключевым словом AS:
SELECT ... {* | <значение1> [, <выражение1 [AS <имя столбца>]> ...]}
Пример.
Выдаваемому в предыдущем примере вычисляемому столбцу присвоить имя STOIM (рис. 25.11)SELECT R.*, T.ZENA, R.KOLVO * T.ZENA AS STOIM FROM RASHOD R, TOVARY T
WHERE R.TOVAR = T.TOVAR
Рис. 25 11.
Агрегатные функции
Агрегатные функции предназначены для выдачи итоговых значений. К агрегатным относятся функции:
COUNT (<выражение>) -
подсчитывает число вхождений значения выражения во все записи результирующего НД;• SUM(<выражение>) - суммирует значения выражения;
AVG (<выражение>) - находит среднее значение выражения;
• МАХ(<выражение>) - определяет максимальное значение выражения;
• МIN(<выражение>) - определяет минимальное значение выражения. Если из группы одинаковых записей нужно учитывать только одну, перед выражением в скобках включают слово DISTINCT
COUNT(DISTINCT POKUP)
Чаще всего в качестве выражения выступают имена столбцов. Выражение может вычисляться и по значениям нескольких таблиц. Пример. Подсчитать число покупателей, приобретавших товары на складе (рис.25.12)
SELECT COUNT(DISTINCT POKUP) AS COUNT_POKUP FROM RASHOD
Пример.
Вычислить общую стоимость отпущенных товаров за 10.01.97 (результат на рис. 25.13)SELECT SUM(R.KOLVO * T.ZENA) AS OBS_ZENA
FROM RASHOD R, TOVARY T
WHERE (R.TOVAR = T.TOVAR) AND
(R.DAT RASH = "10-JAN-1997")
Использование группировок записей
Часто нужно выдать агрегированные значения (минимум, максимум, среднее) не по всему результирующему НД, а по каждой из входящих в него групп записей, характеризующихся одинаковым значением какого-либо столбца. Например, выдать общее число отпущенного товара по каждому товару. В этом случае в оператор SELECT перед предложением WHERE вводят предложение
GROUP BY столбец [,столбец1 ...]
При этом необходимо, чтобы один из столбцов результирующего НД был представлен агрегатной функцией.
Пример.
Выдать общее количество отпуска по каждому из товаров (результат на рис. 25.14)SELECT R.TOVAR, SUM(R.KOLVO) AS OTPUSK FROM RASHOD R GROUP BY R.TOVAR
Пример.
Выдать общую сумму отпуска по каждому из товаров (результат на рис. 25.15)SELECT R.TOVAR, SUM(R.KOLVO * T.ZENA) FROM RASHOD R, TOVARY T
WHERE T.TOVAR = R.TOVAR GROUP BY R.TOVAR
Пример.
Выдать общую сумму отпуска по каждому из товаров на каждую дату (результат на рис. 25.16)SELECT R.TOVAR, R.DAT_RASH, SUM(R.KOLVO * T.ZENA)
FROM RASHOD R, TOVARY T
WHERE T.TOVAR = R.TOVAR GROUP BY R.TOVAR, R.DAT_RASH
Пример.
Выдать число покупателей на каждую дату (результат на рис. 25.17)SELECT DAT_RASH, COUNT(DISTINCT POKUP)
FROM RASHOD GROUP BY DAT RASH
Предложение HAVING - наложение ограничений на группировку записей
Если нужно в результирующем НД выдавать агрегацию не по всем группам, а только по тем из них, которые отвечают некоторому условию, после предложения GROUP BY указывают предложение
HAVING < условия_поиска >
где условия поиска указываются по тем же правилам, что и условия поиска для предложения WHERE, за важным исключением: в условии поиска предложения HAVING можно указывать агрегатные функции, чего нельзя делать в условии поиска для WHERE.
Пример.
Выдать минимальные покупки товара в единицах для всех покупателей, у которых минимальное количество покупаемого товара не меньше 100 единиц (результат на рис. 25.18).SELECT POKUP, MIN(KOLVO) FROM RASHOD
GROUP BY POKUP HAVING MIN(KOLVO) >= 100
Если не указывать
HAVING MIN(KOLVO) >= 100
будут выданы все группы (рис. 25.19).
Можно указывать различные агрегатные функции для возвращаемого столбца и условия в HAVING.
Пример.
Выдать общее количество купленного товара (в единицах) для всех покупателей, у которых минимальное количество покупаемого товара не меньше 100 единиц (результат на рис. 25.20).Использование логических выражений
Может указываться более одного условия поиска. В этом случае они объединяются между собой при помощи логических операторов AND, OR и NOT. Их использование, а также построение из них сложных выражений подчиняется стандартным правилам, принятым для большинства алгоритмических языков (например, Object Pascal для Delphi) с одним важным исключением: операции отношения в них имеют меньший приоритет, чем логические операции, что избавляет от необходимости расстановки многочисленных скобок в сложных условиях поиска (я не работал с InterBase, но в диалекте SQL для TQuery и для MS SQL-сервер это так. Либо удалить это замечание, либо исправить примеры ниже по тексту).
Пример.
Выдать все записи из таблицы RASHOD, для каждого товара выдать его цену из таблицы TOVARY, для каждого покупателя выдать его город из таблицы POKUPATELI (результат на рис. 25.23).SELECT R.*, T.ZENA, P.ADRES FROM RASHOD R, TOVARY T, POKUPATELI P
WHERE (R.TOVAR = T.TOVAR) AND (R.POKUP = P.POKUP)
Рис.
25.23.Пример.
Выдать записи из таблицы RASHOD плюс соответствующую каждому товару цену из таблицы TOVARY. При этом количество отпуска товара должно быть не больше 30 или должно быть не меньше 3000, и название покупателя должно присутствовать (результат на рис. 25.24).SELECT R.*, T.ZENA FROM RASHOD R, TOVARY T
WHERE (R.TOVAR = T.TOVAR) AND ((R.KOLVO <=30) OR (R.KOLVO >= 3000)) AND (R.POKUP IS NOT NULL)
ORDER BY R.KOLVO
Puc. 25.24.
Сравнение столбца с результатом вычисления выражения
Условие поиска в предложении WHERE может быть сформулировано при помощи выражения:
<выражение> <оператор> <столбец>
Может использоваться и другой способ написания условия поиска, <столбец> <оператор> <выражение>
однако оно чаще всего применяется при использовании механизма вложенных подзапросов (вложенных операторов SELECT), речь о которых пойдет ниже. В этом случае результат вычисления выражения сравнивается с содержимым указанного столбца.
Пример.
Выдать из таблицы RASHOD дату, товар, стоимость отпущенного товара. При этом показывать только записи, у которых стоимость отпущенного товара больше 120 (результат на рис. 25.25).SELECT R.DAT_RASH, R.TOVAR, (R.KOLVO * T.ZENA) AS STOIM FROM RASHOD R, TOVARY T
WHERE (R.TOVAR = T.TOVAR) AND ((R.KOLVO * T.ZENA) > 120)
ORDER BY R.DAT_RASH
Использование BETWEEN
В условие поиска можно указать, что некоторое значение (столбец или вычисление значения выражения ) должно находиться в интервале между значением! и значением!'.
<значение> [NOT] BETWEEN <значение1> AND <значение2>
Зарезервированное слово NOT инвертирует условие (значение не должно находиться в интервале между значением! и значением 2).
Пример.
Выдать сведения обо всех отпусках товара, где количество отпущенного товара (в единицах) лежит в диапазоне 1000..3000 (результат на рис. 25.26).SELECT * FROM RASHOD WHERE KOLVO BETWEEN 1000 AND 3000
IN.RASH
DAT_RAS
[KOLVO
Использование IN (список значений)
Если нужно, чтобы значение какого-либо столбца (или результат вычисления некоторого выражения) совпадало с одним из дискретных значений, в условии поиска указывается предложение
<значение> [NOT] IN (<значеиие1> 1, <значение2> ...1)
Тогда в результирующий набор данных будут включены только те записи, для которых значение, стоящее слева от слова IN, равно одному из значений, указанных в списке (<значение1> [, <значение2>...]).
Пример.
Выдать сведения обо всех отпусках товара, где количество отпущенного товара (в единицах) равно или 100, или 1000, или 3000 (результат на рис. 25.27).SELECT * FROM RASHOD WHERE KOLVO IN (100, 1000, 3000)
N_RAS'
DAT_RAS
KOL
TOVAR
POKUP
4
10.01.97
3000
Ставрида консерв.
:Аамирал.АО ;
8
,20.01.97
1000
Кока-кола
Адмирал АО
1
10.01.97
100
Кока-кола
Адмирал, АО
Рис. 25.27.
ЗАМЕЧАНИЕ.
Существует вторая форма использования IN, где список возможных значений возвращается вложенным подзапросом. Этот вариант рассматривается в подразделе, посвященном подзапросам.Использование STARTING
Если в условии поиска нужно, чтобы значение какого-либо символьного столбца или выражения начиналось с определенной подстроки, в условии поиска необходимо указать предложение
<значение> (NOT) STARTING [WITH] <подстрока>
Тогда в результирующий запрос будут включены только те строки, для которых выполняется указанное условие.
Пример.
Выдать все товары, начинающиеся с буквы "С" (результат на рис.25.28).SELECT * FROM TOVARY WHERE TOVAR STARTING WITH "C"
Использование CONTAINING
Если нужно, чтобы значение какого-либо символьного столбца или выражения включало в себя (неважно, начиная с какого символа) определенную подстроку, в условии поиска необходимо указать предложение
<значение> [NOT] CONTAINING <значение>
Тогда в результирующий запрос будут включены только те строки, для которых выполняется указанное условие.
Пример.
Выдать список всех покупателей, чей адрес содержит подстроку "Стр" (результат на рис. 25.29)SELECT * FROM POKUPATELI WHERE ADRES CONTAINING "Стр"
Использование функции UPPER
Функция UPPER(<значение>) используется для преобразования букв символьных значений (содержимого столбца, результата вычисления выражения) к заглавным. Обычно эта функция используется в условиях поиска, когда необходимо игнорировать возможную разницу в высоте букв.Функция UPPER может фигурировать как в списке столбцов результирующего набора данных (после слова SELECT), так и в условии поиска в предложении WHERE.
Пример.
Пусть нам необходимо найти всех покупателей из Москвы. Однако в таблице POKUPATELI два покупателя имеют в столбце GOROD имя города Москвы, однако в одном случае это значение 'Москва', в другом 'МОСКВА'. Если попытаться выполнить следующий запрос, то будет выдан только один покупатель (рис. 25.30). То есть результирующий НД будет неполным:SELECT * FROM POKUPATELI WHERE GOROD = 'Москва'
Проблема решается приведением обеих составляющих условия поиска к одному виду (рис. 25.31).
SELECT * FROM POKUPATELI WHERE UPPER(GOROD) = 'МОСКВА'
Использование LIKE
Предложение LIKE задает шаблоны сравнения строковых значений. Если необходимо, чтобы сравниваемое значение (значение столбца или результат вычисления строкового выражения) удовлетворяло шаблону, в условии поиска необходимо указать
<значение> [NOT] LIKE < шаблон> [ESCAPE <подшаблон>]
В шаблоне используются специальные символы - "%" и "_" . Символ "%" (процент) используется для указания любого значения любой длины и символ "_" (подчеркивание) для указания любого единичного символа. Например:
LIKE "%USD" - указывает, что сравниваемое значение должно оканчиваться символами 'USD' (Надо раз и навсегда решить, какими символами выделять строки. Обычно в тексте книги используются типографские или двойные кавычки, а в примерах - по требованию синтаксиса - апострофы. Проверить и исправить дальше по тексту), независимо от того, какие символы и сколько расположены перед ними;
LIKE "_94" - указывает, что сравниваемое значение может содержать 4 символа, из которых первые два - любые и последние два - '94'.
ESCAPE <подша6лон> используется, если в предложении LIKE символы '%' или '_' должны использоваться в шаблоне подобия как обычные символы (без учета их специальных функций). В этом случае с помощью ESCAPE указывается символ, появление которого в шаблоне отменяет специальные функции следующего за ним символа WHERE STOLBEZ LIKE "%!%" ESCAPE "!"
Пример.
Пусть нужно выдать информацию о покупателе, имя которого мы забыли, и название улицы мы помним неточно - то ли Стромынка, то ли Стормынка, то ли Сторомынка. Точно помним, что название улицы заканчивается на 'мынка'. Тогда нужно выполнить такой запрос (результат на рис. 25.32).SELECT * FROM POKUPATELI WHERE ADRES LIKE "%мынка%"
При этом шаблон "%мынка%" заканчиваем символом '%' потому, что после названия улицы в адресе идут другие реквизиты, которых, естественно, мы не помним.
