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

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

----------一个-------------------------------------------------------------------
alter proc sp_login
@sid int,
@pwd nvarchar(20)
as
declare @i int
set @i = (select count(1) from student where sid = @sid and pwd = @pwd)
return @i



--------------------------查看学生分数信息----------------------------------------------------------------------------------------------------
alter proc sp_viewstudent
@sid int,
@sum int output,
@avg int output,
@pid int output
as
declare cur cursor for select avg(score) p, sum(score) s,sid from score group by sid order by s desc
open cur
declare @p int, @s int, @sid2 int, @i int
set @i = 1
fetch from cur into @p, @s,@sid2
while @@fetch_status = 0
begin
if @sid2 = @sid
begin
set @pid = @i
set @sum = @s
set @avg = @p
end
set @i = @i + 1
fetch from cur into @p, @s,@sid2
end
close cur
deallocate cur

select cname,score from score a,course b
where a.cid = b.cid and a.sid = @sid


declare @avg int,@pid int,@sum int
exec sp_viewstudent 1003 ,@pid output,@sum output,@avg output
print @sum
print @avg
print @pid
-------------------------------------------------------------结束------------------------------------------------------
--使用存储过程添加新课程信息，并输入新课程的ID
alter proc sp_addCourse
@cname nvarchar(10)
as
insert into course values(@cname)
return @@identity

declare @i int
exec @i = sp_addCourse 'PHP'
print @i

select * from course

--实现数据表分页查询
--页码 = 总行数 / 每页行数 page
--每页行数 size
--总行数

--paixu linshibiao zhuanyonghanshu

select top 5 * from (select top (9-(2-1)*5) * from employees order by employeeid desc) a
order by a.employeeid


alter proc sp_page
@page int, --页码
@table varchar(10), --表名称
@orderby varchar(10), --排序字段名
@size int
as
declare @sql varchar(500) --放sql语句字符串
set @sql = 'declare @count int '
set @sql = @sql +' set @count =(select count(1) from ' + @table + ')'
set @sql = @sql + ' select top ' + str(@size) + ' * from (select top (@count - (' + convert(varchar(10),@page) + '-1)*'
+str(@size)+') * from ' + @table + ' order by ' + @orderby + ' desc) a ' +
'order by a.'+ @orderby
exec (@sql)

print @sql

exec sp_page 1,'employees','employeeid',3



--函数
declare @str varchar(10)
set @str = '1234'
declare @i int
set @i = 1234
--print len(@str)
print len(ltrim(str(@i)))

print substring(@str,1,2)
print floor(rand()*1000)

--
print getdate()--Datetime.Now
print dateadd(day,2,getdate())

print dateadd(day,2,'2011-10-20')--往后添加时间

print datediff(day,'2011-10-25','2012-12-10')--时间差 TimeSpan.days

print datepart(year,'2011-10-25')--获
取时间格式中的一部分（即年，月或日）
print datepart(month,'2011-10-25')
print datepart(day,'2011-10-25')
print convert(nvarchar(10),datepart(year,'2011-10-25')