logo
Книга по БД(Вальке А

5.1. Типы данных, доступные в sql

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

Прежде всего, следует бороться с "очевидностью". Например, в банковском деле имеется термин "номер счета". Сразу возникает желание использовать для данного атрибута тип "целое". Но может оказаться, что номер счета формируется достаточно специфично - например, он состоит из 9 цифр, средние 3 которых определяют категорию счета. И если в Вашей задаче требуется осуществлять поиск счетов той или иной категории, то реализовать этот поиск для типа "целое" очень непросто, а для типа "символьная строка" - достаточно просто. И, возможно, использовать тип "символьная строка" для данного атрибута предпочительнее, чем тип "целое".

В варианте SQL, реализованным фирмой Informix, имеются следующие типы данных:

INTEGER и SMALLINT - целое и короткое целое. Для их представления используется, соответственно, 4 и 2 байта.

Следовательно, диапазон допустимых значений для INTEGER будет от -2 147 483 647 до 2 147 483 647, а для SMALLINT - от -32 767 до 32 767. Данный тип используется для представления счетчиков, кодов чего-либо и т.д. К недостаткам данного типа данных следует отнести ограниченный набор значений. Впрочем, это не проблема, если Вы уверены, что для Вашего поля допустимый диапазон значений данного типа подходит.

SERIAL - этот тип данных основан на типе INTEGER, но имеет одну очень важную особенность. Этот тип предназначен для создания и хранения уникального ключа для записей в таблице. Только одно поле в таблице может иметь тип SERIAL. Когда Вы вставляете в таблицу, содержащую поле данного типа, новый ряд, то СУБД автоматически выберет для Вас новое уникальное значение данного типа. Вы можете получить это сгенерированное значение и использовать его в дальнейшей работе.

FLOAT и SMALLFLOAT - типы для представления нецелых чисел. Обычно поля данного типа используются для представления научных, экспериметальных, статистических величин. Внутреннее представление значений данного типа состоит из мантиссы и порядка. То есть значения данного типа могут лежать в очень широком диапазоне, но точными будут только несколько цифр.

Объем памяти (число байтов), выделяемых для хранения значений данного типа зависит от компьютера, но обычно составляет 8 байт для типа FLOAT и 4 байта для SMALLFLOAT. При таком представлении точность типа FLOAT составляет 16 десятичных цифр, а SMALLFLOAT - 8 цифр.

Типы FLOAT и SMALLFLOAT имеют одну особенность - из-за внутреннего двоичного представления нельзя гарантировать, что значения данного типа будут представлены точно. Поэтому данный тип нельзя использовать для хранения денежных сумм или других величин, которые нельзя округлять. Для хранения "неокругляемых" значений используются другие типы данных - DECIMAL и MONEY.

DECIMAL(p) - этот тип аналогичен FLOAT, но предназначен для хранения данных с фиксированным числом значащих цифр. Например, если Вы знаете, что некоторое значение всегда измеряется с точностью до пяти знаков, то это как раз тот тип данных, который Вам нужен. Чмсло значащих цифр (параметр 'p') может быть в пределах от 1 до 32. Диапазон допустимых значений для данного типа находится в пределах от 10^-128 до 10^126.

Для представления данных типа DECIMAL(p) используется двоично-десятичное представление. Следовательно, число байтов, необходимое для представления одного значения типа DECIMAL(p) будет равно 1+p/2.

По сравнению с типом FLOAT тип DECIMAL(p) имеет следующие преимущества:

- можно регулировать точность представления;

- размер требуемой памяти зависит от точности;

Но есть у типа DECIMAL(p) и недостатки по сравнению с типом FLOAT:

- операции сортировки и арифметические операции требуют большего, чем для типа FLOAT, времени;

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

DECIMAL (p,n) - этот тип предназначен для хранения данных не только с фиксированным числом значащих цифр, но и с фиксированным числом значащих цифр после запятой. Параметр 'p' задает общее число десятичных цифр в числе, а 'n' - сколько десятичных цифр будет после запятой. Таким образом, данный тип данный позволяет хранить числа, в десятичном представлении которых не более 32 цифр. Размер памяти для хранения одного значения данного типа составляет 1+p/2 байт.

По сравнению с типами INTEGER и FLOAT тип DECIMAL(p,n) имеет те же достоинства и недостатки, что и тип DECIMAL(p).

MONEY (p,n) - этот тип предназначен для хранения денежных величин. Он полностью аналогичен типу DECIMAL(p,n), но отличается от него тем, что в некоторых языках (например, INFORMIX 4GL) существуют специальные способы форматирования для этого типа на основе некоторых внешних по отношению к программе переменных окружения. Это позволяет писать программы, не зависящие от способа написания денжных величин в каждой конкретной стране (например, величина 3000000 будет печататься или как $3,000,000.00 или как 3.000.000,00Рбл).

DATE - этот тип предназначен для хранения дат. По сути, значение этого типа представляет собой число дней, прошедших с 31 декабря 1899 года. Так как это значение может быть отрицательным, то можно хранить и даты до 1899 года. Под значения этого типа выделяется 4 байта, поэтому диапазон допустимых значений очень широк - около 58 000 столетий вперед и назад. Так же как и для типа MONEY, форматирование ввода и вывода для переменных данного типа может быть указано с помощью внешних переменных.

DATETIME - этот тип предназначен для хранения точных моментов времени. Тип DATETIME содержит информацию о годе (YEAR), месяце (MONTH), дне (DAY), часе (HOUR), минуте (MINUTE), секунде (SECOND) и долях секунды (FRACTION). Вы может выбирать нужный диапазон этих значений. Например, если Вам нужен момент времени с точностью до секунды в течении дня, то следует указать тип DATETIME HOUR TO SECOND. Если же Вам нужна информация о событиии с точностью до минуты, но в произвольном году, то требуемый тип должен записываться как DATETIME YEAR TO MINUTE. При указании долей секунды, надо указывать точность представления - от десятых до тысячных. Соответственно, FRACTION(1) указывает время с точностью до десятых, FRACTION(2) - до сотых, а FRACTION(3) - до тысячных.

По сравнению с типом DATE тип DATETIME может хранить данные более точно, однако он требует больше места для хранения и обрабатывается более медленно.

INTERVAL - тип данных для хранения временных интервалов. Значение типа INTERVAL получается, когда, например, из одной даты вычитают другую. Так же как и DATETIME, следует уточнить диапазон возможных значений.

CHAR и CHAR(n) - тип для хранения символьных строк фиксированной длины. Для типа CHAR(n) параметр 'n' задает длину строки. Тип CHAR без параметра является одиночным символом и идентичен типу CHAR(1). Максимальная длина данного типа (значение параметра 'n') равно 32 511. Для хранения данных данного типа всегда отводитя n байт вне зависимости от реальной длины строки.

VARCHAR(m) - короткая (до 255 символов) символьная строка переменной длины. Параметр 'm' задает максимальную длину строки, но не более 255. Для хранения строк переменной длины произвольного размера испльзуеся тип TEXT. При хранении значений типа VARCHAR используется столько байтов, сколько реально занимает строка.

По сравнению с типом CHAR(n), тип VARCHAR имеет то преимущество, что экономится место при хранении строк разной длины. Но такой способ хранения замедляет операции модификации данных.

TEXT - этот тип предназначен для хранения символьных строк произвольной переменной длины. По сравнению с типом VARCHAR тип TEXT имеет повышенные накладные расходы, но не имеет практического ограничения по длине, Например, для сервера Informix DS максимальная длина значения типа TEXT составяет 2 Гигабайта.

BYTE - тип для хранения двоичных объектов произвольного объема. Этот тип данных можно (и нужно) использовать для хранения исполняемых файлов, оцифрованных картинок, звука и т.д. Максимальная длина значения данного типа (для сервера Informix DS ) ограничена 2 Гигабайтами.

У каждого из перечисленных выше типов данных свои операции, свои значения. Но существует одно общее для всех типов значение ­NULL. Это значение "не определено". Не надо путать это значение с нулем. Значение "нуль" - это вполне определенное значение, а NULL означает, что никакого определенного значения в атрибут или в переменную никогда не записывалось или явно было присвоено значение NULL.

5.2. SQL-операторы создания схемы базы данных

Итак, Вы знаете, какие типы данных можно использовать. Вы провели анализ своей задачи, выделили атрибуты, определили ключи и решили, что Ваша база данных будет состоять из таких-то и таких-то таблиц. Каждая таблица будет содержать такие-то и такие-то поля. Другими словами, Вы разработали схему своей базы данных. Рассмотрим операторы языка SQL, которые позволяют создать на SQL-сервере нужную Вам схему базу данных.

Первым делом надо создать саму базу данных. Для этого используется оператор

CREATE DATABASE <имя базы>

В качестве имени базы данных Вы можете использовать любое имя, состоящее из латинских букв, цифр и символа подчеркивания. Имя должно начинаться с буквы или символа подчеркивания. Если используемые SQL-сервер и операционная система обеспечивают NLS (National Language Support - поддержка национальных языков)8), или SQL-сервер поддерживает GLS (Global Language Support – глобальная поддержка языков)9, то Вы можете использовать для именования и национальные символы. Кстати, большие и маленькие буквы не различаются. Максимальная длина имени составляет 14 символов. Вы не можете создать на одном SQL-сервере две базы данных с одинаковым именем.

Пример:

CREATE DATABASE my_base

В SQL существует понятие "текущая база данных". После того, как Вы создали базу данных оператором CREATE DATABASE, она становится текущей. Все указываемые таблицы, все индексы берутся из текущей базы данных. В каждый момент времени для каждой задачи существует единственная текущая база данных (или таковой не существует вовсе). Это не означает, что Вы не можете обратиться к таблице из другой базы данных, просто при обращении к такой таблице надо кроме имени таблицы, еще указывать и имя базы данны, а если база данных расположена на другом сервере, то и имя сервера базы данных.

Сразу опишем и обратный оператор - оператор удаления базы данных

DROP DATABASE <имя базы>

Пример:

DROP DATABASE my_base

Естественно, удаляемая база должна существовать и Вы должны иметь право на ее удаление (но о правах несколько позднее). Кстати, Вы не можете удалить текущую базу.

Если база данных уже существует, то сделать ее текущей (открыть ее) можно оператором

DATABASE <имя базы>

Пример:

DATABASE my_base

Закрытие текущей базы данных производится оператором

CLOSE DATABASE

Итак, предположим, что база данных создана. Для того, чтобы создавать таблицы используется оператор CREATE TABLE. Его синтаксис

CREATE TABLE <имя таблицы> ( <имя колонки> <тип колонки> [ NOT NULL], <имя колонки> <тип колонки> [ NOT NULL] [, ...] )

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

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

CREATE TABLE companies ( company_id SERIAL, name CHAR(40) NOT NULL, address CHAR(60) )

При создании данной таблицы мы явно указали, что поле name (название) не может быть неопределенным, но поле address (адрес) вполне может быть неопределено.

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

CREATE TABLE items ( item_id SERIAL, company INTEGER NOT NULL, name CHAR(40) NOT NULL, price MONEY(20,2) )

Таблицы 'companies' и 'items' связаны. Логическая связь обеспечивается полем 'company' в таблице 'items' и полем 'company_id' в таблице 'companies'. Поле 'company' данной таблицы содержит идентификатор поставщика. То есть если мы хотим найти поставщика некоторого товара, то надо из этой таблицы для нужной записи выбрать значение поля 'company', а затем, просмотрев таблицу 'companies' найти запись, поле 'company_id' равно найденному значению.

Оператор удаления таблицы очень похож на оператор удаления базы данных:

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

Если вдруг Вы ошиблись, либо изменились условия задачи, и Вам требуется поменять структуру таблицу, то вовсе не обязательно удалять старую таблицу и вместо нее создавать новую. Для модификации структуры существующей таблицы существует оператор ALTER TABLE, который позволяет изменять все то, что указывается при выполнении оператора CREATE TABLE.

Добавление новых полей в твблицу производится следующим образом:

ALTER TABLE <имя таблицы> ADD ( <имя колонки> <тип колонки> [ NOT NULL] [, ...] )

Например, если для поставщика товаров (таблица 'companies') надо добавить и контактный телефон, то следует выполнить следующий оператор:

ALTER TABLE companies ADD (phone CHAR(35))

Для удаления ненужных полей надо выполнить другой вариант оператора ALTER TABLE:

ALTER TABLE <имя таблицы> DROP ( <имя колонки> [, ...] )

Существует вариант этого оператора и для модификации поля:

ALTER TABLE <имя таблицы> MODIFY ( <имя колонки> <тип колонки> [ NOT NULL] [, ...] )

При изменении типа поля производится автоматическая преобразование значений к новому типу. В частности, если тип меняется с целого на символьное, то число 2385 будет преобразовано в строку '2385'.