use master; --切换到master数据库 go create database stuDB go use stuDB --选择当前数据库 go CREATE TABLE StuInfo --创建学生信息表 ( StuNo varchar(12) primary key, StuName varchar(20) not null , StuAge int check(StuAge>=0 and StuAge<=120), StuSex char(2) check(StuSex='男' or StuSex='女') default('男'), StuTel varchar(15), StuAddress varchar(50) default ('地址不详'), StuEmail varchar(50), birthday date, classname varchar(12) ) go CREATE TABLE Exam --创建学生成绩表 ( ExamNo int primary key identity(1,1), StuNo varchar(12) foreign key references StuInfo(StuNo), Written decimal(10,2) check(Written>=0 and Written<=100), Lab decimal(10,2) check(Lab>=0 and Lab<=100) ) go
--插入数据 insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13580901028','周文学','90-03-17','2222273','湖南衡阳','Y2T116'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550715006','陈辉煌','82-10-10','13588912530','江西全南县','Y2T48'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550808001','曾华军','86-08-22','13583020539','广东珠海','Y2T49'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13551219034','罗燕','83-11-07','13578696856','云南省潞西市','Y2T65'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550815003','黄志伟','87-11-13','13643088892','江西会昌县','Y2T50'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550815004','涂乾文','86-07-22','2228852','广东珠海','Y2T50'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550815015','唐宁','81-07-01','13812309868','广东珠海','Y2T50'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550826003','贝然','85-06-13','13882202173','广东珠海','Y2T60'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550808019','白生全','86-06-03','8828888','重庆','Y2T49'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13540925028','沈永强','81-06-23','13588065815','江苏南通市','Y2T50'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('21750710010','张键','85-07-18','8557378','广东江门','Y2T50'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550906016','吴家彬','82-09-22','8324282','珠海市斗门区','Y2T52'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550912004','邹城廷','85-10-15','8218358','广东珠海','Y2T53'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13540706014','娄智欣','85-09-21','8545256','江西峡江','Y2T42'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550304010','陈迪华','00-01-01','5612698','广东珠海','Y2T42'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550321013','陈福清','80-11-25','13751861125','江西于都县','Y2T42'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13551228001','陶正武','83-06-19','13824151800','湖南益阳市','Y2T42'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13540607014','杨振杰','75-08-10','13800138000','珠海','Y2A21'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550418023','鲁力','82-03-23','13727003870','广东珠海','Y2T44'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550513026','贺际勇','80-11-06','2628682','湖南常德市','Y2T44'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('51451226001','刘峰','82-02-06','8385613','湖南衡阳市','Y2T44'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550531024','陈学斌','82-02-03','8358801','广东珠海','Y2T45'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550531001','郑银河','81-08-26','13697788878','湖南隆回县','Y2T47'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550715014','陈卓','86-08-30','2210387','江西省于都县','Y2T47'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550715019','贾海波','85-04-10','13539599336','湖南蓝山县','Y2T47'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550912006','钟文康','84-05-15','13750086179','广东珠海市','S2T53'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13550912007','朱婉婷','83-08-11','13527719983','广东珠海','Y2T53'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13551011008','易虎','84-02-28','13631237777','四川安岳县','Y2T56'); insert into stuinfo (stuno,stuName,birthday,stutel,stuAddress,classname) values('13551011013','陈挺','84-05-19','13433677109','珠海香洲','S2T56'); --插入成绩表 insert into Exam values('13540607014',95,86); insert into Exam values('13540706014',55,56); insert into Exam values('13540925028',65,86); insert into Exam values('13550304010',60,60); insert into Exam values('13550321013',89,null); insert into Exam values('13550418023',91,60); insert into Exam values('13550513026',60,76); insert into Exam values('13550531001',null,null); insert into Exam values('13550531024',null,86); insert into Exam values('13550715006',54,46); insert into Exam values('13550715014',46,76); insert into Exam values('13550715019',32,86); insert into Exam values('13550808001',86,36); --查询数据 select * from stuinfo select * from Exam
--创建存储过程 create proc proc_stuInfo as select * from StuInfo go --调用存储过程 exec proc_stuInfo go --创建存储过程 create proc Proc_passStu as select stuname,stuage,stusex,stuADDress,written,Lab from StuInfo s ,Exam e where s.StuNo=e.StuNo go --调用存储过程 exec Proc_passStu go select stuname,stuage,stusex,stuADDress,written,Lab from StuInfo s ,Exam e where s.StuNo=e.StuNo --创建带输入参数的存储过程 create proc proc_passStu1 @written int, @Lab int as select stuname,stuage,stusex,stuADDress,written,Lab from StuInfo s ,Exam e where s.StuNo=e.StuNo and written>=@written and Lab>=@Lab go --调用带参存储过程 exec proc_passStu1 60,60 go --创建带输入参数并赋默认值的存储过程 create proc proc_passStu2 @written int=60, @Lab int=60 as select stuname,stuage,stusex,stuADDress,written,Lab from StuInfo s ,Exam e where s.StuNo=e.StuNo and written>=@written and Lab>=@Lab go --调用存储过程 exec proc_passStu2 exec proc_passStu2 70,60 --笔试及格线为70,机试默认60 exec proc_passStu2 70 --笔试及格线默认60,机试及格线为70 exec proc_passStu2 default,70 exec proc_passStu2 @lab=70 go --创建带输出参数的存储过程 create proc proc_passStu3 @passnum int output as select @passNum=count(*) from Exam where Written>=60 and Lab>=60 go declare @num int exec proc_passStu3 @num output print '本次及格人数:'+convert(varchar(5),@num) --创建带输入参数并赋默认值及输出参数的存储过程 --没有默认值的参数定义在前面比较好 create proc proc_passStu4 @failnum int output, @written int=60, @Lab int=60 as declare @writtenavg decimal(5,2),@labavg decimal(5,2) select @written=AVG(written) from Exam where written is not null select @lab=AVG(Lab) from Exam where Lab is not null print '笔试平均分'+ convert(varchar(2),@written) print '机试平均分'+ convert(varchar(2),@lab) print '本班考成绩:'+ case when @writtenavg>=70 and @labavg>=70 then '优秀' when @writtenavg>=60 and @labavg>=60 then '一般' else '较差' end print '-------------------------------------------------' print ' 参加本次考试没有通过的学员' print '-------------------------------------------------' select s.StuName,e.StuNo,written,lab from Exam e,StuInfo s where e.StuNo=s.StuNo and (written<@written or lab <@Lab) select @failnum =count(*)from Exam where written<@written or lab <@Lab go declare @failnum int exec proc_passStu4 @failnum output print '本次不及格的人数:'+convert(varchar(5),@failnum) go --注意条件要加小括号 select StuName,Written,Lab from StuInfo s,Exam e where s.StuNo = e.StuNo and (Written<60 or Lab<60) --使用join就不用 select StuName,Written,Lab from StuInfo s join Exam e on s.StuNo = e.StuNo where Written<60 or Lab<60