Управление и манипулирование объектами

1. Управление и манипулирование объектами

1.
Манипулирование данными
2.
Объекты БД
3.
Манипулирование таблицами
4.
Манипулирование представлениями
1

2. 1 Манипулирование данными

Операторы манипуляции данными (Data Manipulation Language, DML):
o SELECT считывает данные, удовлетворяющие заданным условиям,
o INSERT добавляет новые данные,
o UPDATE изменяет существующие данные,
o DELETE удаляет данные.
Управление и манипулирование объектами
2

3. 1 Манипулирование данными

Инструкция INSERT
Добавляет одну или несколько строк в таблицу или представление SQL Server
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n
| derived_table
| execute_statement
| <dml_table_source>
| DEFAULT VALUES
}
}
}
[;]
Управление и манипулирование объектами
]
3

4. 1 Манипулирование данными

Вставка одной строки данных
INSERT INTO Production.UnitMeasure VALUES ('FT', 'Feet', '20080414');
Вставка нескольких строк данных
INSERT INTO Production.UnitMeasure VALUES
('FT2', 'Square Feet ', '20080923'),
('Y', 'Yards', '20080923'),
('Y3', 'Cubic Yards', '20080923');
Вставка данных в порядке, отличном от порядка столбцов таблицы
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)
VALUES ('Square Yards', 'Y2', GETDATE());
Управление и манипулирование объектами
4

5. 1 Манипулирование данными

Инструкция UPDATE
Изменяет существующие данные в таблице или представлении в SQL Server
WITH <common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ { table_alias | <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
| @table_variable
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]
[ <OUTPUT Clause> ]
[ FROM{ <table_source> } [ ,...n ] ]
[ WHERE { <search_condition>
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
Управление и манипулирование объектами
}
5

6. 1 Манипулирование данными

Обновляется один столбец для всех строк в таблице Person.Address
UPDATE Person.Address SET ModifiedDate = GETDATE();
Выполняется обновление значений в столбцах Bonus, CommissionPct и
SalesQuota для всех строк в таблице SalesPerson.
UPDATE Sales.SalesPerson SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;
Инструкция обновляет значение в столбце Color таблицы Production.Product для
всех строк, в которых имеется существующее значение Red в столбце Color и
имеется значение в столбце Name, который начинается с Road-250
UPDATE Production.Product SET Color = N'Metallic Red' WHERE Name LIKE N'Road-250%'
AND Color = N'Red';
Удваивается значение столбца ListPrice для всех строк в таблице Product.
UPDATE Production.Product SET ListPrice = ListPrice * 2;
Управление и манипулирование объектами
6

7. 1 Манипулирование данными

Используется составной оператор += для добавления данных ' - tool malfunction' к
существующему значению в столбце Name для строк, имеющих
значение ScrapReasonID от 10 до 12.
UPDATE Production.ScrapReason SET Name += ' - tool malfunction'
WHERE ScrapReasonID BETWEEN 10 and 12;
Управление и манипулирование объектами
7

8. 1 Манипулирование данными

Используется вложенный запрос в предложении SET для определения
значения, которое используется для обновления столбца. Вложенный
запрос должен возвращать только скалярное значение (то есть одно
значение для каждой строки). В примере изменяется столбец SalesYTD в
таблице SalesPerson для отображения самой последней информации о
продажах, зафиксированной в таблице SalesOrderHeader. Вложенный
запрос проводит статистическую обработку сведений о продажах по всем
продавцам в инструкции UPDATE.
UPDATE
Sales.SalesPerson
SET
SalesYTD = SalesYTD +
(SELECT SUM(so.SubTotal)
FROM Sales.SalesOrderHeader AS so
WHERE so.OrderDate =
(SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS so2
WHERE so2.SalesPersonID = so.SalesPersonID) AND
Sales.SalesPerson.BusinessEntityID = so.SalesPersonID
GROUP BY so.SalesPersonID);
Управление и манипулирование объектами
8

9. 1 Манипулирование данными

Инструкция DELETE
Удаляет одну или несколько строк из таблицы или представления в SQL Server
[ WITH <common_table_expression> [ ,...n ] ]
DELETE
[ TOP ( expression ) [ PERCENT ] ]
[ FROM ]
{ { table_alias
| <object>
| rowset_function_limited
[ WITH ( table_hint_limited [ ...n ] ) ] }
| @table_variable
}
[ <OUTPUT Clause> ]
[ FROM table_source [ ,...n ] ]
[ WHERE { <search_condition>
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
Управление и манипулирование объектами
9

10. 1 Манипулирование данными

Команда удаляет все строки из таблицы SalesPersonQuotaHistory в базе данных
AdventureWorks2012,
поскольку
не
указано
предложение
WHERE,
ограничивающее количество удаляемых строк.
DELETE FROM Sales.SalesPersonQuotaHistory
Команда удаляет все строки таблицы ProductCostHistory в базе данных
AdventureWorks2012, у которых значение в столбце StandardCost больше 1000.00.
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
Значение в столбце StandardCost должно быть в диапазоне от 12.00 до 14.00, а
значение в столбце SellEndDate должно быть равно NULL. Также выводится
значение из функции@@ROWCOUNT, которое показывает количество удаленных
строк.
DELETE Production.ProductCostHistory
WHERE StandardCost BETWEEN 12.00 AND 14.00 AND EndDate IS NULL;
PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));
Управление и манипулирование объектами
10

11. 1 Манипулирование данными

Удаления строк в одной таблице на основании данных в другой таблице. Будут
удалены строки на основании сведений о продажах за текущий год, хранящихся в
таблице SalesPerson.
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
Управление и манипулирование объектами
11

12. Управление и манипулирование объектами

1.
Манипулирование данными
2.
Объекты БД
3.
Манипулирование таблицами
4.
Манипулирование представлениями
12

13. 2 Объекты БД

Объекты (сущности) БД:
• База данных (database)
• Таблица (table)
• Представление (view)
• Процедура (procedure)
• Функция (function)
• Триггер (trigger)
• Пользователь (use)
• Роль (role)
• Схема (scheme)
• Синоним (synonym)
• Статистика (statistics)
• Маршрут (route)
• Правило (rule)
• и др (всего 53)
Управление и манипулирование объектами
13

14. 2 Объекты БД

Операторы определения данных (Data Definition Language, DDL):
o CREATE создает объект БД (саму базу, таблицу, представление,
пользователя и т. д.),
o ALTER изменяет объект,
o DROP удаляет объект;
Управление и манипулирование объектами
14

15. Управление и манипулирование объектами

1.
Манипулирование данными
2.
Объекты БД
3.
Манипулирование таблицами
4.
Манипулирование представлениями
15

16. 3 Манипулирование таблицами

Упрощенный пример создания таблицы
CREATE TABLE имя_таблицы (
столбец тип_данных [ DEFAULT значение_по_умол.]
[ [ CONSTRAINT имя_огранич.] ограничение_поля ]
[, столбец тип_данных [ DEFAULT значение_по_умол. ]
[ [ CONSTRAINT имя_огранич.] ограничение_поля ]] ...);
CREATE TABLE people(
[surname] [varchar](24) NULL,
[name] [varchar](16) NULL,
[secname] [varchar](16) NULL,
[birthday] [datetime] NULL,
[sex] [int] NULL)
Управление и манипулирование объектами
16

17. 3 Манипулирование таблицами

Команда удаления таблицы
DROP TABLE имя_таблицы
Управление и манипулирование объектами
17

18. Управление и манипулирование объектами

1.
Манипулирование данными
2.
Объекты БД
3.
Манипулирование таблицами
4.
Манипулирование представлениями
18

19. 4 Манипулирование представлениями

Представление — виртуальная таблица, представляющая собой
поименованный запрос (синоним к запросу), который будет подставлен
как подзапрос при использовании представления.
В отличие от обычных таблиц реляционной баз данных, представление
не является самостоятельной частью набора данных, хранящегося в
базе. Содержимое представления динамически вычисляется на
основании данных, находящихся в реальных таблицах. Изменение
данных в реальной таблице базы данных немедленно отражается в
содержимом всех представлений, построенных на основании этой
таблицы
Управление и манипулирование объектами
19

20. 4 Манипулирование представлениями

CREATE VIEW имя_представления [ (поле1 [ ,...полеn ] ) ] AS
команда_select
CREATE VIEW Представление_рейсы AS
SELECT Рейсы.№Рейса, Пункты.Пункт_прибытия
FROM Рейсы, Пункты
WHERE Рейсы.№Пункта = Пункты.№Пункта
DROP VIEW имя_представления
Управление и манипулирование объектами
20