第九章_T-SQL编程与应用.ppt

数据库实用技术SQLServer2008,第九章T-SQL编程与应用,SQLServer2008,第九章T-SQL编程与应用,SQLServer2008,T-SQL概述,T-SQL语言：SQL是结构化查询语言，大多数数据库供应商都支持SQL语言。Microsoft公司采用Transact-SQL作为SQLServer的核心组件，简称T-SQL。注意：在T-SQL中，命令和语句的写书不区分大小写的。,第九章T-SQL编程与应用,SQLServer2008,T-SQL概述,T-SQL语言包括：数据定义语言（DDL）：定义和管理数据库及其对象，例如Create、Alter和DroP等语句。数据操作语言（DML）：实现对数据库表各对象的操作，例如Insert、Update、Delete和Select语句。数据控制语言（DCL）：实现对数据库进行安全管理和权限管理等控制，例如Grant、Revoke、Deny等语句。附加的语言元素：包括变量、运算符、函数、注释和流程控制语句等。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,T-SQL语言标识符规则标识符：由字母、数字、下划线、#和$符号组成，其中字母可以是英文字母az或AZ，也可以是来自其他语言的字母字符。首字符不能为数字和$符号。标识符不允许是T-SQL的保留字。标识符内不允许有空格和特殊字符。长度小于128。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,T-SQL语言标识符界定标识符：对于不符合标识符规则的标识符，例如标识符中包含了SQLServer关键字或者包含了内嵌的空格和其他不是规则规定的字符，则要使用界定符方括号（）或双引号（“”）将标识符括起来。如标识符MyTable、“select”内分别使用了空格和保留字select。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,数据类型具有数据类型的对象有：表和视图中的列。存储过程中的参数。变量。函数返回值。具有返回代码的存储过程。数据类型包括：系统数据类型：第5.4.2节中所述的数据类型。自定义数据类型：是在系统数据类型基础上建立的数据类型。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,数据类型例如：为数据库BillingSys定义一个基于char数据类型18位长的身份证号数据类型“IdentifyCard”。操作步骤：在SSMS中展开SQLServer实例，依次选择“数据库”“BillingSys”“可编程性”“类型”，右击选择“新建”“用户定义数据类型”命令，打开“新建用户定义数据类型”窗口。在“新建用户定义数据类型”窗口中，分别向“名称”、“数据类型”、“长度”的文本框输入“IdentifyCard”、“char”、“18”，勾选“允许空值”复选框，然后单击“确定”按钮即可。如下页图所示。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,数据类型定义一个基于char数据类型18位长的身份证号数据类型“IdentifyCard”的自定义数据类型窗口：,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式表达式是指由常量、变量、函数等通过运算符按一定的规则连接起来的有意义的式子。常量：常量，是表示一个特定数据值的符号，在程序运行过程中其值保持不变，也称为文字值或标量值。变量：变量是指在程序运行过程中其值可以发生改变的量。函数：函数是用来完成某种特定功能，并返回处理结果的一组T-SQL语句；处理结果称为“返回值”，处理过程称为“函数体”。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式常量类型：常用类型常量表一：,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式常量类型：常用类型常量表二：,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式变量分类：局部变量局部变量由用户定义，一般出现在批处理、存储过程和触发器中，其作用范围仅在程序内部。局部变量必须先声明，后使用。DECLARE变量声明语句，其语法格式为：DECLARE变量1AS数据类型,变量nAS数据类型说明：局部变量名称必须以开头。指定变量的数据类型，可以是系统提供的数据类型或用户自定义数据类型；对于字符型变量，还可以指定长度；数值型变量，指定精度和小数位数。赋初值NULL。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式变量分类：局部变量【例9-1】声明两个变量，checkid为int类型的变量，name为长度为20的varchar类型变量。DECLAREcheckidint，namevarchar（20）,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式变量分类：局部变量T-SQL为局部变量提供了赋值语句。SELECT赋值语句，其语法格式为：SELECT变量1=表达式1,变量2=表达式2,变量n=表达式n说明：用SELECT命令可以一次给多个变量赋值。当表达式为表的列名时，可使用子查询从表中一次返回多个值，变量只保存最后一个返回值；如果子查询没有返回值，则变量被赋空值NULL。SELECT变量1,变量2,变量n语句可以显示变量的值。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式变量分类：局部变量【例9-2】从Relationer表中，查询RID为“20103541”的记录，并为例9-1中声明两个变量checkid，name赋值。DECLAREnamevarchar(20),checkidintSELECTcheckid=RID,nane=RNameFROMRelationerWHERERID=20103541SELECTcheckid,nameGO,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式变量分类：局部变量T-SQL为局部变量提供了赋值语句。SET赋值语句，其语法格式为：SET变量=表达式SET语句与SELECT语句在为变量赋值的区别在于：一条SET赋值语句只能给一个变量赋值。一条SELECT语句可以给多个变量赋值。约定：当只给一个简单变量赋值时，使用SET赋值语句。当通过查询返回的结果给变量赋值时，使用SELECT赋值语句。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式变量分类：全局变量全局变量由SQLServer系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值。全局变量名以开头。常用的全局变量,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式函数分类：系统内置函数SQLServer提供了大量系统内置函数；主要可分为以下几类：数学函数字符串函数日期函数convert函数聚合函数用户自定义函数,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：数学函数：数学函数用来实现各种数学运算，要求操作数为数值型数据。常用的数学函数一：ABS(数值表达式)：返回指定数值表达式的绝对值。ROUND(数值表达式,长度,类型)：返回一个舍入到指定长度或精度的数值。类型为0，表示舍入，类型为非0，表示截断。POWER(数值表达式,指数)：返回指定表达式的指定幂的值。例如求4的3次幂，使用POWER(4,3)，返回64。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：常用的数学函数二：SQRT(数值表达式)：返回指定表达式的平方根。SQUARE(数值表达式)：返回指定表达式的平方。EXP(数值表达式)：返回以e为底的指定表达式的指数值。例如求，使用EXP(2),返回7.389056。LOG(数值表达式)：返回指定表达式的自然对数。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：常用的数学函数三：LOG10(数值表达式)：返回指定表达式的以10为底的对数。SIN(数值表达式)：返回指定角度（以弧度为单位）的三角正弦值。COS(数值表达式)：返回指定角度（以弧度为单位）的三角余弦值。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：字符串函数：字符串函数对字符串进行处理，返回字符串或数值。常用的字符串函数一：ASCII(字符表达式)：返回字符表达式中最左侧的字符的ASCII代码值。CHAR(整型表达式)：将整数按ASCII代码转换为字符。LEFT（字符表达式,整型表达式)：函数返回字符串中从左边开始的指定个数的字符。RIGHT（字符表达式，整型表达式）：函数返回字符串从右边开始的指定个数的字符。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：常用的字符串函数二：LTRIM(字符表达式)：返回删除了前导空格字符后的字符串。RTRIM（字符表达式）：截断所有尾部空格后返回一个字符串。LOWER(字符表达式）：将字符表达式中的大写字符转换为小写字符。UPPER（字符表达式)：将字符表达式中的小写字符转换为大写字符。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：常用的字符串函数三：SUBSTRING（字符表达式，起始点，n）：返回字符表达式中从“起始点”开始的n个字符的子串。LEN（字符表达式)：返回指定字符串的长度，不计字符串后的空格。STR(浮点表达式，长度，小数)：返回由数字数据转换来的字符数据。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：日期和时间函数：日期和时间函数处理日期和时间，返回日期或时间值、数字或字符串。常用的日期时间参数：,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：常用的日期和时间函数一：GETDATE（）：返回当前的日期和时间。YEAR(日期）：返回指定日期的“年”部分的整数。MONTH（日期）：返回指定日期的“月”部分的整数。DAY(日期)：返回指定日期的“日”部分的整数。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：常用的日期和时间函数二：DATEPART(日期元素，日期）：返回日期元素指定的日期部分的整数。DATENAME（日期元素，日期)：以字符串的形式返回日期元素指定时间的日期名称。DATEDIFF（日期元素，日期1，日期2）：返回两个日期间的差值并将其转换为指定日期元素的形式。DATEADD（日期元素，数值，日期)：按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：日期和时间函数：【例9-3】查询注册超过10年的有效客户信息。SELECTCID,CName,CRegistrationDate,CTypeFROMCustomerWHEREDATEDIFF(yy,CRegistrationDate,GETDATE()=10ANDCStatus=1GO,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：数据类型转换方式隐式转换：例如执行123456时，系统会自动地将456转换为Integer数据类型。显式转换：使用CONVERT函数。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：数据类型转换函数：CONVERT函数格式：CONVERT（数据类型(长度)，表达式,n）说明：函数中最后的参数n是可选的，用于日期时间型数据类型和字符数据类型的转换，取值见下表：,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：数据类型转换函数：【例9-4】利用Convert函数将Customer表中的注册日期值转换为字符型，同时设置显示格式为“yyyy.mm.dd”。SELECTCIDAS客户ID,CNameAS客户名称,CONVERT(char(10),CRegistrationDate,102)AS注册日期,CTypeAS客户类型FROMCustomerGO,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,表达式系统内置函数：聚集函数在6.2.1节中已介绍聚合函数。常用的聚集函数：COUNT()SUM()AVG()MAX()MIN(),第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,运算符运算符是将变量、常量和函数连接起来并指定在一个或多个表达式中执行的操作。SQLServer2008提供七种类型的运算符：,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,运算符运算符的优先级：优先级高的（即数字小的）先运算，相同优先级的运算符按照自左向右的顺序依次进行运算，见下表：,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,流程控制语句控制程序执行顺序的语句。程序执行顺序为：顺序执行条件分支循环和跳转,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,顺序语句：SET语句SET语句有两种用法：(1)给局部变量赋值(2)设定用户执行T-SQL命令时SQLServer的处理选项，一般有以下几种设定方式：SET选项ON：选项开关打开。SET选项OFF：选项开关关闭。SET选项值：设定选项的具体值。例如：设置显示/隐藏受T-SQL语句影响的行数消息语句，其语法如下：SETNOCOUNT(ON|OFF),第九章T-SQL编程与应用,SQLServer2008,流程控制语句,顺序语句：SELECT输出语句SELECT作为输出使用时的语法如下：SELECT表达式1,表达式2,表达式nPRINT输出语句PRINT用于向屏幕输出信息，其语法如下：PRINT表达式,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,顺序语句：注释语句注释语句是对程序的说明解释，增加程序的可读性。单行注释格式：-注释信息单行注释常跟在一条语句后。多行注释格式：/*注释信息*/包含在“/*”、“*/”的注释信息可以是多行的,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,分支语句：IF.ELSE语句具体的语法格式如下：IF逻辑表达式ELSE逻辑表达式功能：当逻辑表达式成立时，顺序执行语句块1中的各语句，否则顺序执行语句块2中的各语句。其中ELSE子句是可选的。,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,分支语句：IF.ELSE语句说明：语句块由BEGINEND包含的多条T-SQL语句组成。关键字BEGIN、END必须成对出现。BEGINEND允许嵌套。IFELSE语句中不止包含一条语句时，必须使用BEGINEND语句块。可以在IF之后或ELSE之后，嵌套另一个IF语句。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,分支语句：IF.ELSE语句【例9-5】Customer表中有两大客户：单位客户和个人客户。查询客户20103530是哪一类客户，如果是单位客户，显示其客户名称。DECLARECustTypevarchar(6),CustNamevarchar(60)SELECTCustName=CName,CustType=CTypeFROMCustomerWHERECID=20103530IFCustType=公众ORCustType=商业ORCustType=大户PRINTCustName,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,分支语句：IF.ELSE语句【例9-6】查询Customer表中有无“商业”类客户，如果有则统计这类客户个数，否则输出“无商业客户”信息。DECLAREbusinessCountintIFEXISTS(SELECT*FROMCustomerWHERECType=商业)BEGINSELECTbusinessCount=COUNT(CID)FROMCustomerWHERECType=商业PRINT商业客户共有：+STR(CustName)+个。ENDELSEPRINT无商业客户。,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,分支语句：CASE函数CASE函数提供了比IF.ELSE结构更多选择和判断的机会。使用CASE函数可以很方便地实现多种选择情况，从而避免编写多重IF.ELSE嵌套。简单CASE函数：语法如下：CASE输入表达式WHEN表达式值1THEN返回表达式1WHEN表达式值2THEN返回表达式2ELSE返回表达式nEND功能：计算CASE输入表达式，将其值按指定顺序与WHEN表达式的值进行比较运算。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,分支语句：CASE函数简单CASE函数：【例9-7】查询客户20103122是哪一类型的客户，如果是单位客户，则输出客户名称+类型信息，如果是个人客户则输出“个人客户”。DECLARECustMsgvarchar(60)SELECTCustMsg=CName+CASECTypeWHEN公众THEN是公众服务类单位。WHEN商业THEN是商业贸易类单位。WHEN大户THEN是大型上市公司。ELSE是个人客户。ENDFROMCustomerWHERECID=20103122PRINTCustMsg,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,分支语句：CASE函数CASE搜索函数：语法如下：CASEWHEN逻辑表达式值1THEN返回表达式1WHEN逻辑表达式值2THEN返回表达式2ELSE返回表达式nEND功能：按指定顺序对每个WHEN子句求逻辑表达式的值。,第九章T-SQL编程与应用,SQLServer2008,T-SQL编程基础,分支语句：CASE函数CASE搜索函数：【例9-8】使用CASE搜索函数完成例9-7的操作功能。DECLARECustMsgvarchar(60)SELECTCustMsg=CName+CASEWHENCType=公众THEN是公众服务类单位。WHENCType=商业THEN是商业贸易类单位。WHENCType=大户THEN是大型上市公司。ELSE是个人客户。ENDFROMCustomerWHERECID=20103122PRINTCustMsg,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,循环语句：WHILE语句语法如下：WHILE逻辑表达式BEGINBREAKCONTINUEEND功能：WHILE命令在设定的条件为真时会重复执行命令行或程序块。CONTINUE命令使程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行命令。BREAK命令则让程序无条件跳出循环，结束WHILE命令的执行。,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,循环语句：WHILE语句【例9-9】一个简单的循环程序。DECLARExint-声明x为一整型变量SETx=0-给变量x赋初值WHILEx=3时终止循环BEGINSETx=x+1PRINTx=+CONVERT(char(1),x)-CONVERT函数将变量x的-值转换为字符ENDGO,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,循环语句：WHILE语句WHILE语句的嵌套【例9-10】利用两重循环实现计算1到5的数两两相乘，输出积在10以内的乘法。,第九章T-SQL编程与应用,SQLServer2008,流程控制语句,循环语句：WHILE语句【例9-10】代码如下：DECLARExint,yint-声明x，y为整型变量SETx=0-给变量x赋初值WHILEx5BEGINSETx=x+1SETy=0-给变量y赋初值WHILEy0COMMITTRANSACTION-提交事务SELECT*FROMCustomerWHERECName=新大白宾馆GO,第九章T-SQL编程与应用,SQLServer2008,游标,游标概念游标：游标使得SQLServer语言可以逐行处理结果集中的数据；主要用在存储过程、触发器和T-SQL脚本中。游标包括以下两个部分游标结果集（CursorResultSet）由定义该游标的SELECT语句返回的行的集合。游标位置(CursorPosition）指向这个集合中某一行的指针。,第九章T-SQL编程与应用,SQLServer2008,游标,游标概念具有以下优点允许定位在结果集的特定行。从结果集的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中使用的访问结果集中的数据的T-SQL语句。SQLServer2008支持以下两种请求游标的方法T-SQL：使用T-SQL语句定义的游标。数据库应用程序编程接口（ApplicationProgrammingInterface，API）游标函数。,第九章T-SQL编程与应用,SQLServer2008,游标,使用游标声明游标游标在使用之前，必须先声明。声明游标使用DECLARECURSOR语句，其语法格式如下：DECLARECURSORINSENSITIVESCROLLCURSORFORSELECT语句FORREADONLY|UPDATEOF列名1,n参数说明：INSENSITIVE：在TempDB中建立创建由该游标使用数据的临时表，并对其进行任何操作。SCROLL：FIRST、LAST、PRIOR、NEXT（默认）、RELATIVE、ABSOLUTE。SELECT语句：定义游标结果集的标准SELECT语句。READONLY：定义只读游标，不能修改数据。UPDATEOF列名1,n：定义游标中可更新的列。,第九章T-SQL编程与应用,SQLServer2008,游标,使用游标打开游标打开游标使用OPEN语句，其语法格式如下：OPENGLOBAL|参数说明：GLOBAL选项是指定其后的游标为全局游标。使用OPEN语句打开游标后，游标位置位于查询结果集的第一行。,第九章T-SQL编程与应用,SQLServer2008,游标,使用游标提取数据在打开游标后，利用FETCH语句从查询结果集中提取数据。使用FETCH语句一次可以提取一条记录。FETCH语句的语法格式如下：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBAL|INTO变量名,n,第九章T-SQL编程与应用,SQLServer2008,游标,使用游标提取数据FETCH语句参数说明：NEXT：提取紧跟当前行之后的结果行，并且将当前行向后移一行。PRIOR：提取紧临当前行前面的一行，并且将当前行向前移一行。FIRST：提取游标中的第一行并将其设置为当前行。LAST：提取游标中的最后一行并将其设置为当前行。ABSOLUTEn|nvar：如果n或nvar为正（负）数，提取从游标头开始（尾之前）的第n行，并将返回的行变成新的当前行；如果n或nvar为0，则没有行返回。n或nvar必须为整型常量。RELATIVEn|nvar：如果n或nvar为正（负）数，提取当前行之后（前）的第n行，并将返回的行变成新的当前行；如果n或nvar为0，提取当前行。GLOBAL：指定其后的游标是全局游标。INTO变量名列表：允许将提取的列数据保存到局部变量中。,第九章T-SQL编程与应用,SQLServer2008,游标,使用游标关闭游标关闭游标使用CLOSE语句，其语法格式如下：CLOSEGLOBAL|释放游标释放游标使用DEALLOCATE语句其语法格式如下：DEALLOCATEGLOBAL|注意：关闭游标并不改变游标的定义，可以再次打开该游标。释放游标就释放了与指定游标有关的一切资源，再次使用该游标，必须重新声明。,第九章T-SQL编程与应用,SQLServer2008,游标,使用游标【例9-12】利用游标生成商业客户的联系人及联系电话信息表。【分析】由于FETCH语句一次可以从结果集中取出一条记录，但是商业客户可能不止一个。所以，要设计一个循环，将FETCH语句放在循环体内，直到将结果集中的商业客户的联系人信息全部提取完，才跳出循环。,第九章T-SQL编程与应用,SQLServer2008,游标,使用游标【例9-12】代码如下：DECLARECust_idint,Cust_RIDint,Cust_namevarchar(60),relationer_namevarchar(20),relationer_telchar(11)PRINT-商业客户联系人信息-DECLAREcustomer_cursorCursor-声明游标