【网学网提醒】：网学会员编辑为广大网友搜集整理了：sql经典技巧绩等信息，祝愿广大网友取得需要的信息，参考学习。
begintransactiondeclare@now_timevarchar(8)select*fromstudentwith(holdlock)whereid=1--表级别即是共享锁select@now_time=convert(varchar,getDate(),8)print'锁定时间：'+@now_timewaitfordelay'00:00:20'select@now_time=convert(varchar,getDate(),8)print'解锁时间：'+@now_timecommittransaction/*以上是事务*/select*fromstudentselectidas'编号',nameas''fromstudentwhereage>20orderbyageasc/*年龄大于20且按升序排列*//*以下是游标的使用*/declarestu_cursor0scrollcursorforselect*fromstudentwhereid=1orderbyageopenstu_cursor0fetchfromstu_cursor0closestu_cursor0declarestu_cursorscrollcursorforselect*fromstudentorderbyageopenstu_cursorfetchnextfromstu_cursorwhile(@@fetch_status=0)beginfetchnextfromstu_cursorenddeallocatestu_cursor/*以下是update触发器的代码*/if(exists(select*fromsysobjectswherename='my_trigger'))begindroptriggermy_triggerendcreatetriggermy_triggeronstudent
forupdateasif(columns_updated()&;1)>0beginrollbacktransactionraiserror('不允许修改',16,1)endif(columns_updated()&;6)>0begininsertintostudent(name,age)selectdel.name,del.agefromdeletedasdelinsertintostudent(name,age)selectins.name,ins.agefrominsertedasinsendupdatestudentsetname='lok',age=24/*执行触发器*//*以下是存储过程的代码*/if(exists(select*fromsysobjectswherename='my_pro'))begindropproceduremy_proendcreateproceduremy_pro@cnointasif(exists(select*fromstudentwherecnoisnull))beginupdatestudentsetcno=@cnowherecnoisnullendelsebeginreturnendexecutemy_pro1/*执行存储过程*/
select*fromstudentinsertintostudent(name,age)values('jack',46)deletefromstudentwhereid=28updatestudentsetcno=2whereid=30
/*分页语句*/selecttop5*fromstudentwhereid个*/
notin(selecttop5idfromstudent)/*取出的是第二页的5
select*fromstudentleftjoincourseoncourse.id=studentoselect*fromstudentrightjoincourseoncourse.id=studentoselect*fromstudentinnerjoincourseoncourse.id=studentoselect*fromstudentcrossjoincourseselectdistinct(age)fromstudentorderbyage/*列出人数多于2个选修的课程名字及人数*/selectcourse.nameas'课程名字',count(*)as'总数'fromstudent,coursewherecourse.id=studentogroupbycourse.namehavingcount(*)>2selectage,namefromstudentgroupbyage,nameorderbyage
/*以下是函数的代码*/if(exists(select*fromsysobjectswherename='my_fun'))begindropfunctionmy_funendcreatefunctionmy_fun(@idint)returnstableasreturnselect*fromstudentwhereid=@idselect*frommy_fun(30)/*执行函数*/
