SQL教学课程-T-SQL编程.ppt

第三章,T-SQL编程,回顾1,建库的语法？补充下列语句：IFEXISTS（SELECT*FROM?WHEREname=stuDB）DROPDATABASEstuDB建表的语法？补充下列语句：IFEXISTS（SELECT*FROM?WHEREname=stuDB）DROPTABLEstuInfo解释下列语句的含义？EXECsp_addloginzhangsan,1234EXECsp_grantdbaccesszhangsan,zhangsanDBUser,回顾2,补充下列语句：1.ALTERTABLEstuInfoADDCONSTRAINT?PRIMARYKEY(stuNo)2.ALTERTABLEstuInfoADDCONSTRAINTUQ_stuID?3.ALTERTABLEstuInfoADDCONSTRAINTDF_stuAddress?4.ALTERTABLEstuInfoADDCONSTRAINTCK_stuAgeCHECK(?)5.ALTERTABLEstuMarksADDCONSTRAINTFK_stuNoFOREIGNKEY?REFERENCES?,目标,掌握如何定义变量并赋值掌握如何输出显示数据掌握IF、WHILE、CASE逻辑控制语句理解SQL中批处理的概念,使用变量,变量分为：局部变量：局部变量必须以标记作为前缀，如age局部变量的使用也是先声明，再赋值全局变量：全局变量必须以标记作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,局部变量,例如：DECLAREnamevarchar(8)DECLAREseatint,声明局部变量DECLARE变量名数据类型,赋值SET变量名=值SELECT变量名=值,例如：SETname=张三SELECTname=stuNameFROMstuInfoWHEREstuNo=s25302,必须确保筛选出的记录只有1条,或,局部变量示例,问题：编写T-SQL查找李文才的左右同桌？,学员信息表,分析：第一步，找出“李文才“的座位号；第二步，李文才的座位号加1或减1,局部变量示例,/*-查找李文才的信息-*/DECLAREnamevarchar(8)-学员姓名SETname=李文才-使用SET赋值SELECT*FROMstuInfoWHEREstuName=name/*-查找李文才的左右同桌-*/DECLAREseatint-座位号SELECTseat=stuSeatFROMstuInfo-使用SELECT赋值WHEREstuName=nameSELECT*FROMstuInfoWHERE(stuSeat=seat+1)OR(stuSeat=seat-1)GO,演示：使用局部变量,参考语句,全局变量,全局变量都使用两个标志作为前缀,全局变量示例,printSQLServer的版本+VERSIONprint服务器的名称:+SERVERNAMEINSERTINTOstuInfo(stuName,stuNo,stuSex,stuAge)VALUES(武松,s25328,男,23)-如果大于0表示上一条语句执行有错误print当前错误号+convert(varchar(5),ERROR)print刚才报名的学员，座位号为:+convert(varchar(5),IDENTITY)UPDATEstuinfoSETstuAge=85WHEREstuName=李文才print当前错误号+convert(varchar(5),ERROR)GO,演示：使用全局变量,参考语句,SQLServer的版本,服务器名称,座位号（自动编号）,错误号,错误号,逻辑控制语句,IFELSE语句,SQL中的IF-ELSE语句IF（条件）BEGIN语句1语句2ENDELSEBEGIN语句1；语句2；END,ELSE是可选部分如果有多条语句，才需要BEGIN-END语句块,IF-ELSE示例,问题：统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息。,学员成绩表,分析：第一步，统计平均成绩存入临时变量；第二步，用IFELSE判断；,IF-ELSE示例,设置输出结果的格式,为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式,逻辑控制语句,WHILE循环语句,SQL中的WHILE语句WHILE（条件）BEGIN语句1语句2BREAKEND,BREAK表示退出循环如果有多条语句，才需要BEGIN-END语句块,WHILE示例,问题：本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止。,学员成绩表,分析：第一步，统计没通过的人数；第二步，如果有人没通过，加分；第三步，循环判断。,DECLAREnintWHILE(1=1)-条件永远成立BEGINSELECTn=COUNT(*)FROMstuMarksWHEREwrittenExam0)UPDATEstuMarks-每人加2分SETwrittenExam=writtenExam+2ELSEBREAK-退出循环ENDprint加分后的成绩如下：SELECT*FROMstuMarks,WHILE示例,参考语句,逻辑控制语句,CASEEND多分支语句,CASEWHEN条件1THEN结果1WHEN条件2THEN结果2ELSE其他结果END,CASE-END示例,问题：采用美国的ABCDE五级打分制来显示笔试成绩。A级:90分以上B级：8089分C级:7079分D级：6069分E级：60分以下,学员成绩表,printABCDE五级显示成绩如下：SELECTstuNo,成绩=CASEWHENwrittenExam60THENEWHENwrittenExamBETWEEN60AND69THENDWHENwrittenExamBETWEEN70AND79THENCWHENwrittenExamBETWEEN80AND89THENBElSEAENDFROMstuMarks,CASEEND示例,参考语句,CASE-END课堂练习,课堂练习：请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成绩，如上图所示。优：90分以上良：8089分中：7079分差：6069分不及格：60分以下,学员成绩分析,CASE-END练习答案,USEstuDBGOSELECT考号=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,批处理语句,批处理是包含一个或多个SQL语句的组，从应用程序一次性地发送到SQLServer执行SQLServer将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条,客户端应用程序,SQLServer服务器,批处理语句：语句1语句2GO,批处理语句示例,SELECT*FROMstuInfoSELECT*FROMstuMarksUPDATEstuMarksSETwrittenExam=writtenExam+2GO,GO是批处理的标志，表示SQLServer将这些T-SQL语句编译为一个执行单元，提高执行效率一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定,批处理语句示例,-例如建表语句的末尾必须添加GOCREATETABLEstuInfo(.)GO,SQLServer规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加GO批处理标志,课堂综合练习,课堂练习：则根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。90分以上：不加分8089分：加1分7079分：加2分6069分：加3分60分以下：加5分,加分前,加分后,课堂综合练习答案,SELECT*FROMstuMarks-原始成绩DECLARElabAvgINTWHILE(1=1)BEGINUPDATEstuMarksSETlabExam=CASEWHENlabExam=85BREAKENDSELECT*FROMstuMarks-加分