В данной публикации мы рассмотрим типы данных в SQL . Хочу обратить ваше внимание на то, что различные СУБД поддерживает различный набор типов данных, поэтому это публикация включает в себя самые часто встречающиеся типы данных в SQL реализации различных СУБД.
Важно знать, чтобы правильно и грамотно проектировать базы данных, правильно выбранный типа данных в SQL может очень сильно облегчить работу другим разработчикам. Поэтому не советую вам пропускать раздел о типах данных и всегда, когда вы знакомитесь с новой , обращать внимание на типы данных, которые поддерживает программа и на то, как она эти типы данных обрабатывает.
Рассмотрим типы данных, которые есть в языке SQL. У каждого столбца таблицы (у атрибута) должен быть тип данных для значений, которые хранятся в столбце. Тип данных для столбца определяется при создании таблицы, а еще лучше, когда он определяется на этапе .
Язык SQL делит данные на пять типов:
Давайте посмотрим, что включает в себя каждый из типов данных SQL и какие значения они позволяют нам хранить.
Целочисленный тип данных в SQL довольно таки широкий и зависит от реализации СУБД, снизу в таблицы лишь некоторые типичные примеры целочисленного типа данных SQL.
| Тип данных SQL | от | до |
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| int | -2,147,483,648 | 2,147,483,647 |
| smallint | -32,768 | 32,767 |
| tinyint | 0 | 255 |
| bit | 0 | 1 |
| decimal | -10^38 +1 | 10^38 -1 |
| numeric | -10^38 +1 | 10^38 -1 |
| money | -922,337,203,685,477.5808 | +922,337,203,685,477.5807 |
| smallmoney | -214,748.3648 | +214,748.3647 |
Еще раз повторюсь, что набор целочисленных типов данных в SQL зависит целиком и полностью от СУБД.
Вещественный тип данных SQL , как и целочисленный тип данных, целиком и полностью зависит от реализации СУБД.
Строковых типов данных в SQL может быть гораздо больше, смотрите спецификацию своей СУБД, чтобы узнать какой тип данных может быть использован для строковых значений.
| Тип данных SQL | Описание |
| char | Максимальная длина 8000 символов (все значения в столбце имеют фиксированный размер, указанный при объявлении столбца). Обратите внимание: единица измерения SQL типа данных char - символ. |
| varchar | Максимальная длина 8000 символов (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единица измерения SQL типа данных varchar - символ. |
| varchar (max) | SQL типа данных varchar (max) - символ. |
| text | Максимальная длина 2,147,483,647 символов. Обратите внимание: единица измерения SQL типа данных text - символ. |
Строковый тип данных SQL в кодировке юникод
Да, некоторые СУБД выделяют строковый тип данных в кодировке юникод, поскольку в строках с кодировкой юникод на запись одного символа тратится, как минимум, 8 байт, поэтому мы и выделяем строковый тип данных в SQL с кодировкой юникод .
| Тип данных SQL | Описание |
| nchar | Максимальная длина 4000 символов (все значения в столбце имеют фиксированный размер, указанный при объявлении столбца). Обратите внимание: единица измерения SQL типа данных nchar - символ. |
| nvarchar | Максимальная длина 4000 символов (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единица измерения SQL типа данных nvarchar - символ. |
| nvarchar (max) | Максимальная длина 231 символ. Обратите внимание: единица измерения SQL типа данных nvarchar (max) — символ. |
| ntext | Максимальная длина 1,073,741,823 символов. Переменная длина. Обратите внимание: единица измерения SQL типа данных ntext — символ. |
Бинарный тип данных в SQL используется для хранения информации в бинарном виде (в виде последовательности байт).
| Тип данных SQL | Описание |
| binary | Максимальная длина 8,000 байт (все значения в столбце имеют фиксированный размер, указанный при объявлении столбца). Обратите внимание: единицы измерения типа данных binary — байты. |
| varbinary | Максимальная длина 8,000 байт (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единицы измерения типа данных varbinary — байты. |
| varbinary (max) | Максимальная длина 231 байт. Обратите внимание: единицы измерения типа данных varbinary (max) — байты. |
| image | Максимальная длина 2,147,483,647 байт (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единицы измерения типа данных image — байты. |
Хочу обратить ваше внимание на то, что каждая СУБД поддерживает свой набор типов данных и размерностей, мы рассмотрели базовые типы данных языка SQL, в следующей теме мы рассмотрим типы данных, которые поддерживает SQLite3.
Хочу обратить ваше внимание на то, что в некоторых СУБД числовые типы данных могут делиться на знаковые и без знаковые типы данных. И это совершенно разные типы данных.
Например, если у вас в одной таблице хранится id INTEGER UNSIGNED, а в другой id_table1 INTEGER SIGNED, то по этим двум столбцам вы реализовать не сможете, так как у них разные типы данных. Будьте внимательны и всегда читайте описания типов данных, когда начинаете использовать новую СУБД.
Приведем пример практического применения типов данных SIGNED и UNSIGNED. Например, у MySQL есть тип данных TINYINT, который занимает один байт и прекрасно подходит для хранения возраста, следовательно, в столбец с типом TINYINT можно записывать целые числа от 0 до 256 (два в восьмой степени, в одном байте 8 бит, а один бит может принимать два значения: ноль или единицу), если столбец без знаковый, если столбец знаковый, то в него можно записать числа от -128 до 127 (один бит уйдет на знак).
Для каждого столбца необходимо определить в обязательном порядке кроме уникального в пределах таблицы имени столбца также тип данных. Присвоение типа данных каждому столбцу является одним из первых шагов, предпринимаемых при проектировании таблицы. Типы данных определяют допустимые значения данных для каждого столбца.
Являются символьными типами данных фиксированной или переменной длины.
char [ (n) ] - символьные данные фиксированной длины, не в Юникоде, с длиной n байт. Значение n должно находиться в интервале от 1 до 8000. Размер хранения данных этого типа равен n байт. Синонимом по стандарту ISO для типа char является character.
varchar [ (n | max) ] - символьные данные переменной длины, не в Юникоде; n может иметь значение от 1 до 8 000; max означает, что максимальный размер хранения равен 2^31-1 байт. Размер хранения равен фактической длине данных плюс два байта. Введенные данные могут иметь длину 0 символов. Синонимами по стандарту ISO для типа varchar являются типы char varying или character varying.
Данные char или varchar могут быть отдельным знаком или строкой максимальной длиной в 8000 символов для данных char и до 2^31 символов для данных varchar.
Типы данных varchar могут принимать две формы. Данные varchar могут быть указанной максимальной длины в знаках, например varchar(6) свидетельствует о том, что в этом типе данных может храниться максимум шесть символов или он может иметь вид varchar(max), увеличивающий максимальное число символов, которые могут храниться в этом типе данных, до 2^31.
Символьные константы должны быть заключены в одинарные (") или двойные кавычки ("). Рекомендуется заключение символьных констант в одинарные кавычки. Заключение символьных констант в двойные кавычки иногда не допускается, если параметру QUOTED IDENTIFIER присвоено значение ON.
Данный пример кода Transact-SQL присваивает символьной переменной значение:
DECLARE @MyCharVar CHAR(25)
SET @MyCharVar = "Ricardo Adocicados"
Если для выделения символьной константы, содержащей вложенную одинарную кавычку, применяются одинарные кавычки, используйте две одинарные кавычки для представления вложенной одинарной кавычки.
Например:
SET @MyCharVar = "O""Leary"
Если данные, подлежащие хранению, длиннее, чем допустимое число символов, они усекаются. Например, если столбец определен как char(10) и в нем сохраняется значение "Это действительно очень длинная символьная строка", то SQL Server усекает символьную строку до "Это действ".
Тип данных char становится типом данных с фиксированной длиной, если указано предложение NOT NULL. Если в столбец char с предложением NOT NULL вставляется значение короче, чем длина столбца, это значение заполняется вправо при помощи пробелов до размера столбца. Например, если столбец определен как char(10) и в нем должно быть сохранено значение "музыка", SQL Server сохраняет данные в виде "музыка____", где "_" обозначает пробел.
Это - символьные типы данных, имеющие постоянную длину (nchar), или переменную длину (nvarchar), содержащие данные в Юникоде и использующие набор символов UCS-2.
nchar [ (n) ] - символьные данные в Юникоде длиной в n символов. Аргумент n должен иметь значение от 1 до 4000. Размер хранилища вдвое больше n байт. Синонимами по стандарту ISO для типа nchar являются типы national char и national character.
nvarchar [ (n | max) ] - символьные данные в Юникоде переменной длины. Аргумент n может принимать значение от 1 до 4 000. Аргумент max указывает, что максимальный размер хранилища равен 2^31-1 (2 147 483 647) байт. Размер хранилища в байтах вдвое больше числа введенных символов + 2 байта. Введенные данные могут иметь длину в 0 символов. Синонимами по стандарту ISO для типа nvarchar являются типы national char varying и national character varying.
Типы данных SQL
разделяются на три группы:
- строковые
;
- с плавающей точкой
(дробные числа
);
- целые числа
, дата
и время
.
Типы данных SQL строковые
| Типы данных SQL | Описание |
| CHAR(size) | Строки фиксированной длиной (могут содержать буквы, цифры и специальные символы). Фиксированный размер указан в скобках. Можно записать до 255 символов |
| VARCHAR(size) | |
| TINYTEXT | Может хранить не более 255 символов. |
| TEXT | |
| BLOB | Может хранить не более 65 535 символов. |
| MEDIUMTEXT | |
| MEDIUMBLOB | Может хранить не более 16 777 215 символов. |
| LONGTEXT | |
| LONGBLOB | Может хранить не более 4 294 967 295 символов. |
| ENUM(x,y,z,etc.) | Позволяет вводить список допустимых значений. Можно ввести до 65535 значений вSQL Тип данных ENUM список. Если при вставке значения не будет присутствовать в списке ENUM , то мы получим пустое значение. Ввести возможные значения можно в таком формате: ENUM ("X", "Y", "Z") |
| SET | SQL Тип данных SET напоминает ENUM за исключением того, что SET может содержать до 64 значений. |
Типы данных SQL с плавающей точкой (дробные числа) и целые числа
Типы данных SQL - Дата и время
Создание таблицы в базе данных производится командой CREATE TABLE .
Синтаксис:
CREATE TABLE tbl_name [(create_definition,...)]
tbl_name - Задает имя таблицы, которая будет создана в текущей базе данных. Если никакая база данных на момен вызова команды CREATE TABLE не была принята текущей, то возникнет ошибка выполнения команды.
IF NOT EXISTS - Если указан этот параметр и производится попытка создать таблицу с дублирующим именем (т.е. таблица с таким именем в текущей БД уже есть), то таблица создана не будет и сообщение об ошибке не появиться. В противном случае таблица также создана не будет, но команда вызовет ошибку.
create_definition - Определяет внутреннюю структуру создаваемой таблицы (названия и типы полей, ключи, индексы и т.д.)
Возможные синтаксисы create_definition :
col_name type
PRIMARY KEY (index_col_name,...)
KEY (index_col_name,...)
INDEX (index_col_name,...)
UNIQUE (index_col_name,...)
FOREIGN KEY (index_col_name,...)
col_name - Задает имя столбца в создаваемой таблице.
Type - Задает тип данных для столбца col_name .
Возможные значения параметра type :
§ INT[(length)]
§ BIGINT[(length)]
§ DOUBLE[(length,decimals)]
§ FLOAT[(length,decimals)]
§ DECIMAL(length,decimals)
§ CHAR(length)
§ VARCHAR(length)
- Указывавет, может ли данных столбец содержать значение NULL или нет. Если не указано, то по умолчанию принимается NULL (т.е. может содержать NULL).
- Задает значение по умолчанию для данного столбца. При вставке новой записи в таблицу командой INSERT если значение для поля col_name явно указано не было, то устанавливается значение default_value .
- При вставке новой записи в таблицу поле с этим атрибутом автоматически получит числовое значение, на 1 больше самого большого значения для этого поля в текущий момент времени. Данная возможность обычно используется для генерирования уникальных идентификаторов строк. Столбец, для которого применяется атрибут AUTO_INCREMENT , должен иметь целочисленный тип. В таблице может быть только один столбец с атрибутом AUTO_INCREMENT . Так же этот столбец должен быть проиндексирован. Отсчет последовательности чисел для AUTO_INCREMENT начинается с 1. Это могут быть только положительные числа.
Следующий пример создает таблицу users с 3 полями, где первое поле - уникальный идентификатор записи, второе поле - имя пользователя, а третье поле - его возраст:
create table Water_Area(
id int auto_increment,
name varchar(70),
constraint pk_Water_Area primary key nonclustered(id))
create table Station(
id_Water_Area int not null,
name varchar(20) not null,
Latitude float(4) null,
Longitude float(4) null,
coordinates varchar(40) null,
coordinates_modern varchar(30) null,
comment varchar(70) null
constraint pk_Station primary key nonclustered(id))
Удаление таблицы.
Для удаления таблицы служит команда DROP TABLE.
DROP TABLE tbl_name [, tbl_name,...]
tbl_name - Имя удаляемой таблицы.
IF EXISTS – Если указан этот параметр, то при попытке удаления несущестующей таблицы ошибки не возникнет. В противном случае возникнет ошибка выполнения команды.
RESTRICT и CASCADE Не несут никакой функциональности. Оставлены для упрощения переноса программы.
Drop table Station
Модификация таблиц.
Для модификации служит команда Alter Table.
Синтаксис:
ALTER TABLE TableName1ADD | ALTER FieldName1FieldType [(nFieldWidth [, nPrecision])] ]] | FieldName]
Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:
Добавляет столбец
Добавляет ограничение целостности
Переопределяет столбец (тип данных, размер, умалчиваемое значение)
Удаляет столбец
Модифицирует характеристики памяти или иные параметры
Включает, выключает или удаляет ограничение целостности или триггер.
alter table Station
unique(id_Water_Area, name)
alter table Station
add constraint fk_Station_Relation_Water_Area foreign key (id_Water_Area) references Water_Area (id)
Типы данных SQL.
|
Описание | ||
|
Строковые типы |
Строка символов постоянной длины |
CHAR (количество_символов) |
|
Строка символов переменной длины |
VARCHAR (количество_символов) |
|
|
Большая строка символов переменной длины | ||
|
Большой двоичный объект переменной | ||
|
Числовые точные типы |
Целое число | |
|
Целое число. Количество разрядов больше или равно INTEGER | ||
|
Целое число. Количество разрядов меньше или равно INTEGER | ||
|
Число с фиксированной запятой (количество_разрядов – общее число знаков, масштаб - знаков после запятой) |
{NUMERIC | DECIMAL | DEC} (кол-во разрядов, масштаб) |
|
|
Числовые неточные типы |
Число с плавающей запятой | |
|
Число с плавающей запятой. Количество разрядов больше или равно REAL |
DOUBLE PRECISION |
|
|
Число с плавающей запятой |
FLOAT (кол-во разрядов) |
|
|
Временные типы |
Дата. Содержит год, месяц и день | |
|
Временная отметка. Содержит год, месяц, день, час, минуты и секунды |
TIMESTAMP (масштаб) |
|
|
Время. Содержит час, минуты и секунды (масштаб - количество знаков в дробной части секунд). Возможно указание временной зоны |
TIME (масштаб) |
|
|
Временной промежуток. | ||
|
Логический тип |
Принимает логические значения |
Литералы – это простейшие выражения, являющиеся значениями соответствующих типов. Каждый тип данных имеет способ представления литералов своего типа. В SQL имеются следующие литералы:
символьных строк (пример: "Символьная строка"),
двоичных строк (пример: X"1AFFD561"),
чисел (примеры: 27 -863 0.173 .8582 -9572.5619 11.54Е7 .94Е194 93Е-12 -29.629Е27),
временные (DATE "2005-12-07" TIME "HH:MI:SS" TIMESTAMP "YYYY-MM-DD HH:MI:SS"),
логические (TRUE, FALSE, UNKNOWN).
Операторы - это конструкции языка, указывающие операции над элементами данных и возвращающие в качестве результата новое значение. Элементы данных, используемые в операторах, называются операндами или аргументами. Операторы представляются в виде специальных символов или ключевых слов. Например, оператор умножения представляется звездочкой (*), а оператор проверки на неопределенное значение - ключевым словом IS NULL.
Есть два основных вида операторов. Унарные имеют только один операнд и обычно представляются в следующем формате:
операнд оператор
Бинарные оперируют двумя операндами и представляются таким образом:
операнд оператор операнд
Кроме того, существуют специальные операторы, использующие более двух операндов. Тип оператора определяется типом используемых аргументов и типом возвращаемого результата. Имеются операторы следующих типов:
строковые (||);
арифметические;
логические;
предикаты сравнения;
специальные предикаты;
операторы над множествами (таблицами).
В современном мире насчитывается большое количество средств и технологий, предназначенных для хранения информации. Одним из наиболее распространенных способов являются базы данных, для работы с которыми используются различные системы управления. Данный способ хранения предполагает, что все данные четко структурированы и занесены в специальные таблицы. Они, в свою очередь, состоят из столбцов-атрибутов определенного типа данных.
Сегодня существует несколько определений, объясняющих понятие термина «тип данных». Однако каждое из них имеет один общий смысл. Поэтому можно условно обозначить как группу данных, характеризуемую ее значениями (символьными, числовыми и т.д.), а также операциями, применяемыми к рассматриваемым значениям.
Сфера применения типов данных многогранна. Они используются не только для хранения информации, но также и в программировании для решения различных задач. При проектировании программ широко распространена практика разработки и использования собственных типов данных с определенным набором операций. Однако в основе пользовательских всегда лежат базовые типы данных. SQL-стандарт также основывается на использовании наиболее распространенных базовых типов, но с рядом определенных дополнений.
Группировка данных по их типу возникла давно и была вызвана необходимостью для более удобной обработки. В настоящее время основу существующих типов данных образуют два: символьный и числовой.
На их базе была разработана современная классификация, включающая в себя указатели, логический, целочисленный, числовой с плавающей точкой и строковый типы данных. SQL- классификация полностью охватывает все вышеперечисленное. Однако для некоторых современных СУБД существуют дополнительные надстройки. К ним относятся Oracle и MySQL.
Использующиеся при создании атрибутов таблиц, отвечающих стандартам языка подразделяются на 4 класса:
Первая группа значений позволяет хранить любые данные, представленные в виде символов.
Это могут быть специальные символы, цифры и буквы, которые в своей совокупности будут обрабатываться как строки в любом Типы данных, таблица с перечислением которых представлена ниже, образуют первую группу.
Используется для хранения строк. Параметр в скобках позволяет фиксировать длину хранимой строки. Максимальный размер в байтах, который можно задать для строки, - 255. |
|
Аналогично предыдущему типу позволяет хранить строки длиной до 255 символов. Однако отличие от CHAR в том, что для хранения значения данного типа выделяется требуемое количество памяти. То есть для строки, состоящей из 5-ти символов, потребуется 6 байт памяти. В первом же случае память для значения будет выделена согласно указанному параметру. |
|
Используется для хранения строковых данных размером до 255 символов. |
|
Используется для хранения текстовой информации, размер которой не превышает 65 535 букв. |
|
Рассматриваемый тип данных аналогичен типу ТЕХТ и позволяет сохранять в базе текстовую информацию, объем которой может достигать 65 535 знаков. Но на практике используется для хранения звуковых данных, рисунков, электронной документации и пр. |
|
Был разработан на базе типа ТЕХТ, но позволяет хранить больше данных за счет увеличенного размера до 16 777 215 букв или символов. |
|
Используется для сохранения в базе электронных документов, размер которых не превышает отметку в 16 777 215 знаков. |
|
Функционально аналогичен предыдущим типам, но с увеличенным объемом памяти до 4 гигабайт. |
|
Позволяет помещать в базу данные больших объемов (4 294 967 295 символа). |
|
ENUM (a, b, c, etc.) | Специальный тип данных, использующийся для задания списка возможных значений. Позволяет указать 65535 значений. Строки рассматриваемого типа могут принимать единственное значение из указанных в множестве. В случае, когда будет происходить добавление значений, которые не присутствуют в заданном списке, в таблицу будут записаны пустые значения. |
Задает множество допустимых значений. В отличие от предыдущего типа, используется для содержания 64 параметров, которые могут быть проинициализированы любым или несколькими элементами из заданных аргументов. |
Дробные SQL типы данных используются для хранения чисел с плавающей точкой. На практике, как правило, задают различные финансовые показатели. В зависимости от необходимой точности применяют один из трех представленных:
Например, в банковских расчетах точность дробной части достигает значение в 8-мь или 10-ть знаков. Первые два типа не могут быть задействованы в данной области.
Хранение финансовых показателей в виде строк значительно облегчает решение многих задач. Однако при решении финансовых вопросов или проведении различных операций SQL преобразование типов данных имеет огромное значение. Разработчики должны обязательно учитывать тип хранения и способы обработки, чтобы данные всегда оставались неизменными.
Целые числа - отдельная группа чисел, образующая один из основных классов. Целочисленные SQL типы данных основываются на использовании базового с некоторым расширением его свойств.
Выбрав правильный тип данных, можно значительно сэкономить память и уменьшить затраты серверного времени, когда выполняются необходимые SQL-запросы. Типы данных, а точнее их диапазон, определяют количество требуемого места для хранения.
Поэтому разработчикам важно помнить, что использование больших диапазонов для атрибутов влечет увеличение затрат на память. Необходимо четко анализировать решаемые задачи и выявлять случаи, где известен примерный диапазон и определено условие использования чисел со знаком. Если диапазон используемых аргументов невелик, а все числа будут положительными, то будет корректнее использовать беззнаковый тип, образуемый атрибутом UNSIGNED.
При изучении основ SQL типы данных даты и времени представляют особый интерес.
Использование нижеуказанных типов предоставляет дополнительные преимущества при разработке систем, работа которых зависит от временных показателей.
Главное предназначение - хранение даты в формате ГОД-МЕСЯЦ-ДЕНЬ (“ГГГГ-MM-ДД” или "уууу-mm-dd"). Обычно значения разделены через «-», однако в качестве разделителя может быть задействован любой символ, кроме цифр. |
|
Позволяет заносить в ячейку таблицы временные значения. Все значения задаются форматом «hh:mm:ss» |
|
Объединяет функции предыдущих двух типов. Формат хранения представлен следующим образом: «уууу-mm-dd hh:mm:ss». |
|
Сохраняет дату и время, исчисляемое количеством секунд, прошедших начиная с полуночи 1.01.1970 года и до заданного значения. |
|
Используется для хранения годовых значений в двух- или четырехзначном формате. |
Все эти типы данных детально систематизированы компанией Microsoft. SQL типы данных ею разработаны более подробно.
Например, фирмой подробно расписан, какой объем памяти в байтах выделяется при использовании каждого типа. Изучив имеющуюся информацию, разработчикам проще проектировать структуру таблиц и всей базы исходя из аппаратных возможностей сервера.
Иногда при заполнении базы данных возникает ситуация, когда при добавлении записи в таблицу необходимость вносить информацию во все столбцы отсутствует. Для этого применяется специальный указатель пустого значения - NULL , который в качестве вспомогательного средства использует язык SQL. Типы данных столбцов, которые не обязательно должны быть заполнены, при создании таблиц указываются с оператором, разрешающим включение пустых значений. В другом случае оператор NULL с дополнительной приставкой NOT может быть задействован для указания обязательного заполнения всех значений.
Указатель NULL не имеет типа, а просто указывает на пустое значение в таблицах баз данных. Поэтому он может быть скомбинированным с любым из вышеперечисленных типов.
