Типы данных. Большинство SQL-серверов имеют схожие типы данных, хотя имеются и различия

Большинство SQL-серверов имеют схожие типы данных, хотя имеются и различия. После того обилия типов, к которому мы привыкли в базах данных Paradox или MS Access, может показаться, что SQL-серверы имеют очень ограниченный набор типов данных. Связано это в первую очередь, с ограничениями стандартов языка запросов SQL.

Например, InterBase не имеет:

• Тип автоинкремент (автоматически увеличивающееся числовое поле). Нехватку такого важного типа можно компенсировать специальными числовыми генераторами.
• Тип Boolean. Вместо него предлагается использовать символьный тип данных Char(1).
• Тип Currency. Денежные типы данных придется хранить в обычных столбцах с вещественным типом данных.

В InterBase все настолько взаимосвязано, что очень сложно говорить о какой-то одной теме, не затрагивая при этом другие. На этой лекции мы коротко затронем вопрос создания таблиц, хотя полностью развернем эту тему позже. Таблицы создаются с помощью специального SQL-запроса, и для этого проще всего использовать утилиту для работы с базами данных. Если InterBase сервер у вас не запущен, запустите его. Также откройте утилиту IBConsole, войдите в локальный сервер и откройте базу данных FIRST. На панели задач находится кнопка Interactive SQL, которая запускает встроенную утилиту для редактирования выделенной базы данных:

Рис. 16. Значок утилиты " Interactive SQL"

Нажав на эту кнопку, вы увидите окно Интерактивного SQL:

Рис. 17. Interactive SQL

На рисунке указаны основные кнопки, с которыми вам придется работать, и два окна: для ввода запроса, и для вывода результатов запроса. Строка состояния содержит адрес и имя текущей базы данных, в нашем случае это C: \DataBases\first.gdb. Все запросы, которые мы будем вводить, будут относиться к этой БД.

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

• Целые числа

InterBase поддерживает два типа целых чисел:

SMALLINT - Короткое целое число (2 байта) со знаком. Диапазон значений от -32768 до 32767.

INTEGER (INT) - Длинное целое число (4 байта) со знаком. Диапазон значений от - 2147483648 до 2147483647.

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

В окне для запросов впишите следующий запрос:

CREATE TABLE Table_Cel(

Korotkoe SMALLINT,

Dlinnoe INT)

и нажмите кнопку Execute Query (Выполнить запрос). Этим запросом мы дали команду создать таблицу с именем " Table_Cel", которая содержит два поля: " Korotkoe", имеющее тип SMALLINT, и " Dlinnoe" с типом INTEGER.

Если вы все сделали правильно, запрос исчезнет. Далее можно закрыть Interactive SQL или просто перейти на основное окно IBConsole. В дереве серверов выделите раздел Tables базы данных FIRST, и в правом окне увидите таблицу TABLE_CEL:

Рис. 18. Новая таблица создана

Чтобы окончательно убедиться, что все получилось правильно, щелкните правой кнопкой по получившейся таблице и выберите команду Properties. Выйдет окно со списком полей на вкладке Properties. Вкладка Metadata содержит запрос, по которому была создана таблица, а на вкладке Data можно увидеть записи таблицы:

Рис. 19. Отображение вкладки Metadata свойств таблицы TABLE_CEL

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

• Вещественные числа

К вещественным типам относятся:

FLOAT - Число с плавающей точкой одинарной точности (4 байта). Диапазон от 3, 4*10^-38 до 3, 4*10⁺³⁸. Значащих цифр 7.

DOUBLE PRECISION - Число с плавающей точкой двойной точности (8 байт). Диапазон от 1, 7*10^-308 до 1, 7*10⁺³⁰⁸. Значащих цифр 15.

Типа данных REAL в InterBase не существует, однако попытка создать поле такого типа не приведет к ошибке, вместо этого InterBase создаст поле типа FLOAT. Попытки создать поле других вещественных типов, которые имеются в Delphi, приведут к ошибке.

Тип FLOAT не рекомендуется использовать там, где нужна точность расчетов дробных значений, особенно в денежных полях. Вместо привычного типа CURRENCY, которого нет в InterBase, лучше подойдет тип DOUBLE PRECISION. Пример:

CREATE TABLE Table_Vesh(

Kol_Float FLOAT,

Kol_Double DOUBLE PRECISION)

Здесь первое поле может содержать вещественные числа, имеющие не более 4 знаков после запятой. При попытке сохранить число " 1, 234567" реально будет записано число " 1, 2345". Второе поле обеспечивает большую точность сохраняемых данных.

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

• Числа с фиксированной точкой

Таких типов два: DECIMAL и NUMERIC, оба типа данных практически равнозначны. Не самые удобные в использовании типы. Они призваны задавать фиксированное количество чисел после запятой. Как известно, вещественные числа определяются двумя значениями:

• Размер - общее количество цифр.
• Точность - количество цифр после запятой.

DECIMAL / NUMERIC (Размер, точность) - Числа с плавающей точкой переменной точности. Оба типа равнозначны и могут использоваться с одинаковым результатом. Размер (от 1 до 15) указывает число значащих цифр. Точность (от 0 до 15) указывает число знаков после запятой. Точность должна быть меньше или равна размеру. Например,

Поскольку специальных типов DECIMAL и NUMERIC на самом деле не существует, вместо них используются другие типы столбцов. Правила таковы:

• При указании размера числа от 1 до 4 будет использован столбец SMALLINT.
• При указании размера числа от 5 до 9 будет использован столбец INTEGER.
• При указании размера числа от 10 до 15 будет использован столбец DOUBLE PRECISION.

Как видите, в первых двух случаях используются целые типы столбцов, и если даже вы укажете точность, числа после запятой будут утеряны. Следовательно, чтобы сохранить вещественное число в полях DECIMAL и NUMERIC, требуется указать размер не менее 10 чисел. Пример запроса:

CREATE TABLE Table_Fiks(

Kol_Dec DECIMAL(15, 2),

Kol_Dec2 NUMERIC(8, 2))

При, казалось бы, похожем объявлении в первом случае мы получили вещественное поле, в котором может храниться число типа 9999999999999, 99, зато второе поле сможет хранить только целую часть числа. То есть, при попытке сохранить число 123456, 78, в таблице останется число 123456, а поле будет иметь тип INTEGER.

• Даты и время

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

TIMESTAMP - тип данных, совместимый с типом TTimeStamp в Delphi. Содержит одновременно и дату, и время в виде двух целых 32-битовых чисел. Первое число содержит порядковый номер дня, прошедший с 01.01.0001. Второе число указывает количество миллисекунд, прошедших с полуночи. Этот тип может применяться как в первом, так и в третьем диалектах SQL.

DATE - тип данных для хранения значений только дат. Диапазон от 1 января 0001 г. до 31 декабря 9999 г. Поддерживается только в базах данных, использующих третий диалект SQL. Попытка создать поле типа DATE в базах данных с первым диалектом ошибки не вызовет, однако реально будет создано поле типа TIMESTAMP.

TIME - тип данных для хранения значений только времени. Диапазон времени от 00: 00 до 23: 59: 59: 9999. Поддерживается только в базах данных, использующих третий диалект SQL. Попытка создать поле типа TIME в базах данных с первым диалектом приведет к ошибке.

Обычно данные этих типов заполняются на компьютере клиента, а там все зависит от настроек операционной системы. Не следует забывать, что даты на компьютере пользователя могут выводиться по-разному, в зависимости от настроек ОС. Например, в английской вариации дат вначале указывается месяц, затем день, и год, тогда как у нас первым идет день. Кроме того, английская (американская) неделя начинается не с понедельника, а с воскресенья. А разделителем значений могут быть и точка ".", и слеш " /", и тире " -". Чтобы избежать конфликтов при работе вашей программы (клиентской части), полезно сразу программно переустановить формат даты и времени на компьютере клиента в привычный российский формат (пример ниже приводится для программ Delphi, а не для Interactive SQL):

DateSeparator: = '.';

ShortDateFormat: = 'dd.mm.yyyy';

ShortTimeFormat: = 'hh: mm: ss';

После этого в клиентской части программы дату и время спокойно можно вводить в привычном для нас формате. Пример создания таблицы с полями даты и времени (пример корректен только для 3 диалекта SQL):

CREATE TABLE Table_DateTime(

Kol_Date DATE,

Kol_Time TIME,

Kol_TimeStamp TIMESTAMP)

• Текстовые типы

Существует два текстовых типа данных: CHARACTER(n) (в сокращенном варианте CHAR(n))и CHARACTER VARYING(n) (в сокращенном варианте VAR CHAR(n)).

n - указывает количество символов в поле. В обоих типах n может быть от 1 до 32767 символов (32 Кбайт). Если n не указать, по умолчанию будет присвоено число 1. Пример создания полей:

CREATE TABLE Table_Text(

Kol_Char CHAR,

Kol_Varchar VARCHAR(255))

В первом случае будет создано поле размером 1 символ, во втором случае в поле можно сохранить текст размером до 255 символов. Между этими типами данных есть отличия. Тип CHAR предназначен для хранения текста фиксированной длины. Другими словами, если мы определим поле в 10 символов, а запишем только 5, то текст будет дополнен завершающими пробелами до полной длины. Тип VARCHAR хранит текст переменной длины, и возвращает сохраненный текст без завершающих пробелов. Тем самым, использование типа VARCHAR в большинстве случаев является предпочтительным.

Еще важными факторами при работе с текстовыми полями является кодировка символов и порядок их сортировки. Как мы знаем из прошлой лекции, при создании базы данных можно указать кодировку " по умолчанию", для кириллицы предпочтительней будет кодировка WIN1251. При этом все создаваемые текстовые поля будут иметь эту кодировку, в чем несложно убедиться, выполнив описанный выше запрос, и посмотрев вкладку Metadata созданной таблицы:

Рис. 20. Установки кодировки " по умолчанию"

Как видно из рисунка, с помощью оператора CHARACTER SET кодировку можно указать явно при описании поля:

CREATE TABLE Table_Text2(

Kol_Char CHAR CHARACTER SET WIN1251,

Kol_Varchar VARCHAR(255) CHARACTER SET ASCII)

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

Для символьных полей также возможно указывать порядок сортировки COLLATE. Этот порядок определяет способ, по которому будут сортироваться и сравниваться текстовые данные при выводе их оператором SELECT. Для кодировки WIN1251 это может быть сортировка WIN1251 или PXW_CYRL. В первом случае русские символы сортируются как

АБВ….эюя

что не очень удобно. Сортировка PXW_CYRL предпочтительней, она выводит русские символы в таком порядке:

аАбБ…яЯ

Пример:

CREATE TABLE Table_Text3(

Kol_Char CHAR CHARACTER SET WIN1251 COLLATE WIN1251,

Kol_Varchar VARCHAR(255) CHARACTER SET WIN1251 COLLATE PXW_CYRL)

• Тип данных BLOB

BLOB (Binary Large Object - Большой двоичный объект) - Поле неограниченного размера, может содержать любой тип двоичных данных, например, файл с фотографией, мелодией или цифровой книгой, или просто большой текст (аналог MEMO). Пример определения поля:

CREATE TABLE Table_BLOB(

Kol_BLOB BLOB)

Поля BLOB несколько отличаются по способу хранения данных от других типов данных. Все остальные поля записи хранятся на одной странице рядом, одно за другим. Поле BLOB сохраняет на этой странице только идентификатор записи, а сама запись хранится на отдельной странице (страницах). Таким образом, оказалось возможным хранить данные большого размера, не ухудшая в целом работу всей БД.

У этого типа есть возможность указывать явно подтип: целое число, определяющее тип хранимых в поле данных. По умолчанию, 0 указывает на двоичные данные и 1 указывает на ASCII - текст. Программист может указывать собственные типы, используя отрицательные значения. Подтип указывается оператором SUB_TYPE:

CREATE TABLE Table_BLOB2(

Kol_BLOB BLOB SUB_TYPE -1)

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

• Столбцы - массивы

В InterBase столбец любого типа, кроме BLOB, может быть объявлен, как массив. Такие столбцы, вместо единичного значения записи, содержат массив значений одинакового типа. Доступ к каждому значению возможен с помощью указания индекса. По умолчанию, нумерация элементов массива начинается с 1, однако программист может явно указать границы элементов:

CREATE TABLE ARRAY_TABLE(

ARRAY1 INTEGER [10],

ARRAY2 FLOAT [0: 10, 5: 10]);

В этом примере была объявлена таблица из двух столбцов-массивов. В первом случае объявлен массив целых чисел с диапазоном от 1 до 10 элементов, во втором случае объявлен двухмерный массив вещественных чисел с указанными программистом границами элементов. Это эквивалентно объявлению в Delphi двух переменных-массивов:

Var

Array1 [1..10] of Integer;

Array2 [0..10, 5..10] of Float;

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

• Логический тип

Как уже говорилось выше, в InterBase не поддерживаются типы Boolean. Вместо этого предлагается использовать тип CHAR(1), который создает односимвольный столбец, и вводить значения типа T/F, Y/N, Д/Н, 1/0, +/- и т.п. Проверка логики и правильности ввода значения возлагается на клиентское приложение.

• Домены

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

CREATE TABLE Table_Firma(

Familiya VARCHAR(20) CHARACTER SET WIN1251 COLLATE PXW_CYRL,

Imya VARCHAR(20) CHARACTER SET WIN1251 COLLATE PXW_CYRL,

Otchestvo VARCHAR(20) CHARACTER SET WIN1251 COLLATE PXW_CYRL)

Но обратите внимание на то, что нам пришлось много раз повторить одно и то же описание типа поля. А если такой же тип будет содержаться и в других таблицах? Гораздо предпочтительней создать домен, который затем и указывать при описании поля. Домен создается таким образом:

CREATE DOMAIN FIO AS VARCHAR(20) CHARACTER

SET WIN1251 COLLATE PXW_CYRL

Выполните последний запрос в Interactive SQL, затем в дереве серверов утилиты IBConsole выделите раздел Domains и вы увидите созданный нами домен. Теперь создание таблицыTable_Firma существенно упростится:

CREATE TABLE Table_Firma(

Familiya FIO,

Imya FIO,

Otchestvo FIO)

Этот же домен можно использовать в описании полей любой таблицы в пределах текущей базы данных.