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

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


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница9 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
TOP
DISTINCT
UNION
JOIN
агрегатные функции типа COUNT или MAX
GROUP BY и HAVING
подзапросы производные столбцы или столбцы, которые вычисляются на основании нескольких значений обращения одновременно к нескольким таблицам
Допустим, у нас есть следующая таблица:
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 VIEW ProductView
AS SELECT ProductName AS Product, Manufacturer, Price
FROM Products
Добавим в него данные:
INSERT INTO ProductView (Product, Manufacturer, Price)
VALUES('Nokia 8', 'HDC Global', 18000)
SELECT * FROM ProductView
MS SQL Server и T-SQL | Обновляемое представление
Стоит отметить, что при добавлении фактически будет добавлен объект в таблицу Products,
которую использует представление ProductView. И поэтому надо учитывать, что если в этой таблице есть какие-либо столбцы, в которые представление не добавляет данные, но которые не допускают значение NULL или не поддерживают значение по умолчанию, то добавление завершится с ошибкой.
Обновление строки представления:
UPDATE ProductView
SET Price= 15000 WHERE Product='Nokia 8'
Удаление строки в представлении:
DELETE FROM ProductView
WHERE Product='Nokia 8'
Обновление и удаление также затрагивают ту таблицу, которую использует представление.
MS SQL Server и T-SQL | Табличные переменные
Табличные переменные
Табличные переменные (table variable) позволяют сохранить содержимое целой таблицы.
Формальный синтаксис определения подобной переменной во многом похож на создание таблицы:
DECLARE @табличная_переменная TABLE
(столбец_1 тип_данных [атрибуты_столбца], столбец_2 тип_данных [атрибуты_столбца] ....)
[атрибуты_таблицы]
Например:
DECLARE @ABrends TABLE (ProductId INT, ProductName NVARCHAR(20))
В данном случае переменная @ABrends будет содержать два столбца.
В дальнейшем мы сможем работать с этой переменной как с обычной таблицей, то есть добавлять в нее данные, изменять, удалять и извлекать их:
DECLARE @ABrends TABLE (ProductId INT, ProductName NVARCHAR(20))
INSERT INTO @ABrends
VALUES(1, 'iPhone 8'),
(2, 'Samsumg Galaxy S8')
SELECT * FROM @ABrends
Однако следует учитывать, что такие переменные не полностью эквивалентны таблицам. Они живут в пределах одного пакета, после завершения работы которого они удаляются. То есть они носят временный характер, и физически их данные нигде не хранятся на жестком диске.
MS SQL Server и T-SQL | Временные и производные таблицы
Временные и производные таблицы
Временные таблицы
В дополнение к табличным переменным можно определять временные таблицы. Такие таблицы могут быть полезны для хранения табличных данных внутри сложного комплексного скрипта.
Временные таблицы существуют на протяжении сессии базы данных. Если такая таблица создается в редакторе запросов (Query Editor) в SQL Server Management Studio, то таблица будет существовать пока открыт редактор запросов. Таким образом, к временной таблице можно обращаться из разных скриптов внутри редактора запросов.
После создания все временные таблицы сохраняются в таблице tempdb, которая имеется по умолчанию в MS SQL Server.
Если необходимо удалить таблицу до завершения сессии базы данных, то для этой таблицы следует выполнить команду DROP TABLE.
Название временной таблицы начинается со знака решетки #. Если используется один знак #, то создается локальная таблица, которая доступна в течение текущей сессии. Ели используются два знака ##, то создается глобальная временная таблица. В отличие от локальной глобальная временная таблица доступна всем открытым сессиям базы данных.
Например, создадим локальную временную таблицу:
CREATE TABLE #ProductSummary
(ProdId INT IDENTITY,
ProdName NVARCHAR(20),
Price MONEY)
INSERT INTO #ProductSummary
VALUES ('Nokia 8', 18000),
('iPhone 8', 56000)
SELECT * FROM #ProductSummary
MS SQL Server и T-SQL | Временные и производные таблицы
SUM(ProductCount * Price) AS
TotalSum
И с этой таблицей можно работать в большей степени как и с обычной таблицей - получать данные, добавлять, изменять и удалять их. Только после закрытия редактора запросов эта таблица перестанет существовать.
Подобные таблицы удобны для каких-то временных промежуточных данных. Например, пусть у нас есть три таблицы:
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:
SELECT ProductId,
SUM(ProductCount) AS TotalCount,
MS SQL Server и T-SQL | Временные и производные таблицы
INTO #OrdersSummary
FROM Orders
GROUP BY ProductId
SELECT Products.ProductName, #OrdersSummary.TotalCount, #OrdersSummary.TotalSum
FROM Products
JOIN #OrdersSummary ON Products.Id = #OrdersSummary.ProductId
Здесь вначале извлекаются данные во временную таблицу #OrdersSummary. Причем так как данные в нее извлекаются с помощью выражения SELECT INTO, то предварительно таблицу не надо создавать. И эта таблица будет содержать id товара, общее количество проданного товара и на какую сумму был продан товар.
Затем эта таблица может использоваться в выражениях INNER JOIN.
Подобным образом определяются глобальные временные таблицы, единственное, что их имя начинается с двух знаков ##:
CREATE TABLE ##OrderDetails
(ProductId INT, TotalCount INT, TotalSum MONEY)
INSERT INTO ##OrderDetails
SELECT ProductId, SUM(ProductCount), SUM(ProductCount * Price)
FROM Orders
GROUP BY ProductId
SELECT * FROM ##OrderDetails
MS SQL Server и T-SQL | Временные и производные таблицы
Производные таблицы
Кроме временных таблиц MS SQL Server позволяет создавать производные таблицы, которые в плане производительности являются более эффективным решением, чем временные. Производная таблица задается с помощью ключевого слова WITH:
WITH OrdersInfo AS
(
SELECT ProductId,
SUM(ProductCount) AS TotalCount,
SUM(ProductCount * Price) AS TotalSum
FROM Orders
GROUP BY ProductId
)
SELECT * FROM OrdersInfo -- здесь нормально
SELECT * FROM OrdersInfo -- здесь ошибка
SELECT * FROM OrdersInfo -- здесь ошибка
В отличие от временных таблиц производные хранятся в оперативной памяти и существуют только во время первого выполнения запроса, который представляет эту таблицу.
MS SQL Server и T-SQL | Хранимые процедуры
Хранимые процедуры
Создание и выполнение процедур
Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении покупке товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект - хранимую процедуру (stored procedure).
То есть по сути хранимые процедуры представляет набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.
Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и тем самым уменьшить вероятность преднамеренных или неосознанных нежелательных действий в отношении этих данных.
И еще один важный аспект - производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.
Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC.
Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода,
безопасность и производительность.
Например, пусть в базе данных есть таблица, которая хранит данные о товарах:
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
);
Создадим хранимую процедуру для извлечения данных из этой таблицы:
USE productsdb;
GO
CREATE PROCEDURE ProductSummary AS
SELECT ProductName AS Product, Manufacturer, Price
FROM Products
MS SQL Server и T-SQL | Хранимые процедуры
Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды
USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.
После имени процедуры должно идти ключевое слово AS.
Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN...END:
USE productsdb;
GO
CREATE PROCEDURE ProductSummary AS
BEGIN
SELECT ProductName AS Product, Manufacturer, Price
FROM Products
END;
После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management
Studio в подузле Programmability -> Stored Procedures:
И мы сможем управлять процедурой также и через визуальный интерфейс.
Выполнение процедуры
Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE:
EXEC ProductSummary
MS SQL Server и T-SQL | Хранимые процедуры
Удаление процедуры
Для удаления процедуры применяется команда DROP PROCEDURE:
DROP PROCEDURE ProductSummary
MS SQL Server и T-SQL | Параметры в процедурах
SELECT * FROM Products
Параметры в процедурах
Процедуры могут принимать параметры. Параметры бывают входными - с их помощью в процедуру можно передать некоторые значения. И также параметры бывают выходными - они позволяют возвратить из процедуры некоторое значение.
Например, пусть в базе данных будет следующая таблица Products:
USE productsdb;
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
);
Определим процедуру, которая будет добавлять данные в эту таблицу:
USE productsdb;
GO
CREATE PROCEDURE AddProduct
@name NVARCHAR(20),
@manufacturer NVARCHAR(20),
@count INT,
@price MONEY
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
После названия процедуры идет список входных параметров, которые определяются также как и переменные - название начинается с символа @, а после названия идет тип переменной. И с помощью команды INSERT значения этих параметров будут передаваться в таблицу Products.
Используем эту процедуру:
USE productsdb;
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);
DECLARE @prodCount INT, @price MONEY
SET @prodName = 'Galaxy C7'
SET @company = 'Samsung'
SET @price = 22000
SET @prodCount = 5
EXEC AddProduct @prodName, @company, @prodCount, @price
MS SQL Server и T-SQL | Параметры в процедурах
Здесь передаваемые в процедуру значения определяются через переменные. При вызове процедуры ей через запятую передаются значения. При этом значения передаются параметрам процедуры по позиции. Так как первым определен параметр @name, то ему будет передаваться первое значение - значение переменной @prodName. Второму параметру - @manufacturer передается второе значение - значение переменной @company и так далее. Главное, чтобы между передаваемыми значениями и параметрами процедуры было соответствие по типу данных.
Также можно было бы передать непосредственно значения:
EXEC AddProduct 'Galaxy C7', 'Samsung', 5, 22000
Также значения параметрам процедуры можно передавать по имени:
USE productsdb;
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);
SET @prodName = 'Honor 9'
SET @company = 'Huawei'
EXEC AddProduct @name = @prodName,
@manufacturer=@company,
@count = 3,
@price = 18000
При передаче параметров по имени параметру процедуры присваивается некоторое значение.
Необязательные параметры
Параметры можно отмечать как необязательные, присваивая им некоторое значение по умолчанию.
Например, в случае выше мы можем автоматически устанавливать для количества товара значение
1, если соответствующее значение не передано в процедуру:
MS SQL Server и T-SQL | Параметры в процедурах
USE productsdb;
GO
CREATE PROCEDURE AddProductWithOptionalCount
@name NVARCHAR(20),
@manufacturer NVARCHAR(20),
@price MONEY,
@count INT = 1
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
При этом необязательные параметры лучше помещать в конце списка параметров процедуры.
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20), @price MONEY
SET @prodName = 'Redmi Note 5A'
SET @company = 'Xiaomi'
SET @price = 22000
EXEC AddProductWithOptionalCount @prodName, @company, @price
SELECT * FROM Products
И в этом случае для параметра @count в процедуру можно не передавать значение.
MS SQL Server и T-SQL | Выходные параметры хранимых процедур
Выходные параметры и возвращение результата
Выходные параметры позволяют возвратить из процедуры некоторый результат. Выходные параметры определяются с помощью ключевого слова OUTPUT. Например, определим еще одну процедуру:
USE productsdb;
GO
CREATE PROCEDURE GetPriceStats
@minPrice MONEY OUTPUT,
@maxPrice MONEY OUTPUT
AS
SELECT @minPrice = MIN(Price), @maxPrice = MAX(Price)
FROM Products
При вызове процедуры для выходных параметров передаются переменные с ключевым словом
OUTPUT:
USE productsdb;
DECLARE @minPrice MONEY, @maxPrice MONEY
EXEC GetPriceStats @minPrice OUTPUT, @maxPrice OUTPUT
PRINT 'Минимальная цена ' + CONVERT(VARCHAR, @minPrice)
PRINT 'Максимальная цена ' + CONVERT(VARCHAR, @maxPrice)
Также можно сочетать входные и выходные параметры. Например, определим процедуру, которая добавляет новую строку в таблицу и возвращает ее id:
USE productsdb;
GO
CREATE PROCEDURE CreateProduct
@name NVARCHAR(20),
@manufacturer NVARCHAR(20),
MS SQL Server и T-SQL | Выходные параметры хранимых процедур
@count INT,
@price MONEY,
@id INT OUTPUT
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
SET @id = @@IDENTITY
С помощью глобальной переменной @@IDENTITY можно получить идентификатор добавленной записи.
При вызове этой процедуры ей также по позиции передаются все входные и выходные параметры:
USE productsdb;
DECLARE @id INT
EXEC CreateProduct 'LG V30', 'LG', 3, 28000, @id OUTPUT
PRINT @id
Возвращение значения
Кроме передачи результата выполнения через выходные параметры хранимая процедура также может возвращать какое-либо значение с помощью оператора RETURN. Хотя данная возможность во многом нивелирована использованием выходных параметров, через которые можно возвращать результат, тем не менее, если надо возвратить из процедуры одно значение, то вполне можно использовать оператор RETURN.
Например, возвратим среднюю цену на товары:
USE productsdb;
GO
CREATE PROCEDURE GetAvgPrice AS
DECLARE @avgPrice MONEY
SELECT @avgPrice = AVG(Price)
FROM Products
RETURN @avgPrice;
После оператора RETURN указывается возвращаемое значение. В данном случае это значение переменной @avgPrice.
Вызовем данную процедуру:
USE productsdb;
DECLARE @result MONEY
EXEC @result = GetAvgPrice
PRINT @result
Для получения результата процедуры ее значение сохраняется в переменную (в данном случае в переменную @result):
MS SQL Server и T-SQL | Определение триггеров
Триггеры
Определение триггеров
Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE,
DELETE.
Формальное определение триггера:
CREATE TRIGGER имя_триггера
ON {имя_таблицы | имя_представления}
{AFTER | INSTEAD OF} [INSERT | UPDATE | DELETE]
AS выражения_sql
Для создания триггера применяется выражение CREATE TRIGGER, после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.
Каждый триггер ассоциируется с определенной таблицей или представлением, имя которых указывается после слова ON.
Затем устанавливается тип триггера. Мы можем использовать один из двух типов:
AFTER: выполняется после выполнения действия. Определяется только для таблиц.
INSTEAD OF: выполняется вместо действия (то есть по сути действие - добавление,
изменение или удаление - вообще не выполняется). Определяется для таблиц и представлений
После типа триггера идет указание операции, для которой определяется триггер: INSERT, UPDATE или DELETE.
Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT.
В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.
1   2   3   4   5   6   7   8   9   10

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


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