eleks.com

• CTE• DML оператори• Представлення

Data Manipulation LanguageViews

Common Table ExpressionКонструкція Common Table Expression (CTE):

;with ActorsAndGenres as(

select a.Name, g.Name as Genre from Actor ainner join [Cast] c on c.ActorId = a.ActorIdinner join Movie m on m.MovieId = c.MovieIdinner join Genre g on m.GenreKey = g.GenreKeygroup by a.Name, g.Name

)select Name from ActorsAndGenreswhere Genre = 'Thriller'unionselect Name from ActorsAndGenreswhere Genre = 'Horror'

Рекурсивні CTEГенерація числової послідовності: ;with Numbers as

( select 1 as n

union all

select n + 1 from Numbers

where n + 1 <= 10

) select n

from Numbers

Отримання вузлів дерева:

;with pathToRoot as

(

select NodeId, ParentNodeId from Node

where NodeId = 42

union all

select n.NodeId, n.ParentNodeId from Node n

inner join pathToRoot r

on r.ParentNodeID = n.NodeID

)

select NodeId, ParentNodeId

from pathToRoot

Data Manipulation Language

• INSERT - вставка нового рядка або набору рядків в таблицю БД

• UPDATE - зміна даних в таблиці БД • DELETE - видалення рядка або набору рядків

із таблиці БД• MERGE – всі операції над даними в одному

виразі• TRUNCATE – Очистка таблиці

Оператор INSERTВставка одного рядка з явно заданими значеннями:

insert into Actor(Name, BirthDate, SexEnum)values (‘Jack Nickolson', ‘1937-04-25’, 1)

Використання опції DEFAULT:CREATE TABLE Document (…Date datetime NOT NULL DEFAULT getdate(),…insert into Document (Type, Number, Client, Amount)values (1, 'Z-001', 1, 10)insert into Document (Type, Number, Date, Client, Amount)values (1, 'Z-003', default, 1, 10)insert into Document DEFAULT VALUES

Оператор INSERTВставка більше одного рядка :

insert into Document (Type, Number, Date, Client, Amount)select 1, 'Z-010', getdate(), 1, 10union allselect 1, 'Z-011', cast('2007-10-30' as datetime), 2, 10

insert into Document (Type, Number, Date, Client, Amount)select Type, 'Z-012', Date, Client, Amount

from Document where Number = 'Z-003‘

insert into Actor (Name, Age, SexEnum)values

('Jack Nicholson', '1937-04-25', 1),('Ralph Fiennes', '1969-03-12', 1),('Robert De Niro', '1943-08-01', 1)

Оператор INSERTАвтоінкремент (IDENTITY):

insert into Document (Type, Number, Date, Client, Amount)select 1, 'Z-005', getdate(), 1, 10

select SCOPE_IDENTITY()

Вставка в такий стовпчик заборонена, видається повідомлення про

помилку.

Включити додавання даних до стовпців з автоінкрементом:

SET IDENTITY_INSERT Document ON/OFF

Оператор UPDATE Змінює наявні дані:

update Documentset Date = default ,Comment = NULLwhere DocumentId = 1

Використання підзапитів та функцій:update Document set Amount = (select sum(Amount*Price) from DocumentDetail where Number = DocumentDetail) ,Comment = case when Comment is null then '' else left( Comment, 10) endwhere DocumentId = 1

Оператор UPDATE Запити з кількох таблиць при зміні даних:

update docset Comment = 'Client - ' + Namefrom Document docinner join Client c

on cInstance = dClient

Можна міняти дані лише в одній таблиці одночасно.

Без використання WHERE оператор UPDATE змінить всі рядки в таблиці

Видалення данихОператор DELETE:

delete from Documentwhere Number > 'Z'

Команда TRUNCATE:truncate table [Cast]

Відмінності використання TRUNCATE та DELETE : Не логується видалення окремих рядків таблиці, записуються

лише відомості про звільнений простір Не обробляються трігери та, як наслідок, посилання на зовнішні

ключі Значення автогенератора (IDENTITY) змінюється на початкове Потрібно мати доступ до таблиці як власник

Як і у випадку з UPDATE: не забувайте WHERE =)

Оператор MERGEВся логіка роботи з таблицею в одному операторі:

;merge into dbo.LayerBuffer as target using @LayerBuffers as source on (target.LayerBufferId = source.LayerBufferId)when matched thenupdate set target.Argb = source.Argbwhen not matched by source and (target.LayerId = @LayerId) thendeletewhen not matched by target theninsert (LayerId, BufferMeters, Shape)values ( @LayerId,source.BufferMeters,source.Shape));

Оператор OUTPUTВивід змінених значень:

insert into Movie (Name, Description, PremiereDate, BoxOffice, GenreKey)

output inserted.MovieId, inserted.Namevalues ('Pulp Fiction', null, '1994-02-22', 21349700, 7),('The Shining', null, '1980-05-23', 30000400, 13)

update [Cast] set CharacterName = ‘Willy Wonka’

output inserted.CharacterName into #tempTablewhere CharacterName = ‘WalterWhite’

delete from Feedbackoutput deleted.FeedbackId into DeletedId(Id)where Rank < 5

OUTPUT та MERGE;with newActor as ( select 'Natalie Portman' Name union select 'Jack Nicholson' union select 'Tom Hanks')merge into Actor as target using newActor as source

on (target.Name = source.Name) when matched then

update set BirthDate = ‘1753-01-01' when not matched by source then

delete when not matched by target then

insert (Name, BirthDate, SexEnum) values(source.Name, source.BirthDate, source.SexEnum)output $action, inserted.ActorId, inserted.Name, deleted.ActorId,

deleted.Name;

OUTPUT та MERGEМапінг значень з різних таблиць:merge into dbo.[Event] using @Events as e on 1 = 0 when not matched then

insert (

Name, Description

)values (

Name, Description

)output e.FakeEventId, inserted.EventId into RealAndFakeIds (fakeId, realId);

Представлення ViewСтворення представлень:

create view DocumentView asselect d.DocumentId, d.Name, t.Type, d.Number, d.Date, d.Amount from Document d inner join Client c on c.ClientId = d.ClientId inner join DocumentType t on t.DocumentTypeId = d.DocumentTypeId

При створенні представлень можна вказати додаткові опції, наприклад, WITH CHECK OPTION чи WITH ENCRYPTION, інші.

Використання представлень:select * from DocumentView

DocumentId Name Type Number Date Amount--------- --------- ------- ------- ---------- -------1 Client_1 Invoice I-001 2007-10-31 0.002 Client_2 Invoice I-002 2007-01-04 35.003 Client_1 Invoice I-003 2007-02-04 10.004 Client_1 Invoice I-004 2007-02-04 20.00

Зміна даних за допомогою представлень:INSERT INTO DocumentView(Name) VALUES ('AAA')

update DocumentViewset Date = cast(convert(varchar(10), Date, 102) as datetime)

Дані в представленнях можна змінювати прямо вказані поля із таблиць за допомогою операторів INSERT, UPDATE, DELETE лише в одній табличці одночасно та при виконанні наступних умов :

― відсутність агрегатних функцій та виразу GROUP BY― не можна використовувати CROSS JOIN― відсутність виразів TOP, DISTINCT чи UNION― не можна використовувати колонки, які обраховуються всередині

представлення

Представлення View