Transact sql типы данных. SQL: Типы данных. Тип данных TIMESTAMP
В данной публикации мы рассмотрим типы данных в SQL . Хочу обратить ваше внимание на то, что различные СУБД поддерживает различный набор типов данных, поэтому это публикация включает в себя самые часто встречающиеся типы данных в SQL реализации различных СУБД.
Важно знать, чтобы правильно и грамотно проектировать базы данных, правильно выбранный типа данных в SQL может очень сильно облегчить работу другим разработчикам. Поэтому не советую вам пропускать раздел о типах данных и всегда, когда вы знакомитесь с новой , обращать внимание на типы данных, которые поддерживает программа и на то, как она эти типы данных обрабатывает.
Рассмотрим типы данных, которые есть в языке SQL. У каждого столбца таблицы (у атрибута) должен быть тип данных для значений, которые хранятся в столбце. Тип данных для столбца определяется при создании таблицы, а еще лучше, когда он определяется на этапе .
Язык SQL делит данные на пять типов:
- Целочисленный тип данных SQL.
- Вещественный тип данных 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
Строковых типов данных в 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 используется для хранения информации в бинарном виде (в виде последовательности байт).
Тип данных SQL | Описание |
binary | Максимальная длина 8,000 байт (все значения в столбце имеют фиксированный размер, указанный при объявлении столбца). Обратите внимание: единицы измерения типа данных binary — байты. |
varbinary | Максимальная длина 8,000 байт (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единицы измерения типа данных varbinary — байты. |
varbinary (max) | Максимальная длина 231 байт. Обратите внимание: единицы измерения типа данных varbinary (max) — байты. |
image | Максимальная длина 2,147,483,647 байт (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единицы измерения типа данных image — байты. |
Хочу обратить ваше внимание на то, что каждая СУБД поддерживает свой набор типов данных и размерностей, мы рассмотрели базовые типы данных языка SQL, в следующей теме мы рассмотрим типы данных, которые поддерживает SQLite3.
Знаковые и без знаковые типы данных в SQL
Хочу обратить ваше внимание на то, что в некоторых СУБД числовые типы данных могут делиться на знаковые и без знаковые типы данных. И это совершенно разные типы данных.
Например, если у вас в одной таблице хранится id INTEGER UNSIGNED, а в другой id_table1 INTEGER SIGNED, то по этим двум столбцам вы реализовать не сможете, так как у них разные типы данных. Будьте внимательны и всегда читайте описания типов данных, когда начинаете использовать новую СУБД.
Приведем пример практического применения типов данных SIGNED и UNSIGNED. Например, у MySQL есть тип данных TINYINT, который занимает один байт и прекрасно подходит для хранения возраста, следовательно, в столбец с типом TINYINT можно записывать целые числа от 0 до 256 (два в восьмой степени, в одном байте 8 бит, а один бит может принимать два значения: ноль или единицу), если столбец без знаковый, если столбец знаковый, то в него можно записать числа от -128 до 127 (один бит уйдет на знак).
Последнее обновление: 12.07.2017
При создании таблицы для всех ее столбцов необходимо указать определенный тип данных. Тип данных определяет, какие значения могут храниться в столбце, сколько они будут занимать места в памяти.
Язык T-SQL предоставляет множество различных типов. В зависимости от характера значений все их можно разделить на группы.
Числовые типы данных
BIT : хранит значение 0 или 1. Фактически является аналогом булевого типа в языках программирования. Занимает 1 байт.
TINYINT : хранит числа от 0 до 255. Занимает 1 байт. Хорошо подходит для хранения небольших чисел.
SMALLINT : хранит числа от –32 768 до 32 767. Занимает 2 байта
INT : хранит числа от –2 147 483 648 до 2 147 483 647. Занимает 4 байта. Наиболее используемый тип для хранения чисел.
BIGINT : хранит очень большие числа от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, которые занимают в памяти 8 байт.
DECIMAL : хранит числа c фиксированной точностью. Занимает от 5 до 17 байт в зависимости от количества чисел после запятой.
Данный тип может принимать два параметра precision и scale: DECIMAL(precision, scale) .
Параметр precision представляет максимальное количество цифр, которые может хранить число. Это значение должно находиться в диапазоне от 1 до 38. По умолчанию оно равно 18.
Параметр scale представляет максимальное количество цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0.
NUMERIC : данный тип аналогичен типу DECIMAL.
SMALLMONEY : хранит дробные значения от -214 748.3648 до 214 748.3647. Предназначено для хранения денежных величин. Занимает 4 байта. Эквивалентен типу DECIMAL(10,4) .
MONEY : хранит дробные значения от -922 337 203 685 477.5808 до 922 337 203 685 477.5807. Представляет денежные величины и занимает 8 байт. Эквивалентен типу DECIMAL(19,4) .
FLOAT : хранит числа от –1.79E+308 до 1.79E+308. Занимает от 4 до 8 байт в зависимости от дробной части.
Может иметь форму опредеения в виде FLOAT(n) , где n представляет число бит, которые используются для хранения десятичной части числа (мантиссы). По умолчанию n = 53.
REAL : хранит числа от –340E+38 to 3.40E+38. Занимает 4 байта. Эквивалентен типу FLOAT(24) .
Типы данных, представляющие дату и время
DATE : хранит даты от 0001-01-01 (1 января 0001 года) до 9999-12-31 (31 декабря 9999 года). Занимает 3 байта.
TIME : хранит время в диапазоне от 00:00:00.0000000 до 23:59:59.9999999. Занимает от 3 до 5 байт.
Может иметь форму TIME(n) , где n представляет количество цифр от 0 до 7 в дробной части секунд.
DATETIME : хранит даты и время от 01/01/1753 до 31/12/9999. Занимает 8 байт.
DATETIME2 : хранит даты и время в диапазоне от 01/01/0001 00:00:00.0000000 до 31/12/9999 23:59:59.9999999. Занимает от 6 до 8 байт в зависимости от точности времени.
Может иметь форму DATETIME2(n) , где n представляет количество цифр от 0 до 7 в дробной части секунд.
SMALLDATETIME : хранит даты и время в диапазоне от 01/01/1900 до 06/06/2079, то есть ближайшие даты. Занимает от 4 байта.
DATETIMEOFFSET : хранит даты и время в диапазоне от 0001-01-01 до 9999-12-31. Сохраняет детальную информацию о времени с точностью до 100 наносекунд. Занимает 10 байт.
Распространенные форматы дат:
yyyy-mm-dd - 2017-07-12
dd/mm/yyyy - 12/07/2017
mm-dd-yy - 07-12-17
В таком формате двузначные числа от 00 до 49 воспринимаются как даты в диапазоне 2000-2049. А числа от 50 до 90 как диапазон чисел 1950 - 1999.
Month dd, yyyy - July 12, 2017
Распространенные форматы времени:
hh:mi am/pm - 1:21 pm
hh:mi:ss - 1:21:34
hh:mi:ss:mmm - 1:21:34:12
hh:mi:ss:nnnnnnn - 1:21:34:1234567
Строковые типы данных
CHAR : хранит строку длиной от 1 до 8 000 символов. На каждый символ выделяет по 1 байту. Не подходит для многих языков, так как хранит символы не в кодировке Unicode.
Количество символов, которое может хранить столбец, передается в скобках. Например, для столбца с типом CHAR(10) будет выделено 10 байт. И если мы сохраним в столбце строку менее 10 символов, то она будет дополнена пробелами.
VARCHAR : хранит строку. На каждый символ выделяется 1 байт. Можно указать конкретную длину для столбца - от 1 до 8 000 символов, например, VARCHAR(10) . Если строка должна иметь больше 8000 символов, то задается размер MAX, а на хранение строки может выделяться до 2 Гб: VARCHAR(MAX) .
Не подходит для многих языков, так как хранит символы не в кодировке Unicode.
В отличие от типа CHAR если в столбец с типом VARCHAR(10) будет сохранена строка в 5 символов, то в столце будет сохранено именно пять символов.
NCHAR : хранит строку в кодировке Unicode длиной от 1 до 4 000 символов. На каждый символ выделяется 2 байта. Например, NCHAR(15)
NVARCHAR : хранит строку в кодировке Unicode. На каждый символ выделяется 2 байта.Можно задать конкретный размер от 1 до 4 000 символов: . Если строка должна иметь больше 4000 символов, то задается размер MAX, а на хранение строки может выделяться до 2 Гб.
Еще два типа TEXT и NTEXT являются устаревшими и поэтому их не рекомендуется использовать. Вместо них применяются VARCHAR и NVARCHAR соответственно.
Примеры определения строковых столбцов:
Email VARCHAR(30), Comment NVARCHAR(MAX)
Бинарные типы данных
BINARY : хранит бинарные данные в виде последовательности от 1 до 8 000 байт.
VARBINARY : хранит бинарные данные в виде последовательности от 1 до 8 000 байт, либо до 2^31–1 байт при использовании значения MAX (VARBINARY(MAX)).
Еще один бинарный тип - тип IMAGE является устаревшим, и вместо него рекомендуется применять тип VARBINARY.
Остальные типы данных
UNIQUEIDENTIFIER : уникальный идентификатор GUID (по сути строка с уникальным значением), который занимает 16 байт.
TIMESTAMP : некоторое число, которое хранит номер версии строки в таблице. Занимает 8 байт.
CURSOR : представляет набор строк.
HIERARCHYID : представляет позицию в иерархии.
SQL_VARIANT : может хранить данные любого другого типа данных T-SQL.
XML : хранит документы XML или фрагменты документов XML. Занимает в памяти до 2 Гб.
TABLE : представляет определение таблицы.
GEOGRAPHY : хранит географические данные, такие как широта и долгота.
GEOMETRY : хранит координаты местонахождения на плоскости.
Раздел 3. Основы языка SQL
Лекция 19. Группы операторов. Типы данных
Язык реляционных БД SQL был разработан в середине 70-х годов в рамках исследовательского проекта экспериментальной реляционной СУБД System R от компании IBM . Данный проект включал в себя разработку реляционной СУБД и языка SEQUEL (Structured English Query Language ). Данное название только частично отражало суть языка. Язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционной БД, фактически он уже являлся полноценным языком реляционной БД и содержал помимо операторов формулирования запросов и манипулирования БД, следующие средства:
· определения схемы БД и манипулирования ей;
· определения ограничений целостности и триггеров;
· создания представлений БД;
· определения структур физического уровня, поддерживающих эффективное выполнение запросов;
· автоматизации доступа к таблицам и их полям;
· поддержки точек сохранения транзакции и откатов.
В конце 70-х годов корпорацией Oracle был выпущен модифицированный вариант языка SEQUEL , получивший название SQL . В 1983 г. компания IBM выпустила SQL в составе СУБД DB 2.
Язык SQL был настолько удачен, что несколько позже, в 1986 г. Американский национальный институт стандартизации ( ANSI ) принял его в качестве стандарта. После этого стандарт уже пересматривался несколько раз, в 1989, 1992 г. в результате в язык SQL были внесены некоторые незначительные изменения. В настоящее время наиболее распространенным стандартом является SQL -92.
Типы команд SQL
Команды языка SQL , условно, можно разделить на группы:
· DCL ( Data Control Language ) – язык управления данными. Команды языка предназначены для управления доступом к информации, хранящейся в БД. (таблица 19.1);
· DDL (Data Definition Language) – язык определения данных . Его команды используются для создания и изменения структуры объектов БД (таблица 19.2);
· DML ( Data Manipulation Language ) – язык манипулирования данными. Используется для манипулирования информацией, содержащейся в объектах БД (таблица 19.3);
· DQL ( Data Query Language ) – язык запросов к данным. Наиболее часто используемая группа, состоящая всего из одного оператора SELECT , предназначенного для формирования запросов к БД (таблица 19.4);
· TCL ( Transaction Control Language ) – язык управления транзакциями (таблица 19.5);
· CCL (Cursor Control Language ) – язык управления курсором (таблица 19.6);
Язык SQL является непроцедурным, но, тем не менее, в среде SQL Server предусмотрен ряд различных управляющих конструкций, без которых невозможно написание эффективных алгоритмов, например, операторные скобки, условия циклы и т.д.
Таблица 19.1- Средства управления данными DCL
Оператор | Описание |
ALTER DATABASE | Изменение набора основных объектов БД |
ALTER DBAREA | Изменение существующей области хранения БД |
ALTER PASSWORD | Изменяет пароль для всей базы данных |
CREATE DATABASE | Создает новую базу данных и определяет ее основные параметры |
CREATE DBAREA | Создает область хранения и делает ее доступной для размещения данных |
DROP DATABASE | Удаляет БД (при наличии прав) |
DROP DBAREA | Удаляет область хранения если в ней не располагаются активные данные |
GRANT | Предоставляет права доступа на действия с объектами БД |
REVOKE | Лишает прав доступа к объектам БД или над действиями с объектами БД |
Таблица 19.2 - Операторы определения данных DDL
Оператор | Описание |
CREATE TABLE | Создает новую таблицу в БД |
DROP TABLE | Удаляет существующую таблицу из БД |
ALTER TABLE | Изменяет структуру таблицы или ограничения таблицы |
CREATE VIEW | Создает представление (виртуальную таблицу) соответствующую некоторому SQL запросу |
DROP VIEW | Удаляет ранее созданное представление |
ALTER VIEW | Изменяет существующее представление |
CREATE INDEX | Создает индекс для некоторой таблицы |
DROP INDEX | Удаляет существующий индекс |
Таблица 19.3 - Операторы манипулирования данными DML
Оператор | Описание |
DELETE | Удаляет одну или несколько записей согласно условиям отбора. Применение оператора согласуется с принципами поддержки ссылочной целостности, поэтому оператор не всегда выполняется корректно, даже если синтаксически записан правильно |
INSERT | Вставляет одну или несколько записей, согласно условию отбора, в базовую таблицу |
UPDATE | Обновляет значения одного или нескольких полей в одной или нескольких записях, соответствующих условиям отбора |
Таблица 19.4 - Язык запросов к данным DQL
Оператор | Описание |
SELECT | Оператор, полностью реализующий возможности реляционной алгебры. Позволяет сформировать результирующие отношение, соответствующее запросу |
Таблица 19.5- Средства управления транзакциями TCL
Оператор | Описание |
COMMIT | Завершает транзакцию (комплексную взаимосвязанную обработку информации, объединенную в транзакции) |
ROLLBACK | Откат транзакции (отмена изменений, проведенных в ходе выполнения транзакции) |
SAVEPOINT | Сохраняет промежуточную точку (состояние) БД, для реализации возможности отката |
Таблица 19.6- Средства управления курсором С CL
Оператор | Описание |
DECLARE | Определяет курсор для запроса |
OPEN | Открывает курсор (Формирует виртуальный НД, соответствующий описанию курсора) |
FETCH | Считывает очередную строку из виртуального НД открытого курсора |
CLOSE | Закрывает открытый курсор |
PREPARE | Готовит оператор SQL к динамическому выполнению |
EXECUTE | Выполняет оператор SQL, ранее подготовленный к динамическому выполнению |
Типы данных языка SQL
В языке SQL имеется шесть скалярных типов данных, определенных стандартом. Их краткое описание представлено в таблице 19.7
Таблица 19.7 – типы данных языка SQL
Тип данных | Объявления |
Символьный | CHAR | VARCHAR |
Битовый | BIT | BIT VARYING |
Точные числа | NUMERIC | DECIMAL | INTEGER | SMALLINT |
FLOAT | REAL | DOUBLE PRECISION |
|
Дата/время | DATE | TIME | TIMESTAMP |
Интервал | INTERVAL |
Строковые типы:
§ CHARACTER(n) или CHAR(n) - символьные строки постоянной длины в n символов. При задании данного типа под каждое значение всегда отводится n символов, и если реальное значение занимает менее, чем n символов, то СУБД автоматически дополняет недостающие символы пробелами.
§ VARCHAR(n) - строки символов переменной длины.
Битовые типы:
§ В IT (п) - строка битов постоянной длины.
§ BIT VARYING(n) - строка битов переменной длины.
Точные типы:
§ NUMERIC[(n,m)] - точные числа, здесь и - общее количество цифр в чис- . ле, m - количество цифр слева от десятичной точки.
§ DECIMAL[(n,m)] - точные числа, здесь п - общее количество цифр в числе, m - количество цифр слева от десятичной точки.
§ DEC[(n,m)] - то же, что и DECIMAl.[(n,m)].
§ INTEGER или INT - целые числа.
§ SMALLINT - целые числа меньшего диапазона.
Вещественные типы:
§ FLOAT[(n)] - числа большой точности, хранимые в форме с плавающей точкой. Здесь n - число байтов, резервируемое под хранение одного числа. Диапазон чисел определяется конкретной реализацией.
§ REAL - вещественный тип чисел, который соответствует числам с плавающей точкой, меньшей точности, чем FLOAT.
§ DOUBLE PRECISION специфицирует тип данных с определенной в реализации точностью большей, чем определенная в реализации точность для REAL.
Типы даты/времени и интервал:
§ DATE - календарная дата.
§ TIME – формат времени.
§ Т I МЕ S ТАМР(точность) - дата и время.
§ INTERVAL - временной интервал.
Большинство коммерческих СУБД поддерживают дополнительные типы данных, которые не специфицированы в стандарте. Так, например, практически все СУБД в том или ином виде поддерживают тип данных для представления неструктурированного текста большого объема. Этот тип аналогичен типу MEMO в настольных СУБД. Называются эти типы по-разному, например в ORACLE этот тип называется LONG, в DB2 - LONG VARCHAR, в SYBASE и MS SQL Server - TEXT.
1. Что представляет собой язык SQL ?
2. Что общего между языком SQL и реляционной алгеброй?
3. Какие средства включает в себя язык SQL ?
4. Какие типы команд выделяют в языке SQL ?
5. DML .
6. Назовите основные команды языка DDL .
7. Назовите основные команды языка DCL .
8. Назовите основные команды языка DQL .
9. Назовите основные команды языка управления транзакциями.
10. Назовите основные типы данных языка SQL .
11. Назовите строковые типы данных языка SQL .
12. Назовите числовые типы данных языка SQL .
13. Назовите типы представления даты и времени.