--------------------------------------------------------------------------

-------------------------------存储过程Study------------------------------

--------------------------------------------------------------------------

--删除表

drop table student

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--创建用户信息数据表

create table student

(

    id int identity(,),--递增1,

    stuNo varchar() primary key, --唯一键

    stuName varchar(),

    stuAge datetime,

    stuSex varchar()

)  

--创建用户分数数据表

create table course

(

    id int identity(,),--递增1,

    stuNo varchar(),

    courseName varchar(),

    courseScore decimal

)  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--插入数据

--插入单条数据

insert into student(stuNo,stuName,stuAge,stuSex) values('','王男','1996-08-27 09:00:00.000','男')  

--多数量插入数据

--SQL Server2008特有的插入

insert into student values('','杨幂','1995-4-20 6:0:0','女'),

                          ('','程峰','1988-9-17 15:30:0','男')  

insert into course values('','思想政治','85.5'),

                         ('','数学',''),

                         ('','语文',''),

                         ('','物理',''),

                         ('','化学',''),

                         ('','英语','')  

insert into course values('','思想政治',''),

                         ('','数学',''),

                         ('','语文',''),

                         ('','物理',''),

                         ('','化学',''),

                         ('','英语','')                          

--使用UNION ALL来进行插入

insert into student

select '','wangan','1895-5-27 14:30:28','女'

union all

select '','zhangnan','1990-1-20 19:0:0','女'

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--修改数据

update student set stuSex='男',stuAge='2016-5-9 8:0:0' where stuName='王男'

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--删除数据

delete from student where stuNo=

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程查询所有数据

--begin...end 类似编程语言中的{}

create proc stu1

as

begin

    select * from student;

end

go  

exec stu1

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程根据条件用户名查询用户信息

create proc stu2

@sname varchar()   --声明全局变量

as

begin

    select * from student s where s.stuName=@sname;

end

go  

exec stu2 '王男'

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程内部设定用户名查询用户信息

create proc stu3

@sname varchar()='王男'

as

begin

    select * from student s where s.stuName=@sname;

end

go  

exec stu3

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程根据用户名查询是否存在这个用户信息

create proc stu4

@sname varchar(),

@result varchar() output --输出参数

as

begin

     if (select COUNT() from student s where s.stuName=@sname)>

     --if exists (select COUNT() from student s where s.stuName=@sname)

     set

        @result='存在!'

     else

     set

        @result='不存在!'

end

go  

declare @result varchar()

exec stu4 '王男1',@result output

print @result

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程内部设定局部变量用户名来查询用户信息

create proc stu5

as

declare @sname varchar()  --局部变量声明

set @sname='杨幂'

begin

    select * from student s where s.stuName=@sname

end

go  

exec stu5

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程根据条件用户学号查询用户名

create proc stu6

@stuNo varchar()

as

declare @sname varchar()

set @sname=(select s.stuName from student s where s.stuNo=@stuNo)

select @sname

go  

exec stu6 ''

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程插入用户信息

create proc stu7

@stuNo varchar(),

@stuName varchar(),

@stuAge datetime,

@stuSex varchar()

as

begin

    insert into student

        (stuNo,stuName,stuAge,stuSex)

    values

        (@stuNo,@stuName,@stuAge,@stuSex)

end

go  

exec stu7 '','王莽','2000-9-9 9:9:9','女'

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程根据用户名来删除对应的用户信息

--@@rowcount返回操作条数

--return返回信息，终止下面的操作

create proc stu8

@stuName varchar()

as

begin

    delete from student where stuName=@stuName

    return @@rowcount

end

go  

declare @result varchar()

exec @result=stu8 '王莽'

select @result as '删除条数'

--print @result

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程根据用户学号来查询他的平均分

create proc stu9

@stuNo varchar(),

@avg int output

as

begin

    set @avg=(select AVG(courseScore) from course where stuNo=@stuNo)

    --等同

    --select @avg=AVG(courseScore) from course where stuNo=@stuNo

end

go  

declare @avg int

exec stu9 '',@avg output

print @avg

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程根据用户学号来联合查询用户信息和课程信息

create proc stu10

@stuNo varchar()

as

    select c.stuNo,s.stuName,s.stuAge,s.stuSex,c.courseName,c.courseScore from student s join course c on s.stuNo=c.stuNo where s.stuNo=@stuNo

go  

exec stu10 ''

go  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程判断学号是否存在，不存在，插入用户信息，返回消息；存在，返回信息

create proc stu11

@stuNo varchar(),

@stuName varchar(),

@stuAge datetime,

@stuSex varchar(),

@result varchar() output

as

    if exists (select * from student where stuNo=@stuNo)

        begin

            set @result='对不起，学号已存在!'

        end

    else

        begin

            insert into student

                (stuNo,stuName,stuAge,stuSex)

            values

                (@stuNo,@stuName,@stuAge,@stuSex)

            set @result='恭喜你，用户信息插入成功!'

        end

go  

declare @result varchar()

exec stu11 '','王忠磊','1980-8-8 8:9:0','男',@result output

print @result  

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--存储过程查询当前用户的平均成绩与总的平均成绩之间的关系

create proc stu12

@stuNo varchar()

as

declare @curAvg decimal(,)

declare @totalAvg decimal(,)

if exists(select * from course where stuNo=@stuNo)

    begin

        set @totalAvg=(select AVG(courseScore) from course)

        select @curAvg=AVG(courseScore) from course where stuNo=@stuNo

        print ('总的平均分:'+convert(varchar(),@totalAvg))

        print ('该生的平均分:'+convert(varchar(),@curAvg))

        if @curAvg>@totalAvg

            print '高于平均水平!'

        else

            print '低于平均水平!'

    end

else

    print '该生对应的分数信息不存在,请重新查询!'

go  

exec stu12 ''

go