矿大数据库应用第2章67

第2章Transact-SQL语言基础李政伟2T-SQL语言简介数据类型系统数据类型和用户定义数据类型变量、运算符、函数局部变量和全局变量运算符函数批处理和流程控制批和脚本流程控制第2章Transact-SQL语言基础31970年6月，埃德加•考特(EdgarFrankCodd)在CommunicationsofACM上发表了《大型共享数据库数据的关系模型》一文。首次明确而清晰地为数据库系统提出了一种崭新的模型，即关系模型。1970年以后，考特继续致力于完善与发展关系理论。1972年，他提出了关系代数和关系演算的概念，定义了关系的并、交、投影、选择、连接等各种基本运算，为SQL语言的形成和发展奠定了理论基础。1979年，SQL(StructureQueryLanguage)在商业数据库中成功得到了应用。2.1T-SQL语言简介41986年，ANSI正式发表了编号为X3.135-1986的SQL标准，并且在1987年获得了ISO组织的认可，被命名为ISO9075-1987。后来这个标准在1992、1999、2001、2003年等不断地得到了扩充和完善。从SQL语言的历史来看，T-SQL语言与SQL语言并不完全等同。不同的数据库供应商一方面采纳了SQL语言作为自己数据库的操作语言，另一方面又对SQL语言进行了不同程度的扩展。这种扩展的主要原因是不同的数据库供应商为了达到特殊目的和实现新的功能，不得不对标准的SQL语言进行扩展，而这些扩展往往又是SQL标准的下一个版本的主要实践来源。2.1T-SQL语言简介5T-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、EXECUTE6SQL语句主要组成部分每条SQL语句均由一个动词（Verb）开始，该动词描述语句要产生的动作。图4-1中的动词SELECT后紧接着一个或多个子句，子句中给出了被动词作用的数据或提供动词动作的详细信息。图2-1SQLSERVER语句的结构7SELECT语句是SQLServer中最基本和最重要的语句之一。SELECT语句是执行全部命令的基础。所谓查询就是对SQLServer发出一个数据请求，数据查询语句只是提出要“查询什么”和“从什么地方查询”，那“怎么查询”则由数据库管理系统DBMS来完成。SELECT语句的简单用法：SELECT列名1,列名2,….,列名NFROM表或视图WHERE查询条件T-SQL语句—数据查询语句89标准工具栏连接(连接服务器)增加缩进断开连接(断开与服务器的连接)更改连接(重新指定连接的服务器)可用数据库(用户可以从数据库列表中指定当前数据库)执行(执行当前选定的Transact-SQL语句)分析(对当前选定的Transact-SQL语句进行分析)取消执行查询(中止正在执行的Transact-SQL语句)显示估计的执行计划(以图形方式显示)在数据库引擎优化顾问中分析查询在编辑器中设计查询(调用编辑器图形化设计查询语句)指定模板中的参数值(指定当前选定模板中的参数值)包括实际的执行计划(在查询结果中同时显示执行计划)包括客户端统计信息(在查询结果中同时显示客户端统计数据)SQLCMD模式(启用SQLCMD脚本撰写模式)以文本格式显示结果以网格显示结果将结果保存到文件中注释选中行取消对选中行的注释减少缩进10注释程序代码中不执行的文本字符串，用于对代码进行说明或暂时禁用正在进行诊断的部分语句。一般地，注释主要描述程序名称、作者名称、变量说明、代码更改日期、算法描述等。两种注释方式双连字符(--)正斜杠星号字符对(/*…*/)11【例2-2】演示使用注释12数据类型：数据所代表信息的类型，它关系到系统在内存或磁盘上开辟相应的存储空间。SQLServer2008提供了33种数据类型，分为数字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。需要使用数据类型的对象包括表中的列、视图中的列、定义的局部变量、存储过程中的参数、函数及存储过程的返回值等。2.2数据类型13最常用的数据类型之一，主要用来存储整数值，可直接进行数据运算。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。一般用于保存用来表示逻辑值的数据。例如，是否会员，是否是新消息等。整型14用于存储十进制小数。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)15Decimal[(p,s)]和Numeric[(p,s)]：两者完全相同，可提供小数所需要的实际存储空间，但也有限制，可用2到17个字节来存储从-10^38+1到10^38-1之间的数值。精度存储字节数1-9510-19920-281329-3817浮点数据类型(Decimal,numeric)注意：SQLSEVER没有无符号类型。16主要用来存储由字母、数字和符号组成的字符串。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字节。字符型17日期时间型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位。18datetimeoffset[(fractionalsecondsprecision)]：用于存储与日期和时区相关的日期时间数据。存储的日期时间数据，需要转化成为UTC（CoordinatedUniversalTime）值的时间，即需要根据时区关系进行换算。格式为YYYY-MM-DDhh:mm:ss[.nnnnnnn][+|_]hh:mm。占用的存储空间因n的取值不同而不同，在8至10个字节之间。time[(fractionalsecondprecision)]：专用于存储时间的数据类型。格式为hh:mm:ss[.nnnnnnn]，长度为3~5字节。日期时间型19例2.1将字符串转换为各种date和time数据类型。SELECTCAST('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';20包括money和smallmoney两种。money用于存储货币值，范围为-263~263，（8字节）。smallmoney范围比money数据类型小，其存储范围为-2147483468到2147483467之间（4字节）。注意输入money或smallmoney类型数据时，(不是)必须在位置前加货币单位符号。money和smallmoney类型精确到它们所代表的货币单位的万分之一。货币型21【例2-2】演示MONEY数据类型22文本和图形数据类型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)。23Binary[(n)]，长度为n字节的固定长度二进制数据，其中n取值从1到8,000。存储大小为n字节。当输入的二进制数据长度小于n时，余下部分填充0。Varbinary[(n|max)]，可变长度二进制数据。n取值从1到8,000。max指示最大存储大小为2^31-1字节。二进制型24程序用数据类型hierarchyid：新增的一种用于存储层次化结构型数据的数据类型。采用hierarchyid来存储，可以利用hierarchyid提供的函数，非常方便地实现数据的存储和节点搜索。geometry：用于存储平面几何对象（平面球）的数据类型，如点、多边形、曲线等11种几何度量中的一种。Geography：用于存储GPS等全球定位类型的地理数据（椭圆球），以纬度和经度为度量来存储。XML：用于存放整个XML文档或者部分片段。Table：用于存储对表或者视图处理后的结果集的数据类型。这种数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。25程序用数据类型Cursor：一种变量或存储过程的输出参数使用的数据类型，也称游标。提供了一种逐行处理查询数据的功能。用cursor定义的变量只能用于定义游标和与游标有关的语句，不能在表设