《数据库技术与应用》课件-项目5：Transact-SQL编程

Transact-SQL语言基础知识的基本认知课程简介2通过本课程的学习，认知Transact-SQL语言基础知识。

主要学习内容：一．T-SQL语言的简介二．标识符三．数据类型四．运算符和表达式五．常量六．变量七．注释八．续行九．数据库对象命名T-SQL语言的简介3Transact-SQL（又称T-SQL）语言是操作SQLServer的核心，在SQLServer中，与SQLServer实例通信的所有应用程序都通过T-SQL语句发送到服务器，实现数据的检索、操纵和控制等功能。T-SQL语言的简介4任何应用程序，不管它是用什么形式的高级语言编写的，只要目的是向SQLServer数据库管理系统发出命令以获得其响应，最终都必须体现为以T-SQL语句为表现形式的指令。因此T-SQL是SQLServer与应用程序之间的语言，是SQLServer的应用程序开发接口。T-SQL语言的简介5T—SQL语言分成五大类，各类语言及作用如下：（1）数据定义语言DDL：用来创建数据库和数据库对象的命令，绝大部分以CREATE开头，如CREATETABLE等。（2）数据操作语言DML：用来操作数据库中各种对象，对数据进行修改和检索。DML语言主要有四种：SELECT（查询）、INSERT（插入）、UPDATE（更新）和DELETE（删除）。T-SQL语言的简介6（3）数据控制语言DCL：用来控制数据库组件的存取许可、权限等命令，如GRANT、REVOKE。（4）事务管理语言TML：用于管理数据库中的事务的命令，包括COMMIT、ROLLBACK。（5）其他语言元素：如标识符、数据类型、流程控制和函数等。T-SQL语言的简介7

每一条T-SQL语句都包含一系列元素，比如标识符、数据类型、运算符、表达式、函数和注释等，下面对这些元素的具体情况进行说明。标识符8

标识符就是指用来定义服务器、数据库、数据库对象和变量等的名称。可分为常规标识符和分隔标识符。1.常规标识符：是严格遵守标识符有关规定的基本格式规则。标识符9

2.分隔标识符：使用了分隔符号（如用[]和””）来进行位置限定，如果不遵守标识符命名规则的标识符，在T-SQL语句中必须使用分隔符加以限定，否则无法进行代码编译。如这条查询语句“SELECT*INTO[MyTable]FROMCustomerWHERECustomerID=14”中MyTable作为数据表名是不符合标识符的格式规则，因为名称中间有一个空格，如果不进行分隔，SQLServer会把看成是两个标识符，从而出现错误，所以必须使用分隔符[MyTable]。数据类型10

数据类型是一种属性，在SQLServer数据库中保存和处理数据时需要区分不同类型的数据，常用的有binary[(n)]、int、decimal[(p[,s])]、varchar[(n)]、date、time、datetime2和bit等。运算符和表达式11表达式是标识符、值和运算符的组合，可以对其计算以获取结果。在SQLServer中有７类运算符：1.算术运算符：用于两个表达式执行数学运算，包括+（加）、-（减）、*（乘）、/（除）、%（取模）。2.赋值运算符：“＝”。3.位运算符：用于在两个操作数之间执行位操作。包括&（位与）、|（位或）、^（位异或），这两个操作数可以为整数数据类型类别中的任何数据类型。运算符和表达式124.比较运算符：用于比较两个表达式的值，包括：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）等，比较运算符结果有3个值：TRUE,FALSE,UNKNOWN(未知)。5.字符串串联运算符：“+”可将字符串串联起来，例如，'采购部主管:'+'张立'的结果就是'采购部主管:张立'。6.一元运算符：一元运算符只对一个表达式执行操作，包括+（正）、-（负）、~（位非）。运算符和表达式137.逻辑运算符：用于对某些条件表达式进行测试比较，返回带有TRUE或FALSE值。8.运算符优先级：当一个复杂的表达式有多个运算符时，运算符优先级决定运算符的先后顺序。在较低级别的运算符之前先对较高级别的运算符进行求值。运算符优先级别如下表所示：运算符和表达式14级别运算符1~（位非）+（正）-（负）2*（乘）、/（除）、%（取模）3+（正）、-（负）、+（加）、（+连接）、-（减）、&（位与）4=,>、<、>=、<=、<>、!=、!>、!<（比较运算符）5^（位异或）、|（位或）6NOT7AND8ALL、ANY、BETWEEN、IN、LIKE、OR、SOME9=（赋值）常量15常量格式取决于它所表示值的数据类型。根据常量值的不同类型，常量分为字符串常量、二进制常量、整型常量、实数常量、日期时间常量、货币常量和惟一标识常量等。变量16SQLServer提供两种变量：用户自定义的局部变量和系统提供的全局变量。1.局部变量用户自己定义的变量称为局部变量，用于保存特定类型的单个数据值的对象。其作用范围仅在程序内部，在程序中通常用来存储从表中查询到的数据或暂存程序执行过程中的数据。变量17

（1）声明局部变量：DECLARE@变量名1数据类型,@变量名2数据类型,...,@变量名n数据类型注意:局部变量名必须以@开头，先用DECLARE声明之后才能使用。用DECLARE声明之后，所有的变量都被赋予初值NULL。数据类型可以是系统提供的类型、用户定义数据类型。变量不能是varchar(MAX)或varbinary(MAX)等数据类型。变量18

（2）赋值局部变量用SET或SELECT语句为局部变量赋值，语法格式如下所示：SET@变量名1=表达式1,@变量名2=表达式2,...@变量名n=表达式nSELECT@局部变量名=表达式[,…n][FROM子句][WHERE子句]变量19

其中，使用SELECT语句给变量赋值时，如果省略了FROM子句和WHERE子句的话，就等同于SET语句赋值；如果有FROM子句和WHERE子句，类似查询表内容后赋值；如果SELECT语句返回多个值，则将返回的最后一个值赋给局部变量。变量20【例】定义两个变量并赋值。代码与运行结果，如图所示：代码中用DECLARE语句同时定义了2个局部变量，并分别用SET及SELECT语句为局部变量进行赋值。变量212.全局变量全局变量由系统定义和维护的变量，用于记录服务器活动状态的一组数据。全局变量名由@@符号开始。例如，@@version全局变量返回当前SQLServer的版本信息和处理器类型。用户不能建立全局变量,也不能用SET语句修改全局变量的值。在SQLServer中，全局变量以系统函数的形式在使用。变量22【例】打印出当前系统服务器的名称代码：PRINT'当前系统服务器的名称:'+@@SERVERNAME代码与运行结果，如图所示：。注释23

注释有两个作用：第一，对程序代码的功能进行简要说明，以便于程序代码维护；第二，可以把程序中暂不用的语句注释起来，使它们暂时不被执行。SQLServer中的注释一般有两种，分别是单行注释和块注释。注释241.单行注释放在T-SQL语句行后，使用“--”表示单行注释语句，如图所示：注释252.块注释语句将多行注释文本包含在符号“/*”和“*/”中间，就形成了块注释语句，如图所示：续行26T－SQL语句如果要写得很长，可以将一条语句在多行中进行编写，T－SQL会忽略掉空格和行尾的换行符号，方便并显著提高了T－SQL的可读性。、如图所示：，可以看到，分行与不分行书写的查询语句，运行结果是一致的。数据库对象命名27数据库对象命名一般由四部分组成的名称，格式如下：

服务器（server）名称．数据库（database）名称．架构（schema）名称.对象（object）名称当引用某个特定对象时，可以省略标识该对象的服务器、数据库和架构这些中间级节点名称，而使用句点表示这些位置。具体有效的对象名格式，如下表所示：数据库对象命名28序号对象引用格式说明1server.database.schema.object完整包含四个部分的全称。2server.database..object省略架构名称。3server..schema.object省略数据库名称。4server...object省略数据库和架构名称。5database.schema.object省略服务器名。6database..object省略服务器和架构名称。7schema.object省略服务器和数据库名称。8object省略服务器、数据库和架构名称。数据库对象命名29【例】一个用户登录到LISA的服务器，并使用Sales数据库，创建一个MyTable表，默认架构是dbo。

则表对象MyTable的全称如下：LISA.Sales.dbo.MyTable流程控制语句的使用课程简介31通过本课程，结合案例学习流程控制语句的使用。

主要学习内容：一．流程控制语句概述二．顺序语句结构三．选择结构语句四．循环结构语句五．其他控制语句流程控制语句概述32T-SQL语句中的流程控制，如顺序、分支、循环等，控制着语句的执行顺序。T-SQL提供的控制流程语句有：IF...ELSE分支、CASE多路分支、WHILE循环、GOTO、WAITFOR和RETURN等多种语句。顺序结构语句33顺序结构语句除了有数据操作类语句以外，与一般高级语言类似，常用的还有赋值、输入、设置等各种处理语句。顺序结构语句341.SET语句有两种用法：除了给局部变量赋值；如图所示，还可以设定用户执行T-SQL命令时SQLServer的处理选项，一般有以下几种设定方式：SET选项ON：选项打开。SET选项OFF：选项关闭。SET选项值：设定选项的具体值。顺序结构语句352.SELECT输出语句SELECT作为输出使用时，其语法如下所示：SELECT表达式1,表达式2,...表达式n顺序结构语句36【例】输出SQLServer版本信息（@@VERSION）。代码与运行结果，如图所示：可以看出，SELECT作为输出使用时，其输出结果也是放在单元格内。顺序结构语句373.PRINT输出语句用于向客户端返回用户定义的消息，PRINT语句允许显示常量、表达式或者变量，其语法如下所示：PRINT表达式顺序结构语句384.BEGIN…END语句可将多个T-SQL语句组合为一个逻辑块，相当于一次执行一组T-SQL语句。它的语法格式如下所示，SQLServer2008允许BEGIN…END语句嵌套使用。BEGIN语句1语句2…END选择结构语句391.IF...ELSE语句实现程序选择结构。它的语法格式如下：IF条件表达式{语句块1}[ELSE{语句块2}]选择结构语句40功能：IF...ELSE用来判断当条件表达式成立时执行语句块1，条件不成立时执行语句块2。ELSE子句是可选的，最简单的IF语句可以没有ELSE子句。SQLServer允许嵌套使用IF…ELSE语句,但是一般嵌套最好不要超过3层，否则会降低程序的可读性。选择结构语句41【例】用IF...ELSE语句查询图书中有没有计算机书，如果图书中有“计算机”，打印存在“计算机”名称图书，否则打印不存在“计算机”名称图书。代码与运行结果，如图所示：选择结构语句422.CASE语句CASE表达式提供了比IF...ELSE结构更多选择和判断的机会。使用CASE表达式可以很方便地实现多种选择情况，从而避免编写多重IF...ELSE嵌套。CASE表达式具有两种格式：选择结构语句43（1）简单CASE语句语法格式如下所示：CASE<输入表达式>WHEN<表达式1>THEN结果表达式1WHEN<表达式2>THEN结果表达式2...ELSE结果表达式nEND选择结构语句44功能：首先计算“输入表达式”值，然后将其值依次与“when表达式”的值进行比较，当“输入表达式”的值等于“WHEN表达式”的值时，返回满足条件THEN后的“结果表达式”的值，然后跳出CASE语句，不再执行后面的语句。如果比较运算结果都不为真，则返回ELSE后的表达式的值。如果省略ELSE子句并且比较运算的计算结果都不为真，表达式的值为NULL。选择结构语句45说明：参数“输入表达式”的值和每个WHEN“表达式”的值的数据类型必须相同，或者必须进行隐式转换。选择结构语句46【例】查询所有用户的人员类别。代码与运行结果,如图所示：选择结构语句47（2）搜索类型的CASE语句语法格式如下所示：

CASE

WHEN逻辑表达式1THEN结果表达式1WHEN逻辑表达式2THEN

结果表达式2[…n][ELSE其他结果表达式]END选择结构语句48功能：用CASE搜索一组逻辑表达式的值，值为真时则将对应THEN后的结果表达式值作为最终结果。【例】显示各用户账号是否可用。代码与运行结果,如图所示：循环结构语句491.WHILE、CONTINUE和BREAK语句WHILE语句实现判断逻辑表达式结果为真后，就重复执行T-SQL语句或语句块，直到表达式值为假。WHILE语句还可以与BREAK、CONTINUE语句一起使用。循环结构语句50其语法如下：WHILE逻辑表达式BEGIN语句块1[CONTINUE][BREAK]语句块2END其中，(1)BREAK:无条件地退出WHILE循环(2)CONTINUE：结束本次循环，进入下次循环，忽略CONTINUE后面的任何语句。循环结构语句51【例】求1~100之间的奇数的和。代码与运行结果,如图所示：其他控制语句521.GOTO语句执行流程无条件跳转到标签处，并从标签位置处继续执行。GOTO语句和标签可在过程、批处理或语句块中的任何位置使用，其语法如下所示：GOTO标签名语句组１标签名：语句组２其他控制语句53【例】利用GOTO语句，求5!结果代码与运行结果，如图所示：其他控制语句542.WAITFOR语句 实现语句延缓一段时间或延迟到某特定的时间执行,其语法格式如下所示：WAITFORDELAY<延时时间>/*设定延迟时间*/WAITFORTIME<到达时间>/*设定等待到某一时刻*/(1)DELAY：用来设定等待的时间，最多可达24小时。(2)TIME：用来设定等待结束的时间点。(3)时间：为TIME类型的数据。其他控制语句55【例1】延迟5s查询图书表数据。代码与运行结果，如图所示：其他控制语句56【例2】在指定时刻查询图书表数据。代码与运行结果，如图所示：其他控制语句573.RETURN语句其语法如下：RETURN[整型表达式]功能：RETURN语句用于无条件结束当前程序的执行，并返回到调用它的程序，可指定一个返回值。RETURN的执行是即时且完全的，RETURN之后的语句不执行。其他控制语句58【例】返回@x和@y中较大的值代码与运行结果，如图所示:函数的使用课程简介60通过本课程，结合实例学习函数的使用。

主要学习内容：一．系统定义函数二．用户定义函数系统定义函数61系统定义函数中，聚合函数、数学函数、字符串函数、日期时间函数、系统函数、系统统计函数是最常用的。1.聚合函数（列函数）聚合函数只能在SELECT查询语句某些子句中作为表达式使用，包括常用COUNT、SUM、AVG、MAX和MIN等，用于对一组数据执行某种计算并返回一个结果。系统定义函数62常用聚合函数与功能如下表所示：函数名功能AVG返回一组值的平均值。COUNT返回一组值中项目的数量。（返回值为int类型）。COUNT_BIG返回一组值中项目的数量。返回值为bigint类型）。MAX返回表达式或者项目中的最大值。MIN返回表达式或者项目中的最小值。SUM返回表达式中所有项的和，或者只返回DISTINCT值。SUM只能用于数字列。STDEV返回表达式中所有值的统计标准偏差。STDEVP返回表达式中所有值的统计标准偏差。VAR返回表达式中所有值的统计标准方差。系统定义函数63【例】使用COUNT（）函数统计用户人数。代码与运行结果，如图所示：系统定义函数642.数学函数数学函数对数字表达式进行运算并返回运算结果。数学函数包含三角函数、反三角函数、角度弧度转换函数、幂函数、边界函数、符号函数、随机函数、PI函数。系统定义函数65【例】产生3个在10以内的随机整数。代码与运行结果，如图所示：其中，RAND()函数返回float类型的随机数，该数的值在0~1之间。CEILING()函数返回大于或等于指定数值表达式的最小整数。系统定义函数663.字符串函数字符串函数用于对字符串进行ASCII码转换、取子串、求长度和截首尾空格取等操作。常用字符串函数与功能如下表所示：系统定义函数67函数功能ASCII(字符表达式)返回字符表达式中最左侧字符的ASCII码值CHAR（整型表达式）将整型的ASCII码转换为字符LEFT(字符表达式,整数表达式)返回字符串中从左边开始的指定个数的字符RIGHT(字符表达式,整数表达式)返回字符串从右边开始的指定个数的字符SUBSTRING(字符表达式,起始点,n)返回字符表达式中从“起始点”开始的n个字符CHARINDEX(字符表达式1,字符表达式2,[开始位置])求子串位置LTRIM(字符表达式)返回删除前导空格字符后的字符表达式RTRIM(字符表达式)截断所有尾部空格后返回一个字符串REPLICATE(字符表达式,n)重复字串REVERSE(字符表达式)倒置字串STR（浮点表达式[，长度[，小数]]）返回由数字数据转换来的字符数据LOWER（字符表达式）将字符表达式中的大写字母转换为小写字母UPPER（字符表达式）将字符表达式中的小写字符转换为大写字符LEN（字符表达式）返回某个指定字符串的长度，不计字符串后的空格系统定义函数68【例】使用ASCII()函数展示字符串“String”最左侧字符的ASCII码值。代码与运行结果，如图所示：系统定义函数694.日期时间函数对日期和时间输入值执行操作，返回一个字符串、数字或日期和时间值。常用的日期和时间函数与功能如下表所示：系统定义函数70函数功能DATEADD(datepart,数值,日期)返回增加一个时间间隔后的日期结果DATEDIFF(datepart,日期1,日期2)返回两个日期之间的时间间隔，格式为datepart参数指定的格式DATENAME(datepart,日期)返回日期的文本表示，格式为datepart指定格式DATEPART(datepart,日期)返回某日期的datepart代表的整数值GETDATE（）返回当前系统日期和时间DAY（日期）返回某日期的日datepart所代表的整数值MONTH（日期）返回某日期的月datepart所代表的整数值YEAR（日期）返回某日期的年datepart所代表的整数值系统定义函数71【例】使用DATEADD（）函数展示2022年12月31日后35天是哪一天。代码与运行结果，如图所示：系统定义函数725.数据类型转换函数数据类型转换函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST（表达式AS数据类型）函数：将表达式的类型转换为指定的数据类型。CONVERT（数据类型（长度），表达式）函数：将表达式的类型转换为指定的数据类型。系统定义函数73【例】展示CONVERT（）等函数今年的具体年份。代码与运行结果，如图所示：系统定义函数746.元数据函数元数据函数返回有关数据库和数据库对象的信息，所以元数据函数都具有不确定性。常用元数据函数与功能如下表所示：函数功能COL_LENGTH(表名,列名)返回列的定义长度（以字节为单位）。COL_NAME(表标识号,列标识号)根据指定的对应表标识号和列标识号返回列的名称。DB_ID([数据库名称])返回数据库标识(ID)号。DB_NAME([数据库的标识号])返回数据库名称。系统定义函数75【例】使用DB_NAME（）和DB_ID（）函数显示当前数据库的名称和标识符。代码与运行结果，如图所示：系统定义函数767.其他内置函数(1)配置函数：实现返回当前配置选项设置的信息的功能。【例】使用@@VERSION函数显示当前安装的SQLServer版本信息代码与运行结果，如图所示：系统定义函数77(2)系统统计函数：通常以全局变量的形式来表示。【例】使用@@CUP_BUSY函数显示SQLServer自上次启动后的工作时间。代码与运行结果，如图所示：系统定义函数78(3)系统函数：返回SQLServer中值、对象、设置的有关信息。【例】使用ISNULL函数完成提示表Users1的存在与否。代码与运行结果，如图所示：ISNULL（空值，指定的值）是空值置换函数，可以用“指定的值”代替“空值”。用户定义函数79在SQLServer中，内置的系统定义函数为完成一些基本应用提供了极大的方便，但在具体的数据库应用中，经常需要将业务逻辑中的多个T-SQL语句定义成函数，从而实现代码的封装和重用。用户定义函数则提供了比存储过程功能更强的封装机制，并具有三个优点：允许模块化程序设计、执行速度快、减少网络流量。用户定义函数80用户定义函数支持用户定义标量函数和表值函数:标量函数:返回的是在RETURNS子句中定义类型的单个数据值。返回类型可以是除大值数据类型varchar(max)、nvarchar(max)和varbinary(max)和cursor等之外的数据类型。表值函数:返回的是在RETURNS子句中指定的“TABLE”类型定义的数据行集（表）。用户定义函数81创建用户定义函数的语法结构如下所示：CREATEFUNCTION<函数名>[(@形参名数据类型[,...n])]RETURNS返回值数据类型[WITH选项][AS]BEGIN T-SQL语句 RETURN返回表达式END编程应用小实例课程简介83通过本课程学习编程应用小实例，掌握相关编程应用。

主要学习内容：一．内置函数及控制语句的使用实例二．创建用户定义标量函数实例三．创建用户定义内联表值函数实例内置函数及控制语句的使用实例84【例】使用内置的系统函数查询的主机名称、主机标示、BookManageDB数据库的标识号、Users表的标识号和当前用户名称等信息，并生成报表；同时利用流程控制语句，在当前服务器中创建新数据库Student。内置函数及控制语句的使用实例85代码与运行