数据库第5章.

DLPU第5章T-SQL程序设计基础1.数据类型2.常量、变量和运算符3.函数4.批处理和流程控制数据类型数据类型对应着SQLServer2008系统在内存或磁盘上开辟存储空间的大小，也决定了访问、显示、更新数据的方式。除了支持数值型、字符型、日期型、货币型等系统提供的数据类型外，T-SQL还支持用户自定义数据类型。系统提供的数据类型SQLServer2008中提供了丰富的数据类型，主要包括二进制、精确数字、近似数字、字符串、日期时间等几大类型。类别数据类型类别数据类型类别数据类型二进制字符串BINARYVARBINARYIMAGE近似数字FLOATREAL日期时间DATETIMEDATESMALLDATETIME精确数字BITINTBIGINTSMALLINTTINYINTDECIMALNUMERICMONEYSMALLMONEY字符串CHARVARCHARTEXTNCHARNVARCHARNTEXT其它类型TIMESTAMPSQL_VARIANTTABLECURSORUNIQUEIDENTIFIERXML表5-1SQLServer2008系统提供的数据类型用户自定义数据类型1．创建用户自定义数据类型系统存储过程sp_addtype可以创建用户自定义数据类型。【例5-1】在数据库TSG中创建自定义数据类型Number，SMALLINT类型，允许空。USETSGGOEXECsp_addtypeNumber,'SMALLINT','NULL'用户自定义数据类型2．查看用户自定义数据类型存储过程sp_help可以查看用户自定义数据类型。。【例5-2】查看Number的特征。EXECsp_helpNumber图5-1例5-2的运行结果用户自定义数据类型3．删除用户自定义数据类型存储过程sp_droptype可以删除用户自定义数据类型。【例5-3】删除自定义的数据类型Number。EXECsp_droptypeNumber常量、变量和运算符常量是一个常值，在程序运行中保持不变，一经定义程序本身不能改变其值。变量是在程序运行过程中其值可以改变的量。常量1．字符串常量字符串常量括在单引号内，并包含字母数字字符（a-z、A-Z和0-9）以及特殊字符如感叹号(!)、at符(@)和数字号(#)。例如：‘Cincinnati’、‘O‘、'Brien'、'ProcessXis50%complete.'字符串常量还可以采用Unicode字符编码格式。例如：N'ASQLString'表示字符串'ASQLString'是一个Unicode字符串。常量2．二进制常量二进制常量具有前辍0x并且是十六进制数字字符串。这些常量不使用引号括起。例如，0xAE、0x12Ef、0x69048AEFDD010E、0x（空二进制串）常量3．日期/时间常量日期/时间常量使用特定格式的字符日期值来表示，并被单引号括起来。例如，'2010-12-05、'12/05/2010'、''20101205'第二种依赖于DATEFORMAT，是SQLServer默认的处理方式。常量4．数值常量1）整型常量整型常量用不包含小数点的数字字符串来表示。必须全部为数字，它们不能包含小数。例如：2356、8等都是整型常量。常量4．数值常量2）数值常量数字常量分为精确数值常量和浮点常量。精确数值常量由没有用引号括起来并且包含小数点的数字字符串来表示。例如：1894.1204和2.0等。浮点常量使用科学记数法来表示。例如101.5E5和0.5E-2等常量4．数值常量3）货币常量货币常量以前缀为可选的小数点和可选的货币符号不使用引号括起的数字字符串来表示。例如：$12和$542023.14都为货币常量，前面的货币符号“$”是可选的。常量4．数值常量4）uniqueidentifier常量UNIQUEIDENTIFIE常量是表示GUID的字符串。可以使用字符或二进制字符串格式指定。'6F9619FF-8B86-D011-B42D-00C04FC964FF'0xff19966f868b11d0b42d00c04fc964ff变量1．局部变量（LocalVariable）局部变量是用户自定义的变量，它的作用范围仅在定义它的程序内部。局部变量名以“@”开头，用DECLARE命令进行定义，语法格式如下：DECLARE@变量名变量类型[,@变量名变量类型]使用SELECT或SET命令来设置变量的值，语法格式如下：SELECT@变量名=变量值或者SET@变量名=变量值变量1．局部变量（LocalVariable）【例5-4】声明一个存放名称的变量Name，类型为char，长度为10，赋值“图书馆”并输出变量的值。DECLARE@Namechar(10)SELECT@Name='图书馆'PRINT@Name图5-2例5-4的运行结果变量2．全局变量（GlobalVariable）不是由用户的程序定义的，是在服务器级定义的系统内部使用的变量，任何程序均可以随时使用。引用全局变量必须以标记符“@@”开头。全局变量含义@@CONNECTIONS返回自上次启动以来连接或试图连接的次数@@CURSOR_ROWS返回连接上最后打开的由表中当前存在的@@ERROR返回最后执行的T-SQL语句的错误代码@@FETCH_STATUS返回上一次FETCH语句的状态值@@IDENTITY返回最后插入的标识值@@MAX_CONNECTIONS返回SQL上允许的同时用户连接的最大数。@@PROCID返回当前存储过程的ID值@@OPTIONS返回当前SET选项的信息@@ROWCOUNT返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为0@@SERVERNAME返回运行SQL服务器名称@@SERVICENAME返回SQLServer正运行于哪种服务状态之下，如MSSQLServer，MSDTC，SQLServerAgent@@SPID返回当前用户进程的服务器进程标识符@@TRANCOUNT返回当前连接的活动事务数@@VERSION返回SQLServer的版本信息表5-2常用的全局变量变量2．全局变量（GlobalVariable）【例5-5】修改TSG数据库的Book表中某条记录的书号“CallNo”，用@@ERROR检测主键冲突。USETSGGOUPDATEBookSETCallNo='F121/L612'WHERECallNo='G0/Z810'IF@@ERROR=2627PRINT'Aconstraintviolationoccurred'图5-3例5-5的运行结果变量2．全局变量（GlobalVariable）【例5-6】查看SQLServer的版本号。SELECT@@VERSION图5-4例5-6的运行结果【例5-7】@@ROWCOUNT的使用。USETSGGOUPDATEBOOKSETTitle='ZDMA'WHERECallNo='999-888-7777'IF@@ROWCOUNT=0print'Warning:Norowswereupdated'图5-5例5-7的运行结果注释与输出1．注释注释语句不是可执行的语句，不参与程序的编译。作用：说明代码的功能或对代码的实现方式给出简要的解释或提示。对T-SQL语句进行注释有两种方法：使用“--”进行单行注释。利用“/*…*/”进行块注释。注释与输出2．输出PRINT语句可直接显示表达式结果RAISERROR({msg_id|msg_str},SEVERITY,STATE[,argumentl[,…n]])RASERROR函数将错误信息显示在屏幕上，同时也可以记录在日志中。RASEERROR函数的基本语法格式如下：msg_id：错误号，省略时系统将产生一个错误号为50000的错误消息；msg_str：错误信息；SEVERITY：错误的严重级别；STATE：说明发生错误时的状态信息。注释与输出2．输出【例5-8】查询索书号为F121/L612的书籍数量，如果1本以上，则显示数量1本以上，否则输出库存不足信息。USETSGGODECLARE@CallNovarchar(9),@Titlevarchar(40),@NumbersmallintSET@CallNo='qq'SELECT@Title=Title,@Number=NumberFromBookWhereCallNo=@CallNoIF(@Number1)PRINT@Title+'1本以上。'ELSERAISERROR('库存不足,报警！',10,1)运算符运算符用来执行数据之间的数学或比较运算，是指定要在一个或者多个表达式中执行操作的一种符号。T-SQL中运算符主要有6大类：算术运算符赋值运算符位运算符比较运算符逻辑运算符字符串串联运算符运算符1．算术运算符包括：加（+）、减（-）、乘（*）、除（/）和取模（%）等。【例5-9】SELECT+3+2加法SELECT-4-1减法SELECT-5*2乘法SELECT10/2整数相除,10.0/2小数除法SELECT10%2取余图5-7例5-9的运行结果运算符2．赋值运算符“=”：将数据值指派给特定的对象。3．位运算符操作数：整型或者二进制数据(IMAGE类型除外)包括：位与（&）、位或（|）、位异或（^）和位非（～）等。运算符4．比较运算符比较两个表达式的大小或是否相同，其比较的结果：TRUE（表示表达式的结果为真）FALSE（表示表达式的结果为假）UNKNOWN除了TEXT、NTEXT或IMAGE数据类型的表达式外，比较运算符可以用于所有其它表达式。运算符5．逻辑运算符把多个逻辑表达式连接起来。包括AND、OR和NOT。运算结果：TRUE或FALSE值的布尔数据类型。6．字符串串联运算符加号(+)，可以进行字符串串联。例如，表达式'ab'+'cd'的结果为'abcd'。函数函数是一组编译好的T-SQL语句，SQLServer支持两种函数类型：内置函数：是一组预定义函数，是T-SQL语言的一部分，用户可以直接使用它们实现希望的功能。用户定义函数：是由用户自行定义并编写的函数，用户可以根据需要编写和修改自定义函数，然后进行调用。常用内置函数SQLServr2008中的函数大概分为7类：数学函数字符串函数日期时间函数聚集函数转换函数系统函数用户自定义函数常用内置函数1．字符串函数函数UPPER(str)ASCII(str)REPLICATE(str,n)CHAR(n)PATINDEX('%pattern%',str)CHARINDEX(str1,str2[,start])LEN(str)STUFF(str1,start,length,str2)REVERSE(str)SUBSTRING(str,start,length)SPACE(n)LTRIM(str)LEFT(str,n)RTRIM(str)RIGHT(str,n)LOWER(str)STR(f[,p[,s]])表5-3常用字符串函数常用内置函数1．字符串函数【例5-11】删除字符串变量中起始处的空格。DECLARE@Str1VARCHAR(40)SET@Str1='TWOSPACESBEFORETHISSTRING'SELECT@Str1SELECT'THERESULTIS:'+LTRIM(@Str1)图5-9例5-11的运行结果常用内置函数1．字符串函数【例5-12】编写一段程序输出数值147.58的整数和小数部分。DECLARE@StrCHAR(18),@PosSMALLINTSET@Str=STR('147.58',6,2)SET@Pos=CHARINDEX('.',@STR)PRINTRTRIM(@Str)+'的整数部分是：'+LEFT(@Str,@Pos-1)PRINTRTRIM(@Str)+'的小数部分是：'+RIGHT(RTRIM(@Str),LEN(RTRIM(@Str))-@pos+1)图5-10例5-12的运行结果常用内置函数2．数学函数函数函数A