Создание и сопровождение БД средствами ms sql server 2000

Abid – идентификатор БД

SI – идентификатор безопасности владельца БД

Crdate – дата создания

Cmptlevel – уровень совместимости

Database Properties

 

NULL – допускает пустое значение для поля

CREATE TABLE Employees

Emp_ID char(4) NOT NULL

FirstName varchar(20) NULL

LastName varchar(20) NOT NULL DEFAULT ‘unknown’

Невозможно создать DEFAULT:
с типом данных: timestamp
со свойством IDENTIFY и ROWGUIDCOL
с существующем определением или объявленном по умолчанию

Идентификатор

Emp_ID SMALLINT IDENTITY(101,1) NOT NULL

                               101 – начало, 1 – шаг значения

Типы данных	Допустимые значения
Bigint	Целые числа от -2^63 до 2^63
Int	Целые числа от -2^31 до 2^31
Smallint	Целые числа от -2^15 до 2^15
Tinyint	Целые числа от 0 до 255
Bit	Целые числа, принимающие значения 0 или 1
Decimal	Десятичные числа с фиксированным количеством до и после запятой в диапазоне от –10^38+1 до 10^38+1
Money	Числа в денежных форматах в диапазоне -2^63 до 2^63

 

1. Типы данных SQL строковые

Типы данных SQL	Описание
CHAR(size)	Строки фиксированной длиной (могут содержать буквы, цифры и специальные символы). Фиксированный размер указан в скобках. Можно записать до 255 символов
VARCHAR(size)	Может хранить не более 255 символов.
TINYTEXT	Может хранить не более 255 символов.
TEXT	Может хранить не более 65 535 символов.
BLOB	Может хранить не более 65 535 символов.
MEDIUMTEXT	Может хранить не более 16 777 215 символов.
MEDIUMBLOB	Может хранить не более 16 777 215 символов.
LONGTEXT	Может хранить не более 4 294 967 295 символов.
LONGBLOB	Может хранить не более 4 294 967 295 символов.
ENUM(x,y,z,etc.)	Позволяет вводить список допустимых значений. Можно ввести до 65535 значений в SQL Тип данных ENUM список. Если при вставке значения не будет присутствовать в списке ENUM, то мы получим пустое значение. Ввести возможные значения можно в таком формате: ENUM ('X', 'Y', 'Z')
SET	SQL Тип данных SET напоминает ENUM за исключением того, что SET может содержать до 64 значений.

1. Типы данных SQL с плавающей точкой (дробные числа) и целые числа

Типы данных SQL	Описание
TINYINT(size)	Может хранить числа от -128 до 127
SMALLINT(size)	Диапазон от -32 768 до 32 767
MEDIUMINT(size)	Диапазон от -8 388 608 до 8 388 607
INT(size)	Диапазон от -2 147 483 648 до 2 147 483 647
BIGINT(size)	Диапазон от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807
FLOAT(size,d)	Число с плавающей точкой небольшой точности.
DOUBLE(size,d)	Число с плавающей точкой двойной точности.
DECIMAL(size,d)	Дробное число, хранящееся в виде строки.

1. Типы данных SQL — Дата и время

Типы данных SQL	Описание
DATE()	Дата в формате ГГГГ-ММ-ДД
DATETIME()	Дата и время в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС
TIMESTAMP()	Дата и время в формате timestamp. Однако при получении значения поля оно отображается не в формате timestamp, а в виде ГГГГ-ММ-ДД ЧЧ:ММ:СС
TIME()	Время в формате ЧЧ:ММ:СС
YEAR()	Год в двух значной или в четырехзначном формате.

 

 

Тип изменения	Методика внесения
Переименование таблицы	sp_rename
Изменение владельца таблицы	sp_changeobjectowner
свойства столбца	Оператор ALTERTABLE
переименование столбца	sp_rename
Удалить	ROP TABLE

 

 

Функции:

Scalar – возвращает обычное скалярное значение, каждая может включать множество команд обеднённых в один блок с помощью конструкции begin, end.

Inline – содержат всего 1 команду select, в виде значений типа данных table

Multi-Statement – возвращает значения типа данных table, содержащая набор данных, однако в теле функции находятся множество команд SQL. Именно с их помощью и формируется набор данных, который должен быть возвращён после выполнения функций.

 

Scalar
<определение скалярной функции>::=
{CREATE|ALTER} FUNCTION {владелец}
([{@имя параметра скаляр_тип_данных [=default]}[,…n]])
RETURNS скаляр_тип_данных
[WITH{ENCRYPTION|SCHEMABINDIG}
[,…n]]
[AS]
BEGIN
<тело_функции>
RETURN скаляр_тип_данных
END

 

Пример: функция для вычисления суммарного количества товара поступившего за определённую дату, владелец функции пользователь с именем User1

Create Function
User1 sales(@data DATETIME)
Returns
As
Begin
Declare @c int
Set @c = (select SUM (количество)
From Сделка
Where дата=@data)
Return(@c)
End

 

<определение табл. функции>::=
{CREATE|ALTER} FUNCTION {владелец}
([{@имя параметра table [=default]}[,…n]])
RETURNS table
[WITH{ENCRYPTION|SCHEMABINDIG}, [,…n]]
[AS]
RETURN [(] Select_оператор [)]

Функция табличного типа

Create Function User1.itog()
returns Table
As
Return (Select top 2 Товар. Название
From Товар inner Join склад On Товар Код Товара = Склад. Код Товара
Order by Склад Остаток DESC)

Хранимые процедуры

Представляет собой оформленный особым образом сценарий, который хранится в БД. Хранимые процедуры отличаются от сценариев тем, что в них допускается использование входных и выходных параметров, а так же возвращаемых значений, которые фактически не могут использоваться в обычном сценарии.

Пример хранимой процедуры, которая возвращает значение всех полей всех строк таблицы:

USE <название процедуры>
GO
 CREATE PROC sp Shippers
AS
 Select from Shippers
EYEC sp Shippers

Удаление хранимых процедур:

DROP PROC / PROCEDURE <sproc name>

 

@ Parameter_name [As] datatype [default/null] [VARYING][output/out]

USE Norohwind
GO
CREATE PROC sp insert Shipper
@ Company Name nvarchar (40),
@ Phone nvarchar (24)
As
INSER INTO Shippers
VALUES
(@CompanyName, @Phone)
EXEC sp insert Shipper ‘UDSU, inc’, ‘(505/123-4567)’

 

1. Типы данных

2. Создание таблиц

3. Хранимые процедуры

 

 

Триггеры

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

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

1. Проверка корректности введённых данных и выполнение сложных ограничений целостности данных

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

3. Накопление аудиторской информации посредством фиксации сведений о внесённых изменениях и в тех таблицах, которые её выполнили

4. Поддержка репликаций

CREATE TRIGGER
<определение триггера>::=
CREATE TRIGGER имя_триггера
BEFORE | AFTER <триггерное событие>
ON <имя_таблицы>
[REFERENCING
 <список_старых_или_новых_>]
[FOR EACH {ROW | STATEMENT}]
[WHEN (условие_триггера)]
<тело_триггера>

 

Недостатки:

1. Сложность

2. Скрытая функциональность

3. Влияние на производительность

4.