第5章++T-SQL语言-1.

第5章T-SQL语言5.1SQL语言与T-SQL语言5.2常量、变量与数据类型5.3运算符与表达式5.4流程控制语句5.5系统内置函数5.6用户定义函数5.1SQL语言与T-SQL语言1．什么是SQL语言SQL语言的全名是结构化查询语言（StructuredQueryLanguage），是用于数据库中的标准数据查询语言，IBM公司最早使用该语言在其开发的数据库系统中。1986年10月，美国ANSI对SQL进行规范后，以此作为关系数据库管理系统的标准语言。作为关系数据库的标准语言，它已被众多商用数据库管理系统产品所采用，不过，不同的数据库管理系统在其实践过程中都对SQL规范做了某些改变和扩充。所以，实际上，不同数据库管理系统之间的SQL语言不能完全通用。例如，微软公司的MSSQL-Server支持的是T-SQL，而甲骨文公司的Oracle数据库所使用的SQL语言则是PL-SQL。2．什么是T-SQL语言T-SQL是SQL语言的一种版本，且只能在微软MSSQL-Server以及SybaseAdaptiveServer系列数据库上使用。T-SQL是ANSISQL的扩展加强版语言，除了提供标准的SQL命令之外，T-SQL还对SQL做了许多补充，提供了类似C、BASIC和Pascal的基本功能，如变量说明、流控制语言、功能函数等。5.1SQL语言与T-SQL语言3．T-SQL语言的构成在SQLServer数据库中，T-SQL语言由以下几部分组成。（1）数据定义语言（DDL）。DDL用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。如前所述，数据库对象主要包括表、默认约束、规则、视图、触发器、存储过程。DDL包括的主要语句及功能如表5.1所示。语句功能说明CREATE创建数据库或数据库对象不同数据库对象，其CREATE语句的语法形式不同ALTER对数据库或数据库对象进行修改不同数据库对象，其ALTER语句的语法形式不同DROP删除数据库或数据库对象不同数据库对象，其DROP语句的语法形式不同表5.1DDL主要语句及功能5.1SQL语言与T-SQL语言DDL各语句的语法、使用方法及举例请参考相关章节。（2）数据操纵语言（DML）。DML用于操纵数据库中的各种对象，检索和修改数据。DML包括的主要语句及功能如表5.2所示。语句功能说明SELECT从表或视图中检索数据是使用最频繁的SQL语句之一INSERT将数据插入到表或视图中UPDATE修改表或视图中的数据既可修改表或视图的一行数据，也可修改一组或全部数据DELETE从表或视图中删除数据可根据条件删除指定的数据表5.2DML主要语句及功能5.1SQL语言与T-SQL语言DML各语句的语法、使用方法及举例请参考相关章节。（3）数据控制语言（DCL）。DCL用于安全管理，确定哪些用户可以查看或修改数据库中的数据。DCL包括的主要语句及功能如表5.3所示。语句功能说明GRANT授予权限可把语句许可或对象许可的权限授予其他用户和角色REVOKE收回权限与GRANT的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限DENY收回权限，并禁止从其他角色继承许可权限功能与REVOKE相似，不同之处是，除收回权限外，还禁止从其他角色继承许可权限表5.3DCL主要语句及功能DCL各语句的语法、使用方法及举例请参考相关章节。（4）T-SQL增加的语言元素。这部分不是ANSISQL所包含的内容，而是微软为了用户编程的方便而增加的语言元素。这些语言元素包括变量、运算符、流程控制语句、函数等。这些T-SQL语句都可以在查询分析器中交互执行。本章将介绍这部分增加的语言元素。5.2常量、变量与数据类型5.2.1常量根据常量值的不同类型，常量分为字符串常量、整型常量、实型常量、日期时间常量、货币常量、唯一标识常量。各类常量举例说明如下。1．字符串常量字符串常量分为ASCII字符串常量和Unicode字符串常量。（1）ASCII字符串常量。ASCII字符串常量是用单引号括起来，由ASCII字符构成的符号串。ASCII字符串常量举例如下：'China''Howdoyou!''O''Bbaar'/*如果单引号中的字符串包含引号，可以使用两个单引号来表示嵌入的单引号。*/5.2.1常量（2）Unicode字符串常量。Unicode字符串常量与ASCII字符串常量相似，但它前面有一个N标识符（N代表SQL-92标准中的国际语言NationalLanguage），N前缀必须为大写字母。Unicode字符串常量举例如下：N'China'N'Howdoyou!'Unicode数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。5.2.1常量2．整型常量按照不同表示方式，整型常量又分为二进制整型常量、十六进制整型常量和十进制整型常量。十六进制整型常量的表示：前辍0x后跟十六进制数字串。十六进制常量举例：0xEBF0x69048AEFDD010E0x/*空十六进制常量*/二进制整型常量的表示：即数字0或1，并且不使用引号。如果使用一个大于1的数字，它将被转换为1。十进制整型常量即不带小数点的十进制数，例如，18942+145345234-21474836485.2.1常量3．实型常量实型常量有定点表示和浮点表示两种方式，举例如下。定点表示：1894.12042.0+145345234.2234-2147483648.10浮点表示：101.5E50.5E-2+123E-3-12E55.2.1常量4．日期时间常量日期时间常量：用单引号将表示日期时间的字符串括起来构成。SQLServer可以识别如下格式的日期和时间。字母日期格式，如'April20,2000'；数字日期格式，如'4/15/1998'，'1998-04-15'；未分隔的字符串格式，如'20001207'；以下是时间常量的例子：'14:30:24''04:24:PM'以下是日期时间常量的例子：'April20,200014:30:24'5.2.1常量5．money常量money常量是以“$”作为前缀的一个整型或实型常量数据。下面是money常量的例子：$12$542023-$45.56+$423456.996．uniqueidentifier常量uniqueidentifier常量是用于表示全局唯一标识符（GUID）值的字符串。可以使用字符串或十六进制字符串格式指定。例如，'6F9619FF-8A86-D011-B42D-00004FC964FF'0xff19966f868b11d0b42d00c04fc964ff5.2.2数据类型1．系统数据类型系统数据类型又称为基本数据类型。在第3章已详细地介绍了系统数据类型，此处不再赘述。2．用户自定义数据类型用户自定义数据类型可看做系统数据类型的别名。在多表操作的情况下，当多个表中的列要存储相同类型的数据时，往往要确保这些列具有完全相同的数据类型、长度和为空性（数据类型是否允许空值）。用户自定义数据类型并不是真正的数据类型，它只是提供了一种提高数据库内部元素和基本数据类型之间一致性的机制。5.2.2数据类型用户自定义数据类型student_num后，可以重新设计学生成绩管理数据库表XSB、CJB结构中的学号字段，如表5.4、表5.5和表5.6所示。依赖的系统类型值允许的长度为空性char6NOTNULL表5.4自定义类型student_num字段名类型字段名类型学号student_num学号student_num表5.5表XSB中学号字段的重新设计表5.6表CJB中学号字段的重新设计5.2.2数据类型通过上例可知：要使用用户自定义类型，首先应定义该类型，然后用这种类型来定义字段或变量。在创建用户自定义数据类型时首先应考虑如下三个属性：数据类型名称；新数据类型所依据的系统数据类型（又称为基类型）；为空性。如果为空性未明确定义，系统将依据数据库或连接的ANSINull默认设置进行指派。创建用户自定义数据类型的方法如下。（1）使用对象资源管理器定义。步骤如下。第1步：启动“SQLServerManagementStudio”，在“对象资源管理器”中展开“数据库→PXSCJ→可编程性”，右击“类型”，选择“新建”选项，再选择“新建用户定义数据类型”，弹出“新建用户定义数据类型”窗口。5.2.2数据类型第2步：在“名称”文本框中输入自定义的数据类型名称，如student_num。在“数据类型”下拉框中选择自定义数据类型所基于的系统数据类型，如char。在“长度”栏中填写要定义的数据类型的长度，如6。其他选项使用默认值，如图5.1所示，单击“确定”按钮即可完成创建。图5.1“新建用户定义数据类型属性”窗口5.2.2数据类型（2）使用命令定义。在SQLServer2008中，使用CREATETYPE语句来实现用户数据类型的定义。语法格式：CREATETYPE[schema_name.]type_nameFROMbase_type[(precision[,scale])][NULL|NOTNULL][;]根据上述语法，定义描述学号字段的数据类型的语句如下：CREATETYPEstudent_numFROMchar(6)NOTNULL5.2.2数据类型（3）删除用户自定义数据类型。在SSMS中删除用户自定义数据类型的主要步骤如下：在对象资源管理器中展开数据库“PXSCJ→可编程性→类型”，在“用户定义数据类型”中选择类型“dbo.student_num”，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，打开“删除对象”窗口后单击“确定”按钮即可（实际不做操作）。（4）使用命令删除用户自定义数据类型。使用命令方式删除自定义数据类型可以使用DROPTYPE语句。语法格式：DROPTYPE[schema_name.]type_name[;]例如，删除前面定义的student_num类型的语句为DROPTYPEstudent_num5.2.2数据类型（5）利用用户自定义数据类型定义字段。在定义类型后，接着应考虑定义这种类型的字段，同样可以利用对象资源管理器和T-SQL命令两种方式实现。读者可以参照第2章进行定义，不同点只是数据类型为用户自定义类型，而不是系统类型。例如，在对象资源管理器中对于XSB表学号字段的定义如图5.2所示。定义student_num后，设计表结构时此类型可用图5.2使用用户自定义数据类型定义XSB表5.2.2数据类型利用命令方式定义XSB表结构：CREATETABLEXSB(学号student_numNOTNULLPRIMARYKEY,/*将学号定义为student_num类型*/姓名char(8)NOTNULL,性别bitNULLDEFAULT1,出生时间datetimeNULL,专业char(12)NULL,总学分intNULL,备注varchar(500)NULL)5.2.2数据类型3．用户自定义表数据类型SQLServer2008还提供了一种新的用户自定义数据类型，称为用户自定义表数据类型（User-definedTableTypes）。这种数据类型也由用户自行定义，可以作为参数提供给语句、存储过程或者函数。创建自定义表数据类型也使用CREATETYPE语句，语法格式如下：CREATETYPE[schema_name.]type_nameASTABLE(column_definition[table_constraint][,...n])[;]【例5.1】创建用户自定义表数据类型，包含CJB表中的所有列。CREATETYPECJB_tabletypeASTABLE(学号char(6)NOTNULL,课程号char(3)NOTNULL,成绩intNOTNULL,PRIMARYKEY(学号,课程号))5.2.3变量1．变量变量名必须是一个合法的标识符。（1）标识符。在SQLServer中标识符分为两类。常规标识符：以ASCII字母、Un