矿大数据库应用第2章

第2章Transact-SQL语言基础李政伟

T-SQL语言简介

数据类型

系统数据类型和用户定义数据类型

变量、运算符、函数

局部变量和全局变量

运算符

函数

批处理和流程控制

批和脚本

流程控制第2章Transact-SQL语言基础21970年6月，埃德加•考特(EdgarFrankCodd)在CommunicationsofACM上发表了《大型共享数据库数据的关系模型》一文。首次明确而清晰地为数据库系统提出了一种崭新的模型，即关系模型。1970年以后，考特继续致力于完善与发展关系理论。1972年，他提出了关系代数和关系演算的概念，定义了关系的并、交、投影、选择、连接等各种基本运算，为SQL语言的形成和发展奠定了理论基础。1979年，SQL(StructureQueryLanguage)在商业数据库中成功得到了应用。2.1T-SQL语言简介31986年，ANSI正式发表了编号为X3.135-1986的SQL标准，并且在1987年获得了ISO组织的认可，被命名为ISO9075-1987。后来这个标准在1992、1999、2001、2003年等不断地得到了扩充和完善。从SQL语言的历史来看，T-SQL语言与SQL语言并不完全等同。不同的数据库供应商一方面采纳了SQL语言作为自己数据库的操作语言，另一方面又对SQL语言进行了不同程度的扩展。这种扩展的主要原因是不同的数据库供应商为了达到特殊目的和实现新的功能，不得不对标准的SQL语言进行扩展，而这些扩展往往又是SQL标准的下一个版本的主要实践来源。2.1T-SQL语言简介4T-SQL语句分类T-SQL语句相关例子数据操作语句SELECT、INSERT、DELETE、UPDATE数据定义语句CREATETABLE、DROPTABLE、ALTERTABLECREATEVIEW、DROPVIEWCREATEINDEX、DROPINDEXCREATEPROCEDURE、ALTERPROCEDURE、DROPPROCEDURECREATETRIGGER、ALTERTRIGGER、DROPTRIGGER数据控制语句GRANT、DENY、REVOKE附加的语言元素BEGINTRANSACTION/COMMIT、ROLLBACK、SETTRANSACTIONDECLARE、OPEN、FETCH、CLOSE、EXECUTE5SQL语句主要组成部分每条SQL语句均由一个动词（Verb）开始，该动词描述语句要产生的动作。图4-1中的动词SELECT

后紧接着一个或多个子句，子句中给出了被动词作用的数据或提供动词动作的详细信息。图2-1SQLSERVER语句的结构6SELECT语句是SQLServer中最基本和最重要的语句之一。SELECT语句是执行全部命令的基础。所谓查询就是对SQLServer发出一个数据请求，数据查询语句只是提出要“查询什么”和“从什么地方查询”，那“怎么查询”则由数据库管理系统DBMS来完成。SELECT语句的简单用法：

SELECT<列名1,列名2,….,列名N>FROM<表或视图>WHERE<查询条件>T-SQL语句—数据查询语句78标准工具栏9注释程序代码中不执行的文本字符串，用于对代码进行说明或暂时禁用正在进行诊断的部分语句。一般地，注释主要描述程序名称、作者名称、变量说明、代码更改日期、算法描述等。两种注释方式双连字符(--)正斜杠星号字符对(/*…*/)10【例2-2】演示使用注释11

数据类型：数据所代表信息的类型，它关系到系统在内存或磁盘上开辟相应的存储空间。SQLServer2008提供了33种数据类型，分为数字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。需要使用数据类型的对象包括表中的列、视图中的列、定义的局部变量、存储过程中的参数、函数及存储过程的返回值等。2.2数据类型12最常用的数据类型之一，主要用来存储整数值，可直接进行数据运算。

int：4字节，取值范围为-231（-2147483648）~231-1（2147483647）。

Smallint：2字节，取值范围为-215（-32768）~215-1（32768）。

Tinyint：1字节，取值范围为0~255。

Bigint：8字节，取值范围为-263（-9223372036854775808）~263（9223372036854775807）。适用于存储长度超过int范围的整型数据。

Bit：1字节，两种取值：0和1。一般用于保存用来表示逻辑值的数据。例如，是否会员，是否是新消息等。整型13用于存储十进制小数。

Real：取值范围为-3.40E+38~3.40E+38（4字节）

。

Float[(n)]：n为用于存储尾数的位数，n∈[1,53]，默认值为53。范围从-1.79E-308到1.79E+308，（4或8字节）。如不指定float的长度，会被存储在8个字节中。当指定长度为1~24间数值时，则实际上定义了一个real数据类型。如果1<=n<=24，则将n视为24。如果25<=n<=53，则将n视为53。浮点数据类型(Real,float)14Decimal[(p,s)]和Numeric[(p,s)]：两者完全相同，可提供小数所需要的实际存储空间，但也有限制，可用2到17个字节来存储从-10^38+1到10^38-1之间的数值。精度存储字节数1-9510-19920-281329-3817浮点数据类型(Decimal,numeric)注意：SQLSEVER没有无符号类型。15主要用来存储由字母、数字和符号组成的字符串。

Char[(n)]，固定长度，非Unicode字符数据。n的范围为1至8,000，存储大小是n个字节。

Varchar[(n|max)]

。可变长度，非Unicode字符数据。n的范围为1至8,000。max指示最大存储大小是2^31-1个字节。存储大小是输入数据的实际长度加2个字节。

Nchar[(n)]

：n个字符的固定长度的Unicode字符数据。n值必须在1到4,000之间（含）。存储大小为两倍n字节。

Nvarchar[(n|max)]

：可变长度Unicode字符数据。n值在1到4,000之间（含）。max指示最大存储大小为2^31-1字节。字符型16日期时间型

Date：用于存储日期，范围从0001年1月1日至9999年12月31日，占3字节。不含具体的时间。

Datetime：用于存储日期和时间。范围从1753年1月1日零时起到9999年12月31日23时59分59秒。

Smalldatetime：与datetime类似，但其日期时间范围较小，范围从1900年1月1日到2079年6月6日。datetime2[(fractionalsecondsprecision)]

：与datetime类似，不同之处是datetime2秒的小数部分精度更高，存储范围更大：0001年1月1日至9999年12月31日，秒数可以精确到小数点后7位。17

datetimeoffset[(fractionalsecondsprecision)]：用于存储与日期和时区相关的日期时间数据。存储的日期时间数据，需要转化成为UTC（CoordinatedUniversalTime）值的时间，即需要根据时区关系进行换算。格式为YYYY-MM-DDhh:mm:ss[.nnnnnnn][+|_]hh:mm。占用的存储空间因n的取值不同而不同，在8至10个字节之间。

time[(fractionalsecondprecision)]：专用于存储时间的数据类型。格式为hh:mm:ss[.nnnnnnn]，长度为3~5字节。日期时间型18例2.1将字符串转换为各种date和time数据类型。SELECT

CAST('2011-11-0908:35:29.1234567+12:15'AStime(7))AS'time'

,CAST('2011-11-0908:35:29.1234567+12:15'ASdate)AS'date'

,CAST('2011-11-0908:35:29.123'ASsmalldatetime)AS

'smalldatetime'

,CAST('2011-11-0908:35:29.123'ASdatetime)AS'datetime'

,CAST('2011-11-0908:35:29.1234567+12:15'ASdatetime2(7))AS

'datetime2'

,CAST('2011-11-0908:35:29.1234567+12:15'ASdatetimeoffset(7))AS'datetimeoffset';19包括money和smallmoney两种。money用于存储货币值，范围为

-263~263，（8字节）。smallmoney范围比money数据类型小，其存储范围为-2147483468到2147483467之间（4字节）。注意输入money或smallmoney类型数据时，(不是)必须在位置前加货币单位符号。money和smallmoney

类型精确到它们所代表的货币单位的万分之一。货币型20【例2-2】演示MONEY数据类型21文本和图形数据类型

Text：长度可变的非

Unicode数据，最大长度为2^31-1。

Ntext：长度可变的Unicode数据，最大长度为2^30–1。

Image：长度可变的二进制数据，从0到2^31-1(2,147,483,647)个字节。在MicrosoftSQLServer的未来版本中将删除ntext、text和image数据类型。请避免在新开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。请改用nvarchar(max)、varchar(max)和varbinary(max)。

22Binary[(n)]

，长度为n字节的固定长度二进制数据，其中n取值从1到8,000。存储大小为n字节。当输入的二进制数据长度小于n时，余下部分填充0。

Varbinary[(n|max)]

，可变长度二进制数据。n取值从1到8,000。max指示最大存储大小为2^31-1字节。二进制型23程序用数据类型hierarchyid：新增的一种用于存储层次化结构型数据的数据类型。采用hierarchyid来存储，可以利用hierarchyid提供的函数，非常方便地实现数据的存储和节点搜索。geometry：用于存储平面几何对象（平面球）的数据类型，如点、多边形、曲线等11种几何度量中的一种。Geography：用于存储GPS等全球定位类型的地理数据（椭圆球），以纬度和经度为度量来存储。XML：用于存放整个XML文档或者部分片段。Table：用于存储对表或者视图处理后的结果集的数据类型。这种数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。24程序用数据类型Cursor：一种变量或存储过程的输出参数使用的数据类型，也称游标。提供了一种逐行处理查询数据的功能。用cursor定义的变量只能用于定义游标和与游标有关的语句，不能在表设计时使用。sql_variant：允许存储多个不同类型数据值的数据类型，除了varchar(max)、nvarchar(max)、text、image、sql_variant、sql_variant(max)、xml、ntext、rowversion等之外的数据类型都可以存储。Uniqueidentifier：用于存储一个16字节长的二进制数据类型，它是SQLServer根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代码（GloballyUniqueIdentifier，简写为GUID）。具有更新订阅的合并复制和事务复制使用uniqueidentifier列来确保在表的多个副本中唯一地标识行。25通过对象资源管理器创建在“对象资源管理器”中展开数据库，右击“可编程性->类型”，单击“新建”→“用户定义数据类型”菜单项，在弹出的“用户定义数据类型属性”对话框中，输入新建数据类型的名称，选择系统数据类型；更改此数据类型可存储的最大数据长度，选择“允许空值”。用户定义数据类型26利用系统存储过程创建语法：sp_addtype

type_name,phystype[(length)|([precision,scale]),null|notnull|identity]type_name，用户定义的数据类型的名字；Phystype，基于的系统数据类型，可包括长度、精度、标度；null|notnull|identity，用户自定义数据类型的性质，分别为允许空值、不允许为空值、具有标识列性质。提醒：后续版本的MicrosoftSQLServer将删除该功能。

用户定义数据类型27用户定义数据类型28右击“用户定义数据类型”，单击“删除”，再单击“确定”。也可用系统存储过程来查看、重命名、删除用户定义的数据类型。 sp_helptype_name查看用户自定义数据类型的特征；sp_renametype_name给用户自定义数据类型重新命名；sp_droptypetype_name删除一个已经定义且未被使用的用户自定义数据类型。删除用户定义数据类型2930

变量

局部变量和全局变量常量

运算符运算符的类别运算符的优先级别

系统函数数学函数、字符串函数日期和时间函数转换函数系统函数聚合函数2.3变量、运算符、函数31局部变量：由用户根据需要定义的变量，使用范围只局限于某一个语句或过程体内。主要用于保存临时数据或由存储过程返回的结果。作用域是其被声明时所在批处理。必须先用DECLARE语句定义后才可以使用。局部变量32定义语法形式：

DECLARE{{{@local_variable[AS]data_type}|[=value]}|{@cursor_variable_nameCURSOR}}[,...n]局部变量不能是text，ntext

或image数据类型。声明局部变量之后，会将其设为NULL，如设定新值，须用SELECT或者SET命令。其语法形式为：

SET{{@local_variable=expression}或

SELECT{@local_variable=expression}[,...n]其中，参数expression是任何有效的SQLServer表达式。局部变量33例2.4，声明一个长度为9个字符的变量@city，并赋值“Welcometochangsha”。DECLARE@cityCHAR(9)SELECT@city='Welcometochangsha'SELECT@city建议：当执行简单变量赋值时，使用SET语句；当基于查询进行变量赋值时，使用SELECT语句。局部变量定义与赋值34由SQLSERVER预先定义并负责维护，主要用于保存系统的某些参数值和性能统计数据，作用域覆盖所有程序。全局变量都以标记符“@@”开头。分类，一是与SQLServer连接有关的全局变量，如@@rowcount表示受最近一个语句影响的行数；二是关于系统内部信息有关的全局变量，如@@version表示SQLServer的版本号。@@IDENTITY：返回最后一条Insert语句执行后的标识值。注意：局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。全局变量（系统函数）35例2.7返回当前用户进程的会话ID、登录名和用户名。

SELECT@@SPIDAS'ID',SYSTEM_USERAS'LoginName',USERAS'UserName'全局变量示例36常量常量：程序运行中值不变的量，也称为文字值或标量值。常量格式取决于其所表示的值的数据类型。例，'Thisisabook.'、'May1,2006'、98321等。对于字符常量或时间日期型常量，需用单引号引起来。分类：字符串常量、二进制常量、bit常量、datetime常量、integer常量、decimal常量、float和real常量、money常量、uniqueidentifier常量等。37主要有六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串连接运算符(参见P29)。运算符的优先级别运算符38内置函数 MicrosoftSQLServer2008系统提供了许多内置函数，这些函数可以完成许多特殊的操作，增强了系统的功能，提高了系统的易用性。

39数学函数40字符串函数41例2.8先使用SUBSTRING函数从字符串中获取指定的子字符串，然后使用LOWER函数、UPPER函数对子字符串进行转换，且把UPPER函数嵌套在LOWER函数内或把LOWER函数嵌套在UPPER内。DECLARE@string_lower_uppervarchar(40)SET@string_lower_upper='InformationModelCreationIssues.'SELECTLOWER(SUBSTRING(@string_lower_upper,1,11))ASLower,

UPPER(SUBSTRING(@string_lower_upper,13,5))ASUpper,

LOWER(UPPER(SUBSTRING(@string_lower_upper,19,8)))AsLowerUpper,

UPPER(LOWER(SUBSTRING(@string_lower_upper,28,7)))AsUpperLower字符串函数42日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。

43转换函数SQLServer能够自动处理某些数据类型的转换。例如，char和varchar、smallint和int，可以将自动转换（隐式转换）。但无法自动转换或者自动转换的结果不符合预期结果的，就需要使用转换函数做显式转换。44data_type

作为目标的系统提供数据类型。length

nchar、nvarchar、char、varchar、binary或varbinary