TP03

SQL编程第三章回顾•写出T-SQL语言创建数据库的语法•写出T-SQL语言创建表的语法•完成下面SQL语句1.USE______________IFEXISTS(SELECT*FROM_____________WHEREname='MySchool')DROPDATABASEMySchool2.USE______________IFEXISTS(SELECT*FROM_____________WHEREname='Student')DROPTABLEStudent3.ALTERTABLEStudentADDCONSTRAINT__________PRIMARYKEY(studentNo),CONSTRAINTUQ_stuID___________________,CONSTRAINTDF_stuAddress______________________________,CONSTRAINTCK_BornDateCHECK(____________________),CONSTRAINTFK_StudentNoFOREIGNKEYREFERENCES________________sysdatabasesmasterUNIQUE(IdentityCard)MySchoolsysobjectsPK_StuNoDEFAULT('地址不详')FORAddressBornDate='1980-1-1'Grade(GradeID)本章任务•查找“李文才”及相邻学号的学生•查询学号是20011学生的姓名和年龄，并输出比他大1岁和小1岁的学生信息•统计学生“JavaLogic”课最近一次考试的平均分并显示前/后3名学生成绩•根据学生“Winforms”课最近一次考试成绩适当加分确保每人这次考试都通过•采用美国的ABCDE五级打分制显示“JavaLogic”最近一次学生考试成绩本章目标•掌握如何定义变量并赋值•掌握如何输出显示数据•掌握IF、WHILE、CASE逻辑控制语句•使用批处理指令完成多语句批量处理使用变量变量分为：局部变量：局部变量必须以标记@作为前缀，如@age局部变量的使用也是先声明，再赋值全局变量：全局变量必须以标记@@作为前缀，如@@version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值局部变量DECLARE@namevarchar(8)DECLARE@seatint声明局部变量赋值SET@name='张三'SELECT@name=studentNameFROMStudentWHEREstudentNo='10011'确保筛选出的记录只有1条DECLARE@变量名数据类型SET@变量名=值或SELECT@变量名=值局部变量示例-1学生信息表编写T-SQL查找李文才及他的相邻学号学生第一步：找出“李文才”的学号第二步：李文才的学号加1或减1局部变量示例-2•参考语句/*--查找李文才的信息--*/DECLARE@namevarchar(8)--学生姓名SET@name='李文才'--使用SET赋值SELECTStudentNo,StudentName,BornDate,AddressFROMStudentWHEREStudentName=@name/*--查找李文才的左右同桌--*/DECLARE@StudentNoint--学号SELECT@StudentNo=StudentNoFROMStudent--使用SELECT赋值WHEREStudentName=@nameSELECTStudentNo,StudentName,BornDate,AddressFROMStudentWHERE(StudentNo=@StudentNo+1)OR(StudentNo=@StudentNo-1)GO演示示例1：使用局部变量SET与SELECT区别SETSELECT同时对多个变量赋值不支持支持表达式返回多个值时出错将返回的最后一个值赋给变量表达式未返回值时变量被赋NULL值变量保持原值DECLARE@addrnvarchar(100),@namenvarchar(100)SET@addr='',@name='张三'SELECT@addr='北京',@name='张三'SET@addr=(SELECTAddressFROMStudent)SELECT@addr=AddressFROMStudent--最后一个Address列值SET@addr=(SELECTAddressFROMStudentWHERE10)--NULL值SELECT@addr=AddressFROMStudentWHERE10--保持原值错误错误全局变量变量含义@@ERROR最后一个T-SQL错误的错误号@@IDENTITY最后一次插入的标识值@@LANGUAGE当前使用的语言的名称@@MAX_CONNECTIONS可以创建的同时连接的最大数目@@ROWCOUNT受上一个SQL语句影响的行数@@SERVERNAME本地服务器的名称@@TRANSCOUNT当前连接打开的事务数@@VERSIONSQLServer的版本信息全局变量都使用两个@标志作为前缀全局变量示例1•如何获得数据库服务器的名称和SQLSERVER的版本信息？PRINT'服务器的名称:'+@@SERVERNAMEPRINT'SQLServer的版本'+@@VERSION第一步：由@@SERVERNAME得到数据库服务器名称，@@VERSION获得SQLSERVER的版本第二步：利用PRINT语句输出消息或SELECT语句输出结果SELECT@@SERVERNAMEAS'服务器的名称:'SELECT@@VERSIONAS'SQLServer的版本'SQLServer的版本服务器名称演示示例2：获得数据库服务器名称全局变量示例2插入名为“武松”、出生日期是1980年12月31日的学生信息修改“武松”的出生日期为“1970年7月8日”INSERTINTOStudent(StudentName,StudentNo,LoginPwd,GradeId,Sex,BornDate,IdentityCard)VALUES('武松',10011,'123456',1,0,'1980-12-31','11010119791231001x')PRINT'当前错误号'+CONVET(varchar(5),@@ERROR)UPDATEStudentSETBornDate='1970-7-8'WHEREStudentNo=10011PRINT'当前错误号:'+CONVERT(varchar(5),@@ERROR)GO错误号错误号演示示例3：获得SQL语句错误号如何获得数据更新失败时的错误信息？@@ERROR的值大于0表示最近一个SQL语句执行失败的错误号指导——使用变量2-1•训练要点：–声明并使用T-SQL变量•需求说明：–用字符“★”拼成下面的三角图形讲解需求说明指导——使用变量2-2•实现思路：–声明T-SQL变量–初始化变量值为“★”–PRINT语句显示三角图形DECLARE@tagnvarchar(1)SET@tag='★'PRINT@tagPRINT@tag+@tagPRINT@tag+@tag+@tagPRINT@tag+@tag+@tag+@tagPRINT@tag+@tag+@tag+@tag+@tagGO完成时间：25分钟一个SET语句只能为一个变量赋值T-SQL数据类型转换2-1•查询学号是10000的学生参加2009年2月15日的“JavaLogic”课程考试的成绩，要求输出学生姓名和成绩DECLARE@NAMEvarchar(50)--姓名DECLARE@Resultdecimal(5,2)--考试成绩DECLARE@NOintSET@NO=10000SELECT@NAME=StudentNameFROMStudentWHEREStudentNo=@NOSELECT@Result=StudentResultFROMStudentINNERJOINResultONStudent.StudentNo=Result.StudentNoINNERJOINSubjectONResult.SubjectNo=Subject.SubjectNoWHERESubjectName='JavaLogic'ANDStudent.StudentNo=@NOANDExamDate='2009-2-15'ANDExamDate'2009-2-16'PRINT'姓名：'+@NAMEPRINT'成绩：'+@ResultGOPRINT'成绩：'+CAST(@Resultasvarchar(10))将变量@Result数据由decimal类型转换为字符串演示示例4：数据类型显式转换PRINT'成绩：'+CONVERT(varchar(10),@Result)第一步：根据学号查出学生姓名第二步：按照指定日期、学号和课程查询得到考试成绩第三步：输出学生的姓名和考试成绩T-SQL数据类型转换2-2•T-SQL提供了两个转换函数SELECTStudentName+'的出生日期是'+CAST(BornDateasvarchar(50))AS'学生信息'FROMStudentSELECTStudentName,CONVERT(varchar(50),BornDate,102)AS出生日期FROMStudent第三个参数可以省略，它一般用于日期类型数据转换为字符类型，或浮点类型数据转换为字符类型不同的样式使转换后字符数据的显示格式不同CONVERT()与CAST()的不同点是：可以指定转换的样式CAST(表达式AS数据类型)CONVERT(数据类型，表达式，样式)练习——使用变量保存查询数据•需求说明：–查询学号是20011的学生姓名和年龄，并输出比他大1岁和小1岁的学生信息•提示：–查询输出学号是20011的学生姓名和计算出该学生的年龄–查询得到学号是20011的学生的出生日期–用日期函数DATEPART()获得学生的出生年份–查询输出大1岁和小1岁的学生信息完成时间：20分钟DECLARE@NOint--学号SET@NO=20011DECLARE@datedatetime--出生日期DECLARE@yearint--出生年份--获得学号是20011的学生姓名和年龄SELECTStudentName姓名,FLOOR(DATEDIFF(DY,BornDate,GETDATE())/365)年龄FROMstudentWHEREStudentNo=@NO--查询输出比学号是20011的学生大1岁和小1岁的学生信息SELECT@date=BornDateFROMStudent--使用SELECT赋值WHEREStudentNo=@NOSET@year=DATEPART(YY,@date)SELECT*FROMStudentWHEREDATEPART(YY,BornDate)=@year+1ORDATEPART(YY,BornDate)=@year-1GO逻辑控制语句•分支结构–IF-ELSE语句–CASE-END语句•循环结构–WHILE语句IF-ELSE语句•IF-ELSE语句SQL中的IF-ELSE语句IF（条件）BEGIN语句1语句2……ENDELSEBEGIN语句1语句2……ENDELSE是可选部分如果有多条语句，才需要BEGIN-END语句块C#中的if-else语句if（条件）{语句1;语句2;……}else{语句1;语句2;……}IF-ELSE示例•统计并显示2009-2-17的JavaLogic考试平均分•如果平均分在70以上，显示“考试成绩优秀”，并显示前三名学生的考试信息•如果在70以下，显示“考试成绩较差”，并显示后三名学生的考试信息学生成绩表演示示例5：使用IF-ELSE语句第一步：统计平均成绩存入临时变量第二步：用IF-ELSE语句判断DECLARE@myavgdecimal(5,2)--平