sql完整数据库操作存储过程登录判断增删改查精

createdatabaseAAgouseAAgocreatetableStudent(sidintprimarykey,snamenvarchar(20,sexnvarchar(20,birthdaydatetime,classnvarchar(10,pwdnvarchar(10gocreatetableCourse(cidintidentity(1,1primarykey,cnamenvarchar(20gocreatetableScore(sidint,cidint,scoreint,primarykey(sid,cidgoinsertintoCoursevalues('C#'insertintoCoursevalues('English'insertintoStudentvalues(100张三：‘男','1990-1-12',一班','123'insertintoStudentvalues(1002李四','女','1990420','—班','456'insertintoStudentvalues(1003王五','男','1991-10-11',二班','789'insertintoStudentvalues(1004，赵六','男'，'1992-8-5','二班','101'insertintoStudentvalues(1005天齐','男'，’1992-5-5','三班','120'insertintoScorevalues(1001,1,65insertintoScorevalues(1001,2,60insertintoScorevalues(1002,1,50insertintoScorevalues(1002,2,40insertintoScorevalues(1003,1,75insertintoScorevalues(1003,2,60insertintoScorevalues(1004,1,72insertintoScorevalues(1004,2,45select*fromstudentselectsnamefromstudentselectsid,snamefromstudentselect*fromstudentorderbysiddesc--desc降序asc升序select*fromstudentorderbysex,classascselect*fromstudentorderbybirthdaydescselect*fromstudentwheresid=1001select*fromstudentwheresex男,'andbirthday>'1991-1-1'select*fromstudentwherebirthdaybetween'1990-1-1'and'1991-1-1'select*fromstudentwheresnamelike'%五%'--%%是通配符selectdistinct(classfromstudent-去除重复项selectcount(sidfromstudentselectcount(sidfromstudentwheresid=1001andpwd='123'selectcount(sid,classfromstudentgroupbyclassselectcount(sid,sexfromstudentgroupbysexselectcount(sid,sex,classfromstudentgroupbysex,classselectcount(sid,classfromstudentwherese|="groupbyclassselectsum(scorefromscoreselectavg(scorefromscorewheresid=1001selectmax(score,cidfromscoregroupbycidselectavg(score,cidfromscoregroupbycidhavingavg(score>60selectavg(score,sid,cidfromscoregroupbysid,cidhavingavg(score<60selectavg(score,sidfromscoregroupbysidhavingavg(score>59select*fromscorewherescore=(selectmax(scorefromscoreselect*fromstudentwherebirthday=(selectmin(birthdayfromstudentselect*fromstudentselect*fromcourseselect*fromscoreselectcount(sidasY_Nfromstudentwheresid=1001andpwd='123'--selectavg(scoreasavgfromscorewheresid=1001--selectcount(sid,classfromstudentgroupbyclass--selectcount(sid,sexfromstudentgroupbysex--selectcount(sid,class,sexfromstudentgroupbyclass,sex--selectcount(sid,classfromstudentwheresex=男'groupbyclass-内连接selectstudent.sid,student.sname,student.class,score.scorefromstudentinnerjoinscoreonstudent.sid=score.sid-三表链接selectstudent.sid,student.sname,ame,score.scorefromscoreinnerjoinstudentonstudent.sid=score.sidinnerjoincourseonscore.cid=course.cidwherestudent.sname弓长'三'--用右外表查询(以右表为基准)selectstudent.sid,student.sname,student.class,score.scorefromstudentrightouterjoinscoreonstudent.sid=score.sidselectstudent.sid,student.sname,student.class,score.scorefromstudentleftouterjoinscoreonstudent.sid=score.sidwherescore.scoreisnul交叉链接(笛卡尔集合(少用selectstudent.sid,student.sname,score.scorefromstudentcrossjoinscore-嵌套查询--查询最高分的学生selectsid,snamefromstudentwheresidin(selectsidfromscorewherescore=(selectmax(scorefromscore总分最高的学生学号selectsidfrom(selecttop1sum(scoreass,sidfromscoregroupbysidorderbysdesca--统计每门课程最高分的学生的学号 selectscore.sid,score.cid,score.scorefromscore,(selectmax(scoreasm,cidfromscoregroupbycidbwherescore.cid=b.cidandscore.score=b.m-查班级平均分selectavg(c.score,c.classfrom(selecta.class,b.scorefromstudenta,scorebwherea.sid=b.sidcgroupbyclass牛人平均成绩selectavg(c.score,c.sidfrom(selecta.sid,b.scorefromstudenta,scorebwherea.sid=b.sidcgroupbysid--单科最高分的同学学号和姓名和班级selectdistinct(d.sid,d.sname,d.classfrom(selectc.sid,c.sname,c.classfromstudentc,(selectsid,scorefromscore,(selectmax(scoreass,cidfromscoregroupbycidawherea.s=score.scorebwherec.sid=b.sidddeclare@iint--inti=0;declare@snvarchar(10-strings;set@i=1;--i=10;--set@s='AAAAAAA'--s='AAAAAAA'while@i<10-while循环beginprint@iset@i=@i+1--i++endselectsid,score,casewhencid=1then'C#'whencid=2then'English'endfromscoreselectsid,cid,score,casewhenscore>59then及格'else不及格'endfromscore--print@i--本地测试用print--print@s——if@i=0--if条件语句--begin--print'BBBBBBBBBBBBBBBBBBBBBBBB'--end--else--begin--print'SSSSSSSSSSSSS'--end使用存储过程判断用户登录信息alterprocsp_login@sidint,--输入参数@pwdnvarchar(20,--输入参数@snvarchar(20output-输出参数asdeclare@iintset@i=(selectcount(1fromstudentwheresid=@sidandpwd=@pwd--return@iif@i=1set@s=合法用户'elseset@s='非法用户'declare@snvarchar(20execsp_login1001,'123',@soutputprint@sif(selectmin(scorefromscorewheresid=1001>90print学生1001成绩全部优秀'elseif(selectmin(scorefromscorewheresid=1001>59print学生1001成绩全部及格‘elseprint学生1001有成绩不及格’--为表创建具有不同字段名的视图createviewv_stu(sid,sname,sexasselectsid,sname,sexfromstudent-查看视图数据select*fromv_stu--使用存储过程实现从表及联删除alterprocsp_delete@sidintasdeletefromscorewheresid=@siddeletefromstudentwheresid=@sidexecsp_delete1001使用存储过程添加学生信息 添加—— 开始 ——alterprocsp_add@sidint,@snamenvarchar(20,@sexnvarchar(10,@birthdaydatetime,@classnvarchar(10,@pwdnvarchar(10,@iint,@rnvarchar(10output一个过程可以有多个输出参数但只有一个返回值 输出参数是任何类型asif@i=0beginifnotexists(selectsidfromstudentwheresid=@sidbegininsertintostudentvalues(@sid,@sname,@sex,@birthday,@class,@pwdset@「添加成功'endelseset@r='重复添加‘endelsebeginupdatestudentsetsname=@sname,sex=@sex,birthday=@birthday,class=@class,pwd=@pwdwheresid=@sidif@@rowcount>0set@r=修改成功'elseset@r=修改无效'enddeclare@rintexec@r=sp_add1011李四','男','1990-1-12','一班','123'print@r 查找所有学生分数 一个 alterprocsp_selectAIIStudentScoreasselectstudent.sid,student.sname,ame,score.scorefromscorerightjoinstudentonstudent.sid=score.sidleftjoincourseonscore.cid=course.cid execsp_selectAIIStudentScoreselect*fromstudentdeletefromstudentwheresid=0 条件查询学生分数 一个 createprocsp_selectstudentscore@sidint,@snamenvarchar(10asif@sid>0beginselectstudent.sid,student.sname,ame,score.scorefromscoreinnerjoinstudentonstudent.sid=score.sidinnerjoincourseonscore.cid=course.cidwherestudent.sid=@sidendelsebeginselectstudent.sid,student.sname,ame,score.scorefromscoreinnerjoinstudentonstudent.sid=score.sidinnerjoincourseonscore.cid=course.cidwherestudent.snamelike'%'+@sname+'%'end execsp_selectstudentscore1001,''execsp_selectstudentscor三“， 查找与删除学生 一个 alterprocsp_operstudent@sidint,@iintasif@i=0select*fromstudentwheresid=@sidelsedeletefromstudentwheresid=@sid 登录 一个 alterprocsp_login@sidint,@pwdnvarchar(20asdeclare@iintset@i=(selectcount(1fromstudentwheresid=@sidandpwd=@pwdreturn@i 查看学生分数信息 -alterprocsp_viewstudent@sidint,@sumintoutput,@avgintoutput,@pidintoutputasdeclarecurcursorforselectavg(scorep,sum(scores,sidfromscoregroupbysidorderbysdescopencurdeclare@pint,@sint,@sid2int,@iintset@i=1fetchfromcurinto@p,@s,@sid2while@@fetch_status=0beginif@sid2=@sidbeginset@pid=@iset@sum=@sset@avg=@pendset@i=@i+1fetchfromcurinto@p,@s,@sid2endclosecurdeallocatecurselectcname,scorefromscorea,coursebwherea.cid=b.cidanda.sid=@siddeclare@avgint,@pidint,@sumintexecsp_viewstudent1003,@pidoutput,@sumoutput,@avgoutputprint@sumprint@avgprint@pid 结束使用存结束储过程添加新课程信息，并输入新课程的储过程添加新课程信息，并输入新课程的IDalterprocsp_addCourse@cnamenvarchar(10asinsertintocoursevalues(@cnamereturn@@identitydeclare@iintexec@i=sp_addCourse'PHP'print@iselect*fromcourse实现数据表分页查询--页码=总行数/每页行数每页行数size--总行数--paixulinshibiaozhuanyonghanshuselecttop5*from(selecttop(9-(2-1*5*fromemployeesorderbyemployeeiddescaorderbya.employeeidalterprocsp_page@pageint页码@tablevarchar(10,一表名称@orderbyvarchar(10,-排序字段名@sizeintasdeclare@sqlvarchar(500--放sql语句字符串set@sql='declare@countint'set@sql=@sql+'set@count=(selectcount(1from'+@table+”set@sql=@sql+'selecttop'+str(@size+'*from(selecttop(@count-('+convert(varchar(10,@page+'-1*'+str(@size+'*from'+@table+'orderby'+@orderby+'desca'+'orderbya.'+@orderbyexec(@sqlprint@sqlexecsp_page1,'employees','employeeid',3函数declare@strvarchar(10set@str='1234'declare@iintset@i=1234--printlen(@strprintlen(ltrim(str(@iprintsubstring(@str,1,2printfloor(rand(*1000--printgetdate(--Datetime.Nowprintdateadd(day,2,getdate(printdateadd(day,2,'2011-10-20往后添加时间printdatediff(day,'2011-10-25','2012-12-10'--时间差TimeSpan.daysprintdatepart(year,'2011-10-25'-获取时间格式中的一部分(即年，月或日)printdatepart(month,'2011-10-25'printdatepart(day,'2011-10-25'printconvert(nvarchar(10,datepart(year,'2011-10-25'+'/'+convert(nvarchar(10,datepart(month,'2011-10-25'+'/'+convert(nvarchar(10,datepart(day,'2011-10-25'按出生年份统计学生人数--sidcountsyearsselectcount(sid,datepart(year,birthdayfromstudentgroupbydatepart(year,birthdayselect*from@tabledeclare@tablenvarchar(10set@table='student'exec('select*from'+@tablecreateprocsp_AA@tablevarchar(10asexec('select*from'+@tableexecsp_AA'student'-编写通用版的分页存储过程--自定义函数createfunctionfun(@iintreturnsnvarchar(10asbeginreturnconvert(nvarchar(10,@iendprintdbo.fun(10--dbo当前数据库里有效--定义函数输入姓名后返回学号createfunctionfun2(@snamenvarchar(10returnsintasbegindeclare@iintset@i=(selectsidfromstudentwheresname=@snamereturn@iendprintdbo.fun2张三'--查询学号，课程号，分数同时在分数栏将<60的成绩直接输出为不及格--学号课程分数--1001195--10021不及格alterfunctionfun3(@scoreintreturnsnvarchar(10asbegindeclare@rnvarchar(10if@score<60set@r二不及格'elseset@r=convert(nvarchar(10,@scorereturn@rendselectsid,cid,dbo.fun3(scorefromscored-同一列，输出Employees表中的lastname和firstname字段createfunctionfun4(@lastnamevarchar(10,@firstnamevarchar(10returnsvarchar(20asbeginreturn@lastname+'-'+@firstnameendselectemployeeid,dbo.fun4(lastname,firstname,titlefromemployees-计算orderdetails表每条订单的总价select*from[orderdetails]createfunctionfun5(@unitpricemoney,@quantitysmallint,@discountrealreturnssmallintasbeginreturn@unitprice*@quantity*(1-@discountendselect*,dbo.fun5(unitprice,quantity,discountfrom[orderdetails]--游标不占用物理内存，全夸E是临时文件declarecurlcursorforselectsid,sname,classfromstudentopencurldeclare@sidint,@snamenvarchar(10,@classnvarchar(10,@iintset@i=1fetchfromcurlinto@sid,@sname,@class--fetcl取游标所在的行的值while@@fetch_status=0beginprintconvert(nvarchar(10,@i+'.'+convert(nvarchar(10,@sid+','+@sname+','+@classset@i=@i+1fetchfromcur1into@sid,@sname,@classendclosecur1deallocatecur1给所有分数v60的人加送10分declarecur2cursorforselect*fromscoreopencur2declare@sidint,@cidint,@scoreintfetchfromcur2into@sid,@cid,@scorewhile@@fetch_status=0--0语句成功-1语句失败或行不在结果集中-2提取的行不存在beginif@score<60updatescoresetscore=@score+10wheresid=@sidandcid=@cidfetchfromcur2into@sid,@cid,@scoreendclosecur2deallocatecur2清空资源--找出重名的人，并使用合适的方式返回结果数据。select*fromstudentalterprocsp_Findascreatetable#table1(sidint,snamenvarchar(10declarecur3cursorforselectsid,snamefromstudentopencur3declare@sidint,@snamenvarchar(10,@countintfetchfromcur3into@sid,@snamewhile@@fetch_status=0beginset@count=(selectcount(sidfromstudentwheresname=@snameif@count>1insertinto#table1values(@sid,@sname--printconvert(nvarchar(10,@sid+''+@snamefetchfromcur3into@sid,@snameendclosecur3deallocatecur3se