第12章 Transact-SQL介绍

第12章Transact-SQL介绍本章学习目标l了解Transact-SQL语言起源、组成l掌握Transact-SQL编程基础知识l综合运用变量、表达式、函数及流控语句等编写应用程序代码12.1Transact-SQL语言概论12.1.1Transact-SQL语言简介SQL语言能够针对数据库完成定义、查询、操纵和控制功能，是关系型数据库领域中的标准化查询语言。微软公司在SQL语言的基础上对其进行了大幅度的扩充，并将其应用于SQLServer服务器技术中，从而将SQLServer所采用的SQL语言称为Transact-SQL语言。目前SQL语言的最新标准为SQL-92，由美国国家标准局制定，包含了语法标准以及对SQL关键字的定义。SQL语言由三部分组成：•数据定义语言（DataDefinitionLanguage，简称DDL）、•数据操纵语言（DataManipularionLanguage，简称DML）、•数据控制语言（DataControlLanguage，简称DCL）。对上述三种语言的描述如表12-1所示。表12-1SQL语言的组成SQL语言的组成描述数据定义语言DDL主要作用为在数据库中创建并且管理各种数据库对象，如数据库、表、视图、索引、触发器等，主要通过对每个对象的CREATE、ALTER、DROP语句来实现数据操纵语言DML用于对数据的查询、添加、修改和删除等操作，使用SELECT、INSERT、UPDATE、DELETE等语句数据控制语言DCL用于对用户的权限进行设控制，主要使用GRANT、GRANT、DENY、REVOKE语句2批处理批处理是多条Transact-SQL语句构成的集合。SQLServer将批处理语句进行编译形成一个可执行单元，称为执行计划。执行计划中的语句一次执行每一条语句，如果在编译过程中出现语法错误，那么批处理中所有的语句均无法正常执行。如果在运行阶段出现错误时，一般都会中断当前以及其后语句的执行，只有在少数情况下，如违反约束时，仅中断当前出错的语句而继续执行其他语句。如果在事务中含有批处理语句，那么在运行阶段一旦出现错误时，都将会对已经执行的操作结果进行回滚。假定在事务中包含10条语句，那么如果第五条语句出现错误，则不会执行后续的语句，并且取消之前完成的操作。在建立批处理时，应该遵循以下规则：l不能在批处理中引用其他批处理中所定义的变量。lCREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW语句不能在批处理中与其它语句组合使用。l不允许在一个批处理中更改表结构、并引用新的字段。l如果EXECUTE语句是批处理中的第一句，则不需要EXECUTE关键字。如果EXECUTE语句不是批处理中的第一条语句，则需要EXECUTE关键字。l一个完整的批处理需要使用GO语句作为结束标记。1.批处理命令以批处理的方式处理单个或多个Transact-SQL语句。有两种基本方法将批处理传送给SQLServer。（1）go向SQLServer发送当前Transact-SQL语句的批处理的一种信号。（2）EXEC用于执行用户定义函数、系统过程、用户定义存储过程或扩展的存储过程。EXEC可以传递参数，并且可以赋值给返回状态变量【例1】执行批处理程序，依次查询系部表、系部总数、班级表、班级总数。USEXSCJGOSELECT*FROM系部表SELECTCOUNT(*)FROM系部表SELECT*FROM班级表SELECTCOUNT(*)FROM班级表GO运行结果如图12-18所示。图12-18执行批处理程序结果Transact-SQL语法要素l注释语句（1）单行注释--（2）多行注释/*…*/l变量@局部变量@@全局变量，只读l变量的声明DECLARE{@local_variabledata_type}[…n]例如，定义一个整数类型的变量@score以及字符串类型的变量@name，可以表示为：DECLARE@scoreINTDECLARE@nameCHAR(12)SET语句一次只能给一个局部变量赋值，SELECT语句则可以同时给一个或多个变量赋值。局部变量通常用于以下几种情况：①作为计数器计算循环执行的次数或控制循环执行的次数。②保存数据值以供流控语句测试。③保存由存储过程返回代码的数据值。变量的作用域指从申明变量的开始位置到含有该变量的批处理或存储过程的结束位置。表12-2Transact-SQL的语法规则语法规则功能描述大写Transact-SQL关键字斜体或小写字母Transact-SQL语法中用户提供的参数|（竖线）分隔括号或大括号内的语法项目。只能选择一个项目[]（方括号）可选语法项目。不必键入方括号{}（大括号）必选语法项目。不要键入大括号[...n]表示前面的项可重复n次。每一项由空格分隔加粗数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及必须按所显示的原样键入的文本标签::=语法块的名称。此规则用于对可在语句中的多个位置使用的过长语法或语法单元部分进行分组和标记。适合使用语法块的每个位置由括在尖括号内的标签表示：标签l变量的赋值方法：SET{@localvariable=expression}SELECT{@localvariable=expression}[,…n]打印输出变量lPRINT‘anyASCIItext’|@local_variable|@@FUNCTION|string_expr例：Ifexists(select*fromswheresno=‘s1’)print‘学生表S中存在S1’Elseprint‘学生表S中不存在S1’2．数据库对象的引用规则一般情况下，数据库对象的引用都由以下四部分所组成:lserver_name用于指定所连结的本地服务器或远程服务器的名称。ldatabase_name用于确定在服务器中当前状态下所操作的数据库名称lobject_name在数据库中被引用的数据库对象名称lowner_name表示数据库对象的所有者l所以一个完整的数据对象引用的表示方法应该为：[server_name.][database_name.][owner_name.]object_name其中，服务器名称、数据库名称以及所有者都可以省略例如：shutupandcode.xscj.dbo.班级表、shutupandcode.xscj.dbo.课程信息表。12.1.3Transact-SQL语法元素在大部分Transact-SQL语句中，都包含诸如标识符、数据类型、函数、表达式、运算符、注释以及保留关键字等语法元素。1．标识符用于标识数据库对象的名称，这些对象包括服务器、数据库及相关对象（如表、视图、列、索引、触发器、过程、约束、规则等）。标识符在定义对象时进行命名，当需要使用某个对象时可以通过引用该对象的名称来完成。标识符可划分为常规标识符与分隔标识符两类，其中常规标识符的命名规则如下：l第一个字符必须由字母a-z、A-Z，以及来自其他语言的字母字符或者下划线_、@、#构成，其中@表示局部变量或参数，以##开头的标识符表示全局临时对象，以@@开始的标识符表示全局变量，也称为配置函数。l在定义标识符时，不能占用Transact-SQL的保留字，例如不能将Table、View、Index等定义为一个标识符。l在标识符中不能含有空格，并且标识符中的字符数量不能超过128个。如果定义的标识符不符合上述规则时，即被称为分隔标识符，需要使用双引号””或方括号[]对其进行分割。例如：SELECT*FROM[mytable]1．数据类型Transact-SQL中的基本数据类型如表12-3表所示：表12-3Transact-SQL基本数据类型bigintBinaryBitcharcursordatetimeDecimalFloatimageintmoneyNcharNtextnvarcharrealsmalldatetimeSmallintSmallmoneytexttimestamptinyintVarbinaryVarcharuniqueidentifier（1）整型数据整数数据类型是最常用的数据类型之一，由正整数和负整数所组成，使用bigint、int、smallint和tinyint数据类型进行存储。bigint数据类型可存储的数字范围比int数据类型广。int数据类型比smallint数据类型的存储范围大，而smallint的数值范围又比tinyint类型大。lbigint：可以存储-263到263-1之间的数字，占据8个字节存储空间。lint：可以存储从-231到231-1范围之间的所有整数，占据4字节存储空间。lsmallint：可以存储从-215到215-1范围之间的所有整数，占据2字节存储空间。ltinyint：可以存储从0到255范围之间的所有正整数。（2）浮点数据类型主要包括Real、Float、Decimal和numeric四种类型。lReal：用于存储7位小数的十进制数据，所能够表示的范围为-3.40E+38到1.79E+38。lFloat：可以精确到第15位小数，数据范围为-1.79E-308到1.79E+308。lDecimal：提供小数所需要的实际存储空间，可以存储2到17个字节的从-1038-1到1038-1之间的数值。lnumeric：与Decimal数据类型几乎完全相同，区别是在表格中，只有numeric型的数据可以带有identity关键字的列。（3）字符数据类型SQLServer提供了三种字符数据类型，分别是Char、Varchar和Text。lChar：最长可以容纳8000个字符，并且每个字符占用一个字节的存储空间。使用Char数据类型定义变量时，需要指定数据的最大长度。如果实际数据的字符长度小于指定长度时，剩余的字节用空格来填充。如果实际数据的长度超过了指定的长度，则超出部分将会被删除。在表示字符串常量时，需要使用一对单引号’’将其括起来。lvarchar：该数据类型的使用方式与Char数据类型类似。Char数据类型不同的是，Varchar数据类型所占用的存储空间由字符数据所占据的实际长度来确定。ltext：该数据类型所能表示的最大长度为231-1即2,147,483,647个字符，当需要表示的数据类型长度超过了8000时，可以采用text来处理可变长度的字符数据。（4）日期/时间数据类型日期/时间数据类型可以分为datetime和smalldatetime两类。lDatetime：范围从1753年1月1日到9999年12月31日，可以精确到千分之一秒，此类型的数据占用8个字节的存储空间。lSmalldatetime：数据范围从1900年1月1日到2079年6月6日，可以精确到分，此类型的数据占4个字节的存储空间。（5）货币数据类型SQLServer提供了Money和Smallmoney两种货币数据类型。lMoney：占据8字节存储空间。每4字节分别用于表示货币值的整数部分及小数部分。Money的取值的范围为-263到263-1，并且可以精确到万分之一货币单位。lSmallmoney：占据4字节存储空间。每2字节分别用于表示货币值的整数部分以及小数部分。smallmoney的取值范围为-214,748.3648到+214,748.3647，可以精确到万分之一货币单位。（6）二进制数据类型用于存储二进制数据，有binary、varbinary两种。lBinary:用于存储固定长度的二进制数据，表示数据的长度取值为1到8000个字节。在输入数据时必须在数据前加上字符0X作为二进制标识。如要输入abc则应输入0xabc。若输入的数据过长将会截掉其超出部分，若输入的数据位数为奇数则会在起始符号0X后添加一个0。如上述的0xabc会被系统自动变为0x0abc。lVarbinary:具有可变长度的特性，表示数据的长度也为1到8000个字节，若输入的数据