第5讲 T-SQL编程基础.ppt

《第5讲 T-SQL编程基础.ppt》由会员分享，可在线阅读，更多相关《第5讲 T-SQL编程基础.ppt（37页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第 5 讲 T-SQL 编程基础哈尔哈尔滨师范大范大学学恒星恒星学学院院本本讲主要主要内内容容T-SQL 程序语言概况程序语言概况T-SQL 程序语言基础程序语言基础 运算符与函数运算符与函数常用工具简介常用工具简介 123 Transact-SQL语言语言(简称简称 T-SQL 语言语言),是是由由 Microsoft 公司开发的一种公司开发的一种 SQL 语言语言,它是它是标准标准 SQL 程序设计语言的增强版程序设计语言的增强版,使得应用程使得应用程序与序与 SQL Server 数据库引擎沟通数据库引擎沟通.T-SQL 提供了标准提供了标准 SQL 的的 DDL、DCL 和和DML功能

2、功能,加上加上扩展的函数扩展的函数、系统存储过程以及系统存储过程以及程序设计结构程序设计结构(例如例如 IF 和和 WHILE)让程序设计让程序设计更有灵活性更有灵活性,还可以实现对数据库管理的功能还可以实现对数据库管理的功能.T-SQL 语言是语言是 SQL Server 的核心的核心.一一.T-SQL T-SQL 程序语言概况程序语言概况 随着随着 Microsoft SQL Server 版本的演进，版本的演进，衍生自衍生自 ANSI SQL 的的 T-SQL语言变得独立且功语言变得独立且功能强大，拥有众多用户，是解决各种数据问题能强大，拥有众多用户，是解决各种数据问题的主流语言的主流语

3、言.在在 SQL Server 的功能大量扩增并的功能大量扩增并广泛进入企业各系统后，日益庞大的数据量让广泛进入企业各系统后，日益庞大的数据量让 T-SQL 发挥了重要的作用发挥了重要的作用.T-SQL 语言主要包括三种类型的语句语言主要包括三种类型的语句:数据定义语言数据定义语言(Date Definition Language,DDL),数据控制语言数据控制语言(Date Control Language,DCL),数据操纵语言数据操纵语言(Date Manipulation Language,DML),简要介绍如下简要介绍如下.数据定义语言数据定义语言(DDL),除了可以用来建立数据库除

4、了可以用来建立数据库与数据库相关对象之外与数据库相关对象之外,还可以修改及删除数据库还可以修改及删除数据库对象对象.例如例如,数据库数据库、表及视图等对象表及视图等对象.T-SQL程序语言的分类程序语言的分类数据定义语言数据定义语言 说说 明明CREATE建立数据库对象建立数据库对象ALTER修改数据库对象修改数据库对象DROP将该数据库对象删除将该数据库对象删除表表5-1 数据定义语言数据定义语言(DDL)USE pubsCREATE TABLE myOrders (OrderID int,OrderDate smalldatetime,CustomerID Char(10),ShipDdd

5、ress varchar(100)数据控制语言数据控制语言(DCL)用来设置用户对数据库用来设置用户对数据库对象的访问权限对象的访问权限,如下表如下表.数据控制语言数据控制语言说说 明明GRANT赋予某个用户具有数据库对象的权限赋予某个用户具有数据库对象的权限DENY禁止使用该数据库对象禁止使用该数据库对象,且不能继承且不能继承REVOKE收回之前设置的权限收回之前设置的权限表表5-2 数据控制语言数据控制语言(DCL)GRANT SELECT ON myOrders TO Jack 数据操纵语言数据操纵语言(DML)用来访问表中的数据用来访问表中的数据,下表下表5-3 列出了每个语句的使用方

6、式列出了每个语句的使用方式.数据操纵语言数据操纵语言说说 明明SELECT从表或视图中选择行从表或视图中选择行INSERT将行插入到表或视图中将行插入到表或视图中UPDATE在表或视图中更新行在表或视图中更新行DELETE在表或视图中删除行在表或视图中删除行表表5-3 数据操纵语言数据操纵语言(DML)SELECT *FROM myOrders T-SQL语言的目的在于为处理大量数据提语言的目的在于为处理大量数据提供必要的结构化处理能力，因此，它并没有提供必要的结构化处理能力，因此，它并没有提供供 VB 和和 C 所具有的某些语言特性所具有的某些语言特性.其它功能其它功能仍需要前端设计工具仍需

7、要前端设计工具(如如FoxPro、Delphi、Java 和和 VB 等等)来处理来处理.所以，所以，一般称一般称 SQL Server、Oracle 和和 mySQL 等数据库等数据库管理系统为管理系统为“数据库引擎数据库引擎”.二二.T-SQL 程序语言基础程序语言基础 批处理是由一条或多条批处理是由一条或多条 T-SQL 语句构成语句构成,应用程序将它作为单个代码单元发送给系统应用程序将它作为单个代码单元发送给系统.SQL 脚本文件或者脚本文件或者查询编辑器查询编辑器的窗口都的窗口都可以包含多个批处理可以包含多个批处理.在这种情况下，需要使用批处理分隔符关在这种情况下，需要使用批处理分隔

8、符关键字键字 GO 来结束每一个批处理来结束每一个批处理.使用使用 GO 来结束批处理时，只能把它自己来结束批处理时，只能把它自己放在单独的一行上放在单独的一行上.结束一个批处理时，将会释放所有由该批结束一个批处理时，将会释放所有由该批处理所创建的局部变量、临时表、游标处理所创建的局部变量、临时表、游标.1.T-SQL批处理批处理 -以下代码会列出该服务器上所有的数据库名称以下代码会列出该服务器上所有的数据库名称SELECT name FROM sys.databases实战练习实战练习 -查询登录的用户账号查询登录的用户账号,使用的数据库与当前的数据库服务使用的数据库与当前的数据库服务器的系

9、统日期和时间器的系统日期和时间SELECT SUSER_SNAME(),DB_NAME(),GETDATE()在在 SSMS 的工具条上会标明当前的数据的工具条上会标明当前的数据库，你也可以使用它来改变当前的数据库库，你也可以使用它来改变当前的数据库.在代码中，则可以使用在代码中，则可以使用 USE 命令来选定命令来选定当前的数据库当前的数据库.使用使用 USE 命令来显式地选定正确的数据命令来显式地选定正确的数据库是一种好的开发方法，不要把选定正确的数库是一种好的开发方法，不要把选定正确的数据库交给用户去做据库交给用户去做.切换数据库切换数据库 有些特殊的有些特殊的 T-SQL 命令必须单独

10、地放在命令必须单独地放在它自己的批处理之中，或者说必须将它们与其它自己的批处理之中，或者说必须将它们与其他的批处理命令分别放到不同的批处理中他的批处理命令分别放到不同的批处理中.数据定义语言命令（数据定义语言命令（CREATE、ALTER和和 DELETE）就是这样）就是这样 T-SQL 命令，命令，应当将应当将它们放到他们自己的批处理中去，这是一项要它们放到他们自己的批处理中去，这是一项要牢记的规则牢记的规则.DDL命令命令 可以在查询编辑器中打开一个可以在查询编辑器中打开一个.sql 脚本脚本文件，按下文件，按下 F5 键或者选择工具栏中的键或者选择工具栏中的 !执行执行项来执行一个项来执

11、行一个 SQL 脚本程序中的全部批处理脚本程序中的全部批处理.在查询编辑器中可以通过在查询编辑器中可以通过突出显示突出显示来选择来选择一些命令，然后执行这些选定的一些命令，然后执行这些选定的 T-SQL 命令命令.在应用程序中在应用程序中,可以通过使用可以通过使用 ADO 或者或者ODBC 来提交并执行来提交并执行 T-SQL 批处理批处理.执行批处理执行批处理 对于对于 T-SQL 代码应代码应格式化格式化，以方便阅读，以方便阅读.T-SQL 命令往往都会写得较长命令往往都会写得较长.但但T-SQL会忽略空格和行尾的换行符，即不需要使用特会忽略空格和行尾的换行符，即不需要使用特殊的续行符号来

12、编写长达数行的殊的续行符号来编写长达数行的 T-SQL 命令命令,从而显著地提高了从而显著地提高了 T-SQL 代码的可读性代码的可读性.注释符号注释符号(略略).2.2.格式化格式化 T-SQL 代码代码 在完成程序代码的编写后，可以单击在完成程序代码的编写后，可以单击”文件文件”菜单中的菜单中的”保存保存”或或”另存为另存为”选项选项,将程序将程序代码存为代码存为*.sql 文件文件.该文件是文本格式该文件是文本格式,故可故可以以使用任何文本编辑器使用任何文本编辑器(如如 NotePad,UltraEdit)来来进行编辑进行编辑.若先前已经打开多个若先前已经打开多个”查询编辑器查询编辑器”

13、窗口窗口,并并编写了多个文件编写了多个文件,则可以使用则可以使用”保存全部查询保存全部查询”的方式来保存所有的程序代码的方式来保存所有的程序代码.3.3.保存程序代码保存程序代码 在在 T-SQL 中可以使用两种变量，中可以使用两种变量，一种是局一种是局部变量（部变量（Local Variable），另外一种是全局变），另外一种是全局变量（量（Global Variable）.局部变量局部变量 局部变量是用户可自定义的变量，它的作用局部变量是用户可自定义的变量，它的作用范围仅在程序内部范围仅在程序内部.局部变量必须以局部变量必须以 开头，而且必须先作说开头，而且必须先作说明：明：DECLARE

14、 变量名变量名 变量类型变量类型4.4.变量变量 在在 T-SQL 中对变量赋值的语法如下：中对变量赋值的语法如下：SELECT 局部变量局部变量=变量值变量值 SET 局部变量局部变量=变量值变量值 两种赋值命令的主要两种赋值命令的主要区别区别在于：在于：SELECT 可以从表、子查询或者视图中检可以从表、子查询或者视图中检索数据，也可以包含其它的索数据，也可以包含其它的 SELECT 子句子句,并且并且一次可为多个变量赋值一次可为多个变量赋值.SET 只能从表达式中获取数据只能从表达式中获取数据.两个命令中都可以使用函数两个命令中都可以使用函数.变量的赋值变量的赋值 问题问题1 考虑下面的

15、程序会有什么结果：考虑下面的程序会有什么结果：DECLARE strName CHAR(10)SELECT strName=SmallFish PRINT strName GO 问题问题2 考虑下面程序运行的结果：考虑下面程序运行的结果：DECLARE Test INT,TestTwo NVARCHAR(25)PRINT Test PRINT TestTwo SET Test=1 SET TestTwo=a value GO PRINT Test -问题问题3 考虑下面程序运行的结果：考虑下面程序运行的结果：USE Northwind DECLARE TempID INT,TempLastNa

16、me VARCHAR(25)SET TempID=99 SELECT TempID=EmployeeID,TempLastName=LName FROM dbo.employee SELECT TempID,TempLastName 全局变量是可以调用的系统变量，全局变量是可以调用的系统变量，它们是只读的，不它们是只读的，不能被创建能被创建.它们的作用是提供系统的信息它们的作用是提供系统的信息.全局变量以全局变量以 开头，最常用的有：开头，最常用的有：Error-最近一次执行的最近一次执行的 T-SQL 语句的错误编号语句的错误编号 Identity-为当前连接所生成的最后一个标识值为当前连接

17、所生成的最后一个标识值 NestLevel-当前存储过程的最大嵌套层次数当前存储过程的最大嵌套层次数 ServerName-当前服务器的名字当前服务器的名字 spid-当前用户进程的会话当前用户进程的会话id cpu_busy-SQL Server 自上次启动后的工作时间自上次启动后的工作时间全局变量全局变量 许多人对许多人对 T-SQL 的初步印象是它的流程控的初步印象是它的流程控制命令较少制命令较少.实际上，虽然它的流程控制命令没实际上，虽然它的流程控制命令没有其它语言那样丰富，但已经足够了有其它语言那样丰富，但已经足够了.它为数据它为数据处理命令提供的布尔扩展处理命令提供的布尔扩展-如如

18、EXISTS、IN 和和CASE-补充了补充了 IF 和和 WHILE 语句的不足语句的不足.IF 这个语句的特别之处在于：利用它只能对其这个语句的特别之处在于：利用它只能对其后的一条语句的执行与否进行控制后的一条语句的执行与否进行控制.同时，它也同时，它也没有没有 THEN，以及，以及 ENDIF 等命令等命令.5 5 流程控制流程控制 例如例如 考虑下面程序运行的结果：考虑下面程序运行的结果：IF 1=0 PRINT Line One PRINT Line Two 在这个脚本中，在这个脚本中，IF 条件应该返回一个假值条件应该返回一个假值,从而不会执行从而不会执行 IF 下面的第一条下面的

19、第一条 PRINT 命令命令.一个一个 IF 命令只能控制一条语句的执行与否命令只能控制一条语句的执行与否,这显然缺乏实用性这显然缺乏实用性.要解决这个问题，要解决这个问题，可以使可以使用用 BEGIN/END 块，块，它可将多条命令作为一个它可将多条命令作为一个整体整体.IF condition BEGIN multiple lines ENDIFELSE 语句语句 IF EXISTS()结构根据结构根据 SELECT 命令返回命令返回的结果集是否包含有行来进行判断的结果集是否包含有行来进行判断.只要查询只要查询返回了一条记录，就可以停止执行查询，转而返回了一条记录，就可以停止执行查询，转而

20、执行批处理中其他的语句，所以这种方法的速执行批处理中其他的语句，所以这种方法的速度更快度更快.USE 商品库存商品库存 IF EXISTS(SELECT *FROM shangpin WHERE 产品数量产品数量=0)BEGIN PRINT 已有产品缺货已有产品缺货 END WHILECONTINUEBREAK WHILE 命令在设定的条件成立时，会重复命令在设定的条件成立时，会重复执行命令行或程序块执行命令行或程序块.其语法如下：其语法如下：WHILE BEGIN BREAK CONTIUNE 命令行命令行|程序块程序块 END其它控制命令参见教科书其它控制命令参见教科书.WHILE 语句语

21、句 CASE 分支语句分支语句 RETURN 终止语句终止语句 WAITFOR 暂停语句暂停语句 PRINT 输出语句输出语句 其语法格式和用法参见教科书其语法格式和用法参见教科书.其它流程控制语句其它流程控制语句 Microsoft SQL Server 2008提供了提供了 7 种类种类型的运算符，分别是算术运算符、赋值运算符、型的运算符，分别是算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串位运算符、比较运算符、逻辑运算符、字符串运算符和一元运算符运算符和一元运算符.SQL Server 提供了许多系统内置函数，同提供了许多系统内置函数，同时也允许用户根据需要自己定义函数

22、时也允许用户根据需要自己定义函数.SQL Serve 提供的常用的内置函数主要有提供的常用的内置函数主要有以下几类：数学函数、字符串函数、日期函数、以下几类：数学函数、字符串函数、日期函数、转换函数、聚合函数等转换函数、聚合函数等.三三.运算符与函数运算符与函数 数数据据库基基础与与应用（用（SQL Server 2008）运算符运算符说明说明语法语法Not对表达式的表达式的值取反取反Not ExpressionAnd与，如果表达式的与，如果表达式的值都都为True，结果果为True，否，否则为FalseExpression1 and Expression2Or或，如果表达式的或，如果表达式的

23、值都都为False，结果果为False，否，否则为TrueExpression1 or Expression2Betweenand如果操作数在某个范如果操作数在某个范围内，内，结果果为TrueExpression between A and BIn如果操作数等于如果操作数等于值列表中的任何一列表中的任何一个，个，结果果为TrueExpression in(值表或子表或子查询)Like如果字符型操作数与某个模式匹配，如果字符型操作数与某个模式匹配，结果果为TrueExpression1 like Expression2Exists如果子如果子查询结果不空，果不空，结果果为TrueExists（子

24、（子查询）Any或或Some如果操作数与一列如果操作数与一列值中的任何一个中的任何一个比比较结果果为True，结果果为TrueExpression any(值表或子表或子查询)All如果操作数与一列如果操作数与一列值中所有中所有值的比的比较结果果为True，结果果为TrueExpression、=、=、!=,!、!（比较运算符）（比较运算符）vNotvAndvAll、Any、Between、In、Like、Or、Somev=（赋值）（赋值）运算符的优先级运算符的优先级 数数据据库基基础与与应用（用（SQL Server 2008）vAbs(numeric_expression)：返回指定数值表

25、达式的绝对值返回指定数值表达式的绝对值vRound(numeric_expression,length ,function )：返回一个舍返回一个舍入到指定的长度或精度的数值入到指定的长度或精度的数值vFloor(numeric_expression)：返回小于或等于指定数值表达式的最大返回小于或等于指定数值表达式的最大整数整数vCeiling(numeric_expression)：返回大于或等于指定数值表达式的最返回大于或等于指定数值表达式的最小整数小整数vPower(float_expression,y)：返回指定表达式的指定幂的值返回指定表达式的指定幂的值vSqrt(float_exp

26、ression)：返回指定表达式的平方根返回指定表达式的平方根vSquare(float_expression)：返回指定表达式的平方返回指定表达式的平方vExp(float_expression)：返回指定的表达式的指数值返回指定的表达式的指数值vLog(float_expression)：返回指定表达式的自然对数返回指定表达式的自然对数vLog10(float_expression)：返回指定表达式的以返回指定表达式的以10为底的对数为底的对数vSin(float_expression)：返回指定角度（以弧度为单位）的三角正弦值返回指定角度（以弧度为单位）的三角正弦值vCos(float_

27、expression)：返回指定角度（以弧度为单位）的三角余弦值返回指定角度（以弧度为单位）的三角余弦值常用的数学函数常用的数学函数 数数据据库基基础与与应用（用（SQL Server 2008）vAscii(character_expression)：返回字符表达式中最左侧的字符的返回字符表达式中最左侧的字符的 ASCII 代码值代码值vChar(integer_expression)：将将 int型的型的 ASCII 代码转换为字符代码转换为字符vStr(float_expression ,length ,decimal )：返回由数字数据转返回由数字数据转换来的字符数据换来的字符数据vL

28、en(string_expression)：返回指定字符串表达式的字符数，其中不包含尾返回指定字符串表达式的字符数，其中不包含尾随空格随空格vSubstring(value_expression,start_expression,length_expression)：返回字符表达式的从返回字符表达式的从start_expression位置开始的位置开始的长度为长度为length_expression的子串的子串vLeft(character_expression,integer_expression)：返回字符串中从返回字符串中从左边开始指定个数的字符左边开始指定个数的字符vRight(cha

29、racter_expression,integer_expression)：返回字符串中返回字符串中从右边开始指定个数的字符从右边开始指定个数的字符vLtrim(character_expression)：返回删除了前导空格之后的字符表达式返回删除了前导空格之后的字符表达式vRtrim(character_expression)：截断所有尾随空格后返回一个字符串截断所有尾随空格后返回一个字符串常用的字符串函数常用的字符串函数 数数据据库基基础与与应用（用（SQL Server 2008）vGetdate()：返回系统当前的日期和时间返回系统当前的日期和时间vYear(date)：返回表示指定参

30、数返回表示指定参数 date 的的“年年”部分的整数部分的整数vMonth(date)：返回表示指定参数返回表示指定参数 date 的的“月月”部分的整数部分的整数vDay(date)：返回表示指定参数返回表示指定参数date 的的“日日”部分的整数部分的整数vDatename(datepart,date)：返回表示指定参数返回表示指定参数date的指定的指定 datepart参数参数 的字符串的字符串vDatepart(datepart,date)：返回表示指定参数返回表示指定参数 date 的指定的指定 datepart参数参数 的整数的整数vDatediff(datepart,start

31、date,enddate)：根据指定根据指定datepart参数返回两个指定日期之间的差值参数返回两个指定日期之间的差值vDateadd(datepart,number,date)：根据根据datepart参数将一个参数将一个时间间隔与指定时间间隔与指定 date 参数相加，返回一个新的参数相加，返回一个新的 datetime 值值日期和时间函数日期和时间函数 数数据据库基基础与与应用（用（SQL Server 2008）用户自定义函数可以接受参数，执行复杂的操用户自定义函数可以接受参数，执行复杂的操作并将操作的结果以值的形式返回作并将操作的结果以值的形式返回.根据函数返回根据函数返回值的类型

32、，可分为标量值函数（数值函数）和表值值的类型，可分为标量值函数（数值函数）和表值函数函数.与其它数据库对象一样，用与其它数据库对象一样，用 CREATE FUNCTION创建，用创建，用 ALTER FUNCTION 修改，用修改，用 DROPFUNCTION 删除删除.也可以用也可以用SSMS管理管理.注意函数与存储过程的区别注意函数与存储过程的区别.用户自定义函数用户自定义函数 1 用用 T-SQL 语句编写程序，计算语句编写程序，计算 1+2+100.2 创建一个函数，在数据库创建一个函数，在数据库 pubs 的的 titles 表中查询表中查询 title_id,title,type，price 并以并以 type 为参数为参数.练习练习