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

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

nt.sid,student.sname,student.class,score.score
from student inner join score
on student.sid = score.sid
--三表链接
select student.sid,student.sname,ame,score.score
from score inner join student
on student.sid=score.sid
inner join course
on score.cid=course.cid
where student.sname = '张三'


--用右外表查询（以右表为基准）
select student.sid,student.sname,student.class,score.score
from student right outer join score
on student.sid=score.sid

select student.sid,student.sname,student.class,score.score
from student left outer join score
on student.sid=score.sid
where score.score is null

--交叉链接(笛卡尔集合)(少用)
select student.sid,student.sname,score.score
from student cross join score


--嵌套查询
--查询最高分的学生
select sid,sname
from student where sid in
(select sid from score where score
= (select max(score) from score))

--总分最高的学生学号
select sid from(
select top 1 sum(score) as s,sid from score group by sid order by s desc) a

--统计每门课程最高分的学生的学号
select score.sid,score.cid,score.score from score,

(select max(score) as m,cid from score group by cid) b
where score.cid=b.cid and score.score= b.m


--查班级平均分
select avg(c.score),c.class
from (select a.class,b.score
from student a,score b
where a.sid=b.sid) c group by class

--个人平均成绩
select avg(c.score),c.sid
from (select a.sid,b.score
from student a,score b
where a.sid=b.sid) c group by sid

--单科最高分的同学学号和姓名和班级

select distinct(d.sid),d.sname,d.class from (
select c.sid,c.sname,c.class from student c,(
select sid,score from score,(
select max(score) as s,cid from score group by cid) a
where a.s = score.score) b
where c.sid=b.sid) d



declare @i int --int i =0;
declare @s nvarchar(10) --string s;
set @i = 1; --i = 10;
--set @s = 'AAAAAAA' --s = 'AAAAAAA'

while @i<10 --while 循环
begin
print @i
set @i = @i + 1 --i++
end

select sid,score,case when cid=1 then 'C#' when cid=2 then 'English' end from score

select sid,cid,score,
case when score>59 then '及格' else '不及格' end
from score



--print @i --本地测试用print
--print @s
--
--if @i=0 --if条件语句
--begin
--print 'BBBBBBBBBBBBBBBBBBBBBBBB'
--end
--else
--begin
--print 'SSSSSSSSSSSSS'
--end


--使用存储过程判断用户登录信息
alter proc sp_login
@sid int, --输入参数
@pwd nvarchar(20), --输入参数
@s nvarchar(20) output --输出参数
as
declare @i int
set @i = (select count(1) from student where sid=@sid and pwd=@pwd )
--return @i
if @i = 1
set @s = '合法用户'
else
set @s = '非法用户'

declare @s nvarchar(20)
exec sp_login 1001,'123',@s output
pri
nt @s

if(select min(score) from score where sid=1001)>90
print '学生1001成绩全部优秀'
else if(select min(score) from score where sid = 1001)>