--------------------------------------------------------------------------  
-------------------------------存儲過程Study------------------------------  
--------------------------------------------------------------------------  
--刪除表  
drop table student  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--創(chuàng)建用戶信息數據表  
create table student  
(  
    id int identity(1,1),--遞增1,  
    stuNo varchar(50) primary key, --唯一鍵  
    stuName varchar(50),  
    stuAge datetime,  
    stuSex varchar(5)         
)  
  
--創(chuàng)建用戶分數數據表  
create table course  
(  
    id int identity(1,1),--遞增1,  
    stuNo varchar(50),  
    courseName varchar(50),  
    courseScore decimal   
)  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--插入數據  
--插入單條數據  
insert into student(stuNo,stuName,stuAge,stuSex) values('01','王男','1996-08-27 09:00:00.000','男')  
  
--多數量插入數據  
--SQL Server2008特有的插入  
insert into student values('02','楊冪','1995-4-20 6:0:0','女'),  
                          ('03','程峰','1988-9-17 15:30:0','男')  
                            
insert into course values('02','思想政治','85.5'),  
                         ('02','數學','70'),  
                         ('02','語文','80'),  
                         ('02','物理','90'),  
                         ('02','化學','65'),  
                         ('02','英語','96')  
                           
insert into course values('03','思想政治','60'),  
                         ('03','數學','65'),  
                         ('03','語文','84'),  
                         ('03','物理','70'),  
                         ('03','化學','76'),  
                         ('03','英語','54')                          
                            
  
--使用UNION ALL來進行插入  
insert into student   
select '04','wangan','1895-5-27 14:30:28','女'  
union all     
select '05','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=01  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程查詢所有數據  
--begin...end 類似編程語言中的{}  
create proc stu1  
as  
begin  
    select * from student;  
end  
go  
  
exec stu1  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程根據條件用戶名查詢用戶信息  
create proc stu2  
@sname varchar(50)   --聲明全局變量  
as  
begin  
    select * from student s where s.stuName=@sname;  
end  
go  
  
exec stu2 '王男'  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程內部設定用戶名查詢用戶信息  
create proc stu3  
@sname varchar(50)='王男'  
as   
begin  
    select * from student s where s.stuName=@sname;   
end  
go  
  
exec stu3   
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程根據用戶名查詢是否存在這個用戶信息  
create proc stu4  
@sname varchar(50),  
@result varchar(8) output --輸出參數  
as  
begin  
     if (select COUNT(1) from student s where s.stuName=@sname)>0  
     --if exists (select COUNT(1) from student s where s.stuName=@sname)  
     set   
        @result='存在!'  
     else  
     set   
        @result='不存在!'  
end  
go  
  
  
declare @result varchar(8)  
exec stu4 '王男1',@result output  
print @result  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程內部設定局部變量用戶名來查詢用戶信息  
create proc stu5  
as  
declare @sname varchar(50)  --局部變量聲明  
set @sname='楊冪'  
begin  
    select * from student s where s.stuName=@sname  
end  
go  
  
exec stu5  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程根據條件用戶學號查詢用戶名  
create proc stu6  
@stuNo varchar(50)  
as  
declare @sname varchar(50)  
set @sname=(select s.stuName from student s where s.stuNo=@stuNo)  
select @sname  
go  
  
exec stu6 '01'  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程插入用戶信息  
create proc stu7  
@stuNo varchar(50),  
@stuName varchar(50),  
@stuAge datetime,  
@stuSex varchar(5)  
as   
begin  
    insert into student   
        (stuNo,stuName,stuAge,stuSex)  
    values   
        (@stuNo,@stuName,@stuAge,@stuSex)   
end  
go  
  
exec stu7 '07','王莽','2000-9-9 9:9:9','女'  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程根據用戶名來刪除對應的用戶信息  
--@@rowcount返回操作條數  
--return返回信息，終止下面的操作  
create proc stu8  
@stuName varchar(50)  
as  
begin  
    delete from student where stuName=@stuName  
    return @@rowcount  
end  
go  
  
declare @result varchar(50)  
exec @result=stu8 '王莽'  
select @result as '刪除條數'  
--print @result  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程根據用戶學號來查詢他的平均分  
create proc stu9  
@stuNo varchar(50),  
@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 '02',@avg output  
print @avg  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程根據用戶學號來聯合查詢用戶信息和課程信息  
create proc stu10  
@stuNo varchar(50)  
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 '02'  
go  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程判斷學號是否存在，不存在，插入用戶信息，返回消息；存在，返回信息  
create proc stu11  
@stuNo varchar(50),  
@stuName varchar(50),  
@stuAge datetime,  
@stuSex varchar(5),  
@result varchar(50) 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(50)  
exec stu11 '06','王忠磊','1980-8-8 8:9:0','男',@result output  
print @result  
  
--------------------------------------------------------------------------  
--------------------------------------------------------------------------  
--存儲過程查詢當前用戶的平均成績與總的平均成績之間的關系  
create proc stu12  
@stuNo varchar(50)  
as  
declare @curAvg decimal(18,2)  
declare @totalAvg decimal(18,2)  
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(18),@totalAvg))  
        print ('該生的平均分:'+convert(varchar(18),@curAvg))  
        if @curAvg>@totalAvg  
            print '高于平均水平!'  
        else   
            print '低于平均水平!'   
    end  
else  
    print '該生對應的分數信息不存在,請重新查詢!'  
go  
  
exec stu12 '03'  
go