T-SQL语言编程.doc

课程名称：数据库基础与应用信息与控制工程学院第八章 T-SQL语言编程1.回顾上一章: 10分钟22.课程知识点讲解：22.1.变量的定义与赋值：25分钟22.2.输出显示数据：1分钟32.3.if、 while、 case 逻辑控制语句：30分钟42.4.sql中的批：30分钟53.本章总结 10分钟74.考核点75.测试题76.扩展部分 常见编程错误77.学生问题汇总78.作业7l 本章主要目标n 本章的目标是向学生介绍T-SQL语言编程的概念与实现。1 掌握如何定义变量并赋值2 掌握如何输出显示数据3 掌握IF、 WHILE 、CASE逻辑控制语句4 理解SQL中批的概念l 本章重点n 掌握如何定义变量并赋值n 掌握IF、 WHILE 、CASE逻辑控制语句n 理解SQL中批的概念l 本章难点n CASE-END 多分支语句1. 回顾上一章:10分钟建库的语法？补充下列语句：IF EXISTS（SELECT * FROM ? WHERE name=stuDB） DROP DATABASE stuDB建表的语法？补充下列语句：IF EXISTS（SELECT * FROM ? WHERE name=stuDB） DROP TABLE stuInfo解释下列语句的含义？EXEC sp_addlogin zhangsan, 1234 EXEC sp_grantdbaccess zhangsan, zhangsanDBUser ALTER TABLE stuInfo ADD CONSTRAINT ? PRIMARY KEY (stuNo) ALTER TABLE stuInfo ADD CONSTRAINT UQ_stuID ? ALTER TABLE stuInfo ADD CONSTRAINT DF_stuAddress ? ALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(?) ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY ? REFERENCES ?2. 课程知识点讲解：2.1. 使用变量：25分钟l 知识点讲解1和C语言一样，变量分为：q 局部变量：q 局部变量必须以标记作为前缀 ，如ageq 局部变量的使用也是先声明，再赋值 q 全局变量：q 全局变量必须以标记 作为前缀，如versionq 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值 注意和c语言中的对比。 引例讲解：声明局部变量 DECLARE 变量名 数据类型例如：DECLARE name varchar(8)DECLARE seat int赋值 SET 变量名 =值 SELECT 变量名 = 值例如：SET name=张三SELECT name=stuName FROM stuInfo WHERE stuNo=s25302 讲解要点：1.先声明再赋值；2.赋值有两种方式： 1）使用set 2）使用select 讲讲它们分别应在什么场合使用。 set用于普通的赋值 select用于从表中查询数据并赋值3. 使用select语句赋值时，必须保证筛选的记录只有1条，否则取最后一条，可演示证明。 所以TSQL语句后面一般接WHERE筛选条件。学生信息表如下：问题：编写T-SQL查找李文才的左右同桌 ？分析：第一步，找出“李文才“的座位号；第二步，李文才的座位号加1或减1 提问： 如何编写T-SQL查找李文才的左右同桌 ？ 即张秋丽和李斯文，向学生解释，为了简单起见，这里的同桌指左右两边的同学。引导： 第一步，找出“李文才“的座位号； 第二步，李文才的座位号加1或减1 让学生先在练习本上编写，然后再讲解答案。说明：为了演示方便，演示前可预先使用企业管理器修改数据。为了修改数据方便，可以在表的设计视图中修改表结构，取消座位列stuSeat标识列的设置。为了设置显示结果的字体，可以在查询分析器中，单击菜单：工具-选项，在“字体”面板中，选择左边的结果表格，然后设置放到字体即可，放大编辑器中的SQL文本字体也同理。/*-查找李文才的信息-*/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 = seat-1)GO全局变量都使用两个标志作为前缀变量含义ERROR最后一个T-SQL错误的错误号IDENTITY最后一次插入的标识值LANGUAGE当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目ROWCOUNT受上一个SQL语句影响的行数SERVERNAME本地服务器的名称TRANSCOUNT当前连接打开的事务数VERSIONSQL Server的版本信息老师提问：问题1：全局变量和局部变量的区别？答：全局变量是系统定义，用户可以使用的变量。局部变量是用户定义用户使用的变量2.2. 输出显示数据：1分钟语法格式： 常用的输出语句有两种；1 print 局部变量或者字符串2 SELECT 变量 AS 自定义列名 示例Print 服务器的名称： +SERVERNAME SELECT SERVERNAME AS 服务器名称2.3. if-else 语句：10分钟IF （条件） BEGIN 语句1 语句2 ENDELSE BEGIN 语句1； 语句2； END1 语法格式：同C语言一样：l ELSE是可选部分l 如果有多条语句，才需要BEGIN-END语句块 /*生活范例名称：求职前提条件：上网站查询工作信息 If (薪水)显示满足条件的工作招聘信息Else 显示“无符合条件的招聘信息，请重新选择”示例如下：学生信息表：问题：统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学生的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学生的考试信息 。分析：第一步，统计平均成绩存入临时变量；第二步，用IFELSE判断；WHILE （条件） BEGIN 语句1 语句2 BREAK END2.4. while循环语句：10分钟 语法格式：讲解要点：1. 先从C语言的while语句，引出SQL的WHILE语句，便于学生接受。2. 强调： 1）BREAK的含义 2）如果有多条语句，才需要BEGIN-END语句块 如学生成绩单问题：本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。分析：第一步，统计没通过的人数 ；第二步，如果有人没通过，加分；第三步，循环判断。 讲解时要注意：1.向学生仔细说明循环加分规则。2.提问学生编写T-SQL的实现思路。3.让学生在练习本上预先编写，然后在公布答案，这样他们才会记忆深刻。4.可以给出适当提示：统计函数select count(*) from stuMarks where writtenExam60用于统计没通过的学生人数。2.5. CASE多分支语句：10分钟CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果END语法格式：问题：采用美国的ABCDE五级打分制来显示笔试成绩。 A级: 90分以上 B级： 8089分 C级: 7079分 D级： 6069分 E级： 60分以下使用如下学生成绩表讲解要点：CASEEND的语法比较难于理解，要注意放慢速度讲解。1.多分支部分整体是一条非常简单的语句：SELECT stuNo, 成绩=某个值 FROM stuMarks2.成绩是别名，等价使用AS。3.成绩后的值不确定，它的值由CASEEND多分支决定。CASE语句根据每条记录中笔试成绩，进行判断，结果是A还是B等。4.学生明白后，强调CASEEND的语法配对，如蓝色标识的部分。课堂练习：请根据平均分和下面的评分规则，编写T-SQL语句查询学生的成 绩，如上图所示。 优 ：90分以上 良 ：8089分 中 ：7079分 差 ：6069分 不及格 ：60分以下由于CASEEND对于学生来说难于理解，补充示例讲解。1.应该请学生在练习本上编写，并视情况抽取个别学生到讲台用白版笔书写。2. 给予提示：求平均分： (writtenExam+labExam)/260 3.然后讲解答案，指出学生的错误。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+labExam)/2 BETWEEN 80 AND 89 THEN 良 ElSE 优 END FROM stuMarks2.6. 批处理语句：30分钟批处理的概念 引入生活范例：/*生活范例名称：多人排队进门 * *要 点：帮助理解批处理的概念十个人进一个门，每个人都要做开门关门的动作。然后是十个人依次进门，只是头一个开门，最后一个关门。比较两种操作序列的优缺点。关门的动作标志进门的结束，在SQL中对应的批处理的结束标志是GO批处理是包含一个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Server执行SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条SELECT * FROM stuInfoSELECT * FROM stuMarksUPDATE stuMarks SET writtenExam=writtenExam+2 GOl GO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率l 一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定 此时应该强调：1.批处理的标志GO2.批处理的好处 速度快等SQLServer规定：如果是建库、建表语句、以及我们后面学习的存储过程和视图等，则必须在语句末尾添加 GO 批处理标志 再次强调语句规范性3. 本章总结 10分钟l 和c语言非常相似，变量的使用也是先声明，然后再赋值局部变量前面必须有一个标记，全局变量前面是两个标记。l 变量的赋值有两种方式：使用SET 语句或SELECT语句。 l 输出结果也有两种方式：print语句和 SELECT语句。 l 控制流语句提供了条件操作所需的顺序和逻辑。l 语句块使用BEGIN 。END。l 批处理可以提高语句执行的效率，批处理结束的标志是GO。4. 考核点 考核点1：局部变量和全局变量的区别以及分别赋值 考核点2：批处理的结束标志以及sql server中对一些sql必须用go结束语句特殊要求5. 测试题 测试题1： 测试题2：6. 扩展部分 常见编程错误 扩展部分1：oracle中的pl/sql语句一行不能太长，强调规范性 扩展部分2：T-SQL调用动态链接库 一般我们要根据数据库的纪录变化时，进行某种操作。我们习惯的操作方式是在程序中不停的查询表，判断是否有新纪录。这样耗费的资源就很高，如何提高这种效率，我想在表中创建触发器，在触发器中调用外部动态连接库通过消息或事件通知应用程序就可实现。而master的存储过程中最好能调用外部的动态连接库，我们在触发器中调用master的存储过程即可。我们来实现一个存储过程中调用外部的dll(storeproc.dll)的函数SetFileName和addLine。存储过程如下(需放到master库中)： CREATE PROCEDURE sp_testdll ASexec sp_addextendedproc SetFileName, storeproc.dll -声明函数exec sp_addextendedproc addLine, storeproc.dll declare szFileName varchar(200) declare szText varchar(200)declare rt intSelect szFileName = c:welcome.txtEXEC rt = SetFileName szFileName -调用SetFileName函数，参数为-szFileName;if rt = 0beginselect szText = welcome 01Exec rt = addLine szText -调用addLineselect szText = welcome 02Exec rt = addLin