Главная страница
qrcode

Руководство по SQLServer. Руководство по ms sql server


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница2 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
REAL: хранит числа от –340E+38 to 3.40E+38. Занимает 4 байта. Эквивалентен типу
FLOAT(24).
MS SQL Server и T-SQL | Типы данных SQL
Примеры числовых столбцов:
Salary MONEY,
TotalWeight DECIMAL(9,2),
Age INT,
Surplus FLOAT
Типы данных, представляющие дату и время
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 воспринимаются как даты в диапазоне 20002049. А числа от 50 до 90 как диапазон чисел 1950 - 1999.
Month dd, yyyy - July 12, 2017
Распространенные форматы времени:
hh:mi - 13:21
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
Строковые типы данных
MS SQL Server и T-SQL | Типы данных SQL
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: может хранить данные любого другого типа данных TSQL.
XML: хранит документы XML или фрагменты документов XML. Занимает в памяти до 2 Гб.
MS SQL Server и T-SQL | Типы данных SQL
TABLE: представляет определение таблицы.
GEOGRAPHY: хранит географические данные, такие как широта и долгота.
GEOMETRY: хранит координаты местонахождения на плоскости.
MS SQL Server и T-SQL | Атрибуты и ограничения столбцов и таблиц
Атрибуты и ограничения столбцов и таблиц
При создании столбцов в T-SQL мы можем использовать ряд атрибутов, ряд которых являются ограничениями. Рассмотрим эти атрибуты.
PRIMARY KEY
С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.
CREATE TABLE Customers
(
Id INT PRIMARY KEY,
Age INT,
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
Email VARCHAR(30),
Phone VARCHAR(20)
)
Первичный ключ уникально идентифицирует строку в таблице. В качестве первичного ключа необязательно должны выступать столбцы с типом int, они могут представлять любой другой тип.
Установка первичного ключа на уровне таблицы:
CREATE TABLE Customers
(
Id INT,
Age INT,
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
Email VARCHAR(30),
Phone VARCHAR(20),
PRIMARY KEY(Id)
)
Первичный ключ может быть составным (compound key). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:
CREATE TABLE OrderLines
(
OrderId INT,
ProductId INT,
Quantity INT,
Price MONEY,
PRIMARY KEY(OrderId, ProductId)
)
Здесь поля OrderId и ProductId вместе выступают как составной первичный ключ. То есть в таблице OrderLines не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.
MS SQL Server и T-SQL | Атрибуты и ограничения столбцов и таблиц
IDENTITY
Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и NUMERIC. При добавлении новых данных в таблицу SQL Server будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно.
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT,
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
Email VARCHAR(30),
Phone VARCHAR(20)
)
Также можно использовать полную форму атрибута:
IDENTITY(seed, increment)
Здесь параметр seed указывает на начальное значение, с которого будет начинаться отсчет. А
параметр increment определяет, насколько будет увеличиваться следующее значение. По умолчанию атрибут использует следующие значения:
IDENTITY(1, 1)
То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу. Но мы можем это поведение переопределить. Например:
Id INT IDENTITY (2, 3)
В данном случае отсчет начнется с 2, а значение каждой последующей записи будет увеличиваться на 3. То есть первая строка будет иметь значение 2, вторая - 5, третья - 8 и т.д.
Также следует учитывать, что в таблице только один столбец должен иметь такой атрибут.
UNIQUE
Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE.
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT,
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
)
MS SQL Server и T-SQL | Атрибуты и ограничения столбцов и таблиц
В данном случае столбцы, которые представляют электронный адрес и телефон, будут иметь уникальные значения. И мы не сможем добавить в таблицу две строки, у которых значения для этих столбцов будет совпадать.
Также мы можем определить этот атрибут на уровне таблицы:
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT,
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
Email VARCHAR(30),
Phone VARCHAR(20),
UNIQUE(Email, Phone)
)
NULL и NOT NULL
Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL. Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа - в этом случае по умолчанию столбец имеет значение NOT NULL.
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
)
DEFAULT
Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
);
Здесь для столбца Age предусмотрено значение по умолчанию 18.
MS SQL Server и T-SQL | Атрибуты и ограничения столбцов и таблиц
CHECK
Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18 CHECK(Age >0 AND Age < 100),
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE CHECK(Email !=''),
Phone VARCHAR(20) UNIQUE CHECK(Phone !='')
);
Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).
Для соединения условий используется ключевое слово AND. Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).
Также с помощью CHECK можно создать ограничение в целом для таблицы:
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE,
CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !=''))
)
Оператор CONSTRAINT. Установка имени ограничений.
С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, DEFAULT, CHECK.
Имена ограничений можно задать на уровне столбцов. Они указываются после CONSTRAINT перед атрибутами:
CREATE TABLE Customers
(
Id INT CONSTRAINT PK_Customer_Id PRIMARY KEY IDENTITY,
Age INT
CONSTRAINT DF_Customer_Age DEFAULT 18
CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100),
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) CONSTRAINT UQ_Customer_Email UNIQUE,
Phone VARCHAR(20) CONSTRAINT UQ_Customer_Phone UNIQUE
)
Ограничения могут носить произвольные названия, но, как правило, для применяются следующие префиксы:
"PK_" - для PRIMARY KEY
"FK_" - для FOREIGN KEY
"CK_" - для CHECK
"UQ_" - для UNIQUE
"DF_" - для DEFAULT
В принципе необязательно задавать имена ограничений, при установке соответствующих атрибутов
SQL Server автоматически определяет их имена. Но, зная имя ограничения, мы можем к нему обращаться, например, для его удаления.
И также можно задать все имена ограничений через атрибуты таблицы:
CREATE TABLE Customers
(
Id INT IDENTITY,
Age INT CONSTRAINT DF_Customer_Age DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30),
Phone VARCHAR(20),
CONSTRAINT PK_Customer_Id PRIMARY KEY (Id),
CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100),
CONSTRAINT UQ_Customer_Email UNIQUE (Email),
CONSTRAINT UQ_Customer_Phone UNIQUE (Phone)
)
MS SQL Server и T-SQL | Внешние ключи
Внешние ключи
Внешние ключи применяются для установки связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Хотя, как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы, но это необязательно должно быть непременным условием. Внешний ключ также может указывать на какой-то другой столбец, который имеет уникальное значение.
Общий синтаксис установки внешнего ключа на уровне столбца:
[FOREIGN KEY] REFERENCES главная_таблица (столбец_главной_таблицы)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в квадратных скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY, но в принципе их необязательно указывать. После выражения REFERENCES
идет выражение ON DELETE и ON UPDATE.
Общий синтаксис установки внешнего ключа на уровне таблицы:
FOREIGN KEY (стобец1, столбец2, ... столбецN)
REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
Например, определим две таблицы и свяжем их посредством внешнего ключа:
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
);
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT REFERENCES Customers (Id),
CreatedAt Date
);
Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента.
Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через
MS SQL Server и T-SQL | Внешние ключи столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
Определение внешнего ключа на уровне таблицы выглядело бы следующим образом:
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id)
);
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса "FK_":
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt Date,
CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id)
);
В данном случае ограничение внешнего ключа CustomerId называется "FK_Orders_To_Customers".
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции:
CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
NO ACTION: предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
SET NULL: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
SET DEFAULT: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты
DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.
Каскадное удаление
По умолчанию, если на строку из главной таблицы по внешнему ключу ссылается какая-либо строка из зависимой таблицы, то мы не сможем удалить эту строку из главной таблицы. Вначале нам необходимо будет удалить все связанные строки из зависимой таблицы. И если при удалении строки из главной таблицы необходимо, чтобы были удалены все связанные строки из зависимой таблицы, то применяется каскадное удаление, то есть опция CASCADE:
MS SQL Server и T-SQL | Внешние ключи
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
)
Аналогично работает выражение ON UPDATE CASCADE. При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Но так как первичные ключи, как правило, изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON
UPDATE используется редко.
Установка NULL
При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL
);
Установка значения по умолчанию
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT
)
MS SQL Server и T-SQL | Изменение таблицы
Изменение таблицы
Возможно, в какой-то момент мы захотим изменить уже имеющуюся таблицу. Например, добавить или удалить столбцы, изменить тип столбцов, добавить или удалить ограничения. То есть потребуется изменить определение таблицы. Для изменения таблиц используется выражение ALTER TABLE.
Общий формальный синтаксис команды выглядит следующим образом:
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
{ ADD название_столбца тип_данных_столбца [атрибуты_столбца] |
1   2   3   4   5   6   7   8   9   10

перейти в каталог файлов


связь с админом