《矿大数据库应用》由会员分享，可在线阅读，更多相关《矿大数据库应用（72页珍藏版）》请在金锄头文库上搜索。
1、第第2章章Transact-SQL语言基础语言基础n T-SQL语言简介语言简介n 数据类型数据类型 系统数据类型系统数据类型和和用户定义数据类型用户定义数据类型n 变量、运算符、函数变量、运算符、函数 局部变量和全局变量局部变量和全局变量 运算符运算符 函数函数n 批处理和流程控制批处理和流程控制 批和脚本批和脚本 流程控制流程控制第第2章章Transact-SQL语言基础语言基础2l1970年年6月月，埃德加，埃德加考特考特(EdgarFrankCodd)在在CommunicationsofACM上发表了大型共享数据库数据的上发表了大型共享数据库数据的关系模型一文。首次明确而清晰地为数据库
2、系统提出了一关系模型一文。首次明确而清晰地为数据库系统提出了一种崭新的模型，种崭新的模型，即关系模型。即关系模型。l1970年以后，考特继续致力于完善与发展关系理论。年以后，考特继续致力于完善与发展关系理论。l1972年，他提出了关系代数和关系演算的概念，年，他提出了关系代数和关系演算的概念，定义了关系定义了关系的并、交、投影、选择、连接等各种基本运算，为的并、交、投影、选择、连接等各种基本运算，为SQL语言语言的形成和发展奠定了理论基础。的形成和发展奠定了理论基础。l1979年，年，SQL(StructureQueryLanguage)在商业数据库中成在商业数据库中成功得到了应用。功得到了应
3、用。2.1T-SQL语言简介语言简介3l1986年，年，ANSI正式发表了编号为正式发表了编号为X3.135-1986的的SQL标准，标准，并且在并且在1987年获得了年获得了ISO组织的认可，被命名为组织的认可，被命名为ISO9075-1987。后来这个标准在。后来这个标准在1992、1999、2001、2003年等不断年等不断地得到了扩充和完善。地得到了扩充和完善。l从从SQL语言的历史来看，语言的历史来看，T-SQL语言与语言与SQL语言并不完全语言并不完全等同等同。不同的数据库供应商一方面采纳了。不同的数据库供应商一方面采纳了SQL语言作为自语言作为自己数据库的操作语言，另一方面又对己
4、数据库的操作语言，另一方面又对SQL语言进行了不同语言进行了不同程度的扩展。这种扩展的主要原因是不同的数据库供应商程度的扩展。这种扩展的主要原因是不同的数据库供应商为了达到特殊目的和实现新的功能，不得不对标准的为了达到特殊目的和实现新的功能，不得不对标准的SQL语言进行扩展，而这些扩展往往又是语言进行扩展，而这些扩展往往又是SQL标准的下一个版标准的下一个版本的主要实践来源。本的主要实践来源。2.1T-SQL语言简介语言简介4T-SQL语句分类语句分类T-SQL语句语句相关例子相关例子数据操作语句数据操作语句SELECT、INSERT、DELETE、UPDATE数据定义语句数据定义语句CREA
5、TETABLE、DROPTABLE、ALTERTABLECREATEVIEW、DROPVIEWCREATEINDEX、DROPINDEXCREATEPROCEDURE、ALTERPROCEDURE、DROPPROCEDURECREATETRIGGER、ALTERTRIGGER、DROPTRIGGER数据控制语句数据控制语句GRANT、DENY、REVOKE附加的语言元素附加的语言元素BEGINTRANSACTION/COMMIT、ROLLBACK、SETTRANSACTIONDECLARE、OPEN、FETCH、CLOSE、EXECUTE5SQL语句主要组成部分语句主要组成部分l每条每条SQL
6、语句均由一个语句均由一个动词动词（Verb）开始，该动词描述）开始，该动词描述语句要产生的语句要产生的动作动作。图。图4-1中的动词中的动词SELECT后紧接着一后紧接着一个或多个个或多个子句子句，子句中给出了子句中给出了被动词作用的数据被动词作用的数据或或提供动提供动词动作的详细信息词动作的详细信息。图图2-1SQLSERVER语句的结构语句的结构6qSELECT语句是语句是SQLServer中最基本和最重要的语句之中最基本和最重要的语句之一。一。SELECT语句是执行全部命令的基础。语句是执行全部命令的基础。q所谓查询就是对所谓查询就是对SQLServer发出一个数据请求，数据查发出一个数
7、据请求，数据查询语句只是提出要询语句只是提出要“查询什么查询什么”和和“从什么地方查询从什么地方查询”，那那“怎么查询怎么查询”则由数据库管理系统则由数据库管理系统DBMS来完成。来完成。qSELECT语句的简单用法语句的简单用法：SELECTFROMWHERET-SQL语句语句数据查询语句数据查询语句78标准工具栏标准工具栏9注释注释l程序代码中不执行的文本字符串，用于对代码进程序代码中不执行的文本字符串，用于对代码进行说明或暂时禁用正在进行诊断的部分语句。一行说明或暂时禁用正在进行诊断的部分语句。一般地，注释主要描述般地，注释主要描述程序名称程序名称、作者名称作者名称、变量变量说明说明、代
8、码更改日期代码更改日期、算法描述算法描述等。等。l两种注释方式两种注释方式l双连字符双连字符(-)l正斜杠星号字符对正斜杠星号字符对(/*/)10【例【例2-2】演示使用注释】演示使用注释 11q数据类型数据类型：数据所代表信息的类型，它关系到：数据所代表信息的类型，它关系到系统在内存或磁盘上开辟相应的存储空间。系统在内存或磁盘上开辟相应的存储空间。qSQLServer2008提供了提供了33种数据类型，分为种数据类型，分为数数字数据类型字数据类型、字符数据类型字符数据类型、日期和时间数据类日期和时间数据类型型、二进制数据类型二进制数据类型以及以及其他数据类型其他数据类型。q需要使用数据类型的
9、对象包括表中的需要使用数据类型的对象包括表中的列列、视图中、视图中的的列列、定义的、定义的局部变量局部变量、存储过程中的、存储过程中的参数参数、函、函数及存储过程的数及存储过程的返回值返回值等。等。2.2数据类型数据类型12q最常用的数据类型之一，主要用来存储整数值，可直接进最常用的数据类型之一，主要用来存储整数值，可直接进行数据运算。行数据运算。qint：4字节，取值范围为字节，取值范围为-231（-2147483648）231-1（2147483647）。qSmallint：2字节，取值范围为字节，取值范围为-215（-32768）215-1（32768）。）。qTinyint：1字节，取
10、值范围为字节，取值范围为0255。qBigint：8字节，取值范围为字节，取值范围为-263（-9223372036854775808）263（9223372036854775807）。适用于存储）。适用于存储长度超过长度超过int范围的整型数据范围的整型数据。qBit：1字节，两种取值：字节，两种取值：0和和1。一般用于保存用来表示。一般用于保存用来表示逻辑值的数据。例如，是否会员，逻辑值的数据。例如，是否会员，是否是新消息等。是否是新消息等。整型整型13q用于存储十进制小数。用于存储十进制小数。qReal：取值范围为：取值范围为-3.40E+383.40E+38（4字节）字节） 。qFlo
11、at(n)：n为用于存储为用于存储尾数的位数，尾数的位数，n1,53，默认值为默认值为53。范围从。范围从-1.79E-308到到1.79E+308，（，（4或或8字节）。字节）。n如不指定如不指定float的长度，会被存储在的长度，会被存储在8个字节中。当指定长个字节中。当指定长度为度为124间数值时，则实际上定义了一个间数值时，则实际上定义了一个real数据类型。数据类型。n如果如果1=n=24，则将，则将n视为视为24。如果。如果25=n类型类型”，单击，单击“新建新建”“用户定义用户定义数据类型数据类型”菜单项，在弹出的菜单项，在弹出的“用户定义数据类型属性用户定义数据类型属性”对话框
12、对话框中，输入新建数据类型的中，输入新建数据类型的名称名称，选择选择系统数据类型系统数据类型；更改此数据；更改此数据类型可存储的最大数据长度，选类型可存储的最大数据长度，选择择“允许空值允许空值”。用户定义数据类型用户定义数据类型26q利用系统存储过程创建利用系统存储过程创建q语法：语法：sp_addtypetype_name,phystype(length)|(precision,scale),null|notnull|identityqtype_name，用户定义的数据类型的名字；，用户定义的数据类型的名字；qPhystype，基于的系统数据类型，可包括长度、精度、标，基于的系统数据类型，
13、可包括长度、精度、标度；度；qnull|notnull|identity，用户自定义数据类型的性质，分，用户自定义数据类型的性质，分别为允许空值、不允许为空值、具有标识列性质。别为允许空值、不允许为空值、具有标识列性质。q提醒提醒：后续版本的：后续版本的MicrosoftSQLServer将删除该功能。将删除该功能。 用户定义数据类型用户定义数据类型27用户定义数据类型用户定义数据类型28q右击右击“用户定义数据类型用户定义数据类型”，单击，单击“删除删除”，再再单击单击“确确定定”。q也可用系统存储过程来查看、重命名、删除用户定义的数也可用系统存储过程来查看、重命名、删除用户定义的数据类型。
14、据类型。qsp_helptype_name查看用户自定义数据类型的特征；查看用户自定义数据类型的特征；qsp_renametype_name给用户自定义数据类型重新命名；给用户自定义数据类型重新命名；qsp_droptypetype_name删除一个已经定义且删除一个已经定义且未被使用未被使用的用的用户自定义数据类型。户自定义数据类型。删除用户定义数据类型删除用户定义数据类型2930p 变量变量 局部变量局部变量和全局变量和全局变量p 常量常量p 运算符运算符 运算符的类别运算符的类别 运算符的优先级别运算符的优先级别 p 系统函数系统函数 数学函数、字符串函数数学函数、字符串函数 日期和时间
15、函数日期和时间函数 转换函数转换函数 系统函数系统函数 聚合函数聚合函数 2.3变量、运算符、函数变量、运算符、函数31q局部局部变量变量：由用户根据需要定义的：由用户根据需要定义的变量变量，使用范围，使用范围只局限于某一个语句或过程体内。只局限于某一个语句或过程体内。q主要用于保存临时数据或由存储过程返回的结果。主要用于保存临时数据或由存储过程返回的结果。q作用域是其被声明时所在批处理。作用域是其被声明时所在批处理。q必须先用必须先用DECLARE语句定义后才可以使用语句定义后才可以使用。局部变量局部变量32l例例2.4，声明一个长度为，声明一个长度为9个字符的变量个字符的变量city，并赋
16、值，并赋值“Welcometochangsha”。DECLAREcityCHAR(9)SELECTcity=WelcometochangshaSELECTcityl建议建议：当执行简单变量赋值时，使用当执行简单变量赋值时，使用SET语句；当基于查语句；当基于查询进行变量赋值时，使用询进行变量赋值时，使用SELECT语句。语句。局部变量定义与赋值局部变量定义与赋值34q由由SQLSERVER预先定义并负责维护，主要用于保存系统预先定义并负责维护，主要用于保存系统的某些参数值和性能统计数据，作用域覆盖所有程序。的某些参数值和性能统计数据，作用域覆盖所有程序。q全局变量都以标记符全局变量都以标记符“
17、”开头。开头。q分类分类，一是与，一是与SQLServer连接有关的全局变量，如连接有关的全局变量，如rowcount表示受最近一个语句影响的行数；二是关于系统表示受最近一个语句影响的行数；二是关于系统内部信息有关的全局变量，如内部信息有关的全局变量，如version表示表示SQLServer的的版本号。版本号。qIDENTITY：返回最后一条：返回最后一条Insert语句执行后的标识值。语句执行后的标识值。q注意注意：局部变量的名称不能与全局变量的名称相同，否则：局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。会在应用程序中出现不可预测的结果。全局变量（系统函数
18、）全局变量（系统函数）35例例2.7返回当前用户进程的会话返回当前用户进程的会话ID、登录名和用户名。、登录名和用户名。SELECTSPIDASID,SYSTEM_USERASLoginName,USERASUserName全局变量示例全局变量示例36常量常量l常量常量：程序运行中值不变的量，也称为文字值或：程序运行中值不变的量，也称为文字值或标量值标量值。l常量格式取决于其所表示的值的数据类型。例，常量格式取决于其所表示的值的数据类型。例，Thisisabook.、May1,2006、98321等。等。对于对于字符常量或时间日期型常量，需用单引号引起来字符常量或时间日期型常量，需用单引号引起
19、来。l分类分类：字符串常量、二进制常量：字符串常量、二进制常量、bit常量常量、datetime常量常量、integer常量、常量、decimal常量、常量、float和和real常量、常量、money常量、常量、uniqueidentifier常量等。常量等。37主要有六大类：主要有六大类：算术运算符算术运算符、赋值运算符赋值运算符、位运算符位运算符、比较运算符比较运算符、逻辑运算符逻辑运算符以及以及字符串字符串连接运算符连接运算符(参见参见P29)。运算符的优先级别运算符的优先级别运算符运算符38内置函数内置函数MicrosoftSQLServer2008系统提供了许多系统提供了许多内置函
20、数，这些函数可以完成许多特殊的操作，内置函数，这些函数可以完成许多特殊的操作，增强了系统的功能，提高了系统的易用性。增强了系统的功能，提高了系统的易用性。39数学函数数学函数40字符串函数字符串函数41例例2.8先使用先使用SUBSTRING函数从字符串中获取指定的子字符串，函数从字符串中获取指定的子字符串，然后使用然后使用LOWER函数、函数、UPPER函数对子字符串进行转换，函数对子字符串进行转换，且把且把UPPER函数嵌套在函数嵌套在LOWER函数内或把函数内或把LOWER函数嵌函数嵌套在套在UPPER内。内。DECLAREstring_lower_uppervarchar(40)SET
21、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日期和时间函数日期和时间函数用于对日期
22、和时间数据进行各种不同的处理和运算，并返用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。回一个字符串、数字值或日期和时间值。 43转换函数转换函数SQLServer能够自动处理某些数据类型的转换。例如，能够自动处理某些数据类型的转换。例如，char和和varchar、smallint和和int，可以将自动转换（隐式转，可以将自动转换（隐式转换）。但无法自动转换或者自动转换的结果不符合预期结换）。但无法自动转换或者自动转换的结果不符合预期结果的，就需要使用转换函数做显式转换。果的，就需要使用转换函数做显式转换。44USEAdventureWorks;GOSE
23、LECTThelistpriceis+CAST(ListPriceASvarchar(12)ASListPriceFROMProduction.ProductWHEREListPriceBETWEEN350.00AND400.00;GO例例例例2.92.9，使用，使用，使用，使用CASTCAST连接非字符型非二进制表达式连接非字符型非二进制表达式连接非字符型非二进制表达式连接非字符型非二进制表达式 48系统函数系统函数用于返回系统、用户、数据库的信息。用户得到信息后，用于返回系统、用户、数据库的信息。用户得到信息后，可以使用语句进行相关的操作。可以使用语句进行相关的操作。49系统函数系统函数5
24、0聚合函数聚合函数针对整个或者一个表列或几个表列进行数据汇总，常用针对整个或者一个表列或几个表列进行数据汇总，常用来计算来计算SELECT语句查询结果集的统计值。如求一个结果语句查询结果集的统计值。如求一个结果集的最大值、最小值、平均值和所有元素和等。集的最大值、最小值、平均值和所有元素和等。聚合函数聚合函数AVG、COUNT、MIN、MAX和和SUM是针对是针对表列进行运算，并回单一的值，其中表列进行运算，并回单一的值，其中COUNT、MIN和和MAX可用于各种数据类型，但可用于各种数据类型，但AVG及及SUM则仅适用于数则仅适用于数值类型值类型。51程序程序是由批、注释、变量、改变执行顺序
25、的控制语句、是由批、注释、变量、改变执行顺序的控制语句、错误和消息的处理等成分组成的。错误和消息的处理等成分组成的。批（处理）批（处理）批是作为一个逻辑单元的一组批是作为一个逻辑单元的一组T-SQL语句。语句。一个批中的所有语句被组合成一个执行计划。因此一个批中的所有语句被组合成一个执行计划。因此对所有语句一起进行语法分析且通过语法验证，否则对所有语句一起进行语法分析且通过语法验证，否则将不执行任何一条语句。将不执行任何一条语句。但如果运行时发生错误，那么任何发生在运行时错但如果运行时发生错误，那么任何发生在运行时错误之前的语句仍然有效。除非是未提交事务的一部分。误之前的语句仍然有效。除非是未
26、提交事务的一部分。批处理和流程控制批处理和流程控制52qGO：向：向SQLServer实用工具发出一批实用工具发出一批Transact-SQL语句结束的信号。语句结束的信号。q两个两个GO之间的之间的T-SQL语句作为一个批。在一个语句作为一个批。在一个批中可包含一条或多条批中可包含一条或多条T-SQL语句，成为一个语句语句，成为一个语句组。这样的语句组从应用程序一次性地发送到组。这样的语句组从应用程序一次性地发送到SQLServer服务器进行执行。服务器进行执行。q使用批的限制（使用批的限制（P35）q脚本脚本：一系列顺序提交的批。：一系列顺序提交的批。批批53/*例例2.10由三个批处理组
27、成的脚本。由三个批处理组成的脚本。*/USEstudentGOCREATEVIEWteacher_info_viewASSELECTteacher_id,name,tech_title,salaryFROMteacher_infoGOSELECT*FROMteacher_info_viewGO脚本脚本54551. BEGINEND语句语句语法格式：语法格式：BEGINENDl用来用来设定一个程序块设定一个程序块，将在，将在BEGINEND内的所内的所有程序有程序视为一个单元执行视为一个单元执行。l经常在条件语句如经常在条件语句如IFELSE中使用，在中使用，在BEGINEND中可嵌套另外的中可
28、嵌套另外的BEGINEND来定义另一程来定义另一程序块。序块。57流程控制流程控制(示例示例2.11 )582. IFELSE语句语句l语法形式语法形式：IFBoolean_expressionsql_statement|statement_blockELSEsql_statement|statement_blocklELSE子句是可选的。子句是可选的。l允许嵌套使用允许嵌套使用IFELSE语句，而且嵌套层数语句，而且嵌套层数没有限制。没有限制。59流程控制流程控制(示例示例2.12 )603. CASE语句语句l作用作用：计算多个条件式，并将其中一个符合条件：计算多个条件式，并将其中一个符合
29、条件的结果表达式返回。的结果表达式返回。l两种使用形式两种使用形式l简单式简单式CASE结构结构：将某个表达式与一组简单：将某个表达式与一组简单表达式进行比较以确定结果。表达式进行比较以确定结果。l搜索式搜索式CASE结构结构：计算一组布尔表达式以确计算一组布尔表达式以确定结果。定结果。l共同点共同点：两种格式都支持可选的：两种格式都支持可选的ELSE参数。参数。61流程控制流程控制(示例示例2.13 )62流程控制流程控制(示例示例2.14 )l例例2.14从从stud_grade表中查询所有同学考试成绩情况，凡表中查询所有同学考试成绩情况，凡成绩为空者输出成绩为空者输出“未考未考”、小于、
30、小于60分输出分输出“不及格不及格”、60分至分至70分输出分输出“及格及格”、70分至分至90分输出分输出“良好良好”、大于、大于或等于或等于90分时输出分时输出“优秀优秀”。SELECTstud_id,name,score=CASEWHENgradeISNULLTHEN未考未考WHENgrade=60ANDgrade=70ANDgrade=90THEN优秀优秀ENDFROMstud_grade634. WHILECONTINUEBREAK语句语句WHILEBoolean_expressionsql_statement|statement_blockBREAKsql_statement|st
31、atement_blockCONTINUEsql_statement|statement_blockl在设定的条件成立时会重复执行命令行或程序块。在设定的条件成立时会重复执行命令行或程序块。lCONTINUE可让程序可让程序跳过本次循环跳过本次循环，其后的语句回到，其后的语句回到下下次次WHILE循环的第一行命令循环的第一行命令。lBREAK命令则让程序命令则让程序完全跳出循环，结束执行完全跳出循环，结束执行WHILE语句语句。64declarexint,yint,numsintselectx=0,y=1setnums=0whiley100beginif(y%3=0)beginsetx=x+y
32、;setnums=nums+1endsety=y+1endprintstr(x)+,+str(nums)例例2.15 计算计算100内能被内能被3整除的数的个数及总和整除的数的个数及总和65qWAITFOR语句用来暂时停止程序执行语句用来暂时停止程序执行qGOTO语句用来改变程序执行的流程语句用来改变程序执行的流程qRETURN语句用于结束当前程序语句用于结束当前程序(所在的批、存所在的批、存储过程和触发器储过程和触发器)的执行，返回到上一个调用它的的执行，返回到上一个调用它的程序。程序。qPRINT语句的作用是在屏幕上显示用户信息。语句的作用是在屏幕上显示用户信息。qRAISERROR语句的
33、作用是将错误信息显示在语句的作用是将错误信息显示在屏幕上，同时也可以记录在日志中。屏幕上，同时也可以记录在日志中。 流程控制流程控制665. WAITFOR语句语句l用于设置程序段的执行时间，包括指定程序段在用于设置程序段的执行时间，包括指定程序段在某一时刻执行或者在某一段时间间隔后自动执行。某一时刻执行或者在某一段时间间隔后自动执行。l格式格式：WAITFORDELAYtime|TIMEtimel其中，其中，DELAY用于指定时间间隔，用于指定时间间隔，TIME用于指用于指定某一时刻，格式为定某一时刻，格式为hh:mm:ss。l例例2.16使用使用WAITFORTIME语句，以便在晚上语句，
34、以便在晚上10:20执行存储过程执行存储过程update_all_stats。BEGINWAITFORTIME22:20EXECUTEupdate_all_statsEND676. GOTO语句语句l使程序直接跳到指定的标签处继续执行，使程序直接跳到指定的标签处继续执行，而位于而位于GOTO语句和标签之间的程序将不语句和标签之间的程序将不会被执行。会被执行。l标签必须符合标签必须符合标识符标识符规则规则，但必须以，但必须以“：”结尾。结尾。GOTOlabellabel:68697. RETURN语句语句l用于无条件地终止一个查询、存储过程或用于无条件地终止一个查询、存储过程或者批处理，此时位于
35、者批处理，此时位于RETURN语句之后的语句之后的程序将不会被执行。程序将不会被执行。l格式格式：RETURNinteger_expressionl其中，参数其中，参数integer_expression为返回的整为返回的整型值。存储过程可以给调用过程或应用程型值。存储过程可以给调用过程或应用程序返回整型值。序返回整型值。708. RAISEERROR语句语句l用于将错误消息显示在屏幕上，同时也可记录在用于将错误消息显示在屏幕上，同时也可记录在日志中。日志中。l格式格式：RAISERROR(msg_id|msg_str|local_variable,severity,state,argumen
36、t,.n)WITHoption,.nl注：任何用户都可指定注：任何用户都可指定018之间的严重级别。之间的严重级别。只有只有sysadmin固定服务器角色成员或具有固定服务器角色成员或具有ALTERTRACE权限的用户才能指定权限的用户才能指定1925之之间的严重级别。若要使用间的严重级别。若要使用19到到25之间的严重级之间的严重级别，必须选择别，必须选择WITHLOG选项选项71q本章介绍了包括数据类型、注释、变量、常量、本章介绍了包括数据类型、注释、变量、常量、运算符、系统函数、批处理、流程控制等内容。运算符、系统函数、批处理、流程控制等内容。q使用使用T-SQL语句进行程序设计时，通常用一组语句进行程序设计时，通常用一组T-SQL语句组成一个批处理，并由一个或多个批处理语句组成一个批处理，并由一个或多个批处理构成一个程序文件。批处理是一组构成一个程序文件。批处理是一组T-SQL语句集合，语句集合，一个批处理以批处理结束语句（一个批处理以批处理结束语句（GO语句）而结束。语句）而结束。q流程控制语句，包括流程控制语句，包括IF、WHILE、CASE、GOTO、BREAK、CONTINUE和和RETURN语句等。语句等。本章小结本章小结72
