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

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


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница8 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
FROM Products
Фактически все то же самое, что и в предыдущем примере, только после CASE не указывается сравниваемое значение. А сами выражения сравнения стоят после оператора WHEN. И если выражение после оператора WHEN будет истинно, то возвращается значение, которое идет после соответствующего оператора THEN.
MS SQL Server и T-SQL | Функции CASE и IIF
IIF
Функция IIF в зависимости от результата условного выражения возвращает одно из двух значений.
Общая форма функции выглядит следующим образом:
IIF(условие, значение_1, значение_2)
Если условие в функции IIF истинно то возвращается значение_1, если ложно, то возвращается значение_2. Например:
SELECT ProductName, Manufacturer,
IIF(ProductCount>3, 'Много товара', 'Мало товара')
FROM Products
MS SQL Server и T-SQL | Функции NEWID, ISNULL и COALESCE
Функции NEWID, ISNULL и COALESCE
NEWID
Для генерации объекта UNIQUEIDENTIFIER, то есть некоторого уникального значения, используется функция NEWID(). Например, мы можем определить для столбца первичного ключа тип
UNIQUEIDENTIFIER и по умолчанию присваивать ему значение функции NEWID:
CREATE TABLE Clients
(
Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Phone NVARCHAR(20) NULL,
Email NVARCHAR(20) NULL
)
INSERT INTO Clients (FirstName, LastName, Phone, Email)
VALUES ('Tom', 'Smith', '+36436734', NULL),
('Bob', 'Simpson', NULL, NULL)
ISNULL
Функция ISNULL проверяет значение некоторого выражения. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:
ISNULL(выражение, значение)
Например, возьмем выше созданную таблицу и применим при получении данных функцию ISNULL:
SELECT FirstName, LastName,
ISNULL(Phone, 'не определено') AS Phone,
ISNULL(Email, 'неизвестно') AS Email
FROM Clients
MS SQL Server и T-SQL | Функции NEWID, ISNULL и COALESCE
COALESCE
Функция COALESCE принимает список значений и возвращает первое из них, которое не равно
NULL:
COALESCE(выражение_1, выражение_2, выражение_N)
Например, выберем из таблицы Clients пользователей и в контактах у них определим либо телефон,
либо электронный адрес, если они не равны NULL:
SELECT FirstName, LastName,
COALESCE(Phone, Email, 'не определено') AS Contacts
FROM Clients
То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка "не определено".
MS SQL Server и T-SQL | Переменные
Переменные и управляющие конструкции
Переменные в T-SQL
Переменная представляет именованный объект, который хранит некоторое значение. Для определения переменных применяется выражение DECLARE, после которого указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @:
DECLARE @название_переменной тип_данных
Например, определим переменную name, которая будет иметь тип NVARCHAR:
DECLARE @name NVARCHAR(20)
Также можно определить через запятую сразу несколько переменных:
DECLARE @name NVARCHAR(20), @age INT
С помощью выражения SET можно присвоить переменной некоторое значение:
DECLARE @name NVARCHAR(20), @age INT;
SET @name='Tom';
SET @age = 18;
Так как @name предоставляет тип NVARCHAR, то есть строку, то этой переменной соответственно и присваивается строка. А переменной @age присваивается число, так как она представляет тип
INT.
Выражение PRINT возвращает сообщение клиенту. Например:
PRINT 'Hello World'
И с его помощью мы можем вывести значение переменной:
DECLARE @name NVARCHAR(20), @age INT;
SET @name='Tom';
SET @age = 18;
PRINT 'Name: ' + @name;
PRINT 'Age: ' + CONVERT(CHAR, @age);
При выполнении скрипта внизу SQL Server Management Studio отобразится значение переменных:
MS SQL Server и T-SQL | Переменные
Также можно использовать для получения значения команду SELECT:
DECLARE @name NVARCHAR(20), @age INT;
SET @name='Tom';
SET @age = 18;
SELECT @name, @age;
MS SQL Server и T-SQL | Переменные в запросах
Переменные в запросах
Через переменные мы можем передавать данные в запросы. И также мы можем получать данные,
которые являются результатом запросов, в переменные. Например, при выборке из таблиц с помощью команды SELECT мы можем извлекать данные в переменную с помощью следующего синтаксиса:
SELECT @переменная_1 = спецификация_столбца_1,
@переменная_2 = спецификация_столбца_2,
@переменная_N = спецификация_столбца_N
Кроме того, в выражении SET значение, присваиваемое переменной, также может быть результатом команды SELECT.
Например, пусть у нас будут следующие таблицы:
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL
);
Используем переменные при извлечении данных:
DECLARE @maxPrice MONEY,
@minPrice MONEY,
@dif MONEY,
@count INT
SET @count = (SELECT SUM(ProductCount) FROM Orders);
MS SQL Server и T-SQL | Переменные в запросах
SELECT @minPrice=MIN(Price), @maxPrice = MAX(Price) FROM Products
SET @dif = @maxPrice - @minPrice;
PRINT 'Всего продано: ' + STR(@count, 5) + ' товарa(ов)';
PRINT 'Разница между максимальной и минимальной ценой: ' + STR(@dif)
В данном случае переменная @count будет содержать сумму всех значений из столбца
ProductCount таблицы Orders, то есть общее количество проданных товаров.
Переменные @min и @max хранят соответственно минимальное и максимальное значения столбца
Price из таблицы Products, а переменная @dif - разницу между этими значениями. И подобно простым значениям, переменные также могут участвовать в операциях.
Другой пример:
DECLARE @sum MONEY, @id INT, @prodid INT, @name NVARCHAR(20);
SET @id=2;
SELECT @sum = SUM(Orders.Price*Orders.ProductCount),
@name=Products.ProductName, @prodid = Products.Id
FROM Orders
INNER JOIN Products ON ProductId = Products.Id
GROUP BY Products.ProductName, Products.Id
HAVING Products.Id=@id
PRINT 'Товар ' + @name + ' продан на сумму ' + STR(@sum)
Здесь извлекаемые данные из двух таблиц Products и Orders группируются по столбцам Id и
ProductName из таблицы Products. Затем данные фильтруются по столбцу Id из Products. А
извлеченные данные попадают в переменные @sum, @name, @prodid.
MS SQL Server и T-SQL | Переменные в запросах
MS SQL Server и T-SQL | Условные выражения
Условные выражения
Для выполнения действий по условию используется выражение IF ... ELSE. SQL Server вычисляет выражение после ключевого слово IF. И если оно истинно, то выполняются инструкции после ключевого слова IF. Если условие ложно, то выполняются инструкции после ключевого слова
ELSE.
Если после IF или ELSE располагает блок инструкций, то этот блок заключается между ключевыми словами BEGIN и END:
IF условие
{инструкция|BEGIN...END}
[ELSE
{инструкция|BEGIN...END}]
Выражение ELSE является необязательным, и его можно опускать.
Например, пусть у нас есть следующие таблицы:
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL
);
Таблица Orders представляет заказы, а столбец CreatedAt - дату заказов. Узнаем, были ли заказы за последние 10 дней:
DECLARE @lastDate DATE
SELECT @lastDate = MAX(CreatedAt) FROM Orders
MS SQL Server и T-SQL | Условные выражения
IF DATEDIFF(day, @lastDate, GETDATE()) > 10
PRINT 'За последние десять дней не было заказов'
Добавим выражение ELSE:
DECLARE @lastDate DATE
SELECT @lastDate = MAX(CreatedAt) FROM Orders
IF DATEDIFF(day, @lastDate, GETDATE()) > 10
PRINT 'За последние десять дней не было заказов'
ELSE
PRINT 'За последние десять дней были заказы'
Если после IF или ELSE идут две и более инструкций, то они заключаются в блок BEGIN...END:
DECLARE @lastDate DATE, @count INT, @sum MONEY
SELECT @lastDate = MAX(CreatedAt),
@count = SUM(ProductCount) ,
@sum = SUM(ProductCount * Price)
FROM Orders
IF @count > 0
BEGIN
PRINT 'Дата последнего заказа: ' + CONVERT(NVARCHAR, @lastDate)
PRINT 'Продано ' + CONVERT(NVARCHAR, @count) + ' единиц(ы)'
PRINT 'На общую сумму ' + CONVERT(NVARCHAR, @sum)
END;
ELSE
PRINT 'Заказы в базе данных отсутствуют'
MS SQL Server и T-SQL | Условные выражения
MS SQL Server и T-SQL | Циклы
Циклы
Для выполнения повторяющихся операций в T-SQL применяются циклы. В частности, в T-SQL есть цикл WHILE. Этот цикл выполняет определенные действия, пока некоторое условие истинно.
WHILE условие
{инструкция|BEGIN...END}
Если в блоке WHILE необходимо разместить несколько инструкций, то все они помещаются в блок
BEGIN...END.
Например, вычислим факториал числа:
DECLARE @number INT, @factorial INT
SET @factorial = 1;
SET @number = 5;
WHILE @number > 0
BEGIN
SET @factorial = @factorial * @number
SET @number = @number - 1
END;
PRINT @factorial
То есть в данном случае пока переменная @number не будет равна 0, будет продолжаться цикл
WHILE. Так как @number равна 5, то цикл сделает пять проходов. Каждый проход цикла называется итерацией. В каждой итерации будет переустанавливаться значение переменных @factorial и
@number.
Другой пример - рассчитаем баланс счета через несколько лет с учетом процентной ставки:
MS SQL Server и T-SQL | Циклы
USE productsdb;
CREATE TABLE #Accounts ( CreatedAt DATE, Balance MONEY)
DECLARE @rate FLOAT, @period INT, @sum MONEY, @date DATE
SET @date = GETDATE()
SET @rate = 0.065;
SET @period = 5;
SET @sum = 10000;
WHILE @period > 0
BEGIN
INSERT INTO #Accounts VALUES(@date, @sum)
SET @period = @period - 1
SET @date = DATEADD(year, 1, @date)
SET @sum = @sum + @sum * @rate
END;
SELECT * FROM #Accounts
Здесь создается временная таблица #Accounts, в которую добавляется в цикле пять строк с данными.
Операторы BREAK и CONTINUE
Оператор BREAK позволяет завершить цикл, а оператор CONTINUE - перейти к новой итерации.
MS SQL Server и T-SQL | Циклы
DECLARE @number INT
SET @number = 1
WHILE @number < 10
BEGIN
PRINT CONVERT(NVARCHAR, @number)
SET @number = @number + 1
IF @number = 7
BREAK;
IF @number = 4
CONTINUE;
PRINT 'Конец итерации'
END;
Когда переменная @number станет равна 4, то с помощью оператора CONTINUE произойдет переход к новой итерации, поэтому последующая строка PRINT 'Конец итерации' не будет выполняться, хотя цикл продолжится.
Когда переменная @number станет равна 7, то оператор BREAK произведет выход из цикла, и он завершится.
MS SQL Server и T-SQL | Обработка ошибок
Обработка ошибок
Для обработки ошибок в T-SQL применяется конструкция TRY...CATCH. Она имеет следующий формальный синтаксис:
BEGIN TRY инструкции
END TRY
BEGIN CATCH инструкции
END CATCH
Между выражениями BEGIN TRY и END TRY помещаются инструкции, которые потенциально могут вызвать ошибку, например, какой-нибудь запрос. И если в этом блоке TRY возникнет ошибка, то управление передается в блок CATCH, где можно обработать ошибку.
В блоке CATCH для обаботки ошибки мы можем использовать ряд функций:
ERROR_NUMBER(): возвращает номер ошибки
ERROR_MESSAGE(): возвращает сообщение об ошибке
ERROR_SEVERITY(): возвращает степень серьезности ошибки. Степень серьезности представляет числовое значение. И если оно равно 10 и меньше, то такая ошибка рассматривается как предупреждение и не обрабатывается конструкцией TRY...CATCH. Если же это значение равно 20 и выше, то такая ошибка приводит к закрытию подключения к базе данных, если она не обрабатывается конструкцией TRY...CATCH.
ERROR_STATE(): возвращает состояние ошибки
Например, добавим в таблицу данные, которые не соответствуют ограничениям столбцов:
CREATE TABLE Accounts (FirstName NVARCHAR NOT NULL, Age INT NOT NULL)
BEGIN TRY
INSERT INTO Accounts VALUES(NULL, NULL)
PRINT 'Данные успешно добавлены!'
END TRY
BEGIN CATCH
PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ':' + ERROR_MESSAGE()
END CATCH
В данном случае для столбцов таблицы вставляются недопустимые данные - значения NULL,
поэтому обработка программы перейдет к блоку CATCH:
MS SQL Server и T-SQL | Обработка ошибок
MS SQL Server и T-SQL | Представления
Customers.FirstName AS
Customer,
Представления и табличные объекты
Представления
Представления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы, которые динамически извлекают используемые данные.
Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-операции. Они защищают данные, так как представления могут дать доступ к части таблицы, а не ко всей таблице. Представления также позволяют возвращать отформатированные значения из таблиц в нужной и удобной форме.
Для создания представления используется команда CREATE VIEW, которая имеет следующую форму:
CREATE VIEW название_представления [(столбец_1, столбец_2, ....)]
AS выражение_SELECT
Например, пусть у нас есть три связанных таблицы:
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL
);
Теперь добавим в базу данных, в которой содержатся данные таблицы, следующее представление:
CREATE VIEW OrdersProductsCustomers AS
SELECT Orders.CreatedAt AS OrderDate,
MS SQL Server и T-SQL | Представления
Products.ProductName As Product
FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id
INNER JOIN Customers ON Orders.CustomerId = Customers.Id
То есть данное представление фактически будет возвращать сводные данные из трех таблиц. И
после его создания мы сможем его увидеть в узле Views у выбранной базы данных в SQL Server
Management Studio:
Теперь используем созданное выше представление для получения данных:
SELECT * FROM OrdersProductsCustomers
При создании представлений следует учитывать, что представления, как и таблицы, должны иметь уникальные имена в рамках той же базы данных.
Представления могут иметь не более 1024 столбцов и могут обращать не более чем к 256
таблицам.
Также можно создавать представления на основе других представлений. Такие представления еще называют вложенными (nested views). Однако уровень вложенности не может быть больще 32-х.
MS SQL Server и T-SQL | Представления
Команда SELECT, используемая в представлении, не может включать выражения INTO или ORDER
BY (за исключением тех случаев, когда также применяется выражение TOP или OFFSET). Если же необходима сортировка данных в представлении, то выражение ORDER BY применяется в команде
SELECT, которая извлекает данные из представления.
Также при создании представления можно определить набор его столбцов:
CREATE VIEW OrdersProductsCustomers2 (OrderDate, Customer,Product)
AS SELECT Orders.CreatedAt,
Customers.FirstName,
Products.ProductName
FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id
INNER JOIN Customers ON Orders.CustomerId = Customers.Id
Изменение представления
Для изменения представления используется команда ALTER VIEW. Эта команда имеет практически тот же самый синтаксис, то и CREATE VIEW:
ALTER VIEW название_представления [(столбец_1, столбец_2, ....)]
AS выражение_SELECT
Например, изменим выше созданное представление OrdersProductsCustomers:
ALTER VIEW OrdersProductsCustomers
AS SELECT Orders.CreatedAt AS OrderDate,
Customers.FirstName AS Customer,
Products.ProductName AS Product,
Products.Manufacturer AS Manufacturer
FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id
INNER JOIN Customers ON Orders.CustomerId = Customers.Id
Удаление представления
Для удаления представления вызывается команда DROP VIEW:
DROP VIEW OrdersProductsCustomers
Также стоит отметить, что при удалении таблиц также следует удалить и представления, которые используют эти таблицы.
MS SQL Server и T-SQL | Обновляемое представление
Обновляемое представление
Представления могут быть обновляемыми (updatable). В таких представлениях мы можем изменить или удалить строки или добавить в них новые строки.
При создании подобных представлений есть множество ограничений. В частности, команда SELECT
в представлении не может содержать:
1   2   3   4   5   6   7   8   9   10

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


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