授课15数据库查询与维护3TSQL程序设计课件

1、授课15-数据库查询与维护_3 (T-SQL程序设计)13.1 Transact-SQL语言基础13.2 批处理与程序流程控制语句13.3 系统内置函数13.4 用户自定义函数【任务（实验21）】 程序控制流程语句的使用实验内容:p279-p28013.1 Transact-SQL语言基础13.1.1 标识符13.1.2 常量13.1.3 变量13.1.4 运算符与表达式13.1.5 注释13.1.1 标识符1常规标识符常规标识符应符合如下规则：（1）第一个字符必须是下列字符之一： ASCII字符、Unicode字符、下划线 (_)、或#。（2）后续字符可以是：ASCII字符、Unicode字

2、符、下划线 (_)、美元符号 ($)或数字符号。（3）标识符不能是 Transact-SQL 的保留字。（4）不允许嵌入空格或其它特殊字符。 (5) 以符号开始的标识符名称具有的特殊用途如下所述。 以 符号开始的标识符表示局部变量或参数。 以 双符号开始的标识符表示全局变量。 以数字标记#符号开始的标识符表示临时表或过程。 以数字标记#双符号开始的标识符表示全局临时对象。2 分隔标识符 如果标识符遵从标识符格式的所有规则，则既可以使用分隔符，也可以不使用分隔符。对不符合标识符的格式规则的标识符必须进行分隔。(1) 分隔标识符类型有以下两种。 被引用的标识符用双引号(“”)分隔开。 括在括号中的

3、标识符用方括号( )分隔。分隔标识符使用示例：SELECT * FROM Blanks In Table NameSELECT * FROM Blanks in Table Name13.1.1 标识符 (2) 分隔标识符的格式规则如下所述。 分隔标识符可以包含与常规标识符相同的字符数(1128 个，不包括分隔符字符)。 标识符的主体可以包含当前代码页内字母(分隔符本身除外)的任意组合。(3) 分隔标识符在下列情况下使用。 当在对象名称或对象名称的组成部分中使用保留字时，推荐不要使用保留关键字作为对象名称，可用分隔标识符引用对象直到可改变其名称。 当使用未被列为合法标识符的字符时，允许在分隔标

4、识符中使用当前代码页中的任何字符。引用SQL Server 对象 可以通过几种方法引用SQL Server 对象。既可以指定对象的完整名称，也可以只指定对象的部分名称，让SQL Server 根据当前操作的上下文环境确定名称的其余部分。(1) 完全合法名称 SQL Server 对象的完整名称包括4个标识符：服务器名称、数据库名称、所有者名称和对象名称。其格式为： Server.database.owner.object 指定了所有4部分的对象名称被称为完全合法名称。SQL Server 中所创建的每一个对象必须具有唯一的完全合法名称。例如，在同一个数据库中可以有两个名称为部门信息的表，只要是

5、分属于不同的所有者即可。同样地，同一个表或视图中的列名也必须唯一。(2) 部分指定名称 当引用一个对象时，不需要指定服务器、数据库和所有者，可以利用句点指出其位置从而省略限定符。下面列出对象名称的有效格式： Server.database.owner.object database.owner.object database.Object owner.object object若创建一个对象时没有指定该名称的不同部分，SQL Server 则使用以下默认值。 服务器默认为本地服务器的当前实例。 数据库默认为当前数据库。 所有者默认为指定数据库中与当前连接的登录标识(ID)相对应的用户名称。13

6、.1.2 常量类 型说 明例 如整型常量没有小数点和指数E60，-365，0 x12F（十六进制），10010（二进制）实型常量定点表示浮点表示15.63，-200.25+123E-3，-12E5ASCII字符串常量单引号括起来学生，this is database，OBbaar（串中包含单引号）Unicode字符串常量前缀是大写字母“N”单引号括起来N学生，NHow do you! 日期时间常量单引号括起来6/5/06，10-12-06，19491001，10-12-06 14:30:24货币型常量以“$”作为前缀的整型或实型常量$450，$3130.2，-$45.56全局唯一标识符前缀0

7、x单引号()引起来0 x6F9619FF13B136D011B42D00C04FC964FF6F9619FF-13B136-D011-B42D-00C04FC964FF指在程序运行过程中值不变的量。常量1.字符串常量 包含在单引号内，由字母数字字符(a-z,A-Z,0-9)及特殊字符(如：！、)组成. 如：SQL Server 2000 实例与应用2.数值常量 包含以下内容:二进制常量 、bit常量、integer常量、decimal常量、float常量 、Real常量、money常量、指定负数和正数。3.日期常量 使用特定格式的字符日期值表示，并被单引号括起来如：19831231、1976/

8、04/23、14：30：244. uniqueidentifier常量：表全局唯一标识符值的字符串常量：注：引用数值常量不用单引号，引用日期、字符串常量时需要加单引号13.1.3 变量 常量和变量是程序设计中不可缺少的元素。变量又分为局部变量和全局变量，局部变量是一个能够保存特定数据类型实例的对象，是程序中各种类型数据的临时存储单元，用在批处理内SQL语句之间传递数据。局部变量的作用域，只在声明它的批处理内，一旦批处理结束，局部变量自动消失。全局变量是系统给定的特殊变量。13.1.3 变量变量分类：1局部变量用来存储指定数据类型的单个数据值对象。 2全局变量由系统提供且预先声明的用来保存SQL

9、 Server系统运行状态数据值的变量。用于临时存放数据，变量中的数据随着程序的运行而变化。 变量对应于内存中的一个存储空间，值在程序运行过程中可以随时改变。13.1.3 变量1局部变量（1）局部变量的定义在批处理或存储过程中用DECLARE语句声明局部变量，所有局部变量在声明后均初始化为NULL。语法格式：DECLARE local_variable data_type ,n（2）局部变量的赋值当声明局部变量后，可用SET或SELECT语句给其赋值。 用SET语句赋值语法格式：SET local_variable=expression用于临时存放数据，变量中的 数据随着程序的运行而变化。例1

10、3.1创建局部变量，并赋值，然后输出变量。declare todayDate char(10),dispStr varchar(20)set todayDate=getdate()set dispStr=今天的日期为:print dispstr+todaydateselect dispstr+todaydatePRINT显示在“消息”框，SELECT显示在“网格”框。例13.2创建一个名为xb的局部变量，并在Select语句中使用该局部变量查找表XSB中所有女同学的学号和姓名。Use stubookDeclare xb char(2)Set xb=女Select 学号,姓名From xsbWh

11、ere 性别=xbGo 用SELECT语句赋值语法格式：SELECT local_variable=expression ,n例13.3创建局部变量var1、var2，并使用查询给变量赋值。Use stubookDeclare var1 varchar(20),var2 varchar(20)Select var1=姓名, var2=院系from xsbwhere 院系=计算机Select var1, var2go观察查询结果，比较使用 Select 姓名,院系 from xsb where 院系=计算机的结果。说明:1.局部变量 用户在程序中定义的变量，一次只能保存一个值，仅在定义的程序范围

12、内有效；Declare dispstr varchar(20)Set dispstr=这是一个局部变量引用出错的演示Go-批处理在这里结束，局部变量被清除。Print dispstrGo 说明: 赋值：初值为空，可以使用select语句和set语句对局部变量进行赋值。 set语句一次只能给一个局部变量赋值，select可以给一个获多个赋值。如：DECLARE var1 int,var2 moneySELECT var1=50,var2=$29.95SELECT var1,var2 实 例 1：编写计算两个整数之和的程序DECLARE i tinyint,j tinyint,sum tinyin

13、t 定义3个局部变量 SET i=50 SET j=60 SELECT sum=i+j PRINT sum 显示sum变量的内容可写成： DECLARE i tinyint,j tinyint,sum tinyint -定义3个局部变量 select i=50,j=60,sum=i+j PRINT sum -显示sum变量的内容实例2打印xscc表中有多少种类型的课程，要求声明局部变量，进行赋值，然后打印变量内容。DECLARE CKind varchar(50)Select CKind=在xscc表中有+convert(varchar(3),(select count(distinct 课程

14、名称) from xscc)+数据类型的课程Print CkindConvert( data_type, expression)将表达式显式地转换为另一种数据类型局部变量注意：通常在以下几种情况使用局部变量1.作为计数器计算循环执行的次数2.保存数据值以供流控制语句测试3.保存由存储过程返回代码返回的数据类型13.1.3 变量 2全局变量 全局变量是SQL Server系统提供并赋值的变量。用户不能定义全局变量，也不能用SET语句来修改全局变量。通常是将全局变量的值赋给局部变量，以便保存和处理。事实上，在SQL Server中，全局变量是一组特定的函数，它们的名称是以开头，而且不需要任何参数，

15、在调用时无需在函数名后面加上一对圆括号，这些函数也称为无参数函数。 大部分的全局变量记录了SQL Server服务器的当前状态信息。13.1.3 变量由系统提供且预先声明的用来保存SQL Server系统运行状态数据值的变量。名 称说 明connections返回当前服务器的连接数目rowcount返回上一条T-SQL语句影响的数据行数error返回上一条T-SQL语句执行后的错误号procid返回当前存储过程的ID号remserver返回登录记录中远程服务器的名字spid返回当前服务器进程的ID标识version返回当前SQL Server服务器的版本和处理器类型language返回当前SQ

16、L Server服务器的语言全局变量 全局变量以开头，实际上是SQL Server的配置函数（也称配置函数，是系统定义的变量，以开头；用户只能使用）全局变量利用全局变量查看SQL Server的版本、当前使用的语言、服务器及服务器名称。print 所用SQL sever的版本信息print versionprint print 服务器名称为： +servernameprint 所用的语言为： +languageprint 所用的服务为: +servicenamego13.1.4 运算符与表达式表达式可以是列名、字符、运算符或函数的任意组合。运算符用来指定要在一个或多个表达式中执行的操作。运算符

17、包含以下类型:算术运算符（+、*、/、%）比较运算符（=、=、=、!=、!）逻辑运算符（NOT、OR、AND、IN、LIKE、BETWEEN、EXISTS、SOME 、 ANY、 ALL）字符串连接运算符（）赋值运算符（=）位运算符（&、|、）13.1.4 运算符与表达式1算术运算符 算术运算符：加(+)、减(-)、乘(*)、除(/)和取余(%)，参与运算的数据是数值类型数据，其运算结果也是数值类型数据。2字符串连接运算符 字符串连接运算符(+)可以实现字符串之间的连接。参与字符串连接运算的数据只能是字符数据类型：char、varchar、nchar、nvarchar、text、ntext，其

18、运算结果也是字符数据类型。13.1.4 运算符与表达式3比较运算符运 算 符含 义=相等大于=大于等于=小于等于 、!=不等于!不大于13.1.4 运算符与表达式4逻辑运算符 运 算 符运 算 规 则AND如果两个操作数值都为TRUE，运算结果为TRUEOR如果两个操作数中有一个为TRUE，运算结果为TRUENOT若一个操作数值为TRUE，运算结果为FALSEALL每个操作数值都为TRUE，运算结果为TRUEANY在一系列操作数中只要有一个为TRUE，运算结果为TRUEBETWEEN如果操作数在指定的范围内，运算结果为TRUEEXISTS如果子查询中包含一些行，运算结果为TRUEIN若操作数值

19、等于表达式列表中的一个，结果为TRUELIKE若操作数与一种模式相匹配，运算结果为TRUESOME如果在一系列操作数中，有些值为TRUE，结果为TRUE13.1.4 运算符与表达式5运算符的优先级运 算 符优 先 级+（正）、-（负）、（按位 NOT）1*（乘）、/（除）、%（模）2+（加）、（+ 串联）、-（减）3=、 =、 =、!=、!、! 比较运算符4（位异或）、&（位与）、|（位或）5NOT6AND7ALL、ANY、BETWEEN、IN、LIKE、OR、SOME8=（赋值）913.1.5 注释 脚本文件除了含有T-SQL语句外，还包含有对SQL语句进行说明的注释。注释是不能执行的文字字

20、符串，或暂时禁用的部分语句。为程序加注释不仅能使程序易懂，更有助于日后的管理和维护。注释通常用于记录程序名、作者姓名和主要的程序更改日期，也用于描述复杂的计算或解释编程方法等。SQL Server支持两种形式的注释语句：行内注释与块注释。13.1.5 注释2块注释块注释的语法格式为：/*注释文本*/或：/*注释文本*/1行内注释行内注释的语法格式为： 注释文本13.2 批处理与程序流程控制语句13.2.1 批处理13.2.2 流程控制语句13.2.1 批处理 批处理就是单个或多个Transact-SQL语句的集合，由应用程序一次性发送给SQL Server解释并执行批处理内的所有语句指令。使用

21、GO命令和使用EXECUTE命令可以将批处理发送给SQL Server。 1GO命令 GO命令本身不属于Transact-SQL语句。 2EXEC命令 EXEC命令用于执行用户定义的函数以及存储过程。 13.2.1 批处理批处理规律： 大多数create命令要在单个批处理中执行，但create database、create table、create index例外。批处理的特点一次发给数据库服务器的所有的SQL语句，由GO作为结束标志。编译成一个可执行单元执行计划执行时，每次执行计划中的一条语句特别注意：如果在一个批处理中，某条语句存在语法错误，SQL Server将不执行批处理中的任何语句

22、。批处理实例查询分析器输入如下指令：use studentgocreate view testas select * from xsdagoselect * from xsccgo由于CREATE VIEW 建立视图语句不能和其他使用语句放在同一个批处理中，所以需要GO命令将CREATE VIEW语句与其下的语句SELECT分成两个批处理。否则SQL Server将报错。13.2.2 流程控制语句 流控语句可以和T-SQL语句一起使用以控制程序流，可以在批内部使用流控语句，也可以在存储过程、脚本和特定的检索内部使用。应用实例：利用流控语句计算1+2+3+4+5+.+10000的和。 命令如下：

23、DECLARE i int, sum int; SELECT i=1, sum=0 WHILE i=10000 BEGIN SELECT sum=sum+i SELECT i=i+1 END SELECT SUM GO 流控制语句流控制语句可以实现程序的3种基本结构： 顺序结构、选择结构、循环结构T-SQL提供的流控制如下:1复合语句包括一系列的 Transact-SQL 语句，称为语句块。语法格式：BEGIN sql_statement /*是任何有效的 Transact-SQL 语句 nEND 13.2.2 流程控制语句2PRINT语句将用户定义的消息返回客户端。在屏幕上显示用户的信息以及

24、char、varchar数据类型变量的内容，其他数据类型必须先进行类型转换。语法格式：PRINT any ASCII text | local_variable | FUNCTION | string_expr13.2.2 流程控制语句3判断语句（IFELSE） 在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的T-SQL语句，可用IFELSE语句实现。语法格式：IF boolean_expression /*条件表达式 sql_statement|statement_block /*条件表达式为真时执行T-SQL语句或语句块ELSE sql_statement|stateme

25、nt_block/*条件表达式为假时执行T-SQL语句或语句块13.2.2 流程控制语句4循环语句（WHILE）、BREAK语句和CONTINUE语句（1）WHILE循环语句如果需要重复执行程序中的一部分语句，可使用WHILE循环语句实现。语法格式：WHILE boolean_expression sql_statement|statement_block /*T-SQL语句或语句块构成的循环体（2）BREAK语句一般用在循环语句中，用于退出本层循环。当程序中有多层循环嵌套时，使用BREAK语句只能退出其所在的这一层循环。语法格式：BREAK（3）CONTINUE语句一般用在循环语句中，结束本

26、次循环，重新转到下一次循环条件的判断。语法格式：CONTINUE13.2.2 流程控制语句USE pubsGO WHILE (SELECT AVG(price) FROM titles) $50 BREAK ELSE CONTINUE ENDPRINT Too much for the market to bear 13.2.2 流程控制语句5RETURN语句 用于从过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。 实现无条件退出批命令、存储过程或触发器。RETURN语句可以返回一个整数给调用它的过程或应用程序，返回不同的值表明执行的不同情况。 返回0 表明成功返回返回-1

27、-99代表不同的出错原因返回-1指“丢失对象”返回-2 指数据类型错误。语法格式：RETURN integer_expression 流控制语句6.GOTO语句 GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行。GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“：”结尾。GOTO labellabel:语法格式：流控制语句利用GOTO语句求出从1加到5的总和。declare sum int, count intselect sum=0, count=1label_1:select sum=sum+countselect count=c

28、ount+1if count=5goto label_1select count , sum流控制语句7. WAITFOR语句 延迟或暂停程序的执行。语法形式为：WAITFOR DELAY 时间间隔或WAITFOR TIME 时间值 DELAY指示数据库服务器一直等待所指定的时间过去，最长可达24小时，TIME指示SQL Server等待到所指定的时间 如：晚上8点执行语句SELECT * FROM Course WAITFOR TIME 20:00:00 SELECT * FROM Course13.3 系统内置函数13.3.1 数学函数13.3.2 字符串函数13.3.3 日期和时间函数1

29、3.3.4 聚合函数13.3.5 系统函数13.3.1 数学函数对数值型数据进行数学运算1ABS函数语法格式：ABS ( numeric_expression ) SELECT ABS(-1.0), ABS(0.0), ABS(1.0) - - - 1.0 .0 1.0 13.3.1 数学函数2ROUND函数语法格式：ROUND ( numeric_expression , length , function )返回数字表达式并四舍五入为指定的长度或精度。Length：是 numeric_expression 将要四舍五入的精度。length 必须是 tinyint、smallint 或int

30、。当 length 为正数时，numeric_expression 四舍五入为 length 所指定的小数位数。当 length 为负数时，numeric_expression 则按 length 所指定的在小数点的左边四舍五入。13.3.1 数学函数2ROUND函数语法格式：ROUND ( numeric_expression , length , function ) function：是要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略 function 或 function 的值为 0（默认），numeric_expression 将四舍

31、五入。当指定 0 以外的值时，将截断 numeric_expression。13.3.1 数学函数例 ： Select ROUND(123.4545, 2) Select ROUND(123.4545, -2) Select ROUND(748.58, -1)Select ROUND(748.58, -2)Select ROUND(748.58, -3)Select ROUND(748.58, -4) Select ROUND(150.75, 0) Select ROUND(150.75, 0, 1) 13.3.1 数学函数3RanD函数语法格式：RAND ( seed ) DECLARE c

32、ounter smallint SET counter = 1 WHILE counter 5 BEGIN SELECT RAND(counter) as 随机数SET counter = counter + 1 ENDGO 13.3.1 数学函数例：将1800转化为弧度 select RADIANS(180) 例.求1238 的值及64的平方根 select power(123,8) Select sqrt(64)13.3.2 字符串函数用于对字符串进行连接、截取等操作1ASCII函数语法格式：ASCII ( character_expression ) 2CHAR函数语法格式：CHAR (

33、 integer_expression ) 3LEFT函数语法格式：LEFT ( character_expression , integer_expression ) 4RIGHT函数语法格式：RIGHT ( character_expression , integer_expression ) 13.3.2 字符串函数5SUBSTRING函数语法格式：SUBSTRING ( expression , start , length )6.REPLACE函数语法格式：REPLACE ( string_expression1 , string_expression2 , string_expre

34、ssion3 ) 7STR函数语法格式：STR ( float_expression , length , decimal ) 13LEN语法格式：LEN ( string_expression ) 例1：给出“数据库”在“大型数据库”中的位置 SELECT CHARINDEX(数据库,大型数据库)例2：计算字符串“SQL SERVER 数据库管理系统”的长度 SELECT LEN(SQL SERVER 数据库管理系统)13.3.3 日期和时间函数显示日期和时间的信息，处理datatime,smalldatatime的值，对其进行算术运算1GETDATE函数语法格式：GETDATE ( )2D

35、AY函数语法格式：DAY ( date ) 3MONTH函数语法格式：MONTH ( date )4YEAR函数语法格式：YEAR ( date )13.3.3 日期和时间函数日期函数功能Getdate()返回服务器当前日期和时间Datename(日期元素，日期)返回指定日期的名字，字符串型Datepart(日期元素，日期)返回指定日期的一部分，整数型Datediff(日期元素，日期1，日期2)返回两个日期间的差值并转换为指定日期元素的形式Dateadd(日期元素，数值，日期)将日期元素加上日期产生新的日期Year(日期)返回年份Month(日期)返回月份day(日期)返回某月几号的整数值Ge

36、tutcdate()返回当前UTC时间日期元素缩写取值yearyy1753-9999monthMm1-12dayDd1-31hourhh0-23给出服务器当前的系统日期和时间，给出系统当前的月份和月份名字。select getdate() 当前日期和时间,datepart(year,getdate() 年,datename(year,getdate() 年名,datepart(month,getdate() 月份,datename(month,getdate() 月份名,datepart(day,getdate() 日print 当前日期+datename(year,getdate()+年+d

37、atename(month,getdate()+月+datename(day,getdate()+日goMary的生日为1980/8/13，请使用日期函数计算Mary的年龄和天数。 select 年龄=datediff(year,1990/8/13,getdate(), 天=datediff(day,1980/8/13,getdate() go13.3.4 聚合函数也称为统计函数，对一组值进行计算并返回一个数值，常与SELECT一 并使用.如：例1.计算xscc的总行数 select count(*) as course总行数 from xscc例2.计算xscj表课程编号为001的平均成绩S

38、elect avg(成绩) as 平均成绩 from xscj Where 课程编号=00113.3.4 聚合函数函 数名 称说 明MAX求最大值返回表达式中的最大值项MIN求最小值返回表达式中的最小值项SUM求和计算并返回表达式中的各项的和AVG求平均值计算并返回表达式中各项的平均值COUNT计数返回一个集合中的项数，返回值为整型13.3.5 系统函数1CASE函数（1）简单的CASE语句：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 语法格式：CASE input_expression WHEN when_expression THEN result_express

39、ion nELSE else_result_expressionEND（2）搜索型CASE语句： CASE 搜索函数计算一组布尔表达式以确定结果语法格式：CASE WHEN Boolean_expression THEN result_expression nELSE else_result_expressionEND实例 对课程进行分类统计，要求显示课程名称、学号、成绩，并计算各门课的平均成绩，按照课程名称，学号和成绩升序排序，用CASE语句实现实例Select 课程名称= case 课程编号 when 001 then GFlash动画欣赏与设计 When 002 then G图形图象处理

40、（CorelDRAW) when 003 then G信息应用技术基础Else G网页设计与制作End, 学号 ,成绩 FROM xscjORDER BY 课程名称,学号,成绩COMPUTE AVG(成绩) by 课程名称Go例：使用简单的CASE语句编写。查询学生信息，通过地址中的城市名确定其所属城市。select 姓名, 系别=case when 专业 like %软件% then 计算机系 when 专业 like %食品% then 化工系 when 专业 like %建工% then 土木系 else 其它系end,年龄from xsdago13.3.5 系统函数2CURRENT_U

41、SER语法格式：CURRENT_USER（）返回当前数据库用户3HOST_NAME语法格式：HOST_NAME ( )返回工作站名称例：给出主机名称。 Select host_name()13.4 用户自定义函数13.4.1 用户自定义函数的定义与调用13.4.2 用户自定义函数的删除13.4.1 用户自定义函数的定义与调用1标量函数 标量函数返回一个确定类型的标量值，其返回值类型为除了TEXT、NTEXT、IMAGE、CURSE、TIMESTAMP和TABLE类型外的其它数据类型。函数体语句定义在BEGINEND语句内。13.4.1 用户自定义函数的定义与调用1标量函数（1）标量函数的定义C

42、REATE FUNCTION 所有者名称.函数名称 （参数名称 AS 标量数据类型=默认值n） RETURNS 标量数据类型 AS BEGIN 函数体 RETURN 标量表达式 END13.4.1 用户自定义函数的定义与调用create function dbo.average(cnum char(20) returns intas begin declare aver int select aver=avg(成绩) from xscj where 课程编号=cnum return aver endgo13.4.1 用户自定义函数的定义与调用（2）标量函数的调用 当调用用户自定义标量函数时，必须提供至少由两部分组成的名称（所有者名.函数名）。可在SELECT语句中调用用户定义标量函数，也可利用T-SQL的EXECUTE语句调用用户定义标量函数。 在SELECT语句中调用语法格式：所有者名.函数名（实参