第09章 T-SQL编程.pptx

课程主讲人：第09章 T-SQL编程数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔第 9 章 T-SQL编程第1章 创建和管理数据库变量的使用2 2 输出语句 逻辑控制语句1 13 3目 录 T-SQL不但有强大的结构化查询功能，和其他高级语言类似，它本身也具有变量、运算和流控制等功能，也可以利用T-SQL语言进行编程，从而可以完成比较复杂的功能。T-SQL编程n局部变量 局部变量是用户自己定义并使用的变量，使用符号作为前缀。u局部变量的声明 使用变量-声明变量name，最多可以存储个字符DECLARE name varchar(8)DECLARE number int -声明一个存放学号的变量numberDECLARE variable_name DataType 演示演示 语法语法u局部变量的赋值 局部变量的赋值有两种方法：使用SET语句和SELECT语句。使用变量SET variable_name=value 语法语法SELECT variable_name=value注意注意1. SET赋值语句一般用于赋给变量指定的数据常量；赋值语句一般用于赋给变量指定的数据常量；SELECT赋值语句一般用赋值语句一般用于从表中查询数据。于从表中查询数据。2. SELECT语句需要确保筛选的记录不多于一条。如果查询的记录多于一条，语句需要确保筛选的记录不多于一条。如果查询的记录多于一条，则将把最后一条记录的值赋给变量。则将把最后一条记录的值赋给变量。n需求说明u 查找与“胡仲友”的学号相邻的学生信息。u首先查询“胡仲友”的学号。u 对“胡仲友”的学号加1或减1，然后进行查询。注意要将学号转换为整型后才可以进行加1或者减1。【演示1】查找相邻学生信息n 演示代码【演示1】查找相邻学生信息USE SchoolDBGO/*查找胡仲友的信息*/DECLARE name varchar(8)SET name=胡仲友胡仲友SELECT StudentNo,StudentName,BornDate,AddressFROM StudentWHERE StudentName=namen 演示代码（续）【演示1】查找相邻学生信息/*查找与胡仲友学号相邻的学生信息*/DECLARE StudentNo nvarchar(50)-使用SELECT赋值, StudentNo中存储胡仲友的学号 SELECT StudentNo=StudentNo FROM StudentWHERE StudentName=name-定义变量用于保存学号后面的数字DECLARE StuNo int-使用SET赋值，将学号转换为整型SET StuNo=CONVERT(int,SUBSTRING(StudentNo,2,7)SELECT StudentNo,StudentName,BornDate,AddressFROM StudentWHERE (CONVERT(int,SUBSTRING(StudentNo,2,7)=StuNo+1) OR (CONVERT(int,SUBSTRING(StudentNo,2,7)=StuNo-1)GOn全局变量 全局变量是SQL Server系统内部使用的变量，通常存储一些SQL Server的配置设定值和统计数据。 全局变量使用两个符号作为前缀。 常用的全局变量有ERROR（返回执行的上一个 Transact-SQL 语句的错误号）、VERSION（返回当前的 SQL Server 安装的版本）等。使用变量n技能要点u 局部变量的声明和使用。 n 需求说明u 用字符“”拼成如下图所示的三角形。实训1-使用局部变量n 关键点分析u 声明T-SQL局部变量，初始化变量值为“” ，并用PRINT语句显示三角形图形。实训1-使用局部变量一起努力 共同提高第1章 创建和管理数据库变量的使用1 1 输出语句 逻辑控制语句2 23 3目 录n输出语句 输出语句用于显示处理的数据结果。常用的输出语句有两种，即PRINT语句和SELECT语句。输出语句PRINT 局部变量或字符串 语法语法SELECT 局部变量 AS 自定义列名n需求说明u（1）使用PRINT语句和全局变量输出服务器名称和SQL Server的版本。u（2）使用SELECT语句和全局变量输出服务器名称和SQL Server的版本。【演示2】输出语句的应用n 演示代码【演示2】输出语句的应用PRINT 服务器的名称：服务器的名称：+SERVERNAMEPRINT SQL Server的版本的版本+VERSIONSELECT SERVERNAME AS 服务器名称服务器名称SELECT VERSION AS SQL Server的版本的版本注意注意用用PRINT语句输出的结果将在语句输出的结果将在“消息消息”窗口中以文本方式显示，用窗口中以文本方式显示，用SELECT语句输语句输出的结果将在结果窗口中以表格方式显示出的结果将在结果窗口中以表格方式显示 使用PRINT语句要求以单个变量或字符串表达式作为参数，而“+”运算符作为连接两个字符串的连接符，要求“+”运算符两侧的操作的数据类型必须是一致。 如下语句会报错。 解决方式是使用转换函数，把数值转换为字符串，如下所示。或类型转换函数PRINT 当前错误号当前错误号 + ERRORPRINT 当前错误号当前错误号 + CONVERT(VARCHAR(5),ERROR)PRINT 当前错误号当前错误号 + CAST(ERROR AS VARCHAR(5)n需求说明u（1）为学生信息表Student插入一条数据。u（2）更新Student中学号为G1463341的学生信息n 演示代码【演示3】全局变量的 演示【演示3】全局变量的 演示INSERT INTO Student (StudentNo,StudentName,GradeId,Sex,Phone,BornDate,IdentityCard) VALUES(G1463341,武松武松,1,男男PRINT 当前错误号当前错误号+CONVERT(varchar(5),ERROR)UPDATE Student WHERE StudentNo=G1463341PRINT 当前错误号当前错误号+CONVERT(varchar(5),ERROR)GOn执行结果 ERROR用于表示最近一条SQL语句是否有错误。如果有错误，则返回非零的值。第一次INSERT语句没错，所以为0；第二次UPDATE语句违反了身份证号码不能超过18位的数据类型，所以错误号不为0。【演示3】全局变量的 演示n技能要点u 使用变量保存查询数据和数据类型的转换。 n 需求说明u 查询学号是G1363302的学生姓名和年龄，并输出比他大1岁或小1岁的学生信息。实训2-使用类型转换函数输出n关键点分析u 使用日期函数DATEDIFF()和数学函数FLOOR()，根据出生日期计算得到学生年龄u 为了将姓名和年龄在一行输出，首先使用CAST()将整数类型的年龄转换为字符类型，然后使用字符串连接符“+”将姓名和转换后的年龄相连。u 使用日期函数DATEPART()获得学生的出生年份。u 为了把输出的表格数据和文本消息显示在同一个窗口中，需要做如下设置。选择“查询结果”“SQL Server” “常规”选项，将“显示结果的默认方式”设置为“以文本格式显示结果”。实训2-使用类型转换函数输出一起努力 共同提高第1章 创建和管理数据库 变量的使用1 1输出语句 逻辑控制语句2 23 3目 录nBEGIN-END语句块 BEGIN-END语句将多个T-SQL语句定义成一个语句块，把每个语句块视为一个单元处理。逻辑控制语句 BEGIN语句或语句块 END 语法语法nIF-ELSE语句 IF-ELSE语句属于分支结构，它与C#编程语言的IF语句类似，也是根据条件是否成立来确定程序执行方向的逻辑控制语句IF(条件) 语句或语句块1ELSE 语句或语句块2 语法语法n 需求说明u 查询C语言程序设计最近一次考试的日期。u 统计平均成绩并存入局部变量。u 如果平均分在70以上，则显示“考试成绩优秀”，并显示前3名学生的考试信息。u 如果平均分在70以下，则显示“考试成绩较差”，并显示后3名学生的考试信息。【演示4】统计最近一次考试情况n 演示代码【演示4】统计最近一次考试情况-查询C语言程序设计课程最近一次考试的日期DECLARE date datetime -考试日期SELECT date=MAX(ExamDate) FROM Result INNER JOIN Subject ON Result.SubjectId=Subject.SubjectId WHERE SubjectName=C语言程序设计语言程序设计 -查询获得本次考试的平均分DECLARE myavg decimal(5,2) -平均分SELECT myavg=AVG(StudentResult) FROM ResultINNER JOIN Subject ON Result.SubjectId=Subject.SubjectIdWHERE SubjectName=C语言程序设计语言程序设计 AND ExamDate=datePRINT 平均分：平均分：+CONVERT(varchar(5),myavg)n 演示代码（续）【演示4】统计最近一次考试情况IF(myavg=70)BEGIN PRINT 考试成绩优秀，前三名的成绩为考试成绩优秀，前三名的成绩为 SELECT TOP 3 StudentNo,StudentResult FROM WHERE SubjectName=C语言程序设计语言程序设计 AND ExamDate=date ORDER BY StudentResult DescENDELSEBEGIN PRINT 考试成绩较差，后三名的成绩为考试成绩较差，后三名的成绩为 SELECT TOP 3 StudentNo,StudentResult FROM WHERE SubjectName=C语言程序设计语言程序设计 AND ExamDate=date ORDER BY StudentResultENDn技能要点u IF-ELSE条件语句。n需求说明u（1）查询学号是G1263201的学生“软件测试技术”科目最近一次成绩的考试，并输出学生姓名和考试对应等级信息。u（2）如果成绩大于85分，则显示“优秀”；如果大于70分，则显示“良好”；如果大于60分，则显示“中等”；否则显示“差”。实训3-使用IF-ELSE语句n关键点分析u（1）用TOP关键字和连接查询语句查出学号是G1263201的学生“软件测试技术”科目最近一次考试的成绩。u（2）判断该学生的考试成绩所在范围，输出相应的提示信息。实训3-使用IF-ELSE语句一起努力 共同提高nCASE多分支语句 CASE-END语句计算一组条件表达式，并返回其中一个符合条件的结果。逻辑控制语句CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果END 语法语法n 需求说明u采用A-E五级打分制显示学生“C语言程序设计”科目最近一次的考试成绩 A级：90分以上（含90分）。 B级：80-89分。 C级：70-79分。 D级：60-69分。 E级：60分以下。【演示5】采用五级打分制显示成绩n 演示代码【演示5】采用五级打分制显示成绩DECLARE date datetimeSELECT date=MAX(ExamDate) FROM ResultINNER JOIN Subject ON Result.SubjectId=Subject.SubjectIdWHERE SubjectName=C语言程序设计语言程序设计SELECT 学号学号=StudentNo,成绩成绩=CASE WHEN StudentResult60 THEN E WHEN StudentResult BETWEEN 60 AND 69 THEN D WHEN StudentResult BETWEEN 70 AND 79 THEN C WHEN StudentResult BETWEEN 80 AND 89 THEN B ELSE A ENDFROM ResultINNER JOIN Subject ON Result.SubjectId=Subject.SubjectIdWHERE SubjectName=C语言程序设计语言程序设计 AND ExamDate=daten 技能要点u CASE-END语句。n 需求说明u 根据C语言程序设计科目最近一次考试的成绩，显示每个学生的等级。等级如下。 90分以上：。 80-89分： 。 70-79分： 。 60-69分： 。 60分以下，你要努力了！。实训4-使用CASE多分支语句一起努力 共同提高nWHILE循环语句 WHILE循环语句可以根据某些条件重复执行一条SQL语句或一个语句块。只要指定的条件为true，就会重复执行语句或语句块，直至指定条件为false为止。在WHILE循环语句中可以使用CONTINUE和BREAK语句来控制语句的执行。逻辑控制语句WHILE(条件)BEGIN语句或语句块BREAK|CONTINUEEND 语法语法n 需求说明u 检查“C语言程序设计”科目最近一次考试是否有不及格（60分及格）的学生。如果有不及格的学生，则每人加2分，高于95分的学生不再加分，直至所有学生此次考试成绩及格为止。【演示6】WHILE循环的应用n 演示代码【演示6】WHILE循环的应用DECLARE date datetime -考试时间DECLARE subNo int -课程编号SELECT subNo=SubjectId FROM Subject WHERE SubjectName=C语言程序设计语言程序设计 SELECT date=MAX(ExamDate) FROM Result WHERE SubjectId=subNoDECLARE n int n 演示代码（续）【演示6】WHILE循环的应用WHILE(1=1) -条件永远成立 BEGIN SELECT n=COUNT(*) FROM Result WHERE SubjectId=subNo AND ExamDate=date AND StudentResult0) -每人加每人加2分分 UPDATE Result SET StudentResult=StudentResult+2 WHERE SubjectId=subNo AND ExamDate=date AND StudentResult=95 ELSE BREAK -退出循环 ENDPRINT 加加分后的成绩如下分后的成绩如下： n技能要点u WHILE循环语句。 n需求说明u 检查C语言程序设计最近一次考试是否有不及格的学生。如果有，则每人加2分直至全部学生都及格。如果学生成绩大于100，则按100分计算。n关键点分析u 用WHILE语句查询C语言程序设计科目最近一次考试的成绩。u 用IF语句判断是否存在不及格的考试成绩。实训5-使用WHILE循环语句一起努力 共同提高 批处理是一条或多条SQL语句的集合，SQL Server将批处理指令编译成一个可执行单元，此单元称为执行计划。每个批处理可编译成单个执行计划，从而提高执行效率。 以一条命令的方式来处理一组命令的过程称为批处理。由于每个批处理之间都是独立的，并不会影响其他批处理中SQL代码的运行。 批处理的主要好处是能够简化数据库的管理。 GO关键字标志着批处理的结束。批处理n需求说明u 利用批处理完成学校对考试不及格学生的处理操作。u 凡是一次考试不及格者，给予警告；3次（含）以下不及格者，按肄业处理；3次以上不及格者，开除学籍u 创建临时表Punish，表结构如下。 处罚记录（学号、不及格次数、处理意见）。 创建新表时处理意见为空。u 查询所有不及格的成绩并插入Punish表。u 根据每个学生不及格的次数批量更新处理意见。【演示7】利用批处理操作n 演示代码【演示7】利用批处理操作-批处理CREATE TABLE Pubnish( -创建表StuNo nvarchar(50) NOT NULL, -学号 StuCnt int NOT NULL, -不及格次数 StuMng varchar(50) -处理意见 )-批处理INSERT INTO PubnishSELECT StudentNo 学号学号,COUNT(0) 不及格次数不及格次数, 处理意见处理意见FROM Result WHERE StudentResult 3GO-批处理SELECT * FROM PubnishGOn变量的使用是先声明，再赋值。局部变量前必须有“”作为前缀，全局变量前必须有两个“”作为前缀。n变量的赋值有两种方式：SET语句和SELECT语句。n输出结果也有两种方式：PRINT语句和SELECT语句。n数据类型转换的两个函数：CAST()和CONVERT()。n控制流语句提供了条件操作所需的顺序和逻辑。n语句块使用BEGIN-END。n批处理可以提高语句执行的速率，批处理结束的标志是“GO”。总结一起努力 共同提高