Материал: BD_Laboratornyi_774_praktikum

Примечание. В таблице Заказ столбец КодЗаказа является автоинкрементным и, поэтому, его значения не приведены.



SET DATEFORMAT dmy /* задаем привычный формат даты день.месяц.год, т.к.

по умолчанию установлен формат год.месяц.день */

INSERT INTO Заказ /* год можно задавать как 2-мя, так и 4-мя цифрами */

VALUES (3, 111, 8, '04.09.12', '14.09.12', 567)

/* . . . команда вставки новой строки – сформируйте самостоятельно */

/* . . . команда вставки новой строки – сформируйте самостоятельно */

/* . . . команда вставки новой строки – сформируйте самостоятельно */

/* . . . команда вставки новой строки – сформируйте самостоятельно */

/* . . . команда вставки новой строки – сформируйте самостоятельно */

/* . . . команда вставки новой строки – сформируйте самостоятельно */

/* . . . команда вставки новой строки – сформируйте самостоятельно */

/* . . . команда вставки новой строки – сформируйте самостоятельно */

3. Создание нового пользователя и связывание его с учетной записью. Синтаксис системной хранимой процедуры (см. [1], стр. 231):

sp_grantdbaccess [@loginame =] 'login'     [,[@name_in_db =] 'name_in_db' [OUTPUT]]



EXEC sp_grantdbaccess 'ИмяДомена\sql1', 'sql1'

EXEC sp_grantdbaccess 'ИмяДомена\sql2', 'sql2'

EXEC sp_grantdbaccess 'ИмяДомена\sql3', 'sql3'

EXEC sp_grantdbaccess 'ИмяДомена\sql4', 'sql4'

GO



Удаление пользователя выполняется с помощью системной хранимой процедуры, имеющей синтаксис (см. [1], стр. 232):

sp_revokedbaccess [ @name_in_db = ] 'name'

4. Создание пользовательской роли. Синтаксис системной хранимой процедуры (см. [1], стр. 240; [2]):

sp_addrole [ @rolename = ] 'role'     [ , [ @ownername = ] 'owner' ]



EXEC sp_addrole 'Гл.бухгалтер', 'sql1'

EXEC sp_addrole 'Бухгалтера', 'sql1'

EXEC sp_addrole 'Экономисты', 'sql1'

GO



Удаление пользовательской роли выполняется с помощью системной хранимой процедуры, имеющей синтаксис (см. [1], стр. 242):

sp_droprole [ @rolename = ] 'role'

5. Добавление нового члена в роль (как фиксированную, так и пользовательскую) базы данных. Синтаксис системной хранимой процедуры (см. [1], стр. 236):

sp_addrolemember [ @rolename = ] 'role' ,     [ @membername = ] 'security_account'



EXEC sp_addrolemember 'db_accessadmin', 'sql1'

EXEC sp_addrolemember 'Гл.бухгалтер', 'sql1'

EXEC sp_addrolemember 'Бухгалтера', 'sql2'

EXEC sp_addrolemember 'Бухгалтера', 'sql3'

EXEC sp_addrolemember 'Бухгалтера', 'Гл.бухгалтер'

EXEC sp_addrolemember 'Экономисты', 'sql4'

EXEC sp_addrolemember 'Экономисты', 'Гл.бухгалтер'

GO



Исключение члена из роли выполняется с помощью системной хранимой процедуры, имеющей синтаксис (см. [1], стр. 237):

sp_droprolemember [ @rolename = ] 'role' ,     [ @membername = ] 'security_account'

6. Предоставление привилегий доступа к объектам базы данных. Синтаксис системной хранимой процедуры (см. [1], стр. 254):

GRANT     { ALL [ PRIVILEGES ] | permission [ ,...n ] }     {         [ ( column [ ,...n ] ) ] ON { table | view }         | ON { table | view } [ ( column [ ,...n ] ) ]         | ON { stored_procedure | extended_procedure }         | ON { user_defined_function }     } TO security_account [ ,...n ] [ WITH GRANT OPTION ] [ AS { group | role } ]



GRANT SELECT, INSERT, UPDATE, DELETE

ON Валюта TO [Гл.бухгалтер] WITH GRANT OPTION

GRANT UPDATE

ON Заказ TO [Гл.бухгалтер] WITH GRANT OPTION

GRANT SELECT

ON Запрос1 TO [Гл.бухгалтер] WITH GRANT OPTION

GRANT UPDATE, DELETE

ON Клиент TO [Гл.бухгалтер] WITH GRANT OPTION

GRANT UPDATE, DELETE

ON Поставщик TO [Гл.бухгалтер] WITH GRANT OPTION

GRANT UPDATE, DELETE

ON Товар TO [Гл.бухгалтер] WITH GRANT OPTION

GRANT SELECT, INSERT

ON Заказ TO Бухгалтера

GRANT SELECT, INSERT

ON Клиент TO Бухгалтера

GRANT SELECT, INSERT

ON Поставщик TO Экономисты

GRANT SELECT, INSERT

ON Товар TO Экономисты

GRANT SELECT, INSERT, UPDATE, DELETE

ON Регион TO public GO



Примечание. С помощью ключевых слов WITH GRANT OPTION пользователям, указанным с помощью строки TO security_account [ ,...n ] будет предоставлено право выдавать другим пользователям разрешения доступа, аналогичные выданным им самим.

7. Запрещение доступа к объектам базы данных. Синтаксис системной хранимой процедуры (см. [1], стр. 258):

DENY     { ALL [ PRIVILEGES ] | permission [ ,...n ] }     {         [ ( column [ ,...n ] ) ] ON { table | view }         | ON { table | view } [ ( column [ ,...n ] ) ]         | ON { stored_procedure | extended_procedure }         | ON { user_defined_function }     } TO security_account [ ,...n ] [ CASCADE ]



DENY UPDATE

ON Заказ (ДатаЗаказа, СрокПоставки) TO [Гл.бухгалтер] CASCADE

GO



Примечание. Кроме предоставления и запрещения прав доступа существует еще и третье состояние – неявное отклонение доступа. Его можно рассматривать как отмену ранее выданных полномочий как по предоставлению, так и запрету доступа. Если ранее пользователю не было выдано никаких полномочий к объекту, то выполнять неявное отклонение доступа бессмысленно, т.к. оно установлено по умолчанию. Неявное отклонение доступа не мешает получить доступ к объекту на другом уровне, например, через членство в некоторой роли. Для неявного отклонения доступа используется команда REVOKE, имеющая следующий синтаксис (см. [1], стр. 259):

REVOKE [ GRANT OPTION FOR ]     { ALL [ PRIVILEGES ] | permission [ ,...n ] }     {         [ ( column [ ,...n ] ) ] ON { table | view }         | ON { table | view } [ ( column [ ,...n ] ) ]         | ON { stored_procedure | extended_procedure }         | ON { user_defined_function }     } { TO | FROM }     security_account [ ,...n ] [ CASCADE ] [ AS { group | role } ]

Раздел IV. Анализ полученных результатов

1. Сохраните созданный итоговый сценарий в файле D:\Work\X7230ХХХ\script.sql. с помощью команды меню File►Save (или соответствующей кнопки на панели инструментов). Далее закройте окно Query, содержащее сценарий script.sql.

2. Удалите базу данных Склад_ХХХ. Для этого в ее контекстном меню выберите команду Delete и затем в появившемся окне Delete Object установите флажок Close Existing Connections.

3. С помощью команды меню File►Open►File загрузите сценарий из файла D:\Work\X7230ХХХ\script.sql, после чего, нажав на панели инструментов кнопку Execute, создайте базу данных Склад_ХХХ заново.

4. Обновите данные на панели Object Explorer и сделайте базу данных Склад_ХХХ видимой.

5. Убедитесь, что с помощью сценария получена база данных Склад_ХХХ с требуемыми объектами и свойствами.

6. Проведите сравнительный анализ лабораторных работ 3 и 4, т.к. их выполнение привело к получению одного и того же результата – баз данных СкладХХХ и Склад_ХХХ соответственно. Укажите для каждого пункта, связанного с созданием объектов базы данных с помощью графического интерфейса (база данных СкладХХХ), соответствующую ему команду языка Transact-SQL и, в частности, какие ее фрагменты связаны с установкой тех или иных свойств конкретного объекта базы данных.

7. Удалите созданную базу данных Склад_ХХХ.

Лабораторная работа №5. Манипулирование данными с помощью команд языка Transact-sql

Цель работы

1. Изучить команды языка манипулирования данными.

2. Освоить основные команды языка определения данных.

Задачи

1. Выборка данных из таблиц и представлений.

2. Обновление данных в таблицах и представлениях.

3. Удаление данных из таблиц и представлений.

4. Изменение структуры таблицы.

5. Удаление таблиц из базы данных.

Методические указания

При помощи пользовательского меню Windows запустите утилиту SQL Server Management Studio, после чего на панели Object Explorer в древовидной структуре раскройте папку Databases.

С помощью команды меню File►Open►File загрузите сценарий из файла D:\Work\X7230ХХХ\script.sql в окно Query.

Выполните сценарий, нажав на панели инструментов кнопку Execute (или клавишу F5). В результате будет создана база данных Склад_ХХХ.

Обновите данные на панели Object Explorer. Для этого используйте команду Refresh в контекстном меню папку Databases или соответствующую кнопку в верхней части панели. В результате база данных Склад_ХХХ станет видимой на панели Object Explorer.

Закройте окно Query, содержащее сценарий script.sql. Затем на панели инструментов нажмите кнопку New Query, и откройте новое пустое окно Query, предназначенное для формирования нового сценария.

Готовые к исполнению команды (пакеты) языка Transact-SQL, из которых будет формироваться сценарий, выделены ниже при помощи стрелок  и .

Сделайте активной созданную базу данных Склад_ХХХ:



USE Склад_ХХХ

GO



Раздел I. Выборка данных из таблиц и представлений.

Синтаксис команды выборки данных из таблиц и/или представлений (см. [1], стр. 1158):

SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]

Опробуем различные варианты команды выборки данных:



/* выборка всех столбцов и всех строк таблицы Регион */

SELECT * FROM Регион

/* выборка некоторых столбцов и всех строк (вертикальный фильтр) */

SELECT Город, Адрес, Факс

FROM Регион

/* выборка всех столбцов и некоторых строк (горизонтальный фильтр) */

SELECT *

FROM Регион

WHERE Страна = 'Беларусь' AND Город != 'Минск'

/* выборка некоторых столбцов и некоторых строк (вертикальный

и горизонтальный фильтры) */

SELECT Город, Адрес, Факс

FROM Регион

WHERE Страна = 'Беларусь' AND Город != 'Минск'

/* выборка с сортировкой строк по столбцу Город, а при совпадении городов – по

столбцу Адрес */

SELECT *

FROM Регион

ORDER BY Город, Адрес

/* выборка из двух таблиц путем их внутреннего соединения по столбцу КодРегиона */

SELECT Поставщик.ИмяПоставщика, Регион.Город, Регион.Факс,

Поставщик.КодПоставщика

FROM Регион

INNER JOIN Поставщик ON Регион.КодРегиона =

Поставщик.КодРегиона

ORDER BY Поставщик.ИмяПоставщика

/* выборка данных из трех таблиц */

SELECT Клиент.ИмяКлиента, Регион.Город, Регион.Факс,

Заказ.Количество, Заказ.ДатаЗаказа

FROM Регион

INNER JOIN Клиент ON Регион.КодРегиона = Клиент.КодРегиона

INNER JOIN Заказ ON Клиент.КодКлиента = Заказ.КодКлиента

WHERE Заказ.Количество >= 2

ORDER BY Клиент.ИмяКлиента, Заказ.ДатаЗаказа DESC

/* та же операция выборка данных из трех таблиц с использованием псевдонимов таблиц */

SELECT К.ИмяКлиента, Р.Город, Р.Факс, З.Количество, З.ДатаЗаказа

FROM Регион Р

INNER JOIN Клиент К ON Р.КодРегиона = К.КодРегиона

INNER JOIN Заказ З ON К.КодКлиента = З.КодКлиента

WHERE З.Количество >= 2

ORDER BY К.ИмяКлиента, З.ДатаЗаказа DESC

/* в SQL Server допускается опускать имена псевдонимов таблиц для тех столбцов, имена которых уникальны в пределах объединяемых таблиц. Поэтому предыдущую операцию выборки данных можно записать еще и так: */

SELECT ИмяКлиента, Город, Факс, Количество, ДатаЗаказа

FROM Регион Р

INNER JOIN Клиент К ON Р.КодРегиона = К.КодРегиона

INNER JOIN Заказ З ON К.КодКлиента = З.КодКлиента

WHERE Количество >= 2

ORDER BY ИмяКлиента, ДатаЗаказа DESC