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

清远职业技术学院教案第_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, '鹿城中学', '李汉明', '','温州市学院路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 storePro<50 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 <条件表达式><SQL语句组1>ELSE<SQL语句组2>【例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 <条件表达式> <单行SQL语句>2）WHILE <条件表达式> BEGIN <SQL语句组> END 【例21】计算并输出1+2+3+100表达式的和。程序清单如下：DECLARE sum intDECLARE i intSELECT sum=0SELECT i=1;WHILE i<=100BEGIN SET sum=sum+i SET i=i+1ENDPRINT sum【例23】分析下列程序的功能。DECLARE sum intDECLARE n intDECLARE i intSELECT n=0;SELECT i=10;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 标签名 <SQL语句组1>标签名: <SQL语句组2>【例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为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。拓展学问 在Transact-SQL语言中，函数被用来执行一些特别的运算以支持SQL Server的标准吩咐。Transact-SQL 编程语言供给了三种函数： (一)系统函数 系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，运用条件语句，依据返回的信息进展不同的操作。与其它函数一样，可以在SELECT语句的SELECT和 WHERE 子句以及表达式中运用系统函数。（二）聚合函数 聚合函数可以返回整个或者几个列或者一个列的汇总数据，它常用来计算SELECT语句查询的统计值。聚合函数常常与 SELECT 语句的 GROUP BY 子句一同运用。（三）用户定义函数 创立用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。 1、用户指定义函数分类返回单值的标量函数类似于视图的可更新内嵌表函数运用代码创立结果集的多语句表函数2、创立标量函数语法：CREATE FUNCTION function_name（ inputparameters）    RETURNS data_type AS BEGIN    function_body    RETURN expressionEND【例27】 阅读分析以下程序：CREATE FUNCTION dbo.multiply (a int ,b int) RETURNS int asBEGIN return a*bENDGoSelect dbo.multiply(3,4)Select dbo.multiply(7,3)3、调用用户定义函数 当调用标量用户定义函数时，必需供给至少由两部分组成的名称：SELECT *, MyUser.MyScalarFunction() FROM MyTable 可以运用一个部分构成的名称调用表值函数：SELECT * FROM MyTableFunction()4、获得有关函数的信息 对象报告有关用户定义函数的信息： sp_help 报告有关用户定义函数的信息。sp_helptext 报告用户定义函数的来源。