sql完整数据库操作、存储过程、登录判断,增删改(6)

完整的数据库代码,实现小型数据库的所有功能,包括登录判断,注入式攻击,存储过程。。。。。。。。。

结果数据。
select * from student

alter proc sp_Find
as
create table #table1
(
sid int,
sname nvarchar(10)
)
declare cur3 cursor for select sid,sname from student
open cur3
declare @sid int,@sname nvarchar(10),@count int
fetch from cur3 into @sid,@sname
while @@fetch_status = 0
begin
set @count = (select count(sid) from student where sname=@sname)
if @count > 1
insert into #table1 values(@sid,@sname)
--print convert(nvarchar(10),@sid)+' '+@sname
fetch from cur3 into @sid,@sname
end
close cur3
deallocate cur3
select * from #table1

exec sp_Find
--触发器
--级联删除学生信息
alter trigger t_delete on student for delete
as
begin
declare @sid int
set @sid = (select sid from deleted)
--print @sid
delete from score where sid = @sid
end

delete from student where sid = 1004

select * from student
select * from score

--添加学生信息,同时设置每人的考试成绩默认为0
alter trigger t_insert on student for insert
as
begin
declare @sid int
set @sid = (select sid from inserted)
declare cur cursor for select cid from course
open cur
declare @cid int
fetch from cur into @cid
while @@fetch_status = 0
begin
insert into score values(@sid,@cid,0)
fetch from cur into @cid
end
close cur
deallocate cur
end


insert into Student values(1030,'十几','男', '1992-5-5','三班','120')
select * from student
select * from score

--修改学生学号信息 101001 111001
alter trigger t_update on student for update
as
begin
declare @sid1 int,@sid2 int
set @sid1 = (select sid from deleted)
set @sid2 = (select sid from inserted)
update score set sid = @sid2 where sid=@sid1
end

update student set sid = 101001 where sid = 1001
select * from course

begin tran
begin try
delete from student where sid = 1002
end try
begin catch
rollback --回滚（撤销）
end catch
select * from student

--排序
declare @temp table(pid int identity(1,1),
s int,sid int)
insert into @temp select sum(score) s,sid from score group by sid
order by s desc
select pid from @temp where sid =1001