--实验十一
select*from dorm
select *from srudent
select *from department
use student_data
go
if exists(select name from sysobjects
where name ='au_info'and type='P')
drop procedure au_info
go
create procedure au_info
as
select distinct tele from dorm
join srudent on dorm.dormno=srudent.dormno
join department on srudent.dno = department.dno
where department.dname='计算机系'
go
au_info
drop procedure au_info
go
--实验十三
select *from srudent
select *from dorm
use student_data
go
if exists(select name from sysobjects where name = 'A' and type = 'P')
drop procedure A
go
create procedure A
@_dormno nvarchar,
@newdormno nvarchar
as
begin
--update student set dormno = @newdormno where dormno = @_dormno
delete from dorm where dormno=@_dormno
end
go
--试
if exists (select dormno from dorm where dormno = 2101)
begin
print'删除成功'
end
else
begin
print'删除不成功'
end
go
select *from srudent where dormno = '2101'
select *from srudent where dormno = '2505'
A '2101','2505'
--
use student_data
go
if exists(select name from sysobjects where name = 'B' and type = 'P')
drop procedure B
go
create procedure B
@name nvarchar
as
begin
--删除成绩
delete from grade where sno=(select sno from srudent join department on srudent.dno=department.dno where dname=@name)
--删除学生
delete from srudent where dno= (select dno from department where department.DNAME = @name)
--删除系号
delete from department where DNAME=@name
end
go
--select sno from student join department on student.DNO=department.DNO where DNAME='物理系'
B '物理系'
select *from department
select *from srudent
--实验十二
use student_data
if exists(select name from sysobjects
where name='tr_up_north'and type='TR')
drop trigger tr_up_north
go
create trigger tr_up_north on srudent
for insert
as
declare @msg int
set @msg=(select sage From Inserted)
if (@msg<18)or(@msg>28)
begin
print '数据有误,请检查!'
rollback
end
insert into srudent values('990505','小贺','女',17,2,2404)
insert into srudent values('990606','小潇','女',20,2,2404)
select *from srudent where SNAME='小贺'
delete from srudent where SNAME='小贺'
select *from srudent where SNAME='小潇'
delete from srudent where SNAME='小潇'
--实验十四
begin transaction
use student_data
go
insert into srudent values('990901','何为','男',20,NULL,2505)
if exists (select sname from srudent where sname='何为')
begin
print'插入成功'
end
else
begin
print'插入不成功'
end
go
save transaction points1
go
delete from srudent where sname='何为'
if exists (select *from srudent where sname='何为')
begin
print'删除不成功'
end
else
begin
print'删除成功'
end
rollback transaction points1
if exists (select *from srudent where sname='何为')
begin
print'插入数据还在'
end
else
begin
print'插入数据不在'
end
SQL server 上机实验
最新推荐文章于 2023-01-28 22:46:13 发布