T-SQL编程

对酒当歌，人生几何1T-SQL编程1.目标掌握如何定义变量并赋值掌握如何输出显示数据掌握IF、WHILE、CASE逻辑控制语句理解SQL中批处理的概念2.重点掌握如何定义变量并赋值掌握IF、WHILE、CASE逻辑控制语句3.难点掌握IF、WHILE、CASE逻辑控制语句知识点=========================================================4.局部变量和全部变量知识点说明1)变量声明局部变量必须以标记@作为前缀，如@age，局部变量的使用也是先声明，再赋值；全局变量必须以标记@@作为前缀，如@@version，全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值。2)常见的全局变量：3)变量赋值变量需要先声明后赋值。关键代码1)全局变量@@identityIDENT_CURRENT('table_name')IDENT_CURRENT和@@IDENTITY类似，这两个函数都返回最后生成的标识值。IDENT_CURRENT返回为某个会话和用域中的指定表生成的最新标识值。@@IDENTITY返回为跨所有作用域的当前会话中的某个表生成的最新标识值。select@@identityselectident_current('表名')/*INSERT语句不为这些类型的列指定值：具有IDENTITY属性的列，此属性为该列生成值。具有默认值的列，此默认值用NEWID函数生成唯一的GUID值。计算列。*/createtablestuInfo(stuNamevarchar(10)notnull,stuNovarchar(10)notnull,对酒当歌，人生几何2stuSexchar(2)notnull,stuAgeint,stuSeatint,stuAddressvarchar(10)default('地址不详'))insertintostuInfoselect'张秋丽','s25301','男',18,1,'北京海淀'unionselect'李文才','s25302','男',28,2,defaultunion--错误，不能使用defaultselect'李斯文','s25303','女',22,3,'河南洛阳'unionselect'欧阳俊雄','s25304','男',28,4,'新疆'unionselect'妹超风','s25318','女',23,5,defaultinsertintostuInfovalues('李文才','s25302','男',28,2,default)--正确/*--查找李文才的信息--*/DECLARE@namevarchar(8)SET@name='李文才'--使用SET赋值SELECT*FROMstuInfowherestuName=@name/*--查找李文才的左右同桌--*/DECLARE@seatintSELECT@seat=stuSeatfromstuInfowherestuName=@name--使用SELECT赋值SELECT*FROMstuInfowhere(stuSeat=@seat+1)or(stuSeat=@seat-1)GO--简单的if-else（使用sp_executesql存储过程）declare@myavgfloat,@sqlnvarchar(1000)set@sql='selecttop3*fromstuMarksorderbywrittenExam'select@myavg=avg(writtenExam)fromstuMarksif(@myavg70)set@sql=@sql+'desc'print@sqlexecsp_executesql@sql--@sql必须是nvarchar类型/*本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止*/CREATETABLE[dbo].[stuMarks]([ExamNo][nvarchar](20)NOTNULL,对酒当歌，人生几何3[stuNo][nvarchar](20)NOTNULL,[writtenExam][int]NOTNULL,[labExam][int]NOTNULL)ON[PRIMARY]deletefromstuMarksgoinsertintostuMarksselect's271811','s25303',80,58unionselect's271813','s25302',50,90unionselect's271816','s25301',77,82unionselect's271818','s25328',45,65setnocount–-不显示受影响的行数onwhile(1=1)beginifexists(select1fromstuMarkswhere[writtenExam]60)updatestuMarksset[writtenExam]=[writtenExam]+2where[writtenExam]=98elsebreak;endprint'提分之后的成绩是：'select*fromstuMarks采用美国的ABCDE五级打分制来显示笔试成绩。A级:90分以上B级：80－89分C级:70－79分D级：60－69分E级：60分以下--1select[ExamNo],[stuNo],[writtenExam]fromstuMarks--2select[ExamNo],[stuNo],--成绩case--提示case单独起一行，end不可少，else可以没有，但是如果取--值在else所在的范围之中时，显示null值when[writtenExam]90then'A'when[writtenExam]80then'B'when[writtenExam]70then'C'when[writtenExam]60then'D'对酒当歌，人生几何4else'E'endfromstuMarks--CASE里面的列不能是别名，必须是实际的列名，即(writtenExam+labExam)/2不能用平均分代替SELECT考号=ExamNo,学号=stuNo,笔试=writtenExam,机试=labExam,平均分=(writtenExam+labExam)/2,等级=CASEWHEN(writtenExam+labExam)/260THEN'不及格'WHEN(writtenExam+labExam)/2BETWEEN60AND69THEN'差'WHEN(writtenExam+labExam)/2BETWEEN70AND79THEN'中'WHEN(writtenExam+labExam)/2BETWEEN80AND89THEN'良'ElSE'优'ENDFROMstuMarks一、批处理1.批处理：指包含一条或多条T-SQL语句的语句组，这组语句从应用程序一次性地发送到SQLserver服务器执行。2.执行单元：SQLserver服务器将批处理语句编译成一个可执行单元，这种单元称为执行单元。3.若批处理中的某条语句编译出错，则无法执行。若运行出错，则视情况而定。4.书写批处理时,go语句作为批处理命令的结束标志，当编译器读取到go语句时，会把go语句前的所有语句当作一个批处理，并将这些语句打包发送给服务器。go语句本身不是T-SQL语句的的组成部分，只是一个表示批处理结束的前端指令。则根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。90分以上：不加分80－89分：加1分70－79分：加2分60－69分：加3分60分以下：加5分declare@avglabExamintwhile(1=1)beginselect@avglabExam=avg(labExam)fromstuMarksif@avglabExam85updatestuMarkssetlabExam=labExam+casewhenlabExam=90then0whenlabExam=80then1whenlabExam=70then2whenlabExam=60then3else5end对酒当歌，人生几何5elsebreak;endselect*fromstuMarksT-SQL允许你使用不同的方法解决一个问题.有的时候,尽管选择不是那么明显,但是却可以让你得到令人满意的和快乐的惊奇.下边让我们解读Dr.TomMoreau对同一问题不同的可能性的探索.可能我们可以在那些不同的方法之中发现一些珍贵的东西.让我们以我们的老朋友Northwind数据库为例,这里我们用到的是[orderdetails]表,这个表是一个定单的明细表,和order表是多对一的关系.也就是一个定单对应多个订购的产品.假设你想得到每个定单订购的总价值,但是不包括号产品.Listing1给了我们第一种解法:selectOrderID,sum(Quantity*UnitPrice)valuefrom[OrderDetails]o1whereProductID59groupbyOrderID上边的语句很简单,它排除掉了号产品的定单明细条目,然后进行分组统计.但是如果我们需要忽略掉订购号产品的定单呢？也就是说我们要统计没有包含号产品的定单的价值.你想到了WHERE,NOTEXIST(S)关键词了吗？Listing2给了我们第二种方法:selecto1.OrderID,sum(o1.Quantity*o1.UnitPrice)valuefrom[OrderDetails]o1wherenotexists(select*from[OrderDetails]o2whereo2.OrderID=o1.OrderIDando2.ProductID=59)groupbyo1.OrderID如果你不喜欢用exist的话,你可以转化成使用notin:Listing3selecto1.OrderID,sum(o1.Quantity*o1.UnitPrice)valuefrom[OrderDetails]o1where59notin(selectProductIDfrom[OrderDetails]o2whereo2.OrderID=o1.OrderID)groupbyo1.OrderID尽管Listing1不满足我们现在的查询条件.但是从性能发面考虑,Listing1还是最好的,因为它只用到了一次表的扫描.而后边的两个查询都是用到了相关子查询,如果你查看查询计划就回看到,他们都涉及到了两次表的扫描.如果你曾经在T-SQL用过交叉表查询的话,你就不会对聚集函数里边的case结构陌生.现在我们就把这个非常有趣的方法应用到我们的问题中来:Listing4对酒当歌，人生几何6selectOrderID,sum(Quantity*UnitPrice)valuefrom[OrderDetails]o1groupbyOrderIDhavingsum(casewhenProductID=59then1else0end)=0HAVING子句起到了对分组的结果进行过滤的作用.如果没有包含号产品,就会出现=0,显然这是满足条件的.如果包含了号产品的订购,就会出现n=0(n0),这样的定单就回被过滤掉.查看执行计划你就回发现是一次表的扫描,非常棒!再来举一个例子:我们这回用到的表是order表,假设我们要统计只通过一个雇员雇员下定单的顾客.你可以想到用子查询notexist来实现:selectdistincto1.CustomerIDfromOrderso1wherenotexists(select*fromOrderso2whereo2.CustomerID=o1.CustomerIDando2.EmployeeIDo1.EmployeeID)同