清远职业技术学院教案.doc

清远职业技术学院教案第_5_次（单元）课 授课时间： 第810周 课程名称SQL SERVER数据库技术专业班级 层次大专授课教师 职称 课 型(大、小)小学时8授课题目（章、节）项目五 编程实现公司数据库系统的操作授课方式理论课；实训课教材及主要参考书SQL Server 2000 数据库教学目的与要求：1能正确理解和使用SQL Server变量2能正确理解和使用SQL Server 函数3会使用流程控制语句编写顺序结构、选择结构和循环结构的程序教学过程设计(内容、时间安排、教学方法等)：理论课4学时实训课4学时教学重点、难点：重点：顺序结构、选择结构和循环结构难点：SQL Server 函数实施情况及小结： 基 本 内 容项目五 实现公司数据库系统的编程一、教学目标终极目标：会利用变量、函数和流程控制语句编写Transact-SQL程序促成目标：1能正确理解和使用SQL Server变量2能正确理解和使用SQL Server 函数3会使用流程控制语句编写顺序结构、选择结构和循环结构的程序二、工作任务通过使用Transact-SQL语句编程，实现公司管理数据库系统的操作。模块1 编程获取公司某雇员的工作业绩信息一、教学目标1能正确理解Transact-SQL批处理2会用Transact-SQL语句编程获取对用户有用的信息3能正确理解和使用SQL Server变量4能正确理解和使用SQL Server 函数二、工作任务通过Transact-SQL编程，获取名为王孔若的姓名、性别、出生年月及雇佣日期等信息；然后输出王孔若所做订单的明细表、所做的订单数目及订单总金额。三、相关实践知识完成本项目工作任务的操作步骤如下：1打开查询分析器窗口，在窗口中输入以下Transact-SQL语句： USE companyInfo-定义SQL server的变量，必须以开头命名变量，用DECLARE定义变量DECLARE emp_Id int DECLARE name varchar(8)DECLARE sex char(2)DECLARE date1 datetimeDECLARE date2 datetime-为变量赋值SET name=王孔若 -通过查询语句将字段的值赋值给变量SELECT emp_Id=雇员id,sex=性别,date1=出生年月 ,date2=雇佣日期 FROM employee WHERE 姓名=name -用PRINT语句结合函数格式化输出变量的值PRINT 姓名: +name+ 性别：+sex + 出生年月：+convert(char(4),year(date1)+ 年 +convert(char(2),month(date1)+ 月 +convert(char(2),day(date1)+ 日 + 雇用日期：+convert(char(4),year(date2)+ 年 +convert(char(2),month(date2)+ 月 +convert(char(2),day(date2)+ 日 -输出雇员王孔若完成的订单明细SELECT customer.公司名称,customer.联系人姓名, product.产品名, P_order.数量,P_order.定货日期, employee.姓名, product.单价 FROM employee,customer,product,P_order WHERE employee.雇员id= emp_Id and P_order.雇员id=employee.雇员id and P_order.产品id=product.产品id and P_order.客户id=customer.客户id -输出雇员王孔若完成的订单数目以及订单总金额SELECT count(*) as 订单数目,sum(P_order.数量*product.单价) as 总金额 FROM employee,product,P_order WHERE employee.雇员id= emp_Id and P_order.雇员id=employee.雇员id and P_order.产品id=product.产品id 2.执行工具栏的运行命令。四、相关理论知识（一）SQL Server 2000语法规则1Transact-SQL批处理查询是一条SQL DML语句，而批处理是一条或多条SQL语句构成的，它被应用程序同时发送给SQL Server执行。SQL Server从批中读取所有语句，并把它们编译成可执行的单元（执行计划），然后，SQL Server就一次执行计划中的所有语句。2GO命令为了在Transact-SQL脚本中结束批处理， SQL Server用到了关键字GO。GO关键字出现在一系列语句之后，使它们能作为一个单独的批处理运行。3续行Transact-SQL语句很多情况下都写的很长。如果全部放在一行写，则不利于阅读与修改。可以将一条语句在多行中进行编写，Transact-SQL会忽略空格和行尾的换行符号。4注释“-”（双减号）或“/*”和“*/”表示，在查询分析器中是以蓝色字体显示。其中“-”（双减号）用于注释单行文。5、 SELECE语句无源查询 SELECT语句无源查询就是最简单的语句。 SELECT 常量|变量|函数|表达式 as 别名,n【例1】使用SELECT语句查看常量。 select 我是中国人6、PRINT功能：将用户定义的消息返回客户端。语法： PRINT 字符串 | 变量 | 字符串的表达式【例2】使用 PRINT 语句有条件地返回消息。 IF EXISTS (SELECT zip FROM authors WHERE zip = 94705) PRINT Berkeley author （二）SQL Server 2000变量1、局部变量局部变量用DECLARE语句声明，只能用在声明该变量的过程实体中，即使用范围是定义它的批、存储过程和触发器等。由一个符号开始。定义：DECLARE 局部变量名 数据类型,n赋值：SET 局部变量名=表达式,n select 局部变量名=表达式,n【例3】本模块声明了一个不超过8个字符的字符型变量name的语句为： DECLARE name varchar(8)【例4】将王孔若赋给变量name的赋值语句。 DECLARE name VARCHAR(10) SET name=王孔若 PRINT 选民 + name【例5】将张立赋给变量name的赋值语句。 DECLARE name VARCHAR(10) SELECT name=张立 PRINT 选民 + name2、全局变量由SQL Server系统提供并赋值的变量，名字由符号开始。用户不能建立全局变量，也不可能使用SET语句去修改全局变量的值。通常应该将全局变量的值赋给在同一个批中的局部变量，以便保存和处理。（三）SQL Server 2000函数在本模块中用了一些函数，如格式转换函数convert()，日期函数year()、month()、day()等，熟练掌握SQL Server常用函数是非常必要并且是很有用的。1日期函数日期计算经常应用于很多商业应用程序中，例如到期时间、利息计算、生日以及孕妇产期计算等。SQL Server日期函数自动执行从CHAR(或VARCHAR)到DATETIME(或SMALLDATETIME)的转换。在使用日期函数之前，用户应了解以下的日期组成部分。表5-1 日期组成部分日期组成部分缩写说明取值范围yearyy, yyyy年份1753到9999quarterqq, q季度1到4Monthmm, m月份1到12dayofyeardy, y年内天数1到366Daydd, d月内天数1到31Weekwk, ww年内周数1到53Hourhh小时0到23minutemi, n分钟0到59secondss, s秒0到59millisecondms毫秒0到999SQL Server 允许用户配置以四年为界限的日期。可以使用SQL企业管理器，也可以使用sp_configure命令进行配置。（1）DATEADD函数此函数允许用户在某日期上加上一个时间间隔，从而获得一个新日期。其语法如下：DATEADD (datepart, number, date)datepart是表5-1中所列的任何值，但用户一般不会在日期上增加秒或毫秒。下面的print语句用于计算参数日期30天后的时间。PRINT DATEADD (DY, 30, 10/01/1998)输出结果如下：10 31 1998 12:00AM(2) DATEDIFF函数DATEDIFF函数返回datepart指定的日期的差。其语法如下：DATEDIFF(datepart,date1,date2)下面的print语句用于计算两个日期之间相差的天数。PRINT DATEDIFF (DY, 08/30/1998, 10/01/1998)结果如下：32(3) DATENAME函数这个函数返回日期的字符串表示。其语法如下：DATENAME (datepart, date)使用如下的代码得到指定日期是星期几：PRINT DATENAME (DW, 6/24/2006)输出结果如下：星期六即2006年6月24日是星期六(4) DATEPART函数该函数返回指定datepart的整数值(注意DATEPART函数与datepart参数不同)。例如，DATEPART函数可以返回当前月份的数值表示。其语法格式如下：DATEPART(datepart, date)下面的PRINT t语句用于从日期中得到月份的数值表示：PRINT DATEPART(MM, 6/11/2006)输出结果如下：6 (5) GETDATE函数GETDATE函数返回当前系统日期和服务器的时间。它不接受任何参数，但却是一个十分有用的函数。其语法如下：GETDATE()下面的PRINT语句用于输出当前日期：PRINT GETDATE()其输出结果如下：07 12 2006 3:06AMGETDATE的一个很好的用途是可以记录运行该任务报告的用户名以及运行报告产生的日期。因为用户不能改变服务器日期和时间，所以该信息是一个很有效的检查标记。(6) DAY函数DAY函数返回某日期的日部分所代表的整数值，该函数等价于DATEPART(dd,date)。其语法如下：DAY(date)下面的print语句用于输出当前日期：PRINT DAY(08/30/1998)其输出结果如下：30(7)MONTH函数MONTH函数返回某日期的月部分所代表的整数值。该函数等价于DATEPART(mm,date)。其语法如下：MONTH (date)下面的print语句用于输出当前日期：PRINT MONTH (08/30/1998)其输出结果如下：8(8)YEAR函数YEAR函数返回某日期的年部分所代表的整数值。该函数等价于DATEPART(yy,date)。其语法如下：YEAR (date)下面的print语句用于输出当前日期：PRINT YEAR (08/30/1998)其输出结果如下：19982.字符串函数SQL Server提供了几种功能强大的函数来操作字符串。最常用的字符串函数是LOWER、LTRIM、RTRIM、REVERSE、STR、SUBSTRING和UPPER。下面将分别给出其概念及语法，并组合使用这些函数举几个示例。（1）LOWER函数该函数将所有字母表中存在的字符转换为小写形式。所有在字母表中不存在的字符保持不变。LOWER函数的语法如下：LOWER(字符串)该函数可以和其他函数结合使用来纠正数据项的错误。有些人全部输入大写字符，有些全部输入小写，甚至还有些以适当的大小写输入。例如：PRINT LOWER (ABCDEF)其输出结果如下：abcdef（2）LTRIM函数该函数删除字符串中所有的前导空格。其语法如下：LTRIM(字符串)它经常用来纠正数据项的错误或在数据迁移、转换、EDI或其他一些数据输入的过程中纠正数据。用户可以将该函数与RTRIM函数结合起来使用，删除所有前导和后缀空格。例如：PRINT LTRIM ( smith )其输出结果如下：smith 参数中，” smith ”前后都有两个空格，用LTRIM函数后，输出结果可以看出前面的空格自动去除了，单后面的空格依然存在。（3）REVERSE函数该函数将字符串中的所有字符逆转。其语法如下：REVERSE(字符串)REVERSE函数也可以将表的一列作为输入。程序设计者可以将它嵌入到SQL查询中，用于设置默认口令。例如，可以将一个触发器设在拥有一系列用户的表上。当一个新用户插入表中时，触发器将其密码字段更新为用户名的逆转。例如：PRINT REVERSE(ABCDEF)其输出结果如下：FEDCBA（4）RTRIM函数该函数删除指定字符串末尾的所有后缀空格。其语法如下：RTRIM(字符串)它经常用于纠正数据项的错误，或者在数据迁移、数据转换、EDI以及其他一些数据输入的过程中纠正数据。另一个应用是将它作用到SELECT语句的一个CHAR类型的列上。有时CHAR类型的列上的数据不能完全填满该列，因此就要用空格填充。和LTRIM函数相似，例子看参考LTRIM函数例子。（5）STR函数该函数将数值型数据转换为字符串型数据。其语法如下：STR(数值,总长度,小数位数)“数值”是将要转换成字符串型数据的数值型数据。可选项“总长度”可以用来指定字符串的总长度，包含符号、空格、数字和十进制小数点。“小数位数”参数也是可选项，用来指定数值型数据小数部分的位数(默认值为10)。该函数的结果常用来和字符串数据结合起来作为输出结果。例如：PRINT STR(123.45,7,3)其输出结果如下：123.450 （6）SUBSTRING函数SUBSTRING函数返回字符串的一部分或全部。其语法如下：SUBSTRING(字符串,开始位置,长度)“字符串”是需要检索子串的字符串或表的列名称。“开始位置”是“字符串”中子串的起始位置。“总长度”是从“字符串”中“开始位置”位置开始的需要取出的字符数目。SUBSTRING函数的一些实际应用有：从电话号码列中分析出地区代码，从姓名列中检索出姓。例如：PRINT SUBSTRING(abcdefg,2,3)其输出结果如下：bcd3.数据类型转换函数将数据从一种数据类型转换为另一种数据类型的工作通常由SQL Server自动完成。如果要进行显示转换数据类型，需要使用两种函数：CAST()或者CONVERT()函数。（1）CAST(原数据 as 目的数据类型)：其功能是将数据从一种数据类型转换为另一种数据类型。（2）Convert(目的数据类型，要转换的数据类型，日期样式)：它可以将数据按一定的格式转换为另一种数据类型，第三个参数是可选参数，一般只有将数据转换为日期数据时，才使用，它可以取很多值，分别代表不同的日期格式，如107代表“月，日，年”格式。例如：PRINT CAST(123 as char(15) 执行结果为：123即将123转换为字符型数据。本模块中的PRINT输出语句中，“convert(char(2),month(date1)+ 月”功能即为先取出date1中的月份，将其转换为字符型数据，再连接月，整个表达式为一个字符串。模块2 编程实现订购订单一、教学目标能正确理解Transact-SQL中的流程控制语句，包括判断语句、循环语句，标签、RETURN语句以及GOTO语句。二、工作任务雇员王孔若签订了一条供应给鹿城中学50个优盘的订单，编程实现将订单涉及到的相关信息写入到数据库中。三、相关实践知识完成本项目的步骤如下：1打开查询分析器窗口，在窗口中输入以下Transact-SQL语句：USE companyInfoDECLARE userId int -保存雇员IDDECLARE max_cusId int -保存客户ID的最大值DECLARE max_ordId int -保存订单ID的最大值DECLARE storePro int -保存产品的库存量DECLARE id_product int -保存供应产品的产品编号SELECT max_cusId=max(客户ID) FROM customerSELECT max_cusId=max_cusId+1 -获取新插入客户的ID值INSERT customer VALUES(max_cusId, 鹿城中学, 李汉明, 05778235423,温州市学院路24号, 325000)SELECT storePro=库存量,id_product=产品ID FROM product WHERE 产品名=优盘SELECT max_ordId=max(订单ID) FROM P_orderSELECT max_ordId=max_ordId+1 -获取要插入的订单信息的订单IDSELECT userId=雇员ID FROM employee WHERE 姓名=王孔若 IF storePro50 PRINT 库存量不够ELSEBEGIN INSERT P_order VALUES(max_ordId,id_product, 50,userId,max_cusId,getdate()UPDATE product SET 库存量=库存量-50 WHERE 产品ID=id_productEND四、相关理论知识SQL Server提供了几种流控制关键字，可用于控制SQL Server如何去执行批处理或过程中的语句。如果不使用流控制关键词， SQL Server只能按顺序方式执行脚本中的语句。如果使用了流控制语言，就可以将语句分组并根据批处理或过程中的判断标准来控制它们的执行。（一） BEGIN.ENDBEGIN.END关键词对用来将多行SQL语句组织在一起，以达到一起执行的目的。它之所以是一对重要的关键词，是因为它可以和许多其他的流控制关键词一起使用。该语句的语法如下： BEGIN SQL语句组 END其中“SQL语句组”是任何合法的SQL单行语句或多行语句。需要用户加以注意的是它必须包含在一个单独的批处理中。（二） IF.ELSE语句1IF.ELSE语句的语法格式IF ELSE【例20】编程判断在P_order订单表中是否存在 “订货日期”在2004年5月7号以后的订单，分别打印相关信息。程序清单如下：IF (SELECT count(*) FROM P_order WHERE 订货日期2004-05-07)0 PRINT 存在“订货日期”在2004年5月7号以后的订单ELSE PRINT 不存“订货日期”的在2004年5月7号以后的订单 （三）WHILE循环WHILE语句用于创建一个循环，SQL Server将在该循环中连续执行某些语句，直到循环条件为假。还可以通过设置一个永远为真的条件，如1=1，使循环一直进行下去。1WHILE循环的语法格式WHILE语句用于创建一个循环。语法：1）WHILE 2）WHILE BEGIN END 【例21】计算并输出1+2+3+100表达式的和。程序清单如下：DECLARE sum intDECLARE i intSELECT sum=0SELECT i=1;WHILE i=10 and i=30)BEGIN IF i%3=0 BEGIN PRINT i SET i=i+1 SET n=n+1 CONTINUE END IF i=22 BREAK SET i=i+1ENDPRINT 有+convert(char(2),n)+ 个数能被3整除程序功能：该程序用于输出能被3整除的整数。其中用了CONTINUE语句和BREAK语句。当程序执行到i为22时，直接跳出循环。故其执行结果如下：12151821有4个数能被3整除（四） GOTO语句和RETURN语句用户在使用GOTO语句时必须十分谨慎，因为过多地使用GOTO语句会使代码难读，执行速度降低。该语句通常和RETURN语句一起使用。GOTO语句用于让SQL Server跳转到SQL代码中的指定标签处，标签设计成如下形式：GOTO语法如下：GOTO 标签名 标签名: 【例24】分析以下语句的执行结果。DECLARE sum intDECLARE x intDECLARE y intSELECT x=9SELECT y=10SELECT sum=x+yGOTO label1SET sum=sum+100label1:PRINT sum Use 信息管理 WAITFOR delay 01:10:12 Select * from 学生【例26】等到11点12分后才执行select 语句Use companyinfo WAITFOR time 11:12:00 Select * from employee（六） RETURN语句 用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。 语法： RETURN integer_expression 参数：integer_expression为返回的整型值。