кос пм02 09.02. кос_ПМ02. Комплект контрольнооценочных средств по профессиональному модулю пм. 02 Разработка и администрирование баз данных для специальности 09. 02. 03 Программирование в компьютерных системах
 Скачать 0.57 Mb.
|
«Добавление, изменение, удаление записей в таблицах»Цель: «Сформировать навыки и умения построения запросов действий» Ход Работы Все запросы, рассмотренные ранее являются запросами на выборку. Добавление, изменение и удаление записей выполняется запросами действиями. Эти запросы не выводят данные на экран, а производят их изменение. Если необходимо просмотреть изменённые данные, то после запроса – действия , выполните запрос выборку. Язык SQL ориентирован на выполнение операций над группами записей, хотя в некоторых случаях операция может проводиться и над отдельной записью. Поэтому неудивительно, что операторы добавления, изменения и удаления записей в общем случае вызывают соответствующие операции над группами записей. 1.Оператор INSERT Оператор INSERТ применяется для добавления записей в объект. В качестве объекта может выступать таблица БД или просмотр (VIEW), созданный оператором CREATE VIEW. В последнем случае записи могут добавляться в несколько таблиц. Формат оператора INSERT: INSERT INTO <объект> [(столбец1[(, столбец2 ...] )] {VALUES «значение1> (, <значение2> ...)) | <оператор SELECT> } Список столбцов указывает столбцы, которым будут присвоены значения в добавляемых записях. Список столбцов может быть опущен. В этом случае подразумеваются все столбцы объекта, причем в том порядке, в котором они определены в данном объекте. Поставить в соответствие столбцам списки значений можно двумя способами. Первый состоит в явном указании значений после слова VALUES, второй - в формирован значений при помощи оператора SELECT. Явное указание списка значений: В этом случае оператор INSER Т при меняется для добавления одной записи и имеет формат INSERT INTO <объект> (столбец1 (, столбец2 ...)) VALUES «значение1> (, <значение2> ...) Значения назначаются столбцам по порядку следования тех и других в операторе: первому по порядку столбцу назначается первое значение, второму столбцу - второе значение и Т.д. Задание 1. Добавить в таблицу tovar новую запись: INSERT INTO tovar (kod_tov, zena, kod_post) VALUES (‘005’, 350, ‘12’) Поскольку столбцы таблицы RASHOD указаны в полном составе и именно в том порядке, в котором они перечислены при создании таблицы tovar оператором CREAТ TЕABLE, оператор можно упростить: INSERT INTO tovar VALUES (‘005’, 350, ‘12’) Указание значений при помощи оператора SELECT : INSERT INTO <объект> (столбец1 (, столбец2 ...)) <оператор SELECT> При этом значениями, которые присваиваются столбцам, являются значения, возвращаемые оператором SELECT. Порядок их назначения столбцам аналогичен предыдущей форме оператора INSERТ: значение первого по порядку столбца результирующего набора данных оператора SELECT присваивается первому столбцу оператора INSERT, второй - второму и Т.Д. Следует обратить внимание на важную особенность: поскольку оператор SELECT в общем случае возвращает множество записей, то и оператор INSERT в данной форме приведет к добавлению в объект аналогичного количества новых записей. Задание 2. Создайте таблицу itog, в которую добавляются записи, выбранные запросом. Создание таблицы: use sklad createtable itog( data varchar(15), kol int ) В эту таблицу добавляется записи за каждую дату с суммарным количеством поступившего товара. use sklad INSERTINTO itog SELECT data,sum (kol) FROM postavka groupby data 2.Оператор UPDAТЕ Оператор UPDAТЕ применяется для изменения значения в группе записей или - в частном случае - в одной записи объекта. В качестве объекта могут выступать ТБД или просмотр, созданный оператором CREAТЕ VIEW. В последнем случае могут изменяться значения записей из нескольких таблиц. Формат оператора UPDAТЕ: UPDAТЕ <объект> SET столбец1 = <значение1> (,столбец2 = <значение2>...) (WНЕRE <условие поиска > ) При корректировке каждому из перечисленных столбцов присваивается соответствующее значение. Корректировка выполняется для всех записей, удовлетворяющих условию поиска. Условие поиска задается так же, как в операторе SELECT Задание 3. Увеличьте цену товара на 10% для поставщика с кодом ‘11’ use sklad select * from tovar where kod_post='11' update tovar set zena=zena+50 where kod_post='11' select * from tovar where kod_post='11' Перед выполнением запроса – действия запускается запрос на выборку, чтобы посмотреть состав данных перед обновлением. После выполнения обновления снова выведем данные для просмотра. 3.Оператор DELETE Оператор DELETE предназначен для удаления группы записей из объекта. В качестве объекта могут выступать ТБД или просмотр, созданный оператор CREATE VIEW.. Формат оператора DELETE: DELETE FROM <объект> [WHERE <условие поиска>]; Удаляются все записи из объекта, удовлетворяющие условию Условие поиска задается так же, как в операторе SELECT. Задание 4.Удалите из таблицы расход все записи за 2 октября. use sklad select * from rashod deletefrom rashod where data = '2 октября' select * from rashod Перед выполнением запроса – действия запускается запрос на выборку, чтобы посмотреть состав данных перед обновлением. После выполнения обновления снова выведем данные для просмотра. Контрольные задания. В таблицу ‘поставщик’ добавьте новую запись о поставщике с кодом «17», «Лесная ягода» Создайте таблицу ‘лучший товар’ с полями kod_tov, kol. Выполните последовательность команд: удалите все имеющиеся записи из таблицы ‘лучший товар’; добавьте в таблицу ‘лучший товар’ код товара и его количество, который больше всего был отпущен со складов (по таблице rashod, вложенный запрос на нахождение максимума); выведите на экран содержимое таблицы ‘лучший товар’. Увеличьте количество поставленного товара на 5 единиц для номера поставки ‘04’. Выведите содержимое таблице до обновления и после обновления. В таблицу Rashod добавьте новую запись о продаже товара с кодом ‘001’. Выведите содержимое таблице до добавления и после добавления записи. Из таблицы Rashod удалите все записи с количеством товара меньше 15. Выведите содержимое таблице до удаления и после удаления. В индивидуальной базе данных выполните примеры на добаление, удаление и обновление записей. Практическая работа №10 «Создание триггеров в ИС «Учебный процесс» для поддержания целостности данных» Цель: «Сформировать навыки и умения реализации триггеров для поддержания целостности данных» Ход Работы 1. Определение триггера Триггер – это хранимая процедура особого типа, вызываемая на выполнение в ответ на определённые события. Триггеры подразделяются на два основных типа: триггеры языка определения данных ( DDL) и триггеры языка манипулирования данными ( DML) Триггеры DDL активизируются в ответ на внесение каких-либо изменений в структуру б.д пользователями ( создание, удаление, изменение структуры таблиц,). Триггеры DML представляют собой фрагменты кода, которые закрепляются за конкретной таблицей. В отличие от хранимых процедур, при использовании которых необходимо явно вызывать на выполнение определённый код, триггеры вызываются на выполнение автоматически при обнаружении события, связанного с ним. Триггеры не получают параметров и не возвращают значений. Конструкция FOR ( или AFTER) позволяет указать какое действие приводит к запуску триггера. Триггер |Insert этот триггер вызывается на выполнение каждый раз, когда вставляется новая строка в таблицу, за которой закреплён триггер. Для каждой новой строки создаётся её копия и вставляется в таблицу INSERTED. Эта таблица сохраняется до тех пор пока действует триггер ( с момента запуска до его завершения). Триггер |Delete вызывается на выполнение каждый раз, при удалении записи. Копия удаляемой записи помещается во временную таблицу Deleted. Триггер |Update вызывается на выполнение при обновлении записи таблицы, для которой он создан. операция модификации строки трактуется как удаление старой версии строки ( помещение её в Deleted) и добавление новой строки ( помещение её в INSERTED). При этом триггеры на удаление и добавление не запускаются. 2. Условный оператор для триггера в языке SQL-Transact
Создадим триггеры на удаление для таблиц со стороны мощности ‘многие’ в схеме данных «Учебный процесс» Чтобы ввести код триггера для события «удаление» данных в таблице kafedra, щёлкните по изображению + . В появившемся списке выберите папку Triggers . Выделите её правой кнопкой мыши и из контекстного меню выберите New Trigger. В представленном шаблоне введите код create trigger del_kafedra on kafedra for delete as declare @kk varchar(3) begin select @kk=kkaf from deleted delete from prepodavatel where prepodavatel.kkaf=@kk end Запустите его на выполнение. Для того чтобы просмотреть код откомпеллированного триггера нужно для таблицы kafedra выбрать подраздел Triggers. При работе с таблицей kafedra во время удаления текущей записи, все записи об этой кафедре в таблице prepodavatel будут удалены. Задание 1. Триггер для события «удаление» данных в таблице prepodavatel create trigger del_prepodavatel on prepodavatel for delete as declare @tn varchar(3) begin select @tn=tabn from deleted delete from isuchenie where isuchenie.tabn=@tn end При работе с таблицей prepodavatel во время удаления текущей записи, все записи об этом преподавателе в таблице isuchenie будут удалены. Задание 2. Триггер для события «удаление» данных в таблице predmet create trigger del_predmet on predmet for delete as declare @kp varchar(3) begin select @kp=kp from deleted delete from isuchenie where isuchenie.kp=@kp end При работе с таблицей predmet во время удаления текущей записи, все записи об этом предмете в таблице isuchenie будут удалены. Задание 3. Триггер для события «удаление» данных в таблице isuchenie create trigger del_isuchenie on isuchenie for delete as declare @kp varchar(3) declare @tn varchar(3) declare @ng varchar(3) declare @vidz varchar(3) begin select @kp=kp, @tn=tabn, @ng=ng, @vidz=vidz from deleted delete from uspevaemost where kp=@kp and tabn=@tn and vidz=@vidz and ng=@ng end Если в созданный триггер необходимо внести изменения, то нужно выбрать таблицу, для которой он создавался, перейти к подразделу Triggers, выделить его и из контекстного меню выбрать Modify. Триггер отобразится в окне редактирования. После внесения изменений на панели выбрать кнопку Execute. Контрольные задания создать триггер на удаление для таблицы gruppa. создать триггер на удаление для таблицы student. создать триггер на удаление для таблицы uspevaemost. В теле триггера выполняется следующий код: при удалении студента из таблицы uspevaemost в таблице gruppa изменяется pball для группы студента. создать триггер на обновление для таблицы uspevaemost. В теле триггера выполняется следующий код: при изменении оценки студента в таблице uspevaemost его pball в таблице student обновляется Практическая работа №11 «Создание триггеров в ИС «Учебный процесс» для поддержания целостности данных» Цель: «Сформировать навыки и умения реализации триггеров, реализующих бизнес - правила» Ход Работы Создадим таблицу “svodnaj”, которая содержит количество всех оценок из таблицы «Успеваемость». CREATE TABLE svodnaj ( Ozenka int not null, Kol int not null, Primary key (Ozenka) ) .1 добавлении новой записи При добавлении новой записи в «Успеваемость» ( Триггер на добавление для «Успеваемость») просматривается таблица “svodnaj”, если строка с такой оценкой уже есть, то изменяется её количество До вставки
После вставки
В противном случае в таблицу “svodnaj” добавляется новая строка с этой оценкой и количеством: 1 svodnaj
Пример триггера CREATE trigger InsUspevaemost On uspevaemost for INSERT as declare @k int begin select @k=ozenka from inserted if@k>1 AND @k<=5 begin IF exists (select s.ozenka from svodnaj s where s.ozenka=@k )// УЖЕ ЕСТЬ UPDATE svodnaj set kol=kol+1 where svodnaj.ozenka=@k else insert into svodnaj ( ozenka,kol) values (@k, 1) end else BEGIN RAISERROR ('ENTER OZENKA ' ,16,1) ROLLBACK TRAN END End 2 Удалениезаписи При удалении записи в «Успеваемости» ( Триггер на удаление для «Успеваемость»), в таблице “svodnaj” для удаляемой оценки уменьшается количество До удаления Удаление из «Успеваемости»
После удаления
Пример триггера create trigger DelUspevaemost on uspevaemost for delete as declare @oz int declare @k int begin select @oz=ozenka from deleted select @k=kol from svodnaj where ozenka=@oz UPDATE svodnaj set kol=kol-1 where ozenka=@oz if @k=1 delete from svodnaj where ozenka=@oz end 3 Обновление записи При обновлении записи в «Успеваемости» ( Триггер на обновление для «Успеваемость»), в таблице “svodnaj” для удаляемой оценки изменяется количество До обновления После обновления
Старое значение «Успеваемости»
Новое значение «Успеваемости»
Пример триггера create trigger [UpdUspevaemost] on [uspevaemost] for update as declare @prev_oz int declare @new_oz int declare @k int begin select @prev_oz=ozenka from deleted select @new_oz=ozenka from inserted if @prev_oz<>@new_oz begin // изменение старой оценки: как при удалении select @k=kol from svodnaj where ozenka=@prev_oz UPDATE svodnaj set kol=kol-1 where ozenka=@prev_oz if @k=1 delete from svodnaj where ozenka=@prev_oz // изменение новой оценки: как при добавлении if @new_oz>1 AND @new_oz<=5 begin IF exists (select s.ozenka from svodnaj s where s.ozenka=@new_oz ) UPDATE svodnaj set kol=kol+1 where svodnaj.ozenka=@new_oz else insert into svodnaj ( ozenka,kol) values (@new_oz, 1) end else BEGIN RAISERROR ('ENTER OZENKA ' ,16,1) ROLLBACK TRAN END end end Контрольные задания Создать триггер на удаление в индивидуальной базе данных для всех таблиц со стороны «одного» в мощности отношений. Создать триггер на обновление в индивидуальной базе данных для всех таблиц со стороны «одного» в мощности отношений. Создать триггер на добавление записи в индивидуальной базе данных. Практическая работа №12 «Хранимые процедуры в SQL Server» Цель: «познакомить студентов с понятием хранимая процедура, рассмотреть основные методы их создания» Ход Работы Хранимая процедура (stored procedure) – это именованный набор команд Transact-SQL, хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базу данных. Она существует независимо от таблиц или каких-либо других объектов баз данных. Хранимая процедура может быть вызвана клиентской программой, другой хранимой процедурой или триггером. Возможно управлять правами доступа пользователей к хранимы процедурам. Прежде чем выполнить хранимую процедуру, сервер генерирует для неё так называемый план исполнения (execution plan), выполняет её оптимизацию и компиляцию. Выполняется кэширование плана исполнения процедуры, а также оптимизированного компилирования кода. Использование хранимых процедур реализует принцип модульного проектирования. 1. Типы хранимых процедур Системные хранимые процедуры (system stored procedures) – это хранимые процедуры, поставляемые в составе SQL Server 2000. Предназначены для выполнения различных административных действий. Такие процедуры имеют префикс sp_ . Они хранятся в база данных master. Пользовательские хранимые процедуры (user-defined stored procedures) – это процедуры созданные пользователями, реализующие те или иные действия (полноценный объект баз данных). Следствием этого является то, что каждая хранимая процедура хранится в конкретной базе данных. Временные хранимые процедуры (temporary stored procedures) – эти процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Бывают локальные и глобальные. Локальные временные хранимые процедуры(local temporary stored procedure) – могут быть вызваны только из того соединения, в котором они были созданы. При создании такой процедуры необходимо дать ей имя, начинающееся символом #. Они хранятся в база данных tempdb и автоматически удаляются при отключении пользователя. 2. Синтаксис процедуры Для создания хранимой процедуры на языке Transact-SQL используется SQL-оператор CREATE PROCEDURE Синтаксис данного оператора для MS SQL Server 2000 CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS Аргументы: procedure_name – имя создаваемой процедуры. Используются префиксы sp_, #, ## (значение указано выше). Как видно из синтаксиса, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, в которой должна быть размещена процедура. Чтобы разместить хранимую процедуру в конкретной базе данных нужно создать её в контексте этой базы данных. ;number – идентификационный номер хранимой процедуры, однозначно определяющий её в группе процедур. На пример: orderproc;1, orderproc;2. Процедура orderproc объединяет в себе две процедуры. При вызове DROP PROCEDURE orderproc, будут удалены все процедуры этой группы. @parameter – имя параметра, который будет использоваться создаваемой хранимой процедурой для передачи входных или выходных данных. Имена параметров должны начинаться с символа @. В одной хранимой процедуре может использоваться до 1024 параметров. data_type – тип данных, который будет иметь соответствующий параметр хранимой процедуры. Можно использовать все типы данных, включая text, ntext и image и пользовательские типа данных. Однако, тип данных cursor может использоваться только как выходной параметр (с указанием ключевого слова OUTPUT). VARYING – ключевое слово, которое используется совместно с параметром OUTPUT, имеющим тип данных cursor. В качестве выходного параметра будет представлено результирующее множество. default – значение, которое будет принимать соответствующий параметр по умолчанию. При вызове процедуры, явно можно будет не указывать значение соответствующего параметра. Будет использовано значение, созданное с помощью этого параметра. OUTPUT – его наличие указывает, что параметр предназначается для возвращения данных из хранимой процедуры. Но этот параметр также может использоваться и для передачи значений в процедуру. Значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Нельзя использовать выражения и константы, допустимые для обычных параметров. n – количество определённых параметров. {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} RECOMPILE - Указывает, что SQL Server должен создавать план выполнения хранимой процедуры при каждом её вызове. ENCRYPTION - Указывает, что SQL Server должен выполнить кодирование хранимой процедуры. Обратите внимание, сохранённые процедуры, созданные с выбором ENCRYPTION не могут рассматриваться с sp_helptext. FOR REPLICATION – используется при репликации данных и включение создаваемой хранимой процедуры в качестве статьи в публикацию. Этот параметр не может быть использован совместно с параметром RECOMPILE. AS – ключевое слово, свидетельствующее о начале тела процедуры 3. Вызов хранимой процедуры Для вызова хранимой процедуры используется оператор EXECUTE. Синтаксис оператора: EXEC procedure_name [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } ] Указывая ключевое слово OUTPUT, вы предписываете присвоить соответствующей локальной переменной (внешней по отношению к процедуре) при завершении хранимой процедуры значение соответствующего параметра. При этом значения параметров могут изменяться в ходе процедуры. DROP PROCEDURE – удаляет процедуру с сервера. Задание 1. Создать процедуру, которая выводит среднюю оценку по каждому предмету. Функция не принимает и не возвращает параметров Выберите базу uch_proc , откройте папку Programmbility, выберите Stored Procedures. Из контекстного меню для Stored Procedures выберите New Stored Procedure. CREATE PROCEDURE sred_ozenka AS BEGIN SELECT kp, avg(ozenka) from uspevaemost group by kp END Для компиляции нажмите !Execute. Процедура появится в списке. Для выполнения процедуры в редакторе запросов введите команду Exec sred_ozenka. Задание 2. Создать процедуру, которая выводит среднюю оценку по предмету, переданному через параметр CREATE PROCEDURE sred_ozenka_pred @pred varchar(20) AS BEGIN SELECT avg(uspevaemost.ozenka) from uspevaemost, isuchenie, predmet where uspevaemost.ng=isuchenie.ng and uspevaemost.kp=isuchenie.kp and uspevaemost.tabn=isuchenie.tabn and uspevaemost.vidz=isuchenie.vidz and isuchenie.kp= predmet.kp and predmet.np=@pred END Для выполнения процедуры в редакторе запросов введите команду Exec sred_ozenka_pred 'информатика' Задание 3. Создать процедуру, которая выводит через выходной параметр фамилию студента, который получал наибольшее количество двоек в группе, переданной через входной параметр. Итак, процедура будет иметь один входной и один выходной параметр. Около выходного параметра указывается ключевое слово output CREATE PROCEDURE neud @ngr varchar(3) , входной параметр – номер группы @fio_s varchar(20) output выходной параметр AS BEGIN SELECT @fio_s=fio from student where ng=@ngr and ns in( SELECT ns from uspevaemost group by ns, ozenka having ozenka=2 and count(kp)>= all ( SELECT count(kp) from uspevaemost group by ns, ozenka, ng having ozenka=2 and ng=@ngr) ) END Для выполнения процедуры в редакторе запросов введите команду use uch_proc declare @f varchar (20) exec neud '101', @fio_s=@f output print @f Задание 4. Создать процедуру, которая выводит коды предметов и вид занятий, по которым получал двойки самый неуспевающий студент указанной группы. Самый неуспевающий студент определяется процедурой neud . Т. е. создаваемая процедура будет вызывать neud CREATE PROCEDURE neud_pred @ngg varchar(3) AS declare @f varchar (20) BEGIN exec neud @ngg, @fio_s=@f output // вызов процедуры neud select @f // вывод фамилии неуспевающего // вывод кода предмета и вида занятия,по которым он получил 2 select uspevaemost.kp, uspevaemost.vidz from uspevaemost, student where uspevaemost.ng= student.ng and uspevaemost.ns= student.ns and student.fio=@f END Для выполнения процедуры в редакторе запросов введите команду use uch_proc exec neud_pred '101' Контрольные задания Создать процедуру, которая в базе данных ‘sklad’определяет количество товара в таблице ‘tovar’. Создать процедуру, которая по входному параметру наименование поставщика выводит наименование, код, цену товара. Создать процедуру, у которой два параметра: входной параметр - наименование товара, выходной – цена товара. По входному параметру определить цену товара. Создать процедуру_2, которая выводит товар и его цену большую средней цены. Среднюю цену возвращает процедура_1 ( как в задании 4). База данных Учебный процесс. Создать процедуру, которая по входному параметру номер группы определяет количество положительных оценок ( выходной параметр) База данных Учебный процесс. Создать процедуру, которая через выходной параметр возвращает наименование предмета с максимальным количеством часов. Входного параметра нет. База данных Учебный процесс. Создать процедуру, которая по входному параметру фамилия студента, возвращает номер группы. Сформулируйте и выполните процедуры в индивидуальной базе данных ( без параметров, с входным и выходным параметром) Практическая работа №13 «Создание запросов с использованием внешнего соединения» Цель: «выявить отличия внешнего и внутреннего соединений, создать запросы для внешних соединений» Ход Работы При внутреннем соединении, рассмотренном ранее, таблицы связывались в условном операторе предложением: <имя столбца таблицы 1> = < имя столбца таблицы 2> Например: отобразить фамилии преподавателей с кафедры ‘информатика’ Select prepodavatel.fio From kafedra, prepodavatel Where kafedra.kkaf = prepodavatel.kkaf and kafedra.nkaf=‘информатика’ В этом случае осуществляется декартово произведение таблиц kafedra, prepodavatel и из полученного набора данных отбираются записи, удовлетворяющие условию поиска (kafedra.kkaf = prepodavatel.kkaf) Существует и другой вид соединения таблиц – внешнее соединение. Оно определяется предложением From согласно спецификации Select * from <таблица 1> <вид соединения> JOIN < таблица 2> ON <условие поиска> Внешнее соединение похоже на внутреннее, но в результирующий набор включаются записи ведущей таблицы соединения, которые объединяются с пустым множеством записей другой таблицы. Какая из таблиц будет ведущей определяет вид соединения: Left - ( левое внешнее соединение), когда ведущей является таблица1 (расположенная слева от вида соединения); Right – (правое внешнее соединение), когда ведущей является таблица2 (расположенная справа от вида соединения); Задание 1: создайте две таблицы
Заполните данными эти таблицы.
Тогда выполнение оператора Select, реализующего внешнее левое соединение Select tab1.Р1, tab1.Р2, tab2.Р4 from tab1 Left JOIN tab2 ON tab1.Р2= tab2.Р2 Приведёт к выдаче результирующего набора
Пунктиром показаны столбцы ведущей таблицы tab1. Как видно для записи таблицы tab1, где столбец tab1.Р1 имеет значение «d», нет парных записей в таблице tab2, для которых бы удовлетворялось условие поиска tab1.Р2= tab2.Р2. Поэтому данная запись таблицы tab1 показана в соединении с пустой записью. В то же время, выполнение оператора Select, реализующего внешнее правое соединение Select tab1.Р1, tab1.Р2, tab2.Р4 from tab1 Right JOIN tab2 ON tab1.Р2= tab2.Р2 Приведёт к выдаче результирующего набора
Пунктиром показаны столбцы ведущей таблицы tab2. Как видно, для записи таблицы tab2, где столбец tab2.Р1 имеет значение ’z’ и столбец tab2.Р2 имеет значение «2», нет парных записей в таблице tab1, для которых бы удовлетворялось условие поиска tab1.Р2= tab2.Р2. Поэтому данная запись таблицы tab2 показана в соединении с пустой записью. Задание 2:Построить внешнее соединение по таблице uspevaemost с таблицей student, т.е. показать студента, соответствующего каждой оценке. use uch_proc Select uspevaemost.ozenka,uspevaemost.kp, student.ns, student.ng from uspevaemost Left JOIN student ON uspevaemost.ns=student.ns and uspevaemost.ng=student.ng Или use uch_proc Select uspevaemost.ozenka,uspevaemost.kp, student.ns, student.ng from student RightJOIN uspevaemost ON uspevaemost.ns=student.ns and uspevaemost.ng=student.ng Результат запроса: Ozenka kp ns ng 3 01 01 101 4 01 01 101 3 03 01 101 Задание3:Построить внешнее соединение по таблице student с таблицей uspevaemost, т.е. показать все оценки по каждому студенту. use uch_proc Select uspevaemost.ozenka,uspevaemost.kp, student.ns, student.ng from student LeftJOIN uspevaemost ON uspevaemost.ns=student.ns and uspevaemost.ng=student.ng Или use uch_proc Select uspevaemost.ozenka,uspevaemost.kp, student.ns, student.ng from uspevaemost RightJOIN student ON uspevaemost.ns=student.ns and uspevaemost.ng=student.ng Результат запроса: Ozenka kp ns ng 3 01 01 101 4 01 01 101 3 03 01 101 NULL NULL 03 102 NULL NULL 01 103 NULL NULL 02 103 NULL NULL 01 104 Задание 4. Используя внутреннее соединение между таблицами student ,isuchenie, predmet и условия существования хотябы одной записи (exists ) в наборе данных, вывести фамилии студентов, которые не получали оценок по изучаемым предметам. use uch_proc Select student.fio, student.ng , predmet.np,isuchenie.vidz from student ,isuchenie, predmet where isuchenie.ng=student.ng and predmet.kp=isuchenie.kp and not exists( select uspevaemost.ns from uspevaemost where uspevaemost.ns=student.ns and uspevaemost.kp=isuchenie.kp and isuchenie.vidz=uspevaemost.vidz ) Аристов 101 математика лек Аристов 101 математика пр Аристов 101 история лек Аристов 101 ин яз пр Аристов 101 философия лек . Контрольные задания Построить внешнее соединение по таблице kafedra с таблицей prepodsavatel, т.е. показать состав каждой кафедры. Построить левое внешнее соединение и правое соединение. Построить внешнее соединение по таблице predmet с таблицей isuchenie, которое отобразит в каких группах и с каим количеством часов преподаются предметы. Построить левое внешнее соединение и правое соединение Построить внешнее соединение по таблице gruppa с таблицей student, которое покажет состав групп. Построить левое внешнее соединение и правое соединение. Сформулировать и выполнить запросы в индивидуальной базе данных с использованием левого внешнего соединения. Сформулировать и выполнить запросы в индивидуальной базе данных с использованием правого внешнего соединения. Практическая работа №14 «Транзакции. Уровни изоляций транзакций. Резервирование данных» Цель: «познакомить студентов с основными принципами администрирования баз данных в SQL Server, изучить основные приемы при резервировании баз данных и журналов транзакций, а также их восстановления» Ход Работы 1.Уровни изоляции транзакций Разработчику предоставляется возможность выбора уровня изоляции транзакций: READCOMMITTED(значение, применяемое по умолчанию) READUNCOMMITTED SERIALIZABLE Для выбора конкретного уровня изоляции транзакций применяется оператор: SET TRANSACTION ISOLATION LEVEL < READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE > Опция READCOMMITTED При использовании этой опции все созданные разделяемые блокировки автоматически освобождаются после завершения выполнения оператора, в котором они были созданы. Задание 1 “уровень изоляции транзакции «READCOMMITTED» “ Установите соединение с базой данных «Учебный процесс». В окне редактора нового запроса ( New Query) введите команду начала транзакции и обновления записи для группы 101 begin tran use uch_proc update gruppa set kol=35 where ng='101' выполните запрос (! Execute ). Запись обновляется, но транзакция не завершена. Транзакция блокирует запись. В новом окне введите код запроса для отображения записи из таблицы «gruppa» с номером '101'. use uch_proc select * from gruppa where ng='101' Внизу на панели будет отображаться процесс «зависания» выполнения запроса: Executing query. При обновлении на запись была установлена «исключительная блокировка», которая не даёт возможность выполнится «разделяемой блокировке» чтения данных. Если в новом окне редактора запросов ввести код, отображающий информацию о группах кроме 101, то он будет выполнен. use uch_proc select * from gruppa where ng<>'101' Результат ng kol pbal 102 32 4,5 103 29 4,8 104 35 4,4 105 45 4,8 . . Все остальные записи таблицы оказались не заблокированы. В окне, где запускалась транзакция завершите её подтверждением : commit tran. После этого код запроса для отображения записи из таблицы «gruppa» с номером '101' будет выполнен. Результат ng kol pbal 101 35 4,5 Задание 2 “уровень изоляции транзакции «READUNCOMMITTED» “ В окне редактора нового запроса ( New Query) введите команду начала транзакции и обновления записи для группы 101 Транзакция № 1 begin tran use uch_proc update gruppa set kol=32 where ng='101' выполните запрос (! Execute ). Запись обновляется, но транзакция не завершена. В новом окне введите код запроса для отображения записи из таблицы «gruppa» с номером '101'. Для выборки данных запустим транзакцию с уровнем изоляции READUNCOMMITTED Транзакция №2 set transaction isolation level read uncommitted begin tran use uch_proc select * from gruppa where ng='101' ng kol pbal 101 32 4,5 102 32 4,5 103 29 4,8 . . Транзакции с этим уровнем изоляции удалось прочитать «незафиксированные» данные. В окне начала транзакции на обновление введём команду «отката» изменений: rollback tran. Просмотрим данные из таблицы «группа» после отката транзакции ng kol pbal 101 35 4,5 102 32 4,5 103 29 4,8 . 2. Резервирование данных и журналов транзакций Одни из лучших способов управления резервной копией информации предоставляется SQL Server Enterprise Manager. Для начала работы с системой резервирования выберите базу данных, которую требуется сохранить, правой кнопкой мыши и выполните команду Tasks | Back Up. Появится диалоговое окно SQL Server Backup, где расположены вкладки General и Options. Основными видами резервирования в SQL Server являются: - Full (полная копия базы данных). В этом случае сохраняются любые действия над базой данных, производимые в процессе копирования, все незафиксированные транзакции и, естественно, все данные. Для создания первой копии необходимо выбрать именно этот вариант. - Differential (дифференциальная копия). В этом случае SQL Server сохраняет только те части базы данных, которые изменились со времени создания полной копии и имеющиеся в журнале незафиксированные транзакции. В этом случае резервное копирование производится быстрее, чем в предыдущем, но полная копия должна существовать. - Transaction log (копия журнала транзакций). В этом случае сохраняются все изменения, произошедшие в базе данных. Для эффективной работы с базой данных, спроектированной в SQL Server, обязательно должны резервироваться следующие объекты: главная база данных; все базы данных, информацию из которых нельзя потерять; все журналы транзакций для каждой базы данных, которые работают в системе или подвергаются тестированию. Если база данных была повреждена в силу некорректных действий пользователя или вследствие сбоев в работе аппаратной и программной части системы, то необходимо провести восстановление системы. Основными действиями для её восстановления являются: восстановление главной базы данных; восстановление последней полной копии базы данных, если она была сделана; восстановление журналов транзакций, которые были зарезервированы пользователем, начиная с момента получения последней резервной/ Задание 3. Создать резервную копию базы данных ‘sklad’. В выберите базу данных ‘sklad’, для которой нужно выполнить резервное копирование. Выполните последовательность действий: Tasks → Back Up. В результате чего на экран будет выведено диалоговое окно SQL Server Backup, содержащее вкладки General и Options. В поле Database укажите имя базы данных, подлежащей резервированию; в поле Name – имя ее резервной копии; Description – задается описание, использование которого облегчит работу с резервируемой базой данных. С помощью клавиши Tab перейдите в область Backup type и укажите тип создаваемой резервной копии (Full, Differential, Transaction log ). Затем активизируйте область окна Destination, в которой указывается область диска или диск для размещения резервной копии базы данных. Нажмите кнопку Add в группе Destination. В диалоговом окне Select Backup Destination определите месторасположения резервной копии базы данных: в отдельном файле или на внешнем устройстве. Для сохранения резервной копии в отдельном файле достаточно указать полный путь доступа к нему; во втором случае – указывается и тип носителя, на котором будет сохраняться копия, и новое имя файла. Для возврата в диалоговое окно SQL Server Backup достаточно нажать кнопку ОК.  Рис 17 в диалоговое окно SQL Server Backup Задание 4 Выполнить восстановления базы данных ‘sklad’. В начале операции восстановления базы данных выполните следующую последовательность команд: Tasks → Restore ->Database. После чего на экран будет выведено диалоговое окно Restore Database, которое также содержит вкладки General и Options. Затем из предлагаемого списка выберите базу данных для восстановления. После заполнения диалогового окна Restore Database нажмите на кнопку ОК. Если все параметры для восстановления базы данных указаны верно, то на экран будет выведено окно сообщений, в котором отображается процесс восстановления базы данных. Контрольные задания Выполните резервное копирование индивидуальной базы данных. Выполните восстановление индивидуальной базы данных. В индивидуальной базе данных запустите транзакцию с уровнем изоляции «READCOMMITTED» В индивидуальной базе данных запустите транзакцию с уровнем изоляции READUNCOMMITTED |
