|
|
Конспект лекций_Администрирование БД. Теоретические основы баз данных
Типы данных, управляющие конструкции языка и функции Transact-SQL.
Типы данных и преобразование типов.
Встроенные и определённые пользователем типы данных хранятся в текущей базе данных в таблице systypes и могут быть просмотрены командой
SELECT * FROM systypes
Бинарные: требуется указывать числа в 16-ричном виде, например 0хFF.
binary(n) – n определяет количество байт для хранения (до 8000 байт); под каждое значение будет выделено указанное количество байт вне зависимости от фактических размеров данных.
varbinary(n) – n определяет максимальное количество байт для хранения (до 8000 байт); если массив данных mn, то данные усекаются до размера поля.
image – хранятся бинарные данные размером до 231-1 байт (
2 Гбайт), память выделяется страницами по 8000 байт. Использование этого поля оправдано, если требуется хранить очень длинные данные. Специальных функции отображения содержимого полей этого типа SQL Server не имеет.
Пример:
DECLARE @VA binary(10), @VB varbinary(10)
SELECT @VA = 0xFF, @VB= 0xAC
SELECT 'Значение переменной VA: ' = @VA, 'Значение переменной VB: ' = @VB
SELECT 'Длина VA: ' = DATALENGTH(@VA), 'Длина VB: ' = DATALENGTH(@VB)
Символьные: интерпретируются в зависимости от того, какая кодовая страница была установлена при инсталляции сервера.
char(n) – n<=8000, ASCII символы (1 байт), резервируется всегда n символов, если размер данных меньше, строка дополняется пробелами справа.
varchar(n) – то же что и char(n), но резервируется максимум n символов. Если размер данных mnchar(n) – n<=4000, Unicode символы (2 байта), остальное как в char(n).
nvarchar(n) – n<=4000, Unicode символы (2 байта), остальное как в varchar(n).
Примеры:
DECLARE @name nvarchar(25)
SET @name = N'Д''Артаньян'
SELECT @name
-- Что получится в каждом из столбцов?
SELECT 1+1,'1'+'1','1'+'2'+3
Типичные функции для работы со строками:
LEN(…) – возвращает длину строки в символах;
LTRIM(…) и RTRIM(…) – удаляет пробелы в начале и в конце строки
SUBSTRING(…) – возвращает подстроку из строки
REPLACE(…) – заменяет подстроку указанным значением
Текстовые:
позволяют хранить достаточно большие объёмы текстовой информации
text – хранение ASCII текстовых блоков размером <=231-1 символов (2 Гбайт), выделяемых постранично.
ntext – хранение Unicode текстовых блоков размером <=230-1 символов (1 Гбайт), выделяемых постранично.
Некоторые функции для работы с текстовыми полями:
SUBSTRING(…) – возвращает подстроку текстового поля;
READTEXT(…) – считывает данные из текстового поля;
DATALENGTH(…) – возвращает количество байт, занимаемых данными;
Целочисленные: следует учитывать, что результат вычисления выражения приводится к типу данных, имеющих максимальный размер из всех участвующих в выражении. Результатом деления целого на целое будет целое с отброшенной дробной частью.
tinyint – диапазон данных от 0 до 255 (длина поля 1 байт).
smallint – диапазон данных от -215 до 215-1 (длина поля 2 байта).
int или integer – диапазон данных от -231 до 231-1 (длина поля 4 байта).
bigint – диапазон данных от -263 до 263-1 (длина поля 8 байт).
Нецелочисленные: с фиксированной и плавающей точкой.
dec или decimal [(p[,s])] или numeric [(p[,s])] – диапазон от –(1038-1) до (1038-1). Содержит фиксированное количество знаков до и после точки: p – общее число знаков, s – число знаков после точки (0 <= s <= p <= 38). Длина поля от 5 до 17 байт.
float [(n)] – число в виде мантиссы и порядка. Максимальный диапазон от -1.79308 до 1.79308. Значение n определяет количество бит, используемых для хранения мантиссы (n<=53). Длина поля 4 или 8 байт.
double precision – частный случай float(53).
real – частный случай float(24). Диапазон данных от -3.438 до 3.438.
Примеры:
DECLARE @VR real
SET @VR=1.23E4
SELECT @VR, DATALENGTH(@VR)
Не рекомендуется указывать нецелочисленные типы данных в конструкциях WHERE и для построения индексов или первичных ключей, так как скорость обработки таких полей существенно ниже чем целочисленных
Преобразование типов данных из числа в строку:
DECLARE @VR float, @VS varchar(20)
SET @VR=3.14
SET @VS=STR(@VR,4,2)+'15' – простая функция преобразования типов
SELECT @VS
SET @VS=CAST($123.45 AS varchar(10)) – универсальная функция преобразования
SELECT @VS
SELECT CAST ('$54321' AS money)
Взаимозаменяемой для функции CAST является функция CONVERT
Некоторые функции для работы с числовыми типами:
ISNUMERIC(…) – проверяет, имеет ли выражение числовой тип данных (1, если да);
RAND() – вычисляет случайное число с плавающей точкой в диапазоне [0…1];
POWER(…) – возведение числа в степень (SELECT power(2,8) );
PI(…) – возвращает значение Пи.
Даты и времени: типы данных позволяют одновременно хранить время и дату.
datetime – В первых 4 байтах хранится смещение относительно 1 января 1753 г. (до этого летоисчисление шло по Григорианскому и Юлианскому календарям) до 31.12.9999. Последние 4 байта – время после полуночи с точностью 3.33 мс.
smalldatetime – диапазон от 1.01.1900 до 6.06.2079 с точностью до 1 мин.
Дополнительные возможности для ввода и вывода дат предоставляет команда
SET DATEFORMAT xxx, где xxx может быть: mdy, dmy, ymd, ydm, myd, dym.
Примеры:
SET LANGUAGE 'русский'
DECLARE @DV datetime
SET @DV='21 октябрь 2003 23:19'
SELECT @DV
Функция ISDATE(<выражение>) возвращает 1, если <выражение> может быть правильно конвертировано в дату, и 0 в противном случае.
Некоторые функции для работы с датой и временем:
GETDATE() – возвращает текущее системное время;
YEAR(…) – возвращает год из указанной даты;
DATEADD(…) – добавляет к дате указанный временной интервал
SELECT year(getdate())
DECLARE @Str1 char(10)
IF DAY(GETDATE())<15 SET @Str1='первая'
ELSE SET @Str1='вторая'
SELECT 'Сейчас '+RTRIM(@Str1)+' половина месяца'
Денежные: поддерживается точность 4 знака после десятичной точки.
money – диапазон от -922 337 203 685 477.5808 до +922 337 203 685 477.5807, длина 8 байт.
smallmoney – диапазон от -214 748.3648 до +214 748.3647, длина 4 байта.
Примеры:
CREATE TABLE MyMoney (
ID bigint IDENTITY (1,1) PRIMARY KEY,
Value money NULL
)
INSERT MyMoney VALUES ($127.35)
SELECT * FROM MyMoney
Специальные:
bit – данные принимают значения 0 / 1 / NULL. Память выделяется побайтно.
timestamp – счетчик-идентификатор записей, уникальный в пределах одной базы данных (не имеет ничего общего с меткой времени). Длина 8 байт. Не может использоваться для объявления переменных. Не должен использоваться в составе первичного ключа.
uniqueidentifier – глобально уникальный идентификатор записи (GUID). Идентификатор уникален в масштабе планеты. Представляет собой 16-байтовую последовательность, составляющуюся с помощью функции NEWID() из MAC-адреса сетевой карты и внутреннего таймера процессора.
sysname – предназначен для хранения имён объектов баз данных SQL Server – столбцов, таблиц, индексов, представлений, хранимых процедур и др.
sql_variant – позволяет в одном и том же столбце хранить значения любого другого доступного типа данных (за исключением text, ntext, image, timestamp, cursor, table и самого sql_variant)
Пример:
DECLARE @Var1 int, @Var2 nvarchar(15), @Var3 datetime, @VA sql_variant
SET @Var1=10
SET @Var2='Просто строка'
SET @Var3='23.08.1969'
SET @VA=@Var1+5
SELECT @VA
SET @VA=@Var2
SELECT @VA
SET @VA=@Var3
SELECT @VA
Функция SQL_VARIANT_PROPERTY возвращает информацию о природе данных, хранящихся под типом sql_variant.
cursor – ссылка на объект базы данных – курсор. Подробнее о курсорах см. далее.
table – временная таблица (массив). Может использоваться только для переменных и значений, возвращаемых функциями пользователя.
Пример:
DECLARE @VarTable TABLE (
Col1 int NOT NULL IDENTITY (1,1) PRIMARY KEY,
Col2 nvarchar(15)
)
INSERT INTO @VarTable (Col2) VALUES ('Первая строка')
SELECT * FROM @VarTable
Пользовательские типы данных: также возможно создавать в MS SQL Server. Для этих целей предусмотрена специальная хранимая процедура sp_addtype. Первым параметром при вызове этой процедуры указывается имя пользовательского типа, вторым – имя системного типа, на основе которого строится пользовательский. Третий параметр указывает, разрешены или запрещены значения NULL. Пример:
sp_addtype nvc15, ’nvarchar(15)’, NONULL
Управляющие конструкции Transact-SQL
Их весьма мало. К ним относятся:
Блок BEGIN … END. Выполняет группировку двух и более TSQL команд. Используется в конструкциях ветвлений, условий и циклов. Допускаются вложенные друг в друга блоки.
Конструкция IF … ELSE. Переход по условию. Допускается в каждой ветви исполнять одну команду или использовать блок BEGIN…END. Пример:
PRINT GetDate()
IF Day(GetDate())=1 AND Month(GetDate())=1
BEGIN
PRINT 'Сегодня Новый Год!'
IF CURRENT_USER='dbo'
PRINT 'Администратор СУБД - это судьба...'
END
ELSE PRINT 'Сегодня точно не Новый Год.'
Конструкция CASE … END. Реализует множественное ветвление. Особенностью конструкции является то, что её можно использовать непосредственно в выражениях, например, при выполнении запроса. Пример:
SELECT au_lname, au_fname,
CASE state
WHEN ‘CA’ THEN ‘Калифорния’
WHEN ‘UT’ THEN ‘Юта’
ELSE state
END
FROM authors
WHERE au_lname LIKE ’O%’
Конструкция COALESCE возвращает первое значение, не равное NULL, из перечня аргументов функции. Пример:
DECLARE @I1 int, @C1 char, @C2 char, @C3 char
SET @I1 = 10
SET @C1 = 'A'
SET @C2 = @C1 + @C3
PRINT COALESCE(@C2,@I1)
Так как конкатенацией любой строки со значением NULL является NULL, то результатом выполнения этой последовательности команд будет значение 10.
Конструкция WHILE… . Организует циклы в TSQL. Это единственный способ организовать циклы в этом языке. Цикл можно принудительно остановить, если в его теле выполнить команду BREAK, и перезапустить из любого места внутри тела цикла с помощью команды CONTINUE.
DECLARE @I1 int
SET @I1 = 1
WHILE @I1 < 8
BEGIN
PRINT 'Квадрат числа '+Str(@I1)+' есть '+Str(Square(@I1))
SET @I1 = @I1 + 1
END
Дополнительные предикаты.
К уже отмеченным ранее предикатам условных конструкций в TSQL добавляются операторы ALL и ANY. C помощью ALL выполняется сравнение скалярного выражения со всеми значениями, возвращаемыми подзапросом. Если логическое условие выполняется для всех возвращаемых подзапросом значений, тогда результат выполнения условия TRUE.
IF 1 = ALL (SELECT contract FROM authors) PRINT ‘Все авторы подписали контракт’
В случае ANY если хотя бы в одной строке содержится значение, для которого выполняется указанное условие, то результат выполнения условия TRUE.
IF 0 = ANY (SELECT contract FROM authors) PRINT ‘Не все авторы подписали контракт’
Комментарии.
Для переменных TSQL не определено понятие «массив», следовательно все операции, характерные для него, следует выполнять во временных или постоянных таблицах.
|
|
|
Скачать 0.98 Mb.