数据库SQL编程教程.ppt

《数据库SQL编程教程.ppt》由会员分享，可在线阅读，更多相关《数据库SQL编程教程.ppt（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、LOGO第十章第十章T-SQLT-SQL编程程1目目标v 掌握如何定掌握如何定义变量并量并赋值v 掌握如何掌握如何输出出显示数据示数据v 掌握掌握IF、WHILE、CASE逻辑控制控制语句句v 理解理解SQL中批中批处理的概念理的概念使用使用变量量 变量分为：变量分为：v局部变量：局部变量：局部变量必须以标记作为前缀，如age局部变量的使用也是先声明，再赋值 v全局变量：全局变量：全局变量必须以标记 作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值 局部局部变量量 例如：例如：DECLARE name varchar(8)DECLARE seat intv

2、声明局部变量声明局部变量 DECLARE 变量名变量名 数据类型数据类型v赋值赋值 SET 变量名变量名=值值 SELECT 变量名变量名=值值例如：例如：SET name=张三张三SELECT name=stuName FROM stuInfo WHERE stuNo=s25302 必须确保筛选出的记录只有必须确保筛选出的记录只有1条条 或 局部局部变量示例量示例问题：问题：编写编写T-SQL查找李文才的左右同桌查找李文才的左右同桌？学员信息表学员信息表分析：分析：第一步，找出第一步，找出“李文才李文才“的座位号；的座位号；第二步，李文才的座位号加第二步，李文才的座位号加1 1或减或减1 1

3、 局部局部变量示例量示例/*-查找李文才的信息查找李文才的信息-*/DECLARE name varchar(8)-学员姓名学员姓名SET name=李文才李文才 -使用使用SET赋值赋值SELECT*FROM stuInfo WHERE stuName=name/*-查找李文才的左右同桌查找李文才的左右同桌-*/DECLARE seat int -座位号座位号SELECT seat=stuSeat FROM stuInfo -使用使用SELECT赋值赋值 WHERE stuName=nameSELECT*FROM stuInfo WHERE(stuSeat=seat+1)OR(stuSeat

4、=seat-1)GO演示：使用局部变量演示：使用局部变量 v 参考参考语句句全局全局变量量 v全局变量都使用两个全局变量都使用两个标志作为前缀标志作为前缀 变量变量含义含义ERROR最后一个最后一个T-SQL错误的错误号错误的错误号IDENTITY最后一次插入的标识值最后一次插入的标识值LANGUAGE当前使用的语言的名称当前使用的语言的名称MAX_CONNECTIONS 可以创建的同时连接的最大数目可以创建的同时连接的最大数目ROWCOUNT受上一个受上一个SQL语句影响的行数语句影响的行数SERVERNAME本地服务器的名称本地服务器的名称TRANSCOUNT当前连接打开的事务数当前连接打

5、开的事务数VERSIONSQL Server的版本信息的版本信息全局全局变量示例量示例print SQL Server的版本的版本+VERSION print 服务器的名称服务器的名称:+SERVERNAME INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge)VALUES(武松武松,s25328,男男,23)-如果大于如果大于0表示上一条语句执行有错误表示上一条语句执行有错误print 当前错误号当前错误号+convert(varchar(5),ERROR)print 刚才报名的学员，座位号为刚才报名的学员，座位号为:+convert(varcha

6、r(5),IDENTITY)UPDATE stuinfo SET stuAge=85 WHERE stuName=李文才李文才print 当前错误号当前错误号+convert(varchar(5),ERROR)GO演示：使用全局变量演示：使用全局变量 v 参考参考语句句SQL Server的版本的版本 服务器名称服务器名称 座位号（自动编号）座位号（自动编号）错误号错误号 错误号错误号 逻辑控制控制语句句vT-SQL语言提供了一些可用于改言提供了一些可用于改变语句句执行行顺序的命令序的命令,称称为流程流程控制控制语句句.v 流程控制流程控制语句与常句与常见的程序的程序设计语言言类似似,主要包括

7、以下几种主要包括以下几种:beginendifelsecasewhile.continue.breakgoto逻辑控制控制语句句vIFELSE语句句SQL中的中的IF-ELSE语句语句IF（条件）（条件）BEGIN 语句语句1 语句语句2 ENDELSE BEGIN 语句语句1；语句语句2；ENDlELSE是可选部分是可选部分l如果有多条语句，才需要如果有多条语句，才需要BEGIN-END语句块语句块 IF-ELSE示例示例问题：问题：统计并显示本班笔试平均分，如果平均分在统计并显示本班笔试平均分，如果平均分在70以上，显示以上，显示“成绩优秀成绩优秀“，并显示前三名学员的考试信息；如果在，并

8、显示前三名学员的考试信息；如果在70以下，显示以下，显示“本班成绩较差本班成绩较差“，并显示后三名学员的考试信息，并显示后三名学员的考试信息。学员成绩表学员成绩表分析：分析：第一步，统计平均成绩存入临时变量；第一步，统计平均成绩存入临时变量；第二步，用第二步，用IFELSE判断；判断；IF-ELSE示例示例设置置输出出结果的格式果的格式v为了文本消息和了文本消息和输出出结果果显示在同一窗口，需要示在同一窗口，需要设置置输出出结果的格式果的格式逻辑控制控制语句句vWHILE循循环语句句SQL中的中的WHILE语句语句WHILE（条件）（条件）BEGIN 语句语句1 语句语句2 BREAK END

9、BREAK表示退出循环表示退出循环如果有多条语句，才需要如果有多条语句，才需要BEGIN-END语句块语句块 WHILE示例示例问题：问题：本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加分，看是否都通过，如果没有全部通过，每人再加2分，分，再看是否都通过，如此反复提分，直到所有人都通过为止再看是否都通过，如此反复提分，直到所有人都通过为止。学员成绩表学员成绩表分析：分析：第一步，统计没通过的人数第一步，统计没通过的人数；第二步，如果有人没通过，

10、加分；第二步，如果有人没通过，加分；第三步，循环判断。第三步，循环判断。DECLARE n intWHILE(1=1)-条件永远成立条件永远成立 BEGIN SELECT n=COUNT(*)FROM stuMarks WHERE writtenExam0)UPDATE stuMarks -每人加每人加2分分 SET writtenExam=writtenExam+2 ELSE BREAK -退出循环退出循环 ENDprint 加分后的成绩如下：加分后的成绩如下：SELECT*FROM stuMarksWHILE示例示例v参考参考语句句逻辑控制控制语句句vCASEEND多分支多分支语句句CAS

11、E WHEN 条件条件1 THEN 结果结果1 WHEN 条件条件2 THEN 结果结果2 ELSE 其他结果其他结果ENDCASE-END示例示例问题：问题：采用美国的采用美国的ABCDE五级打分制来显示笔试成绩。五级打分制来显示笔试成绩。A级级:90分以上分以上 B级：级：8089分分 C级级:7079分分 D级：级：6069分分 E级：级：60分以下分以下学员成绩表学员成绩表print ABCDE五级显示成绩如下：五级显示成绩如下：SELECT stuNo,成绩成绩=CASE WHEN writtenExam60 THEN E WHEN writtenExam BETWEEN 60 AN

12、D 69 THEN D WHEN writtenExam BETWEEN 70 AND 79 THEN C WHEN writtenExam BETWEEN 80 AND 89 THEN B ElSE A END FROM stuMarksCASEEND示例示例v参考参考语句句CASE-END课堂堂练习课堂练习：课堂练习：请根据平均分和下面的评分规则，编写请根据平均分和下面的评分规则，编写T-SQL语句查询学员的成语句查询学员的成 绩，如上绩，如上图所示。图所示。优优：90分以上分以上 良良：8089分分 中中：7079分分 差差：6069分分 不及格不及格：60分以下分以下学员成绩分析学员成

13、绩分析CASE-END练习答案答案USE stuDBGOSELECT 考号考号=ExamNo,学号学号=stuNo,笔试笔试=writtenExam,机试机试=labExam,平均分平均分=(writtenExam+labExam)/2,等级等级=CASE WHEN(writtenExam+labExam)/260 THEN 不及格不及格 WHEN(writtenExam+labExam)/2 BETWEEN 60 AND 69 THEN 差差 WHEN(writtenExam+labExam)/2 BETWEEN 70 AND 79 THEN 中中 WHEN(writtenExam+labE

14、xam)/2 BETWEEN 80 AND 89 THEN 良良 ElSE 优优 END FROM stuMarks批批处理理语句句v批处理是包含一个或多个批处理是包含一个或多个 SQL 语句的组，从应语句的组，从应用程序一次性地发送到用程序一次性地发送到SQL Server执行执行vSQL Server 将批处理语句编译成一个可执行单将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每元，此单元称为执行计划。执行计划中的语句每次执行一条次执行一条客户端应用程序客户端应用程序SQL Server服务器服务器批处理语句：批处理语句：语句语句1语句语句2GO批批处理理语句示例句

15、示例SELECT*FROM stuInfoSELECT*FROM stuMarksUPDATE stuMarks SET writtenExam=writtenExam+2 GOlGO是批处理的标志，表示是批处理的标志，表示SQL Server将这些将这些T-SQL语句编译为一个执行语句编译为一个执行单元，提高执行效率单元，提高执行效率l一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定需求和代码编写者决定 批批处理理语句示例句示例-例如建表语句的末尾必须添加例如建表语句的末尾必须添加GOCREA

16、TE TABLE stuInfo (.)GOvSQLServer规定：如果是建库、建表语句、以规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在及我们后面学习的存储过程和视图等，则必须在语句末尾添加语句末尾添加 GO 批处理标志批处理标志课堂堂综合合练习课堂练习：课堂练习：则根据如下规则对机试成绩进行反复加分，直到平均分超过则根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止。分为止。请编写请编写T-SQL语句实现。语句实现。90分以上：分以上：不加分不加分8089分：分：加加1分分7079分：分：加加2分分6069分：分：加加3分分60分以下：分以下：加加5分

17、分加分前加分前加分后加分后课堂堂综合合练习答案答案SELECT*FROM stuMarks -原始成绩原始成绩DECLARE labAvg INTWHILE(1=1)BEGIN UPDATE stuMarks SET labExam=CASE WHEN labExam=85 BREAK ENDSELECT*FROM stuMarks -加分后的成绩加分后的成绩声明变量，用户临时存放平均分声明变量，用户临时存放平均分循环加分循环加分根据机试成绩酌情加分，根据机试成绩酌情加分，整个是一个整个是一个UPDATE语句语句获取目前的平均分，判断是否还继续加分获取目前的平均分，判断是否还继续加分T-SQL

18、语句的句的综合合应用用学员信息表和成绩表学员信息表和成绩表 应到人数：应到人数：5人人实到人数实到人数4人，人，缺考缺考1人人T-SQL语句的句的综合合应用用如何实现？如何实现？本次考试的缺考情况本次考试的缺考情况 比较笔试平均分和机试平比较笔试平均分和机试平均分，较低者进行循环提均分，较低者进行循环提分，但提分后最高分不能分，但提分后最高分不能超过超过97分分。加分后重新。加分后重新统计通过情况统计通过情况统计通过率统计通过率 T-SQL语句的句的综合合应用用1.提示提示:使用子查询统计缺考情况：使用子查询统计缺考情况：应到人数：应到人数：SELECT count(*)FROM stuInf

19、o实到人数：实到人数：SELECT count(*)FROM stuMarks2.提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、提取学员的成绩信息并保存结果，包括学员姓名、学号、笔试成绩、机试成绩、是否通过是否通过1)提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（提取的成绩信息包含两表的数据，所以考虑两表连接，使用左连接（LEFT JOIN）；）；SELECT stuNameFROM stuInfo LEFT JOIN stuMarks 2)要求新加一列要求新加一列“是否通过（是否通过（isPass）”，可采用，可采用CASE END。为了便于后续的。为了

20、便于后续的通过率统计，通过则为通过率统计，通过则为1，没通过为，没通过为0 SELECT isPass=CASE WHEN writtenExam=60 THEN 1 ELSE 0 END 3)要求保存提取（查询）的结果，可以使用我们曾学习过的要求保存提取（查询）的结果，可以使用我们曾学习过的SELECT INTO newTable语句，生成新表并保存数据语句，生成新表并保存数据 T-SQL语句的句的综合合应用用3.比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超比较笔试平均分和机试平均分，对较低者进行循环提分，但提分后最高分不能超过过97分：分：1)使用使用IF语句语句

21、判断笔试还是机试偏低，决定对笔试还是机试提分；判断笔试还是机试偏低，决定对笔试还是机试提分；2)使用使用WHILE循环循环给每个学员加分，缺考的除外，当最高分超过给每个学员加分，缺考的除外，当最高分超过97分时退出循环；分时退出循环；3)因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需因为给每位学员的笔试或机试提分了，有的学员可能提分后刚好通过了，所以需要更新要更新isPass（是否通过）列。（是否通过）列。UPDATE newTable SET isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1 ELSE 0 EN

22、DT-SQL语句的句的综合合应用用4.提分后，统计学员的成绩和通过情况：提分后，统计学员的成绩和通过情况：1）使用别名实现中文字段名，即）使用别名实现中文字段名，即SELECT 姓名姓名=stuName,学号学号=stuNo2）如果某个学员的成绩为）如果某个学员的成绩为NULL(空空)，则替换为，则替换为”缺考缺考”，否则原样显示；，否则原样显示；3）isPass列中的列中的1替换为是，替换为是，0替换为否；替换为否；SELECT ,机试成绩机试成绩=CASE WHEN labExam IS NULL THEN 缺考缺考 ELSE convert(varchar(5),labExam)END

23、,是否通过是否通过=CASE WHEN isPass=1 THEN 是是 ELSE 否否 ENDT-SQL语句的句的综合合应用用5.提分后统计学员的通过率情况：提分后统计学员的通过率情况：1）通过人数：因为通过用）通过人数：因为通过用1表示，没通过用表示，没通过用0表示，所以表示，所以isPass列的累加和即是列的累加和即是通过人数；通过人数；2）通过率：同理，）通过率：同理，isPass列的平均值列的平均值*100即是通过率；即是通过率；T-SQL参考参考语句句/*-本次考试的原始数据本次考试的原始数据-*/-SELECT*FROM stuInfo-SELECT*FROM stuMarks/

24、*-统计考试缺考情况统计考试缺考情况-*/SELECT 应到人数应到人数=(SELECT count(*)FROM stuInfo),-应到人数为子查应到人数为子查询表达式的别名询表达式的别名 实到人数实到人数=(SELECT count(*)FROM stuMarks),缺考人数缺考人数=(SELECT count(*)FROM stuInfo)-(SELECT count(*)FROM stuMarks)T-SQL参考参考语句句/*-统计考试通过情况，并将结果存放在新表统计考试通过情况，并将结果存放在新表newTable中中-*/IF EXISTS(SELECT*FROM sysobjec

25、ts WHERE name=newTable)DROP TABLE newTableSELECT stuName,stuInfo.stuNo,writtenExam,labExam,isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1 ELSE 0 END INTO newTable FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo-SELECT*FROM newTable-查看统计结果，可用于调试查看统计结果，可用于调试 T-SQL参考参考语句句/*-酌情加分：

26、比较笔试和机试平均分，决定加哪门酌情加分：比较笔试和机试平均分，决定加哪门-*/DECLARE avgWritten numeric(4,1)DECLARE avgLab numeric(4,1)SELECT avgWritten=AVG(writtenExam)FROM newTable WHERE writtenExam IS NOT NULLSELECT avgLab=AVG(labExam)FROM newTable WHERE labExam IS NOT NULLIF avgWritten=97 BREAK ENDELSE 略略-循环给笔试加分，最高分不能超过循环给笔试加分，最高分

27、不能超过97分分T-SQL参考参考语句句-因为提分，所以需要更新因为提分，所以需要更新isPass（是否通过）列的数据（是否通过）列的数据UPDATE newTable SET isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1 ELSE 0 END-SELECT*FROM newTable -可用于调试可用于调试/*-显示考试最终通过情况显示考试最终通过情况-*/SELECT 姓名姓名=stuName,学号学号=stuNo ,笔试成绩笔试成绩=CASE WHEN writtenExam IS NULL THEN 缺考缺考 ELSE co

28、nvert(varchar(5),writtenExam)END,机试成绩机试成绩=CASE WHEN labExam IS NULL THEN 缺考缺考 ELSE convert(varchar(5),labExam)END,是否通过是否通过=CASE WHEN isPass=1 THEN 是是 ELSE 否否 END FROM newTable T-SQL参考参考语句句/*-显示通过率及通过人数显示通过率及通过人数-*/SELECT 总人数总人数=count(*),通过人数通过人数=SUM(isPass),通过率通过率=(convert(varchar(5),AVG(isPass*100)+%)FROM newTable 总结v变量的量的赋值有两种方式：使用有两种方式：使用SET语句或句或SELECT语句。句。v输出出结果也有两种方式：果也有两种方式：print语句和句和SELECT语句。句。v控制流控制流语句提供了条件操作所需的句提供了条件操作所需的顺序和序和逻辑。v语句句块使用使用BEGINEND。v批批处理可以提高理可以提高语句句执行的效率，批行的效率，批处理理结束的束的标志是志是“GO”。